Existuje několik způsobů, jak Excel automaticky obnovit datová připojení a přepočítat list. Tyto triky jsou důležitější pro datový typ Stock v aplikaci Excel pro zákazníky Microsoft 365.
Normálně se Excel při změně automaticky aktualizuje sám hodnota buňky. V dnešní době existují situace, kdy buňky mění hodnotu, ale Excel neaktualizuje list. Jinými slovy, moderní Excel se změnil způsobem, na který se Microsoft dosud plně nepřizpůsobil.
V ideálním případě Excel by měl celkové nastavení pro aktualizaci listu každých n sekund nebo minut. Ve stávající podobě potřebujeme řešení, aby se to stalo.
Zde je několik situací, kdy může být nutné vynutit aktualizaci nebo přepočet dat nebo obezřetný.
Typ dat akcií
U datového typu akcií je důležitější schopnost automatické aktualizace. Uživatelé budou chtít, aby jejich listy uchopily lat automaticky odhadovat ceny, něco, co aktuální verze náhledu neumí. Místo pěkného automatického „tickeru“ se od nás očekává, že klikneme na „Obnovit“, abychom získali nejnovější ceny.
Datové typy Stock a Geo jsou zvědavá zvířata. Jedná se o datová připojení k externím zdrojům, ale NEZOBRAZUJÍ se jako datová připojení Excel. To znamená, že nemůžete nastavit automatické obnovení dat, jako byste to udělali u běžných datových připojení. Ve skutečnosti neexistují žádné vystavené ovládací prvky pro datové typy Stock nebo Geo.
NOW () a další volatilní funkce
Funkce NOW () se aktualizuje na nejnovější datum a čas, kdykoli Excel přepočítá list. Pokud se však k tomu nic neděje, Now () nezmění hodnotu. K provedení aktualizace aplikace Excel Now () a zbytku listu je potřeba nějaký externí faktor. Jinými slovy, měli byste být schopni nahlédnout do listu a vědět, že je až druhý, ale to není možné v Excelu „z krabice“.
Vlastní funkce VBA lze také označit jako volatilní pomocí tento řádek v kódu funkce:
Application.Volatile
Díky tomuto řádku bude funkce spuštěna kdykoli aplikace Excel aktualizuje / přepočítá list.
Zvláštní upozornění
Možná chcete, aby se váš list preventivně automaticky aktualizoval? Mnoho starých rukou aplikace Excel si pamatuje situace, kdy se aplikace Excel neaktualizovala správně, takže se jim líbí přístup „opasek a šle“ (alespoň příležitostně).
Kód
Standardní metoda vynucení automatického aktualizace aplikace Excel je krátký fragment kódu VBA. Zde používáme mnoho variací na stejné téma. Celý kód je ve spodní části článku.
Existují tři funkce.
RefreshAllDataConn
provede skutečné obnovení datových připojení (Workbooks (ThisWorkbook.Name) .RefreshAll) a přidali jsme dva volitelné řádky, které se zobrazí naposledy aktualizované na spodním stavovém řádku.
Pokud jste chtěli být velmi opatrní, přidejte řádek, abyste explicitně vynutili přepočet. Buď ActiveSheet.Calculate, nebo extrémní Application.CalculateFull (to by zpomalilo velký list, používejte střídmě).
AutoRefresh
spusťte každou minutu dílčí stránku RefreshAllDataConn nebo jakoukoli jinou hodnotu, kterou nastavíte řádek Application.OnTime Now + TimeValue („00:01:00“), „AutoRefresh“
Workbook_Open
vestavěná funkce aplikace Excel, která se spustí automaticky při otevření listu . V tomto případě spustí automatické obnovení.
Řešení datového připojení
Nevýhodou přístupu VBA je, že je nutný list .xlsm (list aplikace Excel s povoleným makrem). Při sdílení souborů s povolenými makry mohou nastat problémy z bezpečnostních důvodů.
Příchod PowerQuery / Get a Transformace znamená, že existuje další způsob, jak vynutit přepočet listu. Jedná se o alternativní řešení, které není dokonalé, ale je možné a nepotřebujete pracovní list s podporou maker.
Stručně řečeno, zajistěte, aby existovalo nastavení dotazu na data s automatickým obnovováním. Pokud není k dispozici datové připojení, přidejte do listu malé.
Jakmile budete mít automaticky aktualizovaný dotaz, měl by se také obnovit list včetně všech těkavých funkcí.
V ideálním případě by se měly aktualizovat také datové typy Stock a Geo.
Jakýkoli datový dotaz aplikace Excel přichází s některými možnostmi obnovení ve vlastnostech dotazu. Většina z nich je výchozí VYPNUTO.
Obnovit každých nnn minut – výchozí nastavení je 60 minut.
Obnovit data při otevření souboru
Povolit aktualizaci na pozadí
Obnovit toto připojení při Obnovit vše
Řešením automatického obnovení je vytvořit malý a prakticky bezvýznamný datové připojení. Poté nakonfigurujte toto datové připojení tak, aby se aktualizovalo každou minutu nebo kdykoli chcete. To by mělo přinutit list aktualizovat, včetně těkavých funkcí uvedených výše.
Některé verze tohoto řešení přidávají odkaz na malý soubor CSV ve stejném počítači. Stejného výsledku jsme dosáhli pomocí datového připojení z tabulky v listu.
Vytvořte malou tabulku s jedinou buňkou. Buňka může mít cokoli, ale vytvoříme buňku s NOW (), z důvodů, které vysvětlíme později.
Vyberte tabulku a poté Data | Získat data | Z jiných zdrojů | z tabulky / rozsahu. Přesná položka nabídky se může lišit v závislosti na verzi aplikace Excel.
Když se otevře Editor dotazů, jednoduše jej zavřete a načtěte . V podokně datových připojení se zobrazí dotaz.
Kliknutím pravým tlačítkem myši na dotaz zobrazíte vlastnosti nastavení, která potřebujeme.
Nastavte obnovovací frekvenci, která vám vyhovuje.
Abychom měli pořádek přesuňte zdrojovou tabulku (vpravo) na stejný list v načteném dotazu (vlevo). Protože jsme v buňce zdrojové tabulky použili NOW (), bude snadné zjistit, kdy / jestli se list obnovil.
Příklad kódu 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
Vytvořit automatický seznam nebo obsah listu aplikace Excel
Kompletní řešení Excel NetworkDays () s prázdninami & prázdniny