Læsning af forskellige filer og overførsel af data

Prøv med denne i Ark3 celle B2, idet jeg går ud fra at ZZZ/YYY/ZZZ står i kolonne A2 til A4 ..., samt at A B C D E..., står i B1 til F1...:
=SUMPRODUKT((Ark1!$C$2:C$10=$A2)*(Ark1!$B$2:$B$10=B$1)*(Ark1!$A$2:$A$10))+SUMPRODUKT((Ark2!$C$2:C$10=$A2)*(Ark2!$B$2:$B$10=B$1)*(Ark2!$A$2:$A$10))

Den har jeg også selv tænkt på. Problemet er bare, at jeg ca har 100 ark og der kommer løbende flere til. Derfor bliver formlen alt for lang. Jeg skal altså være i stand til at aflæse alle arkene/filerne i en folder, og indsætte det samlede resultat i Ark3.

Det lyder som en oplagt opgave for en database. Det kan (formodentligt) godt håndteres i Excel, men det vil være noget lettere i f.eks. Access.

---> sjap, Hvordan er hastigheden, hvis man lader Access gøre arbejdet, og præsenterer det i Excel? Hvad med referencer, lookups etc.?

Uuuuh, det ved jeg ikke lige. Har ikke prøvet det. Men det drejer sig vel blot om at få importeret arkene i en (eller flere) tabeller, og så få lavet den forespørgsel, der laver summerne. Når data først er importeret går det ret hurtigt. Eksport tilbage til Excel er heller ikke noget problem.

qln > Er det muligt for dig at placere en sumproduktberegning (som tobler foreslog, men i en ret forenklet udgave) på hver enkelt ark - så kan den samlede sum laves meget simpelt efterfølgende. Nærmere forklaring/beskrivelse følger, hvis det har din interesse.

Det tror jeg nok , der er mulighed for.
Jeg har selv tænkt på Access, men ville høre om det kunne lade sig gøre i Excel.

Så kunne du lave sumprodukt i de enkelte ark f.eks. i et område udenfor input (f.eks. U2), og derefter summe resultaterne sammen, i dit totalark.
Prøv med denne sum: =sum('Ark1:Ark100'!U2) som summer alle U2'er i arkene Ark1 til Ark100!

Hvis du nu skriver følgende:

G1: A
H1: B
F2: XXX
F3: YYY
F4: ZZZ

G2: =SUMPRODUKT(($C$2:$C$500=$F2)*($B$2:$B$500=G$1)*($A$2:$A$500))

Formlen i G2 skal selvfølgelig tilpasses, så den passer til dine rækker (hvis du har mere end 500). Derefter kan du kopiere den til de øvrige celler. Du kan også lave flere overskrifter, og stadig bruge den samme formel.

Når du har gjort det, skal du så blot summe disse værdier i dit "total"-ark

=SUM(Ark1:Ark50!G2)

Dog kræver det regnekraft! Jeg sidder med et lignende projekt med en masse ark og indhold, ved seneste copy/replace stoppe tælleren ved 76206 celler, og derefter regnes der efter i ca. 3-4 min.
Derfor min interesse for en access løsning.

Det lyder også som en frygteligt masse data - og så kan SUMPRODUKT godt gå hen at blive lidt "tung". Nu har jeg ikke lige prøvet med denne her opgave, men jeg har arbejdet med datasæt med ca. 150.000 poster, hvor en forspørgsel stadig foretages på få sekunder.

Det er selvfølgelig hardware-afhængigt. Jeg kan huske at jeg har haft en næsten fuld harddisk - og så går det langsomt! (blev dog løst med en komprimering af datafilen). Som med alle andre programmer, så drejer det sig om hurtig processor, masser af RAM og rigeligt med diskplads (intet nyt under solen).

I eksemplet i nævner har i mange ark(sheets) i den samme fil(book). Kan man i stedet sumere forskellige filer, der ligger i det samme bibliotek?

Maskinen er ikke problemet, det er sumprodukt med fire variabler og derefter summering i diverse totalark.

gln > Ja, det kan du godt, men si vidt jeg ved, så slipper du ikke for at skulle skrive alle filnavnene - for du skal henvise til hver enkelt i din formel. Der findes ingen "smutvej" som findes ved en masse ark i den samme workbook - beklager.

tobler > Har du Access? Det er (for det meste) rimeligt nemt at importere regneark til Access, selvom det godt kan være lidt "morsomt" hvis man skal gøre det fra flere forskellige ark, og lægge dem sammen i én tabel.

Men jeg vil da foreslå dig at prøve det. Måske med et begrænset antal data i første omgang - bare indtil du finder ud af hvordan det fungerer (og om du synes det fungerer for dig).

gln -> det kommer an på din datastruktur. Hvis du kun har to kolonner i hver fil fx
Varenummer, Antal
kan du måske med fordel bruge Data/Konsolidering.

tobler, her er en gammel model der samler alle data på alle ark, og indsætter dem samlet i arket "samle".
Hvis du bruger den, (med et par modifikationer), vil du kunne lave en pivottabel der indeholder det du ønsker.
Bemærk at "Samle" og Pivotarket skal ligge tilsidst

Sub test2()
Dim x As Integer
Dim r As Range
Dim wsNew As Worksheet
Set wsNew = Worksheets("Samle")
For x = 1 To Worksheets.Count - 2
  With Sheets(x)
    If x = 1 Then
      Set r = .Range("a1:C" & .Range("A65536").End(xlUp).Row)
      r.Copy wsNew.Range("A65536").End(xlUp)
    Else
      Set r = .Range("a2:C" & .Range("A65536").End(xlUp).Row)
      r.Copy wsNew.Range("A65536").End(xlUp).Offset(1, 0)
    End If
  End With
Next
End Sub

sjap, ja jeg har access og er igang med at samle materiale og tanker, for om det kan være en løsning.
bak, jeg har f.eks. 50 ark, et pr. kunde, som jeg parer med op til 200 forskellige varegrupper fra salgsstatistikken for 2005 og 2004, det udføres med en sumprodukt med 4 parametre.
Jeg leger nu med ideen om ved ændring/tilføjelse af kunde-nr, at kopiere sumprodukt-formlerne ind og efter endt opdatering lave en copy/pastevalue, så jeg slipper for alle genberegningerne.

Så er min ide noget hurtigere. Makroer samler data hurtigt og hvis den samtidig definere et navn og på baggrund af dette opdater pivottabellen så er alt klaret i et snuptag.
Sumprodukt-formler æder meget hukommelse ....

Denne her er ligeglad med hvor Ark Samle og Ark Pivot ligger.
Der opfrisker også pivottabellen.
Sjap-> du kan ikke køre den i xl97, du vil hænge i nederste linie :-)
se eks.  http://www.tbdl.dk/excel/samle_data.xls

Sub test3()
  Dim x As Integer
  Dim r As Range
  Dim wsNew As Worksheet
  Dim bFirstSheet As Boolean
  Set wsNew = Worksheets("Samle")
  wsNew.Cells.ClearContents
  bFirstSheet = True
  For x = 1 To Worksheets.Count
      With Sheets(x)
        If .Name = wsNew.Name Or .Name = "Pivot" Then GoTo jump
        If bFirstSheet = True Then .Range("A1:C1").Copy wsNew.Range("A1")
        bFirstSheet = False
        Set r = .Range("A2:C" & .Range("A65536").End(xlUp).Row)
        r.Copy wsNew.Range("A65536").End(xlUp).Offset(1, 0)
      End With
jump:
  Next
  wsNew.Range("A1", wsNew.Range("A65536").End(xlUp).Offset(0, 2)).Name = "Data"
  Sheets("Pivot").PivotTables("PivotTable1").PivotCache.Refresh
End Sub

Jamen, så kører jeg ingen steder i dag :0)

Bak:
Jeg er lidt rusten i det med makroer.
Jeg får følgende fejlmeddelelse, når min pivot-tabel skal opdateres:
Unable to get PivotTable property of the Worksheet class.
Jeg har lavet en pivot tabel, der svarer til mine data, så det har helt sikkert noget at gøre med dette. Det er sikkert en meget fundamental fejl.