Archiv für den Monat: März 2021

Doofe intelligente Tabellen oder: der gute alte SVERWEIS

Schreibe eine Antwort

Wir berichten an dieser Stelle von praktischen Fragestellungen und deren Lösungen aus unserem Streifenkarten-Coaching.
Bei einem Coaching auf Streifenkartenbasis in einem deutschen Consulting-Unternehmen entstand eine recht aktuelle Fragestellung: unterjährig wurde der Mehrwertsteuersatz geändert – haben wir ja alle dieses live Jahr miterlebt! Nun sollte die Berechnung von Bruttobeträgen in einer intelligenten Tabelle ab einem bestimmten Datum eben mit der neuen Mehrwertsteuer durchgeführt werden.

Fragestellung:

In einer Intelligenten Tabelle soll ab einer bestimmten Zeile eine abweichende Formel eingesetzt. Konkret: im letzten Jahr änderte sich zweimal die Mehrwertsteuer.

Problem

Intelligente Tabellen sind gar nicht mehr so clever, wenn innerhalb einer Spalte die Formel wechseln soll. Dafür sind sie nicht gemacht!

Nun ließe sich zwar mit einer WENN-Funktion dieser Wechsel abbilden, aber wer weiß, ob nicht die Mehrwertsteuer in absehbarer Zeit wieder geändert wird? Ich würde dringendst abraten, das dann wieder von Hand in einer Formel zu pflegen…

Schlechtes Beispiel:
=WENN(Monat(Rechnungsdatum)<7;1,19*NettoBetrag;1,16*Nettobetrag)

Trick

Geben wir die Mehrwertsteuersätze einfach in eine kleine, clevere Tabelle, der wir natürlich sofort einen aussagefähigen Namen gegeben haben: tblMwSt. Die Mehrwertsteuersätze gelten ab dem jeweils angegebenen Tag.

Mehrwertsteuersätze in Deutschland.
(BTW: die Tabelle ist korrekt!)

Und dann nutzen wir unseren guten alten Freund, den SVERWEIS, um die korrekten Werte zuzuordnen.

Formel für den Bruttopreis:
=[@[Preis Netto]]*(1+SVERWEIS([@Rechnungsdatum];tblMwSt;2;-1))

Hierbei ist der Parameter Bereich_Verweis auf -1 zu setzen, da ja nicht für jeden Monat ein MwSt.-Satz abgegeben werden soll, sondern nur, ab wann ein MwSt.-Satz gilt (daher auch der komische Wert 01.01.1901, das das erste Datum der Microsoft-Welt darstellt). Kommen Sie bitte auch hier niemals auf die Idee, das mit geschachtelten WENN-Formeln zu versuchen!


Dies und Ähnliches bearbeiten wir im Seminar Excel im Finanzwesen und Controlling.

Wenn Ihnen dieser Beitrag gefallen hat, dann teilen  Sie ihn gerne. Falls Sie Anmerkungen haben, schreiben Sie bitte einen Kommentar, oder senden Sie mir eine Mail an info@prt.de.

Excel spricht mit uns!

Schreibe eine Antwort

Vor ca. 15 Jahren war ein junger Mann Teilnehmer eines Inhouse-Seminars, der leider eine geringe Sehfähigkeit hatte Er musste sich seinen Monitor auf die bestmögliche Auflösung einstellen und klebte förmlich mit der Nase am Display, um erkennen zu können, was er in Excel gerade tat. Schade, dass Excel damals noch nicht sprechen konnte. Seit Excel 2016 kann man sich das mit wenigen Klicks einstellen.

Weiterlesen →

Durch das Datum gesteuert Zahlen konsolidieren

Schreibe eine Antwort

Dank Power Query kann man nun Daten automatisch aus verschiedenen Datenquellen „einsammeln“, zu einem Modell zusammenfügen, das Ergebnis als Tabelle mehr oder weniger stark verdichtet und aggregiert in ein Excel-Modell laden. Handelt es sich dabei um ein Dashboard, müssen u.U. die Daten als Basis für ein Diagramm verdichtet werden. In diesem Beitrag möchte ich zeigen, wie Sie das mit der Funktion SUMMEWENNS erreichen können. Da Power Query automatisch aktualisierte Daten aus den Datenquellen „liefert“, soll diese Berechnung der Funktion SUMMEWENNS außerdem vom Datum gesteuert werden. Dazu zeige ich die Funktion HEUTE(), ISOKALENDERWOCHE und TEXT().

Weiterlesen →

Excel und Künstliche Intelligenz? – Wirklich?

Schreibe eine Antwort

Ohne große Ankündigung durch Microsoft werden seit 2018 Elemente von Künstlicher Intelligenz in Excel integriert. Sie sind überrascht? Sie glauben es nicht?
Das sollten Sie aber, denn mit einigen Funktionalitäten hat das Thema KI nahezu unbemerkt Einzug gehalten. In diesem Beitrag möchte ich Ihnen einen Überblick geben, um dann im Rahmen einer kleinen Serie (Excel und KI) mit weiteren Beiträgen diese Funktionalitäten vorzustellen. Teilweise haben wir diese Funktionalitäten in den letzten Jahren bereits vorgestellt, allerdings nicht mit dem Hinweis auf KI. KI-Elemente finden sich derzeit hauptsächlich in einer APP und in dynamischen Bereichen, d.h. sie beschränkt sich derzeit auf Mustererkennung und dem Übertragen von Formeln und Funktionen. Excel bietet so Controllern immer mehr Automatismen an, ohne dass etwas programmiert werden muss.

Weiterlesen →

Excel verbessert die Bedingte Formatierung

Schreibe eine Antwort

Die Bedingte Formatierung ist für uns Controller eine sehr nützliche Funktionalität. Können wir doch damit Abweichungen und andere wichtige Informationen in den Zahlen visualisieren.
Wenn Sie, so wie wir, mit Excel365 arbeiten, werden sie immer wieder von neuen Funktionalitäten überrascht, die sich mit dem letzten Update „eingeschlichen“ haben. Heute möchte ich auf eine Änderung bei der Bedingten Formatierung hinweisen:

  • Das Dialogfenster lässt sich nun beliebig in der Größe verändern und
  • es gibt nun eine Schaltfläche <Doppelte Regeln>, mit der Sie eine ausgewählte Bedingte Formatierungsregel duplizieren können.

Animation, in der die Größe des Dialogfelds verändert wird (Quelle: Microsoft Tech Community)

Wenn Sie Regeln für eine Bedingte Formatierung kopieren möchten, wählen Sie einfach die Regel aus, und klicken Sie auf Schaltfläche <Doppelte Regeln> um die Kopie zu erstellen. So können Sie nun eine Regel erstellen, dann duplizieren und die erforderlichen Anpassungen an der neuen Regel vornehmen.

Screenshot mit der neuen Schaltfläche <Doppelte Regel >im Dialogfeld „Regeln verwalten“

__________________________________________________________________________________________

Wenn Ihnen dieser Beitrag gefallen hat, dann teilen  Sie ihn gerne. Falls Sie Anmerkungen haben, schreiben Sie bitte einen Kommentar, oder senden Sie mir eine Mail an info@prt.de.