Ошибка н д в excel при впр

Ошибки #ЗНАЧ и #Н/Д в функции ВПР() Excel и как сними бороться.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник: ruexcel.ru

Исправление ошибки #Н/Д в функции ВПР

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

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

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

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

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

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

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

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

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

Вместо функции ВПР есть несколько преимуществ использования функций индекс и соответствие.

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

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

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

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

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

Синтаксис

Для создания синтаксиса для функции индекс/MATCH необходимо использовать аргумент массив/ссылка из функцией индекс и вложить в него синтаксис MATCH. Рассмотрим следующую форму:

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

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

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

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

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

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

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

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

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

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

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

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

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

Читайте также:  Как в excel убрать пробелы

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

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

Измените функцию ВПР так, чтобы искать точное совпадение. Для этого укажите для аргумента диапазон_поиска значение ЛОЖЬ. Для “ложь” не требуется сортировка.

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

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

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

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

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

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

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

Источник: support.office.com

ВПР не работает

Как работает ВПР в Excel читайте в посте “Функция ВПР – чудо экономии времени”. Здесь же вы узнаете, что делать, если ВПР не работает. Так бывает. Скорее всего вы допустили ошибку. Есть четыре наиболее распространенных варианта ошибок:

  1. Ошибка Н/Д в каждой ячейке.
  2. Ошибка Н/Д преимущественно в нижней части таблицы.
  3. Ошибка Н/Д в отдельных ячейках.
  4. Ошибка #ССЫЛКА.

А теперь разберем каждый вариант подробнее.

Вариант 1. ВПР вернула ошибку #Н/Д во всех ячейках

  • Вы неправильно выделили диапазон поиска – начали не с того столбца:

Все будет в порядке, как только вы исправите ошибку:

=впр(B3;'[прайс-лист.xlsx]Лист1′! $C$3:$E$7 ;3;ложь)

  • Не совпадает текст, по которому ВПР ищет данные.

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

Единственное исключение – допускаются разные регистры. Например, “Компьютер AF” и “компьютер af” не составит проблемы для ВПР.

Идентичность текста удобно проверять функцией =ЯЧЕЙКА=ЯЧЕЙКА. Смотрите пример на рисунке ниже и описание под ним:

  1. Введите в ячейку D2 знак “=”
  2. Выделите ячейку B2 с первым значением Планшет DC.
  3. Опять введите “=”.
  4. Выделите ячейку C2 со вторым значением.
  5. Кликните Enter и протяните формулу вниз.

В результате в ячейках отобразится либо “ИСТИНА” (значения совпадают), либо “ЛОЖЬ” (значения не совпадают).

Как видите, не совпадают значения в строке 4: “Компьютер А” и “Компьютер F” (в ячейке D4 видим слово “ЛОЖЬ”).

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

Что можно предпринять, если текст не совпадает?

  1. Если вы регулярно работаете с этими файлами, поменяйте текст вручную. Единожды потратив на это время, вы значительно сэкономите его в будущем. Если смена текста невозможна, создайте дополнительный столбец, куда введете текст из второго файла.
  2. Иногда текст не совпадает из-за каких-то наборов знаков, которые повторяются в каждой ячейке. К примеру, в одном из файлов везде добавлен артикул: “010-01583 Компьютер AF”, а в другом файле этого артикула нет “Компьютер AF”. В этом случае текст можно заменить автоматически. Об этом читайте в следующих постах.
  • В одном из файлов в тексте содержатся невидимые пробелы. Это хитрая разновидность несовпадения текста. Хитрая, потому что не всегда ее можно заметить. Вот полюбуйтесь:

Как быть? Воспользуйтесь формулой =СЖПРОБЕЛЫ(ЯЧЕЙКА). Эта формула убирает все лишние пробелы. Подробнее – в посте о работе с текстом.

  • В одном файле числа сохранены в виде текста, в другом – в виде чисел:

Для устранения проблемы сделайте следующее:

  1. Выделите весь столбец с числами в виде текста (на рисунке выше диапазон B2:В5).
  2. Подведите курсор к значку “!” и кликните по появившейся стрелочке.
  3. Выберите из выпадающего списка “Преобразовать в число” – см. рисунок ниже.

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

  • Смещение диапазона. Чаще всего это происходит, если данные подтягиваются из одного и того же файла. Например, из Листа 1 на Лист 2 или даже из одного и того же листа. Что происходит? Мы тянем формулу вниз, а вместе с ней тянется и диапазон. Вот так:

Как исправить? Надо закрепить диапазон:

  1. Кликните дважды по ячейке с формулой (С5) – отобразится формула.
  2. В отобразившейся формуле поместите курсор на В12 и нажмите клавишу F4 на клавиатуре.
  3. Переместите курсор на С14 в этой же формуле и опять нажмите F4.
  4. Кликните Enter и формула преобразится, из а) в б)

а) =ВПР(В5; В12:С14 ;2;ложь)

б) =ВПР(В5; $B$12:$С$14 ;2;ложь)

Значок “$” указывает на то, что диапазон закреплен. Теперь при протягивании формулы вниз, он не будет смещаться.

Вариант 3. ВПР подтягивает только часть значений, остальные – #Н/Д

  • В некоторых ячейках текст не совпадает на 100%.

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

Вариант 4. ВПР возвращает ошибку #ССЫЛКА

  • Вы неверно указали номер столбца. ВПР всегда считает столбцы, начиная с первого столбца выделенного диапазона. Поэтому на рисунке ниже красные номера неверные, зеленые – правильные:

И еще несколько советов по функции ВПР в Excel:

  1. При вводе формулы используете точку с запятой, а не запятые.
  2. Если протягиваете формулу вправо, не забывайте закреплять ссылку в начале формулы, чтобы формула имела вид: =ВПР( $В5 ;$B$12:$С$14;2;ложь). Для этого наведите курсор на В5 (прямо в формуле) и трижды нажмите клавишу F4 на клавиатуре.
  3. Не используйте аргумент “ИСТИНА” в ВПР, если работаете с текстом. Это приблизительный поиск, в котором будет много ошибок.

9 комментариев к “ ВПР не работает ”

Очень хорошая статья! Без воды и написано именно то, что надо по сути
Но, чтобы статья стала почти исчерпывающей я бы добавил, что иногда ВПР может возвращать ошибку, которая может быть не замечена пользователем, так как не появляется #Н/Д или #ССЫЛКА, однако, при этом функция ВПР() работает некорректно, так как результат ошибочный.
Это происходит в тех случаях, когда в формуле последний аргумент не ЛОЖЬ, а ИСТИНА (или вообще не приведен, а это приравнивается к тому, как если бы была указана ИСТИНА) и при этом первый столбец не отсортирован. Формула вида =ВПР(В5;$B$12:$С$14;2) корректно работать то будет.

Спасибо за комментарий! Да, конечно, это все совершенно верно для ВПР с аргументом ИСТИНА (или без него). Но поскольку статья является фактически продолжением статьи, в которой рассматривалась исключительно ВПР с аргументом ЛОЖЬ, то данная ошибка не приведена.

Спасибо.
Доходчиво и быстро применимо. решил вопрос!

Источник: supermanager.pp.ua

Почему не работает формула ВПР (VLOOKUP) в Excel — Решение

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

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

Столбец поиска искомого значения не является крайним левым.

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

Обратите внимание, что в данном примере таблица Прайс лист отличается. Названия товаров теперь находятся не в первом столбце, а в третьем, после цены товара.

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

Решение

1. Если есть возможность, то перенесите столбец, где ищется искомое значение в крайнее левое положение (либо просто левее столбца из которого необходимо возвращать значение, в нашем примере относительно столбца с ценой) как указано на рисунке выше.

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

Выделяем первый столбец прайс листа, выбираем «Главная», «Ячейка», «Вставить», «Вставить столбцы на лист». Либо используйте пустой столбец, если он у вас уже есть.
Встаем в первую ячейку вспомогательного столбца и просто делаем ссылку на нужный нам столбец и протягиваем формулу вниз.

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

Читайте также:  Как в excel делать впр в excel

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

Функция ВПР возвращает ошибку #Н/Д (#N/A)

#Н/Д (#N/A) означает «Нет данных» («not available»). При протягивании формулы ВПР у вас появляется #Н/Д. Вопрос даже не в том, что ВПР не работает, а почему появляются данные значения.

Решение

1. Не закреплен диапазон таблицы

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

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

На рисунке выше есть #Н/Д два раза. Один раз напротив товара «Савок» и другой напротив слова «Стол».

В первом случае понятно — товара «Савок» у нас нет в прайс листе, отсюда и ошибка.

Во втором случае непонятно, товар «Стол» у нас есть, но все равно Н/Д#. Как правило ошибка заключается в написании слов и форматах либо в таблице заказов либо на странице Прайс лист.

В нашем случае у нас после слова «Стол » стоит пробел, поэтому данное слово не находится в прайс листе. Для начала проверьте содержимое ячеек на невидимые пробелы. Особенно часто они встречаются в конце строки. Кликните на ячейку и переместитесь в конец формулы. Удалите лишние пробелы, если они есть.

В данном примере можно так же использовать функцию СЖПРОБЕЛЫ

=ВПР( СЖПРОБЕЛЫ(A3) ;$F$2:$I$16;3;0)

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

В нашем случаем мы ищем текстовые значения — «Наименование товара», иногда поиск идет по числовым значениям. В данном случае #Н/Д может появляться, когда искомое значение, например в формате числа, а в таблице в виде текста. Для исправления ошибку потребуется перевести формат текста в числа или наоборот, то есть сделать одинаковый формат.

Еще более редкий случай, это когда в слове может быть вместо русских слов латинские и наоборот. Например A и А это разные буквы разных алфавитов, поэтому и слова будут разными для ВПР. Чтобы проверить, просто сделайте в отдельном столбце ссылку одной ячейки на другую =A4=F6 в нашем примере эта формула вернет «Ложь» так как эти ячейки не равны (в одном слове есть лишний пробел), аналогичная ситуация будет если вместо русской буквы «С» будет английская буква «C».

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

Функция ВПР возвращает ошибку #ЗНАЧ! (#VALUE!)

Обычно Excel сообщает об ошибке #VALUE! (#ЗНАЧ!), когда значение, использованное в формуле, не подходит по типу данных. Если брать функцию ВПР, то обычно стоит рассмотреть две основные причины ошибки #ЗНАЧ!.

1. Искомое значение больше 255 символов.

В функции ВПР есть ограничение на длину искомого значения – оно не должно быть более 255 символов. Если вы столкнулись с такой проблемой, то рекомендуем использовать для этих целей все ту же связку ИНДЕКС+ПОИСКПОЗ (INDEX+MATCH).

2. Неправильно указан полный путь на другую книгу.

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

3. Аргумент номер_столбца в функции ВПР меньше 1

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

Другие важные особенности работы функции ВПР

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

1. ВПР не чувствительна к регистру

Функция ВПР не чувствительна к регистру и для нее все символы нижнего и верхнего регистра будут одинаковые. То есть, слово «Стол», «СТОЛ» и «стол» для функции ВПР будут одинаковыми.

Если вам необходимо учитывать регистр при использовании ВПР, то используйте другую функцию Excel, наприме (ПРОСМОТР, СУММПРОИЗВ, ИНДЕКС и ПОИСКПОЗ) в сочетании с СОВПАД, которая различает регистр и возвращается ИСТИНУ или ЛОЖЬ при совпадении или не совпадении с искомым значением.

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

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

3. Работа функции ВПР и добавлении или удалении столбца

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

Если кто-то добавит перед столбцом с ценой какую-нибудь информацию, например об остатках товара, то теперь в третьем столбце будет остаток товаров, а цена будет в 4-м столбце, но формуле это автоматически не поменяется, мы должны помнить об этом и менять информацию.

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

Источник: sirexcel.ru

Ошибки Excel 2007

Автор: Индык Игорь Викторович
e-mail: exelentc@yandex.ru

Я ввожу формулу, но вместо результата я вижу текст #Н/Д или #####. Что это значит?

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

Ошибка #####

Чаще всего эта ошибка означает, что столбец недостаточно широк для отображения содержимого ячейки. Для решения проблемы достаточно увеличить ширину столбца.

Другая причина ошибки – в ячейке содержатся дата или время с отрицательными значениями.

Ошибка #Н/Д

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

  • В функции не указаны один или несколько обязательных аргументов.
  • В функциях ВПР, ГПР, ПРОСМОТР или ПОИСКПОЗ указано неправильное значение аргумента искомое_значение.
  • Функции ВПР, ГПР или ПОИСКПОЗ применяются для поиска в несортированной таблице.
  • В исходной таблице вместо пропущенных данных было введено значение #Н/Д или НД().

Ошибка #ДЕЛ/0!

Ошибка #ДЕЛ/0! Означает, что в процессе вычисления происходит деление на ноль. Причины могут быть следующие:

  • В формуле явно задано деление на ноль (например, =10/0).
  • В формуле или функции в качестве делителя стоит ссылка на пустую ячейку или на ячейку, которая содержит ноль.

Ошибка #ПУСТО!

Эта ошибка возникает, когда в условиях функции указано два пересекающихся диапазона, но на самом деле они не пересекаются.

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

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

Ошибка #ЧИСЛО!

Данная ошибка возникает, если в формуле или функции указаны некорректные числовые значения. Например:

  • В функции, которая использует числовой аргумент, в его качестве указан текст или ссылка на ячейку с нечисловым значением.
  • Функция, которая выполняет итерации (вычисление, которое повторяется до тех пор, пока не будет выполнено заданное условие, например ВСД или СТАВКА) не может вычислить результат (например, заданное условие не наступило).
  • Результатом вычисления стало число, слишком большое или слишком малое для использования в Excel. Напоминаем, что максимальное число в Excel не может превышать 10307 и быть меньше минус 10307.

Ошибка #ССЫЛКА!

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

Ошибка #ЗНАЧ!

Такая ошибка возникает при использовании неверного типа аргумента или операнда. Например, пользователь может ошибиться в следующем:

  • В формулу вместо числа или логического значения (ИСТИНА или ЛОЖЬ) введен текст.
  • После ввода или редактирования формулы массива нажимается клавиша ВВОД вместо комбинации CTRL+SHIFT+ВВОД.
  • Ссылка, формула или функция указана как массив.
  • В качестве условия функции, который требует единичное значение, указан диапазон.
Читайте также:  В excel номер страницы

Я понял, что означает ошибка. Но моя формула очень большая/работает с большим объемом данных. Как мне найти место, где допущена ошибка?

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

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

У меня ошибка #ИМЯ? и я не могу найти её причину.

  • Проверьте правильность написания имени (названия) функции. Для этого можно вызвать Мастер функций (группа Библиотека функций вкладки Формулы), и выбрать нужную функцию из предлагаемого списка.
  • В формулу был введен текст, не заключенный в двойные кавычки. Допустим, результатом формулы должен был быть текст с числом – например, Итого 200 р. Если при введении текста Итого в формулу этот текст не заключен в двойные кавычки, Excel интерпретирует такие данные как имя, что вызывает ошибку.

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

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

У меня огромная база данных клиентов – покупателей. Каждый день в базе данных добавляется столбец, в котором указывается сумма выручки от каждого покупателя. Если покупатель ничего не купил, в ячейке ставится #Н/Д. Как мне просуммировать столбец, если там есть такие данные?

Самый простой способ решения такой проблемы – изменить принцип наполнения базы данных и не заполнять ячейки для покупателей, которые не производили покупки.

Но если основную таблицу изменить невозможно (по разным причинам), эту проблему можно попробовать решить с помощью функции БДСУММ.

Допустим, исходная таблица имеет следующий вид:

Применив функцию БДСУММ, мы сможем выбрать для суммирования те ячейки, которые не содержат значения #Н/Д. Функция БДСУММ имеет следующий синтаксис:

  • база_данных – это диапазон ячеек, с которыми будут проводиться действия.
  • поле – это столбец, значения из которого будут суммироваться.
  • условия – это набор условий, которые должны выполняться для значения ячейки, чтобы это значение суммировалось.

То есть, в функции нам нужно задать суммирование первого столбца нашей таблицы, при условии, что значение ячейки этого столбца не равно #Н/Д. Для этого сначала создадим условие – в любом удобном месте рабочего листа напишем условие <>#Н/Д и озаглавим эту ячейку так же как и столбец нужной нам базы данных.

Далее мы вводим следующую формулу:

  • C2:C18 – наша таблица,
  • E2:E3 – наше условие.

Обратите внимание, что диапазоны базы данных, и условий обязательно должны содержать заголовки столбцов! При этом заголовок условия должен совпадать с заголовком столбца, над которым производятся вычисления!

Наш результат указан под таблицей:

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

Этот способ также может использоваться для функций СЧЁТ, БСЧЁТА, ДМАКС, ДМИН, БДПРОИЗВЕД и им подобных.

В начало страницы

В начало страницы

Источник: on-line-teaching.com

Ошибки в Excel

Если Excel не может правильно оценить формулу или функцию рабочего листа; он отобразит значение ошибки – например, #ИМЯ?, #ЧИСЛО!, #ЗНАЧ!, #Н/Д, #ПУСТО!, #ССЫЛКА! – в ячейке, где находится формула. Разберем типы ошибок в Excel, их возможные причины, и как их устранить.

Ошибка #ИМЯ?

Ошибка #ИМЯ появляется, когда имя, которое используется в формуле, было удалено или не было ранее определено.

Причины возникновения ошибки #ИМЯ?:

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

Ошибки в Excel – Использование имени в формуле

Устранение ошибки: определите имя. Как это сделать описано в этой статье.

  1. Ошибка в написании имени функции:

Ошибки в Excel – Ошибка в написании функции ПОИСКПОЗ

Устранение ошибки: проверьте правильность написания функции.

  1. В ссылке на диапазон ячеек пропущен знак двоеточия (:).

Ошибки в Excel – Ошибка в написании диапазона ячеек

Устранение ошибки: исправьте формулу. В вышеприведенном примере это =СУММ(A1:A3).

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

Ошибки в Excel – Ошибка в объединении текста с числом

Устранение ошибки: заключите текст формулы в двойные кавычки.

Ошибки в Excel – Правильное объединение текста

Ошибка #ЧИСЛО!

Ошибка #ЧИСЛО! в Excel выводится, если в формуле содержится некорректное число. Например:

  1. Используете отрицательное число, когда требуется положительное значение.

Ошибки в Excel – Ошибка в формуле, отрицательное значение аргумента в функции КОРЕНЬ

Устранение ошибки: проверьте корректность введенных аргументов в функции.

  1. Формула возвращает число, которое слишком велико или слишком мало, чтобы его можно было представить в Excel.

Ошибки в Excel – Ошибка в формуле из-за слишком большого значения

Устранение ошибки: откорректируйте формулу так, чтобы в результате получалось число в доступном диапазоне Excel.

Ошибка #ЗНАЧ!

Данная ошибка Excel возникает в том случае, когда в формуле введён аргумент недопустимого значения.

Причины ошибки #ЗНАЧ!:

  1. Формула содержит пробелы, символы или текст, но в ней должно быть число. Например:

Ошибки в Excel – Суммирование числовых и текстовых значений

Устранение ошибки: проверьте правильно ли заданы типы аргументов в формуле.

  1. В аргументе функции введен диапазон, а функция предполагается ввод одного значения.

Ошибки в Excel – В функции ВПР в качестве аргумента используется диапазон, вместо одного значения

Устранение ошибки: укажите в функции правильные аргументы.

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

Устранение ошибки: для завершения ввода формулы используйте комбинацию клавиш Ctrl+Shift+Enter .

Ошибки в Excel – Использование формулы массива

Ошибка #ССЫЛКА

В случае если формула содержит ссылку на ячейку, которая не существует или удалена, то Excel выдает ошибку #ССЫЛКА.

Ошибки в Excel – Ошибка в формуле, из-за удаленного столбца А

Устранение ошибки: измените формулу.

Ошибка #ДЕЛ/0!

Данная ошибка Excel возникает при делении на ноль, то есть когда в качестве делителя используется ссылка на ячейку, которая содержит нулевое значение, или ссылка на пустую ячейку.

Ошибки в Excel – Ошибка #ДЕЛ/0!

Устранение ошибки: исправьте формулу.

Ошибка #Н/Д

Ошибка #Н/Д в Excel означает, что в формуле используется недоступное значение.

Причины ошибки #Н/Д:

  1. При использовании функции ВПР, ГПР, ПРОСМОТР, ПОИСКПОЗ используется неверный аргумент искомое_значение:

Ошибки в Excel – Искомого значения нет в просматриваемом массиве

Устранение ошибки: задайте правильный аргумент искомое значение.

  1. Ошибки в использовании функций ВПР или ГПР.

Устранение ошибки: см. раздел посвященный ошибкам функции ВПР

  1. Ошибки в работе с массивами: использование не соответствующих размеров диапазонов. Например, аргументы массива имеют меньший размер, чем результирующий массив:

Ошибки в Excel – Ошибки в формуле массива

Устранение ошибки: откорректируйте диапазон ссылок формулы с соответствием строк и столбцов или введите формулу массива в недостающие ячейки.

  1. В функции не заданы один или несколько обязательных аргументов.

Ошибки в Excel – Ошибки в формуле, нет обязательного аргумента

Устранение ошибки: введите все необходимые аргументы функции.

Ошибка #ПУСТО!

Ошибка #ПУСТО! в Excel возникает когда, в формуле используются непересекающиеся диапазоны.

Ошибки в Excel – Использование в формуле СУММ непересекающиеся диапазоны

Устранение ошибки: проверьте правильность написания формулы.

Ошибка ####

Причины возникновения ошибки

  1. Ширины столбца недостаточно, чтобы отобразить содержимое ячейки.

Ошибки в Excel – Увеличение ширины столбца для отображения значения в ячейке

Устранение ошибки: увеличение ширины столбца/столбцов.

  1. Ячейка содержит формулу, которая возвращает отрицательное значение при расчете даты или времени. Дата и время в Excel должны быть положительными значениями.

Ошибки в Excel – Разница дат и часов не должна быть отрицательной

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

Источник: naprimerax.org