Temat: jak usprawnic/zmienic formule Suma.Iloczynow

Witam,

Obecnie uzywam ponizszej formuly Suma.Iloczynow, ktora jest formula tablicowa zeby policzyc calkowita ilosc poszczegolnych produktow dla danego tygodnia. Formula sprawdza numer tygodnia i numer produktu na obydwu plikach i nastepnie sumuje ilosci produktow na podstawie danych z pliku 2.

Formula dziala prawidlowo. Moj problem jest taki ze plik w ktorym znajduje sie ta formula ma ok 40 kolumn i ok 12 tys wierszy czyli jak latwo policzyc ta formula znajduje sie w 480.000 komorkach :) Plik 2 z ktorego dane sa pobierane dane na chwile obecna ma ok 9000 wierszy i obydwa pliki rosna kazdego dnia :)

Jak zatem sie nietrudno domyslec moj problem jest z czasem kalkulacji. Na chwile obecna to jest jakies 2 min. Musialem zmienic przeliczanie na manualne zeby mozna bylo jakos pliku uzywac. Nie jest to jednak optymalne rozwiazanie.

Zdaje sobie sprawe ze najlepiej byloby zmienic layout plikow itd niestety na chwile obecna to nie wchodzi w gre.

Zstanawiam sie zatem czy jest jakis sposob zeby usprawic ta formule albo zastapic ja czyms innym, mniej pamieciozernym.

Niestety nie moze uzyc SUMA.WARUNKÓW dlatego ze formula musi dzialac nawet jak Plik 2 jest zamkniety.

Ponizej zamieszczam formule i zrzuty ekranu dla obydwu plikow.

Bede wdzieczny za pomoc :)

{=SUMA.ILOCZYNÓW(--('I:\Plik 2'!$C$3998:$C$8209=AQ$7),--('I:\Plik 2'!$G$3998:$G$8209=$G10),'I:\Plik 2'!$I$3998:$I$8209)}

Plik 1 - z formula Suma.Iloczynow





Plik 2 - z ktorego formula Suma.Iloczynow pobiera dane do sumowania

Temat: jak usprawnic/zmienic formule Suma.Iloczynow

Witam

Marcin proponuję zainteresować się dodatkiem do Excel PowerPoivot, w którym otworzą się dla Ciebie możliwości formuł DAX, tworzenia własnych miar, wymiarów oraz relacji między danymi z wielu źródeł.
Ważną kwestią jest brak ograniczeń na ilość wierszy :)

http://www.pivotdashboard.com/Pages/PowerPivotExample....

Temat: jak usprawnic/zmienic formule Suma.Iloczynow

Witam

p. Wojtku oczywiście nie zawstydza mnie Pan taka informacją, gdyż nie chciałbym tutaj prowadzić dyskusji nad kwerendami i funkcjonalnością dodatku PowerPoivota.
Proponuję zastosować kwerendę do analizy danych z kilku źródeł z wykorzystaniem miar osadzonych w okresach czasowych miesiąc, rok, bieżący dzień itp, ważną kwestią jest również wydajność.

Osobiście korzystam z PowerPoivota i jestem zadowolony z oferowanej funkcjonalności, moim zdaniem dodatek jest bardzo dobrą alternatywą kostek OLAP, gdzie są one tworzone w tle.

Temat: jak usprawnic/zmienic formule Suma.Iloczynow

Miałem ten sam problem (dużo danych, w drugiej zakładce konieczność zagregowania danych, dużo index match i sumproduct.)... Tak jak Wojciech wykorzystałbym SQL.

MSQuery albo ACE SQL (driver Access), ja podążyłem tym drugim tropem.

W makro zawartym w pliku Excela występuje łańcuch połączenia ze źródłem danych a następnie zapytania SQL
SELECT x,y,sum(z) FROM nasza baza WHERE x=___, y>___
GROUP BY ___ DESC(ending)

Info np. tutaj (w sieci można znaleźć dużo przykładów):
http://stackoverflow.com/questions/8756802/excel-funct...Ten post został edytowany przez Autora dnia 24.06.14 o godzinie 12:39

Temat: jak usprawnic/zmienic formule Suma.Iloczynow

Np. coś takiego (nie powiem, że moje, potrafię użyć fragmenty kodu, które znajdę w sieci, przepraszam autorów):
Dane są w pliku tekstowym. Jego struktura opisana jest w schema.ini, ktory zawiera opis naszej bazy, np:
[test_database.csv]
ColNameHeader=True
Format=Delimited(;)
DateTimeFormat=yyyy-mm-dd
MaxScanRows=25
CharacterSet=OEM
Col1=NAME Char Width 100
Col2=REGION Char Width 2
Col3=VALUE Integer
Col4=DATE DateTime
Col5=DURATION Integer

W excelu zaś jest np. takie makro:

Sub QueryTextFile()

    

    'Set the path to subdirectory where your data resides, here it is relative:

    FilePath = ThisWorkbook.Path & "\test_db\"

  

    Dim sConnect As String

    

        sConnect = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source= " & FilePath & "; Extended Properties=Text;"

               

    Dim sSql As String

        

        sSql = "SELECT Name, Region, Date, Duration, " & _

        "sum(IIf(Value> " & Sheets("Output").Range("I12").Value & " AND Value< " & Sheets("Output").Range("I13").Value & " ,Value,0)) AS SumSales " & _

        "FROM  test_database.csv  " & _

        "WHERE Date BETWEEN #" & Sheets("Output").Range("I15").Value & "# and #" & Sheets("Output").Range("I16").Value & "# " & _

        "AND Region = '" & Sheets("Output").Range("I17").Value & "' " & _

        "OR Region = '" & Sheets("Output").Range("K17").Value & "' " & _

        "GROUP BY " & Sheets("Output").Range("I18").Value

    

    Dim rsData As ADODB.Recordset

        

        Set rsData = New ADODB.Recordset

    

    rsData.Open sSql, sConnect, adOpenForwardOnly, adLockReadOnly, adCmdText

    

     

    If Not rsData.EOF Then

        Sheet2.Range("A10", Cells(Rows.Count, "E").End(xlUp)).ClearContents      'Clear area where query result is dumped

        Sheet2.Range("A10").CopyFromRecordset rsData                             'Insert new data from recordset to xls

        Sheet2.UsedRange.EntireColumn.AutoFit

        

        Call ert

    Else

        MsgBox "No Records Returned", vbCritical

    End If

    

    rsData.Close

    Set rsData = Nothing

     

End Sub



Sub ert()                                                                         'Macro shrinks table if there are empty rows. Very useful.

                                                                                  'http://www.excelforum.com/excel-programming-vba-macros/847446-auto-resize-shrink-table-on-an-active-sheet.html

Dim lo As ListObject

Application.ScreenUpdating = False

On Error Resume Next 'if SpecialCells(4) not exists

For Each lo In ActiveSheet.ListObjects

    With lo

        .ListColumns(1).Range.SpecialCells(4).Resize(, .ListColumns.Count).Delete shift:=xlUp

    End With

Next lo

Application.ScreenUpdating = True

End Sub

Ten post został edytowany przez Autora dnia 24.06.14 o godzinie 12:54