Как подтянуть информацию в Excel из одного файла в другой?

Как сделать, чтобы информация из одного листа автоматически передавалась и отображалась на втором листе

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

Приведу три способа Автоматического переноса данных с одного листа программы «Эксель» в другой.

Первый, самый простой и примитивный способ связи двух таблиц на разных листах документа -вставка данных при помощи опции специальной вставки.

Рассмотрим, как соединить две таблицы по шагам.

Первый шаг.

Необходимо выделить ту таблицу, из которой будут транслироваться данные во вторую таблицу.

Второй шаг.

Копировать информацию сочетанием клавиш ctrl+C или вызвав контекстное меню правой кнопкой мыши и кликнув по пункту меню «Копировать»

Третий шаг.

Перейти на лист документа «Excel», в который Вы планируете транслировать информацию из первой таблицы.

Четвертый шаг.

Поставить курсор в первую (левую верхнюю) ячейку таблицы и выбрать в меню «Вставка» пункт «Вставить связь». В некоторых версиях программы «Excel» этот пункт находится в меню «Специальная вставка»

После вставки связи следует отформатировать вид ячеек – привести их к надлежащему виду.

Результат вставки связи

Второй способ переноса данных из одной таблицы в другую — это использование сводных таблиц в программе «Excel».

При использовании данного метода роль второй таблицы («реципиента») играет сама сводная таблица.

Как обновить сводную таблицу

При клике правой кнопкой мыши по сводной таблице и нажатии на пункт «Обновить» сводная таблица автоматически перенесет все данные из связанного массива информации («таблицы донора»).

О том, как в «Эксель» создавать сводные таблицы подробно написано в статье:

Как делать сводные таблицы в программе «Excel» и для чего они нужны.

Третий способ самый эффективный и наиболее автоматизированный — это использование меню надстройки «Power Query».

Правда нужно отметить, что этот способ подходит только пользователям Excel 2016 и пользователям Excel 2013и выше с установленной надстройкой «Power Query».

Смысл способа в следующем:

Необходимо открыть вкладку «Power Query». В разделе «Данные Excel» нажимаем кнопку (пиктограмму) «Из таблицы».

Из таблицы -Power Query

Далее нужно выбрать диапазон ячеек, из которых нужно «притянуть» информацию и нажимаем «Ок».

Источник данных для запроса Power Query

После выбора области данных появится окно настройки вида новой таблицы. В этом окне Вы можете настроить последовательность вывода столбцов и удалить ненужные столбцы.

После настройки вида таблицы нажмите кнопку «Закрыть и загрузить»

Обновление полученной таблицы происходит кликом правой кнопки мыши по названию нужного запроса в правой части листа (список «Запросы книги»). После клика правой кнопкой мыши в выпадающем контекстном меню следует нажать на пункт «Обновить»

Обновление запроса в PowerQuery

Как в офисе.

Как работает функция ВПР?

ВПР(искомое_значение,таблица,номер_столбца,[интервальный_просмотр]) Важно! Программа не умеет думать как человек, она четко сопоставляет факты. Если для нас название «Труба1» и «Труба 1» это одно и то же, то для Excel это разные вещи, т.к. в первом случае нет пробела перед 1, а во втором случае есть.

Теперь разжуем что к чему:

• ВПР — название функции
• Искомое значение — по этому значению мы будем подтягивать необходимые данные. Искомое значение, как правило, бывает код товара, который идентичный в любой базе данных. Если наименование номенклатуры может различаться, то код товара всегда одинаков, поэтому лучше всего использовать его. Для выделения диапазона данных не нужно мучатся, и выделять точное количество строк. Выделите весь столбец, нажав на номер или букву столбца.
• Таблица — это диапазон от столбца Искомого значения до подтягиваемых данных со страницы подтягиваемых данных, т.е. с другой страницы или книги. Так же выделяем столбцы. Важно! Искомое значение всегда должно быть справа от подтягиваемых данных.
• Номер столбца — порядковый номер столбца от искомого значения до подтягиваемых данных. Когда Вы выделяете диапазон, обратите внимание, Excel подсказывает Вам какой номер последнего выделенного столбца рядом с курсором мыши.
• Интервальный просмотр, что это такое знать вовсе не обязательно, просто ставьте всегда 0

Теперь рассмотрим эту функцию на примере:

Открываем книгу Excel

Давайте сделаем произвольную таблицу с тремя столбцами код товара, номенклатура, сумма период 1

При создании таблицы можно воспользоваться протягиванием. Данные вносим любые.

Теперь копируем нашу таблицу на другую страницу и меняем столбцы номенклатура (порядок наименований) и сумма период 2. Вот так, например:

Теперь возвращаемся на Лист1 и добавляем столбец Сумма период 2, прописываем формулу в столбце сумма период 2. Ставим курсор на первый товар и пишем =ВПР(, теперь выделяем мышкой столбец «код товара» это есть искомое значение и ставим точку с запятой(;) формула приобретает следующий вид =ВПР(А:А;

Дальше переходим на страницу или книгу с нужными для подтягивания данными и выделяем мышкой диапазон от искомого значения(код товара) до подтягиваемых данных (сумма период 2). Смотрим подсказку — номер столбца (ну или считаем самостоятельно) в нашем конкретном случае это третий столбец. Ставим точку с запятой (;) и пишем 3. Получаем следующий вид формулы:

Проставляем 0 и закрываем скобку =ВПР(А:А;Лист2!А:С;3;0). Протягиваем по всем наименованиям и получаем результат Для быстрого протягивания можно воспользоваться комбинациями клавиш:

Ставим курсор на первый столбец, нажимаем Ctrl+стрелочка вниз, оказываемся в низу таблицы. Теперь Ctrl+стрелочка вправо, отпускаем Ctrl и еще один раз вправо. Мы должны оказаться в столбце, где прописана наша формула внизу таблицы. Теперь нажимаем Shift+ Ctrl+стрелочка вверх, таким образом, выделяем диапазон до написанной формулы и нажимаем Ctrl+D. Готово.

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

БОНУС — КНОПКА ВСТАВКИ ЗНАЧЕНИЯ.

Для удобства и ускорения работы, кнопку специальной вставки как значение можно вывести на панель Excel. Как это работает? Выделяем полностью столбец или диапазон мышкой нажимаем комбинацию клавиш Ctrl+C (комбинация копирования), далее нажимаем кнопку специальной вставки, которую мы сейчас выведем в панель Excel. И все ГОТОВО!

Итак, выводим кнопку:

Идем в Файл/Параметры Excel/Панель быстрого доступа/

Выбираем нужную кнопку и добавляем в панел быстрого доступа. Нажимаем Ок и готово

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

СПЕЦИАЛЬНО ДЛЯ ВАШЕГО УДОБСТВА!

Скачать данный файл Функция ВПР

Понравилась статья? Отблагодари автора

Предыдущая глава Формулы 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-plus

Пошаговые инструкции, как использовать функции, формулы и другие инструменты табличного редактора Microsoft Excel

Функция ВПР в MS Excel. Описание и примеры использования.

• Главная
• Функция ВПР в MS Excel. Описание и примеры использования.

Функция ВПР в MS Excel. Описание и примеры использования.

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

Как вызвать функцию ВПР. Функция ВПР в Excel

В первую очередь разберемся, как вызвать данную функцию. Выбираем закладку Формулы. Находим кнопку Вставить функцию. И нажимаем ее. Так же, можно вызвать функцию ВПР, сочетанием клавиш Shift + F3.

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

Теперь перейдем непосредственно к вариантам применения функции ВПР.

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

Для примера возьмем две таблице. В одной Таблице №1 будет перечень с названиями конфет и будет указана их цена за кг. В другой, Таблица №2, тот же перечень, но с указанием их количества в кг. Наша задача добавить в Таблицу №2, в столбец Цена, цену конфет из Таблицы №1, чтобы в итоге получить стоимость. Названия конфет в разных таблицах находятся в разных местах, поэтому просто скопировать цену конфет с одной таблице в другую не получиться.

Перед тем, как вызвать функцию ВПР, выбираем нужную нам ячейку, в которой будет находиться наша формула функции и соответственно значение, которое мы хотим увидеть. В нашем случае это ячейка G3. Эта ячейка находиться в столбце Цена, Таблица №2. Функция ВПР позволит взять из Таблицы №1 цену Конфеты А и вставить эту цену в столбец Цена, Таблицы №2, напротив Конфеты А.

Вызываем функцию ВПР, как описано выше.

Аргументы функции. Функция ВПР в Excel.

Искомое_значение.

Значение поиска, которое должно быть найдена в указанном нами диапазоне, в строке Таблица. В нашем примере мы указываем Конфеты Ж (ячейка Е3, Таблица №2). Так как это значение идет первое в столбце Название конфет, Таблица №2. (Это не принципиально, но удобно). Это значение, которое будет искать наша функция в Таблице №1.

Что бы выбрать нужную нам ячейку с значением, достаточно просто стать курсором в строку Искомое_значение, а потом клацнуть левой кнопкой мыши, по нужной ячейке в таблице ( В нашем примере ячейка Е3).

Таблица.

Здесь необходимо указать диапазон таблицы, в которой будет происходить поиск нужного нам значения и данных, которые мы хотим перенести. В нашем примере это Таблица №1. Значение, по которому будет происходить поиск это название конфет. Данные, которые мы хотим перенести, это цена конфет. Мы просто ставим курсор в строку Таблица и выделяем нужный нам диапазон. В нашем примере это диапазон Таблицы №1 — B1:C12. При этом ссылки нужно сделать абсолютными, добавив знак $. Это можно сделать, просто добавив эти знаки к ячейкам диапазона, в строке Таблица — $B$1:$C$12.

Можно присвоить нашему диапазону имя, и прописать его в строке Таблица.

Как это сделать. Выбираем нужный нам диапазон. Таблица №1. Выбираем закладку Формулы, кнопка Задать имя. Нажимаем. Появляется диалоговое окно Создание имени. Пишем любое имя. Но нужно его запомнить. Например Конфеты. Нажимаем ОК.

В строке Таблица, вместо диапазона нужно будет ввести имя, которое мы присвоили – Конфеты

Номер_столбца.

Функция ВПР осуществляет поиск значение в самом левом столбце таблицы указанного диапазона поиска. Функция присваивает этому столбцу номер 1, по умолчанию. В нашем примере самый левый столбце, это Название конфет в Таблице №1. А в строке Номер_столбца, нам нужно указать, какой номер по порядку имеет столбец, из которого нужно перенести данные. В нашем примере это столбце Цена в Таблице №1. Он «второй» по порядку, если считать слева на право, от столбца Название конфет, Таблица №1. Поэтому в строку Номер_столбца мы пишем цифру 2. Если бы столбец Цена, Таблица №1 был бы расположен по порядку не на втором месте, а предположим на десятом, то мы соответственно указывали бы в строке Номер_столбца цифру 10.

Интервальный _просмотр.

В этой строке мы пишем цифру ноль «0». Это значит, что функция ВПР будет осуществлять поиск точных совпадений между значениями поиска (Искомое_значение) и значениями в крайнем левом столбце диапазона поиска (Таблица). В нашем примере поиск точных совпадений будет происходить между столбцом Название конфет, Таблица №1, и столбцом Название конфет в Таблице №2.

Если мы поставим цифру один «1», функция будет осуществлять поиск не точного совпадения, а приближенного к нашему критерию поиска.

Вот как это выглядит все вместе.

Протягиваем формулу по всему столбцу Цена в Таблице №2. Все цены перенесены с Таблице №1 в Таблицу №2.

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

У нас есть Таблица №1 и Таблица №2. Каждая таблица состоит из одного столбца. Для понимания алгоритма работы функции ВПР, в данном случае, таких простых таблиц достаточно. Столбцы содержат практически одинаковые данные. При этом, нам нужно сравнить их и узнать, какие данные есть в Таблице №2, но нет в Таблице №1.

Справа от Таблицы 2, в ячейку G3, вставляем функцию ВПР. Это расположение взято в качестве примера, можно использовать любой другой столбец и оформление.

В диалоговом окне, Аргументы функции прописываем следующие данные:

Искомое_значение. Это значение ячейки из Таблицы №2, наличие которой мы проверяем в Таблице №1. В нашем примере, это ячейка F3 (Значение 9).

Таблица. В данном случае мы указываем не диапазон всей таблицы, а только диапазон конкретного столбца, который мы сравниваем. Можно выделять столбец в таблице. А можно выделять весь столбец листа. В том случае, если в нем больше нет других данных. Вместо диапазона можно указать заданное имя столбца (Задаем имя).

Номер_столбца. Поскольку в таблице указан конкретный столбец, то здесь мы ставим цифру 1, так как искомое значение и данные, которые мы хотим перенести совпадают.

Интервальный _просмотр. Здесь ставим ноль «0», так как хотим, чтобы функция искала точные совпадения.

Нажимаем ОК и протягиваем функцию по всему столбцу. В некоторых ячейках, вместо искомого значения появляется ошибка формулы: #Н/Д. В данном случае, это значит, что заданные критерии поиска не были обнаружены в проверяемом столбце.

В нашем примере, в Таблице №2 есть Значение 17 и Значение 10. При проверке, в ячейки с функцией ВПР, вместо искомого значения появилась ошибка #Н/Д. Это значит, что в Таблице №1 нет ячейки с Значением 17 и Значением 10.

Можно проверить с точностью наоборот. И найти какие данные есть в Таблице №1 но нет в Таблице № 2.

Обратите внимание. Функция ВПР в Excel.

Функция ВПР осуществляет поиск значений (это значения, которые указаны в строке Искомое_значение) в первом (самом левом) столбец таблицы, диапазон которой указан в строке Таблица.

Пример, в Таблицу №1, добавили столбец Категория, и теперь столбец Название конфет уже не первый, а второй. Если мы укажем в строке Таблица, в качестве диапазона, все ячейки Таблицы №1, то функция ВПР не сработает (ошибка — #Н/Д), так как она будет осуществлять точный поиск в столбце Категория, Таблицы №1, значений из столбца Название конфет, Таблицы №2. И не найдет точных совпадений.

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

Если в диапазоне Таблица указан один, конкретный столбец, то функция ВПР проверяет только его. И данное правило не обязательно.

Можно осуществлять поиск на разных Листах. Алгоритм работы такой же. Формула функции будет выгладить вот так: =ВПР(E6;Лист1!$B$1:$C$11;2;0). В нашем примере формулы функция ВПР расположена на Листе 2, а поиск значения и перенос данных с диапазона поиска происходит на Листе 1. Вместо диапазона можно использовать Заданное имя. Например Конфеты. Тогда формула функции будет выглядеть вот так: =ВПР(E6;Конфеты;2;0).

Возможные ошибки.

#Н/Д — столбец таблицы, по которому происходит поиск, не крайний левый в диапазон поиска.

#Н/Д — диапазон таблицы, в которой происходит поиск, не закреплен. Нужно использовать либо абсолютные ссылки ($), либо присвоить диапазону Заданное имя.

#Н/Д – функция ВПР не находить точного совпадения в диапазоне поиска по заданному значению поиска.

#Н/Д – возможно необходимо отсортировать диапазон, в котором происходит поиск, по возрастанию.

#ССЫЛКА! – возможно номер столбца, который указан в строке Номер_столбца, указан неверно, и функция не находит данные, которые должна перенести.

Excel: как сравнить 2 таблицы и подставить данные из одной в другую автоматически

У меня есть одна задачка, и уже третий день ломаю голову — не знаю, как ее выполнить. Есть 2 таблицы (примерно 500-600 строк в каждой), нужно взять столбец с названием товара из одной таблицы и сравнить его с названием товара из другой, и, если товары совпадут — скопировать и подставить значение из таблицы 2 в таблицу 1. Запутанно объяснил, но думаю, по фотке задачу поймете ( прим. : фотка вырезана цензурой, все-таки личная информация) .

Заранее благодарю. Андрей, Москва.

Доброго дня всем!

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

Пример работы с функцией ВПР

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

Две таблицы в Excel — сравниваем первые столбцы

Как это сделать.

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

A2 — значение из первого столбца первой таблицы (то, что мы будем искать в первом столбце второй таблицы);

$E$1:$F$7 — полностью выделенная вторая таблица (в которой хотим что-то найти и скопировать). Обратите внимание на значок «$» — он необходим, чтобы при копировании формулы не менялись ячейки выделенной второй таблицы;

2 — номер столбца, из которого буем копировать значение (обратите внимание, что у нас выделенная вторая таблица имеет всего 2 столбца. Если бы у нее было 3 столбца — то значение можно было бы копировать из 2-го или 3-го столбца);

ЛОЖЬ — ищем точное совпадение (иначе будет подставлено первое похожее, что явно нам не подходит).

Какая должна быть формула

Собственно, можете готовую формулу подогнать под свои нужды, слегка изменив ее. Результат работы формулы представлен на картинке ниже: цена была найдена во второй таблице и подставлена в авто-режиме. Все работает!

Значение было найдено и подставлено автоматически

Чтобы цена была проставлена и для других наименований товара — просто растяните (скопируйте) формулу на другие ячейки. Пример ниже.

Растягиваем формулу (копируем формулу в другие ячейки)

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

Значения из одной таблицы подставлены в другую

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

Ну а у меня на этом пока всё, удачи!

Транспортировка данных в Ехсеl

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

Извлекаем полезную информацию В бухгалтерских программах обычно присутствует функция экспорта данных в обычный текстовый файл. А уже оттуда, из файла экспорта, данные можно импортировать в Excel, после чего с ними можно работать в этой программе. Для того чтобы произвести экспорт данных из бухгалтерской программы в текстовый файл, нужно нажать соответствующую кнопку или выбрать нужный пункт в меню. Как эта функция работает конкретно в вашей версии программы, можно узнать из руководства пользователя, которое поставляется изготовителем, или из справочной системы самого программного продукта. В большинстве современных бухгалтерских программ схема действий при выполнении экспорта данных выглядит примерно одинаково. Первым делом нужно отобрать в бухгалтерской программе документы, которые мы хотим перенести в другой файл. После того как сформируем их список, жмем на соответствующую кнопку или выбираем подходящий пункт меню для осуществления экспорта данных. Обычно программа перед началом процедуры переноса данных требует задать имя файла, в который будут сохранены выбранные документы, и указать папку для его сохранения. Результатом экспорта обычно бывает файл с расширением *.txt, содержащий в себе данные из бухгалтерской программы. Встречаются и другие расширения, но значительно реже. Переносим данные в Excel Если у вас нет необходимости переносить данные в Excel постоянно, можно использовать способ непосредственного открытия нужных файлов в среде Excel. Для этого сперва открываем программу Excel и жмем на кнопку открытия файла. В появившемся окошке в строке для типа файла выбираем параметр «Все файлы». А после этого ищем папку с файлом, который содержит данные из бухгалтерской программы, выделяем его курсором и нажимаем кнопку «Открыть». Если вдруг вы не видите нужного документа в папке, где он хранится, то скорее всего вы забыли выбрать пункт «Все файлы», и Excel его просто «не видит». Далее появится окно мастера по импорту данных из файла на лист Excel. В «Мастере текстов» имеются необходимые настройки и параметры, которые позволяют легко добиться хорошего результата. Нужно лишь следовать подсказкам мастера. Обычно импорт файлов производится в три шага. Для удобства пользователя на каждом из них есть окно для предварительного просмотра результатов изменений. Шаг первый. Здесь мы должны задать документу первоначальное форматирование, то есть определить, каким именно образом информация будет размещена по столбцам. Сперва в окне мастера в разделе «Формат файла» нужно выбрать тип используемой кодировки, чтобы текст нормально читался. Если в окне просмотра появляются какие-то непонятные слова или символы, попробуем выбрать для формата файла другой параметр. Потом указываем, с какой строки требуется производить импорт данных. Например, это необходимо, если в первой строке содержится техническое описание столбцов, которое нам не нужно. Когда все готово, нажимаем кнопку «Далее» для перехода к следующему шагу. Шаг второй. На этом этапе нам нужно определить, правильно ли был сделан выбор типа данных. Если в окне, где отображен результат, информация неправильно разбита по столбцам, возвращаемся назад и выбираем другой тип данных. При первом шаге у нас была возможность выбрать для формата данных значение «фиксированной ширины». Если мы установили галочку напротив этого пункта, то на втором шаге потребуется задать границы каждому столбцу. Сделать это можно на линейке в окне «Образец разбора данных». Символ разделителя столбцов пользователь выбирает самостоятельно. Какой именно знак установлен в данный момент, можно увидеть в табличке «Символом-разделителем является:». Если символ выбран неправильно, разбивка по столбцам будет происходить некорректно. Тогда следует подобрать или ввести другой символ-разделитель. Когда все станет нормально, жмем на кнопку «Далее» и переходим к последнему шагу. Шаг третий. В этот момент мы можем сразу исключить из таблицы ненужные столбцы, которые содержат лишнюю информацию. Для каждого из оставшихся столбцов укажем формат данных, который будет учитываться при создании листа в Excel. Будьте внимательны: при изменении формата происходит переформатирование данных, и это может привести к их искажению. Определившись со всеми столбцами, нажимаем на кнопку «Готово». После импорта полученную информацию нужно сразу сохранить. При этом обязательно выберите тип файла «Книга Microsoft Excel». А уже после полученные данные можно отформатировать на свой вкус. Если перенос данных прошел не совсем удачно, расстраиваться не стоит – процедуру импорта можно повторить еще раз. Но в мастере текстов попробуйте подобрать другие настройки и параметры. Регулярно обновляем файл Бывают случаи, когда нужно с некоторой периодичностью импортировать новые данные из специализированной бухгалтерской программы в файл Microsoft Excel, то есть регулярно его обновлять. Процедура такого перемещения очень похожа на вышеописанную. Сперва мы также производим экспорт данных из бухгалтерской программы в файл. А потом в меню программы Excel выбираем пункт «Данные», затем подпункт «Внешние данные», и далее – «Импорт текстового файла. ». В результате на экране появится окно импорта текстового файла, где вместо кнопки «Открыть» будет «Импорт». Выбираем документ, из которого будем брать информацию, и после этого повторяем три вышеописанных шага. Завершаем перенос данных выбором листа, куда следует поместить данные. В начале таблицы, в которую мы импортировали данные из бухгалтерской программы, можно создать новую строчку с описанием столбцов. А слева добавить столбец для нумерации строк. И в последующем они останутся неизменными, а все обновляемые данные будут размещаться за ними. Этот способ имеет свои преимущества. Если данные из бухгалтерской программы нужно постоянно экспортировать в один и тот же файл, то его обновление займет значительно меньше времени. Это возможно благодаря тому, что все шаги мастера не придется каждый раз повторять вручную, программа выполнит их самостоятельно. Обновить данные из бухгалтерской программы в уже имеющемся файле Excel еще проще. Для этого открываем книгу Microsoft Excel, в которой была сохранена информация из текстового файла, и ставим курсор в любую ячейку с перенесенными данными. Потом в меню Excel выбираем пункт «Данные», а затем – «Обновить данные». После этого появится окно для открытия файла, в котором уже указано имя документа, из которого вы брали информацию в прошлый раз. Остается лишь нажать кнопку «Импорт» – и можно приступать к обработке обновленных данных. Когда вы работаете с постоянно обновляемым файлом, не меняйте местами и не добавляйте новые столбцы между уже имеющимися. Это связано с тем, что при последующем импорте данных информация будет размещена в обычном порядке, и в результате данные в таблице будут смещены.

Excel предпочитает краткость Перенося в Excel большие таблицы, можно столкнуться с определенными проблемами. Если общее количество строк в таблице превышает тысячу, то автофильтр будет работать плохо. Дело в том, что этот режим охватывает только первую тысячу строк, а остальные игнорирует. И корректно произвести фильтрацию данных такой длинной таблицы будет невозможно. Еще один «скользкий участок» при использовании функции автофильтра – полностью пустая строка внутри таблицы. В этом случае отфильтрованы будут только строки, расположенные выше пустой. А данные, размещенные после незаполненной строчки, участвовать в режиме фильтра не будут.