Csak nyitott munkafüzetből frissítő képletek

Power BI Desktop alapok Hamarosan Megnézem Két Online délelőtt PowerQuery bevezető Hamarosan Megnézem Két online délelőtt Videó Megnézem - Excel makró alapok - Power BI alapok - Excel PIVOT mester Tanfolyamok - PowerQuery alapok

A másik munkafüzet celláira, tartományaira hivatkozást csatolásnak vagy linknek nevezzük. Nemrég írtam egy cikket arról, hogy hogyan szüntethetjük meg rövid úton a csatolásokat. Érdemes elolvasni.

Most pedig arról lesz szó, hogy milyen esetekben számítsunk arra, hogy egy másik munkafüzetre hivatkozó képlet csak akkor mutat eredményt, azaz számol, ha az a másik (forrás) munkafüzet is nyitva van.

A jelenség

Elkészítjük a képleteinket, amik a másik munkafüzetbe hivatkoznak. Ekkor nyilván az is nyitva van, és minden szuperül működik. Majd ezután mindent bezárunk, elmentünk, stb… Aztán amikor legközelebb kinyitjuk a képleteket tartalmazó munkafüzetet,  a külső hivatkozás miatt látjuk az ismerős kérdést:

A Frissítés gombra kattintva azonban egy következő üzenet érkezhet:

A Csatolások szerkesztése menüben ellenőrizhetjük, hogy jó munkafüzetre hivatkozunk-e, satöbbi. A Tovább gombra kattintva azt tapasztaljuk, hogy néhány, vagy akár egészen sok képlet helyén a cellában a #HIV (#REF) vagy esetleg az #ÉRTÉK (#VALUE) hibaüzenet jelenik meg.

Ha a forrás munkafüzetet is megnyitjuk, akkor minden helyreáll.

Magyarázat

Vannak olyan esetek, amikor az Excel egyszerűen nem frissít zárt forrásmunkafüzetből.

A) Ha a forrás munkafüzetben egy táblázatra vagy annak részére struktúrált hivatkozást használva hivatkoztunk

Forras-1.xlsx'!Tablázatnév[Oszlopnév]

B) Ha a forrás munkafüzetben egy névre hivatkozunk, és a név mögött képlet van (Dinamikus név)

C) Vannak zárt munkafüzetből nem frissítő függvények

• Az összes feltétel szerinti összesítő függvény
  • DARABTELI, DARABHATÖBB, SZUMHAÖBB, ÁTLAGHATÖBB, stb…
• Az összes adatbázis függvény
  • AB.SZUM, AB.ÁTLAG, AB.DARAB, stb…
• Egyéb függvények
  • ELTOLÁS, INDIREKT, KIMUTATÁSADATOT.VESZ

Kerülő megoldás

A feltételes összegző és adatbázis függvényeket ki lehet váltani olyan tömbképletekkel, amik önmagukban nem tartalmazzák a problémás függvényeket, ezért egy kicsit lassabban számolva ugyan, de használhatók zárt munkafüzetből is. Ezzel persze vállaljuk a fantasztikus tömbképletekkel járó macerákat.

Letölthető demo fájlok

Frissites_zart_munkafuzetbol

És persze a VIDEÓ