Kā konsolidēt darblapas programmā Excel 2016

Programma Excel 2016 ļauj konsolidēt datus no dažādām darblapām vienā darblapā. Izmantojot programmas Consolidate komandu pogu lentes cilnē Dati, varat viegli apvienot datus no vairākām izklājlapām.

Piemēram, varat izmantot komandu Konsolidēt, lai apkopotu visas katra uzņēmuma nodaļas sagatavotās budžeta izklājlapas vai izveidotu kopsavilkuma summas ienākumu deklarācijām par vairākiem gadiem. Ja izmantojāt veidni, lai izveidotu katru konsolidējamo darblapu vai identisku izkārtojumu, programma Excel var ātri konsolidēt vērtības, pamatojoties uz to kopējo pozīciju attiecīgajās darblapās. Tomēr pat tad, ja datu ieraksti katrā izklājlapā ir izkārtoti atšķirīgi, programma Excel joprojām var tos konsolidēt, ja esat izmantojis tās pašas etiķetes, lai aprakstītu datu ierakstus attiecīgajās darblapās.

Lielāko daļu laika vēlaties apkopot datus, ko konsolidējat no dažādām darblapām. Pēc noklusējuma programmā Excel tiek izmantota funkcija SUM, lai summētu visas šūnas darblapās, kurām ir vienas un tās pašas šūnu atsauces (ja konsolidējat pēc pozīcijas) vai kuras izmanto tās pašas etiķetes (ja konsolidējat pēc kategorijas). Tomēr, veicot konsolidāciju, programma Excel var izmantot jebkuru no tālāk norādītajām statistikas funkcijām: VIDĒJAIS, SKAITS, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, VAR vai VARP.

Lai sāktu lapu konsolidāciju tajā pašā darbgrāmatā, atlasiet jaunu darblapu, kurā tiks glabāti konsolidētie dati. (Ja nepieciešams, ievietojiet darbgrāmatā jaunu lapu, noklikšķinot uz pogas Ievietot darblapu.) Lai sāktu lapu konsolidāciju dažādās darbgrāmatās, atveriet jaunu darbgrāmatu. Ja dažādās darbgrāmatās esošās lapas ir ģenerētas no veidnes, atveriet jauno darbgrāmatu konsolidētajiem datiem no šīs veidnes.

Pirms sākat konsolidācijas procesu jaunajā darblapā, šajā darblapā izvēlieties šūnu vai šūnu diapazonu, kurā jāparādās konsolidētajiem datiem. (Šo diapazonu sauc par mērķa apgabalu. ) Ja atlasāt vienu šūnu, programma Excel paplašina galamērķa apgabalu līdz kolonnām pa labi un rindām zemāk, lai pielāgotu konsolidētos datus. Ja atlasāt vienu rindu, programma paplašina mērķa apgabalu uz leju nākamajās darblapas rindās, ja tas ir nepieciešams, lai pielāgotu datus. Ja atlasāt vienu kolonnu, programma Excel paplašina galamērķa apgabalu pāri kolonnām pa labi, ja nepieciešams, lai pielāgotu datus. Ja tomēr izvēlaties vairāku šūnu diapazonu kā mērķa apgabalu, programma nepaplašina mērķa apgabalu un ierobežo konsolidētos datus tikai šūnu atlasei.

Ja vēlaties, lai programma Excel izmantotu noteiktu diapazonu darblapā visām konsolidācijām, ko veicat darblapā, šim šūnu diapazonam piešķiriet diapazona nosaukumu Consolidate_Area. Pēc tam Excel konsolidē datus šajā diapazonā ikreiz, kad izmantojat komandu Konsolidēt.

Konsolidējot datus, varat atlasīt datus darbgrāmatu lapās, kuras esat atvēris programmā Excel, vai lapās neatvērtās darbgrāmatās, kas saglabātas diskā. Šūnas, kuras norādāt konsolidācijai, tiek sauktas par avota apgabalu, un darblapas, kurās ir ietverti avota apgabali, tiek sauktas par avota darblapām.

Ja avota darblapas ir atvērtas programmā Excel, varat norādīt atsauces uz avota apgabaliem, norādot uz šūnu atsaucēm (pat ja ir atvērts dialoglodziņš Konsolidēt, programma Excel ļaus jums aktivizēt dažādas darblapas un ritināt tās, atlasot šūnu atsauces uz avota apgabalu). Ja avota darblapas nav atvērtas programmā Excel, šūnu atsauces ir jāievada kā ārējās atsauces, ievērojot tās pašas vadlīnijas, kuras izmantojat, ierakstot saistīšanas formulu ar ārēju atsauci (izņemot to, ka nerakstiet =). Piemēram, lai norādītu datus diapazonā B4:R21 lapā 1 darbgrāmatā ar nosaukumu CG Music — 2014 Sales.xlsx kā avota apgabalu, ievadiet šādu ārējo atsauci:

'[CG Music — 2014 Sales.xlsx]Sheet1'!$b$4:$r$21

Ņemiet vērā: ja vēlaties konsolidēt vienu un to pašu datu diapazonu visās darblapās, kurās tiek izmantots līdzīgs faila nosaukums (piemēram, CG Music — 2012. gada pārdošana, CG mūzika — 2013. gada pārdošana, CG mūzika — 2014. gada pārdošana utt.), varat izmantot zvaigznīte (*) vai jautājuma zīme (?) kā aizstājējzīmes, lai apzīmētu trūkstošās rakstzīmes, kā

'[CG mūzika — 20?? Sales.xlsx]Sheet1'!$B$4:$R$21

Šajā piemērā programma Excel konsolidē diapazonu A2:R21 lapā 1 visās darbgrāmatu versijās, kurās galvenajā failā tiek izmantots “CG — mūzika — 20”, ja šim nosaukumam seko vēl divas rakstzīmes (vai tās ir 12, 13, 14, 15 un tā tālāk).

Konsolidējot datus, programma Excel izmanto tikai tās šūnas avota apgabalos, kurās ir vērtības. Ja šūnās ir formulas, programma Excel izmanto to aprēķinātās vērtības, bet, ja šūnās ir teksts, programma Excel tās ignorē un apstrādā tās tā, it kā tās būtu tukšas (izņemot kategoriju etiķetes, kad konsolidējat datus pēc kategorijas).