Poldet1.ru

Пул Дет №1
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как применить цветные строки или столбцы в 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 и нажмите Главная — Условное форматирование — Создать правило, затем выберите Тип правила с помощью формулы. (Использовать формулу) и введите следующую формулу:

Читайте так же:
Как преобразовать десятичные градусы в градусы минут секунды в Excel?

Формула массива в условном форматировании

В английской версии эта формула выглядит следующим образом:

Логика здесь более сложная. По сути, это табличная формула, которая вычисляет номер группы (детали) списка и определяет, является ли он четным или нечетным:

  • 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

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

Читайте так же:
Как преобразовать даты в текст или дату форматирования текста в реальную дату в Excel?

Таблица

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

Стандартный фильтр

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

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

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

Сортирование диапазона по синей заливке

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

Сортирование диапазона по красной заливке

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

Параметры настраиваемой сортировки

Как настроить постоянный цвет ячейки, основываясь на её текущем значении

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

Решение: найдите все ячейки с одним или несколькими заданными значениями с помощью инструмента Найти все, а затем измените форматирование найденных ячеек с помощью диалога Формат ячеек.

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

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

Найти и выделить все ячейки, удовлетворяющие заданному условию

Здесь существует множество возможностей, в зависимости от того, какую ценность вы ищите.

Чтобы выделить цветом ячейки с определенным значением — например, 50, 100 или 3,4 — нажмите кнопку Найти выделенное > Найти на вкладке Главная в разделе Редактирование на вкладке Главная в разделе Редактирование.

Изменяем цвет заливки ячеек в Excel

Введите нужное значение и нажмите Найти все.

Изменяем цвет заливки ячеек в Excel

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

Изменяем цвет заливки ячеек в Excel

Теперь нажмите на один из элементов, расположенных в нижней части диалогового окна Найти и заменить, и нажмите Ctrl+A, чтобы выделить все найденные записи. Затем нажмите кнопку Закрыть.

Изменяем цвет заливки ячеек в Excel

Вот как можно выделить все ячейки с заданным значением (значениями) с помощью опции Найти все в Excel.

В реальности, однако, нам нужно найти все цены на бензин выше $3,7. К сожалению, инструмент «Найти и заменить» не может помочь нам в этом.

Измененяем цвета заливки выделенных ячеек при помощи диалогового окна «Формат ячеек»

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

Существует три способа доступа к диалоговому окну «Формат ячеек»:

  • Нажав Ctrl+1. Щелкните правой кнопкой мыши на выделенной ячейке и выберите в контекстном меню пункт Форматировать ячейки.
  • На вкладке Главная > Ячейки > Формат > Форматировать ячейки.
Читайте так же:
Как преобразовать разницу во времени между двумя значениями времени в число (часы / минуты / секунды) в Excel?

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

Изменяем цвет заливки ячеек в Excel

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

Изменяем цвет заливки ячеек в Excel

Это результат изменения форматирования в Excel:

Изменяем цвет заливки ячеек в Excel

Этот метод (вместо условного форматирования) гарантирует, что цвет заливки никогда не изменится без вашего ведома, независимо от того, на какое значение изменится значение.

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

Можно сохранить на виду все ключевые наименования таблицы, как по горизонтали, так и по вертикали — для этого следует закрепить целую область в Excel:

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

4 Функция СОВПАД

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

  1. В третьем столбце, где будут размещаться результаты, вводится формула =ИЛИ(СОВПАД(I6;$H$6:$H$19))
  2. Вместо нажатия Enter нажимается комбинация клавиш Ctr + Shift + Enter. Результатом станет появление фигурных скобок слева и справа формулы.
  3. Формула протягивается вниз, до конца сравниваемой колонки — в данном случае проверяется наличие данных из второго столбца в первом. Это позволит изменяться сравниваемому показателю, тогда как знак $ закрепляет диапазон, с которым выполняется сравнение.

Результатом такого сравнения будет вывод уже не найденного совпадающего значения, а булевой переменной. В случае нахождения это будет «ИСТИНА». Если ни одного совпадения не было обнаружено — в ячейке появится надпись «ЛОЖЬ».

Читайте так же:
Как преобразовать формат времени дд чч мм в часы или минуты в Excel?

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

 Как в Экселе посчитать сумму определенных ячеек

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector