tirotiro
Inanahi i te marathon 30 Excel mahi i roto i te 30 ra i kitea e matou nga huānga o te huinga ma te whakamahi i te mahi MATCH (Rapu) ka kitea he pai te mahi i roto i te roopu me etahi atu ahuatanga penei TANGATA (VLOOKUP) me Index (INDEX).
I te 20 o nga ra o to maatau marathon, ka whakapau kaha matou ki te ako mo te mahi ADDRESS (WĀTAURANGA). Ka whakahokia te wāhitau pūtau ki te whakatakotoranga kuputuhi mā te tau haupae me te tīwae. Kei te hiahia tatou i tenei waahi noho? Ka taea ano te mahi me etahi atu mahi?
Kia titiro tatou ki nga korero mo te mahi ADDRESS (ADDRESS) me te ako i nga tauira mo te mahi tahi. Mena kei a koe etahi atu korero, tauira ranei, tohaina ki nga korero.
Mahi 20: ADDRESS
taumahi ADDRESS (ADDRESS) he whakahoki tohutoro pūtau hei kuputuhi i runga i te rarangi me te tau tīwae. Ka taea e ia te whakahoki mai i tetahi wahitau momo hono-hononga tuuturu ranei. A1 or R1C1. I tua atu, ka taea te whakauru i te ingoa rau ki te hua.
Me pehea te whakamahi i te mahi ADDRESS?
taumahi ADDRESS Ka taea e (ADDRESS) te whakahoki i te wahitau o te pūtau, te mahi tahi me etahi atu mahi ki:
- Tikina te wāhitau pūtau kua homai te rarangi me te tau tīwae.
- Kimihia uara pūtau mā te mōhio ki te tau haupae me te tīwae.
- Whakahokia te wāhitau o te pūtau me te uara nui rawa atu.
Wetereo ADDRESS (ADDRESS)
taumahi ADDRESS (ADDRESS) te wetereo e whai ake nei:
ADDRESS(row_num,column_num,[abs_num],[a1],[sheet_text])
АДРЕС(номер_строки;номер_столбца;[тип_ссылки];[а1];[имя_листа])
- abs_num (momo_hononga) – mena he rite 1 karekau ranei i tohua, ka whakahokia e te mahi te wahitau tuturu ($A$1). Hei tiki i te wahitau whanaunga (A1), whakamahia te uara 4. Ētahi atu kōwhiringa: 2=A$1, 3=$A1.
- a1 – ki te TRUE (TRUE) karekau ranei i tohua, ka whakahokia mai e te mahi he tohutoro ki te ahua A1, ki te FALSE (FALSE), ka noho ki te ahua R1C1.
- Rau_kuputuhi (sheet_name) – ka taea te tohu te ingoa o te rau mena ka hiahia koe ki te kite i roto i te hua i whakahokia mai e te mahi.
Rore ADDRESS
taumahi ADDRESS (ADDRESS) ka whakahoki anake i te wāhitau o te pūtau hei aho kuputuhi. Ki te hiahia koe ki te uara o te pūtau, whakamahia hei tohenga mahi WHAKAHUI (KORE) ka whakamahi ranei i tetahi o nga tauira rereke e whakaatuhia ana i te Tauira 2.
Tauira 1: Tikina te wāhitau pūtau mā te tau haupae me te tīwae
Te whakamahi i nga mahi ADDRESS (ADDRESS) Ka taea e koe te tiki i te wahitau pūtau hei kuputuhi ma te whakamahi i te rarangi me te tau tīwae. Mena ka whakauru koe i enei tohenga e rua anake, ko te hua ko te wahitau tuturu kua tuhia ki te momo hono A1.
=ADDRESS($C$2,$C$3)
=АДРЕС($C$2;$C$3)
Tino, whanaunga ranei
Mena karekau koe e tohu he uara tohenga abs_num (tohu_momo) i roto i te tauira, ko te hua he tohutoro tino.
Hei kite i te wahitau hei hononga hono, ka taea e koe te whakakapi hei tohenga abs_num (tohu_momo) uara 4.
=ADDRESS($C$2,$C$3,4)
=АДРЕС($C$2;$C$3;4)
A1 R1C1 ranei
Ki te momo hononga R1C1, hei utu mo te kāhua taunoa A1, Me whakapūtā koe HĒ mō te tautohe a1.
=ADDRESS($C$2,$C$3,1,FALSE)
=АДРЕС($C$2;$C$3;1;ЛОЖЬ)
Ingoa rau
Ko te tohenga whakamutunga ko te ingoa o te rau. Mena ka hiahia koe ki tenei ingoa i roto i te hua, tohua hei tohenga rau_kupu (ingoa_pee).
=ADDRESS($C$2,$C$3,1,TRUE,"Ex02")
=АДРЕС($C$2;$C$3;1;ИСТИНА;"Ex02")
Tauira 2: Kimihia te uara pūtau mā te whakamahi i te tau haupae me te tīwae
taumahi ADDRESS (ADDRESS) ka whakahoki te wāhitau o te pūtau hei kuputuhi, ehara i te hononga whaimana. Mena ka hiahia koe ki te tiki i te uara o te pūtau, ka taea e koe te whakamahi i te hua i whakahokia mai e te mahi ADDRESS (ADDRESS), hei tohenga mo WHAKAHUI (KORERO). Ka ako tatou i te mahi WHAKAHUI (IDIRECT) i muri mai i te marathon 30 Excel mahi i roto i te 30 ra.
=INDIRECT(ADDRESS(C2,C3))
=ДВССЫЛ(АДРЕС(C2;C3))
taumahi WHAKAHUI (IDIRECT) ka taea te mahi me te kore te mahi ADDRESS (WĀTAURANGA). Anei me pehea e taea ai e koe, ma te whakamahi i te kaiwhakahaere hono "&“, matapo i te wahitau e hiahiatia ana i roto i te ahua R1C1 a na reira ka whiwhi te uara o te pūtau:
=INDIRECT("R"&C2&"C"&C3,FALSE)
=ДВССЫЛ("R"&C2&"C"&C3;ЛОЖЬ)
taumahi Index Ka taea hoki e (INDEX) te whakahoki i te uara o te pūtau mena ka tohua he tau haupae me te tīwae:
=INDEX(1:5000,C2,C3)
=ИНДЕКС(1:5000;C2;C3)
1:5000 ko nga rarangi tuatahi 5000 o te rau Excel.
Tauira 3: Whakahokia te wāhitau o te pūtau me te uara mōrahi
I roto i tenei tauira, ka kitea e tatou te pūtau me te uara teitei me te whakamahi i te mahi ADDRESS (ADDRESS) ki te tiki i tana wahi noho.
taumahi MAX Ka kitea e (MAX) te tau mōrahi i te tīwae C.
=MAX(C3:C8)
=МАКС(C3:C8)
Ka haere mai te mahi ADDRESS (ADDRESS) me te MATCH (MATCH), e kimi ana i te tau raina, me Tuhinga o mua (COLUMN), e tohu ana i te tau tīwae.
=ADDRESS(MATCH(F3,C:C,0),COLUMN(C2))
=АДРЕС(ПОИСКПОЗ(F3;C:C;0);СТОЛБЕЦ(C2))