Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1492to1496
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Ergebniss in Abh. von Wert und Spalte

Ergebniss in Abh. von Wert und Spalte
19.05.2016 15:59:08
Wert
Hallo,
ich stehe mal wieder völlig auf dem Schlauch.
Ich habe ein Beispiel beigefügt und hoffe das ich mein Problem
einigermaßen pausibel gemacht habe.
https://www.herber.de/bbs/user/105670.xlsx
Wahrscheinlich gibt es eine einfachere Lösung als ich angedacht habe.
Besten DANK schon einmal!
Jürgen

23
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Ergebniss in Abh. von Wert und Spalte
19.05.2016 16:05:08
Wert
Hola,
in deinem Beispiel gibt es pro Zeile nur einen Wert, ist das in der Originaldatei auch so?
Gruß,
steve1da

AW: Ergebniss in Abh. von Wert und Spalte
19.05.2016 16:16:31
Wert
Hallo steve1da,
NEIN, siehe Zeile 11 Sp. Y, gibt es 30,94, Gesamtsumme ist aber 126,62.
Deswegen muss dann die 2016/22 dort stehen.
Es könnte also in Z11 SP X der Betrag 95,68 stehen.
Gruss
Jürgen

AW: Ergebniss in Abh. von Wert und Spalte
19.05.2016 16:24:04
Wert
Hola,
wenn in Z11 aber bspw. 95,68 steht, dann wäre das Ergebnis doch 2016/23, oder nicht?
Gruß,
steve1da

AW: Ergebniss in Abh. von Wert und Spalte
19.05.2016 16:34:19
Wert
Nein, in der Spalte mit 2016/23 steht nix.
In 2016/22 stehen 30,94
In 2016/21 stehen 95,68
Ich benötige immer die höchste KW (2016 = Jahr / 22 = KW)
Gruss
Jürgen
P.S. Muss jetzt weg, schaue morgen wieder rein!

Anzeige
AW: Ergebniss in Abh. von Wert und Spalte
19.05.2016 16:09:41
Wert
Hallo Jürgen,
probiers mal damit:
=INDIREKT(ADRESSE(2;VERGLEICH(AI4;$A4:$AB4)))
Servus

AW: mit einer Matrixfunktion(alität)sformel ...
19.05.2016 16:35:02
...
Hallo Jürgen,
... und zwar auch ohne Hilfsspalte und es können dann in einer Zeile auch mehrere Werte stehen.
in AE3:

=WENNFEHLER(VERWEIS(9;1/(X4:AB40);X$2:AB$2);"")
und Formel nach unten kopieren,
Gruß Werner
.. , - ...

AW: mit einer Matrixfunktion(alität)sformel ...
19.05.2016 16:38:02
Herbert
Hallo Werner,
meine Formel bringt das gleiche Ergebnis wie Deine, nur unkomplizierter. Aber anscheinend hat er meine noch gar nicht getestet!
Servus

Anzeige
AW: drei Unterschiede gibt es schon ...
19.05.2016 16:49:38
...
Hallo Herbert,
... wenn auch nicht im Ergebnis.
Meine Formel gibt ein="" bei Fehlwerten, sie kommt ohne INDIREKT() und ohne die Hilfsspalte AI aus.
Wenn die Hilfsspalte AI gewünscht ist, dann hätte ich angeboten:

=WENNFEHLER(INDEX(X$2:AB$2;VERGLEICH(AI5;X5:AB5));"")

(INDIREKT() meide ich immer, wenn nur irgend möglich)
Aber wie auch immer, natürlich entscheidet das richtige Ergebnis, teils aber auch wie man es erzielt.
Gruß Werner
.. , - ...

AW: drei Unterschiede gibt es schon ...
19.05.2016 16:59:01
Herbert
Hallo Werner,
das mit "WENNFEHLER" habe ich gedacht, dass er da schon selber drauf kommt! Und ohne Hilfsspalte muss er eben seine Formel mit einbauen, oder ähnl.
"(INDIREKT() meide ich immer, wenn nur irgend möglich)" Ich auch! Doch in Verbindung mit ADRESSE ist sie ja wieder flexibel.
Servus

Anzeige
AW: INDIREKT() ist volatil und zudem ...
19.05.2016 17:35:30
Herbert
Danke Dir, Werner!
Servus

Anzeige
AW: drei Unterschiede gibt es schon ...
19.05.2016 17:02:32
Herbert
Hallo Werner,
was ich bei Deiner Formel nicht kapiere ist das "Suchkriterium" 9? Was bedeutet das? Ich habe auch in der "Hilfe" nix dazu gefunden.
Servus

AW: die 9 als Suchkriterium ...
19.05.2016 17:25:21
...
Hallo Herbert,
... muss nur eine Zahl größer 1 sein (ich nehme da immer die 9), kann also auch 1,001 oder 9876543421 sein.
Diese wird auf die Teil-Matrix von 1/(X3:AB30) "angesetzt". Die Teilmatrix ist eine Matrix von Fehlerwerten #DIV/0! und evtl. einer oder mehrerer 1sen. Die darin letzte (weil VERWEIS()!) gefundene 1 übergibt dessen Position in der Matrix an den Ergebnisvektor X$2:AB$2 und bestimmt damit das Ergebnis. Entscheidend dabei ist, dass VERWEIS() Fehlerwerte in einer Auswertungsmatrix toleriert bzw. besser geschrieben ignoriert ohne deren Position zu "verschlucken".
AGGREGAT() kann auch auch analog Fehlerwerte ignorieren, allerdings nuss man diese der Funktion über ein entsprechendes Zusatzargument (die 6) erst kundtun.
Die analoge AGGREGAT()-Formel würde hier z.B. so aussehen:

=WENNFEHLER(INDEX($2:$2;AGGREGAT(14;6;SPALTE(X1:AB1)/(X3:AB30);1));"")
Gruß Werner
.. , - ...

Anzeige
AW: die 9 als Suchkriterium ...
19.05.2016 17:34:43
Herbert
Danke vielmals!
Servus

wer hats erfunden ...
19.05.2016 19:38:21
lupo1
http://excelformeln.de/formeln.html?welcher=48
Quellen sind eine Zier, allein man lebt gut ohne ihr. Vielleicht haben die es ja schon vorher aus dem amerikanischen Raum übernommen. Wer das wohl überhaupt als erster herausgefunden hat ...

AW: wer hats erfunden ...
20.05.2016 08:41:22
Jürgen
Hallo Herbert, hallo Werner,
ich muss meine EXCEL Einschätzung wohl deutlich nach untern korrigieren, was Ihr hier schreibt ist für mich dann doch ein Buch mit 7 Siegeln.
Habe mich entschlossen mal die kplt. Tabelle reinszstellen.
Erläuterungen stehen unten drunter.
Ich finde das ja richtig toll was ihr hier so ZAUBERT!
Vielleicht könnt ihr euch das ja noch einmal ansehen.
https://www.herber.de/bbs/user/105680.xlsx
Gruss
Jürgen

Anzeige
AW: hat und hätte er so sicherlich auch nicht ...
20.05.2016 18:12:25
...
Hallo Walter,
... lupo1 hat lediglich auf den "Ursprung" des Formeltyps im deutschsprachigen Raum hingewiesen.
Mehr dazu siehe mein Beitrag dort. Deine Formelvorschlag führt zwar auch zum Ergebnis, nur eben über zwei kleine "Umwege" (Hilfsspalte und Kombination von INDEX() und VERWEIS(), wo doch VERWEIS() ausreichend ist). Dazu siehe mein Beitrag an Jürgen.
Gruß Werner
.. , - ...

Anzeige
AW: die gewünschte Anpassung ...
20.05.2016 18:12:44
...
Hallo Jürgen,
... meine hilfsspaltenfreien Formel an Deine nun vorliegende Datei ergibt sogar noch eine Formelvereinfachung. Denn Du willst nun offensichtlich auch keine Ausgabe, wo die Summe der Teilwerte die 1000 bereits überschreitet.
Deshalb jetzt in AE3: =WENNFEHLER(VERWEIS(9;1/(W30);E$2:V$2);"")
und Formel nach unten kopiere.
Gruß Werner
.. , - ...

AW: dazu vielleicht doch etwas mehr ...
20.05.2016 18:12:07
...
Hallo lupo,
... im deutschsprachigen Raum ist das sicherlich so, wie es in excelformeln.de steht: "wohl eine Gemeinschaftskreation von Frank Kabel, ..."
Gemeinschaftskreation trifft es wohl eher als "erfunden". Natürlich kann auch nicht ganz ausgeschlossen werden, dass damals eine gewisse, nennen wir es "Inspiration", aus dem englischsprachigen Raum vorlag.
Zu dieser Zeit war ich exceltechnisch gesehe, wenn überhaupt bestenfalls ein Embryo (hatt erste Erfahrungen mit Excel 2004) und musst mir auch erst die Wirkungsweise der Formel durch Nachfragen in Online Excel Forum erst pö a pö erschließen. Geschätzt seit ca. 2007 hab ich dann selbst auf dieser Basis Lösungen auf entsprechende Hilfeanfragen in Foren bereitstellen können und dabei u.a. neben FP und Sepp Burch einen Anteil an deren Verbreitung und teils auch "Weiterentwicklung" (Bedingungskombinationen) des Basisprinzips beigetragen.
Grundlage dafür war, dass ich mindestens ein dutzend Mal und wahrscheinlich mehr anderen Einsteigern diesen Formeltyp detailliert (auch ausführlicher als hier gegenüber Herbert) auf Nachfragen erläutert habe. So hatte ich mir vor Jahren auch entsprechend in Online Excel Forum begründet erlaubt, die 9 zu verwenden, natürlich nur dann, wenn es nicht die "Originalformel" war.
Bzgl. meiner Verwendung der 9 für die 2 als "Suchkriterium" gab es später auch "Nachahmer" wie z.B. shift del mit der 42 etc.).
Seit dem vorigem Jahr konnte ich meine diesbzgl. gesammelten Erfahrungen fast nahtlos übernehmen, als ich mehr zufällig auf die Funktion AGGREGAT() aufmerksam wurde. Kurze zeit danach hatte hatte ich auch mit "Matrixfunktion(alität)sformel" einen Oberbegriff für derartige Formeltypen geprägt, den ich seit her verwende. Mit diesen Begriff werden von mir nicht nur die spez. VERWEIS()- und AGGREGAT()- Formeln bezeichnet sondern alle Formeln, die auf Basis von Funktionen beruhen, die in einer Matrixversion anwendbar sind. Diese benötigen nicht den spez. Formelabschluss wie die "echten" Matrixformeln (erkennbar an den umschließenden {}, werten aber gleich oder ähnlich wie diese aus.
Gruß Werner
.. , - ...

Anzeige
Nun, das wird wohl so gewesen sein, ...
20.05.2016 23:25:26
Luc:-?
…Werner,
dass es tatsächlich eine internationale ZusammenArbeit unter MS-MVPs gab. Frank hat ja auch gemeinsam mit einem US-MVP das Pgm CFPlus für mehr als 3 BedingtFormatRegeln geschrieben. Leider hat das nicht wirklich bedingt formatiert, sondern direkt (sah nur so aus wie BedingtFormat), weil beide nicht darauf gekommen waren, dass man für eine HptRegel auch UnterRegeln formulieren kann, nach denen dann das Format der HptRegel ausgetauscht wird. Außerdem hat das Pgm noch andere Probleme bereitet und ist nach Franks frühem Tod auch nicht mehr gepflegt worden (zumindest die dt Version).
Aber du bist ja nun wieder auf eines unserer „liebsten“ Diskussions­(Streit)­Themen gekommen. Du weißt ja, dass ich deine Kategorie­Bezeichnung „Matrix­funk­tion(alität)s­formel“ toleriere, aber eigent­lich ist sie ja überflüssig, weil man nahezu alle Fmln dazu zählen könnte. Berechnungen von Daten in Zell­Bereichen und Daten­feldern (aus Ausdrücken als FktsArgument), also Matrizen und Vektoren (die ebenfalls wie Matrizen behandelt wdn → jeder Wert hat 2 Indizes, nur das ggf einer in der Angabe entfallen kann), sind die Grundlage der Xl-(Berechnungs­)Organisation. Einfache Fmln, die sich auf derartige Matrizen beziehen, wdn auch stets für alle Werte berechnet, weshalb es auch nicht erforderlich und bei benannten Fmln auch nicht möglich ist, dafür eine MatrixFml festzulegen. Diese ist allein eine Fktio­nalität des Tab­Blattes, also von ZellFmln, die idR nur festlegt, was angezeigt wdn soll. Bei kompli­zierteren Fmln regelt sie auch, welche Zwischen­Ergebnisse weiter­verwendet wdn sollen.
Kommen Fktt in einer Fml ins Spiel, kommt es nur darauf an, ob die Fkt - wie bspw INDEX - voll in die Xl-Standard-Berechnungs­Abläufe integriert ist oder mehr oder weniger autonom pgmmiert wurde. Bei letzterem tritt dann der von dir gemeinte Effekt auf. Er ist also an bestimmte Fktt (und ggf ihr Zusammen­spiel mit dem Rest der jeweiligen Fml) gebunden, nicht an Fmln schlechthin, denn die haben - s.oben! - (fast) alle diese Fktionalität. Deshalb gibt es MS-offiziell auch keine Fml-, sondern nur Fkts­Kategorien!
Es gibt einige wenige Xl-Standard­Fktt, die nur Einzel­werte verarbeiten können, die meisten können aber wenigstens im Hpt­Argument ganze Zell­Bereiche oder sogar Daten­felder verarbeiten (in skalaren Neben­Argumenten kann gewöhnlich über ein vorgegebenes Intervall variiert wdn). Ob das nun xl-grundlagen-konform über die MatrixFml­Form oder aber durch autonom pgmmierte Fktt erfolgt, ist letztlich schnurz. Aber das habe ich ja schon beweisen können… ;-]
Aber immerhin benötigen letztere Fktt mitunter auch MatrixFml­Form und mitunter reicht das nicht mal, wie die Existenz dualer MatrixFmln beweist. Wir sollten also immer die Darstellung im Tab­Blatt von der eigentlichen Berechnung, vor allem, wenn Fktt im Spiel sind, getrennt halten und nicht beides auf eher verschleiernde als analysierende Weise miteinander vermischen. Dann bleiben nur 2 Fml­Hpt­Kategorien übrig → Normal- und MatrixFmln. Letztere können dann in 3 Teil­Kategorien unter­gliedert wdn. Wollte man Normal­Fmln ebenfalls unter­gliedern, könnte man das so wie du eigentlich nur machen, wenn man sich dabei auf ihre Zusammen­setzung, also die evtl beteiligten Fktt, bezieht. In diesem Sinne könnte man sie ggf als xl-matrix­steuerungs­konform, -teil- bzw -voll­autonom bezeichnen. Die von MS als MatrixFmln bezeichneten Fmln könnten hier eigentlich ebenfalls oft eingeordnet wdn, so dass letztlich tat­sächlich nur eine duale Hpt­Teilung der Fml­Arten übrig bliebe. Es verkompliziert das Ganze also, wenn man hier mehr Katego­risierung hinein­legen will als Normal­Fmln auf der einen und Matrix­Fmln (in 3 Unter­Kategorien wg ihrer Funktions- u/o Darstellungsweise!) auf der anderen Seite…
Übrigens ist die Bezeichnung ohne Array auf der verlinkten xlfmln-Site unzulässig verkürzt und damit irreführend!
Morrn + schöWE, Luc :-?

Anzeige
AW: zu „Matrix­funk­tion(alität)s­formel" ...
22.05.2016 10:25:57
...
Guten Morgen Luc,
... Deine dazu hier eingestellte Argumentationen sind mir ja nicht neu. Jedoch für Jemanden, der unsere Diskussionen dazu nicht kennt, stellt sich sicherlich die Frage, warum schreibst Du, dass Du meine Bezeichnung „Matrix­funk­tion(alität)s­formel" tolerierst, aber Deine dann nachfolgenden Aussagen dem in im Grunde nach widersprechen. Wobei ich wiederum damit nicht behaupten will, dass ich Deine Aussagen in einigen Punkten nicht teile. Aber eben nicht nicht generell. Aber ich werde die Diskussion hier nicht neu aufleben lassen, in dem ich meine Argumentationen wiederhole.
Wir sollten es hier einfach dabei belassen, dass unsere Meinungen diesbzgl. nicht voll kompatibel sind.
Gruß Werner
.. , - ...

Das habe ich auch nur geschrieben, weil ...
24.05.2016 04:23:18
Luc:-?
…du hier darauf (näher erläuternd) gekommen bist, Werner; ;-)
in einfachen Verwendungsfällen toleriere ich das tatsächlich, aber einer näheren Erläuterung sollte nunmal eine andere (mE richti­gere) Behandlung der Problematik gegenüberstehen.
Dass bspw AGGREGAT (und auch TEILERGEBNIS) so pgmmiert ist, ist logisch, denn der Wirkungsbereich muss ja noch näher unter­sucht und vorbehandelt (ggf bereinigt) wdn.
Schreibe ich eine UDF, muss ich von vornherein bei ihren Argumenten alles berücksichtigen, was möglich sein soll, denn die Xl-Steu­erung variiert hier höchstens über ein Variant-Intervall bzw einen ZellBereich (Range). Wenn ich ganz sicher gehen will, kann ich mich darauf nicht verlassen und muss auch Datenfelder und ZellBereiche vorsehen. Nichts anderes wurde bei den Fktt getan, deren Anwesenheit in einer Fml den von dir genannten Effekt hervorrufen kann (aber nicht immer muss, was ich auch schon bewie­sen hatte!).
Einer Fml kann nichts „innewohnen“, was nicht schon die Xl-Steue­rung vorsieht oder in die verwendeten Fktt einpgmmiert wurde! Genau deshalb ist es mE falsch, so etwas an Fmln festmachen zu wollen. Bei TeilFmln (Ausdrücken) als FktsArgument wird das besonders deutlich, denn die wdn zuerst berechnet und der Fkt wird dann nur das Ergebnis dieser Berechnung übergeben. Macht die dann mehr daraus als zu erwarten wäre, liegt das entweder an ihrer Pgmmierung oder der durch die Xl-Steuerung vorgenom­menen Fml-Optimierung, bei der ich aber bezweifle, dass es um mehr geht als um eine reine Berechnungs­Reihenfolge-Optimierung.
Morrn, Luc :-?

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige