Excel life-hacks voor degenen die betrokken zijn bij rapportage en gegevensverwerking

2024 Auteur: Malcolm Clapton | [email protected]. Laatst gewijzigd: 2024-01-13 02:34

In dit bericht deelt Renat Shagabutdinov, adjunct-algemeen directeur van Mann, Ivanov en Ferber Publishing House, enkele coole Excel-levenshacks. Deze tips zijn nuttig voor iedereen die betrokken is bij verschillende rapportages, gegevensverwerking en het maken van presentaties.

Dit artikel bevat eenvoudige technieken om uw werk in Excel te vereenvoudigen. Ze zijn vooral handig voor degenen die zich bezighouden met managementrapportage, het opstellen van verschillende analytische rapporten op basis van downloads van 1C en andere rapporten, en daaruit presentaties en diagrammen maken voor het management. Ik pretendeer niet absoluut nieuw te zijn - in een of andere vorm zijn deze technieken waarschijnlijk besproken op de forums of genoemd in artikelen.

Eenvoudige alternatieven voor VERT. ZOEKEN en HORIZ. ZOEKEN, als de gewenste waarden niet in de eerste kolom van de tabel staan: ZOEKEN, INDEX + ZOEKEN

De functies VERT. ZOEKEN en HORIZ. ZOEKEN werken alleen als de gewenste waarden in de eerste kolom of rij van de tabel staan waaruit u gegevens wilt halen.

Anders zijn er twee opties:

1. Gebruik de ZOEKEN-functie.

   Het heeft de volgende syntaxis: LOOKUP (lookup_value; lookup_vector; result_vector). Maar om het correct te laten werken, moeten de waarden van het view_vector-bereik in oplopende volgorde worden gesorteerd:

   

2. Gebruik een combinatie van MATCH- en INDEX-functies.

   De MATCH-functie retourneert het rangtelwoord van een element in de array (met zijn hulp kun je vinden in welke rij van de tabel het gezochte element zich bevindt), en de INDEX-functie retourneert een array-element met een bepaald nummer (wat we zullen ontdekken met behulp van de MATCH-functie).

   

   Functiesyntaxis:

   • SEARCH (search_value; search_array; match_type) - voor ons geval hebben we een overeenkomend type "exact match" nodig, dit komt overeen met het nummer 0.

   • INDEX (matrix; regelnummer; [kolomnummer]). In dit geval hoeft u het kolomnummer niet op te geven, aangezien de array uit één rij bestaat.

Snel lege cellen in een lijst vullen

De taak is om de cellen in de kolom in te vullen met de waarden bovenaan (zodat het onderwerp in elke rij van de tabel staat, en niet alleen in de eerste rij van het blok met boeken over het onderwerp):

Selecteer de kolom "Onderwerp", klik op het lint in de groep "Home", de knop "Zoeken en selecteren" → "Selecteer een groep cellen" → "Lege cellen" en begin met het invoeren van de formule (dat wil zeggen, zet een gelijk aan teken) en verwijs naar de cel bovenaan door simpelweg op de pijl omhoog op uw toetsenbord te klikken. Druk daarna op Ctrl + Enter. Daarna kunt u de ontvangen gegevens als waarden opslaan, omdat de formules niet langer nodig zijn:

Fouten in een formule vinden

Berekening van een afzonderlijk deel van een formule

Om een complexe formule te begrijpen (waarin andere functies worden gebruikt als functieargumenten, dat wil zeggen sommige functies zijn genest in andere) of om de bron van fouten erin te vinden, moet u er vaak een deel van berekenen. Er zijn twee eenvoudige manieren:

1. Om een deel van een formule direct in de formulebalk te berekenen, selecteert u dat deel en drukt u op F9:

   

   In dit voorbeeld was er een probleem met de functie ZOEKEN - daarin werden argumenten verwisseld. Het is belangrijk om te onthouden dat als u de berekening van het deel van de functie niet annuleert en op Enter drukt, het berekende deel een getal blijft.

2. Klik op de knop Formule berekenen in de groep Formules op het lint:

   

   In het venster dat verschijnt, kunt u stap voor stap de formule berekenen en bepalen in welk stadium en in welke functie een fout optreedt (indien aanwezig):

   

Hoe te bepalen waar een formule van afhankelijk is of naar verwijst?

Om te bepalen van welke cellen een formule afhankelijk is, klikt u in de groep Formules op het lint op de knop Betreffende cellen:

Er verschijnen pijlen om aan te geven waar het rekenresultaat van afhangt.

Als het in de afbeelding rood gemarkeerde symbool wordt weergegeven, is de formule afhankelijk van de cellen op andere bladen of in andere boeken:

Door erop te klikken, kunnen we precies zien waar de beïnvloedende cellen of bereiken zich bevinden:

Naast de knop "Beïnvloeding van cellen" bevindt zich de knop "Afhankelijke cellen", die op dezelfde manier werkt: het toont pijlen van de actieve cel met een formule naar de cellen die ervan afhankelijk zijn.

Met de knop "Pijlen verwijderen", die zich in hetzelfde blok bevindt, kunt u pijlen naar beïnvloedende cellen, pijlen naar afhankelijke cellen of beide soorten pijlen tegelijk verwijderen:

Hoe de som (aantal, gemiddelde) celwaarden van meerdere bladen te vinden

Stel dat u meerdere bladen van hetzelfde type heeft met gegevens die u wilt toevoegen, tellen of op een andere manier wilt verwerken:

Om dit te doen, voert u in de cel waarin u het resultaat wilt zien een standaardformule in, bijvoorbeeld SUM (SUM), en specificeert u de naam van het eerste en laatste blad uit de lijst met die bladen die u moet verwerken in het argument, gescheiden door een dubbele punt:

U ontvangt de som van cellen met het adres B3 uit de bladen "Data1", "Data2", "Data3":

Deze adressering werkt voor bladen die zich bevinden consequent … De syntaxis is als volgt: = FUNCTIE (first_list: last_list! Range reference).

Hoe u automatisch sjabloonzinnen kunt maken

Met behulp van de basisprincipes van het werken met tekst in Excel en een paar eenvoudige functies, kunt u sjabloonfrases voor rapporten voorbereiden. Enkele principes van het werken met tekst:

• We voegen de tekst samen met het &-teken (je kunt het vervangen door de CONCATENATE-functie, maar dat heeft weinig zin).
• De tekst staat altijd tussen aanhalingstekens, verwijzingen naar cellen met tekst zijn altijd zonder.
• Gebruik de functie CHAR met argument 32 om het serviceteken "aanhalingstekens" te krijgen.

Een voorbeeld van het maken van een sjabloonfrase met formules:

Resultaat:

In dit geval wordt naast de CHAR-functie (om aanhalingstekens weer te geven), de ALS-functie gebruikt, waarmee u de tekst kunt wijzigen afhankelijk van of er een positieve verkooptrend is, en de TEXT-functie, waarmee u de nummer in elk formaat. De syntaxis wordt hieronder beschreven:

TEKST (waarde; formaat)

Het formaat wordt gespecificeerd tussen aanhalingstekens, net alsof u een aangepast formaat invoert in het venster Cellen opmaken.

Ook complexere teksten kunnen worden geautomatiseerd. In mijn praktijk was er de automatisering van lange, maar routinematige opmerkingen aan managementrapportage in het formaat INDICATOR daalde / steeg met XX ten opzichte van het plan, voornamelijk als gevolg van de groei / afname van FACTOR1 met XX, groei / afname van FACTOR2 met YY …” met een wisselende lijst van factoren. Als u dergelijke opmerkingen vaak schrijft en het proces om ze te schrijven kan worden gealgoritmed, is het de moeite waard om een keer te puzzelen om een formule of een macro te maken die u op zijn minst een deel van het werk zal besparen.

Gegevens in elke cel opslaan na aaneenschakeling

Wanneer u cellen samenvoegt, blijft slechts één waarde behouden. Excel waarschuwt hiervoor bij het samenvoegen van cellen:

Dienovereenkomstig, als u een formule had die afhankelijk was van elke cel, zal deze niet meer werken nadat u ze hebt gecombineerd (# N / A-fout in regel 3-4 van het voorbeeld):

Om cellen samen te voegen en toch de gegevens in elk ervan te behouden (misschien heb je een formule zoals in dit abstracte voorbeeld; misschien wil je cellen samenvoegen, maar alle gegevens voor de toekomst bewaren of opzettelijk verbergen), voeg alle cellen op het blad samen, selecteer ze en gebruik vervolgens de opdracht Schilder opmaken om de opmaak over te brengen naar de cellen die u moet combineren:

Een spil bouwen op basis van meerdere gegevensbronnen

Als u een spil uit meerdere gegevensbronnen tegelijk moet maken, moet u de "Wizard Draaitabel en Grafiek" toevoegen aan het lint of paneel voor snelle toegang, dat een dergelijke optie heeft.

U kunt dit als volgt doen: "Bestand" → "Opties" → "Snelle toegang werkbalk" → "Alle opdrachten" → "Wizard draaitabel en diagram" → "Toevoegen":

Daarna verschijnt een bijbehorend pictogram op het lint, waarop wordt geklikt en dezelfde wizard wordt opgeroepen:

Als u erop klikt, verschijnt er een dialoogvenster:

Daarin moet u het item "In verschillende consolidatiebereiken" selecteren en op "Volgende" klikken. In de volgende stap kunt u "Creëer één paginaveld" of "Creëer paginavelden" selecteren. Als u voor elk van de gegevensbronnen onafhankelijk een naam wilt bedenken, selecteert u het tweede item:

Voeg in het volgende venster alle bereiken toe op basis waarvan de spil zal worden gebouwd, en geef ze namen:

Geef daarna in het laatste dialoogvenster op waar het draaitabelrapport wordt geplaatst - op een bestaand of nieuw blad:

Het draaitabelrapport is klaar. In het filter "Pagina 1" kunt u indien nodig slechts één van de gegevensbronnen selecteren:

Hoe bereken je het aantal keren dat tekst A in tekst B voorkomt ("MTS SuperMTS-tarief" - twee keer dat de afkorting MTS voorkomt)

In dit voorbeeld bevat kolom A verschillende tekstregels en het is onze taak om uit te zoeken hoe vaak elk van hen de zoektekst in cel E1 bevat:

Om dit probleem op te lossen, kunt u een complexe formule gebruiken die uit de volgende functies bestaat:

1. DLSTR (LEN) - berekent de lengte van de tekst, het enige argument is de tekst. Voorbeeld: DLSTR ("machine") = 6.
2. VERVANGING - vervangt een specifieke tekst in een tekstreeks door een andere. Syntaxis: SUBSTITUTE (tekst; oude_tekst; nieuwe_tekst). Voorbeeld: VERVANGING (“auto”; “auto”; “”) = “mobiel”.
3. UPPER - vervangt alle tekens in een string door hoofdletters. Het enige argument is tekst. Voorbeeld: BOVEN (“machine”) = “CAR”. We hebben deze functie nodig om hoofdletterongevoelige zoekopdrachten uit te voeren. Immers, BOVEN ("auto") = BOVEN ("Machine")

Om het voorkomen van een bepaalde tekenreeks in een andere te vinden, moet u alle vermeldingen in de oorspronkelijke reeks verwijderen en de lengte van de resulterende tekenreeks vergelijken met de originele:

DLSTR ("Tarief MTS Super MTS") - DLSTR ("Tarief Super") = 6

En deel dit verschil dan door de lengte van de string die we zochten:

6 / DLSTR (“MTS”) = 2

Het is precies twee keer dat de regel "MTS" in de originele is opgenomen.

Het blijft om dit algoritme te schrijven in de taal van formules (laten we de tekst waarin we naar gebeurtenissen zoeken met "tekst" aanduiden, en met "gezocht" - degene in wiens aantal gevallen we geïnteresseerd zijn):

= (DLSTR (tekst) -LSTR (VERVANGING (UPPER (tekst); UPPER (zoeken), ""))) / DLSTR (zoeken)

In ons voorbeeld ziet de formule er als volgt uit:

= (DLSTR (A2) -LSTR (VERVANGING (BOVENSTE (A2), BOVENSTE ($ E $ 1), ""))) / DLSTR ($ E $ 1)