Ganze Zeile in neues Tabellenblatt kopieren, wenn 2 Bedingungen erfüllt sind (MS Excel 2010)?
Ich benötige eine Formel, die aus meinem Quelltabellenblatt (nennen wir es Tab1) in Spalte J prüft, ob dort ein bestimmter Text steht bspw. X und ob in Spalte U ein Wert >0 eingetragen ist. Wenn beides zutrifft, soll die ganze Zeile in das Zieltabellenblatt kopiert werden.
Folgendes habe ich bisher erarbeitet: =WENN(ZEILE('Tab1'!4:4)>ZÄHLENWENNS('Tab1'!$U:$U;">0";'Tab1'!$J:$J;"=X")< ZEILE(A1);"";INDEX('Tab1'!A:A; KKLEINSTE(WENN('Tab1'!$J$4:$J$99="X";ZEILE($4:$96));ZEILE(A1))))
Die Formel wirft mir zwar Ergebnisse im Zieltabellenblatt aus, jedoch kopiert sie nicht die ganze Zeile, sondern nur eine Zelle. Die Formel funktioniert auch nur, wenn ich mit ihr im Zieltabellenblatt bei A1 starte. Ich möchte jedoch erst bei A6 starten. Weiterhin wirft die Formel alles aus, was den Text X in Zeile J enthält und ignoriert, dabei die Prüfung auf einen Wert >0 in Zeile U.
Kann mir jemand sagen, was an meiner Formel falsch ist und mir eine korrigierte zur Verfügung stellen?
Vielen Dank im Voraus.
2 Antworten
Hallo :),
eine Komplette Formel oder eine Zeile per Formel zu kopieren ist nicht möglich, du kannst
1. einer Formel nicht sagen, das Sie etwas in eine andere Zelle schreiben soll
(Zirkelbezug)
2. kannst du mit einer Formel nicht sagen, wen etwas bestimmtes erfüllt wird, soll die Zelle übernommen werden.
Du kannst nur sagen das wenn etwas bestimmtes in einer Zelle steht, dann soll der Text angezeigt werden.
Aber, dann steht real immer noch die Formel in der Zelle und nicht der Wert den du haben möchtest.
Grüße,
iMPerFekTioN
Beide Werte abfragen würdest du mit der Formel UND()
Also =WENN(UND([Wahrheitswert1];[Wahrheitswert2]) Hier müssen BEIDE Werte wahr sein damit es weiter geht, ansonsten müssten du die ODER() Funktion nutzen, welche genau gleich aufgebaut ist aber wo eben nur ein Wert Wahr sein muss =)
Das andere Problem habe ich jetzt nicht wirklich verstanden, jedoch trotzdem eine kleine Hilfe:
=WENN(ZEILE('Tab1'!4:4)>ZÄHLENWENNS('Tab1'!$U:$U;">0";'Tab1'!$J:$J;"=X")<
ZEILE(A1);"";INDEX('Tab1'!A:A;
KKLEINSTE(WENN('Tab1'!$J$4:$J$99="X";ZEILE($4:$96));ZEILE(A1))))
Deine Formel hier will beide Werte mit der Funktion ZÄHLENWENN() abfragen.
mit ZÄHLENWENN() zählst du aber nur bestimmte Kriterien die Zustimmen in einem bestimmten Bereich, sprich so etwas z.B.
(ich weiß das hat nicht viel mit deiner Frage zu tun, jedoch wollte ich dir da einfach mal eine kleine "Excel Schulung" geben ;) )
Also, angenommen A1:A200 ist mit Buchstaben befüllt, in Zelle B1 willst du jetzt wissen wie oft der Buchstabe "A" auftaucht, also benutzt du die Funktion =ZÄHLENWENN()
=ZHÄLENWENN("A";A1:A200)
Jetzt wird angezeigt wie oft der Buchstabe "A" in der Spalte A1:A200 vorkommt;) du siehst also, in deiner Formel nur FAST richtig =D
Was du brauchst ist eine normale =WENN Funktion mit UND Abfrage und dann deine ZÄHLENWENN() Funktion ;)
Sprich so etwas in der Art:
=WENN(ZÄHLENWENN(Tabelle1!J:J;"X")+ZÄHLENWENN(Tabelle1!U:U;1)>=2;[Deine Zelle];"-")
Hoffe ich konnte helfen =D
Grüße,
iMPerFekTioN
Ich hätte hier sogar noch eine Formel die genau das macht was du möchtest:
=WENN(UND(ZÄHLENWENN(Tabelle1!J:J;"X")>=1;SUMMEWENN(Tabelle1!U:U;">0")>=1);"[Deine Zelle]";"")
Vielen Dank erstmal.
Ich habe die ZÄHLENWENNS-Funktion (nicht ZÄHLENWENN, sondern die mit S hinten dran. =D) benutzt, da mit dieser mehrere Kriterien abgefragt werden können, so wie ich den Aufbau der Formel und die Definition der Funktion verstehen. In Verbindung mit der INDEX-Funktion, dachte ich daher das hierüber eine Lösung meines Problems möglich sein müsste.
Ich habe versucht deine beiden Formelvorschläge in meine Tabelle einzubauen, jedoch verstehe ich leider nicht, was du mit [Deine Zelle] meinst. Ich habe dort die Zielzelle eingetragen bekomme bei der Formel
=WENN(ZÄHLENWENN(Tabelle1!J:J;"X")+ZÄHLENWENN(Tabelle1!U:U;1)>=2;[Deine Zelle];"-")
dann jedoch eine Fehlermeldung. Zudem verstehe ich an der Formel auch das Ende "-" nicht. Oo
Wenn ich die Formel
=WENN(UND(ZÄHLENWENN(Tabelle1!J:J;"X")>=1;SUMMEWENN(Tabelle1!U:U;">0")>=1);"[Deine Zelle]";"")
verwende, dann weiß ich auch wieder nicht, was bei [Deine Zelle] eingetragen werden soll und verstehe nicht, warum die Formel mit ;"") endet. Wenn ich in der Formel [Deine Zelle] mit der Angabe meiner Zielzelle A6 ersetze, erhalte ich einfach nur den Text A6 angezeigt, wenn die Entere bzw. Enter+Shift+Strg wählen.
Was mache ich denn falsch? =(
Versuch es so:
=WENN(UND(ZÄHLENWENN(Tabelle1!J:J;"X")>=1;SUMMEWENN(Tabelle1!U:U;">0")>=1);Tabelle1!A6;"")
Deine Zelle sollte dir sagen das du dort die Zelle einträgst die du nachher ausgegeben haben möchtest.
Das "") am Ende, beschreibt nur den Sonst-Wert der =WENN() Funktion.
Also wenn jetzt ein Kriterium nicht erfüllt wird, dann soll in der Zelle einfach nichts stehen.
Grüße,
iMPerFekTioN
Dankeschön, ich glaube jetzt ist es schon ganz nah dran. Zumindest verstehe ich jetzt schon viel mehr. =D
Dass Problem ist jetzt (glaube ich) der DANN_WERT, also der Wart, wenn beide Kriterien zutreffen. Ich glaube das ist es auch, was du meintest mit [Deine Zelle] meintest oder?
Ich kann ja da nicht direkt eine Zelle eintragen. Die Formel, soll ja die Daten aus der Zelle anzeigen auf dessen Zeile beide Ergebnisse zutreffen. Ich hoffe, es ist verständlich worauf ich hinaus will. =)
LG
Richtig verstanden =), nur komme ich jetzt etwas durcheinander :D
Kannst du keine Beispieldatei hoch laden und uns den Link zur Verfügung stellen? Dieses beschreiben übers Netz ist echt schwierig... :(
Jackie251 habe ich eine Beispieltabelle per E-Mail geschickt, da ich keine Ahnung habe, wo ich die hochladen könnte und das dann auch ohnehin erst von zuhause erledigen könnte.
Vorschläge, wo ich das hochladen kann?
DropBox, FileUpLoad gibts genug Seiten =), oder du schickst sie mir per E-Mail ;)
Schau mal in deine Nachrichten hier rein =)
imperfektion ist ja schon an der Lösung dran :-D
Ich würde in einer Hilftabelle die Zeilen mit ihrer Nummer Darstellen, welche Übernommen werden müssen
also sowas wie Und(J="X";U>0)*Zeile()
Danach die Zahlenliste um die Nullwerte kürzen (manuell durch Sortieren oder Doppelte Einträge löschen oder Automatisch siehe hier
http://www.excelformeln.de/formeln.html?welcher=160
das kann die erste Spalte der Zieltabelle sein.
danach braucht man in der Zieltabelle nur noch mittels Index die entsprechenden Zeilen der Basistabelle auslesen lassen
Danke für den Vorschlag. Die Arbeit über eine Hilfstabelle ist ein Vorschlag, den ich leider nicht umsetzen kann, da die Quelltabelle regelmäßig neue Zeilen erhält und Zeilen entfallen. Ich müsste also zusätzlich immer die Hilfstabelle pflegen, was mehr Aufwand als nutzen bringt. :/
Um den Sinn etwas näher zu erläutern.
Die Texte in Zeile J variieren. Ich werde also mehrere Zieltabellenblätter (sagen wir TabQ bis TabZ) haben, die jeweils die auf den Text angepassten Formeln enthalten. Die sich daraus ergebenden Lösungen, bilden Anlagen für Rechnung. Diese Anlagen, sollen zeitglich einen Serienbrief erzeugen.
Meine Hoffnung ist, dass ich mit der Formel langwieriges filtern, sortieren, kopieren und manchmal sogar Abtippen, vermeiden kann und nur noch die Quelltabelle und paar wenige Angaben in den Zieltabellenblättern (Bearbeitername, Zeitraum, Datum, Durchwahl, Verwendungszweck, etc.) pflegen muss und so jederzeit auf die Schnelle eine Rechnung im Serienbrief mit zugehöriger Anlage erzeugen kann.
Ich hoffe der Hintergrund hilft das Problem zu verstehen und zu lösen. =)
für die Auswertung reicht sogar eine Hilfsspalte, die man ja automatisch funktioniert
Selbst eine Hilftabelle würde man ja vollautomatisch in den Hintergrund legen, sonst wäre es ja kein Excel wenn man händisch eingreifen muss .
mal schauen, vieleicht kann ich dazu eine Tab. Machen wie ich mir das Denke
ich gehe zu ich hatte noch nicht detailiert nachgesehen, da der Strang doch recht lang ist.
Mir erschein die Vorgehendsweise jedoch recht schlecht für die Performance. Wenn man komplette Spalten übertragen will würde ich nicht in jeder Spalte einer Zeile Abfragen wollen "gehörtst du zu denen die ich übertragen soll? ja! => dann komm mit" sondern jede Zeile einmal prüfen und dann die Indexfunktion nur noch gezielt das übertragen was man braucht.
Ich möchte keine kompletten Spalten übertragen, sondern die kompletten Zeile, auf die beide Argumente zutreffen.
Ich würde ja gerne eine Beispieltabelle zur Verfügung stellen, damit man sieht, wie was aufgebaut ist, aber wie ? D=
antwort ist vorhin raus, nur falls es im Spamfilter gelandet ist ;-)
Vielen Dank, jetzt bin ich etwas schlauer.
Die Anzeige des Textes aus der Quelldatei würde mich schon weiterbringen. Das tut meine Formel ja auch bereits.
Allerdings prüft sie dabei nur auf den Text der Spalte J und nicht auf den Wert der Zeile U. Wie kann ich dieses Problem lösen?
Und wie schaffe ich es, dass ich mit der Formel erst in A6, statt in A1 meines Zieldatenblattes beginne?
Vielen Dank im Voraus.