Как объединить данные из разных таблиц в одну таблицу?
Приветствую.
В Excel есть два листа и на каждом из них таблица. У таблиц разный набор полей и есть некоторые данные, которые я хочу объединить в одну таблицу на отдельном листе. Вот пример того, что есть и как надо сделать:
Смотрел в сети разные способы, но либо у меня не получается, либо способы не те. Подскажите, пожалуйста, реализацию. За пример в файле буду признателен, но и объяснение подойдет.
- Вопрос задан более года назад
- 613 просмотров
2 комментария
Простой 2 комментария
Напишите макрос — минутное же дело.
weranda @weranda Автор вопроса
Akina, Если вы в них что-то понимаете, может быть, тогда оно и минутное.
Решения вопроса 0
Ответы на вопрос 3
SilentBird @SilentBird
Привет. Решал такие задачи по работе, используя Excel 2007. Файл с образцом решения — по ссылке ниже. Для наглядности сделал все на одном листе. Если таблицы на разных листах, принцип такой же. Вкратце:
1. Все таблицы нужно преобразовать в «умные таблицы» Excel. Так не придется следить за размерами этих таблиц.
2. Учитывая, что таблицы на разных листах, возникнет вопрос, все ли строки вошли в объединяющую таблицу. Чтобы знать это наверняка, я себе делаю «индикатор» возле объединяющей таблицы.
3. Заполнение данными происходит через контроль количества строк в исходных таблицах и номера строки в объединенной таблице. То есть «если номер текущей строки в объединенной таблице меньше или равен числу строк в первой таблице, то берем из первой таблицы, а иначе берем из второй таблицы».
4. Номер текущей строки — это формула СТРОКА() минус число строк до этой строки от верха листа. В образце это «СТРОКА()-1», потому что содержимое таблицы начинается со второй строки. За этим нужно следить и корректировать формулы в зависимости от положения таблицы на листе.
Собственно, файл-образец.
На всякий случай, то же самое в zip-архиве.
Надеюсь, помог. Удачи!
Ответ написан более года назад
Нравится 1 2 комментария
weranda @weranda Автор вопроса
За этим нужно следить и корректировать формулы в зависимости от положения таблицы на листе.
Смотрю я на все эти варианты и думаю — проще все ручками иногда скопировать/вставить)
SilentBird @SilentBird
weranda, это я просто понаписал много всяких слов) На самом деле все довольно просто. И исходные таблицы не всегда такие маленькие, как в примере) К тому же, обычно таблицы по листу не таскают, потому и формула один раз пишется, а затем только за размером объединенной таблицы следить и все.
Как объединить таблицы из Excel?
Нужно объединить таблицы по полю Имя сервера . Причем, строчки в этих полях идут вразнобой. Я смотрел библиотеку Pandas, но не могу понять, как сделать не построчное объеденение.
Отслеживать
51.6k 200 200 золотых знаков 61 61 серебряный знак 242 242 бронзовых знака
задан 8 июн 2021 в 6:54
23 2 2 бронзовых знака
2 ответа 2
Сортировка: Сброс на вариант по умолчанию
Воспользуйтесь методом pandas.concat(), чтобы объединить сразу все фреймы:
res = pd.concat( [pd.read_excel(f).set_index("Имя сервера") for f in list_of_excel_files], axis=1).reset_index() Отслеживать
ответ дан 8 июн 2021 в 7:27
MaxU — stand with Ukraine MaxU — stand with Ukraine
149k 12 12 золотых знаков 59 59 серебряных знаков 132 132 бронзовых знака
Мне так же подошел этот вариант, спасибо большое за помощь в решении вопроса!
10 июн 2021 в 7:03
Для начала вам нужно считать файлы excel в датафреймы:
import pandas as pd df1 = pd.read_xlsx("первый файл.xls") df2 = pd.read_xlsx("второй файл.xls") Затем просто сделать merge:
res = df1.merge(df2, on="Имя сервера", how="outer") на вашем примере, res будет
Имя сервера Стенд Используется (Да/Нет) ip CPU 0 dev1 Тест Да 10.10.1 8 1 prom2 Пром Да 10.1.2 4 2 data2 Тест Нет 10.10.2 24 3 data24 Пром Да 10.1.24 12
Сбор данных из разных файлов в одну таблицу Excel(VBA)
Все таблицы названы однотипно — 1_novgorod.xls, 2_dmitrov.xls, 3_shachty.xls и т.д. Во всех файлах внутри одинаковые таблицы, с двумя полями:
| Наименование | — | Количество |
| Продукт | — | 1500 |
| Продукт 2 | — | 1700 |
Каждый новый день, города шлют нам эти же файлы, с новыми данными, за текущие сутки. Может кто помочь, написать или ткнуть на похожий пример, где данные необходимо писать на отдельный лист в книгу за текущие сутки, а при появлении новых данных на след. день, то их копировать уже на новый лист с датой в названии листа (например так: 28.04.18 или просто 28.04).
На данный момент, мой код(солянка) позволяет выбрать файл для копирования из него данных, но они (данные) вставляются в прописанную вручную ячейку (например С3). Если например, повторно запустить макрос и взять уже другой файл, то данные перезапишутся в этой же ячейке (С3), хотя необходимо, чтобы они записались в ячейке, для соответствующего города. И так пока все города (их 15) не пришлют за текущий день данные.
Sub macr3() Dim sFolder As String, sFiles As String With Application.FileDialog(msoFileDialogFilePicker) If .Show = False Then Exit Sub sFiles = .SelectedItems(1) End With sFolder = sFolder & IIf(Right(sFolder, 1) = Application.PathSeparator, "", Application.PathSeparator) Application.ScreenUpdating = False sFolder = Dir(sFiles & "*.xls*") If sFiles <> "" Then Workbooks.Open sFiles & sFolder 'копируем нужный диапазон в откр. книге ActiveWorkbook.Sheets("Лист1").Range("B2:B16").Copy 'закрываем книгу которую открывали для копирования ActiveWorkbook.Close 'активируем нужную книгу Workbooks("main.xlsm").Activate 'выделяем и вставляем скопированные данные ActiveWorkbook.Worksheets("04_06").Range("C3").Select ActiveSheet.Paste End If Application.ScreenUpdating = True End Sub Буду рад, любой помощи.
Как свести таблицы excel из разных файлов в один
Типичная задача — имеем несколько однотипных таблиц на разных листах рабочей книги. Хотим, чтобы при внесении данных в любую из этих таблиц — данные добавлялись в одну общую таблицу, расположенную на отдельном листе.
Инструкция
Устанавливаем себе надстройку ЁXCEL . Читаем справку.
Приступаем к решению. Переходим во вкладку ЁXCEL главного меню, нажимаем кнопку «Таблицы», в выпавшем списке выбираем команду «Объединить таблицы»:
В открывшемся диалоговом окне выделяем листы с таблицами, которые необходимо объединить и нажимаем «ОК»:
Программа сформирует запрос — объединит таблицы и выведет информационное сообщение:
Переходим на итоговый лист (тот где хотим вывести общую таблицу), устанавливаем курсор в ячейку «A1«. Переходим в главном меню во вкладку «Данные» в разделе «Получение внешних данных» нажимаем кнопку «Существующие подключения»:
В открывшемся диалоговом окне выбираем «Подключения в этой книге» — «Запрос из Excel Files» и нажимаем «Открыть»:
В открывшемся диалоговом окне устанавливаем переключатели в положения «Таблица» и «Имеющийся лист», нажимаем «ОК»:
В активном листе будет создана таблица, которая будет объединять таблицы, расположенные на указанных нами листах:
Теперь все таблицы связаны. Добавьте новые строчки в любую из объединенных таблиц. Перейдите во вкладку «Данные» и нажмите кнопку «Обновить все»:
В итоговой таблице появятся строчки, добавленные в выбранный вами лист.
Чтобы обновление итоговой таблицы происходило автоматически вставьте в модуль каждого листа (кроме Итогового) следующий код (Как вставлять макросы?):
Private Sub Worksheet_Change(ByVal Target As Range) ActiveWorkbook.RefreshAll End Sub
Видео-пример
Важно:
- Количество столбцов во всех таблицах должно быть одинаково;
- Кроме таблиц на листах не должно быть никакой информации;
- Если вы переместили файл в другую папку или отправили файл коллеге по электронной почте — необходимо заново связать таблицы (в запросе прописывается абсолютный путь к файлу).
Чтобы запрос работал не зависимо от того в какой папке лежит файл вставьте в модуль «ЭтаКнига» следующий код:
Private Sub Workbook_Open() Dim q As String On Error Resume Next q = Application.ThisWorkbook.Path & "\" & Application.ThisWorkbook.Name 'Определяем текущий путь к файлу With ActiveWorkbook.Connections("Запрос из Excel Files").ODBCConnection 'Имя запроса .Connection = "ODBC;DSN=Excel Files;DBQ=" & q & _ ";DriverId=1046;MaxBufferSize=2048;PageTimeout=5;" 'Меняем строку подключения End With End Sub
Данный макрос при открытии книги будет определять текущий путь к файлу и менять путь к файлу в запросе.
| Файл | Описание | Размер файла: | Скачивания |
|---|---|---|---|
| Пример | 21 Кб | 2887 |
Чтобы оценить всю прелесть — выньте файл из архива и при загрузке файла включите макросы.
Возможные ошибки при использовании этого метода:
- В таблицах одинаковое количество столбцов, но при попытке использовать запрос получаем сообщение: «В таблицах или запросах, выбранных в запросе на объединение, не совпадает число столбцов.» Причина: в одном или нескольких листах с таблицами есть пустые столбцы (в них раньше были данные) и MS Excel считает их столбцами таблиц. Решение: Выделить и удалить все пустые столбцы на листах с таблицами.
- Все столбцы удалили, но ошибка осталась. Причина: файл сохранен на сетевом диске или открыт из почтовой программы. Решение: сохраните файл у себя на локальном компьютере, выполните запрос, вставьте в него макрос меняющий путь в запросе и выложите обратно в сеть (если нужно).
- В полученном запросе в некоторых ячейках пропадают данные. Причина: в ваших таблицах встречаются столбцы, которые одновременно содержатся и числовые и текстовые значения. MS Excel считает, что эти столбцы должны содержать только числа и не выводит текст. Решение:преобразуйте все числовые значения в этих столбцах во всех таблицах в текстовые. Обновите запрос — текстовые данные появятся.
Добавить комментарий
Комментарии
# Евгений 18.07.2023 10:13
Добрый день. помогите сформировать одну таблицу из множеста разных книг(таблицы одинаковые). В каждой книге несколько вкладок с одинаковыми названиями.
# Иркэ 24.06.2023 20:49
Доброго времени. Есть две таблицы с данными-1: ФИО, место госпитализации, 2: ФИО ,группа инвалидности. Не все ФИО в 1 и 2 таблицах не однозначны. Как объединить таблицы так,чтобы они объединились по ФИО, место госпитализации, группа инвалидности?
Благодарю
# Виктория Р. 20.04.2023 18:26
Здравствуйте, есть одна общая таблица на весь коллектив, когда один из работников вносит в нее данные другой работник не может вывести в неё данные. Как можно решить эту проблему?