змест
У нас ёсць дзве табліцы (напрыклад, старая і новая версіі прайс-ліста), якія трэба параўнаць і хутка знайсці адрозненні:
Адразу відаць, што ў новы прайс-ліст нешта дадалі (фінікі, часнок…), нешта зніклі (ажына, маліна…), на некаторыя тавары змяніліся кошты (інжыр, дыні…). Вам трэба хутка знайсці і адлюстраваць усе гэтыя змены.
Для любой задачы ў Excel практычна заўсёды ёсць больш чым адно рашэнне (звычайна 4-5). Для нашай праблемы можна выкарыстоўваць мноства розных падыходаў:
- функцыя ВПР (ВПР) — шукаць назвы тавараў з новага прайс-ліста ў старым і паказваць стары кошт побач з новым, а потым лавіць адрозненні
- аб'яднаць два спісы ў адзін, а затым пабудаваць на яго аснове зводную табліцу, дзе адрозненні будуць добра бачныя
- выкарыстоўваць надбудову Power Query для Excel
Разбяром іх усіх па парадку.
Спосаб 1. Параўнанне табліц з дапамогай функцыі VLOOKUP
Калі вы зусім не знаёмыя з гэтай цудоўнай асаблівасцю, то спачатку зазірніце сюды і прачытайце або паглядзіце відэаўрок па ёй - зэканоміце сабе пару гадоў жыцця.
Як правіла, гэтая функцыя выкарыстоўваецца для атрымання даных з адной табліцы ў іншую шляхам супастаўлення нейкага агульнага параметру. У гэтым выпадку мы будзем выкарыстоўваць яго, каб перанесці старыя цэны на новыя:
Тыя прадукты, у дачыненні да якіх выявілася памылка #N/A, адсутнічаюць у старым спісе, г.зн. былі дададзеныя. Змены коштаў таксама добра бачныя.
Прафесіяналы гэты метад: просты і зразумелы, «класіка жанру», як кажуць. Працуе ў любой версіі Excel.
мінусы таксама ёсць. Для пошуку тавараў, дададзеных у новы прайс-ліст, вам трэба зрабіць тую ж працэдуру ў адваротным кірунку, г.зн. падцягнуць новыя цэны да старых з дапамогай VLOOKUP. Калі заўтра памеры табліц зменяцца, то і формулы давядзецца карэктаваць. Ну, а на сапраўды вялікіх табліцах (> 100 тысяч радкоў) усё гэтае шчасце будзе прыстойна тармазіць.
Спосаб 2: Параўнанне табліц з выкарыстаннем зводнай табліцы
Скапіруем нашы табліцы адну пад іншую, дадаўшы слупок з назвай прайс-ліста, каб потым было зразумець, з якога спісу які радок:
Цяпер на аснове створанай табліцы створым зводку праз Устаўка – зводная табліца (Устаўка — зводная табліца). Давайце кінем поле прадукт да вобласці ліній, палі цана да вобласці калоны і поля ЦЕСА у дыяпазон:
Як бачыце, зводная табліца аўтаматычна сфармуе агульны спіс усіх тавараў са старога і новага прайс-лістоў (без паўтораў!) і адсартуе тавары ў алфавітным парадку. Вы можаце выразна бачыць дададзеныя прадукты (яны не маюць старой цаны), выдаленыя прадукты (яны не маюць новай цаны) і змены коштаў, калі такія маюцца.
Сумы ў такой табліцы не маюць сэнсу, і іх можна адключыць на ўкладцы Канструктар – агульныя вынікі – адключыць для радкоў і слупкоў (Дызайн — агульны вынік).
Калі змяняюцца цэны (але не колькасць тавараў!), то дастаткова проста абнавіць створаную зводку, пстрыкнуўшы па ёй правай кнопкай мышы – абнаўленне.
Прафесіяналы: Гэты падыход на парадак хутчэйшы з вялікімі табліцамі, чым 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, выкарыстоўваючы тую ж кнопку зачыніць і спампаваць (Зачыніць і загрузіць) таб Галоўная (Дадому):
Beauty.
Больш за тое, калі ў далейшым у прайс-лістах адбудуцца якія-небудзь змены (дадаюцца або выдаляюцца радкі, змяняюцца цэны і г.д.), то дастаткова будзе проста абнавіць нашы запыты з дапамогай спалучэння клавіш Ctrl+Alt+F5 або кнопкай Абнавіць усё (Абнавіць усё) таб Дата (Дата).
Прафесіяналы: Мабыць, самы прыгожы і зручны спосаб з усіх. Спрытна працуе з вялікімі сталамі. Не патрабуе ручных правак пры змене памеру табліц.
мінусы: Патрабуецца ўстаноўка надбудовы Power Query (у Excel 2010-2013) або Excel 2016. Назвы слупкоў у зыходных дадзеных нельга мяняць, інакш мы атрымаем памылку «Калонка такая-то не знойдзена!» пры спробе абнавіць запыт.
- Як сабраць дадзеныя з усіх файлаў Excel у дадзенай тэчцы з дапамогай Power Query
- Як знайсці супадзенні паміж двума спісамі ў Excel
- Аб'яднанне двух спісаў без дублікатаў