सामान्य सूत्र
=COUNTIF(data,"<="&A1)+(COUNT(data)*ISTEXT(A1))
सारांश
वर्णमाला क्रम में दोनों संख्याओं और पाठ के साथ डेटा को गतिशील रूप से क्रमबद्ध करने के लिए आप एक हेल्पर कॉलम में संख्यात्मक रैंक उत्पन्न करने के लिए सूत्र का उपयोग कर सकते हैं, फिर रैंक के आधार पर मान प्रदर्शित करने के लिए INDEX और MATCH का उपयोग कर सकते हैं। C5 में सूत्र दिखाया गया है:
=COUNTIF(data,"<="&B5)+(COUNT(data)*ISTEXT(B5))
जहां "डेटा" नाम B5: B13 है।
स्पष्टीकरण
यह सूत्र पहले COUNTIF पर आधारित अभिव्यक्ति का उपयोग करके रैंक मान उत्पन्न करता है:
=COUNTIF(data,"<="&B5)
जिसे यहाँ और अधिक विस्तार से समझाया गया है। यदि डेटा में सभी पाठ मान, या सभी संख्यात्मक मान शामिल हैं, तो रैंक सही होगा। हालाँकि, यदि डेटा में पाठ और संख्या दोनों शामिल हैं, तो हमें संख्यात्मक मानों को ध्यान में रखते हुए सभी पाठ मानों की रैंक "शिफ्ट" करने की आवश्यकता है। यह यहाँ सूत्र के दूसरे भाग के साथ किया गया है:
+(COUNT(data)*ISTEXT(B7))
यहां, हम डेटा में संख्यात्मक मानों की एक संख्या प्राप्त करने के लिए COUNT फ़ंक्शन का उपयोग करते हैं, फिर परिणाम को ISTEXT के तार्किक परिणाम से गुणा करते हैं, जो परीक्षण करता है कि क्या मूल्य पाठ है और TRUE या FALSE देता है। जब हम वर्तमान पंक्ति में एक संख्या के साथ काम कर रहे हैं तो यह प्रभावी रूप से COUNT परिणाम को रद्द कर देता है।
डुप्लिकेट को संभालना
यदि डेटा में डुप्लिकेट होते हैं, तो एक से अधिक बार दिखाई देने वाले मानों के लिए अनुक्रमिक रैंक प्रदान करने के लिए सूत्र को नीचे दिखाया जा सकता है:
=COUNTIF(data,"<"&B5)+(COUNT(data)*ISTEXT(B5))+COUNTIF($B$5:B5,B5)
यह संस्करण प्रारंभिक COUNTIF फ़ंक्शन के तर्क को समायोजित करता है, और डुप्लिकेट को बढ़ाने के लिए एक विस्तृत संदर्भ के साथ एक और COUNTIF जोड़ता है।
क्रमबद्ध मान प्रदर्शित करें
गणना किए गए रैंक मान का उपयोग करके वर्णमाला के क्रम में मानों को पुनः प्राप्त करने और प्रदर्शित करने के लिए, E5 में निम्नलिखित INDEX और MATCH विवरण शामिल हैं:
=INDEX(data,MATCH(ROWS($E$5:E5),rank,0))
जहाँ "डेटा" नाम की श्रेणी B5: B13 है, और "रैंक" नाम की श्रेणी C5: C13 है।
यह सूत्र कैसे काम करता है, इसके बारे में अधिक जानकारी के लिए, यहां उदाहरण देखें।
खाली से निपटना
खाली कोशिकाएँ शून्य की रैंक उत्पन्न करेंगी। मान लें कि आप रिक्त कक्षों को अनदेखा करना चाहते हैं, तो यह ठीक काम करता है क्योंकि ऊपर INDEX और MATCH सूत्र 1 से शुरू होता है। हालाँकि, आपको सॉर्ट किए गए मानों के अंत में # N / A त्रुटियां दिखाई देंगी, प्रत्येक खाली सेल के लिए एक। इसे संभालने का एक आसान तरीका यह है कि IFERROR में INDEX और MATCH सूत्र को इस तरह से लपेटें:
=IFERROR(INDEX(data,MATCH(ROWS($E$5:E5),rank,0)),"")
