নিকটতম নম্বর খোঁজা

অনুশীলনে, প্রায়শই এমন কিছু ঘটনা ঘটে যখন আপনি এবং আমাকে একটি নির্দিষ্ট সংখ্যার সাথে সম্পর্কিত একটি সেটে (টেবিল) নিকটতম মান খুঁজে বের করতে হবে। এটা হতে পারে, উদাহরণস্বরূপ:

• ভলিউমের উপর নির্ভর করে ছাড়ের গণনা।
  
• প্ল্যান বাস্তবায়নের উপর নির্ভর করে বোনাসের পরিমাণের হিসাব।
  
• দূরত্বের উপর নির্ভর করে শিপিং হারের গণনা।
• পণ্যের জন্য উপযুক্ত পাত্র নির্বাচন, ইত্যাদি

অধিকন্তু, পরিস্থিতির উপর নির্ভর করে রাউন্ডিং উপরে এবং নীচে উভয়ই প্রয়োজন হতে পারে।

এই ধরনের সমস্যা সমাধানের জন্য বেশ কয়েকটি উপায় রয়েছে - সুস্পষ্ট এবং এতটা স্পষ্ট নয়। আসুন তাদের ক্রমানুসারে দেখি।

শুরু করার জন্য, আসুন একজন সরবরাহকারীকে কল্পনা করি যিনি পাইকারিতে ছাড় দেন এবং ডিসকাউন্টের শতাংশ ক্রয়কৃত পণ্যের পরিমাণের উপর নির্ভর করে। উদাহরণস্বরূপ, 5টির বেশি পিস কেনার সময় 2% ছাড় দেওয়া হয়, এবং 20 পিস থেকে কেনার সময় - ইতিমধ্যে 6% ইত্যাদি।

কেনা পণ্যের পরিমাণ প্রবেশ করার সময় কীভাবে দ্রুত এবং সুন্দরভাবে ডিসকাউন্ট শতাংশ গণনা করবেন?

পদ্ধতি 1: নেস্টেড IFs

সিরিজের একটি পদ্ধতি "কি ভাবার আছে - আপনাকে লাফ দিতে হবে!"। নেস্টেড ফাংশন ব্যবহার করে IF (আইএফ) সেল মান প্রতিটি ব্যবধানের মধ্যে পড়ে কিনা তা পর্যায়ক্রমে পরীক্ষা করতে এবং সংশ্লিষ্ট পরিসরের জন্য একটি ছাড় প্রদর্শন করতে। তবে এই ক্ষেত্রে সূত্রটি খুব কষ্টকর হতে পারে: 

আমি মনে করি এটি স্পষ্ট যে এই ধরনের একটি "দানব পুতুল" ডিবাগ করা বা কিছু সময় পরে এটিতে কয়েকটি নতুন শর্ত যুক্ত করার চেষ্টা করা মজাদার।

উপরন্তু, মাইক্রোসফ্ট এক্সেলের IF ফাংশনের জন্য একটি নেস্টিং সীমা রয়েছে - পুরানো সংস্করণগুলিতে 7 বার এবং নতুন সংস্করণগুলিতে 64 বার৷ আপনি আরো প্রয়োজন হলে কি?

পদ্ধতি 2. ইন্টারভাল ভিউ সহ VLOOKUP

এই পদ্ধতিটি অনেক বেশি কম্প্যাক্ট। ডিসকাউন্ট শতাংশ গণনা করতে, কিংবদন্তি ফাংশন ব্যবহার করুন VPR (ভলুকআপ) আনুমানিক অনুসন্ধান মোডে:

কোথায়

• B4 - প্রথম লেনদেনে পণ্যের পরিমাণের মূল্য যার জন্য আমরা একটি ছাড় খুঁজছি
  
• $G$4:$H$8 - ডিসকাউন্ট টেবিলের একটি লিঙ্ক - একটি "শিরোনাম" ছাড়া এবং $ চিহ্নের সাথে ঠিক করা ঠিকানাগুলির সাথে।
  
• 2 — ডিসকাউন্ট টেবিলের কলামের ক্রমিক সংখ্যা যেখান থেকে আমরা ডিসকাউন্ট মান পেতে চাই
• 'সত্য' - এখানেই "কুকুর" কবর দেওয়া হয়। যদি শেষ ফাংশন আর্গুমেন্ট হিসাবে VPR উল্লেখ মিথ্যা (মিথ্যা) অথবা 0, তারপর ফাংশনটি সন্ধান করবে কঠোর ম্যাচ পরিমাণ কলামে (এবং আমাদের ক্ষেত্রে এটি একটি #N/A ত্রুটি দেবে, যেহেতু ডিসকাউন্ট টেবিলে কোন মান 49 নেই)। কিন্তু পরিবর্তে যদি মিথ্যা লেখা 'সত্য' (সত্য) অথবা 1, তারপর ফাংশন সঠিক জন্য না দেখাবে, কিন্তু নিকটতম ক্ষুদ্রতম মান এবং আমাদের প্রয়োজনীয় ছাড়ের শতাংশ দেবে।

এই পদ্ধতির নেতিবাচক দিক হল ডিসকাউন্ট টেবিলটিকে প্রথম কলাম দ্বারা আরোহী ক্রমে সাজানোর প্রয়োজন। যদি এমন কোন সাজানো না থাকে (বা এটি বিপরীত ক্রমে করা হয়), তাহলে আমাদের সূত্র কাজ করবে না:

তদনুসারে, এই পদ্ধতিটি শুধুমাত্র নিকটতম ক্ষুদ্রতম মান খুঁজে পেতে ব্যবহার করা যেতে পারে। আপনি যদি নিকটতম বৃহত্তম খুঁজে পেতে চান, তাহলে আপনাকে একটি ভিন্ন পদ্ধতি ব্যবহার করতে হবে।

পদ্ধতি 3. INDEX এবং MATCH ফাংশন ব্যবহার করে নিকটতম বৃহত্তম খুঁজে বের করা

এখন অন্য দিক থেকে আমাদের সমস্যা দেখা যাক. ধরুন আমরা বিভিন্ন ক্ষমতার বিভিন্ন মডেলের শিল্প পাম্প বিক্রি করি। বাম দিকের বিক্রয় টেবিলটি গ্রাহকের প্রয়োজনীয় শক্তি দেখায়। আমাদের নিকটতম সর্বাধিক বা সমান শক্তির একটি পাম্প নির্বাচন করতে হবে, তবে প্রকল্পের প্রয়োজনের চেয়ে কম নয়।

VLOOKUP ফাংশন এখানে সাহায্য করবে না, তাই আপনাকে এর অ্যানালগ ব্যবহার করতে হবে - একগুচ্ছ INDEX ফাংশন (INDEX) এবং আরো উদ্ভাসিত (ম্যাচ):

এখানে, শেষ আর্গুমেন্ট -1 সহ MATCH ফাংশনটি সবচেয়ে কাছের সবচেয়ে বড় মান খুঁজে বের করার মোডে কাজ করে এবং INDEX ফাংশন তখন পাশের কলাম থেকে আমাদের প্রয়োজনীয় মডেল নামটি বের করে।

পদ্ধতি 4. নতুন ফাংশন VIEW (XLOOKUP)

আপনার কাছে যদি অফিস 365 এর একটি সংস্করণ থাকে যেখানে সমস্ত আপডেট ইনস্টল করা আছে, তাহলে VLOOKUP এর পরিবর্তে (ভলুকআপ) আপনি এর অ্যানালগ ব্যবহার করতে পারেন - ভিউ ফাংশন (এক্সলুকআপ), যা আমি ইতিমধ্যে বিস্তারিতভাবে বিশ্লেষণ করেছি:

এখানে:

• B4 - পণ্যের পরিমাণের প্রাথমিক মান যার জন্য আমরা ডিসকাউন্ট খুঁজছি
• $G$4:$G$8 - যে পরিসরে আমরা ম্যাচ খুঁজছি
  
• $H$4:$H$8 - ফলাফলের পরিসীমা যেখান থেকে আপনি ডিসকাউন্ট ফেরত দিতে চান
• চতুর্থ যুক্তি (-1) একটি সঠিক মিলের পরিবর্তে নিকটতম ক্ষুদ্রতম সংখ্যার জন্য অনুসন্ধান অন্তর্ভুক্ত করে যা আমরা চাই।
  

এই পদ্ধতির সুবিধাগুলি হল ডিসকাউন্ট টেবিল বাছাই করার প্রয়োজন নেই এবং প্রয়োজনে অনুসন্ধান করার ক্ষমতা, শুধুমাত্র নিকটতম ক্ষুদ্রতম নয়, নিকটতম বৃহত্তম মানও। এই মামলায় শেষ যুক্তিতর্ক হবে ১.

কিন্তু, দুর্ভাগ্যবশত, সকলের কাছে এখনও এই বৈশিষ্ট্যটি নেই - শুধুমাত্র Office 365 এর খুশি মালিকরা।

পদ্ধতি 5. পাওয়ার কোয়েরি

আপনি যদি এখনও এক্সেলের জন্য শক্তিশালী এবং সম্পূর্ণ বিনামূল্যে পাওয়ার কোয়েরি অ্যাড-ইন এর সাথে পরিচিত না হন, তাহলে আপনি এখানে আছেন। আপনি যদি ইতিমধ্যে পরিচিত হন, তাহলে আসুন আমাদের সমস্যা সমাধানের জন্য এটি ব্যবহার করার চেষ্টা করি।

প্রথমে কিছু প্রস্তুতিমূলক কাজ করা যাক:

1. আসুন একটি কীবোর্ড শর্টকাট ব্যবহার করে আমাদের উত্স টেবিলগুলিকে গতিশীল (স্মার্ট) তে রূপান্তর করি জন্য ctrl+T বা দল হোম - একটি টেবিল হিসাবে বিন্যাস (হোম - টেবিল হিসাবে ফর্ম্যাট).
2. স্বচ্ছতার জন্য, আসুন তাদের নাম দেওয়া যাক। বিক্রয় и ছাড় ট্যাব রচয়িতা (নকশা).
3. বোতামটি ব্যবহার করে প্রতিটি টেবিলকে পাওয়ার কোয়েরিতে লোড করুন টেবিল/রেঞ্জ থেকে ট্যাব উপাত্ত (ডেটা — টেবিল/রেঞ্জ থেকে). এক্সেলের সাম্প্রতিক সংস্করণগুলিতে, এই বোতামটির নাম পরিবর্তন করা হয়েছে পাতা দিয়ে (শীট থেকে).
   
4. যদি টেবিলের বিভিন্ন কলামের নাম থাকে পরিমাণ সহ, যেমন আমাদের উদাহরণে ("পণ্যের পরিমাণ" এবং "পরিমাণ থেকে ..."), তাহলে তাদের অবশ্যই পাওয়ার কোয়েরিতে নামকরণ করতে হবে এবং একই নামকরণ করতে হবে।
5. এর পরে, আপনি পাওয়ার কোয়েরি সম্পাদক উইন্ডোতে কমান্ডটি নির্বাচন করে এক্সেলে ফিরে যেতে পারেন হোম — বন্ধ করুন এবং লোড করুন — বন্ধ করুন এবং লোড করুন... (বাড়ি — বন্ধ করুন এবং লোড করুন — বন্ধ করুন এবং লোড করুন...) এবং তারপর বিকল্প শুধু একটি সংযোগ তৈরি করুন (শুধুমাত্র সংযোগ তৈরি করুন).

   
   

6. তারপর সবচেয়ে আকর্ষণীয় শুরু হয়. আপনার যদি পাওয়ার ক্যোয়ারীতে অভিজ্ঞতা থাকে, তাহলে আমি অনুমান করি যে এই দুটি টেবিলকে জয়েন কোয়েরি (একত্রীকরণ) একটি লা VLOOKUP-এর সাথে একত্রিত করার দিকের চিন্তাধারার আরও লাইন হওয়া উচিত, যেমনটি আগের পদ্ধতিতে ছিল। আসলে, আমাদের অ্যাড মোডে মার্জ করতে হবে, যা প্রথম নজরে একেবারেই স্পষ্ট নয়। এক্সেল ট্যাবে নির্বাচন করুন ডেটা - ডেটা পান - অনুরোধগুলি একত্রিত করুন - যোগ করুন (ডেটা — ডেটা পান — কোয়েরিগুলি একত্রিত করুন — যুক্ত করুন) এবং তারপর আমাদের টেবিল বিক্রয় и ছাড় প্রদর্শিত উইন্ডোতে:

   

7. ক্লিক করার পরে OK আমাদের টেবিলগুলি একে অপরের নীচে - একটি একক পুরোতে আঠালো হবে। দয়া করে মনে রাখবেন যে এই টেবিলে পণ্যের পরিমাণ সহ কলামগুলি একে অপরের নীচে পড়েছিল, কারণ। তাদের একই নাম আছে:

   

8. যদি বিক্রয় সারণীতে সারিগুলির মূল ক্রমটি আপনার কাছে গুরুত্বপূর্ণ হয়, তাহলে যাতে পরবর্তী সমস্ত রূপান্তরের পরে আপনি এটিকে পুনরুদ্ধার করতে পারেন, কমান্ডটি ব্যবহার করে আমাদের টেবিলে একটি সংখ্যাযুক্ত কলাম যুক্ত করুন একটি কলাম যোগ করা হচ্ছে - সূচক কলাম (কলাম যোগ করুন — সূচক কলাম). যদি লাইনের ক্রম আপনার কাছে গুরুত্বপূর্ণ না হয়, তাহলে আপনি এই ধাপটি এড়িয়ে যেতে পারেন।
9. এখন, টেবিলের হেডারে ড্রপ-ডাউন তালিকা ব্যবহার করে, কলাম অনুসারে সাজান পরিমাণ আরোহী:

   

10. এবং প্রধান কৌশল: কলাম হেডারে ডান-ক্লিক করুন ডিসকাউন্ট একটি দল নির্বাচন করুন নিচে পূরণ (নিচে পূরণ). সঙ্গে খালি ঘর অকার্যকর পূর্ববর্তী ডিসকাউন্ট মান দিয়ে স্বয়ংক্রিয়ভাবে পূরণ করা হয়:

    

11. কলাম অনুসারে বাছাই করে সারিগুলির মূল ক্রমটি পুনরুদ্ধার করা বাকি রয়েছে সূচক (আপনি নিরাপদে এটি পরে মুছে ফেলতে পারেন) এবং একটি ফিল্টার দিয়ে অপ্রয়োজনীয় লাইন পরিত্রাণ পান অকার্যকর কলাম দ্বারা লেনদেনের কোড:

    
    

• ডেটা অনুসন্ধান এবং সন্ধান করতে VLOOKUP ফাংশন ব্যবহার করে৷
• VLOOKUP (VLOOKUP) ব্যবহার করা কেস-সংবেদনশীল
• XNUMXD VLOOKUP (VLOOKUP)