Microsoft Excel yana da wasu fasaha mai ban sha'awa da kuma amfani da SUM da INDIRECT ƙayyadaddun hanyoyin dabara su ne kawai hanyoyi biyu don sauƙaƙe sarrafa bayanai da kuke da shi.
SUM - INDIRECT Formula Overview
Yin amfani da aikin INDIRECT a cikin takaddun Excel yana sa sauƙin canza canjin da aka yi amfani da shi a cikin hanyar da ba tare da gyara tsarin da kanta ba.
INDIRECT za a iya amfani dashi da wasu ayyuka da suka yarda da tantancewar salula kamar wata gardama kamar OFFSET da SUM ayyuka.
A cikin wannan batu, ta amfani da INDIRECT a matsayin hujja ga SUM aiki zai iya ƙirƙirar ƙananan labaran ƙididdiga na cell cewa SUM aiki yana ƙara da cewa.
INDIRECT yana yin wannan ta hanyar zartar da bayanai a cikin kwayoyin a kaikaice ta hanyar matsakaici.
Misali: SUM - INDIRECT Formula amfani da Ƙididdiga Dynamic Range
Wannan misali ya dogara ne akan bayanan da aka nuna a hoton da ke sama.
SUM - INDIRECT dabarar ta hanyar yin amfani da matakai na gaba a ƙasa shi ne:
= SUM (INDIRECT ("D" & E1 & ": D" & E2))
A cikin wannan maƙasudin, ƙwaƙwalwar aikin INDIRECT wanda aka kafa ya ƙunshi jigilar sel E1 da E2. Lambobi a waɗannan sel, 1 da 4, lokacin da aka hade tare da sauran ƙwararrun INDIRECT, sun samar da sassan layi D1 da D4.
A sakamakon haka, yawan lambobin da SUM aiki ya ƙunshi bayanai da ke ƙunshe a cikin kewayon sel D1 zuwa D4 - wanda shine 50.
Ta canza lambobin da ke cikin sel E1 da E2; duk da haka, za a iya sauya kewayon da za a iya haɗawa.
Wannan misali zai fara amfani da samfurin da ke sama don tattara bayanai a cikin kwayoyin halitta D1: D4 sannan kuma canza canjin zangon zuwa D3: D6 ba tare da gyara wannan tsari ba a cell F1.
01 na 03
Shigar da Formula - Zabuka
Zaɓuɓɓukan don shigar da wannan tsari sun haɗa da:
- Rubuta wannan samfurin ta kai tsaye cikin cell F1 kuma latsa maɓallin Shigar da ke keyboard
- shigar da aikin INDIRECT a matsayin shaida ta amfani da akwatin maganganun SUM aiki
Yawancin ayyuka a cikin Excel suna da akwatin maganganu, wanda ya ba ka dama ka shigar da dukkanin muhawarar aiki a kan rabaccen layi ba tare da damu ba game da haɗin .
A wannan yanayin, ana iya amfani da akwatin maganganun SUM aiki don sauƙaƙe wannan tsari zuwa wani ƙari. Domin aikin INDIRECT yana samuwa a cikin SUM, dole ne a shigar da aikin INDIRECT da muhawarar da hannu.
Matakan da ke ƙasa amfani da akwatin SUM don shigar da dabara.
Shigar da Bayanan Tutorial
Cell Data D1 - 5 D2 - 10 D3 - 15 D4 - 20 D5 - 25 D6 - 30 E1 - 1 E2 - 4- Shigar da wadannan bayanan cikin sel D1 zuwa E2
Fara SUM - INDIRECT Formula - Gyara SUM Function Dialog Box
- Danna kan salula F1 - wannan ita ce inda za a nuna sakamakon wannan misali
- Danna maɓallin Formulas na shafin rubutun
- Zabi Math & Trig daga ribbon don buɗe jerin aikin da aka sauke
- Danna SUM a cikin jerin don bude akwatin maganganun
02 na 03
Shigar da aikin INDIRECT - Danna don Duba Ƙari Mai Girma
Dole ne a shigar da takaddun INDIRECT a matsayin hujja don aikin SUM.
A cikin saukan ayyukan da aka haɓaka, Excel ba ta yarda bude buƙatar maganganun na biyu don shigar da gardama ba.
Saboda haka, dole ne a shigar da aikin INDIRECT da hannu a cikin lambar Number1 na akwatin SUM Function.
- A cikin akwatin maganganu, danna kan lambar Number1
- Shigar da aikin INDIRECT mai zuwa: INDIRECT ("D" & E1 & ": D" & E2)
- Danna Ya yi don kammala aikin kuma rufe akwatin maganganu
- Yawan lamba 50 ya kamata ya bayyana a cell F1 tun da wannan shine jimlar don bayanan dake cikin sel D1 zuwa D4
- Lokacin da ka danna kan tantanin halitta F1 cikakkiyar tsari = SUM (INDIRECT ("D" & E1 & ": D" & E2)) ya bayyana a cikin maɓallin tsari a sama da takardun aiki
Kaddamar da Ƙungiyar INDIRECT
Domin ƙirƙirar tasiri a cikin shafi na D ta yin amfani da INDIRECT, dole ne mu hade harafin D a cikin muhawarar INDIRECT da lambobin da ke cikin sel E1 da E2.
An kammala wannan ta hanyar haka:
- Ana amfani da ampersand ( & ) don yin amfani da takaddama ko hada tare da bayanan rubutu (a cikin wannan hali harafin D) tare da raƙuman ƙira (E1 da E2)
- Bugu da ƙari, bayanan rubutu wanda aka lakafta shi tare da bayanan salula ya kamata a kewaye shi da alamomi biyu ( "" )
- A ƙarshe, ƙarshen wurare na rabuwa sun rabu da haɗin ( :)
Sabili da haka, an fara bayanin maɓallin kewayon ta haruffa: "D" & E1 .
Shafin na biyu na haruffa: ": D" & E2 ya haɗu da mallaka tare da ƙarshen ƙarshen. Anyi haka ne saboda marin abu ne na rubutu kuma, sabili da haka, dole ne a hada shi a cikin alamomi.
Na uku ampersand a tsakiya yana amfani da shi don yin la'akari da bangarorin biyu a cikin gardama guda:
"D" & E1 & ": D" & E203 na 03
Dynamically Canza wurin SUM Function's Range
Dukkan ma'anar wannan tsari shine don sauƙaƙe sauyawa canjin da SUM aiki tareda ba tare da gyara fasalin aikin ba.
Ta hada da aikin INDIRECT a cikin tsari, canza lambobi a cikin kwayoyin halitta E1 da E2 zasu canza canjin da aka karanta ta aikin SUM.
Kamar yadda aka gani a cikin hoton da ke sama, wannan ma yana haifar da amsar da yake da shi a cikin salula F1 yana canzawa yayin da yake ƙaddamar da sababbin bayanai.
- Danna kan tantanin halitta E1
- Rubuta lambar 3
- Latsa maɓallin shigarwa a kan keyboard
- Danna kan tantanin halitta E2
- Rubuta lambar 6
- Latsa maɓallin shigarwa a kan keyboard
- Amsar a cikin sel F1 ya canza zuwa 90 - wanda shine yawan lambobin da ke cikin sel D3 zuwa D6
- Ƙara jarraba wannan samfuri ta canza abin da ke cikin sel B1 da B2 zuwa kowace lambobi tsakanin 1 da 6
INDIRECT da kuma #REF! Kuskuren kuskure
A #REF! kuskuren kuskure zai bayyana a cikin cell F1 idan aikin da INDIRECT ke aiki:
- ba ƙirar tantanin salula ba ne
- ya ƙunshi bayanin waje game da takardun littafi daban-daban kuma wannan littafi ba'a bude ba
- yana nufin ɓangaren tantanin halitta a waje da iyakokin ayyukan aiki (jeri 1,048,576 ko shafi na XFD)