Excel- formler med veckodagar

[zince]

Hej,

Jag är nybliven schemaläggare och tänkte förbättra den otroligt tidskrävande manuella processen med några av de fantastiska funktionerna i Excel. Problemet är dock att jag kan väldigt lite om det, är självlärd nybörjare. Har kört fast med en förmodligen enkel grej och spenderat timmar i mina försök att googla fram svaret.

 

Jag skulle vilja skapa formler som ger mig tabell med statistik - hur många vardagsjourer, fredagsjourer och helgjourer varje person har den aktuella perioden.

• Tyckte mig ha hittat en formel som funkar bra för vecka 1 men kan inte få in de andra veckorna i den, får ut felmeddelanden hela tiden.
• Då tänkte att kanske bäst att välja hela perioden och köra någon OM funktion - om dagen är mån-tors, räkna antal celler med det aktuella namnet, sen visste jag inte hur jag skulle få in fredagar och helger där. Och hur göra med de andra röda dagarna.
• Och ett annat problem - helgjourer delas på två personer, då vill jag räkna halvdagar i statistiken. Då kan jag inte ha villkor att initialerna i cellen överensstämmer med personens initialer utan det skall vara att cellen innehåller även de initialerna. Men det vet jag inte heller hur man gör.

Svårt att förklara, förmodligen blir det enklast om jag visar hur det ser ut idag (med min formel för första veckan) https://www.dropbox.com/s/poieq5afjtl0kzz/MALL_m%C3%A5nadsschema%20redigeradkopia.xlsx (samma fil i bilagan)

 

Mycket tacksam om någon vänlig själ kan hjälpa mig.

 

MALL_månadsschema redigeradkopia.xlsx

[Monshi]

Veckonummer och Excel...

 

Vilken version av Excel har du? Som du kanske vet finns det olika sätt att räkna fram vilken vecka som är vecka 1 och Excel har haft en amerikanska enbart länge. I år verkar den iofs ge rätt svar för må-lör, men söndag blir fel

=VECKONR(B10;21)

ger alltid rätt i Sverige.

men det blev lite ändringar i arket

1: Ändrat så att din tabell har data på varje rad, dvs veckonummer på varje rad.

2: Lagt till kolumner som ger stöd åt beräkningar, dagnummer, jour i tre olika kolumner.

3: En cell där du anger veckonummer du vill sammanställa över

4: Och formler i stil med

=ANTAL.OMF($E$4:$E$31;K5;$A$4:$A$31;$L$1;$I$4:$I$31;"<=4")

 

Du kan med fördel formatera om ditt område som en tabell och därmed få lite enklare formler att förstå samt få dynamisk uppdatering av formlerna/referenserna.

 

 

 

MALL_månadsschema redigeradkopia.xlsx

[zince]

Monshi du kommer som en ståtlig riddare på en vit häst och lättar stackars flickas tunga börda! Jag är väldigt tacksam.

Skulle aldrig själv kommit på att införa extra tabellrader för beräkningarnas skull. Tänkte göra dem dolda i den färdiga varianten, då borde de inte synas för slutanvändare eller skrivas ut, eller hur.

 

Men har fortfarande några bekymmer (Excel 2007).

• jag var nog otydlig om att jag ville få hela 4-veckors statistik i statistiktabellen, ej uppdelad på veckor. Då antar jag det spelar ingen roll om det finns veckonummer i alla A-celler eller bara i den översta i varje vecka (undrar hur du fick till så att veckonumret inte syns i varje cell, formatering?). Då behövs det inte heller att blanda i veckonumret i formeln men jag vågar inte börja böka med din formel, den är bra mycket över min kunskapsnivå.
• Sedan ser jag att det har ändå blivit nåt tok med fredag (har gulmarkerat) - nu räknas den både som fredag och halva helg.
• Gillar din fiffiga lösning att dela upp helgjourerna i två separata hjälpkolumner. Men ibland går en och samma person helgjour hela dygnet. Ser att det räknas rätt om man skriver samma namn två gånger (som i D9). Men om det bara står ett namn (D10) räknas det som grundjour. Går det att dela upp i hjälpkolumnerna automatiskt på ett enkelt sätt även om det står ett namn eller är det enklast att skriva två gånger? 

MALL_månadsschema redigeradkopia_1.xlsx

[Monshi]

Excel 2007. Okej, mitt minne sviker mig, Veckonummer(a2;21) fungerar väl där?

 

1. Fyraveckorssummering, bara ändra i formeln så den tar en startvecka och en slutvecka som argument.
   Se om du kan lista ut hur jag gjort:
   =ANTAL.OMF(Tabell1[beredskap];K5;Tabell1[v];">="&$L$1;Tabell1[v];"<="&$L$2;Tabell1[Veckodag];"<=4")
2. Miss av mig, Formeln för helgjour1 ska ju ge tomt cell när vardag
   =OM(ÄRFEL(HITTA(" ";[@jour]));"";RENSA(VÄNSTER([@jour];HITTA(" ";[@jour]))))
3. Gjorde antagandet att två namn, skilda med mellanslag, innebar att det var helg. Om du markerar helg på annat sätt, formeln måste veta att det är helg, kan man givetvis göra så att samma namn räknas dubbelt med en enkel OM-sats.
   Men då behövs, jag poängterar, något som visar att det är helg. Kanske att markera namnet med en * om dubbeljour eller skriva namnet dubbelt.
4. Dölja siffror, formatering. Satt texten till samma färg som bakgrunden.
   Skulle även kunna skapa en kolumn du senare döljer för detta och en OM-formel som ser till att bara skriver ut värde på måndagar om man nu inte vill visa veckonummer alla dagar.

Extra beräkningskolumner underlättar och, som du säger, bara att dölja för användaren.

 

Anar att det du egentligen vill är att visa en månads jour.

Tips då är

1: Skapa en lista med datum, första dagen i varje månad.

2: Formatera så att månaden visas enbart, eller kanske månad och år.

3: Skapa en dataverifieringslista med denna tabell som grund.

4: Användaren väljer månad, du räknar fram vecka för start och vecka för slut.

 

1.  

[zince]

Har ändå försökt att rodda själv lite och tycker mig ha löst statistiken för beredskap och vardags- och fredagsjourer. Vad jag ser funkar det bra, den enda hjälptabellen jag behöver använda är dagnummer.

 

Får klia mig i huvudet om helgjourerna nu.

 

Men blir riktigt förbryllad över cellen A7 - varför säger Excel att det är nåt fel där?? Den ser ju exakt lika som A6 och A8 

 

 

MALL_månadsschema redigeradkopia_2.xlsx

[zince]

1. Excel 2007. Okej, mitt minne sviker mig, Veckonummer(a2;21) fungerar väl där?

​Jag använde =VECKONR(B4). (från tillägg Analys)

1. Fyraveckorssummering, bara ändra i formeln så den tar en startvecka och en slutvecka som argument.
   Se om du kan lista ut hur jag gjort:
   =ANTAL.OMF(Tabell1[beredskap];K5;Tabell1[v];">="&$L$1;Tabell1[v];"<="&$L$2;Tabell1[Veckodag];"<=4")

Jag gjorde helt utan veckonumren, typ =ANTAL.OMF($E$4:$E$31;K5;$I$4:$I$31;"<=4"). 

1. Miss av mig, Formeln för helgjour1 ska ju ge tomt cell när vardag
   =OM(ÄRFEL(HITTA(" ";[@jour]));"";RENSA(VÄNSTER([@jour];HITTA(" ";[@jour]))))
2. Gjorde antagandet att två namn, skilda med mellanslag, innebar att det var helg. Om du markerar helg på annat sätt, formeln måste veta att det är helg, kan man givetvis göra så att samma namn räknas dubbelt med en enkel OM-sats.
   Men då behövs, jag poängterar, något som visar att det är helg. Kanske att markera namnet med en * om dubbeljour eller skriva namnet dubbelt.

Ja, kanske borde införa en till dold kolumn som talar om när det är helg. Att veckodag 6 och 7 är helg och de röda kalenderdagarna också är helg. Finns det en färdig formel för det med datum som utgångsvariabel? Jag försökte använda ARBETSDAGAR men kom ingenvart. Eller en formel som bockar i dag 6 och 7 men de röda dagarna bockar jag i manuellt?

Hur skulle då helgjoursformel se ut?

1. Dölja siffror, formatering. Satt texten till samma färg som bakgrunden.

​Najs!!

 

1. Skulle även kunna skapa en kolumn du senare döljer för detta och en OM-formel som ser till att bara skriver ut värde på måndagar om man nu inte vill visa veckonummer alla dagar. 

​Det här förstod jag inte

 

Anar att det du egentligen vill är att visa en månads jour.

Tips då är

1: Skapa en lista med datum, första dagen i varje månad.

2: Formatera så att månaden visas enbart, eller kanske månad och år.

3: Skapa en dataverifieringslista med denna tabell som grund.

4: Användaren väljer månad, du räknar fram vecka för start och vecka för slut.

 

Javisst var ursprungstanken en månad men vi har kört med 4-veckorsperioder (manuellt) sedan tidigare och alla är vana med det. Att få till en hel månad känns alldeles för avancerat och inte så viktigt eller nödvändigt.  dataverifieringslista med denna tabell som grund låter som kinesiska för mig. 

 

 

MALL_månadsschema redigeradkopia_3.xlsx

[Monshi]

Okej, du vill enbart ha dessa fyra veckor. Jag tänkte att tabellen skulle löpa vidare, fyllas på med fler veckor.

 

men men, om tabellen är fast kan du skippa veckonummer i formlerna och bara ta de andra villkoren.

 

Formeln Veckonr, fungerar den variant jag skrivit in i bladet?

För formeln Veckonr (i excel 2007) saknar korrekt argument för att fungera 100% i Sverige, vissa år blir det fel vecka helt enkelt.

http://office.microsoft.com/sv-se/excel-help/funktionen-veckonr-HP010062299.aspx

 

Helgdagar. Enklaste lösningen är en tabell över alla helgdagar på året. Många dagar är fast till vissa datum, andra räknas fram och kanske det finns mall på nätet för alla dagar.

Grunden är att skapa en tabell med alla datum som är helg och söka i denna tabell. Om träff på visst datum, ja då är det helg.

Lördag/söndag är lätt att hålla reda på (veckodag 6 och 7 helt enkelt).

 

Helgjour, så som ditt ark ser ut

I G4

=OM(ÄRFEL(HITTA(" ";D4));OM(ELLER(I4>5;J4="x");D4;"");RENSA(VÄNSTER(D4;HITTA(" ";D4))))

i H4

....

 

äsch, blev en hel del ändringar. Du får ett litet exempel på tabellformat också i hanteringen av helgdagar. Du får fylla på med de dagar som gäller i år.

 

Dataverifiering, den struntar vi i. Du behöver den inte.

 

MALL_månadsschema redigeradkopia.xlsx

[zince]

Tack för hjälpen, har nu fått till mitt schema ungefär som jag ville.

Gjorde en hjälpkolumn för helgdagarna. Begrep inte riktigt hur man får helgdagar att hoppa in automatiskt, skall föra in dem manuellt (det är inte så besvärligt).