İki cədvəlin müqayisəsi

İki cədvəlimiz var (məsələn, qiymət siyahısının köhnə və yeni versiyaları), onları müqayisə etməli və fərqləri tez tapmalıyıq:

Dərhal aydın olur ki, yeni qiymət siyahısına nəsə əlavə olunub (xurma, sarımsaq...), nə isə yoxa çıxıb (böyürtkən, moruq...), bəzi malların qiymətləri dəyişib (əncir, bostan məhsulları...). Bütün bu dəyişiklikləri tez tapıb göstərməlisiniz.

Excel-də hər hansı bir tapşırıq üçün demək olar ki, həmişə birdən çox həll var (adətən 4-5). Problemimiz üçün bir çox fərqli yanaşma istifadə edilə bilər:

• funksiyası VPR (BAXIN) — köhnə qiymət siyahısından məhsul adlarını axtarın və köhnə qiyməti yenisinin yanında göstərin və sonra fərqləri tutun
• iki siyahını birinə birləşdirin və sonra fərqlərin aydın görünəcəyi əsas cədvəl yaradın
• Excel üçün Power Query Add-in istifadə edin

Gəlin hamısını sıra ilə götürək.

Metod 1. VLOOKUP funksiyası ilə cədvəllərin müqayisəsi

Əgər bu gözəl xüsusiyyətlə tanış deyilsinizsə, əvvəlcə bura baxın və bu barədə video dərslik oxuyun və ya baxın - bir neçə il həyatınıza qənaət edin.

Tipik olaraq, bu funksiya bəzi ümumi parametrləri uyğunlaşdırmaqla məlumatları bir cədvəldən digərinə çəkmək üçün istifadə olunur. Bu halda, biz ondan köhnə qiymətləri yeni qiymətə itələmək üçün istifadə edəcəyik:

Qarşısında #N/A xətası çıxan məhsullar köhnə siyahıda yoxdur, yəni əlavə edilib. Qiymət dəyişiklikləri də aydın görünür.

Pros bu üsul: sadə və aydın, necə deyərlər, “janrın klassiki”. Excel-in istənilən versiyasında işləyir.

Eksiler da var. Yeni qiymət siyahısına əlavə edilmiş məhsulları axtarmaq üçün siz eyni proseduru əks istiqamətdə, yəni VLOOKUP köməyi ilə yeni qiymətləri köhnə qiymətə çəkməli olacaqsınız. Sabah cədvəllərin ölçüləri dəyişərsə, o zaman düsturlara düzəlişlər edilməli olacaq. Yaxşı və həqiqətən böyük masalarda (> 100 min sıra), bütün bu xoşbəxtlik layiqincə yavaşlayacaq.

Metod 2: Pivot istifadə edərək cədvəllərin müqayisəsi

Cədvəllərimizi bir-birinin altına köçürük, qiymət siyahısının adı olan bir sütun əlavə edək ki, sonradan hansı siyahıdan hansı sıra olduğunu başa düşə biləsiniz:

İndi yaradılmış cədvəl əsasında xülasə yaradacağıq Daxil et - Pivot Cədvəl (Daxil et - Pivot Cədvəl). Gəlin bir sahə ataq məhsul xətlərin sahəsinə, sahəyə qiymət sütun sahəsinə və sahəsinə ЦENA diapazonda:

Gördüyünüz kimi, pivot cədvəli avtomatik olaraq köhnə və yeni qiymət siyahılarından bütün məhsulların ümumi siyahısını yaradacaq (təkrarlar yoxdur!) və məhsulları əlifba sırası ilə çeşidləyəcək. Əlavə edilmiş məhsulları (onların köhnə qiyməti yoxdur), çıxarılan məhsulları (yeni qiyməti yoxdur) və varsa qiymət dəyişikliklərini aydın görə bilərsiniz.

Belə bir cədvəldəki ümumi məbləğlərin mənası yoxdur və onlar tabda əlil ola bilər Konstruktor – Ümumi cəmi – Satır və sütunlar üçün deaktiv edin (Dizayn - Böyük Toplamlar).

Əgər qiymətlər dəyişirsə (malların miqdarı yox!), onda sadəcə olaraq yaradılmış xülasəni üzərinə sağ klikləməklə yeniləmək kifayətdir – təzələmək.

Pros: Bu yanaşma VLOOKUP-dan daha böyük cədvəllərlə daha sürətli miqyas sırasıdır. 

Eksiler: məlumatları əl ilə bir-birinizin altına köçürməli və qiymət siyahısının adı ilə bir sütun əlavə etməlisiniz. Cədvəllərin ölçüləri dəyişirsə, onda hər şeyi yenidən etməlisiniz.

Metod 3: Power Query ilə cədvəllərin müqayisəsi

Power Query, demək olar ki, istənilən mənbədən Excel-ə məlumat yükləməyə və sonra bu məlumatları istənilən şəkildə çevirməyə imkan verən Microsoft Excel üçün pulsuz əlavədir. Excel 2016-da bu əlavə artıq standart olaraq nişanda quraşdırılmışdır Tarix (Məlumat), və Excel 2010-2013 üçün onu Microsoft veb saytından ayrıca endirməli və quraşdırmalısınız – yeni tab əldə edin Güc sorğusu.

Qiymət siyahılarımızı Power Query-ə yükləməzdən əvvəl, onlar əvvəlcə ağıllı masalara çevrilməlidir. Bunu etmək üçün məlumatları olan diapazonu seçin və klaviaturada birləşməni basın Ctrl+T və ya lentdəki nişanı seçin Ev - Cədvəl kimi formatlayın (Ev - Cədvəl kimi format). Yaradılmış cədvəllərin adları tabda düzəldilə bilər konstruktor (Standartdan ayrılacağam Cədvəl 1 и Cədvəl 2, standart olaraq əldə edilir).

Düyməni istifadə edərək Power Query-də köhnə qiyməti yükləyin Cədvəldən/Aralıqdan (Cədvəldən/Aralıqdan) tabdan Tarix (Tarix) və ya tabdan Güc sorğusu (Excel versiyasından asılı olaraq). Yüklədikdən sonra əmrlə Power Query-dən Excel-ə qayıdacağıq Bağlayın və yükləyin – Bağlayın və yükləyin… (Bağla və Yüklə — Bağla və Yüklə...):

... və görünən pəncərədə seçin Sadəcə əlaqə yaradın (Yalnız bağlantı).

Yeni qiymət siyahısı ilə eyni şeyi təkrarlayın. 

İndi əvvəlki iki məlumatı birləşdirəcək və müqayisə edəcək üçüncü sorğu yaradaq. Bunu etmək üçün tabda Excel-də seçin Məlumat - Məlumat əldə edin - Sorğuları birləşdirin - Birləşdirin (Məlumat - Məlumat əldə et - Sorğuları birləşdir - Birləşdir) və ya düyməni basın Birləşdirmək (Birləşin) nişanı Güc sorğusu.

Qoşulma pəncərəsində açılan siyahılardan cədvəllərimizi seçin, içərisində malların adları olan sütunları seçin və aşağıda qoşulma metodunu təyin edin – Tam xarici (Tam Xarici):

Klikdən sonra OK üç sütundan ibarət bir cədvəl görünməlidir, burada üçüncü sütunda başlıqdakı qoşa oxu istifadə edərək iç-içə cədvəllərin məzmununu genişləndirməlisiniz:

Nəticədə hər iki cədvəldən məlumatların birləşməsini əldə edirik:

Təbii ki, daha başa düşülənlərə iki dəfə klikləməklə başlıqdakı sütun adlarının adını dəyişmək daha yaxşıdır:

Və indi ən maraqlısı. Taba keçin Sütun əlavə edin (Sütun əlavə et) və düyməni basın Şərti sütun (Şərti Sütun). Və sonra açılan pəncərədə müvafiq çıxış qiymətləri ilə bir neçə test şərtlərini daxil edin:

Klikləmək qalır OK və eyni düyməni istifadə edərək nəticədə hesabatı Excel-ə yükləyin bağlayın və yükləyin (Bağla və Yüklə) nişanı Əsas səhifə (Ev):

Gözəllik.

Üstəlik, gələcəkdə qiymət siyahılarında hər hansı dəyişiklik baş verərsə (sətirlər əlavə edilir və ya silinirsə, qiymətlər dəyişir və s.), onda sadəcə olaraq klaviatura qısayolu ilə sorğularımızı yeniləmək kifayətdir. Ctrl+Dayandırmaq+F5 və ya düymə ilə Hamısını yeniləyin (Hamısını yeniləyin) nişanı Tarix (Tarix).

Pros: Bəlkə də ən gözəl və rahat yoldur. Böyük masalarla ağıllı işləyir. Cədvəllərin ölçüsünü dəyişdirərkən əl ilə redaktə tələb olunmur.

Eksiler: Power Query əlavəsinin (Excel 2010-2013-də) və ya Excel 2016-nın quraşdırılmasını tələb edir. Mənbə məlumatında sütun adları dəyişdirilməməlidir, əks halda “Filan sütun tapılmadı!” xətası alacağıq. sorğunu yeniləməyə çalışarkən.

• Power Query istifadə edərək verilmiş qovluqdakı bütün Excel fayllarından məlumatları necə toplamaq olar
• Excel-də iki siyahı arasında uyğunluğu necə tapmaq olar
• İki siyahının dublikatsız birləşdirilməsi