☰

Объединение нескольких столбцов с условием

tag-icon • tag-icon microsoft-excel
Объединение нескольких столбцов с условием

У меня есть ряд столбцов (A1,B1,C1,D1). Эти столбцы имеют значения от 1 до 20. У меня есть формула в F1, например:

Код:

=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 (разделенных запятой)?

Спасибо.

решение1

Это можно сделать с помощью формулы, но она довольно длинная, и 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, и вычисляет массив {ИСТИНА, ЛОЖЬ, ИСТИНА, ЛОЖЬ}. Вторая часть — это номера столбцов A1:D1 или {1,2,3,4}. Умножение этих двух массивов приводит к тому, что логические значения ИСТИНА и ЛОЖЬ преобразуются в 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 является формулой массива и должна быть введена сCtrlShiftEnter

=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","")))))

Я же говорил, что эта формула будет "довольно длинной". Надеюсь, это поможет.

решение2

Вот еще несколько вариантов, которые я нашел в качестве альтернативы приведенным выше формулам:

=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)

Мне пришлось опубликовать второй ответ, потому что эти формулы в комментарии выглядят не очень хорошо.

Связанный контент