Yuav Tsim Ib Qoob Los Sau Hauv Excel

Excel cov ntaub ntawv siv tau cov kev xaiv muaj xws li tsim kom muaj cov ntawv sau tseg uas txwv cov ntaub ntawv uas yuav nkag mus rau hauv ib lub xov tooj ntawm tes rau cov ntawv teev npe ua ntej.

Thaum sau ib daim ntawv teev npe ntxiv rau ib lub xov tooj ntawm tes, tus xub tshwm rau nws. Txhawm rau ntawm lub xub yuav qhib lub npe thiab tso cai rau koj xaiv ib qho ntawm cov npe khoom nkag mus rau hauv lub xovtooj ntawm.

Cov ntaub ntawv siv hauv daim ntawv teev npe no muaj nyob rau hauv:

• nyob rau tib daim ntawv ua haujlwm raws li daim ntawv teev npe
• rau ntawm ib daim ntawv txawv txawv hauv tib lub workbook
• nyob hauv ib phau ntawv sib txawv

Nyeem: Siv cov ntaub ntawv khaws cia rau hauv Txawj Ua Haujlwm Txawv Tebchaws

Hauv qhov kev nyeem no, peb yuav tsim ib daim ntawv teev npe uas siv cov npe ntawm cov ntawv sau nyob hauv ib phau ntawv sib txawv.

Cov txiaj ntsim ntawm kev siv cov npe ntawm cov ntaub ntawv muaj nyob rau hauv ib phau ntawv sib txawv muaj xws li muab cov ntaub ntawv teev npe nruab nrab yog tias nws siv los ntawm ntau cov neeg siv thiab tiv thaiv cov ntaub ntawv ntawm kev yuam kev lossis kev hloov.

Lus Cim: Thaum cov ntaub ntawv sau cia tau muab cia rau hauv ib phau ntawv nyias muaj nyias phau ntawv yuav tsum qhib rau qhov kev sau npe ua haujlwm.

Ua raws li cov kauj ruam hauv cov ntsiab lus kawm hauv qab no koj tuaj yeem tsim los ntawm kev tsim, siv, thiab hloov cov lus sau tseg kom zoo ib yam li pom nyob hauv daim duab saum toj no.

Cov ntawv qhia nyeem no, tsis tas li ntawd, tsis txhob suav cov lus qhia tawm rau cov nplooj ntawv ua haujlwm.

Qhov no yuav tsis cuam tshuam nrog ua kom tiav daim ntawv qhia. Koj daim ntawv khom nqi yuav txawv dua li qhov piv txwv ntawm phab 1, tab sis daim ntawv teev npe cia yuav ua rau koj tib yam kev tshwm sim.

Cov Ntsiab Lus Kawm

• Qhib Cov Ntaub Ntawv thiab Txuag Cov Ntaub Ntawv Xov Xwm
• Tsim Ob Lub Npe Ris Tseg
• Qhib Cov Ntaub Ntawv Sib Txuas Txuas Cov Cai
• Nkag mus rau hauv cov ntaub ntawv thiab Sau Ntawv Tawm Tseg
• Hloov cov Khoom Muag
• Cov Kev Xaiv rau Kev Tiv Thaiv Daim Ntawv Teev Tseg

01 ntawm 06

Nkag mus rau hauv Cov Ntaub Ntawv Xov Xwm

Qhib Ob Txoj Haujlwm Hauv Excel

Raws li tau hais, rau qhov kev nyeem no cov ntaub ntawv rau ntawm daim ntawv teev npe cia yuav raug nyob rau hauv ib phau ntawv sib txawv ntawm daim ntawv teev npe cia.

Rau kev nyeem ntawv no ua raws li cov kauj ruam:

1. Qhib ob phau ntawv sau ua lus Excel
2. Tseg ib phau ntawv nrog rau lub npe ntawm cov ntaub ntawv-source.xlsx - qhov workbook no yuav muaj cov ntaub ntawv rau ntawm daim ntawv teev npe
3. Txuag lub thib ob workbook nrog lub npe ntawm daim ntawv drop-down-list.xlsx - phau ntawv no yuav muaj cov npe sau xa rov qab
4. Tawm ob phau ntawv sau qhib tom qab txuag.

Nkag mus rau hauv Cov Ntaub Ntawv Xov Xwm

1. Sau cov ntaub ntawv hauv qab no rau hauv hlwb A1 rau A4 ntawm cov ntaub ntawv-source.xlsx workbook raws li pom hauv daim duab saum toj no.
2. A1 - Gingerbread A2 - Txiv qaub A3 - Oatmeal Raisin A4 - Chocolate Chip
3. Khaws cov ntawv ua haujlwm thiab nws cia qhib
4. Sau cov ntaub ntawv hauv qab no rau hauv cov hlwb B1 ntawm txoj kev ua theem down-down-list.xlsx .
5. B1 - Kua nplaum uas hom:
6. Khaws cov ntawv ua haujlwm thiab nws cia qhib
7. Cov ntawv teev npe cia yuav muab ntxiv rau lub xov tooj ntawm C1 ntawm phau ntawv no

02 ntawm 06

Tsim Ob Lub Npe Ris Tseg

Tsim Ob Lub Npe Ris Tseg

Ib lub npe tau tso cai rau koj xa koj mus rau ntau hom qe ntshav hauv ib phau ntawv Excel.

Npe cov npe tau siv ntau yam hauv Excel xws li siv lawv hauv cov qauv thiab thaum tsim cov kab kos.

Hauv tag nrho cov rooj sib hais, ib qho npe hu ua yog siv qhov chaw ntawm cov xov tooj ntawm tes hais txog qhov chaw nyob ntawm daim ntawv ua haujlwm.

Thaum siv nyob rau hauv ib qho kev nco nco nyob hauv ib phau ntawv sib txawv, ob lub npe hu ua yuav tsum siv.

• Ib qho yog tsim rau cov khoom teev npe hauv cov ntawv ua haujlwm uas cov ntaub ntawv nyob hauv
• Ib qho thib ob yog tsim nyob rau hauv phau ntawv sau ua haujlwm uas cov ntawv teev npe cia rau hauv. Qhov kev hu ua ob lub npe hu mus rau cov npe hauv thawj phau ntawv ua ntej

Cov Kauj Ruam Tutorial

Thawj Lub Npe Npe

1. Xaiv hlwb A1 - A4 ntawm cov ntaub ntawv-source.xlsx workbook los qhia rau lawv
2. Nias ntawm lub Thawv Npe ntawm sab saud Kem A
3. Hom "Ncuav qab zib" (tsis muaj quotes) hauv lub Thawv Npe
4. Nias tus yuam sij ENTER rau cov keyboard
5. Cov kab A1 rau A4 ntawm cov ntaub ntawv-source.xlsx workbook tam sim no muaj ntau lub npe ntawm Cov Ncuav Qab Zib
6. Khaws cov ntawv ua haujlwm

Lub Npe Ob Npe

Qhov kev hu ua ob kab lus no tsis siv xov tooj ntawm cov ntaub ntawv los ntawm cov ntawv sau npe-list.xlsx .

Es, nws yuav, raws li hais, txuas mus rau lub ncuav qab zib ntau lub npe nyob rau hauv cov ntaub ntawv-source.xlsx workbook.

Qhov no yog qhov tsim nyog vim tias Excel yuav tsis lees txais xov chiv keeb los ntawm ib phau ntawv sib txawv rau ib lub npe. Nws yuav, txawm li cas los xij, tshwj tsis yog lub npe txawv.

Tsim cov kab lus thib ob, yog li no, tsis siv lub Thauv Lub Npe , tiam sis siv lub Npe Lub Chaw Hauj Lwm xaiv nyob rau ntawm Cov Ntawv Tab ntawm txoj cai.

1. Nias ntawm tes C1 hauv phau ntawv teev npe-list.xlsx
2. Nias rau ntawm tus qauv> Lub Npe Tus Thawj Tswj ntawm lub xov tooj qhib lub Npe Lub Tuam Thawj Tuam Tsev Xov Tooj
3. Nias rau ntawm New New button qhib lub Thawv Tshiab Lub Thawv
4. Nyob rau hauv lub npe kab hom: Cov ntaub ntawv
5. Hauv cov lus teb rau kab hom: = 'data-source.xlsx'! Cookies
6. Nias OK los ua kom tiav cov npe thiab xa rov qab mus rau lub Npe Tus Neeg Tiv Thaiv sib tham
7. Nias kaw kaw lub Npe Tus Neeg Tiv Thaiv sib tham
8. Khaws cov ntawv ua haujlwm

03 ntawm 06

Qhib Cov Ntaub Ntawv Sib Txuas Txuas Cov Cai

Qhib Cov Ntaub Ntawv Sib Txuas Txuas Cov Cai

Tag nrho cov ntaub ntawv validation xaiv nyob rau hauv Excel, nrog rau kev nco npe, yog teem siv cov ntaub ntawv validation dialog box.

Ntxiv nrog rau ntxiv kev sau npe rau ib daim ntawv ua haujlwm, cov ntaub ntawv validation hauv Excel kuj siv tau los tswj lossis txwv cov ntaub ntawv uas muab tso nkag tau rau hauv tej lub hlwb hauv daim ntawv ua haujlwm.

Cov Kauj Ruam Tutorial

1. Nias ntawm tes ntawm C1 ntawm cov ntawv sau npe-list.xlsx los ua kom nws lub xovtooj ntawm tes - qhov no yog qhov chaw uas poob rau npe yuav
2. Nias rau ntawm cov ntaub ntawv ntawm txoj cai ntawm daim ntawv tais cai saum toj sau nplooj ntawv
3. Nias ntawm cov ntaub ntawv Validation icon ntawm qhov ribbon qhib lub tshuab raj nqes
4. Nias rau ntawm cov ntaub ntawv Validation nyob rau hauv lub tshuab raj qhib cov lus Validation dialog box
5. Tawm lub thawv ntawv qhib rau lub kauj ruam tom ntej hauv phau ntawv nyeem

04 ntawm 06

Siv Daim Ntawv Teev Npe rau Cov Ntaub Ntawv Siv Tau

Xaiv ib Daim Ntawv Teev Npe rau Cov Ntaub Ntawv Tso Cai

Raws li tau hais muaj ntau qhov kev xaiv rau cov ntaub ntawv validation hauv Excel ntxiv nrog rau ib daim ntawv teev npe.

Hauv kauj ruam no peb yuav xaiv qhov Kev Xaiv cov lus xaiv ua hom ntawm cov ntaub ntawv validation uas siv rau D1 ntawm daim ntawv khomob.

Cov Kauj Ruam Tutorial

1. Nias ntawm nqis nqis hauv lub thawv ntawv sib tham
2. Nias ntawm qhov xub hauv qhov kawg ntawm qhov Cia mus qhib cov ntawv sau cia
3. Nias rau Daim Ntawv Xaiv los xaiv ib qho kev teev npe cia rau cov ntaub ntawv validation nyob rau ntawm C1 thiab mus qhib cov kab hauv kab ntawv

Nkag mus rau hauv cov ntaub ntawv thiab Sau Ntawv Tawm Tseg

Vim tias cov ntaub ntawv khaws cia rau kev teev npe nyob rau ntawm ib phau ntawv sib txawv, lub npe hu ua thib ob ua ntej yuav nkag mus rau hauv qhov Txuas Txuas nyob rau hauv lub thawv ntawv.

Cov Kauj Ruam Tutorial

1. Nias rau ntawm qhov chaw
2. Hom "= Cov ntaub ntawv" (tsis muaj quotes) nyob rau hauv qhov chaw kab
3. Nias OK los ua kom tiav qhov sau npe thiab kaw cov ntaub ntawv Validation dialog box
4. Ib sab me me xub icon nyob rau sab xis ntawm tes ntawm C1
5. Nias ntawm qhov xub hauv qab yuav tsum qhib qhov cim cia nrog cov plaub cookie npe nkag hauv hlwb A1 A4 ntawm cov ntaub ntawv-source.xlsx workbook
6. Txhaj rau ntawm ib lub npe yuav tsum sau tias lub npe ntawd mus rau ntawm tes C1

05 ntawm 06

Hloov Poob Tsi

Hloov cov Khoom Muag

Kom nco cov ntawv teev npe nrog cov kev hloov hauv peb cov ntaub ntawv, nws kuj yuav tsum tau ncua sij hawm hloov cov kev xaiv hauv daim ntawv.

Vim tias peb siv ntau lub npe los ua qhov chaw rau peb cov khoom sau npe tsis yog cov npe ntawm cov npe, hloov cov npe cookie hauv lub npe hu ua nyob rau hauv hlwb A1 rau A4 ntawm cov ntaub ntawv-source.xlsx workbook tam sim ntawd hloov cov npe hauv txoj kev nco sau.

Yog tias cov ntaub ntawv nkag ncaj qha mus rau hauv lub thawv dialog, kev hloov rau daim ntawv yuav rov qab mus rau hauv lub thawv tawm tswv yim thiab hloov cov kab qhov chaw.

Hauv cov kauj ruam no peb yuav hloov Lemon to Shortbread nyob rau hauv kev nco teev los ntawm kev hloov cov ntaub ntawv nyob rau ntawm tes A2 ntawm cov raug teev nyob hauv cov ntaub ntawv-source.xlsx workbook.

Cov Kauj Ruam Tutorial

1. Nias ntawm Cell A2 nyob rau hauv cov ntaub ntawv-source.xlsx workbook (Txiv qaub) ua kom nws lub xovtooj ntawm tes
2. Hom Shortbread rau hauv cell A2 thiab nias lub Nkag Mus rau ntawm cov keyboard
3. Nias ntawm qhov xub cia rau txoj kev nco hauv xov tooj ntawm C1 ntawm cov ntawv sau npe-list.xlsx los qhib lub npe
4. Yam 2 nyob rau hauv daim ntawv yuav tsum tam sim no nyeem Shortbread txhob siv txiv qaub

06 ntawm 06

Cov Kev Xaiv rau Kev Tiv Thaiv Daim Ntawv Teev Tseg

Cov Kev Xaiv rau Kev Tiv Thaiv Daim Ntawv Teev Tseg

Vim tias peb cov ntaub ntawv muaj nyob rau ntawm ib daim ntawv txawv ntawm cov ntawv sau cia muaj rau kev tiv thaiv cov ntaub ntawv teev muaj xws li:

• Tiv thaiv cov ntawv ua haujlwm uas xauv lub hlwb A1 rau A4 ntawm daim ntawv 2
• Yuav tsum muaj ib lo lus zais los hloov cov ntawv ua haujlwm
• Muaj cov phau ntawv qhib yog nyeem ntawv nkaus xwb