Hakimet e jetës së Excel për ata që janë të përfshirë në raportimin dhe përpunimin e të dhënave

2024 Autor: Malcolm Clapton | [email protected]. E modifikuara e fundit: 2024-01-13 02:59

Në këtë postim, Renat Shagabutdinov, Ndihmës Drejtor i Përgjithshëm i Shtëpisë Botuese Mann, Ivanov dhe Ferber, ndan disa hakime interesante të jetës në Excel. Këto këshilla do të jenë të dobishme për këdo që është i përfshirë në raportime të ndryshme, përpunimin e të dhënave dhe krijimin e prezantimeve.

Ky artikull përmban teknika të thjeshta për të thjeshtuar punën tuaj në Excel. Ato janë veçanërisht të dobishme për ata që janë të angazhuar në raportimin e menaxhimit, përgatitin raporte të ndryshme analitike bazuar në shkarkimet nga 1C dhe raporte të tjera, formojnë prezantime dhe diagrame prej tyre për menaxhim. Unë nuk pretendoj të jem risi absolute - në një formë ose në një tjetër, këto teknika ndoshta janë diskutuar në forume ose janë përmendur në artikuj.

Alternativa të thjeshta për VLOOKUP dhe HLOOKUP, nëse vlerat e dëshiruara nuk janë në kolonën e parë të tabelës: LOOKUP, INDEX + SEARCH

Funksionet VLOOKUP dhe HLOOKUP funksionojnë vetëm nëse vlerat e dëshiruara janë në kolonën ose rreshtin e parë të tabelës nga e cila planifikoni të merrni të dhëna.

Përndryshe, ekzistojnë dy opsione:

1. Përdorni funksionin LOOKUP.

   Ajo ka sintaksën e mëposhtme: LOOKUP (vlera_kërkuese; vektor_kërkuesi; vektor_rezultati). Por që ai të funksionojë si duhet, vlerat e gamës view_vector duhet të renditen në rend rritës:

   

2. Përdorni një kombinim të funksioneve MATCH dhe INDEX.

   Funksioni MATCH kthen numrin rendor të një elementi në grup (me ndihmën e tij mund të gjeni se në cilin rresht të tabelës është elementi i kërkuar), dhe funksioni INDEX kthen një element grupi me një numër të caktuar (të cilin do ta zbulojmë duke përdorur funksionin MATCH).

   

   Sintaksa e funksionit:

   • KËRKO (vlera_kërkuese; grup_kërkimi; lloji_përputhje) - për rastin tonë, na duhet një lloj përputhshmërie "përputhje e saktë", që korrespondon me numrin 0.

   • INDEX (vargu; rreshti_numri; [numri_kolona]). Në këtë rast, nuk keni nevojë të specifikoni numrin e kolonës, pasi grupi përbëhet nga një rresht.

Si të mbushni shpejt qelizat boshe në një listë

Detyra është të plotësoni qelizat në kolonë me vlerat në krye (në mënyrë që tema të jetë në secilën rresht të tabelës, dhe jo vetëm në rreshtin e parë të bllokut të librave mbi temën):

Zgjidhni kolonën "Subjekti", klikoni në shiritin në grupin "Home", butonin "Gjeni dhe zgjidhni" → "Zgjidhni një grup qelizash" → "Qeliza bosh" dhe filloni të futni formulën (d.m.th., vendosni një të barabartë shenjë) dhe referojuni qelizës në krye, thjesht duke klikuar shigjetën lart në tastierën tuaj. Pas kësaj, shtypni Ctrl + Enter. Pas kësaj, të dhënat e marra mund t'i ruani si vlera, pasi formulat nuk nevojiten më:

Si të gjeni gabimet në një formulë

Llogaritja e një pjese të veçantë të një formule

Për të kuptuar një formulë komplekse (në të cilën funksionet e tjera përdoren si argumente funksioni, domethënë, disa funksione janë të vendosura në të tjera) ose për të gjetur burimin e gabimeve në të, shpesh duhet të llogaritni një pjesë të saj. Ka dy mënyra të thjeshta:

1. Për të llogaritur një pjesë të një formule direkt në shiritin e formulave, zgjidhni atë pjesë dhe shtypni F9:

   

   Në këtë shembull, kishte një problem me funksionin SEARCH - argumentet u këmbyen në të. Është e rëndësishme të mbani mend se nëse nuk anuloni llogaritjen e pjesës së funksionit dhe shtypni Enter, atëherë pjesa e llogaritur do të mbetet një numër.

2. Klikoni butonin Llogarit Formulën në grupin Formula në shirit:

   

   Në dritaren që shfaqet, mund të llogarisni formulën hap pas hapi dhe të përcaktoni se në cilën fazë dhe në cilin funksion ndodh një gabim (nëse ka):

   

Si të përcaktohet se nga çfarë varet ose i referohet një formulë

Për të përcaktuar se nga cilat qeliza varet një formulë, në grupin Formulat në shirit, klikoni butonin Ndikimi i qelizave:

Shigjetat duken për të treguar se nga varet rezultati i llogaritjes.

Nëse simboli i theksuar në figurë shfaqet me të kuqe, atëherë formula varet nga qelizat në fletë të tjera ose në libra të tjerë:

Duke klikuar mbi të, ne mund të shohim saktësisht se ku ndodhen qelizat ose vargjet ndikuese:

Pranë butonit "Ndikimi i qelizave" është butoni "Qelizat e varura", i cili funksionon në të njëjtën mënyrë: shfaq shigjetat nga qeliza aktive me një formulë në qelizat që varen prej saj.

Butoni "Hiq shigjetat", i vendosur në të njëjtin bllok, ju lejon të hiqni shigjetat në qelizat ndikuese, shigjetat në qelizat e varura ose të dy llojet e shigjetave menjëherë:

Si të gjeni shumën (numrin, mesataren) e vlerave të qelizave nga fletët e shumta

Le të themi se keni disa fletë të të njëjtit lloj me të dhëna që dëshironi të shtoni, numëroni ose përpunoni në ndonjë mënyrë tjetër:

Për ta bërë këtë, në qelizën në të cilën dëshironi të shihni rezultatin, vendosni një formulë standarde, për shembull SUM (SUM) dhe specifikoni emrin e fletës së parë dhe të fundit nga lista e atyre fletëve që duhet të përpunoni në argumenti, i ndarë me dy pika:

Ju do të merrni shumën e qelizave me adresën B3 nga fletët "Data1", "Data2", "Data3":

Kjo adresim funksionon për fletët e vendosura në mënyrë të vazhdueshme … Sintaksa është si më poshtë: = FUNCTION (lista e parë: lista e fundit! Referenca e diapazonit).

Si të ndërtoni automatikisht fraza shabllone

Duke përdorur parimet bazë të punës me tekstin në Excel dhe disa funksione të thjeshta, mund të përgatisni fraza shabllone për raporte. Disa parime të punës me tekstin:

• Ne e lidhim tekstin duke përdorur shenjën & (mund ta zëvendësoni me funksionin CONCATENATE, por kjo nuk ka shumë kuptim).
• Teksti shkruhet gjithmonë në thonjëza, referencat për qelizat me tekst janë gjithmonë pa.
• Për të marrë karakterin e shërbimit "thonjëza", përdorni funksionin CHAR me argumentin 32.

Një shembull i krijimit të një fraze shabllon duke përdorur formula:

Rezultati:

Në këtë rast, përveç funksionit CHAR (për të shfaqur kuotat), përdoret funksioni IF, i cili ju lejon të ndryshoni tekstin në varësi të faktit nëse ka një trend pozitiv të shitjeve dhe funksioni TEXT, i cili ju lejon të shfaqni numër në çdo format. Sintaksa e tij përshkruhet më poshtë:

TEXT (vlera; formati)

Formati specifikohet në thonjëza, njësoj sikur po futni një format të personalizuar në dritaren Format Cells.

Tekstet më komplekse gjithashtu mund të automatizohen. Në praktikën time, ka pasur automatizimin e komenteve të gjata, por rutinë për raportimin e menaxhmentit në formatin “INDICATOR ra / u rrit me XX në raport me planin, kryesisht për shkak të rritjes / rënies së FACTOR1 me XX, rritjes / rënies së FACTOR2 me YY …” me një listë faktorësh në ndryshim. Nëse shkruani shpesh komente të tilla dhe procesi i shkrimit të tyre mund të algoritmizohet, ia vlen të krijoni një formulë ose një makro që do t'ju kursejë të paktën një pjesë të punës.

Si të ruani të dhënat në secilën qelizë pas bashkimit

Kur bashkoni qelizat, ruhet vetëm një vlerë. Excel paralajmëron për këtë kur përpiqet të bashkojë qelizat:

Prandaj, nëse keni pasur një formulë në varësi të secilës qelizë, ajo do të ndalojë së punuari pasi t'i kombinojë ato (# N / Një gabim në rreshtat 3-4 të shembullit):

Për të bashkuar qelizat dhe për të ruajtur ende të dhënat në secilën prej tyre (ndoshta keni një formulë si në këtë shembull abstrakt; ndoshta dëshironi të bashkoni qelizat, por t'i mbani të gjitha të dhënat për të ardhmen ose t'i fshehni ato qëllimisht), bashkoni çdo qelizë në fletë, zgjidhni ato dhe më pas përdorni komandën Format Painter për të transferuar formatimin në qelizat që ju duhet të kombinoni:

Si të ndërtoni një strumbullar nga burime të shumta të dhënash

Nëse keni nevojë të krijoni një bosht nga disa burime të dhënash në të njëjtën kohë, do t'ju duhet të shtoni "PivotTable and Chart Wizard" në shiritin ose panelin e aksesit të shpejtë, i cili ka një opsion të tillë.

Ju mund ta bëni këtë si më poshtë: "Skedari" → "Opsionet" → "Shiri i veglave të aksesit të shpejtë" → "Të gjitha komandat" → "Magjistari i tabelës dhe grafikut" → "Shto":

Pas kësaj, një ikonë përkatëse do të shfaqet në shirit, duke klikuar mbi të cilën thërret i njëjti magjistar:

Kur klikoni mbi të, shfaqet një kuti dialogu:

Në të, ju duhet të zgjidhni artikullin "Në disa vargje të konsolidimit" dhe të klikoni "Next". Në hapin tjetër, mund të zgjidhni "Krijo një fushë faqeje" ose "Krijo fushat e faqes". Nëse dëshironi të gjeni në mënyrë të pavarur një emër për secilin prej burimeve të të dhënave, zgjidhni artikullin e dytë:

Në dritaren tjetër, shtoni të gjitha vargjet mbi bazën e të cilave do të ndërtohet boshti dhe jepini emrat:

Pas kësaj, në kutinë e fundit të dialogut, specifikoni se ku do të vendoset raporti i tabelës kryesore - në një fletë ekzistuese ose të re:

Raporti i tabelës kryesore është gati. Në filtrin "Page 1", mund të zgjidhni vetëm një nga burimet e të dhënave, nëse është e nevojshme:

Si të llogarisni numrin e shfaqjeve të tekstit A në tekstin B ("tarifa MTS SuperMTS" - dy shfaqje të shkurtesës MTS)

Në këtë shembull, kolona A përmban disa rreshta teksti dhe detyra jonë është të zbulojmë se sa herë secila prej tyre përmban tekstin e kërkimit të vendosur në qelizën E1:

Për të zgjidhur këtë problem, mund të përdorni një formulë komplekse të përbërë nga funksionet e mëposhtme:

1. DLSTR (LEN) - llogarit gjatësinë e tekstit, argumenti i vetëm është teksti. Shembull: DLSTR ("makinë") = 6.
2. ZËVENDËSIMI - zëvendëson një tekst specifik në një varg teksti me një tjetër. Sintaksa: SUBSTITUTE (tekst; teksti i vjetër; teksti i ri). Shembull: ZËVENDËSIM ("makinë"; "auto"; "") = "celular".
3. UPPER - zëvendëson të gjitha karakteret në një varg me shkronja të mëdha. Argumenti i vetëm është teksti. Shembull: UPPER ("makinë") = "CAR". Ne kemi nevojë për këtë funksion për të bërë kërkime të pandjeshme ndaj shkronjave të vogla. Në fund të fundit, UPPER ("makinë") = UPPER ("Makina")

Për të gjetur shfaqjen e një vargu të caktuar teksti në një tjetër, duhet të fshini të gjitha dukuritë e tij në origjinalin dhe të krahasoni gjatësinë e vargut që rezulton me atë origjinal:

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

Dhe pastaj ndajeni këtë ndryshim me gjatësinë e vargut që po kërkonim:

6 / DLSTR ("MTS") = 2

Është pikërisht dy herë që linja "MTS" përfshihet në atë origjinale.

Mbetet ta shkruajmë këtë algoritëm në gjuhën e formulave (le të shënojmë me "tekst" tekstin në të cilin kërkojmë dukuritë, dhe me "kërkuar" - atë për numrin e dukurive të të cilit ne jemi të interesuar):

= (DLSTR (tekst) -LSTR (ZËVENDËSIM (SIPER (tekst); UPPER (kërkim), ""))) / DLSTR (kërkim)

Në shembullin tonë, formula duket si kjo:

= (DLSTR (A2) -LSTR (ZËVENDËSIMI (SIPER (A2), SIPER ($ E $ 1), ""))) / DLSTR ($ E $ 1)