1. Условное форматирование
Все специалисты, работающие с большими таблицами и базами данных, стараются придумать различные способы организации, сортировки и систематизации данных: ставят в порядке возрастания/убывания; применяют фильтры, чтобы получить нужную информацию; форматируют таблицы, приводя их в понятный и презентабельный вид. Все эти операции (и не только) можно делать в Excel, используя условное форматирование. C другими возможностями Excel можно ознакомиться на нашем открытом курсе «Аналитика в Excel».
Условное форматирование – обобщающее название для целого набора способов обработки данных. Все эти методы основаны на цветовом выделении ячеек в зависимости от разных критериев и правилах закрашивания.
Например, сравнение с константой; ранжирование по диапазону значений с помощью гистограмм, цветовых шкал и различных значков.
Что очень важно: условное форматирование – это динамическая функция, корректирующая результаты при каждом изменении данных.
Существует множество комбинаций для форматирования, поэтому каждый пользователь быстро найдет подходящий способ для своей задачи из уже существующих методов или создаст свое собственное правило.
Раздел «Условное форматирование» очень легко найти на главной панели быстрого доступа. Как только мы наводим курсор, сразу появляется выпадающий список правил выделения и средств форматирования (диаграммы, шкалы, значки).
При наведении курсора на каждый вы увидите несколько готовых вариантов, которые можно выбрать. Если ни один способ вам не подходит, можно создать свой.
Создадим произвольную таблицу, к которой будем применять форматирование:
Возьмем таблицу, отражающую выручку продавцов за один месяц. Попробуем «раскрасить» ячейки.
Выделим ячейки с одинаковыми значениями.
Выделим ячейки, которые нужно форматировать. В разделе «Условное форматирование» находим «Правила выделения ячеек» и выбираем «Повторяющиеся значения».
Появится диалоговое окно, где вы сможете выбрать – выделять повторяющиеся или уникальные значения, также цвет выделения:
Получим такой результат:
Выделим ячейки С1:С7.
Аналогичным способом откроем «Правила выделения ячеек», но на этот раз закрасим те ячейки, значения которых больше 25 000:
Получим результат:
Можно форматировать таблицы сразу по нескольким условиям. Выделим уже отформатированные ячейки С1:С7 и применим к ним правило: меньше 22 800 и выберем отличный от прошлого дизайн выделения.
Так будет выглядеть наша таблица после применения двойного форматирования:
При необходимости изменения параметров форматирования, дизайна ячеек в разделе «Условное форматирование» выбираем «Управление правилами» и в открывшемся диалоговом окне модифицируем правило так, как нам нужно:
В этом же разделе можно создавать произвольные правила для конкретных случаев и удалять ненужные.
Выберем «Создать правило». В появившемся окне выделим подходящий тип правила (у нас – форматировать ячейки, которые содержат), указываем необходимые параметры (у нас – значения между 22 000 и 25 000) и задаем формат выделений (у нас – жирный шрифт синего цвета):
После всех преобразований таблица будет выглядеть следующим образом:
Замечание 1. Следует аккуратно применять условное форматирование с большим числом правил к одним и тем же данным, особенно, когда много значений удовлетворяют сразу нескольким условиям.
Ведь, как вы уже успели заметить, применяя к данным новое правило, старое тоже сохраняется. Поэтому будет не очень наглядная таблица, в которой сложно ориентироваться, а из-за нагромождения стилей, можно легко запутаться в том, какое форматирование к какому правилу относится!
Замечание 2. Условное форматирование применимо не только к числовым данным, но и к текстовым. Для строк есть встроенное правило «Текст содержит», где указывается фраза и выделяются все ячейки, содержащие её.
В данном разделе очень много полезных приемов для аналитиков, экономистов, бухгалтеров и представителей смежных профессий, помогающих не тратить много времени на простейшие, подготовительные этапы анализа данных.
Например, необходимо выделить 10 лучших позиций в столбце или значения, выше среднего, или первые 10% значений, сохранив при этом исходный порядок (то есть сортировать от большего к меньшему и выделить вручную – не получится). Число 10 можно изменить на другое!
В таком случае приходят на помощь «Правила отбора первых и последних значений»:
Задаем количество первых (по величине значения) ячеек и цвет заливки:
Получаем таблицу с выделенными четырьмя наибольшими значениями:
2. Форматирование с помощью гистограмм
До этого мы рассматривали форматирование с помощью простого выделения ячеек. Теперь познакомимся с более интересным способом.
В Excel можно форматировать с помощью гистограмм. В этом случае в каждой ячейке будет отображаться линия, соответствующая ее длине относительно других ячеек.
Чтобы это сделать, нужно в «Условном форматировании» найти «Гистограммы» и выбрать градиентную или сплошную заливку:
В итоге мы получим таблицу, в которой сразу видно отношение данных друг к другу:
3. Использование значков в сортировке
Порой очень удобно использовать в качестве выделения – значки. То есть каждому знаку можно присвоить свое значение (или интервал значений) и ставить их рядом с теми данными, которые попадают в диапазон.
В «Условном форматировании» есть разные наборы значков, которые можно использовать:
Выбрав подходящий набор значков, каждому нужно присвоить правило, по которому они будут расставляться в ячейках.
Можно ранжировать по процентным соотношениям, расчетам формул или, как у нас, по значениям: галочка, если больше 26 000; восклицательный знак, если больше 22 000, но меньше 26 000; крестик, если меньше 22 000:
4. Форматирование с помощью цветовых шкал
Последний прием, о котором мы поговорим в разделе «Условного форматирование», это использование цветовых шкал.
Для этого выделим данные таблицы, зайдем в «Условное форматирование» → «Цветовые шкалы», где можно выбрать самые разные дизайны раскрашивания:
Мы в таблице хотели сравнить максимальные и минимальные значения. Поэтому выбрали шкалу «красный-желтый-зеленый», которая показывает относительную величину значений с помощью насыщенности оттенков.
Вот какая красивая картинка у нас получилась (максимальной температуре соответствуют темно-красные ячейки, минимальной – темно-зеленые):
Согласитесь, что условное форматирование сильно сокращает время анализа данных, не прибегая к сложным методам.
Конечно, сортировку и визуализацию данных можно сделать и в RStudio, например, с помощью библиотек dplyr и ggplot2. Но для этого требуются знания языка R, недюжинные навыки и опыт в программировании. Если хотите изучить язык R, то советуем начать с нашего открытого онлайн-курса «Аналитика с R и SQL».
А Excel предлагает обработку данных с помощью встроенных конструкций, что делает его очень популярным.
Рассмотрим еще несколько функций, которые смогут пригодиться в работе!
5. Перехват ошибок
Смоделируем еще одну ситуацию. Вы долго делали сложный расчет, создавали новые, объемные формулы, а в результат не получили, потому что вдруг возникла какая-то ошибка.
При этом непонятно: это вы неправильно ввели формулу, отсутствуют какие-то данные или еще что-то. Понадобится много времени на то, чтобы разобраться с проблемой.
Например, нужно было посчитать средний доход с каждого клиента при наличии суммарного дохода и количества клиентов. Это простая формула: дели одно на другое.
Но возникла ошибка «Деление на 0» в ячейке D7. Из-за этого перестала работать формула СРЕДНЕЕ(D2:D10), использующая в расчетах значение этой ячейки.
На помощь в данном случае к нам придет функция ЕСЛИОШИБКА(значение; значение_если_ошибка). Она проверяет, есть ли в формуле или ячейке ошибка, и если есть, то выводит заданное значение (0, пустую строчку – “ ”, число или другое).
Аргументы функции:
§ «значение» – то, что проверяем;
§ «значение_если_ошибка» – то, что вставляем вместо ошибки.
В нашем случае формулу можно было исправить так:
После применения формулы ко всем строкам ошибка исчезнет, и мы получим итоговый ответ.
Замечание. Функция ЕСЛИОШИБКА() появилась лишь в MS Excel 2007. В более ранних версиях была функция ЕОШИБКА(). Они выводили только значения ИСТИНА/ЛОЖЬ и не могла обрабатывать ошибки. Поэтому её использовали в связке с формулой ЕСЛИ().
Для нашего примера это выглядело бы так: =ЕСЛИ(ЕОШИБКА(С2/B2);0), то есть, если ошибка возникает, то выводим в ячейке «0».
Есть еще функция ЕОШ(), которая отличается от ЕОШИБКА() только тем, что не считает #Н/Д за ошибку.
#Н/Д в основном возникает, когда не хватает данных.
6. Функция СЧЁТЕСЛИ(). Обязательно через Ё!
Функция СЧЁТЕСЛИ (диапазон; критерий) считает в заданном диапазоне количество непустых ячеек, значения которых удовлетворяют введенному критерию.
Аргументов у функции всего два:
§ Диапазон – область, в которой происходит поиск. Она может содержать числовые, текстовые значения, ссылки и массивы.
§ Критерий – условие, которому должны удовлетворять ячейки.
В качестве критериев могут выступать выражения со знаками <,>,=, числа, текстовые строки, ссылки и так далее.
Замечание. Обязательно критерий нужно заключать в кавычки! Или писать условие в отдельной ячейке без кавычек, а в функцию передавать ссылку на ячейку.
На примерах рассмотрим, какие еще особенности есть у функции СЧЁТЕСЛИ().
Возьмем произвольную таблицу инвентаризации:
Найдем количество наименований, количество которых больше 110:
Аналогичным образом можно использовать знаки «меньше» и «равно».
Можно писать критерии со знаком “< >” (не равно), но тогда нужно добавлять оператор & (амперсанд), который будет объединять два условия.
Например, найдем, сколько строк в диапазоне отличны от «столы»:
Найдем, сколько раз в столбце «Наименования» встречаются «табуреты»:
Если нужно найти все ячейки, содержащие, например, однокоренные слова или формы слов, нужно использовать значок «*». Если нам нужно совпадение первой части слова, тогда после нее ставится *, если важен только конец слова, то тогда «*» ставится перед сочетанием букв.
Например, сколько слов начинаются на «ст*»:
Теперь посмотрим, сколько слов заканчивается на «*ы»:
Часто нужно считать ячейки по нескольким критериям.
Например, посчитать «стулья» и «кровати». Для этого нужно сложить две функции СЧЁТЕСЛИ():
Соответственно, сколько условий будет, столько функций СЧЁТЕСЛИ() и нужно сложить.
Сделаем следующий запрос: каково количество ячеек, значение которых «>=20», но «<=100»?
Давайте посмотрим:
Можно придумать еще много разных примеров с функцией СЧЁТЕСЛИ(), но мы пойдем дальше.
7. Функция СЧЁТЕСЛИМН()
Функция СЧЁТЕСЛИМН() тоже выводит число ячеек, удовлетворяющих условию, только здесь можно указывать до 127 диапазонов и условий.
Например, найдем скольких наименований мебели, начинающихся на «с*», на складе больше 100 штук. Для этого в качестве первого диапазона выберем А2:А11, в качестве второго – В2:В11.
8. Перенос формул
Небольшое замечание по поводу переноса формул мы решили оставить здесь, в этой статье, потому что об этой полезной уловке знают не все.
Как перенести большие формулы, которые содержат ссылки на ячейки, так, чтобы при переносе ссылки не изменялись, чтобы не пришлось менять их вручную и перепроверять правильность формул?
Все очень просто: замените знак «=» в формуле на любой другой, например, «!». Тогда это уже будет не формула, а символьная строка, которая будет перемещаться без изменений!
После того, как вы перенесете ее в нужную ячейку, сделайте обратную замену на «=». В этом случае вы можете быть полностью уверены в том, что формула не изменится.
Заключение
Существует еще очень много функций, о которых мы редко вспоминаем, но которые должны быть постоянно в нашем арсенале, например:
- СУММЕСЛИ(), СУММЕСЛИМН() – суммируют значения ячеек, удовлетворяющих условиям;
- ПСТР() – возвращает заданное количество знаков, начиная с указанного;
- СЦЕПИТЬ() – объединяет несколько строк и другие. Они простые и не требуют долгого изучения.
Попробуйте познакомиться с ними самостоятельно, а если возникнут какие-то сложности, то пишите нам в комментариях, и мы поможем вам со всем разобраться!