Mac के लिए Excel में किसी आर्ट-एसेट लेबल से जुड़ी हर मात्रा लौटाने के लिए FILTER का उपयोग करें। परिणामी ऐरे वर्कशीट की सेल में फैल सकता है, किसी अन्य गणना में उपयोग हो सकता है या बार-बार मेल खाने वाली प्रविष्टियाँ खोए बिना जोड़ा जा सकता है।

इस चरण-दर-चरण मार्गदर्शिका में ग्राफ़िक एसेट के काल्पनिक लेबल और मात्राओं का उपयोग किया गया है। काम है एक लेबल चुनना, उससे जुड़ी सभी मात्राएँ प्राप्त करना और उन अलग-अलग संख्याओं को किसी गणना में फिर से उपयोग करना। Microsoft के दस्तावेज़ों के अनुसार, FILTER दिए गए मानदंडों के आधार पर एक ऐरे लौटाता है। FILTER फ़ंक्शन।

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

Microsoft के मौजूदा फ़ंक्शन दस्तावेज़ों में स्पष्ट रूप से बताया गया है कि Mac के लिए Excel for Microsoft 365, Excel 2024 और Excel 2021 FILTER का समर्थन करते हैं। नीचे दिए गए फ़ॉर्मूलों के लिए इनमें से किसी एक संस्करण का उपयोग करें। FILTER फ़ंक्शन।

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

Mac के लिए Excel 2019 और Excel 2016, FILTER की दस्तावेज़ित उपलब्धता के दायरे में नहीं आते। यदि आप इनमें से कोई संस्करण इस्तेमाल करते हैं, तो इस विशेष डायनामिक-ऐरे वर्कफ़्लो के लिए समर्थित तरीका है कि वर्कबुक को सूचीबद्ध Mac संस्करणों में से किसी एक में खोलें। नीचे दिए गए निर्देश इसी पूर्वापेक्षा पर निर्भर हैं; ये पुराने ऐरे-फ़ॉर्मूला का कोई वैकल्पिक तरीका नहीं हैं।

एसेट की मात्राओं का एक छोटा उदाहरण सेट करें

खाली वर्कशीट पर नीचे दिया गया डेटा A1:B7 में दर्ज करें। पहली पंक्ति में हेडर हैं। मात्राएँ संख्याओं के रूप में दर्ज करें, जिसमें B6 का शून्य भी शामिल है।

वर्कशीट पंक्ति कॉलम A: एसेट कॉलम B: मात्रा
1 एसेट मात्रा
2 Tile A 4
3 Arc B 7
4 Tile A 2
5 Dot C 5
6 Tile A 0
7 Tile A 4

D1 में Selected asset और D2 में Tile A दर्ज करें। F1 में Matching quantities दर्ज करें। F2 के नीचे की सेल खाली छोड़ें, ताकि परिणाम के फैलने के लिए जगह रहे।

इस नमूने में जानबूझकर एक ही लेबल के लिए चार की दो मात्राएँ और शून्य की एक मात्रा रखी गई हैं। सही परिणाम में मेल खाने वाली सभी चार पंक्तियाँ शामिल होनी चाहिए: 4, 2, 0 और 4। बार-बार आने वाले मान हटाने या शून्य को छोड़ने से माँगा गया परिणाम बदल जाएगा।

FILTER से मेल खाने वाली सभी मात्राएँ लौटाएँ

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

=FILTER(B2:B7,A2:A7=D2)

इस फ़ॉर्मूले में B2:B7 को मात्रा वाले ऐरे के रूप में और A2:A7=D2 को शामिल करने की जाँच के रूप में इस्तेमाल किया गया है। ये रेंज स्रोत की उन्हीं छह पंक्तियों को कवर करती हैं, जिससे Microsoft की यह आवश्यकता पूरी होती है कि शामिल करने वाले ऐरे की ऊँचाई या चौड़ाई संगत हो। FILTER फ़ंक्शन।

नमूना डेटा के साथ, परिणाम F2:F5 में दिखाई देता है:

सेल परिणाम
F2 4
F3 2
F4 0
F5 4

फ़ॉर्मूला केवल F2 में दर्ज करें; Excel बाकी परिणाम अपने आप पास की सेल में रखता है। Microsoft इस व्यवहार को spilling कहता है। बाद में फ़ॉर्मूला बदलने के लिए परिणाम की सबसे ऊपर-बाईं सेल संपादित करें, जो यहाँ F2 है। डायनामिक ऐरे फ़ॉर्मूले और स्पिल्ड ऐरे का व्यवहार।

चार की दोनों आवृत्तियाँ स्रोत की अलग-अलग मेल खाने वाली पंक्तियों को दर्शाती हैं। जब अगली गणना में चुनी गई एसेट की हर आवृत्ति शामिल होनी चाहिए, तो दोनों को बनाए रखें।

XLOOKUP इन चारों पंक्तियों को क्यों नहीं लौटाता

Microsoft के अनुसार, XLOOKUP उसे मिलने वाला पहला मेल खाता आइटम लौटाता है। यह मेल खाने वाली पंक्ति के कई कॉलम लौटा सकता है, लेकिन इससे उन सभी पंक्तियों की सूची नहीं मिलती जिनमें एक ही लेबल हो। XLOOKUP फ़ंक्शन।

उदाहरण के लिए, नीचे दिया गया फ़ॉर्मूला नमूने से केवल 4 लौटाता है:

=XLOOKUP(D2,A2:A7,B2:B7)

जब एक मेल खाने वाली पंक्ति से जवाब मिल जाता है, तब यह परिणाम उपयुक्त है। इस काम में मात्राएँ चार अलग-अलग मेल खाने वाली पंक्तियों से आती हैं, इसलिए FILTER परिणाम का उपयोग करें।

ऐरे को आगे की गणनाओं में फिर से उपयोग करें

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

आगे के फ़ॉर्मूले परिणाम के नीचे तुरंत रखने के बजाय उसके बगल में रखें, ताकि मेल खाने वाली और मात्राओं के लिए जगह रहे। नीचे के उदाहरणों में H से J तक के कॉलम का उपयोग किया गया है।

मेल खाने वाली सभी मात्राएँ जोड़ें

H1 में Total quantity दर्ज करें, फिर H2 में यह फ़ॉर्मूला दर्ज करें:

=SUM(F2#)

उदाहरण का परिणाम 10 है: चार जमा दो जमा शून्य जमा चार। SUM रेंज स्वीकार करता है और उनके संख्यात्मक मान जोड़ता है। SUM फ़ंक्शन।

यदि आपको केवल कुल चाहिए, तो आप इसके बजाय किसी दूसरी खाली सेल में सीधे यह गणना कर सकते हैं:

=SUM(FILTER(B2:B7,A2:A7=D2))

जब अलग-अलग मात्राओं को देखना या फिर से उपयोग करना हो, तो दिखाई देने वाला ऐरे चुनें। जब वर्कशीट में केवल अंतिम कुल चाहिए, तो नेस्टेड फ़ॉर्मूला चुनें।

हर मात्रा को एक साझा गुणक से गुणा करें

मान लें कि उदाहरण में हर मेल खाने वाली मात्रा की तीन प्रतियाँ चाहिए। I1 में Copies और I2 में संख्या 3 दर्ज करें। J1 में Adjusted quantities दर्ज करें, फिर J2 में यह फ़ॉर्मूला दर्ज करें:

=F2#*$I$2

J2:J5 में अपेक्षित परिणाम 12, 6, 0 और 12 हैं। Microsoft ऐसे ऐरे कैलकुलेशन का दस्तावेज़ देता है जो मानों को गुणा करके कई परिणाम लौटाते हैं, और Mac के लिए उसका कैलकुलेटर मार्गदर्शक बताता है कि तारांकन चिह्न गुणा का ऑपरेटर है। ऐरे फ़ॉर्मूलों के दिशानिर्देश और उदाहरण; Mac के लिए Excel में Excel को कैलकुलेटर की तरह इस्तेमाल करें।

एक समायोजित कुल के लिए, H4 में यह दर्ज करें:

=SUM(F2#*$I$2)

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

तय करें कि कुछ भी मेल न खाने पर क्या होना चाहिए

D2 को Wave D में बदलें, जो नमूने में मौजूद नहीं है। मूल दो-तर्क वाला FILTER फ़ॉर्मूला #CALC! लौटाता है, क्योंकि Excel खाली ऐरे नहीं लौटा सकता। Microsoft दस्तावेज़ों में बताया गया है कि वैकल्पिक तीसरा तर्क इस स्थिति में बिना परिणाम के लिए मान देने का तरीका है। #CALC! त्रुटि को ठीक करने का तरीका।

अगली गणना की ज़रूरत के अनुसार वह मान चुनें। निर्णय लेने के लिए नीचे दी गई तालिका इसी वर्कशीट पर लागू होती है:

आवश्यकता फ़ॉर्मूला या तरीका परिणाम
गणनाओं के दौरान मेल न खाने वाली प्रविष्टियाँ दिखती रहनी चाहिए =FILTER(B2:B7,A2:A7=D2) बनाए रखें कुछ भी मेल न मिलने पर #CALC! आता है, ताकि जाँच की जा सके।
परिणाम मुख्य रूप से पढ़ने योग्य सूची है =FILTER(B2:B7,A2:A7=D2,"No matches") का उपयोग करें कुछ भी मेल न मिलने पर एक टेक्स्ट संदेश आता है।
अनुपस्थित लेबल को कुल में शून्य जोड़ना चाहिए =SUM(FILTER(B2:B7,A2:A7=D2,0)) का उपयोग करें कुछ भी मेल न मिलने पर कुल शून्य आता है।

शून्य को वैकल्पिक मान चुनना, गणना का सोच-समझकर बनाया गया नियम है। इसका अर्थ यह नहीं कि स्रोत की कोई मेल खाती पंक्ति शून्य मात्रा वाली है। इस नमूने में Tile A की वास्तविक मात्रा शून्य है; Wave D के लिए स्रोत की कोई पंक्ति नहीं है। एसेट की मौजूदगी मायने रखती हो, तो इस अंतर को बनाए रखें।

टेक्स्ट संदेश का इस्तेमाल भी सावधानी से करें: Microsoft के अनुसार, SUM संदर्भित सेल में मौजूद टेक्स्ट को अनदेखा करता है। इसलिए केवल No matches वाला परिणाम जोड़ने पर शून्य आ सकता है और मेल खाने वाली पंक्तियों की अनुपस्थिति छिप सकती है। जब किसी लेबल के न मिलने पर आगे की गणनाएँ रुकनी चाहिए, तब दिखाई देने वाली त्रुटि वाला तरीका अपनाएँ। SUM फ़ंक्शन।

मूल संख्यात्मक उदाहरणों पर आगे बढ़ने से पहले D2 को वापस Tile A करें।

दूसरी शर्त तभी जोड़ें जब काम में इसकी ज़रूरत हो

यदि आगे के किसी काम में केवल धनात्मक मात्राएँ माँगी गई हों, तो लेबल की जाँच और धनात्मक मात्रा की जाँच, दोनों का उपयोग करें:

=FILTER(B2:B7,(A2:A7=D2)*(B2:B7>0))

नमूने के लिए, यह 4, 2 और 4 लौटाता है। Microsoft के दस्तावेज़ों के अनुसार, FILTER में शर्तों के बीच गुणा AND जाँच के रूप में काम करता है। यह विकल्प जानबूझकर मेल खाने वाले शून्य को छोड़ता है, इसलिए हर मेल खाने वाली मात्रा चाहिए हो तो मूल फ़ॉर्मूला बनाए रखें। FILTER फ़ंक्शन।

परिणाम की समस्या दूर करें और नई पंक्तियाँ शामिल रखें

यदि फ़ॉर्मूला अपेक्षित चार मात्राएँ नहीं देता, तो वर्कशीट को इस क्रम में जाँचें:

  1. आउटपुट की जगह देखें। परिणाम की रेंज में रुकावट होने से #SPILL! आता है। दोबारा कोशिश करने से पहले निर्धारित स्पिल क्षेत्र से ज़रूरी सामग्री हटा दें।
  2. फ़ॉर्मूले की जगह जाँचें। स्पिल्ड ऐरे फ़ॉर्मूले Excel तालिका के बाहर, वर्कशीट ग्रिड में होने चाहिए।
  3. वर्कबुक की निर्भरताएँ जाँचें। Microsoft अलग-अलग वर्कबुक के बीच लिंक किए गए डायनामिक ऐरे का समर्थन तभी करता है, जब दोनों वर्कबुक खुली हों; स्रोत बंद करने के बाद रीफ़्रेश करने पर #REF! आ सकता है।

इन सीमाओं का दस्तावेज़ डायनामिक ऐरे फ़ॉर्मूले और स्पिल्ड ऐरे का व्यवहार में दिया गया है।

इसके बाद स्रोत के लेबल और मात्राएँ जाँचें। D2 की तुलना संबंधित एसेट सेल से करें और पुष्टि करें कि मात्राएँ संख्याओं के रूप में दर्ज हैं। केवल संभावित कुल दिखना पर्याप्त जाँच नहीं है, क्योंकि SUM टेक्स्ट मानों को अनदेखा कर सकता है। SUM फ़ंक्शन।

नमूने के फ़ॉर्मूले स्रोत की पंक्तियों 2 से 7 तक संदर्भित करते हैं। यदि आप पंक्ति 8 में डेटा जोड़ते हैं, तो दोनों स्रोत रेंज संपादित करके उसे शामिल करें। उदाहरण के लिए, A8 में Tile A और B8 में संख्यात्मक मात्रा 3 दर्ज करने के बाद, F2 का फ़ॉर्मूला बदलकर यह करें:

=FILTER(B2:B8,A2:A8=D2)

अपेक्षित ऐरे अब 4, 2, 0, 4, 3 बन जाता है, और कुल 13 हो जाता है। F2# संदर्भ विस्तारित परिणाम का अनुसरण करता है, लेकिन इससे मूल स्रोत रेंज में छूटी हुई पंक्ति अपने आप शामिल नहीं होती।

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

कुल पर भरोसा करने से पहले अलग-अलग परिणाम जाँचें: स्रोत की मूल छह पंक्तियों के लिए Tile A चुनने पर दोनों चार और शून्य समेत चार मात्राएँ मिलनी चाहिए। परिणाम सही होने पर गणनाओं में F2# फिर से उपयोग करें और वर्कशीट के उद्देश्य के अनुसार बिना मेल के लिए स्पष्ट नियम चुनें।

स्रोत