Tai kas ta "pivot table"?

Ši pamokėlė apie tai, kaip, turint didelę lentelę su pasikartojančiais elementais, išfiltruoti tik unikalius elementus, ir unikalių elementų sąraše pateikti bendrą kiekvieno unikalaus elemento vertę, gautą susumavus pasikartojančių šio elemento įrašų vertes. Kaip suskaičiuoti, kiek kartų kiekvienas unikalus įrašas kartojasi. Pamokėlė apie tai, kaip naudotis "pivot table".

Ankstesnių pavyzdžių metu pademonstravau, kad sumanus funkcijų "countif" ir "sumif" panaudojimas gali pilnai pakeisti "pivot table". O kas gi yra ta "pivot table"?

Papasakosiu apie "pivot table" pasitelkdamas jau naudotą pavyzdį. Kaip minėjau ankstesnėje temoje, neseniai teko tyrinėti ES struktūrinių fondų paramą gavusių įmonių sąrašą. Duomenų lentelėje turėjau daugiau nei 13 tūkst. projektų. Prie kiekvieno projekto yra pateiktas jį įgyvendinančios įmonės kodas (pavyzdyje kodai išgalvoti) bei projektui skirta paramos suma (kuri taip pat išgalvota). Štai, ta lentelė:

	A	B	C
1	Projekto eilės numeris	Įmonės kodas	Gautos paramos suma
2	1	2436270	108000
3	2	5859378	328000
4	3	6675973	210000
5	4	3443355	176000
6	5	9566815	135000
7	6	4148778	244000
8	7	7753818	446000
9	8	3176602	179000
...
13144	13143	7558634	150000
13145	13144	7771457	195000
13146	13145	7605815	344000
13147	13146	2194383	57000
13148	13147	8571162	219000
13149	13148	2199652	114000

Kai kurios įmonės gavo paramą daugiau nei vienam projektui vykdyti (t. y. projektų sąraše jos kartojasi), todėl norėtume sužinoti, kiek yra unikalių paramos gavėjų ir kokia yra bendra kiekvienos įmonės gauta paramos suma.

Su ctrl+shift+end pasižymime visą lentelę ir meniu juostos skirtuke "Insert" spaudžiame mygtuką "PivotTable":

Atsidariusiame lange nieko nekeisdami spauskime "OK":

Naujame lape atsiras lentelės "pivot table" karkasas (kairėje) ir laukų pasirinkimo langas (dešinėje):

Mes norėsime, kad kiekvienai įmonei būtų skirta viena eilutė. Įmonę identifikuoja įmonės kodas, tad lauką "Įmonės kodas" pertempiame į "Row Labels" sritį. Kiekvienai įmonei norėsime susumuoti jai skirtos paramos sumas, tad lauką "Gautos paramos suma" pertempiame į sritį "Values", kur nutylėjimo būdu būna įjungtas sumavimas (žr. "Sum of Gauto..." žemiau pateiktoje lentelėje). Šiuo atveju bus sumuojama pagal srityje "Row Labels" esantį įmonės kodą. Štai ir viskas, turime unikalių įmonių sąrašą ir kiekvienos įmonės gautą bendrą paramos sumą:

Jei norėtume matyti ne tik bendrą kiekvienos įmonės paramos sumą, o taip pat, pavyzdžiui, ir projektų, kuriems vykdyti buvo gauta parama, skaičių, dar kartą pertempkime lauką "Gautos paramos suma" į sritį "Values". Ant naujai srityje atsiradusio elemento "Sum of Gautos paramos suma2" spauskime kairįjį pelės mygtuką ir pasirinkime "Value Field Settings...". Iššokusiame lange vietoje "Sum" pasirinkime "Count":

Spauskime "OK", ir mūsų "pivot table" lentelėje atsiras papildomas stulpelis, kuris nurodo, kiek kartų įmonė kartojasi pradiniame projektų sąraše:

Tad linkiu jums išmokti dirbt efektyviau ir sutaupytą darbo laiką feisbuke praleist :D [frazė iš mano pirmosios repo dainos]

"Sumif" triukas - alternatyva "pivot table"

Ši pamokėlė apie tai, kaip, turint didelę lentelę su pasikartojančiais elementais, išfiltruoti tik unikalius elementus, ir unikalių elementų sąraše pateikti bendrą kiekvieno unikalaus elemento vertę, gautą susumavus pasikartojančių šio elemento įrašų vertes.

Kaip minėjau ankstesnėje temoje, neseniai teko tyrinėti ES struktūrinių fondų paramą gavusių įmonių sąrašą. Duomenų lentelėje turėjau daugiau nei 13 tūkst. projektų. Prie kiekvieno projekto yra pateiktas jį įgyvendinančios įmonės kodas (pavyzdyje kodai išgalvoti) bei projektui skirta paramos suma (kuri taip pat išgalvota). Štai, ta lentelė:

	A	B	C
1	Projekto eilės numeris	Įmonės kodas	Gautos paramos suma
2	1	2436270	108000
3	2	5859378	328000
4	3	6675973	210000
5	4	3443355	176000
6	5	9566815	135000
7	6	4148778	244000
8	7	7753818	446000
9	8	3176602	179000
...
13144	13143	7558634	150000
13145	13144	7771457	195000
13146	13145	7605815	344000
13147	13146	2194383	57000
13148	13147	8571162	219000
13149	13148	2199652	114000

Ankstesnėje temoje paaiškinau, kaip susikurti lentelę, kuri apimtų tik unikalius paramos gavėjus, t. y. paramos gavėjai nesikartotų. Kaip rašiau, unikalius paramos gavėjus išfiltruosime pagal požymį "1" (kuris reiškia, kad žiūrint nuo viršaus, įmonės kodas paminėtas pirmą kartą):

Unikalių gavėjų sąrašą turėtume nukopijuoti, pavyzdžiui, į naują skaičiuoklės lapą:

	A	B
1	Įmonės kodas	Bendra gautos paramos suma?
2	2436270
3	5859378
4	6675973
5	3443355
6	9566815
7	4148778
8	7753818
9	3176602
...
6741	7605815
6742	2194383
6743	8571162
6744	2199652

Tad kitame skaičiuoklės lape jau turime unikalių paramos gavėjų sąrašą, ir norėtume sužinoti, kokia yra bendra kiekvienos įmonės gauta paramos suma (kai kurios įmonės gavo paramą daugiau nei vienam projektui). B2 langelyje įveskime formulę:

+SUMIF(Lapas01!$B$2:$B$13149,A2,Lapas01!$C$2:$C$13149)

Mūsų originali lentelė su pasikartojančiais elementų įrašais yra lape "Lapas01", tad pirmasis formulės narys atspindi plotą, kuriame ieškosime įmonės kodo. Kadangi formulę kopijuosime į visus žemiau esančius langelius, turime šį plotą užfiksuoti naudodami "$".

Antrasis narys "A2" rodo, ko bus ieškoma pirmajame naryje apibrėžtame plote - mūsų naujos lentelės antroje eilutėje esančio įmonės kodo. Kaip matote, "A2" imamas iš to paties lapo, kuriame įrašyta formulė.

Trečiasis narys "Lapas01!$C$2:$C$13149" nurodo, kuriame originalios lentelės stulpelyje pateikta vertė bus prisumuota, radus eilutę su ieškomu įmonės kodu.

Tereikia pertraukti formulę iki paskutinio langelio. Mūsų sąraše yra 6743 unikalios įmonės, tad formulę pertraukti su pele būtų neefektyvu. Galima daryti taip:

1) nukopijuoti formulę iš B2 langelio su ctrl+c;
2) pažymėti A2 langelį;
3) spustelėti ctrl+↓; tokiu būdu žymeklis akimirksniu atsidurs paskutinėje lentelės eilutėje (jei būtume nuspaudę šią klavišų kombinaciją esant pažymėtam B2 langeliui, žymeklis būtų peršokęs ne į paskutinę lentelės, bet į paskutinę lapo eilutę, pvz., į kokią 1 000 000 eilutę; taip yra todėl, kad B stulpelyje po B2 langeliu jokių įrašų nėra, tad nėra kaip identifikuoti lentelės pabaigos);
4) turime žymeklį 6744 eilutėje, tad tereikia pasižymėti B6744 langelį, ir spausti ctrl+shift+↑. Tokiu būdu bus pažymėtas visas B stulpelis (nuo B2 iki B6744), tad tereikia paspausti ctrl+v, kad į visus pažymėtus langelius būtų įklijuota kompiuterio atmintyje esanti mūsų formulė.

Viskas, gavome unikalių įmonių sąrašą su bendra kiekvienos įmonės gauta paramos suma:

	A	B
1	Įmonės kodas	Bendra gautos paramos suma?
2	2436270	640000
3	5859378	819000
4	6675973	750000
5	3443355	456000
6	9566815	754000
7	4148778	1054000
8	7753818	990000
9	3176602	326000
...
6741	7605815	344000
6742	2194383	57000
6743	8571162	219000
6744	2199652	114000

Tad linkiu jums išmokti dirbt efektyviau ir sutaupytą darbo laiką feisbuke praleist :D [frazė iš mano pirmosios repo dainos]

"Countif" triukas, vertas "pivot table"

Ši pamokėlė apie tai, kaip, turint didelę lentelę su pasikartojančiais elementais (įrašais), iš besikartojančių įrašų sąrašo išrinkti tik unikalius įrašus. Kitaip, alternatyva "pivot table" funkcionalumui.

Neseniai teko tyrinėti ES struktūrinių fondų paramą gavusių įmonių sąrašą. Norėjau nustatyti, ar yra įmonių, gavusių paramą pagal daugiau negu vieną projektą. Duomenų lentelėje turėjau daugiau nei 13 tūkst. projektų. Prie kiekvieno projekto yra pateiktas jį įgyvendinančios įmonės kodas (čia pateikiamas išgalvotas kodas, sugeneruotas panaudojant funkciją "randbetween") bei projektui skirta paramos suma (kuri čia taip pat pateikiama išgalvota). Štai, mūsų lentelė:

	A	B	C
1	Projekto eilės numeris	Įmonės kodas	Gautos paramos suma
2	1	2436270	108000
3	2	5859378	328000
4	3	6675973	210000
5	4	3443355	176000
6	5	9566815	135000
7	6	4148778	244000
8	7	7753818	446000
9	8	3176602	179000
...
13144	13143	7558634	150000
13145	13144	7771457	195000
13146	13145	7605815	344000
13147	13146	2194383	57000
13148	13147	8571162	219000
13149	13148	2199652	114000

Paprastai manydavau, kad lengviausias būdas sudaryti unikalių paramos gavėjų sąrašą yra panaudoti "pivot table". Tačiau įjungtas kūrybiškumas man pakuždėjo, kad tą patį galima atlikti susikūrus papildomą stulpelį.

Konkrečiai, D2 langelyje reikia įvesti formulę:

+COUNTIF($B$2:B2,B2)

Šią formulę turime nukopijuoti į visą mūsų lentelės D stulpelį. Pavyzdžiui, po tokio veiksmo D10 langelyje esanti formulė bus:

+COUNTIF($B$2:B10,B10)

Tai reiškia, kad D10 langelyje bus suskaičiuota, kiek kartų B2-B10 langeliuose kartojasi B10 langelyje esantis įmonės kodas. $B$2 yra pažymėtas $ ženklais, kurie reiškia, kad kopijuojant formulę į kitus langelius paieškos pradžia visada išliks "B2". T. y. visada funkcija išliks COUNTIF($B$2:Bzz,Bzz).

Jeigu funkcija gražins reikšmę "1", žinosime, kad įmonė, žiūrint nuo viršaus, paminėta pirmą kartą. Jeigu grąžinta reikšmė bus didesnė už "1", žinosime, kad įmonė jau kartojasi, kad tai nebe pirmas įmonės vykdomas projektas.

Unikalius paramos gavėjus galėsime išfiltruoti naudodami meniu juostoje pateikto skirtuko "Data" mygtuką "Filter", D stulpelyje pažymėję, kad norime matyti tik "1". Kaip matyti apatinėje lango juostoje, buvo išrinkti 6743 įrašai iš 13148:

Tokiu būdu gavome unikalių paramos gavėjų sąrašą, kurį galėsime, pavyzdžiui, nukopijuoti į kitą skaičiuoklės lapą tolimesnei analizei.

Tad linkiu jums išmokti dirbt efektyviau ir sutaupytą darbo laiką feisbuke praleist :D [frazė iš mano pirmosios repo dainos]