Xaiv ib daim duab los ntawm cov npe dropdown

Formulation ntawm qhov teeb meem

Nws yog ib qho tsim nyog kom paub tseeb tias nyob rau hauv ib qho ntawm cov hlwb ntawm daim ntawv muaj ib daim ntawv teev npe nrog cov npe, thaum xaiv los ntawm qhov twg, cov khoom yuav muab tso rau ib sab ntawm nws raws li daim duab:

Video

Kauj ruam 1. Tsim ib daim ntawv teev npe nrog ib daim duab thiab muab nws lub npe

Peb tsim rau Cov npe 1 peb yog cov catalog nrog cov npe thiab cov duab ntawm cov khoom, muaj ob kab (qauv и Photo):

Tam sim no peb yuav tsum muab lub npe rau peb phau ntawv qhia kom xa mus rau yav tom ntej. Hauv Excel 2003 thiab laus dua, rau qhov no peb mus rau cov ntawv qhia zaub mov Ntxig – Lub Npe – Muab (Insert – Name – Define), thiab hauv Excel 2007 thiab tshiab dua - nyem rau ntawm lub pob Lub Npe Tus Thawj Tswj tab qauv. Tsim ib thaj tsam - sau npe (piv txwv li Photo Album) thiab qhia tus qauv raws li qhov chaw nyob:

=СМЕЩ(Лист1!$A$1;1;0;СЧЁТЗ(Лист1!$A:$A)-1;1)

=OFFSET(Лист1!$A$1;1;0;COUNTA(Лист1!$A:$A)-1;1)

Cov qauv no txiav txim siab qhov kawg ntawm tes nyob hauv kab A thiab tso tawm qhov ntau ntawm A2 mus rau qhov pom ntawm tes. Xws li kev tsim kho nyuaj yog xav tau txhawm rau txhawm rau ntxiv cov qauv tshiab rau peb cov npe thiab tsis xav txog kev kho qhov ntau. Yog tias koj tsis tas yuav ntxiv dab tsi kom paub meej, ces tsis txhob nkag mus rau qhov kev txaus ntshai no, tsuas yog sau = A2: A5

Kauj ruam 2. Tee-down daim ntawv teev npe xaiv tus qauv

Cia peb mus Daim Ntawv 2 thiab tsim ib lub xov tooj muaj nrog cov npe poob rau cov neeg siv xaiv tus qauv xov tooj (cia nws yog A1). Xaiv ib lub xov tooj thiab mus rau cov ntawv qhia zaub mov Cov ntaub ntawv - Tshawb xyuas (Data - Validation) lossis hauv cov ntawv tshiab ntawm Excel - ntawm lub tab Data Validation (Data – Data Validation). Ntxiv mus rau qhov chaw Hom ntaub ntawv (Allow) xaiv daim ntawv teev, mas as Tau qhov twg los qhia peb Photo Album (tsis txhob hnov ​​​​qab ntxiv ib daim ntawv sib npaug rau pem hauv ntej ntawm nws):

Tsis tas li ntawd, nws yog qhov yooj yim los muab lub xov tooj ntawm tes rau lub npe - ntawv qhia zaub mov dua Ntxig – Lub Npe – Muab thiab tom qab ntawd sau lub npe (piv txwv xaiv) thiab OK.

Kauj ruam 3: Luam daim duab

Cia peb txav thawj daim duab los ntawm cov duab album mus rau hauv daim ntawv teev npe. Xaiv lub xovtooj nrog thawj daim duab (tsis yog daim duab nws tus kheej, tab sis ntawm tes!) thiab

hauv Excel 2003 thiab tom qab ntawd, tuav Shift kom qhib cov ntawv qhia zaub mov Kho kom raug neeg. Ib yam khoom uas tsis pom yav dhau los yuav tsum tshwm nyob rau ntawd. Luam raws li daim duab:

Hauv Excel 2007 thiab tshiab dua, koj tuaj yeem nthuav dav qhov poob qis hauv qab lub pob Copy (copy) on Tsev tab:

Hauv Excel 2010, lwm lub qhov rai ntxiv yuav tshwm sim nrog kev xaiv ntawm hom duab los tsim:

Hauv nws, koj yuav tsum xaiv cov kev xaiv "raws li ntawm qhov screen" thiab "raster".

Luam, mus rau Daim Ntawv 2 mus rau daim ntawv teev npe poob thiab hauv ib lub xov tooj khoob ze nws, ntxig peb cov duab thaij duab me me ntawm lub xov tooj nrog daim duab (menu Kho kom raug - Paste los yog li qub CTRL + V.).

Kauj ruam 4. Tsim ib tug dynamic txuas rau cov duab xaiv

Tam sim no koj yuav tsum ua qhov txuas uas yuav taw tes rau ntawm tes nrog cov duab xaiv. Qhib cov ntawv qhia zaub mov Ntxig - Lub Npe - Txhais or Lub Npe Tus Thawj Tswj tab qauv thiab tsim lwm qhov chaw muaj npe:

Lub npe ntawm peb qhov txuas, cia peb hais, yuav yog Photo, thiab formula

=СМЕЩ(Лист1!$B$2;ПОИСКПОЗ(Выбор;Фотоальбом;0)-1;0;1;1)

=OFFSET(Лист1!$B$2;MATCH(Выбор;Фотоальбом;0)-1;0;1;1)

Technically, muaj nuj nqi TIV THAIV pom ib lub xovtooj nrog tus qauv xav tau hauv phau ntawv teev npe, thiab ua haujlwm OFFSET ces muab ib qhov txuas mus rau ntawm tes uas nyob ib sab mus rau sab xis ntawm lub npe pom, piv txwv li xov tooj ntawm tes nrog daim duab ntawm cov khoom.

Kauj ruam 5. Txuas ib daim duab rau ib qhov txuas

Nws tseem yuav xaiv cov duab theej rau ntawm Cov npe 2 thiab nkag mus rau hauv lub formula bar

=Photo

thiab nias Enter

Txhua tus! 🙂

• Tsim cov npe poob hauv cov ntawv ua haujlwm 
• Tsim cov Dropdowns nyob 
• Nws pib tsim cov npe Dropdown nrog PLEX Ntxiv-In cuab yeej 
• Daim ntawv teev npe nrog kev rho tawm tsis siv neeg ntawm cov khoom siv uas twb tau siv lawm
• Daim ntawv teev npe nrog tsis siv neeg ntxiv ntawm cov khoom ploj lawm