30 Excel e sebetsa ka matsatsi a 30: ADDRESS

Maobane lebelong la marathone 30 Excel e sebetsa ka matsatsi a 30 re fumane likarolo tsa sehlopha ho sebelisa mosebetsi TŠOANELO (BATLA) mme o fumane hore e sebetsa hantle sehlopheng se nang le likarolo tse ling joalo ka VLOOKUP (VLOOKUP) le INDEX (INDEX).

Ka letsatsi la bo20 la marathon ea rona, re tla fana ka thuto ea mosebetsi ADDRESS (ADDRESS). E khutlisetsa aterese ea sele ka mokhoa oa mongolo o sebelisa mohala le nomoro ea mohala. A na re hloka aterese ee? Na ho ka etsoa se tšoanang ka mesebetsi e meng?

Ha re shebeng lintlha tsa tšebetso ADDRESS (ADDRESS) le mehlala ea boithuto ea ho sebetsa le eona. Haeba u na le tlhaiso-leseling e eketsehileng kapa mehlala, ka kopo e arolelane maikutlong.

Mosebetsi oa 20: ADDRESS

mosebetsi ADDRESS (ADDRESS) e khutlisa referense ea sele e le mongolo o thehiloeng mothalong le nomorong ea kholomo. E ka khutlisa aterese e felletseng kapa e amanang le sehokelo. A1 or R1C1. Ho phaella moo, lebitso la lakane le ka kenyelletsoa sephethong.

Mosebetsi oa ADDRESS o ka sebelisoa joang?

mosebetsi ADDRESS (ADDRESS) e ka khutlisa aterese ea sele, kapa ea sebetsa mmoho le mesebetsi e meng ho:

  • Fumana aterese ea selefouno e fanoeng mola le nomoro ea kholomo.
  • Fumana boleng ba sele ka ho tseba mola le nomoro ea kholomo.
  • Khutlisetsa aterese ea sele e nang le boleng bo boholo.

Syntax ADDRESS (ADDRESS)

mosebetsi ADDRESS (ADDRESS) e na le mantsoe a latelang:

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

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

  • abs_num (link_type) - haeba e lekana 1 kapa e sa hlalosoa ho hang, ts'ebetso e tla khutlisa aterese e felletseng ($ A $ 1). Ho fumana aterese e amanang le eona (A1), sebelisa boleng 4. Likhetho tse ling: 2=A$1, 3=$A1.
  • a1 - haeba 'NETE ('NETE) kapa e sa hlalosoa ho hang, ts'ebetso e khutlisa referense ka setaele A1, haeba LESHANO (LESHANO), joale ka setaele R1C1.
  • Sheet_mongolo (sheet_name) - lebitso la lakane le ka hlalosoa haeba u batla ho le bona sephethong se khutlisitsoeng ke mosebetsi.

Maraba ADDRESS

mosebetsi ADDRESS (ADDRESS) e khutlisa feela aterese ea sele e le khoele ea mongolo. Haeba o hloka boleng ba sele, e sebelise joalo ka khang ea tšebetso TS'ELISO (INDIRECT) kapa sebelisa e 'ngoe ea mekhoa e meng e bontšitsoeng ho Mohlala 2.

Mohlala 1: Fumana aterese ea sele ka mola le nomoro ea mohala

Ho sebelisa mesebetsi ADDRESS (ADDRESS) U ka fumana aterese ea sele e le mongolo u sebelisa mola le nomoro ea kholomo. Haeba u kenya likhang tsena tse peli feela, sephetho e tla ba aterese e felletseng e ngotsoeng ka mokhoa oa sehokelo A1.

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

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

Felletseng kapa leloko

Haeba u sa hlalose boleng ba likhang abs_num (reference_type) ka foromo, sephetho ke tšupiso e felletseng.

Ho bona aterese e le sehokelo se amanang, o ka nka sebaka sa khang abs_num (reference_type) boleng 4.

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

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

A1 kapa R1C1

Ho etsa lihokelo R1C1, sebakeng sa mokhoa oa kamehla A1, O tlameha ho hlakisa FALSE bakeng sa ngangisano a1.

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

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

Lebitso la leqephe

Khang ea ho qetela ke lebitso la lakane. Haeba o hloka lebitso lena sephethong, le hlalose joalo ka khang mongolo_wa_lakane (lebitso_la leqephe).

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

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

Mohlala 2: Fumana boleng ba sele ka ho sebelisa nomoro ea mola le kholomo

mosebetsi ADDRESS (ADDRESS) e khutlisa aterese ea sele joalo ka mongolo, eseng joalo ka sehokelo se sebetsang. Haeba o hloka ho fumana boleng ba sele, o ka sebelisa sephetho se khutlisitsoeng ke tšebetso ADDRESS (ADDRESS), joalo ka khang ea TS'ELISO (TSELA). Re tla ithuta mosebetsi TS'ELISO (INDIRECT) hamorao lebelong la marathone 30 Excel e sebetsa ka matsatsi a 30.

=INDIRECT(ADDRESS(C2,C3))

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

mosebetsi TS'ELISO (INDIRECT) e ka sebetsa ntle le ts'ebetso ADDRESS (ADDRESS). Mona ke kamoo u ka khonang, u sebelisa "concatenation operator"&“, foufatsa aterese e batloang ka mokhoa oo R1C1 'me ka lebaka leo fumana boleng ba sele:

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

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

mosebetsi INDEX (INDEX) e ka khutlisa boleng ba sele haeba mola le kholumo li hlalositsoe:

=INDEX(1:5000,C2,C3)

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

1:5000 ke mela e 5000 ea pele ea leqephe la Excel.

Mohlala 3: Khutlisetsa aterese ea sele e nang le boleng bo phahameng

Mohlala ona, re tla fumana sele e nang le boleng bo phahameng mme re sebelise ts'ebetso ADDRESS (ADDRESS) ho fumana aterese ea hae.

mosebetsi MAX (MAX) e fumana palo e kholo ho kholomo C.

=MAX(C3:C8)

=МАКС(C3:C8)

Ka mor'a moo ho tla mosebetsi ADDRESS (ADDRESS) e kopantsoe le TŠOANELO (MATCH), e fumanang nomoro ea mohala, le lenane (COLUMN), e hlalosang nomoro ea kholomo.

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

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

Leave a Reply