Использование автофильтра

Упражнение 17

1. Запустите программу Excel 2010. Откройте файл Заказы.xlsx. Скопируйте таблицу «Заказы» на свободный лист книги и переименуйте лист «Фильтрация данных».

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

3. Перейдите на лист «Фильтрация данных».

4. Требуется отсортировать таблицу по регионам в алфавитном порядке.

5. Выделите таблицу с данными, включая ячейки с заголовками столбцов.

6. Щелкните по кнопке Данные /Сортировка и фильтр /Фильтр. В ячейках с заголовками столбцов появятся кнопки со стрелками.

7. Щелкните по кнопке со стрелкой в ячейке Регион. Появится меню фильтра. Открывающееся в каждом столбце меню фильтра разделено на три части: сортировка, текстовые фильтры (числовые фильтры или фильтр по дате в зависимости от типа значений в столбце), список значений данного поля. В появившемся меню выберите пункт Сортировка от А до Я. Данные в столбце будут отсортированы по алфавиту в возрастающем порядке. Причем данные в других столбцах также переместятся согласно изменениям в столбце «Регион».

8. Чтобы отобразить в таблице записи по заказчику В1 (применить фильтр) щелкните по кнопке со стрелкой в ячейке «Код заказчика». Появится меню фильтра. Выберите список значений данного поля. В списке оставьте флажок у только у заказчика В1.

9. Щелкните по кнопке ОК. В таблице остались записи по заказчику В1, остальные скрыты.

10. Чтобы отобразить в таблице записи по заказчику В1 с заказанными изделиями по цене в пределах от 600 до 1000

· Щелкните по кнопке со стрелкой в ячейке «Цена». Появится меню фильтра.

· Выберите числовые фильтры.

· В открывшемся списке выберите Между, откроется диалоговое окно Пользовательский автофильтр, которое содержит две строки для ввода условий, соединенных оператором И /Или.

· В соответствующие поля добавьте: больше 600 и меньше 1000. В таблице останутся записи, удовлетворяющие заданным условиям. Обратите внимание, что после применения фильтра, на кнопках в ячейках «Код заказчика» и «Цена» появились значки в виде воронки. Это означает, что к данным столбцам применен фильтр.

11. Чтобы отменить фильтр щелкните по кнопке в ячейке «Код заказчика». Появится меню фильтра. В появившемся меню выберите команду Удалить фильтр с «Код заказчика», а затем Удалить фильтр с «Цена». Обратите внимание на другие условия фильтров из раскрывающихся списков.

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

На отдельных листах книги создайте фильтры.

1. Суммы заказов, сделанные 11.01.2010 заказчиком В1;

2. Суммы заказов в период с 11.01.2010 по 20.01.2010, сделанные заказчиком В1или Ц1;

3. Суммы заказов больше среднего значения по полю «Сумма заказа» в период с 11.01.2010 по15.01.2010, сделанные заказчиками В1 и З1.

Использование расширенного фильтра

Чтобы сохранять результаты фильтрации в виде отдельных таблиц следует воспользоваться командой Данные /Сортировка и фильтр /Дополнительно. Выполнив указанную команду, откройте диалоговое окно Расширенный фильтр. Поставьте переключатель в поле Копировать в другой диапазон. Обратите внимание: требуется наличие диапазона критериев фильтрации и диапазона для размещения результата.

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

Упражнение 18

1. Создайте новый лист рабочей книги, назовите его «Расширенный фильтр». Скопируйте на него таблицу «Заказы».

2. Постройте таблицу: Суммы заказов от 11.01.2010, сделанные заказчиком В1.

3. Рядом с исходной таблицей «Заказы» создайте диапазон критериев. (Рис. 24)

Рис. 24 Диапазон критериев

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

5. Ниже диапазона критериев выделите одну ячейку для будущей таблицы (если хотите в будущей таблице отобразить все столбцы исходной таблицы), выполните команду Данные /Сортировка и фильтр /Дополнительно, откроется диалоговое окно Расширенный фильтр, заполните поля.

6. Нажмите ОК. Получите следующий результат. (Рис. 25)

Рис. 25 Результат фильтрации таблицы

7. В ячейку J4 введите название таблицы «Суммы заказов 11.01.2010, сделанные заказчиком В1»

8. Постройте таблицу «Сумма заказов с 11.01.2010 по15.01.2010, сделанные заказчиками В1 и Ц1».

9. Рядом с исходной таблицей «Заказы» создайте диапазон критериев. Так как условия отбора записей по полю Код заказчика объединены союзом «Или», а поле Дата имеет два условия , то диапазон критериев будет выглядеть так: (Рис. 26)

Рис. 26 Диапазон критериев

10. Ниже диапазона критериев выделите для будущей таблицы три ячейки с названиями столбцов: Дата, Код заказчика, Сумма заказа, (если хотите в будущей таблице отобразить только эти столбцы из исходной таблицы) Откройте диалоговое окно Расширенный фильтр, заполните поля.

11. Нажмите ОК. Результат фильтрации будет выглядеть так. (Рис. 27)

Рис. 27 Результат фильтрации таблицы

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

Самостоятельно на этом же листе постройте таблицы с записями, содержащими следующие данные.

1. Сумма заказов по каждому заказчику, количество заказанных изделий по видам изделий, по заказам, сделанным в последнюю декаду месяца (с 21.01.2010).

2. Потребность в различных видах транспорта в период с 10 по 15 января 2010 года.

3. Даты отправки груза ж/д транспортом с указанием регионов.

4. Даты отправки груза ж/д и авто транспортом с указанием регионов и изделий.

9.6 Работа со сводными таблицами и сводными диаграммами

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

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

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

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

Упражнение 19

1. Загрузите файл Заказы.xlsx

2. Для представления данных в виде сводной таблицы выполните следующие действия.

3. Выделите любую ячейку таблицы «Заказы».

4. Щелкните по кнопке Вставка /Таблицы /Сводная таблица. На экране появится диалоговое окно Создание сводной таблицы. Задайте параметры сводной таблицы: в поле Диапазон данных выделите всю таблицу «Заказы» вместе с заголовками столбцов, в поле Размещение сводной таблицы в рабочей книге Excel переключатель установите в положение На новый лист.

5. Щелкните по кнопке ОК. В книге будет создан новый рабочий лист, а на нем отобразится сводная таблица.

Обратите внимание на открывшуюся в правой части окна программы Excel Панель с инструментами для построения сводной таблицы. Панель разделена на две части. В верхней части находится Список полей сводной таблицы. В нижней – окна для размещения выбранных полей и значений в разных областях сводной таблицы. На ленте появились вкладки Параметры и Конструктор с инструментами для работы со сводной таблицей.

6. Чтобы отобразить стоимость заказов по каждому региону, включите в сводную таблицу поля Регион и Сумма заказа.

7. Установите флажки слева от пунктов Регион и Сумма заказа в области Выберите поля для добавления в отчет. Теперь мы видим стоимость заказов по каждому региону. В нижней строке сводной таблицы находится итог с общей суммой. (Рис. 28)

Рис. 28 Сводная таблица

8. Добавьте в сводную таблицу поле Изделие. Теперь сводная таблица содержит сведения о заказанных изделиях. Обратите внимание: слева от названий регионов в таблице появились кнопки со знаком «минус». С помощью этих кнопок можно свернуть и развернуть группы. То есть, изделия в таблице группированы по регионам. Нажмите на кнопку «-», строки с названиями изделий скрываются, а на кнопке появляется значок «плюс». (Рис. 29)

Рис. 29 Сводная таблица с группировкой по регионам

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

10. Имеет значение порядок размещения полей в области Названия строк в нижней правой части окна программы Excel на панели с инструментами для построения сводной таблицы. Группировка производится по полю, расположенному в верхней строке. Переместите поле Изделие с помощью контекстного меню в верхнюю строку в области Названия строк. Сводная таблица будет перегруппирована. (Рис. 30)

Рис. 30 Сводная таблица с группировкой по изделиям

11. Чтобы вернуть сводную таблицу к исходному состоянию следует вновь поменять местами поля Изделия и Регион в области Названия строк. Обратите внимание, что в заголовке Названия строк сводной таблицы находится кнопка фильтра. Кнопка вызывает меню, в котором данные сводной таблицы сортируются в алфавитном порядке или к ним применяется фильтр как к столбцам с названием, так и к вычисляемым значениям.

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

13. Сводная таблица примет вид (Рис. 31)

Рис. 31 Трехмерная сводная таблица

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

15. Уберите флажок с поля Код заказчика в области Выберите поля для добавления в отчет.

16. Чтобы сделать выборку по изделию А и отобразить суммы, уплаченные за него:

· Выделите ячейку с названием любого изделия. Щелкните по кнопке фильтра в ячейке Название строк. Появится меню фильтра.

· Оставьте флажок только в поле Изделие А, щелкните по кнопке ОК. В сводной таблице останутся записи только по изделию А.

· Для отмены фильтра щелкните по кнопке фильтра в ячейке Название строк. В появившемся меню выберите команду Удалить фильтр с «Изделие».

⇐ Предыдущая11121314151617181920Следующая ⇒

Поделиться: