Vlookup по русски: Vlookup Excel: как пользоваться (примеры)

Excel Functions in Russian | Easy-Excel.com

функции excel на русском

These are the 140 most common functions in Excel with Russian translations. This list is very useful if you are used to the English version of Excel, and you are working with a Russian version that only accepts the Russian function names in the formulas. Or the other way around!

Normally you would also have to replace all the commas in my example formulas with semi-colon.

Do you need a complete list of all the function names in Russian in Excel? Scroll down to the bottom of this page to read more about Microsoft’s Functions Translator add-in.

English	Russian
ABS	ABS
ADDRESS	АДРЕС
AGGREGATE	АГРЕГАТ
AND	И
ARABIC	АРАБСКОЕ
AVERAGE	СРЗНАЧ
AVERAGEIF	СРЗНАЧЕСЛИ
AVERAGEIFS	СРЗНАЧЕСЛИМН
BAHTTEXT	БАТТЕКСТ
BIN2DEC	ДВ. В.ДЕС
BIN2HEX	ДВ.В.ШЕСТН
CEILING	ОКРВВЕРХ
CEILING.MATH	ОКРВВЕРХ.МАТ
CELL	ЯЧЕЙКА
CHAR	СИМВОЛ
CHOOSE	ВЫБОР
CODE	КОДСИМВ
COLUMN	СТОЛБЕЦ
COLUMNS	ЧИСЛСТОЛБ
CONCAT	СЦЕП
CONCATENATE	СЦЕПИТЬ
CONVERT	ПРЕОБР
COUNT	СЧЁТ
COUNTA	СЧЁТЗ
COUNTBLANK	СЧИТАТЬПУСТОТЫ
COUNTIF	СЧЁТЕСЛИ
COUNTIFS	СЧЁТЕСЛИМН
DATE	ДАТА
DATEVALUE	ДАТАЗНАЧ
DAY	ДЕНЬ
DAYS	ДНИ
DEC2BIN	ДЕС.В.ДВ
DEC2HEX	ДЕС.В.ШЕСТН
EOMONTH	КОНМЕСЯЦА
EVEN	ЧЁТН
EXACT	СОВПАД
FACT	ФАКТР
FILTERXML	ФИЛЬТР. XML
FIND	НАЙТИ
FLOOR	ОКРВНИЗ
FLOOR.MATH	ОКРВНИЗ.МАТ
FORMULATEXT	Ф.ТЕКСТ
FREQUENCY	ЧАСТОТА
HEX2BIN	ШЕСТН.В.ДВ
HEX2DEC	ШЕСТН.В.ДЕС
HLOOKUP	ГПР
HYPERLINK	ГИПЕРССЫЛКА
IF	ЕСЛИ
IFERROR	ЕСЛИОШИБКА
IFS	УСЛОВИЯ
INDEX	ИНДЕКС
INDIRECT	ДВССЫЛ
INFO	ИНФОРМ
INT	ЦЕЛОЕ
ISBLANK	ЕПУСТО
ISERROR	ЕОШИБКА
ISEVEN	ЕЧЁТН
ISLOGICAL	ЕЛОГИЧ
ISNA	ЕНД
ISNONTEXT	ЕНЕТЕКСТ
ISNUMBER	ЕЧИСЛО
ISODD	ЕНЕЧЁТ
ISOWEEKNUM	НОМНЕДЕЛИ. ISO
ISTEXT	ЕТЕКСТ
LARGE	НАИБОЛЬШИЙ
LEFT	ЛЕВСИМВ
LEN	ДЛСТР
LOOKUP	ПРОСМОТР
LOWER	СТРОЧН
MATCH	ПОИСКПОЗ
MAX	МАКС
MAXIFS	МАКСЕСЛИ
MEDIAN	МЕДИАНА
MID	ПСТР
MIN	МИН
MINIFS	МИНЕСЛИ
MOD	ОСТАТ
MODE	МОДА
MONTH	МЕСЯЦ
MROUND	ОКРУГЛТ
N	Ч
NA	НД
NETWORKDAYS	ЧИСТРАБДНИ
NETWORKDAYS.INTL	ЧИСТРАБДНИ.МЕЖД
NOT	НЕ
NOW	ТДАТА
ODD	НЕЧЁТ
OFFSET	СМЕЩ
OR	ИЛИ
PERCENTILE	ПЕРСЕНТИЛЬ
PERCENTILE. EXC	ПРОЦЕНТИЛЬ.ИСКЛ
PERCENTILE.INC	ПРОЦЕНТИЛЬ.ВКЛ
PERCENTRANK	ПРОЦЕНТРАНГ
PERCENTRANK.EXC	ПРОЦЕНТРАНГ.ИСКЛ
PERCENTRANK.INC	ПРОЦЕНТРАНГ.ВКЛ
PI	ПИ
PMT	ПЛТ
PROPER	ПРОПНАЧ
QUARTILE	КВАРТИЛЬ
QUARTILE.EXC	КВАРТИЛЬ.ИСКЛ
QUARTILE.INC	КВАРТИЛЬ.ВКЛ
RAND	СЛЧИС
RANDBETWEEN	СЛУЧМЕЖДУ
RANK	РАНГ
RANK.AVG	РАНГ.СР
RANK.EQ	РАНГ.РВ
REPLACE	ЗАМЕНИТЬ
REPT	ПОВТОР
RIGHT	ПРАВСИМВ
ROMAN	РИМСКОЕ
ROUND	ОКРУГЛ
ROUNDDOWN	ОКРУГЛВНИЗ
ROUNDUP	ОКРУГЛВВЕРХ
ROW	СТРОКА
ROWS	ЧСТРОК
SEARCH	ПОИСК
SMALL	НАИМЕНЬШИЙ
STDEV	СТАНДОТКЛОН
STDEV. P	СТАНДОТКЛОН.Г
STDEV.S	СТАНДОТКЛОН.В
SUBSTITUTE	ПОДСТАВИТЬ
SUBTOTAL	ПРОМЕЖУТОЧНЫЕ.ИТОГИ
SUM	СУММ
SUMIF	СУММЕСЛИ
SUMIFS	СУММЕСЛИМН
SUMPRODUCT	СУММПРОИЗВ
SWITCH	ПЕРЕКЛЮЧ
TEXT	ТЕКСТ
TEXTJOIN	ОБЪЕДИНИТЬ
TODAY	СЕГОДНЯ
TRANSPOSE	ТРАНСП
TRIM	СЖПРОБЕЛЫ
UNICHAR	ЮНИСИМВ
UNICODE	UNICODE
UPPER	ПРОПИСН
VLOOKUP	ВПР
WEEKDAY	ДЕНЬНЕД
WEEKNUM	НОМНЕДЕЛИ
YEAR	ГОД
FALSE	ЛОЖЬ
TRUE	ИСТИНА

Yes, there is an add-in called the Functions Translator add-in that you can install in a few seconds:

Go to Insert > Get Add-ins to open the add-in dialog. Choose Productivity under Category, find the Functions Translator add-in and click on the Add button.

Click on picture to magnify

Now you will find the Functions Translator on the Home tab to the right.

Use it to look up a functions name in a list (Reference):

Or use the Translator feature to translate a whole formula:

Базовые советы для удобной работы в Google Spreadsheets

Александр Максименюк, основатель и CVO Ringostat, еще со времен университета активно работает с таблицами. Поэтому к нему обращаются за советом, как написать формулу или обработать данные. По его словам, самая частая ошибка — это слишком «тяжелые» решения для задач, которые можно решить проще. Или когда вручную делается то, что можно автоматизировать. Для тех, кто работает с таблицами Google и хотел бы делать это лучше, Александр подготовил подборку советов — от простых к сложным.

Время чтения 14 минут

1. Английский интерфейс и американская локаль
2. Проверка данных (валидация)
3. Нейминг
4. Оформление  
5. Работа с формулами
   • Общие советы для начинающих
   • Модификаторы формул
   • ARRAYFORMULA
   • Формулы, которые важно помнить
   • Полезные фишки
6. Если документ тяжелый и с ним неудобно работать
7. Лимиты

 Здесь мы не будем разбирать, как создать Google Таблицу или вписывать в нее информацию. Инструкции на эту тему можно легко найти. В этой статье мы поговорим про неочевидные лайфхаки при работе с Google Sheets.

Английский интерфейс и американская локаль

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

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

Проверка данных (валидация)

Особенно важна, когда разные сотрудники вручную вносят данные в документ — а значит, могут делать ошибки. Валидация настраивается в разделе Data — Data Validation и бывает двух типов:

• нестрогая валидация — если ввести данные в некорректном формате, появится окошко с сообщением об ошибке;
• строгая валидация — неправильные данные вообще не получится ввести.

Настройка валидации

Нейминг

Называйте таблицу максимально понятно — причем не только вам, но и тем людям, которым к ней откроете доступ. Так вы избежите ситуации, когда вам нужно что-то срочно найти, а в списке открываются сплошные «новые документы». Пример названия: «Отчет об эффективности сотрудников / Иванов / Май 2018 / Компания N».

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

Оформление  

• цифры и деньги размещаем по правому краю столбца с одинаковым округлением и разделителем между разрядами — так легко определить цифры другого порядка;
• текст по левому краю столбца — мы читаем слева направо, поэтому так удобней;
• дата по центру;
• закрепляем верхние строки столбца, если документ не помещается на один экран; 
• удаляем пустые ячейки и столбцы с помощью Crop Sheet — так вы будете уверены, что в документе точно нет других данных, которые просто не видны, и не достигните лимита в 400 000 ячеек на лист.

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

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

Работа с формулами

Приучайте себя делать все расчеты с использованием формул — даже если нужно посчитать 2+2. Расчетные (зависимые) величины могут быть без формул только в двух случаях:

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

Общие советы для начинающих

1. Смотрите на подсказки. Они появляются, как только начинаешь набирать формулу, а при нажатии на подсказку появляется более развернутое описание. В нем описаны примеры и описания условий и значений.  
2. Придумывайте формулы, используя базовые знания. Необязательно помнить сотни формул — иногда можно самостоятельно «собрать» то, что вам нужно. Для этого достаточно знать английский на уровне Pre-Intermediate. Например, есть документ с данными по блогу — общие (general), русскоязычная версия (ru), англоязычная (en). Необходимо транспонировать массив (об этом будет ниже) только по общим данным. Тогда формула будет выглядеть так: =TRANSPOSE(FILTER(A1:X22,A1:А = «general»)). Где: A1:X22 — весь; A1:A = blog_general — условие 1).
3. Ищите готовое решение на Stack Overflow. Там есть решения для большинства задач в нескольких вариантах, и оптимальный вариант обычно отмечен галочкой. И еще один аргумент в пользу англоязычного интерфейса — в Stack Overflow все заточено под него. Если вставите решение оттуда в русскую локаль, то придется разбираться, где запятая, а где все же точка с запятой.
4. Используйте F4, чтобы быстрее менять фиксированные ссылки в в формулах. Если выделить относительную ссылку и нажать один раз эту клавишу, знак $ проставится автоматически. Но учитывайте, что это не подойдет для смешанных ссылок —пример, $A1 или A$1, они наполовину относительные, наполовину абсолютные. 

Название всех формул и их описания читайте в справке Google.

Модификаторы формул

IF и IFS работают для множества функций, но нужно помнить хотя бы основные:

• SUM — сумма;
• AVERAGE — среднее значение;
• COUNT — подсчет количества, например, ячеек, символов в строке, но не их содержимого.

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

Еще одна полезная формула — UNIQUE, которая возвращает уникальные строки в указанном диапазоне, убирая дубликаты. На примере ниже таблица с источниками и каналами, названия которых дублируются. Нужно посчитать количество сессий и коэффициент конверсии для каждого из них. При этом нам нужно суммировать данные по каждому из каналов — например, все сессии из google cpc. Что мы делаем:

• выбираем уникальные значения с помощью UNIQUE;
• с помощью SUMIF проставляем условие, что суммируются все значения, если utm_source — google, а utm_medium — cpc;
• используем $, чтобы закрепить диапазон.

ARRAYFORMULA

Отображает значения, полученные с помощью формулы массива, в нескольких строках и столбцах. При своей простоте, сильно упрощает работу с таблицами. Допустим, есть большой массив данных, который нужно обработать — посчитать conversion rate на примере ниже. Мы могли бы значение одной ячейки поделить на другую и растянуть.

В чем преимущество ARRAYFORMULA:

• скорость;
• не расходуется лимит на количество формул — об этом будет ниже;
• удобство — вписываем диапазоны, нажимаем Enter, и формула все посчитала и растянула.

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

Представим, документ, где приведено количество переходов из всех источников. Нас интересуют именно переходы из Facebook. При этом в документе есть пять меток со словом Facebook (например, Facebook_buisness) и еще три с сокращением Fb (Fb_123). Благодаря подвиду этой формулы REGEXMATCH, мы можем суммировать показатели, где в тексте есть упоминание слова Facebook и Fb.

С ARRAYFORMULA можно использовать любые формулы, кроме INDEX, SEARCH, QUERY, в некоторых случаях некорректно работает SUM и т. д.. Когда выделяете два или несколько массивов, они должны быть одинакового размера.

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

Формулы, которые важно помнить

1. IMPORTRANGE — позволяет импортировать диапазон данных из документа, к которому есть доступ.
2. SPLIT — разбивает текстовые данные по определенному разделителю. Например, у нас есть документ для калькуляции приоретизации features Ringostat. В одной ячейке забит ID задачи и ее описание из системы для управления проектами. В соседнюю ячейку мы затягиваем все до пробела — т. е. только ID. Дополнительно по нему можно сформировать ссылку (формула HYPERLINK) с помощью формулы CONCATENATE.
3. UNIQUE — возвращает уникальные строки в указанном диапазоне, убирая дубликаты. Результат является массивом. В UNIQUE можно вставить двумерный массив и выбрать в нем уникальные строки.
4. VLOOKUP и HLOOKUP — находит значения по ключу в определенной ячейке диапазона. Вернемся к примеру из пункта 2. Есть документ с ID, названием и описанием feature, ее score. Формула позволяет оттуда вытянуть название feature по списку ключей.
5. TRANSPOSE — транспонировать массив, т. е. развернуть. Допустим, есть массив, в котором заголовки в первой строке, а нужно сделать так, чтобы они были столбцом. Так, данные которые располагались слева направо, перемещаются в столбец — сверху вниз. Транспонировать диапазон можно также с помощью специальной вставки paste special.
6. IF — нужен в случаях, когда SUMIF и SUMIFS недостаточно.
7. SORT — сортировка. Например, данные на листе нужно отсортировать по определенному критерию. В том же документе с калькуляцией скоринга features мы затягиваем массив из другого документа. Сначала убираем из него дубликаты с помощью UNIQUE, а потом сортируем по score.

Полезные фишки

1. {A1:A20,C1:C20} {A1:A20;C1:C20} — дают единый массив, при этом результат зависит от разделителя. Вернемся к примеру с таблицей для приоритизации features. Нас интересуют стоблцы A, B и M. Проблема в том, что нельзя импортировать этот диапазон и вставить без столбцов — а объединение массивов это позволяет. Но опять-таки оба варианта работают в английской локали. Вариант с запятой даст горизонтальное объединение массивов, а при точке с запятой объединит данные в один столбец. В русской локали варианта с запятой не существует.
2. A1&B1 — объединяет значения из нескольких строк в одну. Можно еще и так A1&» «&B1.
3. «Some text» — текст оформляется двойными кавычками. Если вдруг вы используете апострофы в качестве «одинарных кавычек», учитывайте, что в Google Spreadsheets они применяются для других целей.
4. Если при FALSE результате в рамках функции IF нужно вывести пустую строку, не забывайте добавить в конце разделитель =if(D2>20,true,) иначе будет подставляться FALSE.
5. Если формула проставляется наперед и содержит деление, в пустых ячейках отобразится NA/0. Чтобы сделать «красивее» и вывести вместо этого пустую ячейку, формулу заворачивают в IFERROR =iferror(ARRAYFORMULA(E2:E/D2:D)).
6. CTRL(CMD — для Mac) + SHIFT + V — вставить значения. Нужен, если копируете ячейки, где есть формулы, и хотите вставить просто значение. Также убирается форматирование при вставке.
7. Строка редактирования формул растягивается по высоте. Для переноса строк при создании или редактировании формул используется CTRL(CMD) + Enter, перенос строки не считается символом. Так намного удобнее редактировать, особенно когда большая формула.
8. F2 на ячейке открывает ее редактирование. Это особенно применимо, если вы работаете на клавиатуре и не хотите постоянно тянуться за мышкой.
9. ≠ записывается как <>. Например, если вам нужно найти все значения, которые не равны определенному содержимому. Например, google cpc.
10. =sum(arrayformula(if(REGEXMATCH(C2:C10,»some regEx»),D2:D10,0))) — это нужно понять для того, чтобы открылся третий глаз при работе в Spreadsheets 🙂 Александр говорит, что уже встречал много подобных кейсов.

Если документ тяжелый и с ним неудобно работать

1. Используйте более легкие функции. Например, QUERY очень тяжелая. Да, у нее больше возможностей, но во многих случаях можно обойтись и VLOOKUP для тех же целей.
2. Уменьшите количество IMPORTRANGE. При импорте данных из нескольких документов, каждый раз когда рендерится документ, формула берет данные оттуда, вставляет, пересчитывает и т. д. С точки зрения нагрузки, куда проще сформировать в первом документе итоговый лист со всеми значениями для импорта. А во втором создать такой же лист и импортировать туда все нужные данные. В итоге данные затянутся за один раз, и не будет пяти открытий исходного документа. Дальше эту информацию можно поместить на нужные листы для работы, например, пользуюсь ARRAYFORMULA.  
3. Используйте оптимальные решения. Тут помогают выводы из собственной практики. Например, при большом количестве данных =IFERROR(B1/A1) «легче», чем =IF(B17<>0,C17/B17,).
4. Перейдите на внешнюю агрегацию данных. Перенесите их в BigQuery или другую базу данных, там вы можете обрабатывать данные, а Google Spreadsheets импортировать готовые значения. Для этого удобно использовать Google Apps Script (GScript).
5. Уберите формулы из ячеек, которые не должны пересматриваться. Например, данные старше трех месяцев. Нажимаем CTRL + C, а потом CTRL + SHIFT + V на тех же ячейках —  документ стал «легче».

Лимиты

• 400 000 ячеек, максимум 256 столбцов на лист;
• 40 000 ячеек, содержащих формулы;
• 200 листов в документе;
• 1 000 формул GoogleFinance;
• 1 000 формул GoogleLookup;
• ImportRange: 50 формул для импорта из других документов;
• 50 формул ImportData, ImportHtml, ImportFeed или ImportXm.

Рекомендуем конспект доклада с примерами дашбордов для техподдержки и отдела продаж — все они построены с помощью Spreadsheets.

Функция ВПР Excel

❮ Назад
Далее ❯

Функция ВПР — это готовая функция в Excel, которая
позволяет выполнять поиск по столбцам.

Он имеет тип = ВПР и состоит из следующих частей:

= ВПР( искомое_значение , табличный_массив ,
col_index_num , [ range_lookup ])

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

Примечание: Различные части функции разделены символом, например запятой , или точкой с запятой ;

Символ зависит от ваших языковых настроек.

Искомое_значение: Выберите ячейку, в которую будут введены значения поиска.

Table_array: Диапазон таблицы, включая все ячейки таблицы.

Col_index_num: Данные, которые просматриваются. Вход
— номер столбца, считая слева:

Range_lookup: ИСТИНА, если числа (1), или ЛОЖЬ, если текст (0).

Примечание: В Range_lookup можно использовать как 1/0, так и True/False.

Как пользоваться функцией ВПР .

1. Выберите ячейку ( h5 )
2. Тип = ВПР
3. Дважды щелкните команду ВПР
4. Выберите ячейку, в которую будет введено значение поиска ( h4 )
5. Тип (, )
6. Диапазон таблицы меток ( A2:E21 )
7. Тип (, )
8. Введите номер столбца, считая слева ( 2 )
9. Тип True (1) или False (0) ( 1 )
10. Нажмите Enter
11. Введите значение в ячейку, выбранную для Lookup_value h4(7)

Давайте посмотрим на пример!

Используйте функцию VLOOKUP , чтобы найти имена покемонов на основе их ID# :

h5 — здесь отображается результат поиска. В этом случае имена покемонов основаны на их ID#.

h4 выбрано как lookup_value . Это ячейка, в которую вводится поисковый запрос. В данном случае Pokemons ID# .

Диапазон таблицы отмечен как table_array , в этом примере A2:E21 .

Число 2 вводится как col_index_number . Это второй столбец слева, и это данные, которые просматриваются.

Иллюстрация выбора col_index_number 2 .

Итак, далее — 1 (Истина) вводится как range_lookup . Это потому, что в самом левом столбце есть только числа. Если это был текст,
0 (False) .

Хорошая работа! Функция возвращает значение #Н/Д . Это связано с тем, что не было введено никакого значения для идентификатора поиска № h4 .

Давайте зададим ему значение, введите h4(7) :

Взгляните на это! Функция VLOOKUP успешно нашла покемона Squirtle с ID# 7 .

Еще раз, введите (h4)4 :

Он все еще работает! Функция вернула имя Charmanders, которое имеет 4 в качестве ID#. Отлично .

❮ Предыдущий
Следующий ❯

ВЫБОР ЦВЕТА

Лучшие учебники

Учебник HTML
Учебник CSS
Учебник JavaScript
Учебник How To
Учебник SQL
Учебник Python
Учебник W3.CSS
Учебник Bootstrap
Учебник PHP
Учебник Java
Учебник C++
Учебник по jQuery

Основные ссылки

Справочник по HTML
Справочник по CSS
Справочник по JavaScript
Справочник по SQL
Справочник по Python
Справочник по W3.CSS
Справочник по Bootstrap
Справочник по PHP
Цвета HTML
Справочник по Java
Справочник по Angular
Справочник по jQuery

90901
Примеры HTML
Примеры CSS
Примеры JavaScript
Примеры How To
Примеры SQL
Примеры Python
Примеры W3. CSS
Примеры Bootstrap
Примеры PHP
Примеры Java
Примеры XML
Примеры jQuery

FORUM |
О

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

Copyright 1999-2022 Refsnes Data. Все права защищены.
W3Schools работает на основе W3.CSS.

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

Урок 19. Как использовать функцию ВПР Excel

/en/excel-tips/a-trick-for-finding-inconsistent-data/content/

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

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

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

Что такое ВПР?

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

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

Мы добавим нашу формулу в ячейку F2 , но вы можете добавить его в любую пустую ячейку. Как и в любой формуле, вы начнете со знака равенства (=). Затем введите имя формулы. Наши аргументы должны быть заключены в круглые скобки, поэтому введите открывающую круглую скобку. Пока это должно выглядеть так:

=ВПР(

Добавление аргументов

Теперь мы добавим наши аргументы. Аргументы сообщат ВПР, что искать и где искать.

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

=VLOOKUP(«Фоторамка»

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

=VLOOKUP(«Фоторамка», A2:B16

Важно знать, что функция VLOOKUP всегда будет найдите в этом диапазоне первый столбец . В этом примере он будет искать столбец A по запросу «Фоторамка». Значение, которое он возвращает (в данном случае цена), всегда должно быть справа от этого столбца.

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

=ВПР(«Фоторамка», A2:B16, 2

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

=ВПР(«Фоторамка», A2:B16, 2, ЛОЖЬ)

Вот и все! Когда вы нажмете Enter, он должен дать вам ответ, который равен 9.0006 9,99 .

Как это работает

Давайте посмотрим, как работает эта формула. Сначала ищет по вертикали вниз по первому столбцу (VLOOKUP — это сокращение от по вертикали ). Когда он находит «Фоторамку», он перемещается во второй столбец , чтобы найти цену.

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

Если мы хотим найти цену другого товара, мы можем просто изменить первый аргумент:

=ВПР(«Футболка», A2:B16, 2, ЛОЖЬ)

или:

=ВПР(«Подарочная корзина», A2:B16, 2, ЛОЖЬ)

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

Другой пример

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

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

Мы также добавили третий столбец с категорией для каждого элемента. Это даст нам возможность найти цену или категорию. Вот как выглядит таблица на данный момент:

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

= ВПР(E2, A2:B16, 2, ЛОЖЬ)

Чтобы найти категорию, нам понадобится изменить второй и третий аргументы . Во-первых, мы изменим диапазон на A2:C16 , чтобы он включал третий столбец. Далее мы изменим порядковый номер столбца на 9.0006 3 потому что наши категории находятся в третьем столбце:

=VLOOKUP(E2, A2:C16, 3, FALSE)

Когда вы нажмете Enter, вы увидите, что подарочная корзина находится в Gifts категория.

Если мы хотим найти категорию другого элемента, мы можем просто изменить имя элемента в ячейке E2 :

Попробуйте это!

Если вы хотите больше практики, посмотрите, можете ли вы найти следующее:

• Цена кофейной кружки
• Категория пейзажной живописи
• Цена сервировочной чаши
• Категория шарфа

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