#arrays #google-sheets #filter #google-sheets-formula #google-query-language
Вопрос:
Был бы очень признателен за некоторую помощь в решении этой проблемы, с которой я боролся некоторое время. Я полагаю, что ответ находится где-то в Match, Index, Indirect, ArrayFormula, но я изо всех сил пытаюсь его найти.
Поэтому моя главная цель-определить стоимость продукта на основе 3 (технически двух) оценок. Входные данные, которые у меня есть, — это уровень и вес.
| Пример | Уровень | Вес, кг | Себестоимость продукции |
|---|---|---|---|
| Пример 1 | SPC | 0.65 | ? |
| Пример 2 | STE | 0.15 | ? |
Справочная таблица, из которой будет получена стоимость продукта, приведена ниже:
| Ряд | Уровень | Минимальная масса(кг) | Максимальная масса (кг) | Себестоимость продукции |
|---|---|---|---|---|
| 1 | sme | 0.00 | 0.10 | 4.0 |
| 2 | STE | 0.00 | 0.10 | 4.5 |
| 3 | STE | 0.10 | 0.25 | 4.7 |
| 4 | STE | 0.25 | 0.50 | 5.0 |
| 5 | LE | 0.00 | 1.00 | 5.5 |
| 6 | SPC | 0.00 | 0.25 | 5.7 |
| 7 | SPC | 0.25 | 0.50 | 6.0 |
| 8 | SPC | 0.50 | 1.00 | 6.5 |
| 9 | SPC | 1.00 | 1.50 | 7.0 |
Чтобы получить стоимость продукта, мне нужно оценить уровень, а затем оценить, в какой диапазон минимального и максимального веса попадает вес продукта (для этого уровня).
Итак, если взять пример 1, уровень-это SPC, который делает строки с 6 по 9 допустимыми. Тогда вес равен 0,65, что находится между минимальным и максимальным значениями веса 0,5 кг и 1,0 кг в строке 8. Поэтому стоимость продукта составляет 6,5.
Пожалуйста, обратите внимание, что оба критерия важны, поскольку диапазоны веса перекрываются между уровнями.
Любая помощь в том, как это оценить, будет высоко оценена.
Спасибо
Комментарии:
1. сколько вариантов уровней у вас есть в вашем реальном наборе данных?
2. Их 5. Всего строк около 45. Немного.
Ответ №1:
пробовать:
=INDEX(IFNA(VLOOKUP(VLOOKUP(F2:F, {UNIQUE(FILTER(A2:A, A2:A<>"")),
SEQUENCE(COUNTUNIQUE(A2:A))*10000}, 2, 0) G2:G, {
VLOOKUP(A2:A, {UNIQUE(FILTER(A2:A, A2:A<>"")),
SEQUENCE(COUNTUNIQUE(A2:A))*10000}, 2, 0) B2:B, D2:D}, 2, 1)))
как это работает:
демонстрационная электронная таблица
Комментарии:
1. Вау, прежде всего, спасибо вам за молниеносную реакцию. какое удивительное сообщество. Во-вторых, формула абсолютно работает! Моя единственная проблема в том, что, когда я копирую и вставляю его, это выдает ошибку, что нижняя ячейка будет перезаписана. таким образом, он отображает ответ на одну строку ниже. Извините, я немного не в себе, так что немного распаковки формулы действительно поможет. Так что фильтр, МОД и последовательность-все это ново для меня. Я посмотрел на функции и попытался разобраться в них. На данный момент я действительно хотел бы понять, где оценивается минимальный максимальный вес :S.
2. @AdnanM его ARRAYFORMULA, что означает, что он автоматически вычисляет все строки, а это значит, что вам нужно очистить весь столбец и использовать его в первой строке, где начинается ваш набор данных. вы можете удалить столбец max. он использует только минимальный столбец, и формула ищет точное совпадение или более высокое приближение. с удовольствием объясню всю формулу, если хотите…
3. @AdnanM смотрите основы: webapps.stackexchange.com/a/123730/186471
4. честно говоря, если бы вы могли объяснить всю формулу целиком, я был бы вам очень признателен. Я пытался воспроизвести это по частям, но я не могу понять логику. Заранее большое спасибо за помощь.
5. Уважаемый @player0. Это так любезно с вашей стороны. Какая великолепная логика и фантастическая пошаговая инструкция! Я шел совершенно другим путем, и в этом гораздо больше смысла. Миллион раз спасибо за вашу поддержку. И извините за задержку с ответом, на моем конце был 1 час ночи, так что я не мог держать глаза открытыми.