PELATIHAN SOFTWARE EXCEL

Keynote Speaker:

ASISTEN LABORATORIUM

STATISTIK DAN MANAJEMEN MUTU

DIVISI RISET DAN TEKNOLOGI

HMTI FT-UH

2019

PELATIHAN EXCEL

1. Fungsi count

COUNT : Menghitung jumlah sel yang terisi data angka.

COUNTA : Menghitung jumlah sel yang terisi data angka maupun teks.

COUNTIF : Menghitung jumlah sel yang terisi data dan memenuhi suatu kriteria.

COUNTIFS : Menghitung jumlah sel yang terisi data dengan pengujian kriteria

bertingkat atau lebih dari satu kriteria.

COUNTBLANK : Menghitung jumlah sel yang tidak terisi data.

(sumber: https://www.advernesia.com/blog/microsoft-excel/rumus-excel-countif-countifs-

counta-dan-countblank/)

a. Membuka Microsoft excel.

b. Menginput data.

c. Membuat pivot table usia, dengan memblok kolom usia (C).

d. Pilih tab insert, lalu pilih pivot table.

e. Pilih existing worksheet, lalu pilih lokasi sel yang diinginkan.

f. Centang ‘usia’.

g. Pindahkan ‘sum of usia’ pada values ke rows yang berada pada pivot table fields.

h. Memblok semua angka pada row labels. Kemudian menduplikatnya pada sel disebelahnya

dengan ctrl+c. Tambahkan label usia. Kemudian menghapus pivot table tadi dengan

menekan delete pada keyboard.

i. Tambahkan kolom ‘jumlah’ di samping kanan kolom ‘usia’, dilanjutkan dengan kolom

‘count’, dan ‘count blank’. Tambahkan kolom ‘no’ di samping kiri kolom ‘usia’.

j. Untuk menggunakan fungsi COUNT, pilih sel dibawah label ‘count’. Kemudian

menginput formula “=COUNT(value1, value2,…)”.

k. Untuk menggunakan fungsi COUNTIF, pilih sel di bawah label ‘jumlah’ kemudian

menginput formula “=COUNTIF(range,criteria)”.

l. Mengulang langkah pada poin k untuk kriteria lainnya dari kolom usia.

m. Untuk menggunakan fungsi COUNTBLANK, pilih sel di bawah label ‘count blank’

kemudian menginput formula “=COUNTBLANK(range)”.

n. Selanjutnya, untuk menggunakan fungsi COUNTA dan COUNTIFS, perlu ditambahkan

kolom baru berisi data berbentuk teks. Yaitu dengan membuat pivot table pekerjaan,

dengan memblok kolom pekerjaan (B) terlebih dahulu.

o. Pilih tab insert, lalu pilih pivot table.

p. Pilih existing worksheet, lalu pilih lokasi sel yang diinginkan.

q. Centang ‘pekerjaan’.

r. Memblok semua teks pada row labels. Kemudian menduplikatnya pada sel lain dengan

ctrl+c. Tambahkan label pekerjaan. Kemudian menghapus pivot table tadi dengan menekan

delete pada keyboard.

s. Tambahkan kolom ‘jumlah’ di samping kanan kolom ‘pekerjaan’, dilanjutkan dengan

kolom ‘count a’ dan ‘count ifs’.

t. Untuk menggunakan fungsi COUNTA, pilih sel di bawah label ‘count a’ kemudian

menginput formula “=COUNTBLANK(value1, value2, …)”.

u. Untuk menggunakan fungsi COUNTIFS, pilih sel di bawah label ‘count ifs’ kemudian

menginput formula “=COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2,

…)”.

v. Untuk menggunakan fungsi COUNTIF, pilih sel di bawah label ‘jumlah’ kemudian

menginput formula “=COUNTIF(range,criteria)”.

w. Mengulang langkah pada poin v untuk kriteria lainnya dari kolom pekerjaan.

2. Buat diagram

a. Diagram batang

1) Membuka Microsoft excel.

2) Menginput data. Dalam hal ini, data yang digunakan adalah data yang digunakan pada

fungsi count. Yaitu data usia beserta jumlahnya.

3) Pilih tab insert. Kemudian pilih ‘insert column or bar chart’.

4) Kemudian pilih ‘clustered column’.

5) Klik kanan pada gambar grafik yang polos, lalu pilih ‘select data’.

6) Pilih ‘add’ pada legend entries.

7) Mengisi series name dan memilih area sel ‘jumlah’. Pemilihan ini untuk nilai yang

tertera pada sumbu y. Lalu pilih ok.

8) Pilih ‘edit’ pada horizontal axis labels.

9) Memilih area sel ‘usia’. Pemilihan ini untuk nilai yang tertera pada sumbu x. Lalu pilih

ok.

10) Pilih ok.

11) Kemudian akan dihasilkan diagram berikut.

b. Diagram lingkaran

1) Membuka Microsoft excel.

2) Menginput data. Dalam hal ini, data yang digunakan adalah data yang digunakan pada

fungsi count. Yaitu data pekerjaan beserta jumlahnya.

3) Pilih tab insert. Kemudian pilih ‘insert pie or doughnut chart’.

4) Kemudian pilih ‘pie’.

5) Klik kanan pada gambar grafik yang polos, lalu pilih ‘select data’.

6) Pilih ‘add’ pada legend entries.

7) Mengisi series name dan memilih area sel ‘jumlah’. Pemilihan ini untuk nilai yang

tertera pada sumbu y. Lalu pilih ok.

8) Pilih ‘edit’ pada horizontal axis labels.

9) Memilih area sel ‘pekerjaan’. Pemilihan ini untuk nilai yang tertera pada sumbu x. Lalu

pilih ok.

10) Pilih ok.

11) Kemudian akan dihasilkan diagram berikut.

c. Diagram garis

1) Membuka Microsoft excel.

2) Menginput data. Dalam hal ini, data yang digunakan adalah data yang digunakan pada

fungsi count. Yaitu data usia beserta jumlahnya.

3) Pilih tab insert. Kemudian pilih ‘insert line or area chart’.

4) Kemudian pilih ‘line’.

5) Klik kanan pada gambar grafik yang polos, lalu pilih ‘select data’.

6) Pilih ‘add’ pada legend entries.

7) Mengisi series name dan memilih area sel ‘jumlah’. Pemilihan ini untuk nilai yang

tertera pada sumbu y. Lalu pilih ok.

8) Pilih ‘edit’ pada horizontal axis labels.

9) Memilih area sel ‘usia’. Pemilihan ini untuk nilai yang tertera pada sumbu x. Lalu pilih

ok.

10) Pilih ok.

11) Kemudian akan dihasilkan diagram berikut.

d. Histogram

Untuk membuat histogram, menggunakan data usia.

n = 130 min = 16 max = 66

jangkauan = max – min = 66 – 16 = 50

k = 1 + 3,3 log (n) p = jangkauan/k

k = 1 + 3,3 log 130 p = 50/8

k = 7,963 8 p = 6,25 7

16 – 22, 23 – 29, 30 – 36, 37 – 43, 44 – 50, 51 – 57, 58 – 64, 65 – 71

1) Membuka Microsoft excel.

2) Menginput data usia dan batas kelas atas usia.

3) Install add-ins analysis toolpak. Buka file lalu options.

4) Pilih add-ins.

5) Pilih pada manage: ‘excel add-ins’ lalu pilih ‘go’

6) Mencentang kotak ‘analysis’ dan ‘solver’ lalu pilih ok.

7) Pilih tab ‘data’ lalu pilih ‘data analysis’.

8) Pilih ‘histogram’ lalu ‘ok’.

9) Pilih data usia pada input range.

10) Pilih data kelas usia pada bin range.

11) Pilih sel lokasi grafik histogram, lalu centang ‘chart output’, lalu pilih ok.

12) Akan dihasilkan gambar seperti ini.

13) Mengedit data kelas di kolom bin.

14) Klik kanan pada grafik batang dan pilih ‘format data series’.

15) Mengubah gap width menjadi 0.

16) Memilih fill & line, lalu pilih border, dan pilih solid line.

17) Memilih warna hitam atau bisa warna lainnya.

18) Akan dihasilkan gambar seperti ini.

e. Polygon frekuensi

1) Klik kanan pada histogram yang telah dibuat sebelumnya. Kemudian pilih ‘select data’.

2) Pilih ‘add’.

3) Pada series name, pilih data yang berada di bin. Pada series values, pilih data yang ada

pada frequency. Pilih ok.

4) Akan dihasilkan grafik seperti ini.

5) Klik kanan pada grafik batang berwarna orange. Pilih change series chart type.

6) Pilih clustered column – line.

7) Pada series name grafik batang orange, pilih line.

8) Pilih line with markers. Pilih ok.

9) Akan dihasilkan grafik seperti di bawah ini.

f. Ogive

1) Pada tabel kelas dan frekuensi sebelumnya ditambahkan frekuensi kumulatif lebih dan

kurang dari seperti di bawah ini.

2) Memblok data kelas, frekuensi lebih dari, dan frekuensi kurang dari.

3) Pilih tab insert, pilih insert line or area chart, kemudian pilih line with markers.

4) Kemudian akan dihasilkan grafik seperti di bawah ini.

3. Pivot table

a. Membuka Microsoft excel.

b. Menginput data.

c. Memblok semua data

d. Pilih tab ‘insert’ lalu pilih ‘pivot table’

e. Ikuti pengaturan di bawah ini lalu pilih ok.

f. Akan ditampilkan tampilan seperti di bawah ini.

g. Kemudian dapat memilih data yang akan ditampilkan.

h. Dapat pula mengubah urutan yang berada pada row labels. Misalnya, diurutkan

berdasarkan bulan, kemudian, daerah, kemudian merek. Mencari label bulan pada rows.

i. Pilih move to beginning.

j. Memilih daerah, kemudian pilih move up.

k. Akan dihasilkan tabel berikut.

4. Vlookup

a. Membuka Microsoft excel.

b. Menginput data.

c. Menginput tabel baru seperti di bawah ini.

d. Menginput fungsi vlookup pada sel di bawah label merek, yaitu

“=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])”.

e. Kemudian akan dihasilkan tabel di bawah ini.

f. Menginput rumus yang sama pada kolom jumlah (ton), biaya produksi, biaya jual, dan

laba, tetapi dengan nilai col_index_num yang berbeda untuk setiap kolomnya, yaitu

berturut-turut 4, 5, 6, dan 7.

g. Sehingga akan dihasilkan tabel di bawah ini.

5. Hlookup

a. Membuka Microsoft excel.

b. Menginput data.

c. Menginput tabel baru seperti di bawah ini.

d. Menginput fungsi vlookup pada sel di bawah label merek, yaitu

“=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])”.

e. Kemudian akan dihasilkan tabel di bawah ini.

f. Menginput rumus yang sama pada kolom jumlah (ton), biaya produksi, biaya jual, dan

laba, tetapi dengan nilai col_index_num yang berbeda untuk setiap kolomnya, yaitu

berturut-turut 3, 4, 5, dan 6.

g. Sehingga akan dihasilkan tabel di bawah ini.

6. Fungsi if, and, or

a. Membuka Microsoft excel.

b. Menginput data.

c. IF tunggal

1) Menginput rumus pada sel di bawah if tunggal, yaitu “=IF(logical_test, value_if_true,

value_if_false)”.

2) Fungsi tersebut berarti apabila nilai yang terdapat pada visual adalah 0, produk tersebut

lulus syarat mutu. Dan apabila nilai pada visual tidak sama dengan 0, maka produk

tersebut tidak lulus syarat mutu.

3) Akan dihasilkan tabel berikut.

d. IF Ganda

1) Menginput rumus pada sel di bawah if ganda, yaitu “=IF(logical_test, value_if_true,

value_if_false)”.

2) Fungsi tersebut berarti produk lulus syarat mutu apabila nilai yang terdapat pada

bocor/berlubang adalah 0, botol penyok adalah 0, dan permukaan mulut botol tidak rata

adalah 0. Dan apabila nilai pada bocor/berlubang, botol penyok, dan permukaan mulut

botol tidak rata tidak sama dengan 0, maka produk tersebut tidak lulus syarat mutu.

3) Akan dihasilkan tabel berikut.

e. IF OR

1) Menginput rumus pada sel di bawah if ganda, yaitu “=IF(OR(logical1, logical2),

value_if_true, value_if_false)”.

2) Fungsi tersebut berarti produk lulus syarat mutu apabila nilai yang terdapat pada salah

satu kriteria visual, bau dan rasa, bocor/berlubang, dan permukaan mulut botol tidak

rata adalah 0. Dan apabila semua nilai pada bocor/berlubang, botol penyok, dan

permukaan mulut botol tidak rata tidak sama dengan 0, maka produk tersebut tidak lulus

syarat mutu.

3) Akan dihasilkan tabel berikut.

f. IF AND

1) Menginput rumus pada sel di bawah if ganda, yaitu “=IF(AND(logical1, logical2),

value_if_true, value_if_false)”.

2) Fungsi tersebut berarti produk lulus syarat mutu apabila nilai yang terdapat pada semua

kriteria visual, bau dan rasa, bocor/berlubang, dan permukaan mulut botol tidak rata

adalah 0. Dan apabila salah satu nilai pada bocor/berlubang, botol penyok, dan

permukaan mulut botol tidak rata tidak sama dengan 0, maka produk tersebut tidak lulus

syarat mutu.

3) Akan dihasilkan tabel berikut.

g. IF Bertingkat

1) Menginput tabel seperti di bawah ini.

2) Menginput rumus pada sel di bawah if bertingkat.

3) Akan dihasilkan tabel berikut.