Përmbajtje
Tabelat kryesore janë të mira për të gjithë - ato llogariten shpejt dhe konfigurohen në mënyrë fleksibël dhe dizajni mund të vendoset në to në mënyrë elegante, nëse kërkohet. Por ka edhe disa miza në vaj, në veçanti, pamundësia për të krijuar një përmbledhje, ku zona e vlerës nuk duhet të përmbajë numra, por tekst.
Le të përpiqemi të kapërcejmë këtë kufizim dhe të dalim me një "dy paterica" në një situatë të ngjashme.
Supozoni se kompania jonë transporton produktet e saj në kontejnerë në disa qytete në Vendin Tonë dhe Kazakistan. Kontejnerët dërgohen jo më shumë se një herë në muaj. Çdo enë ka një numër alfanumerik. Si të dhëna fillestare, ekziston një tabelë standarde që rendit dërgesat, nga e cila duhet të bëni një lloj përmbledhjeje në mënyrë që të shihni qartë numrat e kontejnerëve të dërguar në çdo qytet dhe çdo muaj:
Për lehtësi, le ta bëjmë tabelën me të dhënat fillestare "të zgjuar" paraprakisht duke përdorur komandën Faqja kryesore – Formatoni si tabelë (Shtëpia - Formatoni si tabelë) dhe i jepni një emër dërgesat tab konstruktor (Dizajn). Në të ardhmen, kjo do ta thjeshtojë jetën, sepse. do të jetë e mundur të përdoret emri i tabelës dhe kolonat e saj direkt në formula.
Metoda 1. Më e lehtë - përdorni Power Query
Power Query është një mjet super i fuqishëm për ngarkimin dhe transformimin e të dhënave në Excel. Kjo shtesë është ndërtuar në Excel si parazgjedhje që nga viti 2016. Nëse keni Excel 2010 ose 2013, mund ta shkarkoni dhe instaloni veçmas (plotësisht falas).
Të gjithë procesin, për qartësi, e analizova hap pas hapi në videon e mëposhtme:
Nëse nuk është e mundur të përdorni Power Query, atëherë mund të shkoni në mënyra të tjera - përmes një tabele kryesore ose formulave.
Metoda 2. Përmbledhje ndihmëse
Le të shtojmë një kolonë tjetër në tabelën tonë origjinale, ku duke përdorur një formulë të thjeshtë llogarisim numrin e çdo rreshti në tabelë:
Natyrisht, -1 nevojitet, sepse ne kemi një kokë me një rresht në tabelën tonë. Nëse tabela juaj nuk është në fillim të fletës, atëherë mund të përdorni një formulë pak më komplekse, por universale që llogarit ndryshimin në numrat e rreshtit aktual dhe kokës së tabelës:
Tani, në një mënyrë standarde, ne do të ndërtojmë një tabelë kryesore të llojit të dëshiruar bazuar në të dhënat tona, por në fushën e vlerës do të heqim fushën Numri i linjes në vend të asaj që duam enë:
Meqenëse nuk kemi disa kontejnerë në të njëjtin qytet në të njëjtin muaj, përmbledhja jonë, në fakt, do të japë jo shumën, por numrat e rreshtave të kontejnerëve që na duhen.
Për më tepër, mund të çaktivizoni totalet e mëdha dhe nëntotalet në skedë Konstruktori – Totale të përgjithshme и Nëntotalet (Dizajni - Totalet e mëdha, Nëntotalet) dhe në të njëjtin vend kaloni përmbledhjen në një plan urbanistik më të përshtatshëm të tabelës me butonin Raporto modelin (Raporti i paraqitjes).
Kështu, ne jemi tashmë në gjysmë të rrugës drejt rezultatit: kemi një tabelë ku, në kryqëzimin e qytetit dhe muajit, ka një numër rreshti në tabelën burimore, ku qëndron kodi i kontejnerit që na nevojitet.
Tani le të kopjojmë përmbledhjen (në të njëjtën fletë ose në një tjetër) dhe ta ngjitim atë si vlera, dhe më pas futim formulën tonë në zonën e vlerës, e cila do të nxjerrë kodin e kontejnerit me numrin e linjës që gjendet në përmbledhje:
funksion IF (NESE), në këtë rast, kontrollon që qeliza tjetër në përmbledhje të mos jetë bosh. Nëse bosh, atëherë nxirrni një varg teksti bosh "", dmth lini qelizën bosh. Nëse nuk është bosh, atëherë nxirreni nga kolona Enë tabelë burimore dërgesat përmbajtja e qelizës sipas numrit të rreshtit duke përdorur funksionin INDEKSI (INDEX).
Ndoshta e vetmja pikë jo shumë e dukshme këtu është fjala e dyfishtë Enë në formulë. Një formë kaq e çuditshme shkrimi:
Furnizime[[Enë]:[Enë]]
… është e nevojshme vetëm për të referuar kolonën Enë ishte absolute (si një referencë me shenja $ për tabelat e zakonshme "jo inteligjente") dhe nuk rrëshqiti në kolonat fqinje kur kopjonim formulën tonë në të djathtë.
Në të ardhmen, kur ndryshoni të dhënat në tabelën burimore dërgesat, duhet të kujtojmë të përditësojmë përmbledhjen tonë ndihmëse me numrat e rreshtave duke klikuar me të djathtën mbi të dhe duke zgjedhur komandën Përditëso & Ruaj (Rifresko).
Metoda 3. Formulat
Kjo metodë nuk kërkon krijimin e një tabele kryesore të ndërmjetme dhe përditësim manual, por përdor "armën e rëndë" të Excel - funksionin SUMMESLIMN (SUMIFS). Në vend që të kërkoni numrat e rreshtave në një përmbledhje, mund t'i llogaritni duke përdorur këtë formulë:
Me një masë të madhe të jashtme, në fakt, ky është një rast përdorimi standard për funksionin e përmbledhjes selektive SUMMESLIMNA që mbledh numrat e rreshtave për qytetin dhe muajin e dhënë. Përsëri, meqenëse nuk kemi disa kontejnerë në të njëjtin qytet në të njëjtin muaj, funksioni ynë në fakt nuk do të japë shumën, por vetë numrin e linjës. Dhe pastaj funksioni tashmë i njohur nga metoda e mëparshme INDEKSI Ju gjithashtu mund të nxirrni kodet e kontejnerëve:
Sigurisht, në këtë rast, nuk duhet të mendoni më për përditësimin e përmbledhjes, por në tabela të mëdha funksioni SUMMESLI mund të jetë dukshëm i ngadalshëm. Pastaj do t'ju duhet të çaktivizoni përditësimin automatik të formulave ose të përdorni metodën e parë - një tabelë kryesore.
Nëse pamja e përmbledhjes nuk është shumë e përshtatshme për raportin tuaj, atëherë mund të nxirrni numrat e rreshtave prej saj në tabelën përfundimtare jo drejtpërdrejt, siç bëmë ne, por duke përdorur funksionin GET.PIVOT.TABELA.TË DHËNAT (GET.PIVOT.DATA). Si ta bëni këtë mund ta gjeni këtu.
- Si të krijoni një raport duke përdorur një tabelë kryesore
- Si të vendosni llogaritjet në tabelat kryesore
- Numërimi selektiv me SUMIFS, COUNTIFS, etj.