सामग्रीहरू
हामीले भर्खरै सबैभन्दा उपयोगी एक्सेल प्रकार्यहरू मध्ये एउटामा एउटा लेख समर्पित गरेका छौं VPR र कसरी डाटाबेसबाट कार्यपत्र कक्षमा आवश्यक जानकारी निकाल्न प्रयोग गर्न सकिन्छ भनेर देखाएको छ। हामीले यो पनि उल्लेख गर्यौं कि समारोहको लागि दुईवटा प्रयोग केसहरू छन् VPR र ती मध्ये एउटा मात्र डाटाबेस प्रश्नहरूसँग सम्बन्धित छ। यस लेखमा, तपाईंले प्रकार्य प्रयोग गर्ने अर्को कम ज्ञात तरिका सिक्नुहुनेछ VPR एक्सेलमा
यदि तपाईंले अहिलेसम्म यो गर्नुभएको छैन भने, त्यसपछि प्रकार्यको बारेमा अन्तिम लेख पढ्न निश्चित हुनुहोस् VPR, किनकि तलका सबै जानकारीले मान्दछ कि तपाइँ पहिले नै पहिलो लेखमा वर्णन गरिएका सिद्धान्तहरूसँग परिचित हुनुहुन्छ।
डाटाबेस, प्रकार्यहरूसँग काम गर्दा VPR एक अद्वितीय पहिचानकर्ता पास हुन्छ, जुन हामीले फेला पार्न चाहेको जानकारी पहिचान गर्न प्रयोग गरिन्छ (उदाहरणका लागि, उत्पादन कोड वा ग्राहक पहिचान नम्बर)। यो अद्वितीय कोड डाटाबेसमा उपस्थित हुनुपर्छ, अन्यथा VPR त्रुटि रिपोर्ट गर्नेछ। यस लेखमा, हामी प्रकार्य प्रयोग गर्ने यो तरिका हेर्नेछौं VPRजब आईडी डाटाबेसमा अवस्थित छैन। समारोह जस्तै VPR अनुमानित मोडमा स्विच गरियो, र हामीले केहि फेला पार्न चाहेको बेला हामीलाई कुन डेटा उपलब्ध गराउने छनोट गर्छ। केहि परिस्थितिहरूमा, यो वास्तवमा के आवश्यक छ।
जीवनबाट एउटा उदाहरण। हामीले टास्क सेट गर्यौं
यस लेखलाई वास्तविक जीवनको उदाहरणको साथ चित्रण गरौं - बिक्री मेट्रिक्सको विस्तृत दायरामा आधारित आयोगहरू गणना गर्दै। हामी एक धेरै सरल विकल्पको साथ सुरु गर्नेछौं, र त्यसपछि हामी बिस्तारै यसलाई जटिल बनाउनेछौं जबसम्म समस्याको एकमात्र तर्कसंगत समाधान प्रकार्य प्रयोग गर्नु हुँदैन। VPR। हाम्रो काल्पनिक कार्यको लागि प्रारम्भिक परिदृश्य निम्नानुसार छ: यदि एक विक्रेताले एक वर्षमा $ 30000 भन्दा बढी बिक्री गर्छ भने, त्यसपछि उसको कमीशन 30% हुन्छ। अन्यथा, कमिसन मात्र 20% छ। यसलाई तालिकाको रूपमा राखौं:
बिक्रेताले सेल B1 मा आफ्नो बिक्री डेटा प्रविष्ट गर्दछ, र सेल B2 मा सूत्रले विक्रेताले अपेक्षा गर्न सक्ने सही कमीशन दर निर्धारण गर्दछ। फलस्वरूप, सेल B3 मा विक्रेताले प्राप्त गर्नुपर्ने कुल कमिशन गणना गर्न नतिजा दर प्रयोग गरिन्छ (केवल B1 र B2 कोषहरू गुणा गरेर)।
तालिकाको सबैभन्दा रोचक भाग सेल B2 मा समावेश छ - यो आयोग दर निर्धारण गर्न सूत्र हो। यो सूत्रमा एक्सेल प्रकार्य भनिन्छ IF (IF)। ती पाठकहरूका लागि जो यस प्रकार्यसँग परिचित छैनन्, म यो कसरी काम गर्दछ वर्णन गर्नेछु:
IF(condition, value if true, value if false)
ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)
स्थिति एक प्रकार्य तर्क हो जसले कुनै पनि को मान लिन्छ सही कोड (TRUE), वा गलत (FALSE)। माथिको उदाहरणमा, अभिव्यक्ति B1
के यो सत्य हो कि B1 B5 भन्दा कम छ?
वा तपाईं यसलाई फरक भन्न सक्नुहुन्छ:
के यो सत्य हो कि वर्षको लागि बिक्रीको कुल रकम थ्रेसहोल्ड मान भन्दा कम छ?
यदि हामीले यो प्रश्नको जवाफ दियौं हो, (TRUE), त्यसपछि प्रकार्य फर्काउँछ यदि सत्य हो भने मूल्य (मान यदि सत्य हो)। हाम्रो केसमा, यो सेल B6 को मूल्य हुनेछ, अर्थात् कुल बिक्री थ्रेसहोल्ड भन्दा तल हुँदा कमिसन दर। यदि हामीले प्रश्नको जवाफ दियौं NO (FALSE) त्यसपछि फर्कन्छ यदि गलत मान (यदि FALSE मान)। हाम्रो अवस्थामा, यो सेल B7 को मूल्य हो, अर्थात् कुल बिक्री थ्रेसहोल्ड भन्दा माथि हुँदा कमिसन दर।
तपाईले देख्न सक्नुहुन्छ, यदि हामीले $ 20000 को कुल बिक्री लियौं भने, हामीले सेल B2 मा 20% कमीशन दर पाउँछौं। यदि हामीले $40000 को मान प्रविष्ट गर्यौं भने, आयोग दर 30% ले परिवर्तन हुनेछ:
हाम्रो तालिका यसरी काम गर्दछ।
हामी कार्यलाई जटिल बनाउँछौं
चीजहरू अलि गाह्रो बनाउनुहोस्। अर्को थ्रेसहोल्ड सेट गरौं: यदि विक्रेताले $ 40000 भन्दा बढी कमाउँछ भने, त्यसपछि कमीशन दर 40% मा बढ्छ:
सबै कुरा सरल र स्पष्ट देखिन्छ, तर सेल B2 मा हाम्रो सूत्र उल्लेखनीय रूपमा अधिक जटिल हुन्छ। यदि तपाईंले सूत्रलाई नजिकबाट हेर्नुभयो भने, तपाईंले देख्नुहुनेछ कि प्रकार्यको तेस्रो तर्क IF (IF) अर्को पूर्ण प्रकार्यमा परिणत भयो IF (IF)। यस निर्माणलाई कार्यहरू एकअर्कामा नेस्टिङ भनिन्छ। एक्सेलले यी निर्माणहरूलाई खुसीसाथ अनुमति दिन्छ, र तिनीहरूले काम पनि गर्छन्, तर तिनीहरू पढ्न र बुझ्न धेरै गाह्रो हुन्छन्।
हामी प्राविधिक विवरणहरूमा गहिरिएर जानेछैनौं - किन र कसरी यसले काम गर्छ, र हामी नेस्टेड प्रकार्यहरू लेख्ने सूक्ष्मताहरूमा जानेछैनौं। आखिर, यो समारोहमा समर्पित लेख हो VPR, Excel को लागि पूर्ण गाइड होइन।
जे भए पनि, सूत्र थप जटिल हुन्छ! के हुन्छ यदि हामीले बिक्रीमा $ 50 भन्दा बढी कमाउने बिक्रेताहरूका लागि 50000% को कमीशन दरको लागि अर्को विकल्प प्रस्तुत गर्छौं। र यदि कसैले $ 60000 भन्दा बढी बेचेको छ भने, के तिनीहरूले 60% कमीशन तिर्नेछन्?
अब सेल B2 मा सूत्र, त्रुटि बिना लेखिएको भए पनि, पूर्ण रूपमा अपठनीय भएको छ। मलाई लाग्छ कि त्यहाँ थोरै छन् जो आफ्नो परियोजनाहरूमा 4 स्तरको नेस्टिंगको साथ सूत्रहरू प्रयोग गर्न चाहन्छन्। त्यहाँ सजिलो बाटो हुनुपर्छ?
र त्यहाँ यस्तो तरिका छ! समारोहले हामीलाई मद्दत गर्नेछ VPR.
हामी समस्या समाधान गर्न VLOOKUP प्रकार्य लागू गर्छौं
हाम्रो तालिकाको डिजाइन अलिकति परिवर्तन गरौं। हामी सबै समान क्षेत्र र डेटा राख्नेछौं, तर तिनीहरूलाई नयाँ, थप कम्प्याक्ट तरिकामा व्यवस्थित गर्नेछौं:
एक क्षण लिनुहोस् र नयाँ तालिका सुनिश्चित गर्नुहोस् दर तालिका अघिल्लो थ्रेसहोल्ड तालिका जस्तै डेटा समावेश गर्दछ।
मुख्य विचार प्रकार्य प्रयोग गर्न हो VPR तालिका अनुसार इच्छित ट्यारिफ दर निर्धारण गर्न दर तालिका बिक्री मात्रा मा निर्भर गर्दछ। कृपया ध्यान दिनुहोस् कि विक्रेताले तालिकामा पाँच थ्रेसहोल्डहरू मध्ये एक बराबर नभएको रकममा सामानहरू बेच्न सक्छ। उदाहरण को लागी, उसले $ 34988 मा बेच्न सक्छ, तर त्यहाँ त्यस्तो कुनै रकम छैन। कसरी कार्य गर्दछ हेरौं VPR यस्तो अवस्थामा सामना गर्न सक्छ।
VLOOKUP प्रकार्य सम्मिलित गर्दै
सेल B2 चयन गर्नुहोस् (जहाँ हामी हाम्रो सूत्र घुसाउन चाहन्छौं) र फेला पार्नुहोस् VLOOKUP (VLOOKUP) एक्सेल प्रकार्य पुस्तकालयमा: सूत्रहरू (सूत्रहरू) > प्रकार्य पुस्तकालय (प्रकार्य पुस्तकालय) > लुकअप र सन्दर्भ (सन्दर्भ र arrays)।
एउटा संवाद बाकस देखिन्छ प्रकार्य तर्क (प्रकार्य तर्कहरू)। हामी तर्कहरूको मानहरू एक-एक गरेर भर्छौं, सुरु गरेर लुकअप_मान (lookup_value)। यस उदाहरणमा, यो सेल B1 बाट बिक्रीको कुल रकम हो। फिल्डमा कर्सर राख्नुहोस् लुकअप_मान (Lookup_value) र सेल B1 चयन गर्नुहोस्।
अर्को, तपाईंले प्रकार्यहरू निर्दिष्ट गर्न आवश्यक छ VPRकहाँ डाटा खोज्न। हाम्रो उदाहरणमा, यो तालिका हो दर तालिका। फिल्डमा कर्सर राख्नुहोस् तालिका_एरे (तालिका) र सम्पूर्ण तालिका चयन गर्नुहोस् दर तालिकाहेडर बाहेक।
अर्को, हामीले हाम्रो सूत्र प्रयोग गरेर कुन स्तम्भमा डेटा निकाल्ने भनेर निर्दिष्ट गर्न आवश्यक छ। हामी कमीशन दरमा रुचि राख्छौं, जुन तालिकाको दोस्रो स्तम्भमा छ। त्यसैले, तर्कको लागि Col_index_num (स्तम्भ_संख्या) मान २ प्रविष्ट गर्नुहोस्।
र अन्तमा, हामी अन्तिम तर्क प्रस्तुत गर्दछौं - दायरा_लुकअप (Interval_lookup)।
महत्वपूर्ण: यो यस तर्कको प्रयोग हो जसले प्रकार्य लागू गर्ने दुई तरिकाहरू बीचको भिन्नता बनाउँछ VPR। डेटाबेस संग काम गर्दा, तर्क दायरा_लुकअप (range_lookup) को सधैं मान हुनुपर्छ गलत (FALSE) सही मेल खोज्नको लागि। हाम्रो प्रकार्य को प्रयोग मा VPR, हामीले यो क्षेत्र खाली छोड्नु पर्छ, वा मान प्रविष्ट गर्नुपर्छ सही कोड (TRUE)। यो विकल्प सही रूपमा चयन गर्न धेरै महत्त्वपूर्ण छ।
यसलाई स्पष्ट गर्न, हामी परिचय गर्नेछौं सही कोड (TRUE) क्षेत्रमा दायरा_लुकअप (Interval_lookup)। यद्यपि, यदि तपाईंले फिल्ड खाली छोड्नुभयो भने, यो त्रुटि हुनेछैन, किनकि सही कोड यसको पूर्वनिर्धारित मान हो:
हामीले सबै मापदण्डहरू भरेका छौं। अब हामी थिच्छौं OK, र Excel ले एउटा प्रकार्यको साथ हाम्रो लागि सूत्र सिर्जना गर्दछ VPR.
यदि हामीले कुल बिक्री रकमको लागि धेरै फरक मानहरू प्रयोग गर्छौं भने, हामी सूत्र सही रूपमा काम गर्छ भनेर सुनिश्चित गर्नेछौं।
निष्कर्ष
जब समारोह VPR डाटाबेस, तर्क संग काम गर्दछ दायरा_लुकअप (range_lookup) लाई स्वीकार गर्नुपर्छ गलत (FALSE)। र मान यस रूपमा प्रविष्ट गरियो लुकअप_मान (Lookup_value) डाटाबेसमा अवस्थित हुनुपर्छ। अर्को शब्दमा, यो एक सटीक मिलान खोज्दैछ।
उदाहरणमा हामीले यस लेखमा हेरेका छौं, सटीक मिलान प्राप्त गर्न आवश्यक छैन। यो मामला हो जब समारोह VPR इच्छित परिणाम फर्काउन अनुमानित मोडमा स्विच गर्नुपर्छ।
जस्तै: हामी $34988 को बिक्री मात्रा भएको विक्रेताको लागि आयोग गणनामा कुन दर प्रयोग गर्ने भनेर निर्धारण गर्न चाहन्छौं। समारोह VPR हामीलाई 30% को मान फर्काउँछ, जुन बिल्कुल सही छ। तर सूत्रले 30% वा 20% नभई ठ्याक्कै 40% भएको पङ्क्ति किन चयन गर्यो? अनुमानित खोज भन्नाले के बुझिन्छ? प्रष्ट होऔं।
जब तर्क दायरा_लुकअप (interval_lookup) को मान छ सही कोड (TRUE) वा छोडिएको, प्रकार्य VPR पहिलो स्तम्भ मार्फत दोहोर्याउँछ र लुकअप मान भन्दा बढी नहुने सबैभन्दा ठूलो मान चयन गर्दछ।
महत्त्वपूर्ण बिन्दु: यो योजनाले काम गर्नको लागि, तालिकाको पहिलो स्तम्भलाई बढ्दो क्रममा क्रमबद्ध गर्नुपर्छ।