Uchambuzi wa chaguo za kukokotoa INDIRECT kwa mifano

Kwa mtazamo wa kwanza (hasa wakati wa kusoma usaidizi), kazi INDIRECT (KIASILI) inaonekana rahisi na hata sio lazima. Kiini chake ni kugeuza maandishi ambayo yanaonekana kama kiunga kuwa kiunga kamili. Wale. ikiwa tunahitaji kurejelea kisanduku A1, basi tunaweza kutengeneza kiunga cha moja kwa moja kwa mazoea (weka ishara sawa katika D1, bonyeza A1 na ubonyeze Ingiza), au tunaweza kutumia. INDIRECT kwa madhumuni sawa:

Tafadhali kumbuka kuwa hoja ya kazi - rejeleo la A1 - imeingizwa kwa alama za nukuu, ili, kwa kweli, ni maandishi hapa.

"Sawa," unasema. "Na ni faida gani?" 

Lakini usihukumu kwa hisia ya kwanza - ni ya udanganyifu. Kipengele hiki kinaweza kukusaidia katika hali nyingi.

Mfano 1. Transpose

Aina ya kawaida ya aina: unahitaji kugeuza dia ya wima

groove kwa usawa (transpose). Bila shaka, unaweza kutumia kuingiza maalum au kazi TRANSP (TRANSPOSE) katika fomula ya safu, lakini unaweza kuendelea na yetu INDIRECT:

Mantiki ni rahisi: kupata anwani ya seli inayofuata, tunaweka herufi "A" na herufi maalum "&" na nambari ya safu ya seli ya sasa, ambayo kazi inatupa. COLUMN (SAFU).

Utaratibu wa kurudi nyuma ni bora kufanywa tofauti kidogo. Kwa kuwa wakati huu tunahitaji kuunda kiunga cha seli B2, C2, D2, nk, ni rahisi zaidi kutumia hali ya kiungo ya R1C1 badala ya "vita vya baharini" vya kawaida. Katika hali hii, seli zetu zitatofautiana tu katika nambari ya safu wima: B2=R1C2, C2=R1C3, D2=R1C4 nk

Hapa ndipo hoja ya pili ya chaguo la kukokotoa inapokuja. INDIRECT. Ikiwa ni sawa KUSEMA UONGO (UONGO), basi unaweza kuweka anwani ya kiungo katika hali ya R1C1. Kwa hivyo tunaweza kubadilisha safu ya mlalo kwa urahisi hadi wima:

Mfano 2. Jumla kwa muda

Tayari tumechanganua njia moja ya muhtasari wa dirisha (safu) ya saizi fulani kwenye laha kwa kutumia chaguo la kukokotoa. KUTOLEWA (OFFSET). Tatizo kama hilo pia linaweza kutatuliwa kwa kutumia INDIRECT. Ikiwa tunahitaji muhtasari wa data kutoka kwa kipindi fulani cha anuwai, basi tunaweza kuiunganisha kutoka kwa vipande na kuibadilisha kuwa kiunga kamili, ambacho tunaweza kuingiza ndani ya kazi. SUM (SUM):

Mfano 3. Orodha ya kushuka ya jedwali mahiri

Wakati mwingine Microsoft Excel haichukulii majina na safu wima mahiri za jedwali kama viungo kamili. Kwa hivyo, kwa mfano, wakati wa kujaribu kuunda orodha ya kushuka (tab Data - Uthibitishaji wa Data) kulingana na safu Wafanyakazi kutoka kwa meza ya smart Watu tutapata kosa:

Ikiwa "tutafunga" kiungo na kazi yetu INDIRECT, basi Excel itaikubali kwa urahisi na orodha yetu kunjuzi itasasishwa kwa nguvu wakati wa kuongeza wafanyikazi wapya hadi mwisho wa jedwali mahiri:

Mfano 4. Viungo Visivyoweza Kuvunjika

Kama unavyojua, Excel husahihisha kiotomatiki anwani za marejeleo katika fomula wakati wa kuingiza au kufuta safu-mlalo kwenye laha. Katika hali nyingi, hii ni sahihi na rahisi, lakini sio kila wakati. Wacha tuseme kwamba tunahitaji kuhamisha majina kutoka kwa saraka ya wafanyikazi hadi kwa ripoti:

Ikiwa utaweka viungo vya kawaida (ingiza = B2 kwenye seli ya kwanza ya kijani na kuinakili chini), basi unapofuta, kwa mfano, Dasha, tutapata #LINK! kosa kwenye seli ya kijani inayolingana naye. (#REF!). Katika kesi ya kutumia kazi ya kuunda viungo INDIRECT hakutakuwa na shida kama hiyo.

Mfano 5: Kukusanya data kutoka kwa laha nyingi

Tuseme tuna karatasi 5 zilizo na ripoti za aina moja kutoka kwa wafanyikazi tofauti (Mikhail, Elena, Ivan, Sergey, Dmitry):

Hebu tuchukue kwamba sura, ukubwa, nafasi na mlolongo wa bidhaa na miezi katika meza zote ni sawa - nambari tu hutofautiana.

Unaweza kukusanya data kutoka kwa laha zote (usiifanye muhtasari, lakini iweke chini ya kila mmoja kwenye "rundo") kwa fomula moja tu:

Kama unaweza kuona, wazo ni sawa: tunaunganisha kiunga kwa seli inayotaka ya karatasi iliyopewa, na INDIRECT huigeuza kuwa "live". Kwa urahisi, juu ya meza, niliongeza barua za nguzo (B, C, D), na upande wa kulia - nambari za mstari zinazohitajika kuchukuliwa kutoka kwa kila karatasi.

Pitfalls

Kama ni kutumia INDIRECT (KIASILI) unahitaji kukumbuka juu ya udhaifu wake:

• Ikiwa unaunganisha faili nyingine (kwa kuunganisha jina la faili kwenye mabano ya mraba, jina la laha, na anwani ya seli), basi inafanya kazi tu wakati faili ya awali imefunguliwa. Tukiifunga, tutapata hitilafu #LINK!
• INDIRECT haiwezi kurejelea masafa yanayobadilika yenye jina. Imesimama - hakuna shida.
• INDIRECT ni chaguo la kukokotoa au "tete", yaani, inakokotolewa upya kwa mabadiliko yoyote katika seli yoyote ya laha, na sio tu kuathiri seli, kama katika utendaji wa kawaida. Hii ina athari mbaya kwa utendaji na ni bora kutochukuliwa na meza kubwa za INDIRECT.

• Jinsi ya kuunda safu inayobadilika na ukubwa wa kiotomatiki
• Kuhitimisha juu ya dirisha la visanduku kwenye laha iliyo na chaguo za kukokotoa za OFFSET