हर इन्वेंटरी पंक्ति में एसेट मेटाडेटा जोड़ने के लिए सटीक मिलान वाला लुकअप इस्तेमाल करें, जिसमें दोहराई गई एसेट ID भी शामिल हैं। Mac के लिए Excel में, आप HSTACK और XLOOKUP के साथ एक स्पिल होने वाला परिणाम बना सकते हैं, या Excel 2021 में अलग-अलग लुकअप कॉलम इस्तेमाल कर सकते हैं।

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

Mac के लिए Excel का कौन-सा संस्करण इस तरीके को सपोर्ट करता है?

Excel → About Microsoft Excel के ज़रिए अपना इंस्टॉलेशन देखें। Microsoft के Mac निर्देश बताते हैं कि ऐप का About डायलॉग उसका संस्करण नंबर और लाइसेंस प्रकार दिखाता है। फ़ॉर्मूला चुनते समय दोनों देखें। Office के बारे में: मैं Office का कौन-सा संस्करण इस्तेमाल कर रहा हूँ?

Mac संस्करण इस लेख में तरीका
Mac के लिए Microsoft 365 का Excel HSTACK और XLOOKUP का इस्तेमाल करके एक संयुक्त परिणाम
Mac के लिए Excel 2024 HSTACK और XLOOKUP का इस्तेमाल करके एक संयुक्त परिणाम
Mac के लिए Excel 2021 तीन अलग-अलग XLOOKUP स्पिल कॉलम
Excel 2019 या 2016 इन फ़ॉर्मूलों के लिए नया संस्करण चाहिए

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

अगर आपके पास Excel 2021 है, तो नीचे दिया गया अलग-अलग कॉलम वाला तरीका इस्तेमाल करें। इससे HSTACK की ज़रूरत के बिना वही समृद्ध परिणाम मिलता है।

साझा एसेट ID के साथ इन्वेंटरी और मेटाडेटा सेट अप करें

एक ही वर्कबुक में Inventory और Metadata नाम की दो वर्कशीट बनाएँ। Inventory!A1:B6 में नीचे दिया इन्वेंटरी डेटा दर्ज करें। इस उदाहरण में इन सेल को सामान्य वर्कशीट रेंज के रूप में ही रखें।

AssetID Quantity
ARC-01 3
TILE-02 5
ARC-01 2
GRID-03 4
TILE-02 1

Metadata!A1:D4 में मेटाडेटा दर्ज करें:

AssetID Title Format Palette
ARC-01 Arc Study Vector Teal
TILE-02 Tile Study Raster Amber
GRID-03 Grid Study Vector Slate

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

दोनों शीट में ऐसा एसेट ID इस्तेमाल करें जो लगातार उसी एसेट की पहचान करे। इस उदाहरण में हर ID और मेटाडेटा फ़ील्ड भरा हुआ है। अपनी वर्कबुक में तय करें कि किसी अज्ञात फ़ील्ड को स्पष्ट रूप से कैसे दर्शाना है—उदाहरण के लिए, Unknown टेक्स्ट से—ताकि उसे एसेट का मिलान न मिलने से अलग पहचाना जा सके।

जाँचें कि हर इन्वेंटरी ID का मेटाडेटा से ठीक एक मिलान है

Inventory पर C1 में MetadataMatches दर्ज करें। C2 में यह फ़ॉर्मूला दर्ज करें, फिर उसे C6 तक कॉपी करें:

=COUNTIF(Metadata!$A$2:$A$4,A2)

आउटपुट बनाने से पहले परिणाम का अर्थ समझें:

  • 0: मेटाडेटा शीट में एसेट ID मौजूद नहीं है।
  • 1: एसेट के लिए इस वर्कफ़्लो में अपेक्षित मेटाडेटा की एक पंक्ति मौजूद है।
  • 1 से बड़ी कोई भी संख्या: मेटाडेटा में डुप्लिकेट ID हैं जिन्हें सुलझाना होगा।

COUNTIF मानदंड से मेल खाने वाले सेल गिनता है। दिए गए डेटा के लिए, हर जाँच का परिणाम 1 आता है। यहाँ दिखाए गए जैसे सरल ID इस्तेमाल करें: COUNTIF तारांकन और प्रश्नचिह्न को वाइल्डकार्ड वर्ण मानता है, इसलिए इन वर्णों वाली ID को संभालने के लिए अतिरिक्त तरीके चाहिए। Microsoft Excel में COUNTIF फ़ंक्शन का इस्तेमाल करें

आगे बढ़ने से पहले डुप्लिकेट मेटाडेटा ID सुलझाएँ। XLOOKUP डिफ़ॉल्ट रूप से पहला मिलान लौटाता है, इसलिए केवल भरा हुआ परिणाम यह साबित नहीं कर सकता कि मेटाडेटा कुंजी अद्वितीय है। XLOOKUP फ़ंक्शन

Microsoft 365 या Excel 2024 में एक समृद्ध ऐरे बनाएँ

Enriched नाम की वर्कशीट बनाएँ। A1:E1 में ये पाँच शीर्षक दर्ज करें: AssetID, Quantity, Title, Format, Palette। A2:E6 खाली छोड़ें।

Enriched!A2 चुनें, नीचे दिया पूरा फ़ॉर्मूला दर्ज करें और Return दबाएँ:

=HSTACK(
  Inventory!A2:B6,
  XLOOKUP(Inventory!A2:A6,Metadata!A2:A4,Metadata!B2:B4,"MISSING METADATA",0),
  XLOOKUP(Inventory!A2:A6,Metadata!A2:A4,Metadata!C2:C4,"MISSING METADATA",0),
  XLOOKUP(Inventory!A2:A6,Metadata!A2:A4,Metadata!D2:D4,"MISSING METADATA",0)
)

यह फ़ॉर्मूला दो मूल इन्वेंटरी कॉलम को तीन लुकअप-परिणाम कॉलम के साथ जोड़ता है। हर लुकअप उन्हीं पाँच इन्वेंटरी ID का इस्तेमाल करता है, जिससे जोड़े गए फ़ील्ड मूल प्रविष्टियों के साथ सही क्रम में रहते हैं। HSTACK दिए गए क्रम में ऐरे को क्षैतिज रूप से जोड़ता है। HSTACK फ़ंक्शन

हर लुकअप में आख़िरी 0 सटीक मिलान का अनुरोध करता है। मेल न खाने वाली ID के लिए MISSING METADATA टेक्स्ट आता है। इसे दिखाई देने देना अधूरे मिलानों को खोजने में मदद करता है, बजाय इसके कि कोई खाली सेल बिना स्पष्टीकरण के रह जाए। XLOOKUP फ़ंक्शन

अपेक्षित आउटपुट Enriched!A2:E6 में होगा:

AssetID Quantity Title Format Palette
ARC-01 3 Arc Study Vector Teal
TILE-02 5 Tile Study Raster Amber
ARC-01 2 Arc Study Vector Teal
GRID-03 4 Grid Study Vector Slate
TILE-02 1 Tile Study Raster Amber

ध्यान दें कि ARC-01 की दोनों प्रविष्टियाँ बनी रहती हैं और उनकी मात्राएँ 3 और 2 हैं। दोनों को Arc Study, Vector और Teal मिलता है। मेटाडेटा ने इन प्रविष्टियों को मिलाए बिना उनमें जानकारी जोड़ी है।

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

Mac के लिए Excel 2021 में तीन लुकअप कॉलम इस्तेमाल करें

Excel 2021 के लिए, मूल इन्वेंटरी के ठीक बगल में काम करें। मिलान-गणना की जाँच C कॉलम में रखें, फिर D1:F1 में Title, Format और Palette दर्ज करें। D2:F6 खाली छोड़ें।

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

=XLOOKUP(A2:A6,Metadata!A2:A4,Metadata!B2:B4,"MISSING METADATA",0)

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

=XLOOKUP(A2:A6,Metadata!A2:A4,Metadata!C2:C4,"MISSING METADATA",0)

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

=XLOOKUP(A2:A6,Metadata!A2:A4,Metadata!D2:D4,"MISSING METADATA",0)

हर फ़ॉर्मूले के बाद Return दबाएँ। हर परिणाम अपने शुरुआती सेल के नीचे पाँच पंक्तियाँ भरता है। आपकी एसेट ID और मात्राएँ कॉलम A:B में रहती हैं, जाँच की गणना C में बनी रहती है और जोड़ा गया मेटाडेटा D:F में दिखता है। Mac के लिए Excel 2021 में इस तरीके के लिए ज़रूरी डायनेमिक-ऐरे व्यवहार शामिल है। Mac के लिए Excel 2021 में नया क्या है

ज़्यादा इन्वेंटरी पंक्तियों के लिए वर्कबुक को अनुकूलित करें

उदाहरणों में निश्चित रेंज इस्तेमाल की गई हैं ताकि हर संदर्भ स्पष्ट दिखे। जब आपकी इन्वेंटरी बढ़े, तो इन्वेंटरी के हर संदर्भ को एक ही आख़िरी पंक्ति तक बढ़ाएँ। उदाहरण के लिए, अगर इन्वेंटरी पंक्ति 501 पर समाप्त होती है, तो संयुक्त फ़ॉर्मूले में Inventory!A2:B501 और Inventory!A2:A501 के तीन उदाहरण चाहिए। मिलान-गणना की जाँच को भी उसी पंक्ति तक बढ़ाएँ।

मेटाडेटा बढ़ने पर, उसके सभी संदर्भ एक साथ बढ़ाएँ। अगर उसकी आख़िरी पंक्ति 101 हो जाती है, तो कुंजियों के लिए Metadata!A2:A101 और उनसे मेल खाने वाली B2:B101, C2:C101 तथा D2:D101 की रिटर्न रेंज इस्तेमाल करें। इस्तेमाल किए गए रिकॉर्ड शामिल करें, बड़ी संख्या में खाली पंक्तियाँ नहीं।

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

अगर आपको Excel टेबल के भीतर जानकारी जोड़नी है, तो हर मेटाडेटा कॉलम और इन्वेंटरी पंक्ति के लिए अलग एकल-परिणाम फ़ॉर्मूला इस्तेमाल करें। टेबल में कैलकुलेटेड कॉलम होते हैं, जो पूरे कॉलम पर फ़ॉर्मूला लागू करते हैं; कई सेल में स्पिल होने वाला परिणाम टेबल के बाहर होना चाहिए। Excel टेबल का अवलोकन

समस्याओं का पता लगाएँ और तैयार परिणाम की पुष्टि करें

अगर Excel #SPILL! दिखाता है, तो इच्छित गंतव्य सेल देखें। जिस सामग्री को रखना है उसे कहीं और ले जाएँ और सुनिश्चित करें कि फ़ॉर्मूला Excel टेबल के बाहर है। स्पिल होने वाले परिणाम का केवल सबसे ऊपर-बाएँ सेल संपादित किया जा सकता है; फ़ॉर्मूले में बदलाव वहीं करें। Microsoft इन स्पिल सीमाओं और बाधा-संबंधी त्रुटियों का दस्तावेज़ देता है। डायनेमिक ऐरे फ़ॉर्मूले और स्पिल होने वाले ऐरे का व्यवहार

अगर किसी पंक्ति में MISSING METADATA दिखता है, तो उसकी मिलान-गणना जाँच पर लौटें। शून्य का अर्थ है कि आपको मेटाडेटा का छूटा हुआ रिकॉर्ड जोड़ना होगा या एसेट ID ठीक करनी होगी। जब देखने में समान लेबल अपेक्षा के अनुसार काम न करें, तो आगे या पीछे गलती से लगे स्पेस जाँचें; Microsoft इन्हें अप्रत्याशित COUNTIF परिणामों का एक कारण बताता है। Microsoft Excel में COUNTIF फ़ंक्शन का इस्तेमाल करें

इस सेटअप के लिए दोनों स्रोत वर्कशीट एक ही वर्कबुक में रखें। अगर बाद में स्रोत को किसी दूसरी वर्कबुक में ले जाते हैं, तो Microsoft की दर्ज सीमा का ध्यान रखें: लिंक किए गए डायनेमिक ऐरे के लिए दोनों वर्कबुक खुली होनी चाहिए, और स्रोत बंद होने पर रीफ़्रेश करने से #REF! आ सकता है। डायनेमिक ऐरे फ़ॉर्मूले और स्पिल होने वाले ऐरे का व्यवहार

तीन ठोस जाँचों से काल्पनिक उदाहरण की पुष्टि करें:

  1. मूल इन्वेंटरी के क्रम में आउटपुट में डेटा की पाँच पंक्तियाँ हैं।
  2. मात्राएँ 3, 5, 2, 4, 1 बनी रहती हैं और उनका कुल 15 है।
  3. दोहराई गई ID को एक जैसा मेटाडेटा मिलता है और मिलान-गणना की हर जाँच 1 है।

इन जाँचों से अपेक्षित नतीजे की पुष्टि होती है: हर इन्वेंटरी प्रविष्टि अपनी जगह और मात्रा बनाए रखते हुए अपने एसेट ID से जुड़ा विवरण प्राप्त करती है। बड़ी वर्कबुक में रेंज बढ़ाने या मेटाडेटा रिकॉर्ड जोड़ने के बाद यही जाँचें दोहराएँ।

स्रोत