Управление данными и их анализ в табличном процессоре

Список— прямоугольная область ячеек, в которой строки электронной таблицы имеют фиксированную упорядоченную структуру заполнения.

Во всех строках списка происходит одинаковое использование столбцов. Все ячейки в данном столбце содержат один и тот же тип данных. Верхняя строка списка содержит имена (названия) столбцов. Название может состоять из нескольких слов, размещенных в одной ячейке.

Со списком можно выполнять различного вида обработку. Работа со списком выполняется с помощью пункта меню приложений «Данные».

Сортировка списков

(Команда «Данные -> Сортировка»)

Строки в списке можно сортировать по значениям ячеек одного или нескольких столбцов. Кроме этого, список можно сортировать по строкам. Строки, столбцы или отдельные ячейки в процессе сортировки переупорядочиваются в соответствии с заданным пользователем порядком сортировки. Существует два порядка сортировки: по возрастаниюи по убыванию.

Фильтрация списков

Фильтруя список, можно отображать только те строки, которые удовлетворяют заданным условиям отбора. Например, список покупок, сделанных посетителями, можно отфильтровать, так что на экран будут выведены имена только тех покупателей, которые совершили покупки больше чем на 5000 долларов. В Microsoft Excel доступны два способа фильтрации данных: автофильтр и расширенный фильтр (команды «Данные -> Фильтр -> Автофильтр» и «Данные -> Фильтр -> Расширенный фильтр»).

При выполнении команды «Данные ->Фильтр -> Автофильтр» у каждого названия столбца появляется раскрывающийся список значений, который используется для задания условий фильтрации. Чтобы отфильтровать строки, содержащие определенное значение, необходимо открыть раскрывающийся список и выбрать необходимые значения. Чтобы применить операторы сравнения, необходимо выбрать пункт «Условие». В условии можно использовать оператор И (ИЛИ).

Чтобы отфильтровать список с помощью «Расширенного фильтра», столбцы списка должны иметь заголовки. На листе также должно быть не менее трех пустых строк выше списка. Эти строки будут использованы в качестве диапазона условий отбора.

В условия отбора расширенного фильтра может входить несколько условий, накладываемых на один столбец, несколько условий, накладываемых одновременно на несколько столбцов, а также условия, накладываемые на возвращаемое формулой значение.

1. На ячейки одного столбца накладываются три и более условия отбора.

Чтобы задать для отдельного столбца три и более условия отбора, введите условия в ячейки, расположенные в смежных строках. Например, для следующего диапазона условий будут отобраны строки, содержащие «Белов», «Батурин» или «Сушкин» в столбце «Продавец».

Продавец

Белов

Батурин

Сушкин

2. Условие отбора накладывается на ячейки двух и (или) более столбцов.

Чтобы наложить условия отбора не несколько столбцов одновременно, необходимо ввести условия в ячейки, расположенные в одной строке диапазона условий. Например, для следующего диапазона условий будут отобраны строки, содержащие «Продукты» в столбце «Товар», «Белов» в столбце «Продавец» и имеющие сумму реализации больше 1000 рублей.

Примечание. В данном случае также может быть использована команда «Данные -> Автофильтр».

Чтобы выбрать строки, удовлетворяющие одному из нескольких условий, наложенных на разные столбцы, необходимо ввести условия в ячейки, расположенные в разных строках диапазона условий. Например, для следующего диапазона условий будут отобраны строки, содержащие либо «Продукты» в столбце «Товар», либо «Белов» в столбце «Продавец», либо имеющие сумму реализации больше 1000 рублей.

Чтобы наложить сложное условие отбора, необходимо ввести его составные части в отдельные строки диапазона условий. Например, для следующего диапазона условий будут отобраны строки, содержащие «Белов» в столбце «Продавец» и имеющие сумму реализации больше 3000 рублей, или строки, содержащие «Батурин» в столбце «Продавец» и имеющие сумму реализации больше 1500 рублей.

3. В условии отбора используется возвращаемое формулой значение.

В условии фильтрации можно использовать возвращаемое формулой значение. При задании формул в условиях нельзя использовать в качестве заголовка условия заголовки столбцов списка. Необходимо ввести заголовок, который не является заголовком столбца списка, или оставить заголовок условия незаполненным. Например, для следующего диапазона условий будут отображены строки, в которых значение в столбце G превышает среднее значение в ячейках Е5-Е14, при этом заголовок условия не используется:

Примечание. Используемая в условии формула должна ссылаться либо на заголовок столбца (например, «Продажи»), либо на соответствующее поле в первой записи. В приведенном примере G5 ссылается на соответствующее поле (столбец G) первой записи (строка 5) списка. При использовании заголовка столбца в формуле условия вместо ссылки или имени диапазона в ячейке будет выведено значение ошибки #ИМЯ? или #ЗНАЧ!. Эту ошибку можно не исправлять, так как она не повлияет на результаты фильтрации.

Форма (Команда «Данные --> Форма»)

Для просмотра, изменения, добавления и удаления записей списка, а также для поиска записей, удовлетворяющих заданным условиям, удобно использовать форму. При обращении к команде «Данные -> Форма» создается окно диалога формы, через которое происходит ввод/вывод данных. Всякий раз в окне формы можно видеть содержимое только одной текущей записи. При вводе и правке данных в форме сделанные изменения вносятся в ячейки листа, соответствующие полям текущей записи.

Суммирование значений в списке

Microsoft Excel предоставляет несколько возможных способов суммирования и анализа значений в списке.

Для расчета общего итога по столбцу или по строке может быть использовано автоматическое суммирование, позволяющее быстро создать формулу суммирования значений столбца или строки.

Имеется возможность автоматического расчета промежуточных итогов по значениям списка. Например, для списка, содержащего суммы продаж для представителей отдела продаж по разным регионам, можно рассчитать промежуточные итоги для каждого региона или для каждого представителя отдела продаж. Для этого используется команда «Данные -> Итоги». Предварительно нужно отсортировать список.

Рис. 2

Для расчета итогового значения по строкам списка в соответствии с заданным условием можно воспользоваться командой «Данные -> Автофильтр», чтобы вывести строки, удовлетворяющие этому условию, а затем рассчитать сумму только по этим строкам с помощью автоматического суммирования.

Рис.3.

Суммарные значения списка могут быть определены в отчете, использующем методы расчетов и форматы, заданные пользователем с помощью сводной таблицы. Для создания сводной таблицы необходимо использовать команду «Данные -> Сводная таблица». Сводные таблицы располагают данные следующим образом: выбранные поля становятся заголовками строк, столбцов или страниц, а на пересечении строк и столбцов помещаются сводные значения, полученные по указанным пользователем формулам.

Выводы

1. Табличные процессоры, такие, как Microsoft Excel, относятся к программным средствам, позволяющим автоматизировать табличные расчеты, создавать, редактировать и печатать ведомости.

2. В каждом листе электронной таблицы используется сетка, состоящая из строк и столбцов.

3. Пересечение строк и столбцов образует ячейку.

4. Текущая ячейка - это ячейка, которая в данный момент способна воспринимать ввод с клавиатуры.

5. Строка формул - строка над рабочей областью, разбитая на три части.

6. Строка состояния - область в нижней части окна, отображающая сведения о выбранной команде или выполняемой операции.

7. Текст - любая последовательность, состоящая из цифр, пробелов и нецифровых символов.

8. Формулы представляют собой последовательность значений, ссылок на ячейки, имен, функций или операторов и вычисляют новое значение на основе существующих. Формула всегда начинается со знака равенства (=).

9. Группа выбранных ячеек называется диапазоном.

10.Для отображения текстовых данных, чисел, денежных сумм, календарных дат используют различные форматы.

11.Табличный процессор позволяет создавать диаграмму и располагать ее рядом с таблицей или размещать на отдельном рабочем листе.

12.Программа Excel позволяет также одновременно вводить и редактировать данные на нескольких рабочих листах.

13.Excel выводит в ячейку значение ошибок, когда формула для этой ячейки не может быть правильно вычислена.

14.Для выполнения вычислений на листах книги можно использовать стандартные функции Microsoft Excel.

15.В Excel имеются следующие логические функции: ЕСЛИ, И, ИЛИ, ИСТИНА, ЛОЖЬ, НЕ.

16.Список - прямоугольная область ячеек, в которой строки электронной таблицы имеют фиксированную упорядоченную структуру заполнения.

17.Строки в списке можно сортировать по значениям ячеек одного или нескольких столбцов.

18.Фильтруя список, можно отображать только те строки, которые удовлетворяют заданным условиям отбора.

19.Для просмотра, изменения, добавления и удаления записей списка, а также для поиска записей, удовлетворяющих заданным условиям, удобно использовать форму.

20.Microsoft Excel предоставляет несколько возможных способов суммирования и анализа значений в списке.

Контрольные вопросы

1. Для чего предназначен табличный процессор?

2. Какие типы данных можно вводить в табличный процессор Microsoft Excel? Дайте краткую характеристику перечисленным типам.

3. Что такое абсолютная и относительная ссылки?

4. Понятие ячейки в Microsoft Excel. Из чего складывается адрес ячейки?

5. Понятие текущей ячейки. Как она выделяется в табличном процессоре?

6. Понятие списка в Microsoft Excel. Манипуляции со списком.

7. Чем характеризуется формула в Microsoft Excel?

8. Как построить диаграмму в Microsoft Excel?

9. Для чего предназначен маркер заполнения?

10.Назначение функций. Синтаксис функций.