txheem
Nrog koj zoo siab! Koj ua nws mus rau hnub kawg ntawm marathon 30 Excel ua haujlwm hauv 30 hnub. Nws tau taug kev ntev thiab nthuav dav thaum koj tau kawm ntau yam tseem ceeb ntawm Excel ua haujlwm.
Hnub 30 ntawm lub marathon, peb yuav mob siab rau txoj kev kawm ntawm txoj haujlwm TSEEM CEEB (INDIRECT), uas rov qab qhov txuas tau teev tseg los ntawm kab ntawv. Nrog rau qhov kev ua haujlwm no, koj tuaj yeem tsim cov npe poob qis. Piv txwv li, thaum xaiv ib lub teb chaws los ntawm ib tug dropdown daim ntawv teev cov kev xaiv yuav tshwm sim nyob rau hauv lub nroog dropdown daim ntawv teev.
Yog li, cia peb saib ze dua ntawm qhov theoretical ntawm txoj haujlwm TSEEM CEEB (INDIRECT) thiab tshawb nrhiav cov piv txwv ntawm nws daim ntawv thov. Yog tias koj muaj lus qhia ntxiv lossis piv txwv, thov qhia rau lawv hauv cov lus pom.
Function 30: INDIRECT
muaj nuj nqi TSEEM CEEB (INDIRECT) rov qab qhov txuas tau teev tseg los ntawm cov kab ntawv.
Koj tuaj yeem siv INDIRECT muaj nuj nqi li cas?
Txij li thaum muaj nuj nqi TSEEM CEEB (INDIRECT) rov qab ib qhov txuas uas muab los ntawm cov kab ntawv, koj tuaj yeem siv nws rau:
- Tsim qhov tsis hloov pauv thawj qhov txuas.
- Tsim ib qho kev siv rau qhov muaj npe zoo li qub.
- Tsim ib qhov txuas siv daim ntawv, kab, thiab kab ntawv.
- Tsim ib qho tsis hloov pauv ntawm cov lej.
Syntax INDIRECT (INDIRECT)
muaj nuj nqi TSEEM CEEB (INDIRECT) muaj cov syntax hauv qab no:
INDIRECT(ref_text,a1)
ДВССЫЛ(ссылка_на_ячейку;a1)
- ref_text (link_to_cell) yog cov ntawv nyeem ntawm qhov txuas.
- a1 - yog tias sib npaug rau TRUE (TRUE) lossis tsis tau teev tseg, ces tus qauv ntawm qhov txuas yuav raug siv A1; thiab yog FALSE (FALSE), ces tus style r1c1 ua.
Traps INDIRECT (INDIRECT)
- muaj nuj nqi TSEEM CEEB (INDIRECT) raug suav rov qab thaum twg cov txiaj ntsig hauv Excel hloov pauv. Qhov no tuaj yeem ua rau koj cov phau ntawv ua haujlwm qeeb heev yog tias cov haujlwm tau siv ntau cov qauv.
- Yog muaj nuj nqi TSEEM CEEB (INDIRECT) tsim qhov txuas mus rau lwm phau ntawv ua haujlwm Excel, phau ntawv ua haujlwm yuav tsum qhib lossis cov qauv yuav qhia txog qhov yuam kev #REF! (#LINK!).
- Yog muaj nuj nqi TSEEM CEEB (INDIRECT) hais txog ntau yam uas dhau ntawm kab thiab kab txwv, cov qauv yuav qhia txog qhov yuam kev #REF! (#LINK!).
- muaj nuj nqi TSEEM CEEB (INDIRECT) tsis tuaj yeem hais txog qhov muaj npe dynamic.
Piv txwv 1: Tsim qhov tsis hloov pauv thawj qhov txuas
Hauv thawj qhov piv txwv, kab C thiab E muaj tib tus lej, lawv cov lej suav nrog kev ua haujlwm sum (SUM) kuj yog tib yam. Txawm li cas los xij, cov qauv sib txawv me ntsis. Hauv cell C8, tus qauv yog:
=SUM(C2:C7)
=СУММ(C2:C7)
Hauv cell E8, kev ua haujlwm TSEEM CEEB (INDIRECT) tsim qhov txuas mus rau lub cell pib E2:
=SUM(INDIRECT("E2"):E7)
=СУММ(ДВССЫЛ("E2"):E7)
Yog tias koj ntxig ib kab rau saum daim ntawv thiab ntxiv tus nqi rau Lub Ib Hlis (Lub Ib Hlis), ces tus nqi hauv kab C yuav tsis hloov. Cov mis yuav hloov, reacting rau qhov sib ntxiv ntawm ib kab:
=SUM(C3:C8)
=СУММ(C3:C8)
Txawm li cas los xij, kev ua haujlwm TSEEM CEEB (INDIRECT) kho E2 raws li lub xov tooj pib, yog li Lub Ib Hlis yuav cia li suav nrog hauv kev suav ntawm kab E tag nrho. Qhov kawg ntawm tes tau hloov pauv, tab sis qhov pib ntawm tes tsis tau cuam tshuam.
=SUM(INDIRECT("E2"):E8)
=СУММ(ДВССЫЛ("E2"):E8)
Piv txwv 2: Txuas mus rau qhov chaw muaj npe zoo li qub
muaj nuj nqi TSEEM CEEB (INDIRECT) tuaj yeem tsim ib qho kev siv rau cov npe muaj npe. Hauv qhov piv txwv no, cov hlwb xiav ua rau muaj ntau yam NumList. Tsis tas li ntawd, ib qho dynamic ntau kuj yog tsim los ntawm cov txiaj ntsig hauv kab B NumListDyn, nyob ntawm seb muaj pes tsawg tus lej hauv kab ntawv no.
Cov lej ntawm ob qhov sib txawv tuaj yeem suav los ntawm tsuas yog muab nws lub npe los ua qhov kev sib cav rau txoj haujlwm sum (SUM), raws li koj tuaj yeem pom hauv hlwb E3 thiab E4.
=SUM(NumList) или =СУММ(NumList)
=SUM(NumListDyn) или =СУММ(NumListDyn)
Es tsis txhob typing ib tug ntau lub npe rau hauv ib tug muaj nuj nqi sum (SUM), Koj tuaj yeem xa mus rau lub npe sau rau hauv ib lub hlwb ntawm daim ntawv ua haujlwm. Piv txwv li, yog lub npe NumList yog sau nyob rau hauv cell D7, ces tus qauv hauv cell E7 yuav zoo li no:
=SUM(INDIRECT(D7))
=СУММ(ДВССЫЛ(D7))
Hmoov tsis muaj nuj nqi TSEEM CEEB (INDIRECT) tsis tuaj yeem tsim qhov kev siv ntau yam, yog li thaum koj luam cov qauv no rau hauv cell E8, koj yuav tau txais qhov yuam kev #REF! (#LINK!).
Piv txwv 3: Tsim ib qhov txuas siv daim ntawv, kab, thiab kab ntawv
Koj tuaj yeem tsim qhov txuas tau yooj yim raws li kab thiab kab zauv, nrog rau siv tus nqi FALSE (FALSE) rau qhov kev sib cav thib ob. TSEEM CEEB (INDIRECT). Qhov no yog li cas qhov link style yog tsim r1c1 ua. Hauv qhov piv txwv no, peb ntxiv cov npe ntawv rau qhov txuas - 'MyLinks'!R2C2
=INDIRECT("'"&B3&"'!R"&C3&"C"&D3,FALSE)
=ДВССЫЛ("'"&B3&"'!R"&C3&"C"&D3;ЛОЖЬ)
Piv txwv 4: Tsim cov lej tsis hloov pauv
Qee zaum koj yuav tsum siv cov lej ntawm cov lej hauv Excel cov qauv. Hauv qhov piv txwv hauv qab no, peb xav kom nruab nrab ntawm 3 tus lej loj tshaj plaws hauv kab B. Cov lej tuaj yeem nkag mus rau hauv cov qauv, raws li ua tiav hauv cell D4:
=AVERAGE(LARGE(B1:B8,{1,2,3}))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;{1;2;3}))
Yog tias koj xav tau cov array loj dua, ces koj tsis zoo li xav nkag mus rau txhua tus lej hauv cov qauv. Qhov kev xaiv thib ob yog siv txoj haujlwm LEEJ (ROW), raws li ua tiav hauv cov qauv array nkag hauv cell D5:
=AVERAGE(LARGE(B1:B8,ROW(1:3)))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(1:3)))
Qhov kev xaiv thib peb yog siv lub luag haujlwm LEEJ (STRING) nrog rau TSEEM CEEB (INDIRECT), raws li ua tiav nrog cov qauv array hauv cell D6:
=AVERAGE(LARGE(B1:B8,ROW(INDIRECT("1:3"))))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(ДВССЫЛ("1:3"))))
Qhov tshwm sim rau tag nrho 3 cov qauv yuav zoo ib yam:
Txawm li cas los xij, yog tias kab tau muab tso rau saum toj ntawm daim ntawv, tus qauv thib ob yuav rov qab qhov tshwm sim tsis raug vim qhov tseeb tias cov lus qhia hauv cov qauv yuav hloov nrog rau kab hloov. Tam sim no, tsis yog qhov nruab nrab ntawm peb tus lej loj tshaj plaws, cov qauv rov qab qhov nruab nrab ntawm 3rd, 4th, thiab 5th loj tshaj tus lej.
Siv cov haujlwm TSEEM CEEB (INDIRECT), tus qauv thib peb khaws cov kab lus kom raug thiab txuas ntxiv qhia qhov tseeb.