„Excel“ OFFSET funkcija sulėtins jūsų darbaknygės skaičiavimus. Yra geresnė alternatyva: neįprasta INDEX sintaksė.
Tai yra nišinis patarimas. Yra nuostabiai lanksti funkcija, vadinama OFFSET. Jis yra lankstus, nes gali nurodyti skirtingo dydžio diapazoną, kuris apskaičiuojamas skrendant. Žemiau esančiame paveikslėlyje, jei kas nors pakeis # Qtrs išskleidžiamąjį meniu H1 iš 3 į 4, ketvirtasis OFFSET argumentas užtikrins, kad diapazonas išsiplėstų įtraukiant keturis stulpelius.
Skaičiuoklės guru nekenčia OFFSET, nes tai yra nepastovi funkcija. Jei eisite į visiškai nesusijusį langelį ir įvesite skaičių, bus apskaičiuotos visos OFFSET funkcijos. Net jei ta ląstelė neturi nieko bendra su H1 ar B2. Dažniausiai „Excel“ labai atsargiai gaišta laiką tik apskaičiuodama langelius, kuriuos reikia apskaičiuoti. Bet kai įvesite OFFSET, visi OFFSET langeliai ir visa kita nuo OFFSET pradėta skaičiuoti po kiekvieno darbalapio pakeitimo.
Aš vertinau 2013 m. „ModelOff“ finalą Niujorke, kai keli mano draugai iš Australijos nurodė keistą problemą. Žemiau pateiktoje formulėje prieš funkciją INDEX yra dvitaškis. Paprastai žemiau parodyta INDEX funkcija grąžins 1403 iš langelio D2. Bet kai abiejose INDEX funkcijos pusėse dedate dvitaškį, jis pradeda grąžinti langelio adresą D2, o ne D2 turinį. Tai laukinė, kad ji veikia.
Kodėl tai svarbu? INDEX nėra nepastovi. Jūs gausite visą lankstų OFFSET gėrį be laiko sugaišančių perskaičiavimų.
Pirmą kartą šį patarimą sužinojau iš Dano Mayoho „Fintega“. Dėkojame „Access Analytic“, kad pasiūlė šią funkciją.
Žiūrėti video
Vaizdo įrašo nuorašas
Sužinokite „Excel“ iš tinklalaidės, 2048 epizodas -: INDEKSAS pakeis nepastovų OFFSETĄ!
Ei, aš perduodu visus savo patarimus iš šios knygos, viršutiniame dešiniajame kampe spustelėkite „i“, kad patektumėte į grojaraštį!
Gerai, OFFSET yra nuostabi funkcija! OFFSET leidžia mums nurodyti viršutinio kairiojo kampo langelį ir tada naudoti kintamuosius, kad iš ten apibrėžtume, kiek eilučių žemyn, kiek eilučių virš, ir tada nustatykite formą, gerai. Taigi čia, jei noriu susumuoti arba atlikti vidutinį, tarkime, 3/4, ši formulė čia apžvelgs formulę. OFFSET sako, kad pradedame nuo B2, pradedant nuo Q1, einame žemyn 0, virš 0, forma bus 1 eilutės aukščio ir H1, kitaip tariant, 3 langelių pločio, gerai. Taigi šiuo atveju viskas, ką iš tikrųjų darome, keičiasi, kiek ląstelių mes sudedame, mes visada pradedame B2, B3 ar B4, kai nukopijuoju žemyn, ir tada jis nusprendžia, kiek ląstelių pločio. Gerai, OFFSET yra šaunus dalykas, jis atlieka įvairias nuostabias funkcijas, bet čia yra vargo, jis yra nepastovus!Tai reiškia, kad net jei kažkas nėra skaičiavimo grandinėje, „Excel“ užtruks laiko, kad jį perskaičiuotų, o tai viską sulėtins, gerai.
Ši nuostabi INDEX versija, tiesa, paprastai, jei paprašyčiau šių 4 langelių INDEX, 3, tai grąžins skaičių 1403. Tačiau kai prieš INDEX arba po jo dedu dvitaškį, įvyksta kažkas labai skirtingo, apžvelgsime šią formulę čia. Taigi 4 langelių indeksas, kurio aš noriu, aš noriu trečio, bet matote, kad yra: čia pat. Taigi mes visada eisime nuo B2: E2, ir aš jums parodysiu, ar eisime į Formulės, Įvertinkime formulę, gerai, taigi čia bus apskaičiuotas skaičius 3. Ir čia, INDEKSAS su: next į jį, užuot pasakojęs mums 1403, kaip paprastai apskaičiuoja INDEX, jis grąžins D2 USD, o tada AVERAGE atliks tų 3 vidurkį. Visiškai nuostabu, kaip tai veikia, ir papildoma nauda, tai nepastovi, gerai,ir tai netgi gali būti naudojama norint pakeisti neįtikėtinai sudėtingą OFFSET.
Taigi čia su šiuo OFFSET mes remiamės šiomis vertybėmis. Jei pasirenku Q2 ir Central, gerai, šios formulės naudoja MATCH ir COUNTIF, kad išsiaiškintų, kiek eilučių žemyn, kiek stulpelių virš, kiek aukščio, kiek pločio. Tada OFFSET čia naudoja visas tas vertes, kad išsiaiškintų medianą. Mes tiesiog atliksime testą, kad įsitikintume, jog jis veikia, taigi = to mėlynojo diapazono MEDIENAS, geriau 71, gerai, ir mes čia pasirinksime ką nors kitą, Q3 ir West, taigi. Paspauskite F2, aš tiesiog perkelsiu šį dydį ir pakeisiu jo dydį, kad perrašyčiau tą formulę, paspauskite „Enter“ ir jis veikia su OFFSET
Na, naudojant INDEX, aš iš tikrųjų viduryje turiu dvitaškį, kurio kairėje pusėje yra INDEX, o dešinėje pusėje - INDEX, žiūrėkite, kaip šis dalykas apskaičiuojamas „Įvertinti formulę“. Taigi prasideda, įvertins, įvertins ir čia pat, kad „INDEX“ ruošiasi paversti jį langelio adresu. Taigi H16 yra 1-asis, Vakarai, Q3, ir dešinėje pusėje įvertins dar porą, ir tada ją pakeis į I20. Taigi kietas, kietas, nepastovus, norint pakeisti OFFSET naudojant INDEX funkciją. Gerai, šį patarimą ir daugelį kitų šios knygos, spustelėkite tą „i“ viršutiniame dešiniajame kampe, kad įsigytumėte knygą.
Gerai, apibendrinimas: OFFSET, nuostabi, lanksti funkcija, kai įsisavinsite, galėsite daryti įvairiausius dalykus. Nukreipkite pelės žymiklį į kintamą viršutinę kairę langelį, nurodykite kintamos formos diapazoną, tačiau jis yra nepastovus, todėl jie apskaičiuoja kiekvieną skaičiavimą. „Excel“ paprastai atlieka protingą skaičiavimą arba perskaičiuoja langelius, kuriuos reikia apskaičiuoti, tačiau naudojant OFFSET jis visada bus apskaičiuojamas. Vietoj OFFSET galite naudoti INDEX: arba: INDEX ar net INDEX: INDEX, bet kada, kai šalia INDEX yra dvitaškis, jis grąžins langelio adresą vietoj tos langelio vertės. Ir nauda yra INDEX nėra nestabili!
Gerai, noriu padėkoti už tai, kad užsukote, susitiksime kitą kartą, kai pasirodys dar viena netleris!
Atsisiųsti failą
Atsisiųskite failo pavyzdį čia: Podcast2048.xlsm
