Структуризация, фильтрация, группировка данных

При работе с электронными таблицами часто возникает необходимость их объединения.

Существуют следующие инструменты объединения:

• Организация межтабличных связей(структуризация). Связи между таблицами осуществляются путем использования внешних ссылок, адресов ячеек, содержащих кроме имени столбца и номера строки имя файла данные из которых используются [Exam.xls] C2. [Exam.xls]- имя файла,С2- ячейка. Таблица, на которой нет внешней ссылки, рассматривается как дополнительная, а таблица, на которой есть внешние ссылки, считается основной. Обе таблицы должны находиться в 1 папке или 1 каталоге. Между 2 таблицами могут существовать двух сторонние связи (перекрестные ссылки).

• Консолидация электронной таблицы и их частей. Помимо создания межтабличных связей путем указания имен файлов, многие электронные таблицы предлагают пользователю специальный режим консолидации. Этот режим содержит необходимые команды, для объединения таблиц и их частей, расположенных как на 1 листе, так и на разных листах и в разных рабочих книгах. При выполнении ряда работ у пользователя Microsoft Excel может быть создано несколько однотипных таблиц в одном файле или в нескольких книгах.

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

• Объединение файлов. Команда объединение файлов имеет 3 формы, используемые для:

· копирования,

· суммирования,

· вычитания данных из исходной таблицы в объединенную.

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

Фильтрация данных в Excel

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

В Excel предусмотрено три типа фильтров:

1. Автофильтр – для отбора записей по значению ячейки, по формату или в соответствии с простым критерием отбора.

2. Срезы – интерактивные средства фильтрации данных в таблицах.

3. Расширенный фильтр – для фильтрации данных с помощью сложного критерия отбора.

Автофильтр

Включение автофильтра:

1. Выделить одну ячейку из диапазона данных.

2. На вкладке Данные [Data] найдите группу Сортировка и фильтр [Sort&Filter].

3. Щелкнуть по кнопке Фильтр [Filter].

Фильтрация записей:

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

2. Выбрать условие фильтрации.

Варианты фильтрации данных

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

1. При выборе опции Числовые фильтры появятся следующие варианты фильтрации: равно, больше, меньше, Первые 10… [Top 10…] и др.

2. При выборе опции Текстовые фильтры в контекстном меню можно отметить вариант фильтрации содержит..., начинается с… и др.

3. При выборе опции Фильтры по дате варианты фильтрации – завтра, на следующей неделе, в прошлом месяце и др.

4. Во всех перечисленных выше случаях в контекстном меню содержится пункт Настраиваемый фильтр… [Custom…], используя который можно задать одновременно два условия отбора, связанные отношением И [And] – одновременное выполнение 2 условий, ИЛИ [Or] – выполнение хотя бы одного условия.

Если данные после фильтрации были изменены, фильтрация автоматически не срабатывает, поэтому необходимо запустить процедуру вновь, нажав на кнопку Повторить [Reapply] в группе Сортировка и фильтр на вкладке Данные.

Отмена фильтрации

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

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

Чтобы быстро снять фильтрацию со всех столбцов необходимо выполнить команду Очистить на вкладке Данные

Срезы

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

Создание срезов

В Excel 2010 срезы можно использовать для сводных таблиц, а в версии 2013 существует возможность создать срез для любой таблицы.

Для этого нужно выполнить следующие шаги:

1. Выделить в таблице одну ячейку и выбрать вкладку Конструктор [Design].

 

2. В группе Сервис [Tools] (или на вкладке Вставка [Insert] в группе Фильтры [Filters]) выбрать кнопку Вставить срез [Insert Slicer].

В диалоговом окне отметить поля, которые хотите включить в срез и нажать OK.

Форматирование срезов

1. Выделить срез.

2. На ленте вкладки Параметры [Options] выбрать группу Стили срезов [Slicer Styles], содержащую 14 стандартных стилей и опцию создания собственного стиля пользователя.

3. Выбрать кнопку с подходящим стилем форматирования.

Чтобы удалить срез, нужно его выделить и нажать клавишу Delete.

Расширенный фильтр

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

Задание условий фильтрации

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

2.

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

3. На вкладке Данные [Data] найти группу команд Сортировка и фильтр [Sort&Filter] и выбрать команду Дополнительно [Advansed].

4. В диалоговом окне Расширенный фильтр выбрать вариант записи результатов: фильтровать список на месте [Filter the list, in-place] или скопировать результат в другое место [Copy to another Location].

5. Указать Исходный диапазон [List range], выделяя исходную таблицу вместе с заголовками столбцов.

6. Указать Диапазон условий [Criteria range], отметив курсором диапазон условий, включая ячейки с заголовками столбцов.

7. Указать при необходимости место с результатами в поле Поместить результат в диапазон [Copy to], отметив курсором ячейку диапазона для размещения результатов фильтрации.

8. Если нужно исключить повторяющиеся записи, поставить флажок в строке Только уникальные записи [Unique records only].

Сводные таблицы

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

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

Что же такое сводные таблицы, и зачем они нужны? Мы часто сталкиваемся с ситуациями, когда у нас есть много разнообразных данных (которые можно назвать статистическими), но нас интересуют какие-то общие выводы или промежуточные итоги.

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

За 17 дней продаж у нас получилась большая таблица на 350 записей. Но эта таблица не решает наших проблем. Нам необходимо узнать объемы продаж в денежном и количественном выражении по датам и по отдельным магазинам, но как это сделать? Сортировать таблицу и суммировать отдельные её части? Это требует времени, а завтра поступят новые данные, и всю работу нужно будет снова повторить.

Вот тут нам может помочь сводная таблица. С помощью простого диалогового окна мы создаём нашу первую сводную таблицу. В этой таблице мы группируем данные по столбцам Дата и Точка продажи, а так же указываем, что нужно суммировать данные из столбцов Объем продаж, шт. и Сумма выручки.

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

Таблица сразу же отобразит нужные нам результаты:

Этот пример наглядно демонстрирует преимущества сводных таблиц, к которым относятся:

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

· возможность консолидировать данные из разных таблиц и даже из разных источников;

· возможность оперативно дополнять данные сводной таблицы, просто расширив исходную таблицу и немного изменив настройки сводной.

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

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

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

Практическое задание 1: Выполнить консолидацию данных в EXCEL

1.Есть 4 файла, одинаковых по структуре. Допустим, поквартальные итоги продаж шкур животных.

Нужно сделать общий отчет с помощью «Консолидации данных».

2.Сначала проверим, чтобы

• макеты всех таблиц были одинаковыми;
• названия столбцов – идентичными (допускается перестановка колонок);
• нет пустых строк и столбцов.

3.Диапазоны с исходными данными нужно открыть.

4. Для консолидированных данных отводим новый лист или новую книгу. Открываем ее. Ставим курсор в первую ячейку объединенного диапазона.

Правее и ниже этой ячейки должно быть свободно. Команда «Консолидация» заполнит столько строк и столбцов, сколько нужно.

5.Переходим на вкладку «Данные». В группе «Работа с данными» нажимаем кнопку «Консолидация».

Открывается диалоговое окно вида:

На картинке открыт выпадающий список «Функций». Это виды вычислений, которые может выполнять команда «Консолидация» при работе с данными. Выберем «Сумму» (значения в исходных диапазонах будут суммироваться).

6.Переходим к заполнению следующего поля – «Ссылка».

Ставим в поле курсор. Открываем лист «1 квартал». Выделяем таблицу вместе с шапкой. В поле «Ссылка» появится первый диапазон для консолидации. Нажимаем кнопку «Добавить»

7.Открываем поочередно второй, третий и четвертый квартал – выделяем диапазоны данных. Жмем «Добавить».

Таблицы для консолидации отображаются в поле «Список диапазонов».

8.Чтобы автоматически сделать заголовки для столбцов консолидированной таблицы, ставим галочку напротив «подписи верхней строки». Чтобы команда суммировала все значения по каждой уникальной записи крайнего левого столбца – напротив «значения левого столбца». Для автоматического обновления объединенного отчета при внесении новых данных в исходные таблицы – напротив «создавать связи с исходными данными».

9.Если вносить в исходные таблицы новые значения, сверх выбранного для консолидации диапазона, они не будут отображаться в объединенном отчете. Чтобы можно было вносить данные вручную, снимите флажок «Создавать связи с исходными данными».

10.Для выхода из меню «Консолидации» нажимаем ОК. Консолидированный отчет представляет собой структурированную таблицу. Нажмем «плюсик» в левом поле – появятся значения, на основе которых сформированы итоговые суммы по количеству и выручке.

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

№	Дата	Точка продажи	Название	Объем продаж	Цена за 1 кг (руб).	Сумма выручки
1	14.05.2017	Округ №1	Медведь	4	6000
2	14.05.2017	Округ №1	Олень	3	9000
3	14.05.2017	Округ №1	Волк	2	5000
4	14.05.2017	Округ №2	Лиса	1	3000
5	14.05.2017	Округ №2	Кабарга	2	7000
6	15.05.2017	Округ №1	Олень	3	9000
7	15.05.2017	Округ №1	Волк	6	5000
8	15.05.2017	Округ №2	Медведь	7	6000
9	15.05.2017	Округ №2	Медведь	8	6000
10	16.05.2017	Округ №2	Кабарга	5	7000
11	16.05.2017	Округ №2	Кабарга	6	7000
12	16.05.2017	Округ №2	Кабарга	3	7000
13	16.05.2017	Округ №2	Кабарга	2	7000
14	17.05.2017	Округ №1	Лиса	1	3000
15	17.05.2017	Округ №1	Лиса	2	3000
16	17.05.2017	Округ №2	Лиса	3	3000
17	17.05.2017	Округ №2	Волк	5	5000
18	18.05.2017	Округ №1	Лиса	4	3000
19	18.05.2017	Округ №1	Волк	3	5000
20	18.05.2017	Округ №2	Лиса	2	3000

Практическое задание 3: По предыдущей таблице создать автофильтр по округу №1и расширенный фильтр по продаже лицензий на отстрел лис 16.05.2017 в EXCEL.

Вопросы и задания для контроля знаний. 1. Какие инструменты объединения таблиц вы знаете? 2. Что представляет собой консолидация данных? 3. Что представляет собой фильтрация данных? 4. Какие виды фильтров вы знаете? 5. Охарактеризуйте каждый вид фильтров 6. Для чего применяется сводная таблица?

7. Как создать сводную таблицу?

Тема 24 «Работа с MS Excel. Решение задач оптимизации данных»

Цель – формирование практических умений и навыков решения задач оптимизации данных.

План