Fungsi Match

Fungsi Match digunakan pada microsoft excel pada umumnya untuk menampilkan suatu alamat sel terpilih atau ditetapkan sebelumnya yang terdapat dalam sekelompok data. Fungsi Match masih tergolong pada fungsi lookup dan referensi. 

Secara umum penulisan rumus fungsi Match adalah sebagai berikut :

=MATCH(lookup_value;lookup_array;match_type)

Keterangan :
- Lookup_value : nilai yang akan dicocokan (nilai kunci) yang merupakan bagian dari sekelompok data. 
- lookup_array : range atau tabel data yang digunakan untuk menempatkan nilai yang akan dicocokkan.
- match_type : berisi angka –1, 0, atau 1. Excel akan mencocokkan nilai dalam array. 
 

 
penjelasan singkatnya seperti ini.
jika data diurutkan menaik (ascending), dengan isian angka 1, excel akan menampilkan nilai posisi yang sama dengan nilai kunci. jika tidak, yang akan ditampilkan adalah posisi dibawah argumen kata kunci. jika urutan data menurun (descending), excel akan menampilkan komentar #N/A!. Jika typer diisi 0, excel akan menampilkan posisi nilai yang sama dengan nilai kunci. jika type diisi –1, excel akan menampilkan nilai yang tepat. namuin, jika tidak ditemukan akan ditampilkan nilai diatasnya (array dalam urutan menurun). atau jika array disusun dalam urutan menaik, akan dihasilkan #N/A!. jika argumen tidak ditulis, dianggap sebagai isian type 1.

 contoh penerapan kasus seperti diperlihatkan pada gambar diatas, posisi nomor baris dan kolom diganti dengan pilihan range C18:D18 dengan memilih wilayah dan bulan. pilihan tersebut selanjutnya akan dicari posisi nomor baris dan nomor kolom seperti diperlihatkan pada range C22:D22. berdasarkan data pada tabel atau array dan posisi baris serta kolom, pada alamat sel E18 diketahui hasil sesuai dengan pilihan. selamat mencoba.

FUNGSI INDEX

Fungsi Index pada microsoft excel pada umumnya digunakan sebagai penampil data dari sebuah sel yang ditentukan oleh baris dan kolom dalam suatu tabel yang telah diseleksi sebelumnya. Fungsi Index masih tergolong pada fungsi lookup dan referensi. Penggunaan yang lebih spesifik akan membantu menjadikan pekerjaan pembacaan sel lebih mudah.
Secara umum penulisan rumus fungsi index adalah sebagai berikut :

=INDEX(array;row_num;column_num)

Keterangan :
- Array : area yang telah diseleksi dan dijadikan sebagai isi tabel.
- Row_num : letak data yang berdasarkan pada nomor baris data yang akan ditampilkan setelah pencarian mengacu pada array.
- Column_row : letak data yang berdasarkan pada nomor kolom data yang akan ditampilkan setelah pencarian mengacu pada array.
 

contoh penerapan kasus seperti diperlihatkan pada gambar diatas, posisi nomor baris dan kolom diganti dengan pilihan range C18:D18 dengan memilih wilayah dan bulan. pilihan tersebut selanjutnya akan dicari posisi nomor baris dan nomor kolom seperti diperlihatkan pada range C22:D22. berdasarkan data pada tabel atau array dan posisi baris serta kolom, pada alamat sel E18 diketahui hasil sesuai dengan pilihan. selamat mencoba.

cara memindahkan sheet ke workbook lain

info excel blog yang sudah lama vakum dari perbloggingan kembali mengisi artikelnya dengan trik-trik sederhana yang membantu dalam memudahkan pengoperasian microsoft excel. kali ini, akan membagikan trik mengenai cara memindahkan sheet ke workbook lain.

cara memindahkan disini bukan hanya copy-paste saja, melainkan keseluruhan akan tersalin dengan rapi sesuai aslinya. mulai tertarik untuk menyimak? silahkan kawan..

1. buka dokumen excel yang akan dipindahkan ke dokumen excel lain (penggabungan).

2. klik kanan pada sheet yang dipindah, pilih Move or Copy...



3. pilih pada To Book :
- jika mau dibuat ke dokumen baru, pilih saja (new book).
- apabila mau dipindahkan, pilih nama file atau dokumen excel tersebut.



4. apabila akan melakukan penyalinan (copy), cukup beri tanda centang pada Create a copy.



5. klik ok dan lihat hasilnya.

anda tidak perlu capek-capek copy-paste secara manual. kalau tahu caranya pastinya semua akan berjalan sesuai rencana. semoga bermanfaat artikel mengenai cara memindahkan sheet ke workbook lain. cukup simpel bukan.

Membuat Daftar Sheet Dengan Formula

Terkadang kita bekerja dengan menggunakan banyak Sheet, dan untuk mempermudah kita membuka sheet tertentu, akan lebih baik jika kita mempunyai Sheet Menu, dimana di dalam sheet tersebut berisikan daftar Sheet yang ada.
Proses manual tetap saja bisa dilakukan, tetapi untuk orang "malas" sudah bukan jamannya lagi pakai cara manual...

Nah bagaimana cara membuatnya ?
Ada 2 cara, yang pertama menggunakan macro, bagi macro mania pasti mudah sekali ya, cukup looping sejumlah sheet, kemudian tuliskan pada sheet Menu.
Cara kedua yang akan saya bahas disini adalah menggunakan Formula.

Caranya adalah sebagai berikut :
1. Buatlah Name dengan nama DaftarSheet, scopenya Workbook dan Formulanya adalah :

=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")

Syntax GET.WORKBOOK bisa anda lihat disini


2. Kemudian pada Sheet Menu, silahkan anda tuliskan formula berikut ini :

=IFERROR(INDEX(DaftarSheet,Row()),"")

Karena Formula ini menggunakan fungsi Row(), maka untuk mendapatkan nama Sheet yang pertama, formula ini harus diletakkan pada Row 1

3. Jika ingin meletakkan pada baris yang diinginkan, Row(), bisa diganti dengan cara manual, atau merujuk pada Cell tertentu, seperti pada contoh :

=IFERROR(INDEX(DaftarSheet,A5),"")

 Copy formula ini sebanyak Sheet anda.

Dengan cara ini maka anda akan mendapatkan hasil pada Kolom B
Tetapi untuk kebutuhan Menu anda, maka hal ini kurang membantu...benar ?
Untuk menambahkan link ke sheet yang lain, maka tambahkan formula :

=HYPERLINK("#" & B5 & "!A1","Go to Sheet")

 Kemudian copy formula tersebut sebanyak baris nama Sheet anda.

Match With Multiple Criteria

Fungsi MATCH berfungsi untuk mendapatkan posisi suatu nilai dalam suatu array.

Syntax : MATCH(Lookup_Value, Array_Data, Match_Type)

Lookup_Value : Nilai yang akan dicari posisinya
Array_Data : Array (sekumpulan) data

http://excelformulaandmacro.blogspot.com/2010/10/fungsi-match.html

Bagaimana jika ada lebih dari 1 kondisi ?

Caranya adalah dengan menggunakan fungsi Match dengan kriteria lebih dari 1

Fungsi ini menggunakan array formula

http://excelformulaandmacro.blogspot.com/2010/10/penggunaan-array.html

Syntax : MATCH(1,(Array_Data1=Kondisi1)*(Array_Data2=Kondisi2),0)

Sebagai contoh :

Jangan lupa, karena fungsi ini menggunakan array formula, setelah menuliskan formula akhiri dengan menekan tombol Ctrl + Shift + Enter

Jika berhasil maka akan nampak tanda { diawal formula dan } di akhir formula

Membuat Laporan Penjualan Buku Pendidikan

@infoexcel07 akan membahas bermacam studi kasus untuk bidang bisnis yang sifatnya umum atau sering dijumpai dalam kehidupan sehari-hari agar mudah dalam pemahaman sehingga dalam kegiatan di lapangan akan mudah digunakan. pembahasan akan selalu dimulai dengan permasalahan, memberikan contoh layout output hasil yang diinginkan, kemudian memberikan langkah-langkah dalam pembuatannya agar dapat menyelesaikan studi kasus tersebut dibawahnya. untuk memudahkan sobat @infoexcel07 dalam mempraktikkan materi yang dibahas, akan dilengkapi dengan file dengan studi kasus tersebut.

sebagai contoh studi kasus untuk pertama akan membahas mengenai laporan penjualan buku pendidikan. ini adalah laporan yang sangat sederhana sekali. setiap orang dijamin bisa membuatnya. tapi, apakah rapi dan enak dilihatnya? tentunya jangan hanya selesai membuat laporan dan mengesampingkan kerapihan layout sehingga hanya melihat hasil akhir saja.

sebagai gambaran akhirnya telah captre dibawah ini untuk layout sederhananya.

cara singkatnya ialah sebagai berikut :

1. buka aplikasi Microsoft Excel 2007. maaf ya soalnya yang terinstal masih versi 2007 sih dan @infoexcel07 sudah familiar sekali dengan versi office 2007 tersebut.

2. sobat @infoexcel07 buat ya seperti layoutnya. klo bisa sih semirip mungkin atau download filenya.

3. apabila mengalami kesulitan, dapat melakukan langkah-langkah berikut ini ya.

4. untuk judul (title) tuliskan dengan menekan tombol caps lock "LAPORAN BUKU PENJUALAN PENDIDIKAN"

5. pada cell A3 ketikkan BULAN, dan B3 ketikkan JUMLAH. nah pada cel A5 ketikkan rumus =NOW() diakhiri dengan tombol ENter.

sehingga hasilnya seperti dibawah ini.

6. klik kanan pada cell A5, pilih format cell. buka pada tab number lalu pilih custom. pada type, ubahlah ke format MMMM. mengapa begitu? agar format yang terbaca hanya bulannya saja.

kok jadi februari doang sih? untuk mengaturnya gampang kok, tinggal cell A5 dijadikan sebagai target utama. untuk menjadikan bulan januari tinggal gunakan saja rumus =A5-30 yang dituliskan pada cell A4. sedangkan untuk menjadikan bulan maret dan seterusnya, dapat menggunakan rumus =A5+30. mengapa angka 30? karena asumsi di komputer per bulan itu ada 30 hari.

7. kemudian untuk cell B4 ketikkan angka atau jumlah pada bulan yang terkait. agar lebih enak terlihat, lakukan trik berikut ya. blok terlebih dahulu cell B4 sampai dengan cell B15, kemudian klik kanan pada area tersebut dan pilih comma.

8. pada cell A16 tuliskan TOTAL dan pada cell B16 tuliskan rumus =SUM(B4:B15) untuk eksekusi tekan enter yah sobat @infoexcel07. dapat juga menekan shortcut untuk sum yaitu dengan menekan tombol Alt+= (tombol Alt dengan tombol sama dengan secara bersamaan).

9. kemudian blok cell A3:B16 (dibaca A3 sampai dengan B16). lalu klik kanan format cell, masuk tab border dan pilih garis yang diinginkan. sebagai sampel saja dengan garis lurus dan seperti contoh dibawah ini yaa.

10. taraa,,akhirnya selesai juga dalam membuat laporan penjualan buku pendidikan secara sederhana.

bagaimana sobat @infoexcel? mudah bukan dalam Membuat Laporan Penjualan Buku Pendidikan? ikuti terus ya update dari blog info excel agar tidak ketinggalan informasi. @infoexcel07 senang sih berbagi dengan hal yang sederhana dan nantinya akan lebih kompleks ya. mohon dukungan serta kerjasamanya yah sobat @infoexcel07.

Cara Cepat Menghitung Bilangan Kelipatan pada Microsoft Excel

Lama sekali info-excel absen dari dunia blogging, kangen banget sih dengan melakukan share informasi walaupun hanya sebatas cara cepat menghitung bilangan kelipatan pada microsoft excel. Tetap berprinsip pada Tutorial dan Panduan merancang Formula atau Rumus Tips dan Trik dalam Belajar serta cara mengoptimalkan fungsi dasar Microsoft Excel 2007 untuk keperluan bisnis maupun untuk belajar saja. Yah, namanya juga ilmu. Apabila dirasa menguntungkan dan tidak ada pihak yang dirugikan mengapa tidak kita berbagi saja?

Pada kesempatan kali ini, info-excel akan membagikan cara sederhana yang mungkin dilupakan oleh para master dalam mengoperasikan microsoft excel. Ya memang hanya sebuah cara sederhana, dan ini sangatlah bermanfaat apabila dimaksimalkan yang tentunya terjadi efisiensi waktu, tenaga dan pikiran.

Langsung saja info-excel akan membagikan informasi mengenai cara cepat dan termudah dalam membuat deret angka dengan kelipatan yang sama atau berbeda, yang pada intinya angka tersebut berurutan yah.

Yang pertama ialah bilangan dengan kelipatan sama, misalkan info-excel memberikan contoh apabila bilangan diawali dengan angka 1,2,3,4 dst (bilangan kelipatan +1) atau juga dapat dengan contoh bilangan 1,4,7,10 dst (bilangan kelipatan +3) serta dapat juga digunakan untuk kelipatan bilangan 25,20,15,10,5,0 dst (bilangan kelipatan -5) yang biasanya terdapat pada pelajaran matematikan dan kadang dijumpai pada psikotest saat interview kerja.

Sepintas sih gampang yah apabila hitungan dalam jumlah kecil. Bagaimana jika bilangan tersebut dalam jumlah besar? Ribuan, ratusan ribu, bahkan bisa sampai jutaan? Bingung gimana dapatnya? Berikut info-excel bagi tutorial untuk cara cepat menghitung bilangan kelipatan pada microsoft excel.

1.    Pertama Tentunya harus ada software microsoft excel, dapat digunakan di microsoft excel versi berapapun.

2.    Ketikkan angka yang diinginkan, misalkan seperti gambar dibawah ini yah (buat contoh doang looh).

3.    Terus lakukan klik pada kotakan hitam kecil yang berada pada pojok kanan bawah dari cell yang telah diseleksi sampai kursor mouse berubah menjadi tanda +.
 

4.    Sim salabim. Angka yang diinginkan sudah muncul. Yeeaaayyy :D :D

nantinya, hasil akan terlihat seperti gambar dibawah ini

klik gambar untuk memperbesar yah :D

Mudah kan cara cepat menghitung bilangan kelipatan pada microsoft excel. Apabila masih merasa kesulitan jangan sungkan untukl bertanya yah kawan? Karena pepatah mengatakan “Malu bertanya sesat dijalan” ya kalau diartikan apabila malu bertanya tentang apapun yang belum diketahui dan bertanya kepada orang yang tepat tentunya akan mendapatkan informasi yang dibutuhkan. Sekian sedikit trik yang info-excel berikan dan terima kasih.

Semoga Tutorial, Panduan merancang Formula atau Rumus Tips dan Trik dalam Belajar serta cara mengoptimalkan fungsi dasar Microsoft Excel 2007 untuk bisnis bermanfaat bagi anda. Jika ada pertanyaan, silahkan sampaikan sebisa dan secepat mungkin nanti akan saya tanggapi.

Fungsi Paste Special pada Microsoft Excel

bagi yang sudah terbiasa menggunakan aplikasi kantor (office) jenis excel ini tentunya sudah tidak asing lagi dengan fungsi paste special pada microsoft excel. ada banyak pilihan untuk menaruh (paste) hasil salin (copy) dari suatu sel ke sel yang dituju. sedikit informasi untuk yang baru mengenal, menggunakan dan masih tergolong kategori pemula seperti saya ini ada baiknya untuk mengetahui apa saja fungsi paste special pada microsoft excel sehingga dapat memaksimalkan hasil paste sesuai dengan yang diharapkan.

dalam paste special anda akan diberikan beberapa pilihan, lebih jelasnya dapat melihat gambar dibawah ini.

memang, saya sendiripun pada saat jumpa pertama melihat banyak sekali pilihan yang disediakan oleh microsoft excel merasa bingung. namun, sedikit pengetahuan saya mengenai bahasa Inggris dapat menerjemahkan satu persatu dari kata-kata yang terdapat pada jendela paste special.

saya akan menjelaskan secara detail mengenai paste special pada microsoft excel sebagai berikut :

1 All => menyalin secara keseluruhan isi sel
2 formulas => paste rumus fungsi yang ada pada sel tertentu    

3  values => menaruh data pada sel tertentu, data tersebut berupa teks atau nilai sel     

4 formats => menyalin format sel yang diinginkan
5 comments => menduplikasi komentar yang ada pada suatu sel
6 validation => menyalin aturan validasi. Pada screenshot dibawah ini akan ditunjukkan hasil salinan formula, kemudian dilanjutkan penyalinan validasi dan hasil pengechekan
7 All using Source theme => Menyalin formula, value, format, comment, border, dan validation dengan menggunakan theme yang terdapat pada sumber (source).
8 All except borders => Menyalin formula, value, format, comment, dan validation. Namun tidak menyertakan garis batas (border).
9 Column widths => menyamakan lebar kolom sel yang diinginkan
10 Formulas and number formats => meniru rumus fungsi beserta data format nya
11 Values and number formats => Menyalin Nilai berupa teks dan Format sel

memaksimalkan fungsi paste special dari microsoft excel akan sangat membantu menyelesaikan pekerjaan. sedikit informasi mengenai fungsi paste special dari microsoft excel dari saya seorang yang masih sangat baru dalam microsoft excel. semoga Tutorial, Panduan merancang Formula atau Rumus Tips dan Trik dalam Belajar serta cara mengoptimalkan fungsi dasar Microsoft Excel 2007 untuk bisnis bermanfaat bagi anda. Jika ada pertanyaan, silahkan sampaikan sebisa dan secepat mungkin nanti akan saya tanggapi.

Fungsi Statistik (Statistical Function)

Fungsi Statistik pada Microsoft Excel 2007 merupakan kategori fungsi yang paling sering digunakan dalam penggunaan rumus fungsi dalam Microsoft Excel. sebenarnya macam dari fungsi statistik yang disediakan excel 2007 ini banyak. Saya hanya akan membahas yang paling sering digunakan sehingga dalam mempraktekkan fungsi statistik ini tidak mengalami kesulitan. nantinya, saya akan memberikan contoh sederhana yang mudah dipahami. 

berikut ini beberapa fungsi statistik yang saya jabarkan :

1.  Fungsi  SUM

fungsi untuk mengetahui nilai keseluruhan (jumlah / total) nilai data didalam suatu range tertentu. dengan memanfaatkan fungsi sum ini, kita tidak perlu repot melakukan perhitungan ulang dengan menggunakan tanda "+" di setiap nilai data.

penulisan rumus fungsi sum sebagai berikut :

=SUM(Range Data)

2.  Fungsi AVERAGE

fungsi yang dimanfaatkan untuk mengetahui nilai rata-rata dari suatu nilai data di area range tertentu. memanfaatkan fungsi average tentunya akan membuat pekerjaan anda lebih efektif dan efisien. karena tidak perlu repot menghitung nilai tengah dari suatu nilai dengan berapa kelompok.

penulisan rumus fungsi sum sebagai berikut :

=AVERAGE(Range Data)

3.  Fungsi MAX

fungsi max Digunakan dalam menentukan nilai data paling tinggi di range tertentu. memanfaatkan fungsi max membantu anda dalam menyeleksi suatu nilai dari terbesar sampai terkeci secara urut.

penulisan rumus fungsi sum sebagai berikut :

=MAX(Range Data)

4.  Fungsi  MIN

Digunakan untuk mencari nilai data yang terkecil dibandingkan nilai data yang lain didalam area range.

penulisan rumus fungsi sum sebagai berikut :

=MIN(Range Data)

berikut contoh tampilan dari fungsi sum, fungsi average, fungsi max, dan fungsi min.

berikut ini adalah penulisan rumus fungsi sum, average, max, dan min.

5.  Fungsi COUNTIF

fungsi Countif digunakan dalm perhitungan jumlah sel-sel yang didalamnya berisi data angka (numeric) dan huruf (Character) dengan kata kunci yang telah ditentukan sebelumnya.

penulisan rumus fungsi sum sebagai berikut :

=COUNTIF(range,kriteria)

Dari hasil rumus di atas dapat dijelaskan bahwa fungsi countif digunakan untuk menghitung angka 0 di area range A1:U1 kemudian 0 merupakan kriteria atau kata kunci yang di cari. Hasil dari pemanfaatan formula / rumus fungsi excel di atas adalah = 9, artinya bahwa terdapat 9 angka nol.

6.  Fungsi COUNTBLANK

Countblank sendiri dapat diartikan sebagai pemjumlahan kosong, maksudnya ialah akan menghitung jumlah sel kosong.

penulisan rumus fungsi sum sebagai berikut :

=COUNTBLANK(range).

Hasil dari penggunaan rumus fungsi countblank diperoleh hasil 7, yang berarti ada 7 sel kosong di area range A1:U3

Selamat mencoba.

Semoga Tutorial, Panduan merancang Formula atau Rumus Tips dan Trik dalam Belajar serta cara mengoptimalkan fungsi dasar Microsoft Excel 2007 untuk bisnis bermanfaat bagi anda. Jika ada pertanyaan, silahkan sampaikan sebisa dan secepat mungkin nanti akan saya tanggapi.

Formula atau Rumus pada Excel

Formula atau rumus adalah interuksi matematis dalam suatu sel/ range pada lembar kerja dengan operator aritmatik +, -, *, /, dan ^. Rumus bisa juga berarti alat untuk melakukan penghitungan terhadap nilai yang ada pada sel tertentu seperti penambahan, pengurangan, perkalian, pembagian, atau bahkan analisis data pada lembar kerja.

Formula yang terdiri dari beberapa macam operator aritmatik, akan diproses oleh excel sesuai hirarki atau urutan prioritas sebagai berikut :

Untuk perhitungan yang berada di dalam kurung, Excel akan memproses terlebih dahulu

-  Perpangkatan (^), perkalian (*), pembagian (/)

-  Penambahan (+), pengurangan (-)

 

Contoh : dalam gambar berikut ini pada sel F3 terdapat rumus =D5*E5. Artinya, isi sel D3 dikalikan dengan isi sel E3 dan hasilnya ditempatkan pada sel F3.

Prosedur penulisan rumus tersebut adalah sebagai berikut :

1.      Arahkan atau ketik mouse pada sel F3

2.      Ketik tanda = (sama dengan)

3.      Pilih atau klik sel D3

4.      Ketik tanda * (kali)

5.      Pilih atau klik E3

6.      Akhiri dengan tekan tombol ENTER.

Penulisan rumus dengan cara menunjuk atau mengacu ke alamat sel akan dapat memperkecil kemungkinan salah ketik. Jika isi sel tersebut digeser atau diperbarui isinya, rumus yang mengacu ke sel tersebut akan berubah secara otomatis.

Salam Blogger