Excel life hacks för dem som är involverade i rapportering och databehandling

2024 Författare: Malcolm Clapton | [email protected]. Senast ändrad: 2024-01-13 03:02

I det här inlägget delar Renat Shagabutdinov, assisterande generaldirektör för Mann, Ivanov och Ferber Publishing House, några coola Excel-livshack. Dessa tips kommer att vara användbara för alla som är involverade i olika rapportering, databearbetning och att skapa presentationer.

Den här artikeln innehåller enkla tekniker för att förenkla ditt arbete i Excel. De är särskilt användbara för dem som är engagerade i ledningsrapportering, förbereder olika analytiska rapporter baserade på nedladdningar från 1C och andra rapporter, bildar presentationer och diagram från dem för ledningen. Jag utger mig inte för att vara absolut nyhet - i en eller annan form diskuterades dessa tekniker förmodligen på forumen eller nämndes i artiklar.

Enkla alternativ till VLOOKUP och HLOOKUP, om de önskade värdena inte finns i den första kolumnen i tabellen: LOOKUP, INDEX + SÖK

Funktionerna VLOOKUP och HLOOKUP fungerar bara om de önskade värdena finns i den första kolumnen eller raden i tabellen som du planerar att hämta data från.

Annars finns det två alternativ:

1. Använd SÖKNING-funktionen.

   Den har följande syntax: LOOKUP (lookup_value; lookup_vector; result_vector). Men för att det ska fungera korrekt måste värdena för view_vector-intervallet sorteras i stigande ordning:

   

2. Använd en kombination av MATCH- och INDEX-funktionerna.

   Funktionen MATCH returnerar ordningsnumret för ett element i arrayen (med dess hjälp kan du hitta i vilken rad i tabellen det sökta elementet är), och INDEX-funktionen returnerar ett arrayelement med ett givet nummer (vilket vi kommer att ta reda på med MATCH-funktionen).

   

   Funktionssyntax:

   • SÖK (search_value; search_array; match_type) - för vårt fall behöver vi en matchningstyp "exakt matchning", den motsvarar siffran 0.

   • INDEX (matris; radnummer; [kolumnnummer]). I det här fallet behöver du inte ange kolumnnumret, eftersom arrayen består av en rad.

Hur man snabbt fyller tomma celler i en lista

Uppgiften är att fylla i cellerna i kolumnen med värdena överst (så att ämnet finns i varje rad i tabellen, och inte bara i den första raden i blocket med böcker om ämnet):

Välj kolumnen "Ämne", klicka på menyfliksområdet i gruppen "Hem", knappen "Sök och välj" → "Välj en grupp av celler" → "Tomma celler" och börja skriva in formeln (det vill säga sätta en lika tecken) och hänvisa till cellen högst upp genom att helt enkelt klicka på uppåtpilen på tangentbordet. Tryck sedan på Ctrl + Enter. Därefter kan du spara mottagna data som värden, eftersom formlerna inte längre behövs:

Hur man hittar fel i en formel

Beräkning av en separat del av en formel

För att förstå en komplex formel (där andra funktioner används som funktionsargument, det vill säga vissa funktioner är kapslade i andra) eller för att hitta felkällan i den, behöver du ofta beräkna en del av den. Det finns två enkla sätt:

1. För att beräkna en del av en formel direkt i formelfältet, välj den delen och tryck på F9:

   

   I det här exemplet var det ett problem med SEARCH-funktionen - argument byttes ut i den. Det är viktigt att komma ihåg att om du inte avbryter beräkningen av delen av funktionen och trycker på Enter, kommer den beräknade delen att förbli en siffra.

2. Klicka på knappen Beräkna formel i gruppen Formler på menyfliksområdet:

   

   I fönstret som visas kan du beräkna formeln steg för steg och bestämma i vilket skede och i vilken funktion ett fel uppstår (om något):

   

Hur man avgör vad en formel beror på eller syftar på

För att avgöra vilka celler en formel beror på, klicka på knappen som påverkar celler i gruppen Formler på menyfliksområdet:

Pilar visas för att indikera vad beräkningsresultatet beror på.

Om symbolen som är markerad i bilden i rött visas, beror formeln på cellerna på andra ark eller i andra böcker:

Genom att klicka på den kan vi se exakt var de påverkande cellerna eller områdena finns:

Bredvid knappen "Influencing Cells" finns knappen "Dependent Cells", som fungerar på samma sätt: den visar pilar från den aktiva cellen med en formel till de celler som är beroende av den.

Knappen "Ta bort pilar", som finns i samma block, låter dig ta bort pilar till påverkande celler, pilar till beroende celler eller båda typerna av pilar samtidigt:

Hur man hittar summan (antal, genomsnitt) av cellvärden från flera ark

Låt oss säga att du har flera ark av samma typ med data som du vill lägga till, räkna eller bearbeta på något annat sätt:

För att göra detta, i cellen där du vill se resultatet, ange en standardformel, till exempel SUM (SUM), och ange namnet på de första och sista arken från listan över de ark som du behöver bearbeta i argumentet, avgränsat med ett kolon:

Du kommer att få summan av celler med adressen B3 från arken "Data1", "Data2", "Data3":

Denna adressering fungerar för lokaliserade ark konsekvent … Syntaxen är som följer: = FUNCTION (första_listan: sista_listan! Områdesreferens).

Hur man automatiskt bygger mallfraser

Med hjälp av de grundläggande principerna för att arbeta med text i Excel och några enkla funktioner kan du förbereda mallfraser för rapporter. Flera principer för att arbeta med text:

• Vi sammanfogar texten med &-tecknet (du kan ersätta den med CONCATENATE-funktionen, men det är inte så meningsfullt).
• Texten är alltid skriven inom citattecken, referenser till celler med text är alltid utan.
• För att få tjänstetecknet "citattecken", använd CHAR-funktionen med argument 32.

Ett exempel på att skapa en mallfras med formler:

Resultat:

I det här fallet, förutom CHAR-funktionen (för att visa offerter), används IF-funktionen, som låter dig ändra texten beroende på om det finns en positiv försäljningstrend, och TEXT-funktionen, som låter dig visa nummer i valfritt format. Dess syntax beskrivs nedan:

TEXT (värde; format)

Formatet anges inom citattecken, precis som om du skulle ange ett anpassat format i fönstret Formatera celler.

Mer komplexa texter kan också automatiseras. I min praktik var det automatisering av långa, men rutinmässiga kommentarer till ledningens rapportering i formatet INDIKATOR föll / steg med XX i förhållande till planen, främst på grund av tillväxten / minskningen av FACTOR1 med XX, tillväxt / minskning av FACTOR2 med YY …” med en föränderlig lista över faktorer. Om du skriver sådana kommentarer ofta och processen att skriva dem kan algoritmiseras, är det värt att en gång förbryllande att skapa en formel eller ett makro som kommer att spara dig åtminstone en del av arbetet.

Hur man lagrar data i varje cell efter sammanfogning

När du slår samman celler behålls endast ett värde. Excel varnar för detta när man försöker slå samman celler:

Följaktligen, om du hade en formel beroende på varje cell, kommer den att sluta fungera efter att ha kombinerat dem (# N / A fel på raderna 3-4 i exemplet):

För att slå samman celler och fortfarande bevara data i var och en av dem (kanske har du en formel som i det här abstrakta exemplet; kanske vill du slå samman celler, men behålla all data för framtiden eller dölja den avsiktligt), slå samman alla celler på arket, välj dem och använd sedan kommandot Format Painter för att överföra formateringen till de celler som du behöver kombinera:

Hur man bygger en pivot från flera datakällor

Om du behöver bygga en pivot från flera datakällor samtidigt måste du lägga till "Pivottabell- och diagramguiden" till menyfliksområdet eller snabbåtkomstpanelen, som har ett sådant alternativ.

Du kan göra detta enligt följande: "Arkiv" → "Alternativ" → "Snabbåtkomstverktygsfält" → "Alla kommandon" → "Pivottabell och diagramguide" → "Lägg till":

Efter det kommer en motsvarande ikon att dyka upp på menyfliksområdet, klicka på vilken anropar samma guide:

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

I den måste du välja objektet "I flera konsolideringsintervall" och klicka på "Nästa". I nästa steg kan du välja "Skapa ett sidfält" eller "Skapa sidfält". Om du självständigt vill komma på ett namn för var och en av datakällorna, välj det andra objektet:

I nästa fönster lägger du till alla intervall på grundval av vilka pivoten kommer att byggas och ge dem namn:

Därefter, i den sista dialogrutan, ange var pivottabellsrapporten ska placeras - på ett befintligt eller nytt ark:

Pivottabellsrapporten är klar. I filtret "Sida 1" kan du bara välja en av datakällorna, om det behövs:

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

I det här exemplet innehåller kolumn A flera textrader, och vår uppgift är att ta reda på hur många gånger var och en av dem innehåller söktexten i cell E1:

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

1. DLSTR (LEN) - beräknar längden på texten, det enda argumentet är texten. Exempel: DLSTR ("maskin") = 6.
2. ERSÄTTNING - ersätter en specifik text i en textsträng med en annan. Syntax: SUBSTITUTE (text; gammal_text; ny_text). Exempel: SUBSTITUTE ("bil"; "bil"; "") = "mobil".
3. STORA - ersätter alla tecken i en sträng med versaler. Det enda argumentet är text. Exempel: ÖVRE ("maskin") = "BIL". Vi behöver den här funktionen för att göra skiftlägesokänsliga sökningar. När allt kommer omkring, UPPER ("bil") = UPPER ("Maskin")

För att hitta förekomsten av en viss textsträng i en annan måste du ta bort alla dess förekomster i den ursprungliga och jämföra längden på den resulterande strängen med den ursprungliga:

DLSTR (”Tariff MTS Super MTS”) - DLSTR (”Tariff Super”) = 6

Och dividera sedan denna skillnad med längden på strängen vi letade efter:

6 / DLSTR ("MTS") = 2

Det är exakt två gånger som raden "MTS" ingår i originalet.

Det återstår att skriva denna algoritm på formlerspråket (låt oss beteckna med "text" texten där vi letar efter händelser och med "sökt" - den vars antal förekomster vi är intresserade av):

= (DLSTR (text) -LSTR (SUBSTITUTE (UPPER (text); UPPER (sökning), ""))) / DLSTR (sökning)

I vårt exempel ser formeln ut så här:

= (DLSTR (A2) -LSTR (SUBSTITUTER (ÖVRE (A2), ÖVRE ($ E $ 1), “”))) / DLSTR ($ E $ 1)