Мундариҷа
Мо ду ҷадвал дорем (масалан, версияҳои кӯҳна ва нави нархнома), ки мо бояд онҳоро муқоиса кунем ва фарқиятро зуд пайдо кунем:
Дарҳол маълум мешавад, ки ба нархномаҳои нав чизе илова шудааст (хурма, сирпиёз ...), чизе нопадид шудааст (сиёҳ, малина ...), нархҳо барои баъзе молҳо (анҷир, харбуза ...). Шумо бояд ҳамаи ин тағиротҳоро зуд пайдо кунед ва намоиш диҳед.
Барои ҳама гуна вазифаҳо дар Excel қариб ҳамеша зиёда аз як ҳалли (одатан 4-5) мавҷуд аст. Барои мушкилоти мо, усулҳои гуногун метавонанд истифода шаванд:
- функсия VPR (ИНТИЗОР) — номҳои маҳсулотро аз феҳристи нархҳои нав дар пешинаи кӯҳна ҷустуҷӯ кунед ва дар паҳлӯи нархҳои нав нархи кӯҳнаро нишон диҳед ва сипас фарқиятҳоро дарёфт кунед
- ду рӯйхатро ба як муттаҳид кунед ва сипас дар асоси он ҷадвали пивот созед, ки дар он фарқиятҳо ба таври равшан намоён хоҳанд шуд
- Иловаи 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+Alt+F5 ё бо тугма Ҳамаро нав кунед (Ҳамаашро навсозӣ кунед) ҷадвали Маълумот (Сана).
тарафдор: Шояд роҳи зеботарин ва қулайтарин аз ҳама. Бо мизҳои калон оқилона кор мекунад. Ҳангоми тағир додани андозаи ҷадвал таҳрирҳои дастӣ талаб намекунад.
Омӯз: Барои насб кардани замимаи Power Query (дар Excel 2010-2013) ё Excel 2016 талаб мекунад. Номҳои сутун дар маълумоти манбаъ набояд тағир дода шаванд, вагарна мо хатои "Сутуни фалонӣ ёфт нашуд!" ҳангоми кӯшиши навсозии дархост.
- Чӣ тавр ҷамъ кардани маълумот аз ҳама файлҳои Excel дар ҷузвдони додашуда бо истифода аз Power Query
- Чӣ тавр пайдо кардани мувофиқати байни ду рӯйхат дар Excel
- Якҷоя кардани ду рӯйхат бе такрорӣ