Supportnet / Forum / Tabellenkalkulation
Niederste Wert in Abhängigkeit von anderen Zellen
Frage
Hallo,
weiß jemand eine Lösung?
Ich benötige fortlaufend den niedersten Wert aus einem bestimmten Spaltenbereich. Dieser Bereich definiert sich aus gleichbleibenden Werten in einer anderen Spalte.
Beispiel
Spalte
1 12323 10.000,00
2 12323 5.000,00
3 12323 3.000,00
4 45609 20.000,00
5 45609 10.000,00
6 78900 5.000,00
7 98712 1.000,00
Das Ergebnis sollte so aussehen:
Spalte
1 12323 10.000,00 3.000,00
2 12323 5.000,00 3.000,00
3 12323 3.000,00 3.000,00
4 45609 20.000,00 10.000,00
5 45609 10.000,00 10.000,00
6 78900 5.000,00 5.000,00
7 98712 1.000,00 1.000,00
Vielen Dank im voaus.
Rolf
[code]
Antwort 1 von nighty
hi all :)
kopiert und eingefuegt grrr
=KKLEINSTE(WENN(A1:A100<>0;B1:B100);1)
gruss nighty
kopiert und eingefuegt grrr
=KKLEINSTE(WENN(A1:A100<>0;B1:B100);1)
gruss nighty
Antwort 2 von Saarbauer
Hallo @nighty,
ganz so einfach ist es nicht, da @ schumach immer der kleinsten Wert zu den entsprechen gleichen Zahl in Spalte a haben will.
Gruß
Helmut
ganz so einfach ist es nicht, da @ schumach immer der kleinsten Wert zu den entsprechen gleichen Zahl in Spalte a haben will.
Gruß
Helmut
Antwort 3 von CaroS
Hallo!
Könnte bitte mal jemand das Beispiel erklären? Woher kommt und wie erklärt sich die 3000 in C1? Warum ist in C4 und C5 10000 der kleinste Wert und nicht weiterhin 3000? (3000 < 10000??)
Sollte - unter großzügiger Nichtbeachtung der Beispielwerte C1 = 3000, C2 = 3000 und C4 = 10000 am Ende =MIN(A1; B1) ... =MIN(A7; B7) gemeint sein?
Hallo Rolf????
Gruß,
CaroS
Könnte bitte mal jemand das Beispiel erklären? Woher kommt und wie erklärt sich die 3000 in C1? Warum ist in C4 und C5 10000 der kleinste Wert und nicht weiterhin 3000? (3000 < 10000??)
Sollte - unter großzügiger Nichtbeachtung der Beispielwerte C1 = 3000, C2 = 3000 und C4 = 10000 am Ende =MIN(A1; B1) ... =MIN(A7; B7) gemeint sein?
Hallo Rolf????
Gruß,
CaroS
Antwort 4 von CaroS
Hallo,
ich wollt nur mitteilen, dass ich es nun doch begriffen habe. Tja, manchmal geht erst eine ganze Weile gar nichts und dann geht es plötzlich doch. Liegt wahrscheinlich an der vielen Sonne ...
Melde mich, sobald mir eine Lösung eingefallen ist.
Gruß,
CaroS
ich wollt nur mitteilen, dass ich es nun doch begriffen habe. Tja, manchmal geht erst eine ganze Weile gar nichts und dann geht es plötzlich doch. Liegt wahrscheinlich an der vielen Sonne ...
Melde mich, sobald mir eine Lösung eingefallen ist.
Gruß,
CaroS
Antwort 5 von schumach
Hallo CaroS,
ich geb´s zu, die Beschreibung ist nicht so toll, ist mir aber auch erst jetzt aufgefallen.
Vermutlich bist Du schon draufgekommen, was ich meine:
In Spalte A stehen teilweise gleichbleibende ´Werte, in meinem Beispiel von A1 bis A3, dann A4 bis A5, A6 ist ein Einzelwert, A7 auch.
Die Werte können also einmal vorkommen oder mehrmals und bilden in der Nachbarspalte B jeweils den von mir auszuwertenden Bereich. Nach dem kleinsten Wert will ich also in B1:B3 suchen, dann in B4:B5, dann B6, dann B7 usw.
Ich hoffe, jetzt wird´s klarer.
Rolf
ich geb´s zu, die Beschreibung ist nicht so toll, ist mir aber auch erst jetzt aufgefallen.
Vermutlich bist Du schon draufgekommen, was ich meine:
In Spalte A stehen teilweise gleichbleibende ´Werte, in meinem Beispiel von A1 bis A3, dann A4 bis A5, A6 ist ein Einzelwert, A7 auch.
Die Werte können also einmal vorkommen oder mehrmals und bilden in der Nachbarspalte B jeweils den von mir auszuwertenden Bereich. Nach dem kleinsten Wert will ich also in B1:B3 suchen, dann in B4:B5, dann B6, dann B7 usw.
Ich hoffe, jetzt wird´s klarer.
Rolf
Antwort 6 von rainberg
Hallo Rolf,
schreibe in D1 folgende Formel und kopiere sie nach unten.
=MIN(INDIREKT(ADRESSE(VERGLEICH(B1;B:B;0);3)B:B;0)+ZÄHLENWENN(B:B;B1)-1;3)))
Gruß
Rainer
schreibe in D1 folgende Formel und kopiere sie nach unten.
=MIN(INDIREKT(ADRESSE(VERGLEICH(B1;B:B;0);3)B:B;0)+ZÄHLENWENN(B:B;B1)-1;3)))
Gruß
Rainer
Antwort 7 von CaroS
Hallo schumach,
keine Sorge, ich hab´s begriffen. Und für den Fall, dass Rainers Formel (wahrscheinlich mit einer winzigen Änderung, Klammer oder so) zum Laufen zu bringen ist, starte ich sowieso außer Konkurrenz, denn bei meiner Lösung braucht man eine Hilfsspalte.
Spalten A: Bereichszahlen und B: Werte wie oben, C: Hilfsspalte, Spalte D: Ergebnisse
Formel C1: =B1
Formel C2: =WENN(A2=A1;MIN(B2;C1);B2)
Formel C3:C100:Zelle C2 nach unten ziehen/kopieren
Matrixformel D1: ohne {}-Klammern und mit Strg+Umsch+Eingabe eingeben!
{=MIN(($A$1:$A$100=A1)*(($B$1:$B$100)-MAX($B$1:$B$100)))+MAX($B$1:$B$100)}
(Die absoluten Bezüge sind notwendig.)
Formel D2:D100: Zelle D1 nach unten ziehen/kopieren
Gruß,
CaroS
keine Sorge, ich hab´s begriffen. Und für den Fall, dass Rainers Formel (wahrscheinlich mit einer winzigen Änderung, Klammer oder so) zum Laufen zu bringen ist, starte ich sowieso außer Konkurrenz, denn bei meiner Lösung braucht man eine Hilfsspalte.
Spalten A: Bereichszahlen und B: Werte wie oben, C: Hilfsspalte, Spalte D: Ergebnisse
Formel C1: =B1
Formel C2: =WENN(A2=A1;MIN(B2;C1);B2)
Formel C3:C100:Zelle C2 nach unten ziehen/kopieren
Matrixformel D1: ohne {}-Klammern und mit Strg+Umsch+Eingabe eingeben!
{=MIN(($A$1:$A$100=A1)*(($B$1:$B$100)-MAX($B$1:$B$100)))+MAX($B$1:$B$100)}
(Die absoluten Bezüge sind notwendig.)
Formel D2:D100: Zelle D1 nach unten ziehen/kopieren
Gruß,
CaroS
Antwort 8 von rainberg
Hallo Rolf,
meine Formel hatte leider einen Kopierfehler.
=MIN(INDIREKT(ADRESSE(VERGLEICH(B1;B:B;0);3) &":"& ADRESSE(VERGLEICH(B1;B:B;0)+ZÄHLENWENN(B:B;B1)-1;3)))
Gruß
Rainer
meine Formel hatte leider einen Kopierfehler.
=MIN(INDIREKT(ADRESSE(VERGLEICH(B1;B:B;0);3) &":"& ADRESSE(VERGLEICH(B1;B:B;0)+ZÄHLENWENN(B:B;B1)-1;3)))
Gruß
Rainer
Antwort 9 von nighty
hi all :)
und ich versteh zuwenig von den formeln wie es aussieht :)
gruss nighty
und ich versteh zuwenig von den formeln wie es aussieht :)
gruss nighty
Antwort 10 von CaroS
Hallo nighty,
kein Problem! Wenn Du mal eine Frage hast, wir helfen Dir.
(War aber wahrscheinlich nicht ganz ernst gemeint, oder? ;-) )
Gruß,
CaroS
kein Problem! Wenn Du mal eine Frage hast, wir helfen Dir.
(War aber wahrscheinlich nicht ganz ernst gemeint, oder? ;-) )
Gruß,
CaroS
Antwort 11 von Aliba
Hi,
oder mit Array-Formel:
{=MIN(WENN($A$1:$A$100=A1;$B$1:$B$100))}
ACHTUNG!!! geschweifte Klammern nicht mit eingeben, sondern Formel normal erfassen und dann mit STRG+SHIFT+ENTER abschliessen.
CU Aliba
oder mit Array-Formel:
{=MIN(WENN($A$1:$A$100=A1;$B$1:$B$100))}
ACHTUNG!!! geschweifte Klammern nicht mit eingeben, sondern Formel normal erfassen und dann mit STRG+SHIFT+ENTER abschliessen.
CU Aliba
Antwort 12 von schumach
Vielen Dank an alle
@nighty
Nein, siehe Antwort 2, trotzdem danke
@rainberg
Ich hab noch einiges ausprobiert, aber Deine Formel liefert immer den Wert 0 zurück. Aber egal, danke
@CaroS und Aliba
Es funktioniert, super. Hilfsspalte wäre egal, ich probiere mal beide Möglichkiten mit der Originaldatei (42.500 Zeilen) aus. Angefangen habe ich mit der Lösung von Aliba und muss mich nun in Geduld üben (Excel rechnet und rechnet).
Viele Grüße
Rolf
@nighty
Nein, siehe Antwort 2, trotzdem danke
@rainberg
Ich hab noch einiges ausprobiert, aber Deine Formel liefert immer den Wert 0 zurück. Aber egal, danke
@CaroS und Aliba
Es funktioniert, super. Hilfsspalte wäre egal, ich probiere mal beide Möglichkiten mit der Originaldatei (42.500 Zeilen) aus. Angefangen habe ich mit der Lösung von Aliba und muss mich nun in Geduld üben (Excel rechnet und rechnet).
Viele Grüße
Rolf
Antwort 13 von rainberg
Hallo Rolf,
Problem ist ja nun gelöst, nur noch zur Info:
Meine zweite Formel läuft mit Sicherheit, wenn Deine Daten so angeordnet sind, wie Du sie bei der Ausgangsfrage dargestellt hast.
Also in den Spalten A bis C und die Formel in Spalte D.
Gruß
Rainer
Problem ist ja nun gelöst, nur noch zur Info:
Meine zweite Formel läuft mit Sicherheit, wenn Deine Daten so angeordnet sind, wie Du sie bei der Ausgangsfrage dargestellt hast.
Also in den Spalten A bis C und die Formel in Spalte D.
Gruß
Rainer
Antwort 14 von CaroS
Hallo schumach,
da mus ich doch gleich mal ein gutes Wort (ein sehr gutes!) für rainbergs Formel in AW8 einlegen.
Der Unterschied und der Grund, weshalb Du immer nur den Wert 0 kriegst, ist ganz einfach, dass er Deine Beispieldaten anders interpretiert hat. Spalte A: fortlaufende Nummerierung, Spalte B: Bereichszahlen, Spalte C: Werte. Ist mir übrigens zuerst auch passiert, als ich rainbergs Formel ausprobiert habe. Aber mit einer "Verschiebung" um eine Spalte hat sie dann doch absolut einwandfrei funktioniert.
Deshalb schreibe ich (meistens) noch dazu, mit welchen Daten ich wie/was getestet habe, das hilft, Missverständnisse zu vermeiden. Siehe AW7:
Spalten A: Bereichszahlen und B: Werte wie oben, ... (Nummerierung weggelassen)
Alibas Formel gefällt mir aber insgesamt am besten, weil sie die kürzeste und kompakteste ist. (Und weil sie mir zeigt, dass ich an einer "ordentlichen" Lösung schon sehr dicht dran war. Ich habe dasselbe gemeint, nur nicht ganz geschafft.) Das ist ja das Schöne, dass hier jeder noch was lernen kann, wenn er die Augen offenhält.
Gruß,
CaroS
da mus ich doch gleich mal ein gutes Wort (ein sehr gutes!) für rainbergs Formel in AW8 einlegen.
Der Unterschied und der Grund, weshalb Du immer nur den Wert 0 kriegst, ist ganz einfach, dass er Deine Beispieldaten anders interpretiert hat. Spalte A: fortlaufende Nummerierung, Spalte B: Bereichszahlen, Spalte C: Werte. Ist mir übrigens zuerst auch passiert, als ich rainbergs Formel ausprobiert habe. Aber mit einer "Verschiebung" um eine Spalte hat sie dann doch absolut einwandfrei funktioniert.
Deshalb schreibe ich (meistens) noch dazu, mit welchen Daten ich wie/was getestet habe, das hilft, Missverständnisse zu vermeiden. Siehe AW7:
Spalten A: Bereichszahlen und B: Werte wie oben, ... (Nummerierung weggelassen)
Alibas Formel gefällt mir aber insgesamt am besten, weil sie die kürzeste und kompakteste ist. (Und weil sie mir zeigt, dass ich an einer "ordentlichen" Lösung schon sehr dicht dran war. Ich habe dasselbe gemeint, nur nicht ganz geschafft.) Das ist ja das Schöne, dass hier jeder noch was lernen kann, wenn er die Augen offenhält.
Gruß,
CaroS
Antwort 15 von schumach
Tatsächlich, rainberg´s Formel funktioniert. Da bin ich jetzt irgendwie auf mich selbst reingefallen.
Alles perfekt, jetzt habe ich drei Lösungen 8-))), nochmal danke an die Formel-Götter.
Der Einsatz ist kein Problem, mit der Analyse der Lösungen wird´s wohl etwas dauern (will ja auch was dazulernen).
Viele Grüße Rolf
Alles perfekt, jetzt habe ich drei Lösungen 8-))), nochmal danke an die Formel-Götter.
Der Einsatz ist kein Problem, mit der Analyse der Lösungen wird´s wohl etwas dauern (will ja auch was dazulernen).
Viele Grüße Rolf
Antwort 16 von CaroS
Hallo Leute,
habt Ihr´s bemerkt? Wir sind befördert worden, wir dürfen uns jetzt G... nennen. Hoffentlich gibt das keinen Ärger mit dem Ratzinger!
Trotzdem danke dafür!
CaroS
habt Ihr´s bemerkt? Wir sind befördert worden, wir dürfen uns jetzt G... nennen. Hoffentlich gibt das keinen Ärger mit dem Ratzinger!
Trotzdem danke dafür!
CaroS
Antwort 17 von fürLau
apropos..Pabst
...wird angerufen!
Sagt einer: "Alles Gute zum Namenstag!".
Sagt der Pabst: "Aber heut´ ist doch garnicht Benedikt !"
Sagt der Anrufer:" aber der 16te"
...wird angerufen!
Sagt einer: "Alles Gute zum Namenstag!".
Sagt der Pabst: "Aber heut´ ist doch garnicht Benedikt !"
Sagt der Anrufer:" aber der 16te"
Antwort 18 von Aliba
Hi schumach,
hab gerade erst wieder reingeschaut. Daß es bei dieser Datenmenge mit Arrayformeln zu Performanceproblemen kommen kann ist leicht möglich.
Ich weiss ja nicht, in welcher Art du die Daten benötigst, aber eine sehr schnelle Möglichkeit wäre hierfür auch die PIVOT-Tabelle.
Ich gehe mal davon aus, daß Deine Tabelle Überschriften hat. Die eine Spalte hat in meinem Beispiel die Überschrift: Kundennummer, die zweite die Überschrift: Betrag
Gehe einfach in eine Zelle der Überschriftszeile, dann DATEN - PIVOTBERICHT. Es öffnet sich ein Assistent, hier die ersten beiden Fenster mit WEITER bestätigen, dann den Button "Layout" auswählen. Im nächsten Fenster ziehst du den Button: Kundennummer in den "Zeilenbereich". Den Button Betrag ziehst Du in den Datenbereich. Nun einmal auf den Button "Betrag" im Datenbereich doppelklicken und den Eintrag "Minimum" auswählen. Dann OK und dann auf "Fertigstellen" und das wars.
CU Aliba
hab gerade erst wieder reingeschaut. Daß es bei dieser Datenmenge mit Arrayformeln zu Performanceproblemen kommen kann ist leicht möglich.
Ich weiss ja nicht, in welcher Art du die Daten benötigst, aber eine sehr schnelle Möglichkeit wäre hierfür auch die PIVOT-Tabelle.
Ich gehe mal davon aus, daß Deine Tabelle Überschriften hat. Die eine Spalte hat in meinem Beispiel die Überschrift: Kundennummer, die zweite die Überschrift: Betrag
Gehe einfach in eine Zelle der Überschriftszeile, dann DATEN - PIVOTBERICHT. Es öffnet sich ein Assistent, hier die ersten beiden Fenster mit WEITER bestätigen, dann den Button "Layout" auswählen. Im nächsten Fenster ziehst du den Button: Kundennummer in den "Zeilenbereich". Den Button Betrag ziehst Du in den Datenbereich. Nun einmal auf den Button "Betrag" im Datenbereich doppelklicken und den Eintrag "Minimum" auswählen. Dann OK und dann auf "Fertigstellen" und das wars.
CU Aliba
Antwort 19 von nighty
hi all :)
diesmal stimmts aber,vielleicht ist es schneller als die formeln ? :)
gruss nighty
Option Explicit
Sub AutoFilterFormel1()
Dim zaehler As Long
Application.DisplayAlerts = False
With ActiveSheet
zaehler = 2
Do While zaehler < .Range("A65536").End(xlUp).Row
.Range("A1").AutoFilter field:=1, Criteria1:=.Range("A" & zaehler), VisibleDropDown:=False
.Range("C" & zaehler & ":C" & .Range("A65536").End(xlUp).Row).Value = WorksheetFunction.Subtotal(5, .Range("B2:B" & Rows.Count))
zaehler = .Range("A65536").End(xlUp).Row + 1
.Range("A1").AutoFilter
Loop
End With
Application.DisplayAlerts = True
End Sub
diesmal stimmts aber,vielleicht ist es schneller als die formeln ? :)
gruss nighty
Option Explicit
Sub AutoFilterFormel1()
Dim zaehler As Long
Application.DisplayAlerts = False
With ActiveSheet
zaehler = 2
Do While zaehler < .Range("A65536").End(xlUp).Row
.Range("A1").AutoFilter field:=1, Criteria1:=.Range("A" & zaehler), VisibleDropDown:=False
.Range("C" & zaehler & ":C" & .Range("A65536").End(xlUp).Row).Value = WorksheetFunction.Subtotal(5, .Range("B2:B" & Rows.Count))
zaehler = .Range("A65536").End(xlUp).Row + 1
.Range("A1").AutoFilter
Loop
End With
Application.DisplayAlerts = True
End Sub