Excel works
Excel works!
Правила в условном форматировании. Написание формул
На деньках столкнулся с неувязкой. Человек отлично знал способности условного форматирования, но не додумывался, что можно задать формулу, в зависимости от которой будут изменяться цвета. А ведь это комфортно. Задал правила в условном форматировании, поставил в ячейке необходимое число либо текст, а строчка подсвечивается по формуле. Зрительно с файлом сходу легче работать. Желал отдать свою статью прочесть, а оказывается по данной для нас теме статьи и нет. Исправляюсь.
Поначалу «два слова» о том, что такое условное форматирование. Это функция Excel, позволяющая выделять ячейки цветом либо форматированием текста по условиям. Весьма тщательно о этом я написал в данной для нас статье . Как применять формулы труднее, напишу ниже.
Правила в условном форматировании
Начнем с обычный формулы. Перейдем в Условное форматирование — Управление правилами
В открывшемся окне нажимаем Сделать правило, потом находим самый нижний пункт Употреблять формулу для…
В окне ниже (Форматировать значения, для которых…) уже записываем формулу. Опосля задаем подходящий формат, я избрал зеленоватый фон.
Окно конфигурации правил в условном форматировании
Нажимаем ОК и возвращаемся в Диспетчер правил. Тут уже мы лицезреем перечень сделанных критерий:
Если написанной формулы не видно в пт «Показать правила форматирования для:» избираем Этот лист либо Эта книжка
Жмем Применить. Так можно проверить, что нужные ячейки подсветились зеленоватым.
К слову, формулу можно написать и проверить в Excel заблаговременно. К примеру, так:
Условное форматирование для спектра ячеек
Часто нужно поменять форматирование по правилу во всей строке таблицы.
Для этого в диспетчере правил необходимо избрать подходящий спектр в столбце «Применяется к:»
Направьте внимание, что если форматирование распространяется от сроки к строке, то перед номером строчки в формуле (в нашем случае ЕСЛИ) не ставим $.
Главные формулы для условного форматирования
Набор главных формул, которые я использую
Принципиально добавить
— Как вы увидели по моим правилам, я не использую абсолютные ссылки на спектр при условии. Если вы делаете условие в спектре построчно, то номера строк недозволено созодать абсолютными, т.е. ставить символ $
— В написании правил недозволено ссылаться на остальные листы.
— Не забудьте, что если работаете с датами и временем, то они воспринимаются Excel’ем как число.
Условное форматирование в Excel. Примеры, цветовая шкала, наборы значков
Являясь массивным инвентарем для работы с электрическими таблицами, программка Microsoft Excel предоставляет юзерам большой набор способностей. Данный продукт из пакета Microsoft Office уважают не только лишь простые офисные работники, да и суровые спецы, которые с фуррором пользуются им для классификации и обработки статистических, математических и остальных данных. Есть люди, которые употребляют Excel просто как набор таблиц со интегрированным автоматическим калькулятором. И почти все даже не догадываются о очень нужных и увлекательных функциях данной для нас программки, одной из которых является условное форматирование. В Excel оно дозволяет делать ряд задач по анализу и сортировке данных, делает сведенную в таблицу информацию наиболее приятной и даже помогает выделить в ней тенденции либо ошибки.
Работа с большенными таблицами Excel
Допустим, что на одном из листов в рабочей книжке Excel сотворена большая таблица с данными. Может быть, это сводка по прибыли и иным показателям эффективности компании в разбивке по годам либо кварталам за пару лет, динамика температуры и влажности воздуха для определенного городка по денькам недельки в течение месяца либо неважно какая иная информация, содержащая огромное количество различных характеристик.
Чтоб данная таблица имела некое значение и помогала судить о каких-то действиях, ее непременно нужно проанализировать. Но даже для специалиста этот массив данных на 1-ый взор будет смотреться обычным набором цифр, в котором нужно находить какие-то определенные моменты.
Для что необходимо условное форматирование ячеек в Excel?
К примеру, чтоб отыскать самую большую прибыль компании либо его выручку за обозначенный период, придется пробежаться очами по всем ячейкам с данными пару раз, повсевременно сравнивая характеристики и держа в голове много разных цифр. Когда разыскиваемое значение будет найдено, нужно будет как-то выделить его и приступить к предстоящему анализу сведенных в таблицу данных. Характеристик, которые предстоит выделить из этого массива цифр, может оказаться весьма много, и для поиска всякого придется не один раз полностью просматривать весь массив, при этом весьма пристально, чтоб не упустить из вида данные за какой-либо период.
Конкретно в таковых непростых ситуациях работнику придет на помощь условное форматирование ячеек в Excel. Благодаря этому инструменту можно без усилий выявить числа, пригодные под данные условия, и создать определенный формат для ячеек, в которых размещены данные, удовлетворяющие сиим условиям. Совершив всего несколько манипуляций, человек, работающий с таблицей, может получить приятный свод данных, в которых без усилий будут видны нужные числа.
Где же находить условное форматирование?
Чтоб с фуррором воспользоваться данным инвентарем, принципиально осознать и уяснить, где в Excel условное форматирование размещается.
В старенькой версии программки Excel 2003 нужно зайти в меню «Формат» и избрать строку «Условное форматирование». Для следующих версий Excel 2007, 2010 и 2013 в верхней панели во вкладке «Основная» следует найти раздел «Стили», в котором и находится меню «Условное форматирование». Кликнув на него, юзер увидит весь список вероятных операций.
Виды условного форматирования
Во всплывающем меню данного инструмента показано, какие есть главные правила условного форматирования в Excel.
До этого всего, это правила выделения ячеек, которые разрешают отформатировать ячейки при помощи специально данных критерий, значений и диапазонов. К примеру, посреди избранных данных выделятся числа больше либо меньше какого-то значения, находящиеся снутри установленного спектра, равные данному значению либо схожие по значению либо даже содержащие определенный текст.
В данном перечне есть увлекательное правило, позволяющее создать условное форматирование в Excel даты каких-то событий. Условие в этом случае выставляется от реального денька, к примеру, можно задать ячейки, содержащие даты в последующем месяце либо на прошлой недельке.
Последующий набор правил – правила отбора первых и крайних значений. Используя их, можно выделять значения, входящие в 1-ый либо крайний десяток частей либо процентов, также данные, которые больше либо наименьшего среднего значения.
Также условное форматирование в Excel дозволяет сделать юзеру собственные правила либо удалить их, а при помощи пт меню «Управление правилами» есть возможность просмотреть все правила, сделанные на избранном листе, и совершать с ними различные манипуляции.
Новаторства условного форматирования в наиболее поздних версиях Excel
С версии Excel 2007 в условном форматировании возникли достойные внимания нововведения. Юзер может задать не только лишь обыденное цветовое форматирование перечисленными выше главными правилами, да и отформатировать ячейки разными графическими элементами, что еще более облегчит процесс сопоставления данных. Выделить интересующие значения можно при помощи различных значков, цветовых шкал и даже гистограмм.
Условное форматирование при помощи гистограммы
Этот вид условного форматирования наглядно показывает значение в ячейке по сопоставлению с иными ячейками. Это условное форматирование можно просто поменять созданием истинной гистограммы, ведь на самом деле они делают схожие функции. Но пользоваться форматированием еще удобнее и проще.
В каждой ячейке длина гистограммы будет соответствовать числу, которое в ней находится. Чем выше это значение, тем длиннее будет гистограмма, и напротив.
При помощи этого типа форматирования весьма комфортно определять главные значения в в особенности огромных массивах данных и наглядно представлять их прямо в таблице, а не в отдельном графике.
В меню гистограмм есть несколько предустановленных форматов, которые разрешают стремительно отформатировать таблицу. Также есть возможность сделать новое условие с выбором цвета столбца и случайных значений. Необходимо отметить, что гамма цветов во всех правилах условного форматирования таковая же широкая, как и остальные цветовые наборы в Excel. Это дозволяет выделить разными методами огромное количество данных и создать приятным каждое значение, требующее особенного внимания.
Условное форматирование цветовой шкалой
Благодаря этому типу условного форматирования есть возможность задать шкалу, состоящую из 2-ух либо 3-х цветов, которая будет выступать в роли фона ячейки. Яркость заливки будет зависеть от значения в определенной ячейке относительно остальных в обозначенном спектре.
К примеру, если избрать шкалу «зеленый-желтый-красный», то самые большие значения будут покрашены зеленоватым цветом, а меньшие – красноватым, а средние величины – желтоватым.
Выбрав пункт меню «Остальные правила», юзер может настроить стиль шкалы без помощи других. Тут есть возможность избрать, из каких конкретно цветов будет состоять шкала и подобрать их количество.
Условное форматирование значками
Наглядность большенный таблице с большим количеством значений непревзойденно даст форматирование с внедрением значков. С помощью их можно систематизировать все значения в таблице на несколько групп, любая из которых будет представлять определенный спектр. Зависимо от избранного набора значков таковых групп будет три, четыре либо 5.
К примеру, используя в работе набор из 3-х флагов, можно получить последующую картину: красноватые флажки будут стоять рядом с малеханькими значениями (другими словами наименее 33%), желтоватые рядом со средними значениями (находящимися в спектре меж 33 и 67%), а зеленоватые – с большими значениями (наиболее 67%).
Необходимо отметить, что условное форматирование в Excel 2010 содержит еще больше вариантов значков по сопоставлению с предшествующей версией программки.
Если у юзера есть желание применять значки из различных наборов для отображения нужных значений, то стоит избрать пункт «Остальные правила». В наиболее поздних версиях программки тут можно не только лишь вручную задать нужные условия, но также и избрать понравившийся тип значка для всякого значения. Это дает возможность создать представлены данные еще наиболее приятными и довольно неповторимыми.
Достоинства условного форматирования
Часть правил условного форматирования дозволяет задать для ячеек просто определенные цвета шрифта и заливки, также типы границ. Все это можно сделать и при помощи обычного форматирования методом выбора вручную нужных ячеек и придания им подходящих черт. Но это может занять много времени, тем наиболее что ячейки с близкими значениями придется также отбирать без помощи других, повсевременно просматривая всю таблицу.
Для малеханького массива можно так и поступить, но почаще всего спецы работают конкретно с большенными размерами данных, для обработки которых, безусловн, лучше подойдет конкретно условное форматирование в Excel. Тем наиболее что сделанные правила работают вне зависимости от того, какие значения возникают в ячейках. Человеку не нужно будет всякий раз изменять опции форматирования под определенные данные, и он может сберечь свое время.
Внедрение формул в условном форматировании
Бывают случаи, когда предоставленные стили не подступают для определенной задачки либо просто кое-чем не нравятся юзеру. Для решения таковых заморочек можно без помощи других задать условное форматирование. В Excel 2010 применять формулу для определения нужных значений, которые будут отформатированы, достаточно просто, вообщем, как и в остальных версиях программки.
Для этого нужно избрать в меню условного форматирования пункт «Сделать правило», потом строчку «Употреблять формулу» для определения форматируемых ячеек».
Тут нужно задать характеристики, на базе которых будет выполняться в Excel условное форматирование. Формула заносится в длинноватую строчку, и ее настоящие значения будут определять ячейки, которые подвергнутся обработке. Чтоб избрать формат по собственному усмотрению, нужно надавить на подобающую клавишу. В показавшемся окне у юзера будет возможность избрать тип, размер и цвет шрифта, формат отображения числа, вид границ ячеек и метод заливки.
Данным методом можно задавать в Excel условное форматирование строчки, столбца, отдельной ячейки либо всей таблицы.
Пример использования формулы в условном форматировании
Представим, сотворена таблица, в которой в 2-ух столбцах представлены данные о выручке, приобретенной за реализацию огромного количества различных продуктов за март и апрель. Нужно выделить суммы, которые в марте оказались больше, чем в апреле.
Для этого следует зайти в вышеуказанное меню и в строчку формулы ввести последующее: = B5>C5. Тут B – это столбец марта, С – столбец апреля. Записанное условие можно будет протянуть на весь спектр, в котором собраны данные. Его можно также протянуть далее по столбцам при добавлении новейших месяцев.
Функция условного форматирования делает Excel еще наиболее комфортной и сильной программкой для работы с таблицами. И юзеры, понимающие о плюсах этого инструмента, непременно, работают в программке с большей легкостью и наслаждением.
Как создать условное форматирование в Excel
Условное форматирование – это весьма нужная функция в Excel, которая дозволяет отформатировать числовые данные либо текст в таблице, в согласовании данным условиям либо правилам. Благодаря ему, взглянув на нужные ячейки, Вы сходу можете оценить значения, потому что все данные будут представлены в комфортном приятном виде.
Клавиша «Условное форматирование» находится на вкладке «Основная» в группе «Стили» .
Кликнув по ней, раскроется меню с видами условного форматирования. Давайте разберемся с ними наиболее тщательно.
Выделение ячеек
В этом случае мы можем сопоставить числовые данные выделенного спектра ячеек с определенным данным числом, либо с иным спектром. Ассоциировать можно не только лишь числа, да и текст, и даты.
Пример
Сравним все числа в избранном спектре и если есть повторы, закрасим блоки с ними в определенный цвет. Жмем «Условное форматирование» – «Правила выделения ячеек» – «Повторяющиеся значения» . В перечне избираем «повторяющиеся» и тип заливки. Сейчас все повторы в столбце выделены цветом. Видите ли, в примере пару раз встречаются шестерки и восьмерки.
Сейчас давайте сравним данные в первом спектре со вторым, и если число в первом будет меньше, выделим прямоугольничек цветом. Избираем из перечня «Меньше» . Далее делаем относительную ссылку на 2-ой столбец: кликаем мышкой по первому числу. Бакс перед F означает, что ассоциировать будем конкретно с сиим столбцом, но в различных ячейках. В итоге, все блоки в первом столбце, где числа записаны меньше, чем во 2-м, выделены цветом.
Еще можно применить форматирование к текстовым данным. Для этого изберите из перечня «Текст содержит» . К примеру, выделим все имена, которые начинаются с «В» – другими словами сам блок и текст будут покрашены в избранный цвет.
Отбор первых и крайних значений
Используя данный пункт можно выделить ячейки, которые относятся к первым либо крайним элементам, в согласовании данному числу либо проценту.
Пример
Зададим форматирование для тех блоков, значение в которых выше среднего в выделенной области. Избираем из перечня «Выше среднего» . Надлежащие прямоугольнички и текст в их будут выделены.
Гистограммы
Они демонстрируют информацию в блоке в виде гистограммы. Ячейка принимается за 100%, которому соответствует наибольшее число в избранном спектре. Если значение в блоке будет отрицательное – гистограмма делится на одну вторую, имеет другую направленность и цвет.
Пример
Отобразим число для всякого выделенного блока в виде гистограммы. Избираем хоть какой из предложенных методов заливки.
Сейчас давайте представим, что малое число для построения гистограммы обязано быть 5. Выделяем подходящий спектр, кликаем по кнопке «Условное форматирование» и избираем из перечня «Управление правилами» .
Раскроется последующее окно. В нем можно сделать новое правило для выделенных ячеек, поменять либо удалить необходимое из перечня. Избираем «Поменять правило» .
Понизу окна «Изменение правила форматирования» можно поменять описание для него. Ставим «Малое значение» – «Число» , и в поле «Значение» пишем «5» . Если Вы не желаете, чтоб в ячейках показывались числа, поставьте галочку в пт «Демонстрировать лишь столбец» . Тут же можно поменять цвет и тип заливки.
В итоге малое число для выделенных ячеек «5» , а наибольшее выбирается автоматом. Как видно в примере, в блоках, где число меньше 5: 4, -7, -8, либо равно ему гистограмма просто не отображается.
Цветовые шкалы
Сейчас разберем 4-ый пункт. В этом случае, ячейка заливается цветом, который зависит от числа, которое в ней записано.
Если открыть окно «Изменение правила форматирования» , как описано в прошлом пт, можно избрать «Стиль формата» , «Цвет» заливки, наибольшее и малое значение для избранного спектра.
К примеру, в поле «Малое значение» я поставила «3» . Избранная область будет смотреться последующим образом – блоки, значения в которых ниже 4-ох будут просто не закрашены.
Наборы значков
В ячейку, в согласовании с ее значением, будет вставлен определенный значок.
Открыв окно «Изменение правила форматирования» , можно избрать «Значение» и «Тип» для чисел, которым будет соответствовать любой значок.
Как удалить
Если Для вас необходимо удалить условное форматирование для определенного спектра (и не только лишь), кликните по кнопке «Удалить правила» и изберите подходящий пункт из меню.
Как сделать новое правило
Клавиша «Сделать правило» дозволит сделать новейшие нужные условия для избранного спектра.
Пример
Представим, есть маленькая табличка, которая представлена на рисунке выше. Сделаем для нее разные правила. Если числа в спектре выше «0» – закрасим блоки в желтоватый цвет, выше «10» – в зеленоватый, выше «18» – в красноватый.
Для начала, необходимо избрать тип – «Форматировать лишь ячейки, которые содержат» . Сейчас в поле «Измените описание правила» задаем значение, избираем цвет ячейки и жмем «ОК» . Создаем, таковым образом, три правила для выделенного спектра.
Таблица в примере отформатирована последующим образом.
Как управлять правилами
Если у вас в документе уже есть условное форматирование и для него заданы определенные условия, но необходимо их поменять, то давайте разглядим, как управлять ими. Для этого выделяем тот же спектр и кликаем по кнопке «Управление правилами» .
Раскроется окно «Диспетчер правил условного форматирования» . В нем можно сделать новое для выделенного спектра, поменять либо удалить хоть какое из перечня.
Они все к выделенному спектру, используются в определенном порядке. Давайте разглядим таковой пример: если значение в ячейке «12» , она быть может закрашена в желтоватый и зеленоватый цвет, в согласовании поставленным условиям. Но так обычно «Значение ячейки > 10» имеет больший ценность, в сопоставлении с «Значение ячейки > 0» , то ячейка закрасится в зеленоватый цвет. Поменять порядок правил, можно при помощи стрелочек.
Надеюсь, Вы сообразили, как применять условное форматирование в документе Excel. Оно дозволит отформатировать ячейки необходимым образом. При этом, если значения в ячейках поменяются, то подходящим образом поменяется и форматирование для их.
