Жакынкы номерди табуу – Жанымдагы дени сак тамак

мазмуну

Практикада көп учурда сиз экөөбүзгө берилген санга карата топтомдогу (таблицадан) эң жакын маанини табышыбыз керек болгон учурлар болот. Ал, мисалы, болушу мүмкүн:

Көлөмүнө жараша арзандатууну эсептөө.
Пландын аткарылышына жараша сыйлыктардын өлчөмүн эсептөө.
аралыкка жараша жеткирүү тарифтерин эсептөө.
Товарлар үчүн ылайыктуу идиштерди тандоо ж.б.

Мындан тышкары, тегеректөө кырдаалга жараша өйдө жана ылдый талап кылынышы мүмкүн.

Мындай көйгөйдү чечүүнүн бир нече жолдору бар - ачык жана айкын эмес. Келгиле, аларды ырааттуу карап көрөлү.

Баштоо үчүн, дүңүнөн арзандатууларды берген жеткирүүчүнү элестетип көрөлү, жана арзандатуунун пайызы сатып алынган товарлардын санына жараша болот. Мисалы, 5 даанадан ашык сатып алууда 2% арзандатуу берилет, ал эми 20 даанадан сатып алууда 6% ж.б.

Сатып алынган товарлардын санын киргизүүдө арзандатуу пайызын кантип тез жана кооз эсептөө керек?

Метод 1: Уюшкан IFs

"Ойлоо үчүн эмне бар - секирүү керек!" сериясынан бир ыкма. Уюшкан функцияларды колдонуу IF (ЭГЕР) уячанын мааниси интервалдардын ар бирине туура келгенин ырааттуу түрдө текшерүү жана тиешелүү диапазон үчүн арзандатуу көрсөтүү. Бирок бул учурда формула абдан оор болуп чыгышы мүмкүн:

Эң жакын санды табуу

Менимче, мындай "монстр куурчакты" оңдоо же бир нече убакыттан кийин ага бир нече жаңы шарттарды кошууга аракет кылуу кызыктуу.

Мындан тышкары, Microsoft Excel IF функциясы үчүн уя чеги бар - эски версияларда 7 жолу жана жаңы версияларда 64 жолу. Эгер көбүрөөк керек болсочу?

Метод 2. Интервалдык көрүнүшү менен VLOOKUP

Бул ыкма алда канча компакттуу. Арзандатуу пайызын эсептөө үчүн легендарлуу функцияны колдонуңуз VPR (КӨРҮҮ) болжолдуу издөө режиминде:

Эң жакын санды табуу

кайда

B4 – биз арзандатуу издеп жаткан биринчи транзакциядагы товарлардын санынын наркы
$G$4:$H$8 – арзандатуу таблицасына шилтеме – “баш аты” жок жана $ белгиси менен бекитилген даректер.
2 — дисконттун маанисин алгыбыз келген эсептик таблицадагы графанын иреттик номери
ЧЫНЫГЫ – «иттин» көмүлгөн жери ушул. Эгерде акыркы функция аргументи катары VPR көрсөтүү ЖАЛГАН (ЖАЛГАН) же 0, анда функция издейт катуу матч сан тилкесинде (жана биздин учурда ал #N/A катасын берет, анткени дисконттук таблицада 49 мааниси жок). Бирок анын ордуна ЖАЛГАН жазуу ЧЫНЫГЫ (ЧЫН) же 1, анда функция так эмес, бирок издейт эң жакын эң кичине баалуу жана бизге керектүү арзандатуу пайызын берет.

Бул ыкманын терс жагы - эсептик таблицаны биринчи тилке боюнча өсүү тартибинде иреттөө зарылчылыгы. Эгерде мындай сорттоо жок болсо (же тескери тартипте жасалса), анда биздин формула иштебейт:

Эң жакын санды табуу

Демек, бул ыкма эң жакын эң кичине маанини табуу үчүн гана колдонулушу мүмкүн. Эгер сиз эң жакын чоңун табышыңыз керек болсо, анда башка ыкманы колдонушуңуз керек.

Метод 3. INDEX жана MATCH функцияларын колдонуу менен жакынкы чоңду табуу

Эми маселебизди экинчи жагынан карайлы. Биз ар кандай кубаттуулуктагы өнөр жай насостордун бир нече моделдерин сатуу дейли. Сол жактагы сатуу таблицасы кардар талап кылган күчтү көрсөтөт. Биз жакынкы максималдуу же бирдей кубаттуулуктагы насосту тандоо керек, бирок долбоор талап кылгандан кем эмес.

VLOOKUP функциясы бул жерде жардам бербейт, андыктан анын аналогун - 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 боюнча тажрыйбаңыз болсо, анда мурунку ыкмада болгондой, мындан аркы ой сызыгы бул эки таблицаны бириктирүү суроосу (бириктирүү) a la VLOOKUP менен бириктирүү багытында болушу керек деп ойлойм. Чынында, биз биринчи караганда такыр ачык эмес, кошуу режиминде биригүү керек болот. Excel өтмөгүнөн тандаңыз Берилиштер – Маалыматтарды алуу – Сурамдарды бириктирүү – Кошуу (Data — Маалымат алуу — Суроолорду бириктирүү — Тиркеме) анан биздин столдор сатуу и арзандатуулар пайда болгон терезеде:
Кийин басуу боюнча OK столдорубуз бир бүтүнгө жабышып калат - бири-биринин астына. Бул таблицалардагы товарлардын саны көрсөтүлгөн мамычалар бири-биринин астына түшкөнүн эске алыңыз, анткени. алар бирдей атка ээ:
Эгер сатуу таблицасындагы саптардын баштапкы ырааттуулугу сиз үчүн маанилүү болсо, анда бардык кийинки трансформациялардан кийин аны калыбына келтире аласыз, команданы колдонуп, биздин таблицага номерленген тилкени кошуңуз Мамыча кошуу – Индекс тилкеси (Кошуу тилкеси — Индекс тилкеси). Эгер саптардын ырааттуулугу сиз үчүн маанилүү болбосо, анда бул кадамды өткөрүп жиберсеңиз болот.
Эми, таблицанын башындагы ачылуучу тизмени колдонуп, аны мамыча боюнча иреттеңиз сан Өсүү:
Жана негизги трюк: мамычанын башын оң баскыч менен чыкылдатыңыз арзандатуу команда тандоо Толтуруу - Төмөн (Толтуруу — Төмөн). менен бош клеткалар нөл мурунку арзандатуу маанилери менен автоматтык түрдө толтурулат:
Мамыча боюнча сорттоо менен саптардын баштапкы ырааттуулугун калыбына келтирүү калды көрсөткүч (сиз аны кийинчерээк өчүрө аласыз) жана чыпка менен керексиз сызыктардан арылыңыз нөл тилке боюнча Транзакция коду: