Live-Forum - Die aktuellen Beiträge
Datum
Titel
24.04.2024 20:38:58
Summe mit Bedingung, nur letzte Ergebnisse anzeigen
24.04.2024 19:29:30
Formel filter
24.04.2024 18:49:56
merge & select
24.04.2024 17:19:09
Formel mit Index-Funktion
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1336to1340
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
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1336to1340
1336to1340
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Makro Index & Vergleich

Makro Index & Vergleich
07.11.2013 19:36:20
maba
Hey Leute,
ich habe ein hoffentlich kleines Problem
Und zwar habe ich mehrere Tabellen mit 6000+ Zeilen.
In einem anderen Tabellenblatt möchte ich eine Auswertung vornehmen und muss daten aus den verschiedenen Tabellen zusammenziehen.
Dies möchte ich mit einer index&vergleich-formel in VBA hinbekommen, und zwar für jede einzelne der 6000 Zeilen.
Im Endeffekt möchte ich einfach nur wissen, wie ich die Variable i in die folgende Formel richtig einfüge
For i= 1 to 6000
Range("g"& i).FormulaLocal = " = Index (Beispieltabelle!A1:N1000; Vergleich ( "B"& i &"F"& i; Beispieltabelle!c1:c1000 & Beispieltabelle!g1:g1000;0);8)
Next i
Sodass für jede Zeile die passenden Werte eingefügt werden
Es ist mir wirklich wichtig,dass ich das per Makro in alle Zellen einfügen kann, ich saß da heute schon den halben Tag dran :/.
Ich hoffe ihr versteht was ich euch sagen will^^
Vielen Dank schonmal, ich hoffe mir kann und wird jemand helfen

15
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Makro Index & Vergleich
07.11.2013 19:58:09
Erich
Hi Vorname,
aus deiner VBA-Zuweisung wird nicht recht klar, wie die Formel in Excel nachher aussehen soll.
Insbesondere der Teil
Vergleich ( "B"& i &"F"& i;
also das Suchkriterium für VERGLEICH, ist mir rätselhaft.
Da steht so etwas wie VERGLEICH(B1F1;
Was ist B1F1?
Am einfachsten wäre, wenn du einfach mal schreibst, wie die Formeln in G1 und G2 lauten sollen.
Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich

noch eine Frage
07.11.2013 20:23:37
Erich
Hi,
auch der zweite Parameter von VERGLEICH dürfte Probleme bereiten. VERGLEICH erwartet eine
Suchmatrix, einen Zellbereich, der durchsucht wird.
Was soll bei
Beispieltabelle!c1:c1000 & Beispieltabelle!g1:g1000;0)
durchsucht werden? Verkettete Texte?
Noch eins. Mit
Index(Beispieltabelle!A1:N1000; ... ;8)
werden doch nur Werte aus Spalte H (8. Spalte) abgegriffen. Da kannst du auch einfacher schreiben:
Index(Beispieltabelle!H1:H1000; ...)
Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich

Anzeige
AW: noch eine Frage
07.11.2013 22:44:06
maba
okay ,alsooo
Mal vereinfacht ohne verschiedene tabellen:
A | B | C | D | H
1 | 6 | 11 | 16 |
2 | 7 | 12 | 17 |
3 | 8 | 13 | 18 |
Also ich möchte nun,dass in Spalte H1:H4 die folgende Index-formel steht,wobei immer nach dem Wert in Spalte A und B (also nach 2 Kriterien) gesucht werden soll und wenn der Wert gefunden wurde dann soll der Wert aus C in Spalte H eingetragen werden.
Also die Formel für H1 wäre
Range("H1").FormulaLocal="= Index(A1:B5;Vergleich(A1&B1; A1:A5& B1:B5;0);3)
Im Endeffekt soll das für 6000 Zeilen eingetragen werden in Spalte H und die Zeilennummern sollen sich immer der entsprechenden Zeile anpassen. Ich dachte da wäre ne schleife mit der zeilennummer als Variable ganz sinnvoll.
für die weiteren würde das so aussehen:
Range("H2").FormulaLocal="= Index(A1:B5;Vergleich(A2&B2; A1:A5& B1:B5;0);3)
Range("H3").FormulaLocal="= Index(A1:B5;Vergleich(A3&B3; A1:A5& B1:B5;0);3)
Range("H4").FormulaLocal="= Index(A1:B5;Vergleich(A4&B4; A1:A5& B1:B5;0);3)
Range("H5").FormulaLocal="= Index(A1:B5;Vergleich(A5&B5; A1:A5& B1:B5;0);3)
usw bis zeile 6000 ungefähr^^
Da es händisch massig arbeit wäre suche ich einen weg das per Schleife zu lösen, nur leider haben alle meine Versuche bisher nix gebracht :/
Vielen Dank für die schnelle Antwort :)
ps: Sorry falls die tabelle bisschen verrutscht ist und auch für den vielen Text^^.
Gruß Maba

Anzeige
mal ganz ohne VBA
07.11.2013 23:36:34
Erich
Hi Vorname,
bei "Excel gut" solltest du mit INDEX und VERGLEICH umgehen können. :-)
Hast du mal überprüft, was passiert, wenn du die Formel
= Index(A1:B5;Vergleich(A2&B2; A1:A5& B1:B5;0);3)
in Zelle H2 schreibst?
Wie soll z. B. Index(A1:B5;...;3) funktionieren, wenn A1:B5 nur 2 Spalten hat?
Du solltest erst einmal die richtige Excelformel in Excel ermitteln
und erst im zweiten Schritt die Formel mit VBA eintragen.
Nebenbei:
Stehen in Spalte C nur Zahlen? Das könnte die Formel vereinfachen.
Und welche Art von Werten steht in den Spalten A:B?
Schau mal in http://www.excelformeln.de/formeln.html?welcher=30
Da steht auch etwas zu {Arrayformeln}. In VBA gibt es die Eigenschaft FormulaArray.
Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich

Anzeige
AW: mal ganz ohne VBA
07.11.2013 23:58:41
maba
Zu dem Index funktionieren:Ja gut,das war ein Denkfehler im vereinfachten Beispiel, aber in Wirklichkeit passt das schon so..
Und zu nebenbei: Nein ,es stehen Texte sowie Zahlen, bzw werde ich die gleiche Formel später für eine weitere Spalte anwenden und in einer Spalte sind Zahlen und in der anderen Texte.
Und die Formel kann man selbstverständlich auch in Excel eingeben und dann runterziehen, jedoch kann ich dann nicht mit diesen Formeln weiterarbeiten: z.B. wenn auf die 2 Kriterien mehrere Werte zutreffen, dass dann noch eine neue Zeile eingefügt wird und die anderen Werte übernommen werden. Das ist meines Wissens nicht ohne VBA ohne Weiteres möglich?!
Lasse mich jedoch gerne eines Besseren belehren.
Außerdem möchte ich mich auch in VBA weiterbilden,damit ich beides draufhabe^^
Gruß Maba

Anzeige
Formel
07.11.2013 23:51:07
Erich
Hi,
vielleicht hilft die folgende Tabelle bei der Klärung der Formel:
 ABCDE
1ABCFormelkürzer
216111111
327121212
438131313
527141212
617151515

Formeln der Tabelle
ZelleFormel
D2{=INDEX($A$2:$C$6;VERGLEICH(A2&B2;$A$2:$A$6&$B$2:$B$6;0); 3)}
E2{=INDEX(C$2:C$6;VERGLEICH(A2&B2;A$2:A$6&B$2:B$6;0))}
D3{=INDEX($A$2:$C$6;VERGLEICH(A3&B3;$A$2:$A$6&$B$2:$B$6;0); 3)}
E3{=INDEX(C$2:C$6;VERGLEICH(A3&B3;A$2:A$6&B$2:B$6;0))}
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
Die Formeln in D2 oder D2 kannst du nach unten kopieren. Meintest du diese Formel?
Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich

Anzeige
AW: Formel
08.11.2013 08:41:14
Martin
Hm ja so hatte ich das auch, wie gesagt brauchte ich es für VBA um es weiter zu bearbeiten..
Da bin ich nun auch schon weiter gekommen:
With Range("G2:G6000")
.Formula = "=Index(Tabelle2!$A$1:$N$6000,Match($C2,Tabelle2!$C$1:$C$6000,0),8)"
End With
So trägt er mir die Formel in den ganzen Bereich von G2 und G6000 mit angepassten Suchbegriffen( steht in G2 in $C2 bzw in G3 mit $C3 usw...)
Soweit so gut, war einfacher als gedacht. ^^
Das Problem ist, wenn ich nun nach 2Kriterien Suche also diese Formel:
With Range("G2:G6000")
.Formula = "=Index(Tabelle2!$A$1:$N$6000,Match($C2&$F2,Tabelle2!$C$1:$C$6000&Tabelle2!$G1:$G6000,0),8)"
End With
Dann passt er mir die Zeilen nicht mehr an und gibt nur das Ergebnis für die erste Zeile des Bereiches in allen Zeilen an:/
Gruß Maba

Anzeige
AW: Formel
08.11.2013 08:42:17
Martin
Hm ja so hatte ich das auch, wie gesagt brauchte ich es für VBA um es weiter zu bearbeiten..
Da bin ich nun auch schon weiter gekommen:
With Range("G2:G6000")
.Formula = "=Index(Tabelle2!$A$1:$N$6000,Match($C2,Tabelle2!$C$1:$C$6000,0),8)"
End With
So trägt er mir die Formel in den ganzen Bereich von G2 und G6000 mit angepassten Suchbegriffen( steht in G2 in $C2 bzw in G3 mit $C3 usw...)
Soweit so gut, war einfacher als gedacht. ^^
Das Problem ist, wenn ich nun nach 2Kriterien Suche also diese Formel:
With Range("G2:G6000")
.FormulaArray = "=Index(Tabelle2!$A$1:$N$6000,Match($C2&$F2,Tabelle2!$C$1:$C$6000&Tabelle2!$G1:$G6000,0),8)"
End With
Dann passt er mir die Zeilen nicht mehr an und gibt nur das Ergebnis für die erste Zeile des Bereiches in allen "G"-Zellen an:/
Gruß Maba

Anzeige
AW: Formel
08.11.2013 09:13:34
Luschi
Hallo Martin,
die Formeleinträge per Vba gehen so: With Range("G2") .FormulaArray = "=Index(Tabelle2!$A$1:$N$6000,Match($C2&$F2,Tabelle2!$C$1:$C$6000&Tabelle2!$ _ G$1:$G$6000,0),8)" End With Range("G2").AutoFill Destination:=Range("G2:G6000"), Type:=xlFill Gruß von Luschi
aus klein-Paris
PS: Du einst einen Array-Vverbund definiert, willst aber eigentlich die 'Formeln nach unten auffüllen'!

AW: Formel
08.11.2013 09:37:07
Martin
Hallo Luschi,
Wow, perfekt,genauso wollte ich es, war das erste Mal,dass ich so richtig mit Matrizen gearbeitet habe, daher noch ein wenig unwissend.
DANKEEEEE!!!
Kurze Frage noch: Könnt ihr mir sagen, wie ich dafür sorge, dass er mir alle Treffer anzeigt,indem er soviele Zeilen einfügt wie es Treffer gibt und die dann dort einfügt?
Super Forum mit schnellen Antworten.
Nochmal Danke :)
Gruß Maba

Anzeige
AW: Formel
09.11.2013 14:40:00
maba
Hey, ich bins nochmal,
ich habs nun gestern die ganze zeit versucht eine der formeln von der seite
http://excelformeln.de/formeln.html?welcher=28
auf meine Formel anzuwenden, jedoch krieg ich das nicht so richtig hin, könnte sich der Sache jemand annehmen?:/
Ich möchte halt alles angezeigt bekommen die den 2 Kriterien entsprechen, die Formel steht ja oben schon mehrfach und mein problem ist eigentlich die Zeilen- Funktion. Bei der weiß ich nicht so recht wie man die anpassen muss :(
Gruß Maba

Was ist unklar?
09.11.2013 18:00:03
Erich
Hi Martin,
wo genau liegt jetzt dein Problem?
Du schriebst:
"Kurze Frage noch: Könnt ihr mir sagen, wie ich dafür sorge, dass er mir alle Treffer anzeigt,
indem er soviele Zeilen einfügt wie es Treffer gibt und die dann dort einfügt?"
"Zeilen einfügen" geht natürlich nicht per Formeln. Die Antwort auf die kurze Frage ist lang...
Wie soll das Ergebnis konkret aussehen? (Am besten in einer Beispielmappe oder mit Jeanie o.ä. darstellen)
Welche Probleme hast du mit den Formeln von Nr. 28?
Welche dieser Formeln hast du probiert, wie genau und mit welchem Ergebnis?
"jedoch krieg ich das nicht so richtig hin" ist da nicht wirklich erhellend.
"mein problem ist eigentlich die Zeilen- Funktion. Bei der weiß ich nicht so recht wie man die anpassen muss"
Ja, aber wie man das tun muss, hängt konkret von deinem Tabellenaufbau und dem gewünschten Ergebnis ab!
VBA würde ich erst mal außen vor lassen. Zunächst wäre die Formel zu klären. :-)
Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich

Anzeige
AW: Was ist unklar?
10.11.2013 19:51:46
maba
Also , hier ist mal eine stark vereinfachte Tabelle, auch nur auf einem Tabellenblatt, um das wichtigste zu zeigen:
https://www.herber.de/bbs/user/88022.xlsx
Es geht darum,dass in Tabelle 3 zunächst einmal die 2 vorhandenen Spalten aus Tabelle 1 kopiert werden.
Mithilfe der Index-Verweis Funktion wird dann in jeder Zeile nach dem Wert in Spalte A und dem Wert in Spalte B gesucht und bei einem Treffer der Wert aus der C-Spalte der Tabelle 2 in Tabelle 3 Spalte C eingefügt.
Soweit so gut, nun ist mein Problem jedoch,dass es mehrere Treffer zu den 2 Suchkriterien( Material und Arbeitsplatz) gibt , die Index-Vergleich- Funktion mir jedoch nur den ersten Wert ausspuckt. Ich benötige jedoch alle Werte, am besten in einer Zeile.
Am allerbesten natürlich per VBA aber ich bin momentan für jede Hilfe dankbar,nachdem ich mir gestern und heute die Zähne ausgebissen habe.
Habe auch schon sämtliche Foren durchsucht und
http://www.excelformeln.de/formeln.html?welcher=28
an dieser Seite hab ich mich auch shcon versucht,nur weiß ich das nicht so recht anzuwenden.
Gruß Martin

Formeln
10.11.2013 21:01:59
Erich
Hi Martin,
die Formel in C2 kannst du nach rechts und unten kopieren:
 ABCDEF
1ArbeitsplatzMaterialIstzeit Ein   
2100011111716 
3300082222817 

Formeln der Tabelle
ZelleFormel
A2=WENN(Tabelle1!B2>"";Tabelle1!B2;"")
B2=WENN(Tabelle1!A2>0;Tabelle1!A2;"")
C2{=WENN($A2&$B2="";""; WENNFEHLER(INDEX(Tabelle2!$C:$C; KKLEINSTE(WENN($A2&"|"&$B2=Tabelle2!$A$2:$A$20&"|"&Tabelle2!$B$2:$B$20; ZEILE(Tabelle2!$A$2:$A$20)); SPALTE(A2))); ""))}
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
Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich

AW: Formeln
10.11.2013 21:57:24
maba
Hallo Erich,
Also ich hab die nun an zwei Zeilen getestet, und bislang funktioniert die super,genauso wollte ich das haben!! Ich bin echt begeistert, du Excel-Gott^^
Ganz großes Dankeschön!!!
Viele Grüße
Martin

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige