Як підвести підсумки з різних аркушів в Excel 2016

Ви можете створити підсумковий аркуш у Excel 2016, який підсумовує або підсумовує значення, збережені в купі інших аркушів у книзі.

Тут ви створюєте підсумкову робочу таблицю під назвою Загальний прогнозований дохід для робочої книги MGE – Прогнозований дохід на 2017 рік. Цей підсумковий робочий лист містить підсумок прогнозованих доходів і витрат для всіх компаній, якими керує Mother Goose Enterprises.

Оскільки робоча книга MGE – Прогнозований дохід на 2017 рік уже містить дев’ять робочих аркушів із прогнозованими доходами та витратами на 2017 рік для кожної з цих компаній, і оскільки всі ці робочі аркуші розміщені в одному порядку, створити цей підсумковий аркуш буде легко:

Новий робочий аркуш вставляється перед іншими аркушами в книзі MGE – 2017 Прогнозований дохід і перейменовують його вкладку аркуша з Sheet1 на Загальний дохід.

Далі введіть назву робочого аркуша Mother Goose Enterprises – Загальний прогнозований дохід на 2017 рік у клітинку A1.

Зробіть це, виділивши клітинку A1, а потім ввівши текст.

Нарешті, скопіюйте решту заголовків рядків для стовпця А (містить опис доходів і витрат) з робочого аркуша «Ctr Diet Ctr» у робочий лист «Загальний дохід».

Для цього виберіть клітинку A3 на аркуші «Загальний дохід», а потім клацніть вкладку «Ctr Diet Sprat». Виберіть діапазон комірок A3:A22 на цьому аркуші; потім натисніть Ctrl+C, знову перейдіть на вкладку Загальний дохід і натисніть Enter.

Тепер ви готові створити головну формулу SUM, яка підсумовує доходи всіх дев’яти компаній у клітинці B3 таблиці загальних доходів:

Почніть, клацнувши клітинку B3 і натиснувши Alt+=, щоб вибрати функцію автосуми.

Потім Excel поміщає =SUM( ) у клітинку з точкою вставки між двома дужками.

Клацніть вкладку аркуша «Спрат Дієта Ctr», а потім клацніть його клітинку В3, щоб вибрати прогнозовані доходи для «Центри дієти Джека Спрата».

Після вибору цієї клітинки рядок формул показує =SUM('Sprat Diet Ctr'!B3).

Далі введіть кому (,) — кома починає новий аргумент. Натисніть вкладку аркуша J&J Trauma Ctr, а потім клацніть його клітинку B3, щоб вибрати прогнозовані доходи для травматологічних центрів Джека і Джил.

Після того, як ви виберете цю клітинку, у рядку формул тепер відображається =SUM('Sprat Diet Ctr'!B3,'J&J Trauma Ctr'!B3).

Продовжуйте таким чином, вводячи кому (щоб почати новий аргумент), а потім вибираючи клітинку B3 із прогнозованими доходами для всіх інших компаній на наступних семи аркушах.

Наприкінці цієї процедури тепер з’явиться рядок формул з величезною формулою SUM, показаною на панелі формул на малюнку.

Щоб заповнити формулу СУМУ в клітинці B3 робочого аркуша Загальний дохід, клацніть поле Enter на рядку формул (ви також можете натиснути Enter на клавіатурі).

На малюнку відзначте результат у клітинці B3. Як ви можете бачити в рядку формул, основна формула SUM, яка повертає 6 681 450,78 до клітинки B3 робочого аркуша «Загальний дохід», отримує результат шляхом підсумовування значень у B3 на всіх дев’яти допоміжних аркушах.

Робочий лист Загальний дохід після того, як я створю формулу SUM для загального прогнозованого доходу для всіх компаній Mother Goose.

Якщо ви хочете виділити одну й ту саму клітинку на кількох аркушах, ви можете натиснути й утримувати клавішу Shift, а потім вибрати останній аркуш. Усі робочі аркуші між першим і останнім будуть включені до вибору або, у цьому випадку, до розрахунку.

Все, що залишилося зробити зараз, це скористатися автозаповненням, щоб скопіювати головну формулу з клітинки B3 до рядка 22, як показано нижче:

Якщо клітинка B3 все ще виділена, перетягніть маркер автозаповнення в нижньому правому куті клітинки B3 до клітинки B22, щоб скопіювати формулу для підсумовування значень для дев’яти компаній у цьому стовпці.

Потім видаліть формули SUM з клітинок B4, B12, B14, B15 і B19 (усі вони містять нулі, оскільки ці клітинки не мають жодних доходів або витрат).

На цьому малюнку ви бачите перший розділ робочого аркуша підсумкової суми загального доходу після копіювання формули, створеної в клітинці B3, і після видалення формул із клітинок, які мають бути порожніми (усі ті, що мають значення 0 у стовпці B).

Робочий аркуш Загальний дохід після того, як я скопіюю формулу SUM та видалю формули, які повертають нульові значення.