PDA

Vollständige Version anzeigen : per Formel Lücken aus Tabelle entfernen


Ibanez#
20.07.2014, 21:49
Hallo zusammen,

gibt es eine Möglichkeit, eine Tabelle, in der leere Zeilen existieren, per Formel in eine lückenlose Tabelle umzuwandeln? Die alte Tabelle will ich behalten, ich will die leeren Zeilen NICHT löschen!

Pseudocode für die Formel:
0) Starte oben
1) Suche nächstes nicht-leeres Feld in der alten Tabelle
2) kopiere dieses Feld in das (von oben) erste leere Feld der neuen Tabelle
3) gehe zu 1) wenn Tabellenende nicht erreicht

Ist das mit Formeln zu machen oder brauche ich da VBA?
Habe im Anhang versucht zu erklären was ich möchte ;)

Danke schonmal ;)

Mc Santa
20.07.2014, 23:05
Hallo,

hier ist eine Formellösung. Sie beinhaltet Arrays, beachte dazu die Hinweise am Ende. Außerdem musst du wissen, dass diese Formeln sehr langsam sind, wenn du sie auf große Tabellen anwendest.

<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:117px;" /><col style="width:128px;" /></colgroup><tr style="background-color:#99CCFF; text-align:center; font-weight:bold; "><td>&nbsp;</td><td>B</td><td>C</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:left; "><b>alte Tabelle</b></td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; "><b>neue Tabelle</b></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:left; ">a</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">a</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:left; ">b</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">b</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; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">c</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:left; ">c</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">d</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; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">e</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; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">f</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:left; ">d</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">g</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:left; ">e</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">h</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:left; ">f</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">i</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; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">j</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:left; ">g</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">k</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; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</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:left; ">h</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&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:left; ">i</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&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; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&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:left; ">j</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&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; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&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:left; ">k</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&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>C3:C13</td><td>{=INDEX($B$3:$B$20;KKLEINSTE(WENN($B$3:$B$20&lt;&gt;"";ZEILE($B$3:$B$20)-ZEILE($B$3)+1;1E+300);ZEILE(A1)))}</td><td>$C$3</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/>

Hilft dir das weiter?
Viele Grüße

Ibanez#
21.07.2014, 11:08
Hallo ;)

ja, danke das wäre perfekt, nur wie kann ich die Formel dann "runterziehen", wenn ich eigentlich jedesmal Strg+Umschalt+Enter drücken müsste?

Ich verstehe auch nicht ganz, was in der Formel passiert, das interessiert mich noch ;)

{=INDEX($B$3:$B$20;KKLEINSTE(WENN($B$3:$B$20<>"";ZEILE($B$3:$B$20)-ZEILE($B$3)+1;1E+300);ZEILE(A1)))}

INDEX ist klar, gibt den Wert eines Elements einer Matrix zurück, in unserem Fall das nächste nichtleere Feld, also braucht INDEX als erstes die Matrix und als zweites die Zeile, die mit KKLEINSTE berechnet wird.
Aber das Argument von KKLEINSTE verstehe ich nicht, KKLEINSTE gibt den k-kleinsten Wert aus einer Matrix zurück? Inwiefern sucht das mir die nächste, nicht leere Zeile?

Vielen Dank! ;)

Mc Santa
21.07.2014, 11:20
Hallo,

du gibst die Formel in das erste Feld ein (mit STRG + SHIFT + ENTER) und danach kannst du sie runterziehen. Excel erkennt die Matrixformel und ergänzt sie auch beim Ziehen.

Du hast die Formel bisher gut verstanden. Das erste Argument von kkleinste lautet:
WENN($B$3:$B$20<>"";ZEILE($B$3:$B$20)-ZEILE($B$3)+1;1E+300)
Das heißt soviel wie: gehe jede Zelle in B3:B20 durch, und wenn sie nicht leer ist, dan gib mir die Zeile aus dieser Zelle zurück. (Zeile(B3:B20)). Anschließend erfolgt eine Korrektur, der Zeile, da ich ja in Zeile 3 nicht 3 möchte, sondern eigentlich eine 1. Sonst gib mir eine große Zahl zurück (1e+300 heißt 1 mit 300 nullen --> 1.000.000.000.000..............)

Das ist mehrere Zellen nacheinander ansprechen und auswerten kann, ist die Besonderheit der Matrixformel.

der zweite Teil der Formel heißt einfach Zeile(a1), das ergibt einfach 1. Und wenn du die Formel dann ziehst, ergibt es nacheinadner 2, 3, 4, usw.

Hilft dir das weiter?
Viele Grüße

xlph
21.07.2014, 11:22
Hallo Ibanez,

falls du eine VBA-Lösung suchst, hast du hier eine mögliche Variante:

Public Sub SpalteKopierenOhneLeerzellen_xlph()
With Tabelle1 ' Tabelle1 = Quelle
With .Columns(1)
.ColumnDifferences(.Find(Empty)).Copy Tabelle2.Cells(1, 1) ' Kopiere nach Tabelle2 = Ziel
End With
End With
End Sub

Ibanez#
21.07.2014, 13:22
Ok, funktioniert und habe ich verstanden ;)

In VBA werde ich mich bei Gelegenheit mal einlesen und dann deine Lösung anschauen ;)

Nochmal vielen Dank! ;)