Ich habe einen Spaltenbereich (A1, B1, C1, D1). Diese Spalten haben Werte von 1 bis 20. Ich habe eine Formel in F1 wie diese:
Code:
=if($A1>7,"U",if($B1>7,"X",If($C1>7,"Y",if($D1>7,"Z",""))))
Diese Formel setzt die Werte von U, X, Y und Z ein, wenn eine dieser Zellen (A1, B1, C1 oder D1) größer als 7 ist.
Es kann jedoch nicht damit umgehen, wenn zwei Zellen größer als >7 sind.
Was soll ich tun, damit, wenn es zwei Spalten gibt, die größer als 7 sind, das Endergebnis ein Paar aus U, X, Y oder Z (durch Komma getrennt) ist?
Danke.
Antwort1
Dies kann mit einer Formel erfolgen, ist aber ziemlich lang und VBA ist wahrscheinlich eine bessere Lösung. Außerdem wird dies nicht den möglichen Fall behandeln, in dem SiedreiWerte > 7. Aber ich zeige Ihnen trotzdem die Formelmethode.
Beginnen wir mit dem einfacheren Fall, in dem es nur eine Zelle > 7 gibt:
Die Formel in F1 lautet:
=IF(COUNTIF($A$1:D$1,">7")>1,"Double",IF($A1>7,"U",IF($B1>7,"X",IF($C1>7,"Y",IF($D1>7,"Z","")))))
Der Abschnitt ZÄHLENWENN() zählt die Anzahl der Zellen im Bereich A1:D1, die größer als 7 sind. Wenn diese Summe größer als 1 ist, gibt WENN() „Double“ zurück, was nur ein Platzhalter für die größere Formel ist, die später folgt. Wenn die Summe nicht größer als 1 ist, verwendet die Formel Ihre verschachtelte WENN()-Anweisung, um den Code für die Zelle anzuzeigen, die > 7 ist.
Für den Fall, dass zwei Zellen > 7 sind, funktioniert ein verschachteltes IF() nicht. Wir müssen wissen, welche Spalten > 7 sind. Wenn wir diese Daten haben,
Zuerst erstellen wir ein Array, das die Spalten > 7 auflistet.
(A1:D1>7)*COLUMN(A1:D1)Das macht dieser Ausdruck. Der erste Teil (A1:D1>7)fragt, welche Zellen > 7 sind und ergibt das Array {TRUE,FALSE,TRUE,FALSE}. Der zweite Teil sind die Spaltennummern von A1:D1 oder {1,2,3,4}. Durch Multiplikation dieser beiden Arrays werden die logischen Werte TRUE und FALSE in 1 und 0 umgewandelt, sodass das Ergebnis das Array {1,0,3,0} ist, das die Spaltennummern mit einem Wert > 7 enthält.
Nun können wir diese Zahlen verwenden, um die Spaltencodes mit INDEX() nachzuschlagen:
INDEX({"U","X","Y","Z"},LARGE((A1:D1>7)*COLUMN(A1:D1),2))
Hier gibt LARGE() den zweitgrößten Wert im Array der Spaltennummern > 7 (also 1) zurück, und INDEX() verwendet diesen, um das erste Element im Array der Buchstaben – U – zurückzugeben.
In ähnlicher Weise ruft dieser Ausdruck die Codes ab, die den zweit- und erstgrößten Spaltennummern entsprechen und durch Kommas getrennt sind:
INDEX({"U","X","Y","Z"},LARGE((A1:D1>7)*COLUMN(A1:D1),2))&","&INDEX({"U","X","Y","Z"},LARGE((A1:D1>7)*COLUMN(A1:D1),1)
Jetzt setzen wir all das in die erste Gleichung oben ein und ersetzen "Double". Diese Formel in F1 ist eine Matrixformel und muss mit eingegeben werdenCtrlShiftEnter
=IF(COUNTIF($A$1:D$1,">7")>1,INDEX({"U","X","Y","Z"},LARGE((A1:D1>7)*COLUMN(A1:D1),2))&","&INDEX({"U","X","Y","Z"},LARGE((A1:D1>7)*COLUMN(A1:D1),1)),IF($A1>7,"U",IF($B1>7,"X",IF($C1>7,"Y",IF($D1>7,"Z","")))))
Ich habe Ihnen gesagt, dass diese Formel „ziemlich lang“ sein würde. Ich hoffe, das hilft.
Antwort2
Hier einige andere Optionen, die ich als Alternative zu den obigen Formeln gefunden habe:
=IFERROR(LEFT(IF($A1>7,"U, ","")&IF($B1>7,"X, ","")&IF($C1>7,"Y, ","")&IF($D1>7,"Z, ",""),(COUNTIF($A1:$D1,">7")-1)*3+1),"")
{=TEXTJOIN(",",TRUE,IF(A1:D1>7,MID("UXYZ",COLUMN(A1:D1)-COLUMN(A1)+1,1),""))}
=MID(IF($A1>7,", U","")&IF($B1>7,", X","")&IF($C1>7,", Y","")&IF($D1>7,", Z",""),3,99)
Wenn Sie Formeln haben, die "" zurückgeben:
=MID(IF(N($A1)>7,", U","")&IF(N($B1)>7,", X","")&IF(N($C1)>7,", Y","")&IF(N($D1)>7,", Z",""),3,99)
Ich musste eine zweite Antwort posten, weil diese Formeln im Kommentar nicht gut aussehen.