Поискпоз ошибка имя

Исправление ошибки #ИМЯ?

​Смотрите также​ он появляется после​ Например, сделайте двойной​ ячеек. Если диапазоны​

​ формуле и устранить​​ Ниже приведен пример​Начинайте ввод формул со​Источник: http://www.excel-easy.com/functions/formula-errors.html​ том случае, когда​То есть, для первого​ любое другое повторение​ книге с помощью​, чтобы правильно ввести​Наиболее очевидные признаки числа​

​(нет данных) –​Параметры​в группе​Обычно ошибка #ИМЯ? возникает​ автозамены части формул​ щелчок левой кнопкой​ не пересекаются, программа​ ее.​ правильно составленной формулы,​ знака равенства (=).​Перевел: Антон Андронов​ Excel не может​ аргумента Вы вставляете​ значения, которое Вам​

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

​ отображает ошибочное значение​

​Как видно при делении​ в которой функция​ Пример:​

​Автор: Антон Андронов​ распознать текст в​

Формула ссылается на несуществующее имя

​ значение, которое нужно​ нужно. Если нужно​Трудно представить ситуацию, когда​В большинстве случаев, Microsoft​ показаны на рисунке​

​ не может найти​Щелкните​нажмите кнопку​​ имени формулы. Рассмотрим​​Serge_007​ заголовков столбцов данной​

​ – #ПУСТО! Оператором​​ на ячейку с​​ ЕСЛИ вкладывается в​​=СУММ(A1:A8)​Будь то ошибки #ЗНАЧ!,​ формуле (например, из-за​ проверить на предмет​

1. ​ извлечь все повторяющиеся​ кто-то вводит значение​ Excel сообщает об​ ниже:​ искомое значение. Это​Надстройки​Использовать в формуле​ пример:​: Загляните в ячейку​ ячейки.​

2. ​ пересечения множеств является​​ пустым значением программа​​ другую функцию ЕСЛИ​​Для умножения чисел используйте​​ #ИМЯ! или проблема​​ опечатки).​​ ошибки, а для​​ значения, Вам потребуется​​ меньше​

3. ​ ошибке​

4. ​Кроме этого, числа могут​​ может произойти по​​.​и выберите нужное​

5. ​Важно:​

6. ​ А4, на которую​​Так решетки (;;) вместо​​ одиночный пробел. Им​

   ​ воспринимает как деление​ для расчета вычетов​

7. ​ символ *, а​ с функцией ВПР,​Просто исправьте​ второго аргумента указываете,​

8. ​ комбинация из функций​​1​​#VALUE!​​ быть сохранены в​​ нескольким причинам.​​В списке​​ имя.​ Ошибка #ИМЯ? означает, что​

   

​ ссылается формула в​ значения ячеек можно​ разделяются вертикальные и​ на 0. В​ на основе уровня​

Определенное имя указано неправильно

​ не X. Пример:​ указанные ниже сведения​SU​ что нужно возвратить,​

​ИНДЕКС​, чтобы обозначить столбец,​(#ЗНАЧ!), когда значение,​​ формате​​Хорошая мысль проверить этот​Управление​Подробнее об использовании определенных​ нужно исправить синтаксис,​ В4​

​ увидеть при отрицательно​​ горизонтальные диапазоны, заданные​ результате выдает значение:​

​ доходов.​​=A1*A8​ помогут вам устранить​на​ если ошибка найдётся.​(INDEX),​​ из которого нужно​​ использованное в формуле,​​General​​ пункт в первую​​выберите пункт​​ имен см. в​ поэтому если вы​А4 возвращает значение​

Текстовые значения не заключены в двойные кавычки

​ дате. Например, мы​ в аргументах функции.​ #ДЕЛ/0! В этом​=ЕСЛИ(E2​Следите за соответствием открывающих​ неполадки.​SUM​Например, вот такая формула​НАИМЕНЬШИЙ​ извлечь значение. Хотя​

​ не подходит по​(Общий). В таком​ очередь! Опечатки часто​​Надстройки Excel​​ статье Определение и​ видите ее в​

​ ошибки​​ пытаемся отнять от​В данном случаи пересечением​ можно убедиться и​

В ссылке на диапазон пропущено двоеточие

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

​ возвращает пустую ячейку,​(SMALL) и​ это возможно, если​ типу данных. Что​ случае есть только​ возникают, когда Вы​

​и нажмите кнопку​​ использование имен в​ формуле, устраните ее.​#ИМЯ?​

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

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

​ дату. А в​ C3 и функция​

1. ​Читайте также: Как убрать​​ЕСЛИ значение в ячейке​​ парными. В этом​​ или неожиданные результаты,​​ ошибке​

2. ​ не найдено:​​(ROW).​​ вычисляется другой функцией​

3. ​ВПР​​ – числа выровнены​​ большими объёмами данных,​​.​​Если в формуле неправильно​​ с помощью функций​​ видите в В4​

4. ​ результате вычисления установлен​ отображает ее значение.​​ ошибку деления на​​ A5 меньше чем​

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

​ примере две пары:​ скачайте краткий справочник​

​#ЗНАЧ!​=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),»»)​К сожалению, формулы с​ Excel, вложенной в​, то обычно выделяют​ по левому краю​ состоящих из тысяч​

support.office.com

Функция ВПР не работает – способы устранения ошибок Н/Д, ИМЯ и ЗНАЧ

​Установите нужный флажок и​ указано определенное имя,​ обработки ошибок, например​​Tviga​​ формат ячеек «Дата»​Заданные аргументы в функции:​ ноль формулой Excel.​ 31 500, значение умножается​=ЕСЛИ(40>50;СУММ(G2:G5);0)​ по устранению неполадок​(#VALUE!) в том​=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);»»)​​ВПР​​ВПР​

​ две причины ошибки​ ячейки, в то​ строк, или когда​​ нажмите кнопку​​ вы увидите ошибку​ функции ЕСЛИОШИБКА.​: =ЕСЛИ(‘E:\КХЛ 2014_н\[РЕЗ_2013.xls]МЕДВЕШЧАК’!$C$2=»»;»»;ЕСЛИ(И(МЕДВЕШЧАКСИМВ(A3;1)=».»;ПСТР(‘E:\КХЛ 2014_н\[РЕЗ_2013.xls]МЕДВЕШЧАК’!$C$2;23;1)=»1″);»*»;ЕСЛИ(И(МЕДВЕШЧАКСИМВ(A3;1)<>».»;ПСТР(‘E:\КХЛ​ (а не «Общий»).​ =СУММ(B4:D4 B2:B3) –​В других арифметических вычислениях​ на 15 %. Но​Введите все обязательные аргументы.​​ с функцией ВПР.​​ случае, когда для​Если Вы хотите показать​перестают работать каждый​.​#ЗНАЧ!​ время как стандартно​

​ искомое значение вписано​ОК​ #ИМЯ?.​​Чтобы избежать опечаток в​​ 2014_н\[РЕЗ_2013.xls]МЕДВЕШЧАК’!$C$2;21;1)=»1″);»*»;»»)))​​Скачать пример удаления ошибок​​ не образуют пересечение.​​ (умножение, суммирование, вычитание)​​ ЕСЛИ это не​ В этом вам​​Эта проблема обычно связана​​ формулы введён аргумент​ собственное сообщение вместо​ раз, когда в​Итак, если случилось, что​.​ они выравниваются по​ в формулу.​​.​​Допустим, что в электронной​ именах формулы, используйте​это в ячейке​ в Excel.​ Следовательно, функция дает​

• ​ пустая ячейка также​
• ​ так, проверьте, меньше​ поможет построитель формул.​
• ​ с наличием числовых​
• ​ не подходящего типа.​ стандартного сообщения об​
• ​ таблицу поиска добавляется​ аргумент​Будьте внимательны: функция​

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

​ правому краю.​​Если Вы используете формулу​​Задать вопрос на форуме​​ таблице было создано​​ мастер формул в​​ А4.. а ячейка​​Неправильный формат ячейки так​ значение с ошибкой​ является нулевым значением.​ ли это значение,​ Начните вводить формулу​ и текстовых значений.​

1. Искомое значение написано с опечаткой

​a) Измените значение в​ ошибке функции​ или удаляется новый​col_index_num​ВПР​Решение:​ с условием поиска​ сообщества, посвященном Excel​ определенное имя​ Excel. Когда вы​

2. Ошибка #Н/Д при поиске приближённого совпадения с ВПР

​ В4 ссылается на​ же может отображать​ – #ПУСТО!​​​ чем 72 500. ЕСЛИ​​ с имени функции​ Дополнительные сведения см.​ ячейке​ВПР​ столбец. Это происходит,​​(номер_столбца) меньше​​не может искать​

• ​Если это одиночное​ приближённого совпадения, т.е.​У вас есть предложения​
• ​Прибыль​ начинаете вводить имя​

3. Ошибка #Н/Д при поиске точного совпадения с ВПР

​ А4 в которой​ вместо значений ряд​​Неправильная ссылка на ячейку:​​Неправильное число: #ЧИСЛО! –​ это так, значение​ и нажмите сочетание​ в разделе Исправление​A3​​, впишите его в​​ потому что синтаксис​1​ значения, содержащие более​ значение, просто кликните​​ аргумент​​ по улучшению следующей​

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

​. В приведенном ниже​ формулы в ячейку​ МЕДВЕШЧАКСИМВ(A3;1)=».»; Эксель такого​​ символов решетки (;;).​​ #ССЫЛКА! – значит,​ это ошибка невозможности​ умножается на 25 %;​ клавиш CONTROL+A, чтобы​ ошибки #ЗНАЧ!.​.​ кавычках, например, так:​ВПР​, функция​ 255 символов. Если​ по иконке ошибки​range_lookup​​ версии Excel? Если​​ примере имя указано​

​ или строку формул,​​ не знает! Вот​ivanuch​ что аргументы формулы​ выполнить вычисление в​ в противном случае —​ открыть построитель формул.​​Убедитесь в том, что​​b) Используйте функцию, которая​​=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),»Ничего не найдено. Попробуйте​​требует указывать полностью​ВПР​​ искомое значение превышает​​ и выберите​

5. Числа форматированы как текст

​(интервальный_просмотр) равен TRUE​​ да, ознакомьтесь с​​ неправильно, поэтому функция​​ появляется раскрывающийся список​​ и говорит что​: Почему формула ЕСЛИ​ ссылаются на ошибочный​ формуле.​ на 28 %​

​Текст в формулах следует​ вы правильно ввели​ игнорирует ячейки, содержащие​ еще раз!»)​ весь диапазон поиска​также сообщит об​ этот предел, то​Convert to Number​

​ (ИСТИНА) или не​ темами на портале​ по-прежнему выдает ошибку​ формул с похожим​

​ ИМЯ не верное!​ выражение A1=»*» воспринимает​ адрес. Чаще всего​​Несколько практических примеров:​​.​ заключать в кавычки.​ имена, что текст​ текст.​=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);»Ничего не найдено. Попробуйте​ и конкретный номер​ ошибке​ Вы получите сообщение​(Конвертировать в число)​

​ указан, Ваша формула​​ пользовательских предложений для​ #ИМЯ?.​ именем. После ввода​KuklP​​ как #ИМЯ?​​ это несуществующая ячейка.​Ошибка: #ЧИСЛО! возникает, когда​

​Чтобы использовать функцию ЕСЛИОШИБКА​ Пример:​ заключен в кавычки,​Сообщение об ошибке​ еще раз!»)​ столбца для извлечения​#ЗНАЧ!​​ об ошибке​​ из контекстного меню.​ может сообщить об​​ Excel.​​Решение.​​ имени формулы и​​: Мля! Ну все​​… и что​​В данном примере ошибка​

6. В начале или в конце стоит пробел

​ числовое значение слишком​ с уже имеющейся​​=ЕСЛИ(A2>B2;»Превышение бюджета»;»ОК»)​​ а имена листов —​​#ДЕЛ/0!​​Так как функция​ данных. Естественно, и​.​#ЗНАЧ!​Если такая ситуация со​ ошибке​Этот урок объясняет, как​

​Исправьте опечатку и​ открывающей скобки мастер​ находят, Вы -​ сделать в 2003​

​ возникал при неправильном​ велико или же​ формулой, просто вложите​Измените тип данных для​ в одиночные кавычки​​(#DIV/0!) появляется при​​ЕСЛИОШИБКА​​ заданный диапазон, и​​Если же аргумент​

​.​
​ многими числами, выделите​

​#Н/Д​ быстро справиться с​ повторите попытку.​

​ формул отображает подсказку​ нет. И в​ Екселе чтобы этого​ копировании формулы. У​​ слишком маленькое. Так​​ готовую формулу в​​ ячейки, на которую​​ (‘). О других​​ попытке деления числа​​появилась в Excel​ номер столбца меняются,​col_index_num​​Решение:​​ их и щелкните​​в двух случаях:​​ ситуацией, когда функция​​Совет:​​ с синтаксисом.​

​ В4 у Вас​
​ не было ...​

​ нас есть 3​ же данная ошибка​ функцию ЕСЛИОШИБКА:​​ дана ссылка. Нажмите​​ причинах ошибки и​​ на ноль или​​ 2007, при работе​ когда Вы удаляете​

Ошибка #ЗНАЧ! в формулах с ВПР

​(номер_столбца) больше количества​Используйте связку функций​ по выделенной области​​Искомое значение меньше наименьшего​​ВПР​ Вместо того чтобы вручную​Мастер функций также позволяет​ ссылка на А4.​ПОМОГИТЕ ПОЖАЛУСТА !!!!​​ диапазона ячеек: A1:A3,​​ может возникнуть при​=ЕСЛИОШИБКА(ЕСЛИ(E2​​+1, а затем​​ способах ее устранения​

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

​ на пустую ячейку.​​ в более ранних​​ столбец или вставляете​ столбцов в заданном​ИНДЕКС+ПОИСКПОЗ​ правой кнопкой мыши.​ значения в просматриваемом​(VLOOKUP) не хочет​ вводить определенные имена​​ избежать синтаксических ошибок.​​P.S. ivanuch, теперь​

​Serge_007​​ B1:B4, C1:C2.​​ попытке получить корень​​Это означает, что ЕСЛИ​ выберите параметр​ можно узнать из​a) Изменить значение в​

​ версиях Вам придётся​
​ новый.​

2. Не указан полный путь к рабочей книге для поиска

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

​ меню выберите​​Столбец поиска не упорядочен​​ 2013, 2010, 2007​ это Excel. На​

​ формулой, а затем​
​ нужен файл Эксель,​

​ языке написан адрес​ ячейку A4 вводим​

​ Например, =КОРЕНЬ(-25).​
​ какой-либо части исходной​

​.​Эта ошибка может возникнуть,​​A2​​ЕСЛИ​​И снова на​​сообщит об ошибке​​ справится с этой​​Format Cells​​ по возрастанию.​​ и 2003, а​ вкладке​​ на вкладке​​ а не картинка?​

​ ячейки​ суммирующую формулу: =СУММ(A1:A3).​В ячейке А1 –​​ формулы возвращается ошибка,​​ЕСЛИ — одна из самых​ если вы ввели​на любое число,​​(IF) и​​ помощь спешат функции​#REF!​ задачей:​(Формат ячеек) >​

​Если Вы ищете точное​ также, как выявить​​Формулы​​Формула​ivanuch​А​ А дальше копируем​ слишком большое число​ выводится значение 0,​

3. Аргумент Номер_столбца меньше 1

​ универсальных и популярных​ лишние символы в​ не равное нулю.​​ЕОШИБКА​​ИНДЕКС​(#ССЫЛ!).​=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))​ вкладка​ совпадение, т.е. аргумент​ и исправить распространённые​в группе​​нажмите кнопку​​: …. да видать​

​. Что бы работало​ эту же формулу​​ (10^1000). Excel не​​ а в противном​​ функций в Excel,​​ формуле. Например, не​​b) Предотвратите возникновение ошибки​​(ISERROR) вот так:​(INDEX) и​​Простейший случай – ошибка​​=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))​

​Number​​range_lookup​​ ошибки и преодолеть​Определенные имена​Вставить функцию​​ … зашипела у​​ — надо писать​​ под второй диапазон,​​ может работать с​

Ошибка #ИМЯ? в ВПР

​ случае возвращается результат​​ которая часто используется​​ вводите в формуле​ при помощи логической​=IF(ISERROR(VLOOKUP формула),»Ваше сообщение при​ПОИСКПОЗ​

​#NAME?​Если Вы извлекаете данные​

ВПР не работает (ограничения, оговорки и решения)

​(Число) > формат​​(интервальный_просмотр) равен FALSE​​ ограничения​нажмите кнопку​.​ меня моя …​ на английском​ в ячейку B5.​​ такими большими числами.​​ выражения ЕСЛИ. Некоторые​ в одной формуле​1000 р.​ функции​ ошибке»,VLOOKUP формула)​​(MATCH). В формуле​​(#ИМЯ?) – появится,​

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

​ из другой рабочей​​Number​​ (ЛОЖЬ) и точное​ВПР​Использовать в формуле​Excel автоматически запустит мастер.​ лысина … спасибо​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИ(A1=»*»;1;2)​ Формула, как и​В ячейке А2 –​ пользователи при создании​ несколько раз (иногда​Вместо этого нужно​ЕСЛИ​

​=ЕСЛИ(ЕОШИБКА(ВПР формула);»Ваше сообщение при​​ИНДЕКС+ПОИСКПОЗ​ если Вы случайно​ книги, то должны​(Числовой) и нажмите​ значение не найдено,​.​​и выберите нужное​​Щелкните любой аргумент, и​ за науку …​ivanuch​ прежде, суммирует только​ та же проблема​ формул изначально реализуют​ в сочетании с​

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

​ вводить​​(IF).​​ ошибке»;ВПР формула)​Вы раздельно задаёте​ напишите с ошибкой​ указать полный путь​ОК​ формула также сообщит​В нескольких предыдущих статьях​ имя. Excel добавит​ Excel покажет вам​ExElement​: … к сожалению​ 3 ячейки B2:B4,​ с большими числами.​​ обработку ошибок, однако​​ другими функциями). К​​1000​​Пояснение: Если ячейка​​Например, формула​​ столбцы для поиска​

3. В таблицу был добавлен или удалён столбец

​ имя функции.​​ к этому файлу.​​.​ об ошибке​ мы изучили различные​ его в формулу.​ сведения о нем.​: Здравствуйте уважаемые форумчане,​​ написано A на​​ минуя значение первой​ Казалось бы, 1000​ делать это не​ сожалению, из-за сложности​. О других причинах​A2​ЕСЛИ+ЕОШИБКА+ВПР​ и для извлечения​Решение очевидно – проверьте​ Если говорить точнее,​

​Это наименее очевидная причина​​#Н/Д​ грани функции​​При добавлении текстовых значений​​Ниже приведены другие причины​​ ексельщики.​​ английском …​​ B1.​​ небольшое число, но​ рекомендуется, так как​ конструкции выражений с​ ошибки и способах​будет равна нулю,​, аналогична формуле​ данных, и в​ правописание!​ Вы должны указать​ ошибки​. Более подробно о​

4. Ссылки на ячейки исказились при копировании формулы

​ВПР​ в формулы необходимо​

​ возникновения ошибок #ИМЯ?.​​Столкнулся со сложностями​Pelena​Когда та же формула​​ при возвращении его​​ обработчик подавляет возможные​ ЕСЛИ легко столкнуться​​ ее устранения можно​​ то значением ячейки​​ЕСЛИОШИБКА+ВПР​​ результате можете удалять​Помимо достаточно сложного синтаксиса,​ имя рабочей книги​#Н/Д​​ том, как искать​​в Excel. Если​

ВПР – работа с функциями ЕСЛИОШИБКА и ЕОШИБКА

​ заключать их в​Если формула содержит ссылку​ с формулой ЕСЛИ​​: Покажите формулу полностью​​ была скопирована под​​ факториала получается слишком​​ ошибки и вы​​ с ошибкой #ЗНАЧ!.​​ узнать из раздела​A3​, показанной выше:​ или вставлять сколько​​ВПР​​ (включая расширение) в​​в работе функции​​ точное и приближенное​ Вы читали их​ кавычки, даже если​ на имя, которое​​Собственно задача более​​ivanuch​ третий диапазон, в​

ВПР: работа с функцией ЕСЛИОШИБКА

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

​=IF(ISERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE)),"",VLOOKUP($F$2,$B$2:$C$10,2,FALSE))​
​ угодно столбцов, не​

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

​: … пожалуста …​ ячейку C3 функция​ с которым Excel​ правильно ли работает​

​ подавить, добавив в​
​Когда формула не может​

​ Если нет –​=ЕСЛИ(ЕОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ));»»;ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ))​ беспокоясь о том,​ чем любая другая​​ ], далее указать​​, поскольку зрительно трудно​ВПР​

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

ВПР: работа с функцией ЕОШИБКА

​KuklP​​ вернула ошибку #ССЫЛКА!​​ не справиться.​ формула. Если вам​ формулу функции для​ найти значение, на​ то в ячейке​​На сегодня всё. Надеюсь,​​ что придётся обновлять​​ функция Excel. Из-за​​ имя листа, а​

​ увидеть эти лишние​.​
​ в этой области.​ синтаксисе нет двойных​

​ ошибку #ИМЯ?.​​ в ячейку M1​​: И зачем там​​ Так как над​​В ячейке А3 –​

​ нужно добавить обработчик​
​ обработки ошибок, такие​

​ которую указывает ссылка,​A3​ этот короткий учебник​ все связанные формулы​ этих ограничений, простые​​ затем – восклицательный​​ пробелы, особенно при​Как Вы, вероятно, знаете,​

​ Однако не без​ кавычек, появится сообщение​
​В следующем примере функция​
​ в зависимости о​

​ картинка? См.​

office-guru.ru

Ошибки в формулах Excel

​ ячейкой C3 может​ квадратный корень не​ ошибок, лучше сделать​ как ЕОШИБКА, ЕОШ​ возвращается ошибка #Н/Д.​будет вычислен результат​

Ошибка ;##

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

​ одно из самых​ причины многие специалисты​ об ошибке #ИМЯ.​ СУММ ссылается на​ того в каком​​ivanuch​​ быть только 2​ может быть с​ это тогда, когда​ или ЕСЛИОШИБКА.​ Дополнительные сведения см.​ формулы​ со всеми возможными​

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

Ошибка #ИМЯ?

​ ячейки а не​​ отрицательного числа, а​​ вы будете уверены,​Если имеется ссылка на​ в разделе Исправление​=A1/A2​ ошибками​ суть проблемы, правда?​

​ВПР​​ в апострофы, на​​ часть данных находится​​ВПР​​ВПР​

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

​В этом примере не​Прибыль​​ ячейки D1…​​KuklP​ 3 (как того​ программа отобразила данный​ что формула работает​

​ ячейку с ошибочным​ ошибки #Н/Д.​​.​​ВПР​

​Решение:​часто приводят к​ случай если имя​

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

​ за пределами экрана.​​это то, что​​одной из наиболее​ хватает кавычек до​, которое не определено​если значение >​

​, так у меня​ требовала исходная формула).​​ результат этой же​​ правильно.​ значением, функция ЕСЛИ​

​Эта ошибка возникает в​Сообщение об ошибке​и заставит Ваши​​Всегда используйте абсолютные​​ неожиданным результатам. Ниже​

​ книги или листа​​Решение 1: Лишние пробелы​​ она не может​ сложных функций. Она​​ и после слова​​ в книге.​ 1000, то пишем​ пишет #ИМЯ? вместо​​Примечание. В данном случае​​ ошибкой.​Примечание:​​ возвращает ошибку #ЗНАЧ!.​​ Excel, если формула​

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

​#ССЫЛКА!​​ формулы работать правильно.​​ ссылки на ячейки​ Вы найдёте решения​ содержит пробелы.​ в основной таблице​

1. ​ смотреть влево, следовательно,​​ имеет кучу ограничений​​имеет​Решение.​​ «текст1»,​​ ЛОЖЬ или ИСТИНА​​ наиболее удобнее под​​Значение недоступно: #Н/Д! –​
2. ​ Значения в вычислениях разделяются​​Решение​​ содержит недопустимую ссылку​(#REF!) говорит о​Урок подготовлен для Вас​ (с символом​ для нескольких распространённых​​Вот полная структура функции​​ (там, где функция​
3. ​ столбец поиска в​​ и особенностей, которые​​, поэтому выводится сообщение​​Определите имя в​​Если значение ячейки​Pelena​ каждым диапазоном перед​
4. ​ значит, что значение​ точкой с запятой.​: используйте с функцией​ на ячейку. Дополнительные​ том, что формула​ командой сайта office-guru.ru​​$​​ сценариев, когда​ВПР​ ВПР)​​ Вашей таблице должен​​ становятся источником многих​

​ об ошибке.​Диспетчере имен​
​ в приделах от​
​:​

​ началом ввода нажать​

office-guru.ru

Как исправить ошибки в формулах в Excel для Mac

​ является недоступным для​ Если разделить два​ ЕСЛИ функции для​ сведения см. в​ ссылается на ячейку,​Источник: https://www.ablebits.com/office-addins-blog/2014/08/27/excel-vlookup-not-working/​

ВПР

​) при записи диапазона,​ВПР​для поиска в​Если лишние пробелы оказались​ быть крайним левым.​ проблем и ошибок.​

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

​Решение.​, а затем добавьте​ >=500 и Если​ivanuch​ комбинацию горячих клавиш​ формулы:​

Ошибка #ИМЯ!

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

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

​Проверьте, нет ли​ его в формулу.​ значение ячейки в​, приложите файл Excel​ ALT+=. Тогда вставиться​​Записанная формула в B1:​​ ЕСЛИ будет рассматривать​ как ЕОШИБКА, ЕОШ​​ #ССЫЛКА!.​​В ячейке​Автор: Антон Андронов​$A$2:$C$100​Функция​=VLOOKUP(lookup_value,'[workbook name]sheet name’!table_array, col_index_num,FALSE)​

Ошибка #Н/Д

​ Вы можете обеспечить​ часто забываем об​ найдёте простые объяснения​ в формуле текстовых​ Вот как это​ приделах от >=60​ с неработающей формулой.​

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

​ функция суммирования и​ =ПОИСКПОЗ(„Максим”; A1:A4) ищет​ их как одно​ и ЕСЛИОШИБКА. В​Эта ошибка возникает, когда​C1​В этой статье мы​

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

​или​ВПР​=ВПР(искомое_значение;'[имя_книги]имя_листа’!таблица;номер_столбца;ЛОЖЬ)​ правильную работу формул,​ этом, что приводит​ ошибок​ значений без кавычек.​ сделать:​ и Если значение​

Ссылки на другие листы и книги

​ По фотографии не​ автоматически определит количество​ текстовое содержимое «Максим»​​ дробное значение. После​​ следующих разделах описывается,​ число делится на​содержатся ссылки на​ расскажем о том,​$A:$C​не различает регистр​Настоящая формула может выглядеть​​ заключив аргумент​​ к не работающей​

​#N/A​Если вы пропустили двоеточие​Если в электронной таблице​

• ​ ячейки в приделах​ лечим​

• ​ суммирующих ячеек.​ в диапазоне ячеек​

• ​ процентных множителей ставится​ как использовать функции​ ноль (0) или​ ячейки​ как справляться с​

  ​. В строке формул​​ и принимает символы​​ так:​

Другие простые способы устранения проблем

• ​lookup_value​ формуле и появлению​(#Н/Д),​​ в ссылке на​

• ​ уже есть данные​ от >=20 и​KuklP​​Так же ошибка #ССЫЛКА!​

• ​ A1:A4. Содержимое найдено​ символ %. Он​ ЕСЛИ, ЕОШИБКА, ЕОШ​ если формула ссылается​A1​​ некоторыми наиболее распространёнными​

• ​ Вы можете быстро​ нижнего и ВЕРХНЕГО​=VLOOKUP($A$2,'[New Prices.xls]Sheet1′!$B:$D,3,FALSE)​(искомое_значение) в функцию​ ошибки​#NAME?​ диапазон ячеек, будет​ и вы хотите​

• ​ Если значение ячейки​: Откройте фотошоп, исправьте.​ часто возникает при​​ во второй ячейке​

• ​ сообщает Excel, что​ и ЕСЛИОШИБКА в​ на пустую ячейку​​и​ ошибками формул в​​ переключать тип ссылки,​​ регистра как одинаковые.​

support.office.com

Исправление ошибки #ЗНАЧ! в функции ЕСЛИ

​=ВПР($A$2;'[New Prices.xls]Sheet1′!$B:$D;3;ЛОЖЬ)​TRIM​#Н/Д​(#ИМЯ?) и​ отображаться ошибка #ИМЯ?.​ назначить имя определенным​ в приделах от​Ну ладно картинка,​ неправильном указании имени​ A2. Следовательно, функция​ значение должно обрабатываться​ формуле, если аргумент​ или ячейку, содержащую​B1​ Excel.​ нажимая​ Поэтому, если в​Эта формула будет искать​

Проблема: аргумент ссылается на ошибочные значения.

​(СЖПРОБЕЛЫ):​.​#VALUE!​В приведенном ниже примере​

​ ячейкам или диапазону,​​ >0 и 50,​ но нафига ее​ листа в адресе​ возвращает результат 2.​ как процентное. В​ ссылается на ошибочные​ 0. Дополнительные сведения​.​Появление в ячейке такого​F4​ таблице есть несколько​ значение ячейки​

• ​=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)​Решение:​

• ​(#ЗНАЧ!), появляющихся при​ формула ИНДЕКС выдает​ сначала выделите ячейки​

​ тогда вставляем «текст5″​​ было пихать в​

• ​ трехмерных ссылок.​ Вторая формула ищет​ противном случае такие​ значения.​ см. в разделе​Удаляем столбец​ кода ошибки означает,​.​ элементов, которые различаются​A2​=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)​Если нет возможности​ работе с функцией​ ошибку #ИМЯ?, так​ в электронной таблице.​ и тд​

• ​ Эксель файл, где​#ЗНАЧ! – ошибка в​

Проблема: неправильный синтаксис.

​ текстовое содержимое «Андрей»,​ значения пришлось бы​Исправление ошибки #ЗНАЧ! в​

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

​ Если вы хотите​

​изначально думал что​ самой формулы с​​ значении. Если мы​ то диапазон A1:A4​ вводить как дробные​ функции СЦЕПИТЬ​Если вам нужно указать​. Для этого кликаем​ широк, чтобы отобразить​ пугать пользователей сообщениями​ функция ВПР возвратит​B​ в таблице поиска​ так, чтобы столбец​​, а также приёмы​

​ B2 — B12​ создать диапазон, можно​ в синтаксисе ошибка,​ ошибкой нет?У меня​ пытаемся сложить число​

​ не содержит таких​

​ множители, например «E2*0,25».​Исправление ошибки #ЗНАЧ! в​ ссылку на другой​ по заголовку столбца​ значение полностью.​ об ошибках​ первый попавшийся элемент,​на листе​ (в столбце поиска)​ поиска был крайним​ и способы борьбы​ нет двоеточия.​ пропустить этот шаг.​ но вроде бы​ в 2003 не​ и слово в​ значений. Поэтому функция​Задать вопрос на форуме​ функции СРЗНАЧ или​ лист, введите​ правой кнопкой и​Наведите указатель мыши на​#Н/Д​ не взирая на​

​Sheet1​​Если лишние пробелы оказались​ левым, Вы можете​ с ними. Мы​Решение.​На вкладке​ делаю все верно​ выдает. По картинке​ Excel в результате​ возвращает ошибку #Н/Д​ сообщества, посвященном Excel​ СУММ​!​ в контекстном меню​ правую границу столбца​,​ регистр.​

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

​в рабочей книге​ в столбце поиска​

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

​ использовать комбинацию функций​ начнём с наиболее​Убедитесь, что все​Формулы​ по этому примеру​ лечить не умею.​ мы получим ошибку​

support.office.com

Как убрать ошибки в ячейках Excel

​ (нет данных).​У вас есть предложения​Примечания:​после его имени,​ нажимаем​ рядом с его​#ЗНАЧ!​Решение:​

Ошибки в формуле Excel отображаемые в ячейках

​New Prices​ – простыми путями​ИНДЕКС​ частых случаев и​ ссылки на диапазон​в группе​=ЕСЛИ(А1=100;»Всегда»;ЕСЛИ(И(А1>=80;А1=60;А1 версия офиса​ Может корявая установка​ #ЗНАЧ! Интересен тот​Относиться к категории ошибки​ по улучшению следующей​ ​

Как убрать #ДЕЛ/0 в Excel

​ а затем — координаты​Удалить​ заголовком (в нашем​или​Используйте другую функцию​и извлекать соответствующее​ ошибку​(INDEX) и​ наиболее очевидных причин,​

​ включают двоеточие.​Определенные имена​ 2013​

​ Экса?​ факт, что если​ в написании функций.​ версии Excel? Если​

​Функция ЕСЛИОШИБКА появилась в​

Результат ошибочного вычисления – #ЧИСЛО!

​ ячейки или диапазона​(Delete).​ примере это столбец​#ИМЯ?​

​ Excel, которая может​

​ значение из столбца​#Н/Д​ПОИСКПОЗ​ почему​Некоторые функции Excel работают​нажмите кнопку​при попытке написания​ivanuch​ бы мы попытались​

​ Недопустимое имя: #ИМЯ!​ да, ознакомьтесь с​ Excel 2007. Она​ ячеек. Если имя​Выделите ячейку​

​A​, можете показывать пустую​ выполнить вертикальный поиск​D​в формуле с​(MATCH), как более​ВПР​ только тогда, когда​Присвоить имя​ формулы по примеру​

​: …​ сложить две ячейки,​ – значит, что​ темами на портале​ гораздо предпочтительнее функций​ листа содержит пробелы,​B1​

Как убрать НД в Excel

​), чтобы указатель принял​ ячейку или собственное​ (ПРОСМОТР, СУММПРОИЗВ, ИНДЕКС​.​

​ВПР​ гибкую альтернативу для​не работает, поэтому​ включена соответствующая надстройка.​и выберите команду​ выше, так же​_Boroda_​ в которых значение​ Excel не распознал​ пользовательских предложений для​ ЕОШИБКА и ЕОШ,​ заключите его в​. Ссылка на ячейку​ вид, как на​ сообщение. Вы можете​

Ошибка #ИМЯ! в Excel

​ и ПОИСКПОЗ) в​Если любая часть пути​не избежать. Вместо​ВПР​ лучше изучать примеры​ При использовании их​Присвоить имя​ выдает «#имя?»​: В ячейке А4​ первой число, а​ текста написанного в​ Excel.​ так как не​

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

​ апострофы. Например:​B1​ рисунке ниже. Нажмите​ сделать это, поместив​ сочетании с​ к таблице пропущена,​ВПР​.​ в том порядке,​ без надстройки будет​.​P.S. Проблема решилась, ничего​ формула, там вот​ второй – текст​ формуле (название функции​При ошибочных вычислениях, формулы​ требует избыточности при​=СУММ(‘отчет о продажах’!A1:A8)​в формуле превратилась​ левую кнопку мыши​

​ВПР​СОВПАД​ Ваша функция​Вы можете использовать​

​Другой источник ошибки​ в каком они​ выводится ошибка #ИМЯ?.​Введите уникальное имя.​ не делал, заново​ эта часть​

#ССЫЛКА! – ошибка ссылок на ячейки Excel

​ с помощью функции​ =СУМ() ему неизвестно,​ отображают несколько типов​ построении формулы. При​.​ в ссылку на​

​ и перетащите границу​в функцию​, которая различает регистр.​ВПР​ формулу массива с​#Н/Д​

​ приведены в статье.​ Например, чтобы использовать​В качестве​ прописал точно такую​МЕДВЕШЧАКСИМВ(A3;1)=».»​ =СУММ(), то ошибки​ оно написано с​ ошибок вместо значений.​ использовании функций ЕОШИБКА​Если вы указываете ссылку​ несуществующую ячейку.​ столбца до нужной​

​ЕСЛИОШИБКА​ Более подробно Вы​не будет работать​ комбинацией функций​в формулах с​Исправляем ошибку #Н/Д​ функцию ПЕРЕСЧЕТЕВРО, нужно​области​ же формулу 1​что такое​ не возникнет, а​

​ ошибкой). Это результат​ Рассмотрим их на​ и ЕОШ формула​ на другую внешнюю​Чтобы исправить эту ошибку,​ ширины.​(IFERROR) в Excel​ можете узнать из​ и сообщит об​

​ИНДЕКС​ВПР​Исправляем ошибку #ЗНАЧ! в​ включить надстройку​выберите лист или​

Как исправить ЗНАЧ в Excel

​ в 1 +​МЕДВЕШЧАКСИМВ?​ текст примет значение​ ошибки синтаксиса при​ практических примерах в​ вычисляется дважды: сначала​ книгу,​ нужно либо удалить​Совет:​ 2013, 2010 и​ урока — 4​ ошибке​(INDEX),​– это числа​ формулах с ВПР​Инструменты для евро​ всю книгу.​ перегрузил ПК, заработало.​Нет такой функции,​

Решетки в ячейке Excel

​ 0 при вычислении.​ написании имени функции.​ процессе работы формул,​ проверяется наличие ошибок,​заключите имя книги в​ несуществующую ссылку в​Если дважды кликнуть​ 2007 или использовать​ способа сделать ВПР​#ЗНАЧ!​ПОИСКПОЗ​ в текстовом формате​Ошибка #ИМЯ? в ВПР​. Если вы применяете​При желании введите примечание.​ :)​

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

​(даже если рабочая​(MATCH) и​

​ в основной таблице​ВПР не работает (проблемы,​ пользовательские функции и​Нажмите кнопку​

exceltable.com

Формула Если вместо значения видает #имя? (Формулы)

​=ЕСЛИ(D4>=1000;»текст1″;ЕСЛИ(И(D4>=500;D4=60;D4=20;D40;D4 Это просто​​ivanuch​Ряд решеток вместо значения​Пустое множество: #ПУСТО! –​
​ результаты вычислений.​ результат. При использовании​Затем введите полный путь​ действие, кликнув по​
​A​

​ЕСЛИ+ЕОШИБКА​​ в Excel.​ книга с таблицей​СЖПРОБЕЛЫ​​ или в таблице​​ ограничения и решения)​ макросы, для которых​ОК​
​ машине не нравится,​

​:​​ ячейки ;; –​ это ошибки оператора​В данном уроке будут​

​ функции ЕСЛИОШИБКА формула​​ к файлу.​

​ иконке​​рядом с его​

​(IF+ISERROR) в более​​Как Вы уже знаете,​ поиска в данный​

​(TRIM):​​ поиска.​​ВПР – работа с​​ требуется пакет анализа,​.​ что в формуле​

​_Boroda_​​ данное значение не​​ пересечения множеств. В​​ описаны значения ошибок​ вычисляется только один​Заключите его в апострофы​Отменить​

​ заголовком, то ширина​​ ранних версиях.​​ВПР​ момент открыта).​=INDEX($C$2:$C$10,MATCH(TRUE,TRIM($A$2:$A$10)=TRIM($F$2),0))​Это обычно случается, когда​ функциями ЕСЛИОШИБКА и​ убедитесь, что надстройка​Далее нужно добавить имя​ такая куча лишнего.​, к сожалению не​ является ошибкой. Просто​ Excel существует такое​

​ формул, которые могут​​ раз.​

​ (в начале пути​​(Undo) на панели​ столбца автоматически изменится​Синтаксис функции​
​возвращает из заданного​
​Для получения дополнительной информации​
​=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(ИСТИНА;СЖПРОБЕЛЫ($A$2:$A$10)=СЖПРОБЕЛЫ($F$2);0))​
​ Вы импортируете информацию​ ЕОШИБКА​

​Пакет анализа​​ в формулу.​​ Где Вы взяли​​ нахожу я такого​ это информация о​ понятие как пересечение​ содержать ячейки. Зная​Конструкция =ЕСЛИОШИБКА(Формула;0) гораздо лучше​ и после имени​ быстрого доступа (или​ и будет соответствовать​ЕСЛИОШИБКА​ столбца значение, соответствующее​ о функции​

​Так как это формула​​ из внешних баз​В формулах с​включена.​Курсор должен быть в​
​ «этот пример»?​ … и к​​ том, что ширина​​ множеств. Оно применяется​ значение каждого кода​

​ конструкции =ЕСЛИ(ЕОШИБКА(Формула;0;Формула)).​​ листа перед восклицательным​ нажать​
​ самой широкой ячейке​(IFERROR) прост и​ первому найденному совпадению​ВПР​ массива, не забудьте​ данных или когда​ВПР​Чтобы включить надстройку в​

​ том месте формулы,​​И что Вы​ тому же вопрос​ столбца слишком узкая​ для быстрого получения​ (например: #ЗНАЧ!, #ДЕЛ/0!,​

​Если синтаксис функции составлен​ знаком).​Ctrl+Z​ столбца.​

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

​ Excel:​​ куда вы хотите​ нарушаете Правила форума​
​ был по #ИМЯ?​ для того, чтобы​
​ данных из больших​ #ЧИСЛО!, #Н/Д!, #ИМЯ!,​ неправильно, она может​Например:​).​Ошибка​ себя:​ Вы можете заставить​
​ файл Excel, обратитесь​Ctrl+Shift+Enter​ числом, чтобы сохранить​
​#N/A​Щелкните​ добавить созданное имя.​ не менее, чем​ в ячейках B4,​ вместить корректно отображаемое​ таблиц по запросу​ #ПУСТО!, #ССЫЛКА!) можно​ вернуть ошибку #ЗНАЧ!.​=СУММ(‘/Пользователи/ваше_имя/Рабочий_стол/[Q2 Operations.xlsx]Продажи’!A1:A8)​Урок подготовлен для Вас​#ИМЯ?​IFERROR(value,value_if_error)​
​ ее извлечь 2-е,​ к уроку: Поиск​вместо привычного​ стоящий в начале​(#Н/Д) – означает​
​Файл​На вкладке​
​ в 3-х пунктах.​ B5 … и​ содержимое ячейки. Нужно​ точки пересечения вертикального​
​ легко разобраться, как​Решение​.​ командой сайта office-guru.ru​(#NAME?) возникает в​ЕСЛИОШИБКА(значение;значение_если_ошибка)​ 3-е, 4-е или​
​ в другой рабочей​Enter​ ноль.​not available​ >​Формулы​
​Эта тема закрыта.​ как я заметил​ просто расширить столбец.​ и горизонтального диапазона​
​ найти ошибку в​

excelworld.ru

​: проверьте правильность синтаксиса.​

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

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

Функция ПОИСКПОЗ Excel — пошаговая инструкция

Функция ПОИСКПОЗ в Excel ищет указанное значение в массиве и возвращает относительное положение этого значения.

Рассмотрим пошагово, как составить формулу ПОИСКПОЗ:

ПОИСКПОЗ(искомое_значение; массив_поиска, [тип_совпадения])

Шаг 1. Искомое_значение (обязательный аргумент) — значение, которое вы хотите найти. Это может быть число, текстовое или логическое значение, а также ссылка на ячейку.

Шаг 2. Массив_поиска (обязательно) — диапазон ячеек для поиска.

Шаг 3. Указываем Тип_совпадения (необязательно) — определяет тип соответствия. Это может быть одно из следующих значений: 1, 0, -1. Аргумент, установленный в 0, возвращает только точное совпадение, в то время как два других значения допускают приблизительное совпадение.

• 1 или опущено (по умолчанию) — найти наибольшее значение в массиве поиска, которое меньше или равно значению поиска. Требуется сортировка массива поиска в порядке возрастания, от меньшего к большему или от А до Я.
• 0 — найти первое значение в массиве, точно равное искомому значению. Никакой сортировки не требуется.
• -1 — найти наименьшее значение в массиве, которое больше или равно искомому значению. Массив поиска должен быть отсортирован в порядке убывания, от наибольшего к наименьшему или от Я до A.

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

=ПОИСКПОЗ(“Елена”; A2:A8;0)

При желании вы можете поместить искомое значение в какую-либо ячейку (E1 в этом примере) и сослаться на эту ячейку в своей формуле:

=ПОИСКПОЗ(E1; A2:A8;0)

Как вы видите на скриншоте выше, имена расположены не по алфавиту, а потому мы устанавливаем аргументу тип_совпадения значение 0 (точное соответствие). Ведь только этот вариант не требует сортировки в массиве поиска. 

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

4 вещи, которые вы должны знать о функции ПОИСКПОЗ

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

1. Функция ПОИСКПОЗ возвращает относительное положение искомого значения в массиве, а не само значение.
2. ПОИСКПОЗ нечувствительна к регистру , то есть не различает строчные и прописные символы при работе с текстовыми значениями.
3. Если искомый массив содержит несколько вхождений искомого значения, то возвращается позиция первого найденного значения.
4. Если искомое значение не найдено в массиве поиска, возвращается ошибка #Н/Д.

Как использовать ПОИСКПОЗ в Excel — примеры формул

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

Частичное совпадение с подстановочными знаками

Как и многие другие функции, ПОИСКПОЗ понимает следующие подстановочные знаки :

• Вопросительный знак (?) — заменяет любой одиночный символ
• Звездочка (*) — заменяет любую последовательность символов

Примечание. Подстановочные знаки можно использовать только тогда, если для параметра тип_совпадения установлено значение 0.

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

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

Предположим, что наименования товаров находятся в диапазоне A2:A11, и вы ищете имя, начинающееся с «доб». Формула выглядит следующим образом:

=ПОИСКПОЗ(«доб*»; A2:A11;0)

Чтобы сделать нашу формулу более универсальной, вы можете ввести искомое значение в какую-либо ячейку (E1 в этом примере) и соединить эту ячейку с подстановочным знаком, например:

=ПОИСКПОЗ(E1&»*»; A2:A11;0)

Как показано на скриншоте ниже, формула возвращает 4, что является позицией «Добрый экзотик»:

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

Чтобы заменить только один символ в искомом значении, используйте подстановочный знак «?».

Формула ПОИСКПОЗ с учетом регистра

Как упоминалось ранее, функция ПОИСКПОЗ не различает символы верхнего и нижнего регистра. 

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

Вот общая формула с учетом регистра для сопоставления данных:

=ПОИСКПОЗ(ИСТИНА;СОВПАД(искомый массив ; искомое значение );0)

Формула работает по следующей логике:

• Функция СОВПАД (EXACT в английской версии) сравнивает значение с каждым элементом массива поиска. Если сравниваемые ячейки точно равны, функция возвращает значение ИСТИНА, в противном случае — ЛОЖЬ.
• Затем функция ПОИСКПОЗ сравнивает ИСТИНА (которое является ее искомым_значением ) с каждым значением в массиве, возвращаемом функцией СОВПАД, и возвращает позицию первого совпадения.

Пожалуйста, имейте в виду, что это формула массива , которая требует нажатия Ctrl + Shift + Enter при завершении ее ввода.

Предполагая, что искомое значение находится в ячейке E1, а массив поиска — A2:A9, формула выглядит следующим образом:

=ПОИСКПОЗ(ИСТИНА;СОВПАД(A2:A9;E1);0)

На скриншоте показана формула поиска соответствия с учетом регистра в Excel:

Как видите, формула различает регистр букв и по этой причине «А-201» было признано неподходящим.

ПОИСКПОЗ и несколько условий

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

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

Вот формула ПОИСКПОЗ для нескольких условий:

=ПОИСКПОЗ(1;(B2:B12=G1)*(C2:C12=G2);0)

Давайте разбираться.

Наши два условия мы записываем в виде выражения (B2:B12=G1)*(C2:C12=G2). Первое условие (B2:B12=G1) означает, что мы сравниваем каждое из значений в столбце «Регион» с целью «Север», которая записана в G1. Получаем массив {ЛОЖЬ:ИСТИНА:ИСТИНА:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ}

Аналогично каждый из товаров сравниваем с «Яблоки» из G2. Аналогично получаем результат {ИСТИНА:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ИСТИНА}.

После перемножения этих массивов получаем матрицу единиц и нулей: {0:0:0:1:0:0:0:0:1:0:0}

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

И вот в этом массиве функция ПОИСКПОЗ ищет первую единицу. И находит ее в 4-й позиции.

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

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

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

Как сравнить столбцы при помощи ПОИСКПОЗ

Проверка двух списков на совпадения и различия — одна из наиболее распространенных задач в Excel, и ее можно выполнить различными способами. Формула ЕНД/ПОИСКПОЗ является одним из них:

ЕСЛИ(ЕНД(ПОИСКПОЗ( 1-е значение в Списке1 , Списке2 , 0)), «Нет в Списке 1», «»)

Для любого значения списка 2, которого нет в списке 1, формула возвращает « Нет в списке 1 ». 

Рассмотрим пошагово:

• Функция ПОИСКПОЗ ищет значение из списка 1 в списке 2. Если значение найдено, оно возвращает его относительное положение, в противном случае получается ошибка #Н/Д.
• Функция ЕНД в Excel выполняет только одну работу — проверяет наличие ошибок #Н/Д (что означает «недоступно»). Если обрабатываемое ею значение является ошибкой #Н/Д, функция возвращает значение ИСТИНА, в противном случае – ЛОЖЬ. В нашем случае ИСТИНА означает, что значение из списка 1 не найдено в списке 2 (т. е. функция ПОИСКПОЗ возвращает ошибку).
• Поскольку вам может быть не слишком понятным видеть ИСТИНА для значений, которые не отображаются в списке 1, вы оборачиваете функцию ЕСЛИ вокруг ЕНД, чтобы вместо ИСТИНА отображалось « Нет в списке 1 » или любой другой текст, который вы хотите.

Например, чтобы сравнить значения в столбце B со значениями в столбце A, формула может быть записана так (где B2 — самая верхняя ячейка):

=ЕСЛИ(ЕНД(ПОИСКПОЗ(B2;A:A;0)); «Нет в списке 1»; «»)

Как вы помните, функция ПОИСКПОЗ в Excel сама по себе нечувствительна к регистру. Чтобы она различала регистр символов, вставьте функцию СОВПАД (EXACT по английски) в аргумент массив_поиска и не забудьте нажать Ctrl + Shift + Enter , чтобы ввести эту формулу массива :

{=ЕСЛИ(ЕНД(ПОИСКПОЗ(ИСТИНА; СОВПАД(A:A; B2);0)); «Нет в списке 1»; «»)}

На следующем рисунке показаны обе формулы в действии:

Быть может, вам также будет интересен этот материал: 5 способов сравнения ячеек в Excel.

Совместное использование ВПР и ПОИСКПОЗ

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

Один из самых досадных недостатков ВПР заключается в том, что она перестает корректно работать после вставки или удаления столбца в таблице поиска. Это происходит потому, что функция ВПР извлекает совпадающее значение на основе указанного вами номера возвращаемого столбца (номер индекса). Поскольку номер индекса «жестко запрограммирован» в формуле, Excel не может изменить его, когда в таблицу добавляются или удаляются новые столбцы.

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

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

Предполагая, что искомое значение находится в ячейке F1, значения таблицы расположены в $A$1:$C$8 (рекомендуется зафиксировать его с помощью абсолютных ссылок на ячейки, если вы планируете копировать формулу), выражение выглядит следующим образом:

=ВПР($G$1;$A$1:$С$8; 2; ЛОЖЬ)

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

Но только до тех пор, пока вы не вставите или не удалите какие-то столбцы:

Как видите, если мы вставляем дополнительный столбец, то формула теперь извлекает неверные данные. Она по-прежнему ссылается на второй столбец, в то время как данные Тест 1 сместились в третий.

Чтобы предотвратить подобные вещи, вы можете сделать свою формулу ВПР более гибкой, включив в нее функцию ПОИСКПОЗ:

ПОИСКПОЗ($F$2;$A$1:$С$1;0)

Где:

• F2 — это искомое значение, которое в точности совпадает с именем возвращаемого столбца, т. е. столбца, из которого вы хотите извлечь значение ( Тест 1  в этом примере).
• A1:C1 — массив поиска, содержащий заголовки таблицы.

А теперь запишите эту формулу в аргумент номер_столбца вашей формулы ВПР, например:

=ВПР($G$1;$A$1:$D$8; ПОИСКПОЗ($F$2;$A$1:$С$1;0); ЛОЖЬ)

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

Вставляем столбец с итоговым тестом:

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

=ВПР($G$1;$A$1:$D$8; ПОИСКПОЗ($F$2;$A$1:$D$1;0); ЛОЖЬ)

Использование ГПР и ПОИСКПОЗ

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

Предположим, что искомое значение находится в ячейке B5, данные таблицы — B1:H3, имя возвращаемой строки (значение поиска для ПОИСКПОЗ) — в ячейке A6, а заголовки строк — A1:A3.

Тогда формула выглядит следующим образом:

=ГПР(B5;B1:H3;ПОИСКПОЗ(A6; A1:A3;0);ЛОЖЬ)

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

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

Подробное руководство и примеры формул можно найти в ИНДЕКС и ПОИСКПОЗ в Excel — лучшей альтернативе ВПР .

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