Fungsi OFFSET Excel akan memperlahankan pengiraan buku kerja anda. Terdapat alternatif yang lebih baik: sintaks INDEX yang tidak biasa.
Ini adalah petua khusus. Terdapat fungsi yang sangat fleksibel yang disebut OFFSET. Ia fleksibel kerana dapat menunjukkan jarak yang berbeza yang dihitung dalam perjalanan. Dalam gambar di bawah, jika seseorang mengubah dropdown # Qtrs di H1 dari 3 hingga 4, argumen OFFSET keempat akan memastikan bahawa julat berkembang hingga merangkumi empat lajur.
Guru spreadsheet benci OFFSET kerana ia adalah fungsi yang tidak menentu. Sekiranya anda pergi ke sel yang sama sekali tidak berkaitan dan memasukkan nombor, semua fungsi OFFSET akan dikira. Walaupun sel itu tidak ada kaitan dengan H1 atau B2. Selalunya, Excel sangat berhati-hati hanya membuang masa untuk mengira sel yang perlu dikira. Tetapi setelah anda memperkenalkan OFFSET, semua sel OFFSET, ditambah semua downline dari OFFSET, mula dikira setelah setiap perubahan dalam lembaran kerja.
Saya menilai final ModelOff 2013 di New York City ketika beberapa rakan saya dari Australia menunjukkan penyelesaian yang pelik. Dalam formula di bawah, terdapat titik dua sebelum fungsi INDEX. Biasanya, fungsi INDEX yang ditunjukkan di bawah akan mengembalikan 1403 dari sel D2. Tetapi apabila anda meletakkan titik dua di kedua sisi fungsi INDEX, ia mula mengembalikan alamat sel D2 dan bukannya kandungan D2. Ini liar kerana ia berfungsi.
Mengapa perkara ini berlaku? INDEX tidak mudah berubah. Anda mendapat semua kelebihan Fleksibel OFFSET tanpa pengiraan semula yang menghisap masa berulang-ulang.
Saya pertama kali mengetahui petua ini dari Dan Mayoh di Fintega. Terima kasih kepada Access Analytic kerana mencadangkan ciri ini.
Tonton Video
Transkrip Video
Belajar Excel dari podcast, episod 2048 -: INDEX Akan Mengganti OFFSET Bergejolak!
Hai, saya siarkan semua petua saya dari buku ini, klik “i” di sudut kanan atas untuk masuk ke senarai main!
Baiklah, OFFSET adalah fungsi yang luar biasa! OFFSET membolehkan kita menentukan sel sudut kiri atas, dan kemudian menggunakan pemboleh ubah untuk menentukan dari sana berapa baris ke bawah, berapa baris di atas, dan kemudian menentukan bentuk, baiklah. Jadi di sini, jika saya ingin menambah, atau rata-rata, katakan 3/4, formula ini di sini akan melihat formula. OFFSET mengatakan kita bermula dari B2, bermula dari Q1, kita akan turun 0, lebih dari 0, bentuknya akan tinggi 1 baris, dan akan menjadi H1, dengan kata lain sel lebar 3, baiklah. Jadi dalam kes ini, semua yang sebenarnya kita lakukan adalah mengubah berapa banyak sel yang kita tambah, kita selalu memulakan kembali B2, atau B3 atau B4 semasa saya menyalinnya, dan kemudian menentukan berapa sel yang luas. Baiklah, OFFSET adalah perkara yang keren ini, ia melakukan semua jenis fungsi yang luar biasa, tetapi inilah kerumitan, ia tidak menentu!Yang bermaksud bahawa walaupun ada sesuatu yang tidak ada dalam rantai pengiraan, Excel, akan meluangkan masa untuk mengira semula, yang akan memperlahankan keadaan, baiklah.
Versi INDEX yang menakjubkan ini, betul, biasanya jika saya meminta INDEX dari 4 sel ini, 3, ia akan mengembalikan nombor 1403. Walau bagaimanapun, apabila saya meletakkan titik dua sebelum atau selepas INDEX, sesuatu yang sangat berbeza berlaku, kita akan melihat formula ini di sini. Jadi indeks 4 sel, mana yang saya mahu, saya mahu yang ketiga, tetapi anda melihat ada: di sana. Oleh itu, kita selalu pergi dari B2: E2, dan saya akan menunjukkan kepada anda jika kita pergi ke Formula, Evaluate Formula, baiklah, jadi di sini ia akan mengira nombor 3. Dan di sini, INDEX dengan: seterusnya untuk itu, bukannya memberitahu kita 1403, seperti yang biasanya dikira oleh INDEX, ia akan mengembalikan $ D2, dan kemudian RATA-RATAnya akan menghasilkan rata-rata 3. Mereka sangat mengagumkan, cara ini berfungsi, dan faedah tambahan ia tidak mudah berubah, baiklah,dan ini bahkan boleh digunakan untuk menggantikan OFFSET yang sangat kompleks.
Jadi di sini dengan OFFSET ini kita berdasarkan nilai-nilai ini. Sekiranya saya memilih Q2 dan Central, baiklah, formula ini menggunakan MATCH dan COUNTIF untuk mengetahui berapa baris di bawah, berapa lajur di atas, berapa tinggi, seberapa luas. Dan kemudian OFFSET di sini menggunakan semua nilai tersebut untuk mengetahui mediannya. Kami hanya akan melakukan ujian untuk memastikan ia berfungsi, jadi = MEDIAN dari julat biru di sana, lebih baik 71, baiklah, dan kami akan memilih yang lain di sini, Q3 dan Barat, jadi. Tekan F2, saya hanya akan menyeret ini dan mengubah saiznya untuk menulis semula formula itu, tekan Enter, dan ia berfungsi dengan OFFSET.
Di sini, dengan menggunakan INDEX, saya sebenarnya mempunyai titik dua di tengah dengan INDEX di sebelah kiri dan INDEX di sebelah kanan, saksikan perkara ini dikira dalam Evaluate Formula. Jadi ia bermula, akan Menilai, Menilai, dan di sinilah INDEX akan mengubahnya menjadi alamat sel. Jadi H16 adalah yang ke-1, Barat, Q3, dan di sebelah kanan akan menilai, berpasangan lagi, dan kemudian betul ia berubah menjadi I20. Oleh itu, sejuk dan tidak mudah menguap untuk menggantikan OFFSET menggunakan fungsi INDEX. Baiklah, petua ini dan banyak lagi dalam buku ini, klik "i" di sudut kanan atas untuk membeli buku.
Ringkasan baik-baik saja: OFFSET, fungsi hebat dan fleksibel, setelah anda menguasai, anda boleh melakukan pelbagai jenis perkara. Arahkan ke sel kiri atas yang berubah-ubah, arahkan ke julat yang mempunyai bentuk berubah-ubah, tetapi tidak stabil, sehingga mereka mengira pada setiap pengiraan. Excel biasanya melakukan pengiraan pintar, atau mengira semula sel yang perlu dikira, tetapi dengan OFFSET ia akan selalu dikira. Daripada OFFSET, anda boleh menggunakan INDEX: atau: INDEX atau bahkan INDEX: INDEX, bila-bila masa INDEX mempunyai titik dua di sebelahnya, ia akan mengembalikan alamat sel dan bukan nilai sel itu. Dan faedahnya adalah INDEX tidak mudah berubah!
OK, saya ingin mengucapkan terima kasih kerana mampir, kami akan berjumpa dengan anda untuk lain-lain netcast dari!
Muat turun fail
Muat turun fail sampel di sini: Podcast2048.xlsm
