Сравнение Таблиц В Excel
Батьянов Денис на правах гостевого автора рассказывает в этом посте о том, как найти данные в одной таблице Excel и извлечь их в другую, а также открывает все секреты функции вертикального просмотра. При работе в очень часто возникает потребность найти данные в одной таблице и извлечь их в другую. Если вы ещё не умеете это делать, то, прочитав статью, вы не только научитесь этому, но и узнаете, при каких условиях вы сможете выжать из системы максимум быстродействия. Рассмотрено большинство весьма эффективных приёмов, которые стоит применять совместно с функцией ВПР. Даже если вы годами используете функцию ВПР, то с высокой долей вероятности эта статья будет вам полезна и не оставит равнодушным. Я, например, будучи IT-специалистом, а потом и руководителем в IT, пользовался VLOOKUP 15 лет, но разобраться со всеми нюансами довелось только сейчас, когда я на профессиональной основе стал обучать людей Excel.
Рассмотрим в статье, как сравнить две таблицы в Excel различными способами в зависимости от исходных данных и необходимого результата. Как сравнивать данные в Excel. В этой статье рассказывается, как сравнить данные в Excel: в двух. Я подобрал для вас темы с готовыми решениями и ответами на вопрос Excel Сравнение двух таблиц.
ВПР — это аббревиатура от вертикального просмотра. Аналогично и VLOOKUP — Vertical LOOKUP. Уже само название функции намекает нам, что она производит поиск в строках таблицы (по вертикали — перебирая строки и фиксируя столбец), а не в столбцах (по горизонтали — перебирая столбцы и фиксируя строку). Надо заметить, что у ВПР есть сестра — гадкий утёнок, которая никогда не станет лебедем, — это функция ГПР (HLOOKUP).
Apr 17, 2018 - В данной статье описаны способы поиска повторяющихся значений в Excel, основанные на сравнении данных в двух столбцах.
ГПР, в противоположность ВПР, производит горизонтальный поиск, однако концепция Excel (да и вообще концепция организации данных) подразумевает, что ваши таблицы имеют небольшое количество столбцов и гораздо большее количество строк. Именно поэтому поиск по строкам нам требуется во много раз чаще, чем по столбцам.
Если вы в слишком часто пользуетесь функцией ГПР, то, вполне вероятно, что вы чего-то не поняли в этой жизни. Синтаксис Функция ВПР имеет четыре параметра: =ВПР(;; ; ), тут: — искомое значение (редко) или ссылка на ячейку, содержащую искомое значение (подавляющее большинство случаев); — ссылка на диапазон ячеек (двумерный массив), в ПЕРВОМ (!) столбце которого будет осуществляться поиск значения параметра; — номер столбца в диапазоне, из которого будет возвращено значение; — это очень важный параметр, который отвечает на вопрос, а отсортирован ли по возрастанию первый столбец диапазона. В случае, если массив отсортирован, мы указываем значение ИСТИНА (TRUE) или 1, в противном случае — ЛОЖЬ (FALSE) или 0. В случае, если данный параметр опущен, он по умолчанию становится равным 1. Держу пари, что многие из тех, кто знает функцию ВПР как облупленную, прочитав описание четвёртого параметра, могут почувствовать себя неуютно, так как они привыкли видеть его в несколько ином виде: обычно там идёт речь о точном соответствии при поиске (ЛОЖЬ или 0) либо же о диапазонном просмотре (ИСТИНА или 1). Вот сейчас надо напрячься и читать следующий абзац несколько раз, пока не прочувствуете смысл сказанного до конца.
Там важно каждое слово. Примеры помогут разобраться. Как же конкретно работает формула ВПР.
Как Сделать График В Excel
Вид формулы 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.
Сравнение Таблиц Excel 2016
Умножение на единицу 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) и составной ключ столбца не потребовался бы вовсе. Это моя первая статья для Лайфхакера. Если вам понравилось, то приглашаю вас посетить, а также с удовольствием прочту в комментариях о ваших секретах использования функции ВПР и ей подобных.
У вас всё очень плохо с математикой и с опытом эффективного использования Экселя. Видимо, не приходилось делать ничего сложнее отчётов по продажам.
И сразу в консультанты??? Трудно всерьёз воспринимать 'специалиста по XLS', который не пользуется стилем R1C1. Образец договора возвратной финансовой помощи в узбекистане. Знак 'доллар' в формуле - это уровень не выше секретарш. Приказ о введении в действие инструкций о мерах пожарной безопасности.
Ну, максимум менеджер по продажам, но никак не специалист. Рост в 2 раза — это рост на 100%. Поэтому рост в 57 раз — это рост на 5600%, а не на 5700% ))) XDDD А тот факт, что вы считаете возможным спутать 57-кратный рост с ростом на 57% — просто оскорбление и проявление неуважения к грамотным людям. Похоже повсеместное падение уровня знаний уже необратимо.
Послушайте, юноша со взором горящим:) Невозможно всерьёз воспринимать человека, который делает глобальные выводы на основе не относящихся к делу или несущественных фактов. Статья разве про системы адресации в Excel? ВПР как-то по другому работает с адресацией A1? Если я в примерах использую вашу любимую RC, статью прочтёт больше людей? Прочтёт гораздо меньше. С процентами вы правильно заметили неточность.
Но к чему этот неумный пафос? И вы ещё сетуете на падение уровня знаний, когда сами демонстрируете провалы в логике, неумение корректно вести дискуссию, а также полное непонимание абсурдности своей аргументации. Мб кому-нибудь пригодиться следующий совет по ускорению ВПР: Пусть, к примеру, A1 - адрес ячейки, значение которой мы будем искать; B1:D10 - диапазон, в котором будет проходить поиск, В столбец - содержит искомое значение, D столбец - значение, которое мы хотим получить в результате работы ВПР. Главное требование - отсортированность диапазона B1:D10 по столбцу B (порядок сортировки не важен).
Далее формула ВПР примет вид: =ЕСЛИ(ВПР(А1; B1:B10; 1; 1) A1; 'Значение отсутствует'; ВПР(A1; B1:D10; 3; 1)) Если требуется найти n значений среди m значений, то обычный ВПР имеет сложность O(n.m), улучшенная версия, даже с изначальной сортировкой: O(n.(log2(m) 1) n.ln(n)). Прирост скорости грубо оценить по формуле не составит труда.