Объединение запросов и объединение таблиц
В настоящее время данные обобщаются только на уровне продукта. В таблице «Категория» можно скатить продукты на уровне. таким образом, вы можете загрузить таблицу «Категория» и создать для нее соединить поля «Название товара».
- Выберите таблицу «Категории», а затем выберите «Данные»>«&» > «Из таблицы» или «Диапазон».
- Выберите «Закрыть& Загрузить таблицу, чтобы вернуться на лист, а затем переименуем ярлыж листа в «Категории PQ».
- Выберите таблицу «Данные о продажах», откройте Power Query, а затем на домашней>в>объединить запросы >слияние как новые.
- В диалоговом окне «Слияние» под таблицей «Продажи» выберите в списке столбец «Название товара».
- В столбце «Название товара» выберите таблицу «Категория» из списка.
- Чтобы завершить операцию, выберите «ОК».
Как объединить таблицы в excel
Argument ‘Topic id’ is null or empty
Сейчас на форуме
© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru
Использование любых материалов сайта допускается строго с указанием прямой ссылки на источник, упоминанием названия сайта, имени автора и неизменности исходного текста и иллюстраций.
| ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949 |
ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071 |
Как объединить таблицы в 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 Кб | 2916 |
Чтобы оценить всю прелесть — выньте файл из архива и при загрузке файла включите макросы.
Возможные ошибки при использовании этого метода:
- В таблицах одинаковое количество столбцов, но при попытке использовать запрос получаем сообщение: «В таблицах или запросах, выбранных в запросе на объединение, не совпадает число столбцов.» Причина: в одном или нескольких листах с таблицами есть пустые столбцы (в них раньше были данные) и MS Excel считает их столбцами таблиц. Решение: Выделить и удалить все пустые столбцы на листах с таблицами.
- Все столбцы удалили, но ошибка осталась. Причина: файл сохранен на сетевом диске или открыт из почтовой программы. Решение: сохраните файл у себя на локальном компьютере, выполните запрос, вставьте в него макрос меняющий путь в запросе и выложите обратно в сеть (если нужно).
- В полученном запросе в некоторых ячейках пропадают данные. Причина: в ваших таблицах встречаются столбцы, которые одновременно содержатся и числовые и текстовые значения. MS Excel считает, что эти столбцы должны содержать только числа и не выводит текст. Решение:преобразуйте все числовые значения в этих столбцах во всех таблицах в текстовые. Обновите запрос — текстовые данные появятся.
Добавить комментарий
Комментарии
# Евгений 18.07.2023 10:13
Добрый день. помогите сформировать одну таблицу из множеста разных книг(таблицы одинаковые). В каждой книге несколько вкладок с одинаковыми названиями.
# Иркэ 24.06.2023 20:49
Доброго времени. Есть две таблицы с данными-1: ФИО, место госпитализации, 2: ФИО ,группа инвалидности. Не все ФИО в 1 и 2 таблицах не однозначны. Как объединить таблицы так,чтобы они объединились по ФИО, место госпитализации, группа инвалидности?
Благодарю
# Виктория Р. 20.04.2023 18:26
Здравствуйте, есть одна общая таблица на весь коллектив, когда один из работников вносит в нее данные другой работник не может вывести в неё данные. Как можно решить эту проблему?
Как объединить данные из разных таблиц в одну таблицу?
Приветствую.
В Excel есть два листа и на каждом из них таблица. У таблиц разный набор полей и есть некоторые данные, которые я хочу объединить в одну таблицу на отдельном листе. Вот пример того, что есть и как надо сделать:
Смотрел в сети разные способы, но либо у меня не получается, либо способы не те. Подскажите, пожалуйста, реализацию. За пример в файле буду признателен, но и объяснение подойдет.
- Вопрос задан более года назад
- 777 просмотров
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, это я просто понаписал много всяких слов) На самом деле все довольно просто. И исходные таблицы не всегда такие маленькие, как в примере) К тому же, обычно таблицы по листу не таскают, потому и формула один раз пишется, а затем только за размером объединенной таблицы следить и все.