ডাইনামিক অ্যারে ফাংশনগুলো সবচেয়ে কার্যকর এবং শক্তিশালী বৈশিষ্ট্যগুলোর মধ্যে একটি। সैकড়ো সারিতে জটিল ফর্মুলা লিখে কপি করার বদলে, আপনি একটি ফর্মুলা লিখেন যা অটোমেটিক্যালি "স্পিল" করে প্রয়োজনীয় সেলগুলোর মধ্যে ফলাফল। আপনার ডেটা পরিবর্তন হলে, এই ফাংশনগুলো রিয়েল টাইমে আপডেট হয়। এটি রিপোর্ট এবং সারাংশকে আরও দক্ষ এবং ত্রুটি-প্রতিরোধী করে তোলে।
এই টিউটোরিয়ালে, আমরা দেখাব ৫টি পথ যাতে ডাইনামিক অ্যারে ফাংশন (FILTER, UNIQUE, SORT) আপনার কাজ করার পদ্ধতি বদলাবে।
এক্সেলে ডাইনামিক অ্যারে এবং স্পিলিং
ডাইনামিক অ্যারে: একটি সেলে ফর্মুলা লিখুন, এবং এক্সেল অটোমেটিক্যালি ফলাফলগুলো সন্নিকট সেলগুলোর মধ্যে ফিল বা স্পিল করে। ফলাফলের আকার পরিবর্তন হলে (আরো সারি, কম সারি), স্পিলড রেঞ্জ স্বয়ংক্রিয় বড় বা ছোট হয়। আপনি একটি স্পিলড রেঞ্জ চিনতে পারেন কারণ:
- ফর্মুলা শুধু টপ-লেফট সেলে থাকে।
- অন্য সেলগুলো হালকা বর্ডার দেখায়, এবং ক্লিক করলে ফর্মুলা গ്രে-আউট দেখায়।
- আপনি পুরো স্পিলড রেঞ্জকে A2# (হ্যাশ চিহ্ন) হিসেবে রেফার করতে পারেন।
ডাইনামিক অ্যারে Microsoft 365 (Excel for Microsoft 365), Excel 2021, এবং পরবর্তী ভার্সনে উপলব্ধ।
১. ডুপ্লিকেট ছাড়া সারাংশের জন্য ইউনিক লিস্ট জেনারেট করুন
ডাইনামিক অ্যারে এর আগে, ডুপ্লিকেট সরাতে "Remove Duplicates" বা জটিল ফর্মুলা লাগত। UNIQUE স্পিলযোগ্য, ইউনিক লিস্ট তৈরি করে দ্রুত সারাংশের জন্য। এটি ড্রপডাউন, ভ্যালিডেশন লিস্ট, এবং পিভট-ফ্রি ড্যাশবোর্ডের জন্য আদর্শ।
ইউনিক Region লিস্ট:
- একটি সেল সিলেক্ট করুন এবং নিচের ফর্মুলা লিখুন।
এই ফর্মুলা ইউনিক প্রোডাক্টের লিস্ট স্পিল করে।
ইউনিক কম্বিনেশন:
Region এবং Salesperson-এর ইউনিক কম্বিনেশন পান:
এটি দুই কলাম স্পিল করে যা প্রতিটি ইউনিক কম্বিনেশন দেখায়।
ইউনিক অর্ডার কাউন্ট করুন:
- UNIQUE কে COUNTA-এর সাথে কম্বাইন করুন একটি সারাংশের জন্য।
এই ফর্মুলা মোট ইউনিক অর্ডার সংখ্যা হিসাব করে। আপনি ডেটাসেটে নতুন সারি যোগ করলে, লিস্ট স্বয়ংক্রিয় রিফ্রেশ হবে।
শুধু একবার আসা মান:
যে মানগুলো শুধু একবার আসে সেগুলো খুঁজুন।
তৃতীয় আর্গুমেন্ট (TRUE) শুধু অ-পুনরাবৃত্ত মানগুলো ফেরত দেয়।
ডেটা ভ্যালিডেশন (ড্রপডাউন) এর জন্য UNIQUE ব্যবহার:
- যে সেলে ড্রপডাউন চান সেটা সিলেক্ট করুন।
- Data ট্যাবে যান >> Data Validation বেছে নিন।
- Allow কে List সেট করুন।
- Source-এ টাইপ করুন:
এখন ড্রপডাউন সবসময় আপনার ডেটার ভিত্তিতে বর্তমান ইউনিক অঞ্চল দেখাবে।
UNIQUE ব্যবহার করে গ্রুপড সারাংশ:
আপনি UNIQUE কে SUMIF-এর সাথে জোড়া দিয়ে একটি ডাইনামিক গ্রুপড সারাংশ বানাতে পারেন।
বিভিন্ন অঞ্চলের মোট রেভেনিউ হিসাব করুন:
=SUMIF(B2:B61, I15#, G2:G61)
এই ফর্মুলা পুরো স্পিলড অঞ্চল লিস্টকে (I15#) রেফার করে। SUMIF প্রতিটি অঞ্চলের মোট রেভেনিউ ফেরত দেয়। Sales-এ সারি যোগ বা পরিবর্তন করলে, অঞ্চল এবং টোটাল অটোমেটিক অ্যাডজাস্ট হয়।
এখন, অঞ্চল পরিবর্তন করলে Total Revenue সিলেকশনের ভিত্তিতে অটোমেটিক আপডেট হবে।
২. ডাইনামিক রিপোর্টের জন্য ডেটা অটোমেটিক ফিল্টার করুন
ট্রাডিশনাল ফিল্টারিং ম্যানুয়াল ফিল্টার বা জটিল ফর্মুলা চায়। FILTER() ফাংশন ডাইনামিক অ্যারের জন্য সবচেয়ে শক্তিশালী টুল এক। এটি শুধু সেই সারি বের করে যা আপনার শর্ত পূরণ করে এবং ফলাফলকে একটি টেবিল-সদৃশ রেঞ্জে স্পিল করে। এই ফাংশন ব্যবহার করে, আপনি একটি রিপোর্ট তৈরি করতে পারেন যা 순간ে আপডেট হয়।
Region অনুযায়ী Sales ফিল্টার করুন:
- FILTER ফাংশনের ডাইনামিক আচরণ দেখাতে, Region-এ ড্রপ-ডাউন ব্যবহার করুন।
=FILTER(A2:G61, B2:B61="East")
- এটি আরও ডাইনামিক করতে, ড্রপ-ডাউন থেকে ক্রাইটেরিয়া সেল রেফার করুন।
=FILTER(A2:G61, B2:B61=I4)
এটি "East" Region যেখানে আছে সকল সারি স্পিল করে। এটি একটি মিনি রিপোর্ট যা ডেটা যোগ বা বাদ দিলে বিস্তারিত বা সংকুচিত হয়। I4-কে "North" এ পরিবর্তন করলে, রিপোর্ট স্বয়ংক্রিয় আপডেট হবে—কোনো VBA বা ম্যানুয়াল রিফ্রেশ ছাড়া।
এটি ডেটার স্ট্যাটিক কপি দূর করে; আপনার রিপোর্ট সবসময় সোর্সকে প্রতিফলিত করে।
একাধিক মাপদণ্ড:
East region AND অ্যামাউন্ট $1,000-এর বেশি ফিল্টার করুন:
=FILTER(A2:G61, (B2:B61="East")*(G2:G61>1000), "No matches")
এস্টারিস্ক (*) AND হিসেবে কাজ করে। আপনি প্লাস (+) ব্যবহার করতে পারেন OR শর্তের জন্য।
আপনি ইন্টারেক্টিভ ড্যাশবোর্ড বানাতে পারেন যেখানে ব্যবহারকারী ড্রপডাউন থেকে ক্রাইটেরিয়া বেছে নেবে।
৩. SORT() এবং SORTBY() ব্যবহার করে অটোমেটিক্যালি সর্টেড লিস্ট তৈরি করুন
সর্ট করা আগে মানে ডেটা কপি করা বা টেবিল ব্যবহার করা। SORT ডাইনামিক, স্পিলযোগ্য সর্টেড ভিউ তৈরি করে। আপনি ডেটাসেটে যোগ, বাদ দিন, বা আপডেট করলে, এটি অটোমেটিক্যালি ডেটা সর্ট করে।
অটো-সর্টেড সেলস লিডারবোর্ড:
এই ফর্মুলা সমগ্র ডেটা রেঞ্জকে কলাম 7 (Amount) অনুযায়ী ডিসেন্ডিং অর্ডারে (-1) সর্ট করে। মূল ডেটা অপরিবর্তিত থাকে। একটি নতুন টপ সেল যোগ করলে, সেটি অটোমেটিক্যালি সঠিক পজিশনে আসবে।
একাধিক সর্ট লেভেল:
Salesperson দ্বারা সর্ট করুন, তারপর Amount দ্বারা:
=SORT(A2:G61, {3,7}, {1,-1})
কার্লি ব্র্যাকেট অ্যারে তৈরি করে: কলাম ৩ এসেন্ডিং, তারপর কলাম ৭ ডিসেন্ডিং।
ভিন্ন মাপদণ্ড দিয়ে সর্ট:
SORTBY ফাংশন আপনাকে একটি রেঞ্জকে অন্য রেঞ্জের ভ্যালু অনুযায়ী সর্ট করতে দেয়।
সমগ্র রেঞ্জকে সেলসপারসন নাম অনুযায়ী সর্ট করুন সব কলাম রেখে:
=SORTBY(A2:G61, C2:C61, 1)
৪. FILTER এবং UNIQUE কম্বাইন করুন টার্গেটেড ইউনিক সারাংশের জন্য
উন্নত সারাংশের জন্য, আপনি ফাংশনগুলো কম্বাইন করতে পারেন যাতে প্রথমে ফিল্টার করে, তারপর ইউনিক করে, একটি পরিষ্কার, অটো-আপডেটিং লিস্ট স্পিল করে। ফাংশনগুলো কম্বাইন করুন এমন রিপোর্ট বানাতে যা নিজেদের রক্ষা করে।
- একটি সেল সিলেক্ট করুন এবং নিচের ফর্মুলা লিখুন।
=UNIQUE(FILTER(D2:D61, B2:B61="North"))
এটি North region থেকে প্রোডাক্ট ফিল্টার করে, তারপর ইউনিক সেগুলো স্পিল করে।
- পরবর্তী, SORT ফাংশন যোগ করুন সারাংশ সর্ট করার জন্য।
=SORT(UNIQUE(FILTER(D2:D61, B2:B61="North")))
এখন ফর্মুলা একটি সর্টেড, ইউনিক প্রোডাক্ট লিস্ট স্পিল করে।
এটি জটিল অ্যারে ফর্মুলা যেমন {=INDEX(…)} কে ইউনিক ফিল্টারড লিস্টের জন্য রিপ্লেস করে। ডেটা বা ক্রাইটেরিয়া পরিবর্তন করলে, এটি রিপোর্টের জন্য seamless আপডেট স্পিল করে যেমন অঞ্চলীয় প্রোডাক্ট ইনভেন্টরি।
৫. ডাইনামিক, ক্রাইটেরিয়া-চালিত সারাংশ পেজ (FILTER, UNIQUE, SORT কম্বাইন করুন)
এখন, এই ফাংশনগুলো একটি মিনি সারাংশ/রিপোর্টিং পেজে কম্বাইন করুন যা কিছু ক্রাইটেরিয়া সেল থেকে আপডেট হয়।
একটি অঞ্চল-স্তরের ড্যাশবোর্ড বানান: একটি অঞ্চল ড্রপডাউন (UNIQUE দ্বারা চালিত), একটি ফিল্টারড অঞ্চল লিস্ট (FILTER), এবং একটি টপ-প্রোডাক্টস-বাই-রেজিয়ন লিস্ট (FILTER + SORT)।
ধাপ ১: UNIQUE ব্যবহার করে Region Dropdown
ইউনিক অঞ্চলের একটি লিস্ট তৈরি করা হয়েছে এবং उससे ড্রপ-ডাউন বানানো হয়েছে।
ধাপ ২: Region-Level Sales Details
=FILTER(SalesData!A2:G61, SalesData!B2:B61=B4, "No sales in this region")
এই ফর্মুলা অঞ্চল অনুযায়ী সেলস ডেটা ফিল্টার করে। ড্রপ-ডাউন থেকে অঞ্চল পরিবর্তন করলে সেলস টেবিল স্বয়ংক্রিয় আপডেট হবে।
ধাপ ৩: সিলেক্ট করা অঞ্চলের টপ প্রোডাক্টস
চেনে নিন কোন প্রোডাক্ট সিলেক্ট করা অঞ্চলে সবচেয়ে বেশি বিক্রি হয়েছে।
- Product এবং Total Revenue হেডার সহ একটি ছোট টেবিল বানান।
- L4-এ, সিলেক্ট করা অঞ্চলে বিক্রি হওয়া ইউনিক প্রোডাক্ট পান:
=UNIQUE(FILTER(SalesData!D2:D61, SalesData!B2:B61=B4))
এটি সেই অঞ্চলের জন্য প্রোডাক্টের একটি লিস্ট স্পিল করে।
- M4-এ, সেই অঞ্চলে প্রতি প্রোডাক্ট মোট রেভেনিউ হিসাব করুন:
=SUMIFS(SalesData!G2:G61, SalesData!B2:B61, B4, SalesData!D2:D61, L4#)
এটি L4#-এ প্রতিটি প্রোডাক্টের সাথে মিলে যাওয়া স্পিলড টোটাল রেভেনিউ লিস্ট ফেরত দেয়।
- সেগুলো রেভেনিউ অনুযায়ী সর্ট করা (ডিসেন্ডিং) দেখাতে, দুটি স্পিলড কলাম একসাথে সর্ট করুন:
=SORT(CHOOSE({1,2}, L4#, M4#), 2, -1)
এখানে, CHOOSE({1,2}, L4#, M4#) একটি দুই-কলাম অ্যারে তৈরি করে (Product এবং Total Revenue)। 2 মানে "২য় কলাম (Total Revenue) দিয়ে সর্ট করো"; -1 মানে ডিসেন্ডিং অর্ডার।
ডাইনামিক টপ প্রোডাক্টস ইন [সিলেক্টেড রেজিয়ন] রিপোর্ট:
- B4-এ অঞ্চল ড্রপডাউন পরিবর্তন করুন। লক্ষ্য করুন সব সারাংশ আপডেট হয়েছে।
- Sales-এ নতুন ডেটা যোগ করুন, সেটি রিপোর্টে যোগ হবে।
- কপি করা ফর্মুলা নেই, ম্যানুয়াল সর্টিং নেই, কোনো PivotTable রিফ্রেশ নেই।
উপসংহার
এই টিউটোরিয়াল ৫টি পথ দেখিয়েছে যাতে ডাইনামিক অ্যারে ফাংশন (FILTER, UNIQUE, SORT) আপনার কাজ করার পদ্ধতি বদলায়। ডাইনামিক অ্যারে ফাংশন স্প্রেডশিট রক্ষণাবেক্ষণের ব্যস্ততা দূর করে। আপনি ফর্মুলা কপি করা এবং ভাঙা রেফারেন্স ঠিক করার বদলে বিশ্লেষণে ফোকাস করতে পারেন। রিপোর্টগুলো নিজেদের আপডেট হয়। ড্যাশবোর্ডগুলো অটোমেটিক্যালি আপডেট হয়। একবার এই ফাংশনগুলো ব্যবহার শুরু করলে, আপনি পাবেন এগুলো সারাংশ এবং ড্যাশবোর্ডের জন্য আদর্শ—আপনি এমন রিপোর্ট বানাতে পারেন যা কোনো ম্যানুয়াল রিফ্রেশ ছাড়া, কোনো জটিল অ্যারে ফর্মুলা ছাড়া, এবং কোনো হেল্পার কলাম ছাড়া তাৎক্ষণিক আপডেট হয়।
Get FREE Advanced Excel Exercises with Solutions!
