Кестедегі деректерді табуға және оны басқасына шығаруға арналған Excel VLOOKUP функциясының барлық құпиялары

2024 Автор: Malcolm Clapton | [email protected]. Соңғы өзгертілген: 2023-12-17 04:02

Мақаланы оқығаннан кейін сіз Excel электрондық кестесінде деректерді табуды және оны басқасына шығаруды ғана емес, сонымен қатар VLOOKUP функциясымен бірге қолдануға болатын әдістерді де үйренесіз.

Excel бағдарламасында жұмыс істегенде, көбінесе бір кестеден деректерді тауып, оны басқа кестеге шығару қажеттілігі туындайды. Егер сіз мұны қалай істеу керектігін әлі білмесеңіз, мақаланы оқығаннан кейін сіз мұны қалай жасау керектігін ғана емес, сонымен қатар қандай жағдайларда жүйеден максималды өнімділікті сығып алуға болатындығын білесіз. VLOOKUP функциясымен бірге қолданылуы керек өте тиімді әдістердің көпшілігі қарастырылады.

VLOOKUP функциясын жылдар бойы пайдалансаңыз да, бұл мақала сізге пайдалы болады және сізді бей-жай қалдырмайды. Мысалы, IT маманы, содан кейін IT саласының басшысы бола отырып, мен VLOOKUP қолданбасын 15 жыл бойы қолданамын, бірақ мен Excel бағдарламасын кәсіби негізде адамдарға үйрете бастаған кезде ғана барлық нюанстарды жеңе алдым.

VLOOKUP дегеннің аббревиатурасы болып табылады v вертикалды Н. С тексеру. Сол сияқты, VLOOKUP - Тік ІЗДЕУ. Функцияның атауының өзі бізге оның бағандарда емес (көлденеңінен - бағандар үстінде қайталау және жолды бекіту) кесте жолдарында (тігінен - жолдар бойынша қайталау және бағанды бекіту) іздейтінін меңзейді. Айта кету керек, VLOOKUP-тың әпкесі бар - ешқашан аққуға айналмайтын ұсқынсыз үйрек - бұл HLOOKUP функциясы. VLOOKUP функциясына қарағанда HLOOKUP көлденең іздеулерді орындайды, бірақ Excel тұжырымдамасы (және шын мәнінде деректерді ұйымдастыру тұжырымдамасы) кестелеріңізде аз бағандар және көп жолдар бар екенін білдіреді. Сондықтан жолдар бойынша іздеуді бағандарға қарағанда бірнеше есе жиі жасау керек. Excel бағдарламасында HLO функциясын тым жиі пайдалансаңыз, сіз бұл өмірде бірдеңені түсінбеген боларсыз.

Синтаксис

VLOOKUP функциясының төрт параметрі бар:

= VLOOKUP (;; [;]), Мұнда:

- қажетті мән (сирек) немесе қажетті мәнді қамтитын ұяшыққа сілтеме (жағдайлардың басым көпшілігі);

- БІРІНШІ (!) бағанында параметр мәні ізделетін ұяшықтар ауқымына (екі өлшемді массив) сілтеме;

- мән қайтарылатын диапазондағы баған нөмірі;

- бұл ауқымның бірінші бағанының өсу ретімен сұрыпталған-сұрақталмағаны туралы сұраққа жауап беретін өте маңызды параметр. Егер массив сұрыпталса, біз TRUE немесе 1 мәнін көрсетеміз, әйтпесе - FALSE немесе 0. Егер бұл параметр өткізілмесе, ол әдепкі бойынша 1 болады.

VLOOKUP функциясын қабыршақ деп білетіндердің көпшілігі төртінші параметрдің сипаттамасын оқығаннан кейін өздерін ыңғайсыз сезінуі мүмкін деп ойлаймын, өйткені олар оны сәл басқаша түрде көруге дағдыланған: әдетте олар дәл сәйкестік туралы айтады. іздеу (ЖАЛҒАН немесе 0) немесе ауқымды сканерлеу (ШЫН немесе 1).

Енді сіз айтылған сөздің мағынасын соңына дейін сезінгенше келесі абзацты бірнеше рет штамптап, оқып шығуыңыз керек. Онда әрбір сөз маңызды. Мысалдар оны анықтауға көмектеседі.

VLOOKUP формуласы дәл қалай жұмыс істейді?

• Формула түрі I. Соңғы параметр алынып тасталса немесе 1-ге тең көрсетілсе, VLOOKUP бірінші баған өсу ретімен сұрыпталған деп болжайды, осылайша іздеу келесі жолда тоқтайды: бірден қажетті мәннен үлкен мәнді қамтитын жолдың алдында тұрады … Егер мұндай жол табылмаса, ауқымның соңғы жолы қайтарылады.

  

• Формула II. Егер соңғы параметр 0 ретінде көрсетілсе, VLOOKUP массивтің бірінші бағанын дәйекті түрде сканерлейді және параметрмен бірінші дәл сәйкестік табылған кезде іздеуді дереу тоқтатады, әйтпесе # N / A (# N / A) қате коды. қайтарылады.

  

Формулалар жұмыс үрдістері

VPR I түрі

VPR II түрі

I түрдегі формулалар үшін қорытындылар

1. Формулаларды мәндерді ауқымдар бойынша тарату үшін пайдалануға болады.
2. Егер бірінші бағанда қайталанатын мәндер болса және дұрыс сұрыпталған болса, қайталанатын мәндері бар жолдардың соңғысы қайтарылады.
3. Бірінші бағандағы мәннен анық үлкен мәнді іздесеңіз, кестенің соңғы жолын оңай табуға болады, ол өте құнды болуы мүмкін.
4. Бұл көрініс қажетті мәннен кем немесе оған тең мән таппаған жағдайда ғана # N / A қатесін қайтарады.
5. Массив сұрыпталмаған болса, формула қате мәндерді қайтаратынын түсіну өте қиын.

II типті формулаларға қорытынды

Қажетті мән массивтің бірінші бағанында бірнеше рет орын алса, формула деректерді кейінгі іздеу үшін бірінші жолды таңдайды.

VLOOKUP өнімділігі

Сіз мақаланың шарықтау шегіне жеттіңіз. Соңғы параметр ретінде нөлді немесе бірді көрсетсем, қандай айырмашылық бар? Негізінде, барлығы, әрине, нөлді көрсетеді, өйткені бұл өте практикалық: массивтің бірінші бағанын сұрыптау туралы алаңдамаудың қажеті жоқ, мәннің табылғанын немесе табылмағанын бірден көре аласыз. Бірақ парағыңызда бірнеше мың VLOOKUP формулалары болса, VLOOKUP II баяу екенін байқайсыз. Сонымен бірге, әдетте бәрі ойлана бастайды:

• Маған қуаттырақ компьютер керек;
• Маған жылдамырақ формула керек, мысалы, INDEX + MATCH туралы көптеген адамдар біледі, ол шамалы 5-10% жылдамырақ.

Сіз I түріндегі VLOOKUP қолданбасын пайдалана бастаған кезде және бірінші баған кез келген әдіспен сұрыпталғанына көз жеткізгенде, VLOOKUP жылдамдығы 57 есе артады деп санайтын адамдар аз. Сөзбен жазып отырмын – ЕЛІ ЖЕТІ РЕТ! 57% емес, 5700%. Мен бұл фактіні сенімді түрде тексердім.

Мұндай жылдам жұмыстың құпиясы екілік іздеу (жартылау әдісі, дихотомия әдісі) деп аталатын сұрыпталған массивте өте тиімді іздеу алгоритмін қолдануға болатынында жатыр. Сондықтан I түрдегі VLOOKUP оны қолданады, ал II түрдегі VLOOKUP еш оңтайландырусыз іздейді. Ұқсас параметрді қамтитын MATCH функциясына және тек сұрыпталған массивтерде жұмыс істейтін және Lotus 1-2-3 үйлесімділігі үшін Excel бағдарламасында қамтылған ІЗДЕУ функциясына да солай.

Формуланың кемшіліктері

VLOOKUP-тың кемшіліктері анық: біріншіден, ол тек көрсетілген массивтің бірінші бағанында, екіншіден, тек осы бағанның оң жағында іздейді. Сіз түсінгеніңіздей, қажетті ақпаратты қамтитын баған біз іздейтін бағанның сол жағында болуы мүмкін. Жоғарыда айтылған INDEX + MATCH формулаларының тіркесімі бұл кемшіліктен айырылған, бұл оны VLOOKUP (VLOOKUP) функциясымен салыстырғанда кестелерден деректерді алудың ең икемді шешіміне айналдырады.

Формуланы өмірде қолданудың кейбір аспектілері

Ауқымды іздеу

Ауқымды іздеудің классикалық иллюстрациясы тапсырыс өлшемі бойынша жеңілдікті анықтау міндеті болып табылады.

Мәтін жолдарын іздеу

Әрине, VLOOKUP тек сандарды ғана емес, мәтінді де іздейді. Формула таңбалардың регистрін ажыратпайтынын есте ұстаған жөн. Қойылмалы таңбаларды пайдалансаңыз, анық емес іздеуді ұйымдастыруға болады. Екі қойылмалы таңба бар: "?" - мәтін жолындағы кез келген бір таңбаны ауыстырады, "*" - кез келген символдардың кез келген санын ауыстырады.

Ұрыс алаңдары

Іздеу кезінде қосымша бос орындар мәселесін қалай шешуге болады деген сұрақ жиі көтеріледі. Іздеу кестесін әлі де олардан тазалау мүмкін болса, VLOOKUP формуласының бірінші параметрі әрқашан сізге байланысты емес. Сондықтан, егер ұяшықтарды қосымша бос орындармен бітеп тастау қаупі болса, оны тазалау үшін TRIM функциясын пайдалануға болады.

Әр түрлі деректер пішімі

VLOOKUP функциясының бірінші параметрі санды қамтитын, бірақ ұяшықта мәтін ретінде сақталатын ұяшыққа қатысты болса және массивтің бірінші бағанында дұрыс пішімдегі сандар болса, іздеу сәтсіз болады. Керісінше жағдай да болуы мүмкін. 1-параметрді қажетті пішімге аудару арқылы мәселені оңай шешуге болады:

= VLOOKUP (−− D7; Өнімдер! $ A $ 2: $ C $ 5; 3; 0) - егер D7 мәтінді, ал кестеде сандар болса;

= VLOOKUP (D7 & ""); Өнімдер! $ A $ 2: $ C $ 5; 3; 0) - және керісінше.

Айтпақшы, сіз мәтінді бірден бірнеше тәсілмен санға аудара аласыз, таңдаңыз:

• Қос терістеу -D7.
• Бір D7 * 1-ге көбейту.
• Нөлдік қосу D7 + 0.
• Бірінші қуатқа дейін көтеру D7 ^ 1.

Санды мәтінге түрлендіру Excel бағдарламасын деректер түрін түрлендіруге мәжбүр ететін бос жолмен біріктіру арқылы жүзеге асырылады.

# N / A қалай басуға болады

Мұны IFERROR функциясымен орындау өте ыңғайлы.

Мысалы: = ҚАТЕЛІК (VLOOKUP (D7; Өнімдер! $ A $ 2: $ C $ 5; 3; 0); "").

Егер VLOOKUP қате кодын № N / A қайтарса, онда IFERROR оны ұстап алады және 2-параметрді ауыстырады (бұл жағдайда бос жол), ал егер қате орын алмаса, бұл функция ол мүлдем жоқ деп көрсетеді, бірақ қалыпты нәтижені қайтаратын тек VLOOKUP бар.

Массив

Көбінесе олар массивтің сілтемесін абсолютті етуді ұмытады, ал массив созылғанда «жүзеді». A2: C5 орнына $ A $ 2: $ C $ 5 пайдалануды ұмытпаңыз.

Анықтамалық массивті жұмыс кітабының жеке парағына орналастырған дұрыс. Ол аяқ астына түспейді және қауіпсіз болады.

Бұл массивті аталған ауқым ретінде жариялау одан да жақсы идея болар еді.

Көптеген пайдаланушылар массивті көрсеткенде, бүкіл бағандарды көрсете отырып, A: C сияқты құрылымды пайдаланады. Бұл тәсілдің өмір сүруге құқығы бар, өйткені сіз өзіңіздің массивіңізде барлық қажетті жолдарды қамтитын фактіні бақылаудан құтыласыз. Бастапқы массиві бар параққа жолдарды қоссаңыз, A: C ретінде көрсетілген ауқымды реттеу қажет емес. Әрине, бұл синтаксистік құрылым Excel бағдарламасын дәл диапазонды көрсетуден гөрі біршама көп жұмыс істеуге мәжбүр етеді, бірақ бұл үстеме шығындарды елемеу мүмкін. Біз секундтың жүзден бір бөлігі туралы айтып отырмыз.

Ал, данышпандық шегінде - массивді пішінде орналастыру.

Шығарылатын бағанды көрсету үшін COLUMN функциясын пайдалану

VLOOKUP көмегімен деректерді шығарып жатқан кесте іздеу кестесімен бірдей құрылымға ие болса, бірақ жай ғана азырақ жолдарды қамтыса, VLOOKUP ішіндегі БАҒАН () функциясын шығарып алынатын бағандардың санын автоматты түрде есептеу үшін пайдалануға болады. Бұл жағдайда барлық VLOOKUP формулалары бірдей болады (бірінші параметр үшін реттеледі, ол автоматты түрде өзгереді)! Бірінші параметрде абсолютті баған координатасы бар екенін ескеріңіз.

& "|" & көмегімен құрама кілт жасау

Егер бір уақытта бірнеше бағандар бойынша іздеу қажет болса, онда іздеу үшін құрама кілт жасау қажет. Егер қайтарылатын мән мәтіндік емес («Код» өрісіндегідей), бірақ сандық болса, бұл үшін ыңғайлырақ SUMIFS формуласы қолайлы және құрама баған кілті мүлде қажет болмас еді.

Бұл менің лайфхакерге арналған алғашқы мақалам. Егер сізге ұнаса, мен сізді келуге шақырамын, сонымен қатар VLOOKUP функциясын және т.б. пайдалану құпиялары туралы түсініктемелерде қуана оқимын. рахмет.:)