Excel er et av mine favorittprogram! Det har svært mange muligheter, ved å kombinere funksjoner og makroer er det mulig å få til utrolige ting. Og noen ganger er løsningen enklere enn man skulle tro, og jeg kan ikke annet enn å slå meg for pannen og lure på hvorfor jeg kløna i flere timer med en komplisert løsning. 😉

For at andre skal slippe å kaste bort timer som meg deler jeg her min oppskrift for hvordan du kan lage en oversiktlig årskalender med fargekoder. Den kan være til hjelp for familier med mange aktiviteter, for organisasjoner som trenger et årshjul, for leirsteder med mange ulike arrangement osv.

Den kan skrives ut og henges på f.eks. kjøleskapet, og/eller ligge på et nettsted som en dynamisk kalender.

Og du: Om du synes dette blir for komplisert kan jeg gjøre det for deg! Kontakt meg på kom@altern.no eller 94 13 18 16 om du har et oppdrag.

Mål og bakgrunn

Jeg har tatt over ansvaret for Lia Gårds nettsted mens den ansvarlige har barselperm. Da jeg startet trengte jeg en oversikt over retreatsenterets mange arrangement, hele årsprogrammet. Jeg ønsket at jeg sjøl, Lias husfolk (ansatte og frivillige medhjelpere) og nettstedets besøkende kjapt skulle se når på året det er familieretreater, stille retreater, ulike typer kurs osv.

Jeg jobber med design og grafikk og foretrekker å se informasjon visuelt framstilt framfor lister og tabeller. Jeg tror de fleste mennesker har det som meg: Det er mye lettere å raskt forstå en graf eller en tegning enn en tekst som forteller det samme. (Og forskning viser at mange husker det visuelle bedre enn tekst og dessuten oppfatter det visuelle kjappere.)

Dog er enhver god illustrasjon avhengig av at illustratøren har forstått og har oversikt over det som skal illustreres, så jeg starter med å systematisere dataene i en kjedelig tabell.

Lage liste

En kalenderhendelse består av starttid, sluttid, tittel og gjerne sted, beskrivelse, type hendelse og personer som er med. Så start med en tabell med følgende kolonneoverskrifter:

• Start (siden dette er en årskalender er ikke tidspunkt relevant, kun dato)
• Slutt
• Tittel
• Type hendelse, typen bestemmer fargen på arrangementet

Skriv inn overskriftene, marker området og formater som tabell.

Hver hendelse ligger i hver sin rad. Om du vil kan du lage en kolonne for antall dager hendelsen varer. Skriv følgende i cellene i denne kolonnen:

[code]=[@slutt]-[@start][/code]

På Lia kan to arrangement foregå parallelt eller ett arrangement kan bruke hele senteret. Jeg ville skille mellom de to hovedbyggene på gården og laget derfor én kolonne for hvert bygg, der jeg krysser av for hvor arrangementet foregår. Anlegget er i ferd med å utvides med en tredje del. Når denne er klar kan den legges i en ny, tredje kolonne.

Lager du en kalender for en familie kan du ha en kolonne per person, og krysse av for hvilke personer som deltar på hendelsen.

Du trenger ikke lage en lang liste, bare start med noen hendelser i f.eks. januar for å teste.

Noen tips og triks

For uerfarne Excel-brukere kan det være verdt å merke seg:

• Hvis du bruker NumPad på tastaturet, lengst til høyre om du har et langt tastatur, går det raskt å plotte inn datoer. På kompakte tastatur kan du holde ned fn-tasten og NumLock for å aktivere NumPaden.
• Skriver du 4/1 i en celle gjetter Excel at det er en dato i år og legger til inneværende år automatisk slik at det blir stående 4. januar 2015.
• Cellene kan vise datoene på ulike måter. Du kan velge et datoformat med ukedag slik at du lettere ser om du har skrevet rett dato.
• Excel kjenner igjen ord, skriver du to bokstaver du har skrevet i en tidligere rad gjetter Excel at du skal skrive det samme. Klikk Tab, Enter eller pil på tastaturet for å fullføre.
• I kolonnen Type kan du skrive fritt hva slags type hendelse det er eller sette opp en forhåndsdefinert liste du kan velge fra.

Gi arket og tabellen navn

For å holde oversikten er det lurt å gi både arkene og tabellen gode navn.

1. Stå i tabellen, klikk Formater som tabell om du ikke alt har gjort det.
2. Gå på fanen Formler og klikk Navn.
3. Gi tabellen et kort og forståelig navn, jeg kalte min arr, forkortelse for arrangement.

Gi også arkene navn. Hvis din Excel-bok bare har ett ark oppretter du nye ved å klikke på det lille arksymbolet med gul stjerne eller taste shift+F11. Når du har tre ark gir du hvert ark et navn. Jeg kalte mine arr, kalender og ferdig kalender.

1. Hold musa over arkfanen nederst. Høyreklikk.
2. Velg Gi nytt navn.

Lage kalenderen

Kalenderen lager du i arket kalender.

Overskrifter

Øverste raden skal være overskrifter. Start i celle A1 og skriv uke. Skriv ukedagene i cellene bortover, enten kortformen man eller fullt ut mandag. Etter søndag skriver du de samme ukedagene en gang til, men nå med en t foran.

Datoer

Gå til celle A2, skriv inn ukenummeret på den uka du vil kalenderen skal starte. Gå til neste kolonne og skriv datoen på den dagen du vil kalenderen skal starte.

I C2 skriver du:

[code]=B2+1[/code]

Kopier C2. Marker D2 t.o.m. H2 og lim inn.

I I2 skriver du:

[code]=B2[/code]

Marker J2 t.o.m. O2 og lim inn.

I A-kolonnen skal du i tillegg til ukenummer skrive navn på stedet eller personen hendelsene gjelder for. Jeg henviser til de to delene av anlegget på Lia Gård og skriver derfor Nylia og låven, i rad 3 og 4. Lager du en kalender for f.eks. en familie på fem fyller du inn navn i rad 3, 4, 5, 6 og 7.

Etter navnelisten begynner du på en ny uke, og skriver følgende i henholdsvis A5 (ukenummer) og B5 (dag) (Evt. rad 6, 7, eller 8 osv. i henhold til hvor lang navneliste du har):

[code]=A2+1
=B2+7[/code]

Kopier den siste cella (B5) bortover hele raden.

Hendelser

Hente navn på arrangement

Nå er det på tide å hente fram hendelsene fra tabellen. I celle B3 skriver jeg følgende formel og taster CTRL+SHIFT+enter fordi det er en matriseformel:

[code]
=HVIS(ANTALL.HVIS.SETT(arr[start];"<="&kalender!B2;arr[slutt];">="&kalender!B2)>0;
HVIS(INDEKS(arr[Nylia];MIN(HVIS((B2>=arr[start])*(B2<=arr[slutt]);SAMMENLIGNE(RAD(arr[arr]);RAD(arr[arr])))))="x";
INDEKS(arr[arr];MIN(HVIS((B2>=arr[start])*(B2<=arr[slutt]);SAMMENLIGNE(RAD(arr[arr]);RAD(arr[arr])))));
HVIS(ANTALL.HVIS.SETT(arr[start];"<="&kalender!B2;arr[slutt];">="&kalender!B2)>1;
HVIS(INDEKS(arr[Nylia];N.MINST(HVIS((B2>=arr[start])*(B2<=arr[slutt]);SAMMENLIGNE(RAD(arr[arr]);RAD(arr[arr])));RAD($A$2)))="x";
INDEKS(arr[arr];N.MINST(HVIS((B2>=arr[start])*(B2<=arr[slutt]);SAMMENLIGNE(RAD(arr[arr]);RAD(arr[arr])));RAD($A$2))));
""));"")[/code]

Dette er hva formelen gjør, linje for linje:

1. Sjekk om antall datotreff (altså at datoen i B2 enten samsvarer med start- eller sluttdato, eller befinner seg mellom disse) er mer enn 0, hvis sann: gå videre
2. Sjekk om det første treffet har kryss i Nylia (altså at datoen i B2 har en hendelse som foregår i Nylia).
3. Hvis sann hent arrangementets navn, hvis usann: gå videre
4. Sjekk om det er flere enn ett treff på datoen.
5. Sjekk om det andre treffet har kryss i Nylia.
6. Hvis sann hent arrangementets navn,
7. hvis usann: sett inn ingen tekst

Du kan kopiere formelen min og lime den inn, men må selvfølgelig bytte ut Nylia med navnet du har brukt som overskrift i kolonnen i tabellen, eks. navn på det første familiemedlemmet. Og bytt arr med navnet på tabellen din.

Har du flere enn to navn må du gjenta punkt 4-6 flere ganger.

I neste rad legger du inn en tilsvarende formel, med navn nr. 2 (låven) i stedet for Nylia. Og tilsvarende i de neste radene hvis to har flere enn to navn.

Tips: Lim formelen inn i Notepad/Notisblokk eller tilsvarende og søk/erstatt Nylia med navnene du bruker.

Kopier celle B3 og B4 (osv.) til cellene i resten av uka.

Hent type hendelse

Nå skal vi sjekke hva slags type hendelse dette er, for å kunne gi rett fargekode. Gå til celle I3. Legg inn følgende matriseformel (tast CTRL+SHIFT+enter):

[code]
=HVIS(ANTALL.HVIS.SETT(arr[start];"<="&kalender!C2;arr[slutt];">="&kalender!C2)>0;
HVIS(INDEKS(arr[Nylia];MIN(HVIS((C2>=arr[start])*(C2<=arr[slutt]);SAMMENLIGNE(RAD(arr[arr]);RAD(arr[arr])))))="x";
INDEKS(arr[type];MIN(HVIS((C2>=arr[start])*(C2<=arr[slutt]);SAMMENLIGNE(RAD(arr[arr]);RAD(arr[arr])))));
HVIS(ANTALL.HVIS.SETT(arr[start];"<="&kalender!C2;arr[slutt];">="&kalender!C2)>1;
HVIS(INDEKS(arr[Nylia];N.MINST(HVIS((C2>=arr[start])*(C2<=arr[slutt]);SAMMENLIGNE(RAD(arr[arr]);RAD(arr[arr])));RAD($A$2)))="x";
INDEKS(arr[type];N.MINST(HVIS((C2>=arr[start])*(C2<=arr[slutt]);SAMMENLIGNE(RAD(arr[arr]);RAD(arr[arr])));RAD($A$2))));
""));"")
[/code]

Bytt ut «Nylia» med ditt første navn i tabellen.

Formelen sjekker omtrent på samme måte som forrige formel, men i stedet for navnet på arrangementet returneres type arrangement.

Betinget formatering

Nå er det på tide å fargelegge! Dette gjøres med betinget formatering. Kort fortalt legger du inn like mange regler som du har type arrangement/hendelser. Hver regel tester om arrangementet/hendelsen i denne cellen er av denne bestemte typen. Hvis ja fargelegg. Hvis nei, gå til neste regel.

Stå i celle B3. Klikk Betinget formatering,  og Ny regel eller Behandle regler.  Velg Bruk en formel til å bestemme… og skriv inn f.eks. følgende (erstatt «familie» med en av dine type hendelse):

[code]

=I3="familie"

[/code]

Og velg f.eks. rød bakgrunnsfarge under formatering. Legg til flere regler, en for hver type og gi hver type sin unike formatering (bakgrunnsfarge).

Kopier formateringen i celle B3 til alle de andre cellene den uka.

Gjøre kalenderen pen

Kopier via Word

Nå ligner kanskje kalenderen din på min. Fin og fargerik. Men litt forstyrrende at samme hendelse som går over flere dager står i mange celler.

Det var her jeg klødde meg i hodet. For hvordan skulle jeg få til å bevare formateringen, dvs. gjøre den betingede formateringen permanent før jeg slo sammen celler? Jeg prøvde med med utallige fiffige løsninger som fungert mer eller aller helst mindre. Til jeg kom på at det fantes en sabla enkel en, nemlig å gå via Word!

1. Marker hele kalenderen i Excel. (Altså radene i kolonne A:H, ikke de siste kolonnene).
2. Åpne et Word-dokument og lim inn som HTML.
3. Marker hele kalenderen i Word. Kopier Gå til Excel og lim inn i arket Ferdig kalender.

Nå ser kalenderen helt lik ut som den i kalender-arket, men den betingede formateringen har nå blitt permanent.

Slå sammen celler

For å slå sammen cellene skal du bruke makroer. En makro er en liten programsnutt som gjør kompliserte oppgaver i Excel kjapt. Makroer programmeres i VBA.

Du kan legge makroene inn i fila du jobber med eller en annen fil. Så lenge fila med makroer er åpen kan makroene kjøres på en hvilken som helst annen åpen Excel-bok. Excel-bøker med makroer må lagres som makro-filer: .xlsm og ikke bare .xls.

Åpne VBA ved å taste alt+F11. I menyen klikk på Insert og New Module. Lim inn koden under. Den består av flere makroer som utfører følgende oppgaver:

1. Slår sammen celler ved siden av hverandre i en rad og som har lik verdi.
2. Slår sammen celler under hverandre med lik verdi.
3. Bestemmer bredden på kolonnene.

Kommentarene i koden forteller deg om evt. endringer du bør gjøre. (Dessverre forsvinner innrykkene som gjør det lettere å lese koden når jeg kopierer den fra VBA og limer den inn i WordPress uten en egen plugin.)

[code]

Sub sjekkKolonner()
‘ SLÅR SAMMEN CELLER MED LIKE VERDI
‘ FØRSTE SJEKK ER AV NAVN NR 1
Range("b3").Select
‘FJERNER ADVARSLER OM DATA I SAMMENSLÅTTE CELLER
Application.DisplayAlerts = False

‘declare a variable to hold an integer
Dim j As Long
‘loop a certain number of times
For j = 1 To 48 ‘DENNE DELEN AV MAKROEN KJØRE 48 GANGER, ENDRE EVT. ANTALL TIL 52 OM KALENDEREN DEKKER ETT HELT ÅR

‘declare a variable to hold an integer
Dim i As Long
‘loop a certain number of times
For i = 1 To 6 ‘DENNE DELEN AV MAKROEN KJØRER 6 GANGER (FORDI UKA HAR 7 DAGER)
‘do something within the loop
If Not IsEmpty(ActiveCell.Value) Then

‘SAMMENLIGNE AKTIV CELLE MED DEN TIL HØYRE
aktiv = ActiveCell.Value
neste = ActiveCell.Offset(0, 1).Value
‘HVIS LIK, SLÅ DE SAMMEN
If aktiv = neste Then
Range(ActiveCell, ActiveCell.Offset(0, 1)).Select
Selection.Merge
‘HVIS IKKE GÅ TIL NESTE CELLE
Else
ActiveCell.Offset(0, 1).Select
End If
Else
ActiveCell.Offset(0, 1).Select
End If
Debug.Print i
Next i

‘GÅ TIL NESTE CELLE OG SÅ NESTE NYLIA-RAD
ActiveCell.Offset(0, 1).Select ‘FLYTT 0 PLASSER NED OG 1 PLASS TIL HØYRE
ActiveCell.Offset(3, -7).Select ‘FLYTT 3 PLASSER NED OG 7 PLASSER TIL VENSTRE, ØK 3 TIL 4,5,6 OSV UT FRA ANTALL NAVN

Debug.Print j
Next j
‘FØRSTE SJEKK FERDIG

‘ANDRE SJEKK, AV RAD MED NAVN NR 2, KOPIER EVT FOR B5 OSV. VED FLERE NAVN
Range("b4").Select
‘FJERNER ADVARSLER OM DATA I SAMMENSLÅTTE CELLER
Application.DisplayAlerts = False

‘declare a variable to hold an integer
Dim k As Long
‘loop a certain number of times
For k = 1 To 48 ‘MAKROEN KJØRE 48 GANGER

‘declare a variable to hold an integer
Dim l As Long
‘loop a certain number of times
For l = 1 To 6 ‘MAKROEN KJØRE 6 GANGER
‘do something within the loop
If Not IsEmpty(ActiveCell.Value) Then

‘SAMMENLIGNE AKTIV CELLE MED DEN TIL HØYRE
aktiv = ActiveCell.Value
neste = ActiveCell.Offset(0, 1).Value
‘HVIS LIK, SLÅ DE SAMMEN
If aktiv = neste Then
Range(ActiveCell, ActiveCell.Offset(0, 1)).Select
Selection.Merge
‘HVIS IKKE GÅ TIL NESTE CELLE
Else
ActiveCell.Offset(0, 1).Select
End If
Else
ActiveCell.Offset(0, 1).Select
End If
Debug.Print l
Next l

‘GÅ TIL NESTE CELLE OG NESTE LÅVEN-RAD
ActiveCell.Offset(0, 1).Select
ActiveCell.Offset(3, -7).Select

Debug.Print k
Next k
‘FØRSTE MAKRO FERDIG
End Sub

Sub sjekkRader()
‘ SLÅR SAMMEN CELLER MED LIKE VERDI

Range("b3").Select
‘FJERNER ADVARSLER OM DATA I SAMMENSLÅTTE CELLER
Application.DisplayAlerts = False

‘declare a variable to hold an integer
Dim j As Long
‘loop a certain number of times
For j = 1 To 48 ‘MAKROEN KJØRE 48 GANGER, ENDRE EVT. DENNE

‘declare a variable to hold an integer
Dim i As Long
‘loop a certain number of times
For i = 1 To 6 ‘MAKROEN KJØRE 6 GANGER
‘do something within the loop

If Not IsEmpty(ActiveCell.Value) Then
‘SAMMENLIGNE AKTIV CELLE MED DEN UNDER
aktiv = ActiveCell.Value
neste = ActiveCell.Offset(1, 0).Value
‘HVIS LIK, SLÅ DE SAMMEN
If aktiv = neste Then
Range(ActiveCell, ActiveCell.Offset(1, 0)).Select
Selection.Merge
‘HVIS IKKE GÅ TIL NESTE CELLE TIL HØYRE
Else
ActiveCell.Offset(0, 1).Select
End If
Else
ActiveCell.Offset(0, 1).Select
End If

Debug.Print i
Next i
‘GÅ TIL NESTE CELLE OG NESTE NYLIA-RAD, endre evt 3 til 4, 5, 6 osv
ActiveCell.Offset(3, 0).Select
Set rCell = Cells(ActiveCell.Row, "B")
rCell.Select
Debug.Print j
Next j
End Sub

Sub bredde()
‘ SETTER BREDDEN PÅ KOLONNENE, LAGER STREKER OG UTSKRIFTSOMRÅDE

ActiveSheet.StandardWidth = 11.2
Range("a1").Select
Selection.ColumnWidth = 6
Range("A1:H145").Select

With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With

Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone

With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ThemeColor = 1
.TintAndShade = -0.499984740745262
.Weight = xlMedium
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ThemeColor = 1
.TintAndShade = -0.499984740745262
.Weight = xlThin
End With

ActiveWindow.DisplayGridlines = False

With ActiveSheet.PageSetup
.PrintTitleRows = "A1:h1"
.PrintTitleColumns = ""
End With

ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.708661417322835)
.RightMargin = Application.InchesToPoints(0.511811023622047)
.TopMargin = Application.InchesToPoints(0.590551181102362)
.BottomMargin = Application.InchesToPoints(0.590551181102362)
.HeaderMargin = Application.InchesToPoints(0.31496062992126)
.FooterMargin = Application.InchesToPoints(0.31496062992126)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
.OddAndEvenPagesHeaderFooter = False
.DifferentFirstPageHeaderFooter = False
.ScaleWithDocHeaderFooter = True
.AlignMarginsHeaderFooter = True
.EvenPage.LeftHeader.Text = ""
.EvenPage.CenterHeader.Text = ""
.EvenPage.RightHeader.Text = ""
.EvenPage.LeftFooter.Text = ""
.EvenPage.CenterFooter.Text = ""
.EvenPage.RightFooter.Text = ""
.FirstPage.LeftHeader.Text = ""
.FirstPage.CenterHeader.Text = ""
.FirstPage.RightHeader.Text = ""
.FirstPage.LeftFooter.Text = ""
.FirstPage.CenterFooter.Text = ""
.FirstPage.RightFooter.Text = ""
End With
‘  ActiveWindow.SmallScroll Down:=30
‘ ActiveSheet.PageSetup.PrintArea = "$A$1:$H$145"
‘ Set ActiveSheet.HPageBreaks(2).Location = Range("A104")
‘ ActiveWindow.SmallScroll Down:=-87
‘ ActiveSheet.PageSetup.PrintArea = "$A$1:$H$145"
‘ ActiveWindow.SmallScroll Down:=-9
End Sub

[/code]

Nå er kalenderen klar til å skrives ut.

På nett

Trenger du å publisere kalenderen på nett kan du legge den ut som et bilde, en statisk tabell eller en dynamisk tabell. Hvordan kan jeg forklare senere om ønskelig.

Lenker/kilder

Jeg har klart å lage denne kalenderen med hjelp fra bl.a. følgende nettsteder:

• Excel-guru Ketil Øyesvold Melhus
  • Oversette engelske formler til ditt eget språk
• Home & learning
  • The Excel VBA Offset Property
• WiseOwl
  • Looping in Visual Basic for Applications macros