Az Excel OFFSET funkciója lassítja a munkafüzet számítását. Van egy jobb alternatíva: az INDEX szokatlan szintaxisa.
Ez egy hiánypótló tipp. Van egy elképesztően rugalmas funkció, az úgynevezett OFFSET. Rugalmas, mert különböző méretű tartományra mutathat, amelyet menet közben számolnak. Az alábbi képen, ha valaki a H1 # Qtrs legördülő listáját 3-ról 4-re változtatja, az OFFSET negyedik argumentuma biztosítja, hogy a tartomány négy oszlopra bővüljön.
A táblázatos guruk utálják az OFFSET-et, mert ez egy ingatag függvény. Ha egy teljesen független cellába megy, és megad egy számot, akkor az összes OFFSET függvény kiszámításra kerül. Még akkor is, ha ennek a cellának semmi köze nincs a H1-hez vagy a B2-hez. Az Excel legtöbbször nagyon vigyáz arra, hogy csak a kalkulálandó cellák kiszámításával pazarolja az időt. De miután bevezette az OFFSET-et, az összes OFFSET-cella, plusz az OFFSET-től származó összes alsósor a munkalap minden módosítása után elkezd számolni.
A 2013-as New York-i ModelOff döntőt bíráltam, amikor néhány ausztrál barátom furcsa megoldásra hívta fel a figyelmet. Az alábbi képletben kettőspont található az INDEX függvény előtt. Normál esetben az alább látható INDEX függvény visszaküldi az 1403-at a D2 cellából. De amikor kettőspontot tesz az INDEX függvény mindkét oldalára, az a D2 cellacímét kezdi visszaadni a D2 tartalma helyett. Ez vad, hogy működik.
Miért számít ez? Az INDEX nem ingatag. Megkapja az OFFSET minden rugalmasságát az időigényes újraszámítások nélkül.
Először Dan Mayohtól tanultam ezt a tippet a Fintegában. Köszönet az Access Analytic-nek, hogy ezt a funkciót javasolta.
Videót nézni
Videó átirat
Tanulja meg az Excel-t a podcastból, 2048. epizód -: Az INDEX felváltja az illékony eltolást!
Hé, podcastolom a könyv összes tippjét, kattintson a jobb felső sarokban található „i” gombra, hogy a lejátszási listához jusson!
Rendben, az OFFSET egy csodálatos funkció! Az OFFSET lehetővé teszi számunkra, hogy meghatározzunk egy bal felső sarok cellát, majd a változók segítségével megadhatjuk onnan, hogy hány sor lefelé, hány sor van fent, majd definiálunk egy alakot, rendben. Tehát itt, ha összeadni akarok, vagy mondjuk 3/4-es átlagot, akkor ez a képlet itt megnézi a képletet. Az OFFSET szerint B2-ből indulunk, Q1-től indulva, 0-val megyünk lefelé, 0 felett, az alak 1 sor magas lesz, és H1 lesz, más szóval 3 cella széles, rendben. Tehát ebben az esetben csak annyit csinálunk, hogy megváltoztatjuk, hogy hány cellát adunk össze, mindig visszamegyünk B2-be, vagy B3-ba vagy B4-be, ahogy lemásolom, majd ez dönti el, hogy hány cella széles. Rendben, az OFFSET ez a klassz dolog, mindenféle elképesztő funkciókat végez, de itt van a szóváltás, ingatag!Ami azt jelenti, hogy még ha valami sincs is a számítási láncban, az Excel időt szán arra, hogy újraszámolja, ami lassítani fogja a dolgokat, rendben.
Az INDEX ezen csodálatos változata, rendesen, ha ennek a 4 cellának az indexét kérném, 3, akkor az az 1403-as számot adja vissza. Amikor azonban kettőspontot teszek az INDEX elé vagy után, valami egészen más történik, itt megnézzük ezt a képletet. Tehát a 4 cella indexe, melyiket akarom, a harmadikat akarom, de látja, hogy van: ott. Tehát mindig a B2: E2-től fogunk menni, és megmutatom, ha megyünk a Képletek, a Formula kiértékelése, rendben, szóval itt kiszámítja a 3. számot. És itt az INDEX a következővel ráadásul ahelyett, hogy elmondaná nekünk az 1403-at, amit az INDEX általában kiszámítana, visszatér $ D2-re, majd az ÁTLAG megteszi ezek 3-as átlagát. nem illékony, rendben,és ez akár egy hihetetlenül összetett OFFSET helyettesítésére is használható.
Tehát itt ezzel az OFFSET-tel ezeken az értékeken alapulunk. Ha a Q2 és a Central választást választom, rendben, akkor ezek a képletek a MATCH és a COUNTIF segítségével állapítják meg, hogy hány sor lefelé, hány oszlop fölött, mennyi magas, milyen széles. És akkor az OFFSET itt mindezeket az értékeket felhasználja a medián kitalálására. Csak tesztet fogunk végezni, hogy megbizonyosodjunk róla, hogy működik-e, tehát = ott a kék tartományú MEDIAN, jobb, ha 71, rendben van, és itt valami mást választunk, Q3 és West, tehát. Nyomja meg az F2 billentyűt, ezt csak áthúzom és átméretezem, hogy átírjam ezt a képletet, nyomja meg az Enter billentyűt, és ez működik az OFFSET-tel.
Nos, egy INDEX használatával valójában kettőspont van középen, a bal oldalon egy INDEX, a jobb oldalon pedig egy INDEX, figyelje, hogy ezt a dolgot kiszámolják a Formula értékelése. Tehát kiindul, kiértékeli, kiértékeli, és itt kezdi meg, hogy az INDEX egy cellacímré készül átalakítani. Tehát H16 az 1., Nyugat, Q3, és a jobb oldalon átértékeli, párosítja még, majd jobbra I20-ra változik. Tehát az a hűvös, hűvös, nem illékony, hogy az INDEX funkcióval helyettesítsen egy offsetet. Rendben, ez a tipp és még sok más ebben a könyvben, kattintson a jobb felső sarokban található „i” gombra a könyv megvásárlásához.
Rendben összefoglaló: OFFSET, fantasztikus, rugalmas funkció, ha egyszer elsajátítod, akkor mindenféle dolgot megtehetsz. Mutasson egy változó bal felső cellára, mutasson egy olyan tartományra, amelynek alakja változó, de ez ingatag, ezért minden számításnál kiszámolnak. Az Excel általában intelligens számítást végez, vagy újraszámolja a kiszámítandó cellákat, de az OFFSET használatával mindig kiszámítja. Az OFFSET helyett használhatja az INDEX: vagy: INDEX vagy akár INDEX: INDEX szót, bármikor az INDEX mellett van kettőspont, akkor egy cellacímet ad vissza a cella értéke helyett. Az előny pedig az, hogy az INDEX nem ingatag!
OK, szeretném megköszönni, hogy betértél, legközelebb találkozunk egy újabb netcast-lal!
Fájl letöltése
Töltse le a minta fájlt innen: Podcast2048.xlsm
