Ich habe folgende Aufgabenstellung, zu der ich auch nach langer Suche im Archiv bisher keine Lösung gefunden habe:
Ich habe in einer Datei X eine Spalte A mit Kundennummern (können mehrfach vorkommen) und eine Spalte B mit Datumsangaben. In dieser Spalte B gibt es aber auch leere Zellen bzw. Zellen mit Texteinträgen statt einem Datum.
In einer zweiten Datei Y gibt es in Spalte A wiederum eine Kundennummer. Ich möchte jetzt in Spalte B das älteste Datum zu dieser Kundennummer anzeigen lassen, dass in der Datei X zu finden ist bzw. eine Fehlermeldung, wenn es keinen gültigen Datumswert gibt.
Ich hoffe ich habe mich verständlich ausgedrückt und bekomme Hilfe, danke für jeden Versuch

6
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Benutzer

MAX(WENN(...))

Erich
AW: MAX(WENN(...))

Daniel
zweiter Versuch

Erich
AW: Suche ältestes Datum in anderer Datei

Daniel
dritter Versuch

Erich
MIN(WENN(...))) - vierte Variante

Erich

MAX(WENN(...))

07.02.2012 11:52:56

Erich

Hi großer Doktor (von und zu ;-) ),
schau dir das mal an:
XXXX

	A	B
1	Kd	Datum u.a.
2	123	x
3	456	07.02.2012
4	123	2000000
5	789	-15
6	456	SDF
7	123	07.02.2012
8	789	F
9	6543	40000

YYYY

	A	B	C
1	Kd	Letztes Datum	Standardformat
2	6543	06.07.2009	40000
3	789	################	-15
4	456	07.02.2012	40946
5	123	23.10.7375	2000000

Formeln der Tabelle

Zelle	Formel
B2	{=MAX(WENN(XXXX!A$1:A$999=A2;XXXX!B$1:B$999))}
C2	=B2

Enthält Matrixformel:
Umrandende { } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!

Matrix verstehen

Welche davon sollen gültige Datumswerte sein?
Noch eine Bemerkung: Die Angabe "Datei" reicht nicht. So eine Datei=Excelmappe kann viele Blätter haben,
das Blatt muss also auch vorgegeben werden.
Hier hab ich das mal in einer Mappe stehen lassen - in zwei Blättern.
Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich

AW: MAX(WENN(...))

07.02.2012 13:20:00

Daniel

Hallo Erich,
danke für deinen Input.
Wie der Verweis auf ein Tabellenblatt in einer anderen Datei korrekt ausschaut ist mir schon klar.
Mit "Ungültige Werte" meine ich jeden Wert der kein Datum sein kann, also nicht-nummerische Werte, minus etc.
Mit "ältestes Datum" meine ich das am längsten zurückliegende - MAX wird hier also nicht passen,
Mit MIN kann ich aber auch nicht arbeiten, da ich sonst die leeren = 0 finde
Deine Formel liefert leider (für mich) nicht nachvollziehbare Ergebnisse: manchmal tatsächlich das am längsten zurückliegende Datum, manchmal den Wert "0" obwohl es gültige Datumswerte gibt, manchmal aber auch Datumswerte, die nicht die ältesten vorhandenen sind :((

zweiter Versuch

07.02.2012 13:43:25

Erich

Hi Daniel,
so könnte das besser passen, wobei ich mal Daten vom 1.1.1960 bis zum 31.12.2060 als gültig definiert habe:
XXXX

	A	B
1	Kd	Datum u.a.
2	123	x
3	456	05.05.2050
4	123	2000000
5	789	-15
6	456	SDF
7	123	07.02.2012
8	789	F
9	123	07.07.1977
10	6543	5555
11	33	02.01.1960

YYYY

	A	B	C	D	E
1	Kd	begrenzt		Letztes Datum	Standardformat
2	6543	kein Datum		17.03.1915	5555
3	789	kein Datum		00.01.1900	0
4	456	05.05.2050		05.05.2050	54913
5	123	07.07.1977		07.07.1977	28313
6	33	02.01.1960		02.01.1960	21917

Formeln der Tabelle

Zelle	Formel
B2	{=WENN((MIN(WENN((XXXX!A$1:A$1000=A2)(XXXX!B$1:B$1000>0); XXXX!B$1:B$1000))>1"31.12.1959") (MIN(WENN((XXXX!A$1:A$1000=A2)(XXXX!B$1:B$1000>0); XXXX!B$1:B$1000))<1"1.1.2061"); MIN(WENN((XXXX!A$1:A$1000=A2)(XXXX!B$1:B$1000>0); XXXX!B$1:B$1000)); "kein Datum")}
D2	{=MAX(;MIN(WENN(XXXX!A$1:A$1000=A2;XXXX!B$1:B$1000)))}
E2	=D2

Enthält Matrixformel:
Umrandende { } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!

Matrix verstehen

Wenn einige deiner Daten hicht berücksichtigt werden, könnte es sein, dass sie nicht als Daten (also Zahlen),
sondern als Texte in der Tabelle stehen. Das kannst du prüfen mit =ISTTEXT(zelle) oder =ISTZAHL(zelle).
Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich

AW: Suche ältestes Datum in anderer Datei

07.02.2012 14:03:18

Daniel

Hallo Erich,
jetzt klappts super, vielen herzlichen Dank und schönen Tag noch

dritter Versuch

07.02.2012 15:59:38

Erich

Hi Daniel,
danke für deine Rückmeldung!
Auch wenn's schon einigermaßen klappte: Die Formel hatte noch ihre Tücken, und war ist unnötig lang.
So klappt jetzt insbesondere Kd 33 besser:
XXXX

	A	B
1	Kd	Datum u.a.
2	123	x
3	456	05.05.2050
4	123	2000000
5	789	-15
6	456	SDF
7	123	07.02.2012
8	789	F
9	123	07.07.1977
10	6543	5555
11	33	02.01.1960
12	33	01.01.1955

YYYY

	A	B	C
1	Kd	dritter Versuch	alt
2	6543	kein Datum	kein Datum
3	789	kein Datum	kein Datum
4	456	05.05.2050	05.05.2050
5	123	07.07.1977	07.07.1977
6	33	02.01.1960	kein Datum

Formeln der Tabelle

Zelle	Formel
B2	{=WENN(MIN(WENN((XXXX!A$1:A$1000=A2)(XXXX!B$1:B$1000>21915)(XXXX!B$1:B$1000<58808); XXXX!B$1:B$1000)); MIN(WENN((XXXX!A$1:A$1000=A2)(XXXX!B$1:B$1000>21915)(XXXX!B$1:B$1000<58808); XXXX!B$1:B$1000)); "kein Datum")}
C2	{=WENN((MIN(WENN((XXXX!A$1:A$1000=A2)(XXXX!B$1:B$1000>0); XXXX!B$1:B$1000))>1"31.12.1959") (MIN(WENN((XXXX!A$1:A$1000=A2)(XXXX!B$1:B$1000>0); XXXX!B$1:B$1000))<1"1.1.2061"); MIN(WENN((XXXX!A$1:A$1000=A2)(XXXX!B$1:B$1000>0); XXXX!B$1:B$1000)); "kein Datum")}

Enthält Matrixformel:
Umrandende { } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!

Matrix verstehen

Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich

MIN(WENN(...))) - vierte Variante

08.02.2012 07:02:21

Erich

Hi Daniel,
hier kann man in F4:F5 den Gültigkeitsbereich festlegen. Und per Hilfsspalte C ist die Formel schneller und kürzer:
XXXX

	A	B
1	Kd	Datum u.a.
2	123	x
3	456	05.05.2050
4	123	2000000
5	789	-15
6	456	SDF
7	123	07.02.2012
8	789	F
9	123	07.07.1977
10	6543	5555
11	33	02.01.1960
12	33	01.01.1948
13	33	18.500,75

YYYY

	A	B	C	D	E	F
1	Kd	kleinstes gültiges Datum	Hilf
2	33	25.08.1950	18500,75
3	123	07.07.1977	28313			gültig
4	456	05.05.2050	54913		ab	01.01.1950
5	789	kein gültiges Datum	0		bis	31.12.2050
6	6543	kein gültiges Datum	0

Formeln der Tabelle

Zelle	Formel
B2	=WENN(C2;KÜRZEN(C2); "kein gültiges Datum")
C2	{=MIN(WENN((XXXX!A$2:A$998=A2)(XXXX!B$2:B$998>=F$4)(XXXX!B$2:B$998<=F$5); XXXX!B$2:B$998))}