Detyrat e testimit me temën: “Fletët llogaritëse Excel ».
1. Një spreadsheet është:
a) një program aplikimi për ruajtjen dhe përpunimin e të dhënave të strukturuar në formën e një tabele;
b) program aplikimi për përpunimin e tabelave të kodeve;
c) pajisje Kompjuter personal, duke menaxhuar burimet e tij gjatë përpunimit të të dhënave në formë tabelare;
d) një program sistemi që menaxhon burimet e një kompjuteri personal kur punon me tabela.
2. Dallimi themelor midis një spreadsheet dhe një të rregullt është:
a) aftësia për të përpunuar të dhëna të strukturuara në formën e një tabele;
b) aftësinë për të rillogaritur automatikisht të dhënat e specifikuara nga formula kur ndryshojnë të dhënat origjinale;
c) aftësia për të paraqitur vizualisht lidhjet ndërmjet të dhënave të përpunuara;
d) aftësia për të përpunuar të dhënat e paraqitura në vargje lloje të ndryshme.
3. Rreshtat e fletëllogaritëse:
a) emërtohen nga përdoruesi në mënyrë arbitrare;
b) përcaktohen me shkronjat e alfabetit rus A...Z;
c) tregohen me shkronja Alfabeti latin;
d) janë të numëruara.
4. Adresa e qelizës në Excel përbëhet nga:
a) emri i skedarit;
b) një grup i caktuar karakteresh;
c) emrin e kolonës dhe numrin e rreshtit në kryqëzimin e së cilës ndodhet qeliza;
d) numrin e rreshtit dhe emrin e kolonës në kryqëzimin e së cilës ndodhet qeliza.
5. Nëse në Excel aktivizoni një qelizë dhe shtypni butonin Delete, atëherë:
a) përmbajtja e qelizës do të fshihet;
b) formati i qelizës do të pastrohet;
c) qeliza do të fshihet;
d) emri i qelizës do të fshihet.
6. Informacioni në tabelë paraqitet si:
a) dosjet; b) regjistrimet; c) teksti, numrat, formulat.
7. Shndërrim i shpejtë i të dhënave në fletëllogaritëse ndodh për shkak të...
a) shpërndarja e informacionit nëpër qeliza;
b) prania e formulave që lidhin të dhënat;
c) funksionimin e shpejtë të procesorit.
8. Një qelizë aktive në Excel është:
a) qeliza me adresë A1; b) qeliza e theksuar me një kornizë; c) qelizën në të cilën futen të dhënat.
9. Gama e qelizave në një spreadsheet është...
a) grupi i të gjitha qelizave të mbushura të tabelës;
b) grupi i të gjithave qeliza boshe;
c) një grup qelizash që formojnë një zonë drejtkëndore;
d) një grup qelizash që formojnë një zonë formë të lirë.
10. Specifikoni një formulë të pavlefshme për qelizën F1
a) =A1+B1*D1; b) =A1+B1/F1; c) =C1.
11. Specifikoni një formulë të pavlefshme për të shkruar në qelizën D1
a) =2A 1+B 2; b) =A 1+B 2+C3; c) =A1-C3; d) të gjitha formulat janë të pranueshme.
12. Ju nuk mund të fshini në një spreadsheet.
a) linjë; b) kolona; c) emri i qelizës; d) përmbajtjen e qelizës.
13. Një grup qelizash theksohet në një tabelëC 3: F 10. Sa qeliza janë në këtë grup?
a) 21; b) 24; c) 28; d) 32.
14. Shprehje , i shkruar në përputhje me rregullat e pranuara në matematikë, në një fletëllogaritëse ka formën:
a)3*(A1+B1)/(5*(2*B1–3*A2));
b)3(A1+B1)/5(2B1–3A2);
c)3* (A1+B1)/ 5* (2* B1–3* A2);
d)3(A1+B1)/(5(2B1–3A2)).
15. Kur lëvizni ose kopjoni në një fletëllogaritëse lidhjet absolute:
a) mos ndryshoni;
b) transformohen pavarësisht nga pozicioni i ri i formulës;
c) transformohen në varësi të pozicionit të ri të formulës;
d) transformohen në varësi të gjatësisë së formulës.
16 . Qeliza e tabelës H5 përmban formulën =$B$5*V5. Çfarë formule do të merret prej tij kur kopjohet në qelizën H7:
a)=$B$7*V7; b)=$B$5*V5; c)=$B$5*V7; d)=B$7*V7.
17. Në tabelë, qeliza A1 përmban numrin 10, B1 përmban formulën =A1/2 dhe C1 përmban formulën =SUM(A1:B1). Cila është vlera e C1:
a) 10; b) 15; në 2; d) 150.
18. Diagrami është:
a) forma e paraqitjes grafike vlerat numerike, gjë që e bën më të lehtë interpretimin e të dhënave numerike;
b) orarin;
c) tavolina e përgatitur;
19. Një grafik me shtylla është:
a) një diagram në të cilin vlerat individuale përfaqësohet nga vija me gjatësi të ndryshme të vendosura përgjatë boshtit X;
b) një diagram, vlerat individuale të të cilit përfaqësohen me pika në një sistem koordinativ kartezian;
c) një diagram në të cilin vlerat individuale përfaqësohen nga shufra vertikale me lartësi të ndryshme;
d) një diagram i paraqitur në formën e një rrethi të ndarë në sektorë dhe në të cilin lejohet vetëm një rresht i të dhënave.
20. Histogrami është më i përshtatshëm për:
a) për të shfaqur shpërndarjet;
b) krahasimet elemente të ndryshme grupe;
c) për të shfaqur dinamikën e ndryshimeve të të dhënave;
d) për të shfaqur raporte specifike shenja të ndryshme.
Përgjigjet:
Përpunuesi i tekstit Word ka disa programe të integruara që implementojnë objekte të ndryshme në dokumentin e krijuar. Ato aksesohen përmes Fut/Objekt.... Le të shohim dy më të njohurit prej tyre: ndërtuesi i grafikut dhe redaktori i formulave.
Ndërtimi i grafikëve
Aplikacioni përdoret për të krijuar diagrame "Grafiku i Microsoft Grafiku".
Zgjidhni qelizat në tabelë, të dhënat e të cilave përdoren për të ndërtuar grafikun. përmbajtja linjë e sipërme dhe kolona e majtë e zonës së zgjedhur përdoret për etiketat për të shënuar shenjat në boshtet e koordinatave.
Përmes menusë Fut/Objekt.../Krijo në listën "Lloji i objektit", zgjidhni "Grafiku i grafikut të Microsoft", pas së cilës do të shfaqet një tabelë me të dhëna dhe një diagram.
Vendosni etiketat në tabelë: etiketat nga rreshti i parë i tabelës që shfaqet përdoren për të shënuar boshtin horizontal, dhe nga kolona e parë - për legjendën. Legjenda është një figurë shpjeguese në të djathtë të diagramit.
Redaktoni diagramin. Për ta bërë këtë, kliko me të djathtën mbi elementin e diagramit që po redaktohet - do të shfaqet një menu për modifikimin e tij.
Dilni Microsoft Graph duke klikuar në dritaren kryesore jashtë grafikut.
Zhvendosni diagramin në vendndodhjen e dëshiruar në dokument dhe rregulloni dimensionet e tij.
Shembull
Le të ndërtojmë një diagram që ilustron dinamikën e të ardhurave (shih tabelën e mësipërme). Për ta bërë këtë, zgjidhni intervalin A2:F5 në tabelë dhe ngarkoni "Microsoft Graph". Ju lutemi vini re se ky varg përfshin një rresht me emrat e muajve dhe një kolonë me emrat e qyteteve. Ato përdoren për etiketat e boshteve horizontale dhe legjendave.
Nëse keni nevojë të redaktoni grafikun, duhet të klikoni me të djathtën brenda grafikut dhe të zgjidhni artikullin e menusë "Opsionet e grafikut". Hapet dritarja e opsioneve të grafikut, duke ju lejuar të bëni shumicën e korrigjimeve. Për shembull, për të vendosur etiketat në një bosht horizontal, duhet të hapni skedën "Akset" dhe të zgjidhni kutinë e kontrollit "Aksi X (Kategoritë)" dhe etiketën "Automatike". Nëse keni nevojë të ndryshoni llojin e grafikut, duhet të zgjidhni artikullin e menysë "Lloji i grafikut".
Për të vendosur orientimin vertikal të etiketave nën boshtin X, kliko me të djathtën në cilindo prej etiketave, zgjidhni menunë "Format Axis" dhe në skedën "Alignment" vendosni orientimin vertikal.
Nëse, me objektin "Microsoft Graph" aktiv, hyni në meny Të dhënat/Rreshtat formojnë kolona, atëherë boshti X do të shfaqë të dhënat nga kolonat e tabelës.
Të gjitha veprimet për modifikimin e një grafiku mund të kryhen përmes shiritit të menusë, i cili zëvendëson menynë kryesore ndërsa Microsoft Graph është duke u ekzekutuar.
Faza e fundit e redaktimit të një diagrami është ndryshimi i madhësisë së tij dhe vendosja e tij në vendin e dëshiruar në faqe.
Puna me Redaktorin e Formulës
Redaktori i formulës "Microsoft Equation" është një program që instalohet gjatë instalimit Redaktori i fjalës me kërkesë të përdoruesit. Redaktori ka një grup të madh simbolet matematikore dhe ju lejon të përshkruani formula mjaft komplekse. Ndryshe nga redaktori TEX, në të cilin formula fillimisht kodohet dhe më pas riprodhohet nga një program i veçantë, "Microsoft Equation" ju lejon të shihni formulën ashtu siç është shkruar. Pas regjistrimit të formulës, dimensionet e saj mund të ndryshohen si një vizatim i rregullt.
Për të shkruar një formulë, duhet të vendosni kursorin në vendin e duhur dhe të shkoni te menyja Insert/Object/Microsoft Equation 3.0. Në këtë rast, duhet të shfaqet një kornizë për futjen e një formule dhe një panel redaktues formulash që përmban dy rreshta butonash. Rreshti i sipërm i butonave përcakton paleta e simboleve, më e ulët - paleta e shablloneve. Ju mund të plotësoni një formulë duke klikuar jashtë fushës së hyrjes.
Rendi i përgjithshëm i një grupi formulash konsiston në zgjedhjen e elementit të kërkuar në panelin e redaktuesit të formulës dhe specifikimin e tij në menynë që shfaqet. Karakteret e rregullta futen nga tastiera në fushat e caktuara. Kur lëvizni nga një fushë në tjetrën dhe kur futni fusha të reja, duhet të monitoroni pozicionin dhe madhësinë e kursorit. Për shembull, kursori për thyesë e zakonshme më i madh se për numëruesin ose emëruesin e tij. Në këtë mënyrë shfaqet hapësira për futjen e karakterit të radhës.
Futja e hapësirave në formula nuk mund të kryhet thjesht duke shtypur tastin përkatës. Ka disa lloje hapësirash që janë të disponueshme në Paletën e Karaktereve. Nëse keni nevojë të futni hapësira shpesh, është e përshtatshme të përdorni kombinimet e tasteve të paraqitura në tabelë.
Rreshtimi i formulës mund të kërkohet, për shembull, pas ndryshimit të madhësisë së saj, pavarësisht nga fakti se në shumicën e rasteve vetë redaktori i formulës e përafron formulën në lidhje me rreshtin në të cilin është shkruar. Për të rreshtuar të gjithë formulën ose një pjesë të saj, duhet të telefononi formulën për redaktim duke klikuar dy herë miun, zgjidhni pjesën që do të rreshtoni dhe shtypni kombinimin e tastit Ctrl dhe një prej tasteve të kursorit numrin e kërkuar herë , në varësi të drejtimit të shtrirjes. Çdo klikim e lëviz pjesën e zgjedhur me 1 piksel.
Pyetje kontrolli
- Si i aksesoni programet e integruara që implementojnë objekte të ndryshme në dokumentin e krijuar?
- Çfarë aplikacioni përdorni për të krijuar diagramet?
- Si të zgjidhni një aplikacion grafiku?
- Si të ndërtoni një diagram?
- Çfarë është një legjendë në një tabelë?
- Si të vendosni etiketat për shenjat dhe legjendat e boshtit horizontal?
- Si të redaktoni një diagram?
- Cilat janë aftësitë e redaktuesit të formulës?
- Si të shkruani një formulë në një dokument?
- Cili është rendi i përgjithshëm i një grupi formulash?
- Si të futni një hapësirë në një formulë?
- Si të përafroni një formulë?
Ky është një kapitull nga libri: Michael Girvin. Ctrl+Shift+Enter. Zotërimi i formulave të grupeve në Excel.
Ky shënim është për ata që janë vërtet të interesuar formula komplekse varg. Nëse ju duhet vetëm të nxirrni një listë me vlera unike një herë, është shumë më e lehtë të përdorni një Filter të Avancuar ose Tabela Pivot. Përparësitë kryesore të përdorimit të formulave janë: përditësim automatik kur ndryshoni/shtoni të dhëna burimore ose kritere përzgjedhjeje. Para se të lexoni, këshillohet të rifreskoni kujtesën tuaj për idetë e përfshira në materialet e mëparshme:
- (Kapitulli 11);
- (Kapitulli 13);
- (Kapitulli 15);
- (Kapitulli 17).
Oriz. 19.1. Merrni të dhënat unike duke përdorur opsionin Filtri i avancuar
Shkarkoni shënimin në ose format, shembuj në format
Marrja e një liste unike nga një kolonë e vetme duke përdorur një opsion Filtri i avancuar
Në Fig. Figura 19.1 tregon grupin e të dhënave (vargu A1:C9). Qëllimi juaj është të merrni një listë të pistave unike të garave. Meqenëse duhet të ruani të dhënat origjinale, nuk mund ta përdorni opsionin Hiqni dublikatat(menu TË DHËNAT –> Punojnë me të dhëna –> Hiqni dublikatat). Por ju mund të përdorni Filtri i avancuar. Për të hapur kutinë e dialogut Filtri i avancuar, kaloni nëpër menu TË DHËNAT –> Renditja dhe filtrimi –> Për më tepër, ose shtypni dhe mbani tastin Alt dhe më pas shtypni S, L (për Excel 2007 ose më vonë).
Në kutinë e dialogut që hapet Filtri i avancuar(Fig. 19.1) vendosni opsionin kopjoni rezultatin në një vend tjetër, kontrolloni kutinë e zgjedhjes Vetëm hyrje unike, specifikoni zonën nga e cila do të nxirren vlerat unike ($B$1:$B$9) dhe qelizën e parë ku do të vendosen të dhënat e nxjerra ($E$1). Në Fig. Figura 19.2 tregon listën unike që rezulton (vargu E1:E6). Nëse nuk e përfshini emrin e fushës Gama origjinale kuti dialogu Filtri i avancuar(në vend që të vendosni $B$2:$B$9 në Figurën 19.1), Excel do ta trajtojë rreshtin e parë të diapazonit si emrin e fushës dhe ju rrezikoni të merrni një dublikatë. Në Fig. Figura 19.3 tregon një nga shumë përdorimet e mundshme të një liste unike.
Marrja e një liste unike bazuar në një kriter duke përdorur opsionin Filtri i avancuar
Në shembullin e fundit, ju morët një listë unike nga një kolonë e vetme. Një filtër i avancuar mund të marrë gjithashtu një grup unik regjistrimesh (d.m.th., rreshta të tëra nga tabela burimore) duke përdorur një kriter. Në Fig. Figurat 19.4 dhe 19.5 tregojnë një situatë në të cilën ju duhet të nxirrni regjistrime unike nga diapazoni A1:D10 për të cilin emri i kompanisë është i barabartë me ABC. Më vonë në këtë kapitull, do të shihni se si ta bëni këtë punë duke përdorur një formulë. Megjithatë, nëse nuk keni nevojë që procesi të jetë automatik, mund ta përdorni Filtri i avancuar, e cila është sigurisht më e thjeshtë se formula.
Oriz. 19.4. Ju nevojiten regjistrime unike për Kompaninë ABC; Për të zmadhuar imazhin, klikoni me të djathtën mbi të dhe zgjidhni Hapni imazhin në skedën e re
Oriz. 19.5. Përdorimi Filtri i avancuar për të tërhequr të dhënat unike bazuar në kritere është shumë më e lehtë sesa metoda e formulës. Megjithatë, të dhënat e marra nuk do të përditësohen automatikisht nëse kriteret ose të dhënat e burimit ndryshojnë
Marrja e një liste unike nga një kolonë e vetme duke përdorur një Tabelë Pivot
Nëse tashmë përdorni PivotTables, atëherë e dini se çdo herë që vendosni ndonjë fushë në zonë Vargjet ose Kolonat(Fig. 19.6), ju do të merrni automatikisht një listë unike. Në Fig. Figura 19.6 tregon se si mund të krijoni shpejt një listë unike të pistave të garës dhe më pas të numëroni numrin e vizitave në secilën pistë. Ndërsa një tabelë kryesore është e dobishme për marrjen e një liste unike nga një kolonë e vetme, nuk do ta gjeni të dobishme për marrjen e të dhënave unike bazuar në kritere.
Oriz. 19.6. Ju mund të përdorni Tabela strumbullar kur keni nevojë për një listë unike dhe llogaritje të mëvonshme bazuar në të
Nxjerrja e një liste unike nga një kolonë e vetme duke përdorur formula dhe një kolonë ndihmëse
Përdorimi i një kolone ndihmëse e bën më të lehtë marrjen e të dhënave unike sesa përdorimin e formulave të grupeve (Figura 19.7). Ky shembull përdor metodat që keni mësuar në (Përdorimi i funksionit COUNTIF) dhe (Përdorimi i një kolone ndihmëse). Nëse tani ndryshoni të dhënat e burimit në intervalin B2:B9, formulat do t'i pasqyrojnë automatikisht ato ndryshime në zonën D15:D21.
Formula e grupit: Marrja e një liste unike nga një kolonë e vetme duke përdorur funksionin SMALL
Për shkak se formulat e grupeve të përdorura në këtë seksion janë mjaft komplekse për t'u kuptuar, krijimi i tyre ndahet në faza: së pari, një fragment që numëron vlera unike (Kapitulli 17); e dyta është nxjerrja e të dhënave të bazuara në kriter (Kapitulli 15). Në Fig. Figura 19.8 tregon formulën për llogaritjen e vlerave unike (meqenëse kjo është një formulë grupi, ajo futet duke shtypur Ctrl+Shift+Enter). Vini re aspektet e mëposhtme të kësaj formule:
- Funksioni FREQUENCY kthen një grup numrash (Figura 19.9): për paraqitjen e parë të një piste garash, kthehet numri i ndodhive të tij në të dhënat origjinale; Për çdo paraqitje të mëpasshme të pistës së garës, zeroja kthehet (shih ). Për shembull, Sumner shfaqet në pozicionin e parë dhe të pestë të grupit. Në pozicionin e parë, funksioni FREQUENCY kthen 2 - numri i përgjithshëm Vera në rangun B2:B9, në pozicionin e pestë - 0.
- Funksioni FREQUENCY vendoset në argument log_shprehje IF funksionon, kështu që funksioni IF kthen TRUE për çdo vlerë jo-null dhe FALSE për një vlerë null.
- Argumenti vlera_nëse_e vërtetë Funksioni IF përmban 1, kështu që funksioni SUM numëron numrin e 1-ve të tilla.
Oriz. 19.8. Funksioni FREQUENCY vendoset në argument log_shprehje funksionet IF
Oriz. 19.9. (1) funksioni FREQUENCY kthen një grup numrash; (2) funksioni IF kthen 1 për numra jo zero dhe FALSE për zero.
Tani le të krijojmë një formulë për nxjerrjen e një liste unike. Në Fig. Figura 19.10 tregon një grup pozicionesh relative të vendosura në një argument varg funksionet VOGLA.
Në shembullin e mëparshëm (Fig. 19.9) në argument vlera_nëse_e vërtetë Funksioni IF vendosi një, kështu që funksioni IF ktheu ato dhe FALSE. Këtu (Fig. 19.10) argumenti vlera_nëse_e vërtetë përmban: RRESHT($B$2:$B$9)-ROW($B$2)+1. Prandaj, funksioni IF (brenda funksionit SMALL) kthen numrin e pozicionit relativ në intervalin me një pistë unike garash, ose FALSE për dublikatat (Figura 19.11).
Oriz. 11.19. Funksioni IF kthen numrin e pozicionit relativ në diapazonin me një pistë unike garash, ose FALSE për dublikatat
Në Fig. 19.12 tregojnë rezultatet e formulës. Në Fig. Figura 19.13 tregon se sapo të dhënat fillestare ndryshuan, formulat i pasqyruan menjëherë këto ndryshime. Por, çka nëse shtoni hyrje të reja? Më pas, do të shihni se si të krijoni formulat e diapazonit dinamik.
Oriz. 19.13. Nëse të dhënat e burimit ndryshojnë, formula përditësohet menjëherë. Filtri dhe filtri i avancuar nuk mund të përditësohen automatikisht pa shkruar kodin VBA
Formula e grupit: Marrja e një liste unike nga një kolonë e vetme duke përdorur diapazonin dinamik
Le të zgjerojmë shembullin e fundit me atë që mësuat rreth formulave duke përdorur emra specifikë bazuar në intervalet dinamike (). Në Fig. 19.14 tregon formulën për përcaktimin e emrit Itinerari. Kjo formulë supozon se nuk do të futni kurrë një hyrje pas rreshtit 51.
Oriz. 19.14. Përkufizimi i emrit Itinerari bazuar në formulë
Pasi të përcaktoni një emër, mund ta përdorni në çdo formulë. Në Fig. Figura 19.15 tregon se si të përdorni një emër për të numëruar numrin e vlerave unike (krahasoni me Figurën 19.8). Dhe në Fig. Figura 19.16 tregon një formulë që nxjerr vetë vlerat unike nga një listë pistash garash. Vini re se në vend të fragmentit varg<>»» (siç ishte në Fig. 19.8 dhe 19.10), përdoret funksioni ITEXT (çdo tekst do të kthejë vlerën TRUE). Kur përdorni ETEXT, nëse futni një numër (si në qelizën B11), ose ndonjë tjetër jo-tekst, formula do ta shpërfillë atë vlerë. Në Fig. Figura 19.17 tregon se formula merr automatikisht çdo emër të ri të këngëve, duke injoruar numrat.
Oriz. 19.16. Nxjerrja emër unik Gjurmët e bazuara në diapazonin dinamik
Krijimi i një formule vlere unike për një listë rënëse
Bazuar në shembullin e sapo diskutuar, le të përcaktojmë emrin e dytë - Lista e rrugëve, bazuar gjithashtu në diapazonin dinamik, por tani duke iu referuar një liste gjurmësh unike (varg E5:E14, Fig. 19.18). Meqenëse diapazoni E5:E14 përmban vetëm tekst dhe vlera boshe (vargjet e testit me gjatësi zero - ""), argumenti kërkim_vlera Funksionet MATCH mund të përdorin shkronja të ngurta *? (që do të thotë nga të paktën, një personazh). Dhe në argument lloji i ndeshjes Funksioni MATCH duhet të përdorë një vlerë prej -1, e cila do të gjejë elementin e fundit të tekstit në një kolonë që përmban të paktën një karakter. Siç tregohet në Fig. 19.18, atëherë mund të përdorni një emër specifik në fushë Burimi dritare Vleresimi i vlerave te futura(Për më shumë informacion mbi krijimin e një liste rënëse, shihni). Lista rënëse mund të zgjerohet dhe tkurret ndërsa të dhënat e reja shtohen ose hiqen në kolonën B.
Nëse shkronjat e egra duhet të trajtohen si karaktere të rregullta
Siç mësuat në , ndonjëherë shkronjat e egra duhet të trajtohen si karaktere. Në Fig. Figura 19.18 tregon se si mund të modifikoni formulat për raste të tilla. Ju vendosni një tildë përpara gamës së argumentit kërkim_vlera Funksioni MATCH dhe shtoj një varg bosh në pjesën e pasme të diapazonit në argument shikuar_array.
Përdorni një formulë kolone ndihmëse ose grup për të tërhequr regjistrime unike bazuar në kritere
Në fillim të shënimit, u tregua se për marrjen e të dhënave unike bazuar në kritere, Filtri i avancuar. Megjithatë, nëse keni nevojë për përditësim të menjëhershëm, mund të përdorni një kolonë ndihmëse (Figura 19.20) ose formula të grupeve (Figura 19.21).
Formula dinamike për nxjerrjen e emrave të klientëve dhe vëllimit të shitjeve
Formulat janë paraqitur në Fig. 19.22. Për shembull, nëse shtoni një hyrje të re TTKamionë në rreshtin 17 , formula SUMIF në qelizën F15 do të shtojë automatikisht vlerën e re. Nëse shtoni një klient të ri në kolonën B, ai do të shfaqet menjëherë në kolonën E dhe formula SUMIF në kolonën F do të tregojë totalin e ri.
Oriz. 19.22. Përdorimi i një emri specifik dhe dy formulave të grupeve për të nxjerrë klientët unikë dhe vëllimin e shitjeve
Vini re se funksioni SUMIF ka një argument diapazoni i shumës përmban një qelizë - 10 $ C$. Ja çfarë thotë referenca e formulës SUMSLI për këtë temë: argument diapazoni i shumës mund të mos ketë të njëjtën madhësi me argumentin varg. Kur përcaktohen qelizat aktuale që do të përmblidhen, qeliza e sipërme majtas e argumentit përdoret si qeliza fillestare diapazoni i shumës, dhe pastaj përmblidhen qelizat e pjesës së diapazonit që i korrespondon madhësisë argumentit varg. Formulat e futura në qelizat E15 dhe F15 kopjohen përgjatë kolonave.
Renditja e vlerave numerike
Formulat për renditjen e numrave janë mjaft të thjeshta, por formulat për renditjen e të dhënave të përziera janë jashtëzakonisht komplekse. Prandaj, nëse nuk keni nevojë për përditësim të menjëhershëm, atëherë është më mirë të bëni pa formula duke përdorur opsionin Renditja. Në Fig. Figura 19.23 tregon dy formula klasifikimi.
Në Fig. Figura 19.24 tregon se si mund të përdorni një kolonë ndihmëse për të renditur numrat. Meqenëse funksioni RANK nuk rendit si numra (duke u dhënë të njëjtën rang), shtohet një funksion COUNTIF për të dalluar ndërmjet tyre. Vini re se funksioni COUNTIF ka një gamë të zgjeruar që fillon një rresht më lart. Kjo është e nevojshme në mënyrë që shfaqja e parë e ndonjë numri të mos japë një kontribut. Shfaqja e dytë e numrit do të rrisë gradën me një. Ky numërim sekuencial përcakton rendin në të cilin funksionet INDEX dhe MATCH marrin rekorde në rangun A8:B12.
Nëse keni mundësi të krijoni një kolonë ndihmëse në zonën e nxjerrjes së të dhënave (vargu A10:A14 në figurën 19.25), është e përshtatshme të përdorni renditjen e numrave të përshkruar më sipër bazuar në funksionin SMALL, dhe bazuar në këtë, nxirrni emrat duke përdorur funksionin e vargut.
Oriz. 19.25. Nëse nuk mund të përdorni një kolonë ndihmëse, përdorni një renditje të bazuar në funksionin SMALL (në qelizën A11) dhe një formulë grupi (në qelizën B11)
Shpesh në biznes dhe sport është e nevojshme të nxirret N vlerat më të mira dhe emrat që lidhen me këto vlera. Filloni zgjidhjen tuaj me formulën COUNTIF (qeliza A11 në figurën 19.26), e cila do të përcaktojë numrin e regjistrimeve që do të shfaqen. Vini re se argumenti kriter në funksionin COUNTIF në qelizën A11 - më shumë ose të barabartë vlera në qelizën D8. Kjo lejon që të shfaqen të gjitha vlerat kufitare (në shembullin tonë, megjithëse kërkohet të shfaqet Top 3, ekzistojnë katër vlera të përshtatshme).
Oriz. 19.26. Nxjerrja e tre pikëve më të mira totale dhe emrat e tyre përkatës. Kur N ndryshon në qelizën D8, zona A15:B21 do të përditësohet
Renditja e vlerave të tekstit
Nëse përdorimi i një kolone ndihmëse është i lejueshëm, detyra nuk është aq e vështirë (Fig. 19.27). Operatorët e krahasimit trajtojnë personazhet e tekstit bazuar në kodet numerike ASCII të caktuara për karakteret. Në qelizën C3, funksioni i parë COUNTIF kthen zero dhe i dyti shton një. Në C4: 2+1, C5: 0+2, C6: 3+1.
Renditni të dhënat e përziera
Formula që ju lejon të nxirrni vlera unike nga të dhënat e përziera dhe më pas t'i renditni ato është shumë e madhe (Figura 19.28). Gjatë krijimit të tij, ne kemi përdorur ide që janë hasur më herët në këtë libër. Le të fillojmë të mësojmë formulën duke parë se si funksionon funksioni standard i renditjes në Excel.
Excel i rendit rezultatet në porosia e radhës: fillimisht numrat, pastaj teksti (përfshirë vargjet me gjatësi zero), FALSE, TRUE, vlerat e gabimit sipas rendit të shfaqjes, qelizat boshe. Të gjitha renditjet ndodhin në përputhje me kodet ASCII. Ka 255 kode ASCII, secili që korrespondon me një numër nga 1 në 255:
Për shembull, numri 5 korrespondon me kodin ASCII 53 dhe karakteri S korrespondon me kodin ASCII 83. Nëse i renditni dy vlerat, 5 dhe S, nga më e vogla tek më e madhja, atëherë 5 do të jetë më e lartë se S sepse 53 është më pak se 83.
Seti i të dhënave në diapazonin A2:A5 (Fig. 29) konvertohet në diapazonin E2:E5 në përputhje me rregullat e renditjes. Për të kuptuar më mirë parimet e renditjes, merrni parasysh vlerat në diapazonin C2:C5. Për shembull, nëse bëni pyetjen "Sa njerëz më kalojnë mua?" në ID në qelizën A2 (54678), përgjigja do të jetë zero, sepse në një listë të renditur, ID 54678 do të jetë e para. SD-987-56 do të ketë tre ID mbi të. Ju duhet një formulë për të marrë vlera në rangun C2:C5.
Për të filluar, zgjidhni diapazonin E1:H1 dhe shkruani =TRANSP(A2:A5) në shiritin e formulave dhe futni formulën duke shtypur Ctrl+Shift+Enter (Fig. 19.30). Më pas, zgjidhni diapazonin E2:H5 në shiritin e formulave, shkruani =A2:A5>E1:H1 dhe futni formulën duke shtypur Ctrl+Shift+Enter (Fig. 19.31). Në Fig. Figura 19.32 tregon rezultatin, i cili është një grup drejtkëndor i vlerave TRUE dhe FALSE që korrespondojnë me secilën prej qelizave në grupin që rezulton, si përgjigje për pyetjen "A është titulli i rreshtit më i madh se kreu i kolonës?"
Oriz. 19.30. Zgjidhni diapazonin E1:H1 dhe futni formulat e grupit
Oriz. 19.31. Në rangun E2:H5, futni formulën e grupit =A2:A5>E1:H1
Oriz. 19.32. Çdo qelizë në diapazonin E2:H5 përmban përgjigjen e pyetjes "A është titulli i rreshtit më i madh se kreu i kolonës?"
Për shembull, në qelizën E3 pyetja është: SD-987-56 > 54678. Meqenëse 54678 është më e vogël se SD-987-56, përgjigjja është e VËRTETË. Vini re se diapazoni E3:H3 përfshin tre vlera TRUE dhe një vlerë FALSE. Duke parë prapa në Fig. 19.29, mund të shihni se është numri tre që është në qelizën C3.
Siç tregohet në figurat 19.33 dhe 19.34, ju mund t'i konvertoni vlerat TRUE dhe FALSE në njësh dhe zero duke shtuar një mohim të dyfishtë në formulën e grupit. Meqenëse grupi origjinal (E2:H5) është 4x4 dhe dëshironi që rezultati të jetë një grup 4x1, përdorni funksionin MULTIPLE (shih Figurën 19.35 dhe ). Funksioni MULTIPLE është një funksion vargu, prandaj futeni duke shtypur Ctrl+Shift+Enter (Fig. 19.36). Tani, në vend që të përdorni diapazonin E2:H5, shtoni elementët e duhur brenda formulës (Figura 19.37).
Oriz. 19.36. Duke zgjedhur diapazonin C2:C5 dhe duke futur funksionin e grupit MULTIPLE, ju merrni një kolonë numrash që ju tregojnë se sa ID në listën e renditur janë më të larta se ajo e zgjedhur.
Oriz. 19.37. Në vend të përdorimit të diapazonit ndihmës E2:H5, elementët përkatës shtohen brenda formulës
Në Fig. Figura 19.38 tregon se si mund të zëvendësoni një grup konstantesh me fragmentin ROW($A$2:$A$5)^0.
Oriz. 19.39. Për t'u marrë me qelizat e mundshme boshe, të gjitha shfaqjet e A2:A5 duhet të plotësohen me një kontroll IF(A2:A5).<>"", A2:A5); funksioni ROW nuk kërkon një shtesë të tillë, sepse funksioni punon me adresën e qelizës, jo me përmbajtjen e saj
Për shkak se formula përfundimtare do të përdoret diku tjetër, ju duhet t'i bëni të gjitha vargjet absolute (Figura 19.40). Në Fig. Figura 19.41 tregon vlerat që rezultojnë.
Oriz. 19.40. Gama A2:A5 e kthyer në absolute
Meqenëse ky element do të përdoret dy herë më vonë, mund ta ruani nën një emër specifik. Siç tregohet në kutinë e dialogut (Fig. 19.42), formulës i jepet emri SZB - Sa vlera janë më të mëdha.
- Argumenti varg Funksioni INDEX i referohet gamës së burimit A2:A5.
- Funksioni i parë MATCH do t'i tregojë funksionit INDEX pozicionin relativ të elementit në grupin A2:A5.
- Ndërsa argumenti kërkim_vlera Funksioni MATCH është lënë bosh.
- Emri i përcaktuar (DSN) në argument shikuar_array do t'ju lejojë të përdorni fillimisht një element që ka një vlerë 0, pastaj 2 dhe në fund 3.
- Zero në argumentim lloji i ndeshjes specifikon një përputhje të saktë, e cila do të eliminojë nevojën për t'iu referuar dublikatave.
Oriz. 19.43. Ju filloni një formulë për të nxjerrë dhe renditur të dhënat në qelizën A11. Argumenti kërkim_vlera Funksionet MATCH mbeten bosh për momentin
Para se të krijoni një argument kërkim_vlera Funksionet SEARCH, mbani mend atë që ju nevojitet në të vërtetë. Ka tre ID unike që duhet të renditen, kështu që do t'ju duhen tre numra në argument kërkim_vlera pasi formula është kopjuar. Këta numra do t'ju lejojnë të gjeni pozicionin relativ në grupin A2:A5, që është ajo që duhet t'i jepni funksionit INDEX:
- Në qelizën A11, funksioni MATCH do të kthejë 0, që korrespondon me pozicionin relativ të 1 brenda emrit të përcaktuar SZB.
- Kur formula kopjohet në qelizën A12, funksioni MATCH duhet të kthejë numrin 2 dhe pozicionin relativ = 4 brenda MSB.
- Në qelizën A13, funksioni MATCH duhet të kthejë 3 dhe pozicionin relativ = 2 brenda MSB.
Një fotografi shfaqet kur mendoni për argumentin kërkim_vlera kur kopjoni formulën poshtë, kërkesa duhet të përputhet: "Jepni vlerën minimale brenda një emri specifik SZB që nuk është përdorur ende". Siç tregohet në Fig. 19.44 elementi i formulës MIN(IF(FUND(MATCH($A$2:$A$5,A$10:A10,0)),MSB)) kthen vlerën minimale kur kopjon formulën poshtë, duke iu përgjigjur saktësisht pyetjes. Arsyeja pse kjo funksionon është se fragmenti UND(MATCH($A$2:$A$5,A$10:A10,0)) krahason dy lista (shih ). Vini re shtrirjen e gamës A$10:A10 në argument shikuar_array. Në qelizën A11, kombinimi i UNM dhe MATCH ndihmon në nxjerrjen e të gjithë numrave unikë nga SZB dhe sigurimin e tyre në funksionin MIN. Kur kopjoni formulën në qelizën A12, ID-ja që u mor në qelizën A11 është përsëri e pranishme në intervalin e zgjeruar dhe do të gjendet përsëri në rangun $A$2:$A$5. Megjithatë, UND kthen FALSE dhe vlera 0 nuk do të nxirret nga SZB Për ta parë këtë, futni formulën e grupit në Figurën 19.44 duke shtypur Ctrl+Shift+Enter dhe kopjoni atë.
Oriz. 19.44. Elementi i formulës në argument kërkim_vlera Funksioni MATCH përputhet me kërkesën: "Jepni vlerën minimale brenda një emri specifik SZB që nuk është përdorur ende"
Në Fig. 19.45 tregon se në argument shikuar_array Funksioni i dytë MATCH zgjeroi diapazonin A$10:A10 në A$10:A11. Për të kuptuar se si funksionon kjo formulë, zgjidhni fragmentet e saj në mënyrë sekuenciale dhe klikoni në F9 (Fig. 19.46–19.49).
Oriz. 19.45. Gama e zgjeruar A$10:A11 tani (në qelizën A12) përfshin ID-në e parë (54678)
Oriz. 19.46. Kombinimi i funksionit UNM dhe i dytë MATCH siguron një grup vlerash Boolean; dy vlera FALSE përjashtojnë vlerat null nga emri i përcaktuar SZB
Oriz. 19.47. Zerot eliminohen dhe mbeten vetëm numrat 3 dhe 2; numri 2 është minimumi, kështu që duhet të nxirret më pas
Oriz. 19.48. Funksioni MIN zgjedh numrin 2; funksioni MATCH tani mund të gjejë pozicionin e saktë relativ për funksionin INDEX
Oriz. 19.49. Funksioni INDEX do të marrë vlerën 2, e cila korrespondon me pozicionin relativ të katërt të ID-së në rangun A2:A5
Tani, duke u kthyer në qelizën A11, mund të shtoni një kusht tjetër në mënyrë që qelizat boshe të mos ndikojnë në formulën (Figura 19.50).
Oriz. 19.50. Ekzistojnë dy kushte brenda funksionit MIN; e para: "a janë bosh qelizat?", së dyti: "A nuk është përdorur ende vlera?"
Në Fig. 19.51 tregon formulën përfundimtare. Ai shtoi një kusht për të siguruar që rreshtat në diapazonin A11:A15 të mbeten bosh pas nxjerrjes së vlerave unike të renditura. Në Fig. Figura 19.52 tregon se çfarë ndodh nëse qeliza A3 lihet bosh. Shtesa jonë për të kontrolluar qelizat boshe funksionoi.
Nuk ishte e lehtë. Por, nëse e keni lexuar deri këtu, shpresoj t'ju pëlqejë.