Excel gyvenimas įsilaužimo tiems, kurie dalyvauja ataskaitinį ir duomenų apdorojimo

Renat ne pirmas kartas, kai svečias autorius kalba apie Layfhakere. Anksčiau mes paskelbtas puikią medžiagą iš jo apie tai, kaip sukurti mokymo planą: pagrindinis knyga ir internetiniai ištekliai, taip pat pažangus algoritmas sukurti mokymo planą.

Šis straipsnis yra keletas paprastų būdų, kurie padeda supaprastinti Excel darbą. jie yra ypač naudinga tiems, kurie dalyvauja valdymo ataskaitas, parengia analitinį įvairovė ataskaitas, pagrįstas iškrautus 1C pranešimų ir kitų formų šių pristatymų ir diagramas vadovavimas. Negalima apsimesti absoliuti naujovė - viena ar kita forma, šie metodai tikrai aptarta forumuose arba straipsnyje nurodytas.

Paprasta alternatyva VLOOKUP ir HLOOKUP, jei reikalingi vertės nėra pirmoje lentelės stulpelyje: Peržiūrėti, indeksas + RUNGTYNIŲ

VLOOKUP (VLOOKUP) Ir AAR (HLOOKUP) tik dirbti, jei siektinos vertės yra pirmame stulpelyje arba eilutėje stalo, iš kurio norite gauti informaciją.

Kitais atvejais, yra dvi galimybės:

1. Naudokite peržiūros funkcija (paiešką).
   Ji turi tokią sintaksę: žiūrėti (
   instagram viewer
   lookup_value Paieška vektorius; vektor_rezultata). Bet ji teisingai dirbti jums reikia nuotolio reikšmių Paieška vektorius Jie buvo rūšiuojami didėjimo tvarka:
2. RUNGTYNIŲ naudoti funkcijas (MATCH) derinys ir indekso (indeksas).
   RUNGTYNIŲ funkcija grąžina serijos numerį į masyvo elementą (su jo pagalba galima rasti, į lentelės eilutę norimas elementas), ir funkcija indeksas grąžina masyvo elemento su iš anksto nustatytu numeriu (kuris, kaip žinome, funkcija RUNGTYNIŲ).Sintaksės variantai:
   • MATCH (lookup_value massiv_poiska; MATCH_TYPE) - mūsų atveju, mes turime palyginimo "tiksliu atitikimu" natūra, tai atitinka 0.
   • indeksas (masyvo; ROW_NUM; [COLUMN_NUM]). Šiuo atveju, tai nėra būtina nurodyti stulpelio numerį, kaip masyvas susideda iš vienoje eilutėje.

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

Užduotis - užpildyti langelius su į (priklausomai nuo stovi kiekvienoje lentelės eilutėje, ne tik pirmojo bloko eilutėje knygų šia tema) skiltyje verčių:

Pasirinkite stulpelį "temą", paspauskite ant į "Home" mygtuką grupės juosta "Raskite ir pasirinkite» → «Pažymėkite langelių grupę» → «Tuščia ląstelių "ir pradėkite rašyti formulę (ty, įdėti lygybės ženklą) ir nuoroda į ląstelę iš viršaus, tiesiog spausdami rodyklę aukštyn klaviatūra. Tada paspauskite Ctrl + Enter ". Po to, duomenys gali būti išsaugotas kaip vertės, nes formulės nebereikia:

Kaip rasti klaidų formulę

Skaičiavimo atskira dalis formulė

Suprasti sudėtingą formulę (kurioje, kaip funkcijos argumentai naudojant kitus funkcija, tai yra, kai kurios funkcijos yra įdėta į kitą), arba rasti jame klaidų šaltinį, dažnai reikia apskaičiuoti dalis. Yra du paprasti būdai:

1. Norėdami apskaičiuoti tinkamą dalį į formulę juostoje formulę, pasirinkite pusės, ir paspauskite F9:
   
   Šiame pavyzdyje, ten buvo problema su funkcija SEARCH (paieška) - ten buvo sumaišyti argumentai. Svarbu prisiminti, kad, jei jūs neturite atšaukti funkciją skaičiavimus ir paspauskite Enter, tada apskaičiuojamas dalis liks numeris.
2. Paspauskite "Apskaičiuokite formulę" į grupės "formulę" ant juostos:
   
   
   Lange, kuris pasirodo, galima apskaičiuoti formule žingsnis po žingsnio, ir nustatyti, kokiame etape ir kokia funkcija klaida (jei yra):
   

Kaip nustatyti, kas lemia, kad formulę arba nuoroda

Norėdami nustatyti, kuri iš ląstelių priklauso nuo atsižvelgiant į "formulę" į kaspinėliu grupės formulę, spauskite mygtuką "Trace" precedentais ":

Yra rodyklės nukreipta iš kurio rezultatas priklauso.

Jei pasirodo simbolis ant pasirinkto paveikslėlio raudonai, formulė priklauso nuo ląstelių, kurios yra kituose sąrašuose arba kitų knygų:

Paspaudę ant jo, mes galime pamatyti, kur ląstelės arba intervalas įtaka:

Šalia "Trace precedentų" yra mygtukas "išlaikytiniai", darbo tą patį kelią: ji rodo rodykles iš aktyvaus ląstelių formulę į ląsteles, kurios priklauso nuo jo.

Mygtukas "Pašalinti rodyklės", esančios pačiame bloke, leidžia pašalinti precedentais išlaikytiniai arba abu tipai karto rodyklėmis:

Kaip rasti ląstelių su keliais lapais sumą (numeris, vidurkis) vertybes

Tarkime, jūs turite keletą panašių lapą su duomenimis, kuriuos norite pridėti, rasti arba tvarkomi kitu būdu:

Norėdami tai padaryti, ląstelė, kurioje norite pamatyti rezultatą, įveskite standartinę formulę, kaip antai SUM (suma), ir įveskite argumentą po dvitaškio ir pavardė pirmą lapą nuo lapų sąrašą jums reikia procesas:

Jūs gausite į mobilųjį adresų B3 sumą su lapų "duomenys 1", "duomenys2", "data3":

Tokie sprendžiami aktyvumas lakštai išdėstyti paeiliui. Sintaksė yra: bruožas = (pervyy_list: posledniy_list nuoroda į asortimentą!).

Kaip automatiškai statyti stereotipiniams frazės

Naudojant pagrindinius principus dirbant su teksto Excel ir keletą paprastų funkcijų, gali būti paruošti formalus frazės ataskaitas. Keletas darbo principai su tekstu:

• Sujungti tekstą su ženklu ir (galite pakeisti savo funkciją Jungiant (Jungiant), bet tai ne daug naudoti).
• Tekstas visada parašyta kabutėse, ląstelių literatūros su tekstu - ne visada.
• Norėdami gauti specialių rašmenų "kabutes", naudojant simbolių funkcija (CHAR) argumentą 32.

PAVYZDYS šablonų kūrimo frazių naudojant formulės:

sukelti:

Šiuo atveju, be funkcijos simbolis (CHAR) (rodyti kabučių), naudojant funkciją IF (IF), kuri leidžia pakeisti tekstą Priklausomai nuo to, ar yra teigiama tendencija pardavimų ir teksto (tekstas) funkcija, leidžianti rodyti numerį į bet kurią formatas. Jos sintaksė yra aprašyta toliau:

TEKSTAS (vertė; formatas)

Formatas yra nurodyta kabutėse tokiu pačiu būdu kaip jei įrašėte pasirinktinį formatą į "Format Cells".

Jūs galite automatizuoti sudėtingesnius tekstus. Mano praktikoje buvo automatizuoti ilgas, tačiau eiliniai komentarai apie valdymo sąskaitų formatu "skaičius sumažėjo / rožinė tuo xx dėl plano daugiausia dėl to, kad padidėjimas / sumaţėjimas dėl FAKTORA1 XX, padidinti / sumažėjimo FAKTORA2 YY... »su besikeičiančiais sąrašą veiksniai. Jeigu jums reikia parašyti tokias pastabas dažnai ir raštu gali algoritmavimo procesas - vertės, kai glumina sukurti formulę arba makrokomandą, kuri bus išgelbėti jus nuo bent dalies darbą.

Kaip išsaugoti kiekvienos ląstelės duomenis po susijungimo

Kai derinant ląstelės saugomi tik vieną reikšmę. "Excel" įspėja jus, kai jūs bandote sujungti langelius:

Taigi, jei jūs turėjo formulę, kuri priklauso nuo kiekvienos ląstelės, jis nustos dirbti po jų sąjunga (klaida # N / A linijos 3-4 pavyzdžius):

Sujungti ląsteles, kartu išsaugant duomenis kiekvienoje iš jų (galbūt turite formulę, kaip šiame pavyzdyje, abstrakčiai; galbūt norėsite sujungti ląsteles, bet išsaugoti visus duomenis ir ateityje, ar slėpti savo ketinimus), sujungti bet kokius celes lakštas, pasirinkite jas ir tada naudokite komandą "Formatas Painter" perdavimo formatą ląstelių, kad jūs turite ir sujungti:

Kaip sukurti kelių duomenų šaltinių suvestinė

Jei reikia statyti konsoliduojamos nuo įvairių duomenų šaltinių, būtina pridėti prie juosta arba sparčiosios prieigos įrankių juostos "suvestinės lentelės vedlį ir diagramas", kuriame yra tokia galimybė.

Jūs galite tai padaryti taip: "Failo» → «Nustatymai» → «Sparčiosios prieigos įrankių juosta» → «Visos komandos» → «PivotTable" vedlys ir schemos »→« Pridėti »:

Po to, juosta bus atitinkamą piktogramą Naršyti kuri sukelia kapitonas papildomai:

Kai paspausite ant jos, dialogo lange:

Jame jums reikės pasirinkti "Keletas juostas konsolidavimo" ir spauskite "Next". Kitame skyriuje, galite pasirinkti "Sukurti vieno puslapio lauką" arba "Sukurti puslapį lauką." Jei norite patys sugalvoti pavadinimą kiekvienam iš duomenų šaltinių - pasirinkti antrąjį elementą:

Kitame lange, pridėti visi diapazonai dėl kurio pagrindu sukurti santrauką, ir paprašykite jų pavadinimus:

Po to taško, vyks PivotTable ataskaitą paskutinio dialogo lange - apie esamą arba naują darbalapį:

Pranešti PivotTable "paruoštas. Filtras "Puslapis 1" galite pasirinkti tik vieną iš duomenų šaltinių, jei reikia:

Kaip apskaičiuoti įvykių teksto skaičių A B tekstą ( «MTS tarifų SuperMTS" - du įvykių santrumpą MTS)

Šiame pavyzdyje A skiltyje, ten yra keletas eilučių teksto, ir mūsų užduotis - išsiaiškinti, kiek kartų kiekvienas iš jų atitinka reikiamą tekstą ląstelių E1:

Norėdami išspręsti šią problemą, galite naudoti sudėtingą formulę, sudarytą iš šių funkcijų:

1. LEN (LEN) - apskaičiuoja teksto ilgį, vienas argumentas - tekste. Pavyzdys: LEN ( "mašina") = 6.
2. Pavaduojantys nariai (pakaitalas) - pakeičia teksto eilutė tam tikrą tekstą su kitu. Sintaksė: pavaduojantys nariai (tekstas; star_tekst; nov_tekst). Pavyzdys: PAKAITALŲ ( "automobilis", "automobilis", "") = "mobili".
3. VIRŠUTINIS (viršutinis) - pakeičia visus simbolius į eilutę didžiosiomis raidėmis. Vienintelis argumentas - tekstas. Pavyzdys: viršutinę dalį ( "mašina") = "mašina". Ši funkcija bus reikalingas, kad paieškos didžiųjų ir mažųjų raidžių. Galų gale, VIRŠUTINIS ( "mašina") = UPPER ( "mašina")

Norėdami rasti tam tikrą įrašą teksto eilutės į kitą, jūs turite pašalinti visus savo įvykių originalus ir palyginti eilutę iš originalaus ilgis:

LEN ( "Tarifų super MTS MTS") - LEN ( "super kaina) = 6

Ir tada padalinti skirtumas iki linijos ilgio, mes ieškojome:

6 / LEN ( "MTS") = 2

Tai yra dvigubai eilutėje "MTS" yra originalas.

Belieka būti parašytas algoritmas formulių kalba (žymimas "tekstas" tekstas, kuris mes ieškome įrašą ir "pageidaujamas" - apie įvykius, kad mes esame suinteresuoti numeris):

= (LEN (tekstas) -DLSTR (PAKAITALŲ (viršutinę dalį (tekstas); Viršutinę dalį (ieškoma už); ""))) / LEN (ieškoma už)

Mūsų pavyzdyje, formulė yra tokia:

= (LEN (A2) -DLSTR (PAKAITALŲ (viršutinį (A2); viršutinę dalį ($ E $ 1); ""))) / LEN ($ E $ 1)