Kelių darbalapių duomenų sutraukimas

Norėdami sumuoti ir pateikti rezultatus iš atskirų darbalapių, galite sutraukti kiekvieno lapo duomenis į pagrindinį darbalapį. Lapai gali būti toje pačioje darbaknygėje kaip pagrindinis darbalapis arba kitose darbaknygėse. Sutraukdami duomenis, juos surenkate, kad prireikus galėtumėte lengviau juos atnaujinti ir agreguoti.

Pavyzdžiui, jei kiekvienam iš savo regioninių biurų turite išlaidų darbalapį, galite naudoti konsolidavimo funkciją, kad įdėtumėte šiuos duomenis į pagrindinį įmonės išlaidų darbalapį. Šiame pagrindiniame darbalapyje taip pat gali būti visos įmonės pardavimo sumos, dabartiniai inventoriaus dydžiai ir labiausiai parduodami produktai.

Patarimas: Jei dažnai sutrauksite duomenis, gali būti naudinga kurti naujus darbalapius naudojant darbalapio šabloną, kuris naudoja nuoseklų maketą. Norėdami sužinoti daugiau apie šablonus, žr.: Šablono kūrimas. Tai taip pat puikus laikas nustatyti šabloną su „Excel“ lentelėmis.

Yra du duomenų sutraukimo būdai pagal padėtį arba kategoriją.

Konsolidavimas pagal padėtį: šaltinio sričių duomenys išdėstyti ta pačia tvarka ir naudoja tas pačias žymas. Naudokite šį metodą, norėdami konsoliduoti duomenis iš kelių darbalapių, pvz., departamento biudžeto darbalapių, kurie sukurti naudojant tą patį šabloną.

Konsolidavimas pagal kategoriją: Kai duomenys šaltinio srityse nėra išdėstyti ta pačia tvarka, bet naudoja tas pačias žymas. Naudokite šį metodą norėdami konsoliduoti duomenis iš kelių darbalapių, kurie turi skirtingus maketus, bet tas pačias duomenų žymas.

Duomenų konsolidavimas pagal kategoriją veikia panašiai kaip „PivotTable“ sukūrimas. Tačiau naudodami „PivotTable“ galite lengvai pertvarkyti kategorijas. Apsvarstykite galimybę sukurti "PivotTable", jei jums reikia lankstesnio konsolidavimo pagal kategoriją.

Pastaba: Šiame straipsnyje pateikti pavyzdžiai sukurti naudojant Excel 2016. Nors jūsų rodinys gali skirtis, jei naudojate kitą "Excel" versiją, veiksmai yra tokie patys.

Norėdami sujungti kelis darbalapius į pagrindinį darbalapį, atlikite šiuos veiksmus:

Jei to dar nepadarėte, nustatykite duomenis kiekviename sudedamajame lape atlikdami šiuos veiksmus:
- Įsitikinkite, kad kiekvienas duomenų diapazonas yra sąrašo formatu. Kiekvienas stulpelis turi turėti etiketę (antraštę) pirmoje eilutėje ir turėti panašius duomenis. Bet kurioje sąrašo vietoje negali būti tuščių eilučių ar stulpelių.
- Kiekvieną diapazoną padėkite į atskirą darbalapį, bet neįveskite nieko pagrindiniame darbalapyje, kuriame ketinate sutraukti duomenis. "Excel" tai padarys už jus.
- Įsitikinkite, kad kiekvieno diapazono maketas vienodas.
Pagrindiniame darbalapyje spustelėkite viršutinį kairįjį srities, kurioje norite rodyti pagrindinio darbalapio sutrauktus duomenis, langelį.

Pastaba: Norėdami neperrašyti esamų duomenų pagrindiniame darbalapyje, įsitikinkite, kad palikite pakankamai langelių į dešinę ir žemiau šio langelio sutrauktiems duomenims.
Spustelėkite Duomenų>sutraukimas (grupėje Duomenų įrankiai ).
Lauke Funkcija spustelėkite sumavimo funkcija, kurią programa „Excel“ naudos duomenims sutraukti. Numatytoji funkcija yra SUM.

Toliau pateikiamas pavyzdys, kuriame pasirinkti trys darbalapių diapazonai:
Pasirinkite duomenis.

Tada lauke Nuoroda spustelėkite mygtuką Sutraukti , kad sutrauktumėte skydą ir pasirinktumėte duomenis darbalapyje.

Spustelėkite darbalapį, kuriame yra norimi konsoliduoti duomenys, pasirinkite duomenis ir spustelėkite mygtuką Išplėsti dialogo langą, esantį dešinėje, kad grįžtumėte į dialogo langą Sutraukimas.

Jei darbalapis su duomenimis, kuriuos reikia sutraukti, yra kitoje darbaknygėje, spustelėkite Naršyti , kad rastumėte tą darbaknygę. Radus ir spustelėjus Gerai, "Excel" įves failo kelią lauke Nuoroda ir prie to kelio pridės šauktuko. Tada galite toliau pasirinkti kitus duomenis.

Toliau pateikiamas pavyzdys, kuriame pasirinkti trys darbalapio diapazonai:
Iššokančiajame lange Sutraukimas spustelėkite Įtraukti. Kartokite, kad įtrauktumėte visus sutrauktus diapazonus.
Automatinių ir neautomatinių naujinimų skirtumai: Jei norite, kad "Excel" automatiškai atnaujintų sutrauktų lentelę pasikeitus šaltinio duomenims, tiesiog pažymėkite langelį Kurti saitus į šaltinio duomenis . Jei šis langelis nepažymėtas, sutrauktus duomenis galite atnaujinti rankiniu būdu.
Pastabos:
- Negalite kurti saitų, kai šaltinio ir paskirties sritys yra tame pačiame lape.
- Jei norite pakeisti diapazono dydį arba pakeisti diapazoną, spustelėkite diapazoną iššokančiajame lange Sutraukimas ir atnaujinkite atlikdami anksčiau nurodytus veiksmus. Taip sukursite naują diapazono nuorodą, todėl prieš sutraukiant dar kartą turėsite panaikinti ankstesnę. Tiesiog pasirinkite seną nuorodą ir paspauskite klavišą Delete.
Spustelėkite Gerai ir "Excel" sugeneruos sutraukimą. Jei norite, galite taikyti formatavimą. Formatuoti nebūtina tik vieną kartą, nebent iš naujo paleisite sutraukimą.
- Bet kurios etiketės, nesutampančios su kitų šaltinio sričių etiketėmis, sutraukiant įdedamas atskirose eilutėse ar stulpeliuose.
- Įsitikinkite, kad kategorijos, kurių nenorite sutraukti, turi unikalias žymas, rodomas tik viename šaltinio diapazone.

Jei sutraukti duomenys yra skirtinguose skirtingų darbalapių langeliuose:

Įveskite formulę, kurioje yra nuorodos į kitų darbalapių langelius (vienas darbalapis – viena formulė). Pavyzdžiui, norėdami sutraukti darbalapių, pavadintų Pardavimas (langelyje B4), Žmogiškieji ištekliai (langelyje F5) ir Rinkodara (langelyje B9), duomenis pagrindinio darbalapio langelyje A2, turėtumėte įvesti:

„Excel“ formulės per kelis lapus nuoroda

Patarimas: Norėdami įvesti langelio nuorodą, pvz., Pardavimas! B4 – formulėje neįvesdami teksto įveskite formulę iki vietos, kurioje reikia nuorodos, tada spustelėkite darbalapio skirtuką, tada spustelėkite langelį. "Excel" už jus užpildys lapo pavadinimą ir langelio adresą. PASTABA: tokiais atvejais formulės gali būti galimos su klaidomis, nes labai lengva netyčia pasirinkti netinkamą langelį. Taip pat gali būti sunku pastebėti klaidą įvedus sudėtingą formulę.

Jei sutraukti duomenys yra tuose pačiuose skirtingų darbalapių langeliuose:

Įveskite formulę, kurioje yra trimatė nuoroda, naudojanti nuorodą į darbalapių pavadinimų diapazoną. Pavyzdžiui, norėdami imtinai sutraukti langelių Pardavimai ir Rinkodara duomenis pagrindinio darbalapio langelyje E5, turėtumėte įvesti:

„Excel“ 3D lapo nuorodos formulė