ВПР - мощная функция. Но на одном из моих семинаров по Power Excel я часто получаю вопрос от человека, который хочет знать, может ли функция ВПР вернуть все совпадающие значения. Как вы знаете, функция ВПР с False в качестве четвертого аргумента всегда будет возвращать первое совпадение, которое она находит. На следующем снимке экрана ячейка F2 возвращает 3623, потому что это первое совпадение, найденное для задания J1199.
Тогда возникает вопрос, может ли функция VLOOKUP вернуть все совпадения?
ВПР не будет. Но другие функции могут.
Если вы хотите просуммировать все затраты по заданию J1199, вы должны использовать =SUMIFS($B$2:$B$53,$A$2:$A$53,G2),
Если у вас есть текстовые значения и вы хотите объединить все результаты в одно значение, вы можете использовать =TEXTJOIN(", ",TRUE,IF($A$2:$A$53=G2,$C$2:$C$53,"")). Эта формула работает только в Office 365 и Excel 2019.
Или вам может потребоваться вернуть все результаты для одного задания в новый диапазон рабочего листа. Совершенно новая =FILTER(B2:C53,A2:A53=K1,"None Found")функция, которая появится в Office 365 в 2019 году, решит проблему:
Иногда люди хотят выполнить все операции ВПР и просуммировать их. Если ваша таблица поиска отсортирована, вы можете использовать =SUM(LOOKUP(B2:B53,M3:N5)).
Если вам нужно просуммировать все ВПР с версией ВПР с точным соответствием, вам потребуется доступ к динамическим массивам для использования =SUM(VLOOKUP(B2:B53,M3:N5,2,TRUE)).
Чтобы узнать больше о динамических массивах, прочтите статью «Динамические массивы Excel прямо к делу».
Смотреть видео
Стенограмма видео
Учитесь Excel из эпизода подкаста 2247: Можете ли вы вернуть все значения VLookUp?
Привет. Добро пожаловать в сетевую трансляцию. Я Билл Джелен. На прошлой неделе на моем семинаре в Аплтоне, штат Висконсин, возникли два вопроса, оба связаны между собой. Они сказали, эй, как нам вернуть все ВПР, хорошо? В этом случае, как в J1199, есть куча совпадений, и они, как вы знаете, хотят вернуть их все, и мой первый вопрос, когда кто-то спрашивает меня об этом: ну, что вы хотите делать с совпадениями? Это числа, которые вы хотите сложить, или это текст, который вы хотите объединить? И это забавно. Два вопроса на одном семинаре, один человек хотел сложить их, а другой хотел объединить результаты.
Итак, давайте посмотрим на оба из них. Посмотрите в описании YouTube оглавление, где вы можете перейти к другому, если хотите увидеть результат текста.
Итак, во-первых, если мы хотим сложить их все, мы вообще не будем использовать ВПР. Мы собираемся использовать функцию SUMIF или SUMIFS, которая будет суммировать все, что соответствует этому элементу. Итак, СУММЕСЛИМН. Вот числовые значения, которые мы хотим суммировать, и я нажму F4, чтобы заблокировать это. Таким образом, когда я копирую это вниз, он будет продолжать указывать на тот же диапазон, а затем мы хотим пойти и проверить, соответствует ли номер JOB в столбце A, снова F4, значению слева от нас. - в данном случае E2 - и когда мы скопируем это вниз, мы увидим ВСЕГО для каждого элемента. (СУММЕСЛИМН ($ B $ 2: $ B $ 53, $ A $ 2: $ A $ 53, E2))
Давайте просто сделаем здесь небольшую проверку. J1199. Итого 25365. Хорошо. Итак, это работает. Если это числа, и вы хотите получить все числа и сложить их, переключитесь на СУММЕСЛИ или СУММЕСЛИМН, но если это текст, хорошо, теперь эта функция появилась в Office 365 в феврале 2017 года. Итак, если у вас есть Excel 2016 или Excel 2013, Excel 2010 или любой из этих старых, у вас не будет этой функции. Это функция под названием TEXTJOIN. TEXTJOIN. Это еще одна функция от (Джо МакДейда - 01:50), который только что представил нам все эти великолепные формулы динамических массивов на Ignite в 2018 году, и Джо убедился, что TEXTJOIN будет работать с массивами, и это действительно здорово.
Итак, разделителем здесь будет ПРОБЕЛ, определенно игнорируйте ПУСТО. Здесь мы хотим проигнорировать EMPTY, потому что в следующей части, операторе IF, мы собираемся сгенерировать много пустых полей. ЕСЛИ этот элемент над A2, F4, = этому номеру ЗАДАНИЯ здесь, тогда мне нужен соответствующий элемент из столбца C, F4, в противном случае я хочу «» вот так. Закройте этот оператор IF. Закройте TEXTJOIN. Нужно ли мне нажимать CONTROL + SHIFT + ENTER? Нет, я не. Он приносит мне все продукты, которые так подходят, хорошо? Итак, возвращая все ВПР, если мы хотим их суммировать, да, если мы хотим объединить их, да. (= TEXTJOIN («,», Истина, IF ($ A $ 2: $ A $ 53 = E2, $ C $ 2: $ C $ 53, «»)))
Хорошо, теперь есть еще одна возможность, когда люди спрашивают меня, могут ли они вернуть все ВПР. Это может быть проблема, когда мы хотим найти здесь каждую из этих затрат и вычислить СТОИМОСТЬ ОБРАБОТКИ, а затем просуммировать их все. Например, я не хочу размещать здесь ВПР, здесь ВПР, здесь ВПР, а здесь - ВПР. Я просто хочу сделать их все вместе, и в этом случае мы будем использовать функцию SUM, а затем старую, старую функцию LOOKUP. LOOKUP говорит, что мы собираемся найти все эти значения в столбце B. Мне здесь не нужна F4, потому что я никуда ее не копирую. ,. Вот наша таблица поиска. ), закройте СУММ, и он погаснет и выполнит каждую отдельную ВПР, а затем суммирует их все таким образом. (= СУММ (ПРОСМОТР (B2: B53; K3: L5)))
Ну привет. Все эти темы - моя книга LIV: 54 величайших совета всех времен. Щелкните значок i в правом верхнем углу, чтобы узнать больше.
Итак, вопрос в том, можно ли вернуть все ВПР? Ну, вроде как, но без ВПР. Мы собираемся использовать SUMIF, TEXTJOIN, SUM или LOOKUP, чтобы решить эту проблему.
Ну привет. Я хочу поблагодарить вас за то, что заглянули. Увидимся в следующий раз на другой сетевой трансляции от.
Вы знаете, хорошо, я уже неделю говорил об этих динамических массивах. Я хотел сделать одно видео, в котором я не касался динамических массивов, потому что я знаю, что у многих людей их еще нет, но вот мы здесь. Это выход. Вы знаете, это не по алфавиту. Было бы намного лучше, если бы мы могли их отсортировать, и если у вас есть новые динамические массивы, вы можете отправить это в функцию SORT, SORT, как это, и нажать ENTER, и теперь результаты будут отсортированы таким образом.
Вы знаете, даже эта формула может быть улучшена с помощью динамических массивов. Поиск требует, чтобы вы использовали TRUE. Что, если вы хотите использовать ЛОЖЬ? Мы могли бы изменить это на ВПР, искать весь этот текст в этой таблице, 2,. В этом случае я буду использовать TRUE, но в другом случае вы можете использовать FALSE. КОНТРОЛЬ + SHIFT + ВВОД. Нет, это просто сработает, хорошо? (= СУММ (ВПР (B2: B53; K3: L5,2; Верно)))
Выход динамических массивов в начале 2019 года решит так много проблем.
Спасибо, что остались здесь на вечеринке. Увидимся в следующий раз на другой сетевой трансляции от.
Скачать файл Excel
Чтобы загрузить файл Excel: can-you-return-all-vlookup-values.xlsx
Когда кто-то спрашивает: «Может ли функция VLOOKUP вернуть все совпадения», ответ будет отрицательным. Но есть много других функций, которые могут делать то же самое.
Идея дня в Excel
Я попросил совета у моих друзей-мастеров Excel по поводу Excel. Сегодняшняя мысль задуматься:
«Нормализуйте свои данные, как если бы другие нормализовали свои данные за вас»
Кевин Лербасс
