Pivot Dëscher si gutt fir jiddereen - si berechent séier, a si flexibel konfiguréiert, an den Design kann elegant an hinnen gewéckelt ginn, wann néideg. Mä et sinn och e puer fléien an der Sallef, besonnesch d'Onméiglechkeet e Resumé ze kreéieren, wou d'Wäertberäich keng Zuelen, mee Text enthält.
Loosst eis probéieren dës Begrenzung ëmzegoen an an enger ähnlecher Situatioun mat enger "Koppel Krut" ze kommen.
Ugeholl eis Firma transportéiert hir Produkter a Container a verschidde Stied an eisem Land a Kasachstan. Container ginn net méi wéi eemol de Mount geschéckt. All Container huet eng alphanumeresch Zuel. Als initial Donnéeën gëtt et e Standardtabel op der Lëscht vun Liwwerungen, aus deem Dir eng Aart vu Resumé maache musst fir kloer d'Zuel vun de Container ze gesinn, déi an all Stad an all Mount geschéckt ginn:
Fir d'Bequemlechkeet, loosst eis den Dësch mat den initialen Daten am Viraus mat dem Kommando "smart" maachen Home - Format als Dësch (Home - Format als Table) a ginn hir en Numm Liwwerungen Reiter Builder (Design). An Zukunft wäert dëst Liewen vereinfachen, well. et wäert méiglech sinn den Numm vun der Tabell a seng Kolonnen direkt an de Formelen ze benotzen.
Method 1. Am einfachsten - benotzen Power Query
Power Query ass e super mächtegt Tool fir Daten an Excel ze lueden an ze transforméieren. Dësen Add-in gouf als Standard an Excel gebaut zënter 2016. Wann Dir Excel 2010 oder 2013 hutt, kënnt Dir se separat eroflueden an installéieren (ganz gratis).
De ganze Prozess, fir Kloerheet, hunn ech Schrëtt fir Schrëtt am folgende Video analyséiert:
Wann et net méiglech ass Power Query ze benotzen, da kënnt Dir aner Weeër goen - duerch eng Pivot-Tabelle oder Formelen.
Method 2. Auxiliary Resumé
Loosst eis eng méi Kolonn un eis ursprénglech Tabelle addéieren, wou mir mat enger einfacher Formel d'Zuel vun all Zeil an der Tabell berechent:
Selbstverständlech ass -1 gebraucht, well mir hunn en One-Line Header an eiser Tabell. Wann Ären Dësch net um Ufank vum Blat ass, da kënnt Dir eng liicht méi komplex, awer universell Formel benotzen, déi den Ënnerscheed an den Zuelen vun der aktueller Zeil an der Tabelleheader berechent:
Elo, op eng Standard Manéier, bauen mir eng Pivot-Tabelle vum gewënschten Typ op Basis vun eisen Donnéeën, awer am Wäertfeld fale mir d'Feld erof Linn Nummer amplaz wat mir wëllen Container:
Well mir am selwechte Mount net e puer Container an der selwechter Stad hunn, wäert eise Resumé tatsächlech net de Betrag erausginn, mee d'Linnennummere vun de Container déi mir brauchen.
Zousätzlech kënnt Dir Grouss- an Ënnertotalen op der Tab ausschalten Konstrukteur - Allgemeng Gesamtbetrag и Subtotalen (Design - Grand Totals, Subtotals) an op der selwechter Plaz schalt de Resumé op eng méi praktesch Dësch Layout mat de Knäppchen Rapport mockup (Rapport Layout).
Sou si mer schonn um halwe Wee zum Resultat: Mir hunn eng Tabell, wou op der Kräizung vun der Stad a Mount eng Zeilnummer an der Quelltabel steet, wou de Containercode läit, dee mir brauchen.
Loosst eis elo de Resumé kopéieren (op datselwecht Blat oder en aneren) a paste se als Wäerter, a gitt dann eis Formel an d'Wäertberäich, déi de Containercode duerch d'Zeilnummer extrahéiert, déi am Resumé fonnt gëtt:
Funktioun IF (WANN), an dësem Fall kontrolléiert datt déi nächst Zell am Resumé net eidel ass. Wann eidel, gitt dann eng eidel Textstring "", also loosst d'Zelle eidel. Wann net eidel, Extrait aus der Kolonn Container Quell Dësch Liwwerungen Zell Inhalt vun Rei Zuel benotzt Funktioun INDEX (INDEX).
Vläicht ass deen eenzegen net ganz offensichtleche Punkt hei dat duebelt Wuert Container an der Formel. Sou eng komesch Form vu Schreiwen:
Ëmgeréits [[Container]:[Container]]
... ass nëmme gebraucht fir d'Kolonn ze referenzéieren Container war absolut (wéi eng Referenz mat $ Zeeche fir gewéinlech "net-smart" Dëscher) an ass net op d'Nopeschkolonnen rutscht wann Dir eis Formel no riets kopéiert.
An Zukunft, wann Dir d'Donnéeën an der Quelltabel ännert Liwwerungen, mir mussen drun erënneren, eisen Hëllefsresumé mat Zeilnummeren ze aktualiséieren andeems Dir op et klickt an de Kommando auswielen Update & späicheren (Erfrëschen).
Method 3. Formulen
Dës Method erfuerdert net d'Schafung vun enger Zwëschenpivottabell a manueller Aktualiséierung, awer benotzt d'Excel "schwéier Waff" - d'Funktioun SUMMESLIMN (SUMIFS). Amplaz d'Zeilennummeren an engem Resumé nozekucken, kënnt Dir se mat dëser Formel berechnen:
Mat e puer externen Bulkiness, tatsächlech, ass dëst e Standard Benotzungsfall fir déi selektiv Summatiounsfunktioun SUMMESLIMNA deen d'Zeilenzuele fir déi gegebene Stad a Mount summéiert. Nach eng Kéier, well mir am selwechte Mount net méi Container an der selwechter Stad hunn, gëtt eis Funktioun tatsächlech net de Betrag erausginn, mee d'Linnnummer selwer. An dann ass d'Funktioun schonn aus der viregter Method vertraut INDEX Dir kënnt och Containercodes extrahéieren:
Natierlech, an dësem Fall, braucht Dir net méi iwwer d'Aktualiséierung vum Resumé ze denken, awer op groussen Dëscher ass d'Funktioun SUMMESLI ka merkbar lues sinn. Da musst Dir automatesch d'Aktualiséierung vu Formelen ausschalten, oder déi éischt Method benotzen - eng Pivottabell.
Wann d'Erscheinung vum Resumé net ganz gëeegent ass fir Äre Bericht, da kënnt Dir Reihennummeren dovunner an d'Finale Dësch extrahéieren net direkt, wéi mir et gemaach hunn, awer mat der Funktioun GET.PIVOT.TABLE.DATA (GET.PIVOT.DATA). Wéi Dir dëst maacht, fannt Dir hei.
- Wéi een e Bericht erstellt mat enger Pivottabell
- Wéi Berechnungen a Pivottabellen opzestellen
- Selektiv zielen mat SUMIFS, COUNTIFS, etc.