Microsoft Excel

Herbers Excel/VBA-Archiv

Daten zuordnen | Herbers Excel-Forum


Betrifft: Daten zuordnen von: Mat
Geschrieben am: 20.01.2010 21:06:43

Hallo Leute,

ich möchte aus einer Übersicht Namen zu einer vorgegebene Reihenfolge zuorden. Die Kriterien sind Gruppe und Datum, welche der vorgegebenen Reihenfolge des jeweiligen Datums aus der gleichen Gruppe zugeordnet werden sollen.

Hier mein Beispiel: https://www.herber.de/bbs/user/67364.xls

Viele Grüße

Mat

  

Betrifft: verstehe die Zuordnung nicht? von: Tino
Geschrieben am: 21.01.2010 08:28:39

Hallo,
kannst Du es mal etwas besser erklären damit ich es auch verstehe?

z. Bsp. verstehe ich nicht wieso im Ergebnis bei Dir Hubert auftaucht,
der ist doch der Gruppe B zugeordnet.

Gruß Tino


  

Betrifft: AW: Daten zuordnen von: fcs
Geschrieben am: 21.01.2010 08:32:54

Hallo Mat,

das geht mit einer Mischung aus WENN, INDEX, VERGLEICH und INDIREKT als Formel.

Gruß
Franz
Tabelle1

 ABC
17Ergebnis
18 GruppeDatum
19 A02.01.2010
20Nr.1---
21Nr.2Maier
22Nr.3---
23Nr.4---
24Nr.5---
25Nr.6---
26Nr.7---
27Nr.8Egon
28Nr.9Müller

Formeln der Tabelle
ZelleFormel
B20=WENN(INDEX($B$2:$B$10;VERGLEICH(A20;INDIREKT("Z2S" & VERGLEICH($C$19;$1:$1;0) & ":Z10S" & VERGLEICH($C$19;$1:$1;0); FALSCH); 0))=$B$19;INDEX($A$2:$A$10;VERGLEICH(A20;INDIREKT("Z2S" & VERGLEICH($C$19;$1:$1;0) & ":Z10S" & VERGLEICH($C$19;$1:$1;0); FALSCH); 0)); "---")


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4


  

Betrifft: AW: Daten zuordnen von: Mat
Geschrieben am: 21.01.2010 19:06:36

Hallo Franz,

danke für Deine Hilfe.

Wäre schon fast gut, aber leider habe ich in meinem Beispiel vergessen, dass die gesuchten Werte mehrfach vorkommen können. Kann man da noch was machen?

Hier das aktuallisierte Beispiel: https://www.herber.de/bbs/user/67399.xls

Gruß Mat


  

Betrifft: AW: Daten zuordnen von: fcs
Geschrieben am: 21.01.2010 23:36:18

Hallo Mat,

das war jetzt noch mal ein kniffeliges Formel-Spiel.

Damit der Formelweg ggf. etwas leichter nachvollziehbar ist hab ich rechts das gleiche mit Hilfsberechnungen gemacht. In B20 sind dieFormeln aus D19 und D20:F20 zu einer Formel zusammengezogen.

Die verbundenen Zellen in B20:C37 muss du vor dem Einfügen der Matrix-Formeln wieder in Einzelzellen auflösen. Verwende hier das horizontale Format "über Auswahl zentrieren".

Gruß
Franz

Tabelle1

 ABCDEF
18 GruppeDatumHilfsrechnungen mit gleichem Rechenweg  
19 a02.01.2010Z2S4:Z10S4  
20Nr.1--- 01---
21Nr.1--- 02---
22Nr.2Maier 21Maier
23Nr.2Müller 22Müller
24Nr.3--- 01---
25Nr.3--- 02---
26Nr.4--- 01---
27Nr.4--- 02---
28Nr.5--- 01---
29Nr.5--- 02---
30Nr.6--- 01---
31Nr.6--- 02---
32Nr.7--- 01---
33Nr.7--- 02---
34Nr.8Egon 11Egon
35Nr.8--- 12---
36Nr.8--- 13---
37Nr.8--- 14---

Formeln der Tabelle
ZelleFormel
D19="Z2S" & VERGLEICH($C$19;$1:$1;0) & ":Z10S" & VERGLEICH($C$19;$1:$1;0)
B20{=WENN(ZÄHLENWENN($A$20:A20;A20)>SUMMENPRODUKT((INDIREKT("Z2S" & VERGLEICH($C$19;$1:$1;0) & ":Z10S" & VERGLEICH($C$19;$1:$1;0); FALSCH)=A20)*($B$2:$B$10=$B$19)); "---";INDEX($A$2:$A$10;KKLEINSTE(WENN(($B$2:$B$10=$B$19)*(INDIREKT("Z2S" & VERGLEICH($C$19;$1:$1;0) & ":Z10S" & VERGLEICH($C$19;$1:$1;0); FALSCH)=A20); ZEILE($B$2:$B$10); ZEILE($A$11))-ZEILE($A$2)+1;ZÄHLENWENN($A$20:A20;A20))))}
D20=SUMMENPRODUKT((INDIREKT($D$19;FALSCH)=A20)*($B$2:$B$10=$B$19))
E20=ZÄHLENWENN($A$20:A20;A20)
F20{=WENN(E20>D20;"---";INDEX($A$2:$A$10;KKLEINSTE(WENN(($B$2:$B$10=$B$19)*(INDIREKT($D$19;FALSCH)=A20); ZEILE($B$2:$B$10); ZEILE($A$11))-ZEILE($A$2)+1;E20)))}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4


  

Betrifft: AW: Daten zuordnen von: Mat
Geschrieben am: 22.01.2010 18:50:42

Hallo Franz,

ja super, so was habe ich mir vorgestellt.

Aus einem mir nicht erkennbaren Grund, schaffe ich es jedoch nicht den Bereich zu vergrößern (an die Matrix habe ich gedacht). Sobald ich den Bereich $B$2:$B$10 erweitere bekomme ich Fehler. Ich benötige etwa 100 Zeilen. Die Abfrage würde dann auf ein zweites Blatt kommen.

Kannst Du mir bei der Erweiterung noch mal helfen?

Viele Grüße

Mat


  

Betrifft: AW: Daten zuordnen von: fcs
Geschrieben am: 24.01.2010 03:16:05

Hallo Mat,

wenn die Daten in Tabelle1 bis zur Zeile 104 gehen, dann muss du folgende Ersetzungen in den Formeln machen:

Z10S ---Z104S
B$10 --- B$104
A$10 --- A$104
A$11 --- A$105
Damit der Verweis von Tabelle1 in der Formel stimmt muss du im 
INDIREKT-Ausdruck vor "Z2S" einfügen: "'Tabelle1'!" &

Bis all diese Ersetzungen in der Formel drin sind werden halt jede Menge Fehler angezeigt, weil die Formelsyntax erst am Ende stimmt.

Gruß
Franz
Tabelle2

 ABCDEF
2 GruppeDatumMit Hilfsrechnungen, gleicher Rechenweg  
3 a02.01.2010'Tabelle1'!Z2S4:Z104S4  
4Nr.1--- 01---

Formeln der Tabelle
ZelleFormel
D3="'Tabelle1'!"&"Z2S" & VERGLEICH($C$3;Tabelle1!$1:$1;0) & ":Z104S" & VERGLEICH($C$3;Tabelle1!$1:$1;0)
B4{=WENN(ZÄHLENWENN($A$4:A4;A4)>SUMMENPRODUKT((INDIREKT("'Tabelle1'!"&"Z2S" & VERGLEICH($C$3;Tabelle1!$1:$1;0) & ":Z104S" & VERGLEICH($C$3;Tabelle1!$1:$1;0); FALSCH)=A4)*(Tabelle1!$B$2:$B$104=$B$3)); "---";INDEX(Tabelle1!$A$2:$A$104;KKLEINSTE(WENN((Tabelle1!$B$2:$B$104=$B$3)*(INDIREKT("'Tabelle1'!"&"Z2S" & VERGLEICH($C$3;Tabelle1!$1:$1;0) & ":Z104S" & VERGLEICH($C$3;Tabelle1!$1:$1;0); FALSCH)=A4); ZEILE(Tabelle1!$B$2:$B$104); ZEILE(Tabelle1!$A$105))-ZEILE(Tabelle1!$A$2)+1;ZÄHLENWENN($A$4:A4;A4))))}
D4=SUMMENPRODUKT((INDIREKT($D$3;FALSCH)=A4)*(Tabelle1!$B$2:$B$104=$B$3))
E4=ZÄHLENWENN($A$4:A4;A4)
F4{=WENN(E4>D4;"---";INDEX(Tabelle1!$A$2:$A$104;KKLEINSTE(WENN((Tabelle1!$B$2:$B$104=$B$3)*(INDIREKT($D$3;FALSCH)=A4); ZEILE(Tabelle1!$B$2:$B$104); ZEILE(Tabelle1!$A$105))-ZEILE(Tabelle1!$A$2)+1;E4)))}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4


Beiträge aus den Excel-Beispielen zum Thema "Daten zuordnen"