Hanyoyin haɗi a cikin Excel - cikakke, dangi da gauraye. Kurakurai lokacin aiki tare da alaƙar dangi a cikin Excel

Don sauƙaƙe ƙididdiga ta amfani da ƙididdiga a yanayin atomatik, ana amfani da nassoshi ga sel. Dangane da nau'in rubutun, an raba su zuwa manyan nau'i uku:

1. Hanyoyin haɗi. An yi amfani da shi don ƙididdiga masu sauƙi. Kwafi dabara ya haɗa da canza haɗin kai.
2. Cikakkun hanyoyin haɗi. Idan kana buƙatar samar da ƙarin ƙididdiga masu rikitarwa, wannan zaɓi ya dace. Don gyara amfani da alamar "$". Misali: $1.
3. gauraye mahadi. Ana amfani da irin wannan adireshin a cikin lissafin lokacin da ya zama dole don gyara shafi ko layi daban. Misali: $ A1 ko $1.

Idan ya zama dole a kwafi bayanan dabarar da aka shigar, ana amfani da nassoshi tare da cikakkiyar adireshi da gauraye. Labarin zai bayyana tare da misalan yadda ake yin lissafin ta amfani da nau'ikan hanyoyin haɗi daban-daban.

Maganar sel masu dangantaka a cikin Excel

Wannan saitin haruffa ne waɗanda ke ayyana wurin tantanin halitta. Ana rubuta hanyoyin haɗin kai a cikin shirin ta atomatik tare da adireshin dangi. Misali: A1, A2, B1, B2. Motsawa zuwa jere ko shafi na daban yana canza haruffan da ke cikin dabarar. Misali, farawa matsayi A1. Motsawa a kwance yana canza harafin zuwa B1, C1, D1, da dai sauransu. Hakazalika, canje-canje na faruwa yayin tafiya tare da layi na tsaye, kawai a wannan yanayin lambar ta canza - A2, A3, A4, da dai sauransu. Idan ya zama dole a kwafi. lissafin nau'in iri ɗaya a cikin tantanin halitta da ke kusa, ana yin lissafin ta amfani da ma'anar dangi. Don amfani da wannan fasalin, bi wasu matakai:

1. Da zarar an shigar da bayanan cikin tantanin halitta, matsar da siginan kwamfuta kuma danna tare da linzamin kwamfuta. Haskakawa tare da koren rectangle yana nuna kunna tantanin halitta da shirye-shiryen ƙarin aiki.
2. Ta latsa haɗin maɓalli Ctrl + C, muna kwafin abin da ke ciki zuwa allon allo.
3. Muna kunna tantanin halitta wanda kake son canja wurin bayanai ko wata dabarar da aka rubuta a baya.
4. Ta danna haɗin Ctrl + V muna canja wurin bayanan da aka adana a cikin allo na tsarin.

Nasihar masana! Don aiwatar da nau'in lissafin iri ɗaya a cikin tebur, yi amfani da hack na rayuwa. Zaɓi tantanin halitta mai ɗauke da dabarar da aka shigar a baya. Juya siginan kwamfuta akan ƙaramin murabba'i wanda ke bayyana a kusurwar dama ta ƙasa, da kuma riƙe maɓallin linzamin kwamfuta na hagu, ja zuwa layin ƙasa ko matsananciyar shafi, dangane da aikin da aka yi. Ta hanyar sakin maɓallin linzamin kwamfuta, lissafin za a yi ta atomatik. Ana kiran wannan kayan aikin alamar cikawa ta atomatik.

Misalin hanyar haɗin gwiwa

Don ƙarin bayani, yi la'akari da misalin lissafi ta amfani da dabara tare da ma'anar dangi. A ce mai mallakar kantin sayar da wasanni bayan shekara guda na aiki yana buƙatar lissafin riba daga tallace-tallace.

Tsarin ayyuka:

1. Misalin ya nuna cewa an yi amfani da ginshiƙan B da C don cika adadin kayan da aka sayar da farashinsa. Don haka, don rubuta dabarar kuma sami amsar, zaɓi shafi D. Tsarin ya yi kama da haka: = B2*C

Kula! Don sauƙaƙe aikin rubuta dabara, yi amfani da ɗan dabaru. Saka alamar "=", danna kan adadin kayan da aka sayar, saita alamar "*" kuma danna farashin samfurin. Za a rubuta dabarar bayan alamar daidai gwargwado ta atomatik.

2. Don amsa ta ƙarshe, danna "Shigar". Na gaba, kuna buƙatar ƙididdige yawan adadin ribar da aka karɓa daga wasu nau'ikan samfuran. To, idan adadin layin ba su da yawa, to, duk magudi za a iya yi da hannu. Don cika adadi mai yawa na layuka a lokaci guda a cikin Excel, akwai aiki ɗaya mai amfani wanda ke ba da damar canja wurin dabarar zuwa wasu sel.
3. Matsar da siginan kwamfuta a kan ƙananan kusurwar dama na rectangle tare da dabara ko sakamakon da aka gama. Bayyanar giciye baƙar fata yana aiki azaman sigina cewa za'a iya jan siginan kwamfuta zuwa ƙasa. Don haka, ana yin lissafin atomatik na ribar da aka karɓa don kowane samfur daban.
4. Ta hanyar sakin maɓallin linzamin kwamfuta da aka danna, muna samun sakamako daidai a duk layi.

Ta danna tantanin halitta D3, zaku iya ganin cewa an canza tsarin daidaitawar tantanin halitta ta atomatik, kuma yanzu yayi kama da haka: =B3*C3. Ya biyo baya cewa hanyoyin haɗin gwiwa sun kasance dangi.

Kurakurai masu yuwuwa lokacin aiki tare da hanyoyin haɗin gwiwa

Babu shakka, wannan aikin Excel yana sauƙaƙa lissafin lissafi sosai, amma a wasu lokuta matsaloli na iya tasowa. Bari mu yi la'akari da misali mai sauƙi na ƙididdige ƙimar riba na kowane abu na kaya:

1. Ƙirƙirar tebur kuma cika: A - sunan samfurin; B - yawan sayar da; C - farashi; D shine adadin da aka karɓa. A ce akwai abubuwa 11 kacal a cikin tsarin. Sabili da haka, la'akari da bayanin ginshiƙan, an cika layin 12 kuma yawan adadin riba shine D
2. Danna cell E2 kuma shigar =D2/D13.
3. Bayan latsa maɓallin "Shigar", ƙididdiga na dangi na tallace-tallace na abu na farko ya bayyana.
4. Mikewa ginshiƙin ƙasa kuma jira sakamakon. Koyaya, tsarin yana ba da kuskuren "#DIV/0!"

Dalilin kuskuren shine amfani da maƙasudin dangi don ƙididdigewa. Sakamakon kwafin dabarar, haɗin gwiwar sun canza. Wato, don E3, dabarar za ta yi kama da wannan =D3/D13. Saboda cell D13 ba a cika kuma a ka'idar yana da darajar sifili, shirin zai ba da kuskure tare da bayanin cewa rarraba ta sifili ba zai yiwu ba.

Muhimmin! Don gyara kuskuren, dole ne a rubuta dabarar ta yadda aka daidaita daidaitattun D13. Maganar dangi ba ta da irin wannan aikin. Don yin wannan, akwai wani nau'in haɗin kai - cikakke. 

Yadda ake yin cikakkiyar hanyar haɗi a cikin Excel

Godiya ga amfani da alamar $, ya zama mai yiwuwa a gyara daidaitawar tantanin halitta. Yadda wannan ke aiki, za mu ƙara yin la'akari. Tun da shirin yana amfani da adireshin dangi ta tsohuwa, saboda haka, don tabbatar da shi cikakke, kuna buƙatar aiwatar da ayyuka da yawa. Bari mu bincika mafita ga kuskuren "Yadda ake nemo ƙididdiga daga siyar da kayayyaki da yawa", yin lissafin ta amfani da cikakkiyar magana:

1. Danna E2 kuma shigar da haɗin haɗin haɗin =D2/D13. Tun da mahaɗin yana da dangi, dole ne a saita alama don gyara bayanai.
2. Gyara madaidaitan tantanin halitta D Don yin wannan aikin, gaba da harafin da ke nuna shafi da lambar jere ta sanya alamar "$".

Nasihar masana! Don sauƙaƙe aikin shigarwa, ya isa a kunna tantanin halitta don gyara tsarin kuma danna maɓallin F4 sau da yawa. Har sai kun sami gamsassun dabi'u. Madaidaicin tsari shine kamar haka: =D2/$D$13.

3. Danna maɓallin "Shigar". Sakamakon ayyukan da aka yi, sakamakon daidai ya kamata ya bayyana.
4. Jawo alamar zuwa layin ƙasa don kwafe dabarar.

Saboda amfani da cikakkiyar magana a cikin lissafin, sakamakon ƙarshe a cikin sauran layuka zai zama daidai.

Yadda ake saka hanyar haɗin haɗin gwiwa a cikin Excel

Don ƙididdigar ƙididdiga, ba kawai dangi da cikakkun bayanai ana amfani da su ba, har ma da gauraye. Siffar su ta musamman ita ce suna gyara ɗaya daga cikin masu daidaitawa.

• Misali, don canza matsayin layi, dole ne ka rubuta alamar $ a gaban sunan harafin.
• Akasin haka, idan an rubuta alamar dala bayan sanya wasiƙar, to alamun da ke cikin layin ba za su canza ba.

Daga wannan ya biyo baya don magance matsalar da ta gabata tare da ƙayyade ƙimar siyar da kayayyaki ta amfani da adireshi mai gauraya, ya zama dole a gyara lambar layin. Wato alamar $ ana sanya shi bayan harafin shafi, saboda haɗin kai ba ya canzawa ko da a cikin dangi. Bari mu dauki misali:

1. Don ingantattun ƙididdiga, shigar =D1/$D$3 kuma danna "Enter". Shirin ya ba da amsar daidai.
2. Don matsar da dabarar zuwa sel na gaba zuwa ƙasa da ginshiƙi kuma samun ingantattun sakamako, ja hannu zuwa tantanin ƙasa.
3. A sakamakon haka, shirin zai ba da lissafin daidai.

Hankali! Idan kun saita alamar $ a gaban wasikar, Excel zai ba da kuskure "#DIV/0!", wanda ke nufin cewa ba za a iya yin wannan aikin ba.

"SuperAbsolute" yana magana

A ƙarshe, bari mu kalli wani misali na cikakkiyar hanyar haɗi - "SuperAbsolute" jawabi. Menene siffofinsa da bambancinsa. Ɗauki kimanin lamba 30 kuma shigar da shi a cikin tantanin halitta B2. Wannan lambar ita ce za ta zama babba, wajibi ne a yi jerin ayyuka tare da shi, alal misali, don tayar da shi zuwa wani iko.

1. Don aiwatar da duk ayyuka daidai, shigar da dabara mai zuwa a shafi na C: =$2^$D2. A cikin shafi D mun shigar da darajar digiri.

2. Bayan danna maɓallin "Shigar" kuma kunna dabarar, muna shimfiɗa alamar ƙasa da shafi.
3. Muna samun sakamako daidai.

Layin ƙasa shine duk ayyukan da aka yi ana magana da su zuwa ƙayyadaddun tantanin halitta B2, don haka:

• Kwafi dabara daga cell C3 zuwa cell E3, F3, ko H3 ba zai canza sakamakon ba. Ba zai canza ba - 900.
• Idan kana buƙatar saka sabon shafi, haɗin gwiwar tantanin halitta tare da dabara zai canza, amma sakamakon zai kasance baya canzawa.

Wannan shine yanayin haɗin "SuperAbsolute": idan kuna buƙatar matsar da sakamakon ba zai canza ba. Koyaya, akwai yanayi lokacin da aka shigar da bayanai daga tushen ɓangare na uku. Don haka, ana canza ginshiƙan zuwa gefe, kuma an saita bayanan a tsohuwar hanyar a cikin shafi B2. Menene ya faru a wannan yanayin? Lokacin da aka haɗu, tsarin yana canzawa bisa ga aikin da aka yi, wato, ba zai ƙara nuna B2 ba, amma zuwa C2. Amma tun lokacin da aka shigar da shi a cikin B2, sakamakon ƙarshe zai zama kuskure.

Magana! Don samun damar saka macros daga tushe na ɓangare na uku, kuna buƙatar kunna saitunan haɓakawa (an kashe su ta tsohuwa). Don yin wannan, je zuwa Zaɓuɓɓuka, buɗe saitunan ribbon kuma duba akwatin da ke gefen dama a gaban "Developer". Bayan haka, damar yin amfani da ayyuka da yawa waɗanda aka ɓoye a baya daga idanun matsakaicin mai amfani zai buɗe.

Wannan yana haifar da tambaya: shin zai yiwu a canza ma'auni daga cell C2 domin an tattara lambar asali daga tantanin halitta B, duk da shigar da sababbin ginshiƙan bayanai? Don tabbatar da cewa canje-canje a cikin tebur ba su shafar ƙayyadaddun jimlar lokacin shigar da bayanai daga tushen ɓangare na uku, dole ne ku aiwatar da matakai masu zuwa:

1. Maimakon daidaitawar tantanin halitta B2, shigar da alamomi masu zuwa: = GASKIYA ("B2"). A sakamakon haka, bayan motsi da tsara abun da ke ciki zai yi kama da haka: = GASKIYA ("B2")^$E2.
2. Godiya ga wannan aikin, hanyar haɗin koyaushe tana nuna murabba'i tare da daidaitawa B2, ko da kuwa an ƙara ginshiƙai ko cirewa a cikin tebur.

Dole ne a fahimci cewa tantanin halitta wanda ba ya ƙunshi kowane bayanai koyaushe yana nuna ƙimar "0".

Kammalawa

Godiya ga amfani da nau'ikan hanyoyin haɗin yanar gizo, dama mai yawa suna bayyana cewa suna sauƙaƙa yin aiki tare da lissafin a Forecel. Sabili da haka, kafin ka fara aiki tare da ƙididdiga, da farko karanta hanyoyin haɗin gwiwa da ka'idojin shigarwa.