Мазмұны
Бізде екі кесте бар (мысалы, прейскуранттың ескі және жаңа нұсқалары), біз оларды салыстырып, айырмашылықтарды жылдам табуымыз керек:
Жаңа прейскурантқа бірдеңе қосылғаны (құрма, сарымсақ ...), бірдеңе жоғалғаны (қаражидек, таңқурай ...), кейбір тауарлардың бағасы өзгергені (інжір, қауын ...) бірден анық болды. Барлық осы өзгерістерді тез тауып, көрсету керек.
Excel бағдарламасындағы кез келген тапсырма үшін әрқашан бірден көп шешім бар (әдетте 4-5). Біздің мәселеміз үшін көптеген әртүрлі тәсілдер қолданылуы мүмкін:
- функция VPR (КӨРУ) — ескі бағаның жаңа прейскурантынан тауар атауларын іздеңіз және жаңасының жанында ескі бағаны көрсетіңіз, содан кейін айырмашылықтарды ұстаңыз
- екі тізімді біреуіне біріктіріп, соған негізделген жиынтық кестені жасаңыз, мұнда айырмашылықтар анық көрінеді
- Excel бағдарламасына арналған Power Query қондырмасын пайдаланыңыз
Олардың барлығын ретімен алайық.
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 бағдарламасына жүктеңіз жабыңыз және жүктеңіз (Жабу және жүктеу) қойындысы Home (Үй):
Сұлулық.
Сонымен қатар, болашақта баға тізімдерінде қандай да бір өзгерістер орын алса (жолдар қосылса немесе жойылса, бағалар өзгерсе және т.б.), онда біздің сұрауларымызды пернелер тіркесімі арқылы жаңарту жеткілікті болады. Ctrl+Alt+F5 немесе түйме арқылы Барлығын жаңарту (Барлығын жаңарту) қойындысы мәліметтер (Күні).
артықшылықтары: Ең әдемі және ыңғайлы әдіс. Үлкен үстелдермен ақылды жұмыс істейді. Кестелердің өлшемін өзгерту кезінде қолмен өңдеуді қажет етпейді.
Минус: Power Query қондырмасын (Excel 2010-2013 нұсқаларында) немесе Excel 2016 орнатуды талап етеді. Бастапқы деректердегі баған атауларын өзгертуге болмайды, әйтпесе «Осындай баған табылмады!» деген қате пайда болады. сұрауды жаңарту әрекеті кезінде.
- Power Query көмегімен берілген қалтадағы барлық Excel файлдарынан деректерді жинау жолы
- Excel бағдарламасында екі тізім арасындағы сәйкестіктерді қалай табуға болады
- Екі тізімді көшірмесіз біріктіру