Объединить 2 списка в excel
Итак, у меня есть несколько столбцов с числами, и мне интересно, как сделать из них динамический объединенный список? Я знаю, что консолидация теоретически может работать, но мои списки меняются, и я не думаю, что консолидация является динамичной. «Список комбайнов» справа - это то, чего я надеюсь достичь. Мне нужны все дубликаты, но я не хочу никаких пробелов, потому что, например, столбец C может легко перейти с 3 чисел на 5 чисел. Кроме того, сортировка списка была бы супер-бонусом, так как это моя конечная цель, поэтому, если я могу сделать это другим способом, дайте мне знать.
3 ответа
Попробуйте с O365:
Если вы хотите отсортировать список, то:
РЕДАКТИРОВАТЬ Если вы работаете на MAC или в онлайн-приложении Excel, у вас нет функции FILTERXML . Вместо этого можно использовать следующую формулу:
Эта формула будет работать с немного более чем 300 символами (цифрами), после чего вы столкнетесь с ограничением 32 767 символов для TEXTJOIN . Первая формула будет иметь ограничение чуть больше 3000 чисел. Если у вас может быть больше, вам следует взглянуть на решение VBA
numBers - это именованный диапазон, больший, чем вам нужно. Пробелы игнорируются. В этом случае я использую c10:f100 , но вы можете увеличить размер по своему усмотрению.
с сортировкой
Формула решения для всех версий Excel
1] "Объединить список с сортировкой" в H10 , формула скопирована:
2] «Объединить список с сортировкой и удалением дубликатов» в I10 , формула скопирована:
Поскольку вы используете Office 365, попробуйте эту небольшую функцию, определяемую пользователем:
Как видите, он динамически переливается.
Если вам нужна формула, а не UDF, попробуйте:
Чтобы удалить оба магических числа (4, 9) , мы можем использовать:
На вашем листе два списка, и некоторые значения в списках дублируются, и вы хотите объединить эти два списка и просто оставить уникальные значения, как показано на скриншотах ниже, как вы можете это сделать?
 |  |  |
- Повторное использование чего угодно: Добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
- Более 20 текстовых функций: Извлечь число из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
- Инструменты слияния : Несколько книг и листов в одну; Объединить несколько ячеек / строк / столбцов без потери данных; Объедините повторяющиеся строки и сумму.
- Разделить инструменты : Разделение данных на несколько листов в зависимости от ценности; Из одной книги в несколько файлов Excel, PDF или CSV; От одного столбца к нескольким столбцам.
- Вставить пропуск Скрытые / отфильтрованные строки; Подсчет и сумма по цвету фона ; Отправляйте персонализированные электронные письма нескольким получателям массово.
- Суперфильтр: Создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделям, дням, периодичности и др .; Фильтр жирным шрифтом, формулы, комментарий .
- Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.
Объединяйте списки без дубликатов с помощью функции удаления дубликатов в Excel
Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!
Чтобы объединить два списка и удалить повторяющиеся значения в Excel, вы можете сделать следующее:
1. Скопируйте один из двух списков и вставьте его в конец другого списка, см. Снимок экрана:
| |  |
2. Выберите список и щелкните Данные > Удалить дубликаты, см. снимок экрана:
3. в Удалить дубликаты диалоговое окно, если у вашего столбца нет заголовка, снимите флажок У моих данных есть заголовки, А затем нажмите OK. Смотрите скриншот:
4. Затем на экране отобразится диалоговое окно, в котором сообщается, что дубликаты были удалены, щелкните OK. Смотрите скриншот:
Вы можете увидеть результат:
Объединяйте списки без дубликатов с VBA
В Excel VBA также может помочь вам объединить два списка без дубликатов.
1. Держать ALT и нажмите F11 на клавиатуре, чтобы открыть Microsoft Visual Basic для приложений окно.
2. Нажмите Вставить > Модуль, и скопируйте VBA в модуль.
VBA: объединять списки без дубликатов
3. Нажмите Запустите или нажмите F5 для запуска VBA.
4. На экране появится диалоговое окно, в котором вы можете выбрать диапазон, который хотите объединить. Смотрите скриншот:
5. Нажмите OK, затем появилось другое диалоговое окно, в котором вы можете выбрать ячейку для вывода комбинированного результата. Смотрите скриншот:
6. Нажмите OK. Вы можете видеть, что списки были объединены.
Совет: результат не может сохранить форматирование ячеек после выполнения вышеуказанного VBA.
Объединяйте списки без дубликатов с помощью Kutools for Excel
С установленным Kutools for Excel, вы можете использовать Выберите дубликаты и уникальные ячейки функция для решения проблемы, объединяющая два списка без дубликатов.
Kutools for Excel включает более 300 удобных инструментов Excel. Бесплатная пробная версия без ограничений в течение 30 дней. Получить сейчас
1. Скопируйте один из двух списков и вставьте его в конец другого списка, выберите новый список, затем щелкните Kutools > Выберите Инструменты > Выберите дубликаты и уникальные ячейки. Смотрите скриншот:
2. в Выберите дубликаты и уникальные ячейки диалог, проверьте Все уникальные посетители (включая 1-е дубликаты), нажмите Ok.
3. Затем появится диалоговое окно, в котором указано, сколько уникальных значений было выбрано, щелкните OK и Отмена закрыть два диалога. Смотрите скриншот:
4. Скопируйте выбранные уникальные значения и вставьте их в новый столбец. Смотрите скриншот:
В данной статье рассмотрим три способа соединить (сцепить, склеить) данные из разных ячеек. С помощью знака амперсанд (&) и с помощью функций СЦЕПИТЬ, СЦЕП, ОБЪЕДЕНИТЬ.
Соединение данных с помощью знака амперсанд (&).
Выглядит это вот так:
Пробелы, между данными взятыми из ячеек, появятся автоматические.
Тоже самое можно сделать с числовыми данными.
Так же, вместе с ссылками на ячейки, прямо в формуле можно прописать интересующие нас данные. Например добавим перед ФИО, должность: бухгалтер.
Выглядеть это будет вот так:
Важно.
Для того, чтобы прописать данные (слово, число и т.д.) непосредственно в формуле, их нужно заключить в кавычки. Если этого не сделать, формула выдаст ошибку и может предложить вариант ее исправления.
Как вызвать функции СЦЕПИТЬ, СЦЕП, ОБЪЕДЕНИТЬ.
Нажимаем кнопку Вставит функцию. В открывшемся диалоговом окне Вставка функции, в поле Поиск функции вводим точное название интересующей нас функции. Нажимаем Найти. В результат поиска выбираем нужную нам функцию.
Функция СЦЕПИТЬ.
В результатах поиска выбираем функцию СЦЕПИТЬ. Появляется диалоговое окно Аргументы функции. В данном диалоговом окне есть поле Текс1, Текст2 и т.д. В этих полях можно указать ссылки на ячейки, данные из которых потом объединяться в единой, указной ячейке. Например у нас есть три ячейки с данными В2; С2; D2. Объединим данные из этих трех ячеек в одной (В4) с помощью функции СЦЕПИТЬ.
Так же, в поле Текст1 (и т.д.), вместо ссылок на ячейки, можно добавлять другие данные. Например текст, числа и т.д. Добавим название должности: бухгалтер. Добавим название должности в поле Текст4. Слово бухгалтер появиться после ФИО. Так же необходимо добавит пробел между ФИО и словом Бухгалтер (поле Текст3). Так как функция не делает это автоматически, между данными взятыми из ячеек и данными вписанными в Аргументы функции напрямую. Сделать это очень просто. Ставим курсор в поле Текст3 и нажимаем на пробел. Кавычки появляются автоматически.
Поля Текст, в окне Аргументы функции, добавляются по необходимости, автоматически. Максимальное количество таких полей 255.
Функции СЦЕП.
Основанное отличие функции СЦЕП от функции СЦЕПИТЬ в том, что она позволяет в поле Текст указывать диапазон ячеек, данные из которых будут объединены. Например объединим данные из ячеек В2; С2; D2. При это укажем их как диапазон (B2:D2) в поле Текст1.
Так же, можно указывать несколько диапазонов в разных полях Текст, которые будут объединены. Как и в случае с функцией СЦЕПИТЬ, в поле Текст, можно прописывать различные данные (текст, числа и т.д.). Максимальное количество полей Текст -254 шт.
Функции ОБЪЕДЕНИТЬ.
При выборе данной функции открывается диалоговое окно Аргументы функции, в котором есть следующие поля:
Разделитель. В этом поле указывается символ, который функция вставит между объединяемыми данными. Так же, можно указать ссылку на ячейку, функция возьмет данные от туда и вставит между объединяемыми данными.
Пропускать_пустые. Здесь можно поставить значение аргумента функции один (1). Тогда пустые ячейки, которые находятся в диапазоне объединяемых ячеек, не будут учитываться (не отразятся в объединённых данных). Если поставить значение аргумента функции ноль (0), тогда функция будет учитывать пустые ячейки.
Кавычки в поле Разделитель появляются автоматически. Нажимаем ОК.
Таблица в Microsoft Excel состоит из множества ячеек, в каждой из которых пользователь может записать определенный набор данных, будь то число, текст или целая функция. В некоторых случаях, как в визуальных, так и функциональных целях, требуется объединение нескольких ячеек в одну, что осуществляется тремя разными способами.
Метод 1: Кнопка «Объединить ячейки» на главной вкладке
Кнопка «Объединить ячейки» соединяет выбранный диапазон клеток в одну, оставляя только значение первой и удаляя остальные, что необходимо учитывать еще перед выполнением данного метода. В большинстве случаев он будет полезен, когда вы хотите расположить название таблицы или какую-либо строку по ее центру, избавившись от пустых ячеек рядом. Процесс объединения выглядит следующим образом:
Зажмите левую кнопку мыши и выделите все ячейки, которые хотите объединить, после чего на вкладке «Главная» вызовите меню «Объединить».
Из выпадающего списка выберите понравившийся вам вариант. Вы можете выполнить простое объединение, сделать это с автоматическим расположением по центру или затронуть строки. Возле каждого варианта есть своя миниатюра, описывающая действие инструмента.
После выбора одного из них ячейки объединяются, и необходимо изменить расположение текста, если изначально он отображается некорректно. Для этого используйте стандартные функции редактора.
Вот таким нехитрым образом несколько ячеек объединяются в одну с сохранением содержимого первой, то есть крайней левой. Используйте этот метод для оглавлений и других визуальных составляющих, не разделяя надписи на несколько клеток, а добавляя их грамотно.
Метод 2: Настройка формата ячеек
Этот способ является отражением предыдущего, поскольку приводит к такому же результату, но выполняется иначе. Для активации функции объединения в этом случае понадобится обратиться к меню «Формат ячеек», которое знакомо многим своими настройками значений в ячейках.
Выделите требуемые ячейки ЛКМ и щелкните по любой из них правой кнопкой мыши, вызвав тем самым контекстное меню.
В нем вас интересует пункт «Формат ячеек».
В новом окне перейдите на вкладку «Выравнивание» и активируйте галочку возле пункта «Объединение ячеек». Подтвердите изменения и закройте окно, щелкнув по «ОК».
Обратите внимание на то, как теперь отображается содержимое в уже объединенной ячейке. Измените формат текста, если его нужно расположить с краю или по центру.
Метод 3: Амперсанд или функция СЦЕПИТЬ
Если предыдущие два варианта чаще применялись в тех ситуациях, когда необходимо обеспечить хорошее визуальное отображение деталей книги в Эксель, то этот функционально подкреплен. Использование амперсанда или функции СЦЕПИТЬ позволяет объединить содержимое нескольких ячеек в одну, не потеряв при этом ни одного значения, что происходит в других методах, указанных выше.
Возьму за пример простой текст, но вы можете сделать те же действия и с числами. Допустим, в трех разных ячейках находятся надписи, которые нужно слить в одной ячейке. Это может быть ФИО или любая другая информация. На следующем изображении вы видите этот пример.
Выберите пустую ячейку, в которой хотите соединить содержимое выбранных. Начните запись функции со знака «=» и после номера каждой ячейки добавляйте «&» (то есть знак амперсанда). По итогу должно получиться что-то типа =A14&C14&B14.
По завершении нажмите Enter и ознакомьтесь с результатом. На скриншоте ниже вы более наглядно видите то, как происходит соединение.
Добавьте после каждой ячейки двойные кавычки с пробелом, если не хотите, чтобы весь текст слился в один. Тогда функция будет выглядеть примерно так: =A14&" "&C14&" "&B14.
Еще раз повторюсь, что подобная реализация соединения особенно актуальна для ФИО или похожей информации, которую часто записывают в отдельных ячейках. Для такой функции тоже работает растяжение, поэтому вы можете записать ее только один раз, а затем увеличить на необходимый диапазон, автоматизировав объединение текста или цифр.
Осталось только понять, как работает функция СЦЕПИТЬ, по сути выполняющая практически те же действия, что и амперсанд, но с возможностью комбинирования других формул, что может пригодиться продвинутым пользователям.
В пустой ячейке для объединения остальных объявите функцию =СЦЕПИТЬ().
Впишите номера клеток, которые хотите соединить в одну, ставя знак «;» после каждой.
В итоге вы видите объединение без пробелов.
Добавьте двойные кавычки с пробелами так же, как это было показано в примере с амперсандом, если не хотите видеть слитные надписи или числа.
Как визуальное, так и функциональное соединение ячеек в Microsoft Excel понадобится практически каждому пользователю, работающему над таблицами. Запомнить описанные методы будет не так сложно, поскольку все они подразумевают выполнение всего нескольких действий. В будущем любой из них может пригодиться, и объединение клеток уже не будет для вас чем-то сложным.
Читайте также: