Урок 3. Формулы. Условное форматирование.
Формулы
Чтобы задать формулу для ячейки, необходимо активизировать ее (поставить курсор) и ввести равно (=). Так же можно вводить знак равенства в строку формул. После введения формулы нажать Enter. В ячейке появится результат вычислений.
Символы “+,-,*” и т.д. называются операторами, т.е. указывают на выполняемое действие.
Числа называются операндами, т.е. объектами над которыми будут выполнены действия.
Сложные формулы
Сложные формулы включают в себя ссылки на ячейки или диапазоны ячеек, функции, вложенные функции, гиперссылки и условные операторы.
Функция СУММЕСЛИ позволяет суммировать ячейки, которые удовлетворяют определенному критерию (заданному условию).
Аргументы команды следующие:
-Диапазон – ячейки, которые следует оценить на основании критерия (заданного условия).
-Критерий – определяет, какие ячейки из диапазона будут выбраны (записывается в кавычках).
-Диапазон суммирования – фактические ячейки, которые необходимо просуммировать, если они удовлетворяют критерию.
Получается, что у функции всего 3 аргумента. Но иногда последний может быть исключен, и тогда команда будет работать только по диапазону и критерию.
Рассмотрим пример, который наглядно продемонстрирует, как использовать функцию СУММЕСЛИ и насколько удобной она может оказаться при решении определенных задач.
Имеем таблицу, в которой указаны фамилии сотрудников, их пол и зарплата, начисленная за январь-месяц. Если нам нужно просто посчитать общее количество денег, которые требуется выдать работникам, мы используем функцию СУММ, указав диапазоном все заработные платы.
Но как быть, если нам нужно быстро посчитать заработные платы только продавцов? В дело вступает использование функции СУММЕСЛИ:
-Прописываем аргументы.
-Диапазоном в данном случае будет являться список всех должностей сотрудников, потому что нам нужно будет определить сумму заработных плат. Поэтому проставляем E2:E14.
-Критерий выбора в нашем случае – продавец. Заключаем слово в кавычки и ставим вторым аргументом.
-Диапазон суммирования – это заработные платы, потому что нам нужно узнать сумму зарплат всех продавцов. Поэтому F2:F14.
Получилось 92900. Т.е. функция автоматически проработала список должностей, выбрала из них только продавцов и просуммировала их зарплаты.
Аналогично можно подсчитать зарплаты других сотрудников. Когда таблица небольшая, кажется, что все можно сосчитать и вручную, но при работе со списками, в которых по несколько сотен позиций, целесообразно использовать СУММЕСЛИ.
Домашнее задание №1:
1.Скопируйте на Лист АББ2 таблицу из предыдущего урока и добавьте в нее столбец Сумма.
2.Проставьте формулу в каждой ячейке, чтобы посчитать сумму каждого изделия.
3.Посчитайте Итоговую сумму.
4.С помощью функции СУММЕСЛИ посчитайте общую стоимость всей одежды, аксессуаров, обуви.
Условное форматирование
Условное форматирование помогает обеспечивать наглядность при изучении и анализе данных, обнаружении критических проблем, выявлении шаблонов и тенденций
Инструмент “Условное форматирование” находится на главной странице в разделе “Стили”.
При нажатии на стрелочку справа открывается меню для условий форматирования.
Выделите диапазон значений. Откройте меню “Условного форматирования”. Выберите то правило, которое нужно задать.
С помощью условного форматирования можно легко выделять необходимые ячейки или диапазоны, подчеркивать необычные значения и визуализировать данные с помощью гистограмм, цветовых шкал и наборов значков.
При применении условного форматирования внешний вид ячеек изменяется на основании указанных вами условий.
Домашнее задание №2:
1.Скопируйте на Лист АББ3 таблицу и создайте правило форматирования к столбцу "Количество" вашей таблицы.
Тип правила выберите "Форматировать все ячейки на основании их значений".
В описании правил укажите:
Стиль формата: набор значков
Стиль значка: выберите 1-й вариант светофора
Тип: Число
Зеленый значок присвойте числам значение которых >=40
Желтый значок числам, которые >=30
Красный значок числам <30
2.Создайте правило форматирования к столбцу "Цена за шт.".
Тип правила выберите "Форматировать все ячейки на основании их значений".
В описании правил укажите:
Стиль формата: трехцветная шкала
Тип: во всех трех ячейках - Число
Минимальное значение укажите 3000, среднее значение 10000,
максимальное значение 25000.
Цвета выберите красный - минимальное значение, желтый - среднее значение, зеленый - максимальное значение.
Должна получиться следующая таблица:
Внимание! Задания после каждого урока просим выполнять на отдельных листах Excel. В этом уроке 2 домашних задания. Все домашние задания прислать одним файлом, после прохождения всех уроков.