On olemassa useita tapoja saada Excel päivittämään tietoyhteydet automaattisesti ja laskemaan laskentataulukko uudelleen. Nämä temput ovat tärkeämpiä Microsoft 365 -asiakkaiden Excel-tietotyypin kanssa.
Normaalisti Excel päivittää itsensä, kun muutat soluarvo. Nykyään on tilanteita, joissa solut muuttavat arvoa, mutta Excel EI päivitä laskentataulukkoa. Toisin sanoen moderni Excel on muuttunut tavoilla, joihin Microsoft ei ole vielä täysin sopeutunut.
Ihannetapauksessa Excelillä olisi yleinen asetus päivittää laskentataulukko n: n sekunnin tai minuutin välein. Nykyisessä muodossaan tarvitsemme kiertotapoja sen toteuttamiseksi.
Tässä on joitain tilanteita, joissa tietojen päivittäminen tai uudelleenlaskenta voi olla tarpeen tai varovainen.
Osaketietotyyppi
Varastotietotyypin avulla mahdollisuus päivittää automaattisesti on tärkeämpää. Käyttäjät haluavat, että heidän taulukkonsa tarttuvat latiin hinnat automaattisesti, mitä nykyiset esikatseluversiot eivät voi tehdä. Sen sijaan, että meillä olisi mukava automaattinen ”ticker”, meidän odotetaan napsauttavan ”Päivitä” saadaksesi viimeisimmät hinnat.
Osaketiedot ja maantieteelliset tietotyypit ovat uteliaita petoja. Ne ovat datayhteyksiä ulkoisiin lähteisiin, mutta EIVÄT näy Excel-datayhteyksinä. Tämä tarkoittaa, että et voi määrittää automaattista tietojen päivitystä, kuten normaalien datayhteyksien yhteydessä. Itse asiassa osakkeille tai maantieteellisille tietotyypeille ei ole altistuneita ohjausobjekteja.
NOW () ja muut epävakaat toiminnot
NOW () -toiminto päivittyy viimeisimpään päivämäärään ja aikaan aina, kun Excel laskee laskentataulukon uudelleen. Mutta jos mikään ei saa aikaan sitä, Now () ei muuta arvoa. Joitakin ulkoisia tekijöitä tarvitaan Excel-päivityksen nyt () ja muun laskentataulukon tekemiseen. Toisin sanoen sinun pitäisi pystyä vilkaisemaan laskentataulukkoa ja tietää, että se on toisessa, mutta se ei ole mahdollista Excelin kanssa ”out of the box”.
VBA-mukautetut toiminnot voidaan myös merkitä haihtuviksi käyttämällä tämä rivi toimintokoodissa:
Application.Volatile
Tämä rivi saa toiminnon toimimaan milloin tahansa, kun Excel päivittää / laskee laskentataulukon uudelleen.
Erityistä varovaisuutta
Ehkä haluat, että laskentataulukko päivittyy automaattisesti varotoimenpiteenä? Monet vanhat Excel-kädet muistavat tilanteita, joissa Excel ei ole päivittynyt kunnolla, joten he pitävät ”vyö ja henkselit”-lähestymistavasta (ainakin toisinaan).
Koodi
Vakiomenetelmä automaattisen pakottamiseksi Excelin päivitys on lyhyt katkelma VBA-koodista. Tätä käytämme, samasta teemasta on monia muunnelmia. Koko koodi on artikkelin alaosassa.
Toimintoja on kolme.
RefreshAllDataConn
päivittää todelliset tietoyhteydet (työkirjat (ThisWorkbook.Name) .RefreshAll) ja lisäsimme kaksi valinnaista riviä näyttämään viimeksi päivitetyn ajan alemmassa tilarivissä.
Jos haluat olla erityisen varovainen, lisää rivi pakottaaksesi nimenomaan uudelleenlaskennan. Joko ActiveSheet.Calculate tai äärimmäinen Application.CalculateFull (tämä hidastaisi suurta laskentataulukkoa, käytä säästeliäästi).
Automaattinen päivitys
Suorita RefreshAllDataConn-osa joka minuutti tai mikä tahansa asettamasi arvo rivi Application.OnTime Now + TimeValue (”00:01:00”), ”AutoRefresh”
Workbook_Open
sisäänrakennettu Excel-toiminto, joka toimii automaattisesti, kun laskentataulukko avataan . Tässä tapauksessa se käynnistää automaattisen päivityksen.
Datayhteyden kiertotapa
VBA-lähestymistavan haittana on, että .xlsm-laskentataulukko on välttämätön (makrokäyttöinen Excel-laskentataulukko). Makroyhteensopivien tiedostojen jakamisessa voi olla ongelmia tietoturvaongelmien vuoksi.
PowerQuery / Get and Transformin saapuminen tarkoittaa, että on toinen tapa pakottaa laskentataulukon uudelleenlaskenta. Se on kiertotapa eikä täydellinen, mutta se on mahdollista eikä vaadi makrokäyttöistä laskentataulukkoa.
Lyhyesti sanottuna varmista, että tietokysely on määritetty automaattisella päivityksellä. Jos datayhteyttä ei ole, lisää pieni taulukkoon.
Kun sinulla on automaattinen päivityskysely, laskentataulukon sisältävien laskentataulukoiden tulisi myös päivittyä.
Ihannetapauksessa myös Stock- ja Geo-tietotyyppien tulisi päivittyä.
Kaikissa Excel-tietokyselyissä on joitain päivitysvaihtoehtoja Query-ominaisuuksissa. Suurin osa heistä on oletusarvoisesti POIS. > Päivitä tiedot tiedostoa avattaessa
Ota taustapäivitys käyttöön
Päivitä tämä yhteys päivittämällä kaikki
Automaattisen päivityksen kiertotapa on luoda pieni ja käytännössä merkityksetön datayhteys. Määritä sitten datayhteys päivittymään joka minuutti tai haluamasi aika. Tämän pitäisi pakottaa laskentataulukko päivittymään, mukaan lukien yllä mainitut haihtuvat toiminnot.
Jotkin tämän kiertotavan versiot lisäävät linkin pieneen csv-tiedostoon samalla tietokoneella. Saimme saman tuloksen käyttämällä taulukon taulukon datayhteyttä.
Luo pieni taulukko, jossa on yksi solu. Solussa voi olla mitä tahansa, mutta luomme solun, jossa on NOW (), syistä, jotka selitämme myöhemmin.
Valitse taulukko ja sitten Data | Hae tietoja | Muista lähteistä taulukosta / alueelta. Tarkka valikkokohta voi olla erilainen riippuen Excel-versiostasi.
Kun kyselyeditori avautuu, sulje ja lataa se . Datayhteysruudussa näkyy kysely.
Napsauta hiiren kakkospainikkeella kyselyä ja valitse ominaisuudet, niin näet tarvitsemamme asetukset.
Aseta sinulle sopiva virkistystaajuus.
Ollaksemme siistejä, siirrä lähdetaulukko (oikea) samalle arkille ladatulla kyselyllä (vasen). Koska käytimme lähdetaulukon solussa NOW (), on helppo nähdä, milloin / onko laskentataulukko päivitetty.
VBA-koodiesimerkki
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
Tee automaattinen Excel-laskentataulukko tai sisällysluettelo
Täydellinen Excel NetworkDays () -ratkaisu lomilla & lomat