Adventskalender mit Schokolade, Beauty, Tee und Duftkerzen. Gibt es für unser Alter Adventskalender von Voltaren, Thermacare oder etwas Schönes mit Ibu?

Hallo Rene,

Vor einiger Zeit präsentierte Martin Weiß auf seinem Blog eine Formel, die sämtliche Formelzellen innerhalb eines definierten Bereichs auflistet.

https://www.tabellenexperte.de/alle-formeln-auflisten/

Daraufhin habe ich mir überlegt, wie sich dieses Prinzip auf ein vollständiges Tabellenblatt übertragen lässt. Eine pauschale Markierung des gesamten Blattes würde Martins Formel jedoch überlasten, da hierbei astronomische 17.179.869.184 Zellen abgefragt werden müssten.
Mein Lösungsansatz basiert auf folgendem Kniff: Ruft man über das „Gehe zu“-Menü (Strg+G oder F5) unter „Inhalte…“ die Optionen für Formeln auf, selektiert Excel präzise alle Zellen, die tatsächlich eine Formel enthalten. Diesem unzusammenhängenden Bereich weise ich anschließend im Namensfeld einen frei wählbaren Namen zu, beispielsweise _Formeln.
Es hat mich allerdings beträchtliche Zeit und intensive Recherche gekostet, eine Formel zu entwickeln, die aus diesen verstreuten Zellbereichen eine konsistente Liste generiert.
Das Zusammenspiel der Funktionen läuft wie folgt ab:
Der ersten LAMBDA-Funktion, L_ZellenListe, wird der definierte Bereich übergeben (Syntax: =L_ZellenListe(_Formeln)). Sie erzeugt die Überschriften für die dreispaltige Tabelle und initiiert die zweite Funktion, L_ZellenListe_Rekursion. Letztere durchläuft die einzelnen Teilbereiche von _Formeln mittels rekursiver Aufrufe, bis der gesamte Komplex abgearbeitet ist, und gibt schließlich die finale Liste aus.
Flexibler Einsatz: Grundsätzlich lässt sich jeder beliebige Bereich in die Funktion L_ZellenListe einspeisen. Die einzige Voraussetzung dafür ist, dass dieser entweder als Name definiert oder direkt in Klammern () eingeschlossen ist, z.B. L_ZellenListe((A1:C5;K15000:K15005)).
Salü
Ernst

Kochrezepte für Spaghetti Bolognese sollte so beginnen: 1. Ziehen Sie das weiße T-Shirt aus!

Hallo Rene,

ich habe die beiden LAMBDA-Funktionen L_Zellenliste und L_ZellenListe_Rekursion im Namensmanager definiert.

L_Zellenliste

=LAMBDA(Auswahl;VSTAPELN(HSTAPELN("Adresse";"Formeln";"Wert");L_ZellenListe_Rekursion(Auswahl; 1)))

L_ZellenListe_Rekursion

=LAMBDA(Auswahl;Bereich;LET(_AB; INDEX(Auswahl; ; ; Bereich); _AD; HSTAPELN(ZUSPALTE(ADRESSE(ZEILE(_AB); SPALTE(_AB); 4)); ZUSPALTE(WENNFEHLER(FORMELTEXT(_AB); "KEINE FORMEL"));ZUSPALTE(WENN(ISTLEER(_AB);"";_AB))); WENN(Bereich = BEREICHE(Auswahl);_AD;VSTAPELN(_AD; L_ZellenListe_Rekursion(Auswahl; Bereich + 1)))))

Wobei L_Zellenliste die Funktion L_ZellenListe_Rekursion aufruft.

Wenn ich aber über F3 -> Liste einfügen… mir die Namensdefinitionen im Tabellenblatt auflisten lasse wird folgendes angezeigt.

L_Zellenliste

=LAMBDA(Auswahl;VSTAPELN(HSTAPELN("Adresse";"Formeln";"Wert");L_ZellenListe_Rekursion(Auswahl; 1)))

L_ZellenListe_Rekursion

=Auswahl Bereich  INDEX(Auswahl; ; ; Bereich)  HSTAPELN(ZUSPALTE(ADRESSE(ZEILE(_AB); SPALTE(_AB); 4)); ZUSPALTE(WENNFEHLER(FORMELTEXT(_AB); "KEINE FORMEL"));ZUSPALTE(WENN(ISTLEER(_AB);"";_AB))) Bereich = BEREICHE(Auswahl) _AD _AD

Salü

Ernst

#####

Hi Ernst,

Stimmt: bei mir steht am Ende auch _AD_AD

Gibt es eine Obergrenze bei LAMBDA-Funktionen? Sie ist auffälligerweise 231 Zeichen lang … Eigentlich nicht, oder?

Ein interessantes Phänomen – danke für den Hinweis

Liebe Grüße

Rene

#####

Hallo Rene,
die SI sagt folgendes dazu.
Dass eine LAMBDA-Namensdefinition über die Funktion Namen einfügen ➔ Liste einfügen fehlerhaft oder kryptisch dargestellt wird, liegt an einem bekannten Architektur-Konflikt in Excel zwischen einer uralten Kernfunktion und dem modernen Formel-Konstrukteur.
Der Fehler äußert sich meistens so, dass statt der sauberen Formel plötzlich ein fehlerhafter String wie =“=LAMBDA(…)“ oder ein #NAME?-Fehler in die Zelle geschrieben wird.


Warum passiert das?

  1. Uraltes Werkzeug trifft moderne Engine: Die Funktion Liste einfügen stammt historisch aus den allerersten Excel-Versionen der 1990er-Jahre. Sie wurde damals ausschließlich dafür gebaut, statische Zellbezüge (wie =$A$1:$B$10) oder feste Werte (Konstanten) aufzulisten.
  2. Die Natur von LAMBDA: Eine LAMBDA-Definition im Namens-Manager ist technisch gesehen kein benannter Bereich, sondern eine benutzerdefinierte Funktion
    (eine Programmierlogik).
  3. Automatisches Voranstellen von Anführungszeichen: Excel versucht beim Generieren der Liste krampfhaft, den Inhalt als Bezug zu interpretieren. Da ein LAMBDA-Ausdruck kein gültiger Tabellenbezug ist, „flüchtet“ Excel sich oft darin, die Formel als reinen Text zu erzwingen, indem es ein Gleichheitszeichen und Anführungszeichen davor setzt – was die Formel unbrauchbar macht.

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.