Exceli elu häkkimine neile, kes on seotud aruandluse ja andmetöötlusega

2024 Autor: Malcolm Clapton | [email protected]. Viimati modifitseeritud: 2024-01-13 01:20

Selles postituses jagab kirjastuse Mann, Ivanov ja Ferber peadirektori assistent Renat Šagabutdinov mõnda lahedat Exceli elulugu. Need näpunäited on kasulikud kõigile, kes tegelevad erinevate aruandluse, andmetöötluse ja esitluste loomisega.

See artikkel sisaldab lihtsaid võtteid Excelis töö lihtsustamiseks. Need on eriti kasulikud neile, kes tegelevad juhtimisaruandlusega, koostavad 1C-st ja muudest aruannetest allalaadimiste põhjal erinevaid analüütilisi aruandeid, vormistavad neist juhtkonna jaoks esitlusi ja diagramme. Ma ei pretendeeri absoluutsele uudsusele – ühel või teisel kujul on neid tehnikaid ilmselt foorumites käsitletud või artiklites mainitud.

Lihtsad alternatiivid funktsioonidele VLOOKUP ja HLOOKUP, kui soovitud väärtused pole tabeli esimeses veerus: LOOKUP, INDEX + SEARCH

Funktsioonid VLOOKUP ja HLOOKUP töötavad ainult siis, kui soovitud väärtused on tabeli esimeses veerus või reas, millest kavatsete andmeid hankida.

Vastasel juhul on kaks võimalust:

1. Kasutage funktsiooni LOOKUP.

   Sellel on järgmine süntaks: LOOKUP (otsingu_väärtus; lookup_vector; result_vector). Kuid selleks, et see korralikult töötaks, tuleb vahemiku view_vector väärtused järjestada kasvavas järjekorras:

   

2. Kasutage funktsioonide MATCH ja INDEX kombinatsiooni.

   Funktsioon MATCH tagastab massiivi elemendi järjekorranumbri (selle abil saate teada, millises tabeli real otsitav element asub) ja funktsioon INDEX tagastab massiivi elemendi antud numbriga (mille saame teada kasutades funktsiooni MATCH).

   

   Funktsiooni süntaks:

   • OTSING (otsingu_väärtus; otsingu_massiiv; vaste_tüüp) - meie puhul vajame sobivat tüüpi "täpne vaste", see vastab arvule 0.

   • INDEX (massiiv; rea_number; [veeru_number]). Sel juhul ei pea te veeru numbrit määrama, kuna massiiv koosneb ühest reast.

Kuidas kiiresti täita loendi tühje lahtreid

Ülesanne on täita veerus olevad lahtrid ülaosas olevate väärtustega (nii et teema oleks tabeli igal real, mitte ainult teemaraamatute ploki esimesel real):

Valige veerg "Teema", klõpsake rühmas "Kodu" lindil, nuppu "Otsi ja valige" → "Vali lahtrite rühm" → "Tühjad lahtrid" ja alustage valemi sisestamist (st pange võrdne märk) ja vaadake ülaosas asuvat lahtrit, klõpsates lihtsalt klaviatuuril ülesnoolt. Pärast seda vajutage Ctrl + Enter. Pärast seda saate saadud andmed salvestada väärtustena, kuna valemeid pole enam vaja:

Kuidas valemis vigu leida

Valemi eraldi osa arvutamine

Keerulise valemi (milles funktsiooni argumentidena kasutatakse muid funktsioone, st mõned funktsioonid on teistesse pesastatud) mõistmiseks või selles vigade allika leidmiseks tuleb sageli osa sellest arvutada. On kaks lihtsat viisi:

1. Valemi osa arvutamiseks otse valemiribal valige see osa ja vajutage klahvi F9:

   

   Selles näites oli probleem OTSING funktsiooniga – selles vahetati argumendid. Oluline on meeles pidada, et kui te funktsiooni osa arvutamist ei tühista ja vajutage sisestusklahvi, siis jääb arvutatud osa numbriks.

2. Klõpsake lindi rühmas Valemid nuppu Arvuta valem:

   

   Ilmuvas aknas saate arvutada valemi samm-sammult ja määrata, millises etapis ja millises funktsioonis viga ilmneb (kui see on olemas):

   

Kuidas teha kindlaks, millest valem sõltub või millele viitab

Et määrata, millistest lahtritest valem sõltub, klõpsake lindi rühmas Valemid nuppu Affecting Cells:

Ilmuvad nooled, mis näitavad, millest arvutustulemus sõltub.

Kui kuvatakse pildil punasega esiletõstetud sümbol, sõltub valem teiste lehtede või teiste raamatute lahtritest:

Sellel klõpsates näeme täpselt, kus mõjutavad lahtrid või vahemikud asuvad:

Nupu "Mõjutavad rakud" kõrval on nupp "Sõltuvad lahtrid", mis töötab samamoodi: kuvab aktiivsest lahtrist koos valemiga nooled sellest sõltuvate lahtrite juurde.

Samas plokis asuv nupp "Eemalda nooled" võimaldab eemaldada nooled mõjutavatele lahtritele, nooled sõltuvatele lahtritele või mõlemat tüüpi nooled korraga:

Kuidas leida mitmelt lehelt lahtrite väärtuste summat (arv, keskmine)

Oletame, et teil on mitu sama tüüpi lehte andmetega, mida soovite lisada, loendada või muul viisil töödelda.

Selleks sisestage lahtrisse, milles soovite tulemust näha, standardvalem, näiteks SUM (SUM), ja määrake nende lehtede loendi esimese ja viimase lehe nimi, mida peate töötlema. argument, eraldatuna kooloniga:

Aadressiga B3 lahtrite summa saate lehtedelt "Data1", "Data2", "Data3":

See adresseerimine töötab lehtede puhul, mis asuvad järjekindlalt … Süntaks on järgmine: = FUNCTION (first_list: last_list! Vahemiku viide).

Mallfraaside automaatne koostamine

Kasutades Excelis tekstiga töötamise põhiprintsiipe ja mõnda lihtsat funktsiooni, saate koostada aruannete jaoks mallifraasid. Mitmed tekstiga töötamise põhimõtted:

• Teksti ühendame &-märgi abil (võite selle asendada funktsiooniga CONCATENATE, kuid sellel pole erilist mõtet).
• Tekst on alati kirjutatud jutumärkides, viited tekstiga lahtritele on alati ilma.
• Teenusmärgi "jutumärkide" saamiseks kasutage funktsiooni CHAR argumendiga 32.

Näide mallifraasi loomisest valemite abil:

Tulemus:

Sel juhul kasutatakse lisaks funktsioonile CHAR (jutumärkide kuvamiseks) funktsiooni IF, mis võimaldab muuta teksti sõltuvalt sellest, kas on positiivne müügitrend, ja funktsiooni TEXT, mis võimaldab kuvada number mis tahes vormingus. Selle süntaksit kirjeldatakse allpool:

TEKST (väärtus; formaat)

Vorming on määratud jutumärkides, just nagu sisestaksite kohandatud vormingu aknasse Format Cells.

Keerulisemaid tekste saab ka automatiseerida. Minu praktikas automatiseeriti juhtkonna aruandlusele pikki, kuid rutiinseid kommentaare vormingus “INDIKAATOR langes / tõusis XX võrra võrreldes plaaniga, peamiselt tänu TEguri 1 kasvule / langusele XX võrra, TEguri 2 kasvule / langusele. YY …” muutuva tegurite loeteluga. Kui kirjutate selliseid kommentaare sageli ja nende kirjutamise protsessi saab algoritmeerida, tasub ükskord mõistatuslikult luua valem või makro, mis säästab vähemalt osa tööst.

Kuidas salvestada andmeid igas lahtris pärast ühendamist

Lahtrite liitmisel säilib ainult üks väärtus. Excel hoiatab selle eest lahtrite liitmisel:

Seega, kui teil oli igast lahtrist sõltuv valem, lakkab see pärast nende ühendamist töötamast (# N / viga näite ridadel 3-4):

Lahtrite liitmiseks ja nende andmete säilitamiseks (võib-olla on teil selline valem nagu selles abstraktses näites; võib-olla soovite lahtreid liita, kuid kõik andmed tuleviku jaoks alles jätta või tahtlikult peita), ühendage kõik lehel olevad lahtrid, valige need ja kasutage seejärel käsku Format Painter, et viia vorming üle lahtritesse, mida peate kombineerima:

Kuidas luua pivoti mitmest andmeallikast

Kui teil on vaja pivoti ehitada korraga mitmest andmeallikast, peate lindile või kiirpääsupaneelile lisama "PivotTable and Chart Wizard", millel on selline võimalus.

Seda saate teha järgmiselt: "Fail" → "Valikud" → "Kiirpääsu tööriistariba" → "Kõik käsud" → "PivotTable-liigendtabeli ja diagrammi viisard" → "Lisa":

Pärast seda ilmub lindile vastav ikoon, millel klõpsates helistab sama viisard:

Kui klõpsate sellel, kuvatakse dialoogiboks:

Selles peate valima üksuse "Mitmes konsolideerimisvahemikus" ja klõpsama nuppu "Järgmine". Järgmise sammuna saate valida "Loo üks lehe väli" või "Loo leheväljad". Kui soovite igale andmeallikale iseseisvalt nime välja mõelda, valige teine üksus:

Järgmises aknas lisage kõik vahemikud, mille alusel pivot koostatakse, ja andke neile nimed:

Pärast seda määrake viimases dialoogiboksis, kuhu pivot-tabeli aruanne paigutatakse - olemasolevale või uuele lehele:

Pivot-tabeli aruanne on valmis. Filtris "Lehekülg 1" saate vajadusel valida ainult ühe andmeallika:

Kuidas arvutada teksti A esinemiste arvu tekstis B ("MTS SuperMTS tariif" - kaks lühendi MTS esinemist)

Selles näites sisaldab veerg A mitut tekstirida ja meie ülesanne on välja selgitada, mitu korda igaüks neist sisaldab lahtris E1 asuvat otsinguteksti:

Selle probleemi lahendamiseks võite kasutada keerulist valemit, mis koosneb järgmistest funktsioonidest:

1. DLSTR (LEN) – arvutab teksti pikkuse, ainsaks argumendiks on tekst. Näide: DLSTR ("masin") = 6.
2. ASENDA – asendab kindla teksti tekstistringis teisega. Süntaks: SUBSTITUTE (tekst; vana_tekst; uus_tekst). Näide: SUBSTITUTE (“auto”; “auto”; “”) = “mobiil”.
3. UPPER – asendab kõik tähemärgid stringis suurtähtedega. Ainus argument on tekst. Näide: UPPER (“masin”) = “CAR”. Seda funktsiooni vajame tõstutundlike otsingute tegemiseks. Lõppude lõpuks, UPPER ("auto") = UPPER ("masin")

Teatud tekstistringi esinemise leidmiseks teises peate kustutama kõik selle esinemised algses stringis ja võrdlema saadud stringi pikkust algse stringiga:

DLSTR ("tariif MTS Super MTS") - DLSTR ("tariif super") = 6

Seejärel jagage see erinevus otsitava stringi pikkusega:

6 / DLSTR ("MTS") = 2

See on täpselt kaks korda, kui rida "MTS" sisaldub originaalis.

Jääb üle see algoritm kirjutada valemikeeles (tähistagem "tekstiga" teksti, milles esinemisi otsime, ja "otsitud" - selle, mille esinemiste arv meid huvitab):

= (DLSTR (tekst) -LSTR (ASENDATA (ÜLEMINE (tekst); UPPER (otsing), ""))) / DLSTR (otsing)

Meie näites näeb valem välja selline:

= (DLSTR (A2) -LSTR (ASENDAJA (ÜLEMINE (A2), ÜLEMINE ($ E $ 1), ""))) / DLSTR ($ E $ 1)