Posts filed under ‘Tutorial’
10 most favorit excel functions with examples
Here are 10 popular Excel functions along with examples:
- SUM: Adds up a range of cells. Example:
=SUM(A1:A5)
adds the values in cells A1 to A5. - AVERAGE: Calculates the average of a range of cells. Example:
=AVERAGE(B1:B10)
calculates the average of values in cells B1 to B10. - COUNT: Counts the number of cells that contain numbers. Example:
=COUNT(C1:C8)
counts the number of cells in C1 to C8 that contain numbers. - MAX: Finds the maximum value in a range of cells. Example:
=MAX(D1:D6)
returns the highest value in cells D1 to D6. - MIN: Finds the minimum value in a range of cells. Example:
=MIN(E1:E9)
returns the lowest value in cells E1 to E9. - IF: Performs a logical test and returns different values based on the result. Example:
=IF(F1>10, "Pass", "Fail")
checks if the value in cell F1 is greater than 10 and returns “Pass” if true, or “Fail” if false. - VLOOKUP: Searches for a value in the leftmost column of a table and returns a corresponding value from a specified column. Example:
=VLOOKUP(G1, A1:B10, 2, FALSE)
looks for the value in G1 within the range A1 to B10, and returns the value from the second column of the range. - CONCATENATE: Joins multiple text strings into one. Example:
=CONCATENATE("Hello", " ", "World")
combines the text strings “Hello,” ” “, and “World” to form “Hello World”. - LEN: Calculates the length of a text string. Example:
=LEN(H1)
returns the number of characters in the text string in cell H1. - TODAY: Returns the current date. Example:
=TODAY()
returns the current date.
These are just a few examples of popular Excel functions. Excel offers many more functions that can perform a wide range of calculations and operations on data.
Pusing dengan Rumus yang Panjang?
Ya ….. judul di atas memang sering dialami juga saya maupun teman-teman dan mungkin juga Anda. Belajar rumus excel kebanyakan memang tidak mudah. Yang paling sering kita gunakan adalah sum, max, min, average, dan if. Kalau anda disuruh membaca lagi aturan untuk menggunakan vlookup misalnya, mungkin masih perlu lagi waktu untuk mencernanya. Belum lagi keterbaatasan Ms Excel yang paling terkenal adalah bahwa Anda tidak dapat “menggabungkan” atau “menumpuk” rumus dalam satu sel lebih dari 7 secara ”berjenjang”untuk Ms Excel 2003 ke bawah. Misalnya, rumus berikut ini akan menghasilkan IF berikut akan menghasilkan error. (Walaupun hal ini tidak masalah buat Ms Excel 2007)
=IF(Sheet1!$A$4=1,11,IF(Sheet1!$A$4=2,22,IF(Sheet1!$A$4=3,33, IF(Sheet1!$A$4=4,44,IF(Sheet1!$A$4=5,55,IF(Sheet1!$A$4=4,44,
IF(Sheet1!$A$4=5,55,IF(Sheet1!$A$4=6,66,IF($A$4=7,77,FALSE))))))))
Membuat Zoom Pada Cell Active
Menarik juga pertanyaan Pak Martono: “Gimana membuat zoom pada cell yang aktif tank,s eh thank’s”
Untuk membesarkan sel aktif ada beberapa cara, tapi harus menggunakan macro atau VBA code. Pada contoh ini yang diletakkan kita akan membuat Macro sederhana yang dimasukkan ke dalam Sheet1. Dengancara membesarkan Font sehingga besarnya cell akan ikut berubah. (Tentunya masih banyak cara lainnya !!!).
Kita akan menggunakan Worksheet_SelectionChange(ByVal Target As Range) dan untuk membesarkannya kita gunakan FontSize * 3 misalnya.
Untuk memasukkan ke dalam Sheet1 maka kita gunakan Klik Mouse Kanan -> View Code kemudian ketikan code berikut (kalau bingung download contoh disini termasuk contoh cara lainnya) :
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
UkuranFont = ActiveCell.Font.Size
FontBesar = UkuranFont * 3
Cells.Font.Size = UkuranFont
ActiveCell.Font.Size = FontBesar
End Sub
Selamat mencoba !!
Menggunakan Pivot Untuk Meringkas Data
Berikut satu lagi pertanyaan dari Pak Martono, ST:
Gimana membuat Laporan per tanggal data angsuran yang masuk seperti contoh terlampir
(semua data pada tanggal tersebut muncul dalam baris urut ke bawah)
angsuran | ||||||||||||
no peserta | nama | 1 | 2 | |||||||||
tanggal | jumlah | tanggal | jumlah | |||||||||
56 | beni | 01-03-09 | 1000 | 03-03-09 | 600 | |||||||
36 | aryo | 03-03-09 | 500 | 05-03-09 | 500 | dst …. | ||||||
89 | joko | 05-03-09 | 200 | 06-03-09 | 800 | |||||||
58 | joni | 09-09-09 | 600 | 12-03-09 | 500 | |||||||
58 | jihan | 15-03-09 | 700 | 16-03-09 | 400 | |||||||
45 | cucu | 17-03-09 | 800 | 20-03-09 | 300 | |||||||
78 | dea | 25-03-09 | 400 | |||||||||
89 | mega | 30-03-09 | 500 |
Langkah petama adalah mengatur tabel excel seperti database (lihat Data Diatur dalam file excel)
Kemudian kita gunakan Insert Pivot seperti contoh yang terlampir.
Dan kira-kira begini hasilnya
(more…)
Cari Nilai Tertinggi dan Namanya
Pertanyaan Pak Martono dari Pakisaji – Malang:
Bagaimana mencari nilai tertinggi sekalian dengan namanya ( bisa jadi nilai tertinggi ada dua orang atau lebih) digunakan untuk mencari nilai tertinggi untuk ujian, thank
Berikut solusinya:
Untuk mencari nilai tertinggi bisa digunakan fungsi Max() sedangkan untuk mencari nama bisa digunakan index atau vlookup. Contoh berikut menggunakan fungsi Index(), Match() dan Max() dapat di download disini.
Nama | Nilai | Nilai tertinggi | |||
Achmad | 46 | Dipo dengan nilai 88 | |||
Amir | 21 | ||||
Andi | 42 | dengan rumus | |||
Budi | 60 | INDEX(A2:B11,MATCH(MAX(B2:B11),$B$2:$B$11,0),1)& ” dengan nilai ” &MAX(B2:B11) | |||
Badrun | 86 | ||||
Bakti | 68 | ||||
Dimas | 50 | ||||
Dipo | 88 | ||||
Sujiwo | 69 | ||||
Suharto | 38 |
Menghitung dengan Fungsi FV dan Recieved
Ini adalah pertanyaan dari Deni (Syahroni_MD):
“Tapi bisa minta tolong dijelaskan lebih detail gak tentang fungsi FV.
untuk menghitung nilai uang sekarang tu setara dengan nilai uang 2 tahun kedepan pake rumus apa? apa benar pake rumus FV?”
FV adalah fungsi excel untuk menghitung Nilai masa mendatang dari sebuah investasi periodik, pembayaran konstan dan tingkat bunganya konstan (Returns the future value of an investment based on periodic, constant payments and a constant interest rate.)
Jadi kalau dibayar diawal saja fungsi FV tidak bisa digunakan.
Pivot Table and Examples 2
Pivot Table adalah cara yang cepat dan mudah untuk meringkas sebuah tabel data yang besar.
Berikut contoh lainnya dari tabel yang dapat langsung anda download:
Memilih Directory dng UserForm
Kadang kita perlu mengambil file dari directory tertentu dalam aplikation Excel. Kita dapat menggunakan method GetOpenFileName untuk mendisplay sebuah user dialog yang meminta sebuah file (file name), akan tetapi kita tidak diberi pilihan untuk mendisplay sebuah dialog box that yang menunjukkan hanya directory saja. Dalam tulisan ini saya mencobah menyajikan sebuah fungsi yang diberi nama AmbilDirektori yang akan menampilkan dialog box seperti di bawah ini dan menghasilkan string yang menunjukkan directory yang terpilih. Jika user anda mengklik cancel, maka fungsi ini akan menghasilkan string kosong (empty). Fungsi AmbilDirektori memerlukan satu argument, tapi tidak wajib (optional). Argumen ini adalah variable a string yang akan ditampilkan dalam dialog box. Jika argumen tidak disertakan maka dialog box menampilkan pesan Pilih folder.
AmbilDirektori function
Fungsi AmbilDirektori adalah sebagai berikut. Untuk menggunakannya cukup copykan ke module VBA. Sebagai alternatif ambil contoh disini
Public Type BROWSEINFO hOwner As Long pidlRoot As Long pszDisplayName As String lpszTitle As String ulFlags As Long lpfn As Long lParam As Long iImage As Long End Type ’32-bit API declarations Declare Function SHGetPathFromIDList Lib “shell32.dll” _ Alias “SHGetPathFromIDListA” (ByVal pidl As Long, ByVal pszPath As String) _ As Long Declare Function SHBrowseForFolder Lib “shell32.dll” _ Alias “SHBrowseForFolderA” (lpBrowseInfo As BROWSEINFO) As Long Sub UjiCoba() Dim Msg As String Msg = “Contoh: Pilih lokasi untuk backup.” MsgBox AmbilDirektori(Msg) End Sub Function AmbilDirektori(Optional Msg) As String Dim bInfo As BROWSEINFO Dim path As String Dim r As Long, x As Long, pos As Integer bInfo.pidlRoot = 0& If IsMissing(Msg) Then bInfo.lpszTitle = “Pilih folder.” Else bInfo.lpszTitle = Msg End If bInfo.ulFlags = &H1 x = SHBrowseForFolder(bInfo) path = Space$(512) r = SHGetPathFromIDList(ByVal x, ByVal path) If r Then pos = InStr(path, Chr$(0)) AmbilDirektori = Left(path, pos – 1) Else AmbilDirektori = “” End If End Function
Cara menggunakan fungsi AmbilDirektori
Berikut subrutin sederhana untuk memakai fungsi AmbilDirektori
Sub UjiCoba()
Dim Msg As String
Msg = ” Contoh: Pilih lokasi untuk backup!!”
MsgBox AmbilDirektori(Msg)
End Sub
Pivot Table and Examples
Pivot table adalah cara membuat laporan atau meringkas informasi tabel atau daftar data dalam excel sehingga diperoleh informasi yang ringkas dengan analisis informasi yang diperoleh dari kombinasi kolom data dalam tabel.
Dengan Pivot Table Excel kita dapat menganalisis, meringkas dan memanipulasi data sehingga kita memperoleh informasi untuk mengambil keputusan.
Bagaimana cara membuatnya: lihat Menggunakan Tabel Pivot untuk Meringkas Data
Berikut disajikan beberapa contoh pivot tabel yang dapat langsung anda download: 😎
Tunggu: Contoh selanjutnya
Quick Entry-Entri Cepat -Mouse
Entri data cepat dengan menggunakan Mouse
Copy Data untuk Sel disebelahnya
- Pilih sel yang berisi data yang akan dicopy
- Arahkan mouse pada Fill Handle — kotak hitam kecil disebelah kanan bawah kotak seleksi
- Pada saat pointer berubah menjadi tanda plus hitam, tekan dan tahan tombol kiri mouse, dan tarik, kekiri, ke kanan atau ke beberapa sel .
- Kalau sudah terisi semua, lepas tombol kiri mouse.
Copy isi sel ke sel yang berdekatan — dengan pilihan (options)
- Pilih sel yang berisi data yang akan dicopy
- Arahkan mouse pada Fill Handle — kotak hitam kecil disebelah kanan bawah kotak seleksi
- Pada saat pointer berubah menjadi tanda plus hitam, tekan tombol kanan mouse, dan kekiri, ke kanan atau ke beberapa sel .
- Kalau sudah terisi semua, lepas tombol kanan mouse.
- Dan pilih opsi dari menu shortcut
Recent Comments