Articles

Ayyukan ƙididdiga na Excel: Koyawa tare da misalai don bincike, sashi na huɗu

Excel yana ba da ayyuka masu yawa na ƙididdiga waɗanda ke yin ƙididdiga daga ainihin ma'ana, tsaka-tsaki, da yanayin zuwa ayyukan bincike.

A cikin wannan labarin za mu zurfafa zurfafa cikin ayyukan bincike.

Lura cewa an gabatar da wasu ayyuka na ƙididdiga a cikin sassan Excel na baya-bayan nan don haka ba a samun su a cikin tsoffin juzu'in.

Teburin Abubuwan Ciki

Kiyasta lokacin karantawa: 18 minti

Ayyukan bincike

`MAX`

Aiki MAX An jera na Excel a cikin nau'in Ayyukan ƙididdiga na Microsoft Excel. Yana dawo da ƙima mafi girma daga lissafin ƙima. MAX yana tsaye ga matsakaicin kuma lokacin da kuka ƙididdige lissafin ƙimar yana neman mafi girman ƙimar a ciki kuma ya dawo da ƙimar a cikin sakamakon.

ginin kalma

= MAX(number1, [number2], …)

batutuwa

number1: lamba, tantanin halitta mai ɗauke da lamba, ko kewayon sel masu ɗauke da lambobi waɗanda kuke son samun mafi girma lamba daga cikinsu.
[number2]: lamba ita ce tantanin halitta wanda ke ɗauke da lamba ko kewayon sel masu ɗauke da lambobi waɗanda kuke son samun lamba mafi girma daga cikinsu.

misali

Don ƙware aikin MAX muna buƙatar gwada shi a cikin misali kuma a ƙasa akwai wanda zaku iya gwadawa:

A cikin misali mai zuwa, mun shigar da lambobi kai tsaye cikin aikin ta hanyar raba su da waƙafi.

Note: Hakanan zaka iya shigar da lamba ta amfani da ƙididdiga biyu.

A cikin misali mai zuwa, mun yi la'akari da kewayon kuma sakamakon ya dawo 1861 a matsayin ƙimar mafi girma. Hakanan zaka iya komawa zuwa tsararru.

A cikin misali mai zuwa, mun ci karo da ƙimar kuskure kuma aikin ya dawo da ƙimar kuskure a cikin sakamakon.

`MAXA`

Aikin Excel Maxa yayi kama da Excel aiki Max.

Bambanci kawai tsakanin ayyukan biyu yana faruwa ne lokacin da aka kawo gardama ga aikin azaman nuni ga tantanin halitta ko tsararrun sel.

Aiki Max yayi watsi da ma'ana da ƙimar rubutu yayin aikin Maxa ƙimar ma'ana tana ƙidaya TRUE a matsayin 1, ƙimar ma'ana FALSE kamar yadda 0 da kuma ƙimar rubutu kamar 0.

Aiki MAXA Excel yana dawo da ƙima mafi girma daga tsarin ƙididdiga da aka bayar, yana ƙirga rubutu da ƙimar ma'ana FALSE a matsayin ƙimar 0 da ƙirga ƙimar ma'ana TRUE a matsayin darajar 1.

ginin kalma

= MAXA(number1, [number2], …)

batutuwa

number1: lamba (ko tsararrun ƙimar lambobi), tantanin halitta mai ɗauke da lamba, ko kewayon sel masu ɗauke da lambobi waɗanda kuke son samun mafi girma lamba daga cikinsu.
[number2]: lamba ita ce tantanin halitta da ke ɗauke da lamba (ko tsararrun ƙimar lambobi) ko kewayon sel masu ɗauke da lambobi waɗanda kuke son samun lamba mafi girma daga cikinsu.

A cikin nau'ikan Excel na yanzu (Excel 2007 da kuma daga baya), zaku iya ba da hujjar lambobi har 255 zuwa aikin Maxa, amma a cikin Excel 2003 aikin zai iya karɓar har zuwa muhawara na lamba 30 kawai.

Eemmpi

Masoya 1

Tantanin halitta B1 daga cikin maƙunsar bayanai masu zuwa yana nuna aikin Excel Maxa, ana amfani da shi don dawo da ƙima mafi girma daga saitin ƙima a cikin sel A1-A5.

Masoya 2

Tantanin halitta B1 daga cikin maƙunsar bayanai masu zuwa yana nuna aikin Excel Maxa, ana amfani da shi don dawo da ƙima mafi girma daga saitin ƙima a cikin sel A1-A3.

Lura cewa ƙimar GASKIYA a cikin tantanin halitta A1 Ana ɗaukar maƙunsar bayanai azaman ƙimar lamba 1 ta aikin Maxa. Saboda haka, wannan shine mafi girman darajar a cikin kewayon A1-A3.

Ƙarin misalan aikin Excel Maxa ana bayarwa akan Gidan yanar gizon Microsoft Office .

Kuskuren aiki MAXA

Idan kun sami kuskure daga aikin Maxa na Excel, wannan yana iya zama kuskure #VALORE!: Yana faruwa idan an kawo ƙima ga aikin kai tsaye Maxa ba adadi ba ne.

`MAXIFS`

Aikin Excel Maxifs aikin nema ne wanda ke dawo da mafi girman ƙima daga rukunin ƙimar da aka ƙayyade bisa ɗaya ko fiye da ma'auni.

ginin kalma

= MAXIFS( max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ... )

batutuwa

max_range: Tsari na ƙididdige ƙididdiga (ko kewayon sel masu ɗauke da ƙimar lambobi), daga abin da kuke son dawo da matsakaicin ƙimar idan an cika ka'idojin.
criteria_range1: tsararrun ƙima (ko kewayon sel masu ɗauke da ƙima) don gwadawa criteria1 .(Dole ne wannan jeri ya zama tsayi ɗaya da max_range).
criteria1: Yanayin da za a gwada dangane da ƙimar da ke cikin criteria_range1.
[criteria_range2, criteria2], [criteria_range3, criteria3], ...: Ƙarin tsararrun ƙididdiga na zaɓi don gwadawa da kowane yanayi don gwadawa.

Aiki Maxifs iya rike har zuwa 126 topic nau'i-nau'i criteria_range e criteria.

Kowane ma'aunin da aka bayar zai iya zama:

ƙimar lamba (wanda zai iya zama lamba, ƙima, ƙima, kwanan wata, lokaci ko ƙimar ma'ana) (misali 10, 01/01/2017, TRUE)

igiyar rubutu (misali "Sunan", "MercoleNa")

magana (misali "> 1", "<>0").

Yanzu criteria masu alaƙa da rubutun za ku iya amfani da jakunkuna:

? don daidaita kowane hali guda ɗaya
* don daidaita kowane jerin haruffa.

Idan wani criteria sigar rubutu ce ko magana, dole ne a kawo wannan ga aikin Maxifs a cikin ambato.

Aiki Maxifs Ba abin damuwa ba ne. Don haka, alal misali, lokacin kwatanta ƙima a cikin criteria_range tare da ni criteria, zaren rubutu"TEXT"E"text” za a yi la'akari daidai.

Aiki Maxifs An fara gabatar da shi a cikin Excel 2019 don haka ba a samuwa a cikin sigogin Excel na farko.

Eemmpi

Rubutun da ke ƙasa yana nuna bayanan tallace-tallace na kwata don wakilan tallace-tallace 3.

Aiki Maxifs ana iya amfani da shi don nemo matsakaicin adadin tallace-tallace na kowane kwata, yanki, ko wakilin tallace-tallace (ko kowane haɗin kwata, yanki, da wakilin tallace-tallace).

Bari mu kalli misalai na gaba.

Masoya 1

Don nemo madaidaicin adadin tallace-tallace a cikin kwata na farko:

=MAXIFS( D2:D13, A2:A13, 1 )

wanda ke ba da sakamakon $ 456.000.

A cikin wannan misali, Excel Maxifs yana gano layuka inda ƙimar cikin shafi A yayi daidai da 1 kuma ya dawo da matsakaicin ƙima daga madaidaitan ƙimar a cikin shafi na D.

Wato, aikin yana samun matsakaicin ƙimar $ 223.000, $ 125.000 da $ 456.000 (daga sel D2, D3 da D4).

Masoya 2

Bugu da ƙari, ta yin amfani da maƙunsar bayanan da ke sama, za mu iya kuma amfani da aikin Maxifs don nemo matsakaicin adadin tallace-tallace na "Jeff", a lokacin kwata na 3 da 4:

=MAXIFS( D2:D13, A2:A13, ">2", C2:C13, "Jeff" )

Wannan dabarar tana mayar da sakamakon $ 310.000 .

A cikin wannan misali, Excel Maxifs yana gano layin da:

Ƙimar cikin ginshiƙi A ya fi 2 girma

Shigar a shafi na C yayi daidai da "Jeff"

kuma ya dawo da iyakar madaidaitan dabi'u a cikin shafi D.

Wato, wannan dabarar tana samun matsakaicin ƙimar $ 310.000 da $ 261.000 (daga sel D8 da D11).

Tuntubar da Gidan yanar gizon Microsoft Office don ƙarin cikakkun bayanai akan misalan ayyukan Excel Maxifs.

Kuskuren aiki MAXIFS

Idan kun sami kuskure daga aikin Excel Maxifs, yana yiwuwa ya zama ɗaya daga cikin waɗannan:

#VALUE!: Duba idan tsararru max_range e criteria_range Duk ba su da tsayi iri ɗaya.

@NAME?: Yana faruwa idan kana amfani da tsohuwar sigar Excel (kafin 2019), wanda baya goyan bayan fasalin. Maxifs.

`MIN`

Aiki MIN aikin nema ne wanda ke dawo da mafi ƙarancin ƙima daga lissafin ƙima. MIN yana tsaye mafi ƙanƙanta kuma lokacin da ka ƙididdige lissafin ƙimar yana neman mafi ƙarancin ƙima a cikinsa kuma ya dawo da wannan ƙimar a cikin sakamakon.

ginin kalma

= MIN(number1, [number2], …)

batutuwa

number1: lamba, tantanin halitta wanda ke ɗauke da lamba, ko kewayon sel waɗanda ke ɗauke da lambobi waɗanda kuke son samun mafi ƙarancin lamba daga cikinsu.
[number2]: lamba, tantanin halitta wanda ke ɗauke da lamba, ko kewayon sel waɗanda ke ɗauke da lambobi waɗanda kuke son samun mafi ƙarancin lamba daga cikinsu.

misali

A cikin misali mai zuwa, mun shigar da lambobi kai tsaye cikin aikin ta hanyar raba su da waƙafi.

Hakanan zaka iya shigar da lamba ta amfani da ƙididdiga biyu. Yanzu, a cikin misali mai zuwa, mun yi la'akari da kewayon kuma sakamakon da aka dawo shine 1070.

A cikin misali mai zuwa, mun ci karo da ƙimar kuskure kuma aikin ya dawo da ƙimar kuskure a cikin sakamakon.

`MINA`

Aikin Excel MINA yayi kama da Excel aiki MIN.

Bambanci kawai tsakanin ayyukan biyu yana faruwa ne lokacin da aka kawo gardama ga aikin azaman nuni ga tantanin halitta ko tsararrun sel.

A wannan yanayin aikin MIN yayi watsi da ma'ana da ƙimar rubutu yayin aikin MINA ƙimar ma'ana tana ƙidaya TRUE a matsayin 1, ƙimar ma'ana FALSE kamar yadda 0 da kuma ƙimar rubutu kamar 0.

Aiki MINA Excel yana dawo da mafi ƙarancin ƙima daga tsarin ƙididdiga na ƙididdiga, yana ƙirga rubutu da ƙimar ma'ana FALSE a matsayin ƙimar 0 da ƙirga ƙimar ma'ana TRUE a matsayin darajar 1.

ginin kalma

= MINA( number1, [number2], ... )

batutuwa

number1: lamba, tantanin halitta wanda ya ƙunshi lamba, ko kewayon sel (ko tsararrun ƙididdiga) waɗanda ke ɗauke da lambobi waɗanda kuke son samun mafi ƙarancin lamba daga cikinsu.
[number2]: lamba, tantanin halitta wanda ya ƙunshi lamba, ko kewayon sel (ko tsararrun ƙididdiga) waɗanda ke ɗauke da lambobi waɗanda kuke son samun mafi ƙarancin lamba daga cikinsu.

A cikin nau'ikan Excel na yanzu (Excel 2007 da kuma daga baya), zaku iya ba da hujjar lamba 255 zuwa aikin. MINA, amma a cikin Excel 2003 aikin zai iya karɓar har zuwa ƙididdiga na lamba 30 kawai.

Eemmpi

Masoya 1

Tantanin halitta B1 daga cikin maƙunsar bayanai masu zuwa yana nuna aikin MINA na Excel, wanda ake amfani da shi don dawo da mafi ƙarancin ƙima daga saitin dabi'u a cikin sel. A1-A5.

Masoya 2

Tantanin halitta B1 daga cikin maƙunsar bayanai masu zuwa yana nuna aikin Excel MINA, ana amfani da shi don dawo da mafi ƙanƙanta ƙima daga saitin dabi'u a cikin sel A1-A3.

Ka tuna cewa darajar TRUE a cikin tantanin halitta A1 Ana ɗaukar maƙunsar bayanai azaman ƙimar lamba 1 ta aikin MINA. Saboda haka, wannan ita ce mafi ƙarancin ƙima a cikin kewayon A1-A3.

Ƙarin misalan aikin Excel MINA ana bayarwa akan Gidan yanar gizon Microsoft Office .

Kuskuren aiki MINA

Idan kun sami kuskure daga aikin MINA na Excel, wannan yana iya zama kuskure #VALORE!. Yana faruwa idan ƙimar da aka kawo ga aikin MINA ba lamba ba ne.

`MINIFS`

Aikin Excel MINIFS aikin nema ne wanda ke dawo da mafi ƙarancin ƙima daga rukunin ƙimar da aka ƙayyade bisa ɗaya ko fiye da ma'auni.

ginin kalma

= MINIFS( min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ... )

batutuwa

min_range: Tsari na ƙididdige ƙididdiga (ko kewayon sel masu ɗauke da ƙimar lambobi), daga abin da kuke son dawo da matsakaicin ƙimar idan an cika ka'idojin.
criteria_range1: tsararrun ƙima (ko kewayon sel masu ɗauke da ƙima) don gwadawa criteria1 .(Dole ne wannan jeri ya zama tsayi ɗaya da min_range ).
criteria1: Yanayin da za a gwada dangane da ƙimar da ke cikin criteria_range1.
[criteria_range2, criteria2], [criteria_range3, criteria3], ...: Ƙarin tsararrun ƙididdiga na zaɓi don gwadawa da kowane yanayi don gwadawa.

Aiki Minifs iya rike har zuwa 126 topic nau'i-nau'i criteria_range e criteria.

Kowane ma'aunin da aka bayar zai iya zama:

ƙimar lamba (wanda zai iya zama lamba, ƙima, ƙima, kwanan wata, lokaci ko ƙimar ma'ana) (misali 10, 01/01/2017, TRUE)

igiyar rubutu (misali "Sunan", "MercoleNa")

magana (misali "> 1", "<>0").

Yanzu criteria masu alaƙa da rubutun za ku iya amfani da jakunkuna:

? don daidaita kowane hali guda ɗaya
* don daidaita kowane jerin haruffa.

Idan wani criteria sigar rubutu ce ko magana, dole ne a kawo wannan ga aikin Minifs a cikin ambato.

Aiki Minifs Ba abin damuwa ba ne. Don haka, alal misali, lokacin kwatanta ƙima a cikin criteria_range tare da ni criteria, zaren rubutu"TEXT” da “rubutu” za a yi la’akari da abu ɗaya ne.

Aiki Minifs An fara gabatar da shi a cikin Excel 2019 don haka ba a samuwa a cikin sigogin Excel na farko.

Eemmpi

Rubutun da ke ƙasa yana nuna bayanan tallace-tallace na kwata don masu siyarwa 3.

Aiki Minifs ana iya amfani dashi don nemo mafi ƙarancin adadin tallace-tallace na kowane kwata, yanki ko wakilin tallace-tallace.

Ana nuna wannan a cikin misalai masu zuwa.

Masoya 1

Don nemo mafi ƙarancin adadin tallace-tallace a cikin kwata na farko:

=MINIFS( D2:D13, A2:A13, 1 )

wanda ke ba da sakamakon $ 125.000 .

A cikin wannan misali, Excel Minifs yana gano layuka inda darajar cikin shafi A yayi daidai da 1 kuma ya dawo da mafi ƙarancin ƙima daga madaidaitan ƙimar a cikin shafi na D.

Wato, aikin yana samun mafi ƙarancin ƙimar $223.000, $125.000, da $456.000 (daga sel D2, D3, da D4).

Masoya 2

Bugu da ƙari, ta yin amfani da maƙunsar bayanan da ke sama, za mu iya amfani da aikin Minifs don nemo mafi ƙarancin adadin tallace-tallace na "Jeff" a lokacin kwata na 3 da 4:

=MINIFS( D2:D13, A2:A13, ">2", C2:C13, "Jeff" )

Wannan dabarar tana mayar da sakamakon $261.000 .

A cikin wannan misali, Excel Minifs yana gano layin da:

Ƙimar cikin ginshiƙi A ya fi 2 girma

Shigar a shafi na C yayi daidai da "Jeff"

kuma yana mayar da mafi ƙarancin ƙimar madaidaicin a cikin shafi D.

Wato, wannan dabarar ta samo mafi ƙarancin ƙimar $ 310.000 da $ 261.000 (daga sel D8 da D11).

Don ƙarin misalan aikin Excel Minifs, tuntubar da Gidan yanar gizon Microsoft Office .

Kuskuren aiki MINIFS

Idan kun sami kuskure daga aikin Minifs na Excel, wataƙila ɗayan dalilai masu zuwa:

#VALORE! - Duba idan tsararru min_range e criteria_range Duk ba su da tsayi iri ɗaya.
#NOME? - Yana faruwa idan kuna amfani da tsohuwar sigar Excel (pre-2019), wanda baya goyan bayan fasalin Minifs.

`LARGE`

Aikin Excel Large aikin nema ne wanda ke dawo da kimar k'th mafi girma daga ɗimbin ƙimar lambobi.

ginin kalma

= LARGE( array, k )

batutuwa

tsararru – Tsari na ƙimar lambobi don bincika ƙimar k'th mafi girma.
K - Fihirisar, i.e. aikin yana dawo da ƙimar kth mafi girma dagaarray bayar da.

Za a iya bayar da hujjar tsararru zuwa aikin kai tsaye ko a matsayin nuni ga kewayon sel masu ɗauke da ƙimar lambobi. Idan ƙimar da ke cikin kewayon tantanin halitta da aka bayar sune kimar rubutu, ana watsi da waɗannan ƙimar.

misali

Fayil na gaba yana nuna aikin Excel Large, da aka yi amfani da shi don maido da 1st, 2nd, 3rd, 4th, and 5th most values from the set of values in sel A1-A5.

Wasu tunani game da maƙunsar misalan da ke sama:

A cikin tantanin halitta B1, inda k aka saita zuwa 1, aikin Large yana yin aiki iri ɗaya da Ayyukan Excel Max ;
A cikin tantanin halitta B5, lokacin da aka saita k zuwa 5 (yawan dabi'u a cikin tsararrun da aka bayar), Babban aikin yana yin aiki iri ɗaya kamar Excel Min aiki .

Ana iya samun ƙarin cikakkun bayanai da misalai na babban aikin Excel akan Gidan yanar gizon Microsoft Office .

Kuskuren aiki LARGE

Idan Excel Large ya dawo da kuskure, yana iya yiwuwa ɗaya daga cikin masu zuwa:

#NUM! - Yana faruwa idan:
- Ƙimar da aka kawo na k ƙasa da 1 ko fiye da adadin ƙididdiga a cikin tsararrun da aka kawo
  ko
  Thearray bada komai ba.
#VALUE! – Yana faruwa idan k da aka kawo ba lamba bane.

Koyaya, kurakurai na iya faruwa a cikin ƙididdige ayyukan MANYAN koda kuwa ƙimar da aka kawo na k yana tsakanin 1 da adadin ƙima a cikin tsararrun da aka kawo. Dalili mai yuwuwa na iya zama ƙimar rubutu, gami da wakilcin rubutu na lambobi a cikin tsararrun da aka bayar, ana yin watsi da su ta Babban aikin. Don haka, wannan batu na iya faruwa idan ƙimar da ke cikin tsararrun da aka bayar wakilcin rubutu ne na lambobi maimakon ainihin ƙimar lambobi.

Ana iya samun mafita ta hanyar canza duk ƙimar tsararru zuwa ƙimar lambobi.

`SMALL`

Ƙaramar aikin Excel aikin bincike ne wanda ke dawo da mafi ƙarancin ƙima daga tsarar ƙimar ƙima.

ginin kalma

= SMALL( array, k )

batutuwa

array - Tsari na ƙima don bincika mafi girman ƙimar k'th.
K - Fihirisar, i.e. aikin yana dawo da ƙimar kth mafi girma dagaarray bayar da.

misali

Fayil na gaba yana nuna aikin Excel Small, da aka yi amfani da shi don dawo da mafi ƙanƙanta na 1st, 2nd, 3rd, 4th, da 5th mafi ƙanƙanta daga saitin dabi'u a cikin sel. A1-A5.

A cikin misalin ya zama dole muyi la'akari da cewa::

A cikin cell B1, inda aka saita k zuwa 1, aikin Small yana yin aiki iri ɗaya da Excel Min aiki ;
A cikin cell B5, lokacin da aka saita k zuwa 5 (yawan dabi'u a cikinarray bayar), aikin Small yana yin aiki iri ɗaya da Aikin Max na Excel .

Ƙarin cikakkun bayanai da misalai na aikin Excel Small ana bayarwa akan Gidan yanar gizon Microsoft Office .

Kuskuren aiki SMALL

Idan Excel SMALL ya dawo da kuskure, yana iya yiwuwa ɗaya daga cikin masu zuwa:

#NUM! - Yana faruwa idan:
- Ƙimar da aka kawo na k ƙasa da 1 ko fiye da adadin ƙididdiga a cikin tsararrun da aka kawo
  ko
  Tsarin da aka bayar babu komai.
#VALUE! – Yana faruwa idan k da aka kawo ba lamba bane.

Koyaya, kurakurai na iya faruwa a cikin lissafin aikin LARGE koda ƙimar da aka bayar na k yana tsakanin 1 da adadin ƙimar a cikinarray bayar da. Dalili mai yiwuwa na iya zama ƙimar rubutu, gami da wakilcin rubutu na lambobi a cikinarray idan har aka yi watsi da su da Babban aikin. Saboda haka, wannan matsala na iya faruwa idan dabi'u a cikinarray An bayar da wakilcin rubutu na lambobi maimakon ainihin ƙimar lambobi.

Za a iya samun mafita ta hanyar canza duk dabi'u naarray a cikin ƙimar ƙididdiga.