Momwe mungayikitsire zomwezo (ma formula) m'maselo onse osankhidwa nthawi imodzi

M'nkhaniyi, muphunzira njira ziwiri zofulumira kwambiri zoyika fomula kapena zolemba zomwezo m'maselo angapo nthawi imodzi mu Excel. Izi ndizothandiza mukamafuna kuyika fomula m'maselo onse pamndandanda, kapena mudzaze maselo opanda kanthu ndi mtengo womwewo (mwachitsanzo, "N/A"). Njira zonsezi zimagwira ntchito mu Microsoft Excel 2, 2013, 2010 ndi kale.

Kudziwa zanzeru zosavuta izi kudzakupulumutsirani nthawi yambiri yochita zinthu zosangalatsa.

• Sankhani ma cell onse omwe mukufuna kuyikamo data yomweyo
  
  • Sankhani gawo lonse
  • Sankhani mzere wonse
  • Kusankha ma cell angapo
  • Sankhani tebulo lonse
  • Sankhani maselo onse papepala
  • Sankhani ma cell opanda kanthu mdera lomwe mwapatsidwa (mzere, muzanja, patebulo)
• Njira yofulumira kwambiri yoyika fomula m'maselo onse agawo
• Ikani zomwezo m'maselo angapo pogwiritsa ntchito Ctrl + Lowani

Sankhani ma cell onse omwe mukufuna kuyikamo data yomweyo

Nazi njira zachangu zowunikira ma cell:

Sankhani gawo lonse

• Ngati deta mu Excel idapangidwa ngati tebulo lathunthu, ingodinani pa selo iliyonse yomwe mukufuna ndikudina Ctrl+Space.

Zindikirani: Mukasankha selo iliyonse mu tebulo lathunthu, gulu la ma tabo limawonekera pa Riboni ya Menyu Gwirani ntchito ndi matebulo (Zida Zam'ndandanda).

• Ngati iyi ndi mulingo wabwinobwino, mwachitsanzo, imodzi mwamaselo amtunduwu ikasankhidwa, gulu la ma tabo Gwirani ntchito ndi matebulo (Zida Zamndandanda) sizikuwoneka, chitani izi:

Zindikirani: Mwatsoka, pa nkhani ya losavuta osiyanasiyana, kukanikiza Ctrl+Space idzasankha ma cell onse agawo mu pepala, mwachitsanzo kuchokera C1 ku C1048576, ngakhale deta ili m'maselo okha c1: c100.

Sankhani selo loyamba lachigawo (kapena lachiwiri, ngati selo loyamba liri ndi mutu), ndiye dinani Shift+Ctrl+Endkuti musankhe ma cell onse a tebulo mpaka kumanja kwakutali. Chotsatira, kugwira kosangalatsa, dinani batani kangapo mzere wotsalirampaka gawo lomwe mukufuna likhalabe losankhidwa.

Iyi ndi njira yachangu kwambiri yosankhira ma cell onse pamndandanda, makamaka data ikalumikizidwa ndi ma cell opanda kanthu.

Sankhani mzere wonse

• Ngati deta mu Excel idapangidwa ngati tebulo lathunthu, ingodinani pa selo iliyonse ya mzere womwe mukufuna ndikudina. Shift+Space.
• Ngati muli ndi deta yokhazikika patsogolo panu, dinani selo lomaliza la mzere womwe mukufuna ndikudina Shift+Kunyumba. Excel idzasankha mitundu kuyambira pa selo lomwe mwatchula mpaka pamndandanda А. Ngati deta yomwe mukufuna ikuyamba, mwachitsanzo, ndi mzere B or C, tsina kosangalatsa ndipo dinani batani Mtsuko wolondolampaka mutapeza zotsatira zomwe mukufuna.

Kusankha ma cell angapo

Gwirani Ctrl ndipo dinani batani lakumanzere la mbewa pamaselo onse omwe akuyenera kudzazidwa ndi data.

Sankhani tebulo lonse

Dinani pa selo iliyonse patebulo ndikusindikiza Ctrl + A.

Sankhani maselo onse papepala

Press Ctrl + A kamodzi mpaka katatu. Dinani koyamba Ctrl + A ikuwonetsa malo omwe alipo. Kudina kwachiwiri, kuwonjezera pa malo omwe alipo, kumasankha mizere yokhala ndi mitu ndi ziwopsezo (mwachitsanzo, pamatebulo odzaza). Makina achitatu amasankha pepala lonse. Ndikuganiza kuti mumangoganizira, nthawi zina zimakutengerani kudina kamodzi kokha kuti musankhe pepala lonse, ndipo nthawi zina zimatengera kudina katatu.

Sankhani ma cell opanda kanthu mdera lomwe mwapatsidwa (mzere, muzanja, patebulo)

Sankhani malo omwe mukufuna (onani chithunzi pansipa), mwachitsanzo, ndime yonse.

Press F5 ndi mu dialog yomwe ikuwoneka Kusintha (Pitani ku) dinani batani yosangalatsa (Wapadera).

Mu dialog box Sankhani gulu la ma cell (Pitani Kwapadera) fufuzani bokosilo Maselo opanda kanthu (Zopanda kanthu) ndi knead OK.

Mudzabwereranso kumayendedwe a pepala la Excel ndipo muwona kuti maselo opanda kanthu okha amasankhidwa m'dera lomwe mwasankha. Maselo atatu opanda kanthu ndi osavuta kusankha ndi kudina kosavuta kwa mbewa - mudzanena ndipo mudzakhala olondola. Koma bwanji ngati pali ma cell opitilira 300 opanda kanthu ndipo amwazikana mwachisawawa pama cell angapo a 10000?

Njira yofulumira kwambiri yoyika fomula m'maselo onse agawo

Pali tebulo lalikulu, ndipo muyenera kuwonjezera ndime yatsopano yokhala ndi fomula yake. Tiyerekeze kuti uwu ndi mndandanda wamaadiresi a pa intaneti omwe mukufuna kuchotsamo mayina a madambwe kuti mugwire ntchito zina.

1. Sinthani mtundu kukhala tebulo la Excel. Kuti muchite izi, sankhani selo iliyonse mumtundu wa data ndikusindikiza Ctrl + Tkubweretsa dialog box Kupanga tebulo (Pangani Table). Ngati deta ili ndi mitu yandalama, chongani bokosilo Tebulo yokhala ndi mitu (Gome langa lili ndi mitu). Nthawi zambiri Excel imazindikira mitu yokha, ngati sizikugwira ntchito, fufuzani bokosilo pamanja.
2. Onjezani gawo latsopano patebulo. Ndi tebulo, ntchitoyi ndi yosavuta kusiyana ndi deta yosavuta. Dinani kumanja pa selo iliyonse yomwe ili mugawo lomwe limabwera pambuyo pomwe mukufuna kuyika gawo latsopano, ndipo kuchokera pamenyu yankhaniyo sankhani. Ikani > Mzere kumanzere (Lowetsani> Mzere Watebulo Kumanzere).
3. Perekani dzina ku gawo latsopanoli.
4. Lowetsani fomula mu selo yoyamba yagawo latsopano. Mu chitsanzo changa, ndimagwiritsa ntchito fomula kuti ndichotse mayina amtundu:

   =MID(C2,FIND(":",C2,"4")+3,FIND("/",C2,9)-FIND(":",C2,"4")-3)

   =ПСТР(C2;НАЙТИ(":";C2;"4")+3;НАЙТИ("/";C2;9)-НАЙТИ(":";C2;"4")-3)

   

5. Press Lowani. Voila! Excel idangodzaza ma cell onse opanda kanthu mugawo latsopanolo ndi njira yomweyo.

Ngati mwaganiza zobwerera kuchokera patebulo kupita ku mtundu wanthawi zonse, ndiye sankhani selo iliyonse patebulo ndi pa tabu Constructor (Kupanga) dinani Sinthani kukhala osiyanasiyana (Sinthani kukhala osiyanasiyana).

Chinyengochi chitha kugwiritsidwa ntchito ngati ma cell onse mumgawo alibe kanthu, ndiye ndibwino kuwonjezera gawo latsopano. Chotsatira ndichowonjezera kwambiri.

Ikani zomwezo m'maselo angapo pogwiritsa ntchito Ctrl + Lowani

Sankhani ma cell pa pepala la Excel omwe mukufuna kudzaza ndi data yomweyi. Njira zomwe tafotokozazi zidzakuthandizani kusankha mwamsanga maselo.

Tiyerekeze kuti tili ndi tebulo ndi mndandanda wa makasitomala (ife, ndithudi, tidzatenga mndandanda wopeka). Chimodzi mwa zigawo za tebulo ili ndi malo omwe makasitomala athu adachokera. Maselo opanda kanthu mugawoli akuyenera kudzazidwa ndi mawu akuti "_osadziwika_" kuti asanthule bwino:

1. Sankhani maselo onse opanda kanthu muzambiri.
2. Press F2kusintha selo yogwira, ndikulowetsamo chinachake: ikhoza kukhala malemba, nambala, kapena fomula. Kwa ife, awa ndi mawu akuti "_osadziwika_".
3. Tsopano mmalo mwake Lowani pitani Ctrl + Lowani. Maselo onse osankhidwa adzadzazidwa ndi zomwe zalowa.

Ngati mukudziwa njira zina zolowera mwachangu deta, tiuzeni za iwo mu ndemanga. Ndiwonjeza mokondwera ku nkhaniyi, ndikutchula inu monga wolemba.