Excel Zahl inklusive Operator aus anderer Zelle übernehmen und in Formel nutzen?
Die Frage klingt komplizierter als Sie ist,
ich habe in Einer Zelle z.B. A1 stehen >40
Nun möchte ich dieses größer 40 z.B. in einer Wennabfrage nutzen, also sowas wie:
=wenn(B5&indirekt("A1");"größer";"kleiner")
leider funktioniert die Variante mit indirekt aber nicht, es ist bestimmt wieder nur eine Kleinigkeit, aber wie ließe sich das lösen.
Auf Grund schwieriger Erklärung hier doch nochmal ein Ergänzung mit einem Beispiel:
In Diesem Bild ist die OriginalTabelle in den Zeilen A2 bis C9 mit Namen Tabelle3
In der Zelle G3 habe ich die unten geschriebene Filterfunktion eingegeben, diese Funktioniert auch wie sie soll, nur ist sie eben nicht dynamisch sondern ich müsste jedes mal den Wert >30 ändern wenn ich das Filter Kriterium ändern will, da es sich nicht nur von >30 auf >40 ändert sondern ggf. auch mal auf < oder gar <>, somit muss der Operator mit Bestandteil sein. Hierzu habe ich 2 Versuche gestartet:
- =FILTER(Tabelle3;"Tabelle3[Preis]"&INDIREKT("H10"))
- =FILTER(Tabelle3;VERKETTEN("Tabelle3[Preis]";H10))
Beide Formeln haben den Sinn, das Sie Tabelle3[Preis] mit der Bedingung >30 zu einem festen Ausdruck machen sollen, wie man sieht funktioniert das auch losgelöst von der Filterfunktion wenn ich den Part
- ="Tabelle3[Preis]"&INDIREKT("H10")
- =;VERKETTEN("Tabelle3[Preis]";H10)
einzeln eingebe, wie man in den Zellen L12 und L14 sehen kann, dort stehen die einzeln drin, in der Formel selber, aber scheint dieser Part nicht ausgewertet zu werden, vermutlich wird irgendwas nicht als Formel oder Zellbezug interpretiert, auch wenn ich die Anführungszeichen weglasse oder ähnliches hat sich bisher nichts gebessert.
Weiter Ergänzung, hier nochmal die Frage aus der Sicht eines nicht Fachmanns, vielleicht drücke ich mich zu kompliziert aus:
Für sonst vielleicht eine andere Erklärung zu meinem Gedankengang. Mir geht es darum, ein dynamischen Filter zu nutzen. Dass ich nicht immer wieder in der Formel das < / > abändern muss, sondern einfach eine Formel nutzen kann und dann aus einer anderen Zelle mein Filterkriterium, also zB: Liste alles was > 30 ist auf, kombinieren kann.
Wie beim Bild, dass zB. die Formel steht =Filter(Tabelle3;Tabellee3[Preis] und dann aus Zelle H1 meinen gewünschten Wert für < / > ZAHL umändern kann und mein Ergebnis gelistet bekomme.
Lösung:
Dadurch, dass mich alle Lösungen nur halb zufrieden gestellt haben, auch die durchaus gute von @GutenTag2003, der mit dem S-/XVerweis eine durchaus interessante Idee hatte auf die ich so nicht gekommen, bin daraus habe ich dann eine eigene Formel entwickelt wie unter seinem Beitrag zu sehen, die zumindest funktioniert habe, aber noch nicht schön war.
In einem anderem Forum gab man mir jedoch den Tipp )
=Zählenwenn und =nachzeile zu kombinieren und daraus ist dann folgendes entstanden
Also genau das was ich wollte, ich hoffe dem ein oder anderen hilft das weiter.
Zum Verständnis: Wenn B5 größer als 40 ist, soll in der Zelle "größer" stehen und ansonsten "kleiner". Und das >40 soll nicht in der Formel direkt stehen, sondern in einer Zelle?
genau, da dies dynamisch sein soll, später könnte da evtl <20 stehen, das "kleiner" etc ist nur, damit es hier verständlich ist, ist eigentlich Teil einer komplexen Filterfunktion
.
3 Antworten
Sry Wusste nicht, das ich meine eigene Frage beantworten kann, daher hier nochmal die Antwort dazu.
Lösung:
Dadurch, dass mich alle Lösungen nur halb zufrieden gestellt haben, auch die durchaus gute von @GutenTag2003, der mit dem S-/XVerweis eine durchaus interessante Idee hatte, auf die ich so nicht gekommen bin, daraus habe ich dann meine eigene Formel entwickelt/angepasst, wie unter seinem Beitrag zu sehen, die zumindest funktioniert hat, aber noch nicht schön war.
In einem anderem Forum gab man mir jedoch den Tipp )
=Zählenwenn und =nachzeile zu kombinieren und daraus ist dann folgendes entstanden
Also genau das was ich wollte, ich hoffe dem ein oder anderen hilft das weiter.
Wer das mal nachstricken will ohne diese Endlosformel abzuschreiben:
=WENNFEHLER(FILTER(Tabelle1;(Tabelle1[Produkt]<>"")*(NACHZEILE(Tabelle1[Preis];LAMBDA(Zellwert;ZÄHLENWENN(Zellwert;INDIREKT("G1"))))));"")
Mir fällt keine Möglichkeit ein, das mit ">40" in einer Zelle zu schreiben und in der Formel darauf Bezug zu nehmen (vielleicht weiß sonst jemand eine Möglichkeit).
Würde es ausreichen, wenn in der Zelle nur die 40 steht und man dann folgende Formel nimmt?
=wenn(B5>A1;"größer";"kleiner")
Oder willst du das Vorzeichen in der Zelle evtl. ändern können?
Okay. Sorry, auf die Schnelle fällt mir nichts ein. Ich mache mir noch mal Gedanken.
z.B. so.
XVerweis
Leider kann ich in Kommentaren keine Bilder Anhängen daher versuche ich es mal möglichst Simpel zu schreiben.
Zeile 1: Produkt 1 ; Preis
Zeile 2: Produkt 2 ; Preis
Zeile 3 Produkt 3 ; Preis
Diese Liste aber wird von einer Filterfunktion abgeholt und ich möchte jetzt in den Filter integrieren wenn der Preis < > oder 0 einem bestimmten Wert ist, dann sollen nur dies angezeigt werden nur anstatt mit Fix Werten zu arbeiten möchte ich dazu eine Zelle benutzen in die ich dann das Kriterium reinschreibe, in diesem Beispiel z.B, >40
Soll also so aussehen
=Filter(Tabelle1;Tabelle1[Preis]{Bezug aus Zelle A1})
sprich eigetnlich soll dann da drin stehen
=Filter(Taelle1;Tabelle1[Preis]>40)
Nur das das >40 eben nicht direkt drin steht sondern indirekt aus einer anderen Zelle geholt wird, das besondere hierbei ist eben nur, dass der Operator eben mit dabei sein muss und nicht nur die Zahl selber.
ich hoffe so ist es besser verständlich, dass wenn habe ich hier nur bentzt, da wenn eigentlich so die ziemlich simpelste Formel ist und jeder kennt.
Vom Prinzip her ja, nur ist mir der Tabellenname gar nicht wichtig, sondern nur das Filterkriterium soll dynamisch sein, kann daher deinem Beispiel mit dem Sverweis da nicht ganz so folgen, ich versuche mal ein Beispiel in meinen Originalbeitrag einzufügen.
Dein korrigierte Idee geht zwar in die Richtung würde ich sagen, aber das Ergebnis ist leider gar nicht das was ich erwarte, ich denke mal die Spaltenüberschrift in C2 soll sicher Gewicht lauten und ein A13 soll sicher C1 stehen oder?
Ich beziehe mich mal hier auf das Ergebnis 1:
Du Filterst nach Preis >=30, was genau versuchst du mit der Spalte Gewicht 25 zu Filtern?
Ich würde also bei einer Filterung >=30 erwarten das mir die Produkte 3,4,6,7 inklusive deren Preise aufgelistet werden und bei <40 z.B. Produkt 3,2,1
Daher verstehe ich tatsächlich nicht, was deine Formeln da genau tun du gibst oben 25 ein und erhältst ein Ergebnis in dem 42 ausgegeben wird
Meine Intention war aber eigentlich (ich nehme mal G1 als Filterkriterium) ich gebe in
G1 >20 ein und erhalte als Liste die Produkte 1,2,3,4,5,6,7
G1 >50 ein und erhalte als Liste die Produkte 6,7
G1<45 ein und erhalte als Liste die Proudkte 1,2,3,4
G1 = 50 ein und erhalte kein Ergebnis, da kein Produkt genau 50 kostet
Du Filterst nach Preis >=30, was genau versuchst du mit der Spalte Gewicht 25 zu Filtern?
Ich würde also bei einer Filterung >=30 erwarten das mir die Produkte 3,4,6,7 inklusive deren Preise aufgelistet werden und bei <40 z.B. Produkt 3,2,1
Warum Du für z.B. ein Gewicht mehrere Preise haben willst, in mir unverständlich.
Ich habe das Gefühl zum Ende hin haben wir etwas aneinander vorbei geredet.
Das mit dem Gewicht hast du erst mit eingebracht, bei mir gab es nur eine Tabelle mit Produkten und dazugehörigen Preisen und Herstellungsländern.
Und ich wollte nichts anderes als die Tabelle von oben zu Filtern nach allen Produkten die teurer sind als 30€
Ich weiß nicht warum du so auf den Sverweis aus bist ich kann (nach meinem Kenntnisstand keinen wirklichen Anwendungszweck für den Sverweis finden, da der Sverweis eigentlich immer das Ziel hat EIN bestimmtes Ergebnis zu liefern, ich möchte aber nicht ein Ergebnis haben sondern eine Liste mit Ergebnissen auf die das Kriterium alle zutrifft.
Man stelle sich dazu einfach ein Amazon Preisfilter in einer Exceltabelle vor, ich möchte alle Produkte sehen die mehr als 30€ kosten.
Dies habe ich ja mit der gezeigten Variante gut hinbekommen (Bild1)
nur ist diese statisch, da in der Formel fest drin steht >30, alternativ um sie ein wenig dynamischer zu machen könnte ich auch schreiben
=FILTER(Tabelle3;Tabelle3[Preis]>h1)
und in H1 würde ich dann anstelle von >30 (siehe Bild) eben nur 30 reinschreiben, aber auch dann ist noch immer der Operator (>) statisch, da in der Formel fest drin >h1, ich kann also nur Ergebnisse Anzeigen lassen die > einem gewissen Wert, möchte ich nun aber alle Ergebnisse anzeigen lassen die <30 oder einem beliebigen anderen Wert sind muss ich also in die Formel rein den Operator > in < tauschen und abschicken und genau das möchte ich vermeiden.
Ich möchte den Operator inklusive dem Wert in einer festen Zelle stehen haben und die Filter Formel nur einen Verweis auf diese Zelle schreiben und dieser wird in der Zelle genutzt.
Optimalerweise vielleicht auch getrennt z.b. in Zelle H1 ein dropdown mit den möglich Operatoren "<",">" oder "=" und in J1 dann denn Preis, aber das ist nur Schönheit oder effektivitätsgewinn, mir geht es hier überhaupt erstmal um eine machbare Lösung.
Ich hoffe ich konnte nun klarer ausformulieren was ich wollte, tut mir Leid wenn es missverständlich war.
ich habe in Einer Zelle z.B. A1 stehen >40
Gewicht oder nicht, das ist doch nebensächlich. Excel rechnet mit den Zahlen. Wie Deine Überschrift heißt spielt doch - für Excel - keine Rolle.
Das mit dem Gewicht hast du erst mit eingebracht,
Zumal hinter Gewicht ein "?" steht. Hätte auch Unterhose hinschreiben können
Siehe Ergänzung meiner Antwort ...
... wenn Du eine Auflistung aller Werte haben willst. Wobei in A1 die Auswahl zwischen<> getroffen wird.
Gewicht oder nicht, das ist doch nebensächlich. Excel rechnet mit den Zahlen. Wie Deine Überschrift heißt spielt doch - für Excel - keine Rolle.
Korrekt ist doch aber für die zuletzt hinzugefügte Korrektur vollkommen unerheblich, ob das eine Rolle spielt oder nicht, meine Frage war ja eigentlich nur, warum du eine 3 Spalte hinzugefügt hast ob die nun Gewicht, Preis oder WertX heißt ist vollkommen egal.
Ich meine ich habe ein Beispiel hinzugefügt mit Produkt Preis Land, du hast zwischen Land nochmal Preis hinzugefügt, vielleicht soll es ja eine Hilfsspalte sein, kann ich ja nicht wissen. Wenn ja wie kommst du auf die Werte dort?
Falls es eine Gewichtsspalte ist auch okay, meinetwegen ein weiters mögliches Kriterium.
Und zu deinem 2. Zitat, ja auch das ist korrekt es war auch rein Informativ eine Frage und eine Vermutung keine Ahnung warum dies einer solchen Antwort Bedarf, ich kenne dich ja von vielen Antworten hier auf GF und weiß, dass du gute Antworten geben kannst, daher verstehe ich gerade nicht diese Art der Antwort, da sie zur eigentlich Frage doch keinen Bezug hat.
Ich versuche nochmal auf deine Beispiele einzugehen.
Zu Zelle B1:
Du fragst du ab ob G1 > 0 und < 30 ist wenn ja soll die Zahl aus G1 dort auftauchen, wenn nicht ">=30"
Zu (ich vermute C1):
Fragst du ab ob G1 >0 und G1 > 30 ist, wenn ja soll dort die Zahl auf G1 auftauchen, wenn nicht ">=30"
Was ist der Sinn dieser beiden Zellen, sie finden ja immerhin Anwendung in den Zellen G3 und H3.
Zu G3:
Du fragst ob G1=B1 ist, ist dies der Fall soll der Preis aus Spalte 2 zu dem Produkt aus F3 ausgegeben werden, wenn nicht der Preis aus Spalte 3
Zu H3:
Du fragst ob G1=B1 ist, ist dies der Fall soll das Land zu dem Produkt aus F3 ausgegeben werden, ist dies nicht der Fall auch das Land zu dem Produkt aus F3?? Wozu dann das WENN?
Zu F3:
Woher kommt hier das "Produkt 4"
Und nun generell, was wird mit B1 und C1 hier berechnet auf was sich G3 und H3 beziehen, in deinem Beispiel gibst du 1. 25 ein und erhältst 42 als Ergebnis, ein anderes mal 50 und erhältst 45 als Ergebnis. Was sollen diese Ergenise sein, was haben die mit dem Wert 25/50 zu tun, vielleicht stehe ich auch nur auf dem Schlauch.
Da ich die Möglichkeit nicht ausschließen will, dass ich mich blöd ausgedrückt habe, habe ich die Frage nochmal jemandem vorgelegt, der von Excel absolut keine Ahnung hat, und hab ihn mal ausformulieren lassen, was ich versuche zu sagen ohne die Brille eines auf zu haben der von sich glaubt sich doch ganz gut auszukennen. Daher oben eine weitere Ergänzung
sorry meine Antwort bezog sich noch auf eine alte Version (jedenfalls älter als 15min) ich schreibt ggf. nochmal etwas, falls sich mein Kommentar erübrigt hat.
Habe Dir für die Spalte G;H:I noch eine Variante per XVerweis beigefügt.
.
Der SVerweis hat bei mir Ungenauigkeit ergeben.
Der SVerweis hat bei mir Ungenauigkeit ergeben.
Das wiederum kann ich dir sagen warum, du hast den Optionalen Parameter 0 oder FALSCH vergessen, der überprüft ob exakt das Ergebnis oder ein ungefähres Ergebnis ausreicht, ehrlicherweise weiß ich gar nicht, wann man hier mal 1 oder WAHR setzt, ich dachte aber auch immer 0 wäre Standard, hab es bisher dennoch immer eingetippt.
Habe Dir für die Spalte G;H:I noch eine Variante per XVerweis beigefügt.
Diese beiden Varianten scheinen zu funktionieren, wirken nur etwas umständlich, aber wären als vorübergehende Lösung sicher denkbar, schade das es scheinbar nichts gibt ob den Operator direkt in der Filterfunktion zu nutzen, auch wenn ich überzeugt bin, dass es das gibt ich bin nur zu doof dazu.
Als Optimierung habe ich die Formel in A2 etwas angepasst diese lautet nun:
=WENNS($F$1="<";ZÄHLENWENN(C$2:C2;"<"&G$1);$F$1=">";ZÄHLENWENN(C$2:C2;">"&G$1);1;ZÄHLENWENN(C$2:C2;"="&G$1))
Das ergibt zwar 0 statt FALSCH, ist aber für mich nicht weiter tragisch, habe zumindest keine negativen Konsequenzen entdeckt und die überprüfung ob der Wert negativ ist hab ich mir geschenkt da gar keine negativ Preise in die Tabelle eingegeben werden können, zusätzlich habe ich in F1 ein Dropdown hinzugefügt in dem zwischen "<",">";"=" ausgewählt werden kann.
Aber danke für die Idee, wäre ich vermutlich so nicht drauf gekommen, da wie gesagt meine bevorzugte Variante eigentlich der Filter ist.
Nachtrag:
Wobei mit Filter geht das auch:
=WENNFEHLER(FILTER($B$2:$D$12;(B2:B12<>"")*WENNS($F$1="<";$C$2:$C$12<$G$1;$F$1=">";$C$2:$C$12>$G$1;1;$C$2:$C$12=$G$1));"")
Manchmal braucht man nur einen Tritt in die richtige Richtung
Schön, dann hat es doch noch zum Ergebnis geführt.
Manchmal braucht man nur einen Tritt in die richtige Richtung
Das mit dem Filter werde ich mir mal genauer ansehen. Kenne es nicht so. Aber das Schöne hier ist, man lernt nie aus ... und immer noch dazu. 😉
Störende "0" usw. kann man je per Bedingter Formatierung unsichtbar machen.
Aber das Schöne hier ist, man lernt nie aus ... und immer noch dazu. 😉
So ist es, ich bin in einem anderen Forum noch auf eine andere Idee gestoßen worden, diese habe ich hier mal als Lösung unten an meinen Beitrag angehangen, die Formel dazu würde lauten, falls du es mal nachspinnen willst und nicht abschreiben willst:
=WENNFEHLER(FILTER(Tabelle1;(Tabelle1[Produkt]<>"")*(NACHZEILE(Tabelle1[Preis];LAMBDA(Zellwert;ZÄHLENWENN(Zellwert;INDIREKT("G1"))))));"")
richtig der Operator soll genauso dynamisch sein wie der Rest auch, da die Nachfragen Funktion leider nur sehr kurze Kommentare erlaubt, hier nochmal etwas ausführlicher
Das ganze ist Teil einer Filterfunktion sprich ich möchte festlegen das nur Werte mit einem bestimmten Werte angezeigt werden sollen, dies soll aber dynamisch geschehen, da es
>40 oder <15 oder =50 sein könnte, daher wird der Operator zwingend aus der Zelle A1 benötigt und darf nicht manuell geschrieben werden.