„Excel“ gyvavimo įsilaužimas tiems, kurie dalyvauja ataskaitose ir duomenų apdorojime

<

Šiame leidinyje Renat Shagabutdinov, leidyklos „Mann“, „Ivanov“ ir „Ferber“ generalinio direktoriaus padėjėjas, dalijasi vėsiomis „Excel“ gyvenimo sąlygomis. Šie patarimai bus naudingi visiems, kurie užsiima įvairiomis ataskaitomis, duomenų apdorojimu ir pristatymų kūrimu.

Renatas nėra pirmas kartas, kai svečių autorius rašo „Pc-Articles“. Anksčiau mes paskelbėme puikią medžiagą apie tai, kaip parengti mokymo planą: pagrindines knygas ir internetinius išteklius, taip pat laipsnišką mokymo plano sudarymo algoritmą.

Šiame straipsnyje yra paprastų triukų, kad būtų lengviau dirbti „Excel“. Jie yra ypač naudingi tiems, kurie dalyvauja valdymo ataskaitose, rengia įvairias analitines ataskaitas, pagrįstas 1C parsisiųsti ir kitomis ataskaitomis, jų pateikimo formomis ir valdymo diagramomis. Nemanau, kad esu absoliuti naujovė - vienoje ar kitoje formoje šie metodai tikriausiai buvo aptarti forumuose arba paminėti straipsniuose.

Paprastos CDF ir PDW alternatyvos, jei norimos vertės nėra pirmoje lentelės skiltyje: VIEW, INDEX + MATCH

„VLOOKUP“ ir „HLOOKUP“ funkcijos veikia tik tuo atveju, jei vertybės, kurių ieškote, yra pirmojoje lentelės stulpelyje arba eilutėje, iš kurios planuojate gauti duomenis.

Kitais atvejais yra dvi parinktys:

  1. Naudokite funkciją LOOKUP.
    Ji turi tokią sintaksę: VIEW ( lookup_value; view_vector; results_value ). Tačiau, norint tinkamai veikti, būtina, kad vektoriaus_vaizdo diapazono vertės būtų rūšiuojamos didėjančia tvarka:
  2. Naudokite MATCH ir INDEX derinį.
    „MATCH“ funkcija grąžina masyvo elemento eilės numerį (su pagalba galite rasti, kurią lentelės eilutę ieškote elemento), o INDEX funkcija grąžina masyvo elementą su nurodytu skaičiumi (kurį mes nustatysime naudojant MATCH funkciją). Funkcijų sintaksė:
    • MATCH ( paieškos vertė; paieškos masyvas; atitikties tipas ) - mums reikia „tikslaus atitikimo“ žemėlapio tipo, jis atitinka skaitmenį 0.
    • INDEX ( masyvas; eilutės numeris; [column_number] ). Tokiu atveju stulpelio numeris nebūtinas, nes masyvą sudaro viena eilutė.

Kaip greitai užpildyti tuščias sąrašo ląsteles

Užduotis yra užpildyti stulpelyje esančias ląsteles su viršutinėmis reikšmėmis (taip, kad objektas būtų kiekvienoje lentelės eilutėje, o ne tik pirmoje eilutėje esančių knygų bloko eilutėje):

Pasirinkite stulpelį „Dalykai“, spustelėkite „Rasti ir paryškinkite“ mygtuką namų grupėje ir pasirinkite ląstelių grupę → Ištuštinti langelius ir pradėkite įvesti formulę (t. Y. Įdėkite vienodą ženklą) ir pažiūrėkite į langelį iš viršaus tiesiog paspausdami rodyklė ant klaviatūros. Po to paspauskite Ctrl + Enter. Po to duomenis galite išsaugoti kaip reikšmes, nes formulės nebėra reikalingos:

Kaip rasti klaidų formulėje

Atskiros formulės dalies apskaičiavimas

Norint suprasti sudėtingą formulę (kurioje kitos funkcijos naudojamos kaip funkcijų argumentai, ty kai kurios funkcijos yra įdėtos kitose) arba rasti klaidas, dažnai reikia apskaičiuoti jo dalį. Yra du paprasti būdai:

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


    Šiame pavyzdyje kilo problema su SEARCH funkcija - jame argumentai buvo sumaišyti. Svarbu prisiminti, kad jei neatšauksite funkcijos dalies skaičiavimo ir paspauskite „Enter“, tada apskaičiuota dalis išliks skaičiumi.

  2. „Formulės“ grupės juostoje spustelėkite mygtuką „Apskaičiuoti formulę“:

    Pasirodžiusiame lange galite apskaičiuoti formulę žingsniais ir nustatyti, kuriame etape ir kokioje funkcijoje įvyksta klaida (jei ji yra):

Kaip nustatyti, kas priklauso nuo formulės arba yra jos nuoroda

Norėdami nustatyti, kurios ląstelės priklauso nuo formulės, juostelės grupėje „Formulės“ spustelėkite mygtuką „Įtakojančios ląstelės“:

Rodomos rodyklės, rodančios, koks yra skaičiavimo rezultatas.

Jei rodomas raudonai pažymėtas simbolis, formulė priklauso nuo ląstelių, esančių kituose lapuose arba kitose knygose:

Paspaudę ant jo, pamatysime, kur yra įtakos elementai ar diapazonai:

Šalia mygtuko „Įtakojančios ląstelės“ yra mygtukas „Priklausomos ląstelės“, kuris veikia panašiai: jis rodo rodykles iš aktyviosios ląstelės su formulėmis, priklausančiomis nuo jo priklausančioms ląstelėms.

Mygtukas „Pašalinti rodykles“, esantis tame pačiame bloke, leidžia pašalinti rodykles, kad paveiktų ląsteles, rodykles į priklausomas ląsteles arba abu rodyklių tipus vienu metu:

Kaip rasti kelių lapų ląstelių sumą (kiekį, vidurkį)

Tarkime, kad turite kelis tos pačios rūšies lapus su duomenimis, kuriuos norite pridėti, apskaičiuoti ar apdoroti kitu būdu:

Norėdami tai padaryti, langelyje, kuriame norite matyti rezultatą, įveskite standartinę formulę, pvz., SUM (SUM), ir argumente, po dvitaškio, pirmųjų ir paskutinių lapų pavadinimą iš tų lapų sąrašo, kuriuos reikia apdoroti:

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

Tai susiję su serijomis išdėstytų lapų darbais. Sintaksė yra tokia: = FUNKCIJA ( pirmas_list: last_list! Nuoroda į diapazoną ).

Kaip automatiškai sukurti šablonų frazes

Naudodamiesi pagrindiniais principais dirbant su tekstu „Excel“ ir keliomis paprastomis funkcijomis, galite parengti ataskaitų šablonų frazes. Kai kurie darbo su tekstu principai:

  • Mes sujungiame tekstą su & ženklu (galite jį pakeisti CONCATENATE funkcija, tačiau tai nėra prasminga).
  • Tekstas visada rašomas kabutėmis, ląstelių nuorodos su tekstu visada yra be.
  • Norėdami gauti paslaugų ženklą „kabučių“, mes naudojame CHAR funkciją su argumentu 32.

Šablono frazės sukūrimo pavyzdys naudojant formulę:

Rezultatas:

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

TEKSTAS ( vertė; formatas )

Formatas nurodomas kabutėse, kaip ir jūs, jei įvestumėte pasirinktinį formatą lange „Formuoti langus“.

Galite automatizuoti sudėtingesnius tekstus. Mano praktikoje, buvo automatizuota ilgų, bet įprastinių pastabų dėl valdymo ataskaitų forma „INDIKATORIUS sumažėjo / išaugo XX atžvilgiu, daugiausia dėl to, kad padidėjo / sumažėjo FACTOR1 iki XX, padidėjo / sumažėjo FACTOR2 iki YY ...“ su kintančiu veiksnių sąrašu. Jei rašote tokius komentarus dažnai ir jų rašymo procesas gali būti algoritmas - tai verta vieną kartą sukurti formulę ar makrokomandą, kuri padės jums sutaupyti bent dalį darbo.

Kaip išsaugoti duomenis kiekviename langelyje po susijungimo

Sujungiant ląsteles, išsaugoma tik viena vertė. „Excel“ įspėja apie tai, kai bando sujungti langelius:

Atitinkamai, jei turėjote formulę, kuri priklauso nuo kiekvienos ląstelės, ji sujungs po to, kai juos sujungs (klaida # Н / Д 3-4 eilutėse):

Norėdami sujungti ląsteles ir tuo pat metu išsaugoti duomenis kiekviename iš jų (galbūt turite formulę, kaip ir šiame abstrakčiame pavyzdyje; galbūt norėsite sujungti ląsteles, bet išsaugoti visus duomenis ateityje arba paslėpti juos sąmoningai), sujunkite visas lapo ląsteles, pasirinkite juos ir naudokite komandą „Formatuoti modelį“, jei norite perkelti formatą į ląsteles, kurias reikia sujungti:

Kaip sukurti kelių duomenų santrauką sv

Jei jums reikės iš karto sudaryti santrauką iš kelių duomenų, turėsite pridėti „Pivot Tables“ ir „Charts“ vedlį į juostą arba greitosios prieigos skydelį, kuris turi šią parinktį.

Tai galite padaryti taip: „Failas“ → „Parametrai“ → „Greitas prieigos skydas“ → „Visos komandos“ → „Perkot lentelės ir diagramos vedlys“ → „Pridėti“:

Po to juostoje pasirodys atitinkama piktograma, spustelėję, kuriuos skambučius jis pats:

Kai spustelėsite, pasirodys dialogo langas:

Jame reikia pasirinkti „Keliuose konsolidavimo intervaluose“ ir spustelėkite „Kitas“. Kitoje pastraipoje galite pasirinkti „Sukurti vieną puslapio lauką“ arba „Sukurti puslapio laukus“. Jei norite savarankiškai sugalvoti kiekvieno duomenų pavadinimą, pasirinkite antrąjį elementą:

Kitame lange pridėkite visus intervalus, kuriuose bus sukurta santrauka, ir suteikite jiems vardus:

Po to paskutiniame dialogo lange nurodykite, kur bus pateikta suvestinė lentelė - esamame ar nauju lape:

„Pivot“ lentelės ataskaita parengta. Filtruose galite pasirinkti tik vieną iš duomenų, jei reikia:

Kaip apskaičiuoti teksto A atvejų skaičių tekste B („MTS SuperMTS tarifas“ - du sutrumpinimo MTS atvejai)

Šiame pavyzdyje A stulpelyje yra kelios teksto eilutės, o mūsų užduotis - išsiaiškinti, kiek kartų kiekvienas iš jų suras reikiamą tekstą, esančią ląstelėje E1:

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 tam tikrą teksto eilutės tekstą su kita. Sintaksė: SUBSTITUTE ( tekstas; star_text; new_text ). Pavyzdys: SUB („automobilis“; „auto“; “”) = „mobile“.
  3. UPPER (UPPER) - pakeičia visus eilutėje esančius simbolius didžiosiomis raidėmis. Vienintelis argumentas yra tekstas. Pavyzdys: UPPER („mašina“) = „MAŠINAS“. Mums reikės šios funkcijos, kad atliktume nejautrią paiešką. Galų gale, UPPER („mašina“) = UPPER („Machine“)

Jei norite rasti tam tikros teksto eilutės atsiradimą kitoje, turite pašalinti visus jo įvykius originale ir palyginti gautos eilutės ilgį su originalu:

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

Ir tada padalinkite šį skirtumą pagal eilutės, kurią ieškojome, ilgį:

6 / DLSTR („MTS“) = 2

Tai „MTS“ linija, kuri ateina du kartus.

Lieka rašyti šį algoritmą formulių kalba (mes nurodome „tekstą“ tekstą, kuriame ieškome įvykių, ir „norimas“ yra tas, kurio įvykių skaičius domina):

= (DLSTR ( tekstas ) -DLSTR (FIT (PRESP ( tekstas ); PROFIT ( privaloma ); ""))) / DLSTR ( būtina )

Mūsų pavyzdyje formulė yra tokia:

= (DLSTR (A2) -DLSTR (FIT (PROPISN (A2); PROPISN ($ E $ 1); “))) / DLSTR ($ E $ 1)

<

Populiarios Temos