Ist es möglich,wenn in einer Zelle ein Wert drin steht <>"", dass dann ein Datum aus einer anderen Zelle gleiche Spalte ausgeworfen wird?
"Hallo zusammen,
Frage 1:
ich suche nach einer Formel, die in einer Spalte schaut, ob ein Wert vorhanden ist (z.b. N80:AZ80) und wenn dies der Fall ist, dann soll er das Datum aus der Spalte N3:AZ3 auswerfen.
Ein weiteres Problem ist, dass nicht nur ein Wert in der Spalte stehen kann, sondern auch mehrere (max. vier Werte) in N80:AZ80 stehen können.
Beispiel:
In P80 wird ein Wert (Zahlenwert) gefunden, dann soll er das Datum aus P3 auswerfen. Also wird soll immer das Datum aus der gleichen Spalte ausgeworfen werden.
Frage 2:
Das Problem ist ähnlich wie bei Frage 1.
IN dem Fall soll z,B in der Spalte DL232:GB232 nach einem Wert über 100.000€ gesucht werden und wenn dies der Fall ist, dann soll das Datum aus DL231:GB231 ausgeworfen werden.
Beispiel:
In Spalte FT232 steht ein Wert von 200.000€,dann soll er das Datum aus FT231 in dem Fall Nov 2020 auswerfen.
Ich hoffe, ihr könnt mir helfen.
Gruß und Danke Stefan
2 Antworten
Das erste wäre: =INDEX(N3:AZ3;1;VERGLEICH(A1;N80:AZ80;0))
Unter der Annahme, dass der zu suchende Wert in A1 steht. Habe ich es aber richtig verstanden, dass genau dieser Wert mehrfach vorkommen kann? Das wäre etwas haarig.
Das zweite (auch hier nur, wenn 1 Wert über 200.000 ist)
=WENN(MAX(DL232:GB232)>200000;INDEX(DL231:GB231;1;VERGLEICH(MAX(DL232:GB232);DL232:GB232;0));"")
Zu Frage 2 wäre das ein Ansatz:
=WENN(KGRÖSSTE(DL$232:GB$232;1)>200000;INDEX(DL$231:GB$231;1;VERGLEICH(KGRÖSSTE(DL$232:GB232;1);DL$232:GB$232;0));"")
Die Formel entsprechend der maximal erwarteten Anzahl von Treffern kopieren (darauf achten, dass die Zellbezüge unverändert bleiben) und die beiden fett markierten Werte erhöhen. So bekommst Du die Werte für den größten, zwei-, drittgrößten etc.
ich denke, Du meinst Zeile un d Zellen in verschiedenen Spalten?
Erstreckt sich Deine Ausgabeezeile ebenfalls über die Spalten N:AZ? oder
sollen das 4 Zellen nebeneinander sein? Ersteres wäre halbwegs einfach:
füge eine neue Zeile oberhalb ein (als Zeile1, kannst Du später nach weiter unten versetzen) und belege die ZelleM1 mit der abzufragenden Zeilennummer (lt. Beispiel: 80), dann in N1 die Formel:
=WENN(INDEX(INDIREKT($M$1&":"&$M$1);SPALTE(N1))<>"";AD4;"-")
[N4, weil Zeile 3 durchs Einfügen einer neuen Zeile zu N4 wurde] das als Datum formatieren und nach rechts kopieren bis AZ1
Soll das aber auf vier Spalten komprimiert werden, dann ebenfalls in M1 die gewünschte Zeile, in N1 (meinetwegen auch N2 oder sonstwo) die Formel:
=SUMMENPRODUKT(INDIREKT(ADRESSE(4;KGRÖSSTE((INDIREKT("N"&$M$1&":AZ"&$M$1)<>"")*SPALTE($N:$AZ);4))))
Die Formel ist mir jetzt zu umständlich, um sie auf Verdacht hin zu erklären, nimms hin und frag ggf zurück!
diese Formel nur 1 nach rechts kopieren, dann das );4)))) durch );3)))) ersetzen, wieder eins nach rechts, durch ;2) und zum letzten mal eins nach rechts und ;1) reinschreiben, das ist das K von Kgrösste
(Du hast ja gesagt, max. 4 Einträge).
Für mehr Einträge würde es sich lohnen, das mit irgendeiner Spalte()-Anzahl()-KombiFunktion (ab Spalte N) zu automatisieren oder mit KKleinste zu arbeiten, aber KKleinste macht Komplikationen mit Leerzellen, und dann würds völlig undurchschaubar!
Jetzt hab ich das mit den € 200000 vergessen: morgen dann!
Hi Lamiam,
danke für die Rückantwort. Korrekt ich meinte Zeile und nicht Zelle und ja die Ausgabezelle ist ebenfalls von N:AZ.
Trotzdem bekomme ich es derzeit nicht hin. :(
Meine/ Deine Formel steht in N1: =WENN(INDEX(INDIREKT(N80&":"&N80);SPALTE(N80))<>"";N3;"-")
Die Spalte die ausgegeben werden soll, somit die Datumsspalte steht in Zelle N3 und die Zelle die den Wert beinhalten könnte steht in N80. Leider bekomme ich in N1 nur einen Bezugsfehler gezeigt :(.
Der erste Wert steht derzeit in Zelle U80, da zeigt er logischerweise auch einen Bezugsfehler an.
Sorry aber deine Formel ist mir derzeit ein bisschen zu hoch! ;) :(
Gruß
Stefan
ich bin davon ausgegangen, dass Zeile 80 nur ein Beispiel sein soll und Du deshalb die Zeile in M1 bestimmen können sollst.
Da solltest Du Dich genau an die Anweisung halten, hinterher kannst du M1 und Formeln nach Bedarf verschieben, Zeile 1 frei machen und löschen (oder die zweite Formelvariante da rein (s.u.))
Willst Du das 80 in die Formel in N1 schreiben (und N1 jedesmal nach rechts bis AZ kopieren, reicht was viel einfacheres:
=wenn(N80<>"";N3;"k.Eintr") (bei verblieben eingefügter Zeile:81!)
(aber sowas ähnliches hat wohl Ninombre schon geschrieben.)
Die zweite Formel (für lückenlose Darstellung) hab ich nun doch in Bezug auf das K noch variiert: in M1 wieder die 80, nein, 81, es ist ja oben eine Zeile eingefügt worden! (oder auch wieder variabler:=Zeile($M$81), das korrigiert auch weitere eingefügte/gelöschte Zeilen und in zB
H1: =SUMMENPRODUKT(INDIREKT(ADRESSE(4;KGRÖSSTE((INDIREKT("N"&$M$1&":AZ"&$M$1)<>"")*SPALTE($N:$AZ);SPALTEN(H$1:$H1)))))
Kopiere einfach die Formel von = bis Ende hier und füge sie in H1 ein, kopiere sie dann nach links und nach rechts und du wirst die Ergebnisse links steigend angeordnet finden und rechs davon fallend!
Wieder verschieben nach Bedarf. Löscht Du Zeile 1, dann anstatt ...Adresse(4;... ...Adresse(3;... zur Korrektur.
So, und jetzt hab ich erst mal wieder keine Zeit mehr für 200.000 €!
Für die 200.000€ (bzw >100.000) brauchst Du nur anstatt <>"" >100000 in obige Formeln einsetzen
schreiben und die Adresseneintrage anpassen, also anstatt bei Zeile (4; schreibst Du 231 und anstatt 81 in $M$1 anstatt 81 die Zahl 232
Die Spalten änderst Du auf DL(anstatt N) und GB (anstatt AZ)
ich würde allerdings anstatt M1 hier die Zelle GA1 nehmen, also auch noch $M$1 durch $GA$1 ersetzen, dann kannst Du beides unabhängig voneinander abfragen
etwas vereinfacht kann die Formel auch heissen (in GB1 und dann nach rechts und/oder links kopiert):
=SUMMENPRODUKT((INDIREKT(ADRESSE(231;KGRÖSSTE(($DL232:$GB232>100000)*SPALTE($DL232:$GB232);SPALTEN($GB$1:GB$1))))))
Achtung: ein Text wird von xl als sehr großer Wert "verstanden", es dürfen also keine Texte in DL232:GB232 stehen! (auch keine Leerzeichen!) Ggf Kommentare oder kleine Textfelder in die Zelle! (Die Abfrage auf Istzahl würde das Ganze nochmals verkomplizieren!)
Hi,
danke für deine Antwort. Leider können mehrere Werte in der Spalte stehen und es gibt auch keinen Suchwert, der Wert ist '<> "" '.
Zur 2. Fragen, in der Regel, steht nur ein Wert über 200.000 in der Zeile, aber wenn es mehrere sind, dann wäre es super, wenn er diese Werte auswerfen könnte.
Noch mal danke für deine Hilfe,
Stefan