Как связать два листа в таблице excel

После фильтрации «мусора» все добавленные вспомогательные столбцы можно, конечно же, спокойно удалить, оставив только колонки Name и Data.

Шаг 3. Разворачиваем таблицы

Теперь развернём содержимое таблиц в одно целое, используя кнопку с двойными стрелками в заголовке столбца Data, отключив флажок Использовать исходное имя столбца как префикс (Use original column name as prefix) :

После нажатия на ОК Power Query соберёт для нас все данные в одну мегатаблицу со всех отобранных листов нашего файла:

Останется лишь «навести блеск», а именно:

1. Поднять первую строку в шапку таблицы кнопкой Использовать первую строку в качестве заголовков (Use first row as headers) на вкладке Главная (Home) .
2. Переименовать первый столбец в Город двойным щелчком на заголовку.
3. Удалить повторяющиеся шапки таблиц, попавшие в одну кучу вместе с данными, используя фильтр по столбцу Товар.

Всё. Осталось только дать нашему запросу подходящее имя (например, Сборка) в панели справа и выгрузить затем собранные данные обратно в Excel кнопкой Закрыть и загрузить на вкладке Главная (Home — Close & Load) :

В будущем, при любых изменениях в исходном файле достаточно будет лишь обновить наш запрос, щелкнув по собранной таблице правой кнопкой мыши и выбрав команду Обновить (Refresh) или такой же кнопкой на вкладке Данные (Data) или сочетанием клавиш Ctrl + Alt + F5 .

Плюсы такого подхода:

• Не нужно уметь программировать, всё делается быстро и почти без использования клавиатуры.
• Последовательность столбцов на разных листах может быть различной — это не играет роли, столбцы правильно встанут друг под друга в итоговой сборке.
• Можно быстро обновлять запрос при изменении исходных данных.

Минусы этого способа:

• Собираются только значения, т.е. формулы с исходных листов не сохраняются.
• Названия столбцов должны на всех листах совпадать с точностью до регистра.
• Нельзя выбрать какой именно диапазон берётся с каждого листа — это определяется автоматически (берётся всё, что есть).
• Для обновления нужен Excel 2016 или новее или установленная надстройка Power Query.

Способ 2. Сборка данных с листов макросом на VBA

Похожего результата можно добиться и с помощью более «классического» подохода — макросом на VBA. Для этого на вкладке Разработчик (Developer) нажмите кнпоку Visual Basic или воспользуйтесь сочетанием клавиш Alt + F11 . В открывшемся окне добавьте новый модуль через меню Insert — Module и скопируйте туда текст вот такого макроса:

Sub CollectDataFromAllSheets() Dim ws As Worksheet Set wbCurrent = ActiveWorkbook Workbooks.Add Set wbReport = ActiveWorkbook 'копируем на итоговый лист шапку таблицы из первого листа wbCurrent.Worksheets(1).Range("A1:D1").Copy Destination:=wbReport.Worksheets(1).Range("A1") 'проходим в цикле по всем листам исходного файла For Each ws In wbCurrent.Worksheets 'определяем номер последней строки на текущем листе и на листе сборки n = wbReport.Worksheets(1).Range("A1").CurrentRegion.Rows.Count 'задаем исходный диапазон, который надо скопировать с каждого листа - любой вариант на выбор: Set rngData = ws.Range("A1:D5") 'фиксированный диапазон или Set rngData = ws.UsedRange 'всё, что есть на листе или Set rngData = ws.Range("F5").CurrentRegion 'область, начиная от ячейки F5 или Set rngData = ws.Range("A2", ws.Range("A2").SpecialCells(xlCellTypeLastCell)) 'от А2 и до конца листа 'копируем исходный диапазон и вставляем в итоговую книгу со следующей строки rngData.Copy Destination:=wbReport.Worksheets(1).Cells(n + 1, 1) Next ws End Sub

Запустить созданный макрос можно на вкладке Разработчик кнопкой Макросы (Developer — Macros ) или с помощью сочетания клавиш Alt + F8 . Макрос автоматически создаст новую книгу и скопирует туда нужные вам данные.

Плюсы такого подхода:

• Формулы с исходных листов сохраняются в сборке.
• Имена столбцов не играют роли.
• Макрос будет работать в любой версии Excel.
• Можно выбирать, что именно брать с каждого листа (конкретный фиксированный диапазон или весь лист).

Минусы этого способа:

• Последовательность столбцов на всех листах должна быть одинаковой, т.к. происходит, по сути, тупое копирование таблиц друг-под-друга.
• Защита от макросов должна быть отключена.
• Быстрого обновления, как это было с Power Query, здесь, к сожалению, не будет. При изменении исходных данных придётся запустить макрос повторно.

Способ 3. Готовый макрос из надстройки PLEX

Если лень возиться с макросами или Power Query, то можно пойти по пути наименьшего сопротивления — использовать готовый макрос (кнопка Собрать) из моей надстройки PLEX для Excel. Это, может, и не спортивно, но зато эффективно:

В общем, выбирайте любой удобный вам вариант и действуйте. Выбор — это всегда хорошо.

Ссылки по теме

• Что такое Power Query и с чем его едят.
• Как создавать свои макросы, использовать чужие и запускать их в своих файлах.
• Сборка данных из нескольких файлов с помощью Power Query.

25.03.2019 13:08:53
Шаг 1. Можно не выбирать лист и потом удалять шаги, а сразу файл выбирать
07.04.2019 08:04:07
Согласен. Добавил в видео этот момент. Спасибо!

07.04.2019 14:15:19
Привет!
Строки:

Set rngData = ws.Range("A1:D5") 'фиксированный диапазон или Set rngData = ws.UsedRange 'всё, что есть на листе или Set rngData = ws.Range("F5").CurrentRegion 'область, начиная от ячейки F5 или

забыли удалить?
08.04.2019 09:44:41

Николай, подскажите, пожалуйста, как бороться с ошибкой возникающей при переименовании первого листа (либо ставкой листа в начало книги).
Ошибка возникает при выполнении строчки:
#»Переименованные столбцы» = Table.RenameColumns(#»Измененный тип»,>),

Такая ошибка возникает, в том числе, при работе с файлами из папки.

P.S.
Когда мы увидим Вашу книгу по PQ? Очень ждём!

09.04.2019 23:10:49

Сергей, а как у вас в заголовке оказался «Абакан»? Если вы делали все как написал Николай, такого бы не было. Проверьте по шагам. Ну и в большинстве случаев проблемы доставляют автоматически создающиеся шаги #»Измененный тип», как правило их можно удалять, оставляя приведение типов на последний шаг.

10.04.2019 09:30:22

Степан, «Абакан» оказался в заголовке после первого шага в «наведении блеска» — повышения заголовков. На экранной форме Николая видно, что «Абакан» находится на одной строке с другими заголовками. Следующий шаг как раз и приводит к ошибке в случае, если появился новый лист в начале книги.

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

08.04.2019 10:04:43
Удалил. Разобрался

11.04.2019 17:48:03

Простите, а «технически с помощью условных столбцов» это как? я, обычно, из папки делаю через функцию и файл примера

12.04.2019 14:41:05

В случае обработки файлов из папки, если данные берутся просто из диапазона, повышение столбцов приводит к внесению наименования первого файла в заголовок. Если в будущем в этой папке появляется файл с названием «выше» чем старые файлы, то записанный ранее шаг с переименованием приводит к ошибке. Условными столбцами эту проблему обойти можно. Хотелось бы услышать, как Николай с этим борется.

15.04.2019 17:30:20

Простите, проблема мне понятна, сам наталкивался не раз, меня интересовало как она решается «технически с помощью условных столбцов»

16.04.2019 16:12:36

Алексей, на самом деле идея очень простая, но почти всегда требуются «особые» настройки, которые необходимо внести до повышения первой строки.
В случае файла Николая условный столбец может быть такой:
#»step_N» = Table.AddColumn(#»step_N-1″, «NEW_COLUMN», each if [Column1] = «Товар» then «SheetName» else [Name],
Т.е. если в столбце «Column1» содержится слово «Товар», то в новый столбец вносим «SheetName», в противном случае наименование листа из столбца «Name».
Теперь необходимо удалить первый столбец с наименованиями листов, применить фильтр по слову не «Товар» и повысить первую строку.
Готово!

24.05.2019 18:18:52

Добрый день!
У меня в таком варианте PQ не корректно обрабатывает в случаях если порядок столбцов отличается, а названия столбцов полностью совпадают. Т.е. в итоге данные копируются именно в порядке столбцов на исходных листах.
Подскажите, пожалуйста, что я могу делать не так?

08.06.2019 11:12:50
Здравствуйте. А если книга распологается в облаке (onedrive)? Как сделать подобное?

22.08.2019 21:08:36
для подобной сборки файл с листами может иметь общий доступ ?

10.09.2019 17:52:07

Добрый вечер.
Скажите пожалуйста, а можно ли в этом приеме распространить шаги обработки ранее загруженных листов (например, отмена свертывания) на листы, добавляемые позднее? Если да, можете ли подсказать, как? Спасибо.

11.10.2019 17:55:03

Здраствуте.
Подскажите как с помощью Power Query загрузить (сделать подключение ) к листу на этой же книге ?
Если я делаю «Получить данные- Из файла- Из книги Excel, то я посылаюсь на эту же книгу, но пишет ее адрес (как при внешнем источнике) : C:\Users\okurinna\Desktop\Flash report (short version) v3.xlsx а не лист Actual

Ниже приведено 2 запроса, которые я потом объеденяю в один целый:

Запрос 1:
let
Источник = Excel.Workbook(File.Contents(«C:\Users\okurinna\Desktop\Flash report (short version) v3.xlsx» ), null, true),
Actual_Sheet = Источник[Data],
#»Удаленные верхние строки» = Table.Skip(Actual_Sheet,4),
#»Удаленные столбцы» = Table.RemoveColumns(#»Удаленные верхние строки»,),
#»Повышенные заголовки» = Table.PromoteHeaders(#»Удаленные столбцы», [PromoteAllScalars=true ] )
in
#»Повышенные заголовки»

let
Источник = Excel.Workbook(File.Contents(«C:\Users\okurinna\Desktop\Flash report (short version) v3.xlsx» ) , null, true),
Actual_Sheet = Источник[Data],
#»Удаленные верхние строки» = Table.Skip(Actual_Sheet,4),
#»Повышенные заголовки» = Table.PromoteHeaders(#»Удаленные верхние строки», [PromoteAllScalars=true ] ),
#»Удаленные столбцы» = Table.RemoveColumns(#»Повышенные заголовки»,),
#»Переименованные столбцы» = Table.RenameColumns(#»Удаленные столбцы»,>),
#»Переупорядоченные столбцы» = Table.ReorderColumns(#»Переименованные столбцы»,),
#»Переименованные столбцы1″ = Table.RenameColumns(#»Переупорядоченные столбцы»,>),
#»Строки с примененным фильтром» = Table.SelectRows(#»Переименованные столбцы1″, each ([Sum in USD] <> null)),
#»Удаленные столбцы1″ = Table.RemoveColumns(#»Строки с примененным фильтром»,),
#»Добавлен пользовательский объект» = Table.AddColumn(#»Удаленные столбцы1″, «Article», each Text.From([#»Cost/Revenue»]&» adjustments» ] ),
#»Переупорядоченные столбцы1″ = Table.ReorderColumns(#»Добавлен пользовательский объект»,),
#»Измененный тип» = Table.TransformColumnTypes(#»Переупорядоченные столбцы1″,>)
in
#»Измененный тип»

У меня несколько таблиц на одной и той же книге, но они в разном формате. Мне нужно в Power Query потом писать разные запросы чтобы их потом привести к общей форме и соединить.
Мне крайне важно сделать этот файл автономным и не зависищем от внешних источников.
Как посоветуете ?

Как связать листы в Excel

В создании этой статьи участвовала наша опытная команда редакторов и исследователей, которые проверили ее на точность и полноту.

Команда контент-менеджеров wikiHow тщательно следит за работой редакторов, чтобы гарантировать соответствие каждой статьи нашим высоким стандартам качества.

Количество просмотров этой статьи: 45 350.

В этой статье:

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

Откройте файл Microsoft Excel. Нажмите на значок в виде зелено-белой буквы «X».

Перейдите на целевой лист. Список листов отображается внизу таблицы. Щелкните по листу, который будет связан с другим листом.

Нажмите на пустую ячейку на целевом листе. Это будет целевая ячейка. Когда вы свяжете ее с исходной ячейкой (ячейкой на другом листе), данные в целевой ячейке будут автоматически меняться с изменением данных в исходной ячейке.

Введите = в целевой ячейке. Этот символ свидетельствует о начале ввода формулы.

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

• Также эту формулу можно ввести вручную. Она должна выглядеть так: =! , где вместо подставьте имя исходного листа.

• Например, если вы извлекаете данные из ячейки D12 на листе Лист1, формула будет выглядеть так: =Лист1!D12 .

Нажмите ↵ Enter на клавиатуре. Формула будет активирована, а вы перейдете на целевой лист. Теперь целевая ячейка связана с исходной ячейкой и автоматически извлекает из нее данные. Каждый раз, когда меняется значение в исходной ячейке, значение в целевой ячейке будет обновлено.

Щелкните по целевой ячейке, чтобы выделить ее.

• Перетащить указанный значок и расширить диапазон связанных ячеек можно в любом направлении. Таким образом, диапазон связанных ячеек может включать все ячейки на листе или только какую-то часть ячеек.

Как объединить 2 таблицы в Excel: инструкция для начинающих

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

Зачем таблицы Excel для бизнеса

Excel — это мощный инструмент для работы с данными, который позволяет создавать и обрабатывать большие объемы информации.

Excel может быть использован для управления любым предприятием для сбора или анализа важной информации и создания отчетности.

Основные возможности и функции таблиц:

1. Создание таблицы инвентаризации: Excel позволяет создавать таблицы для отслеживания запасов товаров на предприятии. Вы можете добавлять новые товары, указывать количество, стоимость и дату приобретения, а также отслеживать, когда продукция была продана или использована. Кроме того, есть возможности интеграции таблиц и выгрузки данных из товароучетных программ, например, интеграция с 1С . 2. Расчет стоимости запасов: С помощью функций Excel можно автоматически рассчитывать стоимость запасов на производстве. Можно использовать специальные формулы для умножения количества товаров на их стоимость, а также для суммирования общей стоимости и другой аналитики количества всех товаров. 3. Анализ продаж: Excel может быть использован для анализа продаж и определения наиболее популярных товаров. Вы можете создавать графики и диаграммы, чтобы визуализировать данные о продажах, а также использовать функции фильтрации и сортировки для анализа данных по различным параметрам (товары, месяц, количество и прочее). 4. Прогнозирование спроса: С помощью функций Excel можно прогнозировать спрос на товары на основе прошлых продаж и других факторов, таких как сезонность или маркетинговые акции. Это позволяет оптимизировать запасы и избежать излишков или нехватки товаров. 5. Управление заказами: Excel может быть использован для управления заказами и отслеживания поставок товаров. Вы можете создавать таблицы для отслеживания статуса заказов, даты поставки и других важных параметров. 6. Создание отчетов: Excel позволяет создавать различные отчеты о состоянии склада, продажах, заказах и других аспектах управления складом. Вы можете использовать функции форматирования и группировки данных для создания профессионально выглядящих отчетов.

Объединение таблиц

Объединение таблиц позволяет соединить данные из разных источников в одну таблицу, чтобы упростить анализ и обработку данных. Такое действие полезно для многих пользователей, особенно для тех, кто работает с большими объемами данных (бухгалтеров, финансистов, менеджеров проектов). Использование такого инструмента доступно даже рядовому сотруднику и позволяет принимать решение на основе данных и фактов. Особенно актуально для крупной компании или производства, которое еще только планирует автоматизацию процессов. Преимущества объединения таблиц:

• упрощает процесс работы с данными;
• позволяет объединить данные из разных источников в одну таблицу;
• сокращение времени на поиск и обработку данных;
• упрощает анализ данных.

5 шагов к объединению таблиц в Excel

Перед тем, как объединять таблицы в Excel, необходимо убедиться, что данные в таблицах имеют одинаковую структуру и формат. Также важно проверить, что столбцы, которые вы хотите объединить, содержат одинаковые данные. Если это не так, то перед объединением необходимо привести данные к одному формату.

Как это сделать?

1. Откройте Excel и выберите лист, на котором вы хотите объединить таблицы. Выберите ячейку, в которой вы хотите начать объединение таблиц.

2. Нажимаем на вкладку «Данные» в верхней части экрана, а потом на кнопку «Объединение».

3. Выбираем функцию «Объединить таблицы».

4. Выберите таблицы, которые вы хотите объединить. Важно, чтобы данные были в одном формате и правильно их импортировать на новый или существующий лист.

5. Введите имя новой таблицы и сохраните ее. Ваша новая таблица должна появиться на листе Excel.

Объединение таблиц в Excel — это полезная функция, которая может упростить работу с данными и ускорить анализ информации. При объединении таблиц важно убедиться, что данные имеют одинаковый формат и структуру. Кроме того, есть и другие способы анализа и объединения таблиц — консолидацией или формулами. Следующий уровень анализа — системы бизнес-аналитики (например, BI-системы) . Они более автоматизированы: данные автоматически собираются в одном месте, сведение данных в таблицы происходит за пару минут и визуальное оформление дашбордом сразу показывает результаты деятельности для руководителя в режиме онлайн.

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