Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1476to1480
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Werte aus Spalte in Abhängigkeit in Zeile

Werte aus Spalte in Abhängigkeit in Zeile
04.03.2016 18:17:37
Martin
Guten Tag zusammen ich hab folgendes Problem.
Ich hab folgende Struktur:
Kunde Kennzeichen
A AA-BB 1234
B BB-C 1234
B BB-G 1234
B BB-M 1111
C CC-D 1234
C CC-G 3344
D DD-M 5555
Ich möchte weiter Spalten in Abhängigkeit von Kunde befüllen. Gibt es da eine schlaue Formel? Ergebnis soll sein:
Kunde Kennzeichen 1---Kennzeichen 2---Kennzeichen 3
A AA-BB 1234
B BB-C 1234-------BB-G 1234-------BB-M 1111
C CC-D 1234-------CC-G 3344
D DD-M 5555
Die doppelten in Spalte Kunde würde ich nach der Übertragung in die Kennzeichen
entfernen. Ich würde mich freuen wenn mir hier jemand ne schlaue Formel sagen könnte.
Vielen Dank im vorraus!

11
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: hier mal wieder 2 ("echte") Matrixformeln...
04.03.2016 19:08:44
...
Hallo Martin,
... da Du Excel 2007 im Einsatz hast.
Diese (wenn es sich nicht um zig tausende Datensätze handeln sollte) besser auch in einem anderen Ergebnistabellenblatt (hier Tabelle2) einsetzen. Damit kannst Du in Tabelle1 fortlaufend erfassen und es wird dann das Ergebnis gleich fortlaufend wunschgemäß "protokolliert".
Formel A2 nur nach unten, B2 zusätzlich auch nach rechts weit genug ziehend kopieren.
Die Formeln sind momentan für 99 Datensätzen aufbereitet. Völlig unproblematisch ist es, diese bis 1000 zu erweitern.
Tabelle2

 ABCD
1KundeKennzeichen 1Kennzeichen 2Kennzeichen 3
2AAA-BB 1234  
3BBB-C 1234BB-G 1234BB-M 1111
4CCC-D 1234CC-G 3344 
5DDD-M 5555  
6    

Formeln der Tabelle
ZelleFormel
A2{=INDEX(Tabelle1!A:A;MIN(WENN(ISTNV(VERGLEICH(Tabelle1!A$1:A$99;A$1:A1;)); ZEILE(A$1:A$99))))&""}
B2{=WENNFEHLER(INDEX(Tabelle1!$B:$B;KKLEINSTE(WENN(Tabelle1!$A$1:$A$99=$A2;ZEILE(Tabelle1!B$1:B$99)); SPALTE(A1))); "")}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen


Tabelle1

 AB
1KundeKennzeichen
2AAA-BB 1234
3BBB-C 1234
4BBB-G 1234
5BBB-M 1111
6CCC-D 1234
7CCC-G 3344
8DDD-M 5555
9  


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...

Anzeige
AW: hier mal wieder 2 ("echte") Matrixformeln...
04.03.2016 20:22:36
Martin
Hallo Werner,
vielen Dank schon mal für die Antwort, aber irgendwie schein ich noch was falsch zu machen.
Ich bin wie folgt vorgegangen:
1. Zweites Tabellenblatt die Formeln eingefügt ohne {}
2. Die Formel mit STRG + Enter + SHIFT bestätigt
3. Formel über Spalte erweitern
4. Bei der Formel für B2 hab ich die gleichen Schritte ausgeführt
5. Ich hab nur die Zeilen erweitern, da es bei mir 16753 sind
Ich würde das ganze auf einem Server rechnen lassen, deswegen glaub ich ist das nicht so tragisch.
Bei mir sieht das so aus:
Userbild
Das online Darstellungstool kann ich nicht herunterladen
Viele Grüße
Martin

Anzeige
AW: hier mal wieder 2 ("echte") Matrixformeln...
04.03.2016 20:30:07
Martin
Ich hab hier anbei mal eine kleine Paradedatei. Vielleicht hilft das ja.
So ist mein jetziger Zustand:
https://www.herber.de/bbs/user/104112.xlsx

AW: hier mal wieder 2 ("echte") Matrixformeln...
04.03.2016 21:56:30
Werner
Hallo Martin,
würde mal sagen falsche Reihenfolge
1. Formel reinkopieren
2. Formel verändern in Bezug auf Reihe/Spalte
3. Formel abschließen mit STRG + Shift + Enter
Sobald du an der Formel was änderst ist es keine Array-Formel mehr. D.h. nach Änderung abschließen als Array-Formel und nicht vor der Änderung.
Gruß Werner

AW: hier mal wieder 2 ("echte") Matrixformeln...
04.03.2016 22:24:07
Josef
Hallo Martin
Wenn dir eine aufsteigende Sortierung der Nummern recht ist, dann wird die Formel einfacher.
Tabelle2

 A
1Kunde
29870
3314729
4326177
5398177
6398353
7459482
8586984
9631643
10971576
112025525
122054714
132056518
142058471
152062631
162079831

Formeln der Tabelle
ZelleFormel
A2=WENN(A1="";"";WENNFEHLER(KKLEINSTE(Tabelle1!A$2:A$99;ZÄHLENWENN(Tabelle1!A$2:A$99;"<="&A1)+1); ""))


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
Gruss Sepp

Anzeige
Komplett-Matrixformel-Lösung mit UDFs
05.03.2016 02:11:00
Luc:-?
Morrn, Martin;
dein 1.Bsp hätte einfach ergänzt wdn können, denn du wolltest die doppelten Kunden ja entfernen (Kriterium hier: An=An-1):
 ABCD
1KundeKennzeichen1Kennzeichen2Kennzeichen3
2AAA-BB 1234  
3BBB-C 1234BB-G 1234BB-M 1111
4BBB-G 1234  
5BBB-M 1111  
6CCC-D 1234CC-G 3344 
7CCC-G 3344  
8DDD-M 5555  
9C2[:D8]: {=WENN(ODER($A2=$A1;ZÄHLENWENN($A$2:$A$8;$A2)=1);"";
10              Splint(VJoin(WENN($A$2:$A$8=$A2;$B$2:$B$8;"");"|";-1);"|";
11              RECHTS(C$1);RECHTS(C$1)))}

Deine ParadeDatei zeigt ein Bsp mit anspruchsloserer Struktur. Offensichtlich sollen die verdichteten Daten jetzt auch in eine ExtraTab auf einem anderen Blatt portiert wdn:
 ABCD
1KundeKennzeichen 1Kennzeichen 2Kennzeichen 3
2314729RT-NR 130  
3398177RT-UH 26  
42062631RT-JC 3860  
5398353ES-HC 7179  
62025525S-FN 5500  
7631643UL-LC 100  
8586984TÜ-MY 340  
92079831DÜW-BW 12  
10971576S-EK 9876  
11459482RT-SH 2306  
122056518PF-IA 222  
13326177WÜ-N 3878  
142054714CW-SN 82  
159870CW-B 1120CW-B 5030 
162058471GP-RO 8938  
17A2[:A16]:=--Splint(VJoin(Tabelle1!A$2:A$18;;-1);;ZEILE(A1))
18B2:D2[;B3:D16]: {=VSplit(VJoin(WENN(Tabelle1!A$2:A$18=A2;Tabelle1!B$2:B$18;"");"|";-1)&
19                              WIEDERHOLEN("|";SPALTEN(B$1:D$1)-ZÄHLENWENN(Tabelle1!A$2:A$18;A2)+1);"|")}
Die beiden Fmln sind so aufgebaut, dass sie zeilen­weise kopiert wdn können, wobei die in Spalte A keine Matrix­Fml-Form benötigt, da sie stets die der Zeile entsprd Kunden­Nr aus dem Gesamt­Vektor* aller einander ungleichen Kunden­Nrn wiedergibt. Die ab Spalte B benötigt die Matrix­Fml-Form, da sie alle evtl zur Kunden­Nr in A vorhan­denen Kenn­zeichen wiedergeben muss. Da in Bsp-Tabelle1 auch 2 völlig identische Zeilen vorkommen, habe ich vorgesehen, dass auch gleiche Kenn­zeichen nur 1× aufgeführt wdn. Damit stets die gleiche MxFml über alle im Kopf dafür vorge­sehenen Spalten kopiert wdn kann, wdn ggf zusätzl Trenn­zeichen '|' angefügt (wg der Doppel­Kenn­zeichen mindestens 1 mehr als sonst erforder­lich), die beim Trennen mit UDF VSplit einen Leer­Text in der jewei­ligen Spalten­Zelle ergeben.
Alle verwendeten UDFs sind im Herber-Archiv (Button RECHERCHE, 1.Such­Menü­Zeile benutzen!) enthalten, Splint und VSplit direkt, VJoin in aktueller Version 1.4 nur in hoch­gela­denen Bsp­Dateien (idR zusammen mit VSplit).
* Wenn die, wie Sepp vorschlägt, auch noch sortiert wdn sollen (geht in Fml nur mit rein numerischen Werten, die hier per vorangestelltem -- erzeugt wdn), muss die Fml in A2 so lauten (3.VSplit-Arg für ZahlErzeugung): =KKLEINSTE(VSplit(VJoin(Tabelle1!A$2:A$18;;-1);;1);ZEILE(A1))
Feedback erwünscht! Gruß, Luc :-?
Besser informiert mit …

Anzeige
AW: dazu ist aber anzumerken ...
05.03.2016 08:11:17
...
Hallo Luc,
... dass Deine Hinweise zu dem ersten Beispiel zumindest nicht ausreichend sind. Denn wenn Martin die Zeilen mit den Dupletten in Spalte A löscht, ohne vorher die Formelergebnisse durch ihre Werte zu ersetzen "sitzt er im Wasser". Und wenn er sie löscht, kann er die Erfassung nicht kontinuierlich fortsetzen ohne sie wieder einzusetzen. Aus diesem Grund hab ich ja eine Ergebnistabelle vorgeschlagen.
Außerdem wenn die Kundenbezeichnung alles Zahlenwerte sind, ist eine {}-freie Formel einer {}-Matrixformellösung vorzuziehen.
Gruß Werner
.. , - ...

Stimmt, hatte vergessen zu erwähnen, dass ...
05.03.2016 12:29:02
Luc:-?
…bei der 1.Variante die Ergebnisse als Wert über die Fmln zu speichern sind, Werner,
wie ich das bei einem ähnlichen Bsp schon mal getan hatte. Da hat mich wohl schon die 2.Variante abgelenkt…
Gruß, Luc :-?

Anzeige
AW: der entscheidende Unterschied ist, ...
05.03.2016 08:02:09
...
Hallo Martin,
... dass in Deiner neuen Datei die Kundenkennzeichnung ausschließlich durch Zahlenwerte und nicht alphanumerische Zeichen wie in Deiner ersten Datei erfolgt.
Deshalb ergeben die 100% Umsetzung meines Formelvorschlag bei Dir nicht die gewünschten Ergebnisse. Eine einfaches weglassen von &"" in der Formel von A2 hätte da schon ausgereicht. Teste mal selbst.
Und um nach dem Ende des Listings eine Fehlermeldung zu vermeiden, hätte dafür die Formel auch mit noch einem WENNFEHLER() umklammert werden können, analog wie die Formel in B2.
Aber bei ausschließlich echten Zahlenwerten für die Kundenbezeichnung, wie sie jetzt offensichtlich bei Dir vorliegen, kann die Formel in A2 schon vereinfacht werden. Dafür hat Dir aber Sepp schon eine Lösungsformel aufgezeigt, die ich Dir empfehle.
Gruß Werner
.. , - ...

Anzeige
So etwas sollte man auch bei Testdaten ...
05.03.2016 12:31:37
Luc:-?
…niemals machen, Martin,
die Datentypen sollten wenigstens übereinstimmen!
Luc :-?

AW: So etwas sollte man auch bei Testdaten ...
05.03.2016 12:35:53
Martin
Hallo zusammen,
vielen Dank für eure Unterstützung. Meine Lösung war jetzt eine Mischung aus Werner`s Ansatz und der Erweiterung um Sepp`s Vorschlag. Da es sich um 16753 Zeilen gehandelt hat war Werners Vorschlag zwar korrekt aber noch ein wenig unperformant. Über die Ergänzung von Sepp lief die Berechnung effizienter ab.
Ich muss euch also allen Danke sagen für Eure Unterstützung.
VIELEN LIEBEN DANK!!!!

316 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige