Fungsi If pada Excel

Rumus IF kombinasi dengan LEFT, RIGHT, dan MID – Pada postingan sebelumnya saya sudah menjelaskan tentang macam – macam fungsi string dalam Microsoft Excel. Beberapa fungsi dari string tersebut adalah fungsi LEFT, RIGHT, dan MID. Pada postingan sebelumnya juga sudah saya katakan bahwa fungsi string ini memang tidak cukup penting, tapi akan menjadi penting jika dikombinasikan dengan rumus lainnya, salah satunya adalah jika dikombinasikan dengan fungsi IF. Terutama jika untuk mengerjakan kode-kode, kombinasi dua fungsi ini cukuplah penting.

Pada postingan kali ini, saya akan membahas tentang kombinasi IF dengan fungsi LEFT, RIGHT, dan MID. Pada contoh di bawah, saya akan menjabarkan tiga rincian barang dealer sepeda motor yaitu daerah, merk, dan harga barang berdasarkan kode barang di kolom awal. Untuk lebih jelasnya silahkan simak penjelasan berikut ini.

Menjabarkan Jumlah Karakter Pertama Kiri dari Kode Barang  melalui kombinasi Rumus IF dengan rumus LEFT

Rumus IF dan kombinasi dengan rumus LEFT berarti mengambil suatu teks pada cell lain dimulai dari sebelah kiri dan nantinya akan dihadapkan pada kondisi yang berbeda. Secara syntax kombinasi rumu IF dan LEFT adalah sebagai berikut ini.

=IF(LEFT(cell sumber;jumlah karakter)=”hasil’;”kondisi1”;IF(LEFT(cell sumber;jumlah karakter)=”hasil’;”kondisi2”))

Rumus di atas digunakan jika ada dua kondisi berbeda. 

=IF(LEFT(cell sumber;jumlah karakter)=”hasil’;”kondisi1”;IF(LEFT(cell sumber;jumlah karakter)=”hasil’;”kondisi2”;IF(LEFT(cell sumber;jumlah karakter)=”hasil’;”kondisi3”)))

Rumus di atas digunakan untuk tiga kondisi berbeda. 

Anda dapat menambahkan lagi sesuai dengan jumlah kondisi berbeda. Sekarang contohnya adalah sebagai berikut ini. Buka Microsoft Excel pada komputer anda, lalu buatlah data pengiriman sepeda motor sebagai berikut ini.

Untuk mengisi, cell D5 yang berupa daerah pengiriman, masukkan rumus IF gabungan LEFT berikut ini, lalu anda fill ke bawah dan atur text alignnya maka akan tampak seperti gambar di bawah ini. Rumus:

=IF(LEFT(C5;3)="JTE";"Jawa Tengah";IF(LEFT(C5;3)="JTI";"Jawa Timur";IF(LEFT(C5;3)="BLI";"Bali";IF(LEFT(C5;3)="DKI";"DKI Jakarta";IF(LEFT(C5;3)="DIY";"D I Yogyakarta";IF(LEFT(C5;3)="JBA";"Jawa Barat"))))))

Keterangan :

1.     C5 merupakan cell sumber karakter yang akan diambil.

2.     3 merupakan jumlah karakter yang akan diambil.

3.     JTE, JTI, BLI, DKI, dan DIY merupakan hasil dari pengambilan karakter.

4.     Jawa Tengah, Bali, Jawa Timur, DKI Jakarta, dan D I Yogyakarta merupakan kondisi.

Menjabarkan Jumlah Karakter di Tengah Kode Barang  melalui kombinasi Rumus IF dengan rumus MID

Untuk contoh dari kombinasi rumus IF dengan MID ini saya masih akan menggunakan contoh di atas, yaitu memunculkan merk sepeda motor. Secara syntax sendiri, kombinasi rumus ini adalah sebagai berikut:

=IF(MID(cell sumber;posisi karakter;jumlah karakter)=”hasil”;”kondisi1”;IF(MID(cell sumber;posisi karakter;jumlah karakter)=”hasil”;”kondisi2”))

Rumus di atas dapat anda gunakan apabila terdapat dua kondisi berbeda. 

=IF(MID(cell sumber;posisi karakter;jumlah karakter)=”hasil”;”kondisi1”;IF(MID(cell sumber;posisi karakter;jumlah karakter)=”hasil”;”kondisi2”;IF(MID(cell sumber;posisi karakter;jumlah karakter)=”hasil”;”kondisi1”;IF(MID(cell sumber;posisi karakter;jumlah karakter)=”hasil”;”kondisi1”;)))

Sedangkan anda dapat menggunakan rumus di atas ini jika terdapat tiga kondisi berbeda. Anda dapat menambahkan lagi rumusnya tergantung dari jumlah kondisi yang berbeda.

Pada contoh, untuk mengisi data pada cell E5, anda copy rumus berikut ini, lalu anda auto fill ke bawah sampai cell E14 dan atur text alignnya, maka anda akan mendapatkan gambar sebagai berikut ini. Rumus:

=IF(MID(C5;4;1)="V";"Vario";IF(MID(C5;4;1)="M";"Megapro";IF(MID(C5;4;1)="S";"Supra";IF(MID(C5;4;1)="B";"Beat";IF(MID(C5;4;1)="C";"CBR")))))

Keterangan :

1.     C5 merupakan cell sumber karakter.

2.     4 merupakan posisi awal dari karakter yang akan diambil.

3.     1 merupakan jumlah karakter yang akan diambil.

4.     V, M, S, B, dan C merupakan hasil dari pengambilan karakter.

Vario, Megapro, Supra, Beat, dan CBR merupakan kondisi.

Menjabarkan Jumlah Karakter Paling Kanan dari Kode Barang  melalui kombinasi Rumus IF dengan rumus RIGHT

Secara syntax kombinasi dari kedua rumus ini sama dengan syntax dari kombinasi rumus IF dengan rumus LEFT, hanya saja kata LEFT dig anti dengan kata RIGHT. Contoh saya masih menggunakan data di atas, yaitu harga sepeda motor. Untuk mengisi cell F5berdasarkan berdasarkan kode. Masukkan rumus berikut ini lalu tekan enter. Rumus:

=IF(RIGHT(C5;2)="15";15000000;IF(RIGHT(C5;2)="17";17000000;IF(RIGHT(C5;2)="13";13000000;IF(RIGHT(C5;2)="14";14000000;IF(RIGHT(C5;2)="24";24000000;)))))

Keterangan

1.     C5 merupakan cell sumber karakter.

2.     2 merupakan jumlah karakter yang akan diambil.

3.     15, 17, 13, 14, dan 24  merupakan hasil dari pengambilan karakter.

4.     15000000, 17000000, 13000000, 14000000, dan 24000000 merupakan kondisi. Saya sengaja tidak menggunakan tanda petik (“…”) untuk mengapit kondisi. Karena kondisi berupa angka. 

Jika sudah ubah General menjadi Currency pada grup Number di  tab Home. Atur text alignnya dan auto fill ke bawah sampai pada cell F14, maka anda akan melihat gambar seperti di bawah ini. 

Pengolahan data menggunakan Excel PIVOT

Kita sering bekerja dengan bermacam  – macam data, setiap data yang kita miliki nilai dan juga variabel/ indikator. Jumlah variabel yang banyak membutuhkan waktu yang cukup untuk mengolahnya. Oleh karena kebutuhan mengolah data semakin cepat, maka pivot diperlukan untuk mengolah data dengan cepat. Data – data tersebut perlu diolah dengan baik agar memudahkan pembaca (kita dan orang lain) menganalisanya. Salah satu fungsi yang kurang dimanfaatkan adalah Excel pivot. Menurut Wikipedia pivot diartikan sebagai alat bantu mensederhanakan data untuk meggambarkan dalam program lembar kerja(seperti Microsoft Excel, OpenOffice.org Calc and Lotus 1-2-3) . Kali ini akan membahas pengolahan data menggunakan metode pivot di ms excel. Dalam bidang pendidikan metode ini dapat membantu kita untuk menganalisis kebutuhan guru, tingkat pendistribusian guru dan berbagai analisa lain. Excel dipilih karena program ini banyak digunakan orang. Oleh karena itu langkah pertama yang dilakukan adalah membuka data kita yang berada di excel.

Setelah data dibuka seperti gambar di kanan atas, beberapa langkah persiapan yang perlu dilakukan sebelum melakukan mempivot data,

-          Pastikan variabel yang kita miliki terletak dalam 1 baris. Jika nama variabel kita panjang, tidak kita dapat membuat kode tersendiri yang mudah kita pahami,

-          Pastikan tiap data tidak mempuyai kesamaan nama variabel satu dan lainnya,

-          Pastikan tidak ada kolom yang digabungkan misalnya murid laki-laki kelas 1 dan murid perempuan kelas 1 kita gabung menjadi murid kelas 1

Menampilkan Pivot Table

Sebelum dimulai pivot, data yang mau kita olah, disorot terlebih dahulu menggunakan tombol shift pada keyboard kemudian anak panah hingga semua dataya disorot semua. fungsi pivot dapat kita temukan di menu insert paling kiri sebelah kiri insert tabel.

d

Klik kiri pada pivot table  

kemudian akan muncul kotak dialog untuk menentukan sumber data yang akan di pivot, penempatan hasil pivot, seperti di bawah

Jika sudah terisi sesuai keinginan kita, maka klik kiri tombol OK yang terletak dibawah kotak dialog. Sebagai contoh diatas data yang ingin di pivot berada di A9 sampai CH854 lembar “SD” dan hasilnya nanti akan ditampilkan pada lembar yang baru. Sumber data sudah langsung tersedia, hal ini dikarenakan sebelumnya kita sudah mensorot data yang hendak di pivot.

setelah memilih tombol ok, maka excel akan membuat lembar kerja baru (sesuai permintaan kita dalam kotak dialog sebelumnya) dan berisi tampilan seperti diatas.

Kotak Filter                     merupakan kotak tempat mengisi varibel yang membatasi hasil olah data. Batasan tersebut misalnya kabupaten/kota, Tingkat pendidikan, program dampingan, kategori wilayah dan lainnya.

Kotak Coloum Labels   merupakan kotak tempat mengisi variabel yang hendak dijadikan sebagai kolom dalam tabel hasil pivot

Kotak Row Labels         merupakan variabel yang hendak dijadikan sebagai baris dalam tabel hasil pivot.

Kotak Values                   merupakan nilai yang ingin ditampilkan, pada awalnya nilai yang akan ditampilkan merupakan nilai perhitungan data,namun nilai yang ditampilkan dapat sesuai dengan yang dibutuhkan

 

Memasukkan variabel/ Indikator

Fungsi pivot hanya beroperasi dalam kotak hasil pivot. Sehingga saat kita menggeser di luar kotak tempat tampilan fungsi pivot,maka fungsi pivot akan menghilang. Oleh karena itu untuk menjalankan fungsi pivot cell/kursor selalu berada di dalam kotak. Untuk mengisi kotak kolom, baris, nilai dan filter. klik variabel yang diinginkan kemudian bawa ke salah satu indikator yang diinginkan. Untuk mengisinya klik variabel yang ada di kolom variable kemudian bawa ke kotak indikator (nama kolom, nama baris, filter atau nilai).

Gambar diatas menunjukkan keterkaitan letak antara hasil dan variabelnya. Untuk mengeluarkan variabel yang ada dalam satu kotak, kita klik dan bawa keluar dari kotak indikatornya, dengan demikian hasil tampilan tabelpun akan berubah sesuai dengan variabel yang ada. Dalam satu indikator dapat terdiri dari beberapa variabel, cara untuk menambah variabel sama dengan cara memasukan variabel ke dalam indikator yaitu mengklik dan menbawa variabel yang dimaksud ke dalam variabel yang diinginkan.

Contoh

Dalam contoh diatas adalah mengetahui berapa sekolah dasar milik yayasan pengelola pendidikan dan sekolah negeri yang ada di wilayah kota dan desa di kabupaten Biak Numfor, sehingga variabel yang kita gunakan  adalah variabel kota/desa untuk baris, variabel pengelola pendidikan di kotak kolom, variabel nilai kita gunakan nama sekolah sedang untuk membatasi kabupaten biak maka kita gunakan variabel kab/kota.

Dengan demikian akan muncul tabel yang berisi nama-nama yayasan pengelola pendidikan sebagai baris, kategori desa dan kota sebagai kolom, sedangkan nilainya berasal dari nama sekolah. Informasi yang dapat diambil :

ü  Di Kabupaten Biak Numfor terdapat 158 Sekolah dasar,

ü  124 sekolah berada di pedesaan sedangkan di perkotaan terdapat 34 sekolah,

ü  77 sekolah diantarnya sekolah negeri, dan YPK memiliki 73 sekolah yang berada di kabupaten Biak Numfor. Sedangkan yayasan pendidikan lainnya memiliki sekolah seperti di dalam tabel.

Pembatasan dan mengurutkan data (Filter dan sort data)

Pembatasan data diperlukan agar kita bisa lebih spesifik melihat datanya. Selain melalui indikator filter, pembatasan juga dapat dilakukan dengan cara mengklik anak panah ke bawah disamping varbel sehingga muncul beberapa kriteria yang tersedia

Misal pada kota/kabupaten yang ingin kita batasi, klik anak panah kebawah disamping kanan nama variabel, maka akan muncul sebuah kotak yang berisi item data yang tersedia. Ada dua jenis tampilan. Untuk variabel pembatas(filter) tampilannya seperti di gambar tengah, sedangkan untuk variabel yang menjadi baris atau kolom tampilannya seperti gambar samping kanan. Jika tidak terdapat kota untuk memilih disamping item data, klik kotak di samping select multiples item. Setelah muncul klik (tandai) data yang akan digunakan sedang yang tidak digunakan silahkan hilangkan tandanya dengan cara mengklik kotak disamping item datanya. Contoh diatas ketika anak panah ke bawah disamping kanan kab/kota di klik muncul pilihan item data (ALL), BIAK, JAYAPURA, JAYAWIJAYA, MIMIKA dan SORONG, untuk melihat data kabupaten BIAK, klik(tandai) hanya di item BIAK

Pembatasan variabel di kolom atau baris juga terdapat pilihan pengurutan data (sort) menurut abjad A – Z dan sebaliknya Z – A. Ini digunakan untuk mengurutkan nama variabel berdasarkan abjad.

Mengubah nilai variabel pivot

Nilai secara umum yang ditampilkan pivot merupakan nilai penghitungan, sedang data kita membutuhkan adalah jumlahnya. Langkah yang dilakukan adalah letakan kursor pada tampilan nilai kemudian pilih fungsi field setting yang terletak di menu pivot. Atau dapat juga kita temukan fungsi ini dengan mengklik variabel yang berada di kotak value.

Beberapa variasi nilai yang dapat digunakan

Setelah mengklik Value Field Setting maka akan keluar kotak dialog yang berisi beberapa fungsi, kemudian kita sesuaikan dengan nilai yang akan kita tampilkan, kemudian pilih ok.

Sum              : Jumlah

Count           : Hitung

Average       : Rata-rata

Max             : Paling Besar

Min               : Paling Kecil

Product        : Mengkalikan

Cnt Num     : Hitung (numeric)

StdDev(P)    : Standar Deviasi

Var               : Varians

Contoh

Kita ingin mengetahui konsentrasi guru yang ada di pedesaan dan di perkotaan. Pertama angkat keluar variable nama sekolah yang ada di dalam kotak nilai kemudian kita gantikan dengan variable total guru. Karena variable total guru berupa angka dan kita yang ingin mengetahui jumlah keseluruhan guru yang ada di sekolah di pedesaan dan perkotaan, maka klik Field setting sehingga muncul kotak dialog lalu sesuaikan nilainya dengan memilih/klik di pilihan sum (untuk menjumlahkan), dengan demikian nilai yang akan ditampilkan merupakan penjumlahan dari nilai variabel yang ada. Selain nilai yang dapat disesuaikan field setting juga dapat menampilkan nilai tersebut.

Contoh

Kita ingin mengetahui konsentrasi guru yang ada di pedesaan dan di perkotaan. Pertama angkat keluar variable nama sekolah yang ada di dalam kotak nilai kemudian kita gantikan dengan variable total guru. Karena variable total guru berupa angka dan kita yang ingin mengetahui jumlah keseluruhan guru yang ada di sekolah di pedesaan dan perkotaan, maka klik Field setting sehingga muncul kotak dialog lalu sesuaikan nilainya dengan memilih/klik di pilihan sum (untuk menjumlahkan), dengan demikian nilai yang akan ditampilkan merupakan penjumlahan dari nilai variabel yang ada. Selain nilai yang dapat disesuaikan field setting juga dapat menampilkan nilai tersebut.

Normal                     :  Nilai normal yang ada

Difference From        :  Perbedaan nilai variabel yang ada

% Of                        :  Persentase dengan nilai yang kita tentukan

% Difference From    :  Persentase perbedaan dengan nilai variabel yang ada

% of Row                 :  Persentase dari jumlah per baris

% of Column            :  Persentase dari jumlah per kolom

% of Total                 :  Persentase dari jumlah total

Running Total            : menghitung hanya totalnya

Index                        : nilai yang ditampilkan berupa indeks

Setelah kita sesuaikan nilai yang akan kita tampilkan dalam tabel pivot, kita klik ok.

Menyatukan dan memisahkan beberapa variabel

Setelah menentukan nilai variabel, kadang dalam mengolah data yang ada perlu menggabungkan beberapa variabel yang ada, namun sumbernya tetap dapat terlihat. Sebagai contoh dalam data sekolah – sekolah yang non pemerintah dapat  kita kelompokan menjadi sekolah swasta sehingga beberapa yayasan pengelola pendidikan yang ada dapat disatukan dengan nama kelompok Swasta. Atau jumlah murid laki-laki kelas 1 dan perempuan kelas 1 kita gabungkan dengan nama siswa kelas 1.

Pivot menampilkan hal yang memudahkan kita untuk mengolah itu. Langkah pertama yang kita lakukan adalah mensorot nama-nama  varabel yang mau digabungkan, kemudian kita klik kanan, sehingga keluar menu seperti digambar samping kiri. Ada beberapa pilihan yang tersedia, untuk menggabungkan  pilihan Group.

Kelompok yang baru terbentuk akan muncul di atas nama – nama variabel tersebut dengan nama Group1. Nama kelompok ini dapat kita ganti sesuai dengan kebutuhan kita. Sehingga jika kita ingin mengetahui perbedaan sekolah negeri dan swasta tinggal mengklik tanda minus di samping nama Group, sehingga tampilannya seperti dibawah ini, dan sudah dihitungkan secara otomatis jumlah sekolah swasta yang ada.

Untuk mengembalikan variabel –variabel yang sudah dikelompokan menjadi variabel sebelumnya yang dilakukan hanya klik kanan pada nama kelompok kemudian pilih ungroup

Menampilkan Grafik

Data yang ditampilkan dalam bentuk tabel kurang diminati dibandingkan data dalam bentuk grafik. Pivot tidak hanya menampilkan data berupa tabel namun juga dapat berupa grafik. Tabel yang ada dalam bentuk tabel yang telah ada, dapat dibuatkan grafiknya dengan cara mengklik Pivot Chart yang terletak di atas menu PivotTable Tools

Setelah kita pilih pivot chart maka akan keluar kotak dialog  untuk menentukan jenis grafik yang akan digunakan.setelah itu kita pilih ok, sehinga grafik dapat terbentuk sesuai dengan keinginan kita. Sama halnya dengan nilai yang ditampilkan dalam tabel, nilai yang ada dalam grafik akan selalu disesuaikan dengan variabel yang digunakan, jika terjadi perubahan variabel maka variabel dan nilai dalam grafik juga akan disesuaikan.

Rumus

Kolom Field / variabel

Nama rumus

Rumus yang akan dibuat

Insert field untuk mengisi rumus

OK

Selain variabel yang ada dari data, kita juga dapat menambah variabel baru dengan membuat rumus. Untuk membuat rumus, kita dapat mengklik pilihan formula yang ada di menu pivottabel. Setelah diklik, muncul kotak dialog dimana rumus akan dibentuk

Rumus atau variabel baru yang dibuat dapat berasal dari variabel – variabel yang telah ada. Misalnya kita ingin mengetahui jumlah siswa kelas awal (kelas 1) namun variabel yang tersedia adalah siswa laki-laki kelas 1 dan siswa perempuan kelas 1, maka pilih variabel siswa laki-laki kelas 1 yang ada dikolom Fields kemudian lalu klik tombol Insert Field tanda + di keyboard baru pilih variabel siswa perempuan kelas 1 yag ada di kolom Fields lalu klik tombol Insert Field.