14. august 2007 - 18:04Der er
60 kommentarer og 1 løsning
SUM.HVISER hvor en af flere kriterier skal være opfyldt
Jeg har Excel 2007 og her er en for mig ny formel, nemlig SUM.HVISER(sumområde,kriterieområde1,kriterium1,kriterieområde2, kriterium2…)
Jeg har to kriterier der skal være opfyldt for at Excel skal summere nogle tal.
Jeg vil gerne hvis kriterium 1 kan være flere kriterier, hvor den skal summere hver gang et af disse er opfyldt. Det samme er tilfældet for kriterium 2.
For at være lidt mere konkret så har jeg et regneark der er bygget således op:
Celle A4:D4 udgør forskellige varenumre som er kriterium 1. Celle E4:H4 udgør forskellige lokationskoder som er kriterium 2. Kollone K er kriterieområde til kriterie 1 (K4:K6932) Kollone O er kriterieområde til kriterie 2 (O4:O6932) Kollone P er sumområdet (P4;P6932)
Jeg kunne godt tænke mig at man kunne lave følgende formel:
Problemet er bare at det bliver en meget lang formel på denne måde. Herudover kan man ikke umiddelbart tilføje kriterier der skal medtaget i summen uden at skulle rette i hver enkelt formel.
Det er mange steder jeg har brug for at lave denne formel. Kan man evt. benytte matrix-formler. Det er ikke noget jeg har brugt før, så jeg ved ikke ret meget om hvordan det fungerer.
Så skal Excel summere f.eks. udfra 2 kriterier: Varenr. 110 og 120 og 140 skal medtaget hvis kriterie 2 ligeledes er opfyldt. Lokation L2 og L4 (resultat 2.750)
Jeg ved godt man godt kan lave det af flere omgange men konkret er der tale om optil 10 varenr. samtidig med optil 6 lokationer der skal summeres.
Læg mærke til at kriterierne i sum.hviser i følge forklarigen er med "og", hvilket vil sige at alle kriterier skal være sande for at en celles værdi tages med. Den formel du lægger ud med ville (hvis det virkede med et range af off-set værdi, hvilket det ikke gør) forudsætte et "eller" mellem kriterierne; at bare en eller en kombination skulle være sand. Derfor er løsningen med de fire +'er den rigtige, idet den adskiller de enkelte sande udsagn. Dog er de eventuelle kombinationer ikke med, så du ville ende op med 16 +'er istedet for fire. En anden idé kunne måske derfor være at benytte autofilter og så sætte et custom filter ind for varenr og derefter for lokationskode. En subtotal i toppen =subtotal(9;C2:C6) vil derefter kun summere de synlige værdier. 2003 (som jeg har) har dog kun mulighed for to custom filtre, så man må være lidt kreativ med wildcards eller ranges af varenr. Kunne dog tænke mig at 2007 havde et udvidet antal af custom filtre ... forhåbentlig. Om autofilter ikke er en mulighed ville jeg overveje en pivottabel, navnlig i dette tilfælde hvor du har et vist antal af off-set værdier, samt kombinationer heraf. Lotus123 havde en mulighed for at lave en LIST, som kunne indeholde en række off-set værdier - denne funktion har jeg dog desværre aldrig fundet i xl :o( Men en forsimpling af din liste, som jeg ofte benytter mig af, er at kombinere de enkelte off-set værdier til et. I dette tilfælde lav en ny kolonne med =A2&B2 og kopier der ned af. Så har du kun et off-set nummer du skal arbejde med istedet for to. Håber du kan bruge lidt af ovenstående til inspiration. Hilsen Luffe
Tak for de gode forslag. Jeg tror dog desværre ikke helt de dur til mit konkrete tilfælde.
Jeg kopierer datamængden fra Navision til Excel - hvilket skal gøres månedsvis (ca. 7000 linier pr. måned). Herefter skal salgstal mv. fordeles på forskellige forretningsområder (består af bestemte varenr. afhængig af forretningsområdet) samt yderligere fordeles på lokationer.
Derved vil det være forskelligt fra område til område hvilket autofilter der skal bruges. Jeg mener derfor ikke umiddelbart det kan bruges. Det samme må være tilfælet for en pivot-tabel.
Jeg vil jo gerne have at man for hvert forretningsområde taster varenumre og afd. koder og så skal Excel summere udfra disse kriterier. Det kan godt ske det ikke kan lade sig gøre på den måde, men det kan evt. være nødvendigt at lave regnestykket ad flere gange.
Personligt ville jeg trække det over i Access fra Navision og derfra over i Excel, men det er vel smag og behag. Hvis man kan fra Navision via noget ODBC eller lignende, det skal jeg ikke gøre mig klog på.
En anden løsning ville være at gøre det med et stykke kode og en lille knap.
Men det er måske fordi jeg læser din opgave forkert.
Det bliver svært at lave uden en lang formel, især når man ikke har 2007 til sin rådighed :o)
Her er en funktion jeg tror gør det du eftersøger. Den kaldes blot med formlen =bla(A4;B4;C4;D4;E4;F4;G4;H4)
Function bla(a, b, c, d, e, f, g, h) Dim s_lin, i As Integer Dim vnr, lok As Variant bla = 0 s_lin = Sheets("Ark2").Range("K30000").End(xlUp).Row For i = 4 To s_lin vnr = Sheets("Ark2").Range("K" & i) lok = Sheets("Ark2").Range("O" & i) Select Case vnr Case Is = a, b, c, d Select Case lok Case Is = e, f, g, h bla = bla + Sheets("Ark2").Range("P" & i) Case Else End Select Case Else End Select
Hvis du trykker Alt+F11 skulle du gerne få Visual Basic kodevinduet frem. Vælg insert module og kopier koden ind. I en formel skriver du så =bla(A4;B4;C4;D4;E4;F4;G4;H4)
Men det virker naturligvis kun hvis jeg har forstået opgaven korrekt :o)
Function bla(a, b, c, d, e, f, g, h) Dim s_lin, i As Integer Dim vnr, lok As Variant bla = 0 s_lin = Sheets("Ark2").Range("K30000").End(xlUp).Row For i = 4 To s_lin vnr = Sheets("Ark2").Range("K" & i) lok = Sheets("Ark2").Range("O" & i) Select Case vnr Case Is = a, b, c, d Select Case lok Case Is = e, f, g, h bla = bla + Sheets("Ark2").Range("P" & i) Case Else End Select Case Else End Select Next i End Function
Den skriver stadig det samme. Hvis jeg klikker på funktionen for at redigere kan jeg se den skriver #VÆRDI! udfor E4, som er den først celle der er en tekst istedet for tal.
Prøv denne, kriterierne kan være et område, men kun på en række, gerne flere kolonner. Data for området der kikkes på er: Kollone K er kriterieområde til kriterie 1 (K4:K6932) Kollone O er kriterieområde til kriterie 2 (O4:O6932) Kollone P er sumområdet (P4;P6932)
Public Function FindSum(Kriterie1 As Range, Kriterie2 As Range) Dim Find1 As Variant, Find2 As Variant Dim Data As Variant, I As Integer, J As Integer, M As Long, RW As Long RW = Range("K30000").End(xlUp).Row Find1 = Kriterie1 Find2 = Kriterie2 Data = Range("K2:P" & RW)
For M = 1 To UBound(Data, 1) For I = 1 To UBound(Find1, 2) If Not IsEmpty(Find1(1, I)) Then If Find1(1, I) = Data(M, 1) Then Data(M, 1) = "+"
Exit For End If End If Next
For J = 1 To UBound(Find2, 2) If Not IsEmpty(Find2(1, J)) Then If Find2(1, J) = Data(M, 5) Then Data(M, 5) = "+" Exit For End If End If Next If Data(M, 1) = "+" And Data(M, 5) = "+" Then FindSum = FindSum + Data(M, 6) Next End Function
Nu er jeg ikke den store VBA ørn, så jeg kan ikke gennemskue om de ovenstående koder vil kunne løse dit problem. Men som jeg ville løse det tror jeg nu stadig en pivot ville være et brugbart værktøj. Hvis man skabte en kolonne som før nævnt med kombinationen af varenr og lokation og havde denne som unik nøgle, ville jeg i forlængelse skabe en hierarki liste i to eller flere kolonner, som f.eks: 120L2 Tyskland Proffesional 110L2 Tyskland Proffesional 105L1 Belgien Consumer en master data liste der indeholdt de forskellige kombinationer som du kunne uddrage og som du kunne tænkes at opsummere materialet i. Herefter en lookup i data materialet til den eller de kolonner som du havde listet, hvorefter pivottabeller kunne bygges til alle dine forskellige opsummerings formål. Master data listen skal selvfølgelig vedligeholdes hver måned for nye kombinationer af varenr og lokation, men dine pivottabeller skulle blot have en refresh. Blot en idé (du har måske gættet at jeg synes pivottabeller er et fremragende værktøj :o)) Hilsen Luffe
Jeg går udfra det er den samme fremgangsmåde som ovenstående, nemlig Alt + F11 og så kopier ovenstående. Skal funktionen så hedde =public function findsum(A4:D4;E4:H4)
hvis kriterie 1 er i cellerne A4:D4 og kriterie 1 er i cellerne E4:H4?
Den skriver stadig #NAVN?
Hvis jeg i cellen skriver =findsum(A4:D4;E4:H4) istedet giver det resultatet 0,00
har du dine data i Kollone K er kriterieområde til kriterie 1 (K4:K6932) Kollone O er kriterieområde til kriterie 2 (O4:O6932) Kollone P er sumområdet (P4;P6932)
Er kolonne P og K rene talværdier og ikke tal som tekst Er Kolonne O renset for uønskede mellemrum, så der kan sammenlignes, med det der står i E4:H4
ja Du skal skrive med stort ligesom i kolonne O, der er jo forskel på store og små bogstaver, det SKAL være ens, men jeg laver lige koden om, så den laver alt med stort,
ublic Function FindSum(Kriterie1 As Range, Kriterie2 As Range) Dim Find1 As Variant, Find2 As Variant Dim Data As Variant, i As Integer, J As Integer, M As Long, RW As Long RW = Range("K30000").End(xlUp).Row Find1 = Kriterie1 Find2 = Kriterie2 Data = Range("K2:P" & RW)
For M = 1 To UBound(Data, 1) For i = 1 To UBound(Find1, 2) If Not IsEmpty(Find1(1, i)) Then If Find1(1, i) = Data(M, 1) Then Data(M, 1) = "+"
Exit For End If End If Next
For J = 1 To UBound(Find2, 2) If Not IsEmpty(Find2(1, J)) Then If UCase(Find2(1, J)) = UCase(Data(M, 5)) Then Data(M, 5) = "+" Exit For End If End If Next If Data(M, 1) = "+" And Data(M, 5) = "+" Then FindSum = FindSum + Data(M, 6) Next End Function
Public Function FindSum(Kriterie1 As Range, Kriterie2 As Range) Dim Find1 As Variant, Find2 As Variant Dim Data As Variant, i As Integer, J As Integer, M As Long, RW As Long RW = Range("K30000").End(xlUp).Row Find1 = Kriterie1 Find2 = Kriterie2 Data = Range("K2:P" & RW)
For M = 1 To UBound(Data, 1) For i = 1 To UBound(Find1, 2) If Not IsEmpty(Find1(1, i)) Then If Find1(1, i) = Data(M, 1) Then Data(M, 1) = "+"
Exit For End If End If Next
For J = 1 To UBound(Find2, 2) If Not IsEmpty(Find2(1, J)) Then If UCase(Find2(1, J)) = UCase(Data(M, 5)) Then Data(M, 5) = "+" Exit For End If End If Next If Data(M, 1) = "+" And Data(M, 5) = "+" Then FindSum = FindSum + Data(M, 6) Next End Function
Public Function FindSum(Kriterie1 As Range, Kriterie2 As Range) Dim Find1 As Variant, Find2 As Variant Dim Data As Variant, i As Integer, J As Integer, M As Long, RW As Long RW = Range("K30000").End(xlUp).Row Find1 = Kriterie1 Find2 = Kriterie2 Data = Range("K2:P" & RW)
For M = 1 To UBound(Data, 1) For i = 1 To UBound(Find1, 2) If Not IsEmpty(Find1(1, i)) Then If Find1(1, i) = Data(M, 1) Then Data(M, 1) = "+"
Exit For End If End If Next
For J = 1 To UBound(Find2, 2) If Not IsEmpty(Find2(1, J)) Then If UCase(Find2(1, J)) = UCase(Data(M, 5)) Then Data(M, 5) = "+" Exit For End If End If Next If Data(M, 1) = "+" And Data(M, 5) = "+" Then FindSum = FindSum + Data(M, 6) Next End Function
du skal vide at det kaldes en brugerdefineret funktion, og den er programmeret specielt til det ark den virker på. Men du lærer jo nok at ændre koden, så du kan bruge den andre steder også.
Det kunne være man skulle overveje et kursus i området på et tidspunkt. Jeg arbejder meget i Excel på mit arbejde, men VBA mv. har jeg aldrig lært noget om så det kunne være spændende!
Hvis du er seriøs omkring kursus vil jeg anbefale PeopleNet. Jeg har selv været på et kursus med Roland Greger og ham kan jeg kun anbefale. Det er jo ikke gratis, men hvis der er nogle stykker på dit arbejde der er interesserede så ved man aldrig.
Hvordan kan jeg redigere i nedenstående således den tager sum i kollone Q i stedet for P?
Public Function FindSum(Kriterie1 As Range, Kriterie2 As Range) Dim Find1 As Variant, Find2 As Variant Dim Data As Variant, i As Integer, J As Integer, M As Long, RW As Long RW = Range("K30000").End(xlUp).Row Find1 = Kriterie1 Find2 = Kriterie2 Data = Range("K2:P" & RW)
For M = 1 To UBound(Data, 1) For i = 1 To UBound(Find1, 2) If Not IsEmpty(Find1(1, i)) Then If Find1(1, i) = Data(M, 1) Then Data(M, 1) = "+"
Exit For End If End If Next
For J = 1 To UBound(Find2, 2) If Not IsEmpty(Find2(1, J)) Then If UCase(Find2(1, J)) = UCase(Data(M, 5)) Then Data(M, 5) = "+" Exit For End If End If Next If Data(M, 1) = "+" And Data(M, 5) = "+" Then FindSum = FindSum + Data(M, 6) Next End Function
Public Function FindSum(Kriterie1 As Range, Kriterie2 As Range) Dim Find1 As Variant, Find2 As Variant Dim Data As Variant, i As Integer, J As Integer, M As Long, RW As Long RW = Range("K30000").End(xlUp).Row Find1 = Kriterie1 Find2 = Kriterie2 Data = Range("K2:Q" & RW)
For M = 1 To UBound(Data, 1) For i = 1 To UBound(Find1, 2) If Not IsEmpty(Find1(1, i)) Then If Find1(1, i) = Data(M, 1) Then Data(M, 1) = "+"
Exit For End If End If Next
For J = 1 To UBound(Find2, 2) If Not IsEmpty(Find2(1, J)) Then If UCase(Find2(1, J)) = UCase(Data(M, 5)) Then Data(M, 5) = "+" Exit For End If End If Next If Data(M, 1) = "+" And Data(M, 5) = "+" Then FindSum = FindSum + Data(M, 7) Next End Function
Det er godt at høre fra dig igen. Jeg har lige snakket med min chef og vi har aftalt at jeg snart skal på kursus i ovenstående.
Jeg har en ekstra udfordring til dig! Jeg kan evt. oprette et nyt spørgsmål så du kan få nogle point hvis du kan løse det.
Jeg skal kunne bruge begge funktioner med sum-område i kollone P OG Q. Det har jeg vist selv løst ved at kalde den "gamle" for Findsum1.
Problemet er at det nye beløb i kollone Q der skal summeres er beløb excl. afgift. Beløbet skal tillægges afgift som afhænger af hvilket varenr. der er tale om. Jeg har en liste med varenumre og tilhørende afgift som varierer fra 0 - 2750 kr. pr. 1000 l.
Kan det lade sig gøre at indbygge det i den nye Findsum formel?
Alternativet er at lave en ekstra kollone R hvor man på hver linie laver følgende formel:
Hvor varenumre er U2:U21 og tilhørende afgift er V2:V21 - afgiften divideres med 1000 og ganges med literbeløbet og tillæges salgsbeløbet excl. afgift.
Er det besværligt at lave så kan ovenstående metode anvendes.
Jeg har fået ovenstående vedr. afgift til at fungere så her kan jeg godt klare mig med det.
Jeg kunne dog godt tænke mig at vide hvor det er defineret at:
Kollone K er kriterieområde til kriterie 1 Kollone O er kriterieområde til kriterie 2
Jeg kan se hvor du har rettet vedr. sum - dette har jeg selv rettet til R som er den nye kollone inkl. afgift.
Jeg kan f.eks. ikke se at der står "O" nogle steder. De data der er i K4:K6932 + O4:O6932 er tal for en bestemt periode.
Jeg skal også have nye tal med andre perioder hvor den skal summere udfra de samme kriterier, men istedet for kollone K kan det f.eks. være T og O kan f.eks. være X og sumområde kan være Y og AA (liter og beløb inkl. afgift).
Public Function FindSum(Kriterie1 As Range, Kriterie2 As Range) Dim Find1 As Variant, Find2 As Variant Dim Data As Variant, i As Integer, J As Integer, M As Long, RW As Long RW = Range("K30000").End(xlUp).Row Find1 = Kriterie1 Find2 = Kriterie2 Data = Range("K2:Q" & RW)
For M = 1 To UBound(Data, 1) For i = 1 To UBound(Find1, 2) If Not IsEmpty(Find1(1, i)) Then If Find1(1, i) = Data(M, 1) Then' 1 tallet i Data(M, 1) er Kolonne K Data(M, 1) = "+"
Exit For End If End If Next
For J = 1 To UBound(Find2, 2) If Not IsEmpty(Find2(1, J)) Then If UCase(Find2(1, J)) = UCase(Data(M, 5)) Then' 5 tallet i Data(M, 5) er Kolonne O Data(M, 5) = "+" Exit For End If End If Next If Data(M, 1) = "+" And Data(M, 5) = "+" Then FindSum = FindSum + Data(M, 7) Next End Function
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.
