XL-mania adalah komunitas yahoogroups Microsoft Excel terbesar di dunia. Melalui XL-mania kita dapat belajar Excel bersama. Fungsi yang dipelajari dari fungsi keuangan, tanggal dan waktu, matematika, statistik, kondisi, lookup, referensi, database, logika, teks, informasi, serta cara membuat user defined function. Selain itu, di XL-mania juga dibahas mengenai penggabungan beberapa fungsi Excel menjadi sebuah formula yang unik, array, dan megaformula. Fitur-fitur Excel yang dibahas juga luas, dari conditional formatting, goal seek, chart, pivot table, macro / VBA, business model, financial modeling, solver, dan lain-lain. Dapatkan tips dan trik excel gratis dari XL-mania! Ingat, malu bertanya, kerja manual!

August 11, 2008

menjabarkan data sesuai jumlah yang ditentukan

Pertanyaan:

"Hai semuaaaaaa... Saya lagi belajar Excel nih. Kemaren liat-liat arsip XL-mania. Keren banget yang filteran data unik pake rumusnya. Penasaran aja, kalo misalnya kebalikannya bisa ga? Jadi ada data, di kanannya ada jumlahnya. Dari dua kolom ini bisa muncul satu kolom lagi, isinya daftar dari data pertama tadi, tapi udah sesuai jumlah di kanannya. Kalo bingung coba deh liat attachmentnya. Thanx yaaaaa..... Kalo ga bisa ga papa kok... Tapi kalo bisa... Ntar ada
hadiahnya!" - Mulan Mei, Medan.


Jawab:
Uhm... Kirim aja hadiahnya! Hehehe.... Di E3 tulis: =IF(E2="item",$B$3,OFFSET($B$2,--(COUNTIF($E2:E$3,E2)=ROUND(VLOOKUP(E2,$B$3:$C$9,2,FALSE),0))+MATCH(E2,$B$3:$B$9,0),,,)), kemudian copy ke range E4 dan E15.


Penjelasan:

  1. =IF(E2="item",$B$3... berfungsi melihat apakah cell tersebut adalah cell teratas, jika ya maka dikeluarkan data paling atas, yaitu $B$3, jika tidak ke langkah ke-2.

  2. ...(COUNTIF($E2:E$3,E2)... berfungsi untuk melihat sudah berapa kali data di atas cell yang bersangkutan muncul.

  3. ...VLOOKUP(E2,$B$3:$C$9,2,FALSE)... berfungsi untuk melihat berapa kali data ini ingin ditampilkan sesuai tabel pertama.

  4. ...ROUND(...,0)... berfungsi untuk berjaga-jaga seandainya nilai pada kolom C bukan integer. Fungsi ini dapat dihilangkan jika semua angka pada kolom C adalah integer (tidak mengandung desimal atau pecahan).

  5. ...COUNTIF(...)=ROUND(...)... berfungsi untuk melihat berapa banyak data yang sudah teraktualisasi di kolom E. Jika sudah sesuai dengan yang diinginkan pada langkah 3, maka akan menghasilkan nilai TRUE, jika belum akan menghasilkan FALSE.

  6. ...MATCH(E2,$B$3:$B$9,0)... berfungsi untuk mencari urutan ke berapakah data E pada kolom B.

  7. ...--... mengubah logika TRUE menjadi 1 dan FALSE menjadi 0.

  8. Artinya jika langkah 5 sudah menghasilkan TRUE, maka dia akan menambahkan angka yang dihasilkan pada langkah 6, sehingga data yang ditampilkan berikutnya bergeser 1 baris dari hasil langkah 6. Cara menggesernya adalah dengan fungsi pada langkah ke-9.

  9. ...OFFSET($B$2,x,)... berfungsi untuk menampilkan data pada x baris setelah $B$2, dimana x sendiri merupakan operasi
File:
Anda dapat mendownload materi tips ini di sini
[harus belajar Excel di XL-mania, member XL-mania, daftar dulu ya di XL-maniaaaaaaaaa!]

20 comments:

  1. Makan apa sih mas bisa bikin rumus aneh-aneh gini?

    ReplyDelete
  2. Mas, gimana cara lookup berdasar 2 kondisi?

    ReplyDelete
  3. Mas gimana sih cara ngilangin NA() hasil vlookup atau rumus lain?

    ReplyDelete
  4. Cara cari nama belakang tapi pemisahnya bukan spasi gimana? huruf Besar misalnya.

    ReplyDelete
  5. @ panda
    nasi campur :D

    @ aramis
    http://www.xl-mania.com/2008/11/lookup-dengan-banyak-kriteria.html

    @ doriane
    =if(isna(rumus),"",rumus)

    @ veronica
    ntar dibahas ya

    ReplyDelete
  6. Wuiiiiiihh.. mantabb gan.. xixixixi ... uhmn .. jadi mau otodidak lagi nih xl nya.. ada info job ga ..xixixixi mau donk di pm aja yah ..

    ReplyDelete
  7. Bos Abimono, bagaimana kalo data di balik?
    apel
    apel
    apel
    jeruk
    apel

    maka:
    apel = 4
    jeruk = 1

    tks.

    ReplyDelete
  8. boz abimono, saya juga ingin tau. bagaimana kalau kebalikannya, seprti yg dimaksud pak Flexter Sartono

    ReplyDelete
  9. mas kalau misalnya saya ngetik angka 1 terus di enter itu biar dibawahnya langsung muncul tulisan "satu" bagaimana yaa ?
    need help sangat .
    terimakasih ^^

    ReplyDelete
  10. bs minta tolong pecahin masalah saya via email ga?

    trims,.

    aqajahhh@yahoo.com

    ReplyDelete
  11. @flexter : bisa menggunakan fungsi countif

    ReplyDelete
  12. XL-Mania, sangat membantu saya dalam pekerjaan... web yang sangat berguna...

    ReplyDelete
  13. bagaimana kalau saya inginn mengambil data dari
    apel
    jeruk
    jeruk
    mangga
    mangga
    lemon
    lemon

    menjadi :

    apel
    jeruk
    lemon
    mangga

    ReplyDelete
  14. pengen tau dong mas kalo di balik:

    apel
    apel
    apel
    jeruk
    apel

    maka:
    apel = 4
    jeruk = 1

    caranya bagaimana ya

    ReplyDelete
  15. met malam... saya lagi bingung nih lagi bikin kolom nilai, malah g nyambung. tolong dong..

    "No.
    Urut" Jenis Soal SOAL URAIAN (ESAY) SKOR "NILAI
    AKHIR" "Ketun
    tasan"
    Nomor Soal 1 2 3 4 5 6 7 8 9 10
    1 1 5 5 1 4 2 2 1 3
    1 ABDULLAH HUSEN L 1 1 5 4 1 3 2 2 1 3 23 89 Tuntas
    2 ADE ANGGARA PRADANA L 1 1 3 3 1 2 1 2 1 1 16 50 Blm Tuntas
    3 ADI KURNIAWAN L 1 1 5 5 1 4 2 2 1 3 25 100 Tuntas
    4 ANANG WIDIYANTO**) L 1 1 3 2 1 2 2 1 0 1 14 39 Blm Tuntas
    5 Aninda Puspita P 1 0 1 1 0.5 0 0 0 0 1 4.5 -17 Blm Tuntas
    6 ARDI CANDRA WIRAWAN L 1 1 2 2 1 2 2 2 1 1 15 45 Blm Tuntas
    7 ARIF PRASETYO L 1 1 4 4 1 4 1 1 1 1 19 67 Tuntas

    ReplyDelete
  16. salam kenal mas,,
    bisa minta tolong ajarkan rumus ( formula ) untuk kelompokkan data,,misalnya :
    dalam sheet1 berisi data dengan nama, kelas, kota

    maunya saya dalam sheet2 nanti sdh ada tabel data yg di kelompokkan berdasarkan kota ( diambil dari sheet1 tadi) mohon di bantu y mas
    thx..
    email:uchiloicorporation2@yahoo.co.id

    ReplyDelete
  17. mas abie saya mau tanya kalau datanya dibalik seperti pertanyaan Flexter Sartono gimana ya... makasih ^^

    ReplyDelete
  18. mas, klo rumus untuk membuat nomor urut kebawah dengan rumus gimana ya mas...
    mohon bantuannya?

    ReplyDelete
  19. mas abie, gimana klo data yang di B tidak berurutan....

    ReplyDelete

Silahkan berkomentar tentang XL-mania...