Агуулга
Бидэнд хоёр хүснэгт (жишээлбэл, үнийн жагсаалтын хуучин болон шинэ хувилбарууд) байгаа бөгөөд тэдгээрийг харьцуулж, ялгааг нь хурдан олох хэрэгтэй.
Шинэ үнийн жагсаалтад ямар нэг зүйл нэмэгдсэн (огноо, сармис ...), ямар нэгэн зүйл алга болсон (бөөрөлзгөнө, бөөрөлзгөнө ...), зарим барааны үнэ өөрчлөгдсөн (инжир, амтат гуа ...) нь шууд тодорхой байна. Та эдгээр бүх өөрчлөлтийг хурдан олж, харуулах хэрэгтэй.
Excel-ийн аливаа ажлын хувьд бараг үргэлж нэгээс олон шийдэл байдаг (ихэвчлэн 4-5). Бидний асуудлын хувьд олон янзын аргыг ашиглаж болно:
- үйл ажиллагаа VPR (ХАРАХ) - хуучин үнийн жагсаалтаас бүтээгдэхүүний нэрсийг хайж, шинэ үнийн хажууд хуучин үнийг харуулсны дараа ялгааг олж мэдээрэй.
- хоёр жагсаалтыг нэг болгон нэгтгэж, дараа нь түүн дээр тулгуурлан пивот хүснэгтийг байгуулж, ялгаа нь тодорхой харагдах болно.
- Excel-д зориулсан Power Query нэмэлтийг ашиглана уу
Бүгдийг нь дарааллаар нь авч үзье.
Арга 1. VLOOKUP функцтэй хүснэгтүүдийг харьцуулах
Хэрэв та энэ гайхамшигтай функцийг огт мэдэхгүй бол эхлээд эндээс харж, энэ талаархи видео хичээлийг уншиж эсвэл үзээрэй - хоёр жилийн амьдралаа аврах болно.
Ихэвчлэн энэ функцийг зарим нэг нийтлэг параметртэй тааруулж өгөгдлийг нэг хүснэгтээс нөгөө хүснэгтэд татахад ашигладаг. Энэ тохиолдолд бид хуучин үнийг шинэ үнэ рүү оруулахын тулд үүнийг ашиглана.
#N/A алдаа гарсан бүтээгдэхүүнүүд хуучин жагсаалтад ороогүй, өөрөөр хэлбэл нэмсэн. Үнийн өөрчлөлт ч тодорхой харагдаж байна.
Давуу тал Энэ арга: энгийн бөгөөд ойлгомжтой, тэдний хэлснээр "төрлийн сонгодог". Excel-ийн аль ч хувилбар дээр ажилладаг.
Байг бас байдаг. Шинэ үнийн жагсаалтад нэмэгдсэн бүтээгдэхүүнийг хайхын тулд та эсрэг чиглэлд ижил процедурыг хийх хэрэгтэй болно, өөрөөр хэлбэл VLOOKUP-ийн тусламжтайгаар шинэ үнийг хуучин үнэ рүү татах хэрэгтэй. Хэрэв маргааш хүснэгтийн хэмжээ өөрчлөгдвөл томъёог тохируулах шаардлагатай болно. За, үнэхээр том ширээн дээр (> 100 мянган эгнээ) энэ бүх аз жаргал зохих ёсоор удаашрах болно.
Арга 2: Пивот ашиглан хүснэгтүүдийг харьцуулах
Хүснэгтүүдээ нэг нэгээр нь хуулж үнийн жагсаалтын нэр бүхий баганыг нэмж оруулаад дараа нь аль жагсаалтаас аль мөрийг ойлгох боломжтой болно.
Одоо үүсгэсэн хүснэгтэд үндэслэн бид хураангуйг үүсгэх болно Оруулах - Пивот хүснэгт (Оруулах - Пивот хүснэгт). Талбай шидье Бүтээгдэхүүний шугамын талбай, талбар руу Үнэ баганын талбай болон талбар руу Цэнэ мужид:
Таны харж байгаагаар пивот хүснэгт нь хуучин болон шинэ үнийн жагсаалтаас бүх бүтээгдэхүүний ерөнхий жагсаалтыг автоматаар үүсгэж (давталтгүй!) бөгөөд бүтээгдэхүүнийг цагаан толгойн дарааллаар эрэмбэлнэ. Та нэмсэн бүтээгдэхүүн (тэдгээр нь хуучин үнэ байхгүй), хасагдсан бүтээгдэхүүн (тэд шинэ үнэ байхгүй) болон үнийн өөрчлөлтийг тодорхой харж болно.
Ийм хүснэгтийн нийт дүн нь утгагүй бөгөөд тэдгээрийг таб дээр идэвхгүй болгож болно Constructor – Их нийт дүн – Мөр, баганын идэвхгүй болгох (Дизайн - Их нийт дүн).
Хэрэв үнэ өөрчлөгдвөл (гэхдээ барааны тоо биш!), үүсгэсэн хураангуйг хулганы баруун товчийг дарж шинэчлэхэд л хангалттай. Сэргээх.
Давуу тал: Энэ арга нь 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-д байршуулна уу хааж татаж аваарай (Хаах & Ачаалах) таб Нүүр хуудас (Нүүр хуудас):
Гоо сайхан.
Цаашид үнийн жагсаалтад ямар нэгэн өөрчлөлт гарвал (мөр нэмэх, хасах, үнэ өөрчлөгдөх гэх мэт) гарны товчлолоор хүсэлтээ шинэчлэхэд л хангалттай. Ctrl+Alt+F5 эсвэл товчлуураар Бүгдийг нь сэргээ (Бүгдийг сэргээх) таб Өгөгдөл (Огноо).
Давуу тал: Магадгүй хамгийн үзэсгэлэнтэй, тохиромжтой арга. Том ширээтэй ухаалаг ажиллана. Хүснэгтийн хэмжээг өөрчлөх үед гараар засвар хийх шаардлагагүй.
Байг: Power Query нэмэлт (Excel 2010-2013) эсвэл Excel 2016-г суулгахыг шаарддаг. Эх өгөгдлийн баганын нэрийг өөрчлөх ёсгүй, эс тэгвээс бид "Тийм, ийм багана олдсонгүй!" гэсэн алдаа гарна. асуулга шинэчлэх гэж оролдох үед.
- Power Query ашиглан өгөгдсөн хавтсанд байгаа бүх Excel файлуудаас өгөгдлийг хэрхэн цуглуулах вэ
- Excel дээр хоёр жагсаалтын хооронд тохирохыг хэрхэн олох вэ
- Хоёр жагсаалтыг давхардалгүйгээр нэгтгэж байна