Не работает функция впр в excel: 6 причин, почему функция ВПР не работает

13 Excel-граблей, на которые может наступить контекстник

  • #ИМЯ? При работе с ключевыми словами
  • Безжалостная протяжка
  • Числа не работают
  • Буквы сломались
  • Всё в одном столбце
  • Ячейки рвутся
  • Формулы не работают, да много чего еще тоже не работает
  • Копируешь что-то, а ничего не вставляется, либо ошибка
  • Фильтры и вставки
  • Не работает ВПР
  • Числа превращаются в даты
  • Ячейки прыгают
  • Условное форматирование работает некорректно

1. #ИМЯ? При работе с ключевыми словами

Некоторые ключевые слова, собранные через wordstat, могут содержать плюсы перед стоп-словами. Если стоп-слово идет первым в фразе, то в самом начале окажется плюс. При вставке таких запросов в Excel может возникнуть ошибка #ИМЯ?.

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

Решение

Можно сделать массовую замену, заменив “=+” на “‘+” (равно с плюсом на одиночный апостроф с плюсом).

Также можно предварительно изменить формат ячеек на текстовый и после этого вставлять запросы.

2. Безжалостная протяжка

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

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

И если с текстами вы еще можете заметить ошибку, то с какими-нибудь ссылками или метками эта история может оказаться скрыта.

Решение

Не протягивайте, копируйте.

3. Числа не работают

Иногда числа при вставке могут восприниматься как текст (из-за пробела между разрядами) и никакие арифметические формулы не будут работать.

Решение

Сделайте массовую замену, заменив пробел (либо символ между разрядами) на пустоту.

4. Буквы сломались

Открываете файл, а там абракадабра типа этой:

Вероятно, проблема с кодировкой файла. Но нормальные буквы чаще всего возможно увидеть.

Решение

Попробуйте открыть файл блокнотом или другим редактором, а потом скопируйте содержимое в эксель.

Символы могут распознаться, если повезет.

5. Всё в одном столбце

При открытии csv и подобных файлов с разделителями вы можете получить все данные в одном столбце:

Конечно с этим неудобно работать.

Решение

Воспользуйтесь функцией «Текст по столбцам». Выделите диапазон, в меню данных выберете эту функцию.

Укажите формат данных с разделителями, затем определитесь с разделителем (чаще всего запятая) и нажмите «Готово». Данные должны разбежаться по разным столбцам.

6. Ячейки рвутся

У функции «Текст по столбцам» могут быть последствия. После применения действий, описанных выше, к любой вставке в книгу извне будет применяться «Текст по столбцам».

Например:

Решение

Надо снова зайти в меню «Текст по столбцам» и снять галку с разделителя, который был выбран ранее, после этого при вставке проблем не будет.

7. Формулы не работают, да много чего еще тоже не работает

Иногда вы начинаете пользоваться какими-то формулами, и начинают вылетать ошибки вычислений, нередко дело может быть в том, что у вас разные/ не подходящие форматы ячеек. Также может быть ситуация, когда вы пишите корректную формулу, но в ячейке ничего не считается, а так и продолжает отображаться формула.

Решение

Приведите ячейки к корректным форматам, а чтобы где-то формула перестала восприниматься текстом, выберите формат общий и еще раз отредактируйте формулу, просто войдя в режим редактирования, править ничего не надо.

8. Копируешь что-то, а ничего не вставляется, либо ошибка

Иногда вы пользуетесь формулами в Excel и получаете значения, например строка итогов в какой-нибудь таблице.

Потом вы копируете эти данные в новое место, но вставляются уже другие значения:

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

Решение

Выполняйте не просто вставку, а вставку «как значение»:

9. Фильтры и вставки

Не пытайтесь что-то вставлять рядом с активным фильтром, что-то точно вставится некорректно. Пользуйтесь новым листом или делайте вставку в те строки, где действие фильтра вас не достанет.

10. Не работает ВПР

В Excel есть очень удобная функция для поиска соответствий ВПР. Иногда она может не найти соответствия:

Решение

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

Для проверки можете сделать простое сравнение ячеек, в которых вы уверены. Ну а дальше с помощью массовой замены удалите лишние символы, или с помощью формул избавьтесь от лишних пробелов.

Нередко такая ситуация бывает, когда в списке фраз с минус-словами убирают минус-слова с помощью функции «Текст по столбцам», а потом пытаются сопоставить фразы с другими. Так вот, после разбивки по столбцам у исходной фразы, в конце остается лишний незаметный пробел.

11. Числа превращаются в даты

Работая с числами, определитесь, какой у вас разделитель для дробной части — точка или запятая. Если в вашей системе вы работаете с запятой и откроете файл, где разделитель точка, такие числа могут превратиться в даты.

Решение

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

Другой же вариант, при открытии файла, если появится меню открытия (Мастер текстов), выберите подходящий тип разделителя на 3 шаге:

Если при первом открытии файла подобное меню не появилось, то закройте файл, и снова откройте его через Excel.

12. Ячейки прыгают

Бывает так: работаешь, работаешь, кликаешь на ячейку для редактирования, но твой клик попадает не на ячейку, а на полоску между ячейками. Вот на неё:

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

Решение

Кликайте на сами ячейки, а не между ними.

13. Условное форматирование работает некорректно

Настроив условное форматирование для ячейки вы замечаете, что оно срабатывает не тогда, когда нужно. Вероятно, к ячейкам применено слишком много правил.

Решение

Проверяйте сколько правил уже применено в меню Условное форматирование -> Управление правилами.

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

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

Удачи!

Как исправить ошибку #Н/Д в функции ВПР

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

Совет: Также см. краткий справочник: советы по устранению неполадок с функцией ВПР, в котором представлены распространенные причины проблем #NA в удобном PDF-файле. Вы можете поделиться PDF-файлом с другими или распечатать его для собственного ознакомления.

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

table_array

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

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

Возникает ошибка #N/A, поскольку во втором столбце (Produce) аргумента A2:C10 table_array появляется значение поиска «Kale» . В этом случае Excel ищет его в столбце A, а не в столбце B.

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

Рассмотрите возможность использования ИНДЕКС/ПОИСКПОЗ вместо

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

Существует несколько преимуществ использования ИНДЕКС/ПОИСКПОЗ вместо ВПР:

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

  • С помощью ИНДЕКС и ПОИСКПОЗ можно указать либо строку, либо столбец в массиве, либо указать и то, и другое. Это означает, что вы можете искать значения как по вертикали, так и по горизонтали.

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

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

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

Синтаксис

Чтобы построить синтаксис для ИНДЕКС/ПОИСКПОЗ, вам нужно использовать аргумент массива/ссылки из функции ИНДЕКС и вложить в него синтаксис ПОИСКПОЗ. Это принимает форму:

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

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

=ИНДЕКС(C2:C10,ПОИСКПОЗ(B13,B2:B10,0))

На простом английском это означает:

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

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

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

Если аргумент range_lookup имеет значение ЛОЖЬ, а функция ВПР не может найти точное совпадение в ваших данных, возвращается ошибка #Н/Д.

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

Также рассмотрите возможность использования функции CLEAN или TRIM для очистки данных в ячейках.

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

Если для аргумента range_lookup установлено значение TRUE, а значение поиска меньше наименьшего значения в массиве, вы увидите ошибку #Н/Д. TRUE ищет приблизительное совпадение в массиве и возвращает ближайшее значение, меньшее, чем искомое значение.

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

Решение :

  • При необходимости исправьте значение поиска.

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

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

Если для аргумента range_lookup установлено значение TRUE, а один из столбцов подстановки не отсортирован в порядке возрастания (A-Z), вы увидите ошибку #N/A.

Решение :

  • Измените функцию ВПР для поиска точного совпадения. Для этого установите range_lookup  аргумент для FALSE . Для FALSE сортировка не требуется.

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

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

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

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

Нужна дополнительная помощь?

Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществе ответов.

См. также

  • Исправьте ошибку #Н/Д

  • org/ListItem»>

    ВПР: больше нет #NA

  • Арифметика с плавающей запятой может давать неточные результаты в Excel

  • Краткая справочная карта: освежение VLOOKUP

  • Функция ВПР

  • Обзор формул в Excel

Читайте также: