Lösungsansatz für Berechnung Punktesystem?
Leider bin ich überhaupt kein Excel-Experte, weshalb ich mich an Sie wende! Anforderung: Für eine monatliche Statistik soll ich ein Punktesystem (Kennzahl) entwickeln.
Ich habe 3 Hauptkategorien, die sich wiederum in 3 - 9 unterschiedlichen Kategorien unterteilen. Diese Hauptkategorien werden wiederum in einer Überkategorie bewertet.
Eine einheitliche Berechnung ist schwierig, weil es unterschiedliche Voraussetzungen bei der Berechnung der Kategorien gibt, die sich dann "hochrechnen" sollen.
Habe bereits unterschiedlichste Lösungsansätze (je nachstehender Kategorien) versucht (min, max, wenn, % etc. - Beispiel einer der Berechnungen: =WENN(B7<86,9999;0;WENN(B7>97,0001;100;(100-C2/(B2-B3)*(B2-B7)))) ), bin mir aber nicht sicher, ob ich hier wirklich am richtigen Weg bin, um die Berechnungen zu machen.
Beispiel:
Überkategorie kann 1.000 Punkte erreichen;
Der Schlüssel zur Aufteilung hier: Hauptkategorie A - max. 600 Punkte, Hauptkategorie B - max. 200 Punkte ,Hauptkategorie C - max. 200 Punkte
Diese setzen sich wiederum aus unterschiedlichen Kategorien zusammen: Die Werte der Kategorien werden ausgewiesen, daneben sollen die Punkte stehen (ich habe hier u.a. % in Punkte zweckentfremdet). Pro Kategorien gibt es Ober- und Untergrenzen, im Delta dazwischen sollen die berechneten Punktezahlen ausgewiesen werden.
Wie kann ich hier am einfachsten die Punkte berechnen?
Hauptkategorie A - kann höchstens 600 Punkte erreichen, über einen Schlüssel wurde aufgeteilt, wie viel Punkte pro Kategorie höchstens erreicht werden können.
Kategorie A ............ Wert 290 ................. wenn Wert 250 und weniger dann 100 Punkte, wenn Wert 338 und mehr dann 25 Punkte, alles was drüber oder drunter liegt behält den Wert 100 Punkte bzw. 25 Punkte
Kategorie B ............ Wert 99,4 ............ wenn 96,9 und mehr dann 100 Punkte, wenn Wert 87 und weniger dann 0 Punkte; hier ebenfalls alles was drüber oder drunter liegt behält den Wert 100 Punkte bzw. 0 Punkte
Kategorie C .......... Wert 64 .............. wenn 53 oder weniger dann höchstens 75 Punkte, wenn Wert 71 oder mehr, dann höchstens 25 Punkte (mehr / weniger gibts nicht); alles was drüber oder drunter liegt behält den Wert 75 Punkte bzw. 25 Punkte
Ähnlich geht es mit den anderen Kategorien weiter!
Hat jemand eine Idee, wie dies übersichtlich und ohne allzu große komplizierte xls-Formeln zu bewerkstelligen wäre?
Scheitere hauptsächlich daran, dass ich die Berechnung mit Ober-/Untergrenze machen muss bzw. dass es bei den auszugebenden Werten ebenfalls eine Grenze gesetzt ist.
Vielen lieben Dank schon im Voraus für Eure Hilfe und Mühe und Unterstützung!
2 Antworten
Ganz blicke ich nicht durch - wenn es darum geht, aus einer gegebenen Punktezahl das Ergebnis (Kennzahl) zu ermitteln, ist es am einfachsten, wenn man die Punktevergabe als Tabelle anlegt und dann mit dem Suchbegriff (dem Ist-Wert der Punkte) in der Tabelle das Ergebnis ausliefert. Deutlich einfacher als die ganze Logik in einer Formel zu packen und auch für spätere Anpassungen einfacher zu handhaben.
Wäre das eine Option?
Zur Verdeutlichung das Bild (das geht auch mit komplizierten Fällen, dann eben mit anderer Formel für den Zugriff)
Ich blicke auch kaum durch, aber ich erkläre Dir einfach mal an diesem Beispiel:
Kategorie C .......... Wert 64 .............. wenn 53 oder weniger dann
höchstens 75 Punkte, wenn Wert 71 oder mehr, dann höchstens 25 Punkte
(mehr / weniger gibts nicht); alles was drüber oder drunter liegt behält
den Wert 75 Punkte bzw. 25 Punkte
wie ich das interpretiere! und wie ich das dann lösen würde.
Obergrenze 71=25 Punkte, Untergrenze 53=25 Punkte. die 64 ist ja genau die Mitte, also hätte die 50 Punkte!?.
Folgende Tabelle habe ich gebastelt --> Siehe Screenshot.
Die Formel zur Berechnung der Punkte in B5 lautet:
=RUNDEN(C2+(A2-C2)/((D2-MIN(D2;A5))+(MAX(B2;A5)-B2))*C5;0)
Das lässt sich evtl. noch optimieren/ vereinfachen, liefert aber die Ergebnisse, die ICH mir vorstelle.
Bau das mal nach, schau es Dir in Ruhe an und äußere Dich möglichst ausführlich dazu.
Gern geschehen.😉
Wenn ihr weiter seid und Hilfe braucht, stelle am besten nochmal eine neue Frage (kannst ja dann diesen Thread dort verlinken).
Sonst wird es zu unübersichtlich.
Oubyi: DH! ob das nun so gemeint war oder anders)
@ Frager: Jedenfalls solltest Du für jede Kategorie erst mal (mindestens) eine ganze Spalte anlegen, ab zB Zeile 5, in Zeile 4 das Ergebnis schreiben, in Zeile3 den Maximalwert und dann in Zeile 2 den erreichten Wert (=Min(MaxWert;erreichter Wert). Vorne fügst Du eine Spalte ein, in der Du die gültigen Werte Zeile2 Summierst, evtl nochmals =Max(600;Summe). Zeile 1 für Überschriften.
Für jeden Kandidaten ein extra Blatt, geht das?
wenn alle Kandidaten auf ein Blatt müssen und Du kein extra Zusammenfass-Blatt anlegen kannst/sollst, kannst Du die Einzelspalten-Einträge hinterher soweit nach unten verschieben, dass Du sie nach Erstellung in eine zusammenschieben kannst.
Du musst nur aufpassen bei Formeln wie Summe etc, welche Bereiche verwenden anstatt Einzelzellen (kommt bei Oubyis Formel aber nicht vor), dass Du sie als Einzelkomponenten adressierst, Summe würde alle Zwischenzellen mitsummieren. (also so =Summe(A1;A2;A11;A12;...) v. a. aber Min/Max(Zelle1;Zelle2;...))
Du kannst dann noch für die Kopfzeilen jeder Einzelkategorie einen Bereichsnamen vergeben (ganze Zeile markieren am Zeilenkopf, in der grauen Leiste wo die Zeilennummern stehen).
Wenn Du dann noch die entscheidenden zusammenfassenden Zeilen als Titel fixierst (obere Zeilen markieren, Menü Ansicht, Fenster einfrieren, Fenster einfrieren), kannst Du perfekt im Arbeitsblatt navigieren zum Eintragen oder nachschauen!
Und wenn Du noch Zeilen-Benennungen(Subkategorie) und Datum der Berechnung brauchst, diese links in zwei neue Spalten einfügen und vor dem Titel fixieren zB C4 markieren, danach bleiben A:B und 1:3 als Titel sichtbar.
Tipp:Das heutige Datum kann übrigens kurz mit Strg+Punkt (gleichzeitig) fix eingetragen werden. -1+"Strg+." (Gänsefüßchen eintippen!) fixiert gestern, -7+"Strg+.".den Tag heute vor einer Woche.
Danke für die rasche Antwort! - Lösungsansatz hatten wir schon ähnlich, aber ich glaube, mit Deiner Lösung bin ich dem Ganzen jetzt wieder ein Stück näher gekommen.
(Es ist leider alles ziemlich kompliziert und Mathe/Formeln waren schon in der Schule nicht unbedingt mein Lieblingsfach! *grins*)
Das Problem ist, dass ich quasi monatlich Statistiken aus anderen Abteilungen zu Verfügung gestellt bekomme, wo ich dann den IST-Wert hernehme (pro Kategorie) und diese dann weiterverarbeiten soll (mit FC-, Trend-, Vergleich mit Vormonat selben Jahr, Vergleich Monat Vorjahr etc.). Wir haben nun insgesamt 4 Listen mit je 8 - 4 Tabellenblättern, wo jede einzelne Kategorie berechnet wird. Bin ganz stolz, dass ich sogar die Darstellung mit den farbigen Trendpfeilen hinbekommen habe. Sah wirklich schon sehr gut (fast professionell aus! ggg)
Diese sind natürlich auch unterschiedlich zu bewerten - mal ist es besser, wenn der IST-Wert höher ist als der FC-Wert, mal wenn er unter diesem liegt; mal sind des Absolutwerte, mal sind es Prozent. Ein schönes kunterbuntes "Kuddlmudl" an Zahlen also ...
Die Berechnungen haben wir noch ganz gut hinbekommen. Auch die Berechnungen als %-Wert war nicht ganz so kompliziert, das ging noch ganz gut. Excel bietet hier ja bereits wirklich gute vordefinierte Formeln.
Schwierig wurde es erst, als Abteilungsleiter mit diesem Punktesystem daher kam, wo wir aber nicht mit 0 - 100 rechnen sondern eben mit diesem komplizierten Schlüssel. - Da habe ich - nachdem ich tagelang bereits mit den anderen Kategorien verbracht habe - die Flinte ins Korn geworfen.
Die 4 Tabellen werden dann in einer anderen (extra) Tabelle zusammengeführt, wo wir nun eben diese verflixte Berechnung durchführen wollen.
Die Spalten - wie von Iamiam - hatten wir bereits definiert. Werde die Formel nun für die einzelnen Kategorien noch ein wenig anpassen und dann nochmals einen Versuch starten. Die ersten Test sehen vielversprechend aus!
Nochmals vielen Dank!