MsEXCEL: Olah Data pake DATABASE LIST & PIVOT TABLE

excel.jpgKalo kita punya data kaya’ tabel yang berisi banyak banget data (banyak kolom dan banyak baris), menurut aku lebih baik kalo data Excel kita itu kita ubah dulu sebagai DATABASE LIST, caranya (sorry bahasane campuran ama ENglish, habis nyatetnya pake English🙂 ):

………..Data –> List –> “CREATE LIST”

Now our list became a Database List
On “List Toolbar” there is facilities to count Total
…then it will appear new field “Total” with each cell provide rolling arrow
   with some functions of total

FILTER
if we click the Title Cells, there will be a ‘Filter’ Rolling Arraw Functions
we can just view a particular arrow based on our criteria on the our ‘filter’ cell

we can also COMBINE the FILTER
…so on the ‘Product Name’ title we choose “SNACK” and on ‘Price’ title we choose “Price GREATER THEN 100″
 

convert to Common Excel list ….from the List Toolbar click ….”CONVERT to RANGE”

——————————-

We Need MULTIPLE WINDOWS

multiple windows are useful when we want to compare data located in far location (e.g. A1 with A300)
or to compare data between 2/more Excel files

just using the………………”WINDOW” functions on the menu

———————————–

Tips
Go down to last data cell……………………………..Control + down arrow

Select all data when we have highlighted the last cell arrow……..Shift + COntrol + Home

Go back to firt cell (A1)………………………………control + home

———————————–

Tips Working with Very Large List in order to make the Title Always Appear

Open 2 same windows using…….Window –> New Window
Delete the vertical windows just by double click it

make the up window just shows the title Arrow then Freeze it…Window –> “FREEZE PANES”
then we can work freely with the below excel windows with Title always appears up

————————————

…”PASTE SPECIAL”

Pasting the coppied data ‘special’ just the values or formula or… (choose on the popup menu)

————————————
Tips

Shortcut for changing the $ position on Absolute Reference….press F4

————————————–

PIVOT TABLE

fasilitas ini biasanya kita pake saat kita pengin membikin Rangkuman Analisis Data kita yang lebih kompleks (misal mencari nilai tertinggi dari nilai rata-rata semua baris):

if we want to summarize some diferent data

just use……………..”DATA” –> “Pivot Table”

otherwise you need Formula

—————————————

Entrying Data faster….

just using the Form ……………..DATA –> “FORM”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: