Впр в excel на английском: ВПР на английском | Excel функции перевода

Содержание

Что такое ВПР в Excel и как пользоваться этой функцией?

Содержание

  • 1 Что такое ВПР в Excel?
  • 2 Как пользоваться ВПР в Excel?
    • 2.1 Пример №1
    • 2.2 Пример №2
  • 3 Вопросы от новичков
    • 3.1 Как сравнить таблицы с помощью ВПР?
    • 3.2 Как с помощью ВПР сделать выпадающий список?
  • 4 Подводим итоги

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

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

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

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

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

Совет: научиться правильно применять функцию VLOOKUP чуть сложнее, чем построить диаграмму в Excel; если планируется однократный поиск по единственной или нескольким строкам таблицы, проще будет воспользоваться комбинацией клавиш Ctrl + F и обработать данные вручную.

Как пользоваться ВПР в Excel?

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

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

Пример №1

Имеется сводная таблица с наименованиями товаров, датами и объёмами поставок и сроками годности. Требуется, не прибегая к опции поиска, вывести в любую свободную ячейку информацию о конкретном продукте.

Решение:

  • Открыть исходную электронную таблицу и убедиться, что представленные в ней столбцы и строки могут содержать необходимую информацию.
  • В любом удобном месте (для облегчения работы — лучше на новой странице) создать таблицу с искомыми параметрами. Названия могут совпадать с исходными или отличаться от них; главное — чтобы было прямое соответствие значений, иначе выведенная информацию окажется неверной или бесполезной.
  • В первой строке новой таблицы Excel вписать любое из требуемых наименований. Работать с ВПР можно и по другому параметру, например по объёму поставки, однако логически правильнее и проще для восприятия использовать в качестве ориентира заглавный столбец первой таблицы.
  • Выделить нажатием ячейку, следующую за наименованием товара, и перейти на вкладку Excel «Формулы».
  • Щёлкнуть по кнопке «Вставить функцию» — самой крайней слева в открывшейся «ленте».
  • В открывшемся окошке вызвать верхний выпадающий список и выбрать кликом мыши пункт «Полный алфавитный перечень».
  • Пролистать перечень вниз, выделить строчку «ВПР» и щёлкнуть по кнопке «ОК».
  • Другой способ вызвать функцию VLOOKUP — на той же вкладке «Формулы» открыть выпадающий список «Ссылки и массивы» и выбрать пункт «ВПР».
  • В первой строчке нового диалогового окна указать, нажав на обращённую вверх стрелочку, ячейку с наименованием искомого товара, находящуюся во вновь созданной таблице.
  • Во второй строчке того же окна — зажав левую клавишу мыши, выделить исходную таблицу Excel целиком.
  • В третьей — порядковый номер столбца, в котором требуется осуществить поиск…
  • …Или сам столбец, выделив его аналогичным образом.
  • В четвёртой — указать, требуется точный поиск по таблице Excel (значение «0») или программа должна будет подобрать ближайший подходящий параметр (значение «1»). В этом примере и при решении большинства других задач, связанных с применением функции ВПР, нужны точные совпадения — следовательно. Необходимо вписать в текстовое поле «0», а затем нажать на «ОК».
  • Если значение в строке появилось, но оно не соответствует ожидаемому, тогда следует проверить правильность установленного формата ячейки.
  • В нашем примере в результате должно получиться не абсолютное значение, а дата. Чтобы привести данные в требуемый вид, необходимо щёлкнуть по «неправильной» ячейке правой кнопкой мыши и выбрать в открывшемся контекстном меню пункт «Формат ячеек»…
  • …После чего найти требуемый — «Дата» — и кликнуть по кнопке «ОК».
  • Готово! Ячейка приобрела приемлемый для работы вид — таким он и будет оставаться вплоть до нового изменения.
  • Поскольку функция Excel ВПР достаточно сложна в применении, простое «растягивание» на следующую строку не даст результата: пользователь увидит или значение, вновь не соответствующее ожидаемому, или извещение о неправильном применении формулы.
  • Чтобы добиться требуемого результата, следует или повторить для новой строчки все перечисленные манипуляции, или выделить уже готовую и скопировать в верхнем текстовом поле Excel формулу…
  • …После чего вставить её в нужную строку…
  • …Не забыв заменить номер столбца, в котором на этот раз должен быть осуществлён поиск — теперь это не «3», а «4».
  • Нажав на клавишу Enter и при необходимости вновь подкорректировав формат ячейки, пользователь получит заполненную краткую таблицу с достоверной информацией из большой сводной.
  • Теперь при изменении значения в исходной таблице оно автоматически будет обновляться в новой, что крайне удобно для отслеживания корректировок в реальном времени.
  • Если же, напротив, нужно зафиксировать полученные данные, защитив их от изменений, следует выделить получившуюся таблицу и скопировать её, используя контекстное меню.
  • После чего, вновь вызвав его, выбрать пункт «Специальная вставка», отметить кружочком в разделе «Вставить» пункт «Значения» и кликнуть по кнопке «ОК».
  • В результате вместо формулы в строках новой таблицы будут отображаться неизменяемые абсолютные значения, что и требовалось пользователю.
  • Но главное — другое: теперь, единожды настроив выдачу в таблице Excel, можно вписывать в верхнюю строчку любые требуемые наименования продуктов — искомые значения появятся в строках автоматически в уже установленном формате.

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

Пример №2

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

Решение:

  • Первый шаг, как и в предыдущем случае, — открыть обе таблицы. В идеале их следует разместить на одном листе — так будет проще проверить правильность объединения по одному или нескольким выбранным позициям.
  • Теперь нужно добавить к одной из таблиц Excel (обычно — более содержательной) новый столбец и назвать его в соответствии с переносимыми данными.
  • Выделив щелчком мыши первую свободную ячейку под названием, пользователь должен уже известным образом вызвать функцию ВПР — после первого примера это не сложнее, чем сделать график в Excel.
  • В первой строке диалогового окна следует указать наименование позиции из первой таблицы — той, в которую добавляются данные.
  • Во второй — вставить выделением всю вторую таблицу, включая названия столбцов и строк.
  • В третьей — указать номер столбца второй таблицы, который планируется сопоставить с первым. Последняя строка, как и в первом примере, должна принять значение «0», иначе получить результаты, точно соответствующие заданным параметрам, не выйдет.
  • Щёлкнув по кнопке «ОК», пользователь получит в первой строке искомый результат.
  • Теперь не нужно подставлять формулу в каждую строку — достаточно зажать левой клавишей мыши крестик в правом нижнем углу ячейки и «растянуть» её на все позиции — данные будут подставлены автоматически и в нужном порядке.
  • Соответствия сохранятся даже при пересортировке строк в первой таблице.
  • А кроме того, в неё не будут включены «лишние» данные, отсутствующие во второй.

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

Вопросы от новичков

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

Как сравнить таблицы с помощью ВПР?

Сравнение двух и более таблиц проводится почти так же, как добавление нового ряда данных:

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

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

Как с помощью ВПР сделать выпадающий список?

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

  • Открыть исходную таблицу и способом, подробно описанным в первом примере, подготовить «результирующую».
  • Перейти на вкладку «Данные» и найти в «ленте» инструмент «Проверка данных».
  • Щёлкнув по нему, вызвать выплывающее меню, а далее — кликнуть по одноимённой строчке.
  • Выбрать в новом выплывающем меню «Тип данных» пункт «Список».
  • В строке «Источник» указать требуемый диапазон наименований, после чего нажать «ОК».
  • Теперь пользователь сможет, щёлкая по треугольничку возле названия продукта, выбирать в выпадающем списке нужную позицию — данные в ячейках ниже обновятся автоматически.

Важно: при изменении параметров в исходной таблице соответствующие коррективы будут внесены Excel и в «результирующую».

Подводим итоги

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

Adblock
detector

ТОП-5 полезных формул Excel/Google Sheets для SEO специалиста — Idea Digital Agency

SEO-шники часто применяют таблицы для организации данных. Excel или Google Sheets помогают объединять информацию про семантическое ядро (читайте как быстро собрать СЯ), загрузить URLы с техническими проблемами, систематизировать результаты анализа ссылочного профиля и выполнять другие рутинные задачи. Если кратко, таблицы — ежедневный помощник SEO специалиста. Почему бы тогда не научиться полностью применять потенциал Excel и Google Sheets?

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

VLOOKUP/ВПР

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

Допустим, вы собрали красивое семантическое ядро и хотите увидеть, сколько Clicks, Impression, CTR в Google Search Console получают ваши ключи (или получают вообще).  

Для этого применяем формулу: 

=VLOOKUP(A2,Sheet2!A:F,2,FALSE)

Пока ничего не понятно? Спокойно, сейчас все разберемся. 

Так, еще раз формула:

=VLOOKUP(A2,Sheet2!A:F,2,FALSE). 

  • A2 — это диапазон поиска. И в таблице Sheet 1, и в таблице Sheet 2 у нас повторяется колонка Query, там программа и «ищет» ключ, по которому нужно показать статистику. 
  • Sheet2! — это вкладка в таблице, в которой мы ищем данные (из GSC выгрузки).
  • A:F — это диапазон столбцов в Sheet2!, где происходит поиск.
  • 2 — это порядковый номер колонки в Sheet2!, где происходит поиск (Query — первый столбец по порядковому номеру, Clicks — второй, Impressions — третий и так далее ).
  • FALSE (или можно заменить на 0(ноль)) — означает, что разрешено поиск только точного соответствия.  

Если функция НЕ найдет в Query конкретного ключа (как, например, internet marketing company ключ в таблице выше ), то показывает ошибка N/A.

Если введено значение (TRUE), это означает, что вы разрешаете поиск неточного, а приблизительно ожидаемому, то есть, в случае с internet marketing company функция постарается найти ключ, который максимально похож и выдает количество Clicks этого максимально схожего ключа. 

IMPORTRANGE

=IMPORTRANGE это как =VLOOKUP только с другой таблицы. 

Синтаксис таков:

=IMPORTRANGE(«https://URLадресаСтраницы»; «НазваниеВкладкиТаблицыДляИмпорта!B64»)

На примере выше изображено, как можно свести остатки по бюджету из разных таблиц SEO поектов. Тащим сумму по всем затратам на линбилдинг/контент таблиц и увидим, где у нас сколько бабосиков осталось.

SPLIT

Эта формула выводит текст, разделенными определенными символами, в разные столбцы. Допустим, вы собрали некачественные URL-ы через Ahrefs, которые на вас ссылаются и хотите домены отправить на удаление через Disavow tool. Применяем формулу:

=SPLIT(A2,»/») — номер столбца и символ, по которому разъединяем домены. 

CONCATENATE

Хотите добавить приставку domain: за рекомендацией Google? Легко!

Воспользуемся формулой =CONCATENATE(«domain:»,C2) — в скобках пишем ЧТО хотим присоединить к столбцу.

Кстати, если поменять местами элементы формулы не («domain:»,C2), а (C2, «domain:») то приставка добавляется в конце строки (ichip.rudomain:).

SUMIF

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

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

Прописываем формулу:

=SUMIF(A2:A17, «PPC», C2:C17) 

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

Бонус — ТОП ресурсы, где можно узнать больше

Спасибо, что дочитали до конца! Надеемся, что формулы пригодились. Для вас ниже размещаем наилучшие ресурсы, которые помогут копнуть глубже в формулы Google Sheets и Excel, и практиковать приобретенные знания:

  • Бесплатный видео-курс по формулам от специалистов Google: https://www.coursera.org/learn/getting-started-with-google-sheets (англ., есть русские субтитры).

  • Обучение и справка по Google таблицам: https://support.google. com/a/users/answer/9282959?hl=ru (рус., англ.)

VPR МИР

VPR МИР

ДОБРО ПОЖАЛОВАТЬ В VPR WORLD. с отраслевыми модулями курсов и предоставить им рабочую среду в реальном времени.
VPR World внедряет инновационные курсы в соответствии с потребностями отраслевых стандартов с модульной структурой, чтобы помочь им подготовиться и повысить свои навыки для всех потоков, и это будет сделано путем предоставления специализированного коучинга, лидерства и мотивации, чтобы преуспеть в их производительности.

2944

Ученики

309

Франчайзи

70

Курсы

запрос франчайзи

онлайн-регистрация студентов

проверка учащихся

НАШИ КУРСЫ