एक्सेल ट्यूटोरियल: एक तालिका क्वेरी करने के लिए सूत्र

विषय - सूची

संबंधित शॉर्टकट

इस वीडियो में, हम कुछ फ़ार्मुलों पर नज़र डालेंगे जिनका उपयोग आप किसी तालिका को क्वेरी करने के लिए कर सकते हैं।

क्योंकि तालिकाएँ संरचित संदर्भों का समर्थन करती हैं, आप मूल सूत्रों के साथ तालिका के बारे में बहुत कुछ सीख सकते हैं।

इस शीट पर, तालिका 1 में कर्मचारी डेटा है। चलो कुछ उदाहरणों के माध्यम से चलते हैं।

प्रारंभ करने के लिए, आप तालिका पंक्तियों को गिनने के लिए ROWS फ़ंक्शन का उपयोग कर सकते हैं। यह केवल डेटा पंक्तियों की गिनती है। आप देख सकते हैं कि सूची में हम 19 लोग हैं।

= ROWS (Table1)

कॉलम के लिए COLUMNS फ़ंक्शन समान करेगा।

= रंग (तालिका 1)

तालिका कोशिकाओं की कुल संख्या प्राप्त करने के लिए, आप दोनों फ़ंक्शन के साथ एक सूत्र का उपयोग कर सकते हैं।

= ROWS (Table1) * रंग (Table1)

खाली कोशिकाओं को गिनने के लिए आप COUNTBLANK जैसे फ़ंक्शन का उपयोग कर सकते हैं।

= काउंटी (तालिका 1)

दृश्यमान पंक्तियों को गिनने के लिए, आपको एक स्तंभ का संदर्भ देने वाले SUBTOTAL फ़ंक्शन का उपयोग करना होगा जिसमें खाली कक्ष नहीं हैं।

इस स्थिति में, आईडी एक आवश्यक मूल्य है, मैं एक संदर्भ के लिए फ़ंक्शन नंबर और आईडी कॉलम के रूप में 103 का उपयोग करता हूं।

= सब्बल (103, टेबल 1 (आईडी))

103 नंबर SUBTOTAL को केवल दृश्य पंक्तियों में मान गिनने के लिए कहता है।

अब अगर मैं टेबल को फ़िल्टर करता हूं, तो दृश्यमान पंक्ति की संख्या नीचे जाती है, और जब मैं फ़िल्टर को साफ़ करता हूं, तो यह वापस ऊपर जाता है।

सबटोटल अक्सर तालिकाओं के साथ दिखाई देता है क्योंकि यह फ़िल्टर्ड पंक्तियों को बाहर करता है।

इस कार्यपुस्तिका में शीट 3 में SUBTOTAL के साथ उपलब्ध कार्यों की पूरी सूची है, और हमारी साइट पर अधिक जानकारी के लिए लिंक है।

कुल पंक्ति से एक मान प्राप्त करने के लिए, आप # स्पेसल स्पेसियर का उपयोग कर सकते हैं। बस बिंदु और क्लिक करें।

= तालिका 1 ((# कुल), (समूह)

यदि कुलीन पंक्ति दिखाई नहीं देती है, तो Excel एक #REF त्रुटि लौटाएगा।

आप IFERROR के साथ इस त्रुटि को फंसा सकते हैं, और कुल पंक्ति अक्षम होने पर एक रिक्त स्ट्रिंग लौटा सकते हैं।

= IFERROR (Table1 ((# कुल), (समूह)), "")

संख्यात्मक डेटा वाले कॉलम में, आरंभ तिथि कॉलम की तरह, आप जल्द से जल्द और नवीनतम तारीखों को प्राप्त करने के लिए MIN और MAX का उपयोग कर सकते हैं।

= MIN (Table1 (प्रारंभ))
= MAX (तालिका 1 (प्रारंभ))

यदि आप चाहते हैं कि ये फ़िल्टर पर प्रतिक्रिया दें, तो 105 और 104 के साथ SUBTOTAL फ़ंक्शन का उपयोग करें।

= सबोटल (105, टेबल 1 (स्टार्ट)) - मिनट
= पब्लिक (104, टेबल 1 (स्टार्ट)) - अधिकतम

COUNTIF और SUMIF जैसे कार्य भी तालिकाओं के साथ अच्छी तरह से काम करते हैं। मैं आसानी से COUNTIF के साथ प्रत्येक समूह की गिनती प्राप्त कर सकता हूं।

= COUNTIF (Table1 (समूह), I17)

हमेशा की तरह, डेटा के लिए तालिका का उपयोग करने का सबसे अच्छा हिस्सा यह है कि सीमा गतिशील है। जब मैं अधिक डेटा में पेस्ट करता हूं, तो सभी फॉर्मूले तुरंत अप टू डेट हो जाते हैं।