Садржај
Имамо две табеле (на пример, стару и нову верзију ценовника), које треба да упоредимо и брзо пронађемо разлике:
Одмах се види да је нешто додато на нови ценовник (хурме, бели лук…), нешто је нестало (купине, малине…), цене неке робе су промењене (смокве, диње…). Морате брзо пронаћи и приказати све ове промене.
За било који задатак у Екцел-у, скоро увек постоји више од једног решења (обично 4-5). За наш проблем се може користити много различитих приступа:
- функција ВПР (ВЛООКУП) — потражите називе производа из новог ценовника у старом и прикажите стару цену поред нове, а затим ухватите разлике
- спојите две листе у једну, а затим направите стожерну табелу на основу ње, где ће разлике бити јасно видљиве
- користите Повер Куери додатак за Екцел
Узмимо их све редом.
Метод 1. Поређење табела са функцијом ВЛООКУП
Ако нисте потпуно упознати са овом дивном функцијом, онда прво погледајте овде и прочитајте или погледајте видео водич о њој – уштедите себи неколико година живота.
Обично се ова функција користи за повлачење података из једне табеле у другу тако што се подудара са неким заједничким параметром. У овом случају, користићемо га да гурнемо старе цене у нову цену:
Они производи, против којих се испоставила грешка #Н/А, нису на старој листи, односно додати су. Промене цена су такође јасно видљиве.
Прозодија овај метод: једноставан и јасан, "класика жанра", како кажу. Ради у било којој верзији Екцел-а.
Против је такође ту. За тражење производа додатих на нови ценовник, мораћете да урадите исту процедуру у супротном смеру, тј. повући нове цене на стару цену уз помоћ ВЛООКУП-а. Ако се сутра промене величине табела, онда ће формуле морати да се подесе. Па, и на заиста великим табелама (> 100 хиљада редова), сва ова срећа ће се пристојно успорити.
Метод 2: Упоређивање табела користећи пивот
Препишимо наше табеле једну испод друге, додајући колону са називом ценовник, да касније разумете из које листе у ком реду:
Сада, на основу креиране табеле, направићемо резиме Уметање – заокретна табела (Инсерт — Пивот Табле). Хајде да бацимо поље производ на област линија, поље Cena на област колоне и поље ЦЕна у опсегу:
Као што видите, стожерна табела ће аутоматски генерисати општу листу свих производа са старог и новог ценовника (без понављања!) и сортирати производе по абецедном реду. Можете јасно да видите додане производе (немају стару цену), уклоњене производе (немају нову цену) и промене цене, ако их има.
Укупни износи у таквој табели немају смисла и могу се онемогућити на картици Конструктор – Укупан зброј – Онемогући за редове и колоне (Дизајн — укупни збројеви).
Ако се цене промене (али не и количина робе!), довољно је само да ажурирате креирани резиме десним кликом на њега – Освежити.
Прозодија: Овај приступ је за ред величине бржи са великим табелама него ВЛООКУП.
Против: потребно је ручно копирати податке један испод другог и додати колону са називом ценовника. Ако се величине столова промене, онда морате све поновити.
Метод 3: Поређење табела са Повер Куери-јем
Повер Куери је бесплатни додатак за Мицрософт Екцел који вам омогућава да учитате податке у Екцел из готово било ког извора, а затим трансформишете ове податке на било који жељени начин. У програму Екцел 2016, овај додатак је већ подразумевано уграђен на картици Датум (Подаци), а за Екцел 2010-2013 потребно је да га преузмете одвојено са Мицрософт веб локације и да га инсталирате – набавите нову картицу Повер Куери.
Пре него што учитате наше ценовнике у Повер Куери, они прво морају бити конвертовани у паметне табеле. Да бисте то урадили, изаберите опсег са подацима и притисните комбинацију на тастатури Цтрл+T или изаберите картицу на траци Почетна – Формат као табела (Почетна — Формат као табела). Називи креираних табела се могу кориговати на картици Конструктор (Напустићу стандард Табела КСНУМКС и Табела КСНУМКС, који се добијају подразумевано).
Учитајте стару цену у Повер Куери помоћу дугмета Из табеле/опсега (Из табеле/опсега) са картице Датум (Датум) или са картице Повер Куери (у зависности од верзије Екцел-а). Након учитавања, вратићемо се у Екцел из Повер Куерија са командом Затвори и учитај – Затвори и учитај у… (Затвори и учитај — Затвори и учитај у…):
… и у прозору који се појави изаберите Само створите везу (Само веза).
Поновите исто са новим ценовником.
Сада направимо трећи упит који ће комбиновати и упоређивати податке из претходна два. Да бисте то урадили, изаберите у Екцел-у на картици Подаци – Добијте податке – Комбинујте захтеве – Комбинујте (Подаци — Добијте податке — Обједините упите — Обједините) или притисните дугме Комбиновати (Спајање) табулатор Повер Куери.
У прозору за спајање изаберите наше табеле у падајућим листама, изаберите колоне са називима робе у њима и на дну подесите метод спајања – Комплетна екстерна (Пуна спољна):
После клика на OK требало би да се појави табела са три колоне, где у трећој колони треба да проширите садржај угнежђених табела помоћу двоструке стрелице у заглављу:
Као резултат, добијамо спајање података из обе табеле:
Боље је, наравно, преименовати називе колона у заглављу двоструким кликом на разумљивије:
А сада најзанимљивије. Иди на картицу Додај колону (Додај колону) и кликните на дугме Условна колона (условна колона). А затим у прозору који се отвори унесите неколико услова тестирања са одговарајућим излазним вредностима:
Остаје да кликнете на OK и отпремите резултујући извештај у Екцел користећи исто дугме затворите и преузмите (Затвори и учитај) табулатор Početna (Кућа):
Лепота.
Штавише, ако убудуће дође до било каквих промена у ценовницима (додавање или брисање редова, промена цена итд.), биће довољно само да ажурирамо наше захтеве помоћу пречице на тастатури Цтрл+други+F5 или дугметом Освежи све (Освежи све) табулатор Датум (Датум).
Прозодија: Можда најлепши и најпогоднији начин од свих. Паметно ради са великим столовима. Не захтева ручне измене приликом промене величине табела.
Против: Захтева да се инсталира Повер Куери додатак (у Екцел 2010-2013) или Екцел 2016. Називи колона у изворним подацима се не смеју мењати, иначе ћемо добити грешку „Колона таква и таква није пронађена!“ када покушавате да ажурирате упит.
- Како прикупити податке из свих Екцел датотека у датој фасцикли користећи Повер Куери
- Како пронаћи подударања између две листе у Екцелу
- Спајање две листе без дупликата