Объединить строки Excel из двух листов

Question

Я хотел бы начать этот вопрос, извиняясь, есть многочисленные вопросы о слиянии строк Excel, тем не менее, после просмотра веб - страниц , а список « Вопросы , которые могут уже есть свой ответ », я до сих пор не знаю , как решить мой проблема.

РЕДАКТИРОВАТЬ: Кажется, что возможные решения могут отличаться в зависимости от типа данных в разных таблицах, которые я хотел бы объединить. В этом случае кажется, что использование функции "Консолидировать" решает проблему, когда все записи данных (кроме самого левого столбца) являются числовыми. Моя проблема заключается в поиске решения для смешанных данных чисел и текста (текст также может содержать не английские буквы). Вопрос был отредактирован с целью предоставления лучших примеров.

У меня есть две таблицы с разными данными. Единственное сходство в значениях самого левого столбца:

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

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

Некоторые важные замечания:

Я предпочитаю работать с исходными электронными таблицами, а не объединять их в одну промежуточную непогруженную электронную таблицу, но я приветствую любое решение, которое поможет мне достичь моей цели.

Записи данных (как показано на рисунках выше) могут быть неполными и содержать как цифры, так и текст. Кроме того, записи данных могут быть неправильными, поэтому я не могу предположить, что в данном столбце всегда будут цифры или текст (например, столбец «Отгрузка» электронной таблицы 2).

Существуют строки, в которых значение в левом столбце присутствует только в одной из электронных таблиц (например, "name5" происходит только из электронной таблицы 1, а "name3" и "name6" только из электронной таблицы 2).

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

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

Answer 1

Для числовых данных ...

Попробуйте использовать встроенную функцию консолидации .

Допустим, у меня есть фф. данные в Sheet1 и Sheet2 соответственно.

1. Выберите ячейку назначения. Любая ячейка / диапазон будет делать до тех пор, пока у вас будет достаточно места для результата. Я рекомендую создать новый лист и выделить ячейку A1.
2. Перейдите в Данные > Консолидировать. Вы также можете нажать Alt+A, N.
3. Выберите Сумма.

4. В разделе « Ссылка» нажмите кнопку «Выбрать ссылку» и выделите данные на листе 1 (включая метки строк и заголовки). Нажмите Добавить.

   

5. Повторите предыдущий шаг, но вместо этого добавьте данные в Sheet2.

   

6. В разделе « Использовать метки» убедитесь, что отмечены оба столбца :« Верхний ряд» и « Левый столбец». У вас будет что-то вроде этого:

   

7. Нажмите ОК.

Результат:

---

Для различных типов данных ...

1. Используйте функцию консолидации, чтобы получить уникальные метки строк и заголовки столбцов. Очистите данные, но оставьте метки / заголовки:

   

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

   =IFERROR(IFERROR( INDEX(sheet1_data,MATCH($A2,sheet1_rowlabels,0),MATCH(B$1,sheet1_headers,0)), INDEX(sheet2_data,MATCH($A2,sheet2_rowlabels,0),MATCH(B$1,sheet2_headers,0))), "")

   куда
   sheet1_data , sheet1_rowlabels & sheet1_headers ссылаются на все данные (A1:C5), первый столбец (A1:A5) и первую строку (A1:C1) в Sheet1 соответственно
   sheet2_data , sheet2_rowlabels & sheet2_headers ссылаются на все данные (A1:D6), первый столбец (A1:A6) и первую строку (A1:D1) в Sheet2 соответственно

3. Формат по желанию.

   

Answer 2

Если я правильно понимаю ваш вопрос, это можно сделать простым способом с помощью VLOOKUP.

Сначала вам нужно будет создать недублированный, отсортированный список значений "Item", которые находятся в левых столбцах обеих таблиц (по линиям, показанным в примере "Final Result").

Это может быть сделано:

• Копирование списка элементов из первой таблицы в столбец А листа, на котором вы хотите сохранить конечный результат
• Копирование списка элементов из второй таблицы и вставка его под значениями, скопированными из первой таблицы, чтобы получить один длинный дублированный список
• Сортировка дублированного списка с помощью встроенной функции сортировки Excel на ленте Home.
• Устранение дубликатов, выделив мышкой список и затем выбрав «Данные / Удалить дубликаты» с ленты

Затем вы можете приступить к настройке таблицы конечных результатов со списком уникальных значений элементов в качестве меток строк и меток столбцов, скопированных из двух таблиц (снова, как показано в примере с конечным результатом).

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

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

  =IFERROR(IF(VLOOKUP($A2,Sheet1!$A$2:$C$5,2,0)=0,"-",VLOOKUP($A2,Sheet1!$A$2:$C$5,2,0)),"-")

Синтаксис для поиска: VLOOKUP( lookup_value, table_array, column_index_num, [range_lookup]) . Последний необязательный параметр range_lookup указывает, будет ли совпадение для поиска приблизительным (по умолчанию) или точным.

Таким образом, VLOOKUP в формуле (оба VLOOKUP идентичны) ищут имя элемента в ячейке A2 в первом столбце диапазона A2:C5 и возвращают соответствующее значение во втором столбце диапазона, который является столбцом поставщика. Поскольку последний аргумент в VLOOKUP установлен в 0 (или FALSE), совпадение должно быть точным.

IFERROR, IF и двойное использование VLOOKUP в формуле необходимы для того, чтобы справиться с возможностью того, что нет совпадения с Item (что может произойти, если Item "name1" находится в таблице 2, но не в таблице 1) или что соответствует элементу, но ячейка для соответствующего значения пуста (в противном случае возвращается 0).

Формула поиска для второго столбца таблицы «Итоговый результат» отличается от первого только индексным номером столбца, для которого задано значение 3, чтобы получить контактную информацию:

Точно такой же подход используется для извлечения данных из второй входной таблицы, используя VLOOKUP, который ссылается на диапазон для этой таблицы и соответствующие столбцы данных, которые должны быть возвращены. Обратите внимание, что я установил формулы для возврата тире ("-"), если совпадение не найдено или если возвращаемое значение не доступно (т. Е. Если ячейка с возвращаемым значением пуста). Это так же легко можно изменить на пустую строку (""). Конечно, после того, как формулы для первой строки таблицы результатов настроены с правильными ссылками на диапазоны таблицы и возвращаемые столбцы, они будут скопированы в конец списка элементов.