- Откройте таблицу, созданную в задание 1.
СОЗДАНИЕ ИНТЕРВАЛА КРИТЕРИЕВ
- Для применения расширенного фильтра нужно создать диапазон критериев. Расположите интервал критериев, начиная с ячейки A19. Для этого выделите диапазон с именами столбцов (A1:F1); скопируйте выделенный диапазон в ячейки A19:F19.
- Отберите информацию о книгах, которые закуплены или в 1 квартале или имеют тематику «Экономика». Порядок выполнения:
- в ячейку А20 занесите значение 1 кв, в ячейку B21 занесите значение «Экономика» (т.е. при расположении условий отбора на разных строчках формируется их связь по правилу ИЛИ);
- сделайте текущей любую ячейку в области База_данных (например С6);
- выполните команду вкладка Данные – Сортировка и фильтр – Дополнительно;
- в диалоговом окне Расширенный фильтр поле Исходный диапазон будет уже заполнено (именно для автоматического заполнения этого поля выбиралась ткущая ячейка внутри области данных);
- в поле Диапазон условий либо наберите вручную, либо выделите с помощью мыши диапазон А19:F21 (диапазон ячеек, в котором заданы условия отбора);
- установите опцию Скопировать результат в другое место и в поле Поместить результат в диапазон укажите адрес любой удобной для просмотра ячейки;
- нажмите кнопку ОК.
В результате будут отобраны записи, относящиеся к 1 кварталу или у которых тематика «Экономика».
- Отберите информацию о книгах, которые закуплены в 1 квартале и имеют тематику «Экономика». Порядок выполнения:
- переместите значение «Экономика» из ячейки В21 в ячейку В20 (т.е. при расположении условий отбора на одной строчке формируется их связь по правилу И);
- сделайте текущей любую ячейку в области База_данных;
- выполните команду вкладка Данные – Сортировка и фильтр – Дополнительно;
- выберите поле Диапазон условий, очистите его и введите в него диапазон $A$19:$F$20 (диапазон ячеек, в котором заданы условия отбора);
- установите опцию Скопировать результат в другое место и в поле Поместить результат укажите адрес любой удобно для просмотра ячейки.
- Отберите информацию о книгах, которые закуплены в 1 квартале по тематике «Экономика», а во втором квартале по тематике «Компьютеры». Порядок выполнения:
- очистите ячейку А22, в ячейки В20 и В21 занесите значения «Экономика» и «Компьютеры» соответственно. Первая строка определяет условие отбора записей покупок книг в 1 квартале по тематике «Экономика», а вторая – во 2 квартале по тематике «Компьютеры». Условия в строчках объединены правилом И. Между собой строки объединены правилом ИЛИ;
- сделайте текущей любую ячейку в области База_данных;
- выполните команду вкладка Данные – Сортировка и фильтр –Дополнительно;
- установите опцию Скопировать результат в другое место и в поле Поместить результат укажите адрес любой удобно для просмотра ячейки;
- выберите поле Диапазон условий, очистите его и введите в него диапазон $A$19:$F$21 (диапазон ячеек, в котором заданы условия отбора), нажмите ОК.
ПРИМЕНЕНИЕ ТЕКСТОВЫХ КРИТЕРИЕВ
- Отберите информацию о книгах, названия которых начинаются с латинских букв. Порядок выполнения:
- очистите диапазон ячеек А20:В21, в ячейку С20 занесите условие <A (русская буква), т.е. отберите все названия, которые начинаются с любой латинской буквы;
- сделайте текущей любую ячейку в области База_данных;
- выполните команду вкладка Данные – Сортировка и фильтр – Дополнительно;
- выберите поле Диапазон условий, очистите его и введите в него диапазон $A$19:$F$20 (диапазон ячеек, в котором заданы условия отбора), нажмите ОК.
ПРИМЕНЕНИЕ ВЫЧИСЛЯЕМЫХ КРИТЕРИЕВ
- Отберите информацию о книгах, цена которых больше средней цены всех книг. Порядок выполнения:
- в ячейку С25 введите формулу определения средней цены всех книг: =СРЗНАЧ(D2:D16), в ячейку В25 введите вычисляемый критерий: =D2>$C$25, где D2 – адрес первой ячейки, содержащей цену (адрес этой ячейки должен быть относительным);
- сделайте текущей любую ячейку в области База_данных;
- выполните команду вкладка Данные – Сортировка и фильтр – Дополнительно;
- выберите поле Диапазон условий, очистите его и введите в него диапазон $В$24:$В$25 (в диапазон ячеек, который определяет интервал критериев, обязательно включается ячейка над формулой), нажмите ОК.
КОПИРОВАНИЕ ВЫБРАННОЙ ИНФОРМАЦИИ В ДРУГОЕ МЕСТО РАБОЧЕГО ЛИСТА С ИСПОЛЬЗОВАНИЕМ ДАННЫХ ОТДЕЛЬНЫХ СТОЛБЦОВ
- Отберите информацию о книгах, которые закуплены в 3 квартале, расположите отобранные записи, начиная с ячейки H19, используя информацию из столбцов Дата, Название, Стоимость. Порядок выполнения:
· очистите ячейку В20, в ячейку А20 введите «3 кв»;
· очистите ячейки в диапазоне H19:M24 (где находится информация, отобранная в предыдущем задании);
· в ячейку H19 скопируйте содержимое ячейки А1 (название столбца Дата), в I19 – из ячейки C1, и в J19 – из ячейки F1;
· сделайте текущей любую ячейку в области База_данных;
· выполните команду вкладка Данные – Сортировка и фильтр – Дополнительно;
· включите опцию Скопировать результат в другое место;
· выберите поле Диапазон условий, очистите его и введите в него диапазон $А$19:$F$20 (диапазон ячеек, в котором заданы условия отбора);
· выберите поле Поместить результат в диапазон, и введите в него диапазон $H$19:$J$19 (количество ячеек в диапазоне равно числу выбранных столбцов), нажмите ОК.
В ячейках под именами столбцов появятся выбранные строки с информацией.
ПОЛУЧЕНИЕ ПРОМЕЖУТОЧНЫХ И ОБЩИХ ИТОГОВ
При помощи команды Промежуточные итоги в группе Структура на вкладке Данные можно автоматически посчитать промежуточные и общие итоги в списке для столбцов.
- Определите по тематике «Компьютеры» суммарное количество книг и их стоимость, закупленных в каждом квартале и всего за год. Порядок выполнения:
- очистите ячейку А20, в ячейку В20 введите слово «Компьютеры»;
- сделайте текущей любую ячейку в области База_данных;
- выполните команду вкладка Данные – Сортировка и фильтр – Дополнительно;
- выберите поле Диапазон условий, очистите его и введите в него диапазон $А$19:$F$20;
- нажмите кнопку ОК. После этого отобраны все записи по тематике «Компьютеры» за весь год;
- на вкладке Данные в группе Структура выполните команду Промежуточные итоги;
- в появившемся диалоговом окне Промежуточные итоги в поле При каждом изменении в введите Дата и в поле Операция введите СУММА;
- в поле Добавить итоги по включите элементы Количество и Стоимость. Проверьте, чтобы все остальные элементы в этом поле были выключены (отсутствовала галочка), нажмите кнопку ОК.
В результате получится таблица, в которой имеются итоговые строки за каждый квартал, в которых приведены суммарные значения за соответствующий период.
- Удалите итоги. Для этого в диалоговом окне Промежуточные итоги нажмите кнопку Убрать все.