содржина
Имаме две табели (на пример, старата и новата верзија на ценовникот), кои треба да ги споредиме и брзо да ги најдеме разликите:
Веднаш е јасно дека нешто е додадено на новиот ценовник (урми, лук…), нешто исчезна (капини, малини…), цените се сменија за некои стоки (смокви, дињи…). Треба брзо да ги пронајдете и прикажете сите овие промени.
За која било задача во Excel, скоро секогаш има повеќе од едно решение (обично 4-5). За нашиот проблем, може да се користат многу различни пристапи:
- функција VPR (VLOOKUP) — побарајте ги имињата на производите од новиот ценовник во стариот и прикажете ја старата цена до новата, а потоа фатете ги разликите
- спојувајте две списоци во една и потоа изградете стожерна табела врз основа на неа, каде што разликите ќе бидат јасно видливи
- користете го додатокот Power Query за Excel
Ајде да ги земеме сите по ред.
Метод 1. Споредување табели со функцијата VLOOKUP
Ако не сте целосно запознаени со оваа прекрасна карактеристика, тогаш прво погледнете овде и прочитајте или гледајте видео туторијал за неа - спасете си неколку години живот.
Вообичаено, оваа функција се користи за повлекување податоци од една табела во друга со совпаѓање на некој заеднички параметар. Во овој случај, ќе го користиме за да ги турнеме старите цени во новата цена:
Оние производи против кои излезе грешката #N/A, ги нема во старата листа, односно се додадени. Промените на цените се исто така јасно видливи.
Добрите овој метод: едноставен и јасен, „класика на жанрот“, како што велат. Работи во која било верзија на Excel.
Конс е исто така таму. За да пребарувате производи додадени во новиот ценовник, ќе треба да ја направите истата постапка во спротивна насока, односно да ги повлечете новите цени до старата цена со помош на VLOOKUP. Ако утре се сменат големините на табелите, тогаш формулите ќе треба да се прилагодат. Па, и на навистина големи маси (> 100 илјади редови), сета оваа среќа пристојно ќе се забави.
Метод 2: Споредување на табели со помош на стожер
Ајде да ги копираме нашите табели една под друга, додавајќи колона со името на ценовникот, за подоцна да разберете од кој список кој ред:
Сега, врз основа на креираната табела, ќе креираме резиме преку Вметнете – Стожерна табела (Вметни - Стожерна табела). Ајде да фрлиме поле Производ до областа на линии, поле Цена до областа и полето на колоната Цena во опсегот:
Како што можете да видите, стожерната табела автоматски ќе генерира општ список на сите производи од старите и новите ценовници (без повторувања!) и ќе ги подреди производите по азбучен ред. Можете јасно да ги видите додадените производи (ја немаат старата цена), отстранетите производи (ја немаат новата цена) и промените на цените, доколку ги има.
Големите збирки во таква табела немаат смисла, и тие можат да се оневозможат на јазичето Конструктор – Големи збирки – Оневозможи за редови и колони (Дизајн - Големи збирки).
Ако цените се менуваат (но не и количината на стоката!), тогаш доволно е едноставно да го ажурирате креираното резиме со десен клик на него - освежување.
Добрите: Овој пристап е побрз по ред на големина со големи табели отколку VLOOKUP.
Конс: треба рачно да ги копирате податоците еден под друг и да додадете колона со името на ценовникот. Ако се променат големините на табелите, тогаш треба да направите сè одново.
Метод 3: Споредување табели со Power Query
Power Query е бесплатен додаток за Microsoft Excel кој ви овозможува да вчитате податоци во Excel од речиси секој извор и потоа да ги трансформирате овие податоци на кој било посакуван начин. Во Excel 2016, овој додаток веќе е стандардно вграден на јазичето податоци (податоци), а за Excel 2010-2013 треба да го преземете одделно од веб-локацијата на Microsoft и да го инсталирате - добијте нов таб Барање за напојување.
Пред да ги вчитате нашите ценовници во Power Query, тие мора прво да се претворат во паметни табели. За да го направите ова, изберете го опсегот со податоци и притиснете ја комбинацијата на тастатурата Ctrl+T или изберете го јазичето на лентата Дома – Форматирајте како табела (Дома - Формат како табела). Имињата на креираните табели може да се коригираат на јазичето Изведувач (Ќе го оставам стандардот Табела 1 и Табела 2, кои се стандардно добиени).
Вчитајте ја старата цена во Power Query користејќи го копчето Од Табела/Опсег (Од табела/опсег) од јазичето податоци (Датум) или од јазичето Барање за напојување (во зависност од верзијата на Excel). По вчитувањето, ќе се вратиме во Excel од Power Query со командата Затвори и вчитај – Затвори и вчитај во… (Затвори и вчитај - затвори и вчитај во…):
… и во прозорецот што се појавува потоа изберете Само создадете врска (Само за поврзување).
Повторете го истото со новиот ценовник.
Сега да создадеме трето барање што ќе ги комбинира и споредува податоците од претходните две. За да го направите ова, изберете во Excel на табулаторот Податоци – Добијте податоци – Комбинирајте барања – Комбинирајте (Податоци - Добијте податоци - Барања за спојување - Спојување) или притиснете го копчето Комбинираат (Спојување) табот Барање за напојување.
Во прозорецот за приклучување, изберете ги нашите табели во паѓачките списоци, изберете ги колоните со имињата на стоките во нив и на дното поставете го методот на спојување - Целосно надворешно (Целосно надворешно):
По кликнување на OK треба да се појави табела од три колони, каде што во третата колона треба да ја проширите содржината на вгнездените табели користејќи ја двојната стрелка во заглавието:
Како резултат на тоа, добиваме спојување на податоците од двете табели:
Подобро е, се разбира, да ги преименувате имињата на колоните во заглавието со двоен клик на поразбирливите:
И сега најинтересното. Одете на јазичето Додај колона (Додај колона) и кликнете на копчето Условна колона (Условна колона). И потоа во прозорецот што се отвора, внесете неколку услови за тестирање со нивните соодветни излезни вредности:
Останува да кликнете на OK и испратете го добиениот извештај во Excel користејќи го истото копче затворете и преземете (Затвори и вчитај) табот Почетна (Дома):
Убавина.
Освен тоа, доколку во иднина се случат какви било промени во ценовниците (се додаваат или бришат линии, цените се менуваат итн.), тогаш ќе биде доволно само да ги ажурираме нашите барања со кратенка на тастатурата Ctrl+Alt+F5 или со копче Освежете ги сите (Освежи ги сите) табот податоци (Датум).
Добрите: Можеби најубавиот и најзгодниот начин од сите. Работи паметно со големи маси. Не бара рачни уредувања при промена на големината на табелите.
Конс: Потребно е да се инсталира додатокот Power Query (во Excel 2010-2013) или Excel 2016. Имињата на колоните во изворните податоци не смеат да се менуваат, инаку ќе ја добиеме грешката „Колоната не е пронајдена таква и таква!“ кога се обидувате да го ажурирате барањето.
- Како да собирате податоци од сите датотеки на Excel во дадена папка користејќи Power Query
- Како да најдете совпаѓања помеѓу две списоци во Excel
- Спојување на две листи без дупликати