Как предотвратить разрыв VLOOKUP после добавления столбца в именованный диапазон?

Question

Моя таблица Excel содержит множество VLOOKUP в именованном диапазоне, который я определил. Теперь, когда я добавил столбец в середине моего именованного диапазона, VLOOKUP, которые ссылаются на столбцы после вставленного столбца, теперь прерываются. Я понимаю проблему, но какой лучший способ исправить? Есть ли способ выяснить номер столбца из текста заголовка?

[ПРАВКА] Использование Казе идеи INDEX и MATCH казалось лучшим решением. Вот чем я закончил:

INDEX(MyTableData, MATCH("RowLabel", RowList, 0), MATCH("ColumnLabel", ColumnList,0))

где MyTableData - именованный диапазон всей таблицы, RowList - именованный диапазон заголовка строки, а ColumnList - именованный диапазон заголовков столбцов.

Answer 1

Лично я предпочитаю использовать комбо INDEX-MATCH для поиска вместо VLOOKUP . Вот пример из Pokedex, который я собирал для своей племянницы.

Чтобы получить скрытую способность Squirtle, я бы использовал эту формулу:

=INDEX(F2:F11,MATCH("Squirtle", A2:A11,0),1)

Это дает тот же результат, что и:

=INDEX(A1:G11,MATCH("Squirtle",A1:A11,0),MATCH("Hidden Ability",A1:G1,0))

Хорошая вещь в INDEX-MATCH заключается в том, что в большинстве случаев вам не нужно ссылаться на весь диапазон данных, поэтому первая формула должна работать, даже если вы регулярно добавляете столбцы и строки в свой диапазон данных. Он также имеет еще одно преимущество: поскольку вы ссылаетесь только на 2 одномерных диапазона, он вычисляется быстрее.

Тем не менее, вы все равно можете использовать MATCH с формулами VLOOKUP чтобы получить номер столбца.

=VLOOKUP(C1,NAMED_RANGE,MATCH("COLUMN_TEXT",$A$1:$H1,0),0)

где:
$A$1:$H$1 - это первая строка в вашем диапазоне данных / имен или строка, содержащая текст заголовка
NAMED_RANGE - это имя вашего диапазона данных
"COLUMN_TEXT" - текст заголовка для столбца, который содержит необходимые данные
C1 содержит ваше значение поиска

Редактировать: Добавлен пример соответствия индекса по запросу Дуга. : D

Answer 2

Вы можете использовать функцию MATCH :

Этот сайт имеет довольно хорошее объяснение того, как его использовать:
http://www.techonthenet.com/excel/formulas/match.php

Альтернативой является использование функции COLUMN для считывания фактического номера столбца (если ваша таблица начинается не с столбца A, вам необходимо вычесть номер первого столбца в формуле). Итак, пример:

 =VLOOKUP(B2,$B$2:$D$300,COLUMN($D$1)-Column($B$1), False)

Где столбец D содержит значение поиска

Answer 3

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

Answer 4

Вы также можете просто установить исходный vlookup, используя функцию COLUMN() для столбца со значением, которое нужно вернуть. В простой версии столбец значений поиска вашей таблицы находится в столбце A, а возвращаемое значение - в любом другом столбце, говорят в столбце L. Тогда используйте:

КОЛОННЫ (L:L)

для значения возвращаемого столбца. Сложнее, так как в столбце поиска нет столбца A? Просто вычтите из вышеперечисленного: (для поиска в столбце C)

КОЛОННЫ (L:L)-2

Значение здесь в том, что добавление и удаление столбцов не нарушает вашу формулу, поскольку Excel автоматически исправляет ее. Добавить столбец? Становится КОЛОННА (М: М). И так для удаления столбца. Все обрабатывается в Excel. И это действительно легко сделать. Колонны типа "WYO"? Excel не волнует. Найдите номер столбца вашего поискового столбца с помощью быстрой формулы COLUMN () в этом столбце, затем у вас есть значение для вычитания. Легко.

Без сомнения, менее мощный, чем Index/Match, и практика делает его идеальным, так что это сдерживает вас. Но это быстрый результат, который длится. И вы можете быстро научить этому других, пока вы оттачиваете свои навыки индекса / матча. Это также не так интуитивно, как использование именованных диапазонов, но часто вы не можете (Таблицы кто-нибудь?). (И это может быть базовое значение для именованного диапазона, в этом отношении.)

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

Answer 5

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

Вот один подход, который создает функцию под названием XLOOKUP:

Public Function XLOOKUP(ByRef row As Variant, ByRef column As Variant, ByRef table_array As Variant) As Variant
    XLOOKUP = CVErr(xlErrNA)
    Dim xRow As Long, xCol As Long
    With table_array
        For xRow = 1 To .Rows.Count
            If .Cells(xRow, 1).Value = row Then
                For xCol = 1 To .columns.Count
                    If .Cells(1, xCol).Value = column Then
                        XLOOKUP = .Cells(xRow, xCol).Value
                        Exit Function
                    End If
                Next
                Exit Function
            End If
        Next
    End With

End Function

Как только вы добавите его в свой проект, вы можете использовать его так:

=XLOOKUP(RowLabel, ColumnLabel, Range)