excel एक्सेल: बार-बार मूल्यों के आधार पर कॉलम और समूह का चयन कैसे करें?(1 डी टू 2 डी तालिका)




excel-formula excel-2010 (4)

SQL में सशर्त एकत्रीकरण क्वेरी की क्लासिक आवश्यकता आपका प्रारंभिक उदाहरण इकाई-विशेषता-मान मॉडल दर्शाता है फ़ील्ड_नाम कॉलम पर सशर्त रूप से अलग-अलग कॉलमों को ठीक से संरेखित करने के लिए, दोहराए हुए कॉलमों के समूहीकृत मान के अधिकतम (केवल मान) लौटकर (अन्य सभी दोहराने वाले स्तंभों के लिए SELECT और GROUP BY जोड़ें):

सशर्त समग्र प्रश्न

SELECT Request_ID, Status, 
       MAX(IIF(Field_Name='Name', Value, NULL)) AS Name,
       MAX(IIF(Field_Name='Acc Number', Value, NULL)) AS [Acc Number],
       MAX(IIF(Field_Name='Date', Value, NULL)) AS [Date],
       MAX(IIF(Field_Name='Enquiry Type', Value, NULL)) AS [Enquiry Type]
       MAX(IIF(Field_Name='Reason', Value, NULL)) AS [Reason]
FROM [Worksheet$]
GROUP BY Request_ID, Status

अगर पीसी के लिए एक्सेल का उपयोग करना है, तो आप वीबीए में कार्यपुस्तिका पर एसक्यूएल क्वेरी को चलाने के लिए एसीई एसक्यूएल इंजन (विंडोज़ .डीएल फाइल) का उपयोग कर सकते हैं। मैक का उपयोग करते हुए, डेटा को SQLite जैसे डेटा आयात करें और उपरोक्त क्वेरी ( IIF() के CASE ) CASE स्टेटमेंट के लिए। 700,000+ रिकॉर्ड के लिए, एसक्यूएल इनलाइन फ़ार्मुलों या लूप और एरे के for नेस्टेड की तुलना में एक मजबूत समाधान हो सकता है।

नीचे विंडोज़ एडीओ (दो कनेक्शन उपलब्ध है) का उपयोग करते हुए समाधान है जहां डेटा क्वेरी टैब के लिए रिक्त टैब के साथ एक डेटा टैब में रहता है।

Sub RunSQL()
    Dim conn As Object, rst As Object
    Dim strConnection As String, strSQL As String, i As Integer

    Set conn = CreateObject("ADODB.Connection")
    Set rst = CreateObject("ADODB.Recordset")

    ' CONNECTION STRINGS (DRIVER AND PROVIDER)
'    strConnection = "DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" _
'                      & ActiveWorkbook.FullName ";"
    strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" _
                       & "Data Source='" & ActiveWorkbook.FullName & "';" _
                       & "Extended Properties=""Excel 8.0;HDR=YES;"";"

    strSQL = " SELECT Request_ID, Status," _
               & "    MAX(IIF(Field_Name='Name', Value, NULL)) AS Name," _
               & "    MAX(IIF(Field_Name='Acc Number', Value, NULL)) AS [Acc Number]," _
               & "    MAX(IIF(Field_Name='Date', Value, NULL)) AS [Date]," _
               & "    MAX(IIF(Field_Name='Enquiry Type', Value, NULL)) AS [Enquiry Type]," _
               & "    MAX(IIF(Field_Name='Reason', Value, NULL)) AS [Reason]" _
               & " FROM [DATA$]" _
               & " GROUP BY Request_ID, Status;"            
    ' OPEN CONNECTION AND RECORDSET
    conn.Open strConnection
    rst.Open strSQL, conn

    ' HEADERS
    For i = 0 To rst.Fields.Count - 1
        Worksheets("RESULTS").Cells(1, i + 1) = rst.Fields(i).Name
    Next i           
    ' DATA ROWS
    Worksheets("RESULTS").Range("A2").CopyFromRecordset rst

    rst.Close: conn.Close
End Sub

वैकल्पिक रूप से, Field_Name की चर संख्या के लिए, आप Field_Name एसक्यूएल की अद्वितीय क्रॉसस्टैब क्वेरी का उपयोग कर सकते हैं जो कड़ी मेहनत के मूल्य की संभावनाओं को Field_Name जैसे कि उपरोक्त कुल में Field_Name । और क्योंकि एसीई एसक्यूएल 255 के कॉलम की सीमा तक, क्वेरी से नीचे केवल 253 या Field_Name दो अलग-अलग मानों को वापस कर सकते हैं (दोहराए गए समूह द्वारा कॉलम के लिए):

क्रॉसस्टैब क्वेरी

strSQL = " TRANSFORM Max(Value)" _
           & " SELECT Request_ID, Status" _
           & " FROM [DATA$]" _
           & " GROUP BY Request_ID, Status" _
           & " PIVOT Field_Name;"

डेटा

परिणाम

मैं लेनदेन के डेटा डंप के साथ काम कर रहा हूं जो विशेष उद्देश्य से किसी भी उद्देश्य के लिए बहुत ही अनुकूल स्वरूप में निर्यात नहीं किया जाता है।

Excel में डेटा वर्तमान में लगभग 700,000 पंक्तियों से बना है, जबकि एक बार सही ढंग से संगठित होने पर केवल 70,000 होनी चाहिए, जहां एक विशेष स्तंभ के अनूठे मूल्य कुछ अतिरिक्त कॉलम में ट्रांसक्यूट किए जाते हैं।

वर्तमान में मैं नीचे इस (सरलीकृत) उदाहरण की तरह कुछ के साथ काम कर रहा हूं;

Request_ID   Status       Field_Name       Value
01000        Rejected     Name             John Smith
01000        Rejected     Acc Number       123456
01000        Rejected     Date             1/12/2015
01000        Rejected     Enquiry Type     Type 1
01000        Rejected     Reason           Reason 1
01001        Completed    Name             Jane Jones
01001        Completed    Acc Number       123457
01001        Completed    Date             1/12/2015
01001        Completed    Enquiry Type     Type 2
01001        Completed    Reason           Reason 2

प्रत्येक व्यक्तिगत अनुरोध से संबंधित डेटा की कई पंक्तियों को पूरा करने के लिए पहले दो कॉलम ( अनुरोध_आईडी और स्थिति ) केवल दोहराए गए मान हैं।

Field_Name कॉलम एक विशिष्ट कॉलम प्रत्येक अद्वितीय Request_ID के लिए व्यक्तिगत पंक्तियों पर समान मानों का एक ही सेट को दोहराता है- और मान कॉलम प्रत्येक Field_Name से संबंधित संबंधित मान को सूचीबद्ध करता है।

यह स्तंभ 3 और 4 ( फ़ील्ड_नाम और मान ) है, जिसे मैं नीचे की तरह कुछ उत्पादन करने के लिए पंक्तियों में स्थानांतरित करना चाहता हूं:

Request_ID  Status     Name          Acc Number    Date       Enquiry Type    Reason
01000       Rejected   John Smith    123456        1/12/2012  Type 1          Reason 1
01001       Completed  Jane Jones    123457        1/12/2012  Type 2          Reason 2

जैसा कि आप देख सकते हैं, ऊपर का दूसरा उदाहरण अधिक तार्किक और कम बेमानी डेटा के साथ-साथ सरल फ़िल्टरिंग और विश्लेषण के लिए अनुमति देगा।

इसलिए, कॉल का मेरा पहला बिंदु डेटा को एक पिवटटबल में डालने से उपरोक्त हेरफेर को लागू करने का प्रयास करना था, लेकिन मैं तर्क को नहीं समझ सका। या अगर मैं एक समान स्तंभ स्थिति में खेतों को प्राप्त कर पा रहा था, तो मूल्य केवल टेबल के शरीर की गणना की जाती है, बजाय मूल्य (पीवोटैबल्स की प्रकृति) के बजाय।

क्या ट्रांसस्ज़ेस फ़ंक्शन का उपयोग करना संभव है? (जो यदि हां, तो मैं स्वयं की जांच के लिए खुश हूं)

क्या कोई वैकल्पिक हल है जिसे मैंने नहीं माना है?

( साइड नोट- मेरे डेटा डंप में अतिरिक्त कॉलम हैं जो मैंने उदाहरण में शामिल नहीं किए हैं, क्योंकि वे उन मूल्यों को शामिल करते हैं जिन्हें एक व्यक्तिगत Request_ID से संबंधित प्रत्येक पंक्ति के लिए बस दोहराया जाता है - इसलिए मुझे लगता है कि मैं किसी भी समाधान के लिए उसी तर्क का उपयोग कर सकता हूँ ऊपर)

यदि आप मुझे सही दिशा में इंगित कर सकते हैं, या कोई मार्गदर्शन प्रदान कर सकते हैं, तो मुझे बहुत अधिक सराहना होगी क्योंकि मैं स्टम्प्ड हूं।


किसी भी सेल को चुनें, जी 6 कहें और दर्ज करें:

=INDEX(A:A,(ROWS($1:1)-1)*5+2)

और कॉपी करें H6 में दर्ज करें:

=INDEX(B:B,(ROWS($1:1)-1)*5+2)

और कॉपी करें अंत में I6 दर्ज करें:

=OFFSET($D$2,COLUMNS($A:A)-1+(ROWS($1:1)-1)*5,0)

और इस सेल को पूरे और नीचे दोनों कॉपी करें:


मान लें कि आपका डेटा कॉलम ए में है: डी। कॉलम की कॉलम को कॉपी करें। जीडी का उपयोग करें एक्सेल Excel अनुरोध को अनूठे अनुरोध आईडी प्राप्त करने के लिए निकालें। कर्नल एच से नाम, एसीसी संख्या आदि जैसे सभी संभव फील्ड नामों के लिए हेडर बनाएं और नीचे दिए गए सूत्र का उपयोग करें।

H2 =VLOOKUP(G2,$A$2:$D$11,2,FALSE)

I2 =INDIRECT(ADDRESS(SUMPRODUCT(--($A$2:$A$11=$G2)*--($C$2:$C$11=I$1)*ROW($A$2:$A$11)),4))

अन्य सभी फ़ील्ड नामों के लिए I2 सूत्र खींचें I

यहां एक Google शीट है

मैंने आपके द्वारा पोस्ट किए गए डेटा के साथ प्रयास किया है और ठीक काम करता है। यदि आप पूरी प्रक्रिया को स्वचालित करना चाहते हैं, तो आप इन सभी चरणों के लिए मेक्रो को रिकॉर्ड करने का प्रयास कर सकते हैं।


Excel के आपके संस्करण के आधार पर, आप डेटा को उचित रूप से धुएं करने के लिए या तो Power Pivot (2010-2013) या Get & Transform (2016) का उपयोग कर सकते हैं आपका डेटा, यदि तालिका में पहले से नहीं है, तो उसे एक में रूपांतरित किया जाएगा।

बाद के लिए, From Table चयन करना क्वेरी संपादक को खोलता है। फ़ील्ड नाम और मूल्य कॉलम चुनने के बाद, TransformPivot Column चयन करें

यह एक पिवट कॉलम संवाद लाएगा। आप यह सुनिश्चित करना चाहते हैं कि चयन नीचे दिए गए हैं do not aggregate विकल्प को प्राप्त करने के do not aggregate आपको advanced भी चुनना होगा।

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

मुझे यकीन नहीं है कि यह 700,000 पंक्तियों के साथ कैसे काम करेगा आपको 64-बिट एक्सेल की आवश्यकता हो सकती है

हालांकि, अन्य प्रतिक्रियाओं के लिए टिप्पणियों को देखते हुए, यह समाधान Field Name / Value जोड़े की संख्या के साथ काम करना चाहिए।





transpose