мазмуну
Бизде эки таблица бар (мисалы, прейскуранттын эски жана жаңы версиялары), аларды салыштырып, айырмачылыктарды тез табышыбыз керек:
Жаңы прейскурантка бир нерсе кошулганы (курма, сарымсак…), бир нерсе жок болуп кеткени (кара бүлдүркөн, малина…), кээ бир товарларга (анжир, коон...) баалар өзгөргөнүн дароо эле билүүгө болот. Бул өзгөрүүлөрдүн баарын тез таап, көрсөтүшүңүз керек.
Excelдеги ар кандай тапшырма үчүн дээрлик ар дайым бир нече чечим бар (көбүнчө 4-5). Биздин көйгөй үчүн көптөгөн ар кандай ыкмаларды колдонсо болот:
- милдети VPR (КӨРҮҮ) — эски баанын жаңы прейскуранынан товардын аталыштарын издеңиз жана жаңы баанын жанында эски бааны көрсөтүңүз, андан кийин айырмачылыктарды байкаңыз
- эки тизмени бирине бириктирип, анан анын негизинде пивот таблицасын түзүңүз, анда айырмачылыктар даана көрүнүп турат
- Excel үчүн Power Query кошумчасын колдонуңуз
Алардын баарын ирети менен алалы.
Метод 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 версиясына жараша). Жүктөп алгандан кийин, биз буйрук менен Power Queryден Excelге кайтып келебиз Жабуу жана жүктөө – Жабуу жана жүктөө… (Жабуу жана жүктөө — Жабуу жана жүктөө…):
... жана пайда болгон терезеде тандаңыз Жөн гана байланыш түзүңүз (Туташуу гана).
Жаңы прейскурант менен дагы кайталаъыз.
Эми мурунку экөөнүн маалыматтарын бириктирип, салыштыра турган үчүнчү суроону түзөлү. Бул үчүн, Excel өтмөктөн тандаңыз Маалыматтар - Маалыматтарды алуу - Сурамдарды бириктирүү - Бириктирүү (Маалымат — Маалымат алуу — Суроолорду бириктирүү — Бириктирүү) же баскычты басыңыз бириктирүү (Бириктирүү) табулатура күч суроо.
Кошулуу терезесинде ачылуучу тизмелерден биздин таблицаларды тандап, алардагы товарлардын аталыштары жазылган тилкелерди тандап, ылдый жагында кошулуу ыкмасын орнотуңуз – Толук тышкы (Толук сырткы):
Кийин басуу боюнча OK үч мамычадан турган таблица пайда болушу керек, ал жерде үчүнчү тилкеде баштагы кош жебени колдонуу менен уя салынган таблицалардын мазмунун кеңейтүү керек:
Натыйжада, биз эки таблицадагы маалыматтарды бириктирүүнү алабыз:
Албетте, түшүнүктүүрөөктөрдү эки жолу чыкылдатуу менен баш колонкалардын аталыштарын өзгөртүү жакшы:
Ал эми азыр эң кызыктуусу. Өтмөккө өтүңүз Тилке кошуу (Мамыча кошуу) жана баскычты чыкылдатыңыз Шарттуу тилке (Шарттуу мамыча). Андан кийин ачылган терезеде, тиешелүү чыгуу маанилери менен бир нече сыноо шарттарын киргизиңиз:
Бул басуу үчүн калды OK жана ошол эле баскычты колдонуп, натыйжадагы отчетту Excelге жүктөңүз жабуу жана жүктөп алуу (Жабуу жана жүктөө) табулатура Home (Үй):
Сулуулук.
Мындан тышкары, келечекте прейскуранттарда кандайдыр бир өзгөрүүлөр болсо (саптар кошулуп же жок кылынса, баалар өзгөрсө, ж. Ctrl+Alt+F5 же баскыч менен Баарын жаңыртуу (Баарын жаңылоо) табулатура маалыматтар (Дата).
жакшы: Балким, эң кооз жана ыңгайлуу жол. Чоң столдор менен акылдуу иштейт. Таблицалардын өлчөмүн өзгөртүүдө кол менен оңдоолорду талап кылбайт.
жактары: Power Query кошумчасын (Excel 2010-2013-те) же Excel 2016 орнотууну талап кылат. Булак маалыматындагы тилкелердин аталыштары өзгөртүлбөшү керек, антпесе "Мындай тилке табылган жок!" Ката алабыз. суроону жаңыртуу аракетинде.
- Power Query аркылуу берилген папкадагы бардык Excel файлдарынан маалыматтарды кантип чогултуу керек
- Excelде эки тизменин ортосундагы дал келүүнү кантип тапса болот
- Кайталанбаган эки тизмени бириктирүү