Как чередовать цвет строк на основе групп в Excel?
В Excel для большинства из нас может быть проще раскрасить каждую вторую строку, но пробовали ли вы когда-нибудь чередовать строки на основе изменений значения в столбце – как показано на следующем скриншоте в столбце A? В этой статье я расскажу, как чередовать цвет строк на основе групп в Excel.
Чередование цвета строк на основе изменений значений с помощью полезной функции
Чередование цвета строк на основе изменений значений с помощью вспомогательного столбца и условного форматирования
Чтобы выделить строки попеременно на основе групп, прямого способа нет, поэтому вам нужно создать вспомогательный столбец, а затем применить функцию условного форматирования для их окрашивания. Пожалуйста, действуйте следующим образом:
1. В ячейке D1, той же строке заголовков, введите число 0.
2. А в ячейке D2 введите эту формулу: =IF(A2=A1,D1,D1+1), а затем перетащите эту формулу вниз по ячейкам, к которым хотите её применить, см. скриншот:
Примечание: В приведенной выше формуле A1, A2 — это первая и вторая ячейка столбца, где меняются значения, D1 — это ячейка, в которую вы ввели вспомогательное число 0.
3. Затем выберите диапазон данных A2:D18, включая столбец с вспомогательной формулой, и нажмите Главная > Условное форматирование > Новое правило, см. скриншот:
4. В диалоговом окне Новое правило форматирования выберите Использовать формулу для определения форматируемых ячеек в разделе Выберите тип правила, и введите эту формулу =AND(LEN($A2)>0,MOD($D2,2)=0) в текстовое поле Форматировать значения, где эта формула истинна, см. скриншот:
Примечание: A2 — это первая ячейка вашего столбца, который вы окрашиваете на основе, а D2 — это первая ячейка созданного вами вспомогательного столбца в выбранном диапазоне.
5. Затем нажмите кнопку Формат, чтобы перейти в диалоговое окно Формат ячеек, и выберите понравившийся цвет на вкладке Заливка, см. скриншот:
6. Затем нажмите ОК > ОК, чтобы закрыть диалоговые окна, и строки будут выделены попеременно на основе конкретного столбца, где меняются значения, см. скриншот:
Чередование цвета строк на основе изменений значений с помощью полезной функции
Если предыдущий метод сложен для вас, вы можете использовать полезный инструмент — Kutools для Excel, с его функцией Различия, вы можете быстро окрасить строки на основе групп в Excel.
После установки Kutools для Excel выполните следующие действия:
1. Нажмите Kutools > Формат > Различия, см. скриншот:
2. В диалоговом окне Различия по ключевому столбцу выполните следующие операции по мере необходимости. См. скриншот:
3. После завершения настроек нажмите кнопку ОК, чтобы закрыть диалоговое окно, и вы получите следующий результат, который вам нужен:
Нажмите, чтобы скачать Kutools для Excel и попробовать бесплатно прямо сейчас!
Чередование цвета строк двумя цветами на основе изменений значений с помощью вспомогательного столбца и условного форматирования
Если вы хотите затенять строки двумя разными цветами попеременно на основе изменений значений, как показано на следующем скриншоте, это также можно решить в Excel с помощью условного форматирования.
1. Сначала вы должны создать новый вспомогательный столбец и формулу, как в первом методе от шага 1 до шага 2, вы получите следующий скриншот:
2. Затем выберите диапазон данных A2:D18, а затем нажмите Главная > Условное форматирование > Управление правилами, см. скриншот:
3. В диалоговом окне Диспетчер правил условного форматирования нажмите кнопку Новое правило, см. скриншот:
4. В открывшемся диалоговом окне Новое правило форматирования нажмите Использовать формулу для определения форматируемых ячеек в разделе Выберите тип правила, а затем введите эту формулу =ISODD($D2) (D2 — это первая ячейка созданного вами вспомогательного столбца с формулой), а затем нажмите кнопку Формат, чтобы выбрать цвет заливки, который вам нравится, для нечётных строк группы, см. скриншот:
5. Затем нажмите ОК, чтобы выйти из этого диалогового окна и вернуться к предыдущему диалоговому окну Диспетчер правил условного форматирования, пожалуйста, снова нажмите кнопку Новое правило, чтобы создать другое правило для чётных строк группы.
6. В диалоговом окне Новое правило форматирования нажмите Использовать формулу для определения форматируемых ячеек в разделе Выберите тип правила, как раньше, а затем введите эту формулу =ISEVEN($D2) (D2 — это первая ячейка созданного вами вспомогательного столбца с формулой), а затем нажмите кнопку Формат, чтобы выбрать другой цвет фона для чётных строк группы, см. скриншот:
7. Затем нажмите ОК, чтобы вернуться в Диспетчер правил условного форматирования, и вы увидите, что созданы два правила следующим образом:
8. Затем нажмите ОК, чтобы закрыть это диалоговое окно, и вы увидите, что выбранный диапазон данных был затенён двумя разными цветами попеременно на основе изменений значений в столбце.
- Примечания:
- 1. После чередования цвета строк вы можете скрыть вспомогательный столбец по мере необходимости, но вы не можете его удалить.
- 2. Если в вашем диапазоне данных нет заголовков, просто введите 1 как первое число во вспомогательном столбце, а затем примените вспомогательную формулу как обычно.
Больше статей:
- Увеличение чисел при изменении значения в другом столбце
- Предположим, у вас есть список значений в столбце A, и теперь вы хотите увеличивать число на 1 в столбце B, когда значение в столбце A меняется, то есть числа в столбце B увеличиваются, пока значение в столбце A не изменится, затем увеличение чисел начинается снова с 1, как показано на левом скриншоте. В Excel вы можете решить эту задачу следующим методом.
- Вставка пустых строк при изменении значения в Excel
- Предположим, у вас есть диапазон данных, и теперь вы хотите вставить пустые строки между данными при изменении значения, чтобы разделить последовательные одинаковые значения в одном столбце, как показано на следующих скриншотах. В этой статье я расскажу о некоторых хитростях, которые помогут вам решить эту проблему.
- Суммирование ячеек при изменении значения в другом столбце
- Когда вы работаете в Excel, иногда вам может понадобиться суммировать ячейки на основе группы данных в другом столбце. Например, здесь я хочу суммировать заказы в столбце B, когда данные меняются в столбце A, чтобы получить следующий результат. Как можно решить эту проблему в Excel?
- Вставка разрывов страниц при изменении значения в Excel
- Предположим, у меня есть диапазон ячеек, и теперь я хочу вставить разрывы страниц в рабочий лист, когда значения в столбце A меняются, как показано на левом скриншоте. Конечно, вы можете вставлять их по одному, но существуют ли быстрые способы вставить разрывы страниц сразу на основе изменённых значений одного столбца?
Лучшие инструменты для повышения продуктивности в Office
Повысьте свои навыки работы в Excel с помощью Kutools для Excel и ощутите эффективность на новом уровне. Kutools для Excel предлагает более300 расширенных функций для повышения производительности и экономии времени. Нажмите здесь, чтобы выбрать функцию, которая вам нужнее всего...
Office Tab добавляет вкладки в Office и делает вашу работу намного проще
- Включите режим вкладок для редактирования и чтения в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна вместо новых отдельных окон.
- Увеличьте свою продуктивность на50% и уменьшите количество щелчков мышью на сотни ежедневно!
Все надстройки Kutools. Один установщик
Пакет Kutools for Office включает надстройки для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro — идеально для команд, работающих в разных приложениях Office.
- Комплексный набор — надстройки для Excel, Word, Outlook и PowerPoint плюс Office Tab Pro
- Один установщик, одна лицензия — настройка занимает считанные минуты (MSI-совместимо)
- Совместная работа — максимальная эффективность между приложениями Office
- 30-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
- Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек