Как объединить две таблицы в Excel с одинаковыми столбцами?

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

Насколько я хотел бы, я не могу просто вручную вставить эти дополнительные строки в таблицу, потому что они будут удаляться всякий раз, когда Excel извлекает новые данные из базы данных SQL. Поэтому вместо этого я рассматриваю возможность создания отдельной таблицы с теми же заголовками столбцов на новом листе и ввода данных там, а затем создания третьей таблицы на другом листе, которая каким-то образом объединяет строки из таблицы, которая извлекает данные из SQL и Где я вводим данные вручную. Как я могу это сделать? (Или поочередно, есть ли лучший способ сделать это, которого я как-то не хватает?)


Пример:

Table 1 (From Database): | Person | Week Of | Task | Hours | | Bob | 1/6/13 | Foo | 12 | | Mary | 1/6/13 | Foo | 7 | | Mary | 1/6/13 | Bar | 5 | | John | 1/6/13 | Foo | 5 | | John | 1/13/13 | Foo | 13 | 

 Table 2 (Entered Manually): | Person | Week Of | Task | Hours | | Bob | 1/6/13 | Baz | 3 | | Mary | 1/6/13 | Baz | 2 | | John | 1/13/13 | Baz | 5 | 

 Result: | Person | Week Of | Task | Hours | | Bob | 1/6/13 | Foo | 12 | | Mary | 1/6/13 | Foo | 7 | | Mary | 1/6/13 | Bar | 5 | | John | 1/6/13 | Foo | 5 | | John | 1/13/13 | Foo | 13 | | Bob | 1/6/13 | Baz | 3 | | Mary | 1/6/13 | Baz | 2 | | John | 1/13/13 | Baz | 5 | 

Вот чистое решение Excel без VBA. Он работает с помощью функции INDEX для спуска строк и столбцов данных SQL до тех пор, пока значения не будут исчерпаны и не возникнет условие ошибки. Функция IFERROR улавливает ошибку и использует вторую функцию INDEX для того, чтобы сбрасывать строки и столбцы введенных вручную данных снова до тех пор, пока эти значения не будут исчерпаны и не возникнет условие ошибки. Вторая функция IFERROR ловит ошибку и возвращает тире («-»). (Данные SQL должны быть обновлены через ленту, чтобы формулы получили правильный результат.)

Создайте динамический именованный диапазон SQLDB для данных SQL в Sheet1, используя формулу:

 =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,COUNTA(Sheet1!$1:$1)) 

Создайте второй динамический именованный диапазон EXCELRNG для введенных вручную данных в Sheet2, используя формулу:

 =OFFSET(Sheet2!$A$1,1,0,COUNTA(Sheet2!$A:$A)-1,COUNTA(Sheet2!$1:$1)) 

Оба этих названных диапазона предполагают, что имена переменных вводятся в строке 1 каждого из двух листов.

Введите имена переменных в строке 1 Sheet3 (начиная с ячейки A1).

Введите следующую формулу: В ячейке A2 листа 3:

 =IFERROR(INDEX(SQLDB,ROWS(A$2:A2),COLUMN(A2)),IFERROR(INDEX(EXCELRNG,ROWS(A$2:A2)-ROWS(SQLDB),COLUMN(A2)),"-")) 

Скопируйте формулу по столбцам имен переменных, а затем вниз по строкам, пока результаты формул не станут тире («-»).

В качестве следующего шага можно создать сводную таблицу на другом листе для анализа и организации.

Снова первым шагом было бы создать динамический именованный диапазон, скажем, RESULTRNG, вставив следующую формулу в поле ввода Name Manager для именованного диапазона:

 =OFFSET(Sheet3!$A$1,0,0,COUNTA(Sheet1!$A:$A)+COUNTA(Sheet2!$A:$A)-1,COUNTA(Sheet1!$1:$1)) 

Затем создайте сводную таблицу на новом листе, установив RESULTRNG в качестве таблицы, которую вы хотите проанализировать. Это отфильтрует любые трейлинг-черточки из таблицы формул в Sheet3.

Это работает, потому что формула RESULTRNG подсчитывает общее количество строк в Sheet1 и Sheet2 (исключая заголовок в Sheet2) и общее количество столбцов в Sheet1 и устанавливает его степень на основе этих подсчетов, исключая любые тире в любых завершающих строках ( Или столбцы) в таблице формул Sheet3.

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

Перейдите на вкладку данных на ленте, нажмите «От других источников» и «От Microsoft Query». Затем нажмите «Файлы Excel», выберите файл, в котором вы сейчас работаете, и нажмите «ОК». Затем нажмите «Отмена» и, если вы хотите продолжить редактирование в Microsoft Query, нажмите «Да». Отсюда вы можете нажать кнопку SQL и написать собственный SQL-запрос на любом листе в электронной таблице. В моем случае:

 SELECT * FROM `'Sheet1$'` `'Sheet1$'` UNION ALL SELECT * FROM `'Sheet2$'` `'Sheet2$'` 

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

Если вас интересует решение VBA, я смог заставить работать:

  • Установите динамический именованный диапазон для данных, которые вы извлекаете из SQL Server. Откройте диспетчер имен, введите новое имя (например, «SQLDB») и скопируйте следующую формулу в поле «Относится к». Я предположил, что ваши данные затянуты в Sheet1:

     =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1)) 
  • Установите другой именованный диапазон для диапазона ввода ручных данных. Я использовал имя EXCELRNG и предположил, что это было в Sheet2. Именованный диапазон начинается в строке 2, чтобы исключить строку заголовка. Формула здесь идентична первой, за исключением листа, на который она ссылается:

     =OFFSET(Sheet2!$A$1,1,0,COUNTA(Sheet2!$A:$A)-1,COUNTA(Sheet2!$1:$1)) 
  • Вот первый набор настроек, которые я использовал для подключения к таблице SQL. Доступ к диалоговому окну можно получить, выбрав «Подключения» на вкладке «Данные» на ленте. Отключение обновления фона гарантирует, что макрос VBA приостанавливается до тех пор, пока обновление данных на листе Excel не будет завершено. Обновление соединения при открытии листа может не понадобиться, но я хотел убедиться, что любая проверка подлинности будет выполнена до запуска макроса.

Настройки соединения

  • Вот второй набор настроек. Они находятся в разделе «Свойства» вкладки «Данные» (если выбрана ячейка в импортированной таблице SQL). Хотя я выбрал вариант «Вставить целые строки для новых данных, удалить неиспользуемые ячейки», на самом деле я не столкнулся с проблемой «Вставить ячейки …».

Свойства соединения

  • Наконец, это код VBA. Чтобы вставить его, выберите «Visual Basic» на вкладке «Разработчик». Выделите имя листа в списке слева. Он будет называться «VBA Project (имя листа). Затем выберите« Вставить модуль »в строке меню в верхней части экрана и вставьте код в новый модуль. Обратите внимание, что я поместил консолидированную таблицу в Sheet 3. Как написано, Макрос не сортирует новую таблицу, хотя это не сложно будет добавить.

     Sub StackTables() Dim Rng1 As Range, Rng2 As Range Set Rng1 = ThisWorkbook.Names("SQLDB").RefersToRange Set Rng2 = ThisWorkbook.Names("EXCELRNG").RefersToRange ' refresh the SQL table ThisWorkbook.Connections(1).Refresh ' clear the consolidated table range Sheet3.Cells.ClearContents ' copy the SQL data into the consolidation range Rng1.Copy Sheet3.Range("A1").PasteSpecial xlPasteValues 'copy the manually entered data into the consolidate range Rng2.Copy Sheet3.Range("A1").Offset(Rng1.Rows.Count, 0).PasteSpecial xlPasteValues Application.CutCopyMode = False Sheets("Sheet3").Activate ActiveSheet.Range("A1").Select End Sub 

Вот версия решения «Pure Excel» от @ chuff, разработанная специально для работы со столами. (IE. Два источника данных, которые вы хотите объединить, – это таблицы.)

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

Теперь создайте новую таблицу в верхнем левом углу нового листа и дайте ей те же имена столбцов, что и две другие таблицы. Затем введите следующую формулу в ячейке A2 только что созданного листа:

 =IFERROR(INDEX(Table1,ROWS(A$2:A2),COLUMN(A2)), IFERROR(INDEX(Table2,ROWS(A$2:A2)-ROWS(Table1),COLUMN(A2)), "-")) 

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

Если столбцы в объединенной таблице отображают 0, когда они должны отображать пустую ячейку, вы можете обернуть формулу в этом столбце с помощью функции-заменителя, например:

 =SUBSTITUTE(<old expression here>, 0, "") 

Если вы хотите создать сводную таблицу, которая использует данные из этой новой таблицы, вам придется создать именованный диапазон. Сначала назовите таблицу Table3 . Перейдите на вкладку «Формулы» и нажмите «Определить имя». Дайте ссылку имя, введите следующее уравнение для своего значения («Относится к»):

 =OFFSET(Table3[#All],0,0,ROWS(Table1)+ROWS(Table2)+1) 

Затем вы можете использовать эту именованную ссылку в качестве диапазона для вашей сводной таблицы.

Если вы хотите получить результат только один раз, есть веб-сайт, который объединит для вас две таблицы: https://office-tools.online/table/merge/

Вы вставляете таблицы в веб-страницу и выбираете соответствующие параметры. Вот скриншот встроенного примера, который показывает, как его использовать:

Введите описание изображения здесь

  • Excel Sum, исключающий дубликаты
  • Excel: переместить содержимое ячейки в комментарии
  • Excel: форматирование пользовательских номеров
  • Есть ли способ разместить две страницы на одной странице в MS Excel при печати?
  • Конкатенация диапазона со значениями между
  • Как заставить многие формулы столбцов игнорировать те же строки в Excel?
  • Функция «Удалить дубликаты» не удаляет все дубликаты
  • Как найти и заменить пути ссылок в нескольких файлах Excel одновременно
  • Как включить цитаты в операцию CONCATENATE?
  • Скопировать / заполнить клавиатуру Excel для всех ячеек с непустыми смежными ячейками?
  • Как объединить данные из нескольких файлов Excel в один файл Excel или базу данных Access?
  • Interesting Posts
    Давайте будем гением компьютера.