У меня есть ряд столбцов (A1, B1, C1, D1). Эти столбцы имеют значения от 1 до 20. У меня есть формула в Ф1, как это:
Код:
=if($A1>7,"U",if($B1>7,"X",If($C1>7,"Y",if($D1>7,"Z",""))))
Эта формула помещает значения U, X, Y и Z, если любая из этих ячеек (A1, B1, C1 или D1) больше 7.
но он не может справиться, если есть две ячейки больше чем> 7.
Что мне делать, так что если есть два столбца больше 7, конечным результатом будет пара U, X, Y или Z (через запятую).
Благодарю.
Это можно сделать с помощью формулы, но она довольно длинная, и VBA, вероятно, является лучшим решением. Кроме того, это не будет обрабатывать возможный случай, когда у вас есть три значения> 7. Но я все равно покажу вам метод формулы.
Давайте начнем с более простого случая, когда есть только одна ячейка> 7:
Формула в F1:
=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","")))))
Часть COUNTIF() считает количество ячеек в диапазоне A1:D1 больше 7. Если эта сумма больше 1, IF() возвращает значение "Double", которое является просто заполнителем для более крупной формулы, которая появится позже. Если сумма не больше 1, формула использует ваш вложенный оператор IF(), чтобы отобразить код для ячейки> 7.
Для случая, когда две ячейки> 7, вложенный IF() не будет работать. Нам нужно знать, какие столбцы> 7. Если у нас есть эти данные,
Сначала мы строим массив, в котором перечислены столбцы> 7. Это выражение (A1:D1>7)*COLUMN(A1:D1) делает это.
Первая часть (A1:D1>7) спрашивает, какие ячейки> 7, и оценивает массив {TRUE, FALSE, TRUE, FALSE}. Вторая часть - это номера столбцов A1:D1 или {1,2,3,4}. Умножение этих двух массивов приводит к тому, что логические значения TRUE и FALSE преобразуются в 1 и 0, поэтому результатом является массив {1,0,3,0}, который представляет собой номера столбцов со значением> 7.
Теперь мы можем использовать эти числа для поиска кодов столбцов с помощью INDEX():
INDEX({"U","X","Y","Z"},LARGE((A1:D1>7)*COLUMN(A1:D1),2))
Здесь LARGE() возвращает второе по величине значение в массиве номеров столбцов> 7 (что равно 1), а INDEX () использует это для возврата первого элемента в массиве букв - U.
Аналогично, это выражение получает коды, соответствующие второму и первому наибольшим номерам столбцов, разделенным запятой:
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)
Теперь мы подставим все это в первое уравнение выше, заменив "Double". Эта формула в F1 является формулой массива и должна вводиться с помощью Ctrl Shift Enter
=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","")))))
Я говорил вам, что эта формула будет "довольно длинной". Надеюсь, это поможет.
Вот некоторые другие варианты, которые я нашел альтернативу формулам выше:
=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)
Если у вас есть формулы, возвращающие "":
=MID(IF(N($A1)>7,", U","")&IF(N($B1)>7,", X","")&IF(N($C1)>7,", Y","")&IF(N($D1)>7,", Z",""),3,99)
Мне пришлось опубликовать второй ответ, потому что эти формулы не очень хорошо выглядят в комментарии.