PDA

Vollständige Version anzeigen : Excel 2003: Spalte in Zeile pro Datensatz


Artistline
03.07.2014, 09:12
Hallo zusammen,

ich habe leider folgendes Problem, welches ich momentan nicht lösen kann. Ich hoffe Ihr könnt helfen.

Problem:
Im Anhang sind folgende Felder schon definiert und vorgegeben:
Person, Fa und Ergebnis.
Jetzt muss ich die Top 3 Zahlen aus dem Ergebniss in die Zeile einsetzen.
Die Top 3 Zahlen sind hier die gelben Zellen. So würde es am Ende aussehen.
Die Spalte Ergebnis ist schon absteigend sortiert. Hier müssen also nur die ersten 3 Top Zahlen genau vor der Zeile wo die Personenzahl in die Zeilen eingetragen werden.
Da ich ca. 6000 Datensätze habe, würde es ewig dauern bis ich alles händisch eingetragen habe.

Ich hatte es erst mit Sverweis probiert aber komme da nicht weiter.

Viele Grüße
Artistline

Hajo_Zi
03.07.2014, 09:14
Der Dateiname sollte was mit dem Problem zu tun haben.
Ich habe mir z.B. einen Ordner angelegt in dem ich alle Dateien aus dem Internet speichere. Bei Dateinamen wie Test..., Mappe…, Beispiel… wird eine vorhandene überschrieben.
Schaue hier, Dateiname im Beitrag (http://www.ms-office-forum.net/forum/showthread.php?t=58538&highlight=Beitrags-Nr#3)
das ist nun das Word -Forum, das gilt hier aber auch.
Das bezieht sich auf den Dateiname Test....
<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>

Mc Santa
03.07.2014, 09:17
Hallo,

wenn die Tabelle genau so aussieht, dann habe ich folgende Formellösung für dich:
<br/><b><em>Tabelle1</em></b><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "><colgroup><col style="font-weight:bold; width:40px;" /><col style="width:97px;" /><col style="width:97px;" /><col style="width:97px;" /><col style="width:97px;" /><col style="width:97px;" /><col style="width:97px;" /></colgroup><tr style="background-color:#99CCFF; text-align:center; font-weight:bold; "><td>&nbsp;</td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">1</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Person</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">FA</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Ergebnis</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Top 1</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Top2</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Top3</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">2</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">1000252</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">HN</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">2</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">2</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">1</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">1</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">3</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">UR</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">1</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">4</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">KA</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">1</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">5</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">NE</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">1</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">6</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">SU</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">1</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">7</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">1000253</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">HA</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">5</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">5</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">8</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">1000257</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">NE</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">1</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">1</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">1</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">9</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">CU</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">1</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">10</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">1000261</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">GA</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">28</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">28</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">5</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">3</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">11</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">KA</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">5</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">12</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">RH</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">3</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">13</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">CH</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">3</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">14</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">1000266</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">CH</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">2</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">2</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">1</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">1</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">15</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">TH</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">1</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">16</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">HN</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">1</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">17</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">GA</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">1</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">18</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">GC</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">1</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">19</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">HM</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">1</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">20</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">PM</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">1</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td></tr></table><br/><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px;font-size:11pt; background-color:#ffffff; width:800px;padding-left:2pt; padding-right:2pt; "><tr style="background-color:#FFCC66; text-align:center; font-weight:bold; "><td colspan="3" > verwendete Formeln </td><td> </td></tr><tr valign="top" style="background-color:#FFCC66; text-align:center; font-weight:bold; "><td> Zelle </td><td> Formel</td><td> Bereich </td> <td>N/A</td></tr><tr><td>D2:&nbsp;D20</td><td>=WENN(A2&lt;&gt;"";C2;"")</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr><td>E2:E20</td><td>=WENN(UND(A2&lt;&gt;"";A3="");C3;"")</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr><td>F2:F20</td><td>=WENN(UND(A2&lt;&gt;"";A3="";A4="");C4;"")</td><td>&nbsp;</td><td>&nbsp;</td></tr></table><table cellspacing="0" cellpadding="0"><tr style="text-align:left; font-weight:bold; " class="style21"><td style="text-align:left; font-size: xx-small" ><a href="http://excel-inn.de/dateien/vba_beispiele/tabellenanzeige_in_html_addin.zip" >Excel-Inn.de</a></td></tr> <tr style="text-align:left; font-weight:bold; " class="style21"><td style="text-align:left; font-size: xx-small" ><a href='http://Hajo-Excel.de/tools.htm' >Hajo-Excel.de</a></td></tr><tr style="text-align:left; font-weight:bold;" ><td style="text-align:left; font-size: xx-small" >XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007</td></tr><tr style="text-align:left; font-weight:bold; " ><td style="text-align:left; font-size: xx-small" > Add-In-Version 18.01 einschl. 64 Bit</td></tr></table><br/>Hilft dir das weiter?
VG

Artistline
03.07.2014, 09:17
Hallo Hajo,

hier nochmal die Datei mit veränderten Namen.

@MC Santa: Das ging aber schnell....super ganz herzlichen Dank. Klingt logisch....wieder was dazu gelernt. Ganz toll :-)

Grüße
Artistline

Hajo_Zi
03.07.2014, 09:47
ich hätte ja alle Personalnummern hingeschrieben, wertet sich in Excel leichter aus.
Zellen ausfüllen mit Wert davor
Range("a:a").SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"

<br/><b><em>Tabelle1</em></b><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "><colgroup><col style="font-weight:bold; width:40px;" /><col style="width:97px;" /><col style="width:97px;" /><col style="width:97px;" /><col style="width:97px;" /><col style="width:97px;" /><col style="width:97px;" /></colgroup><tr style="background-color:#99CCFF; text-align:center; font-weight:bold; "><td>&nbsp;</td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">1</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Person</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">FA</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Ergebnis</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Top 1</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Top2</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Top3</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">2</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">1000252</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">HN</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">2</td><td style=" color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:right; ">2</td><td style=" color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:right; ">1</td><td style=" color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:right; ">1</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">3</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">1000252</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">UR</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">1</td><td style=" color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:left; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">4</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">1000252</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">KA</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">1</td><td style=" color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:left; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">5</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">1000252</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">NE</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">1</td><td style=" color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:left; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">6</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">1000252</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">SU</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">1</td><td style=" color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:left; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">7</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">1000253</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">HA</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">5</td><td style=" color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:right; ">5</td><td style=" color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:right; ">0</td><td style=" color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:right; ">0</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">8</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">1000257</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">NE</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">1</td><td style=" color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:right; ">1</td><td style=" color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:right; ">1</td><td style=" color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:right; ">0</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">9</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">1000257</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">CU</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">1</td><td style=" color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:left; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">10</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">1000261</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">GA</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">28</td><td style=" color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:right; ">28</td><td style=" color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:right; ">5</td><td style=" color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:right; ">3</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">11</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">1000261</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">KA</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">5</td><td style=" color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:left; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">12</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">1000261</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">RH</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">3</td><td style=" color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:left; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">13</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">1000261</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">CH</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">3</td><td style=" color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:left; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">14</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">1000266</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">CH</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">2</td><td style=" color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:right; ">2</td><td style=" color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:right; ">1</td><td style=" color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:right; ">1</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">15</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">1000266</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">TH</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">1</td><td style=" color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:left; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">16</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">1000266</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">HN</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">1</td><td style=" color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:left; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">17</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">1000266</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">GA</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">1</td><td style=" color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:left; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">18</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">1000266</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">GC</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">1</td><td style=" color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:left; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">19</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">1000266</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">HM</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">1</td><td style=" color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:left; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">20</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">1000266</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">PM</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">1</td><td style=" color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:left; ">&nbsp;</td></tr></table><br/><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px;font-size:11pt; background-color:#ffffff; width:800px;padding-left:2pt; padding-right:2pt; "><tr style="background-color:#FFCC66; text-align:center; font-weight:bold; "><td colspan="3" > verwendete Formeln </td><td> </td></tr><tr valign="top" style="background-color:#FFCC66; text-align:center; font-weight:bold; "><td> Zelle </td><td> Formel</td><td> Bereich </td> <td>N/A</td></tr><tr><td>D2:F20</td><td>{=WENN(ZÄHLENWENN($A$2:A2;$A2)=1;WENN(ISTFEHLER(KGRÖSSTE($C$2:$C$20;SPALTE()-3)) ;"";KGRÖSSTE($C$2:$C$20*N($A$2:$A$20=$A2);SPALTE()-3));"")}</td><td>$D$2</td><td>&nbsp;</td></tr></table><b>{} Matrixformel mit Strg+Umschalt+Enter abschließen<br/>Matrixformeln sind durch geschweifte Klammern {} eingeschlossen<br/>Diese Klammern nicht eingeben!!</b><br/><br/><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px; font-size:11pt;width:800px; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "><tr style="background-color:#FFCCCC; text-align:center; font-weight:bold; "><td colspan="3" > Zahlenformate </td></tr><tr valign="top" style="background-color:#FFCCCC; text-align:center; font-weight:bold; "><td> Zelle </td><td> Format </td><td> Wert </td></tr><tr><td>D2 </td> <td style="text-align:center; ">'0_ ;-0;""</td> <td style="text-align:right; ">2</td></tr><tr><td>E2 </td> <td style="text-align:center; ">'0_ ;-0;""</td> <td style="text-align:right; ">1</td></tr><tr><td>F2 </td> <td style="text-align:center; ">'0_ ;-0;""</td> <td style="text-align:right; ">1</td></tr><tr><td>D3 </td> <td style="text-align:center; ">'0_ ;-0;""</td> <td style="text-align:right; ">&nbsp;</td></tr><tr><td>E3 </td> <td style="text-align:center; ">'0_ ;-0;""</td> <td style="text-align:right; ">&nbsp;</td></tr><tr><td>F3 </td> <td style="text-align:center; ">'0_ ;-0;""</td> <td style="text-align:right; ">&nbsp;</td></tr><tr><td>D4 </td> <td style="text-align:center; ">'0_ ;-0;""</td> <td style="text-align:right; ">&nbsp;</td></tr><tr><td>E4 </td> <td style="text-align:center; ">'0_ ;-0;""</td> <td style="text-align:right; ">&nbsp;</td></tr><tr><td>F4 </td> <td style="text-align:center; ">'0_ ;-0;""</td> <td style="text-align:right; ">&nbsp;</td></tr><tr><td>D5 </td> <td style="text-align:center; ">'0_ ;-0;""</td> <td style="text-align:right; ">&nbsp;</td></tr><tr><td>E5 </td> <td style="text-align:center; ">'0_ ;-0;""</td> <td style="text-align:right; ">&nbsp;</td></tr><tr><td>F5 </td> <td style="text-align:center; ">'0_ ;-0;""</td> <td style="text-align:right; ">&nbsp;</td></tr><tr><td>D6 </td> <td style="text-align:center; ">'0_ ;-0;""</td> <td style="text-align:right; ">&nbsp;</td></tr><tr><td>E6 </td> <td style="text-align:center; ">'0_ ;-0;""</td> <td style="text-align:right; ">&nbsp;</td></tr><tr><td>F6 </td> <td style="text-align:center; ">'0_ ;-0;""</td> <td style="text-align:right; ">&nbsp;</td></tr><tr><td>D7 </td> <td style="text-align:center; ">'0_ ;-0;""</td> <td style="text-align:right; ">5</td></tr><tr><td>E7 </td> <td style="text-align:center; ">'0_ ;-0;""</td> <td style="text-align:right; ">0</td></tr><tr><td>F7 </td> <td style="text-align:center; ">'0_ ;-0;""</td> <td style="text-align:right; ">0</td></tr><tr><td>D8 </td> <td style="text-align:center; ">'0_ ;-0;""</td> <td style="text-align:right; ">1</td></tr><tr><td>E8 </td> <td style="text-align:center; ">'0_ ;-0;""</td> <td style="text-align:right; ">1</td></tr><tr><td>F8 </td> <td style="text-align:center; ">'0_ ;-0;""</td> <td style="text-align:right; ">0</td></tr><tr><td>D9 </td> <td style="text-align:center; ">'0_ ;-0;""</td> <td style="text-align:right; ">&nbsp;</td></tr><tr><td>E9 </td> <td style="text-align:center; ">'0_ ;-0;""</td> <td style="text-align:right; ">&nbsp;</td></tr><tr><td>F9 </td> <td style="text-align:center; ">'0_ ;-0;""</td> <td style="text-align:right; ">&nbsp;</td></tr><tr><td>D10 </td> <td style="text-align:center; ">'0_ ;-0;""</td> <td style="text-align:right; ">28</td></tr><tr><td>E10 </td> <td style="text-align:center; ">'0_ ;-0;""</td> <td style="text-align:right; ">5</td></tr><tr><td>F10 </td> <td style="text-align:center; ">'0_ ;-0;""</td> <td style="text-align:right; ">3</td></tr><tr><td>D11 </td> <td style="text-align:center; ">'0_ ;-0;""</td> <td style="text-align:right; ">&nbsp;</td></tr><tr><td>E11 </td> <td style="text-align:center; ">'0_ ;-0;""</td> <td style="text-align:right; ">&nbsp;</td></tr><tr><td>F11 </td> <td style="text-align:center; ">'0_ ;-0;""</td> <td style="text-align:right; ">&nbsp;</td></tr><tr><td>D12 </td> <td style="text-align:center; ">'0_ ;-0;""</td> <td style="text-align:right; ">&nbsp;</td></tr><tr><td>E12 </td> <td style="text-align:center; ">'0_ ;-0;""</td> <td style="text-align:right; ">&nbsp;</td></tr><tr><td>F12 </td> <td style="text-align:center; ">'0_ ;-0;""</td> <td style="text-align:right; ">&nbsp;</td></tr><tr><td>D13 </td> <td style="text-align:center; ">'0_ ;-0;""</td> <td style="text-align:right; ">&nbsp;</td></tr><tr><td>E13 </td> <td style="text-align:center; ">'0_ ;-0;""</td> <td style="text-align:right; ">&nbsp;</td></tr><tr><td>F13 </td> <td style="text-align:center; ">'0_ ;-0;""</td> <td style="text-align:right; ">&nbsp;</td></tr><tr><td>D14 </td> <td style="text-align:center; ">'0_ ;-0;""</td> <td style="text-align:right; ">2</td></tr><tr><td>E14 </td> <td style="text-align:center; ">'0_ ;-0;""</td> <td style="text-align:right; ">1</td></tr><tr><td>F14 </td> <td style="text-align:center; ">'0_ ;-0;""</td> <td style="text-align:right; ">1</td></tr><tr><td>D15 </td> <td style="text-align:center; ">'0_ ;-0;""</td> <td style="text-align:right; ">&nbsp;</td></tr><tr><td>E15 </td> <td style="text-align:center; ">'0_ ;-0;""</td> <td style="text-align:right; ">&nbsp;</td></tr><tr><td>F15 </td> <td style="text-align:center; ">'0_ ;-0;""</td> <td style="text-align:right; ">&nbsp;</td></tr><tr><td>D16 </td> <td style="text-align:center; ">'0_ ;-0;""</td> <td style="text-align:right; ">&nbsp;</td></tr><tr><td>E16 </td> <td style="text-align:center; ">'0_ ;-0;""</td> <td style="text-align:right; ">&nbsp;</td></tr><tr><td>F16 </td> <td style="text-align:center; ">'0_ ;-0;""</td> <td style="text-align:right; ">&nbsp;</td></tr><tr><td>D17 </td> <td style="text-align:center; ">'0_ ;-0;""</td> <td style="text-align:right; ">&nbsp;</td></tr><tr><td>E17 </td> <td style="text-align:center; ">'0_ ;-0;""</td> <td style="text-align:right; ">&nbsp;</td></tr><tr><td>F17 </td> <td style="text-align:center; ">'0_ ;-0;""</td> <td style="text-align:right; ">&nbsp;</td></tr><tr><td>D18 </td> <td style="text-align:center; ">'0_ ;-0;""</td> <td style="text-align:right; ">&nbsp;</td></tr><tr><td>E18 </td> <td style="text-align:center; ">'0_ ;-0;""</td> <td style="text-align:right; ">&nbsp;</td></tr><tr><td>F18 </td> <td style="text-align:center; ">'0_ ;-0;""</td> <td style="text-align:right; ">&nbsp;</td></tr><tr><td>D19 </td> <td style="text-align:center; ">'0_ ;-0;""</td> <td style="text-align:right; ">&nbsp;</td></tr><tr><td>E19 </td> <td style="text-align:center; ">'0_ ;-0;""</td> <td style="text-align:right; ">&nbsp;</td></tr><tr><td>F19 </td> <td style="text-align:center; ">'0_ ;-0;""</td> <td style="text-align:right; ">&nbsp;</td></tr><tr><td>D20 </td> <td style="text-align:center; ">'0_ ;-0;""</td> <td style="text-align:right; ">&nbsp;</td></tr><tr><td>E20 </td> <td style="text-align:center; ">'0_ ;-0;""</td> <td style="text-align:right; ">&nbsp;</td></tr><tr><td>F20 </td> <td style="text-align:center; ">'0_ ;-0;""</td> <td style="text-align:right; ">&nbsp;</td></tr></table><b>Zellen mit Format Standard werden nicht dargestellt</b><br/><table cellspacing="0" cellpadding="0"><tr style="text-align:left; font-weight:bold; " class="style21"><td style="text-align:left; font-size: xx-small" ><a href="http://excel-inn.de/dateien/vba_beispiele/tabellenanzeige_in_html_addin.zip" >Excel-Inn.de</a></td></tr> <tr style="text-align:left; font-weight:bold; " class="style21"><td style="text-align:left; font-size: xx-small" ><a href='http://Hajo-Excel.de/tools.htm' >Hajo-Excel.de</a></td></tr><tr style="text-align:left; font-weight:bold;" ><td style="text-align:left; font-size: xx-small" >XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007</td></tr><tr style="text-align:left; font-weight:bold; " ><td style="text-align:left; font-size: xx-small" > Add-In-Version 18.06 einschl. 64 Bit</td></tr></table><br/>

<a href="http://Hajo-Excel.de/index.htm" target="_blank" title="Hajo's Excelseiten">Gruß Hajo</a>