Microsoft Excel is nie net 'n sigbladredigeerder nie, maar ook 'n kragtige toepassing vir verskillende berekeninge. Laaste maar nie die minste nie, het hierdie geleentheid verskyn danksy ingeboude funksies. Met behulp van enkele funksies (operateurs) kan u selfs die berekeningsvoorwaardes spesifiseer wat kriteria genoem word. Kom ons leer in meer besonderhede hoe u dit kan gebruik as u in Excel werk.
Toepassingskriteria
Kriteria is die voorwaardes waaronder 'n program sekere aksies uitvoer. Dit word in 'n aantal ingeboude funksies gebruik. Hulle naam bevat meestal die uitdrukking "AS". Vir hierdie groep operateurs is dit eerstens nodig om dit toe te ken COUNTIF, SCHOTESLIMN, SUMIF, SUMIFS. Benewens die ingeboude operateurs, word kriteria in Excel ook gebruik vir voorwaardelike formatering. Oorweeg die gebruik daarvan in meer besonderhede met verskillende gereedskap van hierdie tafelverwerker.
COUNTIF
Die belangrikste taak van die operateur COUNTIFwat tot 'n statistiese groep behoort, is die tel van besette waardes deur verskillende waardes van selle wat aan 'n sekere gegewe voorwaarde voldoen. Die sintaksis daarvan is soos volg:
= COUNTIF (reeks; maatstaf)
Soos u kan sien, het hierdie operateur twee argumente. "Range" stel die adres voor vir die verskeidenheid elemente op die vel waarop u kan tel.
"Criterion" - dit is 'n argument wat die voorwaarde stel wat die selle van die spesifieke gebied presies moet bevat om by die telling in te sluit. As parameter kan 'n numeriese uitdrukking, teks of 'n skakel na die sel waarin die kriterium vervat is, gebruik word. In hierdie geval, om die kriterium aan te dui, kan u die volgende karakters gebruik: "<" ("Minder"), ">" ("Meer"), "=" ("Gelykes"), "" (nie gelyk nie). As u byvoorbeeld 'n uitdrukking spesifiseer "<50", word slegs die elemente wat deur die argument gespesifiseer word, in ag geneem by die berekening "Range", waarin numeriese waardes kleiner is as 50. Die gebruik van hierdie tekens om parameters aan te dui, is van toepassing op alle ander opsies wat in hierdie les hieronder bespreek sal word.
Kom ons kyk nou na 'n konkrete voorbeeld van hoe hierdie operateur in die praktyk werk.
Daar is dus 'n tabel waar die inkomste uit vyf winkels per week aangebied word. Ons moet die aantal dae bepaal vir hierdie periode waarin die inkomste uit verkope in winkel 2 meer as 15.000 roebels was.
- Kies die velelement waarin die operateur die resultaat van die berekening sal lewer. Klik daarna op die ikoon "Voeg funksie in".
- Begin Funksie towenaars. Ons beweeg na die blok "Statistiek". Daar vind en belig ons die naam "COUNTIF". Klik dan op die knoppie. "OK".
- Die argumentvenster van bogenoemde stelling is geaktiveer. In die veld "Range" dit is nodig om die gebied van selle aan te dui waaronder die berekening gedoen sal word. In ons geval moet ons die inhoud van die reël uitlig "Winkel 2", waarin die omsetwaardes bedags geleë is. Ons plaas die wyser in die gespesifiseerde veld en kies die toepaslike skikking in die tabel met die linkermuisknoppie. Die adres van die geselekteerde skikking word in die venster vertoon.
In die volgende veld "Criterion" moet net die parameter vir onmiddellike seleksie instel. In ons geval moet ons slegs die elemente van die tabel tel waarin die waarde groter is as 15000. Daarom gebruik ons die sleutelbord die uitdrukking in die gespesifiseerde veld ">15000".
Nadat al die bogenoemde manipulasies gedoen is, klik op die knoppie "OK".
- Die program tel en vertoon die resultaat in die velelement wat voor aktivering gekies is Funksie towenaars. Soos u kan sien, is die resultaat in hierdie geval gelyk aan 5. Dit beteken dat in die geselekteerde skikking in vyf selle waardes van meer as 15 000 is, dit wil sê, ons kan aflei dat die inkomste in winkel 2 oor vyf dae uit die sewe wat geanaliseer is, meer as 15 000 roebels was.
Les: Excel-funksie-assistent
SCHOTESLIMN
Die volgende funksie wat volgens die kriteria werk, is SCHOTESLIMN. Dit behoort ook tot die statistiese groep operateurs. taak SCHOTESLIMN tel selle in 'n spesifieke reeks wat aan 'n spesifieke stel voorwaardes voldoen. Dit is die feit dat u nie een, maar verskeie parameters kan spesifiseer en hierdie operateur van die vorige kan onderskei. Die sintaksis is soos volg:
= COUNTIME (voorwaarde_reeks1; voorwaarde1; voorwaarde_reeks2; voorwaarde2; ...)
"Toestandreeks" is identies aan die eerste argument van die vorige stelling. Dit is, dit is 'n skakel na die gebied waarin selle getel sal word wat aan die gespesifiseerde voorwaardes voldoen. Met hierdie operateur kan u verskillende sulke gebiede tegelyk spesifiseer.
"Toestande" stel 'n kriterium voor wat bepaal watter elemente uit die ooreenstemmende datastelsel getel sal word en watter nie. Elke gegewe data-area moet afsonderlik gespesifiseer word, selfs al stem dit ooreen. Dit is noodsaaklik dat alle skikkings wat gebruik word as toestandsareas dieselfde aantal rye en kolomme het.
Om 'n aantal parameters van dieselfde data-gebied in te stel, byvoorbeeld om die aantal selle te tel waarin die waardes groter is as 'n sekere getal, maar minder as 'n ander getal, moet dit as argument beskou word "Toestandreeks" spesifiseer dieselfde skikking verskeie kere. Maar terselfdertyd, as gepaste argumente "Toestande" verskillende kriteria moet aangedui word.
Gebruik 'n voorbeeld van dieselfde tabel met weeklikse omset, en kyk hoe dit werk. Ons moet die aantal dae van die week bepaal wanneer die inkomste by alle gespesifiseerde winkels afgestaan het op die standaard wat daarvoor bepaal is. Die inkomstestandaarde is soos volg:
- Winkel 1 - 14,000 roebels;
- Winkel 2 - 15,000 roebels;
- Winkel 3 - 24,000 roebels;
- Winkel 4 - 11,000 roebels;
- Koop 5 - 32,000 roebels.
- Om die bogenoemde taak uit te voer, kies die element van die werkblad met die pyltjie, waar die resultaat van die verwerking van data vertoon sal word SCHOTESLIMN. Klik op die ikoon "Voeg funksie in".
- Gaan na Funksie-towenaarbeweeg weer na die blok "Statistiek". Die lys moet die naam vind SCHOTESLIMN en kies dit. Nadat u die gespesifiseerde aksie uitgevoer het, moet u op die knoppie druk "OK".
- Na die uitvoering van die bogenoemde algoritme van aksies, word die argumentvenster oopgemaak SCHOTESLIMN.
In die veld "Toestandreeks 1" voer die adres in van die lyn waar die data oor winkel 1-inkomste vir die week geleë is. Om dit te doen, plaas die wyser in die veld en kies die ooreenstemmende ry in die tabel. Die koördinate word in die venster vertoon.
As in ag geneem word dat vir die winkel 1 daaglikse omsetkoers 14.000 roebels is, dan in die veld "Toestand 1" skryf die uitdrukking ">14000".
In die veld "Toestandreeks 2 (3,4,5)" die koördinate van die reëls met die weeklikse inkomste van onderskeidelik Store 2, Store 3, Store 4 en Store 5 moet ingevul word. Die aksie word uitgevoer volgens dieselfde algoritme as vir die eerste argument van hierdie groep.
In die veld "Toestand 2", "Toestand 3", "Uslovie4" en "Uslovie5" ons voer die waardes daarvolgens in ">15000", ">24000", ">11000" en ">32000". Soos u kan raai, stem hierdie waardes ooreen met die omsetinterval wat die norm vir die ooreenstemmende winkel oorskry.
Nadat u al die nodige data (altesaam 10 velde) ingevoer het, klik op die knoppie "OK".
- Die program tel en vertoon die resultaat op die skerm. Soos u kan sien, is dit gelyk aan die getal 3. Dit beteken dat die inkomste by alle afsetpunte binne drie dae vanaf die geanaliseerde week die norm wat daarvoor gestel is, oorskry het.
Laat ons nou die taak verander. Ons moet die aantal dae bereken waarop Shop 1 'n inkomste van meer as 14.000 roebels ontvang het, maar minder as 17.000 roebels.
- Ons plaas die wyser in die element waar die uitset op die vel van die telleresultate geproduseer sal word. Klik op die ikoon "Voeg funksie in" oor die werkarea van die laken.
- Aangesien ons die formule onlangs toegepas het SCHOTESLIMN, hoef jy nie na die groep te gaan nie "Statistiek" Funksie towenaars. Die naam van hierdie operateur kan in die kategorie gevind word "10 onlangs gebruik". Kies dit en klik op die knoppie. "OK".
- Die bekende argument vir die operateur word oopgemaak. SCHOTESLIMN. Sit die wyser in die veld "Toestandreeks 1" en hou die linkermuisknop ingedruk en selekteer al die selle wat inkomste bevat teen die dae van winkel 1. Hulle is in die lyn, wat genoem word "Winkel 1". Daarna word die koördinate van die gespesifiseerde gebied in die venster weergegee.
Stel dan die wyser in die veld "Toestand 1". Hier moet ons die onderste limiet aandui van die waardes in die selle wat aan die berekening sal deelneem. Spesifiseer 'n uitdrukking ">14000".
In die veld "Toestandreeks 2" voer dieselfde adres op dieselfde manier as wat in die veld ingevoer is "Toestandreeks 1", dit wil sê, ons voer weer die koördinate van die selle in met die inkomstewaardes vir die eerste afsetpunt.
In die veld "Toestand 2" dui die boonste grens van seleksie aan: "<17000".
Nadat al die gespesifiseerde aksies gedoen is, klik op die knoppie "OK".
- Die program gee die resultaat van die berekening. Soos u kan sien, is die finale waarde 5. Dit beteken dat die inkomste in die eerste winkel binne 5 dae van die sewe wat bestudeer is, tussen 14.000 en 17.000 roebels beloop.
SUMIF
'N Ander operateur wat kriteria gebruik, is SUMIF. In teenstelling met vorige funksies, behoort dit tot die wiskundige blok van operateurs. Die taak is om data op te som in selle wat ooreenstem met 'n spesifieke toestand. Die sintaksis is soos volg:
= SUMMES (reeks; maatstaf; [som_reeks])
argument "Range" dui die gebied aan van selle wat gekontroleer sal word of dit aan die toestand voldoen. In werklikheid word dit bepaal deur dieselfde beginsel as die funksionele argument met dieselfde naam COUNTIF.
"Criterion" - is 'n vereiste argument wat die seleksie van selle in die gespesifiseerde data-area spesifiseer wat bygevoeg moet word. Die beginsels om te spesifiseer is dieselfde as vir die soortgelyke argumente van die vorige operatore wat ons hierbo ondersoek het.
"Summation Range" Dit is 'n opsionele argument. Dit dui die spesifieke area aan van die skikking waarin die opsomming uitgevoer sal word. As u dit weglaat en dit nie spesifiseer nie, word dit standaard beskou dat dit gelyk is aan die waarde van die vereiste argument "Range".
Oorweeg nou, soos altyd, die toepassing van hierdie operateur in die praktyk. Op grond van dieselfde tabel, staan ons voor die taak om die hoeveelheid inkomste in Winkel 1 vir die periode wat op 11 Maart 2017 begin, te bereken.
- Kies die sel waarin die resultaat gelewer sal word. Klik op die ikoon. "Voeg funksie in".
- Gaan na Funksie-towenaar in blok "Wiskundige" vind en merk die naam "SUMIF". Klik op die knoppie "OK".
- Die funksie-argumentvenster begin SUMIF. Dit het drie velde wat ooreenstem met die argumente van die gespesifiseerde operateur.
In die veld "Range" voer die area van die tabel in waarin die waardes wat gekontroleer moet word vir die voldoening aan die voorwaardes, sal wees. In ons geval sal dit 'n string datums wees. Plaas die wyser in hierdie veld en kies al die selle wat die datums bevat.
Aangesien ons slegs die opbrengste vanaf 11 Maart in die veld moet byvoeg "Criterion" dryf die waarde ">10.03.2017".
In die veld "Summation Range" u moet die gebied spesifiseer waarvan die waardes wat aan die gespesifiseerde kriteria voldoen, opgesom word. In ons geval is dit die lynwaarde-waardes "Magazin1". Kies die ooreenstemmende reeks velelemente.
Nadat al die gespesifiseerde data ingevoer is, klik op die knoppie "OK".
- Daarna sal die resultaat van die verwerking van data deur die funksie in die voorheen gespesifiseerde element van die werkblad vertoon word. SUMIF. In ons geval is dit gelyk aan 47921.53. Dit beteken dat vanaf 11 Maart 2017, en tot aan die einde van die ontleed periode, die totale inkomste vir Shop 1 47.921.53 roebels beloop.
SUMIFS
Ons voltooi die bestudering van operatore wat kriteria gebruik, met die fokus op funksies SUMIFS. Die doel van hierdie wiskundige funksie is om die waardes van die aangeduide gebiede van die tabel op te som volgens verskillende parameters. Die sintaksis van die gespesifiseerde operateur is soos volg:
= SOMER (som_reeks; toestand_reeks1; voorwaarde1; voorwaarde_reeks2; voorwaarde2; ...)
"Summation Range" - dit is die argument, dit is die adres van die skikking waarin selle wat aan 'n sekere kriterium voldoen, bygevoeg word.
"Toestandreeks" - 'n argument, wat 'n verskeidenheid data is, wat gekontroleer word of dit aan die voorwaarde voldoen het;
"Toestande" - 'n argument wat 'n seleksiekriterium vir optelling verteenwoordig.
Hierdie funksie impliseer bedrywighede met verskillende stelle soortgelyke operatore tegelyk.
Kom ons kyk hoe hierdie operateur toepaslik is om probleme op te los in die konteks van ons verkoopinkomste-tabel in winkels. Ons sal die inkomste wat Shop 1 vir die periode van 09 Maart tot 13 Maart 2017 gebring het, moet bereken. In hierdie geval, by die opsomming van die inkomste, moet slegs die dae in ag geneem word, waarin die inkomste meer as 14.000 roebels was.
- Kies weer die sel om die totaal te vertoon en klik op die ikoon "Voeg funksie in".
- die Funksie-towenaarIn die eerste plek beweeg ons na die blok "Wiskundige", en daar kies ons 'n item genaamd "SUMIFS". Klik op die knoppie. "OK".
- Die operateur-argumente-venster word van stapel gestuur, waarvan die naam hierbo aangedui is.
Stel die wyser in die veld "Summation Range". Anders as die volgende argumente, wys hierdie een van die soort ook op die verskeidenheid waardes waar die data wat by die gespesifiseerde kriteria pas, opgesom word. Kies dan die ryarea "Magazin1", waarin die omsetwaardes vir die ooreenstemmende afsetpunt geleë is.
Gaan na die veld nadat die adres in die venster vertoon is "Toestandreeks 1". Hier sal ons die koördinate van die string met die datums moet vertoon. Klik op die linkermuisknop en kies al die datums in die tabel.
Sit die wyser in die veld "Toestand 1". Die eerste voorwaarde is dat ons die data nie vroeër as 09 Maart saamvat nie. Tik dus die waarde in ">08.03.2017".
Ons beweeg na die argument "Toestandreeks 2". Hier moet u dieselfde koördinate invoer wat in die veld aangeteken is "Toestandreeks 1". Ons doen dit op dieselfde manier, dit wil sê deur die lyn met die datums uit te lig.
Stel die wyser in die veld "Toestand 2". Die tweede voorwaarde is dat die dae waarvoor die opbrengs toegevoeg word, nie later nie as 13 Maart moet wees. Daarom skryf ons die volgende uitdrukking: "<14.03.2017".
Gaan veld toe "Toestandreeks 2". In hierdie geval moet ons dieselfde skikking kies wie se adres as 'n opsommingskikking ingevoer is.
Gaan na die veld nadat die adres van die gespesifiseerde skikking in die venster vertoon is "Toestand 3". As in ag geneem word dat slegs waardes waarvan die waarde meer as 14.000 roebels aan die opsomming sal deelneem, maak ons die volgende aard: ">14000".
Nadat u die laaste aksie voltooi het, klik op die knoppie "OK".
- Die program vertoon die resultaat op 'n vel. Dit is gelyk aan 62491,38. Dit beteken dat die som van die inkomste by die toevoeging daarvan vir dae waarin dit meer as 14.000 roebels is, vir die periode van 9 tot 13 Maart 2017 62.491 roebels beloop.
Voorwaardelike formatering
Die laaste hulpmiddel wat ons beskryf het, wanneer ons met kriteria werk, is voorwaardelike formatering. Dit voer die gespesifiseerde tipe formatteringselle uit wat aan die gespesifiseerde voorwaardes voldoen. Kyk na 'n voorbeeld van werk met voorwaardelike formatering.
Ons selekteer die selle in die tabel in blou, waar die daaglikse waardes meer as 14.000 roebels is.
- Ons kies die volledige reeks elemente in die tabel, wat die inkomste van afsetpunte per dag toon.
- Gaan na die oortjie "Home". Klik op die ikoon Voorwaardelike formateringin die blok geplaas word "Styles" op die band. 'N Lys met aksies word oopgemaak. Klik daarop in posisie "Skep 'n reël ...".
- Die venster vir die opwekking van die formatteringsreël is geaktiveer. Kies die naam in die seleksiegebied van die reëltipe "Formateer slegs selle wat bevat". Kies in die eerste veld van die voorwaarde-blok, uit die lys met moontlike opsies "Selwaarde". Kies die posisie in die volgende veld "Meer". In die laaste - spesifiseer die waarde self, meer as waarvoor u die tabelelemente wil formateer. Ons het dit 14000. Klik op die knoppie om die tipe opmaak te kies "Formaat ...".
- Die formateringsvenster is geaktiveer. Gaan na die oortjie "Vulsel". Kies blou uit die voorgestelde opsies vir vulkleure deur dit met die linkerkant te klik. Nadat die gekose kleur in die omgewing vertoon is "Voorbeeld"Klik op die knoppie "OK".
- Die formateringsreëlgenereringsvenster keer outomaties terug. Daarin ook in die veld "Voorbeeld" blou kleur word vertoon. Hier moet ons een enkele aksie uitvoer: klik op die knoppie "OK".
- Na die laaste aksie sal alle selle van die geselekteerde skikking, wat 'n getal groter as 14000 bevat, blou ingevul word.
Meer inligting oor die funksies van voorwaardelike formatering word in 'n aparte artikel bespreek.
Les: Voorwaardelike formatering in Excel
Soos u kan sien, kan Excel, met behulp van gereedskap wat kriteria in hul werk gebruik, redelik uiteenlopende probleme oplos. Dit kan wees, soos die berekening van die bedrae en waardes, opmaak, sowel as die implementering van baie ander take. Die belangrikste instrumente wat in hierdie program werk met kriteria, dit wil sê met sekere voorwaardes waaronder hierdie aksie geaktiveer word, is 'n stel ingeboude funksies, sowel as voorwaardelike formatering.