Функция ВПР в Экселе – пошаговая инструкция
Функция ВПР в Экселе – это то, что позволит вам экономить десятки часов рабочего времени. При помощи функции ВПР вы ускоритесь буквально в разы и будете вспоминать с ужасом о сравнении таблиц вручную.
Так было со мной. На моей первой официальной работе, мне приходилось раз в несколько дней сравнивать два списка. Списки были относительно небольшие, около 100 строк каждый. Но сравнивал я их при помощи ручки и линейки. Это было мучение, более того это было неэффективное мучение, которое каждый раз, занимало у меня (по меньшей мере) пол рабочего дня.
Продолжался этот мазохизм ровно до тех пор, пока однажды коммерческий директор не увидел, как я безжалостно орудую линейкой над двумя листиками.
Он очень удивленно поинтересовался чем это я занимаюсь. Потом, весьма выдержанно сел за мой компьютер, открыл эти два файла и за несколько минут сравнил их при помощи функции ВПР в Экселе.
Сказать, что я был удивлён – это значит ничего не сказать. Я лицезрел настоящее чудо.
Это была потрясающая демонстрации силы автоматизации.
Функция ВПР в Экселе одинаково нужна и маркетологом, и логистам, и закупщикам – всем тем, кто работает с таблицами данных, это просто Must Have.
Функция ВПР в Экселе – быстрый перенос данных
Самое простое применение функция ВПР это быстрый перенос данных из одной таблицы в другую.
Например, у вас есть большой прайс на 500 позиций и запрос от покупателя, скажем на 50 позиций (в реальности и прайс и запрос могут быть гораздо больше, но принцип от этого не меняется).
Вам нужно быстро найти цены на эти 50 позиций. Разумеется, можно отдельно искать каждую позицию в большом прайсе и потратить на это 30 – 60 минут, а можно сделать это менее чем за минуту при помощи функции ВПР.
Итак, у нас в прайсе 500 позиций. Позиции обозначаются следующим образом, буквами обозначается вид позиции, а цифрами модификация.
Например, «Стул_1» и «Стул_21» это два совершенно разных стула.
Цены в прайсе указаны для примера и вряд ли имеют отношение к реальным ценам.
Петрович человек простой, любит всё делать быстро, но не очень чётко. Поэтому его запросы отличаются особым сумбуром в позициях.
Однако это нас не страшит, во-первых, у нас есть ВПР, во-вторых мы и не такое видали.
Петрович требует, чтобы мы очень быстро проставили цены в его запросе. Ждать он намерен максимум 5 минут. Ведь другие поставщики уже завалили его предложениями.
Нам не хочется терять такого клиента и мы практически мгновенно открываем прайс:
Получается у нас должно быть открыто два файла (две книги в Эксель). Запрос от Петровича и Прайс.
Это как раз то что необходимо, осталось только перенести цены из прайса в запрос.
Для этого перейдем в таблицу запроса и в первой ячейке столбца «Цены» (D4) введем «=впр» и два раза кликнем на значок функции:
Сразу же после этого, в строке формулы нужно поставить курсор внутри надписи ВПР и нажать Fx, перед вами появится окно с аргументами функции ВПР:
В аргументах функции вы говорите Экселю что и где нужно искать:
Искомое значение — это значение (в данном случае наименование), цену которого вы хотите найти в прайсе. Соответственно кликайте на первую ячейку столбца «Наименование».
Теперь в аргументах функции заполните следующие поля:
Таблица — выделяете столбцы, которые содержат искомые наименования и цены, таким образом, чтобы наименования были крайним левым столбцом.
Так работает функция ВПР — ищет искомые значения в крайнем левом столбце (для ВПР это столбец №1). Когда ВПР находит искомое значение он начинает смотреть правее, в тот столбец, который вы указали в «Номере столбца».
Там вы должны указать столбец, содержащий необходимые для переноса данные. В нашем случае это цены и в нашем случае это столбец под номером два, относительно той таблицы, которую вы указали в аргументах.
Интервальный просмотр — ставьте 0. Ноль обозначает точное соответствие.
После заполнения аргументов функции нажимайте «Ок» и если всё сделано верно, то в столбце «Цена» (файл «Запрос от Петровича»), появится цена.
Обратите внимание на то, что сейчас мы работали в двух разных файлах (книгах).
Когда работа идёт в двух разных книгах, Эксель автоматически закрепляет таблицу в функции ВПР:
Делает это он при помощи значка $, который проставляет перед столбцами и строками таблицы.
Это позволяет не съезжать формуле когда вы протягиваете её вниз. Это очень актуально когда вы работаете в рамках одного листа или одной книги (в этом случае Эксель автоматически Не закрепляет ячейки).
Давайте посмотрим что получиться если протянуть формулу «без закрепления»:
Обратите внимание, что для первой ячейки всё в порядке и диапазон B3:C502 точно соответствует той таблице, которую мы выделили для поиска данных, однако (без закрепления) так будет не всегда, по мере «протягивания» формулы ВПР вниз, будет смещаться и таблица, пока однажды, мы не увидим вот такую странную надпись #Н/Д:
#Н/Д значит что функция ВПР не смогла найти цену Стул_13 в прайсе и это не мудрено, ведь диапазон таблицы в формуле ВПР уехал ниже этого значения:
Посему, если не хотите чтобы от вас уезжали, закрепляйте диапазон.
Как вы заметили, формулы ссылаются на определённые ячейки, другими словами между формулами и исходными данными есть связь. Стоит изменить исходные данные и значения в формулах сразу изменятся.
Особенно остро это чувствуется в ВПР. Если вы вдруг забудетесь и в исходной табличке добавите лишний столбец в «неположенном месте», то в формула ВПР выдаст совсем неожиданные значения.
Поэтому, если вам не нужна связь между таблицами, рекомендую формулы превратить в данные.
Для этого нужно выделить столбец с формулами, нажать Ctrl+C и в левом верхнем углу выбрать «Вставить» — «Вставить значения».
Для тех кто не любит изучать картинки, я записал небольшое видео в котором показываю всё то, что мы проговорили выше (кроме вставки значений):
Видео — «Быстрый перенос данных с помощью функции ВПР в Экселе»
Перенос данных при помощи ВПР, можно использовать не только для того, чтобы быстро получать данные из одной таблицы в другой, но и для того, чтобы сравнивать две таблицы.
Это очень актуально для тех кто работает в закупках и отправляет заказы поставщику.
Обычно происходит следующая ситуация. Вы отправляете заказ поставщику, через некоторое время получаете ответ в виде счёта и сверяете заказ с счётом.
Всё ли есть в счёте, в нужном ли количестве, по правильным ли ценам и т.д.
Функция ВПР в Экселе – сравнение двух таблиц
Итак, у вас есть «Заказ поставщику» (1) и ответ поставщика в виде «Счёта на оплату» (2).
Для удобства восприятия я разместил их на одном листе:
Для начала проверим все ли позиции и по правильной ли цене указал в счёте поставщик.
Для этого нужно из Счёта перетянуть данные в Заказ при помощи функции ВПР.
Перед «перетяжкой», в таблицу «Заказ поставщику» нужно добавить два «сравнительных» столбца:
После добавления столбцов, нужно перетянуть соответствующие данные при помощи ВПР:
Теперь когда данные перенесены, нужно их сравнить, для это необходимо добавить еще два столбца (Разница 1 и Разница 2):
В столбце «Разница 1» нужно вычесть от исходного количества (D4) количество в счёте (E4).
В столбце «Разница 2» нужно вычесть от исходной цены (G4) цену в счёте (H4).
Таким образом мы сможем увидеть разницу и в количестве и в цене.
Если значение «0», то значит всё хорошо и данные одинаковые.
Если значение плюсовое (например «+3»), то это значит что в счёте не хватает 3 штук.
Если значение отрицательное, это значит, что нам пытаются «впихнуть» лишнее.
Если значение #Н/Д — это значит, что в счёте вообще нет такой позиции.
Тоже самое и с ценами, если значения в плюсе, значит нам сделали скидку, а если значения в минусе — значит нам пытаются продать по завышенной цене.
Таким образом нужно просканировать всю таблицу и выяснить о причинах расхождения у поставщика:
Друзья, вот так мы проверили насколько соответствует Заказ, полученному Счёту и казалось бы что это всё что необходимо для счастливой жизни. Однако это не совсем так.
Нужно еще проверить соответствие Счёта, отправленному заказу, на предмет лишних позиций.
Вдруг хитрый поставщик, среди сотни позиций решил нам скрытно что-то «допродать».
Для этого в «Счёт на оплату» нужно добавить столбец «Кол/во в заказе» и «отвепээрить» туда значения из столбца «Количество» Заказа поставщику.
И если в столбце «Количество в заказе» мы вдруг увидим значения #Н/Д это значит, что позиции с таким наименование не было в нашем заказе поставщику.
Это не всегда означает, что поставщик хочет нас «нагреть», чаще всего просто может быть ошибка в названии товара. Стоит например поставить лишний пробел, как Эксель не задумываясь выдаст #Н/Д. Для него это разные названия.
Теперь всё тоже самое продемонстрирую в небольшом видео.
Видео — «Сравнение двух таблиц с помощью функции ВПР в Экселе»
Коллеги, поздравляю, с этого момента ваша работа с данными значительно упростится и ускорится, ведь теперь вы «почтигуру» по применению функции ВПР в Экселе.
Коллеги, если вы часто работаете в Эксель, то рекомендую прочитать еще парочку моих очень полезных статей по этой тематике, там будет (как всегда) только-то что необходимо в работе:
[expert_bq id=»1570″]Данный оператор способен выводить в другую ячейку не только числа, но и текст, результат вычисления формул и любые другие значения, которые расположены в выбранном элементе листа. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Аргумент «Ссылка на ячейку» представлен в виде ссылки на элемент листа, данные содержащиеся в котором нужно отобразить. При этом указанная ссылка должна иметь текстовый вид, то есть, быть «обернута» кавычками.Ссылки в Excel – инструкция
Обратите внимание, что для первой ячейки всё в порядке и диапазон B3:C502 точно соответствует той таблице, которую мы выделили для поиска данных, однако (без закрепления) так будет не всегда, по мере «протягивания» формулы ВПР вниз, будет смещаться и таблица, пока однажды, мы не увидим вот такую странную надпись #Н/Д:
Автозаполнение ячеек в Excel
Автоматическое заполнение ячеек также используют для продления последовательности чисел c заданным шагом (арифметическая прогрессия). Чтобы сделать список нечетных чисел, нужно в двух ячейках указать 1 и 3, затем выделить обе ячейки и протянуть вниз.
Эксель также умеет распознать числа среди текста. Так, легко создать перечень кварталов. Введем в ячейку «1 квартал» и протянем вниз.
На этом познания об автозаполнении у большинства пользователей Эксель заканчиваются. Но это далеко не все, и далее будут рассмотрены другие эффективные и интересные приемы.
Автозаполнение в Excel из списка данных
В следующем открывшемся окне видны те списки, которые существуют по умолчанию.
Как видно, их не много. Но легко добавить свой собственный. Можно воспользоваться окном справа, где либо через запятую, либо столбцом перечислить нужную последовательность. Однако быстрее будет импортировать, особенно, если данных много. Для этого предварительно где-нибудь на листе Excel создаем перечень названий, затем делаем на него ссылку и нажимаем Импорт.
Жмем ОК. Список создан, можно изпользовать для автозаполнения.
В левой части окна с помощью переключателя задается направление построения последовательности: вниз (по строкам) или вправо (по столбцам).
Автозаполнение чисел с помощью мыши
Автозаполнение в Excel удобнее делать мышкой, у которой есть правая и левая кнопка. Понадобятся обе.
Если выбрать Заполнить только форматы, будут продлены только форматы ячеек.
Сделать последовательность чисел можно еще быстрее. Во время протягивания ячейки, удерживаем кнопку Ctrl.
Этот трюк работает только с последовательностью чисел. В других ситуациях удерживание Ctrl приводит к копированию данных вместо автозаполнения.
Если при протягивании использовать правую кнопку мыши, то контекстное меню открывается сразу после отпускания кнопки.
При этом добавляются несколько команд. Прогрессия позволяет использовать дополнительные операции автозаполнения (настройки см. выше). Правда, диапазон получается выделенным и длина последовательности будет ограничена последней ячейкой.
Для прогнозирования подойдет линейный тренд. Расчет параметров уравнения можно осуществить с помощью функций Excel, но часто для наглядности используют диаграмму с настройками отображения линии тренда, уравнения и прогнозных значений.
Чтобы получить прогноз в числовом выражении, нужно произвести расчет на основе полученного уравнения регрессии (либо напрямую обратиться к формулам Excel). Таким образом, получается довольно много действий, требующих при этом хорошего понимания.
Так вот прогноз по методу линейной регрессии можно сделать вообще без формул и без графиков, используя только автозаполнение ячеек в экселе. Для этого выделяем данные, по которым строится прогноз, протягиваем правой кнопкой мыши на нужное количество ячеек, соответствующее длине прогноза, и выбираем Линейное приближение. Получаем прогноз. Без шума, пыли, формул и диаграмм.
Если данные имеют ускоряющийся рост (как счет на депозите), то можно использовать экспоненциальную модель. Вновь, чтобы не мучиться с вычислениями, можно воспользоваться автозаполнением, выбрав Экспоненциальное приближение.
Более быстрого способа прогнозирования, пожалуй, не придумаешь.
Автозаполнение дат с помощью мыши
Довольно часто требуется продлить список дат. Берем дату и тащим левой кнопкой мыши. Открываем квадратик и выбираем способ заполнения.
По рабочим дням – отличный вариант для бухгалтеров, HR и других специалистов, кто имеет дело с составлением различных планов. А вот другой пример. Допустим, платежи по графику наступают 15-го числа и в последний день каждого месяца. Укажем первые две даты, протянем вниз и заполним по месяцам (любой кнопкой мыши).
Обратите внимание, что 15-е число фиксируется, а последний день месяца меняется, чтобы всегда оставаться последним.
Используя правую кнопку мыши, можно воспользоваться настройками прогрессии. Например, сделать список рабочих дней до конца года. В перечне команд через правую кнопку есть еще Мгновенное заполнение. Эта функция появилась в Excel 2013. Используется для заполнения ячеек по образцу. Но об этом уже была статья, рекомендую ознакомиться. Также поможет сэкономить не один час работы.
На этом, пожалуй, все. В видеоуроке показано, как сделать автозаполнение ячеек в Excel.
Есть ли формула Google Таблиц, чтобы поместить имя листа в ячейку?
По моему опыту, как только название листа меняется, формула в А2 сразу обновляется. Однако этого недостаточно для запуска обновления A3. Но каждую минуту, когда ячейка A1 пересчитывает время, результат формулы в ячейке A2 впоследствии обновляется, а затем, в свою очередь, запускает A3 для обновления с новым именем листа. Это не компактное решение… но, похоже, оно работает.
Оглавление или удобная навигация в рабочей книге
Работа в Excel не ограничивается одним листом.
Практически всегда файл состоит из нескольких листов.
Давайте разберёмся как сделать оглавление в Excel, чтобы было удобно работать с файлами из 10 и более листов?
Каждый раз сталкиваясь с книгой Microsoft Excel с большим количеством листов, невольно хочется иметь удобную навигацию по ней.
В этой статье мы рассмотрим каким образом можно создавать оглавление в Excel.
Обратимся к нашему примеру, его можно скачать по ссылке ниже.
Создание удобной навигации — оглавление
Перейдём во вкладку Оглавление, в ней мы и будем создавать гиперссылки на каждый лист отдельно.
Для создания гиперссылки перейдём во вкладку Вставка и в группе Ссылка, выберем единственный пункт Гиперссылка.
После заполнения всех полей, нажимаем ОК, и мы получим первую рабочую Гиперссылку, при нажатии на которую мы переместимся на соответствующий Лист.
Вызвать меню создания Гиперссылки так же возможно более быстрым способом:
просто кликнув правой кнопкой мыши на ячейку, в которую Вы хотите поместить ссылку, и в появившемся меню в самом низу выбрать Гиперссылка,
далее заполняем всё, как делалось выше.
Создадим по тому же принципу Гиперссылки в Оглавлении для всех остальных Листов.
После того, как мы заполнили лист Оглавление ссылка на всю книгу, мы можем спокойно перемещаться по книге.
Давайте сделаем нашу навигацию ещё удобнее.
Мы можем перейти на любой лист из Оглавления, а обратно — уже не получится.
Выделим все листы, кроме листа с Оглавлением.
Для этого достаточно зажать клавишу Ctrl или Shift и кликнуть по каждому листу.
Гиперссылка назад в оглавление
=ГИПЕРССЫЛКА(«#Оглавление!A1″;»Назад в оглавление»)
- «#Оглавление!A1» — адрес листа, куда мы хотим вернуться, в нашем случае это лист Оглавление;
- «Назад в оглавление» — название гиперссылки, которая отображается в ячейке.
После ввода формулы нажимаем комбинацию клавиш Ctrl + Enter, чтобы применить формулу ко всем выделенным листам.
Теперь на каждом листе у нас есть обратная Гиперссылка.
Мы можем перемещаться из Оглавления в любое место книги, и обратно.
Вот и всё.
Пробегитесь по вашему файлу и оцените насколько стало удобно.
Это особенно полезно для файлов с несколькими десятками листов, где можно очень легко потеряться, но только не с нашей навигацией.
Больше полезных видеоуроков на нашем YouTube канале.
Если у Вас возникли вопросы или просто хотите поделиться мнением, напишите в комментариях к записи.
В этой статье мы рассмотрим каким образом можно создавать оглавление в Excel.
Excel двссыл на другую книгу — Вэб-шпаргалка для интернет предпринимателей!
- «#Оглавление!A1» — адрес листа, куда мы хотим вернуться, в нашем случае это лист Оглавление;
- «Назад в оглавление» — название гиперссылки, которая отображается в ячейке.
В разделеВ менюВвод книги собственный.автозагрузки вы можете настроить должны включаться в грамматические ошибки. Для и сводной таблицей.) доступной оперативной памятиЧисло областей в строк и 256выберите пункт
Есть ли формула Google Таблиц, чтобы поместить имя листа в ячейку?
У вас есть 2 варианта, и я не уверен, что я поклонник их увядания, но это мое мнение. Вы можете чувствовать себя иначе:
Вариант 1: принудительно запустить функцию.
Функция в ячейке не запускается, если она не ссылается на ячейку, которая изменилась. Изменение имени листа не вызывает никаких функций в электронной таблице. Но мы можем заставить эту функцию работать, передавая ей диапазон, и всякий раз, когда элемент в этом диапазоне изменяется, функция запускается.
Вы можете использовать приведенный ниже скрипт для создания пользовательской функции, которая будет получать имя:
Вариант 2: использовать событие OnChange
Событие OnChange запускается при изменении имени листа. Вы можете сделать код ниже более сложным, чтобы проверить наличие ошибок, проверить, что идентификатор листа работает только на заданном листе и т.д. Однако основной код:
После того, как вы сохранили код, в редакторе сценариев установите эту функцию в текущую версию Project On Change Trigger. Он будет записывать имя листа в ячейку K1 в любом событии изменения. Чтобы установить триггер, выберите ” Текущие триггеры проекта” в меню ” Правка”.
Чтобы получить текущее имя листа в листах Google, следующий простой сценарий может помочь вам без ввода имени вручную, выполните следующие действия:
В открывшемся окне проекта скопируйте и вставьте приведенный ниже код скрипта в пустое окно кода, см. Скриншот:
Затем сохраните окно кода и вернитесь к листу, для которого вы хотите получить его имя, затем введите =sheetname() формулу: =sheetname() в ячейку и нажмите клавишу Enter, имя листа будет отображаться сразу.
Если вы ссылаетесь на лист с другого листа, вы можете получить имя листа с помощью функции CELL. Затем вы можете использовать регулярные выражения, чтобы извлечь имя листа.
обновление: формула автоматически обновит “ИМЯ ЛИСТА” с будущими изменениями, но вам нужно будет ссылаться на ячейку (например, А1) на этом листе при первоначальном вводе формулы.
Вот мое предложение для скрипта, который возвращает имя листа с его позиции в списке листов в параметре. Если параметр не указан, возвращается имя текущего листа.
Затем вы можете использовать его в ячейке, как любая функция
Как описано в других ответах, вам нужно добавить этот код в скрипт с:
У меня есть лист, предназначенный для использования другими, и у меня довольно много косвенных() ссылок, поэтому мне нужно формально обрабатывать измененное имя вкладки листа.
Я использовал формулу из JohnP2 (ниже), но у меня возникли проблемы, потому что она не обновлялась автоматически при изменении имени листа. Вам нужно перейти к фактической формуле, внести произвольные изменения и обновить, чтобы запустить ее снова.
Я решил это с помощью информации, найденной в этом решении, о том, как заставить функцию обновляться. Возможно, это не самое элегантное решение, но оно заставило Sheets обратить внимание на эту ячейку и регулярно обновлять ее, чтобы она уловила обновленный заголовок листа.
Благодаря этому Sheets знает, что нужно обновлять эту ячейку каждый раз, когда вы вносите изменение, в результате чего адрес обновляется всякий раз, когда пользователь переименовывает его.
Старый поток, но полезный… так что здесь дополнительный код.
Во-первых, в ответ на замечание Крейга о том, что регулярное выражение является чрезмерно жадным и не подходит для имен листов, содержащих одну кавычку, это должно сработать (замените “SHEETNAME”! A1 своим собственным листом и ссылкой на ячейку):
Формула также учитывает имена листов, которые содержат челки (“!”), Но не будут работать с именами, использующими доллары взрыва (“! $”) – если вам действительно нужно, чтобы имена ваших листов выглядели как полные абсолютные ссылки на ячейки, поместите разделяющий символ между ударом и долларом (например, пробел).
Как уже много раз говорили выше, Google Sheets заметит изменения в названии листа только в том случае, если вы установите перерасчет рабочей книги на “При изменении и каждую минуту”, который можно найти в меню “Файл | Настройки | Расчет”. Изменение может занять до целой минуты.
Во-вторых, если вам, как и мне, вам нужна совместимая формула, которая работает как в Google Sheets, так и в Excel (которая, по крайней мере, для более старых версий не имеет функции REGEXREPLACE), попробуйте:
При этом используется INFO (“релиз”), чтобы определить, на какой платформе мы работаем… Excel возвращает число> 0, тогда как в Google Sheets не реализована функция INFO, и генерируется ошибка, которая выводится формулой в 0 и используется для численного сравнения. Ветка кода Google такая же, как и выше.
Для ясности и полноты, это Excel-только версии (что правильно возвращает имя листа он находится на):
Этот код работает очень хорошо для меня, но если в любом случае он возвращает все имена листов без ссылки на номер листа, я буду очень признателен, если вы поможете мне с этим.
Я получил это, чтобы наконец-то работать в полуавтоматическом режиме без использования сценариев… но для его выполнения требуется 3 ячейки. Заимствуя немного из предыдущих ответов, я начинаю с ячейки, в которой нет ничего, кроме = NOW(), чтобы показать время. Например, мы поместим это в ячейку A1…
Эта функция обновляется автоматически каждую минуту. В следующую ячейку поместите формулу указателя, используя собственное имя листов, чтобы указать на предыдущую ячейку. Например, мы поместим это в A2…
Помимо форматирования ячеек, ячейки A1 и A2 должны в этот момент отображать одинаковое содержимое… а именно текущее время.
И последняя ячейка – это часть, которую я заимствовал из предыдущих решений, используя выражение регулярного выражения, чтобы извлечь формулу из второй ячейки, а затем вычеркнуть имя листа из указанной формулы. Например, мы поместим это в ячейку A3…
На этом этапе результирующее значение, отображаемое в A3, должно быть именем листа.
По моему опыту, как только название листа меняется, формула в А2 сразу обновляется. Однако этого недостаточно для запуска обновления A3. Но каждую минуту, когда ячейка A1 пересчитывает время, результат формулы в ячейке A2 впоследствии обновляется, а затем, в свою очередь, запускает A3 для обновления с новым именем листа. Это не компактное решение… но, похоже, оно работает.
[expert_bq id=»1570″]Как видим, теперь при выделении данного элемента в окне имени отображается не её адрес, а то наименование, которое мы ей дали. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Вслед за выполнением данного действия запускается окно аргументов оператора СУММ, единственной задачей которого является суммирование указанных значений. Синтаксис этой функции очень простой:Оглавление или удобная навигация в рабочей книге | beExcel
Как видите, ссылки в Excel могут быть нескольких видов: относительные, абсолютные и смешанные. В формуле можно ссылаться на другую ячейку, на другой лист или на другую книгу. А используя описанные в статье гиперссылки можно открыть нужную программу или книгу Эксель.
