000818.ru

Финансовые и Бух. услуги
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как найти первое ненулевое значение и вернуть соответствующий заголовок столбца в Excel?

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

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

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

Функция ВПР доступна во всех версиях Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003.

Синтаксис функции VPR выглядит следующим образом

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

  1. искомое_значение – это значение для поиска.

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

  • Поиск числа : =ВПР(40; A2:B15; 2) — формула будет искать число 40.
  • Поиск текста : =ВПР(«яблоки»; A2:B15; 2) — формула будет искать текст «яблоки». Обратите внимание, что вы всегда включаете текстовые значения в «двойные кавычки».
  • Поиск значения из другой ячейки : =ВПР(C2; A2:B15; 2) — формула будет искать значение в ячейке C2.
  1. Таблица состоит из двух и более столбцов данных.

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

Читайте так же:
Как объединить три ячейки для создания даты в Excel?

Итак, наша формула =ВПР(40; A2:B15; 2) будет искать «40» в ячейках от A2 до A15, потому что A — это первый столбец таблицы A2: B15.

  1. Column_number — номер колонки таблицы, из которой должно быть возвращено значение соответствующей строки.

Как видно из приведенной выше таблицы, самый левый столбец — 1, второй — 2, третий — 3 и т.д.

Итак, теперь вы можете прочитать всю формулу =ВПР(40; A2:B15; 2). Формула ищет «40» в ячейках от A2 до A15 и возвращает соответствующее значение из столбца B (потому что B является вторым столбцом в указанной таблице A2:B15).

Interval_view определяет, ищете ли вы точное совпадение (FALSE) или приблизительное (TRUE или опущено). Это необязательный параметр, но очень важный.

Способы именования ячеек

morskoy-boy

В Excel обычно используется способ именования ячеек, в котором адрес ячейки формируется из номера строки и буквы столбца. Так, например, A3 – ячейка, расположенная на пересечении столбца A и строки 3. Такой способ называется стиль ссылок A1. Он очень похож на то, как мы называем клетки во время игры в «морской бой».

Помимо обычного способа адресации, существует альтернативный способ, при котором и столбцы, и строки называются числами. Например, адрес ячейки A3 будет выглядеть следующим образом: Ряд 3, колонка 1. Краткая форма адреса — R3C1, где R и C означают строку и столбец. Такой способ адресации называется эталонным стилем R1C1.

Ошибки

Когда пользователь допускает ошибку при вводе данных или выборе диапазона, вместо результата отображаются различные ошибки: #N/D, #VALUE, #LINK.

Ошибка #H/D появляется в том случае, если:

  1. Указанный диапазон не содержит искомого элемента.
  2. Искомый элемент меньше минимального значения диапазона.
  3. Задан точный поиск (аргумент «FALSE» или 0), но искомый элемент не входит в диапазон.
  4. Задан приблизительный поиск («TRUE» или 1 аргумент), но данные не сортируются в порядке возрастания.
  5. Формат (числовой и текстовый) ячейки, из которой поступает искомый элемент, и ячейки, содержащей данные первого столбца, различаются.
  6. В коде присутствуют пробелы или непечатаемые невидимые символы.
  7. Используются временные значения или большие десятичные числа.
Читайте так же:
Как отбросить самую низкую оценку и получить среднее или общее значение в Excel?

Чтобы избежать ошибки #H/D, когда FIR не находит значения, рекомендуется использовать следующую формулу: =ELIOS ERROR(VPR(C2;A1:B12;2;FALSE);0) — Вместо 0 можно написать «не найдено».

Ошибка #NAME возникает, если

  1. В качестве номера столбца указано число 0.
  2. Длина первого аргумента превышает 255 знаков.

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

Поиск и замена данных

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

Заменить

Еще более удобной является горячая клавиша для поиска и замены в Excel — Ctrl + H.

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

Диалоговое окно Заменить

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

Заменить формат

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

Напоследок рассмотрим один классный трюк с поиском и заменой. Многие знают, что в ячейку можно вставить разрыв строк с помощью комбинации Alt+Enter.
Разделение строк в ячейке
А как быстро удалить все разрывы строк? Обычно это делают вручную. Однако ловкое использование поиска и замены сэкономит много времени. Вызываем команду поиска и замены с помощью комбинации Ctrl+H. Теперь в строке поиска нажимаем Ctrl+J — это символ разрыва строки — на экране появится точка. В строке замены указываем, например, пробел.

Читайте так же:
Как напрямую перейти к следующей повторяющейся ячейке в Excel?

Поиск и замена разрывов строк в ячейке

Нажмите OK. Все переносы строк заменяются пробелами.

Замена разделителей строк пробелами

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

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

Итоги

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

Синтаксис: =FRP(требуемое значение;таблица;номер столбца;вид интервала).

Функцию можно ввести вручную или в специальном окне (Shift+F3).

Искомое значение – относительная ссылка, а таблица – абсолютная.

Соответствующее значение может быть точным или приблизительным при использовании Интервального просмотра.

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

Новости

Для подписчиков Microsoft 365 с июля 2020 года доступна новая функция PROSWER (XLOOKUP), которая призвана заменить функции VPR и GPR. XLOOKUP отличается от функции VPR тем, что использует отдельные массивы уточняющих запросов и возвращаемых значений, тогда как функция VPR использует один массив, за которым следует номер столбца.

Например, старая запись UPP (B3; B3:D7; 3; false) будет соответствовать новой записи: PROSECUTION (B3; B2:B7; D2:D7) для следующей таблицы:

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

Читайте так же:
Как настроить гистограмму, чтобы сделать столбцы шире в Excel?

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector