Как сортировать по цвету в excel

REDMOND

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

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

В этой статье

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

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

Кто за этот месяц продал услуг более чем на 50 000 рублей?

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

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

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

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

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

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

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

Условное форматирование на основе цветов фона ячеек и наборов значков

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

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

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

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

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

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

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

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

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

Не забывайте про контраст цвета и фон

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

REDMOND

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

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

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

Несколько примеров с пошаговыми объяснениями

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

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

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

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

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

Запустите программу Блокнот (Майкрософт).

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Сортировка диапазона по цвету

Способ 1. Если у вас Excel 2007 или новее.

Тут все просто. Начиная с 2007-й версии в Excel добавили сортировку и фильтрацию по цвету заливки и по цвету шрифта как штатную функцию. Проще всего до них добраться через стандартный автофильтр:

color-sort1.png

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

Способ 2. Если у вас Excel 2003 или старше.

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

Для этого откройте редактор Visual Basic через меню Сервис — Макрос — Редактор Visual Basic (Tools — Macro — Visual Basic Editor) , вставьте новый пустой модуль (меню Insert — Module) и скопируйте туда текст простой функции:

Теперь можно закрыть редактор Visual Basic, вернуться в Excel и, выделив любую пустую ячейку, вызвать созданную функцию ColorIndex через меню Вставка — Функция — категория Определенные пользователем (Insert — Function — User defined) . В качестве аргумента укажите ячейку, цвет заливки которой хотите получить в виде цифрового кода.

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

color-sort2.png

Если вам нужно вытаскивать не код цвета заливки, а код цвета шрифта, то функция слегка изменится:

Наша функция ColorIndex, к сожалению, имеет пару недостатков:

  • С ее помощью нельзя получить цвет, который ячейка имеет при использовании условного форматирования
  • Она не пересчитывается автоматически при изменении цвета одной из ячеек, поскольку Excel не считает изменение цвета редактированием содержимого ячейки и не запускает автоматического пересчета листа. Это нужно сделать самому, нажав Ctrl+Alt+F9, либо дописав к нашей функции в каждой ячейке вот такую добавку:
    =ColorIndex(A2)+СЕГОДНЯ()*0
    чтобы содержимое ячейка пересчитывалась автоматически при каждом пересчете листа.

REDMOND

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