Binciken Hankali a cikin Excel (Sample Datasheet)

Tsari a fagen kudi koyaushe suna haɗuwa - ɗayan al'amari ya dogara da wani kuma yana canzawa tare da shi. Bi waɗannan canje-canje kuma ku fahimci abin da za ku jira a nan gaba, watakila ta amfani da ayyukan Excel da hanyoyin maƙunsar bayanai.

Samun sakamako da yawa tare da tebur bayanai

Ƙarfin bayanan bayanai abubuwa ne na menene-idan bincike-sau da yawa ana yin ta ta Microsoft Excel. Wannan shine sunan na biyu don nazarin hankali.

Overview

Teburin bayanai shine nau'in kewayon sel waɗanda za a iya amfani da su don magance matsaloli ta hanyar canza ƙima a wasu sel. An ƙirƙira shi lokacin da ya wajaba don ci gaba da lura da canje-canje a cikin sassan dabarar da karɓar sabuntawa ga sakamakon, bisa ga waɗannan canje-canje. Bari mu gano yadda ake amfani da teburin bayanai a cikin bincike, da kuma nau'ikan su.

Abubuwan asali game da tebur na bayanai

Akwai nau'ikan tebur na bayanai iri biyu, sun bambanta da adadin abubuwan da aka gyara. Kuna buƙatar tattara tebur tare da mai da hankali kan adadin ƙimar uXNUMXbuXNUMXb waɗanda kuke buƙatar bincika tare da shi.

Masana kididdiga suna amfani da tebur mai canzawa guda ɗaya lokacin da akwai mai canzawa ɗaya kawai a cikin magana ɗaya ko fiye da zai iya canza sakamakon su. Misali, ana yawan amfani dashi tare da aikin PMT. An tsara dabarar don ƙididdige adadin biyan kuɗi na yau da kullun kuma yayi la'akari da ƙimar riba da aka ƙayyade a cikin yarjejeniyar. A cikin irin waɗannan ƙididdiga, ana rubuta masu canji a cikin shafi ɗaya, kuma sakamakon lissafin a wani. Misalin farantin bayanai mai sauyi 1:

Na gaba, la'akari da faranti tare da masu canji 2. Ana amfani da su a lokuta inda abubuwa biyu ke tasiri ga canji a kowane mai nuna alama. Matsalolin biyu na iya ƙarewa a cikin wani tebur da ke da alaƙa da lamuni, wanda za'a iya amfani da shi don tantance mafi kyawun lokacin biya da adadin kuɗin kowane wata. A cikin wannan lissafin, kuna buƙatar amfani da aikin PMT. Misalin tebur mai sauyi 2:

Ƙirƙirar teburin bayanai tare da mai canzawa guda ɗaya

Yi la'akari da hanyar bincike ta amfani da misalin ƙaramin kantin sayar da littattafai da littattafai 100 kawai a hannun jari. Wasu daga cikinsu ana iya siyar da su mafi tsada ($ 50), sauran za su kashe masu siyan ƙasa ($ 20). An ƙididdige yawan kuɗin shiga daga siyar da duk kayayyaki - mai shi ya yanke shawarar cewa zai sayar da 60% na littattafai a farashi mai yawa. Kuna buƙatar gano yadda kudaden shiga zai karu idan kun ƙara farashin mafi girma na kaya - 70%, da sauransu.

Kula! Dole ne a lissafta jimlar kudaden shiga ta hanyar amfani da dabara, in ba haka ba ba zai yiwu a haɗa teburin bayanai ba.

1. Zaɓi tantanin halitta kyauta daga gefen takardar kuma rubuta dabarar a ciki: =Tambarin jimlar kudaden shiga. Misali, idan an rubuta kudin shiga a cikin cell C14 (an nuna bazuwar bazuwar), kuna buƙatar rubuta wannan: =S14.
2. Muna rubuta adadin adadin kaya a cikin shafi zuwa hagu na wannan tantanin halitta - ba a ƙasa ba, wannan yana da mahimmanci.
3. Muna zaɓar kewayon sel inda ginshiƙin kashi da mahaɗin zuwa jimillar kuɗin shiga suke.

4. Mun sami shafin "Bayanai" abu "Idan bincike" kuma danna kan shi - a cikin menu wanda ya buɗe, zaɓi zaɓi "Table Data".

5. Ƙaramar taga za ta buɗe inda kake buƙatar tantance tantanin halitta tare da adadin littattafan da aka fara sayar da su akan farashi mai yawa a cikin "Masanya dabi'u ta layuka a...". Anyi wannan matakin ne domin a sake kirga yawan kudaden shiga ta la'akari da karuwar kashi.

Bayan danna maɓallin "Ok" a cikin taga inda aka shigar da bayanan don haɗa teburin, sakamakon lissafin zai bayyana a cikin layi.

Ƙara Formula zuwa Teburin Bayanai Guda Daya

Daga tebur wanda ya taimaka ƙididdige aiki tare da mabambanta ɗaya kawai, zaku iya yin ƙayyadaddun kayan aikin bincike ta ƙara ƙarin dabara. Dole ne a shigar da shi kusa da dabarar da ta riga ta kasance - alal misali, idan tebur yana kan layi, muna shigar da magana a cikin tantanin halitta zuwa dama na data kasance. Lokacin da aka saita ginshiƙi, muna rubuta sabuwar dabara a ƙarƙashin tsohuwar. Na gaba, bi algorithm:

1. Zaɓi kewayon sel kuma, amma yanzu yakamata ya haɗa da sabuwar dabarar.
2. Bude menu na bincike "menene idan" kuma zaɓi "Datasheet".
3. Muna ƙara sabon tsari zuwa filin da ya dace a cikin layuka ko ginshiƙai, dangane da yanayin farantin.

Ƙirƙiri teburin bayanai tare da masu canji biyu

Farkon irin wannan tebur ɗin ya ɗan bambanta - kuna buƙatar sanya hanyar haɗi zuwa jimlar kudaden shiga sama da ƙimar kashi. Na gaba, muna yin waɗannan matakan:

1. Rubuta zaɓuɓɓukan farashi a cikin layi ɗaya tare da hanyar haɗi zuwa samun kudin shiga - tantanin halitta ɗaya don kowane farashi.
2. Zaɓi kewayon sel.

3. Bude taga tebur na bayanai, kamar lokacin tattara tebur tare da madaidaicin guda ɗaya - ta shafin "Data" akan kayan aiki.
4. Sauya a cikin ginshiƙi "Masanya dabi'u ta ginshiƙai a ..." cell tare da farashi mai girma na farko.
5. Ƙara tantanin halitta tare da kashi na farko na tallace-tallacen littattafai masu tsada zuwa shafi "Masanya dabi'u ta layuka a..." kuma danna "Ok".

A sakamakon haka, dukan tebur yana cike da adadin yiwuwar samun kudin shiga tare da yanayi daban-daban don sayar da kaya.

Ƙaddamar da ƙididdige ƙididdiga don takaddun aikin da ke ɗauke da teburin bayanai

Idan kuna buƙatar ƙididdigewa mai sauri a cikin tebur ɗin bayanai wanda baya haifar da sake ƙididdige duk littafin aiki, akwai wasu abubuwa da za ku iya yi don hanzarta aiwatarwa.

1. Bude taga zaɓuɓɓuka, zaɓi abu "Formulas" a cikin menu na dama.
2. Zaɓi abu "Automatic, ban da tebur na bayanai" a cikin sashin "Lissafi a cikin littafin aiki".

3. Bari mu sake ƙididdige sakamakon a cikin tebur da hannu. Don yin wannan, zaɓi dabarun kuma danna maɓallin F.

Wasu Kayan Aikin Don Yin Nazarin Hankali

Akwai wasu kayan aikin a cikin shirin don taimaka muku yin nazarin hankali. Suna sarrafa wasu ayyuka waɗanda in ba haka ba dole ne a yi su da hannu.

1. Ayyukan "Parameter Selection" ya dace idan an san sakamakon da ake so, kuma kuna buƙatar sanin ƙimar shigarwar mai canzawa don samun irin wannan sakamakon..
2. "Neman mafita" ƙari ne don magance matsaloli. Wajibi ne a saita iyaka kuma a nuna su, bayan haka tsarin zai sami amsar. An ƙayyade bayani ta hanyar canza dabi'u.
3. Ana iya yin nazarin hankali ta amfani da Manajan yanayi. Ana samun wannan kayan aiki a cikin menu na menene-idan a ƙarƙashin Data tab. Yana canza dabi'u a cikin sel da yawa - lambar zata iya kaiwa 32. Mai aikawa yana kwatanta waɗannan dabi'u don kada mai amfani ya canza su da hannu. Misali na amfani da manajan rubutun:

Binciken hankali na aikin zuba jari a cikin Excel

Menene-idan bincike yana da amfani musamman a yanayin da ake buƙatar hasashen, kamar saka hannun jari. Manazarta suna amfani da wannan hanya don gano yadda darajar hannayen jarin kamfani za ta canza sakamakon canje-canjen wasu abubuwa.

Hanyar Binciken Hankali na Zuba Jari

Lokacin nazarin "menene idan" yi amfani da ƙididdiga - manual ko atomatik. An san kewayon ƙimar, kuma ana musanya su cikin dabara ɗaya bayan ɗaya. Sakamakon shine saitin dabi'u. Zabi lambar da ta dace daga cikinsu. Bari mu yi la'akari da alamomi guda huɗu waɗanda aka gudanar da bincike na hankali a fagen kuɗi:

1. Ƙimar Ƙididdiga ta Yanar Gizo - Ana ƙididdige shi ta hanyar rage adadin zuba jari daga adadin kuɗin shiga.
2. Yawan dawowa / riba na ciki - yana nuna yawan riba da ake buƙata don karɓa daga hannun jari a cikin shekara.
3. Matsakaicin biyan kuɗi shine rabon duk ribar zuwa hannun jarin farko.
4. Ƙididdigar riba mai rangwame - yana nuna tasiri na zuba jari.

formula

Ana iya ƙididdige ƙididdige ƙididdigewa ta amfani da wannan dabara: Canja cikin sigar fitarwa a cikin % / Canja cikin sigar shigarwa cikin %.

Abubuwan fitarwa da sigogin shigarwa na iya zama ƙimar da aka kwatanta a baya.

1. Kuna buƙatar sanin sakamakon a ƙarƙashin daidaitattun yanayi.
2. Muna maye gurbin ɗaya daga cikin masu canji kuma muna saka idanu akan canje-canje a cikin sakamakon.
3. Muna lissafin canjin kashi na sigogin biyu dangane da kafaffun sharuɗɗan.
4. Mun saka adadin da aka samu a cikin dabara kuma mu ƙayyade hankali.

Misali na nazarin hankali na aikin saka hannun jari a cikin Excel

Don ƙarin fahimtar hanyoyin bincike, ana buƙatar misali. Bari mu bincika aikin tare da sanannun bayanai masu zuwa:

1. Cika tebur don nazarin aikin akan shi.

2. Muna lissafin kuɗin kuɗi ta amfani da aikin OFFSET. A matakin farko, kwararar ruwa daidai yake da saka hannun jari. Bayan haka, muna amfani da dabarar: =IF(KASHE (Lambar,1;)=2;SUM(Inflow 1: Outflow 1); SUM(Inflow 1: Outflow 1)+$B$ 5)
   Nadi tantanin halitta a cikin dabara na iya bambanta, dangane da shimfidar tebur. A ƙarshe, ana ƙara ƙimar daga bayanan farko - ƙimar ceto.

3. Mun ƙayyade lokacin da aikin zai biya. Don lokacin farko, muna amfani da wannan dabara: = GASKIYA(G7:g ku17; "<0"). Kewayon tantanin halitta shine ginshiƙin tsabar kuɗi. Don ƙarin lokaci, muna amfani da wannan dabara: =Lokacin farko+IF(Na farko e.stream>0; Farko e.stream;0). Aikin yana a lokacin hutu a cikin shekaru 4.

4. Muna ƙirƙirar ginshiƙi don lambobin waɗannan lokutan lokacin da aikin ya biya.

5. Muna ƙididdige dawowa kan zuba jari. Wajibi ne a yi magana inda aka raba riba a cikin wani lokaci ta hanyar zuba jari na farko.

6. Muna ƙayyade ƙimar ragi ta amfani da wannan dabara: =1/(1+ Disc.%) ^Lambar.

7. Muna ƙididdige ƙimar halin yanzu ta amfani da ninkawa - ana ninka tsabar kuɗi ta hanyar ragi.

8. Bari mu lissafta PI (ƙididdigar riba). Ƙimar halin yanzu akan lokaci yana rarraba ta hanyar zuba jari a farkon aikin.

9. Bari mu ayyana ƙimar dawowa ta ciki ta amfani da aikin IRR: = IRR (Range na tsabar kudi).

Binciken Hankali na Zuba Jari Ta Amfani da Takardar Bayanai

Don nazarin ayyukan a fagen zuba jari, wasu hanyoyin sun fi dacewa fiye da teburin bayanai. Yawancin masu amfani suna fuskantar rudani lokacin tattara tsari. Don gano dogaron abu ɗaya akan canje-canje a wasu, kuna buƙatar zaɓar madaidaitan sel don shigar da lissafi da karanta bayanai.

Factor da rarrabawa bincike a cikin Excel tare da sarrafa kansa

Wani nau'in bincike na hankali shine nazarin abubuwa da nazarin bambance-bambance. Nau'i na farko yana bayyana alakar da ke tsakanin lambobi, na biyu yana bayyana dogaron ɗaya mai canzawa akan wasu.

ANOVA a cikin Excel

Manufar irin wannan bincike shine a raba bambancin darajar zuwa sassa uku:

1. Canje-canje a sakamakon tasirin wasu dabi'u.
2. Canje-canje saboda alakar dabi'un da suka shafe ta.
3. Canje-canje bazuwar.

Bari mu yi nazarin bambance-bambance ta hanyar ƙarawa a cikin “Bayanin Bayanai”. Idan ba a kunna shi ba, ana iya kunna shi a cikin saitunan.

Teburin farko dole ne ya bi ka'idoji guda biyu: akwai shafi ɗaya don kowace ƙima, kuma bayanan da ke cikinsa an tsara su cikin tsari mai hawa ko sauka. Wajibi ne a bincika tasirin matakin ilimi akan hali a cikin rikici.

1. Nemo kayan aikin Binciken Bayanai a cikin Data tab kuma buɗe taga. A cikin lissafin, kuna buƙatar zaɓar bincike na bambance-bambancen ta hanya ɗaya.

2. Cika layukan akwatin maganganu. Tazarar shigarwar duk sel ne, ban da kan kai da lambobi. Rukuni ta ginshiƙai. Muna nuna sakamakon akan sabon takarda.

Tun da darajar a cikin ƙwayar rawaya ya fi ɗaya girma, za a iya la'akari da zato ba daidai ba - babu dangantaka tsakanin ilimi da hali a cikin rikici.

Factor analysis a Excel: misali

Bari mu bincika dangantakar bayanai a fagen tallace-tallace - wajibi ne don gano samfurori masu ban sha'awa da maras kyau. Bayanan farko:

1. Muna buƙatar gano abin da buƙatun kaya ya karu a cikin wata na biyu. Muna tattara sabon tebur don ƙayyade girma da raguwar buƙata. Ana ƙididdige girma ta amfani da wannan dabara: =IF ((Buƙatar 2-Buƙatu 1)>0; Buƙatar 2- Buƙatar 1; 0). Rage dabara: =IF(Growth=0; Bukatar 1- Bukatar 2;0).

2. Kididdigar karuwar bukatar kaya a matsayin kashi: =IF(Ci gaba/Sakamako 2 =0; Ragewa/Sakamakon 2; Ci gaba/Sakamako 2).

3. Bari mu yi ginshiƙi don tsabta – zaɓi kewayon sel kuma ƙirƙirar histogram ta shafin “Saka”. A cikin saitunan, kuna buƙatar cire cikawa, ana iya yin wannan ta hanyar kayan aikin Format Data Series.

Binciken hanyoyi biyu na bambance-bambance a cikin Excel

Ana yin nazarin bambance-bambance tare da masu canji da yawa. Yi la'akari da wannan tare da misali: kana buƙatar gano yadda sauri da amsa ga sautin ƙararrawa daban-daban ya bayyana a cikin maza da mata.

1. Mun bude "Binciken Bayanai", a cikin jerin kuna buƙatar nemo bincike na hanyoyi biyu na bambance-bambance ba tare da maimaitawa ba.
2. Tazarar shigarwa – sel waɗanda ke ɗauke da bayanai (ba tare da kai ba). Muna nuna sakamakon akan sabon takarda kuma danna "Ok".

Ƙimar F ta fi F-critical girma, wanda ke nufin cewa bene yana rinjayar saurin amsawa ga sauti.

Kammalawa

A cikin wannan labarin, an tattauna nazarin hankali a cikin ma'auni na Excel dalla-dalla, ta yadda kowane mai amfani zai iya fahimtar hanyoyin aikace-aikacen sa.