Ungawathola kanjani futhi uwasuse kanjani izimpinda ku-Excel. Izindlela ezi-5 zokuthola nokukhipha izimpinda ku-Excel

Akuyona imfihlo ukuthi ku-Excel ngokuvamile kufanele usebenze ngamatafula amakhulu aqukethe inani elikhulu lolwazi. Ngesikhathi esifanayo, umthamo onjalo wolwazi ngesikhathi sokucubungula ungabangela ukwehluleka noma izibalo ezingalungile uma usebenzisa amafomula ahlukahlukene noma ukuhlunga. Lokhu kuzwakala ikakhulukazi uma kufanele usebenze ngolwazi lwezezimali.

Ngakho-ke, ukuze senze umsebenzi ube lula ngolwazi olunjalo futhi sisuse ukuthi kungenzeka kube namaphutha, sizohlaziya ngqo ukuthi singasebenza kanjani ngemigqa ku-Excel futhi siyisebenzise ukususa izimpinda. Kungase kuzwakale kuyinkimbinkimbi, kodwa ukukuqonda kulula kakhulu, ikakhulukazi uma kunezindlela eziningi ezifika ezinhlanu zokusebenza ngokuthola nokukhipha izimpinda eziseduze.

Indlela 1: Khipha Mathupha Imigqa Eyimpinda

Isinyathelo sokuqala siwukucabangela ukusebenzisa indlela elula yokubhekana nezimpinda. Lena indlela eyenziwa ngesandla, ebandakanya ukusebenzisa ithebhu "Idatha":

1. Okokuqala udinga ukukhetha wonke amaseli wetafula: cindezela i-LMB bese ukhetha yonke indawo yamaseli.
2. Phezulu kwebha yamathuluzi, udinga ukukhetha isigaba "Idatha" ukuze ufinyelele wonke amathuluzi adingekayo.
3. Sicabangela ngokucophelela izithonjana ezitholakalayo bese sikhetha leyo enamakholomu amabili amaseli apendwe ngemibala ehlukene. Uma uhambisa phezulu kwalesi sithonjana, igama elithi "Susa Izimpinda" lizoboniswa.
4. Ukuze usebenzise ngempumelelo yonke imingcele yalesi sigaba, kwanele ukuqaphela futhi uthathe isikhathi sakho nezilungiselelo. Isibonelo, uma ithebula linesihloko esithi "Header", qiniseka ukuthi unaka into ethi "Idatha yami iqukethe izihloko", kufanele ihlolwe.

5. Okulandelayo kuza iwindi elibonisa ulwazi ngekholomu. Udinga ukukhetha amakholomu ofuna ukuwahlolela izimpinda. Kungcono ukukhetha konke ukuze unciphise ukuthatha okweqiwe.

6. Uma yonke into isilungile, hlola ulwazi olumakiwe futhi bese uchofoza okuthi KULUNGILE.
7. I-Excel izohlaziya ngokuzenzakalelayo amaseli akhethiwe futhi isuse zonke izinketho ezifanayo.
8. Ngemva kokuhlola okugcwele nokususwa kwezimpinda etafuleni, kuzovela iwindi ohlelweni lapho kuzoba nomlayezo wokuthi inqubo isiphelile futhi ulwazi luzokhonjiswa ukuthi mingaki imigqa efanayo esusiwe.

Kufanele uchofoze ku-"OK" futhi ungacabanga ukuthi konke sekumi ngomumo. Yenza ngokucophelela isenzo ngasinye, futhi umphumela ngokuqinisekile ngeke ukudumaze.

Indlela yesi-2: Ukususa Ukuphindaphinda Ngokusebenzisa Ithebula Elihlakaniphile

Manje ake sibhekisise enye indlela ewusizo yokukhipha izimpinda, esekelwe ekusetshenzisweni “kwetafula elihlakaniphile”. Kwanele ukulandela lezi zincomo:

1. Okokuqala, khetha ithebula lonke ofuna ukusebenzisa kulo i-algorithm yokucubungula ulwazi oluzenzakalelayo.

2. Manje sebenzisa ibha yamathuluzi, lapho udinga ukukhetha ingxenye ethi “Ekhaya”, bese uthola okuthi “Fometha Njengethebula”. Lesi sithonjana ngokuvamile sitholakala esigatshaneni esithi "Izitayela". Kusele ukusebenzisa umcibisholo okhethekile oya phansi eduze kwesithonjana bese ukhetha isitayela somklamo wetafula owuthande kakhulu.

3. Uma yonke into yenziwe ngendlela efanele, umlayezo owengeziwe mayelana nokufometha ithebula uzovela. Icacisa ububanzi okuzosetshenziswa umsebenzi we-Smart Table. Futhi uma ukhethe ngaphambilini amaseli adingekayo, khona-ke uhla luzokhonjiswa ngokuzenzakalelayo futhi kuzodingeka uluhlole kuphela.

4. Kusele kuphela ukuqala ukusesha nokuqhubeka nokususa imigqa eyimpinda. Ukuze wenze lokhu, udinga ukuthatha izinyathelo ezengeziwe:
   • beka ikhesa esitokisini setafula esingenasizathu;
   • kubha yamathuluzi ephezulu, khetha ingxenye ethi “Idizayini Yethebula”;
   • sifuna isithonjana esisesimweni samakholomu amabili amaseli anombala ohlukile, uma uhamba phezu kwawo, okubhalwe ukuthi "Susa okuphindwe kabili" kuzovezwa;
   • landela izinyathelo esizishilo endleleni yokuqala ngemva kokusebenzisa isithonjana esinikeziwe.

Nakani! Le ndlela inempahla eyingqayizivele - ngenxa yayo, kuzokwazi ukusebenza namatafula ezinhlobonhlobo ezahlukene ngaphandle kwemingcele. Noma iyiphi indawo ekhethiwe ngenkathi isebenza ne-Excel izohlaziywa ngokucophelela ukuze kutholakale izimpinda.

Indlela yesi-3: Ukusebenzisa Isihlungi

Manje ake sinake indlela ekhethekile evumela ukuthi ungasusi izimpinda etafuleni, kodwa umane uzifihle. Eqinisweni, le ndlela ikuvumela ukuthi ufomethe itafula ngendlela yokuthi akukho lutho oluphazamisa umsebenzi wakho oqhubekayo netafula futhi kungenzeka ukuthi ubukeke uthole ulwazi olufanele noluwusizo kuphela. Ukuze uyisebenzise, ​​udinga nje ukulandela lezi zinyathelo:

1. Isinyathelo sokuqala siwukukhetha lonke ithebula ozokhohlisa kulo ukuze ususe izimpinda.
2. Manje yiya engxenyeni ethi "Idatha" bese uye ngokushesha esigatshaneni esithi "Hlunga".

3. Uphawu olucacile lokuthi isihlungi senziwe sasebenza ukuba khona kwemicibisholo ekhethekile enhlokweni yetafula, ngemva kwalokho kuyokwanela ukuthi uyisebenzise futhi ubonise ulwazi oluphathelene nezimpinda (ngokwesibonelo, igama noma igama ekusesheni) .

Ngakho-ke, ungakwazi ukuhlunga ngokushesha zonke izimpinda futhi wenze ukukhohlisa okwengeziwe ngazo.

Isihlungi esithuthukisiwe sokuthola izimpinda ku-Excel

Kukhona enye indlela eyengeziwe yokusebenzisa izihlungi ku-Excel, kulokhu uzodinga:

1. Yenza zonke izinyathelo zendlela yangaphambilini.
2. Efasiteleni lekhithi yamathuluzi, sebenzisa isithonjana esithi "Okuthuthukisiwe", esitholakala eduze kwesihlungi esifanayo.

3. Ngemva kokusebenzisa lesi sithonjana, udinga nje ukunaka iwindi lezilungiselelo ezithuthukisiwe. Le khithi yamathuluzi ethuthukisiwe izokuvumela ukuthi ujwayelane nolwazi lokuqala:
   • okokuqala, kufanele uhlole ububanzi obushiwo betafula ukuze bufane nalokho okuphawulile;
   • qiniseka ukuthi uqoka ibhokisi elithi “Amarekhodi ayingqayizivele kuphela”;
   • uma konke sekumi ngomumo, kusale kuphela ukuchofoza inkinobho ethi "KULUNGILE".

4. Uma zonke izincomo sezihlangabeziwe, okumele ukwenze nje ukubheka ithebula bese uqinisekisa ukuthi izimpinda azisaboniswa. Lokhu kuzobonakala ngokushesha uma ubheka imininingwane ngezansi kwesokunxele, ebonisa inani lemigqa eboniswa esikrinini.

Kubalulekile! Uma udinga ukubuyisela yonke into efomini layo lokuqala, khona-ke kulula ngangokunokwenzeka ukwenza lokhu. Kwanele nje ukukhansela isihlungi ngokwenza izenzo ezifanayo ezikhonjiswe emyalezweni wendlela.

Indlela 4: Ukufomatha okunemibandela

Ukufometha okunemibandela kuyikhithi yamathuluzi ekhethekile esetshenziswa ekuxazululeni izinkinga eziningi. Ungasebenzisa leli thuluzi ukuze uthole futhi ususe izimpinda kuthebula. Ukuze wenze lokhu, uzodinga ukwenza lokhu okulandelayo:

1. Njengangaphambili, uzodinga kuqala ukukhetha amaseli ethebula ohlele ukufometha.
2. Manje kufanele uye kuthebhu "Ekhaya" futhi uthole isithonjana esikhethekile "Ukufometha Okunemibandela", esitholakala esigatshaneni esithi "Izitayela".

3. Ngemva kokuqeda izinyathelo, uzoba nokufinyelela efasiteleni elibizwa ngokuthi "Imithetho Yokukhetha Amaseli", bese udinga ukukhetha into ethi "Izimpinda Zamanani".

4. Qiniseka ukuthi unaka izilungiselelo zokufometha, kufanele zishiywe zingashintshiwe. Okuwukuphela kwento engashintshwa ukufaka ikhodi kombala ngokuvumelana nezintandokazi zakho. Uma konke sekumi ngomumo, ungachofoza okuthi “KULUNGILE”.

5. Ngenxa yezenzo ezinjalo, ungakwazi ukugqamisa zonke izimpinda ngombala ohlukile futhi uqale ukusebenza nazo esikhathini esizayo.

Qaphela! Ububi obuyinhloko bale ndlela ukuthi uma usebenzisa umsebenzi onjalo, wonke amanani afanayo amakwa, hhayi lezo zinketho kuphela lapho intambo yonke ifanelana khona. Kuyafaneleka ukukhumbula le nuance ukuze ugweme izinkinga ngombono obonakalayo futhi uqonde kahle ukuthi kufanele wenze kanjani nokuthi yini okufanele uyinake.

Indlela yesi-5: Ifomula Yokukhipha Imigqa Eyimpinda

Le ndlela inzima kunazo zonke ezisohlwini, njengoba ihloselwe kuphela labo basebenzisi abaqonda imisebenzi nezici zalolu hlelo. Phela, le ndlela ihilela ukusetshenziswa kwefomula eyinkimbinkimbi. Kubukeka kanjena: =ЕСЛИОШИБКА(ИНДЕКС(адрес_столбца;ПОИСКПОЗ(0;СЧЁТЕСЛИ(адрес_шапки_столбца_дубликатов:адрес_шапки_столбца_дубликатов(абсолютный);адрес_столбца;)+ЕСЛИ(СЧЁТЕСЛИ(адрес_столбца;адрес_столбца;)>1;0;1);0));»»). Manje udinga ukunquma ukuthi uzoyisebenzisa kanjani ngqo nokuthi ungayisebenzisa kuphi:

1. Isinyathelo sokuqala siwukwengeza ikholomu entsha ezonikezelwa kuphela kuzimpinda.

2. Выделите верхнюю ячейку and введите в нее формулу: =ЕСЛИОШИБКА(ИНДЕКС(A2:A90;ПОИСКПОЗ(0;СЧЁТЕСЛИ(E1:$E$1;A2:A90)+ЕСЛИ(СЧЁТЕСЛИ(A2:A90;А2:А90)>1;0;1);0));»»).
3. Manje khetha ikholomu yonke yezimpinda ngaphandle kokuthinta unhlokweni.

• Beka ikhesa ekupheleni kwefomula, vele uqaphele le nto, njengoba ifomula ayibonakali ngokucacile esitokisini, kungcono ukusebenzisa ibha yokusesha ephezulu futhi ubheke ngokucophelela indawo yekhesa efanele.
• Ngemva kokusetha ikhesa, kufanele ucindezele inkinobho F2 kukhibhodi.
• Ngemuva kwalokho, udinga ukucindezela inhlanganisela yokhiye "Ctrl + Shift + Enter".

4. Ngenxa yezenzo ezenziwe, kuzokwazi ukugcwalisa ifomula ngendlela efanele ngolwazi oludingekayo oluvela etafuleni.

Ukuthola okufanayo nomyalo othi Thola

Manje kufanelekile ukucabangela enye inketho ethokozisayo yokuthola izimpinda. Ikakhulukazi ngendlela enjalo, uzodinga enye ifomula ebukeka kanje: =COUNTIF(A:A, A2)>1.

Ulwazi Olwengeziwe! Kule fomula, u-A2 usho uphawu lweseli lokuqala endaweni ohlele ukusesha kuyo. Lapho nje ifomula ifakwa kuseli lokuqala, ungahudula inani futhi uthole ulwazi oludingayo. Ngenxa yezenzo ezinjalo, kuzokwazi ukusabalalisa ulwazi ku-"TRUE" nethi "FALSE". Futhi uma udinga ukusesha endaweni elinganiselwe, bese umaka ububanzi bokusesha futhi uqiniseke ukuthi uvikela lezi zimpawu ngophawu lwe-$, oluzoqinisekisa ukuzibophezela futhi lukwenze kube isisekelo.

Uma ungagculisekile ngolwazi olungolwethi “TRUE” noma “FALSE”, lapho-ke siphakamisa ukuthi usebenzise ifomula elandelayo, ehlanganisa ulwazi: =IF(COUNTIF($A$2:$A$17, A2)>1;“Impinda”;“Ingqayizivele”). Ukwenziwa okufanele kwazo zonke izenzo kuzokuvumela ukuthi uthole zonke izenzo ezidingekayo futhi ubhekane ngokushesha nolwazi olukhona oluyimpinda.

Isetshenziswa kanjani ithebula le-pivot ukuze uthole izimpinda

Indlela eyengeziwe yokusebenzisa imisebenzi ye-Excel ukuthola izimpinda yi-PivotTable. Yiqiniso, ukuze uyisebenzise, ​​usadinga ukuqonda okuyisisekelo kwayo yonke imisebenzi yohlelo. Ngokuqondene nezenzo eziyinhloko, zibukeka kanjena:

1. Isinyathelo sokuqala siwukwenza isakhiwo setafula.
2. Kufanele usebenzise inkambu efanayo njengolwazi lweyunithi yezinhlamvu namanani.
3. Amagama afanayo akhethiwe azoba yisisekelo sokubala okuzenzakalelayo kwezimpinda. Ungakhohlwa ukuthi isisekelo somsebenzi wokubala umyalo othi "COUNT". Ukuze uthole ukuqonda okwengeziwe, khumbula ukuthi wonke amanani adlula inani lika-1 azoba yimpinda.

Naka isithombe-skrini, esibonisa isibonelo sendlela enjalo.

Iphuzu eliyinhloko elihlukanisayo lale ndlela ukungabi khona kwanoma yimaphi amafomula. Ingamukelwa ngokuphepha, kodwa okokuqala kufanele ufunde izici nama-nuances okusebenzisa ithebula le-pivot.

Isiphetho

Manje usunalo lonke ulwazi oludingekayo mayelana nezindlela zokusebenzisa ukusesha nokususwa kwezimpinda, futhi unezincomo namathiphu azokusiza ukuxazulula inkinga ngokushesha.