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-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 Angebotsvergleich Formel, WENN MIN Funktion?

Guten Tag,

ich habe kann die Formel in dem Zellbereich B18 überhaupt nicht nachvollziehen. Ich verstehe schon, welchen Zweck die Formel haben soll, aber ich verstehe nicht wie die Formel aufgebaut ist bzw wie man eine solche Formel erstellt. Ich würde mich sehr freuen wenn mir das jemand erklären könnte.

Ich habe als Anhang 2x Dateien eingefügt. Einmal den Angebotsvergleich ohne Formel und einmal mit Formel. Ich hoffe ihr könnt meine Frage nachvollziehen..

Vielen Dank

Mit freundlichen Grüßen

Sirius

...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 kann ich ein Excel Dropdownmenü erstellen, welches mir Nebeninformationen anzeigt?

Guten morgen,

ich bräuchte ein Excel Dropdownmenü welches mich Zahlen auswählen lässt 1 - 10, jedoch beim öffnen der Dropdownauswahl hätte ich gerne eine Nebeninformation angezeigt. Zum beispiel

1 Montag 2 Dienstag

Nach dem ich 1 ausgewählt habe, soll in der Zelle, lediglich 1 da stehen, da möchte ich die Nebeninformation "Montag" nicht mehr sehen.

Ist dies möglich?

Viele Grüße

...zur Frage

EXCEL Formel gesucht. Für bestimmte Uhrzeiten sollen Stunden und Minuten in eine Dezimalzahl stunde umgewandelt werden?

Hallo. Ich bin Berufskraftfahrer und ich schreibe mit Excel täglich meine Stunden auf.

Nun möchte ich, dass Excel alles selbst rechnet, brauche aber eine passende Formel für...

Die Spalte G soll nur in den Zeiten von 20Uhr bis 24Uhr und von 4Uhr bis 6Uhr als Dezimalzahl angezeigt werden, die Spalte H nur in der Zeit zwischen 0uhr und 4Uhr

Wäre nett, wenn mir einer die Formel aufschreiben kann.

Danke.

...zur Frage

Was möchtest Du wissen?