Izindlela ezi-2 zokushintsha umbala wokugcwalisa iseli ku-Excel ngokusekelwe kumanani azo

Kulesi sihloko, uzothola izindlela ezimbili ezisheshayo zokushintsha umbala weseli ngokusekelwe kunani layo ku-Excel 2013, 2010, kanye no-2007. Futhi, uzofunda ukusebenzisa amafomula ku-Excel ukushintsha umbala wamaseli angenalutho. noma amaseli anamaphutha efomula.

Wonke umuntu uyazi ukuthi ukuze uguqule umbala wokugcwalisa weseli eyodwa noma lonke uhla ku-Excel, mane uchofoze inkinobho Gcwalisa umbala (Gcwalisa umbala). Kodwa kuthiwani uma udinga ukushintsha umbala wokugcwalisa wawo wonke amaseli aqukethe inani elithile? Ngaphezu kwalokho, kuthiwani uma ufuna umbala wokugcwalisa weseli ngalinye ushintshe ngokuzenzakalelayo njengoba okuqukethwe kulelo seli kushintsha? Ngokuqhubekayo esihlokweni uzothola izimpendulo zale mibuzo futhi uthole amathiphu ambalwa awusizo azokusiza ukhethe indlela efanele yokuxazulula inkinga ngayinye.

• Shintsha umbala wokugcwalisa wamaseli ngokusekelwe kumanani awo (ngokuguquguqukayo) - Umbala wokugcwalisa uzoshintsha ngokuzenzakalelayo lapho inani leseli lishintsha.
• Shintsha umbala weseli ngokusekelwe kunani layo lamanje (ngokwezibalo) - Uma usumisiwe, umbala wokugcwalisa awushintshi, kungakhathaliseki ukuthi inani leseli lishintsha kanjani.
• Shintsha umbala wamaseli athile (angenalutho, namaphutha, ngamafomula)

Ungawushintsha kanjani umbala weseli ku-Excel ngokuya ngenani lalo

Umbala wokugcwalisa uzoshintsha kuye ngevelu yeseli.

Inkinga: Unethebula noma ububanzi bedatha futhi ufuna ukushintsha umbala wokugcwalisa wamaseli ngokusekelwe kumanani awo. Ngaphezu kwalokho, kuyadingeka ukuthi lo mbala ushintshe ngokushintshashintshayo, ubonise izinguquko kudatha kumaseli.

Isinqumo: Sebenzisa ukufometha okunemibandela ku-Excel ukuze ugqamise amanani amakhulu kuno-X, ngaphansi kuno-Y, noma phakathi kuka-X kanye no-Y.

Ake sithi unohlu lwamanani entengo kaphethiloli ezifundazweni ezihlukene, futhi ufuna amanani aphezulu kunawo $ 3.7, agqanyiswe ngokubomvu, futhi amancane noma alinganayo $ 3.45 – green.

Qaphela: Izithombe-skrini zalesi sibonelo zithathwe ku-Excel 2010, nokho, ku-Excel 2007 no-2013, izinkinobho, izingxoxo, nezilungiselelo zizofana ncamashi noma zehluke kancane.

Ngakho-ke, nakhu okudingeka ukwenze isinyathelo ngesinyathelo:

1. Khetha ithebula noma ububanzi ofuna ukushintsha umbala wokugcwalisa iseli. Kulesi sibonelo, sigqamisa $B$2:$H$10 (izihloko zekholomu kanye nekholomu yokuqala equkethe amagama ezifunda akukhethiwe).
2. Chofoza Ikhaya (Ekhaya), esigabeni izitayela (Izitayela) chofoza Ukufomatha okunemibandela (Ukufometha Okunemibandela) > Imithetho Emisha (Yakha umthetho).
3. Phezulu kwebhokisi lengxoxo Umthetho Omusha Wokufometha (Dala Umthetho Wokufometha) ensimini Khetha Uhlobo Lomthetho (Khetha uhlobo lomthetho) khetha Fometha kuphela amaseli aqukethe (Fometha kuphela amaseli aqukethe).
4. Phansi kwebhokisi lengxoxo ebhokisini Fometha Kuphela Amaseli nge (Fometha kuphela amaseli ahlangabezana nemibandela elandelayo) Setha imibandela yomthetho. Sikhetha ukufometha kuphela amaseli analesi simo: Ivelu Yeseli (inani leseli) - mkhulu kune- (Okuningi) - 3.7njengoba kuboniswe emfanekisweni ongezansi.Bese ucindezela inkinobho Usayizi (Ifomethi) ukuze ukhethe ukuthi yimuphi umbala wokugcwalisa okufanele usetshenziswe uma umbandela oshiwo ufinyelelwa.
5. Ebhokisini lengxoxo elivelayo Fometha Amaseli (Fometha Amaseli) ithebhu Gcwalisa (Gcwalisa) bese ukhetha umbala (sikhethe obomvu) bese uchofoza OK.
6. Ngemva kwalokho uzobuyela efasiteleni Umthetho Omusha Wokufometha (Ukudala umthetho wokufometha) lapho kumkhakha Buka kuqala (Isampula) izobonisa isampula yokufometha kwakho. Uma wanelisekile, chofoza OK.

Umphumela wezilungiselelo zakho zokufometha uzobukeka kanje:

Njengoba sidinga ukusetha esinye isimo esisivumela ukuthi siguqule umbala wokugcwalisa ube luhlaza kumaseli anamanani angaphansi noma alingana nawo. 3.45, bese ucindezela inkinobho futhi Imithetho Emisha (Yakha Umthetho) bese uphinda izinyathelo 3 kuya ku-6, ubeka umthetho owufunayo. Ngezansi isampula yomthetho wokufometha onemibandela wesibili esiwudalile:

Uma yonke into isilungile - chofoza OK. Manje usunethebula elifomethwe kahle elikuvumela ukuthi ubone amanani aphezulu kanye nobuncane begesi ezifundazweni ezihlukene lapho uthi shazi. Kuhle kubo lapho, eTexas! 🙂

Ithiphu: Ngendlela efanayo, ungashintsha umbala wefonti kuye ngevelu yeseli. Ukuze wenze lokhu, vele uvule ithebhu Ifonti (Ifonti) ebhokisini lengxoxo Fometha Amaseli (Ifomethi Yeseli) njengoba senzile esinyathelweni sesi-5 bese ukhetha umbala wefonti oyifunayo.

Ungawumisa kanjani umbala weseli ongashintshi ngokusekelwe kunani lawo lamanje

Uma usumisiwe, umbala wokugcwalisa ngeke ushintshe, kungakhathaliseki ukuthi okuqukethwe kweseli kushintsha kanjani esikhathini esizayo.

Inkinga: Ufuna ukulungisa umbala weseli ngokusekelwe kunani layo lamanje, futhi ufuna umbala wokugcwalisa uhlale unjalo ngisho nalapho inani leseli lishintsha.

Isinqumo: Thola wonke amaseli anenani elithile (noma amanani) usebenzisa ithuluzi Thola Konke (Thola Konke) bese ushintsha ifomethi yamaseli atholakele usebenzisa ibhokisi lengxoxo Fometha Amaseli (ifomethi yeseli).

Lona ngomunye waleyo misebenzi eyivelakancane okungekho ncazelo yayo kumafayela osizo e-Excel, izinkundla noma amabhulogi, futhi okungekho sixazululo sayo esiqondile. Futhi lokhu kuyaqondakala, ngoba lo msebenzi awujwayelekile. Futhi nokho, uma udinga ukushintsha umbala wokugcwalisa iseli unomphela, okungukuthi, kanye kuphela (noma uze uwushintshe ngesandla), landela lezi zinyathelo.

Thola bese ukhetha wonke amaseli ahlangabezana nombandela othile

Izimo ezimbalwa zingenzeka lapha, kuye ngokuthi yiluphi uhlobo lwenani olufunayo.

Uma ufuna ukufaka imibala kumaseli ngevelu ethile, isibonelo, 50, 100 or 3.4 – bese kuthebhu Ikhaya (Ekhaya) esigabeni Editing (Ukuhlela) chofoza Thola Khetha (Thola bese ugqamisa) > Thola (Thola).

Faka inani olifunayo bese uchofoza Thola Konke (Thola konke).

Ithiphu: Ngakwesokudla sebhokisi lengxoxo Thola futhi ubeke esikhundleni (Thola futhi Faka esikhundleni) kukhona inkinobho Izinketho (Izinketho), ngokucindezela lapho uzokwazi ukufinyelela kuzilungiselelo zokusesha ezithuthukile, njenge Ikesi lokufanisa (icala elibucayi) kanye Qondanisa konke okuqukethwe kweseli (Iseli lonke). Ungasebenzisa izinhlamvu ze-wildcard njengenkanyezi (*) ukuze ufanise nanoma yiluphi uchungechunge lwezinhlamvu, noma uphawu lombuzo (?) ukuze ufanise nanoma yiluphi uhlamvu olulodwa.

Mayelana nesibonelo sangaphambilini, uma sidinga ukuthola wonke amanani kaphethiloli kusuka 3.7 kuya 3.799, bese sizosetha le mibandela yokusesha elandelayo:

Manje chofoza kunoma yiziphi izinto ezitholakele ezansi kwebhokisi lengxoxo Thola futhi ubeke esikhundleni (Thola futhi Faka esikhundleni) bese uchofoza Ctrl + Aukugqamisa konke okufakiwe okutholiwe. Ngemva kwalokho cindezela inkinobho vala (Vala).

Lena yindlela ongakhetha ngayo wonke amaseli anenani elinikeziwe (amanani) usebenzisa inketho Thola Konke (Thola Konke) ku-Excel.

Kodwa-ke, empeleni, sidinga ukuthola zonke izintengo zikaphethiloli ezidlulayo $ 3.7. Ngeshwa ithuluzi Thola futhi ubeke esikhundleni (Thola bese Ufaka esikhundleni) ngeke kusisize ngalokhu.

Shintsha Imibala Yokugcwalisa Yamaseli Akhethiwe Usebenzisa Ibhokisi Lengxoxo Yefomethi Yeseli

Manje usunawo wonke amaseli anenani elinikeziwe (noma amanani) akhethiwe, sisanda kwenza lokhu ngethuluzi Thola futhi ubeke esikhundleni (Thola bese ufaka esikhundleni). Okufanele ukwenze ukusetha umbala wokugcwalisa wamaseli akhethiwe.

Vula ibhokisi lengxoxo Fometha Amaseli (Ifomethi Yeseli) nganoma yiziphi izindlela ezi-3:

• ukucindezela Ctrl + 1.
• ngokuchofoza kunoma iyiphi iseli ekhethiwe ngenkinobho yegundane engakwesokudla nokukhetha into kumenyu yokuqukethwe Fometha Amaseli (ifomethi yeseli).
• ithebhu Ikhaya (Ekhaya) > Amaseli. Amaseli. (amaseli) > Usayizi (Ifomethi) > Fometha Amaseli (ifomethi yeseli).

Okulandelayo, lungisa izinketho zokufometha ngendlela othanda ngayo. Kulokhu sizosetha umbala wokugcwalisa ube owolintshi, ukuze nje sishintshe 🙂

Uma ufuna ukushintsha umbala wokugcwalisa kuphela ngaphandle kokuthinta zonke ezinye izinketho zokufometha, ungavele uchofoze inkinobho Gcwalisa umbala (Gcwalisa umbala) bese ukhetha umbala owuthandayo.

Nawu umphumela wezinguquko zethu zokufometha ku-Excel:

Ngokungafani nendlela yangaphambilini (ngokufometha okunemibandela), umbala wokugcwalisa osethwe ngale ndlela awusoze washintsha ngokwawo ngaphandle kolwazi lwakho, noma ngabe amanani ashintsha kanjani.

Shintsha umbala wokugcwalisa wamaseli akhethekile (akunalutho, ngephutha kufomula)

Njengasesibonelweni sangaphambilini, ungashintsha umbala wokugcwalisa wamaseli athile ngezindlela ezimbili: ngokuguqukayo nangezibalo.

Sebenzisa ifomula ukuze ushintshe umbala wokugcwalisa wamaseli akhethekile ku-Excel

Umbala weseli uzoshintsha ngokuzenzakalelayo kuye ngevelu yeseli.

Cishe uzosebenzisa le ndlela yokuxazulula inkinga kuma-99% wamacala, okungukuthi, ukugcwaliswa kwamaseli kuzoshintsha ngokuhambisana nesimo osishilo.

Isibonelo, ake sithathe ithebula lentengo kaphethiloli futhi, kodwa kulokhu sizongeza izifunda ezimbalwa, futhi senze amanye amaseli angabi nalutho. Manje bona ukuthi ungawathola kanjani lawa maseli angenalutho futhi ushintshe umbala wawo wokugcwalisa.

1. Kuthebhu ethuthukisiwe Ikhaya (Ekhaya) esigabeni izitayela (Izitayela) chofoza Ukufomatha okunemibandela (Ukufometha Okunemibandela) > Imithetho Emisha (Yakha umthetho). Njengasesinyathelweni sesi-2 sesibonelo Ungawushintsha kanjani umbala weseli ngokusekelwe kunani layo.
2. Ebhokisini lengxoxo Umthetho Omusha Wokufometha (Dala umthetho wokufometha) khetha inketho Sebenzisa ifomula ukuze unqume ukuthi yimaphi amaseli ukufometha (Sebenzisa ifomula ukuze unqume ukuthi yimaphi amaseli ozofometha). Ngokuqhubekayo ensimini Fometha amanani lapho le fomula iyiqiniso (Amanani efomethi lapho ifomula elandelayo iyiqiniso) faka eyodwa yamafomula:
   • ukushintsha ukugcwaliswa kwamaseli angenalutho

     =ISBLANK()

     =ЕПУСТО()

   • ukushintsha ukufiphaza kwamaseli aqukethe amafomula abuyisela iphutha

     =ISERROR()

     =ЕОШИБКА()

   Njengoba sifuna ukushintsha umbala wamaseli angenalutho, sidinga umsebenzi wokuqala. Ifake, bese ubeka ikhesa phakathi kwabakaki bese uchofoza isithonjana sokukhetha ububanzi kwesokudla somugqa (noma uthayiphe uhla olufunekayo mathupha):

   =ISBLANK(B2:H12)

   =ЕПУСТО(B2:H12)

   

3. Chofoza inkinobho Usayizi (Ifomethi), khetha umbala wokugcwalisa owufunayo kuthebhu Gcwalisa (Gcwalisa), bese uchofoza OK. Imiyalo enemininingwane inikezwe esinyathelweni sesi-5 sesibonelo "Indlela yokushintsha umbala weseli ngokusekelwe kunani layo." Isampula yokufometha okunemibandela okusethile izobukeka kanje:
4. Uma ujabulile ngombala, chofoza OK. Uzobona ukuthi umthetho odaliwe uzosetshenziswa kanjani ngokushesha etafuleni.

Shintsha umbala wokugcwalisa wamaseli akhethekile ngokwezibalo

Uma sekulungisiwe, ukugcwaliswa kuzohlala kungashintshiwe, kungakhathaliseki inani leseli.

Uma ufuna ukusetha umbala wokugcwalisa unomphela wamaseli angenalutho noma amaseli anamafomula aqukethe amaphutha, sebenzisa le ndlela:

1. Khetha ithebula noma ububanzi bese uchofoza F5ukuvula ibhokisi Iya ku (Yeqa), bese ucindezela inkinobho Special (Gqamisa).
2. Ebhokisini lengxoxo Iya kokuthi Okukhethekile (Khetha iqembu lamaseli) hlola inketho izikhala (Amaseli Angenalutho) ukuze ukhethe wonke amaseli angenalutho.Uma ufuna ukugqamisa amaseli aqukethe amafomula anamaphutha, hlola inketho amafomula (amafomula) > Amaphutha (Amaphutha). Njengoba ubona esithombeni esingenhla, ziningi ezinye izilungiselelo ongazithola.
3. Ekugcineni, shintsha ukugcwaliswa kwamaseli akhethiwe noma usethe noma yiziphi ezinye izinketho zokufometha usebenzisa ibhokisi lengxoxo Fometha Amaseli (Fometha Amaseli), njengoba kuchazwe kokuthi Ukushintsha Ukugcwaliswa Kwamaseli Akhethiwe.

Ungakhohlwa ukuthi izilungiselelo zokufometha ezenziwe ngale ndlela zizogcinwa ngisho noma amaseli angenalutho egcwele amanani noma amaphutha kumafomula elungiswa. Kunzima ukucabanga ukuthi othile angase adinge ukuhamba ngale ndlela, ngaphandle kwezinhloso zokuhlolwa 🙂