বিষয়বস্তু
আমরা সম্প্রতি একটি নিবন্ধ উত্সর্গ করেছি সবচেয়ে দরকারী এক্সেল ফাংশন যাকে বলা হয় VPR এবং দেখিয়েছে কিভাবে এটি একটি ডাটাবেস থেকে একটি ওয়ার্কশীট কক্ষে প্রয়োজনীয় তথ্য বের করতে ব্যবহার করা যেতে পারে। আমরা আরও উল্লেখ করেছি যে ফাংশনের জন্য দুটি ব্যবহারের ক্ষেত্রে রয়েছে VPR এবং তাদের মধ্যে শুধুমাত্র একটি ডাটাবেস কোয়েরি নিয়ে কাজ করে। এই নিবন্ধে, আপনি ফাংশন ব্যবহার করার আরেকটি কম পরিচিত উপায় শিখবেন VPR এক্সেল ইন।
আপনি যদি এখনও এটি না করে থাকেন, তাহলে ফাংশন সম্পর্কে শেষ নিবন্ধটি পড়তে ভুলবেন না VPR, কারণ নীচের সমস্ত তথ্য অনুমান করে যে আপনি ইতিমধ্যেই প্রথম নিবন্ধে বর্ণিত নীতিগুলির সাথে পরিচিত৷
ডাটাবেস, ফাংশন নিয়ে কাজ করার সময় VPR একটি অনন্য শনাক্তকারী পাস করা হয়, যা আমরা যে তথ্যটি খুঁজে পেতে চাই তা সনাক্ত করতে ব্যবহৃত হয় (উদাহরণস্বরূপ, একটি পণ্য কোড বা একটি গ্রাহক সনাক্তকরণ নম্বর)। এই অনন্য কোডটি অবশ্যই ডাটাবেসে উপস্থিত থাকতে হবে, অন্যথায় VPR একটি ত্রুটি রিপোর্ট করবে। এই নিবন্ধে, আমরা ফাংশনটি ব্যবহার করার এই উপায়টি দেখব VPRযখন আইডিটি ডাটাবেসে উপস্থিত থাকে না। যেন ফাংশন VPR আনুমানিক মোডে স্যুইচ করা হয়েছে, এবং যখন আমরা কিছু খুঁজতে চাই তখন কোন ডেটা আমাদের প্রদান করতে হবে তা বেছে নেয়। নির্দিষ্ট পরিস্থিতিতে, এটি ঠিক কি প্রয়োজন।
জীবন থেকে একটি উদাহরণ। আমরা টাস্ক সেট
আসুন এই নিবন্ধটিকে একটি বাস্তব জীবনের উদাহরণ দিয়ে ব্যাখ্যা করি - বিক্রয় মেট্রিক্সের বিস্তৃত পরিসরের উপর ভিত্তি করে কমিশন গণনা করা। আমরা একটি খুব সাধারণ বিকল্প দিয়ে শুরু করব, এবং তারপরে আমরা ধীরে ধীরে এটিকে জটিল করব যতক্ষণ না সমস্যার একমাত্র যুক্তিসঙ্গত সমাধান ফাংশনটি ব্যবহার করা হয়। VPR. আমাদের কাল্পনিক কাজের প্রাথমিক দৃশ্যকল্পটি নিম্নরূপ: একজন বিক্রয়কর্মী যদি বছরে $30000 এর বেশি বিক্রয় করে, তাহলে তার কমিশন 30%। অন্যথায়, কমিশন মাত্র 20%। আসুন এটিকে টেবিলের আকারে রাখি:
বিক্রেতা সেল B1-এ তাদের বিক্রয় ডেটা প্রবেশ করে, এবং সেল B2-এর সূত্রটি বিক্রেতা আশা করতে পারে এমন সঠিক কমিশন রেট নির্ধারণ করে। পরিবর্তে, বিক্রেতাকে যে মোট কমিশন প্রাপ্ত করা উচিত তা গণনা করতে সেল B3-এ ফলাফলের হার ব্যবহার করা হয় (সহজভাবে কোষ B1 এবং B2 গুণ করা)।
টেবিলের সবচেয়ে আকর্ষণীয় অংশটি সেল B2-এ রয়েছে - এটি কমিশনের হার নির্ধারণের সূত্র। এই সূত্রে একটি এক্সেল ফাংশন বলা হয় IF (IF)। এই ফাংশনটির সাথে পরিচিত নন এমন পাঠকদের জন্য, আমি ব্যাখ্যা করব কিভাবে এটি কাজ করে:
IF(condition, value if true, value if false)
ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)
কন্ডিশন একটি ফাংশন আর্গুমেন্ট যা উভয়ের মান নেয় সত্য কোড (সত্য), বা মিথ্যা (মিথ্যা)। উপরের উদাহরণে, অভিব্যক্তি B1
এটা কি সত্য যে B1 B5 এর চেয়ে কম?
অথবা আপনি এটি ভিন্নভাবে বলতে পারেন:
এটা কি সত্য যে বছরের জন্য বিক্রয়ের মোট পরিমাণ থ্রেশহোল্ড মূল্যের চেয়ে কম?
আমরা যদি এই প্রশ্নের উত্তর দেই হ্যাঁ (TRUE), তারপর ফাংশন রিটার্ন করে মান সত্য হলে (সত্য হলে মান)। আমাদের ক্ষেত্রে, এটি হবে সেল B6-এর মান, অর্থাৎ মোট বিক্রয় যখন থ্রেশহোল্ডের নিচে থাকে তখন কমিশনের হার। আমরা যদি প্রশ্নের উত্তর দেই কোন (FALSE) তারপর ফিরে আসে মান মিথ্যা হলে (ভুল হলে মান)। আমাদের ক্ষেত্রে, এটি সেল B7-এর মান, অর্থাৎ মোট বিক্রয় যখন থ্রেশহোল্ডের উপরে থাকে তখন কমিশনের হার।
আপনি দেখতে পাচ্ছেন, যদি আমরা $20000 এর মোট বিক্রয় নিই, আমরা সেল B2-এ 20% কমিশন রেট পাব। যদি আমরা $40000 এর মান লিখি, তাহলে কমিশনের হার 30% দ্বারা পরিবর্তিত হবে:
এইভাবে আমাদের টেবিল কাজ করে।
আমরা টাস্ক জটিল
আসুন জিনিসগুলিকে আরও কিছুটা কঠিন করা যাক। আরেকটি থ্রেশহোল্ড সেট করা যাক: যদি বিক্রেতা $40000-এর বেশি আয় করেন, তাহলে কমিশনের হার 40%-এ বেড়ে যায়:
সবকিছু সহজ এবং পরিষ্কার বলে মনে হচ্ছে, কিন্তু সেল B2-এ আমাদের সূত্র লক্ষণীয়ভাবে আরও জটিল হয়ে উঠেছে। আপনি যদি সূত্রটি ঘনিষ্ঠভাবে লক্ষ্য করেন, আপনি দেখতে পাবেন যে ফাংশনের তৃতীয় আর্গুমেন্ট IF (IF) আরেকটি পূর্ণাঙ্গ ফাংশনে পরিণত হয়েছে IF (IF)। এই নির্মাণকে একে অপরের মধ্যে ফাংশনের নেস্টিং বলা হয়। এক্সেল আনন্দের সাথে এই গঠনগুলিকে অনুমতি দেয়, এবং তারা এমনকি কাজ করে, কিন্তু সেগুলি পড়া এবং বোঝা অনেক কঠিন।
আমরা প্রযুক্তিগত বিশদগুলি অনুসন্ধান করব না - কেন এবং কীভাবে এটি কাজ করে এবং আমরা নেস্টেড ফাংশনগুলি লেখার সূক্ষ্মতার মধ্যে যাব না। সব পরে, এই ফাংশন নিবেদিত একটি নিবন্ধ VPR, এক্সেলের একটি সম্পূর্ণ নির্দেশিকা নয়।
যাই হোক না কেন, সূত্র আরও জটিল হয়! আমরা যদি সেইসব বিক্রেতাদের জন্য 50% কমিশনের হারের জন্য আরেকটি বিকল্প প্রবর্তন করি যারা বিক্রয় $50000-এর বেশি করে। এবং যদি কেউ $60000 এর বেশি বিক্রি করে থাকে তবে তারা কি 60% কমিশন দেবে?
এখন সেল B2-এর সূত্র, এমনকি যদি এটি ত্রুটি ছাড়াই লেখা হয়, সম্পূর্ণরূপে অপঠিত হয়ে গেছে। আমি মনে করি যে খুব কম লোকই আছে যারা তাদের প্রোজেক্টে 4 স্তরের নেস্টিং সহ সূত্র ব্যবহার করতে চায়। একটি সহজ উপায় থাকতে হবে?!
এবং যেমন একটি উপায় আছে! ফাংশন আমাদের সাহায্য করবে VPR.
আমরা সমস্যা সমাধানের জন্য VLOOKUP ফাংশন প্রয়োগ করি
আমাদের টেবিলের ডিজাইন একটু পরিবর্তন করা যাক। আমরা একই ক্ষেত্র এবং ডেটা রাখব, তবে সেগুলিকে একটি নতুন, আরও কমপ্যাক্ট উপায়ে সাজিয়ে রাখব:
একটি মুহূর্ত নিন এবং নতুন টেবিল নিশ্চিত করুন হার সারণী আগের থ্রেশহোল্ড টেবিলের মতো একই ডেটা অন্তর্ভুক্ত করে।
প্রধান ধারণা ফাংশন ব্যবহার করা হয় VPR টেবিল অনুযায়ী পছন্দসই ট্যারিফ হার নির্ধারণ করতে হার সারণী বিক্রয় ভলিউম উপর নির্ভর করে। দয়া করে মনে রাখবেন যে বিক্রেতা এমন পরিমাণে পণ্য বিক্রি করতে পারেন যা টেবিলের পাঁচটি থ্রেশহোল্ডের একটির সমান নয়। উদাহরণস্বরূপ, তিনি 34988 ডলারে বিক্রি করতে পারেন, কিন্তু এমন কোন পরিমাণ নেই। চলুন দেখা যাক কিভাবে ফাংশন VPR এমন পরিস্থিতি মোকাবেলা করতে পারে।
একটি VLOOKUP ফাংশন সন্নিবেশ করা হচ্ছে৷
সেল B2 নির্বাচন করুন (যেখানে আমরা আমাদের সূত্র সন্নিবেশ করতে চাই) এবং খুঁজুন VLOOKUP এক্সেল ফাংশন লাইব্রেরিতে (VLOOKUP) সূত্র (সূত্র) > ফাংশন লাইব্রেরি (ফাংশন লাইব্রেরি) > অনুসন্ধান এবং রেফারেন্স (রেফারেন্স এবং অ্যারে)।
একটি ডায়ালগ বক্স প্রদর্শিত হবে ফাংশন যুক্তি (ফাংশন আর্গুমেন্ট)। আমরা একে একে আর্গুমেন্টের মান পূরণ করি, দিয়ে শুরু করি দেখার মূল্য (দেখার মূল্য). এই উদাহরণে, এটি সেল B1 থেকে বিক্রির মোট পরিমাণ। মাঠে কার্সার রাখুন দেখার মূল্য (Lookup_value) এবং সেল B1 নির্বাচন করুন।
এর পরে, আপনাকে ফাংশনগুলি নির্দিষ্ট করতে হবে VPRযেখানে তথ্য সন্ধান করতে হবে। আমাদের উদাহরণে, এটি একটি টেবিল হার সারণী. মাঠে কার্সার রাখুন টেবিল_অ্যারে (টেবিল) এবং সম্পূর্ণ টেবিল নির্বাচন করুন হার সারণীহেডার ছাড়া।
এর পরে, আমাদের সূত্র ব্যবহার করে কোন কলাম থেকে ডেটা বের করতে হবে তা নির্দিষ্ট করতে হবে। আমরা কমিশনের হারে আগ্রহী, যা টেবিলের দ্বিতীয় কলামে রয়েছে। তাই যুক্তির জন্য ড Col_index_num (কলাম_সংখ্যা) মান 2 লিখুন।
এবং পরিশেষে, আমরা শেষ যুক্তি উপস্থাপন করি - রেঞ্জ_লুকআপ (Interval_lookup)।
গুরুত্বপূর্ণ: এটি এই যুক্তির ব্যবহার যা ফাংশন প্রয়োগ করার দুটি উপায়ের মধ্যে পার্থক্য করে VPR. ডাটাবেস নিয়ে কাজ করার সময় যুক্তি রেঞ্জ_লুকআপ (range_lookup) সবসময় একটি মান থাকতে হবে মিথ্যা (মিথ্যা) একটি সঠিক মিল অনুসন্ধান করতে। আমাদের ফাংশন ব্যবহার VPR, আমাদের অবশ্যই এই ক্ষেত্রটি ফাঁকা রাখতে হবে, অথবা একটি মান লিখতে হবে সত্য কোড (সত্য)। এই বিকল্পটি সঠিকভাবে নির্বাচন করা অত্যন্ত গুরুত্বপূর্ণ।
এটি পরিষ্কার করার জন্য, আমরা পরিচয় করিয়ে দেব সত্য কোড (সত্য) ক্ষেত্রে রেঞ্জ_লুকআপ (Interval_lookup)। যদিও, যদি আপনি ক্ষেত্রটি ফাঁকা রাখেন তবে এটি একটি ত্রুটি হবে না, যেহেতু সত্য কোড এটির ডিফল্ট মান:
আমরা সমস্ত প্যারামিটার পূরণ করেছি। এখন আমরা টিপুন OK, এবং Excel একটি ফাংশন দিয়ে আমাদের জন্য একটি সূত্র তৈরি করে VPR.
যদি আমরা মোট বিক্রয়ের পরিমাণের জন্য বিভিন্ন মান নিয়ে পরীক্ষা করি, তাহলে আমরা নিশ্চিত করব যে সূত্রটি সঠিকভাবে কাজ করে।
উপসংহার
যখন ফাংশন VPR ডাটাবেস, আর্গুমেন্ট নিয়ে কাজ করে রেঞ্জ_লুকআপ (range_lookup) অবশ্যই গ্রহণ করতে হবে মিথ্যা (মিথ্যা)। এবং মান হিসাবে প্রবেশ করান দেখার মূল্য (Lookup_value) ডাটাবেসে বিদ্যমান থাকা আবশ্যক। অন্য কথায়, এটি একটি সঠিক মিল খুঁজছে।
এই নিবন্ধে আমরা যে উদাহরণটি দেখেছি, তার সঠিক মিল পাওয়ার দরকার নেই। এই ক্ষেত্রে যখন ফাংশন VPR পছন্দসই ফলাফল ফেরত দিতে আনুমানিক মোডে স্যুইচ করতে হবে।
উদাহরণ স্বরূপ: আমরা $34988 বিক্রয় ভলিউম সহ একজন বিক্রয়কর্মীর জন্য কমিশন গণনায় কোন হার ব্যবহার করতে হবে তা নির্ধারণ করতে চাই। ফাংশন VPR আমাদের 30% এর একটি মান প্রদান করে, যা একেবারে সঠিক। কিন্তু কেন সূত্রটি ঠিক 30% এবং 20% বা 40% নয় এমন সারিটি নির্বাচন করেছে? আনুমানিক অনুসন্ধান বলতে কি বোঝায়? আসুন পরিষ্কার করা যাক.
যখন তর্ক রেঞ্জ_লুকআপ (interval_lookup) এর একটি মান আছে সত্য কোড (TRUE) বা বাদ দেওয়া, ফাংশন VPR প্রথম কলামের মাধ্যমে পুনরাবৃত্তি করে এবং সবচেয়ে বড় মান নির্বাচন করে যা লুকআপ মান অতিক্রম করে না।
গুরুত্বপূর্ণ পয়েন্ট: এই স্কিমটি কাজ করার জন্য, টেবিলের প্রথম কলামটি ক্রমবর্ধমান ক্রমে সাজাতে হবে।