Mundarija
Bizda ikkita jadval mavjud (masalan, narxlar ro'yxatining eski va yangi versiyalari), biz ularni taqqoslashimiz va farqlarni tezda topishimiz kerak:
Darhol ma'lum bo'ladiki, yangi narxlar ro'yxatiga nimadir qo'shilgan (xurmo, sarimsoq ...), nimadir yo'qolgan (qora uzum, malina ...), ba'zi tovarlar uchun narxlar o'zgargan (anjir, qovun ...). Ushbu o'zgarishlarning barchasini tezda topib, ko'rsatishingiz kerak.
Excelda har qanday vazifa uchun deyarli har doim bir nechta yechim mavjud (odatda 4-5). Bizning muammomiz uchun turli xil yondashuvlardan foydalanish mumkin:
- Funktsiyasi VPR (KO'RISH) — eskisidan yangi narxlar roʻyxatidan mahsulot nomlarini qidiring va yangisi yonida eski narxni koʻrsating, soʻngra farqlarni koʻring.
- ikkita ro'yxatni bitta ro'yxatga birlashtiring va so'ngra uning asosida pivot jadval tuzing, bu erda farqlar aniq ko'rinadi.
- Excel uchun Power Query plaginidan foydalaning
Keling, ularning barchasini tartibda olaylik.
Usul 1. VLOOKUP funksiyasi bilan jadvallarni solishtirish
Agar siz ushbu ajoyib xususiyat bilan mutlaqo notanish bo'lsangiz, unda avval bu yerga qarang va u bo'yicha video darslikni o'qing yoki tomosha qiling - o'zingizni bir necha yil hayotingizni saqlang.
Odatda, bu funktsiya ma'lumotlarni bir jadvaldan ikkinchisiga o'tkazish uchun ba'zi umumiy parametrlarni moslashtirish uchun ishlatiladi. Bunday holda, biz undan eski narxlarni yangi narxga kiritish uchun foydalanamiz:
#N/A xatosi chiqqan mahsulotlar eski ro'yxatda yo'q, ya'ni qo'shilgan. Narxlardagi o'zgarishlar ham aniq ko'rinadi.
Taroziga bu usul: oddiy va tushunarli, ular aytganidek, "janr klassikasi". Excelning istalgan versiyasida ishlaydi.
Kamchiliklari ham bor. Yangi narxlar ro'yxatiga qo'shilgan mahsulotlarni qidirish uchun siz xuddi shunday tartibni teskari yo'nalishda bajarishingiz kerak bo'ladi, ya'ni VLOOKUP yordamida yangi narxlarni eski narxga ko'taring. Agar ertaga jadvallarning o'lchamlari o'zgarsa, formulalarni sozlash kerak bo'ladi. Va haqiqatan ham katta stollarda (> 100 ming qator), bu baxtning barchasi sekinlashadi.
2-usul: Pivot yordamida jadvallarni solishtirish
Keling, jadvallarimizni bir-birining ostiga ko'chiramiz, narxlar ro'yxati nomi bilan ustun qo'shamiz, shunda keyin qaysi ro'yxatdagi qaysi qatorni tushunishingiz mumkin:
Endi yaratilgan jadval asosida biz orqali xulosa tuzamiz Qo'shish - Pivot jadval (Qo'shish - umumiy jadval). Keling, maydonni tashlaymiz mahsulot chiziqlar maydoniga, maydonga narx ustun maydoni va maydoniga Цena diapazonga:
Ko'rib turganingizdek, pivot jadvali avtomatik ravishda eski va yangi narxlar ro'yxatidagi barcha mahsulotlarning umumiy ro'yxatini yaratadi (takrorlashsiz!) va mahsulotlarni alifbo tartibida tartiblaydi. Siz qo'shilgan mahsulotlarni (ular eski narxiga ega emas), olib tashlangan mahsulotlarni (ular yangi narxga ega emas) va agar mavjud bo'lsa, narxlarning o'zgarishini aniq ko'rishingiz mumkin.
Bunday jadvaldagi umumiy summalar mantiqiy emas va ularni yorliqda o'chirib qo'yish mumkin Konstruktor - Umumiy jami - satrlar va ustunlar uchun o'chirib qo'ying (Dizayn - Umumiy jami).
Agar narxlar o'zgarsa (lekin tovarlar miqdori emas!), unda sichqonchaning o'ng tugmachasini bosish orqali yaratilgan xulosani yangilash kifoya - yangilamoq.
Taroziga: Bu yondashuv VLOOKUPga qaraganda katta jadvallar bilan tezroq kattalik tartibidir.
Kamchiliklari: bir-birining ostidagi ma'lumotlarni qo'lda nusxalashingiz va narxlar ro'yxati nomi bilan ustun qo'shishingiz kerak. Agar jadvallarning o'lchamlari o'zgarsa, unda siz hamma narsani qaytadan qilishingiz kerak.
3-usul: Power Query bilan jadvallarni solishtirish
Power Query - bu Microsoft Excel uchun bepul plagin bo'lib, u deyarli har qanday manbadan Excelga ma'lumotlarni yuklash va keyin bu ma'lumotlarni istalgan shaklda o'zgartirish imkonini beradi. Excel 2016 da ushbu plagin allaqachon yorliqda sukut bo'yicha o'rnatilgan ma'lumotlar (ma'lumotlar), va Excel 2010-2013 uchun uni Microsoft veb-saytidan alohida yuklab olishingiz va o'rnatishingiz kerak - yangi yorliq oling Quvvat so'rovi.
Narxlar ro'yxatini Power Query-ga yuklashdan oldin ularni aqlli jadvallarga aylantirish kerak. Buning uchun ma'lumotlar bilan diapazonni tanlang va klaviaturadagi kombinatsiyani bosing Ctrl+T yoki lentadagi yorliqni tanlang Bosh sahifa - Jadval sifatida formatlash (Uy — Jadval sifatida formatlash). Yaratilgan jadvallarning nomlari yorliqda tuzatilishi mumkin konstruktor (Men standartni qoldiraman 1 stol и 2 stol, ular sukut bo'yicha olinadi).
Tugma yordamida Power Query-da eski narxni yuklang Jadval/diapazondan (Jadval/diapazondan) tabdan ma'lumotlar (Sana) yoki yorliqdan Quvvat so'rovi (Excel versiyasiga qarab). Yuklagandan so'ng, biz buyruq bilan Power Query'dan Excelga qaytamiz Yopish va yuklash – Yopish va yuklash… (Yopish va yuklash — yopish va yuklash…):
... va paydo bo'lgan oynada tanlang Faqat ulanishni yarating (Faqat ulanish).
Xuddi shu narsani yangi narxlar ro'yxati bilan takrorlang.
Endi oldingi ikkita ma'lumotni birlashtiradigan va taqqoslaydigan uchinchi so'rovni yaratamiz. Buni amalga oshirish uchun yorliqda Excel-ni tanlang Ma'lumotlar - Ma'lumotlarni olish - So'rovlarni birlashtirish - Birlashtirish (Ma'lumotlar - Ma'lumot olish - So'rovlarni birlashtirish - Birlashtirish) yoki tugmani bosing Aralashtirmoq (Birlashtirish) tab Quvvat so'rovi.
Qo'shilish oynasida, ochiladigan ro'yxatlarda bizning jadvallarimizni tanlang, ulardagi tovarlar nomlari ko'rsatilgan ustunlarni tanlang va pastki qismida qo'shilish usulini o'rnating - To'liq tashqi (To'liq tashqi):
Tugmasini bosgandan so'ng OK uchta ustunli jadval paydo bo'lishi kerak, bu erda uchinchi ustunda sarlavhadagi qo'sh o'q yordamida ichki o'rnatilgan jadvallar tarkibini kengaytirish kerak:
Natijada biz ikkala jadvaldagi ma'lumotlarni birlashtiramiz:
Albatta, sarlavhadagi ustun nomlarini tushunarliroqlarini ikki marta bosish orqali o'zgartirish yaxshiroqdir:
Va endi eng qiziqarli. Tabga o'ting Ustun qo'shish (ustun qo'shish) va tugmani bosing Shartli ustun (shartli ustun). Va keyin ochilgan oynada tegishli chiqish qiymatlari bilan bir nechta sinov shartlarini kiriting:
Bosish qoladi OK va natijada olingan hisobotni xuddi shu tugma yordamida Excelga yuklang yoping va yuklab oling (Yopish va yuklash) tab Bosh sahifa (Uy):
Go'zallik.
Bundan tashqari, agar kelajakda narxlar ro'yxatida biron bir o'zgarishlar ro'y bersa (chiziqlar qo'shilsa yoki o'chirilsa, narxlar o'zgaradi va hokazo), so'rovlarimizni klaviatura yorlig'i bilan yangilash kifoya qiladi. Ctrl+Alt+F5 yoki tugma orqali Hammasini yangilang (Hammasini yangilash) tab ma'lumotlar (Sana).
Taroziga: Ehtimol, eng chiroyli va qulay usul. Katta stollar bilan oqilona ishlaydi. Jadvallar hajmini o'zgartirishda qo'lda tahrirlashni talab qilmaydi.
Kamchiliklari: Power Query plaginini (Excel 2010-2013 da) yoki Excel 2016 o'rnatishni talab qiladi. Manba ma'lumotlaridagi ustun nomlari o'zgartirilmasligi kerak, aks holda biz "Falon ustun topilmadi!" Xatosini olamiz. so'rovni yangilashga urinayotganda.
- Power Query yordamida berilgan papkadagi barcha Excel fayllaridan ma'lumotlarni qanday yig'ish mumkin
- Excelda ikkita ro'yxat o'rtasidagi moslikni qanday topish mumkin
- Ikki ro'yxatni dublikatsiz birlashtirish