4. Формулы

Формула - специальный инструмент Excel, предназначенный для расчетов, вычислений и анализа данных. Формула может включать в себя константу, оператор, ссылку, имя ячейки (диапазона) и функцию

Чтобы задать формулу для ячейки, необходимо активизировать ее (поставить курсор) и ввести равно (=). Так же можно вводить знак равенства в строку формул. После введения формулы нажать Enter. В ячейке появится результат вычислений.

Символы “+,-,*” и т.д. называются операторами, т.е. указывают на выполняемое действие.

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

Сложные формулы

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

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

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

Рассмотрим несколько примеров.

Операторы ЕСЛИ позволяют выполнять логические сравнения условий. Обычно оператор ЕСЛИ означает следующее: если условие имеет значение ИСТИНА, следует выполнить некое действие, в противном случае необходимо выполнить другое действие. Формулы могут возвращать текст, значения или даже другие вычисления

1. В ячейке D9 введите =ЕСЛИ(C9="Яблоко";ИСТИНА;ЛОЖЬ). Правильный ответ — ИСТИНА.
2. Скопируйте ячейку D9 в D10. В ней должно появиться значение ЛОЖЬ, так как апельсины и яблоки — разные вещи.

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

Аргументы команды следующие:

-Диапазон – ячейки, которые следует оценить на основании критерия (заданного условия).

-Критерий (значение, которое нужно найти) – определяет, какие ячейки из диапазона будут выбраны (записывается в кавычках).

-Диапазон суммирования – фактические ячейки, которые необходимо просуммировать, если они удовлетворяют критерию.

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

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

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

Но как быть, если нам нужно быстро посчитать заработные платы только продавцов? В дело вступает использование функции СУММЕСЛИ:

-Прописываем аргументы.

-Диапазоном в данном случае будет являться список всех должностей сотрудников, потому что нам нужно будет определить сумму заработных плат. Поэтому проставляем E2:E14.

-Критерий выбора в нашем случае – продавец. Заключаем слово в кавычки и ставим вторым аргументом.

-Диапазон суммирования – это заработные платы, потому что нам нужно узнать сумму зарплат всех продавцов. Поэтому F2:F14.

Получилось 92900. Т.е. функция автоматически проработала список должностей, выбрала из них только продавцов и просуммировала их зарплаты.

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

ВПР — одна из самых популярных (и наших любимых!) функций Excel. Она позволяет найти значение в столбце слева, а затем вернуть данные из другого столбца справа. У функции ВПР следующий синтаксис:

Для закрепления информации предлагаем выполнить задание.

Задание высылать не обязательно, оно поможет вам в выполнении итогового задания. Если у вас возникают вопросы по выполнению, напишите в чат.

Задание:

1.Скопируйте на Лист 2 таблицу из предыдущего урока и добавьте в нее столбец Сумма.

2.Проставьте формулу в каждой ячейке, чтобы посчитать сумму каждого изделия.

3.Посчитайте Итоговую сумму.

4.С помощью функции СУММЕСЛИ посчитайте общую стоимость всей одежды, аксессуаров, обуви.

* В некоторых роликах вы услышите о том, что информация относится к Microsoft Excel 2010г., эти же функции также работают и в версиях 2013 и 2016 гг.