Excel life hacks for de som er involvert i rapportering og databehandling

2024 Forfatter: Malcolm Clapton | [email protected]. Sist endret: 2024-01-13 02:37

I dette innlegget deler Renat Shagabutdinov, assisterende generaldirektør for Mann, Ivanov og Ferber Publishing House, noen kule Excel-livshack. Disse tipsene vil være nyttige for alle som er involvert i ulike rapportering, databehandling og å lage presentasjoner.

Denne artikkelen inneholder enkle teknikker for å forenkle arbeidet ditt i Excel. De er spesielt nyttige for de som driver med ledelsesrapportering, utarbeider ulike analytiske rapporter basert på nedlastinger fra 1C og andre rapporter, danner presentasjoner og diagrammer fra dem for ledelsen. Jeg later ikke til å være absolutt nyhet - i en eller annen form ble disse teknikkene sannsynligvis diskutert på forumene eller nevnt i artikler.

Enkle alternativer til VLOOKUP og HLOOKUP, hvis de ønskede verdiene ikke er i den første kolonnen i tabellen: LOOKUP, INDEX + SEARCH

Funksjonene VLOOKUP og HLOOKUP fungerer bare hvis de ønskede verdiene er i den første kolonnen eller raden i tabellen som du planlegger å hente data fra.

Ellers er det to alternativer:

1. Bruk OPSLAKK-funksjonen.

   Den har følgende syntaks: LOOKUP (oppslagsverdi; oppslagsvektor; resultatvektor). Men for at det skal fungere riktig, må verdiene til view_vector-området sorteres i stigende rekkefølge:

   

2. Bruk en kombinasjon av MATCH- og INDEX-funksjoner.

   MATCH-funksjonen returnerer ordenstallet til et element i matrisen (med dens hjelp kan du finne i hvilken rad i tabellen det søkte elementet er), og INDEX-funksjonen returnerer et matriseelement med et gitt tall (som vi vil finne ut av ved å bruke MATCH-funksjonen).

   

   Funksjonssyntaks:

   • SØK (søkeverdi; søkematrise; samsvarstype) - for vårt tilfelle trenger vi en samsvarstype "eksakt samsvar", den tilsvarer tallet 0.

   • INDEX (matrise; linjenummer; [kolonne_nummer]). I dette tilfellet trenger du ikke spesifisere kolonnenummeret, siden matrisen består av én rad.

Hvordan raskt fylle tomme celler i en liste

Oppgaven er å fylle ut cellene i kolonnen med verdiene øverst (slik at emnet er i hver rad i tabellen, og ikke bare i den første raden i blokken med bøker om emnet):

Velg "Emne"-kolonnen, klikk på båndet i "Hjem"-gruppen, "Finn og velg"-knappen → "Velg en gruppe med celler" → "Blanke celler" og begynn å skrive inn formelen (det vil si, sett en lik tegn) og referer til cellen øverst, ganske enkelt ved å klikke pil opp på tastaturet. Trykk deretter Ctrl + Enter. Etter det kan du lagre de mottatte dataene som verdier, siden formlene ikke lenger er nødvendige:

Hvordan finne feil i en formel

Beregning av en egen del av en formel

For å forstå en kompleks formel (der andre funksjoner brukes som funksjonsargumenter, det vil si at noen funksjoner er nestet i andre) eller for å finne feilkilden i den, må du ofte beregne en del av den. Det er to enkle måter:

1. For å beregne en del av en formel rett i formellinjen, velg den delen og trykk F9:

   

   I dette eksemplet var det et problem med SEARCH-funksjonen - argumenter ble byttet i den. Det er viktig å huske at hvis du ikke avbryter beregningen av delen av funksjonen og trykker Enter, vil den beregnede delen forbli et tall.

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

   

   I vinduet som vises, kan du beregne formelen trinn for trinn og bestemme på hvilket stadium og i hvilken funksjon en feil oppstår (hvis noen):

   

Hvordan bestemme hva en formel avhenger av eller refererer til

For å finne ut hvilke celler en formel avhenger av, klikk på Påvirkende celler-knappen i Formler-gruppen på båndet:

Det vises piler for å indikere hva beregningsresultatet avhenger av.

Hvis symbolet uthevet i bildet i rødt vises, avhenger formelen av cellene på andre ark eller i andre bøker:

Ved å klikke på den kan vi se nøyaktig hvor de påvirkende cellene eller områdene befinner seg:

Ved siden av knappen "Influencing Cells" er knappen "Dependent Cells", som fungerer på samme måte: den viser piler fra den aktive cellen med en formel til cellene som er avhengige av den.

Knappen "Fjern piler", som ligger i samme blokk, lar deg fjerne piler til påvirkende celler, piler til avhengige celler, eller begge typer piler samtidig:

Hvordan finne summen (antall, gjennomsnitt) av celleverdier fra flere ark

La oss si at du har flere ark av samme type med data som du vil legge til, telle eller behandle på en annen måte:

For å gjøre dette, i cellen der du vil se resultatet, skriv inn en standardformel, for eksempel SUM (SUM), og spesifiser navnet på det første og siste arket fra listen over de arkene du må behandle i argumentet, atskilt med et kolon:

Du vil motta summen av celler med adressen B3 fra arkene "Data1", "Data2", "Data3":

Denne adresseringen fungerer for ark som er plassert konsekvent … Syntaksen er som følger: = FUNKSJON (første_liste: siste_liste! Områdereferanse).

Hvordan bygge malfraser automatisk

Ved å bruke de grunnleggende prinsippene for å jobbe med tekst i Excel og noen få enkle funksjoner, kan du utarbeide malfraser for rapporter. Flere prinsipper for å jobbe med tekst:

• Vi setter sammen teksten ved å bruke &-tegnet (du kan erstatte den med CONCATENATE-funksjonen, men det gir ikke mye mening).
• Teksten er alltid skrevet i anførselstegn, referanser til celler med tekst er alltid uten.
• For å få tjenestetegnet "anførselstegn", bruk CHAR-funksjonen med argument 32.

Et eksempel på å lage en malfrase ved hjelp av formler:

Resultat:

I dette tilfellet, i tillegg til CHAR-funksjonen (for å vise tilbud), brukes IF-funksjonen, som lar deg endre teksten avhengig av om det er en positiv salgstrend, og TEXT-funksjonen, som lar deg vise nummer i alle formater. Syntaksen er beskrevet nedenfor:

TEKST (verdi; format)

Formatet er angitt i anførselstegn, akkurat som om du skulle angi et tilpasset format i vinduet Formater celler.

Mer komplekse tekster kan også automatiseres. I min praksis var det automatisering av lange, men rutinemessige kommentarer til ledelsesrapportering i formatet INDIKATOR falt / steg med XX i forhold til planen, hovedsakelig på grunn av vekst / nedgang av FACTOR1 med XX, vekst / nedgang av FACTOR2 med YY …” med en skiftende liste over faktorer. Hvis du skriver slike kommentarer ofte og prosessen med å skrive dem kan algoritmiseres, er det verdt å lage en formel eller en makro som sparer deg i det minste noe av arbeidet.

Hvordan lagre data i hver celle etter sammenkobling

Når du slår sammen celler, beholdes bare én verdi. Excel advarer om dette når du prøver å slå sammen celler:

Følgelig, hvis du hadde en formel avhengig av hver celle, vil den slutte å fungere etter å ha kombinert dem (# N / A feil i linjene 3-4 i eksemplet):

For å slå sammen celler og fortsatt bevare dataene i hver av dem (kanskje du har en formel som i dette abstrakte eksemplet; kanskje du vil slå sammen celler, men beholde alle data for fremtiden eller skjule dem med vilje), slå sammen alle celler på arket, velg dem, og bruk deretter Format Painter-kommandoen til å overføre formateringen til cellene du må kombinere:

Hvordan bygge en pivot fra flere datakilder

Hvis du trenger å bygge en pivot fra flere datakilder samtidig, må du legge til "Pivottabell- og diagramveiviseren" til båndet eller hurtigtilgangspanelet, som har et slikt alternativ.

Du kan gjøre dette på følgende måte: "Fil" → "Alternativer" → "Hurtigtilgangsverktøylinje" → "Alle kommandoer" → "Pivottabell og diagramveiviser" → "Legg til":

Etter det vil et tilsvarende ikon vises på båndet, ved å klikke på som kaller den samme veiviseren:

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

I den må du velge elementet "I flere konsolideringsområder" og klikke på "Neste". I neste trinn kan du velge "Opprett ett sidefelt" eller "Opprett sidefelt". Hvis du vil uavhengig komme opp med et navn for hver av datakildene, velg det andre elementet:

I det neste vinduet legger du til alle områdene som pivoten skal bygges på, og gi dem navn:

Etter det, i den siste dialogboksen, spesifiser hvor pivottabellrapporten skal plasseres - på et eksisterende eller nytt ark:

Pivottabellrapporten er klar. I «Side 1»-filteret kan du bare velge én av datakildene, om nødvendig:

Hvordan beregne antall forekomster av tekst A i tekst B ("MTS SuperMTS-tariff" - to forekomster av forkortelsen MTS)

I dette eksemplet inneholder kolonne A flere tekstlinjer, og vår oppgave er å finne ut hvor mange ganger hver av dem inneholder søketeksten i celle E1:

For å løse dette problemet kan du bruke en kompleks formel som består av følgende funksjoner:

1. DLSTR (LEN) - beregner lengden på teksten, det eneste argumentet er teksten. Eksempel: DLSTR ("maskin") = 6.
2. ERSTATT - erstatter en bestemt tekst i en tekststreng med en annen. Syntaks: SUBSTITUTE (tekst; gammel_tekst; ny_tekst). Eksempel: SUBSTITUTE ("bil"; "bil"; "") = "mobil".
3. UPPER - erstatter alle tegn i en streng med store bokstaver. Det eneste argumentet er tekst. Eksempel: ØVRE ("maskin") = "BIL". Vi trenger denne funksjonen for å gjøre søk som ikke skiller mellom store og små bokstaver. Tross alt, UPPER ("bil") = UPPER ("Maskin")

For å finne forekomsten av en bestemt tekststreng i en annen, må du slette alle forekomstene i den opprinnelige og sammenligne lengden på den resulterende strengen med den originale:

DLSTR («Tariff MTS Super MTS») - DLSTR («Tariff Super») = 6

Og del deretter denne forskjellen med lengden på strengen vi lette etter:

6 / DLSTR ("MTS") = 2

Det er nøyaktig to ganger at linjen "MTS" er inkludert i den originale.

Det gjenstår å skrive denne algoritmen på formlerspråket (la oss betegne med "tekst" teksten der vi leter etter forekomster, og med "søkt" - den hvis antall forekomster vi er interessert i):

= (DLSTR (tekst) -LSTR (SUBSTITUTE (UPPER (tekst); UPPER (søk), ""))) / DLSTR (søk)

I vårt eksempel ser formelen slik ut:

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