Задание 1. Определить, имеются ли в предложенном списке (см. рис. 42) белые машины, год выпуска которых больше 2000 и цена которых находится в диапазоне от 3000 до 16 000 у. е., или черные Мерседесы, пробег которых более 23 000 км, но менее 150 000 км.
Рис. 42 Список MS Excel
Решение
1. Откройте список, подлежащий фильтрации (список располагается в диапазоне А1:J35, строка заголовка— в диапазоне A1:J1)
2. Сформируйте диапазон критериев для расширенного фильтра в соответствии с рис. 43.
Рис. 43. Диапазон критериев для расширенного фильтра к задаче про белые и черные машины
3. Выполните команду Данные | Фильтр | Расширенный фильтр.
4. Отфильтрованные данные приведены на рис. 44.
Рис. 44. Данные к задаче про белые и черные машины, отобранные расширенным фильтром
Задание 2. Определить, имеются ли в списке (см. рис. 42) машины, год выпуска которых больше 2000 и пробег которых более 100 00 км, но менее 100 000 км, или черные Мерседесы, цена которых более 20 000 у. е., но менее 30 000 у. е.
Решение
1. Откройте список, подлежащий фильтрации (список располагается в диапазоне А1:JЗ5, строка заголовка— в диапазоне A1:J1)
2. Сформируйте вычисляемый критерий для расширенного фильтра в диапазоне МЗ:М4. В ячейку МЗ введите Условие. В ячейку М4 введите формулу:
=ИЛИ(И(G2>10000;G2<100000;D2>1990);И(C2="Мерседес";F2="Черный";H2>20000;H2<30000))
3. Выполните команду Данные | Фильтр | Расширенный фильтр.
4. Отфильтрованные данные представлены на рис. 45.
Рис. 45. Данные к задаче о пробеге, отобранные расширенным фильтром
Задание 3. Определить автомобили белого или красного цвета, цена которых меньше средней цены для всех автомобилей и пробег которых больше либо равен среднему пробегу для всех автомобилей (см. рис. 42).
Решение
1. Откройте список, подлежащий фильтрации (список располагается в диапазоне А1:JЗ5, строка заголовка— в диапазоне A1:J1)
2. Сформируйте вычисляемый критерий для расширенного фильтра в диапазоне L1:L2. В ячейку L1 введите Условие. В ячейку L2 введите формулу:
=И(ИЛИ(Г2="белый";Г2="красный");Н2<СРЗНАЧ($Н$2:$Н$133); G2 >=СРЗНАЧ($G$ 2:$G$13 3))
3. Выполните команду Данные | Фильтр | Расширенный фильтр.
Лабораторная работа №10 Автоматическое подведение итогов в MS Excel.
Итоги необходимы для создания разнообразных отчетов и для обобщения большого количества однотипной информации. Итоги подразделяются на:
• простые промежуточные;
• сложные промежуточные;
• связанные с вычислением частичных сумм (используется мастер частичных сумм).
Задание 1. Дан список со следующими полями (рис. 46): № пп, Продавец, Товар, Номер партии, Цена, Количество, Итого, Дата продажи, Покупатель. Определить количество товаров, проданных
конкретным продавцом за конкретную дату.
Решение
1. Выделите список (или — установите в список указатель ячейки) и проведите сортировку (команда Данные | Сортировка) сначала — по полю Продавец, затем — по полю Дата продажи (рис. 47).
Рис. 46. Список продаж
2. Примените команду Данные | Итоги. В окне Промежуточные итоги установите параметры в соответствии с рис. 48: для получения верхнего (первого) уровня итогов — общее количество товаров, проданных конкретным продавцом.
Рис. 47.Сортировка списка
Рис. 48. Окно Промежуточные итоги для получения итогов по полю Продавец
3. Для получения второго уровня итогов поместите указатель ячейки в список с полученными итогами, затем выполните команду Данные | Итоги, установив в окне Промежуточные итоги параметры в соответствии с рис. 49.
Рис.49. Окно Промежуточные итоги для получения итогов по полю Дата продажи
4. Полученные промежуточные итоги представлены на рис. 50.
Рис. 50.Вложенные промежуточные итоги
Консолидация данных
Консолидация предназначается для обобщения однородных данных. Ее осуществление предполагает использование следующей методики:
1. Указать местоположение будущих консолидированных данных.
2. Выбрать команду Данные | Консолидация.
3. В открывшемся окне указать диапазоны данных, подлежащие консолидации.
4. Указать способ консолидации:
• согласно расположению в диапазоне — сняты все флажки области Использовать в качестве имен;
• согласно заголовкам строк и столбцов— установлены
флажки подписи верхней строки и значения левого столбца.
5. Выбрать тип консолидации, т. е. указать, какая операция будет проводиться с консолидируемыми данными.
6. При необходимости указать добавление структуры — установить флажок Создавать связи с исходными данными.
Задание 1. Объединить данные о количестве и стоимости проданных товаров в сети магазинов, которые представлены в виде списка со следующими полями (рис. 51): Товар, Стоимость, Количество, расположены на листе 2, листе 4 и листе 5.
Решение: Следуя рекомендациям методики проведения консолидации, заполните окно
Рис. 51.Данные о реализованных товарах
Консолидация в соответствии с данными рис. 52. Объединенные данные представлены на рис. 53.
Сводные таблицы
Сводные таблицы представляют собой средство для группировки, обобщения и анализа данных, находящихся в списках MS Excel или в таблицах, созданных в других приложениях. Внешне сводные
Рис. 52. Ввод данных в окно Консолидация
Рис. 53. Представление консолидированных данных
Сводные таблицы
Сводные таблицы представляют собой средство для группировки, обобщения и анализа данных, находящихся в списках MS Excel или таблицах, связанных в других приложениях. Внешние сводные таблицы являются структурой, позволяющей размещать данные в трехмерном виде. Сводные таблицы могут использоваться:
• для обобщения большого количества однотипных данных;
• для реорганизации данных (с помощью перетаскивания);
• для отбора и группировки данных;
• для построения диаграмм.
Сводные таблицы создаются с помощью мастера сводных таблиц (команда Данные | Сводная таблица) по следующей методике:
1. Выбрать место для сводной таблицы, т. е. установить указатель ячейки в необходимое место на рабочем листе.
2. Выполнить команду Данные | Сводная таблица.
3. Задать исходный диапазон данных, выполнив шаги 1 и 2 мастера (рис. 54 и 55). После нажатия кнопки Далее в окне мастера, приведенном на рис. 55, откроется окно 3-го шага мастера (рис. 56).
4. Прежде чем указать местоположение будущей таблицы (рис. 56), необходимо нажать кнопку Макет и в открывшемся окне (рис. 57) сформировать макет сводной таблицы (т. е. задать страницу, строки, столбцы, итоговые и вычисляемые поля сводной таблицы).
Рис. 54Определение местоположения данных для сводной таблицы
Рис. 55.Диапазон данных для сводной таблицы
Рис. 56.Указание местоположения будущей сводной таблицы
5. Для определения необходимой операции для полей, помещенных в область Данные, либо задания вычисляемого поля дважды щелкнуть левой кнопкой мыши на поле, помещенном в область Данные (рис. 57), и выбрать необходимые действия в окне Вычисление поля сводной таблицы (рис. 58).
6. Нажать кнопку Параметры (рис. 56) и в открывшемся окне (рис. 59) установить необходимые параметры сводной таблицы.
Рис. 57.Формирование макета сводной таблицы
Рис. 58. Окно Вычисление поля сводной таблицы
Рис. 59. Установка параметров сводной таблицы
7. После проведения всех подготовительных операций нажать кнопку Готово (рис. 56).
При создании, редактировании и работе со сводными таблицами необходимо учитывать следующее:
• местоположение сводной таблицы — желательно располагать ее на отдельном листе, т. к. при обновлении, группировках сводной таблицы информация, заключающаяся на рабочих листах рядом со сводной таблицей, может оказаться скрытой;
• местонахождение исходных данных – список MS Excel, внешний источник, диапазоны консолидации, находящиеся в другой сводной таблице;
• необходимость при создании структуры сводной таблицы определить:
1) поля, находящиеся в строках и столбцах таблицы;
2) поля, по которым подводятся итоги (с выбором необходимой операции);
3) поля для страниц, что позволяет представить информацию в трехмерном виде.
• сводная таблица – это средство только для отображения данных. Поэтому в самой таблице данные редактировать нельзя. Для изменения данных в сводной таблице необходимо внести изменения в источник данных, а затем обновить сводную (кнопкой Обновить данные на панели инструментов Сводные таблицы (рис. 60);
Рис. 60. Панель инструментов Сводные таблицы
• в сводных таблицах можно изменять названия полей, что не влечет изменений в полях исходных данных. Манипулирование элементами сводной таблицы можно также осуществлять мышью.
• сводные таблицы допускают возможность группировки элементов полей по различным уровням иерархии путем объединения в группы. Для этой цели в меню Данные | Группа и структура существуют две кнопки 
Группировать и 
Разгруппировать. Группы эти можно переименовывать по желанию.
• возможность построения диаграмм на основе сводных таблиц.
Задание Создать сводную таблицу, выводящую итоги по средней цене и суммарному пробегу машин, а также использующую в качестве страницы фамилию владельца, в качестве строк— марку машины, в качестве столбцов — год выпуска.
Решение
1. Выделите подготовленный диапазон данных либо установите в него указатель ячейки.
2. Выполните команду Данные | Сводная таблица.
3. Работая с мастером сводных таблиц, определите все необходимые элементы сводной таблицы.
4. Выполните группировку по полю Год выпуска — после создания сводной таблицы поочередно выделите мышью необходимые года выпуска: 1978—1990, 1991—1995, 1996—1999 и
воспользуйтесь командой Группировать контекстного меню Группа и структура либо соответствующей командой меню Данные | Группа и структура.
5. Отформатируйте сводную таблицу, вызвав кнопкой Формат отчета панели инструментов Сводные таблицы диалоговое окно Автоформат с вариантами форматирования.
6. Подготовленная сводная таблица представлена на рис. 70.
Рис. 70. Пример сводной таблицы
ВАРИАНТ I.
Подготовить на рабочих листах данные в соответствии со следующей структурой строки заголовка.
| Марка машины | Цифры номера | Буквы номера | Год выпуска | Год приобретения | Цвет машины | Пробег | Цена | Владелец |