Если ошибка впр excel примеры

В данной статье расскажу о двух ошибках которые может выдать функция ВПР() :

Перечисленные выше ошибки наиболее часто встречаться при использовании функции ВПР() и очень часто вызывают трудности с устранением у начинающих пользователей Excel .

Когда возникает ошибка #Н/Д и как от нее избавиться при использовании ВПР().

Сообщение об ошибке Н/Д можно расшифровать как аббревиатуру (НД) – нет данных, то есть функции ВПР() нечего отобразить, и она как бы сообщает: «нет данных для отображения».

Почему возникает ошибка Н/Д (НД)?

1. Ошибка может возникать потому, что в Вашем списке (диапазоне) для сравнения нет искомого функцией ВПР() значения.
2. Ошибка может возникать потому, что в Вашем списке (диапазоне) для сравнения значения ячеек имеют ошибки. Иногда ошибки нельзя увидеть «не вооружённым глазом», например, если в ячейке добавлен лишний пробел или едва заметная точка. ВПР() воспринимает значение ячейки без пробела и с пробелом как совершенно разные данные и выдает ошибку «Н/Д».
3. Ошибка может возникать потому, что в искомой ячейке уже стоит значение «Н/Д», то есть ВПР() подтягивает эту ошибку из другой ячейки (искомой).

Как исправить ошибки Н/Д?

1. Первый способ – применить обработку ошибок – функцию ЕСЛИОШИБКА(ВПР(*;*;*;0);”Здесь была ошибка”). Эта функция заменяет сообщение об ошибке на любое значение, которое Вы укажете.
2. Способ №2 – удалить все пробелы и, по возможности, знаки препинания из ячеек. Для этого нужно нажатием клавиш ctrl+H вызвать окно замены значений, потом в поле «Найти» ввести пробел или знак препинания, а в поле «Заменить на:» не вводить ничего и нажить кнопку «Заменить все».
3. Способ №3 – поставить в функции ВПР() допуск ошибки. Как нам извесчтно 4 –й аргумент функции это число ошибок которые может допускать в сравниваемой строке функция ВПР(). То есть, если поставить число «1», то допускается 1 ошибка при сравнении [ВПР(*;*;*;1)]. В таком случае строка без пробела и с одним пробелом будут считаться идентичными. Но в таком способе есть подвох — очень высока вероятность неверных результатов, например, слово «полка» и «палка» имеют отличие всего в один знак и будут восприняты функцией, как одно и то же.

Когда возникает ошибка #ЗНАЧ и как от нее избавиться при использовании ВПР().

Ошибка #ЗНАЧ может выводиться функцией ВПР(), если введенные значения аргументов функции некорректны и функция не может их обработать.

Казалось бы какие значения могут быть некорректными, если ВПР() необходимо просто сравнить одно значение с другим и присвоить ячейке данные из совпавших ячеек, но эта ошибка возникает.

Появляется ошибка #ЗНАЧ в функции ВПР() тогда, когда длина строки сравниваемой функцией слишком большая и не может быть обработана. Например, в Excel 2010 максимальная длина строки обрабатываемой функцией всего 255 символов, и если Вы будете сравнивать строки длиной 256 и более символов, то получите ошибку #ЗНАЧ.

Исправить ошибку #ЗНАЧ в таком случае можно уменьшив длины сравниваемых строк.

Еще ошибка #ЗНАЧ может возникнуть если Вы пропустили(не указали) один из аргументов в функции.

Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).

В этом разделе описаны наиболее распространенные причины появления ошибочных результатов функции ВПР и предлагаются рекомендации по использованию функций индекс и ПОИСКПОЗ .

Совет: Кроме того, просмотрите краткий справочник: советы по устранению неполадок , связанные с #NA проблем в УДОБНОМ PDF-файле. Вы можете предоставить общий доступ к PDF-файлу другим пользователям или принтерам.

Проблема: искомое значение не находится в первом столбце аргумента таблица

Одно из ограничений функции ВПР состоит в том, что оно может только искать значения в крайнем левом столбце в массиве таблиц. Если искомое значение не находится в первом столбце массива, отображается ошибка #N/A.

В следующей таблице нам нужно узнать количество проданной капусты.

Ошибка #N/A, так как в втором столбце (фрукты) аргумента " Таблица " a2: C10 отображается значение подстановки "первое" . В этом случае Excel ищет его в столбце A, А не в столбце B.

Решение: вы можете попытаться устранить эту проблему, НАСТРОИВ функцию ВПР таким образом, чтобы она ссылалась на правильный столбец. Если это невозможно, попробуйте переместить столбцы. Это также может быть очень импрактикабле, если у вас много электронных таблиц, где значения ячеек являются результатами других вычислений, или есть и другие логические причины, по которым просто не удается переместить столбцы. Для решения этой проблемы необходимо использовать сочетание функций индекс и ПОИСКПОЗ, которое может искать значение в столбце независимо от его положения в таблице подстановки. Сведения см. в следующем разделе.

Вместо этого используйте индекс и сопоставление

Функции индекс и соответствие являются хорошими вариантами для многих случаев, в которых ВПР не отвечает вашим потребностям. Ключевое преимущество функции индекс/соответствие заключается в том, что вы можете найти значение в столбце в любом месте таблицы подстановки. Функция индекс возвращает значение из указанной таблицы или диапазона (в зависимости от его положения). Функция MATCH Возвращает относительное положение значения в таблице или диапазоне. Использование ИНДЕКСов и СОВПАДЕНИй в формуле для поиска значения в таблице или массиве путем указания относительной позиции значения в таблице или массиве.

Существует несколько преимуществ использования функции индекс и СОВПАДЕНИе вместо функции ВПР.

При использовании индекса и совпадения возвращаемое значение не обязательно должно находиться в том же столбце, что и столбец подстановки. Это отличается от функции ВПР, в которой возвращаемое значение должно находиться в указанном диапазоне. Как это делается? При использовании функции ВПР необходимо знать номер столбца, который содержит возвращаемое значение. Несмотря на то, что это может показаться несложным, оно может быть утомительным при наличии большой таблицы и для подсчета количества столбцов. Кроме того, при добавлении или удалении столбца в таблице необходимо пересчитать и обновить аргумент Номер_столбца . При использовании ИНДЕКСов и СОВПАДЕНИй подсчет не требуется, так как столбец подстановок отличается от столбца с возвращаемым значением.

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

Индекс и совпадение можно использовать для поиска значений в любом столбце. В отличие от функции ВПР, в которой можно найти значение в первом столбце таблицы, функция индекс и СОВПАДЕНИе будут работать, если искомое значение находится в первом столбце, в последнем или в любом месте.

Функция индекс и соответствие предлагают гибкость создания динамической ссылки на столбец, содержащий возвращаемое значение. Это означает, что вы можете добавлять столбцы в таблицу, не нарушая индекс и не подойти. С другой точки, ВПР прерывает, если нужно добавить столбец в таблицу, так как он формирует статическую ссылку на таблицу.

Индексирование и сопоставление обеспечивает большую гибкость при использовании совпадений. ИНДЕКС и СОВПАДЕНИе могут находить точное совпадение или значение, которое больше или меньше значения подстановки. ФУНКЦИЯ ВПР будет искать только наиболее подходящие значения (по умолчанию) или точное значение. ФУНКЦИЯ ВПР также предполагает, что первый столбец в массиве таблицы отсортирован в алфавитном порядке, и предположим, что таблица не настроена таким образом, функция ВПР вернет первое ближайшее соответствие в таблице, которая может не быть искомыми данными.

Синтаксис

Чтобы создать синтаксис для функции индекс и ПОИСКПОЗ, необходимо использовать аргумент array/Reference из функцией индекс и вложить в него синтаксис MATCH. Форма будет иметь следующий вид:

=ИНДЕКС(массив или ссылка; ПОИСКПОЗ(искомое_значение;массив;[тип_совпадения])

Используйте ИНДЕКСы и СОВПАДЕНИЯ для замены функции ВПР из приведенного выше примера. Синтаксис будет выглядеть следующим образом:

= Индекс (возвращающий значение из значения C2: C10, которое будет соответствовать (первое), которое находится где-то в массиве B2: B10, где возвращаемое значение является первым значением, соответствующим первое).

Формула ищет в C2:C10 первое значение, соответствующее значению Капуста (B7), и возвращает значение в ячейке C7 ( 100).

Проблема: не найдено точное совпадение

Если аргумент Интервальный_просмотр имеет значение ложь, а ВПР не может найти точное соответствие в данных, возвращается ошибка #N/a.

Решение: Если вы уверены в том, что соответствующие данные есть в вашей электронной таблице, и ВПР не перехватите, проверьте, нет ли скрытых пробелов и непечатаемых знаков в ссылочных ячейках. Кроме того, убедитесь, что ячейки имеют правильный тип данных. Например, ячейки с числами должны отформатированы как число, а не как текст.

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

Проблема: искомое значение меньше, чем наименьшее значение в массиве

Если аргумент Интервальный_просмотр имеет значение истина, а значение подстановки меньше, чем наименьшее значение в массиве, отображается ошибка #N/a. ЗНАЧЕНИЕ TRUE ищет приблизительное совпадение в массиве и возвращает ближайшее значение меньше, чем искомое значение.

В приведенном ниже примере искомое значение равно 100, но в диапазоне B2:C10 нет значений меньше 100, поэтому возникает ошибка.

Исправьте искомое значение.

Если не удается изменить значение подстановки и требуется большая гибкость при использовании совпадающих значений, попробуйте использовать функцию индекс и СОВПАДЕНИе вместо функции ВПР — см. раздел выше в этой статье. При использовании индекса или СОВПАДЕНИЯ можно искать значения, которые больше, меньше или равны значению подстановки. Дополнительные сведения об использовании функции индекс и СОВПАДЕНИе вместо ВПР см. в предыдущем разделе этой статьи.

Проблема: столбец подстановки не отсортирован в порядке возрастания

Если аргумент Интервальный_просмотр имеет значение истина, а один из столбцов подстановки не отсортирован в порядке возрастания (A – Z), появится ошибка #N/a.

Измените функцию ВПР для поиска точного совпадения. Для этого задайте для аргумента Интервальный_просмотр значение ложь. Для FALSE нет необходимости в сортировке.

Для поиска значения в несортированной таблице можно также использовать функции ИНДЕКС и ПОИСКПОЗ.

Проблема: значение является большим числом с плавающей запятой

Если в ячейках есть значения времени или большие десятичные числа, Excel возвращает ошибку #N/A из-за числа с плавающей точкой. Числа с плавающей запятой — числа, которые следуют за десятичной запятой. (В Excel хранятся значения времени в виде чисел с плавающей запятой.) Excel не может хранить числа с большими плавающими точками, поэтому для правильной работы функции числа с плавающей запятой необходимо округлить до пяти десятичных разрядов.

Решение. Округлите числа до 5 десятичных разрядов с помощью функции ОКРУГЛ.

У вас есть вопрос об определенной функции?

Помогите нам улучшить Excel

У вас есть предложения по улучшению следующей версии Excel? Если да, ознакомьтесь с темами на портале пользовательских предложений для Excel.

Это вторая глава книги Билла Джелена. Всё о ВПР: от первого применения до экспертного уровня. Раз вы читаете эту главу, видимо, ваша ВПР возвращает ошибку #Н/Д. Этот симптом может быть вызван одной из нескольких проблем. Чтобы найти лекарство, посмотрите, что из следующего применимо к вашему случаю:

• Большинство функций ВПР работают, но есть несколько разрозненных #Н/Д.
• #Н/Д нет в верхней части ваших данных, но #Н/Д встречаются всё чаще, по мере того, как вы двигаться вниз по диапазону. Скорее всего, вы забыли вставить знаки доллара в формулу, чтобы зафиксировать ссылку на таблицу просмотра.
• Все ВПР возвращают #Н/Д.
• ВПР работают для текстовых ячеек, но не для числовых.

Разрозненные #Н/Д. Проблема: отдельные ВПР выдают ошибки #Н/Д (рис. 2.1).

Рис. 2.1. BG33-9 – это новый товар, которого нет в таблице подстановки

Скачать заметку в формате Word или pdf, примеры в формате Excel

Такие ситуации встречаются, когда искомое значение не найдено в первом столбце Таблицы. В нашем примере товар BG33-9 не найден в таблице постановки (в диапазоне F2:H29). Добавьте элемент BG33-9 в таблицу подстановки (рис. 2.2). Исправьте формулу ВПР, расширив диапазон таблицы =ВПР(A2;$F$2:$H$30;3;ЛОЖЬ).

Рис. 2.2. Новый товар добавлен в таблицу подстановки

Чтобы при включении новых строк в таблицу подстановки не приходилось постоянно исправлять функцию ВПР, можно использовать один из следующих приемов:

• Вставьте новые ячейки в любом месте в середине таблицы подстановки. Например, выделите ячейки F28:H28, кликните правой кнопкой мыши, выберите опцию Вставить… и в открывшемся окне укажите Ячейки со сдвигом вниз.
• В качестве таблицы подстановки выберите не диапазон ячеек ($F$2:$H$29), а столбцы целиком – F:H. Теперь, вы можете добавлять элементы внизу таблицы подстановки без переписывания формул. Excel достаточно сообразителен, чтобы использовать только непустые ячейки при расчетах.
• Выберите любую ячейку в таблице подстановки и нажмите сочетание клавиш Ctrl+Т (Т английское). Это превращает диапазон в таблицу. При вводе новых значений в пустые строки ниже таблицы, она автоматически расширится. Теперь ваша ВПР будет ссылаться не на диапазон, а на ТАБЛИЦУ (рис. 2.3).

Рис. 2.3. Превратите диапазон таблицы подстановки в инструмент Excel ТАБЛИЦА; это позволит автоматически расширять его при добавлении новых строк

Чтобы не пропустить ошибки #Н/Д, отсортируйте данные по столбцу Описание по убыванию (рис. 2.4). Все ошибки соберутся вверху. Для этого выделите столбец D, перейдите на вкладку ДАННЫЕ и в области Сортировка и фильтры нажмите кнопку сортировки. В открывшемся окне выберите Автоматически расширить выделенный диапазон и кликните Сортировать.

Рис. 2.4. Сортировка по убыванию разместит все записи с ошибкой #Н/Д вверху диапазона

Использование функций ЕСЛИОШИБКА, ЕСНД и ЕНД для исключения значений с #Н/Д. Проблема: в отличие от предыдущего примера, элементы с #Н/Д не приводят к потере данных и не требуют дополнения таблицы подстановки. Если не найдено значение, желательно иметь пустую ячейку или ноль.

Существует несколько способов решить эту проблему. Если вы используете Excel 2007 или более позднюю версию, воспользуйтесь функциями ЕСЛИОШИБКА() или ЕСНД(). Если у вас более ранняя версия Excel (или требуется совместимость с более ранней версией) подойдет связка функций ЕСЛИ() и ЕНД().

Если исходная формула =ВПР(A67;$F$2:$H$29;3;ЛОЖЬ), то замените ее на =ЕСЛИОШИБКА(ВПР(A67;$F$2:$H$29;3;ЛОЖЬ);0), или на =ЕСНД(ВПР(A67;$F$2:$H$29;3;ЛОЖЬ);0).

Функция ЕСЛИОШИБКА() появилась в Excel 2007, что стало значительным улучшение по сравнению с предыдущими методами. С ЕСЛИОШИБКА() Excel сначала вычисляет формулу в первом аргументе функции. В 99% случаев возвращается правильный ответ, и работа функции останавливается. В тех редких случаях, когда возвращается ошибка, Excel переходит к обработке второго аргумента функции ЕСЛИОШИБКА(). В нашем примере, просто возвращает ноль.

Если вы предпочитаете в случае ошибки получить пустую ячейку, используйте формулу =ЕСЛИОШИБКА(ВПР(A67;$F$2:$H$29;3;ЛОЖЬ); " " ), но помните, что ячейка не будет по-настоящему пустой. Она будет содержать пустую текстовую строку, что, например, выдаст ошибку при попытке суммировать ее с другими числовыми значениями.

В более ранних версиях Excel 2007 используйте формулу: =ЕСЛИ(ЕНД(ВПР(A67;$F$2:$H$29;3;ЛОЖЬ));0;ВПР(A67;$F$2:$H$29;3;ЛОЖЬ)).

Это ужасная формула, чтобы вычислить ее, нужно вдвое больше времени. Для каждой ячейки Excel сначала вычислит ВПР. Если результат – ошибка #Н/Д, то подставится ноль. Если результат – не ошибка, Excel повторно вычислит ВПР. С тысячами ячеек, содержащими ВПР задержка по времени может быть заметной.

Удалите начальные и конечные пробелы. Проблема: ни одна из моих формул ВПР работает. Я четко вижу, что есть совпадение в таблице подстановки, но Excel не находит эти значения.

Когда вы сталкиваетесь с такой проблемой, скорее всего, почти все ячейки содержат #Н/Д, а не некоторые, как в предыдущем разделе. Проблема может заключаться в том, что, либо элементы в столбце A, либо в столбце F имеют пробелы. Это может произойти, если вы импортировали данные в Excel из 1С, SAP и т.п. Чтобы диагностировать эту проблему, выделите ячейку А2 и нажмите клавишу F2, чтобы перевести ячейку в режим редактирования. Мигающий курсор вставки появится в конце ячейки (рис. 2.5). Если курсор вставки появляется сразу после последнего символа, всё в порядке. Если, как на рис. 2.5, значит присутствуют лишние пробелы. Аналогичную диагностику можно провести и для ячеек в столбце F.

Рис. 2.5. Ни одна из ВПР не работает, хотя совпадения наблюдаются

Воспользуйтесь функцией СЖПРОБЕЛЫ() для удаления начальных и конечных пробелов. Если есть пробелы между словами, функция изменит несколько пробелов подряд на один. Таким образом, формулу =ВПР(A3;$F$3:$G$30;2;ЛОЖЬ) замените на =ВПР(СЖПРОБЕЛЫ(A3);$F$3:$G$30;2;ЛОЖЬ).

Если лишние пробелы отображаются в таблице подстановки, вы можете воспользоваться формулой массива. Наберите с клавиатуры =ВПР(A3;СЖПРОБЕЛЫ($F$3:$G$30);2;ЛОЖЬ). Но не жмите Enter после редактирования. Вместо этого, нажмите одновременно Ctrl + Shift + Enter (подробнее см. Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel). Ваша формула будет выглядеть так <=ВПР(A2;СЖПРОБЕЛЫ($F$3:$G$30);2;ЛОЖЬ)>. Появятся фигурные скобки.

Подводный камень: формулы массива работают медленнее обычных формул. Это не проблема, если вы хотите произвести впечатление на коллег. Но в реальной жизни, лучше временно создать столбец, например, Н, в который ввести формулу =СЖПРОБЕЛЫ(F3). Затем скопировать его и вставить как значения в столбец F.

Числа и текст не распознаются как одинаковые. Проблема: часть значений ВПР работают корректно, а часть – возвращают ошибку (рис. 2.6).

Рис. 2.6. Часть значений ВПР возвращают ошибку

Вероятно, некоторые ячейки содержат числовые значения, а некоторые – числа, сохраненные как текст. На приведенном выше рисунке вы можете увидеть крошечный зеленый треугольник в левом верхнем углу ячеек В8, В11, В13. Если вы наведете курсор на ячейку, содержащую треугольник, слева появится символ с восклицательным знаком. Если раскрыть список, вы увидите, сообщение Число, сохраненное как текст (рис. 2.7).

Рис. 2.7. Сообщение: Число, сохраненное как текст

То, что числа хранятся в виде текста не обязательно плохо. Например, если это – модели продукта. Проблема в другом: формат данных в столбцах В и F – разный. Когда значение в B3 хранится в виде числа, а в F2 – в виде текста (см. рис. 2.6), ВПР не находит соответствия. Существуют функции, которые позволяют изменять формат данных. Однако, прежде чем их применить, нужно определить, в каком месте данные хранятся в виде чисел, а в каком – в виде текста. Для этого на свободном месте листа введите формулу =ЕЧИСЛО(В2) и =ЕЧИСЛО(F3). Значение ИСТИНА соответствует числу, ЛОЖЬ – тексту.

Если в столбце В хранится текст, а в столбце F – числа, используйте функцию ЗНАЧЕН, которая позволяет изменить текст на число. К сожалению, она выдаст ошибку, если наименование продукта буквенно-цифровое, например, 1120TS. Поэтому, нужно использовать конструкцию ЕСЛИОШИБКА вместе со ЗНАЧЕН (рис. 2.8).

Рис. 2.8. Преобразования текстовых значений в числовые

Если ситуация обратная, то можно использовать функцию ТЕСКТ. Буквенно-цифровые значения при этом не изменятся (рис. 2.9). Второй аргумент функции ТЕКСТ ( " 0 " ) задает формат числа, на основании которого произойдет конвертация в текст (подробнее см. Пользовательский формат числа в Excel).

Рис. 2.9. Преобразования числовых значений в текстовые

Что делать, если проблема не укладывается ни в один из шаблонов? Т.е., когда, и столбец B, и столбец F, содержат комбинацию чисел и текста (рис. 2.10).

Рис. 2.10. Произвольное сочетание числовых и текстовых форматов в столбцах В и F

Если это произойдет, вы можете конвертировать числа, представленные текстом, в обоих столбцах. Выполните следующее:

1. Выделите ячейку В2.
2. Нажмите Ctrl+Shift+Стрелка вниз. Выделится весь столбец В с данными.
3. Наберите Alt+Ы, не отпуская Alt, продолжайте набирать П, и наконец Г (или кликните на кнопке Готово). Это позволит преобразовать текстовые значения во всем столбце в числа.
4. Повторите пункты 1–3 для столбца F.

Примечание: сочетание клавиш Alt+Ы+П+Г – это быстрый способ выбрать все настройки по умолчанию в Мастере распределения текста по столбцам (вкладка ДАННЫЕ, область Работа с данными). Использование Мастера позволяет преобразовать все данные обратно в общий формат, что, как правило, решает проблему.

Альтернативные стратегии. Существуют и иные способы преобразования текста в числа. Самый простой способ собрать все текстовые ячейки рядом – отсортировать данные по столбцу B по убыванию. Ячейки с текстом попадут в начало списка. Если текстовых ячеек немного, их можно обработать вручную.

В Excel 2002 и более поздних версиях, вы можете преобразовать непрерывный диапазон ячеек из текста в числа. Чтобы сделать это, вы используйте знак ошибки (восклицательный знак). Раскройте список и выберите Преобразовать в число (см. рис. 2.7). Этот метод работает только если верхняя ячейка вашего диапазона содержит число, сохраненное как текст.

Контроль дубликатов при использовании ВПР. Проблема: у меня есть первоначальная таблица, и данные о продажах за 19.06.14. Я использовал функцию ВПР, чтобы извлечь продажи из списка, и отразить их в первоначальной таблице. Затем я получил файл с продажами за 20.06.14. Я воспользовался функцией ПОИСКПОЗ и нашел одного нового клиента в списке продаж 20.06.14 – Sun Life Fincl.

Рис. 2.11. Действительно ли это новый клиент?

Этого клиента новым не назовешь. Кто-то в департаменте закупок создал его вместо существующего клиента по имени Sun Life Financial. Чтобы исправить этот дубль, надо заменить в таблице Sun Life Fincl. на Sun Life Financial. Это устранит ошибку #Н/Д. Однако, после этого исправления у вас появилось две строки, которые соответствуют Sun Life Financial.

ВПР не способна обрабатывать ситуации, подобные описанной. При наличии двух строк в таблице подстановки ВПР вернет продажи из первой попавшейся. Вы получите $3541, но не $2815.

Рис. 2.12. ВПР возвращает первое найденное значение

Если у вас нет полной уверенности, что клиенты в таблице подстановки уникальны, вы не должны использовать ВПР. Вместо этого используйте функцию СУММЕСЛИ. На рис. 2.13 использована следующая формула: =СУММЕСЛИ($A$2:$A$10;E2;$C$2:$C$10). Эта формула предписывает выполнять сканирование по столбцу Клиент (диапазон А2:А10), чтобы найти клиента, соответствующего значению в ячейке Е2. Каждый раз, когда в Excel находит соответствие Выручка из диапазона С2:С10 добавляется к значению в ячейке F2.

Рис. 2.13. Используйте СУММЕСЛИ вместо ВПР для таблиц подстановки, содержащих дубли

Параметр Искомое_значение должен содержать не более 255 символов. Проблема: ни одна из моих ВПР не работает. Я получаю ошибку #ЗНАЧ!

Чтобы выяснить, сколько знаков в ячейке A2, перейдите в любую пустую ячейку и введите =ДЛСТР(А2). ВПР не будет работать, если символов >255. В определенных обстоятельствах вы могли бы усечь содержимое ячейки с помощью формулы =ЛЕВСИМВ(A2;255). Но, иногда это не удастся, если ключи не уникальны при усечении до 255 символов.

Таблица подстановки имеет неверную ориентацию. Проблема: таблица подстановки расположена по горизонтали (рис. 2.14). Можно ли использовать ВПР?

Рис. 2.14. Таблица подстановки расположена по горизонтали

Стратегия: буква “В” в ВПР обозначает вертикальный просмотр. В Excel также есть функция ГПР для горизонтальных таблиц подстановки. Если вы хотите почудачить, то могли бы использовать формулу =ГПР(B3,$F$2:$Q$3,2,ЛОЖЬ).

Альтернативная стратегия: вы, скорее всего, поступите также, как и большинство других пользователей. Скопируете диапазон F2:В3, выделите ячейку F5, выполните специальную вставку, задав параметр Транспонировать. Это предаст таблице подстановки вертикальное положение. Затем вы введете обычную формулу с ВПР. Заметьте, что, начиная с версии 2010 в Excel для транспонирования используется специальный значок: