Category Archives: Excel 2007-2010

Daftar Kata berurutan di Microsoft Excel


Microsoft Excel memiliki beberapa fitur yang dapat menghemat kita dalam mengetik kata berulang-ulang. Salah satunya adalah mengetikkan angka berurutan, tanggal dan bulan yang default nya dalam Bahasa Inggris. Misalnya jika kita mengetikkan angka 1 kemudian kita tarik fill handle (lihat gambar) dengan mouse (klik kiri dan tahan), maka kita dapat menghemat pengetikan. Kita mengetikkan Tuesday kemudian kita tarik fill handle-nya maka kita langsung mendapatkan Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday, Monday. Bila kita mengetikkan Bulan April maka kita akan mendapatkan bulan April, May , June, July dst. (lihat contoh)

  1. Ketik angka satu di sel A1 à

     

  2. Tarik fill handle pada
    ujung kanan bawah sel dengan mouse kiri dan tahan à

  3. Klik tanda dan pilih Fill Series untuk menampilkan urutan angka dari satu sampai misalnya 7

Untuk Hari dalam Bahasa Inggris

  1. Ketikkan nama hari di salah satu sel misalnya D1, kemudian klik mouse dan tahan serta tarik ke arah bawah secukupnya pada fill handle sehingga menjadi daftar sebagai berikut:



Untuk Bulan dalam Bahasa Inggris


 

Bagaimana kalau kita ingin daftar (list) dalam bahasa Indonesia atau bahkan daftar teman kita, daftar kode akuntansi atau daftar urutan pekerjaan dan sebagainya.

Maka langkah pertama adalah menambahkan daftar kata yang kita inginkan secara berurutan dalam sebuah range; (misalnya Senin Selasa dst)



Klik Edit Custom List sehingga muncul tampilan sebagai berikut:


Karena Isi field dalam Import list from cells:
sudah berisi daftar dalam sel H1:H7 maka kita hanya perlu mengklik tombol Import. Dan hasilnya adalah:


Klik Ok dan OK.

 

Untuk mencobanya kita ketik hari Rabu, kemudian tarik fill handle-nya sehingga menghasilkan sebagai berikut:


 

Cukup pelajaran hari ini tunggu selanjutnya manfaat list untuk daftar data (database dalam Excel).


 

Fungsi Logika Excel

Fungsi Logika

 

NOT

Jika nilai argumen logika nya TRUE maka hasilnya akan bernilai NOT. Demikian juga

sebaliknya.

Bentuk penulisannya

=NOT(nilai logika)

Contoh:

Isi sel A4 = 7

=NOT(A4>5) –maka hasilnya adalah TRUE

 

AND

 

Dipakai untuk menggabungkan nilai logika dari beberapa nilai logika menjadi satu nilai logika baru. Hasilnya akan bernilai TRUE bila seluruh logika yang digabungkan bernilai TRUE.

Bentuk penulisannya adalah

=AND(logika1,logika2;…)

 

Contoh

Jika isi A5 diantara lebih besar dari angka 1 dan lebih kecil dari angka 7

=AND(A5>1,A5<7) -7 akan bernilai TRUE

 

OR

 

Sama dengan logika And namun, untuk logika Or hasilnya akan bernilai TRUE jika salah satu nilai logika yang digunakan bernilai TRUE. Akan bernilai False bila tidak ada nilai logika yang bernilai TRUE.

Bentuk penulisannya adalah

=OR(logika1,logika2,…)

Contoh:

Jika salah satu dari sel B1, B2, B3 lebih besar dari 100 maka hasil akan True

=OR (B1>100, B2>100, B3>100)

Contoh Penerapan dengan IF dan AND

= IF(AND(A5>1,A5<7), “HASILNYA ADALAH” & A5, “HASIL SALAH”)

Jika isi A5 lebih besar dari angka 2 maka akan menghasilkan kalimat: HASILNYA ADALAH 2

Jika sebaliknya JIKA A5 lebih kecil sama dengan angka 1 dan lebih besar atau sama dengan 7 maka akan menampilkan HASIL SALAH

Fungsi Index dan Match

Fungsi Index dan Match

Fungsi INDEX adalah fungsi yang cukup sederhana, digunakan untuk mendapatkan nilai dari suatu cell berdasarkan pencarian pada suatu definisi table / data range worksheet kita.

Pencarian digunakan berdasarkan informasi posisi kolom dan baris, dengan acuan berupa kolom dan baris pertama table / data range tersebut.

Fungsi MATCH adalah fungsi yang digunakan untuk mencari suatu nilai dari suatu range yang terdapat pada suatu kolom atau baris, tapi tidak kedua-duanya.

Berikut Contoh Penggunaan Index dan Match

  1. Fungsi Index

  2. Fungsi Match

  3. Fungsi Match untuk Teks dan Angka

  4. Penerapan Fungsi Index dengan Fungsi Match Contoh 1

  5. Penerapan Fungsi Index dengan Fungsi Match 2

  6. Penerapan Fungsi Index dengan Fungsi Match 3

  7. Penerapan Fungsi Index dengan Fungsi Match 4

     

    Anda bisa berkreasi dengan cara menggabungkan fungsi-fungsi Excel agar mendapatkan rumus yang sesuai dengan kebutuhan Anda.

Fungsi Index

INDEX


    Apa yang dihasilkan Fungsi ini

    Fungsi ini memilih nilai dari sebuah tabel dengan mencari ke bawah angka baris tertebtu kemudian ke kanan sesuai dengan nomor kolom


    Fungsi ini dapat digunakan pada satu blok data atau beberapa blok data

    Sintaks

    Terdapat beberapa macam sintaks untuk fungsi ini

    sintaks 1

    =INDEX(RangeYgDicari,Koordinat)

    Dipergunakan jika RangeYgDicari terdapat pada satu kolom atau baris.

    Koordinat menunjukkan berapa baris atau kolom yang harus dicari untuk mengambil data dari range tersebut. Contoh di bawah ini menggunaka sintaks yang sama akan tetapi Coordinatenya berbeda


    sintaks 2

    =INDEX(RangeYgDicari,KoordinatBaris,KoordinatKolom)

    Sintaks ini dipergunakan jika kita menggunakan range yang terdiri dari beberapa Baris dan Kolom


    sintaks 3

    =INDEX(NamaRangeYgDicari,KoordinatBaris,KoordinatKolom,AreaYangDiambil)

    Sintaks ini dipergunakan jika rangenya terdiri dari beberapa area.

    Cara paling mudah adalah jika kita memberi nama kepada area tersebut dengan satu nama range

    AreaToPickFrom menunjukkan area-area mana yang akan dipergunakan

    Pada contoh di bawa kita menggunakan Utara dan Selatan sebagai satu nama menjadi UtaraDanSelatan


    Contoh

    Berikut adalah contoh lainnya yang lebih diperluas.

    Dalam contoh ini nama produk dan Triwulan dapat dimasukkan.

    FungsI =MATCH() dipergunakan untuk mencari posisi baris dan kolom yang dipergunakan.

    Kemudian posisi tersebut dipergunakan oleh fungsi =INDEX() untuk mencari data


     =INDEX(TimurDanBarat,MATCH(F100,C91:C93,0),MATCH(F101,D90:G90,0),IF(F102=C90,1,IF(F102=C95,2)))


BAGAIMANA AGAR EXCEL MENGENALI DATABASE KITA

BAGAIMANA AGAR EXCEL MENGENALI DATABASE KITA

Untuk dapat mengakses atau menggunakan perintah dan fungsi menu Data maka perlu mengenali data kita sebagai sebuah (misalnya fungsi dasar Sort dan Filter atau fungsi yang lebih lanjut yaitu Form, Subtotals dan Pivot Table).
DATABASE yang dimaksud adalah sekelompok column (yang biasa disebut fields oleh ahli database) yang memiliki judul tunggal untuk masing-masing column. Pilihlah format yang berbeda untuk cell judul, sehingga Excel mengenali tabel kita dan memperlakukan data tersebut sebagai sebuah DATABASE. Biasanya kita dapat menggunakan font tebal (bold font) sebagai judul dan diberi garis atau kotak sebagai batas antara judul (field) dengan datanya. Gunakan pemotongan kalimat (Text Wrap) melalui menu “Format/Cells/Alignment” untuk menuliskan data yang panjang dan gunakan “Alt/Enter” untuk memotong kalimat. Ingat satu data satu sel (lihat syarat data).
Database harus dikelilingi oleh baris-baris kosong (di atasnya atau di bawahnya) dan kolom-kolom kosong. Kalau database dimulai dari cell A1, maka kita tidak perlu menambahkan baris dan kolom kosong pada bagian kiri dan atasnya.
Database Excel mulai dari cell judul sampai baris terakhir (yang biasa disebut sebagai record oleh ahli database) dan berisi setidaknya satu nilai pada setiap kolom atau fields.
Untuk memastikan bahwa database kita dikenali oleh Excel, maka click pada salah satu sel yang berisi data atau judul dan gunakan menu Edit/Go to/Specials/Current Region. Bagian yang dipilih/disorot adalah database.
Berikut 4 contoh data Excel sebagai database dimana 3 contoh tidak dikenal dan satu contoh yang dianggap sebagai database.
Data berikut ini tidak dianggap sebagai sebuah database oleh Excel karena terdapat dua baris judul.

Data di bawah ini bukan database Excel karena baris ke dua (2) tidak kosong।

Data di bawah ini bukan database because kolom-kolom B, D, F dan H kosong (tidak berisi data). Hapus kolom yang kosong maka kita akan mendapatkan sebuah database yang dikenali oleh Excel.

Sedangkan tabel di bawah ini dikenali oleh Excel sebagai database. Terdapat 5 field (kolom) yaitu; ….. dan 7 record (baris data). Kolom E tidak kosong, ada sel judulnya dan baris ke 4 tidak kosing karena ada data pada field 1. Boleh terdapat banyak sel yang kosong dalam sebuah database database (kecuali sel-sel judul)tetapi tidak boleh ada baris-baris atau kolom-kolom yang kosong.

Posted by aurino at Tuesday, March 20, 2007  3:55 PM 0 comments      Links to this post

Import data web untuk Excel for Mac

Jika Anda ingin membuat spread sheet yang secara otomatis menampilkan data saham dalam portfolio saham dari web,  maka fitur tersebut dalam Excel: Mac2004, Mac2008 atau Mac2011 atau X dikenal sebagai Web Query. Sebagian dari web query tersebut terdapat pada menu Data. Pada menu Data pilih Get External Data > Run Saved Query.

Untuk penjelasan rinci dari web query ini terdapat pada

http://support.microsoft.com/default.aspx?scid=kb;en-us;274787

Daftar isinya

  • Definition of a Web Query File
  • How to Create a Web Query File
  • How to Use Static and Dynamic Parameters in a Web Query
  • Examples of Web Query Files

dan perlu Anda lihat juga

http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B180375

Selain itu Anda bisa juga mendownload add-on (yang walaupun tidak sebaik yang dapat dilakukan pada Excel ver Windows), tapi dapat mempermudah yaitu:

http://download.cnet.com/WebTableReport/3000-2155_4-36548.html

 


 

Menghilangkan Duplikasi Data Ms Excel

Data double atau Duplikasi data bisa juga disebut data ganda yang merupakan kesalahan dari proses pengisian data sering membuat para pengolah data sakit kepala. Data yang sama muncul atau dimasukkan secara berulangkali oleh operator saat memasukkan. Kesalahan data ganda ini dapat dicegah maupun diatasi dengan beberapa fasilitas yang ada pada Ms Excel.

Jika data belum dimasukkan maka ada dapat mencegah dengan menggunakan:

  1. Data validation (lihat: Menghilangkan Duplikasi Dalam Excel)
  2. Pemrograman VBA:


Jika data sudah dimasukkan maka kita dapat menghilangkan dengan cara:

  1. Program VBA (lihat: Menghilangkan Duplikasi Dalam Excel)
  2. Dengan menghilangkan duplikasi datanya melalui perintah Data lalu klik Remove Duplicates (Ms Excel 2010 atau Ms Excel 2007)

Misal kita memiliki data sebagai berikut:

Nama

Departemen

Tugas

Amir

Pemasaran

Senin

Andi

Personalia

Selasa

Keke

Pemasaran

Rabu

Dika

Produksi

Kamis

Amira

Personalia

Jumat

Sofia

Personalia

Sabtu

Keke

Pemasaran

Minggu

Dika

Produksi

Senin

Bambang

Produksi

Selasa

Bagus

Produksi

Rabu

Zahid

Pemasaran

Kamis

Charlie

Pemasaran

Jumat

Andi

Personalia

Sabtu

 

Lalu pilih atau blok range data yang akan Anda hilangkan duplikasi datanya

Setelah itu klik Tab Data lalu klik Tombol Remove Duplicates


Kemudian akan muncul kotak dialog Remove Duplicates



Ada beberapa pilihan, apakah akan menghapus berdasarkan kriteria kolom tertentu saja atau hapus berdasarkan kriteria semua kolom. Jika ingin menghapus hanya berdasarkan kolom nama saja maka Anda dapat menghilangkan tanda checklist (
Ö) pada kolom lainnya (kolom departemen dan kolom Tugas). Selanjutnya klik 

 



Hasil data yang double atau ganda berdasarkan nama akan dihapus, tanpa kita harus memilih satu persatu.




 

Menghilangkan Duplikasi Data Ms Excel

Data double atau Duplikasi data bisa juga disebut data ganda yang merupakan kesalahan dari proses pengisian data sering membuat para pengolah data sakit kepala. Data yang sama muncul atau dimasukkan secara berulangkali oleh operator saat memasukkan. Kesalahan data ganda ini dapat dicegah maupun diatasi dengan beberapa fasilitas yang ada pada Ms Excel.

Jika data belum dimasukkan maka ada dapat mencegah dengan menggunakan:

  1. Data validation (lihat: Menghilangkan Duplikasi Dalam Excel)
  2. Pemrograman VBA:


Jika data sudah dimasukkan maka kita dapat menghilangkan dengan cara:

  1. Program VBA (lihat: Menghilangkan Duplikasi Dalam Excel)
  2. Dengan menghilangkan duplikasi datanya melalui perintah Data lalu klik Remove Duplicates (Ms Excel 2010) atau
    menu Tools lalu klik Remove Duplicates (Ms Excel 2007)

Misal kita memiliki data sebagai berikut:

Nama

Departemen

Tugas

Amir

Pemasaran

Senin

Andi

Personalia

Selasa

Keke

Pemasaran

Rabu

Dika

Produksi

Kamis

Amira

Personalia

Jumat

Sofia

Personalia

Sabtu

Keke

Pemasaran

Minggu

Dika

Produksi

Senin

Bambang

Produksi

Selasa

Bagus

Produksi

Rabu

Zahid

Pemasaran

Kamis

Charlie

Pemasaran

Jumat

Andi

Personalia

Sabtu

 

Lalu pilih atau blok range data yang akan Anda hilangkan duplikasi datanya

Setelah itu klik Tab Data lalu klik Tombol Remove Duplicates


Kemudian akan muncul kotak dialog Remove Duplicates



Ada beberapa pilihan, apakah akan menghapus berdasarkan kriteria kolom tertentu saja atau hapus berdasarkan kriteria semua kolom. Jika ingin menghapus hanya berdasarkan kolom nama saja maka Anda dapat menghilangkan tanda checklist (
Ö) pada kolom lainnya (kolom departemen dan kolom Tugas). Selanjutnya klik 

 



Hasil data yang double atau ganda berdasarkan nama akan dihapus, tanpa kita harus memilih satu persatu.




 

Mengenal Ms. Access 2010

by: Sridhani Lestari Pamungkas

Microsoft Access adalah salah satu aplikasi program Microsoft Office yang dapat diterapkan dalam pekerjaan manajerial seperti pembuatan database persediaan dalam aktivitas perdagangan. Sebelum kita berbicara lebih lanjut mengenai keguanaan Microsoft Access dalam dunia bisnis terapan, terlebih dulu kita harus mengenal bagian – bagian penting dalam Ms. Access. Bagian – bagian tersebut antara lain :

  1. Tabel –>  merupakan bagian dalam Ms. Access yang berisi database keseluruhan dari tiap kategori. Tabel terdiri dari bebeapa kolom yang disebut Field.  Contoh: Tabel data mahasiswa Universitas Bakrie, Tabel Dosen Universitas Bakrie, dll.
  2. Query –> merupakan bagian dari Ms. Access yang dapat digunakan untuk menampilkan field – field tertentu dari bebrapa tabel. Field dari bebeapa tabel tersebut kemudian dibuat menjadi tabel baru.
  3. Form –> merupakan bagian dari Ms. Access yang digunakan dalam proses menginput data ke tabel/ database
  4. Report –> merupakan bagian dalam Ms. Access yang dapat digunakan dalam proses pelaporan database  dan dapat di print out.

 

Dalam setiap tabel kita harus memiliki sebuah Primary Key yang digunakan sebagai identitas utama tiap data yang diinput dalam tabel tersebut. Sifat dari Primary Key adalah No duplicates, artinya tidak mungkin ada data dalam sebuah tabel yang memiliki Primary Key yang sama.

Selain bagian – bagian penting seperti yang telah dijelaskan diatas, terdapat satu fungsi utama yang sering digunakan dalam Ms. Access yaitu, Relationship. Relationship dapat digunakan untuk menghubungkan beberapa tabel dengan “Tabel Penghubung”. “Tabel Penghubung” dapat dihubungkan dengan tabel lainnya karena salah satu field dalam “Tabel Penghubung” merupakan Primary Key dari tabel lain. Contohnya: Tabel Data konsumen dan Tabel Movie dapat dihubungkan dengan Tabel Pemesanan (“Tabel Penghubung”) karena field yang tersedia dalam tabel pemesanan berupa : ID Pemesanan, ID konsumen ( Primary Key dari tabel data konsumen ), ID Movie (Primary Key dari tabel Movie), Jumlah Pemesanan dan biaya pemesanan.

Fungsi Subtotal Excel

Rumus ini akan menghasilkan subtotal dari sebuah daftar data/list atau database. Biasanya kita menggunakan Perintah Subtotal
dalam Outline group yang ada di Menu Data. Setelah daftar subtotal list dibuat, maka anda dapat memodifikasi fungsi SUBTOTAL nya

Rumus:

SUBTOTAL(function_num, ref1, ref2, …)

 Function_num   adalah angka  1 s/d  11  (kalau menjumlahkan termasuk nilai yang tersembunyi) atau  101 s/d 111 (tidak termasuk nilai yang tersembunyi) yang menyatakan fungsi yang mana yang digunakan untuk menghitung subtotals dalam sebuah table data.

Function_num
(includes hidden values)

Function_num
(ignores hidden values)

Function

1

101

AVERAGE

2

102

COUNT

3

103

COUNTA

4

104

MAX

5

105

MIN

6

106

PRODUCT

7

107

STDEV

8

108

STDEVP

9

109

SUM

10

110

VAR

11

111

VARP

Ref1, ref2   adalah angka Range atau Referensi 1 s/d  254 yang akan disubtotalkan.

Contoh:

Data:

Perhatikan data 14 s.d 16 disembunyikan

Dengan menggunakan fungsi subtotal maka hasilnya adalah sebagai berikut: