Kata Pengantar........................................................................................................ v
Sinopsis
Buku ini menjelaskan trik-trik cerdas terbaru bagaimana cara menguasai Excel dan rumus fungsi dengan baik. Saya yakin sehabis anda mempelajari buku ini, anda akan menguasai bahan di Excel 2010 maupun 2007, alasannya yaitu buku ini sangat gampang untuk dipelajari yaitu di dalamnya termuat petunjuk penggunaan dengan metode step by step, citra ilustrasi yang dibantu dengan panah penunjuk, semoga kita gampang memahami bahan dan menyerupai biasa setiap final serpihan terdapat latihan semoga sanggup melatih kita mengingat trik yang telah dipelajari sebelumnya.
Setiap trik Excel & Rumus Fungsi dijelaskan secara gamblang dan terang sehingga kita bisa memahami maksud dari petunjuk penggunaan setiap ikon. Dengan membaca, menganalisa dan mempraktekkan kita akan hebat dalam hitungan hari dengan mudah.
sample buku
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 sehabis anda mempelajari buku ini, anda akan menguasai bahan di Excel 2010 maupun 2007, alasannya yaitu buku ini sangat gampang untuk dipelajari yaitu di dalamnya termuat petunjuk penggunaan dengan metode step by step, citra ilustrasi yang dibantu dengan panah penunjuk, semoga kita gampang memahami bahan dan menyerupai biasa setiap final serpihan terdapat latihan semoga sanggup melatih kita mengingat trik yang telah dipelajari sebelumnya.
Setiap trik Excel & Rumus Fungsi dijelaskan secara gamblang dan terang sehingga kita bisa memahami maksud dari petunjuk penggunaan setiap ikon. Dengan membaca, menganalisa dan mempraktekkan kita akan hebat dalam hitungan hari dengan mudah.
mengaktifkan sebagian Cell dan Mengunci Sheet
Agar Cell yang kita pilih sanggup aktif untuk melaksanakan edit data dan yang lainnya terkunci sanggup kita lakukan sebagai berikut. Sorot atau Blok Cell yang berisi data à klik mouse kanan pada area blokan à lalu klik Format Cells, maka akan muncul kotak obrolan Format Cells. Untuk lebih jelasnya perhatikan citra ilustrasi di bawah ini.
Pada kotak obrolan 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 sanggup kita gunakan dalam pengeditan à klik OK. Agar lebih jelasnya perhatikan citra ilustrasi di bawah ini.
Langkah selanjutnya kita akan mengunci Sheet dengan cara klik sub Menu Review à klik Protect Sheet à pada kotak obrolan Protect Sheet ketiklah Pasword yang kita inginkan dan pasword ini harus kita ingat semoga kita sanggup membukanya sewaktu-waktu diharapkan untuk pengeditan à selanjutnya klik OK dan kotak obrolan Confirm Password akan tampil. Agar lebih jelasnya perhatikan gambar di bawah ini.
Pada kotak dialog Confirm Password yaitu pada serpihan Reenter Password to Procced ketik ulanglah password tadi à klik OK.
Untuk membuka sheet yang telah dikunci sanggup dilakukan dengan cara klik Menu Review à klik tab Unprotect Sheet à pada kotak obrolan Unprotect Sheet ketik password yang tadi dan apabila terjadi kesalahan pengetikan password, maka sheet tidak akan terbuka.. semoga lebih jelasnya perhatikan gambar di bawah ini.
Apabila terjadi kesalahan pengetikan password maka akan muncul kotak obrolan menyerupai gambar berikut ini.
Untuk mengedit data sanggup kita lakukan pada Cells yang tidak terkunci dan pada cells yang terkunci tidak sanggup 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
0 Komentar untuk "139 Trik Cerdas Excel & Rumus Fungsi"