Frage Summe von vlookup mit Array-Formel


Ich habe solche Tabelle von Zahlungen:

account | amount
--------+-------
  101   | 3
  101   | 5
  102   | 7
  103   | 9

Ich habe den Bereich dieser Tabelle "Zahlungen" genannt. In der ersten Spalte habe ich "Accounts" genannt. In der zweiten Spalte nannte ich "Mengen".

Ich habe auch eine andere Tabelle, die Accounts Gruppen zuweist:

account | group
--------+-------
  101   | 1
  102   | 1
  103   | 2

Ich nannte den Bereich dieser Tabelle "Gruppen".

Jetzt möchte ich alle Zahlungen von Konten der Gruppe 1 mit nur einer Formel summieren, ohne zusätzliche Spalten zu verwenden. Ich versuche das mit einer solchen Array-Formel zu tun: sum (if (vlookup (accounts, groups, 2, false) = 1, Mengen, 0)) Ich habe auch eine solche Array-Formel ausprobiert: sumif (if (vlookup (accounts, groups, 2, false) = 1, Beträge, 0))

Diese Formeln funktionieren nicht. Ich denke, ich weiß warum - es scheint, dass die Vlookup-Funktion Array nicht zurückgibt, wenn sie in einer Zellenfeldformel verwendet wird.

Also, wie kann ich es mit einer Formel berechnen?


3
2018-01-30 04:55


Ursprung




Antworten:


Es scheint mir, dass Sie mit der folgenden Formel erreichen können, wonach Sie suchen:

=SUMPRODUCT(B1:B4*(LOOKUP(A1:A4,C1:C3,D1:D3)=1))

Das =1 bezieht sich auf die Gruppennummer, nach der Sie die Konten suchen. Beachten Sie, dass dies eine reguläre Formel und keine Matrixformel ist.

Ich habe reguläre Zellbezüge im Gegensatz zu benannten Regionen verwendet, weil ich denke, dass es einfacher ist, die Formel mit dem XLS-Blatt in Beziehung zu setzen.

Der folgende Screenshot zeigt, wo sich die verschiedenen Werte befinden und enthält eine etwas allgemeinere Formel, mit der Sie die gleiche Berechnung für verschiedene Gruppen durchführen können, indem Sie die untere rechte Ecke der Zelle ziehen F1.

Screenshot showing values and more generic function

Wenn Sie bei der Verwendung von Namen bleiben möchten, müssen Sie verschiedene Namen für die Spalten mit Konten eingeben. Die Formel würde so aussehen:

=SUMPRODUCT(Payments*(LOOKUP(Accounts1,Accounts2,Groups)=1))

Aus Gründen der Vollständigkeit, überprüfen Sie Wie Sie die Funktion VERWEIS in Excel verwenden für die Bedingungen, unter denen Sie verwenden können LOOKUP.


3
2018-01-30 08:28



Die Verwendung von LOOKUP ist ein gültiger Ansatz, Reinier, aber es gibt einige mögliche Nachteile - erstens für LOOKUP um korrekt zu arbeiten, muss der "Suchbereich" [C1: C3] aufsteigend sortiert werden (Beispiel, aber ich weiß nicht, ob das garantiert werden kann), und zweitens, wenn einer der Accounts1-Codes dann nicht in der accounts2-Liste erscheint Sie würden entweder einen Fehler oder eine "falsche Übereinstimmung" erhalten - mein Ansatz hat diese potenziellen Nachteile nicht - barry houdini
Es funktioniert, großartig, danke! - user983447
@barryhoudini Für die Problemstellung, LOOKUP ist die richtige Funktion. Und denken Sie nicht, dass die resultierende Formel eleganter und besser wartbar ist als zum Beispiel Ihre? Aber aus pädagogischer Sicht hast du recht, ich hätte das erwähnen sollen. Ich habe die Antwort erweitert. - Reinier Torenbeek
@ user983447 Ich bin froh zu helfen. Sehen Sie sich den Link, den ich hinzugefügt habe, oder den Kommentar von Barry Houdini an, unter welchen Bedingungen dieser Ansatz verwendet wird LOOKUP funktioniert. - Reinier Torenbeek
eleganter und besser wartbar? Ja, definitiv, gute Antwort! Vielleicht habe ich versucht, mit nicht existierenden Problemen fertig zu werden - barry houdini


Ja du hast Recht, VLOOKUP gibt kein Array zurück, so dass Sie einen anderen Ansatz benötigen. Angenommen, die erste Spalte der Gruppen wird aufgerufen accounts2 und zweite Spalte ist numbers dann probiere diese Array-Formel aus,

=SUM(IF(ISNUMBER(MATCH(accounts,IF(numbers=1,accounts2),0)),amounts))

bestätigen mit STRG+VERSCHIEBUNG+EINGEBEN


2
2018-01-30 08:39



Soweit ich sehen kann, funktioniert es nicht. - user983447
Haben Sie STRG + UMSCHALT + EINGABE gedrückt? Wenn Sie diese Tastenkombination verwenden, erhalten Sie {und} um die Formel herum und hoffentlich auch das korrekte Ergebnis - barry houdini