एक्सेल सूत्र: डेटा सत्यापन शामिल नहीं होना चाहिए -

सामान्य सूत्र

=SUMPRODUCT(--ISNUMBER(SEARCH(list,A1)))=0

सारांश

कई चीजों में से एक इनपुट को अस्वीकार करने के लिए, आप SEARCH फ़ंक्शन के आधार पर कस्टम डेटा सत्यापन नियम का उपयोग कर सकते हैं।

दिखाए गए उदाहरण में, B5: B11 पर लागू डेटा सत्यापन है:

=SUMPRODUCT(--ISNUMBER(SEARCH(list,B5)))=0

स्पष्टीकरण

उपयोगकर्ता द्वारा सेल मान जोड़ने या बदलने पर डेटा सत्यापन नियमों को ट्रिगर किया जाता है।

यह सूत्र नाम सीमा "सूची" में प्रत्येक मूल्य के लिए उपयोगकर्ता इनपुट का परीक्षण करने के लिए खोज समारोह का उपयोग करता है। खोज तर्क "समाहित" है - जब "सूची" से कोई मान प्राप्त होता है, तो SEARCH एक संख्या के रूप में मान की स्थिति लौटाता है। यदि नहीं मिला, तो SEARCH एक त्रुटि देता है।

ISNUMBER फ़ंक्शन तब संख्या को TRUE और त्रुटियों को FALSE में रूपांतरित करता है, और दोहरा नकारात्मक ऑपरेटर TRUE FALSE मानों को 1s और शून्य में बदलता है। चूंकि नामित सीमा "सूची" में 5 मान शामिल हैं, इसलिए हमें इस तरह से एक सरणी में 5 परिणाम प्राप्त होते हैं:

(0; 0; 0; 0; 0)

SUMPRODUCT तब सरणी में आइटमों को जमा करता है और परिणाम शून्य के खिलाफ परीक्षण किया जाता है। जब तक सभी आइटम शून्य हैं, SUMPRODUCT शून्य वापस कर देता है और सत्यापन सफल होता है। यदि SUMPRODUCT अन्य नंबर लौटाता है (यानी जब "सूची" में कोई आइटम पाया जाता है) तो सूत्र FALSE देता है और सत्यापन विफल हो जाता है।

नोट: डेटा सत्यापन फ़ार्मुलों में सेल संदर्भ सीमा के ऊपरी बाएं सेल के सापेक्ष हैं जब सत्यापन नियम परिभाषित किया गया है, इस मामले में बी 5।