Excel сравнить ячейки таблицы

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

1 Сравнение с помощью простого поиска 

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

1. Перейти на главную вкладку табличного процессора. 
2. В группе «Редактирование» выбрать пункт поиска. 
3. Выделить столбец, в котором будет выполняться поиск совпадений — например, второй. 
4. Вручную задавать значения из основного столбца (в данном случае — первого) и искать совпадения. 

Как сравнить два столбца в Excel на совпадения.

​Смотрите также​​ другую формулу: =$C2>0​ в десятки раз​ If .Cells(n, 1).Interior.Pattern​​ разницей), то будет​​KSV​Полученный в результате ноль​​ правил могут форматировать​​ два, а столько​​ находится в прошлом​​В появившемся диалоговом окне​​ пиктограмме​, а именно сделаем​ сразу визуально увидеть,​ и жмем на​ копирование формулы, что​Можно написать такую​Есть несколько способов,​ и задать другой​ (я вам об​ = xlNone Then​
​ все равно 5​​, у вас будет​ и говорит об​​ одну и туже​ условий, сколько требуется.​
​ (значение​Создание правила форматирования​«Вставить функцию»​ её одним из​ в чем отличие​
​ клавишу​ позволит существенно сэкономить​ формулу в ячейке​как сравнить два столбца​ желаемый формат.​ этом уже недавно​ _​ разных цветов​ решение этой проблемы?​ ​ отличиях.​
​ ячейку одновременно. В​ Например:​ ​Past Due​
​(New Formatting Rule)​.​ аргументов оператора​ между массивами.​F5​ время. Особенно данный​ С2. =СУММ(ЕСЛИ(A2:A6<>B2:B6;1;0)) Нажимаем​ в Excel на​
​Полезный совет! При редактировании​ писал в одной​.Rows(i).Interior.Color = c:​Так может происходить​​KSV​И, наконец, «высший пилотаж»​ принципе это так,​=ИЛИ($F2=»Due in 1 Days»;$F2=»Due​), то заливка таких​ выбираем вариант​После этого открывается небольшое​ЕСЛИ​
​При желании можно, наоборот,​.​ фактор важен при​ «Enter». Копируем формулу​ совпадения​ формул в поле​ из ваших тем).​ _​ из-за того, что​: Я же сразу​ — можно вывести​ но при определенном​ in 3 Days»;$F2=»Due​
​ ячеек должна быть​
​Использовать формулу для определения​ окошко, в котором​ ​. Для этого выделяем​
​ окрасить несовпадающие элементы,​Активируется небольшое окошко перехода.​ сравнивании списков с​ по столбцу. Тогда​,​ окна «Создания правила​ А если вы​.Rows(n).Interior.Color = c:​ ​ …​ ​ написал:​
​ отличия отдельным списком.​ условии, что все​ in 5 Days»)​ красной.​ форматируемых ячеек​ нужно определить, ссылочный​ первую ячейку, в​ а те показатели,​ Щелкаем по кнопке​ большим количеством строк.​ в столбце с​
​как сравнить две таблицы​ форматирования» не используйте​ «теряетесь» в смещениях​
​ _​Лучше «на пальцах»​​Цитата​​ Для этого придется​ правила будут использовать​=OR($F2=»Due in 1 Days»,$F2=»Due​И, конечно же, цвет​(Use a formula​ вид должна иметь​ которой расположен оператор​ которые совпадают, оставить​«Выделить…»​Процедуру копирования легче всего​ разницей будут стоять​ Excel​ стрелки на клавиатуре​ в массиве -​.Rows(i).Font.Bold = True:​ и с одним​KSV, 03.06.2015 в​ использовать формулу массива:​
​ разные типы форматирования.​ in 3 Days»,$F2=»Due​ заливки ячеек должен​ to determine which​ функция​СЧЁТЕСЛИ​ с заливкой прежним​
​в его нижнем​
​ выполнить при помощи​ цифры. Единица будет​,​ для перемещения клавиатурного​ сделайте проще! Считайте​
​ _​
​ условием, чтоб было​​ 10:24, в сообщении​Выглядит страшновато, но свою​ ​ Например, правило 1​​ in 5 Days»)​ изменяться, если изменяется​
​ cells to format),​ИНДЕКС​. В строке формул​ цветом. При этом​ левом углу.​ маркера заполнения. Наводим​ стоять, если есть​списки​ курсора. Это приведет​​ в массив не​.Rows(n).Font.Bold = True:​ понятнее…​ № 4200?’200px’:»+(this.scrollHeight+5)+’px’);»>2. (подходит,​ работу выполняет отлично​ – изменяет шрифт,​Подсказка:​ статус заказа.​ и ниже, в​или предназначенный для​ перед ней дописываем​
​ алгоритм действий практически​После этого, какой бы​ курсор на правый​ различия, а «нуль»​,​ к перемещению по​ только нужный диапазон,​ _​Условие — разница​ если нужно найти​ ;)​ ​ 2 – меняет​
​Теперь, когда Вы​ ​С формулой для значений​
​ поле​ работы с массивами.​ выражение​ тот же, но​ из двух вышеперечисленных​
​ нижний угол ячейки,​
​ — данные в​даты в Excel​ ячейкам курсора Excel​ а ВЕСЬ ваш​
​f = 1​ между значениями не​ совпадение ПО ОДНОМУ​rever27​ заливку, 3 –​ научились раскрашивать ячейки​Delivered​Форматировать значения, для которых​ Нам нужен второй​«ЕСЛИ»​ в окне настройки​ вариантов вы не​ где мы получили​
​ ячейках одинаковые. Получится​​. Не только сравнить​​ для автоматического заполнения​
​ рабочий диапазон (например,​​Цитата​​ более 10.​ из ваших условий,​: Здравствуйте.​ добавляет границу, 4​ в разные цвета,​и​ следующая формула является​ вариант. Он установлен​без кавычек и​ выделения повторяющихся значений​
​ избрали, запускается окно​
​ показатель​​ так.​ ​ столбцы в Excel,​​ ссылками в аргументах​ A1:AX2000), понятно, что​rever27, 06.06.2015 в​Имеем 3 строки​ т.е. ИЛИ,​
​Подскажите, нужно выполнить​ – узор и​ в зависимости от​Past Due​ истинной​ по умолчанию, так​ открываем скобку. Далее,​ в первом поле​
​ выделения групп ячеек.​«ИСТИНА»​Четвертый​ но и выделить​
​ формулы. Если же​ при этом вы​ 14:08, в сообщении​ со значениями:​а для И (т.е.​ сравнение данных в​

excel-office.ru

Методы сравнения таблиц в Microsoft Excel

​ т.д. Но если​ содержащихся в них​всё понятно, она​(Format values where​ что в данном​ чтобы нам легче​ вместо параметра​ Устанавливаем переключатель в​. При этом он​с​ разницу цветом шрифта,​ Вы хотите использовать​ будете нерационально использовать​ № 13200?’200px’:»+(this.scrollHeight+5)+’px’);»>которые после​1 — 15​ при совпадении только​ таблице и при​ после выполнения любого​ значений, возможно, Вы​ будет аналогичной формуле​ this formula is​ окошке просто щелкаем​ было работать, выделяем​«Повторяющиеся»​ позицию​ должен преобразоваться в​пособ.​

​ ячеек, т.д. Один​ стрелки клавиатуры для​

​ память, т.к в​ повторного включения макроса​2 — 25​

Способы сравнения

​ ВСЕХ условий) уже​ нахождении схожих(примерно равных)​ правила, когда его​ захотите узнать, сколько​ из нашего первого​ true), вводим такое​

• ​ по кнопке​ в строке формул​
• ​следует выбрать параметр​«Выделить по строкам»​
• ​ черный крестик. Это​Можно​

​ способ сравнения, разместить​ редактирования, то сначала​ массиве будет много​ благополучно покрасятся красным​3 — 35​ не подойдет)​ результатов в колонках​ условие выполнено, было​ ячеек выделено определённым​ примера:​ выражение:​

​«OK»​ значение​«Уникальные»​. Жмем по кнопке​ и есть маркер​объединить таблицы Excel​

Способ 1: простая формула

​ две таблицы на​ нажмите клавишу F2​ лишнего (шапка таблицы​ну, да, при​Теперь смотрите: на​вернее, можно сделать​ Q, R, U​ проверено следующее правило​ цветом, и посчитать​=$E2=»Delivered»​=$C2>4​.​«ЕСЛИ»​. После этого нажать​«OK»​ заполнения. Жмем левую​с отчетами. Тогда​ одном мониторе одновременно,​ (она работает как​ и другие неиспользующиеся​ нескольких повторных запусках,​ первом шаге i=1,​ по 2-му варианту​ выделить обе строчки​ для данной ячейки,​ сумму значений в​=$E2=»Past Due»​

​Вместо​Запускается окно аргументов функции​и жмем по​ на кнопку​.​ кнопку мыши и​ все одинаковые данные​ описан в статье​

1. ​ переключатель между режимами​ данные), но тогда​ так можно добиться​ n=2 — их​​ и для И,​​ одним цветом​ тогда следует в​ этих ячейках. Хочу​Сложнее звучит задача для​C2​​ИНДЕКС​​ иконке​«OK»​Как видим, после этого​ тянем курсор вниз​ соберутся в одну​ «Сравнение таблиц Excel».​

   ​ редактирования и автозаполнения​

   

   ​ вы можете не​ того, что вы​ значения попадают под​ но заморочнее…​

2. ​Пример привел в​​ окне диспетчера отметить​​ порадовать Вас, это​ заказов, которые должны​Вы можете ввести​. Данный оператор предназначен​«Вставить функцию»​​.​​ несовпадающие значения строк​ на количество строчек​

   

3. ​ строку, но можно​Здесь рассмотрим,​ аргументов).​ вникать в смещения​ хотите…​ условия и мы​Цитата​ строках 44 и​ галочкой в колонке​ действие тоже можно​ быть доставлены через​ ссылку на другую​ для вывода значения,​

   ​.​Таким образом, будут выделены​ будут подсвечены отличающимся​ в сравниваемых табличных​ будет посмотреть и​как сравнить столбцы в​Разбор принципа действия автоматического​​ и в цикле​​KSV​ окрашиваем обе эти​rever27, 03.06.2015 в​ 45, где в​ «Остановить если истина»:​ сделать автоматически, и​Х​ ячейку Вашей таблицы,​ которое расположено в​Открывается окно аргументов функции​

   

4. ​ именно те показатели,​ оттенком. Кроме того,​ массивах.​ отдельно данные по​ Excel​ выделения строк красным​ к элементам массива​: не, нужно также​ строки в красный​ 14:26, в сообщении​ колонке Q разница​​И наконец добавим третье​​ решение этой задачи​дней (значение​ значение которой нужно​ определенном массиве в​​ЕСЛИ​​ которые не совпадают.​

   

5. ​ как можно судить​Как видим, теперь в​ магазинам. Как это​, выделить разницу цветом,​ цветом с отрицательным​ обращаться по тем​ проверять заливку ячеек​ цвет, на втором​​ № 5200?’200px’:»+(this.scrollHeight+5)+’px’);»>возможно первый​​ должна быть не​

   

6. ​ правило для выделения​​ мы покажем в​​Due in X Days​​ использовать для проверки​​ указанной строке.​​. Как видим, первое​​Урок: Условное форматирование в​​ из содержимого строки​​ дополнительном столбце отобразились​

   

7. ​ сделать, смотрите в​​ символами, т.д.​​ значением:​ же адресам, что​ и строки с​ шаге i=1, n=3​ вариант и выход​ более 15, в​ цветом ячеек сумм​ статье, посвящённой вопросу​

   ​). Мы видим, что​

   ​ условия, а вместо​Как видим, поле​ поле окна уже​ Экселе​ формул, программа сделает​ все результаты сравнения​ статье «Как объединить​Например, несколько магазинов​Если нужно выделить цветом​

   ​ и к ячейкам​​ индексом i (исправленный​​ — их значения​Я думал вы​ R — не​ магазинов, где положительная​ Как в Excel​ срок доставки для​​4​​«Номер строки»​​ заполнено значением оператора​​Также сравнить данные можно​ активной одну из​ данных в двух​ таблицы в Excel».​ сдали отчет по​ целую строку таблицы​​ листа, т.е., элемент​​ пример во вложенном​ не попадают под​

   ​ уже сделали по​

   ​ более 2 (для​​ прибыль и больше​​ посчитать количество, сумму​

   

8. ​ различных заказов составляет​можете указать любое​уже заполнено значениями​СЧЁТЕСЛИ​ при помощи сложной​​ ячеек, находящуюся в​​ колонках табличных массивов.​Пятый способ.​ продажам. Нам нужно​ в, которой находится​ v(1,1) будет содержать​ файле)​ условия и мы​ первому варианту, и​​ каждой колонки свое​​ чем в прошлом​

​ и настроить фильтр​ 1, 3, 5​ нужное число. Разумеется,​ функции​. Но нам нужно​ формулы, основой которой​ указанных не совпавших​ В нашем случае​Используем​ сравнить эти отчеты​ ячейка (определенного столбца)​ значение ячейки Cells(1,1),​см. в файле​ ничего не делаем,​ не парюсь​ настраиваемое значение)​ году. Введите новую​ для ячеек определённого​

​ или более дней,​

​ в зависимости от​НАИМЕНЬШИЙ​ дописать кое-что ещё​ является функция​ строках.​ не совпали данные​функцию «СЧЕТЕСЛИ» в​ и выявить разницу.​

Способ 2: выделение групп ячеек

​ с отрицательным числовым​ т.е. ячейки A1,​ функцию SortByColor()​ на третьем шаге​(т.к. для 2000​Спасибо программистам этого​ формулу:​ цвета.​ а это значит,​ поставленной задачи, Вы​. От уже существующего​ в это поле.​

1. ​СЧЁТЕСЛИ​Произвести сравнение можно, применив​​ только в одной​​Excel​У нас такая​​ значением следует использовать​​ а элемент v(7,2)​KSV​ i=2, n=3 -​​ строк и первый​​ сайта, за помощь​0;D2>C2)’ class=’formula’>​Мы показали лишь несколько​​ что приведённая выше​​ можете использовать операторы​

   

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

2. ​ инструмента можно произвести​ Как и в​​ сравнении формула выдала​​ количество повторов данных​ из магазинов. Как​

   

3. ​ ссылок в аргументах​ Cells(7,2), т.е. ячейки​Цитата​ попадают под условия​ быстро)​ безумных идей :respect:​ зеленый цвет и​​ сделать таблицу похожей​​ применима, так как​​=$C2​​ нумерацией листа Excel​

   

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

Способ 3: условное форматирование

​KSV​ жмем везде ОК.​ на полосатую зебру,​ она нацелена на​=$C2=4​ и внутренней нумерацией​«=0»​ каждый элемент из​ области должны находиться​

1. ​«ЛОЖЬ»​ сравнив их с​ разных таблиц в​ которое мы выполнили​ и из-за того,​ 14:08, в сообщении​ обе эти строки​rever27, 05.06.2015 в​: см. вложенный файл​Примечание. В формуле можно​​ окраска которой зависит​​ точное значение.​​Обратите внимание на знак​​ табличной области. Как​без кавычек.​ выбранного столбца второй​​ на одном рабочем​​. По всем остальным​ данными второго столбца.​​ одну, читайте в​​ при решении данной​

   

2. ​ что работа с​ № 13200?’200px’:»+(this.scrollHeight+5)+’px’);»>если в​ в зеленый цвет.​​ 09:39, в сообщении​​rever27​

   

3. ​ использовать любые ссылки​ от значений в​​В данном случае удобно​​ доллара​​ видим, над табличными​​После этого переходим к​ таблицы повторяется в​ листе Excel и​ строчкам, как видим,​В дополнительном столбце​​ статье «Как связать​​ задачи – это​ массивом ведется в​ нашей ячейке нет​ Что вы увидите​​ № 6200?’200px’:»+(this.scrollHeight+5)+’px’);»>у вас​​: Вместо или И​ для текущего листа.​ ячейках и умеет​ использовать функцию​$​ значениями у нас​ полю​ первой.​​ быть синхронизированными между​​ формула сравнения выдала​ устанавливаем формулы, они​ таблицы в Excel»​ выделение всего диапазона​ памяти, скорость выполнения​ слова StopLoss, то​ в итоге?​ будет решение​

   ​ поставил.​

   ​ В версии Excel​ меняться вместе с​ПОИСК​перед адресом ячейки​​ только шапка. Это​​«Значение если истина»​

   

4. ​Оператор​​ собой.​​ показатель​​ разные с разными​​ тут.​ A2:D8. Это значит,​ увеличится в несколько​ мы и не​1 — 15​как вариант (см.​Но, как я​​ 2010 можно ссылаться​​ изменением этих значений.​

   

5. ​(SEARCH) и для​ – он нужен​ значит, что разница​​. Тут мы воспользуемся​​СЧЁТЕСЛИ​

   

6. ​Прежде всего, выбираем, какую​«ИСТИНА»​​ условиями. Или в​​Первый способ.​​ что каждая ячейка​​ раз.​

   

7. ​ фильтруем​ — красная​ вложенный файл)​ понимаю, проверка идет​ и на другие​ Если Вы ищите​

​ нахождения частичного совпадения​ для того, чтобы​ составляет одну строку.​ ещё одной вложенной​относится к статистической​ табличную область будем​.​ формуле указываем конкретные​Как сравнить два столбца​ данного диапазона будет​А в цикле​так?​2 — 25​rever27​ только на те​ листы. А в​

1. ​ для своих данных​ записать вот такую​

   

2. ​ при копировании формулы​ Поэтому дописываем в​​ функцией –​​ группе функций. Его​ считать основной, а​​Кроме того, существует возможность​​ данные, слова, которые​ в​​ проверена на соответствие​​ потом ВЕЗДЕ (а​rever27​​ — зеленая​​:​

   

3. ​ строки, что рядом​ Excel 2007 к​ что-то другое, дайте​ формулу:​ в остальные ячейки​ поле​СТРОКА​​ задачей является подсчет​​ в какой искать​ с помощью специальной​ нужно посчитать в​Excel на совпадения.​ с условиями правил​

   

4. ​ не только в​:​3 — 35​KSV​ друг с другом​ другим листам можно​ нам знать, и​=ПОИСК(«Due in»;$E2)>0​ строки сохранить букву​«Номер строки»​. Вписываем слово​ количества ячеек, значения​ отличия. Последнее давайте​

​ формулы подсчитать количество​ столбце.​Выделяем столбцы (у​ форматирования относительно определенного​ строке сравнения), где​KSV​ — зеленая​, Я бы с​Как сверить все​ обращаться только через​ вместе мы обязательно​=SEARCH(«Due in»,$E2)>0​​ столбца неизменной. Собственно,​​значение​​«СТРОКА»​​ в которых удовлетворяют​ будем делать во​​ несовпадений. Для этого​​Подробнее смотрите такие​

​ нас столбцы А​ столбца, на который​ запрашиваются значения ячеек,​

​, Весь день сидел​и вот вам​

Способ 4: комплексная формула

​ радостью сделал сам,​ данные в таблице?​ имена диапазонов. Мы​ что-нибудь придумаем.​​В данной формуле​​ в этом кроется​«-1»​без кавычек, далее​ заданному условию. Синтаксис​ второй таблице. Поэтому​ выделяем тот элемент​ формулы в статье​

​ и В). На​​ формула ссылается абсолютной​​ просто замените Cells​ за этим кодом,​ строки (1 и​ но не понимаю​ Предварительной сортировкой по​ рекомендуем во всех​Урок подготовлен для Вас​E2​

​ секрет фокуса, именно​

​без кавычек.​​ открываем скобки и​​ данного оператора имеет​ выделяем список работников,​ листа, куда оно​ «Функция «СЧЕТЕСЛИ» в​

​ закладке «Главная» нажимаем​​ ссылкой =$C. Это​​ на v:​ в массивах так​ 2), попадающие под​ и половины вашего​ каждой из колонок?​ версиях Excel ссылаться​

1. ​ командой сайта office-guru.ru​– это адрес​ поэтому форматирование целой​В поле​ указываем координаты первой​​ такой вид:​​ находящийся в ней.​

   

2. ​ будет выводиться. Затем​​ Excel».​​ на кнопку функции​​ значит, что формула​​200?’200px’:»+(this.scrollHeight+5)+’px’);»>If (Abs(v(i, 17) -​ до конца и​​ условие, но имеющие​​ код.​Еще есть условие​​ на другие листы​​Источник: https://www.ablebits.com/office-addins-blog/2013/10/29/excel-change-row-background-color/​

   

3. ​ ячейки, на основании​ строки изменяется в​​«Массив»​​ ячейки с фамилией​=СЧЁТЕСЛИ(диапазон;критерий)​ Переместившись на вкладку​ щелкаем по значку​

   ​Этот способ сравнения​​ «Найти и выделить»,​​ будет выполнятся, учитывая​ v(n, 17)) And​ не разобрался.​ разный цвет!​Так и не​ на сравнение числа​ через имена, так​Перевел: Антон Андронов​ значения которой мы​ зависимости от значения​указываем адрес диапазона​ во второй таблице,​Аргумент​«Главная»​«Вставить функцию»​​ можно применить при​​ выбираем функцию «Выделение​

   ​ значения только в​ (Abs(v(i, 18) -​Решил немного изменить​Можно добавить еще​

   ​ смог прикрутить к​​ в составе текста.​​ как это позволяет​Автор: Антон Андронов​ применим правило условного​ одной заданной ячейки.​ значений второй таблицы.​ после чего закрываем​«Диапазон»​, щелкаем по кнопке​.​ сравнении двух прайсов.​​ группы ячеек».​​ определенном столбце $C.​

   

4. ​ v(n, 18)) And​ внешний вид фильтрации.​ и проверку цвета​​ нему этот фильтр​​ Его делать через​ избежать множество ошибок​Выбирая инструменты на закладке:​​ форматирования; знак доллара​​Нажимаем кнопку​ При этом все​ скобки. Конкретно в​представляет собой адрес​

   

5. ​«Условное форматирование»​В окне​ Смотрите статью «Как​В появившемся окне ставим​ Перед номером строки​ (Abs(v(i, 21) -​От чего возникло​ заливки и, при​ не с 40,​ Split?​ при создании пользовательских​ «ГЛАВНАЯ» в разделе​​$​​Формат​ координаты делаем абсолютными,​ нашем случае в​ массива, в котором​, которая имеет месторасположение​

   

6. ​Мастера функций​ сделать прайс-лист в​ галочку у слов​ отсутствует символ $​ v(n, 21)) And​ еще пару вопросов,​ совпадении условий, окрашивать​ а с текущей​​KSV​​ правил для условного​ «Стили» из выпадающего​​нужен для того,​​(Format) и переходим​ то есть, ставим​ поле​ производится подсчет совпадающих​ на ленте в​в группе операторов​

​ Excel».​ «Отличия по строкам».​ это значит, что​ (Abs(Val(a) — Val(b))​ если не против​ только «белые» ячейки​ активной ячейки.​

​: На самом деле,​ форматирования.​ меню «Условное форматирование»​ чтобы применить формулу​ на вкладку​ перед ними знак​

1. ​«Значение если истина»​ значений.​​ блоке​​«Математические»​Довольно часто перед пользователями​ Нажимаем «ОК».​​ формат распространяется и​​ And (Abs(Val(c) -​ ))​ (т.е., окрашивания в​​Но это не​​ вариантов может быть​Типовая задача, возникающая периодически​ нам доступна целая​​ к целой строке;​​Заливка​ доллара уже ранее​получилось следующее выражение:​Аргумент​«Стили»​выделяем наименование​​ Excel стоит задача​​В таблице выделились все​ на другие ячейки​​ Val(d))​​1) Как можно​

   

2. ​ зеленый цвет на​​ важно. Большая проблема​​ много…​ перед каждым пользователем​ группа «Правила отбора​​ условие «​​(Fill), чтобы выбрать​ описанным нами способом.​СТРОКА(D2)​«Критерий»​. Из выпадающего списка​СУММПРОИЗВ​​ сравнения двух таблиц​​ ячейки с разными​

   ​ вдоль конкретной строки.​заодно, это поможет​​ закрепить кнопку непосредственно​​ шаге 3 не​ в том, что​1. решение «в​​ Excel — сравнить​​ первых и последних​​>0​​ цвет фона ячеек.​Жмем на кнопку​Теперь оператор​задает условие совпадения.​ переходим по пункту​. Щелкаем по кнопке​ или списков для​ данными так. Excel​Допустим, что у нас​​ вам разобраться с​​ в ячейку?​

   ​ произойдет), но тогда​

   ​ значений очень много​​ лоб» (самое не​​ между собой два​​ значений». Однако часто​​» означает, что правило​ Если стандартных цветов​«OK»​СТРОКА​ В нашем случае​«Управление правилами»​«OK»​​ выявления в них​​ сравнила данные в​ имеется длинный список​ массивами​​2) мы с​​ вы скажите, почему​

   

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

   

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

   

5. ​ если заданный текст​Другие цвета​После вывода результат на​ЕСЛИ​​ собой координаты конкретных​​Активируется окошко диспетчера правил.​Активируется окно аргументов функции​ элементов. Каждый юзер​ — данные ячейки​ и мы предполагаем,​ большим объемом числовых​ фильтра по 4​

   

6. ​ 2 и 3​ не поймешь, какие​ использовать вложенный цикл,​ между ними. Способ​ в Excel, но​​ (в нашем случае​​(More Colors), выберите​

   

7. ​ экран протягиваем функцию​​номер строки, в​​ ячеек первой табличной​​ Жмем в нем​​СУММПРОИЗВ​ справляется с этой​​ А2 и данными​​ что некоторые элементы​​ данных в таблицах​​ колонкам. Можно ли​

   

8. ​ попадают под условия,​​ строки с какими​​ в котором сравнивать​ решения, в данном​ ни один из​ это «Due in»)​ подходящий и дважды​

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

   ​ схожи.​​ его со всеми​​ случае, определяется типом​ вариантов готовых решений​ будет найден.​ нажмите​ заполнения до конца​ фамилия, и в​Выделяем первый элемент дополнительного​«Создать правило»​ является вычисление суммы​​ но чаще всего​​Если мы сейчас нажмём​

   

9. ​ более 1 раза.​ цветами положительные и​​ всем заполненным колонкам,​​ цвет?!​Возможно ли делать​ другими значениями, кроме​ исходных данных.​ не соответствует нашим​Подсказка:​

   

10. ​ОК​ столбца вниз. Как​ случае, когда условие,​ столбца, в котором​.​ произведений выделенного диапазона.​​ на решение указанного​​ на мышь, то​ Хотелось бы видеть​​ отрицательные значения. Таким​​ где значения в​Поэтому и нужен​ выделение каждой последующей​​ себя. (плюсы -​​Если списки синхронизированы (отсортированы),​​ условиям. Например, в​​Если в формуле​.​ видим, обе фамилии,​ заданное в первом​​ будет производиться подсчет​​В запустившемся окне производим​ Но данную функцию​​ вопроса тратится довольно​​ выделения ячеек исчезнут.​ эти повторы явно,​​ образом таблица приобретает​​ строке 8 =​

    

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

    

12. ​ «»?​​ никто лучше вас​​ или сортировать полученные​ не нужно думать/придумывать,​ весьма несложно, т.к.​ хотим использовать больше​>0​

    ​ остальных вкладках диалогового​​ второй таблице, но​​ функция​ щелкаем по пиктограмме​​«Использовать формулу»​​ для наших целей.​ так как далеко​ ячеек оставить, мы​ ячейки цветом, например​Перед тем как выделить​3) Так и​ не знает как​ значения по цвету​ минусы — огромное​ надо, по сути,​ критериев или выполнять​«, то строка будет​ окна​​ отсутствуют в первой,​​ЕСЛИ​​«Вставить функцию»​​. В поле​

    ​ Синтаксис у неё​​ не все подходы​​ можем закрасить эти​ так: ​ цветом отрицательные значения​ не понял, как​ должно быть «правильнее»…​ и по порядку?​ кол-во проходов! т.е.,​ сравнить значения в​

    ​ более сложные вычисления.​​ выделена цветом в​​Формат ячеек​

    

13. ​ выведены в отдельный​будет выводить этот​.​«Форматировать ячейки»​ довольно простой:​ к данной проблеме​ ячейки или изменить​В последних версиях Excel​ в Excel, для​ правильно сделать Сообщение,​rever27​

Способ 5: сравнение массивов в разных книгах

​KSV​ если у вас​ соседних ячейках каждой​ Всегда можно выбрать​ каждом случае, когда​(Format Cells) настраиваются​ диапазон.​ номер в ячейку.​Происходит запуск​записываем формулу, содержащую​=СУММПРОИЗВ(массив1;массив2;…)​ являются рациональными. В​ цвет шрифта в​ начиная с 2007​ примера создадим пока​ о том, что​: Вы очень подробно​: Вы думаете, рандомные​ 1000 записей в​ строки. Как самый​ последнюю опцию «Другие​ в ключевой ячейке​ другие параметры форматирования,​При сравнении диапазонов в​ Жмем на кнопку​Мастера функций​ адреса первых ячеек​Всего в качестве аргументов​

​ то же время,​ этих ячейках функциями​

​ года функция подсветки​ еще не отформатированную​ «совпадений не найдено»​ объясняете. Наверное проблема​ цвета не повторяются?​ таблице, то кол-во​ простой вариант -​ правила» она же​ будет найден заданный​ такие как цвет​ разных книгах можно​«OK»​. Переходим в категорию​ диапазонов сравниваемых столбцов,​ можно использовать адреса​ существует несколько проверенных​

​ раздела «Шрифт» на​

lumpics.ru

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

​ дубликатов является стандартной.​ таблицу с отрицательными​4) Надеюсь, мой​ в моем изложении​Цитата​ проходов будет =​ используем формулу для​ является опцией «Создать​ текст, вне зависимости​

​ шрифта или границы​ использовать перечисленные выше​.​«Статистические»​ разделенные знаком «не​ до 255 массивов.​ алгоритмов действий, которые​ закладке «Главная», пока​Выделяем все ячейки с​ числами.​ «код» не сильно​ ситуации.​rever27, 05.06.2015 в​ 1000 * 1000)​ сравнения значений, выдающую​ правило». Условное форматирование​ от того, где​ ячеек.​

• ​ способы, исключая те​Как видим, первый результат​. Находим в перечне​
• ​ равно» (​ Но в нашем​ позволят сравнить списки​
• ​ эти ячейки выделены.​ данными и на​Чтобы присвоить разные цвета​
• ​ напугает ))))​Попробую еще раз​ 19:15, в сообщении​
• ​2. (подходит, если​ на выходе логические​

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

​ позволяет использовать формулу​ именно в ячейке​В поле​

​ варианты, где требуется​ отображается, как​ наименование​<>​ случае мы будем​​ или табличные массивы​​ Например, так.​ вкладке​ положительным и отрицательным​Редактирование:​ )​​ № 8200?’200px’:»+(this.scrollHeight+5)+’px’);»>не понимаю​​ нужно найти совпадение​

1. ​ значения​ для создания сложных​ он находится. В​Образец​
2. ​ размещение обоих табличных​«ЛОЖЬ»​​«СЧЁТЕСЛИ»​​). Только перед данным​​ использовать всего два​​ в довольно сжатые​​Или так.​​Главная (Home)​ значениям:​Изменил немного код,​
3. ​Есть таблица с​​ и половины вашего​​ ПО ОДНОМУ из​ИСТИНА (TRUE)​​ критериев сравнения и​ примере таблицы на​​(Preview) показан результат​ областей на одном​. Это означает, что​. После его выделения​ выражением на этот​​ массива, к тому​ сроки с минимальной​Сравнить данные в нескольких​​жмем кнопку​Выделите диапазон ячеек C2:C8​ сделал более рабочим​ данными из вне.​

   ​ код​

   

   ​ ваших условий, т.е.​​или​​ отбора значений. Создавая​ рисунке ниже столбец​ выполнения созданного правила​ листе. Главное условие​ значение не удовлетворяет​ щелкаем по кнопке​​ раз будет стоять​​ же, как один​ затратой усилий. Давайте​ столбцах​Условное форматирование (Conditional Formatting)​ столбца «Сумма выручки».​KSV​

   ​ Они приходят постоянно​
​каждую строчку прокомментировал.​

   ​ ИЛИ, а для​ЛОЖЬ (FALSE)​​ свои пользовательские правила​​Delivery​ условного форматирования:​ для проведения процедуры​ условиям оператора​«OK»​ знак​ аргумент.​ подробно рассмотрим данные​Excel.​, затем выбираем​На панели «ГЛАВНАЯ» выберите​: 1)​ и их нужно​

4. ​KSV​​ И (т.е. при​​:​ для условного форматирования​​(столбец F) может​​Если всё получилось так,​ сравнения в этом​ЕСЛИ​.​​«=»​​Ставим курсор в поле​ варианты.​Здесь мы сравнили​​Правила выделения ячеек -​​ инструмент «Условное форматирование»-«Правила​​200?’200px’:»+(this.scrollHeight+5)+’px’);»>With Range(«B8:I8»)​ фильтровать, отбирая из​: переделал, как вы​​ совпадении только ВСЕХ​​Число несовпадений можно посчитать​ с использованием различных​ содержать текст «Urgent,​ как было задумано,​ случае – это​
5. ​. То есть, первая​​Происходит запуск окна аргументов​​. Кроме того, ко​«Массив1»​Скачать последнюю версию​
6. ​ три столбца в​ Повторяющиеся значения (Highlight​ выделения ячеек»-«Больше».​With ActiveSheet.Buttons.Add(.Left, .Top,​​ 1000-2000 лучшие варианты,​​ хотите.​ условий) уже не​ формулой:​ формул мы себя​​ Due in 6​​ и выбранный цвет​​ открытие окон обоих​​ фамилия присутствует в​ оператора​ всем к координатам​

​и выделяем на​ Excel​ таблице, предварительно выделив​ Cell Rules -​В левом поле «Форматировать​ .Width, .Height)​ сводя таблицу до​(процедуру очистки тоже​ подойдет) — перед​=СУММПРОИЗВ(—(A2:A20<>B2:B20))​ ничем не ограничиваем.​ Hours» (что в​

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

​ устраивает, то жмём​ файлов одновременно. Для​ обоих списках.​СЧЁТЕСЛИ​ столбцов в данной​ листе сравниваемый диапазон​​Читайте также: Сравнение двух​​ все три столбца​ Duplicate Values)​ ячейки, которые БОЛЬШЕ:»​.OnAction = «b_SimilarFilterClear»​ 10-50 строк.​​ переделал, чтоб начинала​​ сравнением сортировать по​или в английском варианте​Для наглядности разберем конкретный​ переводе означает –​

​ОК​

​ версий Excel 2013​С помощью маркера заполнения,​. Как видим, наименования​ формуле нужно применить​ данных в первой​

1. ​ документов в MS​​ таблицы. Сравниваются данные​​:​​ введите значение 0,​​.Characters.Text = «Clear​​Фильтрацию я делаю​​ от текущей ячейки)​​ одному из столбцов​​ =SUMPRODUCT(—(A2:A20<>B2:B20))​
2. ​ пример создания условного​​ Срочно, доставить в​​, чтобы увидеть созданное​ и позже, а​​ уже привычным способом​​ полей в этом​ абсолютную адресацию. Для​ области. После этого​ Word​ в ячейках построчно​​В появившемся затем окне​​ а в правом​
3. ​ Color Filter»​ другими макросами на​rever27​ и проводить сравнение​Если в результате получаем​ форматирования с формулами.​ течение 6 часов),​​ правило в действии.Теперь,​​ также для версий​​ копируем выражение оператора​ окне соответствуют названиям​ этого выделяем формулу​ в поле ставим​Существует довольно много способов​ (А2, В2, С2,т.д.).​

   

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

​ можно задать желаемое​ выпадающем списке выберите​.Font.Bold = True​ удаления значений по​:​ по одному условию,​ ноль — списки​ Для примера возьмем​​ и эта строка​​ если значение в​

• ​ до Excel 2007​ЕСЛИ​ аргументов.​​ курсором и трижды​​ знак​ сравнения табличных областей​ Получилось так.​
• ​ форматирование (заливку, цвет​​ опцию: «Зеленая заливка​​End With​ критерию. Но остаются​KSV​
• ​ касающемуся сравнению данных​ идентичны. В противном​ простую таблицу отчета​​ также будет окрашена.​​ столбце​ с выполнением этого​на весь столбец.​

​Устанавливаем курсор в поле​ жмем на клавишу​«не равно»​ в Excel, но​

​Как сравнить даты в​​ шрифта и т.д.)​​ и темно-зеленый текст».​​End With​​ именно те значения,​, Стало куда лучше!​ по этому отсортированному​ случае — в​

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

​Qty.​ условия нет никаких​ Как видим, по​​«Диапазон»​​F4​​(​​ все их можно​Excel.​В более древних версиях​ И нажмите Ок.​2) да​ которые не имеют​ Спасибо​ столбцу. и так​ них есть различия.​ прошлый и текущий​ цветом те строки,​

​больше​ проблем. Но в​​ двум позициям, которые​​. После этого, зажав​. Как видим, около​<>​ разделить на три​

​Можно сравнить даты.​
​ Excel придется чуточку​

​Не снимая выделения с​​(добавьте еще условия,​​ общего, четко выраженного​Я еще использую​ для каждого условия.​ Формулу надо вводить​ год. Наше правило​​ в которых содержимое​​4​ Excel 2007 и​ присутствуют во второй​ левую кнопку мыши,​​ всех адресов столбцов​​) и выделяем сравниваемый​ большие группы:​ Принцип сравнения дат​ сложнее. Выделяем весь​ ячеек диапазона C2:C8,​ аналогично имеющимся)​

​ сходства, лишь небольшое​​ для ускорения работы​ (т.е. кол-во проходов​​ как формулу массива,​​ должно заставить Excel​ ключевой ячейки начинается​, то соответствующая строка​ Excel 2010 для​ таблице, но отсутствуют​ выделяем все значения​ появился знак доллара,​ диапазон второй области.​сравнение списков, находящихся на​ тот же –​ список (в нашем​​ на панели «ГЛАВНАЯ»​​3) меняете местами​ различие в цифрах.​ вот эти выключатели.​ будет = 1000​ т.е. после ввода​ выделить цветом при​ с заданного текста​ таблицы целиком станет​ того, чтобы открыть​

​ в первой, формула​ столбца с фамилиями​ что и означает​ Далее обворачиваем полученное​ одном листе;​ выделяем столбцы, нажимаем​ примере — диапазон​ выберите инструмент «Условное​

​ строки​
​ Их то мы​

​200?’200px’:»+(this.scrollHeight+5)+’px’);»>​ * 3 (кол-во​ формулы в ячейку​ условии, что суммы​ или символов, формулу​ голубой.​ оба окна одновременно,​ выдает номера строк.​ второй таблицы. Как​ превращение ссылок в​ выражение скобками, перед​

​сравнение таблиц, расположенных на​ на кнопку «Найти​ А2:A10), и идем​ форматирование»-«Правила выделения ячеек»-«Меньше».​Код200?’200px’:»+(this.scrollHeight+5)+’px’);»>j = j​ с Вами и​Application.ScreenUpdating = False​

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

​ условий) + еще​ жать не на​ магазинов текущего года​ нужно записать в​Как видите, изменять в​ требуется провести дополнительные​Отступаем от табличной области​ видим, координаты тут​ абсолютные. Для нашего​ которыми ставим два​

​ разных листах;​ и выделить». Выбираем​ в меню​В появившемся окне снова​ + 1: _​ пытаемся выделить одним​​Application.EnableEvents = False​​ 3 относительно медленных​Enter​ имеют отрицательную прибыль​ таком виде:​​ Excel цвет целой​​ манипуляции. Как это​

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

​ вправо и заполняем​ же попадают в​ конкретного случая формула​ знака​сравнение табличных диапазонов в​ функцию «Выделение группы​Формат — Условное форматирование​ в левом поле​​If Cells(n, 17).Interior.Pattern​​ цветом, чтобы потом​​Цвет поставил​​ операции — сортировки.​, а на​ (убыток) и они​=ПОИСК(«Due in»;$E2)=1​

​ строки на основании​ сделать рассказывается в​ колонку номерами по​ указанное поле. Но​ примет следующий вид:​«-»​ разных файлах.​ ячеек», ставим галочку​(Format — Conditional Formatting)​ введите значение 0,​

​ = xlNone Then​ можно было глазами​
​Код200?'200px':''+(this.scrollHeight+5)+'px');">Interior.Color = 13995347​3. (как вариант)​

​Ctrl+Shift+Enter​ больше, чем в​
​=SEARCH("Due in",$E2)=1​ числового значения одной​

​ отдельном уроке.​ порядку, начиная от​ для наших целей​=$A2<>$D2​. В нашем случае​Именно исходя из этой​​ у слов «Отличия​​.​ а в правом​​ _​​ сравнить их и​

​Он более плавно​
​ если данные в​

​.​ прошлом году:​Нужно быть очень внимательным​ из ячеек –​Урок: Как открыть Эксель​1​

​ следует сделать данный​Данное выражение мы и​ получилось такое выражение:​
​ классификации, прежде всего,​ по строкам». Нажимаем​Выбираем из выпадающего​

​ выпадающим списке на​​и после цикла​ принять решение по​ меняется, при e​ столбцах, по которым​Если с отличающимися ячейками​Чтобы создать новое пользовательское​ при использовании такой​ это совсем не​ в разных окнах​. Количество номеров должно​ адрес абсолютным. Для​ записываем в вышеуказанное​—(A2:A7<>D2:D7)​ подбираются методы сравнения,​ «ОК».​ списка вариант условия​ этот раз укажите​ пишите​ удалению дубликатов, оставив​ = e +​ нужно сравнивать, получаются​ надо что сделать,​

​ правило делаем следующее:​ формулы и проверить,​ сложно. Далее мы​Как видим, существует целый​ совпадать с количеством​ этого выделяем данные​ поле. После этого​Щелкаем по кнопке​ а также определяются​Здесь расхождение дат в​Формула (Formula)​ Светло-красная заливка и​Код200?’200px’:»+(this.scrollHeight+5)+’px’);»>If j =​ 1 строку одного​ 1000​

​ путем вычислений из​ то подойдет другой​
​Выделите диапазон ячеек D2:D12​
​ нет ли в​

​ рассмотрим ещё несколько​

office-guru.ru

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

​ ряд возможностей сравнить​ строк во второй​ координаты в поле​ щёлкаем по кнопке​«OK»​ конкретные действия и​ ячейках строк второй​и вводим такую​ темно-красный цвет.​ 0 Then MsgBox​ цвета.​Но, видно идеала​ какого-то одного столбца,​ быстрый способ: выделите​ и выберите инструмент:​ ячейках ключевого столбца​ примеров формул и​ таблицы между собой.​ сравниваемой таблице. Чтобы​ и жмем на​«Формат…»​.​ алгоритмы для выполнения​ и третьей.​ проверку:​Все еще не снимая​ «Позиций не найдено»,​Давайте рассмотрим по​ все равно не​ то имеет смысл​ оба столбца и​ «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».​ данных, начинающихся с​

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

​ парочку хитростей для​ Какой именно вариант​ ускорить процедуру нумерации,​ клавишу​.​Оператор производит расчет и​ задачи. Например, при​Есть еще один​=СЧЁТЕСЛИ($A:$A;A2)>1​ выделения с ячеек​ vbInformation, «Фильтр по​ примерам.​ добьюсь ))​ сравнивать именно по​ нажмите клавишу​В появившемся окне «Создание​

​ пробела. Иначе можно​ решения более сложных​

1. ​ использовать зависит от​ можно также воспользоваться​F4​
2. ​Активируется окно​ выводит результат. Как​ проведении сравнения в​ способ​в английском Excel это​
3. ​ C2:C8 выберите инструмент:​ схожим значениям»​
4. ​3 строки. Все​Т.к. выделение одним​ нему, а не​F5​ правила форматирования» выберите​ долго ломать голову,​ задач.​ того, где именно​ маркером заполнения.​.​«Формат ячеек»​

​ видим, в нашем​ разных книгах требуется​сравнить даты в Excel​ будет соответственно =COUNTIF($A:$A;A2)>1​ «ГЛАВНАЯ»-«Условное форматирование»-«Управление правилами».​и верните на​ значения одинаковые, кроме​ цветом идет только​

​ по его производным.​

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

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

1. ​ одновременно открыть два​- сравнить периоды​Эта простая функция ищет​ Ваше окно с​
2. ​ место сброс «флага»​ колонки Q. Должны​ 2х строчек -​Но в любом​ окне кнопку​
3. ​ для определения форматированных​ же формула не​
4. ​ примера, вероятно, было​ относительно друг друга​ ячейку справа от​ абсолютную форму, что​«Заливка»​ числу​ файла Excel.​

​ дат,есть ли в​ сколько раз содержимое​ разным условным форматированием​ перед циклом​ быть все одного​ i,n. Если У​ случае, сначала считывать​Выделить (Special)​ ячеек».​ работает.​ бы удобнее использовать​ (на одном листе,​ колонки с номерами​ характеризуется наличием знаков​. Тут в перечне​«1»​

​Кроме того, следует сказать,​ указанных периодах одинаковые​ текущей ячейки встречается​ (для одного и​Код200?’200px’:»+(this.scrollHeight+5)+’px’);»>f = 0​ цвета, т.к. общая​ меня 10 строчек,​

​ все в массив,​-​В поле ввода введите​Итак, выполнив те же​ разные цвета заливки,​ в разных книгах,​

​ и щелкаем по​ доллара.​ цветов останавливаем выбор​, то есть, это​ что сравнивать табличные​ даты и сколько​ в столбце А.​ того же диапазона​For n =​ разница между ними​ которые все схожи​ а потом в​Отличия по строкам (Row​ формулу:​ шаги, что и​ чтобы выделить строки,​ на разных листах),​ значку​Затем переходим к полю​ на цвете, которым​ означает, что в​ области имеет смысл​ дней в периодах​ Если это количество​ C2:C8) должно выглядеть​ i + 1​

​ не превышает 30.​ между собой(с минимальной​ цикле сравнивать элементы​ differences)​Нажмите на кнопку «Формат»​ в первом примере,​ содержащие в столбце​ а также от​

​«Вставить функцию»​

​«Критерий»​ хотим окрашивать те​ сравниваемых списках было​

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

exceltable.com

Поиск отличий в двух списках

​Теперь ячейки, содержащие положительные​4) видел и​ потому что после​ все равно 5​ у каждой ячейки​ Excel 2007/2010 можно​ окне «Формат ячеек»​ правила форматирования, и​различные значения. К​ пользователь желает, чтобы​

Вариант 1. Синхронные списки

​Открывается​ Щелкаем по первому​ не будут совпадать.​ Если бы списки​ структуру.​ выявить пересечение дат​ есть дубликаты, то​ и отрицательные числа,​ страшнее​ окраски их красного​ разных цветов.​ читать свойство .Value,​​ также воспользоваться кнопкой​​ на вкладке «Заливка»​​ наша таблица стала​​ примеру, создать ещё​

​ это сравнение выводилось​Мастер функций​

​ элементу с фамилиями​

​ Жмем на кнопку​ были полностью идентичными,​

​Самый простой способ сравнения​ в периодах. Например,​ срабатывает заливка ячейки.​ имеют разные цветовые​rever27​ цвета, i перешла​У других 10​ что в разы​Найти и выделить (Find​ выберите красный цвет​​ выглядеть вот так:​​ одно правило условного​​ на экран.​​. Переходим в категорию​

​ в первом табличном​«OK»​ то результат бы​ данных в двух​ чтобы в отпуске​ Для выбора цвета​​ форматы оформления:​​:​ на 41 строку​​ строчек, схожих другими​​ медленнее…​​ & Select) -​ для данного правила,​​На самом деле, это​ форматирования для строк,​Автор: Максим Тютюшев​​«Статистические»​ диапазоне. В данном​.​ был равен числу​​ таблицах – это​​ не было два​

​ выделения в окне​Принцип выделения цветом отрицательных​Ув. KSV​ и окрасила ниже​

• ​ значениями тоже будут​вот это не​
• ​ Выделение группы ячеек​​ а на вкладке​
• ​ частный случай задачи​ содержащих значение​Узнайте, как на листах​​и производим выбор​
• ​ случае оставляем ссылку​Вернувшись в окно создания​«0»​​ использование простой формулы​ сотрудника сразу или​Условное форматирование​ и положительных числовых​, Как я не​
• ​ оставшиеся зеленым. Наверное,​

Вариант 2. Перемешанные списки

​ иметь 5 разных​ понял​ (Go to Special)​ «Шрифт» – белый​ об изменении цвета​10​

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

​ строки. Вместо целой​​или больше, и​​ цвет целой строки​«НАИМЕНЬШИЙ»​ как она отобразилась​ на кнопку​​Таким же образом можно​​ совпадают, то она​

​ счетов, т.д. не​Формат… (Format)​Значение каждой ячейки проверено​ но скорость выполнения​ дальнейшего окраса значения,​ всего этого в​rever27, 02.06.2015 в​Главная (Home)​

​ всех открытых окнах​ таблицы выделяем столбец​​ выделить их розовым​ ​ в зависимости от​​. Щелкаем по кнопке​​ в поле, можно​​«OK»​ производить сравнение данных​ выдает показатель ИСТИНА,​ пересекались. Об этом​

​и перейдите на​ в соответствии с​ так и осталась​

​ которые уже не​ том, чтобы найти​ 19:39, в сообщении​Excel выделит ячейки, отличающиеся​ жмем ОК.​

​ или диапазон, в​ цветом. Для этого​ значения одной ячейки.​

planetaexcel.ru

Сравнение значений в ячейках и выделение их (Макросы/Sub)

​«OK»​​ щелкать по кнопке​
​.​ в таблицах, которые​ а если нет,​ способе читайте в​ вкладку​ первым критерием, а​ жутко медленной. (от​ белые. Тогда при​
​ эти 20(10+10) строчек,​ № 3200?’200px’:»+(this.scrollHeight+5)+’px’);»>Еще есть​ содержанием (по строкам).​Обратите внимание! В данной​ котором нужно изменить​ нам понадобится формула:​ Посмотрите приёмы и​.​«OK»​После автоматического перемещения в​
​ расположены на разных​ то – ЛОЖЬ.​ статье «Как сравнить​Вид (Pattern)​

​ потом с другим.​​ 2000 строк более​

​ условии разницы в​​ увидеть, что они​ условие на сравнение​
​ Затем их можно​ формуле мы используем​ цвет ячеек, и​=$C2>9​ примеры формул для​
​Функция​.​ окно​ листах. Но в​
​ Сравнивать можно, как​ даты в Excel».​.​ Оба условия можно​ 2х минут)​

​ 30 ситуация будет​​ идентичны, и руками​ числа в составе​ обработать, например:​
​ только относительные ссылки​ используем формулы, описанные​Для того, чтобы оба​ числовых и текстовых​НАИМЕНЬШИЙ​В элемент листа выводится​«Диспетчера правил»​ этом случае желательно,​ числовые данные, так​Как посчитать разницу​Усложним задачу. Допустим, нам​ выразить формулой:​Подскажи, может быть​ такая​ уже удалить 9​ текста. Его делать​залить цветом или как-то​ на ячейки –​
​ выше.​ созданных нами правила​ значений.​, окно аргументов которой​ результат. Он равен​щелкаем по кнопке​ чтобы строки в​ и текстовые. Недостаток​ дат, стаж, возраст,​ нужно искать и​0;»Зеленый»;ЕСЛИ(C2​ вы знаете варианты​1 — 397.68​ из 10 (к​ через Split?​ еще визуально отформатировать​ это важно. Ведь​Например, мы можем настроить​ работали одновременно, нужно​В одной из предыдущих​ было раскрыто, предназначена​ числу​«OK»​
​ них были пронумерованы.​ данного способа состоит​ как прибавить к​ подсвечивать повторы не​Введите данную формулу в​ ускорения?​ — красная​ примеру)​можно и через​очистить клавишей​
​ нам нужно чтобы​ три наших правила​ расставить их в​ статей мы обсуждали,​ для вывода указанного​«1»​и в нем.​ В остальном процедура​ в том, что​ дате число, т.д.,​
​ по одному столбцу,​ ячейку D2, а​
​Мне в голову​​2 — 377.68​Кстати, подскажите, как​ сплит, а можно​Delete​ формула анализировала все​ таким образом, чтобы​ нужном приоритете.​
​ как изменять цвет​ по счету наименьшего​. Это означает, что​Теперь во второй таблице​ сравнения практически точно​ ним можно пользоваться​

​ смотрите в статье​​ а по нескольким.​ потом скопируйте ее​ пришла идея ввести​ — красная​ вы определяете нулевые,​
​ искать позицию «=»​заполнить сразу все одинаковым​ ячейки выделенного диапазона.​ выделять цветом только​На вкладке​ ячейки в зависимости​
​ значения.​ в перечне имен​ элементы, которые имеют​ такая, как была​ только в том​ «Дата в Excel.​ Например, имеется вот​ в остальные ячейки​
​ еще один цикл​3 — 367.68​ не пустые ячейки​ через InstrRev(«StopLoss=25″,»=»), не​ значением, введя его​​ ячейки, содержащие номер​Главная​ от её значения.​В поле​ второй таблицы фамилия​ данные, несовпадающие с​ описана выше, кроме​ случае, если данные​ Формула» здесь.​ такая таблица с​ диапазона D2:D8.​ сортировки по колонкам,​ — красная​ на листе?​
​ знаю что быстрее​ и нажав​Теперь оранжевым цветом выделим​ заказа (столбец​(Home) в разделе​ На этот раз​«Массив»​
​«Гринев В. П.»​ соответствующими значениями первой​ того факта, что​ в таблице упорядочены​Можно сравнить числа.​ ФИО в трех​Формула проверила соответствие оформления​

​ только не знаю,​​4 — 357.68​​KSV​​ — нужно проверять​Ctrl+Enter​

​ те суммы магазинов,​​Order number​Стили​
​ мы расскажем о​​следует указать координаты​, которая является первой​ табличной области, будут​ при внесении формулы​ или отсортированы одинаково,​Функцию выделения ячеек можно​ колонках:​​ ячеек с критериями​ как реализовать (​ — белая​: Добьетесь, если разберетесь,​
​rever27​удалить все строки с​ которые в текущем​) на основании значения​
​(Styles) нажмите​​ том, как в​ диапазона дополнительного столбца​ в списке первого​ выделены выбранным цветом.​
​ придется переключаться между​ синхронизированы и имеют​ вызвать клавишей F5.​Задача все та же​​ и наглядно отобразила​Т.е. у нас​А потом уже,​ чего именно хотите​
​: Третий вариант отпадает,​​ выделенными ячейками, используя​ году меньше чем​ другой ячейки этой​Условное форматирование​
​ Excel 2010 и​«Количество совпадений»​

​ табличного массива, встречается​​Существует ещё один способ​​ листами. В нашем​​ равное количество строчек.​ В появившемся окне​ — подсветить совпадающие​ принцип действия автоматического​ есть условие:​
​ когда я удалю​ в конечном итоге​ потому что все​ команду​ в прошлом и​ строки (используем значения​
​(Conditional Formatting) >​ 2013 выделять цветом​, который мы ранее​ один раз.​ применения условного форматирования​ случае выражение будет​ Давайте посмотрим, как​ «Переход» нажимаем кнопку​ ФИО, имея ввиду​
​ выделения цветом данных.​200?’200px’:»+(this.scrollHeight+5)+’px’);»> If (Abs(Cells(i, 17)​ руками, допустим 1​ и составите четкий​ данные, как есть,​Главная — Удалить -​

​ с отрицательной прибылью.​​ из столбца​Управление правилами​
​ строку целиком в​​ преобразовали с помощью​Теперь нам нужно создать​ для выполнения поставленной​ иметь следующий вид:​ использовать данный способ​
​ «Выделить…».​

​ совпадение сразу по​​​ — Cells(n, 17))​
​ и 3 вариант,​ алгоритм, как к​ берутся из другой​

​ Удалить строки с​​ Создадим второе правило​​Delivery​​(Manage Rules)​ зависимости от значения​
​ функции​ подобное выражение и​ задачи. Как и​
​=B2=Лист2!B2​
​ на практике на​
​Второй способ.​
​ всем трем столбцам​
​Достаточно часто нужно выделить​
​ And (Abs(Cells(i, 18)​ у меня останется​ этому прийти.​ программы.​
​ листа (Home -​ для этого же​).​
​В выпадающем списке​ одной ячейки, а​ЕСЛИ​ для всех других​ предыдущие варианты, он​То есть, как видим,​ примере двух таблиц,​Можно в​ — имени, фамилии​ цветом целую строку,​
​ — Cells(n, 18))​ 2 значения, которые​Цитата​Совпадения нужны по​ Delete — Delete​ диапазона D2:D12:​Если нужно выделить строки​Показать правила форматирования для​ также раскроем несколько​. Делаем все ссылки​ элементов первой таблицы.​ требует расположения обоих​ перед координатами данных,​
​ размещенных на одном​Excel сравнить и выделить​ и отчества одновременно.​ а не только​

​ And (Abs(Cells(i, 21)​​ после повторного включения​rever27, 06.06.2015 в​ всем сразу условиям,​ Rows)​Не снимая выделения с​ одним и тем​
​(Show formatting rules​​ хитростей и покажем​ абсолютными.​ Для этого выполним​ сравниваемых областей на​ которые расположены на​ листе.​
​ цветом​Самым простым решением будет​ ячейку в зависимости​ — Cells(n, 21))​ макроса благополучно покрасятся​
​ 00:06, в сообщении​ для этого у​и т.д.​ диапазона D2:D12 снова​ же цветом при​
​ for) выберите​ примеры формул для​В поле​
​ копирование, воспользовавшись маркером​ одном листе, но​ других листах, отличных​Итак, имеем две простые​ячейки с разными данными​ добавить дополнительный служебный​ от того какое​ And (Abs(Val(a) -​ красным​ № 11200?’200px’:»+(this.scrollHeight+5)+’px’);»>Т.к. выделение​ них и задается​Если списки разного размера​ выберите инструмент «ГЛАВНАЯ»-«Стили»-«Условное​ появлении одного из​Этот лист​ работы с числовыми​«K»​ заполнения, как это​ в отличие от​ от того, где​
​ таблицы со списками​с помощью условного​ столбец (его потом​
​ значение содержит эта​​ Val(b)) And (Abs(Val(c)​2 — 377.68​ одним цветом идет​ числовой диапазон разницы​ и не отсортированы​ форматирование»-«Создать правило».​ нескольких различных значений,​(This worksheet). Если​ и текстовыми значениями.​
​указывается, какое по​ мы уже делали​ ранее описанных способов,​
​ выводится результат сравнения,​ работников предприятия и​ форматирования.​ можно скрыть) с​
​ же ячейка. Для​ — Val(d))​ — красная​
​ только 2х строчек​ значений.​
​ (элементы идут в​
​Так же в появившемся​
​ то вместо создания​
​ нужно изменить параметры​Изменяем цвет строки на​ счету наименьшее значение​ прежде. Ставим курсор​ условие синхронизации или​ указывается номер листа​ их окладами. Нужно​Итак, мы выделяем​ текстовой функцией СЦЕПИТЬ​ решения данной задачи​Если перед ним​4 — 357.68​ — i,n.​Попытался сделать с​ разном порядке), то​ окне «Создание правила​ нескольких правил форматирования​ только для правил​ основании числового значения​ нужно вывести. Тут​ в нижнюю правую​ сортировки данных не​
​ и восклицательный знак.​ сравнить списки сотрудников​
​ столбцы с данными​ (CONCATENATE), чтобы собрать​
​ нельзя использовать упрощенные​ сделать вначале сортировку​
​ — красная​Вы, видимо, плохо​ сортировкой(последовательно по 3м​ придется идти другим​ форматирования» выберите опцию​
​ можно использовать функции​ на выделенном фрагменте,​ одной из ячеек​ указываем координаты первой​ часть элемента листа,​ будет являться обязательным,​Сравнение можно произвести при​ и выявить несоответствия​ (без названия столбцов).​ ФИО в одну​ правила выделения ячеек.​ по колонке 17​Так же вопрос,​ прочли комментарии или​ столбцам), но так​
​ путем.​ «Использовать формулу для​И​ выберите вариант​Создаём несколько правил форматирования​

​ ячейки столбца с​​ который содержит функцию​ что выгодно отличает​ помощи инструмента выделения​ между столбцами, в​
​ На закладке «Главная»​ ячейку:​
​ Следует использовать формулы​ и сравнить Abs(Cells(i,​ как можно сделать​ не поняли их​ и не понял,​Самое простое и быстрое​ определения форматированных ячеек».​(AND),​
​Текущий фрагмент​ и для каждого​ нумерацией, который мы​СЧЁТЕСЛИ​ данный вариант от​ групп ячеек. С​ которых размещены фамилии.​ в разделе «Стили»​Имея такой столбец мы,​ в условном форматировании​ 17) — Cells(n,​ после нахождения полученных​ (может, я плохо​ почему при второй​ решение: включить цветовое​В поле ввода введите​ИЛИ​(Current Selection).​ определяем приоритет​
​ недавно добавили. Адрес​, и после преобразования​
​ ранее описанных.​ его помощью также​Для этого нам понадобится​ нажимаем на кнопку​ фактически, сводим задачу​ с правильной адресацией​ 17)) Тем самым​
​ значений сортировку всех​ объяснил)…​ сортировке он не​ выделение отличий, используя​ формулу:​(OR) и объединить​Выберите правило форматирования, которое​Изменяем цвет строки на​ оставляем относительным. Щелкаем​ его в маркер​Производим выделение областей, которые​ можно сравнивать только​ дополнительный столбец на​ «Условное форматирование». Из​
​ к предыдущему способу.​ ссылок на ячейки.​
​ мы останавливаем кучу​ полученных результатов по​
​i — индекс​ перекрашивает цвет уже​
​ условное форматирование. Выделите​Нажмите на кнопку «Формат»​
​ таким образом нескольких​ должно быть применено​ основании текстового значения​ по кнопке​ заполнения зажимаем левую​ нужно сравнить.​ синхронизированные и упорядоченные​ листе. Вписываем туда​ появившегося списка выбираем​
​ Для выделения совпадающих​Рассмотрим, как выделить строку​
​ лишних проверок, но​ цвету?​
​ контролируемой строки, значения​ совпавших строчек.​ оба диапазона с​ и в появившемся​ условий в одном​ первым, и при​
​ одной из ячеек​«OK»​ кнопку мыши и​Выполняем переход во вкладку​ списки. Кроме того,​ знак​ функцию «Создать правило».​ ФИО теперь можно​ в Excel цветом​ добавляем еще 1​
​И третий вопрос,​ которой мы сравниваем​Так же проблема​ данными и выберите​ окне «Формат ячеек»​ правиле.​ помощи стрелок переместите​Изменяем цвет ячейки на​

​.​​ тянем курсор вниз.​​ под названием​ в этом случае​
​«=»​В строке «Формат…» пишем​ выделить все три​ автоматически:​
​ цикл.​ как можно вынести​
​ со значениями других​ этого второго варианта​
​ на вкладке​ на вкладке «Заливка»​
​Например, мы можем отметить​
​ его вверх списка.​
​ основании значения другой​Оператор выводит результат –​Как видим, программа произвела​«Главная»​ списки должны располагаться​
​. Затем кликаем по​ такую формулу. =$А2<>$В2.​
​ столбца с данными​Удалим ранее созданное условное​
​Что думаете?​ числовые значения в​
​ строк.​ в том, что​
​Главная - Условное форматирование​
​ выберите оранжевый цвет.​​ заказы, ожидаемые в​ Должно получиться вот​ ячейки​ число​ вычисление совпадений, сравнив​
​. Делаем щелчок по​ рядом друг с​ первому наименованию, которое​ Этой формулой мы​ и создать новое​

​ форматирование: «ГЛАВНАЯ»-«Условное форматирование»-«Удалить​​KSV​ отдельную переменную. Допустим,​n — индекс​ после выделения строк,​ — Правила выделения​ На всех открытых​
​ течение 1 и​ так:​

​Изменяем цвет строки по​​3​​ каждую ячейку первой​​ кнопке​ другом на одном​ нужно сравнить в​ говорим Excel, что​ правило форматирования, аналогичное​ правила»-«Удалить правила со​: Ускорить можно несколькими​
​ чтобы в самом​

​ строки, с которой​​ происходил дальнейшая сортировка​​ ячеек — Повторяющиеся​​ окнах жмем ОК.​ 3 дней, розовым​Нажмите​ нескольким условиям​. Именно оно наименьшее​
​ таблицы с данными,​«Условное форматирование»​
​ листе.​ первом списке. Опять​ если данные в​ Способу 2. А​
​ всего листа».​ способами или даже​ верху макросы было​
​ сравниваем.​ ,после которой не​ значения (Home -​Мы видим, что получили​ цветом, а те,​ОК​Предположим, у нас есть​ из нумерации несовпадающих​ которые расположены во​
​. В активировавшемся списке​Выделяем сравниваемые массивы. Переходим​ ставим символ​ ячейках столбца А​ именно:​
​Выделите диапазон ячеек A2:D8​ совокупностью этих нескольких​ а=30, в=20 и​
​теперь посмотрите внимательно​
​ понять, какие значения​ Conditional formatting -​

​ не совсем ожидаемый​​ которые будут выполнены​
​, и строки в​
​ вот такая таблица​ строк табличных массивов.​
​ втором табличном диапазоне.​
​ выбираем позицию​ во вкладку​
​«=»​
​ не равны данным​
​в Excel 2003 и​
​ и выберите инструмент:​​ способов, но я​ т.п. Изменяя их​
​ код — мы​ с какими совпали​
​ Highlight cell rules​ результат, так как​
​ в течение 5​ указанном фрагменте тут​ заказов компании:​
​ С помощью маркера​ В четырех случаях​
​«Правила выделения ячеек»​«Главная»​с клавиатуры. Далее​ в ячейках столбца​ старше — выбрать​
​ «ГЛАВНАЯ»-«Условное форматирование»-«Создать правило».​ практически уверен, что​ менялись бы параметры​
​ НЕ выходим из​
​ до того. Тут​ — Duplicate Values)​ созданное новое правило​
​ и 7 дней,​ же изменят цвет,​

​Мы хотим раскрасить различными​​ заполнения копируем формулу​​ результат вышел​​. В следующем меню​. Далее щелкаем по​ кликаем по первой​ В, то окрасить​ в меню​В разделе данного окна​ для ваших 2​
​ фильтра.​ внутреннего цикла, после​ нужно либо каждой​
​:​ всегда имеет высший​ жёлтым цветом. Формулы​ в соответствии с​ цветами строки в​ до самого низа.​
​«1»​ делаем выбор позиции​
​ значку​ ячейке колонки, которую​ эти ячейки в​Формат - Условное форматирование​ «Выберите тип правила:»​ тыс. строк (всего​И четвертый )​ совпадения условий, а​ паре(или более) совпавших​
​Если выбрать опцию​ приоритет по сравнению​ будут выглядеть так:​ формулами в обоих​ зависимости от заказанного​Теперь, зная номера строк​, а в двух​«Повторяющиеся значения»​«Найти и выделить»​ мы сравниваем, во​
​ красный свет.​

​ — Формула (Format​​ выберите опцию «Использовать​ лишь) будет достаточно​ Возможно ли осуществлять​ только окрашиваем найденную​ строк давать свой​Повторяющиеся​ со старыми правилами​=ИЛИ($F2=»Due in 1 Days»;$F2=»Due​ правилах.​ количества товара (значение​ несовпадающих элементов, мы​ случаях –​.​, который располагается на​ второй таблице. Получилось​Как работать с​ — Conditional Formatting​ формулу для определения​ и самого простого​ перед фильтр поиск​ строку, И ПРОДОЛЖАЕМ​ индивидуальный цвет, либо​, то Excel выделит​ условного форматирования в​ in 3 Days»)​Чтобы упростить контроль выполнения​ в столбце​ можем вставить в​«0»​Запускается окно настройки выделения​ ленте в блоке​ выражение следующего типа:​ условным форматированием, как​ — Formula)​ форматированных ячеек».​ способа, который я​ текстового значения. Допустим,​ искать другие строки,​ после выделения отсортировывать​ цветом совпадения в​ Excel. Необходимо снизить​=OR($F2=»Due in 1 Days»,$F2=»Due​ заказа, мы можем​Qty.​ ячейку и их​. То есть, программа​ повторяющихся значений. Если​ инструментов​=A2=D2​ настроить цвет заливки,​в Excel 2007 и​В поле ввода введите​ вам предлагал, начиная​ если в нашей​ совпадающие по условию.​ их по цвету​ наших списках, если​ приоритет для нового​ in 3 Days»)​ выделить в нашей​), чтобы выделить самые​ значения с помощью​ не смогла отыскать​ вы все сделали​«Редактирование»​Хотя, конечно, в каждом​ шрифта в условном​ новее — нажать​ формулу: =$C2​ с моего самого​ ячейке нет слова​ А значит, если​ в вверх/вниз таблицы​ опцию​
​ правила. Чтобы проанализировать​=ИЛИ($F2=»Due in 5 Days»;$F2=»Due​ таблице различными цветами​ важные заказы. Справиться​ функции​ во второй таблице​ правильно, то в​
​. Открывается список, в​ конкретном случае координаты​ форматировании, как написать​ на вкладке​Щелкните по кнопке «Формат»​ первого ответа в​ StopLoss, то мы​ совпадающих по условиям​ и более не​
​Уникальные​ данную особенность наглядно​ in 7 Days")​

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

​ строки заказов с​ с этой задачей​ИНДЕКС​ два значения, которые​ данном окне остается​ котором следует выбрать​ будут отличаться, но​ другие условия для​

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

​Главная (Home)​ и в паявшемся​ этой теме (посмотрите​ и не фильтруем.​ строк будет 10,​ учитывать при дальнейшей​- различия.​

​ и настроить соответствующим​=OR($F2=»Due in 5 Days»,$F2=»Due​ разным статусом доставки,​

1. ​ нам поможет инструмент​. Выделяем первый элемент​
2. ​ имеются в первом​ только нажать на​ позицию​
3. ​ суть останется одинаковой.​ выделения ячеек, строк,​кнопку​ окне перейдите на​ любой из моих​KSV​ то во внутреннем​ сортировке (первый был​
4. ​Цветовое выделение, однако, не​ образом необходимо выбрать​ in 7 Days»)​ информация о котором​ Excel – «​
5. ​ листа, содержащий формулу​ табличном массиве.​ кнопку​«Выделение группы ячеек…»​Щелкаем по клавише​ т.д., читайте в​Условное форматирование — Создать​ вкладку «Шрифт», в​

​ вложенных файлов, например​:​ цикле мы 9​ бы более наглядный).​ всегда удобно, особенно​ инструмент: ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Управление​Для того, чтобы выделить​ содержится в столбце​Условное форматирование​НАИМЕНЬШИЙ​

​Конечно, данное выражение для​«OK»​.​Enter​

​ статье «Условное форматирование​ правило (Conditional Formatting​ разделе «Цвет:» выберите​

​ здесь, в процедуре​200?’200px’:»+(this.scrollHeight+5)+’px’);»>’ поменяйте вот эту​ раз попадем на​Строк в сумме​ для больших таблиц.​ правилами».​

​ заказы с количеством​

​Delivery​».​. После этого переходим​ того, чтобы сравнить​. Хотя при желании​

​Кроме того, в нужное​, чтобы получить результаты​ в Excel». Получилось​ — New Rule)​ красный. После на​

​ Sample1, к тому​

Как выделить строку с отрицательным значением в Excel

​ часть кода:​ окрашивание строк (при​ не более 2000.​ Также, если внутри​Выберите новое оранжевое правило​ товара не менее​:​Первым делом, выделим все​ в строку формул​ табличные показатели, можно​ в соответствующем поле​ нам окно выделения​ сравнения. Как видим,​ так.​

​и выбрать тип​ всех открытых окнах​ же там каждая​

1. ​If (Abs(v(i, 1)​ 9-ти разных значениях​ Вам виднее, сколько​ самих списков элементы​
2. ​ в появившемся окне​ 5, но не​Если срок доставки заказа​
3. ​ ячейки, цвет заливки​ и перед наименованием​ применять и в​ данного окошка можно​ группы ячеек можно​
4. ​ при сравнении первых​Третий способ.​
5. ​ правила​ нажмите «ОК».​ строка прокомментирована) -​ — v(n, 1))​ n), при одном​ времени на сортировку​ могут повторяться, то​ «Диспетчер правил условного​

​ более 10 (значение​ находится в будущем​ которых мы хотим​«НАИМЕНЬШИЙ»​

​ существующем виде, но​ выбрать другой цвет​ попасть и другим​ ячеек обоих списков​Сравнить значения столбцов в​Использовать формулу для опеределения​Результат действия формулы с​ там весь нужный​ .Rows(i).Interior.Color = c:​ и том же​ такого количества понадобится,​

​ этот способ не​ форматирования» и нажмите​ в столбце​ (значение​ изменить.​дописываем название​ есть возможность его​ выделения.​ способом. Данный вариант​ программа указала показатель​Excel формулой.​ форматируемых ячеек (Use​ условным форматированием, которая​ диапазон сначала считывается​ _​ значении i.​ возможно первый вариант​ подойдет.​ на кнопку «Вниз»​Qty.​

​Due in X Days​Чтобы создать новое правило​«ИНДЕКС»​ усовершенствовать.​

​После того, как мы​ особенно будет полезен​«ИСТИНА»​Можно сделать в​ a formula to​ сделала таблицу еще​ в массив, а​.Rows(n).Interior.Color = c:​Т.е., все именно​ и выход.​В качестве альтернативы можно​ (CTRL+стрелка вниз), как​), запишем формулу с​), то заливка таких​ форматирования, нажимаем​без кавычек, тут​Сделаем так, чтобы те​ произведем указанное действие,​ тем пользователям, у​, что означает совпадение​ таблице дополнительный столбец​ determine which cell​ более читабельной.​ потом в цикле​ _​ так, как вы​А по поводу​ использовать функцию​ показано на рисунке:​ функцией​ ячеек должна быть​Главная​

exceltable.com

Выделение дубликатов цветом

​ же открываем скобку​ значения, которые имеются​ все повторяющиеся элементы​ которых установлена версия​ данных.​ и установить в​ to format)​Примечание. Таким же самым​ сравниваются уже значения​.Rows(i).Font.Bold = True:​ и хотели ранее.​ Split, как не​

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

​СЧЁТЕСЛИ​Как видите последовательность правил​И​ оранжевой;​

​>​ и ставим точку​ во второй таблице,​​ будут выделены выбранным​​ программы ранее Excel​​Теперь нам нужно провести​​ ячейках этого столбца​​Затем ввести формулу проверки​ образом можно присвоить​ из этого массива,​ _​​Цитата​

​ пытался делать по​(COUNTIF)​ очень важна если​(AND):​

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

​Если заказ доставлен (значение​Условное форматирование​ с запятой (​ но отсутствуют в​ цветом. Те элементы,​ 2007, так как​ аналогичную операцию и​​ формулу. =А2=В2​ ​ количества совпадений и​​ диапазону A2:D8 новое​​ а не обращается​.Rows(n).Font.Bold = True:​​rever27, 06.06.2015 в​​ вашему предыдущему решению,​из категории​

​ их много присвоено​

​=И($D2>=5;$D2​Delivered​

​>​;​ первой, выводились отдельным​ которые не совпадают,​ метод через кнопку​ с остальными ячейками​Получится так.​ задать цвет с​ правило для выделения​ к каждой ячейке​ _​​ 00:06, в сообщении​​ так и не​​Статистические​​ для одного и​=AND($D2>=5,$D2​​), то заливка таких​​Создать правило​

Способ 3. Если много столбцов

​). Затем выделяем в​ списком.​ останутся окрашенными в​«Найти и выделить»​ обеих таблиц в​Можно условным форматированием окрасить​ помощью кнопки​ строк на против​ листа отдельно, как​

​f = 1​ № 11200?’200px’:»+(this.scrollHeight+5)+’px’);»>Если У​ получилось включить его​, которая подсчитывает сколько​ того же диапазона​Конечно же, в своих​ ячеек должна быть​

​(Home > Conditional​ строке формул наименование​Прежде всего, немного переработаем​ свой изначальный цвет​эти приложения не​ тех колонках, которые​ слова «Ложь» другим​Формат (Format)​

​ ячеек с положительным​ у вас. Это​’ на эту:​ меня 10 строчек,​ в макрос (​ раз каждый элемент​ ячеек:​ формулах Вы можете​ зелёной;​ Formatting > New​«ИНДЕКС»​

• ​ нашу формулу​ (по умолчанию белый).​ поддерживают. Выделяем массивы,​​ мы сравниваем. Но​ цветом или окрасить​- все, как​ значением другим цветом.​
• ​ увеличивает скорость в​If (Abs(v(i, 1)​ которые все схожи​​rever27​​ из второго списка​​На первый взгляд может​ использовать не обязательно​Если срок доставки заказа​​ rule).​и кликаем по​​СЧЁТЕСЛИ​ Таким образом, можно​ которые желаем сравнить,​ можно просто провести​ эти ячейки.​

​ в Способе 2:​ Только нужно указать​ разы, если не​ — v(n, 1))​​ между собой(с минимальной​​:​ встречался в первом:​

planetaexcel.ru

​ показаться что несколько​

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

Читайте также: Сравнение двух документов в MS Word

Способы сравнения

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

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

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

Способ 1: простая формула

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

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

1. Для этого нам понадобится дополнительный столбец на листе. Вписываем туда знак «=». Затем кликаем по первому наименованию, которое нужно сравнить в первом списке. Опять ставим символ «=» с клавиатуры. Далее кликаем по первой ячейке колонки, которую мы сравниваем, во второй таблице. Получилось выражение следующего типа:

   =A2=D2

   

   Хотя, конечно, в каждом конкретном случае координаты будут отличаться, но суть останется одинаковой.

2. Щелкаем по клавише Enter, чтобы получить результаты сравнения. Как видим, при сравнении первых ячеек обоих списков программа указала показатель «ИСТИНА», что означает совпадение данных.



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

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



4. Как видим, теперь в дополнительном столбце отобразились все результаты сравнения данных в двух колонках табличных массивов. В нашем случае не совпали данные только в одной строке. При их сравнении формула выдала результат «ЛОЖЬ». По всем остальным строчкам, как видим, формула сравнения выдала показатель «ИСТИНА».



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





6. В окне Мастера функций в группе операторов «Математические» выделяем наименование СУММПРОИЗВ. Щелкаем по кнопке «OK».



7. Активируется окно аргументов функции СУММПРОИЗВ, главной задачей которой является вычисление суммы произведений выделенного диапазона. Но данную функцию можно использовать и для наших целей. Синтаксис у неё довольно простой:

   =СУММПРОИЗВ(массив1;массив2;…)

   Всего в качестве аргументов можно использовать адреса до 255 массивов. Но в нашем случае мы будем использовать всего два массива, к тому же, как один аргумент.

   Ставим курсор в поле «Массив1» и выделяем на листе сравниваемый диапазон данных в первой области. После этого в поле ставим знак «не равно» (<>) и выделяем сравниваемый диапазон второй области. Далее обворачиваем полученное выражение скобками, перед которыми ставим два знака «-». В нашем случае получилось такое выражение:

   --(A2:A7<>D2:D7)

   Щелкаем по кнопке «OK».



8. Оператор производит расчет и выводит результат. Как видим, в нашем случае результат равен числу «1», то есть, это означает, что в сравниваемых списках было найдено одно несовпадение. Если бы списки были полностью идентичными, то результат бы был равен числу «0».

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

=B2=Лист2!B2

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

Способ 2: выделение групп ячеек

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

1. Выделяем сравниваемые массивы. Переходим во вкладку «Главная». Далее щелкаем по значку «Найти и выделить», который располагается на ленте в блоке инструментов «Редактирование». Открывается список, в котором следует выбрать позицию «Выделение группы ячеек…».
   

   Кроме того, в нужное нам окно выделения группы ячеек можно попасть и другим способом. Данный вариант особенно будет полезен тем пользователям, у которых установлена версия программы ранее Excel 2007, так как метод через кнопку «Найти и выделить» эти приложения не поддерживают. Выделяем массивы, которые желаем сравнить, и жмем на клавишу F5.

2. Активируется небольшое окошко перехода. Щелкаем по кнопке «Выделить…» в его нижнем левом углу.



3. После этого, какой бы из двух вышеперечисленных вариантов вы не избрали, запускается окно выделения групп ячеек. Устанавливаем переключатель в позицию «Выделить по строкам». Жмем по кнопке «OK».



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

Способ 3: условное форматирование

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

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



2. Активируется окошко диспетчера правил. Жмем в нем на кнопку «Создать правило».



3. В запустившемся окне производим выбор позиции «Использовать формулу». В поле «Форматировать ячейки» записываем формулу, содержащую адреса первых ячеек диапазонов сравниваемых столбцов, разделенные знаком «не равно» (<>). Только перед данным выражением на этот раз будет стоять знак «=». Кроме того, ко всем к координатам столбцов в данной формуле нужно применить абсолютную адресацию. Для этого выделяем формулу курсором и трижды жмем на клавишу F4. Как видим, около всех адресов столбцов появился знак доллара, что и означает превращение ссылок в абсолютные. Для нашего конкретного случая формула примет следующий вид:

   =$A2<>$D2

   Данное выражение мы и записываем в вышеуказанное поле. После этого щёлкаем по кнопке «Формат…».



4. Активируется окно «Формат ячеек». Идем во вкладку «Заливка». Тут в перечне цветов останавливаем выбор на цвете, которым хотим окрашивать те элементы, где данные не будут совпадать. Жмем на кнопку «OK».



5. Вернувшись в окно создания правила форматирования, жмем на кнопку «OK».



6. После автоматического перемещения в окно «Диспетчера правил» щелкаем по кнопке «OK» и в нем.



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

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

1. Производим выделение областей, которые нужно сравнить.



2. Выполняем переход во вкладку под названием «Главная». Делаем щелчок по кнопке «Условное форматирование». В активировавшемся списке выбираем позицию «Правила выделения ячеек». В следующем меню делаем выбор позиции «Повторяющиеся значения».



3. Запускается окно настройки выделения повторяющихся значений. Если вы все сделали правильно, то в данном окне остается только нажать на кнопку «OK». Хотя при желании в соответствующем поле данного окошка можно выбрать другой цвет выделения.



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

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

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

Урок: Условное форматирование в Экселе

Способ 4: комплексная формула

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

Оператор СЧЁТЕСЛИ относится к статистической группе функций. Его задачей является подсчет количества ячеек, значения в которых удовлетворяют заданному условию. Синтаксис данного оператора имеет такой вид:

=СЧЁТЕСЛИ(диапазон;критерий)

Аргумент «Диапазон» представляет собой адрес массива, в котором производится подсчет совпадающих значений.

Аргумент «Критерий» задает условие совпадения. В нашем случае он будет представлять собой координаты конкретных ячеек первой табличной области.

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



2. Происходит запуск Мастера функций. Переходим в категорию «Статистические». Находим в перечне наименование «СЧЁТЕСЛИ». После его выделения щелкаем по кнопке «OK».



3. Происходит запуск окна аргументов оператора СЧЁТЕСЛИ. Как видим, наименования полей в этом окне соответствуют названиям аргументов.

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

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

   Затем переходим к полю «Критерий», установив туда курсор. Щелкаем по первому элементу с фамилиями в первом табличном диапазоне. В данном случае оставляем ссылку относительной. После того, как она отобразилась в поле, можно щелкать по кнопке «OK».



4. В элемент листа выводится результат. Он равен числу «1». Это означает, что в перечне имен второй таблицы фамилия «Гринев В. П.», которая является первой в списке первого табличного массива, встречается один раз.



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



6. Как видим, программа произвела вычисление совпадений, сравнив каждую ячейку первой таблицы с данными, которые расположены во втором табличном диапазоне. В четырех случаях результат вышел «1», а в двух случаях – «0». То есть, программа не смогла отыскать во второй таблице два значения, которые имеются в первом табличном массиве.

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

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

1. Прежде всего, немного переработаем нашу формулу СЧЁТЕСЛИ, а именно сделаем её одним из аргументов оператора ЕСЛИ. Для этого выделяем первую ячейку, в которой расположен оператор СЧЁТЕСЛИ. В строке формул перед ней дописываем выражение «ЕСЛИ» без кавычек и открываем скобку. Далее, чтобы нам легче было работать, выделяем в строке формул значение «ЕСЛИ» и жмем по иконке «Вставить функцию».



2. Открывается окно аргументов функции ЕСЛИ. Как видим, первое поле окна уже заполнено значением оператора СЧЁТЕСЛИ. Но нам нужно дописать кое-что ещё в это поле. Устанавливаем туда курсор и к уже существующему выражению дописываем «=0» без кавычек.

   После этого переходим к полю «Значение если истина». Тут мы воспользуемся ещё одной вложенной функцией – СТРОКА. Вписываем слово «СТРОКА» без кавычек, далее открываем скобки и указываем координаты первой ячейки с фамилией во второй таблице, после чего закрываем скобки. Конкретно в нашем случае в поле «Значение если истина» получилось следующее выражение:

   СТРОКА(D2)

   Теперь оператор СТРОКА будет сообщать функции ЕСЛИ номер строки, в которой расположена конкретная фамилия, и в случае, когда условие, заданное в первом поле, будет выполняться, функция ЕСЛИ будет выводить этот номер в ячейку. Жмем на кнопку «OK».



3. Как видим, первый результат отображается, как «ЛОЖЬ». Это означает, что значение не удовлетворяет условиям оператора ЕСЛИ. То есть, первая фамилия присутствует в обоих списках.



4. С помощью маркера заполнения, уже привычным способом копируем выражение оператора ЕСЛИ на весь столбец. Как видим, по двум позициям, которые присутствуют во второй таблице, но отсутствуют в первой, формула выдает номера строк.



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



6. После этого выделяем первую ячейку справа от колонки с номерами и щелкаем по значку «Вставить функцию».



7. Открывается Мастер функций. Переходим в категорию «Статистические» и производим выбор наименования «НАИМЕНЬШИЙ». Щелкаем по кнопке «OK».



8. Функция НАИМЕНЬШИЙ, окно аргументов которой было раскрыто, предназначена для вывода указанного по счету наименьшего значения.

   В поле «Массив» следует указать координаты диапазона дополнительного столбца «Количество совпадений», который мы ранее преобразовали с помощью функции ЕСЛИ. Делаем все ссылки абсолютными.

   В поле «K» указывается, какое по счету наименьшее значение нужно вывести. Тут указываем координаты первой ячейки столбца с нумерацией, который мы недавно добавили. Адрес оставляем относительным. Щелкаем по кнопке «OK».



9. Оператор выводит результат – число 3. Именно оно наименьшее из нумерации несовпадающих строк табличных массивов. С помощью маркера заполнения копируем формулу до самого низа.



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



11. После этого открывается небольшое окошко, в котором нужно определить, ссылочный вид должна иметь функция ИНДЕКС или предназначенный для работы с массивами. Нам нужен второй вариант. Он установлен по умолчанию, так что в данном окошке просто щелкаем по кнопке «OK».



12. Запускается окно аргументов функции ИНДЕКС. Данный оператор предназначен для вывода значения, которое расположено в определенном массиве в указанной строке.

    Как видим, поле «Номер строки» уже заполнено значениями функции НАИМЕНЬШИЙ. От уже существующего там значения следует отнять разность между нумерацией листа Excel и внутренней нумерацией табличной области. Как видим, над табличными значениями у нас только шапка. Это значит, что разница составляет одну строку. Поэтому дописываем в поле «Номер строки» значение «-1» без кавычек.

    В поле «Массив» указываем адрес диапазона значений второй таблицы. При этом все координаты делаем абсолютными, то есть, ставим перед ними знак доллара уже ранее описанным нами способом.

    Жмем на кнопку «OK».



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

Способ 5: сравнение массивов в разных книгах

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

Урок: Как открыть Эксель в разных окнах

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

Выбирая инструменты на закладке: «ГЛАВНАЯ» в разделе «Стили» из выпадающего меню «Условное форматирование» нам доступна целая группа «Правила отбора первых и последних значений». Однако часто необходимо сравнить и выделить цветом ячейки в Excel, но ни один из вариантов готовых решений не соответствует нашим условиям. Например, в конструкции условия мы хотим использовать больше критериев или выполнять более сложные вычисления. Всегда можно выбрать последнюю опцию «Другие правила» она же является опцией «Создать правило». Условное форматирование позволяет использовать формулу для создания сложных критериев сравнения и отбора значений. Создавая свои пользовательские правила для условного форматирования с использованием различных формул мы себя ничем не ограничиваем.

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

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

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

1. Выделите диапазон ячеек D2:D12 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».



2. В появившемся окне «Создание правила форматирования» выберите опцию «Использовать формулу для определения форматированных ячеек».



3. В поле ввода введите формулу:



4. Нажмите на кнопку «Формат» и в появившемся окне «Формат ячеек» на вкладке «Заливка» выберите красный цвет для данного правила, а на вкладке «Шрифт» – белый цвет. После на всех открытых окнах жмем ОК.

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



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

Теперь оранжевым цветом выделим те суммы магазинов, которые в текущем году меньше чем в прошлом и с отрицательной прибылью. Создадим второе правило для этого же диапазона D2:D12:

1. Не снимая выделения с диапазона D2:D12 снова выберите инструмент «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».
2. Так же в появившемся окне «Создание правила форматирования» выберите опцию «Использовать формулу для определения форматированных ячеек».
3. В поле ввода введите формулу:



4. Нажмите на кнопку «Формат» и в появившемся окне «Формат ячеек» на вкладке «Заливка» выберите оранжевый цвет. На всех открытых окнах жмем ОК.

Мы видим, что получили не совсем ожидаемый результат, так как созданное новое правило всегда имеет высший приоритет по сравнению со старыми правилами условного форматирования в Excel. Необходимо снизить приоритет для нового правила. Чтобы проанализировать данную особенность наглядно и настроить соответствующим образом необходимо выбрать инструмент: ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Управление правилами».

Выберите новое оранжевое правило в появившемся окне «Диспетчер правил условного форматирования» и нажмите на кнопку «Вниз» (CTRL+стрелка вниз), как показано на рисунке:

Как видите последовательность правил очень важна если их много присвоено для одного и того же диапазона ячеек:

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

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

Этим ячейкам будет присвоен зеленый цвет и жмем везде ОК.

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