Как в экселе выбрать подобные ячейки

Обновлено: 04.07.2024

Выбирая инструменты на закладке: «ГЛАВНАЯ» в разделе «Стили» из выпадающего меню «Условное форматирование» нам доступна целая группа «Правила отбора первых и последних значений». Однако часто необходимо сравнить и выделить цветом ячейки в Excel, но ни один из вариантов готовых решений не соответствует нашим условиям. Например, в конструкции условия мы хотим использовать больше критериев или выполнять более сложные вычисления. Всегда можно выбрать последнюю опцию «Другие правила» она же является опцией «Создать правило». Условное форматирование позволяет использовать формулу для создания сложных критериев сравнения и отбора значений. Создавая свои пользовательские правила для условного форматирования с использованием различных формул мы себя ничем не ограничиваем.

Как сравнить столбцы в Excel и выделить цветом их ячейки?

Для наглядности разберем конкретный пример создания условного форматирования с формулами. Для примера возьмем простую таблицу отчета прибыльности магазинов за прошлый и текущий год. Наше правило должно заставить Excel выделить цветом при условии, что суммы магазинов текущего года имеют отрицательную прибыль (убыток) и они больше, чем в прошлом году:

Чтобы создать новое пользовательское правило делаем следующее:

Выделите диапазон ячеек D2:D12 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».
В появившемся окне «Создание правила форматирования» выберите опцию «Использовать формулу для определения форматированных ячеек».
В поле ввода введите формулу:
Нажмите на кнопку «Формат» и в появившемся окне «Формат ячеек» на вкладке «Заливка» выберите красный цвет для данного правила, а на вкладке «Шрифт» – белый цвет. После на всех открытых окнах жмем ОК.

Обратите внимание! В данной формуле мы используем только относительные ссылки на ячейки – это важно. Ведь нам нужно чтобы формула анализировала все ячейки выделенного диапазона.

Как выделить цветом ячейку в Excel по условию?

Теперь оранжевым цветом выделим те суммы магазинов, которые в текущем году меньше чем в прошлом и с отрицательной прибылью. Создадим второе правило для этого же диапазона D2:D12:

Не снимая выделения с диапазона D2:D12 снова выберите инструмент «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».
Так же в появившемся окне «Создание правила форматирования» выберите опцию «Использовать формулу для определения форматированных ячеек».
В поле ввода введите формулу:
Нажмите на кнопку «Формат» и в появившемся окне «Формат ячеек» на вкладке «Заливка» выберите оранжевый цвет. На всех открытых окнах жмем ОК.

Мы видим, что получили не совсем ожидаемый результат, так как созданное новое правило всегда имеет высший приоритет по сравнению со старыми правилами условного форматирования в Excel. Необходимо снизить приоритет для нового правила. Чтобы проанализировать данную особенность наглядно и настроить соответствующим образом необходимо выбрать инструмент: ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Управление правилами».

Выберите новое оранжевое правило в появившемся окне «Диспетчер правил условного форматирования» и нажмите на кнопку «Вниз» (CTRL+стрелка вниз), как показано на рисунке:

Как видите последовательность правил очень важна если их много присвоено для одного и того же диапазона ячеек:

На первый взгляд может показаться что несколько правил могут форматировать одну и туже ячейку одновременно. В принципе это так, но при определенном условии, что все правила будут использовать разные типы форматирования. Например, правило 1 – изменяет шрифт, 2 – меняет заливку, 3 – добавляет границу, 4 – узор и т.д. Но если после выполнения любого правила, когда его условие выполнено, было проверено следующее правило для данной ячейки, тогда следует в окне диспетчера отметить галочкой в колонке «Остановить если истина»:

И наконец добавим третье правило для выделения цветом ячеек сумм магазинов, где положительная прибыль и больше чем в прошлом году. Введите новую формулу:

0;D2>C2)' >

Этим ячейкам будет присвоен зеленый цвет и жмем везде ОК.

Примечание. В формуле можно использовать любые ссылки для текущего листа. В версии Excel 2010 можно ссылаться и на другие листы. А в Excel 2007 к другим листам можно обращаться только через имена диапазонов. Мы рекомендуем во всех версиях Excel ссылаться на другие листы через имена, так как это позволяет избежать множество ошибок при создании пользовательских правил для условного форматирования.

Предположим, у вас есть диапазон данных, как на следующем снимке экрана, и теперь вы хотите найти или выбрать ячейки на основе определенной информации о критериях в Excel, например, я найду или выберу ячейки, содержащие числа от 80 до 100. Как вы могли быстро решить эту задачу?

Вкладка Office позволяет редактировать и просматривать в Office с вкладками и значительно упрощает работу . Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%

Повторное использование чего угодно: Добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
Более 20 текстовых функций: Извлечь число из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
Инструменты слияния : Несколько книг и листов в одну; Объединить несколько ячеек / строк / столбцов без потери данных; Объедините повторяющиеся строки и сумму.
Разделить инструменты : Разделение данных на несколько листов в зависимости от ценности; Из одной книги в несколько файлов Excel, PDF или CSV; От одного столбца к нескольким столбцам.
Вставить пропуск Скрытые / отфильтрованные строки; Подсчет и сумма по цвету фона ; Отправляйте персонализированные электронные письма нескольким получателям массово.
Суперфильтр: Создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделям, дням, периодичности и др .; Фильтр жирным шрифтом, формулы, комментарий .
Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.

Фильтровать ячейки по определенным критериям с помощью функции фильтрации

Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!

Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!

С помощью функции фильтра Excel вы можете отображать нужные строки ячеек и скрывать ненужные строки. Вы можете завершить его, выполнив следующие действия:

1. Выделите столбец, в котором вы хотите выделить определенные ячейки.

2. Нажмите Данные > Фильтр, см. снимок экрана:

3. В правом нижнем углу заголовка отобразится маленький треугольник, щелкните маленький треугольник, и появится меню. выберите Числовой фильтр > Пользовательский фильтр… См. Снимок экрана:

4, И Пользовательский автофильтр диалоговое окно появится. Затем укажите критерии, которые вам нужны, как на следующем снимке экрана:

5. Нажмите OK. И все ячейки, соответствующие критериям, были отображены, а остальные скрыты.

При использовании этого метода другие ячейки, не соответствующие критериям, будут скрыты. Но если вы не хотите скрывать другие ячейки, а форматируйте ячейки в соответствии с критериями. Как ты мог?

Поиск ячеек на основе определенных критериев с помощью условного форматирования

Утилита условного форматирования может помочь вам найти и отформатировать ячейки, которые соответствуют вашим критериям в Excel, сделайте следующее:

1. Выберите диапазон данных, который вы хотите использовать.

2. Нажмите Главная > Условное форматирование > Новое правило, см. снимок экрана:

3. В Новое правило форматирования диалоговое окно, выберите Форматировать только ячейки, содержащие вариант под Выберите тип правила, а затем укажите необходимые критерии в разделе Форматирование описания правила, см. снимок экрана:

4. Затем нажмите Формат кнопку в Формат ячеек диалоговое окно, нажмите Заполнять вкладку и выберите один цвет, который вам нравится, для закрашивания ячеек. Смотрите скриншот:

5. Нажмите OK > OK , чтобы закрыть диалоги, и ячейки от 80 до 100 были выделены сразу.

Выберите ячейки на основе определенных критериев с помощью Kutools for Excel

Kutools для Exce l: с более чем 300 удобными надстройками Excel, которые можно попробовать бесплатно без ограничений в течение 30 дней. Получить сейчас

После установки Kutools for Excel выполните следующие действия:

1. Выберите нужный диапазон данных и нажмите Kutools > Выберите Инструменты > Выбрать определенные ячейки…, См. Снимок экрана:

2. В Выбрать определенные ячейки диалоговое окно, отметьте Ячейка от Тип выбораи задайте критерии в Конкретный тип как вам нужно, см. снимок экрана:

3. После установки критериев нажмите OK or Применить кнопка, подсказка напомнит вам количество выбранных ячеек. И все ячейки, соответствующие критериям, были выбраны из диапазона.

Предположим, вам нужно быстро перейти к ячейкам, содержащим определенный текст, или выбрать их на огромном листе, как вы можете быстро выбрать ячейки с определенным текстом в Excel? Вы можете быстро выбрать ячейки, содержащие определенный текст, в Excel следующим образом.

Использование Kutools for Excel для выбора ячеек, содержащих определенный текст

Использование функции поиска для выбора ячеек, содержащих определенный текст

Например, у меня есть рабочий лист, содержащий данные студентов, теперь я хочу выбрать все ячейки, содержащие конкретное имя студента. Использование функции поиска в Excel для выбора ячейки, содержащей определенный текст, следующим образом:

1. Нажмите Главная > Найти и выбрать > НайдитеИ Найти и заменить диалоговое окно появится. Введите нужный текст в поле Найти то, что раскрывающийся список. Смотрите скриншот:

2. Затем нажмите Найти все все необходимые тексты перечислены в следующем поле.

3. Затем нажмите Ctrl + , чтобы выбрать все значения в поле. Нажмите Закрыть , весь нужный текст выделен в диапазоне. Видно скриншоты:

Работы С Нами Найдите функция, вы можете просто выбрать только ячейки, если вы хотите выбрать целые строки с определенным текстом, метод не будет работать.

Использование Kutools for Excel для выбора ячеек, содержащих определенный текст

Работы С Нами Выбрать определенные ячейки of Kutools for Excel, вы можете выбрать как ячейки, так и целые строки с определенными значениями на листе.

После установки Kutools for Excel, сделайте следующее: （Бесплатная загрузка Kutools for Excel прямо сейчас!)

1. Выберите диапазон, в котором вы хотите найти конкретный текст.

2. Нажмите Kutools > Выберите Инструменты > Выбрать определенные ячейки, см. снимок экрана:

3. в Выбрать определенные ячейки диалоговом окне укажите тип выбора что вам нужно, и выбирайте Комплект из Конкретный тип раскрывающийся список, затем введите значение, которое вы хотите выбрать. Смотрите скриншот:
Чаевые: Если вы хотите сопоставить все содержимое ячейки, выберите равным из раскрывающегося списка Конкретный тип.

4. Затем нажмите OK or Применить. Появится диалоговое окно, напоминающее количество выбранных ячеек, закройте его, и весь указанный текст будет выбран.

Заметки :

1. С Конкретный тип раскрывающийся список, вы можете выбрать любые другие критерии, которые соответствуют вашим потребностям.

2. С помощью этого инструмента вы можете выбирать ячейки, целые строки или целые столбцы, которые вам нужны. Если вы проверите Весь ряд, результат будет таким, как показано ниже:

3. С помощью этого инструмента вы также можете выбрать два конкретных текста одновременно. Проверьте Or указать, чтобы выбрать ячейки, содержащие один из двух конкретных текстов, отметьте Разумеется, означает выбрать ячейки, содержащие два конкретных текста.

Если Вы работаете с большой таблицей и вам необходимо выполнить поиск уникальных значений в Excel, соответствующие определенному запросу, то нужно использовать фильтр. Но иногда нам нужно выделить все строки, которые содержат определенные значения по отношению к другим строкам. В этом случаи следует использовать условное форматирование, которое ссылается на значения ячеек с запросом. Чтобы получить максимально эффективный результат, будем использовать выпадающий список, в качестве запроса. Это очень удобно если нужно часто менять однотипные запросы для экспонирования разных строк таблицы. Ниже детально рассмотрим: как сделать выборку повторяющихся ячеек из выпадающего списка.

Выбор уникальных и повторяющихся значений в Excel

Для примера возьмем историю взаиморасчетов с контрагентами, как показано на рисунке:

В данной таблице нам нужно выделить цветом все транзакции по конкретному клиенту. Для переключения между клиентами будем использовать выпадающий список. Поэтому в первую очередь следует подготовить содержание для выпадающего списка. Нам нужны все Фамилии клиентов из столбца A, без повторений.

Перед тем как выбрать уникальные значения в Excel, подготовим данные для выпадающего списка:

Выделите первый столбец таблицы A1:A19.
Выберите инструмент: «ДАННЫЕ»-«Сортировка и фильтр»-«Дополнительно».
В появившемся окне «Расширенный фильтр» включите «скопировать результат в другое место», а в поле «Поместить результат в диапазон:» укажите $F$1.
Отметьте галочкой пункт «Только уникальные записи» и нажмите ОК.

В результате мы получили список данных с уникальными значениями (фамилии без повторений).

Теперь нам необходимо немного модифицировать нашу исходную таблицу. Выделите первые 2 строки и выберите инструмент: «ГЛАВНАЯ»-«Ячейки»-«Вставить» или нажмите комбинацию горячих клавиш CTRL+SHIFT+=.

У нас добавилось 2 пустые строки. Теперь в ячейку A1 введите значение «Клиент:».

Пришло время для создания выпадающего списка, из которого мы будем выбирать фамилии клиентов в качестве запроса.

Перед тем как выбрать уникальные значения из списка сделайте следующее:

Перейдите в ячейку B1 и выберите инструмент «ДАННЫЕ»-«Работа с данными»-«Проверка данных».
На вкладке «Параметры» в разделе «Условие проверки» из выпадающего списка «Тип данных:» выберите значение «Список».
В поле ввода «Источник:» введите =$F$4:$F$8 и нажмите ОК.

В результате в ячейке B1 мы создали выпадающих список фамилий клиентов.

Примечание. Если данные для выпадающего списка находятся на другом листе, то лучше для такого диапазона присвоить имя и указать его в поле «Источник:». В данном случае это не обязательно, так как у нас все данные находятся на одном рабочем листе.

Выборка ячеек из таблицы по условию в Excel:

Выделите табличную часть исходной таблицы взаиморасчетов A4:D21 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило»-«Использовать формулу для определения форматируемых ячеек».
Чтобы выбрать уникальные значения из столбца, в поле ввода введите формулу: =$A4=$B$1 и нажмите на кнопку «Формат», чтобы выделить одинаковые ячейки цветом. Например, зеленым. И нажмите ОК на всех открытых окнах.

Как работает выборка уникальных значений Excel? При выборе любого значения (фамилии) из выпадающего списка B1, в таблице подсвечиваются цветом все строки, которые содержат это значение (фамилию). Чтобы в этом убедится в выпадающем списке B1 выберите другую фамилию. После чего автоматически будут выделены цветом уже другие строки. Такую таблицу теперь легко читать и анализировать.

Принцип действия автоматической подсветки строк по критерию запроса очень прост. Каждое значение в столбце A сравнивается со значением в ячейке B1. Это позволяет найти уникальные значения в таблице Excel. Если данные совпадают, тогда формула возвращает значение ИСТИНА и для целой строки автоматически присваивается новый формат. Чтобы формат присваивался для целой строки, а не только ячейке в столбце A, мы используем смешанную ссылку в формуле =$A4.

Читайте также: