Загрузка...Как применить цветные строки или столбцы в Excel?
Подсветка наборов строк цветом
Вот простой (на первый взгляд), но очень полезный прием: подчеркивайте группы строк с общим значением в любом столбце чередующимися заливками. В приведенном ниже списке модели автомобилей перечислены по столбцу A:
Мы уже рассматривали заполнение зеброй, а также ограничение строк горизонтальными линиями. Теперь давайте рассмотрим и этот вариант, тем более что он обладает некоторыми интересными возможностями даже для опытных пользователей Excel.
Вариант 1. Вспомогательный столбец с формулой
Давайте добавим в нашу таблицу еще один столбец с формулой, которая определяет, должны ли соответствующие строки быть заполнены (1) или нет (0). Сначала определим номер группы:
Логика формулы проста: если содержимое текущей ячейки (A2) не равно содержимому предыдущей ячейки (A1), мы прибавляем единицу к предыдущему значению во вспомогательном столбце (F1+1), в противном случае мы оставляем предыдущее значение (F1).
Чтобы чередовать цвета, примените функцию вычисления остатка от деления на 2 к полученному значению группового номера:
В английской версии Excel формула будет выглядеть как =MOD(IF(A2*>A1;F1+1;F1);2).
Наконец, нам нужно применить условное форматирование, чтобы заполнить строки с 1 во вспомогательном столбце. Для этого выделим нашу таблицу от ячейки A2 до конца, нажмем Главная — Условное форматирование — Создать правило и выберем тип правила Использовать формулу. (Используйте формулу и введите простое условие :
Убедитесь, что вы правильно ввели знаки доллара и выбрали цвет с помощью кнопки Формат. После нажатия кнопки OK блоки строк для каждого шаблона будут выделены цветом.
Способ 2. Формулой массива без вспомогательного столбца
Относительно экзотический способ, который использует формулу массива в качестве критерия условного форматирования.
Начните с ячейки A2 и нажмите Главная — Условное форматирование — Создать правило, затем выберите Тип правила с помощью формулы. (Использовать формулу) и введите следующую формулу:
В английской версии эта формула выглядит следующим образом:
Логика здесь более сложная. По сути, это табличная формула, которая вычисляет номер группы (детали) списка и определяет, является ли он четным или нечетным:
- COUNTRY($A$2:$A2;$A$2:$A2) — подсчитывает количество вхождений каждой модели в список, т.е. для Avensis=3, для Corolla=2 и т.д.
- HELTAL(SUM(1/SUM($A$2:$A2;$A$2:$A2)) — подсчитывает порядковый номер для каждой модели, напр. для Avensis=1, для Corolla=2, для Escape=3 и т.д.
- OSTAT(. ;2) — вычисляет остаток от порядкового номера путем деления на 2 для изменения цветов для каждого блока строк, т.е. для всех строк с Avensis=0, для всех строк с Corolla=1, для всех строк с Escape=1 и т.д.
Плюсами этого метода являются компактность и отсутствие необходимости делать вспомогательный столбец. Есть и минусы, в том числе то, что все формулы (а есть еще и формула массива) пересчитываются на лету. Excel с большим количеством строк значительно замедляется из-за такого пересчета. Такой подход не подходит для больших таблиц.
Способ 3. Макрос
Как всегда, практически любую задачу в Microsoft Excel можно решить с помощью макроса. Нажмите Alt+F11 или кнопку Visual Basic на вкладке «Разработчик», чтобы открыть редактор макросов. Затем вставьте новый пустой модуль через меню Insert — Module и скопируйте этот простой код в этот модуль:
Теперь можно выбрать диапазон данных и запустить макрос с помощью Alt+F8. Макрос запросит у пользователя номер столбца для анализа данных, а затем отформатирует строки в выбранном диапазоне, чередуя заливку при изменении значений в указанном столбце.
Стандартный фильтр и сортировка по цвету в Excel
Представьте себе таблицу с названиями товаров и доходами, где определенные категории выделены цветом и текстом:
Добавим фильтр к диапазону с таблицей (выбираем вкладку Главная ->Редактирование ->Сортировка и фильтр или воспользуемся сочетанием клавиш Ctrl + Shift + L), далее щелкаем по стрелке в заголовке столбца и в выпадающем списке можем выбрать любой вариант сортировки или фильтрации:
К недостаткам этого метода фильтрации относится невозможность отфильтровать диапазон по нескольким цветам.
При сортировке таких проблем не возникает; нужно просто упорядочить данные по цветам.
Например, если мы хотим, чтобы сначала в таблице были показаны ячейки с красной заливкой, а затем с синей, то на первом шаге сделаем сортировку по синей заливке (т.е. сортируем данные в обратном порядке — если в конечном итоге нужен порядок ячеек красный -> синий, то сортируем в порядке синий -> красный):
В результате синие ячейки расположились вверху таблицы, однако остальные идут вразнобой и не упорядочены.
На втором шаге повторно произведем сортирование данных, но на этот раз выберем в качестве критерия сортировки красную заливку:
Аналогичного результата также можно добиться отсортировав данные с помощью инструмента Настраиваемая сортировка (также выбираем вкладку Главная ->Редактирование ->Сортировка и фильтр), где можно настроить различные дополнительные параметры и уровни сортировки:
Как настроить постоянный цвет ячейки, основываясь на её текущем значении
Если этот параметр установлен, цвет заливки не будет меняться независимо от изменений содержимого ячейки в будущем.
Решение: найдите все ячейки с одним или несколькими заданными значениями с помощью инструмента Найти все, а затем измените форматирование найденных ячеек с помощью диалога Формат ячеек.
Это одна из тех редких проблем, объяснения которой вы не найдете ни в файлах справки Excel, ни на форумах, ни в блогах. Это и понятно, поскольку задача необычная. Если вы хотите изменить цвет заливки ячейки навсегда, то есть раз и навсегда (или пока вы не измените его вручную), выполните следующие действия.
Найти и выделить все ячейки, удовлетворяющие заданному условию
Здесь существует множество возможностей, в зависимости от того, какую ценность вы ищите.
Чтобы выделить цветом ячейки с определенным значением — например, 50, 100 или 3,4 — нажмите кнопку Найти выделенное > Найти на вкладке Главная в разделе Редактирование на вкладке Главная в разделе Редактирование.
Введите нужное значение и нажмите Найти все.
Вы можете получить доступ к ряду дополнительных параметров поиска с помощью кнопки Параметры в правой части диалогового окна Найти и заменить. Например, вы можете выбрать опции Искать по регистру и Искать все содержимое ячейки. Можно использовать подстановочные знаки, такие как звездочка (*) для поиска любой строки символов или вопросительный знак (? ) для поиска любого отдельного символа.
Теперь нажмите на один из элементов, расположенных в нижней части диалогового окна Найти и заменить, и нажмите Ctrl+A, чтобы выделить все найденные записи. Затем нажмите кнопку Закрыть.
Вот как можно выделить все ячейки с заданным значением (значениями) с помощью опции Найти все в Excel.
В реальности, однако, нам нужно найти все цены на бензин выше $3,7. К сожалению, инструмент «Найти и заменить» не может помочь нам в этом.
Измененяем цвета заливки выделенных ячеек при помощи диалогового окна «Формат ячеек»
Теперь вы выбрали все ячейки с определенным значением (или значениями), мы только что сделали это с помощью инструмента Найти и заменить. Теперь нужно установить цвет заливки для выделенных ячеек.
Существует три способа доступа к диалоговому окну «Формат ячеек»:
- Нажав Ctrl+1. Щелкните правой кнопкой мыши на выделенной ячейке и выберите в контекстном меню пункт Форматировать ячейки.
- На вкладке Главная > Ячейки > Формат > Форматировать ячейки.
Затем настройте параметры форматирования по своему вкусу. На этот раз мы установим оранжевый цвет заливки, просто для разнообразия.
Если вы хотите изменить только цвет заливки, не изменяя другие параметры форматирования, просто нажмите кнопку Цвет заливки и выберите цвет.
Это результат изменения форматирования в Excel:
Этот метод (вместо условного форматирования) гарантирует, что цвет заливки никогда не изменится без вашего ведома, независимо от того, на какое значение изменится значение.
Как закрепить строку и столбец одновременно
Можно сохранить на виду все ключевые наименования таблицы, как по горизонтали, так и по вертикали — для этого следует закрепить целую область в Excel:
- Нарисуйте верхний и левый края таблицы, которые вы хотите видеть (вы также можете выделить их цветом — это будет описано ниже).
- В самом верху выберите сегмент, который не включен в эту область, под корректируемой строкой и справа от корректируемого столбца.
- Откройте «Вид» и выберите «Исправить области».
- Поверните колесико мыши и проверьте, нет ли недостающей информации.
4 Функция СОВПАД
Достаточно просто выполнить в Эксель сравнение двух столбцов с помощью еще двух полезных операторов — распространенного ИЛИ и встречающейся намного реже функции СОВПАД. Для ее использования выполняются такие действия:
- В третьем столбце, где будут размещаться результаты, вводится формула =ИЛИ(СОВПАД(I6;$H$6:$H$19))
- Вместо нажатия Enter нажимается комбинация клавиш Ctr + Shift + Enter. Результатом станет появление фигурных скобок слева и справа формулы.
- Формула протягивается вниз, до конца сравниваемой колонки — в данном случае проверяется наличие данных из второго столбца в первом. Это позволит изменяться сравниваемому показателю, тогда как знак $ закрепляет диапазон, с которым выполняется сравнение.
Результатом такого сравнения будет вывод уже не найденного совпадающего значения, а булевой переменной. В случае нахождения это будет «ИСТИНА». Если ни одного совпадения не было обнаружено — в ячейке появится надпись «ЛОЖЬ».
Стоит отметить, что функция COUNTRY сравнивает как числа, так и другие типы данных, учитывая верхний регистр. И один из самых распространенных способов использования такой формулы сравнения с двумя столбцами в Excel — это поиск информации в базе данных. Например, различные виды мебели в каталоге.
