Open up, it’s the police! – Prove it – sing Roxanne!

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.

Egal, ob müde, hässlich oder reich: vor der Kaffeemaschine sind wir alle gleich.

Warum denn der XVERWEIS, den wir in der Excelschulung geübt haben, nicht funktioniere, will eine Teilnehmerin wissen:

Die Ursache ist schnell gefunden: sie hatte ein Komma statt eines Semikolons getippt.

Eine andere Teilnehmerin gibt ihr den Tipp den Funktionsassistenten zu verwenden, statt die Formel per Hand zu schreiben. Ich pflichte ihr bei.

Es gibt Streifen von Schinken mit einem Hauch von Ananas an Tomatenmus auf zartem Knusperteig. – Lecker Tiefkühlpizza!

In der Excelschulung fragte eine Teilnehmerin, warum die Funktionstaste [F9] nicht funktioniere. Ich hatte erklärt, dass man mit [F9] das Ergebnis eines Teils einer Formel anzeigen lassen kann:

Ich musste es mir zeigen lassen.

Der Grund: [F9] wandelt die Markierung nicht in einen Wert um, wenn man die Maustaste gedrückt hält! Man muss sie loslassen!

Ihre Katze hat heute Morgen unseren Wellensittich gefressen. Oh, gut, dass Sie es sagen – dann bekommt sie heute nichts mehr zu fressen.

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!

Liebster – bevor ich gehe – sag mir die drei magischen Worte! – Bring Bier mit!

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.

Wenn du auch auf jemenden wartest, der sdich auf Händen trägt – gib nicht auf! Deine Beerdigung kommt sicher.

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.

Ich öffnme den Kühlschrank. Die Butter lacht mich an. Ich lache zurück. Fette unter sich.

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:

  1. Markiere die Zellen, die die Datumsangaben enthalten, für die du die aktuelle Woche hervorheben möchtest.
  2. Klicke auf die Registerkarte „Start“ in der Menüleiste.
  3. Wähle „Bedingte Formatierung“ > „Neue Regel“ aus.
  4. Wähle „Formel zur Ermittlung der zu formatierenden Zellen verwenden“.
  5. Gib die folgende Formel ein:

    =UND(WOCHENTAG(A1)<>1; WOCHENTAG(A1)<>2; WOCHENTAG(A1)<>3; WOCHENTAG(A1)<>4; WOCHENTAG(A1)<>5)
    Diese Formel markiert alle Tage außerhalb der aktuellen Woche.
  6. Klicke auf „Format“ und wähle die gewünschte Formatierung für die hervorgehobenen Zellen aus.
  7. Bestätige alles mit „OK“.
    Aktuellen Tag hervorheben:
  8. Wiederhole die Schritte 1-4.
  9. Gib die folgende Formel ein:

    =A1=HEUTE()
    Diese Formel vergleicht das Datum in der Zelle mit dem aktuellen Datum.
  10. Klicke auf „Format“ und wähle die gewünschte Formatierung für die hervorgehobenen Zellen aus.
  11. Bestätige alles mit „OK“.
    Aktuelle Spalte des aktuellen Tages hervorheben:
    Um die gesamte Spalte des aktuellen Tages farblich hervorzuheben, musst du eine Formel in der bedingten Formatierung verwenden, die die aktuelle Spalte prüft. Hier ist eine beispielhafte Formel für die Spalte A:

    =SVERWEIS(HEUTE();A:A;1;0)=HEUTE()
  12. Markiere die gesamte Spalte, in der die Datumsangaben stehen.
  13. Wiederhole die Schritte 2-4 für eine neue Regel.
  14. Gib die oben genannte Formel ein.
  15. Klicke auf „Format“ und wähle die gewünschte Formatierung für die hervorgehobene Spalte aus.
  16. Bestätige alles mit „OK“.
    Dies sollte die gewünschten bedingten Formatierungen für die aktuelle Woche, den aktuellen Tag und die gesamte Spalte des aktuellen Tages setzen. Beachte, dass die genaue Vorgehensweise je nach der Excel-Version, die du verwendest, leicht variieren kann.
    Formularbeginn

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)

Welche Ziele haben Sie? – Feierabend? – Nein, ich meine: längerfristig! – Wochenende

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.

Mein Mann und ich mögen genau die gleichen Dinge. Aber er hat zehn Jahre gebraucht, bis er das kapiert hat.

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!

Vier Stunden Zocken soll süchtig machen. – Ich gehe acht Stunden arbeiten, aber süchtig danach bin ich noch nicht.

Nicht aufgepasst:

In der Excelschulung stoßen wir auf folgendes Problem:

Ein Datenbanksystem liefert die Daten einer Spalte entweder als Jahreszahl – Q1 (oder Q2, Q3, Q4) oder < 2018 Q1.

Angenommen man möchte nach dem Quartal oder dem Jahr filtern. Dann wäre es sinnvoll, diese Informationen in eigenen Spalten zu haben. Ich zeige die drei Techniken:

  1. Daten / Text in Spalten
  2. Blitzvorschau
  3. Formeln

Als Formel, um das Jahr zu extrahieren, verwende ich:

=WENN(LINKS(K2;1)="<";TEIL(K2;3;4);LINKS(K2;4))

Ein Teilnehmer fragt, was denn wäre, wenn statt einem „<“ ein „>“ als erstes Zeichen in der Zelle stünde.

„Nun“, so lautet meine Antwort, dann könnte man überprüfen, ob das erste Zeichen eine Ziffer ist. Beispielsweise so:

=WENN(ISTZAHL(LINKS(K2;1));LINKS(K2;4);TEIL(K2;3;4))

Die Lösung ist leider falsch:

Auch ISTTEXT funktioniert nicht. Das Problem: LINKS(K2;1) liefert immer einen Text – egal, ob es sich um eine Ziffer oder ein anderes Zeichen handelt. Eine Lösung für dieses Problem wäre beispielsweise:

=WENN(ISTFEHLER(LINKS(K2;1)*1);TEIL(K2;3;4);LINKS(K2;4))

Man multipliziert das erste Zeichen mit 1. Ist das erste Zeichen ein Buchstabe oder ein anderes, nichtnumerische Zeichen, ist ein Fehler die Folge. Dieser kann abgefangen werden.

Der Teilnehmer ist zufrieden.

Bin seit zwei Stunden auffer Weihnachtsfeier. Alle voll, ich duze den Chef und hab ne Beförderung rausgehandelt. Jetzt muss ich bloß noch wissen, welche Firma hier feiert.

Ich habe eine Weile suchen müssen, bis ich die Ursache gefunden hatte. Dann haben wir beide gelacht,

Jürgen fragt, ob ich einen Moment Zeit hätte. Einige Werte würden nicht angezeigt werden:

Meine erste Frage: Was berechnet denn die Formel? Ein Verweis von einer anderen Zelle auf die Zelle D2 zeigt, dass sich in der Zelle ein Wert (<> 0) befindet.

Meine zweite Vermutung: wie ist denn die Zelle formatiert? Die Antwort: Die Schriftfarbe ist „Automatisch“, das Zahlenformat „Standard“

Meine dritte Vermutung: und die bedingte Formatierung?

Sie sieht folgendermaßen aus:

Die erste Bedingung

=$E2=80

wird erfüllt. Die Schriftfarbe wird auf automatisch gesetzt; der Rahmen wird eingeschaltet.

Die zweite Bedingung?

=F2=1400

Sie wird ja nicht erfüllt – in F2 steht 2800. Oder doch?

Der Bereich, auf den die Bedingung angewendet wird, lautet $B$2:$D$31. Da bei der Bedingung das Dollarzeichen vergessen wurde, wird diese Bedingung auf alle drei Spalten angewendet; das heiß: F2 verschiebt sich nach G2 und H2. Da in H2 der Wert 1400 steht, ist die Bedingung DORT korrekt.

Jedoch: warum wird keine rote Hintergrundfarbe angezeigt?

Der Grund: beide Bedingungen sind in der Spalte D korrekt (und nur dort). Die erste Bedingung lautet: weißer Hintergrund; automatische (!) Schriftfarbe. Die zweite Bedingung: roter Hintergrund und weiße Schriftfarbe.

Die erste Bedingung gewinnt für die Hintergrundfarbe. Hätte man schwarze (!) Schriftfarbe verwendet, würde diese auch gewinnen. Jedoch: automatische Schriftfarbe heißt, dass bei der zweiten Bedingung die weiße Schriftfarbe nun gewinnt (!?!), jedoch nicht die rote Hintergrundfarbe!

Also: Dollar reinsetzen – und dann klappt es auch!

Aller Unfug ist schwer

Claudia kommt in der Pause zu mir. Zwei Stunden habe sie den Fehler gesucht und nicht gefunden. Ob ich ihr mit einer Formel helfen könne, die nicht richtig rechne:

Der Bereich A2:A13 trägt den Namen Feiertage_BY, B2:B10 heißt Feiertage_HE.

Darin befinden sich die bayrischen und die hessischen Feiertage.

Markus arbeitet in Bayern; Boris in Hessen.

Claudia möchte mit der Funktion NETTOARBEITSTAGE ausrechnen, wie viele Tage Markus und wie viele Boris arbeiten muss. Für Markus erhält sie die korrekte Zahl 248; Boris muss drei Tage länger arbeiten – eigentlich 251 Tage. Excel jedoch berechnet 253.

Ich kontrolliere die Namen, die Formatierungen, die Formel – alles ist korrekt. Ich sehe nach, ob alle Feiertage auf einen Wochentag fallen. Stimmt auch. Dann prüfe ich die Feiertage.

Haben Sie es gesehen? In der Liste Feiertage_HE befinden sich zwei Mal die Tage 07.04.2023 und 10.04.2023. Allerdings fehlen die Weihnachtsfeiertage. Wahrscheinlich ein Kopierfehler …

Und dann klappt es auch. Und Claudia ist glücklich.

Ein Sonntag, an dem ich die Wohnung nicht verlasse, ist kein verlorener Sonntag, sondern ein Tag, an dem ich die Miete voll ausnutze.

Guten Morgen Rene,

ich würde gerne bei dir einen 2 bis 3 Minuten-Support in Anspruch nehmen. Die bedingte Formatierung zeigt eine „Lücken“ an.

Gruß Jürgen

Es dauert wirklich nur drei Minuten. EIn Blick auf die Formel

=A2<>""

genügt. Da diese Formel in den Spalten C, D und E verwendet wird, „wandert“ der relative Bezug nach rechts. Und so wird in Spalte D auf Spalte B zugegriffen:

Die Formel muss lauten:

=$A2<>""

Der absolute Bezug auf die Spalte A fehlte …

Man kann sich den ganzen Tag ärgern – verpflichtet ist man dazu nicht.

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!

Gähne nie in der Nähe eines Kaffeeautomaten. Die spüren das und lassen dich erst mal alles mögliche auffüllen und entkalken.

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.

Beim Kochen: wie soll ich die Paprika schneidern? – Leg die Tomate weg und verlasse die Küche!

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

Du hörst mir nie richtig zu! – Ja, mach ich, Schatz!

Hallo Herr Martin,

wie heute Morgen gezeigt, machen mir aktuell zwei unterschiedliche Pivot Tabellen Probleme.

Die Spalte „Gesamt“ ist ein berechnetes Feld innerhalb der Pivot,

Für die einzelne Artikel wird die Gesamtstückzahl nicht korrekt berechnet (Stk./Pkg x Bestand).

###

Hallo Frau W.,

das erste Problem ist klar: Pivottabellen summieren erst, dann wird multipliziert.

Aus:

ArtikelMengePreis
Artikel120
Artikel210

Pivot rechnet NICHT: 1 x 20 + 2 x 10 = 40, sondern (1 + 2) x (20 + 10) = 3 x 30 = 90

Lösung 1: Hilfsspalten

Lösung 2: PowerPivot mit der Funktion SUMX

Hilft das?

Was sind Ihre Stärken? – Ordnung und Sauberkeit sind mir sehr wichtig! – Gut, und was sind Ihre Schwächen? – Ich hasse putzen.

Hallo Renè!

Da bin ich wieder 😉

Habe ein Filterproblem.

Vor längerer Zeit habe ich von dir die Formel in dem Arbeitsblatt erhalten.

Damals benötigte ich die Auflistung von Belegnummern.

Dieses mal brauche ich die Funktion um die Leistungsklassen bei der Medaillenbestellung anzugeben.

Komme beim gleichzeitigen Filtern von der Altersklasse und der Disziplin nicht weiter.

Die Formel aus dem Video über die Funktionen habe ich versucht.

Kein Erfolg. #Kalk war das Ergebnis.

Das Filter mit zwei Auswahlen in die frühere Formel einzubauen habe ich nach einiger Zeit aufgegeben.

Bitte um die bewährte Hilfe.

Danke für deine Hilfe,

mit freundlichen Grüßen

Peter

???

Hallo Peter,

Vertriebsmanager steht in der Spalte Position und Berlin steht in der Spalte Ort.

SO geht es:

=FILTER(tbl_Lieferanten;(tbl_Lieferanten[Position]=B35)*(tbl_Lieferanten[Ort]=C35))

Bitte markiere die KORREKTEN Spalten!

Liebe Grüße

Rene

Guck mal – dein Mann spricht gerade mit einer Anderen! – Lass ihn doch. Mal schauen, wie lange er den Bauch einziehen kann.

Amüsant.

PowerQuery-Schulung. Wir greifen auf eine (Währungskurs-)Tabelle im Internet zu:

Wir müssen die Daten transformieren, das heißt: an mehreren Stellen trennen:

Das Ergebnis

wird nach Excel als Tabelle geladen und dort weiterverarbeitet:

IN diesem Beispiel mit einem XVERWEIS (oder SVERWEIS würde auch funktionieren).

Eine Teilnehmerin meldet sich und sagt, dass ihr XVERWEIS Fehler liefern würde:

Die Ursache war schnell gefunden: sie hatte nicht Leerzeichen:Bindestrich als Trennteichen verwendet, sondern nur den Bindestrich:

Somit war noch ein Leerzeichen im Text, Und deshalb lief der XVERWEIS ins Leere …

Die Würde des Mannes ist unten antastbar.

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!

Lieber Gott, wenn du da draußen kochst: es ist GAR!

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!

Schweinefleisch kann man jahrelang frisch halten, indem man die Sau am Leben lässt.

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 Sub

Das 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!

Ist es normal, dass mein Handy im Flugzeugmodus nicht weiter als 25 Meter fliegt?

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

Zucker ist der Stoff, der dem Kaffee den schlechten Geschmack gibt, wenn man NICHT vergisst, ihn reinzutun.

Amüsante Frage. Warum die Funktion SUMMEWENN denn falsch rechne. Ob es einen Bug in Excel in dieser Funktion gebe.

Nicht, dass ich wüsste. Ich schaue es mir an:

Aha – er überprüft mit SUMMEWENN nicht eine Spalte, sondern einen Bereich:

Die ID (hier: 11) wird nicht in der ersten Spalte (der markierten Matrix) gefunden, sondern in der Spalte daneben.

Das Resultat – auch der Suchbereich verschiebt sich (auch wenn nicht ausgewählt – das Ergebnis ist der Wert aus F2.

Wahrscheinlich hatte der Anwender die Funktion SVERWEIS im Kopf als er diese SUMMEWENN erstellte. Ich erkläre ihm, wie es richtig funktioniert:

Geht doch:

Im Kochbuch stand: „Man reibe 3 Tage alte Brötchen.“ Nach einem halben Tag hatte ich die Badewanne und die Schnauze voll.

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 …

Ich bin jemand, der viel unternehmen möchte, gefangen in einem Körper, der viel schlafen will.

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.

Der Berg ruft. Es ist entweder die Wäsche oder der Abwasch.

Eine Teilnehmerin in der Excelschulung fragte mich, warum auf einem Rechner folgendes funktioniert, auf einem anderen nicht:

Normalerweise bewirkt ein Klick auf eine Zelle in der Pivottabelle, dass die Formel

=PIVOTDATENZUORDNEN

erzeugt wird:

Bei ihr jedoch nicht:

Auch das Eintippen der Formel hilft nicht – Intellisense versagt:

Des Rätsels Lösung war schnell gefunden: sie hatte in den Optionen / Formeln die Option „GetPivotData-Funktionen für PivotTable-Bezüge verwenden“ ausgeschaltet.

Danach klappte es wieder.

So – jetzt ist offiziell Frühling. Nun habt gefälligst Gefühle!

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

Was habt ihr alle gegen Beamte? – Die tun doch nix.

Erstaunlich.

Trägt man in Excel ein zehnstellige Zahl ein, beispielsweise 1234567890 oder 5432167890 und zieht diese mit gedrückter [Strg]-Taste herunter wird weitergezählt:

Erstellt man allerdings ein Text-Zahl-Gemisch, wird die Zahl weitergezählt, wenn sie mit 1, 2, 3 oder 4 beginnt. Ab 5 nicht mehr:

Microsoft listet auf der folgenden Seite die Beschränkungen von Excel auf:

https://support.microsoft.com/de-de/office/spezifikationen-und-beschr%C3%A4nkungen-in-excel-1672b34d-7043-467e-8e27-269d656771c3

Leider aber nicht die obenstehende Grenze.

Danke an Dagmar Pilarski für diesen Hinweis.

Wäre ich der Konjunktiv, hätte ich mehr Würde.

Excelschulung. Wir üben rechnen. Zuerst eine einfache Subtraktion; Formel herunterziehen:

Wir lernen, wie man mit dem AutoSummen-Symbol eine Summe erzeugt:

Und jetzt machen Sie es mal alleine, lautet die Aufgabe. Ein Teilnehmer beschwert sich, dass eine Summe falsch rechnet:

Wir gehen der Sache auf den Grund. Klar – er hat die erste Summe nicht nach rechts gezogen, sondern drei Mal eine Summe erstellt. Und bei der dritten Summe hat er nicht aufgepasst und den Vorschlag von Excel übernommen:

Der Tee ist gut. Schön kräftig. Wie heißt der? – Jack Daniels.

Ein bisschen nervt es schon. Bill Jelen (Mr. Excel) hat bereits darauf hingewiesen.

Wenn man einen Teil einer Formel nicht korrekt markiert, erhält man einen Fehler oder einen Fehlerwert.

Ich wollte in einer Schulung doch nur schnell etwas erklären und zeigen. Und natürlich kam die Frage, wieso ich ein #NAME am Bildschirm stehen habe:

Mein Rechner hat sich aufgehängt. Meine Wäsche macht das nie.

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!

Als ich jung war, habe ich jeden Morgen mit den Fingerspitzen meine Zehen mindestens 20 Mal berührt. Heute trinke ich in der Zeit einen Kaffee und winke ihnen einfach kurz zu.

Erstaunlich. Oder schade?

Ich habe eine Liste, bestehend aus zwei Spalten, in den sich Buchstaben befinden.

Ich möchte wissen, ob der erste Teil einer Buchstabenkombination („XY“), beispielsweise „X“ in der ersten Spalte steht und der zweite Teil, beispielsweise „Y“ in der zweiten.

Mit der Formel

=A2:A39&B2:B39

verkette ich die beiden Spalten. Nun kann ich zählen:

=ZÄHLENWENN(D7#;D3)

Will ich allerdings die beiden Formeln zusammenbauen, versagt Excel:

=ZÄHLENWENN((A2:A39&B2:B39);D3)

Und ja – natürlich kann man das Problem (anders) lösen – beispielsweise so:

=ZÄHLENWENNS(A2:A39;LINKS(D3;1);B2:B39;RECHTS(D3;1))

Aber ich hätte gerne den Array in ZÄHLENWENN eingebaut …

Beim Aussteigen aus der Bahn, erkläre ich den zusteigenden Patienten gern visuell die Elementarphysik: Wo ein Körper ist, kann kein zweiter sein.

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

Alexa, ich bin erkältet. – Ich habe vier Bestattungsinstitute in deiner Nähe gefunden.

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)

Sich einfach mal zu den Monstern unter das Bett legen und sich anhören, was die so für Probleme haben.

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:

Schöne Sache. Aber diesmal bin nicht ich es, der nörgelt, sondern Bill Jelen (MrExcel) weist darauf hin, dass ein Datum leider nicht formatiert dargestellt wird:

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

Der morgendliche Blick in den Spiegel ist ein eindeutiger Hinweis dafür, dass ich nachts von Aliens gefoltert wurde.

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

Mein Mann ist Veganer. – Dafür spricht er aber gut Deutsch!

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

Es gibt Tage, da läuft im Kopfkino immer dieselbe bescheuerte Werbung.

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.

Ich wurde gerade in einer Verkehrskontrolle nach Restalkohol gefragt. Dierse entwürdigende Bettelei nimmt langsam beschämende Züge an.

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 meinem Doppelbett neben mir liegen: Laptop, Handy, Buch, Schokolade, … ich wüsste nicht, wohin mit einem Mann.

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.

Von Bodenfrost spricht man dann, wenn beim Hochheben des Dackels noch eine Gehwegplatte mit dranhängt.

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!

Das Unwort des Jahres ist „Klimaterrorist“. An Putins Stelle würde ich die Wahl anfechten.

Einfach nicht aufgepasst. Viele Daten werden verarbeitet. Klappt.

Das Add-In wird dem Kunden ausgeliefert: Fehler:

Die Ursache: in einer Zelle steht kein Text, sondern eine Fehlermeldung:

Und so liefert die Zeile

If ActiveCell.Value Like "*Zuversicht*" Then

einen Fehler. Natürlich: besser wäre:

If ActiveCell.Text Like "*Zuversicht*" Then

Mama, ich bin noch nicht in Hausaufgabenstimmung. Könntest du mich bitte böse anschauen und mir sagen, dass ich nicht so faul sein soll!

„Ein in der Formel verwendeter Wert ist vom falschen Datentyp“, behauptet Excel und liefert den Fehler #WERT beim XVERWEIS:

Nein, lieber XVERWEIS – DAS ist nicht die Ursache des Fehlers, sondern weil die beiden Bereiche unterschiedlich groß sind:

$A$1:$A$297872 und $F$1:$F$297782

Gestern habe ich meinen Emmentaler leer gegessen – heute regnet es. Klar, denn: „ain’t no sunshine, when cheese’s gone.“

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:

Geht doch!

Ich hatte sechs verschiedene Stimmen im Kopf. Jetzt sind es nur noch fünf. Den Moralapostel habe ich im Alkohol ertränkt.

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

Ein echter Pirat weint nicht. Ein echter Pirat heult Rum.

Hallo Herr Martin,
können Sie mir als Excel-Virtuose beim Umgang mit intelligenten Tabellen helfen? Es handelt sich um folgende Sache:

  • gegeben: ein Bereich mit insgesamt 14 Spalten und beliebig vielen Zeilen
  • Ziel: eine intelligente Tabelle bei der Spalte 1, Spalte 2-5, Spalte 6-10; Spalte 11-14 jeweils ein eigenes coloriertes Stripset besitzen (siehe Abbildung = 1. Versuch)
  • meine Versuche + aufgetretene Problematik:
  1. Versuch: mehrere intelligente Tabellen mit dem jeweiligen Stripset (für Zeilen und Spalten) aneinanderfügen
    –> beim Hinzufügen einer neuen Zeile in Spalte 1 (und ff. neu alphabetisch sortieren lassen), sortieren sich die Zeilen der anderen Tabellen nicht automatisch mit –> Chaos
    Idee: Verknüpfung der aneinanderliegenden Tabellen, damit jede auf die Veränderung bspw. in der ersten Spalte (bzw. Tabelle) reagiert
  2. Versuch: eine 14 Spalten umfassende intelligente Tabelle mit benutzerdefinierter Tabellenformatierung
    –> da die Tabellenabschnitte aus einer unterschiedlichen Anzahl aus Spalten bestehen, lässt sich nicht mal ansatzweise mein Ziel über das Formatieren des Stripsets „erste Spalte“ / „zweite Spalte“ verfolgen
    Idee: Hinzufügen der Auswahlfelder Stripset „dritte Spalte“, „vierte Spalte“, usw.
  3. Versuch: eine 14 Spalten umfassende intelligente Tabelle mit „überdeckender“ händisch eingestellten Formatierung
    –> händisch eingestellte Formatierung ist nicht intelligent, sodass bei Filterfunktion die farbliche Unterscheidung zwischen den Zeilen nicht mehr gegeben war (Bsp. Zeile 1 gelb, Zeile 2 grün, Zeile 3 gelb; Filter Z.2 ; Zeile 1 gelb, Zeile 3 gelb)

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

Elternabend. Die immer wiederkehrende Strafe für ungeschützten Sex.

Nicht aufgepasst!

Eine Teilnehmerin möchte wissen, in welcher Zeile und in welcher Spalte sich ein Wert befindet:

Nun: Schritt I: die Formel

=H18=A1:J14

liefert eine Matrix mit Wahrheitswerten:

Und nun die Zeilennummer:

=MAX((H18=A1:J14)*ZEILE())

Das ist definitiv falsch, wie man sofort auf den ersten Blick sieht:

Etwas überlegen – mir dämmert es – klar – die Formel muss natürlich lauten:

=MAX((H18=A1:J14)*ZEILE(A1:J14))

Analog für die Spalte:

Gerade meinen verheirateten Chef bei tinder gefunden. Ich bin schon so gut wie befördert.

Warum rechnet der XVERWEIS bei mir nicht, möchte ein Teilnehmer in meiner Excelschulung wissen:

Ich lasse die korrekt berechnete Zelle editieren:

Ich sehe den Fehler nicht. Er als ich den Teilnehmer bitte, eine andere, fehlerhafte Zelle mit einem Doppelklick zu editieren, sehe ich, dass von den acht Dollarzeichen eines fehlt. Und somit sind die beiden Spalten nicht gleich groß.

Kinderhelden sind unrealistisch: Benjamin Blümchen, ein sprechender Elefant. Bibi Blocksberg: ein hexendes Kind. Aber bei Bob dem Baumeister haben sie es übertrieben: ein pünktlicher und motivierter Handwerker!

Ich schmunzle, wenn Teilnehmerinnen oder Teilnehmer in meiner Excelschulung mich darauf aufmerksam machen, dass sie einen Fehler haben, ihn aber nicht finden. Und das, obwohl sie es GANZ GENAU SO GEMACHT HABEN, wie ich. Eben nicht, denke ich mir.

Wir üben die Funktionen der Kategorie „Nachschlagen und verweisen“: INDEX. Ich habe auf dem ersten Tabellenblatt „Entfernungen“ einen Entfernungsliste (von A nach B). Ei Wert soll Zeilen und ein anderer Spaltenweise gesucht werden. Auf einem zweiten Blatt beginne ich zu tippen:

=INDEX

und wechsle auf das erste Tabellenblatt, wo ich den Bereich markiere und fixiere ([F4]):

Dann drücke ich das Semikolon, um danach auf dem zweiten Blatt den zweiten Wert einzugeben. Allerdings vergisst die Teilnehmerin das Semikolon, klickt auf das andere Blatt, was Excel interpretiert als: „Sie möchte sicherlich einen anderen Blattnamen haben …“ Eben – das führt zu einem Fehler:

Wie war dein Wochenende? – Voll geil! – 3er probiert! – Wie war’s? – Der Bademeister musste mich schreiend runtertragen.

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:

Wenn man nach dem Tod verbrannt und in eine Sanduhr gefüllt wird, dann könnte man noch an Spieleabenden mit seiner Familie teilnehmen.

Lieber Herr Martin, ich habe eine „für mich“ komplexe Frage und finde keine Antworten im Internet. Kann xls 2016 bedingte Formatierungen für relative Bezüge herstellen? Ich möchte dass mein Wert heute abhängig vom Wert gestern, als bedingte Formatierung rote oder grüne Pfeile einsetzen. Wenn Wert höher als gestern -> grüner Pfeil. Wenn Wert niedriger als gestern -> roter Pfeil. Über die Wenn-Funktion kann ich ja keine bedingte Formatierung einbauen. Wissen Sie was ich tun kann ausser die Werte einzufärben, was ich schon tat? Ich würde zusätzlich gerne eben die Pfeile einbauen. Viele liebe und sonnige Grüsse

###

Hallo Frau Pap,
die bedingte Formatierung kann bei Symbolen leider keine Formeln mit relativen Bezüge verwenden. Vor einigen Jahren hat mich ein Freund darauf aufmerksam gemacht; ich habe ihn nicht geglaubt und es bestätigt. Ich habe einen Artikel darüber geschrieben:

Wenn es die Linguisten krachen lassen, veranstalten sie Komma-Saufen.

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.

Ich fühle mich krank. Ich habe die Symptome gegoogelt und herausgefunden, dass die Zylinderkopfdichtung die Ursache daran hat.

Die neuen Arrayfunktionen, die dynamisch in mehrere Zellen die Ergebnisse schreiben und die „starren“ (intelligenten) Tabellen, arbeiten nicht zusammen. Will man die Funktion TEXTTEILEN auf eine Zelle (also auf eine Spalte) anwenden, um die Inhalte zu trennen:

lautet das Ergebnis #Überlauf, da diese Funktion keine neuen Spalten generieren kann:

Flensburg ist wie Payback sammeln – nach acht Punkten gibt es ein Fahrrad.

Die neuen Textfunktionen sind noch nicht ganz bei mir angekommen:

Sie sprechen noch ein bisschen Englisch; aber – was schlimmer ist – sie arbeiten noch nicht korrekt. TEXTTEILEN sollte alle Textteile liefern.

Okay, okay, verstanden – diese Arrayfunktion darf nicht (anders als TEXTVERKETTEN) auf den ganzen Bereich angewendet werden, sondern nur auf eine Zelle. Und dann runterziehen!

Aber sie spricht immer noch Englisch mit mir …

Menschen, seufzt der Kaffee. Jeden Morgen schweigen sie mich missmutig an, aber der Weinflasche erzählen sie abends ihr halbes Leben.

Will man Formeln in Werte verwandeln, sind mehrere Schritte nötig:

  • Die Zellen mit den Formeln kopieren
  • Über das Kontextmenü auf das Symbol [123] klicken

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:

Wenn du das Nutella-Glas ganz nah ans Ohr hältst … kannst du deine Bikinifigur weinen hören!

Hallo Herr Martin,

warum findet Excel Costa Rica nicht? Es gibt Costa Rica!

Genauer: ich habe mit einer Suchformel

=INDEX($F$2:$F$114;VERGLEICH(A9;$G$2:$G$114;0))

einen Wert gesucht. Alle Werte werden gefunden – nur nicht Costa Rica:

Zuerst vermutete ich ein Leerzeichen hinter einer der beiden Wörter. Die Schreibweise ist korrekt. Dann vermute ich das Leerzeichen als Übeltäter. Und so ist es auch: die Funktion

=CODE(TEIL(G23;6;1))

liefert den Wert 160 (geschütztes Leerzeichen) und nicht 32 (Leerzeichen), wie erwartet:

Auf mein Nachfragen erfahre ich: „… ich habe die Liste doch aus wikipedia kopiert. Und ich habe sie explizit als Wert eingefügt – OHNE Formatierung!“ Das genügt leider nicht … wikipedia verwendet an einigen Stellen geschützte Leerzeichen und bedingte Trennstriche. Das macht das Weiterverrbeiten solcher Listen in Excel manchmal mühsam. Böses Excel …

Tomatensoße weiß genau, wann du ein frisches Hemd angezogen hast.

Irgendwie doof.

Ich habe eine Datei, in der sich verschiedene Werte befinden. Diese Datei wird mit Kennwortschutz gespeichert:

Einige Werte dieser Datei werden in eine andere Datei verknüpft:

Sind beide Dateien geschlossen, wird die Zieldatei geöffnet, so fragt Excel danach, ob die Daten aktualisiert werden sollen. Danach erfolgt die Frage nach dem Kennwort der kennwortgeschützten, verknüpften Datei.

Wurde allerdings die verknüpfte Datei an andere Stelle kopiert, dort bearbeitet und wieder zurückgespielt, erkennt Excel dies – aufgrund des Kennwortschutzes – nicht. Eine Neuberechnung zeigt nicht die aktuellen Daten. Man muss die Originaldatei öffnen, um eine Aktualisierung der Daten zu erreichen …

Hast du zugenommen? – Nein, ich habe mich auseinander gelebt.

*ups*

Die Aufgabe lautet: ich möchte die Anzahl der Zeilen wissen, in denen in mindestens einer der vier Spalten etwas steht. Oder Anzahl der Spalten, bei denen Wert Spalte A <> „“ oder Wert Spalte B <> „“ oder Wert Spalte C <> „“ oder Wert Spalte D <> „“.

Ich probiere. Mit & werden die vier Zellen verkettet:

Hier:

=tblMusketiere[D’Artagnan]&tblMusketiere[Aramis]&tblMusketiere[Porthos]&tblMusketiere[Athos]

Während die Funktion

=ANZAHLLEEREZELLEN(G2#)

die Zahl 3 ergibt, liefert

=ANZAHL2(G2#)

die Zahl 26. Die vermeintlich leeren Zellen werden mitgezählt.

Die Formel darf folglich NICHT lauten:

=ANZAHL2(tblMusketiere[D’Artagnan]&tblMusketiere[Aramis]&tblMusketiere[Porthos]&tblMusketiere[Athos])

Die Formel

=G6=""

liefert WAHR, die Formel

=ISTLEER(G6)

liefert FALSCH.

Eine Lösung könnte so aussehen: die Funktion FILTER liefert die korrekte Liste, beispielsweise so:

=FILTER(tblMusketiere[Datum];(tblMusketiere[D’Artagnan]<>"")+(tblMusketiere[Aramis]<>"")+
(tblMusketiere[Porthos]<>"")+(tblMusketiere[Athos]<>""))

Und die kann man zählen:

=ANZAHL2(FILTER(tblMusketiere[Datum];(tblMusketiere[D’Artagnan]<>"")+(tblMusketiere[Aramis]<>"")+
(tblMusketiere[Porthos]<>"")+(tblMusketiere[Athos]<>"")))

Eben am Kondomständer im Drogeriemarkt vorbeigelaufen. So nah am Sex war ich schon lange nicht mehr.

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 😉

Heute Abend werde ich wieder über die Stränge schlagen und nach 21 Uhr einen Film gucken.

Hallo Herr Martin,

ich muss zeitlich und gedanklich kapitulieren und gebe daher gerne folgendes Problem an sie weiter.

In einem Excel.xlsx wird Bezug genommen auf den Wert von einem „Berechnung Planerfüllung.xlsx“,

entweder direkt oder mit einer Rundungsformel ergänzt, z.B.

  • =SUMMEWENN(‚[Berechnung Planerfüllung.xlsx]Daten‘!$F$1:$F$12;A2;'[Berechnung Planerfüllung.xlsx]Daten‘!$G$1:$G$12)
  • =RUNDEN(SUMMEWENN(‚[Berechnung Planerfüllung.xlsx]Daten‘!$F$1:$F$12;A2;'[Berechnung Planerfüllung.xlsx]Daten‘!$G$1:$G$12)/SUMMEWENN(‚[Berechnung Planerfüllung.xlsx]Daten‘!$F$1:$F$12;A2;'[Berechnung Planerfüllung.xlsx]Daten‘!$H$1:$H$12);3)

In diesen zahlreichen Bezügen wurde übersehen, dass der Wert nur übernommen werden darf, wenn er >0 ist.

Um nicht per Hand die zahlreichen Stellen zu ändern, wollte ich dafür eine Formel schreiben,

die den Inhalt dieser Zellen ändert in

  • =WENN(SUMMEWENN(‚[Berechnung Planerfüllung.xlsx]Daten‘!$F$1:$F$12;A2;'[Berechnung Planerfüllung.xlsx]Daten‘!$G$1:$G$12)=0;““;SUMMEWENN(‚[Berechnung Planerfüllung.xlsx]Daten‘!$F$1:$F$12;A2;'[Berechnung Planerfüllung.xlsx]Daten‘!$G$1:$G$12))
  • =WENN(RUNDEN(SUMMEWENN(‚[Berechnung Planerfüllung.xlsx]Daten‘!$F$1:$F$12;A2;'[Berechnung Planerfüllung.xlsx]Daten‘!$G$1:$G$12)/SUMMEWENN(‚[Berechnung Planerfüllung.xlsx]Daten‘!$F$1:$F$12;A2;'[Berechnung Planerfüllung.xlsx]Daten‘!$H$1:$H$12);3)=0;““;RUNDEN(SUMMEWENN(‚[Berechnung Planerfüllung.xlsx]Daten‘!$F$1:$F$12;A2;'[Berechnung Planerfüllung.xlsx]Daten‘!$G$1:$G$12)/SUMMEWENN(‚[Berechnung Planerfüllung.xlsx]Daten‘!$F$1:$F$12;A2;'[Berechnung Planerfüllung.xlsx]Daten‘!$H$1:$H$12);3)).\Tab 29\[Tab29_AP_W_Lage_D.xlsx]Lage_D_1930-1949′!$G$10;-2);““)

Beim Aufbau dieser Formel mit Textkettung scheitere ich daran, dass der Inhalt der Zelle sofort als Formel interpretiert wird und nicht als Text.

z.B. irgendwie so:  =“=Wenn(„&ERSETZEN(I25;1;1;““)&“>0;“&ERSETZEN(I25;1;1;““)&“;““““)“ Fällt Ihnen dazu was ein?

Meinen ersten Vorschlag – mit Ersetzen ([Strg] + [H]) zu arbeiten, scheitert, weil die Formeln auf diesem Tabellenblatt unterschiedlich aufgebaut sind und auf verschiedene Tabellenblätter zugreifen.

Meine zweite Nachfrage, warum sie denn die 0 ausblenden will und ob man das nicht per Formatierung (oder über die Optionen) machen könne, wurde mit einem „manchmal sind auch Fehler in den Formeln drin – die möchte ich auch – im Nachhinein – abfangen“ quittiert.

Mein dritter Gedanke: eine Formel zu ändern und dann nach unten zu ziehen, scheitert, weil die Formeln alle unterschiedlich sind.

Man scheitert auch mit folgenden Schritten:

FORMELTEXT liefert den Namen der Formel

WECHSELN (oder ERSETZEN) ersetzt einen Formelteil durch einen anderen.

Das Ergebnis ist keine Formel, sondern ein Text.

Man kann ihn kopieren und als Inhalt einfügen und dann den Text in der Zelle markieren und anschließend in die erste Zelle einfügen; aber das ist bei vielen (unterschiedlichen) Zellen sehr mühsam. Aber – einen besseren Weg weiß ich nicht.

Ich will nicht irgendwann im Leben auf verpasste Chancen zurückblicken und denken müssen: Das hätte ich essen können!

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

Habe Reinigungstipps mit Wodka bekommen. Ich trinke jetzt Wodka auf der Couch und muss sagen, dass die Wohnung schon viel sauberer aussieht.

Hallo Herr Martin,

in dieser Datei sind die Verknüpfungen falsch zugeordnet.

Ich habe versucht, dies über die Änderung der Hyperlinks zu korrigieren.

Wie kann die Zuordnung dauerhaft geändert werden? Sie scheint intern abgelegt zu sein.

=========

Hallo Herr H.,

Öffnen Sie den Blattschutz (haben Sie wahrscheinlich),

öffnen Sie den Link (haben Sie wahrscheinlich)

und ändern dann an den DREI Stellen den Bezug:

* Beschriftung (anzuzeigender Text)

* Quickinfo

* Ziel

Ein Hai besteht zu 64% aus Haiweiß, zu 33% aus Haigelb und 3% aus Haischale. Weiß nur kaum jemand.

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

Kennst du das? Montag Morgen voller Freude aus dem Haus stürmen? – Ich kenne das auch nicht!

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
    Next

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

STERB!!! – Der Imperativ von „sterben“ wird mit „i“ gebildet, du bildungsresistenter Intelligenzallergiker. – Sterbi?

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:

  1. Wie ist es möglich, dass bei dem Bereich Kriterium-„=“ auch mehrere Werte eines Attributs angegeben werden können?
    Z.B. Attribut
    Lagekarte-GAA = G – Gut
    Lagekarte-GAA = D – Durchschnitt
    Und es werden alle Kauffälle angezeigt, die entweder Lagekarte-GAA = G – Gut ODER Lagekarte-GAA = D – Durchschnitt haben.

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

);““)

Ich wohne nun seit fast 20 Jahren; aber es hat noch niemand geklingelt, um sich Eier auszuleihen. Ich glaube, ich schmeiße die jetzt weg.

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:

In der Vorlesung ertönt plötzlich das Wort „klausurrelevant“. Alle erwachen aus dem Tiefschlaf, 200 Kulis klicken, 7 Bierflaschen fallen um, in der letzten Reihe wird ein Lagerfeuer ausgetreten.

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

Mein Vater: „oh, ihr habt ja eine Waage im Flur stehen!“ – Was dann geschah, war nicht schön für den Staubsaugerrobotter.

Ich gestehe, dass ich mich manchmal irre. Oder Dinge übersehe. Oder schlicht und einfach nicht weiß. Umso mehr freue ich mich darüber, dass andere meinen Blog mitlesen und mir korrigierende Antworten schreiben. Eine Berichtigung hat mich vorgestern erreicht – ich möchte sie hier gerne wiederholen, weil ich danke, dass sie wichtig ist.

In dem Artikel

habe ich geschrieben, dass die Funktion WURDEAUSGELASSEN nicht das gewünschte Ergebnis liefert.

Sven berichtigt mich und schreibt:

Optionale Parameter in der LAMBDA-Funktion müssen in eckigen Klammern [ ] angegeben werden!

=LAMBDA(Wert1;[Wert2];WENN(WURDEAUSGELASSEN(Wert2);Wert1*1,19;Wert1*Wert2))

=LAMBDA([Bereich];WENN(WURDEAUSGELASSEN(Bereich);“Bitte wählen Sie einen Bereich aus!“;MAX(Bereich)-MIN(Bereich)))

Man beachte [Bereich] als Parameter-Angabe in der LAMBDA-Funktion!

Hat mich auch mehrere Stunden gekostet, bis ich das rausbekommen hatte…

####

Danke für den guten und wichtigen Hinweis!

Alexa, stell den Wecker auf 4:30 Uhr. – Ich, wenn ich zu Besuch bei jemand bin, der eine Alexa hat.

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!

Wann willst du dich deinem Alter entsprechend verhalten? – Sag ich nicht!

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!

Für alle, die nicht wollen, dass Alexa mithört: im Sommer kommt Alex, eine männliche Version; er hört überhaupt nicht zu!

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!

Was für ein Gefühl muss der Tropfen haben, der das Fass zum Überlaufen bringt. (Nikolaus Cybinski)

Die Idee ist gut – sie funktioniert nur leider nicht.

Ein Teilnehmer einer Excelschulung möchte eine fortlaufende Reihe erzeugen. Er möchte, dass „Lücken übersprungen“ werden und dass die Reihe bequem fortgesetzt werden kann.

Kein Problem, oder:

Die Formel

=WENN(B2="";"";MAX($A$1:A1)+1)

hilft hierbei.

Damit unter der Liste neue Daten mit einer fortlaufenden Nummer eingetragen werden können, wandle ich die Liste in eine (intelligente) Tabelle um:

Ein neuer Name:
Lücke und ein weiterer Name:

Klappt.

Wird eine Zeile gelöscht:

funktioniert der Mechanismus hervorragend:

Jedoch: wird eine Zeile eingefügt:

Dann versagt der Mechanismus leider:

Was man feststellen kann, wenn man einen Namen einträgt:

Schade!

Oh, da braut sich was zusammen! – Ein Bier? – Nein, was Böses! – Ein alkoholfreies Bier?

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…

https://support.microsoft.com/de-de/office/datumswerte-addieren-oder-subtrahieren-b83768f5-f695-4311-98b1-757345f7e926

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!

Gestern habe ich alkoholfreies Bier gekauft und mit Karte bezahlt. Heute ruft mich die Bank an und fragt, ob meine Karte gestohlen wurde …

Excel-VBA-Schulung. Wir üben das Programmieren von eigenen Funktionen (also function), die in Excel verwendet werden sollen. Ich zeige einen Fehler:

Und erkläre, dass man die Ursache gut finden kann, indem man einen Haltepunkt in Excel setzt:

Dann muss man die Funktion editieren (doppelklick oder [F2]) und sie wird erneut aufgerufen und berechnet:

Allerdings: nichts passiert. Ich brauche eine Weile, bis ich verstehe. Die Parameter sind vom Typ Double deklariert. Einer der Eingabewerte ist jedoch keine Zahl:

Und so wird die Funktion schon direkt nach dem Aufruf abgebrochen und liefert die Fehlermeldung #WERT, ohne dass die Zeile mit dem Haltepunkt erreicht wird. Also flugs den Wert der Zelle in eine Zahl ändern und schon wird der Haltepunkt erreicht.

Wenn ich das Wäscheaufkommen hier in diesem Haushalt sehe, muss ich stark davon ausgehen, dass hier Leute wohnen, die ich noch nie gesehen habe.

LAMBDA zu testen ist sehr, sehr mühsam. DIe Funktion rechnet korrekt, liefert aber im Funktionsassistenten:

Svetlana Cheusheva schreibt auf ihrer Seite

https://www.ablebits.com/office-addins-blog/2021/06/16/write-recursive-lambda-function-excel/

„Sadly, there is no way to test it at this point, and we can only rely on the results of the previous tests and do debugging later if needed.“

Auch Mourad Louha schreibt:

„Ich bin wirklich sehr gespannt, was das Excel-Team in den kommenden Wochen und Monaten an Verbesserungen zu den LAMBDA-Funktionen zur Verfügung stellen wird.“

http://www.excel-ticker.de/die-lambda-funktion-in-excel/

Da es hier offenbar nur um dummes Zeugs geht – nun mal was Ernsthaftes: weiß jemand, was eine Giraffe kostet?

Wie weit ist es von München nach Moskau? Und nach Madrid?

Da ich nächste Woche einen Vortrag über die neuen LAMBDA-Funktionen NACHZEILE, MATRIXERSTELLEN, WURDEAUSGELASSEN, REDUCE, .. halte, probiere ich ein wenig. Und habe folgendes interessantes Beispiel gefunden.

Die Koordinaten von München (beispielsweise Marienplatz) sind

Lat: 48,1371079 und Lon: 11,5753822.

Die vom Roten Platz in Moskau lauten

Lat: 55,7536283 und Lon: 37,6213796006738

Das kann man beispielsweise über

https://www.koordinatengps.de/

herausfinden. Die Entfernung zweier Punkte kann man nicht mit dem Satz des Pythagoras berechnen, sondern mit Hilfe von sphärischer Trigonometrie. Ein Blick in die Formelsammlung oder ins Internet liefert die Lösung:

Entfernung = 6378,388 * acos(sin(lat1) * sin(lat2) + cos(lat1) * cos(lat2) * cos(lon2 - lon1))

Da Sinus und Cosiuns von einer Einheitskugel ausgehen, muss das Ergebnis mit dem Radius der Erde (ungefähr 6.380 km) multipliziert werden. Und da Excel mit der Funktion BOGENMASS diese Angaben in GRAD umrechnet, lautet die Formel:

= 6378*ARCCOS(SIN(BOGENMASS(B2))*SIN(BOGENMASS(B3))+COS(BOGENMASS(B2))*COS(BOGENMASS(B3))*COS(BOGENMASS(C3-C2)))

Das kann man doch sicherlich mit den neuen Arrayfunktionen, beispielsweise mit LAMBDA und REDUCE abkürzen. Da zwei Mal der COSINUS verwendet wird und ein drittes Mal der Cosinus einer Differenz, ermittle ich die Differenz unterhalb der Daten:

Und berechne nun:

= 6378*ARCCOS(REDUCE(1;B2:B3;LAMBDA(a;b;a*SIN(BOGENMASS(b))))+REDUCE(1;B2:B4;LAMBDA(a;b;a*COS(BOGENMASS(b)))))

Die Formel ist etwas kürzer als die erste:

Okay – München – Moskau sind 1.962 km Luftlinie. Und nach Madrid? Ich hole die Daten aus:

https://www.koordinatengps.de/

Ich markiere, kopiere, füge ein und:

Und trage es in meine Formel ein und …

… erhalte einen Fehlerwert. Die Ursache ist schnell gefunden. Ich muss nicht nur das Dezimaltrennzeichen von Punkt in Komma ändern, sondern auch die (unsichtbaren) Leerzeichen, die auf der Homepage vor den Zahlen eingetragen waren, entfernen. Dann klappt es:

Dann funktioniert es. Nach Madrid sind es von München aus „nur“ 1.486 km – ist also näher als Moskau.

Weihnachten ist vorbei – ich habe alle Cookies gelöscht!

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:

Was passiert? Die Formeln
=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 wünsche mir an Weihnachten einen Hamster. Gute Idee – mal was anderes als Rotkraut unmd Gans.

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.

Ich soll meiner Frau ein Shampoo kaufen und soll nun entscheiden, ob ihre Haare glanzlos, strapaziert oder fettig sind. Ich kann nur verlieren!

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.

Wenn ich sowieso zur Hölle fahre, kann ich auch die landschaftlich schöne Route nehmen

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?

Lars Krismes verhaftet! Es sitzt jetzt im Verlies Navidad.

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:

Gerade die Zutaten des Gewürzgurkenglases durchgelesen: Gurken, Wasser, Branntweinessig, Zucker, Zwiebeln, Speisesalz. Und jetzt noch einmal mit der Melodie „Freude schöner Götterfunken“!

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?“

Wissenschaftler: Unsere Erkenntnisse sind nutzlos, wenn sie aus dem Kontext gerissen werden. Medien: Wissenschaftler sagen, ihre Erkenntnisse sind nutzlos.

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:

Warum heißen Männer-Duschgels immer „active“, „Sport“ oder „Energy“? – Emotional würden mich eher „lazy“, „sleepy“ oder „lethargy“ abholen.

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?

Es gibt eigentlich nur drei Dinge, die man fürs Skifahren lernen muss: wie man die Skier anzieht, wie man bergab fährt und wie man einen Krankenhausflur entlangläuft. (Lord Mancroft)

Guten Morgen zusammen, vielleicht könnt Ihr mir helfen

Ich habe für meine Masterarbeit Wetterdaten bekommen und würde jetzt gerne aus den Zahlen für die Windrichtung den Text „Nord“, bzw. „Ost“ usw. generieren. Dabei ist

0<=x<45 => „Nord“

45<=x<135 => „Ost“

135<=x<225 => „Süd“

225<=x<315 => „West“

315<=x<380 => „Nord“

Dafür hab ich folgende WENN-Funktion verwenden wollen:

=WENN(0<=A2<45;“Nord“;WENN(45<=A2<135;“Ost“;WENN(135<=A2<225;“Süd“;WENN(225<=A2<315;“West“;WENN(315<=A2<380;“Nord“;“x“)))))

Wie Ihr auf dem Screenshot sehen könnt, zeigt mir Excel aber nur das „alternative“ x an… Ich kann mir aber nicht erklären warum?

Excel kennt kein „zwischen“. Du darfst nicht schreiben

0<=A2<45

sondern mit UND:

=WENN(UND(0<=A2;A2<45);“Nord“;WENN(UND(45<=A2;A2<135);“Ost“;WENN(UND(135<=A2;A2<225);“Süd“;WENN(UND(225<=A2;A2<315);“West“;WENN(UND(315<=A2;A2<380);“Nord“;“x“)))))

Heute mal den Salzstreuer auffüllen. Dauert bei den kleinen Löchern ja immer ewig.

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

Wenn ich am Morgen ins Badezimmer gehe, wird mir klar, dass es nicht gut ist, Menschen nach ihrer äußeren Erscheinung zu beurteilen.

Excel schwächelt.

Volker zeigt mir eine Fehlermeldung in Excel, die er noch nie gesehen hat:

Für Excel waren beim Berechnen einer oder mehrerer Formeln nicht genügend Ressourcen vorhanden. Daher können diese Formeln nicht ausgewertet werden.

Wir sind erstaunt.

Ich probiere ein bisschen.

Ha – es gelingt mir den Fehler zu reproduzieren:

Ich erstelle in einer leeren Mappe ein zweites Tabellenblatt, beziehe mich auf dem zweiten Blatt in der Zelle A1 auf einen Bereich des ersten Blattes:

=Tabelle1!A:RD

Excel schafft es nicht diese 472 x 1.048.576 Zellen zu verknüpfen. Die Meldung
„Für Excel waren beim Berechnen einer oder mehrerer Formeln nicht genügend Ressourcen vorhanden. Daher können diese Formeln nicht ausgewertet werden.“
ist die Folge:

Es funktioniert natürlich auch mit anderen Bereichen, beispielsweise

=Tabelle1!Z:DF

Danke an Volker Pagel für diesen Hinweis.

Manchmal, wenn mir langweilig ist, kaufe ich Kondome, fülle sie mit Tapetenkleister und werfe sie durchs offene Schlafzimmerfenster auf die Straße.

Excel ist nicht konsistent im Umgang mit leeren Zellen. In den meisten Funktionen wird eine leere Zelle als 0 interpretiert. Aber eben halt nicht immer. Gestern habe ich mich mal wieder geirrt.

Ich wollte die Funktion HÄUFIG mit der Funktion ZÄHLENWENNS nachbauen – wollte die Bereiche definieren.

Mit der Funktion HÄUFIGKEIT (als Matrixfunktion) kann man berechnen, wie viele Daten in den entsprechenden Klassen vorhanden sind:

=HÄUFIGKEIT(B2:K11;M2:M7)

Also baue ich nach:

=ZÄHLENWENNS($B$2:$K$11;">"&M1;$B$2:$K$11;"<="&M2)

Die erste Zelle wird falsch berechnet – klar – weil in der Zelle Text steht:

Also lösche ich die Überschrift:

Das Ergebnis ändert sich nicht.

Klar, denn der Formelteil

">"&M1

wird nicht ausgewertet als >0, sondern nur als >.

Also doch: eine 0 einfügen – dann klappt es:

Darf ich dir deine Nase zurückgeben. Sie steckt in meinen Angelegenheiten.

Excelschulung. Wir erstellen einen Kalender. Daran kann man einige Funktionen üben: die Funktion WENN, Datumsfunktionen, Textfunktionen. Um Funktionen aus der Kategorie „Nachschlagen und verweisen“ zu zeigen, erstelle ich einen mehrsprachigen Kalender. Über eine Auswahlliste (Datenüberprüfung) wird die Sprache gewählt:

Mit VERGLEICH wird die Zeilennummer ermittelt; INDEX „holt“ den Ländercode aus einer kleinen Tabelle:

Eine Teilnehmerin sagt, dass es bei ihr nicht funktioniere. Ich schaue auf ihren Bildschirm. Richtig: Sie hat Excel 2013. Das erkenne ich sofort an den Großbuchstaben der Texte im Menüband. Bis Excel 2013 wurde nicht der ISO-Sprach- und Ländercode verwendet, sondern ein anderer. Zum Glück finde ich ihn auf der Festplatte in einem älteren Beispiel:

Und DAMIT funktioniert es nun auch bei ihr:

Früher waren es Mickey-Maus Hefte. Dann folgten Yps und Bravo. Anschließend der Playboy. Und heute ist es die Apotheken-Umschau.

Einfach nicht aufgepasst!

Excel-VBA-Schulung. Eine Teilnehmerin möchte ein kleines Programm mit mir geschrieben haben: Jede Woche erhält sie eine Liste und jede Woche muss sie in dieser Liste Berechnungen durchführen. Eine bestimmte ID (beispielsweise Idefix) wird gesucht, sämtliche Werte (hier drei) werden wie folgt berechnet:

Die Anzahl der Römer wird mit der Anzahl der Piraten multipliziert und die einzelnen Produkte summiert. Das Ergebnis wird durch die Summe der Römer dividiert. Aber nur dann, wenn keine Hinkelsteine vorhanden sind. Alles klar? – Klar!

Ich beginne Schritt für Schritt. Multipliziere und addiere – hierfür bietet sich doch SUMMENPRODUKT an, oder?. Also: los geht’s:

=SUMMENPRODUKT((A:A=G5)*(B:B)*(C:C))

Ich habe drei Mal überlegen müssen, woher die Fehlermeldung rührt. Die Antwort:

Klar: ich multipliziere jede Zelle jeder Spalte. Und das funktioniert bei der Überschrift (Text!) natürlich nicht!

Ich muss ändern. Entweder so:

=SUMMENPRODUKT((A:A=G5)*1;(B:B);(C:C))

Oder indem ich auf den Bereich ohne Überschrift verweise:

=SUMMENPRODUKT((A2:A40=G5)*(B2:B40)*(C2:C40))

Oder indem ich den Fehler mit WENNFEHLER abfange, oder oder oder.

Und DANN ist der Rest auch kein Problem – beispielsweise so:

=SUMMENPRODUKT((A2:A40=G9)*1;(B2:B40);(C2:C40);(D2:D40<>"Hinkelstein")*1)/SUMMEWENNS(B2:B40;A2:A40;G9;D2:D40;"<>Hinkelstein")

Gerechnet wird also:

(10 x 31 + 40 x 50) / (10 + 40) = 46,2

Und diese Formel kann man mit dem Makrorekorder aufzeichnen und über alle Zellen „laufen lassen“. Das Ganze wird in der Datei Personal.xlsb gespeichert.

2 Uhr, Verkehrskontrolle, zwei sehr junge Polizisten, halbe Kinder. Kopf: „Sag jetzt nichts Falsches!“. Ich: „Wissen eure Eltern, dass ihr noch wach seid?“

Guten Morgen,

Im gleichen „Atemzug“ habe ich dann noch ein Problem mit [=ZELLE(„dateiname“)] gefunden. Das ist scheinbar nicht immer zwingend das aktuelle Workbook, welches dort angezeigt wird bzw. der Inhalt aktualisiert sich nicht automatisch. Wenn zwischenzeitlich eine andere Arbeitsmappe geöffnet war, steht noch deren Pfad im Feld….

Viele Grüße,

Jörn

Hallo Jörn,

ja – ich weiß – ZELLE wird nicht aktualisiert – es gibt da so einige Funktionen in Excel, beispielsweise JETZT(). Mit [F9] oder Formeln / Neu berechnen kann man die Neuberechnung manuell erzwingen.

Liebe Grüße

Rene

Auf der Verpackung des Wildlachses, den ich gerade verzehre, steht: „Fisch mit Zukunft“. Ich will ja nicht schwarzsehen, aber rosig wird diese Zukunft sicherlich nicht …

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!

Ohne Brille werde ich oft angesprochen – ich weiß nur leider nicht, von wem.

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:

https://de.wikipedia.org/wiki/Unicodeblock_Smileys?fbclid=IwAR0LB-Y2bptAeo9O0qfDe7QXo_ArCgd8ektotaDSVsFqDC5exITcmk39fbQ

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.

Meine Lust ist gerade losgegangen meine Motivation zu suchen. Jetzt sind beide weg!

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!

Ich führe gerade mit dem Rad einen Autokorso an. Es wird gehupt und geschrien – die Stimmung ist riesig!

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!

Rapper rappen, Rockstars rocken, aber was machen eigentlich Popstars?

Hallo in die Runde, Dies ist mein erster Post. Normalerweise, wenn ich einen Bereich als Tabelle formatiert habe, wurde eine die Formel mit dem Drücken der Enter-Taste automatisch bis zum Ende der Tabelle ergänzt. Seit kurzem funktioniert das nicht mehr. Ich muss wohl irgendwas verstellt haben. Könnt ihr mir bitte sagen, wie ich das zurück stelle? Unter einstellungen ist formel erweitern auf automatisch. Aber das löst das Problem nicht. Ich nutze Excel für Mac. Vielen dank für Eure Hilfe Gruß Stephen

Hallo Stephen,

schau mal in der Autokorrektur nach.

LG :: Rene

Ich esse eine Tafel Schoklade meistens innerhalb von drei Minuten auf. Das liegt daran, dass mir wirklich wichtig ist, dass sie frisch ist und nciht so lange geöffnet!

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:

Wer spät zu Bett geht und früh heraus muss, weiß, woher das Wort Morgengrauen kommt.

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:

  1. Fall: leere Menge
  2. Fall: ganzer Bereich
  3. Fall: Überschneidung
  1. Fall:
=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:

  1. Fall: Anfang <= Oktoberfest Anfang? Ja:
    Fall a) Ende vor Oktoberfest Anfang?
    Fall b) Ende nach Oktoberfest Ende?
    Fall c) Ende zwischen Oktoberfest Anfang und Ende?
    […]

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

Klappt! Danke an Andreas Protzmann für diesen Hinweis. Auch clever!

Und schließlich reagiert Christian:

Auch sehr clever! Und sehr elegant! Danke, Christian.

Karotten verbessern die Sehkraft. Bier verdoppelt sie!

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

Heute habe ich gelesen, was auf der Flasche Shampoo steht: Für extra Volumen und mehr Fülle!!! Kein Wunder, dass es mir schwerfällt, mein Gewicht zu kontrollieren! Ich werde ab sofort Geschirrspülmittel benutzen! Da steht drauf: entfernt auch hartnäckiges Fett.

Einfach nicht aufgepasst!

Mit VBA wird eine Userform (eine Maske) erstellt zur bequemen Dateneingabe. Der Wert eines Textfeldes wird als String interpretiert und als solcher bei Dezimalzahlen in eine Excelliste eingetragen. Man erkennt es, weil die Zahlen linksbündig in der Zelle stehen:

Dummerweise wird ein Text immer größer als eine Zahl definiert, so dass eine Formel

=WENN(J2>1000;WAHR;FALSCH)

immer WAHR liefert!

Ein Pessimist flucht, wenn ihm ein Vogel auf dem Kopf kackt. Ein Optimist freut sich, dass Kühe nicht fliegen können.

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!

378 Einkaufswagen. Aber ich nehme den dreibeinigen, nach links ziehenden. Immer.

Wir haben einen Ordner. Nennen wir ihn „Bilanz“. In diesem Ordner liegen zwei Dateien: August.xlsx und September.xlsx. In der Datei „September“ gibt es eine Verknüpfung zu August-Mappe:

='C:\Users\Rene Martin\Documents\Bilanz\[August.xlsx]Tabelle1'!$D$10

Beide Dateien werden geschlossen, der Ordner wird umbenannt, beispiesweise in „Bilanz2021“. Das Öffnen und Aktualisieren der Datei funktioniert problemlos.

Wird haben einen Ordner. Nennen wir ihn „Bilanz“. Darin befinden zwei weitere Ordner: „August“ und „September“. Im Verzeichnis „August“ befindet sich eine Datei August.xlsx, im September-Verzeichnis eine Datei mit Namen September.xlsx. In der Datei „September“ gibt es eine Verknüpfung zu August-Mappe:

='C:\Users\Rene Martin\Documents\Bilanz\August\[August.xlsx]Tabelle1'!$D$10

Beide Dateien werden geschlossen, der Ordner „August“ wird umbenannt, beispiesweise in „August2021“. Das Öffnen und Aktualisieren der Datei funktioniert JETZt nicht mehr:

Ärgerlich!

Grundregeln im Restaurant: Alles, was sich bewegt – bedienen! Alles, was sich nicht bewegt: Putzen! Ausnahme: Der Chef. Den nicht putzen!

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,

Intelligente Überschriften zu schreiben, ist gar nicht so einfach. Schon ein einziger Buchstabendreher kann den ganzen Inhalt urinieren.

… und ich kann es nicht lassen, zu mailen, mit den Infos:

  • aktuelle Formel (Konstrukt von gestern), wo noch etwas fehlt:
    =WENN(ANZAHL(J9;L9;N9)=0;““; WENN(ODER(ANZAHL(J9;L9;N9)=2;ANZAHL(J9;L9;N9)=3); MITTELWERT(J9;L9;N9); „“))
  • drei Eingabezellen
  • wenn keine Eingaben in den Zellen >> Zelle mit Ergebnis ist leer
  • der MW wird berechnet, wenn zwei oder drei Zellen Werte enthalten

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

Haben heute ein Koffein-Shampoo benutzt. Meine Haare sind jetzt schon auf dem Weg zur Arbeit. Ich suche noch die Hose.

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.

Ich achte auf meine Ernährung: Was schmeckt, wird gegessen.

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$25

Warum 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?

Stellt euch vor, ihr lasst eine Dornenhecke um euer Schloss wachsen, um in Ruhe schlafen zu können und dann kommt ein übermotivierter Prinz und versaut wieder alles.

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.

Warum bestellen wir runde Pizzas in quadratischen Schachteln und essen sie dann als Dreiecke?

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)

Entweder finden Jogger Leichen oder sie werden selbst ermordet. – Was soll daran gesund sein?

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 …

Zweite Grundregel der Gastronomie: Immer Butter bei die Tische!

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

Wenn man Kindern erzählt, dass Toffifee aus karamellisierten Kuhaugen gemacht wird, kann man die Schachtel alleine leeressen.

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:

  • Entweder zwei Uhrzeiten eintragen, markieren und herunterziehen
  • Oder eine Formel: Bezug auf die obere Zelle und dann plus 15 (Minute) / 24 (Stunden) / 60 (Minuten):

Bei diesen Temperaturen Bier in einem geschlossenen Auto zu lassen – das ist schon Bierquälerei!

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!

Die wollen doch tatsächlich, dass ich auch Dienstag bis Freitag komme. In der Stellenanzeige stand ausdrücklich: „Facharbeiter für Montage gesucht.“

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!Zellbezug

Prima! 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'!W117

Erstaunlich! 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!

Nur noch zwei Mal joggen, dann ist wieder Weihnachten.

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.

Eine Fee fragte mich, ob ich einen riesigen Penis oder ein tolles Gedächtnis haben möchte. Ich habe gewählt. Aber ich kann mich nicht mehr erinnern, wofür ich mich entschieden habe.

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 …

Mal den Teufel nicht an die Wand! – Aber das ist doch ein Spiegel, in den du hineinschaust …

Amüsant: ich habe eine große Excelliste mit mehrere Tausend Datensätzen. Ich bearbeite sie in PowerQuery:

Ich importiere eine zweite Liste und verknüpfe sie mit einem Left outer Join:

Das Ergebnis sieht in PowerQuery gut aus:

Ich lade die Tabelle zurück nach Excel und erhalte einen Fehler:

Zurück zu PowerQuery versuche ich einen Right outer Join:

Die Ursache? PowerQuery zeigt nur 1.000 Datensätze. Wenn in der Liste DANACH eine Zelle mit einem fehlerhaften Wert steht, wird er bei einem Left Outer Join nicht angezeigt. Erst in Excel. Natürlich kann man sich in PowerQuery auf die Suche nach dem fehlerhaften Datensatz machen und ihn entfernen. Oder in Excel:

Danke an Christa für diesen Hinweis und danke für die Bemerkung, dass die Fehlermeldung in älteren PowerQuery-Versionen eine andere war:

En Dios creemos; todos los demás deben mostrar datos.*)

Hallo Herr Martin,

mein Bekannter hat sich die neue Version angeschaut.

Jetzt lässt sich der Datensatz zwar anlegen, aber es treten neue Fehler auf, die bisher noch nicht vorhanden waren.

In der Tabelle erscheint nach dem Löschen des Datensatzes ein Fehler.

Hallo Herr L.,

öffnen Sie mal bitte eine ältere Version und klicken Sie dort auf Datenverwaltung und löschen Sie einen Datensatz.

Klicken Sie anschließend auf den Datensatz direkt über dem Datensatz, den Sie gelöscht haben.

Sie erhalten den Fehler.

Heißt: DIESER Fehler war schon lange drin, bevor Sie mir die Datei geschickt haben.

Woher kommt er?

Beim Löschen einer Zeile liefert die Zeile darüber einen Fehler:

Sie greifen auf den VALUE dieser Zelle zu – das knallt!

Die Ursache des Fehlers:

In der Spalte K (Membership) greift die Formel für den Wert „Silber“ auf die Zeile darunter zu! Die Formeln dieser Spalte sind falsch! Schon bevor Sie mir die Datei geschickt haben!

=WENN(UND(H2="Spain";J2>200);"Black";WENN(UND(H2="Spain";J2>180);"Platinum";WENN(UND(H2="Spain";J2>150);"Gold";WENN(UND(H3="Spain";J2>140);"Silber";WENN(UND(H2="Spain";J2>130);"Standard";WENN(UND(H2="Spain";J2>50);"Blue";"out of order"))))))

Fazit: es sind nicht neue Fehler – in Ihrem Programm sind einige alte Fehler, für die ich mich nicht verantwortlich zeichne.

An dieser Maske dlgKundenverwaltung habe ich nichts geändert.

schöne Grüße

Rene Martin

*) An Gott glauben wir; alle anderen müssen Daten zeigen!

Liebe Nachbarn – am Mittwoch ist Vollmond. Es kann etwas lauter bei uns werden. Die Werwolfs.

Sehr ärgerlich!

Auf einem Formular soll – unter anderem – der Preis für eine gelieferte Menge berechnet werden – in Abhängigkeit vom Gewicht. Dafür gibt es eine Tabelle:

In der Originaltabelle befand sich der Text „kg“ hinter den Zahlen – den habe ich schnell gelöscht. Eine schöne Aufgabe für XVERWEIS denke ich – eine Formel – alles drin, alles dran …

Mich beschleicht ein Gedanke …

Ich rufe den Kunden an und bitte ihn in einer leeren Excelmappe die drei Zeichen =XV zu tippen. „Ich sehe nichts“ lautet die Antwort. Das heißt: sie haben noch eine ältere Excel-Version, in der die Funktion XVERWEIS und XVERGLEICH noch nicht vorhanden sind. Damit natürlich auch noch nicht die neuen und ach so praktischen Matrixfunktionen. *gggrrrrr*

Ick bin übrigens nur Single weil ick von Haus aus meine Mitmenschen durch meine enorme Attraktivität einschüchtere. Vermute ich zumindest.

Wie oft habe ich es schon gesagt: nicht verbinden! Das bringt nur Ärger!

Richtig: ich erhalte ein Formular mit mehreren verbundenen Zellen – hier: 2 x 2 Zellen sind verbunden.

Ich erstelle einen Verweis auf diese Zelle(n):

#ÜBERLAUF! ist die Folge. War ja klar …

Schatz – wir lebennun seit fünf Jahren hier zusammen – findest du mich schüchtern? – DU WOHNST HIER?

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.

Kann man bei dem Auto noch etwas retten? – Ja, man könnte ein neues Auto zwischen die Nummernschilder schrauben.

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.

 

OLYMPUS DIGITAL CAMERA

Menschen, die mich an der Kasse vorlassen, weil sie sehen, dass ich es eilig habe und nur wenige Dinge in der Hand habe, kommen übrigens in den Himmel.

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 Sub

Das Ergebnis:

Die Unterschiede habe ich mit einer bedingten Formatierung farblich hervorgehoben.

Ein Dankeschön für den wertvollen Hinweis an Bernhard Ramroth.

Schau doch bitte nach, ob die Bremslichter funktionieren! – Vorne oder hinten? – Die Menschheit wird definitiv aussterben!

Sehr geehrter Herr Martin,

ich „spiele“ z.Zt. mit einer Tabelle (Mappe1), in der Excel partout nicht rechnen will. In der Kopfleiste habe ich die für die entsprechenden Spalten die Formeln und die Zellen-Formatierungen eingegeben. Der Wert in Spalte #30 ist die Basis für alle Berechnungen in der entsprechenden Zeile.

Kopiert habe ich nach den Anweisungen der EXCEL-Hilfe und anderen Tipps aus dem Internet.

Für Ihre Hilfe wäre ich Ihnen sehr dankbar.

Hallo Herr M.,

beim Öffnen der Datei meldet Excel, dass ein Zirkelbezug vorliegt. Richtig: in der Statuszeile links unten steht es auch:

Denn: in der Zelle N30 steht die Formel

=EL30

in EL30 steht:

=(100%-EK30)/100%

in EK30 steht:

=(100%-EI30)/100%

in EI30 wird wieder Bezug genommen auf N30 mit:

=(EK30-EH30)/N30

Hier schließt sich der Kreis. Das müssen Sie lösen – sonst rechnet Excel nicht mehr!

Er sagte: „Ich oder der Hund!“ – Manchmal denke ich an ihn, wenn ich Gassi gehe.

PowerPoint-Schulung. Wir bauen uns ein Parlament.

Ich füge ein Diagramm ein, wähle den Diagrammtyp „Ring“ und trage die Daten ein: Sitze im Parlament:

Ich schließe das Datenblatt. Da ich einen Halbkreis haben möchte, bearbeite ich erneut die Daten in Excel:

Und trage die Summe unter den Zahlen ein (für den unteren Halbkreis):

Ich schließe die Daten – die Summe wird nicht übernommen:

Also noch einmal: Daten bearbeiten:

Ich versuche das blaue Ecke über die Summe zu ziehen, damit die Summe im Wertebereich eingeschlossen ist:

Weit gefehlt – es geht nicht! Der Bereich „springt“ immer wieder zurück:

Okay! Verstanden! Zeile löschen, Zahl per Hand eintragen, Datenblatt schließen (?!?) und DANN funktioniert es:

Meine Kaffeemaschine wurde repariert. Der Techniker meinte, die Muffbrieme war verbrömmelt und angeeumelt. Man musste nur die Dichtbrumme abquastern und neue Flanschpaddendranwöhnern. Die sind nun vierfach gefröbbelt und neu umwienert. Na – das hätte ich auch selbst machen können.

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.

Natürlich spreche ich mit mir selbst. Manchmal brauche ich eben eine kompetente Beratung.

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

Nein, ich habe deine Kochkünste nicht kritisiert. Ich habe lediglich gesagt, dass wir den einzigen Hund im Ort haben, der nicht am Tisch bettelt.

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

In einem Meeting einen Zettel hochhalten: „Sprecht ganz normal weiter! Wir werden abgehört! Lasst euch nichts anmerken!“

Lieber Rene

Wie geht es dir? Ich habe wieder mal eine Frage:

Im Anhang findest du eine Tabelle. Was ich möchte ist folgendes:

Ich möchte wissen, welche Zahlen zwischen der kleinsten und der grössten Zahl alle noch fehlen. Am liebsten hätte ich alle fehlenden Zahlen in einer Spalte.

Herzliche Grüsse

Andreas

Hallo Andreas,

ich verstehe nicht ganz – in der Liste befinden sich keine Zahlen, sondern Texte. Texte der Form CHE-xxx.yyy.zzz

Was heißt in diesem Zusammenhang „die kleinste und die größte Zahl“?

LG ::  Rene

Lieber Rene

CHE-000.000.000 (kleinst mögliche), CHE-000.000.001 …..  CHE-999.999.999 (grössmögliche).

Vielleicht sollte ich besser „Nummern“ schreiben, das „CHE-“ könnte man auch weglassen, man könnte auch die Punkte durch tausender Trennzeichen ersetzen, damit es  Zahlen werden: 000’000’001 bis 999’999’999

In der Matrix hat es eine Auswahl von möglichen „Nummern“, eine „Nummer“ ist die Grösste, eine die Kleinste, dazwischen hat es in der Matrix welche, aber es fehlen auch welche. Ich möchte gerne alle Nummern, von der kleinsten Nummer bis zu Grössten Nummer, die mir noch fehlen und so, dass ich Sie in einer Spalte habe und sortieren kann.

Konnte ich mich verständlich ausdrücken?

Herzliche Grüsse Andreas

Hoi Andreas,

kurz nachgeschaut:

die kleinste „Zahl“ in deiner Liste ist CHE-100.000.058, die größte: CHE-499.992.187. Das heißt: wir müssten fast 500 Millionen Zahlen prüfen.

Ich habe es mal mit den Zahlen zwischen 100.000.000 und 101.000.000 versucht – bei einer Formel (bist du drin? -> Zählenwenn) geht Excel in die Knie. Nicht auszudenken, wenn du das in einer Spalte „zusammengefasst“ haben möchtest und das ganze x 500!

Da stürzt Excel ab.

Ich würde es entweder mit PowerQuery lösen oder mit VBA.

Liebe Grüße

René

Hoi Rene

1.000 Dank!

Ich prüfe das nochmals…

Herzliche Grüsse

Andreas

Nachricht von den Nerven: „Falls du uns suchst – wir sind am Ende.“

Erstaunlich! Auf dem letzten Excelstammtisch, den Frank Arendt-Theilen organisiert hat, hat er angemerkt, dass die PowerQuery-Funktion Date.WeekOfYear, die man über Spalte hinzufügen / Datum / Woche / Woche des Jahres nach dem US-amerikanischen Modell rechnet. Zwar verfügt Excel seit vielen Versionen über die Funktion ISOKALENDERWOCHE, welche die KW korrekt nach ISO 8601 berechnet. Auch Outlook unterscheidet bei den Kalenderwochen zwischen USA und Europa. Jedoch nicht PowerQuery. „Haben die das vergessen?“, fragt Frank. Also muss man diese Funktion in PQ nachbauen …

So ein süßer Bub! – Es ist ein Mädchen! – Aber er hat etwas Blaues an. – Sie wird zweifarbig erzogen.

Hallo Herr Martin,

ich zweifle an meinem Verstand – ich kann den Fehler in der Differenzrechnung  Zeile 38 nicht entdecken, obwohl ich alle Formatierungen und Eingaben mehrfach gecheckt habe. Der Fehler tritt nur in der Zelle i38 auf, die Formel habe ich mehrfach neu eingegeben und Zeilen und Zellen gelöscht usw. der Fehler bleibt  – HILFE!

Ich bin froh, dass es nur eine private Tabelle ist und nichts Wichtiges, aber so was ist doch nicht zu ertragen…

Übrigens: Ihre Sprüche sind grandios! Wenigstens konnte ich mehrfach laut lachen!

Mit freundlichen Grüßen Ulrike

Hallo Frau H.,

Danke für das Lob der Sprüche – manchmal befürchte ich, dass einige nur auf meine Seite wegen der dummen Sprüche klicken. Die sollen doch mit mir über Excel diskutieren! *lach*

Ich verstehe Ihr Problem nicht:

130,45- 150,31 = 19,86

SO haben Sie es in den anderen Zeilen auch gerechnet.

Wer von uns beiden „denkt falsch“?

Liebe Grüße

Rene Martin

Guten Tag Herr Martin,

erstmal danke für die schnelle Reaktion, die ich zuerst nicht verstanden habe. Beim erneuten Öffnen der gesendeten Mail konnte ich sehen, dass die Zeilen, um die es geht, darin nicht zu sehen sind, obwohl ich das ganze Blatt schicken wollte. Das liegt evtl. an der Druck-einstellung. Ich schicke die Mappe nochmal und stelle fest, dass es Zeile 39 ist, wo der Fehler auftritt (ich hab das mit der Nummerierung der Positionen verwechselt! – war ja auch schon etwas Gaga nach dem vielen rumprobieren) Die Formel heißt also: <=H39-G39> und müsste = ergeben, tut sie aber nicht.

Also noch mal meine Bitte, den Fehler zu identifizieren.

Liebe Grüße Ulrike

Hallo Frau H.,

Ich würde gerne helfen, aber Zeile 39 ist leer!

Liebe Grüße

Rene Martin

Dann bitte bei Zeile 40 nachschauen, folgende Beträge: 129,74 € – 61,67 € ergibt  nicht -67,80€ sondern – 68,07€! Ulrike

Da ist ein Zahlendreher in Ihrem Kopf oder ihrer Wahrnehmung, liebe Frau H.

In der Zelle G40 steht der Wert 129,47 und nicht 129,74:

Vier – sieben – NICHT: sieben – vier!

Liebe Grüße

Rene Martin

Oh mein Gott— gut, dass ich niemand anders gefragt habe – es ist zu peinlich!

Ganz lieben Dank für die Mühe! Ich werde die Seite weiterempfehlen – nicht nur wegen der Sprüche!

Gruß Ulrike

Die Ehe ist ein lustiges Wort für die Übernahme eines erwachsenen männlichen Kindes, dessen Eltern nicht mehr in der Lage waren, die Situation zu bewältigen.

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.

Ich bin froh, dass ich mein Essen nicht selbst jagen muss. Ich wüsste gar nicht, wo Pommes leben!

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.

Schade, das es so teuer ist, Leute auf den Mond zu schießen. Ich hätte da ein paar.

Hallo René,

ich habe ein für mich unerklärliches Excel Phänomen in der Version 365.

In der Zelle C1 habe ich berechnet: 6*7=42 (s. Anlage).

Dieses Ergebnis habe ich zum Datum 18.07.2017 addiert und erhalte das Datum 24.01.6837.

Addiere ich 42 als Zahlenwert zum Datum 18.07.2017, erhalte ich das richtige Ergebnis 29.08.2017.

Hast Du eine Erklärung?

Liebe Grüße
  Traudl

Hi Traudl,

du MULTIPLIZIERST das Datum (18.07.2017 = 42.934) mit 42

42.934 x 42 = 1.803.228 = 24.1.6837

Stimmt schon!

Rene

Oh je René, danke. 

Dass ich multipliziert habe, ist mir gar nicht aufgefallen. Zudem habe ich die Berechnung mehrfach wiederholt.

Wahrscheinlich Corona Blackout.

Sorry, dass ich Dich mit so einer Lapalie belästigt habe.

Liebe Grüsse 

Traudl 

Es ist total spannend älter zu werden. Man lernt jeden Tag neue Knochen kennen.

Hi René,

ich stehe mal wieder auf dem Schlauch:

Kannst Du mir sagen, wie ich, wenn in einem Tabellenblatt einen Wert aus einem anderen Reiter angegeben ist, in der daneben liegenden Spalte automatisch den Wert aus einer anderen Spalte der Tabelle in dem anderen Reiter ausgebe?

In A2 habe ich per Dropdown mit INDIREKT „<10%“ aus tab_AntwortKategorie[Kategorie].

In B2 möchte ich automatisch den dazugehörigen Wert aus tab_AntwortKategorie[Wert].

Was muss ich in B2 eingeben?

Ohne INDIREKT wüsste ich es, aber wie gesagt – ich stehe einfach auf dem Schlauch.

Momentan „fuhrwerke ich wild (und falsch) rum“, z.B.
=WVERWEIS($AE4;tab_AntwortKategorie[Kategorie];[Wert])

Bye

Michael

Hallo Michael,

wenn du den Wert DANEBEN haben möchtest, musst du den SVERWEIS verwenden, nicht den WVERWEIS. „S“ steht für „senkrecht“, heißt: die Liste ist von oben nach unten aufgebaut.

Schau mal in mein Beispiel:

Hilft das?

Liebe Grüße

Rene

Hi René

aaaaah (über eigene Dummheit)!

Ja, danke.

Bye

Michael

Alexa, wie verhindere ich, dass meine Daten ins Internet kommen?

Hallo, ich habe auch so ein Problem in meinem Kalender zur Arbeitszeitberechnung mit den Rauten. Excel gibt mir aber den Hinweis, das entweder die Zelle zu klein ist, oder ein Negativ Wert darin enthalten ist. Bei mir scheint es wohl der Negativ Wert zu sein da eine Formel in der Zelle enthalten ist bei der ich zb. 8 von 9 in meinem Fall Stunden abziehen möchte. Da ich die Zeiten über Dropdown Listen eingebe, habe Rauten in den Zellen solange ich noch keine Werte eingegeben habe. Das sieht natürlich nicht so gut aus. Nun meine Frage. Kann man diese Rauten auch einfach nur ausblenden ohne das die darin enthaltenen Formeln auch ausgeblendet sind? Ich hoffe das ich mein Problem verständlich erklären konnte.

Gruß Uli.

Hallo Uli?

Ich würde um die Formel (wie sieht sie aus?) beispielsweise
=C2-B2
eine WENN-Funktion bauen:

=WENN(C2-B2<0;““;C2-B2)

Hilft das?

Liebe Grüße

René Martin

Hallo Herr Dr.Martin,

Danke Ihnen nochmal für ihre Hilfe, es hat geklappt mit der Formel.

War für Sie bestimmt eine Logische und einfache Sache, aber für mich als Blutiger Anfänger ein Riesen Problem. Würde mich freuen wenn ich mal wieder so ein Problem hätte, mich an Sie wenden dürfte.

Mit freundlichen Grüßen U.

Klar, Uli,

einfach schreiben! Ich helfe gerne

Liebe Grüße aus dem verregneten München

Rene Martin

PS: ich mag den Begriff „logisch“ nicht. Es läuft eine gewisse erlernbare Mechanik ab. Jeder von uns hat ein bestimmtes Wissen und erkennt Lösungen „intuitiv“ und kann sie sicherlich auch beschreiben, warum dieser oder jeder Weg der richtige (oder der geeignete) ist. Also: Kopf hoch, dranbleiben, schmunzeln – ich halte Excel für wichtig – nicht, weil ich häufig damit arbeite, sondern weil sehr, sehr viele Menschen häufig damit arbeiten.

Seit ich gesehen habe, wieviel schlanker ein Bär nach seinem Winterschlaf ist, kommt mir das Konzept noch viel attraktiver vor.

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

Ich habe gerade „ach-da-brauchst-du-nichts-draufschreiben-man-sieht-ja-was-drin-ist“ aus der Gefriertruhe geholt und bin gespannt, was ich heute koche.

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 🙂

Neuer Trend bei Deutschlehrern: Kommasaufen.

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:

Warum sind Blondinenwitze immer so kurz? – Damit auch Männer sie verstehen!

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?

Oma: Homofiss – iss datt wedder son Schwulengrupp? – Enkel: Oma: datt heet Homeoffice. Net Homo-fiss!

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.

Habe meiner Pflanze vorgeschlagen, sie nur noch einmal im Monat zu gießen. Sie ist darauf eingegangen.

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!“

In der Singlebörse wollte sich jemand mit mir treffen um spazieren zu gehen. Solche Sportsfreaks blockier ich doch gleich!

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

Mut ist, wenn du morgens um 4 Uhr knallvoll nach Hause kommst, deine Frau mit dem Besen in der Ecke steht und du sie fragst: „Bist du am Putzen oder fliegst du noch weg?“

Ich habe bereits darüber geschrieben. Bill Jelen hat einen Artikel darüber geschrieben:

https:// www .youtube.com/watch?v=iaJv6E0GRC4

Auch auf der Seite von Microsoft ist zu lesen:

https: // support.microsoft.com/en-us/office/-spill-volatile-size-05aad07c-947e-4c9b-bd6f-7b1f8ae6a7dc?fbclid=IwAR3-mdf2DsQoMOTCOt5Z2m2i2ib5_rVYLikGthKeQllPdFZM94Bfbxz_2bc

Eben:

Dynamic array resizes may trigger additional calculation passes to ensure the spreadsheet is fully calculated. If the size of the array continues to change during these additional passes and does not stabilize, Excel will resolve the dynamic array as #SPILL!.

Nochmal langsam:

This error value is generally associated with the use of RAND, RANDARRAY, and RANDBETWEEN functions. Other volatile functions such as OFFSET, INDIRECT, and TODAY do not return different values on every calculation pass.

Ich probiere es aus: In einer Spalte stehen Zufallswerte:

=ZUFALLSBEREICH(„1.1.2020″;“31.12.2020“)

Die beiden Funktionen SORTIEREN und SORTIERENNACH funktionieren problemlos:

Jedoch erzeugen die beiden Funktionen FILTER und EINDEUTIG regelmäßig einen Fehler:

=FILTER(A3:A100;A3:A100>44000)
und
=EINDEUTIG(A3:A100)

Allerdings nicht immer:

Die Funktion ZUFALLSMATRIX, welche die Funktion ZUFALLSBEREICH in den Parametern min und max verwendet, bleibt erstaunlicherweise stabil:

=ZUFALLSMATRIX(5;5;MIN(A2:A100);MAX(A2:A100);WAHR)

Verwendet ZUFALLSMATRIX jedoch die Funktion ZUFALLSBEREICH in den Zeilen oder Spalten, ist #Überlauf ein häufiger Fehler:

=ZUFALLSMATRIX(M11;M12)
In M11 und M12 steht:
=ZUFALLSBEREICH(1;10)

Und schließlich kann auch die Funktion SEQUENZ nicht mit ZUFALLSBEREICH zusammen arbeiten:

=SEQUENZ(M11;M12)

Fazit: Finger weg von den neuen Arrayfunktionen im Zusammenhang mit den Zufallsfunktionen
ZUFALLSBEREICH
ZUFALLSZAHL
ZUFALLSMATRIX

Eben: Dynamic array resizes may trigger […] #SPILL!.

Wenn du die Wahl hättest zwischen Traumfrau und Traumauto – welche Felgen würdest du dann nehmen?

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!

Glück ist das einzige, was sich verdoppelt, wenn man es teilt. Und Bakterien!

Gestern war ich sehr verblüfft und erstaunt. Ich habe eine Excelmappe mit einer intelligenten (strukturierten, dynamischen, formatierten) Tabelle. In einer ANDEREN Datei greife ich darauf zu – per SVERWEIS, INDEX und VERGLEICH oder XVERWEIS:

Also beispielsweise über:

=SVERWEIS([@Name];Datenquelle.xlsx!tbl_Schlumpf[#Alle];2;FALSCH)

oder:

=INDEX(Datenquelle.xlsx!tbl_Schlumpf[Schlumpf];VERGLEICH([@Name];Datenquelle.xlsx!tbl_Schlumpf[Name];0))

oder:

=XVERWEIS([@Name];Datenquelle.xlsx!tbl_Schlumpf[Name];Datenquelle.xlsx!tbl_Schlumpf[Schlumpf])

Beide Dateien, die sich im gleichen Ordner befinden, werden geschlossen. Nur die Zieldatei wird geöffnet. Das Ergebnis: Fehlermeldungen bei allen drei Formeln:

Ich ersetze die Bezüge, also

Datenquelle.xlsx!tbl_Schlumpf[Name]

durch

[Datenquelle.xlsx]Quelle!$A:$A

Das ist nicht schön! Aber – es funktioniert! Hat Microsoft vergessen DAS bei intelligenten Tabellen zu implementieren? DAS ist ja ein Schritt zurück! DAS will ich eigentlich nicht (mehr). Immerhin: es funktioniert. So kann ich die Zieldatei öffnen, die Werte werden aktuell angezeigt, ohne dass die Quelldatei offen sein muss.

Danke an Christa für den Hinweis, dass man die Verknüpfung über PowerQuery organisieren kann.

Und: vielen Dank an den Hinweis von Mourad Louha: DAS IST GEWOLLT!

https://support.microsoft.com/en-sg/help/2299192/links-to-data-tables-residing-in-external-files-display-ref?fbclid=IwAR32YcJ4HKAB_B_lMBHB17OtX8y8rIV0dVoFCFrmeYTjH4zxnPADLGigbM8

Nach den ganzen Fleisch- und Wurstskandalen der letzten Monate und Jahre habe ich beschlossen nur noch Lebkuchen zu essen. Einen Lebkuchenskandal gab es noch nie!

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: İ.

Hast du schon wieder mein Deo benutzt? – Ich bin Robin Hood! Ich stehle und verteile es unter den Armen!

Ups, das ist mir noch nie aufgefallen! Warum? Weil ich SO nicht arbeite.

Aus einer Datenquelle wird mit PowerQuery eine Abfrage gestartet. Auf diese Liste wird eine Formel aufgesetzt, allerdings nicht in der Schreibweise

=KKLEINSTE(Artikel[Einzelpreis];Artikel[@Einzelpreis])

rechnet, sondern in der Bezugsschreibweise:

=KKLEINSTE($F$2:$F$78;ZEILE(A1))

Die Originaldatenquelle ändert sich – sie wird kleiner. Die Verbindung wird aktualisiert:

Die Folge: Die Formel wird angepasst, beispielsweise in:

=KKLEINSTE($F$2:$F$15;ZEILE(A1))

Fehlermeldungen sind die Folge.

Ändert sich die Liste erneut und wird nun länger, werde diese Bezugsfehler natürlich nicht korrigiert …

Fehler in der Berechnung sind die Folge.

Heißt: Wenn schon (intelligente/formatierte/dynamische) Tabellen – dann bitte die Bezüge auf diese Tabellen in Tabellenschreibweise und nicht in Bezugsschreibweise! Sonst gibt es Ärger!

Die einen gehen nachts feiern. Andere haben nachts wilden Sex. Und ich lade nachts mein Handy auf.

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 …

Na klar, kann man zum Frühstück schon Pralinen essen. Steht doch drauf: „Moin, Cherie“

Hallo Rene,

bin gerade ein wenig verwirrt aufgrund der Formel Nettoarbeitstage.INTL, vermutlich liegts bei mir und nicht an der Formel.

Ich nehme hier einfach mal den 12.10.2020 als Ausgangs- und den 19.10.2020 als Enddatum. Die Formel in E2 ist =NETTOARBEITSTAGE.INTL(A2;B2;11), mit dem Parameter 11 möchte ich die Sonntage ausklammern.

In D2 steht lediglich =TAGE(B2;A2).

Müssten sich die beiden Werte nicht eigentlich unterscheiden, da ein Sonntag dazwischen liegt?

Wenn ich das Ausgangsdatum weiter in die Vergangenheit setze, dann gibt es eine größere Diskrepanz zwischen den beiden Formelergebnissen, z.B. beim 01.09.2020

Was mache ich denn falsch, weißt du das?

Viele Grüße, Dominic

Moin Dominic,

schau mal:

TAGE rechnet wie die Differenz wie DATEDIF: Ende – Anfang. Klar: 19 – 12 = 7.

NETTOARBEITSTAGE und NETTOSARBEITSTAGE.INTL ist die Anzahl der Arbeitstage in einem Datumsbereich außer Sa/So (NETTOARBEITSTAGE) oder außer dem gewählten Tag / den gewählten Tagen.

Heißt: wenn Anfang und Ende gleich sind liefert TAGE immer 0, NETTOARBEITSTAGE meistens 1 – eben: ein Tag liegt in diesem Zeitraum.

Für deinen größeren Zeitraum ist das auch korrekt:

Liebe Grüße

Rene

Nur noch ein paar Stunden blöd gucken und dann ist Feierabend.

Hallo Rene,

vielleicht ist das so ein Ding, was du direkt weißt. Ich öffne via Makro eine Datei. In dieser Datei befinden sich Verknüpfungen zu externen Mappen, die zwar kein Mensch braucht, aber die nun mal drin sind, weil die Dateien immer vom Kunden kommen und der damit wer weiß was macht. 😉

Beim öffnen erscheint immer dieser Hinweis und das Makro läuft natürlich nicht weiter:

Kriegt man das irgendwie weg? Bzw. gibt es einen Befehlt der automatisch „Aktualisieren“ oder „Nicht aktualisieren“ auswählt?

Application.DisplayAlerts = False greift hier nicht.

Danke dir und viele Grüße,

Dominic

####

Hat sich schon erledigt – UpdateLinks:=0 nach dem „Open“-Befehl. Manchmal ist der Makro-Rekorder schon ganz praktisch.

####

Hallo Dominic,

ich muss nur ein bisschen warten – und schon lösen die Leute alleine ihre Probleme.

Ich hätte es trotzdem gewusst.

Hintergrund: Die IT einer großen Behörde beschließt im Frühjahr 2018 das Laufwerk P von allen Anwendern zu löschen. Ab jetzt soll es nur noch Q geben. Jeder Anwender soll seine Dateien von P nach Q kopieren, dabei anschauen, ob er die Dateien noch braucht …

Nach fünf Monaten haben sie festgestellt: ups – einige Tausend Dateien haben ja Verknüpfungen auf andere Dateien auf Laufwerk P. Dumm! Die Verknüpfungen funktionieren nicht mehr.

Ich habe ihnen ein VBA-Tool geschrieben:

* liste alle (Excel-)Dateien auf

* Anwender wählt einen Ordner und legt fest welcher Ordner durch welchen ersetzt werden all. Bspw.: P:\Eigene Dateien\Controlling\Excel\2017 durch Q:\Eigene Dateien\ Controlling\Excel\2017

* öffne alle Excelmappen in diesem Ordner (und Unterordner – kann ausgewählt werden)

* prüfe, ob Verknüpfungen drin sind (in Tabellenblättern, Namen, Bedingten Formatierungen, Datenprüfungen) und putze

* speichere und schließe

Problem beim Öffnen:

* Verknüpfungen (wie du beschreibst)

* AutoOpen-Makros

* geschützte Dateien oder Blätter (mit oder ohne Kennwort)

uff!

Einige Programmierstunden, einige Nachbesserungen, … am Ende habe ich nie mehr etwas gehört … wahrscheinlich konnten sie alle (?) Dateien öffnen und putzen.

Liebe Grüße Rene

Jahrelang hat man uns eingeredet, dass nach 18 Uhr gegessene Kohlenhydrate dick machen. Und jetzt kommt raus: Kohlehydrate wissen gar nicht, wie spät es ist.

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

Schau mich nicht in diesem Ton an!

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

Früher war alles leichter. Ich auch.

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.

Sommer kommt – fuck the Pullis!

Das Telefon klingelt.
„Hallo René, ich bin’s: Angelika. Hast du mal nen Moment Zeit?“

Kennt ihr Gespräche, die so beginnen? „Hast du mal einen Moment Zeit?“ Das dauert normalerweise sehr, sehr lange. Egal – ich nehme mir die Zeit für Angelika.

„Jep, schieß los – was gibt’s?“
„Ach mein Excel nervt wieder! Ich bereite gerade eine Schulung vor. Excel zeigt mir immer nur die Formeln an, nicht die Ergebnisse. Ich bin völlig verzweifelt; ich weiß nicht, was ich machen soll!“
„Hast du die Formel-Anzeige eingeschaltet? – Formeln / Formeln anzeigen.“
„Ne, da habe ich schon geschaut.“
„Wie sind denn die Zellen formatiert? Wie lautet das Zahlenformat“
„Na – als Standard sind sie formatiert!“
„Mach mal einen Doppelklick auf die Zelle mit der Formel! Und dann [Enter]“
„Ach – jetzt geht es! Was war das?“
„Ich frag mal zurück: was hast du gemacht?“

„Also von vorne. Ich bereite gerade die Excelschulung für nächste Woche vor. Ich habe eine Liste mit Telefonnummern:

Ich füge Spalten ein und zeigen den Teilnehmern den Assistenten Daten / Text in Spalten.

Dann will ich die Teile wieder zusammenbauen. Und: egal, was ich verwendet habe: das &-Zeichen, VERKETTEN, TEXTKETTE, TEXTVERKETTEN … immer die Formel statt das Ergebnis!“

„Klar! Du hast die Telefonnummern als Text formatiert.“ Durch das Spalten Einfügen übernimmst du das Textformat. Und deshalb rechnet die Formel nicht mehr, sondern wird als Text eingefügt.“

„René – danke! Ich wusste doch, dass Excel nervt. Und dass du mir helfen kannst!“

Woran erkennt man, dass man zu viel mit Excel gearbeitet hat? Wenn man die Augen schließt und sich das Excel-Grid in die Netzhaut eingebrannt hat (Daniel Kogan – excelhero)

Verblüffend!

Man schreibe in eine Zelle den Text ‚WAHR. Das Ergebnis wird linksbündig als Text angezeigt. Das Gleiche passiert, wenn man eine Zelle als Text formatiert und anschließend mit den Buchstaben WAHR füllt.

Soweit so gut. Die Überprüfung mit der Funktion ISTTEXT bestätigt, dass ein Text in der Zelle steht; ISTLOG verneint die Existenz eines booleschen Wertes (WAHR oder FALSCH). Auch die Überprüfung mit =Zelle=WAHR und =ZELLE=“WAHR“, verneint das erste und bestätigt das zweite:

Irritierend ist nun, dass die Funktion ZÄHLENWENN 0 liefert, wenn man den TEXT „WAHR“ zählt:

Der Blick in den Funktionsassistenten irritiert:

Verblüffend!

Ich lese „SPÜLMASCHINENFEST“. Aber nicht wann, wo, Kosten? Weiß jemand mehr darüber?

Guten Tag Herr Martin,

ich hatte schon lange kein Problem mehr mit Excel, sicherlich auch dank Ihrer Kurse, die ich mir in LINDEDIN Learning immer wieder einmal ansehe.

Heute nun habe ich ein Problem, bei dem Sie mir vielleicht helfen können. Wenn es allerdings nicht so nebenher geht, dann können Sie mir dies gerne mitteilen, dann muss ich weiter forschen.

Ich möchte mir hervorgehobene Zellen zählen lassen, genau genommen, möchte ich beim Wahlverfahren D’Hondt sofort sehen, wie viele Sitze hat Liste 1, 2…

Möglicherweise geht es auch nicht mit dem Zählen der hervorgehobenen Zellen sondern anders.

Ich habe es mit SVERWEIS versucht, was leider dann ab der 5. Zeile ein „NV“ brachte – mir leider unverständlich. Mit INDEX und VERGLEICH komme ich auch nicht weiter, weil ich ja keine genaue Zeilen oder Spalenzahl angeben kann. Diese kann ja – je nach Höchstwert – variieren.

Ich sende Ihnen die Datei einmal zu und freue mich auf Ihre Antwort, kann aber auch verstehen, wenn Sie schreiben: Kann ich Ihnen nun leider nicht mitteilen, dauert zu lange…

Trotzdem danke für Ihr offenes Ohr.

Ich wünsche Ihnen noch einen schönen Tag.

Hallo Frau P.,

das ist eine hübsche Fingerübung.

Ein paar Anmerkungen:

1. man kann Farben in Excel nicht zählen. Und wenn ich jetzt von Ihnen die Funktion ZELLE höre – mit Einschränkungen ja. Aber – diese Funktion reagiert nicht auf Formatänderungen. Und: zeigt auch nicht alle Formate an!

2. Ich würde es klassisch mit SUMMEWENN lösen. SVERWEIS, INDEX & co greifen auf mehrere Spalten zu. Mit der neuen Funktion XVERWEIS kann man es auch lösen – aber ich weiß nicht, ob sie diese schon haben. Werfen Sie einen Blick auf meine Lösung in Spalten M:O, bzw. zusammengefasst in Spalte Q.

=KGRÖSSTE($B$6:$E$10;ZEILE(A1))
=SUMMENPRODUKT((M6=$B$6:$E$10)*ZEILE($A$1:$A$5))
=INDEX($A$6:$A$10;N6)

3. Für welches Beispiel/Land verwenden Sie das? Ich lese bei wikipedia, dass noch die Schweiz, Spanien, Portugal, Belgien, Polen und Finnland dieses Verfahren verwenden. Sie wissen, dass dies in Deutschland 1985 durch das Hare-Niemeyer-Verfahren abgelöst wurde. Ich hätte es nämlich fälschlicherweise in meinem Excel-Formelbuch erläutert … und dann gemerkt, das wir so (in Deutschland) gar nicht mehr rechnen.

zusammengefasst

Hallo Herr Martin,

herzlichen Dank für Ihre schnelle Rückmeldung und Ihren Vorschlag der Berechnung.

Ich habe es jetzt noch einmal nachgebaut und verstanden, obwohl ich niemals auf die Formel gekommen wäre.

Zu Nr. 3:

Ich weiß nur, dass die Hochschule Furtwangen den Vorschlag unserer Justiziarin aus Stuttgart aufgegriffen hat und diesen in ihrer Wahlordnung nun festgelegt hat. Wir haben bisher mit Hare-Niemeyer gewählt. Aber leider soll es auch bei D’Hondt bleiben, obwohl wir eine Hochschule mit drei Standorten sind und unserer kleinster Standort mit diesem Wahlverfahren ernste Schwierigkeiten bekommen könnte. Ich hatte das (als Wahlleitung) angemerkt, es bleibt trotzdem bei D’Hondt.

Ich wünsche Ihnen noch einen schönen Tag und weiterhin viel Erfolg bei Ihrer Arbeit.

Ei rät nicht. Eiweiß.

Excel nervt mal wieder. Kennst du das? Die Funktion DEZINBIN, die Dezimalzahlen in Binärzahlen verwandelt hat eine Obergrenze. Nach 511 ist Schluss:

Auf der Seite

https://im-coder.com/dezimal-zu-binaer-konvertierung-fuer-grossen-zahlen-in-excel.html

Findet man eine Lösung zur Erweiterung:

=DEZINBIN(REST(QUOTIENT($A$13;256^4);256);8)&DEZINBIN(REST(QUOTIENT($A$13;256^3);256);8)&DEZINBIN(REST(QUOTIENT($A$13;256^2);256);8)&DEZINBIN(REST(QUOTIENT($A$13;256^1);256);8)&DEZINBIN(REST(QUOTIENT($A$13;256^0);256);8)

Nicht sehr komfortabel, denkt Christian Neuberger, der auf unserem Excelstammtisch andere Lösungen vorgestellt hat.

Wenn die Anzahl der Stellen fest sein soll, beispielsweise 40, könnte man die Zahl mit der Funktion SEQUENZ umwandeln:

=TEXTKETTE(KÜRZEN(REST(A15/2^SEQUENZ(40;;40-1;-1);2)))

In A15 befindet sich die Zahl, die konvertiert werden soll.

Kann man das variabel halten, fragt er sich? Klar – mit einer Hilfszelle (hier: A16):

Die Formel:

=TEXTKETTE(KÜRZEN(REST(A15/2^SEQUENZ(A16;;A16-1;-1);2)))

oder auch ohne Hilfszelle:

=TEXTKETTE(KÜRZEN(REST(A15/2^SEQUENZ(KÜRZEN(LOG(A15;2)+1);;KÜRZEN(LOG(A15;2));-1);2)))

Eine sehr, sehr clevere Lösung!

Vielen Dank an Christian Neuberger, der mithilft, dass Excel nicht mehr ganz so nervt …

Mein Elan-Empfang ist heute wieder schlecht. Und mein Tatenvolumen ist schon aufgebraucht.

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.

Hatte neulich Stress. Sagte cool: „Fass mich nicht an – ich kann Kibotu“. Muss ja keiner wissen, dass das Kinderbodenturnen heißt.

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

Es ist mit egal, wer dein Vater ist. Solange ich hier angle, läuft mir keiner übers Wasser!

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!

Manche Faultiere fahren den Kreislauf so weit runter, dass sie aus Versehen sterben. – Als ich das gelesen habe, habe ich mich erstmal aufrecht hingesetzt.

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!

Beifahrer, die sagten „mach doch mal die üble Musik leiser“, fragten auch: „Wann darf ich wieder aus dem Kofferraum raus?“

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

Liebe ist, wenn man aussieht wie ein Sack Kartoffeln, aber angeschaut wird wird wie eine heiße Fritte.

Excelschulung. Wir diskutieren über den Unterschied zwischen Zellen formatieren (Nachkommastellen „wegformatieren“) und der Funktion RUNDEN. Ein Teilnehmer beschwert sich, dass dann die Summe der gerundeten Werte möglicherweise nicht mehr mit der urprünglichen Summe übereinstimmt. Ich zeige ihm ein Diagramm:

Egal wie man rundet – es ist falsch!

Ich komme aus einer Zeit, in der freie Knöchel ein Zeichen von falscher Hosengröße war. Man empfand es als peinlich!

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)

Männer kennen nur zwei Empfindungen: hungrig und geil. Wenn du also einen Mann ohne Erektion siehst, dann mach ihm was zu Essen.

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:

Ein Worddokument mit Tabulatoren
… in Excel

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!?!

Daten / Text in Spalten mit Tabstopps
Daten / Text in Spalten mit 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.

Die Nasentropfen brennen – helfen aner wie Sau! – Das ist Türschlossenteiser!

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.

Schokolade erweitert dein Speckdrum.

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)

Ich hatte schon so lange keinen Sex – ich weiß gar nciht mehr wie das geht. – Ist wie Fahrradfahren. – Brauch ich nen Helm?

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

Sex zu haben, hilft das Gedächtnis fit zu halten. Ein schönes Neues Jahr 2012!

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 jetzt einen eigenen Sporttrainer! Dein Sofa? – Ja: mein Personal Couch.

Ich verstehe es nicht: In der Zelle G1 steht die Funktion ZUFALLSBEREICH(1;5) und liefert folglich eine ganze Zahl zwischen einschließlich 1 und 5. Die Funktion SEQUENZ in Zelle 1 baut so viele Zeilen (1 bis 5) und zwei Spalten auf.

Bei einigen Zahlenwerten der Funktion ZUFALLSBEREICH kommt es zu einer Fehlermeldung: ÜBERLAUF:

Auch das Editieren mit [F9] liefert nicht die Lösung:

Ich verstehe es nicht!

Gib nicht Weihnachten die Schuld, dass du dick geworden bist. Du warst schon im August fett.

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!

Je mehr Männer ich kennenlerne, desto netter finde ich Hunde

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.

Nüchtern ins Bett? Was kommt als nächstes? Pünktlich zur Arbeit oder was?

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!

1 2