Kā pievienot un lietot Excel rakurstabulas aprēķināto lauku

Bieži vien, izveidojot Pivot tabulu, ir jāpaplašina analīze un jāiekļauj vairāk datu/aprēķinu.

Ja jums ir nepieciešams jauns datu punkts, ko var iegūt, izmantojot esošos datu punktus rakurstabulā, jums nav jāatgriežas un jāpievieno tas avota datos. Tā vietā varat izmantot a Šarnīra tabulas aprēķinātais lauks lai to izdarītu.

Lejupielādējiet datu kopu un sekojiet tai.

Kas ir rakurstabulas aprēķinātais lauks?

Sāksim ar rakurstabulas pamata piemēru.

Pieņemsim, ka jums ir mazumtirgotāju datu kopa un izveidojat rakurstabulu, kā parādīts zemāk:

Iepriekšējā rakurstabula apkopo mazumtirgotāju pārdošanas un peļņas vērtības.

Ko darīt, ja vēlaties arī uzzināt, kāda bija šo mazumtirgotāju peļņas norma (kur peļņas norma ir “Peļņa” dalīta ar “Pārdošana”).

Ir vairāki veidi, kā to izdarīt:

1. Atgriezieties sākotnējā datu kopā un pievienojiet šo jauno datu punktu. Tātad jūs varat ievietot jaunu kolonnu avota datos un aprēķināt peļņas normu tajā. Kad esat to izdarījis, jums ir jāatjaunina rakurstabulas avota dati, lai iegūtu šo jauno kolonnu kā daļu no tās.
   • Lai gan šī metode ir iespējama, jums manuāli jāatgriežas pie datu kopas un jāveic aprēķini. Piemēram, jums var būt jāpievieno vēl viena sleja, lai aprēķinātu vidējo pārdošanas apjomu vienībā (pārdošanas apjoms/daudzums). Atkal šī kolonna būs jāpievieno avota datiem un pēc tam jāatjaunina rakurstabula.
   • Šī metode arī uzpūš jūsu rakurstabulu, pievienojot tai jaunus datus.
2. Pievienojiet aprēķinus ārpus rakurstabulas. Tas var būt risinājums, ja jūsu rakurstabulas struktūra, visticamāk, nemainīsies. Bet, ja maināt rakurstabulu, aprēķins var netikt attiecīgi atjaunināts un var tikt parādīti nepareizi rezultāti vai kļūdas. Kā parādīts zemāk, es aprēķināju peļņas normu, kad rindā bija mazumtirgotāji. Bet, mainot to no klientiem uz reģioniem, formula deva kļūdu.
3. Pivot tabulas aprēķinātā lauka izmantošana. Tas ir visefektīvākais veids lai izmantotu esošos rakurstabulas datus un aprēķinātu vēlamo metriku. Apsveriet Aprēķināto lauku kā virtuālu kolonnu, kuru esat pievienojis, izmantojot esošās kolonnas no rakurstabulas. Pivot tabulas aprēķinātā lauka izmantošanai ir daudz priekšrocību (kā mēs redzēsim pēc minūtes):
   • Tas neprasa rīkoties ar formulām vai atjaunināt avota datus.
   • Tas ir mērogojams, jo tas automātiski ņems vērā visus jaunos datus, kurus varat pievienot rakurstabulai. Kad esat pievienojis aprēķina lauku, varat to izmantot kā jebkuru citu rakurstabulas lauku.
   • To ir viegli atjaunināt un pārvaldīt. Piemēram, ja metrika mainās vai jums ir jāmaina aprēķins, to var viegli izdarīt, izmantojot pašu rakurstabulu.

Aprēķinātā lauka pievienošana rakurstabulai

Apskatīsim, kā esošajā rakurstabulā pievienot rakurstabulas aprēķināto lauku.

Pieņemsim, ka jums ir rakurstabula, kā parādīts zemāk, un vēlaties aprēķināt katra mazumtirgotāja peļņas normu.

Lai pievienotu rakurstabulas aprēķināto lauku, veiciet tālāk norādītās darbības.

• Pivot tabulā atlasiet jebkuru šūnu.
• Dodieties uz rakurstabulas rīkiem -> Analīze -> Aprēķini -> Lauki, vienumi un kopas.
• Nolaižamajā izvēlnē atlasiet Aprēķinātais lauks.
• Dialoglodziņā Ievietot aprēķināto failu:
  • Piešķiriet tam nosaukumu, ievadot to laukā Nosaukums.
  • Laukā Formula izveidojiet vajadzīgo formulu aprēķinātajam laukam. Ņemiet vērā, ka varat izvēlēties no zemāk uzskaitītajiem lauku nosaukumiem. Šajā gadījumā formula ir ‘= peļņa/ pārdošana’. Jūs varat vai nu manuāli ievadīt lauku nosaukumus, vai arī veiciet dubultklikšķi uz lauka nosaukuma, kas norādīts laukā Lauki.
• Noklikšķiniet uz Pievienot un aizveriet dialoglodziņu.

Tiklīdz pievienosit aprēķināto lauku, tas parādīsies kā viens no laukiem rakurstabulas lauku sarakstā.

Tagad šo aprēķināto lauku varat izmantot kā jebkuru citu rakurstabulas lauku (ņemiet vērā, ka rakurstabulas aprēķināto lauku nevar izmantot kā atskaites filtru vai griezēju).

Kā jau minēju iepriekš, rakurstabulas aprēķinātā lauka izmantošanas priekšrocība ir tā, ka varat mainīt rakurstabulas struktūru, un tā tiks automātiski pielāgota.

Piemēram, ja es velku un nometu reģionu rindu apgabalā, jūs iegūsit rezultātu, kā parādīts zemāk, kur peļņas normas vērtība ir norādīta mazumtirgotājiem, kā arī reģionam.

Iepriekš minētajā piemērā es izmantoju vienkāršu formulu (= peļņa/pārdošana), lai ievietotu aprēķināto lauku. Tomēr varat izmantot arī dažas uzlabotas formulas.

Pirms parādīšu piemēru, kā izmantot uzlaboto formulu, lai izveidotu rakurstabulas aprēķina lauku, šeit ir dažas lietas, kas jums jāzina:

• Veidojot rakurstabulas aprēķināto lauku, NEVAR izmantot atsauces vai nosauktos diapazonus. Tas izslēgtu daudzas formulas, piemēram, VLOOKUP, INDEX, OFFSET utt. Tomēr varat izmantot formulas, kas var darboties bez atsaucēm (piemēram, SUM, IF, COUNT utt.).
• Formulā varat izmantot konstanti. Piemēram, ja vēlaties uzzināt prognozētos pārdošanas apjomus, kuros tiek prognozēts pieaugums par 10%, varat izmantot formulu = Pārdošana*1.1 (kur 1.1 ir nemainīga).
• Prioritātes secība tiek ievērota formulā, kas veido aprēķināto lauku. Kā paraugpraksi izmantojiet iekavas, lai pārliecinātos, ka jums nav jāatceras prioritātes secība.

Tagad aplūkosim piemēru, kā izmantot aprēķinātu lauku, lai izveidotu papildu formulu.

Pieņemsim, ka jums ir datu kopa, kā parādīts zemāk, un jums ir jāparāda prognozētā pārdošanas vērtība rakurstabulā.

Lai prognozētu vērtību, lieliem mazumtirgotājiem (pārdošanas apjoms pārsniedz 3 miljonus) ir jāizmanto pārdošanas apjoma pieaugums par 5%, bet maziem un vidējiem mazumtirgotājiem - 10% (pārdošanas apjoms ir mazāks par 3 miljoniem).

Piezīme. Pārdošanas skaitļi šeit ir viltoti un ir izmantoti, lai ilustrētu šīs apmācības piemērus.

Lūk, kā to izdarīt:

• Pivot tabulā atlasiet jebkuru šūnu.
• Dodieties uz rakurstabulas rīkiem -> Analīze -> Aprēķini -> Lauki, vienumi un kopas.
• Nolaižamajā izvēlnē atlasiet Aprēķinātais lauks.
• Dialoglodziņā Ievietot aprēķināto failu:
  • Piešķiriet tam nosaukumu, ievadot to laukā Nosaukums.
  • Laukā Formula izmantojiet šādu formulu: = IF (reģions = ”Dienvidi”, Pārdošana *1,05, Pārdošana *1,1)
• Noklikšķiniet uz Pievienot un aizveriet dialoglodziņu.

Tādējādi rakurstabulai tiek pievienota jauna sleja ar pārdošanas prognozes vērtību.

Noklikšķiniet šeit, lai lejupielādētu datu kopu.

Pivot tabulas aprēķināto lauku problēma

Aprēķinātais lauks ir pārsteidzoša funkcija, kas patiešām uzlabo jūsu rakurstabulas vērtību ar lauka aprēķiniem, vienlaikus saglabājot visu mērogojamu un pārvaldāmu.

Tomēr rakurstabulas aprēķinātajos laukos ir problēma, kas jums jāzina pirms tās izmantošanas.

Pieņemsim, ka man ir rakurstabula, kā parādīts zemāk, kur es izmantoju aprēķināto lauku, lai iegūtu prognozētos pārdošanas skaitļus.

Ņemiet vērā, ka starpsumma un kopsumma nav pareiza.

Lai gan tiem jāpievieno katra mazumtirgotāja individuālās pārdošanas prognozes vērtība, patiesībā tā tiek veikta saskaņā ar to pašu aprēķināto lauka formulu, ko mēs izveidojām.

Tātad South Total, lai gan vērtībai vajadzētu būt 22 824 000, South Total kļūdaini ziņo par 22 287 000. Tas notiek, jo vērtības iegūšanai tiek izmantota formula 21 225 800*1,05.

Diemžēl jūs to nevarat labot.

Labākais veids, kā to atrisināt, ir noņemt starpsummas un kopsummas no rakurstabulas.

Varat arī izmantot dažus novatoriskus risinājumus, ko Debra ir parādījusi, lai risinātu šo problēmu.

Kā modificēt vai dzēst rakurstabulas aprēķināto lauku?

Kad esat izveidojis rakurstabulas aprēķināto lauku, varat modificēt formulu vai dzēst to, veicot šādas darbības:

• Pivot tabulā atlasiet jebkuru šūnu.
• Atveriet rakurstabulas rīkus -> Analīze -> Aprēķini -> Lauki, vienumi un kopas.
• Nolaižamajā izvēlnē atlasiet Aprēķinātais lauks.
• Laukā Nosaukums noklikšķiniet uz nolaižamās bultiņas (maza lejupvērsta bultiņa lauka beigās).
• Sarakstā atlasiet aprēķināto lauku, kuru vēlaties dzēst vai mainīt.
• Mainiet formulu, ja vēlaties to mainīt, vai noklikšķiniet uz Dzēst, ja vēlaties to izdzēst.

Kā iegūt visu aprēķināto lauku formulu sarakstu?

Ja izveidojat daudz aprēķinātās rakurstabulas lauku, neuztraucieties par to, kā izsekot katrai tai izmantotajai formulai.

Programma Excel ļauj ātri izveidot visu aprēķināto lauku izveidē izmantoto formulu sarakstu.

Lai ātri iegūtu visu aprēķināto lauku formulu sarakstu, veiciet tālāk norādītās darbības.

• Pivot tabulā atlasiet jebkuru šūnu.
• Dodieties uz rakurstabulas rīkiem -> Analīze -> Lauki, vienumi un kopas -> Saraksta formulas.

Tiklīdz noklikšķināsit uz Saraksta formulas, programma Excel automātiski ievietos jaunu darblapu, kurā būs informācija par visiem aprēķinātajiem laukiem/vienumiem, kurus esat izmantojis rakurstabulā.

Tas var būt patiešām noderīgs rīks, ja jums ir jānosūta savs darbs klientam vai jādala tas ar savu komandu.

Var noderēt arī šādas rakurstabulu apmācības:

• Avota datu sagatavošana rakurstabulai.
• Griezēju izmantošana Excel rakurstabulā: rokasgrāmata iesācējiem.
• Kā grupēt datumus Excel rakurstabulās.
• Kā grupēt numurus Excel rakurstabulā.
• Kā filtrēt datus rakurstabulā programmā Excel.
• Kā nomainīt tukšās šūnas ar nullēm Excel rakurstabulās.
• Kā lietot nosacītu formatējumu Excel rakurstabulā.
• Pivot kešatmiņa programmā Excel - kas tas ir un kā to vislabāk izmantot?