Formula Array Excel sangat hebat. Anda boleh mengganti ribuan formula dengan satu formula setelah anda mempelajari helah Ctrl + Shift + Enter. Hari ini, formula array tunggal melakukan 86,000 pengiraan.
Triskaidekaphobia adalah ketakutan pada hari Jumaat ke-13. Topik ini tidak akan menyembuhkan apa-apa, tetapi ia akan menunjukkan formula yang sangat menakjubkan yang menggantikan 110,268 formula. Dalam kehidupan sebenar, saya tidak perlu mengira berapa hari Jumaat ke-13 yang berlaku dalam hidup saya, tetapi kekuatan dan keindahan formula ini menggambarkan kehebatan Excel.
Katakan bahawa anda mempunyai rakan yang khurafat tentang hari Jumaat 13hb. Anda ingin menggambarkan berapa hari Jumaat ke-13 yang telah dilalui oleh rakan anda.
Sediakan lembaran kerja ringkas di bawah, dengan tarikh lahir di B1 dan =TODAY()di B2. Kemudian formula liar di B6 menilai setiap hari bahawa rakan anda masih hidup untuk mengetahui berapa hari itu hari Jumaat dan jatuh pada 13 bulan itu. Bagi saya, jumlahnya adalah 86. Tidak perlu takut.
By the way, 2/17/1965 sebenarnya adalah hari lahir saya. Tetapi saya tidak mahu anda menghantar kad ulang tahun kepada saya. Sebagai gantinya, untuk ulang tahun saya, saya ingin anda memberitahu saya bagaimana formula hebat itu berfungsi, satu langkah kecil pada satu masa.
Adakah anda pernah menggunakan fungsi LANGSUNG? Apabila anda meminta =INDIRECT("C3"), Excel akan pergi ke C3 dan mengembalikan apa sahaja yang ada di sel itu. Tetapi LANGSUNG lebih kuat apabila anda mengira rujukan sel semasa dalam perjalanan. Anda boleh menyiapkan roda hadiah di mana seseorang memilih huruf antara A dan C dan kemudian memilih nombor antara 1 dan 3. Apabila anda menggabungkan dua jawapan, anda akan mempunyai alamat sel, dan apa sahaja yang ada di alamat sel itu adalah hadiah . Nampaknya saya memenangi buku bergambar dan bukannya penginapan resort.
Adakah anda tahu bagaimana Excel menyimpan tarikh? Apabila Excel menunjukkan kepada anda 2/17/1965, ia menyimpan 23790 di dalam sel, kerana 2/17/1965, adalah hari ke-23790 abad ke-20. Inti formula adalah gabungan yang bergabung dengan tarikh mula dan titik dua dan tarikh akhir. Excel tidak menggunakan tarikh yang diformat. Sebaliknya, ia menggunakan nombor siri di belakang tabir. Jadi B3&":"&B4menjadi 23790: 42167. Percaya atau tidak, itu adalah rujukan sel yang sah. Sekiranya anda ingin menambahkan semua perkara dalam baris 3 hingga 5, anda boleh menggunakannya =SUM(3:5). Oleh itu, apabila anda meneruskan 23790: 42167 ke fungsi LANGSUNG, ia menunjukkan semua baris.
Perkara seterusnya yang dilakukan formula pembunuh adalah meminta ROW(23790:42167). Biasanya, anda melewati satu sel: =ROW(D17)ialah 17. Tetapi dalam kes ini, anda melewati ribuan sel. Apabila anda meminta ROW(23790:42167)dan menyelesaikan formula dengan Ctrl + Shift + Enter, Excel sebenarnya mengembalikan setiap nombor dari 23790, 23791, 23792, dan seterusnya hingga 42167.
Langkah ini adalah langkah yang luar biasa. Dalam langkah ini, kita pergi dari dua nombor dan "keluar" susunan 18378 nombor. Sekarang, kita mesti melakukan sesuatu dengan pelbagai jawapan. Sel B9 dari angka sebelumnya hanya mengira berapa banyak jawapan yang kita dapat, yang membosankan, tetapi ia membuktikan bahawa ROW(23790:42167)mengembalikan 18378 jawapan.
Mari kita permudahkan soalan asal secara dramatik supaya anda dapat melihat apa yang berlaku. Dalam kes ini kita akan menjumpai jumlah hari Jumaat pada bulan Julai 2015. Rumus yang ditunjukkan di bawah di B7 memberikan jawapan yang betul di B6.
Inti formula adalah ROW(INDIRECT(B3&":"&B4)). Ini akan mengembalikan 31 tarikh pada bulan Julai 2015. Tetapi formula kemudian meneruskan 31 tarikh tersebut ke WEEKDAY(,2)fungsi. Fungsi ini akan mengembalikan 1 untuk hari Isnin, 5 untuk hari Jumaat, dan seterusnya. Jadi persoalan besarnya ialah berapa banyak dari 31 tarikh tersebut yang mengembalikan 5 tarikh ketika diteruskan ke WEEKDAY(,2)fungsi tersebut.
Anda dapat melihat formula mengira dalam gerakan perlahan dengan menggunakan perintah Evaluate Formula pada tab Formula pada pita.
Ini setelah INDIRECT menukar tarikh menjadi rujukan berturut-turut.
Pada langkah seterusnya, Excel akan meneruskan 31 nombor ke fungsi WEEKDAY. Sekarang, dalam formula pembunuh, ia akan melepasi 18,378 nombor dan bukannya 31.
Berikut adalah hasil fungsi 31 MINGGU. Ingat, kami mahu mengira berapa 5.
Memeriksa untuk melihat apakah array sebelumnya adalah 5 mengembalikan sejumlah besar nilai Benar / Salah. Terdapat 5 nilai yang benar, satu untuk setiap hari Jumaat.
Saya tidak dapat menunjukkan kepada anda apa yang berlaku seterusnya, tetapi saya dapat menerangkannya. Excel tidak dapat MENGUMPULKAN sejumlah nilai Benar dan Salah. Ini bertentangan dengan peraturan. Tetapi jika anda mengalikan nilai Benar dan Salah dengan 1 atau jika anda menggunakan fungsi ganda-negatif atau N (), anda menukar nilai Benar menjadi 1 dan nilai Salah ke 0. Hantar nilai tersebut ke SUM atau SUMPRODUCT, dan anda akan dapatkan kiraan nilai Sebenar.
Berikut adalah contoh yang serupa untuk menghitung berapa bulan mempunyai hari 13 di dalamnya. Ini sepele untuk dipikirkan: Setiap bulan mempunyai tarikh 13, jadi jawapan untuk satu tahun lebih baik ialah 12. Excel melakukan matematik, menghasilkan 365 tarikh, menghantar semuanya ke fungsi DAY (), dan mengetahui berapa akhir naik pada 13 bulan. Jawapannya, seperti yang dijangkakan, adalah 12.
Angka seterusnya adalah lembaran kerja yang melakukan semua logik formula satu pembunuh yang ditunjukkan pada permulaan topik ini. Saya telah mencipta barisan untuk setiap hari yang saya hidup. Di lajur B, saya mendapat HARI () tarikh itu. Pada lajur C, saya mendapat tarikh WEEKDAY (). Pada lajur D, adakah B sama dengan 13? Di Lajur E, adakah C = 5? Saya kemudian mengalikan D * E untuk menukar True / False menjadi 1/0.
Saya telah menyembunyikan banyak baris, tetapi saya menunjukkan kepada anda tiga hari rawak di tengah-tengahnya pada hari Jumaat dan 13hb.
Jumlah dalam F18381 adalah 86 yang sama dengan formula asal saya. Tanda yang hebat. Tetapi lembaran kerja ini mempunyai 110.268 formula. Formula pembunuh asal saya melakukan semua logik 110,268 formula ini dalam satu formula.
Tunggu. Saya ingin menjelaskan. Tidak ada yang ajaib dalam formula asal yang menjadi pintar dan memendekkan logiknya. Formula asli itu benar-benar melakukan 110.268 langkah, mungkin lebih banyak lagi, kerana formula asal harus mengira array ROW () dua kali.
Cari cara untuk menggunakannya ROW(INDIRECT(Date:Date))dalam kehidupan nyata dan hantarkan kepada saya melalui e-mel (pub di dot com). Saya akan menghantar hadiah kepada 100 orang pertama untuk dijawab. Mungkin bukan penginapan resort. Kemungkinan besar Mac Besar. Tetapi begitulah caranya dengan hadiah. Banyak Mac Besar dan tidak banyak tempat penginapan.
Saya pertama kali melihat formula ini disiarkan di Papan Mesej pada tahun 2003 oleh Ekim. Kredit diberikan kepada Harlan Grove. Rumusnya juga muncul dalam buku Bob Umlas This Is Not Excel, It's Magic. Mike Delaney, Meni Porat, dan Tim Sheets semuanya mencadangkan helah tolak / tolak. SUMPRODUCT dicadangkan oleh Audrey Lynn dan Steven White. Terima kasih semua.
Tonton Video
- Terdapat kelas formula rahsia yang disebut Array Formula.
- Formula tatasusunan dapat melakukan ribuan pengiraan pertengahan.
- Mereka sering meminta anda menekan Ctrl + Shift + Enter, tetapi tidak selalu.
- Buku terbaik mengenai formula susunan ialah Mike Girvin Ctrl + Shift + Enter.
- LANGSUNG membolehkan anda menggunakan gabungan untuk membina sesuatu yang kelihatan seperti rujukan sel.
- Tarikh diformat dengan baik tetapi disimpan selama beberapa hari sejak 1 Januari 1900.
- Menyusun dua tarikh akan menunjukkan pelbagai baris di Excel.
- Meminta
ROW(INDIRECT(Date1:Date2))kehendak "keluar" pelbagai nombor berturut-turut - Menggunakan fungsi WEEKDAY untuk mengetahui sama ada tarikhnya adalah hari Jumaat.
- Berapa hari Jumaat yang berlaku pada bulan Julai ini?
- Untuk menonton formula mengira dalam gerakan perlahan, gunakan alat Evaluate Formula
- Berapa tahun ke-13 berlaku tahun ini?
- Berapa hari Jumaat yang ke-13 berlaku antara dua tarikh?
- Periksa setiap tarikh untuk melihat apakah MINGGU adalah hari Jumaat
- Periksa setiap tarikh untuk melihat sama ada HARI itu 13
- Gandakan hasil tersebut dengan menggunakan SUMPRODUCT
- Gunakan - untuk menukar True / False menjadi 1/0
Transkrip Video
Belajar Excel dari podcast, episod 2026 - Formula Kegemaran Saya di Semua Excel!
Podcast keseluruhan buku ini, klik “i” di sudut kanan atas untuk masuk ke senarai main!
Baiklah, ini adalah topik ke-30 dalam buku ini, kita seperti di akhir bahagian formula, atau di tengah-tengah bahagian formula, dan saya mengatakan bahawa saya mesti memasukkan formula kegemaran saya sepanjang masa. Ini hanyalah formula yang luar biasa, sama ada anda perlu mengira nombor pada hari Jumaat ke-13 atau tidak, ia membuka dunia ke dalam kawasan rahsia Excel yang disebut Array Formula! Masukkan tarikh mula, masukkan tarikh akhir, dan formula ini mengira jumlah hari Jumaat ke-13 yang berlaku di antara kedua tarikh tersebut. Ia sebenarnya melakukan lima pengiraan pada setiap hari antara kedua tarikh tersebut, pengiraan 91895 + SUM, pengiraan 91896 yang berlaku di dalam formula kecil ini, baiklah. Sekarang, pada akhir episod ini, anda akan sangat tertarik dengan formula pelbagai. Saya ingin menunjukkan,rakan saya Mike Girvin mempunyai buku terbaik mengenai formula array yang disebut "Ctrl + Shift" Enter ", ini adalah cetakan baru-baru ini dengan sampul biru, dulu kulit kuning dan hijau. Sekarang, mana sahaja yang anda dapat, adalah buku yang bagus, kandungan yang sama dalam buku kuning dan hijau.
Baiklah, mari kita mulakan bahagian dalam ini, dengan formula yang mungkin tidak pernah anda dengar disebut INDIRECT. LANGSUNG membolehkan kita menggabungkan, atau dalam beberapa cara membina sedikit teks yang kelihatan seperti rujukan sel. Baiklah, katakan kita mempunyai roda hadiah di sini, dan saya hanya meminta anda untuk memilih antara A, B, dan C. Baiklah, jadi anda memilih ini dan memilih C, dan kemudian pilih ini dan pilih 3, baiklah, dan hadiah anda adalah penginapan resort, kerana itulah yang disimpan di C3. Dan formula di sini digabungkan bersama-sama apa sahaja sejak C5 dan apa sahaja yang ada di C6 menggunakan & dan kemudian menyampaikannya ke LANGSUNG. Jadi = LANGSUNG (C5 & C6), dalam kes ini, adalah C3, yang harus menjadi rujukan yang seimbang. LANGSUNG mengatakan "Hei, kita akan pergi ke C3 dan mengembalikan jawapan dari itu, baiklah?" Kembali di Lotus 1-2-3 ini disebut fungsi @@,dalam Excel mereka menamakannya menjadi LANGSUNG. Baiklah, jadi anda ada di LANGSUNG, sekarang inilah perkara luar biasa yang berlaku di dalamnya.
Kami mempunyai dua tarikh, bagaimana Excel menyimpan tarikh, 17/2/1965, itu hanya format. Sekiranya kita pergi dan melihat jumlah sebenarnya di belakangnya, itu adalah 23790, yang bermaksud bahawa itu adalah 23790 hari sejak 1/1/1900, dan 42167 hari sejak 1/1/1980. Pada Mac, ia akan berlaku sejak 1/1/1904, jadi tarikhnya adalah sekitar 3000 pelepasan. Baiklah, begitulah cara Excel menyimpannya, ia menunjukkannya kepada kita, berkat format nombor ini sebagai tarikh, tetapi jika kita menggabungkan B3 dan a: dan B4, itu sebenarnya akan memberi kita nombor yang disimpan di belakang tabir. Jadi = B3 & ":" & B4, dan jika kita meneruskannya ke LANGSUNG, ia sebenarnya akan menunjuk ke semua baris dari 23790 hingga 42167.
Jadi ada B6 LANGSUNG, saya meminta ROW dari itu, itu akan memberi saya banyak jawapan, dan untuk mengetahui berapa banyak jawapan yang saya gunakan, baiklah. Dan untuk membuat ini berfungsi, jika saya hanya menekan Enter, ia tidak berfungsi, saya harus menahan Ctrl dan Shift dan tekan Enter, dan lihat, yang menambahkan () di sekitar formula di sini. Ini memberitahu Excel untuk masuk ke mod formula super, mod formula array, dan melakukan semua matematik untuk semua yang muncul dari array itu, 18378, baiklah. Jadi, itu adalah muslihat yang luar biasa, tidak langsung dari tarikh1: tarikh2, sampaikan ke fungsi ROW, dan berikut adalah contoh kecil.
Oleh itu, kami hanya ingin mengetahui berapa hari Jumaat yang berlaku pada bulan Julai ini, inilah tarikh mula, inilah tarikh akhir, dan untuk setiap baris tersebut saya akan meminta hari minggu. WEEKDAY memberitahu kami hari apa dalam seminggu, dan di sini, 2 argumen pada hari Jumaat akan mempunyai nilai 5. Oleh itu, saya sedang mencari jawapannya, dan kami akan memilih formula ini, pergi ke Formula, dan Evaluate Formula, dan Evaluate Formula adalah kaedah terbaik untuk melihat formula dikira dalam gerakan perlahan. Jadi ada B3, 1 Julai, dan anda melihat bahawa berubah menjadi nombor, dan kemudian kita bergabung dengan titik dua, betul, ada B4, yang akan berubah menjadi nombor, dan sekarang kita mendapat teks, 42186: 42216. Pada ketika ini, kami menyampaikannya kepada ROW, dan ungkapan kecil yang sederhana itu akan berubah menjadi 31 nilai di sini.
Sekarang, dalam contoh di mana saya mempunyai segalanya dari tahun 1965 hingga 2015, ia akan mengeluarkan 86000 nilai, betul, dan anda tidak mahu melakukan itu dan menilai formula, kerana akan menjadi tidak waras, baiklah? Tetapi anda dapat melihat apa yang berlaku di sini dengan 31, dan sekarang saya meneruskan 31 hari itu ke fungsi WEEKDAY, dan kami mendapat 3-4-5. Jadi 3 bermaksud hari itu adalah hari Rabu, dan kemudian 4 bermaksud itu adalah hari Khamis, dan kemudian 5 bermaksud hari itu adalah hari Jumaat. Ambil semua 31 nilai tersebut dan lihat apakah itu = 5 yang merupakan hari Jumaat, dan kita akan mendapat banyak PALSU dan BENAR, jadi hari Rabu, Khamis, Jumaat, dan kemudian 7 sel kemudian menjadi BENAR seterusnya, hebat!
Baiklah, jadi dalam kes ini kita mempunyai 5 BENAR dan 26 SALAH, Untuk menambahkannya, saya perlu menukar FALSE menjadi 0 dan BENAR menjadi 1, dan cara yang sangat biasa untuk melakukannya adalah dengan menggunakan - . Baiklah, sayangnya itu tidak menunjukkan jawapan di sana, di mana kita melihat sejumlah besar 1 dan 0, tetapi sebenarnya itulah yang berlaku, dan kemudian SUMPRODUCT menambahkannya dan membawa kita ke 5. Di sini, jika kita mahu tentukan berapa bulan 13 pada tahun ini, dari tarikh mula hingga tarikh akhir ini, proses yang sangat serupa. Walaupun kita akan mempunyai 365, sampaikan ke fungsi DAY, dan periksa untuk melihat berapa banyak 13, baiklah. Untuk contoh baris 92000, anda tahu, kita mendapat hari, kita mendapat hari kerja, memeriksa untuk melihat apakah, HARI = 13, memeriksa untuk melihat apakah WEEKDAY = SALAH, mengalikan * ini,dan hanya dalam kes di mana pada hari Jumaat yang ke-13, ia berakhir sebagai BENAR. SUMPRODUCT kemudian berkata "Tambahkan semua itu", dan begitulah cara kami mendapatkan 86, secara harfiah 91895 pengiraan + SUM, 91896 yang berlaku di dalam formula yang satu ini, sungguh hebat! Beli buku Mike, ini adalah buku yang luar biasa, ia akan membuka seluruh formula Excel kepada anda, dan sebenarnya, anda harus membeli kedua-dua buku tersebut. Beli buku saya, beli buku Mike, dan anda akan mempunyai koleksi hebat yang akan membawa anda sepanjang tahun ini.ini akan membuka seluruh formula Excel kepada anda, dan sebenarnya, anda harus membeli kedua-dua buku tersebut. Beli buku saya, beli buku Mike, dan anda akan mempunyai koleksi hebat yang akan membawa anda sepanjang tahun ini.ini akan membuka seluruh formula Excel kepada anda, dan sebenarnya, anda harus membeli kedua-dua buku tersebut. Beli buku saya, beli buku Mike, dan anda akan mempunyai koleksi hebat yang akan membawa anda sepanjang tahun ini.
Baiklah, jadi rekap: ada kelas rahsia formula yang disebut formula array, dan formula array boleh melakukan ribuan pengiraan pertengahan. Mereka biasanya meminta anda menekan Ctrl + Shift + Enter, tetapi tidak selalu, dan buku terbaik mengenai formula array adalah buku "Ctrl + Shift + Enter" Mike Girvin. Baiklah, jadi INDIRECT membolehkan anda menggunakan gabungan untuk membina sesuatu yang kelihatan seperti rujukan sel, dan kemudian INDIRECT pergi ke rujukan sel itu. Menggabungkan dua tarikh dengan titik dua akan menunjukkan pelbagai baris di Excel, dan kemudian meminta ROW of the INDIRECT of date1: date2 akan mengeluarkan sebilangan besar nombor berturut-turut, mungkin 31, mungkin 365, atau mungkin 85000. Periksa setiap tarikh untuk melihat sama ada MINGGU = Jumaat, periksa setiap hari untuk melihat apakah HARI = 13, kalikan dua tatasusunan BENAR dan SALAH menggunakan SUMPRODUCT. Dalam banyak kes, kita 'Akan digunakan - untuk menukar BENAR / SALAH menjadi 1 dan 0 untuk membolehkan SUMPRODUCT berfungsi. Ini formula yang luar biasa, saya tidak membuatnya, saya menjumpainya di papan mesej, semasa saya mengusahakannya, saya seperti "Wow, ini sangat hebat!"
Baiklah, saya ingin mengucapkan terima kasih kerana mampir, kami akan berjumpa anda di lain waktu untuk siaran lain
Muat turun fail
Muat turun fail contoh di sini: Podcast2026.xlsx
