البحث عن أقرب رقم - طعام صحي بالقرب مني

المحتويات

من الناحية العملية ، غالبًا ما تكون هناك حالات نحتاج فيها أنا وأنت إلى العثور على أقرب قيمة في مجموعة (جدول) فيما يتعلق برقم معين. يمكن أن يكون ، على سبيل المثال:

حساب الخصم حسب الحجم.
حساب مقدار المكافآت حسب تنفيذ الخطة.
حساب أسعار الشحن حسب المسافة.
اختيار الحاويات المناسبة للبضائع ، إلخ.

علاوة على ذلك ، قد يكون التقريب مطلوبًا لأعلى ولأسفل ، اعتمادًا على الموقف.

هناك عدة طرق - واضحة وليست واضحة - لحل مثل هذه المشكلة. دعونا ننظر إليهم بالتسلسل.

بادئ ذي بدء ، دعنا نتخيل موردًا يقدم خصومات على البيع بالجملة ، وتعتمد النسبة المئوية للخصم على كمية البضائع المشتراة. على سبيل المثال ، عند شراء أكثر من 5 قطع ، يتم منح خصم 2٪ ، وعند الشراء من 20 قطعة - بالفعل 6٪ ، إلخ.

كيف يتم حساب نسبة الخصم بسرعة وبشكل جميل عند إدخال كمية البضاعة المشتراة؟

الطريقة 1: IFs المتداخلة

طريقة من سلسلة "ماذا يوجد لتفكر - أنت بحاجة للقفز!". استخدام الوظائف المتداخلة IF (إذا) للتحقق بالتسلسل مما إذا كانت قيمة الخلية تقع في كل من الفواصل الزمنية وعرض خصم للنطاق المقابل. لكن الصيغة في هذه الحالة يمكن أن تكون مرهقة للغاية:

إيجاد أقرب رقم

أعتقد أنه من الواضح أن تصحيح أخطاء مثل هذه "الدمية الوحشية" أو محاولة إضافة بعض الشروط الجديدة إليها بعد مرور بعض الوقت أمر ممتع.

بالإضافة إلى ذلك ، يحتوي Microsoft Excel على حد تداخل لوظيفة IF - 7 مرات في الإصدارات القديمة و 64 مرة في الإصدارات الأحدث. ماذا لو احتجت المزيد؟

الطريقة الثانية. VLOOKUP مع عرض الفاصل الزمني

هذه الطريقة أكثر إحكاما. لحساب نسبة الخصم ، استخدم الدالة الأسطورية VPR (البحث) في وضع البحث التقريبي:

إيجاد أقرب رقم

أين

B4 - قيمة كمية البضائع في المعاملة الأولى التي نبحث عنها حسمًا
4 دولارات أمريكية: 8 دولارات أمريكية - رابط لجدول الخصم - بدون "رأس" وبالعناوين مثبتة بعلامة $.
2 - الرقم الترتيبي للعمود في جدول الخصم الذي نريد الحصول منه على قيمة الخصم
الحقيقة - هذا هو المكان الذي دُفن فيه "الكلب". إذا كانت الوسيطة الدالة الأخيرة VPR تحديد يكذب أو ملقاه (خاطئة) أو 0، ثم ستبحث عن الوظيفة تطابق صارم في عمود الكمية (وفي حالتنا ستعطي الخطأ # N / A ، حيث لا توجد قيمة 49 في جدول الخصم). ولكن إذا بدلا من ذلك يكذب أو ملقاه اكتب الحقيقة (صحيح) أو 1، فإن الوظيفة لن تبحث عن بالضبط ، ولكن أقرب أصغر قيمة وسوف تعطينا النسبة المئوية للخصم الذي نحتاجه.

الجانب السلبي لهذه الطريقة هو الحاجة إلى فرز جدول الخصم بترتيب تصاعدي حسب العمود الأول. إذا لم يكن هناك مثل هذا الفرز (أو تم إجراؤه بترتيب عكسي) ، فلن تعمل صيغتنا:

إيجاد أقرب رقم

وفقًا لذلك ، لا يمكن استخدام هذا النهج إلا للعثور على أقرب قيمة أصغر. إذا كنت بحاجة إلى العثور على أقرب الأقرب ، فعليك استخدام طريقة مختلفة.

الطريقة الثالثة: إيجاد أقرب أكبر باستخدام دالتي INDEX و MATCH

الآن دعونا نلقي نظرة على مشكلتنا من الجانب الآخر. لنفترض أننا نبيع عدة نماذج من المضخات الصناعية ذات السعات المختلفة. يوضح جدول المبيعات الموجود على اليسار الطاقة المطلوبة من قبل العميل. نحن بحاجة إلى اختيار مضخة من أقرب قوة قصوى أو متساوية ، ولكن ليس أقل مما هو مطلوب من قبل المشروع.

لن تساعد وظيفة VLOOKUP هنا ، لذلك سيتعين عليك استخدام نظيرتها - مجموعة من وظائف INDEX (فهرس) وأكثر انكشافاً (مباراة):

إيجاد أقرب رقم

هنا ، تعمل دالة MATCH مع الوسيطة الأخيرة -1 في وضع البحث عن أقرب قيمة أكبر ، وتقوم وظيفة INDEX بعد ذلك باستخراج اسم النموذج الذي نحتاجه من العمود المجاور.

الطريقة الرابعة: عرض وظيفة جديدة (XLOOKUP)

إذا كان لديك إصدار من Office 365 مع تثبيت جميع التحديثات ، فبدلاً من VLOOKUP (البحث) يمكنك استخدام التناظرية - وظيفة العرض (زلوكوب)، والتي قمت بالفعل بتحليلها بالتفصيل:

إيجاد أقرب رقم

هنا:

B4 - القيمة الأولية لكمية المنتج التي نبحث عن خصم لها
4 دولارات أمريكية: 8 دولارات أمريكية - النطاق الذي نبحث فيه عن المباريات
$ H $ 4: $ H $ 8 - نطاق النتائج التي تريد إرجاع الخصم منها
الحجة الرابعة (-1) البحث عن أقرب رقم نريده بدلاً من المطابقة التامة.

تتمثل مزايا هذه الطريقة في عدم وجود حاجة لفرز جدول الخصم والقدرة على البحث ، إذا لزم الأمر ، ليس فقط عن أقرب قيمة أصغر ، ولكن أيضًا أقرب قيمة أكبر. ستكون الحجة الأخيرة في هذه الحالة هي 1.

ولكن ، لسوء الحظ ، لا يمتلك الجميع هذه الميزة حتى الآن - فقط أصحاب Office 365 السعداء.

الطريقة الثالثة. استعلام الطاقة

إذا لم تكن على دراية بوظيفة Power Query الإضافية القوية والمجانية تمامًا لبرنامج Excel ، فأنت هنا. إذا كنت مألوفًا بالفعل ، فلنحاول استخدامه لحل مشكلتنا.

لنقم ببعض الأعمال التحضيرية أولاً:

دعنا نحول جداول المصدر إلى ديناميكية (ذكية) باستخدام اختصار لوحة المفاتيح CTRL+T أو فريق الصفحة الرئيسية - تنسيق كجدول (الصفحة الرئيسية - تنسيق كجدول).
من أجل الوضوح ، دعنا نعطيهم أسماء. المبيعات и خصومات علامة التبويب منشئ (التصميم).
قم بتحميل كل جدول بدوره إلى Power Query باستخدام الزر من الجدول / النطاق علامة التبويب البيانات (البيانات - من جدول / نطاق). في الإصدارات الأخيرة من Excel ، تمت إعادة تسمية هذا الزر إلى بأوراق الشجر (من الورقة).
إذا كانت الجداول تحتوي على أسماء أعمدة مختلفة بكميات ، كما في مثالنا ("كمية البضائع" و "الكمية من ...") ، فيجب إعادة تسميتها في Power Query وتسميتها بنفس الاسم.
بعد ذلك ، يمكنك العودة إلى Excel عن طريق تحديد الأمر في نافذة محرر Power Query الصفحة الرئيسية - إغلاق وتحميل - إغلاق وتحميل ... (الصفحة الرئيسية - إغلاق وتحميل - إغلاق وتحميل إلى ...) ثم الخيار فقط قم بإنشاء اتصال (فقط إنشاء اتصال).
ثم يبدأ الأكثر إثارة للاهتمام. إذا كانت لديك خبرة في Power Query ، فأنا أفترض أن خط التفكير الإضافي يجب أن يكون في اتجاه دمج هذين الجدولين مع استعلام ربط (دمج) على غرار VLOOKUP ، كما كان الحال في الطريقة السابقة. في الواقع ، سنحتاج إلى الدمج في وضع الإضافة ، وهو أمر غير واضح على الإطلاق للوهلة الأولى. حدد في علامة التبويب Excel البيانات - الحصول على البيانات - تجميع الطلبات - إضافة (البيانات - إحضار البيانات - تجميع الاستعلامات - إلحاق) ومن ثم طاولاتنا المبيعات и خصومات في النافذة التي تظهر:
بعد النقر على OK سيتم لصق طاولاتنا في كل واحد - تحت بعضها البعض. يرجى ملاحظة أن الأعمدة التي تحتوي على كمية البضائع في هذه الجداول تقع تحت بعضها البعض ، لأن. لديهم نفس الاسم:
إذا كان التسلسل الأصلي للصفوف في جدول المبيعات مهمًا بالنسبة لك ، فعندئذٍ بعد كل التحولات اللاحقة يمكنك استعادتها ، أضف عمودًا مرقمًا إلى جدولنا باستخدام الأمر إضافة عمود - عمود الفهرس (إضافة عمود - عمود الفهرس). إذا كان تسلسل الأسطر لا يهمك ، فيمكنك تخطي هذه الخطوة.
الآن ، باستخدام القائمة المنسدلة في رأس الجدول ، قم بفرزها حسب العمود كمية تصاعدي:
والحيلة الرئيسية: النقر بزر الماوس الأيمن على رأس العمود خصم الإخوة الإضافي اختر فريق تعبئة لأسفل (تعبئة لأسفل). خلايا فارغة مع فارغة يتم ملؤها تلقائيًا بقيم الخصم السابقة:
يبقى استعادة التسلسل الأصلي للصفوف بالفرز حسب العمود فهرس (يمكنك حذفه بأمان لاحقًا) والتخلص من الأسطر غير الضرورية باستخدام مرشح فارغة حسب العمود رمز العملية: