Sortierung Daten mit Angabe Adressen...

[volker_xy]

Hallo zusammen,
dachte eigentlich, mein Thema sei erledigt aber beim ersten realen Test sind mir noch Schwächen aufgefallen.

Habe also immer noch zwei kleine Probleme mit der Sortierung meiner Daten.

Problem 1:
Es gibt Teile die ich wiege. Den gewogenen Wert trage ich in die Tabelle ab Zelle B16:AE25 ein.
Es sind 3 separate Sortierbretter in die die Teile "vorgelagert" werden.
In 3 weitere sollen die dann gewichtsmäßig sortiert werden - von leicht nach schwer.
Also die ersten 100 leichtesten in ein neues Sortierbrett, die mittelschweren in das zweite neue und die schwersten in dritte neue Sortierbrett.

Jetzt funktioniert die Sortierung im Bereich B32:AE41 perfekt aber das suchen der Teile aus B16:AE25 ist die Pest.

Schön wäre, wenn im Bereich B47:AE56 die "Adressen aus den Sortierbrettern" farblich unterschieden aus welchem Brett das kommt angegeben werden.

Problem 2:
Im Bereich B32:AE41
wäre es schön, wenn die Zellen farblich so markiert werden, dass alle die Zellen in der die Werte mit der in F9 festgelegten Abweichung vom Mittelwert farblich markiert werden.

Nullwerte oder IstNULL Inalte sollen generell an die letzten Positionen geschrieben werden.

Vielen Dank 

Volker

[Gast 123]

Hallo

ich habe mal eine Teillösung, nur das sortieren im Bereich B32:EA41, ohne Adressen.
Der Mittelwert konnte nicht ausgewertet werden, weil es Ganzahlen ohne Komma sind.
Mit firl auf das es fortlaufende Zahlen sind. Wie sieht das Ergebnis mit echten Zahlen aus???

mfg Gast 123

---

Hallo

Sorry, gerade gesehen, falsche Datei hochgeladen, war deine Originaldatei.  Hier die bearbeitete Datei.

mfg Gast 123

[Fred11]

Hallo Volker,

mal so als Ansatz und Anregung:

"Adressen" ermitteln im Bereich B47:AE56
Formel für den Bereich B47:AE56:
=INDEX($A$16:$A$25;SUMMENPRODUKT(($B$16:$AE$25=B32)*ZEILE($A$1:$A$10)))&INDEX($B$15:$AE$15;SUMMENPRODUKT(($B$16:$AE$25=B32)*SPALTE($A$1:$AD$1)))
(Formel für B47 => nach rechts und nach unten ziehen)
Allerdings funktioniert diese Formel  nur (richtig), wenn im Datenbereich B16:AE25 keine doppelten Werte vorkommen
Falls doppelte Werte in dem Datenbereich vorkommen können (und ich vermute mal, dass das bei 300 Werten, die um einen Mittelwert schwanken, nicht so unwahrscheinlich ist), muss man sich da noch was anderes überlegen.
Mir fällt dazu momentan keine einfache Lösung ein.
Entweder die Werte über einen Zwischenschritt alle eindeutig machen (z.B: bei den doppelten einen minimalen Wert dazuaddieren mit +Spalte/10000 ….).
Oder vielleicht was in der Richtung:
https://www.herber.de/excelformeln/pages...uchen.html

Farbliche Markierung im Bereich B47:AE56
Formeln für bedingte Formatierung für den Bereich B47:AE56:
=AUFRUNDEN(SUMMENPRODUKT(($B$16:$AE$25=B32)*SPALTE($A$1:$AD$1));-1)=30
=AUFRUNDEN(SUMMENPRODUKT(($B$16:$AE$25=B32)*SPALTE($A$1:$AD$1));-1)=20
=AUFRUNDEN(SUMMENPRODUKT(($B$16:$AE$25=B32)*SPALTE($A$1:$AD$1));-1)=10

im Bereich B32:AE41 Abweichung vom Mittelwert um einen bestimmten Betrag farblich hervorheben:
Formel für bedingte Formatierung für den Bereich B32:AE41:
=ABS(B16-$F$8)<=$F$9
=ABS(B16-$F$8)>$F$9

Siehe angehängte Beispieldate

Gruss
Fred

[volker_xy]

Hallo Fred,


guter Ansatz!
Im Ergebnis stehen aber immer in drei Zeilen doppelte identische Werte bei den Adressen obwohl die Daten im Beispiel eindeutig sind.


...leider gibt es auch dann im oberen gewogenen Block in real auch doppelte Werte (im Beispiel nicht). Da muss ich mal schauen wie das gelöst werden kann. 
Wie ich das noch eliminieren kann ????
Das mit dem addieren der +Spalte / 10000 funktioniert doch auch nur wenn nicht auch noch doppelte Werte in der Spalte vorliegen oder bin ich hier auf dem Holzweg. 
ggf. mach ich das so, dass ich mir die doppelten Werte über eine bedingte Formatierung anzeigen lasse und einfach den Wert auf zwei Nachkommastellen erweitere, dann von manuell die zwei letzten Nachkommastellen verwende um die Werte eindeutig zu machen.

hat jemand eine Idee wie die bedingte Formatierung aussehen könnte - kann ja doch auch mehrere doppelte (unterschiedliche) Werte geben?


Gruß

Volker

[Ego]

Hallo Volker,

wenn die Ursprungswerte alle ganzzahlig sind, kann man die Werte über das hinzufügen der Zeilen- und Spaltennummer als Nachkommawerte eindeutig machen und diese Nachkommawerte auswerten.

=INDEX($A:$A;GANZZAHL(REST(AGGREGAT(15;6;$B$16:$AE$25+ZEILE($B$16:$AE$25)%+SPALTE($B$16:$AE$25)%%;(REST(SPALTE(A1)-1;10)+1)+(ZEILE(A1)-1)*10+(SPALTE(A1)>10)*100+(SPALTE(A1)>20));1)*100))&
INDEX($15:$15;REST(AGGREGAT(15;6;$B$16:$AE$25+ZEILE($B$16:$AE$25)%+SPALTE($B$16:$AE$25)%%;(REST(SPALTE(A1)-1;10)+1)+(ZEILE(A1)-1)*10+(SPALTE(A1)>10)*100+(SPALTE(A1)>20))*100;1)*100)

a) Falls die Ursprungswerte nicht ganzzahlig sind, aber nur eine feste Anzahl von Nachkommastellen haben, könnte man mit der entsprechenden Zehnerpotenz multiplizieren solange nicht mehr als 9 Vorkommastellen erzeugt werden.
b) Für die bedingte Formatierung wird die Zeile nicht benötigt.

[volker_xy]

Hallo zusammen,

die Tabelle von Fred wäre eigentlich schon fast perfekt wenn das Ergebnis mit den Adressen korrekt wäre.
Hab nun schon versucht das zu korrigieren aber die Lösung fand ich nicht.
Das Kriterium nach dem in der Formel ausgewertet wird ist richtig aber die angegebene Adresse passt nicht.

Info:

• die gewogenen Werte werden immer mit zwei Nachkommastellen abgelesen und eingetragen.
  
• Hier könnte ich auf 4 Nachkommastellen erweitern und gleiche Werte dann manuell an den letzten Stellen unterschiedlich machen.
  
• Zahlen sind immer 113,45 bzw. dann 123,4567
  

Wie gesagt am meisten macht mir die korrekte Auswertung der Adressen Kummer!

Wäre Super, wenn sich ein Profi das anschauen könnte.

Vielen Dank

Volker

[ws-53]

Anbei eine mit Power Query erstellte Lösung.

[{Boris}]

Hi,

nur am Rande - quasi "außer Konkurrenz":

Die Sortierung alle Werte erhält man mit der neuesten Excelversion mit dieser einzigen Formel in

B32:
=SPALTENUMBRUCH(KKLEINSTE(B16:AE25;SEQUENZ(300));10)

[Ego]

Hallo Volker,

bei Weten mit zwei Nachkommata wäre es:

=INDEX($A:$A;GANZZAHL(REST(AGGREGAT(15;6;RUNDEN($B$16:$AE$25*100;0)+ZEILE($B$16:$AE$25)%+SPALTE($B$16:$AE$25)%%;(REST(SPALTE(A1)-1;10)+1)+(ZEILE(A1)-1)*10+(SPALTE(A1)>10)*100+(SPALTE(A1)>20));1)*100))&
INDEX($15:$15;REST(AGGREGAT(15;6;RUNDEN($B$16:$AE$25*100;0)+ZEILE($B$16:$AE$25)%+SPALTE($B$16:$AE$25)%%;(REST(SPALTE(A1)-1;10)+1)+(ZEILE(A1)-1)*10+(SPALTE(A1)>10)*100+(SPALTE(A1)>20))*100;1)*100)

und für die grüne bedingte Formatierung:

=REST(AGGREGAT(15;6;RUNDEN($B$16:$AE$25*100;0)+ZEILE($B$16:$AE$25)%+SPALTE($B$16:$AE$25)%%;(REST(SPALTE(A1)-1;10)+1)+(ZEILE(A1)-1)*10+(SPALTE(A1)>10)*100+(SPALTE(A1)>20))*100;1)*100<=12

---

Sorry,

die Spalte muss noch gerundet werden:

=INDEX($A:$A;GANZZAHL(REST(AGGREGAT(15;6;RUNDEN($B$16:$AE$25*100;0)+ZEILE($B$16:$AE$25)%+SPALTE($B$16:$AE$25)%%;(REST(SPALTE(A1)-1;10)+1)+(ZEILE(A1)-1)*10+(SPALTE(A1)>10)*100+(SPALTE(A1)>20));1)*100))&
INDEX($15:$15;RUNDEN(REST(AGGREGAT(15;6;RUNDEN($B$16:$AE$25*100;0)+ZEILE($B$16:$AE$25)%+SPALTE($B$16:$AE$25)%%;(REST(SPALTE(A1)-1;10)+1)+(ZEILE(A1)-1)*10+(SPALTE(A1)>10)*100+(SPALTE(A1)>20))*100;1)*100;0))

=RUNDEN(REST(AGGREGAT(15;6;RUNDEN($B$16:$AE$25*100;0)+ZEILE($B$16:$AE$25)%+SPALTE($B$16:$AE$25)%%;(REST(SPALTE(A1)-1;10)+1)+(ZEILE(A1)-1)*10+(SPALTE(A1)>10)*100+(SPALTE(A1)>20))*100;1)*100;0)<12

[Ego]

Nochmal sorry,

da ist mir beim kopieren deiner Ermittlung des Blockes hinter ">20" ein "*100" verlorengegangen so dass der dritte Block nicht erkannt wurde.

=INDEX($A:$A;GANZZAHL(REST(AGGREGAT(15;6;RUNDEN($B$16:$AE$25*100;0)+ZEILE($B$16:$AE$25)%+SPALTE($B$16:$AE$25)%%;(REST(SPALTE(A1)-1;10)+1)+(ZEILE(A1)-1)*10+(SPALTE(A1)>10)*100+(SPALTE(A1)>20)*100);1)*100))&
INDEX($15:$15;RUNDEN(REST(AGGREGAT(15;6;RUNDEN($B$16:$AE$25*100;0)+ZEILE($B$16:$AE$25)%+SPALTE($B$16:$AE$25)%%;(REST(SPALTE(A1)-1;10)+1)+(ZEILE(A1)-1)*10+(SPALTE(A1)>10)*100+(SPALTE(A1)>20)*100)*100;1)*100;0))

=RUNDEN(REST(AGGREGAT(15;6;RUNDEN($B$16:$AE$25*100;0)+ZEILE($B$16:$AE$25)%+SPALTE($B$16:$AE$25)%%;(REST(SPALTE(A1)-1;10)+1)+(ZEILE(A1)-1)*10+(SPALTE(A1)>10)*100+(SPALTE(A1)>20)*100)*100;1)*100;0)<12