Excel-life hacking for de som er involvert i rapportering og databehandling

Renat er ikke første gang en gjest forfatter snakker om Layfhakere. Tidligere publiserte vi et utmerket materiale fra ham om hvordan du oppretter en opplæringsplan: hoved~~POS=TRUNC bok og ressurser, samt inkrementell algoritme lage en opplæringsplan.

Denne artikkelen inneholder noen enkle teknikker som hjelper forenkle arbeidet i Excel. de er spesielt nyttig for de som er involvert i ledelsesrapportering, forbereder en rekke analytiske rapporter basert på landing av 1C rapporter og andre former av disse presentasjoner og diagrammer for lederskap. Ikke later til å være en absolutt nyhet - i en eller annen form, disse metodene sikkert diskutert i fora eller nevnt i artikkel.

Enkelt alternativ til FINN.RAD og FINN.KOLONNE Hvis de nødvendige verdiene er ikke i den første kolonnen i tabellen: VIEW, INDEX + MATCH

VLOOKUP (VLOOKUP) Og PGR (HLOOKUP) bare fungere hvis målet verdiene i første kolonne eller rad i tabellen som du ønsker å få data.

I andre tilfeller er det to alternativer:

1. Bruk View-funksjon (oppslag).
   instagram viewer
   
   Hun har følgende syntaks: SE (søkeverdi Søk vektor; vektor_rezultata). Men for at det skal fungere riktig må du områdeverdier Søk vektor De ble sortert i stigende rekkefølge:
2. MATCH bruke en kombinasjon av funksjoner (MATCH) og en indeks (INDEX).
   MATCH funksjonen returnerer serienummer element i matrisen (med dens hjelp kan du finne i en rad i tabellen det ønskede element), og funksjon INDEX returnerer en matrise-element med et forutbestemt tall (som vi kjenner til funksjon MATCH).Syntaks alternativer:
   • MATCH (søkeverdi massiv_poiska; match_type) - i vårt tilfelle, trenger vi en slags sammenligning "eksakt matching", det tilsvarer antall 0.
   • INDEX (matrise; radnummer; [COLUMN_NUM]). I dette tilfelle er det ikke nødvendig å spesifisere kolonnetallet som oppstillingen består av en enkelt linje.

Hvor raskt å fylle de tomme cellene i listen

Oppgaven - for å fylle cellene med verdiene i kolonnen over (mot stående i hver rad i tabellen, ikke bare i det første blokkrekke av bøker om emnet):

Velg kolonnen "Emne", trykk på båndet i gruppen av "Home" -knappen "Finn og velg» → «Marker gruppen av celler» → «Blankt celle "og begynner å skrive inn formelen (dvs. sette et likhetstegn) og refererer til cellen ovenfra, ved ganske enkelt å trykke den opp pil tastatur. Deretter trykker du Ctrl + Enter. Etter det, kan dataene lagres som en verdi, fordi formlene er ikke lenger nødvendig:

Hvordan finne feil i formelen

Beregning separat del formelen

For å forstå den komplekse formel (i hvilken som funksjon argumenter ved hjelp av annen funksjon, det vil si enkelte funksjoner er innebygd i den andre), eller å finne i det en kilde til feil, ofte behov for å beregne del av det. Det er to enkle måter:

1. For å beregne den høyre delen av formelen i formellinjen, velge den delen, og trykke F9:
   
   I dette eksemplet, det var et problem med funksjonen SEARCH (SØK) - det var blandet opp argumenter. Det er viktig å huske at hvis du ikke avbestiller beregningen av funksjon og trykk på Enter, da den beregnede del vil forbli nummer.
2. Trykk "Beregn formelen" i gruppen "formel" på båndet:
   
   
   I vinduet som vises, kan man regne med formel trinnvis og for å bestemme på hvilket stadium og i hvilken funksjonsfeil (hvis noen):
   

Hvordan du finner ut hva som avgjør at formelen eller referert

For å bestemme hvilken av cellene er avhengig av formel i gruppen av "formelen" i båndet ved å klikke på knappen "Trace Presedenser":

Det er piler som peker hvor resultatet avhenger.

Hvis symbolet vises på det valgte bildet i rødt, avhenger formelen på celler som er i andre lister eller i andre bøker:

Ved å klikke på den, kan vi se nøyaktig hvor påvirkning av celle eller et område:

Ved siden av "Trace Presedenser" Det er en knapp "som forsørges", som arbeider på samme måte: den viser piler fra den aktive celle med formelen til celler som avhenger av den.

Knappen "Fjern piler" ligger i samme blokk, gjør det mulig å fjerne Presedenser, pårørende, eller begge typer samtidig piler:

Hvordan finne beløpet (tall, middel) verdier av celler med flere ark

La oss si at du har flere like ark med data som du ønsker å legge til, finne eller behandlet på annen måte:

For å gjøre dette, cellen der du vil se resultatet, skriv standard formel, for eksempel SUM (SUM), og angi argumentet etterfulgt av et kolon og det siste navnet på det første arket i listen ark du trenger prosessen:

Du vil motta mengden av celleadressen B3 med ark "Data1", "data 2", "data3":

Slike adressering verk for arkene ordnet suksessivt. Syntaksen er: FEATURE = (pervyy_list: posledniy_list referanse til området!).

Hvordan automatisk bygge en formalistisk uttrykk

Ved hjelp av de grunnleggende prinsippene for å jobbe med tekst i Excel og noen enkle funksjoner, kan være forberedt formalis setninger for rapporter. Flere prinsipper for arbeidet med teksten:

• Kombinere tekst med merket og (kan erstatte sin funksjon KJEDE.SAMMEN (CONCATENATE), men dette er ikke mye bruk).
• Teksten er alltid skrevet i anførselstegn, celle referanser med teksten - ikke alltid.
• For å få de spesielle tegn "anførselstegn", ved hjelp av tegn funksjon (CHAR) argument 32.

Eksempel skape mal setninger ved hjelp formler:

resultere:

I dette tilfellet, i tillegg til funksjonen SYMBOL (CHAR) (for å vise sitater) ved hjelp av funksjonen HVIS (IF), som gjør det mulig å endre teksten i Avhengig av om det er en positiv trend i salget og funksjonen av teksten (tekst), slik at å vise et tall i en hvilken som helst format. Syntaksen er beskrevet nedenfor:

TEKST (verdi; format)

Formatet er spesifisert i anførselstegn på samme måte som om du har angitt en egendefinert format i "Formater celler."

Du kan automatisere mer komplekse tekster. I min praksis var å automatisere lang, men de rutinemessige kommentarer til driftsregnskapet i formatet "figuren falt / rose på XX på planen hovedsakelig på grunn av økning / reduksjon i FAKTORA1 XX, økning / reduksjon i FAKTORA2 YY... »med skiftende liste faktorer. Hvis du skriver slike kommentarer ofte og prosessen med skriving kan algorithmization - verdt når forundret lage en formel eller makro som vil spare deg fra minst en del av arbeidet.

Hvordan du lagrer dataene i hver celle etter fusjonen

Ved kombinasjon av cellene beholdt bare én verdi. Excel advarer deg når du prøver å slå sammen celler:

Følgelig, hvis man hadde en formel som er avhengig av hver enkelt celle, vil den slutte å arbeide etter deres forening (bug # N / A i linjene 3-4 eksempler):

Til flette celler, uten av data i hver av dem (Kanskje den har en formel, som i dette eksempelet, sammendraget; kan det være lurt å slå sammen celler, men beholde alle data i fremtiden, eller for å skjule sine intensjoner), kombinere en celle på ark, velg dem, og deretter bruke kommandoen "Format Painter" transfer format på cellene som du trenger og kombinere:

Hvordan bygge en oppsummering av flere datakilder

Hvis du trenger å bygge en konsolidert fra flere datakilder, er det nødvendig å legge til tape eller Quick Access Toolbar "pivottabell veiviseren og diagrammer" der det er et slikt alternativ.

Du kan gjøre dette på følgende måte: "Fil» → «Innstillinger» → «Quick Access Toolbar» → «Alle team» → «pivot Wizard og diagrammer» → «Legg til»:

Etter det, vil båndet være en tilsvarende ikon, klikke på noe som fører tilsetningen av hoved:

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

I den vil du trenger for å velge "Noen band av konsolidering" og klikk "Next". I neste avsnitt, kan du velge "Opprett en enkelt side felt" eller "Opprett en side felt." Hvis du ønsker selv å komme opp med et navn for hver av de datakilder - for å velge det andre elementet:

I den neste boksen, legge til alle områdene på grunnlag av å bygge et sammendrag, og spør dem navnene:

Etter dette punktet, vil avholde en pivottabellrapport i den siste dialogboksen - på en eksisterende eller et nytt regneark:

Rapporter pivottabell klar. Filteret "Side 1" du kan velge bare en av datakildene, om nødvendig:

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

I dette eksempelet kolonne A, er det noen linjer med tekst, og vår oppgave - å finne ut hvor mange ganger hver av dem oppfyller ønsket tekst i celle E1:

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

1. LEN (LEN) - beregner lengden av teksten, en enkelt argument - teksten. Eksempel: LEN ( "maskin") = 6.
2. SUBSTITUTE (SUBSTITUTE) - erstatter tekststrengen en viss tekst med en annen. Syntaks: SUBSTITUTE (tekst; star_tekst; nov_tekst). Eksempel: SUBSTITUTE ( "bil", "bil", "") = "mobil".
3. UPPER (ØVRE) - erstatter alle tegn i en streng til store bokstaver. Den eneste argument - teksten. Eksempel: UPPER ( "maskin") = "MACHINE". Denne funksjonen vil være nødvendig for å gjøre søket case-insensitive. Tross alt, ØVRE ( "Machine") = STORE ( "Machine")

For å finne en bestemt oppføring av en tekststreng til en annen, må du fjerne alle sine forekomster i den opprinnelige og sammenligne lengden av strengen fra den opprinnelige:

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

Og deretter dele forskjellen ved lengden av linjen, var vi ute etter:

6 / LEN ( "MTS") = 2

Det er dobbelt linje "MTS" er i originalen.

Det gjenstår å være skrevet på språket til algoritmen formler (benevnt "text" teksten som vi søker oppføring og "ønsket" - antall forekomster som vi er interessert i):

= LEN ((tekst) -DLSTR (SUBSTITUTE (ØVRE (tekst); UPPER (etterspurte for); ""))) / LEN (etterspurte for)

I vårt eksempel, er formelen som følger:

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