Eine Liebeserklärung an MS Excel
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:
Letzte Woche hat Martin Weiß (der tabellenexperte.de) auf unserem Excelstammtisch einige Tipps zu PowerQuery gegeben – damit Excel nicht mehr so nervt:
Wir haben drei Möglichkeiten gefunden, um Werte, die sich in Excel befinden, als Parameter in PowerQuery zu verwenden:
2. Die Zellen, in denen sich die Werte befinden, werden mit einem Namen versehen:
3. Die Werte sind das Ergebnis von Berechnungen von Array-Funktionen, beispielsweise FILTER oder SEQUENZ:
Danke, Martin, für diesen wertvollen Tipp.
Zuerst habe ich mich geärgert. In PowerQuery gab es früher ein Symbol „Von Tabelle“. Daraus wurde in der Gruppe „Daten abrufen und transformieren“ das Symbol „Aus Tabelle/Bereich“.
Seit ein paar Tagen heißt es nun „Vom Blatt“
Muss das sein? Ständiges Umbenennen?
Frank Arentd-Theilen hat mich auf den Grund hingewiesen (danke für den Hinweis):
Ja – denn nun kann man Listen in Excel, die mit den neuen Arrayfunktionen erstellt wurden, beispielsweise mit FILTER, SORTIEREN und SORTIERENNACH in PowerQuery importieren:
Das funktioniert auch mit der Funktion SEQUENZ:
Okay – zugegeben – leider nicht immer. Wenn diese Matrixfunktionen innerhalb einer Liste stehen, wie beispielsweise hier in diesem Monatskalender:
dann wandelt PowerQuery die gesamte Liste in eine (intelligente) Tabelle um und – scheitert! Klar: Tabellen dürfen keine Matrixfunktionen verwenden …
Christian ist irritiert. Zwar übertragen und übernehmen die Arrayfunktionen (SORTIEREN, FILTERN, SEQUENZ, …) nicht die Formatierungen:
Aber wenn man mit dem Gleichheitszeichen auf einen Bereich zugreift, wird in der ersten Zelle das Zahlenformat übernommen, in den übrigen nicht:
Erklärbar – dennoch: irritierend.
Hallo Rene,
Ich hätte da nochmal eine Frage zu den Matrixfunktionen. Wenn ich die Rückgabematrizen in einen Bereichsnamen packe, funktioniert der auf dem Tabellenblatt wunderbar. Wenn ich allerdings diese Matrix über den Bereichsnamen in einer Datenüberprüfung als Liste ausgeben möchte, kann ich ihn zwar über F3 ansprechen, erhalte dann aber eine Fehlermeldung.
Gruß
Hannes
Hallo Hannes,
du musst den Namen im INDIREKT in einen Bezug umwandeln. Meinst du das?
Liebe Grüße :: Rene
Hallo Rene,
ja genau, da sind einige Haken drin.
Die Liste in der Datenüberprüfung übernimmt offensichtlich nur Werte aus Bereichen die auf einem Tabellenblatt liegen und keine Rückgabewerte aus Funktionen.
Aber wenn ich innerhalb einer „formatierten“ Tabelle einen benannten Bereich anlege passt sich dieser Bereich auf die Tabellengröße an und die Werte können auch in der Datenüberprüfung dynamisch verwendet werden.
Wenn ich über die Sequenz-Funktion Werte ermittle und die in einen Bereichsnamen packe, werden die Werte innerhalb eines Tabellenblattes zurückgegeben, innerhalb der Datenüberprüfung jedoch nicht als Liste übernommen.
Die Problematik ist wahrscheinlich, dass die Datenüberprüfung ausschließlich Werte aus dem Tabellenblatt ausliest, daher auch die Bezugsherstellung mit der Indirekt-Funktion.
Keine Ahnung, ob man das irgendwie austricksen kann.
Gruß
Hannes
PS: hier mein „Versuchsaufbau“ zu dem ich ein bisschen was erzählen könnte. Kannst ja vorab schon mal einen Blick drauf werfen, ob das interessant sein könnte
Hi Rene,
sag mal: kann man in intelligenten Tabellen keine Matrixfunktionen verwenden? Hintergrund: ich möchte gerne mit der Funktion SORTIEREN oder SORTIERNNACH eine Liste sortieren und diese sortierte Liste als Basis für eine Tabelle verwenden. Also so:
Und dann passiert:
Hallo Johannes,
eine kleine Überlegung: (Intelligente) Tabellen „denken“ nur zeilenweise oder in Bezug auf eine ganze Spalte. Also:
=[@Umsatz]*19%
oder:
=SUMME(Tabelle1[Umsatz])
analog:
=TEILERGEBNIS(109;[Umsatz])
DAS kollidiert mit einer Arrayfunktion (EINDEUTIG, SORTIEREN, SORTIENNACH, FILTER), die dynamisch einen Bereich definiert oder ZUFALLSMATRIX und SEQUENZ, in die die Größe eines Bereichs eingetragen wird.
Moin Rene,
Danke Dir für die ergänzenden Infos 🙂 Ich habe es jetzt so gelöst … So hat sich auch der Vorteil ergeben, dass es etwas übersichtlicher ist 🙂
Vor ein paar Tagen erreichte mich folgende Anfrage:
Sehr geehrte Damen und Herren,
zu dem in Betreff genannten Thema haben wir noch eine Frage: Die Videoanleitung zum Erstellen eines Internationalen Kalenders konnten wir gut nutzen. In dieser Anleitung wird u.a. geschildert, wie man Feiertage durch eine bedingte Formatierung farblich hervorhebt. Ganz schick wäre es noch, wenn zu diesem farblich markierten Feiertage auch automatisch der Feiertagsname mit angezeigt werden könnte. Ein entsprechendes Tabellenblatt mit diesen Informationen wurde im Verlaufe der Anleitung angelegt. Bei festen Feiertagen wie Neujahr. 1. Mai etc, könnte man dies händisch lösen, doch bei variablen Feiertagen wie Ostern und Pfingsten etc. wäre es wünschenswert, wenn diese gleich automatisch mit angezeigt werden. Leider wurde in dieser Videoanleitung nicht darauf eingegangen. Mit welcher Funktion kann der Feiertagsname automatisch angezeigt werden? Danke schon vorab für Ihre Hilfe.
Mit freundlichen Grüßen / with best regards
#####
Zur Info: Ich habe einen Kalender erstellt, der – nach Änderung des Jahres die Feiertage farblich kennzeichnet. Das klappt mit der bedingten Formatierung und der Funktion ZÄHLENWENN gut und einfach. Die Feiertage (hier: die bayrischen) habe ich auf ein zweites Tabellenblatt ausgelagert.
Die Feiertagsliste
Der Kalender
Ich habe zirka eine halbe Stunde benötigt, damit die Feiertage angezeigt werden – eine hübsche kleine Fingerübung:
Das Ergebnis
Wer knobelt mit? Den ersten Kalender könnt Ihr unter Kalender herunterladen.
Viel Spaß im neuen Jahr mit Excel.
Rene