Mukati
Nezuro mumarathon 30 Excel inoshanda mumazuva makumi matatu takawana zvinhu zvemutsetse tichishandisa basa MATCH (TSVAGA) uye wakaona kuti inoshanda zvakanaka muchikwata chine zvimwe zvinhu zvakaita se VLOOKUP (VLOOKUP) uye INDEX (INDEX).
Pazuva rechi20 remarathon edu, tichazvipira kudzidza kwebasa racho ADDRESS (ADDRESS). Inodzosa kero yesero mufomati yemavara uchishandisa mutsara uye nhamba yekoramu. Tinoda kero iyi here? Zvimwe chetezvo zvinogona kuitwa nemamwe mabasa here?
Ngatitarisei pane zvakadzama zvebasa racho ADDRESS (ADDRESS) uye mienzaniso yekudzidza yekushanda nayo. Kana iwe uine rumwe ruzivo kana mienzaniso, ndapota igovera ivo mune zvakataurwa.
Basa rechi20: ADDRESS
basa ADDRESS (ADDRESS) inodzosa sero rereferenzi semavara zvichienderana nemutsara uye nhamba yekoramu. Inogona kudzosa absolute kana hama yekubatanidza-maitiro kero. A1 or R1C1. Mukuwedzera, iro zita repepa rinogona kuverengerwa mumhedzisiro.
Ko ADDRESS basa rinogona kushandiswa sei?
basa ADDRESS (ADDRESS) inogona kudzosera kero yesero, kana kushanda pamwe nemamwe mabasa ku:
- Tora kero yesero yakapihwa mutsara uye nhamba yekoramu.
- Tsvaga kukosha kwesero nekuziva mutsara uye nhamba yekoramu.
- Dzosa kero yesero ine kukosha kukuru.
Syntax ADDRESS (ADDRESS)
basa ADDRESS (ADDRESS) ine chirevo chinotevera:
ADDRESS(row_num,column_num,[abs_num],[a1],[sheet_text])
АДРЕС(номер_строки;номер_столбца;[тип_ссылки];[а1];[имя_листа])
- abs_num (link_type) - kana zvakaenzana 1 kana isina kutaurwa zvachose, basa racho rinodzorera kero yakakwana ($ A $ 1). Kuti uwane iyo hama kero (A1), shandisa kukosha 4. Dzimwe sarudzo: 2=A$1, 3=$A1.
- a1 - kana CHOKWADI (CHOKWADI) kana chisina kutaurwa zvachose, basa racho rinodzorera referensi muchimiro A1, kana NHEMA (NHEMA), ipapo muchimiro R1C1.
- Sheet_text (sheet_name) - zita repepa rinogona kutsanangurwa kana iwe uchida kuiona mumhedzisiro yakadzoserwa nebasa.
Misungo ADDRESS
basa ADDRESS (ADDRESS) inodzosa kero yesero chete setambo yemavara. Kana iwe uchida kukosha kwesero, shandisa iyo sebasa nharo INDIRECT (INDIRECT) kana kushandisa imwe yemamwe mafomula anoratidzwa muMuenzaniso 2.
Muenzaniso 1: Tora kero yesero nemutsara uye nhamba yekoramu
Kushandisa mabasa ADDRESS (ADDRESS) Unogona kuwana kero yesero semavara uchishandisa mutsara uye nhamba yekoramu. Kana iwe ukangoisa idzi nharo mbiri chete, mhedzisiro ichave yakazara kero yakanyorwa mune yekubatanidza maitiro A1.
=ADDRESS($C$2,$C$3)
=АДРЕС($C$2;$C$3)
Mhedziso kana hama
Kana ukasataura kukosha kwekupokana abs_num (reference_type) mufomula, mhedzisiro ireferensi yakakwana.
Kuti uone kero sechinongedzo chehukama, unogona kutsiva senharo abs_num (reference_type) kukosha 4.
=ADDRESS($C$2,$C$3,4)
=АДРЕС($C$2;$C$3;4)
A1 kana R1C1
To style links R1C1, pachinzvimbo chemaitiro ekutanga A1, Unofanira kutsanangura FALSE pakupokana a1.
=ADDRESS($C$2,$C$3,1,FALSE)
=АДРЕС($C$2;$C$3;1;ЛОЖЬ)
Sheet name
Nharo yekupedzisira izita rebepa. Kana iwe uchida zita iri mumhedzisiro, ritsanangure senharo sheet_text (sheet_zita).
=ADDRESS($C$2,$C$3,1,TRUE,"Ex02")
=АДРЕС($C$2;$C$3;1;ИСТИНА;"Ex02")
Muenzaniso 2: Tsvaga kukosha kwesero uchishandisa mutsara uye nhamba yekoramu
basa ADDRESS (ADDRESS) inodzosa kero yesero semavara, kwete sechinhu chinongedzo. Kana iwe uchida kuwana kukosha kwesero, unogona kushandisa mhedzisiro yakadzoserwa nebasa ADDRESS (ADDRESS), senharo ye INDIRECT (INDIRECT). Tichadzidza basa INDIRECT (INDIRECT) gare gare mu marathon 30 Excel inoshanda mumazuva makumi matatu.
=INDIRECT(ADDRESS(C2,C3))
=ДВССЫЛ(АДРЕС(C2;C3))
basa INDIRECT (INDIRECT) inogona kushanda pasina basa ADDRESS (ADDRESS). Heano maitiro aungaite, uchishandisa concatenation opareta "&“, pofomadza kero yaunoda muchimiro R1C1 uye nekudaro tora kukosha kwesero:
=INDIRECT("R"&C2&"C"&C3,FALSE)
=ДВССЫЛ("R"&C2&"C"&C3;ЛОЖЬ)
basa INDEX (INDEX) inogona zvakare kudzosa kukosha kwesero kana mutsara uye nhamba yekoramu yatsanangurwa:
=INDEX(1:5000,C2,C3)
=ИНДЕКС(1:5000;C2;C3)
1:5000 ndiyo mitsetse 5000 yekutanga yeExcel sheet.
Muenzaniso 3: Dzosera kero yesero ine kukosha kukuru
Mumuenzaniso uyu, tichawana sero rine kukosha kwepamusoro uye shandisa basa racho ADDRESS (ADDRESS) kuti uwane kero yake.
basa MAX (MAX) inowana huwandu hwepamusoro mukoramu C.
=MAX(C3:C8)
=МАКС(C3:C8)
Kunotevera kunouya basa ADDRESS (ADDRESS) yakasanganiswa ne MATCH (MATCH), iyo inowana nhamba yemutsara, uye COLUMN (COLUMN), iyo inotsanangura nhamba yekoramu.
=ADDRESS(MATCH(F3,C:C,0),COLUMN(C2))
=АДРЕС(ПОИСКПОЗ(F3;C:C;0);СТОЛБЕЦ(C2))