Добрый день!
Эта статья посвящена решению такого вопроса, как сравнить две таблицы в Excel, ну или, по крайней мере, два столбца. Да, работать с таблицами удобно и хорошо, но вот когда нужно их сравнение, визуально это сделать достаточно затруднительно. Быть может таблицу до десятка или двух, вы и сможете визуально отсортировать, но вот когда они будут превышать тысячи, тут уже вам будет необходимо дополнительные инструменты анализа.
Увы, нет магической палочки, с помощью которой в один клик всё сделается и информация будет проверена, необходимо и подготовить данные, и прописать формулы, и иные процедуры позволяющие сравнить вашитаблицы.
Рассмотрим несколько вариантов и возможностей для сравнения таблиц в Excel:
- простой способ, как сравнить две таблицы в Excel;
- быстрое выделение значений, которые отличаются;
- сравнить две таблицы в Excel с помощью условного форматирования;
- сравнить две таблицы в Excel с помощью функции СЧЁТЕСЛИ и правил;
- как сравнить две таблицы в Excel с помощью функции ВПР;
- как сравнить две таблицы в Excel с помощью функции ЕСЛИ;
- сравнить две таблицы с помощью макроса VBA;
- сравнить с помощью надстройки Inquire.
Простой способ, как сравнить две таблицы в Excel
Это самые простой и элементарный способ сравнения двух таблиц. Сравнивать таким способом возможно, как числовые значение, так и текстовые. Для примера сравним два диапазона числовых значений, всего на всего прописав в соседней ячейке формулу их равенства =C2=E2, как результат при равенстве ячеек мы получим ответ «ИСТИНА», а если совпадений нет, будет «ЛОЖЬ». Теперь простым авто копированием копируем на весь диапазон нашу формулу позволяющую сравнить два столбика в Excel и видим разницу.
Сравнение 2-х списков в MS EXCEL
действительно больших таблицахфункцию
ячеек — Повторяющиеся т.е. после ввода =0 (ЛОЖЬ в оранжевым цветом)
Задача
3. Списки считаются
Можно цветомСравнить данные в нескольких
разницу цветом шрифта, и «Значение_если_ложь» поместите макроса?. на вкладке Главная несколько условий проверкиЗакрыть и загрузить -Минусы (>100 тыс. строк)
Быстрое выделение значений, которые отличаются
Это также не очень обременительный способ. Если вам просто нужно найти и удостовериться в наличии, ну или отсутствии отличий между таблицами, вам нужно на вкладке «Главная», выбрать кнопку меню «Найти и выделить», предварительно выделив диапазон где надо сравнить две таблицы в Excel. В открывшимся меню выберите пункт «Выделить группу ячеек…» и в появившемся диалоговом окне выберите «отличия по строкам».
Сравнить две таблицы в Excel с помощью условного форматирования
Очень хороший способ, при котором вы сможете видеть выделенным цветом значение, которые при сличении двух таблиц отличаются. Применить условное форматирование вы можете на вкладке «Главная», нажав кнопку «Условное форматирование» и в предоставленном списке выбираем «Управление правилами».
В диалоговом окне «Диспетчер правил условного форматирования», жмем кнопочку «Создать правило» и в новом диалоговом окне «Создание правила форматирования», выбираем правило «Использовать формулу для определения форматируемых ячеек». В поле «Изменить описание правила» вводим формулу =$C2<>$E2 для определения ячейки, которое нужно форматировать, и нажимаем кнопку «Формат».
Определяем стиль того, как будет форматироваться наше значение, которое соответствует критерию.
Теперь в списке правил появилось наше ново сотворённое правило, вы его выбираете, нажимаете «Ок».
И все правило применилось к нашему диапазону, где мы пытаемся проверить на похожесть две таблицы, и стало видны отличия, к которым применилось условное форматирование.
Как сравнить две таблицы в Excel с помощью функции СЧЁТЕСЛИ и правил
Все вышеперечисленные способы хороши для упорядоченных таблиц, а вот когда данные, не упорядоченные необходимы иные способы один из которых мы сейчас и рассмотрим. Представим, к примеру, у нас есть 2 таблицы, значения в которых немного отличаются и нам необходимо сравнить эти таблицы для определения значения, которое отличается. Выделяем значение в диапазоне первой таблицы и на вкладке «Главная», пункт меню «Условное форматирование» и в списке жмем пункт «Создать правило…», выбираем правило «Использовать формулу для определения форматируемых ячеек», вписываем формулу =СЧЁТЕСЛИ($C$1:$C$7;C1)=0 и выбираем формат условного форматирования.
Формула проверяет значение из определенной ячейки C1 и сравнивает ее с указанным диапазоном $C$1:$C$7 из второго столбика. Копируем правило на весь диапазон, в котором мы сравниваем таблицы и получаем выделенные цветом ячейки значения, которых не повторяется.
Сравнение двух таблиц
differences) или с использованием, результат получим вD сравнив их с
не равны данным функцию «Выделение группы из магазинов. Как можно сделать, например, функций» раскройте выпадающий совпадают по данным. изменятся цены и вкладке уже встроена по поле
знакомы с этой этот способ не. В последних версиях очевидных формул в виде цвета заголовкаединый список уникальных данными второго столбца.
- в ячейках столбца ячеек», ставим галочку
перенести данные из протянув вниз правый список «Текстовые» иЧтобы не просматривать т.д.), то достаточноДанные — Получить данные умолчанию на вкладке - Прайс замечательной функцией, то подойдет. Excel 2007/2010 можно дополнительном столбце. исходных списков (полностью
- значений для обоихВ дополнительном столбце
В, то окрасить у слов «Отличия
Способ 1. Сравнение таблиц функцией ВПР (VLOOKUP)
разных таблиц в нижний угол ячейки выберите в нем весь столбец, все будет лишь обновить — Объединить запросыДанные (Data),в область столбцов загляните сначала сюда
В качестве альтернативы можно также воспользоваться кнопкойСОВЕТ: совпадающие списки имеют списков (см. статью устанавливаем формулы, они эти ячейки в по строкам». Нажимаем одну, читайте в с формулой -
строку «СОВПАД». В ли там ИСТИНА, наши запросы сочетанием — Объединить (Dataа для Excel и поле и почитайте или
использовать функцию
Найти и выделить (FindБолее подробное сравнение заголовки зеленого цвета; Отбор уникальных значений разные с разными
красный свет.
«ОК». статье «Как связать при наведении на результате на экране можно в С11 клавиш Ctrl+Alt+F5 или — Get Data 2010-2013 ее нужноЦ посмотрите видеоурок поСЧЁТЕСЛИ & Select) - 2-х списков описано частично совпадающие - из двух диапазонов) условиями. Или вКак работать с
Способ 2. Сравнение таблиц с помощью сводной
Здесь расхождение дат в таблицы в Excel» него указателя мыши появится форма из вставить формулу кнопкой — Merge Queries
отдельно скачать сена ней — сэкономите(COUNTIF) Выделение группы ячеек в статье Сравнение
желтого; не совпадающие
с помощью формулы
формуле указываем конкретные условным форматированием, как
ячейках строк второй
тут. курсор становится черным
двух полей «Текст1»=И (C1:C10)
Обновить все (Refresh All)
— Merge) сайта Microsoft ив область значений: себе пару летиз категории (Go to Special) 2-х списков. — красного). Цвет массива: данные, слова, которые настроить цвет заливки, и третьей.Первый способ. плюсом. Затем выделите
и «Текст2», вТаким образом сравниваютна вкладкеили нажмем кнопку установить — получитеКак видите, сводная таблица жизни.Статистическиена вкладкеТиповая задача, возникающая периодически
заголовков списков определяется
=ЕСЛИОШИБКА(ЕСЛИОШИБКА( нужно посчитать в шрифта в условномЕсть еще одинКак сравнить два столбца третью колонку и каждое из них таблицы, в которых
Данные (Data)
Объединить (Merge)
новую вкладку автоматически сформирует общийОбычно эту функцию используют, которая подсчитывает сколько
Главная (Home)
перед каждым пользователем Условным форматированием.ИНДЕКС(Список1;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список1);0)); столбце. форматировании, как написать способ в на вкладке «Главная»
Способ 3. Сравнение таблиц с помощью Power Query
поместите адрес одной одинаковые данные расположены.на вкладкеPower Query список всех товаров для подтягивания данных раз каждый элементExcel выделит ячейки, отличающиеся Excel — сравнитьПусть дано 2 спискаИНДЕКС(Список2;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список2);0)));Подробнее смотрите такие
другие условия длясравнить даты в ExcelExcel на совпадения. раскройте список «Условное из сравниваемых ячеек не обязательно в
Плюсы
Power Query
. из старого и из одной таблицы из второго списка содержанием (по строкам). между собой два ЧИСЛОвых значений. Определим,»») формулы в статье выделения ячеек, строк,- сравнить периодыВыделяем столбцы (у форматирование» в группе
(B3 и C3). тех же строках.: Пожалуй, самый красивый
.
Перед загрузкой наших прайс-листов
нового прайс-листов (без
в другую по
встречался в первом:
Затем их можно
диапазона с данными совпадают ли этиПодсчитаем количество вхождений каждого «Функция «СЧЕТЕСЛИ» в
т.д., читайте в
дат,есть ли в
нас столбцы А
команд «Стили». В
Затем щелкните поAbram pupkin и удобный способВ окне объединения выберем в Power Query
повторений!) и отсортирует совпадению какого-либо общегоПолученный в результате ноль обработать, например: и найти различия
2 списка (критерий
уникального значения в Excel». статье «Условное форматирование указанных периодах одинаковые
и В). На
разделе «Правила выделения кнопке OK и
: аналогичные вопросы из всех. Шустро в выпадающих списках их необходимо преобразовать продукты по алфавиту. параметра. В данном и говорит обзалить цветом или как-то между ними. Способ сравнения: попарное совпадение оба списка сЭтот способ сравнения в Excel». Получилось
даты и сколько
закладке «Главная» нажимаем
ячеек» выберите строку
увидите результат сравнения
https://otvet.mail.ru/question/87383103
работает с большими наши таблицы, выделим сначала в умные Хорошо видно добавленные случае, мы применим отличиях. еще визуально отформатировать решения, в данном элементов).
помощью формул =СЧЁТЕСЛИ(Список1;D5)
можно применить при так.
дней в периодах на кнопку функции «Равно», а потом — либо надписьhttps://otvet.mail.ru/question/87175757 таблицами. Не требует в них столбцы
Как сравнить две таблицы в Excel с помощью функции ВПР
В этом варианте мы будем использовать функцию ВПР, которая позволит нам сравнить две таблицы на предмет совпадений. Для сравнения двух столбиков, введите формулу =ВПР(C2;$D$2:$D$7;1;0) и скопируйте ее на весь сравниваемый диапазон. Эта формула последовательно начинает проверять есть ли повторы значения из столбика А в столбике В, ну и соответственно возвращает значение элемента, если оно было там найдено если же значение не найдено получаем ошибку #Н/Д.
Как сравнить две таблицы в Excel функции ЕСЛИ
Этот вариант предусматривает использования логической функции ЕСЛИ и отличие этого способа в том что для сравнения двух столбцов будет использован не весь массив целиком, а только та ее часть, которая нужна для сравнения.
Для примера, сравним два столбика А и В на рабочем листе, в соседней колонке С введем формулу: =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(C2;$E$2:$E$7;0));»»;C2) и копируем ее на весь вычисляемый диапазон. Эта формула позволяет просматривать последовательно есть ли определенные элементы из указанного столбика А в столбике В и возвращает значение, в случае если оно было найдено в столбике В.
Поиск отличий в двух списках
29361 06.11.2012
Типовая задача, возникающая периодически перед каждым пользователем Excel — сравнить между собой два диапазона с данными и найти различия между ними. Способ решения, в данном случае, определяется типом исходных данных.
Вариант 1. Синхронные списки
Если списки синхронизированы (отсортированы), то все делается весьма несложно, т.к. надо, по сути, сравнить значения в соседних ячейках каждой строки. Как самый простой вариант — используем формулу для сравнения значений, выдающую на выходе логические значения ИСТИНА (TRUE)
или
ЛОЖЬ (FALSE)
:
Число несовпадений можно посчитать формулой:
=СУММПРОИЗВ(—(A2:A20<>B2:B20))
или в английском варианте =SUMPRODUCT(—(A2:A20<>B2:B20))
Если в результате получаем ноль — списки идентичны. В противном случае — в них есть различия. Формулу надо вводить как формулу массива, т.е. после ввода формулы в ячейку жать не на Enter
, а на
Ctrl+Shift+Enter
.
Если с отличающимися ячейками надо что сделать, то подойдет другой быстрый способ: выделите оба столбца и нажмите клавишу F5, затем в открывшемся окне кнопку Выделить (Special)
—
Отличия по строкам (Row differences)
. В последних версиях Excel 2007/2010 можно также воспользоваться кнопкой
Найти и выделить (Find & Select) — Выделение группы ячеек (Go to Special) на вкладке Главная (Home)
Excel выделит ячейки, отличающиеся содержанием (по строкам). Затем их можно обработать, например:
- залить цветом или как-то еще визуально отформатировать
- очистить клавишей Delete
- заполнить сразу все одинаковым значением, введя его и нажав Ctrl+Enter
- удалить все строки с выделенными ячейками, используя команду Главная — Удалить — Удалить строки с листа (Home — Delete — Delete Rows)
- и т.д.
Вариант 2. Перемешанные списки
Если списки разного размера и не отсортированы (элементы идут в разном порядке), то придется идти другим путем.
Самое простое и быстрое решение: включить цветовое выделение отличий, используя условное форматирование. Выделите оба диапазона с данными и выберите на вкладке Главная — Условное форматирование — Правила выделения ячеек — Повторяющиеся значения (Home — Conditional formatting — Highlight cell rules — Duplicate Values):
Если выбрать опцию Повторяющиеся
, то Excel выделит цветом совпадения в наших списках, если опцию
Уникальные
— различия.
Цветовое выделение, однако, не всегда удобно, особенно для больших таблиц. Также, если внутри самих списков элементы могут повторяться, то этот способ не подойдет.
В качестве альтернативы можно использовать функцию СЧЁТЕСЛИ
(COUNTIF)
из категории
Статистические
, которая подсчитывает сколько раз каждый элемент из второго списка встречался в первом:
Полученный в результате ноль и говорит об отличиях.
И, наконец, «высший пилотаж» — можно вывести отличия отдельным списком. Для этого придется использовать формулу массива:
Выглядит страшновато, но свою работу выполняет отлично 
Ссылки по теме
- Выделение дубликатов в списке цветом
- Сравнение двух диапазонов с помощью надстройки PLEX
- Запрет ввода повторяющихся значений
Альберт
06.11.2012 23:17:08
Способ 5. Проверка вхождения элементов одного списка в другой Большое спасибо за способ!!!! Ато целый день сидел никак не мог сделать. Что только не устанавливал, оказывается всё так просто))). Ссылка
Светлана
06.11.2012 23:17:55
Помогите, пожалуйста. Использую способ 5. В одном столбце 500 текстовых значений. В другой столбец попадает текст, обработанный макросом «выпадаюший список». Сравниваю два этих солбца (СЧЁТЕСЛИ) . В них есть заведомо одинаковые ячейки с текстом , а формула выдает мне 0 ( не находит) .При этом, если вручную перебить заново значение какой-либо ячейки в первом столбце, тут же проставляет 1 (находит). Форматы ячеек в столбцах вроде бы одинаковые, а в чём дело не пойму?. Ссылка
Николай Павлов
06.11.2012 23:20:37
Дело может быть в лишних невидимых пробелах или в латинице — английскую «С» от русской «С» внешне не отличить, а для Excel это уже разные значения. Родитель Ссылка
Игорь
06.11.2012 23:19:30
А я взял да и написал свою утилиту для сравнения. Ибо обычно алгоритмы сравнения или не описаны или убоги. Только приходится из Excel копировать в текстовые файлы данные, ну да ладно. Зато можно обрабатывать просто огромные списки по меркам Excel. Недели 2-е убил на это дело… просьба администрации не пинать за ссылку. https://fobosworld.ru/programs/raznoe/68-compare Ссылка
Николай Павлов
06.11.2012 23:21:01
Пинать никто не будет, совсем даже наоборот. Спасибо, что поделились. Родитель Ссылка
Ольга
06.11.2012 23:20:02
Спасибо большое автору! Все очень понятно. Ссылка
davaispoem
23.04.2013 03:50:17
Скажите пожалуйста, а можно добавить третий список, в котором будет происходить сравнение двух первых списков, при этом первый список берется за основу, а из второго выбираться те значения, которых нет в первом и добавляются в третий список? Ну очень нужно… Ссылка
Николай Павлов
10.05.2013 22:49:13
Можно просто объединить два диапазона данных в один и удалить потом дубликаты (Данные — Удаление дубликатов). Родитель Ссылка
Юрий Смирнов
30.05.2013 13:16:06
Добрый день. У меня возник вопрос. Есть данные в трех столбцах. Необходимо их сравнить построчно и выделить ячейку с наименьшим значением цветом. При использовании формулы(=$A1=МИН($A1:$C1)) в условном форматировании для всего диапазона, выделяется вся строка. Данная формула работает, но только если выделять колонки отдельно и каждой прописывать это правило. Собственно вопрос в том, что можно ли описать данную операцию одним условием при выделении всего диапазона(A1:C10 — условно) сразу? Заранее спасибо. Ссылка
Николай Павлов
30.05.2013 14:00:04
Юрий, просто уберите доллар перед первым А1, т.е. нужно =A1=МИН($A1:$C1) Родитель Ссылка
Юрий Смирнов
30.05.2013 14:05:38
Спасибо, слона то я и не заметил. Родитель Ссылка
Юрий Смирнов
30.05.2013 17:13:47
А верна ли в моем случае формула подсчета ячеек, которые удовлетворяют этому условию(т.е. подсчет в столбце количества ячеек имеющих минимальные значения в строке)? Что-то не работает. =СЧЁТЕСЛИ(B7:B97;B7=МИН($B7:$J7)) Считаю количество минимальных цен у нескольких поставщиков. Спасибо. Родитель Ссылка
Николай Павлов
31.05.2013 16:28:22
Условие у функции СЧЕТЕСЛИ должно быть текстовой строкой. Туда нельзя вставлять логические выражения, к сожалению. Посчитайте минимум в отдельной ячейке и вставьте адрес этой ячейки в качестве второго аргумента функции СЧЁТЕСЛИ. Родитель Ссылка
Юрий Смирнов
31.05.2013 16:34:19
Благодарю за совет, попробую. Родитель Ссылка
Клара З
16.07.2013 20:20:55
Когда делаю способом1 почему-то по всей колонке выдает «ИСТИНА», даже там где должно быть «ЛОЖЬ». Кликаю по неправильной ячейке «ИСТИНА» — значение меняется на верное — «ЛОЖЬ». Как сделать так, чтобы сразу было видно где значение «ИСТИНА», а где «ЛОЖЬ»? Ссылка
Николай Павлов
21.07.2013 12:02:18
Наверняка у вас включен ручной пересчет формул. Посмотрите на вкладке Формулы — Вычисления
. Установите
Автоматически
. Родитель Ссылка
Ирина
22.07.2013 09:35:18
Подскажите пожалуйста. Необходимо сравнить два диапазона А1:С20 и Е1:G20. В диапазоне E1:G20 выделить те ячейки, которые не совпадают с диапазоном А1:C20 с помощью условного форматирования. При написании формулы =Е1=А1 все Ок, но я не могу понять какую задать формулу для всего диапазона??? Ссылка
Николай Павлов
22.07.2013 20:27:01
Выделить первый диапазон, создать правило условного форматирования с формулой и ввести =СЧЁТЕСЛИ($E$1:$G$20;A1)=0 Родитель Ссылка
Ирина
24.10.2013 17:32:47
Подскажите пож., нужно сравнить два текстовых диапазона А1:А20 и В1:В20. Текстовые значения отсортированны в разном порядке, нужно чтобы в столбце С1:С20 указывались те значения которых нет в столбце А1:А20, но есть столбце В1:В20. Формула если(еошибка(поискпоз(В1;А$1:А&20;0));»»;В1)
выводит только совпадающие значение, а нужно наоборот. Помогите пож. что не так. Ссылка
Николай Павлов
02.12.2013 20:39:12
Чем Способ 5 не подходит? Родитель Ссылка
абдул абдулов
18.12.2018 12:13:19
где этот способ 5 о котром вы все говорите:cry: Родитель Ссылка
LOLO ALL
27.11.2013 11:13:40
Добрый день, сравниваю диапазоны 5-м способом. Результаты неутешительны — 1 — там где надо и не надо, и тоже самое с 0(((( Сравниваю В2:В1683 с А2:А1530. Формула выглядит следующим образом: =СЧЁТЕСЛИ(B2:B3211;A2:A4370). Одновременно выдает ошибку «формула не охватывает смежную ячейку». А так же протаскивание формулы по всему диапазону осуществляется с автоматическим смещением ячеек в формуле на 1 шаг, а именно =СЧЁТЕСЛИ(B3:B3212;A3:A4371) и каждая последующая так же смещается, что делает затруднительным использование данного способа при больших диапазонах. Подскажите, пожалуйста, где моя ошибка. Спасибо. Ссылка
Николай Павлов
02.12.2013 20:37:59
Второй аргумент функции СЧЕТЕСЛИ — одна ячейка с критерием, а не диапазон. Посмотрите файл примера повнимательнее, пожалуйста. Родитель Ссылка
Елена
09.01.2014 12:57:32
Подскажите: есть два списка, надо выяснить какие элементы есть в первом списке, но нет во втором (исчезли), а так же какие есть во втором, но отсутствуют в первом (добавились за период). идеале- в отдельный столбец чтоб отбирались убывшие элементы, а в другой добавившиеся… Ссылка
Николай Павлов
09.01.2014 13:27:26
Елена, а чем вам Способ 5
для этого не подходит? И фильтруете потом 1 или 0, чтобы увидеть только новые или старые элементы. А если хочется, чтобы все само происходило и в отдельный список разница выгружалась, то посмотрите функцию сравнения диапазоновиз моей надстройки PLEX. Родитель Ссылка
Олег Гелор
15.05.2014 03:06:19
Подскажите пожалуйста, как решить такую задачу. Три столбца A,В и С. В столбце «А» список слов и выражений. В столбце «В» список слов и выражений больший, чем в столбце «А». Нужно в столбец «С» вписать формулу, которая будет сравнивать значени ячейки столбца «В» со всеми ячейками столбца «А». При совпадении значений в ячейках столбцов «А» и «В», в ячейке справа (столбец «С» появляется знак «*», при несовпадении ячейка справа остается пустой. Еще один момент. Нужно чтобы формула работала даже при не точном совпадении текстов ячеек в столбцах «А» и «В», т.к. слова могут иметь разные окончания, разный регистр, может быть разный порядок слов. С этой задачей отлично справляется формула вида: =ЕСЛИ(ЕОШИБКА(ПОИСК(A$1;B17));»»;»*») Но она сравнивает ячейки столбца «В» только с одной ячейкой столбца «А», а вот как сделать, чтобы она проверяла все ячейки столбца «А» я не знаю, но решение этого вопроса решило бы всю задачу. Подскажите пожалуйста как можно это осуществить. Спасибо! Ссылка
Максим
18.06.2014 11:36:35
Как проверить текстовые значения двух разных столбцов с приблизительными значениями? Ссылка
Анна
13.04.2015 10:06:50
Добрый день! Помогите, пожалуйста! Есть таблица, с кодами номенклатур и неделями, нужно понять сколько было изменений в периоде. Можно это сделать с помощью формул? Спасибо! Ссылка
Rua
19.05.2015 16:05:51
Здравствуйте! Помогите, пожалуйста, решить такую задачу: нужно сравнить значения (ФИО) и присвоить в отдельном столбце уникальные идентификаторы (1, 2, 3 и т.д.) уникальным ФИО (то есть, одинаковые ФИО должны иметь одинаковые идентификаторы). Ссылка
Дмитрий Романов
23.06.2015 11:26:38
Здравствуйте! есть три столбца, это «артикул» «полугодие» и «количество» сравнить надо с другой таблицей, где так же «артикул» «полугодие» и «количество». Артикула могут повторяться, получается нужно взять сумму «артикула» определенного «полугодия» и сравнить с суммой «артикула» такого же «полугодия» но из другой таблицы. важно итоговое расхождение (т.е + или — по количеству) Пожалуйста подскажите как это можно реализовать, спасибо. Ссылка
Николай Павлов
07.02.2016 15:10:24
Дмитрий, вам нужно сначала просуммировать данные по артикулам (сводной таблицей или функцией СУММЕСЛИ
), а потом уже сравнивать любым подходящим способом из этой статьи. Родитель Ссылка
Ильшат
05.02.2016 14:48:22
Добрый день, Николай! Огромное спасибо за формулу массива, действительно красивое решение). Пересмотрел Ваше видео несколько раз, попытался реализовать этот способ, до последнего момента подстановки формулы из лишнего столбца все работает и результат корректен. Как только делаю подстановку и ввожу формулу массива результат искажается, в моем случае из 7 различий остается только 5. Никак не могу понять в чем дело… Офис стоит 2020, насколько я понял такой же как и у Вас. Может еще в чем-то быть проблема? Заранее спасибо Ссылка
Николай Павлов
07.02.2016 15:09:20
Ильшат, может про закрепление долларами абсолютных ссылок не забыли? Скачайте приложенный файл с примером — посмотрите как там сделано. Родитель Ссылка
Ильшат
08.02.2016 09:19:08
Спасибо, Николай, не забыл. Изначально скачивал Ваш пример и копировал формулу массива, подставив свои диапазоны, корректно не получилось. Потом уже решил по шагам из видео все делать. Пока ошибку не нашел) Родитель Ссылка
white-hot
15.03.2016 22:50:10
Николай добрый вечер! подскажите пожалуйста! Сравниваю два списка,прайс,ассортимент с помощью варианта 2, выделил уникальные,редактировал нужный список,необходимо скопировать.но он вставляется в условном форматировании в цвете а как это все отменить чтобы нормально копировалось? спасибо! Ссылка
JURA TAFIJ
19.03.2016 21:01:44
Добрый день. У меня такая ситуация…. Есть папка с товаром за год . В этой папке 12 папок товара по месяцам. В каждой папке 20-22 книги ексель с названием по датам. В этих книгах от 1 до 15 листов а в каждом есть коды товара. При поступлении нового товара мне нужно проверить, не был ли этот товар раньше у нас. А на данный момент мне нужно сверить товар за весь год, не был ли тот же товар у нас дважды (напр. коды товара с апреля, не совпадают ли с кодами тов. за июль ) . Каждая книга называется датой , каждый лист — поставщик. Нельзя ли создать сводную табл. из такого большого кол. книг и потом отфильтровать дубликаты ? Или есть другие способы сверивания данных ? За каждый совет большое спасибо ! ЮРА. Ссылка
игорь громов
06.04.2016 18:53:36
при идентичности все понятно. Но если нужно сравнить когда в одном столбце есть данные как идентичные так и другие. тогда как их индифицировать ? Ссылка
Konstantin Silich
18.07.2016 14:05:57
Добрый день, Николай! Считаю ваш сайт очень полезным и информативным, он не раз помогал мне в решении трудных задач. Но сейчас столкнулся с проблемой, решение которой не смог найти. Суть проблемы в том, что когда-то мной была создана таблица и к ней был применен расширенный фильтр как было описано вами в теме «Расширенный фильтр и немного магии», в результате поиска по этой таблице получается список «A». Вопрос, можно ли этот отфильтрованный список «A» сравнить с еще тремя списками «B», «C» и «D», чтобы в отдельной ячейке появилось название списка в котором максимальное количество совпадений со списком «A»? Заранее спасибо! Ссылка
Наиль Гафиатуллин
23.08.2016 13:58:29
Здравствуйте! Удобно спасибо. Но я привык сравнивать при помощи VBA.. это наверное потому, что я программист в душе Сравнение двух списков при помощи VBA Ссылка
bogdan yakimov
25.08.2016 08:44:09
Добрый день. Прошу помочь с задачей. Есть два столбца значений, которые необходимо сравнить и найти оставшиеся строки. Различаются по количеству строк и формой записи в ячейках. Но суть такова, что в столбце «1» содержится меньше значений, чем в столбце «2». Однако в «2» в одной строке может быть записано до шести значений, которые в столбце «1» написаны отдельно в каждой строке. Было бы отлично, если будет приложен пример. Строк -6000. Ссылка
Zedd Zorander
08.12.2016 18:18:52
Николай, Здравствуйте! Помогите пожалуйста. Использую Ваш последний способ, но у меня почему то не все уникальные значения из 2-го столбца выводятся =ЕСЛИОШИБКА(ИНДЕКС($C$1:$C$21;НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($A$1:$A$21;$C$1:$C$21)=0;СТРОКА($C$1:$C$21));G9)-1);»» Немного смущает по столбцу G. Если стоит 1 в столбце G, то первое уникальное слово из столбца С выводит, а дальге если поставить в столбец G цифру 2 выводит слово, которое есть в столбце А. Ничего не пойму. Спасибо! Ссылка
Максим Л
23.12.2016 14:33:39
Добрый день! Подскажите пожалуйста: 1. Существует 2 таблицы: Таблица №1 имеет: 1 столбец с данными и 3 последующих столбца пустые Таблица №2 имеет: 4 столбца с данными. 2. Стоит такая задача: Нужно сравнить столбец №1 обеих таблиц на наличие повторяющихся значений, если значения повторяются, то данные столбцов №2, 3, 4 таблицы №2, нужно записать в соответствующие пустые столбцы таблицы №1. Заранее большое спасибо. Ссылка
York
27.01.2017 11:44:49
Добрый день, господа! Имеются проблемы с использованием способа 5 (он же способ 6 в файле-примере), никак не могу разобраться сам. Надеюсь на помощь коллективного разума! Диапазон сравнения список 1 — 192 наименования, список 2 — 191 наименование. При изменении в формуле массива диапазонов А2:А10 и С2:С10 (формула корректно выдает три наименования из списка 2, отличающихся от списка 1) на А2:А193 и С2:С192 (формула выдает только 1 наименование)… и при изменения диапазона пропадают {} скобки, при попытке добавить скобки {} вновь — отображается только сама формула… при этом кнопка «Показать формулы» не нажата… MS стандартный 2010 ссылочка на excel здесь Ссылка
Татьяна Боронина
27.02.2017 17:32:26
Ребята, и мне можно ли помочь? Имеется список с полными адресными данными (исходный адрес-столбец А)в одном столбце и разбитыми на составляющие (Город. Улица)- в следующих столбцах. Надо выделить цветом совпадения- хотелось бы при самом красивом раскладе выделить цветом ячейку с совпадающим Городом ( столбец С) и изменить цвет шрифта при совпадении Улицы( столбец В). Например в строке 7 не прописан Зеленоград-значит она не будет выделена, в строке 19 не прописана улица Авиаторная- так же выделение. Требуется найти максимальные совпадения столбцов В И С со столбцом А Файл прилагаю Книга 3 Ссылка
Анрей С
26.06.2017 15:45:32
| У меня есть такая таблица https://docs.google.com/spreadsheets/d/1SWvxQoRKex9wHa4iobZykeG65wE-DdRPy8aBcyXkhTc/edit?usp=sharing Столбцы содержат наименования и соответствующие им значения. Я отметил цветами совпадающие имена в столбцах А и D Какую использовать формулу, чтобы она сама искала совпадающие имена в столбцах А и D и при нахождении подсчитывала разницы между значениями, соответствующими этим столбцам, например как у меня сделано вручную в столбце — G «В ячейке А2 совпадение с ячейкой D3 и найдена разница между значениями эти ячеек и вписана в G2» |
Ссылка
qew wed
12.02.2018 11:39:04
в G1 формула и скопировать или протянуть вниз =ЕСЛИОШИБКА(ВПР(A1;$D$1:$E$10;2)-B1;»») Родитель Ссылка
Сергей Рудометов
11.08.2017 13:32:54
Добрый день, Николай. Подскажите, пожалуйста, в Вашей формуле: =СУММПРОИЗВ(—(A2:A20<>B2:B20)) какую роль играют, для чего нужны и что обозначают символы — Ссылка
qew wed
12.02.2018 10:59:00
чтобы избежать колонки G можно изменить формулу вместо G2 — СТРОКА(A1) {=ЕСЛИОШИБКА(ИНДЕКС($C$1:$C$21;НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($A$1:$A$21;$C$1:$C$21)<>0; СТРОКА($C$1:$C$21));СТРОКА(A1)));»»)} формула массива сравнивает с первой по 21 строки и выводит одинаковые Ссылка
Sam Phelps
15.02.2018 05:40:04
Доброго времени суток Пожалуйста, помогите решить задачу: дан всего один столбец из 1000 строк с буквами : A, B, C, D, E, F, G, H в хаотичном порядке как рассчитать количество возникновения таких ситуация, где В следует за А, Е следует за А, С следует за А и так каждую Уникальную букву. есть вариант немного проще, столбец из множества строк с числами от 0 до 3. также, как рассчитать количество возникновения таких ситуаций, где 1 следуют за 0, 1 следуют за 2, 1 следуют за 3 и т. д. Очень прошу, помогите, кто знает какое-либо решение! Ссылка
Юрий Берестнев
16.03.2018 18:36:43
Добрый вечер! Подскажите как набрать формулу чтобы создать условное форматирование (заливка цветом) по принципу если значение какой либо отдельно взятой форматируемой ячейки (например A2) будет равно значению из диапазона C2:C70 и при этом совпадении в строке совпадения ячейка соседнего столбца в диапазоне F2:F70 ,будет соответствовать значению Z2. Примитив условия работает =И(A2=C2;F2=$Z$2), надо перевести в универсальную формулу с учетом одновременного совпадения ячейки с ячейкой из первого диапазона и в строке совпадения ячейки из другого диапазона с конкретной ячейкой или значением (Например «ТО»;) Очень нужно — помогите товарищи форумчане! Ссылка
Алексей Попов
04.04.2018 08:26:17
Добрый вечер. Подскажите как сделать. Нужно сравнить столбец А (А5 до А50) с тремя столбцами D5-D50, E5-E50, F5-F50 построчно. Если число в столбце А меньше числа в столбце D, то в ячейке А1 прибавить единицу. Если больше чем в столбце D, но меньше чем в столбце E, то в ячейке В1 прибавить единицу. Если больше чем в столбце Е, но меньше чем в столбце F, то в ячейке С1 прибавить единицу. Если больше чем в столбце F, то в ячейке D1 прибавить единицу. Числа в столбце D больше чем в столбце Е, а числа в столбце Е больше чисел в столбце F построчно. Ссылка
ruslan kitenko
21.06.2018 12:22:09
Добрый день, подскажите, пожалуйста, решение. Вариант 1 не работает уже на трех списках (=A1=B1=C1). А если списков двадцать? Ссылка
ruslan kitenko
21.06.2018 15:00:09
Решил только через =ЕСЛИ(И(….)). Ссылка
Алексей Журавлев
26.02.2019 21:44:51
Добрый вечер! Есть два столбца с денежными суммами, с копейками, которые составляют примерно 10 000 строк! Суммы эти могут повторяться в обоих столбцах в разных строках по несколько раз и нужно сделать заливку всех совпадений! Совпадения допускаются с разницей до одного рубля! К примеру в А1=1052,78 а в B200=1053,76 , то это должно считаться совпадением и эти ячейки A1 и В200 должны залиться допустим жёлтым цветом и так все 10 000 строк должны сравниться, а не совпавшие суммы должны остаться допустим без заливки! НАРОД ПОМОГИТЕ ПОЖАЛУЙСТА, КТО МАКРОСОМ, КТО ФОРМУЛОЙ, КТО ЧЕМ МОЖЕТ! Времени у меня на это два дня осталось, а потом расстрел Ссылка
Шахин Караханов
17.04.2019 16:06:10
А можно ли способ формула с массивом составить в макросе? А то этот способ для длинных таблиц затрудняет работу. Ссылка
