Yuav ua li cas ua ib daim ntawv teev npe hauv Excel

Daim ntawv teev npe poob yog ib qho cuab yeej siv tau zoo kawg uas tuaj yeem pab ua haujlwm nrog cov ntaub ntawv yooj yim dua. Nws ua rau nws muaj peev xwm muaj ob peb qhov tseem ceeb hauv ib lub xov tooj ntawm tes ib zaug, uas koj tuaj yeem ua haujlwm, zoo li nrog lwm tus. Txhawm rau xaiv qhov koj xav tau, tsuas yog nyem rau ntawm lub cim xub, tom qab ntawd cov npe ntawm qhov tseem ceeb uXNUMXbuXNUMXbis tso tawm. Tom qab xaiv ib qho tshwj xeeb, lub xov tooj ntawm tes tau sau nrog nws, thiab cov qauv raug suav rov qab raws li nws.

Excel muab ntau txoj hauv kev los tsim cov ntawv qhia zaub mov poob qis, thiab ntxiv rau, nws tso cai rau koj hloov kho lawv yooj yim. Cia peb txheeb xyuas cov txheej txheem no kom ntxaws ntxiv.

Sau cov txheej txheem tsim

Txhawm rau tsim cov ntawv qhia pop-up, nyem rau ntawm cov ntawv qhia zaub mov raws txoj hauv kev "Cov ntaub ntawv" - "Cov ntaub ntawv siv tau". Lub dialog box yuav qhib qhov twg koj yuav tsum nrhiav cov "Parameters" tab thiab nyem rau ntawm nws yog tias nws tsis tau qhib ua ntej. Nws muaj ntau qhov chaw, tab sis cov khoom "Data Type" tseem ceeb rau peb. Ntawm tag nrho cov ntsiab lus, "Sau" yog qhov yog.

Tus naj npawb ntawm txoj kev uas cov ntaub ntawv nkag mus rau hauv cov npe pop-up yog qhov loj heev.

1. Independent indications ntawm daim ntawv teev cov ntsiab lus sib cais los ntawm ib tug semicolon nyob rau hauv lub "Source" teb nyob rau hauv tib lub tab ntawm tib lub dialog box.
   

   

2. Kev qhia ua ntej ntawm qhov muaj nuj nqis. Qhov chaw teb muaj ntau qhov chaw uas cov ntaub ntawv xav tau muaj.
   

   

3. Qhia txog qhov muaj npe. Ib txoj hauv kev uas rov ua dua qhov dhau los, tab sis nws tsuas yog tsim nyog rau npe ua ntej lub npe.
   

   

Ib qho ntawm cov txheej txheem no yuav tsim cov txiaj ntsig xav tau. Wb saib txoj hauv kev los tsim cov npe poob qis hauv lub neej tiag tiag.

Raws li cov ntaub ntawv los ntawm cov npe

Cia peb hais tias peb muaj ib lub rooj piav txog hom txiv hmab txiv ntoo sib txawv.

Txhawm rau tsim ib daim ntawv teev npe hauv cov ntawv qhia zaub mov raws li cov ntaub ntawv no, koj yuav tsum ua cov hauv qab no:

1. Xaiv lub xovtooj tshwj tseg rau cov npe yav tom ntej.
2. Nrhiav cov ntaub ntawv tab ntawm kab. Ntawm no peb nyem rau ntawm "Tshawb xyuas cov ntaub ntawv".
   

   

3. Nrhiav cov khoom "Data Type" thiab hloov tus nqi mus rau "Tsev Npe".
   

   

4. Nyob rau hauv lub teb denoting qhov "Source", nkag mus rau qhov xav tau ntau yam. Thov nco ntsoov tias cov ntaub ntawv pov thawj tseeb yuav tsum tau teev tseg kom thaum luam cov npe, cov ntaub ntawv tsis hloov.
   

Tsis tas li ntawd, muaj kev ua haujlwm los tsim cov npe ib zaug hauv ntau dua ib lub xov tooj. Txhawm rau ua tiav qhov no, koj yuav tsum xaiv lawv tag nrho, thiab ua tib yam li tau piav qhia ua ntej. Ntxiv dua thiab, koj yuav tsum ua kom paub tseeb tias cov ntaub ntawv tsis raug sau. Yog hais tias qhov chaw nyob tsis muaj ib tug duas kos npe rau ntawm kab thiab kab npe, ces koj yuav tsum tau ntxiv lawv los ntawm nias lub F4 yuam sij kom txog thaum lub $ kos npe rau ntawm kab thiab kab npe.

Nrog phau ntawv teev cov ntaub ntawv

Hauv qhov xwm txheej saum toj no, cov npe tau sau los ntawm qhov tseem ceeb ntawm qhov xav tau ntau yam. Qhov no yog ib txoj kev yooj yim, tab sis qee zaus nws yog ib qho tsim nyog los sau cov ntaub ntawv manually. Qhov no yuav ua rau nws muaj peev xwm zam kom tsis txhob muaj cov ntaub ntawv luam tawm hauv phau ntawv ua haujlwm.

Piv txwv tias peb tau ntsib nrog txoj haujlwm ntawm kev tsim cov npe uas muaj ob txoj kev xaiv: yog thiab tsis yog. Txhawm rau ua kom tiav txoj haujlwm, nws yog qhov tsim nyog:

1. Nyem rau ntawm lub cell rau daim ntawv teev npe.
2. Qhib "Cov ntaub ntawv" thiab pom cov ntu "Cov ntaub ntawv txheeb xyuas" paub rau peb.
   

   

3. Ntxiv dua thiab, xaiv hom "Sau" hom.
   

   

4. Ntawm no koj yuav tsum nkag mus rau “Yog; Tsis yog” raws li qhov chaw. Peb pom tias cov ntaub ntawv nkag mus manually siv lub semicolon rau enumeration.

Tom qab nias OK, peb muaj cov txiaj ntsig hauv qab no.

Tom ntej no, qhov kev pab cuam yuav cia li tsim ib tug drop-down ntawv qhia zaub mov nyob rau hauv lub cell tsim nyog. Txhua cov ntaub ntawv uas tus neeg siv tau teev tseg raws li cov khoom hauv cov npe pop-up. Cov kev cai rau kev tsim ib daim ntawv teev npe hauv ntau lub hlwb zoo ib yam li yav dhau los, nrog rau qhov tshwj xeeb uas koj yuav tsum qhia cov ntaub ntawv manually siv semicolon.

Tsim ib daim ntawv teev npe uas siv OFFSET muaj nuj nqi

Ntxiv nrog rau txoj kev classical, nws muaj peev xwm siv tau rau kev ua haujlwm TSEEM CEEBlos tsim dropdown menus.

Cia peb qhib daim ntawv.

Txhawm rau siv cov haujlwm rau cov npe dropdown, koj yuav tsum ua cov hauv qab no:

1. Xaiv lub xovtooj ntawm qhov kev txaus siab uas koj xav tso cov npe yav tom ntej.
2. Qhib "Cov ntaub ntawv" tab thiab "Data Validation" qhov rai hauv ntu.
   

   

3. Teem "List". Qhov no yog ua tib yam li cov piv txwv yav dhau los. Thaum kawg, cov qauv hauv qab no yog siv: =OFFSET(A$2$;0;0;5). Peb nkag mus rau nws qhov twg cov hlwb uas yuav siv los ua kev sib cav tau teev tseg.

Ces qhov kev pab cuam yuav tsim ib daim ntawv qhia zaub mov nrog ib daim ntawv teev cov txiv hmab txiv ntoo.

Lub syntax rau qhov no yog:

=OFFSET(reference,line_offset,column_offset,[height],[dav])

Peb pom tias txoj haujlwm no muaj 5 qhov kev sib cav. Ua ntej, thawj qhov chaw nyob ntawm tes yuav tsum tau offset. Ob qhov kev sib cav tom ntej no qhia tias muaj pes tsawg kab thiab kab rau offset. Hais txog peb, qhov siab sib cav yog 5 vim nws sawv cev rau qhov siab ntawm daim ntawv. 

Cov npe poob hauv Excel nrog cov ntaub ntawv hloov pauv (+ siv OFFSET muaj nuj nqi)

Nyob rau hauv cov ntaub ntawv muab TSEEM CEEB tso cai los tsim cov ntawv qhia pop-up nyob rau hauv ib qho chaw ruaj khov. Qhov tsis zoo ntawm txoj kev no yog tias tom qab ntxiv cov khoom, koj yuav tau hloov kho cov qauv ntawm koj tus kheej.

Txhawm rau tsim cov npe dynamic nrog kev txhawb nqa nkag mus rau cov ntaub ntawv tshiab, koj yuav tsum:

1. Xaiv lub cell ntawm kev txaus siab.
2. Qhib "Cov ntaub ntawv" tab thiab nyem rau ntawm "Data Validation".
3. Hauv lub qhov rai uas qhib, xaiv cov khoom "Sau" dua thiab qhia cov qauv hauv qab no raws li cov ntaub ntawv hauv qab no: =СМЕЩ(A$2$;0;0;СЧЕТЕСЛИ($A$2:$A$100;”<>”))
4. Nyem OK.

Qhov no muaj ib txoj haujlwm COUNTIF, txhawm rau txiav txim siab tam sim muaj pes tsawg lub hlwb tau sau (txawm tias nws muaj ntau qhov kev siv ntau, peb tsuas yog sau nws ntawm no rau lub hom phiaj tshwj xeeb).

Txhawm rau kom cov mis ua haujlwm ib txwm, nws yog qhov yuav tsum tau taug qab seb puas muaj cov hlwb khoob ntawm txoj kev ntawm cov mis. Lawv yuav tsum tsis yog.

Cov npe poob nrog cov ntaub ntawv los ntawm lwm daim ntawv lossis Excel cov ntaub ntawv

Txoj kev classic tsis ua haujlwm yog tias koj xav tau cov ntaub ntawv los ntawm lwm daim ntawv lossis txawm tias ib daim ntawv uas muaj nyob hauv tib cov ntaub ntawv. Rau qhov no, kev ua haujlwm yog siv TSEEM CEEB, uas tso cai rau koj nkag mus rau hauv qhov tseeb hom ntawv txuas mus rau ib lub xovtooj ntawm lwm daim ntawv lossis feem ntau - cov ntaub ntawv. Koj yuav tsum ua cov hauv qab no:

1. Qhib lub xov tooj ntawm tes uas peb tso npe.
2. Peb qhib qhov rais peb twb paub lawm. Nyob rau hauv tib qhov chaw uas peb yav tas los qhia qhov chaw rau lwm yam, ib tug formula qhia nyob rau hauv hom =INDIRECT(“[List1.xlsx]Sheet1!$A$1:$A$9”). Lawm, es tsis txhob List1 thiab Sheet1, koj tuaj yeem ntxig koj phau ntawv thiab daim ntawv npe, feem. 

Nco ntsoov! Lub npe ntawm cov ntaub ntawv yog muab rau hauv square brackets. Hauv qhov no, Excel yuav tsis tuaj yeem siv cov ntaub ntawv kaw tam sim no los ntawm cov ntaub ntawv.

Nws tseem yuav tsum tau muab sau tseg tias cov ntaub ntawv npe nws tus kheej ua rau kev nkag siab tsuas yog tias cov ntaub ntawv xav tau nyob rau hauv tib lub nplaub tshev raws li ib qho uas cov npe yuav muab tso rau. Yog tias tsis yog, koj yuav tsum qhia qhov chaw nyob ntawm daim ntawv no tag nrho.

Tsim cov Dropdowns nyob

Ib daim ntawv teev npe nyob yog ib qho uas nws cov ntsiab lus cuam tshuam los ntawm tus neeg siv kev xaiv hauv lwm daim ntawv teev npe. Piv txwv tias peb muaj ib lub rooj qhib rau pem hauv ntej ntawm peb uas muaj peb yam, txhua tus tau muab lub npe.

Koj yuav tsum ua raws li cov kauj ruam no los tsim cov npe uas nws cov txiaj ntsig cuam tshuam los ntawm kev xaiv xaiv hauv lwm daim ntawv teev npe.

1. Tsim cov npe thib 1 nrog ntau lub npe.
   

   

2. Ntawm qhov chaw nkag, cov ntsuas yuav tsum tau hais txog ib qho los ntawm ib qho.
   

   

3. Tsim ib daim ntawv thib 2 nyob ntawm seb hom nroj tsuag uas tus neeg tau xaiv. Xwb, yog tias koj teev cov ntoo hauv thawj daim ntawv teev npe, cov ntaub ntawv hauv daim ntawv thib ob yuav yog "oak, hornbeam, chestnut" thiab tshaj. Nws yog ib qho tsim nyog los sau cov qauv hauv qhov chaw nkag ntawm cov ntaub ntawv qhov chaw =INDIRECT(E3). E3 - cell muaj lub npe ntawm qhov ntau 1.=INDIRECT(E3). E3 - xov tooj nrog lub npe ntawm daim ntawv teev npe 1.

Tam sim no txhua yam yog npaj txhij.

Yuav ua li cas xaiv ntau yam txiaj ntsig los ntawm daim ntawv teev npe poob?

Qee lub sij hawm nws tsis tuaj yeem muab qhov kev nyiam rau ib qho nqi xwb, yog li yuav tsum xaiv ntau tshaj ib qho. Tom qab ntawd koj yuav tsum ntxiv macro rau nplooj ntawv code. Siv qhov tseem ceeb ua ke Alt + F11 qhib Visual Basic Editor. Thiab tus lej tau muab tso rau ntawd.

Private Sub Worksheet_Change (ByVal Target As Range)

    On Yuam kev Resume Tom ntej

    Yog Tsis Sib Ncaim(Target, Range(«Е2:Е9»)) Tsis muaj dab tsi Thiab Target.Cells.Count = 1 Ces

        Application.EnableEvents = False

        Yog Len (Target.Offset(0, 1)) = 0 Ces

            Target.Offset(0, 1) = Lub hom phiaj

        lwm tus

            Target.End (xlToRight).Offset(0, 1) = Target

        Xaus Yog

        Target.ClearContents

        Application.EnableEvents = Muaj tseeb

    Xaus Yog

End Sub 

Txhawm rau kom cov ntsiab lus ntawm cov hlwb kom pom hauv qab no, peb ntxig cov cai hauv qab no rau hauv tus editor.

Private Sub Worksheet_Change (ByVal Target As Range)

    On Yuam kev Resume Tom ntej

    Yog Tsis Sib Ncaim(Target, Range(«Н2:К2»)) Tsis muaj dab tsi Thiab Target.Cells.Count = 1 Ces

        Application.EnableEvents = False

        Yog Len (Target.Offset(1, 0)) = 0 Ces

            Target.Offset(1, 0) = Lub hom phiaj

        lwm tus

            Target.End (xlDown).Offset (1, 0) = Target

        Xaus Yog

        Target.ClearContents

        Application.EnableEvents = Muaj tseeb

    Xaus Yog

End Sub

Thiab thaum kawg, tus lej no yog siv los sau rau hauv ib lub xovtooj ntawm tes.

Private Sub Worksheet_Change (ByVal Target As Range)

    On Yuam kev Resume Tom ntej

    Yog Tsis Sib Cuam (Target, Range(«C2:C5»)) Tsis muaj dab tsi Thiab Target.Cells.Count = 1 Ces

        Application.EnableEvents = False

        newVal = Lub hom phiaj

        Daim ntawv thov.Undo

        qub = Target

        Yog Len (oldval) <> 0 Thiab oldval <> newVal Ces

            Target = Target & «,» & newVal

        lwm tus

            Target = newVal

        Xaus Yog

        Yog Len (newVal) = 0 Ces Target.ClearContents

        Application.EnableEvents = Muaj tseeb

    Xaus Yog

End Sub

Ntau yam yog kho tau.

Yuav ua li cas ua ib daim ntawv teev npe nrog kev tshawb nrhiav?

Hauv qhov no, koj yuav tsum xub siv ntau hom npe sib txawv. Lub tab "Developer" qhib, tom qab ntawd koj yuav tsum nyem lossis coj mus rhaub (yog tias lub vijtsam kov) ntawm "Ntxig" - "ActiveX" keeb. Nws muaj combo box. Koj yuav raug ceeb toom kom kos daim ntawv teev npe no, tom qab ntawd nws yuav muab ntxiv rau hauv daim ntawv.

Ntxiv mus, nws yog configured los ntawm cov khoom, qhov twg ib tug ntau yam yog teev nyob rau hauv lub ListFillRange kev xaiv. Lub xovtooj ntawm qhov uas tus neeg siv tus nqi txhais tau tshwm sim yog teeb tsa siv qhov kev xaiv LinkedCell. Tom ntej no, koj tsuas yog yuav tsum tau sau thawj cov cim, raws li qhov kev zov me nyuam yuav pom zoo kom muaj txiaj ntsig.

Cov npe poob nrog cov ntaub ntawv hloov pauv tsis siv neeg

Kuj tseem muaj kev ua haujlwm uas cov ntaub ntawv hloov pauv tau tom qab lawv ntxiv rau qhov ntau. Nws yog ib qho yooj yim los ua qhov no:

1. Tsim ib txheej cell rau cov npe yav tom ntej. Hauv peb cov ntaub ntawv, qhov no yog ib txheej xim. Peb xaiv nws.
   

   

2. Tom ntej no, nws yuav tsum tau formatted raws li lub rooj. Koj yuav tsum nyem lub pob ntawm tib lub npe thiab xaiv lub rooj style.
   

   

   

Tom ntej no, koj yuav tsum paub meej tias qhov ntau npaum li cas los ntawm nias lub pob "OK".

Peb xaiv lub ntsiab lus thiab muab nws lub npe los ntawm qhov chaw nkag nyob rau sab saum toj ntawm kab A.

Qhov ntawd yog nws, muaj ib lub rooj, thiab nws tuaj yeem siv los ua lub hauv paus rau daim ntawv teev npe, uas koj xav tau:

1. Xaiv lub xovtooj ntawm qhov chaw nyob.
2. Qhib cov ntaub ntawv Validation dialog.
   

   

3. Peb teev cov ntaub ntawv hom rau "Lus", thiab raws li qhov tseem ceeb peb muab lub npe ntawm lub rooj los ntawm lub = kos npe.
   

   

   

Txhua yam, lub xovtooj ntawm tes tau npaj txhij, thiab cov npe ntawm cov xim tau qhia hauv nws, raws li peb xav tau. Tam sim no koj tuaj yeem ntxiv txoj haujlwm tshiab tsuas yog sau rau hauv ib lub xovtooj ntawm qhov qis me ntsis tam sim ntawd tom qab qhov kawg.

Qhov no yog qhov kom zoo dua ntawm lub rooj, tias qhov ntau yuav nce ntxiv thaum cov ntaub ntawv tshiab ntxiv. Raws li, qhov no yog qhov yooj yim tshaj plaws los ntxiv ib daim ntawv teev npe.

Yuav ua li cas luam daim ntawv teev npe?

Txhawm rau luam tawm, nws txaus los siv qhov tseem ceeb ua ke Ctrl + C thiab Ctrl + V. Yog li daim ntawv teev npe poob yuav raug theej nrog rau cov formatting. Txhawm rau tshem tawm formatting, koj yuav tsum siv qhov tshwj xeeb muab tshuaj txhuam (hauv cov ntawv qhia zaub mov, qhov kev xaiv no tshwm sim tom qab luam daim ntawv), qhov twg "cov xwm txheej ntawm qhov tseem ceeb" xaiv.

Xaiv tag nrho cov cell uas muaj cov npe poob

Txhawm rau ua tiav txoj haujlwm no, koj yuav tsum siv "Xaiv ib pab pawg ntawm cov hlwb" ua haujlwm hauv "Nrhiav thiab Xaiv" pawg.

Tom qab ntawd, lub dialog box yuav qhib, qhov twg koj yuav tsum xaiv cov khoom "Tag nrho" thiab "Cov tib yam" nyob rau hauv lub "Data Validation" ntawv qhia zaub mov. Thawj cov khoom xaiv tag nrho cov npe, thiab qhov thib ob xaiv tsuas yog cov uas zoo ib yam li qee qhov.