03. april 2002 - 22:15Der er
18 kommentarer og 1 løsning
Excel nørderi - måske til Richardt
Hejsa
Jeg har igen et lille problem med Excel... :-)
Jeg har et privatregnskab med omkring 15 forskellige ark, som holder styr på mit regnskab, budget, bil mv. Det ene af disse ark er kontant kladden hvor jeg indtaster alle mine daglige indkøb detaljeret. Dvs. den 03.04.2002 i Føtex, på kvittering nr. 15, købte jeg 2 stk. kaffe 500g til en pris på 50 kr. Dette trækkes automatisk fra det beløb som jeg har sat til rådighedsbeløbet denne måned. Jeg benytter datavalideringsliste i varenavnet, men ellers manuel indtastning. I samme stil fortsætter mine linier ned af, og på et tidspunkt skifter datoen til f.eks. den 01.05.2002. Der er ikke nogen ophold i linierne ellers, så derfor er der kun datoen som idendikerer månedsskiftet.
Jeg har så et andet Statistik ark. I dette ville jeg gerne have Excel til at sammentæller hvor mange antal, af en given vare, og til hvilken samlet udgift - både pr. måned i en sektion og efterhånden som årsforbrug i en anden sektion. Umiddelbart kan man kun skelne månedsskift via datoen, som jeg nævnte.
Jeg har et eksempel ark, som kan fremsendes på mail, fordi mit endelige regnskabsfil fylder omkring 80-90 MB.
Det vil derfor være helt perfekt, hvis jeg kunne drage ud af retur dokumentet, hvorledes jeg selv skaber det samme i mit endelige dokument. Er der tips til forbedringer i øvrigt er disse meget velkomne :-)
Bemærk: Det er jo forskelligt hvor mange linier man taster i hver måned, så derfor skal statistikken være ligeså fleksibel.
Smart formel, Flemming. :-) den kan jeg god li'. Nu har jeg ikke set regnearket, men jeg synes det lyder som om en pivottabel ville være fornuftig, hvis man grupperer den på måneder.
En anden ting er at 80-90 MB lyder alt for stort til et alm. regnskab, selv over mange år. Hvad går der galt her ??
bak>> :-) brug den endelig :-) Jeg har heller ikke set regnearket, men der er formodenligt en del ark, med en del linier og mange formateringer. Ændringer af formateringer kan til tider "hænge" i arkene og fylde på filen. Rigtigt det lyder 70-80 MB for stort.
Løsningen på året er:=SUM.HVIS(Regnskab!E4:E45;G4;Regnskab!D4:D45)
Løsningen på månederne er straks lidt vanskeligere... Jeg er i den situation at jeg har arket og en pivottabel er desværre i den optimale løsning. Løsningen lugter mere af en datavalidering, hvor der hentes en måned og så en makro, der summmerer de enkelte poster pr måned eller en funktioen. Jeg kan bare ikke lige finde ud af det :-)
Det jeg mangler en en funktionen som Sum.Hvis, der har endnu et kriterie: SUM.HVIS(Regnskab!E4:E100;G4 og Måned = Valideringscellen;Regnskab!D4:D100)
Så er koden klar... Men den er desværre ikke så nem at overføre som jeg gerne ville have haft.
Koden til årsforbrug er bare en SUM.HVIS, som du kan kopiere længere ned i arket hvis du får flere varer
Koden til månedsforbrug er desværre noget mere avanceret. Jeg har indsat et datavalideringsfelt i celle B2. Når du har valgt måned trykker du på opdater. Det der så sker er at månedsområdet dynamisk bliver fundet og indsat i formlen (igen en SUM.HVIS), der så sættes ind i arket. Jeg ville godt have fundet en almindelig formel, som kunne dette her af sig selv, men det er ikke lykkedes - måske kender nogle af jer andre til en sådan fomel.
Jeg kunne godt bare selv have summeret det hele sammen uden en formel, men syntes at det var sjovt at finde ud af hvordan jeg indsætter en formel dynamisk - det er nemlig ikke bare sådan lige *S*
Koden: Sub Månedsforbrug() n = 0 X = 0 Y = 0 z = 4
'Tæller rækker der er brugt i arket statistik Sheets("Statistik").Activate For n = 4 To ActiveSheet.UsedRange.Rows.Count If Cells(n, 3) <> "" Then z = z + 1 End If Next n
'Tæller rækker der er brugt i arket Regnskab Sheets("Regnskab").Activate AntalRækker = ActiveSheet.UsedRange.Rows.Count
'Formaterer kolonne A til kun måned Columns("A:A").Select Selection.NumberFormat = "m"
'Finder første og sidste række efter den valgte måned X = 0 For n = 4 To AntalRækker If Mid(Cells(n, 1).Value, 4, 2) = Mid(Cells(3, 2).Value, 7, 2) Then If X = 0 Then X = n End If Y = n End If Next n
'Hvis der ingen poster er If X = 0 Then Sheets("Statistik").Activate AntalRækker = z - 1 For n = 4 To AntalRækker Sheets("Statistik").Cells(n, 2) = "" Sheets("Statistik").Cells(n, 4) = "" Next n Cells(4, 2).Select MsgBox "Der er ingen poster i " & Cells(2, 2).Value Exit Sub End If
'Indsætter en formel i kolonnen Antal Sheets("Statistik").Activate AntalRækker = z - 1 For n = 4 To AntalRækker Streng = "=SUM.Hvis('Regnskab'!E" & X & ":E" & Y & ",C" & n & ",'Regnskab'!D" & X & ":D" & Y & ")" Sheets("Statistik").Cells(n, 2) = Streng Sheets("Statistik").Cells(n, 2).Select SendKeys "{F2}", True SendKeys "{ENTER}", True Next n
'Indsætter en formel i kolonnen Udgift For n = 4 To AntalRækker Streng = "=SUM.Hvis('Regnskab'!E" & X & ":E" & Y & ",C" & n & ",'Regnskab'!G" & X & ":G" & Y & ")" Sheets("Statistik").Cells(n, 4) = Streng Sheets("Statistik").Cells(n, 4).Select SendKeys "{F2}", True SendKeys "{ENTER}", True Next n
'Formaterer kolonne A til almindelig dato Sheets("Regnskab").Activate Columns("A:A").Select Selection.NumberFormat = "m/d/yyyy"
Sheets("Statistik").Activate Cells(4, 2).Select End Sub
rvm>> Disse linier kunne de ikke snilt udskiftes med EN linie !! Streng = "=SUM.Hvis('Regnskab'!E" & X & ":E" & Y & ",C" & n & ",'Regnskab'!G" & X & ":G" & Y & ")" Sheets("Statistik").Cells(n, 4) = Streng Sheets("Statistik").Cells(n, 4).Select SendKeys "{F2}", True SendKeys "{ENTER}", True
nemli denne linie:
Sheets("Statistik").Cells(n, 4).FormulaR1C1 = _ "=SUM.Hvis('Regnskab'!E" & X & ":E" & Y & ",C" & n & ",'Regnskab'!D" & X & ":D" & Y & ")"
Det gælder forså for linierne med Cells(n, 2). Når der nu er SÅ mange rækker, så bør SELECT helt fjernes fra koden, da den gør makroen meget langsom, og her er Select helt unødvendig.
da formlen så ikke virker hos mig. Den skal lige opdateres og oversættes til noget Excel forstår - lidt på samme måde som når man optager indsættelse af en formel - så kommer der et helt andet resultat i koden. Det linierne gør, er at vælge den aktuelle celle - trukke F2 (for at opdatere cellen - og så trykke enter for at aktivere formlen igen. Måske er det bare mine computere, der kræver denne "høker"metode, men jeg så ingen anden løsning...
Det hele havde selvfølgelig været meget bedre, hvis der havde fandtes en Sum.hvis.hvis formel *S* Måske kender du een der kan bruges ?
Hej 247365. Er problemet løst så luk lige spørgsmålet.
Synes godt om
Ny brugerNybegynder
Din løsning...
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.
