Gunakan Formula CSE (Formula Array) untuk Lakukan Super-Perhitungan pada Data di Excel

Gunakan Ctrl + Shift + Enter (CSE formula) untuk data banyak dengan rumus di Excel Anda! Ya, memang benar ... ini kelas rahasia formula di Excel. Jika Anda tahu tiga kunci ajaib, Anda bisa mendapatkan formula Excel dengan array tunggal untuk menggantikan ribuan formula lainnya.

95% dari pengguna Excel tidak tahu tentang Rumus CSE. Ketika kebanyakan orang mendengar nama asli mereka, mereka berpikir "Itu kedengaram kurang berguna" dan tidak perlu repot-repot untuk belajar tentang CSE. Jika Anda berpikir SUMIF dan COUNTIF yang keren, Anda segera akan menemukan bahwa Ctrl + Shift + Enter (CSE) rumus akan menjalankan lingkaran di sekitar SUMIF dan COUNTIF. CSE formula memungkinkan Anda untuk benar-benar mengganti 1000 dari sel-sel formula dengan formula tunggal. Ya, sesama Guru Excel, ada sesuatu yang kuat ini akan tetapi kita tidak pernah menggunakannya.

Sebelum ada SUMIF, Anda bisa menggunakan rumus CSE untuk melakukan hal yang sama seperti SUMIF. Microsoft memberi kami SUMIF dan COUNTIF, tapi formula CSE tidak usang. Oh tidak, dan mereka dapat melakukan lebih banyak lagi! Bagaimana jika Anda ingin melakukan AverageIf? Bagaimana GrowthIf? AveDevIf? Bahkan MultiplyByPiAndTakeTheSquareRootIf. Apa saja yang dapat Anda bayangkan dapat dilakukan dengan salah satu formula CSE.

Berikut ini adalah mengapa saya pikir rumus CSE tidak pernah terungkap. Pertama, adalah nama asli yg membuat takut semua orang. Kedua, mereka memerlukan, sesuatu yg asing kontra-intuitif penanganan dalam Area Sel (Range) agar dapat melakukan proses. Setelah Anda mengetik dalam rumus CSE, Anda tidak bisa hanya tekan Enter. Anda tidak bisa hanya keluar dari sel dengan mengklik tombol panah. Bahkan jika Anda mendapatkan formula yang tepat dan menekan tombol enter, Excel memberikan Anda benar-benar non-user friendly "NILAI!" kesalahan. Ia tidak berkata, "Wow - yang indah Anda 99,1% dari jalan di sana,." Yang Anda mungkin berbeda.

 

Berikut adalah rahasia: Setelah Anda mengetik formula CSE, Anda harus menahan tombol Ctrl dan tombol Shift, dan kemudian tekan Enter. Insert memo dan tulis : Shift Ctrl Enter. Kemampuan Excel berguna dalam formula dan bisanya terlihat nanti ada "{}" didalam formula kita. Jika Anda tidak menulis itu, Anda akan terlupakannya dan pada saat terjadi sesuatu lagi!!!!!! rumus ada tidak jalan.

Lihat contoh ini: Katakanlah Anda ingin rata-rata hanya nilai-nilai dalam kolom C di mana kolom A berada di wilayah Timur.

  Rumus di sel A13 adalah contoh formula CSE. 

=AVERAGE(IF(A2:A10="East",C2:C10))

Plug ini dan Anda akan mendapatkan 7452.667, rata-rata hanya nilai-nilai Timur. Keren? Anda baru saja membuat versi Anda sendiri dari AverageIf fungsi Excel tidak ada. Ingat: Tekan Ctrl + Shift + Enter untuk memasukkan rumus.

Lihat contoh berikut di bawah ini.

Dalam contoh ini, kita membuat rumus CSE yang lebih umum. Daripada menetapkan bahwa kita sedang mencari "Timur," menunjukkan bahwa Anda bebas ingin nilai apa pun di A13. Rumusnya adalah sekarang 

=AVERAGE(IF($A$2:$A$10=A13,$C$2:$C$10)).

HEBAT-nya, Excel akan membiarkan Anda copy dan paste formula CSE tanpa penekanan tombol khusus. Saya menyalin C13 untuk C14: C15 dan saya sekarang memiliki rata-rata untuk semua daerah.

Contoh lainnya jika Anda ingin perkalian  Kuantitas dan Harga Satuan, tapi bukan harga di perlihatkan dan di lakukan perkalian. Tentu, itu cukup mudah untuk menambahkan kolom C dan mengisinya dengan =B2*A2 dan kemudian C beri nama total kolom, dengan CSE Anda tidak perlu itu!

Sekarang, rumus CSE kita adalah =SUM(A2:A10 * B2:B10). Diperlukan range sel A2:A10, lalu dikalikan oleh sel yang sesuai di B2:B10 dan total hasilnya. Setalah ketik rumus, Tahan Ctrl dan Shift sementara Anda tekan enter, dan Anda memiliki Anda menjawab dalam satu rumus bukan 10.

Apakah Anda melihat bagaimana hebatnya ini? Bahkan jika aku memiliki data 10.000 baris, Excel akan mengambil rumus satu, dan melakukan perkalian 10.000 baris dan memberikan hasilnya.

OK, di sini adalah aturan: Anda harus menekan Ctrl+Shift+Enter kapan saja Anda memasukkan atau mengedit formula ini. Kegagalan untuk melakukannya hasil kesalahan muncul #VALUE. Jika Anda memiliki beberapa kisaran, dan juga mepunyai bentuk umum yang sama. Dan jika Anda memiliki berbagai campuran dengan sel tunggal, sel-sel tunggal akan "direplikasi" dalam memori untuk mencocokkan bentuk jangkauan Anda.

Setelah Anda berhasil memasukkan rumus ini dan melihatnya dalam formula bar, Anda akan memiliki kurung kurawal sekitar formula. Dan Anda tidak pernah memasukkan kurung kurawal sendiri. Menekan Ctrl+Shift+Enter yg membuat kurung kurawal itu di sana.

Apakah Anda harus mengambil BASIC di kelas 11 dengan Rusdi? Atau, lebih buruk lagi, apakah Anda mendapatkan "D" di aljabar linear dengan Mrs Rusdi di perguruan tinggi? Jika demikian, Anda berada dalam keadaan yang baik dan akan gemetar setiap kali Anda mendengar kata "array." -Orang akan berteriak ke arah lain jika setiap kali ada yang mengatakan array. Saya memahami mereka, tapi ini adalah Excel, saya tidak perlu array sini!. Rahasia ketakuatanya adalah bahwa Excel dan Microsoft menyebutnya "formula array" formula. Hentikan - tidak perlu melarikan diri. Hal ini OK, benar.  

Rusdi telah berganti nama mereka formula CSE karena kedengarannya kurang menakutkan, dan karena nama membantu Anda ingat bagaimana untuk memasukkan mereka - Shift Ctrl Enter. Aku hanya menyebutkan nama asli di sini dalam kasus Anda mengalami seseorang dari Microsoft yang tidak pernah ada Mrs Rusdi untuk aljabar linear, atau, dalam kasus Anda memutuskan untuk membaca dengan teliti file Help.  

Jika Anda mencari untuk formula ini kita ketik "formula array," tetapi antara kita teman-teman, mari kita menyebut mereka CSE - OK?