একটি টেবিলে ডেটা খুঁজে বের করে অন্যটিতে বের করার জন্য এক্সেল VLOOKUP ফাংশনের সমস্ত গোপনীয়তা

2024 লেখক: Malcolm Clapton | [email protected]. সর্বশেষ পরিবর্তিত: 2023-12-17 03:48

নিবন্ধটি পড়ার পরে, আপনি কীভাবে একটি এক্সেল স্প্রেডশীটে ডেটা খুঁজে পেতে এবং এটিকে অন্যটিতে বের করতে হবে তা শিখবেন না, তবে VLOOKUP ফাংশনের সাথে একত্রে ব্যবহার করা যেতে পারে এমন কৌশলগুলিও শিখবেন৷

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

এমনকি আপনি যদি বছরের পর বছর ধরে VLOOKUP ফাংশনটি ব্যবহার করে থাকেন, তবে উচ্চ মাত্রার সম্ভাবনার সাথে এই নিবন্ধটি আপনার পক্ষে কার্যকর হবে এবং আপনাকে উদাসীন রাখবে না। উদাহরণস্বরূপ, একজন আইটি বিশেষজ্ঞ, এবং তারপরে আইটি-তে একজন প্রধান, আমি 15 বছর ধরে VLOOKUP ব্যবহার করছি, কিন্তু আমি কেবলমাত্র এখন সমস্ত সূক্ষ্মতা মোকাবেলা করতে পেরেছি, যখন আমি পেশাদার ভিত্তিতে লোকেদের এক্সেল শেখাতে শুরু করি।

VLOOKUP এর জন্য একটি সংক্ষিপ্ত রূপ v উল্লম্ব এনএস পরিদর্শন একইভাবে, VLOOKUP - উল্লম্ব লুকআপ। ফাংশনের নামটি আমাদের ইঙ্গিত দেয় যে এটি টেবিলের সারিগুলিতে অনুসন্ধান করে (উল্লম্বভাবে - সারির উপর পুনরাবৃত্তি করা এবং কলাম ঠিক করা), এবং কলামগুলিতে নয় (অনুভূমিকভাবে - কলামগুলির উপর পুনরাবৃত্তি করা এবং সারি ঠিক করা)। এটি লক্ষ করা উচিত যে VLOOKUP-এর একটি বোন রয়েছে - একটি কুৎসিত হাঁসের বাচ্চা যে কখনই রাজহাঁস হবে না - এটি হল HLOOKUP ফাংশন। HLOOKUP, VLOOKUP এর বিপরীতে, অনুভূমিক অনুসন্ধানগুলি সম্পাদন করে, কিন্তু Excel এর ধারণা (এবং প্রকৃতপক্ষে ডেটা সংস্থার ধারণা) বোঝায় যে আপনার টেবিলে কম কলাম এবং আরও অনেক সারি রয়েছে। এজন্য আমাদের কলামের চেয়ে অনেকবার সারি দ্বারা অনুসন্ধান করতে হবে। আপনি যদি এক্সেলে প্রায়শই HLO ফাংশন ব্যবহার করেন, তাহলে সম্ভবত আপনি এই জীবনে কিছু বুঝতে পারেননি।

বাক্য গঠন

VLOOKUP ফাংশনের চারটি পরামিতি রয়েছে:

= VLOOKUP (;; [;]), এখানে:

- পছন্দসই মান (কদাচিৎ) বা পছন্দসই মান ধারণকারী একটি ঘরের একটি রেফারেন্স (বেশিরভাগ ক্ষেত্রে);

- প্রথম (!) কলামে যে প্যারামিটারের মানটি অনুসন্ধান করা হবে তার একটি পরিসরের (দ্বি-মাত্রিক অ্যারে) রেফারেন্স;

- যে পরিসর থেকে মানটি ফেরত দেওয়া হবে তার কলাম নম্বর;

- এটি একটি অত্যন্ত গুরুত্বপূর্ণ প্যারামিটার যা এই প্রশ্নের উত্তর দেয় যে পরিসরের প্রথম কলামটি আরোহী ক্রমে সাজানো হয়েছে কিনা। যদি অ্যারে বাছাই করা হয়, আমরা TRUE বা 1 মানটি নির্দিষ্ট করি, অন্যথায় - FALSE বা 0। যদি এই প্যারামিটারটি বাদ দেওয়া হয়, এটি ডিফল্ট 1-এ থাকে।

আমি বাজি ধরে বলতে পারি যে যারা VLOOKUP ফাংশনটিকে ফ্ল্যাকি হিসাবে জানেন তাদের মধ্যে অনেকেই চতুর্থ প্যারামিটারের বর্ণনা পড়ার পরে অস্বস্তি বোধ করতে পারেন, যেহেতু তারা এটিকে কিছুটা ভিন্ন আকারে দেখতে অভ্যস্ত: সাধারণত তারা একটি সঠিক মিল সম্পর্কে কথা বলে যখন অনুসন্ধান (FALSE বা 0) অথবা একটি পরিসর স্ক্যান (TRUE বা 1)।

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

VLOOKUP সূত্র ঠিক কিভাবে কাজ করে?

• সূত্র প্রকার I. যদি শেষ প্যারামিটারটি বাদ দেওয়া হয় বা 1 এর সমান নির্দিষ্ট করা হয়, তাহলে VLOOKUP অনুমান করে যে প্রথম কলামটি আরোহী ক্রমে সাজানো হয়েছে, তাই অনুসন্ধানটি সারিতে থেমে যায় অবিলম্বে পছন্দসই একটি থেকে বড় মান ধারণকারী লাইনের আগে … যদি এই ধরনের কোনো স্ট্রিং পাওয়া না যায়, তাহলে পরিসরের শেষ সারিটি ফেরত দেওয়া হয়।

  

• সূত্র II। যদি শেষ প্যারামিটারটি 0 হিসাবে নির্দিষ্ট করা হয়, তাহলে VLOOKUP অ্যারের প্রথম কলামটি ক্রমানুসারে স্ক্যান করে এবং প্যারামিটারের সাথে প্রথম সঠিক মিল পাওয়া গেলে অবিলম্বে অনুসন্ধান বন্ধ করে দেয়, অন্যথায় # N / A (# N / A) ত্রুটি কোড ফেরত দেওয়া হয়।

  

সূত্র কর্মপ্রবাহ

ভিপিআর টাইপ আই

VPR প্রকার II

ফর্ম I এর সূত্রগুলির জন্য সমষ্টি

1. রেঞ্জ জুড়ে মান বিতরণ করতে সূত্র ব্যবহার করা যেতে পারে।
2. যদি প্রথম কলামে ডুপ্লিকেট মান থাকে এবং সঠিকভাবে সাজানো হয়, তাহলে ডুপ্লিকেট মান সহ সারিগুলির শেষটি ফেরত দেওয়া হবে।
3. আপনি যদি এমন একটি মান অনুসন্ধান করেন যা স্পষ্টতই প্রথম কলামে থাকতে পারে তার চেয়ে বেশি, তাহলে আপনি সহজেই টেবিলের শেষ সারিটি খুঁজে পেতে পারেন, যা বেশ মূল্যবান হতে পারে।
4. এই ভিউটি শুধুমাত্র # N/A ত্রুটি ফিরিয়ে দেবে যদি এটি পছন্দসই একটির চেয়ে কম বা সমান একটি মান খুঁজে না পায়।
5. আপনার অ্যারে সাজানো না থাকলে সূত্রটি ভুল মান প্রদান করে তা বোঝা কঠিন।

টাইপ II-এর সূত্রগুলির জন্য সমষ্টি

যদি অ্যারের প্রথম কলামে পছন্দসই মান একাধিকবার ঘটে, তাহলে সূত্রটি পরবর্তী ডেটা পুনরুদ্ধারের জন্য প্রথম সারিটি নির্বাচন করবে।

VLOOKUP কর্মক্ষমতা

আপনি নিবন্ধের ক্লাইম্যাক্সে পৌঁছেছেন। এটা মনে হবে, ভাল, আমি যদি শেষ পরামিতি হিসাবে শূন্য বা এক উল্লেখ করি তবে পার্থক্য কি? মূলত, সবাই ইঙ্গিত করে, অবশ্যই, শূন্য, যেহেতু এটি বেশ ব্যবহারিক: আপনাকে অ্যারের প্রথম কলাম সাজানোর বিষয়ে চিন্তা করার দরকার নেই, আপনি অবিলম্বে দেখতে পাবেন যে মানটি পাওয়া গেছে কি না। কিন্তু যদি আপনার শীটে কয়েক হাজার VLOOKUP সূত্র থাকে, আপনি লক্ষ্য করবেন যে VLOOKUP II ধীর। একই সময়ে, সাধারণত সবাই ভাবতে শুরু করে:

• আমার আরও শক্তিশালী কম্পিউটার দরকার;
• আমার একটি দ্রুত সূত্র দরকার, উদাহরণস্বরূপ, অনেকেই INDEX + MATCH সম্পর্কে জানেন, যা অনুমিতভাবে 5-10% দ্রুততর।

এবং খুব কম লোকই মনে করে যে আপনি টাইপ I-এর VLOOKUP ব্যবহার করা শুরু করার সাথে সাথে এবং নিশ্চিত করবেন যে প্রথম কলামটি যে কোনও উপায়ে সাজানো হয়েছে, VLOOKUP-এর গতি 57 গুণ বেড়ে যাবে। কথায় কথায় লিখছি- পঞ্চাশ-সাতবার! 57% নয়, 5,700%। আমি এই সত্যটি বেশ নির্ভরযোগ্যভাবে পরীক্ষা করেছি।

এই ধরনের দ্রুত কাজের রহস্য নিহিত যে একটি অত্যন্ত দক্ষ অনুসন্ধান অ্যালগরিদম একটি সাজানো অ্যারেতে প্রয়োগ করা যেতে পারে, যাকে বলা হয় বাইনারি অনুসন্ধান (অর্ধেক পদ্ধতি, দ্বিমুখী পদ্ধতি)। তাই টাইপ I-এর VLOOKUP এটি প্রয়োগ করে, এবং টাইপ II-এর VLOOKUP কোনো অপ্টিমাইজেশন ছাড়াই অনুসন্ধান করে। MATCH ফাংশনের ক্ষেত্রেও একই কথা প্রযোজ্য, যার মধ্যে একটি অনুরূপ পরামিতি রয়েছে এবং LOOKUP ফাংশন, যা শুধুমাত্র সাজানো অ্যারেতে কাজ করে এবং লোটাস 1-2-3-এর সাথে সামঞ্জস্যের জন্য Excel-এ অন্তর্ভুক্ত।

সূত্রের অসুবিধা

VLOOKUP-এর অসুবিধাগুলি সুস্পষ্ট: প্রথমত, এটি শুধুমাত্র নির্দিষ্ট অ্যারের প্রথম কলামে অনুসন্ধান করে এবং দ্বিতীয়ত, শুধুমাত্র এই কলামের ডানদিকে। এবং আপনি যেমন বুঝতে পেরেছেন, এটি ভালভাবে ঘটতে পারে যে প্রয়োজনীয় তথ্য সম্বলিত কলামটি কলামটির বাম দিকে থাকবে যেখানে আমরা খুঁজব। INDEX + MATCH সূত্রগুলির ইতিমধ্যে উল্লিখিত সংমিশ্রণটি এই ত্রুটি থেকে মুক্ত, যা এটিকে VLOOKUP (VLOOKUP) এর তুলনায় টেবিল থেকে ডেটা বের করার জন্য সবচেয়ে নমনীয় সমাধান করে তোলে।

বাস্তব জীবনে সূত্র প্রয়োগের কিছু দিক

পরিসর অনুসন্ধান

একটি পরিসীমা অনুসন্ধানের একটি ক্লাসিক চিত্র হল অর্ডারের আকার দ্বারা একটি ডিসকাউন্ট নির্ধারণের কাজ৷

টেক্সট স্ট্রিং জন্য অনুসন্ধান

অবশ্যই, VLOOKUP শুধুমাত্র সংখ্যার জন্য নয়, পাঠ্যের জন্যও দেখায়। এটি মনে রাখা উচিত যে সূত্রটি অক্ষরের ক্ষেত্রে পার্থক্য করে না। আপনি ওয়াইল্ডকার্ড ব্যবহার করলে, আপনি একটি অস্পষ্ট অনুসন্ধান সংগঠিত করতে পারেন। দুটি ওয়াইল্ডকার্ড আছে: "?" - একটি টেক্সট স্ট্রিং-এ যেকোনো একটি অক্ষর প্রতিস্থাপন করে, "*" - যেকোনো অক্ষরের যেকোনো সংখ্যা প্রতিস্থাপন করে।

ফাইটিং স্পেস

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

বিভিন্ন তথ্য বিন্যাস

যদি VLOOKUP ফাংশনের প্রথম প্যারামিটারটি এমন একটি ঘরকে নির্দেশ করে যেখানে একটি সংখ্যা রয়েছে, কিন্তু যা পাঠ্য হিসাবে সেলে সংরক্ষিত থাকে এবং অ্যারের প্রথম কলামে সঠিক বিন্যাসে নম্বর থাকে, তাহলে অনুসন্ধান ব্যর্থ হবে৷ বিপরীত পরিস্থিতিও সম্ভব। প্রয়োজনীয় বিন্যাসে প্যারামিটার 1 অনুবাদ করে সমস্যাটি সহজেই সমাধান করা যেতে পারে:

= VLOOKUP (−− D7; পণ্য! $ A $ 2: $ C $ 5; 3; 0) - যদি D7 টেক্সট থাকে এবং টেবিলে সংখ্যা থাকে;

= VLOOKUP (D7 & ""); পণ্য! $ A $ 2: $ C $ 5; 3; 0) - এবং তদ্বিপরীত।

যাইহোক, আপনি একবারে একাধিক উপায়ে একটি সংখ্যায় পাঠ্য অনুবাদ করতে পারেন, চয়ন করুন:

• দ্বিগুণ অস্বীকার -D7.
• একটি D7 * 1 দ্বারা গুণ।
• শূন্য যোগ D7 + 0।
• প্রথম শক্তি D7^1 এ উত্থাপন করা হচ্ছে।

একটি সংখ্যাকে টেক্সটে রূপান্তর করা হয় একটি খালি স্ট্রিং দিয়ে সংযুক্তির মাধ্যমে, যা এক্সেলকে ডেটা টাইপ রূপান্তর করতে বাধ্য করে।

কিভাবে # N/A দমন করা যায়

IFERROR ফাংশনের সাথে এটি করা খুব সুবিধাজনক।

উদাহরণস্বরূপ: = IFERROR (VLOOKUP (D7; পণ্য! $ A $ 2: $ C $ 5; 3; 0); "")।

যদি VLOOKUP ত্রুটি কোড # N/A ফেরত দেয়, তাহলে IFERROR এটিকে আটকাবে এবং প্যারামিটার 2 (এই ক্ষেত্রে, একটি খালি স্ট্রিং) প্রতিস্থাপন করবে এবং যদি কোনও ত্রুটি না ঘটে, তবে এই ফাংশনটি ভান করবে যে এটি একেবারেই নেই, কিন্তু শুধুমাত্র VLOOKUP আছে যা স্বাভাবিক ফলাফল ফিরিয়ে দিয়েছে।

অ্যারে

প্রায়শই তারা অ্যারের রেফারেন্সকে পরম করতে ভুলে যায় এবং অ্যারের প্রসারিত করার সময় "ফ্লোটস"। A2:C5 এর পরিবর্তে $A $2:$C$5 ব্যবহার করতে ভুলবেন না।

ওয়ার্কবুকের একটি পৃথক শীটে রেফারেন্স অ্যারে স্থাপন করা একটি ভাল ধারণা। এটা পায়ের নিচে না, এবং এটা নিরাপদ হবে.

আরও ভাল ধারণা এই অ্যারেটিকে একটি নামযুক্ত পরিসর হিসাবে ঘোষণা করা হবে।

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

ভাল, প্রতিভা প্রান্তে - ফর্ম মধ্যে অ্যারে ব্যবস্থা করতে.

নিষ্কাশন করার জন্য কলাম নির্দিষ্ট করতে COLUMN ফাংশন ব্যবহার করে

যে টেবিলে আপনি VLOOKUP ব্যবহার করে ডেটা পুনরুদ্ধার করছেন সেটির লুকআপ টেবিলের মতো একই কাঠামো থাকলে, কিন্তু সহজভাবে কম সারি থাকে, তাহলে আপনি পুনরুদ্ধার করা কলামগুলির সংখ্যা স্বয়ংক্রিয়ভাবে গণনা করতে VLOOKUP-এ COLUMN () ফাংশন ব্যবহার করতে পারেন৷ এই ক্ষেত্রে, সমস্ত VLOOKUP সূত্র একই হবে (প্রথম প্যারামিটারের জন্য সামঞ্জস্য, যা স্বয়ংক্রিয়ভাবে পরিবর্তিত হয়)! উল্লেখ্য যে প্রথম প্যারামিটারে একটি পরম কলাম স্থানাঙ্ক রয়েছে।

& "|" এবং সহ একটি যৌগিক কী তৈরি করা হচ্ছে

যদি একই সময়ে একাধিক কলাম দ্বারা অনুসন্ধান করা প্রয়োজন হয়, তবে অনুসন্ধানের জন্য একটি যৌগিক কী তৈরি করা প্রয়োজন। যদি রিটার্ন মানটি পাঠ্য না হয় (যেমনটি "কোড" ক্ষেত্রের ক্ষেত্রে হয়), তবে সংখ্যাসূচক, তাহলে আরও সুবিধাজনক SUMIFS সূত্রটি এর জন্য উপযুক্ত হবে এবং যৌগিক কলাম কীটির প্রয়োজন হবে না।

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