Teller antall celler som inneholder de angitte verdiene. Excel statistiske funksjoner.

Microsoft program Excel er ikke lett regnearkredigering, og også den kraftigste applikasjonen for ulike beregninger. Sist men ikke minst dukket denne muligheten opp takket være de innebygde funksjonene. Ved hjelp av noen funksjoner (operatorer) er det til og med mulig å sette beregningsbetingelser, som vanligvis kalles kriterier. La oss se nærmere på hvordan du kan bruke dem når du arbeider i Excel.

Kriterier er betingelsene et program fungerer under visse handlinger... De brukes i en rekke innebygde funksjoner. Navnet deres inneholder oftest uttrykket "HVIS"... Denne gruppen av operatører bør først og fremst inkludere COUNTIF, COUNTIFS, SUMIF, SUMMER... I tillegg til innebygde operatører, brukes også kriterier i Excel når betinget formatering... La oss vurdere applikasjonen deres når vi jobber med ulike verktøy for dette bordprosessor i detaljer.

COUNTIF

Operatørens hovedoppgave COUNTIF, som tilhører den statistiske gruppen, er antall sysselsatte forskjellige betydninger celler som tilfredsstiller en viss spesifisert tilstand. Syntaksen er som følger:

COUNTIF (område, kriterium)

Som du kan se, har denne operatøren to argumenter. "Område" er adressen til utvalget av elementer på arket som skal telles.

"Kriterium" Er et argument som spesifiserer en betingelse for hva nøyaktig cellene i det angitte området må inneholde for å bli inkludert i tellingen. Parameteren kan brukes numerisk uttrykk, tekst eller referanse til cellen som inneholder kriteriet. I dette tilfellet kan følgende tegn brukes for å indikere kriteriet: «<» ("mindre"), «>» ("mer"), «=» ("er lik"), «<>» ("ikke lik"). For eksempel hvis du setter uttrykket «<50» , da vil bare elementene som er spesifisert av argumentet bli tatt med i beregningen "Område" der det er numeriske verdier mindre enn 50. Bruken av disse skiltene for å indikere parametere vil være relevant for alle andre alternativer, som vil bli diskutert i denne leksjonen nedenfor.

La oss nå se hvordan det fungerer med et spesifikt eksempel. denne operatøren på praksis.

Så det er en tabell som viser inntektene for fem butikker per uke. Vi må finne ut antall dager i løpet av denne perioden hvor salgsinntekten i Store 2 oversteg 15 000 rubler.

COUNTIFS

Den neste funksjonen som opererer på kriterier er COUNTIFS... Den tilhører også den statistiske gruppen av operatører. Oppgaven COUNTIFS er et antall celler i en spesifisert matrise som tilfredsstiller et spesifikt sett med betingelser. Det er det faktum at du ikke kan angi én, men flere parametere som skiller denne operatøren fra den forrige. Syntaksen er som følger:

ANTALLHVIS (tilstandsområde1; tilstand1; tilstandsområde2; tilstand2; ...)

"Tilstandsområde" er identisk med det første argumentet i forrige utsagn. Det vil si at det er en referanse til området der cellene som oppfyller de spesifiserte betingelsene vil telles. Denne operatøren lar deg spesifisere flere slike områder samtidig.

"Tilstand" er et kriterium som bestemmer hvilke elementer fra det tilsvarende datasettet som skal inkluderes i tellingen og hvilke som ikke vil. Hvert spesifisert dataområde må spesifisere betingelsen separat, selv om den samsvarer. Det er viktig at alle matriser som brukes som betingelsesomfang har samme antall rader og kolonner.

For å sette flere parametere for samme dataområde, for eksempel for å telle antall celler der verdiene er større enn et visst tall, men mindre enn et annet tall, bør du ta som et argument "Tilstandsområde" spesifiser samme array flere ganger. Men samtidig, som passende argumenter "Tilstand" ulike kriterier bør spesifiseres.

Ved å bruke eksemplet med samme tabell med ukentlige butikkinntekter, la oss se hvordan det fungerer. Vi må finne ut antall dager i uken når inntekten i alle de angitte utsalgsstedene nådde normen som er etablert for dem. Inntektssatsene er som følger:

• Handle 1 - 14 000 rubler;
• Handle 2 - 15 000 rubler;
• Handle 3 - 24 000 rubler;
• Handle 4 - 11 000 rubler;
• Handle 5 - 32 000 rubler.

La oss nå endre oppgaven litt. Vi bør telle antall dager Butikk 1 mottok inntekter på over 14 000 RUB, men mindre enn 17 000 RUB.

SUMIF

En annen operatør som bruker kriterier er SUMIF... I motsetning til de tidligere funksjonene, tilhører den matematikkblokken av operatører. Dens oppgave er å oppsummere data i celler som oppfyller en viss betingelse. Syntaksen er:

SUMIF (område, kriterium, [sum_område])

Argument "Område" indikerer celleområdet som vil bli kontrollert for samsvar med tilstanden. Faktisk er det satt i henhold til samme prinsipp som funksjonsargumentet med samme navn COUNTIF.

"Kriterium"- er et nødvendig argument som spesifiserer parameteren for å velge celler fra det angitte dataområdet som skal oppsummeres. Prinsippene for å spesifisere er de samme som for de analoge argumentene til de tidligere operatørene, som ble diskutert av oss ovenfor.

"Summeringsområde" Er et valgfritt argument. Den peker på et spesifikt område av matrisen som skal summeres. Hvis du utelater det og ikke spesifiserer det, anses det som standard at det er lik verdien av det nødvendige argumentet "Område".

La oss nå, som alltid, vurdere bruken av denne operatøren i praksis. Basert på samme tabell står vi overfor oppgaven med å beregne inntektsbeløpet i Butikk 1 for perioden fra og med 03.11.2017.

SUMMER

La oss avslutte vår studie av operatører som bruker kriteriene ved å fokusere på funksjonen SUMMER... Oppgaven til denne matematiske funksjonen er å summere verdiene til de spesifiserte områdene i tabellen, valgt av flere parametere. Syntaksen for den angitte operatoren er som følger:

SUMHVIS (sum_område; betingelsesområde1; betingelse1; tilstandsområde2; tilstand2; ...)

"Summeringsområde"- dette er et argument som er adressen til matrisen der celler som oppfyller et bestemt kriterium vil bli lagt til.

"Tilstandsområde"- et argument som er en rekke data som kontrolleres for samsvar med en betingelse;

"Tilstand"- et argument som er et utvalgskriterium for addisjon.

Denne funksjonen innebærer operasjoner med flere sett med slike operatører samtidig.

La oss se hvordan denne operatøren kan brukes til å løse problemer i sammenheng med tabellen over inntekter fra salg i utsalgssteder. Vi vil trenge å beregne inntektene som Store 1 ga i perioden 09. til 13. mars 2017. I dette tilfellet, når du summerer inntekt, bør bare de dagene tas i betraktning, der inntektene oversteg 14 000 rubler.

Betinget formatering

Det siste verktøyet vi har beskrevet for å bruke kriterier er betinget formatering. Den utfører den angitte typen formatering på celler som oppfyller de angitte betingelsene. La oss ta en titt på et eksempel på arbeid med betinget formatering.

La oss fremheve de tabellcellene i blått, der de daglige verdiene overstiger 14 000 rubler.

For mer informasjon om mulighetene for betinget formatering, se en egen artikkel.

Som du kan se, ved hjelp av verktøy som bruker kriterier i arbeidet, kan du løse ganske forskjellige oppgaver i Excel. Dette kan være, som beregning av summer og verdier, og formatering, samt gjennomføring av mange andre oppgaver. Hovedverktøyene som fungerer i dette programmet med kriterier, det vil si med visse forhold, under hvilke den angitte handlingen aktiveres, er et sett med innebygde funksjoner, samt betinget formatering.

Artikkelen er et utvalg oppgaver for gjennomføring av avsluttende leksjoner om å lære Excel. Oppgavene er utstyrt med forklaringer, som blir mindre og mindre detaljerte for hver øvelse. Denne tilnærmingen tvinger studentene ikke bare til å utføre de foreslåtte øvelsene, men også til å huske arbeidsteknikker, fordi ellers må de gå tilbake til arbeidet som allerede er fullført og håndtere oppgaven på nytt; og fremmer også enkel og forståelig vurdering av arbeidet deres for studentene.

Oppgave 1. "Shop"

Flere leverandører tar med meieriprodukter til Melkebutikken hver dag. Lag en tabell over leveringer per dag for denne butikken. I en egen tabell bestemmer du antall leveranser av en type meieriprodukter per dag, totalkostnad og gjennomsnittspris .

Kommentarer:

I. Lag to tabeller: med inndata og resultater.

1. Tabellen med de første dataene skal inneholde følgende felt: "Nr.", "Navn", "Leverandør", "Antall, l", "Pris", "Kostnad".
2. Fyll ut tabellen med innledende data, for eksempel:

Tabell 1

3. Tabellen med resultatene skal inneholde følgende felt: "Navn", "Antall leveranser", "Totalkostnad", "Gjennomsnittspris".
4. I den andre tabellen, i kolonnen "Navn", må navnene på meieriprodukter fra den første tabellen vises én gang, for eksempel:

tabell 2

II. Skriv inn formler i tabeller.

1. I den første tabellen legges formelen bare inn i "Kostnad"-feltet: for å bestemme kostnaden, må du multiplisere mengden med prisen. Omtrentlig form for en formel i en celle

F2: = D2 * E2.

Resultat:

Tabell 1.1

2. I den andre tabellen legges formler inn i feltene "Antall leveranser", "Totalkostnad", Gjennomsnittspris ":

a) For å bestemme antall leveranser, må du bestemme hvor mange ganger om dagen, for eksempel melk som ble brakt til butikken. For å gjøre dette, må du bruke funksjonen COUNTIF, som bestemmer mengden data i området som er lik kriteriet. Omtrentlig form for en formel i en celle C15:

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

hvor B2: B11- utvalg av navn på meieriprodukter fra den første tabellen, og B15- celle i den andre tabellen som inneholder navnet på produktet (for dette eksemplet - "Melk").

Resultat:

Tabell 2.1

b) For å bestemme den totale kostnaden for alle produkter med samme navn, må du velge fra den første tabellen og legge sammen kostnadene for alt, for eksempel melk i en butikk. For å gjøre dette, bruk funksjonen SUMIF, som oppsummerer dataene valgt i henhold til et gitt kriterium i et gitt område. Omtrentlig form for en formel i en celle D15:

= SUMIF (B $ 2: B $ 11; B15; F $ 2: F $ 11),

hvor F2: F11- rekkevidden av kostnadene for produktet fra det første bordet.

Resultat:

Tabell 2.2

c) For å bestemme gjennomsnittsprisen legger du til alle priser for én type produkt (tabell 1), og deler deretter på antall leveranser (tabell 2). Omtrentlig form for en formel i en celle E15:

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

hvor E2: E11- et utvalg med priser fra tabell 1, og C15- en celle som inneholder antall leveranser av et gitt produkt.

Resultat:

Tabell 2.3

Merknader:

Denne øvelsen kan suppleres med følgende oppgaver (og ikke bare dem):

1. Bestem mengden (i liter) av hvert produkt som bringes til butikken.
2. Lag en tabell "Leverandører", der du kan bestemme mengden hver leverandør brakte produkter til butikken for, totalvekten av produktene som hver leverandør hadde med, og hvor mange typer produkter hver leverandør brakte.

Oppgave 2. Studentstipend

Lag en tabell over studentenes eksamenskarakterer og fastsett på grunnlag av den gjennomsnittlig poengsum og stipendbeløpet for hver student. Reglene for å bestemme størrelsen på stipendet:

• stipendet deles ikke ut hvis det er minst én toer blant karakterene;
• 3,0 < gjennomsnittlig poengsum< 3,5 - 1000р.
• 3,5 < gjennomsnittlig poengsum< 4,0 - 1200р.
• 4,0 < gjennomsnittlig poengsum< 4,5 - 1500р.
• 4,5 < gjennomsnittlig poengsum< 5,0 - 1800р.
• gjennomsnittlig poengsum = 5,0 - 2000 rubler.

Bestem det totale antallet tildelte stipender.

Merknader:

Formuleringen av dette problemet, med noen endringer, er hentet fra læreboken "Informatikk: Praktisk arbeid med teknologien ved å jobbe på en datamaskin", red. N.V. Makarova.

Kommentarer:

• Etternavn i tabellen må være minst 10, elementer minst 5, for eksempel:

• For å bestemme antall toere, bruk funksjonen COUNTIF... Du kan sette inn en annen kolonne for å bestemme antall toere, eller du kan neste en funksjon COUNTIF i funksjon HVIS, som vil bli brukt til å bestemme størrelsen på stipendet. Den omtrentlige formen til formelen:

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

Resultat:

Oppgave 3. "Armetbetonganlegg"

Armeringsbetonganlegget produserer byggeklosser i betong. Blokkegenskaper: merke, lengde (m), bredde (m), høyde (m) og egenvekt til betongen som blokken er laget av (kg / m3). Anlegget mottok en ordre, som er en liste som inneholder merkene til de nødvendige blokkene og antall blokker av hvert merke. Bestem hvor mange biler som kreves for å sende blokker til kunden hvis: blokker av forskjellige merker ikke kan være i én bil, og bæreevnen til én bil er N tonn.

I. For å løse problemet må du lage to tabeller: med de første dataene og resultatene.

1. Den første tabellen skal inneholde følgende felt: "Brand", "Length", "Width", "Height", "Specific gravity", "Block weight".
a) Fyll ut tabellen, bortsett fra kolonnen "Blokkvekt" (minst 10 merker).
2. Skriv inn en egen celle for kapasitetsverdien.
3. Den andre tabellen skal inneholde feltene: "Merke", "Antall blokker", "Antall biler".
a) I "Merkevare"-kolonnen gjentar du navnene på flere blokkmerker fra den første tabellen (minst 4).
b) Fyll ut kolonnen "Antall blokker" med vilkårlige data.

II. Legge inn formler.

1. Bestem vekten på blokken (i den første tabellen) ved hjelp av funksjonen PRODUKT og konvertere til tonn.
2. For å bestemme antall biler for blokker av hvert merke, må du multiplisere antall blokker med vekten av blokken og dividere med bæreevnen:
a) For å velge tilsvarende blokkvekt fra den første tabellen, bruk funksjonen SUMIF.
b) Siden antall biler bare kan være et heltall, avrund resultatet til et heltall ved hjelp av funksjonen ROUNDUP.
3. I en egen celle bestemmer du det totale antallet vogner for blokker av alle merker.

Oppgave 4. "Prisliste"

Kosmetikkselskapet utsteder en prisliste til distributørene, som angir navnet på produktet og prisen for distributøren og for kunden. Lag en "elektronisk kalkulator" for distributøren, ved hjelp av hvilken han vil være i stand til å bestemme hvor mye han trenger for å kjøpe produktene bestilt av kjøperen fra selskapet, hvor mye kunden skal betale ham, og fortjenesten som han vil motta som følge av salget.

Litteratur

1. Informatikk: Workshop om teknologien ved å jobbe på en datamaskin / Ed. N.V. Makarova. - 3. utg., Revidert. - M .: Finans og statistikk, 2003. - 256 s .: ill.

COUNTIF-funksjonen er en av de mest brukte funksjonene i det kraftige Excel-regnearket fra Microsoft. Hva er den til? Dette fremgår tydelig av navnet. Det vil hjelpe enhver bruker som har stilt spørsmålet "Hvordan telle celler i Excel i henhold til et gitt kriterium?" For å få de nødvendige tallene.

Funksjonsargumenter

Denne funksjonen tar to nødvendige argumenter. Den første er området der antall celler som tilfredsstiller betingelsen vil bli talt.

Det andre argumentet er en betingelse, som kan spesifiseres i form av et tall eller ord, så vel som i form av en betingelse.

La oss utarbeide et dataområde og på grunnlag av det vurdere hvordan eksempler som er spesifikke for funksjonen "COUNTIF" i Excel ser ut. La oss si at vi har en liste over grønnsaker og frukt lagret i et lager med en liste over kjøpspris, salgspåslag og kjøpsdato. Vi begynner å jobbe med tabeller i Excel - vi overfører følgende data til regnearket:

Viktig tillegg

Det andre argumentet til funksjonen er en betingelse. Hvis vi ønsker å spesifisere et tall eller en celleadresse som et argument, så kan vi skrive direkte slik. Hvis vi ønsker å bygge en kompleks tilstand med sammenligningstegn, søke etter en tekst eller en bestemt dato, så må argumentet omsluttes av anførselstegn. Nedenfor vil vi også vurdere hvordan du kan kombinere ulike funksjoner i en tilstand ved å bruke "ampersand"-tegnet.

Enkle betingelser for en funksjon

Datasettet introdusert i forrige seksjon vil hjelpe deg å forstå COUNTIF-funksjonen i Excel. Eksemplene som er diskutert i denne delen kommer til å være ganske enkle, men de er grunnleggende og må vurderes før du går videre til de mer komplekse.

Vi vil oppsummere formlene, resultatene av deres utførelse i tabellform, og også skrive en kort forklarende kommentar. Alle svar er basert på tabelldataene som er lagt inn ovenfor, og utføres per 16. mai 2017.

Jokertegn

De såkalte jokertegnene er mye brukt med COUNTIF-funksjonen i Excel. Eksempler på jokertegn:

Fullt arbeid med tabeller i Excel og analyse av dataene som er lagret i dem er nesten umulig uten bruk av jokertegn.

Betingelser for en jokertegnfunksjon. Kombinere funksjoner

Kombinasjon med andre funksjoner for å skape ønsket tilstand skjer ved å kombinere en logisk betingelse ("større enn", "mindre", "ikke lik") og ønsket funksjon med et og-tegn (&).

La oss se på mer komplekse eksempler på bruk av COUNTIF-funksjonen i tabellform.

COUNTIF-funksjonen tilhører gruppen av statistiske funksjoner. Finner antall celler etter et spesifikt kriterium. Fungerer med numeriske og tekstverdier, datoer.

Funksjonssyntaks og funksjoner

La oss først se på argumentene til funksjonen:

• Område er en gruppe verdier som skal analyseres og telles (påkrevd).
• Kriterium - tilstanden du vil telle cellene med (påkrevd).

Utvalget av celler kan inneholde tekst, numeriske verdier, datoer, matriser, referanser til tall. Tomme celler funksjonen ignorerer.

• Hvis COUNTIF-funksjonen refererer til et område i en annen arbeidsbok, må den arbeidsboken være åpen.
• Kriterieargumentet må stå i anførselstegn (unntatt lenker).
• Funksjonen skiller ikke mellom store og små bokstaver for tekstverdier.
• Du kan bruke jokertegn når du formulerer en tellebetingelse. "?" - hvilken som helst karakter. "*" - hvilken som helst sekvens av tegn. For å få formelen til å se direkte etter disse tegnene, sett en tilde (~) foran dem.
• For at formelen skal fungere ordentlig, bør ikke celler med tekstverdier inneholde mellomrom eller tegn som ikke skrives ut.

COUNTIF-funksjon i Excel: eksempler

La oss telle numeriske verdier i ett område. Tellebetingelsen er ett kriterium.

Vi har et bord som dette:

La oss telle antall celler med tall større enn 100. Formel: = COUNTIF (B1: B11; "> 100"). Område - B1: B11. Tellekriteriet er "> 100". Resultat:

Hvis tellebetingelsen er lagt inn i en egen celle, kan du bruke lenken som et kriterium:

La oss telle tekstverdier i samme område. Søkeordet er ett kriterium.

Formel: = COUNTIF (A1: A11; "krakker"). Eller:

I det andre tilfellet ble en cellereferanse brukt som et kriterium.

En jokertegnformel: = COUNTIF (A1: A11; "tab *").

For å beregne antall verdier som slutter på "og" som inneholder et hvilket som helst antall tegn: = COUNTIF (A1: A11; "* og"). Vi får:

Formelen talte "senger" og "benker".

Vi bruker søkeordet "ikke lik" i COUNTIF-funksjonen.

Formel: = COUNTIF (A1: A11; "" & "stoler"). Operatøren "" betyr "ikke lik". Et-tegnet (&) kombinerer den gitte operatoren og betydningen "stoler".

Det er ofte nødvendig å utføre COUNTIF-funksjonen i Excel etter to kriterier. På denne måten kan du utvide mulighetene betydelig. Ta i betraktning spesielle tilfeller bruk av COUNTIF i Excel og eksempler med to betingelser.

COUNTIF med to betingelser i Excel brukes veldig ofte for automatiserte og effektivt arbeid med data. Derfor anbefales den avanserte brukeren på det sterkeste å studere alle eksemplene ovenfor nøye.

INTERMEDIATE.TOTALS og COUNTSIIF

La oss telle antall varer solgt etter grupper.

Formelen har funnet antall verdier for Chairs-gruppen. På et stort antall linjer (mer enn tusen) en slik kombinasjon av funksjoner kan være nyttig.