Met_Excel

Чтобы воспользоваться расширенным фильтром, нужно выбрать ко-

манду меню Данные - Фильтр - Расширенный фильтр для для Excel 2003

или на вкладке Данные - Дополнительно для Excel 2007. На экране появится диалоговое окно (рис. 32)

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

В элементе управления Исходный диапазон нужно указать диапазон, в

котором размещается список, в элементе управления Диапазон условий -

диапазон критериев. По умолчанию в группе Обработка всегда установлен переключатель фильтровать список на месте. Если выбрать вариант скопировать результат в другое место, то будет задействован диапазон для извлечения. В этом случае становится доступным элемент управления Поместить результат в диапазон, в котором нужно задать диапазон, в

который будут помещены отфильтрованные данные. Флажок Только уникальные записи позволяет исключить повторяющиеся.

Задание условий с использованием логической операции ИЛИ

Чтобы связать условия в диапазоне критериев логической операцией

ИЛИ, нужно эти условия расположить в разных строках (рис. 33).

30

Задание условий с использованием логической операции И

Пусть необходимо создать критерий отбора записей с использованием оператора И. Для этого условия в диапазоне критериев нужно расположить в одной строке (рис. 34).

10.Присвойте следующему листу имя Расширенный фильтр.

11.Скопируйте таблицу данных с листа Автофильтр и поместите её начиная с пятой строки листа.

12.Необходимо отобрать записи о людях с именем Иван или с отчеством Иванович. Для этого сформируйте диапазон критериев с использованием логической операции ИЛИ (рис.33).

13.Самостоятельно отберите записи о сотрудниках с именем Иван,

работающих в бухгалтерии (рис.34).

14.Сохраните результат своей работы

Рис.33 Пример использования операции ИЛИ

Рис.34 Пример использования операцииИ

31

Функции просмотра и ссылок

Функции просмотра и ссылок позволяют просматривать и обрабатывать информацию, хранящуюся в списке или таблице. Так функции ВПР() и ГПРО используются для поиска информации в прямоугольных таблицах. Данные функции имеют следующий синтаксис:

=ВПР (искомое_знач; таблица; номер_столбца; тип_просмотра);

=ГПР (искомое_знач; таблица; номер строки; тип_просмотра),

где:

искомое_знач— это значение, которое необходимо найти в первом столбце (строке) таблицы;

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

также значений даты/времени;

номер_столбца (номер строки) — указывает, из какого столбца

(строки) таблицы следует выбирать возвращаемое значение;

тип_просмотра — определяет логическое значение для указания типа соответствия: точное или приближенное. Если аргумент опущен, то используется приближенное сравнение, что означает поиск наибольшего сравниваемого значения, которое меньше или равно искомому значению.

Поиск осуществляется по следующему правилу:

Искомое значение сравнивается с данными, расположенными в крайнем левом столбце для функции ВПР (или в верхней строке для функции ГПР) таблицы, и таким образом определяется первый индекс возвращаемого значения. Номер столбца (номер строки) задает второй индекс, опре-

деляющий строку или столбец таблицы, из которой возвращается значение.

Упражнение 3

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

отработанных лет.

32

1. Сформируйте ведомость и дополнительную таблицу, как показано на рис. 35. Необходимо рассчитать надбавку учитывая стаж работы,

используя базовую таблицу.

2. В ячейку D2 введите формулу:

=С2*ВПР(В2;$А$16:$В$20;2),

где:

В2 - искомое значение (стаж сотрудника), которое необходимо найти в крайнем левом столбце таблице, определяемой диапазоном $А$16:$В$20;

индекс 2 определяет номер столбца таблицы, из которого будет возвращено значение (в данном случае, процент надбавки); С2 – оклад сотрудника.

3.В ячейку Е2 введите формулу = С2*D2.

4.Скопируйте формулы в соответствующие диапазоны.

5.Отформатируйте таблицу самостоятельно.

6.Результат этого упражнения представлен на рис.36.

7.Сохраните работу под именем Упражнение 3 в книге

Упражнения.

33

Рис.36 Ведомость заработной платы

Лабораторная работа №5

1.Используя функции просмотра и ссылок самостоятельно решите предложенные задачи.

2.Сохраните результат своей работы под именем Задачи.

Задача 1

Сформируйте исходные данные (рис.37)

35

Рис.37 Исходные данные к задаче 1

Задача 2

Сформируйте исходные данные (рис.38)

Рис.38 Исходные данные к задаче 2

36