08.05.2014, 10:46
(Dieser Beitrag wurde zuletzt bearbeitet: 10.05.2014, 12:41 von Moderatorenteam.)
Hallo,
immer wieder tauchen solche oder ähnliche Fragen auf:
Dafür gibt es, wie so oft in Excel, mehrere Möglichkeiten.
Eine Variante ist die Nutzung einer Matrixformel die auch in der angehängten Beispielsdatei enthalten ist.
Die Ausgangstabelle
Eine der Zieltabellen (Tabelle2):
Die Formel in A9, die mit Strg-Shift-Enter abgeschlossen werden muss, kann dann nach rechts und nach unten kopiert werden.
Überwacht wird der Bereich von B2 bis B20 - das kann natürlich entsprechend angepasst werden.
Ab Excel 2007 kann der Zählenwenn-Teil in der Formel durch Funktion Wennfehler ersetzt werden.
Die Formel für A9 würde dann so aussehen:
=WENNFEHLER(INDEX(Tabelle1!A:A;KKLEINSTE(WENN(Tabelle1!$B$2:$B$20>0;ZEILE(Tabelle1!$2:$20));ZEILE(A1)));"")
Allerdings muss auch diese Formel mit Strg-Shift-Enter abgeschlossen werden.
Wenn nur Zeilen übernommen werden sollen die in Spalte B eine bestimmte Menge aufweisen dann reicht eine kleine Änderung der Formel:
=WENN(ZEILE(Tabelle1!1:1)>ZÄHLENWENN(Tabelle1!$B$2:$B$20;1);"";INDEX(Tabelle1!A:A;KKLEINSTE(WENN(Tabelle1!$B$2:$B$20=1;ZEILE(Tabelle1!$2:$20));ZEILE(A1))))
wobei auch in diesem Fall die Formel mit Strg-Shift-Enter abgeschlossen werden muss.
In der angehängten Beispielsdatei sind mehrere Varianten dargestellt:
Tabelle2: Es werden die Zeilen übernommen bei denen die Menge in Spalte B größer 0 ist
Tabelle3: Es werden die Zeilen übernommen bei denen die Menge in Spalte B gleich 1 ist
Tabelle4: Es werden die Zeilen übernommen bei denen die Menge zwischen einer frei definierbaren Ober- und Untergrenze liegen
Tabelle5: Es werden die Zeilen übernommen bei denen die Artikelnummer einer frei wählbaren Artikelnummer entspricht und die Menge einen frei wählbaren Wert übersteigt.
Allerdings sind Matrixformeln rechenintensiv, d.h. wenn der überwachende Bereich sehr groß ist, dann wäre die Nutzung einer VBA-Lösung ratsam.
Mit dieser von Thomas Ramel erstellten Datei habe ich sehr gute Erfahrungen gemacht.
Die notwendigen Anpassungen sind in der Datei erläutert und auch ohne VBA-Kenntnisse recht schnell vollzogen.
immer wieder tauchen solche oder ähnliche Fragen auf:
Zitat:Ich habe ein Tabellenblatt in dem in Spalte B eine Menge eingetragen wird. Wenn dort eine Zahl eingetragen wird dann soll die ganze Zeile in ein anderes Tabellenblatt kopiert werden.
Dafür gibt es, wie so oft in Excel, mehrere Möglichkeiten.
Eine Variante ist die Nutzung einer Matrixformel die auch in der angehängten Beispielsdatei enthalten ist.
Die Ausgangstabelle
| Arbeitsblatt mit dem Namen 'Tabelle1' | |||
| A | B | C | |
| 1 | Artikel | Menge | Preis |
| 2 | A1 | 6 | 10 |
| 3 | A2 | 20 | |
| 4 | A3 | 1 | 30 |
| 5 | A4 | 40 | |
| 6 | A5 | 50 | |
| 7 | A6 | 2 | 60 |
| 8 | A7 | 1 | 70 |
| 9 | A8 | 80 | |
| 10 | A9 | 3 | 90 |
| 11 | A10 | 5 | 100 |
| Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg |
Eine der Zieltabellen (Tabelle2):
| Arbeitsblatt mit dem Namen 'Tabelle2' | |||
| A | B | C | |
| 8 | Artikel | Menge | Preis |
| 9 | A1 | 6 | 10 |
| 10 | A3 | 1 | 30 |
| 11 | A6 | 2 | 60 |
| 12 | A7 | 1 | 70 |
| 13 | A9 | 3 | 90 |
| 14 | A10 | 5 | 100 |
| Zelle | Formel |
| A9 | {=WENN(ZEILE(Tabelle1!1:1)>ZÄHLENWENN(Tabelle1!$B$2:$B$20;">0");"";INDEX(Tabelle1!A:A;KKLEINSTE(WENN(Tabelle1!$B$2:$B$20>0;ZEILE(Tabelle1!$2:$20));ZEILE(A1))))} |
| Achtung, Matrixformel enthalten! |
| Die geschweiften Klammern{} werden nicht eingegeben. |
| Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine. |
| Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg |
Die Formel in A9, die mit Strg-Shift-Enter abgeschlossen werden muss, kann dann nach rechts und nach unten kopiert werden.
Überwacht wird der Bereich von B2 bis B20 - das kann natürlich entsprechend angepasst werden.
Ab Excel 2007 kann der Zählenwenn-Teil in der Formel durch Funktion Wennfehler ersetzt werden.
Die Formel für A9 würde dann so aussehen:
=WENNFEHLER(INDEX(Tabelle1!A:A;KKLEINSTE(WENN(Tabelle1!$B$2:$B$20>0;ZEILE(Tabelle1!$2:$20));ZEILE(A1)));"")
Allerdings muss auch diese Formel mit Strg-Shift-Enter abgeschlossen werden.
Wenn nur Zeilen übernommen werden sollen die in Spalte B eine bestimmte Menge aufweisen dann reicht eine kleine Änderung der Formel:
=WENN(ZEILE(Tabelle1!1:1)>ZÄHLENWENN(Tabelle1!$B$2:$B$20;1);"";INDEX(Tabelle1!A:A;KKLEINSTE(WENN(Tabelle1!$B$2:$B$20=1;ZEILE(Tabelle1!$2:$20));ZEILE(A1))))
wobei auch in diesem Fall die Formel mit Strg-Shift-Enter abgeschlossen werden muss.
In der angehängten Beispielsdatei sind mehrere Varianten dargestellt:
Tabelle2: Es werden die Zeilen übernommen bei denen die Menge in Spalte B größer 0 ist
Tabelle3: Es werden die Zeilen übernommen bei denen die Menge in Spalte B gleich 1 ist
Tabelle4: Es werden die Zeilen übernommen bei denen die Menge zwischen einer frei definierbaren Ober- und Untergrenze liegen
Tabelle5: Es werden die Zeilen übernommen bei denen die Artikelnummer einer frei wählbaren Artikelnummer entspricht und die Menge einen frei wählbaren Wert übersteigt.
Allerdings sind Matrixformeln rechenintensiv, d.h. wenn der überwachende Bereich sehr groß ist, dann wäre die Nutzung einer VBA-Lösung ratsam.
Mit dieser von Thomas Ramel erstellten Datei habe ich sehr gute Erfahrungen gemacht.
Die notwendigen Anpassungen sind in der Datei erläutert und auch ohne VBA-Kenntnisse recht schnell vollzogen.
Gruß
Peter
Peter