Сравнение 2-х таблиц в EXCEL
Сравним две таблицы имеющих практически одинаковую структуру. Таблицы различаются значениями в отдельных строках, некоторые наименования строк встречаются в одной таблице, но в другой могут отсутствовать.
Пусть на листах Январь и Февраль имеется две таблицы с оборотами за период по соответствующим счетам.
Как видно из рисунков, таблицы различаются:
- Наличием (отсутствием) строк (наименований счетов). Например, в таблице на листе Январь отсутствует счет 26 (см. файл примера ), а в таблице на листе Февраль отсутствуют счет 10 и его субсчета.
- Разными значениями в строках. Например, по счету 57 обороты за январь и февраль не совпадают.
Если структуры таблиц примерно одинаковы (большинство наименований счетов (строк) совпадают, количество и наименования столбцов совпадают), то можно сравнить две таблицы. Проведем сравнение двумя способами: один проще в реализации, другой нагляднее.
Простой вариант сравнения 2-х таблиц
Сначала определим какие строки (наименования счетов) присутствуют в одной таблице, но отсутствуют в другой. Затем, в таблице, в которой меньше строк отсутствует (в наиболее полной таблице), выведем отчет о сравнении, представляющий собой разницу по столбцам (разница оборотов за январь и февраль).
Основным недостатком этого подхода является, то, что отчет о сравнении таблиц не включает строки отсутствующие в наиболее полной таблице. Например, в рассматриваемом нами случае, наиболее полной таблицей является таблица на листе Январь, в которой отсутствует счет 26 из февральской таблицы.
Чтобы определить какая из двух таблиц является наиболее полной нужно ответить на 2 вопроса: Какие счета в февральской таблице отсутствуют в январской? и Какие счета в январской таблице отсутствуют в январской?
Это можно сделать с помощью формул (см. столбец Е): = ЕСЛИ(ЕНД(ВПР(A7;Январь!$A$7:$A$81;1;0));»Нет»;»Есть») и = ЕСЛИ(ЕНД(ВПР(A7;Февраль!$A$7:$A$77;1;0));»Нет»;»Есть»)
Сравнение оборотов по счетам произведем с помощью формул: = ЕСЛИ(ЕНД(ВПР($A7;Февраль!$A$7:$C77;2;0));0;ВПР($A7;Февраль!$A$7:$C77;2;0))-B7 и = ЕСЛИ(ЕНД(ВПР($A7;Февраль!$A$7:$C77;3;0));0;ВПР($A7;Февраль!$A$7:$C77;3;0))-C7
В случае отсутствия соответствующей строки функция ВПР() возвращает ошибку #Н/Д, которая обрабатывается связкой функций ЕНД() и ЕСЛИ() , заменяя ошибку на 0 (в случае отсутствия строки) или на значение из соответствующего столбца.
С помощью Условного форматирования можно выделить расхождения (например, красным цветом).
Более наглядный вариант сравнения 2-х таблиц (но более сложный)
По аналогии с задачей решенной в статье Сравнение 2-х списков в MS EXCEL можно сформировать список наименований счетов, включающий ВСЕ наименования счетов из обоих таблиц (без повторов). Затем вывести разницу по столбцам.
Для этого необходимо:
- С помощью формулы массива = ЕСЛИОШИБКА(ЕСЛИОШИБКА(ИНДЕКС(Январь;ПОИСКПОЗ(0;СЧЁТЕСЛИ(A$4:$A4;Январь);0)); ИНДЕКС(Февраль;ПОИСКПОЗ(0;СЧЁТЕСЛИ(A$4:$A4;Февраль);0)));»») сформировать в столбце А перечень счетов из обоих таблиц (без повторов);
- С помощью формулы массива = ЕСЛИОШИБКА(ИНДЕКС(Список; ПОИСКПОЗ(НАИМЕНЬШИЙ(СЧЁТЕСЛИ(Список; » , где Список — именованный диапазон представляет собой перечень счетов из обоих таблиц (столбец А), отсортировать список счетов, полученный на предыдущем этапе;
- С помощью формулы = ЕСЛИ(ЕНД(ВПР($B5;Январь!$A$7:$C$81;2;0));0;ВПР($B5;Январь!$A$7:$C$81;2;0))- ЕСЛИ(ЕНД(ВПР($B5;Февраль!$A$7:$C$77;2;0));0;ВПР($B5;Февраль!$A$7:$C$77;2;0)) произвести сравнение оборотов по счетам;
- С помощью Условного форматирования выделить расхождения цветом, а также выделить счета встречающиеся только в одной таблице (например, на рисунке выше счета, содержащиеся только в таблице Январь, выделены синим, а желтым выделены счета только из февральской таблицы).
Сравнить две таблицы 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»;
- нет необходимости вручную выправлять формулы по столбцам и сами таблицы.
Надеюсь, решения, приведенные в статье, окажутся полезными.
Сравнение двух версий книги с помощью средства сравнения электронных таблиц
Office для бизнеса Средство сравнения электронных таблиц 2013 Средство сравнения электронных таблиц 2016 Средство сравнения электронных таблиц 2019 Средство сравнения электронных таблиц 2021 Еще. Меньше
Если другие пользователи имеют право на редактирование вашей книги, то после ее открытия у вас могут возникнуть вопросы «Кто ее изменил? И что именно изменилось?» Средство сравнения электронных таблиц от Майкрософт поможет вам ответить на эти вопросы — найдет изменения и выделит их.
Важно: Spreadsheet Compare is only available with Office профессиональный плюс 2013, Office профессиональный плюс 2016, Office профессиональный плюс 2019, or Приложения Microsoft 365 для предприятий.
- Откройте средство сравнения электронных таблиц.
- В левой нижней области выберите элементы, которые хотите включить в сравнение книг, например формулы, форматирование ячеек или макросы. Или просто выберите вариант Select All (Выделить все).
- На вкладке 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 на совпадение значений в столбцах
Всем привет! Дорабатывал семантику, в иксельке, ключей получилось изрядно дофига + старые. Подскажите, как найти дубли и убрать их? Сейчас они в четыре колонки собраны — старые с частотами, и новые с частотами, я в одну таблицу их перенес.
28 июня 2022
Для голосования требуется Авторизация
Количество статей:
Рейтинг автора:
Количество статей:
Рейтинг автора:
поделиться:
3 Ответа
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. Все права сохранены. Условия пользовательского соглашения
Сервис Bankoff, позволявший выпускать карты, прекращает свою деятельность из-за наплыва пользователей / Криптобиржа Currency прекращает работать в России / Выпустили огненное интервью на YouTube-канале с командой TraffBraza / Роскомнадзор запретил на территории России рекламное продвижение Google и её сервисов / Новое экзотическое ГЕО от М1 — Эквадор / NashStore вместо Google Play: в России запустят аналог магазина приложений для Android /
Авторизация
Введите ваше имя пользователя или адрес email. Вы получите email сообщение с инструкциями по сбросу пароля.
Создать аккаунт
Делитесь публикациями и зарабатывайте деньги, общайтесь с коллегами и задавайте вопросы. Это бесплатно.