SUMIF basa muExcel uye sum neakawanda mamiriro

Excel chirongwa chinoshanda zvinoshamisa. Kunyangwe iyo yakavakirwa-mukati seti inokwana kupedzisa rinenge chero basa. Uye kunze kwezviyero, zvinozivikanwa nevakawanda, kune zvakare izvo vanhu vashoma vakanzwa nezvazvo. Asi panguva imwe chete, ivo havaregi kubatsira. Ivo vane hunyanzvi hudiki, uye hapana nguva dzose kudiwa kwavo. Asi kana iwe uchiziva nezvavo, saka panguva yakaoma vanogona kubatsira zvakanyanya.

Nhasi tichataura nezverimwe remabasa akadai - SUMMESLIMN.

Kana mushandisi akatarisana nebasa rekupfupisa akati wandei maitiro, achitarisa pane mamwe maitiro, saka zvinodikanwa kushandisa basa racho. SUMMESLIMN. Iyo fomula inoshandisa basa iri inotora aya mamiriro senharo, yobva yapfupikisa hunhu hunosangana navo, uye ipapo kukosha kwakawanikwa kunopinzwa muchitokisi umo makanyorwa. 

SUMIFS Basa Rinotsanangurwa Tsananguro

Tisati tafunga nezvebasa racho SUMMESLIMN, unofanira kutanga wanzwisisa kuti iri nyore vhezheni yacho - SUMMESLI, sezvo iri pairi kuti basa ratiri kufunga rinobva. Mumwe nemumwe wedu angangove akatojairana nemabasa maviri anowanzo shandiswa - SUM (inoita chidimbu chezvinokosha) uye IF (inoedza kukosha maererano nemamiriro akataurwa).

Kana ukaasanganisa, unowana rimwe basa - SUMMESLI, iyo inotarisa data ichipikisana nemushandisi-yakatarwa maitiro uye inoverengera chete nhamba idzo dzinosangana nemaitiro iwayo. Kana tikataura nezve Chirungu vhezheni yeExcel, saka basa iri rinonzi SUMIF. Nemashoko akapfava, zita re-mutauro ishanduro yakananga yemutauro weChirungu. Basa iri rinogona kushandiswa kune zvakasiyana-siyana zvezvinangwa. Kunyanya, inogona kushandiswa seimwe nzira VPR, kureva kuti, nyora pasi

Musiyano mukuru pakati pebasa SUMMESLIMN  kubva pane yakajairika basa SUMMESLI ndechokuti nzira dzakawanda dzinoshandiswa. Syntax yaro yakanyanya kuoma pakuona kwekutanga, asi pakunyatsoongorora, zvinobuda kuti pfungwa yeiyi basa iri nyore kwazvo. Kutanga iwe unofanirwa kusarudza huwandu uko iyo data ichaongororwa, uye wozoisa mamiriro ekuteerera ayo kuongororwa kuchaitwa. Uye kuvhiyiwa kwakadaro kunogona kuitwa kune nhamba yakakura yemamiriro ezvinhu.

Iyo syntax pachayo ndeiyi:

SUMIFS(sum_range, condition_range1, condition1, [condition_range2, condition2], ...)

Munzvimbo dzakakodzera zvakakosha kuisa mitsara yemasero akakodzera mune imwe nyaya. 

Ngatitarisei kupokana mune zvakadzama:

  1. Sum_range. Iyi nharo, pamwe chete nehuwandu hwemamiriro ezvinhu 1 uye mamiriro 1, anodiwa. Iro seti yemasero anoda kupfupikiswa.
  2. Condition_range1. Iyi ndiyo nhanho iyo mamiriro acho achatariswa. Inopetwa nenharo inotevera - Condition1. Iko kupfupikiswa kwehunhu hunoenderana nechiyero kunoitwa mukati memasero akatsanangurwa munharo yapfuura.
  3. Condition1. Nhaurirano iyi inotsanangura maitiro achatariswa achipikisa. Inogona kuiswa, somuenzaniso, nenzira iyi: "> 32".
  4. Condition range 2, Condition 2… Pano, mamiriro anotevera akaiswa nenzira imwechete. Kana zvinopfuura mashoma mamiriro achida kutsanangurwa, ipapo iyo Condition Range 3 uye Condition 3 nharo dzinowedzerwa. Chirevo chezvirevo chakafanana chenharo dzinotevera.

Basa racho rinobvumira kushandiswa kwepamusoro kusvika ku 127 pairs yemamiriro uye mararamiro. 

Unogona kuishandisa munzvimbo dzinoverengeka kamwechete (isu tinongopa mashoma, runyorwa rwakatonyanya kureba):

  1. Accounting. Somuenzaniso, zvakanaka kushandisa basa SUMMESLIMN kugadzira mishumo yepfupiso, nekota yekushandisa pane imwe mari, semuenzaniso. Kana kuti gadzira rondedzero pane chimwe chigadzirwa kubva kune imwe mutengo wechikamu.
  2. Sales management. Pano zvakare basa rinogona kubatsira zvakanyanya. Somuenzaniso, takatarisana nebasa rokupfupikisa chete mutengo wezvinhu zvakatengeswa kune mumwe mutengi pane imwe nguva. Uye mumamiriro ezvinhu akadaro, basa racho SUMMESLIMN inogona kubatsira zvikuru.
  3. Dzidzo. Tichapa mimwe mienzaniso inoshanda kubva munzvimbo ino nhasi. Kunyanya, unogona kuishandisa kuwana pfupiso yemagiredhi evadzidzi. Unogona kusarudza chidzidzo chimwe chete kana mamakisi ega. Munhu anogona pakarepo kuseta akati wandei maitiro ayo ongororo ichasarudzwa, iri nyore kwazvo uye inogona kuchengetedza nguva yakawanda.

Sezvauri kuona, huwandu hwezvishandiso zveichi basa hwakakura kwazvo. Asi uku hakusi iko kwaro chete. Ngatitarisei mamwe mabhenefiti mashoma ane chikamu ichi:

  1. Kugona kuseta akawanda maitiro. Sei izvi zvakanakira? Unogona kushandisa zvakajairika basa SUMMESLI! Uye zvose nokuti zviri nyore. Iko hakuna chikonzero chekuita maverengero akasiyana kune imwe neimwe yemaitiro. Zvose zviito zvinogona kurongwa mberi, kunyange zvisati zvaitika. kuti tafura yedata ichaumbwa sei. Iyi inguva yakanaka saver.
  2. Automation. Nguva yemazuva ano inguva ye automation. Chete munhu anoziva kuita otomatiki basa rake anogona kuwana mari yakawanda. Ndokusaka kugona kugona Excel uye basa SUMMESLIMN kunyanya, yakakosha kune chero munhu anoda kuvaka basa. Kuziva basa rimwe chete kunotendera iwe kuita akati wandei zviito kamwechete, seimwe. Uye pano isu tinoenda kune inotevera bhenefiti yechinhu ichi.
  3. Kuchengetedza nguva. Nekuda kwekuti rimwe basa rinoita akati wandei mabasa kamwechete.
  4. Zviri nyore. Pasinei nenyaya yekuti iyo syntax inorema pakutanga kuiona nekuda kwekuwanda kwayo, kutaura zvazviri, pfungwa yeiyi basa iri nyore kwazvo. Kutanga, huwandu hwe data hunosarudzwa, zvino huwandu hwehuwandu, huchaongororwa kuti hunoenderana nemamiriro ezvinhu. Uye zvechokwadi, mamiriro acho pachawo anofanirawo kutsanangurwa. Uye saka kakawanda. Kutaura zvazviri, basa iri rinobva pane imwe chete inonzwisisika kuvaka, iyo inoita kuti ive nyore kudarika inozivikanwa VPR zvisinei nekuti inogona kushandiswa nekuda kwezvinangwa zvakafanana, uchifungawo huwandu hukuru hwemaitiro. 

Zvimiro zvekushandisa iyo SUMIFS basa

Pane akati wandei maficha ekushandisa basa iri aunofanira kutarisisa. Chekutanga, basa iri rinofuratira mitsara ine tambo dzemavara kana nulls, sezvo aya marudzi edata asingagoni kuwedzerwa pamwechete arithmetic pateni, anongobatanidzwa setambo. Basa iri harigoni kuita izvi. Iwe unofanirwawo kuterera kune zvinotevera mamiriro:

  1. Iwe unogona kushandisa aya marudzi ehutsika semamiriro ekusarudza maseru kuti uwedzere hunhu hurimo: manhamba kukosha, boolean kutaura, maseru mareferenzi, zvichingodaro. 
  2. Kana zvinyorwa, matauriro ane musoro kana zviratidzo zvemasvomhu zvichitariswa, zvino maitiro akadaro anotsanangurwa kuburikidza nemakotesheni.
  3. Hatikwanise kushandisa mazwi akareba kupfuura mavara 255.
  4. Zvinogoneka kushandisa fungidziro yemaitiro ekusarudza kukosha uchishandisa wildcards. Mucherechedzo wemubvunzo unoshandiswa kutsiva chimiro chimwe chete, uye chiratidzo chekuwedzera (asterisk) chinodiwa kutsiva mavara akawanda. 
  5. Boolean values ​​ari muhuwandu hwehuwandu hunoshandurwa otomatiki kuita manhamba kukosha zvinoenderana nerudzi rwavo. Nokudaro, kukosha kwe "CHOKWADI" kunoshanduka kuva imwe, uye "FALSE" - kusvika zero. 
  6. Kana #VALUE! kukanganisa kunoonekwa musero, zvinoreva kuti nhamba yemasero ari muchimiro uye marenji akasiyana. Iwe unofanirwa kuve nechokwadi chekuti saizi dzenharo idzi dzakafanana. 

Mienzaniso yekushandisa iyo SUMIFS basa

basa SUMMESLIMN kwete zvakaoma sezvinoratidzika pakutanga, zvinopera. Asi kuti tinyatsojekesa, ngatitarisei mimwe mienzaniso inoshanda yekuti ungashandisa sei basa racho SUMMESLIMN. Izvi zvinozoita kuti zvive nyore kunyura mumusoro wenyaya.

Condition summation dynamic range

Saka ngatitange nemuenzaniso wekutanga. Ngatitii tine tafura ine ruzivo rwekuti vadzidzi vanoita sei necurriculum mune chimwe chidzidzo. Pane seti yemagiredhi, kuita kunoongororwa pachiyero chegumi-point. Basa nderekutsvaga giredhi rebvunzo revadzidzi vane zita rekupedzisira rinotanga nebhii A, uye zvibodzwa zvavo zvishoma zvishanu.

Tafura inoita seizvi.

SUMIF basa muExcel uye sum neakawanda mamiriro
1

Kuti isu tiverenge zvibodzwa zvakazara zvichienderana nemaitiro atsanangurwa pamusoro, isu tinofanirwa kushandisa inotevera fomula.

SUMIF basa muExcel uye sum neakawanda mamiriro
2

Ngatitsanangurei nharo dzacho zvakadzama:

  1. C3:C14 ndiyo yedu pfupiso siyana. Muchiitiko chedu, inopindirana nemamiriro ezvinhu. Kubva pairi ichasarudzwa mapoinzi anoshandiswa kuverenga iyo mari, asi iyo chete inowira pasi pemaitiro edu.
  2. "> 5" ndiyo mamiriro edu ekutanga.
  3. B3:B14 ndiyo yechipiri chidimbu chechidimbu chinogadziriswa kuti chienderane nechirevo chechipiri. Isu tinoona kuti hapana kupindirana neiyo summation renji. Kubva pane izvi tinogumisa kuti huwandu hwechidimbu uye huwandu hwemamiriro ezvinhu hunogona kana kusafanana. 
  4. “A*” ndiwo mutsara wechipiri, unotsanangura kusarudzwa kwemamakisi chete kune avo vane zita rekupedzisira rinotanga naA. Patiri isu, asterisk inoreva chero nhamba yemavara. 

Mushure mekuverenga, tinowana tafura inotevera.

SUMIF basa muExcel uye sum neakawanda mamiriro
3

Sezvauri kuona, iyo fomula yakapfupisa kukosha kwakavakirwa pane ine simba renji uye zvichienderana nemamiriro akatsanangurwa nemushandisi.

Sarudza kupfupisa nemamiriro muExcel

Zvino ngatitii tinoda kuwana ruzivo rwekuti zvinhu zvipi zvakatumirwa kunyika dzipi mukati mechikamu chekupedzisira. Mushure meizvozvo, tsvaga mari yese kubva mukutakura kwaChikunguru naNyamavhuvhu.

Tafura pachayo inoita seizvi. 

SUMIF basa muExcel uye sum neakawanda mamiriro
5

Kuti tione mhedzisiro yekupedzisira, tinoda fomula yakadaro.

=(СУММЕСЛИМН(D2:D14;A2:A14;»=июнь»;B2:B14;»Товар_2″;C2:C14;»Казахстан»)+(СУММЕСЛИМН(D2:D14;A2:A14;»=август»;B2:B14;»Товар_2″;C2:C14;»Казахстан»)))

Nekuda kwekuverenga kunoitwa neiyi fomula, tinowana mhedzisiro inotevera.

SUMIF basa muExcel uye sum neakawanda mamiriro
4

Chenjerera! Iyi fomula inotaridzika yakakura kunyangwe isu takangoshandisa maitiro maviri chete. Kana iyo data data yakafanana, saka unogona kuderedza zvakanyanya kureba kwefomati, sezvakaratidzwa pasi apa.

SUMIFS basa kuunganidza kukosha mukati meakawanda mamiriro

Zvino ngatipe mumwe muenzaniso wokuenzanisira. Muchiitiko ichi, tafura inoramba yakafanana neyekare. 

Isu tinoshandisa iyo inotevera fomula (asi isu tinoinyora seyakarongwa fomula, ndiko kuti, tinoipinda kuburikidza nekiyi musanganiswa CTRL + SHIFT + ENTER).

=СУММ(СУММЕСЛИМН(D2:D14;B2:B14;»Товар_1″;C2:C14;{«Китай»;»Грузия»}))

Mushure mekuita SUMMESLIMN ichaita chidimbu chehuwandu hwehuwandu hunoenderana nemaitiro akataurwa mufomula (kureva, nyika dzeChina neGeorgia), iyo inokonzeresa rondedzero inopfupikiswa neyakajairwa basa. SUM, iyo yakanyorwa seyakarongwa fomula.

Kana mamiriro acho akapfuudzwa searongedzero yenguva dzose kweanopfuura peya imwe, ipapo formula ichapa mhedzisiro isiriyo.

Zvino ngatitarisei patafura ine zvikamu zvose.

SUMIF basa muExcel uye sum neakawanda mamiriro
6

Sezvauri kuona, takabudirira. Uchabudirirawo zvechokwadi. Kubudirira kukuru mundima iyi. Iri ibasa rakareruka iro munhu achangotsika tsoka munzira yekudzidza Excel anogona kunzwisisa. Uye isu tatoziva kuti basa racho SUMMESLIMN inokubvumira kuti ushande mune chero chikamu chebasa, kubva kuaccounting kusvika kunyange dzidzo. Kunyangwe iwe uri kuvaka basa mune chero imwe nzvimbo isina kutsanangurwa pamusoro, iyi ficha ichiri kukubatsira iwe kuwana mari. Ichi ndicho chikonzero akakosha.

Chinonyanya kukosha, inokubvumira kuchengetedza nguva, izvo, zvinosuruvarisa, zvishoma zviwanikwa. Zvinoita sekunge kune mashoma mashoma ekushandisa maviri mabasa, asi kana iwe uchinge waita nhamba huru yekudzokorora mashandiro, saka masekonzi aya anowedzera kusvika maawa anogona kushandiswa pane chimwe chinhu. Saka tinokurudzira kuti udzidzire kushandisa chimiro ichi. Uyezve, zviri nyore zvikuru.

Leave a Reply