Это еще один пример формулы скорости. Скажем, вам нужно сделать 12 столбцов ВПР. Вы можете сделать это быстрее, используя одну функцию ПОИСКПОЗ и 12 ИНДЕКС.
На следующем рисунке вам нужно будет выполнить 12 функций ВПР для каждого номера счета. ВПР - это мощный инструмент, но для выполнения расчетов требуется много времени.
Кроме того, формулу нужно редактировать в каждой ячейке при копировании. Третий аргумент должен измениться с 2 на 3 для февраля, затем на 4 для марта и так далее.
Один из способов решения проблемы - добавить строку с номерами столбцов. Тогда третий аргумент ВПР может указывать на эту строку. По крайней мере, вы можете скопировать ту же формулу из B4 и вставить в C4: M4, прежде чем копировать весь набор.
Но здесь гораздо более быстрый подход. Добавьте новый столбец B с надписью Где? как заголовок. Столбец B содержит функцию ПОИСКПОЗ. Эта функция очень похожа на ВПР: вы ищете значение в A4 в столбце P4: P227. 0 в конце похож на False в конце VLOOKUP. Он указывает, что вам нужно точное совпадение. Вот большая разница: MATCH возвращает то место, где найдено значение. Ответ 208 говорит, что A308 является 208-й ячейкой в диапазоне P4: P227. С точки зрения пересчета времени, MATCH и VLOOKUP примерно равны.
Я слышу, о чем вы думаете. «Что хорошего в том, чтобы знать, где что-то находится? Я никогда не слышал, чтобы менеджер звонил и спрашивал: «По какой строке это дебиторская задолженность?» »
Хотя люди редко спрашивают, в какой строке что-то находится, функция ИНДЕКС может использовать эту позицию. Следующая формула указывает Excel вернуть 208-й элемент из четвертого квартала: Q227.
По мере копирования этой формулы массив значений перемещается по таблице поиска. Для каждой строки вы выполняете одну функцию ПОИСКПОЗ и 12 ИНДЕКС. Функция ИНДЕКС невероятно быстрая по сравнению с ВПР. Полный набор формул вычислит на 85% быстрее, чем 12 столбцов ВПР.
Смотреть видео
- Скажем, вам нужно сделать 12 столбцов ВПР.
- Осторожно используйте один знак доллара перед столбцом искомого значения.
- Осторожно используйте четыре знака доллара для таблицы поиска
- Вы все еще жестко кодируете аргумент третьего столбца.
- Одним из распространенных решений является добавление ряда вспомогательных ячеек с номером столбца.
- Другое менее эффективное решение - использовать СТОЛБЕЦ (B2) внутри формулы ВПР.
- Но делать 12 ВПР для каждой строки очень неэффективно.
- Вместо этого добавьте вспомогательный столбец с заголовком WHERE и выполните одно Match.
- МАТЧ длится столько же, сколько ВПР за январь.
- Затем вы можете использовать 12 функций ИНДЕКС. Это невероятно быстро по сравнению с ВПР.
- ИНДЕКС будет указывать на один столбец с ответами со знаком $ перед строками.
- ИНДЕКС будет указывать на вспомогательный столбец с символом $ перед столбцом.
Стенограмма видео
Изучите Excel по подкасту, выпуск 2028 - Замена множества ВПР одним МАТЧЕМ!
Щелкните значок «i» в правом верхнем углу, чтобы перейти к списку воспроизведения. Я буду подкастировать всю эту книгу!
Привет, добро пожаловать обратно в сетевую трансляцию, я Билл Джелен! Ну, это классическая проблема, мы должны делать ВПР один раз в месяц, верно? И вы можете быть невероятно осторожны здесь, нажимая F4 3 раза, чтобы заблокировать это до столбца, а затем нажимать F4 один раз, чтобы заблокировать всю строку. Но когда вы дойдете до этой точки, 2, ЛОЖЬ, что 2 жестко закодированы, и когда вы скопируете это, вам придется отредактировать 2 на 3, верно? Есть один неэффективный способ, который мне не нравится, - использовать столбец B1. Столбец B1, конечно, равен 2, но когда вы его скопируете, увидите, что он изменится на столбец C1, который равен 3, но подумайте об этом, это постоянно вычисляет номер столбца снова и снова. Я вижу, что люди делают и почему, вы знаете, предпочитают больше столбцов, мы перетаскиваем их, удерживая Ctrl,поместите числа 2-13 туда, во вспомогательную ячейку, а затем, когда мы дойдем до этой точки, мы поднимемся и укажем номер этого столбца. Нажмите F4 2 раза, чтобы привязать его к строке,, FALSE и так далее. Но даже с этим методом ВПР невероятно неэффективен, потому что он должен искать здесь все эти элементы, пока не найдет A308, а это вычислитель B4. Когда он затем переходит на C4, он забывает, что он просто пошел и посмотрел, и все начинается снова, хорошо. Таким образом, у вас есть одна из самых медленных функций во всем Excel: ВПР, ЛОЖЬ, выполняемые снова и снова для одного и того же элемента.потому что он должен пройти по всем этим элементам здесь, пока не найдет A308, а это фигура B4. Когда он затем переходит на C4, он забывает, что он просто пошел и посмотрел, и все начинается снова, хорошо. Таким образом, у вас есть одна из самых медленных функций во всем Excel: ВПР, ЛОЖЬ, выполняемые снова и снова для одного и того же элемента.потому что он должен пройти по всем этим элементам здесь, пока не найдет A308, а это фигура B4. Когда он затем переходит на C4, он забывает, что он просто пошел и посмотрел, и все начинается снова, хорошо. Таким образом, у вас есть одна из самых медленных функций во всем Excel: ВПР, ЛОЖЬ, выполняемые снова и снова для одного и того же элемента.
Итак, вот гораздо более быстрый способ: мы собираемся вставить вспомогательный столбец, и этот вспомогательный столбец я называю "Где?" А где, черт возьми, А308? Мы будем использовать a = MATCH, искать A308 в первой строке таблицы, нажимать F4 там,, 0 для точного совпадения, хорошо, он говорит нам, что «Эй, посмотри на это, это в строке 6, как это круто? " Но когда мы копируем, видите, это все время в разных местах. Хорошо, теперь этот матч длится столько же, сколько занимает январская ВПР, там они даже мертвы, но вот что замечательно. Оттуда нам никогда не нужно выполнять ВПР для остальной части строки, мы можем просто сделать = ИНДЕКС, ИНДЕКС говорит: «Вот массив ответов». Я собираюсь перейти к январским камерам, и здесь я собираюсь очень осторожно нажать F4 2 раза, чтобы установить его на 4: 227,но Q может меняться по мере моего движения. Запятая, а затем он хочет знать, в какой строке, ну, это будет ответ в B4, я нажимаю F4 3 раза, чтобы получить $ перед B, хорошо, скопируйте это поперек.
Эта формула, эти формулы ИНДЕКСА, эти 12 будут выполнены быстрее, чем потребуется на выполнение февральской ВПР, хорошо. Если мы поместим на это таймер Чарльза Вильямса, все это будет рассчитывать примерно 14% времени 12 VLOOKUP. Ваш менеджер не хочет видеть "Где?" Хорошо, просто скройте этот столбец, все продолжает работать, хорошо, это прекрасный способ ускорить 12 месяцев или 52 недели ВПР. Хорошо, этот совет и многие другие есть в этой книге. Щелкните «i» в правом верхнем углу, вы можете купить книгу, электронную книгу за 10 долларов, печатную книгу за 25 долларов, хорошо.
Итак, сегодня у нас возникла проблема, когда 12 столбцов ВПР, вы можете аккуратно вставить $, но тогда этот третий аргумент все еще должен быть жестко закодирован. Вы можете использовать столбец (B2), я не фанат этого, потому что есть сотни строк * 12 столбцов, где это вычисляется снова и снова. Просто используйте вспомогательную ячейку в ряд, поместите числа от 2 до 12 и укажите на это, но это все еще неэффективно, потому что ВПР после того, как он вычисляет январь, он должен начинаться еще в начале февраля. Поэтому я рекомендую добавить столбец с заголовком «Где?» и провести там единственный МАТЧ. Это СООТВЕТСТВИЕ занимает столько же времени, сколько ВПР для января, но тогда 12 функций ИНДЕКСА займут меньше времени, чем ВПР для февраля, и вы урезали целую кучу времени. Опять же, будьте осторожны с $ в функции ИНДЕКС в обоих местах, один перед строками,и другой перед столбцами, смешанная ссылка в обоих из них.
Привет, я хочу поблагодарить вас за то, что заглянули, увидимся в следующий раз на еще одной трансляции от!
Скачать файл
Загрузите образец файла здесь: Podcast2028.xlsx
