Excel, aggregation mit bedingung

[Nadine2020_a08295]

Liebe/-r Experte/-in,
habe folgendes Problem.
Ich habe eine Exceldatei und darin zwei Tabs. Datentab und Auswahltab…aber das nur als Basisinfo.
Ich ahbe eine große Tabelle mit vielen Spalten und dAten. Mir geht es um eine Spalte wo diverses Datums drin stehen(unsortiert). ich möchte jetzt vom zweiten tab auf den minimalsten und den maximalsten datumswert zurückgreifen, ABER nur wenn bestimmt bedingungen erfüllt sind.
bsp: ich habe folgende spalten:
namevornamedatums

ich möchte, dass er mir den kleinsten datumswert gibt, aber nur dann, wenn name gleich schmitt und vorname gleich hans ist und nur dann. das soll mit einer formal lösbar sein und nur mit excel.

versucht hab ichs mit einer array function, klappt aber nicht und mit sumif, was aber summe bei bedingungen ausrechnet und nicht min oder max. und minif oder maxif gibts glaube nicht:frowning:
hoffe jemand kann mir helfen…probiers seit stunden…frustrierend.

danke und vg

[ThomasM]

Hallo Nadine

Es gibt zwar keine Funktion minif oder maxif, aber man kann Funktionen schachteln. Also fange mit dem Wenn an, also in etwa so:

= wenn(und(name=„Schmitt“;Vorname=„Hans“);Min(C1:C100);„nix“)

Das berechnet Dir jetzt das Minimum unter den von Dir genannten Bedingungen.

Viele Grüße
Thomas

[Kai_13791c]

Hallo Nadine,

ich würde dir gerne helfen, habe aber noch nicht ganz verstanden, was genau du als input eingibst. Am einfachsten wäre ein kleiner Ausschnitt aus deiner Datei sowie die Abfrage.
Eine grundsätzliche Möglichkeit wäre auf jeden Fall, die Daten als Pivottabelle zu formatieren und dann entsprechende Auswahlen zu treffen.
Aber genau kann ich dir nur helfen, wenn du ein Muster schickst. Allerdings ist Pivot seit Version 2007 deutlich verbessert worden. Welches Excel setzt du ein?
Gruß
Kai

[Nadine2020_a08295]

hi thomas,
also quasi IF und AND…nutze auf englisch, werd ich gleich mal testen…
dankeschön…

[Nadine2020_a08295]

folgende Formel nutze ich:

=IF(AND(TASKS!D5:smiley:120=18;TASKS!E5:E120=„IMPL“);MIN(TASKS!O5:open_mouth:120);„nix“)

wobei TASKS das andere Sheet ist und er soll innerhalb einer range halt nach 18 suchen UND nach IMPL innerhalb derselben range aber anderen spalte halt…nur dann soll er das minimum vom datum einer wiederrum anderen spalte berechnen, nämlich spalte O im bsp.
als ergebnis bekomme ich „nix“, aber es gibt zwei zeilen, auf die diese if bedingung zutrifft und somit müsste er ein datum ausspucken…
weißt du, was ich meine…
danke

[Nadine2020_a08295]

hi…oki beispiel:

folgende abfrage hab ich getestet:
=MIN((TASKS!D$5:smiley:$100=18)*TASKS!O$5:open_mouth:$100)

überall dort wo 18 in D steht berechne das MIN von O…
auch das klappt nicht wirklich…zwar kein error, aber falsche datumswerte…
nutze excel 2007.
pivot soll ich nich nutzen, geht angeblich ganz easy mit ner kleinen formel.
erweitert mit 2 bedingungen wäre es:
=MIN((TASKS!D$5:smiley:$100=18)*(TASKS!E$5:E$100=„IMPL“)*TASKS!O$5:open_mouth:$100)
also wenn 18 und impl erfüllt ist in einer zeile quasi, dann minimum von spalte O.
weisst du was ich meine?
danke und vg

[Matse]

Hallo

Ich habe mal eine Array-Formel mit 2 WENN-Verschachtelungen gebaut.
Statt den „N“ und „V“ gibst Du die Namen und Vornamen
ein, oder beziehst auf zwei Zellen, in denen die Vor- und Nachnamen stehen.

Array-Formeln mit CTRL+SHIFT+ENTER abschliessen.

{=MIN(WENN(Tabelle1!$A$2:blush:A$11=„A“;WENN(Tabelle1!$B$2:blush:B$11=„T“;Tabelle1!$C$2:blush:C$11:wink:))}

Gruss
Matthias

[Matse]

Sorry
Statt „N“ und „V“ hab ich „A“ und „T“ drin.
Jetzt sollte es stimmen:

{=MIN(WENN(Tabelle1!$A$2:blush:A$11=„N“;WENN(Tabelle1!$B$2:blush:B$11=„V“;Tabelle1!$C$2:blush:C$11:wink:))

Statt MIN kannst Du auch mit KKLEINSTE arbeiten:

{=KKLEINSTE(WENN(Tabelle1!$A$2:blush:A$11=„N“;WENN(Tabelle1!$B$2:blush:B$11=„V“;Tabelle1!$C$2:blush:C$11:wink:);1)}

Am Ende steht die „1“. Dies ist der kleinste Wert.
Hier könntest Du auch mit 2,3,usw. arbeiten, um den 2.-kleinsten oder 3.-kleinsten Wert (usw.) zu bestimmen.

Gruss
Matthias

[Anonym_682136d9ee8a]

Hallo Nadine2020,

ich habe dein Problem so verstanden, dass du fallweise einen Nachnamen und einen Vornamen eingibst und dann aus der großen Tabelle die zugehörenden Datums auswerten willst und zwar so, dass du das kleinste zugehörenden Datum und das größte für die beiden Namen finden möchtest.

Das geht. Vorgehensweise:

1. Gib den zu suchenden Nachnamen und Vornamen in zwei Zellen in einer Zeile ein. Daneben wirst du dann den Befehl Min() und Max() eingeben.
2. Gib einen Filterbereich (für den Spezialfilter) ein und zwar in zwei Zeilen. Die erste Zeile enthält die Spaltenüberschriften, in der zweiten Zeile machst du einen Bezug auf die beiden Namen aus 1.
3. Gib einen Ergebnisbereich für die Ergebnisse der Spezialfilters ein und zwar in die erste Zeile wieder die Spaltenüberschriften. In die restlichen Spalten fügt Excel die Ergebnisse ein. Lasse also viel Platz.
4. Setze den Cursor in die erste Zelle deiner großen Tabelle und rufe den Spezialfilter auf. Du wirst dann gefragt, was der zu durchsuchende Bereich ist (von dem du die erste Zelle bereits aktiviert hast), wo die Filterkriterien sind und wohin die Ergebnisse zu schreiben sind. Das hast du mit Schritt 2 und 3 getan.
   Beim Ergebnisbereich musst du nur die Überschriftenzeile angeben. Erlaube Duplikate!
5. Wenn du ein Ergebnis hast (mit hoffentlich mehreren Zeilen, dann kannst du die Funktion Min und Max mit den Bezügen anpassen. Nimm hier gleich einen größeren Bereich, dann musst du nicht mehr nachbessern.

Alles klar?
Ich habe eine Beispieltabelle erstellt, die ich dir zusenden könnte (Excel2003-Datei).

Bei Fragen: du kannst mich anrufen. 06103 52090

Viel Erfolg
Harald

[Norbert_Nettekoven]

versuch es mal mit folgender - benutzerdefinierten - Funktion:
Function Datum_min(E_Name, E_Vorname, E_Namespalte, E_Vornamespalte, E_Datumspalte)
npndatummin = 999999999

npnzeileu = Range(Cells(Rows.Count, E_Datumspalte).End(xlUp).Address).Row
For gabi = 1 To npnzeileu
If Cells(gabi, E_Namespalte) = E_Name And Cells(gabi, E_Vornamespalte) = E_Vorname Then
npndatummin = Application.Min(npndatummin, Cells(gabi, E_Datumspalte))
End If
Next gabi
Datum_min = npndatummin
End Function

Function Datum_max(E_Name, E_Vorname, E_Namespalte, E_Vornamespalte, E_Datumspalte)
npndatummax = -999999999

npnzeileu = Range(Cells(Rows.Count, E_Datumspalte).End(xlUp).Address).Row
For gabi = 1 To npnzeileu
If Cells(gabi, E_Namespalte) = E_Name And Cells(gabi, E_Vornamespalte) = E_Vorname Then
npndatummax = Application.Max(npndatummax, Cells(gabi, E_Datumspalte))
End If
Next gabi
Datum_max = npndatummax
End Function

…wenn Du die Exceldatei brauchst, dort habe ich das ausprobiert, benötige ich deine Mailaddresse.

Gruß
Norbert
[email protected]

[Andreas_S_llner_8a3055]

Hallo,

hier eine einfach Lösung (erfüllt jedoch nicht ganz)
Man könnte die Lasche aktivieren dann -Daten - Filter -Autofilter
Danach in Dropdown (A1) Schmid auswählen
in Dropdown (B1) Hans
und dann in Dropdown (C1 ) Sortieren absteigend

Ergebnis steht falls vorhanden in C2

• VBA Funktion schreiben (vielleicht obiges Vorgehen per Makro aufzeichnen und VBA Code anpassen)

Viele Grüße
Andreas

[Nadine2020_a08295]

hi thomas,

Folgende Fornmel funktioniert jetzt:

=MIN(IF((TASKS!$A$5:blush:A$2000=‚KPI‘‚s‘!B19);TASKS!$O$5:blush:O$2000;""))
wobei in B19 die Konkatenation der Bedingungen steht(insgesamt drei).
Was aber nicht sauber ist: Wenn es kein passendes Datum gibt, will ich, dass er einen leeren String ausgibt oder „kein Datum“ schreibt, er schreibt aber dann 00.01.1900 in die Zelle als Defaultwert. Das versuche ich mit IF abzufragen, aber klappt nicht.

Noch eine andere Sache: Ich nutze SUMIF, indem er was bestimmtes summieren soll, bei ERfüllung von drei Bedingungen, klappt auch wunder bar mit folgender Formel:

=SUMIF(TASKS!$A$5:blush:A$1000;‚KPI‘‚s‘!$B11;TASKS!$U$5:blush:U$1000)
wobei in B11 die drei Bedingungen konkateniert sind.
Aber jetzt will ich das gleiche für zwei Bedingungen machen, dass er summiert, wenn MINDESTENS zwei Bedingungen erfüllt sind, also auch wenn alle drei erfüllt sind. Ich nehme in der Kokatenation die dritte Bedingung raus, aber dann prüft er nur noch immer die beiden Bedinungen UND wenn die dritte auch erfüllt ist, summiert er nicht mehr(soll er aber, denn wenn die dritte auch erfüllt ist, sind ja mind. die ersten beiden auch erfüllt.)

entweder du verstehst was ich meine, oder du steigst gar nicht mehr durch:smile:

lieben dank, nadine.

[Nadine2020_a08295]

Hallo Kai

konnte nicht früher wg netz schreiben;(

Folgende Fornmel funktioniert jetzt:

=MIN(IF((TASKS!$A$5:blush:A$2000=‚KPI‘‚s‘!B19);TASKS!$O$5:blush:O$2000;""))
wobei in B19 die Konkatenation der Bedingungen steht(insgesamt drei).
Was aber nicht sauber ist: Wenn es kein passendes Datum gibt, will ich, dass er einen leeren String ausgibt oder „kein Datum“ schreibt, er schreibt aber dann 00.01.1900 in die Zelle als Defaultwert. Das versuche ich mit IF abzufragen, aber klappt nicht.

Noch eine andere Sache: Ich nutze SUMIF, indem er was bestimmtes summieren soll, bei ERfüllung von drei Bedingungen, klappt auch wunder bar mit folgender Formel:

=SUMIF(TASKS!$A$5:blush:A$1000;‚KPI‘‚s‘!$B11;TASKS!$U$5:blush:U$1000)
wobei in B11 die drei Bedingungen konkateniert sind.
Aber jetzt will ich das gleiche für zwei Bedingungen machen, dass er summiert, wenn MINDESTENS zwei Bedingungen erfüllt sind, also auch wenn alle drei erfüllt sind. Ich nehme in der Kokatenation die dritte Bedingung raus, aber dann prüft er nur noch immer die beiden Bedinungen UND wenn die dritte auch erfüllt ist, summiert er nicht mehr(soll er aber, denn wenn die dritte auch erfüllt ist, sind ja mind. die ersten beiden auch erfüllt.)

entweder du verstehst was ich meine, oder du steigst gar nicht mehr durch:smile:

lieben dank, nadine.

[Nadine2020_a08295]

Hallo Matse

leider konnte ich nich früher schreiben wg internetausfall, die telekom war schuld. danke für dein reply.

Folgende Fornmel funktioniert jetzt:

=MIN(IF((TASKS!$A$5:blush:A$2000=‚KPI‘‚s‘!B19);TASKS!$O$5:blush:O$2000;""))
wobei in B19 die Konkatenation der Bedingungen steht(insgesamt drei).
Was aber nicht sauber ist: Wenn es kein passendes Datum gibt, will ich, dass er einen leeren String ausgibt oder „kein Datum“ schreibt, er schreibt aber dann 00.01.1900 in die Zelle als Defaultwert. Das versuche ich mit IF abzufragen, aber klappt nicht.

Noch eine andere Sache: Ich nutze SUMIF, indem er was bestimmtes summieren soll, bei ERfüllung von drei Bedingungen, klappt auch wunder bar mit folgender Formel:

=SUMIF(TASKS!$A$5:blush:A$1000;‚KPI‘‚s‘!$B11;TASKS!$U$5:blush:U$1000)
wobei in B11 die drei Bedingungen konkateniert sind.
Aber jetzt will ich das gleiche für zwei Bedingungen machen, dass er summiert, wenn MINDESTENS zwei Bedingungen erfüllt sind, also auch wenn alle drei erfüllt sind. Ich nehme in der Kokatenation die dritte Bedingung raus, aber dann prüft er nur noch immer die beiden Bedinungen UND wenn die dritte auch erfüllt ist, summiert er nicht mehr(soll er aber, denn wenn die dritte auch erfüllt ist, sind ja mind. die ersten beiden auch erfüllt.)

entweder du verstehst was ich meine, oder du steigst gar nicht mehr durch:smile:

lieben dank, nadine.

[Matse]

Hey Nadine

Zur Fehlersuche musst Du die ganze Formel in eine IF-Abfrage stellen und bei Ergebnis LEER ("") die Ausgabe ebenfalls als leer „“ darstellen. Bei mir klappt es so.

=IF(MIN(IF((TASKS!$A$5:blush:A$2000=‚KPI‘‚s‘!B19);TASKS!$O$5:blush:O$2000;""))="";"";MIN(IF((TASKS!$A$5:blush:A$2000=‚KPI‘‚s‘!B19);TASKS!$O$5:blush:O$2000;"")))

Evtl. musst du wegen deiner Konk–dingsbums (gibts das auch in DEUTSCH?) die Abfrage auf 0 setzen.

Also IF…=0
statt …=""

Gruss und eine schöne Woche
Matse