Excel life hacks for dem, der er involveret i rapportering og databehandling

2024 Forfatter: Malcolm Clapton | [email protected]. Sidst ændret: 2024-01-13 01:04

I dette indlæg deler Renat Shagabutdinov, assisterende generaldirektør for Mann, Ivanov og Ferber Publishing House, nogle seje Excel life hacks. Disse tips vil være nyttige for alle, der er involveret i forskellige rapportering, databehandling og oprettelse af præsentationer.

Denne artikel indeholder enkle teknikker til at forenkle dit arbejde i Excel. De er især nyttige for dem, der er engageret i ledelsesrapportering, udarbejder forskellige analytiske rapporter baseret på downloads fra 1C og andre rapporter, danner præsentationer og diagrammer fra dem til ledelsen. Jeg foregiver ikke at være absolut nyhed - i en eller anden form blev disse teknikker sandsynligvis diskuteret på fora eller nævnt i artikler.

Simple alternativer til VLOOKUP og HLOOKUP, hvis de ønskede værdier ikke er i den første kolonne i tabellen: LOOKUP, INDEX + SEARCH

Funktionerne VLOOKUP og HLOOKUP fungerer kun, hvis de ønskede værdier er i den første kolonne eller række i tabellen, hvorfra du planlægger at hente data.

Ellers er der to muligheder:

1. Brug OPSLAG-funktionen.

   Den har følgende syntaks: LOOKUP (opslagsværdi; opslagsvektor; resultatvektor). Men for at det skal fungere korrekt, skal værdierne for view_vector-området sorteres i stigende rækkefølge:

   

2. Brug en kombination af MATCH og INDEX funktioner.

   MATCH-funktionen returnerer ordenstallet for et element i arrayet (med dens hjælp kan du finde i hvilken række i tabellen det søgte element er), og INDEX-funktionen returnerer et array-element med et givet tal (som vi finder ud af ved at bruge MATCH-funktionen).

   

   Funktionssyntaks:

   • SØG (search_value; search_array; match_type) - for vores tilfælde har vi brug for en matchende type "eksakt match", den svarer til tallet 0.

   • INDEX (matrix; linjenummer; [kolonne_nummer]). I dette tilfælde behøver du ikke at angive kolonnenummeret, da arrayet består af én række.

Sådan udfylder du hurtigt tomme celler i en liste

Opgaven er at udfylde cellerne i kolonnen med værdierne øverst (så emnet er i hver række af tabellen, og ikke kun i den første række af blokken af bøger om emnet):

Vælg kolonnen "Emne", klik på båndet i gruppen "Hjem", knappen "Find og vælg" → "Vælg en gruppe af celler" → "Blanke celler" og begynd at indtaste formlen (det vil sige, sæt en lig tegn) og referer til cellen øverst ved blot at klikke på pil op på dit tastatur. Tryk derefter på Ctrl + Enter. Derefter kan du gemme de modtagne data som værdier, da formlerne ikke længere er nødvendige:

Sådan finder du fejl i en formel

Beregning af en separat del af en formel

For at forstå en kompleks formel (hvor andre funktioner bruges som funktionsargumenter, dvs. nogle funktioner er indlejret i andre) eller for at finde fejlkilden i den, skal du ofte beregne en del af den. Der er to nemme måder:

1. For at beregne en del af en formel lige i formellinjen skal du vælge den del og trykke på F9:

   

   I dette eksempel var der et problem med SEARCH-funktionen - argumenter blev byttet om i den. Det er vigtigt at huske, at hvis du ikke annullerer beregningen af delen af funktionen og trykker på Enter, så forbliver den beregnede del et tal.

2. Klik på knappen Beregn formel i gruppen Formler på båndet:

   

   I vinduet, der vises, kan du beregne formlen trin for trin og bestemme på hvilket stadium og i hvilken funktion en fejl opstår (hvis nogen):

   

Hvordan bestemmer man, hvad en formel afhænger af eller refererer til

For at bestemme, hvilke celler en formel afhænger af, skal du i gruppen Formler på båndet klikke på knappen Påvirkende celler:

Der vises pile for at angive, hvad beregningsresultatet afhænger af.

Hvis symbolet, der er fremhævet i billedet med rødt, vises, afhænger formlen af cellerne på andre ark eller i andre bøger:

Ved at klikke på den kan vi se præcis, hvor de påvirkende celler eller områder er placeret:

Ved siden af knappen "Influencing Cells" er knappen "Dependent Cells", som fungerer på samme måde: den viser pile fra den aktive celle med en formel til de celler, der afhænger af den.

Knappen "Fjern pile", placeret i samme blok, giver dig mulighed for at fjerne pile til påvirkende celler, pile til afhængige celler eller begge typer pile på én gang:

Sådan finder du summen (antal, gennemsnit) af celleværdier fra flere ark

Lad os sige, at du har flere ark af samme type med data, som du vil tilføje, tælle eller behandle på en anden måde:

For at gøre dette skal du i den celle, hvor du vil se resultatet, indtaste en standardformel, for eksempel SUM (SUM), og angive navnet på det første og sidste ark fra listen over de ark, som du skal behandle i argumentet, adskilt af et kolon:

Du vil modtage summen af celler med adressen B3 fra arkene "Data1", "Data2", "Data3":

Denne adressering fungerer for ark placeret konsekvent … Syntaksen er som følger: = FUNKTION (første_liste: sidste_liste! Områdereference).

Sådan bygger du automatisk skabelonsætninger

Ved at bruge de grundlæggende principper for at arbejde med tekst i Excel og nogle få simple funktioner kan du udarbejde skabelonsætninger til rapporter. Flere principper for at arbejde med tekst:

• Vi sammenkæder teksten ved hjælp af &-tegnet (du kan erstatte det med CONCATENATE-funktionen, men det giver ikke meget mening).
• Teksten er altid skrevet i anførselstegn, henvisninger til celler med tekst er altid uden.
• For at få servicetegnet "anførselstegn" skal du bruge CHAR-funktionen med argument 32.

Et eksempel på oprettelse af en skabelonsætning ved hjælp af formler:

Resultat:

I dette tilfælde anvendes, udover CHAR-funktionen (til at vise tilbud), IF-funktionen, som giver dig mulighed for at ændre teksten alt efter om der er en positiv salgstendens, og TEXT-funktionen, som giver dig mulighed for at vise nummer i ethvert format. Dens syntaks er beskrevet nedenfor:

TEKST (værdi; format)

Formatet er angivet i anførselstegn, ligesom hvis du indtastede et brugerdefineret format i vinduet Formater celler.

Mere komplekse tekster kan også automatiseres. I min praksis var der automatisering af lange, men rutinemæssige kommentarer til ledelsesrapportering i formatet INDIKATOR faldt / steg med XX i forhold til planen, hovedsageligt på grund af væksten / faldet i FACTOR1 med XX, væksten / faldet i FACTOR2 med YY …” med en skiftende liste over faktorer. Hvis du skriver sådanne kommentarer ofte, og processen med at skrive dem kan algoritmiseres, er det umagen værd at lave en formel eller en makro, der sparer dig i det mindste noget af arbejdet.

Sådan gemmer du data i hver celle efter sammenkædning

Når du flette celler, bevares kun én værdi. Excel advarer om dette, når du prøver at flette celler:

Derfor, hvis du havde en formel afhængig af hver celle, vil den stoppe med at fungere efter at have kombineret dem (# N / A fejl i linje 3-4 i eksemplet):

For at flette celler og stadig bevare dataene i hver af dem (måske har du en formel som i dette abstrakte eksempel; måske vil du flette celler, men beholde alle data for fremtiden eller skjule dem med vilje), flet alle celler på arket, vælg dem, og brug derefter kommandoen Format Painter til at overføre formateringen til de celler, du skal kombinere:

Sådan bygger du en pivot fra flere datakilder

Hvis du skal bygge en pivot fra flere datakilder på én gang, bliver du nødt til at tilføje "Pivottabel og diagramguiden" til båndet eller hurtigadgangspanelet, som har en sådan mulighed.

Du kan gøre dette på følgende måde: "Filer" → "Indstillinger" → "Hurtig adgangsværktøjslinje" → "Alle kommandoer" → "Pivottabel og diagramguide" → "Tilføj":

Derefter vises et tilsvarende ikon på båndet, ved at klikke på hvilket kalder den samme guide:

Når du klikker på den, vises en dialogboks:

I den skal du vælge elementet "I flere konsolideringsintervaller" og klikke på "Næste". I næste trin kan du vælge "Opret et sidefelt" eller "Opret sidefelter". Hvis du uafhængigt vil finde på et navn til hver af datakilderne, skal du vælge det andet punkt:

I det næste vindue skal du tilføje alle de områder, som pivoten vil blive bygget på grundlag af, og give dem navne:

Derefter skal du i den sidste dialogboks angive, hvor pivottabelrapporten skal placeres - på et eksisterende eller nyt ark:

Pivottabelrapporten er klar. I filteret "Side 1" kan du kun vælge én af datakilderne, hvis det er nødvendigt:

Sådan beregnes antallet af forekomster af tekst A i tekst B ("MTS SuperMTS-takst" - to forekomster af forkortelsen MTS)

I dette eksempel indeholder kolonne A flere tekstlinjer, og vores opgave er at finde ud af, hvor mange gange hver af dem indeholder søgeteksten i celle E1:

For at løse dette problem kan du bruge en kompleks formel, der består af følgende funktioner:

1. DLSTR (LEN) - beregner længden af teksten, det eneste argument er teksten. Eksempel: DLSTR ("maskine") = 6.
2. ERSTAT - erstatter en bestemt tekst i en tekststreng med en anden. Syntaks: SUBSTITUTE (tekst; gammel_tekst; ny_tekst). Eksempel: SUBSTITUTE ("bil"; "auto"; "") = "mobil".
3. UPPER - erstatter alle tegn i en streng med store bogstaver. Det eneste argument er tekst. Eksempel: ØVRE ("maskine") = "BIL". Vi har brug for denne funktion for at udføre søgninger, der ikke er følsomme over for store og små bogstaver. Efter alt, UPPER ("bil") = UPPER ("Maskin")

For at finde forekomsten af en bestemt tekststreng i en anden, skal du slette alle dens forekomster i den originale og sammenligne længden af den resulterende streng med den originale:

DLSTR (“Tarif MTS Super MTS”) - DLSTR (“Tarif Super”) = 6

Og divider derefter denne forskel med længden af den streng, vi ledte efter:

6 / DLSTR ("MTS") = 2

Det er præcis to gange, at linjen "MTS" er inkluderet i den originale.

Det er tilbage at skrive denne algoritme på formlersproget (lad os med "tekst" betegne den tekst, som vi leder efter forekomster i, og med "søgt" - den, hvis antal forekomster vi er interesserede i):

= (DLSTR (tekst) -LSTR (SUBSTITUTE (ØVRE (tekst); UPPER (søg), ""))) / DLSTR (søg)

I vores eksempel ser formlen sådan ud:

= (DLSTR (A2) -LSTR (SUBSTITUTE (ØVRE (A2), ØVRE ($ E $ 1), “”))) / DLSTR ($ E $ 1)