Saturs
Rakurstabulas ir piemērotas ikvienam – tās aprēķina ātri un ir elastīgi konfigurētas, un, ja nepieciešams, tajās var eleganti ietīt dizainu. Bet ir arī dažas muša, jo īpaši nespēja izveidot kopsavilkumu, kur vērtību apgabalā nevajadzētu būt skaitļiem, bet gan tekstam.
Mēģināsim apiet šo ierobežojumu un līdzīgā situācijā izdomāsim “pāris kruķus”.
Pieņemsim, ka mūsu uzņēmums transportē savu produkciju konteineros uz vairākām mūsu valsts un Kazahstānas pilsētām. Konteineri tiek nosūtīti ne biežāk kā reizi mēnesī. Katram konteineram ir burtciparu numurs. Kā sākotnējie dati ir standarta tabula ar piegāžu uzskaiti, no kuras jāizveido sava veida kopsavilkums, lai skaidri redzētu uz katru pilsētu un katru mēnesi nosūtīto konteineru skaitu:
Ērtības labad padarīsim tabulu ar sākotnējiem datiem “gudru”, izmantojot komandu Sākums — formatējiet kā tabulu (Sākums — formatēt kā tabulu) un dod viņai vārdu Piegādes tab Konstruktors (Dizains). Nākotnē tas vienkāršos dzīvi, jo. tabulas nosaukumu un tās kolonnas varēs izmantot tieši formulās.
1. metode. Vienkāršākais – izmantojiet Power Query
Power Query ir īpaši jaudīgs rīks datu ielādei un pārveidošanai programmā Excel. Šī pievienojumprogramma ir iebūvēta programmā Excel pēc noklusējuma kopš 2016. gada. Ja jums ir Excel 2010 vai 2013, varat to lejupielādēt un instalēt atsevišķi (pilnīgi bez maksas).
Skaidrības labad es soli pa solim analizēju visu procesu šajā videoklipā:
Ja nav iespējams izmantot Power Query, varat doties citā veidā – caur rakurstabulu vai formulām.
2. metode. Papildu kopsavilkums
Pievienosim vēl vienu kolonnu mūsu sākotnējai tabulai, kur, izmantojot vienkāršu formulu, mēs aprēķinām katras tabulas rindas skaitu:
Acīmredzot -1 ir vajadzīgs, jo mūsu tabulā ir vienas rindiņas galvene. Ja jūsu tabula nav lapas sākumā, varat izmantot nedaudz sarežģītāku, bet universālu formulu, kas aprēķina pašreizējās rindas un tabulas galvenes skaitļu atšķirību:
Tagad mēs standarta veidā izveidosim vajadzīgā tipa rakurstabulu, pamatojoties uz mūsu datiem, bet vērtības laukā mēs atmetīsim lauku Līnijas numurs tā vietā, ko mēs vēlamies konteiners:
Tā kā mums vienā un tajā pašā pilsētā nav vairāku konteineru vienā mēnesī, mūsu kopsavilkumā faktiski tiks norādīts nevis daudzums, bet gan mums nepieciešamo konteineru rindu numuri.
Turklāt cilnē varat izslēgt galvenās un starpsummas Konstruktors – vispārīgās summas и Starpsummas (Dizains — kopējās summas, starpsummas) un turpat ar pogu pārslēdziet kopsavilkumu uz ērtāku tabulas izkārtojumu Ziņojuma makets (Pārskata izkārtojums).
Tādējādi esam jau pusceļā uz rezultātu: mums ir tabula, kurā pilsētas un mēneša krustojumā avota tabulā ir rindas numurs, kurā atrodas mums nepieciešamais konteinera kods.
Tagad kopēsim kopsavilkumu (uz to pašu lapu vai citu) un ielīmēsim to kā vērtības un pēc tam ievadīsim mūsu formulu vērtību apgabalā, kas izvilks konteinera kodu pēc kopsavilkumā atrodamā rindas numura:
funkcija IF (JA), šajā gadījumā pārbauda, vai nākamā kopsavilkuma šūna nav tukša. Ja tukša, izvadiet tukšu teksta virkni “”, ti, atstājiet šūnu tukšu. Ja tas nav tukšs, izņemiet no kolonnas Konteiners avota tabula Piegādes šūnas saturu pēc rindas numura, izmantojot funkciju INDEX (INDEX).
Varbūt vienīgais ne pārāk acīmredzamais punkts šeit ir dubultvārds Konteiners formulā. Tāda dīvaina rakstīšanas forma:
Izejmateriāli[[Konteineris]:[Konteineris]]
… ir nepieciešama tikai atsaucei uz kolonnu Konteiners bija absolūts (piemēram, atsauce ar $ zīmēm parastajām “negudrajām” tabulām) un, kopējot mūsu formulu pa labi, neslīdēja blakus kolonnās.
Nākotnē, mainot datus avota tabulā Piegādes, mums jāatceras atjaunināt mūsu papildu kopsavilkumu ar rindu numuriem, ar peles labo pogu noklikšķinot uz tā un izvēloties komandu Atjaunināt un saglabāt (Atjaunot).
3. metode. Formulas
Šī metode neprasa starpposma rakurstabulas izveidi un manuālu atjaunināšanu, bet izmanto Excel "smago ieroci" - funkciju SUMMESLIMN (SUMIFS). Tā vietā, lai kopsavilkumā meklētu rindu numurus, varat tos aprēķināt, izmantojot šo formulu:
Faktiski šis ir selektīvās summēšanas funkcijas standarta lietošanas gadījums, ņemot vērā zināmu ārēju apjomu SUMMESLIMNA, kas summē dotās pilsētas un mēneša rindu numurus. Atkal, tā kā mums nav vairāku konteineru vienā pilsētā vienā un tajā pašā mēnesī, mūsu funkcija faktiski norādīs nevis summu, bet gan pašu rindas numuru. Un tad funkcija, kas jau pazīstama no iepriekšējās metodes INDEX Varat arī izvilkt konteineru kodus:
Protams, šajā gadījumā vairs nav jādomā par kopsavilkuma atjaunināšanu, bet uz lielām tabulām funkcija SUMMESLI var būt ievērojami lēns. Pēc tam būs jāizslēdz automātiskā formulu atjaunināšana vai jāizmanto pirmā metode – rakurstabula.
Ja kopsavilkuma izskats nav īpaši piemērots jūsu pārskatam, varat no tā izvilkt rindu numurus gala tabulā nevis tieši, kā mēs to darījām, bet izmantojot funkciju GET.PIVOT.TABLE.DATA (GET.PIVOT.DATA). Kā to izdarīt, var uzzināt šeit.
- Kā izveidot pārskatu, izmantojot rakurstabulu
- Kā iestatīt aprēķinus rakurstabulās
- Selektīva skaitīšana ar SUMIFS, COUNTIFS utt.