© Klaus-Martin Buss   www.kmbuss.de
Diese Seite ist Teil eines Framesets. Sollte links kein Navigationsmenü angezeigt werden, bitte hier klicken ...

Formelsammlung

Meine für Schulungszwecke erstellte Mappe mit Erklärungen zu den gebräuchlichsten Funktionen findet Ihr hier

0001 liefert die Summe der fünf höchsten Werte in einem Bereich
0002 liefert die Dezimalzeit einer in A1 im Format "hh,mm" stehenden Echtzeit
0003 addiert in einem Bereich die Einzelwerte in Spalte B in Abhängigkeit der Werte in Spalte A
0004 zählt die Anzahl bestimmter Zeichen in einer Zelle
0005 addiert zwei als Text formatierte Zellen mit vorangestelltem Text
0006 vergleicht zwei Bereiche / Tabellen auf doppelt vorkommende Werte
0007 ermittelt, wieviele unterschiedliche numerische Werte ein Bereich enthält
0008 ermittelt, wieviele unterschiedliche Daten ein Bereich enthält
0009 berechnet die Kalenderwoche für ein vorgegebenes Datum
0010 Nach- und Vornamen tauschen
0011 trennt duch Kommata getrennte Nach- und Vornamen
0012 gibt an, wie oft ein Teilstring in einem Bereich vorkommt
0013 ermittelt, ob das in A1 angegebene Jahr ein Schaltjahr ist
0014 Stunden im Format hh,mm addieren
0015 zählt, wieviele Termine pro Jahr in einem Bereich vorkommen
0016 zählt die Tage zwischen dem jüngsten Eintrag in Spalte A und dem heutigen Datum
0017 liefert das Ergebnis mehrerer Suchkriterien
0018 ermittelt, ob in einem Bereich Duplikate (gleiche Werte) vorhanden sind
0019 =MIN() ohne Nullwerte
0020 Summe der fünf höchsten Werte
0021 Datum, dem der höchste Wert zugeordnet ist
0022 Duplikate markieren und deren Häufigkeit anzeigen
0023 Kumulierte Jahressummen variabel ermitteln
0024 Flächenmasse berechnen und richtig darstellen
0025 MinWert ohne Null und Zelladresse
0026 Vornamen sortieren
0027 Summe der sechs höchsten Werte einer Matrix

Stand: 02.02.10           (wird fortgesetzt ...)
© 2003 Crocodil Entertainment Klaus-Martin Buss


liefert die Summe der fünf höchsten Werte in einem Bereich     T O P 
  A B C
1 4 50 Formel in B1: =SUMMEWENN(A1:A8;">"&KGRÖSSTE(A1:A8;6))
2 6    
3 7   liefert die Summe der 5 höchsten Werte in einem
4 13   Bereich (hier: 13+13+11+7+6=50)
5 13    
6 11    
7 2    
8 1    

liefert die Dezimalzeit einer in A1 im Format "hh,mm" stehenden Echtzeit     T O P 
  A B C
1 Echtzeit im
Format hh,mm
Dezimalzeit im
Format hh,mm
Formeln in Spalte B
2 1,10 1,17 =GANZZAHL(A2)+RUNDEN((A2-GANZZAHL(A2))/60*100;2)
3 1,30 1,50 =GANZZAHL(A3)+RUNDEN((A3-GANZZAHL(A3))/60*100;2)
4 1,59 1,98 =GANZZAHL(A4)+RUNDEN((A4-GANZZAHL(A4))/60*100;2)

addiert in einem Bereich die Einzelwerte in Spalte B in Abhängigkeit der Werte in Spalte A     T O P 
  A B C D
1 Name Zeit Summe Formeln in Spalte C
2 Meier 3,00 6,00 =SUMMEWENN(A:A;A2;B:B)
3 Müller 40,00 80,00 =SUMMEWENN(A:A;A3;B:B)
4 Schulz 900,00 1.100,00 =SUMMEWENN(A:A;A4;B:B)
5 Meier 3,00 6,00 =SUMMEWENN(A:A;A5;B:B)
6 Weber 17,34 17,34 =SUMMEWENN(A:A;A6;B:B)
7 Schulz 200,00 1.100,00 =SUMMEWENN(A:A;A7;B:B)
8 Müller 40,00 80,00 =SUMMEWENN(A:A;A8;B:B)

zählt die Anzahl bestimmter Zeichen in einer Zelle     T O P 
  A B C D
1 Suchbereich Suchbegriff Ergebnis Formeln in Spalte C
2 Max Meier, Luxemburg x 2 mal =LÄNGE(A2)-LÄNGE(WECHSELN(A2;B2;""))
3 Klaus-Martin Buss s 3 mal =LÄNGE(A3)-LÄNGE(WECHSELN(A3;B3;""))
4 excel-lex.de.vu v 1 mal =LÄNGE(A4)-LÄNGE(WECHSELN(A4;B4;""))
5 12.334,00 3 2 mal =LÄNGE(A5)-LÄNGE(WECHSELN(A5;B5;""))
6 12-34-56-78 - 3 mal =LÄNGE(A6)-LÄNGE(WECHSELN(A6;B6;""))
7 C:\Eigene Dateien\test.xls \ 2 mal =LÄNGE(A7)-LÄNGE(WECHSELN(A7;B7;""))
8 12.3.44.5.6.7.8.9 . 7 mal =LÄNGE(A8)-LÄNGE(WECHSELN(A8;B8;""))

addiert zwei als Text formatierte Zellen mit vorangestelltem Text     T O P 
  A B
1 km: 123 Formel in A3: =ERSETZEN(A1;1;3;" ")+ERSETZEN(A2;1;3;" ")
2 km: 3492 A1: Textformat A2: Textformat A3: Zahlenformat #.##0
3 3.615  

vergleicht zwei Bereiche / Tabellen auf doppelt vorkommende Werte     T O P 
  A B C
1 Bereich 1 Auswertung Formeln in Spalte B
2 Meier nicht vorhanden =WENN(ZÄHLENWENN($A$11:$A$17;A2);"vorhanden";"nicht vorhanden")
3 Müller vorhanden =WENN(ZÄHLENWENN($A$11:$A$17;A3);"vorhanden";"nicht vorhanden")
4 Schulz nicht vorhanden =WENN(ZÄHLENWENN($A$11:$A$17;A4);"vorhanden";"nicht vorhanden")
5 Weber vorhanden =WENN(ZÄHLENWENN($A$11:$A$17;A5);"vorhanden";"nicht vorhanden")
6 Hartmann vorhanden =WENN(ZÄHLENWENN($A$11:$A$17;A6);"vorhanden";"nicht vorhanden")
7 Klein nicht vorhanden =WENN(ZÄHLENWENN($A$11:$A$17;A7);"vorhanden";"nicht vorhanden")
8 König vorhanden =WENN(ZÄHLENWENN($A$11:$A$17;A8);"vorhanden";"nicht vorhanden")
9      
10 Bereich 2    
11 Braun    
12 Heinze    
13 Hartmann    
14 König    
15 Schmitt    
16 Müller    
17 Weber    

ermittelt, wieviele unterschiedliche numerische Werte ein Bereich enthält     T O P 
  A B
1 2  
2 6  
3 4 ermittelt, wieviel unterschiedliche numerische Werte ein Bereich enthält
4 2 (in diesem Fall die unterschiedlichen Werte 1, 2, 4 und 6)
5 6  
6 1  
7 4 Formel in A7: =SUMME((HÄUFIGKEIT(A1:A6;A1:A6)>0)*1)

ermittelt, wieviele unterschiedliche Daten ein Bereich enthält    T O P 
 

A

B

C

1 Hans

7

Formel in B1: {=SUMME(WENN(A1:A11<>"";1/ZÄHLENWENN(A1:A11;A1:A11)))}
2 Peter  

(Array-Formel, abschliessen mit STRG & SHIFT & ENTER)

3 Klaus    
4 3   (in diesem Fall die unterschiedlichen Daten Hans, Peter, Klaus, 3, Boris, 4 und 12)
5 Boris    
6 4    
7 Peter (doppelt)  
8 4 (doppelt)  
9 3 (doppelt)  
10 12    
11 Hans (doppelt)  

berechnet die Kalenderwoche für ein vorgegebenes Datum     T O P 
  A B C
1 15.08.03 33 Formel in B1: =KÜRZEN((A1-WOCHENTAG(A1;2)-DATUM(JAHR(A1+4-WOCHENTAG(A1;2));1;-10))/7)

Nach- und Vornamen tauschen     T O P 
  A B C
1 Meier Josef Josef Meier Formel in B1: =TEIL(A1;FINDEN(" ";A1)+1;LÄNGE(A1))&" "&LINKS(A1;FINDEN(" ";A1)-1)
2 Schneider Hans Hans Schneider Formel in B2: =RECHTS(A2;LÄNGE(A2)-FINDEN(" ";A2))&" "&LINKS(A2;FINDEN(" ";A2))

trennt duch Kommata getrennte Nach- und Vornamen     T O P 
  A B C D E
1 Meier, Josef Meier Josef Formel in B1: =LINKS(A1;FINDEN(",";A1)-1) Formel in C1: =WECHSELN(A1;B1&",";"")
2 Schneider, Hans Schneider Hans Formel in B2: =LINKS(A2;FINDEN(",";A2)-1) Formel in C2: =WECHSELN(A2;B2&",";"")

gibt an, wie oft ein Teilstring in einem Bereich vorkommt     T O P 
  A B C D
1 Hans Ha 2 Formel in C1: {=SUMME(IDENTISCH(LINKS(A1:A5;LÄNGE(B1));B1)*1)}
2 Paul     (Array-Formel, abschliessen mit STRG & SHIFT & ENTER)
3 Hannes     ermittelt, wie oft ein Teilstring in einem
4 Klaus     Bereich vorkommt (hier: String in B2
5 Peter     "Ha" in "Hans" und "Hannes"

ermittelt, ob das in A1 angegebene Jahr ein Schaltjahr ist     T O P 
  A B C
1 2003 Kein Schaltjahr Formel in B1: =WENN(REST(JAHR(A1);400)=0;"Schaltjahr";WENN(UND(REST(JAHR(A1);4)=0;REST(JAHR(A1);100)<>0);"Schaltjahr";"kein Schaltjahr"))
2 2004 Schaltjahr Formel in B2: =WENN(REST(JAHR(A2);400)=0;"Schaltjahr";WENN(UND(REST(JAHR(A2);4)=0;REST(JAHR(A2);100)<>0);"Schaltjahr";"kein Schaltjahr"))

Stunden im Format hh,mm addieren    T O P 
  C D E
3 Stunden und Minuten im Format hh,mm Ergebnis Formel in D4 (ohne geschweifte Klammern eingeben
und mit Strg - Shift - Enter abschliessen
4 7,13 11,12 {=GANZZAHL(SUMME(GANZZAHL(C4:C8))
+SUMME(REST(C4:C8;1))/0,6)
+REST(SUMME(REST((C4:C8);1))/0,6;1)*0,6}
5      
6 1,01    
7 2,58    
8      

zählt, wieviele Termine pro Jahr in einem Bereich vorkommen    T O P 

  A B C D
1 01.02.2002 04.04.2003 5 Formel in C1: =SUMMENPRODUKT((JAHR(A1:B8)=2003)*1)
2 01.05.2002 30.09.2002    
3 01.01.2003 02.03.2004   zählt, wieviele Termine / Jahr in einem Bereich vorkommen
4 01.01.2002 23.05.2003    
5 01.07.2003 04.03.2002   (hier: Termine, die in das Jahr 2003 fallen)
6 23.04.2001 02.01.2002    
7 03.03.2003 07.07.1999    
8 04.04.2004 01.09.1961    

zählt die Tage zwischen dem jüngsten Eintrag in Spalte A und dem heutigen Datum    T O P 

  A B C
1 01.01.2002 6 Formel in C1: =HEUTE()-MAX(A:A)
2 01.02.2002    
3 02.10.2003   zählt die Tage zwischen dem jüngsten Eintrag in Spalte A
4 07.07.2003   und dem heutigen Datum
5 03.03.2003    

liefert das Ergebnis mehrerer Suchkriterien    T O P 
(in Abhängigkeit der in C11:E11 eingegebenen Werte soll in F11 die Schlüsselnummer aus F5:F8 ausgegeben werden)

  C D E F
3 Datentabelle      
4 Strasse Hausnr. Zusatz Schlüsselnummer
5 Antonstr. 1   11111
6 Bertastr. 2 A 22222
7 Antonstr. 1 A 99999
8 Cäsarstr. 56 E 55555
9 Suchtabelle      
10 Strasse Hausnr. Zusatz Schlüsselnummer
11 Antonstr. 1 A 99999
12 Lösung:      
13 Matrixformel in F11:      
14 {=INDEX(F5:F8;VERGLEICH(C11&D11&E11;C5:C8&D5:D8&E5:E8;0))}      
15 P.S.: Matrixformel ohne {} eingeben und mit      
16 Strg+Shift+Enter abschliessen      

ermittelt, ob in einer Zeile (oder Spalte) Duplikate vorhanden sind    T O P 

  A B C D E F
1 2 3 3 5 1 ja
2 1 2 3 4 5 nein
3 1 2 1 2 1 ja
4 2 3 4 5 6 nein
5 1 2 3 2 1 ja
6 1     2 4 nein
Formeln der Tabelle
F1 : =WENN(ISTFEHLER(MODALWERT(A1:E1));"nein";"ja")
F2 : =WENN(ISTFEHLER(MODALWERT(A2:E2));"nein";"ja")
F3 : =WENN(ISTFEHLER(MODALWERT(A3:E3));"nein";"ja")
F4 : =WENN(ISTFEHLER(MODALWERT(A4:E4));"nein";"ja")
F5 : =WENN(ISTFEHLER(MODALWERT(A5:E5));"nein";"ja")
F6 : =WENN(ISTFEHLER(MODALWERT(A6:E6));"nein";"ja")

Minimalwert ohne Berücksichtigung von Nullwerten    T O P 

  A B C D E F
1   28,50 33,60 44,50    
2   44,00 22,60 55,00   Kleinster Wert
3 Summe 72,50 56,20 99,50 0,00 56,20
Formeln der Tabelle
B3 : =SUMME(B1:B2)
C3 : =SUMME(C1:C2)
D3 : =SUMME(D1:D2)
E3 : =SUMME(E1:E2)
F3 : {=MIN(WENN(B3:E3>0;B3:E3))}

Summe der fünf höchsten Werte (von Rainer "Berai" Bergmann)    T O P 

  A B
1 79 12
2   4
3   7
4   13
5   33
6   14
7   1
8   2
9   3
10   4
Formeln der Tabelle
A1 : =SUMME(KGRÖSSTE(B:B;{1.2.3.4.5}))

Datum, dem der höchste Wert zugeordnet ist      T O P 

  A B C
1 14.01.2004 5.800,00 Datum "Höchster Wert"
2 17.01.2004 5.850,00 26.01.04
3 18.01.2004 5.700,00  
4 19.01.2004 5.700,00  
5 20.01.2004 5.700,00  
6 21.01.2004 5.700,00  
7 24.01.2004 6.000,00  
8 25.01.2004 6.200,00  
9 26.01.2004 6.450,00  
10 27.01.2004 6.300,00  
Formeln der Tabelle
C2 : =INDEX(A:A;VERGLEICH(MAX(B:B);B:B;0))

Duplikate markieren und deren Häufigkeit anzeigen      T O P 

  A B
1 2  
2 2 ''2'' ist bereits 1 mal vorhanden
3 4  
4 4 ''4'' ist bereits 1 mal vorhanden
5 5  
6 6  
7 2 ''2'' ist bereits 2 mal vorhanden
8 2 ''2'' ist bereits 3 mal vorhanden
9 6 ''6'' ist bereits 1 mal vorhanden
10 Hans  
11 Peter  
12 Klaus  
13 Hans ''Hans'' ist bereits 1 mal vorhanden
14 Hans ''Hans'' ist bereits 2 mal vorhanden
Formeln der Tabelle
B1 : =WENN(ZÄHLENWENN($A$1:A1;A1)>1;"''"&A1&"'' ist bereits "&ZÄHLENWENN($A$1:A1;A1)-1&" mal vorhanden";"")
B2 : =WENN(ZÄHLENWENN($A$1:A2;A2)>1;"''"&A2&"'' ist bereits "&ZÄHLENWENN($A$1:A2;A2)-1&" mal vorhanden";"")
B3 : =WENN(ZÄHLENWENN($A$1:A3;A3)>1;"''"&A3&"'' ist bereits "&ZÄHLENWENN($A$1:A3;A3)-1&" mal vorhanden";"")
B4 : =WENN(ZÄHLENWENN($A$1:A4;A4)>1;"''"&A4&"'' ist bereits "&ZÄHLENWENN($A$1:A4;A4)-1&" mal vorhanden";"")
B5 : =WENN(ZÄHLENWENN($A$1:A5;A5)>1;"''"&A5&"'' ist bereits "&ZÄHLENWENN($A$1:A5;A5)-1&" mal vorhanden";"")
B6 : =WENN(ZÄHLENWENN($A$1:A6;A6)>1;"''"&A6&"'' ist bereits "&ZÄHLENWENN($A$1:A6;A6)-1&" mal vorhanden";"")
B7 : =WENN(ZÄHLENWENN($A$1:A7;A7)>1;"''"&A7&"'' ist bereits "&ZÄHLENWENN($A$1:A7;A7)-1&" mal vorhanden";"")
B8 : =WENN(ZÄHLENWENN($A$1:A8;A8)>1;"''"&A8&"'' ist bereits "&ZÄHLENWENN($A$1:A8;A8)-1&" mal vorhanden";"")
B9 : =WENN(ZÄHLENWENN($A$1:A9;A9)>1;"''"&A9&"'' ist bereits "&ZÄHLENWENN($A$1:A9;A9)-1&" mal vorhanden";"")
B10 : =WENN(ZÄHLENWENN($A$1:A10;A10)>1;"''"&A10&"'' ist bereits "&ZÄHLENWENN($A$1:A10;A10)-1&" mal vorhanden";"")
B11 : =WENN(ZÄHLENWENN($A$1:A11;A11)>1;"''"&A11&"'' ist bereits "&ZÄHLENWENN($A$1:A11;A11)-1&" mal vorhanden";"")
B12 : =WENN(ZÄHLENWENN($A$1:A12;A12)>1;"''"&A12&"'' ist bereits "&ZÄHLENWENN($A$1:A12;A12)-1&" mal vorhanden";"")
B13 : =WENN(ZÄHLENWENN($A$1:A13;A13)>1;"''"&A13&"'' ist bereits "&ZÄHLENWENN($A$1:A13;A13)-1&" mal vorhanden";"")
B14 : =WENN(ZÄHLENWENN($A$1:A14;A14)>1;"''"&A14&"'' ist bereits "&ZÄHLENWENN($A$1:A14;A14)-1&" mal vorhanden";"")

Kumulierte Jahressummen variabel ermitteln      T O P 
  A B C D E F G H I J K L
1 Januar Februar März April Mai Juni Juli August September Oktober November Dezember
2 2 3 5 1 6 4 8 3 4 5 2 3
3                        
4 Summe bis Monat:   Juli 29                
Formeln der Tabelle
D4 : =SUMME(INDIREKT("A2:"&WAHL(INDEX(SPALTE(A:L);VERGLEICH(C4;1:1;0));"A";"B";"C";"D";"E";"F";"G";"H";"I";"J";"K";"L")&"2"))


Flächenmasse berechnen und richtig darstellen      T O P   
  A B C D E
1 33,3 cm x 3 cm = 99,90 cm²
2 5 m x 25 m = 125,00 m²
3 2,5 mm x 4 mm = 10,00 mm²
Formeln der Tabelle
E1 : =TEXT(LINKS(A1;FINDEN(" ";A1)-1)*LINKS(C1;FINDEN(" ";C1)-1);"#.##0,00")&TEIL(A1;FINDEN(" ";A1);3)&"²"
E2 : =TEXT(LINKS(A2;FINDEN(" ";A2)-1)*LINKS(C2;FINDEN(" ";C2)-1);"#.##0,00")&TEIL(A2;FINDEN(" ";A2);3)&"²"
E3 : =TEXT(LINKS(A3;FINDEN(" ";A3)-1)*LINKS(C3;FINDEN(" ";C3)-1);"#.##0,00")&TEIL(A3;FINDEN(" ";A3);3)&"²"


MinWert ohne Null und Zelladresse      T O P 
  A B C D E F G H I
1 3 5 2 0 5 3   Min-Wert ohne Null aus A1:G1 2
2               steht in Spalte ... C
Formeln der Tabelle
I1 : {=MIN(WENN(A1:F1>0;A1:F1))}
I2 : =ZEICHEN(64+VERGLEICH(I1;A1:F1;0))


Vornamen sortieren      T O P   
  A B
1 Name unsortiert Namen sortiert
2 Klaus Anton
3 Hans Bettina
4 Wolfgang Christiane
5 Ute Christine
6 Bettina Fritz
7 Jürgen Hans
8 Uschi Jan
9 Jan Jürgen
10 Peter Karl
11 Karl Klaus
12 Fritz Otto
13 Anton Peter
14 Xaver Uschi
15 Volker Ute
16 Otto Volker
17 Christine Wolfgang
18 Christiane Xaver
Formeln der Tabelle
B2 : {=WENN(ISTFEHLER(INDEX(Vorname;VERGLEICH(KKLEINSTE(ZÄHLENWENN(Vorname;"<"&Vorname);ZEILEN(Namen!A$2:A3));ZÄHLENWENN(Vorname;"<"&Vorname);0)));"";INDEX(Vorname;VERGLEICH(KKLEINSTE(ZÄHLENWENN(Vorname;"<"&Vorname);ZEILEN(Namen!A$2:A3));ZÄHLENWENN(Vorname;"<"&Vorname);0)))}
B3 : {=WENN(ISTFEHLER(INDEX(Vorname;VERGLEICH(KKLEINSTE(ZÄHLENWENN(Vorname;"<"&Vorname);ZEILEN(Namen!A$2:A4));ZÄHLENWENN(Vorname;"<"&Vorname);0)));"";INDEX(Vorname;VERGLEICH(KKLEINSTE(ZÄHLENWENN(Vorname;"<"&Vorname);ZEILEN(Namen!A$2:A4));ZÄHLENWENN(Vorname;"<"&Vorname);0)))}


Summe der sechs höchsten Werte einer Matrix       T O P   
 

  ABCD
1 4  Summe der sechs höchsten Werte:30
22   
33   
44   
54   
63   
72   
81   
95   
106   
117   
124  bleibt unberücksichtigt, da der siebtgrösste Wert ;-) 

Formeln der Tabelle
ZelleFormel
D1=SUMME(KGRÖSSTE($A$1:$A$12;{1;2;3;4;5;6}))