Прочитав статью, вы не только узнаете, как найти данные в таблице Excel и извлечь их в другую, но и приёмы, которые можно применять вместе с функцией ВПР.
Батьянов Денис на правах гостевого автора рассказывает в этом посте о том, как найти данные в одной таблице Excel и извлечь их в другую, а также открывает все секреты функции вертикального просмотра.
При работе в Excel очень часто возникает потребность найти данные в одной таблице и извлечь их в другую. Если вы ещё не умеете это делать, то, прочитав статью, вы не только научитесь этому, но и узнаете, при каких условиях вы сможете выжать из системы максимум быстродействия. Рассмотрено большинство весьма эффективных приёмов, которые стоит применять совместно с функцией ВПР.
Даже если вы годами используете функцию ВПР, то с высокой долей вероятности эта статья будет вам полезна и не оставит равнодушным. Я, например, будучи IT-специалистом, а потом и руководителем в IT, пользовался VLOOKUP 15 лет, но разобраться со всеми нюансами довелось только сейчас, когда я на профессиональной основе стал обучать людей Excel.
ВПР — это аббревиатура от вертикального просмотра. Аналогично и VLOOKUP — Vertical LOOKUP. Уже само название функции намекает нам, что она производит поиск в строках таблицы (по вертикали — перебирая строки и фиксируя столбец), а не в столбцах (по горизонтали — перебирая столбцы и фиксируя строку). Надо заметить, что у ВПР есть сестра — гадкий утёнок, которая никогда не станет лебедем, — это функция ГПР (HLOOKUP). ГПР, в противоположность ВПР, производит горизонтальный поиск, однако концепция Excel (да и вообще концепция организации данных) подразумевает, что ваши таблицы имеют небольшое количество столбцов и гораздо большее количество строк. Именно поэтому поиск по строкам нам требуется во много раз чаще, чем по столбцам. Если вы в Excel слишком часто пользуетесь функцией ГПР, то, вполне вероятно, что вы чего-то не поняли в этой жизни.
Синтаксис
Функция ВПР имеет четыре параметра:
=ВПР( <ЧТО> ; <ГДЕ> ; <НОМЕР_СТОЛБЦА> [;<ОТСОРТИРОВАНО>] ), тут:
<ЧТО> — искомое значение (редко) или ссылка на ячейку, содержащую искомое значение (подавляющее большинство случаев);
<ГДЕ> — ссылка на диапазон ячеек (двумерный массив), в ПЕРВОМ (!) столбце которого будет осуществляться поиск значения параметра <ЧТО>;
<НОМЕР_СТОЛБЦА> — номер столбца в диапазоне, из которого будет возвращено значение;
<ОТСОРТИРОВАНО> — это очень важный параметр, который отвечает на вопрос, а отсортирован ли по возрастанию первый столбец диапазона <ГДЕ>. В случае, если массив отсортирован, мы указываем значение ИСТИНА (TRUE) или 1, в противном случае — ЛОЖЬ (FALSE) или 0. В случае, если данный параметр опущен, он по умолчанию становится равным 1.
Держу пари, что многие из тех, кто знает функцию ВПР как облупленную, прочитав описание четвёртого параметра, могут почувствовать себя неуютно, так как они привыкли видеть его в несколько ином виде: обычно там идёт речь о точном соответствии при поиске (ЛОЖЬ или 0) либо же о диапазонном просмотре (ИСТИНА или 1).
Вот сейчас надо напрячься и читать следующий абзац несколько раз, пока не прочувствуете смысл сказанного до конца. Там важно каждое слово. Примеры помогут разобраться.
Как же конкретно работает формула ВПР
- Вид формулы I. Если последний параметр опущен или указан равным 1, то ВПР предполагает, что первый столбец отсортирован по возрастанию, поэтому поиск останавливается на той строке, которая непосредственно предшествует строке, в которой находится значение, превышающее искомое. Если такой строки не найдено, то возвращается последняя строка диапазона.
- Вид формулы II. Если последний параметр указан равным 0, то ВПР последовательно просматривает первый столбец массива и сразу останавливает поиск, когда найдено первое точное соответствие с параметром <ЧТО>, в противном случае возвращается код ошибки #Н/Д (#N/A).
Схемы работы формул
ВПР тип I
ВПР тип II
Следствия для формул вида I
- Формулы можно использовать для распределения значений по диапазонам.
- Если первый столбец <ГДЕ> содержит повторяющиеся значения и правильно отсортирован, то будет возвращена последняя из строк с повторяющимися значениями.
- Если искать значение заведомо большее, чем может содержать первый столбец, то можно легко находить последнюю строку таблицы, что бывает довольно ценно.
- Данный вид вернёт ошибку #Н/Д, только если не найдёт значения меньше или равное искомому.
- Понять, что формула возвращает неправильные значения, в случае если ваш массив не отсортирован, довольно затруднительно.
Следствия для формул вида II
Если искомое значение встречается в первом столбце массива несколько раз, то формула выберет первую строку для последующего извлечения данных.
Производительность работы функции ВПР
Вы добрались до кульминационного места статьи. Казалось бы, ну какая разница, укажу ли я в качестве последнего параметра ноль или единицу? В основном все указывают, конечно же, ноль, так как это довольно практично: не надо заботиться о сортировке первого столбца массива, сразу видно, найдено значение или нет. Но если у вас на листе несколько тысяч формул ВПР (VLOOKUP), то вы заметите, что ВПР вида II работает медленно. При этом обычно все начинают думать:
- мне нужен более мощный компьютер;
- мне нужна более быстрая формула, например, многие знают про ИНДЕКС+ПОИСКПОЗ (INDEX+MATCH), которая якобы быстрее на жалкие 5–10%.
И мало кто думает, что стоит только начать использовать ВПР вида I и обеспечить любыми способами сортировку первого столбца, как скорость работы ВПР возрастёт в 57 раз. Пишу прописью — В ПЯТЬДЕСЯТ СЕМЬ РАЗ! Не на 57%, а на 5 700%. Данный факт я проверил вполне надёжно.
Секрет такой быстрой работы кроется в том, что на отсортированном массиве можно применять чрезвычайно эффективный алгоритм поиска, который носит название бинарного поиска (метод деления пополам, метод дихотомии). Так вот ВПР вида I его применяет, а ВПР вида II ищет без какой-либо оптимизации вообще. То же самое относится и к функции ПОИСКПОЗ (MATCH), которая включает в себя аналогичный параметр, а также и к функции ПРОСМОТР (LOOKUP), которая работает только на отсортированных массивах и включена в Excel ради совместимости с Lotus 1-2-3.
Недостатки формулы
Недостатки ВПР очевидны: во-первых, она ищет только в первом столбце указанного массива, а во-вторых, только справа от данного столбца. А как вы понимаете, вполне может случиться так, что столбец, содержащий необходимую информацию, окажется слева от столбца, в котором мы будем искать. Этого недостатка лишена уже упомянутая связка формул ИНДЕКС+ПОИСКПОЗ (INDEX+MATCH), что делает её наиболее гибким решением по извлечению данных из таблиц в сравнении с ВПР (VLOOKUP).
Некоторые аспекты применения формулы в реальной жизни
Диапазонный поиск
Классическая иллюстрация к диапазонному поиску — задача определения скидки по размеру заказа.
Поиск текстовых строк
Безусловно, ВПР ищет не только числа, но и текст. При этом надо принимать во внимание, что регистр символов формула не различает. Если использовать символы подстановки, то можно организовать нечёткий поиск. Есть два символа подстановки: «?» — заменяет один любой символ в текстовой строке, «*» — заменяет любое количество любых символов.
Борьба с пробелами
Часто поднимается вопрос, как решить проблему лишних пробелов при поиске. Если справочную таблицу ещё можно вычистить от них, то первый параметр формулы ВПР не всегда зависит от вас. Поэтому если риск засорения ячеек лишними пробелами присутствует, то можно применять для очистки функции СЖПРОБЕЛЫ (TRIM).
Разный формат данных
Если первый параметр функции ВПР ссылается на ячейку, которая содержит число, но которое хранится в ячейке в текстовом виде, а первый столбец массива содержит числа в правильном формате, то поиск будет неудачным. Возможна и обратная ситуация. Проблема легко решается переводом параметра 1 в необходимый формат:
=ВПР(−−D7; Продукты!$A$2:$C$5; 3; 0) — если D7 содержит текст, а таблица — числа;
=ВПР(D7 & «»); Продукты!$A$2:$C$5; 3; 0) — и наоборот.
Кстати, перевести текст в число можно сразу несколькими способами, выбирайте:
- Двойное отрицание —D7.
- Умножение на единицу D7*1.
- Сложение с нулём D7+0.
- Возведение в первую степень D7^1.
Перевод числа в текст производится через сцепку с пустой строкой, которая заставляет Excel преобразовать тип данных.
Как подавить выдачу #Н/Д
Это очень удобно делать при помощи функции ЕСЛИОШИБКА (IFERROR).
Например: =ЕСЛИОШИБКА( ВПР(D7; Продукты!$A$2:$C$5; 3; 0); «»).
Если ВПР вернёт код ошибки #Н/Д, то ЕСЛИОШИБКА его перехватит и подставит параметр 2 (в данном случае пустая строка), а если ошибки не произошло, то эта функция сделает вид, что её вообще нет, а есть только ВПР, вернувший нормальный результат.
Массив <ГДЕ>
Часто забывают ссылку массива сделать абсолютной, и при протягивании массив «плывёт». Помните, что вместо A2:C5 следует использовать $A$2:$C$5.
Хорошей идеей является размещение справочного массива на отдельном листе рабочей книги. Не путается под ногами, да и сохраннее будет.
Ещё более хорошей идеей будет объявление этого массива в виде именованного диапазона.
Многие пользователи при указании массива используют конструкцию вида A:C, указывая столбцы целиком. Этот подход имеет право на существование, так как вы избавлены от необходимости отслеживать тот факт, что ваш массив включает все необходимые строки. Если вы добавите строки на лист с первоначальным массивом, то диапазон, указанный как A:C, не придётся корректировать. Безусловно, эта синтаксическая конструкция заставляет Excel проводить несколько большую работу, чем при точном указании диапазона, но данными накладными расходами можно пренебречь. Речь идёт о сотых долях секунды.
Ну и на грани гениальности — оформить массив в виде умной таблицы.
Использование функции СТОЛБЕЦ для указания колонки извлечения
Если таблица, в которую вы извлекаете данные при помощи ВПР, имеет ту же самую структуру, что и справочная таблица, но просто содержит меньшее количество строк, то в ВПР можно использовать функцию СТОЛБЕЦ() для автоматического расчёта номеров извлекаемых столбцов. При этом все ВПР-формулы будут одинаковыми (с поправкой на первый параметр, который меняется автоматически)! Обратите внимание, что у первого параметра координата столбца абсолютная.
Создание составного ключа через &»|»&
Если возникает необходимость искать по нескольким столбцам одновременно, то необходимо делать составной ключ для поиска. Если бы возвращаемое значение было не текстовым (как тут в случае с полем «Код»), а числовым, то для этого подошла бы более удобная формула СУММЕСЛИМН (SUMIFS) и составной ключ столбца не потребовался бы вовсе.
Это моя первая статья для Лайфхакера. Если вам понравилось, то приглашаю вас посетить мой сайт, а также с удовольствием прочту в комментариях о ваших секретах использования функции ВПР и ей подобных. Спасибо. 🙂
Инструкции
Гид по ВПР в Excel и Google Таблицах
Что это за функция и как с ней работать
Если нужно объединить данные в таблицах, можно вручную перепроверять и переносить значения с одного места на другое. Но это сложно и долго, плюс легко ошибиться.
Чтобы было быстрее и проще работать, в Google Таблицах и Excel есть множество функций. Одна из таких — ВПР (VLOOKUP). Она мгновенно и точно находит нужные данные в указанном диапазоне, позволяет автоматически переносить их с одного листа на другой (или с одной таблицы на другую, если использовать вместе с функцией IMPORTRANGE).
Чтобы самостоятельно поработать с шаблоном и примерами из статьи, можно открыть эту таблицу, выбрать «Файл → Создать копию».
Как работает ВПР
ВПР (VLOOKUP) — функция поиска и извлечения данных, которая:
- принимает определенный набор символов в качестве запроса;
- ищет совпадение с этим запросом в крайнем левом столбце заданного диапазона;
- копирует значения из ячейки, которая находится в соседнем столбце, но на этой же строке.
Так, ВПР используют магазины, когда нужно объединить или сравнить две таблицы. К примеру, таблицу заказов (какой товар заказали) и прайс-лист (по какой цене заказали, сколько денег ушло на закупку партии и так далее). Или, допустим, ВПР можно использовать, чтобы вычислить скидку для клиента или размер прибыли работника в зависимости от количества продаж.
Функция принимает четыре параметра: запрос, диапазон, номер столбца и сортировки. Подробнее о каждом:
Запрос показывает, что мы ищем в таблице. Например, наименование товара.
Диапазон отражает, где мы ищем запрос. Например, в диапазоне B2:C20. И если будет совпадение с ячейкой B1, функция ничего не вернет, так как эта ячейка не входит в указанный диапазон.
Индекс — номер столбца, который определяет, из какого столбца возвращать значение. Например, если в качестве диапазона указать B2:D11, то столбец C будет вторым, а D — третьим.
Важно: нельзя указать, в каком столбце искать совпадение с запросом — ВПР всегда «смотрит» только в крайнем левом столбце диапазона. Правда, есть лайфхаки, как обойти это ограничение, о которых мы расскажем позже.
Сортировка говорит, отсортированы значения в таблице или нет. 1 или ИСТИНА (TRUE) — да, 0 или ЛОЖЬ (FALSE) — нет.
Как правило, указывают 0 — в таком случае ВПР будет искать только точное совпадение с запросом. В противном случае функция выберет значения, которые примерно похожи на запрос — то есть меньшие или равные ему.
К тому же неточный поиск работает только в отношении чисел. Если ищем по словам, нужно обязательно передавать последним параметром 0 (ЛОЖЬ, FALSE).
А что если будет несколько ячеек, которые соответствуют искомому запросу? Тогда функция все равно выдаст только один результат — завершит работу, как только наткнется на первое совпадение.
Как пользоваться функцией ВПР
Разберемся на примере. Допустим, магазин электроники постоянно торгует разными видами товаров и в очередной раз закупил новую партию.
Прайс-лист магазину выслали в отдельной таблице, поэтому теперь информацию из полученной таблицы нужно перенести в собственную, в которой ведется учет.
Полученная таблица со списком товаров и ценой
Перенести данные можно вручную, если товаров не очень много. Но если таблица состоит из сотни наименований — это проще сделать с ВПР.
А это — таблица, в которую нужно передать данные из предыдущей
Шаг 1: Выбираем функцию и запрос
Для этого в ячейке «стоимость покупки» набираем равно «=» и пишем ВПР. После этого нажимаем на ячейку с запросом в столбце «название товара». Либо прописываем в скобках координаты ячейки. В нашем случае это «B4».
Первый этап работы — выбираем запрос
Шаг 2: Настраиваем диапазон запроса
После того, как выбрали запрос, настраиваем диапазон. Для этого выделяем всю вторую таблицу, из которой в будущем функция будет искать информацию.
Важно! Выделяйте только ячейки, в которых нужно искать запросы. Помните, что ВПР ищет совпадения только по первому столбцу (крайнему слева).
Второй этап — настраиваем диапазон ячеек
Чтобы выбрать диапазон, можно прямо во время написания формулы просто переключиться на нужный лист и выделить там ячейки. Чтобы все нормально вставилось, важно никуда не переключаться — данные сбросятся, если, например, нажать на другой лист.
Переносим диапазон из одной таблицы в другую
Шаг 3: Выбираем номер столбца
Индекс (номер столбца) передаем следующим после диапазона.
Третий этап — выбираем столбец
Важно: столбцы считаются внутри выбранного диапазона.
Так, в нашем примере нужно взять и перенести информацию о стоимости электроники, которая находится в столбце D. Если смотреть на весь лист, то D — это четвертый по счету столбец. А вот в диапазоне C:D столбец D — это именно второй по счету.
Шаг 4: Выбираем параметр «отсортировано» или «не отсортировано»
На этом этапе функция определяет, что искать: точное или неточное (меньшее или равное) совпадение с запросом. Напомню, что здесь есть два варианта:
0 (ЛОЖЬ, FALSE). ВПР выбирает точь-в-точь подходящий вариант. Как правило, используют именно такой режим поиска.
1 (ИСТИНА, TRUE). Функция выбирает примерно подходящий вариант, меньший или равный, но не больший. Это нужно гораздо реже, и имеет смысл, только если значения в диапазоне отсортированы.
Допустим, нам нужно сопоставить размер скидки для клиента с количеством покупок. Для этого создаем отдельную таблицу с диапазоном скидок. Важно, чтобы диапазон был возрастающий. Например, 5, 7, 9, 12, 15. Иначе функция работать не будет.
Сортируем запрос на примере скидок
Теперь прописываем функцию. Ячейка запроса — количество покупок, диапазон — вторая таблица с количеством покупок и размером скидки, номер столбца — второй, сортировка — «ИСТИНА» или «1».
Вот так выглядит формула для расчета скидок
Все еще сортируем запрос
В итоге получаем таблицу с расчетами скидок для клиентов.
Ура! Все работает
Шаг 5: Настраиваем функцию под всю таблицу
Мы прописали функцию для одной ячейки. Чтобы не прописывать ее для каждой отдельно, можно просто протянуть — выделить ячейку с формулой, зажать точку в правом нижнем углу и потянуть вниз.
Но перед этим важно зафиксировать значения диапазона. Для этого нужно поставить знаки доллара как минимум после названий столбцов («G» и «H» в нашем случае), а лучше и перед тоже. Это можно сделать вручную или выделить диапазон и нажать «F4» на клавиатуре.
В противном случае при протягивании формула будет меняться, например, так: G4:H16 → G5:H17 → G6:H18 и так далее. А нам важно искать все значения в определенном диапазоне.
Пятый этап — настроили функцию для всей таблицы
Как сравнить таблицы с помощью ВПР
ВПР также используют, чтобы сравнить таблицы. Например, если изменилась цена на товары, можно быстро сравнить две таблицы и рассчитать процент изменений.
Для начала подтягиваем в таблицу старые цены. Формула будет выглядеть так:
=ВПР(A4;’Таблица со старыми ценами’!$B$4:$C$16;2;0)
Подтягиваем в таблицу данные со старыми ценами
После этого добавляем данные из таблицы с новыми ценами. Формула будет такой:
=ВПР(A4;$G$4:$H$16;2;0)
Таблица для сравнения старых и новых цен
Теперь можно сравнить цены. Чтобы не делать это вручную, прописывайте формулу: новая цена – старая цена / новая цена. А в формате чисел выберите процент.
Сравниваем цены в процентном отношении
Как работать с ВПР, если искомое значение — слева, а не справа
Допустим, мы купили технику с разными ценами, кодами товаров и количеством. Попробуем узнать цену конкретной модели через код товара и формулы ВПР.
Вот такую табличку мы получили от поставщика: с названием товара, ценой, кодом и количеством
Прописать ВПР без изменений в таблице нельзя — по правилам формулы поиск производится по крайнему левому столбцу диапазона.
Поэтому самый простой способ — скопировать столбец «Цена» и перенести его в правый, после «Код товара». После этого внести новый столбец в диапазон и работать с ВПР как и прежде.
Если такой вариант не подходит, есть более сложный — через массивы.
Массив в Excel и Google Таблицах — это определенный набор данных, можно сказать, та же таблица, только «виртуальная».
Массивы могут быть одномерными, то есть состоять только из строк вроде {12} или столбцов вида {1;2}. Или же многомерными — включать и столбцы, и строки.
Для поиска цены создадим отдельные поля «Код товара» и «Цена».
Поля можно создавать в листе с товарами или в новом
И прописываем ВПР для ячейки «Цена». Функция будет вида
=ВПР(H6;{‘Лист6’!D:D ‘Лист6’!C:C};2;0), где:
- H6 — номер ячейки с кодом товара.
- Лист6 — название листа, в котором находится наша таблица с ценами, кодами и количеством товаров.
- D:D — диапазон столбца с кодами товаров.
- C:C — диапазон столбца с ценами товаров.
- 2 — номер столбца из диапазона.
- 0 — точное соответствие.
Фигурными скобками как раз создаем массив, а обратной косой чертой показываем, что данные разделяются по столбцам. Тем самым меняем столбцы исходной таблицы местами — теперь сначала идет D:D, а только потом C:C. Такой лайфхак по обходу ограничения функции ВПР.
Так выглядит формула с массивами
В итоге, когда введем в ячейку «Код товара» соответствующие данные, получим информацию о стоимости устройств.
Проверяем, как все работает
Есть и другие способы работать с ВПР, если искомое значение слева. Например, с помощью функции СУММЕСЛИ, ВЫБОР, ИНДЕКС и ПОСКПОЗ — о таком варианте рассказывали на сайте «Планета Excel». Другую полезную инструкцию по работе с массивами выкладывали в телеграм-канале «Google Таблицы».
Как использовать символьные шаблоны ВПР
Мы рассказывали о том, что в ВПР есть неточный поиск, который работает только с цифрами. Но для неточного поиска по словам тоже кое-что есть.
Разберемся на примере наших товаров. Попробуем найти ячейку, в которой встречается слово Iphone с любыми словами и цифрами до и после него.
Ищем Iphone из списка
Для этого прописываем формулу:
=ВПР(«*Iphone*»;диапазон;1;0)
Звездочки означают любое количество любых символов (в том числе их отсутствие). То есть условию будут соответствовать и «Apple Iphone», и «Iphone 12», и «Iphone».
Проверяем, как работает фильтрация со звездочкой
А чтобы найти ячейку со словом Iphone с определенным количеством знаков после него, нужно составить формулу вида:
=ВПР(«*Iphone ?? ???»;диапазон;1;0)
А теперь смотрим, как работает более строгая фильтрация. Каждый знак вопроса — один символ.
Напоминаем: если в таблице есть несколько ячеек, которые соответствуют запросу из формулы, то функция выдаст только первое вхождение по порядку.
Также ВПР можно настроить для нескольких условий одновременно, о таком способе рассказывал в своем блоге Евгений Намоконов. Пригодится, например, чтобы быстро найти стоимость битого Iphone 12 из таблицы.
ЭКСКЛЮЗИВЫ ⚡️
Читайте только в блоге
Unisender
Поделиться
СВЕЖИЕ СТАТЬИ
Другие материалы из этой рубрики
Не пропускайте новые статьи
Подписывайтесь на соцсети
Делимся новостями и свежими статьями, рассказываем о новинках сервиса
«Честно» — авторская рассылка от редакции Unisender
Искренние письма о работе и жизни. Свежие статьи из блога. Эксклюзивные кейсы
и интервью с экспертами диджитала.
Хитрости »
15 Май 2011 516089 просмотров
Как найти значение в другой таблице или сила ВПР
- Задача и её решение при помощи ВПР
- Описание аргументов ВПР
- Что важно всегда помнить при работе с ВПР
- Как избежать ошибки #Н/Д(#N/A) в ВПР?
- Как при помощи ВПР искать значение по строке, а не столбцу?
- Решение при помощи ПОИСКПОЗ
- Работа с критериями длиннее 255 символов
Если в двух словах, то ВПР позволяет сравнить данные двух таблиц на основании значений из одного столбца.
Чтобы чуть лучше понять принцип работы ВПР лучше начать с некоего практического примера. Возьмем две таблицы:
рис.1
На картинке выше для удобства они показаны рядом, но на самом деле могут быть расположены на разных листах и даже в разных книгах. Таблицы по сути одинаковые, но фамилии в них расположены в разном порядке, и к тому же в одной заполнены все столбцы, а во второй столбцы ФИО и Отдел. И из первой таблицы необходимо подставить во вторую дату для каждой фамилии. Для трех записей это не проблема и руками сделать – все очевидно. Но в жизни это таблицы на тысячи записей и поиск с подстановкой данных вручную может занять не один час. Вот где ВПР(VLOOKUP) будет весьма кстати. Все, что необходимо – записать в ячейку
C2
второй таблицы(туда, куда необходимо подставить даты из первой таблицы) такую формулу:
=ВПР($A2;Лист1!$A$1:$C$4;3;0)
=VLOOKUP($A2,Лист1!$A$1:$C$4,3,0)
Записать формулу можно либо непосредственно в ячейку, либо воспользовавшись диспетчером функций, выбрав в категории Ссылки и массивы(References & Arrays) функцию ВПР(VLOOKUP) и по отдельности указав нужные критерии. Теперь копируем(
Ctrl
+
C
) ячейку с формулой(С2), выделяем все ячейки столбца
С
до конца данных и вставляем(
Ctrl
+
V
).
Теперь разберем поподробнее саму функцию, её аргументы и некоторые особенности.
ВПР ищет заданное нами значение(аргумент искомое_значение) в первом столбце указанного диапазона(аргумент таблица). Поиск значения всегда происходит сверху вниз(собственно, поэтому функция и называется ВПР: Вертикальный ПРосмотр). Как только функция находит заданное значение – поиск прекращается, ВПР берет строку с найденным значением и смотрит на аргумент номер_столбца. Именно из этого столбца берётся значение, которое мы и видим как итог работы функции. Т.е. в нашем конкретном случае, для ячейки С2 второй таблицы, функция берет фамилию “Петров С.А.”(ячейка $A2 второй таблицы) и ищет её в первом столбце указанной таблицы(Лист1!$A$1:$C$4), т.е. в столбце А. Как только находит(это ячейка А3)
ВПР может вернуть только одно значений – первое, подходящее под критерий. Если искомое значение не найдено(отсутствует в таблице), то результатом функции будет ошибка #Н/Д(#N/A). Не надо этого бояться – это даже полезно. Вы точно будете знать, каких записей нет и таким образом можете сравнивать две таблицы друг с другом. Иногда получается так, что Вы видите: данные есть в обеих таблицах, но ВПР выдает #Н/Д. Значит данные в Ваших таблицах не идентичны. В какой-то из них есть лишние неприметные пробелы(обычно перед значением или после), либо знаки кириллицы перемешаны со знаками латиницы. Так же #Н/Д будет, если критерии числа и в искомой таблице они записаны как текст(как правило в левом верхнем углу такой ячейки появляется зеленый треугольничек), а в итоговой – как числа. Или наоборот.
Описание аргументов ВПР
- Искомое_значение($A2) – это то значение из одной таблицы, которые мы ищем в другой таблице. Т.е. для первой записи второй таблицы это будет Петров С.А.. Здесь можно указать либо непосредственно текст критерия(в этом случае он должен быть в кавычках – =ВПР(“Петров С.А”;Лист1!$A$1:$C$4;3;0), либо ссылку на ячейку, с данным текстом(как в примере функции). Есть небольшой нюанс: так же можно применять символы подстановки: “*” и “?”. Это очень удобно, если необходимо найти значения лишь по части строки. Например, можно не вводить полностью “Петров С.А”, а ввести лишь фамилию и знак звездочки – “Петров*”. Тогда будет выведена любая запись, которая начинается на “Петров”. Если же надо найти запись, в которой в любом месте строки встречается фамилия “Петров”, то можно указать так: “*петров*”. Если хотите найти фамилию Петров и неважно какие инициалы будут у имени-отчества(если ФИО записаны в виде Иванов И.И.), то здесь в самый раз такой вид: “Иванов ?.?.”.
Часто необходимо для каждой строки указать свое значение(в столбце А Фамилии и надо их все найти). В таком случае всегда указываются ссылки на ячейки столбца А. Например, в ячейке A2 записано: Иванов. Так же известно, что Иванов есть в другой таблице, но после фамилии могут быть записаны и имя и отчество(или еще что-то). Но нам нужно найти только строку, которая начинается на фамилию. Тогда необходимо записать следующим образом: A2&”*”. Эта запись будет равнозначна “Иванов*”. В A2 записано Иванов, амперсанд(&) используется для объединения в одну строку двух текстовых значений. Звездочка в кавычках (как и положено быть тексту внутри формулы). Таким образом и получаем:
A2&”*” =>
“Иванов”&”*” =>
“Иванов*”
А полная формула в итоге будет выглядеть так: =ВПР(A2&”*”;Лист1!$A$1:$C$4;3;0)
Очень удобно, если значений для поиска много.
Если надо определить есть ли хоть где-то слово в строке, то звездочки ставим с обеих сторон: “*”&A1&”*” - Таблица(Лист1!$A$1:$C$4) – указывается диапазон ячеек, в первом столбце которых будет просматриваться аргумент Искомое_значение. Диапазон должен содержать данные от первой ячейки с данными до самой последней. Это не обязательно должен быть указанный в примере диапазон. Если строк 100, то Лист1!$A$2:$C$100. Диапазон в аргументе таблица всегда должен быть “закреплен”, т.е. содержать знаки доллара($) перед названием столбцов и перед номерами строк(Лист1!$A$1:$C$4).
- Номер_столбца(3) – указывается номер столбца в аргументе Таблица, значения из которого нам необходимо записать в итоговую ячейку в качестве результата. В примере это Дата принятия – т.е. столбец №3. Если бы нужен был отдел, то необходимо было бы указать номер столбца 2, а если бы нам понадобилось просто сравнить есть ли фамилии одной таблицы в другой, то можно было бы указать и 1. Номер столбца всегда указывается числом и не должен быть больше числа столбцов в аргументе Таблица.
если аргумент Таблица имеет слишком большое кол-во столбцов и необходимо вернуть результат из последнего столбца, то совсем необязательно высчитывать их количество. Можно использовать формулу, которая подсчитывает количество столбцов в указанном диапазоне: =ВПР($A2;Лист1!$A$1:$C$4;ЧИСЛСТОЛБ(Лист1!$A$1:$C$4);0). К слову в данном случае Лист1! тоже можно убрать, т.к. функция ЧИСЛОСТОЛБ просто подсчитывает количество столбцов в переданном ей диапазоне и неважно на каком он листе: =ВПР($A2;Лист1!$A$1:$C$4;ЧИСЛСТОЛБ($A$1:$C$4);0).
- Интервальный_просмотр(0) – очень интересный аргумент. Может быть равен либо ИСТИНА либо ЛОЖЬ. Так же допускается указать 1 или 0. 1 = ИСТИНА, 0 = ЛОЖЬ. Если в ВПР указать данный параметр равный 0 или ЛОЖЬ, то будет происходить поиск точного соответствия заданному Искомому_значению. Это не имеет никакого отношения к знакам подстановки(“*” и “?”). Если же использовать 1 или ИСТИНА, то…Совсем в двух словах не объяснить. Если вкратце – ВПР будет искать наиболее похожее значение, подходящее под Искомомое_значение. Иногда очень полезно. Правда, если использовать данный параметр, то необходимо, чтобы список в аргументе Таблица был отсортирован по возрастанию. Обращаю внимание на то, что сортировка необходима только в том случае, если аргумент Интервальный_просмотр равен ИСТИНА или 1. Если же 0 или ЛОЖЬ – сортировка не нужна. Этот аргумент необходимо использовать осторожно – не стоит указывать 1 или ИСТИНА, если нужно найти точное соответствие и уж тем более не стоит использовать, если не понимаете принцип его работы.
Подробнее про работу ВПР с интервальным просмотром, равным 1 или ИСТИНА можно ознакомиться в статье ВПР и интервальный просмотр(range_lookup)
- Таблица всегда должна начинаться с того столбца, в котором ищем Искомое_значение. Т.е. ВПР не умеет искать значение во втором столбце таблицы, а значение возвращать из первого. В лучшем случае ничего найдено не будет и получим ошибку #Н/Д(#N/A), а в худшем результат будет совсем не тот, который должен быть
- аргумент Таблица должен быть “закреплен”, т.е. содержать знаки доллара($) перед названием столбцов и перед номерами строк(Лист1!$A$1:$C$4). Это и есть закрепление(если точнее, то это называется абсолютной ссылкой на диапазон). Как это делается. Выделяете текст ссылки и жмете клавишу F4 до тех пор, пока не увидите, что и перед обозначением имени столбца и перед номером строки не появились доллары. Если этого не сделать, то при копировании формулы из одной ячейки в остальные аргумент Таблица будет “съезжать” и результат может быть совсем не таким, какой ожидался(в лучшем случае получите ошибку #Н/Д(#N/A)
- номер_столбца не должен превышать общее кол-во столбцов в аргументе таблица, а сама Таблица соответственно должна содержать столбцы от первого(в котором ищем) до последнего(из которого необходимо возвращать значения). В примере указана Лист1!$A$1:$C$4 – всего 3 столбца(A, B, C). Значит не получится вернуть значение из столбца D(4), т.к. в таблице только три столбца. Т.е. если мы запишем формулу так: =ВПР($A2;Лист1!$A$1:$C$4;4;0) – мы получим ошибку #ССЫЛКА!(#REF!).
Если аргументом Таблица указан диапазон $B$1:$C$4 и необходимо вернуть данные из столбца С, то правильно будет указать номер столбца 2. Т.к. аргумент Таблица($B$1:$C$4) содержит только два столбца – В и С. Если же попытаться указать номер столбца 3(каким по счету он является на листе), то получим ошибку #ССЫЛКА!(#REF!), т.к. третьего столбца в указанном диапазоне просто нет.
Многие наверняка заметили, что на картинке у меня попутаны отделы для ФИО(в обеих таблицах ФИО относятся к разным отделам). Это не ошибка записи. В прилагаемом к статье примере показано, как можно одной формулой подставить и отделы и даты, не меняя вручную аргумент Номер_столбца: =ВПР($A2;Лист1!$A$1:$C$4;СТОЛБЕЦ();0). Такой подход сработает, если в обеих таблицах одинаковый порядок столбцов.
Как избежать ошибки #Н/Д(#N/A) в ВПР?
Еще частая проблема – многие не хотят видеть #Н/Д результатом, если совпадение не найдено. Это можно обойти при помощи специальных функций.
Для пользователей Excel 2003 и старше:
=ЕСЛИ(ЕНД(ВПР($A2;Лист1!$A$1:$C$4;3;0));””;ВПР($A2;Лист1!$A$1:$C$4;3;0))
=IF(ISNA(VLOOKUP($A2,Лист1!$A$1:$C$4,3,0)),””,VLOOKUP($A2,Лист1!$A$1:$C$4,3,0))
Теперь если ВПР не найдет совпадения, то ячейка будет пустой.
А пользователям версий Excel 2007 и выше будет удобнее использовать функцию
ЕСЛИОШИБКА(IFERROR)
:
=ЕСЛИОШИБКА(ВПР($A2;Лист1!$A$1:$C$4;3;0);””)
=IFERROR(VLOOKUP($A2,Лист1!$A$1:$C$4,3,0);””)
Подробнее про различие между использованием ЕСЛИ(ЕНД и ЕСЛИОШИБКА я разбирал в статье: Как в ячейке с формулой вместо ошибки показать 0
Но я бы не рекомендовал использовать
ЕСЛИОШИБКА(IFERROR)
, не убедившись, что ошибки появляются только для реально отсутствующих значений. Иногда ВПР может вернуть #Н/Д и в других ситуациях:
- искомое значение состоит более чем из 255 символов(решение этой проблемы приведено ниже в этой статье: Работа с критериями длиннее 255 символов)
- искомое значение является числом с большим кол-вом знаков после запятой. Excel не может правильно воспринимать такие числа и в итоге ВПР может вернуть ошибку. Правильным решением здесь будет округлить искомое значение хотя бы до 4-х или 5-ти знаков после запятой(конечно, если это допустимо):
=ВПР(ОКРУГЛ($A2;5);Лист1!$A$1:$C$4;3;0)
=VLOOKUP(ROUND($A2,2),Лист1!$A$1:$C$4,3,0) - искомое значение содержит специальные или непечатаемые символы.
В этом случае придется либо избавиться от непечатаемых символов в искомом аргументе:
=ВПР(ПЕЧСИМВ($A2);Лист1!$A$1:$C$4;3;0)
=VLOOKUP(CLEAN($A2),Лист1!$A$1:$C$4,3,0)
либо добавить перед всеми специальными символами(такими как звездочка или вопр.знак) знак тильды(~), чтобы сделать эти знаки просто знаками, а не знаками специального значения(так же работа со специальными(служебными) символами описывалась в статье: Как заменить/удалить/найти звездочку). Добавить символ перед знаком той же тильды можно при помощи функции ПОДСТАВИТЬ(SUBSTITUTE):
=ВПР(ПОДСТАВИТЬ($A2;”~”;”~~”);Лист1!$A$1:$C$4;3;0)
=VLOOKUP(SUBSTITUTE(A2,”~”,”~~”),Лист1!$A$1:$C$4,3,0)
Если необходимо добавить тильду сразу перед несколькими знаками, то делает это обычно так(на примере подстановки одновременно для тильды и звездочки):
=ВПР(ПОДСТАВИТЬ(ПОДСТАВИТЬ($A2;”~”;”~~”);”*”;”~*”);Лист1!$A$1:$C$4;3;0)
=VLOOKUP(SUBSTITUTE(SUBSTITUTE(A2,”~”,”~~”),”*”,”~*”),Лист1!$A$1:$C$4,3,0)
На самом деле ответ будет коротким – ВПР всегда ищет сверху вниз. Слева направо она не умеет. Но зато слева направо умеет искать её сестра ГПР(HLookup) – Горизонтальный
ПР
осмотр.
ГПР ищет заданное значение(аргумент
искомое_значение
) в первой строке указанного диапазона(аргумент
таблица
) и возвращает для него значение из строки таблицы, указанной аргументом номер_строки. Поиск значения всегда происходит слева направо и заканчивается сразу, как только значение найдено. Если значение не найдено, функция возвращает значение ошибки
#Н/Д(#N/A)
.
Если надо найти значение “Иванов” в строке 2 и вернуть значение из строки 5 в таблице
A2:H10
, то формула будет выглядеть так:
=ГПР(“Иванов”;$A$2:$H$10;5;0)
=HLOOKUP(“Иванов”,$A$2:$H$10,5,0)
Все правила и синтаксис функции точно такие же, как у ВПР:
-в искомом значении можно применять символы астерикса(*) и вопр.знака(?) – “Иванов*”;
-таблица должна быть закреплена –
$A$2:$H$10
;
-интервальный просмотр работает по тому же принципу(0 или ЛОЖЬ точный просмотр слева-направо, 1 или ИСТИНА – интервальный).
Общий принцип работы
ПОИСКПОЗ(MATCH)
очень похож на ВПР – функция ищет заданное значение в массиве (в столбце или строке) и возвращает его позицию(порядковый номер в заданном массиве). Т.е. ищет
Искомое_значение
в аргументе
Просматриваемый_массив
и в качестве результата выдает номер позиции найденного значения в
Просматриваемом_массиве
. Именно номер позиции, а не само значение. Если бы мы хотели применить её для таблицы выше, то она была бы такой:
=ПОИСКПОЗ($A2;Лист1!$A$1:$A$4;0)
=MATCH($A2,Лист1!$A$1:$A$4,0)
- Искомое_значение($A2) – непосредственно значение или ссылка на ячейку с искомым значением. Если опираться на пример выше – то это ФИО. Здесь все ровно так же, как и с ВПР. Так же допустимы символы подстановки * и ? и ровно в таком же исполнении.
- Просматриваемый_массив(Лист1!$A$1:$A$4) – указывается ссылка на столбец, в котором необходимо найти искомое значение. В отличии от той же ВПР, где указывается целая таблица, это должен быть именно один столбец, в котором мы собираемся искать Искомое_значение. Если попытаться указать более одного столбца, то функция вернет ошибку.Справедливости ради надо отметить, что можно указать либо столбец, либо строку
- Тип_сопоставления(0) – то же самое, что и Интервальный_просмотр в ВПР. С теми же особенностями. Отличается разве что возможностью поиска наименьшего от искомого или наибольшего.
С основным разобрались. Но ведь нам надо вернуть не номер позиции, а само значение. Значит ПОИСКПОЗ в чистом виде нам не подходит. По крайней мере одна, сама по себе. Но если её использовать вместе с функцией ИНДЕКС(INDEX)(которая возвращает из указанного диапазона значение на пересечении заданных строки и столбца) – то это то, что нам нужно и даже больше.
=ИНДЕКС(Лист1!$A$1:$C$4;ПОИСКПОЗ($A2;Лист1!$A$1:$A$4;0);2)
Такая формула результатом вернет то же, что и ВПР.
Аргументы функции ИНДЕКС
Массив(Лист1!$A$2:$C$4). В качестве этого аргумента мы указываем диапазон, из которого хотим получить значения. Может быть как один столбец, так и несколько. В случае, если столбец один, то последний аргумент функции указывать не обязательно или он всегда будет равен 1(столбец-то всего один). К слову – данный аргумент может совершенно не совпадать с тем, который мы указываем в аргументе Просматриваемый_массив функции ПОИСКПОЗ.
Далее идут Номер_строки и Номер_столбца. Именно в качестве Номера_строки мы и подставляем ПОИСКПОЗ, которая возвращает нам номер позиции в массиве. На этом все и строится. ИНДЕКС возвращает значение из Массива, которое находится в указанной строке(Номер_строки) Массива и указанном столбце(Номер_столбца), если столбцов более одного. Важно знать, что в данной связке кол-во строк в аргументе Массив функции ИНДЕКС и кол-во строк в аргументе Просматриваемый_массив функции ПОИСКПОЗ должно совпадать. И начинаться с одной и той же строки. Это в обычных случаях, если не преследуются иные цели.
Так же как и в случае с ВПР, ИНДЕКС в случае не нахождения искомого значения возвращает #Н/Д. И обойти подобные ошибки можно так же:
Для всех версий Excel(включая 2003 и раньше):
=ЕСЛИ(ЕНД(ПОИСКПОЗ($A2;Лист1!$A$1:$A$4;0));””;ИНДЕКС(Лист1!$A$1:$C$4;ПОИСКПОЗ($A2;Лист1!$A$2:$A$4;0);2))
Для версий 2007 и выше:
=ЕСЛИОШИБКА(ИНДЕКС(Лист1!$A$1:$C$4;ПОИСКПОЗ($A2;Лист1!$A$1:$A$4;0);2);””)
Есть у ИНДЕКС-ПОИСКПОЗ и еще одно преимущество перед ВПР. Дело в том, что ВПР не может искать значения, длина строки которых содержит более 255 символов. Это случается редко, но случается. Можно, конечно, обмануть ВПР и урезать критерий:
=ВПР(ПСТР($A2;1;255);ПСТР(Лист1!$A$1:$C$4;1;255);3;0)
но это формула массива. Да и к тому же далеко не всегда такая формула вернет нужный результат. Если первые 255 символов идентичны первым 255 символам в таблице, а дальше знаки различаются – формула этого уже не увидит. Да и возвращает формула исключительно текстовые значения, что в случаях, когда возвращаться должны числа, не очень удобно.
Поэтому лучше использовать такую хитрую формулу:
=ИНДЕКС(Лист1!$A$1:$C$4;СУММПРОИЗВ(ПОИСКПОЗ(ИСТИНА;Лист1!$A$1:$A$4=$A2;0));2)
Здесь я в формулах использовал одинаковые диапазоны для удобочитаемости, но в примере для скачивания они различаются от указанных здесь.
Сама формула построена на возможности функции СУММПРОИЗВ преобразовывать в массивные вычисления некоторых функций внутри неё. В данном случае ПОИСКПОЗ ищет позицию строки, в которой критерий равен значению в строке. Подстановочные символы здесь применить уже не получится.
Ну и все же я рекомендовал бы Вам прочитать подробнее про данные функции в справке.
В прилагаемом к статье примере Вы найдете примеры использования всех описанных случаев и пример того, почему ИНДЕКС и ПОИСКПОЗ порой предпочтительнее ВПР.
Скачать пример
Tips_All_VLookUp.xls (26,0 KiB, 17 475 скачиваний)
Так же см.:
ВПР и интервальный просмотр(range_lookup)
ВПР по двум и более критериям
ВПР с возвратом всех значений
ВПР с поиском по нескольким листам
ВПР_МН
ВПР_ВСЕ_КНИГИ
Как заменить/удалить/найти звездочку?
Статья помогла? Поделись ссылкой с друзьями!
Видеоуроки
Поиск по меткам
Access
apple watch
Multex
Power Query и Power BI
VBA управление кодами
Бесплатные надстройки
Дата и время
Записки
ИП
Надстройки
Печать
Политика Конфиденциальности
Почта
Программы
Работа с приложениями
Разработка приложений
Росстат
Тренинги и вебинары
Финансовые
Форматирование
Функции Excel
акции MulTEx
ссылки
статистика
Найти и перенести данные в 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-office.ru
Перенос данных из одной таблицы 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 Спасибо за Вашу Ваш форум повторами, несовпадении должны оставаться
exceltable.com
Перенос данных таблицы через функцию ВПР
не вариант если что то другое. которой прописываются партии еще одну и вставим ФОРМАТЫ с сделать больше ячеек в Word”. Этот КНИГИ1 ЛИСТА1 файл можно только совместимости двух книг Книги1 данные должны: Здравствуйте, подскажи пожалуйста, сводной таблицы, что файла-экспорта.
помощь. Честно признаться, но очень нужна пустые строки. Если в таблице более Был бы файл
Синтаксис ВПР
заказанных товаров (она назовем ее ПРИМЕР. помощью специальной вставки. для найденной информации, способ удобен тем,TheArk через файлообменник, чего по каждому поставщику.
вставляться в Январь
- возможно ли технически очень легко делаетсяА также не
- с функцией суммпроиз Ваша помощь, а Вам не трудно, 150 позиций и ответ мог быть подсвечена зеленым). СправаПримечание. Создавать новые файлыТеперь попробуем выбрать пункт
- установить такие формулы что нужная информация: Добрый день, друзья.
- этот пльзователь, ИМХО, Иначе приходиться копировать Книги2 , из прописать формулу для
формулой “ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ”. В
Как перемещать данные с помощью ВПР?
забудьте “накапать” руководству пока не работала времени на поиск поделитесь опытом - они могут различаться точнее прайс, где значатся Excel можно даже БЕЗ РАМКИ. Получили в бланке. появляется в специальных Подскажите как перенести делать не будет все данные из февраля в февраль того, чтоб данные
дальнейшем, Вам придется на своих IT, )). не понятен здесь подобного решения как в этом
друг от друга.УКД цены каждого товара в разных папках. полную таблицу, ноТеперь достаточно ввести ячейках и не значения из общейlight26
1 книги в и т.д. Т.е их одной Книги только лишь контролировать которые занимаются прикладной знак двойного минуса. уже нет. (если случае можно организовать Как можно это
: китин, упс… Я (подсвечен голубым). Нам Программа будет автоматически только без выделенных в одну ячейку нужно ее выискивать свободной таблицы, в
: другую, ну сами при копировании листов Excel -далее Книга
вновь появившиеся коды. частью программного обеспечения Если не трудно,
такая тема уже выгрузку? Заранее спасибо! осуществить? ж прикрепляла файл( нужно перенести данные
искать указанную книгу, границ.
название фирмы и в таблице по отдельные на другихHugo понимаете, очень, очень, формулы то не 1, автоматически переносилисьПример выкладываю. в Вашей организации объясните его назначение. есть, просто ткнитеЮрий МBema Попробую еще раз. по ценам из независимо от того,Полезный совет! Чтобы перенести вся нужная информация строке, т.д. закладках. К примеру, я так мыслю,
exceltable.com
Перенос значений из одной общей таблицы в другую по признаку (Формулы/Formulas)
очень много раз меняются…. в другую ТаблицуТак можете сделать – свой “хлеб”rusanovava носом!)). Задача в: swetik, в #5: swetik, добрый иpabchek правой таблицы в в какой папке
формат вместе с выберется из строки
Итак, у нас в общей сводной что ему нужно умноженных на 100Наверно я не смогу другой Книги -Далее
на одном листе они также должны: dude, спасибо за принципе проста: из
не Вам. А что: Сводная пойдет? При левую, чтобы подсчитать и на каком
размерами столбцов нужно
и появится в есть огромная таблица
таблицы много значений что-то вроде этого. поставщиков :-))) доходчиво объяснить :-( Книга 2
хоть сколько угодно отрабатывать! Вашу помощь. про таблицы, выгруженной изцитата правила говорят о
желании можно сделать стоимость каждой партии. диске компьютера она перед копированием выделить отдельных ячейках (в с множеством строк с признаком А, Только доработать нужноЯ вообще задумываюсь Или это нереальноСложность в том, сводных таблиц. Единственно,Viktorya R листы учту 1С необходимо заполнить- это полная
файле-примере?Понимаете, файл-пример и автообновляемый диапазон и
Вручную это делать находится. не диапазон исходной одном месте), не и столбцов. Нам мне бы хотелось немного. Или много над вопросом создания сделать… автоматически нужно переносить что в формуле: 1. реальный файл. Вашу формулу
рабочую таблицу. Пример
копия рабочий файл- это кнопочку для обновления)) долго, поэтому воспользуемся
Скопируем в книгу ПРИМЕР таблицы, а столбцы нужно двигать большую нужно выбрать конкретные увидеть все их
200?'200px':''+(this.scrollHeight+5)+'px');">Sub Перенос()
для этого делаДа и формулу не строку,столбец, а “ПОЛУЧИТЬ.ДАННЫЕ….” необходимо будет не могу показать, честно вообще не во вкладке. Проблемасообщения. Вы вообще две большие разницы.
сделал диапазон, перезалил функцией Вертикального Просмотра. таблицу с помощью
excelworld.ru
Перенос данных из одной таблицы в другую
целиком (в данном таблицу, т.д. данные из нескольких на другой закладке.’ хоть какой то в моем случае отдельные ячейки. указать номер сводной так как информация поняла ))).если поможете моя заключается в в курсе, что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_
planetaexcel.ru
Автоматический перенос данных из одной таблицы в другие (Формулы/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
excelworld.ru
Перенос данных из одной таблицы в другую при разных условиях
НЕ ПЕРЕНОС! ладно, тогда не было протянуть формулуПолучили новую формулу, которая таблиц. Это удобно с множеством параметров. устанавливаем формулы для такая функция?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 а не приблизительные. очень полезная функция, у нас задан при копировании. Рассмотрим написать словами, а: в смысле не вверх и по: Ну и зачем
planetaexcel.ru
автоматический перенос данных из Книги1 в другую Книг2 (перенос данных из одной таблицы Книги1 в другую таблицу Книг)
не совсем ясенТ.е. например для составлении сводной таблицы. в рукопашную, то служба имеется, но относятся к п.novichok уникальны в таблице любого листа. ПослеВидим, что из правой позволяющая подтягивать данные
формулой, поэтому при пример. можно вызвать эту работает? все вроде
результатам проверки ответа тогда его в – много тонкостей…”ячейка Е19 Книги Вернее умение работать
Ваш лист “код” загружать их “такими 5.1.1. расчетной таблицы)?, здравствуйте. А зачем
источника, т.к. всегда ввода формулы нужно таблицы в левую из одной таблицы
удалении значений изИмеем таблицу с показателями функцию на закладке работает
переносим текущую строку ЭТОЙ теме задавать?!Может быть лучше 1 должна автоматически с источником данных не дает никакой мелочами” (как быМотя
так много таблиц,
будут переноситься только нажать Контрл Шифт подтянулась цена гречки.
в другую по предыдущих столбцов, он
по наличию макарон «Формулы» в разделе
TheArk
в первую пустуюЧитайте правила (особенно сделать так - вставать в ячейку сводной таблицы. Далее
информации для макроса. мне они сказали: И это нужно представляете сколько их
первое или последнее Ентер вместо обычного
Протягиваем формулу вниз и заданным критериям. Это
автоматически обнуляется. определенных марок на «Ссылки и массивы».: Если Вас не ячейку первого столбца п.4.) и создавайте наладили этими формулами С11 Книги 2″ останется вставить формулуViktorya R) не хочу, делать каждый месяц?! будет через пару найденное значение
Ентер и растянуть визуально проверяем некоторые достаточно «умная» команда,Перенесем данные по остатку складе магазина. Наглядно В функции «ИНДЕКС» затруднит, можно эту соотв. файла. свою.
один лист. При делаем так: “ПОЛУЧИТЬ.ДАННЫЕ…” в ячейку: могу отправить в проще самой применитьДа и еще месяцев (по однойПро оптимизацию этого формулу вниз и
товары, чтобы понять, потому что принцип макарон каждой марки видно, сколько килограмм выбираем функцию «массив; формулу в мойВ конце результатыГость появлении нового месяцав C11 книги2
E12, связать ее личку формулы для облегчения к нужному часу,
таблице на день)? метода: 1- при вбок на сколько что все сделали ее работы складывается с января на
было в начале номер_строки;номер_столбца». файл вставить, так сохраняем.: Добрый времени суток, скопировали этот лист, вводим “=”, затем с необходимыми условиямисопоставление наименований затруднено своего труда, поскольку т.к речь идет
Предлагаю 2 варианта: совпадении значений и нужно. Но без правильно. из нескольких действий: февраль. Это делается месяца, сколько докуплено
Итак, формула такая. =ИНДЕКС($A$2:$K$3;ПОИСКПОЗ($A$7;A2:A3;0);10) будет проще понятьИли, если форматы/заливка у меня такой затем через Ctrl+H переходим на книгу1, и протянуть по еще тем, что
как уже писала, о зарплате1. Сводная - переносе данных, выход фанатизма, а то
Скачать пример переноса таблицы сканирование выбранного массива, буквально в пару и продано, аПояснение формулы. что к чему. не нужны, а вопрос: заменили в формулах помечаем Е19, Enter. остальным ячейкам, исключая
в другом месяце таких отчетов сРАБОЧИХ
на листе 1. из внутреннего цикла; будет тормозить через ВПР выбор нужной ячейки
нажатий. также остаток на
$A$2:$K$3 – этоSergo99
данных много, тоесть три книги
ссылку на прежнийДля других ячеек
итоговые строки. В может появится абсолютно постоянно меняющимися условиями!2. Формульный на
2-если списки отсортировать,=ЕСЛИОШИБКА(ИНДЕКС(БД!A$1:A$99;НАИМЕНЬШИЙ(ЕСЛИ(БД!$D$2:$D$99=ПСТР(ЯЧЕЙКА(“имяфайла”;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 “Вставка” нажимаете левую воспользоваться командой ИНДЕКС
excelworld.ru
Перенести значения по признаку из одной таблицы в другую.
работы со строками ячейку, показывающую количество жмем КОПИРОВАТЬ. этой фирмы (у форума копируем данные по б ничего не2 столбец желтые и в КнигеЕще надо помнить, можно легко учесть. имен диапазонам, то за что же столбцам.: Я даже не крайнюю кнопку “Сводная и ПОИСКПОЗ. Овладев существует горизонтальный просмотр макарон марки АВ свободной ячейке снова нас – это- Создайте свою трём массивам (для оставалось? кто может суммы
2, то формулы что для составленияViktorya R получаю вот это: они получают зРяплату?!
novichok знаю и не таблица”, затем “ОК”. этими двумя функциями – ГПР. на конец месяца вызываем меню правой первый столбец А). тему согласно п.5q
каждого свой счётчик что подсказать
3 столбец синие не меняются и
сводной таблицы в: Книга 1 -
={СУММ(‘[книга1.xlsx]АУП’!$C$3:$AN$175*(‘[книга1.xlsx]АУП’!$B$3:$B$175=E$4)*(‘[книга1.xlsx]АУП’!$C$3:$AN$3=$A11))} , неПоскольку документ чрезвычайно: понимаю как это
Формируется новый лист вы сможете намногоАргументы функции следующие: (в нашем случае кнопкой и нажимаем0 (ноль) – Правил форума
строк)Hugo суммы каждый месяц придется диапазоне A1 : исходный вариант 1с. работает, видимо так важен, служба ITManyasha делать. со сводной таблицей.
больше реализовать сложныхискомое значение – то, это ячейка F2
СПЕЦИАЛЬНАЯ ВСТАВКА. это значит, искатьЭта тема закрыта
5. выгружаем массивы
excelworld.ru
: Формула ничего ПЕРЕНЕСТИ
Самый быстрый ВПР
Если в ваших таблицах всего лишь несколько десятков строк, то, скорее всего, эта статья не будет для вас актуальной. На таких небольших объемах данных любой способ будет работать достаточно шустро, чтобы вы этого не замечали. Если же число строк в ваших списках измеряется тысячами, да и самих таблиц не одна-две, то время мучительного ожидания на пересчете формул в Excel может доходить до нескольких минут.
В этом случае, правильный выбор функции, применяемой для связывания таблиц, играет решающую роль — разница в производительности между ними, как мы увидим далее, может составлять более 20 раз!
Когда я писал свою первую книжку пять лет назад, то уже делал сравнительный скоростной тест различных способов поиска и подстановки данных функциями ВПР, ИНДЕКС+ПОИСКПОЗ, СУММЕСЛИ и др. С тех пор сменилось три версии Office, появились надстройки Power Query и Power Pivot, кардинально изменившие весь процесс работы с данными. А в прошлом году ещё и обновился вычислительный движок Excel, получив поддержку динамических массивов и новые функции ПРОСМОТРХ, ФИЛЬТР и т.п.
Так что пришла пора снова взяться за секундомер и выяснить — кто же самый быстрый. Ну и, заодно, проверить — какие способы поиска и подстановки данных в Excel вы знаете 🙂
Подопытный кролик
Тест будем проводить на следующем примере:
Это книга Excel с одним листом, где расположены две таблицы: отгрузки (500 000 строк) и прайс-лист (600 строк). Наша задача — подставить цены из прайс-листа в таблицу отгрузок. Для каждого способа будем вводить формулу в ячейку С2 и копировать вниз на весь столбец, замеряя время, которое потребуется Excel, чтобы просчитать весь столбец из полумиллиона ячеек. Полученные значения, безусловно, зависят от множества факторов (поколение процессора, объем оперативной памяти, текущая загрузка системы, версия Office и т.д.), но нам важны не конкретные цифры, а, скорее, их сравнение друг с другом. Важно понимать прожорливость каждого способа и их ограничения.
Способ 1. ВПР
Сначала — классика 🙂 Легендарная функция вертикального просмотра — ВПР (VLOOKUP) , которая приходит в голову первой в подобных ситуациях:
- B2 — искомое значение, т.е. название товара, который мы хотим найти в прайс-листе
- $G$2:$H$600 — закреплённая знаками доллара (чтобы не сползала при копировании формулы вниз) абсолютная ссылка на прайс
- 2 — номер столбца в прайс-листе, откуда мы хотим взять цену
- 0 или ЛОЖЬ — переключение в режим поиска точного соответствия, когда любое некорректное название товара (например, ФОНЕРА) в столбце B в таблице отгрузок приведёт к появлению ошибки #Н/Д как результата работы функции.
Время вычисления = 4,3 сек.
Способ 2. ВПР с выделением столбцов целиком
Многие пользователи, применяя ВПР, во втором аргументе этой функции, где нужно задать поисковую таблицу (прайс), выделяют не ограниченный диапазон ( $G$2:$H$600 ), а сразу столбцы G:H целиком. Это проще, быстрее, позволяет не думать про F4 и то, что завтра прайс-лист может быть на несколько строк больше. Формула в этом случае выглядит тоже компактнее:
В старых версиях Excel такое выделение не сильно влияло на скорость вычислений, но сейчас (неожиданно для меня, признаюсь) результат получился в разы хуже предыдущего.
Время вычисления = 14,5 сек.
Способ 3. ИНДЕКС и ПОИСКПОЗ
Следующей после ВПР ступенью эволюции для многих пользователей Microsoft Excel обычно является переход на использование связки функций ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH) . Выглядит эта формула так:
Функция ИНДЕКС извлекает из заданного в первом аргументе диапазона (столбца $H$2:$H$600 с ценами в прайс-листе) содержимое ячейки с заданным номером. А номер этот, в свою очередь, определяется функцией ПОИСКПОЗ, у которой три аргумента:
- Что нужно найти — название товара из B2
- Где мы это ищем — столбец с названиями товаров в прайсе ( $G$2:$G$600 )
- Режим поиска: 0 — точный, 1 или -1 — приблизительный с округлением в меньшую или большую сторону, соответственно.
Формула выходит чуть сложнее, но, при этом имеет несколько ощутимых преимуществ перед классической ВПР, а именно:
- Не нужно отсчитывать номер столбца (как в третьем аргументе ВПР).
- Можно извлекать данные, которые находятся левее столбца, где просходит поиск.
По скорости, однако же, этот способ проигрывает ВПР почти в два раза:
Время вычисления = 7,8 сек.
Если же, вдобавок, полениться и выделять не ограниченные диапазоны, а столбцы целиком:
. то результат получается совсем печальный:
Время вычисления = 28,5 сек.
28 секунд, Карл! В 6 раз медленнее ВПР!
Способ 4. СУММЕСЛИ
Если нужно найти не текстовые, а именно числовые данные (как в нашем случае — цену), то вместо ВПР вполне можно использовать функцию СУММЕСЛИ (SUMIF) . Изначально она задумывалась как инструмент для выборочного суммирования данных по условию (найди и сложи мне все продажи кабелей, например), но можно заставить её искать нужный нам товар и в прайс-листе. Если грузы в нём не повторяются, то суммировать будет не с чем и эта функция просто выведет искомое значение:
- Первый аргумент СУММЕСЛИ — это диапазон проверяемых ячеек, т.е. названия товаров в прайсе ( $G$2:$G$600 ).
- Второй аргумент ( B2 ) — что мы ищем.
- Третий аргумент — диапазон ячеек с ценами $H$2:$H$600 , числа из которых мы хотим просуммировать, если в соседних ячейках проверяемого диапазона есть искомое значение.
Очевидным минусом такого подхода является то, что он работает только с числами. Также этот способ не удобен, если прайс-лист находится в отдельном файле — придется всё время держать его открытым, т.к. функция СУММЕСЛИ не умеет брать данные из закрытых книг, в отличие от ВПР, для которой это не проблема.
В плюсы же можно записать удобство при поиске сразу по нескольким столбцам — для этого идеально подходит более продвинутая версия этой функции — СУММЕСЛИМН (SUMIFS) . Скорость вычислений же, при этом, весьма посредственная:
Время вычисления = 12,8 сек.
При выделении столбцов целиком, т.е. использовании формулы вида =СУММЕСЛИ( G:G ; B2 ; H:H ) всё ещё хуже:
Время вычисления = 41,7 сек.
Это самый плохой результат в нашем тесте.
Способ 5. СУММПРОИЗВ
Этот подход сейчас встречается не часто, но всё ещё достаточно регулярно. Обычно так любят извращаться пользователи старой школы, ещё хорошо помнящие те времена, когда в Excel было всего 255 столбцов и 56 цветов 🙂
Суть этого метода заключается в использовании функции СУММПРОИЗВ (SUMPRODUCT) , изначально предназначенной для поэлементного перемножения нескольких диапазонов с последующим суммированием полученных произведений. В нашем случае, вместо одного из массивов будет выступать условие, а вторым будут цены:
Выражение ($G$2:$G$600=B2) , по сути, проверяет каждое название груза в прайс-листе на предмет соответствия искомому значению (ФАНЕРА ПР). Результатом каждого сравнения будет логическое значение ИСТИНА (TRUE ) или ЛОЖЬ (FALSE) , что в Excel интерпретируется как 1 и 0, соответственно. Последующее умножение этих нулей и единиц на цены оставит в живых цену только того товара, который нам, в данном случае, и нужен.
Эта формула является, по сути, формулой массива, но не требует нажатия обычного для них сочетания клавиш Ctrl + Shift + Enter , т.к. функция СУММПРОИЗВ поддерживает массивы уже сама по себе. Возможно, по этой же причине (формулы массива всегда медленнее, чем обычные) такой скорость пересчёта такой формулы — не очень:
Время вычисления = 11,8 сек.
- Совместимость с любыми, самыми древними версиями Excel.
- Возможность задавать сложные условия (и несколько)
- Способность этой формулы работать с данными из закрытых файлов, если добавить перед ней двойное бинарное отрицание (два подряд знака «минус»). СУММЕСЛИМН таким похвастаться не может.
Способ 6. ПРОСМОТР
Ещё один относительно экзотический способ поиска и подстановки данных, наравне с ВПР — это использование функции ПРОСМОТР (LOOKUP) . Только не перепутайте её с новой, буквально, на днях появившейся функцией ПРОСМОТРХ (XLOOKUP) — про неё мы поговорим дальше особо. Функция ПРОСМОТР существовала в Excel начиная с самых ранних версий и тоже вполне может решить нашу задачу:
- B2 — название груза, которое мы ищем
- $G$2:$G$600 — одномерный диапазон-вектор (столбец или строка), где мы ищем совпадение
- $H$2:$H$600 — такого же размера диапазон, откуда нужно вернуть найденный результат (цену)
На первый взгляд всё выглядит очень удобно и логично, но всю картину портят два неочевидных момента:
- Эта функция требует обязательной сортировки прайс-листа по возрастанию (алфавиту) и без этого не работает.
- Если в таблице отгрузок искомое значение будет написано с опечаткой (например, АГ Е ДОЛ вместо АГИДОЛ), то функция ПРОСМОТР выдаст не ошибку #Н/Д, а цену для ближайшего предыдущего товара:
При работе с неидеальными данными в реальном мире это гарантированно создаст проблемы, как вы понимаете.
Скорость же вычислений у функции ПРОСМОТР (LOOKUP) весьма приличная:
Время вычисления = 7,6 сек.
Способ 7. Новая функция ПРОСМОТРХ
Эта функция пришла с одним из недавних обновлений пока только пользователям Office 365 и пока отсутствует во всех остальных версиях (Excel 2010, 2013, 2016, 2019). По сравнению с классической ВПР у этой функции есть масса преимуществ (упрощенный синтаксис, возможность искать не только сверху-вниз, возможность сразу задать значение вместо #Н/Д и т.д.) Формула для решения нашей задачи будет выглядеть в этом случае так:
Если не брать в расчёт необязательные 4,5,6 аргументы, то синтаксис этой функции полностью совпадает с её предшественником — функцией ПРОСМОТР (LOOKUP) . Скорость вычислений при тестировании на наши 500000 строк тоже оказалась аналогичной:
Время вычисления = 7,6 сек.
Почти в два раза медленнее, чем у ВПР, вместо которой Microsoft предлагает теперь использовать ПРОСМОТРХ. Жаль.
И, опять же, если полениться и выделить диапазоны в прайс-листе целыми столбцами:
. то скорость падает до совершенно неприличных уже значений:
Время вычисления = 28,3 сек.
А если на динамических массивах?
Прошлогоднее (осень 2019) обновление вычислительного движка Microsoft Excel добавило ему поддержку динамических массивов (Dynamic Arrays), о которых я уже писал. Это принципиально новый подход к работе с данными, который можно использовать почти с любыми классическими функциями Excel. На примере ВПР это будет выглядеть так:
Разница с классическим вариантом в том, что первым аргументом ВПР здесь выступает не одно искомое значение (а формулу потом нужно копировать вниз на остальные строки), а сразу весь массив из полумиллиона грузов B2:B500000, цены для которых мы хотим найти. Формула при этом сама распространяется вниз, занимая требуемое количество ячеек.
Скорость пересчета в таком варианте меня, откровенно говоря, ошеломила — пауза между нажатием на Enter после ввода формулы и получением результатов почти отсутствовала.
Время вычисления = 1 сек.
Что интересно, и новая ПРОСМОТРХ, и старая ПРОСМОТР, и связка ИНДЕКС+ПОИСКПОЗ в таком режиме тоже были очень быстрыми — время вычислений не больше 1 секунды! Фантастика.
А вот олдскульные подходы на основе СУММПРОИЗВ и СУММЕСЛИ(МН) с динамическими массивами работать отказались 🙁
Что с умными таблицами?
Обрадовавшись фантастическим результатам, полученным на динамических массивах, я решил вдогон попробовать протестировать разницу в скорости при работе с обычными и «умными» таблицами. Я имею ввиду те самые «красивые таблицы», в которые вы можете преобразовать ваш диапазон с помощью команды Форматировать как таблицу на вкладке Главная (Home — Format as Table) или с помощью сочетания клавиш Ctrl + T .
Если предварительно превратить наши отгрузки и прайс в «умные» (по умолчанию они получат имена Таблица1 и Таблица2, соответственно), то формула с той же ВПР будет выглядеть как:
- [@Груз] — ссылка на ячейку B2, означающая, в данном случае, что нужно взять значение из той же строки из столбца Груз текущей умной таблицы.
- Таблица2 — ссылка на прайс-лист
Жирным плюсом такого подхода будет возможность легко добавлять данные в наши таблицы в будущем. При дописывании новых строк в отгрузки или к прайс-листу, наши «умные» таблицы будут растягиваться автоматически.
Скорость же, как выяснилось, тоже вырастает очень значительно и примерно равна скорости работы на динамических массивах:
Время вычисления = 1 сек.
У меня есть подозрение, что дело тут не в самих «умных» таблицах, а всё в том же обновлении вычислительного движка, т.к. на старых версиях Excel такого прироста в скорости на умных таблицах я не помню.
Бонус. Запрос Power Query
Замерять, так замерять! Давайте, для полноты картины, сравним наши перечисленные способы еще и с запросом Power Query, который тоже может решить нашу задачу. Кто-то скажет, что некорректно сравнивать пересчёт формул с механизмом обновления запроса, но мне, откровенно говоря, просто самому было интересно — кто быстрее?
- Превращаем обе наши таблицы в «умные» с помощью команды Форматировать как таблицу на вкладке Главная (Home — Format as Table) или с помощью сочетания клавиш Ctrl + T .
- По очереди загружаем таблицы в Power Query с помощью команды Данные — Из таблицы / диапазона (Data — From Table/Range) .
- После загрузки в Power Query возвращаемся обратно в Excel, оставляя загруженные данные как подключение. Для этого в окне Power Query выбираем Главная — Закрыть и загрузить — Закрыть и загрузить в. — Только создать подключение (Home — Close&Load — Close&Load to. — Only create connection) .
- После того, как обе исходные таблицы будут загружены как подключения, создадим ещё один, третий запрос, который будет объединять их между собой, подставляя цены из прайса в отгрузки. Для этого на вкладке Данные выберем Получить данные / Создать запрос — Объединить запросы — Объединить (Get Data / New Query — Merge queries — Merge) :
В отличие от формул, запросы Power Query не обновляются автоматически «на лету», а требуют щелчка правой кнопкой мыши по таблице (или запросу в правой панели) и выбору команды Обновить (Refresh) . Также можно воспользоваться командой Обновить все (Refresh All) на вкладке Данные (Data) .
Время обновления = 8,2 сек.
Итоговая таблица и выводы
Если вы честно дочитали до этого места, то какие-то выводы, наверное, уже сделали самостоятельно. Если же пропустили все детали и сразу перешли к итогам, то вот вам общая результирующая таблица по скорости всех методов:
Само-собой, у каждого из нас свои предпочтения, задачи и тараканы, но для себя я сформулировал выводы после этого тестирования так:
ВПР с несколькими условиями: 5 примеров.
Очень часто наши требования к поиску данных не ограничиваются одним условием. К примеру, нам нужна выручка по магазину за определенный месяц, количество конкретного товара, проданного определенному покупателю и т.д. Обычными средствами функции ВПР эту задачу решить сложно и даже не всегда возможно. Ведь там предусмотрено использование только одного критерия поиска.
Мы предложим вам несколько вариантов решения проблемы поиска по нескольким условиям.
ВПР по нескольким условиям с использованием дополнительного столбца.
Задачу, рассмотренную в предыдущем примере, можно решить и другим способом – без использования формулы массива. Ведь работа с массивами многим представляется сложной и недоступной для понимания. Дополнительный столбец для поиска по нескольким условиям будет в определенном отношении более простым вариантом.
Итак, необходимо выбрать значение выручки за определенный месяц, год и по нужному магазину. В итоге имеем 3 условия отбора.
Сразу по трем столбцам функция ВПР искать не может. Поэтому нам нужно объединить их в один. И, поскольку поиск производится всегда в крайнем левом (первом) столбце, то нужно добавить его в нашу таблицу тоже слева.
Вставляем перед таблицей с данными дополнительный столбец A. Затем при помощи оператора & объединяем в нем содержимое B,C и D. Записываем в А7
и копируем в находящиеся ниже ячейки.
Формула поиска в D4 будет выглядеть:
В диапазон поиска включаем и наш дополнительный столбец. Критерий поиска – также объединение 3 значений. И извлекаем результат из 5 колонки.
Все работает, однако вид несколько портит дополнительный столбец. В крайнем случае, его можно скрыть, используя контекстное меню по нажатию правой кнопки мыши.
Вид станет приятнее, а на результаты это никак не повлияет.
ВПР по двум условиям при помощи формулы массива.
У нас есть таблица, в которой записана выручка по каждому магазину за день. Мы хотим быстро найти сумму продаж по конкретному магазину за определенный день.
Для этого в верхней части нашего листа запишем критерии поиска: дата и магазин. В ячейке B3 будем выводить сумму выручки.
Формула в B3 выглядит следующим образом:
Обратите внимание на фигурные скобки, которые означают, что это формула массива. То есть наша функция ВПР работает не с отдельными значениями, а разу с массивами данных.
Разберем процесс подробно.
Мы ищем дату, записанную в ячейке B1. Но вот только разыскивать мы ее будем не в нашем исходном диапазоне данных, а в немного видоизмененном. Для этого используем условие
То есть, в том случае, если наименование магазина совпадает с критерием в ячейке B2, мы оставляем исходные значения из нашего диапазона. А если нет – заменяем их на пробелы. И так по каждой строке.
В результате получим вот такой виртуальный массив данных на основе нашей исходной таблицы:
Как видите, строки, в которых ранее был «Магазин 1», заменены на пустые. И теперь искать нужную дату мы будем только среди информации по «Магазин 2». И извлекать значения выручки из третьей колонки.
С такой работой функция ВПР вполне справится.
Такой ход стал возможен путем применения формулы массива. Поэтому обратите особое внимание: круглые скобки в формуле писать руками не нужно! В ячейке B3 вы записываете формулу
И затем нажимаете комбинацию клавиш CTRL+Shift+Enter. При этом Excel поймет, что вы хотите ввести формулу массива и сам подставит скобки.
Таким образом, функция ВПР поиск по двум столбцам производит в 2 этапа: сначала мы очищаем диапазон данных от строк, не соответствующих одному из условий, при помощи функции ЕСЛИ и формулы массива. А затем уже в этой откорректированной информации производим обычный поиск по одному только второму критерию при помощи ВПР.
Чтобы упростить работу в будущем и застраховать себя от возможных ошибок при добавлении новой информации о продажах, мы рекомендуем использовать «умную» таблицу. Она автоматически подстроит свой размер с учетом добавленных строк, и никакие ссылки в формулах не нужно будет менять.
Вот как это будет выглядеть.
ВПР по нескольким критериям с применением массивов — способ 2.
Выше мы уже рассматривали, как при помощи формулы массива можно организовать поиск ВПР с несколькими условиями. Предлагаем еще один способ.
Условия возьмем те же, что и в предыдущем примере.
Формулу в С4 введем такую:
Естественно, не забываем нажать CTRL+Shift+Enter.
Теперь давайте пошагово разберем, как это работает.
Наше задача здесь – также создать дополнительный столбец для работы функции ВПР. Только теперь мы создаем его не на листе рабочей книги Excel, а виртуально.
Как и в предыдущем примере, мы ищем текст из объединенных в одно целое условий поиска.
Далее определяем данные, среди которых будем искать.
Конструкция вида A7:A20&B7:B20&C7:C20;D7:D20 создает 2 элемента. Первый – это объединение колонок A, B и C из исходных данных. Если помните, то же самое мы делали в нашем дополнительном столбце. Второй D7:D20 – это значения, одно из которых нужно в итоге выбрать.
Функция ВЫБОР позволяет из этих элементов создать массив. как раз и означает, что нужно взять сначала первый элемент, затем второй, и объединить их в виртуальную таблицу – массив.
В первой колонке этой виртуальной таблицы мы будем искать, а из второй – извлекать результат.
Таким образом, для работы функции ВПР с несколькими условиями мы вновь используем дополнительный столбец. Только создаем его не реально, а виртуально.
Двойной ВПР при помощи ИНДЕКС + ПОИСКПОЗ
Далее речь у нас пойдет уже не о функции ВПР, но задачу мы будем решать ту же самую. В качестве критерия поиска нам опять нужно использовать несколько условий.
Формула в С4 теперь выглядит так:
И не забываем при вводе нажать CTRL+Shift+Enter! Это формула массива.
Теперь давайте разбираться, как это работает.
Функция ИНДЕКС в нашем случае позволяет извлечь элемент из списка по его порядковому номеру. Список – это диапазон D7:D20, где записаны суммы выручки. А вот порядковый номер, который нужно извлечь, мы определяем при помощи ПОИСКПОЗ.
Синтаксис здесь следующий:
ПОИСКПОЗ(что_ищем; где_ищем; тип_поиска)
Тип поиска ставим 0, то есть точное совпадение. В нашем случае мы будем искать 1. Далее мы определим массив, в котором будем работать.
Выражение (A7:A20=C1)*(B7:B20=C2)*(C7:C20=C3) позволит создать виртуальную таблицу примерно такого вида:
Как видите, первоначально мы последовательно сравниваем каждое значение с нашим критерием отбора. В столбце А у нас записаны месяцы – сравниваем их с месяцем-критерием из ячейки C1. В случае совпадения получаем ИСТИНА, иначе – ЛОЖЬ. Аналогично последовательно проверяем год и название магазина. А затем просто перемножаем значения. Поскольку логические переменные для Excel – это либо 0, либо 1, то произведение их может быть равно 1 только в том случае, если мы имеем по каждой колонке ИСТИНА (то есть,1). Во всех остальных случаях получаем 0.
Убеждаемся, что цифра 1 встречается только единожды.
При помощи ПОИСКПОЗ определяем, на какой позиции она находится. На какой позиции находится 1, на той же позиции находится в массиве и искомая сумма выручки. В нашем случае это 10-я.
Далее при помощи ИНДЕКС извлекаем 10-ю по счету выручку.
Таким образом мы выбрали значение по нескольким условиям без использования функции ВПР.
Достойная замена – функция СУММПРОИЗВ.
У нас есть данные о продажах нескольких менеджеров в различных регионах. Нужно сделать выборку по дате, менеджеру и региону.
Работает как формула массива, хотя по факту таковой не является. В этом заключается замечательное свойство функции СУММПРОИЗВ, о которой мы еще много будем говорить в других статьях.
Последовательно по каждой строке диапазона от 2-й до 27-й она проверяет совпадение каждого соответствующего значения с критерием поиска. Эти результаты перемножаются между собой и в итоге еще умножаются на сумму выручки. Если среди трех условий будет хотя бы одно несовпадение, то итогом будет 0. В случае совпадения сумма выручки трижды умножится на 1.
Затем все эти 27 произведений складываются, и результатом будет выручка нужного менеджера в каком-то регионе за определенную дату.
В качестве бонуса можно продолжить этот пример и рассчитать общую сумму продаж менеджера в определенном регионе.
Для этого из формулы просто уберем сравнение по дате.
Кстати, возможен и другой вариант расчета с этой же функцией:
Итак, мы рассмотрели примеры использования функции ВПР с двумя и с несколькими условиями. А также обнаружили, что этой ценной функции есть замечательная альтернатива.
Примеры использования функции ВПР:
Как объединить две или несколько таблиц в Excel — В этом руководстве вы найдете некоторые приемы объединения таблиц Excel путем сопоставления данных в одном или нескольких столбцах. Как часто при анализе в Excel вся необходимая информация собирается на одном…
2 способа извлечь данные из разных таблиц при помощи ВПР. — Задача: Данные, которые нужно найти и извлечь при помощи функции ВПР, находятся в нескольких таблицах. Эти таблицы имеют одинаковую структуру (то есть, одни и те же столбцы, расположенные в одном…
/> Вычисление номера столбца для извлечения данных в ВПР — Задача: Наиболее простым способом научиться указывать тот столбец, из которого функция ВПР будет извлекать данные. При этом мы не будем изменять саму формулу, поскольку это может привести в случайным ошибкам.…
4 способа, как сделать левый ВПР в Excel. — Функция ВПР – одна из самых популярных, когда нужно найти и извлечь из таблицы какие-либо данные. Но при этом она имеет один существенный недостаток. Поиск она производит в крайнем левом…
Формула ВПР в Excel для сравнения двух таблиц — 4 способа — Сравнение таблиц – это задача, которую в Excel приходится довольно часто решать. Например, у нас есть старый прайс-лист и его новая версия. Нужно просмотреть, цены на какие товары изменились и…
Почему не работает ВПР в Excel? — Функция ВПР – это очень мощный инструмент поиска. Но если он по каким-то причинам завершился неудачно, то вы получите сообщение об ошибке #Н/Д (#N/A в английском варианте). Давайте постараемся вместе…
Функция ВПР в Excel: пошаговая инструкция с 5 примерами — ВПР — это функция Excel для поиска и извлечения данных из определенного столбца в таблице. Она поддерживает приблизительное и точное сопоставление, а также подстановочные знаки (* и ?). Значения поиска…
Формула ВПР в Excel — 22 факта, которые нужно знать. — В процессе работы в Excel часто возникает задача извлечения нужных данных из рабочих таблиц. Для этой цели в Excel предусмотрена формула ВПР (VLOOKUP в английском варианте). И хотя ВПР относительно…
Суть запроса на выборку – выбрать из исходной таблицы строки, удовлетворяющие определенным критериям (подобно применению стандартного Фильтра ). Произведем отбор значений из исходной таблицы с помощью формул массива . В отличие от применения Фильтра ( CTRL+SHIFT+L или Данные/ Сортировка и фильтр/ Фильтр ) отобранные строки будут помещены в отдельную таблицу.
В этой статье рассмотрим наиболее часто встречающиеся запросы, например: отбор строк таблицы, у которых значение из числового столбца попадает в заданный диапазон (интервал); отбор строк, у которых дата принаждежит определенному периоду; задачи с 2-мя текстовыми критериями и другие. Начнем с простых запросов.
1. Один числовой критерий (Выбрать те Товары, у которых цена выше минимальной)
Пусть имеется Исходная таблица с перечнем Товаров и Ценами (см. файл примера, лист Один критерий — число ).
Необходимо отобразить в отдельной таблице только те записи (строки) из Исходной таблицы, у которых цена выше 25.
Решить эту и последующие задачи можно легко с помощью стандартного фильтра . Для этого выделите заголовки Исходной таблицы и нажмите CTRL+SHIFT+L . Через выпадающий список у заголовка Цены выберите Числовые фильтры. , затем задайте необходимые условия фильтрации и нажмите ОК.
Будут отображены записи удовлетворяющие условиям отбора.
Другим подходом является использование формул массива . В отличие от фильтра отобранные строки будут помещены в отдельную таблицу — своеобразный Отчет , который, например, можно отформатировать в стиль отличный от Исходной таблицы или производить другие ее модификации.
Критерий (минимальную цену) разместим в ячейке Е6 , таблицу для отфильтрованных данных — в диапазоне D10:E19 .
Теперь выделим диапазон D11:D19 (столбец Товар) и в Строке формул введем формулу массива :
Вместо ENTER нажмите сочетание клавиш CTRL+SHIFT+ENTER .
Те же манипуляции произведем с диапазоном E11:E19 куда и введем аналогичную формулу массива :
В результате получим новую таблицу, которая будет содержать только товары, у которых цены попадают в интервал, указанный в ячейках Е5 и Е6 .
Чтобы показать динамизм полученного Отчета (Запроса на выборку) введем в Е6 значение 65. В новую таблицу будет добавлена еще одна запись из Исходной таблицы, удовлетворяющая новому критерию.
Если в Исходную таблицу добавить новый товар с Ценой в диапазоне от 25 до 65, то в новую таблицу будет добавлена новая запись.
В файле примера также содержатся формулы массива с обработкой ошибок, когда в столбце Цена содержится значение ошибки, например #ДЕЛ/0! (см. лист Обработка ошибок ).
Следующие задачи решаются аналогичным образом, поэтому не будем их рассматривать так детально.
3. Один критерий Дата (Выбрать те Товары, у которых Дата поставки совпадает заданной)
Пусть имеется Исходная таблица с перечнем Товаров и Датами поставки (см. файл примера, лист Один критерий — Дата ).
Для отбора строк используются формулы массива, аналогичные Задаче1 (вместо критерия =$B$12:$B$20)*(СТРОКА($B$12:$B$20)-СТРОКА($B$11));$J$12-СТРОКА(A12)+СТРОКА($B$11)+1))
Примечание : После ввода формулы вместо клавиши ENTER (ВВОД) нужно нажать сочетание клавиш CTRL+SHIFT+ENTER. Это сочетание клавиш используется для ввода формул массива.
Скопируйте формулу массива вниз на нужное количество ячеек. Формула вернет только те значения Товаров, которые были поставлены в диапазоне указанных дат. В остальных ячейках будут содержаться ошибки #ЧИСЛО! Ошибки в файле примера (Лист 4.Диапазон Дат) скрыты с помощью Условного форматирования .
Аналогичную формулу нужно ввести и для дат в столбец E.
В ячейке J12 вычислено количество строк исходной таблицы, удовлетворяющих критериям:
Решение2 : Для отбора строк можно использовать формулы массива, аналогичные Задаче2 (т.е. формулы массива, возвращающие несколько значений ):
Для ввода первой формулы выделите диапазон ячеек G12:G20 . После ввода формулы вместо клавиши ENTER (ВВОД) нужно нажать сочетание клавиш CTRL+SHIFT+ENTER.
Решение3 : Если столбец Дат СОРТИРОВАН, то можно не использовать формулы массива.
Сначала необходимо вычислить первую и последнюю позиции строк, которые удовлетворяют критериям. Затем вывести строки с помощью функции СМЕЩ() .
Этот пример еще раз наглядно демонстрирует насколько предварительная сортировка данных облегчает написание формул.
5. Один критерий Дата (Выбрать те Товары, у которых Дата поставки не раньше/ не позже заданной)
Пусть имеется Исходная таблица с перечнем Товаров и Датами поставки (см. файл примера, лист Один критерий — Дата (не позже) ).
Для отбора строк, дата которых не раньше (включая саму дату), используется формула массива:
= ИНДЕКС(A12:A20;НАИМЕНЬШИЙ(ЕСЛИ($E$7 C15;И($B$7>=B15;$B$7 =$B$13:$B$21)*($B$13:$B$21>0);СТРОКА($B$13:$B$21);»»);СТРОКА($B$13:$B$21)-СТРОКА($B$12)) -СТРОКА($B$12))
Условие $E$7=$A$13:$A$21 гарантирует, что будут отобраны товары только определенного типа. Условие $E$8>=$B$13:$B$21 гарантирует, что будут отобраны даты не позже заданной (включая). Условие $B$13:$B$21>0 необходимо, если в диапазоне дат имеются пустые ячейки. Знак * (умножение) используется для задания Условия И (все 3 критерия должны выполняться для строки одновременно).
Примечание . Случай, когда список несортирован, рассмотрен в статье Поиск ДАТЫ (ЧИСЛА) ближайшей к заданной, с условием в MS EXCEL. Несортированный список .
7. Один Текстовый критерий (Выбрать Товары определенного вида)
Пусть имеется Исходная таблица с перечнем Товаров и Ценами (см. файл примера, лист Один критерий — Текст ).
Задача решается аналогично Задачам 1 и 3. Более подробное решение см. в статье Поиск ТЕКСТовых значений в MS EXCEL с выводом их в отдельный список. Часть1. Обычный поиск .
8. Два Текстовых критерия (Выбрать Товары определенного вида, поставленные в заданный месяц)
Пусть имеется Исходная таблица с перечнем Товаров и Ценами (см. файл примера, лист 2 критерия — текст (И) ).
Для отбора строк используется формула массива:
Выражение ($F$6=$A$11:$A$19)*($F$7=$B$11:$B$19) задает оба условия (Товар и Месяц).
Выражение СТРОКА(ДВССЫЛ(«A1:A»&ЧСТРОК($A$11:$A$19))) формирует массив последовательных чисел , т.е. номера строк в таблице.
9. Два Текстовых критерия (Выбрать Товары определенных видов)
Пусть имеется Исходная таблица с перечнем Товаров и Ценами (см. файл примера, лист 2 критерия — текст (ИЛИ) ).
В отличие от Задачи 7 отберем строки с товарами 2-х видов ( Условие ИЛИ ).
Для отбора строк используется формула массива:
= ИНДЕКС(A$11:A$19; НАИБОЛЬШИЙ((($E$6=$A$11:$A$19)+($E$7=$A$11:$A$19))*(СТРОКА($A$11:$A$19)-СТРОКА($A$10)); СЧЁТЕСЛИ($A$11:$A$19;$E$6)+СЧЁТЕСЛИ($A$11:$A$19;$E$7)-ЧСТРОК($A$11:A11)+1))
Условие ($E$6=$A$11:$A$19)+($E$7=$A$11:$A$19) гарантирует, что будут отобраны товары только заданных видов из желтых ячеек (Товар2 и Товар3). Знак + (сложение) используется для задания Условие ИЛИ (должен быть выполнен хотя бы 1 критерий).
Вышеуказанное выражение вернет массив . Умножив его на выражение СТРОКА($A$11:$A$19)-СТРОКА($A$10) , т.е. на массив последовательных чисел , получим массив позиций (номеров строк таблицы), удовлетворяющих критериям. В нашем случае это будет массив .
С помощью функции НАИБОЛЬШИЙ() выведем 3 значения из позиции 5 (строка 15 листа), 6 (16) и 7 (17), т.е. значения Товар2, Товар2 и Товар3. Для этого используем выражение СЧЁТЕСЛИ($A$11:$A$19;$E$6)+СЧЁТЕСЛИ($A$11:$A$19;$E$7)-ЧСТРОК($A$11:A11)+1 , которое последовательно (начиная со строки 11) будет возвращать числа 3; 2; 1; 0; -1; -2; . Формула НАИБОЛЬШИЙ(. ;3) вернет число 5, НАИБОЛЬШИЙ(. ;2) вернет число 6, НАИБОЛЬШИЙ(. ;1) вернет число 7, а НАИБОЛЬШИЙ(. ;0) и далее вернет ошибку, которую мы скроем условным форматированием .
И наконец, с помощью функции ИНДЕКС() последовательно выведем наши значения из соответствующих позиций: = ИНДЕКС(A$11:A$19;5) вернет Товар2, = ИНДЕКС(A$11:A$19;6) вернет Товар2, = ИНДЕКС(A$11:A$19;7) вернет Товар3.
10. Отбор значений с учетом повторов
В разделе Отбор на основании повторяемости собраны статьи о запросах с группировкой данных. Из повторяющихся данных сначала отбираются уникальные значения, а соответствующие им значения в других столбцах — группируются (складываются, усредняются и пр.).
Наиболее популярные статьи из этого раздела:
В качестве примера приведем решения следующей задачи: Выбрать Товары, цена которых лежит в определенном диапазоне и повторяется заданное количество раз или более.
В качестве исходной возьмем таблицу партий товаров.
Предположим, что нас интересует сколько и каких партий товаров поставлялось по цене от 1000р. до 2000р. (критерий 1). Причем, партий с одинаковой ценой должно быть минимум 3 (критерий 2).
Решением является формула массива:
Эта формула возвращает номера строк, которые удовлетворяют обоим критериям.
Формула =СУММПРОИЗВ(($C$14:$C$27>=$B$7)*($C$14:$C$27 =$B$10)) подсчитывает количество строк, которые удовлетворяют критериям.
В файле примера на листе «10.Критерий — колич-во повторов» настроено Условное форматирование , которое позволяет визуально определить строки удовлетворяющие критериям, а также скрыть ячейки, в которых формула массива возвращает ошибку #ЧИСЛО!
11. Используем значение критерия (Любой) или (Все)
В фильтре Сводных таблиц MS EXCEL используется значение (Все), чтобы вывести все значения столбца. Другими словами, в выпадающем списке значений критерия содержится особое значение, которое отменяет сам критерий (см. статью Отчеты в MS EXCEL , Отчет №3).
В файле примера на листе «11. Критерий Любой или (Все)» реализован данный вариант критерия.
Формула в этом случае должна содержать функцию ЕСЛИ() . Если выбрано значение (Все), то используется формула для вывода значений без учета данного критерия. Если выбрано любое другое значение, то критерий работает обычным образом.
Остальная часть формулы аналогична рассмотренным выше.
12. Актуальная цена
Пусть для товара ежедневно заполняется таблица цен (цена может меняться, но не каждый день). Нужно найти актуальнуй цену, т.е. цену на последнюю дату. Если товар всего один, то можно отсортировать по дате и в последней строке будет нужная актуальная цена. Если товаров много, то нужно сначала выбрать Автофильтром нужный товар, затем опять отсортировать по цене.
Чтобы иметь перечень товаров с актуальными ценами придется использовать формулы:
2) определяем последнюю (максимальную) дату для каждого товара с помощью формулы массива =МАКС((Таблица1[товар]=E8)*Таблица1[дата])
3) наконец, выводим актуальную цену =СУММЕСЛИМН(Таблица1[цена];Таблица1[товар];E8;Таблица1[дата];F8)
Для товара не должно быть повторов дат, иначе цены будут суммироваться (если повторяется последняя дата).
