कला-संपत्ति मात्रा मैट्रिक्स को प्रत्येक आइटम के लिए एक वर्कशीट पंक्ति में विस्तारित करने के लिए, पहले प्रत्येक संपत्ति–फ़ॉर्मेट संयोजन को संचयी मात्रा के साथ सूचीबद्ध करें। फिर SEQUENCE और XLOOKUP का उपयोग करके उसके लेबल को आवश्यक संख्या में दोहराएँ। इस निर्देशिका में कृत्रिम कलाकृति लेबल उपयोग किए गए हैं।

काम सीधा है: एक संक्षिप्त योजना ग्रिड को आइटम सूची में बदलना। दो की मात्रा से समान संपत्ति और फ़ॉर्मेट वाली दो पंक्तियाँ बनती हैं; शून्य से कोई पंक्ति नहीं बनती। नीचे दिया गया उदाहरण मध्यवर्ती गणनाएँ दिखाता है, ताकि आप परिणाम को अनुकूलित करने से पहले जाँच सकें।

Excel के Mac संस्करणों में यह तरीका उपलब्ध है?

Mac के लिए Microsoft 365 का Excel, Mac के लिए Excel 2024 या Mac के लिए Excel 2021 उपयोग करें। Microsoft के दस्तावेज़ों में इन संस्करणों को SEQUENCE और XLOOKUP दोनों के लिए सूचीबद्ध किया गया है, और Mac के लिए Excel 2021 की सुविधा मार्गदर्शिका में डायनामिक ऐरे का स्पष्ट उल्लेख है। SEQUENCE फ़ंक्शन, XLOOKUP फ़ंक्शन, Mac के लिए Excel 2021 में नया क्या है।

दूसरे निर्देशों से फ़ॉर्मूले कॉपी करते समय फ़ंक्शन की उपलब्धता मायने रखती है। उदाहरण के लिए, Microsoft Mac पर Microsoft 365 और Excel 2024 के लिए VSTACK को सूचीबद्ध करता है, लेकिन Excel 2021 के लिए नहीं। यहाँ बताया गया तरीका इस निर्भरता से बचता है। Microsoft यह भी स्पष्ट रूप से कहता है कि Excel 2016 और Excel 2019 में XLOOKUP उपलब्ध नहीं है, इसलिए उन संस्करणों में नीचे दिए गए फ़ॉर्मूले बिना बदलाव के नहीं चलेंगे। VSTACK फ़ंक्शन, XLOOKUP फ़ंक्शन।

कला-संपत्ति मात्रा मैट्रिक्स सेट अप करें

एक खाली वर्कशीट पर, नीचे दिया गया कृत्रिम डेटा A1:C4 में दर्ज करें। पहले कॉलम में संपत्ति के लेबल हैं, हेडर पंक्ति में फ़ॉर्मेट हैं और बाकी सेल में मात्राएँ हैं।

संपत्ति छोटा बड़ा
आर्क टाइल 2 0
डॉट टाइल 1 0
वेव टाइल 2 1

इस उदाहरण में, हर मात्रा शून्य या उससे अधिक की संख्यात्मक पूर्ण संख्या होनी चाहिए। जब किसी आइटम की आवश्यकता न हो, तो स्पष्ट रूप से शून्य दर्ज करें। आगे बढ़ने से पहले खाली सेल, टेक्स्ट के रूप में दर्ज मात्राएँ, ऋणात्मक मान, भिन्न और सेल त्रुटियाँ ठीक करें। ये फ़ॉर्मूले मान्य गणनाएँ मानकर चलते हैं; वे उन्हें सत्यापित या पूर्णांक में नहीं बदलते।

अपेक्षित आउटपुट में छह आइटम पंक्तियाँ हैं: दो छोटे आर्क टाइल, एक छोटा डॉट टाइल, दो छोटे वेव टाइल और एक बड़ा वेव टाइल। लेबल का दोहराव जानबूझकर है, क्योंकि हर पंक्ति एक आइटम दर्शाती है। फ़ॉर्मूले वर्कशीट रिकॉर्ड बनाते हैं; वे कलाकृति फ़ाइलें नहीं बनाते या उनकी प्रतिलिपि नहीं बनाते।

मैट्रिक्स को जाँचने योग्य सहायक सूची में बदलें

कॉलम D खाली छोड़ें। E1:I1 में Position, Asset, Format, Quantity और End item हेडर दर्ज करें। E2:E7 में क्रम से एक से छह तक संख्याएँ दर्ज करें। सहायक सूची में छह पंक्तियाँ हैं, क्योंकि तीन संपत्तियों को दो फ़ॉर्मेट से गुणा करने पर छह संयोजन बनते हैं।

सहायक सूची स्रोत की हर पंक्ति में बाएँ से दाएँ पढ़ती है, फिर अगली पंक्ति पर जाती है: आर्क टाइल छोटा, आर्क टाइल बड़ा, फिर डॉट टाइल के दो संयोजन, और उसके बाद वेव टाइल के दो संयोजन। इस क्रम को एकसमान रखना आवश्यक है।

हर संयोजन के लेबल और मात्रा प्राप्त करें

यह फ़ॉर्मूला F2 में दर्ज करें:

=INDEX($A$2:$A$4,INT((E2-1)/2)+1)

यह फ़ॉर्मूला G2 में दर्ज करें:

=INDEX($B$1:$C$1,1,MOD(E2-1,2)+1)

यह फ़ॉर्मूला H2 में दर्ज करें:

=INDEX($B$2:$C$4,INT((E2-1)/2)+1,MOD(E2-1,2)+1)

F2:H2 को कॉपी करके पंक्ति सात तक भरें। ये सामान्य सेल फ़ॉर्मूले हैं; हर सेल में एक परिणाम आता है। INDEX किसी निर्दिष्ट पंक्ति और कॉलम से मान प्राप्त करता है। यहाँ, INT हर दो स्थानों के बाद स्रोत पंक्ति को आगे बढ़ाता है, जबकि MOD स्रोत कॉलम को दोनों फ़ॉर्मेट के बीच बारी-बारी से बदलता है। INDEX फ़ंक्शन, INT फ़ंक्शन, MOD फ़ंक्शन।

हर संयोजन की अंतिम आइटम संख्या निकालें

I2 में यह फ़ॉर्मूला दर्ज करें और उसे कॉपी करके I7 तक भरें:

=SUM($H$2:H2)

स्थिर शुरुआती संदर्भ और आगे बढ़ता अंतिम संदर्भ मिलकर रनिंग टोटल बनाते हैं। SUM पहली सहायक पंक्ति से मौजूदा पंक्ति तक की मात्राएँ जोड़ता है। SUM फ़ंक्शन।

आपकी पूरी सहायक सूची इस तरह दिखनी चाहिए:

स्थान संपत्ति फ़ॉर्मेट मात्रा अंतिम आइटम
1 आर्क टाइल छोटा 2 2
2 आर्क टाइल बड़ा 0 2
3 डॉट टाइल छोटा 1 3
4 डॉट टाइल बड़ा 0 3
5 वेव टाइल छोटा 2 5
6 वेव टाइल बड़ा 1 6

End item को एक सीमा के रूप में समझें। पहला संयोजन आइटम संख्या एक और दो में आता है। तीसरा संयोजन आइटम संख्या तीन में आता है। पाँचवाँ संयोजन आइटम संख्या चार और पाँच में आता है, और छठा आइटम संख्या छह में। शून्य मात्रा से सीमा आगे नहीं बढ़ती, इसलिए उसे कोई आइटम संख्या नहीं मिलती।

हर आइटम के लिए एक आउटपुट पंक्ति बनाएँ

K1 में Asset दर्ज करें; L1 में Format दर्ज करें। दोनों हेडर के नीचे के सेल खाली रखें। आउटपुट को Excel तालिका के बाहर सामान्य वर्कशीट सेल में रखें: Microsoft के दस्तावेज़ों के अनुसार, तालिकाओं के भीतर स्पिल होने वाले ऐरे फ़ॉर्मूले समर्थित नहीं हैं। डायनामिक ऐरे फ़ॉर्मूले और स्पिल होने वाले ऐरे का व्यवहार।

यह पूरा फ़ॉर्मूला K2 में दर्ज करें:

=IF(SUM($H$2:$H$7)=0,"",XLOOKUP(SEQUENCE(SUM($H$2:$H$7)),$I$2:$I$7,$F$2:$F$7,,1,1))

यह पूरा फ़ॉर्मूला L2 में दर्ज करें:

=IF(SUM($H$2:$H$7)=0,"",XLOOKUP(SEQUENCE(SUM($H$2:$H$7)),$I$2:$I$7,$G$2:$G$7,,1,1))

हर फ़ॉर्मूले को Return दबाकर पुष्टि करें। इन दोनों फ़ॉर्मूलों को नीचे कॉपी न करें: उनके ऐरे परिणाम अपने-आप आस-पास की पंक्तियों में भर जाते हैं। Microsoft इस स्वचालित विस्तार को स्पिल होना कहता है। डायनामिक ऐरे फ़ॉर्मूले और स्पिल होने वाले ऐरे का व्यवहार।

SEQUENCE कुल मात्रा के अनुसार एक से शुरू होने वाली आइटम संख्याएँ बनाता है। हर संख्या के लिए, XLOOKUP संचयी सीमाओं में खोजता है। इसके अंतिम दो आर्ग्युमेंट सटीक मिलान या अगला बड़ा मान निर्दिष्ट करते हैं और खोज पहली प्रविष्टि से आख़िरी तक होती है। यह खोज दिशा शून्य के कारण कुल दोहराए जाने पर पहली मिलती-जुलती सीमा को बनाए रखती है। SEQUENCE फ़ंक्शन, XLOOKUP फ़ंक्शन।

बाहरी IF कुल शून्य होने पर खाली टेक्स्ट लौटाता है। इस स्थिति में, दोनों शुरुआती सेल खाली दिखते हैं और उपयोग के लिए कोई आइटम रिकॉर्ड नहीं होते। यह जानबूझकर शून्य-कुल की स्थिति है, जिसमें IF दो परिणामों में से एक चुनता है। IF फ़ंक्शन।

दिए गए मैट्रिक्स के लिए, K1:L7 में यह होना चाहिए:

संपत्ति फ़ॉर्मेट
आर्क टाइल छोटा
आर्क टाइल छोटा
डॉट टाइल छोटा
वेव टाइल छोटा
वेव टाइल छोटा
वेव टाइल बड़ा

दोनों आउटपुट कॉलम में जनरेट की गई समान आइटम संख्याएँ और सीमा रेंज उपयोग होते हैं। केवल लौटाई जाने वाली रेंज बदलती है: कॉलम F से संपत्ति के लेबल मिलते हैं और कॉलम G से फ़ॉर्मेट मिलते हैं। फ़ॉर्मूले संपादित करते समय इन लुकअप इनपुट को एक-दूसरे के अनुरूप रखें।

मैट्रिक्स बढ़ाने से पहले परिणाम जाँचें

नमूना डेटा के साथ तीन जाँच करें:

  1. कुल की तुलना करें: =SUM(B2:C4) और =SUM(H2:H7) दोनों का परिणाम छह होना चाहिए।
  2. संयोजनों की तुलना करें: आउटपुट में बताए गए दोहराव की संख्या के साथ सभी चार गैर-शून्य संयोजन होने चाहिए।
  3. B2 को दो से तीन करें: अपेक्षित आउटपुट सात पंक्तियों का होगा, जिसमें तीन आर्क टाइल छोटा प्रविष्टियाँ होंगी। उसके बाद इसे फिर से दो पर रखें।

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

जानबूझकर और संपत्तियाँ या फ़ॉर्मेट जोड़ें

B2:C4 के भीतर मात्राएँ बदलने के लिए मौजूदा सेटअप पर्याप्त है। मैट्रिक्स के आयाम बढ़ाने पर संदर्भों और सहायक सूची की लंबाई में बदलाव करना होगा।

पूरे उदाहरण के लिए, स्रोत को A1:D5 में चार संपत्तियों और तीन फ़ॉर्मेट तक विस्तारित करें। सहायक क्षेत्र को E:I में रखें या वहाँ ले जाएँ, E2:E13 में एक से बारह तक स्थान दर्ज करें, और F2:H2 के फ़ॉर्मूले इनसे बदलें:

=INDEX($A$2:$A$5,INT((E2-1)/3)+1)

=INDEX($B$1:$D$1,1,MOD(E2-1,3)+1)

=INDEX($B$2:$D$5,INT((E2-1)/3)+1,MOD(E2-1,3)+1)

इन फ़ॉर्मूलों को कॉपी करके पंक्ति तेरह तक भरें, और कॉलम I में रनिंग टोटल फ़ॉर्मूले को भी उसी पंक्ति तक बढ़ाएँ। दोनों आउटपुट फ़ॉर्मूलों में, पंक्ति सात पर समाप्त होने वाली हर सहायक रेंज को बदलकर पंक्ति तेरह तक करें। अब भाजक और शेषफल का आधार तीन होगा, क्योंकि हर संपत्ति के तीन फ़ॉर्मेट हैं। ये बदलाव करने के बाद स्रोत के कुल और अलग-अलग संयोजनों की फिर से जाँच करें।

अधूरे या अवरुद्ध आउटपुट को ठीक करें

यदि Excel #SPILL! दिखाता है, तो इच्छित आउटपुट क्षेत्र में मौजूदा प्रविष्टियों या मर्ज किए गए सेल की जाँच करें, और पुष्टि करें कि फ़ॉर्मूला Excel तालिका के बाहर है। वर्कशीट के किनारे से आगे बढ़ने वाला परिणाम भी स्पिल नहीं हो सकता। Microsoft इन सभी स्थितियों का दस्तावेज़ीकरण करता है। #SPILL! त्रुटि को ठीक करने का तरीका।

यदि आउटपुट में बहुत कम पंक्तियाँ हैं, तो लुकअप लॉजिक बदलने से पहले मात्राओं की जाँच करें। Microsoft बताता है कि SUM संदर्भित रेंज में टेक्स्ट को अनदेखा करता है। इसलिए टेक्स्ट के रूप में संग्रहीत मात्रा कुल में शामिल नहीं हो सकती। स्रोत मान को ठीक करें और कुल की जाँच दोहराएँ। SUM फ़ंक्शन।

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

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

स्रोत