Terimakasih Mr. Kid atas responsnya yang penuh intrik…hahaha Makin faham sekarang
Regards CW From: [email protected] [mailto:[email protected]] Sent: Sabtu, 05 Agustus 2017 02.53 To: BeExcel <[email protected]> Subject: Re: [belajar-excel] cara mengetahui formula harus dieksekusi dengan CSE Hai CW, Pertanyaannya susah-susah gampang... (bukan gampang-gampang susah loh ya) Susah berlipat njelasinnya, tapi gampang ngasih contohnya. Tentu sudah pernah baca atau tahu tentang array, formula, array formula, dan sebangsanya... jadi, gak perlu dijelasin lagi tentang literatur yang sudah banyak itu ya... Sekarang yang simpel-simpel saja. CTRL SHIFT ENTER (disingkat CSE) ditekan ketika selesai menulis formula menggantikan penekanan tombol ENTER (yaah... ini sih dah umum..) Parameter kalo formula harus pakai CSE : adalah kalau formula di-ENTER saja hasilnya error, dan di CSE keluar hasilnya. -> kaidah utama, main coba-coba Tapi ya gak seru kalau gak ada ancer-ancernya ya... Baiklah, ancer-ancernya sebelum menggunakan kaidah utama di atas adalah seperti cerita di baris selanjutnya ya... (Ingat... ancer-ancer sebelum pakai kaidah utama loh ya) Pakai data ini : Kalau begitu, pernah memperhatikan saat menulis suatu fungsi dalam formula, seperti gambar ini nih.. nah loh... jarang memperhatikan ya... Apa bedanya fungsi-fungsi yang dipakai dalam formula di sisi kiri dengan di sisi kanan ? Sum, IF, Row, dan sebagainya (ada banyak sekali), tidak punya inputan yang ada kata array-nya seperti yang dimiliki SumProduct, Index, vLookUp, dan lainnya (ada banyak). 1. Gak ada kata 'array' dalam inputan fungsi, tetapi diisi array hasil hitungan tertentu Cells D5 (hasil total Qty * Price) : Kalau diisi formula berbunyi : =Sum( D2:D4 ) -> hasilnya 140 maka D2:D4 tinggal ngambil, gak usah dihitung-hitung dulu sebelum di-Sum. jadi, =Sum( D2:D4 ) gak perlu CSE, cukup ENTER saja... Kalau di D5 ditulis formula : =Sum( B2:B4 * C2:C4 ) -> hasilnya juga inginnya 140, karena Qty (B2:B4) dikali Price (C2:C4) maka B2:B4 dan C2:C4 diambil, lalu masing-masing yang bersesuaian dikalikan dulu. Hasil perkalian masing-masing itulah nanti yang dihitung oleh si Sum. B2:B4 * C2:C4 inilah yang disebut proses array. Hasil proses array ini pastilah array data Bunyi B2:B4 * C2:C4 itu sama saja di 1 bijik cells D4 ada hitungan begini : B2 * C2 = 1 * 10 = 10 B3 * C3 = 2 * 20 = 40 B4 * C4 = 3 * 30 = 90 artinya ada hasil proses array berupa array data (kumpulan nilai-nilai) 10 , 40 , 90 Hasilnya kan lebih dari 1 bijik nilai... Nah si Sum, kalau lihat gambar di atas, tidak punya kata 'array' di-input-an-nya, adanya cuma bunyi number1, number blabla Berarti si Sum gak bisa terima alias ogah disuruh ngerjain ngitung B2:B4 * C2:C4 Kalau ogah, paksa si Sum supaya mau dengan CSE. Jadi, formula =Sum( B2:B4 * C2:C4 ) lalu tekan CSE akan menghasilkan 140, karena si Sum dipaksa supaya ngerjain B2:B4 * C2:C4 dulu, barulah di sum. Bagaimana dengan Row()... sama saja prinsipnya, malah si Row cuma bisa menerima range, gak bisa diisi angka Coba saja cells A5 diisi dengan formula =Row(1) kan protes berat si Excel. Lha ngawur kok ngisinya... Kalau dah protes berat gitu, mau di paksa pakai CSE juga kagak mau. Namanya udah ngambek ya gitu dah 2. ADA kata 'array', tapi pakai IF atau IFError atau beberapa fungsi semisal lainnya. Fungsi yang punya kata 'array' di-input-an-nya, seperti SumProduct saja deh yang cetho welo welo, gak perlu dipaksa-paksa untuk kerja alilas gak usah repot-repot tekan CSE, asal dia gak ketemu fungsi IF atau IFError. *** pssst... ini rahasia loh ya, yang ada kata 'array' itu, punya teman special yang disayang-sayang, yaitu si IF dan si IFError. Mungkin masih ada yang lainnya lagi kali ya, yang jarang ketemu sama si sumproduct, seperti isodd atau iseven gitu... Tapi yang jelas, ada cukup banyak, dan kaidah utama di atas sana perlu dipakai lebih lanjut. makanya, kalau mereka ketemu IF atau IFError, langsung deh jadi pemalesan... harus dipaksa kerja alilas butuh CSE. Cells D5, kalau diisi formula : =SumProduct( D2:D4 ) -> hasilnya 140, seperti =Sum( D2:D4 ) Cells D5, kalau diisi formula : =SumProduct( B2:B4 * C2:C4 ) lalu ENTER (gak usah CSE segala) -> hasilnya 140, karena si SumProduct punya kata 'array' di-input-an-nya padahal, B2:B4 * C2:C4 itu ya dihitung satu-satu dulu juga loh... rajin ya... huh.. gak kaya si Sum tuh, yang harus dipaksa (pakai CSE) *** jangan gitu sama si Sum... Biasanya, yang males itu justru lebih bisa menyelesaikan masalah lebih cepat, karena mereka akan cari cara paling efektif agar bisa bersegera bermalasan lagi Tapi, kalau cells D5 diisi formula : =SumProduct( IF( B2:B4 = B2:B4 , B2:B4 * C2:C4 ) ) lalu cuma tekan ENTER doang... hadeeh... ketemu dah hilang akalnya si SumProduct gara-gara ketemuan sama si IF... Padahal si IF juga aslinya cuma disuruh ngecek apa bener kolom B isinya sama dengan kolom B (lah pasti sama kan ya, gak usah mikir juga tahu kan), lalu kerjakan B2:B4 * C2:C4 Tahu gak kenapa kok fungsi yang ada kata 'array' di-input-an-nya langsung males ? gini... B2:B4 = B2:B4 itu harus dikerjakan satu per satu oleh si IF dulu, kalau hasilnya bener, baru ngerjain B2*B4 nah... si IF tuh males ngerjain barang begituan... karena si IF gak ada kata 'array' di-input-an-nya (lihat gambarnya lagi deh di atas sana) Supaya si IF mau ngerjain itu kan harus dipaksa ya... nah si SumProduct gak mau maksa si IF, 'cinta' gitu loh... Akhirnya, si SumProduct terima dimarahin user mbelain si IF daripada maksain si IF biar disayang user. Nah... kalau dah begini nih, main paksa saja sama si SumProduct pakai CSE =SumProduct( IF( B2:B4 = B2:B4 , B2:B4 * C2:C4 ) ) lalu tekan CSE -> hasilnya 140 tuh kan, akhirnya mau kerja beneran... soalnya, si SumProduct bilang sama si IF, IF aku cuma dipaksa user loh ini... aslinya aku gak mau maksain kamu... (weeek.. raja gombal) eh beneran loh ini... Coba deh di D5 diisi formula dengan SumProduct begini : =SumProduct( Trim( B2:B4 ) * Trim( C2:C4 ) ) lalu tekan ENTER saja (gak usah CSE) -> hasilnya 140 ya kan, pilih kasih kan si fungsi yang ada kata 'array' nya ini. *** Tapi ingat, ini sekadar ancer-ancer sebelum pakai kaidah utama di atas sana loh ya... supaya gak asal main coba ENTER lalu fail baru CSE.. 3. Si rese bernama vLookUp (lihat gambar), hLookUp, Small, Large, dan Match yang ngaku-ngaku pakai kata 'array' Fungsi-fungsi ini ngaku-ngaku pakai kata 'array' di-input-an-nya. Mungkin ada fungsi lainnya yang juga seperti ini, tetapi cukup jarang dipakai. Misal di cells A5 diisi formula =Max( D2:D4 ) hasilnya adalah 90 Ini setara dengan formula =Large( D2:D4 , 1 ) yang hasilnya adalah 90 Tapi kalau dah jadi begini formulanya : =Large( --D2:D4 , 1 ) lalu cuma di ENTER, baru muncul tabiat asli resenya si Large (saudara kecilnya yang bernama si Small juga gitu). Jadi, kalau minta tolong ke Large dan Small trus ada hitungannya dulu, pastikan tekan CSE, seperti begini : =Large( --D2:D4 , 1 ) lalu tekan CSE -> hasilnya 90 karena --D2:D4 itu butuh dihitung satu per satu dulu, yaitu -1 * -1 * D2:D4 -> seperti 1 * D2:D4 gitu loh Sayangnya, yang namanya Match itu, walaupun rese, kemampuannya sangat dibutuhkan dibanyak masalah. Jadi, kudu pinter-pinter ngakali resenya mereka, terutama si Match. Biasanya, kalau dah ketemu dengan mereka (yang rese begini), maka proses hitung arraynya diserahkan ke yang beneran pakai kata 'array'. Umumnya adalah si Index. *** Si Index ini memang baik hati dan tidak sombong (asal gak ketemu sama si IF, IFError, IsOdd, IsEven -- lihat nomor 2 ya) Contoh formula Large di atas, jadinya begini : =Large( Index( --D2:D4 , 0 ) , 1 ) -> hasilnya 90 [ Index( inputan , 0 ) artinya ngambil semua baris hasil inputan ] 4. Hasil formula pengen ditampilkan di banyak cells sekaligus (namanya blok array) Sebenarnya, ini untuk mengisi banyak cells sekaligus yang disebut sebagai array block (blok array). Maka PASTI harus pakai CSE. Contohnya begini : 1. blok cells E2 sampai E4 2. tulis bunyi formula ini (jangan lupa pakai = ya, kan formula) =123 3. tekan CSE maka E2:E4 adalah blok array berisi angka 123 Nah, hal seperti ini PASTI pakai CSE. Itu baru 1 bijik angka, yang terus dianggap sebagai array data, lalu ditulis ke banyak cells sekaligus. Tanda bahwa formula disitu adalah blok array adalah GAK BISA menghapus 1 cells saja. Contoh, coba deh hapus cells E2. Paling cuman dimarahin sama si Excel... Sekarang contoh pakai formula yang ngitung banyak nilai sekalian ya... blok F2 sampai F4, lalu tulis formula =D2:D4 lalu tekan CSE hmmm... sudah lihat hasilnya kan, semua isi cells D2:D4 ditulis masing-masing sesuai barisnya Coba lagi, blok G2 sampai G4 lalu tulis formula =B2:B4 * C2:C4 tekan CSE ooo... (o nya gak usah panjang-panjang bro or sis) Coba lagi, blok di H2 sampai M4 (yup, 3 baris x 6 kolom), lalu tulis formula =B2:B4 * C2:C4 lalu tekan CSE eh... sudah gak o lagi... Kalo gitu sekarang mulai seriusnya... Fungsi Row kan gak punya kata 'array' tuh, maka kalau mau bikin nomor urut pakai blok array (ingat, blok array PASTI butuh CSE) bisa dengan cara itu Misal : blok D7:D9 (yup, 3 baris x 1 kolom), lalu tulis =Row( A1:A3 ) dan tekan CSE Fungsi Index (ingat kan sama si Index yang baik hati dan tidak sombong), walau ada kata 'array', kalau mau nampilin semua hasilnya ke banyak cells (sebagai blok array), tetap pakai CSE. Contoh : Blok E7:E9, tulis formula =Index( B2:B4 * C2:C4 , 0 ) lalu tekan CSE Padahal, kalau mau melakukan Sum semua hasil Index, gak perelu CSE, seperti di cells B5 diisi formula =Sum( Index( B2:C2 * C2:C4 , 0 ) ) lalu tekan ENTER karena cuma mau nulis di sebuah cells, dan hasil akhir formula cuma 1 bijik nilai (hasil SUM), sedang yang bertugas ngerjain array perkaliannya adalah si Index. Dah ah, dah hampir masuk akhir 1/3 malam... yang ini terakhir buat mainan saja... Coba blok A11:D11 (yup, 1 baris x 4 kolom), lalu tulis formula =vLookUp( "B" , Trim( A2:D4 ) , Column( A1:D1 ) , 0 ) lalu tekan CSE Coba di cells A12 (1 cells) ditulis formula =vLookUp( "B" , Trim( A2:D4 ) , 4 , 0 ) lalu tekan ENTER Coba di cells A13 (1 cells) ditulis formula =vLookUp( "B" , Trim( A2:D4 ) , 4 , 0 ) lalu tekan CSE Silakan membunyikan pendapat kenapa dan mengapanya, dan persamaan beserta perbedaan dari 3 formula vLookUp tersebut. Mau tanya juga boleh... Dah dulu ya.. permisi... Regards, Kid 2017-08-04 8:03 GMT+07:00 'CepWahyu' [email protected] <mailto:[email protected]> [belajar-excel] <[email protected] <mailto:[email protected]> >: Dear Be-Excel, Mau tanya nih…apa sih parameternya kalo formula itu harus dieksekusi dengan CSE? Apakah setiap formula yg ada array-nya atau bagaimana? Regards CW
