Mukati
Makorokoto! Wasvika pazuva rekupedzisira remarathon 30 Excel inoshanda mumazuva makumi matatu. Hwave rwendo rurefu uye runonakidza panguva iyo iwe wakadzidza zvinhu zvakawanda zvinobatsira nezveExcel mabasa.
Pazuva remakumi maviri nenhatu remarathon, isu tichapa chidzidzo chebasa racho INDIRECT (INDIRECT), inodzosa chinongedzo chakataurwa netambo yemavara. Nebasa iri, unogona kugadzira zvinyorwa zvinotsamira pasi-pasi. Semuenzaniso, kana uchisarudza nyika kubva pane yekudonhedza rondedzero inotarisa kuti ndedzipi sarudzo dzichaonekwa mudhorobha rekudonhedza rondedzero.
Saka, ngatitarisei zvakanyanya chikamu chetioretical chebasa racho INDIRECT (INDIRECT) uye ongorora mienzaniso inoshanda yekushandiswa kwayo. Kana iwe uine rumwe ruzivo kana mienzaniso, ndapota igovera ivo mune zvakataurwa.
Basa rechi30: INDIRECT
basa INDIRECT (INDIRECT) inodzosa chinongedzo chakataurwa netambo yemavara.
Ungashandisa sei iyo INDIRECT basa?
Sezvo basa INDIRECT (INDIRECT) inodzosera chinongedzo chakapihwa nerunyoro tambo, unogona kuishandisa ku:
- Gadzira chinongedzo chekutanga chisingachinji.
- Gadzira referensi kune imwe nhanho ine zita renji.
- Gadzira chinongedzo uchishandisa pepa, mutsara, uye ruzivo rwekoramu.
- Gadzira nhamba isingachinji-chinji yenhamba.
Syntax INDIRECT (INDIRECT)
basa INDIRECT (INDIRECT) ine chirevo chinotevera:
INDIRECT(ref_text,a1)
ДВССЫЛ(ссылка_на_ячейку;a1)
- ref_text (link_to_sero) ndiwo mavara ezvekubatanidza.
- a1 - kana yakaenzana neCHOKWADI (CHOKWADI) kana isina kutaurwa, ipapo maitiro ekubatanidza achashandiswa A1; uye kana INHEMA (NHEMA), ipapo manyorerwo acho R1C1.
Traps INDIRECT (INDIRECT)
- basa INDIRECT (INDIRECT) inoverengerwa pese painochinja maitiro ari muExcel sheet sheet. Izvi zvinogona kuderedza zvakanyanya bhuku rako rekushanda kana basa racho richishandiswa mune dzakawanda mafomula.
- Kana basa INDIRECT (INDIRECT) inogadzira chinongedzo kune rimwe bhuku rebasa reExcel, iro bhuku rebasa rinofanira kuvhurika kana fomula inoshuma kukanganisa. #REF! (#LINK!).
- Kana basa INDIRECT (INDIRECT) inoreva renji inodarika mutsara uye mbiru inogumira, fomula ichashuma kukanganisa #REF! (#LINK!).
- basa INDIRECT (INDIRECT) haikwanise kureva renji ine mazita.
Muenzaniso 1: Gadzira chinongedzo chekutanga chisingachinji
Mumuenzaniso wekutanga, makoramu C neE ane nhamba dzakafanana, uwandu hwawo hunoverengerwa pachishandiswa basa SUM (SUM) zvakafanana. Zvisinei, mafomula acho akasiyana zvishoma. Muchitokisi C8, fomula ndeiyi:
=SUM(C2:C7)
=СУММ(C2:C7)
Musero E8, basa racho INDIRECT (INDIRECT) inogadzira chinongedzo kune yekutanga sero E2:
=SUM(INDIRECT("E2"):E7)
=СУММ(ДВССЫЛ("E2"):E7)
Kana iwe ukaisa mutsara pamusoro pebepa uye wowedzera kukosha kwaNdira (Jan), ipapo huwandu huri mukoramu C haizochinji. Iyo formula ichachinja, ichiita nezvekuwedzera kwemutsara:
=SUM(C3:C8)
=СУММ(C3:C8)
Zvisinei, basa INDIRECT (INDIRECT) inogadzirisa E2 seyokutanga sero, saka Ndira anosanganisirwa otomatiki mukuverenga kolamu E matomu. Sero rekupedzisira rachinja, asi sero rekutanga harina kukanganiswa.
=SUM(INDIRECT("E2"):E8)
=СУММ(ДВССЫЛ("E2"):E8)
Muenzaniso 2: Batanidza kune yakamira yakanzi renji
basa INDIRECT (INDIRECT) inogona kugadzira referensi kune yakapihwa zita. Mumuenzaniso uyu, masero ebhuruu anoumba huwandu NumList. Uye zvakare, iyo ine simba renji inogadzirwawo kubva kuhunhu huri mukoramu B NumListDyn, zvichienderana nehuwandu hwenhamba dziri muchikamu chino.
Huwandu hwemapoka ese ari maviri hunogona kuverengerwa nekungopa zita rayo senharo kune basa SUM (SUM), sezvaungaona mumasero E3 uye E4.
=SUM(NumList) или =СУММ(NumList)
=SUM(NumListDyn) или =СУММ(NumListDyn)
Panzvimbo pekunyora zita remhando kuita basa SUM (SUM), Unogona kureva zita rakanyorwa mune rimwe masero epepa rebasa. Somuenzaniso, kana zita NumList yakanyorwa muchitokisi D7, ipapo fomula muchitokisi E7 ichaita seizvi:
=SUM(INDIRECT(D7))
=СУММ(ДВССЫЛ(D7))
Zvinosuruvarisa basa INDIRECT (INDIRECT) haigone kugadzira referensi renji, saka kana iwe ukakopa iyi fomula pasi musero E8, iwe unowana kukanganisa. #REF! (#LINK!).
Muenzaniso 3: Gadzira chinongedzo uchishandisa pepa, mutsara, uye ruzivo rwekoramu
Iwe unogona nyore kugadzira chinongedzo chinoenderana nemutsara uye nhamba dzekoramu, pamwe nekushandisa kukosha kweKUNYEPA (FALSE) kune yechipiri basa nharo. INDIRECT (INDIRECT). Aya ndiwo magadzirirwo akaitwa chimiro chinongedzo R1C1. Mumuenzaniso uyu, tawedzerawo zita reshizha kune chinongedzo - 'MyLinks'!R2C2
=INDIRECT("'"&B3&"'!R"&C3&"C"&D3,FALSE)
=ДВССЫЛ("'"&B3&"'!R"&C3&"C"&D3;ЛОЖЬ)
Muenzaniso 4: Gadzira nhamba isingachinji-chinji yenhamba
Dzimwe nguva iwe unofanirwa kushandisa huwandu hwenhamba muExcel mafomula. Mumuenzaniso unotevera, tinoda kuenzana nhamba huru nhatu muchikamu B. Nhamba dzinogona kuiswa mufomula, sezvinoitwa muchitokisi D3:
=AVERAGE(LARGE(B1:B8,{1,2,3}))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;{1;2;3}))
Kana iwe uchida akakura array, saka haugone kuda kuisa nhamba dzese mufomula. Yechipiri sarudzo ndeye kushandisa basa mutsara (ROW), sezvakaitwa muhurongwa hwefomura yakapinda muchitokisi D5:
=AVERAGE(LARGE(B1:B8,ROW(1:3)))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(1:3)))
Yechitatu sarudzo ndeye kushandisa basa mutsara (STRING) pamwe chete INDIRECT (INDIRECT), sezvakaitwa neyakarongwa fomula muchitokisi D6:
=AVERAGE(LARGE(B1:B8,ROW(INDIRECT("1:3"))))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(ДВССЫЛ("1:3"))))
Mhedzisiro yeese mafomula matatu ichave yakafanana:
Zvakadaro, kana mitsetse ikaiswa pamusoro pebepa, fomula yechipiri inodzosera mhedzisiro isiriyo nekuda kwekuti mareferensi ari mufomula achachinja pamwe nekuchinja kwemutsara. Zvino, panzvimbo yeavhareji yenhamba huru nhatu, fomula inodzosa avhareji yenhamba huru dzechitatu, 3, uye yechishanu.
Kushandisa mabasa INDIRECT (INDIRECT), fomula yechitatu inochengeta mareferensi chaiwo emitsara uye inoramba ichiratidza mhedzisiro chaiyo.