Консолидиране на няколко работни листа в една обобщена таблица в Excel

За да обобщите и съобщите резултати от различни диапазони на работни листове, можете да консолидирате данните от всеки отделен диапазон на работен лист в обобщена таблица на главен работен лист. Отделните диапазони на работни листове могат да са в същата работна книга като главния работен лист или в друга работна книга. Когато консолидирате данни, вие ги събирате, за да можете по-лесно да ги актуализирате и обобщавате редовно или при необходимост.

Полученият консолидиран отчет с обобщена таблица може да има следните полета в списъка с полета на обобщена таблица, които можете да добавите към обобщената таблица: "Ред", "Колона" и "Стойност". Освен това отчетът може да има до четири полета за филтриране на страници, наречени "Страница1", "Страница2", "Страница3" и "Страница4".

Всеки диапазон от данни трябва да бъде подреден в кръстосан формат със съответстващи имена на колоните и редовете за елементите, които искате да обобщите заедно. Не включвайте редове или колони за общи суми от изходните данни, когато задавате данните за отчета. Примерът по-долу показва четири диапазона в кръстосан формат.

Консолидирането на данни може да използва полета за страници, които съдържат елементи, представляващи един или няколко от изходните диапазони. Ако например консолидирате данни за бюджета от отделите по маркетинг, продажби и производство, едно поле за страница може да включва един елемент за показване на данните за всеки отдел, плюс един елемент за показване на комбинираните данни. Примерът по-долу показва консолидирана обобщена таблица, в която са избрани едно поле за страница и няколко елемента.

Ако е вероятно диапазонът от данни да се промени следващия път, когато консолидирате данните (т.е. броят на редовете може да се промени), обмислете дали да не дефинирате име за всеки изходен диапазон в отделните работни листове. След това използвайте тези имена, когато консолидирате диапазоните в главния работен лист. Ако изходен диапазон се разшири, можете да актуализирате диапазона за името в отделния работен лист, за да включите новите данни, преди да обновите обобщената таблица.

Excel предоставя и други начини за консолидиране на данни, които работят при данни в различни формати и оформления. Можете например да създадете формули с 3D препратки или да използвате командата Консолидиране (в раздела Данни, в групата Инструменти за данни).

Можете да използвате съветника за обобщена таблица и обобщена диаграма, за да консолидирате няколко диапазона. В съветника можете да изберете да не използвате полета за страници, да използвате едно поле за страница или да използвате няколко полета за страници.

За да комбинирате данните от всички диапазони и да създадете консолидиране, което няма полета за страници, направете следното:

1. Добавете съветника за обобщена таблица и обобщена диаграма към лентата с инструменти за бърз достъп. За да направите това:

   1. Изберете стрелката до лентата с инструменти и след това изберете Още команди.

   2. Изберете Персонализиране на лентата с инструменти за бърз достъп (...) в долния ляв ъгъл под лентата и след това изберете Още команди.

   3. Под Избирай команди от изберете Всички команди.

   4. В списъка изберете Съветник за обобщена таблица и обобщена диаграма, изберете Добавяне и след това изберете OK.

2. Изберете празна клетка (която не е част от обобщена таблица) в работната книга.

3. Изберете в иконата на съветника за обобщена таблица и обобщена диаграма в лентата с инструменти за бърз достъп.

4. На страницата Стъпка 1 на съветника изберете Множество диапазони за консолидация и след това изберете Напред.

5. На страницата Стъпка 2a на съветника изберете Ще създам полетата за страници и след това изберете Напред.

6. В страницата Стъпка 2b на съветника направете следното:

   1. Върнете се в работната книга и изберете диапазона от клетки, а след това отново в съветника за обобщена таблица и обобщена диаграма изберете Добави.

      Съвет: Ако диапазонът от клетки е в друга работна книга, първо отворете работната книга, за да улесните избирането на данните.

   2. Под Колко полета за страници искате? изберете 0 и след това изберете Напред.

7. На страницата Стъпка 3 на съветника изберете между добавяне на обобщената таблица към нов или съществуващ работен лист и след това изберете Готово.

За да включите едно поле за страница, което има по един елемент за всеки изходен диапазон плюс един елемент, който консолидира всички диапазони, направете следното:

1. Добавете съветника за обобщена таблица и обобщена диаграма към лентата с инструменти за бърз достъп. За да направите това:

   1. Изберете стрелката до лентата с инструменти и след това изберете Още команди.

   2. Изберете Персонализиране на лентата с инструменти за бърз достъп (...) в долния ляв ъгъл под лентата и след това изберете Още команди.

   3. Под Избирай команди от изберете Всички команди.

   4. В списъка изберете Съветник за обобщена таблица и обобщена диаграма, изберете Добавяне и след това изберете OK.

2. Изберете празна клетка (която не е част от обобщена таблица) в работната книга .

3. На страницата Стъпка 1 на съветника изберете Множество диапазони за консолидация и след това изберете Напред.

4. На страницата Стъпка 2a на съветника изберете Създай едно поле за страница вместо мен и след това изберете Напред.

5. В страницата Стъпка 2b на съветника направете следното:

   1. Върнете се в работната книга и изберете диапазона от клетки, а след това отново в съветника за обобщена таблица и обобщена диаграма изберете Добави.

      Съвет: Ако диапазонът от клетки е в друга работна книга, първо отворете работната книга, за да улесните избирането на данните.

6. Изберете Напред.

7. На страницата Стъпка 3 на съветника изберете между добавяне на обобщената таблица към нов или съществуващ работен лист и след това изберете Готово.

Можете да създадете няколко полета за страници и да присвоите собствени имена на елементите за всеки изходен диапазон. Това ви позволява да създадете частични или пълни консолидации; например едно поле за страница, което консолидира "Маркетинг" и "Продажби" отделно от "Производство", и друго поле за страница, което консолидира и трите отдела. За да създадете консолидация, която използва няколко полета за страници, направете следното:

1. Добавете съветника за обобщена таблица и обобщена диаграма към лентата с инструменти за бърз достъп. За да направите това:

   1. Изберете стрелката до лентата с инструменти и след това изберете Още команди.

   2. Изберете Персонализиране на лентата с инструменти за бърз достъп (...) в долния ляв ъгъл под лентата и след това изберете Още команди.

   3. Под Избирай команди от изберете Всички команди.

   4. В списъка изберете Съветник за обобщена таблица и обобщена диаграма, изберете Добавяне и след това изберете OK.

2. Изберете празна клетка (която не е част от обобщена таблица) в работната книга.

3. На страницата Стъпка 1 на съветника изберете Множество диапазони за консолидация и след това изберете Напред.

4. На страницата Стъпка 2a на съветника изберете Ще създам полетата за страници и след това изберете Напред.

5. В страницата Стъпка 2b на съветника направете следното:

   1. Върнете се в работната книга и изберете диапазона от клетки, а след това отново в съветника за обобщена таблица и обобщена диаграма изберете Добави.

      Съвет: Ако диапазонът от клетки е в друга работна книга, първо отворете работната книга, за да улесните избирането на данните.

   2. Под Колко полета за страници желаете? изберете броя на полетата за страници, които искате да използвате.

   3. Под Какви етикети искате да използва всяко поле за страница, за да идентифицира избрания диапазон от данни? изберете диапазона от клетки за всяко поле за страница и след това въведете име за диапазона.

      Пример

      • Ако сте избрали 1 под Колко полета за страници желаете?, изберете всеки диапазон и след това въведете различно име в полето Първо поле. Ако имате четири диапазона и всеки един от тях съответства на различно тримесечие от финансовата година, изберете първия диапазон, въведете "Тримесечие1", изберете втория диапазон, въведете "Тримесечие2" и повторете процеса за Тримесечие3 и Тримесечие4.

      • Ако сте избрали 2 под Колко полета за страници желаете?, направете същото като в предишния пример в полето Първо поле. След това изберете два диапазона и въведете същото име, като например H1 и H2, в полето Второ поле. Изберете първия диапазон, въведете H1, изберете втория диапазон, въведете H1, изберете третия диапазон, въведете H2, изберете четвъртия диапазон и въведете H2.

   4. Изберете Напред.

6. На страницата Стъпка 3 на съветника изберете между добавяне на обобщената таблица към нов или съществуващ работен лист и след това изберете Готово.