Практическое задание 2

(логические, табличные, матричные функции)

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

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

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

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

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

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

Логическая функция ЕСЛИ имеет 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 и т.д. Данные в этой таблице должны быть упорядоче­ны.

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

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

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

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

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

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

ФункцияРАНГвозвращаетранг значений в списке значений (их порядковые номера относи­тельно других значений). Например, тестируемых можно распределить по местам, задав 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 (набор формулы завершается групповой операцией ввода).

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