ডায়নামিক অ্যারে ফাংশনগুলি সবচেয়ে দরকারী এবং শক্তিশালী বৈশিষ্ট্যগুলির মধ্যে একটি। আপনি শত শত সারি অনুলিপি করে এমন জটিল সূত্রগুলি লেখার পরিবর্তে, আপনি একটি সূত্র লিখুন যা স্বয়ংক্রিয়ভাবে "ছিটকে" ফলাফল হিসাবে প্রয়োজন অনুসারে অনেকগুলি কোষে পরিণত হয়। যখন আপনার ডেটা পরিবর্তন হয়, তখন এই ফাংশনগুলি রিয়েল টাইমে আপডেট হয়। এটি প্রতিবেদন এবং সারাংশকে আরও দক্ষ এবং কম ত্রুটি-প্রবণ করে তোলে।
এই টিউটোরিয়ালে, আমরা 5টি উপায় দেখাই যে গতিশীল অ্যারে ফাংশন (ফিল্টার, ইউনিক, SORT) আপনি কীভাবে কাজ করবেন তা পরিবর্তন করবে।
এক্সেলে ডাইনামিক অ্যারে এবং স্পিলিং
ডাইনামিক অ্যারে: একটি কক্ষে একটি সূত্র সন্নিবেশ করান, এবং Excel স্বয়ংক্রিয়ভাবে প্রতিবেশী কোষগুলিতে ফলাফলগুলি পূরণ করে বা ছড়িয়ে দেয়। যদি ফলাফলের আকার পরিবর্তন হয় (আরো সারি, কম সারি), ছিটকে যাওয়া পরিসর স্বয়ংক্রিয়ভাবে বৃদ্ধি পায় বা সঙ্কুচিত হয়। আপনি একটি ছড়িয়ে পড়া পরিসীমা চিনতে পারবেন কারণ:
- সূত্রটি শুধুমাত্র উপরের-বাম কক্ষে থাকে।
- অন্যান্য কক্ষগুলি একটি হালকা সীমানা দেখায়, এবং আপনি যদি সেগুলিতে ক্লিক করেন, আপনি সূত্রটি ধূসর হয়ে গেছে দেখতে পাবেন৷
- আপনি সম্পূর্ণ ছিটকে যাওয়া পরিসীমাকে A2# (হ্যাশ প্রতীক) হিসাবে উল্লেখ করতে পারেন।
ডায়নামিক অ্যারে Microsoft 365 (Microsoft 365 এর জন্য Excel), Excel 2021 এবং পরবর্তীতে পাওয়া যায়।
1. ডুপ্লিকেট ছাড়া সারাংশের জন্য অনন্য তালিকা তৈরি করুন
গতিশীল অ্যারেগুলির আগে, সদৃশগুলি সরানোর জন্য হয় "সদৃশ সরান" বা জটিল সূত্র প্রয়োজন৷ UNIQUE দ্রুত সারাংশের জন্য ছড়ানো, অনন্য তালিকা তৈরি করে। এটি ড্রপডাউন, বৈধতা তালিকা এবং পিভট-মুক্ত ড্যাশবোর্ডের জন্য আদর্শ।
অনন্য অঞ্চলের তালিকা করুন:
- একটি ঘর নির্বাচন করুন এবং নিম্নলিখিত সূত্রটি সন্নিবেশ করুন।
এই সূত্রটি অনন্য পণ্যের একটি তালিকা ছড়িয়ে দেয়।
অনন্য সমন্বয়:
অঞ্চল এবং বিক্রয়কর্মীর অনন্য সমন্বয় পান:
এটি প্রতিটি অনন্য সংমিশ্রণ দেখায় একটি দুই-কলাম স্পিল প্রদান করে।
অনন্য অর্ডার গণনা করুন:
- সারাংশের জন্য COUNTA-এর সাথে UNIQUE একত্রিত করুন।
এই সূত্রটি অনন্য অর্ডারের মোট সংখ্যা গণনা করে। আপনি যদি ডেটাসেটে নতুন সারি যোগ করেন, তালিকাটি স্বয়ংক্রিয়ভাবে রিফ্রেশ হয়।
যে মানগুলি ঠিক একবার দেখা যায়:
এমন মানগুলি খুঁজুন যা শুধুমাত্র একবার ঘটে।
তৃতীয় যুক্তি (TRUE ) শুধুমাত্র এমন মান প্রদান করে যা পুনরাবৃত্তি হয় না।
ডেটা যাচাইকরণের জন্য UNIQUE ব্যবহার করুন (ড্রপডাউন):
- একটি ঘর নির্বাচন করুন যেখানে আপনি একটি ড্রপডাউন চান৷ ৷
- ডেটা -এ যান ট্যাব>> ডেটা যাচাইকরণ নির্বাচন করুন .
- অনুমতি দিন সেট করুন তালিকা করতে .
- সূত্রে , প্রকার:
এখন ড্রপডাউন সর্বদা আপনার ডেটার উপর ভিত্তি করে বর্তমান অনন্য অঞ্চলগুলি দেখায়৷
UNIQUE ব্যবহার করে গোষ্ঠীবদ্ধ সারাংশ:
আপনি একটি গতিশীল গোষ্ঠীবদ্ধ সংক্ষিপ্তসার তৈরি করতে SUMIF এর সাথে UNIQUE যুক্ত করতে পারেন।
বিভিন্ন অঞ্চলের মোট রাজস্ব গণনা করুন:
=SUMIF(B2:B61, I15#, G2:G61)
এই সূত্রটি ছিটকে যাওয়া অঞ্চলগুলির সম্পূর্ণ তালিকাকে বোঝায় (I15# ) SUMIF প্রতিটি এলাকার জন্য মোট রাজস্ব প্রদান করে। বিক্রয়ে সারি যোগ করুন বা পরিবর্তন করুন এবং অঞ্চল এবং মোট স্বয়ংক্রিয়ভাবে সামঞ্জস্য করুন।
এখন, অঞ্চল পরিবর্তন করুন এবং মোট আয় নির্বাচনের উপর ভিত্তি করে স্বয়ংক্রিয়ভাবে আপডেট হবে।
2. ডায়নামিক রিপোর্টের জন্য স্বয়ংক্রিয়ভাবে ডেটা ফিল্টার করুন
প্রথাগত ফিল্টারিংয়ের জন্য ম্যানুয়াল ফিল্টার বা জটিল সূত্র প্রয়োজন। FILTER() ফাংশন ডাইনামিক অ্যারেগুলির জন্য সবচেয়ে শক্তিশালী টুলগুলির মধ্যে একটি। এটি শুধুমাত্র সারিগুলি বের করে যা আপনার শর্ত পূরণ করে এবং ফলাফলটিকে একটি টেবিলের মতো পরিসরে ছড়িয়ে দেয়। এই ফাংশনটি ব্যবহার করে, আপনি একটি প্রতিবেদন তৈরি করতে পারেন যা অবিলম্বে আপডেট হয়।
অঞ্চল অনুসারে বিক্রয় ফিল্টার করুন:
- ফিল্টার ফাংশনের গতিশীল আচরণ দেখাতে, অঞ্চলের জন্য একটি ড্রপ-ডাউন ব্যবহার করুন।
=FILTER(A2:G61, B2:B61="East")
- এটিকে আরও গতিশীল করতে, ড্রপ-ডাউন থেকে মানদণ্ডের ঘরটি উল্লেখ করুন৷
=FILTER(A2:G61, B2:B61=I4)
এটি সমস্ত সারি ছড়িয়ে দেয় যেখানে অঞ্চল "পূর্ব"। এটি একটি মিনি রিপোর্ট যা প্রসারিত বা সংকুচিত হয় যদি আপনি ডেটা যোগ করেন বা সরিয়ে দেন। I4 পরিবর্তন করুন "উত্তর"-এ এবং রিপোর্ট স্বয়ংক্রিয়ভাবে আপডেট হয়-কোন VBA বা ম্যানুয়াল রিফ্রেশের প্রয়োজন নেই৷
এটি ডেটার স্ট্যাটিক কপি বাদ দেয়; আপনার রিপোর্ট সবসময় উৎস প্রতিফলিত.
একাধিক মানদণ্ড:
পূর্ব অঞ্চলের জন্য ফিল্টার করুন এবং এর পরিমাণ $1,000 এর বেশি:
=FILTER(A2:G61, (B2:B61="East")*(G2:G61>1000), "No matches")
তারকাচিহ্ন (* ) AND হিসাবে কাজ করে। আপনি প্লাস ব্যবহার করতে পারেন (+ OR শর্তের জন্য।
আপনি ইন্টারেক্টিভ ড্যাশবোর্ড তৈরি করতে পারেন যেখানে ব্যবহারকারীরা ড্রপডাউন থেকে মানদণ্ড নির্বাচন করে।
3. SORT() এবং SORTBY()
ব্যবহার করে স্বয়ংক্রিয়ভাবে সাজানো তালিকা তৈরি করুন
সাজানো মানে ডেটা কপি করা বা টেবিল ব্যবহার করা। SORT গতিশীল, ছড়ানো যায় এমন সাজানো দৃশ্য তৈরি করে। যখনই আপনি ডেটাসেট যোগ, অপসারণ বা আপডেট করেন, এটি স্বয়ংক্রিয়ভাবে ডেটা বাছাই করে।
স্বয়ংক্রিয়ভাবে সাজানো বিক্রয় লিডারবোর্ড:
এই সূত্রটি কলাম 7 (অ্যামাউন্ট) দ্বারা অবরোহ ক্রমে (-1) দ্বারা সমগ্র ডেটা পরিসীমা সাজায় ) মূল তথ্য অস্পর্শ থাকে. একটি নতুন শীর্ষ বিক্রয় যোগ করুন, এবং এটি স্বয়ংক্রিয়ভাবে সঠিক অবস্থানে প্রদর্শিত হবে৷
একাধিক সাজানোর স্তর:
বিক্রয়কর্মী দ্বারা সাজান, তারপর পরিমাণ অনুসারে:
=SORT(A2:G61, {3,7}, {1,-1})
কোঁকড়া বন্ধনী অ্যারে তৈরি করে:কলাম 3 আরোহী, তারপর কলাম 7 অবরোহ অনুসারে সাজান।
বিভিন্ন মানদণ্ড অনুসারে সাজান:
SORTBY ফাংশন আপনাকে অন্য রেঞ্জের মানের উপর ভিত্তি করে একটি রেঞ্জ বাছাই করতে দেয়।
সমস্ত কলাম রাখার সময় বিক্রয়কর্মীর নাম অনুসারে সমগ্র পরিসর সাজান:
=SORTBY(A2:G61, C2:C61, 1)
4. লক্ষ্যযুক্ত অনন্য সারাংশের জন্য ফিল্টার এবং অনন্য একত্রিত করুন
উন্নত সারাংশের জন্য, আপনি প্রথমে ফিল্টার করার জন্য ফাংশনগুলিকে একত্রিত করতে পারেন, তারপর স্বতন্ত্র করতে, একটি পরিষ্কার, স্বয়ংক্রিয়-আপডেটিং তালিকা ছড়িয়ে দিতে পারেন। রিপোর্ট তৈরি করতে ফাংশন একত্রিত করুন যা নিজেদের বজায় রাখে।
- একটি ঘর নির্বাচন করুন এবং নিম্নলিখিত সূত্রটি সন্নিবেশ করুন।
=UNIQUE(FILTER(D2:D61, B2:B61="North"))
এটি উত্তর অঞ্চলের পণ্যগুলিকে ফিল্টার করে, তারপরে অনন্যগুলি ছড়িয়ে দেয়৷
৷- এরপর, সারাংশ সাজানোর জন্য SORT ফাংশন যোগ করুন।
=SORT(UNIQUE(FILTER(D2:D61, B2:B61="North")))
এখন সূত্রটি একটি সাজানো, অনন্য পণ্য তালিকা ছড়িয়ে দেয়।
এটি অনন্য ফিল্টার করা তালিকার জন্য {=INDEX(…)} এর মতো কষ্টকর অ্যারে সূত্রগুলিকে প্রতিস্থাপন করে৷ ডেটা বা মানদণ্ড পরিবর্তন করুন, এবং এটি আঞ্চলিক পণ্য ইনভেন্টরির মতো প্রতিবেদনের জন্য নির্বিঘ্নে আপডেটগুলি ছড়িয়ে দেয়৷
5. গতিশীল, মানদণ্ড-চালিত সারাংশ পৃষ্ঠাগুলি (ফিল্টার, অনন্য, সাজান একত্রিত করুন)
এখন, এই ফাংশনগুলিকে একটি মিনি সারাংশ/রিপোর্টিং পৃষ্ঠাতে একত্রিত করুন যা কয়েকটি মানদণ্ড কোষ থেকে আপডেট হয়।
একটি অঞ্চল-স্তরের ড্যাশবোর্ড তৈরি করুন:একটি অঞ্চল ড্রপডাউন (UNIQUE দ্বারা চালিত), একটি ফিল্টার করা অঞ্চল তালিকা (FILTER), এবং একটি শীর্ষ-পণ্য-ভিত্তিক তালিকা (FILTER + SORT)৷
পদক্ষেপ 1:অনন্য ব্যবহার করে অঞ্চল ড্রপডাউন
অনন্য অঞ্চলগুলির একটি তালিকা তৈরি করা হয়েছে এবং একটি ড্রপ-ডাউন তৈরি করতে ব্যবহার করা হয়েছে৷
৷ধাপ 2:অঞ্চল-স্তরের বিক্রয় বিবরণ
=FILTER(SalesData!A2:G61, SalesData!B2:B61=B4, "No sales in this region")
এই সূত্রটি অঞ্চলের উপর ভিত্তি করে বিক্রয় ডেটা ফিল্টার করে। ড্রপ-ডাউন থেকে অঞ্চল পরিবর্তন করুন এবং বিক্রয় টেবিল স্বয়ংক্রিয়ভাবে আপডেট হবে।
ধাপ 3:নির্বাচিত অঞ্চলে শীর্ষ পণ্যগুলি৷
বেছে নেওয়া অঞ্চলে কোন পণ্য সবচেয়ে বেশি বিক্রি হয় তা শনাক্ত করুন।
- শিরোনাম এবং মোট আয় সহ একটি ছোট টেবিল তৈরি করুন।
- 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#) একটি দুই-কলাম অ্যারে তৈরি করে (পণ্য এবং মোট রাজস্ব)। 2 মানে "২য় কলাম অনুসারে সাজান (মোট রাজস্ব)"; -1 মানে অবরোহী ক্রম।
[নির্বাচিত অঞ্চল] রিপোর্টে গতিশীল শীর্ষ পণ্য:
- B4 এ অঞ্চলের ড্রপডাউন পরিবর্তন করুন। লক্ষ্য করুন যে সমস্ত সারাংশ আপডেট করা হয়েছে৷
- সেলে নতুন ডেটা যোগ করুন, এবং এটি রিপোর্টে যোগ করা হবে।
- কোন অনুলিপি করা সূত্র নেই, ম্যানুয়াল সাজানো নেই, পিভটটেবল রিফ্রেশ নেই৷
উপসংহার
এই টিউটোরিয়ালটি দেখায় যে 5টি উপায়ে ডায়নামিক অ্যারে ফাংশন (ফিল্টার, ইউনিক, সর্ট) আপনি কীভাবে কাজ করেন তা পরিবর্তন করে। গতিশীল অ্যারে ফাংশন স্প্রেডশীট বজায় রাখার ব্যস্ততা দূর করে। আপনি সূত্র অনুলিপি করা এবং ভাঙা রেফারেন্স ঠিক করার পরিবর্তে বিশ্লেষণে ফোকাস করুন। রিপোর্ট নিজেদের আপডেট. ড্যাশবোর্ড স্বয়ংক্রিয়ভাবে আপডেট হয়। একবার আপনি এই ফাংশনগুলি ব্যবহার করা শুরু করলে, আপনি দেখতে পাবেন যে সেগুলি সারাংশ এবং ড্যাশবোর্ডের জন্য আদর্শ—আপনি এমন রিপোর্ট তৈরি করতে পারেন যা কোনও ম্যানুয়াল রিফ্রেশিং, কোনও জটিল অ্যারে সূত্র এবং কোনও সাহায্যকারী কলাম ছাড়াই তাত্ক্ষণিকভাবে আপডেট করতে পারেন৷
সমাধান সহ বিনামূল্যে উন্নত এক্সেল ব্যায়াম পান!