Excelboy: Excel Formeln,Tipps & Tricks

Wie be­rechne ich den Mit­tel­wert für die Zel­len mit be­stimm­ten Inhalten?

Ex­cel For­mel MITTELWERTWENN

Die Ex­cel For­mel MITTELWERTWENN Als Ar­gu­mente für Ex­cel For­mel MITTELWERTWENN de­fi­nie­ren wir den Be­reich, in dem nach pas­sen­den In­hal­ten ge­sucht wird, die Be­din­gun­gen, die die In­halte be­stim­men, und die Zel­len, die Werte für die Be­rech­nung des Mit­tel­wer­tes ent­hal­ten (optional).

Ex­cel Formel:

=MITTELWERTWENN(Bereich, Be­din­gun­gen; ‚Mittelwert_Zellen‘)

Wenn die ‚Mittelwert_Zellen‘ nicht de­fi­niert sind, wird der Mit­tel­wert nach Be­din­gun­gen aus dem Be­reich errechnet.

MITTELWERTWENN Bei­spiel „Prei­salarm im Garten“

In ei­nem Gar­ten­shop in Müns­ter wird den jähr­li­chen Früh­lings­ver­kauf or­ga­ni­siert. Es wer­den Kräu­ter und Ge­würze ver­kauft. Die Lei­te­rin des Shops – Frau Skibbe – ana­ly­siert den Be­stand und die Preise. Un­ter an­de­rem, möchte Frau Skibbe den durch­schnitt­li­chen Preis be­rech­nen und zwar – nur für die Pflan­zen, die vor­han­den sind. Die Ex­cel Da­ten und Er­geb­nisse se­hen so aus:

Ex­cel For­mel (Bsp. Zelle C11):

=MITTELWERTWENN(B2:B10;”>0″;C2:C10)

Wie wir se­hen, sind die Pflan­zen „Lauch“ und „Brun­nen­kresse“ nicht vor­han­den. Frau Skibbe sollte da­her die Preise für diese Pflan­zen aus der Be­rech­nung des durch­schnitt­li­chen Prei­ses aus­schlie­ßen. Mit Hilfe der Ex­cel For­mel MITTELWERTWENN wird den Be­stand (Be­reich B2:B10) ge­prüft, nur die Pflan­zen mit dem Bestand>0 iden­ti­fi­ziert und den durch­schnitt­li­chen Preis (Be­reich C2:C10) für die vor­han­de­nen Pflan­zen berechnet.

Ex­cel Top Tipp

Beim Be­rech­nen vom Mit­tel­wert in Ex­cel wer­den auch die Zel­len, die den Wert gleich 0 ha­ben, in die Be­rech­nung einbezogen.

In un­se­rem Bei­spiel be­trägt der Ge­samt­be­stand 92 Pflan­zen. Der durch­schnitt­li­che Bestand:

=MITTELWERT(B2:B10)=92/9=10,2 („Lauch“ und „Brun­nen­kresse“ be­ein­flus­sen den Mittelwert)

=MITTELWERTWENN(B2:B10;“>0“)=92/7=13,1 (der Mit­tel­wert nur für die Pflan­zen, die vor­han­den sind)

Wie be­stimme ich den höchs­ten und nied­rigs­ten Wert in Excel?

Ex­cel For­meln MAX MIN

Die Ex­cel For­meln MAX MIN ge­ben ent­spre­chend den ma­xi­ma­len den mi­ni­ma­len Wert aus ei­nem Be­reich zurück.

Ex­cel Formeln:

=MAX(Zahl1,Zahl2…Zahl-n)

=MIN(Zahl1,Zahl2…Zahl-n)

Die MAX und MIN Funk­tio­nen kön­nen ent­we­der stan­dard­ge­mäß, oder mit dem Knopf  aus dem „Start“ oder „For­meln“ Be­reich in Ex­cel auf­ge­ru­fen werden.

Au­ßer­dem wird das Ma­xi­mum und Mi­ni­mum von den mar­kier­ten Zel­len in der Sta­tus­leiste rechts un­ten angezeigt:

MAX, MIN Bei­spiel „Fremdwährung“

Der Ana­ly­ti­ker – Herr Breit­mann – be­ob­ach­tet die Ent­wick­lung des Wech­sel­kur­ses ei­ni­ger Fremd­wäh­run­gen im Ver­gleich zu Euro (€). Sein Ziel da­bei ist her­aus­zu­fin­den, den höchs­ten bzw. den nied­rigs­ten Kurs zu identifizieren.

Mit Hilfe von der Ex­cel For­mel (Zel­len B8 und B9):

=MAX(B2:B7)

=MIN(B2:B7)

fin­det Herr Breit­mann her­aus, dass der höchste Wech­sel­kurs – 1,42€ (Let­ti­sche Lats) und der nied­rigste – 0,67€ (Aus­tra­li­scher Dol­lar) betrug.

Wie be­rechne ich den Durch­schnitts­wert in Excel?

Ex­cel For­mel MITTELWERT

Die Ex­cel For­mel MITTELWERT Funk­tion be­rech­net das arith­me­ti­sche Mit­tel der vor­ge­ge­be­nen Zahlen.

Ex­cel For­mel:

=MITTELWERT(Zahl1,Zahl2…Zahl-n)

Die MITTELWERT Funk­tion kann ent­we­der stan­dard­ge­mäß, oder mit dem Knopf  aus dem „Start“ oder „For­meln“ Be­reich in Ex­cel auf­ge­ru­fen werden.

Au­ßer­dem wird der Mit­tel­wert von den mar­kier­ten Zel­len in der Sta­tus­leiste rechts un­ten angezeigt:

MITTELWERT Bei­spiel „Wetterfrosch“

Der Me­teo­ro­loge – Herr Wind – möchte die durch­schnitt­li­che Tem­pe­ra­tur für die letz­ten 7 Tage be­rech­nen. Die täg­li­chen Werte lie­gen vor.

Mit Hilfe von der Ex­cel For­mel (Bsp. Zelle B2):

=MITTELWERT(B2:B8)

be­kommt Herr Wind den Durch­schnitts­wert von 6,4° C pro Tag.

Ex­cel Top Tipp:

Die Ex­cel For­mel MITTELWERTWENN gibt dem arith­me­ti­schen Mit­tel­wert nur von den Zel­len mit den be­stimm­ten In­hal­ten zurück.

Wie er­setze ich ein feh­ler­haf­tes Er­geb­nis ei­ner Ex­cel Formel?

Ex­cel For­mel WENNFEHLER

Ex­cel For­meln ge­ben ei­nen Feh­ler zu­rück, wenn nicht alle Be­din­gun­gen er­füllt sind. Ent­hält min­des­tens eine Zelle aus dem Be­reich für wei­tere Be­rech­nun­gen ei­nen Feh­ler, so wird das Ge­samt­er­geb­nis auch feh­ler­haft. Die Ex­cel For­mel WENNFEHLER er­setzt den Feh­ler durch ei­nen vor­ge­ge­be­nen Wert.

Ex­cel For­mel:

=WENNFEHLER(Ergebnis; Ergebnis_falls_Fehler)

WENNFEHLER Bei­spiel „VW Niederlassung“

Der VW Händ­ler – Herr Schebp – muss den Ge­samt­um­satz sei­ner Nie­der­las­sung be­rech­nen und an die Zen­trale wei­ter­lei­ten. Die Ver­kaufs­zah­len und –preise sind vorhanden.

Die Be­rech­nung des Um­sat­zes nach „Anzahl*Preis“ (Spalte D) lie­fert ei­nen „#WERT!“ Feh­ler für das Mo­del „Ti­guan“ zu­rück, weil die For­mel das Wort „aus­ver­kauft“ nicht in­ter­pre­tie­ren kann.

Mit Hilfe von der Ex­cel For­mel (Bsp. Zelle F2):

=WENNFEHLER(B2*C2;0)

wird je­der Feh­ler mit ei­ner Null er­setzt. Das er­mög­licht auch eine kor­rekte Be­rech­nung des Ge­samt­um­sat­zes (Zelle F8).

Ex­cel Top Tipp:

Das (“”) Zei­chen als ‚Ergebnis_falls_Fehler‘ in der Ex­cel For­mel WENNFEHLER lie­fert eine leere Zelle zu­rück. Für das obige Beispiel:

=WENNFEHLER(B2*C2; “”)

Wie be­stimme ich eine Be­din­gung in Excel?

Ex­cel For­mel WENN

Die Ex­cel For­mel WENN hat eine be­son­dere Be­deu­tung, weil Be­din­gun­gen bei vie­len kom­ple­xe­ren Aus­wer­tun­gen ge­fragt sind. Ex­cel WENN lie­fert ei­nen be­stimm­ten Wert, wenn die an­ge­ge­bene Be­din­gung er­füllt ist, und sonst — ei­nen an­de­ren Wert.

Ex­cel For­mel:

=WENN(Bedingung; Dann_Ergebnis; Sonst_Ergebnis)

WENN Bei­spiel „Prüfung“

Die As­sis­ten­tin – Frau Pal­mer – möchte eine Über­sicht der Prü­fungs­er­geb­nisse vor­be­rei­ten. Sollte eine Note klei­ner 4,0 sein, be­kom­men die Teil­neh­mer das Merk­mal „be­stan­den“, sonst – „nicht bestanden“.

Ex­cel For­mel (Bsp. Zelle D2):

=WENN(B2<4;“bestanden”;“nicht be­stan­den”)

Wir könn­ten die Be­din­gung (Bsp. für C2) auch mit so ei­ner Ex­cel For­mel bestimmten:

=WENN(B2>=4;“nicht bestanden“;“bestanden“)

Ex­cel Top Tipp:

Dann_Ergebnis und Sonst_Ergebnis der WENN Funk­tion kön­nen auch als For­mel be­stimmt wer­den. Ein Bei­spiel dazu:

WENN Bei­spiel „Um­satz International“

Die N. GmbH ver­kauft ihre Pro­dukte in Eu­ropa und in den USA. Die Um­sätze wer­den ent­spre­chend in Euro(€) und US-Dollar($) ge­spei­chert. Für den Mo­nats­ab­schluss soll­ten alle Um­sätze in € um­ge­rech­net werden.

Der Um­satz in US-Dollar (Zelle A3) wird mit dem Wech­sel­kurs (1€ = 1,36$) umgerechnet.

Wie finde ich ei­nen Wert für ein Kri­te­rium aus ei­ner Ex­cel Ta­belle in ei­ner an­de­ren Tabelle?

Ex­cel For­mel SVERWEIS

Die Ex­cel For­mel SVERWEIS ist ge­nau DIE Ex­cel For­mel, die das Le­ben und Ar­bei­ten mit Ex­cel enorm er­leich­tert. Der Grund da­für ist, dass man Da­ten oft aus meh­re­ren un­ter­schied­li­chen Quel­len be­kommt, und die dann zu­sam­men­füh­ren muss.

Ex­cel For­mel:

=SVERWEIS(Kriterium; Be­reich; Spalte; Verweis)

Die Funk­tion SVERWEIS sucht das Kri­te­rium in dem Be­reich (in der 1.Spalte) und gibt den da­zu­ge­hö­ri­gen Wert aus der Spalte (In­dex) des Be­rei­ches zu­rück. Der Pa­ra­me­ter Ver­weis be­stimmt, ob eine un­ge­fähre (WAHR bzw. 1) oder eine ge­naue Über­ein­stim­mung (FALSE bzw. 0) ge­sucht wird.

SVERWEIS Bei­spiel „DVD Kollektion“

Herr Huhn möchte ei­nige DVDs aus sei­ner Kol­lek­tion (Ta­belle 1) ver­kau­fen. Er holt sich ein An­ge­bot von ei­nem Händ­ler (Ta­belle 2; nach Preis ab­stei­gend sor­tiert) ein. Mit Hilfe von der SVERWEIS Funk­tion ord­net Herr Huhn die Ver­kaufs­preise zu, um über die Zweck­mä­ßig­keit des Ver­kaufs ent­schei­den zu können.

Ex­cel For­mel (Bsp. Zelle E3):

=SVERWEIS(A3;$I$3:$J$12;2;0)

We­gen der Sor­tie­rung war eine di­rekte Über­tra­gung der Preise in die Ex­cel Tabelle_1 un­mög­lich. Die Ex­cel SVERWEIS For­mel hat die Zu­ord­nung per­fekt gemeistert;)

SVERWEIS im Bei­spiel wurde so kon­stru­iert, dass es mög­lich war, die Ex­cel For­mel in die erste Zelle (F3) zu schrei­ben und dann die For­mel für an­dere Zel­len „run­ter­zu­zie­hen“. Da­bei musste man den Be­reich ent­we­der „fi­xie­ren“ (F4) oder „be­nen­nen“ (sh. un­ten) – sonst ver­schie­ben sich die Ver­weise zu­sam­men mit der Formel.

Ex­cel Top Tipps:

1)      um eine fal­sche Zu­ord­nung für die SVERWEIS zu ver­mei­den emp­fehle ich den Be­reich, in dem ge­sucht wird, sinn­voll zu be­nen­nen. Für das Bei­spiel wird der Be­reich I3:J12 mar­kiert und als „preis“ benannt:

Jetzt kön­nen wir die Ex­cel For­mel SVERWEIS so umformulieren:

=SVERWEIS(A3; preis;2;0)

Da­mit wird die For­mel auch übersichtlicher.

2)      Wenn die Ex­cel For­mel SVERWEIS ein Kri­te­rium im Be­reich nicht fin­det, lie­fert die den Wert „#NV“ zu­rück. In die­sem Fall wird z.B. das Auf­sum­mie­ren der Er­geb­nisse (sh. Funk­tion SUMME) nicht mög­lich. Als eine Lö­sung kommt die Ex­cel Funk­tion WENNFEHLER in Frage.

Wie zähle ich die Zel­len mit In­hal­ten in Excel?

Ex­cel For­mel ANZAHL

Mit Hilfe von der Ex­cel For­mel ANZAHL wer­den Zel­len mit Zah­len oder vor­ge­ge­bene Werte zusammengezählt.

Ex­cel For­mel:

=ANZAHL(Zellen,[Werte])

Die ANZAHL For­mel kann ent­we­der stan­dard­ge­mäß, oder mit der  Funk­tion aus dem „Start“ oder im „For­meln“ Be­reich in Ex­cel auf­ge­ru­fen werden.

Um alle nicht leere Zel­len zu zäh­len, wird die Funk­tion ANZAHL2 verwendet.

Ex­cel For­mel:

=ANZAHL2(Zellen,[Werte])

ANZAHL Bei­spiel „Lager“

Der Fa­ci­lity Ma­na­ger – Herr So­ko­low­ski – muss über­prü­fen, wel­che Ar­ti­kel auf La­ger noch vor­han­den sind. In der Ta­belle wird ent­we­der die Menge für die vor­han­de­nen Ar­ti­kel oder „#NV“ angezeigt.

Ex­cel For­meln (Bsp. Zel­len B9 und B10):

=ANZAHL(B2:B8)

=ANZAHL2(B2:B8)

Es sind 5 Ar­ti­kel (ANZAHL) von 7 (ANZAHL2) vor­han­den.

Die Er­geb­nisse für die mar­kier­ten Zel­len wer­den au­ßer­dem in der Sta­tus­leiste rechts un­ten angezeigt:

Ex­cel Top Tipp:

fürs Zäh­len von den Zel­len, de­ren In­halte be­stimm­ten Kri­te­rien ent­spre­chen, wird die Funk­tion ZÄHLENWENN verwendet.

Wie be­komme ich eine fort­lau­fende Zahl für den Tag (Mo­nat, Jahr) aus dem be­stimm­ten Da­tum in Excel?

Ex­cel For­meln TAG,MONAT,JAHR

Die Ex­cel For­meln TAG, MONAT, JAHR ge­ben den Tag, Mo­nat oder das Jahr ei­nes Da­tums als eine fort­lau­fende Zahl zurück.

Ex­cel For­meln:

=TAG(Da­tum)

=MONAT(Da­tum)

=JAHR(Da­tum)

TAG,MONAT,JAHR Bei­spiel „durch­schnitt­li­cher Umsatz“

Die Tank­stel­len­be­sit­ze­rin – Frau Schmedt – will den durch­schnitt­li­chen Um­satz Ih­rer Tank­stelle pro Tag be­rech­nen. Am 28.11.2013 be­trug der Ge­samt­satz im No­vem­ber 1.200€- Mit Hilfe der TAG For­mel wird die An­zahl von ver­gan­ge­nen Ta­gen ermittelt.

Die Tank­stelle brachte im No­vem­ber durch­schnitt­lich 43€ pro Tag.

Die Er­geb­nisse von den Ex­cel For­meln =MONAT(B2) und =JAHR(B2):

Ex­cel Top Tipp:

Ex­cel For­meln TAG, MONAT, JAHR sind u.a. für eine Pro­gno­se­rech­nung sehr hilf­reich. Um die Be­rech­nung für das obige Bei­spiel dy­na­mi­scher ge­stal­ten zu kön­nen, wird die Ver­wen­dung von der Ex­cel For­mel HEUTE zu­sam­men mit der TAG Funk­tion (=TAG(HEUTE())) emp­foh­len.

Wie kann ich die An­zahl von Zel­len mit be­stimm­ten In­hal­ten in Ex­cel herausfinden?

Ex­cel For­mel ZÄHLENWENN

Die Ex­cel For­mel ZÄHLENWENN zählt nur die Zel­len mit In­hal­ten nach vor­ge­ge­be­nen Kri­te­rien aus ei­nem Bereich.

Ex­cel For­mel:

=ZÄHLENWENN(Bereich, Kri­te­rium)

ZÄHLENWENN Bei­spiel „Kühe & Schweine“

Der Bauer – Herr Rich­ter – hat Kühe und Schweine auf sei­nem Bau­ern­hof. Er will her­aus­fin­den, wie viele Kühe und wie viele Schweine un­ter­wegs und au­ßer­dem – wie viele Tiere drei Jahre oder äl­ter sind.

Ex­cel For­mel (Bsp. Zelle B12):

=ZÄHLENWENN(B2:B11;“=Kuh“)

Herr Rich­ter hat 4 Kühe und 6 Schweine, da­von sind 4 Tiere drei Jahr alt oder älter.

Ex­cel Top Tipp:

die Funk­tion ANZAHL zählt alle Zel­len mit Zah­len und ANZAHL2 – nicht leere Zellen.

Wie be­stimme ich das Da­tum von Heute in Excel?

Ex­cel For­mel HEUTE

Mit Hilfe von der Ex­cel For­mel HEUTE be­kom­men wir die fort­lau­fende Zahl des heu­ti­gen Da­tums zurück.

Die Funk­tion HEUTE() ist eine der we­ni­gen Ex­cel Funk­tio­nen, die keine Ar­gu­mente hat.

Ex­cel For­mel:

=HEUTE()

Am 04.01.2014 wäre die Zahl 41643. Ex­cel passt das Zel­len­for­mat au­to­ma­tisch an und lie­fert 04.01.2014 zurück.

HEUTE Bei­spiel „Urlaub“

Herr Meier war­tet un­ge­dul­dig auf sei­nen Ur­laub – am 04.08.2014 fliegt er für zwei Wo­chen auf Fu­er­teven­tura. Er ruft je­den Tag eine Excel-Datei mit den fol­gen­den For­meln auf:

Am 04.01.2014 hat Herr Meier noch 212 Tage bis zu sei­nem Ur­laub. Am 05.01.2014 wer­den es nur noch 211 Tage usw.

Ex­cel Top Tipp:

Ex­cel For­mel HEUTE() ist eine „dy­na­mi­sche“ Funk­tion — das Er­geb­nis än­dert sich täg­lich. Man sollte „In­halte ein­fü­gen…“ ver­wen­den, um das heu­tige Da­tum zu fixieren.

Oft ist es sinn­voll, die Ex­cel For­mel HEUTE mit den Funk­tio­nen TAG, MONAT, JAHR zu verwenden.