Mit Excel nach Kriterien in eine andere Tabelle filtern?

3 Antworten

Vom Fragesteller als hilfreich ausgezeichnet

so mit einem einfachen Filter geht das meines Wissens nicht. Aber mit ein paar zusätzlichen Formelspalten in entweder einem Zwischenblatt oder in ausblendbaren Spalten Deines Ergebnisblatts geht das.
zumindest irgendwie.
Ich muss erst mal wissen, ob Du im Quellblatt zusätzliche Spalten generieren kannst/darfst. das wäre nämlich das einfachste. Ausserdem weiß ich nicht: werden die neuen Daten mit der höheren Rechnungsnummer immer oben eingefügt und die vorhandenen nach unten geschoben oder ist das ein falscher Eindruck?
Ich geh mal von beidem aus. Wenn nicht, musst Du dich melden, bin aber erst Montag wieder verfügbar.
Da die 4 vielfach vorkommt und die Rechnungsnummer ohne was anderes kein Unterscheidungskriterium ist, musst Du sie verknüpfen (meinetwegen in Spalte Z, nur, damit wir von was Konktetem reden können, kann auch jede andere sein) mit

=D2* 10000000+C2 (wenn beides Zahlen sind, sonst =Wert(C2)+Wert(D2)* 10000000

Die Formel muss allerdings bei jeder Neueintragszeile hochkopiert werden. Oder zum Abfragezeitraum gesammelt. (würde unten angehängt, könnte man das auf Vorrat machen!)

in Tab2 schreibst Du nun in Zeile 2 (ich nehme an, Du brauchst die 1.Zeile wieder für Überschriften), zB in Spalte A:

=KGrösste(Tab1!Z:Z;Zeile()-1)

runterkopieren. das bringt Dir alle Zahlen mit 4 (40 millionen + Rechnungsnummer) nach oben.
Wenn Du die Rechnungsnummer nochmals separat in Tab2 brauchst, legst Du eine Spalte (zB B:B) an mit
=A2-40000000. Aber Du ersiehst das ja aus Spalte A schon, ist m.E überflüssig.
Ebenso die Mahnstufe, das ist ja die erste Ziffer in A:A. Wenn doch nötig:

=Ganzzahl(A2/10000000)

Fehlen noch der Rabatt und der Betrag:

=Index(Tab1! E:E;Vergleich(A2-40000000;Tab1!$C:$C;0);1) runterkopieren

eine Spalte nach rechts kopiert erscheint der Betrag. Fertig.

Pferdefuß: Oft sind diese Zahlen nicht als Zahl, sondern als Text gespeichert. Texte werden nicht mit + addiert, sondern mit ="Text1"&"Text2" aneinandergehängt. Bei Zellbezügen aber ohne Anführungszeichen.
Da Du aber in Spalte Z:Z und in Tab2! nur Zahlen hast (und wegen KGrösste() haben musst!), musst Du zum Suchen die Zahl wieder zur Textnummer machen mit
=Index(Tab1! E:E;Vergleich(rechts(A2;7);Tab1!$C:$C;0);1) . Könnte sogar sein, dass Du noch weiter gehen musst:
=Index(Tab1! E:E;Vergleich(Fest(rechts(A2;7);0);Tab1!$C:$C;0);1) oder
=Index(Tab1! E:E;Vergleich(Text(rechts(A2;7);"0");Tab1!$C:$C;0);1)
musst Du ausprobieren, kann ich jetzt nicht bei dieser Trockenübung!

zum Verständnis von index/Vergleich: Vergleich(Was,Wo;Typ) sucht im Bereich C:C nach 1234567 und meldet die n-te Zelle als ersten Fund, wobei wegen C:C die n-te Zelle = die n-te Zeile ist. Index geht nun in der ersten (hier einzigen) Spalte des Bereichs E:E bzw. F:F zu dieser nten Zeile (in die 1.=einzige Spalte, deshalb ;1) ) und meldet den dort gefundenen Wert zurück.

mir ist inzwischen noch eine elegantere Lösung eingefallen. vom Prinzip her das Gleiche, aber ohne Formelspalte in Tab1, die Verknüpfung erfolgt direkt in Tab2, Sp.A. Formel in A2:
=indirekt("Tab1!D"&Zeile())* 10^7+indirekt("Tab1!C"&Zeile())

[ bzw -wenn die Zahlen Labels sind-(scheint gar nicht mehr nötig zu sein!):
=Wert(indirekt("Tab1!D"&Zeile()))* 10^7+Wert(indirekt("Tab1!C"&Zeile())) ]

Die KGrösste-Formel wandert nun nach Sp.B, Formel in B2:
=KGrösste(A:A;Zeile()-1)

optional zusätzlich in C2 die isolierte Rechnungsnummer:
=B2-4* 10^7

und ebenfalls optional in D2 die isolierte Mahnstufe:
=Ganzzahl(B2/10^7)

Rabatt:
=Index(Tab1! E:E;Vergleich($B2-4* 10^7;Tab1!$C:$C;0);1)
(da hat in der Hauptantwort oben vor A2 ein $ gefehlt! Liefert 0, wenn Zelle leer, also kein Rabatt.)
und nach rechts kopieren ergibt:
=Index(Tab1! F:F;Vergleich($B2-4* 10^7;Tab1!$C:$C;0);1)

alles runterkopieren, soweit nötig.

Beim Erreichen von Mahnstufe 3 werden die Zahlen in Sp.C negativ und die indexformeln ergeben Fehler, aber Mahnstufe 3 interessiert Dich ja nicht.
Also runterkopieren, bis die Zahlen negativ / zu Fehlern werden.

Habs diesmal getestet, bei mir läufts so. Bei Fragen melde Dich!

0
@Iamiam

Hmmm, das hört sich gut an. Darauf bin ich nicht gekommen. Ich muss das mal ausprobieren, dann melde ich mich wieder.

0
@vamost

Warnung zum Datenschutz:
für Ausdrucke ist das ok.
Aber wer die Datei hat, kann auch jederzeit die Namen einsehen!
Selbst die Auslagerung in eine andere Datei nützt nichts: die Formel lässt sich ohne Probleme anpassen!
Sicherheit böte nur eine Zusatzdatei, in der das Blatt mit Passwort geschützt ist und die Formeln ausgeblendet (verborgen), oder noch besser: das ganze Blatt zusätzlich ausgeblendet mit Passwort) sodass niemand weiß, auf welche Daten diese Zwischendatei zugreift. Wenn Darstellung dann in einem anderen Blatt dieser Datei mit =Zwischenblatt!B2 (zur Irreführung in A2) etc. übernommen wird, kann wohl niemand mehr auf die Originaldaten zugreifen.
Ist auch das Enddarstellungsblatt geschützt und Formeln ausgeblendet, ist nochmals eine Sicherungsstufe eingebaut.
Aktualisieren müsste dann mit F9 gehen, hab das alles aber jetzt nicht ausprobiert, wollte nur das Problem ansprechen und Lösungen anregen! (vielleicht ginge das auch in der Originaldatei, wenn Stammblatt und Übernahmeblatt beide mit Passwort ausgeblendet werden und nur zur Aktualisierung und nur vorübergehend das Stammblatt sichtbar gemacht, danach wieder ausgeblendet wird.)

0
@Iamiam

jetzt hat das ganze System wieder viel zu lange gebraucht, ich konnte nicht mehr korrigieren:
=Zwischenblatt!B2 (zur Irreführung in A2) etc. ist verkehrt, sondern
=Zwischenblatt!C2 , sonst wird die ganze Idee der Anlage offenbar, wenn die Zahl in B2 oder A2 offenbart wird. Und das könnte dann doch einen Hacker auf die Spur führen (wobei ich nicht weiß, was da trotzdem noch möglich ist, ich bin keiner...)

0

Innerhalb eines Tabellenblattes kann man dafür den Spezialfilter nutzen (bei XL 2007 heißt das Filtern Erweitert...). Meines Wissens funktioniert es aber nicht, die Ausgabe automatisch auf ein anderes Blatt zu senden. Dafür könnte man aber ein kleines Makro programmieren.

LG Hannes

Genau mit dem Makro habe ich es probiert funktioniert. Warum ich nicht sofort darauf gekommen bin. Ich probiere noch den Lösungsvorschlag von lamiam aus. Dann melde ich mich wieder. Trotzdem vielen Dank für die Tipps.

0

Denke dass das auch Arbeitsblattuebergreifend mit den Formeln von SVerweis() bzw. WVerweis() gehen wird. Habe aber jetzt keine Zeit mir das anzusehen.

Schau Dir mal anstatt ..Verweis die Formeln mit index(..;Vergleich(Was;wo;Typ);1) an, da bist Du besser bedient: Kannst nach rechts kopieren, ohne einen Spaltenversatz ändern zu müssen, kannst in einer Spalte links von der Leitspalte suchen, und Du kannst am Zielort auch den Wert drunter, drüber, rechts oder links davon abfragen, auch mehrere dazwischen!

0

Also mit sverweis, kann man das auch machen, aber hierbei will ich mich auf keine Quelle beziehen und nichts aus einer Quelle rausziehen. Sondern nur filtern und in die andere Tabelle ziehen. Ich habe es auch jetzt mit Makros getestet. Mit den Makros funktioniert es einwandfrei.

0

Wie kopiere ich in Excel eine horizontale Spaltenreihe vertikal in Zeilen?

Ich versuche mal meine Frage genauer auszudrücken. Ich habe in Excel zwei Arbeitsblätter (Tabelle1 und Tabelle2). In Tabelle1 habe ich in i34folgende Formel =zählenwenn(i2:i31;"x"). Diese Formel habe ich horizontal bis z34 erweitert. Das Ergebniss aus den einzelnen Zählungen möchte ich gern in Tabelle2 vertikal e5:e35 kopieren.

Aber wenn ich in Tabelle1 die Spalte von i34:z34 kopiere und dann in Tabelle2 den Bereich e5:e35 markiere --> einfüge... Fügt er zwar die Formel ein aber der Bezug fehlt.

Habe es auch schon so ausprobiert dass ich in Tabelle2 in e5 =Tabellle1!i34 eingegeben habe und dann vertikal erweitert habe. Aber dann nimmt er die Daten aus i35 und nicht j34.....

Hoffe jemand erkennt mein Problem und hat eine Lösung für mich.

Bei Unverständlichkeit, einfach schreiben.

...zur Frage

EXCEL: Wenn-Verschachtelung-Begrenzung umgehen?

Hallo ich habe eine Tabelle, die mir per SVERWEIS einen Wert ausgeben soll.

Jedoch steht der Wert in einer von 10 möglichen Tabellen!

Ich habe das im Groben so gelöst:

=Wenn der Wert in Tabelle1 steht, suche Wert in Tabelle1; wenn der Wert in Tabelle2 steht, suche Wert in Tabelle2;...

Da die Wennverschachtelung hierbei aber an ihre Grenzen stößt, suche ich nach einer alternativen Lösung.

Hat jemand eine Idee?

...zur Frage

Excel-Formel mit Bezug zum vorausstehenden Tabellenblatt?

Hallo,

ich habe folgendes Problem in Excel: In Tabellenblatt 2 steht die Formel =Tabelle1!B4+Tabelle1!E4+.. Diese kopiere ich nun in Tabellenblatt 3, muss jedoch die Formel manuell zu =Tabelle2!B4+Tabelle2!E4+.. anpassen. In Blatt 4 zu =Tabelle3!B4+Tabelle3!E4+.. usw. Was muss ich tun, damit sich die Formel beim kopieren automatisch auf das vorausstehende Tabellenblatt bezieht?

Danke im Voraus!

...zur Frage

Excel mehrere Zellen Verweisen?

Ich habe zwei Tabellenblätter im folgenden Tabelle1 und Tabelle2 Im ersten Tabellenblatt habe ich ein Formular erstellt, welches durch ein Drop-Down Menü in "Tablle1!C4" dynamisch sein soll. Das bedeutet wenn ich in dem Drop-Down Menü die Auswahl ändere, sollen auch die Inhalte in dem Formular geändert werden. Insgesamt für 367 Namen.

in Tabelle2 habe ich daher 367 mal dieses Formular untereinander kopiert und immer mit den Namen in Spalte C Am Tabellenkopf. Also in "C1", "C84", "C168", etc.

Wie kann ich die beiden Tabellenblätter so verknüpfen, dass ich nur in Tabelle1 arbeiten muss, und die Daten in Tabelle2 geschrieben, gespeichert und von dort abgerufen werden.

Ich hoffe es war verständlich. Danke für eure Hilfe

...zur Frage

Wie erstelle ich doppelseitige Excel- Lernkarten?

Hallo zusammen, ich bin neu hier und wäre um jede Hilfe von euch dankbar! :)

Ich möchte mit Excel ( Office 365) doppelseitige Lernkarten erstellen.

Gibt es eine Formel, damit Tabelle1 automatisch in Tabelle2 übernommen wird? (ohne dass ich alles einzeln kopieren muss)

Danke im voraus!

...zur Frage

Excel: Formel in der Kopfzeile, wie mach ich das?

Guten Morgen,

ich möchte, dass die Kopfzeile aus Tabelle2 auf Zeilen in Tabelle1 zugreift. Es sollen die Zeilen B5 und A5 untereinander darstellen. Es betrifft die Links oben in der Kopfzeile und direkt darunter, der Rest in der Kopfzeile soll/darf nicht beeinflusst werden. Ich weiß schon, dass man keine Formeln in der Kopfzeile setzen kann und mit Visual Basic agieren muss.

Vielen Dank!

...zur Frage

Was möchtest Du wissen?