cara menampilkan isi sel berdasarkan kolom dan baris pada microsoft excel 2007

terima kasih kepada Wawan Jii sebagai pembaca setia dari info excel blog yang turut berpartisipasi dalam memberikan sebuah pertanyaan sekaligus menjadi bahasan selanjutnya dengan judul cara menampilkan isi sel berdasarkan kolom dan baris pada microsoft excel 2007 sangat berguna bagi perkembangan dan kemajuan info excel blog. saya sebagai admin akan semaksimal mungkin menjawab pertanyaan yang sudah diajukan sebelumnya.

pada intinya, formula yang digunakan untuk melakukan pembacaan isi sel yaitu dengan formula vlookup, fungsi match, dan fungsi index. menggunakan kombinasi ketiga tersebut lah akan tercapai solusi apa yang menjadi permasalahan tersebut.
 


dalam penyelesaian kasus cara menampilkan isi sel berdasarkan kolom dan baris pada microsoft excel 2007 dapat dikatakan sederhana, hanya butuh ketelitian dalam hal logika excel. baiklah, saya akan berusaha menjawab dengan menggunakan kombinas dari ketiga fungsi tersebut dan dengan 2 metode yaitu kombinasi vlookup dengan fungsi match dan fungsi index dengan fungsi match dalam penyelesaian kasus di atas.

1. fungsi vlookup untuk lebih detail mengenai penggunaan rumus fungsi vlookup.

=VLOOKUP(A10;A3:H6;MATCH(B10;B2:H2;0)+1;FALSE)

lebih ditekankan pada kolom besi agar logika dapat membaca isi sel yang diperintahkan sesuai baris dari concrete.

2. fungsi index, untuk lebih mendetail mengenai penggunaan rumus fungsi index.

=INDEX(B3:H6;MATCH(A9;A3:A6;0);MATCH(B9;B2:H2;0))

apabila ada kesulitan dalam melakukan penulisan rumus tersebut saya sertakan file mentahnya untuk menjadi bahan pembelajaran yah. file cara menampilkan isi sel berdasarkan kolom dan baris pada microsoft excel 2007.  sehingga hasilnya seperti dibawah ini.
 


semoga bermanfaat yah kawan.

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.

MEMBUAT DAFTAR HARGA BUKU

Selamat pagi, siang, sore, atau malam para pembaca setia Info Excel Blog. Pada kesempatan kali ini Info Excel akan posting artikel mengenai studi kasus yaitu “Membuat Daftar Harga Buku”. Beliau meminta dengan dibuatkan daftar harga buku dalam rangka bulan diskon. Posting tersebut berawal dari teman saya yang memiliki sebuah perpustakaan kecil namun dia mengalami kesulitan dalam penyusunan formula pada excel. Maksud dari Info Excel menuliskan artikel mengenai studi kasus adalah agar dalam pengaplikasian pada dunia nyata lebih memahami rumus fungsi dari excel.
 
Beberapa hal yang harus diperhatikan dan difungsikan sebagai dasar pengerjaan studi kasus MEMBUAT DAFTAR HARGA BUKU.
 

Susunan Daftar Harga Buku "Toko Surya"

Yang perlu diperhatikan ialah tabel utama nantinya akan diisi dengan membaca isi tabel pembantu. Oleh karena itu, anda dapat memanfaatkan kombinasi fungsi pembacaan tabel.

Dalam hal ini adalah VLOOKUP dengan berbagai fungsi STRING seperti LEFT, MID, dan RIGHT.

Untuk mengisi kolom penerbit ditentukan oleh kode pada digit ke-3 dan ke-4 (lihat tabel pembantu). Gunakan rumus fungsi MID untuk mengambil 2 karakter dari posisi ketukan tertentu pada kode.

Kolom jenis buku ditentukan oleh digit ke-1 dan ke-2 pada kode. Anga dapat menggunakan rumus fungsi LEFT, untuk mengambil 2 karakter dari kiri pada kode.

Kolom harga asal ditentukan oleh digit terakhir pada kode. Anga dapat menggunakan fungsi RIGHT.

Diskon hanya diberikan jika kode harganya A, B, atau C akan mendapat diskon sebesar 10% x Harga Buku.

Harga Baru = Harga Buku – Diskon.

 

Penyelesaian

Untuk menyelesaikan studi kasus diatas, langkah-langkahnya adalah sebagai berikut :
 
1. Silahkan download file mentah dari studi kasus MEMBUAT DAFTAR HARGA BUKU.xlsx
 
2. Pada kolom Penerbit, diisi dengan rumus fungsi pada sel D7=VLOOKUP(MID(C7;3;2);$C$24:$D$33;2;FALSE)
 
3. Pada kolom Jenis Buku, diisi dengan rumus fungsi pada sel E7=VLOOKUP(LEFT(C7;2);$C$36:$D$46;2)
 
4. Pada kolom HARGA AWAL, diisi dengan rumus fungsi pada sel F7=VLOOKUP(RIGHT(C7;1);$F$24:$G$33;2)
 
5. Pada kolom Diskon, diisi dengan rumus fungsi pada sel G7=IF(RIGHT(C7;1)="A";10%;IF(RIGHT(C7;1)="B";10%;IF(RIGHT(C7;1)="C";10%;0)))*F7
 
6. Untuk mengetahui Harga Baru, diisi dengan rumus fungsi pada sel H7=F7-G7.
 
7. Manfaatkan fasilitas copy-paste special-formula pada sel dibawahnya untuk mempercepat pengerjaan.
 
semua rumus fungsi excel pada studi kasus MEMBUAT DAFTAR HARGA BUKU sudah dicoba dan 100% working. apabila menemukan kesalahan atau bertanya mengenai sebuah studi kasus silahkan komentar yah. @infoexcel07.

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.

Mengenal Sel dan Range

Tak kenal maka tak sayang. Sebuah peribahasa yang merupakan motivasi bagi kita (aku dan kamu) dalam mengetahui sesuatu yang ingin dipelajari dan mendalami. Memahami sel (cell) dan segala macam yang berkaitan dengan sel merupakan keharusan untuk dikuasai oleh kamu agar pengembangan kedepannya lebih mudah.

Argumen tersebut menimbulkan pertanyaan dalam diri kamu “Kenapa aku harus menguasainya (dalam hal ini menuntut penguasaan sel pada excel)?”. Dengan ringan aku menjawab, secara mendasar sel merupakan lokasi pengolahan data yang telah kita masukkan (input) ke dalam Excel yang ditandai dengan alamat sel data tersebut berada.

Jadi, yang dimaksud sel adalah perpotongan antara kolom dan baris. Misalnya sel A1, berarti terdapat perpotongan pada kolom A baris ke 1, sel B5 berarti terdapat perpotongan pada B baris ke 5.

Range adalah kumpulan sel pada wilayah tertentu. Misalnya range A1:B5, berarti kumpulan sel yang dimulai dari A1, A2, A3, A4, A5, B1, B2, B3, B4, B5. Range F5:G7 berarti kumpulan sel yang dimulai dari F5, F6, F7, G5, G6, G7.

Salam Blogger