एक्सेल में काम करते समय हमें अक्सर मल्टी लेवल हायरार्की (Multi Level Hierarchy) बनाने की आवश्यकता होती है। मल्टी लेवल हायरार्की का उपयोग करके हम अपने डेटा को सरल और संक्षिप्त प्रारूप में प्रस्तुत कर सकते हैं। माइक्रोसॉफ्ट एक्सेल एक शक्तिशाली और बहुमुखी सॉफ्टवेयर है। एक्सेल का उपयोग करके हम कुछ आसान चरणों का पालन करके मल्टी लेवल हायरार्की बना सकते हैं। इस आर्टिकल में हम एक्सेल में मल्टी लेवल हायरार्की बनाने के 2 सरल तरीकों के बारे में जानेंगे।
एक्सेल में मल्टी लेवल हायरार्की बनाने के 2 सरल तरीके
2 तरीकों में से पहले तरीके में हम एक्सेल के डेटा वैलिडेशन (Data Validation) फीचर का उपयोग करेंगे। और दूसरे तरीके में हम एक्सेल के पावर पिवट ऐड-इन (Power Pivot Add-in) का उपयोग करेंगे।
बता दें कि हमने इस आर्टिकल के लिए माइक्रोसॉफ्ट एक्सेल 365 वर्जन का उपयोग किया है, आप अपनी सुविधा के अनुसार कोई अन्य वर्जन भी उपयोग कर सकते हैं।
1. डेटा वैलिडेशन फीचर का उपयोग करके मल्टी लेवल हायरार्की बनाएं
डेटा वैलिडेशन फीचर का उपयोग करना एक्सेल में मल्टी-लेवल हायरार्की बनाने के सबसे आसान तरीकों में से एक है। निम्नलिखित डेटासेट में हमारे पास एक सिनेप्लेक्स के कुछ चल रहे शो के लिए मूवी नाम (Movie Name), शो शेड्यूल (Show Schedule), और सीट टाइप (Seat Type) हैं। हमारा उद्देश्य इस डेटा का उपयोग करके मल्टी-लेवल हायरार्की बनाना है। आइए नीचे बताए गए चरणों का पालन करके इसे करें।

चरण 01: मूवी नाम कॉलम के लिए डेटा वैलिडेशन ऑप्शन एडिट करना
मूवी नाम कॉलम में हमें एक ड्रॉप-डाउन लिस्ट बनाने की आवश्यकता है ताकि हम अपनी पसंद की कोई भी मूवी चुन सकें। यह करना काफी सीधा है। बस साथ चलें।
- सबसे पहले मूवी नाम कॉलम के तहत सेल B5 को सिलेक्ट करें।
- उसके बाद रिबन से डेटा (Data) टैब पर जाएं।
- फिर डेटा टूल्स (Data Tools) ग्रुप से डेटा वैलिडेशन (Data Validation) ऑप्शन पर क्लिक करें।

नतीजतन डेटा वैलिडेशन डायलॉग बॉक्स खुलेगा जैसा निम्न तस्वीर में दिखाया गया है।

- अब नीचे दी गई छवि में मार्क किए गए ड्रॉप-डाउन आइकन पर क्लिक करें।
- उसके बाद ड्रॉप-डाउन से लिस्ट (List) चुनें।

- अब सोर्स (Source) बॉक्स पर क्लिक करें और निम्नलिखित फॉर्मूला दर्ज करें।
='Dataset 1 '!$B$5:$B$10यहां $B$5:$B$10 दिए गए डेटासेट के मूवी नाम कॉलम के तहत सेल्स की रेंज है।
नोट: यहां हमने एब्सोल्यूट सेल रेफरेंस (Absolute Cell Reference) का उपयोग किया है क्योंकि प्रत्येक सेल में हमें सभी मूवी नाम दिखाने की आवश्यकता है ताकि उनमें से एक को चुना जा सके।

- उसके बाद OK पर क्लिक करें।

- नतीजतन सेल B5 के बगल में एक ड्रॉप-डाउन आइकन उपलब्ध होगा जैसा निम्न तस्वीर में मार्क किया गया है। उस पर क्लिक करें।
परिणामस्वरूप ड्रॉप-डाउन लिस्ट में आप अपनी पसंद की कोई भी मूवी चुन सकते हैं।

चरण 02: मूवी नाम कॉलम में शेष सेल्स में वैलिडेशन फीचर पेस्ट करना
यहां हम एक्सेल के पेस्ट स्पेशल (Paste Special) फीचर का उपयोग करके सेल B5 के डेटा वैलिडेशन फीचर को मूवी नाम नामक कॉलम के अन्य सेल्स में पेस्ट करेंगे।
- सबसे पहले सेल B5 को सिलेक्ट करें और इसे कॉपी करने के लिए CTRL + C दबाएं।

- उसके बाद मूवी नाम कॉलम के शेष सेल्स को सिलेक्ट करें।
- उसके बाद रिबन से होम (Home) टैब पर जाएं।
- अब पेस्ट (Paste) ऑप्शन पर क्लिक करें।
- अब ड्रॉप-डाउन से पेस्ट स्पेशल (Paste Special) ऑप्शन चुनें।

- उसके बाद पेस्ट स्पेशल डायलॉग बॉक्स से नीचे दी गई छवि में मार्क किए अनुसार वैलिडेशन (Validation) ऑप्शन चुनें।
- फिर OK पर क्लिक करें।

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

चरण 03: शो शेड्यूल कॉलम के लिए डेटा वैलिडेशन ऑप्शन एडिट करना
अब हमें मूवी नाम कॉलम में चयन के आधार पर एक ड्रॉप-डाउन लिस्ट बनाने की आवश्यकता है। उदाहरण के लिए मूवी इनटू द डीप (Into the Deep) के दिन में 5 अलग-अलग समय पर शो उपलब्ध हैं। लेकिन मूवी बुलेट ट्रेन (Bullet Train) केवल दिन के 3 अलग-अलग समय पर उपलब्ध है। इसलिए शो शेड्यूल कॉलम में उपलब्ध विकल्प मूवी नाम कॉलम में चुनी गई मूवी पर निर्भर करेगा। इसे करने के लिए हम OFFSET फंक्शन और MATCH फंक्शन का उपयोग करने जा रहे हैं। आइए नीचे बताए गए चरणों का पालन करें।
- सबसे पहले सेल C5 को सिलेक्ट करें और चरण 01 में बताए गए चरणों का पालन करें ताकि निम्नलिखित आउटपुट प्राप्त हो सके।

- उसके बाद सोर्स बॉक्स (Source Box) में निम्नलिखित फॉर्मूला दर्ज करें।
=OFFSET('Dataset 1 '!$B$14,1,MATCH($B5,'Dataset 1 '!$B$14:$G$14,0)-1,5,1)यहां सेल $B$14 डेटासेट 1 के शो शेड्यूल के हेडर (Header) को संदर्भित करता है, सेल $B5 मूवी नाम कॉलम के तहत चुनी गई मूवी को दर्शाता है और रेंज $B$14:$G$14 वर्कशीट डेटासेट 1 में मूवी के नाम की सरणी (Array) है।
फॉर्मूला ब्रेकडाउन
- MATCH($B5,'Dataset 1 '!$B$14:$G$14,0) → यह एक पंक्ति, कॉलम या टेबल में लुकअप मान की स्थिति लौटाता है।
- $B5 → लुकअप_वैल्यू (lookup_value) आर्गुमेंट है
- 'Dataset 1 '!$B$14:$G$14 → लुकअप_एरे (lookup_array) आर्गुमेंट है
- 0 → मैच_टाइप (match_type) आर्गुमेंट है
- आउटपुट → 1
- OFFSET('Dataset 1 '!$B$14,1,MATCH($B5,'Dataset 1 '!$B$14:$G$14,0)-1,5,1) → यह OFFSET('Dataset 1 '!$B$14,1,1-1,5,1) बन जाता है
- 'Dataset 1 '!$B$14 → रेफरेंस (reference) आर्गुमेंट है
- 1 → रोज़ (rows) आर्गुमेंट है
- 1-1 → कॉलम्स (cols) आर्गुमेंट है
- 5 → हाइट (height) आर्गुमेंट है
- 1 → विड्थ (width) आर्गुमेंट है
- आउटपुट → {11:00 AM, 3:30 PM, 6:00 PM, 9:30 PM, 11:00 PM}
- फिर OK पर क्लिक करें।

- परिणामस्वरूप सेल C5 के बगल में एक ड्रॉप-डाउन आइकन दिखाई देगा जैसा नीचे दी गई छवि में दिखाया गया है। उस पर क्लिक करें।
नतीजतन आप मूवी इनटू द डीप के लिए उपलब्ध शो शेड्यूल देखेंगे।

चरण 04: शो शेड्यूल कॉलम में शेष सेल्स में वैलिडेशन फीचर पेस्ट करना
अब हम एक्सेल के पेस्ट स्पेशल फीचर का उपयोग करके सेल C5 के डेटा वैलिडेशन गुणों को शेष सेल्स में कॉपी करेंगे।
- अब सेल C5 को सिलेक्ट करें और चरण 02 में बताए गए चरणों का पालन करें।
- उसके बाद मूवी बुलेट ट्रेन चुनें, आप देख सकते हैं कि हमारे दिए गए डेटासेट की तरह शो शेड्यूल के लिए केवल 3 उपलब्ध विकल्प हैं।

- इसी तरह आप अन्य मूवीज के लिए शो शेड्यूल चुन सकते हैं और नीचे प्रदर्शित छवि जैसा आउटपुट प्राप्त कर सकते हैं।

चरण 05: सीट टाइप कॉलम के लिए डेटा वैलिडेशन ऑप्शन एडिट करना
अब हमें शो शेड्यूल के हमारे चयन के आधार पर सीट टाइप (Seat Type) कॉलम में एक और ड्रॉप-डाउन लिस्ट बनाने की आवश्यकता है। डेटासेट में हम देख सकते हैं कि सभी शो शेड्यूल के समान सीट टाइप नहीं हैं। उदाहरण के लिए 11:00 AM के शो के लिए सभी 3 सीट टाइप उपलब्ध हैं लेकिन 6:00 PM के शो के लिए केवल रेगुलर (Regular) और VIP सीट टाइप उपलब्ध हैं। इस डिपेंडेंट ड्रॉप-डाउन लिस्ट को बनाने के लिए हम फिर से OFFSET फंक्शन और MATCH फंक्शन का उपयोग करेंगे। आइए नीचे चर्चा किए गए चरणों का उपयोग करके इसे करें।
- सबसे पहले सेल D5 को सिलेक्ट करें और चरण 01 में बताए गए चरणों का पालन करें ताकि निम्नलिखित आउटपुट प्राप्त हो सके।

- उसके बाद डेटा वैलिडेशन डायलॉग बॉक्स में सोर्स बॉक्स (Source Box) में निम्नलिखित फॉर्मूला दर्ज करें।
=OFFSET('Dataset 1 '!$B$23,1,MATCH($C5,'Dataset 1 '!$B$23:$F$23,0)-1,3,1)यहां सेल $B$23 डेटासेट 1 में सीट टाइप के हेडर (Header) को संदर्भित करता है, सेल $C5 चुने गए शो शेड्यूल को दर्शाता है और रेंज $B$23:$F$23 वर्कशीट डेटासेट 1 में शो शेड्यूल की सरणी (Array) है।
फॉर्मूला ब्रेकडाउन
- MATCH($C5,'Dataset 1 '!$B$23:$F$23,0) → यह एक पंक्ति, कॉलम या टेबल में लुकअप मान की स्थिति लौटाता है।
- $C5 → लुकअप_वैल्यू (lookup_value) आर्गुमेंट है
- 'Dataset 1 '!$B$23:$F$23 → लुकअप_एरे (lookup_array) आर्गुमेंट है
- 0 → मैच_टाइप (match_type) आर्गुमेंट है
- आउटपुट → 3
- OFFSET('Dataset 1 '!$B$23,1,MATCH($C5,'Dataset 1 '!$B$23:$F$23,0)-1,3,1) → यह OFFSET('Dataset 1 '!$B$23,1,3-1,3,1) बन जाता है।
- 'Dataset 1 '!$B$23 → रेफरेंस (reference) आर्गुमेंट है
- 1 → रोज़ (rows) आर्गुमेंट है
- 3-1 → कॉलम्स (cols) आर्गुमेंट है
- 3 → हाइट (height) आर्गुमेंट है
- 1 → विड्थ (width) आर्गुमेंट है
- आउटपुट → {रेगुलर, VIP}
- उसके बाद OK पर क्लिक करें।

नतीजतन आप सेल D5 के बगल में एक ड्रॉप-डाउन आइकन देखेंगे। चूंकि चुना गया शो शेड्यूल 6:00 PM है इसलिए सेल D5 में रेगुलर और VIP विकल्प उपलब्ध हैं।

चरण 06: सीट टाइप कॉलम में शेष सेल्स में वैलिडेशन फीचर पेस्ट करना
इस चरण में हम सेल D5 के डेटा वैलिडेशन गुणों को सीट टाइप कॉलम के अन्य सेल्स में कॉपी करेंगे। आइए नीचे चर्चा किए गए चरणों का पालन करें।
- सबसे पहले सेल D5 को सिलेक्ट करें और चरण 02 में बताए गए चरणों का पालन करें।
- अब सेल C6 के बगल में ड्रॉप-डाउन आइकन पर क्लिक करें। चूंकि चुना गया शो शेड्यूल 11:00 AM है इसलिए आप देख सकते हैं कि सभी 3 सीट टाइप उपलब्ध हैं।

- इसी तरह अन्य सेल्स के लिए सीट टाइप विकल्प चुनें और आप निम्न तस्वीर जैसा आउटपुट प्राप्त करेंगे।

और पढ़ें: एक्सेल में हायरार्की कैसे बनाएं (3 आसान तरीके)
2. एक्सेल में मल्टी लेवल हायरार्की बनाने के लिए पावर पिवट का उपयोग
पावर पिवट (Power Pivot) फीचर का उपयोग करना एक्सेल में मल्टी-लेवल हायरार्की बनाने का एक और कुशल तरीका है। निम्नलिखित डेटासेट में हमारे पास यूएसए (USA) के विभिन्न राज्यों (States) में अलग-अलग शहरों (Cities) से बिक्री (Sales) डेटा की एक टेबल है। हम इस टेबल का उपयोग करके मल्टी-लेवल हायरार्की बनाएंगे। आइए नीचे चर्चा की गई प्रक्रिया का पालन करें।

चरण 01: पावर पिवट विंडो में टेबल जोड़ना
यदि आपके पास पावर पिवट ऐड-इन (Power Pivot Add-in) इनेबल नहीं है तो इसे इनेबल करने के लिए यहां बताए गए चरणों का पालन करें। पावर पिवट ऐड-इन इनेबल करने के बाद नीचे दिए गए चरणों का उपयोग करें।
- सबसे पहले पूरी टेबल को सिलेक्ट करें और उसके बाद इसे कॉपी करने के लिए CTRL + C दबाएं।

- अब रिबन से पावर पिवट (Power Pivot) टैब पर जाएं।
- फिर निम्नलिखित छवि में मार्क किए अनुसार मैनेज (Manage) ऑप्शन पर क्लिक करें।

परिणामस्वरूप नीचे दी गई तस्वीर की तरह एक खाली पावर पिवट विंडो (Power Pivot Window) खुलेगी।

- अब पावर पिवट विंडो में क्लिपबोर्ड (Clipboard) ऑप्शन पर क्लिक करें।
- उसके बाद ड्रॉप-डाउन से पेस्ट (Paste) ऑप्शन चुनें।

नतीजतन पेस्ट प्रीव्यू (Paste Preview) डायलॉग बॉक्स उपलब्ध होगा।

- उसके बाद पेस्ट प्रीव्यू डायलॉग बॉक्स से OK पर क्लिक करें।

परिणामस्वरूप आपकी टेबल निम्नलिखित छवि में दिखाए अनुसार पावर पिवट विंडो में जुड़ जाएगी।

चरण 02: डायग्राम व्यू में हायरार्की बनाना
इस चरण में हम एक हायरार्की बनाएंगे जो राज्यों के अंतर्गत शहरों और देश के अंतर्गत राज्यों का वर्णन करती है।
- सबसे पहले पावर पिवट विंडो के व्यू (View) टैब से डायग्राम व्यू (Diagram View) ऑप्शन पर क्लिक करें।

नतीजतन निम्नलिखित छवि में प्रदर्शित अनुसार आपके स्क्रीन पर डायग्राम व्यू दिखाई देगा।

- अब नीचे दी गई छवि में दिए गए मार्क किए गए हिस्से पर क्लिक करें।

परिणामस्वरूप एक हायरार्की (Hierarchy) बन जाएगी।

- अब देश (Country) नाम के कॉलम को ड्रैग करके नई बनी हायरार्की में ड्रॉप करें।

- इसी तरह अन्य कॉलम्स को क्रमिक रूप से ड्रैग और ड्रॉप करें और आप निम्नलिखित आउटपुट देखेंगे।

चरण 03: पिवट टेबल बनाना
- उसके बाद पिवट टेबल (Pivot Table) ऑप्शन पर क्लिक करें।

- नतीजतन क्रिएट पिवट टेबल (Create Pivot Table) डायलॉग बॉक्स उपलब्ध होगा और OK पर क्लिक करें।

परिणामस्वरूप निम्नलिखित तस्वीर में दिखाए अनुसार एक नई वर्कशीट बन जाएगी।

- उसके बाद पिवट टेबल फील्ड्स (Pivot Table Fields) डायलॉग बॉक्स से हायरार्की1 (Hierarchy1) को रोज़ (Rows) सेक्शन में और बिक्री (Sales) को वैल्यूज़ का योग (Sum of Values) सेक्शन में ड्रैग और ड्रॉप करें।

नतीजतन आपकी वर्कशीट में निम्नलिखित पिवट टेबल प्राप्त होगी।

- अब निम्नलिखित तस्वीर में मार्क किए अनुसार + साइन पर क्लिक करें।

परिणामस्वरूप आप निम्नलिखित छवि में दिखाए अनुसार राज्यों और प्रत्येक राज्य के बिक्री का योग देखेंगे।

- उसके बाद फिर से अरिज़ोना (Arizona) राज्य के नाम के बगल में + साइन पर क्लिक करें।

यह अरिज़ोना राज्य के शहरों को उनकी बिक्री के साथ दिखाएगा।

- इसी तरह अन्य राज्यों के बगल में + आइकन पर क्लिक करके आप निम्नलिखित आउटपुट प्राप्त कर सकते हैं।

और पढ़ें: एक्सेल पिवट टेबल में हायरार्की कैसे बनाएं (आसान चरणों के साथ)
प्रैक्टिस सेक्शन
एक्सेल वर्कबुक में हमने प्रत्येक वर्कशीट के दाईं ओर एक प्रैक्टिस सेक्शन (Practice Section) प्रदान किया है। कृपया इसे स्वयं अभ्यास करें।

निष्कर्ष
आज के सत्र के बारे में बस इतना ही। मुझे दृढ़ विश्वास है कि यह आर्टिकल आपको एक्सेल में मल्टी-लेवल हायरार्की बनाने में मार्गदर्शन करने में सक्षम था। यदि आपके पास आर्टिकल की गुणवत्ता में सुधार के लिए कोई प्रश्न या सुझाव हैं तो कृपया बेझिझक कमेंट छोड़ें। एक्सेल के बारे में अधिक जानने के लिए आप हमारी वेबसाइट एक्सेलडेमि (ExcelDemy) पर जा सकते हैं। हैप्पी लर्निंग!
संबंधित आर्टिकल्स
- एक्सेल में हायरार्की चार्ट कैसे बनाएं (3 आसान तरीके)
- एक्सेल पिवट टेबल में डेट हायरार्की बनाएं (आसान चरणों के साथ)
- एक्सेल में स्मार्टआर्ट हायरार्की का उपयोग कैसे करें (आसान चरणों के साथ)
- एक्सेल में रो हायरार्की जोड़ें (2 आसान तरीके)