txheem
Formulation ntawm qhov teeb meem
Peb muaj cov ntaub ntawv (cov npe, rooj - hu nws qhov koj xav tau) nrog cov ntaub ntawv ntawm kev them nyiaj ntawm ib daim ntawv Cov ntaub ntawv:
Ua haujlwm: sai sai luam tawm ib daim ntawv txais nyiaj ntsuab (kev them nqi, daim ntawv them nqi ...) rau txhua qhov kev xav tau xaiv los ntawm daim ntawv teev npe no. Mus!
Kauj ruam 1. Tsim ib daim ntawv
Nyob rau lwm daim ntawv ntawm phau ntawv (cia hu rau daim ntawv no daim ntawv) tsim ib daim ntawv khoob. Koj tuaj yeem ua koj tus kheej, koj tuaj yeem siv cov ntawv npaj ua tiav, coj mus rau hauv lub vev xaib ntawm Chief Accountant magazine lossis Microsoft lub vev xaib. Kuv tau txais qee yam zoo li no:
Hauv cov hlwb khoob (Account, Nyiaj, Tau txais los ntawm thiab lwm yam) yuav tau txais cov ntaub ntawv los ntawm lub rooj them nyiaj los ntawm lwm daim ntawv - me ntsis tom qab peb yuav daws qhov no.
Kauj Ruam 2: Npaj lub rooj them nyiaj
Ua ntej muab cov ntaub ntawv los ntawm lub rooj rau peb daim ntawv, lub rooj yuav tsum tau hloov kho me ntsis. Namely, ntxig ib kem khoob rau sab laug ntawm lub rooj. Peb yuav siv los sau ib daim ntawv lo (cia nws yog tsab ntawv Askiv "x") rov qab cov kab uas peb xav ntxiv cov ntaub ntawv rau hauv daim ntawv:
Kauj ruam 3. Txuas lub rooj thiab daim ntawv
Rau kev sib txuas lus, peb siv lub luag haujlwm VPR(VLOOKUP) - Koj tuaj yeem nyeem ntxiv txog nws ntawm no. Hauv peb qhov xwm txheej, txhawm rau txhawm rau ntxig tus lej ntawm kev them nyiaj cim "x" los ntawm Daim Ntawv Teev Npe rau hauv cell F9 ntawm daim ntawv, koj yuav tsum nkag mus rau cov qauv hauv qab no hauv cell F9:
=VLOOKUP(“x”,Data!A2:G16)
=VLOOKUP(“x”;Data!B2:G16;2;0)
Cov. txhais ua "kom nkag siab", lub luag haujlwm yuav tsum pom nyob rau hauv qhov ntau A2: G16 ntawm daim ntawv teev cov ntaub ntawv ib kab pib nrog tus cim "x" thiab muab peb cov ntsiab lus ntawm kab thib ob ntawm kab no, piv txwv li tus lej them nyiaj.
Tag nrho lwm cov hlwb ntawm daim ntawv tau sau rau hauv tib txoj kev - tsuas yog cov kab zauv hloov pauv hauv cov qauv.
Txhawm rau tso cov nyiaj hauv cov lus, kuv siv cov haujlwm Tus kheej los ntawm PLEX add-on.
Qhov tshwm sim yuav tsum yog cov hauv qab no:
Kauj ruam 4. Kom tsis muaj ob "x" ...
Yog tias tus neeg siv nkag "x" tawm tsam ntau kab, VLOOKUP muaj nuj nqi tsuas yog siv thawj tus nqi nws pom. Txhawm rau zam qhov tsis meej pem, nyem rau ntawm daim ntawv tab Cov ntaub ntawv thiab ces Cov ntawv nyeem (Code). Hauv Visual Basic editor qhov rai uas tshwm, luam cov cai hauv qab no:
Private Sub Worksheet_Change(ByVal Target As Range) Dim r As Long Dim str As String If Target.Count > 1 Ces Exit 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 End Yog Application.EnableEvents = True End Sub Qhov macro no txwv tsis pub tus neeg siv nkag mus ntau dua ib qho "x" hauv thawj kab.
Zoo, yog tag nrho! Txaus siab rau!
- Siv VLOOKUP muaj nuj nqi los hloov cov nqi
- Ib qho kev txhim kho ntawm VLOOKUP muaj nuj nqi
- Tus nqi hauv cov lus (Propis muaj nuj nqi) los ntawm PLEX add-on