Несколько значений в одной ячейке Excel
Обновлено: 05.07.2024
При работе с большими объемами данных в Excel иногда вы можете столкнуться с ситуацией, когда вам нужно искать значения, которые встречаются в вашем наборе данных несколько раз. Итак, вы должны увидеть эти данные в одной ячейке. В этом руководстве мы покажем вам, как использовать функцию ВПР для нескольких значений в одной ячейке в Excel.
Вообще говоря, здесь мы не используем функцию ВПР напрямую. Мы собираемся найти несколько значений в одной ячейке, которые будут аналогичны функции ВПР. Мы надеемся, что это руководство окажется для вас полезным.
Скачать рабочую тетрадь
2 простых метода для поиска нескольких значений в одной ячейке
Теперь мы собираемся показать вам 2 способа поиска нескольких значений в одной ячейке в Excel. Первый использует формулы, а второй использует коды VBA. В этой статье мы рассмотрим как повторяющиеся, так и неповторяющиеся значения. Таким образом, вы можете выбрать наиболее подходящий метод в зависимости от вашей проблемы.
Чтобы продемонстрировать эту проблему, мы будем использовать следующий набор данных:
Здесь у нас есть имена некоторых продавцов и их продаваемые продукты. Теперь наша цель — найти продукты, которые продает каждый продавец.
1. Использование формул для поиска нескольких значений в одной ячейке в Excel
Функция TEXTJOIN будет нашей основной функцией для реализации этого метода. Функция TEXTJOIN позволяет вам объединить 2 или более строк вместе, при этом каждое значение отделяется разделителем. В основном мы комбинируем различные функции с функцией TEXTJOIN для реализации нашей формулы.
Основной синтаксис функции TEXTJOIN:
Здесь нашим разделителем будет запятая (",") для разделения значений в одной ячейке.
1.1 Функции TEXTJOIN и IF
Теперь эту формулу довольно легко использовать. Эта формула будет искать значения, а также вставлять их в одну ячейку с разделителем, запятой. Но помните, что эта формула вернет значение с дубликатами.
Основной синтаксис:
📌 Шаги
<р>1. Сначала введите следующую формулу в ячейку F5:
В конце концов, нам удалось использовать ВПР несколько значений в одной ячейке.
🔎 Разбивка формулы
Эта разбивка используется только для человека "Джон"
➤ ЕСЛИ(E5=B5:B13,C5:C13,"")
Эта функция возвращает следующий массив:
➤ ТЕКСТСОЕДИНЕНИЕ(", ",ИСТИНА,ЕСЛИ(E5=B5:B13,C5:C13,""))
Наконец, функция TEXTJOIN вернет следующий результат:
1.2 Функции TEXTJOIN и MATCH (без дубликатов)
Теперь, если вам нужно несколько значений в одной ячейке, вы можете использовать эту формулу. Эта формула представляет собой комбинацию функций TEXTJOIN и MATCH. Эта формула немного сложна в использовании, но, несомненно, даст желаемые значения.
📌 ШАГИ
<р>1. Сначала введите следующую формулу в ячейку F5:=TEXTJOIN(",", ИСТИНА, ЕСЛИ(ЕСЛИОШИБКА(СООТВЕТСТВИЕ(C5:C13, ЕСЛИ(E5=B5:B13, C5:C13, ""), 0),"")=MATCH(СТРОКА(C5 :C13), РЯД(C5:C13)), C5:C13, ""))
В конце концов, нам удалось использовать ВПР несколько значений в одной ячейке без дублирующихся значений.
🔎 Разбивка формулы
Эта разбивка используется только для человека "Джон"
➤ СТРОКА(C5:C13)
Возвращает массив
➤ ПОИСКПОЗ(СТРОКА(C5:C13), СТРОКА(C5:C13))
➤ ЕСЛИ(E5=B5:B13, C5:C13, "")
➤ ПОИСКПОЗ(C5:C13, ЕСЛИ(E5=B5:B13, C5:C13, "")
Эта функция возвращает:
➤ ЕСЛИОШИБКА(ПОИСКПОЗ(C5:C13, ЕСЛИ(E5=B5:B13, C5:C13, ""), 0),"")
➤ ЕСЛИ(ЕСЛИОШИБКА(ПОИСКПОЗ(C5:C13, ЕСЛИ(E5=B5:B13, C5:C13, ""), 0),"")=ПОИСКПОЗ(СТРОКА(C5:C13), СТРОКА(C5: C13)), C5:C13, "")
➤ СОЕДИНЕНИЕ ТЕКСТА(",", ИСТИНА, ЕСЛИ(ЕСЛИОШИБКА(СООТВЕТСТВИЕ(C5:C13, ЕСЛИ(E5=B5:B13, C5:C13, ""), 0),"")=ПОИСКПОЗ(СТРОКА(C5 :C13), РЯД(C5:C13)), C5:C13, ""))
Конечным результатом будет Мобильный телефон, ТВ, Холодильник.
1.3 Функции TEXTJOIN и UNIQUE (без дубликатов)
Теперь функция УНИКАЛЬНАЯ доступна только в Excel 365. Поэтому, если вы используете Excel 365, вы определенно можете использовать эту формулу. Предыдущая формула немного сложна, но эта формула облегчит поиск значений в одной ячейке. Функция UNIQUE возвращает список уникальных значений в списке или диапазоне. Теперь разница между первой и третьей формулой заключается в использовании функции UNIQUE перед функцией IF.
Основной синтаксис функции UNIQUE:
массив — диапазон или массив, из которого извлекаются уникальные значения.
by_col – [необязательно] Как сравнивать и извлекать. По строке = ЛОЖЬ (по умолчанию); по столбцу = ИСТИНА.
exactly_once – [необязательно] TRUE = значения, которые встречаются один раз, FALSE = все уникальные значения (по умолчанию)
📌 ШАГИ
<р>1. Сначала введите следующую формулу в ячейку F5:
Как видите, мы успешно использовали функцию ВПР для нескольких значений в одной ячейке.
🔎 Разбивка формулы
Эта разбивка используется только для человека "Джон"
➤ ЕСЛИ(E5=B5:B13,C5:C13,"")
➤ УНИКАЛЬНЫЙ(ЕСЛИ(E5=B5:B13,C5:C13,""))
➤ TEXTJOIN(", ",ИСТИНА,УНИКАЛЬНЫЙ(ЕСЛИ(E5=B5:B13,C5:C13,"")))
Конечный результат Мобильный,Телевизор,Холодильник
2. Использование кодов VBA для поиска нескольких значений в одной ячейке
Функция ОБЪЕДИНЕНИЕ СОЕДИНЕНИЕ доступна только для MS Excel 2019 и MS Excel 365. Так что, если вы хорошо знакомы с кодами Excel VBA, эти два кода будут вам очень полезны. Первый будет с дубликатами, а второй — без дубликатов. Итак, выберите метод в соответствии с вашей проблемой.
2.1 VBA кодирует несколько значений в одной ячейке
📌 ШАГИ
<р>1. Первый. Нажмите Alt+F11, чтобы открыть редактор Visual Basic. <р>2. Затем нажмите «Вставить» > «Модуль».
В итоге мы использовали функцию ВПР для нескольких значений в одной ячейке.
2.2 Коды VBA для ПОИСКА нескольких значений в одной ячейке (без дубликатов)
📌 ШАГИ
<р>1. Первый. Нажмите Alt+F11, чтобы открыть редактор Visual Basic. <р>2. Затем нажмите «Вставить» > «Модуль». <р>3. Затем введите следующий код: <р>4. После вставки кода нажмите «Инструменты» > «Ссылки» в открывшемся окне Microsoft Visual Basic для приложений, а затем в появившемся диалоговом окне «Ссылки — VBAProject» установите флажок «Среда выполнения сценариев Microsoft» в списке «Доступные ссылки». Нажмите "ОК".
Здесь 2 — это номер столбца набора данных.
Как видите, мы использовали функцию ВПР для нескольких значений в одной ячейке без дубликатов.
Заключение
Статьи по теме
- Как использовать функцию ВПР с несколькими условиями в Excel (2 метода)
- Как удалить дубликаты с помощью функции ВПР в Excel (2 метода)
- Excel VLOOKUP возвращает несколько значений по вертикали
Шанто
Здравствуйте! Я Шанто. Разработчик контента Excel и VBA. Моя цель — предоставить нашим читателям отличные руководства по различным проблемам, связанным с Excel. Я надеюсь, что наши простые, но эффективные уроки обогатят ваши знания. Я получил степень бакалавра компьютерных наук и инженерии в Международном университете Даффодил. Работа с данными всегда была моей страстью. Любите работать с данными, анализировать их и находить закономерности. Кроме того, любите исследовать. Всегда ищите вызовы, которые помогут мне расти.
Этапы объединения нескольких данных из разных ячеек в одну ячейку
Оглавление
<р>1. Откройте свою книгу. <р>2. Выберите ячейку, в которую вы хотите поместить все свои данные.
4. Введите & и используйте кавычки с пробелом.
5. Выберите другую ячейку, которую хотите объединить, и нажмите Enter. Например =A3&" "&B3.
Это работает, только если вы хотите объединить две ячейки в одну ячейку.
Шаги с использованием функции CONCAT.
Вы используете запятые для разделения объединяемых ячеек и кавычки для добавления пробелов, запятых или другого текста.
5. Закройте формулу скобками и нажмите Enter. например может быть =concat(A2, "врачи").
Если у вас много текстов и вы хотите, чтобы они помещались в одну ячейку, это легко сделать. Если вы поместите всю строку в одну ячейку, она будет продолжаться, поэтому вам нужен способ организовать вашу работу и поместить фактические данные в одну ячейку. В основном это делается путем обтекания текста как абзаца или вставки разрыва строки внутри ячейки.
Шаги о том, как обтекать текст, чтобы он поместился в ячейку.
Единственное, что вам нужно сделать, это отформатировать текст так, чтобы он автоматически переносился.
<р>1. Щелкните правой кнопкой мыши внутри ячейки. <р>2. Появится меню.
< /p>
<р>3. Отформатируйте ячейки, откроется диалоговое окно, перейдите на вкладку выравнивания и установите флажок рядом с переносом текста.
Вставка разрыва строки в ячейку
Разрыв строки позволит вам разбивать каждое предложение, когда вы хотите, и так, как вы хотите, чтобы они отображались.
<р>1. Введите каждое предложение в ячейке. <р>2. Чтобы вставить жесткий возврат, нажмите ALT-ENTER. (На Mac выполните навигацию, как CTR-OPTION-ENTER. Или просто нажмите команду и введите.Вово избежание проблем с читабельностью данные в ячейке будут выравниваться по нижнему краю вертикальных линий элемента управления ячейкой.
4. Щелкните правой кнопкой мыши, и появится всплывающее меню. Как это было в первой процедуре.
5 . Выберите формат ячеек и вернитесь на вкладку выравнивания.
Это все, и ваши данные будут чистыми и читабельными. Вмещать все данные в одну ячейку раздражает, когда вы не знаете, как переносить текст или вставлять разрыв строки. Иногда сложно скопировать и вставить. Это будет следующее руководство по копированию и вставке данных в ячейку, которая встроена с жесткими возвратами.
Мы часто оказываемся в ситуации, когда нам нужно другое представление существующих данных. Как вы, наверное, знаете, в Excel есть решение и для этого.
В приведенном ниже примере показано, как вернуть несколько значений из таблицы в одну ячейку.
Вернуть несколько значений в одной ячейке
В нашем примере мы будем использовать таблицу с игроками НБА с несколькими статистическими данными: очки, подборы, передачи и потери.
Чтобы перечислить любую статистическую категорию для любого игрока, мы будем использовать формулу TEXTJOIN.
Эта формула доступна только в Excel 2019 и Excel 365.
Что делает эта формула: она использует и затем комбинирует текст из выбранных вами диапазонов, а затем устанавливает разделитель, который вы определяете, между каждым из текстовых значений, которые будут объединены. Если вы выберете в качестве разделителя пустую строку, будет возвращено несколько значений одно за другим.
Эта формула имеет несколько аргументов:
- разделитель. В качестве разделителя можно выбрать что угодно. Он отделяет наш текст.
- ignore_empty — мы выбираем, хотим ли мы игнорировать пустые ячейки или нет.
- text1– первый диапазон или текстовое значение.
- text2– второй диапазон или текстовое значение.
Мы подготовим имена игроков и определим точки столбца:
Наша формула в ячейке K2 будет выглядеть так:
В этой формуле мы сначала определяем наш разделитель как запятую и пробел, то есть «, ».
Затем мы выбираем TRUE для игнорирования пустых ячеек.
Далее мы определяем наш текст с помощью формулы ЕСЛИ. Логический тест в этой формуле проверяет, совпадает ли значение в ячейке J2 (в нашем примере с Леброном Джеймсом) и в диапазоне имен наших игроков — диапазоне A2:A28.
Эта формула входит в каждую ячейку нашего диапазона и проверяет, совпадают ли значения. Если да, возвращается ИСТИНА. Если нет, возвращается FALSE.
Следующая часть формулы возвращает все очки, которые определенный игрок (в данном случае находится в ячейке J2) имел во всех играх. Мы делаем это, убедившись, что часть value_if_true этой формулы равна диапазону, в котором находятся точки (то есть диапазону D2:D28).
Итак, если имя в ячейке J2 соответствует имени в нашем диапазоне, то будут представлены очки для этого игрока и будут разделены запятой и пробелом.
Результаты следующие:
Все, что нам нужно сделать, чтобы применить ту же концепцию к Энтони Дэвису, — это перетащить формулу в ячейку K2. Поскольку у нас заблокированы определенные диапазоны, мы получим правильные числа:
Эта формула отличается для пользователей Excel 2019 и Microsoft 365. Если вы используете Excel 2019, вам нужно ввести формулу, а затем, удерживая CTRL + SHIFT, нажать клавишу ВВОД. Причина этого в том, что в этой формуле мы имеем дело с динамическими массивами. Они встроены в Microsoft 365, поэтому все, что вам нужно сделать, это ввести формулу и нажать клавишу ВВОД.
В Microsoft Excel существует ряд подходящих способов поиска критерия и последующего возврата нескольких значений, объединенных в одну ячейку на основе этого критерия. В этой статье вы узнаете обо всех доступных методах извлечения соответствующих значений, объединенных в одну ячейку, с соответствующими иллюстрациями.
Загрузить практическую рабочую тетрадь
Вы можете скачать книгу Excel, которую мы использовали для подготовки этой статьи.
4 метода поиска и возврата нескольких значений, объединенных в одну ячейку
1. Использование функции TEXTJOIN для поиска и извлечения нескольких значений, объединенных в одну ячейку
В следующем наборе данных есть несколько моделей смартфонов с их брендами. Мы извлечем названия моделей указанной марки, а затем объединим их в одну ячейку. Используя функцию TEXTJOIN, мы можем легко удовлетворить это требование, поскольку эта функция объединяет список или диапазон текстов с помощью разделителя.
Например, мы хотим объединить все названия моделей смартфонов Xiaomi в одну ячейку. Таким образом, требуемая формула в выходной ячейке C15 будет выглядеть так:
После нажатия Enter вам сразу будут показаны все названия моделей Xiaomi в ячейке.
2. Использование VBA для поиска и возврата нескольких значений, объединенных в одну ячейку
Мы также можем создать определяемую пользователем функцию с помощью VBA, а затем объединить несколько ячеек в одну на основе заданных условий.
📌 Шаг 1
➤ Нажмите ALT+F11, чтобы открыть окно VBA.
➤ Выберите параметр "Модуль" на вкладке "Вставка".
➤ В новом окне модуля скопируйте и вставьте следующие коды:
➤ Нажмите F5, чтобы запустить коды.
➤ Вернитесь к электронной таблице Excel, снова нажав клавиши ALT+F11.
Итак, мы только что создали определяемую пользователем функцию, а именно:
=CONCATIF(LookupRange, LookupVal, ConcatRange, [Разделитель])
📌 Шаг 2
➤ Теперь выберите выходную ячейку C15 и введите:
➤ Нажмите Enter, и готово.
Вы найдете объединенные значения в одной ячейке, как показано на снимке экрана ниже.
3. Поиск и возврат нескольких уникальных значений, объединенных в одну ячейку
Теперь давайте рассмотрим другой сценарий, в котором названия моделей смартфонов могут дублироваться в столбце C. Такие случаи характерны для рабочих листов с большим объемом данных. Здесь мы будем извлекать и объединять все модели смартфонов указанной марки в одну ячейку, но отображать название каждой модели только один раз, если будут найдены дубликаты.
В выходной ячейке C18 требуемая формула в соответствии с указанными критериями будет выглядеть так:
=TEXTJOIN(", ", ИСТИНА, ЕСЛИ(ЕСЛИОШИБКА(СООТВЕТСТВИЕ(C5:C15, ЕСЛИ(C17=B5:B15, C5:C15, "")), 0), "")=MATCH(СТРОКА(B5 :B15), РЯД(B5:B15)), C5:C15, ""))
После нажатия Enter вы найдете все названия моделей смартфонов марки Xiaomi, объединенные в одну ячейку, но без дублирования.
🔎 Как работает формула?
- ЕСЛИОШИБКА(ПОИСКПОЗ(C5:C15, ЕСЛИ(C17=B5:B15, C5:C15, ""), 0), ""): эта часть формулы проверяет, совпадает ли условие, определенное в ячейке C17, с значения в диапазоне ячеек B5:B15 и возвращает соответствующие номера строк значений. Если для значения найдено дублирование, то возвращается номер строки для первого значения. Таким образом, эта часть формулы возвращает следующий массив:
- ЕСЛИ(ЕСЛИОШИБКА(ПОИСКПОЗ(C5:C15, ЕСЛИ(C17=B5:B15, C5:C15, ""), 0), "")=ПОИСКПОЗ(СТРОКА(B5:B15), СТРОКА(B5:B15) )), C5:C15, «»): эта часть является третьим аргументом (text1) функции TEXTJOIN, которая определяет объединяемые операторы. Здесь значения из столбца C будут извлечены на основе номеров строк, найденных на предыдущем шаге. Таким образом, возвращаемый массив здесь будет таким:
- Наконец, вся формула будет объединять значения, полученные на предыдущем шаге, с определенным разделителем — запятой (,).
4. Использование VBA для поиска и возврата нескольких уникальных значений, объединенных в одну ячейку
Мы также можем использовать коды VBA, чтобы сформулировать другую определяемую пользователем функцию, которая будет применяться для объединения нескольких уникальных значений в одну ячейку.
📌 Шаг 1
➤ Нажмите ALT+F11 и откройте окно VBA.
➤ Активируйте новый модуль на вкладке "Вставка", а затем вставьте следующие коды в окно нового модуля:
➤ Нажмите F5, и готово.
Определяемая пользователем функция, которую мы только что создали:
=CONCATUNIQUE(LookupValue, LookupTable, Col_Num)
📌 Шаг 2
➤ Вернитесь к электронной таблице Excel, еще раз нажав клавиши ALT+F11.
➤ В выходной ячейке C18 введите:
➤ Нажмите Enter.
Вы получите возвращаемые значения в одной ячейке, как показано на рисунке ниже.
Заключительные слова
Я надеюсь, что все эти методы, упомянутые выше, теперь помогут вам применить их в электронной таблице Excel для поиска и возврата нескольких значений, объединенных в одну ячейку. Если у вас есть какие-либо вопросы или отзывы, пожалуйста, дайте мне знать в разделе комментариев. Или вы можете ознакомиться с другими нашими статьями, посвященными функциям Excel, на этом веб-сайте.
Читайте также: