3
Matrixformel in Google Tabellen mit mehreren Bedingungen
in Tabelle1 sind je Zeile ein angebotenes Produkt aufgelistet:
AP, GPm, GPj, Name, min, max
Daten-von-Produkt1
Daten-von-Produkt2
Daten-von-Produkt3
Daten-von-Produkt4
...
Tabelle2 soll sich aus Tabelle1 (möglichst weitgehend) dynamisch aufbauen/generieren.
VerbrauchA, Angebot1, Angebot2, Angebot3, Angebot4, ...
VerbrauchB, Angebot1, Angebot2, Angebot3, Angebot4, ...
VerbrauchC, Angebot1, Angebot2, Angebot3, Angebot4, ...
VerbrauchD, Angebot1, Angebot2, Angebot3, Angebot4, ...
Aus Tabelle2 soll ein Diagramm erstellt werden.
Bislang habe ich es noch nicht geschafft, die Formeln/Funktionen für Tabelle2 zusammenzustellen.
Tabelle2 beginnt je Zeile mit einer Zahl: Verbrauch.
In Abhängigkeit von diesem Wert soll für jedes einzelne Produkt aus Tabelle1 in dieser Zeile in Tabelle2 jeweils ein einzelner Wert berechnet werden.
Überlegungen dazu:
Spaltenköpfe der Tabelle2:
Verbrauch, Produkt1, Produkt2, Produkt3, ...
Je Zeile in Tabelle2:
einige Beispiele:
a) 0, 107, 119, 124
b) 500, 254, 289, 260
c) 1699, 609, 697, 585
d) 1700, 609, 698, 585
Details für Tabelle2:
Der erste Wert (Verbrauch) bestimmt für einen Funktion f(x) das x.
Der Spaltenkopf bestimmt die auszuwählende Funktion f - tatsächlich sind es nur zwei zusetzende Parameter a und b für die sonst immer gleiche Funktion f:
f(Verbrauch,a,b) = (a*Verbrauch/100) + b
In Tabelle1 gibt es je Produkt_n auch noch die Kriterien Mindestverbrauch und Maximalverbrauch.
Also, nur wenn (min <= Verbrauch <= max) erfüllt, dann dürfen die Werte AP für a und GP für b aus der Tabelle1 genommen werden und somit ein Wert für eine Spalte in der Zeile in Tabelle2 berechnet werden, sonst: 0, bzw. kein Wert.
Meine Probleme:
Ich suche nach eine Formel, der ich zwei Parameter geben kann - Verbrauch und ProduktNamen.
Die Formel soll
a) mit dem ProduktNamen
UND
b) der Einschränkung was den Verbrauch angeht
dann zwei Werte erhalten, AP und GP, und mit diesen beiden Werten UND dem Verbrauch einen Wert für die Zelle in Tabelle2 berechnen.
Ich habe es in Google Tabellen (Formel-Sprache englisch) oder in Excel (Formel-Sprache deutsch) zu realisieren - aus Sicherheitsgründen ohne Makros.
bislang schaute ich mir in Google Sheets an:
vlookup()
hlookup()
dget()
ArrayFormula()
Index()
Und in Excel 2013:
sverweis()
{Matrixfunktionen}
AP, GPm, GPj, Name, min, max
Daten-von-Produkt1
Daten-von-Produkt2
Daten-von-Produkt3
Daten-von-Produkt4
...
Tabelle2 soll sich aus Tabelle1 (möglichst weitgehend) dynamisch aufbauen/generieren.
VerbrauchA, Angebot1, Angebot2, Angebot3, Angebot4, ...
VerbrauchB, Angebot1, Angebot2, Angebot3, Angebot4, ...
VerbrauchC, Angebot1, Angebot2, Angebot3, Angebot4, ...
VerbrauchD, Angebot1, Angebot2, Angebot3, Angebot4, ...
Aus Tabelle2 soll ein Diagramm erstellt werden.
Bislang habe ich es noch nicht geschafft, die Formeln/Funktionen für Tabelle2 zusammenzustellen.
Tabelle2 beginnt je Zeile mit einer Zahl: Verbrauch.
In Abhängigkeit von diesem Wert soll für jedes einzelne Produkt aus Tabelle1 in dieser Zeile in Tabelle2 jeweils ein einzelner Wert berechnet werden.
Überlegungen dazu:
Spaltenköpfe der Tabelle2:
Verbrauch, Produkt1, Produkt2, Produkt3, ...
Je Zeile in Tabelle2:
einige Beispiele:
a) 0, 107, 119, 124
b) 500, 254, 289, 260
c) 1699, 609, 697, 585
d) 1700, 609, 698, 585
Details für Tabelle2:
Der erste Wert (Verbrauch) bestimmt für einen Funktion f(x) das x.
Der Spaltenkopf bestimmt die auszuwählende Funktion f - tatsächlich sind es nur zwei zusetzende Parameter a und b für die sonst immer gleiche Funktion f:
f(Verbrauch,a,b) = (a*Verbrauch/100) + b
In Tabelle1 gibt es je Produkt_n auch noch die Kriterien Mindestverbrauch und Maximalverbrauch.
Also, nur wenn (min <= Verbrauch <= max) erfüllt, dann dürfen die Werte AP für a und GP für b aus der Tabelle1 genommen werden und somit ein Wert für eine Spalte in der Zeile in Tabelle2 berechnet werden, sonst: 0, bzw. kein Wert.
Meine Probleme:
Ich suche nach eine Formel, der ich zwei Parameter geben kann - Verbrauch und ProduktNamen.
Die Formel soll
a) mit dem ProduktNamen
UND
b) der Einschränkung was den Verbrauch angeht
dann zwei Werte erhalten, AP und GP, und mit diesen beiden Werten UND dem Verbrauch einen Wert für die Zelle in Tabelle2 berechnen.
Ich habe es in Google Tabellen (Formel-Sprache englisch) oder in Excel (Formel-Sprache deutsch) zu realisieren - aus Sicherheitsgründen ohne Makros.
bislang schaute ich mir in Google Sheets an:
vlookup()
hlookup()
dget()
ArrayFormula()
Index()
Und in Excel 2013:
sverweis()
{Matrixfunktionen}
Share on Facebook
Share on Twitter
Share on Reddit
Share on LinkedinPlease also mark the comments that contributed to the solution of the article
Content-Key: 369861
Url: https://administrator.de/contentid/369861
Printed on: April 27, 2024 at 16:04 o'clock
3 Comments
Latest comment
Ich möchte aus einer Tabelle1 eine Zeile mit (mehreren) Werten auswählen für die Berechnung von Werten in Tabelle2.
Die Kriterien für die Auswahl aus der Tabelle1
soll zum einen mit dem jeweiligen Spaltenkopf der Tabelle2
und zum anderen mit dem Zeilenkopf der Tabelle2 erfolgen.
Also in Tabelle2 je Zelle:
f(Zeilenkopf, Spaltenkopf) = Zellenergebnis
Genauer:
f(Zeilenkopf, Verbrauch) = Zellenergebnis
Und im Spaltenkopf steht der Name des auszuwählenden Produktes drin.
Da für das Produkt je Abnahmemenge unterschiedliche Preise gelten können,
ist bei der Auswahl der richtigen Zeile mit den Richtigen Werten aus Tabelle1 auch der Wert Verbrauch mit entscheidend.
Die Kriterien für die Auswahl aus der Tabelle1
soll zum einen mit dem jeweiligen Spaltenkopf der Tabelle2
und zum anderen mit dem Zeilenkopf der Tabelle2 erfolgen.
Also in Tabelle2 je Zelle:
f(Zeilenkopf, Spaltenkopf) = Zellenergebnis
Genauer:
f(Zeilenkopf, Verbrauch) = Zellenergebnis
Und im Spaltenkopf steht der Name des auszuwählenden Produktes drin.
Da für das Produkt je Abnahmemenge unterschiedliche Preise gelten können,
ist bei der Auswahl der richtigen Zeile mit den Richtigen Werten aus Tabelle1 auch der Wert Verbrauch mit entscheidend.
Moin.
INDEX in Kombination mit MATCH bringt dich ans Ziel
https://support.microsoft.com/de-de/help/214142/how-to-use-the-index-and ...
Gruß schnuffi
P.s. Ganz nett ... Discussion guidelines - The rules for our content
INDEX in Kombination mit MATCH bringt dich ans Ziel
https://support.microsoft.com/de-de/help/214142/how-to-use-the-index-and ...
Gruß schnuffi
P.s. Ganz nett ... Discussion guidelines - The rules for our content
Moin schnuffi,
vielen Dank für Deine Gedankenansätze.
Mit INDEX() kann ich aus einer bestehenden Tabelle mit jeweils bekannten x (Spalte) und y (Zeile) Werten mir eine Position innerhalb einer Tabelle bestimmen lassen.
Da och allerdings die Herausforderung hatte, dass eben einer der Werte nicht genau-gleich ("=") ist, sondern nur innerhalb eines Bereichs drin liegen sollte (min <= Verbrauch <= max) kam ich an dieser Stelle nicht weiter.
Auch MATCH() habe ich mir genauer angeschaut.
Tatsächlich wurde es schließlich die Funktion DGET().
Denn bei DGET konnte ich (zumindest in Google Tabellen) mir meine eigenen Suchkriterien "bauen".
=iferror(DGET($A$3:$G$10;"brAP";
Doch der Reihe nach.
Ich habe die Tabelle mit den Produktwerten hinterlegt in A3:G10.
Ich lasse zuerst mir den Wert brAP (brutto Arbeitspreis) heraussuchen.
Und dann kam ein Kunstgriff:
Die zwingend-genau-zweizeilige-Tabelle baute ich mir innerhalb der Funktion selbst zusammen: Mit "{" und "}".
Angefangen mit der 1.Suchspalte (E3) und übergeben den Suchwert J19 aus der Tabelle2.
Jedes Suchkriterium besteht aus-dem-Namen-der-zu-durchsuchenden-Spalte und dem Suchwert, getrennt voneinander mit einem ";", zusammengefasst durch "{" und "}".
Um weitere Suchkriterien hinzuzufügen habe ich diese verbunden mit einem "\".
Also: ...{"Spaltenkopf1";"Suchwert1"}\{"Spaltenkopf2";"Suchwert2"}\{"Spaltenkopf3";"Suchwert3"}...
Und dann folgte ein weiterer Kunstgriff, da ich innerhalb eines Bereiches suchen musste.
Innerhalb der Werte MINIMALWERT verglich ich mit "<=" und verband diesen Operator mit "&" links vor den Suchwert.
Innerhalb der Werte MAXIMALWER verglich ich mit ">=" und verband diesen Operator mit "&" links vor dem Suchwert.
Schließlich folgte der letzte Kunstgriff:
Für manche Produkte gibt es keine Angebot, also kein berechenbares Ergebnis.
Diesen Fehler habe ich abgefangen mit IFERROR().
Die Funktion arbeitet mit zwei Argumenten ganz einfach:
IFERROR(Ausdruck;Fehlerfall)
Wenn der Ausdruck ausgewertet werden kann, dann wird dessen Ergebnis angezeigt.
Wenn der Ausdruck zu einem Fehler führt, dann wird angezeigt was man als Fehlerfall angibt.
Ich habe für den Fehlerfall den Wert "0" angegeben.
Die oben angegebene Funktion ist eine von dreien.
Die angegebene Funktion bestimmt den zu verwendenden Arbeitspreis - x.
Eine nahezu identische Funktion bestimmt mir den zu verwendenden jährlichen Grundpreis - y.
Und die dritte nahezu identische Funktion bestimmt mir den zu verwendenden möglichen Bonus - z.
Der Wert x multipliziert mit dem Verbrauch plus y plus z: Das gesuchte Ergebnis.
Diese drei Funktionen lassen sich nebeneinander kopieren.
Und das lässt sich wiederum über mehrere Zeilen nach untern kopieren.
Somit habe ich eine nahezu dynamische Tabelle2.
Für mich war sehr hilfreich diese Infos:
https://productforums.google.com/forum/#!topic/docs/JCHRKxAxkyY
speziell: Alexander Ivanov und AD:AM
und:
https://www.youtube.com/watch?v=0-J56MyJshQ&t=207s
vielen Dank für Deine Gedankenansätze.
Mit INDEX() kann ich aus einer bestehenden Tabelle mit jeweils bekannten x (Spalte) und y (Zeile) Werten mir eine Position innerhalb einer Tabelle bestimmen lassen.
Da och allerdings die Herausforderung hatte, dass eben einer der Werte nicht genau-gleich ("=") ist, sondern nur innerhalb eines Bereichs drin liegen sollte (min <= Verbrauch <= max) kam ich an dieser Stelle nicht weiter.
Auch MATCH() habe ich mir genauer angeschaut.
Tatsächlich wurde es schließlich die Funktion DGET().
Denn bei DGET konnte ich (zumindest in Google Tabellen) mir meine eigenen Suchkriterien "bauen".
=iferror(DGET($A$3:$G$10;"brAP";
$E$3;J$19}\{$F$3;"<="&$I21}\{$G$3;">="&$I21);0)Doch der Reihe nach.
Ich habe die Tabelle mit den Produktwerten hinterlegt in A3:G10.
Ich lasse zuerst mir den Wert brAP (brutto Arbeitspreis) heraussuchen.
Und dann kam ein Kunstgriff:
Die zwingend-genau-zweizeilige-Tabelle baute ich mir innerhalb der Funktion selbst zusammen: Mit "{" und "}".
Angefangen mit der 1.Suchspalte (E3) und übergeben den Suchwert J19 aus der Tabelle2.
Jedes Suchkriterium besteht aus-dem-Namen-der-zu-durchsuchenden-Spalte und dem Suchwert, getrennt voneinander mit einem ";", zusammengefasst durch "{" und "}".
Um weitere Suchkriterien hinzuzufügen habe ich diese verbunden mit einem "\".
Also: ...{"Spaltenkopf1";"Suchwert1"}\{"Spaltenkopf2";"Suchwert2"}\{"Spaltenkopf3";"Suchwert3"}...
Und dann folgte ein weiterer Kunstgriff, da ich innerhalb eines Bereiches suchen musste.
Innerhalb der Werte MINIMALWERT verglich ich mit "<=" und verband diesen Operator mit "&" links vor den Suchwert.
Innerhalb der Werte MAXIMALWER verglich ich mit ">=" und verband diesen Operator mit "&" links vor dem Suchwert.
Schließlich folgte der letzte Kunstgriff:
Für manche Produkte gibt es keine Angebot, also kein berechenbares Ergebnis.
Diesen Fehler habe ich abgefangen mit IFERROR().
Die Funktion arbeitet mit zwei Argumenten ganz einfach:
IFERROR(Ausdruck;Fehlerfall)
Wenn der Ausdruck ausgewertet werden kann, dann wird dessen Ergebnis angezeigt.
Wenn der Ausdruck zu einem Fehler führt, dann wird angezeigt was man als Fehlerfall angibt.
Ich habe für den Fehlerfall den Wert "0" angegeben.
Die oben angegebene Funktion ist eine von dreien.
Die angegebene Funktion bestimmt den zu verwendenden Arbeitspreis - x.
Eine nahezu identische Funktion bestimmt mir den zu verwendenden jährlichen Grundpreis - y.
Und die dritte nahezu identische Funktion bestimmt mir den zu verwendenden möglichen Bonus - z.
Der Wert x multipliziert mit dem Verbrauch plus y plus z: Das gesuchte Ergebnis.
Diese drei Funktionen lassen sich nebeneinander kopieren.
Und das lässt sich wiederum über mehrere Zeilen nach untern kopieren.
Somit habe ich eine nahezu dynamische Tabelle2.
Für mich war sehr hilfreich diese Infos:
https://productforums.google.com/forum/#!topic/docs/JCHRKxAxkyY
speziell: Alexander Ivanov und AD:AM
und:
https://www.youtube.com/watch?v=0-J56MyJshQ&t=207s