Det er flere måter å få Excel til å oppdatere datatilkoblinger automatisk og beregne et regneark på nytt. Disse triksene er viktigere med lagerdatatypen i Excel for Microsoft 365-kunder.
Normalt oppdateres Excel selv når du endrer en celleverdi. I disse dager er det situasjoner der celler endrer verdi, men Excel oppdaterer IKKE regnearket. Med andre ord, moderne Excel har endret seg på måter som Microsoft ennå ikke har tilpasset seg helt.
Ideelt sett Excel vil ha en generell innstilling for å oppdatere regnearket hvert n. Sekund eller minutt. Som det er nå, trenger vi løsninger for å få det til.
Her er noen situasjoner der det kan være nødvendig å tvinge dataoppdatering eller omberegning eller forsvarlig.
Lagerdatatype
Med lagerdatatypen er muligheten til å oppdatere automatisk viktigere. Brukere vil at regnearkene deres skal ta tak i lat estpriser automatisk, noe de nåværende forhåndsutgivelsene ikke kan. I stedet for å ha en fin automatisk ‘ticker’, forventes det at vi klikker på ‘Refresh’ for å få de nyeste prisene.
Datatypene Stock og Geo er nysgjerrige dyr. De er dataforbindelser til eksterne kilder, men vises IKKE som Excel-datatilkoblinger. Det betyr at du ikke kan konfigurere en automatisk dataoppdatering, slik du ville gjort med normale datatilkoblinger. Faktisk er det ingen eksponerte kontroller for datatypene Stock eller Geo.
NOW () og andre ustabile funksjoner
NOW () -funksjonen oppdateres til siste dato og klokkeslett når Excel beregner regnearket på nytt. Men hvis det ikke er noe å gjøre som skjer, endrer ikke Now () verdi. Noen eksterne faktorer er nødvendig for å gjøre Excel oppdatert Now () og resten av regnearket. Med andre ord, bør du kunne se på et regneark og vite at det er opp til det andre, men det er ikke mulig med Excel out of the box.
Tilpassede VBA-funksjoner kan også merkes som flyktige ved å bruke denne linjen i funksjonskoden:
Application.Volatile
Denne linjen får funksjonen til å kjøre når som helst Excel oppdaterer / beregner regnearket på nytt.
Ekstra forsiktighet
Kanskje du vil at regnearket skal oppdateres automatisk som en forholdsregel? Mange gamle Excel-hender husker situasjoner der Excel ikke har blitt oppdatert ordentlig, slik at de liker tilnærmingen belt and braces (i det minste av og til).
Kode
Standardmetoden for å tvinge automatisk oppdatering av Excel er et kort utdrag av VBA-kode. Her er hva vi bruker, det er mange varianter på samme tema. Hele koden er nederst i artikkelen.
Det er tre funksjoner.
RefreshAllDataConn
gjør den faktiske oppdateringen av datatilkoblinger (arbeidsbøker (ThisWorkbook.Name) .RefreshAll), og vi la til to, valgfrie linjer for å vise siste gang oppdatert på nederste statuslinje.
Hvis du ville være ekstra forsiktig, legg til linje for å eksplisitt tvinge ny beregning. Enten ActiveSheet.Calculate eller den ekstreme Application.CalculateFull (dette vil redusere et stort regneark, bruk sparsomt).
AutoRefresh
kjør RefreshAllDataConn-sub hvert minutt eller hvilken verdi du setter på linjen Application.OnTime Now + TimeValue («00:01:00»), «AutoRefresh»
Workbook_Open
en innebygd Excel-funksjon som kjører automatisk når regnearket åpnes . I dette tilfellet starter den AutoRefresh.
Løsning av datatilkobling
Ulempen med VBA-tilnærmingen er at et .xlsm-regneark er nødvendig (makroaktivert Excel-regneark). Det kan være problemer med å dele makroaktiverte filer på grunn av sikkerhetsproblemer.
Ankomsten av PowerQuery / Get and Transform betyr at det er en annen måte å tvinge et regneark til omberegning. Det er en løsning og ikke perfekt, men det er mulig og trenger ikke et makroaktivert regneark.
Kort sagt, sørg for at det er et datasettoppsett med automatisk oppdatering. Hvis det ikke er en dataforbindelse, kan du legge til en liten i regnearket.
Når du har et automatisk forfriskende spørsmål, skal regnearket, inkludert eventuelle flyktige funksjoner, også oppdateres.
Ideelt sett bør datatypene Lager og Geo også oppdateres.
Alle Excel-spørringer kommer med noen oppdateringsalternativer i spørringsegenskapene. De fleste av dem er som standard AV.
Oppdater hvert nnnminutt – standard er av med 60 minutter foreslått.
Oppdater data når du åpner filen
Aktiver bakgrunnsoppdatering
Oppdater denne tilkoblingen på Oppdater alle
Den automatiske oppdateringen er å skape en liten og praktisk talt ubetydelig datakobling. Konfigurer deretter datatilkoblingen for å oppdatere hvert minutt eller når som helst du ønsker. Det bør tvinge regnearket til å oppdatere, inkludert de ustabile funksjonene nevnt ovenfor.
Noen versjoner av denne løsningen legger til en lenke til en liten csv-fil på samme datamaskin. Vi har samme resultat ved hjelp av en dataforbindelse fra en tabell i regnearket.
Lag en liten tabell med en enkelt celle. Cellen kan ha alt, men vi oppretter en celle med NOW () i, av grunner som vi vil forklare senere.
Velg tabellen og velg deretter Data | Få data | Fra andre kilder | fra tabell / rekkevidde. Det eksakte menyelementet kan variere avhengig av din versjon av Excel.
Når spørreditoren åpnes, er det bare å lukke og laste det inn . I datatilkoblingsruten ser du et spørsmål.
Høyreklikk på spørringen, velg egenskaper for å se innstillinger vi trenger.
Still inn oppdateringsfrekvensen som passer deg.
For å være ryddig, vi flytte kildetabellen (høyre) til samme ark ved den lastede spørringen (til venstre). Fordi vi brukte NOW () i kildetabellcellen, vil det være lett å se når / om regnearket er oppdatert.
Eksempel på VBA-kode
Sub RefreshAllDataConn() " Refresh all Data Connections. " This should include Stock and Geo data types, even though they aren"t listed. Workbooks(ThisWorkbook.Name).RefreshAll " Show update time on status bar to confirm. " comment these lines out if not needed. Application.DisplayStatusBar = True Application.StatusBar = "Refreshed at: " & Now()End SubSub AutoRefresh()" to run a Refresh All on the workbook every n minutes RefreshAllDataConn " Repeat every minute or change to whatever value you prefer. Application.OnTime Now + TimeValue("00:01:00"), "AutoRefresh"" this is a simple example. There"s no coded way to exit this function.End SubPrivate Sub Workbook_Open()" Starts the automatic refresh when the workbook is opened," commented out as a precaution. " AutoRefreshEnd Sub
Lag automatisk Excel-regnearkliste eller innholdsfortegnelse
Komplett Excel NetworkDays () -løsning med høytider & ferier