39.2k Aufrufe
Gefragt in Tabellenkalkulation von rainbow11 Mitglied (107 Punkte)
Bearbeitet von halfstone
Hallo,

Tabelle:

1001.....x......KW43.......abc.......xy1.......3......cd1.......3
1002.............KW50.......abc.......xy2.......4.....................
1003.............KW44.......abc.......xy3.......5.......cd3.......5
1004.....x......kW42........abc.......xy1.......3.....................
1005.............kW42........abc.............................cd2.......4
1006.....x......KW43.......abc....... xy3.......5.......cd3.......5
1007.............KW45.......abc.............................cd1.......3
1008.............KW46.......abc.......xy2.......4.......cd2.......4
1009.............KW43.......abc.......xy3.......5.......cd3.......5

1. Frage:

Alle Zeilen, die nicht mit x markiert sind, sollen auf das nächste Tabellenblatt kopiert werden.

2. Frage

Alle Zeilen, in denen ein Eintrag in Spalte 4 steht, sollen auf das nächste Tabellenblatt kopiert werden

3. Frage

Alle Zeilen, mit dem Eintrag KW45 sollen in das nächste Tabellenblatt kopiert werden

Ich hoffe, dass dies eindeutig ist und bedanke mich für eure Hilfe!
Wenn möglich ohne vba, weil ich davon noch gar keine Ahnung habe!

Irene

14 Antworten

0 Punkte
Beantwortet von paul1 Experte (4.9k Punkte)
Hallo Irene,

Wenn ich die Frage richtig verstanden habe, eine Möglichkeit:

Sollte eine der 3 Bedingungen gegeben sein, werden die Daten in Tabelle 2 übernommen

Tabelle 1 A7 bis H15:

1001 x KW43 abc xy1 3 cd1 3
1002 KW50 abc xy2 4
1003 1 KW44 xy3 5 cd3 5
1004 x KW45 abc xy1 3
1005 KW42 abc cd2 4
1006 x KW43 abc xy3 5 cd3 5
1007 KW45 abc cd1 3
1008 KW46 abc xy2 4 cd2 4
1009 KW43 abc xy3 5 cd3 5

Formeln Tabelle 2 z.B.:
A1:=WENN(Tabelle1!B7="";Tabelle1!A7;WENN(Tabelle1!C7="KW45";Tabelle1!A7;WENN(Tabelle1!D7>0;Tabelle1!A7;"")))
B1:=WENN(A1="";"";Tabelle1!B7)
C1:=WENN(A1="";"";Tabelle1!C7)
D1:=WENN(A1="";"";Tabelle1!D7)
E1:=WENN(A1="";"";Tabelle1!E7)
F1:=WENN(A1="";"";Tabelle1!F7)
G1:=WENN(A1="";"";Tabelle1!G7)
H1:=WENN(A1="";"";Tabelle1!H7)

Alles runterkopieren bis?

Ich hoffe es geht mit der Frage konform und entspricht Deinen Vorstellungen


Schöne Grüße

Paul1
0 Punkte
Beantwortet von rainbow11 Mitglied (107 Punkte)
<danke schön, Paul,

das werde ich die nächsten Tage mal ausprobieren.

Es ist eher so, dass ich mehrere verschiedene Tabellen kreiieren möchte, die anschließend automatisch generiert werden.

Aber ich glaube, dass du mir schon viel weitergeholfen hast.

ich werde Bescheid geben, wie ich klar gekommen bin.

LG

Irene
0 Punkte
Beantwortet von rainbow11 Mitglied (107 Punkte)
Hallo,

jetzt hab ich nochmal drüber nachgedacht.

Pro Tabelle soll nur eine Bedingung erfüllt werden. Es soll aber keine Leerzeilen geben. Die Zeilen, die mit z.B. mit x markiert sind, sind Aufträge, die abgeschlossen sind, und die nicht mehr auftauchen sollen. Es sollte die ganze Zeile kopiert werden!

Das hier hab ich gefunden und damit kämpfe ich gerade!

http://www.excelformeln.de/formeln.html?welcher=28

Schönen Gruß

Irene
0 Punkte
Beantwortet von nighty Experte (6.6k Punkte)
hi all :-)

ein automatismus

gruss nighty

einzufuegen
alt+f11/projektexplorer/deineTabelle

quelle
worksheet(1)
eingabe von x in spalte a


ziel
worksheet(2)

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 And UCase(Worksheets(1).Cells(Target.Row, Target.Column)) = "X" Then
Application.EnableEvents = False
Worksheets(1).Rows(Target.Row).Copy
Worksheets(Worksheets(2).Name).Range("A" & Worksheets(2).Range("A" & Rows.Count).End(xlUp).Row + 1).PasteSpecial Paste:=xlValues, Operation:=xlNone
Worksheets(1).Rows(Target.Row).Delete Shift:=xlUp
Application.CutCopyMode = False
Application.EnableEvents = True
End If
End Sub
0 Punkte
Beantwortet von paul1 Experte (4.9k Punkte)
Hallo Irene,

Die Formel (Array-Formel) mit der Du kämpfst habe ich mir angesehen und kann mit ruhigem Gewissen sagen, dass
meine Lösungsvariante das geringere Übel ist.
Das einzige sind die dadurch entstehenden Leerzeilen, die ganz einfach super mit nachfolgenden Code wegzubringen sind:
In die entsprechende Tabelle einfügen und dann ausführen:

Sub Leerzeilenlöschen()
' Leerzeilen löschen einschließlich der Zeilen die entstehen wenn Zeilen am ende
' gelöscht werden, auch Leerzeilen in der Tabelle werden gelöscht
' von Wolf.W.Radzinski
' es wurden nicht alle Leerzeilen in einer Datei erkannt
' darum Ergänzung
On Error Resume Next
Dim r As Range
Dim anz As Long
Dim c_ges As Long
Dim col As New Collection
c_ges = ActiveSheet.UsedRange.Column + ActiveSheet.UsedRange.Columns.Count - 1
For Each r In ActiveSheet.UsedRange.EntireRow
anz = 0
' ******** Hajo.Ziplies@web.de
Dim I As Byte
Dim L As Byte
L = 0
For I = 1 To 20
If Cells(r.Row, I) <> "" Then
L = 1
Exit For
End If
Next I
If L = 0 Then col.Add r
' ******
' anz = r.SpecialCells(xlCellTypeBlanks).Count
' If anz >= c_ges Then col.Add r
Next
For Each r In col
r.Delete
Next
End Sub

Diese Makro (Hajo) stammt aus folgendem Link (super!), da ich sowas nicht selbst erstellen kann:

Makro Leerzeilen entfernen

Ich weiß Du möchtest das ganze lieber mit Formeln was schon geht allerdings in dem Fall mit Leerzeilen.
Die Formeln von A1 bis H1 kann man in einem runterkopieren, was mit Array-Formeln wahrscheinlich nicht geht.

Nachdem je Bedingung die Daten in einer Tabelle stehen sollen ist nur die Formel in A1 abzuändern auf eine Bedingung,
die Formeln von B1 bis H1 bleiben so wie sie sind.

Ganze Zeilen von einer Tabelle in eine andere auf einmal glaube ich ginge nur mit einem Makro, was Du aber nicht bevorzugst.

schöne Grüße


Paul1

@nigthy

Dein Makro hab ich getestet, aber es tat sich nichts,
vielleicht habe ich was falsch gemacht oder es gibt
einen Gedankenfehler (Es sollten Zeilen nach 3 Kriterien (siehe Frage!) in je eine
Tabelle kopiert werden, wenn möglich ohne Leerzeilen)
0 Punkte
Beantwortet von rainbow11 Mitglied (107 Punkte)
Hallo, Paul,

im Moment kann ich mich leider nicht damit beschäftigen, weil ich im Büro bin.

Meine drei Fragen sollen drei verschiedene Tabellen füllen, das müßten also jeweils unterschiedliche Formeln sein.

Zuerst will ich nur abfragen, welches die offenen Aufträge sind,

dann in einer gesonderten Tabelle z. B. die Aufträge der KW45

usw.

mit VB hab ich mich in letzter Zeit gar nicht beschäftigt, aber vielleicht sollte ich das doch noch tun.

Voraussichtlich kann ich aber erst am Montag hier weitermachen, weil ich am Wochenende ziemlich ausgelastet bin.

Danke für eure bisherige Hilfe

Irene
0 Punkte
Beantwortet von paul1 Experte (4.9k Punkte)
Hallo Irene,

Das wären die Formeln für die 3 verschiedenen Tabellen und sollte funktionieren.

Zur 1. Frage – z. B. in Tabelle 2:



A1: =WENN(Tabelle1!B7="";Tabelle1!A7;"")
B1:=WENN(A1="";"";Tabelle1!B7)
C1:=WENN(A1="";"";Tabelle1!C7)
D1:=WENN(A1="";"";Tabelle1!D7)
E1:=WENN(A1="";"";Tabelle1!E7)
F1:=WENN(A1="";"";Tabelle1!F7)
G1:=WENN(A1="";"";Tabelle1!G7)
H1:=WENN(A1="";"";Tabelle1!H7)

Alles runterkopieren bis?

Zur 2. Frage z.B. in Tabelle 3:

A1: =WENN(Tabelle1!D7>0;Tabelle1!A7;"")
B1:=WENN(A1="";"";Tabelle1!B7)
C1:=WENN(A1="";"";Tabelle1!C7)
D1:=WENN(A1="";"";Tabelle1!D7)
E1:=WENN(A1="";"";Tabelle1!E7)
F1:=WENN(A1="";"";Tabelle1!F7)
G1:=WENN(A1="";"";Tabelle1!G7)
H1:=WENN(A1="";"";Tabelle1!H7)

Alles runterkopieren bis?

Zur 3. Frage z.B. in Tabelle 4:

A1: =WENN(Tabelle1!C7="KW45";Tabelle1!A7; "")
B1:=WENN(A1="";"";Tabelle1!B7)
C1:=WENN(A1="";"";Tabelle1!C7)
D1:=WENN(A1="";"";Tabelle1!D7)
E1:=WENN(A1="";"";Tabelle1!E7)
F1:=WENN(A1="";"";Tabelle1!F7)
G1:=WENN(A1="";"";Tabelle1!G7)
H1:=WENN(A1="";"";Tabelle1!H7)

Alles runterkopieren bis?


In den Tabellen 2 bis 4 gibt es natürlich eine Menge Leerzeilen, die mit dem Dir bereits bekanntgegeben Code entfernbar sind.

In Excel 2003 mache ich es so:

>Code kopieren
>In der Tabelle unten (Reiter) z.B. Tabelle 2
>mit rechter Maustaste anklicken
>Code anzeigen
>es erscheint ein Fenster
>Code einfügen
>Schließen
>Schließen

>Tabelle 2 (ist jetzt ausgewählt)
>Extras
>Makro
>Makros....
>Ausführen

Damit sind die Leerzeilen gelöscht und die Zeilen untereinander aufgelistet.

Ich bin überzeugt, dass Du es hinbekommst.

schöne Grüße

Paul1
0 Punkte
Beantwortet von rainbow11 Mitglied (107 Punkte)
Hallo, Paul,

jetzt habe ich das probiert und es hat zwar im Prinzip geklappt, mich aber nicht zufrieden gestellt. Wenn die ganzen vermeintlich "leeren" Zellen weggelöscht werden, kann ich dies nur einmal machen. Die Aufträge werden aber fortlaufend "fertig" gestellt und dürfen dann zum Beispiel nicht mehr in der "offene Aufträge" Tabelle erscheinen und müssen da automatisch erscheinen bzw. verschwinden, sobald die Bedingung erfüllt bzw. nicht erfüllt ist!

Die Lösung hab ich hier gefunden:

http://www.excelformeln.de/formeln.html?welcher=28

{=INDEX(A:A;KKLEINSTE(WENN(B$1:B$99="x";ZEILE($1:$99));1))}

{=INDEX(A:A;KKLEINSTE(WENN(B$1:B$99="x";ZEILE($1:$99));2))}

umgewandelt für meine Bedürfnisse sieht das dann so aus:

{=INDEX(Uebernahme!B:B;KKLEINSTE(WENN(Uebernahme!C$3:C$2010<>"x";ZEILE($3:$2010));1))}

{=INDEX(Uebernahme!B:B;KKLEINSTE(WENN(Uebernahme!C$3:C$2010<>"x";ZEILE($3:$2010));2))} und dies nach unten bzw. nach rechts kopieren, sooft ich es brauche!

Das bedeutet zwar, dass ich praktisch jedes einzelne Feld überarbeiten muss, aber das Ergebnis ist absolut klasse! Sobald ein Auftrag als erledigt markiert wird, verschwindet er aus der Liste, ohne dass ich da nochmal manuell eingreifen muss!

Auf jeden Fall vielen Dank für eure Hilfe, das Makro hat übrigens sauber funktioniert, es wurden tatsächlich alle Leerzeilen entfernt und ich bin wieder etwas schlauer, was Makros betrifft!

Danke schön

Irene
0 Punkte
Beantwortet von

Hallo zusammen,

der Thread ist etwas älter, aber ich habe ein Problem die genau auf diese Formel zutrifft. Beim nach unten ziehen (wie oben beschrieben) vergrößert sich die Zahl 1 nicht um 1 wie es bei Excel üblich ist, auch wenn ich mehrere Zeilen per Hand ändere das Excel die Erhöhung um 1 erkennen würde. Kann mir jemand helfen?

{=INDEX(Tabelle1!A:A;KKLEINSTE(WENN(Tabelle1!$E$3:$E$2010=$B$2;ZEILE(Tabelle1!$3:$2010));1))}

nächste Zeilen sollten dann wie folgt sein, Excel kopiert sie einfach nur stumpf!

{=INDEX(Tabelle1!A:A;KKLEINSTE(WENN(Tabelle1!$E$3:$E$2010=$B$2;ZEILE(Tabelle1!$3:$2010));2))}

{=INDEX(Tabelle1!A:A;KKLEINSTE(WENN(Tabelle1!$E$3:$E$2010=$B$2;ZEILE(Tabelle1!$3:$2010));3))}

{=INDEX(Tabelle1!A:A;KKLEINSTE(WENN(Tabelle1!$E$3:$E$2010=$B$2;ZEILE(Tabelle1!$3:$2010));4))}

Vielen Dank in Voraus für jegliche Hilfe.

P@

 

0 Punkte
Beantwortet von
Hallo P,

ersetze die Zahl 1 durch die Formel Zeile() vorausgesetzt die Formel steht in Zeile 1, ansonsten musst du noch die Anzahl der darüberliegenden Zeilen abziehen. Steht die Formel z.B. in Zeile 4 ersetzt du die Zahl 1 also durch Zeile()-3

Gruß Mr. K.
...