Ошибки, возникающие при вставке или удалении данных

Решение

В наборе функций Excel, в категории Ссылки и массивы (Lookup and reference) имеется функция ВПР (VLOOKUP). Эта функция ищет заданное значение (в нашем примере это слово "Яблоки") в крайнем левом столбце указанной таблицы (прайс-листа) двигаясь сверху-вниз и, найдя его, выдает содержимое соседней ячейки (23 руб.) Схематически работу этой функции можно представить так:

Для простоты дальнейшего использования функции сразу сделайте одну вещь - дайте диапазону ячеек прайс-листа собственное имя. Для этого выделите все ячейки прайс-листа кроме "шапки" (G3:H19), выберите в меню Вставка - Имя - Присвоить (Insert - Name - Define) или нажмите CTRL+F3 и введите любое имя (без пробелов), например Прайс. Теперь в дальнейшем можно будет использовать это имя для ссылки на прайс-лист.

Теперь используем функцию ВПР. Выделите ячейку, куда она будет введена (D3) и откройте мастер функции (меню Вставка - Функция). В категории Ссылки и массивы (Lookup and Reference) найдите функцию ВПР (VLOOKUP) и нажмите ОК. Появится окно ввода аргументов для функции:

Заполняем их по очереди:

· Искомое значение (Lookup Value) - то наименование товара, которое функция должна найти в крайнем левом столбце прайс-листа. В нашем случае - слово "Яблоки" из ячейки B3.

· Таблица (Table Array) - таблица из которой берутся искомые значения, то есть наш прайс-лист. Для ссылки используем собственное имя "Прайс" данное ранее.

· Номер_столбца (Column index number) - порядковый номер (не буква!) столбца в прайс-листе из которого будем брать значения цены. Первый столбец прайс-листа с названиями имеет номер 1, следовательно нам нужна цена из столбца с номером 2.

· Интервальный_просмотр (Range Lookup) - в это поле можно вводить только два значения: ЛОЖЬ или ИСТИНА:

§ Если введено значение 0 или ЛОЖЬ (FALSE), то фактически это означает, что разрешен поиск только точного соответствия, т.е. если функция не найдет в прайс-листе укзанного в таблице заказов нестандартного товара (если будет введено, например, "Кокос"), то она выдаст ошибку #Н/Д (нет данных).

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

Все! Осталось нажать ОК и скопировать введенную функцию на весь столбец.

P.S.1

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

1. Включен точный поиск (аргумент Интервальный просмотр=0) и искомого наименования нет в Таблице.

2. Включен приблизительный поиск (Интервальный просмотр=1), но Таблица, в которой происходит поиск не отсортирована по возрастанию наименований.

3. Формат ячейки, откуда берется искомое значение наименования (например B3 в нашем случае) и формат ячеек первого столбца (F3:F19) таблицы отличаются (например, числовой и текстовый). Этот случай особенно характерен при использовании вместо текстовых наименований числовых кодов (номера счетов, идентификаторы, даты и т.п.) В этом случае можно использовать функции Ч и ТЕКСТ для преобразования форматов данных. Выглядеть это будет примерно так:
=ВПР(ТЕКСТ(B3);прайс;ЛОЖЬ)

4. Функция не может найти нужного значения, потому что в коде присутствуют пробелы или невидимые непечатаемые знаки (перенос строки и т.п.). В этом случае можно использовать текстовые функции СЖПРОБЕЛЫ (TRIM) и ПЕЧСИМВ (CLEAN) для их удаления:
=ВПР(СЖПРОБЕЛЫ(ПЕЧСИМВ(B3));прайс;ЛОЖЬ)
=VLOOKUP(TRIM(CLEAN(B3));прайс;FALSE)

P.S.2

Для подавления сообщения об ошибке #Н/Д (#N/A) в тех случаях, когда функция не может найти точно соответствия, можно воспользоваться вот такой конструкцией:

Функция ЕНД (ISNA) проверяет - не возникла ли ошибка #Н/Д как результат работы ВПР и если да, то выводит пустую строку ("") или ноль, а если нет - то выводит результат работы ВПР.

В Excel 2007/2010 для подавления сообщения об ошибке можно воспользоваться новой функцией ЕСЛИОШИБКА (IFERROR). Так, например, вот такая конструкция перехватывает любые ошибки создаваемые ВПР и заменяет их нулями:

=ЕСЛИОШИБКА(ВПР(B3;прайс;2;ЛОЖЬ);0)

=IFERROR(VLOOKUP(B3;прайс;2;FALSE);0)

P.S.3

 

Использование функции ВПР с примерами

Posted on 17.05.2011 by l-o-r-y

Функция подстановки значений (ВПР)

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

Так же мы имеем в отдельном файле прайс-лист на рекламную продукции.

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

Обратите внимание на то, что для корректной работы функции ВПР в заголовках таблицы не должно быть объединенных ячеек! Выберем ячейку в которую будем подставлять значения, взятые из другого файла. В начем случае это будет колонка «Цена». Выделим ячейку D3 (первую ячейку диапазона, в который необходимо подставить значения). В закладке Формулы найдем кнопку f_x и нажмем ее. Появится диалоговое окно мастера фукнций.

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

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

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

В поле ввода аргумента «Номер столбца» указываем «2», т.к. колонка с ценой в прайс-листе у нас — это второй по счету столбец.

В поле «Интервальный просмотр» пишем ЛОЖЬ, т.к. нам необходимо перенести точные значения, а не приблизительные

Нажмем ОК.

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

Вот что у нас должно получиться.

В принципе можно было бы сказать, что на этом задача выполнена. Но есть одно маленькое НО. В столбце D3 работает функция, мы можем видеть это в строке формул. Это означает, что наши 2 файла неразрывно связаны, т.е. если поменять данные в прайс-листе, то обязательно изменятся и данные в нашем файле, за 15.01.2011. Этого лучше не допускать. Для этого выделим весь диапазон ячеек, в который мы подставили данные, щелкнем правой кнопкой мыши и выберем опцию «Копировать»

Далее не сбрасывая выделения с области ячеек снова щелкнем по ней правой кнопкой мыши и выберем опцию «Специальная вставка».

В появившемся окне установим галочку напротив опции «значения». Нажмем ОК.

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

This entry was posted in Уроки Excel and tagged функция ВПР, функция подстановки значений. Bookmark the

 

Microsoft Office Excel 2003

Статья написана Ашишем Матуром, специалистом со статусом Microsoft MVP (Most Valuable Professional). Дополнительные сведения см. на сайте Microsoft MVP.

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

Функция ВПР

Функция ВПР выполняет поиск значения в крайнем левом столбце таблицы и возвращает значение из той же строки в указанном столбце. (Буква В в названии функции ВПР означает "вертикально".)

Чтобы продемонстрировать работу функции ВПР, воспользуемся данными в диапазоне B3:C11 — см. рисунок ниже — и найдем почасовые ставки в диапазоне F3:F11 для имен в диапазоне E3:E11. Обратите внимание на то, что порядок имен в диапазоне E3:E11 не совпадает с порядком имен в диапазоне B3:B11.

Здесь используется простая логика: найти для имени в диапазоне E3:E11 соответствие в диапазоне B3:B11, после чего вернуть почасовую ставку для этого имени из диапазона C3:C11 и скопировать ее в диапазон F3:F11.

Для решения этой задачи используется следующая формула:

=ВПР(E3;$B$3:$C$11;2;ЛОЖЬ)

Чтобы понять эту формулу, рассмотрим синтаксис функции ВПР:

ВПР(искомое_значение;таблица;номер_столбца;интервальный_просмотр)

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

· Искомое_значение Это ячейка, содержащая искомое значение. Таким образом, для ячейки F3 искомое_значение — это ячейка E3.

· Таблица В данном случае таблица — это диапазон, содержащий как искомые, так и возвращаемые данные. В нашем примере диапазон B3:C11 представляет собой список, из которого берутся почасовые ставки.

· Номер_столбца Это номер столбца в диапазоне $B$3:$C$11, в котором хранятся возвращаемые данные. В нашем примере почасовые ставки хранятся в столбце 2.

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

Решение

В наборе функций Excel, в категории Ссылки и массивы (Lookup and reference) имеется функция ВПР (VLOOKUP). Эта функция ищет заданное значение (в нашем примере это слово "Яблоки") в крайнем левом столбце указанной таблицы (прайс-листа) двигаясь сверху-вниз и, найдя его, выдает содержимое соседней ячейки (23 руб.) Схематически работу этой функции можно представить так:

Для простоты дальнейшего использования функции сразу сделайте одну вещь - дайте диапазону ячеек прайс-листа собственное имя. Для этого выделите все ячейки прайс-листа кроме "шапки" (G3:H19), выберите в меню Вставка - Имя - Присвоить (Insert - Name - Define) или нажмите CTRL+F3 и введите любое имя (без пробелов), например Прайс. Теперь в дальнейшем можно будет использовать это имя для ссылки на прайс-лист.

Теперь используем функцию ВПР. Выделите ячейку, куда она будет введена (D3) и откройте мастер функции (меню Вставка - Функция). В категории Ссылки и массивы (Lookup and Reference) найдите функцию ВПР (VLOOKUP) и нажмите ОК. Появится окно ввода аргументов для функции:

Заполняем их по очереди:

· Искомое значение (Lookup Value) - то наименование товара, которое функция должна найти в крайнем левом столбце прайс-листа. В нашем случае - слово "Яблоки" из ячейки B3.

· Таблица (Table Array) - таблица из которой берутся искомые значения, то есть наш прайс-лист. Для ссылки используем собственное имя "Прайс" данное ранее.

· Номер_столбца (Column index number) - порядковый номер (не буква!) столбца в прайс-листе из которого будем брать значения цены. Первый столбец прайс-листа с названиями имеет номер 1, следовательно нам нужна цена из столбца с номером 2.

· Интервальный_просмотр (Range Lookup) - в это поле можно вводить только два значения: ЛОЖЬ или ИСТИНА:

§ Если введено значение 0 или ЛОЖЬ (FALSE), то фактически это означает, что разрешен поиск только точного соответствия, т.е. если функция не найдет в прайс-листе укзанного в таблице заказов нестандартного товара (если будет введено, например, "Кокос"), то она выдаст ошибку #Н/Д (нет данных).

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

Все! Осталось нажать ОК и скопировать введенную функцию на весь столбец.

P.S.1

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

1. Включен точный поиск (аргумент Интервальный просмотр=0) и искомого наименования нет в Таблице.

2. Включен приблизительный поиск (Интервальный просмотр=1), но Таблица, в которой происходит поиск не отсортирована по возрастанию наименований.

3. Формат ячейки, откуда берется искомое значение наименования (например B3 в нашем случае) и формат ячеек первого столбца (F3:F19) таблицы отличаются (например, числовой и текстовый). Этот случай особенно характерен при использовании вместо текстовых наименований числовых кодов (номера счетов, идентификаторы, даты и т.п.) В этом случае можно использовать функции Ч и ТЕКСТ для преобразования форматов данных. Выглядеть это будет примерно так:
=ВПР(ТЕКСТ(B3);прайс;ЛОЖЬ)

4. Функция не может найти нужного значения, потому что в коде присутствуют пробелы или невидимые непечатаемые знаки (перенос строки и т.п.). В этом случае можно использовать текстовые функции СЖПРОБЕЛЫ (TRIM) и ПЕЧСИМВ (CLEAN) для их удаления:
=ВПР(СЖПРОБЕЛЫ(ПЕЧСИМВ(B3));прайс;ЛОЖЬ)
=VLOOKUP(TRIM(CLEAN(B3));прайс;FALSE)

P.S.2

Для подавления сообщения об ошибке #Н/Д (#N/A) в тех случаях, когда функция не может найти точно соответствия, можно воспользоваться вот такой конструкцией:

Функция ЕНД (ISNA) проверяет - не возникла ли ошибка #Н/Д как результат работы ВПР и если да, то выводит пустую строку ("") или ноль, а если нет - то выводит результат работы ВПР.

В Excel 2007/2010 для подавления сообщения об ошибке можно воспользоваться новой функцией ЕСЛИОШИБКА (IFERROR). Так, например, вот такая конструкция перехватывает любые ошибки создаваемые ВПР и заменяет их нулями:

=ЕСЛИОШИБКА(ВПР(B3;прайс;2;ЛОЖЬ);0)

=IFERROR(VLOOKUP(B3;прайс;2;FALSE);0)

P.S.3

 

Использование функции ВПР с примерами

Posted on 17.05.2011 by l-o-r-y

Функция подстановки значений (ВПР)

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

Так же мы имеем в отдельном файле прайс-лист на рекламную продукции.

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

Обратите внимание на то, что для корректной работы функции ВПР в заголовках таблицы не должно быть объединенных ячеек! Выберем ячейку в которую будем подставлять значения, взятые из другого файла. В начем случае это будет колонка «Цена». Выделим ячейку D3 (первую ячейку диапазона, в который необходимо подставить значения). В закладке Формулы найдем кнопку f_x и нажмем ее. Появится диалоговое окно мастера фукнций.

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

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

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

В поле ввода аргумента «Номер столбца» указываем «2», т.к. колонка с ценой в прайс-листе у нас — это второй по счету столбец.

В поле «Интервальный просмотр» пишем ЛОЖЬ, т.к. нам необходимо перенести точные значения, а не приблизительные

Нажмем ОК.

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

Вот что у нас должно получиться.

В принципе можно было бы сказать, что на этом задача выполнена. Но есть одно маленькое НО. В столбце D3 работает функция, мы можем видеть это в строке формул. Это означает, что наши 2 файла неразрывно связаны, т.е. если поменять данные в прайс-листе, то обязательно изменятся и данные в нашем файле, за 15.01.2011. Этого лучше не допускать. Для этого выделим весь диапазон ячеек, в который мы подставили данные, щелкнем правой кнопкой мыши и выберем опцию «Копировать»

Далее не сбрасывая выделения с области ячеек снова щелкнем по ней правой кнопкой мыши и выберем опцию «Специальная вставка».

В появившемся окне установим галочку напротив опции «значения». Нажмем ОК.

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

This entry was posted in Уроки Excel and tagged функция ВПР, функция подстановки значений. Bookmark the

 

Microsoft Office Excel 2003

Статья написана Ашишем Матуром, специалистом со статусом Microsoft MVP (Most Valuable Professional). Дополнительные сведения см. на сайте Microsoft MVP.

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

Функция ВПР

Функция ВПР выполняет поиск значения в крайнем левом столбце таблицы и возвращает значение из той же строки в указанном столбце. (Буква В в названии функции ВПР означает "вертикально".)

Чтобы продемонстрировать работу функции ВПР, воспользуемся данными в диапазоне B3:C11 — см. рисунок ниже — и найдем почасовые ставки в диапазоне F3:F11 для имен в диапазоне E3:E11. Обратите внимание на то, что порядок имен в диапазоне E3:E11 не совпадает с порядком имен в диапазоне B3:B11.

Здесь используется простая логика: найти для имени в диапазоне E3:E11 соответствие в диапазоне B3:B11, после чего вернуть почасовую ставку для этого имени из диапазона C3:C11 и скопировать ее в диапазон F3:F11.

Для решения этой задачи используется следующая формула:

=ВПР(E3;$B$3:$C$11;2;ЛОЖЬ)

Чтобы понять эту формулу, рассмотрим синтаксис функции ВПР:

ВПР(искомое_значение;таблица;номер_столбца;интервальный_просмотр)

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

· Искомое_значение Это ячейка, содержащая искомое значение. Таким образом, для ячейки F3 искомое_значение — это ячейка E3.

· Таблица В данном случае таблица — это диапазон, содержащий как искомые, так и возвращаемые данные. В нашем примере диапазон B3:C11 представляет собой список, из которого берутся почасовые ставки.

· Номер_столбца Это номер столбца в диапазоне $B$3:$C$11, в котором хранятся возвращаемые данные. В нашем примере почасовые ставки хранятся в столбце 2.

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

Ошибки, возникающие при вставке или удалении данных

Давайте теперь усложним формулу.