এক্সেল

এক্সেলে ডাইনামিক অ্যারে ফর্মুলা

Dynamic Array Formulas Excel

ডায়নামিক অ্যারেগুলি বছরের মধ্যে এক্সেল সূত্রের সবচেয়ে বড় পরিবর্তন। হয়তো এখন পর্যন্ত সবচেয়ে বড় পরিবর্তন। এর কারণ হল ডাইনামিক অ্যারে আপনাকে একটি সূত্রে একই সময়ে একাধিক ভ্যালুর সাথে সহজে কাজ করতে দেয়। অনেক ব্যবহারকারীর জন্য, এটি প্রথমবারের মতো তারা অ্যারে সূত্রগুলি বুঝতে এবং ব্যবহার করবে।





এটি একটি বড় আপগ্রেড এবং স্বাগত পরিবর্তন। ডাইনামিক অ্যারে এক্সেলে কিছু কঠিন সমস্যা সমাধান করবে এবং কার্যত শীটগুলি ডিজাইন এবং নির্মাণের পদ্ধতি পরিবর্তন করবে।

উপস্থিতি

ডায়নামিক অ্যারে এবং নীচের নতুন ফাংশন শুধুমাত্র উপলব্ধ এক্সেল 365 । এক্সেল 2016 এবং এক্সেল 2019 ডায়নামিক অ্যারে ফর্মুলা সাপোর্ট দেয় না। সুবিধার জন্য, আমি 'ডায়নামিক এক্সেল' (এক্সেল 365) এবং 'ট্র্যাডিশনাল এক্সেল' (2019 বা তার আগে) ব্যবহার করব নীচের সংস্করণগুলিকে আলাদা করতে।





নতুন: ডায়নামিক অ্যারে ফর্মুলা ভিডিও প্রশিক্ষণ

নতুন ফাংশন

ডাইনামিক অ্যারে আপডেটের অংশ হিসেবে, এক্সেল এখন new টি নতুন ফাংশন অন্তর্ভুক্ত করেছে যা প্রচলিত সূত্রের সাহায্যে problemsতিহ্যগতভাবে সমাধান করা কঠিন এমন সমস্যার সমাধানের জন্য ডায়নামিক অ্যারে সরাসরি ব্যবহার করে। প্রতিটি ফাংশনের বিশদ বিবরণ এবং উদাহরণের জন্য নীচের লিঙ্কগুলিতে ক্লিক করুন:

ফাংশন উদ্দেশ্য
ছাঁকনি ডেটা ফিল্টার করুন এবং মিলে যাওয়া রেকর্ডগুলি ফেরত দিন
রান্ডাররে এলোমেলো সংখ্যার অ্যারে তৈরি করুন
ক্রম ক্রমিক সংখ্যার অ্যারে তৈরি করুন
সাজান কলাম অনুসারে পরিসীমা সাজান
ক্রমানুসার অন্য পরিসীমা বা অ্যারে দ্বারা পরিসীমা সাজান
অনন্য একটি তালিকা বা পরিসীমা থেকে অনন্য মান বের করুন
এক্সলুকআপ VLOOKUP এর জন্য আধুনিক প্রতিস্থাপন
XMATCH MATCH ফাংশনের জন্য আধুনিক প্রতিস্থাপন

ভিডিও: এক্সেলে নতুন গতিশীল অ্যারে ফাংশন (প্রায় 3 মিনিট)।



এক্সেল মধ্যরাতের পরে দু'বারের মধ্যে ঘন্টা গণনা করে

বিঃদ্রঃ: এক্সলুকআপ এবং XMATCH নতুন গতিশীল অ্যারে ফাংশনগুলির মূল গোষ্ঠীতে ছিল না, তবে তারা নতুন গতিশীল অ্যারে ইঞ্জিনে দুর্দান্ত চালায়। XLOOKUP VLOOKUP কে প্রতিস্থাপন করে এবং একটি আধুনিক, নমনীয় পদ্ধতির প্রস্তাব দেয় যা অ্যারের সুবিধা নেয়। XMATCH হল MATCH ফাংশনের একটি আপগ্রেড, যা নতুন ক্ষমতা প্রদান করে ইন্ডেক্স এবং ম্যাচ সূত্র

উদাহরণ

বিস্তারিত জানার আগে, আসুন একটি সহজ উদাহরণ দেখি। নীচে আমরা নতুন ব্যবহার করছি অনন্য ফাংশন A5 দিয়ে B5: B15 পরিসীমা থেকে অনন্য মান বের করতে অবিবাহিত E5 এ প্রবেশ করা সূত্র:

 
= UNIQUE (B5:B15) // return unique values in B5:B15

অনন্য ফাংশন উদাহরণ

ফলাফলটি পাঁচটি অনন্য শহরের নামের একটি তালিকা, যা E5: E9 এ প্রদর্শিত হয়।

সমস্ত সূত্রের মতো, ডেটা পরিবর্তন হলে স্বতন্ত্র স্বয়ংক্রিয়ভাবে আপডেট হবে। নীচে, ভ্যাঙ্কুভার পোর্টল্যান্ডকে 11 তম সারিতে প্রতিস্থাপিত করেছে।

পরিবর্তনের পরে অনন্য ফাংশন উদাহরণ

স্পিলিং - একটি সূত্র, অনেক মান

ডায়নামিক এক্সেল -এ, যেসব সূত্র একাধিক মান ফেরত দেবে ' খেলা এই মানগুলি সরাসরি ওয়ার্কশীটে ুকবে। এটি তাত্ক্ষণিকভাবে সূত্র ব্যবহারকারীদের জন্য আরও যৌক্তিক হবে। এটি একটি সম্পূর্ণ গতিশীল আচরণও - যখন সোর্স ডেটা পরিবর্তিত হয়, ছিটানো ফলাফল অবিলম্বে আপডেট হবে।

যে আয়তক্ষেত্র মানগুলোকে ঘিরে রাখে তাকে বলা হয় ' খেলার পরিসীমা '। আপনি লক্ষ্য করবেন যে স্পিল রেঞ্জের বিশেষ হাইলাইটিং রয়েছে। উপরের অনন্য উদাহরণে, স্পিল পরিসীমা হল E5: E10।

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

দ্রষ্টব্য: যখন স্পিলিং অন্যান্য ডেটা দ্বারা ব্লক করা হয়, আপনি একটি #SPILL ত্রুটি দেখতে পাবেন। একবার আপনি স্পিল পরিসরের জন্য জায়গা তৈরি করলে, সূত্রটি স্বয়ংক্রিয়ভাবে ছড়িয়ে পড়বে।

ভিডিও: স্পিলিং এবং স্পিল পরিসীমা

স্পিল রেঞ্জ রেফারেন্স

একটি স্পিল পরিসীমা উল্লেখ করতে, পরিসরের প্রথম ঘরের পরে একটি হ্যাশ চিহ্ন (#) ব্যবহার করুন। উদাহরণস্বরূপ, ব্যবহারের উপরে UNIQUE ফাংশন থেকে ফলাফল উল্লেখ করতে:

 
=E5# // reference UNIQUE results

এটি পুরো স্পিল পরিসীমা উল্লেখ করার মতোই, এবং আপনি এই সিনট্যাক্সটি দেখতে পাবেন যখন আপনি একটি সূত্র লিখবেন যা একটি সম্পূর্ণ স্পিল পরিসীমা বোঝায়।

আপনি একটি স্পিল পরিসীমা রেফারেন্স সরাসরি অন্যান্য সূত্রগুলিতে খাওয়াতে পারেন। উদাহরণস্বরূপ, UNIQUE দ্বারা ফিরে আসা শহরের সংখ্যা গণনা করতে, আপনি ব্যবহার করতে পারেন:

 
= COUNTA (E5#) // count unique cities

ডায়নামিক অ্যারে স্পিল রেঞ্জ রেফারেন্সের উদাহরণ

যখন স্পিল পরিসীমা পরিবর্তন হয়, সূত্রটি সর্বশেষ তথ্য প্রতিফলিত করবে।

ব্যাপক সরলীকরণ

নতুন ডায়নামিক অ্যারে ফর্মুলার সংযোজন মানে নির্দিষ্ট কিছু সূত্র ব্যাপকভাবে সরলীকরণ করা যেতে পারে। এখানে কিছু উদাহরণ আছে:

  • এক্সট্র্যাক্ট এবং তালিকা অনন্য মান ( আগে | পরে )
  • অনন্য মান গণনা করুন ( আগে | পরে )
  • রেকর্ড ফিল্টার এবং এক্সট্র্যাক্ট ( আগে | পরে )
  • আংশিক মিল বের করুন ( আগে | পরে )

একজনের ক্ষমতা

'এক সূত্র, অনেক মূল্যবোধ' পদ্ধতির সবচেয়ে শক্তিশালী সুবিধাগুলির উপর নির্ভরশীলতা কম পরম অথবা মিশ্র রেফারেন্স যেহেতু একটি গতিশীল অ্যারে সূত্র ওয়ার্কশীটে ফলাফল ছড়িয়ে দেয়, রেফারেন্সগুলি অপরিবর্তিত থাকে, কিন্তু সূত্রটি সঠিক ফলাফল তৈরি করে।

উদাহরণস্বরূপ, নীচে আমরা 'A' গ্রুপে রেকর্ড বের করতে ফিল্টার ফাংশন ব্যবহার করি। F5 ঘরে, একটি একক সূত্র প্রবেশ করানো হয়:

 
= FILTER (B5:D11,B5:B11='a') // references are relative

ডায়নামিক অ্যারে এক সূত্র শুধুমাত্র উদাহরণ

লক্ষ্য করুন উভয় রেঞ্জ আনলক আপেক্ষিক রেফারেন্স, কিন্তু সূত্র পুরোপুরি কাজ করে।

এটি অনেক ব্যবহারকারীর জন্য একটি বিশাল সুবিধা, কারণ এটি সূত্র লেখার প্রক্রিয়াটিকে অনেক সহজ করে তোলে। আরেকটি ভাল উদাহরণের জন্য, নীচের গুণক সারণীটি দেখুন।

শৃঙ্খল ফাংশন

যখন আপনি একাধিক ডাইনামিক অ্যারে ফাংশন একসাথে চেইন করেন তখন জিনিসগুলি সত্যিই আকর্ষণীয় হয়ে ওঠে। সম্ভবত আপনি UNIQUE দ্বারা প্রাপ্ত ফলাফলগুলি সাজাতে চান? সহজ। শুধু মোড়ানো SORT ফাংশন এই মত UNIQUE ফাংশন কাছাকাছি:

একসাথে UNIQUE এবং SORT এর উদাহরণ

আগের মতো, যখন সোর্স ডেটা পরিবর্তিত হয়, নতুন স্বতন্ত্র ফলাফল স্বয়ংক্রিয়ভাবে উপস্থিত হয়, সুন্দরভাবে সাজানো হয়।

দেশীয় আচরণ

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

উদাহরণস্বরূপ, ট্র্যাডিশনাল এক্সেলে, যদি আমরা LEN ফাংশন প্রতি পরিসীমা পাঠ্য মান, আমরা একটি দেখতে পাবেন অবিবাহিত ফলাফল. ডায়নামিক এক্সেল -এ, যদি আমরা LEN ফাংশনকে মূল্যবোধের একটি পরিসর দেই, আমরা দেখতে পাব একাধিক ফলাফল নীচের এই পর্দাটি বাম দিকে পুরানো আচরণ এবং ডানদিকে নতুন আচরণ দেখায়:

অ্যারে সহ LEN ফাংশন - পুরানো এবং নতুন

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

 
= VLOOKUP ('jose',F7:H10,{1,2,3},0)

VLOOKUP একাধিক কলাম ফিরিয়ে দেবে:

VLOOKUP এবং গতিশীল অ্যারে সহ একাধিক ফলাফল

অন্য কথায়, যদিও VLOOKUP কখনও একাধিক মান ফেরত দেওয়ার জন্য ডিজাইন করা হয়নি, এটি এখন করতে পারে, ধন্যবাদ ডায়নামিক এক্সেলের নতুন সূত্র ইঞ্জিনের জন্য।

সব সূত্র

অবশেষে, লক্ষ্য করুন যে গতিশীল অ্যারে কাজ করে সব সূত্র শুধুই না ফাংশন । নিচের উদাহরণে C5 এর একটি একক সূত্র রয়েছে:

সেল প্রথম অক্ষর এক্সেল এক্সেল
 
=B5:B14*C4:L4

ফলাফল 10 দ্বারা 10 পরিসরে ছড়িয়ে পড়ে যার মধ্যে 100 টি কোষ রয়েছে:

ডায়নামিক অ্যারে গুণের সারণী

দ্রষ্টব্য: ditionতিহ্যবাহী এক্সেলে, আপনি অ্যারে সূত্র দ্বারা ফেরত একাধিক ফলাফল দেখতে পারেন যদি আপনি সূত্র পরিদর্শন করতে F9 ব্যবহার করুন । কিন্তু যতক্ষণ না আপনি a হিসাবে সূত্রটি প্রবেশ করছেন মাল্টি-সেল অ্যারে সূত্র , ওয়ার্কশীটে শুধুমাত্র একটি মান প্রদর্শিত হবে।

অ্যারেগুলি মূলধারায় যায়

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

 
{1,2,3} // horizontal array {123} // vertical array

অ্যারে একটি প্রোগ্রামিং শব্দ যা একটি নির্দিষ্ট ক্রমে প্রদর্শিত আইটেমের একটি তালিকা বোঝায়। এক্সেল সূত্রগুলিতে অ্যারেগুলি প্রায়শই আসে কারণ হল অ্যারেগুলি কোষের একটি পরিসরে মানগুলি পুরোপুরি প্রকাশ করে

ভিডিও: একটি অ্যারে কি?

অ্যারে অপারেশন গুরুত্বপূর্ণ হয়ে ওঠে

যেহেতু ডায়নামিক এক্সেল ফর্মুলা সহজেই একাধিক ভ্যালুর সাথে কাজ করতে পারে, তাই অ্যারে অপারেশন আরও গুরুত্বপূর্ণ হয়ে উঠবে। 'অ্যারে অপারেশন' শব্দটি একটি অভিব্যক্তি বোঝায় যা একটি অ্যারেতে একটি যৌক্তিক পরীক্ষা বা গণিত অপারেশন চালায়। উদাহরণস্বরূপ, নিচের অভিব্যক্তিটি পরীক্ষা করে যদি B5: B9 এর মান 'ca' এর সমান হয়

 
=B5:B9='ca' // state = 'ca'

অ্যারে অপারেশন উদাহরণ পরীক্ষা a

কারণ B5: B9 এ 5 টি কোষ আছে, ফলে একটি অ্যারেতে 5 টি TRUE/FALSE মান রয়েছে:

 
{FALSETRUEFALSETRUETRUE}

নীচের অ্যারে অপারেশন 100 এর বেশি পরিমাণের জন্য পরীক্ষা করে:

 
=C5:C9>100 // amounts > 100

অ্যারে অপারেশন উদাহরণ পরীক্ষা খ

চূড়ান্ত অ্যারে অপারেশন একক অভিব্যক্তিতে পরীক্ষা A এবং পরীক্ষা B কে একত্রিত করে:

 
=(B5:B9='ca')*(C5:C9>100) // state = 'ca' and amount > 100

অ্যারে অপারেশন উদাহরণ পরীক্ষা a এবং b

দ্রষ্টব্য: গণিত ক্রিয়াকলাপের সময় এক্সেল স্বয়ংক্রিয়ভাবে TRUE এবং FALSE মানগুলিকে 1 এবং 0 এ বাধ্য করে।

এটিকে এক্সেলে ডায়নামিক অ্যারে ফর্মুলায় ফিরিয়ে আনতে, নিচের উদাহরণটি দেখায় যে আমরা কিভাবে ফিল্টার ফাংশনের ভিতরে ঠিক একই অ্যারে অপারেশন ব্যবহার করতে পারি অন্তর্ভুক্ত যুক্তি:

ফিল্টার ফাংশন সহ অ্যারে অপারেশন

ফিল্টার দুটি রেকর্ড ফেরত দেয় যেখানে state = 'ca' এবং পরিমাণ> 100।

একটি প্রদর্শনের জন্য, দেখুন: দুটি মানদণ্ড দিয়ে কীভাবে ফিল্টার করবেন (ভিডিও)।

নতুন এবং পুরানো অ্যারের সূত্র

ডায়নামিক এক্সেলে, নিয়ন্ত্রণ + শিফট + এন্টার সহ অ্যারের সূত্রগুলি প্রবেশ করার দরকার নেই। যখন একটি সূত্র তৈরি করা হয়, এক্সেল চেক করে যে সূত্রটি একাধিক মান ফেরত দিতে পারে কিনা। যদি তাই হয়, এটি স্বয়ংক্রিয়ভাবে একটি গতিশীল অ্যারে সূত্র হিসাবে সংরক্ষিত হবে, কিন্তু আপনি কোঁকড়া ধনুর্বন্ধনী দেখতে পাবেন না। নীচের উদাহরণটি ডাইনামিক এক্সেলে প্রবেশ করা একটি সাধারণ অ্যারের সূত্র দেখায়:

গতানুগতিক এক্সেলে মৌলিক অ্যারের সূত্র

আপনি যদি formulaতিহ্যবাহী এক্সেলে একই সূত্র খুলেন, তাহলে আপনি কোঁকড়া ধনুর্বন্ধনী দেখতে পাবেন:

ডাইনামিক এক্সেলে বেসিক অ্যারে ফর্মুলা

অন্য দিকে যাচ্ছি, যখন একটি 'traditionalতিহ্যবাহী' অ্যারে সূত্র ডায়নামিক এক্সেলে খোলা হয়, আপনি সূত্র বারে কোঁকড়া ধনুর্বন্ধনী দেখতে পাবেন। উদাহরণস্বরূপ, নীচের স্ক্রিনটি ট্র্যাডিশনাল এক্সেলে একটি সাধারণ অ্যারের সূত্র দেখায়:

কোঁকড়া ধনুর্বন্ধনী সহ সরল অ্যারের সূত্র দৃশ্যমান

যাইহোক, যদি আপনি কোন পরিবর্তন ছাড়াই সূত্রটি পুনরায় প্রবেশ করেন, কোঁকড়া ধনুর্বন্ধনীগুলি সরানো হয় এবং সূত্রটি একই ফলাফল প্রদান করে:

কোঁকড়া ধনুর্বন্ধনী সহ সরল অ্যারের সূত্র দৃশ্যমান নয়

নিচের লাইনটি হল যে অ্যারে সূত্রগুলি কন্ট্রোল + শিফট + এন্টার (সিএসই) দিয়ে প্রবেশ করে এখনও সামঞ্জস্য বজায় রাখার জন্য কাজ করে, তবে ডাইনামিক এক্সেলে সিএসই দিয়ে অ্যারের সূত্রগুলি প্রবেশ করার দরকার নেই।

চরিত্র

গতিশীল অ্যারে প্রবর্তনের সাথে, আপনি দেখতে পাবেন @ অক্ষরটি প্রায়শই সূত্রগুলিতে উপস্থিত হয়। @ অক্ষর 'নামে পরিচিত একটি আচরণকে সক্ষম করে অন্তর্নিহিত ছেদ '। অন্তর্নিহিত ছেদ একটি যৌক্তিক প্রক্রিয়া যেখানে অনেক মান এক মান থেকে হ্রাস করা হয়।

Ditionতিহ্যবাহী এক্সেলে, অন্তর্নিহিত ছেদ একটি নীরব আচরণ ব্যবহার করা হয় (যখন প্রয়োজন হয়) একটি কক্ষের একক ফলাফলে একাধিক মান কমাতে ব্যবহৃত হয়। ডায়নামিক এক্সেলে, এটি সাধারণত প্রয়োজন হয় না, যেহেতু একাধিক ফলাফল ওয়ার্কশীটে ছড়িয়ে পড়তে পারে। যখন এটি প্রয়োজন হয়, অন্তর্নিহিত ছেদটি ually অক্ষরের সাথে ম্যানুয়ালি আহ্বান করা হয়।

যখন এক্স্রেডের একটি পুরোনো সংস্করণ তৈরি করা স্প্রেডশীট খোলার সময়, আপনি দেখতে পারেন existing অক্ষরটি বিদ্যমান সূত্রগুলিতে স্বয়ংক্রিয়ভাবে যুক্ত হয়েছে সম্ভাব্য অনেক মান ফিরিয়ে দিতে। Ditionতিহ্যবাহী এক্সেল -এ, একটি সূত্র যা একাধিক মান প্রদান করে তা কার্যপত্রকে ছড়াবে না। @ চরিত্রটি ডাইনামিক এক্সেলে এই একই আচরণকে বাধ্য করে যাতে সূত্রটি একইভাবে আচরণ করে এবং একই ফলাফল প্রদান করে যেমনটি মূল এক্সেল সংস্করণে হয়েছিল।

অন্য কথায়, ওয়ার্কশীটে একাধিক ফলাফল ছড়ানো থেকে পুরনো সূত্র আটকাতে @ যোগ করা হয়। সূত্রের উপর নির্ভর করে, আপনি @ অক্ষরটি অপসারণ করতে সক্ষম হবেন এবং সূত্রের আচরণ পরিবর্তন হবে না।

সারসংক্ষেপ

  • ডাইনামিক অ্যারে কিছু সূত্রকে লিখতে অনেক সহজ করে তুলবে।
  • আপনি এখন মিলিত ডেটা ফিল্টার করতে পারেন, বাছাই করতে পারেন, এবং সূত্রের সাহায্যে অনন্য মানগুলি সহজেই বের করতে পারেন।
  • ফিল্টার এবং সাজানোর মত কাজ করার জন্য ডাইনামিক অ্যারে সূত্রগুলিকে শৃঙ্খলিত (নেস্টেড) করা যেতে পারে।
  • যেসব সূত্র একাধিক মান ফেরত দেবে তা স্বয়ংক্রিয়ভাবে ছিটকে যাবে।
  • একটি অ্যারে ফর্মুলা লিখতে Ctrl+Shift+Enter ব্যবহার করার প্রয়োজন নেই।
  • ডাইনামিক অ্যারে সূত্র শুধুমাত্র এক্সেল 365 এ উপলব্ধ।
লেখক ডেভ ব্রুনস


^