У меня есть рабочий лист, использующий необработанные данные, которые Excel не идентифицирует как таблицы (т. е. просто помещает значения в соседние ячейки), а в другой части моего рабочего листа формулы, выполняющие поиск в этих данных, например:
VLOOKUP(D$1, Config!$C$4:$E$8,2,FALSE)
Все идет отлично, пока я не решаю объявить свои необработанные данные в виде таблицы (Ctrl-T), чтобы получить некоторые дополнительные функции, такие как автоматическое форматирование и автоматическое расширение таблицы.
После объявления таблицы все мои формулы находят в ней значение #N/A, независимо от того, ссылаюсь ли я на зону поиска как на ссылку на ячейку ( Config!$C$4:$E$8) или по ее новому имени таблицы (измененному с помощью менеджера имен).
Ключ(Я не знаю, что это значит) заключается в том, что ключ поиска ( D$1в моей формуле) и ключи данных (самый левый столбец Config!$C$4:$E$8) являются числовыми (значениями типа 2015). Excel, похоже, использует текстовый поиск внутри таблицы и не может сопоставить 2015(в таблице) с "2015"(из D$1). Это только подсказка, я могу ошибаться.
Что я могу сделать, чтобы начать использовать таблицы (автоматическое расширение действительно полезно), не нарушая свои формулы?
РЕДАКТИРОВАТЬ :Все меняется, когда ключ поиска ( D$1) начинает быть частью таблицы: 2015 год теперь отображается в формулах как «2015»
решение1
Как отмечает sicarius92 в своем комментарии, работа с измененным типом данных путем его «приведения» к типу D$1 + 0работает для меня.
Тем не менее, это всего лишь обходной путь, и он не объясняет, почему помещение данных в таблицы волшебным образом меняет тип данных.
решение2
В Excel 2010 (не знаю точно, как это работало в 2007) преобразование диапазона в таблицу превращает содержимое заголовков в текст, независимо от содержимого. Если вы действительно хотите иметь числа в заголовках, вам придется отметить форматирование ячеек как «Число» или «Общее» (или как вам нужно) и заново вставить прежние данные, поскольку, по-видимому, Excel также отключает проверку несовместимых типов данных (и автоматическое решение, маленькую зеленую стрелку и ее меню).
решение3
О, господи, ребята...
Простое IFERROR()решение этой проблемы. Учитывая, что вы знаете, что искомое значение МОЖЕТ быть числовым в таблице поиска, но НЕ числовым в ячейке искомого значения, просто сделайте:
=IFERROR(VLOOKUP(D$1,Config!$C$4:$E$8,2,FALSE), VLOOKUP(VALUE(D$1),Config!$C$4:$E$8,2,FALSE))
Вышесказанное просто обобщает формулу. Если вы не хотите копировать ее в другое место, то вы ЗНАЕТЕ, что искомое значение — это число, которое обрабатывается как текст, и вам нужна только «неудачная» половина формулы. Если вы не знаете наверняка или СОВСЕМ не знаете, но хотите проверить ее на наличие проблем такого рода, используйте ее целиком.
Это работает и в обратную сторону. Допустим, у вас есть числовое искомое значение (например, Excel думает, что оно числовое {где здесь Excel думает, что искомое значение — текст}). Просто используйте TEXT()вместо VALUE()для его решения:
=IFERROR(VLOOKUP(D$1,Config!$C$4:$E$8,2,FALSE), VLOOKUP(TEXT(D$1,"@"),Config!$C$4:$E$8,2,FALSE))
(А если ни один из них не решит проблему, вас ждет кошмар поиска мусорных символов в одном или другом месте... Поскольку заголовки обычно не являются данными, полученными извне вашего безопасного, контролируемого мира, сначала попробуйте материал диапазона поиска. Если только вы не импортировали заголовки туда, где они находятся (ну, вы знаете, как импортировать весь материал, из которого берется искомое значение). Или если вы импортировали оба набора данных.)