Занятие 4

СОРТИРОВКА ДАННЫХ. ФИЛЬТРЫ

Сортировка – расположение данных таблицы в строго определенной последовательности.

Виды сортировок и способы их выполнения:

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

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

Здесь же можно задать многокаскадную сортировку (Затем по; В последнюю очередь, по). Это означает, что если по первому сортируемому значению некоторые строки совпадают, то они между собой будут отсортированными по указанному в пункте Затем по столбцу. Третий столбец указывается, если совпадения будут по обоим первым столбцам.

Кнопка Параметры… вызывает диалоговое окно Параметры сортировки, которое позволяет определить, что будет подвергнуто сортировке – строки или столбцы. По умолчанию сортируются строки таблицы.

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

Фильтрация данных с помощью автофильтра:

- выделить диапазон, для которого будет создан фильтр;

- в меню Данные выбрать пункт Фильтр – Автофильтр;

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

- указать требуемые значения или выбрать строку Условие… и задать критерий выборки в диалоговом окне Пользовательский автофильтр.

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

Ø значение, которое может выбираться из списка или содержать шаблонные символы подстановки * и?;

Ø оператор сравнения.

Способы восстановления всех строк исходной таблицы:

- щелкнуть по кнопке и в раскрывшемся списке выбрать Все;

- выбрать в меню Данные пункт Фильтр – Отобразить все.

Отмена режима автофильтра: выбрать в меню Данные пункт Фильтр – Автофильтр.

Фильтрация данных с помощью расширенного фильтра:

- сформировать специальную область для задания условий фильтрации данных – диапазон условий отбора (интервал критериев). Диапазон условий должен содержать строку с заголовками столбцов и несколько строк для задания условий отбора. Если условия вводятся в одной строке для разных столбцов, то они считаются связанными логической связкой И. Если критерии отбора записываются в разных строках, то они считаются связанными логической связкой ИЛИ. Между значениями условий отбора и таблицей должна находиться как минимум одна пустая строка;

- установить курсор внутри таблицы;

- в меню Данные выбрать пункт Фильтр – Расширенный фильтр;

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

Отмена режима расширенного фильтра: выбрать в меню Данные пункт Фильтр – Отобразить все.

Задания:

1. Запустите MS Excel.

2. В файле Склад создайте и заполните следующую таблицу:

3. В последнем столбце произведите вычисления по формуле: Итого=Цена*Количество.

4. Используя простую сортировку, отсортируйте данные таблицы по полю Цена.

5. Отсортируйте наименования организаций в алфавитном порядке, внутри каждой организации наименование товара в алфавитном порядке и внутри каждого наименования товара – по возрастанию количества проданного товара. Для этого:

- установите курсор в область таблицы;

- выполните команду Данные – Сортировка;

- в первом уровне сортировки выберите поле Организация, во втором — Товар, в третьем — Кол-во и нажмите OK.

6. Скопируйте таблицу с Листа 1 на Лист 2 и назовите новый лист Автофильтр.

7. Создайте для первого столбца автофильтр.

8. Выберите из таблицы только те строки, которые относятся к АОЗТ «Белокуриха».

9. Отключите автофильтр.

10. Создайте автофильтр для всех столбцов таблицы.

11. Используя автофильтр, выведите на экран записи, содержащие организацию АОЗТ «Белокуриха», где в столбце «Товар» присутствует «хлеб», т.е. осуществите выборку по двум полям.

12. Восстановите все строки исходной таблицы.

13. Используя автофильтр, выведите на экран записи, содержащие организацию АОЗТ «Белокуриха», в которых цена товара не превышает 16.

14. Восстановите все строки исходной таблицы.

15. Используя автофильтр, выведите на экран записи, содержащие колхоз «Восток» и дату покупки товара в промежутке после 2 марта до 13 июня.

16. Скопируйте таблицу с Листа 1 на Лист 3 и дайте имя листу Расширенный фильтр.

17. Используя расширенный фильтр, выведите на экран записи, содержащие организацию АОЗТ «Белокуриха», где в столбце «Товар» присутствует «Хлеб», т.е. осуществите выборку по двум полям. Для этого:

- ниже таблицы, оставив пустые 2 строки, скопируйте строку заголовка таблицы;

- в следующей строке сформируйте критерий отбора записей: в столбец Организация введите АОЗТ «Белокуриха», в столбец Товар — «Хлеб»;

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

- выполните команду Данные – Фильтр – Расширенный фильтр…;

- в появившемся диалоговом окне в строке Исходный диапазон появится диапазон, соответствующий расположению анализируемой таблицы;

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

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

18. Используя расширенный фильтр, выведите на экран записи, содержащие организацию АОЗТ «Белокуриха», в которых цена товара не превышает 16. При этом в диапазоне условий отбора в поле Цена укажите условие: < =16.

19. Сохраните файл Склад и закройте его.

20. Откройте файл Страна.xls.

21. Создайте новую рабочую книгу.

22. Добавьте в новую рабочую книгу еще три рабочих листа и сохраните книгу под именем Занятие 4.

23. Скопируйте таблицу из файла Страна.xls в файл Занятие 4 на Лист1.

24. Закройте файл Страна.xls.

25. Скопируйте Лист1 на все остальные рабочие листы.

26. Расположите на первом рабочем листе страны по алфавиту.

27. На втором рабочем листе с помощью автофильтра выберите страны с площадью более 5000 тыс. км².

28. На третьем рабочем листе с помощью автофильтра выберите страны с населением меньше 150 млн. чел.

29. На четвертом рабочем листе с помощью автофильтра выберите страны с плотностью населения от 100 до 300 чел./км².

30. На втором рабочем листе отмените режим фильтрации.

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

32. Сохраните файл и закройте MS Excel.

Задание для самостоятельной работы:

1. В файле Телевизоры создайте на первом рабочем листе следующую таблицу и

a. используя расширенный фильтр, отобразите на втором рабочем листе все телевизоры Sony с размером экрана 37 и Samsung – с размером экрана 51;

b. используя автофильтр, отобразите на первом рабочем листе только те строки, которые относятся к телевизорам, начинающимся на букву S или P.

ЗАНЯТИЕ 5

АНАЛИЗ И ОБОБЩЕНИЕ ДАННЫХ.

ПРОМЕЖУТОЧНЫЕ ИТОГИ В СПИСКАХ.

КОНСОЛИДАЦИЯ ДАННЫХ. СВОДНЫЕ ТАБЛИЦЫ

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

При подведении промежуточных итогов могут быть автоматически вычислены: сумма, количество значений, среднее, максимум, минимум, произведение и т.д.

Значения общих и промежуточных итогов пересчитываются автоматически при каждом изменении данных.

Автоматическое подведение итогов осуществляется следующим образом:

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

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

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

- выбрать столбец, содержащий группы, по которым необходимо подвести итоги (столбец, по которому проводилась сортировка), из списка При каждом изменении в;

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

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

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

Символы структуры:

- Номера уровней. Щелчок по номеру уровня позволяет скрыть детали уровней с большими номерами и показать детали этого уровня и всех уровней с меньшими номерами. Данные первого уровня не могут быть скрыты. Максимальное количество уровней – 8;

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

- Символ «+» маркирует закрытые уровни. Щелчок по нему позволяет показать детали соответствующего уровня;

-

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

Процедура консолидации данных:

1. Выделить ячейку, определяющую положение итоговой таблицы.

2. Выбрать пункт Консолидация меню Данные.

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

4. Установить курсор в строку Ссылка.

5. Выделить первую исходную область.

6. Щелкнуть по кнопке Добавить окна Консолидация и нажать клавишу Del.

7. Повторить действия 4-6 для остальных исходных областей.

8. Нажать кнопку OK.

Окно Консолидация:

Кнопка Обзор … позволяет выбирать исходные области из других рабочих листов и файлов.

Группа флажков Использовать в качестве имен предназначена для того, чтобы сообщать программе, каким образом (в строках или столбцах) размещены данные в исходных областях.

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

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

Сводную таблицу можно создать на основе:

- таблиц MS Excel, организованных в виде списка или базы данных;

- таблиц, полученных в результате консолидации данных;

- других сводных таблиц;

- внешних источников данных.

Создание и модификация сводных таблиц выполняется с помощью Мастера сводных таблиц и диаграмм.

Способы запуска Мастера сводных таблиц и диаграмм:

- пункт Сводная таблица… меню Данные;

- кнопка Мастер сводных таблиц на Панели инструментов Сводные таблицы.

Этапы работы с Мастером сводных таблиц и диаграмм:

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

- во втором окне мастера указать диапазон, в котором содержатся исходные данные;

- в третьем окне мастера выбрать место размещения сводной таблицы, с помощью кнопки Параметры… установить параметры вывода информации, с помощью кнопки Макет… сформировать структуру сводной таблицы и нажать кнопку Готово.

Окно макета сводной таблицы:

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

Процедура создания макета сводной таблицы:

- перетащить поля, в которых нужно произвести отбор нужных записей (фильтрацию), в область Страница. Область Страница может оставаться незаполненной;

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

В области Страница, Строка и Столбец каждое поле может помещаться только один раз.

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

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

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

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

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

- щелкнуть мышью по сводной таблице;

- на Панели инструментов Сводные таблицы щелкнуть по кнопке Сводная таблица и выбрать в раскрывающемся списке команду Формулы – Вычисляемое поле;

- щелкнуть по кнопке Добавить, а затем – по кнопке ОК;

- расположить вычисляемое поле в требуемом месте отчета.

Обновление данных в сводной таблице:

- выделить любую ячейку сводной таблицы;

- щелкнуть по кнопке Обновить данные на Панели инструментов Сводные таблицы.

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

Удаление сводной таблицы:

- установить курсор в любую ячейку сводной таблиц;

- на Панели инструментов Сводные таблицы открыть список Сводная таблица и выбрать в нем команду Выделить – Таблица целиком при активизированной кнопке Разрешить выделение;

- в меню Правка выбрать пункт Очистить – Все.

Задания:

1. Откройте файл Склад.xls.

2. Вставьте в таблицу два новых листа – Лист4 и Лист5.

3. Скопируйте таблицу с Листа1 на Лист4 и Лист5.

4. Перейдите на Лист4.

5. Осуществите автоматическое подведение итогов в таблице по столбцу Итого для каждой организации. Для этого:

- отсортируйте данные в столбце Организации таблицы по алфавиту;

- выделите какую-нибудь ячейку внутри таблицы;

- выберите пункт Итоги… меню Данные;

- в раскрывающемся списке При каждом изменении в выберите Организация;

- из списка Операция выберите Сумма;

- в списке Добавить итоги установите флажок рядом с полем Итоги;

- установите флажки Заменить текущие итоги и Итоги под данными;

- нажмите кнопку ОК.

6. Используя символы структуры, приведите таблицу к следующему виду:

7. Перейдите на Лист5.

8. Осуществите автоматическое подведение итогов в таблице по столбцу Количество для каждого товара.

9. Сохраните и закройте текущий файл.

10. Создайте новую рабочую книгу и дайте ей имя Хлеб.

11. Создайте в рабочей книге Хлеб следующую таблицу:

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

- выделите ячейку, определяющую положение итоговой таблицы, например, А22;

- выберите пункт Консолидация… меню Данные;

- в окне Консолидация в списке Функция укажите функцию Сумма;

- установите курсор в строку Ссылка;

- выделите первую исходную область A4: D7;

- щелкните в окне Консолидация кнопку Добавить и нажмите клавишу Del;

- повторите предыдущие три действия для диапазонов A9: D13, A15: D18;

- установите флажок Использовать в качестве имен значения левого столбца и нажмите ОК.

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

14. Сохраните и закройте текущий документ.

15. Создайте в файле Сводная таблица следующую таблицу:

16. На основании имеющихся данных постройте сводную таблицу. Для этого:

- вызовите Мастер сводных таблиц;

- на первом шаге мастера установите флажки Создать таблицу на основе данных, находящихся в списке или базе данных Microsoft Excel и Вид создаваемого отчета сводная таблица;

- на втором шаге мастера укажите диапазон $A$1: $E$11;

- на третьем шаге установите флажок Поместить таблицу в новый лист, создайте макет будущей таблицы (кнопка Макет…): поместите мышью кнопку Точка в область Страница, кнопку Наименование – в область Строка, кнопку Цена – в область Столбец, кнопку Сумма – в область Данные, нажмите ОК, а затем кнопку Готово.

В результате на Листе 4 будет таблица вида:

17. Используя фильтры сводной таблицы (кнопки ), выведите на экран данные по ржаному и городскому хлебу Булочной №1.

18. Перетащите кнопку Точка в область Строка. Таблица приняла вид:

19. Сохраните текущий документ и закройте MS Excel.

Задания для самостоятельной работы:

1. Для данной таблицы осуществите автоматическое подведение итогов по столбцу Количество для каждого наименования.

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