Rumus Array Excel sangat kuat. Anda dapat mengganti ribuan rumus dengan satu rumus setelah Anda mempelajari trik Ctrl + Shift + Enter. Saat ini, rumus array tunggal melakukan 86.000 kalkulasi.
Triskaidekaphobia adalah ketakutan pada hari Jumat tanggal 13. Topik ini tidak akan menyembuhkan apa pun, tetapi akan menunjukkan kepada Anda rumus yang benar-benar menakjubkan yang menggantikan 110.268 rumus. Dalam kehidupan nyata, saya tidak pernah menghitung berapa banyak hari Jumat tanggal 13 yang telah terjadi dalam hidup saya, tetapi kekuatan dan keindahan formula ini menggambarkan kekuatan Excel.
Katakanlah Anda memiliki teman yang percaya takhayul tentang hari Jumat tanggal 13. Anda ingin mengilustrasikan berapa banyak hari Jumat tanggal 13 yang telah dijalani teman Anda.
Siapkan lembar kerja sederhana di bawah ini, dengan tanggal lahir di B1 dan =TODAY()B2. Kemudian rumus liar di B6 mengevaluasi setiap hari bahwa teman Anda masih hidup untuk mencari tahu berapa hari itu adalah hari Jumat dan jatuh pada tanggal 13 setiap bulan. Bagi saya, jumlahnya 86. Tidak ada yang perlu ditakuti.
Ngomong-ngomong, 2/17/1965 benar-benar ulang tahun saya. Tapi saya tidak ingin Anda mengirimi saya kartu ulang tahun. Sebagai gantinya, untuk ulang tahun saya, saya ingin Anda mengizinkan saya menjelaskan cara kerja formula yang luar biasa itu, selangkah demi selangkah.
Apakah Anda pernah menggunakan fungsi INDIRECT? Saat Anda memintanya =INDIRECT("C3"), Excel akan pergi ke C3 dan mengembalikan apa pun yang ada di sel itu. Tapi INDIRECT lebih kuat saat Anda menghitung referensi sel dengan cepat. Anda dapat mengatur roda hadiah di mana seseorang mengambil huruf antara A dan C dan kemudian mengambil angka antara 1 dan 3. Saat Anda menggabungkan dua jawaban, Anda akan memiliki alamat sel, dan apa pun yang ada di alamat sel itu adalah hadiahnya . Sepertinya saya memenangkan photobook alih-alih menginap di resor.
Tahukah Anda bagaimana Excel menyimpan tanggal? Saat Excel memperlihatkan kepada Anda 2/17/1965, itu menyimpan 23790 dalam sel, karena 2/17/1965, adalah hari ke-23790 di abad ke-20. Inti dari rumus ini adalah rangkaian yang menggabungkan tanggal mulai, titik dua, dan tanggal akhir. Excel tidak menggunakan tanggal yang diformat. Sebaliknya, ia menggunakan nomor seri di belakang layar. Jadi B3&":"&B4menjadi 23790: 42167. Percaya atau tidak, itu adalah referensi sel yang valid. Jika Anda ingin menjumlahkan semuanya di baris 3 hingga 5, Anda dapat menggunakan =SUM(3:5). Jadi, saat Anda meneruskan 23790: 42167 ke fungsi INDIRECT, ini menunjuk ke semua baris.
Hal berikutnya yang dilakukan formula pembunuh adalah meminta ROW(23790:42167). Biasanya, Anda melewatkan satu sel: =ROW(D17)17. Tetapi dalam kasus ini, Anda melewati ribuan sel. Saat Anda meminta ROW(23790:42167)dan menyelesaikan rumus dengan Ctrl + Shift + Enter, Excel sebenarnya mengembalikan setiap angka dari 23790, 23791, 23792, dan seterusnya hingga 42167.
Langkah ini adalah langkah yang luar biasa. Pada langkah ini, kita mulai dari dua angka dan "memunculkan" array 18378 angka. Sekarang, kita harus melakukan sesuatu dengan rangkaian jawaban itu. Sel B9 dari gambar sebelumnya hanya menghitung berapa banyak jawaban yang kita dapatkan, yang membosankan, tetapi itu membuktikan bahwa ROW(23790:42167)menghasilkan 18378 jawaban.
Mari sederhanakan pertanyaan awal secara dramatis sehingga Anda dapat melihat apa yang terjadi. Dalam hal ini kita akan menemukan jumlah hari Jumat bulan Juli 2015. Rumus yang ditunjukkan di bawah ini di B7 memberikan jawaban yang benar di B6.
Inti dari rumus ini adalah ROW(INDIRECT(B3&":"&B4)). Ini akan mengembalikan 31 tanggal pada Juli 2015. Tapi rumus kemudian meneruskan 31 tanggal tersebut ke WEEKDAY(,2)fungsi. Fungsi ini akan mengembalikan 1 untuk Senin, 5 untuk Jumat, dan seterusnya. Jadi pertanyaan besarnya adalah berapa banyak dari 31 tanggal tersebut yang mengembalikan 5 ketika diteruskan ke WEEKDAY(,2)fungsi tersebut.
Anda dapat melihat rumus menghitung dalam gerakan lambat dengan menggunakan perintah Evaluasi Rumus pada tab Rumus di pita.
Ini terjadi setelah INDIRECT mengubah tanggal menjadi referensi baris.
Pada langkah berikutnya, Excel akan meneruskan 31 angka ke fungsi WEEKDAY. Sekarang, dalam rumus pembunuh, itu akan melewati 18.378 angka, bukan 31.
Berikut adalah hasil dari 31 fungsi WEEKDAY. Ingat, kami ingin menghitung berapa jumlahnya 5.
Memeriksa untuk melihat apakah larik sebelumnya adalah 5 mengembalikan sejumlah besar nilai Benar / Salah. Ada 5 nilai True, satu untuk setiap hari Jumat.
Saya tidak dapat menunjukkan kepada Anda apa yang terjadi selanjutnya, tetapi saya dapat menjelaskannya. Excel tidak dapat menjumlahkan sekumpulan nilai Benar dan Salah. Itu melanggar aturan. Tetapi jika Anda mengalikan nilai True dan False dengan 1 atau jika Anda menggunakan fungsi double-negative atau N (), Anda mengonversi nilai True menjadi 1 dan nilai False menjadi 0. Mengirimkannya ke SUM atau SUMPRODUCT, dan Anda akan dapatkan hitungan nilai True.
Berikut adalah contoh serupa untuk menghitung berapa bulan memiliki hari ke-13 di dalamnya. Hal sepele untuk dipikirkan: Setiap bulan memiliki tanggal 13, jadi jawaban untuk setahun penuh lebih baik menjadi 12. Excel menghitung, menghasilkan 365 tanggal, mengirimkan semuanya ke fungsi DAY (), dan mencari tahu berapa banyak akhir up pada tanggal 13 setiap bulan. Jawabannya, seperti yang diharapkan, adalah 12.
Gambar berikutnya adalah lembar kerja yang melakukan semua logika rumus pembunuh satu yang ditunjukkan di awal topik ini. Saya telah membuat pertengkaran untuk setiap hari selama saya hidup. Di kolom B, saya mendapatkan DAY () dari tanggal tersebut. Di kolom C, saya mendapatkan WEEKDAY () dari tanggal tersebut. Di kolom D, apakah B sama dengan 13? Di Kolom E, apakah C = 5? Saya kemudian mengalikan D * E untuk mengubah True / False menjadi 1/0.
Saya telah menyembunyikan banyak baris, tetapi saya menunjukkan tiga hari acak di tengah yang kebetulan jatuh pada hari Jumat dan tanggal 13.
Total di F18381 sama dengan 86 yang dikembalikan rumus asli saya. Pertanda bagus. Tetapi lembar kerja ini memiliki 110.268 rumus. Formula pembunuh asli saya melakukan semua logika dari 110.268 rumus ini dalam satu rumus.
Tunggu. Saya ingin menjelaskan. Tidak ada yang ajaib dalam formula asli yang menjadi pintar dan memperpendek logika. Rumus asli itu benar-benar melakukan 110.268 langkah, bahkan mungkin lebih, karena rumus asli harus menghitung larik ROW () dua kali.
Temukan cara untuk menggunakan ini ROW(INDIRECT(Date:Date))dalam kehidupan nyata dan kirimkan kepada saya melalui email (pub di dot com). Saya akan mengirimkan hadiah kepada 100 orang pertama untuk menjawab. Mungkin bukan resor menginap. Kemungkinan besar Big Mac. Tapi begitulah yang terjadi dengan hadiah. Banyak Big Mac dan tidak banyak resor tetap.
Saya pertama kali melihat formula ini diposting di Message Board pada tahun 2003 oleh Ekim. Penghargaan diberikan kepada Harlan Grove. Rumusnya juga muncul dalam buku Bob Umlas This Isn't Excel, It's Magic. Mike Delaney, Meni Porat, dan Tim Sheets semuanya menyarankan trik minus / minus. SUMPRODUCT disarankan oleh Audrey Lynn dan Steven White. Terima kasih semua.
Menonton video
- Ada kelas rumus rahasia yang disebut Rumus Array.
- Rumus array bisa melakukan ribuan perhitungan menengah.
- Mereka sering kali meminta Anda menekan Ctrl + Shift + Enter, tetapi tidak selalu.
- Buku terbaik tentang rumus array adalah Ctrl + Shift + Enter dari Mike Girvin.
- INDIRECT memungkinkan Anda menggunakan penggabungan untuk membuat sesuatu yang terlihat seperti referensi sel.
- Tanggal diformat dengan baik tetapi disimpan sebagai jumlah hari sejak 1 Januari 1900.
- Menggabungkan dua tanggal akan mengarah ke serangkaian baris di Excel.
- Meminta surat
ROW(INDIRECT(Date1:Date2))wasiat akan "memunculkan" array dari banyak angka yang berurutan - Menggunakan fungsi WEEKDAY untuk mengetahui apakah suatu tanggal adalah hari Jumat.
- Berapa hari Jumat terjadi di bulan Juli ini?
- Untuk melihat rumus menghitung dalam gerakan lambat, gunakan alat Evaluasi Rumus
- Berapa banyak 13 terjadi tahun ini?
- Berapa banyak hari Jumat tanggal 13 yang terjadi antara dua tanggal?
- Periksa setiap tanggal untuk melihat apakah WEEKDAY adalah hari Jumat
- Periksa setiap tanggal untuk melihat apakah DAY itu 13
- Kalikan hasil tersebut menggunakan SUMPRODUCT
- Gunakan - untuk mengubah True / False menjadi 1/0
Transkrip Video
Pelajari Excel dari podcast, episode 2026 - Formula Favorit Saya di Semua Excel!
Untuk membuat podcast untuk seluruh buku ini, klik "i" di pojok kanan atas untuk membuka playlist!
Baiklah, itu adalah topik ke-30 dalam buku ini, kami berada di akhir bagian formula, atau di tengah-tengah bagian formula, dan saya berkata bahwa saya harus memasukkan formula favorit saya sepanjang masa. Ini hanyalah rumus yang luar biasa, apakah Anda harus menghitung angka pada hari Jumat tanggal 13 atau tidak, ini membuka dunia ke dalam area rahasia Excel yang disebut Rumus Array! Masukkan tanggal mulai, masukkan tanggal akhir, dan rumus ini menghitung jumlah hari Jumat tanggal 13 yang terjadi di antara dua tanggal tersebut. Ini sebenarnya melakukan lima perhitungan pada setiap hari antara dua tanggal itu, perhitungan 91895 + SUM, 91896 perhitungan terjadi di dalam rumus kecil ini, oke. Sekarang, di akhir episode ini, Anda akan sangat tertarik dengan rumus array. Saya ingin menunjukkan,teman saya Mike Girvin memiliki buku terbaik tentang rumus array yang disebut "Ctrl + Shift" Enter ", ini adalah cetakan terbaru dengan sampul biru, dulu sampul kuning dan hijau. Sekarang, yang mana pun yang Anda dapatkan, adalah buku yang bagus, konten yang sama dalam warna kuning dan hijau.
Baiklah, mari kita mulai di dalam ini, dengan rumus yang mungkin belum pernah Anda dengar yang disebut TIDAK LANGSUNG. INDIRECT memungkinkan kita untuk menggabungkan, atau dengan cara tertentu membuat sedikit teks yang terlihat seperti referensi sel. Baiklah, katakanlah kita memiliki roda hadiah di sini, dan saya hanya meminta Anda untuk memilih antara A, B, dan C. Baiklah, jadi Anda pilih ini dan pilih C, lalu pilih ini dan pilih 3, oke, dan hadiah Anda adalah penginapan resor, karena itulah yang disimpan di C3. Dan rumus di sini digabungkan bersama apa pun sejak C5 dan apa pun di C6 menggunakan & dan kemudian meneruskannya ke TIDAK LANGSUNG. Jadi = TIDAK LANGSUNG (C5 & C6), dalam hal ini, adalah C3, itu harus menjadi referensi yang seimbang. INDIRECT mengatakan "Hei, kita akan pergi ke C3 dan mengembalikan jawaban dari itu, oke?" Kembali ke Lotus 1-2-3 ini disebut fungsi @@,di Excel mereka mengganti namanya menjadi TIDAK LANGSUNG. Baiklah, jadi Anda ada di TIDAK LANGSUNG, sekarang inilah hal menakjubkan yang terjadi di dalam sana.
Kami memiliki dua tanggal, bagaimana Excel menyimpan tanggal, 2/17/1965, itu benar-benar hanya pemformatan. Jika kita melihat angka sebenarnya dibelakangnya, itu adalah 23790, yang berarti 23790 hari sejak 1/1/1900, dan 42167 hari sejak 1/1/1980. Di Mac, itu akan terjadi sejak 1/1/1904, jadi tanggalnya akan mati sekitar 3000. Baiklah, begitulah cara Excel menyimpannya, itu menunjukkannya kepada kita, berkat format angka ini sebagai tanggal, tetapi jika kita menggabungkan B3 dan a: dan B4, itu sebenarnya akan memberi kita angka yang disimpan di belakang layar. Jadi = B3 & ”:” & B4, dan jika kita meneruskannya ke INDIRECT, itu sebenarnya akan mengarah ke semua baris dari 23790 sampai 42167.
Jadi ada TIDAK LANGSUNG dari B6, saya meminta BARIS itu, itu akan memberi saya banyak jawaban, dan untuk mencari tahu berapa banyak jawaban yang saya gunakan dihitung, oke. Dan untuk membuat ini berfungsi, jika saya hanya menekan Enter, itu tidak berhasil, saya harus menahan Ctrl dan Shift dan tekan Enter, dan lihat, itu menambahkan () di sekitar rumus di sini. Ini memberitahu Excel untuk masuk ke mode rumus super, mode rumus array, dan melakukan semua matematika untuk semua yang muncul dari array itu, 18378, baiklah. Jadi, itu adalah trik yang luar biasa, tidak langsung dari date1: date2, meneruskannya ke fungsi ROW, dan berikut adalah contoh kecilnya.
Jadi kami hanya ingin mencari tahu berapa hari Jumat yang terjadi di bulan Juli ini, ini tanggal mulai, ini tanggal berakhir, dan untuk setiap baris itu saya akan tanyakan untuk WEEKDAY. WEEKDAY memberi tahu kita hari apa ini, dan di sini, di 2 argumen Jumat akan memiliki nilai 5. Jadi, saya mencari jawabannya, dan kita akan memilih rumus ini, buka Rumus, dan Evaluasi Formula, dan Evaluasi Formula adalah cara yang bagus untuk melihat rumus dihitung dalam gerakan lambat. Jadi ada B3, tanggal 1 Juli, dan Anda lihat itu berubah menjadi angka, lalu kita gabungkan titik dua, benar, ada B4, itu akan berubah menjadi angka, dan sekarang kita mendapatkan teksnya, 42186: 42216. Pada titik ini, kita meneruskannya ke ROW, dan ekspresi kecil yang sederhana itu akan berubah menjadi 31 nilai di sini.
Sekarang, dalam contoh di mana saya memiliki segalanya dari 1965 hingga 2015, itu akan memunculkan 86000 nilai, benar, dan Anda tidak ingin melakukan itu dan mengevaluasi rumus, karena akan menjadi agak gila, oke? Tapi Anda bisa melihat apa yang terjadi di sini dengan 31, dan sekarang saya melewati 31 hari itu ke fungsi WEEKDAY, dan kita mendapatkan 3-4-5. Jadi 3 berarti hari Rabu, lalu 4 berarti hari Kamis, dan 5 berarti hari Jumat. Ambil semua 31 nilai itu dan lihat apakah mereka = 5 yang merupakan hari Jumat, dan kita akan mendapatkan sekelompok FALSE dan BENAR, jadi Rabu, Kamis, Jumat, dan kemudian 7 sel kemudian akan menjadi BENAR berikutnya, luar biasa!
Baiklah, jadi dalam hal ini kita memiliki 5 TRUE dan 26 FALSE, Untuk menambahkannya, saya perlu mengonversi FALSE menjadi 0 dan TRUE menjadi 1, dan cara yang paling umum untuk melakukannya adalah dengan menggunakan - . Baiklah, sayangnya itu tidak menunjukkan jawabannya di sana, di mana kita melihat sejumlah besar 1 dan 0, tapi itulah yang sebenarnya terjadi, lalu SUMPRODUCT menambahkannya dan membawa kita ke 5. Di sini, jika kita mau mencari tahu berapa banyak dari 13 bulan yang ada tahun ini, dari tanggal mulai hingga tanggal akhir ini, proses yang sangat mirip. Meskipun kita akan memiliki 365, teruskan itu ke fungsi DAY, dan periksa untuk melihat berapa banyak 13, oke. Untuk contoh baris 92000, Anda tahu, kita mendapatkan hari, kita mendapatkan hari kerja, memeriksa untuk melihat apakah, DAY = 13, memeriksa untuk melihat apakah WEEKDAY = FALSE, mengalikan ini * ini,dan hanya dalam kasus di mana hari Jumat tanggal 13 itu berakhir sebagai BENAR. SUMPRODUCT kemudian mengatakan "Tambahkan semua itu", dan itulah cara kami mendapatkan 86, secara harfiah 91895 perhitungan + SUM, 91896 terjadi di dalam rumus yang satu ini, sangat hebat! Belilah buku Mike, ini adalah buku yang luar biasa, ini akan membuka seluruh dunia rumus Excel untuk Anda, dan sebenarnya, Anda sebaiknya membeli kedua buku tersebut. Beli buku saya, beli buku Mike, dan Anda akan memiliki koleksi keren yang akan membantu Anda sepanjang sisa tahun ini.itu akan membuka seluruh dunia rumus Excel untuk Anda, dan sebenarnya, Anda hanya perlu membeli kedua buku tersebut. Beli buku saya, beli buku Mike, dan Anda akan memiliki koleksi keren yang akan membantu Anda sepanjang sisa tahun ini.itu akan membuka seluruh dunia rumus Excel untuk Anda, dan sebenarnya, Anda hanya perlu membeli kedua buku tersebut. Beli buku saya, beli buku Mike, dan Anda akan memiliki koleksi keren yang akan membantu Anda sepanjang sisa tahun ini.
Baiklah, rekap: ada kelas rumus rahasia yang disebut rumus array, dan rumus array bisa melakukan ribuan perhitungan menengah. Mereka biasanya meminta Anda untuk menekan Ctrl + Shift + Enter, tetapi tidak selalu, dan buku terbaik tentang rumus array adalah buku “Ctrl + Shift + Enter” dari Mike Girvin. Baiklah, jadi INDIRECT memungkinkan Anda menggunakan penggabungan untuk membuat sesuatu yang terlihat seperti referensi sel, lalu INDIRECT menuju ke referensi sel tersebut. Menggabungkan dua tanggal dengan titik dua akan mengarah ke rentang baris di Excel, dan kemudian menanyakan ROW dari INDIRECT of date1: date2 akan memunculkan deretan angka berurutan, mungkin 31, mungkin 365, atau mungkin 85000. Periksa setiap tanggal untuk melihat apakah WEEKDAY = Friday, periksa setiap hari untuk melihat apakah DAY = 13, kalikan kedua array TRUEs dan FALSEs menggunakan SUMPRODUCT. Dalam banyak kasus kami 'll gunakan - untuk mengubah TRUE / FALSE menjadi 1 dan 0 agar SUMPRODUCT berfungsi. Itu adalah formula yang luar biasa, saya tidak membuatnya, saya menemukannya di papan pesan, saat saya mengerjakannya, saya seperti "Wow, ini sangat keren!"
Baiklah, saya ingin mengucapkan terima kasih telah mampir, sampai jumpa di lain waktu untuk netcast lain dari!
Unduh berkas
Unduh file contoh di sini: Podcast2026.xlsx
