پیدا کردن نزدیکترین شماره - غذای سالم در نزدیکی من

فهرست

در عمل، اغلب مواردی وجود دارد که من و شما باید نزدیکترین مقدار را در یک مجموعه (جدول) در رابطه با یک عدد مشخص پیدا کنیم. می تواند به عنوان مثال:

محاسبه تخفیف بسته به حجم.
محاسبه میزان پاداش بسته به اجرای طرح.
محاسبه نرخ حمل و نقل بسته به مسافت.
انتخاب ظروف مناسب برای کالا و غیره

علاوه بر این، بسته به موقعیت ممکن است به گرد کردن هم به بالا و هم به پایین نیاز باشد.

چندین راه – واضح و نه چندان واضح – برای حل چنین مشکلی وجود دارد. بیایید به ترتیب به آنها نگاه کنیم.

برای شروع، بیایید تامین کننده ای را تصور کنیم که در عمده فروشی تخفیف می دهد و درصد تخفیف بستگی به مقدار کالای خریداری شده دارد. به عنوان مثال هنگام خرید بیش از 5 عدد، 2% تخفیف و هنگام خرید از 20 عدد از قبل 6% تخفیف داده می شود.

چگونه به سرعت و به زیبایی درصد تخفیف را هنگام درج تعداد کالاهای خریداری شده محاسبه کنیم؟

روش 1: IF های تودرتو

روشی از مجموعه "چه چیزی برای فکر کردن وجود دارد - باید بپری!". استفاده از توابع تو در تو IF (اگر) به طور متوالی بررسی کنید که آیا مقدار سلول در هر یک از بازه‌ها قرار می‌گیرد یا خیر و یک تخفیف برای محدوده مربوطه نشان می‌دهد. اما فرمول در این مورد می تواند بسیار دست و پا گیر باشد:

پیدا کردن نزدیکترین عدد

من فکر می کنم بدیهی است که اشکال زدایی چنین "عروسک هیولایی" یا تلاش برای اضافه کردن چند شرط جدید به آن پس از مدتی سرگرم کننده است.

علاوه بر این، مایکروسافت اکسل یک محدودیت تودرتو برای تابع IF دارد - 7 بار در نسخه های قدیمی و 64 بار در نسخه های جدید. اگر بیشتر نیاز دارید چه؟

روش 2. VLOOKUP با نمای فاصله

این روش بسیار فشرده تر است. برای محاسبه درصد تخفیف، از تابع افسانه ای استفاده کنید VPR (VLOOKUP) در حالت جستجوی تقریبی:

پیدا کردن نزدیکترین عدد

جایی که

B4 – ارزش مقدار کالا در اولین معامله ای که برای آن به دنبال تخفیف هستیم
$4: $H $8 – پیوندی به جدول تخفیف – بدون «هدر» و آدرس‌هایی که با علامت $ ثابت شده‌اند.
2 - شماره ترتیبی ستونی در جدول تخفیف که می خواهیم مقدار تخفیف را از آن دریافت کنیم
TRUE - اینجا جایی است که "سگ" دفن شده است. اگر به عنوان آخرین آرگومان تابع VPR مشخص كردن دروغ گویی (نادرست) یا 0، سپس تابع به دنبال آن خواهد بود مسابقه سخت در ستون مقدار (و در مورد ما خطای #N/A می دهد، زیرا در جدول تخفیف مقدار 49 وجود ندارد). اما اگر در عوض دروغ گویی نوشتن TRUE (درست است، واقعی) یا 1، سپس تابع نه به دنبال دقیق، بلکه نزدیکترین کوچکترین ارزش دارد و درصد تخفیف مورد نیاز را به ما می دهد.

نکته منفی این روش نیاز به مرتب سازی جدول تخفیف به ترتیب صعودی بر اساس ستون اول است. اگر چنین مرتب‌سازی وجود نداشته باشد (یا به ترتیب معکوس انجام شود)، فرمول ما کار نخواهد کرد:

پیدا کردن نزدیکترین عدد

بر این اساس، این رویکرد تنها می تواند برای یافتن کوچکترین مقدار مورد استفاده قرار گیرد. اگر می‌خواهید نزدیک‌ترین بزرگ‌ترین را پیدا کنید، باید از روش دیگری استفاده کنید.

روش 3. یافتن نزدیکترین بزرگترین با استفاده از توابع INDEX و MATCH

حالا بیایید از طرف دیگر به مشکل خود نگاه کنیم. فرض کنید چند مدل از پمپ های صنعتی با ظرفیت های مختلف را می فروشیم. جدول فروش سمت چپ قدرت مورد نیاز مشتری را نشان می دهد. ما باید پمپی را با نزدیکترین حداکثر یا برابر قدرت انتخاب کنیم، اما نه کمتر از آنچه پروژه مورد نیاز است.

تابع VLOOKUP در اینجا کمکی نمی کند، بنابراین باید از آنالوگ آن استفاده کنید - یک دسته از توابع INDEX (فهرست مطالب) و بیشتر در معرض (همخوانی داشتن):

پیدا کردن نزدیکترین عدد

در اینجا، تابع MATCH با آخرین آرگومان -1 در حالت یافتن نزدیکترین مقدار کار می کند و سپس تابع INDEX نام مدل مورد نیاز ما را از ستون مجاور استخراج می کند.

روش 4. نمای تابع جدید (XLOOKUP)

اگر نسخه ای از آفیس 365 با تمام به روز رسانی های نصب شده دارید، به جای VLOOKUP (VLOOKUP) می توانید از آنالوگ آن استفاده کنید - تابع VIEW (XLOOKUP)، که قبلاً به تفصیل آن را تحلیل کردم:

پیدا کردن نزدیکترین عدد

اینجا:

B4 – ارزش اولیه مقدار محصولی که برای آن به دنبال تخفیف هستیم
4 دلار G$: 8 دلار G$ - محدوده ای که ما به دنبال مسابقات هستیم
$H$4:$H$8 - دامنه نتایجی که می خواهید تخفیف را از آن برگردانید
استدلال چهارم (-1) شامل جستجوی نزدیکترین کوچکترین عددی است که می خواهیم به جای تطبیق دقیق.

از مزایای این روش عدم نیاز به مرتب سازی جدول تخفیف و امکان جستجو در صورت لزوم نه تنها نزدیکترین کوچکترین، بلکه نزدیکترین بزرگترین مقدار است. آخرین آرگومان در این مورد 1 خواهد بود.

اما، متأسفانه، هنوز همه این ویژگی را ندارند - فقط دارندگان خوشحال Office 365.

روش 5. Power Query

اگر هنوز با افزونه قدرتمند و کاملا رایگان Power Query برای اکسل آشنا نیستید، پس اینجا هستید. اگر قبلاً آشنا هستید، بیایید سعی کنیم از آن برای حل مشکل خود استفاده کنیم.

بیایید ابتدا چند کار مقدماتی انجام دهیم:

بیایید جداول منبع خود را با استفاده از میانبر صفحه کلید به پویا (هوشمند) تبدیل کنیم کلیدهای Ctrl+T یا تیم صفحه اصلی – قالب بندی به صورت جدول (صفحه اصلی - قالب بندی به عنوان جدول).
برای وضوح، بیایید آنها را نام ببریم. حراجی и تخفیف برگ سازنده (طرح).
هر یک از جداول را به نوبه خود با استفاده از دکمه در Power Query بارگیری کنید از جدول/محدوده برگ داده ها (داده ها - از جدول / محدوده). در نسخه های اخیر اکسل، این دکمه به تغییر نام یافته است با برگ (از برگه).
اگر جدول ها دارای نام ستون های مختلف با مقادیر هستند، مانند مثال ما ("مقدار کالا" و "تعداد از ...")، پس باید آنها را در Power Query تغییر نام داده و همان نام گذاری کنید.
پس از آن، می توانید با انتخاب دستور در پنجره ویرایشگر Power Query به اکسل برگردید صفحه اصلی — بستن و بارگذاری — بستن و بارگیری در… (صفحه اصلی - بستن و بارگیری - بستن و بارگیری به…) و سپس گزینه فقط یک اتصال ایجاد کنید (فقط ایجاد اتصال).
سپس جالب ترین شروع می شود. اگر در Power Query تجربه دارید، پس من فرض می‌کنم که خط فکری بعدی باید در جهت ادغام این دو جدول با جوین کوئری (ادغام) a la VLOOKUP باشد، همانطور که در روش قبلی انجام شد. در واقع باید در حالت افزودن ادغام شویم که در نگاه اول اصلا مشخص نیست. در تب Excel انتخاب کنید داده – دریافت داده – ترکیب درخواست ها – افزودن (داده - دریافت داده - ترکیب پرس و جوها - پیوست) و سپس میزهای ما حراجی и تخفیف در پنجره ای که ظاهر می شود:
پس از کلیک روی OK میزهای ما به یک کل واحد چسبانده می شوند - زیر یکدیگر. لطفا توجه داشته باشید که ستون های حاوی مقدار کالا در این جداول زیر یکدیگر قرار می گیرند، زیرا. آنها هم نام دارند:
اگر دنباله اصلی ردیف‌ها در جدول فروش برای شما مهم است، پس برای اینکه بعد از همه تبدیل‌های بعدی بتوانید آن را بازیابی کنید، با استفاده از دستور یک ستون شماره‌دار به جدول خود اضافه کنید. اضافه کردن یک ستون - ستون شاخص (افزودن ستون - ستون فهرست). اگر ترتیب خطوط برای شما مهم نیست، می توانید این مرحله را رد کنید.
اکنون با استفاده از لیست کشویی در سربرگ جدول، آن را بر اساس ستون مرتب کنید مقدار صعودی:
و ترفند اصلی: روی هدر ستون کلیک راست کنید تخفیف یک تیم انتخاب کنید پر کردن - پایین (پر کردن - پایین). سلول های خالی با تهی به طور خودکار با مقادیر تخفیف قبلی پر می شود:
باقی مانده است که دنباله اصلی ردیف ها را با مرتب سازی بر اساس ستون بازیابی کنیم شاخص (بعداً می توانید با خیال راحت آن را حذف کنید) و با یک فیلتر از شر خطوط غیر ضروری خلاص شوید تهی توسط ستون کد تراکنش: