Съдържание
Имаме две таблици (например старата и новата версия на ценовата листа), които трябва да сравним и бързо да намерим разликите:
Веднага става ясно, че нещо е добавено към новата ценова листа (фурми, чесън ...), нещо е изчезнало (къпини, малини ...), цените са променени за някои стоки (смокини, пъпеши ...). Трябва бързо да намерите и покажете всички тези промени.
За всяка задача в Excel почти винаги има повече от едно решение (обикновено 4-5). За нашия проблем могат да се използват много различни подходи:
- функция ВПР (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+Друг+F5 или чрез бутон Опресни всички (Опресни всички) етикет Дата (Дата).
Професионалисти: Може би най-красивият и удобен начин от всички. Работи интелигентно с големи маси. Не изисква ръчни редакции при преоразмеряване на таблици.
Против: Изисква добавката Power Query (в Excel 2010-2013) или Excel 2016 да бъде инсталирана. Имената на колоните в изходните данни не трябва да се променят, в противен случай ще получим грешка „Колона такава и такава не е намерена!“ когато се опитвате да актуализирате заявката.
- Как да събирате данни от всички Excel файлове в дадена папка с помощта на Power Query
- Как да намерите съвпадения между два списъка в Excel
- Обединяване на два списъка без дубликати