Узнайте, как на листах Excel быстро изменять цвет целой строки в зависимости от значения одной ячейки. Посмотрите приёмы и примеры формул для числовых и текстовых значений.
В одной из предыдущих статей мы обсуждали, как изменять цвет ячейки в зависимости от её значения . На этот раз мы расскажем о том, как в Excel 2010 и 2013 выделять цветом строку целиком в зависимости от значения одной ячейки, а также раскроем несколько хитростей и покажем примеры формул для работы с числовыми и текстовыми значениями.
Как изменить цвет строки на основании числового значения одной из ячеек
Предположим, у нас есть вот такая таблица заказов компании:
Мы хотим раскрасить различными цветами строки в зависимости от заказанного количества товара (значение в столбце Qty. ), чтобы выделить самые важные заказы. Справиться с этой задачей нам поможет инструмент Excel – «Условное форматирование ».
Как видите, изменять в Excel цвет целой строки на основании числового значения одной из ячеек – это совсем не сложно. Далее мы рассмотрим ещё несколько примеров формул и парочку хитростей для решения более сложных задач.
Как создать несколько правил условного форматирования с заданным приоритетом
В таблице из предыдущего примера, вероятно, было бы удобнее использовать разные цвета заливки, чтобы выделить строки, содержащие в столбце Qty. различные значения. К примеру, создать ещё одно правило условного форматирования для строк, содержащих значение 10 или больше, и выделить их розовым цветом. Для этого нам понадобится формула:
Для того, чтобы оба созданных нами правила работали одновременно, нужно расставить их в нужном приоритете.
Как изменить цвет строки на основании текстового значения одной из ячеек
Чтобы упростить контроль выполнения заказа, мы можем выделить в нашей таблице различными цветами строки заказов с разным статусом доставки, информация о котором содержится в столбце Delivery :
- Если срок доставки заказа находится в будущем (значение Due in X Days ), то заливка таких ячеек должна быть оранжевой;
- Если заказ доставлен (значение Delivered ), то заливка таких ячеек должна быть зелёной;
- Если срок доставки заказа находится в прошлом (значение Past Due ), то заливка таких ячеек должна быть красной.
И, конечно же, цвет заливки ячеек должен изменяться, если изменяется статус заказа.
С формулой для значений Delivered и Past Due всё понятно, она будет аналогичной формуле из нашего первого примера:
=$E2="Delivered"
=$E2="Past Due"
Сложнее звучит задача для заказов, которые должны быть доставлены через Х дней (значение Due in X Days ). Мы видим, что срок доставки для различных заказов составляет 1, 3, 5 или более дней, а это значит, что приведённая выше формула здесь не применима, так как она нацелена на точное значение.
В данном случае удобно использовать функцию ПОИСК (SEARCH) и для нахождения частичного совпадения записать вот такую формулу:
ПОИСК("Due in";$E2)>0
=SEARCH("Due in",$E2)>0
В данной формуле E2 – это адрес ячейки, на основании значения которой мы применим правило условного форматирования; знак доллара $ нужен для того, чтобы применить формулу к целой строке; условие “>0 ” означает, что правило форматирования будет применено, если заданный текст (в нашем случае это “Due in”) будет найден.
Подсказка: Если в формуле используется условие “>0 “, то строка будет выделена цветом в каждом случае, когда в ключевой ячейке будет найден заданный текст, вне зависимости от того, где именно в ячейке он находится. В примере таблицы на рисунке ниже столбец Delivery (столбец F) может содержать текст “Urgent, Due in 6 Hours” (что в переводе означает – Срочно, доставить в течение 6 часов), и эта строка также будет окрашена.
Для того, чтобы выделить цветом те строки, в которых содержимое ключевой ячейки начинается с заданного текста или символов, формулу нужно записать в таком виде:
ПОИСК("Due in";$E2)=1
=SEARCH("Due in",$E2)=1
Нужно быть очень внимательным при использовании такой формулы и проверить, нет ли в ячейках ключевого столбца данных, начинающихся с пробела. Иначе можно долго ломать голову, пытаясь понять, почему же формула не работает.
Итак, выполнив те же шаги, что и в , мы создали три правила форматирования, и наша таблица стала выглядеть вот так:
Как изменить цвет ячейки на основании значения другой ячейки
На самом деле, это частный случай . Вместо целой таблицы выделяем столбец или диапазон, в котором нужно изменить цвет ячеек, и используем формулы, описанные выше.
Например, мы можем настроить три наших правила таким образом, чтобы выделять цветом только ячейки, содержащие номер заказа (столбец Order number ) на основании значения другой ячейки этой строки (используем значения из столбца Delivery ).
Как задать несколько условий для изменения цвета строки
Если нужно выделить строки одним и тем же цветом при появлении одного из нескольких различных значений, то вместо создания нескольких правил форматирования можно использовать функции И (AND), ИЛИ (OR) и объединить таким образом нескольких условий в одном правиле.
Например, мы можем отметить заказы, ожидаемые в течение 1 и 3 дней, розовым цветом, а те, которые будут выполнены в течение 5 и 7 дней, жёлтым цветом. Формулы будут выглядеть так:
ИЛИ($F2="Due in 1 Days";$F2="Due in 3 Days")
=OR($F2="Due in 1 Days",$F2="Due in 3 Days")
ИЛИ($F2="Due in 5 Days";$F2="Due in 7 Days")
=OR($F2="Due in 5 Days",$F2="Due in 7 Days")
Для того, чтобы выделить заказы с количеством товара не менее 5, но не более 10 (значение в столбце Qty. ), запишем формулу с функцией И (AND):
И($D2>=5;$D2<=10)
=AND($D2>=5,$D2<=10)
Конечно же, в своих формулах Вы можете использовать не обязательно два, а столько условий, сколько требуется. Например:
ИЛИ($F2="Due in 1 Days";$F2="Due in 3 Days";$F2="Due in 5 Days")
=OR($F2="Due in 1 Days",$F2="Due in 3 Days",$F2="Due in 5 Days")
Подсказка: Теперь, когда Вы научились раскрашивать ячейки в разные цвета, в зависимости от содержащихся в них значений, возможно, Вы захотите узнать, сколько ячеек выделено определённым цветом, и посчитать сумму значений в этих ячейках. Хочу порадовать Вас, это действие тоже можно сделать автоматически, и решение этой задачи мы покажем в статье, посвящённой вопросу Как в Excel посчитать количество, сумму и настроить фильтр для ячеек определённого цвета .
Мы показали лишь несколько из возможных способов сделать таблицу похожей на полосатую зебру, окраска которой зависит от значений в ячейках и умеет меняться вместе с изменением этих значений. Если Вы ищите для своих данных что-то другое, дайте нам знать, и вместе мы обязательно что-нибудь придумаем.
Одним из основных вопросов при работе с таблицами считается, как выделить цветом ячейку в Еxcel по условию?
Создавая таблицы в Еxcel можно добавлять новые ячейки для удобства, но также есть возможность расставить и другие акценты, к примеру, выделить строку или целый столбец цветом.
Такой способ акцентирования позволяет выделить самые важные блоки, на которые обратить следует особое внимание в ходе работы.
Как делается окрашивание ячейки в Еxcel
Чтобы отметить определенную строку или столбец в таблице или изменить уже окрашенную, следует нажать на вкладке раздел – «Главная». Далее после нажатия ведерка с краской нужно выбрать соответственный цвет.
Когда с представленной палитры нет подходящего оттенка, то можно нажать на вкладку «Другие цвета» и выбрать какой необходим на свое усмотрение.
Стоит отметить, что таким образом можно полностью закрасить строку. Для этого нужно нажать на номер той строки, которая будет окрашиваться чтобы ее выделить. Кроме этого, в таблице можно:
- выделять повторяющиеся значения, в том числе и по нескольким столбцам;
- выделять первые и последние значения;
- выстраивать красочные термальные диаграммы и гистограммы;
- окрасить ячейки, которые содержать определенный текст.
В ситуациях, когда осуществляется многократное копирование ячеек, окрашенных красками, довольно часто зависает программа Еxcel, что приводит к торможению ее работы.
Что нужно делать чтобы производились изменение цвета в таблице по заданным условиям
Для изменения цветовой гаммы в отмеченных строках или столбцах таблицы Еxcel необходимо использовать вкладку «Условное форматирование». Из высветившегося списка потребуется выбрать пункт «Создание правила», что и будет выполнять поставленное условие при дальнейшей работе.
- Следует определить тип, то есть, выбирается строка, где написано «Форматировать только ячейки, которые содержат». В этом пункте нужно прописать какое условие, а именно слово или текст, который будет задавать поставленные параметры.
- Следующим этапом будет выбор кнопки «Формат». В нем надо выбрать соответствующий цвет и нажать «Ок». Тем самым сохранив и созданное правило, и обозначив избранную краску.
Что касается числовых данных, то также можно использовать изменение заливки. Но изначально нужно создать правило. К примеру, задать правило, что цифры в столбце или строке более 100 будут менять цвет, а меньшие значения останутся того же оттенка, что было обозначено изначально. Для этого нужно проделать такие шаги:
- на панели задач выбрать пункт «Управление правилами»;
- далее «Текущий фрагмент»;
- и после «создать правило»;
- затем – «Форматировать только ячейки, для которых выполняется следующее условие»;
- следом нажать кнопку «Меньше или равно»;
- выбрать оттенок заливки;
- когда все пункты будут отмечены следует кликнуть «Применить» и «Ок».
Обязательно нужно чтобы заданные условия работали, то есть автоматически менялись, необходимо создавать несколько правил. К примеру, в первом правиле отмечается, что «Форматировать только ячейки, которые содержат», а в обозначенном блоке описания установить позицию «Значение» в другом поле установить такой пункт, как «Меньше».
Что касается задания значений во втором правиле, то в пункте «Форматировать только ячейки, которые содержат» нужно оставить блок «Значение», а в другом поле установить позицию «Между». В каждом правиле при выборе форматирования выбирается требуемая краска заливки.
Есть возможность окрасить определенные строки или столбцы одним цветом, но разных оттенков. Для этого в пункте диспетчера правил выбрать значение «Форматировать все ячейки на основании значений». После чего выбрать в поле «Цвет» нужную краску и нажать кнопку «Ок». Изменения оттенков будут меняться в зависимости от внесения данных.
Стоит отметить, что «$» перед адресом ячейки ставиться чтобы при осуществлении копирования формулы для других ячеек оставляло букву столбца неизменным. В этой ситуации форматирование всей строки будет переменяться в зависимости от значения одной заданной ячейки.
Изменение заливки в ручную
Самым простым способом изменить палитру в таблице Еxcel, это использовать такой инструмент, как «Цвет заливки». Здесь понадобиться выбрать необходимый цвет и окрасить им отмеченные столбцы или строки.
Также можно использовать и другой вариант. Изначально следует выбрать нужный диапазон строк, а после нажать на пункт меню «Формат ячеек». Далее применить закладку «Заливка».
Имеется еще один способ, как выделить требуемые строки и столбцы в Еxcel цветом. Нужно выбрать на основной панели задач вкладку «Главная». После чего кликнуть на пункт «Стили ячеек». Главным условием при выполнении окрашивания строк является создание правила в программе Еxcel.
Можно использовать такой способ, как на панели задач применить пункт «Найти и выделить». В этом случая пользователь может сам выбирать оттенок заливки к каждой строке или столбце.
Представленные варианты в основном используются для придания внесенным данным в таблицу читабельного вида. Поэтому они могут применяться к таблицам, которые не имеют динамических содержаний, то есть не изменяются в зависимости от внесенных данных.
Как проверить правильность заданных условий
Чтобы проверить заданные параметры условий перед тем, как нажать клавишу «Применить», следует пройтись по всем пунктам, а именно:
- главная панель задач (Home);
- условное форматирование (Conditional Formatting);
- управление правилами (New rule).
Плюсом в этом случае считается то, что каждый пункт можно отформатировать, если имело место внесение не правильных данных.
Одним из важных условий также является внесение точных данных, а именно при указании условий в правиле. Все дело в том, что любая опечатка, написание данных в ином падеже может привести к тому, что заданные параметры в таблице не будут работать.
С представленной информации становится понятным, какие есть способы чтобы изменить цветовую гамму в таблице Еxcel. Все они достаточно просты даже для неопытных пользователей, что весьма удобно при работе в обозначенной программе.
Всем привет. Сегодняшний пост хочу посвятить способам выделения ячеек в Майкрософт Эксель. Если вы уже знаете, как выделять ячейки – бегло просмотрите текст, возможно некоторые возможности программы вам не известны, и вы не пользуетесь их преимуществами.
И так, выделение групп ячеек нужно для того, чтобы выполнить с ними какое-то общее действие: или , и т.д. Как же можно выделить ячейки в Эксель? Разбираемся!
Как выделить все ячейки листа Excel
Сразу отвечу на самый задаваемый вопрос по теме статьи. Как же выделить сразу все ячейки листа? Предлагаю Вам два способа, какой больше нравится, тот и используйте:
Выделение прямоугольного диапазона ячеек
Предположим, вам нужно выделить прямоугольную группу ячеек, чтобы отформатировать их под свой тип данных. Я могу предложить 5 способов выделения, а вы выбирайте, какой будет уместен для вас в данный момент:
- Выделение мышью . Самый распространенный способ – зажмите левую кнопку мыши внутри одной из клеток (не на рамке) и растягивайте выделение в нужном направлении по строкам и столбцам
- Зажатием клавиши Shift . Поставьте курсор в одну из угловых ячеек будущего выделения. Зажмите Shift и выделяйте область клавишами стрелок. Когда закончите – отпустите Shift .
- Нажмите клавишу
F8
. Команда аналогична предыдущей, но клавишу не нужно зажимать. Установите курсор в одну из угловых ячеек массива, нажмите F8
, чтобы включить режим выделения. Клавишами стрелок расширяйте выделение, либо кликните мышью в противоположном углу будущего выделения. Когда массив выделен – нажмите F8
еще раз, чтобы выйти из этого режима.
Еще один способ – установите курсор в верхней левой клетке диапазона, зажмите Shift и кликните мышью на правую нижнюю (см. рис.)
- Введите в поле «Имя»
адрес ячейки или диапазон ячеек (через двоеточие) и нажмите Enter
- Выполните команду (на клавиатуре – F5 , или Ctrl+G ). В открывшемся окне в поле «Ссылка» введите адрес ячейки или диапазона ячеек через двоеточие. Нажмите Ок
Выделение через команду «Перейти»
Выделить строки и столбцы полностью
Если нужно выделить весь столбец или строку – вот несколько вариантов выполнения:
- Кликните мышью на номере строки или имени столбца. Если нужно выбрать несколько смежных строк – зажмите левую кнопку мыши на номере строки и тяните выделение на те строки, которые нужно выделить. Аналогично поступаем со столбцами
- Поставьте курсор в любую ячейку строки, которую нужно выделить и нажмите комбинацию Shift+Пробел . Для выделения столбца пользуйтесь комбинацией Ctrl+Пробел
- Чтобы выделить несмежные строки и столбцы – зажмите Ctrl и кликайте по именам столбцов и номерам строк. После завершения – отпустите Ctrl .
Выделение несмежных диапазонов
Если нужно выделить несколько не стоящих рядом ячеек, сделайте это одним из предложенных способов:
Выделение на нескольких листах одновременно
Если на рабочих листах одного документа находятся одинаковые таблицы с разными данными, мы можем выполнять операции на всех вкладках одновременно. Это экономит время и снижает вероятность ошибки. Например, нам нужно шапки каждой из таблиц на нескольких листах. Не нужно делать это на каждой вкладке отдельно – выделите все листы и сделайте всё за один раз.
Чтобы выделить одну и ту же область на нескольких листах, сначала выделите нужные листы. Активируйте первый лист из списка, зажмите Ctrl и кликайте по ярлыкам всех листов для выделения.
Когда выделены все нужные листы – можно выполнять операции. Обратите внимание, в строке имени возле имени файла появилась надпись «[Группа] ». Это значит, что Эксель готов обрабатывать группу листов.
Изменение названия при выделении группы листов
Далее, на активном листе выделяйте нужные диапазоны, вносите изменения, заполняйте общие данные и формулы. Всё, что вы сделаете, будет применено ко всем выделенным листам. После завершения групповой обработки не забудьте снять выделение листов. Для этого кликните на любой ярлык группированного листа правой кнопкой мыши, в контекстном меню выберите Разгруппировать .
Условное выделение ячеек
Microsoft Excel может выделить группу ячеек в зависимости от их содержимого. Не все пользователи знают об этой возможности, хотя её применение может быть очень полезным.
Выполните команду Главная – Редактирование – Найти и выделить – Перейти . В появившемся окне нажмите Выделить… . Появится диалоговое окно «Выделение группы ячеек», в котором доступны такие опции выделения:
Выделение ячеек по их содержимому
Выделение ячеек через окно поиска
Чтобы открыть окно поиска значения – выполните Главная – Редактирование – Найти и выделить – Найти (или нажмите комбинацию клавиш Ctrl+F ). Появится Найти все . В нижней части окна появится список ячеек, содержащих выделенные данные. Выберите одну или несколько ячеек (с зажатым Ctrl ) в списке, чтобы Эксель их выделил. Чтобы выделить все найденные ячейки – выделите одну из них и нажмите комбинацию Ctrl+A .
Для поиска можно использовать специальные символы:
- «?» — один любой символ
- «*» — сколько угодно любых символов
Например, чтобы найти все ячейки, которые начинаются на букву «А» — введите в поиске «А*».
Вот и всё о выделении ячеек в MS Excel, а следующий пост хочу посвятить . Как всегда, эта статья будет содержать много полезностей, которые обязательно пригодятся в работе. Так что, прочтите, не пожалеете!
Кстати, жду Ваших вопросов касательно этой статьи в комментариях!
Привет, уважаемые читатели. Когда-нибудь вам доводилось работать с огромными данными в таблице? Знаете, с ними гораздо удобнее будет работать, если знать, как выделить несколько ячеек Excel различным цветом при определенном условии. Хотели бы вы узнать, как это делается? В этом уроке мы сделаем так, чтобы менялся цвет ячейки в зависимости от значения Excel, а также окрасим все ячейки с помощью поиска.
Цвет заливки меняется вместе со значением
Для примера мы потренируемся на том, чтобы ячейка меняла цвет в данной таблице при определенном условии. Да ни одна, а все со значением в диапазоне от 60 до 90. Для этого мы воспользуемся функцией «Условное форматирование».
Для начала выделите тот диапазон данных, который мы будем форматировать.
Далее находим на вкладке «Главная» кнопку «Условное форматирование» и в списке выбираем «Создать правило».
У нас открылось окно «Создание правил форматирования». В этом окне выбираем тип правила: «Форматировать только ячейки, которые содержат».
Далее, переходим к разделу «Измените описание правила», где нужно указать те условия, по которым будет выполнена заливка. В этом разделе можно выставить самые различные условия, при которых она будет меняться.
В нашем случае необходимо поставить следующие: «значения ячейки» и «между». Так же мы обозначаем диапазон, что при условии значения от 60 до 90 будет применена заливка. Посмотрите на скриншоте, как это сделал я.
Конечно же при работе с вашей таблицей может потребоваться заполнить совсем другими условиями, которые вы и будете указывать, ну, а сейчас мы всего лишь тренируемся.
Если вы заполнили, то не спешите кликать по кнопке «ОК». Прежде необходимо нажать на кнопку «Формат», как на скриншоте, и перейти к настройке заливки.
Хорошо, как видите, у вас открылось окно «Формат ячейки». Здесь вам нужно перейти на вкладку «Заливка», где вы выбираете нужную, и нажать на «ОК» в этом окне и в предыдущем. Я выбрал зеленую заливку.
Посмотрите на свой результат. Думаю, у вас все получилось. У меня точно получилось. Взгляните на скриншот:
Окрасим ячейку в определенный цвет, если она равна чему-то
Давайте вернемся к нашей таблице в изначальном виде. И теперь мы поменяем цвет там, где содержится цифра 40 на красный цвет, а с цифрой 50 на желтый. Конечно, для этого дела можно воспользоваться первым способом, но мы же хотим знать больше возможностей Excel.
В этот раз мы воспользуемся функцией «Найти и заменить».
Выделите тот участок таблицы, в который будем вносить изменения. Если это весь лист, то выделять нет смысла.
Теперь время открыть окно поиска. На вкладке «Главная» в разделе «Редактирование» нажмите на кнопку «Найти и выделить».
Можно же и горячими клавишами пользоваться: CTRL + F
В поле «Найти» мы указываем то, что ищем. В данном случае пишем «40», а затем жмем кнопку «Найти все».
Теперь, когда ниже были показаны результаты поиска, выберите одно из них и нажмите на сочетание CTRL + A, чтобы выбрать их все сразу. А затем нажмите на «Закрыть», чтобы убрать окно «Найти и заменить».
Когда у нас выбраны все, содержащие цифру 40, на вкладке «Главная» в разделе «Шрифт» выберите окраску ячейки. У нас это красный. И, как вы видите у себя на экране, так и у меня на скриншоте, они окрасились в красный.
Теперь те же самые действия нужно выполнить, чтобы окрасить те, где указано число 50. Думаю, теперь вам понятно, как сделать это.
У вас получилось? А посмотрите, что вышло у меня.
На этом все. Спасибо, друзья. Подписывайтесь, комментируйте, вступайте в группу, делитесь в соц сетях и будьте всегда в курсе новых статей. А также, не забывайте изучать и другие статьи на этом сайте.
Каждый лист рабочей книги Excel разбит на строки и столбцы, количество которых зависит от версии приложения. Строки и столбцы можно удалять, добавлять, объединять и перемещать. Ранее уже рассматривалось удаление строк по условию, удаление столбцов по условию и добавление пустых строк в рабочие книги Excel. Ниже будет рассмотрено еще одно действие, совершаемое со строками - выделение.
Как выделить одну строку?
Существует несколько способов для того чтобы выделить строку:
1. Можно в окне открытого листа кликнуть левой кнопкой мыши курсором-стрелкой один раз по номеру нужной строки;
2. также, в окне открытого листа, можно установить маркер выделения в любую ячейку нужной строки и использовать сочетание клавиш Shift+Space (пробел);
3. и наконец, можно установить маркер выделения в первую ячейку нужной строки и использовать сочетание клавиш Ctrl+Shift+Стрелка вправо. При этом, если в строке есть данные, то первое нажатие этого сочетания клавиш выделит часть строки с даными, а второе - уже всю строку.
Как выделить несколько строк подряд?
1. Выделить первую строку нужного диапазона и не отпуская левую кнопку мыши протащить курсор-стрелку по номерам нужных строк;
2. выделить первую строку нужного диапазона и при нажатой клавише Shift кликнуть по номеру последней строки нужного диапазона;
3. кликнуть курсором ввода текста в адресном окне строки формул, вписать диапазон строк (например 10:20) и нажать клавишу Enter.
Как выделить несколько строк не подряд?
Для выборочного выделения нескольких строк необходимо кликать по номерам нужных строк курсором-стрелкой при нажатой клавише Ctrl.
Как выделить строки по условию?
Если есть необходимость в выборочном выделении строк (для форматирования, копирования или других целей) например, содержащих заданный текст или наоборот, не содержащих определенный текст, можно использовать надстройку, позволяющую ускорить выделение строк по различным условиям. Ниже приведено диалоговое окно, позволяющее оценить возможности этой надстройки.
Надстройка позволяет:
1. Одним кликом мыши вызывать диалоговое окно макроса прямо из панели инструментов Excel;
2. Находить и выделять строки в зависимости от поставленного условия и заданных значений,
возможен ввод сразу нескольких текстовых значений разделенных знаком ";" (точка с запятой);
3. Выбирать одно из восьми условий для строк с искомым значением:
- совпадает с искомым значением;
- не совпадает с искомым значением;
- содержит мскомое значение;
- не содержит искомое значение;
- начинается с искомого значения;
- не начинается с искомого значения;
- заканчивается искомым значением;
- не заканчивается искомым значением.
4. При поиске текста учитывать либо не учитывать регистр (различать заглавные и строчные буквы);
5. При нахождении строки предусмотрена возможность выделять:
а) всю строку по ширине листа;
б) часть строки, ограниченную используемым диапазоном (от первой заполненной до последней заполненной ячейки) ;
в) часть строки, ограниченную выделенным (выбранным) диапазоном поиска.
6. Выбирать диапазон строк для поиска заданных значений и при необходимости вносить ограничения для выбранного диапазона, устанавливая начальную и конечную строку.
