Senin, 07 Juli 2014

MENGUNGKAP RAHASIA FUNGSI EXCEL + CD

Jika anda memiliki buku ini isikan form berikut

BUKU INI MENJELASKAN TENTANG

1.  BAB 1  Solusi Tepat Penggabungan Fungsi Excel + Latihan
2.  BAB 2 Kreatif dengan 67 Fungsi Tunggal Excel + Latihan
3.  BAB 3 Empat Solusi Fungsi Terbilang Excel + Latihan

4.  BAB 4 Otomatisasi Penandaan Data

Contoh Video & Download

5.  BAB 5 Realisasi Fungsi Excel

Daftar Isi

Kata Pengantar............................................................................................................ i
Daftar Isi..................................................................................................................... iii
BAB  1  Solusi Tepat Penggabungan Fungsi Excel.............................. 1
1.1.  Gabungan Fungsi ROUND dan AVERAGE................................................. 2
1.2.  Gabungan Fungsi INT dan TODAY............................................................... 4
1.3.  Gabungan Fungsi IF dan AND........................................................................ 8
1.4.  Gabungan Fungsi IF dan OR........................................................................ 10
1.5.  Gabungan Fungsi SQRT dan ABS............................................................... 11
1.6.  Gabungan Fungsi IF, NOT, ISBLANK, dan VLOOKUP.......................... 12
1.7.  Gabungan Fungsi IF, AND dan OR............................................................. 19
1.8.  Gabungan Fungsi IF, NOT, ISBLANK dan HLOOKUP........................... 21
1.9.  Gabungan Fungsi INT dan AVERAGE....................................................... 29
1.10.  Gabungan Fungsi Aitmatika dan INT...................................................... 30
1.11.  Gabungan Fungsi ROUND dan LN........................................................... 31
1.12.  Gabungan Fungsi IF, NOT dan ISBLANK............................................... 32
1.13.  Gabungan Fungsi IF dan NOT................................................................... 34
1.14.  Penggunaan ARITMATIKA....................................................................... 35
1.15.  Latihan Gabungan Fungsi........................................................................... 37
1.15.1  Gabungan Fungsi INT, AVERAGE, IF, OR dan AND......................... 37
1.15.2  Gabungan Fungsi INT dan TODAY........................................................ 38
1.15.3  Gabungan Fungsi IF, NOT, ISBLANK, dan VLOOKUP..................... 40
BAB  2   Kreatif dengan 67 Fungsi Tunggal Excel........................... 44
2.1.  Fungsi ABS....................................................................................................... 45
2.2.  Fungsi ACOS.................................................................................................... 46
2.3.  Fungsi ACOSH................................................................................................. 47
2.4.  Fungsi AVERAGE........................................................................................... 48
2.5.  Fungsi COMBIN.............................................................................................. 50
2.6.  Fungsi CONVERT........................................................................................... 51
2.7.  Fungsi COS....................................................................................................... 56
2.8.  Fungsi COSH.................................................................................................... 58
2.9.  Fungsi COUNTBLANK.................................................................................. 59
2.10.  Fungsi COUNT.............................................................................................. 60
2.11.  Fungsi COUNTA........................................................................................... 61
2.12.  Fungsi COUNTIF.......................................................................................... 63
2.13.  Fungsi DTE..................................................................................................... 64
2.14.  Fungsi DAYS360........................................................................................... 65
2.15.  Fungsi DEC2HEX......................................................................................... 68
2.16.  Fungsi DEX2DEC......................................................................................... 70
2.17.  Fungsi DEC2BIN.......................................................................................... 71
2.18.  Fungsi DEC2OCT......................................................................................... 72
2.19.  Fungsi DEGREES.......................................................................................... 74
2.20.  Fungsi EVEN.................................................................................................. 75
2.21.  Fungsi EXP..................................................................................................... 76
2.22.  Fungsi FACT.................................................................................................. 77
2.23.  Fungsi GCD.................................................................................................... 78
2.24.  Fungsi HOUR................................................................................................. 80
2.25.  Fungsi IF......................................................................................................... 81
2.26.  Fungsi INT...................................................................................................... 83
2.27.  Fungsi ISBLANK.......................................................................................... 84
2.28.  Fungsi LCM................................................................................................... 86
2.29.  Fungsi LEFT................................................................................................... 88
2.30.  Fungsi LEN..................................................................................................... 91
2.31.  Fungsi LN....................................................................................................... 94
2.32.  Fungsi LOG.................................................................................................... 95
2.33.  Fungsi LOG10................................................................................................ 97
2.34.  Fungsi LOWER.............................................................................................. 98
2.35.  Fungsi MAX................................................................................................... 99
2.36.  Fungsi MID.................................................................................................. 102
2.37.  Fungsi MIN.................................................................................................. 104
2.38.  Fungsi MINUTE.......................................................................................... 106
2.39.  Fungsi MOD................................................................................................. 107
2.40.  Fungsi NOW................................................................................................. 108
2.41.  Fungsi NPER................................................................................................ 109
2.42.  Fungsi ODD.................................................................................................. 111
2.43.  Fungsi PMT.................................................................................................. 112
2.44.  Fungsi POWER............................................................................................ 114
2.45.  Fungsi PRODUCT....................................................................................... 119
2.46.  Fungsi QUOTIENT..................................................................................... 120
2.47.  Fungsi RATE............................................................................................... 121
2.48.  Fungsi RIGHT............................................................................................. 125
2.49.  Fungsi ROUND............................................................................................ 126
2.50.  Fungsi ROUNDDOWN............................................................................... 130
2.51.  Fungsi ROUNDUP...................................................................................... 131
2.52.  Fungsi SECOND.......................................................................................... 132
2.53.  Fungsi SIN.................................................................................................... 133
2.54.  Fungsi SINH................................................................................................ 134
2.55.  Fungsi SQRT................................................................................................ 136
2.56.  Fungsi SUM................................................................................................. 137
2.57.  Fungsi SUMIF............................................................................................. 139
2.58.  Fungsi SUMPRODUCT............................................................................. 143
2.59.  Fungsi SUMSQ............................................................................................ 145
2.60.  Fungsi SUMX2MY2................................................................................... 146
2.61.  Fungsi TAN.................................................................................................. 148
2.62.  Fungsi TIME................................................................................................ 149
2.63.  Fungsi TODAY............................................................................................ 151
2.64.  Fungsi UPPER............................................................................................. 151
2.65.  Fungsi Penggabungan Karakter............................................................... 153
2.66. Fungsi PROPER........................................................................................... 155
2.67. Fungsi RANK................................................................................................ 156
2.68. Latihan Fungsi............................................................................................. 161
2.68.1  Latihan Fungsi SUM, MAX, MIN, AVERAGE dan RANK.............. 161
2.68.2  Latihan COUNT, COUNIF, COUNTA, dan COUNTBLANK.......... 163
2.68.3  Latihan PMT............................................................................................. 166
2.68.4. Latihan RATE.......................................................................................... 167
2.68.5. Latihan NPER........................................................................................... 168
2.68.6. Latihan UPPER, LOWER, RIGHT, LEFT dan LEN.......................... 168
2.68.7. Latihan ROUND, INT, dan MOD......................................................... 169
BAB   3  4 Solusi Mahir Fungsi Terbilang pada Excel.................. 171
3.1. Solusi Penggunaan Fungsi TerbBiasa........................................................ 171
3.2. Terbilang Inggris............................................................................................. 176
3.3. TerbRupiah dan Sen..................................................................................... 180
3.4. Terbilang 3 digit 2 angka dengan memanfaatkn fungsi gabungan..... 185
BAB   4  Otomatisasi Penandaan Data................................................... 189
4.1.   Memanfaatkan  Highlight Cells Ruler..................................................... 189
4.1.1.  Mengatur data dengan Greater Than.................................................... 189
4.1.2.  Mengatur data dengan Less Than......................................................... 193
4.1.3.  Menandai data dengan Between........................................................... 197
4.1.4.  Menandai data dengan Equal to............................................................ 201
4.1.5.  Mengatur data dengan Text That Contains......................................... 205
4.1.6.  Menandai data dengan A Date Occurring............................................ 207
4.1.7.  Menandai data dengan Duplicate Values............................................ 210
4.2.    Memanfaatkan Top/Bottom Rules........................................................ 212
4.2.1.  Menandai data dengan Top 10 Items................................................... 212
4.2.2.  Menandai data dengan Top 10%.......................................................... 214
4.2.3.  Menandai data dengan Bottom 10 Items............................................ 216
4.2.4.  Menandai data dengan Bottom 10%.................................................... 218
4.2.5.  Menandai data dengan Above Average............................................... 220
4.2.6.  Menandai data dengan Below Average................................................ 222
4.3   Menandai data dengan Data Bars............................................................ 225
4.3.1.  Menandai data dengan Gradient Fill..................................................... 225
4.3.2.  Menandai data dengan Solid Fill............................................................ 226
4.4.    Menandai data dengan Colors Scales.................................................... 228
4.5.    Menggunakan Icon Sets............................................................................ 229
4.5.1.  Menandai data dengan Directional....................................................... 229
4.5.2.  Menandai data dengan Shapes.............................................................. 231
4.5.3.  Menandai data dengan Indicators......................................................... 232
4.5.4.  Menandai data dengan Ratings............................................................. 234
4.6.  Menghapus penandaan dengan Clear Rules........................................... 235
BAB  5  Realisasi Fungsi Excel.................................................................. 237
5.1.  Realisasi pada Kuitansi Pembayaran....................................................... 237
5.2.  Realisasi pada Nota Kontan....................................................................... 249
5.3.  Realisasi pada Nilai Sertifikat Komputer................................................. 258
Tentang Penulis............................................................................................... 265

Sinopsis

Mengulas berbagai trik solusi tepat dalam menguasai Excel secara cepat. Pembahasannya mencakup penggabungan fungsi Excel, fungsi Tunggal Excel, fungsi Terbilang, otomatisasi penandaan data, realisasi fungsi Excel. Selain itu, juga disertai latihan fungsi Excel untuk membantu penguasaan materi yang diberikan. Contoh-contoh kasus diolah secara sederhana dan step by step sehingga pembaca dapat memahami isi materi dengan cepat.
Setelah mempelajari Mengungkap Rahasia Fungsi Excel, Anda akan dapat menguasai materi Excel yang sering dicari secara otodidak. Buku ini dapat digunakan pada berbagai versi Excel, terutama Excel 2007, 2010, dan 2013.

DETAIL

JudulMengungkap Rahasia Fungsi Excel + Cd
SeriMicrosoft Excel
ISBN/EAN9786020236124 / 9786020236124
PengarangHamdan Lugina Jaya , S.Pd
PenerbitELEX MEDIA
Terbit9 April 2014
Pages260
Berat250 gram
Dimensi(mm)140 x 210
Tagsaplikasi perkantoran,contoh kasus,fungsi excel,materi,pembahasan,solusi,trik
KategoriKomputer
Jika anda pernah/punya/membeli buku ini isikan form pelanggan di bawah ini

FORMULIR ISIAN PELANGGAN

Sample Buku :

3.1.1.   Mengatur  data dengan Greater Than

Greater Than berfungsi untuk mengatur suatu data dengan kriteria tertentu pada cells atau  untuk mengatur format sel jika kondisi sel memiliki nilai lebih besar dari suatu nilai tertentu. Misalnya batas nilai tertinggi yang ditentukan adalah 70, maka nilai diatas 70 akan  berwarna/memiliki tanda sesuai dengan kriteria yang ditentukan.
Berikut contoh penggunaan Greater Than di bawah ini. Ketiklah data berikut ini kemudian lakukanlah langkah berikutnya.

Lakukan langkah berikutnya yang perlu dilakukan adalah
1.  Bloklah semua kolom nilai
2.  Klik menu HOME
3.  Klik Conditional Formatting
4.  Klik Highlight Cells Rules
5.  Klik Greater Than
Untuk lebih jelasnya perhatikanlah gambar di bawah ini.

Gambar 4.1 Proses pencarian Greater Than
Pada kotak dialog Greater Than lakukanlah langkah berikut ini.
1.  Pada Format cells that are GREATER THAN ketiklah 70 atau kita juga dapat mengklik cells yang berisi batas nilai.
2.  Pada With pilihlah salah satu kriteria penandaan yang diinginkan
3.  Klik OK.
Untuk lebih jelasnya perhatikalnah gambar berikut ini.

Gambar 4.2 kotak dialog GREATER THAN

Gambar 4.3  Hasil GREATER THAN
Selain cara diatas fungsi ini dapat juga digunakan untuk text. Berikut contoh penggunaan Greater Than untuk kriteria text huruf. Tambahkanlah kriteria prestasi di samping nama siswa berikut ini.

Langkah selanjutnya adalah
1.  Bloklah kolom Prestasi kemudian klik Menu HOME
2.  Klik Conditional Formatting
3.  Klik Highlight Cells Rules
4.  Kliklah Greater Than
5.  Pada kotak dialog GREATER THAN di kolom Format cells that are GREATER THAN ketiklah karakter yang diinginkan selain angka misalnya huruf B, maka data selain huruf B dan A akan tetap dan yang lainnya akan berwarna sesuai dengan kriteria.
6.  Pada bagian with pilih dan kliklah kriteria penandaan yang diinginkan.
7.  Klik OK.
Agar lebih memperluas pemahaman kita lihatlah gambar di bawah ini.

Gambar 4.4 Kotak dialog GREATER THAN


Gambar 4.5  Hasil GREATER THAN

Buku lain yang telah terbit
Diposting oleh di Tidak ada komentar:

PENERAPAN FUNGSI EXCEL UNTUK APLIKASI

Jika anda pernah/punya/membeli buku ini isikan form pelanggan di bawah ini
Rp. 39.800
Daftar Isi

Kata Pengantar............................................................................................................ i
Daftar Isi..................................................................................................................... iii
BAB   1  Aplikasi Raport Bayangan SD....................................................... 1
1.1    Aplikasi Raport Bayangan SD....................................................................... 1
1.2    Penerapan fungsi AVERAGE......................................................................... 3
1.3    Penerapan fungsi ROUND.............................................................................. 4
1.4    Trik Membuat List Kelas................................................................................ 7
1.5    Trik Membuat List Semester.......................................................................... 8
1.6    Trik membuat List Tahun Pelajaran.......................................................... 11
1.7    Trik membuat List Exskul............................................................................ 13
1.8    Trik Paste Link................................................................................................ 15
1.9    Penandaan dengan Conditional Formatting............................................. 20
1.10   Penerapan fungsi SUM dan AVERAGE................................................... 23
1.11   Trik Copy dan Paste Link........................................................................... 28
1.12   Trik membuat List Induk............................................................................ 37
1.13   Penerapan Fungsi VLOOKUP..................................................................... 40
1.14   Pemanfaatan Conditional Formatting..................................................... 45
1.15   Penggunaan Visual Basic............................................................................ 49
1.16   Penggabungan Fungsi Logika IF dengan terbilang................................. 57
1.17   Penulisan gabungan fungsi IF dan VLOOKUP....................................... 58
BAB 2  Pembuatan Aplikasi Raport MTs................................................. 61
2.1    Aplikasi Raport MTs..................................................................................... 61
2.2    Pembuatan List Semester............................................................................. 64
2.3    Pemanfaatan Fungsi IF dan VLOOKUP................................................... 77
2.4    Memasukan fungsi terbilang pada Visual Basic dan fungsi gabungan
         IF........................................................................................................................ 81
2.5    Menandai data dengan Conditional Formatting..................................... 86
BAB  3  Pembuatan Aplikasi untuk Sertifikat...................................... 92
3.1    Aplikasi Nilai Sertifikat Komputer ............................................................. 92
3.2    Menggunakan Paste Link............................................................................. 96
3.3    Membuat List Induk..................................................................................... 98
3.4    Penggunaan fungsi IF dan VLOOKUP.................................................... 101
BAB  4   Pembuatan Aplikasi Nota Kontan.......................................... 107
4.1    Format Aplikasi Nota Kontan................................................................... 107
4.2    List Barang.................................................................................................... 108
4.3    Penggabungan fungsi IF,NOT,ISBLANK dan VLOOKUP................... 112
BAB  5   Pembuatan Aplikasi Daftar Transaksi Penjualan......... 114
5.1    Format Aplikasi Daftar Transaksi Penjualan......................................... 114
5.2    Pembuatan list kode.................................................................................... 115
5.3    Penulisan Fungsi Gabungan IF, ISBLANK, dan HLOOKUP............... 119
BAB  6    Pembuatan Aplikasi Daftar Gaji & Struk Gaji Karyawan                     122
6.1    Format Aplikasi Daftar Gaji & Struk Gaji.............................................. 122
6.2    Membuat list kode karyawan................................................................... 124
6.3    Proses Pengetikan rumus fungsi IF, VLOOKUP dan SUM................... 127
BAB  7  Penerapan Aplikasi untuk SKHUN........................................... 134
7.1    Format Aplikasi SKHUN............................................................................ 134
7.2    Validasi Data................................................................................................ 136
7.3    Penggunaan fungsi IF dan Vlookup......................................................... 138
7.4    Penggunaan berbagai gabungan fungsi................................................... 139
7.5    Mengunci sheet dan mengaktifkan Cells ............................................... 143
7.6    Mengunci Documen.................................................................................... 146
BAB  8   Kumpulan Berbagai Fungsi  Excel......................................... 149
8.1    Penerapan SUM, Average, dan COUNTA.............................................. 149
8.2    Penerapan fungsi COUNT, MAX, dan MIN........................................... 152
8.3    Menerapkan fungsi IF dan COUNTIF..................................................... 153
8.4    Fungsi INT..................................................................................................... 156
8.5    Fungsi ROUND............................................................................................. 157
8.6    Fungsi MOD.................................................................................................. 158
8.7    Fungsi PRODUCT........................................................................................ 159
8.8    Fungsi  Gabungan INT dan TODAY........................................................ 160
8.9    Fungsi LEN.................................................................................................... 161
8.10    Fungsi Fungsi Gabungan IF dan AND................................................... 162
8.11    Fungsi Gabungan IF dan OR................................................................... 164
8.12    Penggabungan Karakter........................................................................... 165
8.13    Fungsi POWER........................................................................................... 166
8.14    Fungsi SQRT............................................................................................... 167
8.15    Fungsi FACT............................................................................................... 168
8.16    Fungsi LN.................................................................................................... 169
8.17    Fungsi LOG................................................................................................. 170
8.18    Fungsi LOG10............................................................................................ 171
8.19    Fungsi COS................................................................................................. 172
8.20    Fungsi SIN................................................................................................... 173
8.21    Fungsi TAN................................................................................................. 174
8.22    Fungsi PMT................................................................................................. 175
8.23    Fungsi NPER............................................................................................... 176
8.24    Fungsi RATE.............................................................................................. 177
8.25    Fungsi LOWER.......................................................................................... 178
8.26    Fungsi UPPER............................................................................................ 180
8.27    Fungsi LEFT............................................................................................... 181
8.28    Fungsi RIGHT............................................................................................ 182
8.29    Fungsi MID................................................................................................. 183
8.30    Fungsi CONVERT..................................................................................... 184
8.31    Fungsi DEC2BIN....................................................................................... 185
8.32    Fungsi DEC2HEX...................................................................................... 187
8.33    Fungsi DEC2OCT...................................................................................... 188
8.34    Fungsi SECOND......................................................................................... 189
8.35    Fungsi NOW................................................................................................ 190
8.36    Fungsi TODAY........................................................................................... 191
8.37    Fungsi TIME............................................................................................... 192
8.38    Fungsi MINUTE......................................................................................... 193
8.39    Fungsi HOUR............................................................................................. 194
8.40   Fungsi DATE............................................................................................... 195
8.41   Fungsi DAYS360........................................................................................ 196
Tentang Penulis............................................................................................... 198

info pesanan ke : www.elexmedia.co.id
atau anda dapat membeli di TB GRAMEDIA tedekat jika masih tersedia stoknya

Download Hasil Buku

1. Aplikasi Raport SD
2. Aplikasi Raport MTs
3. Aplikasi Gaji Karyawan
4. Aplikasi Kuitansi/Nota
5. Berbagai Trik Rumus fungsi
6. Aplikasi Terbilang Text
7. Aplikasi Terbilang Xls
8. Aplikasi SKHUN
9. Download REVISI BAB 4 (Setelah mendownload lanjutkan pembahasannya ke Hal 108)
PENERAPAN FUNGSI EXCEL UNTUK APLIKASI

Jika anda pernah/punya/membeli buku ini isikan form pelanggan di bawah ini




Diposting oleh di Tidak ada komentar:

139 TRIK CERDAS EXCEL & RUMUS FUNGSI

jika anda memiliki buku ini isikan form berikut

Jika anda pernah/punya/membeli buku ini isikan form pelanggan di bawah ini

Daftar Isi


 Kata Pengantar........................................................................................................ v
 Daftar Isi................................................................................................................. vii
 BAB 1  TRIK PENGGUNAAN MENU HOME........................................................ 1
1.1 Group Clipboard................................................................................................... 2
1.1.1  Trik 1. Copy - Paste........................................................................................... 2
1.1.2  Trik 2. Copy Formulas...................................................................................... 3
1.1.3  Trik 3. Paste - No Border.................................................................................. 4
1.1.4  Trik 4. Paste - Keep Source Column Widths.................................................. 5
1.1.5  Trik 5. Paste - Transpose.................................................................................. 6
1.1.6  Trik 6. Paste - Formatting................................................................................. 6
1.1.7  Trik 7 Penggunaan Paste - Picture................................................................... 7
1.1.8  Trik 8. Penggunaan PAste - Linked Picture.................................................... 8
1.1.9  Trik 9. Penggunaan Copy - Paste Link........................................................... 9
1.1.10  Trik 10 Menggunakan Copy As Picture...................................................... 10
1.2 Group FONT........................................................................................................ 12
1.2.1 Trik 11 Pengaturan Font.................................................................................. 12
1.2.2 Trik 12. Pengaturan Font Size......................................................................... 12
1.2.3 Trik 13. Pengaturan All Border....................................................................... 13
1.2.4 Trik 14. Penggunaan Bold............................................................................... 14
1.2.5 Trik 15. Penggunaan Italic............................................................................... 15
1.2.6 Trik 16. Penggunaan Double Underline......................................................... 16
1.2.7 Trik 17. Penggunaan Fill Color....................................................................... 17
1.2.8 Trik 18. Penggunaan Font Color..................................................................... 18
1.3 Group Alignment................................................................................................. 19
1.3.1 Trik 19. Penggunaan Top Align...................................................................... 19
1.3.2 Trik 20. Penggunaan Middle Align................................................................. 21
1.3.3 Trik 21. Bottom Align dan Align Text Left.................................................... 22
1.3.4 Trik 22. Penggunaan Center............................................................................ 23
1.3.5 Trik 23. Penggunaan  Align Text Right.......................................................... 24
1.3.6 Trik 24. Penggunaan Orientation.................................................................... 25
1.3.7 Trik 25. Penggunaan Wrap Text..................................................................... 26
1.3.8 Trik 26. Penggunaan Merge & Center............................................................ 27
1.4 Group Styles................................................28
1.4.1 Trik 27. Menggunakan Less Than.................................................................. 28
1.4.2 Trik 28. Menggunakan Greater Than............................................................ 31
1.4.3 Trik 29. Penggunaan Between......................................................................... 33
1.4.4 Trik 30. Menggunakan Equal To.................................................................... 35
1.4.5 Trik 31. Text That Contains............................................................................ 37
1.4.6 Trik 32. Duplicate Values................................................................................. 39
1.4.7 Trik 33. Top 10 Items....................................................................................... 41
1.4.8 Trik 34. Bottom 10 Items................................................................................. 43
1.4.9 Trik 35. Top 10%.............................................................................................. 46
1.4.10 Trik 36. Menggunakan Bottom 10%............................................................ 48
1.4.11 Trik 37. Menggunakan Above Average........................................................ 50
1.4.12 Trik 38. Menggunakan Below Average........................................................ 52
1.4.13 Trik 39. Data Bars.......................................................................................... 54
1.4.14 Trik 40. Color Scales....................................................................................... 56
1.4.15 Trik 41. Icon Sets............................................................................................ 58
1.4.16 Trik 42. Clear Ruler........................................................................................ 60
1.5  Group Cells.......................................................................................................... 61
1.5.1 Trik 43. Insert Cells.......................................................................................... 61
1.5.2 Trik 44. Delete................................................................................................... 63
1.5.3 Trik 45. Format................................................................................................. 65
1.5.4 Trik 46. Column Widht.................................................................................... 68
1.5.5 Trik 47. Row Height......................................................................................... 70
1.5.6 Trik 48. Hide Rows dan Unhide Rows........................................................... 72
1.5.7 Trik 49 Hide Column & Unhide Column....................................................... 74
1.5.8 Trik 50. Rename Sheet..................................................................................... 77
1.5.9 Trik 51. Move or copy Sheet............................................................................ 78
1.6 Group Number..................................................................................................... 79
1.6.1 Trik 52. Penggunaan Custom.......................................................................... 79
1.6.2 Trik 53. Menggunakan lambang Rupiah....................................................... 84
1.6.3 Trik 54. Comma Style....................................................................................... 85
1.6.4 Trik 55. Increase Decimal................................................................................. 87
1.6.5 Trik 56. Decrease Decimal................................................................................ 88
1.6.6 Trik 57. Menggunakan Special........................................................................ 89
1.6.7 Trik 58. Penggunaan Percent Style................................................................. 90
1.7  Group Editing..................................................................................................... 92
1.7.1 Trik 59. Editing SUM....................................................................................... 92
1.7.2 Trik 60. Editing Average.................................................................................. 95
1.7.3 Trik 61. Editing Count Number...................................................................... 97
1.7.4 Trik 62. Editing MAX.................................................................................... 100
1.7.5 Trik 63. Editing MIN...................................................................................... 101
1.7.6 Trik 64. Penggunaan Down........................................................................... 102
1.7.7 Trik 65. Clear All............................................................................................ 103
1.7.8 Trik 66. Clear Formats................................................................................... 104
1.7.9 Trik 67. Clear Contens................................................................................... 106
1.7.10 Trik 68. Clear Comment.............................................................................. 107
1.7.11 Trik 69. Sort & Filter.................................................................................... 108
1.7.12 Trik 70. Find $ Select.................................................................................... 110
BAB 2 TRIK PENGGUNAAN MENU INSERT................................................... 113
2.1 Group Chart....................................................................................................... 113
2.1.1 Trik 71. Chart Column.................................................................................. 113
2.1.2 Trik 72. Chart Line......................................................................................... 115
2.1.3 Trik 73. Chart Pie........................................................................................... 116
2.1.4 Trik 74. Chart Bar.......................................................................................... 118
2.1.5 Trik 75. Chart Area........................................................................................ 119
2.1.6 Trik 76. Chart Scatter.................................................................................... 121
2.1.7 Trik 77. Other Charts..................................................................................... 122
2.2 Group Tables...................................................................................................... 124
2.2.1  Trik 78. Table................................................................................................. 124
2.2.2 Trik 79. Pivot Table........................................................................................ 126
2.2.3  Trik 80. Picture............................................................................................... 128
2.3 Group Illustration ............................................................................................. 128
2.3.1 Trik 81. ClipArt.............................................................................................. 130
2.3.2 Trik 82. Screenshot......................................................................................... 131
2.3.3 Trik 83. Shapes............................................................................................... 134
2.3.4 Trik 84. SmartArt........................................................................................... 136
2.4 Group Links ...................................................................................................... 138
2.4.1 Trik 85. Hyperlink.......................................................................................... 138
2.5 Group Text......................................................................................................... 139
2.5.1 Trik 86. Tex Box.............................................................................................. 139
2.5.2 Trik 87. Header & Footer............................................................................... 140
2.5.3 Trik 88. WordArt............................................................................................ 142
2.5.4 Trik 89. Object................................................................................................ 143
2.6 Group Symbols.................................................................................................. 146
2.6.1 Trik 90. Equation............................................................................................ 146
2.6.2 Trik 91. Symbol............................................................................................... 148
BAB 3. TRIK PENGGUNAAN MENU PAGE LAYOUT.................................... 149
3.1  Group  Themes.................................................................................................. 149
3.1.1  Trik 92. Themes............................................................................................. 149
3.2.  Group Page Setup............................................................................................ 150
3.2.1  Trik 93. Print Area......................................................................................... 150
3.2.2  Trik 94. Breaks............................................................................................... 152
3.2.3  Trik 95. Background..................................................................................... 154
3.2.4  Trik 96. Print Titles........................................................................................ 156
3.2.5  Trik 97.Orientation........................................................................................ 158
3.3 Group Sheet Options......................................................................................... 160
3.3.1  Trik 98.Gridlines............................................................................................ 160
3.3.2  Trik 99.Headings............................................................................................ 161
3.4  Group Arrange.................................................................................................. 162
3.4.1  Trik 100.  Rotate............................................................................................ 162
BAB 4 PENGGUNAAN MENU FORMULAS, MENU DATA,MENU REVIEW, & VIEW     164
4.1  Group Formulas................................................................................................ 164
4.1.1 Trik 101. Insert Function............................................................................... 164
4.1.2 Trik 102. Auto SUM....................................................................................... 167
4.1.3 Trik 103. Penggunaan Text............................................................................ 168
4.2  Menu Data......................................................................................................... 169
4.2.1 Trik 104.Text to Columns.............................................................................. 169
4.3  Group Review.................................................................................................... 173
4.3.1 Trik 105. New Comment................................................................................ 173
4.4 Group View........................................................................................................ 175
4.4.1 Trik 106. Freeze Panes.................................................................................... 175
4.5 Menu File............................................................................................................ 176
4.5.1  Trik 107 Print Out......................................................................................... 176
BAB 5 PENGGUNAAN FUNGSI EXCEL........................................................... 177
5.1  Rumus Fungsi................................................................................................... 177
5.1.1  Trik 108. Penggabungan Karakter.............................................................. 185
5.1.2  Trik 109.Penggabungan Fungsi IF untuk Aplikasi Terbilang.................. 178
5.1.3  Trik 110.  ROUND......................................................................................... 181
5.1.4  Trik 111. Fungsi Gabungan IF, AND dan OR........................................... 183
5.1.5  Trik 112.Fungsi Gabungan IF, NOT, ISBLANK, dan VLOOKUP......... 184
5.1.6  Trik 113. Penggunaan ARITMATIKA....................................................... 185
5.1.7  Trik 114. Fungsi PRODUCT........................................................................ 186
5.1.8  Trik 115. Fungsi IF dengan 2 Kriteria ........................................................ 187
5.1.9  Trik 116. Fungsi IF dengan 3 Kriteria......................................................... 188
5.1.10 Trik 117. Fungsi IF dengan 4 Kriteria........................................................ 189
5.1.11 Trik 118. Fungsi IF dengan 5 Kriteria........................................................ 191
5.1.12 Trik 119. Fungsi IF dengan 6 Kriteria........................................................ 193
5.1.13 Trik 120. Fungsi LEN................................................................................... 195
5.1.14 Trik 121. Fungsi UPPER.............................................................................. 196
5.1.15 Trik 122. Fungsi LOWER............................................................................ 197
5.1.16 Trik 123. Fungsi COUNT............................................................................ 197
5.1.17 Trik 124. Penggunaan COS......................................................................... 199
5.1.18 Trik 125. Penggunaan SIN.......................................................................... 200
5.1.19 Trik 126. Penggunaan TAN......................................................................... 201
5.1.20 Trik 127. Penggunaan LOG......................................................................... 203
5.1.21 Trik 128. Menggunaan LN.......................................................................... 204
5.1.22 Trik 129. Penggunaan FACT...................................................................... 205
5.1.23 Trik 130. Menggunakan MODE................................................................. 206
5.1.24 Trik 131. MEDIA.......................................................................................... 208
5.1.25 Trik 132. QUARTILE.................................................................................. 209
5.1.26 Trik 133. Menggunakan SQRT................................................................... 210
5.1.27 Trik 134. Fungsi INT.................................................................................... 211
5.1.28 Trik 135. Fungsi POWER............................................................................ 212
5.1.29 Trik 136. Vlookup & Data Validation........................................................ 213
5.1.30 Trik 137. HLOOKUP.................................................................................... 220
5.1.31 Trik 138  Fungsi COUNTA......................................................................... 224
5.1.32 Trik 139 Fungsi COUNTBLANK............................................................... 225
BAB 2. LATIHAN TRIK  EXCEL & RUMUS FUNGSI...................................... 227
Trik 1. Sort & Filter................................................................................................. 227
Trik 2. Protect Sheet................................................................................................ 229
Trik 3. Insert Function............................................................................................ 230
Trik 4. Chart............................................................................................................ 232
Trik 5. Above Average............................................................................................. 233
Trik 6. SmartArt...................................................................................................... 234
Trik 7. Fungsi COUNT & COUNTIF................................................................... 236
Trik 8. Fungsi Logika IF......................................................................................... 238
Trik 9. Menggunakan SUM, MAX, MIN, & AVERAGE................................... 239
Trik 10. Data Validation, IF, VLOOKUP, SUM & ISBLANK........................... 240
Tentang Penulis..................................................................................................... 244

Sinopsis


Buku ini menjelaskan trik-trik cerdas terbaru bagaimana cara menguasai Excel dan rumus fungsi dengan baik. Saya yakin setelah anda mempelajari buku ini, anda akan menguasai materi di Excel 2010 maupun 2007, karena buku ini sangat mudah untuk dipelajari yaitu di dalamnya termuat petunjuk penggunaan dengan metode step by step, gambaran ilustrasi yang dibantu dengan panah penunjuk, agar kita mudah memahami materi dan seperti biasa setiap akhir bab terdapat latihan agar dapat melatih kita mengingat trik yang telah dipelajari sebelumnya.


Setiap trik Excel & Rumus Fungsi dijelaskan secara gamblang dan jelas sehingga kita mampu memahami maksud dari petunjuk penggunaan setiap ikon. Dengan membaca, menganalisa dan mempraktekkan kita akan mahir dalam hitungan hari dengan mudah.

sample buku

mengaktifkan sebagian Cell dan Mengunci Sheet
Agar Cell yang kita pilih dapat aktif untuk melakukan edit data dan yang lainnya terkunci dapat kita lakukan  sebagai berikut. Sorot atau Blok Cell yang berisi data à klik mouse kanan pada area blokan à kemudian klik Format Cells, maka akan muncul kotak dialog Format Cells. Untuk  lebih jelasnya perhatikan gambaran ilustrasi di bawah ini.

Pada kotak dialog Format Cells pilih dan kliklah Protectionà hilangkan tanda Ceklis pada Locked dengan cara mengklik à apabila kita ceklis maka cell tersebut tidak aktif dan apabila kita hilangkan tanda ceklisnya hal tersebut berarti Cells aktif dan dapat kita gunakan dalam pengeditan à klik OK. Agar lebih jelasnya perhatikan gambaran ilustrasi di bawah ini.

Langkah selanjutnya kita akan mengunci Sheet dengan cara klik sub Menu Review à klik Protect Sheet  à pada kotak dialog Protect Sheet ketiklah Pasword yang kita inginkan dan pasword ini harus kita ingat agar kita dapat membukanya sewaktu-waktu dibutuhkan untuk pengeditan à selanjutnya klik OK dan kotak dialog Confirm Password akan tampil. Agar lebih jelasnya perhatikan gambar di bawah ini.

Pada kotak dialog  Confirm Password yaitu pada bagian Reenter Password to Procced ketik ulanglah password tadi à klik OK.

Untuk membuka sheet yang telah dikunci dapat dilakukan dengan cara klik Menu Review à klik tab Unprotect Sheet à pada kotak dialog Unprotect Sheet ketik password yang tadi dan apabila terjadi kesalahan pengetikan password, maka sheet tidak akan terbuka.. agar lebih jelasnya perhatikan gambar di bawah ini.

Apabila terjadi kesalahan pengetikan password maka akan muncul kotak dialog seperti gambar berikut ini.

 Untuk mengedit data dapat kita lakukan pada Cells yang tidak terkunci  dan pada cells yang terkunci tidak dapat kita ubah atau kita edit. Untuk lebih jelasnya perhatikan gambar di bawah ini.

TRIK MENGHILANGKAN/TIDAK AKAN MENAMPLIKAN ANGKA 0 PADA CELL
1. blok data yang tidak akan ditampilkan angka 0
2. klik kanan mouse
3. klik Format Cells


1. klik special
2. pada Location pilih pourtuguese (portugal)
2. klik numero de telefone
3. klik OK
hasil


Kembali ke Daftar ISI

Diposting oleh di Tidak ada komentar:
Langganan: Postingan (Atom)