Wenn-Index-Vergleich-Formel

Computer: Software & Programmierung Office Software
#1

Ich habe eine vorhande Tabelle mit Daten und möchte aus dieser Datei nur bestimmte Zeilen in eine neue Tabelle schreiben, könnte man mit SVerweis machen, besser ist es aber denke ich mit der Formel Index und Vergleich, weil die Tabelle nicht sortiert ist. Außerdem benötigt man dann noch den Wenn-Verweis.
Die Tabelle:
Datum Abteilung Artikel
05 06 Einkauf 2 TE GXM 2
03 06 Einkauf 2 UD DKH 5
09 06 Einkauf 1 LM ETR 6

Nun habe ich aber folgende Probleme:
In der 1. Spalte ist ein Datum enthalten, was Excel nicht als Datum findet - das ist ein Problem, weil es als Format Standard gespeichert ist, z. B. 05 06 (bedeutet Mai 2006) oder 03 06 (Mai 2006).
Das nächste Problem liegt schon in der 2. Spalte:
Da ist die Abteilung eingetragen, z. B. Logistik 5, Logistik 7,Einkauf 1 RT, Einkauf 1 LM, Einkauf 2 TE, Einkauf 2 UD, Einkauf 4 SL.
In den nächsten Spalten sind weitere Daten vorhanden.
Nun wird es kompliziert.
Ich möchte aber nur die Daten vom Einkauf 2 haben (also Abteilung Einkauf 2 TE, Einkauf 2 UD) und zwar von der 1. Spalte nur die Daten, die vom heutigen Datum aus betrachtet 6 Monate zurückliegen.

Wäre schön, wenn mir da ein Excel-Profi helfen könnte.
Vielen Dank schon mal im voraus.

#2

Hallo PColada,

setz doch einen Autofilter über die Tabelle.

Dann kannst Du nach Einkauf 2 filtern und in der ersten Spalte nach kleiner/größer …

Das Ganze dann markieren und in eine neu Tabelle droppen.

Viel Erfolg
Ullrich Sander

#3

Hallo

Ich habe eine vorhande Tabelle mit Daten und möchte aus dieser
Datei nur bestimmte Zeilen in eine neue Tabelle schreiben,
könnte man mit SVerweis machen, besser ist es aber denke ich
mit der Formel Index und Vergleich, weil die Tabelle nicht
sortiert ist. Außerdem benötigt man dann noch den
Wenn-Verweis.

Würde sicherlich gehen, allerdings kommst Du glaube ich um Matrixformeln nicht herum und damit kenne ich mich leider überhaupt nicht aus.

Die Tabelle:
Datum Abteilung Artikel
05 06 Einkauf 2 TE GXM 2
03 06 Einkauf 2 UD DKH 5
09 06 Einkauf 1 LM ETR 6

Nun habe ich aber folgende Probleme:
In der 1. Spalte ist ein Datum enthalten, was Excel nicht als
Datum findet - das ist ein Problem, weil es als Format
Standard gespeichert ist, z. B. 05 06 (bedeutet Mai 2006) oder
03 06 (Mai 2006).
Das nächste Problem liegt schon in der 2. Spalte:
Da ist die Abteilung eingetragen, z. B. Logistik 5, Logistik
7,Einkauf 1 RT, Einkauf 1 LM, Einkauf 2 TE, Einkauf 2 UD,
Einkauf 4 SL.
In den nächsten Spalten sind weitere Daten vorhanden.
Nun wird es kompliziert.
Ich möchte aber nur die Daten vom Einkauf 2 haben (also
Abteilung Einkauf 2 TE, Einkauf 2 UD) und zwar von der 1.
Spalte nur die Daten, die vom heutigen Datum aus betrachtet 6
Monate zurückliegen.

Ich würde, wenn es sich um eine einmalige Sache handelt, die Bedingungen in einer Hilfsspalte abprüfen und dann die gefilterte Tabelle kopieren.

Beispiel:

\*\*\* Tabelle: 'Tabelle1' \*\*\*
 A B C D
 1 Datum Abteilung Artikel Prüfung
 2 05 06 Einkauf 2 TE GXM 2 Wahr
 3 03 06 Einkauf 2 UD DKH 5 Falsch
 4 09 06 Einkauf 1 LM ETR 6 Falsch

\*\*\* benutzte Formeln \*\*\* 
D2: =UND(DATEDIF(DATUM(WENN(WERT(RECHTS(A2;2))\>JAHR(HEUTE())-2000;"19";"20")&RECHTS(A2;2);LINKS(A2;2);1);HEUTE();"m")JAHR(HEUTE())-2000;"19";"20")&RECHTS(A3;2);LINKS(A3;2);1);HEUTE();"m")JAHR(HEUTE())-2000;"19";"20")&RECHTS(A4;2);LINKS(A4;2);1);HEUTE();"m")
( **evtl. ersetzen durch =6, für den Fall, dass nur die Zeilen gemeint sind, die genau im 6 Monate zurückliegenden Monat liegen und nicht innerhalb des 6-Monatezeitraums.)  
  
Nun einen Autofilter setzen und nur die Zeilen anzeigen lassen, bei denen in der Spalte Prüfung der Wert "Wahr" steht. Die angezeigten Zeilen dann kopieren.  
  


> Wäre schön, wenn mir da ein Excel-Profi helfen könnte.  
> Vielen Dank schon mal im voraus.

  
Hoffe die Lösung reicht erst mal, ansonsten musst Du doch noch auf Lösungsvorschläge anderer Experten warten.  
  
Gruss, tester**
#4

Hi Pcolada,

In der 1. Spalte ist ein Datum enthalten, was Excel nicht als
Datum findet - das ist ein Problem, weil es als Format
Standard gespeichert ist, z. B. 05 06 (bedeutet Mai 2006) oder
03 06 (Mai 2006).

Format benutzdefiniert: TT.MM

Das nächste Problem liegt schon in der 2. Spalte:
Da ist die Abteilung eingetragen, z. B. Logistik 5, Logistik
7,Einkauf 1 RT, Einkauf 1 LM, Einkauf 2 TE, Einkauf 2 UD,
Einkauf 4 SL.
Ich möchte aber nur die Daten vom Einkauf 2 haben (also
Abteilung Einkauf 2 TE, Einkauf 2 UD) und zwar von der 1.
Spalte nur die Daten, die vom heutigen Datum aus betrachtet 6
Monate zurückliegen.

Tabellenblattname: Tabelle1
 A B C 
1 Dat Abt Art 
2 05.06 Einkauf 2 TE GXM 2 
3 03.06 Einkauf 2 DU DXH 4 
4 09.06 Einkauf 1 LM ETR 6 
5 03.01 Verkauf gdd 
6 01.04 Einkauf 2 DU völf 
7 02.04 Einkauf 2 DU fhjfh 



Tabellenblattname: Tabelle2
 A B C 
1 Dat Abt Art 
2 05.06 Einkauf 2 TE GXM 2 
3 03.06 Einkauf 2 DU DXH 4 
4
5
6
7 02.04 Einkauf 2 DU fhjfh 

Benutzte Formeln:
A1: =Tabelle1!A1
A2: =WENN(UND(LINKS(Tabelle1!$B2;9)="Einkauf 2";DATEDIF(Tabelle1!$A2;HEUTE();"m")
Gruß
Reinhard
#5

hi,
will auch mal eine hübsche Formel posten :wink:
=SUMMENPRODUKT((DATUM(„20“&RECHTS(A2:A8;2);LINKS(A2:A8;2);1)>DATUM(JAHR(HEUTE());MONAT(HEUTE())-6;1))*(RECHTS(B2:B8;9)=„Einkauf 2“))
cu Micha

#6

upps,
ich hatte angenommen du wolltest die Dinger nur zählen
wieder mal falsch oder nicht vollständig gelesen :frowning:
cu Micha

#7

Hallo Ullrich,
danke für Deine Antwort.
Aber Autofilter setzten wollte ich eben nicht, ich möchte keine Handarbeit machen, deshalb suche ich nach Formeln.

Gruß
PColada

[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]

#8

Hallo PColada,

Nun habe ich aber folgende Probleme:
In der 1. Spalte ist ein Datum enthalten, was Excel nicht als
Datum findet - das ist ein Problem, weil es als Format
Standard gespeichert ist, z. B. 05 06 (bedeutet Mai 2006) oder
03 06 (Mai 2006).

wenn das Datum in A1 steht und der Tag keine Rolle spielt (vermutlich), gib bitte z. B. in B1 folgende Formel ein:

=1*(„01“&"-"&LINKS(A1;2)&"-"&RECHTS(A1;2))

Die Formel gibt dann den 1. des angegebenen Monats zurück, soll ein anderer Tag ausgegeben werden, ändere die „01“ in der Klammer entsprechend.

Wenn du das Datum auch als Datum zeigen möchtest, muss die Zelle B1 als Datum formatiert sein, willst du lediglich mit dem Datum rechnen, ist das nicht erforderlich.

Gruß
Maria

#9

Hallo Maria,
vielen Dank für Deine Antwort.
Aber es funktionrt bei mir leider nicht.
Wenn ich in Spalte A1 das Datum 05 06 (genau mit dieser Schreibweise habe), dann erscheint in B1 - wenn ich Deine Formel eingebe, das Datum 01.05 - und das ist ja nicht richtig.
In der nächsten Zeile habe ich 03 06 stehen, dann erscheint in der B-Spalte - wenn ich wieder Deine Formel verwende) - 01.03.

Gruß
PColada

[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]

#10

Hallo Maria,
nun hat es mir Deine Formel doch geholfen. Ich habe es zuerst nicht richtig verstanden. Ich habe sie nur ein bißchen abgeändert. und zwar so: =1*(LINKS(A1;2)&"-"&RECHTS(A1;2))
Und bekomme ich auch das gewünschte Ergebnis.

Vielen herzlichen Dank.^

PColada

[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]

#11

Hallo PColada,

nun hat es mir Deine Formel doch geholfen. Ich habe es zuerst
nicht richtig verstanden. Ich habe sie nur ein bißchen
abgeändert. und zwar so: =1*(LINKS(A1;2)&"-"&RECHTS(A1;2))
Und bekomme ich auch das gewünschte Ergebnis.

die Änderung der Formel bewirkt leider, dass das Datum nicht immer richtig interpretiert wird. Teste bitte mit verschiedenen Datumsvarianten!!!

Sicher gehst du nur mit der Formel, so wie ich sie dir angegeben habe.

=1*(„01“&"-"&LINKS(A1;2)&"-"&RECHTS(A1;2))

Wie das Datum nachher aussehen soll, kannst du einstellen über das Menü: Formalt, Zellen …,Register Zahlen, Kategorie Datum. Dann kannst du unter Typ eine gewünschte Darstellung auswählen.

Gruß
Maria

#12

Hallo PColada,

du kannst natürlich auch ein benutzerdefiniertes Format wählen.
Gruß
Maria

#13

Hallo PClada,

Aber es funktionrt bei mir leider nicht.
Wenn ich in Spalte A1 das Datum 05 06 (genau mit dieser
Schreibweise habe), dann erscheint in B1 - wenn ich Deine
Formel eingebe, das Datum 01.05 - und das ist ja nicht
richtig.
In der nächsten Zeile habe ich 03 06 stehen, dann erscheint in
der B-Spalte - wenn ich wieder Deine Formel verwende) - 01.03.

Du hast über deine Zelle in Spalte B das Format TT.MM., das steht für Tag und Monat jeweils in Kurzform. Da in deiner vorgegebenen Darstellung nur der Monat und das Jahr, aber keine Angabe für den Tag des Monats enthalten war, habe ich das Datum entsprechend umgesetzt und für den Tag (ist für Excel erforderlich) den Tag auf den 1. des Monats gesetzt.

Wenn du also das Datum in der Form Monat Jahr (MM JJ) in Spalte B sehen möchtest, muss das Format entsprechend eingestellt werden, aber nicht die Formel.

Die Formel solltest du nur ändern, wenn du unbedingt die Mitte des Monats oder das Ende des Monats haben möchtest. In diesem Fall schreibe statt der 01 die entsprechende Tageszahl.

Gruß
Maria

#14

Hallo Maria,
vielen Dank für die Information. Manchmal ist Excel aber wirklich schwer zu verstehen.
Auf diese Formel muß man auch erst mal kommen. Bist Du Mathematikerin?

Gruß
PColada

[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]

#15

Hallo PColada,

Manchmal ist Excel aber
wirklich schwer zu verstehen.

Wenn du verstanden hast, dass nicht immer das, was Excel zeigt, identisch ist mit dem, was wirklich in der Zelle steht und das, was du aus der Anzeige interpretierst, auch noch wieder etwas anderes sein kann, hast du schon sehr viel verstanden.

Auf diese Formel muß man auch erst mal kommen. Bist Du
Mathematikerin?

ja, aber ist lange her, ich arbeite als Betriebswirtin/Finanzwirtschaft (2. Studium)
Gruß Maria