Contents [show]
Spiltafels is goed vir almal – hulle bereken vinnig en is buigsaam gekonfigureer, en die ontwerp kan elegant daarin gewikkel word, indien nodig. Maar daar is ook 'n paar vlieg in die salf, in die besonder, die onvermoë om 'n opsomming te skep, waar die waarde-area nie syfers moet bevat nie, maar teks.
Kom ons probeer om hierdie beperking te omseil en met 'n "paar krukke" vorendag te kom in 'n soortgelyke situasie.
Gestel ons maatskappy vervoer sy produkte in houers na verskeie stede in Ons land en Kazakstan. Houers word nie meer as een keer per maand gestuur nie. Elke houer het 'n alfanumeriese nommer. As aanvanklike data is daar 'n standaardtabel wat aflewerings bevat, waaruit u 'n soort opsomming moet maak om duidelik die aantal houers te sien wat na elke stad en elke maand gestuur word:
Kom ons maak die tabel met die aanvanklike data gerieflikheidshalwe vooraf "slim" deur die opdrag te gebruik Tuis – Formateer as 'n tabel (Tuis — Formateer as tabel) en gee haar 'n naam aflewerings tab konstruktor (Ontwerp). In die toekoms sal dit die lewe vereenvoudig, want. dit sal moontlik wees om die naam van die tabel en sy kolomme direk in die formules te gebruik.
Metode 1. Die maklikste – gebruik Power Query
Power Query is 'n super kragtige instrument om data in Excel te laai en te transformeer. Hierdie byvoeging is by verstek in Excel ingebou sedert 2016. As jy Excel 2010 of 2013 het, kan jy dit afsonderlik aflaai en installeer (heeltemal gratis).
Die hele proses, vir duidelikheid, het ek stap vir stap in die volgende video ontleed:
As dit nie moontlik is om Power Query te gebruik nie, kan jy ander maniere gaan – deur 'n spiltabel of formules.
Metode 2. Hulpopsomming
Kom ons voeg nog een kolom by ons oorspronklike tabel, waar ons met behulp van 'n eenvoudige formule die getal van elke ry in die tabel bereken:
Dit is duidelik dat -1 nodig is, want ons het 'n eenlyn-kopskrif in ons tabel. As jou tabel nie aan die begin van die blad is nie, kan jy 'n effens meer komplekse, maar universele formule gebruik wat die verskil in die getalle van die huidige ry en die tabelopskrif bereken:
Nou, op 'n standaard manier, sal ons 'n spiltabel van die verlangde tipe bou op grond van ons data, maar in die waardeveld sal ons die veld laat val Lyn nommer in plaas van wat ons wil hê houer:
Aangesien ons nie verskeie houers in dieselfde stad in dieselfde maand het nie, sal ons opsomming in werklikheid nie die bedrag gee nie, maar die lynnommers van die houers wat ons benodig.
Boonop kan u groot- en subtotale op die oortjie afskakel Konstrukteur – Algemene totale и Subtotale (Ontwerp – Groottotale, subtotale) en verander op dieselfde plek die opsomming na 'n geriefliker tabeluitleg met die knoppie Rapporteer mockup (Verslaguitleg).
Ons is dus reeds halfpad na die resultaat: ons het 'n tabel waar, by die kruising van die stad en maand, daar 'n rynommer in die brontabel is, waar die houerkode lê wat ons benodig.
Kom ons kopieer nou die opsomming (na dieselfde blad of 'n ander) en plak dit as waardes, en voer dan ons formule in die waardearea in, wat die houerkode sal onttrek deur die reëlnommer wat in die opsomming gevind word:
funksie IF (AS), in hierdie geval, kontroleer dat die volgende sel in die opsomming nie leeg is nie. Indien leeg, voer dan 'n leë teksstring “”, maw laat die sel leeg. As dit nie leeg is nie, haal dan uit die kolom Houer bron tabel aflewerings selinhoud volgens rynommer deur funksie te gebruik INDEKS (INDEKS).
Miskien is die enigste nie baie ooglopende punt hier die dubbele woord nie Houer in die formule. So 'n vreemde vorm van skryf:
Voorrade[[houer]:[houer]]
… is slegs nodig om na die kolom te verwys Houer was absoluut (soos 'n verwysing met $-tekens vir gewone "nie-slim" tabelle) en het nie na naburige kolomme gegly toe ons formule na regs gekopieer is nie.
In die toekoms, wanneer die data in die brontabel verander word aflewerings, moet ons onthou om ons hulpopsomming met reëlnommers op te dateer deur met die rechtermuisknop daarop te klik en die opdrag te kies Dateer op en stoor (Verfris).
Metode 3. Formules
Hierdie metode vereis nie die skep van 'n intermediêre spiltabel en handmatige opdatering nie, maar gebruik Excel se “swaar wapen” – die funksie SUMMESLIMN (SUMIFS). In plaas daarvan om rynommers in 'n opsomming op te soek, kan jy dit met behulp van hierdie formule bereken:
Met 'n mate van eksterne omvang, in werklikheid, is dit 'n standaard gebruiksgeval vir die selektiewe opsommingsfunksie SUMMESLIMNA wat die rynommers vir die gegewe stad en maand optel. Weereens, aangesien ons nie verskeie houers in dieselfde stad in dieselfde maand het nie, sal ons funksie in werklikheid nie die bedrag gee nie, maar die lynnommer self. En dan die funksie reeds bekend van die vorige metode INDEKS U kan ook houerkodes onttrek:
Natuurlik hoef u in hierdie geval nie meer daaraan te dink om die opsomming op te dateer nie, maar op groot tabelle is die funksie SUMMESLI kan merkbaar stadig wees. Dan sal jy outomatiese opdatering van formules moet afskakel, of die eerste metode moet gebruik - 'n spiltabel.
As die voorkoms van die opsomming nie baie geskik is vir jou verslag nie, dan kan jy rynommers daaruit in die finale tabel onttrek, nie direk soos ons gedoen het nie, maar met behulp van die funksie KRY.PIVOT.TABELDATA (GET.PIVOT.DATA). Hoe om dit te doen, kan hier gevind word.
- Hoe om 'n verslag te skep met behulp van 'n spiltabel
- Hoe om berekeninge in spilpunttabelle op te stel
- Selektiewe telling met SUMIFS, COUNTIFS, ens.