txheem
Kab tawg nyob rau hauv tib lub cell, ntxiv siv cov keyboard shortcut Alt+Sau yog ib qho zoo heev thiab ntau yam. Qee zaum lawv tau tsim los ntawm cov neeg siv lawv tus kheej los ntxiv kev zoo nkauj rau cov ntawv ntev. Qee lub sij hawm xws li kev hloov pauv tau ntxiv rau thaum tshem tawm cov ntaub ntawv los ntawm txhua qhov haujlwm ua haujlwm (nyob zoo 1C, SAP, thiab lwm yam) qhov teeb meem yog tias tom qab ntawd koj yuav tsum tsis txhob qhuas cov rooj no nkaus xwb, tab sis ua haujlwm nrog lawv - thiab tom qab ntawd cov cim tsis pom kev hloov pauv tuaj yeem ua tau. teeb meem. Thiab lawv yuav tsis dhau los - yog tias koj paub yuav ua li cas rau lawv kom raug.
Cia peb saib ntawm qhov teeb meem no kom ntxaws ntxiv.
Tshem tawm cov kab tawg los ntawm kev hloov
Yog tias peb xav tau tshem tawm cov hyphens, ces thawj qhov uas feem ntau los rau hauv siab yog cov txheej txheem "nrhiav thiab hloov" classic. Xaiv cov ntawv nyeem thiab tom qab ntawd hu rau lub qhov rais hloov nrog lub keyboard shortcut Ctrl+H los ntawm Tsev – Nrhiav thiab xaiv – Hloov (Lub Tsev - Nrhiav & Xaiv - Hloov). Ib qho kev tsis sib haum xeeb - nws tsis paub meej tias yuav nkag mus rau hauv qhov chaw sab saum toj Yuav kom pom (Nrhiav dab tsi) peb invisible kab break cim. Alt+Sau ntawm no, hmoov tsis, nws tsis ua haujlwm lawm, luam cov cim no ncaj qha los ntawm lub xov tooj ntawm tes thiab pasting nws ntawm no kuj ua tsis tiav.
Kev sib xyaw ua ke yuav pab tau Ctrl+J – uas yog lwm txoj Alt+Sau hauv Excel dialog boxes lossis input teb:
Thov nco ntsoov tias tom qab koj muab tus cursor blinking rau saum toj thiab nias Ctrl+J - tsis muaj dab tsi yuav tshwm sim hauv daim teb nws tus kheej. Tsis txhob ntshai - qhov no yog qhov qub, lub cim tsis pom 🙂
Mus rau hauv qab teb Hloov chaw (Hloov nrog) tsis txhob nkag mus rau ib yam dab tsi, lossis nkag mus rau qhov chaw (yog tias peb xav kom tsis txhob tshem cov hyphens xwb, tab sis hloov lawv nrog qhov chaw kom cov kab tsis lo ua ke rau hauv ib qho tag nrho). Tsuas yog nias lub khawm Hloov txhua yam (Hloov tag nrho) thiab peb cov hyphens yuav ploj mus:
nuance: tom qab ua qhov kev hloov pauv nkag nrog Ctrl+J invisible cim tseem nyob rau hauv daim teb Yuav kom pom thiab tej zaum yuav cuam tshuam rau yav tom ntej - tsis txhob hnov qab tshem nws los ntawm kev tso tus cursor rau hauv daim teb no thiab ob peb zaug (rau kev ntseeg siab) nias cov yuam sij Rho tawm и Backspace.
Tshem tawm cov kab tawg nrog cov qauv
Yog tias koj xav tau los daws qhov teeb meem nrog cov qauv, ces koj tuaj yeem siv cov kev ua haujlwm built-in SAU (CLEAN), uas tuaj yeem tshem tawm cov ntawv ntawm tag nrho cov cim tsis tuaj yeem luam tawm, suav nrog peb cov kab mob tsis zoo:
Qhov kev xaiv no, txawm li cas los xij, tsis yog ib txwm yooj yim, vim tias cov kab tom qab ua haujlwm no tuaj yeem ua ke ua ke. Txhawm rau tiv thaiv qhov no tshwm sim, koj yuav tsum tsis txhob tshem tawm cov hyphen xwb, tab sis hloov nws nrog qhov chaw (saib kab lus tom ntej).
Hloov kab tawg nrog cov qauv
Thiab yog tias koj xav tsis yog tsuas yog rho tawm, tab sis hloov Alt+Sau nyob rau, piv txwv li, ib qho chaw, ces lwm, me ntsis ntxiv complex yuav tsum tau:
Txhawm rau teeb tsa qhov tsis pom hyphen peb siv txoj haujlwm SYMBOL (CHAR), uas outputs ib tug cim los ntawm nws cov code (10). Thiab tom qab ntawd ua haujlwm RAWS LI (SIB NTAUS) tshawb xyuas peb cov hyphens hauv cov ntaub ntawv hauv qhov chaw thiab hloov lawv nrog lwm cov ntawv nyeem, piv txwv li, nrog qhov chaw.
Muab faib rau hauv kab los ntawm kab
Paub ntau thiab siv tau zoo heev Cov ntawv nyeem los ntawm kab los ntawm tab Cov ntaub ntawv (Cov ntaub ntawv - Cov ntawv nyeem rau kab) tuaj yeem ua haujlwm tau zoo nrog cov kab so thiab faib cov ntawv los ntawm ib lub xov tooj mus rau ntau, tawg los ntawm Alt+Sau. Ua li no, nyob rau theem thib ob ntawm tus wizard, koj yuav tsum xaiv qhov sib txawv ntawm tus cwj pwm kev cai delimiter. lwm yam (Kev Cai) thiab siv cov keyboard shortcut peb twb paub lawm Ctrl+J raws li lwm txoj Alt+Sau:
Yog tias koj cov ntaub ntawv tuaj yeem muaj ntau txoj kab tawg ua ke, tom qab ntawd koj tuaj yeem "yob" lawv los ntawm kev qhib lub checkbox Kho cov kab sib law liag ua ib qho (Kho qhov sib law liag raws li ib qho).
Tom qab txhaj rau Tom ntej no (Tom ntej) thiab mus dhau tag nrho peb cov kauj ruam ntawm tus wizard, peb tau txais cov txiaj ntsig xav tau:
Thov nco ntsoov tias ua ntej ua qhov kev ua haujlwm no, nws yuav tsum tau ntxig ib tus naj npawb ntawm cov khoob khoob mus rau sab xis ntawm cov kab sib cais kom cov ntawv tau txais tsis sau cov nqi (tus nqi) uas nyob rau sab xis.
Faib rau hauv kab los ntawm Alt + Sau los ntawm Power Query
Lwm txoj haujlwm nthuav yog faib cov ntawv sau ntau ntau los ntawm txhua lub xov tooj tsis mus rau hauv kab, tab sis mus rau hauv kab:
Nws siv sijhawm ntev los ua qhov no manually, nws nyuaj nrog cov qauv, tsis yog txhua tus tuaj yeem sau cov macro. Tab sis hauv kev xyaum, qhov teeb meem no tshwm sim ntau dua li peb xav. Qhov kev daws teeb meem yooj yim thiab yooj yim tshaj plaws yog siv Power Query add-in rau txoj haujlwm no, uas tau tsim rau hauv Excel txij li xyoo 2016, thiab rau cov ntawv dhau los 2010-2013 nws tuaj yeem rub tawm dawb los ntawm Microsoft lub vev xaib.
Txhawm rau thauj cov ntaub ntawv los rau hauv Power Query, koj yuav tsum xub hloov nws mus rau "smart table" nrog keyboard shortcut Ctrl+T los yog khawm Format ua ib lub rooj tab Tsev (Lub Tsev — Format as Table). Yog tias vim li cas koj tsis xav tau lossis siv tsis tau "cov ntxhuav ntse", ces koj tuaj yeem ua haujlwm nrog cov " ruam". Hauv qhov no, tsuas yog xaiv cov thawj ntau yam thiab muab nws lub npe ntawm lub tab Formulas - Tus Thawj Tswj Lub Npe - Tshiab (Cov qauv - Tus Thawj Saib Xyuas Lub Npe - Tshiab).
Tom qab ntawd, ntawm lub tab Cov ntaub ntawv (yog tias koj muaj Excel 2016 lossis tom qab) lossis hauv tab Lub Zog Lus Nug (yog tias koj muaj Excel 2010-2013) koj tuaj yeem nyem rau ntawm lub pob Los ntawm lub rooj / ntau yam (Los ntawm Table/Range)thauj peb lub rooj rau hauv Power Query editor:
Tom qab thauj khoom, xaiv kab nrog ntau kab ntawv hauv lub hlwb thiab xaiv cov lus txib ntawm Main tab Split Kem – Los ntawm Delimiter (Lub Tsev - Split Kem - Los ntawm delimiter):
Feem ntau yuav, Power Query yuav cia li paub txog lub hauv paus ntsiab lus ntawm kev faib thiab hloov lub cim nws tus kheej #(lf) invisible kab feed character (lf = kab pub = kab pub) nyob rau hauv lub separator input teb. Yog tias tsim nyog, lwm cov cim tuaj yeem raug xaiv los ntawm cov npe poob rau hauv qab ntawm lub qhov rais, yog tias koj xub kos lub thawv Sib cais nrog cov cim tshwj xeeb (Pib los ntawm cov cim tshwj xeeb).
Yog li ntawd txhua yam muab faib ua kab, thiab tsis yog kab - tsis txhob hnov qab hloov lub selector Loj (Los ntawm kab) nyob rau hauv cov kev xaiv qib siab pab pawg.
Txhua yam uas tseem tshuav yog nyem rau OK thiab tau txais yam koj xav tau:
Lub rooj tiav tuaj yeem rub rov qab rau hauv daim ntawv siv cov lus txib Kaw thiab thauj khoom - Kaw thiab thauj hauv… tab Tsev (Lub Tsev - Kaw & Load - Kaw & Load rau…).
Nws yog ib qho tseem ceeb uas yuav tsum nco ntsoov tias thaum siv Power Query, koj yuav tsum nco ntsoov tias thaum cov ntaub ntawv hloov pauv, cov txiaj ntsig tsis tau hloov kho, vim. cov no tsis yog cov qauv. Txhawm rau hloov kho, koj yuav tsum right-click ntawm lub rooj zaum kawg ntawm daim ntawv thiab xaiv cov lus txib Hloov tshiab & Txuag (Refresh) los yog nias lub pob Hloov Tag Nrho tab Cov ntaub ntawv (Cov ntaub ntawv - Refresh All).
Macro rau kev faib ua kab los ntawm Alt + Nkag mus
Txhawm rau ua kom tiav daim duab, peb kuj hais txog kev daws teeb meem yav dhau los nrog kev pab ntawm macro. Qhib Visual Basic Editor siv lub pob ntawm tib lub npe ntawm lub tab tsim tawm (Tus tsim tawm) los yog keyboard shortcuts Alt+F11. Nyob rau hauv lub qhov rais uas tshwm, ntxig ib tug tshiab module los ntawm cov ntawv qhia zaub mov Ntxig - Module thiab luam cov cai hauv qab no rau hauv:
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)) ' txiav txim seb tus naj npawb ntawm fragments cell.Offset(1, 0 ).Resize(n, 1).EntireRow.Insert 'insert empty rows below cell.Resize(n + 1, 1) = WorksheetFunction.Transpose(ar) ' nkag rau hauv lawv cov ntaub ntawv los ntawm array Teeb cell = cell.Offset(n + 1, 0) 'hloov mus rau lub xov tooj tom ntej Tom ntej i End Sub
Rov qab mus rau Excel thiab xaiv cov hlwb nrog cov ntawv sau ntau kab uas koj xav faib. Tom qab ntawd siv lub pob Ntsig tab tsim tawm (Tus tsim tawm - Macros) los yog keyboard shortcut Alt+F8khiav lub macro tsim, uas yuav ua txhua yam haujlwm rau koj:
Voila! Programmers yog, qhov tseeb, tsuas yog cov neeg tub nkeeg heev uas xav ua haujlwm nyuaj ib zaug thiab tom qab ntawd tsis ua dab tsi 🙂
- Ntxuav cov ntawv los ntawm cov khib nyiab thiab cov cim ntxiv
- Hloov cov ntawv nyeem thiab stripping qhov chaw tsis tawg nrog SUBSTITUTE muaj nuj nqi
- Yuav ua li cas faib cov ntawv nplaum rau hauv ntu hauv Excel