Координатное выделение

Координатное выделение

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

А если при движении активной ячейки по листу будет подсвечиваться текущая строка и столбец? Своего рода координатное выделение примерно такого вида:

Поудобнее, чем линейка, правда?

Есть несколько способов разной сложности, чтобы реализовать такое. Каждый способ — со своими плюсами и минусами. Давайте разберем их детально.

Способ 1. Очевидный. Макрос, выделяющий текущую строку и столбец

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

Откройте лист со таблицей, в которой хотите получить такое координатное выделение. Щелкните правой кнопкой мыши по ярлычку листа и выберите в контекстном меню команду Исходный текст (Source Code). Должно открыться окно редактора Visual Basic. Скопируйте в него этот текст этих трех макросов:

Измените адрес рабочего диапазона на свой — именно в пределах этого диапазона и будет работать наше выделение. Затем закройте редактор Visual Basic и вернитесь в Excel.

Нажмите сочетание клавиш ALT+F8, чтобы открыть окно со списком доступных макросов. Макрос Selection_On, как нетрудно догадаться, включает координатное выделение на текущем листе, а макрос Selection_Off — выключает его. В этом же окне, нажав кнопку Параметры (Options) можно назначить этим макросам сочетания клавиш для удобного запуска.

Плюсы этого способа:

  • относительная простота реализации
  • выделение — операция безобидная и никак не изменяет содержимое или форматирование ячеек листа, все остается как есть

Минусы этого способа:

  • такое выделение некорректно работает в том случае, если на листе есть объединенные ячейки — выделяются сразу все строки и столбцы, входящие в объединение
  • если случайно нажать клавишу Delete, то очистится не только активная ячейка, а вся выделенная область, т.е. удалятся данные из всей строки и столбца

Способ 2. Оригинальный. Функция ЯЧЕЙКА + Условное форматирование

Этот способ хотя и имеет пару недостатков, мне представляется весьма изящным. Реализовать что-либо, используя только встроенные средства Excel, минимально влезая в программирование на VBA — высший пилотаж 😉

Способ основан на использовании функции ЯЧЕЙКА (CELL), которая может выдавать массу различной информации по заданной ячейке — высоту, ширину, номер строки-столбца, числовой формат и т.д.. Эта функция имеет два аргумента:

  • кодовое слово для параметра, например «столбец» или «строка»
  • адрес ячейки, для которой мы хотим определить значение этого параметра

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

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

  1. Выделяем нашу таблицу, т.е. те ячейки, в которых в будущем должно отображаться координатное выделение.
  2. В Excel 2003 и более старших версиях открываем меню Формат — Условное форматирование — Формула (Format — Conditional Formatting — Formula) . В Excel 2007 и новее — жмем на вкладке Главная (Home) кнопку Условное форматирование — Создать правило (Conditional Formatting — Create Rule) и выбираем тип правила Использовать формулу для определения форматируемых ячеек (Use formula)
  3. Вводим формулу для нашего координатного выделения:

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

  • Нажмите кнопку Формат (Format) и задайте цвет заливки.
  • Все почти готово, но остался один нюанс. Дело в том, что Excel не считает изменение выделения изменением данных на листе. И, как следствие, не запускает пересчет формул и перекраску условного форматирования только при изменении положения активной ячейки. Поэтому добавим в модуль листа простой макрос, который будет это делать. Щелкните правой кнопкой мыши по ярлычку листа и выберите в контекстном меню команду Исходный текст (Source Code). Должно открыться окно редактора Visual Basic. Скопируйте в него этот текст этого простого макроса:

    Теперь при изменении выделения будет запускаться процесс пересчета формулы с функцией ЯЧЕЙКА в условном форматировании и заливаться текущая строка и столбец.

    Плюсы этого способа:

    • Условное форматирование не нарушает пользовательское форматирование таблицы
    • Этот вариант выделения корректно работает с объединенными ячейками.
    • Нет риска удалить целую строку и столбец с данными при случайном нажатии Delete.
    • Макросы используются минимально

    Минусы этого способа:

    • Формулу для условного форматирования надо вводить вручную.
    • Нет быстрого способа включить-выключить такое форматирование — оно включено всегда, пока не будет удалено правило.

    Способ 3. Оптимальный. Условное форматирование + макросы

    Золотая середина. Используем механизм отслеживания выделения на листе при помощи макросов из способа-1 и добавим к нему безопасное выделение цветом с помощью условного форматирования из способа-2.

    Откройте лист со таблицей, в которой хотите получить такое координатное выделение. Щелкните правой кнопкой мыши по ярлычку листа и выберите в контекстном меню команду Исходный текст (Source Code). Должно открыться окно редактора Visual Basic. Скопируйте в него этот текст этих трех макросов:

    Не забудьте изменить адрес рабочего диапазона на адрес своей таблицы. Закройте редактор Visual Basic и вернитесь в Excel. Чтобы использовать добавленные макросы, нажмите сочетание клавиш ALT+F8 и действуйте аналогично способу 1.

    Способ 4. Красивый. Надстройка FollowCellPointer

    Excel MVP Jan Karel Pieterse родом из Нидерландов раздает у себя на сайте бесплатную надстройку FollowCellPointer(36Кб), которая решает ту же задачу, отрисовывая с помощью макросов графические линии-стрелки для подсветки текущей строки и столбца:

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

    • в Excel 2003 и старше — через меню Сервис — Надстройки — Обзор (Tools — Add-Ins — Browse)
    • в Excel 2007 и новее — через Файл — Параметры — Надстройки — Перейти — Обзор (File — Excel Options — Add-Ins — Go to — Browse)

    Источник:
    http://www.planetaexcel.ru/techniques/9/58/

    Как выделить цветом повторяющиеся значения в Excel?

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

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

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

    Далее в этом руководстве вы найдете несколько способов выделения дубликатов записей в зависимости от конкретной задачи. Эти методы работают во всех версиях Excel 2016, Excel 2013, Excel 2010 и ниже.

    Как подсветить дубликаты, используя встроенный инструмент (с 1- м появлением)

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

    1. Выберите данные, которые вы хотите проверить. Это может быть колонка, строка или диапазон ячеек.
    2. На вкладке « Главная » выберите « Условное форматирование» > «Правила выделения ячеек» > «Повторяющиеся значения».

    Откроется диалоговое окно с форматом «Светло-красная заливка» и «Темно-красный текст», выбранными по умолчанию. Чтобы применить формат по умолчанию, просто нажмите ОК .

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

    И вот что мы получили в итоге:

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

    При использовании этого инструмента имейте в виду следующие две вещи:

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

    Как выделить повторы без 1- го вхождения

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

    1. Выберите «Условное форматирование» > « Новое правило»> «Использовать формулу», чтобы определить ячейки для форматирования .
    2. В поле « Форматировать значения», где эта формула возвращает ИСТИНА , введите:

    =СЧЕТЕСЛИ($B$2:$B2;$B2)>1

    Где B2 — самая верхняя ячейка выбранного диапазона.

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

    Если у вас нет большого опыта работы с условным форматированием Excel, вы найдете подробные инструкции по созданию правила на основе формул в следующем руководстве: Как изменить цвет ячейки в зависимости от значения?

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

    Как показать третий, четвертый и все последующие дубликаты?

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

    Чтобы раскрасить третий и все последующие повторы, примените это:

    =СЧЕТЕСЛИ($B$2:$B2;$B2)>=3

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

    =СЧЕТЕСЛИ($B$2:$B2;$B2)>=4

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

    Например, чтобы изменить фон только 2-х экземпляров, вы должны использовать следующую формулу:

    =СЧЕТЕСЛИ($B$2:$B2;$B2)=2

    Как выделить дубликаты в диапазоне

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

    Выделите дубликаты в таблице, включая 1-е вхождение.

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

    Или создайте своё условное форматирование:

    =СЧЕТЕСЛИ(диапазон, первая_ячейка)> 1

    Например, чтобы отметить цветом совпадения в диапазоне A2: C8, сделайте это следующим образом:

    =СЧЕТЕСЛИ($A$2:$C$8, A2)>1

    Обратите внимание на использование абсолютных ссылок на ячейки для диапазона ($A$2:$C$8) и относительных ссылок для верхней ячейки (A2).

    Выделите повторы в таблице без 1-го вхождения.

    Решение для этого сценария намного сложнее. Не удивительно, что в Excel нет для него готового решения 🙂

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

    Правило 1. Относится к первому столбцу.

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

    В этом примере мы применяем для A2: A11 условие:

    =СЧЁТЕСЛИ($A$2:$A2;A2)>1

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

    Правило 2. Относится ко всем последующим колонкам

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

    =СЧЁТЕСЛИ(A$2:$A$11;B2)+СЧЁТЕСЛИ(B$2:B2;B2)>1

    В приведенной выше формуле первая функция СЧЕТЕСЛИ подсчитывает вхождения данного элемента в первом столбце, а второй СЧЕТЕСЛИ делает то же самое для всех последующих. Затем вы складываете эти числа и проверяете, больше ли сумма, чем 1.

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

    Выделение целых строк на основе повторяющихся значений в одной колонке.

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

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

    • Чтобы выделить дублирующиеся строки, исключая первые вхождения:

    =СЧЁТЕСЛИ($B$2:$B2;$B2)>1

    Выделяем строки с одинаковыми наименованиями товаров (столбец B)

    • Чтобы выделить повторяющиеся строки, включая все вхождения:

    = СЧЁТЕСЛИ ($B$2:$B$17;$B2)>1

    Здесь B2 — первая ячейка, а B17 — последняя, которую вы хотите проверить на наличие дублей.

    Как видите, разумное использование абсолютных и относительных ссылок имеет значение.

    Как выделить дублирующиеся строки в Excel

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

    Для этого используйте функцию СЧЁТЕСЛИМН, которая позволяет сравнивать по нескольким критериям. Например, чтобы выделить строки с одинаковыми значениями в B и C, то есть найти заказы одного и того же товара одним заказчиком, используйте одну из следующих формул:

    • Чтобы выделить совпадающие, кроме 1-го вхождения :

    =COUNTIFS($A$2:$A2, $A2, $B$2:$B2, $B2)>1

    Обратите внимание, что форматирование мы применяем ко всей таблице.

    • Чтобы выделить все неуникальные:

    =СЧЁТЕСЛИМН($B$2:$B$17; $B2; $C$2:$C$17; $C2)>1

    Как вы понимаете, приведенный выше пример только для демонстрационных целей. При выделении дублирующихся строк в ваших реальных таблицах вы, естественно, не ограничены сравнением значений только в 2 столбцах. Функция СЧЁТЕСЛИМН может обрабатывать до 127 пар диапазон / критерий.

    Подсветка последовательных дубликатов ячеек в Excel

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

    Для этого выберите область с данными (не включая заголовок) и создайте условное форматирование с одной из следующих формул:

    • Чтобы выделить два идущих друг за другом заказа одного и того же менеджера:

    =ИЛИ($G1=$G2;$G2=$G3)

    • Чтобы выделить второй заказ менеджера, если у него 2 заказа идут подряд:
      =$G1=$G2

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

    • при выделении только второго повтора

    =И($G2<>«»; $G1=$G2)

    • при выделении обоих дублей:

    =И($G2<>«»; ИЛИ($G1=$G2;$G2=$G3))

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

    Источник:
    http://mister-office.ru/excel/how-to-highlight-duplicates.html

    Как в Excel изменять цвет строки в зависимости от значения в ячейке

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

    В одной из предыдущих статей мы обсуждали, как изменять цвет ячейки в зависимости от её значения. На этот раз мы расскажем о том, как в Excel 2010 и 2013 выделять цветом строку целиком в зависимости от значения одной ячейки, а также раскроем несколько хитростей и покажем примеры формул для работы с числовыми и текстовыми значениями.

    Как изменить цвет строки на основании числового значения одной из ячеек

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

    Мы хотим раскрасить различными цветами строки в зависимости от заказанного количества товара (значение в столбце Qty.), чтобы выделить самые важные заказы. Справиться с этой задачей нам поможет инструмент Excel – «Условное форматирование».

    1. Первым делом, выделим все ячейки, цвет заливки которых мы хотим изменить.
    2. Чтобы создать новое правило форматирования, нажимаем Главная >Условное форматирование >Создать правило (Home > Conditional Formatting > New rule).
    3. В появившемся диалоговом окне Создание правила форматирования (New Formatting Rule) выбираем вариант Использовать формулу для определения форматируемых ячеек (Use a formula to determine which cells to format), и ниже, в поле Форматировать значения, для которых следующая формула является истинной (Format values where this formula is true), вводим такое выражение:

    Вместо C2 Вы можете ввести ссылку на другую ячейку Вашей таблицы, значение которой нужно использовать для проверки условия, а вместо 4 можете указать любое нужное число. Разумеется, в зависимости от поставленной задачи, Вы можете использовать операторы сравнения меньше ( =$C2
    =$C2=4

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

  • Нажимаем кнопку Формат (Format) и переходим на вкладку Заливка (Fill), чтобы выбрать цвет фона ячеек. Если стандартных цветов недостаточно, нажмите кнопку Другие цвета (More Colors), выберите подходящий и дважды нажмите ОК.Таким же образом на остальных вкладках диалогового окна Формат ячеек (Format Cells) настраиваются другие параметры форматирования, такие как цвет шрифта или границы ячеек.
  • В поле Образец (Preview) показан результат выполнения созданного правила условного форматирования:
  • Если всё получилось так, как было задумано, и выбранный цвет устраивает, то жмём ОК, чтобы увидеть созданное правило в действии.Теперь, если значение в столбце Qty. больше 4, то соответствующая строка таблицы целиком станет голубой.
  • Как видите, изменять в Excel цвет целой строки на основании числового значения одной из ячеек – это совсем не сложно. Далее мы рассмотрим ещё несколько примеров формул и парочку хитростей для решения более сложных задач.

    Как создать несколько правил условного форматирования с заданным приоритетом

    В таблице из предыдущего примера, вероятно, было бы удобнее использовать разные цвета заливки, чтобы выделить строки, содержащие в столбце Qty. различные значения. К примеру, создать ещё одно правило условного форматирования для строк, содержащих значение 10 или больше, и выделить их розовым цветом. Для этого нам понадобится формула:

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

    1. На вкладке Главная (Home) в разделе Стили (Styles) нажмите Условное форматирование (Conditional Formatting) >Управление правилами (Manage Rules)
    2. В выпадающем списке Показать правила форматирования для (Show formatting rules for) выберите Этот лист (This worksheet). Если нужно изменить параметры только для правил на выделенном фрагменте, выберите вариант Текущий фрагмент (Current Selection).
    3. Выберите правило форматирования, которое должно быть применено первым, и при помощи стрелок переместите его вверх списка. Должно получиться вот так:Нажмите ОК, и строки в указанном фрагменте тут же изменят цвет, в соответствии с формулами в обоих правилах.

    Как изменить цвет строки на основании текстового значения одной из ячеек

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

    • Если срок доставки заказа находится в будущем (значение Due in X Days), то заливка таких ячеек должна быть оранжевой;
    • Если заказ доставлен (значение Delivered), то заливка таких ячеек должна быть зелёной;
    • Если срок доставки заказа находится в прошлом (значение Past Due), то заливка таких ячеек должна быть красной.

    И, конечно же, цвет заливки ячеек должен изменяться, если изменяется статус заказа.

    С формулой для значений Delivered и Past Due всё понятно, она будет аналогичной формуле из нашего первого примера:

    =$E2=»Delivered»
    =$E2=»Past Due»

    Сложнее звучит задача для заказов, которые должны быть доставлены через Х дней (значение Due in X Days). Мы видим, что срок доставки для различных заказов составляет 1, 3, 5 или более дней, а это значит, что приведённая выше формула здесь не применима, так как она нацелена на точное значение.

    В данном случае удобно использовать функцию ПОИСК (SEARCH) и для нахождения частичного совпадения записать вот такую формулу:

    =ПОИСК(«Due in»;$E2)>0
    =SEARCH(«Due in»,$E2)>0

    В данной формуле E2 – это адрес ячейки, на основании значения которой мы применим правило условного форматирования; знак доллара $ нужен для того, чтобы применить формулу к целой строке; условие “>0” означает, что правило форматирования будет применено, если заданный текст (в нашем случае это “Due in”) будет найден.

    Подсказка: Если в формуле используется условие “>0“, то строка будет выделена цветом в каждом случае, когда в ключевой ячейке будет найден заданный текст, вне зависимости от того, где именно в ячейке он находится. В примере таблицы на рисунке ниже столбец Delivery (столбец F) может содержать текст “Urgent, Due in 6 Hours” (что в переводе означает – Срочно, доставить в течение 6 часов), и эта строка также будет окрашена.

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

    =ПОИСК(«Due in»;$E2)=1
    =SEARCH(«Due in»,$E2)=1

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

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

    Как изменить цвет ячейки на основании значения другой ячейки

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

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

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

    Если нужно выделить строки одним и тем же цветом при появлении одного из нескольких различных значений, то вместо создания нескольких правил форматирования можно использовать функции И (AND), ИЛИ (OR) и объединить таким образом нескольких условий в одном правиле.

    Например, мы можем отметить заказы, ожидаемые в течение 1 и 3 дней, розовым цветом, а те, которые будут выполнены в течение 5 и 7 дней, жёлтым цветом. Формулы будут выглядеть так:

    =ИЛИ($F2=»Due in 1 Days»;$F2=»Due in 3 Days»)
    =OR($F2=»Due in 1 Days»,$F2=»Due in 3 Days»)

    =ИЛИ($F2=»Due in 5 Days»;$F2=»Due in 7 Days»)
    =OR($F2=»Due in 5 Days»,$F2=»Due in 7 Days»)

    Для того, чтобы выделить заказы с количеством товара не менее 5, но не более 10 (значение в столбце Qty.), запишем формулу с функцией И (AND):

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

    =ИЛИ($F2=»Due in 1 Days»;$F2=»Due in 3 Days»;$F2=»Due in 5 Days»)
    =OR($F2=»Due in 1 Days»,$F2=»Due in 3 Days»,$F2=»Due in 5 Days»)

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

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

    Источник:
    http://office-guru.ru/excel/kak-v-excel-izmenjat-cvet-stroki-v-zavisimosti-ot-znachenija-v-jacheike-443.html

    Координатное выделение ячейки в Excel

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

    Приветствую всех, уважаемые читатели блога TutorExcel.Ru.

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

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


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

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

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

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

    Способ 1. Макрос

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

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

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

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

    Возвращаемся в Excel. Для начала работы координатного пересечения необходимо включить опцию отображения, для этого открываем окно с макросами (сочетание клавиш Alt + F8) и запускаем макрос Coordinate_Selection_On (для отключения опции запускаем Coordinate_Selection_Off).

    Все готово (не забудьте сначала запустить макрос Coordinate_Selection_On):


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

    Теперь перейдем к альтернативной реализации.

    Способ 2. Условное форматирование

    Следующий способ базируется на 2 основных принципах: условном форматировании (которое будет подсвечивать все ячейки в строке и столбце) и свойствах функции ЯЧЕЙКА (которая позволит нам составить правило для форматирования).

    Пойдем по порядку.
    Выделим диапазон таблицы (в нашем примере это A1:Z35), для которого будем делать перекрестное выделение.
    Далее в панели вкладок выбираем Главная -> Условное форматирование и нажимаем Создать правило:


    В появившемся окне выбираем Использовать формулу для определения форматируемых ячеек и в описании правила вставляем следующую формулу:

    Затем нажимаем кнопку Формат и настраиваем внешний вид выделяемых данных (цвет заливки, шрифта) по своему усмотрению.

    Что нам дает функция ЯЧЕЙКА?

    Мы уже сталкивались с ней, когда, например, получали имя листа в виде формулы.
    Данная функция возвращает различные свойства ссылки в зависимости от параметров введенных аргументов. Поэтому формулы ЯЧЕЙКА(«строка») и ЯЧЕЙКА(«столбец») вернут нам, соответственно, номер строки и столбца текущей ячейки.
    Следовательно, введенная формула выделит все ячейки таблицы, где строка (или столбец) совпадают со строкой (или столбцом) текущей ячейки, в результате после форматирования и получится перекрестное выделение.

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

    Источник:
    http://tutorexcel.ru/priemy/koordinatnoe-vydelenie-yachejki-v-excel/

    Выделение строк таблицы в EXCEL в зависимости от условия в ячейке

    10 октября 2013 г.

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

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

    Задача1 — текстовые значения

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

    Создадим небольшую табличку со статусами работ в диапазоне Е6:Е9 .

    Выделим диапазон ячеек А7:С17 , содержащий перечень работ, и установим через меню Главная/ Цвет заливки фон заливки красный (предполагаем, что все работы изначально находятся в статусе Не начата ).

    Убедимся, что выделен диапазон ячеек А7:С17 ( А7 должна быть активной ячейкой ). Вызовем команду меню Условное форматирование/ Создать правило / Использовать формулу для определения форматируемых ячеек .

    • в поле « Форматировать значения, для которых следующая формула является истинной » нужно ввести =$C7=$E$8 (в ячейке Е8 находится значение В работе ). Обратите внимание на использоване смешанных ссылок ;
    • нажать кнопку Формат ;
    • выбрать вкладку Заливка ;
    • выбрать серый цвет ;
    • Нажать ОК.

    ВНИМАНИЕ : Еще раз обращаю внимание на формулу =$C7=$E$8 . Обычно пользователи вводят =$C$7=$E$8 , т.е. вводят лишний символ доллара.

    Нужно проделать аналогичные действия для выделения работ в статусе Завершена . Формула в этом случае будет выглядеть как =$C7=$E$9 , а цвет заливки установите зеленый.

    В итоге наша таблица примет следующий вид.

    Примечание : Условное форматирование перекрывает обычный формат ячеек. Поэтому, если работа в статусе Завершена, то она будет выкрашена в зеленый цвет, не смотря на то, что ранее мы установили красный фон через меню Главная/ Цвет заливки .

    Как это работает?

    В файле примера для пояснения работы механизма выделения строк, создана дополнительная таблица с формулой =$C7=$E$9 из правила Условного форматирования для зеленого цвета. Формула введена в верхнюю левую ячейку и скопирована вниз и вправо.

    Как видно из рисунка, в строках таблицы, которые выделены зеленым цветом, формула возвращает значение ИСТИНА.

    В формуле использована относительная ссылка на строку ($C7, перед номером строки нет знака $). Отсутствие знака $ перед номером строки приводит к тому, что при копировании формулы вниз на 1 строку она изменяется на =$C8=$E$9 , затем на =$C9=$E$9 , потом на =$C10=$E$9 и т.д. до конца таблицы (см. ячейки G8 , G9 , G10 и т.д.). При копировании формулы вправо или влево по столбцам, изменения формулы не происходит, именно поэтому цветом выделяется вся строка.

    В случае затруднений можно потренироваться на примерах, приведенных в статье Условное форматирование в MS EXCEL .

    Прием с дополнительной таблицей можно применять для тестирования любых формул Условного форматирования .

    Рекомендации

    При вводе статуса работ важно не допустить опечатку. Если вместо слово Завершен а , например, пользователь введет Завершен о , то Условное форматирование не сработает.

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

    Чтобы быстро расширить правила Условного форматирования на новую строку в таблице, выделите ячейки новой строки ( А17:С17 ) и нажмите сочетание клавиш CTRL+D . Правила Условного форматирования будут скопированы в строку 17 таблицы.

    Задача2 — Даты

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

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

    Сначала создадим формулу для условного форматирования в столбцах В и E. Если формула вернет значение ИСТИНА, то соответствующая строка будет выделена, если ЛОЖЬ, то нет.

    В столбце D создана формула массива = МАКС(($A7=$A$7:$A$16)*$B$7:$B$16)=$B7 , которая определяет максимальную дату для определенного сотрудника.

    Примечание: Если нужно определить максимальную дату вне зависимости от сотрудника, то формула значительно упростится = $B7=МАКС($B$7:$B$16) и формула массива не понадобится.

    Теперь выделим все ячейки таблицы без заголовка и создадим правило Условного форматирования . Скопируем формулу в правило (ее не нужно вводить как формулу массива!).

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

    Для этого используйте формулу =И($B23>$E$22;$B23 $E$22;$B27 $E$22;$B31

    Источник:
    http://excel2.ru/articles/vydelenie-strok-tablicy-v-ms-excel-v-zavisimosti-ot-usloviya-v-yacheyke

    Как в Excel выделить цветом ячейку, столбец, строку?

    Как в программе Эксель (Excel) выделять цветом части таблицы (ячейки, столбцы и строки)?

    Какие существуют способы выделения цветом в Эксель (Excel)?

    Удобно выделять ячейки цветом статически, меняя цвет заливки на вкладке главная

    или выбирая вкладку Цвет заливки в строке контекстного меню Формат ячеек.

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

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

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

    Можно выбрать много типов правил:

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

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

    Нет подходящего цвета? Нажимаем на команду Другие цвета. Выбираем необходимый цвет.

    В области ячейки щелкам правой кнопкой мыши. В открывшемся меню выбираем Формат ячеек.

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

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

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

    Самый простой вариант — это воспользоваться пунктом «Цвет заливки», расположенном на панели инструментов Excel.

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

    Для заливки строки / столбца нужно щёлкнуть левой кнопкой мыши на соответствующей цифре / букве:

    После этого указать подходящий цвет.

    Другой вариант — нужно щёлкнуть правой кнопкой мыши на ячейке / строке / столбце и вызвать окно «Формат ячеек» из контекстного меню.

    Далее открываем вкладку «Заливка».

    Здесь можно также сделать градиентную заливку для ячеек и различные узоры.

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

    Рассмотрим такой пример:

    В таблице указано выполнение плана в процентах за первое полугодие (с января по июнь).

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

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

    Для решения этой задачи воспользуемся условным форматированием.

    Выделяем диапазон ячеек D2:D7 и на панели инструментов выбираем «Условное форматирование» -> «Создать правило».

    В качестве типа правила указываем «Форматировать только ячейки, которые содержат».

    В качестве условия указываем «Значение ячейки больше или равно 1».

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

    Тип правила остаётся тем же, а в качестве условия указываем «Значение ячейки меньше 1».

    В окне «Формат» ячеек выбираем красный фон заливки.

    В результате получим следующее:

    Выделение цветом ячеек в таблице Эксель (Excel) можно делать с помощью мышки. Это будет самый простой способ.

    Нажимаете мышкой на ячейку, которую нужно раскрасить. Затем нажимаете мышкой на инструмент «Заливка» (перевернутое ведро) на вкладке «Главная» панелей инструментов вверху. Это «ведерко» находится в зоне «Шрифт». Выбирайте цвет и кликайте на него мышкой. После этого ваша ячейка окрасится в выбранный цвет.

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

    Строка в Excel окрашивается точно также, как и ячейка. Чтобы выделить всю строку (или столбец), нужно мышку поместить в поле номера строки (это слева от начала строки). Для столбца — в поле выше столбца. Вы увидите появление черной стрелочки. Теперь щелкайте мышкой, и выделится вся строка (столбец). После того, как они выделятся, раскрасьте их с помощью ведерка «Заливка». Цвет выберите по своему усмотрению.

    Более сложный способ — раскраска с помощью контекстного меню «Свойства». Выделите ряд ячеек, которые вы хотите раскрасить, после чего нажмите на них правой кнопкой мыши — появится контекстное меню. В нем выберите команду «Формат ячеек. » — откроется диалоговое окно «Формат ячеек». В нем выберите вкладку «Заливка», а в появившемся окне выберите цвет, который вам нужен.

    Вот в принципе и все для Excel.

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

    Источник:
    http://www.bolshoyvopros.ru/questions/2874190-kak-v-excel-vydelit-cvetom-jachejku-stolbec-stroku.html