Oznakomitelnoe_zadanie_Excel

61

гистограммы. Выбрать из контекстного меню Добавить подписи данных.

Получим:

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

Выбрать из списка Формат области построения.

62

В окне Формат области диаграммы выбрать Рисунок или текстура:

Получим:

63

Фильтрация данных

Перейти на лист Автофильтр. Отфильтровать данные в столбце Дата поставки за лето 2012 года. Из отобранных выбрать только те строки, в которых стоимость доставки больше 500 000 руб., но меньше 1 000 000 руб.

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

Автофильтр. Выполнить команду Данные /Фильтр.

64

Укаждого столбца таблицы появится стрелка. Раскроем список

встолбце Дата поставки и в развернувшемся меню оставим галочки только напротив летних месяцев:

Нажать ОК. Получим:

Теперь нужно отобрать строки со стоимостью от 500 000 руб. до 1 000 000 руб. Развернём список в столбце Стоимость доставки и выберем пункт числовые фильтры/больше…:

65

В окне Пользовательский автофильтр выполнить следующие установки:

Получим:

То есть за лето 2012 года был только один груз со стоимостью доставки от 500 000 руб. до 1 000 000 руб.

Расширенный фильтр

Команда Расширенный фильтр, в отличие от команды Автофильтр, требует задания условий отбора строк в отдельном диапазоне рабочего листа или на другом листе. Диапазон условий включает в себя заголовки столбцов условий и строки условий. Заголовки столбцов в диапазоне условий должны точно совпадать

66

с заголовками столбцов в исходной таблице. Поэтому заголовки столбцов для диапазона условий лучше копировать из таблицы. В диапазон условий включаются заголовки только тех столбцов, которые используются в условиях отбора. Если к одной и той же таблице надо применить несколько диапазонов условий, то диапазонам условий (как именованным блокам) удобно присвоить имена. Эти имена затем можно использовать вместо ссылок на диапазон условий. Примеры диапазонов условий (или критериев отбора):

Таблица 1

Стоимость	Город
доставки	получателя
>500 000
	Волгоград

Если условия расположены в разных строках, то это соответствует логическому оператору ИЛИ. Если Стоимость доставки больше 500 000, а Город получателя – любой (первая строка условия). ИЛИ если Город получателя - Волгоград, а Стоимость доставки – любая, то из списка будут отобраны строки, удовлетворяющие одному из условий.

Другой пример диапазона условий (или критерия отбора): Таблица 2

Стоимость	Город
доставки	получателя
>500 000	Волгоград

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

Таким образом, условия фильтрации, размещенные в одной строке диапазона, объединяются логической функцией И; условия, заданные в разных строках, функцией ИЛИ. Пустая ячейка в диапазоне условий означает любые значения.

Создадим новый лист Лист1:

Переименуем его в Фильтр:

67

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

Город получателя – Волгоград и Вес (тонн)>20. Результат получить в виде таблицы на листе Фильтр:

Таблица 3

Наименование Вес (тонн) Объем (куб. м) получателя

Шапку таблицы результата создать на листе Фильтр в ячейках D1:F1 копированием из таблицы на листе Рабочая ведомость.

На листе Фильтр создадим диапазон условий в верхней части листа, в ячейках А1:В2. Названия полей обязательно cкопировать с листа Рабочая ведомость. Если выделяемые блоки несмежные, то при выделении применить клавишу Ctrl.

Присвоим имя этому диапазону условий (A1:B2) Условие1.

Выполним команду Данные/Дополнительно:

Появится диалоговое окно:

68

В нём следует установить следующие настройки:

Исходный диапазон и диапазон условий вставлять с помощью клавиши F3.

Важно! Установить флажок скопировать результат в другое место. Поместить полученные результаты на листе Фильтр в диапазон D1:F1 (выделить ячейки D1:F1).

Получим результат:

Пример 2. Из таблицы на листе Рабочая ведомость с помощью расширенного фильтра отобрать строки с Городом получателя – Волгоград, Характером груза – медикаменты и

69

весом больше 20 тонн и с Городом получателя - Воронеж,

Характером груза – медикаменты и любым весом. На листе Фильтр создадим диапазон условий в ячейках A7:C7. Названия полей и значения периодов обязательно копировать с листа Рабочая ведомость. Присвоим имя этому диапазону условий

Условие2.

Затем выполнить команду Данные/Дополнительно. В диалоговом окне сделать следующие установки:

Получим результат:

Пример 3. Выбрать сведения о поставках в Волгоград, Воронеж и Пермь с объемом более 60 куб. м.

На листе Фильтр в ячейках A12:B15 создадим диапазон условий с именем Условие3:

70

Названия полей обязательно копировать с листа Рабочая ведомость.

После выполнения команды Данные/Дополнительно в диалоговом окне сделать следующие установки:

Получим результат:

Сводные таблицы

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