Cikakken Hanyar Magancewa a Microsoft Excel

Kamar yadda kuka sani, a cikin teburin Excel akwai nau'ikan magance guda biyu: dangi da cikakken. A lamari na farko, hanyar haɗi ta canza a cikin hanyar kwafin ta hanyar ma'anar ƙaura ta dangi, kuma a cikin na biyu an daidaita shi kuma yana ci gaba da canzawa yayin kwafa. Amma ta hanyar tsoho, duk adireshin a cikin Excel cikakke ne. A lokaci guda, sau da yawa ana buƙatar amfani da cikakkun adireshin (gyarawa). Bari mu gano hanyoyin da za a iya yin hakan.

Yin amfani da cikakkun bayanai

Muna iya buƙatar cikakken bayani, alal misali, a cikin yanayin idan muka kwafa dabara, ɓangaren sashi wanda ya ƙunshi m da aka nuna a cikin jerin lambobi, na biyu yana da kullun darajar. Wannan shine, wannan lambar tana ɗaukar nauyin mai aiki koyaushe, wanda kuke buƙatar aiwatar da wani aiki (rarrabuwa, rarrabuwa, da sauransu) don duk lambobin m lambobi.

A cikin Excel, akwai hanyoyi guda biyu don saita ingantaccen jawabi: ta hanyar ƙirƙirar hanyar haɗin kai da amfani da aikin INDIRECT. Bari mu kalli kowane ɗayan waɗannan hanyoyin daki-daki.

Hanyar 1: Cikakkiyar hanyar haɗi

Har zuwa yanzu, shahararren hanyar da aka saba amfani da ita don ƙirƙirar cikakkiyar jawabi ita ce amfani da cikakkiyar hanyar haɗi. Cikakkun hanyoyin haɗin suna da bambanci ba kawai aikin aiki ba, har ma da haɓaka. Adireshin dangi yana da ma'anar rubutu kamar haka:

= A1

A adireshin da aka kafa, an saita alamar dala a gaban darajar daidaitawa:

= $ A $ 1

Za'a iya shigar da alamar dala da hannu. Don yin wannan, sanya siginan kwamfuta a gaban darajar farko na daidaitawar adireshin (a kwance) wanda ke cikin tantanin halitta ko a mashaya dabara. Bayan haka, a cikin babban hanyar amfani da harshen Ingilishi, danna maballin "4" babban (tare da mabuɗin rike saukar Canji) Wannan shine inda alamar dala yake. Don haka kuna buƙatar yin tsari iri ɗaya tare da daidaitawa na tsaye.

Akwai hanya mafi sauri. Wajibi ne a sanya siginan kwamfuta a cikin tantanin da adireshin ya kasance kuma danna maɓallin aikin F4. Bayan wannan, alamar dala za ta bayyana kai tsaye a lokaci ɗaya a gaban daidaitawa da daidaituwa na adireshin da aka bayar.

Yanzu bari mu kalli yadda ake amfani da cikakkun adireshi a aikace ta hanyar amfani da cikakkiyar hanyar haɗi.

Theauki tebur da ke ƙididdige yawan albashin ma'aikata. Ana yin lissafin ta hanyar ninka albashin su na mutum ta hanyar kafaffen sabis, wanda shine daidai ga duk ma'aikata. Nawa sosai yana zaune a cikin sel na daban. Mun fuskanci aikin ƙididdige yawan albashin dukkan ma'aikata da wuri-wuri.

1. Don haka, a cikin tantanin farko na shafi "Albashi" muna gabatar da tsari don ninka yawan adadin ma'aikacin mai daidai ta hanyar mai aiki. A cikin lamarinmu, wannan dabara tana da tsari mai zuwa:

   = C4 * G3



2. Don kirga sakamakon da aka gama, danna maballin Shigar a kan keyboard. Jimlar an nuna shi a cikin tantanin da ke dauke da tsari.



3. Mun lissafta ƙimar albashin ma'aikaci na farko. Yanzu muna buƙatar yin wannan don duk sauran layin. Tabbas, ana iya yin rubutu don kowane sel a cikin shafi. "Albashi" da hannu, shigar da wani tsari mai kama da gyara na farko, amma muna da aiki don aiwatar da lissafin cikin sauri, kuma shigarwar aiki zai dauki lokaci mai yawa. Haka ne, kuma me yasa kuke ƙoƙarin ɓata amfani da shigarwar manual, idan za a iya kwafin dabara kawai zuwa wasu ƙwayoyin?

   Don kwafar dabarar, yi amfani da kayan aiki kamar alamar cika. Mun zama siginan kwamfuta a cikin ƙananan kusurwar dama na tantanin inda ya ƙunshi. A lokaci guda, siginan kanta dole ne a canza shi zuwa wannan alamar mai cikawa a cikin hanyar gicciye. Riƙe maɓallin linzamin kwamfuta na hagu kuma ja siginar ƙasa zuwa ƙarshen tebur.



4. Amma, kamar yadda muke gani, maimakon yin lissafta daidai da albashin sauran ma'aikatan, mun sami kuzari ɗaya.



5. Mun duba dalilin wannan sakamakon. Don yin wannan, zaɓi sel na biyu a cikin shafi "Albashi". Barcin dabara yana nuna magana mai dacewa da wannan tantanin. Kamar yadda kake gani, dalilan farko (C5) yayi daidai da rarar ma'aikaci wanda albashinmu muke fata. Aurawar masu daidaitawa idan aka kwatanta da tantanin da ya gabata ya kasance ne ta dalilin haɗin gwiwa. Koyaya, a wannan yanayin muna buƙatar wannan. Godiya ga wannan, abu na farko shine yawan ma'aikaci da muke buƙata. Amma canzawa na daidaitawa ya faru tare da abu na biyu. Kuma yanzu adireshinsa ba ya nufin mai sahihiyar magana (1,28), amma ga miyar kwayar da ke ƙasa.

   

   Wannan shi ne ainihin dalilin da yasa lissafin albashin ma'aikata na gaba daga jerin ya zama ba daidai ba.

6. Don gyara halin, muna buƙatar canza magana na abu na biyu daga dangi zuwa gyara. Don yin wannan, koma farkon sashin farko na shafi "Albashi"ta hanyar nuna shi. Na gaba, za mu matsa zuwa masarar dabara, inda aka nuna bayanin da muke buƙata. Zaɓi na biyu factor (G3) sannan ka danna maballin aiki.



7. Kamar yadda kake gani, alamar dala ta bayyana kusa da wurin daidaitawa na abu na biyu, kuma wannan, kamar yadda muke tunawa, alama ce ta cikakkiyar magancewa. Don nuna sakamako akan allo, danna maɓallin Shigar.



8. Yanzu, kamar baya, muna kiran alamar cika ta sanya siginan kwamfuta a cikin ƙananan kusurwar dama na farkon ɓangaren shafi "Albashi". Riƙe maɓallin linzamin kwamfuta na hagu ka ja shi ƙasa.



9. Kamar yadda kake gani, a wannan yanayin, an aiwatar da lissafin daidai kuma an ƙididdige yawan albashin duk ma'aikatan kamfanin daidai.



10. Duba yadda aka kwafa dabarar. Don yin wannan, zaɓi kashi na biyu na shafi "Albashi". Mun kalli magana da ke cikin layin tsari. Kamar yadda kake gani, daidaitawa na farkon lamarin (C5), wanda har yanzu dangi ne, ya koma da maki guda ƙasa idan aka kwatanta da tantanin da ya gabata. Amma abu na biyu ($ G $ 3), adireshin da muka sanya gyara, ya kasance ba canzawa.

Har ila yau, Excel na amfani da abin da ake kira jawabi mai hadewa. A wannan yanayin, ko dai ginshiƙin ko jere an daidaita shi a cikin adireshin kashi. An samu wannan ta hanyar da za'a sanya alamar dala kawai a gaban ɗayan masu gudanar da adiresoshin. Ga misalin madaidaiciyar hanyar haɗi:

= $ 1

Wannan adireshin kuma ana ɗaukarsa hade:

= $ A1

Wannan shine, cikakkiyar adreshin a cikin haɗin hade ana amfani dashi don ɗayan darajar daidaita ayyukan guda biyu kawai.

Bari mu ga yadda za a iya amfani da irin wannan hanyar haɗin haɗin kai a aikace ta amfani da teburin albashi ɗaya don ma'aikatan kamfanin a matsayin misali.

1. Kamar yadda kake gani, a baya munyi shi ne saboda dukkan masu gudanar da abu na biyu an magance su sosai. Amma bari mu gani idan a wannan yanayin dole ne a daidaita dabi'u biyu? Kamar yadda kake gani, lokacin kwafa, motsi a tsaye yakan faru, kuma daidaiton kwance kwance ba ya canzawa. Sabili da haka, yana yiwuwa a aiwatar da cikakkiyar adireshin kawai ga daidaitawar layin, kuma a bar masu gudanar da shafin kamar yadda suke a tsoho - dangi.

   Zaɓi kashi na farko na kashi "Albashi" kuma a cikin lamuran dabaru mun aiwatar da amfani na sama. Mun sami tsarin fom na gaba:

   = C4 * G $ 3

   Kamar yadda kake gani, an saita ingantaccen magance magana a cikin abu na biyu kawai ga daidaitawar layin. Don nuna sakamako a cikin tantanin, danna maɓallin Shigar.



2. Bayan wannan, amfani da alamar cikawa, kwafa wannan dabarar zuwa kewayon sel da ke ƙasa. Kamar yadda kake gani, an yi wa ma'aikatan albashi daidai.



3. Mun kalli yadda aka nuna dabarar kwafin kwaya ta biyu a sashin da muka aiwatar da taken. Kamar yadda kuke gani a layin dabarun, bayan zabar wannan kashi na takaddar, duk da cewa masu gudanar da layin kawai suna da cikakken bayani a sashi na biyu, yanayin motsi na shafi bai faru ba. Wannan ya faru ne saboda gaskiyar cewa bamu kwaɓe a sararin sama ba, amma a tsaye. Idan zamu kwafa a sama, sannan a wani yanayi makamancin haka, akasin haka, zamuyi takaitaccen jawabi na daidaitawa na ginshiƙai, kuma saboda layuka wannan hanyar zata zama zaɓi.

Darasi: Cikakkar alaƙa da dangi a cikin Excel

Hanyar 2: INDIRECT aiki

Hanya ta biyu don tsara cikakken adireshin a cikin babbar hanyar shimfida bayanai shine amfani da mai aiki INDIA. Aikin da aka ƙayyade ya ƙunshi rukuni na ma'aikatan ginannen. Tunani da Arrays. Aikinta shine ƙirƙirar hanyar haɗi zuwa tantanin da aka ƙayyade tare da fitarwa a cikin ɓangaren takardar a inda mai aiki yake. A wannan yanayin, an haɗa hanyar haɗin zuwa ga masu gudanarwa har ma sun fi ƙarfin lokacin amfani da alamar dollar. Saboda haka, wasu lokuta al'ada ne don sanya mahadar suna amfani da INDIA "Super cikakke." Wannan magana tana da asalin rubutun:

= INDIRECT (cell_link; [a1])

Aikin yana da hujjoji guda biyu, na farkon wanda ke da matsayin wajibi, na biyun kuma ba shi.

Hujja Hanyar Sadarwa hanyar haɗi ne zuwa ga kyakkyawar hanyar samar da rubutu a hanyar rubutu. Wato, wannan hanyar haɗi ne na yau da kullun, amma an lullube shi cikin alamomin magana. Wannan shi ne ainihin abin da ya sa ya yiwu a tabbatar da halayen cikakkun magancewa.

Hujja "a1" - ba na tilas ba ne kuma ana amfani dashi a lokuta da dama Amfani da shi ya zama dole ne kawai lokacin da mai amfani ya zaɓi zaɓi na zaɓi na gaba, maimakon amfanin da aka saba da shi ta hanyar nau'in "A1" (layuka suna da ƙirar harafi, da layuka - dijital). Wani zaɓi shine don amfani da salon "R1C1", a cikin kowane layuka, kamar layuka, ana nuna lambobi ta lambobi. Kuna iya canzawa zuwa wannan yanayin aiki ta taga zaɓi na Excel. To, amfani da afareta INDIAa matsayin hujja "a1" yakamata a nuna darajar KARYA. Idan kuna aiki a cikin yanayin nuni na yau da kullun na yau da kullun, kamar sauran masu amfani, to azaman hujja "a1" zaku iya tantance darajar "GASKIYA". Koyaya, wannan darajar yana nuna ta tsohuwa, saboda haka gardamar ta fi sauƙi a cikin duka a wannan yanayin. "a1" kayyade.

Bari muyi la’akari da yadda cikakkiyar magance tsari ta amfani da aikin zai yi aiki. INDIA, misali, teburin albashinmu.

1. Mun zaɓi kashi na farko na shafi "Albashi". Mun sanya alama "=". Kamar yadda muke tunawa, abu na farko a cikin tsarin ƙididdigar albashin da aka ƙayyade dole ne adireshin dangi ya wakilta shi. Saboda haka, danna danna kan tantanin da ke dauke da darajar albashin mai daidai (C4) Biyo yadda aka nuna adireshin sa a cikin kashi don nuna sakamakon, danna maɓallin ninka (*) akan keyboard. Sannan muna buƙatar ci gaba zuwa amfani da afareta INDIA. Danna alamar. "Saka aikin".



2. A cikin taga yana buɗewa Wizards na Aiki je zuwa rukuni Tunani da Arrays. Daga cikin jerin sunayen da aka gabatar, muna rarrabe suna "INDIA". Saika danna maballin "Ok".



3. Ana kunna yanayin mai muhawara mai aiki INDIA. Ya ƙunshi fannoni biyu waɗanda suka dace da muhawara na wannan aikin.

   Sanya siginan kwamfuta a cikin filin Hanyar Sadarwa. Kawai danna maɓallin takardar wanda ke aiki don lissafa albashi (G3) Adireshin zai bayyana nan da nan a cikin filin taga shawara. Idan muna ma'amala tare da aiki na yau da kullun, to gabatarwar adireshin zai iya zama cikakke, amma muna amfani da aikin INDIA. Kamar yadda muke tunawa, adreshin da ke ciki yakamata su kasance cikin tsarin rubutu. Sabili da haka, muna ɗaukar ayyukan daidaitawa waɗanda ke cikin filin taga tare da alamun ambato.

   Tunda muna aiki a cikin daidaitaccen yanayin nuna yanayin, filin "A1" bar komai. Latsa maballin "Ok".



4. Aikace-aikacen yana aiwatar da lissafin kuma yana nuna sakamakon a cikin takardar takardar da ke ɗauke da tsari.



5. Yanzu mun kwafa wannan dabara ga dukkanin sauran ƙwayoyin da ke cikin shafi "Albashi" ta amfani da alamar cikewa, kamar yadda muka yi a da. Kamar yadda kake gani, duk sakamakon aka lissafta daidai.



6. Bari mu ga yadda aka nuna tsarin a ɗayan sel da aka kwafa. Zaɓi kashi na biyu na shafi kuma duba layin tsari. Kamar yadda kake gani, abu na farko, wanda shine hanyar haɗin dangi, ya canza kayan aikin sa. A lokaci guda, gardamar abu na biyu, wanda aikin ke wakilta INDIAzauna canzawa. A wannan yanayin, an yi amfani da ingantaccen tsarin magance magana.

Darasi: Mai aiki IFRS a cikin Excel

Cikakken jawabi a cikin teburin Excel za a iya cimma shi ta hanyoyi guda biyu: yin amfani da aikin INDIRECT da amfani da cikakkiyar hanyar haɗi. A lokaci guda, aikin yana ba da mafi ƙarancin madauri don adireshin. Hakanan ana iya amfani da jawabi na sashi na cikakken bayani ta amfani da hade hanyoyin hade.