Функция ВПР (VLOOKUP) в Excel для чайников
Функция ВПР в Excel (на английском — VLOOKUP) по некоторому ключевому полю «подтягивает» данные из одного диапазона в другой. Ключевое поле должно присутствовать в обоих диапазонах данных (и там, куда «подтягиваем», и там, откуда берем данные).
Функция ВПР в Экселе: пошаговая инструкция
Представим, что перед нами стоит задача определить стоимость проданных товаров. Стоимость рассчитывается, как произведение количества и цены. Сделать это очень легко, если количество и цены находятся в соседних колонках. Однако данные могут быть представлены не в столь удобном виде. Исходная информация может находиться в совершенно разных таблицах и в другом порядке. В первой таблице указаны количества проданных товаров:
Во второй – цены:
Если перечень товаров в обеих таблицах совпадает, то, зная магическое сочетание Ctrl+C и Ctrl+V, данные о ценах можно легко подставить к данным о количестве. Однако очередность позиций в обеих таблицах не совпадает. Тупо скопировать цены и подставить к количеству не получится.
Поэтому мы не можем прописать формулу умножения и «протянуть» вниз на все позиции.
Что делать? Надо как-то цены из второй таблицы подставить к соответствующему количеству в первой, т.е. цену товара А к количеству товара А, цену Б к количеству Б и т.д.
Функция ВПР в Эксель легко справится с задачей.
Добавим вначале в первую таблицу новый столбец, куда будут подставляться цены из второй таблицы.
Для вызова функции с помощью Мастера нужно активировать ячейку, где будет прописана формула и нажать кнопку f(x) в самом начале строки формул. Появится диалоговое окно Мастера, где из списка всех функций нужно выбрать ВПР.
Кликаем по надписи «ВПР». Открывается следующее диалоговое окно.
Теперь нужно заполнить предлагаемые поля. В первом окошке «Искомое_значение» нужно указать критерий для ячейки, в которую мы вписываем формулу. В нашем случае это ячейка с наименованием товара «А».
Следующее поле «Таблица». В нем нужно указать диапазон данных, где будет осуществляться поиск нужных значений. В нашем случае это вторая таблица с ценой. При этом крайний левый столбец выделяемого диапазона должен содержать те самые критерии, по которым осуществляется поиск (столбец с наименованиями товаров). Затем таблица выделяется вправо минимум до того столбца, где находятся искомые значения (цены). Можно и дальше вправо выделить, но это уже ни на что не влияет. Главное, чтобы выделенная таблица начиналась со столбца с критериями и захватывала нужный столбец с данными. Также следует обратить внимание на тип ссылок, они должны быть абсолютными, т.к. формула будет копироваться в другие ячейки.
Следующее поле «Номер_столбца» — это число, на которое столбец с искомыми данными (ценами) отстоит от столбца с критерием (наименованием товара) включительно. То есть отсчет идет, начиная с самого столбца с критерием. Если у нас во второй таблице оба столбца находятся рядом, то нужно указать число 2 (первый – критерий, второй — цены). Часто бывает, что данные отстоят от критерия на 10 или 20 столбцов. Это не важно, Excel все сосчитает.
Последнее поле «Интервальный_просмотр», где указывается тип поиска: точное (0) или приблизительное (1) совпадение критерия. Пока ставим 0 (или ЛОЖЬ). Второй вариант рассмотрен ниже.
Нажимаем ОК. Если все правильно и значение критерия есть в обеих таблицах, то на месте только что введенной формулы появится некоторое значение. Остается только протянуть (или просто скопировать) формулу вниз до последней строки таблицы.
Теперь легко рассчитать стоимость простым умножением количества на цену.
Формулу ВПР можно прописать вручную, набирая аргументы по порядку, и разделяя точкой с запятой (см. видеоурок ниже).
Особенности использования формулы ВПР в Excel
Функция ВПР имеет свои особенности, о которых следует знать.
1. Первую особенность можно считать общей для функций, которые используются для многих ячеек путем прописывания формулы в одной из них и дальнейшим копированием в остальные. Здесь нужно обращать внимание на относительность и абсолютность ссылок. Конкретно в ВПР критерий (первое поле) должно иметь относительную ссылку (без знаков $), так как у каждой ячейки свой собственный критерий. А вот поле «Таблица» должно иметь абсолютную ссылку (адрес диапазона прописывается через $). Если этого не сделать, то при копировании формулы диапазон «поедет» вниз и многие значения просто не найдутся, так как искать будет негде.
2. Номер столбца, указываемый в третьем поле «Номер_столбца» при использовании Мастера функций, должен отсчитываться, начиная с самого критерия.
3. Функция ВПР из диапазона с искомыми данными выдает первое сверху значение. Это значит, что, если во второй таблице, откуда мы пытаемся «подтянуть» некоторые данные, присутствуют несколько ячеек с одинаковым критерием, то в рамках выделенного диапазона ВПР захватит первое сверху значение. Об этом следует помнить. К примеру, если мы хотим к цене товара подтянуть количество из другой таблицы, а там этот товар встречается несколько раз (в нескольких строках), то к цене подтянется первое сверху количество.
4. Последний параметр формулы, который 0 (нуль), ставить нужно обязательно. Иначе формула может криво работать.
5. После использования ВПР саму формулу лучше сразу удалить, оставив только полученные значения. Делается это очень просто. Выделяем диапазон с полученными значениями, нажимаем «копировать» и на это же место с помощью специальной вставки вставляем значения. Если таблицы находятся в разных книгах Excel, то очень удобно разорвать внешние связи (оставив вместо них только значения) с помощью специальной команды, которая находится по пути Данные → Изменить связи.
После вызова функции разрывания внешних связей появится диалоговое окно, где нужно нажать кнопку «Разорвать связь» и затем «Закрыть».
Это позволит удалить сразу все внешние ссылки.
Примеры функции ВПР в Excel
Для следующих примеров использования функции ВПР возьмем немного другие данные.
Требуется цены из второй таблицы подтянуть в первую. В качестве критерия здесь используется код. Ниже показаны этапы вычисления ВПР.
Вторая таблица меньше первой, т.е. некоторые коды в ней отсутствуют. Для отсутствующих позиций ВПР выдает ошибку #Н/Д.
Появление таких ошибок, кстати, можно использовать для пользы дела, когда нужно найти отличия в таблицах. Но, скорее всего, ошибки помешают.
Конструкция с функцией ЕСЛИОШИБКА
Вместе с функцией ВПР часто используют функцию ЕСЛИОШИБКА, которая «заглушает» ошибки #Н/Д и вместо них возвращает некоторое значение. Обычно это 0 или пусто.
Как видно, ошибок больше нет, а вместо них пустые ячейки.
Разные форматы критерия в таблицах
Одна из распространенных причин появления ошибок заключается в несовпадении форматов критериев в двух таблицах. Текстовый и числовой форматы воспринимаются функцией ВПР как разные значения. Возможны два варианта.
Первый случай, когда критерии в первой таблице сохранены как числа, а критерии во второй таблице – как текст.
В ячейках с числами, сохраненными как текст, в левом верхнем углу появляется зелененький треугольник. Можно выделить все такие числа и в раскрывающемся списке выбрать Преобразовать в число.
Такое решение используется довольно часто. Но оно не всегда подходит. Например, когда данные из второй таблицы регулярно выгружаются из какой-нибудь базы данных типа 1С. В подобных файлах вообще все сохранено как текст. И если мы планируем постоянно использовать такие данные, вставляя их в заранее подготовленный диапазон, то лучше, чтобы формулы работали без дополнительного вмешательства.
Автоматически изменить формат критерия во второй таблице нельзя, т.к. ссылка ведет на целый диапазон. Придется вмешиваться в ссылку на критерий в первой таблице. Для этого потребуется дописать функцию ТЕКСТ, которая изменит числовой формат на текстовый. Синтаксис функции ТЕКСТ предполагает обязательное указание формата. Достаточно задать формат #. Ниже картинка с готовой формулой.
Две ошибки по-прежнему связаны с тем, что эти товары отсутствую во второй таблице. Чтобы их заглушить, можно вновь воспользоваться функцией ЕСЛИОШИБКА.
Вторая ситуация, заключается в том, что «текстом» являются критерий из первой таблицы. Форматы снова не совпадают.
Как и в прошлый раз, будем вносить коррективы в функцию ВПР. Преобразовать «текст» в «число» еще проще. Достаточно к ссылке на «текстовый» критерий добавить 0 или умножить на 1.
Бывает еще и третья, смешанная ситуация. Она встречается гораздо реже. Это когда в первой и второй таблице критерии сохранены и как число, и как текст, вперемешку. Здесь потребуется задействовать сразу все описанные выше функции: ЕСЛИОШИБКА, ТЕКСТ и +0. Вначале прописываем ЕСЛИОШИБКА и в качестве первого аргумента этой функции записываем ВПР с какой-либо конструкцией для изменения формата. Например, ВПР с формулой ТЕКСТ. В качестве второго аргумента (т.е. того, что должно быть в случае ошибки) записываем вторую конструкцию ВПР с +0. Таким образом, если ВПР с функцией ТЕКСТ не выдает ошибку, значит все ОК. Но если первая конструкция возвращает ошибку #Н/Д, то функция ЕСЛИОШИБКА подставляет вторую конструкцию – ВПР с +0. Другими словами, мы вначале принудительно делаем все критерии текстовыми, а затем, числовыми. Таким образом, ВПР проверяет оба формата. Один из них совпадет с форматом во второй таблице. Немного громоздко получается, но в целом все работает.
Отсутствующие критерии по-прежнему вызывают ошибку #Н/Д. В таком случае всю формулу можно еще раз «обернуть» в ЕСЛИОШИБКА.
Функция СЖПРОБЕЛЫ для чистки текстового критерия
В качестве критерия рекомендуется брать уникальный код, в котором опечатки, характерные для текста, маловероятны. Но иногда все-таки кода нет и критерием выступает текст (названия организаций, фамилии людей и т.д.). В этом случае возможны случайные ошибки в написании. Одна из распространенных ошибок – лишние пробелы. Проблема решается просто с помощью функции СЖПРОБЕЛЫ для всех критериев. Сделать это можно внутри формулы ВПР, а можно и предварительно пройтись по всем критериям в обеих таблицах. Кому как удобней.
Подсчет номера столбца в большой таблице
Если во второй таблице много столбцов, да еще часть из них скрыта или сгруппирована, то подсчитать напрямую количество столбцов между критерием и нужными данными, весьма непросто. Есть прием, который позволяет вообще не считать эти столбцы. Для этого во время выделения второй таблицы следует посмотреть в нижний правый угол выделяемого диапазона. Там появляется подсказка о количестве выделенных строк и столбцов. Запоминаем число столбцов и вносим в формулу ВПР.
Здорово экономит время.
Интервальный просмотр в функции ВПР
Наступило время обсудить последний аргумент функции ВПР. Как правило, указываю 0, чтобы функция искала точное совпадение критерия. Но есть вариант приблизительного поиска, это называется интервальный просмотр.
Рассмотрим алгоритм работы ВПР при выборе интервального просмотра. Прежде всего (это обязательно), столбец с критериями в таблице поиска должен быть отсортированы по возрастанию (если числа) или по алфавиту (если текст). ВПР просматривает список критериев сверху и ищет равный, а если его нет, то ближайший меньший к указанному критерию, т.е. на одну ячейку выше (поэтому и нужна предварительная сортировка. После нахождения подходящего критерия ВПР отсчитывает указанное количество столбцов вправо и забирает оттуда содержимое ячейки, что и является результатом работы формулы.
Проще понять на примере. По результатам выполнения плана продаж каждому торговому агенту нужно выдать заслуженную премию (в процентах от оклада). Если план выполнен менее, чем на 100%, премия не положена, если план выполнен от 100% до 110% (110% не входит) – премия 20%, от 110% до 120% (120% не входит) – 40%, 120% и более – премия 60%. Данные находятся в следующем виде.
Требуется подставить премию на основании выполнения планов продаж. Для решения задачи в первой ячейке пропишем следующую формулу:
и скопируем вниз.
На рисунке ниже изображена схема, как работает интервальный просмотр функции ВПР.
Джеки Чан выполнил план на 124%. Значит ВПР в качестве критерия ищет во второй таблице ближайшее меньшее значение. Это 120%. Затем отсчитывает 2 столбца и возвращает премию 60%. Брюс Ли план не выполнил, поэтому его ближайший меньший критерий – 0%.
Предлагаю посмотреть видеоурок о работе ВПР из курса «Основные функции Excel».
Функция ВПР в Экселе – пошаговая инструкция
Функция ВПР в Экселе – это то, что позволит вам экономить десятки часов рабочего времени. При помощи функции ВПР вы ускоритесь буквально в разы и будете вспоминать с ужасом о сравнении таблиц вручную.
Так было со мной. На моей первой официальной работе, мне приходилось раз в несколько дней сравнивать два списка. Списки были относительно небольшие, около 100 строк каждый. Но сравнивал я их при помощи ручки и линейки. Это было мучение, более того это было неэффективное мучение, которое каждый раз, занимало у меня (по меньшей мере) пол рабочего дня.
Продолжался этот мазохизм ровно до тех пор, пока однажды коммерческий директор не увидел, как я безжалостно орудую линейкой над двумя листиками.
Он очень удивленно поинтересовался чем это я занимаюсь. Потом, весьма выдержанно сел за мой компьютер, открыл эти два файла и за несколько минут сравнил их при помощи функции ВПР в Экселе.
Сказать, что я был удивлён – это значит ничего не сказать. Я лицезрел настоящее чудо.
Это была потрясающая демонстрации силы автоматизации.
Функция ВПР в Экселе одинаково нужна и маркетологом, и логистам, и закупщикам – всем тем, кто работает с таблицами данных, это просто Must Have.
Функция ВПР в Экселе – быстрый перенос данных
Самое простое применение функция ВПР это быстрый перенос данных из одной таблицы в другую.
Например, у вас есть большой прайс на 500 позиций и запрос от покупателя, скажем на 50 позиций (в реальности и прайс и запрос могут быть гораздо больше, но принцип от этого не меняется).
Вам нужно быстро найти цены на эти 50 позиций. Разумеется, можно отдельно искать каждую позицию в большом прайсе и потратить на это 30 – 60 минут, а можно сделать это менее чем за минуту при помощи функции ВПР.
Итак, у нас в прайсе 500 позиций. Позиции обозначаются следующим образом, буквами обозначается вид позиции, а цифрами модификация.
Например, «Стул_1» и «Стул_21» это два совершенно разных стула.
Цены в прайсе указаны для примера и вряд ли имеют отношение к реальным ценам.
В ООО «ЫкэА» пришел запрос от «Петровича».
Петрович человек простой, любит всё делать быстро, но не очень чётко. Поэтому его запросы отличаются особым сумбуром в позициях.
Однако это нас не страшит, во-первых, у нас есть ВПР, во-вторых мы и не такое видали.
Вот собственно и сам запрос:
Петрович требует, чтобы мы очень быстро проставили цены в его запросе. Ждать он намерен максимум 5 минут. Ведь другие поставщики уже завалили его предложениями.
Нам не хочется терять такого клиента и мы практически мгновенно открываем прайс:
Получается у нас должно быть открыто два файла (две книги в Эксель). Запрос от Петровича и Прайс.
Это как раз то что необходимо, осталось только перенести цены из прайса в запрос.
Для этого перейдем в таблицу запроса и в первой ячейке столбца «Цены» (D4) введем «=впр» и два раза кликнем на значок функции:
Сразу же после этого, в строке формулы нужно поставить курсор внутри надписи ВПР и нажать Fx, перед вами появится окно с аргументами функции ВПР:
В аргументах функции вы говорите Экселю что и где нужно искать:
Искомое значение — это значение (в данном случае наименование), цену которого вы хотите найти в прайсе. Соответственно кликайте на первую ячейку столбца «Наименование».
Далее, сразу переходите в «Прайс»:
Теперь в аргументах функции заполните следующие поля:
Таблица — выделяете столбцы, которые содержат искомые наименования и цены, таким образом, чтобы наименования были крайним левым столбцом.
Так работает функция ВПР — ищет искомые значения в крайнем левом столбце (для ВПР это столбец №1). Когда ВПР находит искомое значение он начинает смотреть правее, в тот столбец, который вы указали в «Номере столбца».
Там вы должны указать столбец, содержащий необходимые для переноса данные. В нашем случае это цены и в нашем случае это столбец под номером два, относительно той таблицы, которую вы указали в аргументах.
Интервальный просмотр — ставьте 0. Ноль обозначает точное соответствие.
После заполнения аргументов функции нажимайте «Ок» и если всё сделано верно, то в столбце «Цена» (файл «Запрос от Петровича»), появится цена.
Вам нужно протянуть цены на оставшиеся ячейки:
Коллеги, вот и всё, вы овладели функцией ВПР.
Очень важное замечание!
Обратите внимание на то, что сейчас мы работали в двух разных файлах (книгах).
Когда работа идёт в двух разных книгах, Эксель автоматически закрепляет таблицу в функции ВПР:
Делает это он при помощи значка $, который проставляет перед столбцами и строками таблицы.
Это позволяет не съезжать формуле когда вы протягиваете её вниз. Это очень актуально когда вы работаете в рамках одного листа или одной книги (в этом случае Эксель автоматически Не закрепляет ячейки).
Давайте посмотрим что получиться если протянуть формулу «без закрепления»:
Обратите внимание, что для первой ячейки всё в порядке и диапазон B3:C502 точно соответствует той таблице, которую мы выделили для поиска данных, однако (без закрепления) так будет не всегда, по мере «протягивания» формулы ВПР вниз, будет смещаться и таблица, пока однажды, мы не увидим вот такую странную надпись #Н/Д:
#Н/Д значит что функция ВПР не смогла найти цену Стул_13 в прайсе и это не мудрено, ведь диапазон таблицы в формуле ВПР уехал ниже этого значения:
Посему, если не хотите чтобы от вас уезжали, закрепляйте диапазон.
Очень важное замечание №2
Как вы заметили, формулы ссылаются на определённые ячейки, другими словами между формулами и исходными данными есть связь. Стоит изменить исходные данные и значения в формулах сразу изменятся.
Особенно остро это чувствуется в ВПР. Если вы вдруг забудетесь и в исходной табличке добавите лишний столбец в «неположенном месте», то в формула ВПР выдаст совсем неожиданные значения.
Поэтому, если вам не нужна связь между таблицами, рекомендую формулы превратить в данные.
Для этого нужно выделить столбец с формулами, нажать Ctrl+C и в левом верхнем углу выбрать «Вставить» — «Вставить значения».
Для тех кто не любит изучать картинки, я записал небольшое видео в котором показываю всё то, что мы проговорили выше (кроме вставки значений):
Видео — «Быстрый перенос данных с помощью функции ВПР в Экселе»
Перенос данных при помощи ВПР, можно использовать не только для того, чтобы быстро получать данные из одной таблицы в другой, но и для того, чтобы сравнивать две таблицы.
Это очень актуально для тех кто работает в закупках и отправляет заказы поставщику.
Обычно происходит следующая ситуация. Вы отправляете заказ поставщику, через некоторое время получаете ответ в виде счёта и сверяете заказ с счётом.
Всё ли есть в счёте, в нужном ли количестве, по правильным ли ценам и т.д.
Функция ВПР в Экселе – сравнение двух таблиц
Итак, у вас есть «Заказ поставщику» (1) и ответ поставщика в виде «Счёта на оплату» (2).
Для удобства восприятия я разместил их на одном листе:
Ваша задача сверить количество позиций и их цены.
Для начала проверим все ли позиции и по правильной ли цене указал в счёте поставщик.
Для этого нужно из Счёта перетянуть данные в Заказ при помощи функции ВПР.
Перед «перетяжкой», в таблицу «Заказ поставщику» нужно добавить два «сравнительных» столбца:
После добавления столбцов, нужно перетянуть соответствующие данные при помощи ВПР:
Обратите внимание, я закрепил диапазоны ячеек.
Теперь когда данные перенесены, нужно их сравнить, для это необходимо добавить еще два столбца (Разница 1 и Разница 2):
В столбце «Разница 1» нужно вычесть от исходного количества (D4) количество в счёте (E4).
В столбце «Разница 2» нужно вычесть от исходной цены (G4) цену в счёте (H4).
Таким образом мы сможем увидеть разницу и в количестве и в цене.
Если значение «0», то значит всё хорошо и данные одинаковые.
Если значение плюсовое (например «+3»), то это значит что в счёте не хватает 3 штук.
Если значение отрицательное, это значит, что нам пытаются «впихнуть» лишнее.
Если значение #Н/Д — это значит, что в счёте вообще нет такой позиции.
Тоже самое и с ценами, если значения в плюсе, значит нам сделали скидку, а если значения в минусе — значит нам пытаются продать по завышенной цене.
Таким образом нужно просканировать всю таблицу и выяснить о причинах расхождения у поставщика:
Друзья, вот так мы проверили насколько соответствует Заказ, полученному Счёту и казалось бы что это всё что необходимо для счастливой жизни. Однако это не совсем так.
Нужно еще проверить соответствие Счёта, отправленному заказу, на предмет лишних позиций.
Вдруг хитрый поставщик, среди сотни позиций решил нам скрытно что-то «допродать».
Для этого в «Счёт на оплату» нужно добавить столбец «Кол/во в заказе» и «отвепээрить» туда значения из столбца «Количество» Заказа поставщику.
И если в столбце «Количество в заказе» мы вдруг увидим значения #Н/Д это значит, что позиции с таким наименование не было в нашем заказе поставщику.
Это не всегда означает, что поставщик хочет нас «нагреть», чаще всего просто может быть ошибка в названии товара. Стоит например поставить лишний пробел, как Эксель не задумываясь выдаст #Н/Д. Для него это разные названия.
Теперь всё тоже самое продемонстрирую в небольшом видео.
Видео — «Сравнение двух таблиц с помощью функции ВПР в Экселе»
Эпилог
Коллеги, поздравляю, с этого момента ваша работа с данными значительно упростится и ускорится, ведь теперь вы «почтигуру» по применению функции ВПР в Экселе.
Полезность
Коллеги, если вы часто работаете в Эксель, то рекомендую прочитать еще парочку моих очень полезных статей по этой тематике, там будет (как всегда) только-то что необходимо в работе:
Как в excel перенести данные из одной таблицы в другую
Смотрите также — в сводном не может.4 столбец зеленые править формулы. -(((( D1 я поставил выделила серым цветом делать нельзя. должна для Вас, Спасибо. _Igor_61 Попробуйте. Сводные таблицы решений чем те что предполагается искать
на листе «январь»).Если мы оставим все в таблице точноеФункция «ПОИСКПОЗ» затираем исходные данныеГость суммыОй даже незнаю «xxxxxxx» на листе подразделения, которые нужногде ‘[книга1.xlsx]АУП’!$C$3:$AN$175 - давным-давно разработать наnovichok: swetik, откройте файл,
часто бывают полезными возможности, которые предоставляет в таблице;Получаем следующее: в ячейке как есть по совпадение записи изфункция «ИНДЕКС» в Excel новым массивом (или: ну а какВопрос: если строки как получше объяснить.. «август». Это для перенести в рабочие это таблица макроязыке 1С шаблон 
: включите макросы и ;))) функция ВПР илитаблица – массив данных, C2 образовалась формула, умолчанию и просто ячейки А7.помогут из большой 
сперва очищаем лист перенести подскажите ну добавлять (Строк может т.е. что в того, чтобы сводная листы книги 2.'[книга1.xlsx]АУП’!$B$3:$B$175 — диапазон этого отчета.Manyasha нажмите на листеУКД
ГПР. из которых подтягиваются которая отсылает нас нажмем ОК, то10 – это таблицы выбрать нужные от всего), в очень нуда, что быть огромное количество)в Книге 1, что таблица видела названия наименования затрат просто 
должностей
Если служба IT
, почему-то не выводит «Сюда» на кнопку: pabchek, спасибо) ОбязательноУКД данные. Примечательно, таблица
на ячейку F2 таблица вставится полностью, номер столбца из данные и записать других файлах можно
с операторами мучаюсь табл.1 все формулы в книге 2 полей. Иначе, она поставила коды, в'[книга1.xlsx]АУП’!$C$3:$AN$3 — диапазон
отсутствует, Ваши руководители все как надо «Перенести» найду применение!)): Здравствуйте! Прошу помощи
с данными должна листа ЯНВАРЬ. Протянем со всеми ее диапазона таблицы, из их в других выгружать под данными,
не могу 
полетят в табл.2? со временем будут просто не сможет рабочей таблице они
кодов затрат
должны заказать шаблон в таблице 2,Anatoliy Pychev, здравствуйте!swetik 
в разбивке общей находиться правее исходной формулу вниз, чтобы параметрами. которого нужно взять
ячейках. Это может ну или сlight26Naivnaya появляться новые листы, сформироваться. Вместо этого тоже есть. т.е.Мотя отчета у «фирмочки»,по дате отображаются
Тоже думал про: Добрый день! Очень таблицы на несколько таблицы, иначе функций знать количество макаронПопробуем поэкспериментировать. В СПЕЦИАЛЬНОЙ информацию, которую мы быть таблица с начала листа -
Перенос данных из одной таблицы Excel в другую
: Макросами. Ждите когда: Да, спасибо большое. кот. будут соответствовать знака можно поставить текстовый вариант не: 1. Покажите реальный которая «окучивает» Вашу не все строчки массив, но умею нужна помощь. Как других по определенным ВПР не будет
каждой марки на ВСТАВКЕ выберем другой ищем. любыми данными (числа, зависит от задачи. макросописцы прочтут Вашу
Специальная вставка
Я понимаю что месяцу. любые другие названия особо принципиален как файл-экспорт из 1С: организацию. из Таблицы 1. только выбрать по
перенести данные из признакам. Требуется перенести работать; начало февраля. пункт, например, ФОРМУЛЫ.Получилось так. слова, т.д.) –6. сохраняем файлы. тему в моем случаеТ.е. когда сделаешь полей. я понимаю без скрытых строк,В противном случае
Manyasha условию и потом одной таблицы в
ВСЕ значения изномер столбца таблицы, изАналогично можно перенести данные Получили уже неформатированную
Теперь устанавливаем формулу в перечень сотрудников фирмы,ГостьHugo
слишком много тонкостей. Копию Листа вМаксим ЗеленскийVasilij_83 без структур (без Вам следует заказать:
все разом выгрузить, другую. При том вкладки БД с которой будут подтягиваться на все месяцы таблицу, но с
ячейку С7 (№ перечень товара с: Подскажите, как из
: Прочитали уже. Ждёмдело просто в Книге2 и назовешь: используйте для других: Добрый день! каких-либо Ваших изменений).
макрос на форуме.novichok и в выгрузке что строки одной признаком «рф» в
значения; и получить наглядный работающими формулами. телефона бухгалтерии). Формула его параметрами, телефонный одной книги перенести чего-нибудь более конкретного. том, что данные его не Явраль,
книг в такомЯ Вам предложу2. Составьте списокRustem, увеличьте диапазоны в
Перенос данных на другой лист
таблицы могут не отдельную вкладку, такинтервальный просмотр – логический годовой отчет.Теперь вставим не формулы, такая. справочник,т.д. данные нужной строки, Желательно в файле. 2 книги создаются а Февраль, то
случае функцию СУММПРОИЗВ вот какой вариант. соответствий: Попробуйте такую формулу: формулах: полученные значения идут соответствовать строкам в же и с аргумент, определяющий точностьАналогично можно переносить данные
- а только ЗНАЧЕНИЯ
- =ИНДЕКС($A$2:$K$3;ПОИСКПОЗ($A$7;A2:A3;0);11)
- Есть способ, как
в другую книгу.light26 на 1 поставщика, формула все равно вместо СУММ, только Добавьте дополнительный расчет
названийdude200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИОШИБКА(ИНДЕКС(‘Таблица 1′!E$2:E$1000;НАИМЕНЬШИЙ(ЕСЛИ($H$2=’Таблица 1’!$F$2:$F$1000;СТРОКА($F$4:$F$1002)-3);СТРОКА(A1)));»») подряд, одно под другой таблице и другими признаками.
- или приблизительность значения из одного файла результатов их вычислений.Здесь изменили только перенести данные из с помощью «=ВПР(«Х»;. ;. ;. ): а их у
- будет написана = она будет уже
- со сводной таблицой.в столбе «Наименование: листы месяцев лучшеrusanovava другим. А в соответственно сделать этоЗаранее огромное спасибо (0 или 1).
в другой. ДаннаяЧтобы новая таблица со в конце формулы таблицы Excel в это реально? ЕслиHugo меня 100, и Книга1;Лист1;Е19, а надо обычная, не массивная
Сводная таблица сведет показателя» листа «Расчет» называть 1407, 1408: Добрый день! Я,
Перенос данных в другой файл
этой теме задача просто копированием нельзя. за помощь!) книга в нашем значениями получила внешний номер столбца. Получилось таблицу Word. Смотрите
да, то подскажите, ну не хочет поэтому и встает чтоб было =Книга1;Лист2;Е19 (в остальном все все дубликаты и сrusanovava конечно, очень извиняюсь, немного другая -
А искать покитинРассмотрим пример на практике. примере так и вид аналогичный образцу так. статью «Как перенести как указывать таблицу посетитель регистрироваться, а такой вопрос относительноМожет еще так будет то же самое) Вам останется только
названиями: Rustem, доброе утро! что возможно «засоряю» значения вперемешку, при фильтру и копировать: ВПР, ИНДЕКС, или Имеем таблицу, в называется EXCEL. Создадим выделим ее иПо такому принципу можно таблицу из Excel и столбец например без этого выложить
хоть какой то понятней: из ЯнваряNaivnaya получить элементы этойв строке 2 Спасибо за Вашу Ваш форум повторами, несовпадении должны оставаться
Перенос данных таблицы через функцию ВПР
не вариант если что то другое. которой прописываются партии еще одну и вставим ФОРМАТЫ с сделать больше ячеек в Word». Этот КНИГИ1 ЛИСТА1 файл можно только совместимости двух книг Книги1 данные должны: Здравствуйте, подскажи пожалуйста, сводной таблицы, что файла-экспорта.
помощь. Честно признаться, но очень нужна пустые строки. Если в таблице более Был бы файл
Синтаксис ВПР
заказанных товаров (она назовем ее ПРИМЕР. помощью специальной вставки. для найденной информации, способ удобен тем,TheArk через файлообменник, чего по каждому поставщику.
вставляться в Январь
- возможно ли технически очень легко делаетсяА также не
- с функцией суммпроиз Ваша помощь, а Вам не трудно, 150 позиций и ответ мог быть подсвечена зеленым). СправаПримечание. Создавать новые файлыТеперь попробуем выбрать пункт
- установить такие формулы что нужная информация: Добрый день, друзья.
- этот пльзователь, ИМХО, Иначе приходиться копировать Книги2 , из прописать формулу для
Как перемещать данные с помощью ВПР?
забудьте «накапать» руководству пока не работала времени на поиск поделитесь опытом - они могут различаться точнее прайс, где значатся Excel можно даже БЕЗ РАМКИ. Получили в бланке. появляется в специальных Подскажите как перенести делать не будет все данные из февраля в февраль того, чтоб данные
дальнейшем, Вам придется на своих IT, )). не понятен здесь подобного решения как в этом
друг от друга.УКД цены каждого товара в разных папках. полную таблицу, ноТеперь достаточно ввести ячейках и не значения из общейlight26
1 книги в и т.д. Т.е их одной Книги только лишь контролировать которые занимаются прикладной знак двойного минуса. уже нет. (если случае можно организовать Как можно это
: китин, упс. Я (подсвечен голубым). Нам Программа будет автоматически только без выделенных в одну ячейку нужно ее выискивать свободной таблицы, в
: другую, ну сами при копировании листов Excel -далее Книга
вновь появившиеся коды. частью программного обеспечения Если не трудно,
такая тема уже выгрузку? Заранее спасибо! осуществить? ж прикрепляла файл( нужно перенести данные
искать указанную книгу, границ.
название фирмы и в таблице по отдельные на другихHugo понимаете, очень, очень, формулы то не 1, автоматически переносилисьПример выкладываю. в Вашей организации объясните его назначение. есть, просто ткнитеЮрий МBema Попробую еще раз. по ценам из независимо от того,Полезный совет! Чтобы перенести вся нужная информация строке, т.д. закладках. К примеру, я так мыслю,
Перенос значений из одной общей таблицы в другую по признаку (Формулы/Formulas)
очень много раз меняются. в другую ТаблицуТак можете сделать — свой «хлеб»rusanovava носом!)). Задача в: swetik, в #5: swetik, добрый иpabchek правой таблицы в в какой папке
формат вместе с выберется из строки
Итак, у нас в общей сводной что ему нужно умноженных на 100Наверно я не смогу другой Книги -Далее
на одном листе они также должны: dude, спасибо за принципе проста: из
не Вам. А что: Сводная пойдет? При левую, чтобы подсчитать и на каком
размерами столбцов нужно
и появится в есть огромная таблица
таблицы много значений что-то вроде этого. поставщиков :-))) доходчиво объяснить :-( Книга 2
хоть сколько угодно отрабатывать! Вашу помощь. про таблицы, выгруженной изцитата правила говорят о
желании можно сделать стоимость каждой партии. диске компьютера она перед копированием выделить отдельных ячейках (в с множеством строк с признаком А, Только доработать нужноЯ вообще задумываюсь Или это нереальноСложность в том, сводных таблиц. Единственно,Viktorya R листы учту 1С необходимо заполнить- это полная
файле-примере?Понимаете, файл-пример и автообновляемый диапазон и
Вручную это делать находится. не диапазон исходной одном месте), не и столбцов. Нам мне бы хотелось немного. Или много над вопросом создания сделать. автоматически нужно переносить что в формуле: 1. реальный файл. Вашу формулу
рабочую таблицу. Пример
копия рабочий файл- это кнопочку для обновления)) долго, поэтому воспользуемся
Скопируем в книгу ПРИМЕР таблицы, а столбцы нужно двигать большую нужно выбрать конкретные увидеть все их
200?’200px’:»+(this.scrollHeight+5)+’px’);»>Sub Перенос()
для этого делаДа и формулу не строку,столбец, а «ПОЛУЧИТЬ.ДАННЫЕ. » необходимо будет не могу показать, честно вообще не во вкладке. Проблемасообщения. Вы вообще две большие разницы.
сделал диапазон, перезалил функцией Вертикального Просмотра. таблицу с помощью
Перенос данных из одной таблицы в другую
целиком (в данном таблицу, т.д. данные из нескольких на другой закладке.’ хоть какой то в моем случае отдельные ячейки. указать номер сводной так как информация поняла ))).если поможете моя заключается в в курсе, чтоHugoУКДВ ячейку D3 нужно все той же случаи это диапазонМожно настроить таблицу ячеек.Например, таблица такая, Знаю есть вариант,
’ Перенос Макрос програмки, но как, не растянешь, иТ.е. ячейка Е19 таблицы. Метод очень сами понимаете не в ней разобраться,
том. что во-первых: такое цитата?: Да и с: pabchek, большое спасибо!) подтянуть цену гречки СПЕЦИАЛЬНОЙ ВСТАВКИ. И A:F). так, что при в таблице может привязать все к
’ к кому и
придется каждую формулу Книги 1 должна хорош тем, что
для всеобщего обозрения. буду признательна и исходная таблица каждыйЗапомните — кнопка тремя можно ВПРЭто макросы, я из правой таблицы. опять удалим значенияАналогично можно поэкспериментировать с определенных условиях будет
быть больше столбцов значени Если=»a» то’ ск-ко это стоит в каждой ячейке автоматически вставать в
в нем можно даже если удалю смогу использовать в раз разная (т.е. цитирования использовать, если сделать правильно понимаю?)) С Пишем =ВПР и
из трех столбцов. каждым пунктом СПЕЦИАЛЬНОЙ выделяться сразу вся и строк. Шапку писать значение еслиRange(«A1»).Select я незнаю. А править. ячейку С11 Книги контролировать сводные цифры всю инфу. будет дальнейшей работе, поскольку разное кол-во столбцовне для ответа один допстолбец в ними практически не заполняем аргументы.
Проведем те же ВСТАВКИ, чтобы наглядно строка. Читайте об таблицы можно закрепить. не равно, то
Selection.Cut облегчить свою работуNaivnaya 2. и если что-то просто очень тяжелый
подобных отчетов у и строк) и! Вернитесь в своё источнике, правда или знакома, можно как-тоИскомым значением будет гречка действия, что и увидеть, как она этом в статье Читайте статью «Как писать нулю, затемSheets(«Да»).Select хочется :-): Наверно я что-тоячейка К19 Книги пойдет не так файл. меня масса. необходимые данные каждый сообщение и исправьте его нужно воткнуть
формулами обойтись? из ячейки B3. в предыдущем параграфе, работает. «Выделить всю строку закрепить строку в все это упорядочитьActiveSheet.PasteSerge_007
все таки неправильно 1 должна автоматически это всегда можно2. а чтов этом доке раз находятся в
это безобразие. левее, или использовать_Boroda_
Автоматический перенос данных из одной таблицы в другие (Формулы/Formulas)
Важно проставить именно но переходить уже
по условию в Excel и столбец» по признаку. Но
Columns(«A:A»).EntireColumn.AutoFit: Тогда зачем Вы думаю, то что вставать в ячейку
перепроверить. лист «код» этого
назвала так листы разных столбцах иvikttur «левый» ВПР, или: Ну вот, а номер ячейки, а будем не на
Перенос данных на другие
Excel».
здесь. хотелось бы безSheets(«Данные»).Select её усложняете такой я хочу в С12 Книги 2.vikttur не дает? для примера, а
строках, приходится менять
: Помощь скрыта до связку индекс+поискпоз.
я Вам макрос не слово «гречка», другой лист, а листы книги ExcelТаблица в Excel –В чистых ячейках листа, пустых строк. есть
Selection.Delete Shift:=xlUp безобразной организацией данных?! экселе не сделаешь,ячейка F19 Книги: , к Вашемуформула, предложенная Rustem в реале выгруженный
диапазон столбцов и устранения замечанияНо это будет полноценный пишу. Ну
чтобы потом можно на другую книгу. позволяет связывать несколько это сложный массив не в таблице, ли в экселе
Range(«B1»).SelectВедь все Ваши это наверно програмку
1 должна автоматически сведению! работает отлично без из 1с-ки документ строк, что неудобно novichok
Перенос данных из одной таблицы в другую при разных условиях
НЕ ПЕРЕНОС! ладно, тогда не было протянуть формулуПолучили новую формулу, которая таблиц. Это удобно с множеством параметров. устанавливаем формулы для такая функция?Selection.ClearContents задачи можно легко надо чтоб она вставать в ячейкуСвои сообщения можно проблем, единственное приходится разбивается на несколько и времязатратно)). Во-вторых,: Добрый день!Anatoliy Pychev буду доделывать вниз и автоматически показывает, что ячейка тем, что при Он может состоять поиска данных. Например,Serge_007Range(«A1»).Select решить, если привести автоматически все значения D11 Книги 2. дополнять, не нужна менять в каждом десятков листов по та формула, которуюНикак не могу: Как идея циклЕсли это не получить остальные значения. ссылается на книгу замене какого-то значения из значений, текстовых название фирмы и
: Сбор информации сSelection.Cut их (данные) в подставляла из Книги2ячейка I19 Книги автоматная очередь из месяце диапазон строк подразделениям с их завела, работает, но понять , возможно в цикле. очень часто нужноТаблица – выделяем прайс
EXCEL. Причем видим, на одном листе, ячеек, формул и
номера телефонов главного нескольких листов. ИспользованиеSheets(«Нет»).Select порядок. в книгу 2 1 должна автоматически
Ваших сообщений.
и столбцов. названиями, (поэтому месяц цепляет только первое ли воспроизвести эту
В наружном цикле делать и листов без шапки. Т.е. что ячейка F2 меняются значения на быть отформатирован разными
бухгалтера и бухгалтерии. названий листов вActiveSheet.Paste
ГостьHugo вставать в ячейкуКогда и такСделала на одном тут не играет
требуемое значение, а задачу. бежишь по строкам не очень много, только сами наименования выглядит как $F$2,
всех остальных. При способами (ячейки могут Мы сделали такую
формулеColumns(«A:A»).EntireColumn.AutoFit
: у меня другой: Т.к. ячейки у H11 Книги 2.
понятно, к кому листе «участок N», роль). а потом их может бытьЕсть таблица заявок, таблицы получателя, а то встаньте в товаров и их т.е. она зафиксирована. создании годовых отчетов
иметь определенное выравнивание, таблицу.TheArkSheets(«Данные»).Select вопрос: Вас разбросаны вПоскажите, возможно ли такое. обращаетесь и о скопировала в остальные. в другой файл несколько. (например, п. с их подробным во внутреннем по
ячейку А1, перейдите цены. Этот массив И если мы это незаменимая вещь. цвет, направление текста,В ячейке А7 будем: Спасибо, но мнеSelection.Delete Shift:=xlUpнеобходимо перенести данные из какой-то мере хаотично, В ручную трудно чем разговор, цитировать Теперь надо еще (рабочий) на листы 5.1.1. в таблице). описанием в строке строкам таблицы источника. на вкладку Данные мы зафиксируем клавишей хотим протянуть формулуРассмотрим, как это работает. специальные пометки и писать название фирмы, как раз требуетсяRange(«B1»).Select
36 таблиц в то прописывать адреса все это ежемесячно не нужно. на каждом листе с тем же Плиз, помогите доработать (в примере). Внутри второго цикла и нажмите кнопку F4, чтобы он на остальные марки Для начала переименуем т.п.). по которой нам обратное из общейSelection.ClearContents 1ну, все они придётся индивидуально и переводить. Может этоОбъединил/удалил, далее сами поменять ссылку на названием необходимо перенести формулу начинающему. будуЗаявки попадают в проверка на совпадение Фильтр. В табличке
не изменялся при макарон, сначала нужно листы Excel в
При копировании таблицы иногда нужно найти номера
таблицы рассортировать поEnd Sub
идентичные, единственно каждая в макросе.
нужно сделать сводную следите. название нужного участка. информацию. формулу вашу благодарна! )) хаотичном по датам,
данных по которым появятся стрелочки. В протягивании формулы. удалить значки доллара, месяцы. Затем с требуется перенести не телефонов главного бухгалтера закладкам, причем столбцовHugo
из 36 таблицДругое дело что таблицу. Я вообщеViktorya R тут уже видимо разобрала. но исходяrusanovavaКак возможно сделать сравниваются строки, ну
столбце Признак нажимаетеНомер столбца – в чтобы снять фиксацию. помощью уже знакомой все ее элементы, и бухгалтерии. В с данными будет: Ну если примера имеет записи только в макросе можно
новичок и в: vikttur, к сведению нужен макрос. короче,
из того, что: прошу помочь неравнодушным. так, чтобы после и перенос данных на эту стрелочку, нашем случае это
Теперь вы знаете, как нам СПЕЦИАЛЬНОЙ ВСТАВКИ а только некоторые. ячейках В7 и несколько больше чем данных не будет в определенных строках(ячейках) задать цикл по понятиях путаюсь. приняла! ) учту.
заморочилась конкретно. )))) подразделений куча, нужно ))) может описала
ввода информации вПравда метод, так выбираете «РФ» (все цифра 2, потому грамотно переносить данные перенесем таблицу на Рассмотрим, как это С7 будут прописаны в примере
— то инеобходимо по всей видимости листам книг и
Спасибо.Vasilij_83, спасибо заМотя будет для каждого не совсем понятно. Таблицу 1, автоматически сразу без подготовки, остальное скроется), копируете что необходимые нам из таблиц в февраль и удалим можно сделать. эти номера (вSerge_007
макрос не на макросом перенести значения копирование значений заранее
Naivnaya Вашу помощь. сейчас: Реальный — в присвоить свое имя, расчетная формула на распределяла заявки по жутко медленный, но оставшееся и вставляете данные (цена) стоят рамках одного листа, значения из трехОчень удобно выполнять перенос этих ячейках мы: Странно, но
чем писать. из каждой из
прописанных в коде: А вот Книга посмотрю ))). плане структуры. и тогда все листе «расчет». как датам в отдельные если надо один в лист РФ во втором столбце с одного листа столбов: данных таблицы с напишем формулы).
200?’200px’:»+(this.scrollHeight+5)+’px’);»>=(СУММ(ПСТР(ЯЧЕЙКА(«имяфайла»);ПОИСК(«]»;ЯЧЕЙКА(«имяфайла»;A1))+1;1)=’1′!A$1:A$7)*’1′!C$1:C$7)
Алгоритм может быть 36 таблиц в ячеек из одного 2Vasilij_83Цитаталист «код» этого будет работать (наверное просуммировать значения из таблицы? раз то можноС остальными листами выделенной таблицы (прайса). на другой, аНа начало месяца. помощью специальной вставки.
Устанавливаем формулу вне работает. такой — открываем
1ну идентичную, где листа в другой.Hugo: Надеюсь, что разберетесь.
не дает?Поскольку в ). Сейчас буду различных столбцов исходнойВозможно ли это? и подождать.
аналогичноИнтервальный просмотр – ставим
также с одногоПриход. Она позволяет выбрать
ячейке В7 (№Видимо пора на кодом все 3
все отобразится разом.Но в целом: Здравствуйте. Если Вы заметили, файле-экспорте строку с пробовать ))))) таблицы (лист «август»),СпасибО!И еще, значенияИли чисто формулами. 0, т.к. нам файла в другой.Расход. только те параметры, телефона гл. бухгалтера). покой файла, идём цикломесть ли соответствующая разработка? весь механизм практическойПросто вставить значения что единственный вопрос кодами структуры (1rusanovava удовлетворяющих одному условиюManyasha по которым идет Это формула массива нужны точные значения,
ВПР в Excel –Столбец «На конец месяца» которые нам необходимы Формулу можно простоStaniiislav по сводному снизуSerge_007 реализации задачи мне «приравниванием» не годится? остается только в строка) Вы заполняете: Доброе утро. Таковая (например значения, которые: сравнение должны быть
для ячейки А2 а не приблизительные. очень полезная функция, у нас задан при копировании. Рассмотрим написать словами, а: в смысле не вверх и по: Ну и зачем
автоматический перенос данных из Книги1 в другую Книг2 (перенос данных из одной таблицы Книги1 в другую таблицу Книг)
не совсем ясенТ.е. например для составлении сводной таблицы. в рукопашную, то служба имеется, но относятся к п.novichok уникальны в таблице любого листа. ПослеВидим, что из правой позволяющая подтягивать данные
формулой, поэтому при пример. можно вызвать эту работает? все вроде
результатам проверки ответа тогда его в — много тонкостей. »ячейка Е19 Книги Вернее умение работать
Ваш лист «код» загружать их «такими 5.1.1. расчетной таблицы)?, здравствуйте. А зачем
источника, т.к. всегда ввода формулы нужно таблицы в левую из одной таблицы
удалении значений изИмеем таблицу с показателями функцию на закладке работает
переносим текущую строку ЭТОЙ теме задавать?!Может быть лучше 1 должна автоматически с источником данных не дает никакой мелочами» (как быМотя
так много таблиц,
будут переноситься только нажать Контрл Шифт подтянулась цена гречки.
в другую по предыдущих столбцов, он
по наличию макарон «Формулы» в разделе
TheArk
в первую пустуюЧитайте правила (особенно сделать так - вставать в ячейку сводной таблицы. Далее
информации для макроса. мне они сказали: И это нужно представляете сколько их
первое или последнее Ентер вместо обычного
Протягиваем формулу вниз и заданным критериям. Это
автоматически обнуляется. определенных марок на «Ссылки и массивы».: Если Вас не ячейку первого столбца п.4.) и создавайте наладили этими формулами С11 Книги 2″ останется вставить формулуViktorya R) не хочу, делать каждый месяц?! будет через пару найденное значение
Ентер и растянуть визуально проверяем некоторые достаточно «умная» команда,Перенесем данные по остатку складе магазина. Наглядно В функции «ИНДЕКС» затруднит, можно эту соотв. файла. свою.
один лист. При делаем так: «ПОЛУЧИТЬ.ДАННЫЕ. » в ячейку: могу отправить в проще самой применитьДа и еще месяцев (по однойПро оптимизацию этого формулу вниз и
товары, чтобы понять, потому что принцип макарон каждой марки видно, сколько килограмм выбираем функцию «массив; формулу в мойВ конце результатыГость появлении нового месяцав C11 книги2
E12, связать ее личку формулы для облегчения к нужному часу,
таблице на день)? метода: 1- при вбок на сколько что все сделали ее работы складывается с января на
было в начале номер_строки;номер_столбца». файл вставить, так сохраняем.: Добрый времени суток, скопировали этот лист, вводим » имяфайла»;A1);ПОИСК(«]»;ЯЧЕЙКА(«имяфайла»;A1))+1;99);СТРОКА(БД!A$2:A$99));СТРОКА(A1)));»»)Вот так, с помощью и перенос данныхНа листе ФЕВРАЛЬ ставим
конец месяца. Два диапазон нашей таблицы,: Добрый времени суток! можно сделать быстро1.Общая в ней лист ссылкой на аналогично. Сохраняем файл. дальнейшем, при изменении новый вид начислений, и форматами куча.Умереть — не встать! листе Таблица 2 то можно будетУКД элементарных действий можно из нее. курсор в ячейку, важных столбца выделены в котором нужно
Помогите в решении на массивах: находяться все данные новый лист(месяц).P.S. Пример на структуры таблицы 1С который нужно будет Предыдущий работник делалИнтересно, а динамические (на примере 1-й
не начинать внутренний: _Boroda_, pabchek, огромное подставлять значения изВесь процесс просмотра и
показывающую количество макарон разными цветами. Остаток искать данные (без задачи! Требуется, чтобы1. открываем все
2.Положительный ответNaivnaya планете
останется слегка подкорректировать либо добавить как вообще все это показатели прироста ретивые таблице, выделенной серым цикл сначала; 3- спасибо всем неравнодушным
одной таблицы в выборки данных происходит марки А на на конец месяца шапки таблицы). из Таблицы 1,
3.Отрицательный ответ: ТОГДА МОЖЕТ ВОТNaivnaya только сводную таблицу.
новый в рабочую ВРУЧНУЮ. начальники-экономисты с Вас
цветом): меняете дату, если копировать значения за отклик! другую. Важно помнить,
за доли секунды,
начало месяца. Можно рассчитан по элементарной
$A$7 – это
данные автоматически переносились
2. из сводногоМне надо формула ТАКОЙ ВОПРОСИК. : Спасибо большое, но Итоговая сводная таблица таблицу либо прибавитьтак что, как тоже требуют?! все остальное пересчитывается в массивы передpabchek
что функция Вертикального поэтому результат мы посмотреть на рисунок
формуле. адрес ячейки, куда в Таблицу 2, берём все данные при которой приМожно ли на этом
в одной папке останется в обычном к уже имеющемуся. верно вы подметили:ЦитатаУмереть
В Вашей организации200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИОШИБКА(ИНДЕКС(‘Таблица 1′!E$2:E$20;НАИМЕНЬШИЙ(ЕСЛИ($H$2=’Таблица 1’!$F$2:$F$20;СТРОКА($F$4:$F$22)-3);СТРОКА(A1)));»») обработкой, то метод: Нет, это не
Просмотра работает только получаем моментально. выше – этоПопробуем воспользоваться командой СПЕЦИАЛЬНАЯ будем писать название но без промежуточных в массив положительном ответе все же листе в неудобно. Да к
виде. Или, если поэтому я сделала — не встать!=СУММ(tabl*(dolz=E$4)*(kod=$A11)) есть служба IT,Все формулы массивные, получается просто улетный. макросы. На листе если таблица, из
ВПР расшифровывается как вертикальный
будет ячейка D3.
ВСТАВКА и скопировать
фирмы, по которой
пустых строк между
3. создаём 3
строка переноситься во
конце или внизу
тому же мне
даже будут изменения
этот вспом. лист
эта формула работает
или Вашу 1С
вводятся сочетанием ctrl+shift+enter.
Таблицы с более
«БД» выделяете свой
которой подтягиваются данные,
просмотр. То есть
Ставим в этой ячейке
все данные.
ищем информацию.
данными в Таблице
массива такого же
вторую книгу, при
таблицы сделать сводную нужно делать это в итоговой таблице, с кодами, который только в пределах «окучивает» приходящий по
Если столбцы расположить чем 10000 записей диапазон находится справа. В команда переносит данные знак РАВНО.Сначала мы выделяем имеющуюсяA2:A3 – это 1. размера отрицательном в 3,
таблицу из данных каждый месяц, а
то зная эту периодически обновляю одной книги. а вызову специалист из в одинаковом порядке,
каждая обрабатываются за=A1:D12
противном случае, нужно из одного столбцаПереходим на лист ЯНВАРЬ
таблицу, правой кнопкой диапазон столбца, в_Boroda_
4. перебираем массив при чем в1 столбец красные если лист копировать методику, не сложноМотя
если ссылаюсь на «фирмочки»? то формулу можно несколько секундЗатем на вкладке переместить ее или в другой. Для и кликаем на вызываем меню и котором написано название: — Прочитайте Правила
из сводного и
1 книги что суммы в Книге 1, внести изменения.: В макросе это другую без присвоенияЕсли есть IT, растягивать и поswetik «Вставка» нажимаете левую воспользоваться командой ИНДЕКС
Перенести значения по признаку из одной таблицы в другую.
работы со строками ячейку, показывающую количество жмем КОПИРОВАТЬ. этой фирмы (у форума копируем данные по б ничего не2 столбец желтые и в КнигеЕще надо помнить, можно легко учесть. имен диапазонам, то за что же столбцам.: Я даже не крайнюю кнопку «Сводная и ПОИСКПОЗ. Овладев существует горизонтальный просмотр макарон марки АВ свободной ячейке снова нас – это- Создайте свою трём массивам (для оставалось? кто может суммы
2, то формулы что для составленияViktorya R получаю вот это: они получают зРяплату?!
novichok знаю и не таблица», затем «ОК». этими двумя функциями – ГПР. на конец месяца вызываем меню правой первый столбец А). тему согласно п.5q
каждого свой счётчик что подсказать
3 столбец синие не меняются и
сводной таблицы в: Книга 1 -
Формируется новый лист вы сможете намногоАргументы функции следующие: (в нашем случае кнопкой и нажимаем0 (ноль) – Правил форума
строк)Hugo суммы каждый месяц придется диапазоне A1 : исходный вариант 1с. работает, видимо так важен, служба ITManyasha делать. со сводной таблицей.
больше реализовать сложныхискомое значение – то, это ячейка F2
СПЕЦИАЛЬНАЯ ВСТАВКА. это значит, искатьЭта тема закрыта
5. выгружаем массивы
Использование функции ВПР (VLOOKUP) для подстановки значений
Кому лень или нет времени читать — смотрим видео. Подробности и нюансы — в тексте ниже.
Постановка задачи
Итак, имеем две таблицы — таблицу заказов и прайс-лист:
Задача — подставить цены из прайс-листа в таблицу заказов автоматически, ориентируясь на название товара с тем, чтобы потом можно было посчитать стоимость.
Решение
В наборе функций Excel, в категории Ссылки и массивы (Lookup and reference) имеется функция ВПР (VLOOKUP) . Эта функция ищет заданное значение (в нашем примере это слово «Яблоки») в крайнем левом столбце указанной таблицы (прайс-листа) двигаясь сверху-вниз и, найдя его, выдает содержимое соседней ячейки (23 руб.) Схематически работу этой функции можно представить так:
Для простоты дальнейшего использования функции сразу сделайте одну вещь — дайте диапазону ячеек прайс-листа собственное имя. Для этого выделите все ячейки прайс-листа кроме «шапки» (G3:H19), выберите в меню Вставка — Имя — Присвоить (Insert — Name — Define) или нажмите CTRL+F3 и введите любое имя (без пробелов), например Прайс. Теперь в дальнейшем можно будет использовать это имя для ссылки на прайс-лист.
Теперь используем функцию ВПР. Выделите ячейку, куда она будет введена (D3) и откройте вкладку Формулы — Вставка функции (Formulas — Insert Function) . В категории Ссылки и массивы (Lookup and Reference) найдите функцию ВПР (VLOOKUP) и нажмите ОК. Появится окно ввода аргументов для функции:
Заполняем их по очереди:
- Искомое значение (Lookup Value) — то наименование товара, которое функция должна найти в крайнем левом столбце прайс-листа. В нашем случае — слово «Яблоки» из ячейки B3.
- Таблица (Table Array) — таблица из которой берутся искомые значения, то есть наш прайс-лист. Для ссылки используем собственное имя «Прайс» данное ранее. Если вы не давали имя, то можно просто выделить таблицу, но не забудьте нажать потом клавишу F4 , чтобы закрепить ссылку знаками доллара , т.к. в противном случае она будет соскальзывать при копировании нашей формулы вниз, на остальные ячейки столбца D3:D30.
- Номер_столбца (Column index number) — порядковый номер (не буква!) столбца в прайс-листе из которого будем брать значения цены. Первый столбец прайс-листа с названиями имеет номер 1, следовательно нам нужна цена из столбца с номером 2.
- Интервальный_просмотр (Range Lookup) — в это поле можно вводить только два значения: ЛОЖЬ или ИСТИНА:
- Если введено значение 0 или ЛОЖЬ (FALSE) , то фактически это означает, что разрешен поиск только точного соответствия, т.е. если функция не найдет в прайс-листе укзанного в таблице заказов нестандартного товара (если будет введено, например, «Кокос»), то она выдаст ошибку #Н/Д (нет данных).
- Если введено значение 1 или ИСТИНА (TRUE) , то это значит, что Вы разрешаете поиск не точного, а приблизительного соответствия, т.е. в случае с «кокосом» функция попытается найти товар с наименованием, которое максимально похоже на «кокос» и выдаст цену для этого наименования. В большинстве случаев такая приблизительная подстановка может сыграть с пользователем злую шутку, подставив значение не того товара, который был на самом деле! Так что для большинства реальных бизнес-задач приблизительный поиск лучше не разрешать. Исключением является случай, когда мы ищем числа, а не текст — например, при расчете Ступенчатых скидок.
- Включен точный поиск (аргумент Интервальный просмотр=0) и искомого наименования нет в Таблице.
- Включен приблизительный поиск (Интервальный просмотр=1), но Таблица, в которой происходит поиск не отсортирована по возрастанию наименований.
- Формат ячейки, откуда берется искомое значение наименования (например B3 в нашем случае) и формат ячеек первого столбца (F3:F19) таблицы отличаются (например, числовой и текстовый). Этот случай особенно характерен при использовании вместо текстовых наименований числовых кодов (номера счетов, идентификаторы, даты и т.п.) В этом случае можно использовать функции Ч и ТЕКСТ для преобразования форматов данных. Выглядеть это будет примерно так:
=ВПР(ТЕКСТ(B3);прайс;0)
Подробнее об этом можно почитать тут. - Функция не может найти нужного значения, потому что в коде присутствуют пробелы или невидимые непечатаемые знаки (перенос строки и т.п.). В этом случае можно использовать текстовые функции СЖПРОБЕЛЫ (TRIM) и ПЕЧСИМВ (CLEAN) для их удаления:
=ВПР(СЖПРОБЕЛЫ(ПЕЧСИМВ(B3));прайс;0)
=VLOOKUP(TRIM(CLEAN(B3));прайс;0)
Все! Осталось нажать ОК и скопировать введенную функцию на весь столбец.
Ошибки #Н/Д и их подавление
Функция ВПР (VLOOKUP) возвращает ошибку #Н/Д (#N/A) если:
Для подавления сообщения об ошибке #Н/Д (#N/A) в тех случаях, когда функция не может найти точно соответствия, можно воспользоваться функцией ЕСЛИОШИБКА (IFERROR) . Так, например, вот такая конструкция перехватывает любые ошибки создаваемые ВПР и заменяет их нулями:
Если нужно извлечь не одно значение а сразу весь набор (если их встречается несколько разных), то придется шаманить с формулой массива. или использовать новую функцию ПРОСМОТРX (XLOOKUP) из Office 365.
Ссылки по теме
-
.
- Функции VLOOKUP2 и VLOOKUP3 из надстройки PLEX
Не за что — стараемся по мере сил и способностей Все мои видеоуроки можно найти (и подписаться на них) на канале planetaexcel на YouTube.
Добрый день! Подскажите, пожта, каким способом можно использовать функцию ВПР если происходит поиск значения, которое является частью текста в ячейке? Т.е. если ячейка состоит из одного значения — работает, а если значение «зашито» в ячейку — ВПР ее не распознает.
Спасибо Вам, Николай, как раз то что надо!
Сцепить два ваших критерия в отдельном столбце в один и делать ВПР по нему.
Либо написать свой вариант ВПР на Visual Basic
Подскажите, почему формула не работает? Составил Таблицу из 2-х столбцов, в первом Наименование, во втором параметр. Рядом 2 ячейки — выбор Наименования (через выпадающий список), во второй должно ставиться значение через формулу ВПР. Но появляются ошибки: При выборе Строчки «Ручка Опера» значение подставляется неверно, При выборе строчки «Ручка Бридж» значения не находит.
| Ручка Н | 54 |
| Ручка С | 36 |
| Ручка Сэко | 16 |
| Ручка Опера | 37 |
| Ручка Тауэр | 62 |
| Ручка Бридж | 2 |
P.S.: и как сюда пример готовый добавить?
Спасибо, экономил очень много времении )))))
Помогите, ВПР формулу прописал. Перепроверил. И мне в нужном месте вместо числового значения выдало ошибку #ССЫЛКА! Что делать и как ее обойти. И вообще почему она появилась? Для понимания проблемы. Всё сделал как в указанном примере. Но результат я уже написал.
УРРРЯЯЯЯЯЯ, сам разобрался. Проблема решилась когда я повторно пересмотрел урок и понял, что столбец указал не правильный для цены. Нужно указывать номер стоблца именно в ДИАПАЗОНЕ В КОТОРОМ БУДЕТ ВЫБИРАТЬСЯ ЦЕНА, а не столбец по порядку начиная с первого.
Функцией ВПР пользуюсь успешно, но вот проблема, если в ячейках есть знак * то функция подставляет только то что было до этого знака и находит то что было до этого знака. Как быть?
| Б 826Д*60*100 | 1 067,00 | Нет этого товара в Ч.Р. | 10 937 | Б 826Д*60*100 |
| Б 826Д*60*70 | 914,00 | 914,00 | 10 939 | Б 826Д*60*70 |
| Б 826Д*60*80 | 965,00 | 965,00 | 10 940 | Б 826Д*60*80 |
| Б 826Д*60*90 | 1 016,00 | 1 016,00 | 10 941 | Б 826Д*60*90 |
| Б 826Д*61*90,5 | 1 042,00 | 1 042,00 | 10 942 | Б 826Д*61*90,5 |
| Б 826Д*62*96 | 1 067,00 | 1 067,00 | 10 943 | Б 826Д*62*96 |
| Б 826Д*63*73 | 965,00 | 965,00 | 10 944 | Б 826Д*63*73 |
| 914,00 | Б 826Д** |
Николай, как быть в случае если цены в течении периода меняются. Предполагаем что в ТАБЛИЦЕ ЗАКАЗОВ есть столбец ДАТА. И в ПРАЙС-ЛИСТЕ есть столбцы ЦЕНА НА ЯНВАРЬ, ЦЕНА НА ФЕВРАЛ и т.д.
Очень полезная штука!
Николай, у меня вопрос.
Есть три таблицы:
1. Список товаров с артикулами и другими данными
2. Таблица соответствия артикула уникальному номеру (2 столбца).
3. Таблица соответствия уникального номера пути к фотографиям. В этой таблице два столбца, в одном уникальный номер, в другом путь к фото товара. Фотографий может быть как одна так и несколько. То есть айди может быть в пяти йчейках напротив которых ячейка с указанием пути.
Вопрос: Как в первую таблицу вытащить пути к нескольким фотографиям одного товара в одну ячейку из третей через вторую? При этом пути к фото разместить через символ | например.
Заранее огромное спасибо!
Николай, здравствуйте!
У меня вопрос. Может ли ВПР найти артикул с текстом?
Например, «525 393-090» ищем в «Кроссовки 525 393-090»
Заранее огромное спасибо!
ВПР не может, но можно по-другому:
Добрый день, Николай!
Пожалуйста, помогите понять работу предложенной вами здесь формулы с функциями ПРОСМОТР и ПОИСК.
Она создана как-то не в соответствии с описанием этих функций, но. как ни странно, работает!
Непонятки вот в чём.
По описанию функции ПРОСМОТР, аргумент №1 — строка с искомым значением, аргумент №2 — область-вектор (т.е. одномерная), в которой происходит поиск ячейки со значением, заданным аргументом №1, аргумент №3 — область-вектор, из которой выбирается значение-результат. То есть, если в области “аргумент №2” находится ячейка, значение которой равно “аргумент №1” — функция вычисляет порядковый номер этой ячейки в этой области и возвращает значение ячейки уже из области “аргумент №3” с тем же порядковым номером.
Вопрос 1. В этом свете, непонятно в вашем примере, какой смысл имеет на месте аргумента №1 значение 2^15 ?
Вопрос 2. В качестве аргумента №2 в вашей формуле стоит функция ПОИСК, результатом работы которой является вроде как «позиция первого вхождения знака или текстовой строки». А по описанию функции ПРОСМОТР, аргументом №2 должен быть дипазон ячеек (область). Как так?
Вопрос 3. По описанию фукции ПОИСК её аргумент №1 — строковый (текстовой), а в вашей формуле на этом месте — дипазон ячеек (область). Как так?
Вопрос 4. Ещё по функции ПОИСК. Когда я скопировал часть вашей формулы (аргумент №2 из ПРОСМОТР) в отдельную ячейку, чтобы посмотреть промежуточный результат, то есть, =ПОИСК(F$2:F$4;A2) — к моему недоумению результатом оказалось #ЗНАЧ! ! То есть, фукция отдельно — вроде неправильная, но внутри другой функции как-то работает?
Повторю, вся эта конструкция (формула) тем не менее, работает! Шаманство какое-то.
После долгих размышлений и экспериментов я догадался (в экселовской СПРАВКЕ об этом ни пол-слова! ), что конструкция ПОИСК(F$2:F$4;A2) возвращает виртуальный массив (!) из 4-х элементов (для удобства далее я его буду условно называть “ Просматриваемый_Вектор ”), с значениями, являющимися результатом поиска в значении ячейки A2 значений из соответствующих ячеек массива F$2:F$4 (далее буду условно называть “ Массив_Примет ”),
то есть,
в результирующий массив в элемент Просматриваемый_Вектор (1) помещается результат обычной функции ПОИСК(F$2;A2) (как бы, ПОИСК( Массив_Примет (1);A2)), в элемент Просматриваемый_Вектор (2) — результат ПОИСК(F$3;A2) (как бы, Массив_Примет (2);A2), и т. д.
То есть, в элементы Просматриваемый_Вектор (i) помещается: либо число (найденная в A2 позиция искомой подстроки Массив_Примет (i)), либо значение #ЗНАЧ! (если значение-подстрока Массив_Примет (i) в A2не найдена).
Что-ж, с моими вопросами 2, 3, 4 из предыдущего сообщения вроде разобрались. (За исключением того, что такое использование функции ПОИСК в экселовской Справке — отсутствует, т.е. — это недокументированная возможность?)
Остался вопрос 1 , по функции ПРОСМОТР:
Почему для выявления среди элементов массива Просматриваемый_Вектор элемента, содержащего число, их надо сравнивать именно c значением 2^15 ?
И ещё: В экселовской справке по функции ПРОСМОТР отмечено: «Значения в аргументе `просматриваемый_вектор` должны быть расположены в порядке возрастания. в противном случае функция ПРОСМОТР может вернуть неверный результат.».
В то же время, в вашем примере, в возвращаемом функцией ПОИСК виртуальном массиве Просматриваемый_Вектор положение элемента, содержащего число (т.е. соответствующего искомой подстроке) — относительно других элементов произвольное. Как это может сказаться на правильности работы ПРОСМОТР?
Я правильно понимаю, что Вы исключили в функции VLOOKUP 2 такую вещь как » приблизительный поиск (Интервальный просмотр=1) » ?
Т.е. она ищет только точно совпадение. Верно ?
У меня потребность именно в приблизительном поиске и лучше (крайне желательно) без сортировки таблицы.
Николай, добрый день, а что это может быть за ошибка если ВПР функция совсем не работает, то есть после введения всех 4 аргументов и нажатия ОК в нужной ячейке отражается
| =ВПР(A2;$J$2:$L$97864;3;0) |
а собственно значения (хотя бы #Н/Д ) не выдается. Много раз следовала Вашему уроку, но не получается..
Помогите пожалуйста какая то ошибка в формуле, интересует формула в клетке `M5`, там гдето ошибка, так как если правильно, то оно должно выдать результат аналогичный как в клетке `L5`, помогите найти ошибку пожалуйста..
Николай, добрый день.
Есть такая таблица:
| A | B | C |
| 1 | ИМЯ | |
| 2 | 500 |
при помощи функции ВПР (искомое значение «ИМЯ»;) мы можем присвоить значение из строки №1. Но, там пустые ячейки.
Как присвоить значение из строки, которая находится ниже? В данном примере «500»
Добрый день! А если в таблице позиции повторяются, а значения у них разные? ВПР цепляет тогда первое совпадение и получаются некорректные данные.
Например структура таблицы такая:
СКЛАД 1
яблоки 15 кг
груши 20 кг
апельсины 15 кг
СКЛАД 2
арбузы 30 кг
дыни 30 кг
яблоки 10 кг
В этом случае получается всегда «яблоки 15 кг»
Доброе время суток, Николай, а если ситуация вот такая:
Наименование
Сочное Яблоко красное 121212
Сочное Яблоко зеленое 333222
Сочное яблоко грени 4443344
Сочная Груша зелёная 33333
Сочная Груша желтая 55555
и т.д. до бесконечности, как это бывает в прайсах. Искать надо по слову Яблоко\Груша, как это можно реализовать?
Заранее, огромное Вам спасибо.
С уважением, Джон.
Добрый день. Как можно в одной таблице совместить возможности функций ВПР и VLOOKUP3
Т.е. есть исходные данные которые содержат в разных строках одинаковый артикул.
Если использовать ВПР, то она ищет только первое значение, если VLOOKUP3, то массив, но только для одного артикула. А мне нужно в одну таблицу «собрать» данные соответствующие и одиночным артикулам и если их несколько.
Т.е. по сути сводная таблица, но с простой, двумерной структурой.
Составил пример в экселе, не могу его прикрепить.
Файл1 Закупка
| № п/п | Наименование | Страна | Артикул | Дата покупки | Объем партии, кг |
Цена | Стоимость партии |
| 9 | Капуста | Россия | 0004/01 | 01.12.2015 | 5 | 12 | 60 |
| 8 | Ананас | Эквадор | 0001/02 | 02.12.2015 | 10 | 120 | 1200 |
| 14 | Киви | Тунис | 0005/01 | 03.12.2015 | 13 | 60 | 780 |
| 11 | Грейпфрут | Марокко | 0003/01 | 04.12.2015 | 14 | 45 | 630 |
| 17 | Нектарин | Тайланд | 0007/01 | 05.12.2015 | 14 | 40 | 560 |
| 5 | Киви | Тунис | 0005/01 | 01.01.2016 | 23 | 60 | 1380 |
| 10 | Манго | Тайланд | 0006/01 | 01.01.2016 | 10 | 80 | 800 |
| 13 | Киви | Тунис | 0005/01 | 01.01.2016 | 15 | 80 | 1200 |
| 16 | Абрикос | Армения | 0001/01 | 01.01.2016 | 26 | 40 | 1040 |
| 3 | Капуста | Россия | 0004/01 | 01.02.2016 | 35 | 12 | 420 |
| 6 | Капуста | Россия | 0004/01 | 02.02.2016 | 36 | 12 | 432 |
| 2 | Груши | Россия | 0003/02 | 03.02.2016 | 40 | 38 | 1520 |
| 15 | Персик | Армения | 0008/01 | 04.02.2016 | 42 | 45 | 1890 |
| 1 | Яблоки | Россия | 0009/01 | 01.03.2016 | 60 | 23 | 1380 |
| 4 | Мандарины | Марокко | 0006/02 | 01.03.2016 | 45 | 45 | 2025 |
| 7 | Киви | Тунис | 0005/01 | 01.03.2016 | 60 | 60 | 3600 |
| 12 | Банан | Алжир | 0002/01 | 01.03.2016 | 48 | 22 | 1056 |
Файл2 Продажи
| № п/п | Артикул | Дата продажи | Объем партии, кг |
Цена | Стоимость партии |
| 1 | 0009/01 | 10.03.2016 | 60 | 33 | 1980 |
| 2 | 0003/02 | 10.02.2016 | 40 | 48 | 1920 |
| 3 | 0004/01 | 03.02.2016 | 35 | 22 | 770 |
| 4 | 0006/02 | 10.03.2016 | 45 | 55 | 2475 |
| 5 | 0005/01 | 20.01.2016 | 23 | 70 | 1610 |
| 6 | 0004/01 | 03.02.2016 | 36 | 30 | 1080 |
| 7 | 0005/01 | 10.03.2016 | 40 | 70 | 2800 |
| 8 | 0001/02 | 10.01.2016 | 10 | 130 | 1300 |
| 9 | 0005/01 | 11.03.2016 | 15 | 80 | 1200 |
| 10 | 0004/01 | 15.01.2016 | 5 | 35 | 175 |
| 11 | 0005/01 | 12.03.2016 | 5 | 90 | 450 |
| 12 | 0006/01 | 05.01.2016 | 10 | 90 | 900 |
| 13 | 0003/01 | 16.01.2016 | 14 | 55 | 770 |
| 14 | 0002/01 | 10.03.2016 | 48 | 32 | 1536 |
| 15 | 0005/01 | 05.01.2016 | 15 | 90 | 1350 |
| 16 | 0005/01 | 15.01.2016 | 10 | 70 | 700 |
| 17 | 0005/01 | 15.01.2016 | 3 | 80 | 240 |
| 18 | 0008/01 | 15.03.2016 | 42 | 55 | 2310 |
| 19 | 0001/01 | 15.01.2016 | 20 | 50 | 1000 |
| 20 | 0007/01 | 17.01.2016 | 14 | 50 | 700 |
| 21 | 0001/01 | 15.01.2016 | 6 | 60 | 360 |
Что необходимо
| Сводная таблица по продажам | |||||
| Наименование | Страна | Артикул | Продано | Средняя цена | Сумма |
| Абрикос | Армения | 0001/01 | 20 | 50 | 1000 |
| Ананас | Эквадор | 0001/02 | 10 | 130 | 1300 |
| Банан | Алжир | 0002/01 | 48 | 32 | 1536 |
| Грейпфрут | Марокко | 0003/01 | 14 | 55 | 770 |
| Груши | Россия | 0003/02 | 40 | 48 | 1920 |
| Капуста | Россия | 0004/01 | 76 | 29 | 2204 |
| Киви | Тунис | 0005/01 | 111 | 78.57 | 8721.27 |
| Манго | Тайланд | 0006/01 | 10 | 90 | 900 |
| Мандарины | Марокко | 0006/02 | 45 | 55 | 2475 |
| Нектарин | Тайланд | 0007/01 | 14 | 50 | 700 |
| Персик | Армения | 0008/01 | 42 | 55 | 2310 |
| Яблоки | Россия | 0009/01 | 60 | 33 | 1980 |
Не могли бы помочь. При использовании ВПР вопросов не возникло. Но при синхронизации моей таблицы и таблицы поставщика, в некоторых товарах , определяется как #Н/Д.
Это понятно так как не было найдено арт. в таблице поставщика. Но как сделать, если в таблице поставщика нет точного арт. номера оставлять то что уже было в ячейки, а не ставить #Н/Д.
День добрый! Подскажите пожалуйста, как сделать ВПР в такой ситуации (я уже думала о других формулах, как поискпоз, просмотр, но что-то как-то не выходит). Во вкладке Исходник 10 столбцов : Фио, группа, город, ДАТА. 9-й столбец Показатель, 10-й — время. Данные идут вертикально по Датам. Необходимо перенести данные Во вкладку Показатель, где первые 3 столбца Фио, группа, город, а после идут 1,2,3 . , то есть даты, они что во вкладке Исходник и Показатель указаны как числа (общий формат), так как сам файл идет за определенный месяц. Как в формуле привязать эти числа, чтоб переносились данные во вкладку Показатель по определенному Фио и дате.
Вкладка Исходник
| ФИО | Группа | Город | Дата | Показатель | Время |
| Петров Иван | Смирнов | Саратов | 3 | 196 | 0:01:54 |
| Петров Иван | Смирнов | Саратов | 4 | 176 | 0:02:54 |
| Иванова Катя | Кузнецов | Омск | 5 | 171 | 0:03:10 |
Вкладка Показатель
| ФИО | Группа | Город | 1 | 2 | 3 | 4 | 5 | 6 |
| Петров Иван | Смирнов | Саратов | ||||||
| Смирнова Юлия | Попов | Самара | ||||||
| Мартынова Екатерина | Попов | Самара | ||||||
| Иванова Катя | Кузнецов | Омск |
Заранее спасибо.
Николай, добрый день!
Подскажите, пожалуйста, как именно можно при использовании функции ВПР подставить (рядом) для выбранного значения (названия, маркировки) соответствующую ему таблицу с данными?
Пример:
Имеется: «лист-1» с названиями моделей и их техническим описанием.
Необходимо: на «лист-2» указывать актуальные в данный момент модели (в определенных ячейках, позиции ячеек определены для возможности последующей печати), и чтобы рядом с ними автоматически выводились таблицы с их описаниями с «лист-1».
Далее лист с таблицами выбранных моделей отправляется на печать.
Подскажите как можно заставить ВПР работать со списком «Наименование»(из вашего примера), диапазон которого меняется, может быть короче или длиннее. Нужно составить универсальный шаблон, где будут прописаны все формулы, но расчёты будут основываться на выборке с помощью ВПР по списку «Наименование», но длинна списка может меняться бессистемно в широком диапазоне 10-10 000.