क्या Excel VLOOKUP सभी परिणाम लौटा सकता है और उनके बीच में अल्पविराम जोड़ सकता है?
वीडियो देखेंा
- लक्ष्य एक VLOOKUP से सभी पाठ उत्तरों को संक्षिप्त करना है
- बिल की विधि: GetAll नामक एक VBA फ़ंक्शन का उपयोग करें
- निकालें डुप्लिकेट का उपयोग करके अद्वितीय सूची
- माइक की विधि:
- उन्नत फ़िल्टर का उपयोग करके अद्वितीय सूची
- TEXTJOIN फ़ंक्शन को Office 365 में जोड़ा गया है
TEXTJOIN(", ",,IF(OilChangeData(ID)=D2,OilChangeData(Comment),""))- IF फ़ंक्शन के कारण, सूत्र को संपादित करने के लिए किसी भी समय Ctrl + Shift + दर्ज करने की आवश्यकता होती है
- Alt AQOR Enter एडवांस्ड फ़िल्टर को फिर से चलाएगा!
वीडियो ट्रांसक्रिप्ट
एपिसोड 183: सभी VLOOKUP मैचों में शामिल हों
बिल जेलन: अरे, वापस स्वागत है। यह एक और द्वंद्वयुद्ध एक्सेल पॉडकास्ट का समय है। मैं बिल जेलन हूं, मैं एक्सेल फन के लिए माइक गिर्विन से जुड़ूंगा। यह हमारा एपिसोड 183 है: Join all VLOOKUP Matches।
(संगीत)
ठीक है, आज का सवाल मैट से। क्या VLOOKUP सभी परिणाम लौटा सकता है और प्रत्येक के बीच अल्पविराम स्थान के साथ जुड़ सकता है। उदाहरण के लिए, 109876 जो ये दोनों यहाँ हैं, क्या यह 12/12 पर लो ऑयल कॉमा स्पेस की जाँच कर सकता है। और निश्चित रूप से अगर वहाँ अधिक थे, तो यह अधिक वापस आ जाएगा। ठीक है, इसलिए यहां मेरा समाधान कुछ VBA का उपयोग करने वाला है। ठीक है, इसलिए सुनिश्चित करें कि यह xlsm के रूप में सहेजा गया है या आप VBA या xlsb नहीं चला सकते, लेकिन xlsx नहीं - xlsx एक फ़ाइल है जो VBA नहीं चला सकती है। हम Alt + F11 दबाएंगे, सुनिश्चित करें कि आप Dual183 या आपकी कार्यपुस्तिका का नाम जो भी हो। रिक्त मॉड्यूल में मॉड्यूल डालें और हम इस कोड को ठीक करने जा रहे हैं।
आइए इस फ़ंक्शन GetAll पर एक नज़र डालें, और यहां वह आईडी नंबर है जिसे हम खोज रहे हैं और फिर वह श्रेणी जिसे हम देखना चाहते हैं। और हम शुरू करते हैं, हम GetAll नामक एक चर को वापस करने जा रहे हैं, इसलिए हम खाली खाली के बराबर होने के साथ शुरू करते हैं। मेरी श्रेणी के प्रत्येक सेल के लिए, यदि सेल वैल्यू वह है जिसे हम खोज रहे हैं तो हम GetAll = GetAll & “” और उसके बाद सेल लेने जा रहे हैं। सेल (ऑफसेट, 0 कॉलम, 1 कॉलम), दूसरे शब्दों में मान उस ID नंबर के ठीक पीछे, क्योंकि VBA में वापस, यहाँ आईडी नंबर है। यदि हमें मिलान आईडी संख्या मिल जाए तो हम 1 कॉलम से ऊपर जाना चाहते हैं। अब, क्या होगा अगर आप 2 कॉलम को ओवर या 3 कॉलम से ऊपर जाना चाहते हैं, तो आप इस 0 पंक्तियों और 1 कॉलम को एक 2 में बदल दें। ठीक है, यह देखने के लिए भी जांचें कि क्या हम एक अल्पविराम स्थान नहीं रखते हैं यदि यह सबसे पहला।तो अगर GetAll चर वर्तमान में "" है, तो हम अल्पविराम स्थान नहीं रखेंगे, ठीक है?
तो अब जब हमारे यहाँ यह फंक्शन है, तो यह देखिए कि मैट की समस्या को हल करना कितना आसान है। हम यहां आने वाले हैं और आइए उसकी आईडी, Ctrl + C और जैसे Ctrl + V पेस्ट करते हैं। डेटा, डुप्लिकेट निकालें, ठीक पर क्लिक करें। तो आईडी की एक अद्वितीय सूची है और फिर हम = getall कहना चाहते हैं और हम E2 अल्पविराम में उस मान की तलाश कर रहे हैं। यहाँ पर इस रेंज को देखते हुए, मैं F4 दबाऊंगा। F4 एक नियमित कार्य की तरह काम करता है। और फिर से मैट के सवाल को आगे बढ़ाते हुए, उस डाउन को शूट करने के लिए डबल-क्लिक करें। यह काम करेगा।
और चलो बस कोशिश करते हैं, चलो यहाँ कुछ पागल कोशिश करते हैं। चलो एक वाक्यांश 1 करते हैं और बस 10. 1 के माध्यम से वाक्यांश 1 की तरह उनमें से एक गुच्छा डालते हैं। हम इन सभी को 109999 पर हस्ताक्षर करेंगे। चिपकाएँ, और फिर यहाँ पेस्ट करें। उस सूत्र को कॉपी करें, सूत्र को संपादित करें ताकि यह नीचे तक सभी तरह से चला जाए। हाँ। और यह उन सभी वाक्यांशों को वापस कर देगा। ठीक है, इसलिए यह मेरा समाधान है, VBA, वहाँ एक छोटा सा फ़ंक्शन। माइक, आइए देखें कि आपके पास क्या है।
माइक गिरविन: धन्यवाद,। GetAll, यह एक भयानक VBA फ़ंक्शन है। ठीक है, मैं यहीं चादर पर जाने वाला हूं। मैंने पहले ही इसे एक एक्सेल टेबल में बदल दिया है ताकि जब हम नीचे रिकॉर्ड्स जोड़ते हैं, तो उम्मीद है कि चीजें अपडेट होंगी।
अब पहली बात मैं इसे दो भागों में करने जा रहा हूँ। मैं एक अद्वितीय सूची निकालने के लिए यहाँ एक सूत्र कर सकता था लेकिन मैं एक और विकल्प देखना चाहता हूँ: उन्नत फ़िल्टर में एक अद्वितीय सूची विकल्प होता है और इसे अद्यतन किया जा सकता है। मैं एडवांस फिल्टर के ऊपर सिर्फ आईडी कॉलम डेटा को हाइलाइट करने जा रहा हूं, या मैं कीबोर्ड Alt, A, Q. Now, फिल्टर लिस्ट इन-प्लेस, का उपयोग करने जा रहा हूं। मैं इसे दूसरे स्थान पर कॉपी करना चाहता हूं। इसे सिर्फ ए कॉलम मिला और क्योंकि यह एक एक्सेल टेबल है जो बाद में विस्तार करेगा। मेरे पास कोई मापदंड नहीं है, मैं इसे D1 पर कॉपी करना चाहता हूं और केवल अनन्य रिकॉर्ड की जांच करना चाहता हूं। ओके पर क्लिक करें।
अब, मैं यहां आने वाला हूं, सभी टिप्पणियाँ दर्ज करते हैं और मैं एक फ़ंक्शन का उपयोग करने जा रहा हूं जो केवल Excel 2016 Office 365 में काम करता है: = TEXTJOIN फ़ंक्शन। यह फ़ंक्शन अकेले एक्सेल का नवीनतम संस्करण प्राप्त करने के लायक है। यह एक ऐसा सामान्य कार्य है जिसे लोग करना चाहते हैं, कई चीजों को एक साथ जोड़ते हैं। अब हमारे "," और इस समारोह के बारे में महान बात यह है कि हम इसे खाली कोशिकाओं को अनदेखा करने के लिए कह सकते हैं। अब, मैं TRUE रख सकता हूं, 1 या इसे छोड़ सकता हूं, इसे छोड़ सकता हूं। इसलिए, मैं इसे छोड़ने जा रहा हूं, इसे स्वीकार करता हूं। और यहां हमें अपने पाठ की आवश्यकता है। हम IF फ़िल्टर का उपयोग करने जा रहे हैं और हमें जो आइटम चाहिए वे प्राप्त कर सकते हैं। मैं यहाँ इस पूरे कॉलम को देखने जा रहा हूँ: सारणी का नाम और फिर () क्षेत्र का नाम, आपमें से कोई भी = इस सापेक्ष कोशिका संदर्भ में, यह तार्किक परीक्षण है। अगर मुझे यह क्लिक करना था और मूल्यांकन करने के लिए F9 कुंजी मारा,आप अभी देख सकते हैं कि हमारे पास केवल 2 TRUES हैं, Ctrl + Z अब मैं एक अल्पविराम टाइप करता हूं और Trues और Falses की सरणी के साथ, अब मैं इसे चुनने के लिए आइटम दे सकता हूं। तो अब, हम केवल उन वस्तुओं को निकालेंगे जिनके पास इस श्रेणी से TRUE है। कोमा और मैं "" सुनिश्चित करना चाहते हैं - जो कि TEXTJIN में दूसरे तर्क के संबंध में एक खाली सेल के रूप में दिखाई देगा।
अब, मैं कोष्ठक को बंद करने जा रहा हूं और अब IF फ़ंक्शन ट्रूज़ और फाल्सेस के उस तार को बनाएगा, इस सीमा से वास्तविक वस्तुओं को उठाया जाएगा यदि यह इसे सही देखता है और अन्य सभी वस्तुओं में वह खाली सेल होगा। और अंदाज लगाइये क्या? TEXTJOIN उन सभी खाली कक्षों को पूरी तरह से अनदेखा कर देगा और इस ID से मेल खाने वाली वस्तुओं को वापस कर देगा, और फिर इसे उस सीमांकक के साथ जोड़ देगा। अब यह निश्चित रूप से एक ऐरे सूत्र है जिसके लिए विशेष कीस्ट्रोक Ctrol + Shift + Enter की आवश्यकता होती है। तार्किक परीक्षण तर्क हमारे Array ऑपरेशन को होल्ड करता है और जब तक हम कीबोर्ड Ctrl + Shift + Enter का उपयोग नहीं करते हैं, तब तक यह तर्क इस Array ऑपरेशन की सही गणना नहीं कर सकता है। अब मैं कोष्ठकों को बंद करने जा रहा हूं। वास्तव में हम पाठ 1 में यहां 1 सही साबित कर सकते हैं यदि मैं यह सब F9 करता हूं, तो हम देख सकते हैं कि हमें 2 आइटम मिलेंगे, बाकी उन खाली कोशिकाओं को नजरअंदाज कर दिया जाएगा। Ctrl + Z अब छोडो'इसे Ctrl + Shift + Enter के साथ सेल में दर्ज करें। तुरंत फॉर्मूला बार को देखें। उन घुंघराले कोष्ठक एक्सेल बता रहे हैं जो आपको समझ गए हैं और इसे एक सरणी सूत्र के रूप में गणना की है। अब मैं इसे डबल-क्लिक कर सकता हूं और इसे नीचे भेज सकता हूं। यह अच्छा लग रहा है।
मैं अंतिम सेल में जा रहा हूं और यह सत्यापित करने के लिए F2 मारा कि सभी रेंज सही तरीके से देख रहे हैं। अब मैं जो नहीं करना चाहता हूं वह यह है कि मैं एंटर को हिट नहीं करना चाहता हूं क्योंकि एडिट मोड में डालने के बाद वह फॉर्मूला केवल सही गणना करेगा यदि हम Ctrl + Shift + Enter का उपयोग करते हैं; या, क्योंकि हम पहले ही सूत्र में प्रवेश कर चुके हैं, हम एडिट मोड में डालने से पहले सेल में जो कुछ भी है उसे वापस करने के लिए हम Esc कुंजी का उपयोग कर सकते हैं।
Now, let's test this. I'm going to click in the last cell down here and hit Tab and then type a new ID, Tab, Tab. Another new record, Tab, and I can already see I didn't have enough work here. I am, we're going to put - Perfect and then Enter. Now, this isn't going to automatically update like if we have a bunch of formulas that we’re counting unique items and then extracting unique items, but no problem. Watch this. We can update this list of unique records because we used Advanced Filter and it doesn't matter what cell you start from either because when Advanced Filter is invoked, it memorizes the extract range and the ranges it was originally looking at. You can click on Advanced Filter or use the keyboard Alt+A+Q. We do have to select Copy to another location, but look at that. It totally remembered and expanded to A13 because of the Excel Table feature. It remembered the extract range. I do have to check Unique records only but click OK.
Now, I have to come over and copy this formula down. And there you go, using Advanced Filter and the amazing TEXTJOIN function with, in Array operation to get just the items that match. Alright, throw back to.
Bill Jelen: Hey, Mike, that is awesome. Alright, wrapping up this episode. I used the VBA function called GetAll, and my unique list was created by Remove Duplicates which is far easier than Advanced Filter but the problem is it’s a one-time thing. It doesn't remember the previous settings. Mike created his unique list using Advanced Filter which means that he could later redo that Advanced Filter without re-specifying the input range and the extract range. And then TEXTJOIN, a beautiful new function, added an Office 365. Mike says that alone is a reason to get the latest Office. I said the TEXTJOIN would be life-changing. TEXTJOIN is awesome because it can handle Arrays.
Alright, so here's the formula that Mike wrote: putting an IF in there and the “ ” returning the equivalent of an empty cell; and here we're saying Ignore empty cells. Ahh, that's beautiful but because of the IF function, the formula requires Ctrl+Shift+Enter to create the formula, or any time you edit the formula, all that Mike used to Esc to get out. And this section about IF forces you into Ctrl+Shift+Enter is a topic in Mike's awesome, awesome book, An Array Formulas Ctrl+Shift+Enter. Check that out at Amazon or elsewhere, your favorite bookseller. And then, the beautiful thing is that because Advanced Filter remembers the old settings, Mike used Alt+A+Q and then could have used O+R Enter, will rerun the Advanced Filter, copy the formula down for the new cells and it works. That is beautiful, alright.
ओह हे, मैं हर किसी को रोकने के लिए धन्यवाद देना चाहता हूं। हम आपको अगली बार किसी अन्य द्वंद्वयुद्ध एक्सेल पॉडकास्ट से और एक्सेल इज़ फन के लिए देखेंगे।
फ़ाइल डाउनलोड करें
यहाँ नमूना फ़ाइल डाउनलोड करें: Duel183.xlsm
