☑ Datenschutz
☑ Datenschutz
⌧
⌧
Hinweise zu den Beispielen finden Sie hier: Beispiele
Kategorie: Beispiel > Excel > Formel
Balkendiagramm in Zelle
(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".
Erweiterung für MAX(), z. B. dritthöchster Wert?
(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:
Dementsprechend funktioniert auch die Funktion KKLEINSTE, sie sucht nach den kleinsten Zahlen.
Teil eines Datums einlesen
(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.
Text und Datumswert verketten
(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.
Pfad in Zelle
(Tipp 179)
Wie erhalte ich in einer Zelle den aktuellen Pfad?
=INFO("Verzeichnis")
F9 drücken zum Aktualisieren.
Tips (ähnliche Funktionen):
Differenz zwischen zwei Datumswerten
(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):
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)
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)
Wie kann ich anstelle der Ergebnisse die Formeln drucken?
Zeichen zu Zellinhalt hinzufügen
(Tipp 183)
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)
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)
Wie kann ich die Anzahl der Einträge in einer Spalte feststellen?
=ANZAHL2(A1:A9999)
Wenn Quellzelle leer dann Zielzelle leer
(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;"")
Minuszeichen nach vorn
(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)
Uhrzeiten - Ergebnis als Dezimalzahl
(Tipp 188)
Wie kann ich als Ergebnis einer Uhrzeitenberechnung eine Dezimalzahl erhalten?
=(A2-A1)*24
Zufallszahl
(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.
Ausgabe eines Formular-Listenfeld-Eintrages
(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.
Zahlenformat in Formel
(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")
Mit negativen Zeiten rechnen
(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.
Anzahl im gefilterten Bereich
(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)
Inhalt einer Zelle als Bezug
(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)
Letzter Wert einer Spalte
(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.
Runden auf fünf Pfennige oder Cent
(Tipp 196)
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)
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)
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)
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)
Wie heißt der SVERWEIS auf Spanisch?
=BUSCARV(B1;A:B;2;FALSO)
Zellbezug und Kopieren
(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.
| A | B | C | Bedeutung | |
| Zeile 1 | =A1 | =B1 | eine Zelle nach links | |
| Zeile 2 | =A2 | =B2 | eine 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)
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)
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)
Wie kann man per Formel den Namen des Blattes in die Zelle einfügen?
Zellbezug in anderer Zelle
(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.
Datum und Text in einer Zelle
(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.
Anleitung: Rechnung erstellen
(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.:
Zeilenumbruch in Formel erzwingen
(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
Wert in der Zelle neben dem Maximalwert ermitteln
(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)
Innerhalb einer Datumsspanne Zahlen suchen, Zahlen aus anderer Spalte addieren (SUMMEWENN)
(Tipp 327)
Letzte ausgefüllte Zelle einer Spalte
(Tipp 334)