Office: leere Zellen suchen, dann erst rechnen

Hallo zusammen,

ich habe ein kleines Auswertungstool für 6 Lieferanten in Excel erstellt.

Formel: =WENN(MIN(G$25;J$25;M$25;P$25;S$25;V$25)=G$25;"Günstigster";1-(MIN(G$25;J$25;M$25;P$25;S$25;V$25)/G$25))

Das funktioniert wunderbar, solange alle Lieferanten angeboten haben. Wenn einer allerdings nicht anbietet und die Lieferantenspalte leer bleibt, kommt zwangsweise bei allen anderen 4 Lieferanten „100% zu teuer“.

Man müsste Excel sagen, dass er zuerst nach leeren Zellen suchen muss und dann erst die oben genannte Formel zum Einsatz kommt.

Vielleicht habe ich ja auch einen ganz falschen Ansatz gewählt?

Wer kann helfen?

Vielen Dank im Voraus
dutti

 

Hallo dutti,

1) Du hast wahrscheinlich bei dem Lieferanten, der nicht angeboten hat, einen Preis 0 eingegeben; wenn Du diese Lieferantenzelle leer lässt, dann übergeht auch Excel in MIN(...) diese Position.

2) Darüberhinaus bin ich mit Deiner Formel nicht ganz einverstanden: Du kannst auch selber folgenden Test machen, indem Du beim günstigsten Lieferanten 100 eingibst, beim 2.-günstigsten 110, beim 3.-günstigsten 120, usw.
Als Ergebnis würde man sich erwarten, dass dann der 2.-günstigste um 10%, der 3.-günstigste um 20% zu teuer ist usw. - Deine Formel liefert jedoch 9,09%, 16,67% usw.
Die Formel müsste nämlich davon ausgehen, dass der Günstigste mit 100% bewertet wird; und die Formel soll die Frage beantworten, um wieviel % ist jeder der anderen Lieferanten teurer im Vergleich zum günstigsten 100%-Lieferant.
Also lautet die Formel:
=WENN(MIN(G$25;J$25;M$25;P$25;S$25;V$25)=G$25;"Günstigster";G$25/MIN(G$25;J$25;M$25;P$25;S$25;V$25)-1)
Allerdings werden dann Lieferanten ohne Anbot mit -100% angezeigt. Wenn Du das nicht haben willst, zB. wenn Du eine Leerzellenanzeige bevorzugst, genügt es, die Prozentzellen mit dem benutzerdef. Format 0,00%;; zu formatieren oder auch 0,00%;"Kein Anbot"

3) Auf diese Weise kannst Du sogar die Formel erheblich vereinfachen:
=G$25/MIN(G$25;J$25;M$25;P$25;S$25;V$25)-1
Als benutzerdef. Format gibst Du in die Formelzellen ein: 0,00%;"Kein Anbot";"Günstigster"

 

Hallo Exl121150,

zuerst einmal danke für Deine detaillierte Antwort!

zu 1. ich habe die Zellen als Währung formatiert, das möchte ich eigentlich auch beibehalten. Als Test habe ich diese Formatierung gelöscht und dann hats auch super geklappt. Heißt das, ich muss bei jeder Zelle die Formatierung herausnehmen?

zu 2. Ich gehe davon aus, dass der Lieferant seinen Preis als 100% ansieht (er kennt den günstigsten Preis ja nicht) und kann sich dann mit der Abweichung den günstigsten Preis selber ausrechnen.

zu 3. kannst Du mir bitte dein benutzerdefiniertes Format erklären und wie sie funktioniert:
0,00%;"Kein Anbot";"Günstigster"

Beste Grüße
dutti

 

Hallo,

ad 1) Die Formatierung kannst Du ohnehin so lassen (als Währung), nur keinen Nuller (0) darfst Du eingeben, sondern die Zelle leer (=keinen Wert; 0 ist ein Wert!!) lassen. Eine Leerzelle erhältst Du garantiert, wenn Du die Taste Entf drückst.

ad 2) Wenn Du es so haben möchtest - das überlasse ich Dir - nur wird dann die Vereinfachung unter Punkt 3) nicht ganz funktionieren ("Kein Anbot" wegen #DIV/0). Aber da Du Excel2007 hast, könntest Du den Division-durch-0-Fehler einfach mit folgender Formel abfangen (die Formel liefert im Fehlerfall den 2.Parameter -1 zurück), sodass auch die Formatierung unter Punkt 3) funktioniert:
=WENNFEHLER(1-MIN($G$25;$J$25;$M$25;$P$25;$S$25;$V$25)/G$25;-1)

ad 3) Bei einem benutzerdef. Format kann man 1-3 Zahlenintervalle ansprechen. Bei meinem benutzerdef. Format werden 3 Bereiche verwendet, die durch Strichpunkte zu trennen sind:
der 1. Bereich (Formatierung: 0,00%) gilt für positive Zahlen (das ist ja bei Deinen Proz.Zahlen normalerweiser der Fall)
der 2. Bereich (Formatierung: "Kein Anbot") gilt für negative Zahlen: bei meiner Formel kommt bei den Lieferanten, die kein Anbot abgegeben haben (leerer Lieferantenpreis) -1 bzw. -100% heraus; da dies eine neg. Zahl ist, wird Abschnitt 2 aktiv und der enthält die Textkonstante "Kein Anbot", was ja genau das Zutreffende ist.
der 3. Bereich (Formatierung: "Günstigster") gilt für 0-Werte; beim günstigsten Lieferanten kommt als Ergebnis 0 heraus, damit wird der 3. Abschnitt des benutzerdef. Formates aktiv und dort steht die Textkonstante "Günstigster".
Theoretisch könnte man noch einen 4. Abschnitt im benutzerdef. Format festlegen (aber der gilt nicht mehr für Zahlen, sondern für Textwerte in der Zelle).
Aber wie gesagt, das alles bezieht sich auf die Formatierung der Zelle - der Wert, der in der Zelle steht, ist weiterhin ein Zahlenwert (nämlich 0 falls "Günstigster" angezeigt wird bzw. -1 oder -100% falls "Kein Anbot" angezeigt wird bzw. eben ein %-Wert)

EDIT:
Ist man mit obigen (Punkt 3) Standardintervallen nicht zufrieden, kann auch benutzerdef. Intervalle definieren. Ebenso kann man die Farbe in den Intervallen festlegen, etc. - aber wozu gibt es denn eine Excel-Hilfe im Programm integriert ?

 

Hallo Exl121150,

herzlichen Dank für Deine Antworten.
Jetzt weiß ich wieder etwas mehr über Excel und werde mich bezüglich der benutzerdefinierten Formatierung einmal intensiver mit der Hilfe beschäftigen ;-).

nochmal tausend Dank
dutti