Kadang kala anda perlu untuk membulatkan suatu hasil tidak mengikuti ketentuan matematika umum. Dengan fungsi pembulatan Round(angka,desimal) maka hasil yang didapatkan adalah seperti dalam contoh baris 3 s/d 4. Jika anda ingin mendapatkan pembulatan lainnya maka contoh paa baris 6 s/d 14 dapat dipergunakan sebagai acuan. Untuk pembulatan dengan angka 5 maka anda perlu membagi angka asli dengan angka 5 kemudian dibulatkan ke 0 desimal dan dikalikan lagi dengan angka 5. Demikian pula jika anda ingin mendapatkan pembulatan ke angka 0.5 maupun ke angka 0.25.

Oct

Filter dengan Kriteria

Posted by: aurino in Ms Excel

Membuat Databasenya

Contoh yng digunakan dalam artikel ini dapat pada gambar 1. Databasenya ada dikolom A:D dan range kriteria mulai dari sel F1.

Catatan: Jika anda memakai rumus sebagai kriteria, maka kolom judul dikosongkan, atau gunakan judul yang tidak sama dengan judul tabel manapun.

Gambar 1

Meng-Extract lima record tertinggi

F1: Judul Kriteria – Biarkan Kosong
F2: Kriteria: =D2>=LARGE($D$2:$D$28,5)

Fungsi LARGE akan menghasilkan lima nilai tertinggi dari range D2:D28.
Referensi absolut diberlakukan untuk range — $D$2:$D$28.

Sel D2, sel pertama dalam kolom total, adalah sebagai pembanding awal. Referensi relatif dipergunakan untuk sel D2 ini. Baris-baris dengan total yang lebih besar dan sama dengan lima angka terbesar akan lolos terhadap filter ini. (Catatan: Jika ada yang sama maka hasilnya akan lebih dari lima data)

Gambar 2

Filter untuk data yang sama dalam dua kolom

F1: Judul Kriteria – Biarkan Kosong
F2: kriteria: =C2=D2

Untuk setiap record, tanggal Order dibandingkan dengan tanggal Pengiriman. Referensi sel keduannya relatif — C2 and D2. Baris tanggal yang sama akan lolos dari filter ini.

Filter untuk data yang tidak sama dari dua kolom

F1: Judul Kriteria – Biarkan Kosong
F2: kriteria: =C2<>D2

Untuk setiap record, jumlah Invoice dibandingkan dengan jumlah Pembayaran. Dalam filter ini dipergunakan referensi relatif — C2 dan D2. Baris-baris yang jumlahnya tidak sama akan ditampilkan.

Catatan: Anda dapat menggunakan judul kolom sebagai rumus kriteria, sebagai ganti dari referensi sel. Formula akan menampilkan #NAME? atau #VALUE!, akan tetapi filter ini akan berfungsi dengan benar. Sebagai contoh:

F2: Kriteria: =Invoice<>Paid

Temukan angka-angka dari sel yang berisi angka (nilai)

F1: Judul Kriteria – Biarkan Kosong

F2: Kriteria: =ISNUMBER(FIND(”8″,C2))

Fungsi FIND akan mencari string “8″ dalam sel C2.
Jika ditemukan angka tersebut, naja hasilnya aalah lokasi yang mengandung angka 8. Jika tidak ditemukan maka hasilnya adalah Error.

Filter Baris-baris dengan sel yang Kosong

F1: Judul Kriteria – Biarkan Kosong
F2: Kriteria: =C2=”"

Untuk setiap record, nilai dalam kolom C akan diperiksa. Jika ada yang kosong, maka rekord akan lolos dari filter.

Filter Item dalam sebuah Daftar (List)

Kolom H: Masukkan daftar nama item yang akan dicari
F1: Judul Kriteria – Biarkan Kosong
F2: Kriteria: =COUNTIF(H:H,C2)

Untuk setiap record, nilai dalam kolom C akan diperiksa. Jika ditemukan data yang sesuai dengan kolom H, maka record lolos dari filter.

Untuk File yang dipergunakan dapat di ambil dari workbook ini, klik disini.

Diterjemahkan dari Excel Tips http://www.contextures.com/

No Comments

Sep

Fungsi Excel Text, Abs dan number atau date formats

Posted by: aurino in Ms Excel

Fungsi TEXT() dan ‘&’ sangat berguna untuk memasukkan nilai sel numeric ke dalam kalimat yang dapat lebih mudah dipahami oleh pembaca worksheet anda. Anda dapat membuat penyataan dalam bentuk kalimat yang merupakan gabungan antara kata-kata dan angka/nilai. Sebagai contoh. “Gaji seluruh karyawan untuk bulan April adalah sebesar Rp. 1.200.000.000“).

Simbol ‘&’ adalah operator yang dapat dipergunakan untuk menggabungkan (atau menyambungkan) dua data string atau beberapa kata. Sebagai contoh: =”Hal ini mencerminkan kunjungannya ” & D2 jika sel D2 berisi “Kurang Memuaskan” atau “Memuaskani”. Untuk membantu memformat data angka dan tanggal ke dalam sebuah kalimat maka anda memerlukan bantuan fungsi TEXT agar formatnya sesuai.

Fungsi TEXT mengubah angka (number) ke Teks dan memformat angka tersebut sesuai dengan yang anda inginkan. Sintaksnya adalah: =TEXT(value, “FormatCode”). Untuk dapat mengetahui format apa saja yang bisa diterapkan maka anda harus memahami FormatCodes. Beberapa format code dapat dilihat pada contoh di bawah ini, sedangkan yang lainnya anda dapat melihat di help Excel dan cari ‘Formatting numbers : Custom formats‘.

Fungsi ABS mengubah nilai negatif menjadi positive dan sintaksnya =ABS(value)

Sel E11	=G4&” melakukan kunjungan ke “&ABS(F9) &” konsumen “&IF(F7>F8,”lebih sedikit “,IF(F7=F8,”sama dengan “,”lebih banyak”))&”dari rencana”
Sel E12	=”Hal ini mencerminkan kunjungannya ” & IF(F9<0,”>
Sel E13	=”Bulan Laporan “&TEXT(G3,”mmm yyyy”)

Beberapa format untuk Fungsi TEXT()

4 Comments

Sep

Excel Chart Dinamis dengan Pilihan Data

Posted by: aurino in Ms Excel

Pembuatan grafik (Chart) excel secara dinamis dapat dilakukan dengan tanpa harus menggunakan VBA excel. Anda dapat menggunakan formula vector untuk membuat grafik yang dinamis. Data yang diperoleh dapat diubah-ubah sesuai dengan menggunakan data matrik. Sebagai contoh, misalnya pada sel B6:B9 you anda memiliki nama mahasiswa. Beri Name Range NamaMahasiswa . Kemudian, pada sel C6:H9, masukkan angkanya . Beri Range Name NilaiMahasiswa. Kemudian pada sel dengan nama mahasiswa dan buat list-type validation dengan referensi namamahasiswa. Selanjutnya, pada range B16:G16 beri nama ranger NilaiSiswa, dan masukkan formula: =OFFSET(NilaiMahasiswa,MATCH(Mahasiswa,NamaMahasiswa,0)-1,0,COLUMNS(NilaiMahasiswa)), jangan lupa tekan CTRL-SHIFT-ENTER bukan hanya ENTER setelah memasukkan Formula. dan Akhirnya, Buat chart yang datanya berasal dari range NilaiSiswa.

Dengan demikian, jika anda mengubah Mahasiwa, maka otomatis grafik./chart akan berubah sesuai dengan yang bersangkutan. Contohnya dapat di download Grafik-Berdasarkan-Pilihan

No Comments

Sep

Formula Array

Posted by: aurino in Ms Excel

Formula Array merupakan tool yang sangat berguna dari Excel. Sebuah formula array adalah formula yang bekerja dengan array, atau deret data. Ada dua kelompok formulas array: pertama yang berhubungan dengan array atau deret data dan menjumlahkan, biasanya dengan menggunakan SUM, AVERAGE, atau COUNT, dan menghasilkan nilai tunggal dalam sebuah sell. Hasil dari formula array adalah nilai tunggal. Yang kedua adalah formula array yang menghasilkan dua sel atau lebih. formula array jenis ini akan menghasilkan nilai array.

Formula Array dengan Hasil Tunggal

Sebagai contoh, dalam bentuk sederhananya, formula =ROW(A1:A10) menghasilkan angaka 1, yang merupakan nomor baris sel pertama dalam range A1:A10. Akan tetapi, jika formula tsb dimasukkan sebagai formula array, maka akan menghasilkan sederetan angka, yang masing-masing merupakan nomor baris dari range A1:A10, yaitu {1, 2, 3, 4, 5, 6, 7, 8, 9, 10}. (Dalam notasi standar, array ditulis dalam kurung kurawal { }.) Pada saat menggunakan formula array, maka anda biasanya menggunakan fungsi-fungsi seperti SUM atau COUNT untuk menjumlahkan array menjadi angka tunggal. Misalnya kita menggunakan formula =SUM(ROW(A1:A10)) yang dimasukkan secara normal, maka hasilnya adalah 1. Hal ini disebabkan bahwa dalam moda normal, ROW(A1:A10) menghasilkan angka tunggal, 1, dan oleh karena itu hasil SUM hanya menjumlahkan satu angka saja. Akan tetapi, jika formula ini dimasukkan sebagai formula array, maka ROW(A1:A10) akan menghasilkan deret nomor baris dan kemudian menjumlahkannya dengan SUM, shg menghasilkanf 55 ( = 1 + 2 + 3 + … + 10).

Membuat/memasukkan FORMULA ARRAY: untuk memasukkan formula sebagai formula array, maka ketikan formula dalam sel dan kemudian tekan CTRL SHIFT and ENTER bersama-sama bukan hanya ENTER. Anda dapat melakukan hal ini secara langsung atau dengan cara meng-edit (tekan F2) formula yang telah dimasukkan. Jika anda melakukan dengan benar, maka Excel akan menampilkan formula dalam kurung kurawal { }. Jangan mengetik sendiri kurung kurawal ini — Excel akan otomatis menampilkannya. Jika anda mengabaikan langkah memasukan formula denganCTRL SHIFT ENTER, maka formula dapat menghasilkan error #VALUE atau menghasilkan perhitungan yang salah.

Membuat Formula Array dengan menggunakan Array Data

Fungsi IF function dapat dipergunakan dalam formula array untukk menguji hasil uji beberapa sel yang dilakukan satu kali. Sebagai contoh, anda menghitung rata-rata(average) nilai dalam range A1:A5 tetapi tidak memasukkan angka yang lebih kecil dan sama dengan nol. Maka rumus yang anda gunakan adalah AVERAGE yaitu:

=AVERAGE(IF(A1:A5>0,A1:A5,FALSE))

Formula akan bekerja dengan menguji apakah masing-masing sel dalam A1:A5 > 0. This returns an array of Boolean values such as {TRUE, TRUE, FALSE, FALSE, TRUE}.

Formulas Array umum digunakan dalam menghitung atau menjumlahkan sel dengan menggunakan kriteria ganda – beberapa kriteria:

Misalnya tabel di atas. Untuk menghitung jumlah mesin Fax yang dijual Taufik, maka anda dapat menggunakan formula array berikut:

=SUM((A2:A10=”Fax”)*(B2:B10=”Taufik”)*(C2:C10))

Ingat selalu menekan CTRL-SHIFT-ENTER ….. bukan hanya ENTER saja.

Logical Operations With Array Formulas

In addition to the logical AND operation using multiplication shown above, other logical operations can be performed arithmetically.

1 Comment

Sep

Fungsi Indirect Excel

Posted by: aurino in Ms Excel, Tips

Dalam Excel, jika anda ingin menggunakan isi sel sebagai referensi (reference) untuk formula di sel lainnya maka fungsi Indirect adalah jawabannya. Fungsi ini menghasilkan referensi sel berdasarkan pada isi sel yang berbentuk string.

Sintaks Indirect adalah sebagi berikut:

Indirect( string_reference, ref_style )

string_reference adalah referensi sel dalam bentuk teks, misalnya B2, A25, IV23 atau R1C2.

ref_style adalah pilihan bentuk referensi yang berisi nilai TRUE atau FALSE. TRUE jika string_reference akan diintepretasikan sebagai bentuk
referensi
A1. FALSE menunjukkan bahwa string_reference dalam bentuk referensi R1C1. Jika pilihan ini dikosongkan maka, fungsi Indirect akan menganggap bahwan string_reference sebagai bentuk A1.

Contoh berikut berlaku untuk Excel 2007, Excel 2003, Excel XP, Excel 2000

Let’s take a look at an example:

Dengan menggunakan spreadsheet Excel di atas, maka:

Formula	Hasil
=Indirect(”$B$4″)	menghasilkan angka 3
=Indirect(”A5″)	menghasilkan 10570
=Indirect(”A5″, TRUE)	menghasilkan 10570
=Indirect(”R2C3″, FALSE)	Menghasilkan $3.50

Contoh penerapan fungsi INDIRECT lebih lanjut adalah dengan menggabungkan dengan fungsi-fungsi lainnya. Tentunya hal ini dapat diperoleh dengan nilai string yang dapat dibuat dengan menggunakan konstanta string dan nilai sel lainnya dalam sebuah formula, dan digabungkan dengan menggunakan operator penyambungan (concatenate) teks (&). Sebagai contoh, misal anda memiliki formula sederhana sbb:

=SUM(A5:A10)

Yang menjumlahkan nilai dalam range A5:A10. Akan tetapi, misalnya anda ingin dapat menentukan range baris yang dijumlahkan segera tanpa mengubah formula, maka dengan fungsi INDIRECT anda dapat melakukan hal ini. Misalnya anda meletakkan awal baris pada sel B1, dan akhir baris di C1. Maka, Formula anda menjadi

=SUM(INDIRECT(”A”&B1&”:A”&C1))

Argumen dalam fungsi INDIRECT anda adalah

“A”&B1&”:A”&C1

Jika B1 berisi nilai 5 dan C1 berisi nilai 10, maka argumen tersebut di atas this akan menghasilkanstring “A5:A10″. Fungsi INDIRECT mengubah string tersebut menjadi range reference sebenarnya, yang selanjutnya akan dimasukkan sebagai referensi bagi fungsi SUM.

Fitur yang sangat berguna lainnya dari fungsi INDIRECT disebabkan oleh perlakuan fungsi ini yang menggunakan argumen string, sehingga anda dapat menggunakan dengan referensi sel yang tidak berubah jika anda ingin menginsert dan mendelete baris. Biasanya, Excel akan mengubah referensi sel (cell references) pada saat anda meng- Insert atau Men- Delete baris atau kolom, bahkan jika anda menggunakan Referensi Absolut. Jika anda memiliki formula =SUM($A$1:$A$10), kemudian anda meng-Insert baris pada baris ke 5, maka Excel akan mengubah formulanya menjadi =SUM($A$1:$A$11). Hal ini tidak akan terjadi jika anda menggunakan fungsi INDIRECT function sebagai referensi sel/range:

=SUM(INDIRECT(”A1:A10″))

Karena Excel menganggap bahwa “A1:A10″ sebagai text string bukannya sebagai range reference, maka referensi ini tidak akan berubah jika baris atau kolom di-delete atau di-insert.

Fitur ini juga sangat penting jika kita menggunakannya dengan array formulas. Seringkali, formula array menggunakan fungsi ROW(), yaitu fungsi yang menghasilkan deret (array) angka-angka. Sebagai contoh, formula berikut akan menghasilkan rata-rata dari 10 angka terbesar dalam sebuah range A1:A60 :

=AVERAGE(LARGE(A1:A60,ROW(1:10)))

Akan tetapi. Jika anda menambahkan baris dengan meng-insert diantara baris 1 dan 10, maka Excel akan mengubah formula menjadi

=AVERAGE(LARGE(A1:A60,ROW(1:11)))

Tentunya hasil formula ini menjadi salah karena akan menghasilkan rata-rata 11 angka terbesar. Jika kita menggunakan fungsi ROW() dengan argumen string, maka Excel tidak akan mengubah referensi tersebut, jadi formula akan tetap menghasilkan perhitungan yang benar, walaupun ada baris yang di-insert atau di-delete.

=AVERAGE(LARGE(A1:A60,ROW(INDIRECT(”1:10″))))

Anda dapat pula menggunakan fungsi INDIRECT bersama dengan fungsi ADDRESS. Fungsi ADDRESS menggunakan angka baris dan kolom untuk membuat alamat sel sebagai string. Sebagai contoh, formula =ADDRESS(5,6) menghasilkan $F$5, karena $F$5 adalah baris ke 5 dari kolom ke 6. Anda dapat memasukkan formula ini ke INDIRECT untuk mendapatkan nilai F5. Sebagai contoh, =INDIRECT(ADDRESS(5,6)) .

Memang kelihatannya fungsi-fungsi diatas biasa-biasa saja, namun dalam kenyataannya fungsi diatas akan sangat membantu dalam pengembangan formula yang lebih rumit.

2 Comments

Sep

Bentuk Referensi dalam Excel

Posted by: aurino in Ms Excel

Bentuk Referensi (Reference Style)	Artinya
$A$1	Baik Referensi Kolom (column) maupun baris (row) adalah tetap. Keduanya tidak akan berubah dalam proses copy maupun fill.
$A1	Hanya kolom yang merupakan referensi tetap. Pada saat di-copy atau fill akan tetap, sedangkan barisnya akan berubah.
A$1	Kebalikan dari yang di atas, hanya barisnya yang tetap, sedangkan kolomnya akan berubah pada saat proses pengcopy-an dan fill.

1 Comment

Sep

Jadwal Angsuran Sederhana dng Excel

Posted by: aurino in Finance, Ms Excel

Jadwal Amortisasi Sederhana

Tabel yang menunjukkan saldo pokok dan bunga dalam setiap angsuran; pada tabel akan terlihat penurunan saldo pokok pinjaman dalam setiap pembayaran angsuran.

Download File xls untuk amortisasi-sederhana

1 Comment

Sep

Fungsi Membalik kalimat atau kata

Posted by: aurino in Ms Excel

Ini mungkin fungsi Iseng atau supaya orang tidak bisa membaca … Karena kita membalik tulisan. Caranya adalah:

Klik Alt+F11 untuk membuka Visual Basic Editor (VBE).
Dari menu Insert , pilih Module (untuk menambah sebuah module).
Masukkan kode-kode berikut dalam module:

Function BalikText(text) As String

 Dim PanjangText As Integer
 Dim i As Integer
 PanjangText = Len(text)
 For i = PanjangText To 1 Step -1
  BalikText = BalikText & Mid(text, i, 1)
 Next i

End Function

Silahkan coba sendiri yaitu =BalikText(alamat sel yang ada textnya) … dan ketawa sendiri

my world

Senin, 02 Maret 2009

Memasukkan Tabel MS Excel ke Wordpress

artikel ms.excel

Filter dengan Kriteria

Fungsi Excel Text, Abs dan number atau date formats

Excel Chart Dinamis dengan Pilihan Data

Formula Array

Fungsi Indirect Excel

Bentuk Referensi dalam Excel

Jadwal Angsuran Sederhana dng Excel

Fungsi Membalik kalimat atau kata

Senin, 08 Desember 2008

my SchooL

boEkoE tAmoE

Pengikut

Arsip Blog

Mengenai Saya

tEmAnkOe