Функции для анализа списка

Примеры диапазонов критерия.

Пример 2: Имя Отчество

Иван

Иванович

В этом случае будут отобраны люди с именем Иван или с отчеством Иванович (т.е. все Иваны с любыми отчествами и все Ивановичи с любыми именами).

Пример 3: Фамилия

А*

Б*

В*

Будут отобраны люди, чья фамилия начинается или на букву А, или на Б, или на В.

Справка по математическим функциям:

Ввод функций. Функции могут вводиться несколькими способами.

После ввода в ячейку знака «=» функция может быть введена непосредственно с клавиатуры.

Другой вариант: на вкладке Формулы щелкнуть Вставить функцию (появится окно Мастер функций), далее Категория, Математические, SIN.

Иначе: щелчок на значке fx слева от строки формул, (появится окно Мастер функций).

Некоторые встроенные функции:

ABS(A3) – абсолютное значение числа в ячейке А3, СУММ() – сумма, ПРОИЗВЕД() – произведение, КОРЕНЬ() – квадратный корень,, LN- натуральный логарифм, LOG10- десятичный логарифм, EXP – экспонента, СТЕПЕНЬ(x,a) = x^a, SIN,COS, TAN -тангенс,ASIN -арксинус, ACOS -арккосинус, ATAN -арктангенс, КОРЕНЬ- квадратный корень.

Вычисления в таблицах:

Ввод формулы в ячейку: Формула начинается со знака =, например, =C3*D3 (содержимое ячейки С3 умножается на D3;результат отображается в ячейке с формулой). Можно также использовать готовую встроенную функцию, которая выбирается из списка. При использовании в функции нескольких аргументов они отделяются один от другого точкой с запятой (ПРОИЗВЕД(A1;B2;C4)). Однако, любой аргумент может быть диапазоном (СУММ(A1:A3;B2:B4) – суммирует все числа в отмеченном диапазоне).

Автосумма. Выделить диапазон (столбец или строку). На Формула-Библиотека функций щелкнуть на треугольнике списка Автосумма, щелкнуть Сумма. В свободной ячейке рядом с выбранным диапазоном появится значение искомой суммы. Аналогично находятся среднее значение, максимальное и минимальное значения.

Суммирование по строке (или по столбцу). Выделить ячейку в конце строки, f_x, выбрать Математические, выбрать СУММ, ОК, ОК. То же можно сделать набрав формулу СУММ(диапазон).

Эти функции дают информацию об элементах списка (таблицы), которые удовлетворяют некоторым условиям:

(1) Функция СЧЕТЕСЛИ (диапазон; условие) подсчитывает количество записей (строк таблицы), удовлетворяющих одному условию по данным, записанным в указанном диапазоне ячеек. Принадлежит категории статистические функции.

Пример 1. Количество сотрудников с именем Иван в диапазоне B2:B7 вычисляется с помощью формулы =СЧЕТЕСЛИ(B2:B7; “Иван”). Здесь символ “ означает двойные латинские кавычки.

Пример 2. Количество двоек в ряде чисел: =СЧЕТЕСЛИ(B2:B7; 2)

Пример 3. Пусть таблица содержит данные по продажам телевизоров (строка – продажа). Столбец В содержит названия телевизоров. Требуется подсчитать, сколько всего было продаж телевизоров Panasonic (без учета количества штук).

Последовательность действий: курсор в D12, вызвать мастер функций, нажав f_x в строке формул, выбрать в категории Статистические функции функцию СЧЕТЕСЛИ, ОК. В открывшемся окне в верхнем поле ввести диапазон в столбце В (например, B4:B11), ниже –критерий отбора (“ Panasonic*”. В результате формула будет иметь вид: =СЧЕТЕСЛИ (B4:B11; “ Panasonic*”). Нажать ОК.

(2) Функция СУММЕСЛИ (диапазон; условие; диапазон суммирования) суммирует значения, удовлетворяющие одному заданному условию (раздел Математические функции).

Пример. Пусть таблица содержит данные по продажам телевизоров (строка – продажа), в столбце В находятся названия телевизоров, а в столбце С – количество купленных штук. Требуется подсчитать (и поместить результат в D12), сколько всего было продано телевизоров Panasonic (в таблице имется несколько таких моделй: PanasonicTX-32LE, Panasonic32LM, …).

Последовательность действий: курсор в D12, вызвать мастер функций, нажав f_x в строке формул, выбрать в категории М атематические функции функцию СУММЕСЛИ, ОК. В открывшемся окне в верхнем поле ввести диапазон в столбце В (например, B4:B11; можно также выделить диапазон на листе), ниже – критерий отбора (“ Panasonic*” (при вводе апострофы ” ” можно не вводить), здесь звездочка заменяет любой набор символов), ниже - диапазон суммирования (С4:С11). В результате формула будет иметь вид: СУММЕСЛИ (B4:B11; “ Panasonic*”; С4:С11). Нажать ОК.

(3) Определение числа записей, удовлетворяющих нескольким условиям с помощью функции СЧЕТЕСЛИМН (раздел Статистические функции).

Отличие от предыдущего случая (с функцией СЧЕТЕСЛИ):

-в открывшемся окне Аргументы функции вначале видны два поля. После заполнения поля Диапазон условия1 открывается поле Условие 1 и т.д.

Пример: необходимо вычислить, сколько было продаж телефонов (без учета количества штук) Nokia филиалом Мобик.

В результате: СЧЕТЕСЛИМН (А2:А10; “ Nocia*”;D2:D10;” Мобик '').

(4) Суммирование значений, удовлетворяющих нескольким условиям с помощью функции СУММЕСЛИМН.

Отличия от предыдущего случая (с функцией СУММЕСЛИ): (1) в открывшемся окне Аргументы функции вначале видны два поля. После заполнения поля Диапазон условия1 открывается поле Условие 1 и т.д.; (2) в формуле для функции порядок аргументов – другой.

Пример: необходимо вычислить, сколько было продано телефонов Nokia филиалом Мобик. Пусть модель телефона содержится в столбце А, название филиала – в столбце D, количество проданных штук – в столбце С. Тогда (эту формулу можно ввести в ячейку и без f_x):

СУММЕСЛИМН (С2:С10;А2:А10; “ Nocia*”;D2:D10;” Мобик '').

Функция СУММПРОИЗВ (очень часто используемой при решении задач оптимизации)

Осуществляет перемножение элементов нескольких столбцов (строк) и суммирование по строкам(столбцам). Например, пусть в диапазоне A1:A3 листа Excel находятся числа 2, 4, 3, а в диапазоне B1:B3 – числа 6, 1, 5. СУММПРОИЗВ(A1:A3;B1:B3) дает сумму произведений Ai*Bi по i=1,2,3: 2*6+4*1+3*5.

Статистические функции:

- СРЗНАЧ(A1:G1) вычисляет среднее арифметическое семи первых чисел в 1-й строке,

- МАКС(A1:D4) выводит наибольшее число в заданном диапазоне.

Дата добавления: 2017-01-13