Există mai multe modalități prin care Excel reîmprospătează automat conexiunile de date și recalculează o foaie de lucru. Aceste trucuri sunt mai importante cu tipul de date stoc în Excel pentru clienții Microsoft 365.
În mod normal, Excel se va actualiza singur când schimbați o valoare a celulei. În aceste zile există situații în care celulele schimbă valoarea, dar Excel NU actualizează foaia de lucru. Cu alte cuvinte, Excelul modern s-a schimbat în moduri la care Microsoft nu s-a adaptat încă complet.
În mod ideal, Excel ar avea o setare generală pentru a reîmprospăta foaia de lucru la fiecare n secunde sau minute. În starea actuală, avem nevoie de soluții alternative pentru ca aceasta să se întâmple.
Iată câteva situații în care poate fi necesară forțarea reîmprospătării sau recalcularii datelor sau prudent.
Tipul de date stoc
Cu tipul de date stoc, capacitatea de actualizare automată este mai importantă. Utilizatorii vor dori ca foile lor de lucru să prindă lat Prețurile sunt în mod automat, ceea ce lansările actuale de previzualizare nu pot face. În loc să avem un „ticker” automat, trebuie să facem clic pe „Reîmprospătare” pentru a obține cele mai recente prețuri.
Tipurile de date Stock și Geo sunt fiare curioase. Sunt conexiuni de date la surse externe, dar NU apar ca conexiuni de date Excel. Asta înseamnă că nu puteți configura o reîmprospătare automată a datelor, așa cum ați face cu conexiunile de date normale. De fapt, nu există controale expuse pentru tipurile de date Stock sau Geo.
ACUM () și alte funcții volatile
Funcția ACUM () se actualizează la data și ora cele mai recente ori de câte ori Excel recalculează foaia de lucru. Dar dacă nu există nimic care să facă acest lucru, Now () nu schimbă valoarea. Este necesar un anumit factor extern pentru ca actualizarea Excel Now () și restul foii de lucru. Cu alte cuvinte, ar trebui să puteți arunca o privire la o foaie de lucru și să știți că este până la a doua, dar acest lucru nu este posibil cu Excel „out of the box”.
Funcțiile personalizate VBA pot fi, de asemenea, etichetate ca volatile folosind această linie din codul funcției:
Application.Volatile
Această linie face funcția să ruleze oricând Excel actualizează / recalcă foaia de lucru.
Atenție suplimentară
Poate doriți ca foaia dvs. de lucru să se actualizeze automat ca măsură de precauție? Multe mâini vechi Excel își amintesc situațiile în care Excel nu s-a actualizat corect, așa că le place abordarea „centurii și acoladelor” (cel puțin ocazional).
Cod
Metoda standard de forțare automată actualizarea Excel este un fragment scurt de cod VBA. Iată ce folosim, există multe variante pe aceeași temă. Codul complet se află în partea de jos a articolului.
Există trei funcții.
RefreshAllDataConn
face reîmprospătarea reală a conexiunilor de date (Workbooks (ThisWorkbook.Name) .RefreshAll) și am adăugat două linii, opționale, pentru a afișa ultima dată reîmprospătată în bara de stare de jos. > Dacă vrei să fii foarte atent, adaugă linie pentru a forța explicit recalcularea. Fie ActiveSheet.Calculate, fie Extreme Application.CalculateFull (acest lucru ar încetini o foaie de lucru mare, utilizați-o cu ușurință).
AutoRefresh
rulați subfresca RefreshAllDataConn în fiecare minut sau orice valoare ați setat pe linia Application.OnTime Now + TimeValue („00:01:00”), „AutoRefresh”
Workbook_Open
o funcție Excel încorporată care rulează automat la deschiderea foii de lucru . În acest caz, pornește AutoRefresh.
Soluție conexiune date
Dezavantajul abordării VBA este că este necesară o foaie de lucru .xlsm (foaie de lucru Excel activată pentru macro). Pot exista probleme la partajarea fișierelor activate pentru macro din motive de securitate.
Sosirea PowerQuery / Get and Transform înseamnă că există un alt mod de a forța recalcularea foii de lucru. Este o soluție alternativă și nu este perfectă, dar este posibilă și nu are nevoie de o foaie de lucru activată pentru macro.
Pe scurt, asigurați-vă că există o configurare a interogării datelor cu reîmprospătare automată. Dacă nu există o conexiune de date, adăugați una mică la foaia de lucru.
După ce aveți o interogare de reîmprospătare automată, foaia de lucru, inclusiv orice funcții volatile, ar trebui să fie reîmprospătată.
În mod ideal, tipurile de date stoc și geo ar trebui să fie reîmprospătate.
Orice interogare de date Excel vine cu câteva opțiuni de reîmprospătare în proprietățile interogării. Cele mai multe dintre ele sunt OFF.
Reîmprospătați la fiecare nnn minute – opțiunea implicită este oprită cu 60 de minute sugerate.
Reîmprospătați datele la deschiderea fișierului
Activați reîmprospătarea fundalului
Reîmprospătați această conexiune pe Reîmprospătați toate
Soluția de reîmprospătare automată este de a crea un mic și practic nesemnificativ conexiune de date. Apoi configurați conexiunea de date pentru a se actualiza în fiecare minut sau oricând doriți. Acest lucru ar trebui să forțeze foaia de lucru să se actualizeze, inclusiv funcțiile volatile menționate mai sus.
Unele versiuni ale acestei soluții alternative adaugă un link către un mic fișier CSV de pe același computer. Am obținut același rezultat folosind o conexiune de date dintr-un tabel din foaia de lucru.
Creați un mic tabel cu o singură celulă. Celula poate avea orice, dar creăm o celulă cu ACUM (), din motive pe care le vom explica mai târziu.
Selectați tabelul, apoi alegeți Date | Obțineți date | Din alte surse | din tabel / interval. Elementul de meniu exact poate diferi în funcție de versiunea dvs. de Excel.
Când se deschide Editorul de interogări, trebuie doar să închideți și să îl încărcați . În panoul conexiunilor de date, veți vedea o interogare.
Faceți clic dreapta pe interogare, alegeți proprietăți pentru a vedea setările de care avem nevoie.
Setați rata de reîmprospătare care vă convine.
Pentru a fi ordonat, noi mutați tabelul sursă (dreapta) pe aceeași foaie la interogarea încărcată (stânga). Deoarece am folosit ACUM () în celula tabelului sursă, va fi ușor de văzut când / dacă foaia de lucru s-a reîmprospătat.
Exemplu de cod VBA
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
Realizați o listă automată de foi de lucru Excel sau un cuprins
Completați soluția Excel NetworkDays () cu sărbători & vacanțe