Функция ВПР в Excel с примером (англ. VLOOKUP)
Допустим у нас есть таблица с прайс листом товаров. Задача состоит в том, чтобы заполнить таблицу Заказов.
Функция ВПР в Excel с примером
Для решения данной задачи мы будем использовать функцию ВПР. В Excel существуют и другие функции с помощью которых можно решить данную задачу, но мы с вами разбираем самую популярную и часто используемую функцию ВПР в Excel.
Итак, чтобы решить задачу в нашем примере нам необходимо сначала заполнить столбец «С» в таблице заказов, т.е найти цену товаров в таблице «Прайс лист», а затем, чтобы узнать стоимость – перемножить цену на количество товаров.
Синтаксис функции ВПР (VLOOKUP) в Excel
[ads]ВПР(искомое_значение, таблица, номер_столбца, [интервальный_просмотр])
Функция ВПР ищет значение в крайнем левом столбце таблицы и возвращает значение ячейки, находящейся в указанном столбце той же строки.
Разберем функцию на нашем примере. Вставим курсов в ячейку C3 и наберем формулу =ВПР(A3;$F$2:$H$22;3;0)
в английской версии =VLOOKUP(A3;$F$2:$H$22;3;0)
В данной формуле с ВПР (англ. VLOOKUP):
A3 – искомое_значение. В нашем случае это «Ведро», т.е нам необходимо найти «ведро» в таблице «Прайс лист»
$F$2:$H$22 – таблица. В нашем примере это таблица «Прайс лист» (F2:H22). В диапазон данной таблицы вставлены знаки $ для его закрепления, чтобы он не сдвигался вниз, когда мы будем протягивать формулу. Знак доллара в Excel превращает относительный диапазон в абсолютный.
3 – номер столбца. В нашем случае это цифра “3”, так как цена находится в третьем столбце нашей таблицы «Прайс лист».
0 – интервальный просмотр. Может принимать только два значения 0 или 1: 0 – ищет точное совпадение, 1 – приблизительное. В 99% случаях требуется искать точное значение (в нашем случае нам необходимо искать слово «Ведро»). Поэтому практически всегда указывается цифра 0.
Таким образом, логика функции ВПР Excel в нашем примере следующая. Функция ищет искомое значение («ведро») в крайнем левом столбце таблицы («Прайс лист»), после того как находит – возвращает значение ячейки находящейся в указанном столбце той же строки, т.е цену 120 рублей.
После этого переходим в ячейку D3 и находит стоимость товаров. Прописываем формулу =C3*B3, т.е перемножаем цену товара на количество.
Далее для автоматической простановки формул по остальным товаром, необходимо протянуть формулу вниз. Для этого необходимо выделить обе ячейки которые нужно протянуть и потянуть вниз за нижний правый угол (смотрите рисунок 3)
Если статья была вам полезна, то буду благодарен, если вы поделитесь ей со своими друзьями с помощью кнопок расположенных ниже.
Спасибо за внимание.
Добрый день, у меня есть две таблицы одна наименование и ид коллекции и есть вторая таблица и ид коллекции и название. Я сопоставил, но некоторые товары входят в две колекции например, и у них через запятую написанно, как реализовать, что бы он сопоставлял два название через запятую?
Если я правильно понял, то перед и после искомого значения поставьте знак *, но это только при условии что искомые значения не похоже друг на друга, иначе будет неправильно искать
Подскажите пожалуйста, какую функцию лучше использовать если в таблице несколько одинаковых наименований товаров с разными ценами и необходимо подтягивать цены по каждому наименованию.
Для тех кому необходимо получать n-ые значения ВПР()
Для получения n-го значения ВПР() необходимо произвести идентификацию каждой записи двух таблиц (поверьте, это возможно в Excel даже при одинаковом наименовании товара).
Идентификация записи – функция СЧЁТЕСЛИ() с последующим получением только значений + немного комбинаторики команд.
Результат – одна таблица с 1048576 записями проходит идентификацию в ручном режиме (без макроса) за 15 -20 минут.
Ну и дальше (после идентификации записей второй таблицы) используется обычным порядком ВПР().
Добрый день! Помогите пожалуйста решить задачу, давайте используя ваш пример с заказами. Мне нужно из списка товаров например, отфильтровать значение “Ершик” и чтобы в итоге вышла сумма количества всех “Ершиков”, даже если будут нулевые или пустые значения? Можно ли это решить используя формулу ВПР? или какую то другую формулу.
Я бы написала бы так =ВПР(“Ершик”;’Лист 1′!A1:В27;2;ЛОЖЬ). но при повторяющихся значениях он возьмет только первую цифру. А как все собрать и сложить?
По описанию вам нужно посчитать сколько раз встречается слово Ершик. Для этого используется функцию СЧЁТЕСЛИ https://sirexcel.ru/priemi-excel/kak-poschitat-kolichestvo-yacheekznachenij-v-excel/
Здравствуйте!
Скорее всего Ольга имела ввиду, как сложить результат формулы ВПР. Так же не могу найти выход!
Например:
Наименование – Цена
Ершик 10.50
Метла 12.00
Табурет 40.22
Стул 20.11
Ершик 15.22
Ковер 19.40
Карандаши 13.40
Ершик 14.66
Как видим, тут “Ершик” встречается три раза с разными суммами.
Вопрос: Как получить ответ Ершик 40.38? Условие поиска “ершик”
Добрый день! Спасибо, это я поспешил с ответом. Нужно использовать суммесли.
Сначала можно сделать таблицу с уникальными товарами а потом посчитать напротив общую сумму.
https://sirexcel.ru/priemi-excel/funkciya-summesli-v-excel-s-primerami/
А как быть, если вместо чисел текстовые значения и нужно получить их список в другом месте?
На первом листе две колоники: Дата и Название команды.
Нужно на другом листе выводить список команд, которые соответствуют определенной дате. Т.е. я ввожу дату, мне показывается список команд.
Текст нужно обернуть в функцию ЗНАЧЕН()
Здравствуйте. Подскажите пожалуйста.Вот такая задача. Есть стольбцы A,B,C,D,E. Есть ещё стольбцы G,H,I,J,K. Надо найти совпадения по стольбцам A и G и сортировать все G,H,I,J,K стольбцы не на новых а на своих местах.
Также надо учесть что в A есть данные который нет в G и наоборот.
см. фаил
Нет файла во вложении
Добрый день!
Подскажите, пжл, как решить проблему. Функция ВПР строится по диапазону A:AV;45;0, Возможно ли при добавлении столбцов внутри диапазона сохранить чтобы формула ВПР была привязана к определенному столбцу. например при добавлении 3-х столбцов менялся диапазон A:AY;48;0
сделайте строчку с номерами 1,2,3 и так далее и потом указывайте не 45, а ссылку на число 45. После того как добавите столбцы, просто протяните заново 1,2,3.. и т.д
Либо в вашем варианте у вас диапазон начинается с первого столбца, поэтому удобно вместо 45 написать просто столбец(), который и вернет число 45. Когда добавите столбцы, это не испортит формулу.
Если диапазон начинался бы со столбца B, то нужно было бы написать Столбец()-1
Вложение .docx
Внесла исправления в формулу (Добавила вместо номера столбца -Столбец(). Не работает формула. Прилагаю пример с ошибкой. Помогите, пжл, разобраться…
Вариант сделать строчку с номерами, а при добавлении протягивать-не подходит, тк к каждому столбцу написана формула впр на отдельный лист, листов много, протягивать на каждом формулы не получится.
По поводу первого совета со СТОЛБЕЦ() в ВПР сейчас понял, что я вам глупость порекомендовал.
Вы неправильно описали условия задачи, как можно было понять, что искомая таблица находится в другом месте. В этом случае этот способ не поможет. Вам нужно разобраться со смыслом использования СТОЛБЕЦ(), тогда бы сразу это стало понятно.
Функция СТОЛБЕЦ() возвращается номер столбца той ячейке в которой прописана. Если на другом листе поменяется столбец, то это никак не отобразится на этой формуле.
Вообще решая подобную задачу нужно исходить из условия по которому вы определяете номер столбца. В вашей задаче как вы определяете, что нужные значения находятся в столбце AW? По скриншотам закономерности нет. Например по дате. Тогда нужно искать номер столбца с помощью функции ПОИСКПОЗ, привязавшись к дате. Если нет, то самый правильный способ это все же в на листе где сводная, добавить вспомогательную строку 1 и там, например протянуть ту же формулу СТОЛБЕЦ() на всю строку. В итоге у вас в ячейке AW1 будет номер столбца, который будет меняться как надо, если вы будете добавлять другие столбцы. Не забудьте закрепить номер строки, чтобы она не менялась. Вот так будет выглядеть формула
=ВПР(B:B;сводная!A:AW;сводная!AW$1;0)
Добрый день!
Ситуация следующая: есть значение “Ведро” в таблице 1, в таблице 2 в строке “Ведро” имеются следующие данные – 21.09.2016 3 яблока; 21.10.2016 5 яблок; 21.11.2016 1 яблоко. Вопрос: как в таблицу 1 подгрузить значение на последнюю дату (21.11.2016)? Так чтобы привязка была не к конкретной дате, а к последней.
Пример:
Ведро значение Х на 21.11.2016
ведро 21.09.2016 3 яблока
ведро 21.11.2016 5 яблок
ведро 21.10.2016 1 яблоко
Используете ВПР, исходную таблицу берете со всеми данными, аргумент номер_столбца ищите с помощью функции ПОИСКПОЗ и НАИБОЛЬШИЙ, которая вернет позицию самого последней даты
Добрый день! Возможен ли с помощью ВПР перенос данных не с одной таблицы в другую, а с трех в одну (так как общее число строк 2,5 млн.)?
смотря какая задача. в любом случае если ВПР, то лучше использовать ИНДЕКС – он работает быстрее. ВПР скорее всего не справиться,тем более с тремя таблицами
добрый день! подскажите пожалуйста такой момент:
непример в таблице 1 есть: А=1 В=1 и С=1. Использую в другой таблице ВПР: поиск по 1 значения А, В или С. Функция работает корректно: она выдает мне первое встретившееся значение равное 1, например А.
Как сделать так, чтобы выцеплять данные таким образом, чтобы если А уже использовалось, то он показывал В, С и тд?
Если такая функция или это набор нескольких?
спасибо
Вложение
Если значений всего и всегда три, то можно перед искомым значением. У вас это столбец с 1, добавить вспомогательный столбец.
и добавить счетчик одинаковых значений и потом уже искать по искомому значению со порядковым номером
Здравствуйте,подскажите пожалуйста,как в Excel,при вводе числа выбивало значение прописью?
Вот тут написано: https://sirexcel.ru/makrosi_vba/kak-propisat-summu-chislo-cifry-propisyu-v-excel/
Добрый день. Продлив формулы ВПР выдает не верные значение. Тоесть если брать данный предмет, следующий строкой в формуле является значение “Стол”. При данной формуле из таблицы “Прайс-лист” он выдает мне значение соответствующей строки, а не значения “Стол”. Тоесть в данном случаи в строку цены стола он мне вставляет цену веника. как это можно исправить?
Нужно закреплять диапазон, так как при протягивании вниз этот диапазон сдвигается. А так должно все работать.
Попробуйте еще раз внимательно проверить формулу.
Загляните сюда https://sirexcel.ru/priemi-excel/funkcii/pochemu-ne-rabotaet-formula-vpr-vlookup-v-excel-reshenie/
там указано по каким причинам может не работать функция ВПР, если ничего не поможет, то присылайте пример. Пример из страницы работает корректно и при протягивании вниз.
Скажите, каким образом можно выделить повторяющиеся ячейки в двух столбцах на разных страницах или на одной? Условное форматирование не подходит
Можно и с помощью ВПР в соседнем столбце, просто у вас будет таблица из одного столбца и возвращать вы будете сам этот столбец. Если искомое значение (из одного столбца) будет найдено (то есть повторяется) в таблице (второй ваш столбец), то будет возвращено это же значение, если оно не повторяется во втором столбце, то будет возвращена ошибка.
Будет выглядеть как-то так:
=ВПР(A1;$F$1:$F$2000;1;0)
где A – это первый столбец
F – второй столбец
Добрый день. Вы мне очень помогли. Есть вопрос. Можно ли осуществлять поиск значения по названию файла. Т.е. в столбце А есть ведро. И есть файл с названием “ведро”. В этом файле в ячейке В75 есть нужное мне значение. Можно ли в определенной папке искать файл с названием “ведро” и при совпадении, брать значение этой ячейки В75.
Можно было бы воспользоваться функцией ДВССЫЛ
https://sirexcel.ru/priemi-excel/ssylki-na-odnu-i-tu-zhe-yachejku-s-raznyx-listov/
но насколько я помню она не может подтягивать данные из закрытых файлов.
то есть эти файлы должны быть открыты, чтобы функция смогла бы подтянуть данные.
При изменении размеров браузера, ваш сайт постоянно загружается заново, что очень мешает
Всем доброго времени суток.
Может подскажите такой вопрос: Использую ВПР для заполнения одного документа.
Заполняет он его как надо, но не сохраняет шрифты из исходного места!
В источнике часть текста пишется жирным шрифтом, а часть нет.
А так куда вставляется (из источника) весь текст одного шрифта (без жирного)
Как это можно исправить?
СПАСИБО!!!!!!!!!
Здравствуйте! Думаю без макросов тут не обойтись. Как сделать макросами, так сразу код не напишу, но видимо нужно просто проходить по списку, находить нужное значение и полностью с форматом копировать к нам в таблицу.
Вложение workbook_sec4.xls
Привет. Помогите мне плиз с лист1 (VLOOKUP) question 5 и с третьим листом (Student Info). Буду очень благодарен.
Здравствуйте!
Подскажите, в чем у меня ошибка в формуле, если мне, например, надо вынести Цену ведра (из вашего примера), а у меня в результате выносит не “120” , а “Цена, руб” и, если я копирую формулу вниз, то везде результат один – “Цена, руб”?
Скорее всего вы промахнулись и искомое_значение выбрали не ведро A3, а товар (в шапке таблицы) A2 и вдобавок скорее всего вы закрепили эту ссылку. То есть указали $A$2
Вложение -инструмента.xlsx
Подскажите пожалуйста, а можно при помощи ВПР найти нужный текст и скопировать его в соседнюю ячейку ?
Тоисть, есть база, где куча текста, нужно найти с этой базы скопировать текст что находиться в соседней ячейке.
Да, можно. Для этого необходимо сделать сортировку с помощью формул. Попробуйте разобраться вот по этой статье: https://sirexcel.ru/priemi-excel/spiski-i-formy/formirovanie-spiska-s-pomoshhyu-formul-excel/
Здравствуйте.
Ситуация следующая:
Таблица 1 – список ключей, в нее через ВПР() добавляются данные [код, ВПР()]
Таблица 2 – источник данных для Таблицы 1 [код, значение_для_таб1]
Если ключи совпадают – все ОК.
Если в Таблице 2 (источник) ключ отсутствует – в Таблицу 1 попадает не Н/Д, а какое-то значение, похоже что последнее найденное для предыдущего ключа.
Подскажите как решить данную проблему.
У вас скорее всего пропущен последний аргумент в функции ВПР – интервальный просмотр, который вам необходимо указать в качестве 0, то есть точный поиск, у вас скорее это значение пропущено, поэтому ищется приблизительное значение
Здравствуйте!
Помогите пожалуйста. Во вкладку Сформировать_ОСВ нужно подтянуть Наименование счета (столбец В) из вкладки План счетов при помощи ВПР.
В строку В3 прописала формулу =впр(A3;’План счетов’;D7;0). Выходить ошибка – Ошибка в формуле. Не могу понять, что именно не так. Зранее спасибо
Нет примера, поэтому не совсем понятен вопрос. По формуле ‘План счетов’ я так понимаю это у вас именованный диапазон? Если нет, то можно указать диапазон столбцов. У вас вроде это A:B. Вместо D7 должен быть номер столбца, у вас видимо это столбец 2
Большое спасибо! Все очень понятно, наконец-то разобрался с ВПР, отличная и полезная функция. Автору респект и уважуха
Вложение
Добрый день помогите пожалуйста в создании формулы:
У меня есть товар и цена, и есть еще одна колонка в разброс так же товар и разная цена, нужно отфильтровать в третью колонку если у товара осталась цена преждняя(после сравнения 1 колонки и 2 колонки) то ее не менять, если изменилась то изменить и внести в 3 колонку.
Буду благодарен вам!
Вложение prays_01_07_2015.xlsx
здравствуйте. мне нужно чтобы в таблице накладная на листе аленка романенко 9 высвечивались только те наименования которые будут указаны в таблице на листе магазин
Решили вопрос?
Не понимаю, что за Аленка и что за Романенко
Добрый день,вопрос есть две таблицы,пример: в одной заказ и номер машины в одной ячейке,а в другой просто заказ,допустим BOP237569\1 5 машина 01.05.2015
а во второй BOP237569\1 01.05.2015
,как можно через впр сделать сравнение,можно ли искать по заказу?Просто из за слова машина не видит заказ
Я так понимаю, что номер машины имеет одинаковый формат (кол-во символов)
Поэтому можно сделать доп. столбец где отобразить только номер машины.
=ЛЕВСИМВ(ссылка_ячейка;11), где 11 – это кол-во знаков в номере. А дальше уже можно с помощью ВПР, если же дата тоже в этой же ячейке, но нужно еще сцепить дату с другой стороны
Абалдеть!!! Автору громадное человеческое спасибо, мне помогло!!! Просто и доступно. Краткость – сестра таланта)))
Вопрос на перспективу:
Можно ли создать .exe самостоятельный файл, в котором можно вводить определенные данные (а лучше массив из .txt) и чтобы в окошечках выводился определенный ответ в окошечке/ах, а все расчеты происходили в фоновом режиме в таблице Excel? Сама таблица формул и расчетов у меня есть.
И как дорого это будет стоить? (в пределах разумного конечно)))
Ответ да или нет, пожалуйста сюда: [email protected]
Чтобы обратная связь сохранилась на почте
Да, так можно сделать макросами, но в данный момент заказы я не беру. Но это будет не отдельный exe файл, а файл Excel где будет открываться форма, куда необходимо ввести данные или выбрать файл txt
Добрый день!
Помогите, пожалуйста, разобраться.
Формула получается следующая: =VLOOKUP(B5,$G$2:$H$371,2,0)
НО, в ячейке отображается не текстовое значение, которое должно быть, а формула (хотя в мастере формулы в строке Result отображается нужный текст).
При протягивании также копируется текст формулы без изменений.
(версия Excell for Mac 2011)/
Спасибо!
Скорее всего у ячейки задан формат “текстовый”, надо сменить на “общий”
Не работа на Excell for Mac 2011, но если по аналогии с Excel на PC, у вас вместо запятых должны быть точка с запятой
=VLOOKUP(B5;$G$2:$H$371;2;0)
Но если в мастере формул все отражается правильно, возможно, это просто отображение содержимого ячейки.
Зайдите во вкладку “Формулы” – “Зависимости формул” и там отожмите “Показать формулы”.
В англ. версии не могу сказать как, вот по картинке посмотрите
Пробовала и запятыми, и точкой с запятой…
Нашла видео для нужной версии (https://youtu.be/J1xPOekfmY8), все так же, как у вас, но все равно не получилось…
Ок, попробую еще сейчас в настройках.
Добрый день!Подскажите пожалуйста ,как исправить ошибку,возможно использую не ту функцию. Выбор данных сравниваемого значения происходит из всплывающего списка,мне нужно чтобы в соседнем столбце автоматически прикреплялось значение из существующей таблицы(находится на другом листе) после выбора нужной позиции. Использовала и формулу ВПР и Индекс. Один раз считает правильно,но когда выбираю из списка повторное значение(которое ранее уже выбирала) ответ не выдается #НД
Добрый день! С выпадающим списком должно работать, без примера файла трудно что ответить
бля наконецто врубился по самой распространенной ситуации с заказами и ценами в впр по первому примеру. спасибо
Пожалуйста!
Добрый день! Нужна ваша помощь.у вас на примере таблица заказов и прайс.Мне нужно чтобы ссылка прайса была не на одну ячейку ,а на две .допустим кровать в одном столбце ольха в другом.чтобы ссылка распознавала и предмет т.е. кровать и породу древесины. так вот чтобы расценка на кровать из ольхи была по одной цене, а кровать из ясеня по другой.заранее спасибо!!!!!
Если вы имеет виду, что у вас есть столбец с изделием кровать и другие и в другом столбце есть материал. То есть есть кровать из ольхи, дуба и т.к далее, решение простое. Делаете доп. столбец, где сцепляете эти данные в исходной таблице, обязательно с левой стороны этой таблицы.
Получаете такой столбец. КроватьОльха, КроватьДуб и т.д
Дальше там где используете ВПР в качестве искомого значения указываете не просто ссылку на слова кровать, а на такие же сцепленные данные “A1&B1” (в столбце A – изделия, в столбце B – материал). Либо просто создаете так же доп. столбец КроватьОльха, КроватьДуб и искомое значение указываете как ссылку на эти ячейки
Добрый день! Нужна Ваша помощь. Есть две таблицы с кодами клиента, необходимо найти коды в левой таблице (она больше) из правой (она меньше), и те коды которых нет в правой таблице, как то обозначить в левой таблице. Спасибо.
Добрый день! Надежда, решили задачу?
Имеется магазин и склад. При появлении товара на складе, он добавляется в магазин. В таблице ведется статистика, если товар добавился то в «Добавлено» пишем 1 и в выпадающий список вносим артикул товара, затем в ячейке “Всего товара” счетчик фиксирует количество. Также проверяется наличие и отсутствие товара на складе в ячейках “В наличии”, “Нет в наличии”, поэтому в соседних ячейках разместить список не получится. Эта статистика периодически очищается, кроме показании счетчиков.
Анатолий, увы ничего в голову не приходит.
… вы имеете ввиду, что вы добавили цифру 1 и добавили новую ссылку, после этого она должна сохраниться в раскрывающемся списке и так при добавлении ссылок они должны там накапливаться?… Да, должно накапливаться, 1…ссылка, 2…ссылка и желательно все в одной ячейке (как в таблице).
в других ячейках список ссылок будет одинаковый или у каждой ячейки будет свой?
у каждой свой.
для чего нужен раскрывающийся список? нужно будет когда-нибудь для чего-то выбирать какую-ту ссылку, или это просто для компактности?
В поле «Добавлено» вы всегда вводите цифру 1? можно ли рассмотреть вариант, чтобы добавлять не цифру 1, а просто ссылку, а цифра 1 будет автоматически прибавляться к нужному число в коде? или есть нюансы?
Можно ли расположить раскрывающийся список в дополнительном соседнем столбце?
Не совсем так, в ячейку «Добавлено» ввел 1 и в эту же ячейку ввел ссылку на этот товар, нужно чтобы ячейка имела выпадающий список с количеством добавленных ссылок.
ничего не понятно. Ссылку на товар – это что? гиперссылка? или что? пишите, что ввели ссылку и в то же время там должен быть раскрывающийся список. В общем вообще ничего не понятно.
Да, именно гиперссылка.Я сейчас пришлю таблицу.
Как выглядит теперь понятно. Но остальное не понятно.
Вы ввели цифру 1 и в эту же ячейку ввели ссылку. А потом пишите, “нужно чтобы ячейка имела выпадающий список с количеством добавленных ссылок.”
Так все таки вы хотите вводить ссылку или выбирать из раскрывающегося списка?
Если выбирать, то что означает “с количеством добавленных ссылок”? вы имеете ввиду, что вы добавили цифру 1 и добавили новую ссылку, после этого она должна сохраниться в раскрывающемся списке и так при добавлении ссылок они должны там накапливаться?
В общем, может лучше описать, чего вы хотите добиться, может можно найти более нормальный вариант реализации.
https://yadi.sk/i/Gb3uWAsEZneZf
EXCEL, Вы помогли мне со счетчиком, спасибо. Встал другой вопрос, можно ли в ячейке «Добавлено» разместить еще и выпадающий список со ссылками на добавленный товар?
т.е чтобы не вводить данные, а выбирать из раскрывающегося списка?
Если так, то просто делайте раскрывающийся список и в коде везде где был минус единица – уберите ее.
Если имеете ввиду другое, то объясняйте более детально
Спасибо огромное за помощь! я разобралась, сделала в дополнительном столбце А раскрывающийся список, где можно выбрать номер работника, а в ячейках Н24, К24 формула ВПР, где искомое значение ячейка А24.
Все, разобрался. Спасибо!
Я изменил строчку в макросе
Cells(ActiveCell.Row – 1, 10).Value = a + 1
Все стало как надо, спасибо! Как применить это ко всем строкам?
Вот, это то, что нужно! Только добавлять в ячейку «Всего товаров» нужно не содержимое ячейки «Добавлено» а 1. Просто, какое бы я число не ввел, счетчик «Всего товаров» увеличивался бы на единицу.
1,2,3,4,5…
А можно сделать так, в ячейке “всего товаров” прописать переменную, которую, можно редактировать, а уже к ней прибавлять 1 при добавлении товара, т.е реагировать на событие в ячейке “добавлено”?
Написано на коленке. Надеюсь, что самостоятельно доработаете.
Чтобы работало обязательно после ввода данных в “Добавлено” нужно нажать Enter, чтобы курсор перешел на следующую ячейку.
После нажатия на Enter это число будет добавлено к ячейке с столбце “Всего товара”
https://yadi.sk/d/KZlX_IB_ZcZSk
Оно будет реагировать на изменение ячейки. Но опять так, как я вам и писал все минусы которые я описывал – сохраняются
“А можно сделать так, в ячейке «всего товаров» прописать переменную, которую, можно редактировать, а уже к ней прибавлять 1 при добавлении товара, т.е реагировать на событие в ячейке «добавлено»?”
От этого ничего не поменяется. Давайте для просто понимания сделаем доп. столбец и там будет сохранять данные этой переменной. Но данные этой переменной – это текущая цифра “Всего товаров”. Мы ее храним и когда добавляется товар, добавляем к ней эту цифру и и переписываем переменную, так как число во “всего товара” изменилось.
Если тут вы ошиблись при вводе “Добавлено” в переменную будет записано неверное число сразу же и снова будет ошибка и назад вернуть нельзя будет.
Если что-нибудь придет на ум – напишу
Нет,желательно, для всех строк (групп). 8 строка просто пример. Хотя можно сделать для одной, просто, чтобы понять принцип.
Сорри, за длительный ответ. Написать не проблема, но тут такая проблемка. Очень легко допустить ошибку. Допустим мы прописали цифру, она автоматически прибавилась. А если вам потребуются изменить цифру, вы поменяете данные, а они опять прибавятся, случайно вставите еще раз эту цифру, тоже будет добавлено. Как в этом случае быть?
Можно например добавить кнопку. Далее вы добавляете цифру, потом нажимаете на кнопку и она уже прибавляет данные к этому числу. Так было бы более безопасно. Такой способ вам подойдет?
Вот посмотрите:
https://yadi.sk/d/4RWcV3YbZWAvp
Данное условие должно сохраняться только в строке 8?
А Вы могли бы написать подобный макрос? Файл эксель я пришлю с подробными пояснениями.
Не обещаю, но присылайте, если много времени не потребует – посмотрю
Здравствуйте, возникла небольшая проблема, например нужно организовать элементарный счетчик товара с сохранением результата в ячейке. Допустим было (с1)10 товаров сегодня добавили (в1)5 ,значение стало (с1+в1) 15 и при очистке ячейки (в1) с цифрой 5, значение 15 так и осталось. Завтра добавили еще 3 товара и значение сохранилось 18 и.тд. Думаю формулами экселя это возможно, не прибегая к вба.
Если я все правильно понял, то формулами это сделать нельзя, так же как в предыдущем комментарии у NATALLIA, подсчет будет вестись с помощью формул. Формуле прописывается сложить с1 и b1, если вы очистите любую из ячеек. Результат автоматически также поменяется.
Выходит либо вба либо каждый раз сохранять результат как значение
Либо вести учет по другому. Например, не удалять старые данные товара, а добавлять их дальше снизу, добавив столбец, например с датой (счетчиком, временем). А дальше можно вытаскивать любые данные, а так же представлять их в нужном виде
Нужно, например, чтобы в ячейке К24 был Петров, значит я нахожу Петрова в таблице BE$23:CB$103 (он под номером 2) и выбираю в ячейке ВЕ23 № 2. Далее, в ячейке К25 должен быть Никитин, значит я снова в ячейке ВЕ23 выбираю 5 и т.д.Я могла бы сделать формулу такую: =ВПР(BE28;BE$23:CB$103;9), т.е искомое значение ВЕ28 каждый раз менять в ячейке Н24 и К24, и дальше в Н25, Н26, Н27, К25, К26, К27, а хотелось бы, чтобы при вводе в ячейку ВЕ23 нужный номер автоматически менялись данные в ячейках Н24, К24, потом снова в ячейку ВЕ23 ввести другой номер и чтобы поменялись данные в ячейках Н25, К25 и т.д.
Т.е вы хотите так. Выбрать 2, чтобы появились данные по Петрову, далее вы хотите, чтобы выбрав другой номер, например 4, данные по Петрову сохранились, а в следующей строке появились данные по Антонову (цифра 4).
Все верно? Если это так, то формулами это сделать нельзя, только макросом. Так как, когда вы введете новую цифру, то все данные поменяются автоматически – ведь это формула.
Пока вы отвечаете на этот вопрос, задам еще один.
Если вы сами решаете какую фамилию вы хотите видеть в столбце К, почему не сделать раскрывающейся список в ячейках К24, k25 и т.д где вы будете выбирать фамилии которые вы хотите видеть, а остальные данные подтягивать уже по ФИО.
Либо сделать дополнительный вспомогательный столбец, например “B”, где вы будите из раскрывающегося списка выбирать порядковый номер сотрудника, а все остальные данные уже будут подтягиваться из таблицы.
да, там опечатка. А как я могу вам отправить свой вариант в формате .хslx?
Возможно для вас это кажется простым, но получив файл все равно не понятно, что вы хотите сделать в вашем файле.
В раскрывающемся списке выбираем номер сотрудника из таблицы. Например 2 – это Петров.
Далее понятно, что в ячейке H24 у вас должен отразиться код организации. С этим понятно.
Далее в K24 должно отразиться ФИО это сотрудника.
А что дальше нужно при протягивании? Просто видимо логика не продумана или я снова не понимаю, что вы хотите сделать.
Если протянуть h24 и k24, то искомое значение у вас всегда будет BE23 – то есть 2 в нашем примере. А значит всегда данные будут по сотруднику номер 2 и они не будут меняться как это и происходит сейчас.
не понятно какие данные должны быть в столбце h и k
Объясните или заполните данные как вы это хотите видеть без формул, думаю так будет понятнее
[email protected]
В ячейке А1 формула ВПР, где дожно отражаться фио работника. В ячейке В1 раскрывающийся список (№ п/п), а в ячейке С1 фио. Формула выглядит следующим образом: =ВПР(В1;В$1:С$15$2). Например, я ищу нужного мне человека в столбце С и смотрю соответствующий ему № п/п, нахожу этот номер в раскрывающемся списке и выбираю, а при протягивании формулы отражается одно и тоже фио, т.е. если я выберу работника под номером 1, то в во всех строках столбца А будет один и тот же человек и соответственно если выбрать номер 5 будет тоже самое. Получается только тогда, когда формула выглядит так: =ВПР(1;В$1:С$15$2), т.е. если не ссылаться на раскрывающийся список.
Добрый день! Из описания не совсем понятно, для чего это может понадобитmся, в таком виде как вы это описываете. Искомое значение у вас находится в самой таблице данных.
Во-первых раскрывающийся список не должен никак влиять
во-вторых формула у вас неверна
у вас =ВПР(В1;В$1:С$15$2)
надо =ВПР(В1;В$1:С$15;2;0)
Думаю доллар это у вас опечатка, в вот ноль в конце вы пропустили
В-третьих посмотрите у меня в файле все работает, так же я там добавил свой вариант как это должно выглядеть исходя из вашего описания
https://yadi.sk/i/dSesTnShZRbB8
Искомое значение-ФИО работника, которое выбирается из другой таблицы из раскрывающегося списка. При протягивании формулы (функция ВПР) фио работника не меняется. Как сделать, чтобы при протягивании формулы были разные фио?
Вопрос не очень понятен. У вас есть таблица с фио и какими-то данными. В другом месте, например в ячейке A1 у вас есть раскрывающийся список с ФИО из этой таблицы. А в соседней ячейке B1 у вас прописана формула с ВПР, таким образом протягивая вниз эту формулу и расскрывающий список у вас не работает ВПР так? Если правильно понял, то возможно у вас в формуле ВПР не закреплен диапазон с помощью знака $, либо пришлите пример файла, так как объяснили вы непонятно
функция ВПР всегда ищет искомое значение в самой леыой строке? или это можно както указать? что если мне надо найти например значение во втором столбце таблицы, а взять значение из первого?
Добрый день! Всегда ищет в крайнем левом столбце.
Можно просто дублировать столбец справа и тогда вы сможете
использовать ВПР
А можно использовать функцию ИНДЕКС в сочетании с ПОИСКПОЗ
Добрый день!
Подскажите пожалуйста какой формулой можно расширить функцию ВПР чтобы она подтягивал данные из конкретной строки. Пример: есть один договор доходный ему соответствуют две строки одна по проектным работам другая по строительномонтажным. необходимо в итоговый формат в первую строку подтянуть все проектным работам во вторую строку все по строительномонтажным.
Добрый день! Не понятен вопрос. Можете привести пример как это должно в итоге выглядеть
Каеим образом сверить с помощью ВПР фамилии набранные на разных регистрах – Все заглавные в одной таюлице и заглавные только первык буквы во второй.
В функции ВПР регистр не имеет значение. Какая стоит у вас задача? Сверить для чего?
Благодарю!
Огромнейшее спасибо – это именно то решение,которое нужно (мне не хватило знаний для использования функции “наименьший”).
Пожалуйста!
Перенести данные надо по Иванову на один лист,по Петрову на второй и т.д.
Данные постоянно обновляются ( в день добавляется 5-6 строк), в списке порядка 30 фамилий, то есть к номеру привязаться не вариант…Очень похоже, что одним только ВПР-ом не обойтись…Нужно, что бы ВПР искал сначала по записи в первой строке ( как он это и делает), а затем начинал искать значение со второй строки(точнее со следующей,содержащей значение “Иванов”) проигнорировав уже найденное…
Немного кустарным способом, но можно сделать вот так скачать
Я сделал все на одном листе, но главное, чтобы вы поняли. Просто выбирайте из списка E1 разные фамилии.
Можно один раз настроить на одном листе, а дальше копировать листы изменяя фамилию только в ячейке E1
Извините, отражается в ответе не как на экране
1 Иванов 500
2 Петров 200
3 Иванов 400
4 Иванов 150
Это исходные
Результат нужен такой :
Иванов 500
Иванов 400
Иванов 150
Опять таки не очень понятно. Я так понял, что нужно перенести только Иванова?
В вашем случае ВПР нельзя использовать по “Иванову” т.к они повторяются т.е не уникальные значения.
Для решения можно привязаться к номеру, номер указать в скрытом столбце за таблицей. И впр использовать с искомым значением номера.
1 Иванов 500
3 Иванов 400
4 Иванов 150
Как сделать более удобно это зависит от самой задачи. Нужно ли вам постоянно переносить эти данные или просто нужно большой объем данных перенести один раз.
Нужно немного не это,
Есть данные : Нужно получить:
1 Иванов 500 Иванов 500
2 Петров 200 Иванов 400
3 Иванов 400 Иванов 150
4 Иванов 150
Добрый день.Есть таблица данных, первый столбец – уникальные номера, вторая – фамилии, которые периодически повторяются,третий столбец с данными.Задача – в новые таблицы перенести последовательно данные 2-х столбцов. Для примера 1.( яч.А1) Иванов(А2) 500(А3) 2.(В1) Петров(В2) 200(В3) 3.Иванов 400 4. Иванов 150 В таблице на новом листе должно быть : Иванов(А1) 500(В1) Иванов(А2) 400(В2) Иванов(А3) 150(В3) Если использовать ВПР, то он будет возвращать первое значение ( А3 первого листа). Не могу понять как найти значения следующих ячеек…
Может вам воспользоваться вот этой функцией ТРАНСП
Функция ТРАНСП сможет сделать вот так
Добрый день! Вы пишите, что есть таблица, где первый столбец это уникальные номера. Но в тоже время пишите, что эти номера находятся в ячейке A1, B1.
Как правильно?
1 Иванов 500
2 Петров 200
или
1 2
Иванов Петров
500 200
Как сделать чтобы бралось второе найденное значение?
То есть искомое значение встречается несколько раз и вы хотите чтобы бралось значение в строке которая встречается свою второй раз? Просто впр не получится + должна быть какая-то закономерность чтобы сделать костыль
блин, наконец-то врубился,только по ваше информации функция таблицы заработала. Спасибо
Рады были помочь
Подскажите пожалуйста, в моем случае искомое значение бывает как в текстовом формате так и в числовом. Так вот, в строках где это значение цифра, в результате выдается ошибка. Как можно правильно решить такую проблему? Спасибо.
Добрый день! Желательно, сделать еще один столбец и там приводить к одному формату, например можно воспользоваться функцией =ТЕКСТ(А1;”ДД.ММ.ГГ”), где А1 – это дата в числовом или текстовом формате и если дата у вас выглядит как 12.04.12 (обратите внимание на год – в формате указаны последние числа года, если дата полная то нужно указать ГГГГ), тогда эта дата будет восприниматься как тест. Если, нужно что-то другое, то пишите более подробно или с примером
Спасибо, получилось!!!!
Подскажите, пожалуйста, эта функция работает, если у меня не таблица заказов, а ячейка с выпадающим списком, рядом с которой пустая ячейка для автоматического заполнения цены? Спасибо!
Да, конечно, функция будет работать
какую функцию можно использовать для поиска не из крайнего левого столбца, а из любого другого? четвертого, например
Можно использовать функцию ИНДЕКС: https://sirexcel.ru/priemi-excel/funkciya-indeks-v-excel/
совместно с функцией (находим номер строки или столбца или и то и другое) https://sirexcel.ru/priemi-excel/funkciya-poiskpoz-v-excel/
Что делать если ВПР перенес с одного листа на другой некорректную информацию.
например я переносила информацию о пролонгации договоров контрагентов, так вот он перенес значение С ПРОЛ. тому контрагенту которого в списке переносимых значений (т.е в списке листа с которого переносилась инфо о пролонгации) нет вообще.
Возможно Вы не указали интервальный просмотр (параметр функции ВПР). Просьба прислать пример файла/фрагмента с используемой формулой
(Отправил запрос на указанный Вами e-mail).
Огромное спасибо!!!
Очень интересный пример и для школьников. Спасибо!
Спасибо. Хороший материал для студентов.
Спасибо,очень понятно!!!
Пожалуйста! Были рады помочь
объяснили доступным языком. Привели наглядный пример. Спастбо!!!
Пожалуйста!