Excel life hacks vir diegene wat betrokke is by verslagdoening en dataverwerking

2024 Outeur: Malcolm Clapton | [email protected]. Laas verander: 2024-01-13 00:29

In hierdie pos deel Renat Shagabutdinov, Assistent-hoofdirekteur van Mann, Ivanov en Ferber Publishing House, 'n paar oulike Excel-lewenshacks. Hierdie wenke sal nuttig wees vir almal wat betrokke is by verskeie verslagdoening, dataverwerking en die skep van aanbiedings.

Hierdie artikel bevat eenvoudige tegnieke om jou werk in Excel te vereenvoudig. Dit is veral nuttig vir diegene wat betrokke is by bestuursverslaggewing, verskeie analitiese verslae voorberei op grond van aflaaie vanaf 1C en ander verslae, aanbiedings en diagramme daaruit vir bestuur vorm. Ek gee nie voor as absolute nuwigheid nie – in een of ander vorm is hierdie tegnieke waarskynlik op die forums bespreek of in artikels genoem.

Eenvoudige alternatiewe vir VLOOKUP en HLOOKUP, as die gewenste waardes nie in die eerste kolom van die tabel is nie: OPSOEK, INDEKS + SOEK

Die VLOOKUP- en HLOOKUP-funksies werk slegs as die verlangde waardes in die eerste kolom of ry van die tabel is waaruit jy beplan om data te kry.

Andersins is daar twee opsies:

1. Gebruik die OPSOEK-funksie.

   Dit het die volgende sintaksis: OPSOEK (opsoek_waarde; opsoek_vektor; resultaat_vektor). Maar om korrek te werk, moet die waardes van die view_vector-reeks in stygende volgorde gesorteer word:

   

2. Gebruik 'n kombinasie van MATCH en INDEX funksies.

   Die MATCH-funksie gee die rangnommer van 'n element in die skikking terug (met sy hulp kan jy vind in watter ry van die tabel die gesoekte element is), en die INDEX-funksie gee 'n skikkingselement met 'n gegewe getal terug (wat ons sal uitvind met behulp van die MATCH-funksie).

   

   Funksie sintaksis:

   • SOEK (soekwaarde; soekskikking; pasmaattipe) - vir ons geval benodig ons 'n ooreenstemmende tipe "presiese pasmaat", dit stem ooreen met die getal 0.

   • INDEKS (skikking; lynnommer; [kolomnommer]). In hierdie geval hoef jy nie die kolomnommer te spesifiseer nie, aangesien die skikking uit een ry bestaan.

Hoe om leë selle vinnig in 'n lys te vul

Die taak is om die selle in die kolom in te vul met die waardes aan die bokant (sodat die onderwerp in elke ry van die tabel is, en nie net in die eerste ry van die blok boeke oor die onderwerp nie):

Kies die "Onderwerp"-kolom, klik op die lint in die "Tuis"-groep, die "Vind en kies"-knoppie → "Kies 'n groep selle" → "Leë selle" en begin die formule invoer (dit wil sê, plaas 'n gelyke teken) en verwys na die sel aan die bokant, eenvoudig deur op-pyltjie op jou sleutelbord te klik. Druk daarna Ctrl + Enter. Daarna kan u die ontvangde data as waardes stoor, aangesien die formules nie meer nodig is nie:

Hoe om foute in 'n formule te vind

Berekening van 'n aparte deel van 'n formule

Om 'n komplekse formule te verstaan (waarin ander funksies as funksie-argumente gebruik word, dit wil sê sommige funksies is geneste in ander) of om die bron van foute daarin te vind, moet jy dikwels 'n deel daarvan bereken. Daar is twee maklike maniere:

1. Om 'n deel van 'n formule reg in die formulebalk te bereken, kies daardie deel en druk F9:

   

   In hierdie voorbeeld was daar 'n probleem met die SEARCH-funksie - argumente is daarin omgeruil. Dit is belangrik om te onthou dat as jy nie die berekening van die deel van die funksie kanselleer en Enter druk nie, dan sal die berekende deel 'n getal bly.

2. Klik op die Bereken Formule-knoppie in die Formules-groep op die lint:

   

   In die venster wat verskyn, kan jy die formule stap vir stap bereken en bepaal in watter stadium en in watter funksie 'n fout voorkom (indien enige):

   

Hoe om te bepaal waarna 'n formule afhang of waarna verwys

Om te bepaal van watter selle 'n formule afhang, klik in die Formules-groep op die lint op die Affekterende selle-knoppie:

Pyltjies verskyn om aan te dui waarvan die berekeningsresultaat afhang.

As die simbool wat in die prentjie in rooi uitgelig is, vertoon word, hang die formule af van die selle op ander velle of in ander boeke:

Deur daarop te klik, kan ons presies sien waar die beïnvloedende selle of reekse geleë is:

Langs die "Beïnvloed selle"-knoppie is die "Afhanklike selle"-knoppie, wat op dieselfde manier werk: dit vertoon pyle van die aktiewe sel met 'n formule na die selle wat daarvan afhanklik is.

Die knoppie "Verwyder pyle", geleë in dieselfde blok, laat jou toe om pyle na beïnvloedende selle, pyle na afhanklike selle, of albei tipes pyle gelyktydig te verwyder:

Hoe om die som (getal, gemiddeld) van selwaardes uit verskeie velle te vind

Kom ons sê jy het verskeie velle van dieselfde tipe met data wat jy wil byvoeg, tel of op 'n ander manier wil verwerk:

Om dit te doen, voer in die sel waarin jy die resultaat wil sien 'n standaardformule in, byvoorbeeld SOM (SOM), en spesifiseer die naam van die eerste en laaste velle uit die lys van daardie velle wat jy moet verwerk in die argument, geskei deur 'n dubbelpunt:

U sal die som van selle met die adres B3 van die velle "Data1", "Data2", "Data3" ontvang:

Hierdie adressering werk vir velle wat geleë is konsekwent … Die sintaksis is soos volg: = FUNKSIE (eerste_lys: laaste_lys! Reeksverwysing).

Hoe om sjabloonfrases outomaties te bou

Deur die basiese beginsels van werk met teks in Excel en 'n paar eenvoudige funksies te gebruik, kan jy sjabloonfrases vir verslae voorberei. Verskeie beginsels van werk met teks:

• Ons voeg die teks saam met die &-teken (jy kan dit vervang met die CONCATENATE-funksie, maar dit maak nie veel sin nie).
• Die teks is altyd in aanhalingstekens geskryf, verwysings na selle met teks is altyd sonder.
• Om die dienskarakter "aanhalingstekens" te kry, gebruik die CHAR-funksie met argument 32.

'n Voorbeeld van die skep van 'n sjabloonfrase deur formules te gebruik:

Resultaat:

In hierdie geval, bykomend tot die CHAR-funksie (om aanhalings te vertoon), word die IF-funksie gebruik, wat jou toelaat om die teks te verander na gelang van of daar 'n positiewe verkoopsneiging is, en die TEXT-funksie wat jou toelaat om die nommer in enige formaat. Die sintaksis daarvan word hieronder beskryf:

TEKS (waarde; formaat)

Die formaat word in aanhalingstekens gespesifiseer, net asof jy 'n gepasmaakte formaat in die Format Cells-venster invoer.

Meer komplekse tekste kan ook geoutomatiseer word. In my praktyk was daar die outomatisering van lang, maar roetine-kommentaar tot bestuursverslaggewing in die formaat “INDIKATOR het met XX gedaal relatief tot die plan, hoofsaaklik as gevolg van die groei / afname van FAKTOR1 met XX, groei / afname van FAKTOR2 met YY …” met 'n veranderende lys van faktore. As jy gereeld sulke opmerkings skryf en die proses om dit te skryf kan gealgoritmiseer word, is dit die moeite werd om een keer raaiselagtig te wees om 'n formule of 'n makro te skep wat jou ten minste van die werk sal spaar.

Hoe om data in elke sel te stoor na aaneenskakeling

Wanneer jy selle saamvoeg, word slegs een waarde behou. Excel waarsku hieroor wanneer u probeer om selle saam te voeg:

Gevolglik, as jy 'n formule gehad het wat afhang van elke sel, sal dit ophou werk nadat jy dit gekombineer het (# N / A fout in reëls 3-4 van die voorbeeld):

Om selle saam te voeg en steeds die data in elkeen van hulle te bewaar (miskien het jy 'n formule soos in hierdie abstrakte voorbeeld; miskien wil jy selle saamvoeg, maar hou alle data vir die toekoms of versteek dit doelbewus), voeg enige selle op die blad saam, kies hulle en gebruik dan die Format Painter-opdrag om die formatering oor te dra na die selle wat jy moet kombineer:

Hoe om 'n spilpunt uit verskeie databronne te bou

As jy 'n spilpunt van verskeie databronne gelyktydig moet bou, sal jy die "Draaitabel en Grafiek Wizard" by die lint of vinnige toegangspaneel moet voeg, wat so 'n opsie het.

Jy kan dit soos volg doen: "Lêer" → "Opsies" → "Vinnige toegang-nutsbalk" → "Alle opdragte" → "Draaitabel en grafiek-assistent" → "Voeg by":

Daarna sal 'n ooreenstemmende ikoon op die lint verskyn, deur te klik wat dieselfde towenaar oproep:

Wanneer jy daarop klik, verskyn 'n dialoogkassie:

Daarin moet u die item "In verskeie konsolidasiereekse" kies en op "Volgende" klik. In die volgende stap kan jy "Skep een bladsyveld" of "Skep bladsyvelde" kies. As jy onafhanklik met 'n naam vir elk van die databronne vorendag wil kom, kies die tweede item:

Voeg in die volgende venster al die reekse by op grond waarvan die spilpunt gebou sal word, en gee hulle name:

Daarna, in die laaste dialoogkassie, spesifiseer waar die spilpunttabelverslag geplaas sal word - op 'n bestaande of nuwe blad:

Die spilpunttabelverslag is gereed. In die "Bladsy 1"-filter kan jy slegs een van die databronne kies, indien nodig:

Hoe om die aantal gevalle van teks A in teks B te bereken ("MTS SuperMTS-tarief" - twee gevalle van die afkorting MTS)

In hierdie voorbeeld bevat kolom A verskeie teksreëls, en ons taak is om uit te vind hoeveel keer elkeen van hulle die soekteks bevat wat in sel E1 geleë is:

Om hierdie probleem op te los, kan jy 'n komplekse formule gebruik wat uit die volgende funksies bestaan:

1. DLSTR (LEN) - bereken die lengte van die teks, die enigste argument is die teks. Voorbeeld: DLSTR ("masjien") = 6.
2. PLAASVERVANG - vervang 'n spesifieke teks in 'n teksstring met 'n ander. Sintaksis: PLAASVERVANGER (teks; ou_teks; nuwe_teks). Voorbeeld: PLAASVERVANGER ("motor"; "motor"; "") = "mobiel".
3. HOOF - vervang alle karakters in 'n string met hoofletters. Die enigste argument is teks. Voorbeeld: BOON (“masjien”) = “MOTOR”. Ons het hierdie funksie nodig om hoofletter-onsensitiewe soektogte te doen. Immers, BOVENSTE ("motor") = BOONSTE ("Masjien")

Om die voorkoms van 'n sekere teksstring in 'n ander te vind, moet jy al sy voorkoms in die oorspronklike een uitvee en die lengte van die resulterende string met die oorspronklike een vergelyk:

DLSTR (“Tarief MTS Super MTS”) - DLSTR (“Tarief Super”) = 6

En deel dan hierdie verskil deur die lengte van die tou waarna ons gesoek het:

6 / DLSTR (“MTS”) = 2

Dit is presies twee keer dat die reël "MTS" by die oorspronklike een ingesluit is.

Dit bly om hierdie algoritme in die taal van formules te skryf (laat ons die teks aandui waarin ons na gebeurtenisse soek, en met "gesoek" - die een in wie se aantal gebeurtenisse ons belangstel, met "teks" aan te dui):

= (DLSTR (teks) -LSTR (PLAASVERVANGER (BOONSTE (teks); BOONSTE (soek), ""))) / DLSTR (soek)

In ons voorbeeld lyk die formule soos volg:

= (DLSTR (A2) -LSTR (PLAASVERVANGER (BOONSTE (A2), BOONSTE ($ E $ 1), “”))) / DLSTR ($ E $ 1)