Thaum koj paub yuav ua li cas siv cov haujlwm tsis ncaj hauv Excel, koj tuaj yeem rub cov ntaub ntawv los ntawm lwm cov nplooj ntawv, siv cov npe npe, thiab muab nws nrog lwm cov haujlwm los tsim cov cuab yeej siv tau ntau yam. Nws tuaj yeem siv me ntsis kev xyaum kom tau txais kev tuav nrog, tab sis nrog txoj haujlwm tsis ncaj, koj tuaj yeem ua tau ntau dua li qhov koj xav.
Cov lus qhia hauv kab lus no siv rau Excel rau Microsoft 365, Excel 2019, thiab Excel 2016.
Dab tsi yog Indirect Function?
Kev ua haujlwm tsis ncaj yog ib txoj hauv kev los hloov cov kab ntawv rau hauv kev siv. Ntawd yog, nws rub cov ntaub ntawv los ntawm kev siv mus rau lwm lub xov tooj lossis ntau yam. Nws tsim ib qho kev siv los ntawm cov ntawv nyeem, thiab tsis hloov thaum lub hlwb, kab, lossis kab hloov pauv, ntxiv, lossis tshem tawm los ntawm ntau qhov kev suav. Cov ntaub ntawv uas nws tsim tau raug soj ntsuam hauv lub sijhawm tiag tiag, yog li cov ntaub ntawv siv ib txwm raug rau cov ntaub ntawv nws tau kos los ntawm.
Yog tias xav tias tsis meej pem me ntsis, tsis txhob ntxhov siab. Cov mis tsis ncaj tuaj yeem nkag siab yooj yim nrog cov qauv siv tau thiab hauv kev xyaum. Yog tias tsis ntseeg, ua raws cov kauj ruam hauv qab no, thiab koj yuav tau txais lub hang ntawm nws sai sai no.
Siv Indirect Function Nrog Lub Npe Ntau
Npe npe nyob hauv Excel yog txoj hauv kev zoo los sau cov ntaub ntawv raws li ib qho kev siv, thiab kev ua haujlwm tsis ncaj ua rau rub cov ntaub ntawv los ntawm lawv yooj yim dua me ntsis. Ntawm no yog yuav ua li cas:
-
Qhib ib daim ntawv Excel nrog cov npe npe uas twb tau thov lawm. Hauv peb qhov piv txwv, peb muaj cov ntaub ntawv muag khoom los ntawm ntau yam khoom noj thiab dej qab zib, nrog rau cov nyiaj khwv tau los txhua hnub ntawm lub limtiam tau sau los ntawm cov npe uas muaj npe tom qab cov khoom.
Image -
Xaiv ib lub xov tooj rau koj lub npe npe, thiab sau ib qho ntawm lawv rau hauv nws. Hauv peb qhov piv txwv, peb siv Burgers. Ntxiv lwm cov npe xaiv thiab xim yog tias koj xav tau.
Image -
Xaiv lwm lub xovtooj uas koj xav kom cov zis tsis ncaj mus. Txij li thaum peb tab tom nrhiav ntxiv tag nrho cov khoom muag los ntawm lub lim tiam rau ib qho khoom noj tshwj xeeb, qhov no Burgers, peb yuav ntaus cov hauv qab no rau hauv lub cell:
=SUM(INDIRECT(G5)
-
Qhov no xaiv qhov kev ua haujlwm SUM, uas yuav siv qhov kev ua tsis ncaj los kos cov ntaub ntawv los ntawm ntau lub npe hauv cell G5, qhov no Burgers. Cov zis yog 3781, tag nrho cov muag khoom rau lub lim tiam rau Burgers.
Hauv peb qhov piv txwv, peb tuaj yeem hloov Burgers hauv cell G5 nrog Lemonade lossis Desserts, lwm qhov ob lub npe, thiab cov zis yuav hloov mus rau lawv qhov SUM tag nrho.
Image
Siv Indirect Function ntawm Ntau Daim Ntawv
Cov mis tsis ncaj ncees tseem muaj zog dua thaum koj siv nws los rub cov ntaub ntawv los ntawm lwm nplooj ntawv. Koj tsis tas yuav siv cov npe npe los ua nws, ib yam.
- Qhib koj cov ntaub ntawv Excel nrog ntau daim ntawv, lossis tsim lawv nrog txhua cov ntaub ntawv tsim nyog.
- Nyob rau hauv daim ntawv uas koj xav kom cov indirect tso zis mus, tsim ib lub xovtooj nrog lub npe ntawm daim ntawv koj xav kos cov ntaub ntawv los ntawm. Hauv peb qhov piv txwv, nws yog FoodSales.
-
Vim tias peb xav rub cov ntaub ntawv los ntawm peb FoodSales daim ntawv kom tag nrho cov naj npawb ntawm Burgers muag, peb ntaus cov hauv qab no (hloov cov Cell ntau yam thiab daim ntawv npe nrog koj tus kheej):
=SUM(INDIRECT(B4&"!B4:B10"))
Image - Qhov no xaiv nws ua SUM muaj nuj nqi, vim peb tab tom sim nrhiav tag nrho. Nws ces xaiv cell B4 raws li cov ntawv siv rau kev ua haujlwm tsis ncaj. Lub & coj los ua ke cov ntsiab lus ntawm qhov kev ua haujlwm no, ua raws li cov lus tsocai thiab exclamation point, thiab tom qab ntawd ntau lub hlwb uas peb xav kos cov ntaub ntawv los ntawm. B4 through B10
- Cov zis yog tag nrho ntawm Burger muag rau lub lim tiam ntawd. Thaum peb tsim ib daim ntawv tshiab FoodSales2 rau lub lim tiam tshiab nrog cov lej sib txawv, peb tsuas yog yuav tsum tau kho cov xov tooj ntawm tes B4 los hais FoodSales2 kom tau txais cov ntaub ntawv ntawm kev muag Burger rau lub lim tiam ntawd.
Siv qhov Indirect Function Nrog R1C1 Style Reference
Rau cov nplooj ntawv uas txuas ntxiv nthuav dav, qhov kev siv koj xav siv yuav tsis nyob hauv tib lub xovtooj, R1C1 Style cov lus qhia tuaj yeem siv nrog cov mis tsis ncaj los muab cov ntaub ntawv koj xav tau. Peb yuav txuas ntxiv siv peb cov qauv muag khoom noj ntawm no, tab sis xav txog nws yog rau cov ntawv ua haujlwm siab dua uas saib cov khoom muag txhua lub limtiam tag nrho.
- Qhib Excel daim ntawv nrog tag nrho cov ntaub ntawv koj xav kos los ntawm thiab xaiv ib lub xovtooj rau koj qhov kev tso tawm tsis ncaj ncees. Hauv peb qhov piv txwv, peb tab tom saib cov khoom noj txhua hli muag tag nrho thiab peb xav paub qhov muag tag nrho tam sim no rau lub hli.
-
Hauv peb qhov piv txwv, cov qauv zoo li no:
=INDIRECT("R12C"&COUNTA(12:12), FALSE)
Image - Kev ua haujlwm tsis ncaj yog siv R12 (kab 12) ua raws li C txhawm rau txheeb xyuas ib kab, kaw hauv cov lus hais. Lub & koom nrog ob feem ntawm txoj haujlwm ua ke. Peb tab tom siv COUNTA muaj nuj nqi los suav tag nrho cov hlwb tsis pub dawb nyob rau hauv kab 12 (xaiv kab lossis ntaus ntawv 12:12), ua raws li tus lej cim. FALSE xaiv qhov no raws li kev siv R1C1.
-
Cov zis tawm yog qhov kawg nkag hauv peb lub rooj, qhov no 8102, lossis $ 8, 102. Thaum peb ntxiv rau lub Plaub Hlis cov ntaub ntawv muag, tus lej muag khoom tshiab yuav hloov kho lub sijhawm tiag tiag.
Image
