Пожалуй, каждый, кто работает с данными в Excel сталкивается с вопросом как сравнить два столбца в Excel на совпадения и различия. Существует несколько способов как это сделать. Давайте рассмотрим подробней каждый из них.
Как сравнить два столбца в Excel по строкам
Сравнивая два столбца с данными часто необходимо сравнивать данные в каждой отдельной строке на совпадения или различия. Сделать такой анализ мы можем с помощью функции . Рассмотрим как это работает на примерах ниже.
Пример 1. Как сравнить два столбца на совпадения и различия в одной строке
Для того, чтобы сравнить данные в каждой строке двух столбцов в Excel напишем простую формулу . Вставлять формулу следует в каждую строку в соседнем столбце, рядом с таблицей, в которой размещены основные данные. Создав формулу для первой строки таблицы, мы сможем ее протянуть/скопировать на остальные строки.
Для того чтобы проверить, содержат ли два столбца одной строки одинаковые данные нам потребуется формула:
=ЕСЛИ(A2=B2; “Совпадают”; “”)
Формула, определяющая различия между данными двух столбцов в одной строке будет выглядеть так:
=ЕСЛИ(A2<>B2; “Не совпадают”; “”)
Мы можем уместить проверку на совпадения и различия между двумя столбцами в одной строке в одной формуле:
=ЕСЛИ(A2=B2; “Совпадают”; “Не совпадают”)
=ЕСЛИ(A2<>B2; “Не совпадают”; “Совпадают”)
Пример результата вычислений может выглядеть так:
Для того чтобы сравнить данные в двух столбцах одной строки с учетом регистра следует использовать формулу:
=ЕСЛИ(СОВПАД(A2,B2); “Совпадает”; “Уникальное”)
Как сравнить несколько столбцов на совпадения в одной строке Excel
В Excel есть возможность сравнить данные в нескольких столбцах одной строки по следующим критериям:
- Найти строки с одинаковыми значениями во всех столбцах таблицы;
- Найти строки с одинаковыми значениями в любых двух столбцах таблицы;
Пример1. Как найти совпадения в одной строке в нескольких столбцах таблицы
Представим, что наша таблица состоит из нескольких столбцов с данными. Наша задача найти строки в которых значения совпадают во всех столбцах. В этом нам помогут функции Excel и . Формула для определения совпадений будет следующей:
=ЕСЛИ(И(A2=B2;A2=C2); “Совпадают”; ” “)
Если в нашей таблице очень много столбцов, то более просто будет использовать функцию в сочетании с :
=ЕСЛИ(СЧЁТЕСЛИ($A2:$C2;$A2)=3;”Совпадают”;” “)
В формуле в качестве “5” указано число столбцов таблицы, для которой мы создали формулу. Если в вашей таблице столбцов больше или меньше, то это значение должно быть равно количеству столбцов.
Пример 2. Как найти совпадения в одной строке в любых двух столбцах таблицы
Представим, что наша задача выявить из таблицы с данными в несколько столбцов те строки, в которых данные совпадают или повторяются как минимум в двух столбцах. В этом нам помогут функции и . Напишем формулу для таблицы, состоящей из трех столбцов с данными:
=ЕСЛИ(ИЛИ(A2=B2;B2=C2;A2=C2);”Совпадают”;” “)
В тех случаях, когда в нашей таблице слишком много столбцов – наша формула с функцией будет очень большой, так как в ее параметрах нам нужно указать критерии совпадения между каждым столбцом таблицы. Более простой способ, в этом случае, использовать функцию .
=ЕСЛИ(СЧЁТЕСЛИ(B2:D2;A2)+СЧЁТЕСЛИ(C2:D2;B2)+(C2=D2)=0; “Уникальная строка”; “Не уникальная строка”)
=ЕСЛИ(СЧЁТЕСЛИ($B:$B;$A5)=0; “Нет совпадений в столбце B”; “Есть совпадения в столбце В”)
Эта формула проверяет значения в столбце B на совпадение с данными ячеек в столбце А.
Если ваша таблица состоит из фиксированного числа строк, вы можете указать в формуле четкий диапазон (например, $B2:$B10 ). Это позволит ускорить работу формулы.
Как сравнить два столбца в Excel на совпадения и выделить цветом
Когда мы ищем совпадения между двумя столбцами в Excel, нам может потребоваться визуализировать найденные совпадения или различия в данных, например, с помощью выделения цветом. Самый простой способ для выделения цветом совпадений и различий – использовать “Условное форматирование” в Excel. Рассмотрим как это сделать на примерах ниже.
Поиск и выделение совпадений цветом в нескольких столбцах в Эксель
В тех случаях, когда нам требуется найти совпадения в нескольких столбцах, то для этого нам нужно:
- Выделить столбцы с данными, в которых нужно вычислить совпадения;
- На вкладке “Главная” на Панели инструментов нажимаем на пункт меню “Условное форматирование” -> “Правила выделения ячеек” -> “Повторяющиеся значения”;
- Во всплывающем диалоговом окне выберите в левом выпадающем списке пункт “Повторяющиеся”, в правом выпадающем списке выберите каким цветом будут выделены повторяющиеся значения. Нажмите кнопку “ОК”:
- После этого в выделенной колонке будут подсвечены цветом совпадения:
Поиск и выделение цветом совпадающих строк в Excel
Поиск совпадающих ячеек с данными в двух, нескольких столбцах и поиск совпадений целых строк с данными это разные понятия. Обратите внимание на две таблицы ниже:
В таблицах выше размещены одинаковые данные. Их отличие в том, что на примере слева мы искали совпадающие ячейки, а справа мы нашли целые повторяющие строчки с данными.
Рассмотрим как найти совпадающие строки в таблице:
- Справа от таблицы с данными создадим вспомогательный столбец, в котором напротив каждой строки с данными проставим формулу, объединяющую все значения строки таблицы в одну ячейку:
=A2&B2&C2&D2
Во вспомогательной колонке вы увидите объединенные данные таблицы:
Теперь, для определения совпадающих строк в таблице сделайте следующие шаги:
- Выделите область с данными во вспомогательной колонке (в нашем примере это диапазон ячеек E2:E15 );
- На вкладке “Главная” на Панели инструментов нажимаем на пункт меню “Условное форматирование” -> “Правила выделения ячеек” -> “Повторяющиеся значения”;
- Во всплывающем диалоговом окне выберите в левом выпадающем списке “Повторяющиеся”, в правом выпадающем списке выберите каким цветом будут выделены повторяющиеся значения. Нажмите кнопку “ОК”:
- После этого в выделенной колонке будут подсвечены дублирующиеся строки:
Друзья, рад вас приветствовать на страницах моего блога. Сегодня хочу рассказать вам о 4 способах сравнения файлов по содержимому. Необходимость найти различия возникает довольно часто. Приходится делать сравнение файлов по типу или размеру. Сравнение файлов в Word и Excel тоже весьма распространённая процедура в офисах и конторах, где постоянно приходится работать с текстовыми документами и таблицами.
Но для веб-мастера или владельца сайта часто бывает необходимо сравнить два файла по содержимому. И речь идёт не о текстовых файлах, хотя и они тоже анализируются так же, а о форматах.html, .css, .php и так далее. Тех файлах, где нужно найти изменения в коде. И порой изменение одного лишь знака может обеспечить уйму трудностей с достижением результата.
Так, последний пример, когда я использовал сравнение двух файлов, — это проблема с работой комментариев. В своём я упоминал об этом. Причина была всего лишь в одном пробеле. Вы только представьте, как можно найти такое несущественное различие вручную.
И, вообще, работая с файлами сайта, достаточно часто приходиться анализировать код. Делать это вручную нереально. Куда проще воспользоваться подручными средствами, инструментами, которые должны быть у каждого веб-мастера и владельца сайта.
Способ 1. Сравнение файлов в программе Total Commander.
Этой программой я пользуюсь давно. Для работы с файлами, на мой взгляд, это одна из лучших программ. Программа Total Commander платная, но можно в течение 30 дней пользоваться и пробной версией, которая по истечении времени также будет работать, но с предложением о покупке. Скачать пробную версию можно .
Ранее я рассказывал, как настроить через TC, которое позволяет легко управлять файлами сайта и осуществлять все необходимые операции прямо с компьютера, минуя личный кабинет хостинга. Только из-за одной этой возможности уже стоит использовать эту программу.
А теперь разберём инструмент сравнения файлов по содержимому, который также позволяет редактировать и копировать данные в процессе анализа.
Итак, запускаете Total Commander – в одной из панелей выбираете первый файл для сравнения – во второй панели открываете папку со вторым файлом.
Откроется новое окно, также разделённое на две панели. В самом верху, в каждой панели будет срока для выбора файла. В одной из них первый файл будет уже выбран (но может быть и пустым). Для выбора файла нужно нажать на небольшую кнопочку со стрелочками (>>) .
Выбрав файлы для сравнения, нажимаете на кнопку «Сравнить» . В каждой панели появится содержимое выбранного файла. Отличия будут отмечены фоновым цветом.
Для внесения изменений в файл достаточно нажать на кнопку «Редактировать» . И можно приступать к внесению изменений. Также здесь будут доступны функции копирования и отката, поиска и изменение кодировки.
Если вы внесли изменения в файл, то после закрытия окна сравнения, будет предложено сохранить результат.
Способ 2. Сравнение файлов в Notepad++.
Если вам приходилось, хоть когда-либо, редактировать файлы html, css и php. То наверняка вы слышали о бесплатном редакторе кода Notepad++. Скачать программу можно .
Эта прекрасная программа, которая к неисчислимым её достоинствам позволяет ещё и файлы сравнивать по их содержимому.
Но для этого потребуется установить небольшой плагин. Делается всё это непосредственно внутри самого редактора.
Итак, запускаете редактор – переходите в пункт меню «Плагины » — «Plugin Manager» — «Show Plugin Manager» .
В новом окне выбираете плагин « Compare» и жмёте кнопку « Install» .
После установки плагина можно приступать к сравнению содержимого файлов. Для этого нужно открыть два файла – перейти в пункт меню «Плагины» — « Compare» — « Compare (Alt+ D)» .
Результат сравнения файлов будет представлен в отдельных панелях. То есть в левой будет первый файл, а в правой второй. Напротив строк, в которых найдены отличия будет предупреждающий знак и подсветка фоновым цветом.
Строка, где найдены малые отличия, будет подсвечиваться жёлтым цветом, и обозначена восклицательным знаком.
Строка, которая полностью отличается, будет отмечена на одной панели красным минусом на другой зелёным плюсом. И подсвечена соответствующим цветом.
Разумеется, найдя отличие, вы сразу можете внести необходимые поправки и сохранить изменения.
Завершить сравнение файлов можно комбинацией клавиш Ctrl+ Alt+ D .
Способ 3. Сравнение содержимого двух файлов в программе WinMerge.
Эта программа позволяет сравнивать не только содержимое файлов, но и содержимое целых папок. Этим она похожа на Тотал Коммандер, только программа бесплатная. Скачать её можно .
Установка программы стандартная и не составит труда. Поэтому на этом останавливаться не будем, а сразу перейдём к процессу сравнения двух файлов.
Запустив программу, нужно выбрать файлы для сравнения. Открываете пункт меню «Файл» — «Открыть» .
Результат сравнения представлен в двух панелях, так же как и в других примерах. Только здесь ещё и выделяется именно тот фрагмент кода, который отличается в конкретной строке.
В этой программе можно также редактировать файлы. А когда будете закрывать окно сравнения, программа предложит сохранить изменения в файлах.
Способ 4. Сравнение файлов с помощью командной строки Windows.
Этот способ позволяет сравнивать содержимое файлов без сторонних программ. Достаточно воспользоваться стандартным инструментом операционной системы Windows.
Также этот способ не позволяет редактировать файлы. Для этого нужно использовать другие инструменты. Но, тем не менее с поставленной задачей вы справитесь без дополнительных программ.
Для этого открываете «Пуск» — «Все программы» — «Стандартные» — «Командная строка» . И вводите вот эту команду:
Fc /N путь к первому файлу путь ко второму файлу
выглядит это так:
Fc /N C:\Мои документы\file1.txt C:\Мои документы\file2.txt
Готовый результат будет выглядеть так:
Когда различий мало, то их достаточно быстро можно исправить использую для этого уже программы редактирования. Но когда различий много, то анализировать информацию с помощью этого способа достаточно трудно.
Итак, о способах сравнения файлов я вам рассказал, а теперь предлагаю посмотреть видеоурок в, котором я показываю сравнение двух файлов всеми способами.
Уверен, теперь если вам необходимо будет сравнить файлы, то вы легко сможете это сделать, использую один из предложенных способов.
На этом сегодня всё, всем желаю успехов и хорошего настроения и до встречи в новых статьях и видеоуроках!
С уважением, Максим Зайцев.
Нужно сравнить два файла Microsoft Excel? Вот два простых способа сделать это.
Существует множество причин, по которым вам может понадобиться взять один документ Excel и сравнить его с другим. Это может быть трудоемкой задачей
это требует большой концентрации, но есть способы облегчить себе жизнь.
Нужно ли вам внимательно посмотреть вручную или вы хотите, чтобы Excel выполнял некоторые тяжелые работы
от вашего имени, вот два простых способа сравнить несколько листов.
Как сравнить файлы Excel
Excel позволяет пользователям сразу выводить на экран две версии документа, чтобы быстро установить различия между ними:
- Сначала откройте рабочие книги, которые нужно сравнить.
- Перейдите к Вид> Окно> Вид рядом .
Сравнение файлов Excel на глаз
Для начала откройте Excel и все рабочие книги, которые вы хотите сравнить. Мы можем использовать ту же технику для сравнения листов в одном документе
или совершенно разные файлы.
Если из одной и той же книги получено более одного листа, ее необходимо отделить заранее. Для этого перейдите к Вид> Окно> Новое окно .
Это не разделит отдельные листы навсегда, просто откроет новый экземпляр вашего документа.
В этом меню будут перечислены все таблицы, которые в данный момент открыты. Если у вас есть только два открытых, они будут выбраны автоматически.
Сделайте свой выбор и нажмите Хорошо . Вы увидите, что обе таблицы появятся на экране.
Если это более удобно, вы можете использовать Расставить все кнопка для переключения между вертикальной и горизонтальной конфигурацией.
Один важный вариант, о котором следует знать, это Синхронная прокрутка переключения.
Включение этого параметра гарантирует, что при прокрутке одного окна другое будет двигаться синхронно. Это важно, если вы работаете с большой таблицей
и вы хотите продолжать проверять одно против другого. Если по какой-либо причине два листа не выровнены, просто нажмите Сбросить положение окна .
Сравнение файлов Excel с использованием условного форматирования
Во многих случаях лучший способ сравнения двух электронных таблиц может состоять в том, чтобы просто вывести их на экран одновременно. Однако возможно несколько автоматизировать процесс.
Использование условного форматирования
Мы можем проверить Excel на наличие расхождений между двумя листами. Это может сэкономить много времени, если все, что вам нужно найти, это различия между одной версией и другой.
Для этого метода нам нужно убедиться, что два листа, с которыми мы работаем, являются частью одной рабочей книги. Для этого щелкните правой кнопкой мыши имя листа, который вы хотите перенести, и выберите Переместить или скопировать .
Здесь вы можете использовать выпадающее меню, чтобы решить, в какой документ он будет вставлен.
Выберите все ячейки, которые заполнены на листе, где вы хотите, чтобы любые различия были выделены. Быстрый способ сделать это — щелкнуть ячейку в верхнем левом углу, а затем использовать ярлык
Ctrl + Shift + End .
Перейдите к Главная> Стили> Условное форматирование> Новое правило .
Выбрать Используйте формулу, чтобы определить, какие ячейки форматировать и введите следующее:
A1sheet_name!A1
Просто не забудьте выложить «sheet_name» для любого имени другого листа. Эта формула выполняет только проверку, когда ячейка на одном листе не точно соответствует соответствующей ячейке на другом листе, и помечает каждый экземпляр.
Выше вы можете увидеть результаты. Все ячейки, содержащие изменения, были выделены красным, что позволяет быстро и легко сравнить два листа.
Пусть Excel сделает тяжелую работу
Приведенная выше методика демонстрирует один способ, которым вы можете позволить Excel справиться с некоторыми тяжелыми задачами. Даже если вы уделяете пристальное внимание, есть вероятность, что вы можете пропустить изменение, если вы будете выполнять эту задачу вручную. Благодаря условному форматированию вы можете быть уверены, что ничего не проскальзывает в сеть
Excel хорош в монотонных и детально ориентированных работах. Как только вы овладеете его возможностями, вы сможете сэкономить немного времени и усилий, используя такую технику, как условное форматирование и немного изобретательности.
У вас есть совет по сравнению документов в Excel? Или вам нужна помощь с процессами, описанными в этом руководстве? В любом случае, почему бы не присоединиться к беседе в разделе комментариев ниже?
Статья даёт ответы на следующие вопросы:
- Как сравнить две таблицы в Excel?
- Как сравнивать сложные таблицы в Excel?
- Как производить сравнение таблиц в Excel с использованием функции ВПР()?
- Как формировать уникальные идентификаторы строк, если их уникальность изначально определяется набором значений в нескольких столбцах?
- Как фиксировать значения ячеек в формулах при копировании формул?
При работе с большими объемами информации пользователь может столкнуться с такой задачей, как сравнение двух табличных источников данных. При хранении данных в единой системе учета (например, системы на базе 1С Предприятие, системы, использующие SQL базы данных), для сравнения данных могут использоваться встроенные в систему или СУБД возможности. Как правило, для этого достаточно привлечь программиста, который напишет запрос к базе данных, или программный механизм отчета. С запросом может справиться и опытный пользователь, владеющий навыком написания запросов 1C, либо SQL.
Проблемы начинаются, когда требуется выполнить задачу сравнения данных срочно, а привлечение программиста и написание им запроса или программного отчета по времени может превысить установленные для решения задачи сроки. Еще одной не менее распространенной проблемой является потребность сравнения информации из различных источников. В таком случае постановка задачи для программиста будет звучать как интеграция двух систем. Решение такой задачи потребует более высокой квалификации программиста а также займет больше времени, чем разработка в единой системе.
Для решения обозначенных проблем идеальным приемом является использование для сравнения данных табличного редактора Microsoft Excel. Большинство распространенных систем управленческого и регламентированного учета поддерживают выгрузку в формат Excel. Эта задача потребует всего лишь определенной квалификации пользователя по работе с этим офисным пакетом и не потребует навыков программирования.
Рассмотрим решение задачи сравнения таблиц в Excel на примере. Мы имеем две таблицы, содержащие списки квартир. Источники выгрузки - 1С Предприятие (учёт строительства) и таблица в Excel (учёт продаж). Таблицы размещены в рабочей книге Excel на первом и втором листах соответственно.
Перед нами стоит задача сравнить эти списки по адресу. В первой таблице - все квартиры дома. Во второй таблице - только проданные квартиры и имя покупателя. Конечная цель - отобразить в первой таблице по каждой квартире имя покупателя (для тех квартир, которые были проданы). Задача осложняется тем, что адрес квартиры в каждой таблице является строительным и состоит из нескольких полей: 1) адрес корпуса (дома), 2) секция (подъезд), 3) этаж, 4) номер на этаже (например, от 1 до 4).
Для сравнения двух таблиц Excel нам нужно добиться того, чтобы в обеих таблицах каждая строка идентифицировалась бы одним полем, а не четырьмя. Получить такое поле можно объединив значения четырех полей адреса функцией Сцепить(). Назначение функции Сцепить() - объединение нескольких текстовых значений в одну строку. Значения в функции перечисляются через символ ";". В качестве значений могут выступать как адреса ячеек, так и произвольный текст, заданный в кавычках.
Шаг 1.
Вставим в начале первой таблицы пустую колонку "A" и пропишем в ячейке этой колонки напротив первой строки с данными формулу:
=СЦЕПИТЬ(B3;"-";C3;"-";D3;"-";E3)
Для удобства визуального восприятия между значениями объединяемых ячеек мы установили символы "-".
Шаг 2. Скопируем формулу в нижеследующие ячейки колонки А.
Шаг 4.
Для сравнения таблиц Excel по значениям следует воспользоваться функцией ВПР().
Назначение функции ВПР() - поиск значения в крайнем левом столбце таблицы и возвращение значения ячейки, находящейся в указанном столбце той же строки. Первый параметр - искомое значение. Второй параметр - таблица, в которой будет осуществляться поиск значения. Третий параметр - номер столбца, из ячейки которого в найденной строке будет возвращено значение. Четвертый параметр - тип поиска: ложь - точное совпадение, истина - приближенное совпадение.
Поскольку выходная информация должна быть размещена в первой таблице (именно в нее требовалось добавить имена покупателей), то формулу будем прописывать в ней.
Сформируем в свободной колонке справа от таблицы напротив первой строки данных формулу:
=ВПР(A3;Лист2!$A$3:$F$10;6;ЛОЖЬ)
При копировании формул "умный" Excel автоматически изменяет адресацию ячеек. В нашем случае искомое значение для каждой строки будет меняться: A3,A4 и т.д., а адрес таблицы, в которой ведется поиск, должен оставаться неизменным. Для этого зафиксируем ячейки в параметре адреса таблицы символами "$". Вместо "Лист2!A3:F10" делаем "Лист2!$A$3:$F$10".
Довольно часто перед пользователями Excel стоит задача сравнения двух таблиц или списков для выявления в них отличий или недостающих элементов. Каждый юзер справляется с этой задачей по своему, но чаще всего на решение указанного вопроса тратится довольно большое количество времени, так как далеко не все подходы к данной проблеме являются рациональными. В то же время, существует несколько проверенных алгоритмов действий, которые позволят сравнить списки или табличные массивы в довольно сжатые сроки с минимальной затратой усилий. Давайте подробно рассмотрим данные варианты.
Существует довольно много способов сравнения табличных областей в Excel, но все их можно разделить на три большие группы:
Именно исходя из этой классификации, прежде всего, подбираются методы сравнения, а также определяются конкретные действия и алгоритмы для выполнения задачи. Например, при проведении сравнения в разных книгах требуется одновременно открыть два файла Excel.
Кроме того, следует сказать, что сравнивать табличные области имеет смысл только тогда, когда они имеют похожую структуру.
Способ 1: простая формула
Самый простой способ сравнения данных в двух таблицах – это использование простой формулы равенства. Если данные совпадают, то она выдает показатель ИСТИНА, а если нет, то – ЛОЖЬ. Сравнивать можно, как числовые данные, так и текстовые. Недостаток данного способа состоит в том, что ним можно пользоваться только в том случае, если данные в таблице упорядочены или отсортированы одинаково, синхронизированы и имеют равное количество строчек. Давайте посмотрим, как использовать данный способ на практике на примере двух таблиц, размещенных на одном листе.
Итак, имеем две простые таблицы со списками работников предприятия и их окладами. Нужно сравнить списки сотрудников и выявить несоответствия между столбцами, в которых размещены фамилии.
- Для этого нам понадобится дополнительный столбец на листе. Вписываем туда знак «=»
. Затем кликаем по первому наименованию, которое нужно сравнить в первом списке. Опять ставим символ «=»
с клавиатуры. Далее кликаем по первой ячейке колонки, которую мы сравниваем, во второй таблице. Получилось выражение следующего типа:
Хотя, конечно, в каждом конкретном случае координаты будут отличаться, но суть останется одинаковой.
- Щелкаем по клавише Enter , чтобы получить результаты сравнения. Как видим, при сравнении первых ячеек обоих списков программа указала показатель «ИСТИНА» , что означает совпадение данных.
- Теперь нам нужно провести аналогичную операцию и с остальными ячейками обеих таблиц в тех колонках, которые мы сравниваем. Но можно просто провести копирование формулы, что позволит существенно сэкономить время. Особенно данный фактор важен при сравнивании списков с большим количеством строк.
Процедуру копирования легче всего выполнить при помощи маркера заполнения. Наводим курсор на правый нижний угол ячейки, где мы получили показатель «ИСТИНА» . При этом он должен преобразоваться в черный крестик. Это и есть маркер заполнения. Жмем левую кнопку мыши и тянем курсор вниз на количество строчек в сравниваемых табличных массивах.
- Как видим, теперь в дополнительном столбце отобразились все результаты сравнения данных в двух колонках табличных массивов. В нашем случае не совпали данные только в одной строке. При их сравнении формула выдала результат «ЛОЖЬ» . По всем остальным строчкам, как видим, формула сравнения выдала показатель «ИСТИНА» .
- Кроме того, существует возможность с помощью специальной формулы подсчитать количество несовпадений. Для этого выделяем тот элемент листа, куда оно будет выводиться. Затем щелкаем по значку «Вставить функцию» .
- В окне Мастера функций в группе операторов «Математические» выделяем наименование СУММПРОИЗВ . Щелкаем по кнопке «OK» .
- Активируется окно аргументов функции СУММПРОИЗВ
, главной задачей которой является вычисление суммы произведений выделенного диапазона. Но данную функцию можно использовать и для наших целей. Синтаксис у неё довольно простой:
СУММПРОИЗВ(массив1;массив2;…)
Всего в качестве аргументов можно использовать адреса до 255 массивов. Но в нашем случае мы будем использовать всего два массива, к тому же, как один аргумент.
Ставим курсор в поле «Массив1» и выделяем на листе сравниваемый диапазон данных в первой области. После этого в поле ставим знак «не равно» (<> ) и выделяем сравниваемый диапазон второй области. Далее обворачиваем полученное выражение скобками, перед которыми ставим два знака «-» . В нашем случае получилось такое выражение:
--(A2:A7<>D2:D7)
Щелкаем по кнопке «OK» .
- Оператор производит расчет и выводит результат. Как видим, в нашем случае результат равен числу «1» , то есть, это означает, что в сравниваемых списках было найдено одно несовпадение. Если бы списки были полностью идентичными, то результат бы был равен числу «0» .
Таким же образом можно производить сравнение данных в таблицах, которые расположены на разных листах. Но в этом случае желательно, чтобы строки в них были пронумерованы. В остальном процедура сравнения практически точно такая, как была описана выше, кроме того факта, что при внесении формулы придется переключаться между листами. В нашем случае выражение будет иметь следующий вид:
B2=Лист2!B2
То есть, как видим, перед координатами данных, которые расположены на других листах, отличных от того, где выводится результат сравнения, указывается номер листа и восклицательный знак.
Способ 2: выделение групп ячеек
Сравнение можно произвести при помощи инструмента выделения групп ячеек. С его помощью также можно сравнивать только синхронизированные и упорядоченные списки. Кроме того, в этом случае списки должны располагаться рядом друг с другом на одном листе.
Способ 3: условное форматирование
Произвести сравнение можно, применив метод условного форматирования. Как и в предыдущем способе, сравниваемые области должны находиться на одном рабочем листе Excel и быть синхронизированными между собой.
Существует ещё один способ применения условного форматирования для выполнения поставленной задачи. Как и предыдущие варианты, он требует расположения обоих сравниваемых областей на одном листе, но в отличие от ранее описанных способов, условие синхронизации или сортировки данных не будет являться обязательным, что выгодно отличает данный вариант от ранее описанных.
При желании можно, наоборот, окрасить несовпадающие элементы, а те показатели, которые совпадают, оставить с заливкой прежним цветом. При этом алгоритм действий практически тот же, но в окне настройки выделения повторяющихся значений в первом поле вместо параметра «Повторяющиеся» следует выбрать параметр «Уникальные» . После этого нажать на кнопку «OK» .
Таким образом, будут выделены именно те показатели, которые не совпадают.
Способ 4: комплексная формула
Также сравнить данные можно при помощи сложной формулы, основой которой является функция СЧЁТЕСЛИ . С помощью данного инструмента можно произвести подсчет того, сколько каждый элемент из выбранного столбца второй таблицы повторяется в первой.
Оператор СЧЁТЕСЛИ относится к статистической группе функций. Его задачей является подсчет количества ячеек, значения в которых удовлетворяют заданному условию. Синтаксис данного оператора имеет такой вид:
СЧЁТЕСЛИ(диапазон;критерий)
Аргумент «Диапазон» представляет собой адрес массива, в котором производится подсчет совпадающих значений.
Аргумент «Критерий» задает условие совпадения. В нашем случае он будет представлять собой координаты конкретных ячеек первой табличной области.
Конечно, данное выражение для того, чтобы сравнить табличные показатели, можно применять и в существующем виде, но есть возможность его усовершенствовать.
Сделаем так, чтобы те значения, которые имеются во второй таблице, но отсутствуют в первой, выводились отдельным списком.
- Прежде всего, немного переработаем нашу формулу СЧЁТЕСЛИ , а именно сделаем её одним из аргументов оператора ЕСЛИ . Для этого выделяем первую ячейку, в которой расположен оператор СЧЁТЕСЛИ . В строке формул перед ней дописываем выражение «ЕСЛИ» без кавычек и открываем скобку. Далее, чтобы нам легче было работать, выделяем в строке формул значение «ЕСЛИ» и жмем по иконке «Вставить функцию» .
- Открывается окно аргументов функции ЕСЛИ
. Как видим, первое поле окна уже заполнено значением оператора СЧЁТЕСЛИ
. Но нам нужно дописать кое-что ещё в это поле. Устанавливаем туда курсор и к уже существующему выражению дописываем «=0»
без кавычек.
После этого переходим к полю «Значение если истина» . Тут мы воспользуемся ещё одной вложенной функцией – СТРОКА . Вписываем слово «СТРОКА» без кавычек, далее открываем скобки и указываем координаты первой ячейки с фамилией во второй таблице, после чего закрываем скобки. Конкретно в нашем случае в поле «Значение если истина» получилось следующее выражение:
СТРОКА(D2)
Теперь оператор СТРОКА будет сообщать функции ЕСЛИ номер строки, в которой расположена конкретная фамилия, и в случае, когда условие, заданное в первом поле, будет выполняться, функция ЕСЛИ будет выводить этот номер в ячейку. Жмем на кнопку «OK» .
- Как видим, первый результат отображается, как «ЛОЖЬ» . Это означает, что значение не удовлетворяет условиям оператора ЕСЛИ . То есть, первая фамилия присутствует в обоих списках.
- С помощью маркера заполнения, уже привычным способом копируем выражение оператора ЕСЛИ на весь столбец. Как видим, по двум позициям, которые присутствуют во второй таблице, но отсутствуют в первой, формула выдает номера строк.
- Отступаем от табличной области вправо и заполняем колонку номерами по порядку, начиная от 1 . Количество номеров должно совпадать с количеством строк во второй сравниваемой таблице. Чтобы ускорить процедуру нумерации, можно также воспользоваться маркером заполнения.
- После этого выделяем первую ячейку справа от колонки с номерами и щелкаем по значку «Вставить функцию» .
- Открывается Мастер функций . Переходим в категорию «Статистические» и производим выбор наименования «НАИМЕНЬШИЙ» . Щелкаем по кнопке «OK» .
- Функция НАИМЕНЬШИЙ
, окно аргументов которой было раскрыто, предназначена для вывода указанного по счету наименьшего значения.
В поле «Массив» следует указать координаты диапазона дополнительного столбца «Количество совпадений» , который мы ранее преобразовали с помощью функции ЕСЛИ . Делаем все ссылки абсолютными.
В поле «K» указывается, какое по счету наименьшее значение нужно вывести. Тут указываем координаты первой ячейки столбца с нумерацией, который мы недавно добавили. Адрес оставляем относительным. Щелкаем по кнопке «OK» .
- Оператор выводит результат – число 3 . Именно оно наименьшее из нумерации несовпадающих строк табличных массивов. С помощью маркера заполнения копируем формулу до самого низа.
- Теперь, зная номера строк несовпадающих элементов, мы можем вставить в ячейку и их значения с помощью функции ИНДЕКС . Выделяем первый элемент листа, содержащий формулу НАИМЕНЬШИЙ . После этого переходим в строку формул и перед наименованием «НАИМЕНЬШИЙ» дописываем название «ИНДЕКС» без кавычек, тут же открываем скобку и ставим точку с запятой (; ). Затем выделяем в строке формул наименование «ИНДЕКС» и кликаем по пиктограмме «Вставить функцию» .
- После этого открывается небольшое окошко, в котором нужно определить, ссылочный вид должна иметь функция ИНДЕКС или предназначенный для работы с массивами. Нам нужен второй вариант. Он установлен по умолчанию, так что в данном окошке просто щелкаем по кнопке «OK» .
- Запускается окно аргументов функции ИНДЕКС
. Данный оператор предназначен для вывода значения, которое расположено в определенном массиве в указанной строке.
Как видим, поле «Номер строки» уже заполнено значениями функции НАИМЕНЬШИЙ . От уже существующего там значения следует отнять разность между нумерацией листа Excel и внутренней нумерацией табличной области. Как видим, над табличными значениями у нас только шапка. Это значит, что разница составляет одну строку. Поэтому дописываем в поле «Номер строки» значение «-1» без кавычек.
В поле «Массив» указываем адрес диапазона значений второй таблицы. При этом все координаты делаем абсолютными, то есть, ставим перед ними знак доллара уже ранее описанным нами способом.
Жмем на кнопку «OK» .
- После вывода результат на экран протягиваем функцию с помощью маркера заполнения до конца столбца вниз. Как видим, обе фамилии, которые присутствуют во второй таблице, но отсутствуют в первой, выведены в отдельный диапазон.
Способ 5: сравнение массивов в разных книгах
При сравнении диапазонов в разных книгах можно использовать перечисленные выше способы, исключая те варианты, где требуется размещение обоих табличных областей на одном листе. Главное условие для проведения процедуры сравнения в этом случае – это открытие окон обоих файлов одновременно. Для версий Excel 2013 и позже, а также для версий до Excel 2007 с выполнением этого условия нет никаких проблем. Но в Excel 2007 и Excel 2010 для того, чтобы открыть оба окна одновременно, требуется провести дополнительные манипуляции. Как это сделать рассказывается в отдельном уроке.
Как видим, существует целый ряд возможностей сравнить таблицы между собой. Какой именно вариант использовать зависит от того, где именно расположены табличные данные относительно друг друга (на одном листе, в разных книгах, на разных листах), а также от того, как именно пользователь желает, чтобы это сравнение выводилось на экран.
