Как в экселе впр: Краткий справочник: функция ВПР — Служба поддержки Майкрософт

ВПР и числа-как-текст

49129
26.05.2020
Скачать пример


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


Рассмотрим классический пример — подстановку цен из прайс-листа в таблицу заказов по совпадению артикулов:


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


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


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

Вариант 1. Числа-как-текст в искомых значениях


Предположим для начала, что псевдочисла эпизодически встречаются у нас в искомых значениях, т.е. в таблице заказов (диапазон B4:B7) и перемешаны с нормальными числовыми артикулами, что приводит к появлению ошибок.


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

  • умножения или деления на 1

  • прибавления или вычитания 0

  • двойного знака минус перед артикулом (равносильно двойному умножению на -1)


Значение артикула от выполнения такой безобидной математической операции никак не изменится, но сам факт её выполнения заставит Excel воспринимать артикул именно как число. А значит и ВПР найдет текстовые значения без проблем:



Вариант 2. Числа-как-текст в таблице, где ищем


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


Самым простым и компактным вариантом будет приклеивание к артикулу пустой строки:



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

Вариант 3. Пропадание начальных нулей


Частным случаем предыдущего примера бывает сценарий, когда артикулы в прайс-листе не просто текстовые, но ещё и содержат нули в начале строк, дополняющие их до заданной разрядности. Тут поможет функция ТЕКСТ (TEXT), которая умеет любое число выводить как текст по заданному шаблону:


Вариант 4.

Числа-как-текст вперемешку с числами в обеих таблицах


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


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



Вот и всё — и никаких больше ошибок :)

Ссылки по теме

  • Как выполнить левый ВПР

  • Многоразовый ВПР, извлекающий сразу все значения

  • Функция ПРОСМОТРХ как преемник ВПР

Как использовать функцию ВПР — devsap.ru

МЅ Excel: как использовать функцию ВПР

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

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

Синтаксис

Синтаксис функции ВПР в Microsoft Excel-это:

ВПР( значение, Таблица, номер_индекса, [approximate_match] )
Параметры или Аргументы

значение
Значение для поиска в первом столбце таблицы.
Таблица
Два или более столбцов данных, отсортированных по возрастанию.
номер_индекса
Номер столбца в таблице, из которой соответствующее значение должно быть возвращено. Первый столбец 1.
истина или ложь
Необязательно. Введите значение ЛОЖЬ (0), чтобы найти Точное совпадение. Введите ИСТИНА (1), чтобы найти приблизительное совпадение. Если этот параметр опущен, ИСТИНА  по умолчанию.

Примечание

  • Если вы укажите значение ЛОЖЬ для параметра approximate_match и точное соответствие не найдено, то функция ВПР возвращает значение #Н/А.
  • Если вы укажите значение ИСТИНА для параметра approximate_match и точное соответствие не найдено, то следующее меньшее значение возвращается.
  • Если номер_индекса меньше 1, функция ВПР возвращает значение ошибки #знач!.
  • Если номер_индекса больше, чем количество столбцов в таблице, то функция ВПР возвращает ошибку #ссылка!.
  • См. также функция ГПР выполнять горизонтальный поиск.

Пример (как функция)

ВПР как функция листа в Excel.

Nаблицы Excel примеры ВПР:

=ВПР(10251, А1:В6, 2, ложь)
Результат: «значение возвращает груши» в 2-м столбце

=ВПР(10251, А1:С6, 3, ложь)
Результат: возвращает значение $18.60 в 3-й колонке

=ВПР(10251, А1:Д6, 4, ложные)
Результат: 9 ‘возвращает значение в 4-м столбце

=ВПР(10248, А1:В6, 2, ложь)
Результат: возвращает #н/д ‘ошибки #н/д (Точное совпадение)

=ВПР(10248, А1:В6, 2, истина)
Результат: «яблоки» ‘возвращает приблизительное совпадение

Теперь, давайте посмотрим на пример =ВПР(10251, А1:В6, 2, false), которое возвращает значение «груши» и присмотреться, почему.

Первый Параметр

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

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

=ВПР(«10251», А1:В6, 2, ложь)
Второй Параметр

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

В данном примере второй параметр А1:В6, которые дает нам два столбца данных для использования в вертикальном поиска — А1:А6 и В1:В6. Первый столбец в диапазоне (А1:А6) используется для поиска значения порядка 10251. Во втором столбце в диапазоне (В1:В6) содержится значение, возвращаемое значение продукта.

Третий Параметр

Третий параметр-номер позиции в таблице, где возвращают данные можно найти. Значение 1 указывает, что первый столбец в таблице. Второй столбец 2, и так далее.

В этом примере третий параметр-2. Это означает, что второй столбец в таблице, где мы найдете значение возвращать. Поскольку диапазон таблицы имеет значение А1:В6, возвращаемое значение будет во второй колонке где-то в диапазоне В1:В6.

Четвертый Параметр

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

В данном примере четвертый параметр имеет значение false. Параметр false означает, что функция ВПР ищет точное соответствие для значения 10251. Параметр True означает, что «закрыть» матч будет возвращена. С ВПР может найти значение 10251 в диапазоне А1:А6, она возвращает соответствующее значение из В1:В6, которые это груши.

Точное соответствие и приблизительное совпадение

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

Давайте подстановки значение, которое не существует в наши данные демонстрируют важность этого параметра!

Точное Совпадение

Используйте false, чтобы найти Точное совпадение:

=ВПР(10248, А1:В6, 2, ложь)
Результат: #Н/Д

Если нет точного совпадения, #н/д возвращается.

Приблизительное Совпадение

Используйте значение True, чтобы найти приблизительное совпадение:

=ВПР(10248, А1:В6, 2, истина)
Результат: «Яблоки»

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

ВПР из другого листа

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

Мы можем переписать наш оригинальный пример, в котором мы найти значение 10251 следующим образом:

=ВПР(10251, Лист2!А1:В6, 2, ложь)

Перед таблицы с имя листа и восклицательный знак, мы можем обновить нашу vlookup для обращения к таблице на другом листе.

ВПР из другого листа с пробелами в имени листа

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

Предположим, что Таблица на листе под названием «лист-тест» в диапазоне А1:В6, теперь нам нужно завернуть имя листа в одинарные кавычки, как показано ниже:

=ВПР(10251, ‘лист испытаний’!А1:В6, 2, ложь)

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

ВПР из другой книги

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

=ВПР(10251, и ‘C:[data.xlsx]Лист1’!$А$1:$В$6, 2, ЛОЖЬ)

Это будет искать значение 10251 в C:data.xlxs файл в 1 лист, где данные таблицы находится в диапазоне $A$1:$В$6.

Зачем использовать абсолютные ссылки?

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

Так что если вы имели следующую формулу в ячейку G1:

=ВПР(10251, А1:В6, 2, ложь)

И тогда вы скопировать эту формулу из ячейки G1 в ячейку Н2, он внесет изменения в формулу vlookup для этого:

=ВПР(10251, В2:С7, 2, ложь)

Так как ваша Таблица находится в диапазоне A1:B6 и не В2:С7, ваша формула будет возвращать неверные результаты в ячейке h3. Чтобы убедиться, что ваш выбор не меняется, попробуйте, ссылающиеся на таблицы с использованием абсолютной ссылки следующим образом:

=ВПР(10251, $А$1:$B В 6$, 2, ЛОЖЬ)

Теперь, если скопировать эту формулу в другую ячейку, диапазон таблицы будет оставаться $в$1:$В$6.

Как справиться с #Н/ошибки

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

Например, если у вас следующие формулы:

=ВПР(10248, $А$1:$B В 6$, 2, ЛОЖЬ)

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

=Если(ЕОШ(ВПР(10248, $в$1:$B в 6$, 2, ложь)), «не нашли», ВПР(10248, $в$1:$В$6, 2, ложь))

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

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

 

Формула Excel LIKE, AND, IF, WILDCARDS

спросил

Изменено
2 года, 1 месяц назад

Просмотрено
148 тысяч раз

У меня есть сценарий, в котором я использую несколько удобных для меня формул вместе и не получаю результата. Я хочу получить ЛЮБОЙ результат, где в ячейке присутствует «1». (1 является результатом формулы). А также там, где текст определенного столбца содержит &. («&/ИЛИ»)

Я пробовал пару формул

 =ЕСЛИ(И(I1=1,C2="*"&$Q$1&"*")),1," ")

— В этом я попытался поместить & в ячейку и сослаться на нее

 = ЕСЛИ (I1 = 1,1, " ")

и затем в новом столбце

 =ЕСЛИ(C2="*"&"/"&"*",1," ")

Затем объединить результаты двух? Кто-нибудь заметил, что с ним не так??

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

=A1="*&*"

, который будет рассматривать * как буквальные звездочки (не подстановочные знаки), поэтому будет возвращаться только TRUE , если A1 буквально содержит *&*

28 может использовать функцию

COUNTIF даже для одной ячейки, например.

=СЧЕТЕСЛИ(A1,"*&*")

Это вернет 1, если A1 содержит и , поэтому для ваших целей:

=ЕСЛИ(И(I1=1,СЧЁТЕСЛИ($G$1) ,"*&*")),1,"")

2

СЧЁТЕСЛИМН также можно использовать для проверки нескольких условий:

 =СЧЁТЕСЛИМН(A1,"слово1",A2,"слово2")

слово1, слово2 может содержать подстановочные знаки, если необходимо

или что-то вроде

 =ЕСЛИ(И(I1=1,НЕ(ОШИБКА(НАЙТИ("&",$G$1)))),1," ")

любой вариант этого действительно. ..

0

Зарегистрируйтесь или войдите в систему

Зарегистрируйтесь с помощью Google

Зарегистрироваться через Facebook

Зарегистрируйтесь, используя адрес электронной почты и пароль

Опубликовать как гость

Электронная почта

Обязательно, но не отображается

Опубликовать как гость

Электронная почта

Требуется, но не отображается

Функция Excel для выполнения SQL-подобных запросов к данным рабочего листа?

У меня есть большая таблица на листе Excel:

 Column_1 | Столбец_2 | Столбец_3
ЗначениеA ЗначениеB ЗначениеC
. ...

Мне нужна функция, которая будет принимать в качестве входных данных диапазон и SQL-подобную строку запроса и возвращать диапазон строк, соответствующих запросу, например:

 =SQL_SELECT(A1:C1000, "SELECT * WHERE Column_1 = ValueH AND Column_3 = blah")

Существует ли нечто подобное? Или что было бы лучшим способом реализовать себя?

  • sql
  • excel
  • vba
  • функция

3

Вы можете использовать Получить внешние данные (несмотря на его название), расположенные на вкладке «Данные» в Excel 2010, чтобы настроить соединение в книге для запроса данных от себя. Используйте Из других источников Из Microsoft Query для подключения к Excel

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

Вот быстрый Sub для демонстрации доступа к объектам соединения

 Sub DemoConnection()
    Dim c As соединения
    Dim wb как рабочая книга
    Дим и пока
    Dim strSQL как строка
    
    Установите wb = ActiveWorkbook
    Установите c = wb.Connections
    Для i = 1 To c.Count
        ' Обновить данные
        c(i).Обновить
        ' просмотреть SQL-запрос
        strSQL = c(i).ODBCConnection.CommandText
        MsgBox стрSQL
    Следующий
Конец сабвуфера

7

Если вы можете сохранить книгу, у вас есть возможность использовать ADO и Jet/ACE для обработки книги как базы данных и выполнения SQL для листа.

Информацию MSDN о том, как попасть в Excel с помощью ADO, можно найти здесь.

3

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

Читайте также: