Tælle antal kreditnotaer ud af journalliste

Nu skal jeg nok blande mig udenom ting der ikke rager mig ;o) - men det lader til, at en database er det du har brug for, ikke et regneark :o)

Men kan du ikke bare smide en  HVIS omkring din formel, og så ikke gøre noget hvis en eller anden betingelse ikke er opfyldt, og gøre noget andet hvis den er?

Alternativt, lave en macro der looper over alle dine poster, og kun tæller hvis en eller anden betingelse er opfyldt, fx. at et eller andet nummer er større end 9000000

Hvis dataen lå og flød hos mig, ville jeg nok også gøre det den vej.

Men jeg har adgang til dette Oracle extract via internettet, og vil derfor lige kopiere fire formler ind i regnearket, for at gi' mig de værdier jeg skal bruge i mine videre analyser.

Excel kan fint bruges til en opgave som denne.
Om man ønsker at bruge Access er oftest en smagssag.
I bund og grund er Excel også en database.

Du skal have gang i en array- eller =SUMPRODUKT-formel.
Hvis du skal have mere hjælp skal vi have lidt flere oplysninger eller du kan sende dit ark til mig.

janvogt@esenet.dk

Ja, men det er lidt basværligt at lave SQL-udtræk fra Excel ;o)

Men for at vende tilbage til spørgsmålet - er det ikke bare at smide en HVIS ind?

HVIS-formlen er bedst når der kun er ét kriterie. Her er der mindst 3, så derfor er det nødvendigt at bruge en lidt kraftigere formel.

Martin-> det eneste besværlige ved det, er at man kun kan lave sql-udtræk fra lukkede excel-filer (via msquery) :-)

Flere Hvis'er kan jo sagtens kombineres.

"nu vil jeg gerne udvide formlen til kun at tælle alle fakturaer som har et nummer under 900000 og alle kreditnotaer som har et nummer over 900000."

Er det ikke bare at putte 2 hvis'ser ind i hinanden? Eller en Hvis kombineret med en OG...

Nå, blander mig udenom. Over & Out, Martin :o)

Du kan ikke kombinere HVIS og OG - prøv selv ......

Hvis'er er heller ikke specielt stærke, når det gælder et range.
Med to eller flere hvis´ser vil du kunne finde ud af, om den pågældende linie opfylder kriterierne, men ikke tælle alle linier.
Så skal det ihvertfald kombineres med en array-formel.

oki :o)

=|:-)

Kolonne A indeholder en række af faktura-/kreditnota-numre i spredt blanding og mange af dem er redundante, idet der kan være flere linier med vareoplysninger pr. faktura/kreditnota.

=SUM(IF(FREQUENNCY(A:A;A:A)>0;1)) giver mig det samlede antal faktura/kreditnotaer, nu ville det være genialt, hvis jeg kunne afgrænse det i to formler, en der tæller antallet under 900000 og en der tæller antaller over 900000.

Naturligvis kan jeg lave en sortering, og derefter markere fra/til hvilke celler der skal behandles og få resultatet af den vej, men mon ikke der findes en løsning unden at skulle sortere.

Hvordan skelner du mellem faktura og kreditnota?

Faktura-nummeret er mindre end 900000, kreditnota-nummeret er større end 900000.

Jeg nævner lige igen, at en macro kan løse det på meget elegant vis - men nu har jeg jo allerede skrevet Over&Out een gang ;o)

Jeg kæmper stadig med en enkelt array-formel, men vender nok ikke tilbage før i morgen. Det er alligevel en "lidt" kompliceret opgave :-)

Derfor kan det jo godt være, at jeg må overgive mig og erkende at en makro er den eneste løsning, men indtil videre har jeg ikke givet op ..... :-)

Hvorfor have den modvilje mod en macroløsning, som jeg fornemmer. Macroer er vores venner :o)

Disse to kan nok bruges, under forudsætning af at der ikke er tomme linier.
=SUMPRODUKT(1/TÆL.HVIS(A1:A100;A1:A100)*(A1:A100<900000))
=SUMPRODUKT(1/TÆL.HVIS(A1:A100;A1:A100)*(A1:A100>=900000))

Over and Out :-)

Macroer er vore venner ........ ;-)
Denne funktion tæller tager et range, et minimum og et maximum som argumenter og returnerer antallet af unikke værdi der imellem.
Kan også bruges med strenge.
=CountUniqBetween(A2:A10000;1;899999)
=CountUniqBetween(A2:A10000;999999;10000000)



Function CountUniqBetween(RNG As Range, mini As Variant, maxi As Variant) As Long

Dim xcol As New Collection
Dim varray As Variant
Dim i As Long
Dim j As Long
varray = RNG
On Error Resume Next
For i = 1 To UBound(varray, 2)
    For j = 1 To UBound(varray, 1)
    If varray(j, i) >= mini And varray(j, i) <= maxi Then _
            xcol.Add varray(j, i), CStr(varray(j, i))
    Next
Next
On Error GoTo 0
CountUniqBetween = xcol.Count
Set xcol = Nothing
Set varray = Nothing

End Function

Funktionen har et lille problem ang blank celle. dette løses ved at erstatte det midterste indenfor løkken med

If vArray(j, i) >= mini And vArray(j, i) <= maxi And Len(vArray(j, i)) > 0 Then _
            xcol.Add vArray(j, i), CStr(vArray(j, i))

Selvfølgelig er makroer vore venner :-)

Det er bare lidt mere sport i at lave en formel på én linie end en makro på 20 linier .... :-)

Helt enig, Jan .................
Og en formel virker i alle ark, uden at man skal have indsat et vba-modul.
problemet er bare at der kan gå frygtelig lang tid med at lave formlen. :-)
her er det ofte hurtigere med en makro.

Normalt er det hurtigt at lave en formel, men jeg må indrømme at denne er lidt langhåret. Problemet er at få den til at tage højde for blanke linier.
Måske kan du hjælpe ...

Jeg er foreløbig nået frem til denne arrayformel:
=SUM(IF(FREQUENCY(IF(LEN(A1:A1000)>0;MATCH(A1:A1000;A1:A1000;0);"");IF(LEN(A1:A1000)>0;MATCH(A1:A1000;A1:A1000;0);""))>0;1))

Denne tæller unikke værdier i området A1 til A1000, og tager også højde for blanke.

Problemet nu består i at få koblet (A1:A100<900000) på. Kan du gennemskue den?

Det var vist noget vrøvl noget at det jeg skrev.
Problemet består ikke i at tage højde for blanke linier. Den har jeg jo løst ....

jeg har en lidt anden indgangvinkel:
=SUM(IF(COUNTIF(A1:A1000;A1:A1000)=0;"";1/COUNTIF(A1:A1000;A1:A1000)*(A1:A1000<900000)))
og
=SUM(IF(COUNTIF(A1:A1000;A1:A1000)=0;"";1/COUNTIF(A1:A1000;A1:A1000)*(A1:A1000>=900000)))

begge er array-formler

Ja, godt set :-)

Ikke hjemmelavet, men kopieret. Desværre :-(

Godt fundet så :-)

Jeg troede faktisk aldrig at jeg skulle opleve at en hjemmelavet vba-funktion var hurtigere end en formel, men det er faktisk tilfældet her.
Jeg testede på 10000 linier og array-funktion satte næsten min computer istå, hvorimod min egen funktion går meget hurtig.
Det må nok skyldes at array-funktionen bygger min. 5 arrays á 10000 elementer og skal kombinere disse.
Min vba-funktion bygger kun et array og overfører det til en collection (en collection kan kun tage mod unikke elementer i sit index)

Det lykkedes mig at løse mit problem med nedenstående formel fra bak, så drop mig lige et svar.

=SUM(IF(COUNTIF(A1:A1000;A1:A1000)=0;"";1/COUNTIF(A1:A1000;A1:A1000)*(A1:A1000<900000)))
og
=SUM(IF(COUNTIF(A1:A1000;A1:A1000)=0;"";1/COUNTIF(A1:A1000;A1:A1000)*(A1:A1000>=900000)))

til uerfarne brugere, array-formler skal indtastes med "Ctrl+Shift+Enter"

Vedrørende macro diskussionen, ja så må jeg hellere rive en halv til hel dag ud af min kalender, og komme igang med den side af løsningsmulighederne.

Tak for hjælpen. :-))

Selv tak, tobler :-)
Tag du hellere en hel uge ud. Du vil blive forbavset over hvad excel også kan ..... ;-)