TIPS DAN TRIK MS. EXCEL

Rumus Excel VLOOKUP

Syntax dari fungsi microsoft excel ini adalah : VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)
Rumus Excel VLOOKUP dipakai untuk mencari nilai dalam kolom pertama dari tabel dan mengembalikan nilai dalam baris yang sama dari kolom lain dalam tabel. Supaya lebih jelas, kita pakai fungsi Microsoft Excel VLOOKUP dalam contoh berikut.
Toko Saribusana memiliki tabel Daftar Barang sbb. :

Kemudian, saya diminta membuat tabel hasil penjualan bulan Juli 2011 seperti berikut :

Untuk mengisi kolom Keterangan, saya akan ambil dari Daftar Barang Toko Saribusana berdasarkan Kode yang ada, maka rumus Excel nya adalah
=VLOOKUP(B12,$B$3:$E$6,2,FALSE)
B12 : Kode Barang di B12 adalah acuan yang dipakai untuk mengambil data di tabel Daftar Barang.
$B$3:$E$6 : range tabel yang akan kita ambil datanya, dalam hal ini adalah tabel Daftar Barang tanpa mengikutsertakan judul kolom. Dan,  jangan lupa gunakan $ sebab range tabel adalah absolut.
2 : kolom ke-2 yaitu kolom Keterangan di range tabel adalah kolom yang akan kita ambil datanya. Kolom ke-1 selalu dimulai dari kolom paling kiri.
Singkatnya, kita menyuruh Excel melihat Kode di cel B12 lalu cari di range tabel Daftar Barang sesuai Kode yang sama, setelah itu ambil nilai di kolom Keterangannya dan isikan di tabel Hasil Penjualan. Hasilnya :

Demikian juga untuk mengisi kolom Harga Penjualan di cel E12, dengan memperhatikan bahwa Harga Penjualan = Harga Pokok * (1+Markup), maka , rumus Excel nya adalah
=VLOOKUP(B12,$B$3:$E$6,3,FALSE)*(1+VLOOKUP(B12,$B$3:$E$6,4,FALSE))
Di fungsi Microsoft Excel tersebut, kita menyuruh Excel melihat Kode di B12 lalu cari kode yang sama di tabel Daftar Barang, setelah itu ambil Harga Pokoknya dan kalikan Harga Pokok dengan (1+Markup). Nilai Markup diambil dengan cara yang sama dengan cara mengambil nilai Harga Pokok. Maka kita mendapatkan Harga Penjualan untuk Kode di B12.
Oke, silakan mencoba dulu dan mengutak atik fungsi Microsoft Excel VLOOKUP. Semoga mengerti.
Oh, ya, V di VLOOKUP singkatan dari vertikal. Penggunaan VLOOKUP (bukan HLOOKUP) ketika nilai-nilai acuan Anda berada dalam kolom di sebelah kiri tabel data dan Anda mengambil data lain di sebelah kanannya. Gunakan fungsi microsoft excel HLOOKUP ketika nilai-nilai acuan Anda berada dalam baris di sebelah atas tabel data dan Anda ingin mengambil data-data di baris bawahnya.

Rumus Excel IF

Syntax dari fungsi microsoft excel ini adalah : IF(logical_test,value_if_true,value_if_false)
Maka dengan menggunakan fungsi microsoft excel IF, kita memberikan :

• suatu kondisi kepada Excel, lalu
• jika kondisi itu terpenuhi maka lakukan A,
• jika tidak terpenuhi maka lakukan B.
  

Oke, kita coba dalam kasus yang sama yaitu Toko Saribusana. Kembali saya diminta menambahkan sebuah kolom di Tabel Penjualan bulan Juli 2011 yang menunjukkan status barang. Jika penjualan barang lebih dari 500 unit berarti Barang Laku, jika penjualan diantara 300 dan 500 maka Cukup Laku, dan di bawah 300 berarti Ga Laku.

Rumus excel di sel G12 adalah
=IF(D12>300,IF(D12>500,”Laku Keras”,”Kurang Laku”),”Ga Laku”)
Saya meminta Excel untuk mengecek :

• Apakah unit terjual di D12 lebih dari 300  =IF(D12>300
• Jika ya maka cek lagi apakah D12 lebih dari 500, IF(D12>500
• Jika ya maka Laku Keras, “Laku Keras”
• jika tidak maka Kurang Laku.  “Kurang Laku”
• jika tidak berarti barang Ga Laku. “Ga Laku”
  

Di atas terlihat bahwa terjadi dua kali pengecekan, atau saya memakai 2 kali rumus excel IF. Fungsi If kedua berada di dalam fungsi If pertama. Fungsi IF dapat dipakai di dalam fungsi IF lainnya, jangan lupa fungsi IF di dalam tidak memakai tanda sama dengan [=] lagi.
Sekarang kita akan mencoba fungsi microsoft excel yang berhubungan dengan tanggal.

Fungsi Microsoft Excel Date and Time

Kedua fungsi microsoft excel ini sering juga dipakai dalam mengolah data di Excel.

Langsung saja, di atas adalah tabel Daftar Siswa Tercepat dalam Ujian IPA dari tiap kelas. Petugas administrasi memasukkan data tanggal, bulan, tahun dalam kolom-kolom terpisah supaya lebih cepat dan mudah. Nah, tugas saya mencari siapa yang tercepat dari kelima kontestan tersebut.
Pertama, saya satukan dulu tanggal, bulan dan tahun menjadi satu di sel C12 dengan fungsi microsoft excel :
=DATE(E3,D3,C3)
Fungsi microsoft excel ini mengambil data di sel E3,D3,C3 dan menjadikannya tanggal.
Lalu satukan juga jam, menit, dan detik tiap siswa di sel D12 dengan rumus :
=TIME(F3,G3,H3)
Rumus ini mengambil data di sel E3,D3,C3 dan menjadikannya waktu, untuk format time yang Anda ingini, ubah dalam menu Format Cells (klik kanan di sel, pilih Format Cells, pilih Time, pilih format yang Anda mau).
Baru saya cari siswa tercepat di sel E12 dengan fungsi microsoft excel :
=RANK(D12,$D$12:$D$16,1)
Fungsi Rank mencari urutan dalam suatu range, lalu Anda bisa pilih mau Ascending atau Descending.  Hasilnya adalah tabel berikut

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 kolomsehingga 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 resali atau 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
1. Fungsi VALUE :
digunakan untuk merubah nilai value menjadi nilai text,
penulisannya : =VALUE(text)
2. Fungsi FIND :
digunakan untuk menghasilkan posisi substring dari sebuah string atau suatu nomor yang dicari,penulisannya : =FIND(cari text,pada text,mulai nomor)
3. Fungsi MID
digunakan untu mengambil karakter tertentu dari sederet karakter, penulisannya : =MID(text,posisi awal,jumlah karakter)
4. Fungsi LEFT atau RIGHT :
digunakan untuk mengambil substring sebelah kiri atau kanan string, penulisannya =LEFT atau =RIGHT(text,jumlah karakter)
5. 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)
6. 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,…)

Fungsi yang sering digunakan
1. fungsi Sum :
Digunakan untuk menjumlahkan sekumpulan data pada satu range, penulisannya : =SUM(number1,number2,..)

2. Fungsi Average :
Digunakan untuk mencari nilai rata-rata,
penulisannya : =average(number1,number2,…)
3. Fungsi Max :
Digunakan untuk mencari nilai tertinggi dari sekumpulan data, penulisannya : =max(number1,number2,…)
4. Fungsi Min:
Digunakan untuk mencari nilai terendah dari sekumpulan data, penulisannya : =max(number1,number2,…)
5. fungsi Count :
Digunakan untuk menghitung jumlah data dari range yang kita pilih
6. Fungsi Stedev :
Digunakan untuk menentukan standart devisiasi dari suatu range, penulisannya : =stedev(number1,number2,…)
7. Fungsi Var :
Digunakan untuk menentukan nilai varience dari suatu range, penulisannya : =var(number1,number2,…).

TCP/IP UNTUK LAN DAN INTERNET

Jaringan Komputer adalah menghubungkan dua komputer atau lebih, sehingga antara satu komputer
dengan yang lain dapat saling bertukar resource seperti sharing file, pri nter, dll dengan menggunakan protocol
yang sama, dalam hal ini TCP/IP. Adapun jaringan komputer ini telah banyak digunakan untuk berbagai
kebutuhan, missal :
- WARNET
- MULTIPLAYER GAME
- PERKANTORAN
- KAMPUS, DLL
Untuk membuat sebuah jaringan komputer diperlukan perangkat sebagai berikut :
- Dua buah computer atau lebih
- LAN Card/NIC sesuai dengan jumlah computer (untuk computer sekarang biasanya sudah onboard)
- Kabel UTP sesuai kebutuhan
- Tang Crimping
- Konektor RJ45
- Switch/Hub jika ingin mengunakan tiga computer ata u lebih
- Gunting
Gambar perangkatnya :
 


Gambar 1 : LAN CARD
 


Gambar 2: Kabel UTP
 


Gambar 3 : Tang Crimping
 


Gambar 4 : Konektor RJ 45
 
Gambar 5 : Switch/Hub
Setelah semua peralatan terkumpul lengkap kita telah siap untuk membuat jaringan computer, langkahlangkahnya
adalah sebagai berikut :
TENTUKAN MAU MEMBUAT JARINGAN DENGAN BERAPA KOMPUTER ?
Hal ini diperlukan karena konfigurasi kabel untuk jaringan dua dengan tiga computer/lebih berbeda.
Untuk menghubungkan dua buah komputer tidak diperlukan switch dan menggunakan kabel UTP dengan
pengkabelan jenis straight, sedangkan untuk jaringan dengan tiga computer atau lebih menggunakan switch dan
kabel UTP dengan konfigurasi pengkabelan cross -over (Mengenai pengkabelan akan dibahas selanjutnya).
MEMASANG RJ-45 PADA KABEL UTP
Karena pada tutorial ini kita akan menggunakan kabel UTP sebagai medianya, maka kita harus memasang
konektor RJ-45 pada kabel tersebut agar dapat ditancapkan pada LAN CARD, seperti yang telah dikatakan tadi
konfigurasi jaringan dua computer dengan tiga computer berbeda, caranya adalah sebagai berikut :
Konfigurasi dua computer menggunakan crossover
1. Kupas kedua ujung kulit luar kabel utp menggunakan mata pisau crimping atau gunting, dengan panjang
kira-kira 1.5cm, seperti ini :
 
Gambar 6 : Kabel UTP Setelah dikupas kulit luar
2. Kemudian urutkan warna-warnanya dengan aturan jika ujung kabel satu menggunakan konfigurasi T568A
maka ujung kabel satunya menggunakan T568B. (Konfigurasi dapat dilihat pada gambar dibawah)

Gambar 7 : Konfigurasi kabel T568A dan T568B
3. Setelah kedua ujung kabel diurutkan warnanya sesuai konfigurasi di atas, ratakan ujung kabel utp dengan
memotong sedikit bagian kabel (tidak usah di kupas kabelnya), kira -kira hasilnya seperti ini :


Gambar 8 : Kabel UTP yang sudah diratakan ujungnya
4. Masukkan kabel tersebut kedalam konektor RJ -45 secara hati-hati, jangan sampai tertukar urutannya,
maka hasilnya akan seperti ini :

Gambar 9 : Kabel UTP setelah dimasukkan pada RJ -45
5. Kemudian crimping/jepit kabel tersebut menggu nakan tang crimping sampai terdengan suara klik. (Ingat
dalam mengcrimping jangan terlalu keras menekan krn menyebabkan konektor patah.

Gambar 10 : Kabel UTP di Crimping/diJepit
6. Setelah selesai di crimping, maka hasil kedua ujung kabel akab seperti berik ut :

Gambar 11 : Kabel UTP Sudah Terpasang pada konektornya
MEMASANG KABEL UTP YANG SUDAH TERPASANG KONEKTOR KE PC
Setelah kabel utp terpasang konektor, langkah selanjutnya adalah menancapkan kabel utp tersebut ke
LAN CARD computer, jika hanya menggun akan dua computer maka pasanglah kabel UTP yg telah terpasang RJ -45
dengan konfigurasi cross-over ke masing-masing computer, tetapi jika terdiri dari tiga computer atau lebih maka
kita harus menggunakan switch/switch dengan topologi kira -kira sebagai berikut :
Gambar 12

Gambar 12 : Topologi LAN dengan empat computer

Gambar 13 : Switch/Hub Tampak dari belakang
Gambar di atas merupakan switch atau hub yang terdiri 8 port, yang banyak terdapat dipasaran
switch/hub dengan jumlah colokan rj -45 sebanyak 4/8/16/32 port , mengenai pemelihan tinggal disesuaikan
dengan kebutuhan. Untuk memasang kabel UTP pada switch, Anda tinggal menancapkan satu konektor RJ -45
yang telah terpasang pada kabel UTP tadi ke salah satu port pada switch/hub dan satunya lagi pada PC. Jika sudah
di tancapkan maka akan muncul icon LAN Connected pada system tray computer :

MENGKONFIGURASI IP ADDRESS
Setelah jaringan secara fisik terinstall, maka Anda harus mengkonfigurasi IP Address masing -masing
computer sehingga dapat saling berkomunikasi/terko neksi. IP Address adalah pengalamatan jaringan computer
yang terdiri dari 32 bit biner. Untuk membuat jaringan LAN anda dapat menggunakan IP Address salah satu kelas
dibawah ini :
Kelas A : 10.x.x.x dengan subnet mask 255.0.0.0
Kelas B : 172.16.x.x – 172.31.x.x dengan subnet mask 255.255.0.0
Kelas C : 192.168.x.x dengan subnet mask 255.255.255.0
Dalam satu jaringan LAN, semua komputer harus menggunakan kelas yang sama. Pada praktek kali ini kita
menggunakan Kelas C. Karena jumlah computer yang terkoneksi sedikit, dimana kelas C ini mampu
mengkoneksikan maksimal 254 komputer. Jadi kita menggunakan IP :
192.168.x.x dengan Subnet 255.255.255.0
255.255.255.0 maksudnya 24 bit pada IP Address merupakan network address sedangkan 8 bit berikutnya adalah
host ID (ingat 255 jika dibinerkan ada 8 bit yaitu 11111111). Nilai x di atas dapat di isi terserah dengan range 1 -
254. Jadi :
 Host IDà Adalah Network address sedangkan x à192.168.x
Jadi Range alamat IP Address LAN kita nantinya = 192.168.1.1 – 192.168.1.254 Subnetmask 255.255.255.0 (jika
bingung dengan keterangan di atas, selalu pakai range IP Address ini saja bila ingin membangun LAN, ga sah
pusing-pusing mikirin teori di atas)
Pada kali ini kita akan menggunakan network address 192.168.1 dan host ID antara 1 -254. (Nama pada masingmasing
computer harus unik artinya jika ada computer dengan IP Address 192.168.1.1 maka tidak boleh ada
computer lain dalam satu LAN yang mempunyai IP Adress tersebut)
Cara Mengeset IP Addess adalah sebagai berikut :
 Control Panel sampai keluar kotak dialog Control Panel (seperti terlihatà Setting à1. Click Start
digambar dibawah ini)

Gambar 14 : Dialog Control Panel
2. Double-Click Icon Network Connection sampai keluar kotak dialog Network Connection

Gambar 15 : Dialog Network Connection
3. Double-click Icon Local Area Connection sampai keluar kotak dialog Local Connection Area
Status
Gambar

Gambar 16 : Dialog LAN Area Connection
4. Double-Click Internet Protocol (TCP/IP) yang ada di dalam kotak dialog Local Area Connection
Properties sampai keluar kotak dialog baru : Internet Protocol (TCP/IP) Protocol

Gambar 17 : Dialog TCP/IP
5. Klik use the following IP Address , Masukkan data-data IP Address. IP Address computer 1
adalah 192.168.1.1 komputer 2 : 192.168.1.2 dst, dengan subnet mask 255.255.255.0 . sedangkan
untuk gateway dikosongkan saja, karena jaringan LAN tidak terhubung ke jaringan LAN
lain/internet.

6. Setelah data-data setting TCP/IP ini dimasukkan, click OK untuk menutup kotak dialog Internet
Connection (TCP/IP) Properties.
7. click OK untuk menutup kotak dialog “Local Area Connection Properties”
 sampai keluar kotak dialog RUN, dan ketikkan CMD sampai keluar kotakà Run à8. Click Star
dialog baru “command”
9. Di kotak dialog “Command” tadi, ketikkan : ipconfig . Jika Ethernet dari computer belum
tersambung dengan LAN, akan keluar hasil seperti gambar dibawah.

Gambar 19 : Ipconfig (sorry gbrnya pake IP 134.114.70.143 dan ada gatewaynya, seharusnya
192.168.1.1 dan ga ada gatewaynya)
10. Lakukan test ping koneksi dengan computer sebelah (computer dengan ip address 192.168.1.2,
192.168.1.3, dsb), dengan mengetik pada command prompt:
C:\Documents and Settings\Lenovo>ping 192.168.1.2
Pinging 192.168.1.1 with 32 bytes of data:
Reply from 192.168.1.2: bytes=32 time=3ms TTL=64
Reply from 192.168.1.2: bytes=32 time=3ms TTL=64
Reply from 192.168.1.2: bytes=32 time=2ms TTL=64
Reply from 192.168.1.2: bytes=32 time=2ms TTL=64
SHARING FILE