Šajā apmācības daļā ir sniegta informācija par rakurstabulas izveidi programmā Excel. Šis raksts tika rakstīts programmai Excel 2007 (kā arī jaunākām versijām). Norādījumi par iepriekšējām Excel versijām ir atrodami atsevišķā rakstā: Kā izveidot rakurstabulu programmā Excel 2003?

Kā piemēru ņemiet vērā šo tabulu, kurā ir iekļauti uzņēmuma pārdošanas dati par 2016. gada pirmo ceturksni:

Sākumā izveidosim ļoti vienkāršu rakurstabulu, kas parādīs katra pārdevēja kopējos pārdošanas apjomus saskaņā ar augstāk esošo tabulu. Lai to izdarītu, rīkojieties šādi:

Rakurstabula tiks aizpildīta ar katra pārdevēja kopējo pārdošanas apjomu, kā parādīts attēlā iepriekš.

Ja vēlaties parādīt pārdošanas apjomus naudas vienībās, ir jāformatē šūnas, kurās ir šīs vērtības. Vienkāršākais veids, kā to izdarīt, ir iezīmēt šūnas, kuru formātu vēlaties pielāgot, un atlasīt formātu Naudas(Valūta) sadaļa Numurs(Numurs) cilne mājas(Sākums) Excel izvēlnes lentes (kā parādīts zemāk).

Rezultātā rakurstabula izskatīsies šādi:

Lūdzu, ņemiet vērā, ka noklusējuma valūtas formāts ir atkarīgs no sistēmas iestatījumiem.

Daudzi biroja lietotāji bieži saskaras ar vairākām problēmām, mēģinot izveidot un rediģēt jebkādus biroja dokumentus. Bieži vien tas ir saistīts ar to, ka uzņēmumi izmanto vairākas biroja programmas, kuru darbības principi var ievērojami atšķirties. Excel rakurstabula var būt īpaši problemātiska.

Par laimi, salīdzinoši nesen parādījās MS Office 2010-2013, kas ne tikai ietver vairākas atjauninātas programmas teksta failu, izklājlapu, datu bāzu un prezentāciju apstrādei, bet arī ļauj ar tiem strādāt vienlaikus vairākiem darbiniekiem, kas ir vienkārši nenovērtējami. korporatīvajā vidē.

Kāpēc jaunas versijas?

Lai saprastu šo problēmu, jums ir jāiedomājas visas būtiskākās izmaiņas, kas notikušas ar šo biroja komplektu.

Tāpat kā iepriekš, tieši Excel ir otrā populārākā programma, kas ļauj ne tikai izveidot vienkāršas tabulas, bet pat izveidot diezgan sarežģītas datu bāzes. Tāpat kā citos komponentos, tai ir pievienota poga "Birojs", uz kuras noklikšķinot var saglabāt dokumentu sev vajadzīgā formātā, mainīt to vai norādīt nepieciešamos pasākumus tā aizsardzībai. Dokumentu aizsardzības modulis ir būtiski atjaunināts.

Runājot par būtiskākajām izmaiņām programmā Excel, jāatzīmē daudzo kļūdu labošana formulās, tāpēc iepriekšējās versijās aprēķinos bieži bija diezgan rupjas kļūdas.

Jāsaka tā jauna versija pakete ir ne tikai biroja programmu komplekts, bet arī jaudīgs rīks, kas piemērots pat ļoti sarežģītu uzdevumu risināšanai. Kā jūs varētu uzminēt, šodien mēs apskatīsim Excel rakurstabulu.

Kas tas ir un kāpēc tas ir vajadzīgs?

Būtiskākās izmaiņas jaunajās Office versijās ir to pilnībā pārveidotais interfeiss, ko veidotāji nosauca par lenti (lenti). Plūsmās visas 1500 komandas ir ērti sagrupētas kategorijās, un tāpēc tās nav ilgi jāmeklē. Lai pilnībā ievērotu šos parametrus, izstrādātāji programmā Excel ir pievienojuši arī uzlabotas rakurstabulas.

Šodien mēs apsvērsim Excel "manekeniem". Rakurstabulas ir īpašs rīks, kurā procesa rezultāti tiek vizuāli grupēti. Vienkārši sakot, ar viņu palīdzību jūs varat redzēt, cik no noteiktām precēm katrs pārdevējs pārdeva savas darba maiņas laikā. Turklāt tos izmanto gadījumos, kad tas ir nepieciešams:

• Sagatavot analītiskos aprēķinus gala ziņojumu rakstīšanai.
• Aprēķiniet katru to rādītāju atsevišķi.
• Grupējiet datus pēc veida.
• Veikt saņemto datu filtrēšanu un padziļinātu analīzi.

Šajā rakstā mēs apskatīsim vienkāršus veidus viņu darinājumi. Jūs uzzināsit, kā Excel rakurstabula palīdz izveidot un analizēt laika rindas, izveidot un detalizētāk analizēt prognozi.

Mēs izveidojam nepieciešamo dokumentu

Tātad, kā programmā Excel izveidot rakurstabulu? Pirmkārt, mums ir jāizveido vienkāršs dokuments, kurā ievadām datus to turpmākai analīzei. Katrā kolonnā jābūt vienam parametram. Ņemsim vienkāršāko piemēru:

• Izpārdošanas laiks.
• Pārdots priekšmets.
• Kopējās pārdošanas izmaksas.

Tādējādi veidojas attiecības starp visiem parametriem katrā konkrētajā ailē: pieņemsim, ka kedas tika pārdotas pulksten 9 no rīta, un peļņa bija n-rubļi.

Tabulas formatēšana

Sagatavojot visu sākotnējo informāciju, novietojiet kursoru pirmās kolonnas pirmajā šūnā, atveriet cilni "Ievietot" un pēc tam noklikšķiniet uz pogas "Rakurstabula". Tūlīt parādīsies tas, kurā varat veikt šādas darbības:

• Ja nekavējoties noklikšķināsit uz pogas "OK", Excel rakurstabula nekavējoties tiks parādīta atsevišķā lapā.
• Pilnībā pielāgojiet izvadi.

Pēdējā gadījumā izstrādātāji dod mums iespēju noteikt šūnu diapazonu, kurā tiks parādīta nepieciešamā informācija. Pēc tam lietotājam ir jānosaka, kur tieši tiks izveidota jaunā tabula: uz esošās lapas vai uz jaunizveidotās lapas. Noklikšķinot uz "OK", jūs uzreiz redzēsiet gatavo tabulu savā priekšā. Tas pabeidz rakurstabulu izveidi programmā Excel.

Lapas labajā pusē ir apgabali, ar kuriem jums ir jāstrādā. Laukus var ievilkt atsevišķos apgabalos, pēc tam dati no tiem tiks parādīti tabulā. Attiecīgi pati pagrieziena tabula atradīsies darbvietas kreisajā pusē.

Turot nospiestu lauku "Produkts" ar nospiestu peles kreiso pogu, mēs to nosūtām uz "Rindu nosaukumi", un vienums "Visu pārdošanas apjomu summa" tādā pašā veidā tiek pārsūtīts uz "Vērtības" (kreisajā pusē no lapas). Tādā veidā jūs varat iegūt pārdošanas apjomu visam analizējamajam periodam.

Laika rindu veidošana un grupēšana

Lai analizētu pārdošanas apjomus noteiktos laika periodos, jums ir jāievieto atbilstošās preces pašā tabulā. Lai to izdarītu, atveriet lapu "Dati" un pēc tam ievietojiet trīs jaunas kolonnas tūlīt aiz datuma. Atlasiet kolonnu un pēc tam noklikšķiniet uz pogas "Ievietot".

Ir ļoti svarīgi, lai visas jaunizveidotās kolonnas atrastos jau esošā tabulā ar avota datiem. Šajā gadījumā rakurstabulu izveide programmā Excel vairs nebūs nepieciešama. Jūs vienkārši pievienosit jaunus laukus ar nepieciešamajiem datiem.

Izmantotās formulas

Piemēram, jaunizveidotās kolonnas var nosaukt par "Gads", "Mēnesis", "Mēneši-gadi". Lai iegūtu mūs interesējošos datus, katrs no tiem būs jāieraksta atsevišķā aprēķinu formulā:

• Laukā "Ikgadējais" ievietojam formulu šādā formā: "=GADS" (atsaucoties uz datumu).
• Mēnesis jāpapildina ar izteicienu: "=MĒNESIS" (arī ar norādi uz datumu).
• Trešajā kolonnā ievietojam formulu šādā formā: "=CONCATENATE" (attiecas uz gadu un mēnesi).

Attiecīgi mēs iegūstam trīs kolonnas ar visiem sākotnējiem datiem. Tagad jums jāiet uz izvēlni "Kopsavilkums", ar peles labo pogu noklikšķiniet uz jebkuras brīvas vietas tabulā un pēc tam atvērtajā logā atlasiet "Atjaunināt". Piezīme! Uzmanīgi ierakstiet formulas Excel rakurstabulās, jo jebkura kļūda novedīs pie neprecīzas prognozes.

Kā iziet cauri gadiem?

Atkal turiet nospiestu vienumu “Gads” ar peles kreiso pogu un pēc tam velciet to uz “Sleju nosaukumi”, pēc tam rakurstabulā sāk parādīties noteikta veida produkta pārdošanas rezultāti par visiem gadiem. Un ja jums ir jāanalizē arī ikmēneša pārdošanas apjomi? Tādā pašā veidā turiet nospiestu LMB "Mēnesis", velkot to zem gada kolonnas.

Lai iegūtu vizuālu priekšstatu par ikmēneša pārdošanas apjoma izmaiņu dinamiku pa gadiem, lauks Mēnesis ir jāievelk tieši gada kolonnā, kas iepriekš tika pārvietota uz kolonnu nosaukumi. Pēc šīs darbības veikšanas jūs redzēsiet tabulu šādā skatā:

• Konkrētas preces pārdošanas apjoms par visu gadu.
• Katra no tām pārdošanas dinamika pa gadiem.

Datu noņemšana no izdošanas

Pieņemsim, ka mums no izdevuma ir jāizņem dati par oktobri, jo mums nav pilnīgas statistikas par to. Rakurstabulā atrodiet “Pārskatu filtru”, pēc kura mēs velkam uz tā “Gads — mēnesis”.

Virs tabulas tiek parādīts filtrs, kurā jāatzīmē izvēles rūtiņa blakus vienumam “Atlasīt vairākus elementus”. Pēc tam parādītajā sarakstā noņemiet atzīmi no oktobra izvēles rūtiņas un pēc tam noklikšķiniet uz "OK".

Tādējādi jūs varat pievienot un noņemt elementus no filtra, veidojot jums patiešām aktuālas un nepieciešamas analīzes. Programma automātiski aprēķinās rādītājus jums interesējošajiem mēnešiem, vizuāli attēlojot rādītāju izmaiņu dinamiku.

Mēs aprēķinām prognozes

Mēģināsim prognozēt pārdošanas rezultātus kādu laiku uz priekšu. Lai to izdarītu, vispirms ir jāizslēdz kopējās summas, lai tās neradītu neprecizitātes ģenerētajā prognozē.

Lai to izdarītu, novietojiet kursoru laukā "Grand Total" un pēc tam noklikšķiniet uz pogas "Dzēst kopējo summu". Pēc tam mēs izpildām Excel rakurstabulu iepriekš aprakstītajā veidā (noklikšķiniet uz peles labās pogas, atlasiet "Atjaunināt").

Rezultātā šie dati pazūd no mūsu rakurstabulas. Lai izveidotu pārdošanas prognožu vizuālu displeju, novietojiet kursoru uz gada, no kura plānojam sākt, un pēc tam ar peles kreiso taustiņu noklikšķiniet uz pogas "Chart Forecast Model".

Pēc tam parādās skaista un informatīva diagramma, kas vizuāli parāda prognozētos pirkšanas aktivitātes rezultātus atbilstoši jums interesējošajai prognozei. Tas ietaupīs jūsu enerģiju un ietaupīs naudu, ko varēsiet iztērēt patiešām nepieciešamo preču iegādei.

Rakurstabulas veidošanai mēs izmantojam citus datu avotus

Ir pienācis laiks apskatīt progresīvākas Excel nodarbības. Rakurstabulu var izveidot no dokumentiem, kurus rediģējuši citi lietotāji.

Funkcija "Apvienot" mums to palīdzēs. Šī programma ļauj automātiski apvienot dokumenta tekstu un nepieciešamās adreses, vārdus vai citu nepieciešamo informāciju, lai izveidotu personalizētas prognozes, kas mūsu gadījumā ir nepieciešamas.

Sapludināšanas līdzeklis jaunākajās Office versijās lielākoties nav mainīts salīdzinājumā ar Office 2003. Tomēr datu bāzes vaicājumus, lai atlasītu datu avotu, var veikt, izmantojot Microsoft Query. Lai iegūtu datus no Excel rakurstabulas, ir nepieciešami divi objekti:

• Galvenais dokuments, kurā ir vajadzīgā informācija, kā arī lauki, kas norāda Excel, kādus datus ievietot dokumentā. Galvenā tabula ir standarta izklājlapas dokuments, taču dialoglodziņā Pasta sapludināšanas palīgs ir jānorāda, ka tā ir jāizmanto kā galvenā.
• Datu avots, kas satur prognozes veidošanai nepieciešamo informāciju. Varat izveidot jaunu tabulu iepriekš norādītajā veidā.

Kad notiek kopsavilkuma dokumentu izveides process, programma izveido vienu kopiju katram ierakstam, kas atrodas datu avota failā. Šos failus var parādīt kā atsevišķas sadaļas jaunā dokumentā vai izmantot kā sadaļas jūsu rakurstabulā.

Atliek tikai noklikšķināt uz vajadzīgā lauka ar peles kreiso pogu, un jūs varat redzēt avota datus, nevis lauku nosaukumus, kas tiek parādīti galvenajā dokumentā. Laukus var ievietot jebkurā dokumenta vietā.

Izmantotās kategorijas

Arī šeit varat izmantot kategoriju ASK, kas nozīmē "pieprasīt", un FILLIN, kas nozīmē "aizpildīt", NEXT un NEXTIF, ELSE. Varat arī konfigurēt to prognožu vienumu parametrus, kas tiks parādīti gatavajā tabulā. Jūs varat atrast vajadzīgo ierakstu, izmantojot īpašu filtru vai vienkārši sakārtojot datus, kā mēs jau runājām iepriekš.

Kad viss ir gatavs, lai izveidotu saliktu dokumentu, dialoglodziņš Sapludināšana sniedz lietotājam iespēju identificēt un labot atsevišķas kļūdas, pirms tās rodas. Kad viss ir pārbaudīts, varat droši nospiest pogu "Apvienot". Ja apvienošanās ir jānokārto e-pasts, jums tiks piedāvāts instalēt pasta programmu (ja jums tā nav instalēta).

Ja sapludināšanas laikā rodas kļūda, tas norāda uz nepareizi norādītu lauku kāda cita dokumentā vai izmantotie dati tiek parādīti neatbalstītā formātā. Tāpēc šī procedūra ir jāpārskata.

Kopumā Excel (strādā ar rakurstabulām, kuras mēs apsveram) ļauj ne tikai veikt salīdzinoši sarežģītu pirktspējas analīzi, bet arī izveidot uz tām balstītas prognozes.

Termins "rakurstabula" mums neko daudz nepasaka. Vienkārša definīcija varētu izklausīties šādi: rakurstabula ir atskaite, kas ļauj skatīt datus ērtāk un saprotamāk. Visbiežāk rakurstabulas tiek izmantotas datu rādīšanas organizēšanai no datu bāzes, taču ir iespējams arī izveidot rakurstabulas no grāmatā esošajiem datiem. Svarīga prasība ir strukturāli sakārtota tabula (piemēram, reģistrs), kurā ir lauki un ieraksti (rindas). Piemēram, tabulā, kurā uzskaitīti darbinieki, ir šādi lauki: Uzvārds, Vārds, Vidējais vārds, Amats, Dzimšanas datums, Alga utt.

Rakurstabulas izveides process ir vienkāršs un vienlaikus sarežģīts, taču, uzzinot, kā tās izveidot, tas kļūs par jūsu galveno rīku lielu datu apjomu prezentēšanai. Uzreiz gribu brīdināt, ka rakstveidā ir diezgan grūti izskaidrot tapšanas procesu, tāpēc iesaku pēc izlasīšanas iepazīties ar video demonstrāciju. Tāpat vienā rakstā nav iespējams aptvert visas iespējas strādāt ar rakurstabulām, tāpēc raksti būs vairāk nekā viens.

Lai izveidotu pirmo rakurstabulu, mēs par pamatu ņemsim grāmatu, kas izveidota nodarbībā "", saite.

Atveriet grāmatu uz lapas “1. iespēja”

Mēs atveram" Dati — PivotTable…»

Tiek atvērts PivotTable vedņa logs. Saliekam visu kā attēlā:

Nākamajā logā jānorāda datu diapazons, no kura vēlaties izveidot rakurstabulu. Izmantojot peli, atlasiet visas rindas un kolonnas tabulā "Option 1" (pēc noklusējuma jau ir atlasīta).

Video piemērā es norādīju diapazonu "Option 1"!$A:$G

Tas ir nepieciešams, ja tabula tiek pastāvīgi papildināta ar datiem, un var būt ļoti grūti vai vienkārši slinkums pastāvīgi veidot tabulu :) Tādējādi es norādīju kolonnu diapazonu, bet rindu diapazonu ierobežo tikai Excel iespējas (in 2003. gadā tas ir 65 536 rindas, 2007.–2010. gadā vairāk nekā 1 miljons rindu). Bet šai metodei ir neliels trūkums, “tukša” kritērijs parādās tabulās (skatīt zemāk). Lai gan mani tas īsti netraucē.

Šajā darbībā mēs norādām, kur izveidot tabulu. Mēs aizejam " jauna lapa". Var arī uzreiz uzbūvēt izkārtojumu (manuprāt ērtāk to darīt tālāk aprakstītajā veidā, tā ir vairāk vizuāli) vai uzlikt tabulai kādus parametrus. Taču to visu nākotnē var uzlabot.

Klikšķis " Gatavs».

Mēs redzēsim šādu attēlu

Šis ir mūsu tabulas izkārtojums. Kreisajā pusē jāietver kritēriji, piemēram, darījumu partneru nosaukumi, darījumu veidi utt. Augšpusē ir arī teksta kritēriji. Atšķirība ir tāda, ka kreisā daļa tiks atspoguļota lentē, sadalot datus, un augšējā ļaus mums izvēlēties kritēriju, galvenajā (lielajā) apgabalā ir dati (summas, skaitļi utt.). Nedaudz augstāks laukums ļauj atdalīt šos datus, piemēram, pēc datuma vai mēnešiem utt.

Tabula tiek veidota, velkot laukus no " Rakurstabulas lauku saraksts» uz vēlamajām zonām. Velciet laukus uz šīm zonām:

Darbības veids - velciet uz kreiso zonu

Piegādātājs - arī pa kreisi, bet nedaudz pa labi no Operācijas veida;

Mēs velkam produkta nosaukumu uz augšējo zonu;

Mēs pēc kārtas ievelkam kvantitāti un daudzumu lielākajā zonā;

Lai sadalītu pēc datumiem, velciet lauku Datums uz zonu tieši virs datu apgabala;

Rezultātā mēs iegūstam šādu tabulu:

Rezultāts ir pārāk pārpildīta tabula ar summām, un laukos Summa un Daudzums tā nav vērtību summa, bet gan to skaits.

Fiksācija.

Lai mainītu aprēķina opciju, ir jāpārvieto peles kursors virs rindiņas “Daudzums pēc daudzuma lauka” un “Daudzums pēc daudzuma lauka”, lai tas iegūtu melnas bultiņas formu:

Vienreiz noklikšķinot ar peles kreiso pogu, visas grupas “Daudzums pēc daudzuma” rindas ir jāizceļ, kā parādīts attēlā.

Tagad ar peles labo pogu noklikšķiniet un atlasiet " Lauka opcijas»

Atvērtajā aprēķinu opciju logā atlasiet " Summa»

Dariet to pašu ar rindām grupā "Daudzums pēc lauka Summa" /

Tagad paslēpsim papildu kopsummas rindas. Lai to izdarītu, mēs arī atlasām grupas:

Un konteksta izvēlnē atlasiet vienumu " Slēpt»

Rezultātam vajadzētu būt šādai tabulai:

Visi. Tagad varat apskatīt dažādu informāciju, izmantojot atlases kritērijus. Piemēram, paskatīsimies, kas mums atved iebiezināto pienu, kādā daudzumā un par kādu daudzumu. Lai to izdarītu, noklikšķiniet produkta nosaukuma laukā atbilstoši bultiņas attēlam un atlasiet no saraksta:

Iegūstiet veidlapas tabulu:

Mainot lauku komplektāciju un secību, mēs varam attēlot un aprēķināt datus jebkurā mums ērtā formā, vai tie būtu dati pa dienām, piegādātājs, nosaukums, kopsumma utt. Es saprotu, ka tēma ir diezgan sarežģīta un ir ļoti grūti to detalizēti aprakstīt. Tāpēc mēs vēlamies labot tālāk redzamo video demonstrāciju.

Tas pagaidām ir viss. Nākotnē mēs iemācīsimies veidot tabulas, izmantojot datus no datu bāzēm, un veidot diagrammas, pamatojoties uz rakurstabulām.

Pievienotais fails: svodnaya_excel.zip

Video: Rakurstabulas izveide programmā Excel

Rakurstabulas tiek uzskatītas par vienu no jaudīgākajiem datu apstrādes rīkiem programmā Excel. Tie pastāv, lai vienkāršotu sarežģītu un apgrūtinošu tabulu un padarītu aprēķinu rezultātus vienkāršus, saprotamus un pieejamus. Bet! Ne visas tabulas var apkopot.

Tabulai jābūt parasta saraksta formā, tas ir, kolonnu virsraksti var būt tikai pirmajā rindā. Šādas tabulas piemērs ir parādīts attēlā. P1.1.

Ja jūsu tabulā ir kādas starpgalvenes vai starpsummas, tās ir jāizdzēš. Lai vārdos neizskaidrotu visas rakurstabulas priekšrocības, es to parādīšu ar piemēru. Izvēlnes Insert grupā Tabulas noklikšķiniet uz pogas Pivot Table (Att. A1.2).

Tiks atvērts logs. Tajā vispirms ir jāatlasa tabula vai tabulas diapazons, kuram tiek apkopota rakurstabula, un jānorāda, kur to novietot. Varat to novietot uz jaunas lapas vai novietot blakus oriģinālajam galdam, uz tās pašas lapas, kā vēlaties (Att. A1.3).

Es ievietoju rakurstabulu tajā pašā lapā. Loga labajā daļā parādījās dizaina panelis rakurstabulas izveidei, un augšpusē parādījās jauna cilņu grupa — Darbs ar rakurstabulām.

Kā tiek izgatavota šarnīra tabula?

Paneļa labajā pusē, ko sauc par rakurstabulas lauku sarakstu, ir redzams 2. attēlā redzamās tabulas kolonnu virsrakstu saraksts. P1.1. No šiem laukiem tagad varat izveidot jaunu tabulu kā no konstruktora. Lai to izdarītu, ar peli velciet lauka nosaukumu uz vajadzīgo apgabalu. Es nolēmu, ka rakurstabulas kolonnās man būs mēnešu nosaukumi, rindās - uzvārdi, un es aizpildīšu lauku Vērtības ar vērtībām no kolonnas Saņemtais. Tas nozīmē, ka rakurstabula ietvers tikai datus par saņemto naudu. Rezultāts ir parādīts attēlā. P1.4.

Rakurstabulu tā sauc, jo tā apkopo visus jūsu rezultātus vienkāršā tabulā, apkopojot kopējos rezultātus. IN Šis gadījums tabulā ir apkopoti dati (slejā Vērtības ir norādīta summa laukā Saņemtais). Starp citu, cilnē Parametri atveriet pogas Aprēķini sarakstu (Att. A1.5).

Kopējās summas lieku uz vidējo, un, kā redzams att. A1.5, tagad pivot tabula nerēķina summu pa mēnešiem un uzvārdiem, bet vidējo vērtību: vidējo algu pa mēnešiem un vidējo vērtību pa darbiniekiem. Turklāt, lai izveidotu rakurdiagrammu, varat izmantot rakurstabulas vērtības (Att. A1.6).

Tādējādi tiek atvērta cilņu grupa Rakursdiagrammas rīki, kas jums nav nekas jauns. Mēs to visu apsvērām, analizējot parasto diagrammu darbu. Starp citu, pievērsiet uzmanību: es apmainīju rindas un kolonnas, tāpēc kopsummas tagad tiek aprēķinātas pēc kolonnas Atlikums vērtības (sk. A1.6. att.). Es to izdarīju, lai jūs zinātu, ka kolonnu, rindu un vērtību lauka vērtības var sajaukt, kā vēlaties.

Rakurstabulā varat arī ievietot šķēlēju. Šis ir papildu filtrs, kas ļauj rezultātu padarīt vēl skaidrāku (Att. A1.7).

Cilnes Parametri grupā Kārtot un filtrēt noklikšķiniet uz pogas Ievietot šķēlēju un atlasiet parametru, pēc kura vēlaties filtrēt datus. Esmu norādījis mēnesi. Tagad šķēluma logā varēsiet izvēlēties konkrētu mēnesi, un rakurstabulā tiks parādīti tikai ar šo mēnesi saistītie dati (skat. A1.6. att.). Jūsu rīcībā parādīsies arī vesela cilne - Šķēles rīki. Starp citu, tabulā var ievietot vairāk nekā vienu šķēli, bet vairākas.

Es jums pastāstīju vienkāršākos trikus darbam ar rakurstabulām. Ja jūs to saprotat, jūs varat saprast visu pārējo. Vienkārši neaizmirstiet, ka pirms rakurstabulas izveides tam ir jāsagatavo avota tabula, tas ir, izveidojiet to tā, lai tajā nebūtu nevienas starpgalvenes un kopsummas. Nu, ja kaut kas joprojām nav skaidrs vai vēlaties sīkāk izpētīt rakurstabulu iespējas, iesaku atsaukties uz īpašās vietnes Excel 2010 Pivot Tables materiāliem, kas pilnībā ir veltīti darba metodēm ar datiem. programmā Excel 2010 rakurstabulas.

Rakurstabulas ir nepieciešamas, lai apkopotu, analizētu un parādītu datus, kas atrodas "lielās" avota tabulās, dažādos griezumos. Apsveriet vienkāršu rakurstabulu izveides procesu.

Rakurstabulas (Ievietot/ Tabulas/ Rakurstabula) var būt noderīgi, ja vienlaikus tiek izpildīti šādi nosacījumi:

• ir sākuma tabula ar daudzām rindām (ierakstiem), mēs runājam par vairākiem desmitiem un simtiem rindu;
• nepieciešams veikt datu analīzi, kas prasa datu iztveršanu (filtrēšanu), to grupēšanu (summēšanu, saskaitīšanu) un datu uzrādīšanu dažādās sadaļās (atskaišu sagatavošana);
• šo analīzi ir grūti veikt, pamatojoties uz sākotnējo tabulu, izmantojot citus rīkus: ( CTRL+SHIFT+L), ;
• avota tabula atbilst noteiktām prasībām (skatīt zemāk).

Lietotāji bieži izvairās no lietošanas Rakurstabulas, jo tie noteikti ir pārāk sarežģīti. Patiešām, jebkura jauna instrumenta vai metodes apguve prasa laiku un pūles. Bet rezultātā jaunā apgūšanas efektam vajadzētu pārsniegt ieguldītās pūles. Šajā rakstā mēs sapratīsim, kā izveidot un pieteikties Rakurstabulas.

Sākotnējās tabulas sagatavošana

Sāksim ar prasībām oriģinālajai tabulai.

• katrai kolonnai jābūt virsrakstam;
• katrā kolonnā ir jāietver vērtības tikai vienā formātā (piemēram, ailē Piegādes datums jāsatur visas vērtības šajā formātā datums; ailē "Piegādātājs" - uzņēmumu nosaukumi tikai teksta formātā vai arī var ievadīt Piegādātāja kodu ciparu formātā);
• tabulā nedrīkst būt pilnīgi tukšas rindas un kolonnas;
• Šūnās jāievada “atomu” vērtības, t.i. tikai tās, kuras nevar sadalīt dažādās kolonnās. Piemēram, vienā šūnā nevar ievadīt adresi šādā formātā: "Pilsēta, ielas nosaukums, ēkas numurs". Pretējā gadījumā jums ir jāizveido 3 kolonnas ar tādu pašu nosaukumu rakurstabula strādās neefektīvi (ja nepieciešama informācija, piemēram, pilsētas kontekstā);
• izvairieties no tabulām ar "nepareizu" struktūru (skat. attēlu zemāk).

Tā vietā, lai izveidotu atkārtotas kolonnas ( 1. reģions, 2. reģions,…), kurā būs daudz tukšu šūnu, pārdomājiet tabulas struktūru, kā parādīts attēlā iepriekš (visām pārdošanas vērtībām jābūt vienā kolonnā, nevis sadalītām vairākās kolonnās. Lai to īstenotu, iespējams, būs jāsaglabā detalizētāka informācija ierakstus (skatiet attēlu iepriekš), nevis norādot kopējo pārdošanas apjomu katram reģionam).

Detalizētāki padomi par ēku galdiem ir sniegti tāda paša nosaukuma rakstā.

Tas nedaudz atvieglo veidošanas procesu. rakurstabula, fakts, ka, ja oriģināls ( Ievietot/ Tabulas/ Tabula). Lai to izdarītu, vispirms izveidojiet avota tabulu atbilstoši iepriekš minētajām prasībām, pēc tam atlasiet jebkuru tabulas šūnu un izsauciet izvēlnes logu Ievietot/ Tabulas/ Tabula. Visi lauki logā tiks automātiski aizpildīti, noklikšķiniet uz Labi.

Tagad lauku sarakstā atzīmējiet izvēles rūtiņu blakus laukam Pārdošana.

Jo Ja kolonnas Pārdošanas šūnas ir ciparu formātā, tās automātiski tiks ievietotas sadaļā Vērtību lauku saraksts.

Ar dažiem peles klikšķiem (precīzāk, sešiem) mēs izveidojām pārdošanas pārskatu par katru produktu. To pašu rezultātu varētu sasniegt, izmantojot formulas (skat. rakstu).
Ja nepieciešams, piemēram, noteikt pārdošanas apjomus katram Piegādātājam, tad, lai to izdarītu, Lauku sarakstā noņemiet atzīmi no rūtiņas blakus laukam Preces un atzīmējiet rūtiņu blakus laukam Piegādātājs.

Rakurstabulas datu izpēte

Ja jums ir jautājumi par to, kādi dati no sākotnējās tabulas tika izmantoti noteiktu vērtību aprēķināšanai rakurstabula, tad pietiek ar dubultklikšķi uz noteiktas vērtības rakurstabula lai izveidotu atsevišķu lapu ar rindām, kas atlasītas no sākotnējās tabulas. Piemēram, paskatīsimies, kuri ieraksti tika izmantoti, lai apkopotu Preces "Apelsīni" pārdošanas apjomu. Lai to izdarītu, veiciet dubultklikšķi uz vērtības 646720. Tiks izveidota atsevišķa lapa, kurā būs tikai oriģinālās tabulas rindas, kas saistītas ar produktu “Apelsīni”.

Atjauniniet rakurstabulu

Ja pēc izveidošanas rakurstabula Sākotnējai tabulai tika pievienoti jauni ieraksti (rindas), šie dati netiks automātiski ņemti vērā rakurstabula. Atjaunot rakurstabula atlasiet jebkuru no tās šūnām un atlasiet izvēlnes vienumu: izvēlne Darbs ar rakurstabulām/ Opcijas/ Dati/ Atsvaidzināt. To pašu rezultātu var sasniegt, izmantojot konteksta izvēlni: atlasiet jebkuru šūnu rakurstabula atjaunot.

Rakurstabulas dzēšana

Dzēst rakurstabula iespējams vairākos veidos. Pirmais ir vienkārši noņemt lapu no rakurstabula(ja vien tajā nav citu noderīgu datu, piemēram, sākotnējā tabula). Otrs veids ir izdzēst tikai sevi rakurstabula: atlasiet jebkuru šūnu rakurstabula, klikšķis CTRL+ A(visi tiks atlasīti) rakurstabula), nospiediet taustiņu Dzēst.

Kopsummas funkcijas maiņa

Veidojot rakurstabula grupētās vērtības tiek summētas pēc noklusējuma. Patiešām, risinot problēmu par pārdošanas apjomu atrašanu katrai precei, mēs nerūpējāmies par kopējo summu funkciju - tika summēti visi ar vienu Preci saistītie pārdošanas apjomi.
Ja nepieciešams, piemēram, saskaitīt katras Preces pārdoto partiju skaitu, tad kopsummas funkcija ir jāmaina. Šim nolūkam iekšā rakurstabula Kopējās summas / daudzums.

Kārtošanas secības maiņa

Tagad pārveidosim mūsu Konsolidētais pārskats. Vispirms mainīsim Preču nosaukumu kārtošanas secību: sakārtosim tās apgrieztā secībā no Z līdz A. Lai to izdarītu, nolaižamajā sarakstā, kas atrodas Preču nosaukumu kolonnas augšdaļā, atveriet izvēlni. un atlasiet Kārtot no Z līdz A.

Tagad pieņemsim, ka Baranka vienums ir vissvarīgākais, tāpēc tas ir jāparāda pirmajā rindā. Lai to izdarītu, atlasiet šūnu ar Baganka vērtību un novietojiet kursoru uz šūnas robežas (kursoram jābūt krusta formā ar bultiņām).

Pēc tam, nospiežot peles kreiso pogu, velciet šūnu uz saraksta augšējo pozīciju tieši zem kolonnas virsraksta.

Pēc peles pogas atlaišanas Bagel vērtība tiks pārvietota uz saraksta augšējo pozīciju.

Mainiet skaitlisko vērtību formātu

Tagad pievienosim skaitliskām vērtībām ciparu grupas atdalītāju (lauks Pārdošana). Lai to izdarītu, laukā Pārdošana atlasiet jebkuru vērtību, ar peles labo pogu izsauciet konteksta izvēlni un atlasiet izvēlnes vienumu Ciparu formāts…

Parādītajā logā atlasiet skaitļa formātu un atzīmējiet izvēles rūtiņu Tūkstoš atdalītājs.

Jaunu lauku pievienošana

Pieņemsim, ka vēlaties sagatavot pārskatu par Preču pārdošanu, bet sadalītu pa pārdošanas reģioniem. Lai to izdarītu, pievienojiet lauku Pārdošanas reģions, atzīmējot atbilstošo izvēles rūtiņu sarakstā Lauki. Lauks Pārdošanas reģions tiks pievienots lauku saraksta apgabalam Rindu nosaukumi (laukam Produkts). Mainoties zonā Rindu nosaukumi Lauku sarakstā, lauku Prece un Pārdošanas reģions secībā, iegūstam šādu rezultātu.

Izceļot jebkuru Produkta nosaukumu un noklikšķinot uz izvēlnes vienuma Rakurstabulas rīki/ Opcijas/ Aktīvais lauks/ Sakļaut visu lauku, var velmēt rakurstabula lai parādītu tikai pārdošanas apjomu pēc reģiona.

Kolonnu pievienošana

Rezultātā rindu apgabalam pievienojot lauku Pārdošanas reģions rakurstabula paplašināts līdz 144 rindām. Tas ne vienmēr ir ērti. Jo pārdošana tika veikta tikai 6 reģionos, ir jēga ievietot lauku Pārdošanas reģions kolonnas apgabalā.

rakurstabula būs šāda forma.

Kolonnu maiņa

Lai mainītu kolonnu secību, jums ir jāsatver kolonnas virsraksts rakurstabula velciet to uz pareizo vietu.

Lauku noņemšana

Jebkuru lauku var noņemt no rakurstabulas. Lai to izdarītu, novietojiet peles kursoru virs tā lauku sarakstā (apgabalos Pārskatu filtrs, Atskaišu nosaukumi, Kolonnu nosaukumi, Vērtības), nospiediet peles kreiso pogu un velciet dzēšamo lauku ārpus lauku saraksta.

Vēl viens veids ir noņemt atzīmi no izvēles rūtiņas blakus dzēšamajam laukam Lauku saraksta augšdaļā. Bet šajā gadījumā lauks tiks dzēsts no visiem lauku saraksta apgabaliem uzreiz (ja tas tika izmantots vairākos apgabalos).

Filtra pievienošana

Pieņemsim, ka ir jāsagatavo Preču grupu pārdošanas pārskats, un tas jādara 2 variantos: viens par Preču partijām, kas nesušas peļņu, otrs par nerentablajām. Priekš šī:

• PivotTable, noklikšķiniet uz izvēlnes vienuma ;
• Atzīmējiet ķeksīti Lauku sarakstā pie laukiem Grupa, Pārdošana un Peļņa;
• Pārvietojiet lauku Peļņa no lauku saraksta apgabala Rindu nosaukumi uz apgabalu Pārskatu filtrs;

Rezultāta veids rakurstabula vajadzētu būt šādam:

Tagad izmanto priekšrocības Nolaižamais (nolaižamais) sarakstsšūnā B1 (Peļņas lauks) varat, piemēram, izveidot atskaiti par to preču grupu pārdošanu, kas radīja peļņu.

Pēc pogas Labi nospiešanas tiks parādītas tikai ienesīgo partiju pārdošanas vērtības.

Lūdzu, ņemiet vērā, ka Lauku sarakstā rakurstabula pretī laukam Peļņa parādījās filtra ikona. Filtru var noņemt, noņemot atzīmi no izvēles rūtiņas lauku sarakstā.

Filtru var notīrīt izvēlnē Rakurstabulas rīki/ Opcijas/ Darbības/ Notīrīt/ Notīrīt filtrus.

Dati ir pieejami arī nolaižamajā sarakstā rindu un kolonnu galvenēs Rakurstabula.

Vienam laukam ir vairākas kopsummas

• Notīrīt iepriekš izveidoto pārskatu: atlasiet jebkuru vērtību rakurstabula, noklikšķiniet uz izvēlnes vienuma Rakurstabulas rīki/ Opcijas/ Darbības/ Notīrīt/ Notīrīt visu;
• Lauku saraksta augšdaļā atzīmējiet izvēles rūtiņas blakus laukiem Produkts un Pārdošana. Lauks Pārdošana automātiski tiks ievietots apgabalā Vērtības;
• Velciet un nometiet citu lauka Pārdošanas kopiju tajā pašā vērtību apgabalā. IN rakurstabula būs 2 kolonnas, kas skaitīs pārdošanas apjomu;

• V rakurstabula atlasiet jebkuru vērtību laukā Pārdošana, ar peles labo pogu noklikšķiniet uz konteksta izvēlnes un atlasiet Kopējās summas / daudzums. Problēma atrisināta.

Atspējot kopējo rindu skaitu

Kopējo summu joslu var atspējot, izmantojot izvēlni: Darbs ar PivotTables/ Konstruktors/ Izkārtojums/ Kopsummas. Neaizmirstiet iepriekš atlasīt jebkuru šūnu rakurstabula.

Skaitļu un datumu grupēšana

Pieņemsim, ka vēlaties sagatavot pārdošanas laika pārskatu. Rezultātā ir jāiegūst sekojoša informācija: cik Preču sūtījumi tika pārdoti laika posmā no 1 līdz 10 dienām, laika posmā no 11-20 dienām utt. Priekš šī:

• Notīrīt iepriekš izveidoto pārskatu: atlasiet jebkuru vērtību rakurstabula, noklikšķiniet uz izvēlnes vienuma Rakurstabulas rīki/ Opcijas/ Darbības/ Notīrīt/ Notīrīt visu;
• Lauku saraksta augšpusē atzīmējiet izvēles rūtiņu blakus laukam Pārdošana (Preču faktiskās pārdošanas datums). Lauks Pārdošana automātiski tiks ievietots apgabalā Vērtības;
• atlasiet vienīgo lauka Pārdošana vērtību rakurstabula, ar peles labo pogu izsauciet konteksta izvēlni un atlasiet vienumu Kopējās summas / daudzums.
• Velciet citu lauka Pārdošanas kopiju ar peli uz apgabalu Rindu nosaukumi;

Tagad rakurstabula parāda, cik Preču sūtījumi tika pārdoti 5, 6, 7, ... dienās. Kopā ir 66 rindas. Sagrupēsim vērtības ar soli 10. Lai to izdarītu:

• Iezīmējiet vienu vērtību rakurstabula ailē Rindu nosaukumi;
• Izvēlnē atlasiet vienumu Grupējiet pēc lauka;
• Aizpildiet parādīto logu, kā parādīts attēlā zemāk;

• Noklikšķiniet uz Labi.

Tagad rakurstabula parāda, cik Preču sūtījumu tika realizēts laika posmā no 1 līdz 10 dienām, laika posmā no 11-20 dienām utt.

Lai atgrupētu vērtības, atlasiet Atgrupēt izvēlnē Darbs ar PivotTables/ Options/ Group.

Līdzīgu grupēšanu var veikt laukā Piegādes datums. Šajā gadījumā logs Grupējiet pēc lauka izskatīsies šādi:

Tagad rakurstabula parāda, cik Preču sūtījumu tika piegādāts katru mēnesi.

Rakurstabulas šūnu nosacīta formatēšana

Uz šūnām rakurstabula varat lietot noteikumus tāpat kā parastā diapazona šūnām.
Izcelsim, piemēram, šūnas ar 10 lielākajiem pārdošanas apjomiem. Priekš šī:

• Atlasīt visas šūnas, kurās ir pārdošanas vērtības;
• Izvēlieties izvēlnes vienumu Sākums/ Stili/ Nosacījuma formatēšana/ Pirmās un pēdējās vērtības atlases noteikumi/ Pirmie 10 elementi;
• Noklikšķiniet uz Labi.