Direktnachricht

Ihre Nachricht:



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

Hinweise zu den Beispielen finden Sie hier: Beispiele

Kategorie: Beispiel > Excel > Formel

Beitrag zuklappen Balkendiagramm in Zelle

Nach oben Adresse des Beitrags Nachricht zum Beitrag an Autor (Tipp 175)

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

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

Nach oben Adresse des Beitrags Nachricht zum Beitrag an Autor (Tipp 176)

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.

Beitrag zuklappen Teil eines Datums einlesen

Nach oben Adresse des Beitrags Nachricht zum Beitrag an Autor (Tipp 177)

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.

Beitrag zuklappen Text und Datumswert verketten

Nach oben Adresse des Beitrags Nachricht zum Beitrag an Autor (Tipp 178)

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.

Beitrag zuklappen Pfad in Zelle

Nach oben Adresse des Beitrags Nachricht zum Beitrag an Autor (Tipp 179)

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

Beitrag zuklappen Differenz zwischen zwei Datumswerten

Nach oben Adresse des Beitrags Nachricht zum Beitrag an Autor (Tipp 180)

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.

Beitrag zuklappen Summewenn mit mehreren Kriterien

Nach oben Adresse des Beitrags Nachricht zum Beitrag an Autor (Tipp 181)

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.

Beitrag zuklappen Formeln drucken

Nach oben Adresse des Beitrags Nachricht zum Beitrag an Autor (Tipp 182)

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.

Beitrag zuklappen Zeichen zu Zellinhalt hinzufügen

Nach oben Adresse des Beitrags Nachricht zum Beitrag an Autor (Tipp 183)

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

="abc " & A1 & "def"

Beitrag zuklappen Teil einer Zelle mit Text verbinden

Nach oben Adresse des Beitrags Nachricht zum Beitrag an Autor (Tipp 184)

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

Beitrag zuklappen Anzahl der Einträge in einer Spalte

Nach oben Adresse des Beitrags Nachricht zum Beitrag an Autor (Tipp 185)

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

=ANZAHL2(A1:A9999)

Beitrag zuklappen Wenn Quellzelle leer dann Zielzelle leer

Nach oben Adresse des Beitrags Nachricht zum Beitrag an Autor (Tipp 186)

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

Beitrag zuklappen Minuszeichen nach vorn

Nach oben Adresse des Beitrags Nachricht zum Beitrag an Autor (Tipp 187)

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)

Beitrag zuklappen Uhrzeiten - Ergebnis als Dezimalzahl

Nach oben Adresse des Beitrags Nachricht zum Beitrag an Autor (Tipp 188)

Wie kann ich als Ergebnis einer Uhrzeitenberechnung eine Dezimalzahl erhalten?

=(A2-A1)*24

Beitrag zuklappen Zufallszahl

Nach oben Adresse des Beitrags Nachricht zum Beitrag an Autor (Tipp 189)

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.

Beitrag zuklappen Ausgabe eines Formular-Listenfeld-Eintrages

Nach oben Adresse des Beitrags Nachricht zum Beitrag an Autor (Tipp 190)

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.

Beitrag zuklappen Zahlenformat in Formel

Nach oben Adresse des Beitrags Nachricht zum Beitrag an Autor (Tipp 191)

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

Beitrag zuklappen Mit negativen Zeiten rechnen

Nach oben Adresse des Beitrags Nachricht zum Beitrag an Autor (Tipp 192)

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.

Beitrag zuklappen Anzahl im gefilterten Bereich

Nach oben Adresse des Beitrags Nachricht zum Beitrag an Autor (Tipp 193)

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)

Beitrag zuklappen Inhalt einer Zelle als Bezug

Nach oben Adresse des Beitrags Nachricht zum Beitrag an Autor (Tipp 194)

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)

Beitrag zuklappen Letzter Wert einer Spalte

Nach oben Adresse des Beitrags Nachricht zum Beitrag an Autor (Tipp 195)

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.

Beitrag zuklappen Runden auf fünf Pfennige oder Cent

Nach oben Adresse des Beitrags Nachricht zum Beitrag an Autor (Tipp 196)

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

=VRUNDEN(A1;0,05)

Beitrag zuklappen Runden auf volle Fünfer

Nach oben Adresse des Beitrags Nachricht zum Beitrag an Autor (Tipp 197)

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)

Beitrag zuklappen SVERWEIS: Zellen rechts addieren

Nach oben Adresse des Beitrags Nachricht zum Beitrag an Autor Beispiel hier anzeigen/aktualisieren Beispieldatei in Excel Online (Tipp 198)

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.

Beitrag zuklappen Zeitdifferenz in Sekunden

Nach oben Adresse des Beitrags Nachricht zum Beitrag an Autor (Tipp 199)

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.

Beitrag zuklappen SVERWEIS auf Spanisch

Nach oben Adresse des Beitrags Nachricht zum Beitrag an Autor (Tipp 200)

Wie heißt der SVERWEIS auf Spanisch?

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

Beitrag zuklappen Zellbezug und Kopieren

Nach oben Adresse des Beitrags Nachricht zum Beitrag an Autor (Tipp 201)

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.

Beitrag zuklappen Mehrwertsteuer und Runden

Nach oben Adresse des Beitrags Nachricht zum Beitrag an Autor (Tipp 202)

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.

Beitrag zuklappen Ostersonntag aus Jahr errechnen

Nach oben Adresse des Beitrags Nachricht zum Beitrag an Autor (Tipp 203)

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.

Beitrag zuklappen Blattname in Zelle

Nach oben Adresse des Beitrags Nachricht zum Beitrag an Autor (Tipp 204)

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

Beitrag zuklappen Zellbezug in anderer Zelle

Nach oben Adresse des Beitrags Nachricht zum Beitrag an Autor (Tipp 205)

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.

Beitrag zuklappen Datum und Text in einer Zelle

Nach oben Adresse des Beitrags Nachricht zum Beitrag an Autor (Tipp 206)

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

Beitrag zuklappen Anleitung: Rechnung erstellen

Nach oben Adresse des Beitrags Nachricht zum Beitrag an Autor (Tipp 207)

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:

Beitrag zuklappen Zeilenumbruch in Formel erzwingen

Nach oben Adresse des Beitrags Nachricht zum Beitrag an Autor (Tipp 208)

Wie kann ich in einer Formel einen Zeilenumbruch erzwingen?

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

Beitrag zuklappen Wert in der Zelle neben dem Maximalwert ermitteln

Nach oben Adresse des Beitrags Nachricht zum Beitrag an Autor (Tipp 209)

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

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