Список – это табличная база данных, которая содержится в листе рабочей таблицы. Такая база данных состоит из имен полей, которые расположены в первой строке и информационных записей, находящихся в следующих строках. В списке не должно быть пустых столбцов или пустых строк. Над списками можно выполнять следующие операции:
· Фильтрация данных в списке – это выбор данных по заданному критерию (условию). Имеются две разновидности этой команды, задаваемые параметрами: Автофильтр и Расширенный фильтр. При выборе команды Данные/сортировка и фильтр/Фильтр автоматически устанавливается автофильтр..Для обращения к расширенному фильтру следует выбрать команду Данные/сортировка и фильтр/дополнительно
· Сортировка списка. Сортировка – это расположение данных в определенном порядке по возрастанию или убыванию. Сортировка выполняется командой Данные/ сортировка и фильтр/ Сортировка.
· Вставка формул для подведения промежуточных итогов.
· Создание с помощью сводной таблицы итоговой таблицы данных списка, отфильтрованного по определенным критериям.
· Создание с помощью сводной таблицы итоговой таблицы данных списка.
Подведение промежуточных итогов в смежных диапазонах осуществляется через команду меню Данные/Структура/промежуточные итоги.
Порядок выполнения работы
1. Ввести список «Периферия», представленный ниже.
Товар | Тип | Наименование | Цена | Кол- во | Сумма |
Сканер | Листовой | Paragon Page Easy | |||
Сканер | Планшетный | Paragon 1200 SP | |||
Сканер | Планшетный | ScanExpress A3 P | |||
Принтер | Струйный | Epson Stylus Photo 700 | |||
Принтер | Лазерный | HP LaserJet 4000 | |||
Принтер | Лазерный | HP LaserJet Color 8500 | |||
Сканер | Листовой | Paragon Page 630 | |||
Сканер | Планшетный | Paragon 800IIEP | |||
Принтер | Матричный | Epson LX-1050+ | |||
Принтер | Лазерный | HP LaserJet 5000 | |||
Сканер | Планшетный | ScanExpress 6000 SP | |||
Принтер | Струйный | Epson Stylus-1500 | |||
Принтер | Матричный | Epson LQ-2170 | |||
Принтер | Матричный | Epson LQ-100 | |||
Принтер | Струйный | Epson Stylus-1520 |
1. Подсчитайте сумму для каждого товара по формуле: Цена*Кол-во.
2. Отсортируйте записи по Товару в порядке возрастания, по полю Тип в порядке возрастания командой Данные/Сортировка.
3. Используя автофильтр Данные/сортировка и фильтр/Фильтр, отберите записи:
· По полю Тип: Лазерные принтеры. Для этого перейдите на поле Тип. Раскройте список фильтрации и выберите Лазерные. На экране остались только данные по лазерным принтерам. Отмените фильтр (Данные / сортировка и фильтр /Очистить).
· По полю Товар: Сканеры. Выполнить самостоятельно! Отмените использование фильтра.
· По полю Кол-во: больше 3. Раскройте список фильтрации и выберите Числовые фильтры. Выберите из списка «больше». В соседнем поле задайте значение 3. ОК.
Отмените использование фильтра.
4. используя Расширенный фильтр Данные /Сортировка и фильтр/дополнительно найдите сканеры, которых больше 1. Для этого под таблицей запишем условия отбора Товар – Сканер, Кол – во > 1. Выполним команду Данные /Сортировка и фильтр/дополнительно. Выполним обработку, как показано на рисунке ниже.
Результат:
Самостоятельно с помощью расширенного фильтра выберите Лазерные принтеры с ценой больше 1500 руб.
5. Создайте промежуточные итоги командой Данные/структура/промежуточные итоги.
5.1. Найдем суммы, затраченные отдельно на покупку всех принтеров и всех сканеров. Для этого:
1) Отсортируйте таблицу по товару.
2) В меню Данные / структура выберите промежуточные итоги. Откроется диалоговое окно Промежуточные итоги.
3) Для того, чтобы подвести итоги по каждому товару (отдельно принтеры и отдельно сканеры), в списке При каждом изменении в выберите Товар
4) Убедитесь, что в окне Операция выбрана Сумма
5) Для того, чтобы просуммировать показатели количества товара и сумм, затраченных на покупку, установите флажки в поле Добавить итоги по напротив строк Кол-во и Сумма.
6) Проверьте, что напротив строк Заменить текущие итоги и Итоги под данными установлены флажки и нажмите ОК.
7) Таким образом, вы получите итоговые значения количества и суммы для каждого типа товара.
8) Для удаления промежуточных итогов выберите команду Данные/структура/промежуточные итоги и щелкните кнопку Убрать все. Таблица вернется в исходное состояние.
5.2. Найдите среднее значение цены отдельно для всех принтеров и всех сканеров, а также общее среднее значение цены. Для этого:
1) Отсортируйте таблицу по товару.
2) Данные è Промежуточные итоги:
3) При каждом изменении è Товар
4) Операция èСреднее
5) Добавить итоги по èЦена
6) В конце не забудьте убрать промежуточные итоги.
5.3. Вычислите число разновидностей принтеров и сканеров. Для этого
1) Отсортируйте таблицу по товару.
2) Данные è Промежуточные итоги:
3) При каждом изменении è Товар
4) Операция èКол-во значений
5) Добавить итоги по èНаименование
6) В конце не забудьте убрать промежуточные итоги.
5.4. Самостоятельно вычислите среднее значение сумм, потраченных на покупку всех принтеров и всех сканеров.
Покажите результат преподавателю!!!
В конце не забудьте убрать промежуточные итоги.
5.5. Вычислите суммы, потраченные на покупку каждого типа товара (каждой разновидности принтеров и сканеров).
1) Отсортируйте таблицу по типу принтеров.
2) Данные è Промежуточные итоги:
3) При каждом изменении è Тип
4) Операция èСумма
5) Добавить итоги по èСумма
6. Часто при составлении итогового отчета нужно видеть только итоги, а остальные записи скрыть. Для этого в колонках с кнопками 1 2 3 можно поменять знак - на +. Такое можно провести для каждого уровня.
Воспользуйтесь результатами предыдущего задания.
1) Скройте результаты второго уровня: Щелкните по кнопке 2.
2) Для возврата таблицы в исходный вид щелкните по кнопке того уровня, который вы хотите показать (кнопка 3)
3) Попробуйте по очереди скрывать отдельные записи второго уровня (нажимайте на кнопку -)
4) Для возврата - на кнопку +.
5) Попробуйте поработать с кнопкой 1.
6) Верните таблицу в исходное состояние.
7) Уберите промежуточные итоги.
Контрольные вопросы
1. Как работать с формой?
2. Какие виды фильтров вы знаете? Как работает расширенный фильтр?
3. Для чего нужна операция Промежуточные итоги?
4. Что такое структура? Какими способами создается структура?
Лабораторная работа №6
Подведение итогов для данных с помощью сводных таблиц и операции консолидации
Цель: Научиться использовать сводные таблицы и операцию консолидации