Andas nytt liv i ett äldre Excel-kalkylblad

Alla är överens om att det är en dålig idé att hålla ett Excel-kalkylblad i verken efter dess utgångsdatum. Tyvärr, i praktiken är det inte alltid ett alternativ att börja från början. Ofta är nyckeln till framgång perspektiv - en enkel strategiförändring. Arket som visas i figur A är ett bra exempel på hur en förändring i perspektiv kan avslöja en enkel lösning. Det är en förenklad version av ett äldre ark som har växt fram till sitt ursprungliga syfte.

Figur A

Ett arvblad som har vuxit fram sitt ursprungliga syfte.

Den ursprungliga arbetsboken har använts i 15 år till dess gränser. I det här fallet har en befintlig affärsregel kastat en skiftnyckel till en ny begäran. Ledningen vill ha frånvarotimmar uppdelade på hela och delvisa dagar. Till att börja med begärde bladets vårdgivare en summeringsformel för att hantera detta med följande överväganden:

  • Befintliga formler måste förbli som de är på grund av beroenden.
  • Arbetsdagstimmar är inkonsekventa: Måndag till torsdag är arbetsdagen 8, 75 timmar. Fredagen är en 7, 5 timmars dag. En hel arbetsvecka är 42, 5 timmar.
  • Måste behålla befintlig struktur, men det är tillåtet att lägga till kolumner.
  • Tomma celler, snarare än värdet 0, anger missade dagar.

Ledningens begäran är rimlig, och det är svårt att föreställa sig varför det kan vara problematiskt i det enkla exemplet du ser. Jag måste tigga dig lite över - du ser inte de dussintals raderna med anställda och de dussintals kolumner som rymmer övertid, fackavgifter, specialpriser, försäkringspremier, semestertimmar, skattesatser och så vidare.

Den ursprungliga arbetsboken är full av detaljer. Många företag köper mjukvara för att göra vad den här (kompletta) arbetsboken gör. Nackdelen med all denna funktionalitet är hantering; det kan vara svårt att se skogen för träden.

Innan vi börjar hamra ut en lösning, låt oss granska de befintliga formlerna:

  • Kolumn G: = SUM (B6: F6) summerar de faktiska arbetade timmarna.
  • Kolumn H: = (42, 5-G6) returnerar antalet frånvarande timmar genom att subtrahera de faktiska arbetade timmarna från hela arbetsveckovärdet 42, 5.
  • Kolumn I: = COUNTBLANK (B6: F6) returnerar antalet missade arbetsdagar.

Titta nu på vad som händer när samma anställd missar en hel dag och några timmar från en annan arbetsdag ( figur B ). Tekniskt sett är värdena i kolumn H korrekta, men det är inte vad ledningen vill se. Den nuvarande formeln är totalt timmarna; ledningen vill att antalet helt eller delvis missade dagar beräknas separat. Med andra ord, vi behöver två nya summor: ämnen som är lika med 8, 75 eller 7, 50 och värden som är mindre än 8, 75 och 7, 50.

Figur B

Den nuvarande formeln uppgår till timmarna.

Användaren tillbringade lite tid på att försöka lägga till och subtrahera timmar och dagar, men du kunde testa flera komplexa formler bara för att kasta dina händer i luften i nederlag. I det här fallet är nyckeln att sluta tänka på timme- och dagskomponenter - det är ett tågvrak. Det är inte omöjligt, men det är svårare än nödvändigt. Vi har inte konsekventa värden som vi kan summera. Vi har tomma ögon, så låt oss använda dem. Att tillämpa ett värde på varje blanktecken, beroende på veckodagen, är lättare än att försöka beräkna vad som ska vara, men inte. Figur C visar den nya lösningen och det tog de två nya enkla formlerna för att möta ledningens begäran:

  • Kolumn J: = COUNTBLANK (B6: E6) * 8, 75 + COUNTBLANK (F6) * 7, 5
  • Kolumn K: = (42, 5-G6) -J6

Figur C

Den nya lösningen.

Den nya formeln i kolumn J multiplicerar tomma ämnen som finns i en måndag till torsdagcell med 8, 75, ett tomt i fredagscellen med 7, 5 och summerar sedan dessa produkter. Formeln i kolumn K drar bort det nya värdet i kolumn J från det totala antalet arbetade timmar minus värdet i kolumn G (arbetade timmar).

I det här fallet fungerade inte arbetstidens summering, så vi gav ett värde till ämnena och summerade dem istället. En växel i strategi avslöjade en enkel lösning.

Arvet fortsätter

Tillväxten är bra, och om du har tur kommer den enkla arbetsbok du började med att rymma den tillväxten. Realistiskt sett är det ofta svårt att lägga till nya krav efter faktumet - "Om jag visste att du skulle behöva ... skulle jag ha ..." Ofta är den skyldige inte dålig design eller något så akademiskt. Ibland växer verksamheten helt enkelt från arbetsboks ursprungliga syfte, och det är ett bra problem att ha! När detta händer och du inte kan bygga om, tänka om.

Du kan använda de nedladdningsbara .xlsx- och .xls-filerna för att upptäcka en annan lösning, men för att göra saker intressanta, försök att behålla användarens begränsningar. Dela dina lösningar i kommentarerna nedan.

Skicka mig din fråga om Office

Jag svarar på läsarnas frågor när jag kan, men det finns ingen garanti. När du kontaktar mig, var så specifik som möjligt: ​​Till exempel "Felsök min arbetsbok och fixa vad som är fel" kommer förmodligen inte att få något svar, men "Kan du berätta varför denna formel inte ger de förväntade resultaten?" makt. Jag ersätts inte av TechRepublic för min tid eller min expertis, och jag begär inte heller en avgift från läsarna. Du kan kontakta mig på


© Copyright 2020 | mobilegn.com