09. november 2012 - 11:42Der er
12 kommentarer og 1 løsning
Perfomance forbedring af formel
Hejsa,
Jeg sidder med Excel 2010, på engelsk. Jeg vil gerne have en formael der kan hjælpe mig med følgende lille udfordring.
Jeg har understående "lille" udtræk, hvor jeg gerne vil tælle hvor mange unikke værdier der er i kolonne A, hvis kolonne B=01, kolonne C=DK, kolonne D=22 og kolonne E=yes. Tællene i kolonne a går igen, men jeg vil kun tælle dem med en gang, og kun hvis alle 4 betingelser er opfyldt.
Jeg har i en tidligere tråd fået følgende svar fra PHChristensen (og tak for en god løsning), som virker, men da jeg har 127455 rekords lige nu, og med et potentiale på omkring 250.000 rekords, er dette for meget for excel der går ned hver gang jeg bruger formlen.
Kan man forbedre performance på denne, så den kan køres på så mange rekords? Jeg kan kun bruge formler, jeg kan ikke bruge VBA:(
Delivery no.=A Delivery status=B Country =C Orderline status=D Is planned date before today?=E
A B C D E 906951 01 NO 22 No 860401 02 SE 22 No 802057 02 SE 22 No 885656 02 SE 22 No 921432 01 DK 22 No 913123 01 FI 22 No 914955 01 NO 22 No 885415 02 SE 22 No 900358 01 NO 22 No 914739 01 SE 22 No 842658 05 NO 33 Yes 842658 05 NO 22 Yes 891892 02 NO 22 No 842658 05 NO 33 Yes 842658 05 NO 33 Yes 853979 02 SE 22 No 888688 02 SE 22 No 932638 01 NO 22 No 931741 01 SE 22 No 910786 02 SE 22 No 916469 01 NO 22 No 896629 01 NO 22 No 879490 02 DK 22 No 902582 02 SE 22 No 858404 05 DK 33 Yes 858404 05 DK 33 Yes 916235 01 NO 22 No 858404 05 DK 33 Yes 858404 05 DK 33 Yes 858404 05 DK 33 Yes 913123 01 FI 22 No 858404 05 DK 33 Yes 902362 02 SE 22 No 927493 02 NO 33 Yes 858404 05 DK 33 Yes 914955 01 NO 22 No 853979 02 SE 22 No 853979 02 SE 22 No 892275 02 SE 22 No 904063 01 DK 22 No 934213 01 NO 22 No 909565 01 NO 22 No 853979 02 SE 22 No 853979 02 SE 22 No
Ja det har du ret i, mit problem er at jeg skal bruge omkring 4 forskellige diffinationer. Med andre ord, jeg samler op på 40 forskellige betingelser. Hvis jeg skal bruge pivo til det, vil det give mig 40 forskellige pivo tabeller.
Nej, hvis den samme værdi kan fremgå flere gange i kolonne A, duer min formel ikke, så får du for mange hits. Men hvis du starter med at fjerne dubletter, vil det virke. Er det en mulighed?
Synes godt om
Slettet bruger
10. november 2012 - 07:26#8
Hvis du bruger den formel du selv viste i #5 i tråden "SUM.HVISER eller lignende":
så er det første du kan prøve da at begrænse celleområdet til det strengt nødvendige i stedet for at bruge "full-column-references". Sidstnævnte er måske bekvemt, men det er absolut en uskik i forbindelse med matriksfunktioner.
Det er ikke sikkert at det løser dit problem her, men rådet om at begrænse celleområdet til det nødvendige en generelt anvendeligt.
Hans
Synes godt om
Slettet bruger
10. november 2012 - 07:34#9
Glem venligst #8. Jeg kom til at blande noget forkert sammen.
Du er nu ikke helt ved siden af. Det er en slem uskik og have så meget data i excel og så ønske og ligge sum, og hvis formler ned over alt den data. Problematikken er at mit firma mangler og indse de er vokset fra excel som en standart løsning.
Jeg tror ikke på excel kan løse dette problem. Excel løser dette enten ved en vba programering, eller ved hjælp af filter, og ikke en løsning hvor der bruges så mange summer over så meget data.
Jeg lader lige spørgsmålet stå lidt i nu i håb om at jeg tager fejl, og der er en smart funktion til dette i excel.
Men kun muligt såfremt der er via en funktion der giver mig mulighed for excel selv gør dette hver gang data arket opdateres.
Synes godt om
Slettet bruger
12. november 2012 - 11:59#12
Problemet er ikke at finde en funktion, men mængden af data.
Jeg er klar over du ikke er glad for VBA, men overvej alligevel følgende (hvis opgaven skal løses ved hjælp af Excel)
Med dine data i A2:E150000 (eller hvor meget det nu var) indtast følgende formel i F2: =(B2=1)*(C2="DK")*(D2=22)*(E2="Yes"). Med celle F2 aktiveret dobbeltklik på fyldhåndtaget for at kopiere hele vejen ned. Denne type Boolesk test er ekstremt hurtig (og rimelig hurtig selv ved 150.000 rækker). Lav nu en filtrering på kolonne F (=1).
Indsæt følgende kode i et almindeligt modul:
Sub CopyFilteredData() Dim rng As Range
Set rng = ActiveSheet.AutoFilter.Range rng.Copy
Worksheets("Sheet2").Range("A1").PasteSpecial (xlPasteValues) Application.CutCopyMode = False End Sub
Kør koden fra Sheet1.
I Sheet2 indsætter du denne formel (i for eksempel H1):
Udvid celleområdet hvis der kan forekomme mere end 99 unikke værdier.
Jeg er selvfølgelig klar over, at løsningen ikke helt er det du ønsker. Om du kan bruge mmetoden må du selv afgøre.
Men hele opgaven her er i øvrigt anskuelsesundervisning i, at Excels 2^20 (= 1.048.576) rækker (når det gælder formler) mest af alt er mmarketing hype fra Microsofts side.
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.
