mengubah tanggal format SAP ke format Excel

Pertanyaan:

"Hai XL-mania! Saya baru ikut XL-mania nih. Ternyata Excel seru juga ya. Saya ada problem. Mungkin gampang buat kalian, tapi susah buat saya. Saya sering download data dari SAP, format tanggalnya adalah teks yang dipisah titik. Nah saya pengen ubah ke format Excel. Selama ini saya manual. Ada ga sih rumus gampangnya?" - Olga yang lagi "belajar excel", Surabaya.

menjabarkan data sesuai jumlah yang ditentukan

Pertanyaan:

"Hai semuaaaaaa... Saya lagi belajar Excel nih. Kemaren liat-liat arsip XL-mania. Keren banget yang filteran data unik pake rumusnya. Penasaran aja, kalo misalnya kebalikannya bisa ga? Jadi ada data, di kanannya ada jumlahnya. Dari dua kolom ini bisa muncul satu kolom lagi, isinya daftar dari data pertama tadi, tapi udah sesuai jumlah di kanannya. Kalo bingung coba deh liat attachmentnya. Thanx yaaaaa..... Kalo ga bisa ga papa kok... Tapi kalo bisa... Ntar ada
hadiahnya!" - Mulan Mei, Medan.

membuat tabel data unik tanpa pivot table

Pertanyaan:

"Halo guru Excel! Saya sedang belajar Excel nih, tolong bantuan rumus ajaibnya yah. Saya ingin membuat tabel seperti yang biasa dibuat dengan pivot table. Tujuannya sih untuk memfilter data unik, kemudian menentukan jumlahnya. Kalau untuk jumlahnya saya sudah tahu caranya. Kalau di data saya ini pakai =COUNTIF($B$3:$B$15,D3). Masalahnya bagaimana cara membuat item unik di kolom D itu dengan formula? Pasti ada kan caranya? Tujuan saya pakai formula agar tabel itu selalu update, tidak seperti pivot table yang harus selalu di-refresh." - Sarah Azharia, Jakarta.

rata-rata berdasarkan kondisi tertentu

Pertanyaan:

"Halo dokter Excel! Saya lagi nih, jangan bosen ya. Kali ini saya ingin membuat rata-rata dari suatu tabel, dengan kondisi tertentu. Artinya jika dia tidak memenuhi kondisi itu, dia tidak menjadi data yang dirata-rata. Kasusnya seperti terlampir. Misalnya saya ingin membuat rata-rata dari "beruang", artinya yang dirata-rata hanya angka 20, 40, dan 60. Ada ga cara lain biar ga usah pakai AVERAGEIF? Saya nggak punya Excel 2007... hiksss...." - Sandra Dewiana, Jakarta.

Jawab:

Hai Sandra, sepertinya ini nyambung lagi dengan postingan saya tentang fungsi baru di Excel 2007. Sebenarnya ada cara membuat rata-rata dengan kondisi terntentu. Di cell G3 cukup tulis =AVERAGE(IF(B3:B9="beruang",C3:C9,FALSE)), akhiri dengan menekan tombol Ctrl+Shift+Enter sehingga keluar tanda {...} yang mengapit formula tersebut. Formulanya akan menjadi {=AVERAGE(IF(B3:B9="beruang",C3:C9,FALSE))}

Penjelasan:

1. 
   
   {...} adalah tanda bahwa formula tersebut adalah formula array
2. 
   
   ...=AVERAGE(... adalah fungsi untuk mencari rata-rata biasa
3. 
   
   ...(IF(B3:B9="beruang",C3:C9,FALSE))... adalah fungsi kondisi yang memberikan angka di range C3:C9 jika di B3:B9 berisi beruang, dan FALSE jika tidak. FALSE sendiri tidak dihitung sebagai angka jika dirata-rata. 
    
   
   
   

vlookup yang bisa nengok ke kiri

Pertanyaan:

"Hai XL-mania! Saya baru belajar Excel nih. Kemaren baru kenal sama yang namanya VLOOKUP nih. Tapi kok saya coba aplikasi di kasus di bawah ini nggak bisa ya? Apa yang salah? Saya maunya range F3:F5 itu bisa langsung keisi sesuai tabel B2:C12. Masalahnya, kolom hasilnya ada di sebelah kiri." - Soesilo Bambang, Jakarta.

Jawab:
Memang benar, VLOOKUP tidak dapat "menengok" ke kiri. Untuk mengambil data di sebelah kiri, kita dapat menggunakan kombinasi INDEX dan MATCH. Caranya dengan mengetikkan formula: =INDEX($B$3:$B$12,MATCH(E3,$C$3:$C$12,0)) di cell F3, kemudian mengcopynya ke range F4 dan F5.

Penjelasan:

1. 
   
   ...MATCH(E3,$C$3:$C$12,0)... berfungsi untuk mencari pada baris ke berapa di range C3 dan C12 ditemukan data sesuai E3. Angka 0 menunjukkan bahwa hasil pencarian harus sama persis dengan yang dicari.
2. 
   
   =INDEX($B$3:$B$12,...) berfungsi untuk memberikan nilai pada baris yang ditunjuk.

Selamat Belajar...

fungsi baru di excel 2007

Pertanyaan:
"Hai XL-mania! Saya tertarik untuk install Excel 2007 nih. Penasaran aja, sebenernya banyak ga sih fungsi tambahan di Excel 2007? Kalo ada daftar fungsinya mau dongggg..." - Rismawati Indah, Surabaya.

Jawab:
Memang benar ada fungsi tambahan di Microsoft Excel 2007, tapi kebanyakan adalah fungsi engineering. Fungsi seperti SUMIFS dan AVERAGEIFS sendiri tidak kompatibel dengan Excel versi lama. Fungsi seperti ini akan dibahas formula penggantinya di postingan selanjutnya. Berikut adalah daftarnya:

Cube: CUBEKPIMEMBER - Menunjukkan nama, properti, dan ukuran KPI [Key Performance Indicator] serta menampilkan nama dan properti KPI
Cube: CUBEMEMBER - Menampilkan anggota dalam hirarki cube.
Cube: CUBEMEMBERPROPERTY - Menampilkan nilai dari properti anggota cube.
Cube: CUBERANKEDMEMBER - Menampilkan urutan atau rangking member dalam suatu set.
Cube: CUBESET - Mendefinisikan sebuah set member.
Cube: CUBESETCOUNT - Menampilkan banyak item dalam suatu set.
Cube: CUBEVALUE - Menampilkan nilai agregat dalam suatu cube.
Engineering: BESSELI - Menampilkan modified Bessel function In(x)
Engineering: BESSELJ - Menampilkan the Bessel function Jn(x)
Engineering: BESSELK - Menampilkan the modified Bessel function Kn(x)
Engineering: BESSELY - Menampilkan the Bessel function Yn(x)
Engineering: BIN2DEC - Mengubah angka biner menjadi desimal.
Engineering: BIN2HEX - Mengubah angka biner menjadi heksadesimal.
Engineering: BIN2OCT - Mengubah angka biner menjadi oktal.
Engineering: COMPLEX - Mengubah bilangan real dan koefisien imajiner menjadi bilangan kompleks.
Engineering: CONVERT - Mengubah angka dari suatu sistem ukuran menjadi sistem yang lain.
Engineering: DEC2BIN - Mengubah angka desimal menjadi biner.
Engineering: DEC2HEX - Mengubah angka desimal menjadi heksadesimal.
Engineering: DEC2OCT - Mengubah angka desimal menjadi oktal.
Engineering: DELTA - Menguji apakah kedua nilai sama.
Engineering: ERF - Mengembalikan fungsi error.
Engineering: ERFC - Mengembalikan fungsi error komplementer.
Engineering: GESTEP - Menguji apakah sebuah angka lebih besar dari ambang batasannya.
Engineering: HEX2BIN - Mengubah angka heksadesimal menjadi biner.
Engineering: HEX2DEC - Mengubah angka heksadesimal menjadi desimal.
Engineering: HEX2OCT - Mengubah angka heksadesimal menjadi oktal.
Engineering: IMABS - Menghasilkan angka absolut dari sebuah bilangan kompleks.
Engineering: IMAGINARY - Menampilkan koefisien imajiner dari bilangan kompleks.
Engineering: IMARGUMENT - Mengembalikan argumen theta, sudut yang diekspresikan dalam radian.
Engineering: IMCONJUGATE - Menghasilkan konjugasi kompleks dari bilangan kompleks.
Engineering: IMCOS - Menghasilkan cosinus dari bilangan kompleks.
Engineering: IMDIV - Menampilkan hasil bagi dari dua buah bilangan kompleks.
Engineering: IMEXP - Menampilkan nilai eksponensial dari sebuah bilangan kompleks.
Engineering: IMLN - Menghasilkan logaritma natural dari sebuah bilangan kompleks.
Engineering: IMLOG10 - Menghasilkan logaritma 10 dari sebuah bilangan kompleks.
Engineering: IMLOG2 - Menghasilkan logaritma 2 dari sebuah bilangan kompleks.
Engineering: IMPOWER - Menghasilkan bilangan kompleks yang dijadikan pangkat dari integer.
Engineering: IMPRODUCT - Menampilkan hasil kali bilangan kompleks (dari 2 buah sampai 29 buah)
Engineering: IMREAL - Menampilkan koefisien riil dari bilangan kompleks.
Engineering: IMSIN - Menghasilkan sinus dari bilangan kompleks.
Engineering: IMSQRT - Menghasilkan akar kuadrat dari bilangan kompleks.
Engineering: IMSUB - Menampilkan selisih antara 2 bilangan kompleks.
Engineering: IMSUM - Menampilkan jumlah antara 2 bilangan kompleks.
Engineering: OCT2BIN - Mengubah angka oktal menjadi biner.
Engineering: OCT2DEC - Mengubah angka oktal menjadi desimal.
Engineering: OCT2HEX - Mengubah angka oktal menjadi heksadesimal.
External: EUROCONVERT - Mengubah sebuah angka ke standard Euro.
External: SQL.REQUEST - Melakukan koneksi dengan data eksternal dan melakukan query, menghasilkan sebuah array.
Math and trigonometry: SUMIFS - Menjumlahkan sesuai beberapa kriteria (di Excel versi lebih lama dapat digantikan dengan memodifikasi penggunaan dari SUMPRODUCT)
Statistical: AVERAGEIF - Menampilkan rata-rata jika memenuhi kriteria tertentu (di Excel versi lebih lama dapat digantikan dengan formula CSE kombinasi AVERAGE dan IF)
Statistical: AVERAGEIFS - Menampilkan rata-rata jika memenuhi beberapa kriteria tertentu.
Text: FINDB - Versi "double byte" dari FIND( )
Text: LEFTB - Versi "double byte" dari LEFT( )
Text: LENB - Versi "double byte" dari LEN( )
Text: MIDB - Versi "double byte" dari MID( )
Text: REPLACEB - Versi "double byte" dari REPLACE( )
Text: RIGHTB - Versi "double byte" dari RIGHT( )
Text: SEARCHB - Versi "double byte" dari SEARCH( )

menjumlah berdasar banyak kriteria tanpa SUMIF

Saya ingin menjumlahkan berdasarkan banyak kriteria. Kasusnya seperti terlampir. Ceritanya saya ingin menjumlahkan berdasarkan syarat1 dan syarat2. Saya ingin yang syarat1-nya "bbb" dan syarat2-nya "222". Jadi yang akan dijumlahkan adalah D4, D6, dan D9. Kalau datanya sedikit sih gampang. Tapi ini datanya banyak buaaannnggeettt. Kata temen saya kalau pakai Excel 2007, tinggal tulis di G6: =SUMIFS(D3:D9,B3:B9,G3,C3:C9,G4). Tapi saya nggak punya Excel 2007. Ada ga cara lain biar ga usah pakai SUMIF?

Jawab:
Hai Sandra, sepertinya ini nyambung dengan postingan saya tentang fungsi baru di Excel 2007. Sebenarnya ada kok cara lain untuk menjumlah berdasarkan beberapa kriteria / banyak kriteria. Misalnya pada posisi data dan syarat seperti kasus Sandra, di G6 saya tinggal tulis =SUMPRODUCT((B3:B9=G3)*(C3:C9=G4)*D3:D9).

Penjelasan:

1. =SUMPRODUCT(... berfungsi untuk mencari hasil kali dua buah array.
   ...(B3:B9=G3)... memberikan hasil TRUE jika hasilnya sama dengan G3, FALSE jika tidak sama.
2. ...(C3:C9=G4)... memberikan hasil TRUE jika hasilnya sama dengan G4, FALSE jika tidak sama.
3. ...D3:D9... adalah nilai yang akan dijumlahkan

Selamat Mencoba...