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

Excel имеет более 500 встроенных функций. Любую из них можно вводить с клавиатуры непосредственно в строке формул окна Excel или используя Мастер функций (Вставка-Функция или значок f_x). Функция в Excel - это имя функции и аргументы в круглых скобках, которые разде­ляются точкой с запятой. Аргументами в функциях могут быть константы и адреса переменных (ссылки).

Функция СУММ (А1; В5; С10) выдаст сумму чисел, заданных в аргу­ментах-ссылках; МИН(С2:Е4) на­йдёт минимальное среди чисел указан­ного в аргументе диапазона ячеек. В качестве аргументов могут применяться другие (вложенные) функ­ции; например, СУММ (МИН (C2:E4)); A1; B5; C10).

Все функции разделены на группы. Самую большую группу составляют вычислительные функции Они также разделены на подгруппы. К ним относятся математи­че­ские, тригонометрические, статисти­ческие и др. Функции текстовой группы выполняют преоб­ра­зо­вания чисел в ASCII-коды (Американский стандартный код обмена информацией) и обратно, определяют длину данных, выделяют из строк подстроки, объединяют подстроки, удаляют пробелы. Информационные функции определяют состояние яче­ек, выдают типы ошибок. Финан­совые обрабатывают платежи, инвестиции, процентные ставки. Подробная информация обо всех функциях есть в Справке диалоговых окон Мастера функций. Там же можно получить информацию по син­таксису выбранной функции и прототипам ее аргу­мен­тов. Мастер функций приме­няют также при наборе сложных формул с большим числом аргументов; он упро­щает на­бор функций, так как разбивает эту операцию на отдель­ные шаги, выдает подсказки, отобра­жает ре­зультат каждого шага на экране и берет на себя часть работы, добавляя в формулу поля для ввода аргументов, скобки, точки с запятой. Его окно можно перемещать.

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

Автосуммирование. Вызывается кнопкой () на стандартной панели инструментов и предна­значена для быстрого вычисления суммы зна­чений ячеек, расположенных в последова­тель­ных строках или столбцах. Если выделить интервал C3:F5 в представленном ниже фраг­мен­те таблицы и нажать кнопку Автосумма, то все пустые клетки заполнятся суммар­ными значениями.

Чтобы увидеть последовательность обработки чи­сел по заданным формулам, приме­няют Трассировку вычислений  выделяют ячейку с результатом, затем За­висимости-Влияющие ячейки и появляются линии, указывающие порядок использования аргументов при вычислении. А если выделить ячейки-аргументы, задать За­ви­симости-За­висимые ячейки, стрелки укажут результаты.

	C	D	E	F
3	56	78	35
4	67	35	49
5

	C	D	E	F
3	56	78	35	169
4	67	35	49	151
5	123	113	84	320

Логическая функция ЕСЛИ имеет 3 аргумента и позволяет по условию выбирать раз­ные решения. Её синтаксис: ЕСЛИ ( лог_выраж; знач.1; знач2 ).
Семантику (смысл, действие) данной функции можно пояснить следующим образом:

если лог.выраж.(1-й аргумент) - истина, то результат - 2-й аргумент, иначе - 3-й..

Любое логическое выражение (“высказывание”) может иметь одно из двух значений: TRUE (истина) или FALSE (ложь).

В качестве логических выражений используются: - логические отношения  два арифметич. выражения, соединенные символом операции отношения, например, x > k-1; это простое логическое выражение;

- логические одночлены - два или более логич.отношений, соединенные логической опера­цией "И": x > 10 И x < 15 ( "х лежит между 10 и 15"); эту операцию называют также логическим умножением; в Еxcel эти выражения записывают по-другому в префиксной форме  символ логической операции находится перед аргумента­ми): И (x > 10; x < 15) ;

- логические многочлены - два или более логических одночлена, соединенные операцией "ИЛИ" (логическое сложение): ИЛИ ( x=2; y=2; z=2 ) - т.е. "хотя бы одна из трех переменных - x, y, z - равна 2" .Пример использования функции ЕСЛИ в одном из вариантов расчёта подоход­ного налога: =ЕСЛИ ( C4>100000; 20%*C4; 12%*С4) ; здесь функция ЕСЛИ выдаёт два возмож­ных ре­зультата в зависимости от оклада. Если в качестве 3-го аргумента функции ЕСЛИ использо­вать эту же - вложенную - функ­цию, то можно получить 3 решения: = ЕСЛИ ( Х>10; формула1; ЕСЛИ (X<5; формула2; форм.3)).

Функция ЕСЛИ неявно применяется в функциях СУММЕСЛИ и СЧЁТЕСЛИ, вычисляющих сумму и количество тех значений, которые удовлетворяют заданному условию.

Табличные функции предназначены для обработки данных одной или нескольких таб­лиц. В данном разделе рассматриваются функции ВПР, ГПР, ЧАСТОТА и РАНГ.

Функции ВПР (верти­кальный просмотр таблиц  по столбцам) и ГПР (горизонтальный  по строкам) позволяют сопоставить данные двух таблиц  основной и вспомогательной (справоч­ной). Справочные таблицы содержат граничные значения диапазонов, а основ­ные  произво­ль­­ные значения, каждое из которых должно быть отнесено к одному из диапазонов.

Рассмотрим пример обработки результатов тестирования группы студентов по 3 предметам.

Необходимо для каждого студента определить общую оцен­­ку в десятибальной системе по результатам Табл.1 с ис­поль­зованием справочной таблицы Табл.2. Справочная таблица содержит оцен­ки, соответствующие сумме баллов. Если сумма находится в пределах от 1 до 7, то оценка – 2; если от 8 до 14, то – 3 и т.д. Данные в этой таблице должны быть упорядоче­ны.

Д

Рез-ты тестирования Табл.1

Фамилия	Тест1	Тест2	Тест3
Атоян	20	21	22
Витов	9	7	10
Фокина	15	17	14
Ким	19	24	23
Троль	13	18	21
Зотова	22	15	19
Веткин	24	17	17
Шубина	10	10	10
Сомова	13	12	13
Жук	25	23	23
Ромм	13	20	17

ля решения этой задачи можно было бы применить функциюЕСЛИ с самовложением, но коли­чество её вложений ограничено семью, поэтому применим функцию ВПР. Она имеет 4 аргумента:

1) адрес 1-го значения в столбце массива исходных данных;

2) абсолютную ссылку на массив всех значений справочной таблицы;

3) номер столбца, содержащего искомые данные;

4) пусто или истина, что означает соответственно приближённое или точное совпадение исходного значения 1-й таблицы и значения из 1-го столбца справочной таблицы.

Ниже представлено решение этой задачи.

Оценки Табл.2
Сумма баллов	Оценки
0	1
7	2
14	3
21	4
28	5
35	6
42	7
49	8
56	9
63	10

Функция РАНГ возвращает ранг значений в списке значений (их порядковые номера относи­тельно других значений). Например, тестируемых можно распределить по местам, задав 1 тому, кто набрал наибольшее количество баллов, 2 – для второго места и т.д. Функция имеет 3 аргумента:  число в первой ячейке столбца исходных данных;  ссылка на весь исходный столбец в абсолютной адресации;  порядок (0  по возрастанию ранга, 1  по убыванию).

Функция ЧАСТОТА (исходный массив; массив карманов) считает, сколько значений из исходного массива попадают в диапазоны значений, представленные в массиве кар­манов. Если массив карманов содержит числа {a, b, c}, то числа исходного массива распределя­ются по интервалам: (-, a], (a, b], (b, c], (c, ). Таким образом, количество промежутков на 1 больше элемен­тов в массиве карманов. Прежде чем использовать функцию ЧАСТОТА, выделяют свобод­ный массив ячеек, на единицу больший чем массив карманов, и вводят функцию ЧАСТОТА. За исходный массив можно взять массив ячеек в любом столбце таблицы Табл1, за массив карманов – значения из столбца Сумма баллов справочной таблицы Табл2 (в абсолютных адресах). Затем щел­чок в строке формул и  завершение операции одновременным нажатием Ctrl+Shift+Enter (не ОК).

Матричные функции предназначены для обработки двумерных массивов данных  матриц, которые применяются для решения математических задач. В Excel это Математические функции: МОПРЕД – вычис­ление определителя матрицы, МОБР – вычис­ление обратной матрицы, МУМНОЖ – перемно­жение матриц, и в категории Ссылки и массивы ТРАНСП – транспонирование матрицы. Функция МОПРЕД выдает число, поэтому вводится как обычная формула. Осталь­ные функции и операции поэлементной обработки матриц формируют блок ячеек, поэтому они вводятся как массивы. При обработке матриц удобно вводить имена для массивов данных. Так, если две матрицы введены в блоки А1:С2 и Е1:G2 и этим блокам присвоены имена М и N, то формула поэлементного сло­жения матриц будет иметь простой и понятный вид: =M+N (набор формулы завершается групповой операцией ввода).

Ф

ормула =5*М–3*N выполнит поэлементное умножение каждой матрицы на постоянное число, вычитание 13 -15 23 и возвратит результирующую матрицу -11 34 15

Для решения системы уравнений её представляют в матричном виде: АХ=В, где А  матрица коэффици­ентов при неизвестных, Х  массив неизвестных, В  массив свободных членов. Если определитель матрицы не равен нулю, то решение системы можно получить путём перемноже­ния обратной матрицы A^-1 на массив В (с применением функциии МУМНОЖ).

Функции прогнозирования. Одной из наиболее часто используемых возможностей Excel является экстраполяция ряда имеющихся данных с целью оценки характера их изменения и получения прогноза на будущее. Изве­стны различные способы решения этой задачи - простые (на основе арифметической и геометрической прогрессии) и специально разработанные методы. Среди статистических функций Excel есть две функции - Тенден­ция и Рост, вычисляющие соот­ветст­венно линейную и экспоненциальную экстраполяцию. Эти функции имеют по три обязатель­ных аргумента (известные значения y, известные значения x и новые значения х). По первым двум наборам данных – x и y Excel строит зависимость у от х; после обработки этих значений формиру­ет­ся новая линия - тренд (прямая для Тенден­ции и кривая для Роста), которая затем продолжает­ся до заданного нового значения х. Можно проиллюстрировать решение такой задачи на примере. Этот пример достаточно подробно описан и может быть выполнен самостоятельно.

Пример 1. Имеются данные об объемах ежеквартальных продаж за некоторый период.

Требуется получить прогноз динамики роста объемов продаж на ближайший год.

	A	B	C
1	Объем продаж
2			Фактич.
3	1-й кв. 03	1	234300
4	2-й кв. 03	2	269800
5	3-й кв. 03	3	255900
6	4-й кв. 03	4	275500
7	1-й кв. 04	5	262800
8	2-й кв. 04	6	276600
9	3-й кв. 04	7	269100
10	4-й кв. 04	8	288200
11	1-й кв. 05	9
12	2-й кв. 05	10
13	3-й кв. 05	11
14	4-й кв. 05	12

Решение. 1) Открыть новый лист Еxcel, ввести в него данные таблицы; для стол­бцов А и B применить автоза­полнение (числовой столбец В введён и используется в вычислениях вместо А, содержащего текстовые значения).

2) Выполнить линейную экстраполяцию: - установить курсор в С11; - щёлкнуть на значке Вставка функции, - выбрать Тенденцию; - в окне мастера функций задать три аргумента: С3:С10, B3:B10, B11 (последние два аргумента задать в абсолютных адресах) и  OK.

3) Полученный в ячейке С11 результат можно скопировать на три следующие клетки, но более точные данные получатся, если приме­нять эту же функцию для каждой из оставшихся ячеек, используя при этом полученные новые значения.

4

)Cоздать на этом же листе диаграмму-график с линией тренда, для чего выделить диапазон ячеек (A3:A14; C3:C14), щёлкнуть на значке Мастер диаграмм, выбрать График и Готово. Затем изменить диапазон для вертикальной оси (установить 220000-320000) и вставить линию тренда с уравнением:

Диаграмма-Добавить линию тренда-Линейная- Параметры-Показать уравнение на диаграмме.

Отредактировать полученную зависимость по образцу.