Как в excel найти совпадения

Содержание

Поиск совпадений в двух списках

Как в excel найти совпадения
5049 08.07.2017 Скачать пример

Тема сравнения двух списков поднималась уже неоднократно и с разных сторон, но остается одной из самых актуальных везде и всегда. Давайте рассмотрим один из ее аспектов – подсчет количества и вывод совпадающих значений в двух списках. Предположим, что у нас есть два диапазона данных, которые мы хотим сравнить:


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

Для этого нужно выделить ячейки с элементами списка и на вкладке Формулы нажать кнопку Менеджер Имен – Создать (Formulas – Name Manager – Create).

Также можно превратить таблицы в “умные” с помощью сочетания клавиш Ctrl+T или кнопки Форматировать как таблицу на вкладке (Home – Format as Table).

Подсчет количества совпадений

Для подсчета количества совпадений в двух списках можно использовать следующую элегантную формулу:


В английской версии это будет =SUMPRODUCT(COUNTIF(Список1;Список2))

Давайте разберем ее поподробнее, ибо в ней скрыто пару неочевидных фишек.

Во-первых, функция СЧЁТЕСЛИ (COUNTIF). Обычно она подсчитывает количество искомых значений в диапазоне ячеек и используется в следующей конфигурации:

=СЧЁТЕСЛИ(Где_искать; Что_искать)

Обычно первый аргумент – это диапазон, а второй – ячейка, значение или условие (одно!), совпадения с которым мы ищем в диапазоне. В нашей же формуле второй аргумент – тоже диапазон.

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

По сути, это равносильно целому столбцу дополнительных вычислений, свернутому в одну формулу:


Во-вторых, функция СУММПРОИЗВ (SUMPRODUCT) здесь выполняет две функции – суммирует вычисленные СЧЁТЕСЛИ совпадения и заодно превращает нашу формулу в формулу массива без необходимости нажимать сочетание клавиш Ctrl+Shift+Enter. Формула массива необходима, чтобы функция СЧЁТЕСЛИ в режиме с двумя аргументами-диапазонами корректно отработала свою задачу.

Вывод списка совпадений формулой массива

Если нужно не просто подсчитать количество совпадений, но и вывести совпадающие элементы отдельным списком, то потребуется не самая простая формула массива:


В английской версии это будет, соответственно:

=INDEX(Список1;MATCH(1;COUNTIF(Список2;Список1)*NOT(COUNTIF($E$1:E1;Список1));0))

Логика работы этой формулы следующая:

  • фрагмент СЧЁТЕСЛИ(Список2;Список1), как и в примере до этого, ищет совпадения элементов из первого списка во втором
  • фрагмент НЕ(СЧЁТЕСЛИ($E$1:E1;Список1)) проверяет, не найдено ли уже текущее совпадение выше
  • и, наконец, связка функций ИНДЕКС и ПОИСКПОЗ извлекает совпадающий элемент

Не забудьте в конце ввода этой формулы нажать сочетание клавиш Ctrl+Shift+Enter, т.к. она должна быть введена как формула массива.
Возникающие на избыточных ячейках ошибки #Н/Д можно дополнительно перехватить и заменить на пробелы или пустые строки “” с помощью функции ЕСЛИОШИБКА (IFERROR).

Вывод списка совпадений с помощью слияния запросов Power Query

На больших таблицах формула массива из предыдущего способа может весьма ощутимо тормозить, поэтому гораздо удобнее будет использовать Power Query.

Это бесплатная надстройка от Microsoft, способная загружать в Excel 2010-2013 и трансформировать практически любые данные.

Мощь и возможности Power Query так велики, что Microsoft включила все ее функции по умолчанию в Excel начиная с 2016 версии. 

Для начала, нам необходимо загрузить наши таблицы в Power Query. Для этого выделим первый список и на вкладке Данные (в Excel 2016) или на вкладке Power Query (если она была установлена как отдельная надстройка в Excel 2010-2013) жмем кнопку Из таблицы/диапазона (From Table):


Excel превратит нашу таблицу в “умную” и даст ей типовое имя Таблица1. После чего данные попадут в редактор запросов Power Query.

Никаких преобразований с таблицей нам делать не нужно, поэтому можно смело жать в левом верхнем углу кнопку Закрыть и загрузить – Закрыть и загрузить в… (Close & Load To…

) и выбрать в появившемся окне Только создать подключение (Create only connection):

        

Затем повторяем то же самое со вторым диапазоном.

И, наконец, переходим с выявлению совпадений. Для этого на вкладке Данные или на вкладке Power Query находим команду Получить данные – Объединить запросы – Объединить (Get Data – Merge Queries – Merge):


В открывшемся окне делаем три вещи:

  1. выбираем наши таблицы из выпадающих списков
  2. выделяем столбцы, по которым идет сравнение
  3. выбираем Тип соединения = Внутреннее (Inner Join)

После нажатия на ОК на экране останутся только совпадающие строки:


Ненужный столбец Таблица2 можно правой кнопкой мыши удалить, а заголовок первого столбца переименовать во что-то более понятное (например Совпадения). А затем выгрузить полученную таблицу на лист, используя всё ту же команду Закрыть и загрузить (Close & Load):


Если значения в исходных таблицах в будущем будут изменяться, то необходимо не забыть обновить результирующий список совпадений правой кнопкой мыши или сочетанием клавиш Ctrl+Alt+F5. 

Макрос для вывода списка совпадений

Само-собой, для решения задачи поиска совпадений можно воспользоваться и макросом. Для этого нажмите кнопку Visual Basic на вкладке Разработчик (Developer). Если ее не видно, то отобразить ее можно через Файл – Параметры – Настройка ленты (File – Options – Customize Ribbon).

В окне редактора Visual Basic нужно добавить новый пустой модуль через меню Insert – Module и затем скопировать туда код нашего макроса:

Sub Find_Matches_In_Two_Lists() Dim coll As New Collection Dim rng1 As Range, rng2 As Range, rngOut As Range Dim i As Long, j As Long, k As Long Set rng1 = Selection.Areas(1) Set rng2 = Selection.Areas(2) Set rngOut = Application.InputBox(Prompt:=”Выделите ячейку, начиная с которой нужно вывести совпадения”, Type:=8) 'загружаем первый диапазон в коллекцию For i = 1 To rng1.Cells.Count coll.Add rng1.Cells(i), CStr(rng1.Cells(i)) Next i 'проверяем вхождение элементов второго диапазона в коллекцию k = 0 On Error Resume Next For j = 1 To rng2.Cells.Count Err.Clear elem = coll.Item(CStr(rng2.Cells(j))) If CLng(Err.Number) = 0 Then 'если найдено совпадение, то выводим со сдвигом вниз rngOut.Offset(k, 0) = rng2.Cells(j) k = k + 1 End If Next j End Sub

Воспользоваться добавленным макросом очень просто. Выделите, удерживая клавишу Ctrl, оба диапазона и запустите макрос кнопкой Макросы на вкладке Разработчик (Developer) или сочетанием клавиш Alt+F8. Макрос попросит указать ячейку, начиная с которой нужно вывести список совпадений и после нажатия на ОК сделает всю работу:


Более совершенный макрос подобного типа есть, кстати, в моей надстройке PLEX для Microsoft Excel.

Ссылки по теме

Источник: https://www.planetaexcel.ru/techniques/14/3304/

Как сравнить два столбца в Excel на совпадения

Как в excel найти совпадения

Пожалуй, каждый, кто работает с данными в Excel сталкивается с вопросом как сравнить два столбца в Excel на совпадения и различия. Существует несколько способов как это сделать. Давайте рассмотрим подробней каждый из них.

Как сравнить два столбца в Excel по строкам

Сравнивая два столбца с данными часто необходимо сравнивать данные в каждой отдельной строке на совпадения или различия. Сделать такой анализ мы можем с помощью функции ЕСЛИ. Рассмотрим как это работает на примерах ниже.

Пример 1. Как сравнить два столбца на совпадения и различия в одной строке

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

Для того чтобы проверить, содержат ли два столбца одной строки одинаковые данные нам потребуется формула:

=ЕСЛИ(A2=B2; “Совпадают”; “”)

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

=ЕСЛИ(A2B2; “Не совпадают”; “”)

Мы можем уместить проверку на совпадения и различия между двумя столбцами в одной строке в одной формуле:

=ЕСЛИ(A2=B2; “Совпадают”; “Не совпадают”)

или

=ЕСЛИ(A2B2; “Не совпадают”; “Совпадают”)

Пример результата вычислений может выглядеть так:

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

=ЕСЛИ(СОВПАД(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; “Уникальная строка”; “Не уникальная строка”)

Первая функция СЧЁТЕСЛИ вычисляет количество столбцов в строке со значением в ячейке А2, вторая функция СЧЁТЕСЛИ вычисляет количество столбцов в таблице со значением из ячейки B2. Если результат вычисления равен “0” – это означает, что в каждой ячейке, каждого столбца, этой строки находятся уникальные значения. В этом случае формула выдаст результат “Уникальная строка”, если нет, то “Не уникальная строка”.

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

=ЕСЛИ(СЧЁТЕСЛИ($B:$B;$A5)=0; “Нет совпадений в столбце B”; “Есть совпадения в столбце В”)

Эта формула проверяет значения в столбце B на совпадение с данными ячеек в столбце А.

Если ваша таблица состоит из фиксированного числа строк, вы можете указать в формуле четкий диапазон (например, $B2:$B10). Это позволит ускорить работу формулы.

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

Когда мы ищем совпадения между двумя столбцами в Excel, нам может потребоваться визуализировать найденные совпадения или различия в данных, например, с помощью выделения цветом. Самый простой способ для выделения цветом совпадений и различий – использовать “Условное форматирование” в Excel. Рассмотрим как это сделать на примерах ниже.

Поиск и выделение совпадений цветом в нескольких столбцах в Эксель

В тех случаях, когда нам требуется найти совпадения в нескольких столбцах, то для этого нам нужно:

  • Выделить столбцы с данными, в которых нужно вычислить совпадения;
  • На вкладке “” на Панели инструментов нажимаем на пункт меню “Условное форматирование” -> “Правила выделения ячеек” -> “Повторяющиеся значения”;
  • Во всплывающем диалоговом окне выберите в левом выпадающем списке пункт “Повторяющиеся”, в правом выпадающем списке выберите каким цветом будут выделены повторяющиеся значения. Нажмите кнопку “ОК”:
  • После этого в выделенной колонке будут подсвечены цветом совпадения:

Поиск и выделение цветом совпадающих строк в Excel

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

В таблицах выше размещены одинаковые данные. Их отличие в том, что на примере слева мы искали совпадающие ячейки, а справа мы нашли целые повторяющие строчки с данными.

Рассмотрим как найти совпадающие строки в таблице:

  • Справа от таблицы с данными создадим вспомогательный столбец, в котором напротив каждой строки с данными проставим формулу, объединяющую все значения строки таблицы в одну ячейку:

=A2&B2&C2&D2

Во вспомогательной колонке вы увидите объединенные данные таблицы:

Теперь, для определения совпадающих строк в таблице сделайте следующие шаги:

  • Выделите область с данными во вспомогательной колонке (в нашем примере это диапазон ячеек E2:E15);
  • На вкладке “” на Панели инструментов нажимаем на пункт меню “Условное форматирование” -> “Правила выделения ячеек” -> “Повторяющиеся значения”;
  • Во всплывающем диалоговом окне выберите в левом выпадающем списке “Повторяющиеся”, в правом выпадающем списке выберите каким цветом будут выделены повторяющиеся значения. Нажмите кнопку “ОК”:
  • После этого в выделенной колонке будут подсвечены дублирующиеся строки:

На примере выше, мы выделили строки в созданной вспомогательной колонке.

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

Для этого сделаем следующее:

  • Так же как и в примере выше создадим вспомогательный столбец, в каждой строке которого проставим следующую формулу:

=A2&B2&C2&D2

Таким образом, мы получим в одной ячейке собранные данные всей строки таблицы:

  • Теперь, выделим все данные таблицы (за исключением вспомогательного столбца). В нашем случае это ячейки диапазона A2:D15;
  • Затем, на вкладке “” на Панели инструментов нажмем на пункт “Условное форматирование” -> “Создать правило”:
  • В диалоговом окне “Создание правила форматирования” кликните на пункт “Использовать формулу для определения форматируемых ячеек” и в поле “Форматировать значения, для которых следующая формула является истинной” вставьте формулу:

=СЧЁТЕСЛИ($E$2:$E$15;$E2)>1

  • Не забудьте задать формат найденных дублированных строк.

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

Источник: https://excelhack.ru/kak-sravnit-dva-stolbca-v-excel-na-sovpadeniya/

Пример функции ПОИСКПОЗ для поиска совпадения значений в Excel

Как в excel найти совпадения

Функция ПОИСКПОЗ в Excel используется для поиска точного совпадения или ближайшего (меньшего или большего заданному в зависимости от типа сопоставления, указанного в качестве аргумента) значения заданному в массиве или диапазоне ячеек и возвращает номер позиции найденного элемента.

Например, имеем последовательный ряд чисел от 1 до 10, записанных в ячейках B1:B10. Функция =ПОИСКПОЗ(3;B1:B10;0) вернет число 3, поскольку искомое значение находится в ячейке B3, которая является третьей от точки отсчета (ячейки B1).

Данная функция удобна для использования в случаях, когда требуется вернуть не само значение, содержащееся в искомой ячейке, а ее координату относительно рассматриваемого диапазона. В случае использования для констант массивов, которые могут быть представлены как массивы элементов «ключ» – «значение», функция ПОИСКПОЗ возвращает значение ключа, который явно не указан.

Например, массив {“виноград”;”яблоко”;”груша”;”слива”} содержит элементы, которые можно представить как: 1 – «виноград», 2 – «яблоко», 3 – «груша», 4 – «слива», где 1, 2, 3, 4 – ключи, а названия фруктов – значения.

Тогда функция =ПОИСКПОЗ(“яблоко”;{“виноград”;”яблоко”;”груша”;”слива”};0) вернет значение 2, являющееся ключом второго элемента.

Отсчет выполняется не с 0 (нуля), как это реализовано во многих языках программирования при работе с массивами, а с 1.

Функция ПОИСКПОЗ редко используется самостоятельно. Ее целесообразно применять в связке с другими функциями, например, ИНДЕКС.



Пример 1. Найти позицию первого частичного совпадения строки в диапазоне ячеек, хранящих текстовые значения.

Вид исходной таблицы данных:

Для нахождения позиции текстовой строки в таблице используем следующую формулу:

=ПОИСКПОЗ(D2&”*”;B:B;0)-1

Описание аргументов:

  • D2&”*” – искомое значение, состоящее и фамилии, указанной в ячейке B2, и любого количества других символов (“*”);
  • B:B – ссылка на столбец B:B, в котором выполняется поиск;
  • 0 – поиск точного совпадения.

Из полученного значения вычитается единица для совпадения результата с id записи в таблице.

Пример поиска:

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

Пример 2. В Excel хранятся две таблицы, которые на первый взгляд кажутся одинаковыми. Было решено сравнить по одному однотипному столбцу этих таблиц на наличие несовпадений. Реализовать способ сравнения двух диапазонов ячеек.

Вид таблицы данных:

Для сравнения значений, находящихся в столбце B:B со значениями из столбца A:A используем следующую формулу массива (CTRL+SHIFT+ENTER):

Функция ПОИСКПОЗ выполняет поиск логического значения ИСТИНА в массиве логических значений, возвращаемых функцией СОВПАД (сравнивает каждый элемент диапазона A2:A12 со значением, хранящимся в ячейке B2, и возвращает массив результатов сравнения).

Если функция ПОИСКПОЗ нашла значение ИСТИНА, будет возвращена позиция его первого вхождения в массив. Функция ЕНД возвратит значение ЛОЖЬ, если она не принимает значение ошибки #Н/Д в качестве аргумента.

В этом случае функция ЕСЛИ вернет текстовую строку «есть», иначе – «нет».

Чтобы вычислить остальные значения «протянем» формулу из ячейки C2 вниз для использования функции автозаполнения. В результате получим:

Как видно, третьи элементы списков не совпадают.

Поиск ближайшего большего знания в диапазоне чисел Excel

Пример 3. Найти ближайшее меньшее числу 22 в диапазоне чисел, хранящихся в столбце таблицы Excel.

Вид исходной таблицы данных:

Для поиска ближайшего большего значения заданному во всем столбце A:A (числовой ряд может пополняться новыми значениями) используем формулу массива (CTRL+SHIFT+ENTER):

Функция ПОИСКПОЗ возвращает позицию элемента в столбце A:A, имеющего максимальное значение среди чисел, которые больше числа, указанного в ячейке B2. Функция ИНДЕКС возвращает значение, хранящееся в найденной ячейке.

Результат расчетов:

Для поиска ближайшего меньшего значения достаточно лишь немного изменить данную формулу и ее следует также ввести как массив (CTRL+SHIFT+ENTER):

Результат поиска:

Особенности использования функции ПОИСКПОЗ в Excel

Функция имеет следующую синтаксическую запись:

=ПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления])

Описание аргументов:

  • искомое_значение – обязательный аргумент, принимающий текстовые, числовые значения, а также данные логического и ссылочного типов, который используется в качестве критерия поиска (для сопоставления величин или нахождения точного совпадения);
  • просматриваемый_массив – обязательный аргумент, принимающий данные ссылочного типа (ссылки на диапазон ячеек) или константу массива, в которых выполняется поиск позиции элемента согласно критерию, заданному первым аргументом функции;
  • [тип_сопоставления] – необязательный для заполнения аргумент в виде числового значения, определяющего способ поиска в диапазоне ячеек или массиве. Может принимать следующие значения:
  1. -1 – поиск наименьшего ближайшего значения заданному аргументом искомое_значение в упорядоченном по убыванию массиве или диапазоне ячеек.
  2. 0 – (по умолчанию) поиск первого значения в массиве или диапазоне ячеек (не обязательно упорядоченном), которое полностью совпадает со значением, переданным в качестве первого аргумента.
  3. 1 – Поиск наибольшего ближайшего значения заданному первым аргументом в упорядоченном по возрастанию массиве или диапазоне ячеек.

Скачать примеры ПОИСКПОЗ для поиска совпадения значений в Excel

Примечания:

  1. Если в качестве аргумента искомое_значение была передана текстовая строка, функция ПОИСКПОЗ вернет позицию элемента в массиве (если такой существует) без учета регистра символов. Например, строки «МоСкВа» и «москва» являются равнозначными. Для различения регистров можно дополнительно использовать функцию СОВПАД.
  2. Если поиск с использованием рассматриваемой функции не дал результатов, будет возвращен код ошибки #Н/Д.
  3. Если аргумент [тип_сопоставления] явно не указан или принимает число 0, для поиска частичного совпадения текстовых значений могут быть использованы подстановочные знаки («?» – замена одного любого символа, «*» – замена любого количества символов).
  4. Если в объекте данных, переданном в качестве аргумента просматриваемый_массив, содержится два и больше элементов, соответствующих искомому значению, будет возвращена позиция первого вхождения такого элемента.

Источник: https://exceltable.com/funkcii-excel/primery-funkcii-poiskpoz

Как в excel найти совпадения

Как в excel найти совпадения

Сегодня расскажу как искать совпадение в столбцах Excel. Разберем все тонкости на примерах.

Задача 1: Есть 6 текстов в 6 ячейках. Необходимо узнать, какие из них уникальные, а какие повторяются.

Использовать будем Условное форматирование.

  • Выбираем ячейки, которые необходимо сравнить;
  • Во вкладке переходим «Условное форматирование -> Правила выделения ячеек -> Повторяющиеся значения»;

Выскакивает новое окно и в таблице начинают подсвечиваться ячейки, которые повторяются.

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

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

Выбираем таблицу и заходим в Повторяющиеся значения. Все совпадения будут подсвечены.

Можно, например, найти совпадения в одном столбце. Для этого достаточно перед применением опции выделить только его.

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

Искать таким образом можно буквы, слова, символы, тексты и т.д.

Как скачать видеоурок с сайта

Не забудьте поделиться ссылкой на статью ⇒

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

Сейчас приведу пример. Допустим, у вас есть ячейка «A», в которой находится имя, фамилия и отчество.

Вам необходимо сделать так, чтобы в первой ячейке «A» была только фамилия, в ячейке «B» — имя, ну и в ячейке «C» отчество.

В этом уроке расскажу как поставить в начале ячейки знак плюс или ноль перед числом в Excel. Давайте представим ситацию, что вам необходимо ввести в ячейку номер телефона в формате «+7 987…». В обычном состоянии программа Эксель просто удалит этот знак плюс.

Сегодня расскажу как решить проблему, когда в Excel столбцы обозначены цифрами. Ситуация может быть для многих знакомой. Вы открываете файл Excel и в подписях столбцов вместо букв стоят цифры. Адреса в этом случае имеют не совсем привычное написание R1C1, R1C3 и т.д.

В этом уроке расскажу как объединить текст в ячейках в Excel с помощью функции Сцепить. Оба эти способа, которые рассмотрим, позволяют сцепить в единый массив несколько ячеек. Ячейки при этом могут быть как текстовые, так и числовые.

Этот урок будет более полезен для кадровой службы, потому что именно ей приходится считать трудовой стаж. Нам на помощь придет специальная функция РАЗНДАТ в Excel. Это фантомная функция, поэтому ее нет в обычном диспетчере формул. Работает она только с датами и с ее помощью можно рассчитать определенный период и вывести его в днях, годах и месяцах.

Источник:

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

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

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

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

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

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

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

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

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

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

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

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

Например, для сравнения двух прайсов в Excel даже на разных листах.

Источник:

Блог о программе Microsoft Excel: обучение, трюки, хитрости и подсказки

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

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

Об этих хитростях мы и поговорим в сегодняшней статье.

Читать далее

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

Читать далее

Сегодня я представляю свой последний (седьмой) урок бесплатного онлайн курса по основам Excel, в котором мы поговорим о том, как управлять рабочими листами.

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

Читать далее

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

Читать далее

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

Читать далее

Пришло время для нашего четверного обучающего видеоурока онлайн курса «Основы Excel», который охватит вопросы, касающиеся изменения ячеек.

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

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

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

Читать далее

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

Читать далее

В наше время, практически, любая работа требует базовых навыков владения Microsoft Excel. Мы не говорим о написании макросов или создании сводных таблиц.

Это уровень навыка, который позволит качественно изменить подход в работе с большими объемами данных или аналитике.

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

Читать далее

Источник: https://soveti-masterov.com/sovety/kak-v-excel-najti-sovpadeniya.html

Как найти совпадения в Excel – Офис Ассист

Как в excel найти совпадения

Элен Брэдли объясняет тонкости работы с функцией ВПР в Microsoft Excel при поиске данных в таблице.

  • Основы
  • Синтаксис функции ВПР
  • ВПР в действии
  • Работа с точными совпадениями
  • Используем проверку данных
  • Сортируем данные

В Microsoft Excel есть несколько функций поиска, среди них VLOOKUP (ВПР), очень похожая на нее функция HLOOKUP (ГПР) и LOOKUP (ПРОСМОТР).

Функция ВПР используется для того, чтобы искать данные в таблице.

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

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

Функция ПРОСМОТР имеет две формы — векторную и массива, и может возвращать значение из одного столбца, одной строки или из массива (аналог ВПР и ГПР). Из этих трёх функций, вероятнее всего, Вы будете использовать ВПР гораздо чаще, чем остальные. Именно на ней я сфокусируюсь в этой статье.

В целом, если Вы поймете и сможете применить функцию ВПР, то сможете справиться и с ГПР.

Синтаксис функции ВПР

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

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

Например, 1 — это первый столбец диапазона, 2 — это следующий за ним вправо и так далее.

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

У этой функции существует ещё один не обязательный аргумент, который позволяет искать приблизительное или точное совпадение искомого значения, причем первый режим используется по умолчанию. В случае если Вы устанавливаете режим поиска точного совпадения, т.е. последний аргумент равен FALSE (ЛОЖЬ), таблица может быть не отсортирована.

Если же Вы устанавливаете режим поиска неточного совпадения, т.е. последний аргумент не указан или равен TRUE (ИСТИНА), то необходимо отсортировать таблицу в порядке возрастания, иначе функция может возвратить неправильный результат.

При поиске неточного совпадения, Excel ищет значение равное искомому, а если его нет — использует ближайшее, которое меньше искомого.

Итак, для примера, в этой таблице представлены значения веса в фунтах (Lbs weight), а также стоимость обработки и перевозки. Мы можем использовать функцию ВПР, чтобы найти значение веса и определить стоимость обработки (Handling) и перевозки (Shipping) партии товара такого веса.

Конечно же, вес большинства партий товара не будет иметь такие же ровные значения, поэтому мы используем в качестве последнего аргумента TRUE (ИСТИНА), либо вовсе не указываем его. В таком случае наша формула найдёт результат, даже без точного совпадения.

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

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

Чтобы для значения из ячейки B11, которое является весом партии товара в фунтах (в данном случае 1.5 фунта), возвратить стоимость обработки из столбца Handling, воспользуемся вот такой формулой:

=VLOOKUP(B11,D2:F7,2) =ВПР(B11;D2:F7;2)

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

=VLOOKUP(B11,$D$2:$F$7,2) =ВПР(B11;$D$2:$F$7;2)

Теперь при создании формулы Вы можете использовать имя диапазона. Вот здесь вместо адреса диапазона указано его имя:

=VLOOKUP(B12,shipping_and_handling,2) =ВПР(B12; shipping_and_handling;2)

Мы можем адаптировать формулу из столбца Handling для расчёта значений в столбце Shipping. В данном случае поменяется только номер столбца. Для Shipping — это значение 3:

=VLOOKUP(B12,shipping_and_handling,3) =ВПР(B12;shipping_and_handling;3)

Используя предыдущий пример, мы сможем найти соответствия для весов в 11, 12 или 25 фунтов, хоть они и отсутствуют в таблице. Возможность находить ближайшее значение, которое меньше искомого, выглядит очень привлекательной.

Однако, существуют некоторые оговорки при использовании этой формулы. Одна из них — стартовое значение в таблице должно быть равно 0, что собственно и сделано у нас.

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

Работа с точными совпадениями

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

Пример с размерами ковров и ценами, который представлен на рисунке ниже, как раз это и демонстрирует. Если в таблице нет ковра Green weave, то и не нужно искать следующее меньшее значение.

В такой ситуации нам необходимо или точное совпадение, или сообщение об ошибке.

В данном примере мы ищем название в столбце A и возвращаем цену из столбца 2 или 3, в зависимости от указанного размера ковра — средний (M) или большой (L). В этой ситуации нам необходимо использовать функцию IF (ЕСЛИ), чтобы определить, какой номер столбца нужно использовать. Формула поиска будет выглядеть следующим образом:

=VLOOKUP(A7,A2:C4,IF(B7=”M”,2,3),FALSE) =ВПР(A7;A2:C4;ЕСЛИ(B7=”M”;2;3);ЛОЖЬ)

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

 Перейдите Data >Data Validation >Data Validation (Данные > Проверка данных > Проверка данных). В появившемся диалоговом окне на вкладке Settings (Параметры) в поле Allow (Тип данных) выберите значение List (Список).

Кликните в поле Source (Источник) и выделите ячейки от A2 до A4, в которых содержится список с названиями ковров. Нажмите ОК.

Таким же способом Вы можете создать выпадающий список для ввода размеров L или M, используя в качестве источника данных диапазон B1:C1.

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

Это гарантирует, что название будет указано без ошибок, так как всегда будет выбрана одна из присутствующих в списке позиций.

Кроме этого, если они передумают и решат выбрать другой ковер, то функция ВПР автоматически произведёт пересчет и вернет правильную стоимость.

Сортируем данные

Если Вы работаете с приблизительными совпадениями, то должны выполнить сортировку в таблице. Для этого выделите весь диапазон с данными, включая заголовки строк в первом столбце. Заголовки столбцов (шапку) можно не выделять. На вкладке Data (Данные) нажмите команду Sort (Сортировка), откроется одноименное диалоговое окно.

В строке Sort By (Сортировать по) укажите параметры сортировки. В первом выпадающем списке выберите столбец, по которому необходимо выполнить сортировку, в нашем случае это первый столбец таблицы.

Во втором выберите Values (Значения), а в третьем укажите порядок сортировки по возрастанию.

Если вместе с данными, Вы выделили шапку таблицы, не забудьте поставить галочку My data has headers (Мои данные содержат заголовки). Нажмите ОК.

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

Урок подготовлен для Вас командой сайта office-guru.ru Источник: https://www.ablebits.com/office-addins-blog/2011/12/14/lookup-excel-functions-vlookup/ Перевел: Андрей Антонов Правила перепечатки Еще больше уроков по Microsoft Excel

Источник: http://officeassist.ru/excel/kak-najti-sovpadeniya-v-excel/

Поделиться:
Нет комментариев

    Добавить комментарий

    Ваш e-mail не будет опубликован. Все поля обязательны для заполнения.