Test Kondisi Fungsi IF pada Excel

Excel menyediakan fungsi if untuk melakukan pengujian atau biasa juga disebut dengan test kondisi,fungsi if biasanya digunakan untuk melakukan pengujian dimana ketentuannya dibuat dalam bentuk kalimat. Bentuk dari fungsi if jika hanya dua kondisi adalah:

=if(test_kondisi,nilai_bernar,nilai_salah), 

tetapi jika lebih dari dua kondisi bentuknya bisa digunakan:

=if(text_kondisi,nilai_benar,if(test_kondisi,nilai_benar,nilai_salah)), 

dengan kata lain jika kondisi banyak maka harus diatur nilai salahnya menjadi test kondisi lagi.Sedangkan jika ketentuan sudah dalam bentuk tabel atau sudah disusun berdasarkan kolom atau baris maka excel menyedian fungsi pembacaan tabel, memang tidak mungkin melakukan atau mengerjakannya menggunakan fungsi If tetapi sudah merupakan suatu pekerjaan yang sangat sia-sia karena membutuhkan banyak waktu. contohnya dalam pengerjaan latihan dibawah ini,

untuk mencari nama gerbong, harga tiket, tujuan/jurusan,jam pergi, jenis tiket kalau digunakan menggunakan if maka rumus if yang dipakai akan panjang karena ada 15 kondisi. untuk menyingkatnya digunakanlah fungsi pembacaan tabel dimana rumusnya sebagai berikut:

 =vlookup(Nilai_patokan_pencarian,sumber_tabel,nomor_kolom_yang_dicari,Match)

=Hlookup(Nilai_patokan_pencarian,sumber_tabel,nomor_baris_yang_dicari,Match) 

dimana match bernilai true/1 atau false/0, jka tidak diberikan maka standarnya adalah 1
Untuk contoh Kasus diatas digunakan rumus mencari nama Gerbong: 

=VLOOKUP(B9,$B$25:$E$39,2,0)

mencari harga tiket:

 =VLOOKUP(B9,$B$25:$E$39,3,0) 

mencari tujuan/jurusan :

 =VLOOKUP(B9,$B$25:$E$39,4,0)

jika anda perhatikan di rumus tersebut terdapat $B$25:$E$39 yang maksudnya sama dengan B25:E39 hanya saja jika kamu ketikkan rumus tersebut pada kotak c9 maka rumus tersebut jika dicopykan ke baris dibawahnya maka tidak akan manjur sehingga perlu diberi tanda dollar dimana tanda dollar tersebut menandakan absolut atau tidak akan berubah.
contohnya :

1. Pada kotak c9 anda mengetikkan rumus =$b9*250 maka rumus tersebut jika dikopikan ke kotak d9 maka rumus yang terpoki adalah $b9 sedangkan jika dikopi pada kotak c10 maka hasilnya adalah $b10 karena $ didepan b menandakan bahwa kolom b-nya tidak akan berubah ketika rumus dikopi ketempat lain

2. Pada kotak c9 anda mengetikkan rumus =b$9*250 maka rumus tersebut jika dikopi pada kotak d9 maka rumus tersebut akan berubah menjadi =c$9*250 sedangkan jika dikopi pada kotak c10 maka rumus tersebut akan berubah menjadi =b$9*250
   atau bentuk sederhananya saya gambarkan menggunakan kolom dibawah ini
   rumus sebelumnya rumus dicopi rumus dicopy 

Read more »

Posted in: Ms.Office Exel

Menggunakan fungsi IF, LEN dan REPLACE di excel 2007

Dalam postingan kali ini akan dibahas tentang cara mengubah tanda titik koma menjadi tanda titik di excel 2007. Terkadang disaat melakukan pekerjaan eksport import data di excel data dari software tertentu mempunyai format yang berbeda dengan format data yang diinginkan. Dengan menggunakan microsoft excel dapat dilakukan pengubahan karakter tertentu secara otomatis dengan menggunakan fungsi yang tersedia di excel. Sebelum membaca lebih lanjut postingan ii saya sarankan membaca postingan sebelumnya telah dibahas tentang cara menghitung jumlah karakter di excel dan fungsi IF excel

Agar lebih mudah menerapkan fungsi replace untuk mengganti koma menjadi titik, perlu diketahui terlbih dahulu karakteristik data. Karena karakteristik data sangat berpengaruh dalam proses penggunaan rumus IF , LEN dan REPLACE.

Karakteristik data:

Data yang akan diubah tanda komanya menjadi tanda titik adalah data koordinat yang masih dalam satuan radian. dengan format derajat,menit-detik (bisa dilihat pada tabel di bawah). Secara umum besaran sudut adalah 0 sampai 360 (1 sampai 3 karakter) , kemudian menit ( 2 karakter), detik (2 karakter), tanda koma berada diantara derajat dan menit (1 karakter)

Kisaran variasi karakter adalah  6 karakter untuk sudut yang lebih kecil dari 10 derajat, 7 karakter untuk sudut yang lebih kecil dari 100 derajat, dan 8 karakter untuk sudut yang lebih kecil atau sama dengan 360 derajat.

Berdasarkan karakteristik data di atas, maka dapat dibuat rumus sederhana untuk mengganti koma menjadi titik

1. Buat tabel seperti di bawah

Kolom A diisi dengan data angka koordinat dalam format derajat menit detik

2. Di sel B1 isi dengan formula gabungan IF LEN REPLACE berikut:

=IF(LEN(A1)=8,REPLACE(A1,4,1,"."),IF(LEN(A1)=7,REPLACE(A1,3,1,"."),REPLACE(A1,2,1,".")))

Hasilnya seperti di bawah ini

Catatan :

=IF(LEN(A1)=8,REPLACE(A1,4,1,"."),IF(LEN(A1)=7,REPLACE(A1,3,1,"."),REPLACE(A1,2,1,".")))

Fungsi di atas berarti:

Jika jumlah karakter di sel A1 sama dengan 8 maka ganti karakter ke-4 dengan titik

Jika jumlah karakter di sel A1 sama dengan 7 maka ganti karakter ke-3 dengan titik

Jika jumlah karakter di sel A1 bukan 7 atau 8 maka ganti karakter ke-2 dengan titik

Read more »

Posted in: Ms.Office Exel

Menjumlahkan nilai teks berdasarkan kriteria sebagian teks dengan SUMIF dan COUNTIF

Postingan ini merupakan kelanjutan dari postingan sebelumnya tentang fungsi teks di excel. Dalam postingan sebelumnya dibahas tentang cara memilih sebagian teks yang terdapat dalam sebuah sel. Tulisan ini masih berkaitan erat dengan posting sebelumnya. Dalam pembahasan kali ini akan dibahas cara menghitung nilai dan jumlah teks yang proses perhitungan berdasarka kriteria sebagian teks yang terdapat dalam sel. Beberapa penggunaan SUMIF dan COUNTIF pernah saya bahas disini belajar excel dan belajar excel 2007

Untuk lebih mudahnya bisa lihat contoh di bawah ini:
Diketahui :
Dalam sebuah perpustakaan kawasan tertinggal terdapat beragam buku. Buku tulis ada 5 buah, Buku gambar pinokio ada 8 buah, Buku harian ada 3 buah, Novel remaja ada 2 buah, Novel sejarah 6 buah. (Tentunya di perpustakaan tersebut masih banyak buku lain , silahkan ditambah sendiri untuk pengembangan formula excel, dalam contoh ini hanya tediri dari beberapa buku di atas)

Ditanyakan:
Berapa jumlah buku
Berapa jumlah novel
Berapa banyak jenis buku
Berapa banyak jenis novel

Jika datanya hanya beberapa baris mungkin masih mudah untuk menghitung secara manual, namun bagaimana jika datanya bertambah menjadi ratusan hingga ribuan baris, tentunya perhitungan dengan cara manual masih bisa dilakukan namun lebih efektif jika menggunakan software excel.

Untuk menyelesaikan kasus di atas buat tabel seperti di bawah ini

1. Tabel data

2. Lakukan perhitungan jumlah buku
a. Untuk menghitung jumlah buku, di D11
=SUMIF($A$3:$A$7,"*"&C11&"*",B3:B7)
Formula di atas berarti  jumlahkan semua nilai pada range B3:B7 jika sel pada range A3:A7 memenuhi kriteria  *C11* atau *Buku*
b. Untuk menghitung jumlah novel di D12
=SUMIF($A$3:$A$7,CHAR(42)&C12&CHAR(42),$B$3:$B$7)
Rumus di atas sama pada rumus 2a, namun menggunakan ASCI Code  dengan rumus lakukan penjumlahan nilai pada range B3:B7 jika kriterianya CHAR(42)&C12&CHAR(42) atau sederhananya *Novel*   dimana tanda bintang (*) adalah karakter ke 42 dalam code ASCII

3. Lakukan perhitungan jenis buku
a. Di sel D15 ketik rumus
=COUNTIF($A$3:$A$7,"*"&C11&"*")
b. Di D16 ketik formula
=COUNTIF($A$3:$A$7,"*"&C12&"*")
Rmus pada 3a dan 3b  hampir sama pada rumus 2a dan 2b, namun pada COUNTIF hanya melakukan pencacahan sedangkan pada SUMIF  melakukan penjumlahan berdasarkan kriteria, jika tidak sesuai kriteria akan dibaikan.

Read more »

Posted in: Ms.Office Exel

Cara membuat grafik sinus di excel 2007

Dengan menggunakan microsoft excel , kita dapat dengan mudah membuat grafik sinusoidal, grafik cosinus, grafik tangen dan grafik-grafik dari fungsi trigonometri lainnya. Dalam postingan ini khusus dibahas tentang cara membuat grafik sinus di excel yang mungkin bisa bermanfaat buat teman-teman yang masih pemula.

Untuk membuat grafik sin di excel lakukan prosedur berikut:
1. Buat tabel seperti di bawah ini
Di kolom A isi dengan data sudut, dalam contoh berikut dibuat besaran sudut dengan interval 15 derajat.
Di sel B3 ketik formula =SIN(RADIANS(A3))

2. Blok range A3:B27
Di menu bar pilih Insert - Scatter - Pilih type grafik scatter yang mempunyai garis

3. Hasilnya akan terlihat seperti di bawah ini

File excelnya bisa download di sini.

Read more »

Posted in: Ms.Office Exel

Fungsi IF sederhana di excel 2007

Fungsi IF di microsoft excel 2007 dapat digunakan sebagai formula logika untuk menghitung atau menentukan nilai sebuah pernyataan dimana fungsi IF akan memberikan nilai tertentu jika logika benar atau salah.

Dalam contoh ini kita akan menggunakan fungsi IF untuk menentukan nilai kelulusan siswa,
Jika Nilai Ujian (kolom B) > 30 maka status kelulusan (kolom C), adalah lulus, sebaliknya jika <30 maka  "tidak lulus"

1. Buat tabel seperti di bawah ini
Kode Siswa, Nilai ujian dan status

2.Pada sel C3 ketik formula
=IF(B3>30,"lulus","tidak lulus") kemudian enter
Selanjutnya kopi formula hingga sel C10
Hasilnya akan tampak seperti di bawah ini

Read more »

Posted in: Ms.Office Exel

Istilah-istilah dalam Excel

1.  Cell : merupakan bagian terkecil dari worksheet yng dapat diisi dengan jumlah karakter(max. 255 karakter) isi cell dapat berupa value, formula atau text. Contoh : cell A3, cell D5

2.   Worksheet (lembar Kerja) : merupakan kumpulan dari 256 kolom dan 65536 baris.

3.  Workbook (buku kerja) : merupakan kumpulan dari 256 worksheet (berlabel sheet1 sampai sheet 256)

4.  Range : merupakan sekelompok cell yang akan mendapataksi sama sesuai perintah yang anda jalankan. Pemberian alamat/ address dilakukan mulai dari cell sudut kiri atas sampai cell sudut kanan bawah. Contoh : A4:D6 → range mulai dari cell A4 sampai cell D6

5.   Alamat Relatif : merupakan alamat yang jika dituliskan kedalam bentuk rumus atau fungsi akan berubah jika dicopy ke cell lain.Contoh :
cell berisi formula A5*6 ,B3 dicopy ke C5 formula pada C5 berubah menjadi B8*6

6.  Alamat Semi Absolut : merupakan alamat yang dituliskan dengan tanda $ didepan baris atau kolom sehingga nilai tidak akan berubah.
Contoh : Cell B1 berisi formula $A1*7,B1 dicopy kan ke D5 formula pada D5 menjadi $A5*7

7. Alamat Absolut : merupakan alamat yang dituliskan dengan tanda $ didepan baris dan kolom.tekan tombol F4 untuk menghasilkan alamat absolut pada formula bar. Contoh : cell B1 berisi formula $A$1&5,B1 dicopy kan ke C3 formula pada C3 menjadi $A$1*5

8.   Name Box : menunjukkan pada cell/ range yang aktif saat itu. Anda dapat juga membuat nama range melalui kotak nama disebelah kiri formula bar.
Contoh : Holla nama lain range A5:G7

Menulis Rumus

1.Operasi Logika

Dibawah ini terdapat operasi logika anda dapat menggunakan operasi perbandingan dengan lambang yang digunakan pada Excel dan fungsinya sebagai berikut :

a. =      : sama dengan

b. >      : lebih besar dari

c. <      : lebih kecil dari d. >= : lebih besar atau sama dengan

e. <=    : lebih kecil atau sama dengan

f. <>    : tidak sama dengan

2. Menggunakan Fungsi

Fungsi sebenarnya adalah rumus yang sudah ada disediakan oleh Excel 2003, yang akan membantu dalam proses perhitungan. Kita tinggal memanfaatkan sesuai dengan kebutuhan. Umumnya penulisan Fungsi harus dilengkapi dengan argumen, baik erupa angka, label, rumus, alamat sel atau range. Argumen ini harus ditulis dengan diapit tanda kurung ().

A.Fungsi Logika

Fungsi logika adalah fungsi yang digunakan untuk menyelesaikan perhitungan-perhitungan yang berhubungan dengan pengambilan keputusan fungsi logika yang digunakan adalah :

1.   Fungsi If :
Bentuk penulisannya : =If(kondisi,nilai jika benar,nilai jika salah)
Contoh : =If(A5<17,”anak-anak”,”dewasa”)>30,”panas”,if(A5>0,”hangat”,”dingin”))

2.   Fungsi String
Fungsi string berfungsi untuk mengubah isi text numeric menjadi bilangan
Fungsi VALUE :
digunakan untuk merubah nilai value menjadi nilai text, penulisannya :
=VALUE(text)

3.   Fungsi FIND :
digunakan untuk menghasilkan posisi substring dari sebuah string atau suatu nomor yang dicari,penulisannya : =FIND(cari text,pada text,mulai nomor)

4.   Fungsi MID
digunakan untu mengambil karakter tertentu dari sederet karakter, penulisannya : =MID(text,posisi awal,jumlah karakter)

5.   Fungsi LEFT atau RIGHT :
digunakan untuk mengambil substring sebelah kiri atau kanan string, penulisannya
=LEFT atau =RIGHT(text,jumlah karakter)

6.   Fungsi REPLACE :
digunakan untuk menggantikan substring dengan substring lain dalam sebuah string (sederetan karakter atau karakter), penulisannya :
=REPLACE(text lama,nomor awal,jumlah karakter,text baru)

7.   Fungsi CONCATENATE :
digunakan untuk menggabungkan string menjadi satu kalimat maksimal 30 string, penulisannya :
=CONCATENATE(text1,text2,…)

B. Fungsi Tabel :

Fungsi HLOOKUP dan VLOOKUP digunakan untuk membaca tabel secara vertikal (VLOOKUP) atau secara horizontal (HLOOKUP), penulisanya :
=HLOOKUP(lookup_value,table_array,row_index_num,…)
=VLOOKUP(lookup_value,table_array,row_index_num,…)

Download Contoh File 

Terima Kasih atas kunjungan anda....
copas alamat diatas dan download
Terima Kasih

Read more »

Posted in: Ms.Office Exel

Tutorial Cara Membuat pengolahan data penelitian dengan Microsoft Excel

Membuat pengolahan data penelitian atau data administrative tidak selalu dengan program-program statistik, ternyata Microsoft excel juga mampu melakukan hal tersebut.

Demikianlah gambaran sebagian orang tentang kegunaan dari program Microsoft Excel dalam pengolahan data statistik. Untuk itu pada tulisan ini, 6laskar-pelangi akan mengajak anda untuk bermain statistik bersama Microsoft Excel 2010.

Statistic adalah ilmu dan seni atau teknik untuk mengumpulkan data, menyajikan data, mengumpulkan data dan mengambil kesimpulan berdasarkan data yang berhasil dihimpun.

Cara Mengaktifkan Referensi Tools Statistik Pada Excel 2010.

Microsoft Excel 2010 sudah menyediakan dua fasilitas untuk mengolah data statistic, yaitu dengan memanfaatkan fungsi-fungsi statistik yang ada, dan perintah analisis yang merupakan perintah tambahan (add-in) sehingga tidak ditampilkan pada menu utama Microsoft Excel 2010.

Sebelum dapat menggunakan perintah data analisis, langkah pertama yang harus dilakukan adlah mengaktifkan referensi tools yang sudah disediakan oleh Microsoft Excel , di mana langkah-langkahnya sebagai berikut :

1. Aktifkan program Microsoft Excel hingga terdapat worksheet kosong.

2. Klik Office button Microsoft Excel yang berada di ujung kiri atas jendela utama.

3. Klik Menu Options yang berada dalam kumpulan menu vertical, mulai dari menu back sampai exit. 

4. Sebuah kotak dialog Excel Options ditampilkan, dan klik menu add-ins yang ada di jendela sebelah Kiri

5. Pilih dan Klik Analysis tool pack pada daftar aplikasi add-ins yang tidak aktif. 

6. Klik tombol Go, dan sebuah kotak dialog add-ins ditampilkan.  

7. Berikan tanda check (lihat gambar) pada kotak check analysis tool Pack

8. Terakhir, klik tombol OK dan tunggu beberapa saat sampai proses instalasi

Cara melakukan analysis stastik deskriptif dengan Excel bisa dilakukan dengan beberapa langkah yang sebenarnya siapa saja bisa melakukannya.  Beberapa langkah yang dapat dilakukan dalam standar proses analysis adalah sebagai berikut :

1.Dari menu utama MS.Excel, klik menu Data (lihat  gambar)

 

2. Klik menu Data Analysis pada grup analysis dan sebuah kotak dialog data analysis ditampilkan (lihat gambar).

Baca Juga " Cara Membuat Histogram" 

Read more »

Posted in: Ms.Office Exel