Как в двух файлах excel найти совпадения

Сравнение двух версий книги с помощью средства сравнения электронных таблиц

Office для бизнеса Средство сравнения электронных таблиц 2013 Средство сравнения электронных таблиц 2016 Средство сравнения электронных таблиц 2019 Средство сравнения электронных таблиц 2021 Еще. Меньше

Если другие пользователи имеют право на редактирование вашей книги, то после ее открытия у вас могут возникнуть вопросы «Кто ее изменил? И что именно изменилось?» Средство сравнения электронных таблиц от Майкрософт поможет вам ответить на эти вопросы — найдет изменения и выделит их.

Важно: Spreadsheet Compare is only available with Office профессиональный плюс 2013, Office профессиональный плюс 2016, Office профессиональный плюс 2019, or Приложения Microsoft 365 для предприятий.

1. Откройте средство сравнения электронных таблиц.
2. В левой нижней области выберите элементы, которые хотите включить в сравнение книг, например формулы, форматирование ячеек или макросы. Или просто выберите вариант Select All (Выделить все).
3. На вкладке Home (Главная) выберите элемент Compare Files (Сравнить файлы).

В диалоговом окне «Сравнение файлов» в строке » » выберите более ранжную версию книги. Помимо выбора файлов, сохраненных на компьютере или в сети, можно также ввести веб-адрес, ведущий к книге, сохраненной на сайте.

В диалоговом окне «Сравнение файлов» в строке «С» до нужной версии.

Примечание: Можно сравнивать два файла с одинаковыми именами, если они хранятся в разных папках.

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

Результаты сравнения отображаются в виде таблицы, состоящей из двух частей. Книга в левой части соответствует файлу, указанному в поле «Compare» (Сравнить), а книга в правой части — файлу, указанному в поле «To» (С чем). Подробные сведения отображаются в области под двумя частями таблицы. Изменения выделяются разными цветами в соответствии с их типом.

Интерпретация результатов

• В двух расположенных рядом частях таблицы сравнивается каждый лист из обоих файлов, начиная с самого крайнего слева. Если лист в книге скрыт, он все равно отображается и сравнивается в средстве сравнения электронных таблиц.
• Если содержимое не умещается в ячейках, выберите команду Resize Cells to Fit (Размер ячеек по размеру данных).
• Различия разного типа выделяются с помощью цвета заливки ячейки или цвета шрифта текста. Например, ячейки с введенными значениями (не с формулами) выделяются заливкой зеленого цвета в расположенных рядом частях таблицы и шрифтом зеленого цвета в области результатов. В левой нижней части указаны условные обозначения, поясняющие значения цветов.

Другие способы работы с результатами сравнения

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

• Вы можете экспортировать результаты в файл Excel, более удобный для чтения. Выберите Home > Export Results (Главная > Экспорт результатов).
• Чтобы скопировать результаты и вставить их в другую программу, выберите Home > Copy Results to Clipboard (Главная > Копировать результаты в буфер обмена).
• Чтобы отобразить форматирование ячеек из книги, выберите Home > Show Workbook Colors (Главная > Показать цвета книги).

Другие причины для сравнения книг

• Допустим, в вашей организации ожидается аудиторская проверка. Вам нужно проследить данные в важных книгах, в которых показаны изменения по месяцам и по годам. Это поможет вам найти и исправить ошибки раньше, чем до них доберутся проверяющие.
• Средство сравнения электронных таблиц можно использовать не только для сравнения содержимого листов, но и для поиска различий в коде Visual Basic для приложений (VBA). Результаты отображаются в окне таким образом, чтобы различия можно было просматривать параллельно.

Сравнение двух таблиц в Excel на совпадение значений в столбцах

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

Сравнение двух столбцов на совпадения в Excel

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

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

1. Выберите инструмент «ФОРМУЛЫ»-«Определенные имена»-«Присвоить имя».
2. В появившемся окне в поле «Имя:» введите значение – Таблица_1.
3. Левой клавишей мышки сделайте щелчок по полю ввода «Диапазон:» и выделите диапазон: A2:A15. И нажмите ОК.

Для второго списка выполните те же действия только имя присвойте – Таблица_2. А диапазон укажите C2:C15 – соответственно.

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

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

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

1. Выделите диапазон первой таблицы: A2:A15 и выберите инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать правило»- «Использовать формулу для определения форматированных ячеек:».
2. В поле ввода введите формулу:
3. Щелкните по кнопке «Формат» и на вкладке «Заливка» укажите зеленый цвет. На всех окнах жмем ОК.
4. Выделите диапазон первого списка: C2:C15 и снова выберите инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать правило»- «Использовать формулу для определения форматированных ячеек:».
5. В поле ввода введите формулу:
6. Щелкните по кнопке «Формат» и на вкладке «Заливка» укажите синий цвет. На всех окнах жмем ОК.

Принцип сравнения данных двух столбцов в Excel

При определении условий для форматирования ячеек столбцов мы использовали функцию СЧЕТЕСЛИ. В данном примере эта функция проверяет сколько раз встречается значение второго аргумента (например, A2) в списке первого аргумента (например, Таблица_2). Если количество раз = 0 в таком случае формула возвращает значение ИСТИНА. В таком случае ячейке присваивается пользовательский формат, указанный в параметрах условного форматирования.

Ссылка во втором аргументе относительная, значит по очереди будут проверятся все ячейки выделенного диапазона (например, A2:A15). Например, для сравнения двух прайсов в Excel даже на разных листах. Вторая формула действует аналогично. Этот же принцип можно применять для разных подобных задач.

• Excel Formula Examples
• Создать таблицу
• Форматирование
• Функции Excel
• Формулы и диапазоны
• Фильтр и сортировка
• Диаграммы и графики
• Сводные таблицы
• Печать документов
• Базы данных и XML
• Возможности Excel
• Настройки параметры
• Уроки Excel
• Макросы VBA
• Скачать примеры

Как в двух файлах excel найти совпадения

28.06.2022 в 16:40
28.06.2022 в 16:40
Для голосования требуется Авторизация

Проще простого. Выбери инструмент «ФОРМУЛЫ»-«Определенные имена»-«Присвоить имя» В появившемся окне в поле «Имя:» введите значение – Таблица_1. Левой клавишей мышки сделайте щелчок по полю ввода «Диапазон:» и выделите диапазон: A2:A15. И нажмите ОК. Тоже самое со вторым списком. Выделите диапазон первой таблицы: A2:A15 и выберите инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать правило»- «Использовать формулу для определения форматированных ячеек:». В поле ввода введите формулу: =СЧЕТЕСЛИ(Таблица_2;A2)=0 Щелкните по кнопке «Формат» и на вкладке «Заливка» укажите зеленый цвет. На всех окнах жмем ОК. Тоже самое со вторым диапазоном.Заливку задаем синюю. Выйдет примерно вот так:

28.06.2022 в 16:47
28.06.2022 в 16:47
Для голосования требуется Авторизация

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

28.06.2022 в 16:49
28.06.2022 в 16:49
Для голосования требуется Авторизация

Какой-то способ для школьников, если честно. Так просто по одному ключу проверять устанешь. Таблицы можно было и не сводить в одну. В икселе есть функция Inquire начиная с версий 2013 года. Она нужна для того, чтобы сравнивать не колонки, а два файла .XLS или .XLSX. Зайдите в параметры Выберите сначала надстройки, а затем управление надстройками COM. Отметьте Inquire и нажмитеь «ОК». Перейдите к вкладке Inquire. Нажмите на кнопку Compare Files, указать, какие именно файлы будут сравниваться, и выбрать Compare. В открывшемся окне провести сравнения, используя показанные совпадения и различия между данными в столбцах. Если уже свели в один лист: Способ первый что я использую Используй функцию ИЛИ и СОВПАД вместе вот так: 1. В третьем столбце, где будут размещаться результаты, вводится формула =ИЛИ(СОВПАД(I6;$H$6:$H$19))
2. Вместо нажатия Enter нажимается комбинация клавиш Ctr + Shift + Enter. Результатом станет появление фигурных скобок слева и справа формулы.
3. Формула протягивается вниз, до конца колонки — в данном случае проверяется наличие данных из второго столбца в первом. Это позволит изменяться сравниваемому показателю, тогда как знак $ закрепляет диапазон, с которым выполняется сравнение. Операция выведет не дубль значения, а подсветит, в какой строке в целом обнаружен дубль. В случае нахождения это будет «ИСТИНА». Если ни одного совпадения не было обнаружено — в ячейке появится надпись «ЛОЖЬ». Второй способ, удобнее Состоит в использовании функции ВПР, которую можно расшифровать как «Вертикальный Просмотр». Для сравнения двух столбцов из похожего примера следует ввести в верхнюю ячейку (J6) третьей колонки формулу =ВПР(H6;$I$6:$I$15;1;0) и протянуть ее в самый низ, до J15. Результаты проверки устанавливаются четко напротив сравниваемого значения в первом столбце.

Для ответа вам необходимо Авторизоваться

ProTraffic.com — твой путеводитель по арбитражу трафика. Все фишки и экспертный контент от практиков, актуальные новости и аналитика, обучающие видео и интервью с лидерами рынка. Лей в плюс с ProTraffic!

© 2019-2023, ProTraffic. Все права сохранены. Условия пользовательского соглашения
Авторизация

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

Создать аккаунт

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

Сравнить две таблицы excel

Решим достаточно тривиальную задачу с помощью языка python — сравним две таблицы excel и выведем результат в третью. Что может быть проще, и почему просто не использовать средства самой программы, входящей в пакет office? Попробуем разобраться.

Дано

У нас есть две таблицы с условными названиями «Прайс1» и «Прайс2».

Обе имеют расширение .xlsx и открываются программой excel без каких-либо дополнительных действий. Но есть проблема — таблицы доступны в формате read-only дабы никто кроме владельца не мог изменить данные. Поэтому, для того, чтобы начать применять какие-либо формулы в самих таблицах необходимо таблицы продублировать, сохранив их дубликаты.

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

И проблема третья — столбец с количеством товара не обязательно следует за столбцом с наименованиями товаров.

Как сравнить данные таблицы с наименьшими трудозатратами и сделать, так чтобы это сравнение легко адаптировалось под иные вводные?

Какие предложения от excel ?

Как правило, в задачах подобного рода применяется функция ВПР.

Например формула может выглядеть следующим образом:

=ЕСЛИОШИБКА(ВПР(F4;$B$3:$C$5;2;0);0)

Логика следующая: берем позицию в Прайсе2 и ищем ее по Прайсу1, выводя значение.

Однако, этот вариант работает не для всех случаев: если в Прайсе2 нет позиции, которая была в Прайсе1, формула не работает —

Формула посложней

Она повторяет предыдущую, но уже учитывает значение (количество товара) при поиске.

=ЕСЛИ(ЕСЛИОШИБКА(ВПР(F3;$B$3:$C$5;2;0);0)=G3;"";ЕСЛИОШИБКА(ВПР(F3;$B$3:$C$5;2;0);0))

Но она также бесполезна, если позиция выбыла в Прайсе2:

И третий вариант формулы

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

Для ячеек в столбце с Прайсом1 формула примет вид:

=ЕСЛИ(ЕНД(ВПР(A2;'D:\Users\al\Desktop\[Прайс1.xlsx]Лист1'!$B$3:$B$5;1;0));"Нет";ВПР(A2;'D:\Users\al\Desktop\[Прайс1.xlsx]Лист1'!$B$3:$C$5;2;0))

Для ячеек в столбце с Прайсом2:

=ЕСЛИ(ЕНД(ВПР(A2;'D:\Users\al\Desktop\[Прайс2.xlsx]Лист1'!$B$3:$B$5;1;0));"Нет";ВПР(A2;'D:\Users\al\Desktop\[Прайс2.xlsx]Лист1'!$B$3:$C$5;2;0))

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

Здесь видно, что формула учитывает моменты, если в Прайсах пропадают или появляются позиции. В таблице они обозначены словом «Нет».

Формула работает. Но, помимо ужасающих размеров, она имеет одно «но», точнее два «но».
Чтобы все работало корректно, необходимо:

• правильно указать диапазоны из Прайсов (выделить их в Прайсах Ctrl+Shift+Enter и перенести в формулу);
• позиции товаров в финальной таблице должны идти с учетом всех выбывших и/или прибывших позиций в обоих Прайсах. Сама формула не будет искать эти позиции в Прайсах и в вставлять в итоговую. Она просто берет наименование в итоговой таблице и ищет его в Прайсах, записывая количество товара и/или его отсутствие.

Посмотрим, что предлагает python.

Python в деле

Можно пойти через использование библиотеки openpyxl и тогда решение будет выглядеть примерно так.

*Код написан не для прайсов, но для вычисления прямого и косвенного владения в компаниях, но логика та же.

текст программы

import openpyxl,pprint from openpyxl.utils import get_column_letter,column_index_from_string wb = openpyxl.load_workbook('Прайс1.xlsx') sheet=wb.get_active_sheet() wb2 = openpyxl.load_workbook('Прайс2.xlsx') sheet2=wb2.get_active_sheet() h = open('struct.txt','a') test=<> test2=<> test3=[] poisk=str(input('компания: ')) #test - словарь из "кто владеет:номер строки) for row in sheet['A2':'A290']: for cellObj in row: i = cellObj.value b = cellObj.row test.setdefault(i,b) #test2 - словарь из "кем владеют:номер столбца) for row in sheet['B1':'HH']: for cellObj in row: i = cellObj.value b = cellObj.column c = column_index_from_string(b) #переводим названия столбцов excel в цифры test2.setdefault(i,c) print('\n'+'прямое владение') # прямое владение for row1 in sheet['B2':'HH290']: for cellObj in row1: if cellObj.value ==None: #пропускаем пустые значения в клетках continue i = float (cellObj.value)/100 #A в B s =sheet.cell(row=cellObj.row,column=1).value if s!=poisk: continue d=sheet.cell(row=1,column=column_index_from_string(cellObj.column)).value #B (кем владеют) for k,v in test.items(): for u in range (2,217): # все значения- B2:F6 if sheet.cell(row=v, column=u).value ==None: continue b = sheet.cell(row=v, column=u).value # % владения q=float(''.format(i*100)) y=sheet.cell(row=1,column=u).value #кем владеют p=s+' владеет '+ d +' - '+str(q)+'%' if p not in test3: test3.append(p) print(p) h.write(p+'\n') print('\n'+'1-е косвенное участие') # 1-е косвенное участие for row1 in sheet['B2':'HH290']: for cellObj in row1: if cellObj.value ==None: #пропускаем пустые значения в клетках continue i = float (cellObj.value)/100 #A в B s =sheet.cell(row=cellObj.row,column=1).value if s!=poisk: continue d=sheet.cell(row=1,column=column_index_from_string(cellObj.column)).value #B (кем владеют) for k,v in test.items(): if d in k: # если кем владеют, есть в кто владеет - то ищем по строке значение for u in range (2,217): # все значения if sheet.cell(row=v, column=u).value ==None: continue b = sheet.cell(row=v, column=u).value # % владения q=float(i)*float(b) #процент косвенного владения A через B в С q1=float(''.format(q)) #5 знаков после запятой и * 100 y=sheet.cell(row=1,column=u).value #кем владеют print (' через '+ d + ' в ' + y +' - '+str(q1)+'%') h.write(s+' через '+ d + ' владеет ' + y +' - '+str(q1)+'%'+'\n') h.write('\n') 

Программа собирает все наименования и количество товара по ячейкам в обоих Прайсах, далее заполняет итоговую таблицу excel наименованиями и, найдя по координатам, количество товара — также и значениями количества товара.

Работает. Но громоздко и легко запутаться.

Воспользуемся возможностями библиотеки pandas, если она не установлена, то pip install pandas.
Импортируем библиотеку и считаем Прайсы в датафреймы(наборы данных):

import pandas as pd df1 = pd.read_excel('Прайс1-.xlsx', sheet_name = 'Лист1') df2 = pd.read_excel('Прайс2-.xlsx', sheet_name = 'Лист1') 

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

m = (df1.merge(df2, how='outer', on=['товар','Количество'], suffixes=['', '_new'], indicator=True)) m2 = (df2.merge(df1, how='outer', on=['товар','Количество'], suffixes=['', '_new'], indicator=True)) 

Создадим третий датафрейм из запросов к двум предыдущим и уберем оттуда дубликаты:

m3=pd.merge(m.query("_merge=='right_only'"), m2.query("_merge=='right_only'"), how ='outer').drop_duplicates(subset=['товар','Количество']) 

Осталось сохранить новую таблицу:

m3.query("_merge=='right_only'").to_excel('out.xlsx') 

На выходе мы получаем итоговую таблицу:

Как видно, в нее не попала позиция «сок», так как в этой позиции не произошло изменений.

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

Какое из решений использовать — дело вкуса.

Однако данный вариант имеет преимущества:

• не требует перевода таблиц из «read-only»;
• нет необходимости вручную выправлять формулы по столбцам и сами таблицы.

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