EXCEL - Вложенные диапазоны для ссылок

Question

спасибо за чтение моего вопроса.

Я пытаюсь найти ценовые категории для разных размеров пакетов товаров. У меня есть диапазон данных на Sheet1, A1:Z102, с верхней строкой в качестве массы пакетов (B1:Z1) и первым столбцом (A2:A102) в качестве цены продажи. В массиве я рассчитал размер прибыли (B2:Z102)

Я пытаюсь настроить «что, если» на листе автоматически, что позволяет мне вводить различные веса »Sheet2!B1:B10 "- это выберет соответствующий столбец весов упаковки из" Sheet1!A2:Z2 ", а затем используйте функцию MATCH, ближайшую строку этого столбца к моей" марже прибыли "что, если" "Sheet2!A1 ", и, наконец, вернемся к Листу 2, C1:C10, цене продажи, которая ближе всего к этой марже прибыли.

SHEET1
       1lbs    2lbs    3lbs    4lbs
$1     $0.10  -$0.80  -$1.80% -$2.80
$2     $1.74   $0.74  -$0.26  -$1.26
$3     $2.61   $1.61   $0.61  -$0.39
$4     ...     ...     ...     $0.47
...

SHEET2
$0.20  2lbs    X
       4lbs    X

[Edit1] Таким образом, функция вернет C1 = $ 2, а C2 = $ 4 за три итеративных шага (я думаю).

1. Cell.Лист2!B1.Значение соответствует одинаковому столбцу в Sheet1 Row1: возвращает столбец Y
2. Для столбца Y на листе 1 найдите значение, наиболее близкое к положительному значению ячейки.Sheet2A1: возвращает столбец Y, строка Z
3. Для столбца Лист1 Y возвращаемое значение строки Z в строке 1 Столбец 1 строки Z. повторить это значение в листе C1 C1
4. Повторите 1-3 для B2 ...Bn

Мне трудно решить каждый шаг:

1. MATCH может определить правильный столбец
2. Не могу понять, как вставить MATCH в "OFFSET", чтобы искать только столбец Y. Я пробовал INDEX MATCH, INDEX MATCH MATCH, LOOKUPs(V & H).
3. Вернуть значение Column1 в RowZ.

Я понимаю, что это может быть решение для макросов, но я просто не знаю, как это VBA:

Dim profit, lbs, cost, reflbs, refprofit As Range

Set cost = Worksheets("Sheet1").Range("$A$2", "$A$5")
Set profit = Worksheets("Sheet1").Range("$B$2", "$E$5")
Set lbs = Worksheets("Sheet1").Range("$B$1", "$E$1")
Set reflbs = Worksheets("Sheet2").Range("$B1")
Set refprofit = Worksheets("zTest").Range("$H$39")

For Each profitCell In profit
    For Each costCell In cost
        For Each lbsCell In lbs
            If lbsCell.Value = reflbsCell.Value Then

Тогда я не уверен, как направить поиск в столбце, заголовок которого lbs соответствует "reflbs", и тогда я бы вставил:

If profitCell.Value = refprofitCell.Value Then

Затем верните строку Z для матча, а затем верните строку Z, столбец A Sheet1! в листе 2!С1.

Огромное спасибо!

Answer 1

Предполагая, что ваши исходные данные изложены, как ваш пример следующим образом:

Затем на листе 2, если ваши данные выложены следующим образом:

Затем вы можете использовать следующую формулу, чтобы получить цифры в красном:

=INDEX(Sheet1!$B$2:$E$5,IFERROR(MATCH(Sheet2!$A$1,Sheet1!$A$2:$A$5,1),1),MATCH(Sheet2!$B1,Sheet1!$B$1:$E$1,0))

Когда искомое значение в долларах меньше, чем первое значение в столбце, возможно, вы получили ошибку. Способ vlookup и match работает так, что они не ищут значения ближайшего числа. Они ищут последний номер, который был меньше, чем они искали. Вот почему ваш столбец должен быть отсортирован в порядке возрастания. Чтобы иметь дело с допустимым значением, которое меньше первого значения в списке, функция сопоставления, которая будет выдавать ошибку, обернута в функцию iferror, которая возвращает 1 для представления первой строки. Обратите внимание, что если в sheet2 введена неверная запись для цены!A1, он все равно вернет 1 для первого ряда.

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