Inhoud
Draaitabellen zijn goed voor iedereen: ze berekenen snel, zijn flexibel geconfigureerd en het ontwerp kan er desgewenst elegant in worden gewikkeld. Maar er zijn ook een paar vliegen in de zalf, met name het onvermogen om een samenvatting te maken, waarbij het waardegebied geen cijfers, maar tekst moet bevatten.
Laten we proberen om deze beperking te omzeilen en een "paar krukken" bedenken in een vergelijkbare situatie.
Stel dat ons bedrijf zijn producten in containers vervoert naar verschillende steden in Ons Land en Kazachstan. Containers worden maximaal één keer per maand verzonden. Elke container heeft een alfanumeriek nummer. Als eerste gegevens is er een standaardtabel met leveringen, waaruit u een soort samenvatting moet maken om duidelijk het aantal containers te zien dat naar elke stad en elke maand wordt verzonden:
Laten we voor het gemak de tabel met de initiële gegevens van tevoren "slim" maken met behulp van de opdracht Home – Opmaak als tabel (Home - Opmaken als tabel) en geef haar een naam Leveringen tab Constructeur (Ontwerp). In de toekomst zal dit het leven vereenvoudigen, want. het is mogelijk om de naam van de tabel en de kolommen rechtstreeks in de formules te gebruiken.
Methode 1. De gemakkelijkste – gebruik Power Query
Power Query is een super krachtig hulpmiddel voor het laden en transformeren van gegevens in Excel. Deze add-in is sinds 2016 standaard ingebouwd in Excel. Als je Excel 2010 of 2013 hebt, kun je deze apart downloaden en installeren (geheel gratis).
Het hele proces heb ik voor de duidelijkheid stap voor stap geanalyseerd in de volgende video:
Als het niet mogelijk is om Power Query te gebruiken, kunt u andere manieren gebruiken: via een draaitabel of formules.
Methode 2. Aanvullende samenvatting
Laten we nog een kolom toevoegen aan onze oorspronkelijke tabel, waar we met behulp van een eenvoudige formule het aantal van elke rij in de tabel berekenen:
Het is duidelijk dat -1 nodig is, omdat we een kop van één regel in onze tabel hebben. Als uw tabel niet aan het begin van het blad staat, kunt u een iets complexere, maar universele formule gebruiken die het verschil in de nummers van de huidige rij en de tabelkop berekent:
Nu zullen we op een standaard manier een draaitabel van het gewenste type bouwen op basis van onze gegevens, maar in het waardeveld laten we het veld vallen Regelnummer in plaats van wat we willen houder:
Aangezien we in dezelfde maand niet meerdere containers in dezelfde stad hebben, geeft ons overzicht in feite niet het aantal, maar de regelnummers van de containers die we nodig hebben.
Bovendien kunt u eindtotalen en subtotalen uitschakelen op het tabblad Constructor – Algemene totalen и subtotalen (Ontwerp — Eindtotalen, Subtotalen) en op dezelfde plaats de samenvatting wijzigen in een handiger tabelindeling met de knop Mockup rapporteren (Rapportlay-out).
We zijn dus al halverwege het resultaat: we hebben een tabel waar, op het snijpunt van stad en maand, in de brontabel een rijnummer staat, waar de containercode staat die we nodig hebben.
Laten we nu de samenvatting kopiëren (naar hetzelfde blad of een ander) en deze als waarden plakken, en dan onze formule invoeren in het waardegebied, dat de containercode zal extraheren met het regelnummer in de samenvatting:
Functie IF (ALS), controleert in dit geval of de volgende cel in de samenvatting niet leeg is. Indien leeg, voer dan een lege tekstreeks uit "", dwz laat de cel leeg. Indien niet leeg, extraheer dan uit de kolom Containers brontabel Leveringen celinhoud op rijnummer met functie INDEX (INHOUDSOPGAVE).
Misschien is het enige niet erg voor de hand liggende punt hier het dubbele woord Containers in de formule. Wat een vreemde schrijfwijze:
Benodigdheden[[container]:[container]]
... is alleen nodig om naar de kolom te verwijzen Containers was absoluut (zoals een verwijzing met $-tekens voor gewone "niet-slimme" tabellen) en gleed niet naar aangrenzende kolommen bij het kopiëren van onze formule naar rechts.
In de toekomst, bij het wijzigen van de gegevens in de brontabel Leveringen, moeten we niet vergeten om onze hulpsamenvatting bij te werken met regelnummers door er met de rechtermuisknop op te klikken en de opdracht te kiezen Bijwerken en opslaan (Vernieuwen).
Methode 3. Formules
Voor deze methode is het niet nodig om een tussenliggende draaitabel te maken en handmatig bij te werken, maar het maakt gebruik van het "zware wapen" van Excel - de functie SUMMESLIMN (SOMMEN). In plaats van rijnummers in een samenvatting op te zoeken, kunt u ze met deze formule berekenen:
Met enige externe omvangrijkheid is dit in feite een standaard use-case voor de selectieve sommatiefunctie SUMMESLIMNA die de rijnummers voor de gegeven stad en maand optelt. Nogmaals, aangezien we in dezelfde maand niet meerdere containers in dezelfde stad hebben, zal onze functie in feite niet het bedrag geven, maar het regelnummer zelf. En dan de functie die al bekend is van de vorige methode INDEX U kunt ook containercodes extraheren:
In dit geval hoeft u natuurlijk niet meer na te denken over het bijwerken van de samenvatting, maar bij grote tabellen de functie SUMMESLI merkbaar traag kan zijn. Dan moet u het automatisch bijwerken van formules uitschakelen of de eerste methode gebruiken - een draaitabel.
Als het uiterlijk van de samenvatting niet erg geschikt is voor uw rapport, dan kunt u de rijnummers eruit halen in de finaletafel, niet direct, zoals wij deden, maar met behulp van de functie GET.DRAAITABEL.GEGEVENS (Haal.DRAAIEN.GEGEVENS). Hoe u dit doet, vindt u hier.
- Een rapport maken met een draaitabel
- Berekeningen instellen in draaitabellen
- Selectief tellen met SOMMEN, AANTALLEN, enz.