Räknar antalet celler som innehåller angivna värden. Excel statistiska funktioner.

Microsoft program Excel är inte lätt kalkylbladsredigerare, och även en kraftfull applikation för olika beräkningar. Sist men inte minst dök denna möjlighet upp tack vare de inbyggda funktionerna. Med hjälp av vissa funktioner (operatorer) kan du till och med ställa in beräkningsvillkor, som vanligtvis kallas kriterier. Låt oss ta reda på mer i detalj hur du kan använda dem när du arbetar i Excel.

Kriterier representerar de villkor under vilka ett program fungerar vissa handlingar. De används i en rad inbyggda funktioner. Deras namn innehåller oftast uttrycket "OM". Denna grupp av operatörer bör först och främst omfatta COUNTIF, COUNTIFS, SUMIF, SUMMER. Förutom inbyggda operatörer är kriterier i Excel också vana vid villkorlig formatering. Låt oss överväga deras användning när vi arbetar med olika verktyg av detta bordsprocessor i detalj.

COUNTIF

Operatörens huvuduppgift COUNTIF relaterat till den statistiska gruppen är antalet sysselsatta olika betydelser celler som uppfyller ett visst specificerat villkor. Dess syntax är följande:

ANTAL.OM(intervall; kriterium)

Som du kan se har denna operatör två argument. "Räckvidd" representerar adressen till arrayen av element på kalkylbladet som ska räknas i.

"Kriterium"är ett argument som anger vad exakt cellerna i det angivna området måste innehålla för att inkluderas i räkningen. Parametern kan användas numeriskt uttryck, text eller länk till cellen som innehåller kriteriet. I det här fallet kan följande tecken användas för att ange kriteriet: «<» ("mindre"), «>» ("Mer"), «=» ("likar"), «<>» ("inte jämnlikt"). Till exempel om du ställer in uttrycket «<50» , då kommer endast de element som anges av argumentet att beaktas vid beräkningen "Räckvidd", där de finns numeriska värden mindre än 50. Användningen av dessa tecken för att indikera parametrar kommer att vara relevant för alla andra alternativ som kommer att diskuteras i den här lektionen nedan.

Låt oss nu ta ett konkret exempel för att se hur det fungerar denna operatör på praktiken.

Så det finns en tabell som visar intäkterna för fem butiker per vecka. Vi måste ta reda på antalet dagar under denna period då Store 2:s försäljningsintäkter översteg 15 000 rubel.

COUNTIFS

Nästa funktion som arbetar på kriterier är COUNTIFS. Den tillhör också den statistiska gruppen av operatörer. Uppgiften COUNTIFSär ett antal celler i en specificerad array som uppfyller en specificerad uppsättning villkor. Det är det faktum att du inte kan ange en utan flera parametrar som skiljer denna operatör från den föregående. Syntaxen är som följer:

ANTAL.OMF(villkorsintervall1,villkor1,skickintervall2,villkor2,...)

"Konditionsintervall"är identisk med det första argumentet i föregående påstående. Det vill säga, det representerar en länk till området där celler som uppfyller de angivna villkoren kommer att räknas. Denna operatör låter dig ange flera sådana områden samtidigt.

"Skick"är ett kriterium som avgör vilka element från motsvarande datamatris som kommer att inkluderas i beräkningen och vilka som inte kommer att ingå. Varje angivet dataområde måste anges separat, även om det stämmer. Det är obligatoriskt att alla arrayer som används som villkorsområden har samma antal rader och kolumner.

För att ställa in flera parametrar för samma dataområde, till exempel för att räkna antalet celler som innehåller värden större än ett visst antal, men mindre än ett annat tal, använd som argument "Konditionsintervall" ange samma array flera gånger. Men samtidigt som motsvarande argument "Skick" olika kriterier bör anges.

Med hjälp av samma tabell med veckobutiksintäkter som ett exempel, låt oss se hur det här fungerar. Vi måste ta reda på antalet dagar i veckan när inkomsten på alla specificerade butiker nådde den norm som fastställts för dem. Intäktsnivåerna är följande:

• Förvara 1 - 14 000 rubel;
• Förvara 2 - 15 000 rubel;
• Förvara 3 – 24 000 rubel;
• Förvara 4 – 11 000 rubel;
• Förvara 5 – 32 000 rubel.

Låt oss nu ändra problemet lite. Vi bör räkna antalet dagar under vilka Butik 1 fick intäkter på över 14 000 rubel, men mindre än 17 000 rubel.

SUMIF

En annan operatör som använder kriterier är SUMIF. Till skillnad från de tidigare funktionerna tillhör den ett matematiskt block av operatorer. Dess uppgift är att sammanfatta data i celler som uppfyller ett visst villkor. Syntaxen är:

SUMMAOM(intervall; kriterium; [summa_intervall])

Argument "Räckvidd" indikerar området av celler som kommer att kontrolleras för att se om villkoret är uppfyllt. Faktum är att den är inställd enligt samma princip som funktionsargumentet med samma namn COUNTIF.

"Kriterium"— är ett obligatoriskt argument som anger parametern för att välja celler från det angivna dataområdet som ska summeras. Indikationsprinciperna är desamma som för liknande argument från tidigare operatörer, som vi diskuterade ovan.

"Sammanfattningsintervall"är ett valfritt argument. Den pekar på ett specifikt område av arrayen där summeringen kommer att utföras. Om den utelämnas och inte specificeras, anses den som standard vara lika med värdet på det obligatoriska argumentet "Räckvidd".

Låt oss nu, som alltid, överväga användningen av denna operatör i praktiken. Utifrån samma tabell står vi inför uppgiften att beräkna intäkterna i Butik 1 för perioden från och med 2017-11-03.

SUMMER

Låt oss slutföra vår studie av operatorer som använder kriterier genom att fokusera på funktionen SUMMER. Uppgiften för denna matematiska funktion är att sammanfatta värdena för de angivna områdena i tabellen, valda enligt flera parametrar. Syntaxen för den angivna operatorn är:

SUMIFS(summa_intervall, villkorsintervall1, villkor1, villkorsintervall2, villkor2,...)

"Sammanfattningsintervall"är ett argument som är adressen till arrayen där de celler som uppfyller ett visst kriterium kommer att läggas till.

"Konditionsintervall"— Ett argument som representerar en rad data som kontrolleras för att uppfylla villkoret.

"Skick"— Ett argument som representerar ett urvalskriterium för addition.

Denna funktion involverar operationer med flera uppsättningar av liknande operatörer samtidigt.

Låt oss se hur den här operatören är tillämpbar för att lösa problem inom ramen för vår tabell över intäkter från försäljning i butiker. Vi kommer att behöva beräkna den inkomst som Butik 1 inbringat för perioden 9 mars till 13 mars 2017. I det här fallet, när man summerar inkomsten, bör endast de dagar då intäkterna översteg 14 000 rubel beaktas.

Villkorlig formatering

Det sista verktyget vi har beskrivit som använder kriterier är villkorlig formatering. Den utför en viss typ av formatering på celler som uppfyller angivna villkor. Låt oss ta en titt på ett exempel på att arbeta med villkorlig formatering.

Låt oss markera de tabellcellerna i blått där de dagliga värdena överstiger 14 000 rubel.

Möjligheterna med villkorlig formatering beskrivs mer i detalj i en separat artikel.

Som du kan se, med hjälp av verktyg som använder kriterier i sitt arbete, kan du lösa ganska olika problem i Excel. Detta kan antingen vara att räkna summor och värden, eller formatering, såväl som att utföra många andra uppgifter. De viktigaste verktygen i detta program som arbetar med kriterier, det vill säga med vissa villkor som, när de uppfylls, aktiverar den angivna åtgärden, är en uppsättning inbyggda funktioner, såväl som villkorlig formatering.

Artikeln är ett urval av uppgifter för avslutande lektioner om att lära sig Excel. Uppgifterna är försedda med förklaringar som blir mindre och mindre detaljerade för varje övning. Detta tillvägagångssätt tvingar eleverna inte bara att utföra de föreslagna övningarna, utan också att komma ihåg arbetsteknikerna, för annars måste de återgå till arbetet som redan är avslutat och ta itu med uppgiften igen; och underlättar även en bedömning av deras arbete som är enkel och begriplig för eleverna.

Övning 1. "Shop"

Flera leverantörer levererar mejeriprodukter till Moloko-butiken varje dag. Skapa en daglig redovisningstabell för den här butiken. Bestäm i en separat tabell antalet leveranser av mejeriprodukter av en typ per dag, deras totala kostnad och genomsnittspris .

Kommentarer:

I. Skapa två tabeller: med initiala data och resultat.

1. Tabellen med källdata måste innehålla följande fält: "Nej", "Namn", "Leverantör", "Mängd, l", "Pris", "Kostnad".
2. Fyll i tabellen med initiala data, till exempel:

bord 1

3. Tabellen med resultaten bör ha följande fält: "Namn", "Antal leveranser", "Total kostnad", "Genomsnittspris".
4. I den andra tabellen i kolumnen "Namn" ska namnen på mejeriprodukter från den första tabellen visas en gång, till exempel:

Tabell 2

II. Skriv in formler i tabeller.

1. I den första tabellen anges formeln endast i fältet "Kostnad": för att bestämma kostnaden måste du multiplicera kvantiteten med priset. Ungefärlig vy av en formel i en cell

F2: =D2*E2.

Resultat:

Tabell 1.1

2. I den andra tabellen skrivs formler in i fälten "Antal leveranser", "Total kostnad", Genomsnittspris:

a) För att bestämma antalet leveranser måste du bestämma hur många gånger per dag, till exempel mjölk levererades till butiken. För att göra detta måste du använda funktionen COUNTIF, som bestämmer mängden data i intervallet som är lika med kriteriet. Ungefärlig vy av en formel i en cell C15:

=ANTALOM(B$2:B$11;B15),

Var B2:B11- utbud av namn på mejeriprodukter från den första tabellen, och B15- cell i den andra tabellen som innehåller namnet på produkten (för detta exempel - "Mjölk").

Resultat:

Tabell 2.1

b) För att bestämma den totala kostnaden för alla produkter med samma namn måste du välja från den första tabellen och lägga till kostnaden för allt, till exempel mjölk i en butik. För att göra detta, använd funktionen SUMIF, som sammanfattar data som valts av ett givet kriterium i ett givet intervall. Ungefärlig vy av en formel i en cell D15:

=SUMMAOM(B$2:B$11,B15,F$2:F$11),

Var F2:F11- produktkostnadsintervall från första bordet.

Resultat:

Tabell 2.2

c) För att bestämma det genomsnittliga priset måste du lägga ihop alla priser för en produkttyp (tabell 1) och sedan dividera med antalet leveranser (tabell 2). Ungefärlig vy av en formel i en cell E15:

=SUMMAOM(B$2:B$11,B15,E$2:E$11)/C15,

Var E2:E11- sortiment med priser från tabell 1, och C15- en cell som innehåller antalet leveranser av en given produkt.

Resultat:

Tabell 2.3

Anmärkningar:

Denna övning kan kompletteras med följande uppgifter (och inte bara dem):

1. Bestäm kvantiteten (i liter) av varje produkt som tas in i butiken.
2. Skapa en "Leverantörer"-tabell där du kan fastställa mängden produkter som varje leverantör tog med till butiken, den totala vikten av produkter som varje leverantör tog med och hur många typer av produkter varje leverantör tog med.

Övning 2. ”Studentstipendier”

Sammanställ en tabell över studentexamensresultat och, baserat på den, bestäm medelpoängen och stipendiebeloppet för varje student. Regler för att bestämma stipendiets storlek:

• Stipendiet delas inte ut om betygen innehåller minst ett D;
• 3,0 < GPA< 3,5 - 1000р.
• 3,5 < GPA< 4,0 - 1200р.
• 4,0 < GPA< 4,5 - 1500р.
• 4,5 < GPA< 5,0 - 1800р.
• genomsnittlig poäng = 5,0 - 2000 gnid.

Bestäm det totala antalet stipendier som delas ut.

Anmärkningar:

Formuleringen av detta problem, med vissa modifieringar, är hämtad från läroboken "Informatik: Workshop om tekniken att arbeta på en dator", red. N.V. Makarova.

Kommentarer:

• Det måste finnas minst 10 efternamn i tabellen, minst 5 ämnen, till exempel:

• För att bestämma antalet tvåor, använd funktionen COUNTIF. Du kan infoga en annan kolumn för att bestämma antalet tvåor, eller så kan du kapsla en funktion COUNTIF till en funktion OM, som kommer att användas för att fastställa stipendiets storlek. En ungefärlig form av formeln:

=OM(ANTALOM(C3:G3,2)>0,0,OM(H3<3,5;1000;ЕСЛИ(H3<4;1200;
OM(H3<4,5;1500;ЕСЛИ(H3<5;1800;2000))))).

Resultat:

Övning 3. "Anläggning av armerade betongprodukter"

Armerad betongfabriken producerar byggstenar i betong. Egenskaper för blocken: märke, längd (m), bredd (m), höjd (m) och specifik vikt för betongen som blocket är tillverkat av (kg/m3). Fabriken fick en beställning, som är en lista som innehåller märkena av obligatoriska block och antalet block av varje märke. Bestäm hur många vagnar som kommer att krävas för att skicka block till kunden om: block av olika märken inte kan finnas i en vagn och en vagns bärförmåga är N ton.

I. För att lösa problemet måste du skapa två tabeller: med initiala data och resultat.

1. Den första tabellen bör innehålla fälten: "Varumärke", "Längd", "Bredd", "Höjd", "Specific gravity", "Blockvikt".
a) Fyll i tabellen, förutom kolumnen "Blockvikt" (minst 10 markeringar).
2. Skapa en separat cell för lastkapacitetsvärdet.
3. Den andra tabellen ska innehålla fälten: "Varumärke", "Antal block", "Antal bilar".
a) I kolumnen "Varumärke", upprepa namnen på flera märken av block från den första tabellen (minst 4).
b) Fyll i kolumnen "Antal block" med godtyckliga data.

II. Ange formler.

1. Bestäm vikten på blocket (i den första tabellen) med hjälp av funktionen PRODUKT och konvertera till ton.
2. För att bestämma antalet bilar för block av varje märke måste du multiplicera antalet block med blockets vikt och dividera med bärförmågan:
a) För att välja motsvarande blockvikt från den första tabellen, använd funktionen SUMIF.
b) Eftersom antalet bilar bara kan vara ett heltal, avrunda resultatet till ett heltal med hjälp av funktionen RUNDA UPP.
3. Bestäm det totala antalet bilar för block av alla märken i en separat cell.

Övning 4. "Prislista"

Ett kosmetikföretag utfärdar en prislista till distributörer, som anger produktens namn och dess pris för distributören och för kunden. Skapa en "elektronisk kalkylator" för distributören, med hjälp av vilken han kan bestämma hur mycket pengar han kommer att behöva för att köpa produkterna som köparen beställt från företaget, hur mycket kunden måste betala honom och vinsten han kommer att få till följd av försäljningen.

Litteratur

1. Informatik: Workshop om datorteknik / Ed. N.V. Makarova. – 3:e uppl., reviderad. – M.: Finans och statistik, 2003. – 256 s.: ill.

COUNTIF-funktionen är en av de mest använda funktionerna i den kraftfulla Excel-kalkylbladsprocessorn från Microsoft. Vad är det för? Detta framgår redan av namnet. Det kommer att hjälpa alla användare som har ställt frågan "Hur räknar man celler i Excel enligt ett givet kriterium?"

Funktionsargument

Denna funktion tar två nödvändiga argument. Den första av dem är intervallet där antalet celler som uppfyller villkoret kommer att räknas.

Det andra argumentet är ett villkor, som kan anges i form av ett tal eller ett ord, eller i form av ett villkor.

Låt oss förbereda ett dataintervall och, baserat på det, titta på hur exempel som är specifika för funktionen "COUNTIF" i Excel ser ut. Låt oss säga att vi har en lista över grönsaker och frukter som lagras i ett lager med en lista över deras inköpspris, försäljningspremie och inköpsdatum. Vi börjar arbeta med tabeller i Excel - överför följande data till kalkylbladet:

Viktigt tillägg

Funktionens andra argument är ett villkor. Om vi ​​vill ange ett nummer eller en celladress som ett argument, så kan vi skriva direkt så här. Om vi ​​vill bygga ett komplext villkor med jämförelsetecken, sök efter text eller ett specifikt datum, så måste argumentet omges av citattecken. Nedan kommer vi också att diskutera hur du med hjälp av et-tecken kan kombinera olika funktioner i ett tillstånd.

Enkla villkor för en funktion

Datauppsättningen som introducerades i föregående avsnitt hjälper dig att förstå COUNTIF-funktionen i Excel. Exemplen som diskuteras i det här avsnittet kommer att vara ganska enkla, men de är grundläggande och måste övervägas innan du går vidare till mer komplexa.

I tabellform kommer vi att sammanfatta formlerna, resultaten av deras utförande och även skriva en kort förklarande kommentar. Alla svar är baserade på tabelldata som anges ovan och utförs från och med 2017-05-16.

Jokertecken

Så kallade jokertecken används ofta med COUNTIF-funktionen i Excel. Exempel på jokertecken:

Fullständigt arbete med tabeller i Excel och analys av data som lagras i dem är nästan omöjligt utan användning av jokertecken.

Villkor för en jokerteckenfunktion. Kombinera funktioner

Att kombinera med andra funktioner för att skapa det önskade villkoret sker genom att koppla ett logiskt villkor ("större än", "mindre än", "inte lika") och den önskade funktionen med et-tecken (&).

I tabellform kommer vi att överväga mer komplexa exempel på användning av funktionen "COUNTIF".

Funktionen COUNTIF är en del av gruppen statistiska funktioner. Låter dig hitta antalet celler enligt ett visst kriterium. Fungerar med numeriska och textvärden, datum.

Funktionssyntax och funktioner

Låt oss först titta på funktionsargumenten:

• Range – en grupp av värden för analys och beräkning (obligatoriskt).
• Kriterier – villkoret för att celler måste räknas (krävs).

Ett cellintervall kan innehålla text, numeriska värden, datum, matriser och referenser till siffror. Tomma celler funktionen ignoreras.

• Om COUNTIF-funktionen refererar till ett intervall i en annan arbetsbok måste den arbetsboken vara öppen.
• Argumentet "Kriterier" måste omges av citattecken (förutom referenser).
• Funktionen är inte skiftlägeskänslig för textvärden.
• Du kan använda jokertecken när du formulerar ett räknevillkor. "?" - vilken symbol som helst. "*" - vilken sekvens av tecken som helst. För att formeln ska söka direkt efter dessa tecken sätter vi ett tildetecken (~) framför dem.
• För att formeln ska fungera korrekt bör det inte finnas några mellanslag eller icke-utskrivbara tecken i celler med textvärden.

COUNTIF-funktionen i Excel: exempel

Låt oss räkna numeriska värden i ett område. Räknevillkoret är ett kriterium.

Vi har ett bord så här:

Låt oss räkna antalet celler med siffror större än 100. Formel: =COUNTIF(B1:B11,">100"). Räckvidd – B1:B11. Räknekriteriet är ">100". Resultat:

Om du anger beräkningsvillkoret i en separat cell kan du använda länken som ett kriterium:

Låt oss räkna ut textvärden i samma intervall. Sökvillkoret är ett kriterium.

Formel: =ANTALOM(A1:A11,"stolar"). Eller:

I det andra fallet användes en cellreferens som ett kriterium.

Formel med jokertecken: =COUNTIF(A1:A11,"tab*").

För att beräkna antalet värden som slutar på "och" som innehåller valfritt antal tecken: =COUNTIF(A1:A11,"*and"). Vi får:

Formeln räknade "sängar" och "banketter".

Vi använder sökvillkoret "inte lika" i COUNTIF-funktionen.

Formel: =COUNTIF(A1:A11,""&"stolar"). Operatorn "" betyder "inte lika". Et-tecken (&) kombinerar denna operator och betydelsen av "stolar".

Ofta behöver du utföra COUNTIF-funktionen i Excel mot två kriterier. På så sätt kan du utöka dess kapacitet avsevärt. Låt oss överväga speciella fall tillämpningar av COUNTIF i Excel och exempel med två villkor.

COUNTIF med två villkor i Excel används mycket ofta för automatiserade och effektivt arbete med data. Därför rekommenderas den avancerade användaren att noggrant studera alla exemplen ovan.

SUBTOTALER och ANTALIF

Låt oss beräkna antalet sålda varor per grupp.

Formeln hittade antalet värden för gruppen "Stolar". På stort antal rader (mer än tusen), kan en sådan kombination av funktioner vara användbar.