agwebrunner.com

VLOOKUP এক্সেলের অন্যতম দরকারী কার্যকারিতা এবং এটিও স্বল্পতম বোঝা যায়। এই নিবন্ধে, আমরা একটি বাস্তব জীবনের উদাহরণ দিয়ে VLOOKUP কে নির্মূল করি। আমরা একটি ব্যবহারযোগ্য তৈরি করব চালানের টেম্পলেট একটি কল্পিত সংস্থার জন্য।

ভিএলুকআপ একটি এক্সেল ফাংশন। এই নিবন্ধটি ধরে নেওয়া হবে যে পাঠকের ইতিমধ্যে এক্সেল ফাংশনগুলির একটি ক্ষণস্থায়ী বোঝাপড়া রয়েছে এবং এটি সুম, গড় এবং আজকের মতো মৌলিক ফাংশনগুলি ব্যবহার করতে পারে। এর সর্বাধিক প্রচলিত ব্যবহারে, ভিএলুকআপ হ'ল একটি তথ্যশালা ফাংশন, এর অর্থ এটি ডাটাবেস টেবিলগুলির সাথে কাজ করে - বা আরও সহজভাবে, তালিকা এক্সেল ওয়ার্কশিটে জিনিস। কি ধরণের জিনিস? আমরা হব, যে কোন জিনিস সাজানোর. আপনার কাছে একটি ওয়ার্কশিট থাকতে পারে যাতে কর্মচারী, বা পণ্য, বা গ্রাহক, বা আপনার সিডি সংগ্রহের সিডি, বা রাতের আকাশের তারা রয়েছে। এটা আসলে কিছু যায় আসে না।

এখানে একটি তালিকা বা ডাটাবেসের উদাহরণ রয়েছে। এক্ষেত্রে এটি এমন পণ্যগুলির তালিকা যা আমাদের কল্পিত সংস্থা বিক্রয় করে:

সাধারণত এর মতো তালিকাগুলির তালিকার প্রতিটি আইটেমের জন্য কিছু ধরণের অনন্য শনাক্তকারী থাকে। এই ক্ষেত্রে, অনন্য সনাক্তকারীটি "আইটেম কোড" কলামে রয়েছে। দ্রষ্টব্য: VLOOKUP ফাংশনটি একটি ডাটাবেস / তালিকার সাথে কাজ করার জন্য, সেই তালিকার অবশ্যই একটি কলাম থাকতে হবে অনন্য শনাক্তকারী (বা "কী", বা "আইডি"), এবং সেই কলামটি অবশ্যই টেবিলের প্রথম কলাম হবে। উপরে আমাদের নমুনা ডাটাবেস এই মানদণ্ডকে সন্তুষ্ট করে।

VLOOKUP ব্যবহারের সবচেয়ে শক্ত অংশটি হ'ল এটির জন্য ঠিক কী তা বোঝা। সুতরাং আসুন আমরা আগে এই পরিষ্কারটি পেতে পারি কিনা তা দেখা যাক:

VLOOKUP অনন্য শনাক্তকারীর সরবরাহিত উদাহরণের ভিত্তিতে একটি ডাটাবেস / তালিকা থেকে তথ্য পুনরুদ্ধার করে।

উপরের উদাহরণে, আপনি কোনও আইটেম কোড সহ অন্য স্প্রেডশিটে VLOOKUP ফাংশনটি সন্নিবেশ করিয়েছিলেন এবং এটি আপনার মূল বর্ণনায় বর্ণিত সম্পর্কিত আইটেমের বিবরণ, তার দাম বা তার প্রাপ্যতা (এটির "স্টক" পরিমাণ) আপনার কাছে ফিরে আসবে তালিকা। এই তথ্যগুলির মধ্যে কোনটি আপনাকে ফেরত দেবে? ঠিক আছে, আপনি সূত্রটি তৈরি করার সময় আপনি এটি সিদ্ধান্ত নিতে পারেন।

আপনার যদি প্রয়োজন কেবল ডাটাবেস থেকে এক টুকরো তথ্য, এটিতে VLOOKUP ফাংশন সহ একটি সূত্র তৈরি করতে যেতে অনেক ঝামেলা হবে। সাধারণত আপনি এই ধরণের কার্যকারিতা পুনরায় ব্যবহারযোগ্য স্প্রেডশিটে যেমন কোনও টেম্পলেট ব্যবহার করবেন। প্রতিবার কেউ যখন কোনও বৈধ আইটেম কোড প্রবেশ করে তখন সিস্টেমটি সম্পর্কিত আইটেম সম্পর্কিত সমস্ত প্রয়োজনীয় তথ্য পুনরুদ্ধার করে।

আসুন এর উদাহরণ তৈরি করুন: আন চালানের টেম্পলেট যে আমরা আমাদের কল্পিত সংস্থায় বারবার ব্যবহার করতে পারি।

প্রথমে আমরা এক্সেল শুরু করি এবং আমরা একটি ফাঁকা চালান তৈরি করি:

এটি এইভাবে কীভাবে কাজ করবে: চালানের টেম্পলেটটি ব্যবহারকারী ব্যক্তি কলাম "এ" এর আইটেম কোডগুলির একটি সিরিজ পূরণ করবে এবং সিস্টেমটি আমাদের পণ্যের ডেটাবেস থেকে প্রতিটি আইটেমের বিবরণ এবং মূল্য পুনরুদ্ধার করবে। এই তথ্যটি প্রতিটি আইটেমের জন্য মোট লাইন গণনা করতে ব্যবহার করা হবে (ধরে নেওয়া যাক আমরা একটি বৈধ পরিমাণ প্রবিষ্ট করেছি)।

এই উদাহরণটিকে সহজ রাখার উদ্দেশ্যে, আমরা একই কার্যপত্রে একটি পৃথক শীটে পণ্য ডাটাবেসটি সনাক্ত করব:

বাস্তবে, সম্ভবত এটি সম্ভবত ডেটাবেস একটি পৃথক ওয়ার্কবুকে অবস্থিত। এটি VLOOKUP ফাংশনে সামান্য পার্থক্য নিয়ে আসে, যা একই শীট, একটি অন্য শিট, বা সম্পূর্ণ আলাদা ওয়ার্কবুকে ডাটাবেসটি অবস্থিত কিনা তা সত্যই যত্ন করে না।

সুতরাং, আমরা আমাদের পণ্য ডেটাবেস তৈরি করেছি, যা দেখতে এরকম দেখাচ্ছে:

আমরা যে ভিওলুকআপ সূত্রটি লিখতে চলেছি তা পরীক্ষা করার জন্য, আমরা প্রথমে আমাদের ফাঁকা চালানের সেল এ 11 এ একটি বৈধ আইটেম কোড প্রবেশ করি:

এরপরে, আমরা সক্রিয় কক্ষে সেই কক্ষে সরিয়ে নিয়ে যাই যেখানে আমরা VLOOKUP দ্বারা ডাটাবেস থেকে প্রাপ্ত তথ্য সঞ্চয় করতে চাই। মজার বিষয় হল, এই পদক্ষেপটি বেশিরভাগ লোকেরা ভুল হয়ে যায়। আরও ব্যাখ্যা করতে: আমরা একটি VLOOKUP সূত্র তৈরি করতে চলেছি যা সেল এ 11 এর আইটেম কোডের সাথে সম্পর্কিত বর্ণনাকে পুনরুদ্ধার করবে। আমরা যখন এই বিবরণটি পাই তখন এটি কোথায় রাখি? বি 11 এ অবশ্যই। সুতরাং আমরা এখানে VLOOKUP সূত্রটি লিখি: সেল B11 তে। এখনই বি 11 সেলটি নির্বাচন করুন।

এক্সেলকে যে সমস্ত উপলভ্য ফাংশন সরবরাহ করতে হবে তার তালিকা আমাদের সনাক্ত করতে হবে, যাতে আমরা ভ্লুকআপ চয়ন করতে পারি এবং সূত্রটি সম্পূর্ণ করতে কিছু সহায়তা পেতে পারি। এটি প্রথমে ক্লিক করে পাওয়া যায় সূত্র ট্যাব এবং তারপরে ক্লিক করুন সন্নিবেশ ফাংশন:

একটি বাক্স উপস্থিত হয় যা আমাদের এক্সলে উপলব্ধ যে কোনও ফাংশন নির্বাচন করতে দেয়।

আমরা যা খুঁজছি তার সন্ধান করতে আমরা একটি অনুসন্ধান শব্দ "লুকিং" এর মতো টাইপ করতে পারি (কারণ যে ফাংশনটিতে আমরা আগ্রহী তা হ'ল একটি খুঁজে দেখো ফাংশন)। সিস্টেমটি আমাদের এক্সেল-এ সমস্ত অনুসন্ধান-সম্পর্কিত ফাংশনগুলির একটি তালিকা ফিরিয়ে দেবে। VLOOKUP তালিকার দ্বিতীয়টি। এটি ক্লিক করুন ঠিক আছে.

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

1. আপনি ডাটাবেসে কোন অনন্য পরিচয়দাতা সন্ধান করছেন?
2. ডাটাবেস কোথায়?
3. অনন্য পরিচয়দাতার সাথে সম্পর্কিত ডেটাবেস থেকে কোন অংশের তথ্য আপনি আপনার জন্য পুনরুদ্ধার করতে চান?

প্রথম তিনটি যুক্তি দেখানো হয়েছে মোটা অক্ষরে, তারা ইঙ্গিত করে যে তারা বাধ্যতামূলক আর্গুমেন্ট (VLOOKUP ফাংশন এগুলি ছাড়া অসম্পূর্ণ এবং কোনও বৈধ মান ফেরত দেবে না)। চতুর্থ যুক্তি সাহসী নয়, এর অর্থ এটি alচ্ছিক:

আমরা শীর্ষ থেকে নীচে তর্কগুলি সম্পূর্ণ করব।

আমাদের প্রথম যুক্তিটি শেষ করতে হবে দেখার মূল্য যুক্তি. অনন্য শনাক্তকারী (কোথায়।) কোথায় পাবেন তা আমাদের জানাতে ফাংশনটির প্রয়োজন পণ্য সংকেত এই ক্ষেত্রে) এটির বিবরণটি ফিরিয়ে দেওয়া উচিত। আমরা অবশ্যই পূর্বে প্রবেশ করা আইটেম কোডটি নির্বাচন করতে হবে (এ 11 এ)।

প্রথম যুক্তির ডানদিকে নির্বাচক আইকনটিতে ক্লিক করুন:

তারপরে আইটেম কোড (A11) সম্বলিত ঘরে একবার ক্লিক করুন এবং টিপুন প্রবেশ করান:

"আ 11" এর মানটি প্রথম আর্গুমেন্টে .োকানো হয়।

এখন আমাদের জন্য একটি মান লিখতে হবে টেবিল_আরে যুক্তি. অন্য কথায়, আমাদের ভিএলউকআপকে জানাতে হবে যেখানে ডাটাবেস / তালিকাটি খুঁজে পাবে। দ্বিতীয় যুক্তির পাশের নির্বাচক আইকনে ক্লিক করুন:

এখন ডাটাবেস / তালিকাটি সনাক্ত করুন এবং সম্পূর্ণ তালিকাটি নির্বাচন করুন - শিরোনামের লাইনটি অন্তর্ভুক্ত নয়। আমাদের উদাহরণস্বরূপ, ডাটাবেসটি পৃথক ওয়ার্কশিটে অবস্থিত, তাই আমরা প্রথমে সেই কার্যপত্রক ট্যাবে ক্লিক করি:

পরবর্তী আমরা শিরোনাম লাইনটি সহ না, পুরো ডাটাবেসটি নির্বাচন করি:

… এবং টিপুন প্রবেশ করান। ডাটাবেসগুলিকে উপস্থাপন করে এমন ঘরগুলির পরিসীমা (এই ক্ষেত্রে "’ প্রোডাক্ট ডেটাবেস ’! এ 2: ডি 7") আমাদের জন্য দ্বিতীয় যুক্তিতে স্বয়ংক্রিয়ভাবে প্রবেশ করে।

এখন আমাদের তৃতীয় যুক্তি প্রবেশ করতে হবে, Col_index_num। আমরা এই আর্গুমেন্টটি VLOOKUP এ নির্দিষ্ট করার জন্য ব্যবহার করি যে ডেটাবেস থেকে তথ্য টুকরো, A11 এ আমাদের আইটেম কোডের সাথে সংযুক্ত, আমরা আমাদের কাছে ফিরে যেতে চাই। এই বিশেষ উদাহরণে, আমরা আইটেমটির থাকতে চাই বর্ণনা আমাদের ফিরে। আপনি যদি ডাটাবেস ওয়ার্কশিটে সন্ধান করেন, আপনি লক্ষ্য করবেন যে "বিবরণ" কলামটি এটি দ্বিতীয় ডাটাবেসে কলাম। এর অর্থ হল যে আমাদের অবশ্যই "2" এর মান লিখতে হবে Col_index_num বাক্স:

এটি লক্ষ করা গুরুত্বপূর্ণ যে আমরা এখানে একটি "2" প্রবেশ করছি না কারণ "বিবরণ" কলামটি রয়েছে খ সেই কার্যপত্রকের উপর কলাম। যদি কলামে ডাটাবেসটি শুরু হয় কে কার্যপত্রকের, আমরা এখনও এই ক্ষেত্রে একটি "2" প্রবেশ করবো কারণ "বিবরণ" কলামটি "টেবিল_আরে" নির্দিষ্ট করার সময় আমরা নির্বাচিত ঘরগুলির সেটগুলির মধ্যে দ্বিতীয় কলাম।

শেষ অবধি, আমাদের সিদ্ধান্ত নিতে হবে চূড়ান্ত VLOOKUP আর্গুমেন্টে কোনও মান লিখতে হবে কিনা, ব্যাপ্তি_দর্শন। এই যুক্তির জন্য হয় একটি সত্য বা মিথ্যা মান, বা এটি ফাঁকা ছেড়ে দেওয়া উচিত। ডাটাবেসগুলির সাথে VLOOKUP ব্যবহার করার সময় (যেমন সময়ের 90% সত্য), এই যুক্তিতে কী রাখবেন তা সিদ্ধান্ত নেওয়ার উপায়টি নীচে নিম্নলিখিতভাবে ভাবা যেতে পারে:

ডাটাবেসের প্রথম কলামটি (স্বতন্ত্র শনাক্তকারীদের কলামে) বর্ণানুক্রমিকভাবে / সংখ্যাসূচককে আরোহণ ক্রম অনুসারে বাছাই করা হয়, তবে এর মান প্রবেশ করা সম্ভব ’s সত্য এই তর্ক মধ্যে, বা এটি ফাঁকা ছেড়ে।

ডাটাবেসের প্রথম কলামটি যদি হয় না সাজানো বা এটি সাজানো ক্রমে সাজানো, তারপরে আপনি অবশ্যই একটি মান লিখুন মিথ্যা এই তর্ক মধ্যে

যেমনটি আমাদের ডাটাবেসের প্রথম কলাম না বাছাই, আমরা প্রবেশ মিথ্যা এই যুক্তিতে:

এটাই! আমরা আমাদের প্রয়োজনীয় মানটি ফিরিয়ে দিতে VLOOKUP এর জন্য প্রয়োজনীয় সমস্ত তথ্য প্রবেশ করিয়েছি। ক্লিক করুন ঠিক আছে বোতামটি এবং লক্ষ্য করুন যে আইটেম কোড "R99245" এর সাথে সম্পর্কিত বর্ণনাটি সঠিকভাবে B11 ঘরে প্রবেশ করেছে:

আমাদের জন্য যে সূত্রটি তৈরি করা হয়েছিল তা দেখতে এইরকম:

আমরা যদি প্রবেশ করি বিভিন্ন আইএল কোডটি সেল এ 11 এ, আমরা ভিএলউউকিউপি ফাংশনটির শক্তি দেখতে শুরু করব: নতুন আইটেম কোডের সাথে মেলে বিবরণ ঘর পরিবর্তন হবে:

আমরা আইটেমটি পেতে একই ধাপে ধাপে সেট করতে পারি দাম E11 ঘরে ফিরে এসেছিল। নোট করুন যে নতুন সূত্র অবশ্যই E11 ঘরে তৈরি করা উচিত। ফলাফলটি এরকম দেখতে পাবেন:

… এবং সূত্রটি দেখতে এমন হবে:

উল্লেখ্য যে দুটি সূত্রের মধ্যে কেবলমাত্র তফাতটি তৃতীয় যুক্তি (Col_index_num) একটি "2" থেকে "3" তে পরিবর্তিত হয়েছে (কারণ আমরা ডাটাবেসে তৃতীয় কলাম থেকে ডেটা উদ্ধার করতে চাই)।

যদি আমরা এই আইটেমগুলির মধ্যে 2 টি কেনার সিদ্ধান্ত নিয়েছি তবে আমরা সেল ডি 11 ঘরে একটি "2" প্রবেশ করবো। তারপরে লাইনটি মোট পেতে আমরা ঘরে ঘরে F11 এ একটি সহজ সূত্র প্রবেশ করিয়ে দেব:

= ডি 11 * ই 1

… যা দেখতে এরকম…

চালানের টেম্পলেট সম্পূর্ণ করা হচ্ছে

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

আমাদের চালানের টেমপ্লেটটি এখনও সম্পূর্ণ হয়নি। এটি সম্পন্ন করার জন্য, আমরা নিম্নলিখিতগুলি করব:

1. আমরা সেল এ 11 থেকে নমুনা আইটেম কোড এবং সেল ডি 11 থেকে "2" সরিয়ে ফেলব। এটি আমাদের নতুন তৈরি VLOOKUP সূত্রগুলি ত্রুটি বার্তাগুলি প্রদর্শন করতে বাধ্য করবে:

   
   

   
   আমরা এর প্রতিকার করতে পারি এক্সেলের ব্যবহারিক ব্যবহারের মাধ্যমে remedy যদি () এবং ফাঁকা() ফাংশন আমরা আমাদের সূত্রটি এটি থেকে পরিবর্তন করি ... = ভিএলুকআপ (এ 11, ‘প্রোডাক্ট ডেটাবেস’! এ 2: ডি 7,2, মিথ্যা)… এই…= যদি (ISBLANK (A11), "", VLOOKUP (A11, 'পণ্য ডেটাবেস ’! এ 2: ডি 7,2, মিথ্যা)

   
   

2. চালানের আইটেমের সারিগুলির বাকী অংশগুলিতে আমরা বি 11, E11 এবং F11 সেলগুলিতে সূত্রগুলি অনুলিপি করব। মনে রাখবেন যে আমরা যদি এটি করি তবে ফলাফলযুক্ত সূত্রগুলি আর সঠিকভাবে ডাটাবেস সারণিকে উল্লেখ করবে না। আমরা ডাটাবেসের জন্য সেল রেফারেন্স পরিবর্তন করে এটি ঠিক করতে পারি পরম সেল রেফারেন্স। বিকল্পভাবে - এবং আরও ভাল - আমরা একটি তৈরি করতে পারি পরিসীমা নাম পুরো পণ্য ডেটাবেস (যেমন "পণ্য") এর জন্য এবং সেল রেফারেন্সের পরিবর্তে এই ব্যাপ্তির নামটি ব্যবহার করুন। সূত্রটি এ থেকে পরিবর্তন হবে ... = যদি (ISBLANK (A11), "", VLOOKUP (A11, 'পণ্য ডেটাবেস ’! A2: D7,2, মিথ্যা)… এই… = যদি (ISBLANK (A11), "", VLOOKUP (A11, পণ্য, 2, মিথ্যা))…এবং তারপর ইনভয়েস আইটেমের সারিগুলিতে সূত্রগুলি অনুলিপি করুন।
3. আমরা সম্ভবত আমাদের সূত্রগুলি (বা বরং) যে কক্ষগুলিকে "লক" করব আনলক করুন দ্য অন্যান্য কোষ) এবং তারপরে কার্যপত্রকটি সুরক্ষিত করুন, যাতে কেউ চালান পূরণ করতে আসে যখন আমাদের সাবধানতার সাথে নির্মিত সূত্রগুলি দুর্ঘটনাক্রমে ওভাররাইট করা না হয়।
4. আমরা ফাইল হিসাবে সংরক্ষণ করতে হবে টেমপ্লেট, যাতে এটি আমাদের সংস্থার প্রত্যেকে ব্যবহার করতে পারে

যদি আমরা অনুভব করতাম সত্যিই বুদ্ধিমান, আমরা অন্য ওয়ার্কশিটে আমাদের সমস্ত গ্রাহকের একটি ডাটাবেস তৈরি করব এবং তারপরে F6, B7 এবং B8 ঘরগুলিতে গ্রাহকের নাম এবং ঠিকানা স্বয়ংক্রিয়ভাবে পূরণ করতে গ্রাহক আইডি ব্যবহার করব cell

আপনি যদি ভিএলুকআপের সাথে অনুশীলন করতে চান বা কেবল আমাদের ফলাফল ইনভয়েস টেম্পলেট দেখতে পান তবে এটি এখান থেকে ডাউনলোড করা যায়।