Продолжаем наш эксель.
Нет я нитормаз. Просто так получилось.
Сегодня я расскажу об достаточно интересной функции, позволяющей быстро выделять какую-либо нужную вам информацию в массиве данных.
Официально она называется "Условное форматирование".
Для чего она нужна?
Допустим у вас есть здоровая грустная монотонная таблица. Чтобы найти в ней информацию (допустим максимальные значения по продажам у менеджеров) необходимо либо долго и муторно её искать, либо заниматься сортировкой. Причём, в случае, если данные поменяются - сортировку придётся делать по новой.
Условное же форматирование будет делать это автоматически, выделяя самостоятельно цветами ту информацию, которую вы ей задатите.
Пример. На этой картинке зеленым выделены строки, где количество товара, который лежит на складе и товара который ожидается в поставке превышает минимальный запас.
Красным выделены те строки, где количество товара, лежащего на складе и ожидающегося в поставке меньше, чем минимальный запас.
Что позволяет менеджеру по закупкам не заниматься каждый раз сортировкой, а сразу видеть на какие позиции необходимо обратить внимание.
Как это сделано?
Я для каждого цвета задал правило. Как оно работает? Берется ячейка С, складывается с ячейкой D, и смотрится, в случае если их сумма превышает число в ячейке Е - то эти ячейки будут залиты.
Теперь подробнее. Нажимаем условное форматирование.
Видим выпадающую менюшку. В ней есть несколько пунктов, первые 5 пунктов - уже заранее созданные стандартные правила. Их достаточно просто применять, тут их описывать не будут. Если кому-то будет непонятно как - задайте вопрос в комментах - помогу.
А вот нижние 3 пункта позволяют писать нам практически любое правило в виде формулы и управлять правилами на листе.
Я использовал пункт создать правило. Получаем такое окошко. Первые 5 пунктов окошка - опять же какие-то готовые стандартные правила. А вот 6 как раз позволяет нам писать правила формулой.
Как это сделать?
Правило начинается всегда со знака равно. Затем нам необходимо написать математическое выражение которое мы будем проверять. В нашем случае это была проверка - больше ли сумма столбца С и столбца D, чем число в столбце E. Затем по кнопке "Формат" нам надо выбрать тот формат, которым заливать ячейки, соответствующие правилу.
На этом скриншоте я уже написал формулу и выбрал зеленый формат. Обратите внимание формула у нас со значками доллара, о которых я рассказывал в прошлой лекции. Почему я их поставил - чуть позже.
Жмем окей.
И сразу понимаем, что ничего не произошло.
И вот тут очень важный момент!
Справа есть столбец "применяется к". Дело в том, что я нажал кнопку условное форматирование тогда, когда у меня была выделена ячейка F2. И соответственно правило написалось только для неё. Но нам то надо применять правило на других ячейках. Соответственно, если вы забыли заранее выделить нужный диапазон, просто жмите на "применяется к". Стирайте то, что там не нужно, и выбирайте то, что нужно.
Выбрал то, что нужно. Жмёте окей. Вуаля получаете заливку. Теперь, когда данные в таблице будут изменяться - таблица будет автоматически перезаливаться.
2 важных момента:
1. Откуда взялись доллары:
Для проверки я взял диапазон С2:Е20. Проверять мы хотим сумму в стоблце С,D и сравнивать с столбцом Е. Появились они из-за логики работы этой функции в экселе.
Эксель берет 1 столбце 1 строки выбранного диапазона и сравнивает для него написанное правило.
В нашем случае 1 столбец 1 строки это ячейка C2.
Эксель берёт формулу С2+D2>E2 и проверяет выполняется ли она.
Ествественно для ячейки С2 она выполняется.
Далее эксель берет ячейку D2. И если мы не закрепим долларами столбцы то для ячейки D2 он уже будет проверять D2+E2>F2 вместо нашей формулы. Т.е. произойдет смещение.
Если же столбцы закрепить как у меня, то для ячейки D2 будет проверяться написанная нами формула C2+D2>E2.
Ну и таким образом эксель гоняет каждую ячейку.
2. момент.
Если вы выбрали диапазон для проверки с С2, то и формулу вы должны писать с С2.
Если вы выбрали диапазон с С1, или вообще целиком столбцы С:Е, то формулу вы должны писать с ячейки С1. Посмотреть это на примере можно на 2 фотографии. Там в одном случае я написал формулу для С2:Е20, а в другом случае для столбцов С:Е. И формула соответственно тоже разная.
В случае, если вы выберете диапазон с С1, а формулу напишете с С2, то весь результат выделения у вас сместится на 1 строку. Т.к. для ячейки С1, будет браться формула для проверки с С2.
Для начала хватит про условное форматирование. Чуть позже расскажу о более сложных примерах применения. Например как сделать диаграмму ганта. С помощью УФ.
Надеюсь ничего не забыл. В любом случае на вопросы в комментах отвечу.
