Lai apkopotu un sniegtu pārskatu par rezultātiem no atsevišķām darblapām, varat konsolidēt datus no katras lapas šablona darblapā. Lapas var atrasties tajā pašā darbgrāmatā, kur atrodas galvenā darblapa, vai citās darbgrāmatās. Kad konsolidējat datus, tie tiek apvienoti, lai nepieciešamības gadījumā tos varētu vieglāk atjaunināt un apkopot.
Piemēram, katras reģionālās nodaļas izdevumu darblapa var izmantot konsolidāciju, lai apvienotu šos skaitļus uzņēmuma galveno izdevumu darblapā. Šādā šablona darblapā var būt arī kopējais apgrozījums, vidējie rādītāji, pašreizējā inventāra saraksts un vislabāk pārdotie uzņēmuma produkti.
Padoms
Ja bieži konsolidējat datus, ieteicams izveidot jaunas darblapas no darblapas veidnes ar nemainīgu izkārtojumu. Papildinformāciju par veidnēm skatiet rakstā: Veidnes izveide. Šis ir arī piemērots brīdis veidnes iestatīšanai, izmantojot Excel tabulas.
Datu konsolidēšanas veidi
Ir divi veidi, kā konsolidēt datus — pēc novietojuma vai kategorijas.
Konsolidācija pēc pozīcijas: avota apgabalu dati ir tādā pašā secībā un izmanto tās pašas etiķetes. Izmantojiet šo metodi, lai konsolidētu datus no vairāku darblapu sērijas, piemēram, no nodaļas budžeta darblapām, kas izveidotas no vienas veidnes.
Konsolidācija pēc kategorijas: ja avota apgabalu dati nav sakārtoti vienā secībā, bet izmanto vienas un tās pašas etiķetes. Izmantojiet šo metodi, lai konsolidētu datus no tādu darblapu sērijas, kurām ir atšķirīgi izkārtojumi, bet vienādas datu etiķetes.
- Datu konsolidēšana pēc kategorijas ir līdzīga rakurstabulas izveidei. Tomēr, izmantojot rakurstabulu, var viegli pārkārtot kategorijas. Apsveriet iespēju izveidot rakurstabulu, ja jums ir nepieciešama elastīgāka konsolidācija pa kategorijām.
Piezīme
Šajā rakstā minētie piemēri ir izveidoti, izmantojot Excel 2016. Lai gan jūsu skats var atšķirties, ja izmantojat citu Excel versiju, darbības ir tādas pašas.
Kā konsolidēt
Lai konsolidētu vairākas darblapas šablona darblapā, rīkojieties šādi:
Ja vēl neesat to izdarījis, iestatiet datus katrā komponenta lapā, rīkojoties šādi:
- Pārliecinieties, vai visi datu diapazoni ir saraksta formātā. Katras kolonnas pirmajā rindā jābūt etiķetei (galvenei), kā arī jāsatur līdzīgi dati. Sarakstā nedrīkst būt tukšu rindu vai kolonnu.
- Novietojiet katru diapazonu atsevišķā darblapā, bet neievadiet neko šablona darblapā, kurā plānojat konsolidēt datus. Excel to paveiks jūsu vietā.
- Pārliecinieties, vai visiem diapazoniem ir vienāds izkārtojums.
Šablona darblapā noklikšķiniet konsolidēto datu parādīšanas apgabala kreisajā augšējā šūnā.
Piezīme
Lai šablona darblapā netiktu pārrakstīti esošie dati, pārliecinieties, vai pa labi un uz leju no šīs šūnas ir pietiekami daudz šūnu konsolidēto datu ievietošanai.
Noklikšķiniet uz Datu>konsolidēšana (grupā Datu rīki ).
Lodziņā Funkcija noklikšķiniet uz kopsavilkuma funkcijas, kas programmai Excel jāizmanto datu konsolidēšanai. Noklusējuma funkcija ir SUM.
Lūk, piemērs, kurā ir izvēlēti trīs darblapu diapazoni:
Atlasiet datus.
Pēc tam lodziņā Atsauce noklikšķiniet uz pogas Sakļaut , lai samazinātu paneli un darblapā atlasītu datus.
Noklikšķiniet uz darblapas, kurā ir konsolidējamie dati, atlasiet tos un pēc tam noklikšķiniet uz pogas Izvērst dialogu labajā pusē, lai atgrieztos konsolidēšanas dialogā.Ja darblapa, kurā ir konsolidējamie dati, ir citā darbgrāmatā, noklikšķiniet uz Pārlūkot , lai atrastu šo darbgrāmatu. Pēc atrašanās vietas noteikšanas un noklikšķināšanas uz Labi, Excel lodziņā Atsauce ievadīs faila ceļu un pievienos šim ceļam izsaukuma zīmi. Varat turpināt atlasīt citus datus.
Lūk, piemērs, kurā ir izvēlēti trīs darblapu diapazoni:
Konsolidēšanas uznirstošajā logā noklikšķiniet uz Pievienot. Atkārtojiet šo darbību, lai pievienotu visus konsolidētos diapazonus.
Automātiskie un manuālie atjauninājumi: Ja vēlaties, lai Excel automātiski atjauninātu jūsu konsolidēšanas tabulu, mainoties avota datiem, vienkārši atzīmējiet izvēles rūtiņu Izveidot saites uz avota datiem . Ja šī rūtiņa paliek neatzīmēta, varat atjaunināt konsolidāciju manuāli.
Piezīme
- Saites nevar izveidot, ja avota un mērķa apgabali atrodas vienā lapā.
- Ja vēlaties mainīt diapazona lielumu vai aizstāt diapazonu, noklikšķiniet uz diapazona konsolidēšanas uznirstošajā lodziņā un atjauniniet to, izmantojot iepriekš norādītās darbības. Tādējādi tiks izveidota jauna diapazona atsauce, tāpēc pirms atkārtotas konsolidēšanas būs nepieciešams izdzēst iepriekšējo. Vienkārši izvēlieties veco atsauci un nospiediet taustiņu Dzēst.
Noklikšķiniet uz Labi, un Excel ģenerēs konsolidāciju jūsu vietā. Papildus varat lietot formatējumu. Formatēšana ir jāveic tikai vienreiz, ja vien konsolidācija netiek palaista atkārtoti.
- Cita avota apgabala etiķetēm neatbilstošās etiķetes konsolidācijā veido atsevišķas rindas vai kolonnas.
- Pārliecinieties, vai visām kategorijām, kuras nav jākonsolidē, ir unikālas etiķetes, kuras parādās tikai vienā avota diapazonā.
Formulas izmantošana datu konsolidācijai
Ja konsolidējamie dati ir dažādās darblapās dažādās šūnās:
Katrai darblapai ievadiet formulu ar šūnu atsaucēm uz citām darblapām. Piemēram, lai konsolidētu datus no darblapas Apgrozījums (šūnā B4), Stundas (šūnā F5) un Mārketings (šūnā B9) šablona darblapas šūnā A2, ievadiet šādu formulu:
Padoms
Lai ievadītu šūnas atsauci, piemēram, Pārdošana! B4— formulā bez rakstīšanas rakstiet formulu līdz vietai, kur nepieciešama atsauce, pēc tam noklikšķiniet uz darblapas cilnes un pēc tam noklikšķiniet uz šūnas. Excel aizpildīs lapas nosaukumu un šūnas adresi jūsu vietā. PIEZĪME: formulas šādos gadījumos var būt kļūdas, jo ir ļoti viegli nejauši atlasīt nepareizu šūnu. Arī pēc sarežģītas formulas ievadīšanas var būt grūti pamanīt kļūdu.
Ja konsolidējamie dati dažādās darblapās ir vienās un tajās pašās šūnās:
Ievadiet formulu, kurā iekļautas telpiskās atsauces uz vairāku darblapu nosaukumu diapazonu. Piemēram, lai konsolidētu datus šūnās A2, sākot ar darblapu Apgrozījums līdz darblapai Mārketings, šablona darblapas šūnā E5 ievadiet šādu formulu:
Vai nepieciešama papildu palīdzība?
Vienmēr varat pajautāt speciālistam Excel tehnoloģiju kopienā vai saņemt atbalstu kopienās.
Skatiet arī
Pārskats par formulām programmā Excel
Kā nepieļaut kļūdainas formulas
Kļūdu atrašana un izlabošana formulās
Excel īsinājumtaustiņi un funkciju taustiņi