Jak korzystać z funkcji programu Excel ŚREDNIA.JEŻELI -

Podsumowanie

Funkcja programu Excel ŚREDNIA.WARUNKÓW oblicza średnią liczb w zakresie spełniającym co najmniej jedno kryterium. Kryteria używane dla ŚREDNIA.JEŻELI mogą obejmować operatory logiczne (>, <,, =) i symbole wieloznaczne (* ,?) w celu częściowego dopasowania.

Cel, powód

Średnia liczba komórek spełniających wiele kryteriów

Wartość zwracana

Średnia z komórek spełniających wszystkie kryteria

Składnia

= AVERAGEIFS (średni_rng; zakres1; kryterium1; (zakres2); (kryterium2)…)

Argumenty

  • avg_rng - zakres do średniej.
  • range1 - pierwszy zakres do ewaluacji.
  • kryteria1 - kryteria do zastosowania dla zakresu1.
  • range2 - (opcjonalne) Drugi zakres do oceny.
  • kryteria2 - (opcjonalne) kryteria do zastosowania w zakresie2.

Wersja

Excel 2007

Uwagi dotyczące użytkowania

Funkcja ŚREDNIA.WARUNKÓW oblicza średnią liczb w zakresie, które spełniają podane kryteria. Pierwszy zakres podany do funkcji AVERAGEIFS to zakres zawierający liczby do średniej. Kryteria są dostarczane w parach (zakres, kryteria) i wymagana jest tylko pierwsza para. Dla każdego dodatkowego kryterium podaj inną parę zakres / kryteria. Kryteria podane w funkcji AVERAGEIFS są stosowane z logiką AND - wszystkie kryteria muszą być prawdziwe, aby liczba została uwzględniona w średniej.

Kryteria mogą być dostarczane jako liczby, ciągi lub odwołania. Na przykład prawidłowe kryteria to 10, „> 10”, A1 lub „<” i A1. Kryteria mogą również zawierać symbole wieloznaczne do częściowego dopasowania.

AVERAGEIFS znajduje się w grupie ośmiu funkcji w programie Excel, które dzielą kryteria logiczne na dwie części (zakres + kryteria). W rezultacie składnia używana do konstruowania kryteriów jest inna, a ŚREDNIA.JEŻELI wymaga zakresu komórek dla argumentów zakresu - nie można użyć tablicy.

AVERAGEIFS automatycznie ignoruje puste komórki, nawet jeśli kryteria są zgodne. Innymi słowy, AVERAGEIFS nie będzie uwzględniać pustych komórek w średniej. AVERAGEIFS zwraca # DIV / 0! jeśli żadna z komórek nie spełnia kryteriów.

Przykłady

W pokazanym przykładzie formuły w H5: H7 to:

=AVERAGEIFS(C5:C15,C5:C15,">0") =AVERAGEIFS(C5:C15,C5:C15,">0",C5:C15,"=2",E5:E15,">1")

Te formuły zwracają średnią cenę nieruchomości, gdzie:

  1. ceny są większe od zera
  2. ceny są większe od zera i mniejsze niż 500 000 USD
  3. nieruchomości mają co najmniej 2 sypialnie i więcej niż 1 łazienkę

Podwójne cudzysłowy („”) w kryteriach

Na ogół wartości tekstowe w kryteriach są ujęte w podwójne cudzysłowy („”), a liczby nie. Jeśli jednak operator logiczny jest dołączony do liczby, liczba i operator muszą być ujęte w cudzysłowy. Zwróć uwagę na różnicę w dwóch poniższych przykładach. Ponieważ druga formuła używa operatora większe lub równe (> =), operator i liczba są ujęte w podwójne cudzysłowy.

=AVERAGEIFS(C5:C15,D5:D15,2) // 2 bedrooms =AVERAGEIFS(C5:C15,D5:D15,">=2") // 2+ bedrooms

Podwójne cudzysłowy są również używane dla wartości tekstowych. Na przykład, aby uśrednić wartości w B1: B10, gdy wartości w A1: A10 są równe „czerwony”, możesz użyć następującego wzoru:

=AVERAGEIFS(B1:B10,A1:A10,"red")

Wiele kryteriów

Wprowadź kryteria parami (zakres, kryteria). Na przykład, aby uśrednić wartości w A1: A10, gdzie B1: B10 = „A”, a C1: C10> 5, użyj:

=AVERAGEIFS(A1:A10,B1:B10,"A",C1:C10,">5")

Wartość z innej komórki

Wartość z innej komórki można uwzględnić w kryteriach przy użyciu konkatenacji. W poniższym przykładzie funkcja ŚREDNIA.JEŻELI zwróci średnią liczb w komórkach A1: A10, które są mniejsze niż wartość w komórce B1. Zwróć uwagę, że operator mniejszy niż (którym jest tekst) jest ujęty w cudzysłów.

=AVERAGEIFS(A1:A10,A1:A10,"<"&B1) // average values less than B1

Symbole wieloznaczne

W kryteriach można używać symboli wieloznacznych znaku zapytania (?), Gwiazdki (*) lub tyldy (~). Znak zapytania (?) Oznacza dowolny znak, a gwiazdka (*) oznacza zero lub więcej znaków dowolnego rodzaju. Na przykład, aby uśrednić wartości w B1: B10, gdy wartości w A1: A10 zawierają tekst „czerwony”, możesz użyć następującego wzoru:

=AVERAGEIFS(B1:B10,A1:A10,"*red*")

Tylda (~) to znak ucieczki, który pozwala znaleźć literalne symbole wieloznaczne. Na przykład, aby dopasować dosłowny znak zapytania (?), Gwiazdkę (*) lub tyldę (~), dodaj tyldę przed symbolem wieloznacznym (tj. ~ ?, ~ *, ~~).

Uwaga: kolejność argumentów jest różna między AVERAGEIFS i AVERAGEIF. Zakres do średniej jest zawsze pierwszym argumentem funkcji ŚREDNIA.JEŻELI.

Uwagi

  • Jeśli żadne dane nie spełniają kryteriów, funkcja AVERAGEIFS zwraca # DIV0! błąd
  • Każdy dodatkowy zakres musi mieć taką samą liczbę wierszy i kolumn, jak średnia_zakres .
  • Kryteria nienumeryczne muszą być ujęte w podwójne cudzysłowy, ale kryteria numeryczne nie. Na przykład: 100, „100”, „> 32”, „jim” lub A1 (gdzie A1 zawiera liczbę).
  • Znaki wieloznaczne? i * mogą być używane w kryteriach. Znak zapytania oznacza dowolny znak, a gwiazdka zero lub więcej znaków dowolnego rodzaju.
  • Aby znaleźć dosłowny znak zapytania lub gwiazdkę, użyj tyldy (~) przed znakiem zapytania lub gwiazdką (tj. ~ ?, ~ *).

Interesujące artykuły...