30 Excel imagwira ntchito m'masiku 30: ADDRESS

Zamkatimu

Ntchito 20: ADDRESS
Chitsanzo 1: Pezani adilesi yam'manja ndi mzere ndi nambala
Chitsanzo 2: Pezani mtengo wa cell pogwiritsa ntchito mzere ndi nambala
Chitsanzo 3: Bweretsani adilesi ya cell yokhala ndi mtengo wokwanira

Dzulo pa marathon 30 Excel imagwira ntchito m'masiku 30 tinapeza zinthu zamagulu pogwiritsa ntchito ntchitoyi GANIZANI (SEARCH) ndipo adapeza kuti imagwira ntchito bwino pagulu lomwe lili ndi zina monga VLOOKUP (VLOOKUP) ndi INDEX (INDEX).

Pa tsiku la 20 la marathon athu, tidzapereka phunziro la ntchitoyi ADDRESS (ADDRESS). Imabwezeranso adilesi ya cell mumtundu wamawu pogwiritsa ntchito mzere ndi nambala yazanja. Kodi tikufuna adilesi iyi? Kodi zomwezo zingachitike ndi ntchito zina?

Tiyeni tiwone tsatanetsatane wa ntchitoyi ADDRESS (ADDRESS) ndi zitsanzo zamaphunziro akugwira nawo ntchito. Ngati muli ndi zina zowonjezera kapena zitsanzo, chonde gawanani nawo mu ndemanga.

Ntchito 20: ADDRESS

ntchito ADDRESS (ADDRESS) imabweretsanso chilolezo cha cell monga mawu otengera mzere ndi nambala ya gawo. Itha kubweretsanso adilesi yotsimikizika kapena yachibale. A1 or Mtengo wa R1C1. Kuphatikiza apo, dzina la pepala likhoza kuphatikizidwa muzotsatira.

Kodi ntchito ya ADDRESS ingagwiritsidwe ntchito bwanji?

ntchito ADDRESS (ADDRESS) ikhoza kubweza adilesi ya selo, kapena kugwira ntchito limodzi ndi ntchito zina ku:

Pezani adilesi yam'manja yopatsidwa mzere ndi nambala yazanja.
Pezani mtengo wa cell podziwa mzere ndi nambala yazanja.
Bweretsani adilesi ya selo yokhala ndi mtengo waukulu kwambiri.

Syntax ADDRESS (ADDRESS)

ntchito ADDRESS (ADDRESS) ili ndi mawu awa:

ADDRESS(row_num,column_num,[abs_num],[a1],[sheet_text])

АДРЕС(номер_строки;номер_столбца;[тип_ссылки];[а1];[имя_листа])

abs_nu (link_type) - ngati ofanana 1 kapena osatchulidwa nkomwe, ntchitoyi idzabwezera adilesi yonse ($ A $ 1). Kuti mupeze adilesi yachibale (A1), gwiritsani ntchito mtengowo 4. Zosankha zina: 2=A$1, 3=$A1.
a1 - ngati CHOONADI (CHOONA) kapena sichinatchulidwe nkomwe, ntchitoyi imabwezeretsanso kalembedwe A1, ngati ZABODZA (ZABODZA), ndiye mumayendedwe Mtengo wa R1C1.
Mapepala_lemba (sheet_name) - dzina la pepala likhoza kufotokozedwa ngati mukufuna kuliwona muzotsatira zomwe zabwezedwa ndi ntchitoyi.

Misampha ADDRESS

ntchito ADDRESS (ADDRESS) imabweza adilesi yokha ya cell ngati chingwe cholemba. Ngati mukufuna mtengo wa selo, igwiritseni ntchito ngati mtsutso wa ntchito KUMALO (INDIRECT) kapena gwiritsani ntchito imodzi mwa njira zomwe zasonyezedwa mu Chitsanzo 2.

Chitsanzo 1: Pezani adilesi yam'manja ndi mzere ndi nambala

Kugwiritsa ntchito ADDRESS (ADDRESS) Mutha kupeza adilesi yam'manja ngati mawu pogwiritsa ntchito mzere ndi nambala. Mukangoyika mikangano iwiriyi, zotsatira zake zidzakhala adilesi yolembedwa motsatira ulalo A1.

=ADDRESS($C$2,$C$3)

=АДРЕС($C$2;$C$3)

Mtheradi kapena wachibale

Ngati simutchula mtengo wotsutsa abs_nu (reference_type) mu chilinganizo, zotsatira zake ndi umboni wokwanira.

Kuti muwone adilesi ngati ulalo wachibale, mutha kusintha ngati mkangano abs_nu (reference_type) mtengo 4.

=ADDRESS($C$2,$C$3,4)

=АДРЕС($C$2;$C$3;4)

A1 kapena R1C1

Kupanga maulalo Mtengo wa R1C1, m'malo mwa sitayilo yokhazikika A1, Muyenera kutchula FALSE pa mkangano a1.

=ADDRESS($C$2,$C$3,1,FALSE)

=АДРЕС($C$2;$C$3;1;ЛОЖЬ)

Dzina lapepala

Mtsutso womaliza ndi dzina la pepala. Ngati mukufuna dzinali muzotsatira, tchulani ngati mkangano pepala_lemba (tsamba_dzina).

=ADDRESS($C$2,$C$3,1,TRUE,"Ex02")

=АДРЕС($C$2;$C$3;1;ИСТИНА;"Ex02")

Chitsanzo 2: Pezani mtengo wa cell pogwiritsa ntchito mzere ndi nambala

ntchito ADDRESS (ADDRESS) imabweretsanso adilesi ya selo ngati mawu, osati ngati ulalo wovomerezeka. Ngati mukufuna kupeza mtengo wa selo, mutha kugwiritsa ntchito zotsatira zomwe zabwezedwa ndi ntchitoyi ADDRESS (ADDRESS), monga mtsutso wa KUMALO (INDIRECT). Tiphunzira ntchito KUMALO (INDIRECT) pambuyo pake mu marathon 30 Excel imagwira ntchito m'masiku 30.

=INDIRECT(ADDRESS(C2,C3))

=ДВССЫЛ(АДРЕС(C2;C3))

ntchito KUMALO (INDIRECT) imatha kugwira ntchito popanda ntchitoyi ADDRESS (ADDRESS). Umu ndi momwe mungathere, pogwiritsa ntchito concatenation operator "&", chititsani khungu adilesi yomwe mukufuna mumayendedwe ake Mtengo wa R1C1 ndipo chifukwa chake mumapeza mtengo wa cell:

=INDIRECT("R"&C2&"C"&C3,FALSE)

=ДВССЫЛ("R"&C2&"C"&C3;ЛОЖЬ)

ntchito INDEX (INDEX) ingathenso kubweza mtengo wa selo ngati mzere ndi nambala ya gawo zatchulidwa:

=INDEX(1:5000,C2,C3)

=ИНДЕКС(1:5000;C2;C3)

1:5000 ndi mizere 5000 yoyamba ya pepala la Excel.

Chitsanzo 3: Bweretsani adilesi ya cell yokhala ndi mtengo wokwanira

Mu chitsanzo ichi, tidzapeza selo lomwe lili ndi mtengo wapatali ndikugwiritsa ntchito ntchitoyi ADDRESS (ADDRESS) kuti mupeze adilesi yake.

ntchito Max (MAX) amapeza nambala yochuluka mugawo C.

=MAX(C3:C8)

=МАКС(C3:C8)

Kenako pakubwera ntchito ADDRESS (ADDRESS) ophatikizidwa ndi GANIZANI (MATCH), yomwe imapeza nambala ya mzere, ndi Danga (COLUMN), yomwe imatchula nambala yagawo.

=ADDRESS(MATCH(F3,C:C,0),COLUMN(C2))

=АДРЕС(ПОИСКПОЗ(F3;C:C;0);СТОЛБЕЦ(C2))