Hej Joker!

Igår var jag ute på cruising därav sent svar.

Att du inte kan skapa just de tre formlerna beror nog på att du inte har skapat tabellerna tS, tV samt tSV. Om du tittar längre ner i listan i namnhanteraren så ser du att tabellerna har tabellikoner.

Tabellområden kan man inte skapa inne i Namnhanteraren utan det måste göras genom att antingen markera tabellområdet och trycka Ctrl+T eller uppe på menyraden välja ”Formatera som tabell”, vilket jag berättade om tidigare i denna tråd. När tabellerna väl är skapade kan man använda dem i namnhanteraren med tabellnamn och hakparenteser (tS[#Rubriker ], tS[#Denna rad], tV[0], etc.).

Vi börjar därför del 3 av utmaningen med att definiera de tre tabellområdena.

1 - Skapa tabeller

Systemtabellen tS

Skriv in siffrorna 1-12 i cell H23:S23. Markera sen cellområdet H23:S24 och tryck Ctrl+T. Ange att tabellen har rubrikrad samt följande cellområde.

Döp sedan om tabellen till tS antingen i Tabellverktygsmenyn eller i namnhanteraren.

Vikttabellen tV

Skriv in rubrikerna ”Vikt” samt ”0” i cell B23:C23. Markera sen cellområdet B23:C24 och tryck Ctrl+T. Ange att tabellen har rubrikrad samt följande cellområde.

Döp sedan om tabellen till tV antingen i Tabellverktygsmenyn eller i namnhanteraren.

Sorterad Vikttabell tSV

Skriv in rubriken ”Sorterad Vikt” i cell F4. Markera sen cellområdet F4:F5 och tryck Ctrl+T. Ange att tabellen har rubrikrad samt följande cellområde.

Döp sedan om tabellen till tSV antingen i Tabellverktygsmenyn eller i namnhanteraren.

När de tre tabellerna är skapade kan du göra klart resten i namnhanteraren.

2 – Formler i området ”Resultat”

Ange följande formler i nedan celler. Observera att när formlerna omges av klammerparenteser { och } är det en matrisformel som måste avslutas med Ctrl+Shift+Enter i samtliga versioner av Excel förutom Excel 2021 samt 365.

Markera cell B5:D5 och skriv in följande formel:

=INDEX(rStreck;KOLUMN();vM)

Avsluta med Ctrl+Shift+Enter för att bekräfta matrisformeln. Då kommer formelfältet se ut så här:

Cell B6 (matrisformel)

{=OMFEL(fVv*vProc1%;0)}

Cell C6 (matrisformel)

{=OMFEL(fVv*vProc2%;0)}

Cell D6 (matrisformel)

{=OMFEL(fVv*vProc3%;0)}

Cell B8 (inte matrisformel)

=OMFEL(fRG+RAD(tS[#Rubriker]);0)

Cell C8 (inte matrisformel)

=OMFEL(fRG;0)

Cell D8 (inte matrisformel)

=SUMMA(FÖRSKJUTNING(rStreck;1;))

Nu börjar inlägget bli långt igen så vi får avsluta del 3 och fortsätta med del 4 när du kommit så här långt Jokern.

Trevlig dag i värmen!

Hej igen,

Vad bra att det börjar fungera! Vill du ha en knapp så ska du såklart få en knapp. Den tar vi dock i sista delen, när allt annat är förberett.

Nu kör vi vidare.

3 – Formler i området ”Streck”

I det här området finns inga formler alls – förutom för vilkorsstyrd formatering, men den delen tar vi på slutet för den är inte nödvändig för arkets funktion.

Det är i det här området du anger alla dina procentsatser för matcherna.

4 – Formler i området ”Val”

I det här området finns inte heller några formler – förutom för dataverifiering – vilket används för att man inte ska kunna mata in ogiltiga värden, t.ex. ogiltiga procentsatser, etc.

På Datafliken hittar du funktionen Dataverifiering. Markera cell B12 och ange följande.

I cell C12 följande.

Samt i cell D12.

5 – Formler i området ”Urval”

I det här området finns - förutom vilkorsstyrd formatering som vi återkommer till – endast två matrisformler.

Markera cellområde H11:T14 och ange följande matrisformel.

=INDEX(rStreck;;)

Markera cellområde U11:AF14 och skriv in exakt samma matrisformel.

=INDEX(rStreck;;)

(Glöm inte att matrisformler måste anges med Ctrl+Shift+Enter)

6 – Formler i området ”Värden”

I det här området finns följande formler.

I cellområde B17:C17 använd följande formel för rubriktexten.

="Total vikt ("&vProc1&"%)"

I cell B18 använde följande formel.

=SUMMA(tV[Vikt])

I cell C18 använde följande formel.

=OMFEL(STÖRSTA(tV[0];1);0)

Och i cell D18 använde följande formel.

=3^vA

7 – Formler i området ”Mall”

I mallområdet byggs mallen som ligger till grund för systemet med två matrisformler. Markera först cellområde H17:S17 och använd följande matrisformel för rubriktexten.

=INDEX(rStreck;;)

Markera sedan cellområde H18:S20 och använd följande matrisformel för mallen. (Glöm inte Ctrl+Shift+Enter)

=fM

Nu har vi byggt det mesta av mallen. Återkom när du är klar med ovan Joker så fortsätter vi med nästa del som kommer innehålla den sista delen av det vi kommer göra i Excel. ”Knappen” innebär lite kodande i VBA så den får ett eget avsnitt.

Trevlig kväll!

God morgon Jokern,

Nu har vi kommit till den sista delen av arbetet i Excel för att kunna göra klart mallen.

8 – Formler i området ”Rätt rad”

I detta område finns fyra formler.

Skriv in följande formel i cell F2

="Total vikt ("&vProc1&"%)"

Skriv in följande formel i cell F3

=SUMMA(tSV[#Data])

I cell H2 skriv in följande formel.

="("&vProc1&")"

Markera sedan cellområde I2:T2 och ange följande matrisformel. (Ctrl+Shift+Enter)

=OM(fI-vA>0;"";FÖRSKJUTNING(tS;PASSA(SANT;tV[Vikt]=fVv*vProc1%;)-1;cK-vA+fI-1;;1))

Vilkorsstyrd formatering

Den vilkorsstyrda formateringen är inte nödvändig för arkets funktion, men visar på ett enkelt och överskådligt sätt vilka val man gjort beträffande antal matcher, osv.

Det verkar finns en bugg (åtminstone i Excel 2007) som gör att man inte kan använda sig av tabellreferenser för vilkorsstyrd formatering. Man får inget felmeddelande, men regeln fungerar helt enkelt inte. Vidare om man dessutom sparar dokumentet i xlsb-formatet så blir filen korrupt och man kan inte öppna den igen utan att Excel krashar.

Börja med att markera cellområdet H6:T8 och välj vilkorsstyrd formatering från snabbmenyn. Välj där ”Ny regel” och ange följande regelformel.

=INDEX(rStreck;1;KOLUMN()-7)=vM

Väl sen ”Format” och ange text- och bakgrundsalternativ enligt önskemål.

När regeln är klar ska den se ut så här ungefär.

Nästa två regler är för området ”Urval”. Markera cellområde H12:AF14.

Gör samma sak igen. Välj vilkorsstyrd formatering och ”Ny regel”.

Använd följande formelregel.

=OCH(KOLUMN()-KOLUMN($H$1)+1>vM;KOLUMN()-KOLUMN($H$1)+1<vM+vA)

Och för sista regeln markera cellområde H12:T14.

Gör samma sak igen. Välj vilkorsstyrd formatering och ”Ny regel”.

Använd följande formelregel.

=INDEX(rStreck;1;KOLUMN()-7)=vM

Väl sen ”Format” och ange text- och bakgrundsalternativ enligt önskemål.

När reglerna är klara ska de se ut så här ungefär.

Observera att ordningen på reglerna är viktig. Den sista regeln vi skapade måste vara längst ner i listan. Är inte reglerna i rätt ordning kan man använda upp/ner-pilarna ovanför reglerna för att ändra ordningen.

Nu är hela systemet klart att använda. Vill du ”provköra” det utan VBA-kod och knapp följ följande steg.

1 – Systemtabellen

Ange följande formel i cellerna H24:S24. Gör en kolumn i taget eller använd kopiera följt av "Klistra in formler". 

Excel kommer automatiskt fylla hela tabellen (6,5 milj. celler) med formelerna så det kan ta lite tid.

2 – Vikttabellen kolumn ”Vikt”

Fortsätt sedan med vikttabellen. I cell B24 skriv in följande formel.

När första kolumnen i viktabellen nu är fylld med formler samt viktvärdena är beräknade markera då hela första kolumnen i vikttabellen. Det är när den lilla svarta pilen pekar nedåt som du ska klicka.

Kopiera alla värden med Ctrl+C. Markera sedan första cellen i tabell Sorterade Vikter och välj Klistra in Värden.

Nu kommer det se ut så här.

Nu är det dags för sortering. Välj sortera ”Största till minsta” på menyalternativet ”Sortera”.

3 – Vikttabellen kolumn ”0”

Nu är vi framme vid det sista steget. Markera cell C24 i kolumn ”0” i vikttabellen och ange formeln.

=fS

Nu är hela systemet klart och du kan analysera dina värden och data. Så här kommer det hel se ut med mina val 8 matcher, match 1 samt 50% som gränsvärdet.

Nu kan man välja att spara dokumentet med ett annat namn om man inte vill ”förstöra” mallen. Mallen är liten och går snabbt att öppna så den är bra att ha kvar i original.

I sista delen i denna ”miniserie” kommer vi skapa en ”knapp” åt dig som med hjälp av VBA utför de manuella stegen vi gjorde på slutet i det här avsnittet under ”testkörningen”.

Önskar dig också en fin dag Jokern!

God kväll!

Nej, jag jobbar inte specifikt med Excel, men håller till inom IT-branschen och använder Excel mer eller mindre dagligen.

Som alltid smög det sig in några fel i mitt inlägg. Jag har nu uppdaterat det och markerat ändringarna med understruken fetstil i mitt tidigare inlägg.

Man kan inte bryta, eller ändra endast en del av en matris. Har man markerat fel celler när man trycker Ctrl+Shift+Enter får man helt enkelt radera alla cellerna och skriva in formlerna och trycka Ctrl+Shift+Enter på nytt med rätt celler markerade.

Området H6:T8 är där du skriver in procentsatserna. Där finns inga formler så där kan det inte bli något "fel".

Så länge alla tre tabeller är tomma - vilket de är tills du gör "testkörningen" på slutet - kommer en del celler bara visa 0 eller #SAKNAS. Att jag inte lagt in felkorigering i vissa av dessa celler beror på att det väsentligt försämrar beräkningsprestandan samt käkade upp grymt mycket minne. När jag la in felhantering i cellområde I2:T2 så gick det åt över 300MB internminne, vilket jag tyckte var onödigt slöseri med resurserna.

Behöver du en "nödstopp-knapp" finns det en sådan i Aktivitetshanteraren. Den får du fram genom att trycka Ctrl+Alt+Del. Där kan man manuellt avsluta processer och applikationer när de slutat svara. Den har jag fått använda "hundratals" gånger under detta projekt. 

När min mall är tom ser den ut så här.

Efter jag lagt in mina procentsatser i cellområde H6:T8 ser den ut så här.

Filen kommer inte bli 70MB om du inte fyllt tabellerna och sparat som xlsx. När vi kommer till nästa steg med VBA kan man inte spara dokumentet som xlsx längre för det formatet stödjer inte VBA. Så det är ytterligare en anledning att redan nu övergå till xlsb-formatet om du inte redan gjort det.

Tabellerna bör man lämna helt tomma så man har en liten och smidig mall. Du kommer få en "knapplösning" av mig så fort mallen fungerar som den ska. Min mall med procentsatserna inlagda är endast 1'748 kB.

Man är begränsad till 1'048'576 rader per arbetsblad i själva Excel. Men det finns inget som förhindrar att man använder flera arbetsblad, eller källfiler, och exempelvis använder Power Query-verktyget i nyare versioner av Excel för att importera/länka, transformera och presentera datan.

Jag är online ett par timmar nu om du har fler frågor under kvällen.

God morgon Jokern!

Vi tar en sak i taget och släpper det där med hästarna i denna tråd.

För att fylla i tabellerna med systemet och procentsatserna så används formlerna =fT, =fV samt =fS vilket finns beskrivet i mitt tidigare inlägg där vi gör den manuella testkörningen.

Om din mall nu ser ut som min gör när den är tom kan du väl göra en testkörning med samma procentsatser och parameterar (8 matcher, vald match 1) som jag visade resultatet av i mitt tidigare inlägg.

Om du får exakt samma resultat som jag fick i min skärmdump så vet vi att våra mallar är identiska och kan då gå vidare till VBA med "knappen".