У вас есть рабочая книга Excel^{(Excel workbook)} с тысячами чисел и слов. Там обязательно должны быть кратные одному и тому же числу или слову^{(number or word)} . Возможно, вам придется найти их. Итак, мы рассмотрим несколько способов поиска совпадающих значений в Excel 365 .

Мы рассмотрим поиск одинаковых слов или чисел на двух разных листах и ​​в двух разных столбцах. Мы рассмотрим использование функций EXACT , MATCH и VLOOKUP . Некоторые методы, которые мы будем использовать, могут не работать в веб-версии Microsoft Excel , но все они будут работать в настольной версии. 

Что такое функция Excel?^{(What’s An Excel Function?)}

Если вы уже использовали функции, пропустите их. 

Функция Excel^{(Excel function)} похожа на мини-приложение^{(mini app)} . Он применяет ряд шагов для выполнения одной задачи. Наиболее часто используемые функции Excel^{(Excel function)} можно найти на вкладке « Формулы ». ^{(Formulas )}Здесь мы видим их классифицированными по характеру функции –^{(function –)}

• Автосумма
• Недавно использованный
• финансовый
• Логический
• Текст
• Дата и время
• Поиск и ссылка
• Математика и триггер
• Дополнительные функции.

Категория « Дополнительные функции^{(More Functions )} » содержит категории « Статистика», «Инженерия», «Куб», «Информация», «Совместимость» и «Интернет»^{(Statistical, Engineering, Cube, Information, Compatibility, and Web)} .

Точная функция^{(The Exact Function)}

Задача функции Exact^{(Exact function)} — просмотреть^{(s task)} строки двух столбцов и найти совпадающие значения в ячейках Excel . Точно значит точно. Сама по себе функция Exact^{(Exact function)} чувствительна к регистру. Он не будет рассматривать Нью-Йорк^{(New York )} и Нью- Йорк^{(new york )} как совпадения.

В приведенном ниже примере есть два столбца текста — Tickets и Receipts^{(text – Tickets and Receipts)} . Только для 10 наборов текста мы могли сравнить их, взглянув на них. Представьте, если бы было 1000 строк или больше. Вот когда вы должны использовать функцию Exact^{(Exact function)} .

Поместите курсор в ячейку C2. В строке формул^{(formula bar)} введите формулу 

=EXACT(E2:E10,F2:F10)

E2:E10 относится к первому столбцу значений, а F2:F10 относится к столбцу рядом с ним. Как только мы нажмем Enter , Excel сравнит два значения в каждой строке и сообщит нам, совпадают ли они ( True ) или нет ( False ). Поскольку мы использовали диапазоны вместо двух ячеек, формула распространится на ячейки под ней и оценит все остальные строки.

Однако этот метод ограничен. Он будет сравнивать только две ячейки, которые находятся в одной строке. Например, он не будет сравнивать то, что находится в A2, с B3. Как мы это делаем? МАТЧ^(MATCH) может помочь.

Функция ПОИСКПОЗ^{(The MATCH Function)}

ПОИСКПОЗ^(MATCH) можно использовать, чтобы сообщить нам, где находится совпадение для определенного значения в диапазоне ячеек.

Допустим, мы хотим узнать, в какой строке находится конкретный SKU ( Stock Keeping Unit ), в примере ниже.

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

=MATCH(J1,E2:E9,0)

J1 относится к ячейке со значением, которое мы хотим сопоставить. E2:E9 относится к диапазону значений, которые мы ищем. Ноль ( 0 ) в конце формулы указывает Excel искать точное совпадение. Если бы мы сопоставляли числа, мы могли бы использовать 1 , чтобы найти что-то меньшее, чем наш запрос, или 2 , чтобы найти что-то большее, чем наш запрос.

Но что, если мы хотим узнать цену AA003 ?

Функция ВПР^{(The VLOOKUP Function)}

V в VLOOKUP означает^(VLOOKUP) вертикальный. Это означает, что он может искать заданное значение в столбце. Он также может возвращать значение в той же строке, что и найденное значение^{(found value)} . 

Если у вас есть подписка на Office 365 в ^{(Office 365)}ежемесячном канале^{(Monthly channel)} , вы можете использовать более новый XLOOKUP . Если у вас есть только полугодовая подписка, она будет доступна вам в июле 2020 года^{(July 2020)} .

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

Там, где раньше искали строку, вводим формулу:

=VLOOKUP(J1,E2:G9,3,FALSE)

J1 относится к ячейке со значением, которое мы сопоставляем. E2:G9 — это диапазон значений, с которым мы работаем. Но функция ВПР^(VLOOKUP) будет искать совпадения только в первом столбце этого диапазона. 3 относится к 3 -му столбцу от начала диапазона.  

Поэтому, когда мы вводим SKU^(SKU) в J1, функция ВПР^(VLOOKUP) найдет совпадение и возьмет значение из столбца ячейки 3^{(cell 3)} от него. FALSE сообщает Excel , какое совпадение мы ищем. FALSE означает, что это должно быть точное совпадение , тогда как TRUE говорит, что это должно быть близкое совпадение.

Как найти совпадающие значения на двух разных листах?^{(How Do I Find Matching Values in Two Different Sheets?)}

Каждая из вышеперечисленных функций может работать на двух разных листах, чтобы найти совпадающие значения в Excel . Мы собираемся использовать функцию EXACT,^{(EXACT function)} чтобы показать вам, как это сделать. Это можно сделать практически с любой функцией. Не только те, которые мы рассмотрели здесь. Есть и другие способы связать ячейки между разными листами и книгами.

Работая на листе Держателей^{(Holders )} , вводим формулу 

=EXACT(D2:D10,Tickets!E2:E10)

D2:D10 — это диапазон, который мы выбрали на листе держателей^{(Holders sheet)} . После того, как мы поставим запятую после этого, мы можем щелкнуть лист «Билеты» и перетащить^{(Tickets sheet and drag)} и выбрать второй диапазон. 

Посмотрите, как он ссылается на лист и диапазон^{(sheet and range)} как Tickets!E2:E10 ? В этом случае каждая строка совпадает, поэтому все результаты равны True .

Как еще я могу использовать эти функции?^{(How Else Can I Use These Functions?)}

Как только вы освоите эти функции для сопоставления и поиска вещей, вы сможете начать делать с ними много разных вещей. Также взгляните на совместное использование функций ИНДЕКС и ПОИСКПОЗ,^{(using the INDEX and MATCH functions)} чтобы сделать что-то похожее на ВПР^(VLOOKUP) .

Есть несколько полезных советов по использованию функций Excel для поиска совпадающих значений в Excel ? Может быть, вопрос о том, как сделать больше? Напишите^(Drop) нам в комментариях ниже.

How to Find Matching Values in Excel

Yоu’νe got an Excel workbook with thousands of numberѕ and words. There are bound to be multiples of the same number or word in there. You might need to find them. So we’re going to look at several ways you can find matching values in Excel 365.

We’re going to cover finding the same words or numbers in two different worksheets and in two different columns. We’ll look at using the EXACT, MATCH, and VLOOKUP functions. Some of the methods we’ll use may not work in the web version of Microsoft Excel, but they will all work in the desktop version. 

What’s An Excel Function?

If you’ve used functions before, skip ahead. 

An Excel function is like a mini app. It applies a series of steps to perform a single task. The most commonly used Excel functions can be found in the Formulas tab. Here we see them categorized by the nature of the function –

• AutoSum
• Recently Used
• Financial
• Logical
• Text
• Date & Time
• Lookup & Reference
• Math & Trig
• More Functions.

The More Functions category contains the categories Statistical, Engineering, Cube, Information, Compatibility, and Web.

The Exact Function

The Exact function’s task is to go through the rows of two columns and find matching values in the Excel cells. Exact means exact. On its own, the Exact function is case sensitive. It won’t see New York and new york as being a match.

In the example below, there are two columns of text – Tickets and Receipts. For only 10 sets of text, we could compare them by looking at them. Imagine if there were 1,000 rows or more though. That’s when you would use the Exact function.

Place the cursor in cell C2. In the formula bar, enter the formula 

=EXACT(E2:E10,F2:F10)

E2:E10 refers to the first column of values and F2:F10 refers to the column right next to it. Once we press Enter, Excel will compare the two values in each row and tell us if it’s a match (True) or not (False). Since we used ranges instead of just two cells, the formula will spill over into the cells below it and evaluate all the other rows.

This method is limited though. It will only compare two cells that are on the same row. It won’t compare what’s in A2 with B3 for example. How do we do that? MATCH can help.

The MATCH Function

MATCH can be used to tell us where a match for a specific value is in a range of cells.

Let’s say we want to find out what row a specific SKU (Stock Keeping Unit) is in, in the example below.

If we want to find what row AA003 is in, we would use the formula:

=MATCH(J1,E2:E9,0)

J1 refers to the cell with the value we want to match. E2:E9 refers to the range of values we’re searching through. The zero (0) at the end of the formula tells Excel to look for an exact match. If we were matching numbers, we could use 1 to find something less than our query or 2 to find something greater than our query.

But what if we wanted to find the price of AA003?

The VLOOKUP Function

The V in VLOOKUP stands for vertical. Meaning it can search for a given value in a column. What it can also do is return a value on the same row as the found value. 

If you’ve got an Office 365 subscription in the Monthly channel, you can use the newer XLOOKUP. If you only have the semi-annual subscription it will be available to you in July 2020.

Let’s use the same inventory data and try to find the price of something.

Where we were looking for a row before, enter the formula:

=VLOOKUP(J1,E2:G9,3,FALSE)

J1 refers to the cell with the value we’re matching. E2:G9 is the range of values we’re working with. But VLOOKUP will only look in the first column of that range for a match. The 3 refers to the 3rd column over from the start of the range.  

So when we type a SKU in J1, VLOOKUP will find the match and grab the value from the cell 3 columns over from it. FALSE tells Excel what kind of match we’re looking for. FALSE means it must be an exact match where TRUE would tell it that it has to be a close match.

How Do I Find Matching Values in Two Different Sheets?

Each of the functions above can work across two different sheets to find matching values in Excel. We’re going to use the EXACT function to show you how. This can be done with almost any function. Not just the ones we covered here. There are also other ways to link cells between different sheets and workbooks.

Working on the Holders sheet, we enter the formula 

=EXACT(D2:D10,Tickets!E2:E10)

D2:D10 is the range we’ve selected on the Holders sheet. Once we put a comma after that, we can click on the Tickets sheet and drag and select the second range. 

See how it references the sheet and range as Tickets!E2:E10? In this case each row matches, so the results are all True.

How Else Can I Use These Functions?

Once you master these functions for matching and finding things, you can start doing a lot of different things with them. Also take a look at using the INDEX and MATCH functions together to do something similar to VLOOKUP.

Have some cool tips on using Excel functions to find matching values in Excel? Maybe a question about how to do more? Drop us a note in the comments below.

Related posts

• Добавить линейный Regression Trendline на Excel Scatter Plot

• Как сделать Histogram в Excel

• Как надежно Password Protect Excel File

• Как создать Flowchart в Word and Excel

• Как Create Labels в Word от Excel Spreadsheet

• Как разделить имя и фамилию в Excel

• Как изменить Background в Microsoft Teams

• Как использовать спарклайны в Excel

• Вставить лист Excel в документ Word

• Как понять анализ «что, если» в Microsoft Excel

• Как открыть несколько экземпляров Excel

• Как создать Distribution List в Outlook

• Как Automatically Backup A Word Document в OneDrive

• Как вычитать даты в Excel

• Форматировать ячейки с помощью условного форматирования в Excel

• 40 лучших сочетаний клавиш Microsoft Excel

• Автоподбор ширины столбцов и высоты строк в Excel

• Как сделать простой график или диаграмму в Excel

• Как Group Worksheets в Excel

• График ваших данных Excel