Zamkatimu
Kuduka mizere mkati mwa selo lomwelo, kuwonjezeredwa pogwiritsa ntchito njira yachidule ya kiyibodi alt+Lowani ndi chinthu chofala kwambiri komanso chofala. Nthawi zina amapangidwa ndi ogwiritsa ntchito okha kuti awonjezere kukongola kwa zolemba zazitali. Nthawi zina kusamutsidwa koteroko kumawonjezedwa pokhapokha potsitsa deta kuchokera ku mapulogalamu aliwonse ogwira ntchito (hello 1C, SAP, etc.) vuto. Ndipo sangakhale - ngati mukudziwa momwe mungawagwiritsire ntchito moyenera.
Tiyeni tione nkhaniyi mwatsatanetsatane.
Kuchotsa zoduka mizere mwakusintha
Ngati tifunika kuchotsa ma hyphens, ndiye chinthu choyamba chomwe chimabwera m'maganizo ndi njira yachikale "peza ndikusintha". Sankhani mawu kenako imbani zenera lolowa m'malo ndi njira yachidule ya kiyibodi Ctrl+H kapena kudzera Kunyumba - Pezani ndikusankha - Bwezerani (Kunyumba - Pezani&Sankhani - Bwezerani). Kusagwirizana kumodzi - sizodziwika bwino momwe mungalowe m'munda wapamwamba Kuti mupeze (Pezani chiyani) wathu wosaoneka mzere kuswa khalidwe. alt+Lowani apa, mwatsoka, sikugwiranso ntchito, kukopera chizindikiro ichi mwachindunji kuchokera ku selo ndikuchiyika apa ndikulephera.
Kuphatikiza kungathandize Ctrl+J - ndiye njira ina alt+Lowani m'mabokosi a dialog a Excel kapena magawo olowetsa:
Chonde dziwani kuti mutayika cholozera chonyezimira pamwamba ndikusindikiza Ctrl+J - palibe chomwe chidzawoneke m'munda womwewo. Osachita mantha - izi ndizabwinobwino, chizindikirocho ndi chosawoneka 🙂
Kumunda wapansi M'malo (Sinthani ndi) mwina osalowetsapo kalikonse, kapena kulowa m'malo (ngati tikufuna osati kuchotsa ma hyphens, koma kuwasintha ndi danga kuti mizereyo isagwirizane kukhala imodzi). Ingodinani batani Bwezerani chilichonse (Sinthani Zonse) ndipo ma hyphens athu adzasowa:
Nuance: atatha kuchita m'malo adalowa ndi Ctrl+J khalidwe losaoneka limakhalabe m’munda Kuti mupeze ndipo zitha kusokoneza mtsogolo - musaiwale kuzichotsa poyika cholozera pamalowa ndi kangapo (chifukwa chodalirika) kukanikiza makiyi. Chotsani и Backspace.
Kuchotsa zosweka za mzere ndi chilinganizo
Ngati mukufuna kuthetsa vutoli ndi ma formula, ndiye kuti mutha kugwiritsa ntchito ntchito yomanga Sindikizani (CLEAN), yomwe imatha kuchotsa zilembo zonse zomwe sizingasindikizidwe, kuphatikizapo kuthyola mizere yathu molakwika:
Njirayi, komabe, si yabwino nthawi zonse, chifukwa mizere ikatha ntchitoyi imatha kulumikizidwa pamodzi. Kuti izi zisachitike, simuyenera kungochotsa hyphen, koma m'malo mwake ndi malo (onani ndime yotsatira).
Kusintha zoduka mizere ndi chilinganizo
Ndipo ngati mukufuna osati kuchotsa, koma m'malo alt+Lowani pa, mwachitsanzo, danga, ndiyeno, zomangamanga zina zovuta kwambiri zidzafunika:
Kukhazikitsa hyphen yosaoneka timagwiritsa ntchito ntchitoyi SYMBOL (CHAR), yomwe imatulutsa munthu ndi code yake (10). Ndiyeno ntchito WOLEMBEDWA (MALOWA) amasaka ma hyphens athu mu data yomwe adachokera ndikuyika m'malo mwake ndi mawu ena aliwonse, mwachitsanzo, ndi danga.
Gawani m'mipingo poduka mizere
Zodziwika kwa ambiri komanso chida chothandiza kwambiri Zolemba ndi zigawo kuchokera ku tabu Deta (Deta - Zolemba ku Mizati) imathanso kugwira ntchito bwino ndikuduka mizere ndikugawa mawu kuchokera mu selo imodzi kukhala angapo, ndikuphwanya alt+Lowani. Kuti muchite izi, pa sitepe yachiwiri ya wizard, muyenera kusankha chosiyana cha chikhalidwe cha delimiter Zina (Mwambo) ndikugwiritsa ntchito njira yachidule ya kiyibodi yomwe tikudziwa kale Ctrl+J ngati njira ina alt+Lowani:
Ngati deta yanu ikhoza kukhala ndi mizere ingapo motsatana, ndiye kuti mutha "kuwagwetsa" poyatsa bokosi. Chitani zodulira motsatizana ngati m'modzi (Chitani zinthu zotsatizanatsatizana ngati m'modzi).
Pambuyo pang'anani Ena (Ena) ndikudutsa masitepe onse atatu a wizard, timapeza zotsatira zomwe tikufuna:
Chonde dziwani kuti musanagwire ntchitoyi, ndikofunikira kuyika mizere yokwanira yopanda kanthu kumanja kwa gawo logawanika kuti mawu otulukawo asalembenso mitengo (mitengo) yomwe inali kumanja.
Gawani mizere ndi Alt + Enter kudzera mu Power Query
Ntchito ina yosangalatsa ndikugawa zolemba zamitundu yambiri kuchokera ku selo iliyonse osati mizere, koma mizere:
Zimatenga nthawi yayitali kuti muchite izi pamanja, ndizovuta ndi mafomu, si aliyense amene angalembe macro. Koma pochita, vutoli limapezeka nthawi zambiri kuposa momwe timafunira. Yankho losavuta komanso losavuta ndikugwiritsa ntchito chowonjezera cha Power Query pantchitoyi, yomwe idamangidwa mu Excel kuyambira 2016, ndipo pamatembenuzidwe am'mbuyomu 2010-2013 itha kutsitsidwa kwaulere patsamba la Microsoft.
Kuti mukweze deta yochokera ku Power Query, muyenera kuyisintha kukhala "smart table" yokhala ndi njira yachidule ya kiyibodi. Ctrl+T kapena pa batani Pangani ngati tebulo tsamba Kunyumba (Kunyumba - Pangani Monga Table). Ngati pazifukwa zina simukufuna kapena simungagwiritse ntchito "matebulo anzeru", ndiye kuti mutha kugwira ntchito ndi "opusa". Pankhaniyi, ingosankhani mtundu wapachiyambi ndikuupatsa dzina pa tabu Mafomula - Woyang'anira Dzina - Watsopano (Mafomula - Woyang'anira Dzina - Watsopano).
Pambuyo pake, pa tabu Deta (ngati muli ndi Excel 2016 kapena mtsogolo) kapena pa tabu Kufunsa Mphamvu (ngati muli ndi Excel 2010-2013) mutha dinani batani Kuchokera pa tebulo/ranji (Kuchokera pa Table/Range)kukweza tebulo lathu mu Power Query editor:
Mukatsitsa, sankhani ndime yokhala ndi ma multiline m'maselo ndikusankha lamulo pa Main tabu Gawani Mzere - Wolemba Delimiter (Kunyumba - Gawani Mzere - Wolemba malire):
Mwachidziwikire, Power Query imangozindikira mfundo yogawa ndikulowetsa chizindikirocho #(lf) wosawoneka mzere chakudya chamtundu (lf = chakudya chamzere = chakudya chamzere) m'gawo lolowetsamo olekanitsa. Ngati kuli kofunikira, zilembo zina zitha kusankhidwa pamndandanda wotsikira pansi pawindo, ngati mutayang'ana kaye bokosilo. Gawani ndi zilembo zapadera (Ogawanika ndi zilembo zapadera).
Kotero kuti chirichonse chigawidwe m'mizere, osati mizati - musaiwale kusintha chosankha Mizere (Ndi mizere) mu gulu lazosankha zapamwamba.
Chotsalira ndikudina OK ndi kupeza zomwe mukufuna:
Gome lomalizidwa litha kutsitsidwanso papepala pogwiritsa ntchito lamulo Tsekani ndikutsegula - Tsekani ndikulowetsani… tsamba Kunyumba (Kunyumba - Tsekani & Kwezani - Tsekani & Kwezani ku…).
Ndikofunika kuzindikira kuti mukamagwiritsa ntchito Power Query, muyenera kukumbukira kuti pamene gwero la deta likusintha, zotsatira zake sizisinthidwa zokha, chifukwa. awa si ma formula. Kuti musinthe, muyenera dinani kumanja patebulo lomaliza papepala ndikusankha lamulo Sinthani & Sungani (Bwezeretsani) kapena dinani batani Sinthani Zonse tsamba Deta (Deta - Tsitsani Zonse).
Macro yogawidwa kukhala mizere ndi Alt + Enter
Kuti timalize chithunzichi, tiyeni titchulenso yankho la vuto lapitalo mothandizidwa ndi macro. Tsegulani Visual Basic Editor pogwiritsa ntchito batani la dzina lomwelo pa tabu mapulogalamu (Wolemba Mapulogalamu) kapena njira zazifupi za kiyibodi alt+F11. Pazenera lomwe likuwoneka, ikani gawo latsopano kudzera mu menyu Ikani - Module ndi kukopera khodi ili:
Sub Split_By_Rows() Dim cell As Range, n As Integer Set cell = ActiveCell For i = 1 To Selection.Rows.Count ar = Split(cell, Chr(10)) 'zindikira kuchuluka kwa zidutswa za cell.Offset(1, 0 ).Sinthaninso(n, 1).EntireRow.Lowetsani 'ikani mizere yopanda kanthu m'munsi mwa selo.Resize(n + 1, 1) = WorksheetFunction.Transpose(ar)' lowetsani mu datayo kuchokera ku gulu Set cell = cell.Offset(n + 1, 0) 'sinthani kupita ku selo lotsatira Next I End Sub
Bwererani ku Excel ndikusankha ma cell omwe ali ndi malemba ambiri omwe mukufuna kuwagawa. Kenako gwiritsani ntchito batani Macros tsamba Woyambitsa (Wopanga - Macros) kapena njira yachidule ya kiyibodi alt+F8kuyendetsa macro opangidwa, omwe angakuchitireni ntchito zonse:
Voila! Okonza mapulogalamu ndi anthu aulesi kwambiri omwe angakonde kugwira ntchito molimbika kamodzi kenako osachita kalikonse 🙂
- Kuchotsa mawu osafunika ndi zilembo zina
- Kusintha mawu ndikuvula malo osasweka ndi ntchito ya SUBSTITUTE
- Momwe mungagawire zolemba zomata kukhala zigawo mu Excel