Der er flere måder at få Excel til automatisk at opdatere dataforbindelser og genberegne et regneark. Disse tricks er vigtigere med lagerdatatypen i Excel til Microsoft 365-kunder.
Normalt opdaterer Excel sig selv, når du ændrer en celleværdi. I disse dage er der situationer, hvor celler ændrer værdi, men Excel opdaterer IKKE regnearket. Med andre ord har moderne Excel ændret sig på måder, som Microsoft endnu ikke har tilpasset sig fuldt ud.
Ideelt set Excel ville have en samlet indstilling til at opdatere regnearket hvert n sekund eller minut. Som det står, har vi brug for løsninger for at få det til at ske.
Her er nogle situationer, hvor det kan være nødvendigt at tvinge dataopdatering eller genberegning eller klog.
Lagerdatatype
Med lagerdatatypen er evnen til at opdatere automatisk vigtigere. Brugere vil have deres regneark til at gribe lat estpriser automatisk, noget de nuværende forhåndsvisning ikke kan gøre. I stedet for at have en flot automatisk ticker forventes det, at vi klikker på Opdater for at få de nyeste priser.
Datatyperne Stock og Geo er nysgerrige dyr. De er dataforbindelser til eksterne kilder, men vises IKKE som Excel-dataforbindelser. Det betyder, at du ikke kan konfigurere en automatisk opdatering af data, som du ville gøre med normale dataforbindelser. Faktisk er der ingen eksponerede kontroller for Stock- eller Geo-datatyperne.
NU () og andre ustabile funktioner
NU () -funktionen opdateres til den seneste dato og tid, hver gang Excel er genberegner regnearket. Men hvis der ikke er noget, der får det til at ske, ændrer Now () ikke værdi. Der er behov for en ekstern faktor for at få Excel til at opdatere Now () og resten af regnearket. Med andre ord skal du være i stand til at kigge på et regneark og vide, at det er op til det andet, men det er ikke muligt med Excel ud af boksen.
VBA-brugerdefinerede funktioner kan også mærkes som flygtige ved hjælp af denne linje i funktionskoden:
Application.Volatile
Denne linje får funktionen til at køre når som helst Excel opdaterer / genberegner regnearket.
Ekstra forsigtighed
Måske vil du have, at dit regneark opdateres automatisk som en sikkerhedsforanstaltning? Mange gamle Excel-hænder husker situationer, hvor Excel ikke er opdateret korrekt, så de kan lide tilgangen til bælte og seler (i det mindste lejlighedsvis).
Kode
Standardmetoden til at tvinge automatisk opdatering af Excel er et kort stykke VBA-kode. Her er hvad vi bruger, der er mange variationer på det samme tema. Den fulde kode er nederst i artiklen.
Der er tre funktioner.
RefreshAllDataConn
udfører den aktuelle opdatering af dataforbindelser (Workbooks (ThisWorkbook.Name) .RefreshAll), og vi tilføjede to, valgfrie linjer for at vise den sidste opdaterede gang i den nederste statuslinje.
Hvis du ville være ekstra forsigtig, skal du tilføje linje for eksplicit at tvinge genberegning. Enten ActiveSheet.Calculate eller den ekstreme Application.CalculateFull (dette vil sænke et stort regneark, bruge sparsomt).
AutoRefresh
kør RefreshAllDataConn-sub hvert minut eller den værdi, du indstiller på linjen Application.OnTime Now + TimeValue (“00:01:00”), “AutoRefresh”
Workbook_Open
en indbygget Excel-funktion, der kører automatisk, når regnearket åbnes . I dette tilfælde starter det AutoRefresh.
Løsning af dataforbindelse
Ulempen ved VBA-tilgangen er, at et .xlsm-regneark er nødvendigt (makroaktiveret Excel-regneark). Der kan være problemer med at dele makroaktiverede filer på grund af sikkerhedsproblemer.
Ankomsten til PowerQuery / Get and Transform betyder, at der er en anden måde at tvinge en regneark til genberegning på. Det er en løsning og ikke perfekt, men det er muligt og har ikke brug for et makroaktiveret regneark.
Kort sagt skal du sikre dig, at der er en dataforespørgsel med automatisk opdatering. Hvis der ikke er en dataforbindelse, skal du tilføje en lille til regnearket.
Når du har en automatisk forfriskende forespørgsel, skal regnearket med eventuelle flygtige funktioner også opdateres.
Ideelt set bør datatyperne Stock og Geo også opdateres.
Enhver Excel-dataforespørgsel leveres med nogle opdateringsindstillinger i forespørgselsegenskaberne. De fleste af dem er som standard FRA.
Opdater hvert nnn minut – standard er slået fra med 60 minutter foreslået.
Opdater data, når filen åbnes
Aktivér baggrundsopdatering
Opdater denne forbindelse på Opdater alle
Den automatiske opdateringsløsning er at skabe en lille og praktisk talt ubetydelig dataforbindelse. Konfigurer derefter dataforbindelsen til at opdatere hvert minut eller uanset hvilken tid du ønsker. Det skulle tvinge regnearket til at opdatere inklusive de flygtige funktioner, der er nævnt ovenfor.
Nogle versioner af denne løsning tilføjer et link til en lille csv-fil på den samme computer. Vi har det samme resultat ved hjælp af en dataforbindelse fra en tabel i regnearket.
Opret en lille tabel med en enkelt celle. Cellen kan have alt, men vi opretter en celle med NOW () i af grunde, vi forklarer senere.
Vælg tabellen, og vælg derefter Data | Hent data | Fra andre kilder | fra tabel / rækkevidde. Det nøjagtige menupunkt kan være anderledes afhængigt af din version af Excel.
Når Forespørgselseditor åbnes, skal du bare lukke og indlæse det . I dataforbindelsesruden ser du en forespørgsel.
Højreklik på forespørgslen, vælg egenskaber for at se indstillinger, vi har brug for.
Indstil den opdateringshastighed, der passer til dig.
For at være ryddelig har vi flyt kildetabellen (til højre) på det samme ark ved den indlæste forespørgsel (til venstre). Fordi vi brugte NOW () i kildetabelcellen, vil det være let at se, hvornår / hvis regnearket er opdateret.
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
Lav automatisk Excel-regnearkliste eller indholdsfortegnelse
Komplet Excel NetworkDays () -løsning med helligdage & ferier