У Ом есть сотни листов с разными именами в одной книге. Структура каждого рабочего листа по сути одинакова. Он хочет скопировать фиксированный диапазон (A146: O146) из каждого листа на новый лист, одну строку за другой.

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

  1. Откройте вкладку «Формулы» на ленте.

  2. В группе «Определенные имена» щелкните инструмент «Диспетчер имен». Excel отображает диалоговое окно Диспетчер имен.

  3. Щелкните кнопку New. Excel отображает диалоговое окно «Новое имя». (См. Рис. 1.)

  4. В поле «Имя» введите имя ListSheets (обратите внимание, что это одно слово без пробелов).

  5. В поле «Ссылается на» введите следующую формулу:

  6. Нажмите кнопку ОК, чтобы завершить создание именованного диапазона. Новый диапазон должен появиться в диалоговом окне «Диспетчер имен».

  7. Нажмите кнопку «Закрыть», чтобы закрыть диалоговое окно «Диспетчер имен».

Определив таким образом формулу, вы можете перейти к рабочему листу, на котором хотите объединить все эти диапазоны. (Допустим, этот рабочий лист называется «Сводка».) Я настоятельно рекомендую убедиться, что этот рабочий лист является самым последним в вашей книге. На листе «Сводка» поместите следующую формулу в столбец A любой строки:

=INDIRECT(INDEX(ListSheets,ROWS($A$1:$A1))&"!"&CELL("address",A$146))

Скопируйте формулу, но для представления всех листов требуется много строк. Другими словами, если у вас есть 25 листов (не считая сводного листа), скопируйте формулу на 24 строки вниз. После подсчета оригинала формула должна появиться в общей сложности в 25 строках.

Примечание: формула ListSheets — та, которую вы определили в диспетчере имен — возвращает массив имен рабочих листов. Функция ROWS используется для определения того, какой элемент этого массива возвращается через функцию INDEX. Если ваш итоговый рабочий лист не последний в вашей книге, он может быть легко возвращен ListSheets, и вы в конечном итоге получите из него значения. Вы, несомненно, не хотите этого делать, поэтому я предложил сделать так, чтобы «Сводка» была последним листом в книге.

Теперь просто скопируйте формулы из столбца A вправо, чтобы они были полностью заполнены столбцом O. В результате вы будете иметь значения из A146: O146 в ячейках, содержащих формулы.

Ранее я сказал, что считаю подход, основанный на макросах, лучшим выбором.

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

Sub CopyRange()

Dim w As Worksheet     Dim sRange As String     Dim lRow As Long

sNewName = "Summary" 'Name for summary worksheet     sRange = "A146:O146" 'Range to copy from each worksheet

Worksheets(1).Select     Worksheets.Add     ActiveSheet.Name = sNewName

lRow = 2     For Each w In Worksheets         If w.Name <> sNewName Then             'Comment out the following line if you don't want to             'include worksheet names in the summary sheet             Cells(lRow, 1) = w.Name             'If you commented out the previous line, make a change             'in the following line: change (lRow, 2) to (lRow, 1)

w.Range(sRange).Copy Cells(lRow, 2)

lRow = lRow + 1         End If     Next w End Sub

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

Затем макрос делает активным первый рабочий лист в книге и добавляет новый рабочий лист, который будет использоваться для суммирования. Этому рабочему листу назначается имя, которое вы указали в переменной sNewName. Затем макрос проходит цикл, проверяя каждый из других листов. Пока рабочий лист не является сводным, имя рабочего листа помещается в столбец A сводного листа, а диапазон, указанный в переменной sRange (A146: O146), копируется в сводный рабочий лист, начиная со столбца B. .

Макро-подход быстрый и простой. Кроме того, если вам когда-нибудь понадобится переделать сводную таблицу, просто удалите старую и повторно запустите макрос.

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

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

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

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

Этот совет (9812) применим к Microsoft Excel 2007, 2010, 2013, 2016, 2019 и Excel в Office 365.