PDA

Vollständige Version anzeigen : SVERWEIS, INDEX, VERGLEICH - Problem


#_Columbo_#
06.07.2015, 08:48
Hallo Community,

ich bin formeltechnisch nicht so bewandert, mache fast alles mittels VBA.
Aber jetzt brauche ich mal eine Formel:

A11:A500 = Datum (Arbeitstage)
G11:G500 = Tätigkeiten

Ich bräuchte jetzt die Formel, die den 1. Werte (egal, ob Zahl oder Text) in Tätigkeiten ermittelt und das passende Datum aus Spalte A (gleiche Zeile) ausgibt.
Ich habe es nur geschafft, entweder Text oder Zahlen zu suchen

Alle Versuche mit obigen Funktionen und deren Kombination fanden entweder nichts, NV, oder aber merkwürdigerweise die letzte statt der ersten Fundstelle. :confused:

Ich muß erfahren, wann ein Arbeitnehmer seine Tätigkeit aufgenommen hat

Bin für Tips und Fingerzeige äußerst dankbar

lg Frank

steve1da
06.07.2015, 08:55
Hola,

für die erste Tätigkeit (z.B. in H1)

{=INDEX(G11:G500;VERGLEICH(WAHR;G11:G500<>"";0))}

Achtung: die {} nicht mit eingeben, sondern die Formel mit Strg-Shift-Enter abschließen.

Das zugehörige Datum:

=INDEX(A11:A500;VERGLEICH(H1;G11:G500;0))

Gruß,
steve1da

RPP63neu
06.07.2015, 08:59
Hi Steve!
Warum Matrixformel, oder springe ich zu kurz?

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:80px;" /><col style="width:61px;" /><col style="width:15px;" /><col style="width:80px;" /><col style="width:80px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >&nbsp;</td><td >A</td><td >G</td><td >H</td><td >I</td><td >J</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Datum</td><td >T&auml;tigkeit</td><td >&nbsp;</td><td >T&auml;tigkeit</td><td >ErsterFund</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">01.07.2015</td><td >Z</td><td >&nbsp;</td><td >x</td><td style="text-align:right; ">05.07.2015</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">02.07.2015</td><td >&nbsp;</td><td >&nbsp;</td><td >a</td><td style="text-align:right; ">04.07.2015</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">03.07.2015</td><td style="text-align:right; ">2</td><td >&nbsp;</td><td style="text-align:right; ">2</td><td style="text-align:right; ">03.07.2015</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">04.07.2015</td><td >a</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">05.07.2015</td><td >x</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">06.07.2015</td><td style="text-align:right; ">2</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">07.07.2015</td><td >a</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">08.07.2015</td><td >Z</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:right; ">09.07.2015</td><td >x</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="text-align:right; ">10.07.2015</td><td style="text-align:right; ">2</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="text-align:right; ">11.07.2015</td><td >a</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="text-align:right; ">12.07.2015</td><td >Z</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="text-align:right; ">13.07.2015</td><td >x</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="text-align:right; ">14.07.2015</td><td style="text-align:right; ">2</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="text-align:right; ">15.07.2015</td><td >Z</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="text-align:right; ">16.07.2015</td><td >x</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td style="text-align:right; ">17.07.2015</td><td >Z</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td style="text-align:right; ">18.07.2015</td><td >x</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td style="text-align:right; ">19.07.2015</td><td >Z</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Formeln der Tabelle</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Zelle</td><td >Formel</td></tr><tr><td >J2</td><td >=INDEX(A:A;<span style=' color:008000; '>VERGLEICH(I2;G:G;0)</span>)</td></tr><tr><td >J3</td><td >=INDEX(A:A;<span style=' color:008000; '>VERGLEICH(I3;G:G;0)</span>)</td></tr><tr><td >J4</td><td >=INDEX(A:A;<span style=' color:008000; '>VERGLEICH(I4;G:G;0)</span>)</td></tr></table></td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel Tabellen im Web darstellen &gt;&gt; </span><a style ="font-family:Arial; font-size:9pt; color:#fcf507; background-color:#800040; font-weight:bold;" href="http://www.excel-jeanie-html.de" target="_blank"> Excel Jeanie HTML 4.8 </a>

Gruß Ralf

steve1da
06.07.2015, 09:17
Hola Ralf,

die den 1. Werte (egal, ob Zahl oder Text) in Tätigkeiten ermittelt

ich bin davon ausgegangen dass nicht eine bestimmte Tätigkeit gesucht wird, sondern der erste Eintrag überhaupt.

Gruß,
steve1da

#_Columbo_#
06.07.2015, 09:31
Hi Steve,

super, genau das was ich gesucht habe... :)

Habs die ganze Zeit ohne {} versucht, wahrscheinlich kam ich deswegen nicht weiter.

Kann man das ganze auch in einer Formel verpacken ohne Hilfszelle H1?

#_Columbo_#
06.07.2015, 09:47
Ups hat sich erledigt:

{=INDEX(A11:A500;VERGLEICH(INDEX(G11:G500;VERGLEICH(WAHR;G11:G500<>"";0));G11:G500;0))}

#_Columbo_#
11.07.2015, 10:17
Puh :confused:
ich muß den Thread doch noch mal aufmachen:

{=INDEX($B$13:$B$447;VERGLEICH(INDEX(G$13:G$447;VERGLEICH(WAHR;G$13:G$447<>"";0));G$13:G$447;0))}(Bereich nur an neue Gegebenheiten angepasst)

funktioniert bei Direkteingabe in die Zelle.

Jetzt habe ich versucht, die Matrix-Formel mittels VBA einzutragen:
g_wsUGen.Range("g3").FormulaArray = "=INDEX($B$13:$B$447;MATCH(INDEX(G$13:G$447;MATCH(TRUE;G$13:G$447<>"""";0));G$13:G$447;0))"


Fehlermeldung : Die FormulaArray-Eigenschaft des Range-Objektes kann nicht festgelegt werden

Wo ist der Fehler ...?? ... ich krieg' hier graue Haare !!

Wenn ich keine Lösung finde, muß ich mir doch eine eigene Funktion in VBA basteln

Hajo_Zi
11.07.2015, 10:24
Hallo Frank,

mein Excel hätte die Formel so übersetzt.
=INDEX($B$13:$B$447,MATCH(INDEX(E$13:E$447,MATCH(TRUE,E$13:E$447<>"",0)),E$13:E$447,0))

<img src="http://Hajo-Excel.de/images/grusz1.gif" align="middle" height="40" alt="Grußformel"><a href="http://Hajo-Excel.de/index.htm"><img border="0" src="http://Hajo-Excel.de/images/logo_hajo3.gif" align="middle" height="40" alt="Homepage"></a>

#_Columbo_#
11.07.2015, 11:01
Hallo Hajo,

ich werde mich wohl doch mal mehr in die Formelsyntax reinknien müssen:

g_wsUGen.Range("g3").FormulaArray = "=INDEX($B$13:$B$447,MATCH(INDEX(G$13:G$447,MATCH(TRUE,G$13:G$447<>"""",0)),G$13:G$447,0))"

funktioniert auch wie gewünscht...

Ich hatte in der Zeile drüber eine FormulaLocal stehen
g_wsUGen.Range("g5").FormulaLocal = "=(MONAT(HEUTE())-1)*2 " & _
" - WENN(ISTNV(G3);0;(MONAT(G3)-1)*2)"
und habe dadurch blöderweise die Semikolons in der FormulaArray übernommen

Danke für den entscheidenden Hinweis

Muß aber gestehen, ich wusste erst nicht, was Du mir damit sagen wolltest ;)