Ayyuka 30 na Excel a cikin kwanaki 30: GASKIYA

Taya murna! Kun isa ranar ƙarshe ta tseren marathon 30 Excel yana aiki a cikin kwanaki 30. Ya kasance tafiya mai tsawo da ban sha'awa yayin da kuka koyi abubuwa da yawa masu amfani game da ayyukan Excel.

A ranar 30th na marathon, za mu ƙaddamar da nazarin aikin GASKIYA (INDIRECT), wanda ke mayar da hanyar haɗin da aka ƙayyade ta hanyar rubutun rubutu. Tare da wannan aikin, zaku iya ƙirƙirar jerin abubuwan saukarwa masu dogaro. Misali, lokacin zabar ƙasa daga jerin zaɓuka yana ƙayyade waɗanne zaɓuɓɓukan da za su bayyana a cikin jerin zaɓukan birni.

Don haka, bari mu kalli sashin ka'idar aikin GASKIYA (RASHIN GASKIYA) kuma bincika misalai masu amfani na aikace-aikacen sa. Idan kuna da ƙarin bayani ko misalai, da fatan za a raba su a cikin sharhi.

Aiki 30: GASKIYA

aiki GASKIYA (INDIRECT) yana dawo da hanyar haɗin da aka kayyade ta layin rubutu.

Ta yaya za ku yi amfani da aikin INDIRECT?

Tun da aikin GASKIYA (INDIRECT) yana dawo da hanyar haɗin da aka bayar ta hanyar rubutun rubutu, zaku iya amfani da shi zuwa:

  • Ƙirƙiri hanyar haɗin farko mara canzawa.
  • Ƙirƙiri tunani zuwa tsayin daka mai suna kewayo.
  • Ƙirƙiri hanyar haɗi ta amfani da takarda, jere, da bayanan shafi.
  • Ƙirƙiri tsararrun lambobi marasa canzawa.

Syntax IN DIRECT

aiki GASKIYA (INDIRECT) yana da ma'ana mai zuwa:

INDIRECT(ref_text,a1)

ДВССЫЛ(ссылка_на_ячейку;a1)

  • ref_text (link_to_cell) shine rubutun hanyar haɗin.
  • a1 - idan daidai yake da GASKIYA (GASKIYA) ko ba a bayyana shi ba, to za a yi amfani da salon hanyar haɗin gwiwa A1; idan kuma KARYA (KARYA) to salon Saukewa: R1C1.

Tarko INDIRECT

  • aiki GASKIYA Ana sake lissafin (INDIRECT) a duk lokacin da ƙimar da ke cikin takardar aikin Excel ta canza. Wannan na iya rage saurin littafin aikinku idan an yi amfani da aikin a cikin dabaru da yawa.
  • Idan aikin GASKIYA (INDIRECT) yana ƙirƙirar hanyar haɗi zuwa wani littafin aikin Excel, littafin aikin dole ne a buɗe ko tsarin zai ba da rahoton kuskure. #REF! (#LINK!).
  • Idan aikin GASKIYA (INDIRECT) yana nuni da kewayon da ya wuce layin layi da iyaka, tsarin zai ba da rahoton kuskure #REF! (#LINK!).
  • aiki GASKIYA (INDIRECT) ba zai iya yin la'akari da tsayayyen kewayon mai suna ba.

Misali 1: Ƙirƙiri hanyar haɗin farko mara canzawa

A misali na farko, ginshiƙan C da E sun ƙunshi lambobi iri ɗaya, ana ƙididdige jimlar su ta amfani da aikin SUM (SUM) ma haka suke. Koyaya, dabarun sun ɗan bambanta. A cikin cell C8, dabarar ita ce:

=SUM(C2:C7)

=СУММ(C2:C7)

A cikin cell E8, aikin GASKIYA (INDIRECT) yana ƙirƙirar hanyar haɗi zuwa farkon tantanin halitta E2:

=SUM(INDIRECT("E2"):E7)

=СУММ(ДВССЫЛ("E2"):E7)

Idan ka saka jere a saman takardar kuma ka ƙara darajar ga Janairu (Jan), to adadin a shafi na C ba zai canza ba. Tsarin zai canza, yana mai da martani ga ƙari na layi:

=SUM(C3:C8)

=СУММ(C3:C8)

Duk da haka, aikin GASKIYA (INDIRECT) yana gyara E2 azaman farkon tantanin halitta, don haka Janairu ana haɗa shi ta atomatik cikin lissafin jimlar shafi E. Ƙarshen tantanin halitta ya canza, amma farkon tantanin halitta bai shafi ba.

=SUM(INDIRECT("E2"):E8)

=СУММ(ДВССЫЛ("E2"):E8)

Misali 2: Haɗi zuwa tsayayyen kewayo mai suna

aiki GASKIYA (INDIRECT) na iya ƙirƙirar nuni zuwa kewayon mai suna. A cikin wannan misali, ƙwayoyin shuɗi sun ƙunshi kewayon Lissafin Lambobi. Bugu da kari, ana kuma ƙirƙiri kewayon mai ƙarfi daga ƙimar da ke cikin shafi na B NumListDyn, dangane da adadin lambobi a wannan shafi.

Ana iya ƙididdige jimlar duka jeri biyu ta hanyar ba da suna kawai a matsayin hujja ga aikin SUM (SUM), kamar yadda kuke gani a cikin sel E3 da E4.

=SUM(NumList) или =СУММ(NumList)

=SUM(NumListDyn) или =СУММ(NumListDyn)

Maimakon buga sunan kewayon cikin aiki SUM (SUM), Kuna iya komawa zuwa sunan da aka rubuta a ɗayan sel na takardar aikin. Misali, idan suna Lissafin Lambobi An rubuta shi a cikin cell D7, sannan tsarin da ke cikin cell E7 zai kasance kamar haka:

=SUM(INDIRECT(D7))

=СУММ(ДВССЫЛ(D7))

Abin takaici aikin GASKIYA (INDIRECT) ba zai iya ƙirƙirar tunani mai ƙarfi ba, don haka lokacin da kuka kwafi wannan dabara zuwa cikin tantanin halitta E8, zaku sami kuskure #REF! (#LINK!).

Misali 3: Ƙirƙiri hanyar haɗi ta amfani da takardar, jere, da bayanin shafi

Kuna iya ƙirƙirar hanyar haɗin kai cikin sauƙi bisa lambobi jere da shafi, da kuma amfani da ƙimar FALSE (FALSE) don hujjar aiki ta biyu. GASKIYA (KAI TSAYE). Wannan shine yadda ake ƙirƙirar hanyar haɗin yanar gizo Saukewa: R1C1. A cikin wannan misalin, mun kuma ƙara sunan takardar zuwa mahaɗin - 'MyLinks'!R2C2

=INDIRECT("'"&B3&"'!R"&C3&"C"&D3,FALSE)

=ДВССЫЛ("'"&B3&"'!R"&C3&"C"&D3;ЛОЖЬ)

Misali 4: Ƙirƙiri jerin lambobi marasa canzawa

Wani lokaci kuna buƙatar amfani da tsararrun lambobi a cikin dabarun Excel. A cikin misali mai zuwa, muna son matsakaita manyan lambobi 3 a shafi na B. Ana iya shigar da lambobi cikin dabara, kamar yadda ake yi a cell D4:

=AVERAGE(LARGE(B1:B8,{1,2,3}))

=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;{1;2;3}))

Idan kuna buƙatar tsararru mafi girma, to da alama ba za ku so shigar da duk lambobi a cikin tsarin ba. Zabi na biyu shine yin amfani da aikin Row (ROW), kamar yadda aka yi a cikin tsarin tsararru da aka shigar a cikin cell D5:

=AVERAGE(LARGE(B1:B8,ROW(1:3)))

=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(1:3)))

Zabi na uku shine a yi amfani da aikin Row (STRING) tare da GASKIYA (INDIRECT), kamar yadda aka yi tare da tsarin tsararru a cikin tantanin halitta D6:

=AVERAGE(LARGE(B1:B8,ROW(INDIRECT("1:3"))))

=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(ДВССЫЛ("1:3"))))

Sakamako ga duk dabara guda 3 zai zama iri ɗaya:

Koyaya, idan an saka layuka a saman takardar, dabara ta biyu za ta dawo da sakamakon da ba daidai ba saboda gaskiyar cewa nassoshi a cikin dabarar zasu canza tare da motsin jere. Yanzu, maimakon matsakaita na manyan lambobi uku, dabarar tana mayar da matsakaitan lambobi mafi girma na 3rd, 4th, da 5th.

Amfani da ayyuka GASKIYA (INDIRECT), dabara na uku yana kiyaye madaidaitan nassoshi jere kuma ya ci gaba da nuna madaidaicin sakamako.

Leave a Reply