It-tabelli tal-pern huma tajbin għal kulħadd - jikkalkulaw malajr, u huma kkonfigurati b'mod flessibbli, u d-disinn jista 'jiġi mdawwar fihom b'mod eleganti, jekk meħtieġ. Iżda hemm ukoll ftit fly fl-ingwent, b'mod partikolari, l-inabbiltà li jinħoloq sommarju, fejn iż-żona tal-valur m'għandux ikun fiha numri, iżda test.
Ejja nippruvaw nersqu din il-limitazzjoni u noħorġu b’“koppja ta’ krozzi” f’sitwazzjoni simili.
Ejja ngħidu li l-kumpanija tagħna tittrasporta l-prodotti tagħha f'kontenituri lejn diversi bliet f'Pajjiżna u l-Każakstan. Il-kontenituri jintbagħtu mhux aktar minn darba fix-xahar. Kull kontenitur għandu numru alfanumeriku. Bħala dejta inizjali, hemm tabella standard li telenka l-kunsinni, li minnha trid tagħmel xi tip ta’ sommarju sabiex tara b’mod ċar in-numri ta’ kontenituri mibgħuta lil kull belt u kull xahar:
Għall-konvenjenza, ejja nagħmlu t-tabella bid-dejta inizjali "intelliġenti" bil-quddiem billi tuża l-kmand Home – Format bħala tabella (Id-dar — Format bħala Tabella) u agħtiha isem Kunsinni tab kostruttur (Disinn). Fil-futur, dan se jissimplifika l-ħajja, għaliex. ikun possibbli li tuża l-isem tat-tabella u l-kolonni tagħha direttament fil-formuli.
Metodu 1. L-eħfef – uża Power Query
Power Query hija għodda super qawwija għat-tagħbija u t-trasformazzjoni tad-dejta f'Excel. Dan is-add-in ilu mibni f'Excel awtomatikament mill-2016. Jekk għandek Excel 2010 jew 2013, tista' tniżżel u tinstallah separatament (kompletament b'xejn).
Il-proċess kollu, għaċ-ċarezza, analizzajt pass pass fil-video li ġej:
Jekk ma jkunx possibbli li tuża Power Query, allura tista 'tmur modi oħra - permezz ta' tabella tal-pern jew formuli.
Metodu 2. Sommarju awżiljarju
Ejja nżidu kolonna oħra mat-tabella oriġinali tagħna, fejn bl-użu ta' formula sempliċi nikkalkulaw in-numru ta' kull ringiela fit-tabella:
Ovvjament, -1 huwa meħtieġ, għaliex għandna header ta 'linja waħda fit-tabella tagħna. Jekk it-tabella tiegħek mhix fil-bidu tal-folja, allura tista 'tuża formula kemmxejn aktar kumplessa, iżda universali li tikkalkula d-differenza fin-numri tar-ringiela attwali u l-intestatura tat-tabella:
Issa, b'mod standard, aħna se nibnu tabella tal-pern tat-tip mixtieq ibbażata fuq id-dejta tagħna, iżda fil-qasam tal-valur se niżlu l-qasam Numru tal-linja minflok dak li rridu kontenitur:
Peress li m'għandniex diversi kontenituri fl-istess belt fl-istess xahar, is-sommarju tagħna, fil-fatt, se jagħti mhux l-ammont, iżda n-numri tal-linji tal-kontenituri li għandna bżonn.
Barra minn hekk, tista 'titfi t-totali kbar u sekondarji fuq it-tab Kostruttur – Totali ġenerali и Subtotali (Disinn — Totali Grandi, Subtotali) u fl-istess post taqleb is-sommarju għal tqassim tal-mejda aktar konvenjenti bil-buttuna Irrapporta mockup (Layout tar-Rapport).
Għalhekk, aħna diġà qegħdin f'nofs ir-riżultat: għandna tabella fejn, fl-intersezzjoni tal-belt u x-xahar, hemm numru ta 'ringiela fit-tabella tas-sors, fejn jinsab il-kodiċi tal-kontenitur li għandna bżonn.
Issa ejja nikkopja s-sommarju (għall-istess folja jew oħra) u waħħalha bħala valuri, u mbagħad daħħal il-formula tagħna fiż-żona tal-valur, li se tiġbed il-kodiċi tal-kontenitur bin-numru tal-linja misjub fis-sommarju:
funzjoni IF (JEKK), f'dan il-każ, jiċċekkja li ċ-ċellula li jmiss fis-sommarju ma tkunx vojta. Jekk vojta, imbagħad toħroġ string ta' test vojta “”, jiġifieri ħalli ċ-ċellula vojta. Jekk mhux vojta, imbagħad estratt mill-kolonna Kontenitur tabella tas-sors Kunsinni kontenut taċ-ċelluli b'numru ta 'ringiela bl-użu tal-funzjoni INDIĊI (INDIĊI).
Forsi l-uniku punt mhux ovvju hawn huwa l-kelma doppja Kontenitur fil-formula. Forma stramba ta’ kitba bħal din:
Provvisti[[Kontenitur]:[Kontenitur]]
… hija meħtieġa biss biex tirreferi għall-kolonna Kontenitur kienet assoluta (bħal referenza b'sinjali $ għal tabelli ordinarji "mhux intelliġenti") u ma żelqux għal kolonni ġirien meta kkuppjat il-formula tagħna lejn il-lemin.
Fil-futur, meta tbiddel id-dejta fit-tabella tas-sors Kunsinni, irridu niftakru li naġġornaw is-sommarju awżiljarju tagħna bin-numri tal-linja billi nikklikkjaw il-lemin fuqha u nagħżlu l-kmand Aġġorna u Issejvja (Aġġorna).
Metodu 3. Formuli
Dan il-metodu ma jeħtieġx il-ħolqien ta 'tabella tal-pern intermedju u aġġornament manwali, iżda juża l-"arma tqil" ta' Excel - il-funzjoni SUMMESLIMN (SUMIFS). Minflok ma tfittex in-numri tar-ringieli f'sommarju, tista' tikkalkulahom billi tuża din il-formula:
B'xi bulkiness estern, fil-fatt, dan huwa każ ta 'użu standard għall-funzjoni ta' somma selettiva SUMMESLIMNA li tgħodd in-numri tar-ringiela għall-belt u x-xahar partikolari. Għal darb'oħra, peress li m'għandniex diversi kontenituri fl-istess belt fl-istess xahar, il-funzjoni tagħna, fil-fatt, se tagħti mhux l-ammont, iżda n-numru tal-linja innifsu. U mbagħad il-funzjoni diġà familjari mill-metodu preċedenti INDIĊI Tista' wkoll tiġbed il-kodiċijiet tal-kontenituri:
Naturalment, f'dan il-każ, m'għadx għandek bżonn taħseb dwar l-aġġornament tas-sommarju, iżda fuq tabelli kbar, il-funzjoni SUMMESLI jista' jkun notevoli bil-mod. Imbagħad ikollok itfi l-aġġornament awtomatiku tal-formuli, jew tuża l-ewwel metodu - tabella pivot.
Jekk id-dehra tas-sommarju ma tkunx adattata ħafna għar-rapport tiegħek, allura tista 'taħt in-numri ta' ringiela minnha fit-tabella finali mhux direttament, kif għamilna aħna, iżda billi tuża l-funzjoni IKOLLOK.DATA.TABELLA PIVOT (GET.PIVOT.DATA). Kif tagħmel dan tista 'tinstab hawn.
- Kif toħloq rapport billi tuża tabella pivot
- Kif twaqqaf kalkoli f'tabelli pivot
- Għadd selettiv b'SUMIFS, COUNTIFS, eċċ.