Imisebenzi engama-30 ye-Excel ezinsukwini ezingama-30: ADDRESS

Izolo kumarathon 30 Excel isebenza ngezinsuku ezingama-30 sithole izakhi zelungu elifanayo sisebenzisa umsebenzi MATCH (SESHA) futhi uthole ukuthi isebenza kahle eqenjini elinezinye izici ezifana nalezi I-VLOOKUP (VLOOKUP) kanye INDEX (INDEX).

Ngosuku lwama-20 lwemarathon yethu, sizonikela ngesifundo somsebenzi ADDRESS (IKHELI). Ibuyisela ikheli leseli ngefomethi yombhalo isebenzisa umugqa nenombolo yekholomu. Ingabe siyalidinga leli kheli? Ingabe okufanayo kungenziwa nangeminye imisebenzi?

Ake sibheke imininingwane yomsebenzi ADDRESS (IKHELI) kanye nezibonelo zokufunda zokusebenza nalo. Uma unolwazi olwengeziwe noma izibonelo, sicela wabelane ngazo kumazwana.

Umsebenzi 20: IKHELI

Umsebenzi ADDRESS (ADDRESS) ibuyisela ireferensi yeseli njengombhalo osuselwe kumugqa nenombolo yekholomu. Ingabuyisela ikheli lesitayela sesixhumanisi esiphelele noma esihlobene. A1 or I-R1C1. Ngaphezu kwalokho, igama leshidi lingafakwa kumphumela.

Umsebenzi we-ADDRESS ungasetshenziswa kanjani?

Umsebenzi ADDRESS (ADDRESS) ingabuyisela ikheli leseli, noma isebenze ngokuhlanganyela neminye imisebenzi ku:

  • Thola ikheli leseli elinikeziwe umugqa nenombolo yekholomu.
  • Thola inani leseli ngokwazi umugqa nenombolo yekholomu.
  • Buyisa ikheli leseli elinenani elikhulu kakhulu.

I-syntax ADDRESS (ADDRESS)

Umsebenzi ADDRESS (ADDRESS) ine-syntax elandelayo:

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

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

  • inombolo_ye-abs (link_type) - uma ilingana 1 noma kungashiwongo nhlobo, umsebenzi uzobuyisela ikheli eliphelele ($A$1). Ukuze uthole ikheli elihlobene (A1), sebenzisa inani 4. Ezinye izinketho: 2=A$1, 3=$A1.
  • a1 - uma IQINISO (TRUE) noma ingacacisiwe nhlobo, umsebenzi ubuyisela ireferensi ngesitayela A1, uma AMANGA (FALSE), bese ngesitayela I-R1C1.
  • Ishidi_umbhalo (igama_leshidi) - igama leshidi lingashiwo uma ufuna ukulibona kumphumela obuyiselwe umsebenzi.

Izicupho ADDRESS

Umsebenzi ADDRESS (ADDRESS) ibuyisela kuphela ikheli leseli njengochungechunge lombhalo. Uma udinga inani leseli, lisebenzise njengempikiswano yokusebenza INDALO (INDIRECT) noma sebenzisa elinye lamafomula aboniswe kuSibonelo sesi-2.

Isibonelo 1: Thola ikheli leselula ngomugqa nenombolo yekholomu

Ukusebenzisa imisebenzi ADDRESS (IKHELI) Ungathola ikheli leseli njengombhalo usebenzisa umugqa nenombolo yekholomu. Uma ufaka lezi zimpikiswano ezimbili kuphela, umphumela uzoba ikheli eliphelele elibhalwe ngesitayela sesixhumanisi A1.

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

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

Okuphelele noma isihlobo

Uma ungacacisi inani le-agumenti inombolo_ye-abs (reference_type) kufomula, umphumela uyireferensi ephelele.

Ukuze ubone ikheli njengesixhumanisi esihlobene, ungashintshanisa njengengxabano inombolo_ye-abs (inkomba_uhlobo) inani 4.

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

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

I-A1 noma i-R1C1

Ukwenza izixhumanisi zesitayela I-R1C1, esikhundleni sesitayela esizenzakalelayo A1, Kumelwe ucacise FALSE ekungqubuzaneni a1.

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

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

Igama leshidi

Impikiswano yokugcina igama leshidi. Uma udinga leli gama kumphumela, licacise njengengxabano umbhalo_weshidi (igama_leshidi).

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

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

Isibonelo sesi-2: Thola inani leseli usebenzisa inombolo yomugqa nekholomu

Umsebenzi ADDRESS (ADDRESS) ibuyisela ikheli leseli njengombhalo, hhayi njengesixhumanisi esivumelekile. Uma udinga ukuthola inani leseli, ungasebenzisa umphumela obuyiswe umsebenzi ADDRESS (ADDRESS), njengempikiswano yokuthi INDALO (INDIRECT). Sizofunda umsebenzi INDALO (I-INDIRECT) kamuva ku-marathon 30 Excel isebenza ngezinsuku ezingama-30.

=INDIRECT(ADDRESS(C2,C3))

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

Umsebenzi INDALO (INDIRECT) ingasebenza ngaphandle komsebenzi ADDRESS (IKHELI). Nakhu ukuthi ungakwenza kanjani, usebenzisa i-opharetha yokuhlanganisa “&“, phuphuthekisa ikheli olifunayo ngesitayela I-R1C1 futhi ngenxa yalokho uthole inani leseli:

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

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

Umsebenzi INDEX (INDEX) ingase futhi ibuyisele inani leseli uma umugqa nenombolo yekholomu kucacisiwe:

=INDEX(1:5000,C2,C3)

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

1:5000 ziyimigqa yokuqala engu-5000 yeshidi le-Excel.

Isibonelo sesi-3: Buyisa ikheli leseli elinenani eliphezulu

Kulesi sibonelo, sizothola iseli elinenani eliphakeme futhi sisebenzise umsebenzi ADDRESS (IKHELI) ukuze uthole ikheli lakhe.

Umsebenzi MAX (MAX) ithola inombolo enkulu kukholomu C.

=MAX(C3:C8)

=МАКС(C3:C8)

Okulandelayo kuza umsebenzi ADDRESS (IKHELI) kuhlanganiswe ne MATCH (MATCH), othola inombolo yomugqa, kanye IKHOLOMU (COLUMN), ecacisa inombolo yekholomu.

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

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

shiya impendulo