Seit wir 1993 das erste Mal unser Seminar Excel für Controller durchgeführt haben, werden wir immer wieder gefragt, welches die wichtigsten Excel-Funktion(alität)en für Controller sind. Das hängt natürlich immer davon ab, welchen Aufgaben im Controlling und Finanzwesen man mit Excel nachkommen möchte. In diesem BLOG gibt es bereits einen Beitrag zu den wichtigsten Features. Dennoch wollen wir die uns permanent gestellte Frage hier aus einer anderen Perspektive beantworten.
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…
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-1zu 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!
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.
Immer wieder werde ich auf Publikationen, Dokumente oder Posts in den Sozialen Medien aufmerksam, in denen Nachteile von Excel aufgezählt werden. Interessanterweise zählt der jeweilige Autor nicht nur die Nachteile von Excel auf, sondern stellt meist auch noch eine Alternative zu Excel vor. Es handelt sich dann meist um ein Tool einer Softwareschmiede, auf dessen payroll der Autor auch zu finden ist. Seit 1989 arbeite ich mit Excel und kenne seine Stärken und weiß, wie man seine “Schwächen” umgehen kann. Kürzlich habe ich wieder ein Whitepaper gefunden. Hier eine Serie von Gegendarstellungen zu den darin aufgestellten Behauptungen.
Behauptung Nr.2
Gegenrede:
Diese Behauptung ist nicht haltbar. Wenn man ein Excel-Modell nach bestimmten Prinzipien aufbaut (z.B. PRT-Modellierung), dann ist das Excel-Modell geteilt in einen Import-Bereich, einen Berechnungs-Bereich und in einen Ausgabe-Bereich. Im Berechnungsbereich und Importbereich werden “intelligente Tabellen” eingesetzt, damit werden alle Formeln/Funktionen und Bereiche automatisch angepasst. VLOOKUP/SVERWEIS wird entweder durch XVERWEIS (Excel365), INDEX/VERGELICH oder durch Power Query ersetzt. Die Sheets des Berechnungsbereich und des Ausgabe-Bereich werden vor dem Überschreiben geschützt. Setzt man zusätzlich noch Schaltflächen ein, so entfällt auch eine manuelle Dateneingabe von Steuerungsparametern oder anderen Daten.
——————————————————————————————————————————-
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.
Mit einem Update Ende April 2020 haben sich tiefgreifende Änderungen in Excel 365 ergeben. In diesem Beitrag möchte ich die neue Funktion XVERGLEICH vorstellen, die leider nicht abwärtskompatibel ist! Vorstellen möchte ich diese neue Funktion an einem Beispiel aus dem Personal-Controlling, dass wir seit 1993 in unserem Seminar EXCEL im Controlling und Finanzwesen verwenden.
Wer mit Matrix-Funktionen wie SVERWEIS(), VERGLEICH() arbeitet, der erhält gelegentlich die Fehlermeldung #NV. Das heißt etwa so viel wie «Nicht Vorhanden»! In diesem Beitrag möchte ich zeigen, wie dieses #NV entsteht, wie man es vermeidet, aber noch viel wichtiger:
Wie nützlich diese Fehler-Meldung bei Diagrammen sein kann!
Ganz nebenbei kläre ich die wahre Bedeutung des Arguments “Bereich_Verweis” in der Funktion SVERWEIS() auf und warum Sie dort WAHR (=1) oder FALSCH(=0). Diese Bedeutung wird seit Jahrzehnten nicht vollständig erläutert!
Mit einem Update Ende April 2020 haben sich tiefgreifende Änderungen in Excel 365 ergeben. In diesem Beitrag möchte ich die neue Funktion XVERWEIS vorstellen, die SVERWEIS und WVERWEIS obsolet macht. Je nachdem, könnte man auch auf die von uns präferierte Kombination aus INDEX()/VERGLEICH() zukünftig verzichten. Die Funktion ist leider nicht abwärtskompatibel! Vorstellen möchte ich diese neue Funktion an einem Beispiel, dass wir seit 1993 in unserem Seminar EXCEL im Controlling und Finanzwesen verwenden.
“Welche Formeln/Funktionen lassen sich gut verschachteln und welche Regeln müssen dafür eingehalten oder welche Operatoren müssen dafür verwendet werden ?”
In unserer XING-Gruppe Controlling meets Excel & Co. gab es im Mai 2019 diese Frage zu Excel-Funktionen. Das haben wir zum Anlass genommen, diverse Beiträge zum Thema Funktionen, die seit langer Zeit schon fast fertig “in der Schublade” lagen, nun fertig zu stellen und hier nach und nach zu publizieren. In diesem Beitrag geht es um die Angabe von Argumenten, wenn Funktionen “verschachtelt” werden.
Wenn man mit Excel-Funktionen oder -Formeln arbeitet, werden stets Operatoren verwendet. Ein Operator ist eine mathematische Vorschrift, die Berechnungen regelt. Die Operatoren werden durch ein spezielles, mathematisches Symbol dargestellt. Mittels Operatoren wird die Art der Berechnung festgelegt, die mit den Elementen einer Formel durchgeführt werden soll. Excel unterscheidet vier Arten von Operatoren:
Excel ist bekanntlich vielseitig einsetzbar. In einem Beispiel zeigen wir verschiedene Techniken, um hier Mitarbeiter-Produktivität in einem produzierenden Unternehmen zu berechnen, bzw. in einer fertigungsnahen Kostenstelle (Werkstatt). Neben einer “intelligenten Tabelle”, kommen Namen für Zellen und Zellbereiche zum Einsatz sowie diverse Funktionen. Hier geht´s zur Beispieldatei.
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.
Mit Matrix-Funktionen kann man Elemente aus einer Datenliste/Tabelle heraussuchen, bekommt aber immer nur ein Ergebnis. Denn Matrix-Funktionen sind nicht für das Erstellen von Abfragen (im Sinne von Datenbanken) ausgelegt. Wenn man verschiedene Matrix-Funktionen kombiniert, erhält man die gewünschte Mehrfach-Lösung. Allerdings ist diese Lösung mit den Funktionen SVERWEIS; VERGLEICH; BEREICH.VERSCHIEBEN; WENNFEHLER; ANZAHL sehr aufwändig.
