Das Clever-Excel-Forum.de - Treffen
findet vom 15. - 17. September 2017 in Thüringen / Region Großer Inselsberg statt. Hotelbuchung ab sofort möglich.


Prozentuale Abweichung schrittweise einfärben
#1
Hallo Zusammen!

Ich habe ein kleines Problem mit dem Erstellen einer Formel in Excel 2013. Ich habe eine Tabelle mit verschiedenen Produkten und der Herstellungszeit in Minuten. Jetzt geht es darum die Produkte (insgesamt 650 Stück) an Hand ihrer Herstellungszeiten in Produktfamilien einzuteilen. Die Literatur gibt folgende Formel vor:

((längste Herstellungzeit - kürzeste Herstellungszeit)/längste Herstellungszeit)*100<30%

Das heißt die zeitliche Abweichung innerhalb einer Familie darf höchstens 30% betragen. Was ich erreichen möchte ist, dass Excel erkennt wann eine Abweichung im 30% Bereich ist und diese Familie dann einfärbt, sodass ich später die einzelnen Produktfamilien habe, die jeweils in ihrer Herstellungszeit nicht mehr als 30% auseinanderliegen.

Ich habe echt schon alles probiert, komme aber auf keinen grünen Zweig. 22

Liebe Grüße
Basti 21
to top
#2
Hi Basti,

(10.03.2015, 09:55)Hav0c86 schrieb: Ich habe echt schon alles probiert, komme aber auf keinen grünen Zweig.  22

Du hast aber dann nur zwei Familien (>=30 und <30) oder?
Spalte D ist nur, damit der %-Wert da steht, Spalte E könnte auch in einer bedingten Formatierung mit Einfärbung stehen und Spalte F für eine Darstellung der Familie:

Tabelle1
ABCDEF
1ProduktHerstellzeit langHerstellzeit kurzProzentPrüfungFamilie
21151033,3333333333333Falsch2
32151220Wahr1
4310820Wahr1
5410370Falsch2
65151033,3333333333333Falsch2
76151220Wahr1
8710820Wahr1
9810370Falsch2
109151033,3333333333333Falsch2
1110151220Wahr1
121110820Wahr1
131210370Falsch2

verwendete Formeln
Zelle Formel Bereich N/A
D2: D13=((B2-C2)/B2)*100
E2:E13=((B2-C2)/B2)*100<30
F2:F13=WENN(E2;1;2)

Zahlenformate
Zelle Format Wert
D2, D6, D10 '0 33,3333333333333
D3: D4, D7: D8, D11: D12 '0 20
D5, D9, D13 '0 70
Zellen mit Format Standard werden nicht dargestellt

Bedingte Formatierung Haupttabelle 1
Zelle   Nr. Bed.  Regeltyp  Operator   Formel1   Formel2   Format Schrift Füllfarbe   Unterstrichen   Schrift- farbe Muster   Musterfarbe Typ Bereich
$A$2:$F$1301.Bed.: Formel ist=(($B2-$C2)/$B2)*100>=3049407       2A2:F13
$A$2:$F$1302.Bed.: Formel ist=(($B2-$C2)/$B2)*100<305296274       2A2:F13

Excel-Inn.de
Hajo-Excel.de
XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007
Add-In-Version 18.24 einschl. 64 Bit

Gruß Ralf

?mage

Die deutsche Rechtschreibung ist Freeware, d.h. du kannst sie kostenlos nutzen.
Allerdings ist sie nicht Open Source, deswegen darfst du sie nicht verändern oder in veränderter Form veröffentlichen.
to top
#3
Hola,

http://www.office-fragen.de/index.php/to...270.0.html

hier ist wohl eine Beispieltabelle vorhanden.

Gruß,
steve1da
to top
#4
Gelöscht. Weil: da war jemand schneller...
cu jörg eine rückmeldung wäre ganz reizend XL2003 bis XL2013
to top
#5
Hi Rabe!

Hier nochmal eine Beispieltabelle:

https://www.dropbox.com/s/byhl5ndrvnjld4....xlsx?dl=0

Jedes Produkt hat nur EINE Herstellzeit. Ich habe die Produkte jetzt mal nach Herstelltzeit geordnet. Excel soll jetzt Zelle (B2-B3)/B2 rechnen und prüfen ob der Quotient <0,3 ist. Wenn die Probe wahr ist, soll Excel (B2-B4)/B2 rechnen. Wenn diese Probe jetzt >0,3 ist dann soll B2 und B3 eingefärbt werden und bilden somit eine Produktfamilie. Jetzt wird B4 als Basis verwendet. Also (B4-B5)/B4<0,3 und so weiter und sofort.
Ich rechne bei dem Umfang von ca. 600 Produkten und Herstellzeiten von 3-360 Minuten mit ca. 20-25 Produktfamilien.
to top
#6
Hi,

(10.03.2015, 13:43)Hav0c86 schrieb: Hier nochmal eine Beispieltabelle:
[...]
Jedes Produkt hat nur EINE Herstellzeit. Ich habe die Produkte jetzt mal nach Herstelltzeit geordnet.
[...]
Ich rechne bei dem Umfang von ca. 600 Produkten und Herstellzeiten von 3-360 Minuten mit ca. 20-25 Produktfamilien.

"nochmal" ist gut, bisher habe ich weder hier noch im anderen Forum eine Beispieldatei gesehen. 19

Also hier der Ausschnitt Deiner Datei (ich sehe da keine Ordnung):

Tabelle1
ABCDEFGH
1ArtikelProduktionszeit
2E24
3Q120,5
4B180,25
5L100,583333333333333
6O110,541666666666667
7G50,791666666666667
8D20
9C15
10U7
11R2

verwendete Formeln
Zelle Formel Bereich N/A
D3=(B2-B3)/B2
E4=(B2-B4)/B2
F5=(B2-B5)/B2
G6=(B2-B6)/B2
H7=(B2-B7)/B2

Zahlenformate
Zelle Format Wert
D3 '0,00 0,5
D4: D11, E3, E5:E11, F3:F4, F6:F11, G3:G5, G7:G11, H3:H6, H8:H11 '0,00
E4 '0,00 0,25
F5 '0,00 0,583333333333333
G6 '0,00 0,541666666666667
H7 '0,00 0,791666666666667
Zellen mit Format Standard werden nicht dargestellt
Excel-Inn.de
Hajo-Excel.de
XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007
Add-In-Version 18.24 einschl. 64 Bit


Welche Produkte sind da nun in der gleichen Familie?

Ich glaube, das geht, wenn überhaupt, nur per Makro.
Ich weiß nicht, wie Excel per Formel den Beginn der nächsten Produktfamilie für die Formel als Vergleichsstart verwenden kann. Der Bereich kann ja "beliebig" lang werden.

Aber nur weil ich das nicht weiß, heißt das nicht, daß alle anderen auch sagen, daß das nicht geht. Blush
Gruß Ralf

?mage

Die deutsche Rechtschreibung ist Freeware, d.h. du kannst sie kostenlos nutzen.
Allerdings ist sie nicht Open Source, deswegen darfst du sie nicht verändern oder in veränderter Form veröffentlichen.
to top
#7
Hi Steve,

(10.03.2015, 13:25)steve1da schrieb: hier ist wohl eine Beispieltabelle vorhanden.

obwohl es dort so steht, sehe ich in dem dortigen Forum keine!
Gruß Ralf

?mage

Die deutsche Rechtschreibung ist Freeware, d.h. du kannst sie kostenlos nutzen.
Allerdings ist sie nicht Open Source, deswegen darfst du sie nicht verändern oder in veränderter Form veröffentlichen.
to top
#8
Hi Ralf,

kann natürlich sein, dass die nur für registrierte sichtbar ist.

Gruß,
steve1da
to top
#9
So hier mal eine händisch ausgerechnete Tabelle:

https://www.dropbox.com/s/tvf6fb9hdb9cbc....xlsx?dl=0

So soll es im Endeffekt zum Schluss aussehen...

Any ideas?

LG
Basti
to top


Möglicherweise verwandte Themen...
Thema Verfasser Antworten Ansichten Letzter Beitrag
  Jahreskalender, Datum eintragen => automatisches einfärben in Kalender Fuxalex 4 107 03.12.2016, 23:42
Letzter Beitrag: schauan
  [VBA] Einfärben von Textboxen über Schleife Rabe 4 80 25.11.2016, 22:04
Letzter Beitrag: schauan
  Zelle einfärben+Text nicht sichtbar LindaB 3 92 10.11.2016, 13:12
Letzter Beitrag: Jockel
  Reiter einfärben wenn lion7123 18 391 04.11.2016, 08:11
Letzter Beitrag: lion7123
  Bedingtes Einfärben ganzer Zeilen ExcelNoob2413 9 166 27.10.2016, 10:19
Letzter Beitrag: steve1da
  Pivot -> Abweichung berechnen über Element? larsddorf 2 128 22.09.2016, 15:21
Letzter Beitrag: Peter
  Stückliste vergleichen: Teilinhalte von Zellen in div. Spalten suchen und einfärben Rabe 8 360 23.08.2016, 12:23
Letzter Beitrag: Rabe
  Prozentuale Differenz über mehrere Ebenen ermitteln Olchen1 3 301 09.05.2016, 11:34
Letzter Beitrag: radagast
  Spalte und eine Matrix überprüfen, dann eine Zelle einfärben Sommi 3 557 08.03.2016, 12:16
Letzter Beitrag: Sommi
  einzelne Linien in Diagramm einfärben mikele gross 4 648 14.02.2016, 10:35
Letzter Beitrag: mikele gross

Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste