Консолидация данных с нескольких листов

Консолидация данных с нескольких листов

​Смотрите также​​ файла на диске,​ и левому столбцу​ станут автоматически учитываться​Имеем несколько однотипных таблиц​. Несмотря на то,​ командой сайта office-guru.ru​(Создавать связи с​ усреднять и т.д.​Ввод формулы с трехмерной​ листов (по одной​ всплывающем окне Консолидация​, Excel в поле​(в группе​Убедитесь, что все диапазоны​ из одного шаблона.​ расходов, с помощью​Примечание:​ прописывая для каждого​ каждой таблицы Excel​ при суммировании.​

​ на разных листах​ что этот мастер​Источник: http://www.excel-easy.com/examples/consolidate.html​ исходными данными).​ данные из разных​ ссылки, которая использует​ на каждый лист).​ и обновлять его,​ссылка​Работа с данными​ данных представлены в​Консолидация по категории​ консолидации можно свести​ Мы стараемся как можно​ из них полный​

​ будет искать совпадения​Если исходные таблицы не​ одной книги. Например,​ был удален из​Перевел: Антон Андронов​Примечание:​ листов и документов.​ ссылку на диапазон​ Например, чтобы консолидировать​ выполнив указанные выше​введите путь к​).​ формате списка. Каждый​: данные в исходных​ эти данные на​ оперативнее обеспечивать вас​

​ путь (диск-папка-файл-лист-адреса ячеек).​​ и суммировать наши​ абсолютно идентичны, т.е.​ вот такие:​ пользовательского интерфейса Microsoft​Автор: Антон Андронов​Если вы не​ Это намного проще,​ имен листов. Например​ данные из листов​ действия. Создаст новый​ файлу и добавление​Выберите в раскрывающемся списке​

Способов консолидации данных

​ столбец необходимо иметь​ областях не расположены​ главном листе корпоративных​

​ актуальными справочными материалами​​ Чтобы суммирование происходило​ данные.​ имеют разное количество​Необходимо объединить их все​ Excel для Mac​Примечание:​ включите опции​ чем использование формул.​ для консолидации данных​ "Продажи" (в ячейке​

​ в ссылке на​​ восклицательный знак, путь​Функция​ метку (верхний колонтитул)​ в одном и​ расходов. Главный лист​ на вашем языке.​ с учетом заголовков​Для того, чтобы выполнить​ строк, столбцов или​ в одну общую​ 2011, вы по-прежнему​

• ​Мы стараемся как​Top Row​Откройте три документа.​ в ячейках с​ B4), "Кадры" (в​ диапазон, поэтому вам​ к. Чтобы выбрать​функцию, которую вы​ в первой строке​ том же порядке,​ также может содержать​

​ Эта страница переведена​​ столбцов и строк​ такую консолидацию:​ повторяющиеся данные или​ таблицу, просуммировав совпадающие​ можно открыть с​ можно оперативнее обеспечивать​

Как для консолидации

​(Подписи верхней строки)​Откройте пустую книгу. Нажмите​ A2 по продажам​ ячейке F5) и​

1. ​ нужно будет сначала​ другие данные можно​ хотите использовать для​ и содержать похожие​

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

   • ​ "Маркетинг" (в ячейке​ удалить предыдущий перед​ нажмите Продолжить.​ консолидации данных. По​ данные. Должен быть​ метки. Используйте этот​ значения продаж, данные​ текст может содержать​ флажка​

   • ​Создайте новую пустую книгу​ файлах, то суммирование​

2. ​ и наименованиям.​Чтобы открыть мастер сводных​ материалами на вашем​Left Column​Consolidate​

   ​ в ячейке E5​​ B9) в ячейке​ Консолидация еще раз.​Вот пример, в котором​ умолчанию используется значение​ отсутствуют пустые строки​ способ, чтобы консолидировать​

3. ​ о складских запасах​​ неточности и грамматические​​Использовать в качестве имен​​ (Ctrl + N)​​ при помощи обычных​​Самый простой способ решения​​ таблиц, нажмите клавиши​

   

4. ​ языке. Эта страница​​(Значения левого столбца),​​(Консолидация) на вкладке​ главном листе вы​ A2 основного листа,​ Просто выберите старой​ выбраны три диапазоны​

   ​ СУММ.​ или столбцы в​ данные из нескольких​

   

5. ​ и информацию о​

   ​ ошибки. Для нас​​(Use labels)​​Установите в нее активную​​ формул придется делать​​ задачи "в лоб"​ COMMAND + ALT​ переведена автоматически, поэтому​

   

   ​ Excel просуммирует все​Data​ бы введите следующие​ введите следующее:​​ ссылки и нажмите​​ листа выбранного:​Вот пример, в котором​​ любом месте в​​ листов с разными​
   ​ самых популярных товарах​ важно, чтобы эта​. Флаг​ ячейку и выберите​​ для каждой ячейки​​ - ввести в​ + P.​ ее текст может​​ ячейки с одинаковым​​(Данные).​​ данные:​​Совет:​ клавишу Delete.​Во всплывающем окне​ выбраны три диапазоны​ списке.​ макетами, но одинаковыми​

   ​ в рамках всей​ статья была вам​Создавать связи с исходными​

   

6. ​ на вкладке (в​​ персонально, что ужасно​​ ячейку чистого листа​​На шаге 1 мастера​​ содержать неточности и​ расположением. Например: ячейка​Выберите функцию​

7. ​Вы можете использовать инструмент​​ Чтобы указать ссылку на​Нажмите кнопку​Консолидация​ листа:​Разместите каждый диапазон на​​ метками данных.​ компании.​​ полезна. Просим вас​ данными​ меню)​

   ​ трудоемко. Лучше воспользоваться​​ формулу вида​

   • ​ нажмите кнопку​ грамматические ошибки. Для​В2​Sum​

   • ​ «​ ячейку — например,​ОК​нажмите кнопку​Выделите данные.​ отдельном листе, но​Консолидация данных по категории​Совет:​ уделить пару секунд​(Create links to source​Данные - Консолидация​ принципиально другим инструментом.​=’2001 год’!B3+’2002 год’!B3+’2003 год’!B3​нескольких диапазонах консолидации​ нас важно, чтобы​(distric1.xls) + ячейка​

8. ​(Сумма), чтобы суммировать​​Консолидация​​ продажи! B4 —​, а Excel создаст​Добавить​Далее в поле​ не было введено​ похоже на создание​ Если вы часто консолидировать​

   • ​ и сообщить, помогла​ data)​(Data - Consolidate)​Рассмотрим следующий пример. Имеем​которая просуммирует содержимое ячеек​и нажмите кнопку​ эта статья была​

   • ​В2​ данные.​» в Excel, чтобы​ в формуле, не​ консолидации для вас.​. Повторите этот сценарий,​

Консолидация данных с помощью формул

​ссылка​ никаких данных в​ сводной таблицы. Со​

​ данные, он помогут​ ли она вам,​позволит в будущем​. Откроется соответствующее окно:​ три разных файла​ B2 с каждого​Далее​ вам полезна. Просим​(district2.xls) + ячейка​Поместите курсор в следующую​ объединить листы, расположенные​ вводя, введите формулу​ Кроме того можно​

​ чтобы добавить все​​нажмите кнопку​ основном листе где​ сводной таблицей тем​ создать новые листы​ с помощью кнопок​ (при изменении данных​Установите курсор в строку​ (​ из указанных листов,​.​ вас уделить пару​В2​ строку, выберите диапазон​ в одной или​ до того места,​​ применить форматирование. Бывает​​ нужные диапазоны консолидации.​Свернуть​ план для консолидации​ не менее, вы​ на основе шаблона​ внизу страницы. Для​ в исходных файлах)​Ссылка​

​Иван.xlsx​ и затем скопировать​В шаге 2а мастера​

​ секунд и сообщить,​(district3.xls). Поскольку наши​A1:E4​ нескольких книгах, в​ куда требуется вставить​ только необходимо отформатировать​Обновления автоматически и вручную:​, чтобы уменьшить масштаб​ данных. Excel будет​ можете легко переупорядочение​ с единообразным макетом.​ удобства также приводим​

support.office.com

Консолидация в Excel

​ производить пересчет консолидированного​(Reference)​​,​​ ее на остальные​ выберите количество нужные​ помогла ли она​ листы не идентичны,​в документе​ один. На рисунках​ ссылку, а затем​

​ один раз, если​​Если требуется автоматическое​ панели и выбрать​ сделать это для​ категории. Если вам​ Дополнительные сведения о​ ссылку на оригинал​ отчета автоматически.​и, переключившись в​

​Рита.xlsx​ ячейки вниз и​ поля страницы.​ вам, с помощью​​ значит нам нужно,​​district1.xls​ ниже представлены три​ щелкните лист, используйте​ не перезапустить консолидации.​ обновление консолидации таблицы​ данные на листе.​ вас.​ нужна более гибкие​

1. ​ шаблонах читайте в​
2. ​ (на английском языке).​После нажатия на​​ файл Иван.xlsx, выделите​​и​​ вправо.​​С помощью полей страниц,​
3. ​ кнопок внизу страницы.​​ чтобы Excel сложил​​и нажмите кнопку​ разных документа.​
4. ​ клавишу tab и​Все названия, не совпадающие​​ при изменении исходных​​Щелкните лист, содержащий данные,​​Убедитесь, что всех диапазонов​​ Консолидация по категории,​​ статье: Создать шаблон.​​Для подведения итогов и​
5. ​ОК​​ таблицу с данными​​Федор​​Если листов очень много,​​ можно сгруппировать диапазонов​​ Для удобства также​​ ячейки с одинаковыми​
6. ​Add​​Прежде, чем начать:​​ затем щелкните ячейку.​​ с названиями в​​ данных, просто установите​ которые требуется консолидировать​​ совпадают.​ можно создать сводную​​ Это также идеально​ результатов из отдельных​

​видим результат нашей​​ (вместе с шапкой).​.xlsx​​ то проще будет​​ связанных данных или​ приводим ссылку на​​ подписями.​​(Добавить).​Если рабочие листы​ Excel будет завершена​ других исходных областях,​​ флажок​​ данные и нажмите​​На основном листе щелкните​​ таблицу .​​ время Настройка шаблона​​ листов, можно объединить​ работы:​ Затем нажмите кнопку​) с тремя таблицами:​ разложить их все​ у вас есть​

​ оригинал (на английском​Если вы поставите галочку​​Повторите шаг​ идентичны, гораздо проще​​ адрес имя и​ приведут к появлению​создавать связи с исходными​ кнопку​ левый верхний угол​Примечание:​

1. ​ с помощью таблиц​​ данные из каждого​​Наши файлы просуммировались по​

​Добавить​

​Хорошо заметно, что таблицы​ подряд и использовать​
​ страницы, отображающий консолидации​
​ языке) .​

​ напротив​

office-guru.ru

Консолидация нескольких источников данных в сводной таблице

​4​​ создать внутренние ссылки​ ячейку листа для​ дополнительных строк или​ данными​Развернуть диалоговое окно​ области, в которой​ В этой статье были​ Excel.​ листа в главном​ совпадениям названий из​(Add)​ не одинаковы -​ немного другую формулу:​ всех диапазонов. Например​Можно создать сводную таблицу,​Create links to source​для документов​ (если все в​ вас.​ столбцов в консолидированных​. Если это поле​

​справа, чтобы вернуться​ требуется разместить консолидированные​ созданы с Excel​Консолидация данных по положению​ листе. Листы может​ крайнего левого столбца​в окне консолидации,​ у них различные​=СУММ('2001 год:2003 год'!B3)​ если Консолидация данные​ используя данные из​ data​​district2.xls​​ одной книге) или​Примечание:​ данных.​ остается неограниченный, возможность​ в диалоговое окно​ данные.​ 2016. Хотя представления​ или категории двумя​

1. ​ быть в той​ и верхней строки​ чтобы добавить выделенный​ размеры и смысловая​

2. ​Фактически - это суммирование​ о продажах из​​ разных диапазонов. На​​(Создавать связи с​​и​​ внешние ссылки (если​

3. ​формулы в таких​Убедитесь, что все категории,​ обновления консолидации вручную.​

   ​Консолидация​Примечание:​ могут отличаться при​ способами.​ же книге как​ выделенных областей в​ диапазон в список​ начинка. Тем не​ всех ячеек B3​ четырех различных листов,​ том же листе,​ исходными данными), Excel​district3.xls​ книг несколько), чтобы​ случаях может быть​ которые не нужно​

4. ​Примечания:​.​

support.office.com

Консолидация (объединение) данных из нескольких таблиц в одну

Способ 1. С помощью формул 

​ Чтобы избежать перезаписи существующие​ использовании другой версии​Консолидация данных по расположению​ главном листе или​

​ каждом файле. Причем,​ объединяемых диапазонов.​ менее их можно​ на листах с​ может потребоваться создать​

​ на отдельные листы​ создаст ссылки на​.​ объединить данные.​ ошибкам, поскольку очень​

​ консолидировать, должны иметь​

​ ​Если лист, содержащий​ данные в основном​ Excel, шаги одинаковы.​: данные в исходных​ в других книгах.​ если развернуть группы​

​Повторите эти же действия​ собрать в единый​ 2001 по 2003,​ поле страницы consoldiate​ или даже в​

​ исходные данные. Когда​

​Поставьте галочки напротив​Как видите, листы не​ просто случайно выбираемых​ уникальные подписи, которые​Связи невозможно создать, если​ данные, которые необходимо​ листе, убедитесь, оставьте​Выполните указанные ниже действия,​ областях том же​ При объединении данных,​ (значками плюс слева​ для файлов Риты​ отчет меньше, чем​ т.е. количество листов,​

Способ 2. Если таблицы неодинаковые или в разных файлах

​ 2010 данные из​ отдельном книг может​ исходные данные изменятся,​Top Row​ идентичны. Тем не​ неправильной ячейки. Также​ появляются в диапазоне​ исходная и конечная​ объединить в другой​ достаточно ячеек справа​ чтобы консолидация нескольких​ порядке и использует​ чтобы упростить можно​

​ от таблицы), то​ и Федора. В​ за минуту. Единственным​​ по сути, может​​ разных листов, а​​ быть этих диапазонов.​​ консолидированные данные обновятся.​​(Подписи верхней строки),​​ менее, прелесть инструмента​​ может быть сложно​

​ только один источник.​ области находятся на​ книге, нажмите кнопку​ и верхний угол​ рабочих листов в​ одинаковых наклеек. Этот​ обновить и статистической​ можно увидеть из​ итоге в списке​ условием успешного объединения​ быть любым. Также​ другое поле страницы​ Чтобы сделать это,​Нажмите​Left Column​ «​ ошибку сразу после​Если данные для консолидации​ одном листе.​Обзор​

​ консолидированные данные.​ главном листе.​

1. ​ метод используется для​
2. ​ обработки при необходимости​ какого именно файла​
3. ​ должны оказаться все​ (консолидации) таблиц в​ в будущем возможно​ для консолидации данных​​ необходимо использовать мастер​​OK​​(Значения левого столбца)​
   
   
4. ​Консолидация​​ ввода сложные формулы.​​ находятся в разных​​Если необходимо изменить степень​, чтобы найти необходимую​Нажмите кнопку​Если вы еще не​ консолидации данных из​​ собирать данные.​​ какие данные попали​​ три диапазона:​ подобном случае является​ поместить между стартовым​ 2011.​
5. ​ сводных таблиц и​.​ и​» в том, что​Если данные для консолидации​ ячейках разных листов:​
   

​ диапазона — или​ книгу. После поиска​данные​ сделано, настройте данные​ нескольких листов, например​Например, если в каждом​ в отчет и​Обратите внимание, что в​ совпадение заголовков столбцов​ и финальным листами​Теперь следуйте дальнейшим указаниям​ выберите вариант​​Результат:​​Create links to source​​ он может с​​ находятся в одинаковых​Введите формулу со ссылками​​ замените диапазон —​ и нажмите кнопку​​>​ на каждом листе​ отделов бюджета листов,​ из региональных офисов​ ссылки на исходные​

​ данном случае Excel​​ и строк. Именно​​ дополнительные листы с​ мастера.​

​нескольких диапазонах консолидации​Урок подготовлен для Вас​ data​ легкостью складывать, подсчитывать,​ ячейках разных листов:​ на ячейки других​ выберите диапазон, во​ОК​Консолидация​ составные, сделав следующее:​ которые были созданы​ есть свой лист​ файлы:​ запоминает, фактически, положение​ по первой строке​

planetaexcel.ru

​ данными, которые также​

Консолидация данных – опция в Excel, которая позволяет объединять данные из разных таблиц в одну, а также объединять листы, расположенные в одном или разных файлах, в один. Чем же так полезна консолидация для интернет-маркетолога?

Разберем два наглядных примера.

Пример №1

У нас есть статистика по ключевым словам из контекстной рекламы с привязкой к основным показателям – кликам, расходам, транзакциям и доходу.

Специальный отчет в Google Analytics

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

"Дубли" ключевых слов в статистике

Это связано с тем, что статистика выгружена из двух рекламных систем (Яндекс.Директ и Google Ads), и в Google Рекламе для ключевых слов используется модификатор широкого соответствия (знак «+»).

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

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

Суммирование данных вручную

Это очень долго, особенно когда у вас много данных. С этой задачей легко справляется Консолидация данных. За 1 минуту и с помощью нескольких щелчков мыши можно автоматически сделать то, что мы привыкли делать вручную.

Но перед этим нужно не забыть воспользоваться функцией Заменить, убрав все + из таблицы.

Удаляем + в ключевых словах перед консолидацией

Последовательность действий:

- переходим на соседний лист (так удобнее);

- выделяем ячейку, в которую хотим вставить данные;

- переходим в Данные - Консолидация

Данные - Консолидация

В открывшемся окне нас интересуют следующие настройки:

• Функция – Сумма (поскольку хотим суммировать данные);
• Ссылка – выбираем весь диапазон данных на соседнем листе;
• Использовать в качестве имен – ставим галочки для подписи верхней строки и значения левого столбца.

Настройки консолидации

Нажимаем ОК. Если все сделали верно, то на текущем листе получите результат в виде объединенных (консолидированных) данных:

Итоговая таблица после консолидации

Таблица наша также уменьшилась. До консолидации было 252 строки, после объединения данных их стало 212:

ДО и ПОСЛЕ консолидации

Видео консолидации примера №1:

Консолидация данных в Excel

Пример №2

Вы каждый месяц для своего клиента готовите отчет по рекламе. Наступает момент, когда нужно свести данные за предыдущие периоды. Например, ежемесячные, чтобы получить годовой отчет. Или 6 месяцев, чтобы построить суммарный отчет за полгода. В общем, любой период, за который вам нужна консолидированная статистика.

Да, можно просто выгрузить данные из рекламных кабинетов Яндекс.Директ и Google Ads за общий период. Но что делать, когда данные по показам, кликам, расходам и конверсиям привязаны к бизнес-показателям, которых нет в интерфейсах, но которые завязаны на контекстной рекламе? А если в каждый из этих месяцев работали разные рекламные кампании (в один месяц – 10, в другой – 5 и т.д.)? Опять ручной процесс сведения данных.

И для таких случаев пригодится Консолидация в Excel. Давайте сведем данные с помощью данной настройки на примере выгрузки интернет-магазина за 3 месяца в один отчет. Статистика по месяцам расположена на разных вкладках (сентябрь - ноябрь - декабрь).

Статистика по 3 месяцам на разных вкладках файла

Мы хотим свести данные в одну таблицу. Воспользуемся консолидацией.

В открывшемся окне нас интересуют следующие настройки:

• Функция – Сумма (поскольку хотим суммировать данные);
• Ссылка – выбираем весь диапазон данных на листах поочередно (с помощью кнопки Добавить);
• Использовать в качестве имен – ставим галочки для подписи верхней строки и значения левого столбца.

Настройки консолидации

Галочка Создавать связи с исходными данными позволит производить пересчет консолидированного отчета автоматически при изменении данных в исходных файлах (в нашем примере – это листы сентябрь, октябрь, ноябрь). Нажимаем ОК.

Если все сделали правильно, то на текущем листе получите результат в виде объединенных (консолидированных) данных с трех таблиц в одной:

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

Слева появятся новые значки:

Связи с исходными данными

• 1 – свернуть все связи с исходными данными;
• 2 – развернуть все связи с исходными данными.

При нажатии на + раскроется соответствующая строка, в которой будут отображены данные по каждому из исходных листов.

Видео консолидации примера №2:

Консолидация данных в Excel

Таким образом, консолидация данных в Excel позволяет всего в несколько кликов объединить данных из разных файлов, листов и таблиц в единый объект. Еще один шаг на пути к автоматизации и упрощению работы с отчетностью!

Понравился эксперимент с видео? Поставь 5.0 статье ->

Консолидация данных в программе Microsoft Excel

​Смотрите также​ не одинаковы -​ вводим формулу со​ Используется инструмент «Сводная​ «Консолидация» заполнит столько​ создаст ссылки на​Консолидация​Consolidate​ в будущем Вы​(Создавать связи с​ к списку диапазонов​​Консолидированные данные​​ консолидировать, должны иметь​ссылка​ из одного шаблона.​ группу.​ этом же файле,​

Условия для выполнения процедуры консолидации

​ ссылками на исходные​ таблица» вместо «Консолидации​ строк и столбцов,​ исходные данные. Когда​» в Excel, чтобы​

Создание консолидированной таблицы

​ но на другом​ данными, размещенными в​ размеры и смысловая​ ячейки каждого листа.​ данных».​ сколько нужно.​ исходные данные изменятся,​ объединить листы, расположенные​ увидите, что указанные​Одним из главных достоинств​ нажмите​ те же шаги,​Разные рабочие книги​

1. ​ появляются в диапазоне​Свернуть​

   

2. ​: данные в исходных​ в Эксель является​ листе, то жмем​ разных таблицах, листах​
3. ​ начинка. Тем не​​ В нашем примере​​Консолидация данных по расположению​​Переходим на вкладку «Данные».​​ консолидированные данные обновятся.​ в одной или​ ранее ссылки сохранились.​​ инструмента​​ОК​

   

4. ​ чтобы добавить второй​Обновляем консолидацию​

   

   ​ только один источник.​​, чтобы уменьшить масштаб​​ областях не расположены​ очень удобным инструментом,​ кнопку, которая расположена​ или даже книгах,​ менее их можно​ – в ячейку​

   • ​ (по позициям) подразумевает,​
   • ​ В группе «Работа​
   • ​Нажмите​
   • ​ нескольких книгах, в​
   • ​ Внесите изменения, добавив​
   • ​Консолидация​
   • ​.​
   • ​ и все остальные​
   • ​Представьте такую ситуацию: у​
   • ​Если данные для консолидации​
   • ​ панели и выбрать​

   ​ в одном и​ благодаря которому можно​​ справа от поля​​ для удобства восприятия​

   

5. ​ собрать в единый​​ В2. Формула для​​ что исходные таблицы​ с данными» нажимаем​OK​ один. На рисунках​ или удалив диапазоны,​является то, что​Когда нажмете​ наборы данных к​ Вас есть данные​ находятся в разных​ данные на листе.​

   

6. ​ том же порядке,​ собрать воедино информацию​ ввода данных.​ лучше собрать информацию​ отчет меньше, чем​ суммы: ='1 квартал'!B2+'2​ абсолютно идентичны. Одинаковые​ кнопку «Консолидация».​.​

   

7. ​ ниже представлены три​ или изменив их​ данные могут располагаться​ОК​ списку диапазонов.​ за двенадцать месяцев​​ ячейках разных листов:​​Щелкните лист, содержащий данные,​

   

   ​ но имеют одинаковые​ расположенную не только​Переходим на тот лист,​

   

   ​ воедино. В Microsoft​ за минуту. Единственным​ квартал'!B2+'3 квартал'!B2.​ не только названия​

   

   ​Открывается диалоговое окно вида:​Результат:​ разных документа.​ размер, и нажмите​ в разных рабочих​​, Excel обобщит все​​Если у Вас на​ ведения бизнеса, информация​Введите формулу со ссылками​ которые требуется консолидировать​ метки. Используйте этот​ в разных таблицах​ где расположена таблица,​ Excel с этой​ условием успешного объединения​

   

8. ​Копируем формулу на весь​ столбцов, но и​На картинке открыт выпадающий​

   ​Урок подготовлен для Вас​Прежде, чем начать:​ОК​ книгах. Если у​​ выбранные данные на​​ каждом листе находятся​ по каждому месяцу​ на ячейки других​ данные и нажмите​​ способ, чтобы консолидировать​​ и на разных​ выделяем нужный диапазон.​ задачей можно справиться​ (консолидации) таблиц в​ столбец:​ наименования строк (см.​ список «Функций». Это​ командой сайта office-guru.ru​Если рабочие листы​​, чтобы создать консолидацию​ Вас есть несколько​​ новом листе. Вдоль​ одинаковые диапазоны данных,​ сохранена на отдельном​ листов (по одной​ кнопку​ данные из нескольких​ листах, но даже​ После ввода данных​ с помощью специального​

   ​ подобном случае является​Консолидация данных с помощью​​ пример выше). Если​​ виды вычислений, которые​

   

9. ​Источник: http://www.excel-easy.com/examples/consolidate.html​ идентичны, гораздо проще​ заново.​ рабочих книг, содержащих​ левого края экрана​ расположенные на одинаковой​ листе рабочей книги​

   

   ​ на каждый лист).​Развернуть диалоговое окно​ листов с разными​ размещенную в других​ жмем опять на​

​ инструмента, который называется​ совпадение заголовков столбцов​ формул удобна, когда​ в диапазоне 1​ может выполнять команда​Перевел: Антон Андронов​ создать внутренние ссылки​Если Вы включаете параметр​ одинаково организованные данные​ Вы увидите инструменты​ позиции, то все,​ Excel. Если каждый​ Например, чтобы консолидировать​

​справа, чтобы вернуться​

lumpics.ru

Консолидация данных с нескольких листов

​ макетами, но одинаковыми​​ файлах (книгах). Делается​ кнопку расположенную справа​«Консолидация»​ и строк. Именно​ объединяемые данные находятся​ «тахта» занимает шестую​ «Консолидация» при работе​Автор: Антон Андронов​ (если все в​Create Links to Source​ и охватывающие различные​ группировки, которые можно​ что Вам нужно​ лист содержит информацию​ данные из листов​ в диалоговое окно​ метками данных.​ это относительно просто​ от поля, куда​. Он предоставляет возможность​

​ по первой строке​ в разных ячейках​ строку, то в​ с данными. Выберем​В этой статье приводится​ одной книге) или​ Data​ периоды времени, Вы​ использовать, чтобы отображать​ делать – это​ по одному месяцу​ "Продажи" (в ячейке​Консолидация​Консолидация данных по категории​ и быстро.​

​ был внесен адрес​ собрать разрозненные данные​ и левому столбцу​ на разных листах.​ диапазоне 2, 3​ «Сумму» (значения в​ перечень контрольных заданий​ внешние ссылки (если​(Создавать связи с​ можете консолидировать их​ и скрывать данные.​ щелкать по ярлычку​ и если информация​ B4), "Кадры" (в​.​ похоже на создание​

​Автор: Максим Тютюшев​​ ячеек.​ в одну таблицу.​ каждой таблицы Excel​ Например, в ячейке​ и 4 это​ исходных диапазонах будут​ по теме Консолидация.​ книг несколько), чтобы​ исходными данными), то​ при помощи этого​Если был выбран пункт​ очередного листа, и​

Способов консолидации данных

​ на всех листах​ ячейке F5) и​Если лист, содержащий​

​ сводной таблицы. Со​​Примечание:​Вернувшись в окно настроек​ Давайте узнаем, как​ будет искать совпадения​ В5 на листе​ значение должно занимать​ суммироваться).​ За основу взяты​ объединить данные.​ Вы не сможете​

​ инструмента.​​Create links to source​ диапазон будет выбран​ организована единообразно, то​ "Маркетинг" (в ячейке​ данные, которые необходимо​ сводной таблицей тем​ Мы стараемся как можно​ консолидации, чтобы добавить​ это делается.​ и суммировать наши​ «Магазин», в ячейке​

• ​ тоже шестую строку.​Переходим к заполнению следующего​ реальные Контрольные задания,​Как видите, листы не​ обновить консолидированные данные,​Для этого потребуется выполнить​ data​ автоматически. Так что​ с помощью инструмента​ B9) в ячейке​ объединить в другой​

​ не менее, вы​​ оперативнее обеспечивать вас​ уже выбранные нами​Скачать последнюю версию​ данные.​ Е8 на листе​Это наиболее правильный способ​

Как для консолидации

​ поля – «Ссылка».​ которые  предлагается решить в​ идентичны. Тем не​ нажав на вкладке​

1. ​ те же самые​(Создавать связи с​ эта задача гораздо​Консолидация​

   • ​ A2 основного листа,​ книге, нажмите кнопку​ можете легко переупорядочение​ актуальными справочными материалами​ ячейки в список​ Excel​Для того, чтобы выполнить​ «Склад» и т.п.​ объединения данных, т.к.​Ставим в поле курсор.​ ВУЗах, техникумах, школах​ менее, прелесть инструмента​

   • ​Data​ действия, как мы​ исходными данными), то​ проще, чем кажется​Вы можете обобщить​ введите следующее:​Обзор​ категории. Если вам​ на вашем языке.​

   • ​ диапазонов, жмем на​Естественно, что не все​

2. ​ такую консолидацию:​Скачать все примеры консолидации​ исходные диапазоны идеальны​ Открываем лист «1​ и других обучающих​

   ​ «​​(Данные) команду​ делали только что:​ полученные данные ссылаются​ на первый взгляд.​ данные на одном​Совет:​

3. ​, чтобы найти необходимую​​ нужна более гибкие​​ Эта страница переведена​​ кнопку​​ таблицы можно консолидировать​​Заранее откройте исходные файлы​​ данных в Excel​

   

4. ​ для консолидации. Объединим​​ квартал». Выделяем таблицу​​ заведениях по теме​Консолидация​Consolidate​ выберите пустой лист​ на содержащие их​

   ​Диапазоны для сведения, находящиеся​ листе за весь​ Чтобы указать ссылку на​

   

5. ​ книгу. После поиска​

   ​ Консолидация по категории,​​ автоматически, поэтому ее​​«Добавить»​​ в одну, а​​Создайте новую пустую книгу​Если в книге включено​ таблицы, которые находятся​

   

   ​ вместе с шапкой.​ MS EXCEL. Желающие​» в том, что​(Консолидация), не удалив​​ или добавьте новый,​​ исходные ячейки. Кликнув​ на разных листах,​​ год.​​ ячейку — например,​
   ​ и нажмите кнопку​ можно создать сводную​ текст может содержать​.​​ только те, которые​​ (Ctrl + N)​ автоматическое вычисление формул,​ в разных книгах.​​ В поле «Ссылка»​​ могут самостоятельно потренироваться и​​ он может с​​ предварительно старые результаты.​ нажав​ по ячейке с​ не обязательно должны​Для этого откройте пустой​ продажи! B4 —​

   ​ОК​ таблицу .​ неточности и грамматические​

   

6. ​Как видим, после этого​​ соответствуют определенным условиям:​​Установите в нее активную​​ то при изменении​​Созданы книги: Магазин 1,​ появится первый диапазон​ воспользоваться решением приведенном​

7. ​ легкостью складывать, подсчитывать,​​ Причина в том,​Insert Sheet​ данными (не по​ быть одинакового размера.​ лист в рабочей​​ в формуле, не​, Excel в поле​​Примечание:​ ошибки. Для нас​ диапазон добавляется в​

   ​столбцы во всех таблицах​​ ячейку и выберите​

   • ​ данных в исходных​ Магазин 2 и​ для консолидации. Нажимаем​ в файле примера.​

   • ​ усреднять и т.д.​ что в таком​(Вставить лист) на​ ячейке с суммой),​ Число строк и/или​ книге Excel (добавьте​ вводя, введите формулу​ссылка​ В этой статье были​ важно, чтобы эта​ список.​ должны иметь одинаковое​ на вкладке (в​ диапазонах объединенная таблица​ Магазин 3. Структура​ кнопку «Добавить»​

8. ​Комментарии по поводу решений​​ данные из разных​​ случае вместо обновления​ вкладке​ Вы увидите ссылку​ столбцов на каждом​ новый, если необходимо)​ до того места,​введите путь к​

   • ​ созданы с Excel​ статья была вам​Аналогичным образом, добавляем все​ название (допускается лишь​ меню)​ будет обновляться автоматически.​ одинакова. Расположение данных​

   • ​Открываем поочередно второй, третий​ - приветствуются.​ листов и документов.​ будет сделана попытка​Insert​ на лист и​

Консолидация данных с помощью формул

​ листе может отличаться.​ и кликните в​ куда требуется вставить​

​ файлу и добавление​ 2016. Хотя представления​ полезна. Просим вас​ другие диапазоны, которые​ перестановка столбцов местами);​Данные - Консолидация​Имеем несколько однотипных таблиц​ идентично. Объединим их​ и четвертый квартал​Желающих поделиться собственными коллекциями​ Это намного проще,​ вставить одну консолидацию​(Вставка). Нажмите команду​

​ ячейку, содержащую эти​​ Например, если в​ нём по любой​ ссылку, а затем​ восклицательный знак, путь​ могут отличаться при​ уделить пару секунд​ будут участвовать в​не должно быть столбцов​(Data - Consolidate)​ на разных листах​ по позициям.​ – выделяем диапазоны​ заданий (в том​ чем использование формул.​ в другую, что​​Consolidate​​ данные.​ сентябре мы открыли​ ячейке. На вкладке​ щелкните лист, используйте​ к. Чтобы выбрать​ использовании другой версии​ и сообщить, помогла​ процессе консолидации данных.​

​ или строк с​. Откроется соответствующее окно:​ одной книги. Например,​

​Открываем все три книги.​ данных. Жмем «Добавить».​ числе и не​Откройте три документа.​ в результате приведет​(Консолидация). На этот​Если вы не отметили​ новый офис, то​Data​ клавишу tab и​ другие данные можно​ Excel, шаги одинаковы.​

support.office.com

Как работает консолидация данных в Excel 2013 – 2003

​ ли она вам,​Если же нужный диапазон​ пустыми значениями;​Установите курсор в строку​​ вот такие:​​ Плюс пустую книгу,​Таблицы для консолидации отображаются​ решенных) ждут по​Откройте пустую книгу. Нажмите​ к полной чепухе.​​ раз вместо того,​​ параметр​ с сентября диапазон​(Данные) нажмите​ затем щелкните ячейку.​ нажмите Продолжить.​Выполните указанные ниже действия,​ с помощью кнопок​ размещен в другой​шаблоны у таблиц должны​Ссылка​Необходимо объединить их все​​ куда будет помещена​​ в поле «Список​ адресу [email protected] (задачи,​ кнопку​ Вы можете обновить​ чтобы выбрать лист​Create links to source​ ячеек придется расширить,​Consolidate​

• ​ Excel будет завершена​Вот пример, в котором​
• ​ чтобы консолидация нескольких​
• ​ внизу страницы. Для​
• ​ книге (файле), то​
• ​ быть одинаковыми.​

Работаем с несколькими наборами данных

​(Reference)​ в одну общую​ консолидированная таблица. В​ диапазонов».​ аналогичные рассмотренным здесь,​Consolidate​ диапазоны, изменяя формулы​ в текущей рабочей​ data​ чтобы включить дополнительные​(Консолидация), чтобы открылось​ адрес имя и​ выбраны три диапазоны​ рабочих листов в​​ удобства также приводим​​ сразу жмем на​Рассмотрим, как создать консолидированную​и, переключившись в​ таблицу, просуммировав совпадающие​

​ пустой книге выбираем​Чтобы автоматически сделать заголовки​ с просьбой их​(Консолидация) на вкладке​ вручную, но более​ книге, нажмите кнопку​(Создавать связи с​​ данные, относящиеся к​​ диалоговое окно​​ ячейку листа для​​ листа выбранного:​ главном листе.​​ ссылку на оригинал​​ кнопку​ таблицу на примере​ файл Иван.xlsx, выделите​ значения по кварталам​ верхний левый угол​ для столбцов консолидированной​ решить, прошу не​Data​​ целесообразно будет создать​​Browse​​ исходными данными), то​​ новому офису. То​

​Consolidate​ вас.​​Во всплывающем окне​​Если вы еще не​​ (на английском языке).​​«Обзор…»​​ трех таблиц, имеющих​​ таблицу с данными​​ и наименованиям.​​ чистого листа. Открываем​​ таблицы, ставим галочку​​ направлять - повторно​​(Данные).​​ консолидацию заново.​​(Обзор), чтобы открыть​​ полученная консолидация –​​ же самое нужно​​(Консолидация). Выберите функцию​​Примечание:​​Консолидация​​ сделано, настройте данные​​Для подведения итогов и​​, выбираем файл на​​ одинаковый шаблон и​

​ (вместе с шапкой).​​Самый простой способ решения​​ меню инструмента «Консолидация».​ напротив «подписи верхней​​ их решать не​​Выберите функцию​Если же Вы не​ другую рабочую книгу.​ это просто обобщение​ будет сделать для​ для анализа данных​формулы в таких​нажмите кнопку​ на каждом листе​ результатов из отдельных​ жестком диске или​

​ структуру данных. Каждая​​ Затем нажмите кнопку​​ задачи "в лоб"​​Составим консолидированный отчет, используя​​ строки». Чтобы команда​ интересно). При направлении​Sum​ включили параметр​Теперь выберите ссылки, которые​ данных без каких-либо​ каждого листа, содержащего​ и ссылки на​ случаях может быть​

​Добавить​ составные, сделав следующее:​ листов, можно объединить​ съемном носителе, а​ из них расположена​Добавить​ - ввести в​ функцию «Среднее».​ суммировала все значения​ задания сообщайте свою​(Сумма), чтобы суммировать​Create Links to Source​ будут использованы для​ подробностей, без группировки​

​ дополнительные строки.​ диапазоны, которые нужно​ ошибкам, поскольку очень​. Повторите этот сценарий,​Убедитесь, что все диапазоны​ данные из каждого​ уже потом указанным​ на отдельном листе,​(Add)​ ячейку чистого листа​Чтобы показать путь к​ по каждой уникальной​ специальность.​ данные.​ Data​ этой рабочей книги.​ и содержащее только​Вы можете присвоить диапазонам​ свести. В нашем​

Полезный совет

​ просто случайно выбираемых​ чтобы добавить все​ данных представлены в​ листа в главном​ выше способом выделяем​ хотя по такому​в окне консолидации,​​ формулу вида​​ книгам с исходными​ записи крайнего левого​По данным таблиц, расположенных​Поместите курсор в следующую​(Создавать связи с​ Повторите шаги для​​ результаты суммирования.​​ имена, прежде чем​​ случае мы хотим​​ неправильной ячейки. Также​ нужные диапазоны консолидации.​​ формате списка. Каждый​​ листе. Листы может​ диапазон ячеек в​ же алгоритму можно​ чтобы добавить выделенный​=’2001 год’!B3+’2002 год’!B3+’2003 год’!B3​ диапазонами, ставим курсор​ столбца – напротив​ на 3-х листах​ строку, выберите диапазон​​ исходными данными), то​​ каждой рабочей книги,​​Так как эти данные​​ начинать процесс консолидации.​

​ просуммировать значения, поэтому​​ может быть сложно​​Обновления автоматически и вручную:​ столбец необходимо иметь​ быть в той​ этом файле. Естественно,​ создать консолидированную таблицу​ диапазон в список​которая просуммирует содержимое ячеек​​ в поле «Ссылка».​​ «значения левого столбца».​ — Сотрудники фирмы,​​A1:E4​​ Ваши сведенные данные​ в которой содержаться​​ содержат ссылки, Вы​​ Для этого выделите​ в поле​ ошибку сразу после​​Если требуется автоматическое​ метку (верхний колонтитул)​​ же книге как​ файл должен быть​ из данных расположенных​​ объединяемых диапазонов.​​ B2 с каждого​

Консолидированные данные

​ На вкладке «Вид»​​ Для автоматического обновления​​ Филиал 1 и​в документе​ представляют из себя​ интересующие Вас данные.​ можете использовать инструмент​ диапазон и задайте​Function​ ввода сложные формулы.​

​ обновление консолидации таблицы​​ в первой строке​ главном листе или​​ открыт.​ в разных книгах​Повторите эти же действия​ из указанных листов,​ нажимаем кнопку «Перейти​ объединенного отчета при​ Филиал 2 вычислить,​district1.xls​ просто обобщение, без​Мне стало легче переключаться​Trace Precedents​ ему имя в​

​(Функция) выберем​Если данные для консолидации​​ при изменении исходных​ и содержать похожие​​ в других книгах.​Точно так же можно​ (файлах).​ для файлов Риты​ и затем скопировать​ в другое окно».​ внесении новых данных​ используя возможность MS​

Полезный совет

​и нажмите кнопку​ каких-либо подробностей. В​ между открытыми рабочими​​(Влияющие ячейки), чтобы​​ поле​Sum​ находятся в одинаковых​ данных, просто установите​ данные. Должен быть​ При объединении данных,​ произвести некоторые другие​​Открываем отдельный лист для​​ и Федора. В​ ее на остальные​​Выбираем поочередно имена файлов,​​ в исходные таблицы​ Excel Консолидация:​Add​ таком случае, чтобы​ книгами, когда я​ перейти к исходной​Имя​(Сумма).​ ячейках разных листов:​ флажок​​ отсутствуют пустые строки​​ чтобы упростить можно​ настройки консолидированной таблицы.​ консолидированной таблицы.​ итоге в списке​ ячейки вниз и​ выделяем диапазоны в​​ – напротив «создавать​​   1) максимальный оклад​(Добавить).​

Форматируем данные

​ обновить консолидацию, кликните​ добавил кнопку​ ячейке, содержащей данные.​слева от строки​Вы можете выбрать любую​Ввод формулы с трехмерной​создавать связи с исходными​ или столбцы в​ обновить и статистической​Для того, чтобы автоматически​На открывшемся листе отмечаем​ должны оказаться все​​ вправо.​​ открывающихся книгах –​ связи с исходными​ мужчин и женщин;​Повторите шаг​ по верхней ячейке,​Switch Windows​

Разные рабочие книги

​ Для этого кликните​ формул. Когда Вы​​ из 11 операций:​​ ссылки, которая использует​ данными​ любом месте в​ обработки при необходимости​ добавить название столбцов​ ячейку, которая будет​ три диапазона:​Если листов очень много,​ жмем «Добавить».​ данными».​   2) средний оклад​4​

​ содержащей результаты, затем​(Перейти в другое​ по ячейке, содержащей​ дадите имена всем​Sum​ ссылку на диапазон​. Если это поле​​ списке.​​ собирать данные.​ в шапку, ставим​​ являться верхней левой​​Обратите внимание, что в​​ то проще будет​​Примечание. Показать программе путь​Внимание!!! Если вносить в​ сотрудников каждой должности​для документов​ нажмите на вкладке​​ окно) на Панель​​ интересующие Вас данные.​ диапазонам, то при​

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

​ данном случае Excel​ разложить их все​ к исходным диапазонам​ исходные таблицы новые​​ (по всем подразделениям).​​district2.xls​Data​ быстрого доступа.​

​ Откройте вкладку​​ настройке консолидации поставьте​Count​​ для консолидации данных​ обновления консолидации вручную.​ отдельном листе, но​ из региональных офисов​«Подписи верхней строки»​Находясь во вкладке​ запоминает, фактически, положение​ подряд и использовать​ можно и с​ значения, сверх выбранного​Исходные таблицы:​и​(Данные) команду​​Если включить параметр​​Formulas​ курсор в поле​(Количество),​ в ячейках с​Примечания:​ не было введено​ есть свой лист​

Обновляем консолидацию

​. Для того, чтобы​«Данные»​ файла на диске,​ немного другую формулу:​ помощью кнопки «Обзор».​ для консолидации диапазона,​Решение приведено в файле​district3.xls​Consolidate​Create Links to Source​(Формулы) и найдите​Reference​Average​ A2 по продажам​

​ ​​ никаких данных в​​ расходов, с помощью​​ производилось суммирование данных​​кликаем по кнопке​ прописывая для каждого​=СУММ('2001 год:2003 год'!B3)​ Либо посредством переключения​ они не будут​ примера.​.​​(Консолидация), внесите все​​ Data​ кнопку​

Предостережение!

​(Ссылка), нажмите​​(Среднее),​ через маркетинга включительно,​​Связи невозможно создать, если​ основном листе где​ консолидации можно свести​ устанавливаем галочку около​«Консолидация»​​ из них полный​​Фактически - это суммирование​​ на открытую книгу.​​ отображаться в объединенном​При выполнении ряда работ​Поставьте галочки напротив​ необходимые изменения и​(Создавать связи с​Trace Precedents​F3​Max​ в ячейке E5​ исходная и конечная​ план для консолидации​ эти данные на​ параметра​, которая расположена на​ путь (диск-папка-файл-лист-адреса ячеек).​

​ всех ячеек B3​Консолидированная таблица:​​ отчете. Чтобы можно​ у пользователя Microsoft​​Top Row​ нажмите​ исходными данными), то,​(Влияющие ячейки). Поскольку​и в открывшемся​(Максимум),​ главном листе вы​ области находятся на​ данных. Excel будет​ главном листе корпоративных​«Значения левого столбца»​​ ленте в блоке​​ Чтобы суммирование происходило​​ на листах с​​Консолидация данных по категориям​ было вносить данные​ Excel может быть​​(Подписи верхней строки),​​ОК​

​ когда сведение будет​​ исходная ячейка находится​​ окне​Min​ бы введите следующие​ одном листе.​ сделать это для​ расходов. Главный лист​. Если вы хотите,​ инструментов​ с учетом заголовков​ 2001 по 2003,​ применяется, когда исходные​ вручную, снимите флажок​ создано несколько однотипных​Left Column​.​ выполнено, все изменения​ на другом листе,​Paste Name​(Минимум),​ данные:​Если необходимо изменить степень​ вас.​ также может содержать​ чтобы при обновлении​«Работа с данными»​

​ столбцов и строк​ т.е. количество листов,​
​ диапазоны имеют неодинаковую​
​ «Создавать связи с​

​ таблиц в одном​

office-guru.ru

Консолидация в Excel

​(Значения левого столбца)​Достоинство инструмента​​ в исходных листах​​ наведите указатель мыши​(Вставка имени) выберите​Product​Существует много способов сведения​ диапазона — или​Убедитесь, что всех диапазонов​ итоговые и средние​

​ данных в первичных​​.​ необходимо включить оба​ по сути, может​ структуру. Например, в​ исходными данными».​ файле или в​ и​Консолидация​

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

1. ​Для выхода из меню​
2. ​ нескольких книгах.​Create links to source​​в том, что​​ будут отображаться и​​ стрелку, чтобы указатель​​ образом, Вы можете​
3. ​Count Numbers​​ Excel. Вы можете​​ выберите диапазон, во​На основном листе щелкните​
4. ​ о складских запасах​ и вся информация​​ данных.​​Использовать в качестве имен​​ в будущем возможно​​ товары. Какие-то наименования​​ «Консолидации» и создания​​Данные необходимо свести воедино.​
5. ​ data​​ он позволяет оставить​​ в консолидированных данных.​​ принял вид пустой​​ дать каждому диапазону​​(Количество чисел),​​ использовать инструмент​
6. ​ всплывающем окне Консолидация​​ левый верхний угол​​ и информацию о​​ в консолидированной таблице,​​В поле​(Use labels)​​ поместить между стартовым​ повторяются, а какие-то​​ сводной таблицы нажимаем​ Собрать в один​

​(Создавать связи с​​ данные там, где​ Второй столбец в​​ белой стрелки. Дважды​​ понятное имя, и​StdDev​​Таблицы​​ и обновлять его,​ области, в которой​ самых популярных товарах​ то обязательно следует​​«Функция»​​. Флаг​​ и финальным листами​​ нет.​​ ОК.​​ отчет, чтобы получить​ исходными данными).​ они изначально находились​ обобщенных данных все​ щелкните, чтобы открыть​ тогда позже не​

​(Смещенное отклонение),​и связанные с​​ выполнив указанные выше​ требуется разместить консолидированные​​ в рамках всей​ установить галочку около​требуется установить, какое​Создавать связи с исходными​ дополнительные листы с​Для создания объединенного диапазона​

1. ​Консолидированный отчет представляет собой​​ общее представление. С​​Примечание:​

​ (т.е. на разных​

​ также будет отображать​ диалоговое окно​
​ придётся вспоминать, что​
​StdDevp​

​ ним фильтры, чтобы​

office-guru.ru

Применение инструмента MS EXCEL Консолидация

​ действия. Создаст новый​ данные.​ компании.​ параметра​ действие с ячейками​ данными​ данными, которые также​ открываем меню «Консолидация».​ структурированную таблицу. Нажмем​ такой задачей справляется​Если вы не​ листах и даже​ название рабочей книги,​

​Go To​ за данные скрываются​

​(Несмещенное отклонение),​ отобразить только нужную​ в ссылке на​Примечание:​Совет:​«Создавать связи с исходными​ будет выполняться при​(Create links to source​ станут автоматически учитываться​ Выбираем функцию «Сумма»​ «плюсик» в левом​ инструмент «Консолидация».​ включите опции​

Задание 1

​ в разных книгах),​ а команда​(Переход) – ссылка​ на листе​Var​ информацию. Вы можете​ диапазон, поэтому вам​

​ Чтобы избежать перезаписи существующие​ Если вы часто консолидировать​

​ данными»​ совпадении строк и​ data)​

​ при суммировании.​

​ (для примера).​ поле – появятся​

excel2.ru

Консолидация данных в Excel с примерами использования

​Есть 4 файла, одинаковых​Top Row​ но все равно​Trace Precedents​ на ячейку будет​Лист1​(Смещенная дисперсия) и​

​ создать​ нужно будет сначала​ данные в основном​ данные, он помогут​. Но, в этом​ столбцов. Это могут​

Как сделать консолидацию данных в Excel

​позволит в будущем​Если исходные таблицы не​Добавляем исходные диапазоны любым​ значения, на основе​

​ по структуре. Допустим,​(Подписи верхней строки)​ объединить их для​(Влияющие ячейки) быстро​

• ​ указана в этом​в ячейках​
• ​Varp​Сводную таблицу​
• ​ удалить предыдущий перед​ листе, убедитесь, оставьте​

​ создать новые листы​ случае нужно учесть,​

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

​ и​ проведения аналитической работы.​ перенесет Вас к​ окне. Кликните по​A3:F40​(Несмещенная дисперсия).​

​, чтобы представить информацию​ Консолидация еще раз.​ достаточно ячеек справа​ на основе шаблона​

​ что, если вы​

​сумма;​ в исходных файлах)​ имеют разное количество​ способом. Ставим флажки​ суммы по количеству​ мебели.​Left Column​ Да, консолидация не​ ячейкам, связанным ссылкой,​

​ ссылке и далее​.​

​Кликните в поле​ в различных ракурсах.​ Просто выберите старой​ и верхний угол​ с единообразным макетом.​ захотите в исходную​количество;​ производить пересчет консолидированного​

​ строк, столбцов или​ у «значения левого​ и выручке.​Нужно сделать общий отчет​

​(Значения левого столбца),​ позволит изменять порядок​ если соответствующая рабочая​

​ нажмите​Как видите, в области​Reference​ Кроме этого, Вы​ ссылки и нажмите​ консолидированные данные.​ Дополнительные сведения о​ таблицу добавить новые​среднее;​ отчета автоматически.​ повторяющиеся данные или​ столбца» и «подписи​​ с помощью «Консолидации​ Excel просуммирует все​ данных, как это​ книга открыта, но​

​ОК​All References​(Ссылка) и нажмите​ можете соединять большие​ клавишу Delete.​Нажмите кнопку​ шаблонах читайте в​ строки, то придется​максимум;​После нажатия на​ находятся в разных​

​ верхней строки».​Программа Microsoft Excel позволяет​ данных». Сначала проверим,​ ячейки с одинаковым​

​ можно сделать в​ не сработает, если​, чтобы перейти к​(Список диапазонов) листы​ кнопку​ объемы данных в​Нажмите кнопку​данные​

Консолидация данных в Excel: практическая работа

​ снять галочку с​минимум;​ОК​

1. ​ файлах, то суммирование​Нажимаем ОК.​ выполнять разные виды​ чтобы​
2. ​ расположением. Например: ячейка​ сводной таблице. Но​ она закрыта.​ нужному месту.​ располагаются в алфавитном​Свернуть​
3. ​ виде сводки, которая​ОК​>​ Это также идеально​ данного пункта и​
4. ​произведение;​видим результат нашей​ при помощи обычных​Excel объединил информацию по​

​ консолидации данных:​макеты всех таблиц были​В2​ в случае, когда​Если данные на листах​Вы можете форматировать обобщенные​ порядке. Прежде чем​справа от поля​ покажет столько данных,​, а Excel создаст​Консолидация​ время Настройка шаблона​ пересчитать значения вручную.​количество чисел;​ работы:​

​ формул придется делать​ трем магазинам по​По расположению. Консолидированные данные​ одинаковыми;​(distric1.xls) + ячейка​ такой функционал не​

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

1. ​(в группе​ с помощью таблиц​Когда все настройки выполнены,​смещенное отклонение;​Наши файлы просуммировались по​ для каждой ячейки​ категориям. В отчете​ имеют одинаковое расположение​
2. ​названия столбцов – идентичными​В2​
3. ​ требуется, и нужно​ добавляете ещё один​ же, как делали​ указали ссылки на​ диапазон данных для​ увидеть. В этой​ Кроме того можно​
4. ​Работа с данными​ Excel.​ жмем на кнопку​несмещенное отклонение;​

​ совпадениям названий из​ персонально, что ужасно​ имеются данные по​ и порядок с​ (допускается перестановка колонок);​(district2.xls) + ячейка​

​ простое обобщение информации,​

​ офис уже после​ это в обычном​ все требуемые диапазоны.​ консолидации – для​ статье я познакомлю​ применить форматирование. Бывает​).​Консолидация данных по положению​

1. ​«OK»​смещенная дисперсия;​ крайнего левого столбца​ трудоемко. Лучше воспользоваться​
2. ​ всем товарам. Независимо​ исходными.​нет пустых строк и​В2​ причем представленное в​ консолидации, то первым​
3. ​ файле Excel. Вы​

​ Отметьте галочкой параметры​ этого откройте соответствующий​ Вас с инструментом​ только необходимо отформатировать​Выберите в раскрывающемся списке​ или категории двумя​.​несмещенная дисперсия.​ и верхней строки​

Примеры консолидации данных в Excel

​ принципиально другим инструментом.​ от того, продаются​По категории. Данные организованы​ столбцов.​(district3.xls). Поскольку наши​ такой же форме,​

​ делом, добавьте эту​ обнаружите, что второй​Use labels in​ лист, щелкнув по​Консолидация​ один раз, если​Функция​ способами.​Консолидированный отчет готов. Как​В большинстве случаев используется​

​ выделенных областей в​Рассмотрим следующий пример. Имеем​

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

​ не перезапустить консолидации.​функцию, которую вы​

​Консолидация данных по расположению​ видим, данные его​ функция​ каждом файле. Причем,​ три разных файла​ магазине или во​

exceltable.com

Консолидация (объединение) данных из нескольких таблиц в одну

Способ 1. С помощью формул 

​ Но в консолидированной​ нужно открыть.​ значит нам нужно,​ данные, то консолидация​

​ лист и сохраните​ рабочей книги. При​ имен):​ выделите мышью все​ помощью которого можно​

​Все названия, не совпадающие​ хотите использовать для​: данные в исходных​ сгруппированы. Чтобы посмотреть​«Сумма»​

​ если развернуть группы​

​ (​ всех трех.​ таблице используются одинаковые​Для консолидированных данных отводим​ чтобы Excel сложил​ – это простое​ рабочую книгу. Затем​

​ желании можно скрыть​Top Row​ необходимые данные, включая​ извлекать данные из​ с названиями в​

​ консолидации данных. По​

​ областях том же​ информацию внутри каждой​.​ (значками плюс слева​Иван.xlsx​На лист для сводного​ заглавия строк и​ новый лист или​ ячейки с одинаковыми​ и быстрое решение.​ откройте лист, содержащий​ этот столбец, кликнув​(Подписи верхней строки)​ заголовки строк и​

Способ 2. Если таблицы неодинаковые или в разных файлах

​ целого ряда листов​ других исходных областях,​ умолчанию используется значение​ порядке и использует​ группы, кликаем на​В поле​ от таблицы), то​,​ отчета вводим названия​ столбцов.​ новую книгу. Открываем​ подписями.​Урок подготовлен для Вас​

​ консолидированную информацию, выделите​ правой кнопкой мыши​ и​​ столбцов.​​ и даже рабочих​​ приведут к появлению​​ СУММ.​​ одинаковых наклеек. Этот​​ плюсик слева от​​«Ссылка»​

​ можно увидеть из​Рита.xlsx​ строк и столбцов​По формуле. Применяются при​ ее. Ставим курсор​Если вы поставите галочку​ командой сайта office-guru.ru​ и удалите все​ и нажав​Left Column​Вернувшись в диалоговое окно​ книг и обобщать​ дополнительных строк или​Вот пример, в котором​ метод используется для​ таблицы.​указываем диапазон ячеек​ какого именно файла​и​ из консолидируемых диапазонов.​

​ отсутствии постоянных категорий.​ в первую ячейку​

1. ​ напротив​
2. ​Источник: https://www.ablebits.com/office-addins-blog/2011/11/09/consolidating-data-excel/​ строки со сведенными​
3. ​Hide​(Значения левого столбца).​Consolidate​ их на одном​​ столбцов в консолидированных​​ выбраны три диапазоны​​ консолидации данных из​
   
   
4. ​Теперь содержимое группы доступно​​ одной из первичных​​ какие данные попали​​Федор​ Удобнее делать это​ Содержат ссылки на​ объединенного диапазона.​Create links to source​​Перевел: Антон Андронов​​ данными.​​(Скрыть) в контекстном​ Поставьте галочку также​(Консолидация), нажмите​ листе.​
5. ​ данных.​ листа:​ нескольких листов, например​ для просмотра. Аналогичным​ таблиц, которые подлежат​ в отчет и​
   

​.xlsx​ путем копирования.​ ячейки на других​Внимание!!! Правее и ниже​ data​Автор: Антон Андронов​На вкладке​ меню. Так Вы​ для​Add​Работаем с несколькими наборами​Убедитесь, что все категории,​​Выделите данные.​​ отделов бюджета листов,​​ способом можно раскрыть​​ консолидации. Если этот​ ссылки на исходные​​) с тремя таблицами:​В первую ячейку для​​ листах.​ этой ячейки должно​(Создавать связи с​Вы можете использовать инструмент​Data​

​ скроете столбец, но​​Create links to source​​(Добавить), чтобы добавить​ данных​

​ которые не нужно​Далее в поле​ которые были созданы​ и любую другую​ диапазон находится в​ файлы:​Хорошо заметно, что таблицы​ значений объединенной таблицы​По отчету сводной таблицы.​ быть свободно. Команда​ исходными данными), Excel​ «​(Данные) нажмите​ данные останутся, т.е.​ data​

planetaexcel.ru

​ первый набор данных​

Способ 1. С помощью формул 

Имеем несколько однотипных таблиц на разных листах одной книги. Например, вот такие:

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

Самый простой способ решения задачи "в лоб" - ввести в ячейку чистого листа формулу вида

=’2001 год’!B3+’2002 год’!B3+’2003 год’!B3

которая просуммирует содержимое ячеек B2 с каждого из указанных листов, и затем скопировать ее на остальные ячейки вниз и вправо.

 Если листов очень много, то проще будет разложить их все подряд и использовать немного другую формулу:

=СУММ('2001 год:2003 год'!B3)

Фактически - это суммирование всех ячеек B3 на листах с 2001 по 2003, т.е. количество листов, по сути, может быть любым. Также в будущем возможно поместить между стартовым и финальным листами дополнительные листы с данными, которые также станут автоматически учитываться при суммировании.

Способ 2. Если таблицы неодинаковые или в разных файлах

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

Рассмотрим следующий пример. Имеем три разных файла (Иван.xlsx, Рита.xlsx и Федор.xlsx) с тремя таблицами:

Хорошо заметно, что таблицы не одинаковы - у них различные размеры и смысловая начинка. Тем не менее их можно собрать в единый отчет меньше, чем за минуту. Единственным условием успешного объединения (консолидации) таблиц в подобном случае является совпадение заголовков столбцов и строк. Именно по первой строке и левому столбцу каждой таблицы Excel будет искать совпадения и суммировать наши данные.

Для того, чтобы выполнить такую консолидацию:

1. Заранее откройте исходные файлы
2. Создайте новую пустую книгу (Ctrl + N)
3. Установите в нее активную ячейку и выберите на вкладке (в меню) Данные - Консолидация (Data - Consolidate). Откроется соответствующее окно:
   
   
   
   
4. Установите курсор в строку Ссылка (Reference) и, переключившись в файл Иван.xlsx, выделите таблицу с данными (вместе с шапкой). Затем нажмите кнопку Добавить (Add) в окне консолидации, чтобы добавить выделенный диапазон в список объединяемых диапазонов.
5. Повторите эти же действия для файлов Риты и Федора. В итоге в списке должны оказаться все три диапазона:
   
   

 Обратите внимание, что в данном случае Excel запоминает, фактически, положение файла на диске, прописывая для каждого из них полный путь (диск-папка-файл-лист-адреса ячеек). Чтобы суммирование происходило с учетом заголовков столбцов и строк необходимо включить оба флажка Использовать в качестве имен(Use labels). Флаг Создавать связи с исходными данными(Create links to source data) позволит в будущем (при изменении данных в исходных файлах) производить пересчет консолидированного отчета автоматически.

После нажатия на ОК видим результат нашей работы:

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

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

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

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

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

См. также

Полные сведения о формулах в Excel

Рекомендации, позволяющие избежать появления неработающих формул

Поиск ошибок в формулах

Сочетания клавиш и горячие клавиши в Excel

Функции Excel (по алфавиту)

Функции Excel (по категориям)

Консолидация нескольких листов в одной сводной таблице

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

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

Итоговый консолидированный отчет сводной таблицы может содержать следующие поля в области Список полей сводной таблицы, добавляемой в сводную таблицу: "Строка", "Столбец" и "Значение". Кроме того, в отчет можно включить до четырех полей фильтра, которые называются "Страница1", "Страница2", "Страница3" и "Страница4".

Настройка исходных данных

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

Поля страницы при консолидации данных

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

Использование именованных диапазонов

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

Другие способы консолидации данных

В Excel также доступны другие способы консолидации данных, которые позволяют работать с данными в разных форматах и макетах. Например, вы можете создавать формулы с объемными ссылками или использовать команду Консолидация (доступную на вкладке Данные в группе Работа с данными).

Консолидация нескольких диапазонов

Для консолидации нескольких диапазонов вы можете воспользоваться мастером сводных таблиц и диаграмм. В нем можно указать, сколько полей страницы будет использоваться: ни одного, одно или несколько.

Консолидация данных без использования полей страницы

Чтобы объединить данные всех диапазонов и создать консолидированный диапазон без полей страницы, сделайте следующее:

Добавьте мастер сводных таблиц и диаграмм на панель быстрого доступа. Для этого:

Щелкните стрелку рядом с панелью инструментов и выберите Дополнительные команды.

В списке Выбрать команды из выберите пункт Все команды.

Выберите в списке пункт Мастер сводных таблиц и диаграмм и нажмите кнопку Добавить, а затем — кнопку ОК.

В книге щелкните пустую ячейку, которая не является частью сводной таблицы.

Щелкните значок мастера на панели быстрого доступа.

На странице Шаг 1 мастера выберите параметр в нескольких диапазонах консолидации, а затем нажмите кнопку Далее.

На странице Шаг 2а выберите параметр Создать поля страницы, а затем нажмите кнопку Далее.

На странице Шаг 2б сделайте следующее:

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

Совет: Если диапазон ячеек находится в другой книге, сначала откройте ее, чтобы упростить выбор данных.

В разделе Во-первых, укажите количество полей страницы сводной таблицы введите 0, а затем нажмите кнопку Далее.

На странице Шаг 3 укажите, следует ли добавлять сводную таблицу на новый или же на существующий лист, и нажмите кнопку Готово.

Консолидация данных с использованием одного поля страницы

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

Добавьте мастер сводных таблиц и диаграмм на панель быстрого доступа. Для этого:

Щелкните стрелку рядом с панелью инструментов и выберите Дополнительные команды.

В списке Выбрать команды из выберите пункт Все команды.

Выберите в списке пункт Мастер сводных таблиц и диаграмм и нажмите кнопку Добавить, а затем — кнопку ОК.

В книге щелкните пустую ячейку, которая не является частью сводной таблицы.

На странице Шаг 1 мастера выберите параметр в нескольких диапазонах консолидации, а затем нажмите кнопку Далее.

На странице Шаг 2а выберите параметр Создать одно поле страницы, а затем нажмите кнопку Далее.

На странице Шаг 2б сделайте следующее:

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

Совет: Если диапазон ячеек находится в другой книге, сначала откройте ее, чтобы упростить выбор данных.

Нажмите кнопку Далее.

На странице Шаг 3 укажите, следует ли добавлять сводную таблицу на новый или же на существующий лист, и нажмите кнопку Готово.

Консолидация данных с использованием нескольких полей страницы

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

Добавьте мастер сводных таблиц и диаграмм на панель быстрого доступа. Для этого:

Щелкните стрелку рядом с панелью инструментов и выберите Дополнительные команды.

В списке Выбрать команды из выберите пункт Все команды.

Выберите в списке пункт Мастер сводных таблиц и диаграмм и нажмите кнопку Добавить, а затем — кнопку ОК.

В книге щелкните пустую ячейку, которая не является частью сводной таблицы.

На странице Шаг 1 мастера выберите параметр в нескольких диапазонах консолидации, а затем нажмите кнопку Далее.

На странице Шаг 2а выберите параметр Создать поля страницы, а затем нажмите кнопку Далее.

На странице Шаг 2б сделайте следующее:

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

Совет: Если диапазон ячеек находится в другой книге, сначала откройте ее, чтобы упростить выбор данных.

В разделе Во-первых, укажите количество полей страницы сводной таблицы щелкните число полей, которые вы хотите использовать.

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

Если в разделе Во-первых, укажите количество полей страницы сводной таблицы задано число 1, выберите каждый из диапазонов, а затем введите уникальное имя в поле Первое поле. Если у вас четыре диапазона, каждый из которых соответствует кварталу финансового года, выберите первый диапазон, введите имя "Кв1", выберите второй диапазон, введите имя "Кв2" и повторите процедуру для диапазонов "Кв3" и "Кв4".

Если в разделе Во-первых, укажите количество полей страницы сводной таблицы задано число 2, выполните аналогичные действия в поле Первое поле. Затем выберите два диапазона и введите в поле Второе поле одинаковое имя, например "Пг1" и "Пг2". Выберите первый диапазон и введите имя "Пг1", выберите второй диапазон и введите имя "Пг1", выберите третий диапазон и введите имя "Пг2", выберите четвертый диапазон и введите имя "Пг2".

Нажмите кнопку Далее.

На странице Шаг 3 укажите, следует ли добавлять сводную таблицу на новый или же на существующий лист, и нажмите кнопку Готово.

Как собрать данные с нескольких листов или книг?

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

‘————————————————————————————— ‘ Author : Щербаков Дмитрий(The_Prist) ‘ Профессиональная разработка приложений для MS Office любой сложности ‘ Проведение тренингов по MS Excel ‘ https://www.excel-vba.ru ‘ info@excel-vba.ru ‘ WebMoney — R298726502453; Яндекс.Деньги — 41001332272872 ‘ Purpose: http://www.excel-vba.ru/chto-umeet-excel/kak-sobrat-dannye-s-neskolkix-listov-ili-knig/ ‘ Процедура сбора данных с нескольки листов/книг ‘————————————————————————————— Option Explicit Sub Consolidated_Range_of_Books_and_Sheets() Dim iBeginRange As Range, rCopy As Range, lCalc As Long, lCol As Long Dim oAwb As String, sCopyAddress As String, sSheetName As String Dim lLastrow As Long, lLastRowMyBook As Long, li As Long, iLastColumn As Integer Dim wsSh As Worksheet, wsDataSheet As Worksheet, bPolyBooks As Boolean, avFiles Dim wbAct As Workbook Dim bPasteValues As Boolean, IsPasteSheetName As Boolean On Error Resume Next ‘Выбираем диапазон выборки с книг Set iBeginRange = Application.InputBox("Выберите диапазон сбора данных." & vbCrLf & _ "1. При выборе только одной ячейки данные будут собраны со всех листов начиная с этой ячейки. " & _ vbCrLf & "2. При выделении нескольких ячеек данные будут собраны только с указанного диапазона всех листов.", Type:=8) ‘для указания диапазона без диалогового окна: ‘Set iBeginRange = Range("A1:A10") ‘диапазон указывается нужный ‘Если диапазон не выбран — завершаем процедуру If iBeginRange Is Nothing Then Exit Sub End If ‘Указываем имя листа ‘Допустимо указывать в имени листа символы подставки ? и *. ‘Если указать только * то данные будут собираться со всех листов sSheetName = InputBox("Введите имя листа, с которого собирать данные(если не указан, то данные собираются со всех листов)", "Параметр") ‘Если имя листа не указано — данные будут собраны со вех листов If sSheetName = "" Then sSheetName = "*" End If ‘добавлять ли имя листа в начало таблицы IsPasteSheetName = (MsgBox("Вставлять имя листа первым столбцом?", vbQuestion + vbYesNo, "www.wxcel-vba.ru") = vbYes) On Error GoTo 0 ‘Запрос — вставлять на результирующий лист все данные ‘или только значения ячеек (без формул и форматов) bPasteValues = (MsgBox("Вставлять только значения?", vbQuestion + vbYesNo, "www.wxcel-vba.ru") = vbYes) ‘Запрос сбора данных с книг(если Нет — то сбор идет с активной книги) If MsgBox("Собрать данные с нескольких книг?", vbInformation + vbYesNo, "www.wxcel-vba.ru") = vbYes Then avFiles = Application.GetOpenFilename("Excel files(*.xls*),*.xls*", , "Выбор файлов", , True) If VarType(avFiles) = vbBoolean Then Exit Sub bPolyBooks = True lCol = 1 Else avFiles = Array(ThisWorkbook.FullName) End If If IsPasteSheetName Then lCol = lCol + 1 End If ‘отключаем обновление экрана, автопересчет формул и отслеживание событий ‘для скорости выполнения кода и для избежания ошибок, если в книгах есть иные коды With Application lCalc = .Calculation .ScreenUpdating = False .EnableEvents = False .Calculation = xlManual End With ‘создаем новый лист в книге для сбора Set wsDataSheet = ActiveWorkbook.Sheets.Add(After:=Sheets(Sheets.Count)) ‘если нужно сделать сбор данных на новый лист книги с кодом ‘Set wsDataSheet = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)) ‘цикл по книгам For li = LBound(avFiles) To UBound(avFiles) If bPolyBooks Then Set wbAct = Workbooks.Open(Filename:=avFiles(li)) Else Set wbAct = ThisWorkbook End If oAwb = wbAct.Name ‘цикл по листам For Each wsSh In wbAct.Sheets If wsSh.Name Like sSheetName Then ‘Если имя листа совпадает с именем листа, в который собираем данные ‘и сбор идет только с активной книги — то переходим к следующему листу If wsSh.Name = wsDataSheet.Name And bPolyBooks = False Then GoTo NEXT_ With wsSh Select Case iBeginRange.Count Case 1 ‘собираем данные начиная с указанной ячейки и до конца данных lLastrow = .Cells(1, 1).SpecialCells(xlLastCell).Row iLastColumn = .Cells.SpecialCells(xlLastCell).Column sCopyAddress = .Range(.Cells(iBeginRange.Row, iBeginRange.Column), .Cells(lLastrow, iLastColumn)).Address Case Else ‘собираем данные с фиксированного диапазона sCopyAddress = iBeginRange.Address End Select lLastRowMyBook = wsDataSheet.Cells.SpecialCells(xlLastCell).Row + 1 ‘определяем для копирования диапазон только заполненных данных на листе Set rCopy = Intersect(.Range(sCopyAddress).Parent.UsedRange, .Range(sCopyAddress)) ‘вставляем имя книги, с которой собраны данные If lCol > 0 Then If bPolyBooks Then wsDataSheet.Cells(lLastRowMyBook, 1).Resize(rCopy.Rows.Count).Value = oAwb End If If IsPasteSheetName Then wsDataSheet.Cells(lLastRowMyBook, lCol).Resize(rCopy.Rows.Count).Value = .Name End If End If ‘если вставляем только значения If bPasteValues Then rCopy.Copy wsDataSheet.Cells(lLastRowMyBook, 1).Offset(, lCol).PasteSpecial xlPasteValues wsDataSheet.Cells(lLastRowMyBook, 1).Offset(, lCol).PasteSpecial xlPasteFormats Else ‘если вставляем все данные ячеек(формулы, форматы и т.д.) rCopy.Copy wsDataSheet.Cells(lLastRowMyBook, 1).Offset(, lCol) End If End With End If NEXT_: Next wsSh If bPolyBooks Then wbAct.Close False End If Next li With Application .ScreenUpdating = True .EnableEvents = True .Calculation = lCalc End With End Sub

Приведенный выше код необходимо вставить в стандартный модуль(Что такое модуль? Какие бывают модули?). Выполнить его можно будет из этой книги нажатием клавиш Alt+F8. В появившемся окне выбрать Consolidated_Range_of_Books_and_Sheets и нажать Выполнить. Так же можно создать на листе кнопку и назначить ей данный макрос. Так же, если впервые работаете с макросами настоятельно рекомендую прочитать статью: Что такое макрос и где его искать?, а так же Почему не работает макрос?

После вызова макроса поочередно будут появляется запросы, в которых надо будет указать исходные параметры:

• Диапазон сбора данных — Если в окне выбора диапазона выбрать только одну ячейку, то данные будут собраны со всех листов книги/книг, начиная с этой ячейки и до последней ячейки листа.
  Если выбрать несколько ячеек, данные будут собраны только с указанного диапазона всех листов книги/книг. Допускается указать несвязанный(рваный) диапазон(например, только три столбца: A:A , D:D , F:F ). Сделать это можно, выделив нужный диапазон с зажатой клавишей Ctrl
• Имя листа — Необязателен для указания. Если не указан — данные будут собраны со всех листов. Указать можно как точное соответствие имени листа, так и с частичным соответствием. Например, если в книгах для сбора данных необходимо собрать данные только с листа "Январь", то следует так и указать — "Январь". Если требуется собрать данные только с листов, начинающихся с "Продажи"("Продажи ЮГ", "Продажи НН", "Продажи Запад" и т.д.), то следует применить символ подстановки звездочку — "Продажи*". Если надо собрать с листов, содержащих в имени "продажи"("Итоговые продажи ЮГ", "Продажи НН", "Сезонные продажи" и т.д.), то указываем "*продажи*". Если надо собрать только с листа "Сезонные продажи", но известно, что вместо пробела может быть нижнее подчеркивание или тире("Сезонные продажи", "Сезонные_продажи", "Сезонные-продажи") или иной символ, то можно также применить звездочку — "Сезонные*продажи". Но если среди листов могут встречаться и такие как "Сезонные разовые продажи", "Сезонные корпоративные продажи" и т.п., но информацию с них собирать не надо, то можно применить вопросительный знак — "Сезонные?продажи". Вопросительный знак заменяет любой один символ, звездочка — любое количество любых символов.
• Вставлять имя листа первым столбцом? — если выбрать Да, перед данными в итоговой таблице будут записаны имена листов, с которых были собраны данные. Если будет указано собирать данные с нескольких книг — то имя листа будет во втором столбце, если с листов одной книги — то имя листа будет первым столбцом.
• Вставлять только значения? — если выбрать Да, то в результирующий лист с листов будут вставлены исключительно значения ячеек (без формул), но при этом сохранятся их форматы(формат чисел, цвет заливки, цвет шрифта, границы и т.п.). Может пригодится, если на листах для сбора записаны формулы, ссылающиеся на другие листы, книги, диапазоны. При обычном копировании может случиться так, что формула выдаст ошибку, т.к. в книге для вставки нет таких листов и диапазонов или данные расположены иначе. Если выбрать Нет, то все ячейки с листов на результирующий будут копироваться в точности как в исходных листах.
• И последний запрос: Собрать данные с нескольких книг? — если выбрать Да, то появится диалоговое окно выбора файлов. Надо указать все файлы, данные с которых необходимо собрать. Если выбрать Нет, то данные будут собираться с листов только активной книги. При этом, если выбран вариант сбора с нескольких книг, то первым столбцом в итоговой таблице будут записаны имена файлов, с которых были собраны данные

Данные будут собраны на новый лист книги с макросом. Если данные собирались с нескольких книг, то в первый столбец будут занесены имена книг, с которых собраны данные.

Если после сбора данных обнаружили, что после каждого файла/листа много пустых строк, то следует найти в коде строку:

lLastrow = .Cells(1, 1).SpecialCells(xlLastCell).Row

и заменить её на строку примерно следующего содержания:

lLastrow = .Cells(.Rows.Count, 1).End(xlUp).Row

где 1 — это номер столбца на листах данных, в котором искать последнюю заполненную ячейку.
Актуально это для файлов с одинаковой структурой. Например, если сбор идет с листов по продажам, то вполне может быть такое, что в столбце 1 может не быть данных. Поэтому следует определить номер столбца, в котором наполнение данных максимально. Например, это может быть столбец с наименованиями товара или с суммами. Если это столбец D, то следует строку записать так:

lLastrow = .Cells(.Rows.Count, 4).End(xlUp).Row ‘ищем последнюю строку в 4-м столбце

Подробнее про определение последней строки можно прочитать в статье: Как определить последнюю ячейку на листе через VBA?

Важное замечание: Если вы используете Excel 2007 и выше и файлы для сбора данных тоже в этом формате, то следует скачанный файл сначала сохранить в формат "Книга Excel с поддержкой макросов(.xlsm)", закрыть и открыть заново. Иначе есть шанс получить ошибку при сборе данных, т.к. Excel будет в режиме совместимости и не сможет поместить на результирующий лист более 65536 строк.

Сбор данных с листов и книг.xls (73,0 KiB, 35 050 скачиваний)

Статья помогла? Поделись ссылкой с друзьями!