Материал: ЭКСЕЛЬ2 2013 Методичка Расширенные возможности

11

Microsoft Excel 2013. Уровень 2. Расширенные возможности

Применение различных типов встроенных функций

Кроме ввода формул для выполнения базовых математических операций, таких как сложение, вычитание, умножение и деление, в Microsoft Excel можно использовать большую библиотеку встроенных функций для выполнения других операций.

Обращение к функции: ИМЯ_ФУНКЦИИ(аргумент1;аргумент2;…). Имя функции не может содержать пробелы. Аргументы перечисляются через ; (или запятую – зависит от настроек), количество аргументов зависит от функции.

Вставка функции.

Способ 1. Классический

1.Выделить ячейку для результата.

2.Вызвать мастер функций одним из вариантов:

Нажать кнопку в строке формул .

На вкладке Формулы [Formulas], в группе Библиотека функций [Function Library], выбрать Вставить функцию [Insert Function].

Нажать клавиши Shift + F3 .

3.Ввести текст для быстрого поиска функции, нажать Найти [Go] или выбрать в списке Категория [Or select a category] нужную категорию функций, затем выбрать нужную функцию в списке Выберите функцию [Select a function], нажать

OK.

4.Ввести аргументы функции, нажать OK. Аргументами могут быть числа, адреса других ячеек, вычисляемые выражения и другие функции.

12

Microsoft Excel 2013. Уровень 2. Расширенные возможности

Способ 2. Современный

Для упрощения создания и редактирования формул, удобно использовать автозавершение формул.

1.Выделить ячейку для результата.

2.Ввести с клавиатуры знак = .

3.Ввести с клавиатуры первые буквы имени функции. Под ячейкой ввода автоматически отображается динамический список наиболее близких по именам функций. Всплывающие подсказки помогают сделать оптимальный выбор.

4.Выбрать нужную функцию из предлагаемого списка:

Щелкнуть дважды по подходящей функции.

Стрелочками с клавиатуры выделить функцию и нажать клавишу Tab .

5.Ввести аргументы функции, используя всплывающие подсказки или вызвать окно Аргументы функции [Function Arguments] – нажать или клавиши Shift + F3 .

Влюбой момент, можно вернуться в окно аргументов функции. Для этого выделить ячейку с формулой (если формула состоит из нескольких функций, то щелкнуть в формуле в название

нужной функции) и нажать или клавиши Shift + F3 .

Математические функции

Функции округления

Настройка числового формата в окне Формат ячеек [Format Cells] позволяет на экране получить внешний вид значения, округленного по правилам математики до указанного количества десятичных знаков. Однако в расчетах будет использоваться именно исходное число. Поэтому в результатах последующих вычислений могут возникать погрешности вычислений. К тому же, порой необходимо результат вычисления округлить в большую или меньшую сторону. Для решения таких задач, следует обратить внимание на функции округления.

ОКРУГЛ(Число;Число_разрядов) – округляет число до указанного количества десятичных разрядов (по правилам математики).

ROUND(Number;Num_digits)

=ОКРУГЛ(755,37;1) 755,4

13

Microsoft Excel 2013. Уровень 2. Расширенные возможности

ОКРУГЛВНИЗ(Число;Число_разрядов) – округляет число до ближайшего меньшего по модулю до указанного количества десятичных разрядов.

ROUNDDOWN(Number;Num_digits)

=ОКРУГЛВНИЗ(755,37;1) 755,3

ОКРУГЛВВЕРХ(Число;Число_разрядов) – округляет число до ближайшего большего по модулю до указанного количества десятичных разрядов.

ROUNDUP(Number;Num_digits)

=ОКРУГЛВВЕРХ(755,37;1) 755,4

ОКРВНИЗ(Число;Точность) – округляет число до ближайшего меньшего по модулю целого,

кратному указанному значению.

FLOOR(Number;Significance)

=ОКРВНИЗ(755,37;10) 750

ОКРВВЕРХ(Число;Точность) – округляет число до ближайшего большего по модулю целого,

кратному указанному значению.

CEILING(Number;Significance)

=ОКРВВЕРХ(755,37;10) 760

ЦЕЛОЕ(Число) – округляет число до ближайшего меньшего целого.

INT(Number)

=ЦЕЛОЕ(755,37) 755

ПРАКТИКУМ:

Открыть файл 01_2 Математические функции.

На листе ЗАДАНИЕ1 выполнить задание.

Сохранить сделанные изменения в файле.

Функции сложения

Задачи на суммирование исходных данных встречаются достаточно часто. Можно осуществлять сложение всех числовых аргументов или только значений, которые отвечают заданным критериям.

СУММ(Число1;Число2) – суммирует только числовые аргументы.

SUM(Number1;Number1)

Функцию СУММ [Sum] можно быстро вызывать клавишами ALT + = .

14

Microsoft Excel 2013. Уровень 2. Расширенные возможности Следует помнить, что дата и время тоже являются числовыми данными.

ПРИМЕР: Определить затраты на Доставку.

=СУММ(C2:C7) – суммирует из диапазона ячеек C2:C7 только числовые значения.

СУММЕСЛИ(Диапазон;Критерий;Диапазон_суммирования) – суммирует ячейки, заданные

указанным условием.

SUMIF(Range;Criteria;Sum_range)

Диапазон [Range] – диапазон ячеек, где ответ на критерий (условие).

Критерий [Criteria] – критерий отбора (условие).

Больше >

Меньше <

Не более <=

Не менее >=

Не равно <>

Для текстовых значений: ? – замена 1-го символа, * – замена символов. Примеры использования символа ? и *:

к?т – слово из 3-х букв: первая – к, третья – т и обязательно один символ между ними. Может быть кит, кот, кэт, к-т, к8т и т.д.

*дом – заканчивается на дом дом* – начинается с дом *дом* – содержит дом

Условие сравнения со ссылкой на ячейку: "оператор_сравнения"&Ячейка "<="&D4, где D4 – ячейка, в которой находится число, дата или время.

Диапазон_суммирования [Sum_range] – диапазон суммируемых ячеек. Если Диапазон_суммирования не указан, то будут использоваться ячейки, указанные в поле Диапазон.

15

Microsoft Excel 2013. Уровень 2. Расширенные возможности

ПРИМЕР: Рассчитать объем продаж (кг) Киви.

=СУММЕСЛИ(A:A;"Киви";B:B) – суммирует ячейки из столбца B, если соответствующие им ячейки столбца A содержат слово Киви.

=СУММЕСЛИ(D:D;">"&F1;C:C) – суммирует ячейки из столбца С, если соответствующие им ячейки в столбце D содержат дату, большую даты из ячейки F1.

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

Функция СУММЕСЛИ позволяет суммировать ячейки только при выполнении одного критерия, если критериев несколько, то нужно использовать функцию СУММЕСЛИМН, которая позволяет суммировать ячейки при 127 условиях максимум.

СУММЕСЛИМН(Диапазон_суммирования;Диапазон_условия1;Условие1;) – суммирует

ячейки, удовлетворяющие заданному набору условий.

SUMIFS(Sum_range;Criteria_range1;Criteria1;)

Диапазон_суммирования [Sum_range] – диапазон суммируемых ячеек.

Диапазон_условия [Criteria_range] – диапазон ячеек, где ответ на условие (критерий).

Условие [Criteria] – условие отбора (критерий).

Порядок следования условий значения не имеет, т.к. пока все условия не будут выполнены, значение для расчета суммы не будет выбрано.