বিষয়বস্তু
সমস্ত ক্লাসিক অনুসন্ধান এবং টাইপ প্রতিস্থাপন ফাংশন VPR (ভলুকআপ), গ্রাউন্ড-পেনেট্রেটিং রাডার (হলুকআপ), আরও উন্মুক্ত (ম্যাচ) এবং তাদের মত যারা তাদের একটি গুরুত্বপূর্ণ বৈশিষ্ট্য আছে – তারা প্রথম থেকে শেষ পর্যন্ত অনুসন্ধান করে, অর্থাত্ উৎস ডেটাতে বাম-থেকে-ডান বা উপরে-থেকে-নিচে। যত তাড়াতাড়ি প্রথম মিল পাওয়া যায়, অনুসন্ধান বন্ধ হয়ে যায় এবং শুধুমাত্র আমাদের প্রয়োজনীয় উপাদানটির প্রথম ঘটনাটি পাওয়া যায়।
আমাদের যদি প্রথমটি না, তবে শেষ ঘটনাটি খুঁজে বের করতে হয় তবে কী করবেন? উদাহরণস্বরূপ, ক্লায়েন্টের জন্য শেষ লেনদেন, সর্বশেষ অর্থপ্রদান, সাম্প্রতিকতম অর্ডার ইত্যাদি?
পদ্ধতি 1: একটি অ্যারে সূত্র দিয়ে শেষ সারি খুঁজে বের করা
যদি মূল টেবিলে একটি তারিখ বা একটি সারির সিরিয়াল নম্বর সহ একটি কলাম না থাকে (অর্ডার, অর্থপ্রদান …), তাহলে আমাদের কাজ হল, শেষ সারিটি খুঁজে বের করা যা প্রদত্ত শর্তকে সন্তুষ্ট করে। এটি নিম্নলিখিত অ্যারে সূত্র দিয়ে করা যেতে পারে:
এখানে:
- ক্রিয়া IF (আইএফ) একটি কলামের সব কক্ষ একে একে পরীক্ষা করে ক্রেতা এবং লাইন নম্বর প্রদর্শন করে যদি এতে আমাদের প্রয়োজনীয় নাম থাকে। শীটে লাইন নম্বর ফাংশন দ্বারা আমাদের দেওয়া হয় লাইন (ROW), কিন্তু যেহেতু আমাদের টেবিলে সারি সংখ্যা দরকার, তাই আমাদের অতিরিক্ত 1 বিয়োগ করতে হবে, কারণ আমাদের টেবিলে একটি হেডার আছে।
- তারপর ফাংশন MAX টি (MAX টি) সারি সংখ্যার গঠিত সেট থেকে সর্বোচ্চ মান নির্বাচন করে, অর্থাৎ ক্লায়েন্টের সাম্প্রতিক লাইনের সংখ্যা।
- ক্রিয়া এর INDEX (INDEX) অন্য যেকোনো প্রয়োজনীয় টেবিল কলাম থেকে পাওয়া শেষ নম্বর সহ ঘরের বিষয়বস্তু ফেরত দেয় (অর্ডার কোড).
এই সব হিসাবে প্রবেশ করা আবশ্যক অ্যারে সূত্র, যেমন:
- অফিস 365-এ সর্বশেষ আপডেট ইনস্টল করা এবং গতিশীল অ্যারেগুলির জন্য সমর্থন সহ, আপনি কেবল প্রেস করতে পারেন প্রবেশ করান.
- অন্যান্য সমস্ত সংস্করণে, সূত্রটি প্রবেশ করার পরে, আপনাকে কীবোর্ড শর্টকাট টিপতে হবে জন্য ctrl+স্থানপরিবর্তন+প্রবেশ করান, যা ফর্মুলা বারে স্বয়ংক্রিয়ভাবে কোঁকড়া ধনুর্বন্ধনী যুক্ত করবে।
পদ্ধতি 2: নতুন লুকআপ ফাংশন দিয়ে বিপরীত লুকআপ করুন
আমি ইতিমধ্যে একটি নতুন বৈশিষ্ট্য সম্পর্কে একটি ভিডিও সহ একটি দীর্ঘ নিবন্ধ লিখেছি দেখুন (এক্সলুকআপ), যা পুরানো VLOOKUP প্রতিস্থাপন করতে অফিসের সর্বশেষ সংস্করণে উপস্থিত হয়েছে৷ (ভলুকআপ). ব্রাউজের সাহায্যে, আমাদের কাজটি বেশ প্রাথমিকভাবে সমাধান করা হয়েছে, কারণ। এই ফাংশনের জন্য (VLOOKUP-এর বিপরীতে), আপনি স্পষ্টভাবে অনুসন্ধানের দিকনির্দেশ সেট করতে পারেন: টপ-ডাউন বা বটম-আপ – এর শেষ আর্গুমেন্ট (-1) এর জন্য দায়ী:
পদ্ধতি 3. সর্বশেষ তারিখ সহ একটি স্ট্রিং অনুসন্ধান করুন
যদি উত্স ডেটাতে আমাদের কাছে সিরিয়াল নম্বর বা একটি তারিখ সহ একটি কলাম থাকে যা একই ভূমিকা পালন করে, তবে কাজটি সংশোধন করা হয়েছে - আমাদের একটি মিলের সাথে শেষ (সর্বনিম্ন) লাইনটি নয়, সর্বশেষের সাথে লাইনটি খুঁজে বের করতে হবে ( সর্বোচ্চ) তারিখ।
ক্লাসিক ফাংশন ব্যবহার করে কীভাবে এটি করা যায় তা আমি ইতিমধ্যেই বিস্তারিত আলোচনা করেছি এবং এখন নতুন ডাইনামিক অ্যারে ফাংশনগুলির শক্তি ব্যবহার করার চেষ্টা করা যাক। বৃহত্তর সৌন্দর্য এবং সুবিধার জন্য, আমরা একটি কীবোর্ড শর্টকাট ব্যবহার করে মূল টেবিলটিকে একটি "স্মার্ট" টেবিলে রূপান্তর করি জন্য ctrl+T বা আদেশ হোম - একটি টেবিল হিসাবে বিন্যাস (হোম - টেবিল হিসাবে ফর্ম্যাট).
তাদের সহায়তায়, এই "হত্যাকারী দম্পতি" আমাদের সমস্যাটি খুব সুন্দরভাবে সমাধান করে:
এখানে:
- প্রথমে ফাংশন ছাঁকনি (ছাঁকনি) আমাদের টেবিল থেকে শুধুমাত্র সেই সারি নির্বাচন করে যেখানে কলামে আছে ক্রেতা - আমাদের প্রয়োজন নাম।
- তারপর ফাংশন গ্রেড (সাজান) তারিখ অনুসারে নির্বাচিত সারিগুলিকে নিচের ক্রমে সাজায়, সবচেয়ে সাম্প্রতিক চুক্তিটি শীর্ষে থাকে।
- ক্রিয়া এর INDEX (INDEX) প্রথম সারিটি বের করে, অর্থাৎ আমাদের প্রয়োজনীয় শেষ ট্রেডটি ফেরত দেয়।
- এবং, অবশেষে, বহিরাগত ফিল্টার ফাংশন ফলাফল থেকে অতিরিক্ত 1ম এবং 3য় কলামগুলি সরিয়ে দেয় (অর্ডার কোড и ক্রেতা) এবং শুধুমাত্র তারিখ এবং পরিমাণ ছেড়ে দেয়। এই জন্য, ধ্রুবক একটি অ্যারে ব্যবহার করা হয়. {0;1;0;1}, আমরা কোন কলামগুলি (1) চাই বা (0) প্রদর্শন করতে চাই না তা নির্ধারণ করে।
পদ্ধতি 4: পাওয়ার কোয়েরিতে শেষ মিল খুঁজে বের করা
ঠিক আছে, সম্পূর্ণতার জন্য, আসুন পাওয়ার কোয়েরি অ্যাড-ইন ব্যবহার করে আমাদের বিপরীত অনুসন্ধান সমস্যার সমাধান দেখি। তার সাহায্যে, সবকিছু খুব দ্রুত এবং সুন্দরভাবে সমাধান করা হয়।
1. আসুন একটি কীবোর্ড শর্টকাট ব্যবহার করে আমাদের মূল টেবিলটিকে "স্মার্ট" তে রূপান্তর করি জন্য ctrl+T বা আদেশ হোম - একটি টেবিল হিসাবে বিন্যাস (হোম - টেবিল হিসাবে ফর্ম্যাট).
2. বোতাম দিয়ে পাওয়ার কোয়েরিতে এটি লোড করুন টেবিল/রেঞ্জ থেকে ট্যাব উপাত্ত (ডেটা — টেবিল/রেঞ্জ থেকে).
3. আমরা (হেডারে ফিল্টারের ড্রপ-ডাউন তালিকার মাধ্যমে) তারিখের ক্রমানুসারে আমাদের টেবিলকে সাজাই, যাতে সাম্প্রতিকতম লেনদেনগুলি শীর্ষে থাকে।
4… ট্যাবে রুপান্তর একটি দল নির্বাচন করুন গ্রুপ দ্বারা (রূপান্তর — গোষ্ঠী অনুসারে) এবং গ্রাহকদের দ্বারা গ্রুপিং সেট করুন এবং একটি সমষ্টিগত ফাংশন হিসাবে, বিকল্পটি নির্বাচন করুন সব লাইন (সমস্ত সারি). আপনি আপনার পছন্দ মতো নতুন কলামের নাম দিতে পারেন - উদাহরণস্বরূপ বিস্তারিত.
গ্রুপ করার পর, আমরা আমাদের ক্লায়েন্টদের অনন্য নামের একটি তালিকা এবং কলামে পাব বিস্তারিত - তাদের প্রতিটির সমস্ত লেনদেন সহ টেবিল, যেখানে প্রথম লাইনটি সর্বশেষ লেনদেন হবে, যা আমাদের প্রয়োজন:
5. বোতাম দিয়ে একটি নতুন গণনা করা কলাম যোগ করুন কাস্টম কলাম ট্যাব কলাম যুক্ত করুন (কলাম যোগ করুন — কাস্টম কলাম যোগ করুন)এবং নিম্নলিখিত সূত্র লিখুন:
এখানে বিস্তারিত - এটি সেই কলাম যা থেকে আমরা গ্রাহকদের দ্বারা টেবিল গ্রহণ করি, এবং ৭ {} আমরা যে সারিটি বের করতে চাই তার সংখ্যা হল (পাওয়ার কোয়েরিতে সারি সংখ্যা শূন্য থেকে শুরু হয়)। আমরা রেকর্ড সহ একটি কলাম পাই (রেকর্ড), যেখানে প্রতিটি এন্ট্রি প্রতিটি টেবিলের প্রথম সারি:
কলাম হেডারে ডবল তীর সহ বোতামের সাহায্যে সমস্ত রেকর্ডের বিষয়বস্তু প্রসারিত করা বাকি রয়েছে শেষ চুক্তি পছন্দসই কলাম নির্বাচন করা:
… এবং তারপর কলামটি মুছুন যা আর প্রয়োজন নেই বিস্তারিত এর শিরোনামে ডান ক্লিক করে - কলাম সরান (কলামগুলি সরান).
এর মাধ্যমে শিটে ফলাফল আপলোড করার পর হোম — বন্ধ করুন এবং লোড করুন — বন্ধ করুন এবং লোড করুন (বাড়ি — বন্ধ করুন এবং লোড করুন — বন্ধ করুন এবং লোড করুন...) আমরা সাম্প্রতিক লেনদেনের তালিকা সহ এমন একটি সুন্দর টেবিল পাব, যেমনটি আমরা চেয়েছিলাম:
আপনি যখন উত্স ডেটা পরিবর্তন করেন, তখন আপনি অবশ্যই তাদের উপর ডান-ক্লিক করে ফলাফল আপডেট করতে ভুলবেন না - কমান্ডটি আপডেট এবং সংরক্ষণ করুন (রিফ্রেশ) বা কীবোর্ড শর্টকাট জন্য ctrl+অল্টার+F5.
- LOOKUP ফাংশন VLOOKUP-এর একটি বংশধর
- কিভাবে নতুন ডাইনামিক অ্যারে ফাংশন SORT, FILTER, এবং UNIC ব্যবহার করবেন
- LOOKUP ফাংশন সহ সারি বা কলামে শেষ অ-ফাঁকা ঘরটি খুঁজে বের করা