18:09 Uhr
Prüfung inwieweit ein Wert, bspw. eine Kostenstelle, innerhalb eines Intrevalls (Gruppe) liegt in Excel
Da das SAP System ein paar Tage offline sein wird, soll für ein Bericht anhand einer Kostenträger festgestellt werden, in welchen Bereich diese liegt. Grundsätzlich sollten Kostenträger soweit sprechend sein, dass man auch anhand der Nummer erkennen sollte, wo diese zuzuordnen ist, aber teilweise kann dieses (zum Beispiel bedingt durch Umgliederung von Kostenstellen oder bei Innenaufträgen) problematisch sein.
Daher wurden die einzelnen Gruppen in einer Exceltabelle exportiert, so dass hier nachgesehen werden kann, in welcher Gruppe ein Kostenträger liegt. Hierbei können sowohl Einzelwerte als auch Intervalle eine entsprechende Gruppe darstellen.
Um das Beispiel etwas zu vereinfachen (und nicht mit der Kostenstellenstruktur zu arbeiten) sind in diesen Beispiel statt einzelner Kostenstellen Notenpunkte und als Kostenstellengruppen die entsprechende Schulnote genommen worden.
Im Beispiel soll durch die Eingabe der Kostenstelle 11 (Punkte) direkt ausgegeben werden, dass dieses der Kostenstellengruppe (Schulnote) "Note 2 - Gut" entspricht.
Hierzu wurde die Tabelle wie folgt aufgebaut.

Hierbei wurden im Namensmanager die einzelnen Spaltenüberschriften einen entsprechenden Zellenbereich zugeordnet (KS_von umfasst die Zellen A2 bis A7).
Die Definition von Namensräumen durch Verwendung des Namensmanager ist im Artikel "Formulare gestalten in Excel" beschrieben.
Wenn es sich bei den Werten um Einzelwerte handeln würde (dieses ist im Fall der 0 Punkte der Fall) könnte nun wie im Artikel "Die Funktion SVERWEIS ohne #NV ausgeben" verfahren werden. Da es sich jedoch um Intervalle handelt und zum Beispiel die Kostenstelle 11 zwischen 10 und 12 in der Kostenstellengruppe "Note 2 - Gut" liegt muss hier eine andere Formel verwendet werden.
Funktion VERWEIS Syntaxversion VEKTORVERSION
Hierzu kann die die Vektorversion der Formel VERWEIS verwendet werden.
Der Syntax der Version lautet dabei wie folgt:
=VERWEIS(Suchkriterium;Suchvektor;[Ergebnisvektor])
Die Besonderheit dieser Funktion liegt darin, dass sie wenn die VERWEIS-Funktion keinen Wert finden kann, der mit dem jeweiligen Wert von Suchkriterium übereinstimmt, den größten Wert in Suchvektor verwendet, der kleiner oder gleich dem Wert von Suchkriterium ist.
Für unser Beispiel würde die Formel zur Ermittlung der Kostenstellengruppe wie folgt lauten:=WENN(ISTNV(VERWEIS(1;1/(B10>=KS_von)/(B10<=KS_bis);KS_Gruppe));"Nicht zugeordnet";VERWEIS(1;1/(B10>=KS_von)/(B10<=KS_bis);KS_Gruppe))
Hierbei funktioniert die Funktion wie folgt:
Überprüfe jede Zeile, ob Teil 2 und Teil 3 (getrennt durch /) des SuchVEKTORS! wahr sind und liefere dann die passende Zeile aus dem Zielvektor zurück.
Dieses funktioniert, da durch den Ausdruck (B10>=KS_von) ein WAHR ausgegeben wird, welches einer 1 entspricht, ebenso würde der Fall WAHR (und damit 1) eintreten, wenn B10<=KS_bis entspricht. Sofern kein Treffer erfolgt würde ein #NV ausgegeben werden.
Dieser Fall ist in der obigen Formel durch die Formel ISTNV und der Wertausgabe "nicht zugeordnet" abgefangen.
Das Ergebnis dieser Formel kann dann auch wiederum in einer SVERWEIS oder vergleichbare Funktion verwendet werden um zum Beispiel Berichtsempfänger einer Kostenstelle (Kostenstellengruppenverantwortliche) zu erhalten oder um ergänzende Funktionen zu erhalten. So könnte zum Beispiel bei der Schulnote 4 ausreichend noch ein Hinweis auf Nachprüfung möglich erfolgen.
Kleiner Hinweis am Rand:
In der Zelle C11 wurde die Formel um ein =WENN(B11="";""; ... ) ergänzt, da andernfalls kein Wert als 0 interpretiert werden würde und somit keine Angabe automatisch der Schulnote 6 - ungenügend entspricht.
Fazit
Zwar ist es schöner Kostenstellengruppen direkt im System auszuwerten, aber manchmal kann es hilfreich sein auch Berichte außerhalb des Systems nach den einzelnen Stammdaten auswerten zu können. Sehr hilfreich ist hier die im Artikel "Auflösen von Stammdatengruppen nach Einzelwerten - Einzelwerte zu Sets" beschriebene Methode zum Export von Stammdatengruppen aus SAP.
Weitere Verweisfunktionen in Excel
Insgesamt hat Excel hier drei Verweisfunktionen:
1. VERWEIS Funktion
Die in diesen Artikel dargestellte Formel/Funktion VERWEIS ist die Grundfunktion aller Verweisfunktionen und ist am dynamischsten in der Anwendung, wie hier ja auch zu sehen ist.
2. SVERWEIS Funktion
Die SVERWEIS Funktion dürfte eine der bekannteren Formeln im Controlling sein.
Dabei ist sie wie folgt aufgebaut.
=SVERWEIS(Suchkriterum;Matrix;Spaltenindex;Bereich_Verweis)
Anhand eines Suchkriteriums wird eine Matrix (Suchbereich) durchsucht und die entsprechende Spalte ausgegeben, die mit der ersten Spalte übereinstimmt. Ferner wird über Bereich_Verweis festgelegt ob eine exakte übereinstimmung (FALSCH) oder ungefähre Übereinstimmung (WAHR) gewünscht ist.
Im Artikel "SVERWEIS ohne NV und dynamische Größen für Datenbereiche" ist diese anhand eines Beispiels beschrieben.
3. WVERWEIS Funktion
Diese Funktion arbeitet vergleichbar zur SVERWEIS Funktion gibt aber nicht Spalten sondern gibt das Ergebnis aus einen Zeilenindex heraus.
Dabei ist sie wie folgt aufgebaut.
=WVERWEIS(Suchkriterum;Matrix;Zeilenindex;Bereich_Verweis)
Als Eselsbrücke könnte man sich merken, dass die SVERWEIS Funktion senkrecht nach der ersten Spalte die Daten durchsucht, wohingegen die WVERWEIS Funktion waagrecht in der ersten Zeile nach einer Übereinstimmung sucht und die folgenden Daten dann anhand des übergegeben Index ausgibt.
Ein Beispiel für die WVERWEIS Funktion ist im Artikel "Excel: WVERWEIS Funktion oder Jahresdaten umgruppieren für Mehrjahresvergleich" erläutert.
ein Angebot von Espresso Tutorials

unkelbach.link/et.books/
unkelbach.link/et.reportpainter/
unkelbach.link/et.migrationscockpit/
Diesen Artikel zitieren:
Unkelbach, Andreas: »Prüfung inwieweit ein Wert, bspw. eine Kostenstelle, innerhalb eines Intrevalls (Gruppe) liegt in Excel« in Andreas Unkelbach Blog (ISSN: 2701-6242) vom 22.6.2014, Online-Publikation: https://www.andreas-unkelbach.de/blog/?go=show&id=540 (Abgerufen am 13.2.2025)
Keine Kommentare - Permalink - Office
Artikel datenschutzfreundlich teilen
🌎 Facebook 🌎 Bluesky 🌎 LinkedIn