এক্সেলের ডাইনামিক অ্যারে ফাংশন কীভাবে আমার ওয়ার্কফ্লো সহজ করে দিয়েছে—একটি প্রোডাক্টিভিটি ব্রেকথ্রু

আমার পরিচিত অধিকাংশ এক্সেল ব্যবহারকারী একইভাবে ফর্মুলা শিখেছেন যা আমি শিখেছিলাম—একবারে একটি ফাংশন, ইতিমধ্যে জানা বিষয়গুলির ওপর দিয়ে স্ট্যাক করে। ডাইনামিক অ্যারে ফাংশনগুলা সেই দক্ষতাগুলোকে প্রতিস্থাপন করে না; বরং সেগুলো অনেক ওয়ার্কঅরাউন্ডের প্রয়োজনীয়তা দূর করে দেয়। আমি একক্ষণ ধরে TAKE এবং DROP ফাংশন দিয়ে স্বয়ংক্রিয়ভাবে আপডেট হওয়া টপ-ফাইভ তালিকা চালিয়ে আসছি, এবং নিচের চারটি ফাংশনের ক্ষেত্রেও একই পরিবর্তন ঘটেছে। প্রতিটি ফাংশন আমার আগের বহু-ধাপের রুটিনকে—which আমি প্রতিবর্তমানে করতাম—একক ফর্মুলায় পরিণত করেছে।

FILTER: হেল্পার কলাম এবং অ্যারে ফর্মুলার পুরো রিচুয়ালকে প্রতিস্থাপন করেছে

একটি ফর্মুলা এখন সে কাজ করে যা একাধিক ফাংশন মিলে করত

পুরনো এক্সেলে মিল রো বের করা মানে নেস্টেড INDEX, MATCH, SMALL এবং IFERROR ফর্মুলা বানানো এবং সেটি Ctrl + Shift + Enter দিয়ে এন্টার করা। সেটি কাজ করত, কিন্তু পরে সেটি মেইনটেইন করা সমস্যা। অন্য অপশন ছিল AutoFilter প্রয়োগ করা, ভিজিবল রো কপি করা এবং অন্য কোথাও স্ট্যাটিক ভ্যালু হিসেবে পেস্ট করা। সেটও ঠিকই ছিল যতক্ষণ না সোর্স ডেটা পরিবর্তন হত।

FILTER ফাংশন একই কাজ এক লাইনে করে। আমার সেলস স্প্রেডশীটে—which ৩২টি রো নিয়ে অঞ্চল, পণ্য ক্যাটাগরি এবং সেলসপারসন নিয়ে কাজ করে—পশ্চিমাঞ্চল থেকে সব ইলেকট্রনিক্স সেল বের করার ফর্মুলা এমন:

=FILTER(A2:G33, (B2:B33="West")*(C2:C33="Electronics"))

প্রথম আর্গুমেন্ট হলো যা রিটার্ন করতে চান সেই রেঞ্জ। দ্বিতীয় আর্গুমেন্ট হলো কন্ডিশন, এবং দুটি চেকের মধ্যে গুণ করা AND হিসেবে কাজ করে—দুটোই সত্য হতে হবে। গুণ চিহ্নকে প্লাস চিহ্নে বদলালে সেটি OR হয়ে যায়, এবং রেজাল্ট স্বয়ংক্রিয়ভাবে স্পিল হয়। সোর্সে নতুন রো যোগ করলেই আউটপুট এন্টার চাপা সাথে আপডেট হয়ে যায়।

আপনি IFERROR দিয়ে FILTER কে র‍্যাপ করে থার্ড আর্গুমেন্ট দিয়ে খালি রেজাল্ট হ্যান্ডেল করতে পারেন। =FILTER(range, condition, "No matches") কোনো রো কোয়ালিফাই না করলে #CALC! এরর দেখানোর বদলে "No matches" দেখাবে।

UNIQUE: আমার তিন-ধাপের ডিডুপ্লিকেশন রুটিনকে একক সেলে পরিণত করেছে

Remove Duplicates ভালো, কিন্তু কখনো স্বয়ং আপডেট হয়নি

Data ট্যাবের অধীনে Remove Duplicates অপশন একবারের ক্লিন-আপের জন্য ঠিক আছে। কিন্তু সেটি একটি স্ট্যাটিক তালিকা তৈরি করে। আপনি যখন একটি কলাম কপি করে নতুন লোকেশনে নিয়ে যান, ডায়লগ চালান, বা রেজাল্ট সর্ট করেন—পরবর্তীবার কেউ রো যোগ করলে সব কিছু পুনরায় করতে হয়। আমি এতবার করা হয়েছে যে গণনাতीत।

UNIQUE সেটি সব ধাপ বাদ দেয়। আমার সেলস স্প্রেডশীটের সেলসপারসন কলামে পয়েন্ট করলেই:

=UNIQUE(D2:D33)

রেজাল্ট স্পিল হয়ে যায়: John Smith, Sarah Johnson, Mike Wilson, Lisa Brown, David Chen, Emma Davis, Tom Rodriguez, এবং Amy Foster। আটটি নাম, কোনো ডায়লগ বক্স ছাড়া। এটাকে =SORT(UNIQUE(D2:D33)) হিসেবে র‍্যাপ করলে একই তালিকা অ্যালফাবেটিক্যালি সর্ট হয়ে আসে। আউটপুট সোর্সের সাথে কানেক্টেড থাকে, তাই সোর্স ডেটায় নতুন নাম যোগ করলেই স্পিল করা লিস্ট স্বয়ংক্রিয়ভাবে বাড়ে।

এই সেটআপ ডেটা ভ্যালিডেশন ড্রপডাউন লিস্টের জন্য একটি ক্লিন সোর্স তৈরি করে। আপনি যদি স্পিল রেঞ্জকে হ্যাশ দিয়ে রেফারেন্স করেন যেমন D35#, তাহলে সোর্স ডেটায় নতুন নাম আসার সাথে সাথে আপনার ড্রপডাউন বেড়ে যাবে।

SORTBY: আমার কপি-পেস্ট-অ্যান্ড-সার্ট শাফলকে চিরস্থায়ীভাবে শেষ করেছে

সোর্স ডেটা ছোঁয়া ছাড়া একটি ভিউ সর্ট করা

এক্সেলে সর্টিং সবসময় একটি ছোট রিস্ক বহন করে। সোর্স রি-অ্যারেঞ্জ করলে ফিক্সড রো রেফারেন্স करने वाले ফর্মুলা ভাঙতে পারে, তাই আমার ফলব্যাক ছিল এক টুকরো কপি করে সেফ জায়গায় রাখা এবং কপি টাকে সর্ট করা। SORT সাহায্য করত, কিন্তু শুধু তখনই যখন আমি সকল কলাম ডিসপ্লে করতে চাই যা দিয়ে সর্ট করছি।

SORTBY সেই বাধা দূর করে। এটি একটি রেঞ্জকে অন্য রেঞ্জের ভ্যালু দিয়ে সর্ট করে, এবং দ্বিতীয় রেঞ্জ আউটপুটে থাকতে হয় না। আমি আমার ডেটাসেটে সেলসপারসনকে রেভিনিউ দিয়ে র‍্যাঙ্ক করার জন্য নিচের ফর্মুলা ব্যবহার করেছি:

=SORTBY(D2:D33, G2:G33, -1)

প্রথম আর্গুমেন্ট হলো যা রিটার্ন করবেন (সেলসপারসন), দ্বিতীয় হলো কী দিয়ে সর্ট করবেন (সেলস রেভিনিউ), এবং -1 অর্ডারকে ডিসেন্ডিং সেট করে। নামগুলো হাইएস্ট সেলস অ্যামাউন্ট থেকে লোএস্ট পর্যন্ত র‍্যাঙ্ক হয়ে আসে, এবং সেলস অ্যামাউন্ট কলাম আউটপুটে আসে না যতক্ষণ না আপনি চান।

এটি FILTER এর সাথে খুব ভালো জোড়া মারায়। FILTER(D2:G33, B2:B33="North") কে SORTBY দিয়ে র‍্যাপ করলে শুধু নর্থ রিজনের রেকর্ডগুলো হাই-টু-লো সর্ট হয়ে এক শটে আসে। সোর্স ডেটা অক্ষত থাকে।

SORTBY একাধিক সর্ট লেভেল এক্সেপ্ট করে। আপনি আরও রেঞ্জ/অর্ডার পেয়ার যোগ করে প্রথমে রিজন দিয়ে, তারপর প্রতিটি রিজনের ভিতরে সেলস রেভিনিউ দিয়ে সর্ট করতে পারেন।

SEQUENCE: ফিল হ্যান্ডেল এবং ROW ট্রিকগুলা যেগুলোতে আমি গর্বিত ছিলাম, সেগুলাকে প্রতিস্থাপন করেছে

কোনো সেল ড্র্যাগ না করে একটি সিরিজ জেনারেট করা

নাম্বার্ড সিরিজ জেনারেট করার মানে ছিল ফিল হ্যান্ডেল ড্র্যাগ করা যতক্ষণ না ধৈর্য চলে যেত, বা =ROW(A1) লিখে নিচে কপি করা। দুটোই কাজ করে, কিন্তু যেকোনো নিচের ডেটা পরিবর্তন হলে সেগুলো রিসাইজ হয় না।

SEQUENCE একই কাজ একক সেলে করে, এবং আর্গুমেন্টের ওপর ভিত্তি করে রো, কলাম বা পুরো গ্রিড জেনারেট করে। সিনট্যাক্স:

=SEQUENCE(rows, [columns], [start], [step])

আমার ডেটার আটটি ইউনিক সেলসপারসনকে ১০০১ থেকে নাম্বার করার জন্য আমি এই ফর্মুলা ব্যবহার করেছি:

=SEQUENCE(8, 1, 1001, 1)

এটি ১০০১ থেকে ১০০৮ পর্যন্ত একক কলামে রিটার্ন করে। কলাম ১ এবং স্টেপ ১ রাখলে সেটি लगাতার ইন্টিজার সহ একটি ভার্টিক্যাল লিস্ট থাকে। SEQUENCE असলে অন্য ফাংশনের ভিতরে কাজ করার সময় চমক দেয়। জানুয়ারি ২০২৬-এর জন্য ৩১-দিনের ডেট রেঞ্জ বানাতে এক ফর্মুলা:

=DATE(2026, 1, SEQUENCE(31))

স্পিল করা অ্যারে TEXT, INDEX, বা যেকোনো রেঞ্জ এক্সপেক্ট করার ফাংশনে ফিড করলে যা আগে হেল্পার কলাম দরকার হতো সেটি সমাহিত হয়ে যায়। আমি এটার ওপর বিস্তারিত লিখেছিলাম SEQUENCE দিয়ে ডেট কলাম ফিল করার কথা, এবং এটা এখনো আমার প্রতিটি ক্যালেন্ডার বা শিডিউল বানাতে সময় বাঁচায়।

আগামীতে আমি যেসব ফাংশন এক্সপ্লোর করতে চাই

পুরনো কোনো মেথড ভাঙলো নাই। সেগুলো শুধু বেশি সময় নেয় এবং যেসব ফাইল তৈরি করে সেগুলো মেইনটেইন করা কঠিন—আমার জন্যও এবং যারা পরে ওয়ার্কবুক খোলবেন তাদের জন্যও। আমার জন্য শিফ্ট হলো আমি ডিফল্ট olaraq কীতে হাত দিই। নতুন স্প্রেডশীট ডাইনামিক অ্যারে চিন্তাভাবনা দিয়ে শুরু হয়, এবং পুরনো টুলবক্স শুধু তখন বের হয় যখন কাউকে অন্যের ফাইল এডিট করতে হয়। আমার লিস্টে পরের হলো স্ট্রিং ভাঙার জন্য TEXTSPLIT, রেঞ্জ কম্বাইন করার জন্য VSTACK এবং HSTACK, এবং পিভট টেবিল ছাড়া পেইর করার জন্য PIVOTBY এবং GROUPBY। প্রতিটি রিলিজ আরেকটি অভ্যাস রিটায়ার করে দিচ্ছে, যা আমার পক্ষে ঠিকই আছে।