advanced - excel formulas formulas and functions with microsoft excel 2003




Excel: एक SUMIF करें जहां मापदंड एक अल्पविराम-सीमांकित सूची है (3)

अल्पविराम के बजाय स्तंभों के साथ मानदंडों को सीमांकित करने पर विचार करें, फिर आप कुछ सुमीफ फ़ार्मुलों को जोड़ सकते हैं। मानदंड को सीमांकित करना एक बुरा विचार है।

संपादित करें: वास्तव में, क्यों नहीं दर्जनों कॉलम का उपयोग करें? Excel में उनमें से बहुत से हैं, तो आइए उनका उपयोग करें। एक कॉलम में अल्पविराम से सीमांकित सूची डालें। शायद एक अलग शीट पर भी। अलग-अलग स्तंभों में मानों को अलग करने के लिए पाठ से कॉलम का उपयोग करें एक सौ के लिए अनुमति दें फिर प्रत्येक स्तंभ के लिए एक एकल योग सूत्र के साथ सौ कॉलम जोड़ें। बहुत ही सही पर, सभी सममित बयानों को कुल करने के लिए योग सूत्र का उपयोग करें।

आप टेक्स्ट को कॉलम की बात करने के लिए VBA का उपयोग कर सकते हैं और स्तंभों को विभाजित मूल्यों और स्तंभों के साथ सूत्रों के साथ छुपा सकते हैं, जिससे आपको एक कॉलम के साथ एक कॉलम के बगल में अपने संबंधित योगों के साथ एक स्तंभ के साथ छोड़ दिया जाता है।

मेरी पहली Excel कार्यपत्रक में, मेरे पास आईडी और मूल्यों की एक तालिका है:

ID  VALUE
1   30
2   35
3   14
4   92

मेरी दूसरी वर्कशीट में, मैं एक कॉलम में आईडी की अल्पविराम से सीमांकित सूची प्रदान करूंगा, और दूसरे कॉलम में, मुझे उन ID की मानों की गणना राशि, जैसे कि:

IDs     SUM
1,2     65
2,3,4   141
3       14

मैं दूसरे वर्कशीट के एसओएम कॉलम के लिए फार्मूला कैसे लिखूँगा? यदि संभव हो तो मैं VBA से बचना चाहूंगा


आप इसे प्राप्त करने के लिए उपयोगकर्ता परिभाषित फ़ंक्शन का उपयोग कर सकते हैं।

Function GetSum(r As String) As Long
    Dim str1, str2
    Dim ws1 As Worksheet, ws2 As Worksheet
    Set ws1 = Worksheets("Sheet1")
    Set ws2 = Worksheets("Sheet2")
    Dim d As Long
    str1 = Split(r, ",")
    str2 = UBound(str1)
    For i = 0 To str2
        d = d + WorksheetFunction.SumIf(ws1.Range("A:A"), str1(i), ws1.Range("B:B"))
    Next i
    GetSum = d
End Function

शीट 1 और शीट 2 दोनों में अपना डेटा मानते हुए ए 2 के साथ शुरू होता है, तो वर्कशीट 2 बी 2 में नीचे दिए गए सूत्र को लागू करें

=getsum(A2)


यह मानते हुए, उदाहरण तालिकाओं के लिए जो आप देते हैं:

1) आपकी स्रोत तालिका Sheet1!A1:B5 (1 पंक्ति में हेडर के साथ) में है

2) आपकी परिणाम तालिका, Sheet2!A1:B4 (1 पंक्ति में हेडर के साथ) रेंज में है

3) परिणाम तालिका के स्तंभ ए में प्रविष्टियों के लिए सीमांकक केवल एक एकल अल्पविराम है

फिर, Sheet2!B2 में Sheet2!B2 , सरणी सूत्र **:

=SUM(IF(ISNUMBER(FIND(","&Sheet1!A$2:A$5&",",","&A2&",")),Sheet1!B$2:B$5))

नीचे B4 कॉपी करें

सादर

** सरणी फ़ार्मुलों को 'मानक' फार्मूले के समान नहीं दर्ज किया गया है। बस एन्टर दबाकर, आप पहले CTRL और SHIFT दबाकर रखें, और उसके बाद केवल ENTER दबाएँ यदि आपने इसे सही तरीके से किया है, तो आप एक्सेल को सूत्र के चारों ओर घुंघराले ब्रैकेट {} को बताएंगे (हालांकि मैन्युअल रूप से इन्हें खुद को डालने का प्रयास नहीं करते हैं)।





excel-formula