Функции и вычисления
(логические, табличные, матричные функции, прогнозирование)
Excel имеет более 500 встроенных функций. Любую из них можно вводить с клавиатуры непосредственно в строке формул окна Excel или используя Мастер функций (Вставка-Функция или
значок fx). Функция в Excel - это имя функции и аргументы в круглых скобках, которые разделяются точкой с запятой. Аргументами в функциях могут быть константы и адреса переменных (ссылки).
Функция СУММ (А1;В5;С10) выдаст сумму чисел, заданных в аргументах-ссылках; МИН(С2:Е4) найдёт минимальное среди чисел указанного в аргументе диапазона ячеек. В качестве аргументов могут применяться другие (вложенные) функции; например, СУММ (МИН (C2:E4)); A1; B5; C10).
Все функции разделены на группы. Самую большую группу составляют вычислительные функции Они также разделены на подгруппы. К ним относятся математические, тригонометрические, статистические и др. Функции текстовой группы выполняют преобразования чисел в ASCII-коды (Американский стандартный код обмена информацией) и обратно, определяют длину данных, выделяют из строк подстроки, объединяют подстроки, удаляют пробелы. Информационные функции определяют состояние ячеек, выдают типы ошибок. Финансовые обрабатывают платежи, инвестиции, процентные ставки. Подробная информация обо всех функциях есть в Справке диалоговых окон Мастера функций. Там же можно получить информацию по синтаксису выбранной функции и прототипам ее аргументов. Мастер функций применяют также при наборе сложных формул с большим числом аргументов;онупрощает набор функций, так как разбивает эту операцию на отдельные шаги, выдает подсказки, отображает результат каждого шага на экране и берет на себя часть работы, добавляя в формулу поля для ввода аргументов, скобки, точки с запятой. Его окно можно перемещать.
Если в процессе набора формулы нужна вложенная функция, её имя вставляют из открывающегося списка функций слева в строке формулы, при этом появляется новое окно. После ввода аргументов вложенной функции в её окне вместо ОК делают щелчок в поле ввода основной функции в строке формулы, чтобы вернуться в её окно и закончить набор.
Автосуммирование. Вызывается кнопкой (S) на стандартной панели инструментов и предназначена для быстрого вычисления суммы значений ячеек, расположенных в последовательных строках или столбцах. Если выделить интервал C3:F5 в представленном ниже фрагменте таблицы и нажать кнопку Автосумма, то все пустые клетки заполнятся суммарными значениями.
Чтобы увидеть последовательность обработки чисел по заданным формулам, применяют Трассировку вычислений - выделяют ячейку с результатом, затем Зависимости-Влияющие ячейки и появляются линии, указывающие порядок использования аргументов при вычислении. А если выделить ячейки-аргументы, задать Зависимости-Зависимые ячейки, стрелки укажут результаты.
C | D | E | F | |
C | D | E | F | |
Логическая функция ЕСЛИимеет 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 (набор формулы завершается групповой операцией ввода).
|
каждой матрицы на постоянное число, вычитание 13 -15 23
и возвратит результирующую матрицу -11 34 15
Для решения системы уравнений её представляют в матричном виде: АХ=В, где
А - матрица коэффициентов при неизвестных, Х - массив неизвестных, В - массив свободных членов. Если определитель матрицы не равен нулю, то решение системы можно получить путём перемножения обратной матрицы A-1 на массив В (с применением функциии МУМНОЖ).
Функции прогнозирования. Одной из наиболее часто используемых возможностей Excel является экстраполяция ряда имеющихся данных с целью оценки характера их изменения и получения прогноза на будущее. Известны различные способы решения этой задачи - простые (на основе арифметической и геометрической прогрессии) и специально разработанные методы. Среди статистических функций Excel есть две функции - Тенденция и Рост, вычисляющие соответственно линейную и экспоненциальную экстраполяцию. Эти функции имеют по три обязательных аргумента (известные значения y, известные значения x и новые значения х). По первым двум наборам данных – x и y Excel строит зависимость у от х; после обработки этих значений формируется новая линия - тренд (прямая для Тенденции и кривая для Роста), которая затем продолжается до заданного нового значения х. Можно проиллюстрировать решение такой задачи на примере. Этот пример достаточно подробно описан и может быть выполнен самостоятельно.
Пример 1. Имеются данные об объемах ежеквартальных продаж за некоторый период.
Требуется получить прогноз динамики роста объемов продаж на ближайший год.
A | B | C | |
Объем продаж | |||
Фактич. | |||
1-й кв. 03 | |||
2-й кв. 03 | |||
3-й кв. 03 | |||
4-й кв. 03 | |||
1-й кв. 04 | |||
2-й кв. 04 | |||
3-й кв. 04 | |||
4-й кв. 04 | |||
1-й кв. 05 | |||
2-й кв. 05 | |||
3-й кв. 05 | |||
4-й кв. 05 |
Решение. 1) Открыть новый лист Еxcel, ввести в него данные таблицы; для столбцов А и B применить автозаполнение (числовой столбец В введён и используется в вычислениях вместо А, содержащего текстовые значения).
2) Выполнить линейную экстраполяцию: - установить курсор в С11; - щёлкнуть на значке Вставка функции, - выбрать Тенденцию;
- в окне мастера функций задать три аргумента: С3:С10, B3:B10, B11 (последние два аргумента задать в абсолютных адресах) и - OK.
3) Полученный в ячейке С11 результат можно скопировать на три следующие клетки, но более точные данные получатся, если применять эту же функцию для каждой из оставшихся ячеек, используя при этом полученные новые значения.
4) Cоздать на этом же листе диаграмму-график с линией тренда, для чего выделить диапазон ячеек (A3:A14; C3:C14), щёлкнуть на значке Мастер диаграмм, выбрать График и Готово. Затем изменить диапазон для вертикальной оси (установить 220000-320000) и вставить линию тренда с уравнением:
Диаграмма-Добавить линию тренда-Линейная-
Параметры-Показать уравнение на диаграмме.
Отредактировать полученную зависимость по образцу.
Дата добавления: 2014-12-13; просмотров: 822;