Excel life hacks tiems, kurie užsiima ataskaitų teikimu ir duomenų apdorojimu

2024 Autorius: Malcolm Clapton | [email protected]. Paskutinį kartą keistas: 2024-01-13 02:18

Šiame įraše Renatas Šagabutdinovas, leidyklos „Mann, Ivanov and Ferber“generalinio direktoriaus padėjėjas, dalijasi keletu puikių „Excel“gyvenimo būdų. Šie patarimai bus naudingi visiems, kurie užsiima įvairių ataskaitų teikimu, duomenų apdorojimu ir prezentacijų kūrimu.

Šiame straipsnyje pateikiami paprasti būdai, kaip supaprastinti darbą „Excel“. Jie ypač naudingi tiems, kurie užsiima vadovybės ataskaitų teikimu, rengia įvairias analimines ataskaitas pagal atsisiuntimus iš 1C ir kitas ataskaitas, formuoja iš jų pristatymus ir diagramas valdymui. Nepretenduoju į absoliučią naujovę – vienaip ar kitaip šios technikos tikriausiai buvo aptariamos forumuose ar minimos straipsniuose.

Paprastos VLOOKUP ir HLOOKUP alternatyvos, jei norimos reikšmės nėra pirmame lentelės stulpelyje: LOOKUP, INDEX + SEARCH

Funkcijos VLOOKUP ir HLOOKUP veikia tik tuo atveju, jei norimos reikšmės yra pirmajame lentelės, iš kurios planuojate gauti duomenis, stulpelyje arba eilutėje.

Kitu atveju yra dvi parinktys:

1. Naudokite funkciją LOOKUP.

   Ji turi tokią sintaksę: LOOKUP (paieškos_vertė; lookup_vector; result_vector). Tačiau, kad jis veiktų tinkamai, rodinio_vektoriaus diapazono reikšmės turi būti rūšiuojamos didėjančia tvarka:

   

2. Naudokite MATCH ir INDEX funkcijų derinį.

   Funkcija MATCH grąžina masyve esančio elemento eilės numerį (su jos pagalba galima sužinoti, kurioje lentelės eilutėje yra ieškomas elementas), o funkcija INDEX – masyvo elementą su duotu skaičiumi (kurį išsiaiškinsime naudojant MATCH funkciją).

   

   Funkcijos sintaksė:

   • PAIEŠKA (search_value; search_array; match_type) – mūsų atveju reikia atitikties tipo „tiksli atitiktis“, jis atitinka skaičių 0.

   • INDEX (masyvas; eilutės_numeris; [stulpelio_numeris]). Tokiu atveju stulpelio numerio nurodyti nereikia, nes masyvą sudaro viena eilutė.

Kaip greitai užpildyti tuščius sąrašo langelius

Užduotis yra užpildyti langelius stulpelyje su viršuje esančiomis reikšmėmis (kad tema būtų kiekvienoje lentelės eilutėje, o ne tik pirmoje temos knygų bloko eilutėje):

Pasirinkite stulpelį „Tema“, spustelėkite juostelę grupėje „Pagrindinis“, spustelėkite mygtuką „Rasti ir pasirinkite“→ „Pasirinkti langelių grupę“→ „Tušti langeliai“ir pradėkite įvesti formulę (ty padėkite lygų ženklas) ir nukreipkite į langelį viršuje, tiesiog spustelėdami klaviatūros rodyklę aukštyn. Po to paspauskite Ctrl + Enter. Po to gautus duomenis galite išsaugoti kaip reikšmes, nes formulės nebereikalingos:

Kaip rasti klaidų formulėje

Atskiros formulės dalies apskaičiavimas

Norint suprasti sudėtingą formulę (kurioje kitos funkcijos naudojamos kaip funkcijos argumentai, tai yra, kai kurios funkcijos yra įdėtos į kitas) arba rasti joje klaidų šaltinį, dažnai reikia apskaičiuoti jos dalį. Yra du paprasti būdai:

1. Norėdami apskaičiuoti dalį formulės tiesiai formulės juostoje, pasirinkite tą dalį ir paspauskite F9:

   

   Šiame pavyzdyje kilo problema su funkcija SEARCH – joje buvo pakeisti argumentai. Svarbu atsiminti, kad jei neatšauksite funkcijos dalies skaičiavimo ir paspausite Enter, tada apskaičiuota dalis liks skaičiumi.

2. Spustelėkite mygtuką Apskaičiuoti formulę juostelės grupėje Formulės:

   

   Atsidariusiame lange galite žingsnis po žingsnio apskaičiuoti formulę ir nustatyti, kuriame etape ir kurioje funkcijoje įvyksta klaida (jei yra):

   

Kaip nustatyti, nuo ko priklauso arba nuo ko priklauso formulė

Norėdami nustatyti, nuo kurių langelių priklauso formulė, juostelės grupėje Formulės spustelėkite mygtuką Affecting Cells:

Rodomos rodyklės, nurodančios, nuo ko priklauso skaičiavimo rezultatas.

Jei paveikslėlyje raudonai paryškintas simbolis rodomas, formulė priklauso nuo langelių kituose lapuose ar kitose knygose:

Paspaudę ant jo pamatysime, kur tiksliai yra įtakojančios ląstelės ar diapazonai:

Šalia mygtuko „Įtakoti langelius“yra mygtukas „Priklausomos ląstelės“, kuris veikia taip pat: rodomos rodyklės iš aktyvaus langelio su formule į langelius, kurie nuo jo priklauso.

Tame pačiame bloke esantis mygtukas „Pašalinti rodykles“leidžia pašalinti rodykles į įtakojančias ląsteles, rodykles į priklausomas ląsteles arba abiejų tipų rodykles vienu metu:

Kaip rasti ląstelių verčių sumą (skaičių, vidurkį) iš kelių lapų

Tarkime, kad turite kelis to paties tipo lapus su duomenimis, kuriuos norite pridėti, skaičiuoti arba kokiu nors kitu būdu apdoroti:

Norėdami tai padaryti, langelyje, kuriame norite matyti rezultatą, įveskite standartinę formulę, pavyzdžiui, SUM (SUM) ir nurodykite pirmojo ir paskutinio lapų pavadinimus iš tų lapų sąrašo, kuriuos reikia apdoroti. argumentas, atskirtas dvitaškiu:

Ląstelių su adresu B3 sumą gausite iš lapų „Data1“, „Data2“, „Data3“:

Šis adresavimas veikia esantiems lapams nuosekliai … Sintaksė yra tokia: = FUNCTION (first_list: last_list! Diapazono nuoroda).

Kaip automatiškai sukurti šablonines frazes

Naudodami pagrindinius darbo su tekstu „Excel“principus ir keletą paprastų funkcijų, galite paruošti šablonines ataskaitų frazes. Keli darbo su tekstu principai:

• Tekstą sujungiame naudodami & ženklą (galite jį pakeisti funkcija CONCATENATE, bet tai neturi didelės prasmės).
• Tekstas visada rašomas kabutėse, nuorodos į langelius su tekstu visada be.
• Norėdami gauti paslaugos simbolį „kabutės“, naudokite funkciją CHAR su 32 argumentu.

Šablono frazės kūrimo naudojant formules pavyzdys:

Rezultatas:

Šiuo atveju, be funkcijos CHAR (kad būtų rodomos kabutės), naudojama funkcija IF, leidžianti keisti tekstą priklausomai nuo to, ar yra teigiama pardavimo tendencija, ir funkcija TEXT, kuri leidžia rodyti numerį bet kokiu formatu. Jo sintaksė aprašyta toliau:

TEKSTAS (reikšmė; formatas)

Formatas nurodomas kabutėse taip, lyg lange Format Cells įvedėte pasirinktinį formatą.

Sudėtingesni tekstai taip pat gali būti automatizuoti. Mano praktikoje buvo automatizuojami ilgi, bet įprasti komentarai vadovybės ataskaitoms formatu „INDIKATORIUS sumažėjo / padidėjo XX, palyginti su planu, daugiausia dėl 1 faktoriaus augimo / sumažėjimo XX, 2 faktoriaus augimo / sumažėjimo YY …“su besikeičiančiu veiksnių sąrašu. Jei tokius komentarus rašote dažnai ir jų rašymo procesas gali būti algoritmizuotas, kartą susimąsčius verta susikurti formulę ar makrokomandą, kuri sutaupytų bent dalį darbo.

Kaip saugoti duomenis kiekviename langelyje po sujungimo

Kai sujungiate langelius, išsaugoma tik viena reikšmė. „Excel“įspėja apie tai bandant sujungti langelius:

Atitinkamai, jei turėjote formulę, priklausančią nuo kiekvienos ląstelės, sujungus jas ji nustos veikti (# N / A klaida 3-4 pavyzdžio eilutėse):

Norėdami sujungti langelius ir vis tiek išsaugoti duomenis kiekviename iš jų (galbūt turite formulę, kaip šiame abstrakčiame pavyzdyje; galbūt norite sujungti langelius, bet palikti visus duomenis ateičiai arba paslėpti juos sąmoningai), sujunkite visus lapo langelius., pasirinkite juos ir naudokite komandą Format Painter, kad formatavimą perkeltumėte į langelius, kuriuos reikia sujungti:

Kaip sukurti suvestinę iš kelių duomenų šaltinių

Jei jums reikia sukurti suvestinę iš kelių duomenų šaltinių vienu metu, turėsite pridėti „PivotTable and Chart Wizard“prie juostos arba greitosios prieigos skydelio, kuriame yra tokia parinktis.

Tai galite padaryti taip: „Failas“→ „Parinktys“→ „Greitos prieigos įrankių juosta“→ „Visos komandos“→ „PivotTable ir diagramos vedlys“→ „Pridėti“:

Po to juostelėje pasirodys atitinkama piktograma, kurią spustelėjus iškviečiamas tas pats vedlys:

Spustelėjus jį, pasirodo dialogo langas:

Jame turite pasirinkti elementą „Keliuose konsolidavimo diapazonuose“ir spustelėkite „Kitas“. Kitame žingsnyje galite pasirinkti „Sukurti vieno puslapio lauką“arba „Sukurti puslapio laukus“. Jei norite savarankiškai sugalvoti pavadinimą kiekvienam duomenų šaltiniui, pasirinkite antrą elementą:

Kitame lange pridėkite visus diapazonus, kurių pagrindu bus kuriamas sukimasis, ir suteikite jiems pavadinimus:

Po to paskutiniame dialogo lange nurodykite, kur bus pateikta suvestinės lentelės ataskaita – esamame ar naujame lape:

Suvestinės lentelės ataskaita paruošta. Filtre „Puslapis 1“, jei reikia, galite pasirinkti tik vieną iš duomenų šaltinių:

Kaip apskaičiuoti teksto A pasikartojimų skaičių B tekste ("MTS SuperMTS tarifas" - du santrumpos MTS atvejai)

Šiame pavyzdyje A stulpelyje yra kelios teksto eilutės, o mūsų užduotis yra išsiaiškinti, kiek kartų kiekvienoje iš jų yra E1 langelyje esantis paieškos tekstas:

Norėdami išspręsti šią problemą, galite naudoti sudėtingą formulę, kurią sudaro šios funkcijos:

1. DLSTR (LEN) – apskaičiuoja teksto ilgį, vienintelis argumentas yra tekstas. Pavyzdys: DLSTR („mašina“) = 6.
2. SUBSTITUTE – pakeičia konkretų tekstą teksto eilutėje kitu. Sintaksė: SUBSTITUTE (tekstas; senas_tekstas; naujas_tekstas). Pavyzdys: SUBSTITUTE („automobilis“; „auto“; „“) = „mobilusis“.
3. UPPER – visus eilutės simbolius pakeičia didžiosiomis raidėmis. Vienintelis argumentas yra tekstas. Pavyzdys: UPPER ("mašina") = "CAR". Mums reikia šios funkcijos, kad galėtume atlikti paieškas be didžiųjų ir mažųjų raidžių. Galų gale, UPPER ("automobilis") = UPPER ("Mašina")

Norėdami rasti tam tikros teksto eilutės atsiradimą kitoje, turite ištrinti visus jos atvejus pirminėje ir palyginti gautos eilutės ilgį su originalia:

DLSTR („Tarifas MTS Super MTS“) – DLSTR („Super tarifas“) = 6

Tada padalykite šį skirtumą iš ieškomos eilutės ilgio:

6 / DLSTR („MTS“) = 2

Lygiai du kartus linija „MTS“yra įtraukta į originalią.

Belieka parašyti šį algoritmą formulių kalba (tekstu pažymėkime tekstą, kuriame ieškome įvykių, o „ieškoma“- tą, kurio pasikartojimų skaičius mus domina):

= (DLSTR (tekstas) -LSTR (PAKEITIMAS (VIRŠUTINIS (tekstas); UPPER) (paieška), ""))) / DLSTR (paieška)

Mūsų pavyzdyje formulė atrodo taip:

= (DLSTR (A2) -LSTR (PAKEITIMAS (UPPER (A2), UPPER ($ E $ 1), ""))) / DLSTR ($ E $ 1)