PDA

Vollständige Version anzeigen : Liste generieren


Berti_S
04.07.2014, 13:23
Hallo an euch,

ich muss in anhängendem beispielhaftem Konstrukt eine Resultats-Liste ableiten (aus 4 je vierzeiligen Blöcken mit Zahlen, die von 1 bis 7 reichen können, aber summarisch maximal vier Einträge haben dürfen - also nicht im ersten Block 1, 2, 3, 4 und im zweiten Block 1, 5, 6, 7).

Diese (per Formel zu erzeugende) Liste soll in einem fünften Block darunter angeordnet sein und die in den oberen vier Blöcken verwendeten Zahlen sortiert auflisten.
VBA ist nicht erlaubt.

Aktuell löse ich dies mit Wenn-Formeln, die einen Hilfsstring erzeugen, welchen ich dann auswerte.

Habt ihr eine Idee, wie so etwas eleganter zu lösen ist ?

Vielen Dank vorab von


Berti

Mc Santa
04.07.2014, 13:39
Hallo,

ich habe folgenden Vorschlag:
<br/><b><em>Demo</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 colspan="1" rowspan="4;border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Block 1</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#99CC00 ;; text-align:center; ">1</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#99CC00 ;; text-align:center; ">1</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#99CC00 ;; text-align:center; ">1</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">2</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#99CC00 ;; text-align:center; ">2</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#99CC00 ;; text-align:center; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#99CC00 ;; text-align:center; ">2</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">3</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#99CC00 ;; text-align:center; ">3</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#99CC00 ;; text-align:center; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#99CC00 ;; text-align:center; ">3</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">4</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#99CC00 ;; text-align:center; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#99CC00 ;; text-align:center; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#99CC00 ;; text-align:center; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">5</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; 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 colspan="1" rowspan="4;border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Block 2</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#00CCFF ;; text-align:center; ">1</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#00CCFF ;; text-align:center; ">2</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#00CCFF ;; text-align:center; ">4</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">7</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#00CCFF ;; text-align:center; ">2</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#00CCFF ;; text-align:center; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#00CCFF ;; text-align:center; ">5</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">8</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#00CCFF ;; text-align:center; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#00CCFF ;; text-align:center; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#00CCFF ;; text-align:center; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">9</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#00CCFF ;; text-align:center; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#00CCFF ;; text-align:center; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#00CCFF ;; text-align:center; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">10</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; 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; ">11</td><td colspan="1" rowspan="4;border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Block 3</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFCC00 ;; text-align:center; ">3</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFCC00 ;; text-align:center; ">1</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFCC00 ;; text-align:center; ">1</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">12</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFCC00 ;; text-align:center; ">4</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFCC00 ;; text-align:center; ">6</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFCC00 ;; text-align:center; ">2</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">13</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFCC00 ;; text-align:center; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFCC00 ;; text-align:center; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFCC00 ;; text-align:center; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">14</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFCC00 ;; text-align:center; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFCC00 ;; text-align:center; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFCC00 ;; text-align:center; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">15</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; 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 colspan="1" rowspan="4;border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Block 4</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FF0000 ;; text-align:center; ">1</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FF0000 ;; text-align:center; ">2</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FF0000 ;; text-align:center; ">1</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">17</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FF0000 ;; text-align:center; ">2</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FF0000 ;; text-align:center; ">7</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FF0000 ;; text-align:center; ">2</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">18</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FF0000 ;; text-align:center; ">4</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FF0000 ;; text-align:center; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FF0000 ;; text-align:center; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">19</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FF0000 ;; text-align:center; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FF0000 ;; text-align:center; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FF0000 ;; text-align:center; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">20</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; 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; ">21</td><td colspan="1" rowspan="4;border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:center; ">Resultat</td><td style=" color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:center; ">1</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:center; ">1</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:center; ">1</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">22</td><td style=" color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:center; ">2</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:center; ">2</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:center; ">2</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">23</td><td style=" color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:center; ">3</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:center; ">6</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:center; ">3</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">24</td><td style=" color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:center; ">4</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:center; ">7</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFF00 ;; text-align:center; ">4</td></tr></table><br/><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; "><tr style="background-color:#CCFF66; text-align:center; font-weight:bold; "><td>&nbsp;verbundene Zellen&nbsp;</td></tr><tr><td>A1:A4</td></tr><tr><td>A6:A9</td></tr><tr><td>A11:A14</td></tr><tr><td>A16:A19</td></tr><tr><td>A21:A24</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>B21,D21,F21</td><td>=MIN(B1:B19)</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr><td>B22:B24,D22:D24,F22:F24</td><td>{=WENN(MAX(B$1:B$19)=MAX(B$21:B21);"";MIN(WENN(B$1:B$19&gt;B21;B$1:B$19)))}</td><td>$B$22</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/><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/>

Wie du mit dem Fehler umgehen wolltest, war mir nicht ganz klar, daher ist es in diesem Beispiel nicht berücksichtigt.

Hilft dir das weiter?
VG

Berti_S
04.07.2014, 14:16
Hallo Santa,

leider klappt deine Lösung bei mir nicht.
Ich habe die Formeln wie angegeben benutzt, Excel liefert mir aber in den Matrixbereichen nur jeweils eine Null, keine Zahl.
Elegant ist es natürlich - leider bin ich bei Matrixformeln wie wohl 99% der Normal-User ein Totalausfall und verstehe nur ansatzweise deine Lösung.

Aber trotzdem vielen Dank !

Berti

Mc Santa
04.07.2014, 14:51
Kopiere die Formel und gebe sie in eine Zelle ein. Lösche die geschweiften Klammern vorne und hinten und drücke jetzt STRG + SHIFT + ENTER.
Die fertige Formel kannst du jetzt nach unten ziehen.

Klappt es so?
Ich kann dir später auch eine Datei hochladen.

VG

Berti_S
07.07.2014, 11:22
Ja, nun klappt es.

Ich hatte wohl bei der Eingabe schon alle drei Felder markiert gehabt.

Vielen Dank !