উন্নত VLOOKUP উদাহরণ: বহু-মাপদণ্ড অনুসন্ধান

ফাংশনের উপর আমাদের টিউটোরিয়ালের দ্বিতীয় অংশে VPR (VLOOKUP) এক্সেলে, আমরা কয়েকটি উদাহরণ বিশ্লেষণ করব যা আপনাকে সমস্ত শক্তি নির্দেশ করতে সাহায্য করবে VPR সবচেয়ে উচ্চাভিলাষী এক্সেল কাজগুলি সমাধান করতে। উদাহরণগুলি অনুমান করে যে এই বৈশিষ্ট্যটি কীভাবে কাজ করে সে সম্পর্কে আপনার ইতিমধ্যেই একটি প্রাথমিক জ্ঞান রয়েছে৷ যদি না হয়, আপনি এই টিউটোরিয়ালের প্রথম অংশ দিয়ে শুরু করতে আগ্রহী হতে পারেন, যা সিনট্যাক্স এবং মৌলিক ব্যবহার ব্যাখ্যা করে। VPR. আচ্ছা, শুরু করা যাক.

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

একাধিক মানদণ্ড দ্বারা এক্সেলে অনুসন্ধান করুন

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

উদাহরণ 1: 2টি ভিন্ন মানদণ্ড দ্বারা অনুসন্ধান করুন৷

ধরুন আমাদের অর্ডারের একটি তালিকা আছে এবং আমরা খুঁজে পেতে চাই পণ্যের পরিমাণ (পরিমাণ), দুটি মানদণ্ডের উপর ভিত্তি করে – গ্রাহকের নাম (গ্রাহক) এবং নবাগত পণ্য (পণ্য)। বিষয়টি এই কারণে জটিল যে প্রতিটি ক্রেতা বিভিন্ন ধরণের পণ্যের অর্ডার দিয়েছেন, যা নীচের টেবিল থেকে দেখা যেতে পারে:

নিয়মিত ফাংশন VPR এই পরিস্থিতিতে কাজ করবে না কারণ এটি প্রদত্ত লুকআপ মানের সাথে মেলে এমন প্রথম মানটি ফিরিয়ে দেবে। উদাহরণস্বরূপ, আপনি যদি একটি আইটেমের পরিমাণ জানতে চান মিষ্টি'ক্রেতা দ্বারা আদেশ জেরেমি হিল, নিম্নলিখিত সূত্র লিখুন:

=VLOOKUP(B1,$A$5:$C$14,3,FALSE)

=ВПР(B1;$A$5:$C$14;3;ЛОЖЬ)

- এই সূত্রটি ফলাফল ফিরিয়ে দেবে 15পণ্যের সাথে সম্পর্কিত আপেল, কারণ এটি প্রথম মান যা মেলে।

একটি সহজ সমাধান আছে - একটি অতিরিক্ত কলাম তৈরি করুন যাতে সমস্ত পছন্দসই মানদণ্ড একত্রিত করা যায়৷ আমাদের উদাহরণে, এই কলাম গ্রাহকের নাম (গ্রাহক) এবং নবাগত পণ্য (পণ্য)। ভুলে যাবেন না যে মার্জ করা কলামটি অবশ্যই সার্চ রেঞ্জের সবচেয়ে বাম কলাম হতে হবে, যেহেতু এটি ফাংশনটি বাম কলাম VPR একটি মান খুঁজছেন যখন আপ দেখায়.

সুতরাং, আপনি টেবিলে একটি অক্জিলিয়ারী কলাম যোগ করুন এবং এর সমস্ত কক্ষে নিম্নলিখিত সূত্রটি অনুলিপি করুন: =B2 এবং C2. আপনি যদি স্ট্রিংটি আরও পঠনযোগ্য হতে চান তবে আপনি একটি স্থানের সাথে মিলিত মানগুলিকে আলাদা করতে পারেন: =B2&» «&C2. এর পরে, আপনি নিম্নলিখিত সূত্র ব্যবহার করতে পারেন:

=VLOOKUP("Jeremy Hill Sweets",$A$7:$D$18,4,FALSE)

=ВПР("Jeremy Hill Sweets";$A$7:$D$18;4;ЛОЖЬ)

or

=VLOOKUP(B1,$A$7:$D$18,4,FALSE)

=ВПР(B1;$A$7:$D$18;4;ЛОЖЬ)

সেল কোথায় B1 আর্গুমেন্টের সমন্বিত মান রয়েছে দেখার মূল্য (lookup_value) এবং 4 - যুক্তি কলাম_সূচি_সংখ্যা (কলাম_সংখ্যা), অর্থাৎ পুনরুদ্ধার করা ডেটা ধারণকারী কলামের সংখ্যা।

উদাহরণ 2: দুটি মাপদণ্ডে VLOOKUP টেবিলটি অন্য শীটে দেখা হচ্ছে

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

পূর্ববর্তী উদাহরণের মতো, আপনার সম্মিলিত মান সহ লুকআপ টেবিলে একটি সহায়ক কলামের প্রয়োজন হবে। এই কলামটি অবশ্যই অনুসন্ধান পরিসরের বাম কলাম হতে হবে৷

তাই সূত্র দিয়ে VPR এই মত হতে পারে:

=VLOOKUP(B2&" "&C2,Orders!$A&$2:$D$2,4,FALSE)

=ВПР(B2&" "&C2;Orders!$A&$2:$D$2;4;ЛОЖЬ)

এখানে, B এবং C কলামে যথাক্রমে গ্রাহকের নাম এবং পণ্যের নাম এবং লিঙ্ক রয়েছে অর্ডার!$A&$2:$D$2 অন্য শীটে দেখার জন্য একটি টেবিল সংজ্ঞায়িত করে।

সূত্রটিকে আরও পঠনযোগ্য করতে, আপনি ভিউ পরিসরকে একটি নাম দিতে পারেন এবং তারপর সূত্রটি আরও সহজ দেখাবে:

=VLOOKUP(B2&" "&C2,Orders,4,FALSE)

=ВПР(B2&" "&C2;Orders;4;ЛОЖЬ)

সূত্রটি কাজ করার জন্য, আপনি যে টেবিলটি দেখছেন তার বাঁদিকের কলামের মানগুলি অবশ্যই অনুসন্ধানের মানদণ্ডের মতো ঠিক একইভাবে একত্রিত হতে হবে। উপরের চিত্রে, আমরা u2bu2band মানগুলিকে একত্রিত করেছি তাদের মধ্যে একটি স্পেস রেখেছি, একইভাবে আপনাকে ফাংশনের প্রথম আর্গুমেন্টে করতে হবে (BXNUMX& “” & CXNUMX)।

মনে রাখবেন! ক্রিয়া VPR 255 অক্ষরের মধ্যে সীমাবদ্ধ, এটি 255 অক্ষরের বেশি লম্বা একটি মান অনুসন্ধান করতে পারে না। এটি মনে রাখবেন এবং নিশ্চিত করুন যে পছন্দসই মানটির দৈর্ঘ্য এই সীমা অতিক্রম না করে।

আমি সম্মত যে একটি সহায়ক কলাম যোগ করা সবচেয়ে মার্জিত নয় এবং সর্বদা গ্রহণযোগ্য সমাধান নয়। আপনি হেল্পার কলাম ছাড়া একই কাজ করতে পারেন, কিন্তু এর জন্য ফাংশনের সংমিশ্রণ সহ আরও জটিল সূত্রের প্রয়োজন হবে এর INDEX (INDEX) এবং ম্যাচ (আরো উদ্ভাসিত)।

আমরা VLOOKUP ব্যবহার করে ২য়, ৩য়, ইত্যাদি মান বের করি

আপনি এটি ইতিমধ্যে জানেন VPR শুধুমাত্র একটি মিলে যাওয়া মান ফেরত দিতে পারে, আরও সঠিকভাবে, প্রথমটি পাওয়া গেছে। কিন্তু যদি এই মানটি দেখা অ্যারেতে বেশ কয়েকবার পুনরাবৃত্তি করা হয় এবং আপনি তাদের 2য় বা 3য়টি বের করতে চান? তাহলে কি সব মান? সমস্যা জটিল মনে হয়, কিন্তু সমাধান বিদ্যমান!

ধরুন টেবিলের একটি কলামে গ্রাহকদের নাম (গ্রাহকের নাম) রয়েছে এবং অন্য কলামে তাদের কেনা পণ্য (পণ্য) রয়েছে। আসুন একটি প্রদত্ত গ্রাহকের দ্বারা কেনা 2য়, 3য় এবং 4র্থ আইটেমগুলি খুঁজে বের করার চেষ্টা করি৷

সবচেয়ে সহজ উপায় হল কলামের আগে একটি অক্জিলিয়ারী কলাম যোগ করা ক্রেতার নাম এবং প্রতিটি নামের পুনরাবৃত্তি নম্বর সহ গ্রাহকের নাম দিয়ে এটি পূরণ করুন, উদাহরণস্বরূপ, জন ডো ১, জন ডো ১ ইত্যাদি। আমরা ফাংশন ব্যবহার করে সংখ্যার কৌশলটি করব COUNTIF (COUNTIF), প্রদত্ত যে গ্রাহকের নাম কলাম B এ রয়েছে:

=B2&COUNTIF($B$2:B2,B2)

=B2&СЧЁТЕСЛИ($B$2:B2;B2)

এর পরে আপনি স্বাভাবিক ফাংশন ব্যবহার করতে পারেন VPRপ্রয়োজনীয় অর্ডার খুঁজে পেতে। উদাহরণ স্বরূপ:

• আবিষ্কার 2-ম গ্রাহক দ্বারা আদেশ আইটেম ড্যান ব্রাউন:

  =VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)

  =ВПР("Dan Brown2";$A$2:$C$16;3;ЛОЖЬ)

• আবিষ্কার 3-ম গ্রাহক দ্বারা আদেশ আইটেম ড্যান ব্রাউন:

  =VLOOKUP("Dan Brown3",$A$2:$C$16,3,FALSE)

  =ВПР("Dan Brown3";$A$2:$C$16;3;ЛОЖЬ)

প্রকৃতপক্ষে, আপনি পাঠ্যের পরিবর্তে লুকআপ মান হিসাবে একটি সেল রেফারেন্স প্রবেশ করতে পারেন, যেমনটি নিম্নলিখিত চিত্রে দেখানো হয়েছে:

আপনি যদি শুধুমাত্র খুঁজছেন 2-ই পুনরাবৃত্তি, আপনি আরও জটিল সূত্র তৈরি করে অক্জিলিয়ারী কলাম ছাড়া এটি করতে পারেন:

=IFERROR(VLOOKUP($F$2,INDIRECT("$B$"&(MATCH($F$2,Table4[Customer Name],0)+2)&":$C16"),2,FALSE),"")

=ЕСЛИОШИБКА(ВПР($F$2;ДВССЫЛ("$B$"&(ПОИСКПОЗ($F$2;Table4[Customer Name];0)+2)&":$C16");2;ИСТИНА);"")

এই সূত্রে:

• $F$2 – ক্রেতার নাম সম্বলিত একটি ঘর (এটি অপরিবর্তিত, অনুগ্রহ করে মনে রাখবেন – লিঙ্কটি পরম);
• $B$ - কলাম ক্রেতার নাম;
• Table4 - আপনার টেবিল (এই জায়গাটি একটি নিয়মিত পরিসরও হতে পারে);
• $C16 - আপনার টেবিল বা পরিসরের শেষ কক্ষ।

এই সূত্রটি শুধুমাত্র দ্বিতীয় মিলে যাওয়া মান খুঁজে পায়। আপনি যদি অবশিষ্ট পুনরাবৃত্তিগুলি বের করতে চান তবে পূর্ববর্তী সমাধানটি ব্যবহার করুন।

আপনি যদি সমস্ত মিলের একটি তালিকা প্রয়োজন - ফাংশন VPR এটি একটি সহায়ক নয়, যেহেতু এটি একটি সময়ে শুধুমাত্র একটি মান প্রদান করে - সময়কাল। কিন্তু এক্সেলের একটা ফাংশন আছে এর INDEX (INDEX), যা সহজেই এই কাজটি মোকাবেলা করতে পারে। এই ধরনের একটি সূত্র দেখতে কেমন হবে, আপনি নিম্নলিখিত উদাহরণে শিখবেন।

পছন্দসই মানের সমস্ত পুনরাবৃত্তি পুনরুদ্ধার করুন

উপরে উল্লিখিত VPR স্ক্যান করা পরিসীমা থেকে সমস্ত ডুপ্লিকেট মান বের করতে পারে না। এটি করার জন্য, আপনার একটি সামান্য জটিল সূত্র প্রয়োজন, যা বেশ কয়েকটি এক্সেল ফাংশন দ্বারা গঠিত, যেমন এর INDEX (INDEX), ক্ষুদ্র (ছোট) এবং সারিটি (লাইন)

উদাহরণস্বরূপ, নীচের সূত্রটি B2:B2 পরিসরে সেল F16 থেকে মানের সমস্ত পুনরাবৃত্তি খুঁজে পায় এবং C কলামে একই সারি থেকে ফলাফল প্রদান করে।

{=IFERROR(INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3)),"")}

{=ЕСЛИОШИБКА(ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3));"")}

একাধিক সংলগ্ন কোষে এই অ্যারে সূত্রটি প্রবেশ করান, যেমন কোষ F4: F8নিচের চিত্রে দেখানো হয়েছে। কক্ষের সংখ্যা অবশ্যই অনুসন্ধান করা মানের পুনরাবৃত্তির সর্বাধিক সম্ভাব্য সংখ্যার সমান বা তার বেশি হতে হবে। ক্লিক করতে ভুলবেন না Ctrl + Shift + Enterঅ্যারে সূত্র সঠিকভাবে প্রবেশ করতে।

আপনি যদি এটি কীভাবে কাজ করে তা বুঝতে আগ্রহী হন, আসুন সূত্রটির বিশদ বিবরণে একটু ডুব দেওয়া যাক:

পার্ট 1:

IF($F$2=B2:B16,ROW(C2:C16)-1,"")

ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"")

$F$2=B2:B16 - B2:B2 রেঞ্জের প্রতিটি মানের সাথে সেল F16-এর মান তুলনা করুন। যদি একটি মিল পাওয়া যায়, তাহলে অভিব্যক্তি STRING(C2:C16)-1 সংশ্লিষ্ট লাইনের সংখ্যা প্রদান করে (মান -1 আপনাকে হেডার লাইন অন্তর্ভুক্ত না করার অনুমতি দেয়)। কোন মিল না থাকলে, ফাংশন IF (IF) একটি খালি স্ট্রিং প্রদান করে।

ফাংশন ফলাফল IF (IF) এই ধরনের একটি অনুভূমিক অ্যারে থাকবে: {1,"",3,"",5,"","","","","","",12,"","",""}

পার্ট 2:

ROW()-3

СТРОКА()-3

এখানে ফাংশন সারিটি (লাইন) একটি অতিরিক্ত কাউন্টার হিসাবে কাজ করে। যেহেতু সূত্রটি F4:F9 কোষে অনুলিপি করা হয়েছে, আমরা সংখ্যাটি বিয়োগ করি 3 ফাংশন ফলাফল থেকে মান পেতে 1 কোষে F4 (লাইন 4, বিয়োগ 3) পেতে 2 কোষে F5 (লাইন 5, বিয়োগ 3) এবং তাই।

পার্ট 3:

SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))

НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))

ক্রিয়া ক্ষুদ্র (ছোট) রিটার্ন n-ওহ ডেটা অ্যারের মধ্যে সবচেয়ে ছোট মান। আমাদের ক্ষেত্রে, কোন অবস্থানটি (সবচেয়ে ছোট থেকে) ফিরতে হবে তা ফাংশন দ্বারা নির্ধারিত হয় সারিটি (লাইন) (পার্ট 2 দেখুন)। সুতরাং, একটি সেল জন্য F4 ক্রিয়া SMALL({অ্যারে},1) আয় 1-ম (সবচেয়ে ছোট) অ্যারে উপাদান, যেমন 1. সেলের জন্য F5 আয় 2-ম অ্যারের ক্ষুদ্রতম উপাদান, যে 3ইত্যাদি

পার্ট 4:

INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))

ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))

ক্রিয়া এর INDEX (INDEX) সহজভাবে একটি অ্যারের একটি নির্দিষ্ট ঘরের মান প্রদান করে সি 2: সি 16. সেলের জন্য F4 ক্রিয়া ইনডেক্স($C$2:$C$16) ফিরে আসবে আপেলজন্য F5 ক্রিয়া ইনডেক্স($C$2:$C$16) ফিরে আসবে মিষ্টি' এবং তাই.

পার্ট 5:

IFERROR()

ЕСЛИОШИБКА()

অবশেষে, আমরা ফাংশনের ভিতরে সূত্রটি রাখি IFERROR (IFERROR), কারণ আপনি ত্রুটি বার্তার সাথে সন্তুষ্ট হওয়ার সম্ভাবনা কম #এটি (#N/A) যদি সূত্রটি কপি করা হয় এমন কক্ষের সংখ্যা দেখা পরিসরে সদৃশ মানের সংখ্যার চেয়ে কম হয়।

পরিচিত সারি এবং কলাম দ্বারা XNUMXD অনুসন্ধান

এক্সেলে একটি XNUMXD অনুসন্ধান সম্পাদনের সাথে একটি পরিচিত সারি এবং কলাম নম্বর দ্বারা একটি মান অনুসন্ধান করা জড়িত৷ অন্য কথায়, আপনি একটি নির্দিষ্ট সারি এবং কলামের সংযোগস্থলে ঘরের মান বের করছেন।

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

XNUMXD অনুসন্ধান করার বিভিন্ন উপায় রয়েছে। বিকল্পগুলি দেখুন এবং আপনার জন্য সবচেয়ে উপযুক্ত একটি চয়ন করুন৷

VLOOKUP এবং MATCH ফাংশন

আপনি ফাংশন একটি গুচ্ছ ব্যবহার করতে পারেন VPR (VLOOKUP) এবং আরও উন্মুক্ত (MATCH) ক্ষেত্রের সংযোগস্থলে মান খুঁজে বের করতে নবাগত পণ্য (স্ট্রিং) এবং মাস প্রশ্নে থাকা অ্যারের (কলাম)

=VLOOKUP("Lemons",$A$2:$I$9,MATCH("Mar",$A$1:$I$1,0),FALSE)

=ВПР("Lemons";$A$2:$I$9;ПОИСКПОЗ("Mar";$A$1:$I$1;0);ЛОЖЬ)

উপরের সূত্রটি একটি নিয়মিত ফাংশন VPR, যা A2 থেকে A9 কক্ষে "লেমন" মানের একটি সঠিক মিল খোঁজে। কিন্তু যেহেতু আপনি জানেন না যে মার্চের বিক্রয় কোন কলামে আছে, আপনি তৃতীয় ফাংশন আর্গুমেন্টের জন্য কলাম নম্বর সেট করতে পারবেন না। VPR. পরিবর্তে, ফাংশন ব্যবহার করা হয় আরও উন্মুক্তএই কলাম সংজ্ঞায়িত করতে.

MATCH("Mar",$A$1:$I$1,0)

ПОИСКПОЗ("Mar";$A$1:$I$1;0)

মানুষের ভাষায় অনুবাদ করা এই সূত্রটির অর্থ হল:

• আমরা অক্ষর খুঁজছি “মার” – যুক্তি দেখার মূল্য (দেখার মূল্য);
• A1 থেকে I1 পর্যন্ত ঘরের মধ্যে খুঁজছেন – যুক্তি lookup_array (lookup_array);
• প্রত্যাবর্তন সঠিক মিল – যুক্তি ম্যাচ_টাইপ (match_type)।

ব্যবহার 0 তৃতীয় যুক্তিতে, আপনি ফাংশন বলেন আরও উন্মুক্ত প্রথম মানটি দেখুন যা আপনি যে মানটি খুঁজছেন তার সাথে ঠিক মেলে। এটি মান সমতুল্য মিথ্যা (FALSE) চতুর্থ যুক্তির জন্য VPR.

এইভাবে আপনি Excel এ একটি দ্বি-মুখী অনুসন্ধান সূত্র তৈরি করতে পারেন, যা দ্বি-মাত্রিক অনুসন্ধান বা দ্বিমুখী অনুসন্ধান নামেও পরিচিত।

SUMPRODUCT ফাংশন

ক্রিয়া SUMPRODUCT (SUMPRODUCT) নির্বাচিত অ্যারের পণ্যগুলির যোগফল প্রদান করে:

=SUMPRODUCT(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar"),$A$2:$I$9)

=СУММПРОИЗВ(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar");$A$2:$I$9)

INDEX এবং MATCH ফাংশন

পরবর্তী নিবন্ধে আমি এই ফাংশনগুলি বিস্তারিতভাবে ব্যাখ্যা করব, তাই আপাতত আপনি এই সূত্রটি অনুলিপি করতে পারেন:

=INDEX($A$2:$I$9,MATCH("Lemons",$A$2:$A$9,0),MATCH("Mar",$A$1:$I$1,0))

=ИНДЕКС($A$2:$I$9;ПОИСКПОЗ("Lemons";$A$2:$A$9;0);ПОИСКПОЗ("Mar";$A$1:$I$1;0))

নামকৃত রেঞ্জ এবং ইন্টারসেকশন অপারেটর

আপনি যদি সেই সমস্ত জটিল এক্সেল সূত্রগুলিতে না থাকেন তবে আপনি এই দৃশ্য এবং স্মরণীয় উপায়টি পছন্দ করতে পারেন:

1. টেবিলটি নির্বাচন করুন, ট্যাবটি খুলুন সূত্র (সূত্র) এবং ক্লিক করুন নির্বাচন থেকে তৈরি করুন (নির্বাচন থেকে তৈরি করুন)।
2. বাক্সগুলি পরীক্ষা করুন উপরের সারি (উপরের লাইনে) এবং বাম কলাম (বাম দিকের কলামে)। মাইক্রোসফ্ট এক্সেল আপনার স্প্রেডশীটের উপরের সারি এবং বাম কলামের মানগুলি থেকে রেঞ্জগুলিতে নাম নির্ধারণ করবে। এখন আপনি সূত্র তৈরি না করে সরাসরি এই নামগুলি ব্যবহার করে অনুসন্ধান করতে পারেন।
3. যে কোনো খালি ঘরে লিখুন =সারি_নাম কলাম_নাম, উদাহরণস্বরূপ এই মত:

   =লেবু মার

   … অথবা উলটা:

   = মার লেবু

   মনে রাখবেন যে সারি এবং কলামের নামগুলি অবশ্যই একটি স্থান দ্বারা পৃথক করা উচিত, যা এই ক্ষেত্রে ছেদ অপারেটরের মতো কাজ করে।

আপনি যখন একটি নাম লিখবেন, তখন Microsoft Excel মিলিত নামের তালিকা সহ একটি টুলটিপ দেখাবে, ঠিক যেমন আপনি একটি সূত্র লিখবেন।

4. প্রেস প্রবেশ করান এবং ফলাফল পরীক্ষা করুন

সাধারণভাবে, উপরের পদ্ধতিগুলির মধ্যে যেটি আপনি বেছে নিন, দ্বি-মাত্রিক অনুসন্ধানের ফলাফল একই হবে:

এক সূত্রে একাধিক VLOOKUP ব্যবহার করা

এটি ঘটে যে প্রধান টেবিল এবং লুকআপ টেবিলে একটি একক কলাম মিল নেই এবং এটি আপনাকে স্বাভাবিক ফাংশন ব্যবহার করতে বাধা দেয় VPR. যাইহোক, আরেকটি টেবিল আছে যেটিতে আমাদের আগ্রহের তথ্য নেই, তবে মূল টেবিল এবং লুকআপ টেবিলের সাথে একটি সাধারণ কলাম রয়েছে।

চলুন নিচের উদাহরণটি দেখে নেওয়া যাক। আমাদের একটি কলাম সহ একটি প্রধান টেবিল রয়েছে SKU (নতুন), যেখানে আপনি অন্য টেবিল থেকে সংশ্লিষ্ট দামের সাথে একটি কলাম যোগ করতে চান। উপরন্তু, আমাদের 2 টি লুকআপ টেবিল আছে। প্রথমটিতে (লুকআপ টেবিল 1) আপডেট করা নম্বর রয়েছে SKU (নতুন) এবং পণ্যের নাম, এবং দ্বিতীয়টি (লুকআপ টেবিল 2) – পণ্যের নাম এবং পুরানো সংখ্যা SKU (পুরানো).

দ্বিতীয় লুকআপ টেবিল থেকে মূল টেবিলে দাম যোগ করতে, আপনাকে অবশ্যই ডবল নামে পরিচিত একটি ক্রিয়া সম্পাদন করতে হবে VPR বা বাসা বাঁধে VPR.

1. একটি ফাংশন লিখুন VPR, যা টেবিলে পণ্যের নাম খুঁজে পায় সন্ধান টেবিল 1ব্যবহার SKU, পছন্দসই মান হিসাবে:

   =VLOOKUP(A2,New_SKU,2,FALSE)

   =ВПР(A2;New_SKU;2;ЛОЖЬ)

   এখানে নতুন_SKU - নাম করা পরিসীমা $A:$B টেবিলের সন্ধান টেবিল 1, 2 - এটি কলাম B, যাতে পণ্যগুলির নাম রয়েছে (উপরের ছবিটি দেখুন)

2. একটি টেবিল থেকে দাম সন্নিবেশ করার জন্য একটি সূত্র লিখুন সন্ধান টেবিল 2 সুপরিচিত পণ্য নামের উপর ভিত্তি করে. এটি করার জন্য, নতুন ফাংশনের জন্য লুকআপ মান হিসাবে আপনি আগে তৈরি করা সূত্রটি পেস্ট করুন VPR:

   =VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)

   =ВПР(ВПР(A2;New_SKU;2;ЛОЖЬ);Price;3;ЛОЖЬ)

   এখানে মূল্য - নাম করা পরিসীমা $A:$C টেবিলের সন্ধান টেবিল 2, 3 কলাম C হল দাম রয়েছে।

নীচের চিত্রটি আমাদের তৈরি করা সূত্র দ্বারা প্রত্যাবর্তিত ফলাফল দেখায়:

VLOOKUP এবং INDIRECT ব্যবহার করে বিভিন্ন টেবিল থেকে ডেটার গতিশীল প্রতিস্থাপন

প্রথমে, আসুন আমরা একে অপরকে সঠিকভাবে বুঝতে পারি তা নিশ্চিত করতে "বিভিন্ন টেবিল থেকে ডেটার গতিশীল প্রতিস্থাপন" অভিব্যক্তি দ্বারা আমরা কী বুঝি তা স্পষ্ট করি।

এমন পরিস্থিতি রয়েছে যখন একই বিন্যাসের ডেটা সহ বেশ কয়েকটি শীট রয়েছে এবং একটি নির্দিষ্ট পত্রক থেকে প্রয়োজনীয় তথ্য বের করা প্রয়োজন, একটি প্রদত্ত কক্ষে প্রবেশ করা মানের উপর নির্ভর করে। আমি মনে করি এটি একটি উদাহরণ দিয়ে ব্যাখ্যা করা সহজ।

কল্পনা করুন যে আপনার কাছে একই পণ্য এবং একই বিন্যাসে বিভিন্ন অঞ্চলের বিক্রয় প্রতিবেদন রয়েছে। আপনি একটি নির্দিষ্ট অঞ্চলের জন্য বিক্রয় পরিসংখ্যান খুঁজে পেতে চান:

আপনার যদি শুধুমাত্র দুটি এই ধরনের রিপোর্ট থাকে, তাহলে আপনি ফাংশন সহ একটি অসম্মানজনক সহজ সূত্র ব্যবহার করতে পারেন VPR и IF (IF) অনুসন্ধান করতে পছন্দসই প্রতিবেদন নির্বাচন করতে:

=VLOOKUP($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)

=ВПР($D$2;ЕСЛИ($D3="FL";FL_Sales;CA_Sales);2;ЛОЖЬ)

যেখানে:

• $ ডি $ 2 পণ্যের নাম ধারণকারী একটি সেল। মনে রাখবেন যে আমরা অন্য কক্ষে সূত্র অনুলিপি করার সময় লুকআপ মান পরিবর্তন এড়াতে এখানে পরম রেফারেন্স ব্যবহার করি।
• $D3 অঞ্চলের নাম সহ একটি কোষ। আমরা একটি পরম কলাম রেফারেন্স এবং একটি আপেক্ষিক সারি রেফারেন্স ব্যবহার করছি কারণ আমরা একই কলামের অন্যান্য কক্ষে সূত্রটি অনুলিপি করার পরিকল্পনা করছি।
• FL_Sales и CA_সেলস - সারণির নাম (বা নামকৃত রেঞ্জ) যাতে সংশ্লিষ্ট বিক্রয় প্রতিবেদন থাকে। আপনি, অবশ্যই, সাধারণ শীটের নাম এবং সেল পরিসরের রেফারেন্স ব্যবহার করতে পারেন, উদাহরণস্বরূপ 'FL শিট'!$A$3:$B$10, কিন্তু নামকৃত রেঞ্জ অনেক বেশি সুবিধাজনক।

যাইহোক, যখন এই ধরনের অনেক টেবিল আছে, ফাংশন IF সেরা সমাধান নয়। পরিবর্তে, আপনি ফাংশন ব্যবহার করতে পারেন পরোক্ষ (পরোক্ষ) পছন্দসই অনুসন্ধান পরিসর ফেরাতে।

আপনি সম্ভবত জানেন, ফাংশন পরোক্ষ একটি টেক্সট স্ট্রিং দ্বারা প্রদত্ত একটি লিঙ্ক ফেরত দিতে ব্যবহৃত হয়, যা আমাদের এখন প্রয়োজন। সুতরাং, সাহসের সাথে উপরের সূত্রে এক্সপ্রেশনটিকে ফাংশনের সাথে প্রতিস্থাপন করুন IF ফাংশনের সাথে লিঙ্ক করতে পরোক্ষ. এখানে একটি সমন্বয় VPR и পরোক্ষ এর সাথে দুর্দান্ত কাজ করে:

=VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)

=ВПР($D$2;ДВССЫЛ($D3&"_Sales");2;ЛОЖЬ)

যেখানে:

• $ ডি $ 2 - এটি পণ্যের নামের একটি ঘর, এটি পরম লিঙ্কের কারণে অপরিবর্তিত।
• $D3 অঞ্চলের নামের প্রথম অংশ ধারণকারী কোষ। আমাদের উদাহরণে, এই FL.
• _বিক্রয় - সমস্ত নামকৃত রেঞ্জ বা টেবিলের নামের সাধারণ অংশ। D3 কক্ষে মানের সাথে মিলিত হলে, এটি প্রয়োজনীয় পরিসরের সম্পূর্ণ যোগ্য নাম গঠন করে। যারা ফাংশনে নতুন তাদের জন্য নিচে কিছু বিবরণ দেওয়া হল পরোক্ষ.

কিভাবে INDIRECT এবং VLOOKUP কাজ করে

প্রথমে, আমাকে আপনাকে ফাংশনের সিনট্যাক্স মনে করিয়ে দিতে দিন পরোক্ষ (পরোক্ষ):

INDIRECT(ref_text,[a1])

ДВССЫЛ(ссылка_на_текст;[a1])

প্রথম আর্গুমেন্ট হতে পারে একটি সেল রেফারেন্স (A1 বা R1C1 শৈলী), একটি পরিসরের নাম, বা একটি পাঠ্য স্ট্রিং। দ্বিতীয় যুক্তি নির্ধারণ করে প্রথম আর্গুমেন্টে লিঙ্কের কোন স্টাইল রয়েছে:

• A1যদি যুক্তি হয় সত্য কোড (সত্য) বা নির্দিষ্ট নয়;
• R1C1, যদি Fএএস ই (মিথ্যা)।

আমাদের ক্ষেত্রে, লিঙ্কের শৈলী আছে A1, তাই আপনি দ্বিতীয় যুক্তিটি ছেড়ে দিতে পারেন এবং প্রথমটিতে ফোকাস করতে পারেন।

তাই আসুন আমাদের বিক্রয় প্রতিবেদনে ফিরে যাই। আপনি যদি মনে রাখবেন, তাহলে প্রতিটি রিপোর্ট একটি পৃথক শীটে অবস্থিত একটি পৃথক টেবিল। সূত্রটি সঠিকভাবে কাজ করার জন্য, আপনাকে অবশ্যই আপনার টেবিলের (বা রেঞ্জ) নাম দিতে হবে এবং সমস্ত নামের একটি সাধারণ অংশ থাকতে হবে। উদাহরণস্বরূপ, এই মত: CA_সেলস, FL_বিক্রয়, TX_ বিক্রয় এবং তাই আপনি দেখতে পাচ্ছেন, "_বিক্রয়" সমস্ত নামে উপস্থিত রয়েছে৷

ক্রিয়া পরোক্ষ কলাম D এবং টেক্সট স্ট্রিং "_Sales"-এর মানকে সংযুক্ত করে, যার ফলে বলা হয় VPR কোন টেবিলে অনুসন্ধান করতে হবে। যদি ঘর D3-এ "FL" মান থাকে, তাহলে সূত্রটি টেবিলটি অনুসন্ধান করবে FL_বিক্রয়, যদি "CA" - টেবিলে CA_সেলস এবং তাই.

ফাংশন ফলাফল VPR и পরোক্ষ নিম্নলিখিত হবে:

যদি ডেটা বিভিন্ন এক্সেল বইতে থাকে, তাহলে আপনাকে নামযুক্ত পরিসরের আগে বইটির নাম যোগ করতে হবে, উদাহরণস্বরূপ:

=VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)

=ВПР($D$2;ДВССЫЛ($D3&"Workbook1!_Sales");2;ЛОЖЬ)

যদি ফাংশন পরোক্ষ অন্য ওয়ার্কবুক বোঝায়, সেই ওয়ার্কবুকটি অবশ্যই খোলা থাকতে হবে। এটি বন্ধ হলে, ফাংশন একটি ত্রুটি রিপোর্ট করবে। #REF! (#SSYL!)