सूत्र द्वारा क्रमबद्ध करें

यदि आपको सूची को क्रमबद्ध करने की आवश्यकता है, तो आपकी सेवा में बहुत सारे तरीके हैं, जिनमें से सबसे आसान टैब पर या मेनू में सॉर्ट बटन हैं जानकारी (डेटा - क्रमबद्ध करें). हालाँकि, ऐसी परिस्थितियाँ होती हैं जब सूची की छँटाई स्वचालित रूप से करने की आवश्यकता होती है, अर्थात सूत्र। यह आवश्यक हो सकता है, उदाहरण के लिए, ड्रॉप-डाउन सूची के लिए डेटा उत्पन्न करते समय, चार्ट के लिए डेटा की गणना करते समय, आदि। फ्लाई पर एक सूत्र के साथ सूची को कैसे क्रमबद्ध करें?

विधि 1. संख्यात्मक डेटा

यदि सूची में केवल संख्यात्मक जानकारी है, तो इसे आसानी से कार्यों का उपयोग करके क्रमबद्ध किया जा सकता है कम से कम (छोटा) и लाइन (पंक्ति):

समारोह कम से कम (छोटा) सरणी (कॉलम ए) से एक पंक्ति में एन-वें सबसे छोटा तत्व खींचता है। वे। SMALL(A:A;1) कॉलम में सबसे छोटी संख्या है, SMALL(A:A;2) दूसरी सबसे छोटी संख्या है, इत्यादि।

समारोह लाइन (पंक्ति) निर्दिष्ट सेल के लिए पंक्ति संख्या देता है, अर्थात ROW(A1)=1, ROW(A2)=2 आदि। इस मामले में, इसका उपयोग केवल n=1,2,3… संख्याओं के अनुक्रम के जनरेटर के रूप में किया जाता है। हमारी क्रमबद्ध सूची। उसी सफलता के साथ, एक अतिरिक्त कॉलम बनाना संभव था, इसे मैन्युअल रूप से संख्यात्मक अनुक्रम 1,2,3 से भरें ... और ROW फ़ंक्शन के बजाय इसे देखें।

विधि 2. पाठ सूची और नियमित सूत्र

यदि सूची में संख्याएँ नहीं हैं, लेकिन पाठ है, तो SMALL फ़ंक्शन अब काम नहीं करेगा, इसलिए आपको एक अलग, थोड़ा लंबा, पथ पर जाना होगा।

सबसे पहले, आइए एक सूत्र के साथ एक सेवा कॉलम जोड़ें जहां भविष्य में क्रमबद्ध सूची में प्रत्येक नाम की क्रम संख्या की गणना फ़ंक्शन का उपयोग करके की जाएगी COUNTIF (काउंटिफ):

अंग्रेजी संस्करण में यह होगा:

= COUNTIF(A:A,»<"&A1)+COUNTIF($A$1:A1,"="&A1)

पहला पद उन कक्षों की संख्या गिनने के लिए एक फलन है जो वर्तमान संख्या से कम हैं। दूसरा एक सुरक्षा जाल है यदि कोई नाम एक से अधिक बार आता है। तब उनके पास समान नहीं होगा, लेकिन क्रमिक रूप से बढ़ती संख्या होगी।

अब प्राप्त संख्याओं को आरोही क्रम में क्रमबद्ध रूप से व्यवस्थित किया जाना चाहिए। इसके लिए आप फंक्शन का इस्तेमाल कर सकते हैं कम से कम (छोटा) पहले रास्ते से:

खैर, अंत में, सूची से नामों को उनकी संख्या के आधार पर निकालना बाकी है। ऐसा करने के लिए, आप निम्न सूत्र का उपयोग कर सकते हैं:

समारोह अधिक उजागर (मिलान) वांछित सीरियल नंबर (1, 2, 3, आदि) के लिए कॉलम बी में खोज करता है और वास्तव में, उस लाइन की संख्या देता है जहां यह नंबर स्थित है। समारोह सूचकांक (अनुक्रमणिका) कॉलम ए से इस लाइन नंबर पर नाम निकालता है।

विधि 3: ऐरे फॉर्मूला

यह विधि, वास्तव में, विधि -2 के समान ही प्लेसमेंट एल्गोरिथ्म है, लेकिन एक सरणी सूत्र द्वारा कार्यान्वित किया जाता है। सूत्र को सरल बनाने के लिए, कक्षों की श्रेणी C1:C10 को नाम दिया गया था सूची (सेल चुनें, दबाएं Ctrl + F3 और बटन बनाएं):

सेल E1 में, हमारे फॉर्मूले को कॉपी करें:

= इंडेक्स (सूची; मैच (छोटा (काउंटिफ (सूची; "<" और सूची); पंक्ति (1: 1)); काउंटिफ (सूची; "<" और सूची); 0))

या अंग्रेजी संस्करण में:

= इंडेक्स (सूची, मैच (छोटा (काउंटिफ (सूची, «<"और सूची), पंक्ति (1: 1)), काउंटिफ (सूची, "<" और सूची), 0))

और धक्का Ctrl + Shift + Enterइसे एक सरणी सूत्र के रूप में दर्ज करने के लिए। फिर परिणामी सूत्र को सूची की पूरी लंबाई में कॉपी किया जा सकता है।

यदि आप चाहते हैं कि सूत्र एक निश्चित सीमा को ध्यान में न रखे, लेकिन सूची में नए तत्वों को जोड़ते समय समायोजित करने में सक्षम हो, तो आपको रणनीति को थोड़ा बदलना होगा।

सबसे पहले, सूची श्रेणी को गतिशील रूप से सेट करने की आवश्यकता होगी। ऐसा करने के लिए, बनाते समय, आपको एक निश्चित श्रेणी C3:C10 निर्दिष्ट करने की आवश्यकता नहीं है, लेकिन एक विशेष सूत्र जो सभी उपलब्ध मानों को संदर्भित करेगा, उनकी संख्या की परवाह किए बिना। क्लिक ऑल्ट + F3 या टैब खोलें सूत्र - नाम प्रबंधक (सूत्र - नाम प्रबंधक), एक नया नाम बनाएं और क्षेत्र में संपर्क (संदर्भ) निम्न सूत्र दर्ज करें (मुझे लगता है कि सॉर्ट किए जाने वाले डेटा की श्रेणी सेल C1 से शुरू होती है):

=СМЕЩ(C1;0;0;СЧЁТЗ(C1:C1000);1)

=ऑफसेट(C1,0,0,SCHÖTZ(C1:C1000),1)

दूसरे, उपरोक्त सरणी सूत्र को भविष्य में दर्ज किए गए अतिरिक्त डेटा की अपेक्षा के साथ - मार्जिन के साथ बढ़ाया जाना चाहिए। इस स्थिति में, सरणी सूत्र उन कक्षों पर #NUMBER त्रुटि देना शुरू कर देगा जो अभी तक नहीं भरे गए हैं। इसे इंटरसेप्ट करने के लिए, आप फ़ंक्शन का उपयोग कर सकते हैं IFERROR, जिसे हमारे सरणी सूत्र "चारों ओर" जोड़ने की आवश्यकता है:

=इफेरर (इंडेक्स (सूची; मैच (छोटा (काउंटिफ (सूची; "<" और सूची); पंक्ति (1: 1)); काउंटिफ (सूची; "<" और सूची); 0));»»)

=IFERROR(NDEX(सूची, MATCH(SMALL(COUNTIF(List, «<"&List), ROW(1:1)), COUNTIF(List, "<"&List), 0));"")

यह #NUMBER त्रुटि पकड़ता है और इसके बजाय एक शून्य (खाली उद्धरण) आउटपुट करता है।

:

• रंग के अनुसार श्रेणी क्रमबद्ध करें
• सरणी सूत्र क्या हैं और उनकी आवश्यकता क्यों है
• नए Office 365 में सॉर्ट सॉर्टिंग और डायनेमिक सरणियाँ