Агуулга
Практикт та бид хоёр өгөгдсөн тоотой холбоотой багц (хүснэгт) дэх хамгийн ойрын утгыг олох шаардлагатай тохиолдол ихэвчлэн тохиолддог. Энэ нь жишээлбэл:
- Эзлэхүүнээс хамаарч хөнгөлөлтийн тооцоо.
- Төлөвлөгөөний хэрэгжилтээс хамаарч урамшууллын хэмжээг тооцох.
- Тээвэрлэлтийн үнийг зайнаас хамааран тооцоолох.
- Барааны тохиромжтой савыг сонгох гэх мэт.
Түүнээс гадна нөхцөл байдлаас шалтгаалан дээш доошоо дугуйлах шаардлагатай байж болно.
Ийм асуудлыг шийдэх хэд хэдэн арга байдаг - ойлгомжтой, тийм ч тодорхой биш. Тэдгээрийг дараалан авч үзье.
Эхлэхийн тулд бөөний худалдаанд хөнгөлөлт үзүүлдэг ханган нийлүүлэгчийг төсөөлөөд үз дээ, хямдралын хувь нь худалдаж авсан барааны тоо хэмжээнээс хамаарна. Жишээлбэл, 5-аас дээш ширхэгийг худалдаж авахдаа 2%, 20 ширхэгээс авахдаа 6% гэх мэт хөнгөлөлт үзүүлдэг.
Худалдан авсан барааны тоо хэмжээг оруулахдаа хөнгөлөлтийн хувийг хэрхэн хурдан, сайхан тооцоолох вэ?
Арга 1: Суурилуулсан IFs
"Бодох зүйл юу байна - чи үсрэх хэрэгтэй!" Цувралын арга. Оруулсан функцуудыг ашиглах IF (Хэрэв) Нүдний утга интервал тус бүрт багтаж байгаа эсэхийг дараалан шалгаж, харгалзах мужид хөнгөлөлт үзүүлэх. Гэхдээ энэ тохиолдолд томъёо нь маш төвөгтэй байж болно:
Ийм "мангас хүүхэлдэй"-г дибаг хийх эсвэл хэсэг хугацааны дараа түүнд хэд хэдэн шинэ нөхцөл нэмэхийг оролдох нь хөгжилтэй байдаг нь ойлгомжтой гэж би бодож байна.
Нэмж дурдахад, Microsoft Excel нь IF функцийг үүрлэх хязгаарлалттай байдаг - хуучин хувилбаруудад 7 удаа, шинэ хувилбаруудад 64 удаа. Хэрэв танд илүү хэрэгтэй бол яах вэ?
Арга 2. Интервалаар харах VLOOKUP
Энэ арга нь илүү нягт юм. Хөнгөлөлтийн хувийг тооцоолохын тулд домогт функцийг ашиглана уу VPR (ХАРАХ) ойролцоогоор хайлтын горимд:
хаана
- B4 – бидний хөнгөлөлт хүсч буй эхний гүйлгээнд байгаа барааны тоо хэмжээний үнэ цэнэ
- $G$4:$H$8 – хөнгөлөлтийн хүснэгтийн холбоос – “толгой”гүй, $ тэмдгээр зассан хаягууд.
- 2 - хөнгөлөлтийн утгыг авахыг хүсч буй хөнгөлөлтийн хүснэгтийн баганын дарааллын дугаар
- ҮНЭН - энд "нохой" оршуулсан газар юм. Хэрэв функцийн сүүлийн аргумент бол VPR зааж өгөх ХУДЛАА (ХУДАЛ) эсвэл 0, дараа нь функц хайх болно хатуу тоглолт тоо хэмжээ баганад (мөн манай тохиолдолд хөнгөлөлтийн хүснэгтэд 49 гэсэн утга байхгүй тул энэ нь #N/A алдаа өгөх болно). Харин оронд нь бол ХУДЛАА бичих ҮНЭН (ҮНЭН) эсвэл 1, дараа нь функц нь яг биш, харин хайх болно хамгийн ойрын жижиг үнэ цэнэтэй бөгөөд бидэнд шаардлагатай хөнгөлөлтийн хувийг өгөх болно.
Энэ аргын сул тал нь хөнгөлөлтийн хүснэгтийг эхний баганаар өсөх дарааллаар эрэмбэлэх хэрэгцээ юм. Хэрэв ийм эрэмбэ байхгүй бол (эсвэл урвуу дарааллаар хийгдсэн бол) бидний томъёо ажиллахгүй болно.
Иймээс энэ аргыг зөвхөн хамгийн ойрын бага утгыг олоход ашиглаж болно. Хэрэв та хамгийн ойрын хамгийн томыг олох шаардлагатай бол өөр аргыг ашиглах хэрэгтэй.
Арга 3. INDEX болон MATCH функцуудыг ашиглан хамгийн ойрын хамгийн томыг олох
Одоо асуудлаа нөгөө талаас нь харцгаая. Бид янз бүрийн хүчин чадалтай үйлдвэрлэлийн насосны хэд хэдэн загварыг зарж байна гэж бодъё. Зүүн талд байгаа борлуулалтын хүснэгтэд хэрэглэгчийн шаардагдах хүчийг харуулав. Бид хамгийн ойрын хамгийн их эсвэл тэнцүү чадлын насосыг сонгох хэрэгтэй, гэхдээ төслийн шаардлагатай хэмжээнээс багагүй байна.
VLOOKUP функц нь энд тус болохгүй тул та түүний аналог буюу олон тооны INDEX функцийг ашиглах хэрэгтэй болно. (INDEX) болон ИЛҮҮ ИЛҮҮ (ТОГЛОЛТ):
Энд -1 сүүлчийн аргументтай MATCH функц нь хамгийн ойрын хамгийн том утгыг олох горимд ажиллах ба INDEX функц нь зэргэлдээ баганаас бидэнд хэрэгтэй загварын нэрийг гаргаж авдаг.
Арга 4. Шинэ функц VIEW (XLOOKUP)
Хэрэв танд бүх шинэчлэлтүүд суулгасан Office 365 хувилбар байгаа бол VLOOKUP-ын оронд (ХАРАХ) Та түүний аналог болох VIEW функцийг ашиглаж болно (ТАЙЛБАР), би үүнийг аль хэдийн нарийвчлан шинжилсэн:
Энд:
- B4 – бидний хямдрал хайж байгаа бүтээгдэхүүний тоо хэмжээний анхны үнэ цэнэ
- $G$4:$G$8 – бидний шүдэнз хайж буй хүрээ
- $H$4:$H$8 – хөнгөлөлтийг буцаахыг хүссэн үр дүнгийн хүрээ
- дөрөв дэх аргумент (-1) нь яг таарч тохирохын оронд хамгийн ойрын хамгийн бага тоог хайхыг агуулдаг.
Энэ аргын давуу тал нь хөнгөлөлтийн хүснэгтийг эрэмбэлэх шаардлагагүй бөгөөд шаардлагатай бол хамгийн ойрын хамгийн жижиг төдийгүй хамгийн ойрын хамгийн том утгыг хайх чадвар юм. Энэ тохиолдолд хамгийн сүүлийн аргумент нь 1 байх болно.
Гэвч харамсалтай нь хүн бүр ийм онцлогтой байдаггүй - зөвхөн Office 365-ийн аз жаргалтай эзэмшигчид.
Арга 5. Power Query
Хэрэв та Excel-д зориулсан хүчирхэг, бүрэн үнэгүй Power Query нэмэлтийг хараахан мэдэхгүй байгаа бол та энд байна. Хэрэв та аль хэдийн танил болсон бол асуудлыг шийдэхийн тулд үүнийг ашиглахыг хичээцгээе.
Эхлээд бэлтгэл ажил хийцгээе:
- Гарын товчлолыг ашиглан эх хүснэгтээ динамик (ухаалаг) болгон хөрвүүлцгээе Ctrl+T эсвэл баг Нүүр хуудас - Хүснэгт хэлбэрээр форматлах (Нүүр хуудас - Хүснэгт хэлбэрээр форматлах).
- Тодорхой болгохын тулд тэдэнд нэр өгье. Борлуулалтын и хөнгөлөлт таб байгуулагч (Дизайн).
- Товчлуурыг ашиглан хүснэгт бүрийг Power Query болгон ачаална уу Хүснэгтээс/Мужаас таб Өгөгдөл (Өгөгдөл - Хүснэгтээс/мужаас). Excel-ийн сүүлийн хувилбаруудад энэ товчлуурын нэрийг өөрчилсөн Навчтай (Хуудаснаас).
- Хүснэгтүүд нь манай жишээн дээрх ("Барааны тоо хэмжээ" ба "...-аас авсан тоо хэмжээ") адил тоо хэмжээ бүхий өөр өөр баганын нэртэй байвал тэдгээрийг Power Query-д нэрлэж, ижил нэртэй байх ёстой.
- Үүний дараа та Power Query засварлагчийн цонхон дээрх командыг сонгон Excel рүү буцах боломжтой Нүүр хуудас — Хаах ба ачаалах — Хаах ба ачаалах... (Нүүр хуудас — Хаах&Ачаалах — Хаах&Ачаалах...) тэгээд сонголт Зүгээр л холболт үүсгэ (Зөвхөн холболт үүсгэх).
- Дараа нь хамгийн сонирхолтой нь эхэлнэ. Хэрэв та Power Query дээр ажиллаж байсан туршлагатай бол өмнөх аргын нэгэн адил эдгээр хоёр хүснэгтийг нэгтгэх (нэгтгэх) болон VLOOKUP хийх чиглэлтэй байх ёстой гэж би бодож байна. Үнэн хэрэгтээ бид нэмэх горимд нэгдэх хэрэгтэй болно, энэ нь эхлээд харахад тийм ч тодорхой биш юм. Excel таб дээрээс сонгоно уу Өгөгдөл - Өгөгдөл авах - Хүсэлтийг нэгтгэх - Нэмэх (Өгөгдөл - Өгөгдөл авах - Асуултуудыг нэгтгэх - Хавсаргах) тэгээд бидний ширээ Борлуулалтын и хөнгөлөлт гарч ирэх цонхонд:
- Үргэлжлүүлсний дараа OK Манай ширээнүүд бие биенийхээ доор нэг бүтэн наасан байх болно. Эдгээр хүснэгтэд байгаа барааны тоо хэмжээ бүхий баганууд бие биенийхээ доор унасан болохыг анхаарна уу. Тэд ижил нэртэй:
- Хэрэв борлуулалтын хүснэгт дэх мөрүүдийн анхны дараалал нь танд чухал бол дараагийн бүх өөрчлөлтийн дараа үүнийг сэргээхийн тулд тушаалыг ашиглан манай хүснэгтэд дугаарласан баганыг нэмнэ үү. Багана нэмэх - Индекс багана (Багана нэмэх - Индекс багана). Хэрэв мөрүүдийн дараалал танд хамаагүй бол та энэ алхамыг алгасаж болно.
- Хүснэгтийн толгой хэсэгт байрлах унадаг жагсаалтыг ашиглан баганаар нь ангилна уу Тоо хэмжээ Өсөх:
- Мөн гол заль мэх: баганын толгой дээр хулганы баруун товчийг дарна уу хөнгөлөлт баг сонгох Бөглөх - доош (Дүүргэх - доош). Хоосон нүднүүд тэг өмнөх хөнгөлөлтийн утгуудаар автоматаар бөглөнө:
- Энэ нь баганаар эрэмбэлэх замаар мөрүүдийн анхны дарааллыг сэргээх хэвээр байна индекс (та дараа нь аюулгүйгээр устгаж болно) шүүлтүүрээр шаардлагагүй мөрүүдийг арилгана тэг баганаар Гүйлгээний код:
- VLOOKUP функцийг ашиглан өгөгдөл хайх, хайх
- VLOOKUP (VLOOKUP) ашиглах нь том жижиг үсгээр харгалздаг
- XNUMXD VLOOKUP (VLOOKUP)