4.4.5. Средства анализа данных в таблицах

В MS Excel в целях анализа имеется возможность графического пред­ставления данных в виде диаграмм. При этом можно создать либо внедрен­ную диаграмму, либо лист диаграммы. Внедренная диаграмма - это объект, расположенный на листе и сохраняемый вместе с листом при сохранении книги. Лист диаграммы - лист книги, содержащий только диаграмму (вне­дренные диаграммы и листы диаграммы связываются с данными таблиц и обновляются при изменении данных в таблице). Для того чтобы построить диаграмму необходимо выделить ячейки, которые содержат данные (и кото­рые должны быть отражены на диаграмме), нажать кнопку Мастер диа­грамм и следовать инструкциям Мастера. Для создания диаграмм из не­смежных диапазонов нужно выделить первую группу ячеек, содержащих не­обходимые данные, далее, удерживая клавишу CTRL, выделить необходимые дополнительные группы ячеек и нажать кнопку Мастер диаграмм. При этом можно выбирать: тип диаграммы (Мастер диаграмм – на вкладках Стан­дартные или Нестандартные выбирается необходимый тип); изменять за­головки и другие тексты диаграммы - имена рядов данных или текст ле­генды, изменять подписи значений; отображать ряды данных на вспомога­тельной оси; изменять цвет, узор, линии и рамки на диаграмме и др.

В табличном процессоре Excel имеет средства для обработки массивов данных, так называемых списков (список представляет собой базу данных). Список - это упорядоченный набор данных, состоящий из строки заголовков и строк данных, которые могут быть числовыми и текстовыми (размер спи­ска ограничен размерами рабочего листа). Полями принято называть столбцы списка, а записями - строки. Excel будет считать таблицу списком, если ее формат удовлетворяет следующим условиям:

• список обязательно должен содержать строку заголовков;

• в каждом столбце должна содержаться однотипная информация;

• в списке не должно быть пустых строк;

• рекомендуется помещать список на отдельный лист.

Для работы со списками в Excel имеются следующие средства:

• пополнение списка с помощью формы;

• сортировка списка;

• фильтрация списка;

• подведение промежуточных итогов;

• создание итоговой сводной таблицы на основе данных списка.

При вводе данные можно непосредственно добавлять в ячейки списка, а можно воспользоваться специальной формой ввода (команда Форма из пункта меню Данные). В окне диалога выводятся значения записей. Окно содержит кнопки управления: Добавить, Удалить, Вернуть, Назад, Далее, Критерии, Закрыть. Для добавления новых записей необходимо воспользо­ваться кнопкой Добавить (появится пустая форма) и ввести значения в ка­ждое поле. Для редактирование записей с помощью формы необходимо найти нужную запись с помощью кнопок Назад и Далее (или клавишами управления курсором на полосе прокрутки формы) и ввести нужную правку. Форму ввода можно использовать не только для ввода данных. Она позво­ляет просматривать существующие записи, редактировать их, удалять и вы­борочно отображать данные по определенному критерию.

В MS Excel записи, организованные в списки, можно сортировать (по алфавиту, по убыванию, по возрастанию). Сортировка выполняется по соот­ветствующим полям (ключам). Чтобы отсортировать список необходимо:

• поместить курсор в тот столбец таблицы, по которому нужно выполнить сортировку;

• затем щелкнуть по кнопке Сортировать по возрастанию или Сортировать по убыванию.

Если необходимо выполнить сортировку по двум или нескольким столбцам, то надо произвести следующие действия:

• выбрать команду Сортировка из пункта меню Данные;

• в появившемся окне «Сортировка диапазона» в поле Сортировать по вы­брать название столбца и указать порядок сортировки;

• затем сделать то же самое для второго и третьего поля;

• нажать кнопку ОК.

Упорядочивание текстовых строк чаще всего выполняют по алфавиту. Но это не всегда удобно. Для того чтобы установить особый порядок сорти­ровки, например по годам или месяцам, необходимо выбрать команду Сор­тировка из пункта меню Данные. Затем следует нажать кнопку Параметры. В открывшемся диалоговом окне Параметры сортировки из раскрывающе­гося списка «Сортировка по первому ключу» выбрать полные названия меся­цев. Теперь при сортировке по полю «Год» и/или «Месяц» названия месяцев будут располагаться в привычном порядке.

Для поиска и отбора записей (обработки их по заданному значению или критерию) в MS Excel применяется метод, который называется Фильтрация. Для фильтрации данных применяются команды Автофильтр (для простых критериев) и Расширенный фильтр (для сложных критериев).

Перед тем как использовать Автофильтр, следует выделить любую ячейку списка. Затем подать из пункта меню Данные команды - Фильтр – Автофильтр. При включении Автофильтра возле имен полей списка появ­ляются кнопки со стрелками. При щелчке по любой из этих кнопок раскры­вается меню, при помощи которого можно отобрать все записи с заданным значением поля. Иногда стандартных условий Автофильтра оказывается не­достаточно, поэтому создают собственный Автофильтр. Для этого необхо­димо выполнить следующие действия:

• в списке выделить любую ячейку;

• для включения автофильтра подать из пункта меню Данные команды - Фильтр - Автофильтр (в каждом столбце в верхней ячейке появляется кнопка раскрывающегося списка);

• щелкнуть на кнопку раскрывающегося списка того столбца, имя которого будет использоваться в пользовательском фильтре;

• в списке операторов сравнения выбрать нужный, а в списке значений вы­брать значение поля-ключа;

• задать критерий выборки во втором поле таблицы;

• щелкнуть по кнопке ОК (на листе будут отображены записи, отвечаю­щие заданным критериям).

Часто для отбора нужной информации из списка бывает вполне доста­точно Автофильтра или пользовательского фильтра. Однако для решения сложной задачи приходится прибегать к помощи расширенной фильтрации. Расширенный фильтр гораздо гибче Автофильтра. С помощью расширенного фильтра можно:

• определить более сложный критерий фильтрации;

• помещать результат отбора данных в любое место рабочей книги;

• устанавливать вычисляемый критерий отбора.

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

В Excel меню Данные имеется команда Итоги для упорядочения списка и вывода промежуточных итогов. Для получения промежуточных итогов не­обходимо выполнить следующие действия:

• отсортировать список (сортировку следует проводить по тому полю спи­ска, при изменении значений которого должны рассчитываться проме­жуточные итоги);

• при необходимости можно выполнить фильтрацию;

• выбрать команду Итоги из пункта меню Данные;

• в диалоговом окне «Промежуточные итоги» следует установить критерии, функцию и другие параметры, которые будут использованы при расчете итогов.

В раскрывающемся списке При каждом изменении в... диалогового окна «Промежуточные итоги» имеются названия всех столбцов таблицы и следует выбрать заголовок того поля, по которому проводилась сортировка. В раскрывающемся списке Операция на выбор даются 11 функций, которые могут быть использованы для расчета итогов. В окне Добавить итоги по «галочкой» следует отметить названия полей списка, для которых надо под­вести итоги. При каждом повторном использовании команды Итоги можно выбирать новую функцию. Если необходимо, чтобы промежуточные итоги накапливались, то есть каждый последующий уровень итогов не уничтожал предыдущий, опция Заменить текущие итоги должна быть отключена.

Другим способом получения итоговой информации является Консоли­дация данных (одно из средств решения задачи объединения данных из раз­ных источников). При консолидации происходит обобщение однородных данных с выполнением определенных действий в зависимости от заданной функции консолидации: суммирования, определения минимального и макси­мального значения, или произведения (выполняется командой Консолида­ция из пункта меню Данные). Консолидация данных может выполняться двумя методами: по расположению и по именам полей (реквизитов). Консо­лидация данных по расположению выполняется над данными, расположен­ными на разных листах, но в таблицах с одинаковой структурой. Консолида­ция по именам (реквизитам) выполняется над данными таблиц неодинаковой структуры и расположения, но консолидируемые данные обязательно должны иметь одинаковые имена полей сводимых диапазонов. Для консоли­дации данных необходимо определить область расположения консолидируе­мой информации. Это может быть любой рабочий лист или любая рабочая книга. Затем на листе для консолидации данных следует активизировать ячейку начала диапазона ячеек с итогами.

В MS Excel имеется возможность построения динамической таблицы итоговых данных (извлеченных или рассчитанных на основе информации, со­держащейся в списках), так называемой Сводной таблицы. Для ее создания следует установить курсор в одну из ячеек списка и выполнить команду Сводная таблица из пункта меню Данные. На экране появится окно Мас­тера сводных таблиц и диаграмм. На первом шаге определяется источник данных. На втором – Мастер просит уточнить диапазон, в котором располо­жены исходные данные. На третьем шаге диалогового окна «Мастер сводных таблиц и диаграмм» следует щелкнуть по кнопке Макет. В открывшемся окне поля списка представлены в виде кнопок, и надо перетащить эти кнопки в соответствующие области макета сводной таблицы.

Страница - значения поля, помещенного в эту область, используются в качестве заголовков страниц сводной таблицы. Строка - значения поля, по­мещенного в эту область, используются в качестве заголовков строк сводной таблицы. Данные - поле (поля), для которого подводится итог в сводной таб­лице. Столбец - значения поля, помещенного в эту область, используются в качестве заголовков столбцов сводной таблицы. В каждую область можно перетащить любое количество полей, но все поля использовать не обяза­тельно (если случайно кнопка попала не в ту область, ее можно просто вы­вести за пределы макета). По умолчанию при перетаскивании кнопки в об­ласть Данные будет применена функция Сумм, если поле содержит число­вые значения, и функция Счет - в случае нечисловых значений. Если дважды щелкнуть по полю в области данных, функцию можно будет изменить. Соз­данную сводную таблицу можно изменять и настраивать, пока она не будет выглядеть необходимым образом (очень полезной здесь будет панель инст­рументов Сводные таблицы и если она не появилась автоматически, сле­дует выполнить команды Панели инструментов - Сводные таблицы из пункта меню Вид). При этом можно изменять структуру Сводной таблицы, добавлять и удалять поля Сводной таблицы, изменять параметры полей Сводной таблицы, производить форматирование Сводной таблицы, а также создавать диаграммы по Сводной таблице и др.