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

Идея уплотнения довольно проста: вам просто нужно скопировать данные из второго и последующих листов в первую пустую строку на первом листе. К счастью, в Excel есть функция, позволяющая выполнять именно этот процесс — инструмент «Консолидировать».

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

В книге, листы которой вы хотите объединить, выберите Данные | Консолидировать. Excel отображает диалоговое окно «Консолидировать». (См. Рис. 1.)

В диалоговом окне есть много элементов управления, но главное, о чем вам нужно беспокоиться, — это указать диапазоны для объединения.

image

Рисунок 1. Диалоговое окно «Консолидировать».

Вы указываете диапазоны, используя поле «Ссылка». Укажите в поле первый диапазон, который вы хотите объединить. Если вы объединяете по позиции, то в ссылке не должно быть меток столбцов; если по категории, то надо. Когда вы указываете ссылку на диапазон, вы нажимаете «Добавить», и ссылка появляется в списке «Все ссылки». Вы продолжаете определять контрольные диапазоны, пока все они не будут завершены.

Если вы хотите, чтобы консолидированные данные содержали ссылки на исходные данные, убедитесь, что установлен флажок «Создать ссылки на исходные данные», в противном случае снимите его. Затем вы можете нажать ОК, чтобы выполнить консолидацию.

Обратите внимание, что в диалоговом окне «Консолидировать» есть и другие элементы управления; Упомянутые выше элементы управления — это те, на которые следует как минимум обращать внимание. Лучший способ узнать, что делают другие — поиграться с ними, сделав несколько консолидаций.

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

Sub Combine()

Dim J As Integer

On Error Resume Next     Sheets(1).Select     Worksheets.Add ' add a sheet in first place     Sheets(1).Name = "Combined"



' copy headings     Sheets(2).Activate     Range("A1").EntireRow.Select     Selection.Copy Destination:=Sheets(1).Range("A1")



' work through sheets     For J = 2 To Sheets.Count ' from sheet 2 to last sheet         Sheets(J).Activate ' make the sheet active         Range("A1").Select         Selection.CurrentRegion.Select ' select all cells in this sheets

' select all lines except title         Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select

' copy cells selected in the new sheet on last line         Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)

Next End Sub

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

_Примечание: _

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

link: / excelribbon-ExcelTipsMacros [Щелкните здесь, чтобы открыть эту специальную страницу в новой вкладке браузера].

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (3005) относится к Microsoft Excel 97, 2000, 2002 и 2003. Вы можете найти версию этого совета для ленточного интерфейса Excel (Excel 2007 и более поздних версий) здесь:

link: / excelribbon-Condensing_Multiple_Worksheets_Into_One [Объединение нескольких рабочих листов в один].