এক্সেল ফর্মুলা: সেলে অনেক কিছুর মধ্যে একটি থাকে

জেনেরিক সূত্র | _+_ | সারসংক্ষেপ

একটি কোষে অনেকগুলো স্ট্রিং আছে কিনা তা পরীক্ষা করার জন্য, আপনি এর উপর ভিত্তি করে একটি সূত্র ব্যবহার করতে পারেন অনুসন্ধান করুন , সংখ্যা এবং SUMPRODUCT ফাংশন C5- এর সূত্রটি কপি করা হয়েছে:

= SUMPRODUCT (-- ISNUMBER ( SEARCH (things,A1)))>0

কোথায় জিনিস হয় নামযুক্ত পরিসীমা E5: E7।

ব্যাখ্যা

এই উদাহরণের লক্ষ্য হল B5: B14 এর প্রতিটি কোষ পরীক্ষা করা যাতে দেখা যায় যে এতে কোন স্ট্রিং আছে কিনা নামযুক্ত পরিসীমা জিনিস (E5: E7)। এই স্ট্রিং প্রদর্শিত হতে পারে যে কোন জায়গায় কক্ষে, তাই এটি একটি আক্ষরিক 'ধারণ' সমস্যা। C5- এর সূত্রটি কপি করা হয়েছে:

 = SUMPRODUCT (-- ISNUMBER ( SEARCH (things,B5)))>0

এই সূত্রের উপর ভিত্তি করে আরেকটি সূত্র যে একটি একক substring জন্য একটি কোষ পরীক্ষা করে। যদি কক্ষটিতে উপসর্গ থাকে, সূত্রটি TRUE প্রদান করে। যদি না হয়, সূত্রটি মিথ্যা প্রদান করে:

 = SUMPRODUCT (-- ISNUMBER ( SEARCH (things,B5)))>0

যখন অনুসন্ধান ফাংশন একটি স্ট্রিং খুঁজে বের করে, এটি একটি স্ট্রিং এর অবস্থানকে একটি সংখ্যা হিসাবে প্রদান করে। যদি অনুসন্ধান করুন না একটি স্ট্রিং খুঁজুন, এটি একটি #মান প্রদান করে! ত্রুটি. এর মানে হল যদি কোন মিল থাকে তাহলে ISNUMBER সত্য ফিরে আসবে এবং যদি না হয় তাহলে মিথ্যা।

এই উদাহরণে, লক্ষ্য হল একাধিক স্ট্রিং পরীক্ষা করা, তাই আমরা সার্চ ফাংশন দিচ্ছি a স্ট্রিংগুলির তালিকা মধ্যে নামযুক্ত পরিসীমা জিনিস । যেহেতু 3 টি স্ট্রিং আছে জিনিস ('লাল', 'সবুজ', এবং 'নীল'), অনুসন্ধান 3 এ একটি ফলাফল প্রদান করে অ্যারে এটার মত:

কিভাবে একটি ভিউলআপ সূত্র লিখতে হয়

  ISNUMBER ( SEARCH (substring,B5)) // test for substring

কারণ 'লাল' এবং 'নীল' পাওয়া যায় না, অনুসন্ধান একটি #মান দেয়! ত্রুটি. যাইহোক, যেহেতু 'সবুজ' সেল B5- এ পাঠ্যের শেষের দিকে প্রদর্শিত হয়, অনুসন্ধান 23 প্রদান করে (যেমন 'সবুজ' 23 তম অক্ষরে শুরু হয়)।

এই অ্যারেটি সরাসরি ফেরত দেওয়া হয় ISNUMBER ফাংশন , যা অ্যারের আইটেমগুলিকে সত্য বা মিথ্যে রূপান্তর করে:

 {#VALUE!#VALUE!23}

যুক্তিসঙ্গতভাবে, যদি আমাদের অ্যারেতে এমনকি একটি সত্য থাকে, আমরা জানি যে একটি কোষে কমপক্ষে একটি স্ট্রিং রয়েছে যা আমরা খুঁজছি। TRUE চেক করার সবচেয়ে সহজ উপায় হল সব মান একসাথে যোগ করা। আমরা এটা দিয়ে করতে পারি SUMPRODUCT ফাংশন , কিন্তু প্রথমে আমাদেরকে সত্য / মিথ্যা মানগুলি 1s এবং 0s এ a দিয়ে জোর করতে হবে ডবল নেতিবাচক (--) এটার মত:

  ISNUMBER ({#VALUE!#VALUE!23}) // returns {FALSEFALSETRUE}

এটি শুধুমাত্র 1s এবং 0s ধারণকারী একটি নতুন অ্যারে উৎপন্ন করে:

এক্সেল 2010 এ কীভাবে একটি ড্রপ ডাউন বাক্স সন্নিবেশ করা যায়

 --{FALSEFALSETRUE} // coerce to 1s and 0s

যা সরাসরি SUMPRODUCT এ বিতরণ করা হয়:

 {001}

প্রক্রিয়া করার জন্য মাত্র একটি অ্যারে দিয়ে, SUMPRODUCT অ্যারেতে আইটেম যোগ করে এবং একটি ফলাফল প্রদান করে। যে কোন শূন্য ফলাফল মানে আমাদের একটি 'হিট' আছে, তাই আমরা সত্য বা মিথ্যা চূড়ান্ত ফলাফল জোর করতে> 0 যোগ করি:

 = SUMPRODUCT ({001}) // returns 1

মনে রাখবেন যে মিলের যেকোনো সংমিশ্রণ শূন্যের চেয়ে বড় সংখ্যা ফিরিয়ে দেবে এবং সূত্রটি সত্যকে ফিরিয়ে আনবে।

একটি হার্ড কোডেড তালিকা সহ

স্ট্রিংগুলির তালিকার জন্য একটি পরিসীমা ব্যবহার করার প্রয়োজন নেই। আপনি একটি ব্যবহার করতে পারেন অ্যারে ধ্রুবক । উদাহরণস্বরূপ, 'লাল', 'নীল', বা 'সবুজ' পরীক্ষা করার জন্য, আপনি এই মত একটি সূত্র ব্যবহার করতে পারেন:

 = SUMPRODUCT ({001})>0 // returns TRUE

SUM ফাংশন

তিহাসিকভাবে, SUMPRODUCT প্রায়ই উপস্থিত হয় অ্যারের সূত্র , কারণ এটি স্থানীয়ভাবে অ্যারে পরিচালনা করতে পারে, নিয়ন্ত্রণ ছাড়াই + শিফট + এন্টার । এটি অধিকাংশ ব্যবহারকারীর কাছে 'আরো বন্ধুত্বপূর্ণ' সূত্রটি তৈরি করে। ভিতরে এক্সেল 365 , যা অ্যারে পরিচালনা করে স্থানীয়ভাবে , দ্য SUM ফাংশন SUMPRODUCT এর পরিবর্তে নিয়ন্ত্রণ + শিফট + এন্টার ছাড়া ব্যবহার করা যেতে পারে:

এক্সেলে পিভট টেবিল ব্যবহার

 = SUMPRODUCT (-- ISNUMBER ( SEARCH ({'red','blue','green'},B5)))>0

মিথ্যা ম্যাচ প্রতিরোধ

এই পদ্ধতির সাথে একটি সমস্যা হল আপনি দীর্ঘ শব্দগুলির মধ্যে উপস্থিত সাবস্ট্রিং থেকে মিথ্যা মিল পেতে পারেন। উদাহরণস্বরূপ, যদি আপনি 'dr' কে মেলানোর চেষ্টা করেন তবে আপনি 'আন্দ্রেয়া', 'পানীয়', 'শুকনো' ইত্যাদি খুঁজে পেতে পারেন যেহেতু এই শব্দগুলির মধ্যে 'dr' দেখা যায়। এটি ঘটে কারণ অনুসন্ধান স্বয়ংক্রিয়ভাবে একটি 'ধারণ' ম্যাচ করে।

দ্রুত হ্যাকের জন্য, আপনি অন্য শব্দে 'dr' ধরা থেকে বাঁচতে অনুসন্ধান শব্দগুলির (যেমন 'dr', বা 'dr') এর চারপাশে স্থান যোগ করতে পারেন। কিন্তু এটি ব্যর্থ হবে যদি 'dr' একটি কোষে প্রথম বা শেষ প্রদর্শিত হয়, অথবা বিরামচিহ্নের সাথে উপস্থিত হয়।

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

সংযুক্তি