Функции и вычисления

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

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 (набор формулы завершается групповой операцией ввода).

Формула =5*М–3*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; просмотров: 814;


Поиск по сайту:

При помощи поиска вы сможете найти нужную вам информацию.

Поделитесь с друзьями:

Если вам перенёс пользу информационный материал, или помог в учебе – поделитесь этим сайтом с друзьями и знакомыми.
helpiks.org - Хелпикс.Орг - 2014-2024 год. Материал сайта представляется для ознакомительного и учебного использования. | Поддержка
Генерация страницы за: 0.014 сек.