Kamus formula excel

Excel Function Dictionary V4.0

Excel Function Dictionary (xlfdic) berisi lebih dari 150 contoh penggunaan berbagai fungsi formula excel, file buatan Peter Noneley ini gratis alias freeware. Sejak 1998 s.d.sekarang, xlfdic sudah sampai pada versi 4.0. Format file sempat berganti dari .xls ke .exe (memanfaatkan XLtoEXE converter), dan sekarang kembali lagi ke format .xls.

Bagi pemula yang baru belajar formula excel dan bosen dengan penjelasan formula di excel help, kamus ini dapat sebagai alternatif panduan.

Ubah menjadi kapital semua

Merubah huruf menjadi huruf kapital semuanya pada suatu cell dapat dilakukan dengan formula UPPER.
Cuma jadi ribet bila yang ingin dirubah adalah data pada seluruh sheet yang sedang aktif.
VBA code di bawah ini digunakan untuk mengubah ke dalam huruf kapital atas semua data pada sheet yang aktif.
Formula tidak ikut terpengaruh.


=======

Sub kapital()
For Each cell In ActiveSheet.UsedRange
cell.Formula = UCase(cell.Formula)
Next cell
End Sub

=======

Untuk merubah ke huruf kecil semua (Lower Case), ubah UCase menjadi LCase

Transpose Tabel

Mencari solusi dalam hal merubah posisi tabel matrik dari yang sebelumnya merupakan data kolom menjadi data baris… (transpose tabel), cara VBA berikut ini dapat dijadikan sebagai solusi alternatif.
=======
Sub test()
With Selection.CurrentRegion
.Offset(.Rows.Count + 4).Resize(.Columns.Count, .Rows.Count) = Application.WorksheetFunction.Transpose(.Formula)
End With
End Sub
=======
Pilih range dari tabel matrik yang akan di transpose, dan jalankan vba code tersebut.
Kalo bisa tanpa ke makro kenapa gak…
Gunakan teknik copy paste, dengan pilihan paste special, dan jangan lupa gunakan pilihan “transpose”.
>Masih ada cara alternatif lainnya…
solusi transpose menggunakan VBA Code dan copy paste special sudah dijelaskan, cara berikutnya adalah menggunakan formula.
Sebagai contoh, pada cell A7 tulis formula berikut ini: “=Transpose(A1:D5)”
Range A1:D5 merupakan daerah tabel yang akan ditransposisikan.
Sorot range A7:E11 > klik F2 > kemudian lakukan eksekusi dengan menggunakan Ctrl + Shift + Enter
apabila cara di atas telah dilakukan sesuai dengan petunjuk, maka seharusnya hasil dari array formula menunjukkan tabel yang sudah tertranposisikan kolom dan barisnya.

74 Sumber Pengetahuan MS Excel Serba Guna

Apakah anda sering menggunakan Excel? Atau anda baru ingin mulai mempelajarinya? 74 sumber dibawah ini berisi berbagai tips dan tutorial yang pasti dapat membantu anda dalam menggunakan dan mendalami MS Excel.
Video dan Tutorial Online
1. Online introduction to Excel: Membantu anda memplejari Excel secara bertahap, sangat cocok untuk pemula.
2. Data Pig Technologies: Tersedia berbagai koleksi video yang menjelaskan jampir semua aspek dari Excel. Dari dasar-dasar Excel sampai VBA programming dan sebagian besar videonya gratis.
3. Online Charts Tutorial: Disini anda bisa mendapatkan tutorial online dari Jon Peltier yang ahli dalam membuat chart.
4. Basic Formulas Guide: Tutorial ini akan membantu anda menguasai formula Excel dengan cepat.
5. Common uses for Formulas: Sesuai namanya, contoh-contoh disini akan membantu anda memahami penggunaan formula.
6. An introduction to Pivot Tables: Menjelaskan kegunaan dari Pivot Table yaitu sebuah tool yang sebenarnya bagus tetapi jarang digunakan karena terlihat rumit.
7. Creating a Pivot Table: Sebuah video selama 7 menit yang memperlihatkan cara membuat dan bekerja dengan Pivot Tables.
8. Pivot Tables in Excel 2007: Pengenalan untuk Pivot Tables di Excel 2007 yang lebih mudah digunakan.
9. Practicing Pivot Tables: Tutorial bertahap dari Microsoft ini akan mengasah keahlian anda dalam Pivot Tables.
10. Microsoft Excel help / 2007: Microsoft Excel Help akan selalu menjadi sumber yang bagus untuk menjawab pertanyaan-pertanyaan anda.
Buku
General:
11. Excel Bible 2003 / versi 2007: Ditulis oleh John Walkenbach, seorang ahli Excel yang terkenal. Menjelaskan semua hal dari fungsi dan formula dasar sampai validasi data dan excel programming.
12. Excel Charts: Buku ini mudah dimengerti untuk mempelajari chart. Sumber yang tepat untuk pemula maupun yang sudah berpengalaman.
13. Excel Formulas: Ditulis juga oleh John Walkenbach. Buku ini berisi semua hal tentang formula.
14. Pivot Tables and data analysis / versi 2007: Ditulis tentang Bill Jelen dan Michael Alexander yang menjelaskan tentang Pivot Tables.
15. Excel Programming: Buku terbaik dalam programming Excel sampai saat ini. Satu kekurangannya, anda harus memiliki sedikit pengetahuan dasar programming untuk benar-benar mempelajarinya.
16. Report programming with Excel: Jika anda akan membuat sebuah sistem laporan dengan Excel, maka ini adalah buku yang tepat untuk anda. Buku ini mengajarkan bagaimana cara menggunakan Excel untuk membuat sebuah analisa data atau laporan dan bagaimana cara bekerja dengan database SQL.
Tips dan studi kasus
17. Excel case studies: Bukan ditujukan untuk pemula, berisi tentang saran-saran untuk menggunakan Excel di dunia nyata.
18. Excel Tips: Buku yang berisi berbagai tips dan sangat disarankan untuk dibaca.
19. Some more Excel Tips: Dapat menjadi jalan keluar ketika anda menemukan jalan buntu ketika sedang menggunakan Excel.
20. This isn’t Excel it’s Magic: Ditulis oleh Bob Umlas yang mungkin adalah salah satu ahli formula terbaik di dunia. Anda akan terkejut dengan apa yang sebenarnya bisa anda lakukan dengan formula.
Buku khusus
21. Principals of finance with Excel: Buku ini akan membantu anda mengerti bagaimana mengaplikasikan Excel dalam lingkungan finansial.
22. Statistical Analysis with Excel: Dengan bahasa Inggris yang mudah dan contoh dari dunia nyata, buku ini berisi informasi yang dapat membantu anda dalam analisa statistik.
23. Business Analysis with Excel: Buku ini menjelaskan berbagai hal dalam menjalan bisnis seperti inventarisasi, perkiraan penjualan, pajak dan lainnya, dan bagaimana cara melakukan semuanya dengan Excel.
24. Sales Forecasting with Excel: Buku ini menunjukkan anda bagaimana caranya anda menggunakan Excel untuk memprediksi tren dan penjualan berdasarkan angka-angka dengan menggunakan data dari masa lalu.
25. Excel for Chemists: Sebagian besar buku ini berisi introduksi umum untuk Excel, dan penuh dengan contoh-contoh yang berhubungan dengan kimia.
Forum, News Gorup dan Mailing List
26. Mailing List: Excel-G mailing list ini dimonitor oleh ahli-ahli Excel terbaik di dunia. Jika anda mengirimkan masalah yang cukup menarik, maka ahli-ahli Excel ini akan berlomba-lomba untuk menjawabnya dan memberikan solusi yang paling elegan.
27. Mr Excel Message Boards: Forum yang sangat ramah dan biasanya angota-anggotanya akan memberikan jawaban dalam 3-5 jam. Sekelompok ahli Microsoft memonitor forum ini.
28. Excel News Group: Jika anda lebih menyukai group USENET maka anda akan menyukai group Microsoft Excel. Pertanyaan akan dijawab dalam 12 jam.
29. ExcelForum.com: ExcelForum.com menyediakan suatu web interface untuk Excel news group. Jika anda tidak inggin diganggu dengan interface USENET, maka site ini adalah alternatif yang sangat berguna.
Ahli-Ahli Excel
30. Jon Peltier: jika anda memiliki masalah dengan chart, mungkin anda bisa bekerjasama dengan Jon, yang memiliki 20 tahun pengalaman dengan Excel dan gelar PhD dari MIT.
31. Chip Pearson: Pearson adalah ahli Excel yang terkenal dan salah satu yang terbaik, walaupun biaya untuk menyewa keahliannya tidaklah murah.
32. Mr. Excel Consulting Services: Mereka mungkin adalah konsultan Excel terbesar di dunia yang terdiri dari ahli-ahli Excel terbaik.
33. JMT Consulting: Jasa konsultan dari dua ahli Excel terkenal yaitu Masaru Kaii dan Andrew Engwirda.
Site Tips dan Blog
34. Daily Dose of Excel: Sebuah blog yang dikelola oleh Dick Kusleika dan ditulis oleh banyak ahli Excel. Diupdate beberapa kali dalam seminggu. Sangat disarankan bagi para pengguna Excel.
35. ExcelTip.com: Setelah bertahun-tahun, ExcelTips telah mengumpulkan ratusan tips dan solusi dari berbagai masalah. Anda juga bisa mendaftarkan diri untuk mendapatkan newsletter tips. Dikelola oleh Joseph Rubin.
36. Official Microsoft 2007 Blog: Blog Excel yang resmi, ditulis oleh berbagai programmer dan product manager Excel. Informasi dan artikel di blog ini bervariasi, dari “useful to everyone” sampai “only for hardcore excel services programmers.
37. Vital News Excel Tips: Serupa dengan ExcelTip, site ini berisi ratusan tips yang disortir menjadi berbagai kategori dan juga menawarkan newsletter mingguan.
38. Chip Pearson Newsletter: Jasa berita dari Chip Pearson. Cocok bagi anda yang benar-benar ingin mendalami Excel.
39. Excel User: Dibuat dan dikelola oleh Charles Kyd. Memiliki banyak artikel yang sangat bagus dan ada bagian “Visitor Section”.
40. Andrew’s Excel Tips: Andrew Engwirda memiliki banyak pengalaman dalam programming dan Excel dan ia juga menulis salah satu blog Excel terbaik.
41. Codswallop: Walaupun bukan blog yang berpusat di Excel, tetapi pengelolanya berencana untuk segera menawarkan banyak artikel tentang Excel.
42. Smurf On Spreadsheets: Simon Murphy adalah seorang ahli Excel Programming. Jika anda tertarik dengan Excel Programming, maka ini adalah blog yang cocok untuk anda.
43. The Ken Puls Blog: Blog ini tidak berfokus pada Excel dan tidak terlalu sering diupdate, tetapi jika Ken menulis sesuatu tentang Excel maka artikelnya akan sangat menarik.
44. XL Dennis: Dennis Wallentin menulis tentang pengembangan solusi yang berpusat pada Excel dengan framework .Net.
Template Excel
Template bisnis
45. Budget Templates: Template untuk budget pribadi, pernikahan, rumah tangga atau apapun itu.
46. Balance Sheets: Anda bisa menemukan berbagai balance sheet disini.
47. Expense Reports: Gunakan template ini untuk mencata dan mengatur pengeluaran.
48. Business Forms: Disini anda bisa menemukan berbagai form yang untuk berbagai tujuan.
49. Inventory Templates: Atur dan catat inventaris anda dengan template yang tersedia disini.
50. Invoices, Work Orders, Packing Slips: Anda bisa menemukan berbagai template invoice dan work order disini.
51. Purchase Orders: Dapat membantu anda dalam membeli dan mengantarkan barang.
52. Receipts: Tersedia berbagai template untuk tanda terima.
53. Time Sheets: Gunakan template ini untuk mencatat waktu kerja para karyawan.
54. Reports: Template untuk berbagai laporan finansial dan manajemen.
Template lainnya
55. List: Template untuk daftar belanja, daftar nomor telepon, daftar hadiah dan lainnya.
56. Planning Templates: Template untuk rencana pribadi dan rencana bisnis.
57. Schedules: Template untuk jadwal pribadi, bisnis ataujadwal lainnya.
58. Vertex42 Excel templates: Tersedia berbagai template Excel, beberapa bahkan tersedia user manualnya.
59. OZGrid Excel Templates Page: Koleksi template yang banyak dan terkenal.
Tool Excel
60. Asap Utilites: Add-in yang berisi advanced selection options, advanced brwosing capabilities dan penggunaan formula yang lebih baik.
61. Send Mail: Tool gratis dari Ron de Bruin ini memungkinkan anda untuk mengirim email dengan konten dari suatu workbook, sheet atau bahkan area yang diseleksi saja.
62. Excel Sentry: Mencegah data bisnis anda jatuh ke tangan kompetitor dengan mengenkripsi spreadsheet anda.
63. XL Statistics: Paket statistik gratis yang mengembangkan fungsionalitas Excel.
64. Palo: Server OLAP (On-line Analytical Processing) open source untuk Excel. Server OLAP biasanya berharga ratusan ribu dollar, tetapi Jedox (perusahaan yang membuat Palo) memberikannya secara gratis.
65. PDF to Excel: PDF2XL dapat mengekstrak data dari file PDF untuk dimasukkan ke Excel.
66. FlorenceSoft: Aplikasi kecil ini dapat membantu anda untuk menemukan perbedaan antara dua sheet yang berbeda dengan mudah.
67. Excel Password Remover: Jika suatu saat anda lupa akan password untuk suatu spreadsheet anda, maka inilah jawabannya.
68. Tree Plan: Tool analisa data dari Mike Middleton.
69. DPlot: Gunakan ini untuk membuat grafik 2D dan 3D.
70. DigDB: Add-in produktivitas untuk Excel yang terkenal.
Sumber Excel lainnya
71. Excel User Conference: Dikelola oleh Daemon Longworth, sampai saat ini adalah tempat terbaik untuk meningkatkan keahlian Excel anda. Anda akan diajar oleh ahli-ahli Excel terbaik (semua instrukturnya adalah orang-orang terbaik Microsoft).
72. Charts by Jorge Camoes: Site yang didedikasikan untuk chart Excel dan add-in chart. Juga termasuk blog yang berpusat pada chart.
73. Excel funny videos: Siapa bilang Excel tidak menyenangkan?
74. Excel games: Dan akhirnya… Excel gaming platform.

Sumber: http://salsabilillahuey.multiply.com/journal/item/28 (terima kasih atas terjemahan dari http://www.cogniview.com/convert-pdf-to-excel/post/the-excel-magician-70-excel-tips-and-shortcuts-to-help-you-make-excel-magic/)

Pengaplikasian Command “Paste Picture Link” Lebih Lanjut

Sebuah group yang lebih baik adalah sebuah group yang mempunyai nilai yang lebih daripada penjumlahan masing-masing anggota group itu sendiri. Begitu pula dalam belajar excel, harapan lebih lanjut dalam mempelajari excel tidak hanya mampu mengetahui suatu kegunaan masing-masing fasilitas dalam excel, namun lebih lanjut adalah mampu menyusun suatu suatu kombinasi yang terdiri atas lebih dari satu fasilitas excel dalam rangka pencapaian tujuan dimana sebelumnya tidak dapat diselesaikan hanya dengan satu fasilitas excel.

Kembali ke topik pokok sesuai judul posting ini, dengan pengetahuan kita tentang cara penggunaan command “Paste Picture Link” serta pengetahuan kita dalam formula excel dan pemberian nama pada suatu range (defined name), kita dapat membuat suatu hasil kreatifitas seperti pada gambar berikut ini:

File dapat di-download di sini

Apabila angka yang terdapat pada gambar dengan frame berwarna indigo blue di atas kita rubah (angka mulai dari 1 s.d. 5) maka secara otomatis gambar ber-frame warna indigo blue akan berubah sesuai dengan setting data pada range gambar ber-frame hijau.

Dalam pembuatannya, dapat dilakukan tahapan sebagai berikut:
Tahap 1
Berikan nama pada range masing-masing picture.
Almoss >> =Sheet1!$B$4:$C$16
Betty >> =Sheet1!$E$4:$F$16
Cassanova >> =Sheet1!$H$4:$I$16
Domino >> =Sheet1!$K$4:$L$16
Emma >> =Sheet1!$N$4:$O$16
Tahap 2
Pilih diantara 2 cara berikut ini:
>Lakukan insert sembarang file berformat picture ke dalam worksheet dan tempatkan pada tempat yang diinginkan
>Copy>paste picture link pada tempat yang diinginkan dalam worksheet
Tahap 3
Berikan nama range pada cell yang akan digunakan sebagai perantara antara picture dan formula
nomer >> =Sheet1!$H$23
Tahap 4
Berikan formula pada picture yang ditujukan sebagai live picture.
pilih >> =CHOOSE(nomer;Almoss;Betty;Cassanova;Domino;Emma)

Mengenal Command “Paste Picture Link”

Apa itu Command “Paste Picture Link”
Command “paste picture link” merupakan saudara dari Command “Paste Special”. “Paste Picture Link” memberikan hasil paste dalam bentuk format picture yang hidup (live picture) dimana menampakkan berbagai perubahan tampilan yang terjadi pada copy range (berbeda dengan paste picture biasa). Apabila data pada area yang kita copy terdapat data input baru, atau perubahan warna cell, serta perubahan tampilan lainnya, maka secara otomatis perubahan dapat langsung termonitor dan terlihat pada live picture. Live picture dapat kita letakkan didaerah manapun dalam worksheet. Excel Live picture dapat diibaratkan sebagai sebuah TV Monitor petugas keamanan yang memonitor setiap situasi dan kejadian di suatu area dimana kamera keamanan ditempatkan secara live.

Dimanakah letak command “paste picture link”?
Gambar di bawah ini menunjukkan letak command “Paste Cicture Link” yang terdapat pada 2003 dan 2007. Pada excel 2003 command ini letaknya tersembunyi, karena memerlukan tambahan dengan menekan tombol shift sebelum meng-klik Menu Edit. Namun pemanfaatan live picture sebenarnya tidak harus melalui Menu Edit terlebih dahulu, pada setiap format picture yang ada pada worksheet dapat langsung dirubah menjadi live picture dengan langsung memberikan formula pada picture yang secara otomatis picture yang ada tergantikan menjadi live picture.

Pengaplikasian Command Paste Picture Link
1. Tentukan range yang akan di-live picture-kan

2. Lakukan perintah copy seperti biasa (Ctrl + C)
3. Lakukan perintah Paste Picture Link pada tempat pemonitoran. Hasil paste dapat digeser-geser atau dengan kata lain dapat dilaukan dragging ke tempat yang kita inginkan.

Apabila range $B$35:$D$39 (copy range) berubah, maka live picture akan otomatis berubah sesuai dengan perubahan pada daerah yang dilakukan peng-copy-an.
Silakan di download file berkaitan dengan Paste Picture Link di sini

Menambahkan Camera Tools Icon ke Toolbar
Cara lain untuk Paste Picture Link adalah dengan mengklik camera tools icon pada tollbar. Namun camera tools icon letaknya tersembunyi. Untuk mengeluarkannya dari persembunyiannya klik menu View > Toolbar > Customize > Tools > Camera. Letak camera icon lihat gambar berikut ini

Split Satu Baris Text Menjadi Beberapa Baris Dalam Cell yang Sama

Dengan Bantuan Alt + Enter
Untuk melakukan split atau pembagian text ke dalam beberapa baris dalam cell yang sama dapat dilakukan dengan cara menekan Alt + Enter. Untuk lebih jelasnya lihat gambar berikut ini.

Pada gambar di atas dilakukan Alt + Enter setelah kata Ayem, No.2, dan Jeruk.

Dengan Bantuan Formula
Cara yang lain adalah dengan menggunakan formula (dengan syarat bahwa cell berformat wrap-text).

Di depan setiap text yang akan kita lakukan pemisahan ke baris yang berbeda tambahkan CHAR(10)