Ukuqoqa nokukhipha idatha ku-Excel. Isinyathelo ngesinyathelo imiyalelo ngesithombe

Manje yisikhathi solwazi. Inani ledatha abantu okufanele balicubungule nsuku zonke likhula kakhulu. Lokhu kusebenza kuzo zonke izici zokuphila okungenzeka, kuhlanganise nomsebenzi. Manje sekunezindawo eziningi kakhulu zomsebenzi womuntu lapho kunesidingo esikhulu sokukwazi ukucubungula inani elikhulu lolwazi ngokushesha.

Esinye sezici ze-Excel ezikuvumela ukuthi wenze lokhu ukuhlanganisa. Iqiniso liwukuthi ulwazi lokuhlela lusiza ukusebenza hhayi ngayo yonke idatha etholakalayo, kodwa kuphela ngezingxenye ezincane. Uma upakisha uhlobo olufanayo lolwazi kubhulokhi eyodwa, kuba lula kukhompyutha nakumuntu ngokwakhe. Cishe ayikho indawo lapho ukwakheka kolwazi bekungeke kudingeke khona:

1. Ukucubungula idatha yokuthengisa. Ama-warehouse ahlala ethola amaqoqo amakhulu ezinhlobonhlobo zezimpahla ezinezindleko ezihlukene, isisindo, umphakeli, igama, nokunye. Ukuhlela idatha kwenza kube lula ukuzulazula kulo lonke lolu chungechunge lolwazi.
2. Izifundo. Izinga lemfundo kanye nokuzifundisa kuhlobene eduze nokuthi ulwazi lwakhiwe kahle kangakanani. Ngakho-ke, uma uqoqa kahle idatha yohlobo olufanayo ohlangothini ngalunye, kuyoba lula ukwenza imisebenzi engokoqobo ehlobene nezibalo, isibonelo, kodwa futhi nemisebenzi yetiyetha, ukuhlela umsebenzi wesikole, njalonjalo.
3. Umbiko Wezezimali. Ama-accountant njalo kufanele abhekane nezinombolo, ngayinye enokuxhumana nezinye izinombolo. Futhi ukwenza kube lula ukusebenza ngenani elikhulu lamanani uXNUMXbuXNUMXahlobene elinye nelinye kanye nolwazi lohlobo oluhlukile, kulula kakhulu ukusebenzisa ukuqoqwa kwedatha.

Futhi, umsebenzi wokuqoqa idatha ikuvumela ukuthi ufihle ulwazi oluphelelwe yisikhathi. Ake sibone ukuthi isebenza kanjani nokuthi ungayisebenzisa kanjani.

Ungawamisa kanjani amapharamitha wokusebenza

Ukuze usebenze ngokuqoqa idatha, kufanele uqale ukulungiselele. Ukuze wenze lokhu, yiya kuthebhu ethi “Idatha” bese uthola inketho ethi “Isakhiwo” lapho. Okulandelayo, kuzovela iphaneli ye-pop-up lapho udinga ukuthola khona inkinobho ekhoneni elingezansi kwesokudla.

Ngemuva kwalokho, kuzovela iwindi lapho udinga ukukhetha amabhokisi okuhlola afanelekile bese ucindezela ukhiye OK. Lezi zilungiselelo zilawula ukuthi idatha izoboniswa kanjani.

Okubalulekile: empeleni, abantu abaningi bakuthola kungalungile ukubonisa amanani ngaphansi kwedatha. Ngakho-ke, ungashiya leli bhokisi lingahloliwe. Kuyanconywa futhi ukuthi uhlole ibhokisi elithi “Izitayela Ezizenzakalelayo”.

Ngemva kokuqeda lezi zinyathelo, ungaqala ngokushesha ukuqoqa imininingwane.

Uyiqoqa kanjani idatha ngemigqa

Manje ake sithole ukuthi yini okufanele yenziwe emigqeni yeqembu, ngokwenza.

1. Dala umugqa omusha ngenhla noma ngaphansi kwalawo esifuna ukuwaqoqa. Konke kuncike ekutheni iyiphi indlela yokubonisa imiphumela ekhethwe esigabeni sangaphambilini.
2. Isinyathelo esilandelayo ukwakha isihloko sethebula kuseli engaphezulu kwesokunxele somugqa owengeziwe. Leli kuzoba yigama leqembu okuzohlanganiswa kulo amaseli ajwayelekile ngesisekelo esithile.
3. Khetha wonke amaseli endaweni engezansi komugqa osanda kwakhiwa noma ngaphezulu kwawo (kuye ngokuthi senzeni esinyathelweni sokuqala). Ngemuva kwalokho, sibheka inkinobho ethi "Isakhiwo" kuthebhu yedatha futhi lapho sithola inketho ethi "Iqembu". Kubalulekile ukuthi ungachofozi umcibisholo noma igama lomyalo kuphaneli ye-pop-up, kodwa kusithonjana.

Uma usachofoza kumcibisholo oya phansi, kuzovela imenyu eyengeziwe ongakhetha kuyo umsebenzi wokuqoqa noma udale isakhiwo. Sithanda kakhulu ukuqoqa iqembu kulesi sigaba.

Ngemuva kwalokho, sikhetha isici esiyinhloko lapho ukuqoqwa kuzokwenziwa khona. Kungaba imigqa noma amakholomu. Uma ungashintshi lutho, into yokuqala ikhethwa ngokuzenzakalelayo. Ngemva kokuqinisekisa ukuthi izilungiselelo zisethwe njengoba sidinga, sidinga ukuqinisekisa izenzo zethu ngokucindezela inkinobho ethi KULUNGILE. Njengoba siqoqa ngomugqa, asikho isidingo sokushintsha lutho, sidinga nje ukuhlola kabili.

Okubalulekile: uma, ngaphambi kokuqala ukuqoqa izinto, ungakhethi iseli, kodwa wonke amakholomu noma imigqa kuphaneli yokuxhumanisa, leli bhokisi lengxoxo ngeke livele. Uhlelo luzothola ukuthi yini okumele luyenze ngokwalo.

Iqiniso lokuthi imigqa iqoqwe, singayiqonda ngophawu lokususa kuphaneli yokuxhumanisa. Lokhu kusitshela ukuthi idatha idaluliwe. Manje sesingawafihla ngokuchofoza lesi sithonjana noma ngokuchofoza inkinobho engu-1 phezulu kancane (ikhombisa izinga lokuqoqa).

Siyabona ukuthi imigqa ifihliwe, futhi uphawu lokususa lushintshile lwaba ukuhlanganisa. Ukuvula umugqa owufunayo, ungachofoza kuwo, bese uhlelo luzokwenza ngokwalo. Uma udinga ukwandisa yonke imigqa, khona-ke udinga ukuchofoza inkinobho ethi "2", etholakala phezulu kwephaneli yokuxhumanisa kuleli cala.

Ungawaqoqa kanjani amakholomu

Ukuze uqoqe amakholomu, i-algorithm yezenzo icishe ifane:

1. Kuye ngokuthi yiziphi izinketho esizikhethile kuzilungiselelo, sidinga ukufaka ikholomu entsha ngakwesobunxele noma kwesokudla sendawo ezoqoqwa.
2. Sibhala phansi igama leqembu kuseli ephezulu kakhulu yekholomu evelayo.
3. Sikhetha wonke amakholomu esiwadingayo ukuwaqoqa (vele ushiye leyo esiyengeze esigabeni sokuqala), bese uchofoza inkinobho ethi “Iqembu” ngendlela efanayo ne-algorithm echazwe ngenhla.
4. Manje sidinga ukuchofoza into ethi "Amakholomu" efasiteleni elincane bese uchofoza inkinobho ethi "Kulungile".
5. Ngikufisela inhlanhla.

Inothi. Njengalapho siqoqa imigqa, uma sikhetha wonke amakholomu kubha yokuhlanganisa evundlile, asilitholi ibhokisi lengxoxo elincane.

Ungawenza kanjani ama-multilevel grouping

I-Excel iwuhlelo olusebenzayo, kodwa amathuba ayo awagcini ngokuqoqana ezingeni elilodwa, njengoba kuchazwe ezibonelweni ezingenhla. Kuphinde kube nekhono lokuqoqa amaseli ngamaleveli amaningana. Lokhu kwenziwa ngale ndlela elandelayo:

1. Okokuqala, iqembu eliyinhloko lakhiwe ngendlela echazwe ngenhla. Amaqembu amancane azobese engezwa kuyo.
2. Ngemva kwalokho, siqinisekisa ukuthi iqembu eliyinhloko livuliwe. Kuyo, senza futhi izenzo ezichazwe ngenhla. Izinyathelo ezithile zincike ekutheni umuntu usebenza ngemigqa noma ngamakholomu.
3. Ngenxa yalokho, amaqembu amazinga amaningana angadalwa.

Imiyalo yokukhipha idatha eqoqweni

Isimo singase sivele lapho iqembu elidalwe ngaphambilini noma iqembu elincane lingasadingeki. Kunomsebenzi ohlukile walokhu - "Susa iqembu". Ukuze uyisebenzise, ​​udinga ukulandela lezi zinyathelo ezilandelayo:

1. Khetha ama-elementi ayingxenye yeqembu.
2. Vula ithebhu "Idatha".
3. Sithola iqembu elithi "Isakhiwo" lapho, livule ngomcibisholo ongezansi.
4. Lapho, chofoza inkinobho ethi “Susa iqembu”. Kubaluleke kakhulu ukuchofoza isithonjana, hhayi umbhalo.

Okulandelayo, sikhetha ukuthi yini ngempela esifuna ukuyisusa eqenjini. Sikhetha into efanelekile kuye ngokuthi sihlanganise ini ngaphambilini. Ngemva kokuqeda isenzo, cindezela inkinobho ethi KULUNGILE.

Qaphela! Uma ngaphambi kwalokho kwenziwa iqembu elinamazinga amaningi noma kwakhiwe amaqembu amaningana ahlukene, kufanele alungiswe ngokuhlukana.

Lapha siphinde sithole umphumela onjalo.

Lena i-algorithm evamile yokuhlukanisa amaseli, kodwa njengakunoma yiliphi ibhizinisi, maningi ama-nuances. Ake sizibheke ngokuningiliziwe.

Ungawahlukanisa kanjani amashidi

Kungase kudingeke ukuba uhlukanise amashidi ngezikhathi ezithile. Ukuze wenze lokhu, kufanele wenze ukulandelana kwezenzo ezilandelayo:

1. Okokuqala, sithola lawo mashidi aqoqwe. Uma amashidi eqoqwe kusengaphambili, azovezwa ngombala ofanayo noma isihloko sizoba ngokugqamile.
2. Ngemuva kwalokho, udinga ukuchofoza kwesokudla kwelinye lamashidi avela eqenjini, bese uchofoza inkinobho ethi "Susa amashidi" kumenyu yokuqukethwe. Manje bazohlukaniswa, futhi zonke izinguquko zizokwenziwa kubo ngaphandle kwabanye.

Enye indlela yokukhipha amashidi ngokusebenzisa ukhiye we-Shift bese uchofoza ishidi elisebenzayo eqenjini ofuna ukulikhipha. Okusho ukuthi, udinga ukuchaza iqembu okudingeka ukuhlukaniswa kwalo, bese uchofoza kwelinye lamathebhu, bese uchofoza kulo ngenkathi ubambe ukhiye u-Shift.

Ngemva kokuba izinguquko zenziwe, manje ungakwazi ukuqoqa amanye amashidi. Ukuze wenze lokhu, cindezela u-Ctrl noma u-Cmd ukhiye (owokuqala ungowamakhompyutha asebenzisa iWindows, owesibili ngowobuchwepheshe be-Apple), futhi ngenkathi ubambe inkinobho, chofoza ngokuhlukile kumashidi ofuna ukuwahlanganisa abe yiqembu. Ngemuva kwalokho, uhlelo luzokwenza konke kumsebenzisi.

Ungayikhipha kanjani idatha eqoqwe ngokwenza

Ngaphambi kokukhipha amaseli, kufanele uqale uqonde ukuthi aqoqwe kanjani: ngokwenza noma ngokuzenzakalelayo. Ukuqoqana ngezandla kuthathwa njengendlela echazwe ngenhla. Ukukhiqiza okuzenzakalelayo kwamaqembu yilapho enziwa kusetshenziswa imisebenzi ethile. Isibonelo, ngemuva kokukhiqiza ama-subtotals. Iqiniso lokuthi leli thuba lisetshenzisiwe lingaqondwa emgqeni othi "Imiphumela ephakathi".

Uma idatha iqoqwe ngomsebenzi wegama elifanayo Excel, khona-ke ukuze uyihlakaze, udinga ukwenza isiqiniseko sokuthi iqembu lisatshalalisiwe. Uma kungenjalo, kuzomele uchofoze inkinobho ethi + kubha eseceleni kwesokunxele. Iqiniso lokuthi iqembu lisetshenzisiwe, singahlulela ngenkinobho - endaweni efanayo. Uma sandisa iqembu, siqala ukubona kokubili imigqa efihliwe namaqembu. Ngemuva kwalokho, usebenzisa ikhibhodi noma inkinobho yegundane kwesokunxele, khetha wonke amaseli eqenjini, bese - ngokusho kwe-algorithm echazwe ngenhla.

Bambalwa abantu abazi ukuthi kukhona enye indlela yokukhipha amaseli aqoqwe ngesandla - usebenzisa okhiye abashisayo.

Ukuze wenze lokhu, kufanele uqale ukhethe lawo makholomu aqoqwe, bese ucindezela u-Alt + Shift + okhiye bemicibisholo Yesokunxele. Uma umsebenzi wenziwa kukhompyutha elawulwa ngaphansi kwe-Mac OS, udinga ukusebenzisa inhlanganisela yokhiye Command + Shift + J.

Ungayikhipha kanjani idatha eqoqwe ngokuzenzakalelayo

Uma, ngenxa yokuhlolwa okwenziwe esigabeni esandulele, kwavela ukuthi idatha iqoqwe ngokuzenzakalelayo, khona-ke yonke into iyinkimbinkimbi kakhulu, ngoba umsebenzi ojwayelekile we-ungroup ngeke usebenze kuleli cala. Izenzo zincike ekutheni yini ngempela eyenze ukuqoqwa kwedatha. Uma lona kuwumsebenzi othi “Amathothali amancane”, ukulandelana kwezenzo kuba kanje:

1. Sivula ithebhu efanayo ngedatha kuphaneli eyinhloko (noma iribhoni, njengoba ivame ukubizwa kanjalo).
2. Ngemuva kokuthi sichofoze inkinobho ethi "Amanani aphansi" (okuyikhona kanye okudingeka sikwenze njengesinyathelo sesibili), sizoba newindi. Inkinobho ngokwayo itholakala esigabeni esifanayo - Isakhiwo. Ngemuva kwalokho, kuzovela iwindi lapho sidinga ukuchofoza inkinobho ethi "Susa Konke". Ingatholakala ekhoneni elingezansi kwesokunxele lewindi noma kwenye indawo (kuye ngokuthi inguqulo ye-Office kanye nohlelo oluthile lwesipredishithi).

Qaphela! Le ndlela ayisusi kuphela iqembu, kodwa futhi nama-subtotals. Ngakho-ke, uma udinga ukuzigcina, kungcono ukukopisha izakhi zeqembu kwelinye ishidi futhi uzisebenzise njengezingahlanganisiwe.

Isincomo esinamandla sanoma yikuphi ukusebenza okunethebula lokuqoqa noma ukususa iqembu ledatha. Ngaphambi kokwenza lokhu, udinga ukwenza ikhophi yetafula lokuqala. Kulokhu, ungakwazi ukubuyisela umbono wangempela wedokhumenti uma okuthile kungahambi ngokohlelo.

Ngakho, i-Excel inomsebenzi obanzi kakhulu wokuhlela idatha. Yiqiniso, ngeke ikwazi ukwenzela umuntu yonke into, kodwa ikuvumela ukuthi uhlele kalula umsebenzi ngedatha ehlelekile. Okunye kuzomele kwenziwe ngumuntu. Kodwa-ke, leli yithuluzi elisebenza kakhulu elizoba usizo kakhulu kulabo okufanele basebenze ngenani elikhulu lolwazi lwezinombolo kanye nombhalo.