Как сопоставить два спектра в Excel 2007/2010/2013/2016

Как сопоставить два спектра в Excel 2007/2010/2013/2016?

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

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

Резвое сопоставление значений в 2-ух спектрах Excel

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

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

При сопоставлении значений в Excel существует один аспект. Числа могут быть отформатированы как текст, что не постоянно можно найти зрительно (читать подробнее). Другими словами число в Excel быть может как числовым значением, так и текстовым и эти два значения не равны меж собой. Весьма нередко это явление служит предпосылкой различного рода ошибок. Для того, чтоб исключить такие ошибки, употребляется функция «Ассоциировать числа, как текст», которая включена по дефлоту. Внедрение данной функции дозволяет ассоциировать не числовые, а перевоплощенные из их текстовые значения.

Надстройка дозволяет:

1. Одним кликом мыши вызывать диалоговое окно макроса прямо из панели инструментов Excel;

2. отыскивать элементы спектра №1, которых нет в спектре №2;

3. отыскивать элементы спектра №2, которых нет в спектре №1;

4. отыскивать элементы спектра №1, которые есть в спектре №2;

5. отыскивать элементы спектра №2, которые есть в спектре №1;

6. выбирать один из 9 цветов заливки для ячеек с разыскиваемыми значениями;

7. стремительно выделять спектры, используя опцию «Ограничить спектры», при всем этом можно выделять полностью строчки и столбцы, сокращение выделенного спектра до применяемого делается автоматом;

8. заместо сопоставления числовых значений употреблять сопоставление текстовых значений с помощью функции «Сопоставить числа как текст»;

9. ассоциировать значения в ячейках спектра, не беря во внимание излишние пробелы;

10. ассоциировать значения в ячейках спектра, не беря во внимание регистр.

Как сопоставить два столбца с внедрением макроса (надстройки) для Excel?

Сравнение столбцов является личным случаем сопоставления случайных диапазонов. В спектрах №1 и №2 выделяем два столбца, при этом выделять можно конкретно столбцы, а не протягивать мышью рамку выделения по спектрам с ячейками (для удобства по дефлоту включена функция «Ограничить спектры», которая в случае выделения столбцов либо строк полностью, ограничивает такие выделения применяемым спектром), избираем нужное действие для поиска или различий, или совпадений, избираем цвет заливки ячеек и запускаем программку. Ниже виден итог поиска совпадающих значений в 2-ух столбцах.

Как сопоставить две строчки в Excel?

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

Задачки на сопоставление строк и столбцов можно решать также с помощью сортировки.

Как сопоставить столбцы в Гугл Sheets

Гугл Sheets — это массивное и обычное в освоении пасмурное приложение для работы с электрическими таблицами. Таблицы соперничают с Microsoft Excel на рынке электрических таблиц, у их нет таковой же широты и глубины функций.

Таблицы Гугл, Документы Гугл и Презентации Гугл являются частью офисного веб-пакета, предлагаемого Гугл безвозмездно. Эти приложения интегрируются с Гугл Диском, хранилищем файлов Гугл и службой синхронизации.

Одна из таковых рутинных задач с электрическими таблицами — это сопоставление инфы в различных столбцах. Таблицы наиболее чем способны выполнить такое сопоставление.

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

Столбцы сопоставления Гугл Таблиц

Сравните два столбца в Гугл Таблицах

Один из обычных методов сопоставления столбцов в Таблицах — употреблять формулу. Допустим, у нас есть два столбца данных, столбец A и столбец B. Если мы желаем сопоставить столбцы и отметить любые различия, мы можем употреблять формулу.

Формула ЕСЛИ — мощнейший инструмент в Таблицах (также в Excel). В операторе IF есть три аргумента.

1-ый аргумент является тест , чтоб быть выполнена, 2-ой аргумент является результатом , чтоб возвратиться , если тест не правильно, а 3-ий аргумент является результатом , чтоб возвратиться , если тест является настоящим. Это достаточно просто осознать, но тяжело прочесть в формуле, потому давайте разглядим ее подробнее.

1. Откройте собственный лист на страничке, которую вы желаете сопоставить.
2. С данными в столбцах A и B выделите ячейку C1.
3. Вставьте ‘ = if ( A1 = B1 , «» , «Mismatch» ) »в ячейку C1. Логика такая: если A1 и B1 схожи (т. Е. A1 = B1), формула возвращает пустую строчку, а если они не совпадают (A1 не равно B1), формула возвращает «Несоответствие».
4. Кликните левой клавишей мыши в правом нижнем углу ячейки C1 и перетащите вниз. Это копирует формулу из C1 во все ячейки столбца C.

Сейчас для каждой строчки, в которой A и B не схожи, столбец C будет содержать слово «Несоответствие». Если вы видите пустую ячейку, то формула ничего не возвратила, что показывает на совпадение столбцов.

Сравнение данных из нескольких столбцов

Сравнение данных меж 2-мя столбцами — это отлично и полезно … но что, если у вас есть несколько столбцов данных и для вас необходимо провести сопоставление? Что ж, Sheets может совладать и с сиим, используя функцию ARRAYFORMULA.

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

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

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

Итак, у нас есть данные Джейн. Но потом Боб посылает свои числа для такого же набора инфы, и мы подозреваем, что меж этими 2-мя наборами данных могут быть некие различия. (В этом примере вы сможете просто зрительно найти различия, но представим, что это электрическая таблица с тыщами записей.) См. Ниже числа Джейн и Боба, расположенные рядом.

Если мы желаем проверить, совпадают ли цены за единицу, обозначенные Джейн и Бобом, мы можем употреблять для этого ARRAYFORMULA. Мы желаем докладывать о всех различиях и распечатывать их, начиная с ячейки I3, потому в I3 мы вводим эту формулу:

= ARRAYFORMULA (СЧЁТЕСЛИ (ЕСЛИ (C12: C336 <D12: D336, «Да», «Нет»), «Да»))

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

Сейчас мы лицезреем, что у SKU A10305 есть разница, и мы можем узнать, у кого есть верная информация, а у кого ошибка.

Внедрение Power Tools для сопоставления столбцов

Иной подход — употреблять инструмент сопоставления в одном из доп пакетов для Гугл Таблиц. Один инструмент известен как Power Tools, весьма нужный набор надстроек для расширения функциональности Гугл Sheets.

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

Ранее они были бесплатными на неопределенный срок, но сейчас Power Tools просит подписки по окончании бесплатного демонстрационного периода. Я бы произнес, что Power Tools стоит собственных средств для активных юзеров электрических таблиц в 29,95 баксов в год либо 89,95 баксов за бессрочную подписку.

Power Tools имеет огромное количество массивных функций, но мы просто разглядим его способ сопоставления столбцов тут.

1. Опосля того, как Power Tools будет добавлен в таблицу Гугл, перейдите к Ons Add- ниспадающее меню
2. Изберите Электроинструменты
3. Потом изберите Запуск
4. Кликните пункт меню «Данные», потом изберите «Сопоставить два листа».
5. Введите спектры столбцов, которые вы желаете сопоставить. Направьте внимание, что вы сможете ассоциировать несколько столбцов сразу и даже ассоциировать на различных листах!
6. Изберите, желаете ли вы отыскать неповторимые значения либо повторяющиеся значения.
7. Изберите, как вы желаете, чтоб Power Tools показывал результаты сопоставления. Вы сможете избрать цвет в циклических либо неповторимых ячейках, для перемещения либо копирования данных в новейшие столбцы и остальные варианты.

Резвый метод сопоставить различия меж текстом и таблицами

Если для вас не необходимы хлопоты с написанием формул либо внедрением надстройки и вы просто желаете стремительно сопоставить значения либо текст в 2-ух документах, есть бесплатный онлайн-инструмент, который сделает за вас томную работу. Он именуется Diffchecker и, кажется, работает отлично. Его даже рекомендуется употреблять на форуме Документов Гугл.

.
1. Вставьте один набор текста либо значений в левую панель, а иной столбец либо текст — в правую.
2. Изберите «Найди отличия!»
3. Веб-сайт сравнит две панели и выделит любые различия.

Diffchecker полезен, если вы пытаетесь различать столбцы и нуждаетесь лишь в результатах.

Как насчет того, чтоб при помощи Excel ассоциировать столбцы при помощи этого инструмента? Ну естественно можно!

Сравните два столбца в Microsoft Excel

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

Способ 1 для сопоставления столбцов на наличие дубликатов в Excel:

1. Выделите два столбца, которые желаете проверить.
2. Изберите «Условное форматирование» на ленте «Основная».
3. Изберите «Выделить правила для ячеек и повторяющиеся значения».
4. Изберите формат для отображения и нажмите ОК.

Способ 2 для сопоставления столбцов на предмет различий в Excel:

1. Выделите ячейку 1 в столбце C.
2. Вставьте ‘= ЕСЛИ (СЧЁТЕСЛИ ($ A: $ A, $ B2) = 0, «Нет совпадений в A», «») »в строчку формул.
3. Вы должны узреть «Нет совпадений в A» в столбце C, если эти два столбца различаются.

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

Формула ЕСЛИ в Excel 3 условия

Excel редактор, стремительно решающий сложные задачки: рассчитывает проценты, проводит анализ, строит графики. Одна из самых фаворитных его функций — ЕСЛИ. Существует много методов ее использования. Разглядим на примерах, как работает формула ЕСЛИ в Excel 3 условия.

Что же все-таки это такое

Рекомендую перед предстоящим чтением ознакомиться со статьей: «Правильное написание формул».
Функция ЕСЛИ инспектирует условие. Когда оно производится (ИСТИНА) ворачивается значение. Если нет — другое.
Выглядите так: =ЕСЛИ(логическое выражение; правда; ересь).
Где:

1. Выражение задается в виде числа либо фразы. К примеру, с текстом «без налога»;
2. Правда. Значение, которое отобразится если выражение верно;
3. Ересь — когда некорректно.

Обычный пример формулы если в Эксель (Excel). Проверка равенства чисел из 2-ух ячеек.
Разглядим примеры формулы ЕСЛИ в Ексель (Excel).

Сравнение

Сделаем условие:
Проверяется ячейка А1 и сравнивается с числом 20. Это выражение. Если число больше отобразится надпись «больше 20». По другому — меньше.

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

Пример 2

Перед экзаменом необходимо здать зачет. Проверяются текстовые данные в формуле В2. Проверяем 2 условия.

Несколько критерий

Что созодать, если необходимо учитывать 2-3 варианта. К примеру, 3 условия. Проверяется значение ячейки А5. Если оно равно нулю, возвращает текст «ноль». Если «правда». Когда значение ересь, происходит иная проверка, является ли значение меньше 0, и ворачивается «Меньше нуля», ЕСЛИ итог положительный. По другому — «больше нуля».

Функция если в Excel больше либо равно

Рассчитаем скидки. Условия, последующие:

1. До 100 000 — скидка 0 процентов;
2. От 100 001 до 30000 — 3;
3. 300001 до 500 000 — 5;
4. Наиболее 500001 — 7.

В ячейке А1 размер продаж. Формула воспримет вид:

Функция если в Excel больше но меньше

Используйте ЕСЛИ вместе с функцией И. Если А2 больше В2 но меньше С1 вернется значение ИСТИНА. По другому ЛОЖЬ.

Вывод

Мы разглядели, как работает функция ЕСЛИ в Excel. Используйте описанные методы. Сделайте работу с табличным редактором удобнее.

Сравнение ячеек в excel

При работе в Excel у неких категорий людей только нередко возникает задачка сопоставить 2 столбца в различных таблицах и осознать, в чём они совпадают, а в чём разнятся. С помощью формул рабочего листа эта задачка обычно решается или с помощью ВПР , или с помощью СЧЁТЕСЛИ . Но, дело это весьма муторное, просит бдительности, усидчивости, а, если таковых таблиц много, то вы очень стремительно запутаетесь и устанете.

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

Вот перед вами упрощённый учебный пример таковой задачки.

Путём нехитрых развлечений с функцией ВПР , вы сможете получить таковой итог:

Другими словами мы отыскали пересечение наших 2-ух диапазонов там, где ВПР возвратил значение. Отфильтровав по #Н/Д в каждой таблице, мы получаем перечень значений того, что есть слева, но отсутствует справа, и того, что есть справа, но отсутствует слева. Это максимум того, что можно выдавить из обычного подхода с ВПР.

Недочеты обычного подхода:

• Мы издержали много сил. Если таковых таблиц у нас много, то таковой способ не годится. Это и трудоёмко и очень просто запутаться.
• Как мы знаем, ВПР отыскивает 1-ое совпадение и означает о том, что, к примеру, в левой таблице два значения 040310475653, а в правой лишь одно, мы можем и не выяснить, в особенности, если таблицы огромные.
• В случае с #Н/Д мы также не будем осознавать структуру аномалий наших данных. К примеру, то, что значение 40310307297 справа встречается два раза.
• У нас не будет инфы, сколько совершенно неповторимых значений встречается в каждой таблице.

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

Главные способности предлагаемого инструмента:

• Исчерпающее сопоставление 2-ух списков с выявлением всех вероятных аномалий в данных
• Сравнение осуществляется как на месте, где размещены обозначенные юзером спектры (вставляется колонка справа), так и на отдельном новеньком листе рабочей книжки
• Цветовое акцентирование результатов сопоставления
• Предоставление детализированной статистики по результатам сопоставления (вставляется в комментарий к ячейке заголовка столбца, где происходит сопоставление)
• Автоопределение столбца с данными при указании диапазонов (довольно указать одну ячейку)
• Две модели сопоставления: обычная и рядовая.
• Учёт регистра текста, если в этом есть необходимость
• Учёт наличия / отсутствия заголовка у диапазонов
• 2 типа сортировки
• Возможность поменять обычные статусы сопоставления на пользовательские

Применяемая теория сопоставления списков

Теория весьма ординарна и легка для осознания при минимуме усилий.

1. 2 сравниваемых спектра будем именовать ЛЕВЫЙ и ПРАВЫЙ . Это разумеется и естественно, если таблицы размещаются на одном листе. В случае различных листов, левым спектром можно именовать ту таблицу, чей рабочий лист размещается левее листа 2-ой таблицы.
2. Каждое значение в сравниваемых перечнях получит СТАТУС , значащий то, как данное значение соотносится с аналогичным значением во 2-ой таблице и (!) с таковыми же значениями в собственной таблице, если данное значение повторяется.
3. Есть 2 набора статусов: упрощённый и обыденный.
4. Облегченные статусы: BOTH , LEFT , RIGHT .
   • BOTH — значение есть в обоих столбцах. К примеру, если значение «5» встречается в левой таблице 2 раза, а в правой 3 раза, то все эти строчки получат статус BOTH
   • LEFT — значение встречается лишь в левой таблице
   • RIGHT — значение встречается лишь в правой таблице
5. Обыденные статусы (вы сможете избрать, какой набор статусов будете употреблять в форме управления данным инвентарем):
   • Группа BOTH :
     • L1R1 — в левои и правом спектрах есть по одному значению
     • LnRn — в левои и правом спектрах есть по нескольку (наиболее 1) значений с каждой стороны.
     • L1Rn — слева — одно значение, справа — несколько
     • LnR1 — слева — несколько значений, справа — одно
   • Группа LEFT :
     • L1R0 — в левом спектре одно значение, в правом такового нет
     • LnR0 — в левом спектре несколько схожих значений, в правом таковых нет
   • Группа RIGHT :
     • L0R1 — в левом спектре нет таковых значений, в правом — одно
     • L0Rn — в левом спектре нет таковых значений, в правом — несколько (наиболее 1-го)

Результаты работы инструмента

Ах так смотрятся результаты сопоставления примера из начала статьи

Мы лицезреем, что выходит последующая картина:

1. В левом перечне есть 12 значений, которые отсутствуют в правом (статус L1R0 )
2. В левом перечне задвоено значение 040310475653 (статус LnR1 )
3. Лишь 3 значения совпали в перечнях по принципу «один к одному» (статус L1R1 )
4. В правом перечне есть 7 значений, которые отсутствуют в левом перечне (статус L0R1 )
5. В правом перечне есть 4 строчки (две по 2 значения), которых нет слева и которые задвоены справа (статус L0Rn )
6. И слева, и справа есть 2 значения, которые встречаются единожды слева, но которые задвоены справа (статус L1Rn )

и режим объединенных списков:

Видите ли, моя утилита всё разложила по полочкам!

Пользовательский интерфейс

Описание частей управления:

1. Списки для сопоставления — Левый перечень и Правый перечень
   • Сравниваемые списки должны быть в составе одной книжки Excel
   • Списки могут размещаться на различных листах книжки
   • Если они размещаются на одном листе, то колонка левого перечня должен быть вправду левее правой колонки
   • Рекомендуется заполнять данные поля ввода с помощью клавиш Выбор
   • Через клавишу Выбор довольно указать одну ячейку подходящей колонки, программка автоматом расширит ваш выбор на всю область в составе этого столбца, к которой относится обозначенная ячейка. В данной связи пустые ячейки в сравниваемых колонках рекомендуется подменять на какие-то текстовые константы, типа «Пусто».
2. Различное — Выделить цветом
   • Если указана данная функция, то колонки со сравниваемыми значениями и колонка со статусами получают обычное цветовое акцентирование, эталоны которого вы сможете созидать ниже
   • Обыкновенные статусы:
   • Обыденные статусы:
3. Различное — Соединить списки
   • По дефлоту эта функция не выбрана и списки сравниваются в том месте, где они размещены. Справа от колонки с данными вставляется столбец со статусами сопоставления.
   • Если эта функция выбрана, то создаётся новейший лист, куда помещается таблица с объединенными неповторимыми значениями из сравниваемых списков. Данная таблица имеет 4 столбца: Значения , Кол-во слева , Кол-во справа , Статус .
   • Данный режим комфортен для детализированного анализа всех аномалий в данных. А именно лишь так видны определенные количества строк у статусов с буковкой «n» . К примеру, LnRn либо L0Rn .
   • Лишь в режиме объединения перечня таблица может сортироваться, потому что в проивном случае это могло бы разрушить ваши данные — ведь обозначенный столбец быть может в составе большенный таблицы, но определение координат данной таблицы (для сортировки) уже очень выходит за рамки данного инструмента.
4. Различное — Учёт регистра
   • Полезно при анализе текстовых списков, где быть может важен регистр текстовых значений.
5. Различное — Статистика в комм .
   • При всем этом создаётся комментарий к ячейке, содержащей заголовок столбца со статусом сопоставления данных.
   • Таковых объяснений 2, если списки сравниваются на собственных уникальных местах, и один, если они объединены.
   • Комментарий смотрится приблизительно так
6. Различное — Обыкновенные статусы
   • Осуществляется выбор меж ординарными и обыкновенными статусами. Данные статусы были описаны выше.
7. Различное — Есть заглавия
   • Указываем имеют ли ваши списки заглавия столбцов. По умолчания включено.
8. Где сравниваем? — Оригинальное пространство либо Отдельный лист
   • Настройка блокируется, если выбрана функция Соединить списки.
   • В неприятном случае вы в праве избрать пространство без помощи других.
9. Сортировка — По значению либо По статусу
   • Настройка блокируется (по дефлоту), если не выбрана функция Соединить списки, потому что сотрировка осуществляется лишь на отдельном листе.
10. Добавочно — Другие статусы
    • По дефлоту отключено. Включаем, если вы желаете по каким-то своим причинам употреблять свои статусы.
11. Добавочно — Обыденные статусы либо Упрощённые статусы
    • Статусы сохраняются на сокрытом листе той книжки, где вы пользовались сиим инвентарем, и в последующий раз подгружаются оттуда на форму. Другими словами для вас не придётся их вводить повсевременно.
12. Сопоставить списки
    • Клавиша, которую нужно надавить для пуска процедуры сопоставления опосля того, как вы избрали все нужные для вас функции.