Със сигурност много от активните потребители на Excel-та редактора на електронни таблици понякога трябваше да се справят със ситуации, в които е необходимо да се заменят стойности от една таблица в друга. Само си представете, че някой артикул е дошъл във вашия склад. Разполагаме с два файла: един с списък с имената на получените стоки, а вторият - с ценовата листа на самия продукт. След като отворим ценовата листа, откриваме, че в нея има повече позиции и те не се намират в последователността, която е във файла със списъка с имена. Малко вероятно е някой от нас да хареса идеята за ръчно преглеждане на файлове и прехвърляне на цени от един документ на друг. Разбира се, когато става въпрос за 5-10 позиции, механичното въвеждане на данни е напълно възможно, но какво, ако броят на имената надвишава 1000? В този случай, Excel и неговата магическа CDF функция (или vlookup, ако говорим за английската версия на програмата) ще ни помогне да се справим с монотонната работа.
VPR ви позволява да работите с големи таблици, като прехвърляте данни от едно към друго
Какво е CDF и как да го използвам?
Така че, в началото на нашата работа по преобразуване на данни от една таблица в друга, ще бъде подходящо да се направи малък преглед на CDF функцията. Както вероятно вече сте разбрали, vlookup ви позволява да прехвърляте данни от една таблица в друга, като по този начин запълвате клетките, от които се нуждаем автоматично. За да може функцията CDF да работи правилно, обърнете внимание на присъствието на обединени клетки в заглавките на вашата таблица. Ако има такива, ще трябва да ги счупите.
Да предположим, че трябва да попълните таблицата с поръчки с данните от „Ценова листа“
Така че, изправени сме пред задачата - да прехвърлим цените на съществуващите продукти на таблица с техните имена и да изчислим общата стойност на всеки продукт. За да направите това, трябва да изпълним следния алгоритъм:
- Първо, въведете електронната таблица на Excel във формата, която искате. Добавете към подготвената матрица данни две колони с имената “Цена” и “Цена”. Изберете за клетките в диапазона от новообразувани колони, паричния формат.
- Сега активирайте първата клетка в блока "Цена" и извикайте "Wizard" на функцията. Това може да стане чрез натискане на бутона “fx”, намиращ се пред линията на формулата, или чрез задържане на клавишната комбинация “Shift + F3”. В диалоговия прозорец, който се отваря, намерете категорията "Връзки и масиви". Тук не ни интересува нищо друго освен функцията на CDF. Изберете го и натиснете „OK“. Между другото, трябва да се каже, че функцията VLOOKUP може да бъде извикана чрез раздела “Формули”, в падащия списък, в който се намира и категорията “Връзки и масиви”.
- След активирането на CDF ще видите прозорец със списък на аргументите на избраната от вас функция. В полето „Необходима стойност“ ще трябва да въведете диапазона от данни, съдържащ се в първата колона на таблицата, със списъка на получените стоки и тяхното количество. Това означава, че трябва да кажете на Excel какво точно трябва да се намери във втората таблица и да се прехвърли на първата.
- След маркирането на първия аргумент можете да отидете на втория. В нашия случай вторият аргумент е таблица с ценова листа. Поставете курсора на мишката в полето за аргументи и преминете към списъка с цени. Ръчно изберете диапазона с клетки, разположени в областта на колоните с имената на търговските продукти и тяхната цена. Определете Excel, кои стойности искате да картографирате VLOOKUP функциите.
- За да не се обърка Excel и да се позове на необходимите ви данни, е важно да се определи връзката, дадена на нея. За да направите това, маркирайте необходимите стойности в полето Таблица и натиснете клавиша F4. Ако всичко е направено правилно, знакът $ трябва да се появи на екрана.
- Сега отиваме в полето на аргумента за номер на страница и го поставяме на стойност “2”. Този блок съдържа всички данни, които искате да изпратите на нашата работна таблица, и затова е важно да присвоите лъжлива стойност на Interval View (задайте позиция на FALSE). Това е необходимо, за да може функцията VLR да работи само с точни стойности, а не около тях.
Попълнете формулата за първата колона.
Сега, след като всички необходими действия са завършени, можем да ги потвърдим само с натискане на бутона „OK“. Веднага след като данните в първата клетка се променят, ще трябва да приложим функцията CDF към целия документ на Excel. За да направите това, достатъчно е да разпространите VLOOKUP през цялата колона „Цена“. Това може да стане чрез плъзгане на долния десен ъгъл на клетката с модифицираната стойност до най-долната част на колоната. Ако всичко се изработи и данните се променят, колкото е необходимо, можем да преминем към изчисляване на общата стойност на нашите стоки. За да извършим това действие, трябва да намерим продукта от две колони - “Количества” и “Цени”. Тъй като Excel съдържа всички математически формули, изчислението може да бъде предоставено в "Формула Бар", използвайки познатата икона "fx".
Важен момент
Изглежда, че всичко е готово и VLOOKUP се справи с нашата задача, но не беше там. Факт е, че в колоната „Цена“ функцията CDF е все още активна, доказателство за този факт е показването на последната във формулата. Това означава, че и двете ни таблици остават свързани един с друг. Такъв тандем може да доведе до факта, че когато данните в таблицата с цената се променят, информацията, която се съдържа в нашия работен файл със списъка на стоките също се променя.
Най-добре е тази ситуация да се избегне чрез разделяне на двете таблици. За да направите това, трябва да изберете клетките, които са в обхвата на колоната Цена, и щракнете с десния бутон върху нея. В отворения прозорец изберете и активирайте опцията "Копиране". След това, без да премахвате избора от избраната клетъчна област, натиснете отново десния бутон на мишката и изберете опцията "Paste Special".
Активирането на тази опция ще отвори диалогов прозорец на екрана, в който ще трябва да поставите отметка в квадратчето до категорията „Стойност“. Потвърдете действията си, като кликнете върху бутона „OK“.
Връщаме се в нашата формула и проверяваме за наличието на активната функция VLOOKUP в колоната Цена. Ако на мястото на формулата виждате само числови стойности, тогава всичко се е получило и функцията CDF е изключена. Това означава, че връзката между двата Excel файла е нарушена и няма заплаха от непланирани промени или заличаване на данни, прикрепени към таблицата с ценовата листа. Сега можете спокойно да използвате табличния документ и да не се притеснявате какво ще се случи, ако ценовата листа бъде затворена или преместена на друго място.
Плъзнете маркера в долния десен ъгъл на клетката, за да приложите формулата към цялата таблица.
Как да сравните две таблици в Excel?
С помощта на функцията CDF можете да сравните няколко различни стойности за няколко секунди, за да сравните, например, как са се променили цените на съществуващ продукт. За да направите това, трябва да регистрирате VLOOKUP в празна колона и да свържете функцията към променените стойности, които са в друга таблица. Най-доброто от всички, ако колоната "Нова цена" ще бъде разположена непосредствено зад колоната "Цена". Такова решение ще ви позволи да направите ценовите промени по-видими за сравнение.
Възможност за работа с няколко условия
Друго безспорно предимство на функцията VLOOKUP е способността му да работи с няколко параметъра, присъщи на вашия продукт. За да намерите продукт с две или повече характеристики, трябва:
- Създайте две (или, ако е необходимо, повече) думи за търсене.
- Добавя се нова колона, към която по време на работата на функцията ще се добавят всички други колони, чрез които се търси продуктът.
- В получената колона, съгласно описания по-горе алгоритъм, въвеждаме функционалната формула VLOOKUP, която вече ни е позната.
В заключение трябва да се каже, че поддържането на Excel на такава функция като CDF значително опростява работата с таблична информация. Не се страхувайте да използвате VLOOKUP при работа с огромно количество данни, защото без значение как се изпълняват, принципът на функцията винаги е един и същ. Всичко, което трябва да направите, е да определите правилно нейните аргументи.