ABC-02-Intro to Excel Data Analysis.ppt

Report
Applied Business Computation - 02
INTRO TO EXCEL DATA ANALYSIS
abc.aurino.com
1
INTRO TO EXCEL DATA ANALYSIS
• Introduction to Data Analysis using Ms
Excel 2007
• Organizing Worksheet Data
• Creating Formula, Using Function (Statistics
& Financial )
• Working With Pivot Tables and Charting
Data
abc.aurino.com
2
WORK BOOK
Name box and
current cell
address
Cell pointer
Formula
bar
Sheet tabs
abc.aurino.com
Status bar
3
KOMPONEN WORK BOOK
Name box menunjukkan alamat (address) sel yang aktif (sedang dipilih)
Formula bar untuk mengedit atau input data ke worksheet
Perpotongan Baris (row) dan Kolom (column) disebut cell (sel)
Lokasi sel disebut cell address (alamat sel)
Alamat sel diberi identifikasi koordinat (A1)
Sel yang sedang terpilih (diedit atau memasukkan data) disebut active cell
Sheet tabs digunakan untuk memilih atau berpindah-pindah antar sheet dalam
sebuah workbook
Status bar memberikan informasi ringkas perintah aktif atau yang sedang
dilaksanakan
Mode indicator informasi tentang Pekerjaan tertentu.
Pilihan dua atau lebih sel two misalnya B5:B14 disebut range
abc.aurino.com
4
abc.aurino.com
FORMULA

Jika kita memasukkan perhitungan ke Excel,
maka perlu:
 Mengetahui
dimana kita akan memasukkan
formula tersebut
 Mengetahui dengan tepat sel mana dan bagai
mana rumusnya
 Gunakan referensi sel jangan angka atau konstanta
 Tentukan apa yang harus dihitung
5
abc.aurino.com
FORMULA
Viewing a Formula
Formula
appears in
formula bar
Result of
formula
6
abc.aurino.com
OPERATOR
Excel arithmetic operators
7
abc.aurino.com
LABEL AND NILAI DAN AUTOSUM




Label berisi teks dan angka tapi tidak untuk perhitungan
Label digunakan sebagai penjelas data bisa diletakkan
sebagai judul baris atau kolom. Untuk memasukkannya
maka masukkan label dulu baru isinya
Nilai (Value) bisa berupa are angka, rumus, dan fungsi
yang dapat dipergunakan untuk perhitungan.
Fungsi (function) adalah rumus yang tersedia dalam excel




Fungsi biasanya memiliki argument
yi informasi yang perlu untuk perhitungan
Dengan meng-Klik Tombol AutoSum
maka range
didekatnya (di atasnya atau di kiri) akan dijumlahkan,
walaupun Anda bisa mengubah range-nya
Tombol Fill dapat mengisi secara berurutan berdarsarkan
angka pertama dalam range
8
abc.aurino.com
ENTERING LABELS AND VALUES
AND USING AUTOSUM
Creating a formula using the AutoSum button
AutoSum
button
Formula
Fill button
atau
gunakan
Fill handle
Cells included in
formula
9
abc.aurino.com
EDIT ISI SEL

Untuk mengubah atau edit isi sel aktif, maka: Double-click sel


Click dalam formula bar, atau
Langsung mengetik dalam sel tersebut.
10
abc.aurino.com
EDITING CELL ENTRIES
Common pointers in Excel
11
abc.aurino.com
MEMASUKKAN DAN MENGEDIT FORMULA
Formula dimulai dengantanda sama dengan (=)
yang biasa disebut formula prefix
 Calculation operators dalam formula
menunjukkan operasi perhitungan

Arithmetic operators – penambahan (+), pengurangan
(-), mengalikan (*), dan membagi (/)
 Comparison operators membandingkan (>,<,<=,>=,<>)
 Text concatenation operators menggabungkan teks (&)
 Reference operators menggunakan isi range tertentu
untuk perhitungan ( A1, R1:R12, $R1, $R$2, AR$20,
$R1:R$5 atau nama range)

12
abc.aurino.com
TAMPILAN WORKSHEET (VIEWS)

Untuk mengubah view worksheet window:
Tab View pada Ribbon
 Tombol View pada status bar

Normal view menampilkan worksheet tanpa
headers & footers, rulers dan nomor halaman
 Page Layout View tampilan seperti bentuk tercetak
di halaman printer, yang menunjukkan batas
halaman, headers and footers, rulers, dsb.
 Page Break Preview menampilkan displays a
reduced view of each bentuk worksheet, dengan
indikator page break

13
abc.aurino.com
SWITCHING WORKSHEET VIEWS
Page Layout View
Horizontal
ruler
Workbook
Views group
Header text
box
Vertical
ruler
14
abc.aurino.com
SWITCHING WORKSHEET VIEWS
Page Break Preview
Blue outline
indicates
print area
Bottom page
break
indicator
15
abc.aurino.com
PRINT OPTIONS

Untuk melihat worksheet yang akan dicetak kita gunakan:

Page Layout tab


Print Preview


Bentuk cetak dengan meng-click tombol Print di Ribbon
Page Layout tab

Page Setup group




Garis titik-titik menunjukkan print area, yaitu area yang akan dicetak
Print orientation: landscape or portrait
Scale to Fit group
Sheet Options group
Print dialog box akan membantu pemilihan jumlah copy,
printer, dan lain-lain.
16
abc.aurino.com
CHOOSING PRINT OPTIONS
Worksheet with Portrait orientation
Page
Layout tab
Dotted line
surrounds
print area
17
abc.aurino.com
CHOOSING PRINT OPTIONS
Worksheet in Print Preview
Print
button
Close Print
Preview
button
Zoom
button
18
MEMBUAT FORMULA
Formula dengan beberapa operator
Complex
formula
Mode
indicator
abc.aurino.com
Urutan pengerjaan oleh excel:
1. Operasi dalam kurung
dihitung terlebih
dahulu;
2. Pangkat dihitung
berikutnya
3. Perkalian dan
pembagian
selanjutnya dari kiri ke
kanan
4. Selanjutnya
Penambahan dan
pengurangan (urutan
dari kiri ke kanan)
19
abc.aurino.com
MENGGUNAKAN FUNGSI

Function atau fungsi adalah rumus yang telah
dimasukkan terlebih dahulu ke dalam excel
untuk perhitungan yang kompleks:
 Dapat
digunakan sendiri atau dalam rumus lainnya
 Jika digunakan sendiri maka diawali dengan (=)
20
abc.aurino.com
MENGGUNAKAN FUNCTION
Function Arguments dialog box
Function
Insert
Function
button
Argument
Description
and
argument
format
21
abc.aurino.com
MENGETIKAN FUNGSI
MAX function in progress
22
abc.aurino.com
MENGCOPY DAN MEMINDAHKAN ISI SEL
Paste
button
Copied data in Clipboard
Copy
button
Clipboard
launcher
Item in
Clipboard
23
abc.aurino.com
RELATIVE AND ABSOLUTE CELL
REFERENCES

Penggunaan relative cell reference diperlukan jika hubungan
formula dengan lokasi ingin dipertahankan


Perhitungan dilakukan berdasarkan pada hubungan sel
Pada saat dicopy ke tempat lain maka referensi sel ikut berubah
Formula containing
relative references
24
abc.aurino.com
ABSOLUTE CELL REFERENCE

Jika ingin mempertahankan alamat sel dalam sebuah formula
Referensi sel tidak akan berubah jika formula dicopykan ke lokasi
lainnya
 Dibuat dengan menambahkan tanda dollar ($) sebelum huruf
column dan angka baris dari sebuah alamat sel
Cell referenced
in
Absolute
absolute
references do
formulas
not adjust

Relative
references
adjust
25
abc.aurino.com
MIXED REFERENCE

Referensi campuran baik referensi sel relatif dan absolut


Contoh: Jika formula dicopykan, kita ingin referensi baris berubah
tapi refensi kolom tidak.
Dibuat dengan menekan [F4]
26
abc.aurino.com
MENCOPY FORMULA DENGAN FILL HANDLE
Paste
button list
arrow
Fill handle
Auto Fill
Options
button
27
abc.aurino.com
COPY FORMULA DNG
REFERENSI SEL RELATIF

Dengan Auto Fill maka kita dapat mengisi sel dengan
text atau nilai yang berurutan



Nama Bulan; nama hari; atau text dengan angka (Quarter 1,
Quarter 2, dsb.)
Klik mouse dan tarik fill handle untuk menambahkan
Jadikan absolute cell reference sebelum mengcopy formula
jika ingin satu atau lebih referensi sel tidak berubah
28
abc.aurino.com
CREATING AN ABSOLUTE REFERENCE IN FORMULA
Absolute cell
reference in
formula
Incorrect
values from
relative
referencing in
copied
formulas
29
abc.aurino.com
MENAMBAH FUNGSI DALAM FORMULA
Sel yang berisi data finansial lebih mudah dibaca jika angka
dibelakang desimalnya sedikit
Use the ROUND function to round down your results
ROUND
function and
opening
parenthesis
inserted in
formula
ScreenTip
indicates what
information is
needed
30
abc.aurino.com
MEMFORMAT NILAI



Format sel untuk menentukan tampilan label dan nilai: Bold, italic,
dollar signs, commas, dsb.
Formatting tidak mengubah isi data hanya tampilan yang berubah
Pilih sel atau range, lalu terapkan formatting
Format Cells dialog box
31
abc.aurino.com
ATTRIBUTES DAN ALIGNMENT
Formatting
buttons
selected
Title centered
across columns
Center button
Merge &
Center button
Column headings centered,
bold, and underlined
32
abc.aurino.com
attribute and alignment dan lebar kolom
Sesuaikan lebar kolom untuk mengakomodasi data. Lebar kolom default adalah
8.43 karacter (sekitar 1 inchi). Kolom dapat diubah lebarnya dengan mouse,
menu di Ribbon, atau shortcut menu.
Dengan Mouse: Letakkan mouse pointer diantara huruf kolom (column heading)
pada garis batas antar kolom:
Click dan drag untuk mengubah besar kolom.
Double-click untuk AutoFit
33
abc.aurino.com
CONDITIONAL FORMATTING

Excel dapat memformat berdasarkan isi sel dengan
conditional formatting.

Misal nilai di atas angka tertentu berwarna biru dan yang lainnya
berwarna lain.
34
FORMATTING DATA DENGAN FUNGSI TEXT

Conversion tools dan Fungsi text otomatis mem- format a cell range
 Convert Text dengan memecah data fields ke beberapa kolom



Element Data yang akan dipecah harus dipisahkan (delimiter) dengan
spasi, koma, atau titik koma, titik dan lain-lain.
PROPER() – mengubah huruf pertama menjadi huruf besar, selain
itu ada UPPER() dan LOWER()
CONCATENATE() – fungsi menggabungkan dua text atau lebih.
Preview data
dng delimiter
abc.aurino.com
35
OPERASI RANGE DATA BERDASAR KONDISI

SUMIF function



COUNTIF function



Hitung jumlah sel dalam range berdasarkan kriteria tertentu.
COUNTIF(range, criteria)
AVERAGEIF function


abc.aurino.com
Total sel suatu range yang memenuhi kriteria tertentu.
SUMIF(range, criteria, [sum_range])
Rerata isi range berdasarkan kriteria tertentu.
AVERAGEIF(range, criteria, [average_range])
36
MENGGUNAKAN NAMED RANGES

Memberikan ke sel dan ranges akan mengurangi kesalahan
dan mempermudah pengertian thd perhitungan



Names yang digunakan dalam formula menjadi referensi absolut
Name bisa menggunakan huruf besar atau huruf kecil dan angka
Setelah sel atau range diberi nama, maka bisa digunakan di sheet
manapun, jika sel atau range dipindahkan mana namanya ikut
pindah
Label
Tarif Pajak
abc.aurino.com
Data dan Formula
Nama Sel C
Formula Sel C
15%Tarif_Pajak
Gaji
Rp 10,000,000.00
Gaji
Pajak
Rp 1,500,000.00
Pajak
=Gaji*Tarif_Pajak
Gaji Bersih
Rp 8,500,000.00
Gaji_Bersih
=Gaji-Pajak
37
MENGATUR NAME SEL ATAU RANGE
Name
Manager
abc.aurino.com
38
FUNGSI IF() DAN VLOOKUP()


IF(logical_test,value_if_true,value_if_false)
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
abc.aurino.com
39
BUILDING A LOGICAL FORMULA WITH
THE IF FUNCTION (CONT.)
Comparison operators
abc.aurino.com
40
FUNGSI AND() DAN OR()


AND(logical1,logical2, ...)
Returns TRUE if all its arguments are TRUE; returns FALSE if one or more
arguments is FALSE.
AND(1<A2,
A2<100)


Displays TRUE if the number in cell A2 is
between 1 and 100. Otherwise, it displays
FALSE.
TRUE
OR(logical1, [logical2], ...)
Returns TRUE if any argument is TRUE; returns FALSE if all arguments are
FALSE.
=OR(TRUE)
One argument is TRUE (TRUE)
abc.aurino.com
=OR(1+1=1,2+2=5)
All arguments evaluate to FALSE (FALSE)
=OR(TRUE,FALSE,TRUE)
At least one argument is TRUE (TRUE)
41
FUNGSI KEUANGAN

PMT adalah fungsi keuangan yang menghitung
cicilan periodik dari uang yang dipinjam:
PMT(rate,nper,pv,fv,type)
PMT(0.085/12,60,15000) = $307.75
Tingkat bunga
abc.aurino.com
Jumlah cicilan
Nilai Pinjaman
Cicilan Bulanan
42
FUNGSI KEUANGAN LAINNYA







FV(rate,nper,pmt,pv,type)
IRR(values,guess)
NPER(rate, pmt, pv, fv, type)
NPV(rate,value1,value2, ...)
PV(rate,nper,pmt,fv,type)
RATE(nper,pmt,pv,fv,type,guess)
dll
abc.aurino.com
43
FUNGSI STATISTIK


AVERAGE() adalah fungsi statistik untuk menghitung
cicilan data: AVERAGE(number1, number2,…)
STD(), MAX(), MIN(), SKEW() dan lain-lain
10
7
9
=AVERAGE(C4:G4)
11
=STDEV(C4:G4)
9.460444
=MAX(C4:G4)
27
=MIN(C4:G4)
2
=SKEW(C4:G4)
1.620981
abc.aurino.com
27
2
44
PIVOT TABLE


Gunakan PivotTable untuk meringkas data
sebuah worksheet
Petunjuk membuat PivotTable:





Review data sumber
Tentukan tujuan PivotTable dan tulis nama field yang
ingin disertakan
Tentukan field mana yang akan diringkas dan dengan
fungsi apa
Putuskan bagaimana bentuk tabelnya
Tentukan lokasi PivotTable
abc.aurino.com
45
MERENCANAKAN DAN MENDESIAN
PIVOTTABLE
Column fields
Row
fields
Data summary field
abc.aurino.com
46
CREATING A PIVOTTABLE REPORT
Click to add a field
to the PivotTable
abc.aurino.com
47
MEMBUAT PIVOTTABLE
Column fields
Row fields
Data area
abc.aurino.com
48
MENGUBAH FUNGSI DATA PIVOTTABLE
PivotTable
showing
averages
abc.aurino.com
49
MEMFILTER DAN MENGURUTKAN DATA
PIVOTTABLE
Only the 1st
quarter
should e
displayed
Quarter is in
the Report
Filter area
Sales for 1st
quarter only
abc.aurino.com
50
UPDATING A PIVOTTABLE REPORT
New record
added
Total reflects
new CD
information
abc.aurino.com
51
MENGUBAH STRUKTUR DAN FORMAT
PIVOTTABLE REPORT
Category is
now in the
Report Filter
area
abc.aurino.com
52
MEMBUAT PIVOTCHART REPORT
PivotChart
abc.aurino.com
53
MENGGUNAKAN GETPIVOTDATA
FUNCTION
GETPIVOTDATA
Function
abc.aurino.com
Result of the
GETPIVOTDATA
Function
54

similar documents