Что значит перенос в excel

Перенос текста в Excel

Часто, при работе в Excel, вы можете столкнуться со следующей ситуацией: при заполнении ячейки, например, в столбике А, её текст «залезает» на ячейки столбика В, а то и С. То есть «перенос текста по словам» не работает и ячейка А как бы перекрывает несколько столбиков. Это довольно частая проблема, если вы не поняли о чем идет речь — далее в статье будут рассмотрены примеры.

Но как её решить?

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

С помощью функции «Перенести текст»

Это самый простой вариант переноса. Разработчики Excel знают, что любой человек при работе в программе, скорее всего, столкнется с проблемой перекрытия текстом других ячеек, поэтому они вынесли функцию «перенести текст» в главный раздел программы. Давайте посмотрим как это работает!

Вот пример, вы можете видеть, что значения ячеек столбика А «перекрывает» столбик В и С, и даже, в некоторых случаях, «заходит» за столбик D:

Как это исправить?

• Выделяем столбик А и щелкаем на «Главная»;

• Далее — «Перенести текст».

Результат будет таким:

Если вам удобнее использовать функцию из панели быстрого доступа, вы можете добавить её туда. Например, нажав правой кнопкой.

С помощью горячих клавиш

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

Комбинация горячих клавиш следующая:

С помощью функции «Формат ячеек»

На мой взгляд, это самый долгий способ, но вы все равно должны знать о нем. Когда-нибудь может пригодиться.

• Выделите диапазон ячеек и щелкните на «Главная» -> Стрелка, идущая вниз (как на картинке, также вы можете использовать горячие клавиши CTRL + 1);

• Во второй вкладке активируйте опцию «переносить по словам»;

• Подтвердите.

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

Excel автоматически вычисляет какой будет высота ячейки

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

Перенос строк вручную

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

Как это сделать?

• Начните редактировать ячейку, щелкните на то место, где хотите сделать перенос и нажмите ALT + ENTER (как на картинке ниже):

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

Регулируем высоту строки вручную

Бывает такое, что в ячейке будет несколько абзацев текста и их нужно как-то обработать так, чтобы высота строки не была слишком большой (а в автоматическом режиме, при использовании функции «перенос по словам» — она именно такой и будет).

Вы можете увидеть пример на картинке ниже:

В таком случае, мы можем вручную задать высоту строки. Таким образом во всех ячейках столбика она будет одинакова, и это уже не будет смотреться некорректно.

1. Выделите диапазон ячеек и щелкните на «Главная» -> «Формат»;

• Далее — «Высота строки…»;

• В открывшемся окне укажите нужную высоту, в моем случае это «40»;

• Подтвердите.

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

Перенос текста работает некорректно, причины

В случае, если перенос текста, в вашей табличке, работает как-то «не так», это может происходить из-за нескольких причин:

Функция «перенести текст» отключена

Может быть вы забыли включить функцию «перенести текст» можно быстро проверить это, зайдя в раздел «Главная». Если функция активирована, то её значок будет выделен.

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

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

Можно не делать это вручную, так как есть прекрасная функция «автоподбор высоты строки»:

Программа автоматически рассчитает нужную высоту.

По заданной ширине ячейки весь текст «влезает»

Бывает и такое, что в какой-то отдельной ячейке текста немного. И он просто помещается в ту ширину, которую вы или Excel выбрали.

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

Что значит перенос в excel

Содержание

• Как исправить ошибку #ПЕРЕНОС! в Excel
• Как исправить ошибку #ЗНАЧ! в Excel
• Как исправить ошибку #ПУСТО! в Excel
• Как исправить ошибку #ИМЯ? в Excel

Как исправить ошибку #ПЕРЕНОС! в Excel

Прежде чем рассмотреть ошибку #ПЕРЕНОС! (#SPILL), рассмотрим, что такое перенос. В Excel это означает, что формула возвращает несколько значений (массив), и они автоматически переносятся в соседние ячейки.

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

Ошибка #ПЕРЕНОС! возникает, когда формула возвращает несколько значений, но Excel не может вывести один или несколько результатов.

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

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

Рассмотрим решения, которые подойдут для наиболее распространенных вариаций этой ошибки.

Диапазон для переноса содержит одну или более ячеек с значениями

Решение: очистить диапазон для переноса.

Диапазон для переноса находится внутри таблицы

Решение 1: преобразовать таблицу в диапазон данных.

Для этого выполните следующие шаги:

1. нажмите на любую ячейку в таблице,
2. в меню в верхней части окна выберите «Конструктор»,
3. выберите команду «Преобразовать в диапазон».

Обратите внимание, что расположение меню и списка команд могут отличаться в зависимости от версии.

Решение 2: переместить формулу за границы таблицы.

Диапазон для переноса содержит объединенные ячейки

Решение: разделить ячейки внутри диапазона.

Как исправить ошибку #ЗНАЧ! в Excel

Ошибка #ЗНАЧ! (#VALUE) возникает в следующих случаях:

• что-то не так с ячейкой (ячейками), на которую ссылается формула,
• что-то не так с самой формулой.

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

Математическая формула ссылается на текст

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

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

Например, на примере ниже функция =СУММ(B2,B11) будет игнорировать текст в ячейке B11.

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

Одна или более ячеек содержат пробелы

В этих случаях ячейка выглядит пустой.

Решение: найти и заменить пробелы. Вот как это сделать:

1. выделите диапазон ячеек, к которому обращается формула;
2. нажмите на иконку с биноклем, нажмите «Найти и выделить»;
3. в окне «Найти и заменить» выберите «Заменить»;
4. в поле «Найти» вставьте пробелы;
5. поле «Заменить на» оставьте пустым;
6. нажмите «Заменить все».

Как исправить ошибку #ПУСТО! в Excel

Ошибка #ПУСТО! (#REF) возникает, когда формулы ссылается на ячейку, которая уже не существует. Разберем наиболее распространенные причины возникновения ошибки и как их исправить.

Ячейка, на которую ссылается формула, удалена

Формула использует прямые ссылки на ячейки (каждая ячейка отделена запятой), но одна или несколько ячеек удалены. Это основная причина, почему Excel не рекомендует использовать прямые ссылки на ячейки.

Решение 1: Если ячейки удалены случайно, отмените это действие.

Решение 2: Обновите формулу, чтобы она ссылалась на диапазон ячеек. В этом случае Excel сможет произвести вычисления, даже если одна из ячеек была удалена.

Формула содержит относительные ссылки

Относительная ссылка означает, что используемые данные привязаны к ячейке, в которую вставлена формула. Например, если формулу =СУММ(B2:E2) скопировать из ячейки G2 в G3, Excel предположит, что должен суммировать все ячейки из колонок B-E в ряду 3.

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

Например, если формулу =СУММ(G2:G7) перенести в ячейку I4, Excel предположит, что пользователю требуется сложить шесть ячеек над клеткой I4. В данном случае это невозможно, так как доступны лишь три ячейки.

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

Например, формула =СУММ(G2:G7) с абсолютными ссылками будет выглядеть как: =СУММ($G$2:$G$7).

Как исправить ошибку #ИМЯ? в Excel

Ошибка #ИМЯ? (#NAME) возникает, если название формулы неверно написано. Рассмотрим основные решения проблемы.

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

Решение: обновить название формулы. Лучший способ избегать опечаток — использовать встроенный редактор формул Excel. Когда пользователь начинает печатать название формулы, программа автоматически предложит список названий, содержащих те же буквы.

Неверное название формулы

Иногда пользователи вводят название несуществующей формулы или формулы, которая называется иначе (например, ПЛЮС вместо реальной функции СУММ). В этом случае формула вернет ошибку #ИМЯ?.

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

Вот как это сделать:

1. выберите в меню раздел «Формулы»;
2. нажмите на «Вставить функцию» в левой части панели инструментов, в открывшемся окне «Вставка функции» просмотрите недавно использовавшиеся функции или полный список всех доступных функций;
3. выберите и вставьте нужную функцию;
4. в редакторе формул обновите ячейки, к которым будет отсылаться формула (поля зависят от выбранной функции).

Эти советы применимы и к ошибкам в Google Таблицах (за исключением #ПЕРЕНОС!, которая часто отображается как #REF).

Фото на обложке: Aihr.com

Подписывайтесь на наш Telegram-канал, чтобы быть в курсе последних новостей и событий!

Перенос в ячейке Excel

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

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

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

Функция «Символ»

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

Для этого вы можете использовать функцию СИМВОЛ , которая позволяет вставлять любой символ ASCII в ячейку. Но прежде чем описать, как его применять, вам необходимо понять, как это работает.

Excel постоянно использует коды различных символов. Каждая буква, пиктограмма имеет свой собственный код от 1 до 255. Например, код заглавной буквы A равен 97. Разрыв строки же имеет номер 10. И хотя этот символ невидимый, он помогает программе понять, в каком месте делать перенос. Поэтому он очень важен.

Перед переносом строки необходимо включить режим «Перенос текста». Таким образом, вы можете убедиться, что символ разрыва строки не виден.

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

Удаление переносов строк

Как уже упоминалось выше, если вы добавите разрывы строк, эти невидимые символы могут вызвать много неудобств. Но при правильном подходе вы сможете понять, как от них избавиться.

Поскольку вы уже знаете, что добавление разрывов строк возможно с помощью комбинации Ctrl + Enter, вам все равно придется выяснить, как их устранить, если есть дополнительные символы. Ведь никто не будет отрицать того, что невозможно стартовать без умения тормозить. Поэтому уметь удалять переносы строк очень важно.

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

Так что же можно предпринять, чтобы автоматизировать процесс удаления переносов строк? Это можно сделать несколькими способами.

Замена символа переноса

Самый простой способ удалить перенос — использовать стандартную функцию поиска и замены в Excel. Вам необходимо выбрать все ячейки, доступные в документе, нажав комбинацию клавиш Ctrl + A и вызвав соответствующее диалоговое окно, используя комбинации клавиш Ctrl + H. Вы также можете открыть окно «Найти и заменить» на вкладке «Главная» на ленте, где вы найдете и выберите группу «Поиск» и нажмите кнопку «Заменить».

Здесь у внимательного и вдумчивого читателя возникнет вопрос: что вводить в верхнее поле «Поиск», если символ невидимый и на клавиатуре его нет? И здесь невозможно ввести комбинацию клавиш «Alt + Enter», поэтому вы не можете скопировать ее из готовой таблицы.

Но у этой комбинации есть альтернатива — Ctrl + J. Эта комбинация клавиш может заменить Alt + Enter в любом диалоговом окне или поле ввода.

После того, как пользователь добавляет мигающий курсор в поле «Найти» и вводит комбинацию клавиш Ctrl + J, визуально ничего не меняется. Это не должно смущать, так как сам символ невидим, и он при всем желании пользователя Excel не может быть обнаружен.

В поле «Заменить» вы можете ничего не писать. Если вы хотите, чтобы линии не были соединены, вы можете поставить пробел. В результате, между строками будет свободное пространство. Чтобы подтвердить действие, нужно нажать кнопку «Заменить все». Все, после этого переноса не будет во всем документе.

Справедливый и обратный ход. Если вы введете пробел в поле «Найти» и нажмете комбинацию Ctrl + J в поле «Заменить», то все пробелы будут заменены переносами строк. Это может быть очень удобно в некоторых ситуациях. Например, если есть перечень слов, которые были случайно напечатаны в ряд, в то время как их нужно поставить в столбик. В таком случае можно открыть то же окно «Найти и заменить» и заменить пробел на символ переноса строки.

Важно учитывать еще один момент. Символ перевода строки, введенный в поле Find, может остаться после замены, когда это диалоговое окно открывается во второй раз. Поэтому лучше его снова вызвать и несколько раз нажать кнопку «Backspace», при этом курсор будет помещен в поле.

Формула удаления переносов строк

Если вам нужно использовать формулу только для удаления разрывов строк, вы должны использовать функцию ПЕЧСИМВ , предназначенную для удаления всех невидимых символов. Разрывы строк не являются исключением.

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

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

Деление на столбцы по переносу строки

Инструмент «Текст по столбцам», расположенный на вкладке «Данные», также хорошо работает с переносами строк. Для этого на втором шаге выберите разделитель «Другой» и введите символ перевода строки, используя комбинацию клавиш Ctrl + J.

Если в таблице несколько разрывов строк один за другим, флажок «Считать последовательные разделители одним». И после того, как вы нажмете кнопку «Далее», мы получим этот результат.

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

Как разделить текст на несколько строк?

Excel дает возможность деления текста, который состоит из нескольких строк на отдельные строки.

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

Но как обойтись без навыков программирования, если все таки приходится постоянно разделять строки? Самый простой способ — использовать Power Query.

Описание надстройки Power Query

По умолчанию эта надстройка была впервые включена в стандартный комплект Excel 2016. Однако, если вы используете более старую версию, можно загрузить ее с веб-сайта Microsoft. Power Query — это аддон, позволяющий подключаться к разным данным и обрабатывать их для дальнейшего анализа. Он также может быть использован для разделения строк.

Чтобы загрузить данные в Power Query, вы должны преобразовать их в формат Smart Table. Для этого нажмите комбинацию клавиш Ctrl + T или нажмите кнопку «Формат», как в таблице на вкладке «Главная». Но если вы не хотите этого делать, есть способ поступить иначе.

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

Далее на вкладке «Данные» (в Excel начиная 2016 версии) или на вкладке Power Query (если Excel более старой версии с установленным аддоном) следует нажать на кнопку «Из таблицы/диапазона», чтобы перенести данные в редактор Power Query.

После того, как данные загрузятся, колонка с многострочным текстом выделяется, а на вкладке «Главная» найти опцию «Разделить столбец – По разделителю».

С большой вероятностью Power Query будет независимо определять, в зависимости от каких критериев должно осуществляться разделение, и сам будет помещать символ # (lf) символа переноса строки, чтобы пользователь мог увидеть, где находится этот невидимый символ. При необходимости вы можете использовать другие символы, выбранные из раскрывающегося списка, расположенного внизу окна, после выбора флажка «Разделить с помощью специальных символов».

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

Осталось только подтвердить настройки нажатием кнопки ОК и получить желаемый результат.

Полученную таблицу можно вставить в рабочую таблицу с помощью команды «Закрыть и загрузить» — «Закрыть и загрузить в …». Вы можете найти ее на вкладке «Главная».

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

Использование макроса для деления строки

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

Чтобы использовать макрос для разделения строк переносами, необходимо перейти на вкладку «Разработчик» или нажать комбинацию Alt + F11. Затем откроется окно, в котором новый модуль вставляется через меню «Insert — Module». Затем вам нужно скопировать этот код и вставить его в среду разработки Visual Basic.

Sub Split_By_Rows()

Dim cell As Range, n As Integer

Set cell = ActiveCell

For i = 1 To Selection.Rows.Count

ar = Split(cell, Chr(10)) ‘делим текст по переносам в массив

n = UBound(ar) ‘определяем кол-во фрагментов

cell.Offset(1, 0).Resize(n, 1).EntireRow.Insert ‘вставляем пустые строки ниже

cell.Resize(n + 1, 1) = WorksheetFunction.Transpose(ar) ‘вводим в них данные из массива

Set cell = cell.Offset(n + 1, 0) ‘сдвигаемся на следующую ячейку

Next i

End Sub

Как удалить переносы строк с помощью макроса?

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

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

Недостаток – как и в предыдущем примере, необходимо владение навыками программирования.

Достаточно просто в редактор Visual Basic вставить следующий код.

Sub RemoveCarriageReturns()

Dim MyRange As Range

Application.ScreenUpdating = False

Application.Calculation = xlCalculationManual

For Each MyRange In ActiveSheet.UsedRange

If 0 < InStr(MyRange, Chr(10)) Then

MyRange = Replace(MyRange, Chr(10), «»)

End If

Next

Application.ScreenUpdating = True

Application.Calculation = xlCalculationAutomatic

End Sub

Выводы

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

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

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

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

Почему впр выдает ошибку перенос

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel для iPad Excel Web App Excel для iPhone Excel для планшетов с Android Excel для телефонов с Android Еще…Меньше

Ошибки #ПЕРЕНОС! возникают, когда формула возвращает несколько результатов, а Excel не может вернуть результаты в сетку. Дополнительные сведения об этих типах ошибок см. в следующих разделах справки.

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

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

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

Excel не удалось определить размер массива с переносом, так как он является переменным и меняет размер между этапами вычислений. Например, следующая формула вызывает эту ошибку #ПЕРЕНОС! :

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

Это значение ошибки обычно связано с использованием функций СЛЧИС, СЛМАССИВи СЛУЧМЕЖДУ. Другие переменные функции, например СМЕЩ, ДВССЫЛ и СЕГОДНЯ, не возвращают разные значения на каждом этапе вычисления.

Например, при размещении в ячейке E2 формулы =ВПР(A:A;A:C;2;ЛОЖЬ) (как в примере ниже) ранее выполнялся поиск ИД только в ячейке A2. Однако в динамическом массиве Excel формула вызывает ошибку #ПЕРЕНОС!, так как Excel выполняет поиск во всем столбце, возвращает 1 048 576 результатов и достигает конца сетки Excel.

Эту проблему можно устранить 3 простыми способами:

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

=ВПР(A2:A7;A:C;2;ЛОЖЬ)

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

=ВПР(A2;A:C;2;ЛОЖЬ)

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

=ВПР(@A:A;A:C;2;ЛОЖЬ)

Формулы массивов с переносом не поддерживаются в таблицах Excel. Попробуйте переместить формулу из таблицы или преобразовать таблицу в диапазон (нажмите Конструктор таблиц > Инструменты > Преобразовать в диапазон).

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

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

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

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

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

Дополнительные сведения

См. также

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Динамические массивы и поведение массива с переносом

Оператор неявного пересечения: @

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

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

В нашем учебном примере показаны продажи по менеджерам Иванов, Петров, Сидоров за январь – июнь. Мы хотим продублировать продажи Петрова при помощи ссылки на диапазон ячеек:

Обратите внимание на формулу в ячейке B6, мы в ней указываем тот диапазон, который хотим повторить. В предыдущих версиях Excel такая функция вернула бы только значение из первой ячейки, а мы получили ошибку #ПЕРЕНОС! вместо требуемого нам результата. Что делать? В чем причина такой ошибки?

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

Готово, ошибку #ПЕРЕНОС! мы исправили. Запомните общее правило, которое пригодится вам в таких ситуациях: Проверьте, что содержится в ячейках, где вы хотите отобразить данные. Возможно, что информация может быть специфично оформлена, к примеру белым цветом, что не позволяет быстро найти заполненные ячейки. Если вы уверены, что в ячейках нет нужных вам данных – выделите их все и нажмите Del для удаления содержимого.

Если вы сталкиваетесь в Excel и с другими ошибками, рекомендую прочитать наши статьи:

• Как исправить ошибку #ССЫЛКА! в Excel
• Как исправить ошибку #ЗНАЧ в Excel
• Как исправить ошибку #Н/Д в Excel
• Как исправить ошибку #ДЕЛ/0! в Excel
• Как убрать ошибку #### в Excel

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

Содержание

• Как исправить ошибку #ПЕРЕНОС! в Excel
• Как исправить ошибку #ЗНАЧ! в Excel
• Как исправить ошибку #ПУСТО! в Excel
• Как исправить ошибку #ИМЯ? в Excel

Как исправить ошибку #ПЕРЕНОС! в Excel

Прежде чем рассмотреть ошибку #ПЕРЕНОС! (#SPILL), рассмотрим, что такое перенос. В Excel это означает, что формула возвращает несколько значений (массив), и они автоматически переносятся в соседние ячейки.

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

Ошибка #ПЕРЕНОС! возникает, когда формула возвращает несколько значений, но Excel не может вывести один или несколько результатов.

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

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

Рассмотрим решения, которые подойдут для наиболее распространенных вариаций этой ошибки.

Диапазон для переноса содержит одну или более ячеек с значениями

Решение: очистить диапазон для переноса.

Диапазон для переноса находится внутри таблицы

Решение 1: преобразовать таблицу в диапазон данных.

Для этого выполните следующие шаги:

1. нажмите на любую ячейку в таблице,
2. в меню в верхней части окна выберите «Конструктор»,
3. выберите команду «Преобразовать в диапазон».

Обратите внимание, что расположение меню и списка команд могут отличаться в зависимости от версии.

Решение 2: переместить формулу за границы таблицы.

Диапазон для переноса содержит объединенные ячейки

Решение: разделить ячейки внутри диапазона.

Как исправить ошибку #ЗНАЧ! в Excel

Ошибка #ЗНАЧ! (#VALUE) возникает в следующих случаях:

• что-то не так с ячейкой (ячейками), на которую ссылается формула,
• что-то не так с самой формулой.

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

Математическая формула ссылается на текст

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

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

Например, на примере ниже функция =СУММ(B2,B11) будет игнорировать текст в ячейке B11.

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

Одна или более ячеек содержат пробелы

В этих случаях ячейка выглядит пустой.

Решение: найти и заменить пробелы. Вот как это сделать:

1. выделите диапазон ячеек, к которому обращается формула;
2. нажмите на иконку с биноклем, нажмите «Найти и выделить»;
3. в окне «Найти и заменить» выберите «Заменить»;
4. в поле «Найти» вставьте пробелы;
5. поле «Заменить на» оставьте пустым;
6. нажмите «Заменить все».

Как исправить ошибку #ПУСТО! в Excel

Ошибка #ПУСТО! (#REF) возникает, когда формулы ссылается на ячейку, которая уже не существует. Разберем наиболее распространенные причины возникновения ошибки и как их исправить.

Ячейка, на которую ссылается формула, удалена

Формула использует прямые ссылки на ячейки (каждая ячейка отделена запятой), но одна или несколько ячеек удалены. Это основная причина, почему Excel не рекомендует использовать прямые ссылки на ячейки.

Решение 1: Если ячейки удалены случайно, отмените это действие.

Решение 2: Обновите формулу, чтобы она ссылалась на диапазон ячеек. В этом случае Excel сможет произвести вычисления, даже если одна из ячеек была удалена.

Формула содержит относительные ссылки

Относительная ссылка означает, что используемые данные привязаны к ячейке, в которую вставлена формула. Например, если формулу =СУММ(B2:E2) скопировать из ячейки G2 в G3, Excel предположит, что должен суммировать все ячейки из колонок B-E в ряду 3.

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

Например, если формулу =СУММ(G2:G7) перенести в ячейку I4, Excel предположит, что пользователю требуется сложить шесть ячеек над клеткой I4. В данном случае это невозможно, так как доступны лишь три ячейки.

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

Например, формула =СУММ(G2:G7) с абсолютными ссылками будет выглядеть как: =СУММ($G$2:$G$7).

Как исправить ошибку #ИМЯ? в Excel

Ошибка #ИМЯ? (#NAME) возникает, если название формулы неверно написано. Рассмотрим основные решения проблемы.

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

Решение: обновить название формулы. Лучший способ избегать опечаток — использовать встроенный редактор формул Excel. Когда пользователь начинает печатать название формулы, программа автоматически предложит список названий, содержащих те же буквы.

Неверное название формулы

Иногда пользователи вводят название несуществующей формулы или формулы, которая называется иначе (например, ПЛЮС вместо реальной функции СУММ). В этом случае формула вернет ошибку #ИМЯ?.

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

Вот как это сделать:

1. выберите в меню раздел «Формулы»;
2. нажмите на «Вставить функцию» в левой части панели инструментов, в открывшемся окне «Вставка функции» просмотрите недавно использовавшиеся функции или полный список всех доступных функций;
3. выберите и вставьте нужную функцию;
4. в редакторе формул обновите ячейки, к которым будет отсылаться формула (поля зависят от выбранной функции).

Эти советы применимы и к ошибкам в Google Таблицах (за исключением #ПЕРЕНОС!, которая часто отображается как #REF).

Фото на обложке: Aihr.com

Подписывайтесь на наш Telegram-канал, чтобы быть в курсе последних новостей и событий!

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

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

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

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

1. Исправляем ошибку #Н/Д функции ВПР в Excel
2. 1. Искомое значение написано с опечаткой
3. 2. Ошибка #Н/Д при поиске приближённого совпадения с ВПР
4. 3. Ошибка #Н/Д при поиске точного совпадения с ВПР
5. 4. Столбец поиска не является крайним левым
6. 5. Числа форматированы как текст
7. 6. В начале или в конце стоит пробел
8. Ошибка #ЗНАЧ! в формулах с ВПР
9. 1. Искомое значение длиннее 255 символов
10. 2. Не указан полный путь к рабочей книге для поиска
11. 3. Аргумент Номер_столбца меньше 1
12. Ошибка #ИМЯ? в ВПР
13. ВПР не работает (ограничения, оговорки и решения)
14. 1. ВПР не чувствительна к регистру
15. 2. ВПР возвращает первое найденное значение
16. 3. В таблицу был добавлен или удалён столбец
17. 4. Ссылки на ячейки исказились при копировании формулы
18. ВПР – работа с функциями ЕСЛИОШИБКА и ЕОШИБКА
19. ВПР: работа с функцией ЕСЛИОШИБКА
20. ВПР: работа с функцией ЕОШИБКА

Исправляем ошибку #Н/Д функции ВПР в 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 (СЖПРОБЕЛЫ):

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Оцените качество статьи. Нам важно ваше мнение:

Skip to content

Функция ВПР – это очень мощный инструмент поиска. Но если он по каким-то причинам завершился неудачно, то вы получите сообщение об ошибке #Н/Д (#N/A в английском варианте).

Давайте постараемся вместе ответим на вопрос: «Почему функция ВПР не работает?»

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

Все остальные случаи связаны с тем, что мы что-то не учли или сделали неверно.

Итак, почему ВПР не находит значение, хотя оно есть?

1. Неточность при вводе данных.
2. Опечатка при вводе функции.
3. Неверные ссылки.
4. Неверно указан параметр «интервальный просмотр».
5. Столбец поиска не является первым слева столбцом диапазона поиска.
6. Несовпадение форматов данных.
   • Преобразуем число в текст.
   • Преобразуем текст в число.
   • Если числовой и текстовый форматы беспорядочно перемешаны?
7. Лишние пробелы и непечатаемые знаки.
8. Неправильно указан номер столбца.
9. Неверная ссылка на данные из другой таблицы
10. После изменения таблицы функция ВПР перестала работать
11. Как убрать сообщение #Н/Д в ВПР?

Неточность при вводе данных.

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

Это вполне может случиться, особенно если вы записываете его прямо в формулу.

Опечатка при вводе функции.

Если вы видите ошибку #ИМЯ?, то это означает, что при записи названия самой функции вы допустили неточность – перепутали или добавили лишнюю букву.

Проверьте синтаксис, и всё будет в порядке.

Неверные ссылки.

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

Случается, что в формуле вы указали обычные (относительные) ссылки, забыв заменить их на абсолютные ($), а затем произвели какие-то действия с таблицей. К примеру, добавили столбец. Ваши ссылки в формуле теперь будут указывать на неверные координаты.

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

Неверно указан параметр «интервальный просмотр».

Включен поиск до первого приблизительного совпадения в отсортированном диапазоне (параметр = ИСТИНА или вовсе опущен), но на самом деле данные не отсортированы.

Об этой проблеме мы подробно говорили ранее.

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

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

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

Это важное ограничение нельзя забывать.

Несовпадение форматов данных.

Формат ячейки, откуда берется искомое значение наименования (например D3 в нашем случае), и формат ячеек первого столбца (A3:A21) из диапазона поиска отличаются (например, числовой и текстовый). Этот случай особенно часто встречается при использовании вместо текстовых наименований числовых кодов (номера счетов, артикулы, идентификаторы, даты и т.п.). Ведь число, записанное в таблицу Excel, может быть в двух принципиально разных состояниях — как число и как текст. И визуально их отличить практически невозможно.

Преобразуем число в текст.

Как видите, с виду записи одинаковы, однако в D6 артикул сохранен как число, а в A10 – как текст (на таких ячейках обычно видна пометка — зелёный уголок). Текст не может быть равнозначен числу, поэтому получаем #Н/Д. ВПР не находит значение, хотя оно с виду есть.

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

Как видно, обычную ссылку на D6 мы заменяем конструкцией

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

Можно поступить и проще — «приклеить» к числу пустую строку при помощи оператора склейки &.

Запомните простое правило:

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

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

Как видите, ошибка исчезла, поскольку теперь текстовое значение сравнивается с таким же текстом.

Преобразуем текст в число.

А если нужно сделать обратное преобразование? Преобразовать число как текст в обычное число, чтобы все данные в таблице были одного типа? Давайте рассмотрим на примере.

Добавьте перед проблемным значением двойной минус. Это означает — дважды умножить на минус 1. А еще со школы мы помним, что минус на минус дает плюс. В результате ничего не изменится. Но есть важный момент.

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

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

Если числовой и текстовый форматы беспорядочно перемешаны?

А теперь — самый сложный случай. И здесь тоже есть простое решение.

Если поиск с преобразованием значения в число не удался, то будем искать его как текст. Объединяем оба варианта при помощи функции ЕСЛИОШИБКА.

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

Лишние пробелы и непечатаемые знаки.

Формула не работает, потому что в артикуле присутствуют пробелы или невидимые непечатаемые знаки (перенос строки и т.п.).

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

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

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

В этом случае можно использовать функции СЖПРОБЕЛЫ (TRIM) и ПЕЧСИМВ (CLEAN) для удаления лишних пробелов либо других невидимых символов. Вместо

Функция СЖПРОБЕЛЫ убирает пробелы, а ПЕЧСИМВ удаляет все непечатаемые и невидимые символы.

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

Неправильно указан номер столбца.

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

Он не может быть меньше 1 и не может быть больше, чем количество столбцов в указанном для поиска диапазоне. Если он указан неверно, то получим ошибку #ЗНАЧ!

Когда вы видите такое, пересчитайте количество колонок в диапазоне, который вы указали, и убедитесь, что оно не меньше, чем номер, указанный третьим аргументом функции. Быть может, вы указали 4, а колонок всего 3.

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

Неверная ссылка на данные из другой таблицы

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

Если вы случайно допустили неточность при указании ссылки на эти данные, то увидите сообщение #ССЫЛКА!.

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

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

Вы сделали все правильно и до тех пор, пока вы не вставили несколько строк или столбцов в вашу таблицу, все работало верно.

Теперь же появилась ошибка #Н/Д либо результаты стали явно неверны. В чем дело?

А дело всё в том, что вы не использовали абсолютные ссылки, не добавляли к адресам ячеек, строк и столбцов знак $. В результате после добавления (или удаления) строк или столбцов ваши ссылки изменились, и все сломалось.

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

Постарайтесь отменить сделанные изменения, благо Excel хранит всю историю изменений вашей таблицы.

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

Как убрать сообщение #Н/Д в ВПР?

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

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

Можно легко перехватить ошибки и заменить их на любое другое подходящее нам значение (например, на ноль) с помощью функции ЕСЛИОШИБКА.

Синтаксис её подобен ЕСЛИ. В качестве условия используется выражение, которое потенциально может возвратить сообщение об ошибке.

Задача – заменить его числом или выражением, указанным во втором аргументе. Рассмотрим это на примере.

В нашем случае маракуйя отсутствует в прайсе. Но вместо #Н/Д мы выведем ноль.

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

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

Надеемся, теперь вы сможете ответить на вопрос: «Почему не работает функция ВПР в Excel?».

Еще полезные примеры:

• 1 Функция ВПР в Экселе – быстрый перенос данных
  • 1.1 Критерий поиска
  • 1.2 Диапазон данных
  • 1.3 Номер столбца для итогового значения
  • 1.4 Условие для поиска
  • 2.1 ВПР пример 1
  • 2.2 ВПР пример 2
  • 2.3 ВПР пример 3
  • 4.1 Конструкция с функцией ЕСЛИОШИБКА
  • 4.2 Разные форматы критерия в таблицах
  • 4.3 Функция СЖПРОБЕЛЫ для чистки текстового критерия
  • 4.4 Подсчет номера столбца в большой таблице
  • 6.1 С одним условием
  • 6.2 С несколькими условиями

  Функция ВПР в Экселе – быстрый перенос данных

  Самое простое применение функция ВПР это быстрый перенос данных из одной таблицы в другую.

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

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

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

  Например, «Стул_1» и «Стул_21» это два совершенно разных стула.

  Цены в прайсе указаны для примера и вряд ли имеют отношение к реальным ценам.

  В ООО «ЫкэА» пришел запрос от «Петровича».

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

  Однако это нас не страшит, во-первых, у нас есть ВПР, во-вторых мы и не такое видали.

  Вот собственно и сам запрос:

  

  Петрович требует, чтобы мы очень быстро проставили цены в его запросе. Ждать он намерен максимум 5 минут. Ведь другие поставщики уже завалили его предложениями.

  Нам не хочется терять такого клиента и мы практически мгновенно открываем прайс:

  

  Получается у нас должно быть открыто два файла (две книги в Эксель). Запрос от Петровича и Прайс.

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

  Для этого перейдем в таблицу запроса и в первой ячейке столбца «Цены» (D4) введем «=впр» и два раза кликнем на значок функции:

  

  Сразу же после этого, в строке формулы нужно поставить курсор внутри надписи ВПР и нажать Fx, перед вами появится окно с аргументами функции ВПР:

  

  В аргументах функции вы говорите Экселю что и где нужно искать:

  Искомое значение — это значение (в данном случае наименование), цену которого вы хотите найти в прайсе. Соответственно кликайте на первую ячейку столбца «Наименование».

  Далее, сразу переходите в «Прайс»:

  

  Теперь в аргументах функции заполните следующие поля:

  Таблица — выделяете столбцы, которые содержат искомые наименования и цены, таким образом, чтобы наименования были крайним левым столбцом.

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

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

  Интервальный просмотр — ставьте 0. Ноль обозначает точное соответствие.

  После заполнения аргументов функции нажимайте «Ок» и если всё сделано верно, то в столбце «Цена» (файл «Запрос от Петровича»), появится цена.

  Вам нужно протянуть цены на оставшиеся ячейки:

  

  Коллеги, вот и всё, вы овладели функцией ВПР.

  Очень важное замечание!

  Обратите внимание на то, что сейчас мы работали в двух разных файлах (книгах).

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

  

  Делает это он при помощи значка $, который проставляет перед столбцами и строками таблицы.

  Это позволяет не съезжать формуле когда вы протягиваете её вниз. Это очень актуально когда вы работаете в рамках одного листа или одной книги (в этом случае Эксель автоматически Не закрепляет ячейки).

  Давайте посмотрим что получиться если протянуть формулу «без закрепления»:

  

  Обратите внимание, что для первой ячейки всё в порядке и диапазон B3:C502 точно соответствует той таблице, которую мы выделили для поиска данных, однако (без закрепления) так будет не всегда, по мере «протягивания» формулы ВПР вниз, будет смещаться и таблица, пока однажды, мы не увидим вот такую странную надпись #Н/Д:

  

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

  

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

  Очень важное замечание №2

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

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

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

  Для этого нужно выделить столбец с формулами, нажать Ctrl+C и в левом верхнем углу выбрать «Вставить» — «Вставить значения».

  

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

  Критерий поиска

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

  Диапазон данных

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

  Номер столбца для итогового значения

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

  Условие для поиска

  Логическое значение (истина/1 или ложь/0), которое указывает приблизительное совпадение искать (1) или точное (0).

  Функция ВПР в Экселе: пошаговая инструкция

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

  

  Во второй – цены:

  

  Если перечень товаров в обеих таблицах совпадает, то, зная магическое сочетание Ctrl+C и Ctrl+V, данные о ценах можно легко подставить к данным о количестве. Однако очередность позиций в обеих таблицах не совпадает. Тупо скопировать цены и подставить к количеству не получится.

  

  Поэтому мы не можем прописать формулу умножения и «протянуть» вниз на все позиции.

  Что делать? Надо как-то цены из второй таблицы подставить к соответствующему количеству в первой, т.е. цену товара А к количеству товара А, цену Б к количеству Б и т.д.

  

  Функция ВПР в Эксель легко справится с задачей.

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

  

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

  

  Кликаем по надписи «ВПР». Открывается следующее диалоговое окно.

  

  Теперь нужно заполнить предлагаемые поля. В первом окошке «Искомое_значение» нужно указать критерий для ячейки, в которую мы вписываем формулу. В нашем случае это ячейка с наименованием товара «А».

  Следующее поле «Таблица». В нем нужно указать диапазон данных, где будет осуществляться поиск нужных значений. В нашем случае это вторая таблица с ценой. При этом крайний левый столбец выделяемого диапазона должен содержать те самые критерии, по которым осуществляется поиск (столбец с наименованиями товаров). Затем таблица выделяется вправо минимум до того столбца, где находятся искомые значения (цены). Можно и дальше вправо выделить, но это уже ни на что не влияет. Главное, чтобы выделенная таблица начиналась со столбца с критериями и захватывала нужный столбец с данными. Также следует обратить внимание на тип ссылок, они должны быть абсолютными, т.к. формула будет копироваться в другие ячейки.

  Следующее поле «Номер_столбца» — это число, на которое столбец с искомыми данными (ценами) отстоит от столбца с критерием (наименованием товара) включительно. То есть отсчет идет, начиная с самого столбца с критерием. Если у нас во второй таблице оба столбца находятся рядом, то нужно указать число 2 (первый – критерий, второй — цены). Часто бывает, что данные отстоят от критерия на 10 или 20 столбцов. Это не важно, Excel все сосчитает.

  Последнее поле «Интервальный_просмотр», где указывается тип поиска: точное (0) или приблизительное (1) совпадение критерия. Пока ставим 0 (или ЛОЖЬ). Второй вариант рассмотрен ниже.

  

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

  

  Теперь легко рассчитать стоимость простым умножением количества на цену.

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

  ВПР пример 1

  1. После создания отсортированного учебного файла щёлкнуть любую свободную ячейку (например, F9): в ней будет возвращён результат.
  1. На строке формул щёлкнуть fx.

  

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

  

  4. Выделить функцию ВПР и кликнуть OK.

  

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

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

  

  6. Как видно, заданное или ключевое значение в окне аргументов именуется «Искомым». Такое название не кажется мне удачным: оно задаётся изначально, а не ищется, и используется как ключ к поиску. Предположим, что нас интересует зарплата, получаемая сотрудницей Ириной Соколовой. Самый простой способ задать верхний параметр – щёлкнуть на ячейке A8, после чего – кликнуть на строке параметра «Таблица».

  7. В качестве параметра «Таблица» выделяется весь заполненный участок Excel, исключая заголовки столбцов. А на строке «Номер столбца» следует ввести номер столбца, содержащего список зарплат, т. е. 2. Как видно, правильное значение зарплаты сотрудницы Ирины Соколовой появляется прямо в окне аргументов.

  

  8. После клика на OK возвращённое функцией значение появится и в ячейке, зарезервированной для результата.

  

  ВПР пример 2

  Естественно, что аналогичным образом могут быть извлечены данные из других информационных столбцов. Предположим, что нам нужно узнать дату рождения всё той же Ирины Соколовой. Аргументы функции ВПР претерпят единственное изменение на строке «Номер столбца». Как видно, функция возвращает, на первый взгляд, непонятное число 29025.

  

  После щелчка на ОК оно появится и в ячейке результата.

  

  Опытные пользователи Excel знают, что в этой программе даты (и время) хранятся в числовом формате, начиная с условно принятой даты 0 января 1900 года. Для отображения полученного значения в привычном человеку виде достаточно придать ячейке результата формат даты. Для этого сначала открыть её контекстное меню, и щёлкнуть пункт «Формат ячеек».

  

  В появившемся одноимённом окне:

  • щёлкнуть пункт «Дата»;
  • в правом списке при необходимости поменять вариант отображения;
  • щёлкнуть OK.

  

  После этого отображаемая в ячейке результата дата примет «человеческий» вид.

  

  ВПР пример 3

  Этот пример отличается от первого двумя аргументами – первым и заполненным последним. Как видно, в первом аргументе вместо имени использован подстановочный знак звёздочки *. Если не заполнять последний аргумент, то возвращаемое функцией значение будет ошибочным. Ввод логического значения ЛОЖЬ сделает результат правильным.

  

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

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

  Функция ВПР имеет свои особенности, о которых следует знать.

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

  2. Номер столбца, указываемый в третьем поле «Номер_столбца» при использовании Мастера функций, должен отсчитываться, начиная с самого критерия.

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

  4. Последний параметр формулы, который 0 (нуль), ставить нужно обязательно. Иначе формула может криво работать.

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

  

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

  

  Это позволит удалить сразу все внешние ссылки.

  Примеры функции ВПР в Excel

  Для следующих примеров использования функции ВПР возьмем немного другие данные.

  

  Требуется цены из второй таблицы подтянуть в первую. В качестве критерия здесь используется код. Ниже показаны этапы вычисления ВПР.

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

  

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

  Конструкция с функцией ЕСЛИОШИБКА

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

  

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

  Разные форматы критерия в таблицах

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

  Первый случай, когда критерии в первой таблице сохранены как числа, а критерии во второй таблице – как текст.

  

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

  

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

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

  

  Две ошибки по-прежнему связаны с тем, что эти товары отсутствую во второй таблице. Чтобы их заглушить, можно вновь воспользоваться функцией ЕСЛИОШИБКА.

  Вторая ситуация, заключается в том, что «текстом» являются критерий из первой таблицы. Форматы снова не совпадают.

  

  Как и в прошлый раз, будем вносить коррективы в функцию ВПР. Преобразовать «текст» в «число» еще проще. Достаточно к ссылке на «текстовый» критерий добавить 0 или умножить на 1.

  

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

  

  Отсутствующие критерии по-прежнему вызывают ошибку #Н/Д. В таком случае всю формулу можно еще раз «обернуть» в ЕСЛИОШИБКА.

  Функция СЖПРОБЕЛЫ для чистки текстового критерия

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

  Подсчет номера столбца в большой таблице

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

  

  Здорово экономит время.

  Видео-урок: Как использовать функцию ВПР в Excel

  Функция ВПР в Excel для чайников

  На примере ниже, в таблице справа, мы хотим подставить данные результатов экзамена по математике для студента Сидорова. Нам известны данные по всем студентам из таблицы слева. Используя формулу, мы можем подставить необходимые для нас данные.

  

  С одним условием

  Рассмотрим функцию на простом примере поиска сотрудника по присвоенному ему коду. Таблицу нужно отсортировать в порядке возрастания.

  1. Кликнуть по ячейке справа от таблицы или под ней и вписать искомый номер.
  2. В ячейке рядом ввести функцию ВПР через знак «=», либо же использовать опцию «Вставить функцию» в разделе «Формулы». Проще использовать именно опцию – там есть подсказки, какие именно данные нужны.
  3. Задать параметры поиска, заполнив соответствующие поля. Интервальный просмотр в данном случае пропустим.
  4. Нажать «Enter» – и появится результат поиска.
     Когда таблица не отсортирована и данные введены в хаотичном порядке, результат будет неправильный – программа найдет ближайшее соответствие («ИСТИНА»). Но можно не сортировать таблицу, а указать интервальный просмотр «ЛОЖЬ».
     Чтобы найти другой элемент, следует просто изменить первый аргумент, и результат автоматически преобразится.

  С несколькими условиями

  Часто на практике требуется сравнить данные нескольких диапазонов и выбрать значение с учетом 2-х и более критериев. Здесь задействована также функция ЕСЛИ, которая отвечает как раз за условия.

  Из таблицы нужно найти показатель выручки по конкретному менеджеру в определенный день:

  1. В первую выделенную ячейку вписать дату, во вторую – фамилию менеджера. В третьей ячейке будет происходить поиск.
  2. Кликнуть по пустой ячейке и ввести формулу =ВПР(G1;ЕСЛИ(C2:C12=G2;A2:D12;»»);4;0).
  3. Для подтверждения действия зажать комбинацию клавиш Ctrl+Shift+Enter. Нажатие на «Enter» не сработает в этом случае, потому что формула должна быть выполнена в массиве (об этом свидетельствуют скобки «<>», в которые взята вся формула).

  Синтаксис

  =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) – английская версия

  =ВПР(искомое_значение, таблица, номер_столбца, [интервальный_просмотр]) – русская версия

  Аргументы функции

  • lookup_value (искомое_значение) – это величина для поиска, из крайнего левого столбца таблицы. Это может быть значение, ссылка на ячейку или текстовая строка. В примере со студентами это их фамилии;
  • table_array (таблица) – это диапазон данных, в котором будет осуществлен поиск. Это может быть ссылка на диапазон ячеек или именованный диапазон. В примере с таблицей со студентами, это будет вся таблица, которая содержит оценку и фамилии студентов;
  • col_index (номер_столбца) – это порядковый номер столбца в диапазоне с данными, из которого будет получена искомая величина;
  • [range lookup] ([интервальный_просмотр]) – этот аргумент указывает на точность совпадения данных при поиске. Укажите “0” – если точное, “1” – если приблизительное совпадение.

  Дополнительная информация

  • совпадение искомых данных может быть точным и приблизительным;
  • при сопоставлении по приблизительной точности данных убедитесь, что данные в таблицах отсортированы в убывающем порядке (от большого к малому). Иначе, результат сопоставления будет некорректным;
  • при сопоставлении данных по приблизительной точности:
    – если функция не найдет искомое значение, она выдаст наибольшую величину, которая будет меньше чем значения поиска;
    – если функция при сопоставлении выдает ошибку #N/A, то искомое значение меньше чем самая маленькая величина в искомом диапазоне;
    – вы можете использовать подстановочные знаки для искомых значений.
  • https://pavel-istomin.ru/funkciya-vpr-v-eksele/
  • https://WindowsTips.ru/vpr-v-excel
  • https://statanaliz.info/excel/funktsii-i-formuly/funktsiya-excel-vpr-vlookup-i-gpr-hlookup/
  • https://gospodaretsva.com/excel-vlookup.html
  • https://excelhack.ru/funkciya-vlookup-vpr-v-excel/
  • https://sysadmin-note.ru/article/funkciya-vpr-v-excel/

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

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

  • 1 Столбец поиска искомого значения не является крайним левым.
  • 2 Функция ВПР возвращает ошибку #Н/Д (#N/A)
    • 2.1 1. Не закреплен диапазон таблицы
    • 2.2 2. Искомое значение и значение в просматриваемом диапазоне не совпадает
    • 3.1 1. Искомое значение больше 255 символов.
    • 3.2 2. Неправильно указан полный путь на другую книгу.
    • 3.3 3. Аргумент номер_столбца в функции ВПР меньше 1
    • 4.1 1. ВПР не чувствительна к регистру
    • 4.2 2. ВПР возвращает первое найденное значение
    • 4.3 3. Работа функции ВПР и добавлении или удалении столбца

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

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

    

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

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

    

    Решение

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

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

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

    

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

    

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

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

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

    

    Решение

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Похожие публикации:

    1. Как пронумеровать строки в excel по порядку
    2. Как разблокировать макросы в excel
    3. Как распечатать документ в excel
    4. Как сбросить настройки excel по умолчанию