Wenden Sie Funktionen wie MEDIAN auf einzelne Elemente in einer Matrixformel - Excel

stimmen
1

Ich bin mit der MEDIAN-Funktion, die Überlappung von zwei Datumsbereichen zu berechnen. Die Formel sieht ungefähr so ​​aus

=MEDIAN(A18,C18+1,$C$8+1)-MEDIAN(A18,C18+1,$B$8)

A18& C18Sind Datumsbereich 1

B8 & C8 Sind Datumsbereich 2

Ich möchte das in eine Matrixformel drehen ähnlich wie dies die Ergebnisse für mehrere Zeilen zu summieren

{=SUM(MEDIAN(A18:A24,C18:C24+1,$C$8+1)-MEDIAN(A18:24,C18:24+1,$B$8))}

Dies funktioniert nur , wenn MEDIAN auf jedes Element in der Arrays separat angelegt wird A18:A24& C18:C24.

Derzeit verkettet Excel die Arrays in jedem MEDIAN Anruf enthalten ist, und gibt den Gesamt Median, so dass nur ein Wert für SUM zu summieren. Und das ist das ‚falsche‘ Ergebnis für das, was ich tun muß.

Gibt es eine Möglichkeit Excel zwingen MEDIAN zu jedem Elemente in meinem Arrays anwenden? Ie ist es eine Möglichkeit, zu erzwingen Excel ein Array von der MEDIAN Funktion zurückzukehren.

Wenn dies möglich ist es mir erlaubt, eine 120.000-Element-Array oder einen Makro aktiviert Arbeitsmappe zu vermeiden.

Veröffentlicht am 20/10/2018 um 12:32
quelle vom benutzer
In anderen Sprachen...                            


1 antworten

stimmen
0

Kühle MEDIANFormel. Aber man kann nicht verwendet werden, MEDIANwie Sie mit einer Matrixformel wünschen , da MEDIANimmer einen gibt einzelnes Ergebnis (kein Array).

Ich habe eine Lösung für Ihr Problem, aber es ist etwas träge / respetitive:

(Ich habe Kommentare rechts von der Formel enthält, zu erklären, was passiert ist)

= IF(C18:C24>C8,     // (For each cell in C18:C24) If the value is greater than C8
     C8,             // Then C8 is the upper bound
     C18:C24)        // Else, the specific value from C18:C24 is the upper bound
  -                  // (minus sign)
  IF(A18:A24<B8,     // (For each cell in A18:A24) If the value is less than B8
     B8,             // Then B8 is the lower bound
     A18:A24)        // Else, the specific value from A18:A24 is the lower bound

Dies gibt eine Reihe von Überschneidungen von Datumsbereichen, außer dass es möglich ist , dass diese Ergebnisse negative Zahlen zurück (wenn eine Überlappung nicht auftritt), aber in diesem Fall mögen Sie zurückkommen 0.

Sie können mit einer anderen diesem Problem umgehen IFAussage.

= IF(formula < 0, 0, formula)

Wo formulaist die erste Formel I oben haben.

Randbemerkung: Normalerweise in dieser Situation würden Sie in der Lage zu vermeiden , die Eingabe aus formulazweimal mit etwas wie folgt aus :

= MAX(formula,0)

Außer das wird nicht funktionieren , wenn die formulaselbst ein Array zurückgibt , da MAXnur ein einziges Ergebnis zurückgibt. Dies ist ein ähnliches Problem mit dem Sie erleben MEDIANin dem ersten Platz.

Alles in allem ohne Kommentare, das ist die Formel, die Werte zu summieren:

= SUM(IF((IF(C18:C24>C8,C8,C18:C24)-IF(A18:A24<B8,B8,A18:A24))<0,0,
          IF(C18:C24>C8,C8,C18:C24)-IF(A18:A24<B8,B8,A18:A24)))

Natürlich ist dies eine Matrixformel ist, muss mit eingegeben werden Ctrl+ Shift+ Enterstatt nur Enter.

Beantwortet am 20/10/2018 um 16:11
quelle vom benutzer

Cookies help us deliver our services. By using our services, you agree to our use of cookies. Learn more