Excel — выборка ячеек по цвету заливки

Excel — выборка ячеек по цвету заливки

December 15, 2013

На работе столкнулся с такой задачей — имеется таблица в Excel, в которой ведется табель выходов рабочих в цеху.

В таблице подсчитывается количество часов, фактически отработанных; часов переработки и часов сверх нормы. Так вот, необходимо сделать так, чтобы производилась автоматическая выборка ячеек таблицы по цвету заливки последних.

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

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

В Excel нет встроенных (готовых) инструментов для выборки подобного рода; можно отбирать ячейки только по одному условию — по значению, находящемуся в них. Поэтому решение задачи получалось только одно — через VBA (пользовательские функции).

Прекрасное и готовое решение моей задачи я нашел на сайте http://www.excel-vba.ru/. Даже не одно, а целых два решения, под разные условия. Ниже привожу последовательность шагов, которые привели меня к успеху.

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

Режим “Разработчик” в Excel

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

Переходим в “Пуск — Параметры Excel” и находим в левом списке пункт “Надстройки”:

Выбираем в основном окне строчку “Пакет анализа — VBA” и жмем кнопочку “Перейти” в самом низу окна. Откроется еще одно окошко со списком доступных под Excel расширений (надстроек). Снова выбираем в этом списке “Пакет анализа — VBA” и соглашаемся, что хотим установить его, нажав кнопку “ОК”:

Потребуется установочный диск с Microsoft Office на нем (или же подключение к Интернет) чтобы программа получила необходимые пакеты для инсталляции. Если установка прошла успешно, то в “Ленте” появиться пункт “Разработчик” (Excel 2010). Можно перейти в него через эту панель или же с помощью сочетания клавиш Alt + F11 .

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

Вставка готовых функций в Excel VBA

Но есть готовые решения, которые я вставлю в виде кода с помощью меню “Insert — Module”. Просто берем отсюда код функций и вставляем в свой Excel. Затем сохраняем файл Excel с поддержкой VBA (макросов) и все готово для дальнейшей работы.

Вставленные функции появятся в списке формул таблицы:

Ниже представлен готовый код двух функций на VBA, написанных их автором Дмитрием Щербаковым. Первая функция с именем “CountByInteriorColor” выполняет подсчет количества ячеек по цвету заливки.

Вторая функция с именем “SumByInteriorColor” выполняет выборку ячеек по цвету заливки и суммирует все значения в этих ячейках.

Обе функции имеют одинаковый синтаксис и принимают три входных аргумента, первые два из которых обязательные, а третий — необязательный:

Источник:
http://gearmobile.github.io/excel/excel-select-cell-by-color/

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

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

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

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

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

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

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

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

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


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


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

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

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


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


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

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

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

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

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

Источник:
http://tutorexcel.ru/diapazony/filtr-i-sortirovka-yacheek-po-cvetu-v-excel/

Рекомендации и примеры сортировки и фильтрации данных по цвету

Сортировка и фильтрация данных по цвету значительно упрощает анализ данных и помогает пользователям быстро увидеть на листе выделенные данные и динамику их изменений.

В этой статье

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

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

Чьи продажи услуг в этом месяце составили более 50 000 рублей?

Для каких продуктов прирост выручки по сравнению с прошлым годом превысил 10 %?

Кто из студентов является лучшим и худшим на младшем курсе?

Каковы исключения в отчетах по прибыли за последние пять лет?

Каково общее распределение сотрудников по возрасту?

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

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

На этом рисунке демонстрируется фильтрация и сортировка по цвету и значкам в столбцах «Категория», M/M Δ% и «Наценка».

Фильтрацию можно также выполнять по набору значков, созданному с помощью условного форматирования. Набор значков используется для аннотирования и классификации данных по трем-пяти категориям, разделенным пороговым значением. Каждый значок соответствует диапазону значений. Например, в таблице наборов значков, представленной ниже, в наборе значков «Три стрелки» зеленая стрелка, указывающая вверх, соответствует высоким значениям, желтая стрелка, направленная в сторону, соответствует средним значениям, а красная стрелка вниз — низким значениям.

Таблица наборов значков

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

Эффективное использование цвета при анализе данных

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

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

Использование стандартных цветов и ограничение числа цветов

При фильтрации и сортировке по цвету можно выбрать предпочитаемые цвета, и результат покажется приемлемым для автора. Однако сначала нужно задать себе главный вопрос: «Подойдут ли эти цвета для остальных пользователей?» Современный компьютер способен отображать 16 777 216 цветов в 24-битном режиме. Однако большинство пользователей способны различить лишь незначительную их часть. Кроме того, качество цветопередачи может отличаться от компьютера к компьютеру. Освещение в комнате, качество бумаги, разрешение экрана и принтера и параметры браузера — все эти параметры могут различаться. До 10 % людей испытывают некоторые трудности с различением и восприятием определенных цветов. Это важные переменные факторы, которые вряд ли возможно контролировать.

Читайте также  Поиск или замена текста и чисел на листе - Служба поддержки Office

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

Контраст цвета и фон

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

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

1. Похожие цвета находятся на цветовом круге рядом друг с другом (например, фиолетовый и оранжевый цвета похожи на красный).

2. Контрастный цвет смещен на три цвета от текущего цвета (например, синий и зеленый цвета являются контрастными по отношению к красному).

3. Дополняющие цвета находятся на цветовом круге друг напротив друга (например, сине-зеленый цвет является дополнительным для красного).

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

Совет: При печати документов в цвете перепроверьте цвета ячеек и шрифта на предмет удобочитаемости. Если цвет ячеек слишком темный, попробуйте использовать белый шрифт для улучшения восприятия.

Выбор наиболее подходящих цветов

Нужны краткие рекомендации? Используйте красный, желтый, зеленый или синий цвет на белом или сером фоне.

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

Лучшие 10 цветов

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

Эти цвета можно легко применить к ячейкам и шрифтам с помощью кнопок Цвет заливки и Цвет текста в группе Шрифт на вкладке Главная.

Использование цветов, которые естественно передают смысл

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

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

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

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

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

Несколько примеров

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

Образец данных

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

Чтобы скопировать данные в пустую книгу, выполните указанные ниже действия.

Сохранение образца данных в XLSX-файле

Запустите программу Блокнот.

Выделите текст образца, а затем скопируйте его и вставьте в Блокнот.

Сохраните файл, указав его имя и расширение, например Products.csv.

Выйдите из Блокнота.

Откройте файл, сохраненный в Блокноте.

Сохраните файл в формате XLSX.

Какие виды упаковки используются?

Предположим, вам нужно подобрать различные типы тары для продуктов, но столбец «Тара» отсутствует. Вы можете вручную раскрасить все ячейки в столбце «Количество на единицу», а затем выполнить сортировку по цвету. Можно также добавить условные обозначения, поясняющие пользователю смысл каждого цвета.

Чтобы вручную задать цвета ячеек согласно цветовой схеме из таблицы выше, щелкните каждую ячейку и примените нужный цвет с помощью кнопки Цвет заливки в группе Шрифт на вкладке Главная.

Совет: Кнопка Формат по образцу в группе Буфер обмена на вкладке Главная позволяет быстро применить выбранный цвет к другой ячейке.

Щелкните ячейку в столбце Количество на единицу, на вкладке Главная в группе Правка нажмите Сортировка и фильтр и выберите элемент Настраиваемая сортировка.

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

В разделе Порядок в первой строке выберите красный цвет, во второй — синий, а в третьей — желтый.

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

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

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

Источник:
http://support.microsoft.com/ru-ru/office/%D1%80%D0%B5%D0%BA%D0%BE%D0%BC%D0%B5%D0%BD%D0%B4%D0%B0%D1%86%D0%B8%D0%B8-%D0%B8-%D0%BF%D1%80%D0%B8%D0%BC%D0%B5%D1%80%D1%8B-%D1%81%D0%BE%D1%80%D1%82%D0%B8%D1%80%D0%BE%D0%B2%D0%BA%D0%B8-%D0%B8-%D1%84%D0%B8%D0%BB%D1%8C%D1%82%D1%80%D0%B0%D1%86%D0%B8%D0%B8-%D0%B4%D0%B0%D0%BD%D0%BD%D1%8B%D1%85-%D0%BF%D0%BE-%D1%86%D0%B2%D0%B5%D1%82%D1%83-b1bf3982-051d-49b8-8330-80e99c94365b

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

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

Смотрим мы на кнопки, смотрим, и понимаем, что кнопка автофильтра ячейки с заголовком столбца «С» значительно отличается от кнопок в ячейках-заголовках столбцов «А» и «В»:

Глядя на кнопки в ячейках, мы можем даже зрительно определить, какие команды нами были даны автофильтру.

Наличие маленькой тонкой стрелочки «вверх» или же «вниз», говорит нам о том, что была дана команда «Сортировать». Присутствие на кнопке значка «Воронка», сообщает нам о фильтрации (об отборе) данных по каким-то признакам.

Здесь возникает вопрос: А нам действительно нужно было отсортировать и ещё к тому же отфильтровать данные, или всё же что-то одно сделать?

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

А давайте, возьмём и скажем самим себе, что под сортировкой мы понимаем внутреннюю перестановку данных в таблице (списке). А под фильтрацией, — выборку определённых данных из всего их массива.

Ну, а теперь перейдём к сортировке и фильтрации данных по цвету.

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

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

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

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

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

Основной «пульт управления» форматированием ячеек находится во вкладке «Главная»:

Щёлкнув по стрелочке рядом со значком «Ведёрко» мы раскроем операционное окошко заливки цветом ячеек, а щёлкнув по стрелочке рядом со значком в виде буквы «А», раскроем операционное окошко окрашивания данных, которые находятся внутри выделенной (жирная чёрная рамка) ячейки. Заливать цветом мы можем сразу несколько ячеек, нужно только их выделить заранее. Если мы хотим залить цветом несколько ячеек идущих подряд (не важно, в строке или столбце), то мы должны помогать себе во время выделения удержанием клавиши Shift. Если выделяемые ячейки идут не подряд, то помогаем удержанием клавиши Ctrl. На страницах сайта о таких выделениях мы говорили много.

Давайте зальём каким-нибудь цветом выделенную ячейку «С9»:

Щёлкнем по стрелочке значка «Ведёрко» и перед нами откроется окошко с палитрой:

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

Как только, мы определились с выбором, то щёлкнем по квадратику-цвету, и выделенная ячейка окрасится:

Так же мы действуем в случае, когда собираемся окрасить содержимое выделенной ячейки (данные). Только в этот раз щёлкаем по стрелочке рядом со значком «А»:

Всё то, о чём сейчас шла речь, мы можем сделать в операционном окошке поименованном «Формат ячеек». Для того чтобы это окошко распахнулось, предоставив нам содержащиеся в нём опции, мы должны щёлкнуть по маленькой стрелочке в нижнем правом углу раздела «Шрифт» вкладки верхнего меню «Главная»:

Читайте также  Как в excel сделать по горизонтали буквы?

Вот и открылось перед нами это самое операционное (диалоговое) окошко, в котором, нажав кнопку «Шрифт» верхнего меню:

мы можем выбрать цвет для окраса содержимого ячейки и дополнительно, много чего ещё с этим содержимым (данными) поделать. Завершающим действием будет нажатие кнопки «Ок» в нижней части операционного окошка.

Для получения доступа к палитре для заливки самой ячейки, в верхнем меню окошка мы нажмём кнопку «Заливка»:

Это поле окошка несколько отличается от поля с палитрой для окраса шрифта (данных). Скольжение курсором мыши по цветным квадратикам палитры, в этом случае, не повлечёт одновременного отображение цвета в строке «Образец»:

Цвет, которым будет залита выделенная ячейка, в строке «Образец» отобразится только после щелчка мышью по цветному квадратику палитры цветов:

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

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

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

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

В перечне присутствуют наши «старые знакомые»: значок «Ведёрко» и значок буквы «А». Щелчок по опции «Формат ячеек» откроет для нас уже знакомое операционное окошко, о котором мы только что говорили. Ну а опции «Фильтр» и «Сортировка» в особом представлении не нуждаются. Один щелчок по одной из этих опций и мы уже даём команды фильтру по сортировке или же по фильтрации.

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

Теперь перейдём непосредственно к фильтрации и сортировки по цвету. Перед тем как мы рассмотрим эти процессы, а точнее подготовку к запуску фильтра, хочется сказать о подходах к окрасу ячеек и их содержимого (данных). Это, в сущности, и есть те мероприятия, которые в дальнейшем позволят нам воспользоваться фильтрацией по цвету и сортировкой по цвету.

Мы можем окрашивать данные в момент их ввода. В момент ввода данных можем окрашивать ячейки. Можем окрасить (залить цветом) и ячейки и только что введённые данные. Вспомним, что заливка определённым цветом ячеек ли, данных ли, в большей степени должна иметь смысл, а не просто, потому что так красивее. Так, мы можем выделить цветом некоторые данные или ячейки с целью обратить внимание пользователей этими данными. Окрас в этом случае не лишён смысла.

Давайте рассмотрим на простейшем примере работу автофильтра по цвету. Произведём сначала сортировку фруктов по названиям и сортам.

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

Формируем мы табличку себе и формируем, но вдруг раздаётся звонок товароведа, который просит нас незамедлительно прислать ему данные об остатках красных яблок. Мы отвечаем ему, что через пять минут вышлем (электронной почтой) эти данные.

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

В данном случае нам нужна кнопка-стрелка фильтра в ячейки заголовка столбца «А». Нажмём её, а затем в открывшемся окошке команд, выберем команду «Фильтр по цвету»:

Наведение курсора мыши на команду «Фильтр по цвету» открывает перед нами окошко конкретизации, в котором содержатся цветные прямоугольнички. Эти прямоугольники отображают те цвета, которыми мы окрасили названия фруктов. Яблоки красные в нашей табличке окрашены красным цветом, поэтому мы щёлкнем мышкой по красному прямоугольнику, а затем нажмём кнопку «Ок» в нижней части окошка команд:

И вот какой стала наша таблица:

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

При сортировке по цвету мы проделываем все те же действия. Отличие лишь в том, что мы выбираем команду «Сортировка по цвету»:

После такой сортировки, в таблице будут стоять первыми данные, цвет шрифта которых соответствует, выбранному нами цвету в фильтре:

Теперь рассмотрим сортировку и фильтрацию по цвету ячеек.

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

Этим действием мы сделали выделение всего листа. Теперь обратимся к опции заливки шрифта цветом во вкладке «Главная», где одним щелчком по стрелочке раскроем окошко выбора цвета:

В этом окошке щёлкнем по варианту «Авто» и окрас шрифта будет аннулирован:

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

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

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

Вот так выглядит наша таблица после заливки ячеек цветом:

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

Сделав выбор цвета, жмём кнопку «Ок», а затем смотрим на изменения в таблице:

Предположим, что нам нужно сообщить в отдел закупок о фруктах, запасы которых относятся к пограничной зоне и требуют пополнения в ближайшие два дня. Нажатием кнопки-стрелки в ячейке с заголовком столбца «С» мы вызываем окошко команд, в котором выбираем команду «Фильтр по цвету», а в появившемся окошке с перечнем цветов, которыми окрашены ячейки, жёлтый:

И вот такой стала наша таблица после фильтрации:

Нам и кнопки «Ок» нажимать не пришлось, потому что фильтр сразу же представил нам таблицу с отфильтрованными данными.

Мы можем использовать фильтр для окраса тех данных или ячеек, которые хотим выделить цветом.

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

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

А можем и целой строкой:

Для выделения всей строки листа нам нужно щёлкнуть мышью по квадратику №2 нумерации строк листа:

А затем, удерживая левую кнопку мыши нажатой, спуститься на строку ниже (№6), и спустившись, щелчком уже правой кнопки мыши, вызвать операционное окошко, содержащее нужные опции:

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

Сделаем щелчок вне поля выделения для его же отмены, а затем отключим фильтр щелчком по значку «Воронка» во вкладке «Данные». И вот что у нас получилось:

Источник:
http://pmweb.ru/sortirovka-i-filtratsiya-dannykh-po-tsvetu-v-excel-2010

Запрос на выборку данных (формулы) в EXCEL

13 апреля 2013 г.

Суть запроса на выборку – выбрать из исходной таблицы строки, удовлетворяющие определенным критериям (подобно применению стандартного Фильтра ). Произведем отбор значений из исходной таблицы с помощью формул массива . В отличие от применения Фильтра ( CTRL+SHIFT+L или Данные/ Сортировка и фильтр/ Фильтр ) отобранные строки будут помещены в отдельную таблицу.

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

1. Один числовой критерий (Выбрать те Товары, у которых цена выше минимальной)

Пусть имеется Исходная таблица с перечнем Товаров и Ценами (см. файл примера, лист Один критерий — число ).

Необходимо отобразить в отдельной таблице только те записи (строки) из Исходной таблицы, у которых цена выше 25.

Решить эту и последующие задачи можно легко с помощью стандартного фильтра . Для этого выделите заголовки Исходной таблицы и нажмите CTRL+SHIFT+L . Через выпадающий список у заголовка Цены выберите Числовые фильтры. , затем задайте необходимые условия фильтрации и нажмите ОК.

Будут отображены записи удовлетворяющие условиям отбора.

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

Критерий (минимальную цену) разместим в ячейке Е6 , таблицу для отфильтрованных данных — в диапазоне D10:E19 .

Теперь выделим диапазон D11:D19 (столбец Товар) и в Строке формул введем формулу массива :

Вместо ENTER нажмите сочетание клавиш CTRL+SHIFT+ENTER .

Те же манипуляции произведем с диапазоном E11:E19 куда и введем аналогичную формулу массива :

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

Чтобы показать динамизм полученного Отчета (Запроса на выборку) введем в Е6 значение 65. В новую таблицу будет добавлена еще одна запись из Исходной таблицы, удовлетворяющая новому критерию.

Если в Исходную таблицу добавить новый товар с Ценой в диапазоне от 25 до 65, то в новую таблицу будет добавлена новая запись.

В файле примера также содержатся формулы массива с обработкой ошибок, когда в столбце Цена содержится значение ошибки, например #ДЕЛ/0! (см. лист Обработка ошибок ).

Читайте также  Создание и настройка сводных таблиц Excel 2007

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

3. Один критерий Дата (Выбрать те Товары, у которых Дата поставки совпадает заданной)

Пусть имеется Исходная таблица с перечнем Товаров и Датами поставки (см. файл примера, лист Один критерий — Дата ).

Для отбора строк используются формулы массива, аналогичные Задаче1 (вместо критерия =$B$12:$B$20)*(СТРОКА($B$12:$B$20)-СТРОКА($B$11));$J$12-СТРОКА(A12)+СТРОКА($B$11)+1))

Примечание : После ввода формулы вместо клавиши ENTER (ВВОД) нужно нажать сочетание клавиш CTRL+SHIFT+ENTER. Это сочетание клавиш используется для ввода формул массива.

Скопируйте формулу массива вниз на нужное количество ячеек. Формула вернет только те значения Товаров, которые были поставлены в диапазоне указанных дат. В остальных ячейках будут содержаться ошибки #ЧИСЛО! Ошибки в файле примера (Лист 4.Диапазон Дат) скрыты с помощью Условного форматирования .

Аналогичную формулу нужно ввести и для дат в столбец E.

В ячейке J12 вычислено количество строк исходной таблицы, удовлетворяющих критериям:

Для ввода первой формулы выделите диапазон ячеек G12:G20 . После ввода формулы вместо клавиши ENTER (ВВОД) нужно нажать сочетание клавиш CTRL+SHIFT+ENTER.

Решение3 : Если столбец Дат СОРТИРОВАН, то можно не использовать формулы массива.

Сначала необходимо вычислить первую и последнюю позиции строк, которые удовлетворяют критериям. Затем вывести строки с помощью функции СМЕЩ() .

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

5. Один критерий Дата (Выбрать те Товары, у которых Дата поставки не раньше/ не позже заданной)

Пусть имеется Исходная таблица с перечнем Товаров и Датами поставки (см. файл примера, лист Один критерий — Дата (не позже) ).

Для отбора строк, дата которых не раньше (включая саму дату), используется формула массива:

= ИНДЕКС(A12:A20;НАИМЕНЬШИЙ(ЕСЛИ($E$7 C15;И($B$7>=B15;$B$7 =$B$13:$B$21)*($B$13:$B$21>0);СТРОКА($B$13:$B$21);»»);СТРОКА($B$13:$B$21)-СТРОКА($B$12)) -СТРОКА($B$12))

Условие $E$7=$A$13:$A$21 гарантирует, что будут отобраны товары только определенного типа. Условие $E$8>=$B$13:$B$21 гарантирует, что будут отобраны даты не позже заданной (включая). Условие $B$13:$B$21>0 необходимо, если в диапазоне дат имеются пустые ячейки. Знак * (умножение) используется для задания Условия И (все 3 критерия должны выполняться для строки одновременно).

Примечание . Случай, когда список несортирован, рассмотрен в статье Поиск ДАТЫ (ЧИСЛА) ближайшей к заданной, с условием в MS EXCEL. Несортированный список .

7. Один Текстовый критерий (Выбрать Товары определенного вида)

Пусть имеется Исходная таблица с перечнем Товаров и Ценами (см. файл примера, лист Один критерий — Текст ).

Задача решается аналогично Задачам 1 и 3. Более подробное решение см. в статье Поиск ТЕКСТовых значений в MS EXCEL с выводом их в отдельный список. Часть1. Обычный поиск .

8. Два Текстовых критерия (Выбрать Товары определенного вида, поставленные в заданный месяц)

Пусть имеется Исходная таблица с перечнем Товаров и Ценами (см. файл примера, лист 2 критерия — текст (И) ).

Для отбора строк используется формула массива:

Выражение ($F$6=$A$11:$A$19)*($F$7=$B$11:$B$19) задает оба условия (Товар и Месяц).

Выражение СТРОКА(ДВССЫЛ(«A1:A»&ЧСТРОК($A$11:$A$19))) формирует массив последовательных чисел <1:2:3:4:5:6:7:8:9>, т.е. номера строк в таблице.

9. Два Текстовых критерия (Выбрать Товары определенных видов)

Пусть имеется Исходная таблица с перечнем Товаров и Ценами (см. файл примера, лист 2 критерия — текст (ИЛИ) ).

В отличие от Задачи 7 отберем строки с товарами 2-х видов ( Условие ИЛИ ).

Для отбора строк используется формула массива:

= ИНДЕКС(A$11:A$19; НАИБОЛЬШИЙ((($E$6=$A$11:$A$19)+($E$7=$A$11:$A$19))*(СТРОКА($A$11:$A$19)-СТРОКА($A$10)); СЧЁТЕСЛИ($A$11:$A$19;$E$6)+СЧЁТЕСЛИ($A$11:$A$19;$E$7)-ЧСТРОК($A$11:A11)+1))

Условие ($E$6=$A$11:$A$19)+($E$7=$A$11:$A$19) гарантирует, что будут отобраны товары только заданных видов из желтых ячеек (Товар2 и Товар3). Знак + (сложение) используется для задания Условие ИЛИ (должен быть выполнен хотя бы 1 критерий).

Вышеуказанное выражение вернет массив <0:0:0:0:1:1:1:0:0>. Умножив его на выражение СТРОКА($A$11:$A$19)-СТРОКА($A$10) , т.е. на массив последовательных чисел <1:2:3:4:5:6:7:8:9>, получим массив позиций (номеров строк таблицы), удовлетворяющих критериям. В нашем случае это будет массив <0:0:0:0:5:6:7:0:0>.

С помощью функции НАИБОЛЬШИЙ() выведем 3 значения из позиции 5 (строка 15 листа), 6 (16) и 7 (17), т.е. значения Товар2, Товар2 и Товар3. Для этого используем выражение СЧЁТЕСЛИ($A$11:$A$19;$E$6)+СЧЁТЕСЛИ($A$11:$A$19;$E$7)-ЧСТРОК($A$11:A11)+1 , которое последовательно (начиная со строки 11) будет возвращать числа 3; 2; 1; 0; -1; -2; . Формула НАИБОЛЬШИЙ(. ;3) вернет число 5, НАИБОЛЬШИЙ(. ;2) вернет число 6, НАИБОЛЬШИЙ(. ;1) вернет число 7, а НАИБОЛЬШИЙ(. ;0) и далее вернет ошибку, которую мы скроем условным форматированием .

И наконец, с помощью функции ИНДЕКС() последовательно выведем наши значения из соответствующих позиций: = ИНДЕКС(A$11:A$19;5) вернет Товар2, = ИНДЕКС(A$11:A$19;6) вернет Товар2, = ИНДЕКС(A$11:A$19;7) вернет Товар3.

10. Отбор значений с учетом повторов

В разделе Отбор на основании повторяемости собраны статьи о запросах с группировкой данных. Из повторяющихся данных сначала отбираются уникальные значения, а соответствующие им значения в других столбцах — группируются (складываются, усредняются и пр.).

Наиболее популярные статьи из этого раздела:

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

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

Предположим, что нас интересует сколько и каких партий товаров поставлялось по цене от 1000р. до 2000р. (критерий 1). Причем, партий с одинаковой ценой должно быть минимум 3 (критерий 2).

Решением является формула массива:

Эта формула возвращает номера строк, которые удовлетворяют обоим критериям.

Формула =СУММПРОИЗВ(($C$14:$C$27>=$B$7)*($C$14:$C$27 =$B$10)) подсчитывает количество строк, которые удовлетворяют критериям.

В файле примера на листе «10.Критерий — колич-во повторов» настроено Условное форматирование , которое позволяет визуально определить строки удовлетворяющие критериям, а также скрыть ячейки, в которых формула массива возвращает ошибку #ЧИСЛО!

11. Используем значение критерия (Любой) или (Все)

В фильтре Сводных таблиц MS EXCEL используется значение (Все), чтобы вывести все значения столбца. Другими словами, в выпадающем списке значений критерия содержится особое значение, которое отменяет сам критерий (см. статью Отчеты в MS EXCEL , Отчет №3).

В файле примера на листе «11. Критерий Любой или (Все)» реализован данный вариант критерия.

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

Остальная часть формулы аналогична рассмотренным выше.

12. Актуальная цена

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

Чтобы иметь перечень товаров с актуальными ценами придется использовать формулы:

2) определяем последнюю (максимальную) дату для каждого товара с помощью формулы массива =МАКС((Таблица1[товар]=E8)*Таблица1[дата])

3) наконец, выводим актуальную цену =СУММЕСЛИМН(Таблица1[цена];Таблица1[товар];E8;Таблица1[дата];F8)

Для товара не должно быть повторов дат, иначе цены будут суммироваться (если повторяется последняя дата).

В файле примера приведено решение на листе 12. Актуальная цена.

Источник:
http://excel2.ru/articles/zapros-na-vyborku-dannyh-formuly-v-ms-excel

Цветной выпадающий список в Excel

Из этой статьи Вы узнаете как создать выпадающий список в Excell и раскрасить его.

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

Если Вам нужен красивый выпадающий список первым делом создайте отдельный лист (Sheet) и в столбик выпишите все опции, которые хотите видеть в данном списке.

Пишите в том порядке, в котором хотите увидеть в списке. Раскрашивать пока не обязательно. По-настоящему цвет задаётся на другом этапе.

1. Выпишите в столбец опции, которые Вам нужны в выпадающем списке

2. Выделите всё, что написали и кликните правой кнопкой мыши → Выберите команду Присвоить имя (Define Name).

3. В поле Имя (Name) введите имя записей, например Результаты (Results), и нажмите кнопку ОК.

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

4. Щелкните ячейку на листе, в которую требуется поместить раскрывающийся список.

Перейдите в раздел Data и нажмите Data Validation.

Если в Вашей версии Excel надписи Data Validation нет — ищите значок

5. На вкладке Параметры (Settings) в поле Разрешить (Allow) нажмите кнопку Список (List).

6. В поле Источник (Source) введите знак равенства
(=), а сразу за ним — имя, присвоенное списку в шаге 3. Например: =Results.

8. В выбранной ячейке появится список. Пока что чёрно-белый.

Редактирование выпадающего списка

Изменение раскрывающегося списка, основанного на именованном диапазоне.

Откройте лист, содержащий именованный диапазон для раскрывающегося списка.

Мой список уже раскрашен, но на добавление нового элемента это никак не влияет.

Чтобы добавить элемент, перейдите в конец списка и введите новый элемент.

На вкладке Формулы (Formulas) нажмите кнопку Диспетчер имен (Name Manager).

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

Изменяем диапазон. Я заменил 7 на 8.

Затем добавляем цвет если нужно.

Чтобы посмотреть список всех доступных диапазонов нажминте CTRL + G или из вкладки Home перейдите в Find & Select и нажмите Go to:

Список будет выглядеть примерно так:

Добавление цвета в выпадающие ячейки

Чтобы сделать список цветным выделяем ячейку. Переходим в Home → Условное форматирование (Conditional Formatting) → Новое правило (New Rule)

Выбираем Format only cells that contain → Меняем условие на equal to Вводим первый элемент из нашего списка в данном случае OK → Format

Я хочу, чтобы у варианта OK был зелёный фон.

Чтобы раскрасить все элементы выпадающего списка перейдём в редактирование правила.

Conditional Formattiong → Manage Rules

Создаем новое правило (New Rule) для каждого элемента списка. И так пока не создадите все необходимые правила. Я создал семь правил.

Проверяем созданный список правил.

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

Пройти обучение Excel можно на сайте SkillBox

Возможные проблемы

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

Источник:
http://www.andreyolegovich.ru/PC/ms_office/ms_excel/ms_excel_dropdown_with_colour.php