Ayyukan Excel 30 a cikin kwanaki 30: ADDRESS

Jiya a gudun marathon 30 Excel yana aiki a cikin kwanaki 30 mun sami abubuwan da ke cikin tsararru ta amfani da aikin MATCH (BINCIN) kuma ya gano yana aiki sosai a cikin ƙungiya tare da wasu siffofi kamar KYAUTA (VLOOKUP) da INDEX (INDEX).

A ranar 20th na marathon mu, za mu sadaukar da nazarin aikin ADDRESS (ADDRESS). Yana mayar da adireshin tantanin halitta a tsarin rubutu ta amfani da jere da lambar shafi. Muna bukatar wannan adireshin? Za a iya yin haka tare da wasu ayyuka?

Bari mu dubi cikakkun bayanai game da aikin ADDRESS (ADDRESS) da kuma nazarin misalan aiki da shi. Idan kuna da ƙarin bayani ko misalai, da fatan za a raba su a cikin sharhi.

Aiki 20: ADDRESS

aiki ADDRESS (ADDRESS) yana dawo da bayanin tantanin halitta azaman rubutu akan layi da lambar shafi. Yana iya mayar da cikakken adireshin hanyar haɗin yanar gizo ko dangi. A1 or Saukewa: R1C1. Bugu da ƙari, ana iya haɗa sunan takardar a cikin sakamakon.

Ta yaya za a iya amfani da aikin ADDRESS?

aiki ADDRESS (ADDRESS) na iya mayar da adireshin tantanin halitta, ko aiki tare da wasu ayyuka zuwa:

  • Samu adireshin salula da aka ba jere da lambar shafi.
  • Nemo ƙimar tantanin halitta ta sanin layin layi da lambar shafi.
  • Koma adireshin tantanin halitta tare da mafi girman ƙima.

ADDRESS Syntax (ADDRESS)

aiki ADDRESS (ADDRESS) yana da ma'ana mai zuwa:

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

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

  • abs_num (link_type) - idan daidai 1 ko ba a bayyana ba kwata-kwata, aikin zai dawo da cikakken adireshin ($ A$1). Don samun adireshin dangi (A1), yi amfani da ƙimar 4. Wasu zaɓuɓɓuka: 2=A $1, 3=$A1.
  • a1 - idan GASKIYA (GASKIYA) ko ba a ƙayyade ba kwata-kwata, aikin yana dawo da tunani a cikin salon A1, idan KARYA (KARYA), to a salo Saukewa: R1C1.
  • sheet_rubutu (sheet_name) - ana iya ƙayyade sunan takardar idan kuna son ganin ta a cikin sakamakon da aikin ya dawo.

Tarko ADDRESS

aiki ADDRESS (ADDRESS) yana mayar da adireshin tantanin halitta kawai azaman sigar rubutu. Idan kana buƙatar ƙimar tantanin halitta, yi amfani da shi azaman hujjar aiki GASKIYA (KA KYAUTA) ko yi amfani da ɗayan madadin hanyoyin da aka nuna a Misali 2.

Misali 1: Samu adireshin salula ta jere da lambar shafi

Amfani da ayyuka ADDRESS (ADDRESS) Kuna iya samun adireshin salula azaman rubutu ta amfani da layi da lambar shafi. Idan ka shigar da waɗannan gardama guda biyu kawai, sakamakon zai zama cikakken adireshi da aka rubuta cikin salon hanyar haɗin gwiwa A1.

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

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

Cikakken ko dangi

Idan ba ku ƙayyade ƙimar hujja ba abs_num (reference_type) a cikin dabara, sakamakon shine cikakken tunani.

Don ganin adireshin azaman hanyar haɗin gwiwa, zaku iya musanya azaman hujja abs_num (reference_type) darajar 4.

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

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

A1 ko R1C1

Zuwa hanyar haɗin kai Saukewa: R1C1, maimakon tsarin tsoho A1, Dole ne ku saka KARYA don hujja a1.

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

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

Sunan takardar

Hujja ta ƙarshe ita ce sunan takardar. Idan kuna buƙatar wannan suna a cikin sakamakon, saka shi azaman hujja takardar_rubutu (sheet_name).

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

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

Misali 2: Nemo ƙimar tantanin halitta ta amfani da lambar jere da shafi

aiki ADDRESS (ADDRESS) yana mayar da adireshin tantanin halitta azaman rubutu, ba azaman hanyar haɗi mai inganci ba. Idan kuna buƙatar samun ƙimar tantanin halitta, zaku iya amfani da sakamakon da aikin ya dawo dashi ADDRESS (ADDRESS), a matsayin hujja don GASKIYA (KAI TSAYE). Za mu yi nazarin aikin GASKIYA (KA KYAU) daga baya a tseren marathon 30 Excel yana aiki a cikin kwanaki 30.

=INDIRECT(ADDRESS(C2,C3))

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

aiki GASKIYA (INDIRECT) na iya aiki ba tare da aikin ba ADDRESS (ADDRESS). Anan ga yadda zaku iya, ta amfani da ma'aikacin haɗin gwiwa "&", makantar da adireshin da ake so a cikin salon Saukewa: R1C1 kuma a sakamakon haka sami darajar tantanin halitta:

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

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

aiki INDEX (INDEX) kuma na iya dawo da ƙimar tantanin halitta idan an ƙayyade jere da lambar shafi:

=INDEX(1:5000,C2,C3)

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

1:5000 sune layuka 5000 na farko na takardar Excel.

Misali 3: Mayar da adireshin tantanin halitta tare da iyakar ƙimar

A cikin wannan misali, za mu sami tantanin halitta tare da iyakar ƙimar kuma amfani da aikin ADDRESS (ADDRESS) domin samun adireshinta.

aiki MAX (MAX) yana samun matsakaicin lamba a shafi na C.

=MAX(C3:C8)

=МАКС(C3:C8)

Na gaba yana zuwa aikin ADDRESS (ADDRESS) hade da MATCH (MATCH), wanda ke nemo lambar layin, da shafi (COLUMN), wanda ke ƙayyade lambar shafi.

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

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

Leave a Reply