Okuqukethwe
Sinamathebula amabili (isibonelo, izinguqulo ezindala nezintsha zohlu lwamanani), okudingeka siwaqhathanise futhi sithole ngokushesha umehluko:
Kuyacaca ngokushesha ukuthi kukhona okungeziwe ohlwini lwamanani amasha (izinsuku, ugalikhi ...), kukhona okunyamalele (amajikijolo amnyama, ama-raspberries ...), amanani ashintshile kwezinye izimpahla (amakhiwane, amakhabe ...). Udinga ukuthola ngokushesha futhi ubonise zonke lezi zinguquko.
Kunoma yimuphi umsebenzi ku-Excel, cishe njalo kunesixazululo esingaphezu kwesisodwa (imvamisa 4-5). Ngenkinga yethu, izindlela eziningi ezahlukene zingasetshenziswa:
- umsebenzi VPR (VLOOKUP) — bheka amagama emikhiqizo ohlwini olusha lwamanani komdala bese uveza intengo endala eduze kwentsha, bese ubamba umehluko
- hlanganisa izinhlu ezimbili zibe olulodwa bese wakhe ithebula le-pivot elisuselwe kulo, lapho umehluko uzobonakala ngokucacile
- sebenzisa Isengezo Sombuzo Wamandla ku-Excel
Masizithathe zonke zilandelane.
Indlela 1. Ukuqhathanisa amathebula nomsebenzi we-VLOOKUP
Uma ungasazi nhlobo lesi sici esihle, qale ubheke lapha futhi ufunde noma ubuke isifundo sevidiyo kuso - zisindise iminyaka embalwa yokuphila.
Ngokuvamile, lo msebenzi usetshenziselwa ukudonsa idatha isuka kwelinye ithebula iye kwelinye ngokufanisa ipharamitha ethile evamile. Kulokhu, sizoyisebenzisela ukucindezela izintengo ezindala zibe yintengo entsha:
Leyo mikhiqizo, okuvele kuyo iphutha elithi #N/A, ayikho ohlwini oludala, okungukuthi yengeziwe. Izinguquko zentengo nazo zibonakala ngokucacile.
buhle le ndlela: elula futhi ecacile, "i-classic yohlobo", njengoba besho. Isebenza kunoma iyiphi inguqulo ye-Excel.
bawo ikhona futhi. Ukuze useshele imikhiqizo engezwe ohlwini lwamanani amasha, kuzodingeka wenze inqubo efanayo ngakolunye uhlangothi, okungukuthi ukhuphule amanani entengo entengo endala ngosizo lwe-VLOOKUP. Uma osayizi bamathebula beshintsha kusasa, khona-ke amafomula kuzodingeka alungiswe. Yebo, futhi ematafuleni amakhulu ngempela (> imigqa eyizinkulungwane eziyi-100), yonke le njabulo izokwehla ngokufanele.
Indlela yesi-2: Ukuqhathanisa amathebula usebenzisa i-pivot
Ake sikopishe amatafula ethu elinye ngaphansi kwelinye, sengeze ikholomu enegama lohlu lwamanani, ukuze kamuva uqonde ukuthi yiluphi uhlu ukuthi yiluphi umugqa:
Manje, ngokusekelwe kuthebula elidaliwe, sizodala isifinyezo ngokusebenzisa Faka – PivotTable (Faka — Ithebula Le-Pivot). Asijikijele insimu Product endaweni yemigqa, inkambu Inani endaweni yekholomu kanye nenkambu Цabanye ebangeni:
Njengoba ubona, ithebula le-pivot lizokhiqiza ngokuzenzakalelayo uhlu olujwayelekile lwayo yonke imikhiqizo kusukela kuhlu lwamanani amadala namasha (akukho ukuphindaphinda!) futhi lihlele imikhiqizo ngama-alfabhethi. Ungabona ngokucacile imikhiqizo eyengeziwe (ayinayo intengo endala), imikhiqizo esusiwe (ayinayo intengo entsha) kanye nezinguquko zentengo, uma zikhona.
Amathothali amakhulu kuthebula elinjalo awawenzi umqondo, futhi angakhutshazwa kuthebhu Umakhi - Amanani amakhulu - Khubaza imigqa namakholomu (Idizayini — Amanani Aphelele).
Uma amanani eshintsha (kodwa hhayi inani lezimpahla!), kwanele ukumane ubuyekeze isifinyezo esidaliwe ngokuchofoza kwesokudla kuso - Vuselela.
buhle: Le ndlela iwuhlelo lobukhulu ngokushesha ngamathebula amakhulu kune-VLOOKUP.
bawo: udinga ukukopisha mathupha idatha ngaphansi komunye nomunye bese wengeza ikholomu enegama lohlu lwamanani. Uma amasayizi amatafula eshintsha, kufanele wenze yonke into futhi.
Indlela yesi-3: Ukuqhathanisa amathebula Nombuzo Wamandla
I-Power Query isengezo samahhala se-Microsoft Excel esikuvumela ukuthi ulayishe idatha ku-Excel kusuka cishe kunoma yimuphi umthombo bese uguqula le datha nganoma iyiphi indlela oyifunayo. Ku-Excel 2016, lesi sengezo sesivele sakhelwe ngaphakathi ngokuzenzakalelayo kuthebhu Idatha (Idatha), kanye ne-Excel 2010-2013 udinga ukuyilanda ngokwehlukana newebhusayithi ye-Microsoft futhi uyifake - thola ithebhu entsha Umbuzo Wamandla.
Ngaphambi kokulayisha izinhlu zamanani ethu kokuthi Umbuzo Wamandla, kufanele ziqale ziguqulwe zibe amathebula ahlakaniphile. Ukuze wenze lokhu, khetha ububanzi obunedatha bese ucindezela inhlanganisela kukhibhodi Ctrl+T noma khetha ithebhu kuribhoni Ikhaya – Fometha njengetafula (Ekhaya — Fometha Njengethebula). Amagama amathebula adaliwe angalungiswa kuthebhu umakhi (Ngizoshiya izinga Ithebula 1 и Ithebula 2, ezitholwa ngokuzenzakalelayo).
Layisha intengo endala kokuthi Umbuzo Wamandla usebenzisa inkinobho Kusukela kuThebula/Ububanzi (Kusuka kuThebula/Ububanzi) kusuka kuthebhu Idatha (Usuku) noma kusukela kuthebhu Umbuzo Wamandla (kuya ngenguqulo ye-Excel). Ngemva kokulayisha, sizobuyela emuva ku-Excel sisuka kumbuzo wamandla ngomyalo Vala futhi ulayishe - Vala futhi ulayishe... (Vala & Layisha — Vala & Layisha Ku...):
… futhi efasiteleni elivelayo bese ukhetha Vele udale uxhumano (Ukuxhumana Kuphela).
Phinda okufanayo ngohlu lwamanani amasha.
Manje ake sakhe umbuzo wesithathu ozohlanganisa futhi uqhathanise idatha kusuka kokubili kwangaphambilini. Ukuze wenze lokhu, khetha ku-Excel kuthebhu Idatha - Thola Idatha - Hlanganisa Izicelo - Hlanganisa (Idatha - Thola Idatha - Hlanganisa Imibuzo - Hlanganisa) noma cindezela inkinobho Hlanganisa (Hlanganisa) ithebhu Umbuzo Wamandla.
Efasiteleni lokujoyina, khetha amatafula ethu ohlwini lokudonsela phansi, khetha amakholomu anamagama ezimpahla kuwo, futhi ngezansi, setha indlela yokujoyina - Qedela okwangaphandle (Ingaphandle Egcwele):
Ngemuva kokuchofoza OK kufanele kuvele ithebula lamakholomu amathathu, lapho kukholamu yesithathu udinga ukwandisa okuqukethwe kwamathebula abekwe esidlekeni usebenzisa imicibisholo ekabili enhlokweni:
Njengomphumela, sithola ukuhlanganiswa kwedatha kuwo womabili amathebula:
Kungcono, vele, ukuqamba kabusha amagama ekholomu kunhlokweni ngokuchofoza kabili kulawo aqondakala kakhudlwana:
Futhi manje okuthakazelisa kakhulu. Iya kuthebhu Faka ikholamu (Engeza Ikholomu) bese uchofoza inkinobho Ikholomu enemibandela (Ikholomu enemibandela). Bese kuthi ewindini elivulayo, faka izimo ezimbalwa zokuhlola ngamavelu azo okukhiphayo ahambisanayo:
Kusele ukuchofoza OK bese ulayisha umbiko owumphumela ku-Excel usebenzisa inkinobho efanayo vala futhi ulande (Vala futhi ulayishe) ithebhu Ikhaya (Ikhaya):
Ubuhle.
Ngaphezu kwalokho, uma noma yiziphi izinguquko zenzeka ohlwini lwamanani esikhathini esizayo (imigqa iyengezwa noma isusiwe, amanani ayashintsha, njll.), khona-ke kuyokwanela nje ukubuyekeza izicelo zethu ngesinqamuleli sekhibhodi. Ctrl+Alt+F5 noma ngenkinobho Vuselela konke (Vuselela Konke) ithebhu Idatha (Usuku).
buhle: Mhlawumbe indlela enhle futhi elula kunazo zonke. Isebenza ngobuhlakani ngamatafula amakhulu. Ayidingi ukuhlela mathupha lapho ushintsha usayizi wamathebula.
bawo: Idinga isengezo Sombuzo Wamandla (ku-Excel 2010-2013) noma i-Excel 2016 ukuthi ifakwe. Amagama ekholomu kudatha yomthombo akufanele ashintshwe, ngaphandle kwalokho sizothola iphutha "Ikholomu ethi nokuthi ayitholakalanga!" lapho uzama ukubuyekeza umbuzo.
- Uyiqoqa kanjani idatha kuwo wonke amafayela e-Excel kufolda enikeziwe usebenzisa Umbuzo Wamandla
- Ungakuthola kanjani ukufana phakathi kwezinhlu ezimbili ku-Excel
- Ukuhlanganisa izinhlu ezimbili ngaphandle kwezimpinda