Excel, если значения ячеек совпадают, затем вставьте значение из другой ячейки
Обновлено: 05.07.2024
Совет. Попробуйте использовать новые функции XLOOKUP и XMATCH, улучшенные версии функций, описанных в этой статье. Эти новые функции работают в любом направлении и по умолчанию возвращают точные совпадения, что делает их более простыми и удобными в использовании, чем их предшественники.
Предположим, что у вас есть список номеров офисов, и вам нужно знать, какие сотрудники работают в каждом офисе. Электронная таблица огромна, поэтому вы можете подумать, что это сложная задача. На самом деле это довольно легко сделать с помощью функции поиска.
Функции ВПР и ГПР вместе с ИНДЕКС и ПОИСКПОЗ — одни из самых полезных функций в Excel.
Примечание. Мастер поиска больше не доступен в Excel.
Вот пример использования функции ВПР.
=ВПР(B2,C2:E7,3,ИСТИНА)
В этом примере B2 — это первый аргумент — элемент данных, необходимый для работы функции. Для ВПР этот первый аргумент — это значение, которое вы хотите найти. Этот аргумент может быть ссылкой на ячейку или фиксированным значением, например «кузнец» или 21 000. Второй аргумент — это диапазон ячеек C2-:E7, в котором нужно искать значение, которое вы хотите найти. Третий аргумент — это столбец в этом диапазоне ячеек, который содержит искомое значение.
Четвертый аргумент является необязательным. Введите ИСТИНА или ЛОЖЬ. Если вы введете TRUE или оставите аргумент пустым, функция вернет приблизительное соответствие значению, указанному вами в первом аргументе. Если вы введете FALSE, функция будет соответствовать значению, предоставленному первым аргументом. Другими словами, оставляя четвертый аргумент пустым или вводя значение TRUE, вы получаете больше гибкости.
В этом примере показано, как работает функция. Когда вы вводите значение в ячейку B2 (первый аргумент), функция ВПР ищет ячейки в диапазоне C2:E7 (2-й аргумент) и возвращает наиболее близкое приближенное совпадение из третьего столбца диапазона, столбца E (3-й аргумент).< /p>
Четвертый аргумент пуст, поэтому функция возвращает приблизительное совпадение. В противном случае вам пришлось бы вводить одно из значений в столбцах C или D, чтобы вообще получить результат.
Когда вы хорошо разбираетесь в функции ВПР, функция ГПР так же проста в использовании. Вы вводите те же аргументы, но поиск выполняется в строках, а не в столбцах.
Использование ИНДЕКС и ПОИСКПОЗ вместо ВПР
Существуют определенные ограничения при использовании функции ВПР: функция ВПР может искать значение только слева направо. Это означает, что столбец, содержащий искомое значение, всегда должен располагаться слева от столбца, содержащего возвращаемое значение. Теперь, если ваша электронная таблица не построена таким образом, не используйте функцию ВПР. Вместо этого используйте комбинацию функций ИНДЕКС и ПОИСКПОЗ.
В этом примере показан небольшой список, в котором значение, которое мы хотим найти, Чикаго, отсутствует в крайнем левом столбце. Итак, мы не можем использовать функцию ВПР. Вместо этого мы будем использовать функцию ПОИСКПОЗ, чтобы найти Чикаго в диапазоне B1:B11. Оно находится в строке 4. Затем ИНДЕКС использует это значение в качестве аргумента поиска и находит население Чикаго в 4-м столбце (столбец D). Используемая формула показана в ячейке A14.
Попробуйте
Если вы хотите поэкспериментировать с функциями поиска, прежде чем применять их к своим собственным данным, вот несколько примеров данных.
Пример VLOOKUP в работе
Скопируйте следующие данные в пустую электронную таблицу.
Совет. Прежде чем вставлять данные в Excel, задайте для столбцов с A по C ширину 250 пикселей и нажмите "Обтекание текстом" (вкладка "Главная", группа "Выравнивание").
Допустим, у вас есть рабочий лист с товарами в двух отдельных столбцах и соответствующими ценами в другом отдельном столбце. Среди товарных позиций некоторые из них могут иметь повторяющиеся названия. Теперь все, что вам нужно, это скопировать цены на повторяющиеся товары в другую ячейку вашего рабочего листа. Если вы столкнулись с этой проблемой прямо сейчас, то прочитайте всю статью. Потому что вы собираетесь изучить 3 метода копирования соответствующих значений в другую ячейку, если две ячейки совпадают в Excel.
Загрузить рабочую тетрадь
Рекомендуется загрузить файл Excel и попрактиковаться вместе с ним.
3 метода копирования значений в другую ячейку, если две ячейки совпадают в Excel
В этой статье мы будем использовать образец прайс-листа продукта в качестве набора данных для демонстрации всех методов. Итак, давайте взглянем на набор данных:
Итак, без дальнейших обсуждений, давайте сразу рассмотрим все методы один за другим.
1. Используйте функцию ЕСЛИ для копирования значений в другую ячейку, если две ячейки совпадают
У нас есть несколько названий продуктов в двух столбцах с именами Itemcat1 и Itemcat2. В этих двух столбцах есть несколько повторяющихся названий продуктов. В третьем столбце у нас есть соответствующие цены на продукты.
Что мы собираемся сделать, так это скопировать цены товаров, которые являются дубликатами. Есть еще один столбец под названием «Цена совпадающего товара», в который вы собираетесь скопировать цены дубликатов товаров. Мы можем сделать все это, используя только функцию ЕСЛИ.
Итак, без дальнейшего обсуждения, давайте сразу перейдем к процедурным шагам:
❶ Выберите ячейку E5.
❷ Введите формулу:
❸ Нажмите кнопку ВВОД.
❹ После этого перетащите значок "Ручка заполнения" в конец столбца "Цена совпадающего товара".
Когда вы выполните все вышеперечисленные шаги, вы увидите результат, как на картинке ниже:
2. Используйте функцию ВПР для копирования значений в другую ячейку, если две ячейки совпадают
Теперь у нас есть окно поиска для поиска повторяющихся значений элементов. Поле поиска называется Item. Куда вы будете вставлять названия любых элементов, перечисленных в основной таблице данных.
Таким образом, если наша формула обнаружит совпадения между двумя ячейками с точки зрения их значений, соответствующая цена будет скопирована в другую ячейку.
Например, мы вставили лапшу в поле элемента. В столбце «Предметы» нашего набора данных уже есть еще один предмет под названием «Лапша» по цене 936 долларов. Итак, в поле «Цена» под полем «Товар» мы собираемся вернуть эту цену с помощью функции ВПР.
Чтобы выполнить эту операцию, все, что нам нужно сделать, это
❶ Выберите ячейку C15.
❷ Введите формулу:
❸ Нажмите кнопку ВВОД.
Сделав все это, вы увидите, что мы успешно скопировали цену лапши из основной таблицы данных.
␥ Разбивка формулы
- $C$14 ▶ содержит значение поиска, то есть Noodles.
- B5:C12 ▶ диапазон всей таблицы данных.
- 2 ▶ порядковый номер столбца. Это означает, что цена копируется из второго столбца основной таблицы данных.
- 1 ▶ относится к прибл. соответствие между искомым значением и восстановленным элементом.
- =VLOOKUP($C$14,B5:C12,2,1) ▶ копирует соответствующие значения, если две ячейки совпадают с другой ячейкой.
3. Используйте функцию ИНДЕКС и ПОИСКПОЗ для копирования значений в другую ячейку, если две ячейки совпадают
В этом разделе мы будем использовать ИНДЕКС и функцию ПОИСКПОЗ для копирования значений в другие ячейки, если две ячейки совпадают с точки зрения их соответствующих значений. Теперь выполните следующие шаги, чтобы научиться использовать эти две функции:
❶ Выберите ячейку C15.
❷ Введите формулу:
❸ Нажмите кнопку ВВОД.
␥ Разбивка формулы
- ПОИСКПОЗ(C14,B5:B12,0) ▶ поиск от B5 до B12 для соответствия значениям, хранящимся в C14. C14 хранит элемент под названием Noodles, который находится в шестой строке таблицы данных. Итак, эта функция возвращает 6.
- =ИНДЕКС(B5:C12,ПОИСКПОЗ(C14,B5:B12,0),2) ▶ ищет цену, указанную аргументом 2. Цена в шестой строке равна 936, которая возвращается функцией ИНДЕКС функция.
Что нужно помнить
📌 Будьте осторожны с синтаксисом функций.
📌 Аккуратно вставляйте диапазон таблицы в формулы.
Заключение
Подводя итог, мы обсудили 3 метода копирования значений в другую ячейку, если две ячейки совпадают в Excel.Вам рекомендуется скачать практическую рабочую тетрадь, прилагаемую к этой статье, и практиковать все методы с ней. И не стесняйтесь задавать любые вопросы в разделе комментариев ниже. Мы постараемся ответить на все соответствующие запросы как можно скорее. Посетите наш веб-сайт Exceldemy, чтобы узнать больше.
Подробнее
- Как выполнить визуальный поиск и получить последнее совпадение в Excel (4 способа)
- Excel находит совпадающие значения в двух столбцах
- Как найти соответствие с учетом регистра в Excel (6 формул)
Мринмой
Привет! Это Мринмой. Я пишу об Excel и VBA для Exceldemy. У меня есть диплом инженерно-технологического университета Кхулна, Бангладеш. У меня огромный интерес к науке о данных и машинному обучению. В свободное время я люблю играть на гитаре и смотреть Tedtalks. Я был независимым кинорежиссером и свадебным фотографом. Я либерален к любой конструктивной критике и склонен расширять свои знания.
MS Excel предоставляет различные параметры и методы для облегчения наших задач. В этой статье я покажу несколько способов, если мы хотим сверить ячейки друг с другом и вернуть другую ячейку в Excel.
5 способов проверить, равна ли одна ячейка другой, и вернуть их в другую ячейку
Скачать учебное пособие
1. Использование функции ЕСЛИ
ЕСЛИ — это одна из самых простых функций, которая используется для логического сравнения двух значений. В этом методе мы увидим, как использовать функцию ЕСЛИ для сравнения одной ячейки с другой и возврата другого значения ячейки. Прежде чем перейти к примеру, давайте узнаем больше об этой функции. Синтаксис функции такой:
В первой части параметра нам нужно передать наше условие, на основе которого мы собираемся сравнивать. Затем вторая и третья часть определяют, что будет, если значения после сравнения получатся True или False.
Предположим, что у нас есть набор данных фруктов с двумя столбцами. Каждая строка имеет определенное значение. Теперь мы найдем строки, в которых совпадают Fruit 1 и Fruit 2, и отобразим их значения в столбце Matched Values.
Шаг 1. Введите приведенную ниже формулу в ячейку D4.
Разбивка формулы
Во-первых, используя условие B4=C4, мы сравниваем название фруктов каждого столбца Fruits 1 и Fruits 2 . Если условие принимает значение True, оно печатает значения из столбца «Значение» в столбец «Совпавшие значения».
Шаг 2. Скопируйте формулу до D8.
2. Использование функции ЕСЛИ и формулы
В этом методе мы будем использовать ту же функцию ЕСЛИ, но в зависимости от условия будем использовать формулу и показывать их в другой ячейке. Давайте подумаем о том же наборе данных, который использовался в предыдущем методе, но здесь я обновлю новую цену, если значение флага не равно «X», и наша новая цена будет в 2 раза выше текущей цены.
Шаг 1. Введите следующую формулу в ячейку E4 и нажмите клавишу ВВОД.
Разбивка формулы
В этой формуле с помощью D4<>"X" мы проверяем, не равно ли значение флага "X" или нет. Если условие верно, то цена удвоится, в противном случае она останется прежней.
Шаг 2. Скопируйте формулу до E8.
3. Использование функции ПРОСМОТР
Что касается поиска чего-либо в Excel, функция ПРОСМОТР будет правильным выбором для этого. Эта функция позволяет нам искать что-то по вертикали или горизонтали в рамках условия в определенном диапазоне. Для этих конкретных целей в Excel есть функции ВПР и ГПР. Давайте посмотрим на основы функции ВПР. Синтаксис функции такой:
Во-первых, значение -> содержит значение, которое нужно искать в первом столбце таблицы.
table -> Здесь будет имя таблицы.
col_index -> Это значение индекса столбца таблицы, откуда мы будем собирать значение.
[range_lookup] -> Этот последний раздел предназначен для обозначения необязательного диапазона.
Например, рассмотрим набор данных о некоторых фруктах, как раньше. Но здесь у нас будет 3 столбца: Fruits, ID, Price. Теперь будем искать цену Фруктов из этой таблицы с помощью ВПР.
Шаг 1. Введите формулу в ячейку G4.
Разбивка формулы
Здесь в функции я сначала передал значение, представляющее собой ячейку G3, затем таблицу, из которой мы хотим извлечь данные, которая указана диапазоном B3: D8 всей таблицы. После этого в третьей части мы получим значения из столбца «Цена», который является столбцом № 3, поэтому нам нужно передать 3. Наконец, 0 используется для указания того, что мы хотим точного совпадения.
Шаг 2. Вы можете узнать цену любого другого фрукта, введя название в ячейку G3.
Теперь мы увидим использование функций HLOOKUP, если наши данные спроектированы горизонтально. Синтаксис функций ГПР:
Это почти как функция ВПР. Единственное отличие состоит в том, что вместо индекса столбца здесь используется индекс строки в 3-й части параметра.
Шаг 1. Введите формулу в ячейку B8.
Разбивка формулы
Как мы уже говорили ранее, это почти как функция ВПР. Здесь я передал значение по строкам, а не по столбцам. Вот почему сначала ввели индекс строки нашего желаемого значения, которое равно B7. Кроме того, диапазон таблицы также меняется, так как наша таблица смещается по горизонтали.
4. Использование функций ИНДЕКС и ПОИСКПОЗ
В этом разделе мы будем делать то же самое, что и функция ПРОСМОТР, но с той лишь разницей, что здесь мы не будем использовать функцию ПРОСМОТР. Функции ИНДЕКС и ПОИСКПОЗ делают то же самое, что и ПРОСМОТР. Кроме того, набор данных будет таким же. Прежде чем перейти к примеру, давайте подробно рассмотрим эти две функции.
Эта функция может принимать максимум четыре аргумента и минимум два аргумента. В первом разделе своего параметра он берет диапазон ячеек, из которых мы будем проверять значение индекса. Затем идет номер строки ссылки или совпадающего значения. Последние два аргумента являются необязательными, с ними мы можем определить или указать номер столбца, из которого будут извлекаться совпадающие данные, а также номер диапазона области.
Другой часто используемой функцией является ПОИСКПОЗ. Первый аргумент принимает значение поиска или значение, которое мы собираемся сопоставить. Второй — это массив или диапазон, в котором мы будем искать нужные данные. И последний тип соответствия. В зависимости от различных значений типа соответствия мы можем управлять сопоставлением.
1 -> При объявлении 1 будет найдено наибольшее значение, меньшее или равное искомому значению.
0 -> Если мы поместим 0 в качестве типа соответствия, оно будет соответствовать значению, которое точно соответствует значению поиска.
-1 -> Это будет соответствовать наименьшему значению, большему или равному значению поиска.
Шаг 1. Введите формулу в ячейку G4.
Разбивка формулы
В этой части мы попытаемся сопоставить значение, которое находится в ячейке G3, в диапазоне от B3 до B8 в нашей таблице поиска. А так как мы рассматривали точное совпадение, то в последнем аргументе присваивается 0.
Внешней функцией является функция ИНДЕКС. В первой части я назначил диапазон ячеек. Затем совпадающее значение будет рассчитано с помощью функции ПОИСКПОЗ. Наконец, используется 3, так как мы хотим получить данные из третьего столбца нашей таблицы поиска.
5. Вернуть элементы из другого рабочего листа, если есть совпадение
Давайте создадим два рабочих листа: один — еженедельные приемы пищи, а другой — ингредиенты. Теперь я покажу, как сравнивать блюда и покажу ингредиенты на первом листе.Рабочий лист «Планирование питания на неделю» будет выглядеть следующим образом:
И рабочий лист ингредиентов блюд будет таким:
Теперь я покажу, как найти пищевые ингредиенты из листа ингредиентов в листе еды, введя название продукта в ячейку A12.
Шаг 1. Введите формулу в ячейку B12.
Разбивка формулы
Сначала мы передали номер ячейки значения поиска, который равен $A12, затем был отправлен диапазон таблицы других рабочих листов (рабочий лист ингредиентов) $A3:D14. После того, как функция COLUMN передана, чтобы получить значение столбца этой строки. Наконец, FALSE используется для поиска точного совпадения, что означает, что он чувствителен к регистру, и при объявлении false он будет искать точное соответствие значения.
Шаг 2. Скопируйте формулу справа. Затем отобразятся все ингредиенты выбранной еды.
Шаг 3. Вы можете проверить это, введя название любого продукта в ячейку A12 и нажав Enter.
Точно так же, если просто ввести любой продукт питания в поле «Название продукта», будут показаны все ингредиенты этого выбранного продукта из другого листа.
Заключение
Это способы сравнить одну ячейку с другой и вернуть другую ячейку в Excel. Я показал все методы с соответствующими примерами. Кроме того, я обсудил основы этой функции и наиболее часто используемые коды формата этой функции. Если у вас есть другой способ добиться этого, поделитесь им с нами.
< /p>
< /p>
Как вернуть другую ячейку, если одна ячейка равна тексту или другой в Excel?
Например, у меня есть следующий диапазон данных, теперь я хочу извлечь значения в столбце C, если соответствующие данные в столбце A равны соседним данным в столбце B, как показано ниже. Как мне справиться с этой задачей в Excel?
Возвращает другую ячейку, если одна ячейка равна другой с формулой
Потрясающе! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Сэкономьте 50 % своего времени и сократите количество кликов мышью каждый день!
Вот простая формула, которая поможет вам вернуть значение в другой ячейке, если одна ячейка равна соседней, сделайте следующее:
Введите следующую формулу: =ЕСЛИ(A2=B2, C2, "") (A2 и B2 — это две ячейки, которые вы хотите сравнить, а C2 — это значение ячейки, которое вы хотите вернуть) в пустую ячейку, где вы хотите отобразить результат, а затем перетащите дескриптор заполнения вниз к ячейкам, которые вы хотите вернуть соответствующие значения, см. снимок экрана:
Возвращает другую ячейку, если одна ячейка соответствует определенному тексту с формулой
Вот другая ситуация, допустим, у вас есть два столбца, и вам нужно вернуть значение ячейки на основе другой ячейки, если она содержит определенный текст, например «Да», как показано на следующем снимке экрана:
Для решения этой задачи следует применить следующую формулу: =ЕСЛИ(B2="да",A2,"") в пустую ячейку для вывода результата, а затем перетащите манипулятор заполнения вниз к нужным ячейкам. хотите содержать эту формулу, и все значения рядом с вашим конкретным текстом были возвращены, как показано на следующем снимке экрана:
Вернуть другую ячейку, если одна ячейка соответствует определенному тексту с помощью Kutools for Excel
Если у вас есть Kutools for Excel, с его функцией «Выбор определенных ячеек» вы можете быстро выбрать строки с определенным текстовым значением, а затем вставить строки в другое место по мере необходимости.
После установки Kutools for Excel сделайте следующее:
<р>1. Выберите столбец, который содержит нужный вам текст, а затем нажмите Kutools > Выбрать > Выбрать определенные ячейки, см. снимок экрана:
Демонстрация: вернуть другую ячейку, если одна ячейка соответствует определенному тексту с помощью Kutools for Excel
Kutools for Excel: с более чем 200 удобными надстройками Excel, которые можно бесплатно попробовать без ограничений в течение 60 дней. Скачать и бесплатно попробовать прямо сейчас!
Читайте также: