Excel manhamba mabasa: Tutorial ine mienzaniso yekutsvagisa, chikamu chechina

Excel inopa huwandu hwakasiyana hwehuwandu hwemabasa anoita kuverenga kubva kune yakakosha zvinoreva, yepakati, uye modhi yekutarisa mabasa.

Muchikamu chino tichaongorora zvakadzama mumabasa ekutsvaga.

Ndokumbira utarise kuti mamwe mabasa enhamba akaunzwa mushanduro dzichangoburwa dzeExcel uye nekudaro haawanikwe mumashanduro ekare.

Inofungidzirwa nguva yekuverenga: 18 minuti

Tsvaga mabasa

MAX

Basa racho MAX yeExcel yakanyorwa muchikamu cheMicrosoft Excel Statistical Functions. Inodzosa kukosha kukuru kubva kune runyorwa rwezvinokosha. MAX inomiririra iyo yepamusoro uye kana iwe ukatsanangura rondedzero yemakosheni inotarisa iyo yakakosha kukosha mairi uye inodzosera iyo kukosha mumhedzisiro.

nemarongerwo

= MAX(number1, [number2], …)

zvidzidzo

• number1:  nhamba, sero rine nhamba, kana mutsara wemaseru ane nhamba dzaunoda kuwana nhamba huru kubva kwairi.
• [number2]:  nhamba inhamba (number) inhamba ine nhamba kana mutsara wemasero ane nhamba dzaunoda kuwana nhamba huru kubva kwairi.

muenzaniso

Kuti tigone iyo MAX basa isu tinofanirwa kuiedza mumuenzaniso uye pazasi pane imwe yaunogona kuedza:

Mumuenzaniso unotevera, takaisa nhamba zvakananga mubasa nekudziparadzanisa necomma.

Cherechedza: Iwe unogona zvakare kuisa nhamba uchishandisa kaviri makotesheni.

Mumuenzaniso unotevera, takanongedza renji uye mhedzisiro yakadzosa 1861 seyakanyanya kukosha. Iwe unogona zvakare kutarisa kune array.

Mumuenzaniso unotevera, tasangana nemhosho uye basa racho rakadzosa kukosha kwekukanganisa mumhedzisiro.

MAXA

Basa reExcel Maxa zvakafanana chaizvo Excel basa Max.

Musiyano uripo pakati peaya mabasa maviri unoitika kana gakava rinopihwa kubasa sekureva sero kana nhevedzano yemasero.

Basa racho Max inofuratira zvine musoro uye zvinyorwa zvemavara panguva iyo basa Maxa ukoshi hunonzwisisika hunokosha TRUE se1, kukosha kunonzwisisika FALSE se 0 uye zvinyorwa zvakakosha se0.

Basa racho MAXA Excel inodzosa kukosha kukuru kubva kune yakapihwa seti yenhamba, kuverenga zvinyorwa uye kukosha kunonzwisisika FALSE se kukosha kwe0 uye kuverenga kukosha kunonzwisisika TRUE kukosha kwe1.

nemarongerwo

= MAXA(number1, [number2], …)

zvidzidzo

• number1:  nhamba (kana nhevedzano yenhamba dzenhamba), sero rine nhamba, kana mutsara wemaseru ane nhamba dzaunoda kuwana nhamba huru kubva kwairi.
• [number2]:  nhamba isero rine nhamba (kana nhevedzano yenhamba dzenhamba) kana mutsara wemaseru ane nhamba dzaunoda kuwana nhamba huru kubva kwairi.

Mushanduro dzazvino dzeExcel (Excel 2007 uye gare gare), unogona kupa mazana maviri nemakumi mashanu enhamba dzenharo kuMaxa basa, asi muExcel 255 basa racho rinongogamuchira anosvika makumi matatu enhamba nharo.

esempi

Muenzaniso 1

Sero B1 yespredishiti inotevera inoratidza basa Excel Maxa, inoshandiswa kudzoreredza kukosha kukuru kubva kune seti yemakoshi mumaseru A1-A5.

Muenzaniso 2

Sero B1 yespredishiti inotevera inoratidza basa Excel Maxa, inoshandiswa kudzoreredza kukosha kukuru kubva kune seti yemakoshi mumaseru A1-A3.

Ziva kuti TRUE value muchitokisi A1 yespredishiti inobatwa senhamba kukosha 1 nebasa Maxa. Nokudaro, iyi ndiyo inonyanya kukosha muhuwandu A1-A3.

Mimwe mienzaniso yebasa Excel Maxa vanopihwa pa Microsoft Office webhusaiti .

Kukanganisa kwebasa MAXA

Kana iwe ukawana kukanganisa kubva kune basa Maxa yeExcel, iyi ingangove iko kukanganisa #VALORE!: Inoitika kana kukosha kwakapihwa zvakananga kune basa Maxa hadzisi nhamba.

MAXIFS

Basa reExcel Maxifs ibasa rekutsvaga rinodzosa kukosha kwepamusoro kubva kune subset yemakoshero anotsanangurwa zvichibva pane imwe kana akawanda maitiro.

nemarongerwo

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

zvidzidzo

• max_range:  Mutsara wenhamba dzenhamba (kana mutsara wemasero ane manhamba akoshero), kubva kwaunoda kudzosa kukosha kwepamusoro kana maitiro aitwa.
• criteria_range1:  nhevedzano yezvakakosha (kana mutsara wemaseru ane kukosha) kuti uedze uchipikisa criteria1 .(Rondedzero iyi inofanira kunge yakaenzana nehurefu semax_range ).
• criteria1: Mamiriro ekuyedza nekuremekedza kune kukosha mukati criteria_range1.
• [criteria_range2, criteria2], [criteria_range3, criteria3], ...: Yekuwedzera sarudzo dzemhando dzemhando dzekuyedza uye anoenderana mamiriro ekuyedza.

Basa racho Maxifs inogona kubata anosvika 126 misoro yemaviri maviri criteria_range e criteria.

Imwe neimwe yemaitiro akapihwa anogona kuve:

• kukosha kwenhamba (inogona kuva nhamba yakakwana, decimal, zuva, nguva kana kukosha kunonzwisisika) (semuenzaniso 10, 01/01/2017, TRUE)

kana

• tambo yemavara (semuenzaniso "Zita", "MercoleOf")

kana

• chirevo (semuenzaniso ">1", "<>0").

Nei criteria zvinoenderana nezvinyorwa zvaunogona kushandisa wildcards:

• ? kuenderana nechero munhu mumwe chete
• * kuenzanisa chero kutevedzana kwemavara.

Kana a criteria irworwo tambo kana chitauriro, ichi chinofanirwa kupihwa kune basa Maxifs mumakotesheni.

Basa racho Maxifs Haisi nyaya sensitive. Naizvozvo, semuenzaniso, kana uchienzanisa kukosha mu criteria_range neni criteria, mitsara yemavara "TEXT"Uye"text” vachanzi vakaenzana.

Basa racho Maxifs yakatanga kuunzwa muExcel 2019 uye nekudaro haisi kuwanikwa mumavhezheni ekutanga eExcel.

esempi

Iyo spreadsheet pazasi inoratidza kota yekutengesa data kune 3 vamiririri vekutengesa.

Basa racho Maxifs inogona kushandiswa kutsvaga huwandu hwepamusoro hwekutengesa kune chero kota, nharaunda, kana yekutengesa rep (kana chero musanganiswa wekota, ndima, uye mutengesi rep).

Ngatitarisei mienzaniso inotevera.

Muenzaniso 1

Kuti uwane huwandu hwekutengesa kwakanyanya mukati mekota yekutanga:

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

izvo zvinopa mhedzisiro $ 456.000.

Mumuenzaniso uyu, iyo Excel Maxifs inozivisa mitsara uko kukosha muchikamu A kuenzana ne1 uye inodzosa kukosha kwepamusoro kubva kune zvinoenderana nekoramu D.

Ndiko kuti, basa rinowana huwandu hwehuwandu hwe $ 223.000, $ 125.000 uye $ 456.000 (kubva kumasero D2, D3 uye D4).

Muenzaniso 2

Zvekare, tichishandisa iyo data spreadsheet iri pamusoro, isu tinogona zvakare kushandisa iyo Maxifs basa kuti tiwane yakanyanya kutengesa nhamba ye "Jeff", panguva yekota 3 uye 4:

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

Iyi fomula inodzosa mhedzisiro $ 310.000 .

Mumuenzaniso uyu, iyo Excel Maxifs inozivisa mitsetse umo:

• Kukosha muchikamu A kunopfuura 2

E

• Kupinda muchikamu C kwakaenzana na "Jeff"

uye inodzosa huwandu hwehuwandu hunoenderana mukoramu D.

Ndiko kuti, iyi fomula inowana huwandu hwehuwandu hwemadhora 310.000 uye $261.000 (kubva kumasero D8 uye D11).

Bvunza iyo Microsoft Office webhusaiti kuti uwane mamwe mashoko pamusoro peExcel basa mienzaniso Maxifs.

Kukanganisa kwebasa MAXIFS

Kana iwe ukawana chikanganiso kubva kuExcel basa Maxifs, inogona kunge iri imwe yeinotevera:

#VALUE!: Inotarisa kana arrays max_range e criteria_range anopiwa haasi ose ane hurefu hwakafanana.

@NAME?: Inoitika kana uri kushandisa yekare vhezheni yeExcel (pre-2019), iyo isingatsigire chimiro Maxifs.

MIN

Basa racho MIN ibasa rekutsvaga rinodzosa kukosha kwakaderera kubva kune runyorwa rwezvikoshi. MIN inomiririra kushoma uye kana iwe ukatsanangura rondedzero yezvikoshi inotsvaga iyo yakaderera kukosha mairi uye inodzosera iyo kukosha mumhedzisiro.

nemarongerwo

= MIN(number1, [number2], …)

zvidzidzo

• number1:  nhamba, sero rine nhamba, kana mutsara wemaseru ane manhamba pauri kuda kuwana nhamba diki.
• [number2]:  nhamba, sero rine nhamba, kana mutsara wemaseru ane manhamba pauri kuda kuwana nhamba diki.

muenzaniso

Mumuenzaniso unotevera, takaisa nhamba zvakananga mubasa nekudziparadzanisa necomma.

Iwe unogona zvakare kuisa nhamba uchishandisa kaviri makotesheni. Iye zvino, mumuenzaniso unotevera, tataura mutsara uye mhedzisiro yakadzoserwa ndeye 1070.

Mumuenzaniso unotevera, tasangana nemhosho uye basa racho rakadzosa kukosha kwekukanganisa mumhedzisiro.

MINA

Basa reExcel MINA zvakafanana chaizvo Excel basa MIN.

Musiyano uripo pakati peaya mabasa maviri unoitika kana gakava rinopihwa kubasa sekureva sero kana nhevedzano yemasero.

Muchiitiko ichi basa MIN inofuratira zvine musoro uye zvinyorwa zvemavara panguva iyo basa MINA ukoshi hunonzwisisika hunokosha TRUE se1, kukosha kunonzwisisika FALSE se 0 uye zvinyorwa zvakakosha se0.

Basa racho MINA Excel inodzosa kukosha kudiki kubva kune yakapihwa seti yenhamba, kuverenga zvinyorwa uye kukosha kunonzwisisika FALSE se kukosha kwe0 uye kuverenga kukosha kunonzwisisika TRUE kukosha kwe1.

nemarongerwo

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

zvidzidzo

• number1:  nhamba, sero rine nhamba, kana mutsara wemaseru (kana nhevedzano yenhamba dzenhamba) dzine nhamba dzaunoda kuwana nhamba diki kubva kwairi.
• [number2]:  nhamba, sero rine nhamba, kana mutsara wemaseru (kana nhevedzano yenhamba dzenhamba) dzine nhamba dzaunoda kuwana nhamba diki kubva kwairi.

Mushanduro dzazvino dzeExcel (Excel 2007 uye gare gare), unogona kupa anosvika mazana maviri nemakumi mashanu enhamba dzenharo pabasa. MINA, asi muExcel 2003 basa racho rinogona kungogamuchira kusvika kumakumi matatu enhamba dzenharo.

esempi

Muenzaniso 1

Sero B1 yespredishiti rinotevera rinoratidza basa reExcel MINA, rinoshandiswa kudzoreredza kukosha kudiki kubva kune seti yemakoshe mumasero. A1-A5.

Muenzaniso 2

Sero B1 yeinotevera spreadsheet inoratidza basa reExcel MINA, inoshandiswa kudzoreredza kukosha kudiki kubva kune seti yemakoshi mumaseru A1-A3.

Ramba uchifunga kuti kukosha TRUE muchitokisi A1 yespredishiti inobatwa senhamba kukosha 1 nebasa MINA. Nokudaro, iyi ndiyo idiki kukosha muhuwandu A1-A3.

Mimwe mienzaniso yeExcel basa MINA vanopihwa pa Microsoft Office webhusaiti .

Kukanganisa kwebasa MINA

Kana iwe ukawana kukanganisa kubva kune basa MINA yeExcel, iyi ingangove iko kukanganisa #VALORE!. Inoitika kana hunhu hwakapihwa kune iyo MINA basa isiri nhamba.

MINIFS

Basa reExcel MINIFS ibasa rekutsvaga rinodzosa kukosha kwepasi kubva kune subset yetsika dzakatsanangurwa zvichibva pane imwe kana akawanda maitiro.

nemarongerwo

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

zvidzidzo

• min_range:  Mutsara wenhamba dzenhamba (kana mutsara wemasero ane manhamba akoshero), kubva kwaunoda kudzosa kukosha kwepamusoro kana maitiro aitwa.
• criteria_range1:  nhevedzano yezvakakosha (kana mutsara wemaseru ane kukosha) kuti uedze uchipikisa criteria1 .(Iri rondedzero rinofanira kunge rakafanana nehurefu ne min_range ).
• criteria1: Mamiriro ekuyedza nekuremekedza kune kukosha mukati criteria_range1.
• [criteria_range2, criteria2], [criteria_range3, criteria3], ...: Yekuwedzera sarudzo dzemhando dzemhando dzekuyedza uye anoenderana mamiriro ekuyedza.

Basa racho Minifs inogona kubata anosvika 126 misoro yemaviri maviri criteria_range e criteria.

Imwe neimwe yemaitiro akapihwa anogona kuve:

• kukosha kwenhamba (inogona kuva nhamba yakakwana, decimal, zuva, nguva kana kukosha kunonzwisisika) (semuenzaniso 10, 01/01/2017, TRUE)

kana

• tambo yemavara (semuenzaniso "Zita", "MercoleOf")

kana

• chirevo (semuenzaniso ">1", "<>0").

Nei criteria zvinoenderana nezvinyorwa zvaunogona kushandisa wildcards:

• ? kuenderana nechero munhu mumwe chete
• * kuenzanisa chero kutevedzana kwemavara.

Kana a criteria irworwo tambo kana chitauriro, ichi chinofanirwa kupihwa kune basa Minifs mumakotesheni.

Basa racho Minifs Haisi nyaya sensitive. Naizvozvo, semuenzaniso, kana uchienzanisa kukosha mu criteria_range neni criteria, mitsara yemavara "TEXT” uye “chinyorwa” zvicharangarirwa zvakafanana.

Basa racho Minifs yakatanga kuunzwa muExcel 2019 uye nekudaro haisi kuwanikwa mumavhezheni ekutanga eExcel.

esempi

Iyo spreadsheet pazasi inoratidza kota yekutengesa data yevatatu vatengesi.

Basa racho Minifs inogona kushandiswa kuwana huwandu hwekutengesa hushoma kune chero kota, dunhu kana mumiriri wekutengesa.

Izvi zvinoratidzwa mumienzaniso inotevera.

Muenzaniso 1

Kuti uwane huwandu hwekutengesa hushoma mukati mekota yekutanga:

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

izvo zvinopa mhedzisiro $ 125.000 .

Mumuenzaniso uyu, iyo Excel Minifs inozivisa mitsara uko kukosha muchikamu A kuenzana ne1 uye inodzosa kukosha kwepasi kubva kune zvinoenderana mukoramu D.

Ndiko kuti, basa rinowana hushoma hwehukoshi $223.000, $125.000, uye $456.000 (kubva kumasero D2, D3, uye D4).

Muenzaniso 2

Zvekare, tichishandisa data spreadsheet iri pamusoro, tinogona kushandisawo basa racho Minifs kuwana huwandu hwekutengesa hushoma hwe "Jeff" panguva yekota 3 uye 4:

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

Iyi fomula inodzosa mhedzisiro $261.000 .

Mumuenzaniso uyu, iyo Excel Minifs inozivisa mitsetse umo:

• Kukosha muchikamu A kunopfuura 2

E

• Kupinda muchikamu C kwakaenzana na "Jeff"

uye inodzosa hushoma hweiyo inoenderana kukosha mukoramu D.

Ndiko kuti, iyi fomula inowana hushoma hweiyo kukosha $310.000 uye $261.000 (kubva kumasero D8 uye D11).

Kuti uwane mimwe mienzaniso yeExcel basa Minifs, bvunzai Microsoft Office webhusaiti .

Kukanganisa kwebasa MINIFS

Kana iwe ukagamuchira chikanganiso kubva kuExcel Minifs basa, ingangoita chimwe chezvikonzero zvinotevera:

• #VALORE! - Tarisa kana arrays min_range e criteria_range anopiwa haasi ose ane hurefu hwakafanana.
• #NOME? - Inoitika kana uri kushandisa yekare vhezheni yeExcel (pre-2019), iyo isingatsigire chimiro Minifs.

LARGE

Basa reExcel Large ibasa rekutsvaga rinodzosa kukosha kukuru kwe k'th kubva munhevedzano yenhamba dzenhamba.

nemarongerwo

= LARGE( array, k )

zvidzidzo

• array - Nhamba dzenhamba dzenhamba yekutsvaga iyo k'th yakakura kukosha.
• K - Indekisi, kureva kuti basa rinodzorera kth yakakura kukosha kubvaarray akapa.

The array argument inogona kupihwa kune basa racho zvakananga kana sereferensi kune mutsara wemaseru ane manhamba. Kana ma values ​​ari musero rakapihwa ari matext values, aya ma values ​​anofuratirwa.

muenzaniso

Iri rinotevera spreadsheet rinoratidza basa reExcel Large, yakashandiswa kutora yekutanga, 1nd, 2rd, 3th, uye 4th yakakura kukosha kubva pane seti yemakoshe mumasero. A1-A5.

Dzimwe pfungwa pamuenzaniso spreadsheet pamusoro:

• Muchitokisi B1, apo k yakaiswa kuna 1, basa Large anoita chiito chimwechete se Excel basa Max ;
• Muchitokisi B5, kana k ikaiswa ku5 (nhamba yezvikoshi muhurongwa hwakapihwa), iro Large basa rinoita zvakafanana Excel Min basa .

Rumwe ruzivo uye mienzaniso yeExcel Yakakura basa inogona kuwanikwa pa Microsoft Office webhusaiti .

Kukanganisa kwebasa LARGE

Kana iyo Excel Large inodzosera kukanganisa, ingangove imwe yeinotevera:

• #NUM! - Zvinoitika kana:
  • Iko kukosha kwakapihwa kwe k ishoma pane 1 kana hukuru pane huwandu hwemakoshe mune inopihwa array.
    kana
    L 'array zvakapihwa hazvina chinhu.
• #VALUE! - Inoitika kana k yakapihwa isiri nhamba.

Nekudaro, zvikanganiso zvinogona kuitika mukuverenga kweiyo LARGE basa kunyangwe iyo yakapihwa kukosha kwek iri pakati pe1 uye huwandu hwehuwandu mune yakapihwa array. Chikonzero chinogona kunge chiri chekuti kukosha kwemavara, kusanganisira manyorerwo anomiririra manhamba mukati mehurongwa hwakapihwa, anofuratirwa neiyo Large function. Naizvozvo, dambudziko iri rinogona kuitika kana makoshero ari muchikamu chakapihwa ari mameseji anomiririra manhamba kwete chaiwo manhamba.

Mhinduro yacho inogona kuwanikwa nekushandura ese kukosha kweiyo array kuita manhamba kukosha. 

SMALL

Iyo Excel Diki basa ibasa rekutarisa rinodzosa kth diki kukosha kubva kune akatevedzana enhamba dzenhamba.

nemarongerwo

= SMALL( array, k )

zvidzidzo

• array -Nhamba dzenhamba dzenhamba yekutsvaga iyo k'th yakakura kukosha.
• K - Indekisi, kureva kuti basa rinodzorera kth yakakura kukosha kubvaarray akapa.

The array argument inogona kupihwa kune basa racho zvakananga kana sereferensi kune mutsara wemaseru ane manhamba. Kana ma values ​​ari musero rakapihwa ari matext values, aya ma values ​​anofuratirwa.

muenzaniso

Iri rinotevera spreadsheet rinoratidza basa reExcel Small, yakashandiswa kudzoreredza 1st, 2nd, 3rd, 4th, uye 5th diki kukosha kubva pane seti yemitengo mumaseru. A1-A5.

Mumuenzaniso, zvakakosha kufunga kuti:

• Musero B1, uko k yakaiswa ku1, basa Small anoita chiito chimwechete se Excel Min basa ;
• Muchitokisi B5, kana k inoiswa ku5 (nhamba yemakoshero muarray akapa), basa Small anoita chiito chimwechete se Excel's Max basa .

Mamwe ruzivo uye mienzaniso yeExcel basa Small vanopihwa pa Microsoft Office webhusaiti .

Kukanganisa kwebasa SMALL

Kana iyo Excel SMALL inodzosera kukanganisa, ingangove imwe yeinotevera:

• #NUM! - Zvinoitika kana:
  • Iko kukosha kwakapihwa kwe k ishoma pane 1 kana hukuru pane huwandu hwemakoshe mune inopihwa array.
    kana
    Mutsara wakapihwa hauna chinhu.
• #VALUE! - Inoitika kana k yakapihwa isiri nhamba.

Zvisinei, kukanganisa kunogona kuitika mukuverenga kwebasa racho LARGE kunyangwe kana kukosha kwakapihwa kwek kuri pakati pe1 uye nhamba yezvakakosha muarray akapa. Chikonzero chinogona kunge chiri icho kukosha kwemavara, kusanganisira zvinyorwa zvinomiririra nhamba mukati mearray zvakapihwa, ivo vanofuratirwa neiyo Large basa. Naizvozvo, dambudziko iri rinogona kuitika kana maitiro ari muarray zvinopihwa zvinomiririra manhamba kwete nhamba chaidzo.

Mhinduro inogona kusvikwa nekushandura ese kukosha kwearray munhamba dzenhamba. 

Related Readings

• Excel manhamba mabasa: Tutorial ine mienzaniso, chikamu chekutanga
• Excel machati, zvaari, maitiro ekugadzira chati uye maitiro ekusarudza iyo yakakwana chati
• JSP Chikumbiro, nzira dzese dzechinhu chakakumbirwa Chikumbiro
• Project Management mukudzidzira ruzivo
• Pivot Matafura: zvavari, maitiro ekugadzira muExcel neGoogle. Tutorial ine mienzaniso

Ercole Palmeri