Как использовать формулу Excel для сравнения вычисленного значения с таблицей с учетом нескольких критериев

Question

У меня Excel 2016.

У меня есть два значения, которые получаются из некоторых формул, которые я хочу сравнить с таблицей и выводить либо "размер трубы в порядке", либо "размер трубы слишком мал".
Одно из значений, которое я получаю, относится к максимальной развернутой (dev) длине, которая должна быть округлена до следующего большего табличного значения. Затем, используя этот столбец в таблице, я беру количество имеющихся у меня единиц крепления и сравниваю их как с размером трубы, так и с максимальной длиной устройства. Если число узлов крепления больше максимально допустимого для этого размера трубы, учитывая максимально допустимую развернутую длину, тогда трубу необходимо будет увеличить. если количество узлов крепления меньше допустимого, тогда размер трубы в норме.

моя таблица в основном выглядит так:

distro pipe 40 60 80 100 ... <= максимальная длина устройства (футы)
1/2" 2.5 2 1.5 1.5 ... <= максимально допустимые единицы измерения
3/4" 9.5 7.5 6.5 5.5 ...
1" 32 25 20 16.5 ...
1-1/4" 32 32 27 21 ...
1-1/4" 32 32 32 32 ...
1-1/4" 80 80 80 75 ...
1-1/2" 87 87 87 87 ...
... ....

поэтому, учитывая мои другие расчеты, я получаю максимальную развернутую длину 49 футов для трубы 3/4 "и общее количество креплений 56. Мой результат должен вывести" размер трубы слишком мал "

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

Я использовал функцию соответствия индекса для округления максимальной развернутой длины до следующего более высокого значения столбца, однако я не могу найти формулу, чтобы затем ссылаться на значения единиц прибора.

=INDEX('pipe sizing charts'!$C$2:$M$2,MATCH($H$59,'pipe sizing charts'!$C$2:$M$2,1)+(LOOKUP($H$59,'pipe sizing charts'!$C$2:$M$2)<>$H$59))

где C2:CM - мои номера таблицы максимальной длины dev, а H59 - максимальная длина dev секции, которую я измеряю

Answer 1

Я думаю, что у меня есть это.

Мои предположения из вашего вопроса:

• Ваша формула INDEX находит правильный столбец
• Вам нужно пройти вниз по этому столбцу, чтобы найти значение на один шаг выше, чем рассчитанное вами значение.
• У вас уже есть значение, которое вы ищете (в случае вашего вопроса: 56)
• Вам нужно вернуть данные из столбца B (я думаю, что это ссылка на ваш первый столбец)

Итак, если мои предположения верны, сначала нужно отсортировать данные в таблице в порядке убывания.

Затем вам нужно вернуть букву столбца столбца, который вы хотите найти вниз. Обертывание формулы INDEX в функцию ЗАМЕНА / АДРЕС должно работать

= ЗАМЕНА (АДРЕС (1, КОЛОННА (ИНДЕКС)("таблицы размеров труб"!$ C $ 2:$ M $ 2, MATCH ($ H $ 59, "таблицы размеров труб"!$ C $ 2:$ M $ 2,1)+(LOOKUP ($ H $ 59, "таблица размеров труб")!$ C $ 2:$ M $ 2)<> $ H $ 59))), 4), "1", "")

Теперь, когда у вас есть буква столбца, вы можете использовать INDIRECT, чтобы написать свой INDEX/MATCH

= ИНДЕКС («таблицы размеров труб»!В: В, ПОИСКПОЗ ($ H $ 59, КОСВЕННЫЕ (хх & ":" & хх), - 1))

Xx в приведенной выше формуле является ссылкой на ячейку, в которой вы ввели формулу SUBSTITUTE выше. В этом случае MATCH работает, так как тип соответствия «-1» ищет следующее число, большее, чем искомое, но данные должны быть отсортированы в порядке убывания, чтобы он работал.

Вышесказанное должно сделать это, возможно, с небольшими изменениями, чтобы вписать его в свой лист.

Дайте мне знать, как вы идете.