Führen Sie die Suche nach jeder Zelle in einem Bereich mit Excel (für Mac) - Excel, Excel-Formel, MS-Office, Excel-2016

Eine Zeile in Excel mit einer Variablennummer von gegebenSpalten, die mit Artikelnummern gefüllt sind, würde ich gerne in einer anderen Tabelle suchen, die die Kosten für jeden Artikel in den aufgefüllten Spalten auflistet, und die Gesamtkosten summieren. Mein Google-Fu hat mich im Stich gelassen, da ich scheinbar keinen finde Möglichkeit, eine Excel-Formel (SVERWEIS in diesem Fall) für jede Zelle in einem Bereich auszuführen. Grundsätzlich, wenn ich folgende Tabellen habe:

ArtikelKosten Item | Cost A | 10 B | 20 C | 40

Aufträge Order | 1 | A 2 | A | B 3 | B | C

Wie kann ich die Summe in der folgenden Tabelle mit einer Bestellnummer berechnen?

Bestellübersicht Order | Total 1 | 10 2 | 30 3 | 60

Ich habe es geschafft, einige Formeln zu schreiben, die den Lookup-Bereich im Formular generieren Orders!$B$2:$C$2. I thought I could use that range as the input zu einer Formel, um die Artikel in der Reihenfolge ihren Artikelkosten zuzuordnen und sie zu summieren. Ich hatte gehofft, ich könnte einen SVERWEIS auf jeden Gegenstand in der Reihe durchführen und es könnte einen Bereich von der ItemCosts Tabelle, die ich dann summieren könnte, aber SVERWEIS nimmt nur eine einzelne Zelle als Lookup-Wert. Ich fühle mich, als würde ich etwas Offensichtliches vermissen. Soll ich es einfach in VBA machen? Diese Antwort erwähnt, dass SVERWEIS ein Array nehmen und dann ein Array zurückgeben kann, aber ich konnte es nicht in Excel 2016 für Mac reproduzieren. Es gibt nur die erste Übereinstimmung als einen einzelnen Wert zurück.

EDIT:Aufgrund der Kommentare scheint die unnormalisierte Orders-Tabelle ein Problem zu sein. Aber selbst wenn es so aussieht, ist es mir nicht klar, wie ich die Kosten jedes Artikels nachschlagen soll, damit ich die Summe berechnen kann.

Aufträge Order | Item 1 | A 2 | A 2 | B 3 | B 3 | C

Antworten:

1 für die Antwort № 1

Es gibt zwei Möglichkeiten, um Ihre benötigten zu produzierenAusgabe mit "Standardformeln", die ich vorschlagen werde. Ich stelle mir vor, dass es möglicherweise eine Array-Funktion gibt, die dies ohne Hilfssäulen / -blätter lösen könnte, aber sie sind in der Regel komplex und schwer zu warten.

Für die normalisierte Version könnten Sie Folgendes tun:

1) Fügen Sie der Tabelle orders eine Wertespalte hinzu:

"Column of Cost in ItemCost Table" = ColumnCost
"Column of Item in ItemCost Table" = ColumnItem

Order | Item | Value
1     | A    |=index(ColumnCost, match(ItemCell, ColumnItem, 0))
2     | A    |...
2     | B    |...
3     | B    |...
3     | C    |...

2) Verwenden Sie eine Summenformel, um die Spalte Wert in der Auftragstabelle unter Berücksichtigung der Auftragsnummer zu summieren.

Für die nicht normalisierte Version anstelle einer Hilfssäule können Sie ein Hilfsbogen verwenden, das die gleiche Form und die gleichen Abmessungen wie das Bestellformular hat.

Verwenden Sie die gleiche Struktur der Indexformel wie im obigen Beispiel, aber anstatt die ItemCell im Auftragsblatt zu verwenden. Es wird wie folgt aussehen:

Aufträge

Order |
1     | A
2     | A | B
3     | B | C

Aufträge Helfer

Order |
1     | 10
2     | 10 | 20
3     | 20 | 40

Für das Übersichtsblatt summieren Sie einfach die entsprechende Spalte.

Ein Beispiel dafür finden Sie hier: https://ufile.io/rbxf1