Yuav ua li cas los tsim ib daim ntawv hauv Excel

Feem ntau, cov neeg siv ntawm cov ntawv sau ntawv tau ntsib cov haujlwm xws li tsim ib daim ntawv tshwj xeeb rau kev nkag mus rau cov ntaub ntawv tsim nyog. Cov ntawv yog ib daim ntawv uas pab txhawb txoj kev ua kom tiav cov ntaub ntawv nthuav dav. Tus editor muaj cov cuab yeej sib xyaw ua ke uas tso cai rau koj los sau cov ntawv ua haujlwm li no. Tsis tas li ntawd, tus neeg siv ntawm qhov kev pab cuam, siv lub macro, tuaj yeem tsim nws tus kheej version ntawm daim ntawv, hloov mus rau ntau yam dej num. Hauv tsab xov xwm, peb yuav txiav txim siab nthuav dav ntau txoj hauv kev uas tso cai rau koj los tsim ib daim ntawv hauv daim ntawv nthuav dav.

Siv cov cuab yeej sau rau hauv daim ntawv teev cov editor

Daim ntawv sau yog ib qho tshwj xeeb nrog cov teb uas nws cov npe sib raug rau cov npe ntawm cov kab ntawm cov phaj tau sau. Nws yog ib qho tsim nyog yuav tsum tau tsav cov ntaub ntawv mus rau hauv cov teb, uas tam sim ntawd yuav muab tso rau hauv ib txoj kab tshiab hauv cheeb tsam xaiv. Cov duab tshwj xeeb no tuaj yeem siv los ua ib qho kev sib koom ua ke ntawm ib leeg lossis tuaj yeem pom ntawm daim ntawv ua haujlwm nws tus kheej raws li ntau yam. Cia peb txheeb xyuas txhua qhov kev hloov pauv kom ntxaws ntxiv.

Thawj txoj kev: integrated element rau nkag mus rau cov ntaub ntawv

Cia peb pib xav txog yuav ua li cas siv daim ntawv sib koom ua ke los ntxiv cov ntaub ntawv rau tus kws kho cov ntaub ntawv nthuav dav. Cov lus qhia ntxaws ntxaws zoo li no:

1. Nco ntsoov tias thaum xub thawj, lub cim uas suav nrog daim ntawv no tau muab zais. Peb yuav tsum ua cov txheej txheem ua kom lub cuab yeej. Peb tsiv mus rau "Cov ntaub ntawv" submenu, nyob rau sab laug sab sauv ntawm cov ntaub ntawv editor interface. Peb pom ntawm no ib lub caij uas muaj lub npe "Parameters", thiab nyem rau ntawm nws nrog rau sab laug nas khawm.

2. Lub qhov rais hu ua "Excel Options" tshwm rau ntawm cov zaub. Peb txav mus rau ntu ntu "Quick Access Vaj Huam Sib Luag". Muaj ntau yam kev teeb tsa ntawm no. Nyob rau sab laug yog cov cuab yeej tshwj xeeb uas tuaj yeem qhib tau rau hauv toolbar, thiab sab xis yog twb muaj cov cuab yeej. Nthuav cov npe nyob ib sab ntawm cov ntawv sau "Xaiv cov lus txib los ntawm:" thiab xaiv lub caij "Commands on the ribbon" nrog rau sab laug nas khawm. Nyob rau hauv cov npe ntawm cov lus txib tso tawm nyob rau hauv cov ntawv sau, peb tab tom nrhiav rau cov khoom "Form ..." thiab xaiv nws. Nyem "Ntxiv".

3. Peb nyem rau ntawm lub pob "OK".

4. Peb tau qhib cov cuab yeej no rau ntawm ib qho tshwj xeeb ribbon.

5. Tam sim no peb yuav tsum pib tsim lub header ntawm lub phaj, thiab tom qab ntawd nkag mus rau qee qhov ntsuas rau hauv nws. Peb lub rooj yuav muaj 4 kab. Peb tsav hauv cov npe.

6. Peb kuj tau tsav qee tus nqi rau hauv kab thib 1 ntawm peb lub phaj.

7. Peb xaiv txhua qhov chaw ntawm lub phaj npaj thiab nyem rau ntawm "Form ..." ntsiab nyob rau ntawm lub cuab yeej ribbon.

8. Cov cuab yeej nqis qhov rais qhib. Ntawm no yog cov kab sib txuas rau cov npe ntawm cov kab ntawm cov phaj.

Nws yog ib qho tsim nyog sau cia tias thawj kab twb tau sau nrog cov ntaub ntawv, txij li thaum peb tau nkag mus rau lawv hauv daim ntawv ua haujlwm peb tus kheej.

9. Peb tsav tsheb hauv cov ntsuas uas peb xav tias tsim nyog hauv cov kab txuas ntxiv. Nyem rau ntawm "Add" khawm.

10. Cov ntsuas nkag tau cia li hloov mus rau 1 kab ntawm lub rooj, thiab hauv daim ntawv nws tus kheej, kev hloov pauv tau ua rau lwm qhov thaiv ntawm thaj chaw sib raug rau kab thib 2 ntawm lub rooj.

11. Peb sau cov cuab yeej qhov rais nrog cov ntsuas uas peb xav pom hauv kab thib 2 ntawm lub phaj. Peb nyem "Add".

12. Cov ntsuas nkag tau cia li hloov mus rau kab thib 2 ntawm lub phaj, thiab hauv daim ntawv nws tus kheej, kev hloov pauv tau ua rau lwm qhov thaiv ntawm thaj chaw sib raug rau kab thib 3 ntawm lub phaj.

13. Los ntawm txoj kev zoo sib xws, peb sau rau hauv lub phaj nrog tag nrho cov ntsuas tsim nyog.

14. Siv cov "Tom ntej" thiab "Rov qab" khawm, koj tuaj yeem taug kev los ntawm cov ntsuas tau nkag mus dhau los. Lwm txoj hauv kev yog scroll bar.

15. Yog xav tau, koj tuaj yeem hloov kho txhua qhov ntsuas hauv lub rooj los ntawm kev kho lawv hauv daim ntawv nws tus kheej. Txhawm rau txuag koj cov kev hloov pauv, nyem rau ntawm "Add".

16. Peb pom tias tag nrho cov kev hloov kho qhov tseem ceeb tau tshwm sim hauv lub phaj nws tus kheej.

17. Siv lub pob "Delete", koj tuaj yeem siv qhov kev tshem tawm ntawm ib kab tshwj xeeb.

18. Tom qab txhaj, lub qhov rais ceeb toom tshwj xeeb yuav tshwm sim, uas qhia tias cov kab xaiv yuav raug muab tshem tawm. Koj yuav tsum nyem "OK".

19. Cov kab tau raug tshem tawm ntawm lub rooj. Tom qab tag nrho cov txheej txheem ua tiav, nyem rau ntawm "Kaw" lub caij.

20. Tsis tas li ntawd, koj tuaj yeem tsim nws kom lub phaj tau txais qhov zoo nkauj zoo nkauj.

Txoj kev thib ob: sau cov ntaub ntawv nrog cov ntaub ntawv los ntawm cov ntsiav tshuaj

Piv txwv li, peb muaj ib lub phaj uas muaj cov ntaub ntawv ntawm kev them nyiaj.

Lub hom phiaj: sau rau hauv daim ntawv nrog cov ntaub ntawv no kom nws thiaj li yooj yim thiab raug luam tawm. Cov lus qhia ntxaws ntxaws zoo li no:

1. Ntawm ib daim ntawv sib cais ntawm daim ntawv, peb tsim ib daim ntawv khoob.

Nws yog tsim nyog sau cia tias qhov zoo li ntawm daim ntawv nws tus kheej tuaj yeem tsim los ntawm nws tus kheej lossis koj tuaj yeem rub tawm cov ntawv npaj tau los ntawm ntau qhov chaw.

2. Ua ntej koj nqa cov ntaub ntawv los ntawm lub phaj, koj yuav tsum hloov nws me ntsis. Peb yuav tsum ntxiv ib kem khoob rau sab laug ntawm lub rooj qub. Ntawm no ib lub cim yuav muab tso rau ntawm kab uas peb npaj yuav ntxiv rau daim ntawv nws tus kheej.

3. Tam sim no peb yuav tsum tau siv txoj hlua khi ntawm lub phaj thiab daim ntawv. Txhawm rau ua qhov no, peb xav tau VLOOKUP tus neeg teb xov tooj. Peb siv cov qauv no: =VLOOKUP(“x”,Data!A2:G16).

4. Yog tias koj muab lub cim rau ntawm ob peb kab, ces tus neeg teb xov tooj VLOOKUP tsuas yog siv qhov ntsuas thib 1 pom. Txhawm rau daws qhov teeb meem no, koj yuav tsum tau nyem rau ntawm lub icon ntawm daim ntawv nrog lub hauv paus phaj thiab nyem rau ntawm "Cov ntawv nyeem" keeb. Hauv qhov rai uas tshwm, sau cov lej hauv qab no:

Private Sub Worksheet_Change (ByVal Target As Range)

Dim r As Long

Dim str As String

Yog Target.Count> 1 Ces Tawm Sub

Yog Target.Column = 1 Ces

str = Target.Value

Application.EnableEvents = False

r = Cells(Rows.Count, 2).End(xlUp).Row

Range(«A2:A» & r).ClearContents

Target.Value = str

Xaus Yog

Application.EnableEvents = Muaj tseeb

End Sub

5. Qhov macro no tsis tso cai rau koj nkag mus rau ntau tshaj ib daim ntawv lo hauv thawj kab.

Cov lus xaus thiab cov lus xaus txog kev tsim daim ntawv.

Peb pom tias muaj ntau ntau hom kev tsim ib daim ntawv nyob rau hauv ib tug spreadsheet editor. Koj tuaj yeem siv cov ntaub ntawv tshwj xeeb nyob rau ntawm daim kab xev, lossis koj tuaj yeem siv VLOOKUP tus neeg teb xov tooj hloov cov ntaub ntawv los ntawm lub phaj mus rau daim ntawv. Tsis tas li ntawd, cov macros tshwj xeeb yog siv.