Как считать однотипные ошибки в впр

1. ВПР в начальной школе: методика проверки заданий по русскому языку

Шило Т.Б., к.п.н, доцент,
доцент кафедры НОО
ГАОУ ДПО «ЛОИРО»

2. Всероссийская проверочная работа

• Назначение ВПР по русскому языку – оценить уровень
общеобразовательной подготовки обучающихся 4 класса в
соответствии с требованиями ФГОС, осуществить диагностику
достижения предметных, метапредметных результатов и уровня
сформированности универсальных учебных действий.
• Результаты ВПР могут быть использованы образовательными
организациями для совершенствования методики преподавания
русского языка в начальной школе, муниципальными и
региональными органами исполнительной власти, осуществляющими
государственное управление в сфере образования, для анализа
текущего состояния муниципальных и региональных систем
образования и формирования программ их развития.
• Сопровождение проведения ВПР осуществляется посредством
сайта Федерального института оценки качества образования –
https:// fioco.ru /

3. Структура варианта проверочной работы

• Вариант проверочной работы состоит из двух частей,
которые выполняются в разные дни и различаются по
содержанию и количеству заданий.
• Время выполнения: На выполнение работы по русскому
языку даётся 90 минут. На выполнение заданий каждой
части отводится 45 минут. Время инструктажа не входит во
время выполнения работы.
• Часть 1 содержит 3 задания: диктант (задание 1) и 2
задания по написанному тексту.
• Часть 2 содержит 12 заданий, из них 9 (6-14) заданий к
приведенному в варианте проверочной работы тексту для
чтения и 1 (15) задания с развернутым ответом.

4. Критериальное оценивание заданий части I проверочной работы

5. Структура части I проверочной работы

• 1. Запиши текст под диктовку.
— проверяет традиционное базовое умение обучающихся правильно писать
текст под диктовку, соблюдая при письме изученные орфографические и
пунктуационные нормы.
• 2. Найди в тексте предложение с однородными сказуемыми
(подлежащими). Выпиши это предложение и подчеркни в нём однородные
сказуемые (подлежащие).
— проверяет умение распознавать и подчеркивать однородные члены в
предложении.
• 3. Выпиши из текста 10-е предложение.
1) Подчеркни в нём главные члены.
– умение распознавать и графически обозначать главные члены
предложения.
2) Над каждым словом напиши, какой частью речи оно является.
– умение распознавать изученные части речи в предложении.

6. Особенности текстов для диктанта

• Художественные и научно-популярные тексты
• Наличие предложений различных по цели высказывания:
восклицательных, повествовательных и вопросительных предложений
(Какая звезда обитает в море? Лучше не нарисуешь! В лесу много грибов. )
• Наличие имен в текстах (Серёжа, Женя, Олег)
• Применение:
— простых нераспространенных предложений (Наступила весна. Середина
лета.)
— односоставных предложений (Похолодало.)
— предложений, осложненных однородными членами (Морозный воздух
иголочками колет щёки и уши.)
• Наличие в текстах:
— наречий (лучше, быстро, дружно, трудно, часто)
— числительных (пятью, сорок)
• Использование терминологии (пересмешники, подосиновики,
подберезовики)

7. Примерные программы по учебным предметам: Русский язык

8. Система оценивания проверочной работы

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

9. К1. Соблюдение орфографических норм

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

10. Частные орфографические нормы

1. Повторные ошибки в одном и том же слове считаются как одна
ошибка (например, если ученик дважды написал в слове «песок» вместо
«е» букву «и»);
2. Две негрубые ошибки считаются за одну ошибку (если в тексте
несколько раз повторяется слово, и в нём допущена одна и та же ошибка,
она считается как одна);
Негрубыми считаются следующие ошибки:
повторение одной и той же буквы в слове;
перенос, при котором часть слова написана на одной строке, а на другой
опущена;
дважды написанное одно и то же слово.
3. Ошибки на одно и то же правило, допущенные в разных словах,
считаются как разные ошибки (например, написание буквы «т» вместо
«д» в слове «лошадка» и буквы «с» вместо «з» в слове «повозка»);
4. При трёх поправках оценка снижается на один балл.

11. Ошибками в диктанте не считаются:

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

12.

1. Учитываются ли дисграфические (замена букв,
пропуск букв в словах) ошибки при оценивании
диктанта в 4 классе у обучающихся с ОВЗ?
1. ВПР проводится по общеобразовательной
программе. Вам необходимо оценить работу по
критериям, выложенным в личном кабинете.
2. В предложении «Так они и останутся
зелёными.» пропущен союз «И». Больше ошибок и
исправлений в тексте нет, работа написана
аккуратно. Считается ли пропуск союза ошибкой
или это непреднамеренный пропуск?
2. Пропуск союза в данном случае не является
орфографически и пунктуационно значимым и
поэтому не влияет на оценивание.
3. Ученик несколько раз в диктанте написал слово 3. Это повторяющиеся в одном и том же слове
снИгЕря (ей). За сколько ошибок считать?
ошибки; засчитываем 2 орфографические ошибки.
4. Считать ли ошибку в слове
прЕвыкают?
4. Написания приставок пре/при в начальной школе не
изучаются, поэтому ошибки на эти орфограммы не
учитываются. При диктовке учитель может орфографически
проговаривать слова с такими написаниями.
5. Ученик, подчеркивая однородные члены
предложения, не подчеркнул частицу НЕ.
Как оценивать?
5. Можно оценить данный ответ как правильный.

13.

6. Последнее предложение (Скорее домой!)
ученик записал в кавычках (» Скорее домой!»)
Как оценивать?
6. В данном случае неоправданную постановку
парных кавычек можно считать за одну
пунктуационную ошибку.
7. Ребёнок подчеркнул правильно однородные
подлежащие, но подчеркнул и сказуемое (без
ошибок). Снижать балл за лишние выполненные
задания?
7. В соответствии с критериями балл в данном
случае не снижается.
8. Оценивать ли задание № 3 по русскому языку в 4
классе, если предложение выписано не то, а части
речи указаны правильно?
8. Задание с неверно выписанным
предложением не оценивается: 0 баллов.
9. В предложении «Могучий дуб лёгким золотом 9. Это грамматическая ошибка, она не учитывается
покрыла.», в глаголе покрыла (ученики написали при подсчете баллов, так как в тексте диктанта,
покрылО). Считается ли это ошибкой?
допустить ее практически невозможно. Видимо,
коллективная ошибка связана с погрешностями
диктовки.
10. Считать ли за ошибку, если ребёнок
правильно нашёл однородные члены, но при
этом сверху подписал части речи?
10. Нет, это не является ошибкой. Основное задание
выполнено.

14.

11. В задании № 3 в ВПР по русскому языку в 4 классе кроме 11. Задание считается выполненным.
главных членов предложения ученик подчеркнул и
второстепенные. Считать ли задание выполненным верно?
12. ВПР 4 класс русский язык. Ребёнок написал
слово ОРАНЖЕВЫЕ так: «ораньджывые».
Сколько ошибок засчитывать?
13. Выписано не то предложение, но правильно
подчёркнуты главные члены предложения, верно
обозначены части речи всех слов.
12. Орфографические и пунктуационные ошибки на
не изучаемые в начальной школе правила не
учитываются.
13. Задание с неверно выписанным предложением
не оценивается: 0 баллов.
14. В предложении учащиеся вместо восклицательного
знака поставили точку. Нужно ли считать это
пунктуационной ошибкой и снимать балл?
15. Союз ИЛИ — дети не изучали.
Засчитывать за ошибку или нет?
14. Да, это пунктуационная ошибка в выборе
знака завершения.
15. Ошибки в данном случае не будет, т.к. данный
союз не в курсе начальной школы.
16. Написание –тся и –ться в глаголах; буквы -е, -и в
окончаниях глаголов 1 и 2 спряжения не изучались в
классе. Правописание суффиксов наречий «приятнА»,
«обычнА» + числительных «сорАк»?
16. Ошибки в данном случае не будет

15. Критериальное оценивание заданий части II проверочной работы

16. Структура части II проверочной работы

Представлены в виде заданий открытого типа (с кратким ответом или с
развернутым ответом).
Текст с заданиями
Задание 4 направлено на проверку умения распознавать правильную
орфоэпическую норму
Задание 5 проверяет умение классифицировать согласные звуки в результате
частичного фонетического анализа
Задания 6-14 проверяют умения обучающихся работать с текстовой
информацией заданной в письменном виде
Задания 15 проверяет умения обучающихся:
— адекватное понимание учащимися письменно предъявленной информации;
— умение на основе данной информации и собственного жизненного опыта
адекватно интерпретировать текст-пословицу;
— умение строить связное речевое высказывание в письменной форме.

17.

1. Задание 9. Лексическое значение
слова «гербарий» ребенок указывает
«букет», «книга», «атлас» засушенных
растений.
2. Задание 10. Ребенок синонимом к слову
«гибкий» выбрал словосочетание «легко
гнется». Как оценивать?
3. При выполнении 11 задания ребенок
выбрал и разобрал по составу слово
«крепкий», а в критериях предлагается
разобрать слово «дубки».
Но ведь у этих слов одинаковый состав.
Как оценивать?
1. Лексическое значение слова в:
— «Энциклопедическом словаре» – это собрание
высушенных растений, расположенных между листами
бумаги.
— «Энциклопедии биологии» — это коллекция специально
засушенных растений.
Основа для лексического значения — засушенные растения,
поэтому «букет» — будет частичная ошибка.
2. «Легко гнется» не является синонимом слова
«гибкий».
3. — В «Морфемно-словообразовательном словаре»
И.В.Гурковой для 1-4 кл., рекомендованном Институтом
русского языка им В.В. Виноградова
— В «Морфемно-орфографическом словаре русского
языка» А.Н.Тихонова — в слове «крепкий» выделяется корень крепк-.

18.

4. Задание 12. Как оценить ответ ребёнка, если
он выписал слово «время» и определил у него
следующие грамматические признаки: ср.р., 2
скл., ед.ч., В.п.?
5. В задании 12 ученик выписал имя
существительное без предлога. Как оценивать?
4. Если ученики наряду с обозначенными в ключе
именами существительными дополнительно
указали существительное «время», их ответ
оценивается 1 баллом.
5. При общем верном выполнении этой части
задания единичный недочет не учитывается.
6. Считается ли ошибкой, если ребёнок в задании 13
выписал прилагательное без существительного.
6. Да, это ошибка.
7. В задании 13 выписаны два имени
прилагательных с существительными — правильно.
Указали число, род, падеж, но не на выбор, а в
обоих случаях. Но в словосочетании «Разных стран»
определены МН.Ч, Ж.Р., Р.п. Считать за ошибку во
мн.ч. указанный род?
7. Если ученик вместо одного имени
прилагательного разобрал два и разбор одного слова
выполнил безошибочно, а в разборе другого ошибся,
засчитайте в качестве оцениваемого безошибочный
вариант в пользу ученика.
8. Задание 15. Ребенок начинает писать
предложение с заглавной буквы, в конце
предложения не ставит точку. Как оценивать?
8. Если ребенок начитает с заглавной буквы за
ошибку можно не считать, а отсутствие точки в
конце предложения – ошибка.

19. Адрес в контакте

Современная начальная школа: Инновации https://vk.com/club156651489

20. Спасибо за внимание!

Этот урок объясняет, как быстро справиться с ситуацией, когда функция ВПР (VLOOKUP) не хочет работать в Excel 2013, 2010, 2007 и 2003, а также, как выявить и исправить распространённые ошибки и преодолеть ограничения ВПР.

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

В этой статье Вы найдёте простые объяснения ошибок #N/A (#Н/Д), #NAME? (#ИМЯ?) и #VALUE! (#ЗНАЧ!), появляющихся при работе с функцией ВПР, а также приёмы и способы борьбы с ними. Мы начнём с наиболее частых случаев и наиболее очевидных причин, почему ВПР не работает, поэтому лучше изучать примеры в том порядке, в каком они приведены в статье.

• Исправляем ошибку #Н/Д
• Исправляем ошибку #ЗНАЧ! в формулах с ВПР
• Ошибка #ИМЯ? в ВПР
• ВПР не работает (проблемы, ограничения и решения)
• ВПР – работа с функциями ЕСЛИОШИБКА и ЕОШИБКА

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

В формулах с ВПР сообщение об ошибке #N/A (#Н/Д) – означает not available (нет данных) – появляется, когда Excel не может найти искомое значение. Это может произойти по нескольким причинам.

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

Хорошая мысль проверить этот пункт в первую очередь! Опечатки часто возникают, когда Вы работаете с очень большими объёмами данных, состоящих из тысяч строк, или когда искомое значение вписано в формулу.

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

Если Вы используете формулу с условием поиска приближённого совпадения, т.е. аргумент range_lookup (интервальный_просмотр) равен TRUE (ИСТИНА) или не указан, Ваша формула может сообщить об ошибке #Н/Д в двух случаях:

• Искомое значение меньше наименьшего значения в просматриваемом массиве.
• Столбец поиска не упорядочен по возрастанию.

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

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

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

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

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

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

Другой источник ошибки #Н/Д в формулах с ВПР – это числа в текстовом формате в основной таблице или в таблице поиска.

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

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

Кроме этого, числа могут быть сохранены в формате General (Общий). В таком случае есть только один заметный признак – числа выровнены по левому краю ячейки, в то время как стандартно они выравниваются по правому краю.

Решение: Если это одиночное значение, просто кликните по иконке ошибки и выберите Convert to Number (Конвертировать в число) из контекстного меню.

Если такая ситуация со многими числами, выделите их и щелкните по выделенной области правой кнопкой мыши. В появившемся контекстном меню выберите Format Cells (Формат ячеек) > вкладка Number (Число) > формат Number (Числовой) и нажмите ОК.

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

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

Решение 1: Лишние пробелы в основной таблице (там, где функция ВПР)

Если лишние пробелы оказались в основной таблице, Вы можете обеспечить правильную работу формул, заключив аргумент lookup_value (искомое_значение) в функцию TRIM (СЖПРОБЕЛЫ):

=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)

Решение 2: Лишние пробелы в таблице поиска (в столбце поиска)

Если лишние пробелы оказались в столбце поиска – простыми путями ошибку #Н/Д в формуле с ВПР не избежать. Вместо ВПР Вы можете использовать формулу массива с комбинацией функций ИНДЕКС (INDEX), ПОИСКПОЗ (MATCH) и СЖПРОБЕЛЫ (TRIM):

=INDEX($C$2:$C$10,MATCH(TRUE,TRIM($A$2:$A$10)=TRIM($F$2),0))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(ИСТИНА;СЖПРОБЕЛЫ($A$2:$A$10)=СЖПРОБЕЛЫ($F$2);0))

Так как это формула массива, не забудьте нажать Ctrl+Shift+Enter вместо привычного Enter, чтобы правильно ввести формулу.

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

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

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

Будьте внимательны: функция ВПР не может искать значения, содержащие более 255 символов. Если искомое значение превышает этот предел, то Вы получите сообщение об ошибке #ЗНАЧ!.

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

=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))
=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))

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

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

Вот полная структура функции ВПР для поиска в другой книге:

=VLOOKUP(lookup_value,'[workbook name]sheet name'!table_array, col_index_num,FALSE)
=ВПР(искомое_значение;'[имя_книги]имя_листа'!таблица;номер_столбца;ЛОЖЬ)

Настоящая формула может выглядеть так:

=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)
=ВПР($A$2;'[New Prices.xls]Sheet1'!$B:$D;3;ЛОЖЬ)

Эта формула будет искать значение ячейки A2 в столбце B на листе Sheet1 в рабочей книге New Prices и извлекать соответствующее значение из столбца D.

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

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

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

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

Итак, если случилось, что аргумент col_index_num (номер_столбца) меньше 1, функция ВПР также сообщит об ошибке #ЗНАЧ!.

Если же аргумент col_index_num (номер_столбца) больше количества столбцов в заданном массиве, ВПР сообщит об ошибке #REF! (#ССЫЛ!).

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

Простейший случай – ошибка #NAME? (#ИМЯ?) – появится, если Вы случайно напишите с ошибкой имя функции.

Решение очевидно – проверьте правописание!

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

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

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

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

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

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

Как Вы уже знаете, ВПР возвращает из заданного столбца значение, соответствующее первому найденному совпадению с искомым. Однако, Вы можете заставить ее извлечь 2-е, 3-е, 4-е или любое другое повторение значения, которое Вам нужно. Если нужно извлечь все повторяющиеся значения, Вам потребуется комбинация из функций ИНДЕКС (INDEX), НАИМЕНЬШИЙ (SMALL) и СТРОКА (ROW).

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

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

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

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

Этот заголовок исчерпывающе объясняет суть проблемы, правда?

Решение: Всегда используйте абсолютные ссылки на ячейки (с символом $) при записи диапазона, например $A$2:$C$100 или $A:$C. В строке формул Вы можете быстро переключать тип ссылки, нажимая F4.

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

Если Вы не хотите пугать пользователей сообщениями об ошибках #Н/Д, #ЗНАЧ! или #ИМЯ?, можете показывать пустую ячейку или собственное сообщение. Вы можете сделать это, поместив ВПР в функцию ЕСЛИОШИБКА (IFERROR) в Excel 2013, 2010 и 2007 или использовать связку функций ЕСЛИ+ЕОШИБКА (IF+ISERROR) в более ранних версиях.

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

Синтаксис функции ЕСЛИОШИБКА (IFERROR) прост и говорит сам за себя:

IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)

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

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

=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"")

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

=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")

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

Так как функция ЕСЛИОШИБКА появилась в Excel 2007, при работе в более ранних версиях Вам придётся использовать комбинацию ЕСЛИ (IF) и ЕОШИБКА (ISERROR) вот так:

=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",VLOOKUP формула)
=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)

Например, формула ЕСЛИ+ЕОШИБКА+ВПР, аналогична формуле ЕСЛИОШИБКА+ВПР, показанной выше:

=IF(ISERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE)),"",VLOOKUP($F$2,$B$2:$C$10,2,FALSE))
=ЕСЛИ(ЕОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ));"";ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ))

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

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

ВПР (искомое значение; диапазон поиска; номер столбца с входным значением; 0 (ЛОЖЬ) или 1 (ИСТИНА)).

ЛОЖЬ – точное значение, ИСТИНА – приблизительное значение.

Простейшая задача для функции ВПР. Например, у нас есть список лекарственных препаратов. Наша первая задача – найти стоимость препарата Хепилор.

В ячейке С12 начинаем писать функцию:

1. B12 – поскольку нам нужен Хепилор, выбираем ячейку с предварительно написанным названием искомого лекарства.
2. Далее выбираем диапазон данных B3:D10, где функция будет совершать поиск нужного нам значения. Крайний левый столбец диапазона должен содержать в себе искомый критерий, по которому производится поиск значения.
3. Следующий шаг – указать номер столбца в массиве B3:D10, из которого будет считана информация на одной строке с Хепилором. Столбцы нумеруются слева направо в самом диапазоне, в нашем примере первый столбец – В, но не А, поскольку А лежит вне области диапазона.

Поиск по столбце «Производитель» будет работать точно так же, нужно просто указать последовательность столбца, где находится нужная нам информация – заменяем цифру «3» в формуле (ячейка С27) на цифру «2»:

Есть определённая особенность, связанная со столбцами. Иногда в Excel-файле в таблицах некоторые ячейки объединяют. На картинке ниже в формуле на месте порядкового номера столбца у нас написана цифра «3», но результат – название производителя, а не цена, как в первом примере:

Произошел сдвиг нумерации столбцов как раз из-за наличия объединения ячеек в столбце «Лекарственное средство»: мы объединяли столбцы «H» и «I», зрительно столбец «Лекарственное средство» — это первый столбец, а «Производитель» — второй, НО формула нумерует их следующим образом:

• H – первый;
• I – второй;
• J – третий;
• K – четвертый.

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



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

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

1. B3 – критерий, по котором проводим поиск данных.
2. F3:G14 – диапазон, по котором наша функция будет осуществлять поиск совпадения критерия и данных по строке.
3. Цифра «2» — номер столбца с нужной нам информацией по критерию.
4. Цифра «0» (или можно использовать слово «ЛОЖЬ») — для точности результатов.

Таким образом, когда мы задаем формуле искомый критерий, она начинает поиск совпадений с верхней ячейки первого столбца (шаг 1 на картинке). Затем функция «читает» все критерии сверху вниз, пока не найдет точное совпадение (шаг 2). Когда ВПР дойдет до Хепилора, она отсчитает нужное количество столбцов вправо (шаг 3) и выдаст нам искомое значение для критерия – цену 86,90 (шаг 4):

Но сейчас у нас есть данные только по первому критерию. Для того чтобы заполнить третий столбец D первой таблицы до конца, нужно просто скопировать функцию до последнего критерия. Однако, на этом этапе для корректной работы диапазон, где совершается поиск, нужно закрепить, иначе массив данных «съедет» вниз и у нас ничего не получится. Для этого используем абсолютные ссылки для диапазона в ячейке D3 – выделяем курсором диапазон F3:G14 и нажимаем клавишу F4, после чего совершаем копирование формулы до конца таблицы:

В итоге мы получаем необходимый нам результат:

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

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

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

Использование приблизительного значения

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

Как видим, размер премии зависит от того диапазона по системе премирования, куда попал показатель выполнения продаж конкретного сотрудника. Мы видим, что если план выполнен менее, чем на 100% — премия не присваивается, а если на 107% (выше 100%, но меньше 110%), тогда сотрудник получает премию размером 10%. Описанные показатели премии нам нужно вписать с помощью функции ВПР в столбец «Премия» первой таблицы, только на этот раз критерий будет находиться в определённом диапазоне.

Для корректной работы нужно убедиться, что границы диапазонов во второй таблице крайнего левого столбца размещены по возрастанию сверху вниз (шаг 1). Формула берёт выбранный нами критерий и осуществляет поиск в первом столбце второй таблицы (шаг 2), просматривая все значения сверху вниз (шаг 3). Как только функция находит первое значение, которое превышает критерий с первой таблицы, делает «шаг назад» (шаг 4) и считывает значение, которое соответствует найденому критерию (шаг 5). Иными словами, при неточном поиске функция ВПР ищет меньшее значение для искомого критерия:

Таким образом, наша функция будет выглядеть так:

И результат использования функции ВПР с приблизительным поиском имеет вот такой результат:

Скачать все пошаговые примеры функции ВПР в Excel

Например, сотрудник Ольга имеет премию размером 0%, поскольку она выполнила 76% продаж, тоесть перевыполнила план на 0%. А сотрудник Наталья совершила продажи на 21% выше нормы и была премирована на 20%, что мы и видим, если сравнить самостоятельно данные с двух таблиц.

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