ifempty

    Урок 7. Консолидация рабочих листов

    Оглавление

    Введение

    Пример решения задачи №1

    Способ 1 - суммирование путем последовательного перебора листов

    Способ 2 - консолидация листов с помощью трехмерных формул

    Способ 3 - консолидация листов с помощью инструмента консолидации

    Пример решения задачи №2

    Файлы для скачивания

     

    Введение

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

    Можно выделить три основных способа консолидации листов:

    1. Путем последовательного перебора листов при написании обычной формулы;
    2. С помощью трехмерных формул;
    3. С помощью инструмента консолидации.

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

     

     Пример решения задачи №1

    Скачайте и откройте файл Консолидация_приложение1.xlsx  по ссылке ниже. 

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

     консолидация рис1

     Рисунок 1. Разрешение редактирования файла

    Файл состоит из 5 листов. На листах филиал 1, филиал 2 и филиал 3 находятся списки отделов с количеством сотрудников и суммами ЗП. Структура таблиц и их положение на листе одинаковые. Содержимое таблиц отличается. На листе Сборка (без решения) находятся таблицы, в которые нужно путем суммирования объединить данные трех филиаловВ Таблице1 нужно данные консолидировать первым способом: суммировать ячейки последовательным перебором листов, в Таблице2 нужно консолидаровать данные вторым способом: с помощью трехмерной формулы, и в таблице3 нужно консолидаровать данные с помощью инструмента консолидации. 

     

    Способ 1 - суммирование путем последовательного перебора листов

    В скачанном файле на листе Сборка (без решения) встаньте на ячейку B3 и поставьте знак равно  = , как показано на рисунке 2:

     консолидация рис2

    Рисунок 2. Начало формулы сложения

    Далее перейдите на лист филиал1 и выберите ячейку B2, поставьте знак плюс +  и затем также на листах филиал2 и филиал3 выберите и суммируйте ячейки B2. У вас должна получиться такая формула:

    ='филиал 1'!B2+'филиал 2'!B2+'филиал 3'!B2

    на листе это будет выглядеть как на рисунке 3:

     консолидация рис3

    Рисунок 3. Формула сложения ячеек из разных листов

    Далее с помощью маркера автозаполнения протяните формулу вниз до ячейки B9 включительно и на столбец вправо, чтобы заполнить графу сумма ЗП. У вас должно получиться  как на рисунке 4.

     консолидация рис4

    Рисунок 4. Консолидация листов первым способом

     

    Способ 2 - консолидация листов с помощью трехмерных формул

    В скачанном файле на листе Сборка (без решения) в ячейке B14 вызовите функцию суммирования, для этого встаньте на ячейку В14, перейдите на вкладку Формулы и нажмите на кнопку Автосумма. Внутри скобок функции СУММ удалите ссылки, как показано на рисунке 5:

     консолидация рис5

    Рисунок 5. Настройка трехмерной формулы суммирования (шаг 1)

    Далее перейдите на лист филиал1 и выберите ячейку B2, затем, удерживая клавишу Shift мышкой выберите лист филиал3 и нажмите клавишу Enter. Обратите внимание, что при этом лист филиал3 не будет открыт, но формула станет трехмерной - то есть будет охватывать диапазон листов. Формула будет выглядеть так:

    =СУММ('филиал 1:филиал 3'!B2)

    На листе Сборка (без решения) в ячейке B14 у вас должен быть результат как на рисунке 6:

     консолидация рис6

    Рисунок 6. Трехмерная формула суммирования

    Далее нужно с помощью маркера автозаполнения протянуть формулу вниз до ячейки В20 включительно и затем протянуть на столбец вправо, чтобы заполнить графу сумма ЗП. У вас должно получиться как на рисунке 7.

     консолидация рис7

    Рисунок 7. Консолидация листов вторым способом

     

    Способ 3 - консолидация листов с помощью инструмента консолидации

    В скачанном файле на листе Сборка (без решения) встаньте на ячейку Е2, перейдите на вкладку Данные и нажмите на кнопку Консолидация, как показано на рисунке 8.

     консолидация рис8

    Рисунок 8. Вкладка Данные - кнопка открытия окна инструмента консолидации

    У вас откроется окно Консолидация, где поле Функция оставляем без изменений, так как нам нужно суммировать значения. Перемещаемся в поле ссылка и выбираем таблицу на листе филиал1. Таблицу нужно выбрать полностью вместе с заголовками, как показано на рисунке 10. После чего нужно нажать на кнопку Добавить.

     консолидация рис10

     Рисунок 10. Окно с настройками инструмента консолидации листов

    После того, как вы нажали на кнопку Добавить, ссылка переместиться в поле Список диапазонов, как показано на рисунке 11:

     консолидация рис11

    Рисунок 11. Окно с настройками инструмента консолидации листов

    Далее нужно аналогично перейти на лист филиал2, выделить таблицу целиком вместе с заголовками и нажать на кнопку Добавить. И также сделать на листе филиал3. Посе этого установить активными флаги Подписи верхней строки и Значение левого столбца. В итоге у вас должно получиться как на рисунке 12.

     консолидация рис12

     Рисунок 12. Окно с настройками инструмента консолидации листов 

    Далее нажмите кнопку ОК. В результате Таблица3 должна заполниться сводными данными как на рисунке 13.

     консолидация рис13

    Рисунок 13. Результат вызова инструмента консолидации

     

    Пример решения задачи №2

    Скачайте и отктройте файл Консолидация_Приложение2.xlsx. Если у вас стоит защищенный просмотр, то нажмите на кнопку Разрешить редактирование, как показано на рисунке 1. Файл состоит из 5 листов. На листах Головной офис, Отделение 1 и Отделение 2 находятся списки сотрудников, где среди прочего указаны отделы, в которых они работают и их оклады. На листе Консолидация (без решения) нужно определить среднюю заработную плату в разрезе отделов по группе компаний. 

    Так как количество строк на листах разное, то тут нужно использовать инструмент консолидации. Для этого на листе  Консолидация (без решения) становимся на ячейку А1, переходим на вкладку Данные и нажимаем на кнопку Консолидация, как показано на рисунке 14.

     консолидация рис14

    Рисунок 14. Вкладка Данные - кнопка открытия окна инструмента консолидации

    У вас отктроется окно с настройками консолидации. В поле фукнция нужно указать Среднее, так как по сусловию задачи нужно определить среднюю заработную плату. Далее становимся в поле Ссылка, переходим на лист Головной офис и выбираем диапазон ячеек начиная от ячейки G6 и до конца таблицы до ячейки N56 включительно, как показано на рисунке 15.

     консолидация рис15

     Рисунок 15.  Настройка инструмента консолидации 

    Далее нажимаем кнопку Добавить, при этом диапазон в поле Ссылка переместиться в поле Список диапазонов, как показано на рисунке 16.

     консолидация рис16

     Рисунок 16. Окно настройки инструмента консолидации

    Далее аналогично нужно перейти на лист Отделение1, выбрать диапазон ячеек от отделов начиная и до конца таблицы и нажать на кнопку Добавить. И также сделать на листе Отделение 2. Затем установить активными флаги в полях: Подписи левой строки и Значение левого столбца. В итоге окно настройки консолидации должно выглядеть так:

     консолидация рис17

    Рисунок 17. Окно настройки инструмента консолидации

    Нажмите кнопку ОК. У вас должно получиться как на рисунке 18.

     консолидация рис18

    Рисунок 18. Результат использования инструмента консолидации

    Так как по условию задачи нам нужно определить среднюю заработную плату в разрезе отделов (по каждому отделу), то получается, что столбцы от В до G являются лишними. Удаляем эти столбцы и в резльтате получается так:

     консолидация рис19

    Рисунок 19. Итоговая таблица: данные о среднем окладе по отделам

     

    Файлы для скачивания:

     

     

    Зарегистрируйтесь и получите пробный однодневный доступ к полным материалам курса!  В него входит: учебник, видеоурок, практика, тестирование. После входа в систему вам будет доступен личный кабинет с курсами.

    Войти с помощью: 


    Пробная подписка 1 День

    Пробная подписка на 1 день

    Вы можете активировать только 1 раз.


    MS Excel Basic Навсегда


    MS Excel Intermediate Навсегда

    Курс Basic получаете в подарок


    MS Excel Advanced Навсегда

    Курсы Basic и Intermediate получаете в подарок.