Excel-lifehack for dem, der er involveret i rapportering og databehandling

Renat er ikke første gang en gæst forfatter taler om Layfhakere. Tidligere offentliggjorde vi et glimrende materiale fra ham om, hvordan du opretter en uddannelsesplan: vigtigste bog og online-ressourcer, samt trinvis algoritme skabe en uddannelsesplan.

Denne artikel indeholder nogle enkle teknikker, der hjælper forenkle arbejdet i Excel. de er særlig nyttige for dem, der er involveret i ledelsesrapportering, forbereder en række analytiske rapporter baseret på landinger af 1C rapporter og andre former for disse præsentationer og diagrammer for lederskab. Må ikke foregive at være en absolut nyhed - i en eller anden form, disse metoder helt sikkert diskuteres i fora eller er omhandlet i artikel.

Enkel alternativ til LOPSLAG og VOPSLAG, hvis de krævede værdier ikke i første kolonne i skemaet: VIEW, INDEX + MATCH

LOPSLAG (LOPSLAG) Og PGR (VOPSLAG) kun arbejde, hvis målværdierne er i den første kolonne eller række i tabellen, hvorfra du ønsker at få data.

I andre tilfælde er der to muligheder:

1. Brug VIEW funktion (LOOKUP).
   instagram viewer
   
   Hun har følgende syntaks: SE (opslagsværdi Søg vektoren; vektor_rezultata). Men for at det virker korrekt skal du range værdier Søg vektor De blev sorteret i stigende rækkefølge:
2. MATCH bruger en kombination af funktioner (MATCH) og et indeks (INDEX).
   MATCH -funktionen giver serienummer element i array (med dens hjælp kan du finde, i en række i tabellen den ønskede element), og funktion INDEX returnerer en matrice element med et forudbestemt antal (som vi kender til funktion MATCH).Syntaks muligheder:
   • MATCH (opslagsværdi massiv_poiska; MATCH_TYPE) - i vores tilfælde, vi har brug for en form for sammenligning "eksakt match", det svarer til antallet 0.
   • INDEX (matrix; ROW_NUM; [COLUMN_NUM]). I dette tilfælde er det ikke nødvendigt at angive kolonnen nummer som array består af en enkelt linje.

Hvor hurtigt at fylde de tomme celler i listen

Opgaven - at fylde cellerne med værdierne i kolonnen ovenfor (med forbehold af at stå i hver række i tabellen, ikke kun i den første blok rækken af ​​bøger om emnet):

Vælg kolonnen "Emne", tryk på båndet i gruppen af ​​"Home" knappen "Find og vælg» → «Fremhæv gruppe af celler» → «Blank celle "og begynde at indtaste formlen (dvs. sætte et lighedstegn) og henviser til cellen fra oven, ved blot at trykke på pil op tastatur. Tryk derefter på Ctrl + Enter. Efter dette, kan dataene gemmes som en værdi, fordi de formler er ikke længere brug for:

Sådan finder fejl i formlen

Beregning separat del formel

For at forstå den komplekse formel (hvori som funktionsargumenter bruger andre funktion, der er nogle funktioner er indlejret i den anden), eller at finde i det en kilde til fejl, der ofte har brug for at beregne del af det. Der er to nemme måder:

1. For at beregne den rigtige del af formlen i formellinjen, skal du vælge side og trykke på F9:
   
   I dette eksempel, der var et problem med den funktion SØG (SEARCH) - der var blandet op argumenter. Det er vigtigt at huske, at hvis du ikke annullerer beregningen af ​​funktionen, og tryk på Enter, så den beregnede del vil forblive nummer.
2. Tryk på "Beregn formlen" i gruppen "formel" på båndet:
   
   
   I det vindue, der vises, kan du beregne formlen trin for trin og at bestemme, på hvilket tidspunkt og i hvilken funktion fejl (hvis nogen):
   

Hvordan kunne fastslå hvilke fastslår, at formel eller refereres

For at afgøre, hvilke af celler afhænger af den formel i gruppen af ​​"formel" i båndet, skal du klikke på knappen "Spor overordnede":

Der er pile, der peger hvorfra resultatet afhænger af.

Hvis symbolet vises på det valgte billede i rød, formlen afhænger af celler, der er i andre lister eller i andre bøger:

Hvis du klikker på det, kan vi se præcis, hvor indflydelsen af ​​cellen eller interval:

Ved siden af ​​"Spor overordnede" er der en knap "underordnede", der arbejder på samme måde: det viser pile fra den aktive celle med formlen til celler, der er afhængige af den.

Knap "Fjern pile" ligger i samme blok, gør det muligt at fjerne fortilfælde Pårørende, eller begge typer på en gang pile:

Sådan finder beløbet (nummer, middelværdier) værdier af celler med flere ark

Lad os sige, at du har flere lignende side med de data, du vil tilføje, finde eller behandles på anden måde:

For at gøre dette, den celle, hvor du vil se resultatet, indtast standard formel, såsom SUM (SUM), og indtaste det argument efterfulgt af et kolon og det sidste navn på det første ark fra listen over ark, du behøver proces:

Du vil modtage den mængde celle adresse B3 med plader "Data1", "Data2", "oplysninger3":

Sådanne fat værker for ark anbragt successivt. Syntaksen er: FEATURE = (pervyy_list: posledniy_list henvisning til den række!).

Hvordan til automatisk at bygge en stereotyp sætninger

Ved hjælp af de grundlæggende principper for at arbejde med tekst i Excel og et par simple funktioner, kan fremstilles stereotyp sætninger for rapporter. Adskillige principper for arbejdet med teksten:

• Kombiner tekst med mærket & (kan erstatte sin funktion SAMMENKÆDNING (SAMMENKÆDNING), men det er ikke meget brug).
• Teksten er altid skrevet i anførselstegn, cellereferencerne med teksten - ikke altid.
• For at få den særlige karakter "citater", ved hjælp af tegn funktion (CHAR) argument 32.

EKSEMPEL skabe skabelon sætninger hjælp formler:

resultere:

I dette tilfælde, udover funktionen SYMBOL (CHAR) (for at vise anførselstegn) ved hjælp af funktionen HVIS (IF), som gør det muligt at ændre teksten i Afhængigt af, om der er en positiv udvikling i salget og funktionen af ​​teksten (TEKST), gør det muligt at vise et nummer til en format. Dets syntaks er beskrevet nedenfor:

TEKST (værdi; format)

Formatet angives i anførselstegn på samme måde, som hvis du har indtastet et brugerdefineret format i "Formater celler".

Du kan automatisere mere komplekse tekster. I min praksis var at automatisere længe, ​​men de rutinemæssige kommentarer til forvaltningsregnskaberne i formatet "Tallet faldt / steg på XX på planen primært på grund af stigningen / faldet i FAKTORA1 XX, stigning / fald i FAKTORA2 YY... »med de skiftende liste faktorer. Hvis du skriver sådanne bemærkninger ofte og processen med at skrive dåse algorithmization - værd engang forundret skabe en formel eller makro, der vil spare dig fra det mindste en del af arbejdet.

Sådan gemme dataene i hver celle, efter fusionen

Ved kombination af cellerne bibeholdt kun én værdi. Excel advarer dig, når du forsøger at flette celler:

Følgelig hvis du havde en formel, der afhænger af hver celle, vil den ophøre at arbejde efter deres union (bug # N / A i linjer 3-4 eksempler):

At flette celler, og samtidig bevare data i hver af dem (måske har du en formel, som i dette eksempel, det abstrakte; du måske ønsker at flette celler, men holde alle data i fremtiden, eller for at skjule deres hensigter), kombinere enhver celle på ark, skal du vælge dem, og derefter bruge kommandoen "Format Painter" transfer-format på cellerne, som du har brug for og kombinere:

Hvordan til at bygge en sammenfatning af flere datakilder

Hvis du har brug for at opbygge en konsolideret fra flere datakilder, er det nødvendigt at tilføje til båndet eller værktøjslinjen Hurtig adgang "Pivottabel Wizard og diagrammer", hvor der er en sådan mulighed.

Du kan gøre dette på følgende måde: "File» → «Indstillinger» → «værktøjslinjen Hurtig adgang» → «Alle hold» → «Pivottabel Guiden og diagrammer» → «Tilføj»:

Efter dette, vil båndet være en tilsvarende ikon, klikke på, som forårsager tilføjelse af master:

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

I den vil du nødt til at vælge "Et par bånd af konsolidering" og klik på "Næste". I næste afsnit kan du vælge "Opret en enkelt side felt" eller "Opret en side felt." Hvis du ønsker selv at komme med et navn for hver af de datakilder - for at vælge det andet punkt:

I den næste boks, tilføje alle områderne på baggrund af at bygge et resumé, og bede dem navnene:

Efter det tidspunkt vil være vært for en pivottabel i den sidste dialogboks - på en eksisterende eller et nyt regneark:

Rapport Pivottabel Klar. Filteret "Side 1" kan du vælge kun én af de datakilder, hvis det er nødvendigt:

Hvordan til at beregne antallet af forekomster af teksten i teksten til A B ( «MTS toldmæssige SuperMTS" - to forekomster af forkortelsen MTS)

I dette eksempel, kolonne A, der er et par linjer tekst, og vores opgave - at finde ud af, hvor mange gange hver af dem opfylder den krævede tekst i cellen E1:

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

1. LEN (LEN) - beregner længden af ​​teksten, et enkelt argument - teksten. Eksempel: LEN ( "maskine") = 6.
2. SUBSTITUTE (stedfortræder) - erstatter tekststrengen en bestemt tekst med en anden. Syntaks: SUBSTITUTE (tekst; star_tekst; nov_tekst). Eksempel: SUBSTITUTE ( "bil", "bil", "") = "mobil".
3. UPPER (UPPER) - erstatter alle tegn i en streng til store bogstaver. Det eneste argument - teksten. Eksempel: UPPER ( "maskine") = "MACHINE". Denne funktion vil være behov for at gøre søgningen store og små bogstaver. Efter alt, UPPER ( "Maskine") = UPPER ( "Maskine")

For at finde en bestemt post af en tekststreng til en anden, skal du fjerne alle dens forekomster i den oprindelige og sammenligne længden af ​​strengen fra den originale:

LEN ( "Tariff Super MTS MTS") - LEN ( "Super Rate") = 6

Og derefter dividere forskellen med længden af ​​den linje, vi ledte efter:

6 / LEN ( "MTS") = 2

Det er dobbelt så line "MTS" er i den oprindelige.

Det er endnu ikke skrevet på det sprog, algoritmen formler (betegnet "tekst" den tekst, som vi søger indrejse og "ønske" - antallet af forekomster, at vi er interesseret i):

= (LEN (tekst) -DLSTR (SUBSTITUTE (UPPER (tekst); UPPER (efterspurgt til); ""))) / LEN (efterspurgt til)

I vores eksempel, formlen er som følger:

= (LEN (A2) -DLSTR (SUBSTITUTE (UPPER (A2); UPPER ($ E $ 1); ""))) / LEN ($ E $ 1)