Использование мастера функций.

Кнопка в панели инструментов или команда ВСТАВКА - ФУНКЦИЯ активизирует диалоговое окно» Мастер функций - шаг1 из 2″.

В списке «Категория» выделяется тип функций, в соседнем списке «Функция» выбирается имя функции.

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

После задания всех аргументов нужно нажать на кнопку «Готово».

Типы функций

Функции подразделяются на текстовые, математические, финансовые, статистические, логические, функции даты.

Статистические функции

=СРЗНАЧ(С3:С26) =МАКС(В3:В18) =СУММ(D3:D18) =СЧЕТ(B3:B12)

Логические функции

Одной из наиболее часто используемой функцией этого типа является ЕСЛИ.

Формат этой функции:

ЕСЛИ (логическое_ выражение; значение_если_истина; значение_если_ ложь).

Первым аргументом, обозначенным как логическое_выражение, является условие. Если оно выполняется, в силу вступает 2-ой аргумент (значение_если_истина), если нет — работает 3-й (значение_если_ложь).

=ЕСЛИ(С3>CРЗНАЧ(С4:С18);D3*50%;D3*20%)

Ссылки и массивы

Рассмотрим функцию ВПР, которая может быть использована при совместной обработке двух таблиц, а именно: при расчете одной таблицы можно выбирать по заданному критерию данные из другой таблицы:

ВПР(искомое_значение; табличный_массив; номер_столбца;).

Искомое_значение — это значение, которое должно быть найдено в первом столбце таблицы, обозначенной в формате команды как табличный_массив. Искомое_значение может быть значением, ссылкой или символьной строкой.

Таблица 1

Таблица 2 (на ЛИСТЕ2) Разряд Тариф   120 Р.   150 р. ФИО	Разряд	Тариф
Иванов		ВПР(……)
Петров
Сидоров
Соколов
Петин

Табличный_массив — это таблица с искомой информацией- справочник.. При вводе функции следует использовать ссылку на интервал, где находится эта таблица. Отметим, что табличный массив должен быть отсортирован по первому столбцу.

Номер_столбца — это номер столбца в табличном массиве, в котором выбирается информация. Нумерация начинается с 1. Так, если номер столбца равен 1, информация выбирается из первого столбца и т.д.

Например, найти значения тарифов работников таблицы 1, взяв их из таблицы 2.

ВПР(С2;Лист2!$А$2:$В$4;2) – формула из ячейки С2 первой таблицы.. Найти значение РАЗРЯДА(С2) из таблицы 1, в первом столбце табличного массива Лист2!$А$2:$В$4 и взять значение для вычисления из столбца 2 этого табличного массива (120 р). При этом сам табличный массив должен быть отсортирован по первому столбцу в порядке возрастания.

Вложенные функции

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

ЕСЛИ (СРЗНАЧ(D3:D24)>50;C3*1,5;C3*1,2)

В формулах можно использовать до семи уровней вложения функций. Когда «функция Б» является аргументом «функции А», то «функция Б» считается вторым уровнем вложения. Если в «функции Б» содержится в качестве аргумента «функция В», то «функция В» будет считаться третьим уровнем вложения функций.

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

Например, нажимая стрелку списка в строке формул, можно вставить «функцию Б» в качестве аргумента «функции А». Для продолжения ввода аргументов в «функцию А» укажите ее имя в строке формул.

СПИСКИ

Cовокупность данных в виде таблицы полей и записей называется списком или базой данных. В EXCEL понятия список и база данных взаимозаменяемы.

При создании списков и баз данных необходимо соблюдать определенные правила:

· не размещайте более одного списка на листе.

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

· Не допускайте в самом списке пустых строк и столбцов, чтобы не затруднить идентификацию и выделение списка;

· Не размещайте важные данные у левого или правого края списка, так как после применения фильтра они могут оказаться скрытыми.

· Во всех строках в одинаковых столбцах должны находится однотипные данные.

· В ячейке перед данными не вводите лишние пробелы, так как они влияют на сортировку.

· Не оставляйте пустую строку между заголовками и первой строкой данных.

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

Форма – простое и эффективное средство для редактирования записей.

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

- нажать кнопку Критерии

- очистка полей

- в определенное поле вволится условие

Переход между полями TAB и SHIFt+tab

Перед установкой нового критерия слкедует воспользоваться кнопкой ОЧИСТИТЬ.

Для редактирования записей по заданному критерию используется кнопка ПРАВКА.

Фильтрация списков

Включить режим автоматической фильтрации можно, выделив некоторую ячейку списка, а затем выбрав команду ДАНННЫЕ_ ФИЛЬТР _АВТОФИЛЬТР. В заголовках каждого столбца данных появится кнопки фильтра со стрелочками вниз. Далее с помощью имен полей следует определить условия, по которым будет выполняться фильтрация списка. Выбрав необходимые данные, вы можете отключить АВТОФИЛЬТР, выбрав эту же команду.

Эти ключи позволяют найти в списке:

Поиск по значению поля – для поиска точно соответствия

·? десять записей, ведущих по выбранному показателю; — команда первые 10. количество отбираемых записей (с наибольшим или наименьшим значением) м. задавать непосредственно либо в процентах.

·? записи, удовлетворяющие заданному пользователем условию; — для определенного поля задаются одно или два условия

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

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

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

Предоставляемые возможности поиска и фильтрации шире. Можно более свободно применять операции И, ИЛИ, а также составлять вычисляемые критерии. Кроме того, отфильтрованные данные могут быть скопированы в другой диапазон ячеек.

ПРИНЦИПЫ РАБОТЫ РФ: необходимо создать диапазон критериев, где задаются условия поиска данных. Верхняя строка диапазона должна содержать заголовки полей. ПРОЩЕ всего обеспечить точность совпадения путем копирования.

Для ввода условий можно использовать несколько строк, однако если какая-либо строка остается пустой, на рабочем листе останутся видны все записи. Пустая строка условий означает, что условия не заданы.

Нет необходимости именовать диапазон критериев, но если это сделать, работа с расширенным фильтром упроститься и сократиться число возможных ошибок.

1.Диапазон критериев может содержать константы, шаблоны, простые сравнения, а также формулы.

2.После подготовки диапазона критериев все готово к использованию команды Данные, Фильтр, Расширенный фильтр.
!!! Расширенный фильтр отключает Автофильтр, поэтому его можно использовать независимо от того, включен или нет Автофильтр.

3.Выберите один из переключателей в группе ОБРАБОТКА. При выборе переключателя Фильтровать список на месте, не удовлетворяющие критерию записи будут скрыты, При выборе СКОПИРОВАТЬ РЕЗУЛЬТАТ В ДРУГОЕ МЕСТО список останется нетронутым, а отобранные записи будут помещены в указанный диапазон.

4. Если необходимо, перейдите к полю Исходный ДИАПАЗОН и введите ссылку или имя диапазона списка.

Ввод ссылок во всех полях данного диалогового окна легче и лучше всего осуществлять путем мыши

5. Если необходимо, перейдите к полю ДИАПАЗОН КРИТЕРИЕВ и введите ссылку или имя диапазона критериев.

6. Если выбран переключатель СКОПИРОВАТЬ РЕЗУЛЬТАТ В ДРУГОЕ МЕСТО, перейдите к полю ПОМЕСТИТЬ РЕЗУЛЬТАТ В ДИАПАЗОН и введите ссылку или имя диапазона назначения.

7. Установите флажок ТОЛЬКО УНИКАЛЬНЫЕ ЗАПИСИ, если не хотите, чтобы одинаковые записи повторялись.

Результаты работы Расширенного фильтра сразу же отобразятся на рабочем листе.

!!! При вводе критерия для поиска простого текстового соответствия можно использовать символ *.