Hi,
Die Matrixformel
{=MIN(WENN((Rechnungen!$B$3:$B$13558=$B4);1;50000)*Rechnungen!$D$3:$D$13558)}
rechnet richtig, wenn der bereich 3:13558 mit Daten gefüllt ist. schon eine Leerzeile mehr (3:13559) bringt 00.01.1900
Bei der Max-Formel kann ich den Bereich zukunftssicher auf 20000 erweitern:
{=MAX((Rechnungen!$B$3:$B$20000=$B4)*(Rechnungen!$D$3:$D$20000))}
Wennfehler hilft nicht?
ist ja auch klar, weil Formel bringt dann 0 und das ist kein Fehler
Habs auch schon probiert mit einem Buchstaben anstelle von 50000
Was muss ich tun, oder geht das gar nicht zukunftssicher?
Danke im Voraus
Hola,
Code:
=MIN(WENN((Rechnungen!B3:B13358=B4)*(Rechnungen!D3:D13358>0);Rechnungen!D3:D13358))
Gruß,
steve1da
Hallo, es gibt auch DBMIN() oder und DBMAX() als Alternative zur Matrixformel...
Hi
____|_A_|_B_|__C_|_D_|_E_|_F_|
1| | | | | | |
2| | | | | | |
3| |A | 10| |B | 9|
4| |B | 9| | | 3|
5| |C | 8| | | |
6| |A | 7| | | |
7| |B | 6| | | |
8| |C | 5| | | |
9| |A | 4| | | |
10| |B | 3| | | |
11| |C | 2| | | |
F3 {=MAX((B3:B11=E3)*C3:C11)}
F4 {=MIN(WENN((B3:B11=E3);C3:C11;"N"))}
Moin,
Vielen Dank für die Vorschläge, aber ich bekomme es nicht hin.
Excel rechnet sich einen Wolf und du kannst unten sehen wie langsam die % der Berechnung steigen.
Die Daten werden aus einer anderen Anwendung mittels Hyperlinks aktualisiert und die Datei ist ohne die beiden neuen Spalten schon nervtötend langsam, selbst mit deaktivierten Hyperlinks.
Ich hab mal das anonymisiert.
Ich werde heute mal fragen, ob auch - nur für die beiden neuen Spalten - eine VBA-Lösung in frage kommt. ich denke, das is deutlich schneller, zumal bei meiner jetzigen Min-Formel bei jedem update der Range angepasst werden muss, damit keine Leerzeilen im Bereich sind.
Danke Jockel für den Hinweis DBMIN/DBMAX. Ich bekomme es damit aber auch nicht hin, und ich darf auch keinen Kriterienbereich hinzufügen.
Die Formel von steve1da sieht gut aus, bringt aber 0 wo meine Formel richtig 28.4.17 bringt
Bei der Max-Formel kann ich zukunftssicher den zu erfassenden Bereich auf 20.000 setzen, aktuell sind 13.558 Zeilen gefüllt.
bei der Min-Formel nicht, da muss ich bislang genau auf den gefüllten Range schauen bzw. jedes Mal nach Aktualisierung die Formel anpassen.
Und schließlich zurück zum Betreff: Dort wo kein Datum zu finden ist, hätte ich gerne anstelle von Gartenzäunen bzw 00.01.1900 nix oder einen Strich (ALT+0151) drin stehen.
Und ganz Toll wäre, gibt es nur eine Rechnung, dann soll deren Datum in beiden Spalten stehen.
Ich fange jetzt mal für mich mit einer VBA-Lösung an, aber wenn es da schnellere Formeln gäbe, die auch richtig rechnen, interessiert mich das stark, da ich in Formeln noch ned so gut bin.
Danke für Unterstützung
Moin Wastl,
R5 =WENNFEHLER(1/(1/MIN(WENN(B5=Rechnungen!B1:B40000;Rechnungen!D1:D40000)));"") '(CSE)
S5 =WENNFEHLER(1/(1/MAX(WENN(B5=Rechnungen!B3:B40000;Rechnungen!D3:D40000)));"") '(CSE)
Getestet und geht.
Problem in der Bsp. Datei war das Spalte Rechnungen!D noch als Text Vorlag.
Gruß Elex
Hallo, du kannst doch einfach so tun, oder ..?
| Arbeitsblatt mit dem Namen 'Alle Kunden' |
| R | S |
| 2 | Erster Umsatz Datum | Letzter Umsatz Datum |
| 3 | | |
| 4 | 28.04.2017 | 21.11.2017 |
| Zelle | Formel |
| R4 | =WENNFEHLER(INDEX(Rechnungen!$D$3:$D$500000;VERGLEICH(B4;Rechnungen!$B$3:$B$50000;0));"") |
| S4 | =WENNFEHLER(VERWEIS(2;1/(Rechnungen!$B$3:$B$5000=B4);Rechnungen!$D$3:$D$5000);"") |
| Verwendete Systemkomponenten: [Windows (32-bit) NT :.00] MS Excel 2013 |
| Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg |
Hei Jungs,
vielen Dank
Zitat:Problem in der Bsp. Datei war das Spalte Rechnungen!D noch als Text Vorlag.
Das ist leider auch so in der Originaldatei und die von mir gestrickten bzw. aus dem Indernet geholten und umgeschriebenen Formeln hat das nicht gestört. (Daten kommen aus SAP)
Und die Formeln von Jockel stört das auch nicht
So rechnen von beiden beide Formeln fixer als meine, aber die von Elex bringen keine Ergebnisse.
Eine Frage an Jockel:
deine Formel für R4 findet das erste Vorkommen in der Liste. Bist du dir sicher, dass das immer das kleinste Datum ist? S4=reziprok analog also der Letzte Eintrag zu der Rechnungsnummer?
(nein, habs mit VBA nachgeprüft, isses leider nicht)
:s
Schau dir mal mit deinen Formeln Zeilen 122-126 an
Hier mal mein VBA (es braucht auf meinem Lapdepp 40 Sekunden)
Code:
Sub erster_umsatz()
Dim Alle_Kunden As Variant
Dim Rechnungen As Variant
Dim Erster_letzter_Umsatz As Variant
Dim letzRechn As Long
Dim AK As Long
Dim RN As Long
Dim Datuum As Date
Dim ReNr As String
Dim Lauf As Long
letzRechn = Sheets("Rechnungen").UsedRange.SpecialCells(xlCellTypeLastCell).Row
Alle_Kunden = Sheets("Alle Kunden").UsedRange.Columns(2)
Rechnungen = Sheets("Rechnungen").UsedRange.Range("B1:D" & letzRechn)
ReDim Erster_letzter_Umsatz(3 To UBound(Alle_Kunden) + 1, 1 To 2)
For AK = 3 To UBound(Erster_letzter_Umsatz)
Erster_letzter_Umsatz(AK, 1) = "—"
Erster_letzter_Umsatz(AK, 2) = "—"
Next AK
For AK = 2 To UBound(Alle_Kunden)
ReNr = Alle_Kunden(AK, 1)
Lauf = 0
For RN = 3 To letzRechn
If Rechnungen(RN, 1) = ReNr Then
Datuum = Rechnungen(RN, 3)
If Lauf = 0 Then
Erster_letzter_Umsatz(AK + 1, 1) = Datuum
Erster_letzter_Umsatz(AK + 1, 2) = Datuum
Lauf = Lauf + 1
Else
If Datuum > Erster_letzter_Umsatz(AK + 1, 2) Then
Erster_letzter_Umsatz(AK + 1, 2) = Datuum
End If
If Datuum < Erster_letzter_Umsatz(AK + 1, 1) Then
Erster_letzter_Umsatz(AK + 1, 1) = Datuum
End If
End If
End If
Next RN
Next AK
End Sub
(08.02.2018, 13:32)Wastl schrieb: [ -> ]Hei Jungs,
vielen Dank
Das ist leider auch so in der Originaldatei und die von mir gestrickten bzw. aus dem Indernet geholten und umgeschriebenen Formeln hat das nicht gestört. (Daten kommen aus SAP)
Und die Formeln von Jockel stört das auch nicht
So rechnen von beiden beide Formeln fixer als meine, aber die von Elex bringen keine Ergebnisse.
Eine Frage an Jockel:
deine Formel für R4 findet das erste Vorkommen in der Liste. Bist du dir sicher, dass das immer das kleinste Datum ist? S4=reziprok analog also der Letzte Eintrag zu der Rechnungsnummer?
(nein, habs mit VBA nachgeprüft, isses leider nicht)
:s
Schau dir mal mit deinen Formeln Zeilen 122-126 an
...
Hallo, ich bin davon ausgegangen, dass die Listen sortiert sind... und hatte in der Tat nur die ersten Werte geprüft. Und dabei (richtig) das jeweils erste und letzte Vorkommen ausgeben lassen; dass wie Werte keine Datumswerte (Zahlen) sind spielt hierbei keine Rolle. Bei der von Elex schon... ... dazu hatte er aber auch was geschrieben... Aus Texten kann man nunmal kein Minimum oder Maximum bestimmen...
(08.02.2018, 13:32)Wastl schrieb: [ -> ]So rechnen von beiden beide Formeln fixer als meine, aber die von Elex bringen keine Ergebnisse.
Die Formel bringt schon Ergebnisse. Nur muss auf die ein oder andere Art aus dem Text rechenbare Werte gemacht werden. Geht auch in der Formel wenn du (1* oder --) hinzufügst.
Sortierte Liste und Jörgs Formel sind aber sicher schneller.
R5 =WENNFEHLER(1/(1/MIN(WENN(B5=Rechnungen!B1:B40000;1*Rechnungen!D1:D40000)));"") '(CSE)
S5 =WENNFEHLER(1/(1/MAX(WENN(B5=Rechnungen!B3:B40000;1*Rechnungen!D3:D40000)));"") '(CSE)