Datenschutzerklärung


Excel-Beispiele:
Erklärungen, Formeln, VBA-Code und mehr

Hinweise zu den Beispielen finden Sie hier: Beispiele


Kategorie: Beispiel > Excel > Formel (40)

Direktnachricht



Balkendiagramm in Zelle

(Tipp 175) Nachricht zum Beitrag an Autor Nach oben

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".

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

(Tipp 176) Nachricht zum Beitrag an Autor Nach oben

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.

Teil eines Datums einlesen

(Tipp 177) Nachricht zum Beitrag an Autor Nach oben

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.



Text und Datumswert verketten

(Tipp 178) Nachricht zum Beitrag an Autor Nach oben

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.

Pfad in Zelle

(Tipp 179) Nachricht zum Beitrag an Autor Nach oben

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

Differenz zwischen zwei Datumswerten

(Tipp 180) Nachricht zum Beitrag an Autor Nach oben

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.

Summewenn mit mehreren Kriterien

(Tipp 181) Nachricht zum Beitrag an Autor Nach oben

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.

Formeln drucken

(Tipp 182) Nachricht zum Beitrag an Autor Nach oben

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.

Zeichen zu Zellinhalt hinzufügen

(Tipp 183) Nachricht zum Beitrag an Autor Nach oben

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

="abc " & A1 & "def"

Teil einer Zelle mit Text verbinden

(Tipp 184) Nachricht zum Beitrag an Autor Nach oben

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))

Anzahl der Einträge in einer Spalte

(Tipp 185) Nachricht zum Beitrag an Autor Nach oben

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

=ANZAHL2(A1:A9999)

Wenn Quellzelle leer dann Zielzelle leer

(Tipp 186) Nachricht zum Beitrag an Autor Nach oben

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;"")

Minuszeichen nach vorn

(Tipp 187) Nachricht zum Beitrag an Autor Nach oben

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)

Uhrzeiten - Ergebnis als Dezimalzahl

(Tipp 188) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich als Ergebnis einer Uhrzeitenberechnung eine Dezimalzahl erhalten?

=(A2-A1)*24



Zufallszahl

(Tipp 189) Nachricht zum Beitrag an Autor Nach oben

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.

Ausgabe eines Formular-Listenfeld-Eintrages

(Tipp 190) Nachricht zum Beitrag an Autor Nach oben

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.

Zahlenformat in Formel

(Tipp 191) Nachricht zum Beitrag an Autor Nach oben

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")

Mit negativen Zeiten rechnen

(Tipp 192) Nachricht zum Beitrag an Autor Nach oben

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.

Anzahl im gefilterten Bereich

(Tipp 193) Nachricht zum Beitrag an Autor Nach oben

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)

Inhalt einer Zelle als Bezug

(Tipp 194) Nachricht zum Beitrag an Autor Nach oben

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)

Letzter Wert einer Spalte

(Tipp 195) Nachricht zum Beitrag an Autor Nach oben

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.



Runden auf fünf Pfennige oder Cent

(Tipp 196) Nachricht zum Beitrag an Autor Nach oben

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

=VRUNDEN(A1;0,05)

Runden auf volle Fünfer

(Tipp 197) Nachricht zum Beitrag an Autor Nach oben

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)

SVERWEIS: Zellen rechts addieren

(Tipp 198) Beispieldatei in Excel Online Nachricht zum Beitrag an Autor Nach oben

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.

Zeitdifferenz in Sekunden

(Tipp 199) Nachricht zum Beitrag an Autor Nach oben

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.

SVERWEIS auf Spanisch

(Tipp 200) Nachricht zum Beitrag an Autor Nach oben

Wie heißt der SVERWEIS auf Spanisch?

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

Zellbezug und Kopieren

(Tipp 201) Nachricht zum Beitrag an Autor Nach oben

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.

Mehrwertsteuer und Runden

(Tipp 202) Nachricht zum Beitrag an Autor Nach oben

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.



Ostersonntag aus Jahr errechnen

(Tipp 203) Nachricht zum Beitrag an Autor Nach oben

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.

Blattname in Zelle

(Tipp 204) Nachricht zum Beitrag an Autor Nach oben

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

Zellbezug in anderer Zelle

(Tipp 205) Nachricht zum Beitrag an Autor Nach oben

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.

Datum und Text in einer Zelle

(Tipp 206) Nachricht zum Beitrag an Autor Nach oben

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

Anleitung: Rechnung erstellen

(Tipp 207) Nachricht zum Beitrag an Autor Nach oben

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:

Zeilenumbruch in Formel erzwingen

(Tipp 208) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich in einer Formel einen Zeilenumbruch erzwingen?

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

Wert in der Zelle neben dem Maximalwert ermitteln

(Tipp 209) Nachricht zum Beitrag an Autor Nach oben

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

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