Excelovi življenjski vzorci za tiste, ki se ukvarjajo s poročanjem in obdelavo podatkov

2024 Avtor: Malcolm Clapton | [email protected]. Nazadnje spremenjeno: 2024-01-13 02:55

V tej objavi Renat Shagabutdinov, pomočnik generalnega direktorja založbe Mann, Ivanov in Ferber, deli nekaj kul Excelovih življenjskih trikov. Ti nasveti bodo koristni za vse, ki se ukvarjajo z različnimi poročanji, obdelavo podatkov in ustvarjanjem predstavitev.

Ta članek vsebuje preproste tehnike za poenostavitev vašega dela v Excelu. Še posebej so uporabni za tiste, ki se ukvarjajo s poročanjem o upravljanju, pripravljajo različna analitična poročila na podlagi prenosov iz 1C in drugih poročil, iz njih oblikujejo predstavitve in diagrame za upravljanje. Ne pretvarjam se, da sem absolutna novost - v takšni ali drugačni obliki so bile te tehnike verjetno obravnavane na forumih ali omenjene v člankih.

Enostavne alternative za VLOOKUP in HLOOKUP, če želene vrednosti niso v prvem stolpcu tabele: LOOKUP, INDEX + SEARCH

Funkciji VLOOKUP in HLOOKUP delujeta samo, če so želene vrednosti v prvem stolpcu ali vrstici tabele, iz katere nameravate pridobiti podatke.

Sicer pa sta dve možnosti:

1. Uporabite funkcijo LOOKUP.

   Ima naslednjo sintakso: LOOKUP (iskana_vrednost; iskani_vektor; rezultat_vektorja). Toda da bi deloval pravilno, morajo biti vrednosti obsega view_vector razvrščene v naraščajočem vrstnem redu:

   

2. Uporabite kombinacijo funkcij MATCH in INDEX.

   Funkcija MATCH vrne redno številko elementa v matriki (z njeno pomočjo lahko ugotovite, v kateri vrstici tabele je iskani element), funkcija INDEX pa vrne element matrike z dano številko (kar bomo ugotovili). z uporabo funkcije MATCH).

   

   Sintaksa funkcije:

   • SEARCH (search_value; search_array; match_type) - za naš primer potrebujemo tip ujemanja "exact match", ki ustreza številki 0.

   • INDEX (matrika; številka_vrstice; [številka_stolpca]). V tem primeru vam ni treba določiti številke stolpca, saj je niz sestavljen iz ene vrstice.

Kako hitro zapolniti prazne celice na seznamu

Naloga je izpolniti celice v stolpcu z vrednostmi na vrhu (tako da je tema v vsaki vrstici tabele in ne samo v prvi vrstici bloka knjig na to temo):

Izberite stolpec »Zadeva«, kliknite na trak v skupini »Domov«, gumb »Najdi in izberi« → »Izberi skupino celic« → »Prazne celice« in začnite vnašati formulo (torej vnesite enako znak) in se sklicujte na celico na vrhu, preprosto tako, da kliknete puščico navzgor na tipkovnici. Po tem pritisnite Ctrl + Enter. Po tem lahko prejete podatke shranite kot vrednosti, saj formule niso več potrebne:

Kako najti napake v formuli

Izračun ločenega dela formule

Če želite razumeti zapleteno formulo (v kateri se druge funkcije uporabljajo kot argumenti funkcije, to pomeni, da so nekatere funkcije ugnezdene v druge) ali da bi našli vir napak v njej, morate pogosto izračunati njen del. Obstajata dva enostavna načina:

1. Če želite izračunati del formule neposredno v vrstici s formulo, izberite ta del in pritisnite F9:

   

   V tem primeru je prišlo do težave s funkcijo SEARCH – v njej so bili zamenjani argumenti. Pomembno si je zapomniti, da če ne prekličete izračuna dela funkcije in pritisnete Enter, bo izračunani del ostal številka.

2. Kliknite gumb Izračunaj formulo v skupini Formule na traku:

   

   V oknu, ki se prikaže, lahko izračunate formulo korak za korakom in določite, v kateri fazi in v kateri funkciji pride do napake (če obstaja):

   

Kako ugotoviti, od česa je formula odvisna ali na kaj se nanaša

Če želite ugotoviti, od katerih celic je odvisna formula, v skupini Formule na traku kliknite gumb Vpliva na celice:

Puščice kažejo, od česa je odvisen rezultat izračuna.

Če se prikaže simbol, označen na sliki z rdečo, je formula odvisna od celic na drugih listih ali v drugih knjigah:

S klikom nanj lahko natančno vidimo, kje se nahajajo vplivne celice ali obsegi:

Poleg gumba »Vpliv na celice« je gumb »Odvisne celice«, ki deluje na enak način: prikaže puščice od aktivne celice s formulo do celic, ki so od nje odvisne.

Gumb "Odstrani puščice", ki se nahaja v istem bloku, vam omogoča, da odstranite puščice do celic, ki vplivajo na celice, puščice do odvisnih celic ali obe vrsti puščic hkrati:

Kako najti vsoto (število, povprečje) vrednosti celic iz več listov

Recimo, da imate več listov iste vrste s podatki, ki jih želite dodati, prešteti ali obdelati na drug način:

Če želite to narediti, v celico, v kateri želite videti rezultat, vnesite standardno formulo, na primer SUM (SUM), in določite ime prvega in zadnjega lista s seznama tistih listov, ki jih morate obdelati. argument, ločen z dvopičjem:

Prejeli boste vsoto celic z naslovom B3 iz listov "Podatki1", "Podatki2", "Podatki3":

To naslavljanje deluje za locirane liste dosledno … Sintaksa je naslednja: = FUNCTION (prvi_seznam: zadnji_seznam! Referenca obsega).

Kako samodejno zgraditi predloge besednih zvez

Z uporabo osnovnih načel dela z besedilom v Excelu in nekaj preprostih funkcij lahko pripravite predloge besednih zvez za poročila. Več načel dela z besedilom:

• Besedilo povežemo z znakom & (lahko ga zamenjate s funkcijo CONCATENATE, vendar to nima veliko smisla).
• Besedilo je vedno napisano v narekovajih, sklicevanja na celice z besedilom so vedno brez.
• Če želite dobiti "narekovaje" storitvenega znaka, uporabite funkcijo CHAR z argumentom 32.

Primer ustvarjanja predlogne fraze z uporabo formul:

rezultat:

V tem primeru se poleg funkcije CHAR (za prikaz narekovajev) uporablja funkcija IF, ki omogoča spreminjanje besedila glede na pozitiven prodajni trend, in funkcija TEXT, ki omogoča prikaz številko v kateri koli obliki. Njegova sintaksa je opisana spodaj:

BESEDILO (vrednost; oblika)

Oblika je podana v narekovajih, tako kot če bi v okno Oblikovanje celic vnesli obliko po meri.

Bolj zapletena besedila je mogoče tudi avtomatizirati. V moji praksi je prišlo do avtomatizacije dolgih, a rutinskih pripomb k vodstvenemu poročanju v obliki »INDIKATOR padel/zvišal za XX glede na načrt, predvsem zaradi rasti/pada FACTOR1 za XX, rasti/padec FACTOR2 za XX. YY …« s spreminjajočim se seznamom dejavnikov. Če takšne komentarje pišete pogosto in je postopek njihovega pisanja mogoče algoritmizirati, je vredno enkrat zmedeno ustvariti formulo ali makro, ki vam bo prihranil vsaj nekaj dela.

Kako shraniti podatke v vsako celico po povezovanju

Ko združite celice, se ohrani samo ena vrednost. Excel opozori na to, ko poskuša združiti celice:

V skladu s tem, če ste imeli formulo, odvisno od vsake celice, bo prenehala delovati, ko jih združite (# N / napaka v vrsticah 3-4 primera):

Če želite združiti celice in še vedno ohraniti podatke v vsaki od njih (morda imate formulo, kot je v tem abstraktnem primeru; morda želite celice združiti, vendar obdržite vse podatke za prihodnost ali jih namerno skrijete), združite vse celice na listu, jih izberite in nato uporabite ukaz Format Painter, da prenesete oblikovanje v celice, ki jih morate združiti:

Kako zgraditi pivot iz več virov podatkov

Če morate naenkrat zgraditi pivot iz več podatkovnih virov, boste morali na trak ali ploščo za hitri dostop, ki ima tako možnost, dodati "čarovnika za vrtilne tabele in grafikone".

To lahko storite na naslednji način: "Datoteka" → "Možnosti" → "Orodna vrstica za hitri dostop" → "Vsi ukazi" → "Čarovnik za vrtilne tabele in grafikone" → "Dodaj":

Po tem se bo na traku pojavila ustrezna ikona, s klikom na katero pokliče istega čarovnika:

Ko kliknete nanj, se prikaže pogovorno okno:

V njem morate izbrati postavko "V več konsolidacijskih obsegih" in klikniti "Naprej". V naslednjem koraku lahko izberete "Ustvari eno stransko polje" ali "Ustvari polja strani". Če želite samostojno pripraviti ime za vsak vir podatkov, izberite drugi element:

V naslednjem oknu dodajte vse obsege, na podlagi katerih bo zgrajen pivot, in jim dajte imena:

Nato v zadnjem pogovornem oknu določite, kam bo poročilo vrtilne tabele postavljeno - na obstoječem ali novem listu:

Poročilo vrtilne tabele je pripravljeno. V filtru "Stran 1" lahko po potrebi izberete samo enega od virov podatkov:

Kako izračunati število pojavljanj besedila A v besedilu B ("MTS SuperMTS tarifa" - dve pojavitvi kratice MTS)

V tem primeru stolpec A vsebuje več vrstic besedila, naša naloga pa je ugotoviti, kolikokrat vsaka od njih vsebuje iskalno besedilo, ki se nahaja v celici E1:

Za rešitev te težave lahko uporabite kompleksno formulo, ki jo sestavljajo naslednje funkcije:

1. DLSTR (LEN) - izračuna dolžino besedila, edini argument je besedilo. Primer: DLSTR ("stroj") = 6.
2. SUBSTITUTE - zamenja določeno besedilo v besedilnem nizu z drugim. Sintaksa: SUBSTITUTE (besedilo; staro_besedilo; novo_besedilo). Primer: SUBSTITUTE (»avto«; »avto«; »«) = »mobilni«.
3. UPPER - zamenja vse znake v nizu z velikimi črkami. Edini argument je besedilo. Primer: UPPER (»stroj«) = »AVTO«. To funkcijo potrebujemo za iskanje brez upoštevanja velikih in malih črk. Konec koncev, UPPER ("avto") = UPPER ("stroj")

Če želite najti pojav določenega besedilnega niza v drugem, morate izbrisati vse njegove pojavljanje v izvirnem nizu in primerjati dolžino nastalega niza z izvirnim:

DLSTR ("Tarifa MTS Super MTS") - DLSTR ("Tarifa Super") = 6

In nato to razliko delite z dolžino niza, ki smo ga iskali:

6 / DLSTR (»MTS«) = 2

Natanko dvakrat je vrstica "MTS" vključena v prvotno.

Ostaja še, da ta algoritem zapišemo v jeziku formul (z "besedilo" označimo besedilo, v katerem iščemo pojavitve, in z "iskano" - tisto, katerega število pojavljanj nas zanima):

= (DLSTR (besedilo) -LSTR (NADOMESTNIK (GOR (besedilo); UPPER (iskanje), ""))) / DLSTR (iskanje)

V našem primeru je formula videti takole:

= (DLSTR (A2) -LSTR (NADOMESTNIK (ZGORNJI (A2), ZGORNJI ($ E $ 1), “”))) / DLSTR ($ E $ 1)