VBA-Beispiele > Excel: > Formeln

Tipp 175: Balkendiagramm in Zelle

Wie kann man eine %-Zahl als Balkendiagramm in einer Zelle darstellen?


Wenn in B1 die Prozentzahl steht, kann man eingeben: =WIEDERHOLEN("n";B1*100)

Anschließend formatiert man die Zelle mit Wingdings und hat seine "Balkengrafik".

[Einzellink]

Tipp 176: Erweiterung für MAX(), z. B. dritthöchster Wert?

Kann ich mit einer Formel/Funktion (=max) auch den zweit- oder dritthöchsten Wert ziehen, ohne sortieren zu müssen?


Dafür gibt es eine weitere Funktion:

=KGRÖSSTE(B1:B14;2)
ermittelt den zweithöchsten Wert in B1 bis B14,
=KGRÖSSTE(B1:B14;3)
den dritthöchsten.

Dementsprechend funktioniert auch die Funktion KKLEINSTE, sie sucht nach den kleinsten Zahlen.

[Einzellink]

Tipp 207: Anleitung: Rechnung erstellen

Wie kann ich mit Formeln eine Rechnung erstellen?


Du hast eine Tabelle namens "Rechnung" und eine Tabelle namens "Artikel". Auf der Tabelle "Artikel" befinden sich ab A2 die Artikelnummern (hier fortlaufend numeriert), ab B2 die Artikel und ab C2 die Einzelpreise. Auf dem Blatt "Rechnung" wird die Rechnung erstellt.:

  1. In A20 bis A30 sollen die Nummern der gekauften Artikel eingetragen werden.
  2. In B20 bis B30 wird die Anzahl der gekauften Artikel eingetragen.
  3. In C20 bis C30 sollen automatisch die Artikel erscheinen. Dazu kannst Du die Formel verwenden (in einer Zeile):
  4. In D20 bis D30 sollen die dazugehörigen Einzelpreise eingelesen werden. Die Formel dazu (in einer Zeile):
  5. In E20 bis E30 sollen die Preise der gekauften Artikel errechnet werden, die Formel:
  6. In E31 soll Netto ausgerechnet werden:
  7. MwSt. in E32:
  8. Brutto in E33:

[Einzellink]

Tipp 177: Teil eines Datums einlesen

Ich habe in einer Zelle ein Datum stehen. z.B 10.03.2000. Jetzt möchte ich in einer anderen Zelle die ersten zwei Zahlen und den Punkt angezeigt bekommen, also 10.


[Einzellink]

Tipp 178: Text und Datumswert verketten

In Zelle A20 steht folgender Text: "Mietersparnis 01/89 bis" Nun möchte ich diesen Text nach dem Wort bis mit dem variablen Datumswert aus Zelle C2 (z. B. 07/99") ergänzen.


[Einzellink]

Tipp 204: Blattname in Zelle

Wie kann man per Formel den Namen des Blattes in die Zelle einfügen?


[Einzellink]

Tipp 205: Zellbezug in anderer Zelle

Ich möchte in eine Zelle (z.B. A1) eine Formel bzw. Funktion eingeben, die dieser Zelle A1 den Wert aus einer anderen Zelle zuweist. Diese Quellzelle liegt immer in einer Spalte (z.B. C), die Zeilennummer ändert sich aber. Diese Zeilennummer ist in einer Zelle A2 definiert. Wie kann ich also diese Zahl aus A2 quasi als Variable für den Zellbezug nehmen? Bei Änderung des Wertes in A2 soll dann der Zelle A1 automatisch der neue Werte aus der entsprechenden Zeile in Spalte C zugewiesen werden.


[Einzellink]

Tipp 206: Datum und Text in einer Zelle

In einer Zelle steht ein Datum, in einer anderen ein Text. Wie kann ich beides in einer Zelle anzeigen lassen? =A1&C1 funktioniert nicht.


[Einzellink]

Tipp 203: Ostersonntag aus Jahr errechnen

Wie kann man das Datum des Ostersonntags aus einem Jahr errechnen?


Das Jahr steht in A1, die Zelle mit der Formel muß als Datum formatiert sein.

[Einzellink]

Tipp 201: Zellbezug und Kopieren

Beim Kopieren einer Formel wird der Zellbezug verändert, z. B. wird aus =A1 =A2. Wie kann ich das verhindern?


Beim normalen Verwenden eines Bezuges merkt sich Excel nicht den Bezug, sondern den Weg zu der Zelle, auf die sich der Bezug bezieht. Steht in B1 die Formel = A1, heißt das für Excel eine Zelle nach links. Befindet sich die Formel in A2, heißt das eine Zelle nach oben. Wenn die gleiche Formel (=A1) in B2 eingetragen ist, bedeutet dies für Excel eine Zelle nach oben und eine Zelle nach links. Beim Kopieren verändert Excel die Bezüge in Formeln deshalb. Wird die Formel =A1 (eine Zelle nach links) eine Zelle nach unten kopiert, lautet sie nun =A2 - also wiederum eine Zelle nach links. Deshalb nennt man diesen Bezug auch "Relativer Zellbezug". Kurzgefasst: Beim senkrechten Kopieren ändert sich die Zeile im Bezug, beim waagerechten Kopieren die Spalte.

Beispiel: Die Formel =A1 wird nach rechts, unten und rechts unten kopiert.

 ABCBedeutung
Zeile 1 =A1=B1eine Zelle nach links
Zeile 2 =A2=B2eine Zelle nach links

Um festzulegen, dass sich in einem Bezug eine Angabe (Spalte oder Zeile) beim Kopieren nicht ändert, schreibt man vor diese Angabe ein $. Soll sich die Zeile nicht ändern, schreibt man im Beispiel =A$1, soll sich die Spalte nicht ändern: =$A1. Diesen Bezug nennt man "Gemischter Bezug".

Sind im Bezug zwei $-Zeichen enthalten, nennt man dies "Absoluter Bezug".

Tip: Setzt man den Cursor in den Bezug, kann man die $-Zeichen durch mehrmaliges Drücken der Taste F4 einfügen.

[Einzellink]

Tipp 202: Mehwertsteuer und Runden

Wenn ich die Mehrwertsteuer mit =A1*1,16 errechne, ergibt das natürlich Zahlen mit mehr als zwei Nachkommastellen. Trotz Formatierung als Währung wird Netto jedoch mit den genauen Zahlen errechnet, so daß sich oft eine Differenz ergibt.


Entweder mit =RUNDEN(A1*1,16;2) arbeiten oder in Extras - Optionen - Berechnen "Genauigkeit wie angezeigt" einstellen.

[Einzellink]

Tipp 199: Zeitdifferenz in Sekunden

Ich benötige die Differenz in Sekunden, um mit dieser Zahl weitere Berechnungen durchführen zu können. Die Zeitdifferenz habe ich über die Formatierung (Benutzerdefiniert [s]) in Sekunden vorliegen.


Durch die Zellformatierung hat sich an der Zahl selbst nichts geändert. In Excel entspricht der Wert 1,0 einem Tag. Um nun die Sekunden herauszukriegen muss man den Zeitwert mit 24*60*60 multiplizieren.

[Einzellink]

Tipp 200: SVERWEIS auf Spanisch

Wie heißt der SVERWEIS auf Spanisch?


=BUSCARV(B1;A:B;2;FALSO)

[Einzellink]

Tipp 197: Runden auf volle Fünfer

In einer Zelle wird eine ganze Zahl angezeigt. Diese möchte ich auf- bzw. abrunden, aber immer auf 5 oder auf 0. Beispiel: 283 wird auf 285 gerundet, 282 auf 280.


=VRUNDEN(A1;5)

[Einzellink]

Tipp 198: SVERWEIS: Zellen rechts addieren

Per SVERWEIS lasse ich in Spalte A einen Begriff suchen. Wie ist es möglich, alle sich rechts vom gefundenen Begriff enthaltenen Zahlen zu addieren?


In E1 befindet sich der Suchbegriff.

[Einzellink]

Tipp 179: Pfad in Zelle

Wie erhalte ich in einer Zelle den aktuellen Pfad?


=INFO("Verzeichnis")

F9 drücken zum Aktualisieren.

Tips (ähnliche Funktionen):

=INFO("Sysversion")
gibt die Windowsversion zurück,
=INFO("version")
gibt die Excel-Version zurück

[Einzellink]

Tipp 180: Differenz zwischen zwei Datumswerten

Um die Differenz zwischen 2 Datumswerten zu erhalten, nimmt man einfach die Differenz der beiden Werte und formatiert die Zelle als Standard. Was aber wenn die Zeitspanne größer als ein Jahr ist und man getrennt Jahre, Monate und Tage erhalten will?


Hierzu gibt es in Excel eine schöne Funktion, die jedoch komischerweise nirgends in der Online-Hilfe erklärt ist.

Die DATEDIF-Funktion:

Syntax: DATEDIF(Datum1;Datum2;"Parameter")

Datum2 muß größer als Datum1 sein.

Parameter (Muß immer in "" stehen):

  • y = Jahre DATEDIF("01.01.98";"15.02.99";"y") = 1
  • m = Monate DATEDIF("01.01.98";"15.02.99";"m") = 13
  • d = Tage DATEDIF("01.01.98";"15.02.99";"d") = 410
  • ym = Monate minus Jahre DATEDIF("01.01.98";"15.02.99";"ym") = 1 (13 Monate -1 Jahr (12 Monate))
  • yd = Tage minus Jahre DATEDIF("01.01.98";"15.02.99";"yd") = 45 (410 Tage - 1 Jahr (365 Tage!))
  • md = Tage minus Monate minus Jahre DATEDIF("01.01.98";"15.02.99";"md") = 14 (Differenz vom 01.02. & 15.02))

Das Ergebnis der letzten beiden Beispiele mag einen verwundern, man beachte aber, daß es sich um die Differenz handelt.

[Einzellink]

Tipp 181: Summewenn mit mehreren Kriterien

Wie kann man mit der Funktion SUMMEWENN auch mehrere Kriterien bestimmen?


Dazu muß man eine Array-Funktion benutzen. Dies erreicht man, indem man erst die normale Formel angibt und dann Strg + Umschalttaste + Enter drückt.

Bsp.: Folgende Formel summiert, wenn in Spalte A ein a und in derselben Zeile in Spalte B ein h ist, die entsprechenden Werte der Spalte C. Die geschweiften Klammern signalisieren, das es ein Array ist.

Wie geschrieben: Die geschweiften Klammern nicht eingeben, diese werden durch Strg + Shift + Enter erzeugt.

[Einzellink]

Tipp 182: Formeln drucken

Wie kann ich anstelle der Ergebnisse die Formeln drucken?


  1. Sollen alle Formeln des Blattes gedruckt werden, kann das per Extras - Optionen - Ansicht - Formeln festgelegt werden. Alternativ geht es auch mit der Tastenkombination Strg und #.
  2. Sollen nur ausgewählte Formeln gedruckt werden, kann man als erstes in die Zelle ein Hochkomma (') eingeben, dann wird der Inhalt der Zelle als Text behandelt. Das empfiehlt sich übrigens auch dann, wenn eine längere Formel nicht beendet ist, aber die Mappe geschlossen werden soll.

[Einzellink]

Tipp 183: Zeichen zu Zellinhalt hinzufügen

Wie füge ich an mehrere Zellen mit einen beliebigen Zellinhalt mehrere, gleichbleibende Zeichen (am Anfang oder Ende) hinzu?


="abc " & A1 & "def"

[Einzellink]

Tipp 184: Teil einer Zelle mit Text verbinden

In Zeile 1 stehen in den Zellen Vornamen und Namen, in Zeile 2 die zugehörigen Anreden. Wie kann ich erreichen, daß ein einer anderen Zelle nur die Anrede mit dem Nachnamen erscheint?


=A2&" "&RECHTS(A1; LÄNGE(A1)-FINDEN("";A1))

[Einzellink]

Tipp 185: Anzahl der Einträge in einer Spalte

Wie kann ich die Anzahl der Einträge in einer Spalte feststellen?


=ANZAHL2(A1:A9999)

[Einzellink]

Tipp 186: Wenn Quellzelle leer dann Zielzelle leer

Wie kann ich erreichen, daß bei einer Berechnung eine Zahl nur dann erscheint, wenn eine andere Zelle eine Zahl enthält?


=WENN(A1="";"";WENN(A2="";"";A1+A2))

=WENN(A1+A2>0;A1+A2;"")

[Einzellink]

Tipp 187: Minuszeichen nach vorn

Wie kann ich aus einer Zahl, hinter der das Minuszeichen steht, eine negative Zahl erstellen?


=WENN(RECHTS(A1;1)="-";(-1)*LINKS(A1;LÄNGE(A1)-1);1*A1)

[Einzellink]

Tipp 188: Uhrzeiten - Ergebnis als Dezimalzahl

Wie kann ich als Ergebnis einer Uhrzeitenberechnung eine Dezimalzahl erhalten?


=(A2-A1)*24

[Einzellink]

Tipp 189: Zufallszahl

Wie kann ich eine Zufallszahl zwischen dem 01.01.60 und dem 01.01.80 erzeugen?


Dazu im Add-In-Manager das Analyse-Funktionen-Add-In installieren. Danach steht die Funktion ZUFALLSBEREICH(untereGrenze;obereGrenze) zur Verfügung. Zum Generieren der Datumszufallszahl muss die untere Grenze auf 21916 und die obere Grenze auf 29221 gesetzt werden.

[Einzellink]

Tipp 190: Ausgabe eines Formular-Listenfeld-Eintrages

Wie kann ich in einer Zelle den Eintrag eines Listenfeldes ausgeben statt der Zahl?


=INDEX(A1:A7;A8)

In A8 steht die Ausgabeverknüpfung, A1:A7 ist die Liste.

[Einzellink]

Tipp 191: Zahlenformat in Formel

In A1 steht AAA, in B1 steht 1 - allerdings formatiert mit 0000, also erscheint 0001. Wenn ich jetzt die Formel =A1&B1 eingebe, erscheint leider nur AAA1 - nicht AAA0001.


=A1&TEXT(B1;"0000")

[Einzellink]

Tipp 192: Mit negativen Zeiten rechnen

Ich benötige für unsere Zeiterfassung die Möglichkeit, auch negative Zeiten (in Stunden und Minuten) berechnen zu können. Beispiel: A1 = 100:00 A2 = 120:00 A3 = (A1-A2) Daraufhin werden nur ####... angezeigt.


Mit dieser Möglichkeit wird die Differenz immer (!) positiv angezeigt:

=WENN(A2-A1<0;(A2-A1)*-1;A2-A1)

Bei dieser Variante werden negative Differenzen mit Minuszeichen angezeigt; diese sind dann aber Text, d. h., damit kann nicht gerechnet werden:

=WENN(A2-A1<0;"-"&TEXT((A2-A1)*-1;"[hh]:mm");A2-A1)

Das Datumsformat in den Optionen auf 1904 umstellen. Das ist aber nicht ganz problemlos, siehe Datum und Zeit.

[Einzellink]

Tipp 193: Anzahl im gefilterten Bereich

Ich möchte in einer Spalte die Anzahl eines bestimmten Begriffes. Dies funktioniert mit "Zählenwenn". Wenn ich aber Filter setze, ändert sich die Anzahl nicht.


Die 3 steht für Anzahl2, in C2 bis C6 befinden sich die zu zählenden Daten:

=TEILERGEBNIS(3;C2:C6)

[Einzellink]

Tipp 194: Inhalt einer Zelle als Bezug

In A1 steht $C$1, in C1 steht Hallo. Wie kann ich in B1 den Inhalt der Zelle sichtbar machen, die in A1 genannt ist - also von C1?


=INDIREKT(A1)

[Einzellink]

Tipp 195: Letzter Wert einer Spalte

Wie bekomme ich den letzten Wert einer Spalte z. B. von B210 nach z. B. A1, wenn die Spalte jeden Tag um einen Wert erweitert wird?


Wenn Spalte B keine leeren Zellen zwischen den einzelnen Daten aufweist, kann folgende Formel verwendet werden:

=INDIREKT("B"&ANZAHL2(B2:B10000)+1)

Das +1 heißt, daß der erste Wert in B in Zeile 2 steht. Steht der erste Wert in Zeile 1, kann es weggelassen werden. Steht der erste Wert aber in Zeile 3, wäre es +2 usw.

[Einzellink]

Tipp 196: Runden auf fünf Pfennige oder Cent

Gibt es eine EXCEL-Funktion, um auf fünf Pfennige bzw. Cent zu runden?


=VRUNDEN(A1;0,05)

[Einzellink]

Tipp 208: Zeilenumbruch in Formel erzwingen

Wie kann ich in einer Formel einen Zeilenumbruch erzwingen?


=A1&ZEICHEN(10)&B1&" "&C1&ZEICHEN(10)&D1&ZEICHEN(10)&ZEICHEN(10)&E1&" "&F1

[Einzellink]

Tipp 209: Wert in der Zelle neben dem Maximalwert ermitteln

Wie kann ich den Wert einer Zelle rechts neben der Zelle mit dem Maximalwert ermitteln?


=SVERWEIS(MAX(A:A);A:B;2;0)

[Einzellink]

 

Das Chemtrailhandbuch von Jörg Lorenz mit einem Vorwort von Jörg Kachelmann

Susanne Hammann-Sayilir - Modedesign  Modeillustration