Det finns flera sätt att göra Excel automatiskt uppdatera dataanslutningar och beräkna ett kalkylblad igen. Dessa knep är viktigare med lagerdatatypen i Excel för Microsoft 365-kunder.
Normalt uppdateras Excel själv när du byter ett cellvärde. Dessa dagar finns det situationer där celler ändrar värde men Excel uppdaterar INTE kalkylbladet. Med andra ord har modern Excel förändrats på ett sätt som Microsoft ännu inte helt har anpassat sig till.
Helst, Excel skulle ha en övergripande inställning för att uppdatera kalkylbladet varannan eller minut. Som det är nu behöver vi lösningar för att det ska hända.
Här är några situationer där det kan vara nödvändigt att uppdatera eller omberäkna data klokt.
Lagerdatatyp
Med lagerdatatypen är möjligheten att uppdatera automatiskt viktigare. Användare vill att deras kalkylblad ska ta tag i lat est-priser automatiskt, något som de nuvarande förhandsversionerna inte kan göra. Istället för att ha en fin automatisk ”ticker” förväntas vi klicka på ”Uppdatera” för att få de senaste priserna.
Datatyperna Stock och Geo är nyfikna djur. De är dataanslutningar till externa källor men visas INTE som Excel-dataanslutningar. Det betyder att du inte kan konfigurera en automatisk datauppdatering, som du skulle göra med normala dataanslutningar. Faktum är att det inte finns några exponerade kontroller för datatyperna Stock eller Geo.
NOW () och andra flyktiga funktioner
NOW () -funktionen uppdateras till senaste datum och tid när Excel räknar om kalkylbladet. Men om det inte finns något som får det att hända ändrar inte värde () nu. Någon extern faktor behövs för att Excel ska uppdateras nu () och resten av kalkylbladet. Med andra ord bör du kunna titta på ett kalkylblad och veta att det är upp till det andra men det är inte möjligt med Excel out of the box.
VBA-anpassade funktioner kan också märkas som flyktiga med denna rad i funktionskoden:
Application.Volatile
Den raden gör att funktionen körs när som helst Excel uppdaterar / omberäknar kalkylbladet.
Extra försiktighet
Kanske vill du att ditt kalkylblad ska uppdateras automatiskt som en försiktighetsåtgärd? Många gamla Excel-händer kommer ihåg situationer där Excel inte har uppdaterats ordentligt så att de gillar ”bälte och hängslen” -metoden (åtminstone ibland).
Kod
Standardmetoden för att tvinga automatiskt uppdatering av Excel är ett kort utdrag av VBA-kod. Här är vad vi använder, det finns många variationer på samma tema. Hela koden finns längst ner i artikeln.
Det finns tre funktioner.
RefreshAllDataConn
gör den faktiska uppdateringen av dataanslutningar (Workbooks (ThisWorkbook.Name) .RefreshAll) och vi lade till två, valfria, rader för att visa den senaste uppdateringen i den nedre statusfältet.
Om du vill vara extra försiktig, lägg till rad för att uttryckligen tvinga omberäkning. Antingen ActiveSheet.Calculate eller den extrema Application.CalculateFull (detta skulle sakta ner ett stort kalkylblad, använd sparsamt).
AutoRefresh
kör RefreshAllDataConn-sub varje minut eller vilket värde du ställer in på raden Application.OnTime Now + TimeValue (”00:01:00”), ”AutoRefresh”
Workbook_Open
en inbyggd Excel-funktion som körs automatiskt när kalkylbladet öppnas . I det här fallet startar det AutoRefresh.
Lösning av dataanslutning
Nackdelen med VBA-metoden är att ett .xlsm-kalkylblad är nödvändigt (makroaktiverat Excel-kalkylblad). Det kan finnas problem med att dela makroaktiverade filer på grund av säkerhetsproblem.
Ankomsten av PowerQuery / Get and Transform innebär att det finns ett annat sätt att tvinga fram en omräkning av kalkylbladet. Det är en lösning och inte perfekt, men det är möjligt och behöver inte ett makroaktiverat kalkylblad.
Kort sagt, se till att det finns en datafrågeinställning med automatisk uppdatering. Om det inte finns en dataanslutning lägger du till en liten i kalkylbladet.
När du har en automatisk uppfriskningsfråga bör också kalkylbladet med alla flyktiga funktioner uppdateras.
Helst bör datatyperna Stock och Geo också uppdateras.
Alla Excel-datafrågor kommer med några uppdateringsalternativ i Query-egenskaperna. De flesta av dem är som standard AV.
Uppdatera varje nnn minut – standardinställningar av med 60 minuter föreslagna.
Uppdatera data när filen öppnas
Aktivera bakgrundsuppdatering
Uppdatera denna anslutning på Uppdatera alla
Den automatiska uppdateringslösningen är att skapa en liten och praktiskt taget obetydlig uppkoppling. Konfigurera sedan den dataanslutningen för att uppdatera varje minut eller vilken tid du vill. Det borde tvinga kalkylbladet att uppdatera inklusive de flyktiga funktioner som nämns ovan.
Vissa versioner av den här lösningen lägger till en länk till en liten csv-fil på samma dator. Vi har samma resultat med en dataanslutning från en tabell i kalkylbladet.
Skapa en liten tabell med en enda cell. Cellen kan ha vad som helst men vi skapar en cell med NOW () i, av skäl som vi kommer att förklara senare.
Välj tabellen och välj sedan Data | Hämta data | Från andra källor från tabell / intervall. Det exakta menyalternativet kan variera beroende på din version av Excel.
När frågeredigeraren öppnas är det bara att stänga och ladda det . I dataförbindningsfönstret ser du en fråga.
Högerklicka på frågan, välj egenskaper för att se inställningar vi behöver.
Ställ in uppdateringsfrekvensen som passar dig.
För att vara snygg, vi flytta källtabellen (höger) till samma ark vid den laddade frågan (vänster). Eftersom vi använde NOW () i källtabellcellen blir det lätt att se när / om kalkylbladet har uppdaterats.
Exempel på VBA-kod
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
Skapa automatisk Excel-kalkylblad eller innehållsförteckning
Komplett Excel NetworkDays () -lösning med helgdagar & semester