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