Excel-life hacking för dem som deltar i rapportering och databehandling

Renat är inte första gången en gäst författare talar om Layfhakere. Tidigare publicerade vi en utmärkt material från honom om hur man skapar en utbildningsplan: huvudboken och online-resurser, samt inkrementell algoritm skapa en utbildningsplan.

Den här artikeln innehåller några enkla tekniker som hjälper förenkla arbetet i Excel. De är särskilt användbara för dem som deltar i rapportering, förbereder en rad olika analytisk rapporter baserade på landningar av 1C rapporter och andra former av dessa presentationer och diagram för ledarskap. Inte låtsas vara en absolut nyhet - i en eller annan form, dessa metoder diskuteras visserligen i forum eller som avses i artikel.

Enkelt alternativ till VLOOKUP och HLOOKUP, om önskade värden är inte i första kolumnen i tabellen: VIEW, INDEX + MATCH

LETARAD (VLOOKUP) Och PGR (HLOOKUP) bara om målvärdena är i den första kolumnen eller raden i tabellen som du vill hämta data.

I andra fall finns det två alternativ:

1. Användning visa funktion (lookup).
   Hon har följande syntax: SE (
   instagram viewer
   letauppvärde Sök vector; vektor_rezultata). Men för att det ska fungera korrekt måste du intervallvärden Sök vektor De sorteras i stigande ordning:
2. MATCH använder en kombination av funktioner (MATCH) och ett index (INDEX).
   MATCH returnerar serienumret för elementet i arrayen (med dess hjälp kan du hitta i en rad i tabellen det önskade elementet), och funktion INDEX returnerar en array element med ett förutbestämt antal (som vi känner till funktionen MATCH).Syntax alternativ:
   • MATCH (letauppvärde massiv_poiska; match_type) - i vårt fall behöver vi en slags jämförelse "exakt matchning", det motsvarar antalet 0.
   • INDEX (array; rad_num; [Kolumn]). I detta fall är det inte nödvändigt att ange kolumnnumret som matrisen består av en enda rad.

Hur snabbt för att fylla de tomma cellerna i listan

Uppgiften - att fylla cellerna med värdena i kolumnen ovan (med förbehåll för att stå i varje rad i tabellen, inte bara i det första blocket raden av böcker i ämnet):

Markera kolumnen "Ämne", tryck på bandet i gruppen "Home" knappen "Sök och välj» → «Markera den grupp av celler» → «Blank cell "och börja skriva formeln (dvs sätta ett likhetstecken) och refererar till cellen från ovan, genom att helt enkelt trycka på uppåtpilen tangentbord. Tryck sedan på Ctrl + Enter. Efter det kan data sparas som ett värde, eftersom formler inte längre behövs:

Hur man hittar fel i formel

Beräkning separat del formeln

För att förstå den komplexa formeln (i vilken som funktionsargument använder andra funktion, det vill säga en del funktioner är inbäddade i den andra), eller att hitta i det en källa till fel, ofta behöver för att beräkna del av den. Det finns två enkla sätt:

1. För att beräkna den högra delen av formeln i formelfältet, välj sidan och tryck på F9:
   
   I det här exemplet det fanns ett problem med funktionen SEARCH (SEARCH) - det var blandade argument. Det är viktigt att komma ihåg att om du inte avbryter beräkningen av funktionen och tryck på Enter, sedan den beräknade delen kommer att förbli nummer.
2. Tryck på "Beräkna formeln" i gruppen "formel" på bandet:
   
   
   I fönstret som visas kan du beräkna formeln stegvis och för att bestämma i vilket skede och i vilken funktion fel (i förekommande fall):
   

Så att avgöra vad bestämmer att formel eller refereras

För att avgöra vilken av cellerna beror på formeln i gruppen av "formel" i menyfliksområdet, klicka på knappen "Trace Prejudikat":

Det finns pilar som pekar från vilket resultatet är beroende av.

Om symbolen visas på den valda bilden i rött, formeln beror på celler som finns i andra listor eller i andra böcker:

Genom att klicka på det, kan vi se exakt var påverkan av cellen eller range:

Bredvid "Trace Prejudikat" Det finns en knapp "anhöriga", som arbetar på samma sätt: den visar pilar från den aktiva cellen med formeln till celler som är beroende av den.

Button "Ta bort pilar" ligger i samma kvarter, gör det möjligt att ta bort Prejudikat, anhöriga, eller båda typerna samtidigt pilar:

Hur man hittar den mängd (nummer, medelvärde) värden av celler med flera ark

Låt oss säga att du har flera liknande ark med de data som du vill lägga till, hitta eller behandlas på annat sätt:

För att göra detta, den cell där du vill se resultatet, ange standardformeln, såsom SUM (SUM), och ange argument följt av ett kolon och det sista namnet på den första plåten från listan ark som du behöver processen:

Du kommer att få den mängd celladressen B3 med lakan "Data1", "Data2", "uppgifter3":

Sådana adresse gäller i ark anordnade successivt. Syntaxen är: FEATURE = (pervyy_list: posledniy_list hänvisning till intervallet!).

Hur man automatiskt skapa en formel fraser

Genom att använda de grundläggande principerna för att arbeta med text i Excel och några enkla funktioner kan beredas formel fraser för rapporter. Flera principer för arbetet med texten:

• Kombinera text med Mark & ​​(kan ersätta dess funktion CONCATENATE (CONCATENATE), men det är inte mycket användning).
• Texten skrivs alltid inom citationstecken, cellreferenserna med text - inte alltid.
• För att få specialtecken "citat", med hjälp av teckenfunktionen (CHAR) argument 32.

EXEMPEL skapa mall fraser med användning formler:

resultera:

I det här fallet, förutom funktionen SYMBOL (char) (för att visa citat) med hjälp av funktionen IF (IF), vilket gör det möjligt att ändra texten i Beroende på om det finns en positiv trend i försäljningen och funktionen av texten (TEXT), gör det möjligt att visa ett nummer i någon format. Dess syntax beskrivs nedan:

TEXT (värde; format)

Formatet anges inom citattecken på samma sätt som om du har angett ett anpassat format i "Formatera celler".

Du kan automatisera mer komplexa texter. I min praktik var att automatisera lång, men rutinen kommentarer om förvaltningskonton i formatet "siffran föll / ros vid XX på planen främst på grund av ökning / minskning i FAKTORA1 XX, ökning / minskning i FAKTORA2 YY... »med den föränderliga listan faktorer. Om du skriver sådana kommentarer ofta och processen att skriva kan algorithmization - värt en gång förbryllad skapa en formel eller makro som kommer att rädda dig från åtminstone en del av arbetet.

Så för att spara data i varje cell efter sammanslagningen

Vid kombination cellerna behöll endast ett värde. Excel varnar dig när du försöker att slå samman celler:

Således, om du hade en formel som beror på varje cell, kommer det att upphöra att arbeta efter deras union (bugg # N / A i raderna 3-4 exempel):

Att slå samman celler, samtidigt som data i vart och ett av dem (kanske du har en formel, som i detta exempel, det abstrakta; kanske du vill sammanfoga celler, men behålla alla data i framtiden, eller för att dölja sina avsikter), kombinera någon cell på ark, markera dem och sedan använda kommandot "Hämta format" överföringsformat på de celler som du behöver och kombinera:

Hur man bygger en sammanfattning av flera datakällor

Om du behöver för att bygga en konsoliderad från flera datakällor, är det nödvändigt att lägga till i band eller verktygsfältet Snabbåtkomst "Pivottabell Wizard och diagram" där det finns ett sådant alternativ.

Du kan göra detta på följande sätt: "File» → «Inställningar» → «verktygsfältet Snabbåtkomst» → «All laget» → «pivottabellen Wizard och diagram» → «Lägg till»:

Efter det kommer bandet att vara en motsvarande ikon, klicka på vilket orsakar tillsatsen av befälhavaren:

När du klickar på den, en dialogruta:

I det måste du välja "några band av konsolidering" och klicka på "Nästa". I nästa avsnitt kan du välja "Skapa en enda sida fält" eller "Skapa en sida fält." Om du vill själv komma med ett namn för var och en av datakällorna - för att välja den andra punkten:

I nästa ruta, lägga till alla de områden på grundval av att bygga en sammanfattning, och be dem namnen:

Efter den punkten, kommer att vara värd en pivottabellrapport i den sista dialogrutan - på en befintlig eller ett nytt kalkylblad:

Rapport pivottabellen redo. Filtret "Page 1" kan du välja bara en av datakällorna, om nödvändigt:

Hur man beräknar antalet förekomster av texten i texten A B ( «MTS tull SuperMTS" - två förekomster av förkortningen MTS)

I detta exempel kolumn A finns det några rader text, och vår uppgift - att ta reda på hur många gånger varje av dem möter önskad text i cellen E1:

För att lösa detta problem, kan du använda en komplicerad formel som består av följande funktioner:

1. LEN (LEN) - beräknar längden på texten, ett enda argument - texten. Exempel: LEN ( "maskin") = 6.
2. Suppleant (substitut) - ersätter textsträngen en viss text med en annan. Syntax: suppleant (text; star_tekst; nov_tekst). Exempel: BYT.UT ( "bil", "bil", "") = "mobil".
3. ÖVRE (övre) - ersätter alla tecken i en sträng till versaler. Det enda argument - texten. Exempel: UPPER ( "maskin") = "MACHINE". Denna funktion kommer att behövas för att göra sökningen skiftlägeskänslig. När allt kommer omkring, ÖVRE ( "Machine") = övre ( "Machine")

För att hitta en viss inmatning av en textsträng till en annan, måste du ta bort alla sina händelser i original och jämföra längden av strängen från den ursprungliga:

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

Och sedan dividera skillnaden med längden på linjen, letade vi efter:

6 / LEN ( "MTS") = 2

Det är dubbelt raden "MTS" är i original.

Det återstår att skrivas på det språk som algoritmen formler (betecknas "text" den text som vi söker inträde och "önskat" - antalet händelser som vi är intresserade av):

= (LEN (text) -DLSTR (SUBSTITUTE (ÖVRE (text); UPPER (sökta); ""))) / LEN (sökta)

I vårt exempel, är formeln som följer:

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