Eine Liebeserklärung an MS Excel
Schöne Lösung in der Excelschulung.
Wir überprüfen, ob ein Wert zwischen 25 und 30 liegt.
Eine Teilnehmerin präsentiert folgende Lösung:
=WENN(25<B2>30;"dazwischen";"außerhalb")Es klappt. Für die Werte zwischen 25 und 30. Warum?
Die Antwort: 25<B2 liefert WAHR oder FALSCH:
WAHR (oder FALSCH) ist jedoch immer > 30. Warum? Excel hat folgende benutzerdefinierte Reihenfolge:
Buchstabe < Zahl < WAHR/FALSCH
Hallo Herr Martin,
ich verfolge mit Begeisterung Ihre Excelkurse und -hilfen, die mir schon in der einen oder anderen Situation weitergeholfen haben. Nun scheitere ich aber dann doch und hoffe Sie können mir weiterhelfen.
Ich arbeite mit Excel 2019 und habe folgende Herausforderung. Ich möchte zu einem eingegebenen Wert alle Daten, die sich in der direkt danebenstehenden Spalte befinden anzeigen lassen. An den Wert, den ich eingebe, sind z. B. 10 Datensätze verknüpft. Ich habe es schon mit einer Matrixformel probiert, die index und vergleich beinhaltet, aber meist bekomm ich nur einen Wert, der dann aber z. B. 10 mal daneben angezeigt wird, je nachdem wie weit ich die Formel runterziehe.
Ich hoffe Sie können mir einen kleinen Tipp geben und weiterhelfen.
Liebe Grüße
Hallo Frau S.,
Sie verwenden wahrscheinlich den SVERWEIS, oder? Der SVERWEIS verlangt die Nummer der Spalte, in welcher der Wert gefunden werden soll. Man kann hier die Funktion SPALTE() verwenden – sie gibt die Nummer der aktuellen Spalte zurück (möglicherweise muss man + oder – eine Zahl rechnen.
Man kann mit WENN abfangen, ob überhaupt etwas gefunden wird und mit WENNFEHLER auf mögliche Fehler reagieren.
Hilft das?
Ich hänge mal eine Dummy-Datei an.
LIebe Grüße :: Rene Martin
Gestern habe ich über den interessanten Beitrag von Martin Weiß (tabellenexperte) geschrieben, der herausgefunden hat, dass SVERWEIS, wenn er auf eine andere Datei zugreift, die gesamte Datei – genauer: den gesamten ausgewählten Bereich – speichert:
Wird die Quelldatei geschlossen und umbenannt, funktioniert der SVERQWEIS noch immer – man kann sogar eine andere Spaltennummer wählen und ein anderes Suchkriterium.
Ich werde neugierig und benenne die Zieldatei mit .ZIP um und entzippe sie.
Tatsächlich: im Order \xl\externalLinks befindet sich die Datei externalLink1.xml in welcher sämtliche Daten gespeichert sind!
Fatal. Oder – wie Martin zu recht schreibt: Aufpassen!
Martin (der tabellenexperte) wundert sich, dass bei der Verwendung von SVERWEIS & co gesamte Tabellen in die Datei geladen werden … Aber: kennt Abhilfe.
Danke für die wertvollen Informationen und die guten Hinweise.
Gestern in der Excelschulung habe ich einen Kalender erstellen lassen. Ich wollte die Wochenenden mit einer Bedingten Formatierung grau formatieren.
Ich frage, wie man mit Hilfe der Funktion WOCHENTAG den Samstag (Wochentag = 6) und den Sonntag (Wochenentag = 7) grau formatieren kann.
Eine Teilnehmerin fragt, ob man das mit einem Doppelpunkt machen kann: von Samstag bis Sonntag, also 6:7.
Eine gute Idee, aber Excel kennt SOLCHE Bereiche leider nicht.
Ich überlege: A6:A7 funktioniert.
Tabelle6:Tabelle7 funktioniert.
Aber 6:7 wäre entweder eine Uhrzeit oder Zeile 6 bis Zeile 7.
Die Lösung lautet natürlich Wochentag > 5.
Ein guten Tag bei Sonnenschein René,
ich hoffe, dass es dir so weit gut geht
und frage heute nach, ob du mir eine EXCEL-Lsg verkaufen kannst,
die du bestimmt schon zigmal implementiert hast:
Thema: Dropdownliste mit Datenquelle
Bei der der Datenbereich gefüllt ist, aber z.B. keine Daten in den letzten 10 Zeilen hat.
Effekt, den du kennst:
Unter dem letzten Eintrag existieren viele, viele leere Zeilen.
Man scheint das Problem der leeren Zellen beheben zu können,
in dem man die Zellfunktionalität BEREICH.VERSCHIEBEN nutzt.
###
Hallo Jürgen,
verwende mal die Formel
=BEREICH.VERSCHIEBEN(Daten!$A$1;$E$12:$F$1210;0;ANZAHL2(Daten!$A$2:$A$50);1)
Oder du verwendest eine intelligente Tabelle und gibst ihr einen Namen und verwendest den Namen in der Datenüberprüfung. Geht auch. IN Excel 2016.
Schöne Frage in der Excel-Schulung:
Warum umschließen zwei eckige Klammern die Spalte Kategorie-Nummer?
Die Antwort: Wegen des Trennzeichens. Würde man die Spalte in KategorieNr umbenennen, würde die Formel lauten:
=XVERWEIS([@KategorieNr];tbl_Kategorien[Kategorie-Nr];tbl_Kategorien[Kategoriename])
Mit Trennzeichen würde @Kategorie-Nr nicht „verstanden“ werden.
Gestern auf dem Excelstammtisch stellte Thomas eine interessante Frage:
Ich habe eine Liste mit Internetadressen. Auf diese verweist die Funktion HYPERLINK:
Wenn ich die Zelle kopiere, klappt der Link nicht.
Klar – hier befindet sich ein relativer Verweis auf B4. Er muss in einen absoluten umgewandelt werden.
Aber, so fragt Thomas weiter, wenn ich nun die Funktion auf ein anderes Blatt kopiere?
Dann könnte die Funktion INDIREKT Abhilfe schaffen, beispielsweise so:
=HYPERLINK(INDIREKT("B4");"Trägheit")
Und wenn die Zelle in eine andere Datei kopiert werden soll?
Ich empfehle den Bezug zu markieren und durch [F9] in den Wert zu ersetzen:
Aber ich habe sehr viele Links, erwidert Thomas.
Und fügt an: ich kopiere sie bisher immer nach google sheets.
Das ist wohl auch eine Lösung – wenn man nicht VBA programmieren möchte oder ChatGPT um Hilfe fragen will.
Diese Woche werde ich nicht nörgeln. Ich verspreche es.
Excel ist schon klasse.
Und: in Excel kommen regelmäßig neue, brauchbare Funktionen hinzu. Wer Microsoft 365 hat, wird sie schon entdeckt haben. Hier Technik IV:
Markiert man in der Bearbeitungszeile oder in einer Zelle einen Teil einer Formel, wird im Quickinfo das Ergebnis angezeigt. [F9] ist nicht mehr nötig!
Diese Woche werde ich nicht nörgeln. Ich verspreche es.
Excel ist schon klasse.
Und: in Excel kommen regelmäßig neue, brauchbare Funktionen hinzu. Wer Microsoft 365 hat, wird sie schon entdeckt haben. Hier Technik I:
Man kann jetzt mit der Tastenkombination [Umschalt] + [Strg] + [V] kopierten Text als Wert einfügen und somit Formeln in Werte verwandeln.
Mal nix zu nörgeln.
Stefan hat mich auf eine interessante Seite aufmerksam gemacht: GPT Excel – AI Powered Excel formula Generator
Spannend! Damit Excel nicht so sehr nervt.
Frage in der Excelschulung: in drei Spalten stehen verteilt Titel | Vorname | Nachname.
Sie sollen verkettet werden.
Wendet man die Blitzvorschau an und schreibt in die erste Zelle Vorname und Nachname, dann fehlt der Titel:
Hat die erste Person einen Titel und trägt man alle drei Informationen ein, wird ein Leerzeichen bei den Personen ohne Titel eingefügt:
Die Blitzvorschau führt hier zu keinem brauchbaren Weg.
Die Teilnehmerin, die die Frage gestellt hatte, war sehr traurig.
Die Lösung für das Problem heißt beispielsweise: die Funktion „TEXTVERKETTEN“.
=TEXTVERKETTEN(" ";WAHR;C2:E2)
Wichtig ist der Wert WAHR beim Parameter Leer_ignorieren. So wird bei Personen, die keinen Titel haben, die leere Zelle nicht mit einem Leerzeichen verkettet, sondern übergangen.
Die Teilnehmerin war glücklich.
Lieber Herr Martin,
wieder wende ich mich mit einer Excel-Frage an Sie.
Als Anhang schicke ich Ihnen eine Excel-Datei, in der zwei bedingte Formate eingefügt wurden:
1. Die optische Hervorhebung der aktuellen Woche.
2. Die optische Hervorhebung des aktuellen Tages.
Gibt es die Möglichkeit, daß auch die Spalte des aktuellen Tages ebenfalls farblich hervorgehoben werden kann?
Vielen Dank im Voraus für Ihre Hilfe,
###
Hallo Herr F.,
haben Sie schon von KI gehört? Von ChatGPT oder copilot?
Ich verwende sie seit einer Weile und empfehle allen, dies auch zu tun.
Es hilft!
Natürlich weiß ich die Antwort auf Ihre Frage. Ich habe die Frage bei ChatGPT gepostet und erhalte folgende Antwort:
In einer Excel-Datei befinden sich Datumsangaben. Mit zwei bedingten Formate sollen: 1. Die optische Hervorhebung der aktuellen Woche. 2. Die optische Hervorhebung des aktuellen Tages. Gibt es die Möglichkeit, daß auch die Spalte des aktuellen Tages ebenfalls farblich hervorgehoben werden kann?
ChatGPT
Ja, es ist möglich, in Excel Datumsangaben mit bedingten Formatierungen hervorzuheben. Du kannst bedingte Formatierungen verwenden, um die aktuelle Woche und den aktuellen Tag in einer Excel-Tabelle zu markieren. Um auch die gesamte Spalte des aktuellen Tages farblich hervorzuheben, müssen wir eine Kombination von Formeln und bedingten Formatierungen verwenden. Hier sind die Schritte:
Aktuelle Woche hervorheben:
Ich habe es etwas anders gelöst. Mit den folgenden drei Formeln:
=A1=HEUTE()
(nur für die Zeile)
=A$1=HEUTE()
(für die ganze Spalte)
=KALENDERWOCHE(A1;21)=KALENDERWOCHE(HEUTE();21)
Sehr geehrter Herr Martin,
ich bin aktuell verzweifelt auf der Suche nach einer Lösung und eine Freundin von mir hat mir gesagt, dass Sie ihr netterweise eine Frage beantwortet haben und da dachte ich, ich versuche mein Glück ebenfalls. Ich habe einen Jahreskalender auf Excel erstellt und über das Jahr verteilt wiederholt sich ein Termin mehrmals, nur immer an einem anderen Tag. Gibt es die Möglichkeit, dass man die gesamte Tabelle so filtert, dass einem nur noch dieser Termin angezeigt wird? Also nicht nach einzelnen Spalten filtern, das kann ich, sondern das mir dieser Termin in jedem Monat auf einer Seite gleichzeitig angezeigt wird.
Ich bedanke mich jetzt schon im Voraus vielmals!
Mit freundlichen Grüßen
####
Hallo Frau K.,
es kommt darauf an, wie Sie Ihren Kalender aufgebaut haben – untereinander, nebeneinander, verteilt auf mehrere Tabellenblätter.
Und: haben Sie Microsoft 365? Haben Sie die (neue) Funktion FILTER?
Gerne können Sie mir die Datei (oder einen Dummy) zuschicken – ich schaue es mir an:
####
Hallo Herr Martin,
vielen Dank für die schnelle Rückmeldung.
Anbei eine Dummy Version meines Kalenders. Ich habe jetzt beliebige Beispiele in die Monate eingetragen und Sport extra mehrmals zum Filtern.
Microsoft 365 habe ich leider nicht.
Liebe Grüße
Hallo Frau Kain,
ich hoffe, Sie haben die Funktion TEXTKETTE. Sonst wird es schwierig …
In A42 wählen Sie die Kategorie aus.
In C43 wird die Liste der Datumsangaben angezeigt.
Ich habe folgende Formel verwendet:
=TEXTKETTE(WENN(B3:AG38=A42;(SPALTE(B3:AG38)-2)&"."&(GANZZAHL((ZEILE(B3:AG38)-1)/3))&ZEICHEN(10);""))Hallo Herr Martin,
vielen Dank für die schnelle Hilfe.
Genauso hatte ich mir das vorgestellt. Ich probiere es mal in meinem Jahreskalender aus. Vielleicht kriege ich es ja auch genauso hin.
Eine Teilnehmerin einer Excelschulung stellt mir eine Frage. Ob ich ihr helfen könne.
Klar.
In einer Tabelle hat sie in einer Spalte Namen, in einer anderen einen Rechnungsbetrag:
Sie möchte auf einem zweiten Tabellenblatt zu dem Namen den entsprechenden Rechnungsbetrag sehen:
Nichts leichter als das: XVERWEIS muss her:
=XVERWEIS(A2;Tabelle1!$E$2:$E$51;Tabelle1!$H$2:$H$51)
Sieht doof aus, weil in beiden Tabellen Leerzeilen stehen und Überschriftsinformationen, welche nun mit 0 Euro quittiert werden.
Also wird die Formel angepasst:
=WENN(XVERWEIS(A2;Tabelle1!$E$2:$E$51;Tabelle1!$H$2:$H$51)=0;"";XVERWEIS(A2;Tabelle1!$E$2:$E$51;Tabelle1!$H$2:$H$51))Sieht doof aus, weil Nina 0 Euro hat und nun eine leere Zelle:
Also: Formel ändern und 0 durch „“ ersetzen:
=WENN(XVERWEIS(A8;Tabelle1!$E$2:$E$51;Tabelle1!$H$2:$H$51)="";"";XVERWEIS(A8;Tabelle1!$E$2:$E$51;Tabelle1!$H$2:$H$51))
Immer noch doof – weiter unten entdecken wir einen Namen, der noch nicht auf dem ersten Tabellenblatt auftauchte. Also ändern wir den Parameter wenn_nicht_gefunden in „“ – und schon verschwindet das #NV
Die Formel:
=WENN(XVERWEIS(A27;Tabelle1!$E$2:$E$51;Tabelle1!$H$2:$H$51;"")="";"";XVERWEIS(A27;Tabelle1!$E$2:$E$51;Tabelle1!$H$2:$H$51;""))Die Teilnehmerin ist glücklich!
Gestern auf dem London Excel Meetup group, Munich, Germany.
Martin Weiß hat einen tollen Vortrag gehalten, wie man mit einer Hilfsspalte und der Funktion AGGREGAT überprüfen kann, ob eine Zeile ausgeblendet ist:
Ich wollte es mal wissen, ob sich AGGREGAT genauso verhält wie TEILERGEBNIS.
Zur Erinnerung: Die Funktionen SUMME, TEILERGEBNIS und AGGREGAT mit Parameter 9, beziehungsweise 109 berechnen die Summe:
Filtert man die Liste, werden nur die gefilterten Werte von TEILERGEBNIS und AGGREGAT summiert:
Blendet man Zeilen aus, werden diese nur von TEILERGEBNIS mit Parameter 109 und AGGREGAT summiert:
Filtert man die Liste UND blendet Zeilen aus, werden sie von TEILERGEBNIS und AGGREGAT summiert:
Das irritiert, weil die Hilfe erläutert, dass der Parameter 109 die ausgeblendeten und gefilterten Daten nicht summiert, der Parameter 9 jedoch nur die gefilterten.
Das heißt: Der Parameter 9 von TEILERGEBNIS summiert Werte von ausgeblendeten Zeilen, wenn kein Filter gesetzt ist, summiert sie jedoch nicht, bei einem eingeschalteten Filter.
AGGREGAT hingegen mit dem Parameter 9 verhält sich wie TEILERGEBNIS mit 109.
Seltsam!
Im Rätsel 5 bei der Funktion Wochentag – was ist der Unterschied zwischen Typ 2 und Typ 11? Mir sehen sie ident aus… Zahlen 1 (Montag) bis 7 (Sonntag).
Ich glaube nicht, dass es einen Unterschied gibt. Ich vermute, der Parameter 11 wurde aufgenommen, um Vollständigkeit bei „beginnt bei Mo, Di, Mi, …)“ zu haben – also eine zweite Liste, die sich mit der ersten überschneidet.
Hallo René,
Ich gucke gerne deine Excel Erklärvideos auf LinkedIn. Ich habe aber eine Frage und dachte ich wende mich mal an den Excel-Experten.
Ich würde gerne eine Tabelle zum Tagesablauf anfertigen, in der ich in der ersten Spalte die Urzeit habe und in der zweiten Spalte die jeweilige dazu gehörige Aktivität. Da der Zeitplan noch nicht feststeht würde ich gerne, dass wenn ich eine Uhrzeit änder sich alle anderen auch anpassen.
Also wenn ich 8:30 in A2 zustehen habe und in A3 9:20 und ich A2 aber auf 8:45 änder, dass sich dann A3 anpasst und sich auf 9:35 ändert.
Hast du eine Idee wie ich das hinbekommen?
Danke in Voraus für deine Hilfe.
Viele Grüße,
Marie
###
Hallo Marie,
ja – das funktioniert mit Formeln.
Wenn es eine Beziehung zwischen den Uhrzeiten gibt, kannst du sie einbauen. Also statt 8:30 und 8:45 schreibst du 8:30 und =A1+15/60/24 (oder die entsprechende Zelle).
Alternative:
Die Uhrzeit, die du hinzuzählst, steht in F1, beispielsweise 00:00:50
Dann steht in A1
=ZEIT(0;8;30)+$F$1
in A2
=ZEIT(0;8;45)+$F$1
Oder – letzte Variante – in der ersten Spalte steht deine Ursprungsuhrzeit (8:30, 8:45, … )
In einer Spalte daneben steht
=A1+$F$1
Die Formel wird nach unten gezogen.
Spalte A kann man ausblenden.
Kommst du damit klar?
LG :: Rene
Ich bin begeistert. Mal nichts zu nörgeln:
Microsoft hat in Excel für Inhalte einfügen als Werte eine Tastenkombination spendiert: [Umschalt] + [Strg] + [V].
Ich bin begeistert!
Auf dem letzten Excelstammtisch haben wir uns über die englischsprachigen Parameternamen amüsiert:
Beispielsweise in SPALTENWAHL. Mehr noch: der Parametername lautet row_num – gemeint ist allerdings die Spalte. Also: col_num …
Sehr seltsam! In einer (intelligenten) Tabelle (tbl_Kunden1) wurde zeilenweise gerechnet. Der Betrag aus der Spalte Rechnungsbetrag wurde um 100 erhöht.
Kopiert man nun diese Formel in eine andere (intelligente) Tabelle, in der sich auch eine Spalte Rechnungsbetrag befindet, wird leider der Bezug auf die erste Tabelle mitgenommen:
Schade!
Einen kleinen Haken hat Excel Labs jedoch: man kann keine Kommentare einfügen:
/* xxx */oder
//oder ähnliche Anweisungen scheitern – klar – wie wären Teil der Formel …
Also muss man doch bei der Funktion N bleiben …
Ich bin begeistert. Seit heute nervt mit Excel etwas weniger. Martin Weiß hat mich auf Excel Lab aufmerksam gemacht. Über die Add-Ins kann man dieses Office Add-In herunterladen und installieren:
Befindet sich in einer Zelle eine Formel, wird sie nach der Hierarchie der verschachtelten Funktionen analysiert. Sehr praktisch!
Dort kann man auch über den Aufgabenbereich die Formel bearbeiten und ändern!
Ich glaube, das hatte ich schon einmal!
Ich suche per VBA eine Kostenstelle in einer anderen Liste und benötige die Zeilennummer, weil ich aus dieser Zeile mehrere Informationen auslesen will:
So steht beispielsweise der Wert von A2 (4711) in Zeile 5.
Sub Kostenstellensuche()
Dim strKostenstelle As String
Dim lngZeile As Long
strKostenstelle = ActiveSheet.Range("A2").Value
If Application.WorksheetFunction.CountIf(ActiveSheet.Columns("D"), strKostenstelle) > 0 Then
lngZeile = Application.WorksheetFunction.Match(strKostenstelle, ActiveSheet.Columns("D"), 0)
MsgBox lngZeile
End If
End SubDas Programm läuft leider an die Wand:
Die Match-Eigenschaft des WorksheetFunction-Objektes kann nicht zugeordnet werden.
Der Grund: da ich nicht sicher bin, ob die Kostenstelle immer als Zahl vorliegt, habe ich sie in einer String-Variablen gespeichert.
Für CountIF (ZÄHLENWENN) stellt dies kein Problem dar; allerdings für Match (Vergleich). Also prüfen:
Sub Kostenstellensuche()
Dim strKostenstelle As String
Dim lngZeile As Long
strKostenstelle = ActiveSheet.Range("A2").Value
If Application.WorksheetFunction.CountIf(ActiveSheet.Columns("D"), strKostenstelle) > 0 Then
If IsNumeric(strKostenstelle) Then
lngZeile = Application.WorksheetFunction.Match(CLng(strKostenstelle), ActiveSheet.Columns("D"), 0)
Else
lngZeile = Application.WorksheetFunction.Match(strKostenstelle, ActiveSheet.Columns("D"), 0)
End If
MsgBox lngZeile
End If
End Sub
Und DAS funktioniert!
Sehr geehrter Herr Martin,
Heute habe ich folgende Frage.
Wenn ich Wörter miteinander verketten möchte, habe ich bisher die Funktion Verketten genommen, wo ich auch zwischen 2 Wörtern ein Komma setzen konnte:
So weit, so gut.
Nehme ich aber die Funktion Textverketten, funktioniert es nicht mit dem Komma:
Frage: Was müßte ich tun bei der letzten Funktion, um z. B. zwischen Nachname und Ort ein Komma zu setzen, dem ein Leerzeichen folgt?
####
Hallo Herr F.,
nein, mit TEXTVERKETTEN können Sie nicht mit unterschiedlichen Trennzeichen verketten. Der Gedanke ist, dass TEXTVERKETTEN mit Bereichen arbeitet:
=TEXTVERKETTEN(„; „;WAHR;A2:H2)
Wenn Sie einzeln verbinden möchten, müssen Sie auf das «&» zurückgreifen oder die alte VERKETTEN-Funktion verwenden.
Ich benutze TEXTVERKETTEN gerne, wenn ich eine Reihe an Mailadressen habe – dann verbinde ich sie zu einer Zeile:
=TEXTVERKETTEN(„;“;WAHR;A1:A555)
und kann sie so über Outlook in das An-Feld eintragen. DAS würde mit VERKETTEN nicht funktionieren.
Liebe Grüße
Rene Martin
Verblüfft war ich schon.
Der Auftrag: der Kunde möchte in eine Excelliste Informationen eintragen, beispielsweise die Namen der Tabellenblätter, die mit einem Klick auf einen Button erzeugt werden. Auf den Blättern werden Verknüpfungen zu den anderen Zellen hergestellt, auf einem weiteren Blatt wird eine Formel aktualisiert. So weit so gut – ich teste – klappt:
Der Kunde testet und schickt meine Mail mit dem Hinweis, dass „Nicht genügend Speicher“ vorhanden sei.
Seltsam – bei mir nicht.
Doch – wenn er 200 Tabellenblätter erzeuge, meldet Excel diesen Fehler nach Blatt Nummer 117.
Seltsam. Bei mir auch:
Der Fehler trat beim Erstellen der Formel auf. Zuerst dachte ich an Schwierigkeiten des Prozessors beim Erstellen so vieler Formeln. Oder vielleicht hatte ich die Objektvariablen nicht sauber „geputzt“. Oder es gab ein Geschwindigkeitsproblem:
Die Ursache war eine andere: Die Formel war schlicht zu lang. Mit der Funktion SUMMEWENNS sollten Berechnungen für jedes Tabellenblatt ausgeführt werden und diese Werte addiert werden. Ein teil der Formel (bei Blatt Nummer 116) ist hier zu sehen:
Also haben wir eine andere Lösung gesucht.
Und: mit Verlaub: ich bin nicht sicher, ob die fast 100 Monster-SUMMEWENNS auf dem Tabellenblatt die Datei mit den 200 Blättern nicht in die Knie gezwungen hätte …
Das ist mir ja noch nie aufgefallen:
In einer Datei habe ich 26 Tabellenblätter, die A, B, C, … Z heißen.
Von einem weiteren Blatt aus greife ich darauf zu:
Erstaunlicherweise wird der Blattnamen C in Apostroph gesetzt: ‚C‘. Ebenso ‚R‘, ‚S‘ und ‚Z‘.
Klar: row und column – diese beiden Begriffe sind geschützt. Das ist wichtig zu wissen, wenn man per Programmierung den Tabellenblattnamen aus der Formel =A!A1 und=’C‘!A1 herauslöst.
Lieber Rene,
ich habe mal wieder eine Aufgabe (für Sie bestimmt eine Kleinigkeit), die ich als PDF-Formular nicht vollständig gelöst bekomme, aber auch als EXCEL-„Formular“ möglicherweise zu lösen wäre:
„Ich sende an unsere Partner ein Formular mit 6 Dekor-Beispielen. Jedes der 6 Dekor-Beispiele soll mit einer Note von 1 – 6 bewertet werden.
Dabei soll jede Note nur einmal vorkommen, sodass eine eindeutige Bewertungs-Reihenfolge entsteht.
Bis jetzt habe ich 6 kleine Zahlengruppen (von 1 – 6) jeweils mit Optionsfeldern belegt.
Wie kann ich verhindern, dass z.B. das Dekor 2 und das Dekor 5 versehentlich beide mit der Note 1 bewertet werden?
…oder anders herum, versehentlich 2 Bewertungen in einer Zahlengruppe eingegeben werden
Ich würde mich riesig freuen, wenn Sie mir hierzu einen Lösungstipp auch in EXCEL geben könnten.
Ganz herzlichen Dank vorab
Christoph
####
Hallo Christoph,
ich würde in jeder Zelle mit der Datenüberprüfung die Werte 1 … 6 zulassen. Dann würde ich mit einer bedingten Formatierung die Zellen rechts neben jeder Zelle überprüfen, ob einer der Werte dem Wert der Zelle entspricht. Beispielsweise mit:
=UND(ODER(A1=B1:F1);A1<>““)
Volker Pagel ist sehr irritiert. Er hat lange gesucht, um die Ursache für folgende Inkonsequenz zu finden.
In einer Liste befinden sich Kostenstellennummer, beispielsweise 010, 0010 oder 000010.
Die Funktionen der Klasse ZÄHLENWENN, SUMMEWENN & co unterscheiden nicht zwischen 010, 0010 oder 00001, obwohl diese als Text vorliegen. Wahrscheinlich werden diese Texte intern als Zahlen behandelt.
Anders dagegen die Funktionen WENN, SVERWEIS, XVERWEIS, SUMMENPRODUKT & co. HIER wird klar zwischen 010 und 00010 unterschieden!
Hallo Hr Martin, ich habe heute einen Post von Ihnen gesehen und finde in leider nicht mehr ;-( wie formatiert man mit Mausklick? Vielen lieben Dank
###
Hallo Herr L.,
die Frage ist zu kurz: Sie meinen Excel? Zellformatierung? Die Tastenkombination Strg + 1? Oder die Minisymbolleiste?
Könnten Sie die Frage bitte etwas genauer formulieren?
Ich weiß auch nicht, auf welchen Post Sie sich beziehen?
lG :: Rene Martin
####
Sorry Ecxel, Mausklick auf eine Zelle, Zeile und Spalte hervorheben über bedingte Formatierung
####
Hallo Herr L.,
den Trick habe ich auch irgendwo gelesen – er war nicht von mir.
Wenn sich der Cursor IN der Zelle befindet, kann man das mit
=ODER(ZELLE(„ZEILE“)=ZEILE();ZELLE(„SPALTE“)=SPALTE())
hervorheben. Man sollte allerdings die Eingabe mit dem grünen Haken beenden.
Liebe Grüße
Rene Martin
Sehr geehrter Herr Martin,
wieder eine Frage:
Mit der Funktion LINKS kann man ja von links her einen bestimmten Text ausgeben.
Beispiel italienische Gerichte mit mehreren Namen, wobei nur der erste Name ausgegeben werden soll:
Der Versuch, mit der Funktion RECHTS, also das jeweils nur letzte Wort anzeigen zu lassen, hat nicht funktioniert. Geht das überhaupt?
####
Mit RECHTS können Sie nur sehr umständlich das letzte Wort ermitteln. Verwenden Sie: TEXTNACH mit dem Parameter -1:
=TEXTNACH(„Rene Bernd Martin“;“ „;-1)
Hurra – ich habe die Funktion (genauer: die Hilfe) auch!
Während man früher einen Teil einer Formel markieren und dann mit [F9] sich das Ergebnis anzeigen lassen konnte, ist es jetzt möglich lediglich einen Formelteil zu markieren – ein Quickinfo zeigt den berechneten Wert an:
Außerdem, so meint er, kann die Anzeige ein fehlerhaftes Ergebnis anzeigen, wenn falsch markiert wurde (das konnte bei [F9] allerdings auch schon passieren):
Korrekt wäre (hätte man die 31 und nicht die 1 markiert):
Guten Tag Herr Martin
Ich habe mal eine Funktion gesehen, ich meinte, diese haben sie mir sogar gezeigt, wie man so ein Fenster anzeigen kann, wo diese Informationen ohne VBA ersichtlich sind.
Ich prüfe aktuell ein Excel mit gefühlt 10 Mio Formeln 😉 und wollte die Grösse ermitteln, damit ich danach sagen kann wie viel ich kontrollieren konnte.
Freundliche Grüsse
####
Hallo Herr Schönenberger,
Sie haben nach einer „Funktion“ gefragt.
Haben Sie den Inquire? Schauen Sie mal in Com-Add-Ins nach.
Damit ist die Analyse schnell möglich:
Danke an XLarium – oder natürlich über die Arbeitsmappenstatistik
Sehr geehrter Herr Martin,
ich bin gerade am Experimentieren.
Ich habe an dem Beispiel die ersten drei Monate von 2023 genommen und die jeweilige Kalenderwoche eingefügt.
Ich habe aber keine Idee, wie ich auch das Datum links neben Montag rot formatieren kann.
Hätten Sie eine Idee? Vielleicht eine solche, wo man alle drei Zellen: Datum, Wochentag (hier der Montag) und KW-Zahl zugleich fett und rot formatieren könnte.
Hallo Herr F.,
mit der Funktion WOCHENTAG können Sie das Problem lösen.
Verwenden Sie bei WOCHENTAG den Parameter 2, dann ist Wochentag(Datum;2) = 1 bei Montag.
Liebe Grüße Rene Martin
Die Kamera ist sicherlich bekannt. Mit ihrer Hilfe kann man einen dynamischen Snapshot auf einen Teil der Excelmappe herstellen.
Christian Gröblacher hat mich darauf hingewiesen, dass zu viele Fotografien eine Excelmappe jedoch enorm verlangsamen können. Also: Vorsicht ist geboten!
Danke für diesen guten Tipp, Christian.
ChatGPT verblüfft. Ich habe etwas gewühlt.
was bedeutet die Zellformatierung ;;; in Excel?
lautet meine Frage. Die Antwort:
Wenn du beispielsweise eine Zahl eingibst, die mit einem Nullenprefix beginnt (z.B. „01234“), würde Excel sie automatisch als Zahl formatieren und die Nullen entfernen. Wenn du die Formatierung „;;;“ verwendest, wird die Zahl als Text behandelt und die Nullen bleiben erhalten.
Um die Zellformatierung „;;;“ in Excel zu verwenden, kannst du die Zelle auswählen und dann auf den „Zahlenformatierungs-Typ“ klicken, der sich in der Registerkarte „Start“ im Bereich „Zahlenformatierung“ befindet. Du kannst auch die Tastenkombination „STRG + 1“ verwenden, um die Zahlenformatierung aufzurufen, und dann „Text“ auswählen.
Nein – das kann ich so nicht durchgehen lassen.
Nächste Frage:
In Microsoft Excel kannst du den Nachnamen aus einem vollen Namen extrahieren, indem du eine Kombination aus Textfunktionen verwendest. Hier sind einige Möglichkeiten, wie das funktionieren kann:
Die Funktion "RECHTS": Die Funktion "RECHTS" kann verwendet werden, um eine bestimmte Anzahl von Zeichen von der rechten Seite einer Zeichenfolge zu extrahieren. Hier ist ein Beispiel:=RECHTS(A2,LEN(A2)-FIND(“ „,A2))
Diese Formel sucht den Leerraum im Namen und berechnet die Anzahl der Zeichen bis zum Leerraum. Die Funktion „RECHTS“ extrahiert dann die gleiche Anzahl von Zeichen vom Ende des Namens.
Die Funktion "TEIL": Die Funktion "TEIL" kann verwendet werden, um einen Teil einer Zeichenfolge anhand eines bestimmten Zeichens zu extrahieren. Hier ist ein Beispiel:=TEIL(A2,FIND(“ „,A2)+1,LEN(A2))
Diese Formel sucht den Leerraum im Namen und extrahiert alles nach dem Leerraum als Nachnamen.
Die Funktion "TEXTVOR": Die Funktion "TEXTVOR" kann verwendet werden, um eine bestimmte Anzahl von Zeichen von einer Zeichenfolge zu entfernen. Hier ist ein Beispiel:=TEXTVOR(A2,“ „)
Diese Formel sucht den Leerraum im Namen und entfernt alles nach dem Leerraum als Vornamen.
Okay, schon ganz gut, denke ich. Aber: bitte alles auf Deutsch (nicht LEN) und bitte Semikolon statt Komma!
wie ermittle ich die Anzahl der Sonntage eines Monats in Excel?
will ich wissen. Und erhalte die Antwort:
In Microsoft Excel kannst du die Anzahl der Sonntage eines Monats mit einer Kombination aus Funktionen berechnen. Hier ist ein Beispiel, wie das geht:
Verwende die Funktion "NETZWERKDIENSTAG" für das erste Datum des Monats:=NETZWERKDIENSTAG(DATE(Jahr,Monat,1))
In dieser Formel:
"Jahr" ist das Jahr, für das du die Anzahl der Sonntage berechnen möchtest
"Monat" ist der Monat, für den du die Anzahl der Sonntage berechnen möchtest
Verwende eine Schleife, um die Anzahl der Sonntage für jeden Tag des Monats zu berechnen:=SUMME(WENN(NETZWERKDIENSTAG(DATE(Jahr,Monat,{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31}))=1,1,0))
In dieser Formel:
Die Funktion "WENN" überprüft, ob jeder Tag ein Sonntag ist
Die Funktion "SUMME" addiert schließlich die Anzahl der Sonntage zusammen.
Uff – was bitte ist NETZWERKDIENSTAG ?
Auch die Antwort auf die Frage, wie man eine Quersumme berechnen kann, liefert ein kurioses Ergebnis:
In Microsoft Excel kannst du die Quersumme einer Zahl berechnen, indem du die Zahl in eine Zeichenfolge umwandelst und dann jede Ziffer einzeln addierst. Hier ist ein Beispiel, wie du das mit einer Formel tun kannst:
less
=SUMME(WENNTEIL(Zahl,ROW(INDIREKT(„1:“ & LÄNGE(Zahl))))=““,0,WENNTEIL(Zahl,ROW(INDIREKT(„1:“ & LÄNGE(Zahl))))))
In dieser Formel:
"Zahl" ist die Zahl, deren Quersumme berechnet werden soll
Die Funktion "LÄNGE" bestimmt die Anzahl der Zeichen in der Zahl
Die Funktion "INDIREKT" und "ROW" erstellen eine Zeichenfolge mit der Anzahl der Zeichen in der Zahl
Die Funktion "WENNTEIL" teilt die Zahl in einzelne Zeichen auf
Die Funktion "SUMME" addiert schließlich alle Zeichen zusammen, um die Quersumme zu berechnen.
WENNTEIL würde ein Schmunzeln hervorrufen – ein Anwender wird sicherlich verärgert sein, weil er überhaupt nicht weiß, wie man diese Formel umbauen kann/muss, damit sie ein korrektes Ergebnis liefert.
ChatGPT irritiert schon sehr. Haben Sie es schon ausprobiert. Hier einige Stilblüten. Ich will wissen
Wie kann ich mit VBA überprüfen, ob eine Zelle eine Datenüberprüfung besitzt?
Die Antwort:
Sie können in VBA überprüfen, ob eine Zelle eine Datenüberprüfung besitzt, indem Sie folgenden Befehl verwenden:
If Range(„A1“).Validation.Type <> 0 Then
‚ Code ausführen, wenn die Zelle eine Datenüberprüfung hat
End If
In diesem Beispiel wird die Eigenschaft „Validation.Type“ verwendet, um zu überprüfen, ob die Zelle „A1“ eine Datenüberprüfung besitzt. Wenn die Überprüfung vorhanden ist, wird die Eigenschaft „Validation.Type“ ungleich 0 sein, und der Code innerhalb der If-Abfrage wird ausgeführt.
Nein, liebes ChatGPT, das ist falsch.
Wenn auf einer Zelle keine Datenüberprüfung liegt, liefert Range(„A1“).Validation.Type einen Fehler. Diesen kann man abfangen (On Error Resume Next) und dann den Wert der Err-Variable ermitteln.
Würde jemand dein Beispiel ausprobieren, wäre er oder sie frustriert und verärgert, weil es einen Fehler liefert.
Auch folgende Antwort erstaunt:
Gib mir fünf Lösungen, um die Summe der Zahlen in Spalte F zu berechnen
Hier sind fünf Lösungen, um die Summe der Zahlen in Spalte F in Microsoft Excel zu berechnen:
Verwenden Sie die SUM-Funktion:=SUM(F1:F10)
Verwenden Sie die AutoSum-Funktion:
Klicken Sie auf die Zelle unter der letzten Zelle in Spalte F und drücken Sie die Tastenkombination "Strg + Alt +".
Verwenden Sie die Formel SUMME:=SUMME(F1:F10)
Verwenden Sie die SUM-Formel mit einer festen Zellenbereichsadresse:=SUM(F1,F2,F3,F4,F5,F6,F7,F8,F9,F10)
Verwenden Sie die SUM-Formel mit einer bedingten Formatierung:
Wählen Sie die Zellen in Spalte F aus und verwenden Sie die Funktion "Bedingte Formatierung". Wählen Sie dann die Option "Formel" und geben Sie "=ISTZAHL(F1:F10)" ein. Die Zellen, die Zahlen enthalten, werden automatisch markiert. Verwenden Sie dann die SUM-Formel und beschränken Sie die Zellenbereichsadresse auf die markierten Zellen.=SUM(F1:F10)
Hinweis: In diesen Beispielen wird davon ausgegangen, dass die Daten in Spalte F in den Zellen F1 bis F10 enthalten sind. Stellen Sie sicher, dass Sie die Zellenadressen entsprechend anpassen, wenn Ihre Daten anders angeordnet sind.
Okay – das sind fünf Antworten (auch wenn die automatische Nummerierung versagt), aber sehr ähnliche. Ich hatte auf TEILERGEBNIS, AGGREGAT, … gehofft. Tja …
Und: einmal SUM, einmal SUMME zu schreiben sind eigentlich keine zwei Lösungen. Schließlich: liebes ChatGPT – bitte verwende doch Semikolon als Trennzeichen.
Vielleicht sollte ich die Antwort regenerieren lassen.
In letzter Zeit wird viel über ChatGPT diskutiert. Ich habe es mir angesehen und bin nicht sehr glücklich über die Antworten, die ich dort erhalte.
Ich will Folgendes wissen:
was ist der Unterschied zwischen SVERWEIS und XVERWEIS?
Und erhalte die Antwort:
Der Unterschied zwischen „SVERWEIS“ und „XVERWEIS“ liegt darin, wie sie mit fehlenden oder mehreren Übereinstimmungen umgehen.
„SVERWEIS“ gibt immer den ersten Wert zurück, der dem Suchkriterium entspricht, auch wenn es mehrere Übereinstimmungen gibt. Wenn kein Wert gefunden wird, gibt „SVERWEIS“ den Fehlerwert „#NV“ (Nicht verfügbar) zurück.
„XVERWEIS“ kann jedoch so konfiguriert werden, dass es bei mehreren Übereinstimmungen den ersten, den letzten oder einen bestimmten Wert zurückgibt. Wenn kein Wert gefunden wird, kann „XVERWEIS“ auch einen alternativen Wert zurückgeben, den Sie angeben, anstatt einen Fehler auszulösen.
Zusammenfassend ist „SVERWEIS“ schneller und einfacher zu verwenden, während „XVERWEIS“ mehr Flexibilität und Kontrolle bei der Verarbeitung von Suchergebnissen bietet.
Okay – diese Antwort ist richtig, aber XVERWEIS weist noch weitere Fähigkeiten auf, die ich bin SVERWEIS vermisse: Platzhaltersuche, matrixfähig, Standard die exakte Suche, die Spalte, in der die Werte stehen, die zurückgegeben werden, kann auch VOR der Suchespalte stehen, …)
Der Kommentar, dass „SVERWEIS“ schneller und einfacher zu verwenden,“ ist, halte ich für bedenkenswert.
Kennt ihr ChatGPT? Auf wikipedia ist zu lesen:
ist der Prototyp eines Chatbots, also eines textbasierten Dialogsystems als Benutzerschnittstelle, der auf maschinellem Lernen beruht. Den Chatbot entwickelte das US-amerikanische Unternehmen OpenAI, das ihn im November 2022 veröffentlichte.
Und weiter:
Der Chatbot wurde mithilfe zweier Arten des sogenannten bestärkenden Lernens trainiert: RLHF (Reinforcement Learning from Human Feedback) und PPO (Proximal Policy Optimization[4][5]). Er nutzt zur Textein- und -ausgabe das Sprachmodell GPT-3.5 (GPT steht für Generative Pre-trained Transformer), eine verbesserte Version von GPT-3, die ebenfalls von OpenAI stammt. GPT basiert auf Transformern, ein von Google Brain vorgestelltes Maschinenlernmodell.
Klingt spannend. Ich melde mich an – das ist kostenlos.
Ich probiere aus – die Seite soll ja sehr gut Antworten zu Excel liefern – auf Englisch und auf Deutsch. Erster Versuch:
How to use xlookup in Excel?
Die Antwort:
XLOOKUP is a function in Microsoft Excel that allows you to search for a value in a table and return a corresponding value from a specified column. The syntax for XLOOKUP is:
=XLOOKUP(lookup_value, lookup_array, return_array, [default_value])
lookup_value: The value you want to search for in the lookup_array.
lookup_array: The range of cells that contains the data you want to search.
return_array: The range of cells that contains the data you want to return.
default_value: The value to be returned if the function does not find a match (optional).For example, if you have a table of data in columns A and B, with the names in column A and the corresponding ages in column B, you can use XLOOKUP to find the age of a specific person by using the following formula:
=XLOOKUP(„John“, A1:A5, B1:B5)
This will search for „John“ in column A, and return the corresponding value in column B.
Note: XLOOKUP function is available starting with Excel version 365, if you are working with an older version of Excel you may consider using INDEX and MATCH function or VLOOKUP.
rene.martin@compurem.de
how can I find an information in Column A in Excel with VBA?
Ich versuche es auf Deutsch:
Erkläre mit, was XVERWEIS in Excel macht
Die Antwort:
„XVERWEIS“ ist eine Funktion in Microsoft Excel, die eine Verweisoperation durchführt. Es ermöglicht es Ihnen, Werte aus einem anderen Tabellenbereich oder einer anderen Arbeitsmappe zu suchen und in Ihre aktuelle Tabelle einzufügen. Die Syntax von XVERWEIS lautet wie folgt: XVERWEIS(Suchkriterium, Bereich, [Spalte], [exakt]). „Suchkriterium“ ist der Wert, nach dem Sie suchen möchten. „Bereich“ ist der Bereich, in dem Sie suchen möchten. „Spalte“ ist optional und gibt an, in welcher Spalte Sie suchen möchten. „Exakt“ ist auch optional und legt fest, ob eine exakte Übereinstimmung gefunden werden soll oder nicht.
Ich reibe verwundert die Augen. Ich frage nach XVERWEIS und erhalte eine Erklärung zum SVERWEIS. Nein, liebes ChatGPT – DA musst du noch einmal ran!
Die Teilnehmerin in der Excelschulung möchte wissen, wie man eine Formel wieder im Funktionsassistenten anzeigen lassen kann. Beispielsweise den XVERWEIS:
Erstaunlicherweise liefert ein Klick auf f(x) nicht den Dialog, sondern das Ergebnis, was nicht gewollt ist:
Die Ursache ich schnell gefunden: die Formel besteht nicht nur aus einer (ineinandergeschachtelten) Funktion, sondern aus einer Funktion und einer Berechnung. DAS kann nicht im Funktionsassistenten angezeigt werden.
Aber man kann die Funktion innerhalb der Formel markieren und dann mit f(x) im Funktionsassistenten anzeigen lassen:
Excelschulung
„Mit welcher Funktion“, will eine Teilnehmerin wissen, kann man die Änderung einer Zelle protokollieren. Das heißt: „wie kann man ermitteln, um welchen Wert eine Zelle geändert wurde.“
„Schöne Frage“, denke ich und antworte: „Das geht mit Excel-Formeln leider nicht. Das müsse man per Programmierung ermitteln. Beim Ändern einer Zelle wird der alte Wert mit dem neuen Wert verglichen.“
Hallo Herr Martin,
können Sie mir als Excel-Virtuose beim Umgang mit intelligenten Tabellen helfen? Es handelt sich um folgende Sache:
Idee: Anwenden der bedingten Formatierung. Hierbei habe ich schnell festgestellt, dass mir der Ansatz gänzlich fehlt –> an welche „Variable“ od. „Konstante“ binde ich die Formatierung, damit ich einfach eine normale intelligente Tabelle erhalte, bei der sich die Hintergrundfarben unterschiedlich vieler Spalten abschnittsweise unterscheiden, die sich zusätzlich mitverändern, sollte man filtern oder neue Zeilen an unterschiedlicher Stelle hinzufügen
Ich hoffe, ich konnte es einigermaßen verständlich machen, woran es bei mir scheitert. Weiterhin hoffe ich, dass Sie Zeit und Lust haben, sich mit dieser Problematik zu befassen.
Mit freundlichen Grüßen
Marcel
PS: Ihr Forum „Excel nervt …“ ist mega unterhaltsam und hilfreich und befasst sich verständlich mit atypischen Fragen im Umgang mit dem Programm. Einfach genial. Hat mir sehr gefallen!
#####
Hallo Herr Gröschel,
1.) ich würde nicht mehrere intelligente Tabellen verwenden. Das widerspricht dem Gedanken der Tabellen.
2.) Ich habe nachgeschaut: es geht nicht mit dem Stripset. DAS ist recht einfach aufgebaut und erlaubt nicht so viele Varianten, wie Sie es gerne hätten.
3.) Warum nicht bedingte Formatierung? Setze ich auch gerne ein. Beispielsweise um eine Zeile farblich zu hinterlegen.
Die Formel (bspw.):
=UND(SPALTE()>=11;SPALTE()<=14)
Hilft das?
Liebe Grüße
Rene Martin
####
Guten Abend Herr Martin,
vielen Dank, dass Sie so schnell geantwortet haben! Ihre Formel hat mir grundsätzlich weitergeholfen. Damit konnte ich das Problem der verschiedenen Spaltenfarben in meiner Tabelle lösen. Es war simpel und genial.
Dennoch blieb das Problem der farblichen Abgrenzung zwei aufeinanderfolgender Zeilen bestehen. Zuerst probierte ich es mit:
=REST(ZEILE();2)
Hat soweit gut funktioniert, bis ich dann die Filterfunktion der intelligenten Tabelle verwendet habe. Im Ergebnis unterschied das Programm nicht zwischen eingeblendeten und ausgeblendeten Zeilen.
Basierend darauf kam ich durch weitere Recherche zur Ziellösung:
=REST(TEILERGEBNIS(3;$B$5:$B5);2)=0
(wobei die erste Spalte meiner Tabelle in Blattspalte B beginnt und ab Zeile 5 stets einen Wert beinhaltet)
Hierbei ist zu beachten, dass der gewünschte Effekt bzw. konkret die gewünschte farbliche Formatierung in der gesamten Tabelle nur dann funktioniert, wenn in Spalte B, in jeder Zelle der Tabelle auch ein beliebiger Wert steht. In meinem Fall befinden sich hier jeweils die Bezeichnungen.
Das wiederum bedeutet, dass beim Hinzufügen einer neuen Zeile vorerst nicht der gewünschte Effekt eintritt, sondern erst nach befüllen der neuen Zelle in Spalte B. Es ist unterm Strich noch nicht perfekt, aber es lässt sich damit arbeiten.
Mit freundlichen Grüßen
Marcel Gröschel
Warum findet Excel „Bielefeld“ nicht?
Ich sehe den Text doch in Zelle B19.
Der Grund: In B19 steht nicht „Bielefeld“, sondern eine Formel:
Stimmt – ich muss die Option „Suchen in“ umschalten von „Formeln“ auf „Werte“. Dann wird auch „Bielefeld“ gefunden:
excel 😀
Hallo Herr Martin, ich verzweifle an einer wahrscheinlich simplen Excel Aufgae im Zuge meines Onboardings.
1) Fill in the question mark: 2,7,17,?,41,53,67,79
Das ist die Aufgabe. Ich gehe davon aus, dass es eine Art Logiktest ist, und excel die möglichkeit besitzt das ? durch die korrekte Zahl zu ersetzen?
Haben Sie hier ggf. einen Tipp für mich? Ich wäre Ihnen sehr dankbar und Sie würden qusi meine Woche retten. 😀
Alles Gute für Sie.
Viele Grüße aus Hamburg
Hallo Frau F.,
die Lösung lautet 29.
Was bekomme ich für die Erklärung?
Hahahaha 😀 meine Hochachtung!
Und wie ist die Excel Formel dazu? Dann wären Sie mein Held des Tages!!!
keine Excel-Formel.
Werfen Sie mal einen Blick auf die ersten Primzahlen:
2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 31, 37, 41, 43, 47, 53, 59, 61, 67, 71, 73, 79, 83, 89, 97.
Und nun streichen Sie jeweils zwei weg …
Verrückt! Mit Zahlen habe ich es leider nicht so, aber das sieht sehr logisch aus!
Da das nun aber eine >Excel Skill Aufgabe ist. Frage ich mich, was möchte der Prüfer hier von mir. Irgendwas mit Excel muss die Frage doch zu tun haben.-
Zumal Sie in einem Excel Sheet gestellt ist
Hallo Frau F.,
erzeugen Sie zu den Werten ein Diagramm und lassen sich eine Trendlinie anzeigen. Der polynomische Trend approximiert am besten. Auch hier lautet die Lösung ungefähr 29.
Schade. Die Funktion SORTIERENNACH listet beim ersten Parameter „Sortierreihenfolge1“ die beiden Möglichkeiten „Aufsteigend“ und „Absteigend“ auf.
Beim zweiten Parameter „Sortierreihenfolge2“ leider nicht mehr – hier muss man sich die Zahl des ersten gemerkt haben:
Ich vergesse es immer wieder: die Arrayfunktionen kann man leider nicht per Doppelklick auf das Kästchen herunterziehen:
Will man Formeln in Werte verwandeln, sind mehrere Schritte nötig:
Hierfür sind mehrere Klicks nötig. Etwas schneller geht es mit der Tastenkombination [Strg] + [Alt] + [V] – damit wird der Dialog „Inhalte einfügen“ aufgerufen. [Alt] + [W] (für Werte) und mit [Enter] bestätigen.
Damit Excel etwas weniger nervt.
Randbemerkung: Man kann auch den Bereich mit der rechten Maustaste verschieben und wieder an gleicher Stelle fallen lassen und anschließend die Option „Hierhin nur als Werte kopieren“ auswählen:
Hallo Herr Martin,
ZÄHLENWENN (und SUMMEWENN) kenne ich. Damit kann ich die Anzahl der Zeilen bestimmen, die einem Kriterium genügen. ZÄHLENWENNS kenne ich auch. Damit kann ich zählen, wie oft mehrere Kriterien erfüllt sind, die mit einem logischen UND verknüpft sind.
Soweit so gut.
Aber wie kann ich eine Formel erstellen (gerne auch mit SUMMENPRODUKT), in der die Zeilen gezählt werden, bei denen in der ersten oder zweiten oder dritten Spalte nichts steht. Also: mindestens eine Spalte muss gefüllt sein.
Hallo Frau S.
ich würde die die drei Zellen verketten und wenn alle drei nicht leer sind, also <>““, dann würde ich diese zählen. Da Falsch = 0 und Wahr = 1, kann man die Summe verwenden.
beispielsweise so
=SUMME((C4:C15&D4:D15&E4:E15<>"")*1)Hallo Herr Martin,
vielen Dank für die pragmatische Lösung, gefällt mir 😉
„Wo finde ich denn die Fußnoten in Excel?“, fragt mich eine Dame, die ich in puncto Excel berate.
Die gibt es nicht in Excel. Sie zeigt mir ihre Datei, die etwa so aussieht:
„Ich habe hier Sternchen eingefügt“, erklärt sie mir. Die kann ich ja auch hochgestellt formatieren.“
Ich nicke mit dem Kopf.
„Ob man denn auch bei Zahlen ein hochgestelltes Sternchen hinzuformatieren könne“, möchte sie wissen. „Nein – Zahlen sind ein Objekt“, lautet meine Antwort, „in Excel kann man nicht beispielsweise bei der Uhrzeit 1200 die beiden 00 hochstellen. Geht nicht!“
„Und bei Formeln?“, fragt sie:
Beispielsweise
=Vorjahr!D45&"*"Meine Antwort: „Das geht leider auch nicht!“ Eine Formel kann eine Zahl liefern, aber keine formatierte Zahl. Ich kann die GANZE Zelle formatieren, aber leider nicht einen Teil des Ergebnisses der Formel.“
Sie bedauert.
Als ich argumentiere: „Aber PowerPoint hat auch keine Fußnoten“, antwortet sie: „aber dort kann ich einfach in die Texte Sternchen einfügen.“ Stimmt: PowerPoint kennt auch keine Zahlen wie Excel und keine Formeln.
In Excel kann man, wenn die Berechnungsoptionen auf „manuell“ gestellt sind, die ganze Datei neu berechnen lassen oder ein Blatt:
Und was fragt die Teilnehmerin in der letzten Excelschulung:
„Kann ich auch alle Blätter neu berechnen lassen, aber nicht das aktuelle? Weil dort sehr viele Formeln stehen – DIESE Aktualisierung ist sehr zeitaufwändig.“
Ich wüsste nicht wie (außer natürlich mit einem Makro).
Reguläre Ausdrücke – sie sind so klasse – warum mag Microsoft sie nicht?
Die Aufgabe: Extrahieren Sie aus zirka 30.000 Zellen einer Exceltabelle die darin befindlichen Datumsinformationen. Es finden sich Texte wie:
Maistraße 17 (St. 29.03.2016), Flurnummer-alt: 47/11
Bahnhofstraße 1 – 17.3.2022 – Flurnummer-alt: 08/15
Hirtenweg 3A 05-2005 – Garagen, Flurnummer-alt: 00/77
Ich überlege: Formeln wären eine Option. Aber sehr umständlich.
VBA wäre gut.
PowerQuery auch – kennt aber keine regulären Ausdrücke.
Ich entscheide mich für VBA.
Dort muss man einen Verweis auf „Microsoft VBScript Regular Expressions“ einbinden. Oder diese Klasse mit
Set regex = CreateObject("vbscript.regexp")aufrufen. Dann kann man definieren:
' -- Muster: ***01.01.2022***
strMuster1 = ".*\d{1,2}\.\d{1,2}\.\d{2,4}.*"
strMuster1_Raus = "\d{1,2}\.\d{1,2}\.\d{2,4}"
Und kann nun extrahieren:
regex.Pattern = strMuster1
regexRaus.Pattern = strMuster1_Raus
regexRaus.Global = True
For i = 1 To ThisWorkbook.Worksheets(1).Range("A1").CurrentRegion.Rows.Count
If regex.Test(Range("I" & i).Value) = True Then
Set strTreffer = regexRaus.Execute(Range("I" & i).Value)
j = 0
For Each strFund In strTreffer
strTemp = strFund
If IsDate(strTemp) Then
If Len(Split(strTemp, ".")(2)) = 3 Or Len(Split(strTemp, ".")(2)) = 1 Then
Range("Q" & i).Offset(0, j).Value = strTemp
Range("Q" & i).Offset(0, j).Interior.Color = vbRed
Else
Range("Q" & i).Offset(0, j).Value = CDate(strTemp)
If Year(CDate(strTemp)) > Year(Date) Then
Range("Q" & i).Offset(0, j).Interior.Color = vbRed
End If
End If
Else
Range("Q" & i).Offset(0, j).Value = strTemp
Range("Q" & i).Offset(0, j).Interior.Color = vbRed
End If
j = j + 1
Next
End If
NextVier andere Varianten werden analog abgearbeitet. Klappt.
Warum hat VBA nicht als Standard Regex eingebunden?
Warum kennt PowerQuery keine regulären Ausdrücke?
Warum kann man keine regulären Ausdrücke beim Autofilter oder Spezialfilter eingeben?
Nachtrag: Ich habe etwas gewühlt. Imke Feldmann beschreibt, wie man über JavaScript einen Zugriff auf RegEx erhält:
Trotzdem: ich ziehe hier VBA vor.
Und: vor einigen Jahren hatte ich eine XML-Schulung, in der ich die regulären Ausdrücke vorgestellt hatte. Die Teilnehmerinnen kannten sie, waren damit vertraut, arbeiteten in „anderen Welten“ damit und waren begeistert. Sie wollten sich sogar T-Shirts mit dem Aufdruck „I ♥ RegEx“ drucken lassen. Haben sie aber doch nicht.
Hallo Herr Martin,
Und noch gleich eine Frage hinterher, s. Anhang.
Hallo Frau I.,
Sie können die Spannweite als Text darstellen lassen, beispielsweise so:
=I11&“ – „&I12
Oder Sie können die Zahlen auch formatiert darstellen:
=TEXT(I11;“#.##0,00″)&“ – „&TEXT(I12;“#.##0,00“)
Liebe Grüße
Rene Martin
Lieber Herr Martin,
nun haben sich bezüglich der Kriterien- „=“-Auswahl einige Fragen ergeben.
Zur Erinnerung: Sie hatten uns Excel-Dokumente erstellt, in denen unterschiedliche Funktionen kombiniert wurden, damit bestimmte Filterungen auf ein eingelesenes Exceldokument ausgeführt werden können.
Zu den Fragen:
Hallo Frau I.,,
Habe ich Sie richtig verstanden: Sie möchten eine Mehrfachauswahl mit ODER.
Das Multiplikationszeichen entspricht dem logischen UND; das Additionszeichen den logischen ODER.
Also so:
=WENNFEHLER(FILTER(qry_Datenzugriff_SpaltenAuswahl;
(WENN(ODER(A2=““;B2=““);SEQUENZ(ZEILE(qry_Datenzugriff_SpaltenAuswahl);1;1;0);INDIREKT(„qry_Datenzugriff_SpaltenAuswahl[„&A2&“]“)=B2))+
WENN(ODER(A3=““;B3=““);SEQUENZ(ZEILE(qry_Datenzugriff_SpaltenAuswahl);1;1;0);(INDIREKT(„qry_Datenzugriff_SpaltenAuswahl[„&A3&“]“)=B3))+
WENN(ODER(A4=““;B4=““);SEQUENZ(ZEILE(qry_Datenzugriff_SpaltenAuswahl);1;1;0);(INDIREKT(„qry_Datenzugriff_SpaltenAuswahl[„&A4&“]“)=B4))+
WENN(ODER(A5=““;B5=““);SEQUENZ(ZEILE(qry_Datenzugriff_SpaltenAuswahl);1;1;0);(INDIREKT(„qry_Datenzugriff_SpaltenAuswahl[„&A5&“]“)=B5))+
WENN(ODER(A6=““;B6=““);SEQUENZ(ZEILE(qry_Datenzugriff_SpaltenAuswahl);1;1;0);(INDIREKT(„qry_Datenzugriff_SpaltenAuswahl[„&A6&“]“)=B6))+
WENN(ODER(A7=““;B7=““);SEQUENZ(ZEILE(qry_Datenzugriff_SpaltenAuswahl);1;1;0);(INDIREKT(„qry_Datenzugriff_SpaltenAuswahl[„&A7&“]“)=B7))+
WENN(ODER(D2=““;E2=““;F2=““);SEQUENZ(ZEILE(qry_Datenzugriff_SpaltenAuswahl);1;1;0);(INDIREKT(„qry_Datenzugriff_SpaltenAuswahl[„&D2&“]“)>=E2)*(INDIREKT(„qry_Datenzugriff_SpaltenAuswahl[„&D2&“]“)<=F2))*
WENN(ODER(D3=““;E3=““;F3=““);SEQUENZ(ZEILE(qry_Datenzugriff_SpaltenAuswahl);1;1;0);(INDIREKT(„qry_Datenzugriff_SpaltenAuswahl[„&D3&“]“)>=E3)*(INDIREKT(„qry_Datenzugriff_SpaltenAuswahl[„&D3&“]“)<=F3))*
WENN(ODER(D4=““;E4=““;F4=““);SEQUENZ(ZEILE(qry_Datenzugriff_SpaltenAuswahl);1;1;0);(INDIREKT(„qry_Datenzugriff_SpaltenAuswahl[„&D4&“]“)>=E4)*(INDIREKT(„qry_Datenzugriff_SpaltenAuswahl[„&D4&“]“)<=F4))*
WENN(ODER(D5=““;E5=““;F5=““);SEQUENZ(ZEILE(qry_Datenzugriff_SpaltenAuswahl);1;1;0);(INDIREKT(„qry_Datenzugriff_SpaltenAuswahl[„&D5&“]“)>=E5)*(INDIREKT(„qry_Datenzugriff_SpaltenAuswahl[„&D5&“]“)<=F5))*
WENN(ODER(D6=““;E6=““;F6=““);SEQUENZ(ZEILE(qry_Datenzugriff_SpaltenAuswahl);1;1;0);(INDIREKT(„qry_Datenzugriff_SpaltenAuswahl[„&D6&“]“)>=E6)*(INDIREKT(„qry_Datenzugriff_SpaltenAuswahl[„&D6&“]“)<=F6))*
WENN(ODER(D7=““;E7=““;F7=““);SEQUENZ(ZEILE(qry_Datenzugriff_SpaltenAuswahl);1;1;0);(INDIREKT(„qry_Datenzugriff_SpaltenAuswahl[„&D7&“]“)>=E7)*(INDIREKT(„qry_Datenzugriff_SpaltenAuswahl[„&D7&“]“)<=F7))
);““)
Letzte Woche habe ich eine interessante Frage erhalten. Vielleicht kann man sie mit LAMBDA lösen. Aber da er kein LAMBDA hat, habe ich eine Lösung mit Hilfsspalte gebaut. Etwas Besseres ist mir nicht eingefallen.
Die Fragestellung: in mehreren getrennt nebeneinander stehenden Reihen befinden sich Daten. Gesucht ist der Rang:
Problem 1: Die Funktion RANG setzt einen zusammenhängenden Wertebereich voraus.
Problem 2: diese Funktion ist in keiner anderen Funktion vorhanden – nicht in AGGREGAT oder TEILERGEBNIS, …
Deshalb habe ich es mit einer Hilfsspalte gelöst: Alle Werte untereinander geschrieben, sortiert und über die Funktion VERGLEICH die Position ermittelt. Mir ist nichts Eleganteres eingefallen:
Ein bisschen kniffelig war es. Ein bisschen probieren musste ich schon.
Die Aufgabe: in einer Zeile soll ein „x“ an einer oder mehreren Positionen stehen. Die erste Position (von links) soll ermittelt werden und aus einer anderen Zeile (der Überschriftszeile), der entsprechende Wert hierzu angezeigt werden.
Den Wert zu finden, ist nicht schwierig:
=VERGLEICH("x";E165:W165;0)Den zugehörigen Wert zu ermitteln, auch nicht:
=INDEX($E$162:$W$162;VERGLEICH("x";E165:W165;0))Da die leeren Zellen einen Fehler erzeugen würden, kann dieser noch abgefangen werden:
=WENNFEHLER(INDEX($E$162:$W$162;VERGLEICH("x";E165:W165;0));"")Klappt!
Allerdings: In anderen Blöcken werden Zahlen eingetragen. Welche ist die erste Spalte, in der eine Zahl steht?
Die Funktion
=VERGLEICH(">0";E111:S111;0)versagt jedoch. Allerdings … nach einigen Versuchen:
=VERGLEICH(WAHR;INDEX(ISTZAHL(E109:W109);0);0)funktioniert! Und man kann die Überschrift suchen über:
=INDEX($E$108:$W$108;VERGLEICH(WAHR;INDEX(ISTZAHL(E109:W109);0);0))und schließlich:
=WENNFEHLER(INDEX($E$108:$W$108;VERGLEICH(WAHR;INDEX(ISTZAHL(E109:W109);0);0));"")Da diese Formulare dynamisch per VBA erzeugt werden, stellt es nun keine große Herausforderung mehr dar, diese in VBA-Code umzuwandeln:
.FormulaR1C1 = "=IFERROR(INDEX(R" & intAktuelleZeilenNummer & "C5:R" & intAktuelleZeilenNummer & "C23,MATCH(""x"",RC[-20]:RC[-2],0)),"""")"und analog:
.FormulaR1C1 = "=IFERROR(INDEX(R" & intAktuelleZeilenNummer & "C5:R" & intAktuelleZeilenNummer & "C23,MATCH(TRUE,INDEX(ISNUMBER(RC[-20]:RC[-2]),0),0)),"""")"Hi Rene
Wie geht es dir?
Du, ich muss mich verzweifelt bei dir melden mit einem Excel-Problem. Ich mache einen Import zu WordPress und der Kunde hat mir die Inhalte als Excel geliefert. Es geht um Schadbilder (Gärtner-Themen). Jedes Schadbild wird ein Artikel und sollte deshalb eine Zeile sein. Soweit so gut, jetzt der Kniff: Jeder Text hat Zwischentitel und diese sind aber als Spalten im Excel File angelegt. Also sind die verschiedenen Spalten nicht einzelne Felder in WordPress, sondern ein grosses Textfeld. Und die Spaltentitel sollten jeweils als Zwischentitel in diesen Texten zu finden sein. Die Zwischentitel sollten zudem ein HTML H-Tag erhalten und nicht einfach „fett und grösser“ sein.
Kannst du mir da vielleicht sagen, wie ich weiterkommen kann? Bitte sei ehrlich, wenn das deine Kapazitäten sprengt. Dann machen wir das manuell, das würde auch gehen, es sind um die 140 Artikel.
Ich gestehe – ich habe zuerst überlegt, dieses Problem mit TEXTVERKETTEN zu lösen. Als Trennzeichen hätte ich „</p><p>“ oder Ähnliches eingegeben. Aber irgendwie gefiel mir die Rechnerei nicht.
Warum nicht PowerQuery?
Klar: 1. Schritt: Liste in Tabelle verwandeln. Die Daten aus Tabelle/Bereich importieren:
Das Zauberwort heißt „entpivotieren“. Und schon habe ich eine Tabelle mit zwei Spalten: in der ersten steht die Überschrift, in der zweite die Daten aus den entsprechenden Tabellen:
Und das kann problemlos zu einer Spalte verkettet werden:
"<h1>" & [Attribut] & "</h1>#(lf)<p>" & [Wert] & "<p>"Die nicht mehr benötigten Spalten werden gelöscht, der Rest in Text konvertiert:
Und zurück nach Excel.
Man hätte die Zeilen in PowerQuery zu einem Wert zusammenfassen können – ich denke, es ist geschickter in Excel mit TEXTVERKETTEN zu erledigen.
Ihre Reaktion:
unglaublich, ich staune! Ich war mir sicher, dass du es kannst, wenn Excel es kann. Aber ich hab schon an Excel gezweifelt.
Danke vielmals!
Ich wohne in der Albert-Roßhaupter-Straße. Eigentlich müsste sie – nach neuer deutscher Rechtschreibung – Albert-Rosshaupter-Straße heißen. Aber der Name des SPD-Politikers wurde nicht geändert. Sei’s drum. Was passiert allerdings bei der Suche in Excel von Wörtern mit „ß“ und „ss“?
Die Funktion SVERWEIS unterscheidet; XVERWEIS allerdings nicht:
Erstaunlich! Umso erstaunlicher ist es, dass im Spanischen weder zwischen „n“ und „ñ“ unterschieden wird:
Im spanischen Excel werden auch nicht die Vokale mit und ohne Akzent unterschieden:
nicht bei BUSCARV (SVERWEIS) und nicht bei BUSCARX (XVERWEIS). Sehr erstauntlich.
Danke an Mourad Louha für diesen wertvollen Tipp!
Die Frage ist gut – die Antwort leider nicht befriedigend.
Eine Liste von Namen und eine Liste von Abteilungen.
Soweit so gut – die Zuweisung kann per Datenüberprüfung erfolgen:
Allerdings: eine Person kann für mehrere Abteilungen arbeiten (eine klassische n : m-Beziehung). Die Namen sollen untereinander aufgelistet werden. Also so:
Leider kann man über die Liste der Datenauswahl nur einen Eintrag auswählen und keine Mehrfachselektion vornehmen. Auch mit einer Formel funktioniert es nicht: „addiere zu dem vorhandenen Wert einen weiteren hinzu“ – das wäre ein klassischer Zirkelbezug.
Meine Lösung war folgende: Neben der Liste werden die einzelnen Einträge ausgewählt:
Die ausgewählten Einträge werden darunter zu einer Zeichenkette verkettet:
Die Funktion
=TEXTVERKETTEN(ZEICHEN(10);WAHR;H2:H10)löst dieses Problem.
Wichtig ist hierbei, dass der Textumbruch eingeschaltet ist, sonst sieht man den Effekt nicht.
Diese Zelle kann nun kopiert und als Wert in eine andere Zelle eingefügt werden.
Zugegeben: Nicht perfekt – aber es funktioniert!
Ich weiß, dass es schwierig ist einen Text von einer Sprache in eine andere zu übersetzen. Auch noch, wenn die Zeit drängt. Dennoch: Hilfetexte sind auch ein Aushängeschild. Für Microsoft. Die ihre Texte automatisch – besser: halbautomatisch – übersetzen. Wohl, ohne dass ein (deutschsprachiger) Mensch darüber schaut. Und so habe ich auch schon einige Male gespottet. Auch Josef reibt sich verwundert die Augen und berichtet:
„Grüß dich Rene!
Gerade hatte ich ein sehr amüsantes Gespräch mit einem Kollegen 🙂
Er wollte eine Excel Formel haben und hat – ganz vorbildlich – die Excel Hilfe konsultiert. Leider war die wenig hilfreich, also rief er mich an.
Die Formel sollte ein Datum berechnen, ausgehend von einem Startdatum + X Monate. So weit so gut…
Kollege ganz stolz: „Guck ich mach genau das, was da steht: EDATE(…“
Ich: Stop! EDATE? Das muss EDATUM heißen. Hast Du etwa eine englische Internetseite gefunden?
Kollege: Neeee! Ich bin doch nicht doof! Ist die deutsche Microsoft Hilfe Seite!
Mit EDATUM(Startdatum;Dauer) hats wunderbar funktioniert.
Erst dachte ich: Naja… maschinell übersetzter Hilfe-Artikel… wird halt der Screenshot englisch sein, schade!
Aber nein! Das Ding ist eine fröhliche Mischung aus Englisch und Deutsch 😛
Wir haben herzlich gelacht! 🙂
Vermutlich hat da jemand versucht zu übersetzen, aber dann war plötzlich Zeit für Feierabend…
Im Text steht (mehrfach) EDATE
Die Beispiel-Formel im Text hat
o Deutsche Datumsschreibweise 15.05.19
o und ein Semikolon als Trenner zwischen den Parametern
o aber als Rückgabewert ein Datum in amerikanischer Schreibweise (4/15/19)
Im Screenshot
o Ist die Formel in der Bearbeitungszeile englisch, inklusive Komma als Trenner
o Die Spaltenbeschriftungen und Monatsnamen sind aber deutsch
Unterhalb des Screenshots wirds dann ganz verrückt:
o In Schritt 3 steht „Geben Sie =EDATE(A2;B2) in Zelle C2 ein,… “
Das wird weder in einem deutschen noch in einem amerikanischen Excel funktionieren.
Entweder EDATE und Komma oder EDATUM und Semikolon
In der Excel Hilfe zur EDATUM() Funktion ist es besser. Da steht nur einmal EDATE statt EDATUM in der Überschrift, aber sonst stimmts!
Viele Grüße und bis zum nächsten (online) Excel Stammtisch!
Josef“
Diese Mischung ist auch zu finden bei:
https://support.microsoft.com/de-de/office/edatum-funktion-3c920eb2-6e66-44e7-a1f5-753ae47ee4f5
Danke, Josef, für den Beitrag!
Das ist mir ja noch nie aufgefallen.
Ich öffne über f(x) den Funktionsassistenten und trage eine Frage ein. Beispielsweise möchte ich wissen, ob Excel nervt:
Die Antwort:
„Bitte formulieren Sie Ihre Frage neu!“
Und: ich erhalte eine neue Kategorie an Funktionen: „Empfohlen“. Leider ist diese Kategorie leer.
Ein bisschen doof ist es schon:
Ich habe eine (intelligente) Tabelle.
Ich füge eine Ergebniszeile hinzu – dort wird gerechnet:
Ich entferne die Farben und wandle sie in eine Liste um:
=TEILERGEBNIS(109;Tabelle1!$E$2:$E$10)
bleiben stehen. Ebenso die Beschriftung der ersten Zelle: „Ergebnis“:
Wandelt man diese Liste nun erneut in eine Tabelle um:
ist die Formelzeile nun Teil der Tabelle.
Man kann erneut eine Ergebniszeile hinzufügen – das Ganze ist nun recht verwirrend:
Ich habe gelacht. Für die nächste Schulung, bei der mehrere Dutzend Teilnehmerinnen und Teilnehmer geschult werden sollen, hat der IT-Leiter eine Namensliste angelegt: wer aus welcher Abteilung sich für welche Schulung eingetragen hat.
Und hier ist die Unterschriftliste, sagt er und schmunzelt: ich habe doch keine Lust die Namen per Hand einzutragen. Deshalb habe ein eine kleine Formel geschrieben. Alles andere würde doch nur nerven.
Recht hat er, denke ich und lache.
Hallo ich brauch bitte mal Hilfe bei bedingter Formatierung!
Kann man wenn eine Zelle automatisch die Farbe rot erhält über die bedingte Formatierung dann da automatisch einen Buchstaben mit einfügen ?
Vielen Dank für eure Hilfe
=======
Du kannst eine bedingte Formatierung mit Hintergrundfarbe, Schriftfarbe und einem (benutzerdefinierten) Zahlenformat versehen. Beispielsweise „Rot“ – dann wird dieser Text angezeigt, wenn die Bedingung erfüllt ist.
Merkwürdig. In einer Zelle steht eine Formel:
=SUMMENPRODUKT((JAHR(Tabelle2[Aktionsstart])=2021)*(Tabelle2[Carrier für den Versand]="contoso")*(Tabelle2[Gesamtmenge]))Ich benötige die Formel und lasse sie vom Makrorekorder aufzeichnen:
ActiveCell.Formula2R1C1 = _
"=SUMPRODUCT((YEAR(Tabelle2[Aktionsstart])=2021)*(Tabelle2[Carrier für den Versand]=""contoso"")*(Tabelle2[Gesamtmenge]))"
Baue sie etwas um.
Der Kunde beschwert sich über einen Fehler. Der Grund: die Eigenschaft
Formula2R1C1
ist noch nicht in Excel 2013 verfügbar *ggrrrr*
Warum zeichnet Excel nicht
ActiveCell.FormulaR1C1 = auf?
Es fing harmlos an. Ein Anruf am Wochenende. Ob ich mal kurz helfen könne. „Worum geht es denn?“ In einer Liste tauchen Werte mehrmals auf – sie sollen auf einer Serienbrief-Seite stehen. „Machen Sie doch eine Pivottabelle“, war meine lapidare Antwort. Ganz so einfach gestaltet sich das Problem allerdings nicht.
Die Banken (aus der letzten Spalte) wiederholen sich, sie sollen gruppiert werden und zu jeder Bank alle Kunden aufgelistet werden, die bei ihr Mitglied sind. Mit weiteren Informationen.
Für das Gruppieren beginnen wir mit der Funktion EINDEUTIG, entscheiden uns aber später für eine Pivottabelle, weil man hier am leichtesten filtern kann:
Alle Kunden sollen aufgelistet werden. Warum nicht mit FILTER?
Die Formel
=FILTER(Tabelle1!$A$2:$O$12384;Tabelle1!$N$2:$N$12384=A3)tut gute Dienste:
Alle Spalten werden geliefert. Ich benötige aber nur die Namen. Also wird reduziert:
=FILTER(Tabelle1!$D$2:$D$12384;Tabelle1!$N$2:$N$12384=A3)
Allerdings sollen die Daten ja nicht untereinander, sondern in einer Zelle stehen. Also muss man die Texte verketten. Die Funktion TEXTVERKETTEN hilft hierbei:
=TEXTVERKETTEN(ZEICHEN(10);WAHR;FILTER(Tabelle1!$D$2:$D$12384;Tabelle1!$N$2:$N$12384=A3))
Das Ergebnis verblüfft. Klar – man muss noch den Textumbruch einschalten:
Herunterziehen – und wieder ein Erstaunen:
Das müssten doch mehr Namen sein! – Klar: man muss die optimale Zeilenhöhe aktivieren. Doppelklick – dann funktioniert es:
Und so wird der Rest ausgefüllt. Allerdings – bei den Währungen und Datumsangaben muss man sich noch mit der Funktion TEXT behelfen:
=TEXTVERKETTEN(ZEICHEN(10);WAHR;"€ "&TEXT(FILTER(Tabelle1!$H$2:$H$12384;Tabelle1!$N$2:$N$12384=A3);"#.##0,00"))
Außerdem soll noch ein bestimmter Datumswert gefiltert werden. Das Jahr wird ausgelagert. Man könnte mit der Funktion JAHR arbeiten – wir entscheiden uns für einen ZWISCHEN-Bereich, also >= und <=
=TEXTVERKETTEN(ZEICHEN(10);WAHR;TEXT(FILTER(Tabelle1!$K$2:$K$12384;(Tabelle1!$N$2:$N$12384=A3)*(Tabelle1!$L$2:$L$12384<=DATUM($L$2;12;31))*(Tabelle1!$L$2:$L$12384>=DATUM($L$2-100;1;1)));"TT.MM.JJJJ"))
Und so kann man auf Basis dieser Tabelle einen Serienbrief erstellen.
Die Bank wird einmal aufgelistet; die Namen alle einzeln untereinander:
Und das Ganze in der Vorschau:
Ein Stückchen Arbeit – aber ein Mensch war glücklich und ich zufrieden.
Übrigens: die Matrixfunktionen FILTER, EINDEUTIG, SEQUENZ & co sind seeeehhhhhr langsam. Beim Herunterziehen meldet Excel:
Und benötigt leider seeeeeehhhhhhhr viel Zeit:
Ich verstehe Excel ab und zu nicht.
Seit letzter Woche habe ich in der Kategorie „Text“ die neue Funktion „MATRIXZUTEXT“. Sie wandelt die Werte eines Bereichs in eine Zeichenkette um:
Ich sehe keinen Unterschied zur Funktion TEXTVERKETTEN.
Okay – es gibt einen zweiten Parameter – trägt man beim Parameter Format den Wert 1 ein, erfolgt die Darstellung des Textes als:
{„Nr“.“Länge“.“Name“.“Kontinent“.“Quellgebiet“.“Mündung“.“Einzugsgebiet“.“Mittlerer Abfluss“;1.6852.“Nil“.“Afrika“.“Ruandaberge“.“Mittelmeer“.3254853.2660;2.6448.“Amazonas“.“Südamerika“.“Anden, Peruanische Ostkordillere“.“Atlantischer Ozean“.6112000.206000;3.6380.“Jangtsekiang“.“Asien“.“Tibet“.“Ostchinesisches Meer“.1722155.31900;4.6051.“Mississippi“.“Nordamerika“.“Rocky Mountains“.“Golf von Mexiko“.2981076.18400;5.5540.“Jenissei“.“Asien“.“Sajangebirge“.“Arktischer Ozean“.2554482.19600;6.5410.“Ob“.“Asien“.“Mongolischer Altai“.“Obbusen“.2972497.12500;7.5052.“Amur“.“Asien“.“Chentii-Gebirge“.“Ochotskisches Meer“.2400000.11400;8.4845.“Gelber Fluss“.“Asien“.“Bayan-Har-Gebirge“.“Gelbes Meer“.752000.2570;9.4835.“Kongo“.“Afrika“.“Südlich des Tanganjikasees“.“Atlantischer Ozean“.3730474.41800;10.4500.“Mekong“.“Asien“.“Tibet“.“Südchinesisches Meer“.795000.15000}
Es bleibt die Antwort auf die Frage: „Und wer braucht das?“
Seltsame Ereignisse in der letzten Excelschulung.
Ich weiß, dass beim Arbeiten mit dem Funktionsassistenten bei der Funktion ZÄHLENWENN ein Bildschirmfehler (Grafikfehler) auftritt. Ich habe ihn bereits beschrieben:
Allerdings trat dieser Fehler bei einem Teilnehmer auch bei der Funktion WENN auf – er wollte zwei WENN-Funktionen ineinander verketten:
Excel-Schulung. Ich beginne mit den Funktionen SUMME, ANZAHL, MITTELWERT & co. Danach die wohl zweitwichtigste Gruppe: Funktionen der Kategorie „Logik“. Ich zeige, dass man WENN tippen kann oder über den Funktionsassistenten, den man in Formeln in der Gruppe „Logik“ eingeben kann.
Völlig entgeistert starre ich auf die angebotenen Funktionen. Waren die ALLE gestern schon da?
Hallo Rene,
Hallo Rene,
schöne Grüße an dich. Als ich den Artikel über die negative Null gelesen habe (https://www.excel-nervt.de/entweder-sie-geben-mir-eine-gehalterhoehung-oder-erzaehle-500-kollegen-ich-haette-eine-bekommen/) , ist mir eingefallen, dass ich ebenfalls Versuche zu diesem Thema gemacht habe. Tatsächlich ist es möglich in eine Excelzelle eine negative Null einzutragen
Dazu verwende ich folgende kleine Funktion.
Public
Function MinusNull()
MinusNull = -0#
End Function
Durch den Aufruf =MinusNull() wird eine negative Null in der Zelle eingetragen. Diese negative Null kann nun als reiner Wert in andere Zellen kopiert werden.
Wenn diese negative Null in A1 kopiert und in B1 eine einfache Null eintrage wird, ergeben sich folgende Ergebnisse bei einem Vergleich dieser Zellen.
| -0 | 0 |
| Formeltext | Wert |
| =A1=B1 | FALSCH |
| =A1<B1 | WAHR |
| =VORZEICHEN(A1) | -1 |
| =VORZEICHEN(B1) | 0 |
| =TEXT(A1;“0,00000000000000000″) | 0,00000000000000000 |
Die TEXT()-Funktion unterschlägt das Minus-Zeichen. Bei allen anderen Vergleichen gibt es einen Unterschied zwischen einer negativen und der normalen Null.
Ob die Möglichkeit eine negative Null in eine Excelzelle einzutragen Sinn macht ist allerdings fraglich.
Schöne Grüße
bleib gesund
Ernst
Auch wenn die bedingte Formatierung in Excel bei Datenbalken, Farbskalen und Symbolsätzen behauptet, sie könne „Formeln“, unterstützt sie jedoch nur absolute Bezüge. Schade!
Ein Teilnehmer der letzten Excelschulung hatte folgendes Problem. Eine Liste von Mitarbeitern und Mitarbeiterinnen nehmen an Fortbildungen teil. In einer Spalte werden die Summen der Stunden, an sie an Fortbildungen teilgenommen haben, aufgelistet. Eigentlich sollten sie bis zum ersten Quartal 15 Stunden absolviert haben, bis Ende des zweiten Quartals 22,5 Stunden, 30 bis Ausgang des dritten Quartals und 45,5 bis Ende des Jahres. Die Hälfte wäre noch okay – wünschenswert ist die volle Stundenzahl.
Nun möchte der Controller durch lustige Fähnchen (grün, gelb und rot) den Status visualisieren. Ein gemischter Bezug der Form =C$2 wäre gut. Allerdings weigert sich die bedingte Formatierung:
Absolute Bezüge werden unterstützt, relative und gemischte leider nicht. Also muss man für die vier Quartale vier Bezüge erstellen. Zum Glück sind es nur vier!
Excelschulung. Eine Teilnehmerin möchte eine Dropdownliste durch eine Datenüberprüfung haben, in der Smileys angezeigt werden. Ich überlege: in der Schriftart Wingdings gibt es drei Smileys. Man kann sie über Einfügen / Symbol einfügen, oder indem man die Buchstaben J, K und L mit der Schriftart Wingdings formatiert.
Fügt man eine Datenüberprüfung ein, werden jedoch nur die drei Buchstaben dargestellt – auch das Formatieren der Zelle nutzt nichts:
Ich überlege: vielleicht werde ich in den nicht druckbaren Zeichen fündig, die man mit [ALT] + [1], [ALT] + [2], … erzeugen kann. Jedoch finden sich nur zwei Smileys hinter den Nummern 1 und 2:
Mourad hat eine Idee und hilft mir. Im Unicode-Zeichensatz (beispielsweise der Schriftart Calibri) finden sich Smileys:
Wenn ich Word die Unicode-Zahl eintrage (beispielsweise U+1F600) und anschließend [ALT] + [C] erhalte ich das dahinter liegende Symbol:
In Excel funktioniert das leider nicht. Muss ich die Zeichen von Word nach Excel kopieren? Quatsch, meint Mourad – du kannst sie doch direkt von der Internetseite nach Excel kopieren:
Oder mit der Funktion UNIZEICHEN umwandeln, also beispielsweise:
=UNIZEICHEN(128512)
Klappt! Und so können wir eine lustige Auswahlliste erstellen:
Auch mein Add-In [Strg] + [Q] funktioniert:
Eine großes Dankeschön an Mourad Louha für die Hilfe.
Hallo zusammen,
ich möchte folgendes in Excel durchführen.
Ich habe verschiedene Spalten in Excel mit Daten.
Spalte A:
Name
Spalte B:
Vorname
Spalte C:
Geburtsdatum
Spalte D:
Organisation
Excel soll mir jetzt die Einträge markieren die doppelt sind, jedoch nur wenn Name und Vorname identisch sind sprich Spalte A UND B.
Wie kann ich das am besten lösen.
Doppelte Werte anzeigen ist ja an sich nicht schwierig jedoch weiss ich nicht wie ich in dem o.g. Fall vorgehen muss.
Habt ihr da zufällig eine Lösung
Hallo Jörg,
hast du XVERGLEICH?
Dann könntest du es mit folgender Formel in der bedingten Formatierung lösen:
=WENNNV(XVERGLEICH($A2&$B2;$A3:$A$27&$B3:$B$27;0);0)+WENNNV(XVERGLEICH($A2&$B2;$A1:$A$1&$B1:$B$1;0);0)
wenn in A und B Name und Vorname stehen. Ich fange immer in einer Zelle an; trage dort die Bed. Formatierung ein und erweitere ANSCHLIESSEND den Bereich.
cool. Die Funktion xVergleich kenne ich gar nicht. Vielen Dank für den Tipp
XVERWEIS und XVERGLEICH erweitern SVERWEIS und VERGLEICH. Umgekehrt: ich habe es zuerst mit
=Zählenwenn(A:A&B:B;“Rene Martin“)
versucht – geht aber leider nicht … ZÄHLENWENN versagt hier … Schade!
Excelschulung. Wir üben die WENN-Funktion. Ich erkläre, dass man Text in Excel in Anführungszeichen setzen muss – manche Assistenten machen dies automatisch; andere nicht.
Meine Empfehlung: Immer per Hand die Anführungszeichen setzen:
Danach üben wir die bedingte Formatierung – auch eine Art „WENN“. Da die Teilnehmerinnen und Teilnehmer meinen Rat befolgen, tragen Sie den Text in Anführungszeichen ein:
Was passiert? – Nichts! Der Grund:
Excel wandelt den Text „Pandora Papers“ in „““Pandora Papers“““ um – Excel geht davon aus, dass die Anführungszeichen Teil des Suchtextes sind. Also raus damit!
Für eine Firma erstelle eine Feiertagstabelle. Der Lieferant verlangt einen Zuschlag, wenn in der Woche (Mo – Fr) ein Feiertag liegt. Die Basis sind die Feiertage von NRW.
Diese Liste wird auf einem anderen Tabellenblatt verwendet:
Ich klicke auf das Symbol f(x), um die Formel im Funktionsassistenten zu bearbeiten:
Das Ergebnis: „Formelergebnis = Veränderlich“ ?!? Ein Klick auf [OK] und das Meldungsfenster wird geschlossen. Aha!
Immerhin: die Formelüberwachung funktioniert:
Zuerst hört sich die Frage ganz einfach an, aber dann kam ich ins Schleudern:
„Hallo Rene
zum Thema Datumsberechnungen hätte ich gleich eine Frage:
kann Excel auch Zeiträume erkennen, die sich überschneiden, aber unterschiedliche Anfangs- und Endzeiten haben?
Also zum Beispiel:
Mitarbeiter A arbeitet vom 01.05.2021 bis 31.08.2021
Mitarbeiter B arbeitet vom 01.06.2021 bis 15.09.2021
In welchem Zeitraum haben beide gearbeitet
Oder
| Von | Bis | Thema |
| 8:00 | 12:00 | Nachdenken |
| 11:30 | 12:30 | Pause |
| 12:30 | 15:00 | Nix tun |
Ich überlege. Und erweitere das Beispiel. Das Oktoberfest hätte in diesem Jahr vom 18. September bis 03. Oktober stattgefunden. Adelheid ist von 01.09. bis 30.09 in München; Basti vom 25.09. bis 25.10.; Christoph vom 27.09. bis 30.09.; Doris vom 10.09. bis 10.10.; Erich vom 01.09. bis 10.09. und Franziska vom 10.10. bis 20.10. Wie viele Tage hätten sie aufs Oktoberfest gehen können?
Im ersten Schritt habe ich das visualisiert:
Ich beginne mit Erich und Franziska: Wenn Ende < Beginn Oktoberfest, dann 0. Wenn Anfang > Ende Oktoberfest, dann 0:
=WENN(B33<$B$21;0)
Dann die umschließenden Bereiche:
=WENN(UND(B28>=$B$21;B29<=B22);B29-B28+1;0)
Und schließlich die überschneidenden Bereiche:
Wenn Anfang <= Anfang Oktoberfest und Ende <= Ende Oktoberfest, dann Ende – Anfang Oktoberfest:
=WENN(UND(B24<=$B$21;B25<=$B$22);B25-$B$21+1;0)
Analog die zweite Überschneidung:
Wenn Ende >= Ende Oktoberfest und Anfang >= Anfang Oktoberfest, dann Ende Oktoberfest – Anfang:
=WENN(UND(B27>=$B$22;B26>=$B$21);$B$22-B26+1;0)Dabei fällt auf, dass die ersten beiden Fälle mit einbezogen werden – man muss sie explizit ausschließen, beispielsweise mit einem dritten Fall:
und Ende >= Anfang Oktoberfest, beziehungsweise: Anfang <= Ende Oktoberfest:
=WENN(UND(B24<=$B$21;B25<=$B$22;B25>=$B$21);B25-$B$21+1;0)beziehungsweise:
=WENN(UND(B27>=$B$22;B26>=$B$21;B26<=$B$22);$B$22-B26+1;0)so:
Ein Summieren der Varianten verbietet sich, da stets mit >= und <= gearbeitet wurde. So könnte ein Datumsbereich vom 18.09. bis 20.09. in zwei der sechs Kategorien fallen. Entweder man ändert einige der <=in < oder man baue den Baum auf:
=WENN(ODER(B33<$B$21;B32>$B$22);0)2. Fall a: Datumsbereich liegt im Oktoberfestzeitraum:
=WENN(ODER(B33<$B$21;B32>$B$22);0;WENN(UND(B32>=$B$21;B33<=$B$22);B33-B32+1;0))2. Fall b: Oktoberfestzeitraum liegt im Datumsbereich:
=WENN(ODER(B31<$B$21;B30>$B$22);0;WENN(UND(B30>=$B$21;B31<=$B$22);B31-B30+1;WENN(UND($B$21>=B30;$B$22<=B31);$B$22-$B$21+1;0)))
Und schließlich die beiden Fälle Nummer 3:
=WENN(ODER(B25<$B$21;B24>$B$22);0;WENN(UND(B24>=$B$21;B25<=$B$22);B25-B24+1;WENN(UND($B$21>=B24;$B$22<=B25);$B$22-$B$21+1;WENN(UND(B24<=$B$21;B25<=$B$22);B25-$B$21+1;0))))und:
=WENN(ODER(B27<$B$21;B26>$B$22);0;WENN(UND(B26>=$B$21;B27<=$B$22);B27-B26+1;WENN(UND($B$21>=B26;$B$22<=B27);$B$22-$B$21+1;WENN(UND(B26<=$B$21;B27<=$B$22);B27-$B$21+1;WENN(UND(B27>=$B$22;B26>=$B$21);$B$22-B26+1;0)))))
Natürlich hätte man die Bedingungen auch umdrehen können.
Natürlich hätte man den Bedingungsbaum auch anders aufbauen können:
Die Teilnehmerinnen waren begeistert und erschöpft. Leider haben sie nur Excel 2016. Ich überlegte mir, ob man das Problem nicht mit den Matrixfunktionen SEQUENZ & co lösen kann. Die Antwort: Man kann:
Wir beginnen mit Adelheid. Im ersten Schritt werden alle Adelheid-Tage und Oktoberfesttage aufgelistet. Die Funktion SEQUENZ hilft hierbei:
=SEQUENZ(A6-A5+1;1;A5)
Nun kann man zählen, wie oft jeder Adelheid-Tag in der Oktoberfest-Tagesliste vorkommt – einmal oder kein Mal:
=ZÄHLENWENN($E$1#;C1)
Und diese Kolonne kann man summieren.
Oder in einer Formel:
=SUMME(ZÄHLENWENN($C$1#;SEQUENZ(A6-A5+1;1;A5)))
Geht das auch mit FILTER? Klar:
=FILTER(C1#;(C1#>=A5)*(C1#<=A6))Oder in einer Formel:
=FILTER(SEQUENZ(A2-A1+1;1;A1);(SEQUENZ(A2-A1+1;1;A1)>=A5)*(SEQUENZ(A2-A1+1;1;A1)<=A6))
Ich bin sicher, dass es für dieses Problem noch weitere Lösungen gibt. Viel Spaß beim Knobeln.
Hallo Rene,
meine Lösungsvorschlag kommt mit einer einer einfachen Formel (ohne Matrixformel) aus.
Das Startdatum des Oktoberfest steht in B2
Das Enddatum des Oktoberfest steht in B3
Der erste Tag von Adele in München steht in B5
Der letzte Tag von Adele in München steht in B6
Dann berechnet folgende Formel die Überschneidung, also die Anzahl von Tagen die Adele auf das Oktoberfest gehen kann.
=ZEILEN(INDEX(A:A;B2):INDEX(A:A;B3) INDEX(A:A;B5):INDEX(A:A;B6))
Im Falle Adele 13 Tage.
Für die anderen Personen muss diese Formel nur entsprechend angepasst werden.
Gibt es keine Überschneidung wie bei Erich und Franziska gibt die Formel den Wert #NULL! aus.
Wichtig ist das Leerzeichen in der Mitte der Formel. Dadurch wird die Schnittmenge ermittelt.
Salü
Ernst
PS: Nachtrag:
Allerdings kann man durch eine kleine Änderung der Formel meines Lösungsvorschlages (aus Zeilen() mache Zeile() und gebe die Formel als Matrixformel ein) eine einspaltige Matrix erzeugen, in der die Datumswerte der Schnittmenge eingetragen sind.
Also
=ZEILEN(INDEX($A:$A;$B$2):INDEX($A:$A;$B$3) INDEX($A:$A;B5):INDEX($A:$A;B6))Ergebnis = 13
{=ZEILE(INDEX($A:$A;$B$2):INDEX($A:$A;$B$3) INDEX($A:$A;B5):INDEX($A:$A;B6)) }Ergebnis ( Zellen als kurzes Datum formatiert.):
| 18.09.2021 |
| 19.09.2021 |
| 20.09.2021 |
| 21.09.2021 |
| 22.09.2021 |
| 23.09.2021 |
| 24.09.2021 |
| 25.09.2021 |
| 26.09.2021 |
| 27.09.2021 |
| 28.09.2021 |
| 29.09.2021 |
| 30.09.2021 |
Danke Ernst – sehr clever!
Und schließlich hat Helmut Cantzler eine Lösung mit SUMMENPRODUKT gefunden:
=SUMMENPRODUKT((SEQUENZ($B$3-$B$2+1;;$B$2;1)<=B6)*(SEQUENZ($B$3-$B$2+1;;$B$2;1)>=B5))
Danke an Helmut – auch sehr clever!
Und schließlich kann man das Problem auch mit VBA lösen. Man muss zwei Bereiche (Range) definieren und die Schnittmenge (Application.Intersect) bestimmen:
Dim xlBereich1 As Range
Dim xlBereich2 As Range
Dim xlSchnittmenge As Range
With ThisWorkbook.Worksheets("Helmut")
Set xlBereich1 = .Range(.Cells(Range("B2").Value2, 1),
.Cells(.Range("B3").Value2, 1))
Set xlBereich2 = .Range(.Cells(Range("B5").Value2, 1),
.Cells(.Range("B6").Value2, 1))
End With
Set xlSchnittmenge = Application.Intersect(xlBereich1, xlBereich2)
MsgBox xlSchnittmenge.Cells.Count
Oder in einem Befehl:
MsgBox Application.Intersect(Range(Cells(Range("B2").Value2, 1), Cells(Range("B3").Value2, 1)), Range(Cells(Range("B5").Value2, 1), Cells(Range("B6").Value2, 1))).Cells.CountKlappt! Danke an Andreas Protzmann für diesen Hinweis. Auch clever!
Und schließlich reagiert Christian:
Auch sehr clever! Und sehr elegant! Danke, Christian.
Hallo René,
ich lese regelmäßig deinen Blog „Excel nervt“ und hatte dir vor einiger Zeit auch schon einmal eine Mail zu deinem Buch „Excel: Zahlen. Rechnen. Formeln“ geschickt, die du mir sehr freundlich und ausführlich beantwortet hattest.
Heute bräuchte ich mal deine Hilfe. Ich sitze hier vor einem Problem, bei dem ich alleine nicht weiterkomme. Es geht um einen Milchviehbetrieb, der seine Jungtiere von einem Aufzuchtsbetrieb großziehen lässt. Ich soll nun rückwirkend die Aufzuchtskosten pro Monat überprüfen. Dazu steht mir eine Tabelle zur Verfügung mit dem Abgangsdatum der Tiere vom Milchviehbetrieb (= Zugangsdatum Aufzuchtsbetrieb) und dem Zugangsdatum der Tiere beim Milchviehbetrieb. Ist das Tier aktuell noch beim Aufzuchtsbetrieb, ist jeweilige Feld für das Zugangsdatum leer. Pro Tag, den eine Kuh beim Aufzuchtsbetrieb ist, erhält der Aufzuchtsbetrieb eine Pauschale (z.B. 1€ pro Kuh und Tag). Ich hatte irgendwie gedacht, dass man das relativ leicht über eine Formel ermitteln könnte. Problematisch ist vor allem ein angebrochener Monat, wenn beispielsweise eine Kuh am 5. März 2021 an den Aufzuchtsbetrieb geliefert wird, erhält der Aufzuchtsbetrieb für diese Kuh ja theoretisch 27 €.
Anbei habe ich eine Beispieltabelle hinzugefügt. In den Spalten E bis R möchte für den jeweiligen Monat und für jede Kuh die Tage ermitteln, die diese beim Aufzuchtsbetrieb war.
Kannst du mir da weiterhelfen? Irgendwie stehe ich gerade auf dem Schlauch.
Vielen Dank im Voraus.
Hallo,
hübsche Fingerübung.
Ich würde in die erste Zeile jeweils den 01. April 2020, 01. Mai 2020, … und als April 2020, Mai 2020, … formatieren. Also mit MMM JJJJ
Du musst einen „Baum abarbeiten“:
1. Fall: sind Jahr und Monat identisch -> dann rechne die Anzahl Tage bis Ende des Monats.
=WENN(UND(JAHR(E$1)=JAHR($C2);MONAT(E$1)=MONAT($C2));MONATSENDE($C2;0)-$C2+1;0)
Die Erklärung: Ich brauche in diesem Fall die Anzahl der Tage bis zum Ende des Monats. Die Funktion
MONATSENDE($C2;0)berechnet den letzten Tag des Monats (hier: 30.04.2020). Und davon wird das Datum abgezogen (hier: 11.04.2020). Das Ergebnis lautet 19; plus 1 = 20 = die Anzahl der Tage vom 11. bis zum 30. (beide einschließlich)
2. Fall: liegt Abgang vor dem Datum (bspw. 01. April) und Zugang nach dem nächsten Monat -> dann voller Monat (die zweite WENN-Funktion wird die Stelle der 0 gesetzt:)
=WENN(UND(JAHR(F$1)=JAHR($C2);MONAT(F$1)=MONAT($C2));
MONATSENDE($C2;0)-$C2+1;
WENN(UND($C2<F$1;$D2>=G$1);TAG(MONATSENDE(F$1;0));0))
Die Erklärung:
TAG(MONATSENDE(F$1;0))liefert die Anzahl der Tage des Monats – hier 31 beim Mai.
3. Fall: liegt Abgang vor dem Datum (bspw. 01. April) und Zugang nach dem Datum (bspw. 01. April) -> Anzahl der Tage des Monats
=WENN(UND(JAHR(M$1)=JAHR($C2);MONAT(M$1)=MONAT($C2));
MONATSENDE($C2;0)-$C2+1;
WENN(UND($C2<M$1;$D2>=N$1);TAG(MONATSENDE(M$1;0));
WENN(UND($C2<M$1;$D2>M$1);TAG($D2);0)))
Auch hier wird wieder die 0 durch eine neue, dritte WENN-Funktion ersetzt. Die Funktion
TAG($D2)berechnet die Anzahl der Tage bis zum Tag, also aus dem 17.12.2020 wird die Zahl 17 berechnet.
4. Fall: noch kein Zugang eingetragen und Abgang vor dem Datum (bspw. 01. April) -> dann Tag des Monats
=WENN(UND(JAHR(Q$1)=JAHR($C7);MONAT(Q$1)=MONAT($C7));
MONATSENDE($C7;0)-$C7+1;
WENN(UND($C7<Q$1;$D7>=R$1);TAG(MONATSENDE(Q$1;0));
WENN(UND($C7<Q$1;$D7>Q$1);TAG($D7);
WENN(UND($D7="";$C7<Q$1);TAG(MONATSENDE(Q$1;0));0))))
Die Erklärung: Ebenso wie oben berechnet
TAG(MONATSENDE(Q$1;0)die Anzahl der Tage des Monats.
Stimmt das?
Hallo Rene,
vielen Dank für die schnelle Antwort.
Für diese Kühe hatte ich die Auswertung händisch vorgenommen und komme auf das gleiche Ergebnis wie mit deiner Formel. Scheint also zu passen.
Einen „Baum“ hatte ich mir auch schon überlegt, allerdings hatte ich Schwierigkeiten bei dem Umgang mit den „angebrochenen“ Monaten und den Kühen ohne Zugangsdatum.
In der ersten Zeile hatte ich sogar schon jeweils den 1. des Monats als Datum eingetragen, weil ich damit rechnen wollte. Ich hatte es dann über „Zellen formatieren…“ – Kategorie: Datum – Typ: Mrz. 12 umgewandelt. Blöd, dass Excel dann beim Mai auch einen Punkt macht, wie mir gerade auffällt.
Vielen, vielen Dank für deine Hilfe und mach weiter so. Ich werde deinen Blog auf jeden Fall weiter verfolgen.
Liebe Grüße
Schöne Frage in der letzten Excelschulung:
Wie kann ich es erreichen, dass eine Datenreihe im 15-Minuten-Takt fortgesetzt wird? Beispielsweise für einen Stundenplan. Ich schaue nach:
Erstaunlicherweise lässt der Assistent „Datenreihe“, den man in der Gruppe „Bearbeiten“ in der Registerkarte „Start“ findet, keine Uhrzeiten zu …
Natürlich könnte man es mit einer Formel erreichen:
=A2+15/24/60
Da in der Schulung Anfängerinnen waren, die bislang noch wenig Erfahrung mit Formeln hatten, schlage ich die naheliegende Lösung vor: zwei Startzeiten eintragen, markieren und runterziehen:
Geht auch so!
Sehr geehrter Herr Martin,
mit großem Interesse verfolge ich Ihre Excel-Seminare und mag es sehr von Ihnen neue Dinge zu lernen und mein Wissen zu erweitern. Helfen Sie auch bei speziellen Excel-Problemen? Ich habe hier eine größere Datenbank. Basis sind verschiedene Materialnummer in einer Spalte, welche mehrmals auftreten, da es zu den Materialien mehrere Bestellungen gibt mit unterschiedlichen Konditionen. Mich interessiert pro Materialnummer der Maximalpreis. Ich habe hier an die 80 Materialien und dazu jeweils mehrere Bestellungen. Der Maximalpreis sollte dann in einer zz. Spalte erscheinen. Mit freundlichen Grüßen
Hallo Frau S.,
ich würde eine Pivottabelle erstelle. Gruppieren Sie die Materialnummern und ziehen Sie dann die Preise in das Wertefeld. Ändern Sie in den Wertfeldeinstellungen die Summe in MAX.
Alternative: Wenn Sie Excel in Microsoft 365 haben: mit der Funktion EINDEUTIG erhalten sie die eindeutige Liste der Materialnummern. Mit MAXWENN können Sie das MAX pro ID berechnen lassen. Hilft Ihnen das?
LG :: Rene Martin
Hallo Herr Martin, vielen Dank für die prompte Antwort. Die Pivottabelle ist der Lösung für mich. Entsprechende Seminare dazu stehen noch auf meiner persönlichen Agenda. Für den akuten Fall, haben Sie mir prima geholfen. Grüße,
… und ich kann es nicht lassen, zu mailen, mit den Infos:
Frage: Kann man noch prüfen
wenn ODER(Prüfung auf TEXT(J9), Prüfung auf TEXT(L9), Prüfung auf TEXT(N9)): „“
D.h.: Wenn mindestens eine der Zellen Text beinhaltet >>> Ergebnis der Zelle: Nix drinnen
Ich habe schon ein wenig schlechtes Gewissen …
Ich maile noch ein DANKESCHÖN & Gruß
Jürgen
Hallo Jürgen,
deine Bedingungen werden mit
=WENNFEHLER(WENN(ANZAHL(J9;L9;N9)>1;MITTELWERT(J9;L9;N9);"");"")abgefangen. Die Funktion MITTELWERT übergeht (ebenso wie SUMME, ANZAHL, MAX, …) Texte. Senn du das explizit abfangen willst, dann beispielsweise so:
=WENNFEHLER(WENN(ANZAHL(J9;L9;N9)<ANZAHL2(J9;L9;N9);"";WENN(ANZAHL(J9;L9;N9)>1;MITTELWERT(J9;L9;N9);""));"")Liebe Grüße
Rene
Excelschulung. Eine junge Teilnehmerin. Azubi.
Sie zeigt mir Beispiele von ihrem Lehrer, die sie verstehen und können muss, da ähnliche Beispiele Teile der Prüfung von Excel sind. Geübt werden sollen einige Formeln:
Die Werte werden kummuliert. In der ersten Zelle steht
=WENN(F15<>"";F15;"")Darunter befindet sich die Formel:
=WENN(G15<>"";G15+F16;"")So hatte ich es vor vielen Jahren auch gemacht. Inwzischen löse ich das Problem der kummulierten Werte mit einer Formel, die ich nach unten ziehe:
=SUMME($F$15:F15)Fazit: Lehrer und Lehrerinnen sollten regelmäßig ihre Übungsaufgaben durchsehen und überprüfen, ob es bessere Lösungestechniken, neue Funktionen gibt, mit denen man Aufgaben eleganer lösen kann und testen, ob sie einige Bedingungen vergessen haben.
Excelschulung. Eine junge Teilnehmerin. Azubi.
Sie zeigt mir Beispiele von ihrem Lehrer, die sie verstehen und können muss, da ähnliche Beispiele Teile der Prüfung von Excel sind. Geübt werden sollen einige Formeln:
Der prozentuale Anteil wird berechnet:
=WENN(UND(A15<>"";B15<>"");D15*100/$D$25;"")Okay, mit der Funktion
UND(A15<>"";B15<>"")wird überprüft, ob die Zellen der Spalte A und B gefüllt sind. Eine hübsche Übung, um UND und WENN zu üben.
Dennoch: WENNFEHLER wäre sicherlich besser gewesen, um ALLE Fehler abzufangen.
Aber noch mehr irritiert mich die Berechnung
D15*100/$D$25Warum wird mit 100 multipliziert? Ich hätte den Anteil von einer Dezimalzahl in eine Prozentzahl formatiert. Und damit weitergerechnet.
Wäre das nicht eher im Sinne von Excel gewesen?
Excelschulung. Eine junge Teilnehmerin. Azubi.
Sie zeigt mir Beispiele von ihrem Lehrer, die sie verstehen und können muss, da ähnliche Beispiele Teile der Prüfung von Excel sind. Geübt werden sollen einige Formeln:
Ich komme bei der Verwendung der Funktion RANG ins Grübeln:
=RANG(B15;$B$15:$B$24;0)Wenn man diese Funktion in Excel eintippt, sieht man vor dem FUnktionsnamen ein Ausrufezeichen. Das bedeutet, dass diese Funktion durch andere erstetzt wurde und aus Kompatibilitätsgründen noch zur Verfügung steht. Richtig: in Excel 2010 wurd diese Funktion RANG durch RANG.GLEICH und RANG.MITTELW abgelöst. Man findet RANG nun in der Kategorie Kompatibilität:
Das Problem bei der Funktion Rang ist die Antwort auf die Frage, welche Zahl weisen wir zwei gleich großen Werten zu? Also: wenn es den größten Werte zwei Mal gibt? Zählen wir dann 1; 1; 3 (so rechnet RANG.GLEICH und RANG) oder 1,5; 1,5; 3 – so rechnet RANG.MITTELW.
Ich probiere es aus, ändere zwei Werte so, dass sie gleich groß sind. Das Ergebnis: Fehler in der weiteren Berechnung, die nicht abgefangen wurden:
Fazit: vielleicht hätte der Lehrer oder die Lehrerin in den letzten zehn Jahren einmal das Beispiel neu nachrechnen sollen und auf Konsistenz prüfen sollen. Und vor allem: prüfen sollen, ob es inzwischen nicht neue, bessere Funktionen zur Lösung des von ihm oder ihr gestellten Problems gibt.
Excelschulung. Eine junge Teilnehmerin. Azubi.
Sie zeigt mir Beispiele von ihrem Lehrer, die sie verstehen und können muss, da ähnliche Beispiele Teile der Prüfung von Excel sind. Geübt werden sollen einige Formeln:
In Spalte A stehen Lieferantennummern, in Spalte B Umsatzzahlen. In Spalte C wird der Rang berechnet:
=RANG(B15;$B$15:$B$24;0)Doch schon Spalte D lässt mich stutzen:
=INDEX($A$15:$B$24;(VERGLEICH(1;$C$15:$C$24;0));2)Darunter befindet sich die Formel:
=INDEX($A$15:$B$24;(VERGLEICH(2;$C$15:$C$24;0));2)
Danach Nummer 3, Nummer 4, … Uff! Kennt der Lehrer oder die Lehrerin nicht die Funktion ZEILE? Ich stutze und wundere mich …
#####
Hallo Rene,
kannst du vielleicht auch deinen Verbesserungsvorschlag für die Formel dazu schreiben. Mir erschließt sich grad nämlich nicht, wie man die Funktion ZEILE hier einbauen soll. Wahrscheinlich steh ich nur auf dem Schlauch…
Lieber Anonymous,
die Funktion ZEILE hat zwei „Gesichter“:
=ZEILE()liefert die Zeilennummer der aktuellen Zelle. Steht also diese Funktion in C7,liefert =ZEILE() die Zahl 7. Beim Herunterziehen erhalte ich 8, 9, 10, …
Die Funktion der Lehrerin oder des Lehrers
VERGLEICH(1;$C$15:$C$24;0)sucht den Wert 1 in der Rang-Spalte, also die Zeile, die den größten Wert enthält.
VERGLEICH(2;$C$15:$C$24;0)sucht den zweitgrößten Wert.
Ich bin sicher, dass die Lehrerin oder der Lehrer die Werte 1, 2, 3, 4, … per Hand getippt hat. Da die Formel in D15 stand, hätte man auch schreiben (und herunterziehen) können:
=INDEX($A$15:$B$24;(VERGLEICH(ZEILE()-14;$C$15:$C$24;0));2)Auch das ist noch etwas umständlich. Man kann mit ZEILE einen Bezug auf eine Zelle herstellen und von dieser Zeile die Zeilennummer ermitteln. Also:
=ZEILE(A1)liefert 1. Und beim Herunterziehen erhält man die Werte1, 2, 3, 4, die den größten, zweitgrößten, drittgrößten, … Wert liefert. Also:
=INDEX($A$15:$B$24;(VERGLEICH(ZEILE(A1);$C$15:$C$24;0));2)Excelschulung. EIne junge Teilnehmerin. Azubi.
Sie zeigt mir Beispiele von ihrem Lehrer, die sie verstehen und können muss, da ähnliche Beispiele Teile der Prüfung von Excel sind. Geübt werden sollen WENN und SVERWEIS:
Ich sehe folgende Formel:
=WENN(A17="";"";SVERWEIS(A17;Leistung;2))Ist ja okay zu überprüfen, ob A17 leer ist, aber wäre es nicht besser ALLE Fehler mit einem WENNFEHLER abzufangen?
Sicherlich dient diese Formel WENN zum Üben …
Hallo Rene!
Hoffe, dass du, deine Familie und dein Umfeld alle gesund seid.
Du hast mir vor einiger Zeit geholfen mehrere Belegnummern in eine Zelle zu schreiben. Nun wollte ich auch das Datum (auch mehrere) auf gleiche Weise anzeigen lassen. Herausgekommen sind dann die Zahl(en) vom Datum. Wenn nur eine Zahl (=Datum) angezeigt wurde konnte ich das Datumformat. Wenn nur ein Zahlenwert vorhanden ist, habe ich, wie gelernt, die Formel mit 1 multipliziert und dann das Datumformat angewendet. Meine Vermutung ist, dass man die Formel um eine Formatierungsformel ergänzt. Videos über die allgemeine Formatierung mit Formeln in der Zelle habe ich leider nicht gefunden. Bitte um Lösung.
Vielen Dank, Peter
Hallo Peter, die Formel muss statt
=TEXTVERKETTEN(", ";WAHR; FILTER(tbl_KF_Kosten[Datum];tbl_KF_Kosten[Pferd]=E30;"0")) lauten:
=TEXTVERKETTEN(", ";WAHR; TEXT(FILTER(tbl_KF_Kosten[Datum];tbl_KF_Kosten[Pferd]=E30;"0");"TT.MM.JJJJ")) Die Funktion TEXT formatiert eine Zahl (und damit auch ein Datum).
LG Rene
Gestern in der Excelschulung. Interessante Frage: Wenn ich ein Datum herunterziehe, ist die Schrittweise ein Tag. Wenn ich eine Uhrzeit herunterziehe ist die Schrittweise eine Stunde. Kann ich das in Excel einstellen, dass Excel im 15-Minuten-Takt zählt?
Ich überlege. Da gibt es doch den Assistenten Ausfüllen / Datenreihe im Register „Start“:
Allerdings: dort kann man nur die Schrittweite für Datumsangaben – nicht für Uhrzeiten eintragen:
Damit bleibt nur:
Kennen Sie den Median? Die Excel-Funktion MEDIAN?
Gemeint ist – umgangssprachlich – der mittlere Wert einer Datenreihe. Hat man zwei mittlere Werte (bei einer geraden Anzahl Elemente), berechnet sich der Median als Durchschnitt der beiden mittleren Werte.
Wer braucht denn so etwas?
Stellen Sie sich vor, sie haben eine Basketballmannschaft, die aus fünf Spielern besteht: vier recht kleinen und einem sehr langen Spieler. Der Mittelwert würde das Bild „verzerren“, weil der Ausreißer den Durchschnitt nach oben verschiebt. Der Median gibt dagegen ein besseres Bild der Mitte, weil er resistent gegenüber Ausreißern ist:
Median – eine statistische Größe also. Nun finde ich in einer Berechnung von Transportkosten folgende Formel:
=WENN(G15="";"";WENN(UND(ODER(MAX(Logistikdaten!I38:K38)>120;MEDIAN(Logistikdaten!I38:K38)>60;MIN(Logistikdaten!I38:K38)>60);$G$15<=440);"Ja";"Nein"))Verwundert reibe ich die Augen? Warum werden die Logistikkosten mit einem Median berechnet?
Dann verstehe ich: es wird Bezug genommen auf die drei Werte Länge, Breite und Höhe. Von diesen drei Werten wird der größte Wert (MAX), der kleinste Wert (MIN) und der mittlere Wert (MEDIAN) berechnet. Sehr clever!
Ich muss per Programmierung den Inhalt einer Formel mit einem Bezug auswerten. Ich überlege:
Jede Bezugsformel innerhalb einer Arbeitsmappe hat in Excel die Form:
=Blattname!ZellbezugPrima! Liegt auf dem Blatt „BMW“ in der Zelle „Z8“ ein Wert, kann man die Formel
=BMW!Z8
gut auswerten, indem man an dem Ausrufezeichen trennt. Vor dem Ausrufezeichen: Blattname; hinter dem Ausrufezeichen: Zellbezug. Gesagt – getan.
Jedoch: mir fällt auf, dass mein Programm manchmal einen Fehler produziert. Beispielsweise beim Bezug auf das Blatt „Alfa Romeo“, „Aston Martin“, „Rolls-Roycs“, und so weiter. Klar, ein Bezug auf das Blatt „Alfa Romeo“ wird dargestellt als:
=’Alfa Romeo‘!P1
Da der Blattname ein Leerzeichen (oder Gedankenstrich) enthält, muss ich den Apostroph aus dem Blattnamen löschen. Wirklich? Sollte ich ihn nicht besser von links und rechts löschen? Also: Wenn das erste Zeichen = ‚, dann entfernen. Wenn das letzte Zeichen = ‚, dann entfernen? Kann ein Blattname ein Apostroph enthalten? Ich probiere aus:
Tatsächlich: Mercedes‘ Benz funktioniert! Excel verbietet bei Namen von Tabellenblättern am Anfang und am Ende ein Apostroph, aber innerhalb des Namens ist es erlaubt.
Und wie sieht der Bezug auf dieses Blatt aus?
='Mercedes'' Benz'!W117Erstaunlich! Der Apostroph wird entwertet, indem das Zeichen zwei Mal geschrieben wird! Das ist mir noch nie aufgefallen!
Das heißt: ich muss zwei Hochkommata (‚ ‚) durch eines ersetzen und muss den Apostroph am Anfang und am Ende löschen. Perfide!
Übrigens: Bevor Sie jetzt erboste Kommentare schreiben: die in Sindelfingen produzierende Automobilfirma heißt MERCEDES BENZ – ohne Apostroph!
Eine sehr hübsche Knobelaufgabe. Ich erstelle Formulare für eine Firma, die Waren liefert. Dabei sind einige komplexe Berechnungen nötig. Beispielsweise folgende:
„Sollte das Lieferdatum in eine Woche mit einem Feiertag fallen, werden 25% mehr Kosten berechnet“.
Ich frage nach: „Welche Feiertage?“ Die Antwort: die Feiertage von NRW.
Zweite Frage: Wenn der Feiertag auf einen Samstag oder Sonntag fällt? Die Antwort: dann soll er nicht berücksichtigt werden.
Erster Schritt: Ich erstelle eine Liste der Feiertage von Nordrhein-Westfalen:
Man kann die beweglichen Feiertage auf Basis des Ostersonntags berechnen, für den es eine Formel gibt. Und die festen Feiertage berechnen. Oder man kopiert sich diese Liste aus dem Internet. Oder greift mit PowerQuery auf eine Feiertagsliste im Internet zu.
Im zweiten Schritt erstelle ich eine Spalte mit Datumsangaben – beispielsweise vom 30.12.2019 bis zum 31.12.2034.
Die Formel
=WENN(UND(ZÄHLENWENN($B$2:$P$13;A21)>0;WOCHENTAG(A21;2)<=5);"F";"")prüft, ob das Datum ein Feiertag ist (also in der Feiertagsliste steht) und ob der Feiertag auf einen Tag von Montag bis Freitag fällt (also Wochentag <= 5):
Danach überprüfe ich, ob in der Woche ein Feiertag („F“) liegt:
Dazu verwende ich die Formel:
=WENN(ZÄHLENWENN(BEREICH.VERSCHIEBEN(A21;-WOCHENTAG(A21;3);1;7;1);"F")>0;"F";"")Der Rest ist trivial, oder? Man sucht ein Datum in der ersten Datumsspalte und prüft, ob in der Wochenspalte ein „F“ steht oder nicht:
=WENN(SVERWEIS(H20;A21:C5501;3;FALSCH)="F";25%;0%)
Beispiel: der 01. Mai 2021 fiel auf einen Samstag, also ist für den 29. April kein Zuschlag erforderlich.
Pfingstmontag war in diesem Jahr am 24. Mai – also muss für den 26. Mai 2021 ein Zuschlag bezahlt werden.
Eine hübsche Knobelaufgabe – ich habe ein bisschen überlegen müssen.
Hallo Herr Martin,
könnten Sie bitte noch berechnen, ob das Paket Sperrgut ist.
Ich frage zurück: „Woran erkenne ich, ob es Sperrgut ist?“ Ich erhalte die schriftliche Antwort:
„Länge und Breite dürfen nicht größer als 2,40 Meter sein.“
Ich überlege:
Länge + Breite < 2,4?
Länge < 2,4 UND Breite < 2,4?
Länge < 2,4 ODER Breite < 2,4?
Was ist mit der Höhe? – Kann man das Paket nicht drehen?
Ich warte noch auf die Antwort.
Wahrscheinlich überlegen sie selbst …
Letzte Woche hatte ich eine Einzelplatzschulung. Nennen wir es Coaching: ein älterer Herr wollte sich fit in den Anwendungsprogrammen machen. Ich erkläre die Grundlagen: Text und Zahl. „Bitte schließen Sie die Eingabe immer mit [Enter] ab.“ (Dass es noch andere Möglichkeiten gibt, wusste er selbst.) „Bitte drücken Sie immer die Ok-Taste. In der Kirche wird sie auch die [Amen]-Taste genannt.“ Er schmunzelt.
Wir üben Formeln und drücken [Enter]. Immer. Nicht wegklicken! Okay – verstanden.
Wir ziehen die Formel am Ausfüllkästchen nach unten. Er drückt [Enter]. Okay … ich muss mich ein bisschen korrigieren.
Ich habe hier einige Artikel zu dem kostenlosen PlugIn Excel-DNA geschrieben, mit dem man Excelfunktionen (und andere Werkzeuge) in Excel schreiben kann und welche Probleme es dabei gibt.
Nun habe ich einen Artikel – nein: genauer: es sind drei Artikel – darüber veröffentlicht: in der neuen dotnetpro.
Bernhard hat mich darauf aufmerksam gemacht. Ist mir bislang nicht aufgefallen.
Die Funktion DATEDIF in Excel und die VBA-Funktion DateDiff rechnen unterschiedlich.
Trägt man in zwei Zellen die Datumsangaben 20.05.2021 und 01.06.2021 ein, so beträgt bei der Excelfunktion DATEDIF mit dem Parameter „M“ das Ergebnis 0, bei der VBA-Funktion dagegen 1.
Ich habe eine kleine Tabelle aufgebaut: im oberen Teil einige Datumsdifferenzen auf Basis des Monats:
Im unteren Teil verwende ich ein kleines VBA-Makro:
Sub BerechneDateDIFF()
Dim intZeile As Integer
Dim intSpalte As Integer
For intZeile = 21 To 30
For intSpalte = 2 To 20
ActiveSheet.Cells(intZeile, intSpalte).Value = DateDiff("M", ActiveSheet.Cells(intZeile, 1).Value, ActiveSheet.Cells(20, intSpalte).Value)
Next intSpalte
Next intZeile
End SubDas Ergebnis:
Die Unterschiede habe ich mit einer bedingten Formatierung farblich hervorgehoben.
Ein Dankeschön für den wertvollen Hinweis an Bernhard Ramroth.
Witzig. Vorlauter Formeln und Assistenten übersieht man manchmal die Wirklichkeit.
Wir schauen die Zielwertsuche an. Mit der Funktion RMZ (regelmäßige Zahlungen) wird die Annuität berechnet.
Nun soll „zurückgerechnet“ werden. Ich möchte wissen wie hoch der Zinssatz bei einer Rückzahlung von 220 Euro ist. Also Zielwertsuche (sie soll ja auch gezeigt werden:
Sie rechnet und liefert:
-4,92% !?! Minus !?!
Es dauert eine Weile, bis wir verstehen: Das Ergebnis ist korrekt. Man müsste mit negativen Zinsen rechnen, um auf diesen Betrag zu kommen … Doch nicht falsch und auch nicht so unrealistisch.
Danke an Hartmut Hilbich für dieses Beispiel.
Gestern im Excelstammtisch. Frank Arendt-Theilen macht darauf aufmerksam, das PowerQuery einen Parameter bei der Funktionen RUNDEN (Number.Round) anbietet:
IntelliSense zeigt allerdings in M, dass diese Funktion einen weiteren Parameter besitzt: roundingMode mit fünf Konstanten:
Die Standardeinstellung von PowerQuery ist RoundingMode.ToEven. Damit unterscheidet sich diese Rundenfunktion von RUNDEN in Excel. Dort wird RoundingMode.AwayFromZero verwendet. Deshalb unterscheiden sich diese beiden Runden-Funktionen: PQ rundet wie VBA; Excel rundet anders …
Vielen Dank, Frank für diesen sehr, sehr wertvollen Hinweis!
Und: während sich auf der Microsoft-Hilfeseite
https://docs.microsoft.com/de-de/powerquery-m/number-round
leider kein Hinweis dazu findet, hat Ken Pulse es sehr gut erläutert:
https://www.excelguru.ca/blog/2014/09/17/power-query-the-round-function/
XLarium weist darauf hin, dass es noch weitere Artikel und Videos zu diesem Thema gibt:
MrExcel hat vor kurzem ein Video dazu gemacht:
Weitere Videos zum Thema Runden:
MROUND In Power Query
TRUNC In Power Quer
CEILING oder FLOOR in Power Query
Hallo Herr Martin,
ich filtere in dem Kunden-Excel die Werte mittels einer Pivot-Tabelle. Der Filter zeigt aber nur die tatsächlich vorhandenen Werte an.
Jetzt möchte ich im Pivot einen festen Wertefilter definieren, unabhängig welche aktuellen Werte vorhanden sind:
Beispiel:
Ich möchte immer alle Einträge < 24 Stunden gefiltert haben
Aktuelle Werteinträge sind 1 und 2 Stunden. Den Filter 24 Stunden kann ich aber erst auswählen, wenn es mindestens einen Eintrag mit 24 Stunden gibt. Lässt sich dies im Pivot einstellen. Ich habe bislang keine Möglichkeit gefunden.
Hallo Herr H.,
der Gedanke der Pivottabelle ist ja, die vorhandenen Werte zu gruppieren und die Zahlen zusammenzufassen (aggregieren, also: summieren, zählen, …) Wenn Sie andere Werte sehen möchten, müssen diese in der Liste stehen (man müsste sie ausblenden).
Hallo Herr Martin,
es gibt im Office 365 Excel den Befehl FILTER, der genau das macht, was ich benötige. Nur hat mein Kunde leider eine ältere Version. Lässt sich das in einem älteren Excel mit einem workaround bauen?
Viele Grüße
Hallo Herr H.,
Nein – bitte nicht die Funktion FILTER verwenden – sonst hat die Firma ein Problem!
Was würde ich tun?
* entweder die Daten dazwischen verstecken (und die Zeilen ausblenden)
* oder die Daten auf einem anderen Blatt sammeln und dort alle notwendigen Daten einsammeln.
Hum. Sonst? Müsste mal überlegen
Liebe Grüße
Rene Martin
Hallo Herr Martin,
Filter geht beim Kunden nicht, aber ist die Funktion so gefährlich?
Ich habe mittels Pivot die Daten auf ein anderes Blatt ausgelagert und nutze das Ergebnis für die Dropdown-Felder.
Ich habe jetzt einen Dummy-Wert eingefügt, damit die Pivots die Auswahlfelder behalten, auch wenn keine Daten auszuwerten sind. Ggf. wäre eine Lösung ganz ohne Pivots zu arbeiten, aber dazu bräuchte ich so etwas wie die Filter-Funktion.
Hallo Herr H.,
nein, nein: FILTER & co sind klasse – Problem: nicht jeder hat diese Funktionen. Deshalb: bauen wir den Filter doch nach!
Werfen Sie mal einen Blick in meine Liste: In Spalte H befinden sich die sechs Werte. Einer wird in J2 ausgewählt. In Spalte L ermittle ich die Zeilennummer, falls gefunden. In Spalte M sammle ich diese Nummern ein; gruppiere sie also. Mit BEREICH.VERSCHIEBEN baue ich die Liste ab O1 auf (ich hätte auch INDIREKT oder INDEX / VERGLEICH verwenden können).
Ich könnte es auch mit AGGREGAT aufbauen – aber lassen wir das …
kommen Sie damit klar?
Liebe Grüße
Rene Martin
Tim will’s wissen.
Nachdem er die zehn wichtigsten Excel-Funktionen beschrieben hat, fragt er sich, welches die nächsten zehn wichtigen sind.
Habt ihr Lust ihm zu helfen?
Welchen sind deiner Meinung nach wichtige, nützliche und von dir häufig verwendete Funktionen?
Hallo Herr Martin,
ich wieß nicht mehr genau, ob ich Sie einmal auf folgende Thematik angesprochen hatte.
Es ging um die Umformatierung von Postleitzahlen aus einer Spalte in Aneinanderreihung
in einer Zelle mit Komma/-Lehrzeichen-Trennung (siehe Anhang „screenshot“):
Diese Schreibweise (nebeneinander) ist die auf unserem content-management-system.
Wenn ich nun größere Zahlenmengen erhalte, wird es händisch sehr aufwendig.
Könnten Sie mir bei diesem Prozedere wieder behilflich sein?
Vielen Dank schon vorab.
Mit besten Grüße
Hallo Herr S.,
welche Excel-Version haben Sie denn? Haben Sie die Funktion TEXTVERKETTEN? DAMIT klappt es ganz einfach:
=TEXTVERKETTEN(„, „;WAHR;E3:E14)
E3:E14 ist natürlich der Bereich der Postleitzahlen. Dann kopiere ich den Bereich an eine andere Stelle und füge ihn als Werte ein – schon habe ich die Liste.
Klappt das?
Liebe Grüße
René Martin
Hallo Herr Martin,
vielen dank für die schnelle Reaktion. Ich habe die 2010’er Version Version 14.7263.5000 (32 Bit)
Direkt das Wort TEXTVERKETTEN ist in den Formeln nicht enthalten.
Gibt es unter dieser alten Version vielleicht trotzdem eine Lösung?
Beste Grüße nach München C. S.
Hallo Herr S.,
oder so:
wiederholen Sie in der ersten Zelle den Wert, bspw.: =E16
schreiben Sie darunter: =F16&“, „&E17
ziehen Sie die untere Formel runter!
das Ergebnis der letzten Zelle können Sie kopieren und an andere Stelle als Wert einfügen
Liebe Grüße
Rene Martin
Einfach genial,
Sie machen einem Freude! ! !
Einfach soo logisch, dass ich mir immer wieder die Frage stelle,
warum komme ich nicht einmal selbst auf solch logische Lösungen.
Ich hoffe, dass ich ihre Geduld und Zeit nicht zu sehr in Anspruch nehme.
Ganz herzlichen Dank. Bleiben Sie gesund und zuversichtlich
C. S.
Christian ist irritiert. Zwar übertragen und übernehmen die Arrayfunktionen (SORTIEREN, FILTERN, SEQUENZ, …) nicht die Formatierungen:
Aber wenn man mit dem Gleichheitszeichen auf einen Bereich zugreift, wird in der ersten Zelle das Zahlenformat übernommen, in den übrigen nicht:
Erklärbar – dennoch: irritierend.
Hallo Rene,
Ich hätte da nochmal eine Frage zu den Matrixfunktionen. Wenn ich die Rückgabematrizen in einen Bereichsnamen packe, funktioniert der auf dem Tabellenblatt wunderbar. Wenn ich allerdings diese Matrix über den Bereichsnamen in einer Datenüberprüfung als Liste ausgeben möchte, kann ich ihn zwar über F3 ansprechen, erhalte dann aber eine Fehlermeldung.
Gruß
Hannes
Hallo Hannes,
du musst den Namen im INDIREKT in einen Bezug umwandeln. Meinst du das?
Liebe Grüße :: Rene
Hallo Rene,
ja genau, da sind einige Haken drin.
Die Liste in der Datenüberprüfung übernimmt offensichtlich nur Werte aus Bereichen die auf einem Tabellenblatt liegen und keine Rückgabewerte aus Funktionen.
Aber wenn ich innerhalb einer „formatierten“ Tabelle einen benannten Bereich anlege passt sich dieser Bereich auf die Tabellengröße an und die Werte können auch in der Datenüberprüfung dynamisch verwendet werden.
Wenn ich über die Sequenz-Funktion Werte ermittle und die in einen Bereichsnamen packe, werden die Werte innerhalb eines Tabellenblattes zurückgegeben, innerhalb der Datenüberprüfung jedoch nicht als Liste übernommen.
Die Problematik ist wahrscheinlich, dass die Datenüberprüfung ausschließlich Werte aus dem Tabellenblatt ausliest, daher auch die Bezugsherstellung mit der Indirekt-Funktion.
Keine Ahnung, ob man das irgendwie austricksen kann.
Gruß
Hannes
PS: hier mein „Versuchsaufbau“ zu dem ich ein bisschen was erzählen könnte. Kannst ja vorab schon mal einen Blick drauf werfen, ob das interessant sein könnte
Hi Rene,
sag mal: kann man in intelligenten Tabellen keine Matrixfunktionen verwenden? Hintergrund: ich möchte gerne mit der Funktion SORTIEREN oder SORTIERNNACH eine Liste sortieren und diese sortierte Liste als Basis für eine Tabelle verwenden. Also so:
Und dann passiert:
Hallo Johannes,
eine kleine Überlegung: (Intelligente) Tabellen „denken“ nur zeilenweise oder in Bezug auf eine ganze Spalte. Also:
=[@Umsatz]*19%
oder:
=SUMME(Tabelle1[Umsatz])
analog:
=TEILERGEBNIS(109;[Umsatz])
DAS kollidiert mit einer Arrayfunktion (EINDEUTIG, SORTIEREN, SORTIENNACH, FILTER), die dynamisch einen Bereich definiert oder ZUFALLSMATRIX und SEQUENZ, in die die Größe eines Bereichs eingetragen wird.
Moin Rene,
Danke Dir für die ergänzenden Infos 🙂 Ich habe es jetzt so gelöst … So hat sich auch der Vorteil ergeben, dass es etwas übersichtlicher ist 🙂
Sehr geehrter Herr Martin,
ich habe eben begonnen, Ihre Übungen zu den statistischen Funktionen durchzuführen.
Bei dem Thema Häufigkeit sehe ich eine Differenz zwischen dem, was Sie zeigen und dem, was ich gemacht habe, obwohl die Zahlen, die Sie benutzen, die Zahlen sind, die ich auch genommen habe (ich habe diese Zahlen von Ihrem Beispiel abgetippt).
Ich verstehe nicht den Unterschied, obwohl ich wie Sie die Funktion Häufigkeit verwendet habe.
Das betrifft auch das Ausrechnen der Häufigkeit als Matrix. Auch hier unterscheiden sich die Zahlen.
Zur Überprüfung schicke ich Ihnen meine Datei mit.
Hallo Herr F.,
HÄUFIGKEIT liegt in zwei Varianten vor: als Matrixfunktion und als „normale“. Wenn Sie im „neuen“ Excel HÄUFIGKEIT verwenden, wird es als Arrayfunktion interpretiert: wie viele Daten sind bis zu Wert x, wie viele darüber:
Bei den „älteren“ Excel-Versionen ist dies nicht nötig. So kann ich die Funktion runterziehen. Dazu muss der Datenbereich fixiert werden. Die Daten werden also kumuliert:
Da HÄUFGKEIT matrixfähig ist, muss ich (in denen neueren Excel-Versionen) nicht mehr mit Umschalt + Strg + Enter beenden. Dann werden die einzelnen Bereichen berechnet:
Lieber Herr Martin,
Excel nervt nicht, ich weiß nur noch nicht ob es kann was ich gerne hätte.
Ich müsste Zellen in Spalten verknüpfen, wenn diese einer Bedingung in einer anderen Zelle
Entsprechen. So wie SummeWENN ich diese Zellen zusammenaddieren kann ist die Frage ob
Ich den Inhalt der Zellen auch verkettet ausgeben kann.
Vielen Dank
Bevor ich eine Antwort gebe, Frau L.,
zwei Gegenfragen: sind die Daten sortiert (nach den Kategorien, nach denen sie gruppiert werden sollen) und: haben Sie die Funktionen FILTER und EINDEUTIG? Haben Sie XVERWEIS? (damit geht es „recht einfach“)
Liebe Grüße
Rene Martin
So ist es mir recht,
leider kein XVERWEIS und kein WENNS… hoffe auf ein besseres 2021…
Eindeutig? Index? Geht nicht mit Liste sondern nur Matrix oder Bezug, damit kenn ich mich nicht aus…
Gruppiert nach der Nummer insofern dass sie untereinander geschrieben wurden (händisch…) allerdings Filter vorhanden
Die erste Spalte soll als Referenz herangezogen werden die letzte Spalte beinhaltet die Informationen die ich gerne zusammen verkettet hätte, also
Für 20-44 sollte dort MFM, MFM; CPS, PIB stehen, fantastisch wäre, wenn jeder Wert nur 1x vorkommen würde, aber soweit wage ich nicht zu träumen…
Beim Trennzeichen wäre ich emotional flexibel
Die erste Spalte wird in einem Übersichtssheet sozusagen als Einzeiler ausgegeben.
Vielen Dank
Hallo Frau L.,
ich hoffe, Sie haben die Funktion TEXTVERKETTEN. DAMIT klappt es.
Entweder Sie setzen eine Pivottabelle auf die Liste auf, gruppieren die Daten und verketten dann die Infos der letzten Spalte. Dann haben Sie allerdings Duplikate.
Oder Sie erstellen zwei Pivottabellen: eine für die gruppierten Infos der ersten Spalte und eine mit den Werten der ersten und letzten Spalte. Und verketten so die Texte.
Die erste Formel lautet:
=TEXTVERKETTEN(„;“;FALSCH;BEREICH.VERSCHIEBEN($I$1;
VERGLEICH(L2;$A$2:$A$27;0);0;
VERGLEICH(L2;$A$2:$A$27;1)-
VERGLEICH(L2;$A$2:$A$27;0)+1))
Die zweite:
=TEXTVERKETTEN(„;“;FALSCH;BEREICH.VERSCHIEBEN($M$31;
VERGLEICH(O32;$L$32:$L$51;0);0;
VERGLEICH(O32;$L$32:$L$51;1)-
VERGLEICH(O32;$L$32:$L$51;0)+1))
Klappt das? Ist es das, was Sie möchten?
Angelika ruft erneut an: „Hallo Rene: SVERWEIS kann DOCH zwischen Groß- und Kleinschreibung unterscheiden. Schau mal, ich hab’s gefunden!“
Prinzipiell glaube ich andere Menschen NICHT. Ich schaue die Datei an, die sie mir geschickt hat:
„Schau“, sagt sie, „der Text arnstein steht einmal groß- und einmal kleingeschrieben in der Liste. Wenn ich die Liste nun sortiere, und den Parameter WAHR beim SVERWEIS verwende, findet Excel den unteren, großgeschriebenen Eintrag.“
Ich prüfe das und trage den Text in Kleinbuchstaben ein: „arnstein“.
Auch hier wird der untere gefunden. Ich stutze: „Angelika: deine Liste ist nicht sortiert. Probier mal aus: =CODE(„A“) ergibt 65, =CODE(„a“) liefert 97. Arnstein ist kleiner als arnstein. Du musst anders herum sortieren.
Wir probieren es aus:
und auch:
Beide Male wird der untere Text gefunden. Die folgende Vermutung liegt nahe:
SVERWEIS „läuft“ bei der Verwendung des Parameters WAHR in [Bereich_Verweis] in einer Liste so lange nach unten bis ein größerer Wert als der gesuchte gefunden wird. Dann „stoppt“ SVERWEIS und gibt den Wert der zuletzt gefundenen Zeile zurück. Dabei wird weder bei WAHR noch bei FALSCH zwischen Groß- und Kleinschreibung unterschieden (wie fast an kleiner Stelle in Excel).
Fazit: Wenn man Texte in Listen mit SVERWEIS suchen muss (was durchaus sein kann – Artikelnummern, Personalnummer, Länderkennzeichen, etc. die alphanumerisch aufgebaut sind), sollte man NIEMALS die Liste sortieren und mit dem Parameter WAHR (oder ohne diesen Parameter) in SVERWEIS arbeiten. Schließlich möchte man ein eindeutiges Ergebnis. WAHR ist nur bei numerischen Reihen sinnvoll, beispielsweise: Umsatzzahlen, Erlöse, Gewinne, Kosten, Kilometer … von Betrag x bis Betrag y. Oder Datum von d1 bis d2.
Ich glaube, Angelika war mit meiner Erklärung einverstanden. Ganz sicher bin ich mir nicht. Ich werde sie noch einmal anrufen.
Angelika fragt weiter: „Kennst du das Problem? Ich habe eine Liste in der Begriffe mehrmals in unterschiedlicher Schreibweise vorliegen – manchmal in Großbuchstaben, manchmal nur in Kleinschreibweise. SVERWEIS findet leider nur den ersten Eintrag – egal wie er geschrieben ist.“
Bevor ich antworten kann, beantwortet Angelika ihre Frage selbst: „Ich habe vor Kurzem einen Artikel von Martin gelesen; dem Tabellenexperten – er beschreibt dort das Problem mit der Lösung IDENTISCH. Diese Funktion unterscheidet Groß- und Kleinschreibung.“
https://www.tabellenexperte.de/wie-sverweis-nur-richtig/
„Ja“, pflichte ihr bei: „den Artikel habe ich auch gelesen. Ich habe es ausprobiert – ja, er hat recht. Und: seine Lösung funktioniert so!“
Lieber Herr Martin,
Ich muss zugeben, Excel nervt mich normalerweise nicht, aber ev beschäftige ich mich zuwenig
Damit..
Jetzt habe ich einen neuen Job und muss SVERWEISE nachvollziehen, prinzipiell fühle ich mich dazu
In der Lage, allerdings habe ich heute als Spaltenindex folgendes gefunden.
=SVERWEIS($A393;’specs quotation‘!$F$14:$DD$150;F$2;FALSCH)
Was bedeuted F$2
Vielleicht können sie mir ja helfen.
Vielen Dank
Judith
Ps: ich liebe ihre Seite :-J
Hallo Judith,
danke für das Lob.
Die Antwort ist (wahrscheinlich) einfach:
Schauen Sie mal in dem Tabellenblatt nach, welcher Wert in der Zelle F2 steht. Mit Sicherheit die Nummer der Spalte aus der der Wert des Bereichs !$F$14:$DD$150 des Blattes „specs quotation“ geholt wird.
Ich vermute, dass der SVERWEIS in mehreren Zellen der gleichen Spalte verwendet wird – deshalb wurde die Zeile 2 fixiert, also statt F2 wurde geschrieben F$2. Vielleicht wird er auch in mehreren Spalten verwendet. Was steht in G2? Was in H2? Wenn der SVERWEIS auch in mehreren Spalten benutzt wird, dann bedeutet F (ohne $-Zeichen) ein relativer Bezug – das heißt: in der Spalte daneben wird der Wert aus G$2 verwendet.
Wenn SVERWEIS nur in einer Spalte zu finden ist, hätte man auch $F$2 schreiben können, F$2 ist aber auch okay.
verstehen Sie die Ausführung?
schöne Grüße aus dem kalten und sonnigen München
Rene Martin
Lieber Herr Martin,
Vielen Dank, in Zelle F2 steht leider nix und in allen Formel Zeilen nach unten steht immer F$2 (eh klar),
die Info die in der Zelle aufscheint steht in Spalte :-J…
Es braucht definitiv Smiley in Outlook…
Ev nervt es ja doch, noch so kurz vor Weihnachten…
Danke
Lg Judith
Hallo Judith,
klicken Sie mal bitte auf die Zelle mit der Formel SVERWEIS.
klicken Sie mal auf den Funktionsassistenten fx neben der Eingabezelle.
Dort müsste der Werte von F$2 angezeigt werden:
Die Erleuchtung kam gerade, yup, in F2 steht 5, dass ist die 5te Spalte in der der Wert
Steht ausgehend von der Matrix.. Excel nervt doch nicht J
Vielen Dank!
Liebe Grüße ins wunderschöne München!
Frohe Feiertage und einen guten Rutsch ins nächste Jahr!
Judith
Hallo Rene!
Benötige bitte deine Hilfe.
Zum Jahresende fallen die Auszahlungen der Subventionen an.
Die vorgelegten Unterlagen entsprechen nicht immer den Formvorschriften.
Damit bei dem Jahresbericht zu lesen ist warum etwas nicht anerkannt wurde habe ich die beiliegende Tabelle erstellt.
Nun möchte ich, dass bei der Zusammenfassung der Paare bei den Anmerkungen und Beleg-Nr. die Ziffern aus der Aufstellung jeweils in einer Zwelle eingetragen werden.
Bitte um deine bewährte Hilfe.
Bleib gesund,
mit besten Grüßen
Peter
Meine erste Überlegung: mit VERGLEICH(F28;tbl_KF_Kosten[Turnierpaar];1)-VERGLEICH(F28;tbl_KF_Kosten[Turnierpaar];0) kann ich die Differnez zwischen letzten gefundenem Wert und erstem gefundenem Wert ermitteln. BEREICH.VERSCHIEBEN liefert mir den Bereich; TEXTVERKETTEN fasst sie zusammen. Das Problem hierbei ist jedoch, dass die Werte nicht gruppiert untereinander stehen und auch nicht alphabetisch sortiert sind. Also: zweiter Versuch: FILTER:
=TEXTVERKETTEN(",";WAHR;FILTER(tbl_KF_Kosten[Bel
Klub];tbl_KF_Kosten[Turnierpaar]=F28;""))
Und damit geht es hervorragend!
Ich verwende beim Programmieren und auch oft in Excel gerne das Pipe-Zeichen (senkrechter Strich) „|“ ([Alt Gr] + [<] – auf der Schweizer Tastatur [Alt Gr] + [7]; [ALT] + 1 2 4) als Trennzeichen. Dabei bin ich ziemlich sicher, dass es in einem „normalen“ Text nicht vorkommt. Also beispielsweise:
Brummbär|Pimpel|Happy|Chef|Hatschi|Schlafmütz|Seppel
Umso erstaunter war ich über das Ergebnis der folgenden Formel:
Warum liefert die Formel
=ZÄHLENWENNS(Tabelle2[@[Jan 20]:[Dez 20]];"I")den Wert 6. Ich habe eine Weile überlegen müssen, bis ich verstanden habe, dass der Buchstabe „I“ nicht „|“ ist – bei einigen Schriften sehe die beiden Zeichen sehr ähnlich aus. Vielleicht sollten wir – analog dem Türkischen – auch ein Punkt auf das große I setzen: İ.
Und wieder eine „gefundene“ Zeichnung. Diesmal keine Karrikatur – sondern eine Frage: Sagt Pythagoras nicht etwas anderes?
Wäre gemäß Excel die Länge nicht
=WURZEL(2*1,5^2)
also: 2,1213?
Seit ein paar Tagen habe ich in Microsoft 365 die neue Funktion LET. Damit kann man Formeln unter einem Namen ablegen, um sie (mehrmals) wieder zu verwenden. Beispielsweise so:
=ZELLE(„dateiname“)
oder besser:
=ZELLE(„filename“)
liefert den Namen und Speicherort der aktuellen Datei:
Benötigt man nun den Pfad, kann man das Zeichen „[“ suchen (oder finden):
=SUCHEN(„[„;D3)
liefert in unserem Beispiel 19. Bis ein Zeichen vor diesem Zeichen kann von links der Text herausgelöst werden:
=LINKS(D3;D4-1)
liefert hier: D:\Eigene Dateien\
Oder zusammengefasst:
=LINKS(ZELLE(„dateiname“);SUCHEN(„[„;ZELLE(„dateiname“))-1)
Darin wird nun zwei Mal die Funktion ZELLE(„dateiname“) verwendet. Man kann sie an einen Namen in der Funktion LET übergeben, beispielsweise an „Dateiname“:
=LET(Dateiname;ZELLE(„dateiname“);LINKS(Dateiname;SUCHEN(„[„;Dateiname)-1))
Allerdings: Das Ergebnis ist länger als die vorherige Funktion. Also ein kürzerer Funktionsname:
=LET(DN;ZELLE(„dateiname“);LINKS(DN;SUCHEN(„[„;DN)-1))
Das erhöht allerdings nicht gerade die Lesbarkeit!
Fazit: LET ist sicherlich eine nützliche und praktische Funktion. Allerdings kommt sie wohl erst bei sehr langen Formeln zum Einsatz. Und auch nur dann wenn Formelteile sich mehrmals wiederholen. Legt man diese Teil unter einem (langen) sprechenden Namen ab, wird das Ergebnis weder kürzer noch gut lesbar. Ich fürchte, sie wird wohl in den wenigsten Berechnungen in Excel in Microsoft 365 Einzug finden …
Hallo Rene!
Nach längerer Zeit wieder eine Bitte!
Durch den Corona-Virus musste ich die Termine für meine ABO-Vorstellungen selbst zusammenstellen.
Die Auswahl der Stücke war in ‚ABO Stücke‘ bald erstellt. Mit Pivot habe ich mir die Theaterstücke zusätzlich unterschiedlich angeordnet.
In diesen Tabellen wollte ich die Zellen färbig (Der Leuchtturm) markieren, die in ‚ABO Stücke‘ grün sind. Das war mir nicht möglich.
Im Internet habe ich entdeckt, dass eine Zellfärbung in der Pivot-Tabelle nur mit den dortigen Daten möglich ist.
Nachdem Excel so viele Möglichkeiten bietet die Daten aufzubereiten und darzustellen meine oben erwähnte Bitte
um eine Lösung.
Mit freundlichen Grüßen
Peter
Hallo Peter,
das geht so nicht. Excel kann nicht per Formel Formatierungen abfragen.
Du benötigst im Tabellenblatt „ABO Stücke“ eine Hilfsspalte (bspw. Premiere: ja/nein)
und kannst dann mit einer Formel in der bedingten Formatierung anzeigen lassen:
=UND(INDEX(‚ABO Stücke‘!$F:$F;VERGLEICH($A15;’ABO Stücke‘!$D:$D;1))=“ja“;
B$14=INDEX(‚ABO Stücke‘!$B:$B;VERGLEICH($A15;’ABO Stücke‘!$D:$D;1)))
wirf mal einen Blick auf die Datei – ich habe drei Mal „ja“ eingefügt.
Liebe Grüße Rene
Hallo Rene,
danke für die schnelle Hilfe und den Hinweis.
Da stand ich auf der Leitung.
Klar, dass Excel nur mit Zahlen und Buchstaben arbeiten kann. Die Auswahl mit farbiger
Zelle ist optisch leichter zu überblicken, darum habe ich sie angewendet aber nicht bedacht,
dass Pivot natürlich einen ‚echten‘ Wert benötigt.
Mit besten Grüßen Peter
gerne, Peter,
stimmt: EXCEL kann nur Zahlen und Texte verarbeiten. Mit VBA hätte man die Möglichkeit zu überprüfen:
If Zelle.Interior.Color = …
Liebe Grüße Rene
Hallo Her Martin,
ich bin auf der Suche nach einer Info zu Excel auf ihre Seite „https://www.excel-nervt.de/das-nichts-in-excel/“
gekommen und habe folgende (aus meiner Sicht) falsche Aussage gefunden:
zuerst der betreffende Textabschnitt:
———–
Text, leerer Text oder kein Text
Ähnlich wie bei Zahlen geht Excel bei Texten vor. Eine Zelle kann leer sein, kann Text oder eine leere Zeichenkette beinhalten. Letzteres kann das Ergebnis einer Funktion sein oder auch direkt eingegeben werden:
=““
Die folgenden drei Abfragen liefern dann WAHR als Ergebnis:
=WENN(ISTLEER(A1);…
=WENN(A1=““;…
=WENN(ISTTEXT(A1);…
———–
Wenn ich aber einer Zelle A1 den Wert „“ (also leeren String) zugewiesen habe, liefert mir die Formel
=WENN(ISTLEER(A1);…
leider nicht den Wert wahr, sondern falsch. Die Zelle wird nicht als „leer“ erkannt.
Hiermit habe ich übrigens genau ein Problem. Ich habe nämlich das „ISTLEER“ oft benutzt und zu spät bemerkt, daß es nicht wie erwartet funktioniert. Jetzt versuche ich, die Formeln zu ändern (z.B. durch Abfrage auf Länge = 0) und die vorhandenen „“ aus Wertfeldern zu eliminieren. Leider habe ich für beides bisher noch keine ganz einfache, schnelle und sichere Vorgehensweise gefunden.
Mit freundlichen Grüßen
Michael Richter
Hallo Herr Richter,
ich freue mich immer sehr, wenn Leserinnen und Leser auf meine Artikel reagieren – Kritik äußern, Dinge korrigieren, die ich falsch behauptet habe oder Lob äußern.
Sie haben recht – die Artikel, die Sie unter „über“ und „schade“ finden, sind recht alt und sollten überarbeitet werden. Umgekehrt: wenn Sie „ISTLEER“ in das Suchfeld eintragen, finden Sie weitere Artikel von mir zu diesem Thema.
Liebe Grüße
Rene Martin
Hallo Herr Martin,
Und jetzt noch Lob:
Ihre Antwort auf meine Zuschrift find ich vorbildlich. Außerdem freue ich mich immer, wenn jemand im Internet nützliche Informationen zur Verfügung stellt, und Ihre Seiten sehen „recht gut gemacht“ aus (ich habe sie allerdings bisher noch nicht näher angesehen, daher nur eine Beurteilung des Eindrucks auf den ersten Blick).
Liebe Grüße
Michael Richter
War das früher besser? Zumindest ging das früher!
Vorn vorne: die Excel-Funktion HÄUFIGKEIT ist eine Matrixfunktion. Mit ihrer Hilfe (ebenso wie mit ZÄHLENWENN oder SUMMENPRODUKT) kann man die Häufigkeit von Daten in einer Liste bestimmen, beispielsweise, wie oft Notenwerte vorhanden sind. Dabei muss Die Funktion als Matrixfunktion verwendet werden, das heißt mit [Umschalt] + [Strg] + [Enter] beendet werden:
Das funktioniert bei exakt vorkommenden Werten, aber auch bei „Zwischenwerten“ – von – bis:
Man kann die Funktion aber auch als eine Funktion verwenden und runterziehen – dann werden die Werte kumuliert:
Nein – so konnte man das früher machen – vor den Spillfunktionen (den Arrayfunktionen SEQUENZ, SORTIEREN, EINDEUTIG, …) machen. Seit Microsoft diese Funktionen und diese Arbeitstechnik eingeführt hat, erzeugt HÄUFIGKEIT beim Markieren eines Wertes zwei Ergebnisse: Wie oft taucht dieser Wert auf und wie viele andere Werte sind vorhanden.
Und heute? – Man muss HÄUFIGKEIT mit einem @ entwerten, dann hat man wieder die gleiche Funktionalität wie früher:
Danke an Mourad Louha für diesen Hinweis.
Mal nichts Nervendes. Ein Gimmick zum Schmunzeln:
Vor Kurzem habe ich folgenden Text gefunden:
D1353 M1TT31LUNG Z31GT D1R, ZU W3LCH3N GRO554RT1G3N L315TUNG3N UN53R G3H1RN F43H1G 15T! 4M 4NF4NG W4R 35 51CH3R NOCH 5CHW3R, D45 ZU L353N, 483R
M1TTL3W31L3 K4NN5T DU D45 W4HR5CH31NL1ICH 5CHON G4NZ GUT L353N, OHN3 D455 35 D1CH W1RKL1CH 4N5TR3NGT. D45 L315T3T D31N G3H1RN M1T 531N3R 3NORM3N L3RNF43HIGKEIT. 8331NDRUCK3ND, OD3R? DU D4RF5T D45 G3RN3 KOP13R3N, W3NN DU 4UCH 4ND3R3 D4M1T 83G315T3RN W1LL5T
Kann man / kann ich selbst solche Texte produzieren? Mit Excel natürlich! Klar die Funktion WECHSELN hilft dabei:
Ich trage einen Text in die Zelle A3 ein. Die Funktion GROSS verwandelt ihn in Großbuchstaben. In den Zelle C1:G2 stehen die Werte E, I, S, B und A, die durch 3, 1, 5, 8 und 4 ersetzt werden. In C3 befindet sich die Formel
=WECHSELN(B3;C1;C2)
Rüberziehen. Und sich über das Ergebnis freuen:
4L5 GR3GOR 54M54 31N35 MORG3N5 4U5 UNRUH1G3N TRÄUM3N 3RW4CHT3, F4ND 3R 51CH 1N 531N3M 83TT ZU 31N3M UNG3H3U3R3N UNG3Z13F3R V3RW4ND3LT. 3R L4G 4UF 531N3M P4NZ3R4RT1G H4RT3N RÜCK3N UND 54H, W3NN 3R D3N KOPF 31N W3N1G HO8, 531N3N G3WÖL8T3N, 8R4UN3N, VON 8OG3NFÖRM1G3N V3R5T31FUNG3N G3T31LT3N 84UCH, 4UF D3553N HÖH3 51CH D13 83TTD3CK3, ZUM GÄNZL1CH3N N13D3RGL31T3N 83R31T, K4UM NOCH 3RH4LT3N KONNT3. 531N3 V13L3N, 1M V3RGL31CH ZU 531N3M 5ON5T1G3N UMF4NG KLÄGL1CH DÜNN3N 831N3 FL1MM3RT3N 1HM H1LFLO5 VOR D3N 4UG3N.
Hallo Rene, ich mache zurzeit Ihre Ecel Schulung Tipps & Tricks, bis jetzt bin ich begeistert, vielen Dank. Eventuell können Sie mir mit einer Formel helfen? Ich habe zwei Tabellenblätter und möchte Emailadresse, die in Blatt 1 in Spalten aufgelistet sind, zu Blatt 2 einen automatischen Bezu herstellen, sodass dort in einer Zelle die EMailadressen automatisch erscheinen, wenn ich sie in Blatt 1 eingetippt habe. Ich danke bereits für Ihre Hilfe.
Hallo Anna, wenn sich der Cursor auf der Zelle befindet, wo die Email-Adresse stehen soll, genügt ein „=“, dann ein Klick auf das erste Tabellenblatt und ein zweiter Klick auf die Zelle, in der sich die Mailadresse befindet. Achtung: Nicht zurückklicken! Sondern mit [Enter] bestätigen! Dann steht in der Zielzelle beispielsweise =Tabelle1!C3 Hilft das? Oder denke ich zu einfach? schöne Grüße Rene Martin
Lieber Rene, danke für die supr schnelle Antwort. Es ist leider doch ein wenig komplizierter, aber ich denk für Sie ebenfalls problemlos zu lösen. Ich füge ein Beispieldokument ein zum besseren Verständnis. Aus Tabelle1 möchte ich automatische alle Email-adressen von Runde 1 in aufgelistet in ein Zelle im Tabellenblatt ‚Mailverteiler‘ auflisten. Wie lautet die Formel des Zellbezugs? Besten Dank für Ihre Hilfe.
Hallo Anna, hast du eine neueres Excel? Und dort die Funktion TEXTVERKETTEN? Damit geht es LG :: Rene
Hallo Herr Martin,
Bei meiner täglichen Arbeit ist mir ein sehr gravierender Excel Fehler untergekommen der mich fast an Excel zweifeln lässt.
Ich bin sehr guter Excel Anwender wie VBA Programmierer.
Anbei ein Vorher/Nachher Bild und eine Excel Datei, die den Fehler beschreibt und zeigt.
Könnten Sie diesen Fehler vielleicht an Microsoft melden und in einem Ihrer Videos auf „LinkedIn“ kund tun?
Hallo Herr F.,
Vielen Dank für Ihren Hinweis. Kennen Sie meine Seite excel-nervt.de ? Dort sammle ich Bugs, Ungereimtheiten, merkwürdige Dinge, …
Ich habe das Phänomen mit/ohne Blattnamen bereits 2016 veröffentlicht:
Weitere interessante Dinge rund um Blattnamen finden Sie beispielsweise auf
Aber kann diesen Bug gerne noch einmal veröffentlichen.
Andererseits – ich bin leider kein MVP und habe auch keinen direkten Kontakt zu Microsoft. Es gibt eine Seite, über die man Fehler melden kann – allerdings bin ich bei der Anmeldung gescheitert …
Hallo Herr Martin,
ich danke Ihnen für Ihre schnelle Antwort.
Nun fürchte ich, am nächsten Wochenende ihre komplette Web-Seite lesen zu müssen.
Mit einem freundlichen Händedruck
*lach*
Sie müssen nicht alles lesen!
Doof! In einer intelligenten Tabelle habe ich drei Spalten: ID, Vorname und Nachname. XVERWEIS kann einen Vornamen finden:
=XVERWEIS(G2;tbl_Kunden[Nr];tbl_Kunden[Vorname])
XVERWEIS kann ALLE Vornamen (als Matrixfunktion) zurückgeben:
=XVERWEIS(G2:G9;tbl_Kunden[Nr];tbl_Kunden[Vorname])
XVERWEIS kann Vor- und Nachnamen (hätten wir noch mehr Informationen – so auch diese) zurückgeben:
=XVERWEIS(G2;tbl_Kunden[Nr];tbl_Kunden[Vorname]:tbl_Kunden[Nachname])
Aber leider: beides geht nicht: mehrere Spalten zurückgeben von mehreren IDs:
Schade!
Die ersten dunklen Wolken am Horizont der neuen Funktion XVERWEIS.
Wenn die Funktion nicht nur einen Wert aus einer Spalte, sondern aus mehreren Spalten zurückgibt, kann man die Funktion leider nicht mit einem Doppelklick herunterziehen.
Also:
=XVERWEIS(O2;tbl_Kunden3[Nr];tbl_Kunden3[Vorname]:tbl_Kunden3[Ort];“nicht gefunden“)
Dass hiner AutoSum die Funktion SUM steckt, verstanden die Teilnehmer. Aber warum erzeugt ein Klick auf die Schaltfläche „Count Numbers“ die Funktion COUNT?
Manchmal ist es irritierend. Aufgabe in der Excelschulung: Erhöhen Sie den Jahresbeitrag der Blue-Mitgleider um 5 Euro, der anderen Mitglieder um 10 Euro. Ein Teilnehmer arbeitet mit einer Hilfsspalte:
=WENN(J2=“Blue“;5;10)
Ich sage, dass dieses Vorgehen völlig okay sei, dass er aber auch direkt in der WENN-Funktion rechnen könne. Ich korrigiere und klicke auf die Zelle J2:
=WENN(J2=“Blue“;H25;10)
Der Teilnehmer schaut mich irritiert an. Ich füge ein Pluszeichen ein – und schon wird klar, wie gerechnet wurde:
Der zweite Teil muss natürlich noch eingefügt werden:
=WENN(J2=“Blue“;H2+5;H2+10)
Irritierend, verwirrend und nicht konsequent!
In Excelschulungen erzähle ich immer, wenn ich den Assistenten „Daten / Text in Spalten“ vorstelle, dass es in Excel keine Tabulatoren gibt – man kann die Grundeinstellung (die beim Import von Textdateien verwendet wird) ruhig stehenlassen:
Ich werde eines Besseren belehrt: Wenn Sie ein Worddokument oder einen Mailtext aus Outlook nach Excel kopieren, werden die Tabstopps durch Spalten getrennt:
Wenn Sie dagegen Texte IN Zellen kopieren (das heißt: die Zelle mit [F2] editieren) und dann den kopierten Text einfügen, stehen Tabstopps in Zellen. Merkwürdigerweise sieht man sie nicht, wenn der Cursor AUF der Zelle steht – lediglich IN der Zelle werden sie angezeigt:
Der Assistent „Daten / Text in Spalten“ interpretiert die Tabstopps als Leerzeichen!?!
Die Funktion CODE liefert den Wert 32 – den gleichen wie Leerzeichen!?!
Wenn man in den Ersetzen-Dialog einen Tabstopp hineinkopiert und durch ein Leerzeichen ersetzen lässt, funktioniert dies hervorragend.
Fazit: Finger weg von Tabstopps in Exceltabellen! Excel behandelt diese Zeichen nicht konsequent als eigene Zeichen – eine Unterscheidung durch Leerzeichen ist nicht an allen Stellen durchgeführt. Und wenn ein Anwender Texte in die Zellen eingefügt hat – dann bleibt nur noch der Ersetzen-Assistent oder die WECHSELN-Funktion.
Ein Dankeschön an Mourad Louha für seinen Vortrag beim Excelstammtisch Hessen zum Thema Internationalisierung bei Excel und worauf geachtet werden muss, wenn Dateien von einer Sprachregion in eine andere geschickt werden.
Amüsiert habe ich mich über seine Forlie „Kuriositäten“, in welcher er amüsante Dinge bei der Übersetzung einiger Funktionen zeigte: die längste Excelformel (AVRUND.GJELDENDE.MULTIPLUM.OPP.MATEMATISK im Norwegischen), SE.NON.DISP., die einen Punkt am Ende hat, CURRENCY – die Übersetzung von DOLLAR im Griechischen …
Ein Dankeschön an Mourad Louha, der mir seine Präsentzation zur Verfügung gestellt hat.
Heute in der Excelschulung. Ich zeige die Datenüberprüfung. Eine Artikelliste. In die Mengenspalte darf nur eine ganze Zahl eingetragen werden. Ein Teilnehmer fragt, ob man die Eingabe auch so begrenzen kann, dass die Gesamtmenge < 100 sein muss. Klar – kein Problem: Benutzerdefiniert – =SUMME(D:D)<100
Klappt.
Und dann möchte der Teilnehmer, dass die Eingabe nur ganze Zahlen akzeptiert. Leider kann man keine zwei Datenüberprüfungen miteinander verketten.
Man müsste die Formel erweitern, beispielsweise so:
=UND(RUNDEN(D1;0)=D1;SUMME(D:D)<100)
Danke an die Einladung zum Excelstammtisch in Hessen. Auch in Frankfurt – wie bei uns in München – eine geballte Ladung an Wissen, Freude über Excel, Staunen und Spaß.
Klasse war es.
Danke auch an Andreas Thehos, der sich mit dem gleichen Problem rumgeschlagen hat wie ich gestern. Nur: die Aufgabenstellung war eine andere.
Gegeben sei eine Namensliste. Sie ist Basis für eine Pivottabelle. Es erstaunt, dass ein Name zwei Mal auftaucht. Und nein: es sind keine Leerzeichen hinter dem Namen.
Die Ursache: auch hier hat sich ein geschütztes Leerzeichen (ein non-breaking space) mit dem CODE 160 zwischen den Vornamen und Nachnamen geschummelt.
Wie passiert so etwas? Entweder wurden die Daten aus einer Internetseite entnommen – oder von einem Word-Dokument. DORT kann man mit der Tastenkombination [Umschalt] + [Strg] + [Leertaste].
Ich habe mich über Mourad Louhas Kommentar zu den maschinell übersetzten Hilfetexten von Microsoft.
Wer schauen möchte:
https://techcommunity.microsoft.com/t5/deutsch/excel-eindeutig-online-hilfe-nicht-%C3%BCbersetze-terme-und-fehler-in/idi-p/1091294?fbclid=IwAR1-eJ9AQqa6t36yZT3tZhVKbtuoIKiZg-oNm_MVbVQdIfrvrSsW1fCC-x8#M868
Er spricht mir aus der Seele.
Ich habe drei Mal hinschauen müssen. Bis ich verstanden habe:
https://support.office.com/de-de/article/berechnen-einer-laufenden-summe-in-excel-1359bf89-180b-4771-b5b4-c6f6558549c5
Die Zeilennummerierung wurde eine Zeile zu hoch angesetzt – Zeile 1 darf nicht die Zeile neben den Köpfen sein, sondern muss eine Zeile tiefer beginnen. Dann würde es stimmen!
Ist Ihnen das schon aufgefallen? Sie möchten eine laufende Nummer eintragen und berechnen die erste Zelle mit:
=SUMME(A2:$A$2)
Und ziehen die Formel nach unten:
Die Folge:
=SUMME(A$2:$A3)
Das ist zwar richtig, aber doch erstaunlich. Beginnt man in der zweiten Zelle mit
=SUMME($A$2:A3)
wird die Formel nach unten (und auch noch oben) in dieser Form weitergezählt.
Liebe Microsoftis: die neue Funktion EINDEUTIG ist – ebenso wie die anderen fünf neuen ARRAY-Funktionen klasse! Wirklich, ehrlich: super-spitzen Klasse! Brauchbar ohne Ende! Aber, bitte, bitte: übersetzt die Parameter ins Deutsche. Wie bei den anderen fünf Funktionen!
Boah – muss ich mich wieder ärgern!
Ich schaue mir gerade die beiden neuen Funktionen XVERWEIS und XVERGLEICH an (wirklich klasse!) und werfe einen Blick auf die Hilfeseite von Microsoft:
https://support.office.com/de-de/article/xvergleich-funktion-d966da31-7a6b-4a13-a1c6-5a33ed6a0312?NS=EXCEL&Version=90&SysLcid=1031&UiLcid=1031&AppVer=ZXL900&HelpId=xlmain11.chm60676&ui=de-DE&rs=de-DE&ad=DE
Das Beispiel 1 ist völlig falsch! Es wird keine Platzhaltersuche (4) verwendet, sondern eine exakte Übereinstimmung oder das nächst größere Element. Deshalb liefert 1 das Ergebnis 2! Der Parameterwert 4 würde #NV liefern, weil Gra? nicht vorhanden ist. Nur Gra?? oder Gra*.
Boah!
Das Boah geht weiter: Beispiel 2: Der Satz „Beachten Sie, dass diese Methode erfordert, dass Ihre Daten in absteigender Reihenfolge sortiert sind.“ Nein – das erfordert sie nicht – es wird der Wert darüber ODER darunter zurückgegeben.
Beispiel 3 ist korrekt; bei Beispiel 4 hätte ich mir eine vollständige Übersetzung gewünscht:
=XVERGLEICH(4;{5;4;3;2;1})
Nun ja!
manchmal freue ich mich auch über Excel. Kennt ihr das? Probleme, die nicht lösbar scheinen, finden doch eine Lösung.
Kennt ihr die neuen Array-Funktionen in Excel 365? Beispielsweise ZUFALLSMATRIX:
=ZUFALLSMATRIX(10;1;1;10;WAHR)
generiert zehn zufällige ganze Zahlen zwischen einschließlich 1 und 10. Okay.
=SUMME(ZUFALLSMATRIX(10;1;1;10;WAHR))
summiert zehn zufällige Zahlen und liefert ein Ergebnis zwischen 10 und 100. Okay.
Vor einer Weile wollte ein Kunde aus einer Liste von zirka 100.000 Werten 100 Werte zufällig herausgreifen und von diesen den Durchschnitt berechnen. Mit einer Hilfsspalte ist das kein Problem. Jedoch scheinen die Matrixformeln zu versagen, weil {…ZUFALLSBEREICH …} 100 Mal die gleiche Zufallszahl liefert und nicht 100 verschiedene. Die Lösung für dieses Problem liefert ZUFALLSMATRIX:
In A2:A100000 stehen Zahlen. Die Funktion
=MITTELWERT(BEREICH.VERSCHIEBEN($A$1;ZUFALLSMATRIX(100;1;1;100000;WAHR);0))
berechnet einen Durchschnitt für diese 100 zufällig gefundenen Werte. [F9] zum Neuberechnen liefert einen anderen Wert. Markiert man einen Teil der Formel, dann zeigt [F9], dass tatsächlich zufällige Werte ermittelt wurden. Die Zahl 100 kann ausgelagert und erhöht werden. Je mehr man sich 100000 nähert, umso mehr nähert sich der Zufalls-Mittelwert dem echten Mittelwert.
Und wer sich nun fragt: „wer braucht denn so etwas?“ – Das Teilchen heißt Monte-Carlo-Simulation und wird in Mathematik, Physik, Finanzwesen, … seit über 60 Jahren angewandt. Weite Infos – beispielsweise Wikipedia.
Und ich bin begeistert – Excel rechnet so wie ich will!
Und schon wieder eine merkwürdige Darstellung bei verschachtelten Excelfunktionen im Funktionsassistenten. Merkwürdig … aber ich glaube, dass ich nicht Excel, sondern dem Beamer, der Grafikkarte, der Auflösung, … die Schuld geben muss …
Mit Lorenz Hölscher (http://www.software-dozent.de/) haben wir für unsere Exceltage (www.munich-office-group.de) 2019 einen hervorragenden Dozenten gefunden. Er hat über Themen wie sichere Datenqualität und Dateneingabe, Verbesserungen im VBA-Code referiert. Und er stellte die neuen Array-Funktionen vor, die in Excel nun Einzug gefunden haben.
Auch er kann sich manchmal freche Bemerkungen nicht verkneifen, wenn er fragt, warum in Excel die Funktion TEXT in VBA Format genannt wurde – warum HEUTE() in Access Datum() heißt … Und er machte Witze über Praktikanten, die so etwas implementiert haben – solche Witze machen seine Vorträge nicht nur lehrreich, sondern auch amüsant.
Die Frage ist interessant: Aus einer Geburtstagsliste sollen all diejenigen angezeigt werden, die in dieser (laufenden Kalender-)Woche Geburtstag haben. Eine kleine Fingerübung, oder:
Zuerst wird das Geburtsdatum in ein Datum des aktuellen Jahres „transformiert“. Dann wird von diesem Datum und vom aktuellen Tag die ISOKALENDERWOCHE berechnet. Und schließlich beides miteinander verglichen:
Hallo Wolfgang,
und so geht es:
=WENN(ISOKALENDERWOCHE(HEUTE())=ISOKALENDERWOCHE(DATUM(JAHR(HEUTE());MONAT(K2);TAG(K2)));“x“;““)
Schau dir mal die Schritt in der angefügten Tabelle an
Viel Spaß mit KW und Geburtstag
Rene
Lieber René,
vielen Dank. Ich hatte es gleich gestern Abend noch nachgetüfftelt und bin zum Ergebnis gekommen, allerdings hatte ich das Datum anders umgewandelt.
Da sah dann so aus: =WENN(ISOKALENDERWOCHE(TAG(A1)&“.“&MONAT(A1)&“.“&JAHR(HEUTE()))=ISOKALENDERWOCHE(HEUTE());“Happy Birthday“;““)
Viele Grüße
Wolfgang
Ist auch richtig, Wolfgang – meine Lösung ist natürlich besser *lach*
Im Ernst: ich mag es nicht, wenn du ein Datum (intern eine Zahl) in einen Text umwandelst und diese implizit wieder in ein Datum konvertieren lässt. Bei sehr vielen Datensätzen dauert es länger als meine Lösung, die ein Datum als Datum lässt.
Liebe Grüße Rene
Heute mal nichts zu nörgeln. Oder doch?
Microsoft Excel stellt im Insider-Programm die neue Funktion XVERWEIS vor. Andreas Thehos stellt sie auf seinem Kanal vor. Dumm nur, dass diese Funktion nicht abwärtskompatibel ist; das heißt: wer eine ältere Version von Excel hat, sieht leider nur #NAME!
Hier der Link:
Etwas verblüfft war ich in der letzten Excelschulung. Ich löse mit den Teilnehmern folgendes Problem: Es werden in zwei verschiedenen Zellen zwei Monate ausgewählt und die Kosten von – bis werden berechnet. BERICH.VERSCHIEBEN eignet sich hervorragend zur Lösung dieses Problems.
Meine Lösung:
BEREICH.VERSCHIEBEN:
Beginne bei A1.
Suche E1 im Datumsbereich mit der Funktion VERGLEICH und wandere so viele Zeilen nach unten.
Wandere eine Spalte nach rechts.
Ermittle die Höhe des aufzuspannenden Bereichs als Differenz beider Werte Ende – Anfang, die mit VERGLEICH berechnet werden.
Die Breite des Bereichs ist eine Spalte.
Klappt. Ein Teilnehmer präsentiert eine andere Lösung, die er parallel entwickelte:
SUMME(BEREICH.VERSCHIEBEN(A1;VERGLEICH();1:BEREICH.VERSCHIEBEN(A1;VERGLEICH();1))
Mich irritiert der Doppelpunkt. Dann wird mir klar, wie der Teilnehmer gedacht und wie die Formel gearbeitet hat:
Mit =C3 wird eine Referenz auf die Zelle C3 gesetzt. Diese Formel liefert den Wert der Zelle C3. Also steht „C3“ für zweierlei: die Zelle C3 als Objekt, als Bezug, aber auch der Inhalt der Zelle C3.
Und genau so arbeitet seine Formel – Während „meine“ Funktion BEREICH.VERSCHIEBEN den Wert der Zelle (beziehungsweise die Werte der Zellen) zurückgibt, setzt er einen Bezug auf die erste und die letzte Zelle und spannt zwischen ihnen einen Bereich auf, dessen Werte summiert werden.
Verblüffend und clever!
In der Schulung zeige ich eine Funktion (INDEX). Ich zeige sie zuerst mit dem Funktionsassistenten. Der erste Parameter verlangt eine Matrix, die sich auf dem anderen Tabellenblatt befinden. Die anderen beiden Parameter befinden sich auf dem gleichen Tabellenblatt wie die Funktion – hier: D2 und E2. Kein Problem:
Anschließend zeige ich das Gleiche, indem ich die Funktion tippe. Erster Parameter: anderes Tabellenblatt. Zweiter und dritter Parameter – ich muss zurück zum ursprünglichen Tabellenblatt. Excel notiert den Namen des Tabellenblattes (hier: Tabelle2!). „Ärgerlich“ findet ein Teilnehmer. Ich gebe ihm recht …
Hallo René,
Frage am Rande:
Den Mittelwert Teilnahme über alle Spalten Teilnahme, die >0 bekomme ich wie am schicksten? Mittelwertwenn geht nur über einen zusammenhängenden Bereich, zählenwenn auch,…
Am Ende ist die Tabelle so:
Hallo Florian,
ja, das ist richtig: ZÄHLENWENN, MITTELWERTWENN kann keine getrennten Bereiche verarbeiten. Der Grund:
Wie soll man ZÄHLENWENN(A2:A5;C2:C5;“>0“)
verstehen?
Also muss du den MITTELWERT „nachbauen“. Mittelwert ist ja Summe/Anzahl
Also beispielsweise so:
=SUMME(A2:A6;D2:D6;G2:G6)/(ZÄHLENWENN(A2:A6;“>0″)+ZÄHLENWENN(D2:D6;“>0″)+ZÄHLENWENN(G2:G6;“>0″))
die Spalten A, D und G
Oder:
{=SUMME(A2:A6;D2:D6;G2:G6)/SUMME((A2:A6>0)*1;(D2:D6>0)*1;(G2:G6>0)*1)}
als Matrixfunktion mit Umschalt+Strg+Enter beendet.
Zu Erklärung der zweiten Funktion: A2:A6>0 liefert WAHR;WAHR;FALSCH;WAHR;WAHR;WAHR
diese Wahrheitswert kann ich jedoch nicht summieren. Multipliziere ich sie mit 1, so erhalte ich
1;1;0;1;1;1
Und das kann ich summieren.
Es gibt noch weitere Ansätze.
Liebe Grüße
Rene u
Lieber Herr Martin,
Ein Studienkollege meines Sohnes hat auf seinem PC Excel als Programm nicht und benutzte Excel als Online-Version (one-drive). Er wollte dort eine Tabelle transportieren. Er findet aber dort nicht die entsprechende Funktion.
In Excel geht das ja über Einfügen > Inhalte einfügen à Transponieren oder über den rechten Mausklick. Aber in diesem Online-Excel scheint es diese Funktion nicht zu geben. Oder gibt es doch eine Möglichkeit einer Transponierung auf dieser Excel-Website? Vorausgesetzt, daß Sie diese mal benutzt haben?
Hallo Herr F.,
Ich habe nachgeschaut: Excel online hat viele Funktionen nicht – beispielsweise transponieren. Auch die Funktion MTRANS klappt nicht, weil Excel online keine Matrixfunktionen unterstützt.
Man kann die Tabelle natürlich mit Formeln transponieren. Beispielsweise mit:
=BEREICH.VERSCHIEBEN($C$1;SPALTE(A1);ZEILE(A1))
Es funktioniert auch mit INDIREKT
In Excel 2016 wurde das ausschließliche ODER (die Kontravalenz oder antivalente Disjunktion) eingeführt. In den seltensten kaufmännischen, mathematischen oder statistischen Berechnung wird diese Funktion, die im Bereich der Schaltalgebra in der Elektrotechnik und in der Verschlüsselungstheorie, eine große Rolle spielt, benötigt.
Zur Erläuterung: die Funktion ODER (eigentlich ein Konjunktor) bedeutet: das eine oder das andere oder beide. Umgangssprachlich häufig mit und/oder bezeichnet.
XODER meint dagegen ein ausschließliches ODER – also: entweder das eine oder das andere. Aber nicht beide gleichzeitig. Eine Verknüpfungstabelle erklärt dies:
WAHR XOR WAHR = FALSCH
WAHR XOR FALSCH = FALSCH XOR WAHR = WAHR
FALSCH XOR FALSCH = FALSCH
XOR ist also kommutativ. Und auch assoziativ:
WAHR XOR (WAHR XOR WAHR) = WAHR XOR FALSCH = WAHR
analog:
(WAHR XOR WAHR) XOR WAHR = FALSCH XOR WAHR = WAHR
Das erstaunt: drei mit XOR verknüpfte WAHR-Werte liefern also WAHR (und nicht FALSCH, wie man vielleicht vermuten würde)
Ebenso liefern vier mit XOR verknüpfte WAHR-Werte FALSCH, dagegen ist:
WAHR XOR WAHR XOR WAHR XOR FALSCH = WAHR
(die Reihenfolge ist vertauschbar)
Und Excel? Excel berechnet dies korrekt:
ABER: in der Microsoft-Hilfe ist das falsch erklärt
https://support.office.com/de-de/article/xoder-funktion-1548d4c2-5e47-4f77-9a92-0533bba14f37
Das ist nicht korrekt!
=XODER(3>0;2<9) liefert FALSCH. Nicht WAHR, wie auf der Hilfeseite zu lesen ist. Und der Satz:
„Da eine der beiden Prüfungen „Wahr“ ergibt, wird WAHR zurückgegeben.“
ist so auch nicht korrekt!
Der Satz
„Das Ergebnis von XODER ist gleich WAHR, wenn die Anzahl von Eingaben mit dem Ergebnis WAHR ungerade ist, und gleich FALSCH, wenn die Anzahl von Eingaben mit dem Ergebnis WAHR gerade ist.“
ist korrekt.
Danke an Paul für den Hinweis!
Ich weiß – es gibt Schlimmeres in Excel. Trotzdem musste ich schmunzeln: Die Cubefunktion CUBEMENGE verwendet als vorletzten Parameter „Sortier_reihenfolge“. Lustig geschrieben: Ich hätte ihn „Sortierreihenfolge“ genannt – der Unterstrich ist albern. Wahrscheinlich eine Analogie zum letzten Parameter „Sortieren_nach“.
Manchmal verblüfft mich Excel. Beziehungsweise Phänomene, die ich noch nie bemerkt habe.
Man erstelle eine Funktion, die einen leeren Wert zurückgibt, beispielsweise
=WENN(A1=“Januar“;1;““)
Man markiere die Spalte mit den Formeln, kopiere sie und fügen sie als Werte ein.
Mit [Strg] + [↓] springt Excel zu letzten (leeren?) Zelle. Stoppt nicht bei den Zahlen. Überspringt die vermeintlich leeren Zellen. Man sieht zwar nichts, aber die Funktion =ISTLEER liefert den Wert FALSCH. Diese Zellen sind nicht leer.
In der letzten Excelschulung.
Wir verknüpfen zwei Arbeitsblätter. Ich ziehe den Bezug =ErstesBlatt!ErsteZelle herunter. Da ich nicht weiß wie weit, schieße ich weit übers Ziel hinaus. Das Ergebnis sind Nullen.
Ich frage die Teilnehmer, wie man die Nullen unterdrücken kann. Ich erhalte die Antwort: „in den Optionen“. Richtig: Dort kann man in Datei / Optionen / Erweitert in der Kategorie „Optionen für dieses Arbeitsblatt anzeigen“ die Einstellung „In Zellen mit Nullwert eine Null anzeigen“ ausschalten.
Ich erkläre, dass diese Option nicht immer geschickt ist, denn so werden SÄMTLICHE Nullen unterdrückt. Ich möchte es gerne lokal unterbinden.
Wenn ich solche Fragen stelle, erwarte ich eine Antwort. Erwarte ich WENN(=““. Ein Teilnehmer meldet sich und antwortet: „Mit der Funktion ISTLEER“. Ich bin verblüfft. Damit habe ich nicht gerechnet. Stimmt – ISTLEER prüft das Gleiche wie =““. Wirklich? Wir testen es und stellen natürlich kleine Unterschiede fest. Wenn in der Zelle ein Fehler ist. Oder beispielsweise ein leerer String:
=““
Wir lernen – was auf den ersten Blick gleich aussieht, muss nicht unbedingt gleich sein …
Wenn man in (intelligenten / dynamischen / formatierten) Tabellen eine Ergebniszeile einfügt, kann man dort die aggregierenden Funktionen SUMME, ANZAHL, MAX, … verwenden. Hierfür benutzt Excel die Funktion TEILERGEBNIS, die ausgeblendete Zeilen übergeht:
Warum nicht die Funktion AGGREGAT, fragt man sich und reibt verwundert die Augen. Die Antwort ist einfach: Die Tabellen wurden mit Excel 2007 eingeführt. Damals gab es schon TEILERGEBNIS. Die Funktion AGGREGAT hielt allerdings erst in Excel 2010 Einzug in die Tabellenkalkulation. Und wenn etwas einmal drin ist, dann wird es so schnell nicht wieder geändert.
Seltsam: Die Funktion SUCHEN kann Platzhalter („*“ und „?“) verarbeiten – FINDEN nicht!
Danke an Martin Weiß (der Tabellenexperte) für diesen Hinweis.
=======
Die finanzmathematische Funktion zur Auflösung nach dem Zinssatz i lautet:
ZSATZINVEST(Zzr;Bw;Zw)
K0 = 10.000
K10 = 20.000
= 10 Jahre
Gesucht:
= ZSATZINVEST (10;10000;20000) = 7,18%
Hallo Christian,
Sag mal: ist das korrekt: löst die Funktion ZSATZINVEST die Funktion RMZ nach dem Zinssatz auf? (S. 5) Tut das nicht die Funktion Zins?
Hallo Rene,
Hier muss man unterscheiden, ZSATZINVEST ist für eine einmalige Anlage, Laufzeit, BW und Endkapital sind bekannt.
ZINS liefert Ergebnisse bei periodische Zahlungen z. B. den effektiven Jahreszins im Kreditbereich. Hier ist Zinssatz, (mtl.) Rate, Kreditbetrag und Laufzeit bekannt.
####
Wieder etwas gelernt!
Traurig fragte mich eine Teilnehmerin der Excelschulung, was sie denn machen könne. Sie erhält von einem System regelmäßig Listen mit einer leeren Spalte. Jedoch befinden sich einige Zwischenüberschriften in dieser Liste.
Ihre Aufgabe ist es, eine fortlaufende Nummerierung zu erstellen, bei der die Zwischenüberschriften übergangen werden. Mein Vorschlag war folgender: Schreiben Sie in die erste Zelle eine 1. Markieren Sie ab der zweiten Zelle bis zum Ende der Liste. Wählen Sie nur die sichtbaren Zellen aus: Start / Suchen und Auswählen / Inhalte auswählen oder Tastenkombination [ALT] + [;] (also: [ALT] + [Umschalt] + [,].
Schreiben Sie in die erste Zelle die Formel
=MAX($H$5:H5)+1
und beenden die Eingabe mit [STRG] + [Enter]. Damit sie für alle Zellen übernommen wird.
Diese Formel kann man anschließend mit Kopieren / Inhalte einfügen: Werte in Zahlen umwandeln.
Sie war glücklich.
Hallo René,
ich bin auf deine Webseite gestoßen und hoffe, dass es ok ist, eine E-Mail zu schreiben.
ich habe folgendes Problem: Mir steht eine Exceltabelle mit einer Liste von verschiedenen Kostenarten in Spalte A zur Verfügung, z.B. „MasterCard, Adobe“, „Klipfolio 30€“ und „Juni Klipfolio VisaCard“. Diese möchte ich in die Kategorien „Adobe“ und „Klipfolio“ einteilen (nur als Beispiel, in Wirklichkeit sind es natürlich wesentlich mehr Kategorien) und suche deshalb nach genau diesen Stichwörtern im Text. Wird eines dieser Stichwörter gefunden, soll es entsprechend in Spalte B geschrieben werden, so dass ich das ganze später bequem in einer Pivottabelle zusammenfassen kann.
A
Kostenart
MasterCard, Adobe Adobe
Klipfolio 30€
Juni Klipfolio VisaCard Klipfolio
Insgesamt handelt es sich um ca. 1.000 Datensätze und rund 50 verschiedene Stichwörter. Das „Problem“ ist zur Zeit über eine lange verschachtelete Wenn-Funktion gelöst. Ich dachte aber, es gäbe über VBA vllt eine schnellere und unkompliziertere Lösung. Zur Zeit bin ich so weit, dass ich nach einem bestimmten Stichwort (egal wo im Text es steht) suchen lassen kann.
Sub a()
Dim Rng As Range
Dim wks As Worksheet
Set wks = Worksheets(„data“)
Set Rng = wks.Range(wks.Cells(1, 1), wks.Cells(1000, 1)).Find(what:=“Adobe“, lookat:=xlPart, LookIn:=xlValues, MatchCase:=True)
If Not Rng Is Nothing Then Rng.Select
End Sub
Ich komme allerdings jetzt nicht mehr weiter bzw. bin mir auch nicht sicher, ob das überhaupt ein guter Ansatz ist.
Kannst du mir weiterhelfen?
Vielen Dank im Voraus,
#####
Hallo J.,
ich würde das mit einem SVERWEIS lösen. Oder mit der Funktion INDEX und VERGLEICH. Kannst du?
Liebe Grüße
Rene
#####
Hallo Herr Martin,
ich habe eine Art Kalender, also beginnend mit dem 01.01.2018, endend mit
dem 31.12.2018.
Bei der Aufziehung des Datums lasse ich das Wochenende weg.
In einer Extra-Spalte soll vor jedem Montag die Kalenderwochenzahl stehen.
Wenn ich diese Funktion anwende, wird die Kalenderwochenzahl auch vor den
Tagen Dienstag bis Freitag eingefügt.
Gibt es eine Möglichkeit, z. B. über die Bedingte Formatierung, daß die
Kalenderwochenzahl nur neben dem Montag gezeigt wird?
Herzliche Grüße
####
Hallo Herr F.,
ich würde es mit einer WENN-Funktion lösen. Beispielsweise so (wenn in B1) das Datum steht:
=
schöne Grüße
Rene Martin
####
Hallo Herr Martin,
das ging aber recht schnell mit Ihrer Antwort. Vielen Dank.
Ich habe etwas herum experimentiert und diese Lösung benutzt.
Einzig beim Übergang von 2018 zu 2019 scheint dies nicht zu funktionieren, denn der 31.12.2018 ist zwar noch zum alten Jahr gehörig, müßte aber dennoch mit der 1 beginnen, macht es aber nicht:
Herzliche Grüße
####
Hallo Herr F.,
ich bin nicht sicher, ob in Excel 2010 bereits der Parameter 21 in der Funktion KALENDERWOCHE vorhanden war. Schauen Sie mal bitte nach – ich habe hier kein Excel 2010. Wenn das nicht der Fall ist, müssen Sie die Funktion ISOKALENDERWOCHE nachbauen. Anleitungen finden Sie im Internet.
schöne Grüße
Rene Martin
####
Hallo Herr Martin,
vielen Dank für Ihre letzte Antwort. Sie haben natürlich recht: mit ISOKALENDERWOCHE läßt sich das Problem beheben.
Wie ist es aber, wenn man Excel 2010 benutzt. Da gibt es diese Funktion nicht. Wie ließe ich hier das Problem lösen, also nur daß vor dem Montag die Kalenderwochenzahl steht?
Herzliche Grüße
####
Hallo Herr Martin,
den Parameter 21 hat Excel 2010 nicht.
Mal sehen, was ich im Internet finde, bisher habe ich nichts gefunden.
Dennoch vielen Dank.
Herzliche Grüße
####
Hallo Herr Martin,
ich muß mich korrigieren. Der Parameter 21 ist in Excel 2010 vorhanden.
Ich habe wie folgt die Funktion geschrieben:
=
Und es funktioniert auch der Wechsel von der 52. KW 2018 zur KW 1 2019.
Herzliche Grüße
####
Wer nervt mehr? Excel oder Menschen?
Schade.
Der Cursor befindet sich unterhalb einer Liste.
Eine Formel kann erstellt werden indem man mit der Tastenkombination [⇑] + [Strg] + [↑] nach oben markiert.
Mit [Strg] + [Rückschritt/Backspace] gelangt man wieder zu der Zelle zurück, in der man gearbeitet hat:
Leider funktioniert dieses „Zurückspringen“ nicht blattübergreifend. Schade!
Mein neues Excel-Buch – 544 Seiten zum Thema Zahlen, rechnen, Formeln, Funktionen, verknüpfen, knobeln, denken, Probleme in und um Excel lösen. Mein gesamtes Wissen (okay – ein großer Teil davon). Von Erklärungen SVERWEIS, verschachtelte WENN-Funktionen und Datumsberechnungen über die Funktionen der Kategorie Nachschlagen & Verweisen, Textfunktionen und Statistik bis hin zum numerischen Integrieren, Iterationen und Determinatenberechnungen. ich denke – da ist für jeden etwas dabei. Erhältlich in jedem Buchladen: Excel: Zahlen rechnen Formeln: Formeln, Berechnungen und Verknüpfungen in Excel Taschenbuch – 10. Januar 2018 von René Martin (Autor) ISBN-10: 3746064872 ISBN-13: 978-3746064871
Excelschulung. Eine Teilnehmerin beschwert sich:
ZÄHLENWENN und SUMMEWENN & co denken: „ich suche WO – WAS“
VERGLEICH und SVERWEIS verlangen: „ich suche WAS – WO“.
Hätte man das nicht auf die gleiche Art strukturieren können?
Tja!
Manchmal stolpert man (ich) über seine eigenen Füße.
Heute. Excelschulung. Ich erkläre die Funktion INDEX. Ich verweise mit INDEX vom zweiten Tabellenblatt auf das erste Blatt „Diäten“. Ich ziehe mit der Maus den Bereich auf. Bei den ersten beiden Spalten lautet die Formel:
=INDEX(Diäten!A1:B24
füge ich die Spalte C hinzu lautet die Formel jedoch:
=INDEX(Diäten
Schließe ich Spalte D ein finde ich nun folgende Formel:
=INDEX(Diäten!A1:D24
Ich grüble eine Weile über das seltsame Phänomen, bis ich dahinter komme, dass ich ja den Bereich A1:C24 benannt habe: er heißt „Diäten“.
Man sollte nicht Tabellenblätter so beschriften wie die Namen, die in der Datei vergeben wurden. Das führt zu Chaos!
Excel-Schulung. Eine Teilnehmerin präsentiert eine Liste. In einer Spalte stehen Geldbeträge. Jeweils unter einer bestimmten Gruppe befinden sich Zwischensummen. Manche Summenbeträge sind positiv, manche negativ. Sie möchte gerne nur die positiven Zwischensummen und die dazugehörigen darüberstehenden Werte sehen. Das funktioniert mit einem einfachen Filter nicht. Man muss mit einer Hilfsspalte arbeiten. Dort wird überprüft, ob die Spalte eine Zwischenspalte ist – ob der Wert positiv oder negativ ist. Dann wird es als solches gekennzeichnet. Falls nicht, wird der untenstehende Wert übernommen:
=WENN(UND(B2=““;D2<=0);“neg“;WENN(UND(B2=““;D2>0);“pos“;H3))
Jede Formel bezieht sich auf die darunter stehende Zelle. Excel rechnet von links nach rechts, von oben nach unten. Eigentlich muss der letzte, unterste Wert zuerst berechnet werden, dann den zweiten Wert von unten. Auf diesen bezieht sich die Formel darüber … und so weiter bis zur zweiten Zeile.
Wie rechnet Excel? Iterativ? Wahrscheinlich!
Die Hummel besitzt 0,7 qcm Flügelfläche bei 1,2 Gramm Gewicht.
Nach den bisher bekannten physikalischen Gesetzen der Aerodynamik, sowie unserem physikalischen Verständnis ist es unmöglich, bei diesem Verhältnis zu fliegen!
Die Hummel weiß das aber alles nicht. Sie fliegt einfach!
Vielleicht weiß Excel das auch nicht – und rechnet einfach.
Was passieren kann, wenn man einen Text als Text formatiert – darauf habe ich schon hingewiesen. Auch was passiert, wenn man einen (langen) Text als Buchhaltung formatiert:
Amüsant ist dagegen auch folgendes Phänomen: Wenn man einen Text als Datum, Prozentwert oder Währung formatiert:
und dann mit der Funktion LÄNGE weiter rechnet – allerdings mit mindestens zwei Rechenoperationen (beispielsweise LÄNGE – 1 oder LÄNGE x 1), dann wird das Zahlenformat übernommen:
Erstaunlicherweise: FINDEN und SUCHEN liefern auch Zahlen – sie übernehmen jedoch nicht das Zahlenformat.
Hallo Herr Martin,
ich befinde mich derzeit in einem Excel-Grundkurs und bin bei einer „verschachtelten Wenn-Funktion“ auf folgende Problematik gestoßen:
Excel rechnet nicht weiter, sobald der erste „Wahr“wert erreicht ist, was zu logischen Fehlern führt, sofern die Abfragewerte aufsteigend abgefragt werden.
Der „Sonst“Wert wird allerdings korrekt ausgegeben.
In meinem Beispiel habe ich dann die Abfragewerte 25% und 20% umgekehrt und es hat wie geplant funktioniert.
Wo liegt mein Fehler?
Haben Sie eventuell eine Idee zur Vereinfachung der Funktion?
Im voraus vielen Dank für Ihre Hilfe.
Mit freundlichen Grüßen
Jan
Hallo Herr S.,
Sie haben recht: Excel arbeitet Wenn-Funktionen baumartig ab. Also:
Wenn > 80% dann
wenn > 75% dann
sonst: Rest <= 75%
Sie können es aber auch „umbauen“:
wenn <= 75% dann
wenn <= 80% dann
Rest: sonst > 80%
Tipp: ich zeichne manchmal so eine Baumstruktur auf, um es besser zu verstehen.
Tipp 2: schreiben Sie 2 und 5 und nicht „2“ oder „5“. Sie möchten ja mit dem Zahle weiterrechnen. Sie können übrigens auch 2% und 5% verwenden
Hallo Herr Martin,
vielen dank für die schnelle Antwort.
Das ganze ist ja ganz schön verwirrend, gibt es dann noch eine andere Funktion die das Ziel einfacher erreicht?
Schöne Grüße
Jan
Hallo Herr S.
schauen Sie sich einmal die Funktion SVWEIS an – dort werden mehrere Fälle auf einer Ebene abgearbeitet.
Wer in Excel gerne mit Namen arbeitet, weiß die Funktionstaste [F3] zu schätzen. Man kann den Dialog, der die Namen anzeigt aus der bedingten Formatierung, der Datenüberprüfung, den Diagrammen, … heraus aufrufen.
Leider nicht aus den Steuerelementen heraus, wenn dort im Formatierungsdialog ein Wert in eine Zelle geschrieben wird, die einen Namen hat.
F3 geht leider nicht
Danke an Tony de Jonker für diesen Hinweis
Ein Teilnehmer der Excelschulung gestern hat es ausprobiert:
Trage Sie ein Datum ein, beispielsweise [Strg] + [.] -> das aktuelle Datum. In einer anderen Zelle berechnen Sie den Tag davor, also:
=A1-1
Was passiert, wenn das Datum nun 01.01.1900 lautet? Wissen Sie es? Probieren Sie mal aus, welcher Tag in Excel vor dem 01. Januar/Jänner 1900 kam.
Einfach blöde. Ich habe einen Verweis von einer Excelmappe auf eine andere:
vorher …
Diese Mappe wird unter einem anderen Namen gespeichert. Der Verweis wird nun auf die neue Datei gesetzt; bleibt nicht mehr bei der alten:
… nachher
Leider stellt Excel keinen Schalter zur Verfügung, diesen Verweis NICHT mitzunehmen.
Schon irgendwie doof!
In einer Arbeitsmappe befindet sich ein Tabellenblatt – nennen wir es „Jena“. In dieser Mappe befindet sich ein zweites Tabellenblatt, das Bezug auf das erste Blatt nimmt. Die Formeln können dann beispielsweise so aussehen:
=Jena!H4
In einer zweiten Arbeitsmappe gibt es auch ein Tabellenblatt „Jena“. Wird nun das zweite Blatt aus der ersten Daten in die zweite kopiert oder werden die Zellen in die zweite Datei kopiert, so wird der Bezug auf die erste Datei „mitgenommen“ und nicht auf die zweite Datei:
Ärgerlich!
Excel ist an vielen Stellen nicht konsequent. Der Text „1“ ist beispielsweise etwas anderes als die Zahl 1. Dennoch kann man den Text mit 1 multiplizieren.
Ebenso verhält es sich mit WAHR und 1. Eigentlich entspricht Wahr dem Wert 1 und Falsch dem Wert 0. Eigentlich. Multipliziert man die beiden Werte WAHR und FALSCH, beziehungsweise die Funktionen =WAHR() und =FALSCH() mit 1, erhält man 1 beziehungsweise 0. Intern handelt es sich jedoch um einen anderen Wert. Also
=WAHR=1
liefert FALSCH.
Und diese Unschärfe führt auch dazu, das Wahr-Werte nicht addiert werden können:
=SUMMENPRODUKT(C:C=“Briefpapier“)
liefert 0.
=SUMMENPRODUKT((C:C=“Briefpapier“)*1)
dagegen das korrekte Ergebnis:
Eigentlich unterscheidet Excel zwischen Text und Zahl. Eigentlich. Sicherlich kennen Sie folgendes Phänomen:
Eine Spalte ist als Text formatiert:
In einer anderen Spalte stehen ZAHLEN. Diese werden mit der Funktion SVERWEIS als #NV (nicht vorhanden) quittiert:
Das habe ich schon mehrmals beschrieben – beispielsweise in: „Sverweis funktioniert nicht“
Ebenso werden sie bei der Überprüfung auf Gleichheit
{=ODER(K2=$G$2:$G$22)}
(als Matrixformel) korrekt als unterschiedliche Werte erkannt:
Verwendet man statt dem Gleichheitsoperator „=“ jedoch die Funktion IDENTISCH werden die Texte und Zahlen als gleich(wertig) eingestuft:
{=ODER(IDENTISCH(K2;$G$2:$G$22))}
Ebenso übergeht ZÄHLENWENN diesen Unterschied:
Auch hier gilt mal wieder – schade, dass Excel an so vielen unterschiedlich ist, beziehungsweise einfach nicht konsequent. Kein Verlass auf gar nichts!
Kennen Sie das? Sie arbeiten mit Excel, Kinder oder Nichten und Neffen mit libreOffice, ein Freund mit Numbers oder Google Tabellen? Und Sie sind erstaunt, dass es in der einen Tabellenkalkulation Funktionen gibt, die in der anderen fehlt. Nervig und ärgerlich (gerade beim Austausch der Tabellen).
Der Zeitschriftenverlag Heise hat sich die Mühe gemacht, sämtliche Funktionen dieser vier Tabellenkalkulationen aufzulisten und zu vergleichen:
https://www.heise.de/mac-and-i/downloads/65/2/1/7/2/5/5/4/Formelfunktionen_Vergleich.pdf
Beim Durchsehen der Liste fällt auf, dass auch in dem geliebten Excel einige (wichtige) Funktionen fehlen, die in einem der anderen Programme integriert sind:
Ostersonntag, Tagname, Monatsname, BasisInZahl (habe ich noch nie vermisst), Laufzeit, ZGZ, ISEMAIL, ISURL, AKTUEL, FORMEL (heißt in Excel: FORMELTEXT), BEREICH.ÜBERSCHNEIDEN, BEREICH.VERBINDEN, POLYNOM, COUNTUNIQUE, FARBE, UMRECHNEN (entspricht EUROKONVERT), B, KOVARIANZ (heißt KOVAR, KOVARIANZ.P und KOVARIANZ.S in Excel), SCHÄTZER.EXP.VOR.MULT, SCHÄTZER.EXP.MULT, KLARTEXT, REGEXEXTRACT, REGEXMATCH, REGEXREPLACE und 59 weitere Funktionen …
Formeln im Vergleich (Ausschnitt)
Der Problem mit dem Datum hat mich gestern noch beschäftigt.
In der Zelle D3 steht nichts. Erstaunlicherweise liefert =JAHR(D3) keinen Fehler (wie ich vermutet hätte), sondern 1900. Warum?
Die Antwort: Schreiben Sie in eine Zelle das Datum 05.01.1900. Subtrahieren Sie von diesem Datum die Zahl 1. Setzen die Formel fort. Nach dem 01. Januar 1900 folgt der 00. Januar 1900. Dann ein Fehler:
Und das ist der Grund, warum man von einer leeren Zelle die Jahresinformation auslesen kann. Auch JAHR(0) liefert das Jahr 1900. Steht in der Zelle allerdings ein leerer Text („“) oder #NV, dann ist ein Fehler die Folge:
Das heißt im Umkehrschluss: WENNFEHLER(JAHR(D3);““) fängt keinen Fehler ab, wenn die Zelle D3 nicht gefüllt ist. Lediglich wenn in der Zelle kein Datum, also Text steht. Mit WENNFEHLER kann man diese Information also nicht abfangen.
Ich habe mich heute sehr amüsiert. Ich war in einer großen Firma, die Sie auch kennen. Dort haben mir Mitarbeiter eine große Exceltabelle gezeigt, mit der Bitte, ihnen die Formeln zu erklären und möglicherweise zu verbessern. Das Grundproblem tauchte an sehr vielen Stellen auf: In zwei unterschiedlichen Spalten stehen Datumsangaben. Allerdings: nicht in jeder Zelle.
Es sollen die Datumsdifferenzen berechnet werden. Allerdings nicht Ende – Anfang, da die leeren Zellen ein Ergebnis verfälschen würden. Nun hat ein Kollege – wahrscheinlich über Jahre – verschiedene Formeln eingetragen:
Ist okay – hier habe ich nichts zu nörgeln.
Das Leerzeichen stört mich; würde ich nicht machen – besser: „“.
Warum einfach, wenn es auch umständlich geht. D2-C2 entspricht DATEDIF(C2;D2;“d“).
Ganz schlecht: D2-C2 liefert keinen Fehler, wenn eine der beiden Zellen leer ist. Das Ergebnis ist beispielsweise -42780.
Bis Excel 2003 gab es noch nicht die Funktion WENNFEHLER – bis dahin musste man WENN(ISTFEHLER(… schreiben. Falsch und überflüssig!
Gut: beide Zellen werden überprüft!
Die Funktion ISTZAHL habe ich in der Tabelle nicht gefunden.
Wir haben uns amüsiert, weil das gleiche Problem von einem Anwender auf verschiedene Arten gelöst wurde. Das ist nicht konsistent und auch nicht nachzuvollziehen. Aber man kann ja mal vermuten, was die Ursache des Formelwandels war …
Letzte Woche in der Excel-Schulung. Ich erkläre, dass es manchmal besser ist, den Funktionsassistenten zu verwenden, weil dort einige Informationen angeboten werden, manchmal ist die Eingabe über die Tastatur die bessere Wahl, weil Parameter aufgelistet und erklärt werden.
Ein Teilnehmer meldet sich und fragt traurig, warum er keine Formelvervollständigung hat:
och!
Die Lösung war schnell gefunden: In den Option war in der Kategorie „Formeln“ die Option „AutoVervollständigen für Formeln“ deaktiviert.
… und der Teilnehmer war wieder glücklich …
Amüsiert war ich schon. In einer Firma sollte ich letzte Woche Excel-Formeln anpassen. Man erklärte mir, dass jeder Auditvorgang ein Revisionsdatum hat. Möglicherweise auch ein zweites und ein drittes. Wenn es ein drittes Datum gibt, gibt es auch ein zweites. Also: die Tabelle ist gefüllt: entweder 1. Datum oder 1. und 2. oder 1. und 2. und 3. oder alle vier Spalten sind mit einer Datumsinformation gefüllt.
Ein Kollege hatte eine Formel erstellt:
=WENN(H2=““;1;WENN(I2=““;2;WENN(J2=““;3;4)))
Meine Aufgabe war es diese Formel für weitere Datumsangaben anzupassen. Ich war etwas irritiert.
=ANZAHL(G2:J2)
hätte das Gleiche getan. Und ist leichter anzupassen. Die Teilnehmer waren begeistert und glücklich.
Excel-Schulung. Ich erkläre die Funktion SUMME. Anschließend eine Übung. Unter der Spalte mit den laufenden Nummern soll die Summe der Preise gezogen werden. Die Preise befinden sich jedoch in einer anderen Spalte.
Ich schaue einer Teilnehmerin über die Schulter: Sie klickt auf die leere Zelle, in der sich die Summe befinden soll:
SUMME
Der Vorschlag wird verworfen, indem sie auf der erste Zelle der Zahlenkolonne klickt:
Hier beginnt die SUMME
Anschließend tippt sie einen Doppelpunkt:
SUMME bis …
Und schaut mich nun erstaunt an, was Excel „tut“. Zugegeben: Excel schlägt eine Zelle vor bis zu der der Bereich ausgedehnt werden könnte. Jedoch ist die Anfängerin überfordert, weil nach der Eingabe eines Doppelpunktes etwas „anderes“ in der Formel steht.
Vielleicht wäre hier keine Hilfe besser gewesen.
Bei unserem letzten Excel-Stammtisch haben wir uns darüber unterhalten, wann eine Formel einfacher zu verstehen ist: beim Tippen oder wenn man den Funktionsassistenten zu Hilfe nimmt. Es gibt eine Reihe von Funktionen, da erhalten Sie Informationen beim Tippen, beispielsweise SVERWEIS, TEILERGEBNIS oder WOCHENTAG:
Assi vs. Tippen
Bei anderen Funktionen ist es umgekehrt, beispielsweise RUNDEN:
Assi vs. Tippen
Heute habe ich mir die Funktion RÖMISCH angesehen und musste schmunzeln:
Assi vs. Tippen
„Typ ist eine Zahl, …“ ist völlig aussagelos.
„Knapper, knapper und knapper“ ist auch nicht sehr (vielver-)sprechend und wenig aussagekräftig.
Zur Ehrenrettung von Excel (und denen, die Hilfetexte erstellen) muss gesagt werden, dass das QuickInfo erläutert, dass:
Erstes Knapper: LDVLIV für 499
Zweites Knapper: XDIX für 499
Drittes Knapper: VDIV für 499
Alles klar?
Ich bin nicht der einzige Nörgler.
Bei den Vorbereitungen zum einem Seminar „Excel und Statistik“ stoße ich auf der Microsoft-Seite
https://support.office.com/de-de/article/Verwenden-der-Analyse-Funktionen-um-komplexe-Datenanalysen-auszuf%C3%BChren-6c67ccf0-f4a9-487c-8dec-bdb5a2cefab6#__toc309744619
auf eine Erklärung des Zwei-Stichproben F-Tests:
„In dem Tool wird der Wert f einer F-Statistik (oder F-Verhältnis) berechnet. Ein f-Wert nahe 1 beweist, dass die Varianzen der Grundgesamtheiten gleich sind. In der Ausgabetabelle: Wenn f < 1 ist, gibt „P(F <= f) einseitig“ die Wahrscheinlichkeit an, dass ein Wert der F-Statistik beobachtet wird, der kleiner als f ist, wenn die Varianzen der Grundgesamtheiten gleich sind und „Kritischer F-Wert bei einseitigem Test“ einen kritischen Wert kleiner als 1 für die ausgewählte Irrtumswahrscheinlichkeit Alpha angibt. Wenn f > 1 ist, gibt „P(F <= f) einseitig“ die Wahrscheinlichkeit an, dass ein Wert der F-Statistik beobachtet wird, der größer als f ist, wenn die Varianzen der Grundgesamtheiten gleich sind und „Kritischer F-Wert bei einseitigem Test“ einen kritischen Wert größer als 1 für Alpha angibt.“
Conrad Carlberg kommentiert diese Erläuterungen in seinem Buch „Statistical Analysis: Microsoft Excel 2010“ auf Seite 157:
Got it? Neither did I.
Hallo Herr Martin,
ich habe ein Problem. Ich habe in einer Spalte Zahlen. Ich möchten von diesen Zahlen die letzten drei Stellen, also die drei Ziffern vor dem Komma abschneiden. Der Rest soll gerundet werden. Mit welcher Formel mache ich das?
so?
Die Antwort: Dafür gibt es leider keine Formel. Aber wenn in G2 der Wert stehen, dann erhalten Sie den gewünschten Wert mit der Formel:
=RUNDEN(G2;-3)/1000
Enttäuscht?
Hallo Herr Martin,
ich finde in Excel einfach keine Funktion QUARTALSENDE. Konkret: Ich benötige den letzten Tag (als Datum) des Quartals, in dem sich ein Datum befindet. Also beispielsweise:
1.1.2016 -> 31.03.2016
2.2.2016 -> 31.03.2016
5.5.2016 -> 30.06.2016
und so weiter.
Ist das Quartal nicht zu Ende?
Das ist richtig: DIESE Funktion gibt es in Excel leider nicht. Man muss sie zusammenbauen. Wenn in A1 das Datum steht, dann beispielsweise so:
=DATUM(JAHR(A1);MONAT(A1)-REST(MONAT(A1)+2;3)+3;1)-1
oder so:
=DATUM(JAHR(A1);OBERGRENZE(MONAT(A1);3)+1;1)-1
(beide Funktionen suchen den letzten Monat des Quartals, addieren 1 (also verwenden den nächsten Monat) und von diesem Monat den ersten Tag. Davon wird 1 abgezogen. Oder auch so:
=MONATSENDE(A1;REST(3-MONAT(A1);3))
Man ermittelt die Anzahl der Monate, die zu dem Datum dazu gezählt werden muss.
Kennen Sie folgendes Phänomen? In Excel-Schulungen werde ich ab und zu gefragt:
Ich zeige die Funktion SUMMEWENNS, die Teilnehmer schauen mir zu. machen anschließend mit und fragen mich nun, wo denn die Eingabefelder bei Ihnen verborgen sind.
Richtig, wenn man mit dem Funktionsassistent die Funktion SUMMEWENNS öffnet, sieht sie folgendermaßen aus:
Start
Trägt man die Informationen ein, öffnet sich ein weiteres Pflichtfeld – allerdings erst dann:
und go!
Sehr unglücklich gemacht, dass nicht die ersten DREI Parameter angezeigt werden. Kein Trost: Während bei ZÄHLENWENNS die Parameter korrekt angezeigt werden, fehlen sie bei MITTELWERTWENNS ebenso.
Eigentlich sind Namen in Excel ein prima Sache:
Vor allem: Name sind sprechend und können so leicht verwendet werden, wenn man das Konzept verstanden hat. Jedoch das Konzept hat einen großen Haken:
Namen können sowohl an die Datei gebunden sein als auch an das Tabellenblatt. Wenn Sie VBA können, wissen Sie sicherlich, dass sowohl das Workbook-Objekt als auch das Worksheet-Objekt einen Namen haben können.
Kostprobe gefällig: Markieren Sie ein Zelle oder einen Bereich auf einem Tabellenblatt. Geben Sie ihm einen Namen. Kopieren das Blatt in eine andere Datei. Kopieren Sie das Blatt noch einmal in eine andere Datei. Was passiert? Nicht die Datei, sondern die beiden Tabellenblätter haben eine Zelle oder einen Bereich, die den gleichen Namen tragen. Wenn man nun eine Formel verwendet:
Wo sind test2 und test3?
ist nicht klar, von welchem Blatt sich Excel den Bereich zieht. Der Namensmanager zeigt es deutlich an:
Mehrere gleichlautende Namen
Die zuerst erstellten (hinüberkopierten) Namen gelten global für die Abreitsmappe (und darauf nimmt test1, test2 und test3 Bezug), die danach erstellten Namen gelten nur lokal für das Blatt.
Das heißt ganz einfach: Höllisch aufpassen mit den Namen! Und: bloß keine Namen mehrmals verwenden! Sonst ist Chaos vorprogrammiert.
Das heißt auch: gutes Konzept, aber leider nicht ganz durchdacht.
Sehr geehrter Herr Dr. Martin,
Wahrscheinlich haben Sie wesentlich schneller eine Lösung parat als ich, die ich nun schon stundenlang damit rummache und jetzt irgendwie aufgebe.
Es geht um dieselbe Tabelle, also diese Zeitberechnung.
Nun soll aber noch folgendes dazu berechnet werden.
Wenn die Stundenanzahl >= 6 Stunden sollen 0,5 Stunden abgezogen werden, wenn die Stundenanzahl >= 10 Stunden soll eine Stunde abgezogen werden.
Ich habe versucht, mich langsam heran zu tasten und habe sogar die > 6 Stunden und >10 Stunden einzeln herausbekommen. Allerdings bekomme ich nun beim besten Willen diese ganzen Schachteln nicht zusammengesetzt…(Das mit dem „=“ dazu hat auch nicht so funktioniert…)
Haben Sie eine (schnelle) Lösung oder eine passende Erklärung mit einem Link?
Die Tabelle mit meinen Versuchen ist angehängt. Wie ich gelesen hatte, muss man dazu die Uhrzeit wieder in eine normale Zahl formatieren, was ich getan habe.
Herzlichen Dank für Ihre Bemühungen.
####
Stundenberechnung
Hallo Frau P.,
Ich denke, Ihr Problem resultiert aus Folgendem: Sie sollten sich einen Baum malen, der die verschiedenen Fälle auflistet: tagsüber gearbeitet oder nachts? In beiden Fälle: mehr als 10 Stunden – > ja: eine Stunde abziehen. Nein: – > wenn mehr als sechs Stunden: -> ja, 0,5 Stunden abziehen – sonst nichts.
Und so müssen Sie die Formel eingeben.
Kommen Sie damit klar?
####
Hallo Herr Dr. Martin,
Jetzt habe ich gesehen, dass Sie die Sachen ja alle schon richtig ausgefüllt haben.
Das Resultat ist in „Zahl“ formatiert. Ich glaube, es wäre auch mit der „Uhrzeit 37:…“ gegangen. Allerdings nicht mehr in dieser Version.
Ich kann es nur hinnehmen und Ihnen danken, dass Sie es eingerichtet haben. Vom Verständnis bin ich leider noch etliches entfernt.
Aber ich denke, meine Kolleginnen werden sich freuen, endlich eine funktionierende Zeitberechnung zu haben. Bleibt zu hoffen, dass ihnen nicht noch etwas Neues einfällt…
Also noch einmal: Herzlichen Dank!
Ich wünsche Ihnen noch einen schönen Tag.
####
Gerne, Frau Pojer,
wenn Sie es verstehen möchten, machen Sie Folgendes:
Klicken Sie auf eine Zelle mit einer Formel. Klicken Sie auf den Funktionsassistenten.
Sie sehen die Bedingung. Klicken Sie nun in die Bearbeitungszeile auf den Teil den Sie sehen möchten. So können Sie sich den Baum entlanghangeln:
Der Funktionsassistent
Umgekehrt: gehen Sie an ein solches Problem mit einem Blatt Papier und einem Stift ran. Ich „mal gerne Bäume“. Sicherlich kennen Sie diese Ablaufdiagramme: sie helfen:
Ein kleines Diagramm – als Hilfe
Ich weiß; ich bin ja auch nicht glücklich darüber. Wir haben eine Liste, in der in einer Spalte dummerweise Datumsangaben, Texte, Zahlen (Kennziffern) und gar nichts eingetragen wird. Ich habe nun die Aufgabe, über eine Hilfsspalte die Zellen herauszufinden, in denen ein Datum steht. Allerdings: Wenn ich die Funktion ISTZAHL verwende, werden auch die „echten“ Zahlen gefunden. Die will ich aber nicht. Gibt es wirklich keine Funktion „ISTDATUM“? Auch nicht in Excel 2016?
Wo ISTDATUM?
Die Antwort: Wenn es möglich ist, prüfen Sie die interne Zahl, also beispielsweise:
=UND(I4>40000;ISTZAHL(I4))
Oder Sie verwenden die Funktion ZELLE. Sie liefert mit dem Parameter „format“ den Wert D1, der anzeigt, dass es sich um ein Datum handelt:
Geht doch!
Verblüffend – ich kann es nicht nachvollziehen. Es gibt Anwender, die tragen eine Formel in das Suchen-Feld des Funktionsassistenten ein. Manchmal findet er sie allerdings nicht. Ich kann das Phänomen nicht immer nachvollziehen; meisten klappt die Suche.
Wer suchet der findet – ich bin mir nicht so sicher …
Anwender reiben sich verwundert die Augen: In Excel 2013 wurde die Funktion TAGE eingeführt. Damit kann man die Differenz zweier Datumsangaben in Tagen berechnen. Warum diese Funktion? Keine Ahnung – Excel kann doch seit Beginn seiner Existenz Datum2 – Datum1 berechnen. Da Datumsangaben intern als serielle Zahlen verwaltet werden, war das nie ein Problem in Excel. Auch die Funktion =DATEDIF(Anfangsdatum;Enddatum;“D“) konnte dies und hat das Gleiche erledigt. Ein Blick in die Liste der Funktionen: Nein, Excel 2013 hat leider keine Funktion MONATE und auch keine JAHRE. Vielleicht haben sie es vergessen. Wir warten auf die Version 2016. Erstaunt reiben wir uns hier die Augen – nein, sorry, auch in der aktuellen Version 2016 gibt es keine Funktion MONATE oder JAHRE. Geduld … vielleicht in der nächsten Version.
MONATE und JAHRE fehlt noch immer.
Vor ein paar Tagen erreichte mich folgende Anfrage:
Sehr geehrte Damen und Herren,
zu dem in Betreff genannten Thema haben wir noch eine Frage: Die Videoanleitung zum Erstellen eines Internationalen Kalenders konnten wir gut nutzen. In dieser Anleitung wird u.a. geschildert, wie man Feiertage durch eine bedingte Formatierung farblich hervorhebt. Ganz schick wäre es noch, wenn zu diesem farblich markierten Feiertage auch automatisch der Feiertagsname mit angezeigt werden könnte. Ein entsprechendes Tabellenblatt mit diesen Informationen wurde im Verlaufe der Anleitung angelegt. Bei festen Feiertagen wie Neujahr. 1. Mai etc, könnte man dies händisch lösen, doch bei variablen Feiertagen wie Ostern und Pfingsten etc. wäre es wünschenswert, wenn diese gleich automatisch mit angezeigt werden. Leider wurde in dieser Videoanleitung nicht darauf eingegangen. Mit welcher Funktion kann der Feiertagsname automatisch angezeigt werden? Danke schon vorab für Ihre Hilfe.
Mit freundlichen Grüßen / with best regards
#####
Zur Info: Ich habe einen Kalender erstellt, der – nach Änderung des Jahres die Feiertage farblich kennzeichnet. Das klappt mit der bedingten Formatierung und der Funktion ZÄHLENWENN gut und einfach. Die Feiertage (hier: die bayrischen) habe ich auf ein zweites Tabellenblatt ausgelagert.
Die Feiertagsliste
Der Kalender
Ich habe zirka eine halbe Stunde benötigt, damit die Feiertage angezeigt werden – eine hübsche kleine Fingerübung:
Das Ergebnis
Wer knobelt mit? Den ersten Kalender könnt Ihr unter Kalender herunterladen.
Viel Spaß im neuen Jahr mit Excel.
Rene
Einfache Frage – einfache Antwort: Wenn Zellen markiert werden, in denen sich Datumsangaben befinden, summiert Excel die internen seriellen Zahlen. Das kann erstaunen und vielleicht verwirren …
Summe der Datumsangaben ?!?
Ist Ihnen folgender erklärbarer, aber auf den ersten Blick verwirrender Algorithmus aufgefallen?
In einer Exceltabelle befindet sich ein Zahlenblock. Klickt man in der er ersten Zelle daneben auf das Summensymbol, schlägt Excel die Reihe links daneben als Bereich vor. Eine Zelle tiefer wird ebenfalls die Zeile daneben vorgeschlagen. In der dritten Zeile jedoch die beiden Zahl (also die Summen) darüber.
Das Algorithmus, der dahinter steckt, ist klar: Excel überprüft zuerst die Zellen darüber. Befindet sich keine Zahlen darin, werden die Zellen links neben der aktuellen Zelle geprüft (F2). Bei F3 erkennt Excel die Summe darüber und schlägt die gleiche Formel erneut vor. Würde in der Zelle darüber keine Funktion, sondern eine Zahl stehen, würde diese vorgeschlagen werden. In der dritten Zelle F4 werden zwei Formeln über der aktuellen Zelle (mit Zahlen als Ergebnis) erkannt und nun diese vorgeschlagen.
Okay – übersichtlicher wird die Formel sicherlich nicht, aber man kann mit der Funktion N etwas hineinkommentieren. Zu Erläuterung: N(„Beliebiger Text“) liefert den Wert 0. Und an den entsprechenden Stellen in einer Funktion kann man so etwas kommentieren. Einem Teilnehmer meiner Schulung gefiel dies heute sehr.
Kommentare in Funktionen – geht doch!
Ey – ich bin so genervt! Ich weiß, dass ich eine Spalte, in der sich Formeln befinden, kopieren kann und dann über „Inhalte einfügen“ die Formeln in Werte verwandeln kann. Aber was macht die Schweinebacke? – Richtig: Völliger Blödsinn!!! Schreibt einfach ANDERE Formeln rein. Das kanns doch wohl nicht sein“
Kopieren | Inhalte einfügen
Und dann das!!!
Die Antwort: Du hast aus Versehen die Option „Multiplizieren“ gewählt – DIE muss ausgeschaltet werden – auf „Vorgang: Keine“. Dann funktioniert es auch.
Ein kleiner Klick zuviel
Ein Kollege von mir hat mir eine Formel in Excel erstellt. Ich verstehe die Formel aber nicht. Im dritten WENN-Teil fehlt doch ein Teil, oder?
Fehlt da nicht etwas?
Die Antwort: Schauen wir uns die Formel mal genauer an:
=WENN(AJ2=““;““;WENN((AJ2)>14;“zu lang“;WENN(AJ2<=14;“ok“;)))
Der erste Teil ist klar (=WENN(AJ2=““;““): Wenn die Zelle AJ2 leer ist, dann bleibe ich selbst auch leer. Prima.
Im zweiten Teil wird überprüft, ob in AJ2 ein Wert größer als 14 steht:
WENN((AJ2)>14;“zu lang“
Wenn das erfüllt ist, so liefert die Formel den Text „zu lang“. Nun wollte der Kollege überprüfen, ob der Wert kleiner oder gleich 14 ist. Er hat dies mit einer weiteren WENN-Funktion realisiert. Das ist nicht falsch, aber überflüssig.
Ich würde die Formel folgendermaßen schreiben:
=WENN(AJ2=““;““;WENN((AJ2)>14;“zu lang“;“ok“))
Sie liefert das gleiche Ergebnis und ist viel kürzer.
So geht es auch – das ist sogar eleganter!
Warum finde ich die Funktion nicht? Ein Kollege hat in einer Abrechnungstabelle für Kopierer unserer Firma eine Funktion DATEDIF eingefügt, die offensichtlich die Anzahl der Monate zwischen Vertragsbeginn und Vertragsende berechnet. Allerdings – in der Liste der Funktionen taucht sie nicht auf. Wo ist sie denn?
DATEDIF – nicht da!
Die Antwort: Stimmt! Diese Funktion finden Sie nicht in der Liste der Funktionen im Funktionskatalog. Sie wurde aus Kompatibilitätsgründen zu Lotus 1-2-3 aufgenommen. Seit 2003 wurde diese Tabellenkalkulation nicht mehr weiterentwickelt, 2014 wurde der Support sogar eingestellt. Trotzdem finden sich noch immer ein paar Relikte von Lotus 1-2-3 in Excel. Zum Beispiel diese Funktion. Und: Wenn Sie auf das Symbol für den Funktionsassistenten f(x) klicken erhalten Sie die Funktionsargumente im Assistenten.
DATEDIF
TEILERGEBNIS. Ich versteh mal wieder gar nichts. Ich habe eine Liste. Darunter stehen drei Funktionen:
=SUMME(C2:C42)
=TEILERGEBNIS(9;C2:C42)
=TEILERGEBNIS(109;C2:C42)
Drei mal erhalte ich den Wert 20.205.490.
TEILERGEBNIS
So weit, so klar. Wenn ich nun filtere, liefert TEILERGEBNIS mit der 9 das Gleiche wie TEILERGEBNIS mit der 109. Natürlich einen anderen Wert wie die Summe.
TEILERGEBNIS bei gefilterten Daten
Ich schaue in die Hilfe, um den Unterschied zwischen der Konstante 9 und 109 – beides Mal die Funktion SUMME zu ermitteln. Dort lese ich:
„ist eine Zahl von 1 bis 11 (bezieht ausgeblendete Werte ein) oder von 101 bis 111 (ignoriert ausgeblendete Werte), die festlegt, welche Funktion bei der Berechnung des Teilergebnisses innerhalb einer Liste verwendet werden soll.“ HÄ? Ich habe doch ausgeblendet. Trotzdem ist das Ergebnis das Gleiche!
Die Hilfe – nicht wirklich eine Hilfe …
Die Antwort: „ausgeblendete Werte“ ist unglücklich formuliert. Excel meint die Zelle, die SIE ausgeblendet haben (beispielsweise mit der rechten Maustaste oder [Strg] + [9]). Er bezieht sich dabei nur auf „manuell“ ausgeblendete Zellen oder durch Gruppierung ausgeblendete Zellen, aber nicht auf ausgeblendete Zellen durch Filtern!
Beim Ausblenden von Zeilen wird der Unterschied zwischen 9 und 109 beim TEILERGEBNIS deutlich.
Ich habe mal nachgeschaut: Heute, am 07. Januar 2015 verwendet Excel intern die Zahl 42.011. Das erhalte ich, wenn ich die Zelle als Standard formatiere. In meinem openOffice und libreOffice genauso. Allerdings ist der 1. Januar 1900 bei Excel die Zahl 1, in openOffice und libreOffice die Zahl 2. Kann mir das mal einer erklären?
Fehlt ein Tag?
Das ist ganz einfach. Da hat einer nicht aufgepasst! Jedes Jahr, das durch vier teilbar ist, ist ein Schaltjahr. Alle Hundert Jahre ist kein Schaltjahr, alle 400 haben wir wieder ein Schaltjahr. Das heißt: 2016, 2020 und 2024 werden wir ein Schaltjahr haben, im Jahre 2000 hatten wir eines, aber nicht 1900. Die Macher von Excel haben das übersehen. Wenn Sie den 29.02.1900 eingeben, dann erhalten sie ein gültiges Datum, was Sie daran erkennen können, dass die Zahl rechtsbündig steht. Die Macher von openOffice/libreOffice haben dies richtig erkannt und dieses Datum weggelassen. Nun, da Excel im Jahre 1900 anfängt, sind also die ersten beiden Monate falsch. Also: geben Sie keine Datumsangaben zwischen dem 1.Januar 1900 und dem 28.Februar 1900 ein. Aber das haben Sie sowieso nicht vor, oder?
Was mache ich falsch? SUMMEWENN und ZÄHLENWENN können die beiden Platzhalter * und ? verarbeiten, die Funktion WENN jedoch nicht?
Richtig – erstaunlicherweise kann WENN keinen Platzhalter verarbeiten – man muss hier mit
=WENN(LINKS(I5;1)=“P“;G5+20;G5+10)
arbeiten.
Zählenwenn und Summewenn kann mit * arbeiten …
… Wenn jedoch nicht …
Die Formel
=“Hamburg, „&HEUTE()
funktioniert nicht.
Stimmt. Beim Verketten von Texten (übrigens würde die Funktion Verketten das gleiche Ergebnis liefern), wird nicht die formatierte Datumsinformation verwendet, sondern der interne Wert des Datums. Lösung schafft die Funktion:
=“Hamburg, „&TEXT(HEUTE();“TT.MM.JJJJ“)
Oder ein benutzerdefiniertes Datumsformat.
Datum verketten – funktioniert nicht
Mit der Funktion TEXT klappt es …
… oder mit einem benutzerdefinierten Datumsformat (Zahlenformat)
