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

Question

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

Объясняется с помощью общего примера

Справочная таблица

| Name | From | To |
| A    |    1 |  5 |
| B    |    6 |  8 |
| C    |    9 |  9 |

Исходная таблица

| Name | Value |
|      |     0 |
|      |     4 |
|      |     9 |
  ^
  Field is getting "calculated".

Таблица результатов

| Name | Value |
|      |     0 |
| A    |     4 |
| B    |     6 |
| C    |     9 |
  ^
  Field is getting "calculated".

Конкретное объяснение

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

В моей таблице источников / результатов (то же самое) у меня есть список частот, и я хотел бы, чтобы ему автоматически присваивалась информация о полосе и ваттах.

Справочная таблица

|  Band | Frequency Range | Watts |
| 160 m |  1.810 |  1.850 |   100 |
| 160 m |  1.890 |  2.000 |    10 |

Таблица ожидаемых результатов

             First part is source field for the look up.
             v
|  Band | Frequency Range | Watts | Description |
| 160 m |  1.811 |  1.811 |   100 | Foo Bar     |
| 160 m |  1.891 |  1.897 |    10 | Lorem ipsum |
    ^                         ^
    Field is "calculated".    Field is "calculated".

Заключение

Как определить (когда это вообще возможно из-за диапазона) формулу Excel, которая выбирает правильную ссылочную строку матрицы из матрицы, одновременно ища одно значение в пределах диапазона из матрицы?

Формула должна быть совместима с Excel 97, но, по крайней мере, с Excel 2000.

Answer 1

В основном вам нужно использовать VLOOKUP чтобы получить эту информацию.

Сначала скопируйте / переместите столбец "от", чтобы он был самым левым, чтобы ваши столбцы были упорядочены: A (от), B (имя), C (к).

Если у вас есть непрерывный диапазон (например, вы можете считать 1,870 в своем примере членом первой группы), тогда ваша формула (в A2 таблицы результатов): =IFERROR(VLOOKUP(B2,reference_sheet!A:C,2,true),"")

Если вы хотите получить пустые значения, когда ваша частота находится между диапазонами, то ваша формула будет более сложной (все еще используя ссылки в соответствии с общим примером):

• сначала получите строку вашей группы, игнорируя верхний предел: =IFERROR(MATCH(B2,reference_sheet!A:C,1),"") - укажите это, например, в столбце C

• затем проверьте, находится ли ваше значение в диапазоне: =IFERROR(B2<=index(reference_sheet!C:C,C2),FALSE)

Теперь у вас есть номер строки в столбце D и TRUE в столбце E для каждой из ваших записей, с помощью функции INDEX теперь вы можете собирать необходимые данные. (также возможно сделать все без какого-либо вспомогательного столбца, но формулы в этом случае будут намного сложнее)

Answer 2

Если вы готовы изменить свою справочную таблицу так, чтобы столбец диапазона был самым левым столбцом, то функция VLOOKUP должна делать то, что вы хотите. Поскольку вы пытаетесь сопоставить диапазоны, а не конкретные значения, вы должны убедиться, что ваша справочная таблица находится в отсортированном по порядку порядке, и передать значение TRUE в VLOOKUP в качестве 4-го параметра.

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

Все эти функции доступны в Excel 2007 и более поздних версиях.