Istnieje kilka sposobów automatycznego odświeżania połączeń danych i ponownego obliczania arkusza przez program Excel. Te sztuczki są ważniejsze w przypadku typu danych giełdowych w programie Excel dla klientów Microsoft 365.
Zwykle program Excel aktualizuje się sam po zmianie wartość komórki. Obecnie zdarzają się sytuacje, w których komórki zmieniają wartość, ale program Excel NIE aktualizuje arkusza. Innymi słowy, nowoczesny program Excel zmienił się w sposób, do którego firma Microsoft jeszcze nie przystosowała się w pełni.
Idealnie, Program Excel miałby ogólne ustawienie odświeżania arkusza co „n” sekund lub minut. Obecnie potrzebujemy obejść, aby tak się stało.
Oto kilka sytuacji, w których wymuszenie odświeżenia lub ponownego obliczenia danych może być konieczne lub ostrożny.
Typ danych giełdowych
W przypadku typu danych giełdowych ważniejsza jest możliwość automatycznej aktualizacji. Użytkownicy będą chcieli, aby ich arkusze robocze pobierały szerokość ceny szacuj automatycznie, czego nie oferują obecne wersje zapoznawcze. Zamiast mieć ładny automatyczny „pasek”, powinniśmy kliknąć „Odśwież”, aby uzyskać najnowsze ceny.
Typy danych Stock i Geo to ciekawe bestie. Są to połączenia danych ze źródłami zewnętrznymi, ale NIE pojawiają się jako połączenia danych programu Excel. Oznacza to, że nie możesz skonfigurować automatycznego odświeżania danych, tak jak w przypadku normalnych połączeń danych. W rzeczywistości nie ma żadnych widocznych elementów sterujących dla typów danych Stock lub Geo.
NOW () i inne funkcje nietrwałe
Funkcja NOW () aktualizuje się do najnowszej daty i godziny, gdy program Excel przelicza arkusz. Ale jeśli nie ma nic, co mogłoby do tego doprowadzić, Now () nie zmienia wartości. Do zaktualizowania programu Excel Now () i pozostałej części arkusza potrzebny jest jakiś czynnik zewnętrzny. Innymi słowy, powinieneś być w stanie rzucić okiem na arkusz i wiedzieć, że to do sekundy, ale nie jest to możliwe w przypadku programu Excel „po wyjęciu z pudełka”.
Niestandardowe funkcje VBA można również oznaczyć jako zmienne za pomocą ten wiersz w kodzie funkcji:
Application.Volatile
Ten wiersz powoduje uruchomienie funkcji za każdym razem, gdy program Excel aktualizuje / przelicza arkusz.
Dodatkowa ostrożność
Może chcesz, aby na wszelki wypadek arkusz roboczy był aktualizowany automatycznie? Wiele starych rąk Excela pamięta sytuacje, w których Excel nie został poprawnie zaktualizowany, więc podoba im się podejście „pas i szelki” (przynajmniej czasami).
Kod
Standardowa metoda wymuszania automatycznego aktualizacja programu Excel to krótki fragment kodu VBA. Oto, czego używamy, istnieje wiele odmian tego samego tematu. Pełny kod znajduje się na dole artykułu.
Istnieją trzy funkcje.
RefreshAllDataConn
dokonuje faktycznego odświeżenia połączeń danych (skoroszyty (ThisWorkbook.Name) .RefreshAll) i dodaliśmy dwie opcjonalne linie do wyświetlania ostatniego odświeżenia na dolnym pasku stanu.
Jeśli chcesz być bardzo ostrożny, dodaj linię, aby wymusić ponowne obliczenie. Albo ActiveSheet.Calculate, albo ekstremalne Application.CalculateFull (spowolniłoby to duży arkusz roboczy, używaj oszczędnie).
AutoRefresh
uruchamiaj RefreshAllDataConn co minutę lub jakąkolwiek ustawioną wartość wiersz Application.OnTime Now + TimeValue („00:01:00”), „AutoRefresh”
Workbook_Open
wbudowana funkcja programu Excel, która uruchamia się automatycznie po otwarciu arkusza . W tym przypadku uruchamia funkcję AutoRefresh.
Obejście dla połączenia danych
Wadą podejścia VBA jest to, że konieczny jest arkusz roboczy .xlsm (arkusz programu Excel z włączoną obsługą makr). Mogą wystąpić problemy z udostępnianiem plików obsługujących makra ze względu na bezpieczeństwo.
Pojawienie się PowerQuery / Get and Transform oznacza, że istnieje inny sposób na wymuszenie ponownego obliczenia arkusza. Jest to obejście i nie jest doskonałe, ale jest możliwe i nie wymaga arkusza z włączoną obsługą makr.
Krótko mówiąc, upewnij się, że istnieje konfiguracja zapytania o dane z automatycznym odświeżaniem. Jeśli nie ma połączenia danych, dodaj małe połączenie do arkusza.
Po zapytaniu automatycznie odświeżającym się, arkusz zawierający funkcje nietrwałe również powinien zostać odświeżony.
Idealnie byłoby, gdyby typy danych Stock i Geo również się odświeżyły.
Każde zapytanie dotyczące danych programu Excel ma pewne opcje odświeżania we właściwościach zapytania. Większość z nich jest domyślnie WYŁĄCZONA.
Odśwież co nnn minut – domyślnie wyłączone, sugerowane 60 minut.
Odśwież dane podczas otwierania pliku
Włącz odświeżanie w tle
Odśwież to połączenie przy odświeżaniu wszystkiego
Obejście autoodświeżania polega na utworzeniu małego i praktycznie nieistotnego łącze danych. Następnie skonfiguruj to połączenie danych, aby aktualizowało się co minutę lub o dowolnej porze. Powinno to zmusić arkusz do aktualizacji, w tym wymienionych wyżej funkcji nietrwałych.
Niektóre wersje tego obejścia dodają łącze do niewielkiego pliku CSV na tym samym komputerze. Ten sam wynik uzyskaliśmy, używając połączenia danych z tabeli w arkuszu.
Utwórz małą tabelę z jedną komórką. Komórka może mieć wszystko, ale tworzymy komórkę z TERAZ () w niej, z powodów, które wyjaśnimy później.
Zaznacz tabelę, a następnie wybierz Dane | Pobierz dane | Z innych źródeł | z tabeli / zakresu. Dokładna pozycja menu może się różnić w zależności od wersji programu Excel.
Po otwarciu Edytora zapytań po prostu zamknij go i załaduj . W okienku połączeń danych zobaczysz zapytanie.
Kliknij zapytanie prawym przyciskiem myszy, wybierz właściwości, aby wyświetlić potrzebne nam ustawienia.
Ustaw odpowiednią częstotliwość odświeżania.
Aby zachować porządek, przenieś tabelę źródłową (po prawej) do tego samego arkusza przy załadowanym zapytaniu (po lewej). Ponieważ użyliśmy NOW () w źródłowej komórce tabeli, łatwo będzie zobaczyć, kiedy / czy arkusz został odświeżony.
Przykład kodu 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
Utwórz automatyczną listę arkuszy lub spis treści programu Excel
Kompletne rozwiązanie Excel NetworkDays () ze świętami & wakacje