@rukilang Oh iya pak, kalo fungsi yang ini bisa tolong dijelaskan sedikit pak?
((INDEX(db.Katalog.Sumber,0,1)="")*10^5),ROW($A1)))-5,2),"")
Tujuan dari formula tersebut adalah untuk membentuk sebuah array berupa indeks baris sesuai baris data pada sheet TES 2 yang akan diambil. Selengkapnya:
SUMPRODUCT(SMALL(((INDEX(db.Katalog.Sumber;0;1)<>"")*ROW(db.Katalog.Sumber))+((INDEX(db.Katalog.Sumber;0;1)="")*10^5);ROW($A1)))-5
Formula tersebut sebenarnya bisa diselesaikan dengan Array Formula. Hanya saja, karena pada sheet TES 2 sudah ada beberapa Array Formula, jadikan saya gunakan Non Array Formula. Intinya begini:
[1]. Asumsikan, pada sheet TES 2 sudah terisi data mulai baris ke-6 sampai dengan baris ke-35. Hasil kode yang akan diambil adalah pada range O6:P35. Agar mudah dijadikan acuan, maka saya buatkan nama range db.Katalog.Sumber yang merupakan nama range dinamis (formulanya lihat pada Name Manager). Dengan menggunakan nama range tersebut, pada dasarnya formula sudah mengacu ke range O6:P35 pada sheet TES 2.
[2]. Dengan adanya tabel data yang sudah ditentukan melalui nama range db.Katalog.Sumber, proses selanjutnya adalah menyusun data dari sheet TES 2 tanpa menampilkan baris data kosong (Empty Cells atau Blank Cells), secara Ascending. Oleh karena saya menetapkan tidak akan menggunakan Array Formula, maka ditentukan yang menjadi kolom acuan untuk memeriksa setiap baris data ada pada kolom pertama dari tabel data pada nama range db.Katalog.Sumber. Jika dinarasikan:
Untuk setiap baris data pada kolom pertama dari db.Katalog.Sumber yang tidak kosong, maka kembalikan indeks baris data tersebut. Jika baris data pada kolom pertama dari db.Katalog.Sumber kosong, maka kembalikan nilai 10^5 (100000).
Nilai yang dikembalikan jika baris data kosong pada dasarnya tidak mutlak 100000. Berapapun bisa, syaratnya, nilai tersebut harus lebih besar dari baris terakhir dari range data. Jika range data adalah O6:P35, maka berarti nilai tersebut harus berada di atas 35. Namun karena sifat tabel data dari nama range db.Katalog.Sumber adalah dinamis, sebaiknya gunakan saja nilai yang cukup besar. Bisa saja kalau memang mau dibuatkan formula. Namun jauh lebih simple dengan menggunakan notasi 10^5 ... :)
[3]. Untuk setiap baris data pada kolom pertama dari db.Katalog.Sumber yang tidak kosong, formulanya adalah:
= ((INDEX(db.Katalog.Sumber;0;1)<>"")*ROW(db.Katalog.Sumber))
3.1. INDEX(db.Katalog.Sumber;0;1)
menghasilkan deret nilai (array) berupa nilai pada kolom O (range O6:O35).
3.2. (INDEX(db.Katalog.Sumber;0;1)<>"")
menghasilkan deret nilai (array) berupa nilai TRUE atau FALSE sesuai kondisi. Misalkan, jika nilai pada sel O6 kosong, maka hasilnya adalah FALSE sedangkan jika tidak kosong, maka hasilnya adalah TRUE.
3.3. ((INDEX(db.Katalog.Sumber;0;1)<>"")*ROW(db.Katalog.Sumber))
menghasilkan deret nilai indeks baris data jika nilai hasil dari poin 3.2 adalah TRUE, dan 0 (nol) jika nilai hasil dari poin 3.2 adalah FALSE. Contoh:
= {6;7;8;9;10;11;12;13;14;15;16;17;18;0;20;21;0;23;24;0;26;27;28;29;30;31;32;33;34;35}
[4]. Untuk setiap baris data pada kolom pertama dari db.Katalog.Sumber yang kosong, formulanya adalah:
= ((INDEX(db.Katalog.Sumber;0;1)="")*10^5)
4.1. INDEX(db.Katalog.Sumber;0;1)
menghasilkan deret nilai (array) berupa nilai pada kolom O (range O6:O35).
4.2. (INDEX(db.Katalog.Sumber;0;1)="")
menghasilkan deret nilai (array) berupa nilai TRUE atau FALSE sesuai kondisi.
4.3. ((INDEX(db.Katalog.Sumber;0;1)<>"")*10^5)[/b]
menghasilkan nilai 100000 jika nilai hasil dari poin 4.2 adalah TRUE, dan 0 (nol) jika nilai hasil dari poin 4.2 adalah FALSE. Contohnya:
= {0;0;0;0;0;0;0;0;0;0;0;0;0;100000;0;0;100000;0;0;100000;0;0;0;0;0;0;0;0;0;0}
[5]. Jika hasil dari poin ke-3 dan ke-4 digabungkan, maka akan terbentuk deret nilai ± seperti berikut:
={6;7;8;9;10;11;12;13;14;15;16;17;18;100000;20;21;100000;23;24;100000;26;27;28;29;30;31;32;33;34;35}
[6]. Untuk mendapatkan indeks baris secara Ascending, maka digunakan fungsi SMALL. Oleh karena sintaks dari fungsi SMALL adalah:
SMALL(array, k)
maka hasil formulasi pada poin ke-5 dijadikan parameter Array, sedangkan indeks array ditentukan melalui notasi ROW($A1). Dengan demikian, jika diurai, hasil dari fungsi SMALL untuk baris pertama akan terkalkulasi ± sebagai berikut:
=SMALL({6;7;8;9;10;11;12;13;14;15;16;17;18;100000;20;21;100000;23;24;100000;26;27;28;29;30;31;32;33;34;35}, ROW($A1))
=SMALL({6;7;8;9;10;11;12;13;14;15;16;17;18;100000;20;21;100000;23;24;100000;26;27;28;29;30;31;32;33;34;35}, 1)
= 6
[7]. Oleh karena fungsi SMALL di atas mengkalkulasi data array, untuk menghindari Array Formula, digunakan fungsi SUMPRODUCT.
Demikian mas @Rukilang. Semoga tercerahkan.