Excel yog lub cuab yeej muaj zog rau kev tshuaj xyuas cov ntaub ntawv, tab sis qee yam xws li flipping hlwb hauv kab lossis kab tuaj yeem ua rau muaj kev cuam tshuam tiag tiag. Muaj txoj hauv kev qeeb qeeb, thiab muaj cov lus qhia ceev kom tig kab, tig kab, lossis hloov kab rau hauv kab hauv vib nas this xwb.
Nyob rau hauv kab lus hauv qab no, koj yuav kawm yuav ua li cas tig ib kab tag nrho ntawm hauv qab mus rau sab saum toj, sib pauv kab ntawm sab xis mus rau sab laug, thiab yuav ua li cas sib pauv kab nrog txhua kab.
Cov lus qhia no siv rau Microsoft Excel 2010, 1013, 2016, 2019, thiab Excel rau Microsoft 365.
Yuav ua li cas tig kab kab hauv Excel
Txawm hais tias muaj ntau ntau txoj kev xaiv hauv Excel rau kev teeb tsa cov lej ntawm cov tsiaj ntawv, tsis muaj qhov kev xaiv tsim los yooj yim los hloov cov ntaub ntawv sib txawv, xws li cov npe.
Tsis tau txhais tias nws yog imposslble. Nws tsuas yog txhais tau tias koj yuav tsum ua raws li ob peb kauj ruam ntxiv txhawm rau siv Excel cov yam ntxwv los hloov kab ke.
- Right-click kem A, thiab xaiv Insert txhawm rau ntxiv ib kem tshiab rau sab laug ntawm tus neeg muag khoom koj xav txheeb.
-
Hom 1 hauv thawj lub xov tooj (kab 2). Hom 2 rau hauv lub xov tooj thib ob (kab 3).
- Tuav lub Cntrl thiab tso tus nas rau ntawm kaum sab xis ntawm lub xov tooj nrog tus lej 2. Tus cursor yuav hloov mus rau ob kab kab rov tav. Ob-sab laug nyem tus nas kom pib-sau cov seem ntawm kab mus rau tus neeg muag khoom kawg lub npe. Qhov no yuav autofill rau seem ntawm kab nrog cov lej ntawm 3 txog 8.
- Highlight tag nrho lub rooj.
- In the Home zaub mov, xaiv Sort & Lim los ntawm ribbon. Xaiv Custom Sort.
- Set Sort By to Kab A, Sort On rau Cell Nqis, and Order to Larest to Smallest. Nyem OK.
Qhov no yuav tsis tsuas yog tig tag nrho kem ntawm hauv qab mus rau sab saum toj, tab sis nws yuav ua kom tag nrho cov ntaub ntawv nyob rau hauv seem ntawm daim ntawv sib pauv kom phim tus neeg muag khoom lub npe raug.
Tam sim no koj tuaj yeem nyem rau kab A los xaiv nws, nyem rau kab A, thiab xaiv Rho tawm kom tshem tawm kab lej.
Tam sim no koj muaj tib lub rooj qub uas koj pib nrog, tab sis nrog tag nrho kem thiab cov ntaub ntawv rov qab mus rau saum.
How to Swap Kab Cells hauv Excel
Yuav ua li cas yog tias koj xav pom cov ntaub ntawv muag khoom txij lub Kaum Ob Hlis mus txog Lub Ib Hlis, tsis yog Lub Ib Hlis txog Lub Kaum Ob Hlis?
Koj tuaj yeem siv tib txoj hauv kev raws li saum toj no rau flipping kab, nrog me ntsis variation.
Ua ntej, ntxiv ib kab tshiab nyob rau hauv lub header, thiab suav cov cell ntawm 1 (hauv lub Ib Hlis) mus rau 12 (hauv lub Kaum Ob Hlis).
Tam sim no ua raws tib cov kauj ruam txhawm rau txheeb lub rooj, tab sis lub sijhawm no koj yuav txheeb los ntawm kab es tsis yog los ntawm kab.
- Highlight tsuas yog kab A txog M.
-
In the Home zaub mov, xaiv Sort & Lim los ntawm ribbon. Xaiv Custom Sort.
- Click Options, thiab xaiv Saib laug mus rau sab xis.
- Set Sort By to Row 2, Sort On to Cell Values, and Order to Larest to Smallest. Nyem OK.
Tam sim no koj yuav pom tias koj thawj kab tag nrho yog nyob nrog cov hlwb hauv kab ntawv header flipped. Txij li thaum koj tau hais txog txhua kab uas muaj cov ntaub ntawv, tag nrho cov ntaub ntawv kab tau flipped ib yam nkaus, kom txhua yam kab mus zoo kawg nkaus.
Tam sim no nyem rau ntawm kab 2 thiab tshem tawm cov lej lej.
Swap Tsuas yog ob kab lossis kab
Yog tias koj xav tig mus rau kab los yog kab sib txuas, muaj qhov ua kom yuam kev nas muaj txiaj ntsig koj tuaj yeem siv hauv Excel kom ua tiav qhov no.
Hauv qhov piv txwv no, koj tuaj yeem sib pauv kab Julie Moore nrog John Hudson kab nrog ob peb nias ntawm tus nas.
Ntawm no yog koj ua li cas:
- Highlight tag nrho kab nrog Julie Moore hauv kab A.
- Tuav lub Shift tus yuam sij, thiab txav tus nas cursor mus rau sab saum toj ntawm Julie Moore cell. Tus cursor nas yuav hloov mus rau crosshairs.
- Nrog rau Shift tus yuam sij tseem tuav cia, rub tus nas mus rau sab sauv ntawm John Hudson ntawm tes kom txog rau thaum kab saum toj ntawm kab ntawv tseem ceeb nrog kab tsaus.
- Thaum koj tso tus nas khawm sab laug, ob kab tag nrho yuav sib pauv.
Yog tias koj xav hloov cov kab es tsis yog kab, koj tuaj yeem ua kom pom ib kem, thiab ua raws li cov txheej txheem tib yam.
Qhov txawv tsuas yog koj rub tus nas cursor rau sab kom txog rau thaum kab tom qab kab koj xav pauv nrog cov ntsiab lus tsaus nti.
Koj tuaj yeem sib pauv ntau kab lossis kab uas siv tib cov txheej txheem no. Koj tsuas yog yuav tsum tau qhia ntau kab lossis kab hauv thawj kauj ruam, thiab tom qab ntawd ua raws li cov txheej txheem tib yam.
How to Swap Columns and Rows
Yuav ua li cas yog tias koj xav hloov tag nrho cov kab ntawv hauv kab nrog rau tag nrho cov neeg muag khoom, thaum tswj kev ncaj ncees ntawm cov ntaub ntawv hauv daim ntawv tshaj tawm?
Cov neeg feem coob xaiv ua qhov no manually, tsis paub tias Excel muaj qhov ua haujlwm tsim hu ua "transpose" uas yuav ua rau koj.
Koj yuav xav tau thaj chaw uas koj tuaj yeem tsim koj cov ntaub ntawv "transposed" tshiab, yog li tsim ib daim ntawv tshiab hauv koj daim ntawv teev npe hu ua "Kev Muag Khoom Los Ntawm Lub Hlis".
- Highlight tag nrho cov cell rau koj tag nrho cov ntaub ntawv muag khoom. Nias Cntrl-C kom luam tag nrho ntau yam.
- Nyem hauv daim ntawv tshiab koj tsim. Txoj nyem hu rau A1, thiab xaiv qhov Transpose kev xaiv los ntawm Muab tshuaj txhuam.
- Tam sim no koj yuav pom koj daim ntawv tshaj tawm thawj zaug, tab sis nrog cov kab ntawv header swapped nrog thawj kab, thiab tag nrho cov ntaub ntawv nyob rau hauv qhov ntau tau teeb tsa kom raug.
Siv Excel's transpose feature tuaj yeem txuag koj cov sij hawm ntawm phau ntawv kho ua haujlwm thaum koj xav rov teeb tsa koj cov ntawv nthuav dav thiab saib cov ntaub ntawv sib txawv.
Siv VBA los pauv kab lossis kab (Advanced)
VBA yog lub zog muaj zog hauv Excel. Koj tuaj yeem ua tiav txhua txoj haujlwm saum toj no nrog VBA code yooj yim heev.
Tom qab ntawd hloov koj txoj haujlwm VBA rau hauv Excel kev cai uas koj tuaj yeem hu thaum twg koj xav pauv kab lossis kab.
Flip kab lossis kab
Txhawm rau rov teeb tsa tag nrho kab ntawm sab laug mus rau sab xis, lossis ib kab ntawm sab saum toj mus rau hauv qab, koj tuaj yeem tsim Flip_Columns() lossis Flip_Rows() ua haujlwm kom ua tiav qhov no.
Tau tsim cov cai no, xaiv qhov Developer ntawv qhia zaub mov thiab xaiv View Code.
Yog tus tsim tawm tsis muaj npe hauv cov ntawv qhia zaub mov, koj tuaj yeem ntxiv nws. Nyem rau File, nyem Options, thiab xaiv Customize Ribbon Hauv qhov rai no, nrhiav Developer ntawm sab laug pane thiab Ntxiv rau sab xis. Nyem OK thiab qhov kev xaiv tsim tawm yuav tshwm sim.
Txhawm rau tig kab ntawm kab xaiv hauv koj daim ntawv, koj tuaj yeem siv VBA code hauv qab no:
Sub Flip_Rows()
Dim vTop As Variant
Dim vEnd As Variant
Dim iStart As Integer
Dim iEnd As Integer
Application. ScreenUpdating=False
iStart=1
iEnd=Selection. Rows. Count
Do Thaum iStart < iEnd
vTop=Selection. Rows(iStart) vEnd=Selection. Rows(iEnd)
Selection. Rows(iEnd)=vTop
Selection. Rows(iStart)=vEnd
iStart=iStart + 1 iEnd=iEnd - 1
Loop
Application. ScreenUpdating=True
End Sub
Koj tuaj yeem ua tib yam nrog cov hlwb hauv ib kab los ntawm flipping tag nrho cov kab hauv kab uas siv cov cai hauv qab no.
Sub Flip_Columns()
Dim vLeft As Variant
Dim vRight As Variant
Dim iStart As Integer
Dim iEnd As Integer Application. ScreenUpdating=False
iStart=1
iEnd=Selection. Columns. Count
Do Thaum iStart < iEnd
vTop=Selection. Columns(iStart) vEnd=Selection. Columns(iEnd)
Selection. Columns(iEnd)=vRight
Selection. Columns(iStart)=vLeft
iStart=iStart + 1
iEnd=iEnd - 1
Loop
Application. ScreenUpdating=True
End Sub
Koj tuaj yeem khiav ib qho ntawm cov ntawv VBA no los ntawm kev xaiv kab lossis kab koj xav thim rov qab, nkag mus rau hauv lub qhov rais code editor, thiab nyem rau ntawm lub cim khiav hauv cov ntawv qhia zaub mov.
Cov VBA cov ntawv no yog txoj hauv kev nrawm rau thim rov qab cov hlwb hauv kab lossis kab nrog tsuas yog nyem ib zaug, tab sis lawv yuav tsis tig lub hlwb hauv cov ntaub ntawv muaj feem cuam tshuam, yog li siv cov ntawv no tsuas yog thaum koj xav tig ib qho. kab los yog kab thiab tsis muaj dab tsi ntxiv.
Swap Ob Kab lossis Kab
Koj tuaj yeem pauv ob qhov txiaj ntsig ntawm ib daim ntawv los ntawm kev xaiv ob qhov txiaj ntsig thiab khiav VBA code hauv qab no.
Sub Swap()
For i=1 To Selection. Areas(1). Count
temp=Selection. Areas(1)(i)
Selection. Areas(1)(i)=Selection. Areas(2)(i)
Selection. Areas(2)(i)=temp
Next i
End Sub
VBA code no yuav pauv ob lub hlwb, txawm tias lawv nyob ib sab los yog ib qho ntawm sab saum toj. Cia li nco ntsoov koj tsuas yog pauv ob lub hlwb nrog cov lej no.
Txhob Cia Siab Tag Nrho
Nws muaj peev xwm siv VBA los xaiv los ntawm ib daim ntawv (ib yam li hauv cov ntawv sau saum toj no), hloov qhov ntau, thiab tom qab ntawd muab tso rau lwm qhov (lossis hauv daim ntawv sib txawv).
Muaj ib txoj hauv kev hauv VBA rau lub hom phiaj no:
Teem DestRange=Application. WorksheetFunction. Transpose(SelectedRange)
Txawm li cas los xij, ua qhov no hauv VBA yog qhov ua haujlwm ntau dua li nws tsim nyog, txij li thaum koj tau pom ua ntej hauv tsab xov xwm no transposing tag nrho cov ntau yam tsis muaj ntau tshaj li luam thiab pasting qhov ntau yam nrog ob peb clicks ntawm tus nas.
Flipping kab thiab kab hauv Excel
Raws li koj tuaj yeem pom, flipping kab thiab kab, swapping hlwb, lossis hloov tag nrho cov ntau yam yooj yim heev hauv Excel.
Thaum koj paub tias koj xav kom koj cov ntaub ntawv zoo li cas, koj tuaj yeem xaiv qhov kev xaiv zoo tshaj plaws thiab tig los yog hloov cov hlwb hauv ob peb kauj ruam yooj yim xwb.
