Как сделать выборку в excel по цвету?

Фильтр и сортировка ячеек по цвету в Excel

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

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

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

При этом фильтрация позволяет показать только те данные, которые удовлетворяют заданным критериям, и соответственно, скрыть те данные, которые не подходят по условию.
Сортировка же показывает все данные, однако упорядочивает их согласно заданным критериям.

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

  • Автофильтр и инструмент «Настраиваемая сортировка» (доступен начиная с версии Excel 2007);
  • Применение пользовательских функций.

Стандартный фильтр и сортировка по цвету в Excel

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


Добавим фильтр к диапазону с таблицей (выбираем вкладку Главная -> Редактирование -> Сортировка и фильтр или воспользуемся сочетанием клавиш Ctrl + Shift + L), далее щелкаем по стрелке в заголовке столбца и в выпадающем списке можем выбрать любой вариант сортировки или фильтрации:


Из недостатков данного способа фильтрации можно отметить невозможность отфильтровать диапазон по нескольким цветам.

С сортированием подобных проблем не возникает, для этого необходимо последовательно отсортировать данные по заданным цветам.

Например, если мы хотим, чтобы сначала в таблице были показаны ячейки с красной заливкой, а затем с синей, то на первом шаге сделаем сортировку по синей заливке (т.е. сортируем данные в обратном порядке — если в конечном итоге нужен порядок ячеек красный -> синий, то сортируем в порядке синий -> красный):


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


Аналогичного результата также можно добиться отсортировав данные с помощью инструмента Настраиваемая сортировка (также выбираем вкладку Главная -> Редактирование -> Сортировка и фильтр), где можно настроить различные дополнительные параметры и уровни сортировки:

Сортировка и фильтр по цвету с помощью функций

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

Функция цвета заливки ячейки на VBA

Для создания пользовательских функций перейдем в редактор Visual Basic (комбинация клавиш Alt + F11), создадим новый модуль и добавим туда код следующей функции:

Как сделать выборку в Excel из списка с условным форматированием

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

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

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

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

Читать еще:  Как таблицу excel сделать онлайн?

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Сумма ячеек по цвету, шрифту, формату и т.д

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

1. Сохраняем документ с поддержкой макросов, хотя его там и не будет, но в данном случае так надо.

2. Нажатием Ctrl+F3 или на вкладке «Формулы» выбираем «Диспетчер имён», где в строке «Имя» вводим название формулы ЦветЯчейки, а в поле «Диапазон» саму формулу: =ПОЛУЧИТЬ.ЯЧЕЙКУ(63;ДВССЫЛ(«RC[-1]»;0))

Я не буду грузить читателя разъяснением значений этой формулы, главное, чтобы всё работало, не так ли?)) Отмечу лишь, что 63 это код цвета заливки, заменяя который, можно подсчитывать ячейки по формату, шрифту, выделению курсивом и т.п.

Вот таблица с кодами и их значениями:

3. В соседнюю от цветной ячейки вводим «равно» и имя созданной формулы ЦветЯчейки, затем протягиваем её вниз. В результате отображаются коды цветов, которые будут необходимы нам для подсчёта.

4. Теперь с помощью формулы =СУММЕСЛИ(B2:B12;10;A2:A12) можно посчитать сумму цветных ячеек, где 10 это код цвета, для жёлтых — 6:

Созданная формула =ЦветЯчейки определяет 56 цветов. На некоторые цветовые гаммы он реагирует некорректно, так что любителям 50 оттенков серого придётся воспользоваться хардкорным чёрным или серым))

Вот пример палитры «твёрдых» цветов:

Следует учесть, что Excel при изменении цвета ячейки автоматически не совершит пересчёт данных, для этого необходимо заново ввести формулу, либо нажать Ctlr+Alt+F9, что гораздо проще и быстрее.

Также на этот случай есть макрос,

1. Нажимаем Alt+F11 и в открывшемся окне вводим следующий текст

Public Function SumByColor(DataRange As Range, ColorSample As Range) As Double

Читать еще:  Как сделать фильтр в excel от меньшего к большему?

Dim Sum As Double

For Each cell In DataRange

If cell.Interior.Color = ColorSample.Interior.Color Then

Sum = Sum + cell.Value

2. В разделе «Формулы» — «Вставить функцию» выбираем категорию «Определенные пользователем», где указываем нашу функцию, которой задаём диапазон подсчёта и образец цвета:

И получаем результат:

Чтобы макрос учитывал не цвет заливки фона, а цвет шрифта ячейки, в шестой строке заменяем свойство Interior на Font в обеих частях выражения.

Для подсчёта количества цветных ячеек, а не суммы, заменяем в седьмой строке Sum = Sum + cell.Value на Sum = Sum + 1

Как и в первом варианте, макрос также не может подсчитать сумму ячеек автоматически, поэтому после произведённых изменений нажимаем клавишу F9.

Если значения в Вашей таблице окрашены в различные цвета и нужно их пересчитать по отдельности, то просто замените ячейку с образцом на цвет из таблицы и нажав F9 получите мгновенный результат.

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

Следует учесть, что функция перебирает все, в том числе и пустые ячейки, в DataRange, поэтому задавайте в качестве первого аргумента только диапазон со значениями, а не весь столбец, иначе Excel «зависнет» надолго.

P.S. Уважаемые подписчики, посты про Excel будут выходить по понедельникам

Дяденька, а можно на Облако выкладывать файл-образец с формулами из поста? Чтобы потыкаться и скопировать себе нужное. Чтобы понять быстрее, как работает эта функция. А то получайникам сложно, а ужас как нужно это дело!! ))

Ссылка на файл в облаке

На каком сайте лучше в облаке выкладывать? Некоторые боятся скачивать файлы с неизвестных источников.

Можно создать папочку, расшарить ее и туда добавлять файлы согласно выходу постов. Вышел 12 ноября — «2019, ноябрь 12 — Сумма ячеек по цвету, шрифту, формату и т.д.»

Начало продумать — чтобы сортировка была красивая.

И всегда ссылку в постах одну — на эту папочку.

И места мало будет занимать и людям удобно.

И в файле можно ссылку на пост делать — чтобы нашёл файл, и оттуда на пост пошёл )

Спасибо! Очень жду по цвету.

Сегодня сделаю в облаке и выложу электронный вариант

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

Например Облако мейл ру, главное не архив, а сам файл екселя. И там идёт открытие сначала в браузере же, и проверка на вирусы тоже есть

Качать файл с поддержкой макросов от анонимуса из интернета? Ну так себе идейка. Тем более совсем получайник не найдет как эти самые макросы разрешить.

Попробуйте пошагово сделать ровно то, что в посте.

Я стараюсь подробно описать порядок действий, чтобы даже самый неопытный пользователь смог воспользоваться этими формулами

Гугл «как включить макросы», первый же запрос на сайт Майкрософта ведёт. Там два клика.

И остается получайник с разрешенными макросами.

Нет, не так, грузи давай)))

Иногда помощь приходит оттуда, откуда её не ждёшь)

Спасибо, в закладки

Как же это вовремя! Как раз надо было! Спасибо!

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

Просто сделайте добавить строку перед ячейкой со значением, всё вниз и опустится

Да это понятно. Хотелось бы автоматический способ.

Увы мне такой способ не знаком

Я прошу прощения, но вы не могли бы писать (допустим в скобках) и английские клманды тоже? Пожалуйста. 🙂

Чет у меня не пашет 🙁

Документ сохранен с поддержкой макросов?

В word есть такая штука, как рассылка.

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

Есть ли в excel что-то подобное?

Например есть список гостей и пригласительный сверстаный в excel. И нужно для каждого гостя распечатать пригласительный.

Попробуйте просто запись макроса. Если разовая задача- то это будет проще, чем разбираться с VBA.

Я через Apache POI делаю сейчас. Получается книга из 1600 листов, приходится бить на несколько файлов.

А зачем эта книга вообще? Задача ведь: изменить ФИО-отправить на печать- выбрать следующего адресата-изменить ФИО и т.д.

Данные у вас и так имеются в отдельной таблице.

Читать еще:  Как в excel сделать переход на другую ячейку стрелками?

Ну или печатайте сначала в ПДФ с названием файла в виде ФИО, если перед печатью надо все проверить. А потом просто скопом отправляйте это барахло на принтер.

По итогу или 10 минут в записи и опробовании макроса и 1600 нажатий клавиш, или еще 20-40 минут на изучение как работают переменные в VBA и как найти кол-во строк — и у вас все генерится по одной кнопке. Это если с VBA вообще не сталкивался.

Документ не для меня, я лишь исполнитель.

Ну если сейчас все работает и всех устраивает -менять только проблем наживёте ))

Но попробуйте просто для себя заколбасить это в екселе без апача. Ессно, поинтересовавшись, а чего с этими 160 страничными доками потом делают.

А то окажется, что печатают этикетки. Где в программах для принтера уже имеются вот эти все функции вытаскивания данных из таблицы.

Вы близки к истине.

Этикетки это word документы. Из них нужно протоколы ОТК сделать по шаблону. Из них и из таблички.

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

BarTender из ексель- таблицы данные вытягивает. ZebraDesigner тоже.
Или у вас там хитрый принтер этикеток какой?

Но по скудным от вас сообщениям как и чего, даже догадки сложно строить )) Может быть и помог бы чем.

Этикетки это обычный лист А4 в ворд документе. Не пользуются они специализированным софтом.

Каждая этикетка в течении года руками составлялась. И фактически только там ТТХ изделия есть.

Теперь же нужно из таблицы эксель с перечнем изделий и некоторыми параметрами для каждой строки распарить этикетку. И потом для каждой строки сгенерировать заполненный документ на основе данных из таблицы и этикетки.

И я это сделал, но через POI, т.к. мне проще было на scala написать, чем с VBA разбираться.

Реализуйте все в екселе. Из ворда довольно легко все перекинуть.

Заодно многоязычность можно прикрутить и всякие проверки заполнения.

Вот тут вариант реализации. Покопайтесь, очень интересный опросник. @Veseliy.4el может и на его основе пару постов забабахает. И на все ТТХ сделать один документ с выбором из списка. Однокнопочные сотрудники будут рады.

А уж сгенерировать нечто (в ПДФ-е для распечатки или новый документ) можно или макросом или через VBA. Главное иметь данные в табличном виде, а не разбросанные по вордовским документам. Особенно если там разная верстка и делали это разные люди.

значимое форматирование — от лукавого

начнутся потом формулы, которые меняют ячейки в зависимости от цвета..

оформление должно оставаться оформлением

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

тогда информация будет полной и экспортируемой вне зависимости от используемого инструмента

А впр по цвету ячейки есть?

Первый раз использую пикабу, как «ответы майл», но может кто знает, как подсчитать сумму чисел, написанных в одной ячейке через запятую?=)))

1. Самый быстрый и простой способ. Выделить ячейку, нажать Ctrl+H, «Найти» — запятую, «Заменить на» — знак плюс. Потом просто в начале формулы вставить «=» и нажать Enter.

2. Чуть менее быстрый способ. Скопировать ячейку/всю колонку с такими ячейками в Блокнот, сохранить в нем файл с расширением CSV (для этого выбрать формат «Все файлы» при сохранении), затем в Excel выбрать Данные — Из текста/CSV, указать сохраненный файл, при необходимости указать, каким символом разделены колонки (по умолчанию — запятой), получится таблица, где все числа встанут в отдельных колонках. Просуммировать все колонки. Этот способ имеет смысл, когда надо еще что-нибудь делать с цифрами.

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

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

Ссылка на основную публикацию
Adblock
detector