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

491
YAtreyu

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

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

Я пытаюсь настроить «что, если» на листе автоматически, что позволяет мне вводить различные веса «Sheet2! B1: B10» - это выберет соответствующий столбец весов упаковки из «Sheet1! A2: Z2», затем возьмите функцию MATCH, ближайшую строку этого столбца, к моей марже прибыли «что, если» «Лист2! 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.Sheet2! B1.Value соответствует одинаковому столбцу в Sheet1 Row1: возвращает столбец Y
  2. Для столбца Y в Sheet1 найдите значение, ближайшее к положительному значению для Cell.Sheet2A1: возвращает столбец Y, строка Z
  3. Для столбца Y Листа1, строка Z возвращает значение в Листе 1 Столбец 1, строка Z. Повторите это значение в C1 Лист2
  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.

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

0
Похоже, у вас есть хорошее представление о том, что нужно сделать. Можете ли вы дать нам знать, какую формулу вы пробовали до сих пор и как она не дает ожидаемого результата? cybernetic.nomad 5 лет назад 0
Спасибо, что вернулись ко мне. У меня есть экспериментальная таблица с H38 в качестве желаемого веса сумки (6 фунтов, пользовательское отформатированное число, поэтому ячейка имеет только 6, то есть «$» 0,00, если доллары, или 0,0 «фунты», если вес). Сначала у меня есть функция, чтобы найти относительный номер столбца соответствующего веса: = MATCH (H38, G40: M40), который возвращает «7». Я попробовал LOOKUP (и H & V LOOKUP), понимая, что ему нужен возрастающий список. Я также пробовал INDEX MATCH и INDEX MATCH MATCH. Моей последней попыткой было СМЕЩЕНИЕ от «А1» и от АДРЕСА, установив для столбцов смещения значение «7», найденное до этого, -1, чтобы попасть в столбец «А». YAtreyu 5 лет назад 0

1 ответ на вопрос

0
Forward Ed

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

enter image description here

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

enter image description here

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

=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))

enter image description here

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

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

Эй, спасибо, что вернулся ко мне. Я попытался использовать вашу формулу, и она работает именно так, как вы предложили. Мне нужно сделать еще один шаг, когда формула «находит» C2 в Sheet1! сообщить значение в Sheet1! Столбец A для той же строки, т. Е. Укажите идеальную цену пакета в сравнении с прибылью от цены такого размера. YAtreyu 5 лет назад 0
Таким образом, вместо 0,80 долларов вы хотите, чтобы он вернул 1 доллар? Forward Ed 5 лет назад 0
Ага! Это поиск в фунтах, которые я установил, чтобы соответствовать весу в столбце, затем поиск в этом столбце прибыли, которую я установил, затем в строке, в которой находится прибыль, и возврат цены, по которой устанавливается продукт. YAtreyu 5 лет назад 0
Таким образом, 0,80 долл. - это 1 долл. От 0,20 долл., А 0,74 долл. - всего 0,44 долл. Какое ожидаемое значение для X в 2 фунта и 4 фунта вы хотите увидеть? Все еще пытаюсь обернуть мою голову вокруг того, какой ряд ты пытаешься выбрать и почему. Forward Ed 5 лет назад 0

Похожие вопросы