txheem
Nag hmo hauv marathon 30 Excel ua haujlwm hauv 30 hnub peb pom cov ntsiab lus ntawm cov array siv cov haujlwm TIV THAIV (SEARCH) thiab pom tias nws ua haujlwm zoo hauv pab pawg nrog lwm cov nta xws li VLOOKUP (VLOOKUP) thiab INDEX (INDEX).
Hnub 20 ntawm peb marathon, peb yuav mob siab rau txoj kev kawm ntawm txoj haujlwm CHAW NYOB (TXHAIS). Nws xa rov qab qhov chaw nyob ntawm tes hauv hom ntawv siv kab thiab kab zauv. Peb puas xav tau qhov chaw nyob no? Puas tuaj yeem ua tiav nrog lwm cov haujlwm?
Cia peb saib cov ntsiab lus ntawm kev ua haujlwm CHAW NYOB (ADDRESS) thiab kawm cov piv txwv ntawm kev ua haujlwm nrog nws. Yog tias koj muaj lus qhia ntxiv lossis piv txwv, thov qhia rau lawv hauv cov lus pom.
Function 20: ADDRESS
muaj nuj nqi CHAW NYOB (ADDRESS) xa rov qab cov ntaub ntawv ntawm tes raws li cov ntawv nyeem raws kab thiab kab zauv. Nws tuaj yeem xa rov qab qhov tseeb lossis qhov txheeb ze txuas-style chaw nyob. A1 or r1c1 ua. Tsis tas li ntawd, daim ntawv npe tuaj yeem suav nrog hauv qhov tshwm sim.
Yuav siv ADDRESS function li cas?
muaj nuj nqi CHAW NYOB (ADDRESS) tuaj yeem xa rov qab qhov chaw nyob ntawm lub xov tooj ntawm tes, lossis ua haujlwm nrog lwm cov haujlwm rau:
- Tau txais qhov chaw nyob ntawm tes muab kab thiab kab zauv.
- Nrhiav tus nqi ntawm tes los ntawm kev paub kab thiab kab zauv.
- Rov qab qhov chaw nyob ntawm tes nrog tus nqi loj tshaj plaws.
Syntax ADDRESS (ADDRESS)
muaj nuj nqi CHAW NYOB (ADDRESS) muaj cov syntax hauv qab no:
ADDRESS(row_num,column_num,[abs_num],[a1],[sheet_text])
АДРЕС(номер_строки;номер_столбца;[тип_ссылки];[а1];[имя_листа])
- abs_num (link_type) – yog tias sib npaug 1 lossis tsis tau teev tag nrho, cov haujlwm yuav rov qab qhov chaw nyob ($A$1). Kom tau txais qhov chaw nyob txheeb ze (A1), siv tus nqi 4. Lwm txoj kev xaiv: 2=A$1, 3=$A1.
- a1 - Yog tias TRUE (TRUE) lossis tsis tau teev tag nrho, txoj haujlwm rov qab siv rau hauv cov style A1, yog FALSE (FALSE), ces nyob rau hauv style r1c1 ua.
- Sheet_ ntawv (sheet_name) - lub npe ntawm daim ntawv tuaj yeem teev yog tias koj xav pom nws hauv cov txiaj ntsig rov qab los ntawm kev ua haujlwm.
Traps ADDRESS
muaj nuj nqi CHAW NYOB (ADDRESS) xa rov qab tsuas yog qhov chaw nyob ntawm lub xov tooj ntawm tes raws li cov kab ntawv. Yog tias koj xav tau tus nqi ntawm lub xov tooj ntawm tes, siv nws ua qhov kev sib cav TSEEM CEEB (INDIRECT) lossis siv ib qho ntawm lwm cov qauv qhia hauv Piv txwv 2.
Piv txwv 1: Tau txais xov tooj ntawm tes los ntawm kab thiab kab zauv
Siv cov haujlwm CHAW NYOB (ADDRESS) Koj tuaj yeem tau txais qhov chaw nyob ntawm tes ua ntawv siv kab thiab kab zauv. Yog tias koj nkag mus tsuas yog ob qhov kev sib cav no, qhov tshwm sim yuav yog qhov chaw nyob uas sau nyob rau hauv qhov txuas style A1.
=ADDRESS($C$2,$C$3)
=АДРЕС($C$2;$C$3)
Muaj tseeb los yog txheeb ze
Yog tias koj tsis qhia tus nqi sib cav abs_num (reference_type) nyob rau hauv ib tug qauv, qhov tshwm sim yog ib tug kiag li siv.
Txhawm rau pom qhov chaw nyob raws li tus txheeb ze txuas, koj tuaj yeem hloov qhov kev sib cav abs_num (reference_type) tus nqi 4.
=ADDRESS($C$2,$C$3,4)
=АДРЕС($C$2;$C$3;4)
A1 los yog R1C1
Mus rau style links r1c1 ua, hloov ntawm lub neej ntawd style A1, Koj yuav tsum qhia FALSE rau qhov kev sib cav a1.
=ADDRESS($C$2,$C$3,1,FALSE)
=АДРЕС($C$2;$C$3;1;ЛОЖЬ)
Daim ntawv npe
Qhov kawg sib cav yog lub npe ntawm daim ntawv. Yog tias koj xav tau lub npe no hauv qhov tshwm sim, qhia nws raws li kev sib cav sheet_text (npe_npe).
=ADDRESS($C$2,$C$3,1,TRUE,"Ex02")
=АДРЕС($C$2;$C$3;1;ИСТИНА;"Ex02")
Piv txwv 2: Nrhiav tus nqi ntawm tes siv kab thiab kab zauv
muaj nuj nqi CHAW NYOB (ADDRESS) xa rov qab qhov chaw nyob ntawm tes raws li cov ntawv nyeem, tsis yog qhov txuas siv tau. Yog tias koj xav tau kom tau txais tus nqi ntawm lub xov tooj ntawm tes, koj tuaj yeem siv cov txiaj ntsig rov qab los ntawm kev ua haujlwm CHAW NYOB (ADDRESS), raws li kev sib cav rau TSEEM CEEB (INDIRECT). Peb yuav kawm txog kev ua haujlwm TSEEM CEEB (INDIRECT) tom qab hauv marathon 30 Excel ua haujlwm hauv 30 hnub.
=INDIRECT(ADDRESS(C2,C3))
=ДВССЫЛ(АДРЕС(C2;C3))
muaj nuj nqi TSEEM CEEB (INDIRECT) tuaj yeem ua haujlwm yam tsis muaj kev ua haujlwm CHAW NYOB (TXHAIS). Nov yog qhov koj tuaj yeem ua tau, siv tus neeg teb xov tooj sib txuas "&", qhov muag tsis pom qhov chaw nyob xav tau hauv cov style r1c1 ua thiab yog li ntawd tau txais tus nqi ntawm lub cell:
=INDIRECT("R"&C2&"C"&C3,FALSE)
=ДВССЫЛ("R"&C2&"C"&C3;ЛОЖЬ)
muaj nuj nqi INDEX (INDEX) kuj tseem tuaj yeem xa rov qab tus nqi ntawm lub xov tooj yog tias kab thiab kab zauv tau teev tseg:
=INDEX(1:5000,C2,C3)
=ИНДЕКС(1:5000;C2;C3)
1:5000 yog thawj 5000 kab ntawm daim ntawv Excel.
Piv txwv 3: Rov qab qhov chaw nyob ntawm tes nrog tus nqi siab tshaj plaws
Hauv qhov piv txwv no, peb yuav pom cov xov tooj ntawm tes nrog tus nqi siab tshaj plaws thiab siv cov haujlwm CHAW NYOB (ADDRESS) kom tau nws qhov chaw nyob.
muaj nuj nqi MAX (MAX) pom tus lej siab tshaj plaws hauv kab C.
=MAX(C3:C8)
=МАКС(C3:C8)
Tom ntej no los ua haujlwm CHAW NYOB (ADDRESS) ua ke nrog TIV THAIV (MATCH), uas pom cov kab zauv, thiab SAB (COLUMN), uas qhia txog kab zauv.
=ADDRESS(MATCH(F3,C:C,0),COLUMN(C2))
=АДРЕС(ПОИСКПОЗ(F3;C:C;0);СТОЛБЕЦ(C2))