Fungsi OFFSET Excel akan memperlambat perhitungan buku kerja Anda. Ada alternatif yang lebih baik: sintaks INDEX yang tidak biasa.
Ini adalah tip khusus. Ada fungsi luar biasa fleksibel yang disebut OFFSET. Ini fleksibel karena dapat menunjuk ke kisaran ukuran berbeda yang dihitung dengan cepat. Pada gambar di bawah ini, jika seseorang mengubah dropdown # Qtrs di H1 dari 3 menjadi 4, argumen OFFSET keempat akan memastikan bahwa rentang diperluas untuk menyertakan empat kolom.
Guru spreadsheet membenci OFFSET karena ini adalah fungsi yang mudah menguap. Jika Anda pergi ke sel yang sama sekali tidak terkait dan memasukkan angka, semua fungsi OFFSET akan dihitung. Bahkan jika sel itu tidak ada hubungannya dengan H1 atau B2. Sering kali, Excel sangat berhati-hati untuk hanya membuang waktu menghitung sel yang perlu dihitung. Tapi begitu Anda memperkenalkan OFFSET, semua sel OFFSET, ditambah semua downline dari OFFSET, mulai menghitung setelah setiap perubahan dalam lembar kerja.
Saya sedang menilai final ModelOff 2013 di New York City ketika beberapa teman saya dari Australia menunjukkan solusi yang aneh. Dalam rumus di bawah ini, ada titik dua sebelum fungsi INDEX. Biasanya, fungsi INDEX yang diperlihatkan di bawah ini akan mengembalikan 1403 dari sel D2. Tapi saat Anda meletakkan titik dua di kedua sisi fungsi INDEX, itu mulai mengembalikan alamat sel D2 dan bukan konten D2. Ini liar yang berhasil.
Mengapa ini penting? INDEX tidak mudah berubah. Anda mendapatkan semua kebaikan fleksibel OFFSET tanpa perhitungan ulang yang menghabiskan waktu berulang-ulang.
Saya pertama kali mempelajari tip ini dari Dan Mayoh di Fintega. Terima kasih kepada Access Analytic karena telah menyarankan fitur ini.
Menonton video
Transkrip Video
Belajar Excel dari podcast, episode 2048 -: INDEX Akan Menggantikan OFFSET yang Bergejolak!
Hai, saya membuat podcasting semua tip saya dari buku ini, klik "i" di pojok kanan atas untuk masuk ke playlist!
Baiklah, OFFSET adalah fungsi yang luar biasa! OFFSET memungkinkan kita untuk menentukan sel sudut kiri atas, dan kemudian menggunakan variabel untuk menentukan dari sana berapa banyak baris ke bawah, berapa banyak baris, dan kemudian menentukan bentuk, oke. Jadi di sini, jika saya ingin menjumlahkan, atau melakukan rata-rata, katakanlah 3/4, rumus ini di sini akan melihat rumusnya. OFFSET mengatakan kita mulai dari B2, mulai dari Q1, kita turun 0, lebih dari 0, bentuknya akan menjadi 1 baris, dan itu akan menjadi H1, dengan kata lain lebar 3 sel, oke. Jadi dalam hal ini yang benar-benar kami lakukan adalah mengubah berapa banyak sel yang kami tambahkan, kami selalu memulai kembali di B2, atau B3 atau B4 saat saya menyalin, dan kemudian memutuskan berapa banyak sel lebar. Baiklah, OFFSET adalah hal yang keren ini, ia melakukan semua jenis fungsi yang luar biasa, tetapi inilah kerumitannya, ia mudah berubah!Artinya, meskipun ada sesuatu yang tidak ada dalam rantai kalkulasi, Excel akan meluangkan waktu untuk menghitung ulang, yang akan memperlambat segalanya, baiklah.
Versi INDEX yang luar biasa ini, benar, biasanya jika saya meminta INDEX dari 4 sel ini, 3, itu akan mengembalikan angka 1403. Namun, ketika saya meletakkan titik dua sebelum atau sesudah INDEX, sesuatu yang sangat berbeda terjadi, kita akan melihat rumus ini di sini. Jadi indeks dari 4 sel, yang mana yang saya inginkan, saya ingin yang ketiga, tetapi Anda lihat ada: di sana. Jadi kita akan selalu pergi dari B2: E2, dan saya akan menunjukkan kepada Anda jika kita pergi ke Rumus, Evaluasi Rumus, baiklah, jadi di sini akan menghitung angka 3. Dan di sini, INDEX dengan: berikutnya untuk itu, alih-alih memberi tahu kita 1403, yang biasanya akan dihitung oleh INDEX, itu akan mengembalikan $ D2, dan kemudian RATA-RATA akan melakukan rata-rata 3. Benar-benar luar biasa, cara kerjanya, dan manfaat tambahan, itu tidak mudah berubah, baiklah,dan ini bahkan dapat digunakan untuk menggantikan OFFSET yang sangat kompleks.
Jadi di sini dengan OFFSET ini kami didasarkan pada nilai-nilai ini. Jika saya memilih Q2 dan Central, baiklah, rumus ini menggunakan MATCH dan COUNTIF untuk mengetahui berapa banyak baris ke bawah, berapa banyak kolom, berapa tinggi, berapa lebar. Dan kemudian OFFSET di sini menggunakan semua nilai tersebut untuk menghitung median. Kami hanya akan melakukan tes untuk memastikan bahwa itu berfungsi, jadi = MEDIAN dari rentang 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 ukurannya untuk menulis ulang rumus itu, tekan Enter, dan itu bekerja dengan OFFSET.
Nah di sini, dengan menggunakan INDEX, saya sebenarnya memiliki titik dua di tengah dengan INDEX di sisi kiri dan INDEX di sisi kanan, perhatikan hal ini dihitung dalam Formula Evaluasi. Jadi itu dimulai, akan Mengevaluasi, Mengevaluasi, dan di sini INDEX akan mengubahnya menjadi alamat sel. Jadi H16 adalah yang pertama, Barat, Q3, dan di atas di sisi kanan akan dievaluasi, pasangan lagi, dan kemudian di kanannya berubah menjadi I20. Jadi cool, cool non-volatile untuk menggantikan sebuah OFFSET menggunakan fungsi INDEX. Baiklah, tip ini dan banyak lagi di buku ini, klik "i" di pojok kanan atas untuk membeli buku.
Baiklah rekap: OFFSET, mengagumkan, fungsi fleksibel, setelah Anda menguasai, Anda dapat melakukan segala macam hal. Arahkan ke sel kiri atas variabel, arahkan ke rentang yang memiliki bentuk variabel, tapi mudah berubah, sehingga mereka menghitung di setiap perhitungan. Excel biasanya melakukan penghitungan cerdas, atau menghitung ulang sel yang perlu dihitung, tetapi dengan OFFSET akan selalu dihitung. Daripada OFFSET, Anda dapat menggunakan INDEX: atau: INDEX atau bahkan INDEX: INDEX, kapan pun INDEX memiliki titik dua di sebelahnya, ini akan mengembalikan alamat sel, bukan nilai sel itu. Dan keuntungannya adalah INDEX tidak mudah berubah!
Oke, saya ingin mengucapkan terima kasih telah mampir, sampai jumpa di lain waktu untuk netcast lain dari!
Unduh berkas
Unduh file contoh di sini: Podcast2048.xlsm
