Schlagwort: Matrixfunktionen

Hoffentlich merkt das Finanzamt nicht dass ich meinen Kaffee schwarz trinke.

Merkwürdige Formeln

Eine sehr schöne Bemerkung von Christoph Steiner. Danke fürs genaue Hinschauen. Da ist mir doch glatt etwas entgangen.

Ich greife auf eine Matrixfunktion zu:

=Mitarbeiterliste!A50#

Dabei stören die 0-Werte, die aufgrund der leeren Zellen entstehen. Hier im Titel. Mein Gedanke war sie mit der Funktion WECHSELN zu ersetzen:

Und richtig: es verschwinden zwar die Nullen in der Spalte „Titel“, allerdings auch die Nullen in der Personalnummer und Postleitzahl und in den Rechnungsbeträgen. Und: da WECHSELN einen Text liefert, werden die Datumsangaben als Zahl angezeigt.

Danke für den Hinweis, Chris!

Eine mögliche Lösung:

Man ersetzt die Nullen nur in der dritten Spalte. Beispielsweise so:

=HSTAPELN(SPALTENWAHL(Mitarbeiterliste!A50#;1;2);
WECHSELN(SPALTENWAHL(Mitarbeiterliste!A50#;3);"0";"");
SPALTENWAHL(Mitarbeiterliste!A50#;4;5;6;7;8;9;10;11;12;13))

Das Ergebnis sieht gut aus:

Man kann die Formel natürlich verkürzen:

=HSTAPELN(SPALTENWAHL(Mitarbeiterliste!A50#;1;2);
WECHSELN(SPALTENWAHL(Mitarbeiterliste!A50#;3);"0";"");
SPALTENWAHL(Mitarbeiterliste!A50#;SEQUENZ(10;1;4;1)))

Oder so:

=LET(B;Mitarbeiterliste!A50#;
HSTAPELN(SPALTENWAHL(B;1;2);
WECHSELN(SPALTENWAHL(B;3);"0";"");
SPALTENWAHL(B;SEQUENZ(10;1;4;1))))
2 Kommentare ,

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

Fehlermeldungen

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:

Kommentar hinterlassen , , , ,

Lass deinen Drucker nie spüren, dass du in letzter Minute ausdrucken möchtest. Sie können deine Angst spüren.

Power Query

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:

  1. Die Werte stehen in einer intelligenten Tabelle:

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.

Kommentar hinterlassen , , ,

Ich habe gerade versucht, den Mähroboter mit einem Grasbüschel vom Nachbarn an den Gartenzaun zu locken. War wohl schon satt.

Power Query, Tabellen (Intelligente Tabellen)

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 …

Kommentar hinterlassen , , , , , , , ,

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

Merkwürdige Formeln, Zahlenformate

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.

Kommentar hinterlassen ,

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

Merkwürdige Formeln, Tabellen (Intelligente Tabellen)

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

2 Kommentare , ,

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.

Excel rechnet gar nicht, Merkwürdige Formeln, Tabellen (Intelligente Tabellen)

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 🙂

Kommentar hinterlassen , , ,

Feiertage wären klasse

Merkwürdige Formeln

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

Die Feiertagsliste

Der Kalender

Der Kalender

Ich habe zirka eine halbe Stunde benötigt, damit die Feiertage angezeigt werden – eine hübsche kleine Fingerübung:

Das Ergebnis

Das Ergebnis

Wer knobelt mit? Den ersten Kalender könnt Ihr unter Kalender herunterladen.

Viel Spaß im neuen Jahr mit Excel.

Rene

2 Kommentare , , ,