Причины возникновения ошибки времени выполнения 9 Excel
Ошибка времени выполнения 9 Excel возникает, когда происходит попытка получить доступ к элементу коллекции, индекс которого выходит за пределы диапазона. Эта ошибка часто возникает при использовании макросов VBA в Excel.
Вот некоторые распространенные причины возникновения ошибки времени выполнения 9 Excel:
- Индекс элемента вышел за пределы размера коллекции. Например, попытка получить доступ к элементу массива или к ячейке, которая находится за пределами диапазона листа.
- Неправильное использование функции или метода, которые ожидают диапазон или массив в качестве аргумента, и этот аргумент имеет неправильный индекс.
- Ошибка в коде макроса, который приводит к неправильной работе с элементами коллекции.
- Неправильное использование переменной, которая содержит индекс элемента коллекции.
- Изменение размера коллекции без обновления соответствующих индексов.
Ошибки времени выполнения 9 можно обнаружить и устранить, используя отладчик VBA и просмотрев код макроса. При возникновении этой ошибки рекомендуется проверить индексы и диапазоны, используемые в коде, а также убедиться, что размер коллекции соответствует ожиданиям.
Хорошая практика — перед обращением к элементам коллекции, проверить ее длину и границы, чтобы избежать ошибок времени выполнения 9. Также рекомендуется использовать конструкции проверки ошибок и обработку исключений для более надежной работы с коллекциями в Excel.
Что значит ошибка перенос в Экселе?
Эта ошибка возникает, если диапазон для формулы с пролитым массивом не пуст. . Если формула выбрана, пунктирый обтекает диапазон, который предполагается пролить.
Что значит ошибка число в Экселе?
В Excel эта ошибка возникает тогда, когда формула или функция содержит недопустимое числовое значение. . В Excel ошибка #ЧИСЛО! также может возникать, если: в формуле используется функция, выполняющая итерацию, например ВСД или СТАВКА, которая не может найти результат.
Что означает перенос в Excel?
Чтобы транспонировать диапазон (обменять местами строки и столбцы) в Microsoft Excel всегда имелась встроенная функция ТРАНСП (TRANSPOSE).
Что означает если в ячейке Excel #знач?
Ошибка #ЗНАЧ! . в Excel означает: «Формула вводится неправильно. Или что-то не так с ячейками, на которые указывают ссылки».
Что означает ошибка Spill?
Эта ошибка возникает, если диапазон для формулы с пролитым массивом не пуст. При выборе формулы пунктирый обтекает предполагаемый диапазон отлива. . После очистки этой функции формула массива будет пролита по замечу.
Как перенести данные с одной страницы на другую в Excel?
Сначала мы выделяем имеющуюся таблицу, правой кнопкой вызываем меню и жмем КОПИРОВАТЬ. В свободной ячейке снова вызываем меню правой кнопкой и нажимаем СПЕЦИАЛЬНАЯ ВСТАВКА. Если мы оставим все как есть по умолчанию и просто нажмем ОК, то таблица вставится полностью, со всеми ее параметрами.
Как убрать ошибку число в Excel?
- в формуле используется функция, выполняющая итерацию, например ВСД или СТАВКА, которая не может найти результат. Чтобы исправить ошибку, измените число итераций формулы в Excel. .
- Результат формулы — число, слишком большое или слишком малое для отображения в Excel.
Почему в Excel пишет ссылка?
указывает на то, что формула ссылается на недопустимую ячейку. Чаще всего это происходит потому, что формула ссылается на ячейки, которые были удалены или заменены другими данными.
Почему в Excel пишет имя?
Важно: Ошибка #ИМЯ? означает, что нужно исправить синтаксис, поэтому если вы видите ее в формуле, устраните ее. Не скрывайте ее с помощью функций обработки ошибок, например функции ЕСЛИОШИБКА
Чтобы избежать опечаток в именах формулы, используйте мастер формул в Excel.
Как перенести текст в ячейке таблицы Excel в несколько строк?
- Установите курсор в то место строки, где должен быть перенос
- Переключитесь на английскую раскладку клавиатуры
- Нажмите Alt+Enter для вставки переноса
- Нажмите Enter для завершения редактирования
Как установить новый абзац в Excel?
- Как мы писали выше, нажмите Alt + Enter, эта универсальная комбинация вставляет абзац
- Зажмите Alt и нажмите 1 и, не отпуская Alt, сразу ноль. Эти действия вставляют символ под номером 10.
Когда появляется ошибка в Excel?
Если Excel не может правильно оценить формулу или функцию рабочего листа; он отобразит значение ошибки – например, #ИМЯ?, #ЧИСЛО!, #ЗНАЧ!, #Н/Д, #ПУСТО!, #ССЫЛКА! – в ячейке, где находится формула.
Что позволяет выполнять электронная таблица?
Электронная таблица (англ. spreadsheet)— компьютерная программа, позволяющая проводить вычисления с данными, представленными в виде двумерных массивов, имитирующих бумажные таблицы. . Электронные таблицы (ЭТ) представляют собой удобный инструмент для автоматизации вычислений.
Что означает сообщение об ошибке пусто в ячейке MS Excel?
#ПУСТО! — данная ошибка связана с диапазонами в формуле. Чаще всего она возникает, когда в формуле указывается два непересекающихся диапазона, например =СУММ(С4:С6;А1:С1) .
ЕСЛИ ОШИБКА ВПР Формула Да/Нет
В какой-то ситуации вы можете что-то искать, но вместо того, чтобы тянуть спички, просто хотите вернуться Да (или другой текст, если искомое значение найдено) и Нет (если искомое значение не найдено). Чтобы это сделать, вы можете использовать эту общую формулу:
ЕСЛИ(ОШИБКА(ВПР(…)), “text_if_not_found“, “text_if_found“)
Предположим, что в нашем образце данных вы хотите узнать, какие учащиеся провалили тест, а какие нет. Для этого подайте уже знакомую формулу ВПР ЕОШИБКА для логической проверки ЕСЛИ и скажите ей выводить «Нет», если значение не найдено (ВПР ЕОШИБКА возвращает ИСТИНА), и «Да», если найдено (ВПР ЕОШИБКА возвращает ЛОЖЬ):
=ЕСЛИ(ЕОШИБКА(ВПР(A3, $D$3:$E$9, 2, ЛОЖЬ)), “Нет”, “Да”)
Перенос данных таблицы через функцию ВПР
ВПР в Excel – очень полезная функция, позволяющая подтягивать данные из одной таблицы в другую по заданным критериям. Это достаточно «умная» команда, потому что принцип ее работы складывается из нескольких действий: сканирование выбранного массива, выбор нужной ячейки и перенос данных из нее.
Весь процесс просмотра и выборки данных происходит за доли секунды, поэтому результат мы получаем моментально.
Синтаксис ВПР
ВПР расшифровывается как вертикальный просмотр. То есть команда переносит данные из одного столбца в другой. Для работы со строками существует горизонтальный просмотр – ГПР.
Аргументы функции следующие:
- искомое значение – то, что предполагается искать в таблице;
- таблица – массив данных, из которых подтягиваются данные. Примечательно, таблица с данными должна находиться правее исходной таблицы, иначе функций ВПР не будет работать;
- номер столбца таблицы, из которой будут подтягиваться значения;
- интервальный просмотр – логический аргумент, определяющий точность или приблизительность значения (0 или 1).
Как перемещать данные с помощью ВПР?
Рассмотрим пример на практике. Имеем таблицу, в которой прописываются партии заказанных товаров (она подсвечена зеленым). Справа прайс, где значатся цены каждого товара (подсвечен голубым). Нам нужно перенести данные по ценам из правой таблицы в левую, чтобы подсчитать стоимость каждой партии. Вручную это делать долго, поэтому воспользуемся функцией Вертикального Просмотра.
В ячейку D3 нужно подтянуть цену гречки из правой таблицы. Пишем =ВПР и заполняем аргументы.
Искомым значением будет гречка из ячейки B3
Важно проставить именно номер ячейки, а не слово «гречка», чтобы потом можно было протянуть формулу вниз и автоматически получить остальные значения
Таблица – выделяем прайс без шапки. Т.е. только сами наименования товаров и их цены. Этот массив мы зафиксируем клавишей F4, чтобы он не изменялся при протягивании формулы.
Номер столбца – в нашем случае это цифра 2, потому что необходимые нам данные (цена) стоят во втором столбце выделенной таблицы (прайса).
Интервальный просмотр – ставим 0, т.к. нам нужны точные значения, а не приблизительные.
Видим, что из правой таблицы в левую подтянулась цена гречки.
Протягиваем формулу вниз и визуально проверяем некоторые товары, чтобы понять, что все сделали правильно.
Скачать пример переноса таблицы через ВПР
Вот так, с помощью элементарных действий можно подставлять значения из одной таблицы в другую
Важно помнить, что функция Вертикального Просмотра работает только если таблица, из которой подтягиваются данные, находится справа. В противном случае, нужно переместить ее или воспользоваться командой ИНДЕКС и ПОИСКПОЗ
Овладев этими двумя функциями вы сможете намного больше реализовать сложных решений чем те возможности, которые предоставляет функция ВПР или ГПР.
Ячейка Excel выдает ошибку #ЧИСЛО!
Если ваша формула содержит недопустимые числовые значения, появится ошибка #ЧИСЛО!. Это часто происходит, когда вы вводите числовое значение, которое отличается от других аргументов, используемых в формуле.
И еще, при вводе формулы, исключите такие значения, как $ 1000, в формате валюты. Вместо этого введите 1000, а затем отформатируйте ячейку с валютой и запятыми после вычисления формулы. Просто число, без знака $ (доллар).
Как устранить эту ошибку
Проверьте, не ввели ли вы какую-либо отформатированную валюту, дату или спецсимвол. Обязательно удалите их из формулы, сохранив только цифры.
Исправления, которые необходимо выполнить, если диапазон разлива не пустой
Если вы видите, что диапазон разлива не пустой, выполните следующие исправления.
Исправление 1. Удалите данные, блокирующие диапазон разлива.
Если в ячейках диапазона разлива уже есть данные, при применении формулы вы увидите ошибку #SPILL.
Когда вы можете четко видеть данные, которые блокируют диапазон разлива
Рассмотрим приведенный ниже пример. Когда вы применяете формулу = D2: D5 к данным, выдается ошибка РАЗЛИВА, так как здесь I m находится в пределах диапазона разлива.
Чтобы избавиться от ошибки #SPILL, просто переместите данные или удалите данные из диапазона разлива.
Когда данные, блокирующие диапазон разлива, скрыты
В некоторых случаях данные, которые блокируют диапазон разлива, скрыты и не очень очевидны, как в случае 1. Рассмотрим пример ниже.
В таких случаях, чтобы найти ячейку, блокирующую диапазон разлива, выполните следующие действия:
Шаг 1. Щелкните ячейку с надписью #SPILL! ошибка
Шаг 2: Щелкните восклицательный знак, как показано ниже. Вы можете видеть, что ошибка связана с тем, что диапазон разлива не пустой.
Шаг 3: В раскрывающемся списке нажмите «Выбрать препятствующие ячейки».
Шаг 4. Ячейка, блокирующая диапазон разлива, выделяется, как показано ниже.
Теперь, когда вы знаете, какая ячейка блокируется, проверьте, что именно вызывает проблему.
Шаг 5: При внимательном изучении ячейки вы можете увидеть некоторые данные, скрытые внутри ячеек.
Как видно на изображении выше, есть некоторые данные. Поскольку шрифт имеет белый цвет, распознать засор непросто. Чтобы избавиться от ошибки, удалите данные из ячейки в диапазоне Spill.
Исправление 2: удалите форматирование произвольных чисел; ; ; нанесен на ячейку
Иногда при произвольном форматировании чисел; ; ; нанесен на ячейку, есть вероятность увидеть ошибку SPILL. В таких случаях,
Шаг 1. Щелкните ячейку с надписью #SPILL! ошибка
Шаг 2: Щелкните восклицательный знак, как показано ниже.
Шаг 3: В раскрывающемся списке нажмите «Выбрать препятствующие ячейки».
Шаг 4. Ячейка, блокирующая диапазон разлива, выделяется, как показано ниже.
Шаг 5: Щелкните правой кнопкой мыши блокирующую ячейку.
Шаг 6: выберите формат ячеек
Шаг 7. Откроется окно «Форматирование ячеек». Перейдите на вкладку Number
Шаг 8. На левой панели выберите Пользовательский.
Шаг 9: На правой боковой панели измените Тип с; ; ; генералу
Шаг 10: нажмите кнопку ОК.
Математическая логика: основные понятия
Основателем формальной логики считают Аристотеля. В XVII в. Г. Лейбниц предложил вводить символы для определения высказываний. Д. Буль закрепил усвоенные знания и впервые обозначил предложения символами.
Схематически «ИСТИНА» замещается 1, а «ЛОЖЬ» – 0.
Под высказыванием понимают любое повествовательное предложение, дающее какую-либо информацию и способное принимать значение истинности или ложности. В алгебре логики отвлекаются от смысловой нагрузки предложений и рассматривают только логические значения.
Под отрицанием понимают новое выражение, принимающее значение истины в случае его ложности и наоборот.
Конъюнкцией двух переменных называют новое предложение, принимающее значение истинности в случае одновременного обозначения «1» и ложности в остальных ситуациях.
Под дизъюнкцией двух высказываний понимают новое выражение, принимающее значение «ЛОЖЬ» только при одновременном наличии «0» и «ИСТИНА» в остальных вариациях.
Импликацией двух переменных называют новое предложение, в котором:
- если посылка истинна, а следствие ложно, то выражение равняется «0»;
- высказывание равняется «1» в остальных случаях.
Под эквиваленцией двух переменных понимают новое высказывание, принимающее значение истинности только в случае одинаковости элементов. Иначе предложение равняется «0».
Логические значения выражений принято оформлять в табличном виде. Есть и другое название у такого рода информации. Говорят, для высказывания нужно построить таблицу истинности. В ней указываются первоначальные значения для всех переменных, а потом вычисляется результат всего выражения.
Функция ВПР: неверный результат либо ошибка #Н/Д
. развития. в четвертой ячейкеИнтервальный_просмотр артикула можно использовать Выпадающий столбца (в этом вернуть, отсчитываемый вправо откройте вкладку диапазон в формуле. сведения о формулах по ошибке #Н/Д русский вариант чаще н/д. нужно чтоб ВПР и тогда показателями ситуации не столбца (F3:F19) таблицы значения цены. ПервыйЭта функция ищетМотя G27 получаю данные нужно задать ИСТИНА или список (см. ячейку случае это будет от столбца поиска.ФормулаВ данном примере ячейка массива см. в
для конкретной функции, через ; работает. там просто был
поймете, что ЕСТЬ… улучшили. Аналогичная ситуация
отличаются (например, числовой столбец прайс-листа с заданное значение (в: Однако, аргумент… =(E27*F27)*B4, где B4 вообще опустить.Е9
самоПричиной может быть то,и нажмите кнопку E2 содержит ссылку статье Использование формул например ВПР илиСмысл формулы - ноль. пробовала черезВариант — «=VLOOKUP(E13;$Реестрдоговоров.B1:F601;4;FALSE())». получилась с функцией и текстовый). Этот названиями имеет номер нашем примере этоА чем плоха это курс валют.Для вывода Наименования товара используйте формулу =ВПР($A7;$A$11:$B$17;2;ИСТИНА) ).искомое_значение что вы допустилиВставить функцию
на несовпадающие диапазоны: массива: рекомендации и ИНДЕКС/ПОИСКПОЗ, щелкните здесь. если впр возвращает ЕСЛИ — неps Ключик -
VLOOKUP в Open случай особенно характерен 1, следовательно нам слово «Яблоки») в
в EXCEL 2003Так вот, еслиДля вывода найденной цены (онаПонятно, что в нашей)). Часто левый столбец ошибку при вводе
.= SUM (если (A2: примеры.
Если вы хотите работать ошибку (не нашлось), работает.. в сортировке или Office Calc. как при использовании вместо
нужна цена из крайнем левом столбце комбинация ЕСЛИ(ЕОШИБКА(ВПР(… мы не вводим не обязательно будет задаче ключевой столбец называется аргументаExcel автоматически запустит мастер.
A11 = D2,По умолчанию функции, которые дальше, приведенный ниже то выводим 0Hugo121
ее отсутствии… ;) ни странно, схожая текстовых наименований числовых
столбца с номером указанной таблицы (прайс-листа)Работает распрекрасно хорошо значение в E27 совпадать с заданной) используйте не должен содержатьключевымномер_столбцаЩелкните любой аргумент, и B2: B5)) ищут данные в контрольный список поможет
planetaexcel.ru>
Функция ВПР возвращает ошибку #ЗНАЧ! (#VALUE!)
Обычно Excel сообщает об ошибке #VALUE! (#ЗНАЧ!), когда значение, использованное в формуле, не подходит по типу данных. Если брать функцию ВПР, то обычно стоит рассмотреть две основные причины ошибки #ЗНАЧ!.
1. Искомое значение больше 255 символов.
В функции ВПР есть ограничение на длину искомого значения – оно не должно быть более 255 символов. Если вы столкнулись с такой проблемой, то рекомендуем использовать для этих целей все ту же связку ИНДЕКС+ПОИСКПОЗ (INDEX+MATCH).
2. Неправильно указан полный путь на другую книгу.
Эта ошибка может возникнуть, если вы извлекаете данных из другой книги Excel прописали неполный путь до нее. Чтобы не ошибиться при написании формулы не пишите путь к другой книге от руки, а откройте эту книгу и выделяйте нужный диапазон.
3. Аргумент номер_столбца в функции ВПР меньше 1
Это может произойти если номер столбца вы ищите с помощью какой-то другой функции. Просто проверьте и убедитесь, что функция работает корректно.
Исправления, которые необходимо соблюдать, если диапазон разлива слишком велик.
Допустим, вы определяете причину и замечаете, что ошибка видна из-за слишком большого диапазона разлива, как показано ниже.
Когда динамический массив отсутствовал, в Excel происходило нечто, называемое неявным пересечением, которое заставляло возвращать один результат, даже если формула могла возвращать несколько результатов. Рассмотрим пример: если формула = B: B * 5% применяется в Excel 2019 или более ранних версиях с неявным пересечением, результат будет следующим:
Однако, когда та же формула используется в Excel 365, вы видите следующую ошибку
Чтобы решить эту проблему, попробуйте следующие исправления
Исправление 1: примените неявное пересечение с помощью оператора @
Когда мы говорим = B: B, динамический массив будет ссылаться на весь столбец B. Вместо этого мы можем заставить Excel наложить неявное пересечение с помощью оператора @
Поскольку добавлено неявное пересечение, формула будет применена к одной ячейке. Чтобы расширить формулу,
1. Просто нажмите на точку, как показано ниже.
2. При необходимости перетащите его на ячейки. К этим ячейкам будет применена та же формула.
Исправление 2: вместо ссылки на столбец обратитесь к диапазону
В формуле = B: B * 5% мы ссылаемся на столбец B. Вместо этого ссылаемся на конкретный диапазон = B2: B4 * 5%.
Это все
Надеемся, эта статья была информативной.
Пожалуйста, поставьте лайк и прокомментируйте, если вам удалось решить проблему с помощью вышеуказанных методов.
Спасибо за чтение.
Функция ВПР возвращает ошибку #Н/Д (#N/A)
#Н/Д (#N/A) означает «Нет данных» («not available»). При протягивании формулы ВПР у вас появляется #Н/Д. Вопрос даже не в том, что ВПР не работает, а почему появляются данные значения.
Решение
1. Не закреплен диапазон таблицы
Если при протягивании формулы ВПР у вас первое значение подтянулось а в остальных отобразилось #Н/Д то скорее всего вы не закрепили диапазон таблицы с помощью $ и при протягивании у вас таблица начала съезжать вниз. Еще раз прочитайте внимательно описание функции ВПР с примером.
2. Искомое значение и значение в просматриваемом диапазоне не совпадает
На рисунке выше есть #Н/Д два раза. Один раз напротив товара «Савок» и другой напротив слова «Стол».
В первом случае понятно — товара «Савок» у нас нет в прайс листе, отсюда и ошибка.
Во втором случае непонятно, товар «Стол» у нас есть, но все равно Н/Д#. Как правило ошибка заключается в написании слов и форматах либо в таблице заказов либо на странице Прайс лист.
В нашем случае у нас после слова «Стол » стоит пробел, поэтому данное слово не находится в прайс листе. Для начала проверьте содержимое ячеек на невидимые пробелы. Особенно часто они встречаются в конце строки. Кликните на ячейку и переместитесь в конец формулы. Удалите лишние пробелы, если они есть.
В данном примере можно так же использовать функцию СЖПРОБЕЛЫ
=ВПР(СЖПРОБЕЛЫ(A3);$F$2:$I$16;3;0)
то есть искомое значение сразу пропускается через данную функцию, чтобы удалить в искомом значении лишние пробелы.
В нашем случаем мы ищем текстовые значения — «Наименование товара», иногда поиск идет по числовым значениям. В данном случае #Н/Д может появляться, когда искомое значение, например в формате числа, а в таблице в виде текста. Для исправления ошибку потребуется перевести формат текста в числа или наоборот, то есть сделать одинаковый формат.
Еще более редкий случай, это когда в слове может быть вместо русских слов латинские и наоборот. Например A и А это разные буквы разных алфавитов, поэтому и слова будут разными для ВПР. Чтобы проверить, просто сделайте в отдельном столбце ссылку одной ячейки на другую =A4=F6 в нашем примере эта формула вернет «Ложь» так как эти ячейки не равны (в одном слове есть лишний пробел), аналогичная ситуация будет если вместо русской буквы «С» будет английская буква «C».
Что интересно, используя #Н/Д можно находить неуникальные значения в списках. Например, у вас есть список сотрудников компании и вам присылают другой список сотрудников и просят из этого списка найти тех людей, которые не работают в вашей компании. Применяем ВПР одного списка к другому и там где #Н/Д и будут люди, которые не работают в вашей компании.
Как в Excel перенести текст на другую строку в одной ячейке
- Выделите ячейку, в которой вы хотите запустить формулу,
- Перейдите на вкладку «Формулы» в верхней части навигации.
- Выберите «Вставить функцию«. Если вы используете Microsoft Excel 2007, этот параметр будет находиться слева от панели навигации «Формулы».
Чтобы обойти такую ошибку, вам нужно ввести правильный параметр, удалить проверку информации или скопировать другую ячейку и скопировать ее в ту, в которую вы не пишете. Самый правильный шаг — ввести правильный параметр, потому что при наличии контроля в Excel работают специальные алгоритмы, которые при несоблюдении правила ввода данных будут выполнять функции некорректно.
Полезные сведения → Как объединить ячейки → Как вставить значения → Аргументы функции → Работа с форматами → Функция ЕСЛИ → Как удалить пробелы → Функция впр vlookup→ Работа с таблицами
Что значит ошибка перенос в excel
Формула с пролитой массивом, в который вы попытались ввести формулу, выходит за пределы диапазона. Попробуйте еще раз с небольшим диапазоном или массивом.
В следующем примере при перемещении формулы в ячейку F1 ошибка будет исправлена, и формула будет переносима правильно.
Распространенные причины: полные ссылки на столбцы
При создании формул в lookup_value в них часто неправильно засоряются. Перед динамическим массивом Приложение Excel учитывало только значение в той же строке, что и формулу, и игнорировало все остальные, так как при функции ВЛОП ожидалось только одно значение. При вводе динамических массивов Excel рассматривает все значения, предоставляемые lookup_value. Это означает, что если в качестве аргумента lookup_value столбец, Excel попытается найти все 1 048 576 значений в столбце. Когда все будет готово, он попытается пролить их на сетку и, скорее всего, зажмет #SPILL! ошибка «#ЗНАЧ!».
Например, при размещении в ячейке E2, как по примеру ниже, формула =ВЛОЖЕННАЯ(A:A;A:C;2;ЛОЖЬ) ранее только подыскала бы ИД в ячейке A2. Однако в динамическом массиве Excel формула приведет к #SPILL! из-за того, что Excel подытнет весь столбец, отвернет результат с результатом 1 048 576 и пожмет в конце сетки Excel.
Существует три простых способа решения этой проблемы:
Ссылаясь только на значения подпапок, которые вас интересуют. Этот стиль формулы возвращает динамический массив, ноне работает с таблицами Excel.
Ссылаясь только на значение в той же строке, скопируйте формулу вниз. Этот традиционный стиль формул работает в таблицах,но не возвращаетдинамический массив.
=В ПРОСМОТР(A2;A:C;2;ЛОЖЬ)
Запрос на выполнение неявного пересечения с помощью оператора @ и копирование формулы вниз. Этот стиль формулы работает в таблицах,но не возвращаетдинамический массив.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.
Столбец поиска искомого значения не является крайним левым.
Самая распространенная ошибка, когда ВПР не работает. Давайте рассмотрим тот же пример, что мы рассматривали при описании функции ВПР. У нас есть таблица заказов и Прайс лист.
Обратите внимание, что в данном примере таблица Прайс лист отличается. Названия товаров теперь находятся не в первом столбце, а в третьем, после цены товара
В текущем виде нельзя будет воспользоваться функцией ВПР, так как функция находит искомое значение в Крайнем левом столбце и возвращает значение из указанного вами номера столбца который находится правее крайнего левого столбца. В нашем случае правее ничего нет, поэтому ВПР не будет работать.
Решение
1. Если есть возможность, то перенесите столбец, где ищется искомое значение в крайнее левое положение (либо просто левее столбца из которого необходимо возвращать значение, в нашем примере относительно столбца с ценой) как указано на рисунке выше.
2. Обычно переносить столбец не очень удобно, например в случаях, когда эту таблицу нам присылаются постоянно в таком формате, поэтому как правило применяют прием добавления слева таблицы дублирующего вспомогательного столбца.
Выделяем первый столбец прайс листа, выбираем «Главная», «Ячейка», «Вставить», «Вставить столбцы на лист». Либо используйте пустой столбец, если он у вас уже есть. Встаем в первую ячейку вспомогательного столбца и просто делаем ссылку на нужный нам столбец и протягиваем формулу вниз.
Получили точную копию столбца с наименованием товара левее цены, теперь можно использовать функцию ВПР. Таблицу теперь необходимо брать начиная с нашего вспомогательного столбца.
3. Можно использовать комбинацию функций ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH), как более гибкую альтернативу для ВПР.
Как исправить ошибку #ЗНАЧ! в Excel
Ошибка #ЗНАЧ! (#VALUE) возникает в следующих случаях:
- что-то не так с ячейкой (ячейками), на которую ссылается формула,
- что-то не так с самой формулой.
Иногда найти источник проблемы не просто. Ниже — о самых распространенных случаях и как и их исправить.
Математическая формула ссылается на текст
Формулы с математическими операторами могут вычислять только числа. Если одна или несколько ячеек, на которые ссылается формула, содержит текст, формула будет возвращать ошибку.
Решение: использовать вместо формулы (уравнения, составленного пользователем) функцию (формулу, заранее заданную Excel). Функции по умолчанию игнорируют большую часть текстовых значений и производят расчеты лишь с числами.
Например, на примере ниже функция =СУММ(B2,B11) будет игнорировать текст в ячейке B11.
Скриншот: Zapier.com
Стоит помнить, что эта функция не оповещает автоматически о пропусках ячеек, и ее результат может вводить в заблуждение.
Одна или более ячеек содержат пробелы
В этих случаях ячейка выглядит пустой.
Решение: найти и заменить пробелы. Вот как это сделать:
- выделите диапазон ячеек, к которому обращается формула;
- нажмите на иконку с биноклем, нажмите «Найти и выделить»;
- в окне «Найти и заменить» выберите «Заменить»;
- в поле «Найти» вставьте пробелы;
- поле «Заменить на» оставьте пустым;
- нажмите «Заменить все».
ЕСЛИ ОШИБКА формула в Excel
Чтобы вернуть пользовательское сообщение или выполнить другой расчет при возникновении ошибки, используйте ЕОШИБКА вместе с функцией ЕСЛИ. Общая формула выглядит следующим образом:
ЕСЛИ(ЕОШИБКА(формула(…), text_or_calculation_if_error, формула())
В переводе на человеческий язык это гласит: если основная формула приводит к ошибке, вывести указанный текст или запустить другой расчет, в противном случае вернуть нормальный результат формулы.
На изображении ниже деление суммы на количество приводит к нескольким ошибкам в Цена столбец:
Чтобы заменить все различные коды ошибок настраиваемым текстом, вы можете использовать следующую формулу ЕСЛИ ОШИБКА:
=ЕСЛИ(ЕОШИБКА(A2/B2), «Неизвестно», A2/B2)
В Excel 2007 и более поздних версиях того же результата можно добиться с помощью встроенной функции ЕСЛИОШИБКА:
=ЕСЛИОШИБКА(A2/B2, «Неизвестно»)
Следует отметить, что формула ЕСЛИОШИБКА работает немного быстрее, поскольку вычисление A2/B2 выполняется только один раз. В то время как ЕСЛИ ОШИБКА вычисляет его дважды — сначала, чтобы увидеть, не генерирует ли он ошибку, а затем еще раз, если тест ЛОЖЬ.
Причины и пути решения в Excel
Существует много причин, почему не работает ВПР в Excel и появляется надпись Н / Д. В каждой из ситуаций необходимо индивидуально подходить к решению вопроса с учетом возникшей неисправности.
Наиболее эффективный метод
В ситуации, когда не работает функция ВПР в Excel, проверьте наличие элемента на листе или задействуйте в формуле функцию обработки ошибок, к примеру, =ЕСЛИОШИБКА(ФОРМУЛА();0). В таком случае при появлении сбоев в расчете показывается ноль, а в ином случае — результат формулы. Можно дополнить запись “”, чтобы ничего не показывалось, или внести в скобки какую-либо запись.
Ошибка в типе параметров
Характерная причина, почему не работает ВПР в Excel — нахождение исходных / искомых данных к различным типам. К примеру, если вы используете ВПР в виде числа, а исходные данные сохраняются в качестве текста. Для решения вопроса убедитесь, что типы информации идентично. Для проверки формата сделайте следующее:
- Выберите ячейку (одну или несколько).
- Жмите правой кнопкой мышки.
- Выберите формат ячеек, а дальше Число.
Измените формат.
Для принудительного внесения изменений нужно изменить формат для всего столбца. Для начала примените требуемое форматирование, а после выберите «Данные», «Текст по столбцам» и «Готово». После этого проверьте, появляется Н Д или нет.
Лишние пробелы
Распространенная причина, почему не работает формула ВПР в Excel, состоит в наличии пробелов. Для их удаления используйте функцию СЖПРОБЕЛЫ.
Ошибки метода поиска совпадения
Следующее объяснение, почему не срабатывает ВПР в Excel и возвращается Н Д — ошибки в применении метода совпадения. По умолчанию у опции ВПР имеется аргумент «интервальный просмотр», который дает команду на поиск точного совпадения даже при отсутствии сортировки данных в таблице.
Для поиска точного совпадения введите для аргумента «интервальный_просмотр» показатель ЛОЖЬ.
При этом учтите, что ИСТИНА, которое дает возможность поиска приблизительного параметра, может вернуть ошибку Н / Д. При использовании опции ПОИСКПОЗ попробуйте поменять параметр аргумента «тип_сопоставления» для указания порядке сортировки таблицы.
Не соответствие числа строк / столбцов заданному диапазону
В ситуации, когда не работает ВПР в Excel и вылетает ошибка Н / Д, сделайте дополнительную проверку. Вам нужно убедиться, что диапазон, в отношении которого ссылается формула, правильный. Как вариант, можно ввести формулу массива в меньшее / большее количество ячеек с учетом ссылки на диапазон.
В ячейке введена надпись Н / Д или Н Д
В Эксель ВПР часто не работает, если пользователь вручную ввел в ячейку параметр #Н / Д или НД (). Для решения проблем его нужно поменять на фактические данные, как только они будут доступны. До этого момента формулы, в которых содержатся ссылки на эти ячейки, не смогут вычислить этот параметр. При этом будет возвращаться ошибка Н Д.
Другие ошибки
Дополнительно стоит выделить и ряд других ситуаций, когда ВПР в Excel по какой-то причине не работает:
- В используемой формуле нет одного или более аргументов. Для исправления проблемы введите все необходимые документы и проверьте, работает опция или нет. Для контроля можно использовать Visual Basic.
- Пользовательская опция недоступна. Для исправления проблемы убедитесь, что документ Excel с пользовательской функцией открыт, а опция работает корректно.
- Макрос имеет функцию, которая возвращает Н Д. Если ВПР не работает по этой причине, для исправления ошибки убедитесь в правдивости аргументов и их нахождении в нужных местах.
- Изменение защищенного файл с опцией ЯЧЕЙКА. Для исправления ситуации, когда ВПР в Excel не работает, жмите на комбинацию Ctrl+Alt+F9.
- Столбец не является первым слева дли поискового диапазона. Для решения проблемы нужно ввести соответствующий параметр и проверить, появляется ли Н Д. Как вариант, можно использовать функции ИНДЕКС и ПОИСКПОК в качестве гибкой альтернативы для ВПР.
- Неправильное форматирование числа. Бывают ситуации, когда цифры указаны в текстовом формате. Это часто происходит при импортировании сведений из внешней базы данных или при вводе апострофа перед числом для сохранения нуля в начале. Для решения проблемы жмите по ошибке и укажите Convert to Number. При появлении Н Д для многих чисел выделите их и жмите правой кнопкой мышки, а после выберите Format Cells и вкладку Число и Числовой.
Выше рассмотренные основные причины, почему не работает ВПР в Excel, а также описаны базовые шаги для устранения ошибки Н Д. Всегда начинайте с проверки правильности ввода параметра, а после переходите к другим вариантам.