Dieses Forum nutzt Cookies
Dieses Forum verwendet Cookies, um deine Login-Informationen zu speichern, wenn du registriert bist, und deinen letzten Besuch, wenn du es nicht bist. Cookies sind kleine Textdokumente, die auf deinem Computer gespeichert werden. Die von diesem Forum gesetzten Cookies werden nur auf dieser Website verwendet und stellen kein Sicherheitsrisiko dar. Cookies aus diesem Forum speichern auch die spezifischen Themen, die du gelesen hast und wann du zum letzten Mal gelesen hast. Bitte bestätige, ob du diese Cookies akzeptierst oder ablehnst.

Ein Cookie wird in deinem Browser unabhängig von der Wahl gespeichert, um zu verhindern, dass dir diese Frage erneut gestellt wird. Du kannst deine Cookie-Einstellungen jederzeit über den Link in der Fußzeile ändern.

Sortierung Daten mit Angabe Adressen...
#1
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


Angehängte Dateien
.xlsm   Sortierung2.xlsm (Größe: 20,91 KB / Downloads: 11)
Antworten Top
#2
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


Angehängte Dateien
.xlsm   Sortierung2.xlsm (Größe: 20,91 KB / Downloads: 1)
.xls   Sortierung Daten mit Adressen.xls (Größe: 34 KB / Downloads: 7)
Antworten Top
#3
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


Angehängte Dateien
.xlsx   Sortierung2_fs.xlsx (Größe: 30,07 KB / Downloads: 5)
< es lebe die Hilfsspalte >
[-] Folgende(r) 2 Nutzer sagen Danke an Fred11 für diesen Beitrag:
  • volker_xy,
Antworten Top
#4
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
Antworten Top
#5
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.

Zitat:=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.
helmut

Für mich ist die Möglichkeit in Excel an Zellen und Bereichen Namen zu vergeben die wichtigste Funktionalität.
Sie macht Formeln und den VBA-code verständlicher. Für Makros gilt die Regel: "Nur über benannte Bereiche auf den Inhalt der Zellen zugreifen."
Und wofür sind Regeln da? Um nachzudenken bevor man sie bricht.





[-] Folgende(r) 1 Nutzer sagt Danke an Ego für diesen Beitrag:
  • volker_xy
Antworten Top
#6
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
Antworten Top
#7
Anbei eine mit Power Query erstellte Lösung.


Angehängte Dateien
.xlsx   cef - Sortierung Daten mit Angabe Adressen (PQ).xlsx (Größe: 63,08 KB / Downloads: 6)
[-] Folgende(r) 2 Nutzer sagen Danke an ws-53 für diesen Beitrag:
  • volker_xy,
Antworten Top
#8
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)
[-] Folgende(r) 2 Nutzer sagen Danke an {Boris} für diesen Beitrag:
  • volker_xy,
Antworten Top
#9
Hallo Volker,

bei Weten mit zwei Nachkommata wäre es:
Zitat:=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:
Zitat:=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:
Zitat:=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))
Zitat:=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


Angehängte Dateien
.xlsm   Sortierung2(1).xlsm (Größe: 39 KB / Downloads: 1)
.xlsm   Sortierung2(1).xlsm (Größe: 39,82 KB / Downloads: 0)
helmut

Für mich ist die Möglichkeit in Excel an Zellen und Bereichen Namen zu vergeben die wichtigste Funktionalität.
Sie macht Formeln und den VBA-code verständlicher. Für Makros gilt die Regel: "Nur über benannte Bereiche auf den Inhalt der Zellen zugreifen."
Und wofür sind Regeln da? Um nachzudenken bevor man sie bricht.





[-] Folgende(r) 1 Nutzer sagt Danke an Ego für diesen Beitrag:
  • volker_xy
Antworten Top
#10
Nochmal sorry,

da ist mir beim kopieren deiner Ermittlung des Blockes hinter ">20" ein "*100" verlorengegangen so dass der dritte Block nicht erkannt wurde.
Zitat:=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))
Zitat:=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


Angehängte Dateien
.xlsm   Sortierung3.xlsm (Größe: 39,69 KB / Downloads: 5)
helmut

Für mich ist die Möglichkeit in Excel an Zellen und Bereichen Namen zu vergeben die wichtigste Funktionalität.
Sie macht Formeln und den VBA-code verständlicher. Für Makros gilt die Regel: "Nur über benannte Bereiche auf den Inhalt der Zellen zugreifen."
Und wofür sind Regeln da? Um nachzudenken bevor man sie bricht.





[-] Folgende(r) 2 Nutzer sagen Danke an Ego für diesen Beitrag:
  • volker_xy,
Antworten Top


Gehe zu:


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