Excel 2010: как ссылаться на определенное поле, используя определенное имя столбца и номер строки?

Question

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

Я настроил три листа: Продукты, Категория по умолчанию и Рассчитано. Столбцы - это сведения о продукте / категории, и каждый элемент / категория имеет свою собственную строку. Я также хотел бы максимально использовать именованные столбцы для удобства чтения. Имя столбца, начинающееся с P, - это подробности о продукте, а имя, начинающееся с D, - из листа CategoryDefault.

Поля в Calculated выглядят примерно так:

=IF(Products!G2="",
INDEX(DPRICE, MATCH(Products!A2, DCAT, 0)),
Products!G2)

где DPRICE - именованный столбец для цены по умолчанию, а DCAT - имя категории для листа CategoryDefaults.

Формула означает следующее: Если столбец G с номером продукта 2 пуст, найдите значение столбца «Продукт 2» в листе CategoryDefault и верните DefaultPrice. В противном случае вернуть определенную цену.

Пока все это работает нормально. Я хотел бы сделать еще один шаг вперед: в предыдущем примере Products!G2 относится к столбцу цен на продукт в строке 2. Я хотел бы сделать формулу более читабельной, заменив буквенную ссылку на столбец «G» именованной ссылкой на столбец, а именно «PPRICE». Я бы сделал то же самое со ссылкой на Продукты!A2, вызвав столбец «PCAT» (категория, к которой относится продукт)

Окончательная формула будет выглядеть примерно так:

=IF(Products!PPRICE:2="",
INDEX(DPRICE, MATCH(Products!PCAT:2, DCAT, 0)),
Products!PPRICE:2)

Я просто не могу заставить это работать. Я пробовал «Применить имена ...» безрезультатно, без изменений двоеточий и $ безрезультатно. Кто-нибудь делал это раньше?

Answer 1

Я обнаружил, что INDEX(PPRICE, 2) будет возвращать значение второй строки в столбце PPRICE. Весь фрагмент кода будет выглядеть так:

=IF(INDEX(PPRICE, 2)="",
INDEX(DPRICE, MATCH(Products!A2, DCAT, 0)),
INDEX(PPRICE, 2))

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