Chosavuta, poyang'ana koyamba, vuto ndi yankho losawonekera: tulutsani liwu lomaliza pamzere wamalemba. Chabwino, kapena, makamaka, chidutswa chomaliza, cholekanitsidwa ndi chilengezo choperekedwa (malo, koma, ndi zina zotero) Mwanjira ina, ndikofunikira kukhazikitsa kusaka mobwerera (kuchokera kumapeto mpaka koyambirira) mu chingwe cha a. chopatsidwa ndiyeno kuchotsa zilembo zonse kumanja kwake .
Tiyeni tiwone mwamwambo njira zingapo zomwe mungasankhe: mafomula, ma macros, komanso kudzera mu Power Query.
Njira 1. Zolemba
Kuti zikhale zosavuta kumvetsetsa tanthauzo ndi zimango za chilinganizo, tiyeni tiyambire pang'ono kuchokera patali. Choyamba, tiyeni tiwonjeze kuchuluka kwa mipata pakati pa mawu muzolemba zathu zoyambira, mwachitsanzo, zidutswa 20. Mutha kuchita izi ndi ntchito yobwezeretsa. WOLEMBEDWA (MALOWA) ndi ntchito yobwereza munthu wopatsidwa N-nthawi - REPEAT (REPT):
Tsopano tadula zilembo 20 kuchokera kumapeto kwa zolembazo pogwiritsa ntchito ntchitoyi ZABWINO (KUKWERA):
Kukutentha eti? Zimatsalira kuchotsa malo owonjezera pogwiritsa ntchito ntchitoyi TRIM (TRIM) ndipo vuto lidzathetsedwa:
M'Chingerezi, formula yathu idzawoneka motere:
= KUCHITA(KULADZO(MALOWA(A1;» «;REPT(» «;20)));20))
Ndikuyembekeza kuti zikuwonekeratu kuti mfundo siziyenera kuyika malo enieni a 20 - chiwerengero chilichonse chidzachita, malinga ngati chiri choposa kutalika kwa mawu aatali kwambiri m'mawu oyambira.
Ndipo ngati gwero liyenera kugawidwa osati ndi danga, koma ndi munthu wina wolekanitsa (mwachitsanzo, ndi koma), ndiye kuti ndondomeko yathu iyenera kukonzedwa pang'ono:
Njira 2. Macro ntchito
Ntchito yochotsa mawu omaliza kapena chidutswa kuchokera m'malembawo itha kuthetsedwanso pogwiritsa ntchito macros, mwachitsanzo, kulemba ntchito yofufuzira m'mbuyo mu Visual Basic yomwe ingachite zomwe tikufuna - fufuzani chingwe chapang'onopang'ono mu chingwe kumbali ina - kuchokera mapeto mpaka pachiyambi.
Dinani njira yachidule ya kiyibodi alt+F11 kapena batani Zooneka Basic tsamba Woyambitsa (Wolemba Mapulogalamu)kuti mutsegule macro editor. Kenako onjezani gawo latsopano kudzera pa menyu Ikani - Module ndi kukopera khodi ili:
Function LastWord(txt As String, Optional delim As String = " ", Optional n As Integer = 1) As String arFragments = Split(txt, delim) LastWord = arFragments(UBound(arFragments) - n + 1) End Function
Tsopano mutha kusunga bukulo (mumtundu wothandizidwa ndi macro!)
=LastWord(txt ; delim ; n)
kumene
- txt - cell yokhala ndi mawu oyambira
- delim - olekanitsa chikhalidwe (chosasintha - danga)
- n - ndi mawu ati omwe ayenera kuchotsedwa kumapeto (mwachikhazikitso - woyamba kuchokera kumapeto)
Ndi kusintha kulikonse pamawu oyambira m'tsogolomu, ntchito yathu yayikulu idzawerengedwanso pa ntchentche, monga ntchito iliyonse yamtundu wa Excel sheet.
Njira 3. Funso la Mphamvu
Kufunsa Mphamvu ndi chowonjezera chaulere kuchokera ku Microsoft cholowetsa deta mu Excel kuchokera pafupifupi kulikonse ndikusintha zomwe zidatsitsidwa kukhala mtundu uliwonse. Mphamvu ndi kuziziritsa kwa chowonjezerachi ndikwambiri kotero kuti Microsoft yapanga zida zake zonse mu Excel 2016 mwachisawawa. Kwa Excel 2010-2013 Power Query ikhoza kutsitsidwa kwaulere apa.
Ntchito yathu yolekanitsa mawu omaliza kapena chidutswa kudzera pa cholekanitsa chopatsidwa pogwiritsa ntchito Power Query imathetsedwa mosavuta.
Choyamba, tiyeni tisinthe tebulo lathu la data kukhala tebulo lanzeru pogwiritsa ntchito njira zazifupi za kiyibodi. Ctrl+T kapena malamulo Kunyumba - Pangani ngati tebulo (Kunyumba - Pangani Monga Table):
Kenako timayika "smart table" yomwe idapangidwa mu Power Query pogwiritsa ntchito lamulo Kuchokera pa tebulo/ranji (Kuchokera patebulo/mtundu) tsamba Deta (ngati muli ndi Excel 2016) kapena pa tabu Kufunsa Mphamvu (ngati muli ndi Excel 2010-2013):
Pazenera la mkonzi wamafunso lomwe limatsegulidwa, pa tabu Transformation (Sinthani) sankhani gulu Gawani Mzere - Wolemba Delimiter (Gawani Mgawo - Wolemba malire) ndiyeno zimatsalira kukhazikitsa olekanitsa ndi kusankha njira Chotsitsa chakumanjaosadula mawu onse, koma omaliza okha:
Pambuyo pang'anani OK mawu otsiriza adzapatulidwa kukhala gawo latsopano. Mzere woyamba wosafunikira ukhoza kuchotsedwa ndikudina kumanja mutu wake ndikusankha Chotsani (Chotsani). Mukhozanso kutchulanso gawo lotsalira pamutu wa tebulo.
Zotsatira zitha kukwezedwanso papepala pogwiritsa ntchito lamulo Kunyumba - Tsekani ndikunyamula - Tsekani ndikunyamula ku ... (Kunyumba - Tsekani & Kwezani - Tsekani & Kwezani ku...):
Ndipo chifukwa chake timapeza:
Monga chonchi - chotsika mtengo komanso chansangala, popanda mafomula ndi macros, pafupifupi osakhudza kiyibodi 🙂
Ngati mndandanda wapachiyambi ukasintha m'tsogolomu, zidzakhala zokwanira kudina-kumanja kapena kugwiritsa ntchito njira yachidule ya kiyibodi Ctrl+alt+F5 sinthani pempho lathu.
- Kugawa mawu omata m'migawo
- Kusanthula ndi kugawa mawu ndi mawu okhazikika
- Kutulutsa mawu oyamba m'mawu ndi SUBSTITUTE ntchito