INDEX Match zwischen 2 Daten

[LCohen]

Hier allgemein dargestellt: =RECHNUNG_POS_DEFAULT(A4;B4;C4;A1:C2;0;1)

office-hilfe.com/support/threads/rechnung-mit-positionen-und-defaultwert-vervollstaendigung.58479/
(falls noch fehlerhaft, bitte dort gucken)

1) Der Ausnahmenbereich kann genauso auch zum Regelbereich werden (dann lückenlos bestimmt)
2) Es werden die Positionen zusätzlich gebildet, also pro Zeile am Beispiel USD x Tage = Positionsbetrag
3) Es ist die Ausgabe der (1) Gesamttabelle, (2) Durchschnittssatz (hier im Thread: USD), (3) Summe der Positionen möglich
4) Anstelle von Datümern kann man auch andere ganzzahlige Bereiche nehmen
5) Ungefragt habe ich noch eine MwSt- und eine Rechnungsbetrag- hinter der Summenzeile angehängt, die man (alle drei) nach Belieben auch WEGLASSEN() kann.

[snb]

Einfaches UDF:

PHP-Code:

=F_snb($A$4:$B$4;$E$4:$G$5;$C$2) 


Code:

Function F_snb(sn, sp, t)
  n = sp(1, 1)
  For j = sn(1, 1) To sn(1, 2)
    For jj = n To sp(2, 2)
      If j = jj Then Exit For
    Next
    y = y + IIf(jj <= sp(1, 2), sp(1, 3), IIf(jj >= sp(2, 1), sp(2, 3), t))
    n = jj + 1
  Next
 
  F_snb = y / (sn(1, 2) - sn(1, 1) + 1)
End Function

[neopa]

Hallo maninweb,

ich bin dir noch eine Antwort schuldig auf Deine Frage in #9. Sorry. Ich konnte jedoch erst heute Nachmittag wieder online gehen.

Nein, es ist ganz leicht meine in #7 eingestellte Formel für 2 auf 3 Raten zu erweitern. Lediglich meine dortige Auswertungsformel würde dann entsprechend etwas länger. Dies hier darzustellen, verzichte ich allerdings. Denn bevor evtl. die Frage nach weiteren auszuwertenden Raten gestellt wird, habe ich jetzt meine Auswertung allgemein erweitert und dabei gleichzeitig die Formellänge verkürzt. Dafür notwendig wurde lediglich eine weitere uralt XL-Funktion und (in meiner älteren XL-Version) zwei weitere kleine benannte Formeln, welche in XL365 wohl sicherlich einfach durch LET() ersetzt werden könnten.

Mit meiner nachfolgenden Formeldefnition können akt. bis zu 95 Raten ausgewertet werden, die zudem in B5:C99 auch unsortiert angeordnet sein können. Natürlich sollten es aber keine zeitliche Überlappungen zwischen den einzelnen Zeitdaten geben.

Arbeitsblatt mit dem Namen 'Tabelle2'
	A	B	C	D	E
2	Tage:	Von	Bis	Wert	Ergebnis Ø:
3		02.01.2023	24.02.2023	7	9,99
4
5	Rate1	05.01.2023	11.01.2023	13
6	Rate2	15.01.2023	20.01.2023	9,5
7	Rate3	24.01.2023	06.02.2023	12,3
8	Rate4	09.02.2023	15.02.2023	11,3
9	Rate …
10	…

Name	Bezug
_Raten	=MTRANS(!$D$5:$D$99)
_RatenTage	=(_Tage>=MTRANS(!$B$5:$B$99))*(_Tage<=MTRANS(!$C$5:$C$99))

Zelle	Formel
E3	=(SUMMENPRODUKT(_RatenTage*_Raten)+(C3-B3+1-SUMMENPRODUKT(_RatenTage))*D3)/(C3-B3+1)

Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

Bitte beachten: In der obigen Darstellung fehlt noch die bereits in #7 benannte Formel: _Tage:=ZEILE(INDEX(!$A:$A;!$B$3):INDEX(!$A:$A;!$C$3)) Da diese benannte Formel lediglich für die gelisteten zwei benannten Formeln als Teilargumente (somit aber in der 2. "Unterebene" liegen) benötigt werden, werden diese durch das Tool von Gerd Bamberg leider nicht gelistet. Für die Auswertung ist sie natürlich auch zwingend notwendig zu defnieren.

[maninweb]

Hallo neopa,

Deine Lösung ist klasse! Habe die mal so umgebaut, dass diese bei Excel 365 den Bereich zu den Raten dynamisch adressiert
und ohne Namen auskommt. Die Lösung braucht somit nur fünf Excel-Funktionen, inklusive LET.

PHP-Code:

E3=LET(vnStart; B3; vnEnde; C3; vnBasis; B5:D8; vnBasisStandard; D3; 
       vnZeitreihe; SEQUENZ(vnEnde - vnStart + 1; 1; vnStart); 
       vnMatrix; (vnZeitreihe >= MTRANS(INDEX(vnBasis; 0; 1))) * (vnZeitreihe <= MTRANS(INDEX(vnBasis; 0; 2))); 
       vnErgebnis; (SUMMENPRODUKT(vnMatrix * MTRANS(INDEX(vnBasis; 0; 3))) + (vnEnde - vnStart + 1 - SUMMENPRODUKT(vnMatrix)) * vnBasisStandard) / (vnEnde - vnStart + 1); 
       vnErgebnis) 


Gruß

[LCohen]

Ich MTRANSe Datensätze nicht so gern, weil es dann nur 16384 sein dürfen.

[snb]

Froh mit VBA: ohne Beschränkungen.

PHP-Code:

=F_snb($A$1:$B$1;$E$1:$G$5) 


UDF

Code:

Function F_snb(sn, sp)
  y = sp(1, 3) * (sn(1, 2) - sn(1, 1) + 1)
  For j = sn(1, 1) To sn(1, 2)
    For jj = 2 To sp.Rows.Count
      y = y + (j >= sp(jj, 1)) * (j <= sp(jj, 2)) * (sp(jj, 3) - sp(1, 3))
    Next
  Next
 
  F_snb = y / (sn(1, 2) - sn(1, 1) + 1)
End Function

[maninweb]

Hallo,

@LCohen: die Beschränkung scheint mir zumindestens teilweise aufgehoben bzw. auf die maximale Zeilenanzahl erweitert, wenn man MTRANS nur zum Rechnen verwendet.

PHP-Code:

=SUMME(MTRANS(SEQUENZ(1048576;1;1;0))) 


Gruß

[LCohen]

Interessant ... hatte es nicht ausprobiert. Dann bliebe aber noch der Umstand, dass man möglichst nicht zu große MTRANS-Matrizen aufspannen sollte. Die frühere quadratische Limitierung war ungefähr 5000 x 5000 per MTRANS (genaues Limit steht bei Excelformeln oder bei XLimits).