Rumus dan pengelompakan

  1. tahun lalu

    permisi para mas Didi, mas Fuji dan yang lain...saya lampirkan file dan pertanyaan serta rumus yang sudah saya temukan di mbah google, mohon di bantu,,,terima kasih..

  2. para suhu ada yang bias membantu...?

  3. Caton

    7 Jul 2017 Terverifikasi Indonesia + 12.569 Poin

    Kemungkinan para master sedang cuti atau masih banyak kesibukan setelah libur kemaren... ;) Saya coba bantu sedikit ya. Untuk kasus yang mas @HARI.sp tanyakan, untuk pertanyaan nomor 2, jika ingin dikelompokkan seperti hasil pada kolom Q, formulanya bisa menggunakan formula pada kolom tersebut dengan perubahan pada referensi range $C$3:$C$28 menjadi $D$3:$D$28, sedangkan untuk range pada fungsi COUNTIF, dirujuk ke kolom dimana formula disusun, misalnya pada kolom R, maka rangenya dirujuk menjadi $R$1:R2.

    Sedangkan pertanyaan ketiga, kita bahas fungsi COUNTIF dahulu. Pada fungsi COUNTIF dalam formula di sel Q3, range kalkulasi dibuat semi dinamis ($Q$1:Q2). Sel awal range dibuat absolut ($Q$1), sedangkan sel akhir dibuat dinamis (Q2). Sehingga, jika formula pada sel Q3 disalin ke bawah, maka sel awal akan tetap dan sel akhirnya akan berubah sesuai lokasi baris. Akibatnya, nilai dalam range dari fungsi COUNTIF tersebut pun ikut berubah. Contoh untuk sel Q3 sampai dengan sel Q5, nilai dalam rangenya masing-masing menjadi:

    Sel Q3 -> $Q$1:Q2 = {0;0}
    Sel Q4 -> $Q$1:Q3 = {0;0;"B 9689 TIN"}
    Sel Q5 -> $Q$1:Q4 = {0;0;"B 9689 TIN";"B 9583 TIN"}

    Oleh karena formula yang digunakan adalah Array Formula, maka setiap nilai pada criteria dari fungsi COUNTIF akan dihitung eksistansinya pada nilai range. Sebagai gambaran, nilai (array) pada criteria adalah:

    {"B 9689 TIN";"B 9724 TIN";"B 9583 TIN";"B 9677 TIN";"B 9721 TIN";"B 9823 TIN";"B 9702 TIN";"B 9585 TIN";"B 9723 TIN";"B 9678 TIN";"B 9703 TIN";"B 9727 TIN";"B 9811 TIN";"B 9726 TIN";"B 9657 TIN";"B 9715 TIN";"B 9621 TIN";"B 9496 TIN";"B 9583 TIN";"B 9677 TIN";"B 9702 TIN";"B 9585 TIN";"B 9811 TIN";"B 9726 TIN";"B 9723 TIN";"B 9703 TIN"}

    Jika nilai-nilai pada criteria kita hitung pada range $Q$1:Q2 (sel Q3), range $Q$1:Q3 (sel Q4) dan range $Q$1:Q4 (sel Q5) maka hasilnya adalah:

    Sel Q3 -> {0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}
    Sel Q4 -> {1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}
    Sel Q5 -> {1;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}

    Pada sel Q3, hasil kalkulasi fungsi COUNTIF semua bernilai 0 karena tidak satupun nilai pada array criteria yang eksis pada array nilai range $Q$1:Q2. Pada sel Q4, hasilnya berbeda karena nilai pertama pada array criteria yakni B 9689 TIN eksis pada array range $Q$1:Q3. Pada formula di atas, untuk setiap nilai pada array criteria akan dilakukan kalkulasi sebanyak ukuran array range. Sehingga untuk sel Q3, nilai criteria B 9689 TIN akan dikalkulasi 2 kali (ukuran range Q$1:Q2). Apabila nilai criteria ada pada array range, hasil kalkulasi akan menjadi 1 per nilai pada array criteria, meskipun terdapat nilai yang sama (duplikat). Pada dasarnya, fungsi COUNTIF tersebut hanya akan menghasilkan nilai 0 atau 1 saja, dengan syarat nilai-nilai pada array bersifat unik terhadap nilai array criteria. Sebagai gambaran perbedaannya:

    COUNTIF({"A";"B"};{"A";"A";"D";"B"}) = {1;1;0;1}
    COUNTIF({"B";"B"};{"A";"A";"D";"B"}) = {0;0;0;2}

    Oleh karena targetnya adalah menghasilkan nilai-nilai unik (tanpa duplikat) dari kolom C, maka nilai-nilai yang sama pada array range dengan nilai-nilai pada array criteria tidak boleh muncul dua kali pada array range fungsi COUNTIF. Bagaimana jika ada sel kosong pada kolom C? Maka fungsi IF($C$3:$C$28="";1;0) akan membuat hasil dari fungsi COUNTIF tersebut menjadi 1 (ingat, hasil yang diinginkan hanya berupa angka 0 atau 1 saja).

    Lalu dari mana nilai B 9689 TIN bisa muncul pada array range COUNTIF? Nilai tersebut merupakan hasil dari fungsi INDEX dan MATCH. Oleh karena fungsi COUNTIF hanya akan menghasilkan nilai 0 atau 1 saja, maka fungsi MATCH pada formula tersebut di atas akan mencari nilai 0 pertama yang ditemukan. Jika kita tinjau hasil dari fungsi MATCH pada sel Q3 akan seperti:

    =MATCH(0;COUNTIF($Q$1:Q2;$C$3:$C$28&"")+IF($C$3:$C$28="";1;0);0)
    =MATCH(0;{0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0};0)
    =1

    Selanjutnya ketika kita tinjau hasil dari fungsi INDEX pada sel Q3:

    =INDEX($C$3:$C$28;MATCH(0;COUNTIF($Q$1:Q2;$C$3:$C$28&"")+IF($C$3:$C$28="";1;0);0))
    =INDEX($C$3:$C$28;MATCH(0;{0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0};0))
    =INDEX($C$3:$C$28;1)
    ="B 9689 TIN"

    Sehingga nilai pada sel Q3 akan menjadi B 9689 TIN. Sebagai gambaran hasil dari fungsi MATCH untuk sel Q3 sampai dengan sel Q5 adalah:

    Sel Q3 -> MATCH(0;{0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0};0) = 1
    Sel Q4 -> MATCH(0;{1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0};0) = 2
    Sel Q5 -> MATCH(0;{1;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0};0) = 3

    Mungkin itu dulu yang bisa saya bantu. Mudah-mudahan mas @HARI.sp bisa mendapatkan gambaran sederhananya. Untuk lebih jelasnya, ada banyak sumber bacaan mengenai hal tersebut di internet yang bisa dibaca lebih detil.... :D

  4. Dear Mas @Caton benar kemungkinan masih pada liburan, hehe...

    kembali ke topik, saya sudah mencobanya, dengan hasilnya seperti file yg saya lampirkan, tidak semuanya terbaca. saya berharap Mas @Caton bisa membuatkan filenya sebagai contoh. terima kasih sebelumnya..

  5. Caton

    7 Jul 2017 Terverifikasi Indonesia + 12.569 Poin

    Maaf mas @HARI.sp, yang tidak terbaca apanya? Sebagai informasi, formula pada kolom Q itu pada dasarnya bertujuan untuk menyusun daftar tanpa ada nilai yang sama (duplikat). Jadi jika formula tersebut mas terapkan pada kolom R dengan range lookup dan criteria adalah $D$3:$D$28, maka hasilnya adalah sebagaimana pada file yang mas lampirkan.

    Namun jika mas @HARI.sp ingin mendapatkan nilai dari kolom D (ALOKASI) berdasarkan nilai acuan pada kolom Q, maka mas bisa gunakan fungsi VLOOKUP atau INDEX+MACTH.

  6. Dear Mas @Caton bener kalau pakai vlookup bisa Mas, namun kalau di sort alokasinya maka tidak bisa ter sort misal A-Z

  7. Caton

    7 Jul 2017 Terverifikasi Indonesia + 12.569 Poin
    Di sunting tahun lalu oleh Caton

    Yang saya tahu, jika yang dimaksud adalah untuk mengurutkan hasil formulasi, misalnya pada kolom R yang merupakan hasil dari formula VLOOKUP, memang tidak bisa diurutkan menggunakan fitur Data>Sort. Fitur tersebut hanya berfungsi untuk nilai statis (non formulasi/kalkulasi).

  8. iya mas jika di sort gak mau, mungkin karena pengaruh sama formula yang di Q ya..,

  9. Caton

    7 Jul 2017 Terverifikasi Indonesia + 12.569 Poin

    Untuk memastikannya, mas bisa buat pada sheet baru. Misal, pada sel A1 masukkan nilai 10, pada sel A2 masukkan nilai 30 dan pada sel A3 masukkan nilai 20. Pada sel B1 masukkan formula =A1, pada sel B2 masukkan formula =A2 dan pada sel B3 masukkan formula =A3. Lalu coba sort kolom B.... :D

  10. Baik saya coba ya Mas..

 

atau Mendaftar untuk ikut berdiskusi!