#excel
#excel
Вопрос:
Мне нужно выполнить сложный выбор типа vlookup / maxif. Данные, которые у меня есть, как показано ниже
Row Col G Col H Col I colJ col K
1 Bench Strip Block BenchAbove BenchBelow
2 1 1 4
3 1 1 5
4 1 1 6
5 1 1 7
6 1 1 8
7 8 1 4 ?? ??
8 8 1 5
9 8 1 6
10 8 1 7
11 8 1 8
12 9 1 4
13 9 1 5
14 9 1 6
15 9 1 7
….. этот список длинный (это только пример)
Для каждой комбинации (полосы, блока), например, (1,4), есть скамейки, подобные 1, 8 и 9. Таким образом, скамейка выше для 8 равна 1, а скамейка ниже для 8 равна 9. Мне нужно определить таблицу выше и таблицу ниже для каждой строки. Нет скамьи выше 1 и нет скамьи ниже 9.
Я не думаю, что vlookup является решением здесь. Не уверен, что MAX (ЕСЛИ ..) тоже может помочь. Какие формулы лучше всего получить, скажем, в строке 7, комбинация блоков равна 1,4. Рассматриваемый стенд равен 8. Таблица выше равна 1, а таблица ниже равна 9. Таким образом, для Col J и Col I выше потребуется 2 формулы.
Ожидаемый ответ для приведенных выше примеров данных :
Row Col G Col H Col I colJ col K
1 Bench Strip Block BenchAbove BenchBelow
2 1 1 4 - 8
3 1 1 5 - 8
4 1 1 6 - 8
5 1 1 7 - 8
6 1 1 8 - 8
7 8 1 4 1 9
8 8 1 5 1 9
9 8 1 6 1 9
10 8 1 7 1 9
11 8 1 8 1 9
12 9 1 4 8 -
13 9 1 5 8 -
14 9 1 6 8 -
15 9 1 7 8 -
Комментарии:
1. Какую версию Excel вы используете?
2. Использование Excel 2016
Ответ №1:
Возможно, в J2 :
=IFERROR(LOOKUP(2,1/((H$1:H1=H2)*(I$1:I1=I2)),G$1:G1),"-")
В K2 :
=IFERROR(INDEX(G3:G$16,MATCH(1,INDEX((H3:H$16=H2)*(I3:I$16=I2),),0)),"-")
Тем не менее, я нахожу ваш вопрос немного запутанным, поэтому этот ответ может быть немного неточным.
Комментарии:
1. Привет, спасибо за ответ, но он не сработал. Я обнаружил, что вы не используете Col H и Col I в своих формулах.. Возможно ли их включить? . Я ищу следующий (более высокий и более низкий) Col G для каждого набора I, J комбинаций. Например, (1,4) представляет собой комбинацию. В приведенных мною данных другими комбинациями могут быть (1,5), (1,6) и т. Д. У меня есть несколько других строк в реальных данных. Итак, если вы должны были отфильтровать в Excel для Col H = 1 и Col I = 4, вы получите в Col G следующие 1,8,9. Следовательно, позиция строки выше 8 равна 1, а ниже 8 — 9. Это не имеет отношения к 1 или -1. Спасибо
2. @Ranajit, я отредактировал свой ответ, поскольку теперь он немного понятнее. Посмотрите, работает ли это для вас.
3. Спасибо за отредактированный ответ, он отлично сработал. За исключением последней строки — но это нормально, я могу управлять этим вручную. Большое спасибо за вашу помощь
4. @Ranajit, видишь, как я в своем примере ссылался на строку 16 вместо 15 , чтобы заставить это работать? Кроме того, если он ответил на ваш вопрос, пожалуйста, примите ответ, нажав на галочку слева от ответа
Ответ №2:
Если у вас есть Office365 , то вы можете MAXIFS(), MINIFS() легко использовать, чтобы получить BenchAbove и. BenchBelow Попробуйте-
=MAXIFS(A2:A15,B2:B15,B7,C2:C15,C7)
=MINIFS(A2:A15,B2:B15,B7,C2:C15,C7)
РЕДАКТИРОВАТЬ: решение для Excel-2016
Попробуйте следующую формулу-
=INDEX($A$2:$A$15,AGGREGATE(14,6,ROW($A$2:$A$15)-ROW($A$1)/(($B$2:$B$15=B7)*($C$2:$C$15=C7)),ROW(1:1)))
=INDEX($A$2:$A$15,AGGREGATE(15,6,ROW($A$2:$A$15)-ROW($A$1)/(($B$2:$B$15=B7)*($C$2:$C$15=C7)),ROW(1:1)))
Комментарии:
1. Спасибо, Харун, за ваш быстрый ответ. Я в Excel 2016, попробовал формулы, но это не сработало, я получаю #NAME? . Есть ли эквивалент в Excel 2016
2. Спасибо, Харун, за измененный ответ. Извините, я не получаю желаемого ответа. Я попытался =INDEX($ G $ 2: $ G $ 30000, AGGREGATE(14,6, СТРОКА ($ G $ 2: $ G $ 30000) -СТРОКА ($ A $ 1)/(($ H $ 2: $ H $ 30000= H2)*($ I $ 2: $ I $ 30000 = I2)), СТРОКА (1: 1))) Мои данные для Bench — это столбец G, разделите на столбик H и заблокируйте на столбик I. Я правильно понял?
3. Извините, я, возможно, не уточнил это в вопросе, мне нужны BenchAbove и BenchBelow для каждой строки, а не только для строки 7. Только то, что для скамьи 1 скамьи выше нет, а для скамьи 9 — скамьи ниже нет
4. @Ranajit Просто настройте формулу следующим образом
=INDEX($G$2:$G$30000,AGGREGATE(14,6,ROW($G$2:$G$30000)-ROW($A$1)/(($H$2:$H$30000=H2)*($I$2:$I$30000=I2)),ROW($1:$1)))5. С этим изменением у меня есть все строки с одинаковым ответом или 9 и 1.