Eng yaqin raqamni topish

Mundarija

Amalda, ko'pincha siz va men berilgan raqamga nisbatan to'plamdagi (jadvaldagi) eng yaqin qiymatni topishimiz kerak bo'lgan holatlar mavjud. Bu, masalan, bo'lishi mumkin:

Hajmiga qarab chegirmani hisoblash.
Rejaning bajarilishiga qarab bonuslar miqdorini hisoblash.
Yuk tashish tariflarini masofaga qarab hisoblash.
Tovarlar uchun mos idishlarni tanlash va boshqalar.

Bundan tashqari, vaziyatga qarab yaxlitlash ham yuqoriga, ham pastga talab qilinishi mumkin.

Bunday muammoni hal qilishning bir necha yo'li bor - aniq va unchalik aniq emas. Keling, ularni ketma-ket ko'rib chiqaylik.

Boshlash uchun, ulgurji chegirmalar beradigan etkazib beruvchini tasavvur qilaylik va chegirmaning foizi sotib olingan tovarlar miqdoriga bog'liq. Masalan, 5 donadan ortiq sotib olayotganda 2% chegirma beriladi, 20 donadan sotib olayotganda - allaqachon 6% va hokazo.

Sotib olingan tovarlar miqdorini kiritishda chegirma foizini qanday tez va chiroyli tarzda hisoblash mumkin?

1-usul: o'rnatilgan IFs

"O'ylash uchun nima bor - sakrash kerak!" Serialidan usul. Ichki funksiyalardan foydalanish IF (Agar) hujayra qiymati har bir intervalga to'g'ri kelishini ketma-ket tekshirish va tegishli diapazon uchun chegirmani ko'rsatish. Ammo bu holda formula juda og'ir bo'lib chiqishi mumkin:

Eng yaqin raqamni topish

Menimcha, bunday "yirtqich qo'g'irchoq" ni tuzatish yoki bir muncha vaqt o'tgach, unga bir nechta yangi shartlarni qo'shishga urinish qiziqarli.

Bundan tashqari, Microsoft Excel-da IF funktsiyasi uchun joylashtirish chegarasi mavjud - eski versiyalarda 7 marta va yangi versiyalarda 64 marta. Agar sizga ko'proq kerak bo'lsa-chi?

2-usul. Intervalli ko'rinish bilan VLOOKUP

Bu usul ancha ixchamroq. Chegirma foizini hisoblash uchun afsonaviy funksiyadan foydalaning VPR (KO'RISH) taxminiy qidiruv rejimida:

Eng yaqin raqamni topish

qayerda

B4 – biz chegirma izlayotgan birinchi tranzaksiyadagi tovarlar miqdorining qiymati
$G$4:$H$8 – chegirma jadvaliga havola – “sarlavha”siz va $ belgisi bilan belgilangan manzillar bilan.
2 — chegirma qiymatini olishni istagan chegirma jadvalidagi ustunning tartib raqami
HAQ - bu erda "it" dafn etilgan. Agar oxirgi funktsiya argumenti sifatida VPR belgilang YOLG'ON (YOLG'ON) yoki 0, keyin funksiya qidiradi qat'iy muvofiqlik miqdor ustunida (va bizning holatlarimizda u #N/A xatosini beradi, chunki chegirma jadvalida 49 qiymati yo'q). Ammo o'rniga bo'lsa YOLG'ON yozish HAQ (TO'G'RI) yoki 1, keyin funksiya aniq emas, balki qidiradi eng yaqin eng kichik qiymat va bizga kerak bo'lgan chegirma foizini beradi.

Ushbu usulning salbiy tomoni chegirma jadvalini birinchi ustun bo'yicha o'sish tartibida saralash zarurati. Agar bunday tartiblash bo'lmasa (yoki u teskari tartibda amalga oshirilsa), unda bizning formulamiz ishlamaydi:

Eng yaqin raqamni topish

Shunga ko'ra, bu yondashuv faqat eng yaqin eng kichik qiymatni topish uchun ishlatilishi mumkin. Agar siz eng yaqin eng kattasini topishingiz kerak bo'lsa, unda siz boshqa yondashuvdan foydalanishingiz kerak.

3-usul. INDEX va MATCH funksiyalari yordamida eng yaqin eng kattani topish

Endi muammomizga boshqa tomondan qaraylik. Aytaylik, biz turli quvvatdagi sanoat nasoslarining bir nechta modellarini sotamiz. Chapdagi savdo jadvali mijoz tomonidan talab qilinadigan quvvatni ko'rsatadi. Biz eng yaqin maksimal yoki teng quvvatli nasosni tanlashimiz kerak, lekin loyiha tomonidan talab qilinadiganidan kam emas.

VLOOKUP funksiyasi bu erda yordam bermaydi, shuning uchun siz uning analogidan foydalanishingiz kerak bo'ladi - bir qator INDEX funktsiyalari (INDEKS) va KO'PROQ (MATCH):

Eng yaqin raqamni topish

Bu yerda oxirgi -1 argumentli MATCH funksiyasi eng yaqin eng katta qiymatni topish rejimida ishlaydi va INDEX funksiyasi keyin qo'shni ustundan bizga kerak bo'lgan model nomini chiqaradi.

4-usul. Yangi funksiya VIEW (XLOOKUP)

Agar sizda barcha yangilanishlar o'rnatilgan Office 365 versiyasi bo'lsa, VLOOKUP o'rniga (KO'RISH) uning analogi - VIEW funksiyasidan foydalanishingiz mumkin (XIZASH), men allaqachon batafsil tahlil qilganman:

Eng yaqin raqamni topish

Bu yerga:

B4 - biz chegirma qidirayotgan mahsulot miqdorining boshlang'ich qiymati
$G$4:$G$8 - biz gugurt izlayotgan diapazon
$H$4:$H$8 - chegirmani qaytarmoqchi bo'lgan natijalar oralig'i
to'rtinchi dalil (-1) aniq moslik o'rniga biz xohlagan eng yaqin eng kichik raqamni qidirishni o'z ichiga oladi.

Ushbu usulning afzalliklari shundaki, chegirma jadvalini tartiblashning hojati yo'q va agar kerak bo'lsa, nafaqat eng yaqin eng kichik, balki eng yaqin eng katta qiymatni ham qidirish imkoniyati mavjud. Bu holatda oxirgi argument 1 bo'ladi.

Ammo, afsuski, hali hamma ham bunday xususiyatga ega emas - faqat Office 365-ning baxtli egalari.

5-usul. Power Query

Agar siz Excel uchun kuchli va mutlaqo bepul Power Query plaginini hali bilmasangiz, demak siz shu yerdasiz. Agar siz allaqachon tanish bo'lsangiz, keling, muammoimizni hal qilish uchun undan foydalanishga harakat qilaylik.

Avval tayyorgarlik ishlarini bajaramiz:

Keling, klaviatura yorliqlari yordamida manba jadvallarimizni dinamik (aqlli) ga aylantiramiz Ctrl+T yoki jamoa Bosh sahifa - Jadval sifatida formatlash (Uy — Jadval sifatida formatlash).
Aniqlik uchun keling, ularga nom beraylik. savdo и Chegirmalar tab konstruktor (Dizayn).
Har bir jadvalni tugma yordamida Power Query-ga navbatma-navbat yuklang Jadval/diapazondan tab ma'lumotlar (Ma'lumotlar - jadvaldan/diapazondan). Excelning so'nggi versiyalarida bu tugma nomi o'zgartirildi Barglari bilan (Varaqdan).
Agar jadvallar bizning misolimizdagi kabi miqdorlar bilan turli ustun nomlariga ega bo'lsa ("Tovarlar miqdori" va "Miqdor ..."), ular Power Query-da qayta nomlanishi va bir xil nomlanishi kerak.
Shundan so'ng Power Query muharriri oynasida buyruqni tanlab, Excelga qaytishingiz mumkin Bosh sahifa — Yopish va yuklash — Yopish va yuklash… (Uy — Yopish va yuklash — Yopish va yuklash…) va keyin variant Faqat ulanishni yarating (Faqat ulanish yaratish).
Keyin eng qiziqarlisi boshlanadi. Agar sizda Power Query bo'yicha tajribangiz bo'lsa, menimcha, oldingi usulda bo'lgani kabi, keyingi fikr ushbu ikkita jadvalni qo'shilish so'rovi (birlashtirish) a la VLOOKUP bilan birlashtirish yo'nalishida bo'lishi kerak. Aslida, biz qo'shish rejimida birlashishimiz kerak bo'ladi, bu birinchi qarashda umuman ko'rinmaydi. Excel yorlig'ida tanlang Ma'lumotlar - Ma'lumotlarni olish - So'rovlarni birlashtirish - Qo'shish (Ma'lumotlar - Ma'lumot olish - So'rovlarni birlashtirish - Qo'shish) va keyin bizning stollarimiz savdo и Chegirmalar paydo bo'lgan oynada:
Tugmasini bosgandan so'ng OK stollarimiz bir butunga - bir-birining ostiga yopishtiriladi. E'tibor bering, ushbu jadvallardagi tovarlar miqdori ko'rsatilgan ustunlar bir-birining ostiga tushdi, chunki. ular bir xil nomga ega:
Agar savdo jadvalidagi satrlarning asl ketma-ketligi siz uchun muhim bo'lsa, keyingi barcha o'zgarishlardan so'ng uni qayta tiklashingiz uchun buyruq yordamida jadvalimizga raqamlangan ustun qo'shing. Ustun qo'shish - indeks ustuni (Ustun qo'shish - indeks ustuni). Agar chiziqlar ketma-ketligi siz uchun muhim bo'lmasa, unda siz ushbu bosqichni o'tkazib yuborishingiz mumkin.
Endi, jadval sarlavhasidagi ochiladigan ro'yxatni ishlatib, uni ustunlar bo'yicha tartiblang miqdor Ko'tarilish:
Va asosiy hiyla: ustun sarlavhasini o'ng tugmasini bosing chegirma jamoani tanlang To'ldirish - pastga (To'ldirish - pastga). bilan bo'sh hujayralar null oldingi chegirma qiymatlari bilan avtomatik ravishda to'ldiriladi:
Ustun bo'yicha saralash orqali satrlarning asl ketma-ketligini tiklash qoladi indeks (siz uni keyinroq xavfsiz o'chirishingiz mumkin) va filtr yordamida keraksiz chiziqlardan xalos bo'ling null ustun bo'yicha Tranzaksiya kodi: