Табличные формулы не являются отдельной категорией функций. Они представляют собой особую запись обычных функций и используются в тех случаях, когда необходимо выполнить сразу несколько вычислений или проверок условий. В отличие от обычной формулы табличная формула в качестве своих аргументов использует диапазоны ячеек.
Вводятся табличные формулы с помощью сочетания клавиш CTRL+SHIFT+ENTER, после чего Excel автоматически заключает формулу в фигурные скобки «{ }».
Табличные формулы могут возвращать одно или несколько значений. Ограничимся рассмотрением возвращения одного значения на примере таблицы 1.5.
Таблица 1.5 – Информация об изделиях
| A | B | C | |
| Стоимость единицы изделия | Цена единицы изделия | Количество изделий | |
Ответим на следующие вопросы, вводя формулы в ячейки, расположенные ниже таблицы 1.5.
1 Имеются ли изделия с ценой, равной 20 р.?
Ввод формулы {=ЕСЛИ(ИЛИ(B2:B4=20);"есть";"нет")} даст результат «есть».
2 Сколько изделий со стоимостью 16 р.?
Введем формулу {=СУММ(ЕСЛИ(A2:A4=16;C2:C4;0))}, получим 400. Эту задачу можно было решить обычной формулой: =СУММЕСЛИ(A2:A4;16;C2:C4).
3 Какое количество строк в таблице с ценой более 20 р.?
Введем формулу {=СЧЁТ(ЕСЛИ(B2:B4>20;1;"a"))}, получим 1. Вариант не табличной формулы: =СЧЁТЕСЛИ(B2:B4;">20") даст такой же результат.
Особенности конструирования табличных формул
В табличных формулах не следует использовать в качестве условия функции И и ИЛИ с более чем одним аргументом. Если алгоритм требует использования сложного условия, то следует данное сложное условие разложить на несколько простых условий. Например.
Требуется посчитать количество изделий, цена которых не менее 20 и не более 30 рублей.
Для решения этой задачи нужно ввести формулу
{=СУММ(ЕСЛИ(B2:B4>=20;ЕСЛИ(B2:B4<=30;C2:C4;0);0))}, получим 400.
Работа со списком (базой данных)
Основные понятия
Список – это таблица Excel, столбцы которой содержат однотипную информацию. Каждый столбец имеет свой заголовок, который называется именем поля. Все имена полей располагаются в первой строке таблицы. На каждое имя отводится одна ячейка. Отдельный столбец называется полем данных, а каждая строка – записью. Концом списка считается пустая строка. Примером списка может служить таблица 1.6.
Таблица 1.6 - Результаты работы автосалона
| A | B | C | D | |
| Продавец | Марка | Год выпуска | Оборот | |
| Иванов | Мерседес | |||
| Петров | Форд | |||
| Иванов | Форд | |||
| Сидоров | Рено | |||
| Петров | Ауди | |||
| Сидоров | Мерседес | |||
| Сидоров | Мерседес |
В первой строке списка указаны имена полей: Продавец, Марка, Год выпуска и Оборот. Список содержит семь записей.
Сортировка списка
Под сортировкой списка понимается упорядочение его записей в заданном порядке. Сортировка может производиться по содержимому одного, двух и трех полей одновременно. Причем в каждом поле может быть задан свой (убывающий или возрастающий) порядок сортировки.
Для сортировки списка надо выполнить следующие действия:
• активизировать любую ячейку списка;
• выполнить команду Данные-Сортировка;
• в окне диалога задать поля и порядок сортировки;
• щелкнуть по кнопке Ok;
Фильтрация списка
Фильтрация это выбор и отображение на экране только тех записей из списка, которые удовлетворяют заданным условиям отбора. Excel имеет два вида фильтра: автофильтр и расширенный фильтр.
Автофильтр
Этот вид фильтра применяется в тех случаях, когда необходимо быстро отфильтровать данные с заданием одного или двух простых условий отбора. Отфильтрованные записи замещают исходную таблицу. Вызов автофильтра производится командой Данные-Фильтр-Автофильтр. При этом активной должна быть ячейка таблицы.
После этого в первой строке рядом с названием каждого поля появится кнопка со стрелкой (рис. 1.1). С помощью этой кнопки можно раскрыть список элементов отбора записей. Выбрав элемент «условие» можно задать сложный критерий отбора, используя функции И и ИЛИ.
| A | B | C | D | |||||
| Продавец | Ñ | Марка | Ñ | Год выпуска | Ñ | Оборот | Ñ | |
Рисунок 1.1 – Первая строка таблицы после вызова Автофильтра
Пример. Показать в базе данных записи с оборотом от 200 до 400 для продавца Иванова.
Для этого из раскрывающегося списка поля Продавец
| Продавец | Ñ |
выберем пункт Иванов, а из раскрывающегося списка
| Оборот | Ñ |
выберем пункт «условие». Появится окно Пользовательского автофильтра, в котором надо указать условия для поля «Оборот» (рис. 1.2).
Рисунок 1.2 – Окно пользовательского автофильтра
В результате на месте исходной таблицы 1.6 будут выведены отфильтрованные записи (таблица 1.7).
Таблица 1.7 – Результат автофильтра
| A | B | C | D | |
| Продавец | Марка | Год выпуска | Оборот | |
| Иванов | Форд |
С помощью повторного выполнения команды Данные-Фильтр-Автофильтр можно восстановить отображение всех записей базы данных.
Расширенный фильтр
В этом виде фильтра сначала необходимо создать условия отбора записей в отдельном диапазоне ячеек текущего рабочего листа. Этот диапазон называется диапазоном критериев. Он может размещаться на любом свободном месте.
Затем выполнить фильтрацию командой Данные-Фильтр-Расширенный фильтр.
