Как да използвате VLOOKUP Excel? Excel функцията в Excel за манекени и не само

Всеки човек, който се занимава с номера, независимо дали за професионален интерес или просто за любов към изкуството, разбира се, първото нещо е да овладее калкулатора. Какво следва? На следващо място е находчивата програма за работа с числа - Excel. Вероятно е невъзможно да се познае тази програма перфектно, както и да се познава перфектно математиката. Въпреки това, има основни функции в програмата, функцията на Excel, знаейки, че с 90% увереност можем да говорим за способността да се работи с данни на ниво уверен потребител. Една от най-важните функции в Excel е VLOOKUP, която е много гъвкава. Как да използваме VLOOKUP в Excel (за манекени) е описано в статията. Разбира се, възприемането на теорията няма да доведе до пълно разбиране на процеса на използване на функцията. Необходимо е първо да се практикуват малки масиви от данни, а след това на таблици от всякакъв размер.

Същността и целта на функцията CDF в Excel

Функцията CDF спестява много време за такива специалисти като икономисти, финансисти, счетоводители, технолози. Описанието на функцията VLOOKUP в Excel и пример за неговото прилагане трябва да се започне, като се вземе предвид ситуацията, която често срещат експерти от различни видове в работата си. Ако има две таблици, в които се появяват еднакви дефинирани данни (например имена на стоки), но дефинициите за тях в различни таблици се различават, например в едно има количество, а в друга - цена. В същото време, задачата е да се съчетаят всички тези данни и да се направят съответните изчисления - в такива ситуации функцията CDF идва на помощ. За да разберем как VLOOKUP работи в Excel, по-долу са представени две таблици. Във всяка от тях има код на продукта. В едно има подходящо количество, а в другото - цената. За заместване на цените в първата таблица и използваните CDF.

Ръчната подмяна на данни между таблиците е огромна загуба на време и усилия на служителя, грешките не могат да бъдат избегнати. С помощта на CDF можете да намерите съответните стойности бързо и лесно. За дълбоко разбиране за това как да се използва VLOOKUP в Excel, е необходимо да се анализират всички аргументи на функциите.

Аргументи на функцията

За да използвате функцията, е необходимо да стоите в резултата от клетката, изберете раздела Формули - "Връзки и масиви" - "CDF" на лентата. В клетката се появи надпис "= VLR". Сега трябва да въведете правилно аргументите на функцията.Това може да направите чрез точка и запетая директно във формулата.Въпреки това е по-удобно за начинаещия потребител да направи това чрез диалоговия прозорец на функционалните аргументи.

Функцията CDF има четири аргумента - стойността, която търсите, таблицата, номера на колоната, търсенето на интервал. Изискват се първите три, маркирани с получер шрифт в диалоговия прозорец. Обясняваме какви са аргументите на функцията:

• търсената стойност е това, което трябва да се търси;
• маса - къде да търсите;
• номер на колона - в коя колона да се търси;
• интервал - сортиране.

Аргумент "търсена стойност"

В първото поле трябва да въведете това, което трябва да търсите. Може да има номер, текст, дата. Можете да въведете абсолютна стойност, можете да поставите препратка към клетка с желаната стойност. Търсената стойност трябва теоретично да присъства в двете таблици. В примера по-горе, такава стойност може да бъде продуктов код. За да се избегнат грешки, по-добре е да използвате връзката.

Когато използвате текста като желана стойност, той трябва да бъде в кавички. Когато се отнася за клетка с текста, кавичките не са необходими. Регистрирайте се, когато въвеждате текст, няма значение. Може да се използва размито търсене, базирано на фрагмент от текст. За да направите това, в кавичките трябва да поставите част от текста в * знаци.

Най-често срещаният проблем при използването на функцията е различният формат на клетките в диапазоните. Ако в полето на първия аргумент се постави връзка към клетката, където данните са в текстов формат, а в диапазона на търсене данните ще бъдат в цифров формат, то няма да намери съвпадение с външната идентичност на CDF стойностите.

Преобразуването в цифров формат е лесно - трябва да умножите масива с един.

Табличен аргумент

Тук трябва да въведете диапазона, където функцията ще търси първата въведена стойност. Диапазонът за намиране на стойности ще бъде първата колона на избраната област. Стойността, която искате да намерите и поставите като резултат, трябва да бъде в колоната вдясно от графата за търсене на съвпадение. Това е един от ключовите недостатъци при използването на CDF: за да може да работи с нея, таблицата често трябва да бъде възстановена, така че да бъдат оставени необходимите данни. Обхватът може да бъде въведен както ръчно, така и чрез справка. В примера тя изглежда така:

Обхватът тук е правилната таблица. Търсената стойност в колоната вдясно, където Excel ще търси стойността 3187849428, стойността, която трябва да се намери и замени в резултат на формулата, е отляво. За да получите точен резултат, по-добре е да фиксирате обхвата, да го изберете и да натиснете клавиша F4, връзката към масива ще стане абсолютна.

Аргумент "номер на колона"

Тук е необходимо да се постави цифра, в която в колоната на колоната, от най-лявата, е необходимо да се вземе стойността за заместването като резултат от смятането. В горния пример, това е втората колона, в низ от аргументи трябва да поставите числото 2. Ако между колоните Код и Цена има друга колона, тогава ще трябва да поставите числото 3 и т.н.

Аргумент за предварителен преглед на интервала

Попълването на това поле е по избор, но може да бъде много важно. Може да има една от двете стойности - 1 (истина) или 0 (невярна). Повечето потребители смятат, че функцията на този аргумент е да определи точността на съвпадение на желаните стойности. Това не е напълно правилно. При задаване на единица, ако има повторения в обхвата на таблицата, функцията ще върне последната намерена стойност. В този случай функцията VLR ще вземе предвид всички стойности, по-малки или равни на тези, въведени в полето "Необходима стойност". Ако функцията намери по-голяма стойност, но не намери по-малка или еднаква стойност, тя ще генерира грешка N / A.

Можете да видите, че номерът 3187849425 е въведен в полето "Стойност", няма такава стойност в желания диапазон и програмата, намираща всички стойности по-малки или равни на желаната, връща стойността, съответстваща на последния подходящ код в списъка - 3187848593, чиято цена е 2479,46 рубли. Ако полето за преглед на интервала е оставено празно, функцията ще работи по същия начин, както стойността на устройството.

Когато стойността 0 е въведена в полето за аргументи, функцията ще върне само стойността, съответстваща на изискваната. Ако има повторение в обхвата на търсене, функцията ще поеме първото съвпадение. Когато използвате формула с аргумент 0, функцията работи много по-дълго, но много по-точно.

Характеристики на използването на CDF при няколко условия

Използването на функцията CDF често е творчески процес, който изисква математическо мислене от потребителя. Често има нужда да се намери съвпадение не за една колона, а за две или дори повече. С помощта на допълнителни действия може да се приложи и функцията VLOOKUP. Необходимо е да се създадат допълнителни колони в двете таблици, където да се комбинират данни от разглежданите колони. Това може да стане с помощта на функцията "CLUTCH" или иконата "&".

Как да използвате функцията CDF, ако данните са на различни листове

Как да използвате VLOOKUP в Excel на различни листове, в различни файлове? Често потребителите създават справочници, с които е необходимо да сравняват и намират съответствия. Разликите в работата в такива условия се състоят в малко по-различна форма на полето "Таблица" в прозореца с функционални аргументи. Преди да влезете в обхвата е необходимо да поставите номера на листа (ако данните са в една книга) или името на файла (ако данните не са в една книга). Ако в горния пример ценовата листа се копира в отделен файл, наречете го "Цена" и, използвайки VPR функцията, потърсете там цени, тя ще изглежда така:

Всички горепосочени действия могат да бъдат извършени чрез връзката. Трябва да поставите курсора в полето "Таблица", да отидете на желания файл и да изберете обхвата с мишката. Понякога се случва такава таблица да не се вмъква като връзка в прозорец. След това трябва да направите следното: отворете оригиналната таблица, където трябва да направите изчисления, след това през менюто "File" - "Open" намерете втората таблица. Файловете, отворени по този начин, работят гладко.

Всички горепосочени действия могат да бъдат извършени чрез връзката. Трябва да поставите курсора в полето "Таблица", да отидете на желания файл и да изберете обхвата с мишката. Понякога се случва такава таблица да не се вмъква като връзка в прозорец. След това трябва да направите следното: отворете оригиналната таблица, където трябва да направите изчисления, след това през менюто "File" - "Open" намерете втората таблица. Файловете, отворени по този начин, работят гладко.

За претегляне на файла, в който има формули с CDF, с позоваване на други файлове ще бъде много повече, отколкото без тях. Това може да бъде проблематично, например прехвърляне на файлове. За да избегнете тези проблеми, трябва да преобразувате формулата в стойности. На лентата изберете подменюто "Данни" и командата "Промяна на комуникацията". Дори ако данните не са взети от друг файл, винаги е полезно да се заменят формулите със стойности - това прави изчисленията по-надеждни.

Падащ списък за улесняване на работата с CDF

Често функцията VLR не работи, ако има незначителни несъответствия в данните. Това допълнително пространство в текста, данните, въведени с грешки. За да избегнете всички тези проблеми, използвайте падащия списък в Excel, за да въведете стойности. Има смисъл да го стартирате, когато работите с постоянно повтарящи се данни. Ако има определена справочна книга, която се използва като таблица за сравнение, тогава диапазонът на сравнението може да бъде взет като данни за падащ списък и да се използва за оформяне на таблица, в която след това данните ще бъдат вмъкнати в CDF функцията. Курсорът се поставя в клетката, в която трябва да въведете стойност. По-нататък върху лентата има подраздел "Данни", като е избрана командата "Проверка на данни". В диалоговия прозорец в полето "Тип данни" въведете стойността "Списък". В полето "Източник" се записва обхватът на референтната таблица. Падащият списък се оформя. Сега, когато попълвате таблицата, се гарантира пълно съответствие на стойностите.

Пример за използване на функцията VLOOKUP в Excel

Как да използвате VLOOKUP в Excel, всеки потребител сам решава, въз основа на практически задачи в работата си. Като се вземат предвид всички изброени по-горе нюанси, предишният пример може да бъде доведен до логическия му край и да се изгради удобен и ясен алгоритъм за прилагане на CDF функцията на практика. Така че има оценка на разходите за резервни части за ремонт на оборудване. Има и ценова листа за резервни части. Икономистът на компанията е длъжен да намери разходите за резервни части за ремонт.

Първо трябва правилно да напишете формулата.

Така, използвайки функцията VLOOKUP, цената ще се появи съответстваща на кода в първия ред. Необходимо е да умножите формулата надолу, за да направите това, изберете клетката с формулата и издърпайте квадратчето в десния ъгъл на клетката. В колона D функцията връща цените, съответстващи на кодовете. Необходимо е да замените формулите със стойности. За да направите това, изберете запълнените клетки от колона D, копирайте ги и ги поставете като стойности. След това трябва да създадете колона "Сума", където да въведете формулата за произведението на количеството и цената, след което да използвате сумирането, за да обобщите разходите.

Това е пример за това как VLOOKUP работи в Excel.

Грешки при използване на CDF функцията

В началния етап на използване, вместо желаните стойности, функцията често посочва различни типове грешки. Да се ​​знае какво означава грешка е правилният начин да се поправи бързо. Най-често извежданите грешки:

• "N / A" - най-често срещаният тип грешка. Може да възникне по няколко причини.

1. Колоната, на която функцията търси съвпадение, е неправилно разположена (тя трябва да е най-лява). Ако възникне ситуация, при която желаната стойност е вляво от областта на мача, таблицата трябва да бъде преобразувана. Например, копирайте желаната колона и го поставете в дясно от областта за търсене.
2. Грешката "N / A" може да бъде върната, ако диапазонът на търсене не е фиксиран, докато дърпате WFD формулата.
3. Ако точното търсене е зададено с аргумента Interval View (цифрата 0 е зададена), грешката "N / A" се връща, ако няма точно съвпадение в двете таблици.
4. Аргументът Interval Viewing е зададен на най-близката стойност (1 е посочен или полето не е попълнено) и обхватът, в който се извършва търсенето, не е сортиран. В случай на неточно търсене е необходимо да се сортира най-лявата колона от диапазона за търсене.
5. Сравнените данни имат допълнителни интервали (за да ги премахнете, можете да използвате функцията "SIFPROBELS", прилагайки я към таблицата и желаната стойност), различен формат, допълнителни кавички. За да запишете едни и същи стойности в двете таблици, има смисъл да използвате падащия списък.

• "LINK" - тази грешка често възниква, когато номерът на колоната е указан неправилно, ако толкова колони не са в избрания диапазон. В такива случаи е необходимо да се помни, че номерът се записва, като се брои от лявата колона на избрания диапазон, а не таблицата като цяло.
• „NAME“ - грешката често се връща, когато текстът е въведен в „Необходимата стойност“. Текстът трябва да бъде написан в кавички.

Знания за това как да се използва VLOOKUP в Excel, идват, както всичко останало в тази програма, с практиката. Самото изследване на теорията, вместо яснотата, може да доведе до хаос в разбирането на конкретна операция в програмата. При изучаването на всяка функция на Excel винаги е по-ефективно за потребителя да разчита на конкретна, макар и малка, примерна таблетка. Това ви позволява да разберете по-добре същността на анализа, да затвърдите придобитите знания. Така че с функцията CDF в Excel. С голямо количество теория, практическото приложение показва, че то не е толкова сложно, колкото е полезно.