Шаг 34.
VBA в MSExcel.
Методы объекта Range, использующие команды Excel. Метод Consolidate

    На этом шаге мы рассмотрим назначение и использование этого метода.

    Метод Consolidate (Консолидация) применяется для объединения данных из нескольких диапазонов в одну итоговую таблицу, которые могут находиться на различных рабочих листах. Этот метод позволяет подвести итоги и обобщить однородные данные, размещенные в нескольких диапазонах. Вручную метод Сonsolidate выполняется с помощью команды Данные | Консолидация (Data | Consolidate).

    Синтаксис:

  Объект.Consolidate (Sources, Function, TopRow, LeftColumn, CreateLinks) 

    Аргументы:

Таблица 1. Аргументы метода Consolidate
Аргумент Назначение
Объект Диапазон, где будет построена итоговая таблица
Sources Массив ссылок в R1C1-формате на диапазоны, по которым строится итоговая таблица. Ссылки должны содержать полные имена диапазонов с указанием имен рабочих листов, на которых они расположены
Function Функция, на основе которой строится итоговая таблица. Допустимые значения:
  • xlAverage (среднее);
  • xlCount (количество значений);
  • xlCountNums (количество чисел);
  • xlMax (максимум);
  • xlMin (минимум);
  • xlProduct (произведение);
  • xlStDev (несмещенная дисперсия);
  • xlStDevP (смещенная дисперсия);
  • xlSum (сумма);
  • xlVar (несмещенное отклонение);
  • xlVarp (смещенное отклонение).
TopRow Допустимые значения: True (консолидация основывается на заголовках столбцов, консолидируемых диапазонов) и False (в противном случае)
LeftColumn Допустимые значения: True (консолидация основывается на заголовках строк, консолидируемых диапазонов) и False (в противном случае).
CreateLinks Допустимые значения: True (консолидируемая таблица связана с исходными) и False (в противном случае)

    Приведем соответствие между аргументами метода Consolidate и выполнением команды Данные | Консолидация (Data | Consolidate) на примере консолидации двух таблиц доходов за 1998 и 1999 годы в одну итоговую таблицу.

  1. Выделим диапазон назначения. В данном случае диапазон A1:D4 (рисунок 1).


    Рис.1. Диалоговое окно диапазонов

        Диапазон A1:D4 является тем объектом, к которому будет применен метод Consolidate.

  2. Выберем команду Данные | Консолидация (Data | Consolidate). | В диалоговом окне Консолидация (Consolidate) (рисунок 2):


    Рис.2. Диалоговое окно Консолидация

    • В поле Ссылка (Reference) указывается исходный диапазон '[Книга1]1998' !$A$1:$D$4. Нажав кнопку Добавить (Add) диапазон из поля Ссылка (Reference) вставляется в Список диапазонов (All References). После чего в поле Ссылка (Reference) указывается второй исходный диапазон '[Книга2]1999' !$A$1:$D$4. Нажав кнопку Добавить (Add) диапазон из поля Ссылка (Reference) вставляется в Список диапазонов (All References). Всего в Список диапазонов (All References) можно ввести до 255 диапазонов.
    • В раскрывающемся списке Функция (Function) выбирается тип консолидации. В данном случае выберем Сумма.
    • Если консолидация происходит по расположению, то можно снять флажки Подписи верхней строки (Top Row) и Значения левого столбца (Left Column). Эти флажки должны быть установлены, если консолидация происходит согласно заголовкам строк и столбцов. В данном случае установим эти два флажка.
    • Флажок Создавать связи с исходными данными (Create Links to Source Data) позволяет установить связь между исходными данными и итоговым диапазоном так, что результаты будут обновляться при изменении данных. В данном случае сбросим этот флажок.
  3. Нажатие кнопки ОК приводит к построению итоговой таблицы (рисунок 3).


    Рис.3. Результат консолидации

        В методе Consolidate за исходные диапазоны отвечает аргумент Sources, за способ консолидации данных - аргумент Function, за установку флажков Подписи верхней строки (Top Row), Значения левого столбца (Left Column) и Создавать связи с исходными данными (Create Links to Source Data) - аргументы TopRow, LeftColumn и CreateLinks. Подытожим все присвоения значений аргументов для рассмотренного примера:

      Range ("A1:D4").Consolidate_
        Sources:=Array("'[Книга1]1998'!R1C1:R4C4", _
             "'[Книга2]1999'!R1C1:R4C4") , _
        Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=False
    

    На следующем шаге мы рассмотрим метод Find.




Предыдущий шаг Содержание Следующий шаг