Šioje pamokos dalyje išsamiai aprašoma, kaip „Excel“ sukurti „PivotTable“. Šis straipsnis buvo parašytas „Excel 2007“ (taip pat ir vėlesnėms versijoms). Ankstesnių „Excel“ versijų instrukcijas rasite atskirame straipsnyje: Kaip sukurti „PivotTable“ programoje „Excel 2003“?
Kaip pavyzdį apsvarstykite šią lentelę, kurioje pateikiami 2016 m. pirmojo ketvirčio įmonės pardavimų duomenys:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Data | Sąskaitos faktūros Nr | suma | Pardavimų konsultantas. | regionas |
| 2 | 01/01/2016 | 2016 - 0001 | $ 819 | Barnes | Šiaurė |
| 3 | 01/01/2016 | 2016 - 0002 | $ 456 | rudas | Į pietus |
| 4 | 01/01/2016 | 2016 - 0003 | $ 538 | Jones | Į pietus |
| 5 | 01/01/2016 | 2016 - 0004 | $ 1,009 | Barnes | Šiaurė |
| 6 | 01/02/2016 | 2016 - 0005 | $ 486 | Jones | Į pietus |
| 7 | 01/02/2016 | 2016 - 0006 | $ 948 | Kalvis | Šiaurė |
| 8 | 01/02/2016 | 2016 - 0007 | $ 740 | Barnes | Šiaurė |
| 9 | 01/03/2016 | 2016 - 0008 | $ 543 | Kalvis | Šiaurė |
| 10 | 01/03/2016 | 2016 - 0009 | $ 820 | rudas | Į pietus |
| 11 | ... | ... | ... | ... | ... |
Pirmiausia sukurkime labai paprastą suvestinę lentelę, kurioje bus parodytas bendras kiekvieno pardavėjo pardavimas pagal aukščiau pateiktą lentelę. Norėdami tai padaryti, atlikite šiuos veiksmus:
- Pasirinkite bet kurį langelį iš duomenų diapazono arba visą diapazoną, kuris bus naudojamas suvestinėje lentelėje.DĖMESIO: Jei pasirinksite vieną langelį iš duomenų diapazono, „Excel“ automatiškai aptiks ir pasirinks visą „PivotTable“ duomenų diapazoną. Kad Excel teisingai pasirinktų diapazoną, turi būti įvykdytos šios sąlygos:
- Kiekvienas duomenų diapazono stulpelis turi turėti savo unikalų pavadinimą;
- Duomenyse neturi būti tuščių eilučių.
- Spustelėjus mygtuką suvestinė lentelė (Pivot Table) skyriuje Lentelės (Lentelės) skirtuką įsiuvas (Įterpti) „Excel“ meniu juostelės.
- Ekrane pasirodys dialogo langas. Sukurkite „PivotTable“. (Sukurkite PivotTable), kaip parodyta toliau pateiktame paveikslėlyje.
Įsitikinkite, kad pasirinktas diapazonas atitinka langelių diapazoną, kuris turėtų būti naudojamas kuriant „PivotTable“. Čia taip pat galite nurodyti, kur turi būti įterpta sukurta suvestinė lentelė. Galite pasirinkti esamą lapą, kad į jį įterptumėte suvestinę lentelę, arba parinktį – Į naują lapą (Naujas darbalapis). spustelėkite OK. - Atsiras tuščia suvestinė lentelė ir skydelis Suvestinės lentelės laukai (Pivot Table Field List) su keliais duomenų laukais. Atminkite, kad tai yra pradinio duomenų lapo antraštės.
- Plokštėse Suvestinės lentelės laukai (Pivot lentelės laukų sąrašas):
- vilkite ir upuść Pardavimų konsultantas. į sritį Eilutės (Eilutės etiketės);
- vilkite ir upuść suma в Vertybės (Vertės);
- Mes tikriname: in Vertybės (Vertės) turi būti vertė Sumos laukas Suma (Sumos suma), а Nе Suma pagal lauką Kiekis (Sumos skaičius).
Šiame pavyzdyje stulpelis suma yra skaitinės reikšmės, taigi sritis Σ Vertybės (Σ Vertės) bus pasirinktos pagal numatytuosius nustatymus Sumos laukas Suma (Sumos suma). Jei stulpelyje suma bus neskaitinės arba tuščios reikšmės, tada galima pasirinkti numatytąją suvestinę lentelę Suma pagal lauką Kiekis (Sumos skaičius). Jei taip atsitiks, galite pakeisti kiekį į sumą taip:
- Lauke Σ Vertybės (Σ Vertės) spustelėkite Suma pagal lauką Kiekis (Sumos skaičius) ir pasirinkite parinktį Vertės lauko parinktys (Verčių lauko nustatymai);
- Skirtuke Išplėstinė Operacija (Apibendrinti reikšmes pagal) pasirinkite operaciją Suma (Suma);
- Paspauskite čia OK.
Įsitikinkite, kad pasirinktas diapazonas atitinka langelių diapazoną, kuris turėtų būti naudojamas kuriant „PivotTable“. Čia taip pat galite nurodyti, kur turi būti įterpta sukurta suvestinė lentelė. Galite pasirinkti esamą lapą, kad į jį įterptumėte suvestinę lentelę, arba parinktį – Į naują lapą (Naujas darbalapis). spustelėkite OK.
„PivotTable“ bus užpildyta kiekvieno pardavėjo pardavimo suma, kaip parodyta aukščiau esančiame paveikslėlyje.
Jei norite rodyti pardavimo apimtis piniginiais vienetais, turite suformatuoti langelius, kuriuose yra šios reikšmės. Lengviausias būdas tai padaryti – pažymėti langelius, kurių formatą norite tinkinti, ir pasirinkti formatą Piniginis (Valiuta) skyrius Skaičius (Skaičius) skirtuką Pagrindinis (Pagrindinis) „Excel“ meniu juostelės (kaip parodyta toliau).
Dėl to suvestinė lentelė atrodys taip:
- suvestinė lentelė prieš skaičių formato nustatymą
- suvestinė lentelė nustačius valiutos formatą
Atkreipkite dėmesį, kad numatytasis valiutos formatas priklauso nuo sistemos nustatymų.
Rekomenduojamos „PivotTables“ naujausiose „Excel“ versijose
Naujausiose „Excel“ versijose („Excel 2013“ arba naujesnėse versijose) įsiuvas Yra mygtukas (Įterpti). Rekomenduojamos sukamosios lentelės (Rekomenduojamos suvestinės lentelės). Remiantis pasirinktais šaltinio duomenimis, šis įrankis siūlo galimus suvestinės lentelės formatus. Pavyzdžius galima peržiūrėti „Microsoft Office“ svetainėje.