У Ом есть сотни листов с разными именами в одной книге. Структура каждого рабочего листа по сути одинакова. Он хочет скопировать фиксированный диапазон (A146: O146) из каждого листа в новый лист, одну строку за другой.
Есть несколько способов сделать это. На мои деньги простой макрос – лучший выбор. Однако прежде чем перейти к подходу на основе макросов, есть способ сделать это с помощью формул. Один довольно уникальный способ основан на определении формулы в диспетчере имен. Для запуска выполните следующие действия:
- Отобразите вкладку «Формулы» на ленте.
- В группе «Определенные имена» щелкните инструмент «Диспетчер имен». Excel отображает диалоговое окно «Диспетчер имен».
- Нажмите кнопку «Создать». Excel отображает диалоговое окно «Новое имя». (См. Рис. 1.)
- В поле «Имя» введите имя ListSheets (обратите внимание, что это одно слово с без пробелов).
- В поле “Ссылается на” введите следующую формулу:
- Нажмите кнопку ОК, чтобы завершить создание именованного диапазона. Новый диапазон должен появиться в диалоговом окне «Диспетчер имен».
- Нажмите кнопку «Закрыть», чтобы закрыть диалоговое окно «Диспетчер имен».
Рис. 1. Диалоговое окно «Новое имя».
= REPLACE (GET.WORKBOOK (1), 1, FIND ("]" , GET.WORKBOOK (1), 1), "")
С определенной формулой таким образом вы можете затем перейти к рабочему листу, на котором вы хотите объединить все эти диапазоны. (Допустим, этот рабочий лист называется «Сводка».) Я настоятельно рекомендую убедиться, что этот рабочий лист является самым последним в вашей книге. На листе сводки поместите следующую формулу в столбец A любой строки:
= INDIRECT (INDEX (ListSheets, ROWS ($ A $ 1: $ A1)) & "!" & CELL ("адрес ", A $ 146))
Скопируйте формулу вниз, сколько бы строк ни потребовалось для представления всех листов. Другими словами, если у вас есть 25 листов (не считая итогового листа), скопируйте формулу на 24 строки вниз. Подсчитав оригинал, теперь у вас должна появиться формула в общей сложности в 25 строках.
Примечание: формула ListSheets – та, которую вы определили в диспетчере имен – возвращает массив рабочего листа имена. Функция ROWS используется для определения того, какой элемент этого массива возвращается через функцию INDEX. Если ваш итоговый рабочий лист не последний в вашей книге, он может быть легко возвращен ListSheets, и вы в конечном итоге получите из него значения. Вы, несомненно, не хотите этого делать, поэтому я предложил убедиться, что Summary был последним листом в книге.
Теперь просто скопируйте формулы из столбца A вправо, чтобы у вас есть они на всем протяжении столбца O. В результате вы будете иметь значения из A146: O146 в ячейках, содержащих формулы.
Ранее я сказал, что считаю подход на основе макросов лучший выбор. Вот небольшой небольшой макрос, который демонстрирует, почему это так.
Sub CopyRange () Dim w As Worksheet Dim sRange As String Dim lRow As Long sNewName = "Summary" 'Имя сводного рабочего листа sRange = "A146: O146" 'Диапазон для копирования из каждого рабочего листа Рабочие листы (1). Выберите рабочие листы. Добавить ActiveSheet.Name = sNewName lRow = 2 For Each w In Worksheets If w.Name sNewName Then' Закомментируйте следующую строку, если вы не хотите 'включать имена рабочих листов в сводный лист Cells (lRow, 1) = w.Name' Если вы закомментировали предыдущую строку, внесите изменения 'в следующую строку: change (lRow, 2) to (lRow , 1) w.Range (sRange) .Copy Cells (lRow, 2) lRow = lRow + 1 End If Next wEnd Sub
Обратите внимание, что установлены две переменные (sNewName и sRange) в начале макроса. Они представляют имя, которое вы хотите использовать для нового итогового рабочего листа, созданного макросом, и диапазон ячеек, который вы хотите скопировать с каждого рабочего листа.
Затем макрос делает первый рабочий лист в книге активным и добавляет новый рабочий лист, который будет использоваться для обобщения. Этому листу назначается имя, указанное вами в переменной sNewName. Затем макрос проходит цикл, проверяя каждый из других листов. Пока рабочий лист не является сводным, имя рабочего листа помещается в столбец A сводного листа, а диапазон, указанный в переменной sRange (A146: O146), копируется в сводный рабочий лист, начиная со столбца B. .
Макро-подход быстр и прост. Кроме того, если вам когда-нибудь понадобится переделать сводную таблицу, просто удалите старую и повторно запустите макрос.