Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Выбор по максимальному значению из строчки, fx(max); fx(if); fx(dmax)
 
#1
19.01.2019 19:24:37
Доброго времени суток всем.
Помогите разобраться вот в какой задаче. Фаил в приложении.
Задача. По максимальному значение из диапазона вставить соответствующие значение в ячейку из "шапки таблицы".
Всем огромное спасибо кто откликнется и всех с наступившим 2019!
 
 
 
#2
19.01.2019 19:40:52
Массивная
=IFERROR(INDEX($1:$1;SMALL(IF(A2:K2=MAX(A2:K2);COLUMN(A2:K2));1));"")
&IFERROR(" & " & INDEX($1:$1;SMALL(IF(A2:K2=MAX(A2:K2);COLUMN(A2:K2));2));"")
&IFERROR(" & " & INDEX($1:$1;SMALL(IF(A2:K2=MAX(A2:K2);COLUMN(A2:K2));3));"")
&IFERROR(" & " & INDEX($1:$1;SMALL(IF(A2:K2=MAX(A2:K2);COLUMN(A2:K2));4));"")
&IFERROR(" & " & INDEX($1:$1;SMALL(IF(A2:K2=MAX(A2:K2);COLUMN(A2:K2));5));"")
&IFERROR(" & " & INDEX($1:$1;SMALL(IF(A2:K2=MAX(A2:K2);COLUMN(A2:K2));6));"")
&IFERROR(" & " & INDEX($1:$1;SMALL(IF(A2:K2=MAX(A2:K2);COLUMN(A2:K2));7));"")
&IFERROR(" & " & INDEX($1:$1;SMALL(IF(A2:K2=MAX(A2:K2);COLUMN(A2:K2));8));"")
&IFERROR(" & " & INDEX($1:$1;SMALL(IF(A2:K2=MAX(A2:K2);COLUMN(A2:K2));9));"")
&IFERROR(" & " & INDEX($1:$1;SMALL(IF(A2:K2=MAX(A2:K2);COLUMN(A2:K2));10));"")
&IFERROR(" & " & INDEX($1:$1;SMALL(IF(A2:K2=MAX(A2:K2);COLUMN(A2:K2));11));"")

IF(A2:K2=MAX(A2:K2);COLUMN(A2:K2)) можно поместить в имена и итоговая формула будет короче.

Если известно количество равных максимальных заведомо не больше N, то  количество повторов можно уменьшить до N, сейчас это 11
Изменено: БМВ - 19.01.2019 19:41:03
По вопросам из тем форума, личку не читаю.
 
 
 
#3
19.01.2019 20:45:50
UDF
Код
Function СЦЕПИТЬШАПКУ(iTbl As Range, iVal, iRow&, Optional dlm$ = " & ")
'iTbl - исходная таблица
'iVal - искомое значение
'iRow - номер строки в таблице, в которой ищем значение. Без учета 'шапки' таблицы
arr = iTbl.Value
For I = 1 To UBound(arr, 2)
    If arr(iRow + 1, I) = iVal Then
        СЦЕПИТЬШАПКУ = IIf(СЦЕПИТЬШАПКУ <> Empty, СЦЕПИТЬШАПКУ & dlm & arr(1, I), arr(1, I))
    End If
Next
If СЦЕПИТЬШАПКУ = Empty Then СЦЕПИТЬШАПКУ = CVErr(xlErrNA)
End Function
Согласие есть продукт при полном непротивлении сторон
 
 
 
#4
19.01.2019 21:00:47
Миша, так интереснее:
=ПОДСТАВИТЬ(ТЕКСТ(--ПСТР(СУММ(НАИМЕНЬШИЙ(ЕСЛИ(A2:I2=L2;СТОЛБЕЦ(A:I););СТРОКА($1:$9))/10^(СТРОКА($1:$9)-1));3;11);"\n\_#\ \& \n\_#\ \&\ \n\_#\ \&\ \n\_#\ \&\ \n\_#\ \&\ \n\_#\ \&\ \n\_#\ \&\ \n\_#\ \&\ \n\_#");"n_ & ";)&ЕСЛИ(J2=L2;" & n_10";"")&ЕСЛИ(K2=L2;" & n_11";"")
формула массива, не упрощал
 
 
 
#5
19.01.2019 21:10:32
vikttur,  Виктор, но тогда формула не отвечает на вопрос
Цитата
Alexandr Dumanetskiy написал:
вставить соответствующие значение в ячейку из "шапки таблицы".
ведь в шапке может быть что угодно и без индекса.
По вопросам из тем форума, личку не читаю.
 
 
 
#6
19.01.2019 21:12:24
А как же мелодия формулы? Медведь себе на ухо наступил? Мне нравится процесс, а не одежки :)
А еще допилить можно, ибо не все красиво
 
 
 
#7
19.01.2019 21:36:12
Все работает @БМВ - задача решена 100%
@vikttur @Sanja как всегда - все супер!
Всем большое спасибо!
 
 
 
#8
19.01.2019 21:39:20
Цитата
vikttur написал:
допилить можно
типа "\n\_#"&REPT("\ \&\ \n\_#";8)
Не, ну способ то хорош, правда конечно ограничение в разрядах быстро приводит к такому варианту :-)
=SUBSTITUTE("&"&IF(A2=L2;" & "&A$1;"")&IF(B2=L2;" & "&B$1;"")&IF(C2=L2;" & "&C$1;"")&IF(D2=L2;" & "&D$1;"")&IF(E2=L2;" & "&E$1;"")&IF(F2=L2;" & "&F$1;"")&IF(G2=L2;" & "&G$1;"")&IF(H2=L2;" & "&H$1;"")&IF(I2=L2;" & "&I$1;"")&IF(J2=L2;" & "&J$1;"")&IF(K2=L2; " & "&K$1;"");"& & ";"")

P.S. Вот зачем я MAX(A2:K2) ф  формулу вместо L2 запихнул в #2? :-)
Изменено: БМВ - 19.01.2019 21:40:52
По вопросам из тем форума, личку не читаю.
 
 
 
#9
19.01.2019 21:47:07
Да, двузначные  портят все удовольствие.
 
 
 
#10
19.01.2019 21:56:39
Цитата
vikttur написал:
Да, двузначные  портят все удовольствие.
да и без них то не более 15 столбцов :(
По вопросам из тем форума, личку не читаю.
 
 
 
Страницы: 1
Наверх