Microsoft Excel

Herbers Excel/VBA-Archiv

Zahlen zuordnen aus 2 Zeilen

Betrifft: Zahlen zuordnen aus 2 Zeilen von: erichm
Geschrieben am: 05.10.2014 16:21:12

Hallo,

habe vor kurzem ein ähnliches Problem hier gelöst bekommen. Nachstehend eine farblich markierte Übersicht zum besseren Verständnis. Ich suche die formeln für N3 bis Q3.

Die Grunddaten befinden sich in einer anderen Tabelle; ich denke aber, das ist für die grundsätzliche Formel nicht entscheidend und muss nur angepasst werden, wenn die Formel jetzt in einer Tabelle erstellt wird:

Tabelle1 (2)

 ABCDEFGHIJKLMNOPQ
1 Tabelle 1 Tabelle 2
2betrifft123456 gesucht aus Zeile 3 Ergebnis betrifft Zahl in Zeile 2
3gesucht25117133 331725 5631


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

DANKE.

mfg

  

Betrifft: Sehr hübsch, nur leider wdn die Zusammenhänge ... von: Luc:-?
Geschrieben am: 05.10.2014 16:33:14

…nicht so recht klar, Erich… :-|
Was sind die Ausgangsdaten (B2:G3 ?)? Nach welchen Kriterien wird gesucht (Ergebnis I3:L3 ?)? Was hat das mit B2:G2 zu tun (lt N3:Q3)?
Einfach zu viele ?? !
Gruß, Luc :-?


  

Betrifft: AW: Sehr hübsch, nur leider wdn die Zusammenhänge ... von: erichm
Geschrieben am: 05.10.2014 16:58:27

SORRY!

Also in N3 bis Q3 sollen die Zahlen aus B2 bis G2 eingetragen werden wenn
- die Zahl aus I3 in B3 bis G3 gefunden wird und dann in der betreffenden Spalte die Zahl aus Zeile 2
= Ergebnis in N3
- die Zahl aus J3 in B3 bis G3 gefunden wird und dann in der betreffenden Spalte die Zahl aus Zeile 2 = Ergebnis in O3
- die Zahl aus K3 in B3 bis G3 gefunden wird und dann in der betreffenden Spalte die Zahl aus Zeile 2 = Ergebnis in P3
- die Zahl aus L3 in B3 bis G3 gefunden wird und dann in der betreffenden Spalte die Zahl aus Zeile 2 = Ergebnis in Q3

Bei zwei gleichen Zahlen (siehe I3 und J3), dann soll aus der Zeile 2 aus der zuerst gefundenen Spalte übertragen werden.

Ich hoffe dfas ist jetzt verständlicher - DANKE!

mfg


  

Betrifft: Ja, aber bei der Bedingung ... von: Luc:-?
Geschrieben am: 05.10.2014 18:06:04

bei zwei gleichen Zahlen (siehe I3 und J3), dann soll aus der Zeile 2 aus der zuerst gefundenen Spalte übertragen werden ergibt sich aber in N3:Q3 5 5 3 1
mit der Formel: N3[:Q3]:=INDEX($B$2:$G$2;VERGLEICH(I3;$B$3:$G$3;0))
Luc :-?


  

Betrifft: AW: Ja, aber bei der Bedingung ... von: erichm
Geschrieben am: 05.10.2014 18:17:51

War ich wieder zu ungenau:
Bei zwei gleichen Zahlen (siehe I3 und J3), dann soll aus der Zeile 2 aus der zuerst gefundenen Spalte übertragen werden.
gemeint ist, dass zuerst aus der ersten Spalte, dann aus der zweiten Spalte usw. übertragen wird; deswegen bei O3 die "6" als Ergebnis

Danke nochmal

mfg


  

Betrifft: Dann hättest du besser ... von: Luc:-?
Geschrieben am: 05.10.2014 21:38:17

…bei zwei gleichen Zahlen (siehe I3 und J3), dann soll aus der Zeile 2 aus der jeweiligen Spalte übertragen werden schreiben sollen, Erich,
denn das ist ungleich komplizierter, weshalb du wohl bis Montag auf AW warten musst… ;-]
Gruß, Luc :-?


  

Betrifft: So, ich schlage dir nun folgende recht ... von: Luc:-?
Geschrieben am: 06.10.2014 02:51:09

…universelle einzellige MatrixFormel vor, Erich:
N3[:Q3]: {=KKLEINSTE(INDEX(WENNFEHLER(--TEXT(($B3:$G3=MTRANS($I3:$L3))*$B2:$G2;"0;0;");"");SPALTEN($A3:A3);0);ZÄHLENWENN($I3:I3;I3))}
Diese Fml fktioniert auch mit 3 gleichen Zahlen (auch in I3:L3 verteilt) und mit 2×2 gleichen Zahlen wie ein Test der folgenden, als MatrixKonstanten dargestellten Zellinhalte beweist, …
B5:G6:={1.2.3.4.5.6;25.3.17.1.3.3} und I6:L6:={3.3.3.25}
B8:G9:={1.2.3.4.5.6;25.3.17.1.3.3} und I9:L9:={17.3.3.3}
B11:G12:={1.2.3.4.5.6;25.3.17.1.3.3} und I12:L12:={3.3.25.3}
B14:G15:={1.2.3.4.5.6;25.1.17.1.3.3} und I15:L15:={3.3.1.1}
B17:G18:={1.2.3.4.5.6;25.1.17.1.3.3} und I18:L18:={3.1.3.1}
…deren gezeigten Standorten sich die o.g. Fml automatisch anpasst, wenn sie von N3:Q3 nach N6:Q6 usw kopiert wird.
Hinweis: Um die Test-MxKonstt in das Blatt zu übernehmen, die jeweilige MxKonst in die 1. vorgesehene Zelle kopieren (ab/inkl = → zuvor alle benötigten Zellen auswählen!) und als MxFml abschließen.
Morrn, Luc :-?


  

Betrifft: AW: So, ich schlage dir nun folgende recht ... von: erichm
Geschrieben am: 06.10.2014 07:28:45

Wahnsinn - eine Formel mit MTRANS hatte ich bisher noch nicht!!

Nochmals besten Dank für die Geduld und die perfekte Lösung!!

mfg


  

Betrifft: Ja, MTRANS war hier nötig, weil sonst entweder ... von: Luc:-?
Geschrieben am: 06.10.2014 15:12:02

…von Spalte zu Spalte nur mit einer (anderen) Zahl aus I3:L3 oder stets mit der gleichen vgln worden wäre und so intern keine Matrix (aus der dann mit SPALTEN($A3:A3) die entsprd Zeile gewählt, die mit KKLEINSTE kumulativ sortiert wird), sondern nur ein Vektor aufgebaut würde.
Bitte sehr, Luc :-?


  

Betrifft: Nachtarbeit ... von: neopa C (paneo)
Geschrieben am: 06.10.2014 08:22:35

Guten Morgen Luc,

... so eine Formel, wow.

Ich halte meine morgendliche einfachere MATRIXformel dagegen:

Tabelle1

 ABCDEFGHIJKLMNOPQ
1 Tabelle 1 Tabelle 2
2betrifft123456 gesucht aus Zeile 3 Ergebnis betrifft Zahl in Zeile 2
3gesucht25117133 331725 5631

Formeln der Tabelle
ZelleFormel
N3{=INDEX(2:2;KKLEINSTE(WENN($B3:$G3=I3;SPALTE($B3:$G3)); ZÄHLENWENN($I$3:I3;I3)))}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

Gruß Werner
.. , - ...


  

Betrifft: Nachts denkt man scheints weiter, ... von: Luc:-?
Geschrieben am: 06.10.2014 14:58:51

…Werner,
als bei morgendlichen Schnellschüssen, abgelenkt von Alltagsgeräuschen… ;-)
Ich habe mich ja nicht ohne Grund um eine gewisse Universalität bemüht. Wenn ich deine Fml mit meinen modifizierten Testdaten ausprobiere, kommt Folgendes raus:
B5:G6:={1.2.3.4.5.6;25.3.17.1.3.3} und I6:L6:={3.3.3.25} ⇒ {2.5.6.1} bzw bei dir ⇒ {5.#ZAHL!.#ZAHL!.#ZAHL!}
B8:G9:={1.2.3.4.5.6;25.3.17.1.3.3} und I9:L9:={17.3.3.3} ⇒ {3.2.5.6} bzw bei dir ⇒ {3.#ZAHL!.#ZAHL!.#ZAHL!}
B11:G12:={1.2.3.4.5.6;25.3.17.1.3.3} und I12:L12:={3.3.25.3} ⇒ {2.5.1.6} bzw bei dir ⇒ {6.#ZAHL!.1.#ZAHL!}
B14:G15:={1.2.3.4.5.6;25.1.17.1.3.3} und I15:L15:={3.3.1.1} ⇒ {5.6.2.4} bzw bei dir ⇒ {#ZAHL!.#ZAHL!.2.4}
B17:G18:={1.2.3.4.5.6;25.1.17.1.3.3} und I18:L18:={3.1.3.1} ⇒ {5.2.6.4} bzw bei dir ⇒ {#ZAHL!.2.#ZAHL!.#ZAHL!}
…und als Zugabe eine Konstellation, bei der auch meine Fml (berechtigt) #ZAHL! ausgibt:
B20:G21:={1.2.3.4.5.6;25.1.17.1.3.3} und I21:L21:={3.1.15.1} ⇒ {5.2.#ZAHL!.4} bzw bei dir ⇒ {#ZAHL!.4.#ZAHL!.#ZAHL!}
Gruß, Luc :-?


  

Betrifft: oder auch etwas zu weit ;-) ...denn siehe ... von: neopa C (paneo)
Geschrieben am: 06.10.2014 15:33:20

Hallo Luc,

... meine (ich weiß nicht, welche Du eingesetzt hast) Formel min. so angepasst, dass sie nach unten kopierbar ist, ergibt für Deine Beispiele natürlich die gleichen Werte:

 ABCDEFGHIJKLMNOPQ
1 Tabelle 1 Tabelle 2
2betrifft123456 gesucht aus Zeile 3 Ergebnis betrifft Zahl in Zeile 2
3gesucht25117133 331725 5631
4 25317133 33325 2561
5 25317133 17333 3256
6 25317133 33253 2516
7 25117133 3311 5624
8 25117133 3131 5264
9 25117133 31151 52##4

Formeln der Tabelle
ZelleFormel
N3{=INDEX($2:$2;KKLEINSTE(WENN($B3:$G3=I3;SPALTE($B3:$G3)); ZÄHLENWENN($I3:I3;I3)))}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

Gruß Werner
.. , - ...


  

Betrifft: Das lag wieder mal am Dollar, ... von: Luc:-?
Geschrieben am: 06.10.2014 16:06:40

…Werner,
du hast 2$ hinzugefügt und 1$ entfernt; ich hatte nur hinzugefügt. Das war mir bei meinem „Schnellschuss“ denn doch entgangen (s.oben → halt tags!)… ;-)
Ansonsten hängt so eine Lösung ja auch vom gewählten Ansatz ab und folgt somit auch einer gewissen Einfalls­zufälligkeit.
Gruß, Luc :-?


  

Betrifft: hast wohl zuviel $, das lässt sich ändern ... ;-) von: neopa C (paneo)
Geschrieben am: 06.10.2014 16:53:06

Hallo Luc,

... Du hattest Dich bestimmt ganz schön abgemüht mit Deinem Formelansatz, der ja auch völlig korrekte Ergebnisse ergibt. Und dann zeige ich, ausgeruht wie ich zufällig heute Morgen mal war, einfach so einen relativ einfacheren Ansatz. Denk jetzt nicht "da könnt man sich gleich ein Bein raus reißen und mitten in die ... scheint der Mond durchs offene Fenster ...", denn das ist mein Spruch in solchen Momenten, die ich oft genug hatte und sicher noch haben werde. ;-)

Übrigens, MTRANS() hat mir auch schon so manchmal gute Dienste geleistet, aber hier wäre diese Funktion und die damit hier von verbunden "Formelanbauten" nicht notwendig gewesen, wie Du nun gesehen hast.

Gruß Werner
.. , - ...


  

Betrifft: Bei meinem Test wären die hinzugefügten $ ... ; von: Luc:-?
Geschrieben am: 06.10.2014 17:48:15

…irrelevant gewesen, Werner,
weshalb ich die endgültig nicht hinzugefügt hatte. Entscheidend war das weggelassene $, das schon in der 1.Fml überflüssig war, was ich nun von dir nicht angenommen hätte (und habe!)… ;-)
Ansonsten gehe ich von der vollständigen Matrix aus und du gleich nur vom jeweiligen Vektor derselben, was mit meinen ersten, ähnlichen Ansätzen nicht klappen wollte, weshalb ich dann auf die Matrix (als sichere Bank) verfiel. Und mit der vollständigen Matrix wird's wohl vom Prinzip her nicht viel anders fktionieren.
Luc :-?


  

Betrifft: ach so ... von: neopa C (paneo)
Geschrieben am: 07.10.2014 08:52:35

Guten Morgen Luc,

... nun war es für mich besser nachvollziehbar, wieso Du gestern zu den fehlerhaften Ergebnissen gelangt bist. Mein gestern Morgen unbedacht "gespendetes" $, hat Dir den Streich gespielt. Mir war dies nicht aufgefallen, weil ja nur die Rede davon war, dass die Auswertung (nur) in Zeile 3 erfolgt und somit die Formel nicht nach unten kopiert werden muss, wie ich es dann bei meinem Nachweis gestern Nachmittag vorgenommen hatte. Da hatte ich natürlich die "Spende" wieder eliminiert.

Mein Fazit an Dich: "Trau schau wem ... und was" ;-)


Gruß Werner
.. , - ...


  

Betrifft: Ja, ja, wob. Vektor natürl effektiver ist! ;-) orT von: Luc:-?
Geschrieben am: 07.10.2014 14:50:28

Gruß, Luc :-?


 

Beiträge aus den Excel-Beispielen zum Thema "Zahlen zuordnen aus 2 Zeilen"