☰

Excel: комбинация ИНДЕКС( ,ПОИСКПОЗ( ), )) работает с диапазонами, но не с данными, отформатированными как ТАБЛИЦА

tag-icon • tag-icon microsoft-excel
Excel: комбинация ИНДЕКС( ,ПОИСКПОЗ( ), )) работает с диапазонами, но не с данными, отформатированными как ТАБЛИЦА

Установка:

У меня есть два листа Excel с данными, оба "отформатированы как таблица(ы)". Один из них - большой набор данных, со множеством строк и столбцов (полей), а другой - своего рода "словарь", всего с 2 строками и 2 столбцами (полями).

Таблица 1 (набор данных)

+----------------------------------------------------+
| month | week | productName | price     | sold pcs. |
+---------------------------------------------------+|
| jan   | 1    | heavy       | (formula) | 25        |
| jan   | 2    | heavy       | (formula) | 51        |
| jan   | 3    | heavy       | (formula) | 06        |
| jan   | 4    | heavy       | (formula) | 00        |
| jan   | 1    | light       | (formula) | 39        |
| jan   | 2    | light       | (formula) | 11        |
| jan   | 3    | light       | (formula) | 98        |

Таблица 2 (дикт.)

+---------------------+
| productName | price |
+---------------------+
|  heavy      | 125   |
|  light      | 65    |

Что мне нужно сделать:

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

Что я пробовал:

=INDEX(Table2[@price], MATCH(Table1[@productName], Table2[@productName], 0))

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

Вот что я получаю в результате:

+-------------------------------------------------+
| month | week | productName | price  | sold pcs. |
+-------------------------------------------------+
| jan   | 1    | heavy       | 125    |  25       |
| jan   | 2    | heavy       | #N/A   |  51       |
| jan   | 3    | heavy       | #VALUE |  06       |
| jan   | 4    | heavy       | #VALUE |  00       |
| jan   | 1    | light       | #VALUE |  39       |
| jan   | 2    | light       | #VALUE |  11       |
| jan   | 3    | light       | #VALUE |  98       |

Первая строка правильно сопоставлена, вторая строка (опять же такая же, как и первая) не найдена, и оттуда начинаются значения ошибок. Что мне делать?

Спасибо

решение1

Вы неправильно используете именованные диапазоны:

  • Table2[@productName]указывает на один элемент в таблице
  • Table2[productName]указывает на весь столбец
  • Table2указывает на все столбцы и строки таблицы, за исключением заголовков

Это можно легко сделать с помощью функции ВПР:

=INDEX(Table2,MATCH([@productName],Table2[productName],0),MATCH("price",Table2[#Headers],0))

Альтернативные решения:

=VLOOKUP([@productName],Table2,2,0)

=VLOOKUP([@productName],Table2,MATCH("price",Table2[#Headers]),0)

Или, если каждое productName уникально, можно использовать функцию SUMIF:

=SUMIF(Table2[productName],[@productName],Table2[price])

решение2

Это должно вам подойти:

=INDEX(Table2[[#All],[Price]],MATCH([@productName],Table2[[#All],[ProductName]],0),1)

Обратите внимание: при использовании #ALL выбирается весь столбец, а не только СТРОКА.

Связанный контент