Excel VLOOKUP функциясынын бардык сырлары таблицадагы маалыматтарды табуу жана аны башкага чыгаруу үчүн

2024 Автор: Malcolm Clapton | [email protected]. Акыркы өзгөртүү: 2023-12-17 04:03

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

Excelде иштөөдө көбүнчө бир таблицадан маалыматтарды таап, экинчисине чыгаруу зарылчылыгы келип чыгат. Эгер сиз дагы эле муну кантип жасоону билбей жатсаңыз, анда макаланы окуп чыккандан кийин, сиз муну кантип жасоону үйрөнүп гана тим болбостон, кандай шарттарда системадан максималдуу өндүрүмдүүлүктү сыгып аларыңызды билесиз. VLOOKUP функциясы менен бирге колдонулушу керек болгон абдан натыйжалуу ыкмалардын көпчүлүгү каралат.

VLOOKUP функциясын көп жылдардан бери колдонуп жатсаңыз да, бул макала сиз үчүн пайдалуу болот жана сизди кайдыгер калтырбайт. Мисалы, IT адиси, анан IT тармагынын жетекчиси болгондуктан, мен 15 жылдан бери VLOOKUP колдонуп келем, бирок мен Excel программасын профессионалдык негизде элге үйрөтө баштаганда гана бардык нюанстарды чече алдым.

VLOOKUP үчүн аббревиатура болуп саналат v вертикалдуу NS текшерүү. Ошо сыяктуу эле, VLOOKUP - Vertical LOOKUP. Функциянын аталышынын өзү эле бизге анын тилкелерде эмес (горизонталдуу - мамычалардын үстүнөн итерациялоо жана сапты бекитүү) таблицанын саптарында (вертикалдуу - саптардын үстүнөн итерациялоо жана мамычаны бекитүү) издерин көрсөтүп турат. Белгилей кетчү нерсе, VLOOKUP карындашы бар - эч качан ак куу болуп калбай турган көрксүз өрдөк балыгы - бул HLOOKUP функциясы. HLOOKUP, VLOOKUPтан айырмаланып, горизонталдуу издөөлөрдү аткарат, бирок Excel концепциясы (жана чындыгында маалыматтарды уюштуруу концепциясы) таблицаларыңызда азыраак мамычалар жана дагы көп саптар бар экенин билдирет. Ошондуктан биз тилкелерге караганда саптар боюнча көп жолу издөөбүз керек. Эгерде сиз Excelде HLO функциясын өтө көп колдонсоңуз, анда сиз бул жашоодо бир нерсени түшүнгөн жоксуз.

Синтаксис

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

= VLOOKUP (;; [;]), бул жерде:

- керектүү маани (сейрек) же керектүү маанини камтыган уячага шилтеме (иштердин басымдуу көпчүлүгү);

- уячалардын диапазонуна шилтеме (эки өлчөмдүү массив), анын БИРИНЧИ (!) тилкесинде параметрдин мааниси изделүүчү;

- маани кайтарыла турган диапазондогу тилке номери;

- бул диапазондун биринчи тилкеси өсүү тартибинде иреттелгенби деген суроого жооп берген абдан маанилүү параметр. Эгерде массив иреттелген болсо, анда биз TRUE же 1 маанисин белгилейбиз, антпесе - ЖАЛГАН же 0. Бул параметр алынып салынса, ал демейки 1ге айланат.

VLOOKUP функциясын билгендердин көбү төртүнчү параметрдин сыпаттамасын окугандан кийин өздөрүн ыңгайсыз сезиши мүмкүн деп ишенем, анткени алар аны бир аз башкача формада көрүүгө көнүп калышкан: адатта алар так дал келүү жөнүндө айтып жатышат. издөө (ЖАЛГАН же 0) же диапазонду сканерлөө (TRUE же 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де камтылган LOOKUP функциясына тиешелүү.

Формуланын кемчиликтери

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 функциясы менен иштөө үчүн абдан ыңгайлуу.

Мисалы: = 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 ичиндеги COLUMN () функциясын автоматтык түрдө чыгарыла турган мамычалардын сандарын эсептөө үчүн колдонсоңуз болот. Бул учурда, бардык VLOOKUP формулалары бирдей болот (автоматтык түрдө өзгөргөн биринчи параметр үчүн туураланган)! Биринчи параметрдин абсолюттук мамычанын координаты бар экенине көңүл буруңуз.

& "|" менен курама ачкыч түзүү &

Эгер бир эле учурда бир нече тилке боюнча издөө зарыл болуп калса, анда издөө үчүн курама ачкыч жасоо керек. Эгерде кайтаруу мааниси тексттик эмес ("Код" талаасындагыдай), бирок сандык болсо, анда бул үчүн ыңгайлуураак SUMIFS формуласы ылайыктуу болмок жана композиттик мамычанын ачкычы такыр талап кылынбайт.

Бул менин Lifehacker үчүн биринчи макалам. Эгер сизге жакса, мен сизди зыярат кылууга чакырам, ошондой эле VLOOKUP функциясын жана башка ушул сыяктууларды колдонуу сырлары жөнүндө комментарийлерден кубаныч менен окуйм. Рахмат.:)