Dear,
Terimakasih solusi dan ilmunya,
bermain dengan logika ya,
harus banyak latihan-latihan lagi nihh,
Salam,
Ade
On Fri, 19 Oct 2012 06:05:20 +0700, hendrik karnadi <[email protected]> wrote:
> Sekedar selingan, pake macro.
>
> Salam,
> Hendrik Karnadi
>
>
>
> ----- Forwarded Message -----
> From: Mr. Kid <[email protected]>
> To: [email protected]
> Sent: Thursday, 18 October 2012, 23:47
> Subject: Re: [belajar-excel] Index Data Beberapa Kolom atau Lebih
>
>
> 1. Untuk cara 2, ada array formula bantu berbunyi : (cell M3)
> =MIN(IF(COUNTIF(N$2:N2,$B$3:$F$14)=0,ROW($B$3:$F$14)*1000+COLUMN($B$3:$F$14)))
> Kondisi yang ada baru 1, yaitu bagian CountIF. Belum ada pengkondisian
> berdasar ada atau tidaknya teks dalam data.
> Pengkondisian untuk mengambil cell yang ada datanya saja adalah dengan
> memeriksa jumlah karakter yang ada di dalam cell tersebut.
> ( Len($B$3:$F$14) > 0 )
>
> Jadi, 2 kondisi yang harus digunakan adalah :
>>> ( COUNTIF(N$2:N2,$B$3:$F$14)=0 )
>>> ( Len($B$3:$F$14) > 0 )
> Keduanya harus terpenuhi, artinya harus dihubungkan dengan operator AND
> yang setara dengan kegiatan operasi matematis perkalian.
> Jadi kedua kondisi akan diekspresikan menjadi :
> ( COUNTIF(N$2:N2,$B$3:$F$14)=0 )*( Len($B$3:$F$14) > 0 )
>
> Maka bagian CountIF dalam array formula diganti dengan 2 pengkondisian
> baru ini akan menjadi :
> =MIN(IF( ( COUNTIF(N$2:N2,$B$3:$F$14)=0 )*( Len($B$3:$F$14) > 0 )
> ,ROW($B$3:$F$14)*1000+COLUMN($B$3:$F$14)))
>>> Jangan lupa untuk di-Enter sebagai Array formula dengan CTRL SHIFT
>>> ENTER <<
>
> 2. Menghitung jumlah unique item (seperti cell H2 atau M2)
> Formula yang ada :
> =SUMPRODUCT(1/COUNTIF(B3:F14,B3:F14))
> Ketika ada cell di B3:F14 yang tidak ada datanya, maka akan menghasilkan
> hasil countif akan 0. Maka akan ada item yang kalkulasinya berupa 1/0
> yang menghasilkan error value #DIV/0!
> Maka efek ketika hasil CountIF adalah 0 harus dinetralisir dengan suatu
> nilai yang bukan 0. CountIF menghasilkan 0 jika tidak ada data, maka
> kondisi tidak ada data bisa diubah hasilnya menjadi 1 yang setara TRUE.
> Maka Len(B3:F14)=0 akan bernilai TRUE ketika B3:F14 tidak ada datanya.
> Bunyi kondisi terhadap CountIF adalah sebagai berikut :
>>> Jika hasil CountIF adalah 0, maka isi dengan Len(B3:F14)=0
>>> Jika hasil CountIF bukan 0, maka isi dengan hasil CountIF
> Secara harfiah, translatenya ke bahasa formula menjadi :
> IF( COUNTIF(B3:F14,B3:F14)= 0 , ( Len(B3:F14)=0 ) ,
> COUNTIF(B3:F14,B3:F14) )
> Dari kalimat ini, sebenarnya jika diperhatikan secara seksama, akan ada
> benang merah berbunyi :
> "Nilai hasil CountIF atau Len(B3:F14)=0"
> yang bahasa formulanya :
> ( COUNTIF(B3:F14,B3:F14) + ( Len(B3:F14)=0 ) )
> Hasilnya, akan didapat bahwa seluruh cell yang tidak ada isinya akan
> bernilai TRUE (setara 1).
> Maka, 1/COUNTIF(B3:F14,B3:F14)
> akan menjadi :
> 1/( COUNTIF(B3:F14,B3:F14) + ( Len(B3:F14)=0 ) )
> tetapi, akan terjadi 1/1 ketika cell tidak ada isinya, karena 1/( 0 +
> TRUE ) = 1/( 0 + 1 ) = 1/1
> Hal ini akan menghasilkan jumlah unique yang tidak tepat. Oleh sebab itu
> bagian 1 (biru) dalam :
> 1/( COUNTIF(B3:F14,B3:F14) + ( Len(B3:F14)=0 ) )
> harus berupa pemfilteran bahwa jika cell berisi data maka bernilai 1 dan
> jika tidak berisi data bernilai 0.
> ( Len(B3:F14)>0 )
> Maka formula lengkap :
> =SUMPRODUCT(( Len(B3:F14)>0 ) / ( COUNTIF(B3:F14,B3:F14) + (
> Len(B3:F14)=0 ) ) )
>
> 3. Cara1.
> untuk versi xl2007 (dengan IFError) array formulanya (J3)
> =IFERROR(INDEX($H$3:$H$63,MATCH(0,COUNTIF(J$2:J2,$H$3:$H$63),0)),"")
> untuk semua versi (berdasar jumlah item unique yang mungkin) : (K3)
> =IF(ROW()-2>$H$2,"",INDEX($H$3:$H$63,MATCH(0,COUNTIF(K$2:K2,$H$3:$H$63),0)))
> memiliki benang merah pada bagian yang biru :
> MATCH(0,COUNTIF(K$2:K2,$H$3:$H$63),0)
> Bahasa manusianya :
> "Cari posisi angka 0 pada suatu data array yang berupa nilai-nilai
> jumlah cacah (CountIF) data dalam $H$3:$H$63 di suatu area K$2:K2 yang
> pencariannya harus bener-bener persis [0]"
> Kalimat 'nilai-nilai jumlah cacah data dalam $H$3:$H$63 di suatu area
> K$2:K2' akan menghasilkan angka bukan 0 jika memang telah ada di K$2:K2
> dan menghasilkan 0 jika belum ada di K$2:K2. Padahal area K$2:K2 adalah
> hasil (output). Artinya, kalau sampai hasil CountIF adalah 0, maka data
> itu belum ada di area output alias data baru. Dengan demikian, fungsi
> Match tugasnya adalah selalu mendapatkan posisi data baru (data yang
> belum ada di output).
> Pada kondisi itu, tidak ada pengkondisian bahwa data di $H$3:$H$63
> bukanlah bernilai 0.
> Jelas sudah diketahui bahwa data baru (belum ada di output) adalah yang
> COUNTIF(K$2:K2,$H$3:$H$63)=0
> Kondisi data di $H$3:$H$63 bukanlah bernilai 0 dapat diekspresikan
> sebagai :
> ( $H$3:$H$63 <> 0 )
>
> Sampai disini, sudah ada 2 kondisi :
> 1. data baru (belum ada dioutput) :
> ( COUNTIF(K$2:K2,$H$3:$H$63)=0 )
> 2. tidak adanya nilai di data $H$3:$H$63
> ( $H$3:$H$63 <> 0 )
> Keduanya harus terpenuhi berarti setara AND yang setara perkalian.
> Maka array data dalam Match akan berupa :
> ( COUNTIF(K$2:K2,$H$3:$H$63)=0 ) * ( $H$3:$H$63 <> 0 )
> Hasilnya adalah 1 (untuk data baru dan nilai data di $H$3:$H$63 bukanlah
> 0 ) atau 0 (untuk salah satu dari kedua kondisi yang tak terpenuhi).
> Jadi, yang menjadi lookup value (dicari oleh match) bukan lagi angka 0,
> melainkan angka 1
> Nah... bagian Match akan menjadi :
> MATCH(1 , ( COUNTIF(K$2:K2,$H$3:$H$63)=0 ) * ( $H$3:$H$63 <> 0 ) , 0 )
>
> Array formula lengkap masing-masing :
> 1. untuk versi xl2007 :
> =IFERROR(INDEX($H$3:$H$63,MATCH(1 , ( COUNTIF(K$2:K2,$H$3:$H$63)=0 ) * (
> $H$3:$H$63 <> 0 ) , 0 ) ),"")
>
> 2. untuk semua versi :
> =IF(ROW()-2>$H$2,"",INDEX($H$3:$H$63,MATCH(1 , (
> COUNTIF(K$2:K2,$H$3:$H$63)=0 ) * ( $H$3:$H$63 <> 0 ) , 0 ) ))
>
> Kesimpulannya :
> Pengubahan formula karena adanya perubahan kondisi (bertambah atau
> berkurang jumlah kondisinya), difokuskan pada penyusunan kondisi-kondisi
> baru yang menyertakan kondisi awal (sebelum berubah) secara konsisten.
> Seperti kondisi bahwa jika CountIF 0 artinya sebagai data baru bergabung
> bersama kondisi baru berupa ada data di suatu area (dengan Len misalnya).
>
> Wassalam,
> Kid.
>
>
>
>
> 2012/10/18 M.Ade Alfarid <[email protected]>
>
> Dear,
>>
>>
>> Untuk menghilangkan angka 0 pada list index,
>> tambah rumus apa?
>>
>> Ade
>>
>>
>>
>>
>> On Mon, 15 Oct 2012 16:30:55 +0700, Mr. Kid <[email protected]> wrote:
>>
>>
>>> Coba file terlampir.
>>> Ada yang dibantu oleh sebuah kolom untuk menyusun data multi kolom
>>> menjadi
>>> data sekolom.
>>>
>>> Wassalam,
>>> Kid
>>>
>>>
>>> 2012/10/15 M.Ade Alfarid <[email protected]>
>>>
>>>> **
>>
>>>>
>>>>
>>>> BeExceller,
>>>>
>>>> Bagaimana untuk mengurut banyak data beberapa kolom,
>>>> data dobel dihitung sekali saja?
>>>> Terimakasih
>>>> --
>>>> ade
>>>>
>>
>
> Sekedar selingan, pake macro.
>
> Salam,
> Hendrik Karnadi
>
>
>
> ----- Forwarded Message -----
> From: Mr. Kid <[email protected]>
> To: [email protected]
> Sent: Thursday, 18 October 2012, 23:47
> Subject: Re: [belajar-excel] Index Data Beberapa Kolom atau Lebih
>
>
> 1. Untuk cara 2, ada array formula bantu berbunyi : (cell M3)
> =MIN(IF(COUNTIF(N$2:N2,$B$3:$F$14)=0,ROW($B$3:$F$14)*1000+COLUMN($B$3:$F$14)))
> Kondisi yang ada baru 1, yaitu bagian CountIF. Belum ada pengkondisian
> berdasar ada atau tidaknya teks dalam data.
> Pengkondisian untuk mengambil cell yang ada datanya saja adalah dengan
> memeriksa jumlah karakter yang ada di dalam cell tersebut.
> ( Len($B$3:$F$14) > 0 )
>
> Jadi, 2 kondisi yang harus digunakan adalah :
>>> ( COUNTIF(N$2:N2,$B$3:$F$14)=0 )
>>> ( Len($B$3:$F$14) > 0 )
> Keduanya harus terpenuhi, artinya harus dihubungkan dengan operator AND
> yang setara dengan kegiatan operasi matematis perkalian.
> Jadi kedua kondisi akan diekspresikan menjadi :
> ( COUNTIF(N$2:N2,$B$3:$F$14)=0 )*( Len($B$3:$F$14) > 0 )
>
> Maka bagian CountIF dalam array formula diganti dengan 2 pengkondisian
> baru ini akan menjadi :
> =MIN(IF( ( COUNTIF(N$2:N2,$B$3:$F$14)=0 )*( Len($B$3:$F$14) > 0 )
> ,ROW($B$3:$F$14)*1000+COLUMN($B$3:$F$14)))
>>> Jangan lupa untuk di-Enter sebagai Array formula dengan CTRL SHIFT
>>> ENTER <<
>
> 2. Menghitung jumlah unique item (seperti cell H2 atau M2)
> Formula yang ada :
> =SUMPRODUCT(1/COUNTIF(B3:F14,B3:F14))
> Ketika ada cell di B3:F14 yang tidak ada datanya, maka akan menghasilkan
> hasil countif akan 0. Maka akan ada item yang kalkulasinya berupa 1/0
> yang menghasilkan error value #DIV/0!
> Maka efek ketika hasil CountIF adalah 0 harus dinetralisir dengan suatu
> nilai yang bukan 0. CountIF menghasilkan 0 jika tidak ada data, maka
> kondisi tidak ada data bisa diubah hasilnya menjadi 1 yang setara TRUE.
> Maka Len(B3:F14)=0 akan bernilai TRUE ketika B3:F14 tidak ada datanya.
> Bunyi kondisi terhadap CountIF adalah sebagai berikut :
>>> Jika hasil CountIF adalah 0, maka isi dengan Len(B3:F14)=0
>>> Jika hasil CountIF bukan 0, maka isi dengan hasil CountIF
> Secara harfiah, translatenya ke bahasa formula menjadi :
> IF( COUNTIF(B3:F14,B3:F14)= 0 , ( Len(B3:F14)=0 ) ,
> COUNTIF(B3:F14,B3:F14) )
> Dari kalimat ini, sebenarnya jika diperhatikan secara seksama, akan ada
> benang merah berbunyi :
> "Nilai hasil CountIF atau Len(B3:F14)=0"
> yang bahasa formulanya :
> ( COUNTIF(B3:F14,B3:F14) + ( Len(B3:F14)=0 ) )
> Hasilnya, akan didapat bahwa seluruh cell yang tidak ada isinya akan
> bernilai TRUE (setara 1).
> Maka, 1/COUNTIF(B3:F14,B3:F14)
> akan menjadi :
> 1/( COUNTIF(B3:F14,B3:F14) + ( Len(B3:F14)=0 ) )
> tetapi, akan terjadi 1/1 ketika cell tidak ada isinya, karena 1/( 0 +
> TRUE ) = 1/( 0 + 1 ) = 1/1
> Hal ini akan menghasilkan jumlah unique yang tidak tepat. Oleh sebab itu
> bagian 1 (biru) dalam :
> 1/( COUNTIF(B3:F14,B3:F14) + ( Len(B3:F14)=0 ) )
> harus berupa pemfilteran bahwa jika cell berisi data maka bernilai 1 dan
> jika tidak berisi data bernilai 0.
> ( Len(B3:F14)>0 )
> Maka formula lengkap :
> =SUMPRODUCT(( Len(B3:F14)>0 ) / ( COUNTIF(B3:F14,B3:F14) + (
> Len(B3:F14)=0 ) ) )
>
> 3. Cara1.
> untuk versi xl2007 (dengan IFError) array formulanya (J3)
> =IFERROR(INDEX($H$3:$H$63,MATCH(0,COUNTIF(J$2:J2,$H$3:$H$63),0)),"")
> untuk semua versi (berdasar jumlah item unique yang mungkin) : (K3)
> =IF(ROW()-2>$H$2,"",INDEX($H$3:$H$63,MATCH(0,COUNTIF(K$2:K2,$H$3:$H$63),0)))
> memiliki benang merah pada bagian yang biru :
> MATCH(0,COUNTIF(K$2:K2,$H$3:$H$63),0)
> Bahasa manusianya :
> "Cari posisi angka 0 pada suatu data array yang berupa nilai-nilai
> jumlah cacah (CountIF) data dalam $H$3:$H$63 di suatu area K$2:K2 yang
> pencariannya harus bener-bener persis [0]"
> Kalimat 'nilai-nilai jumlah cacah data dalam $H$3:$H$63 di suatu area
> K$2:K2' akan menghasilkan angka bukan 0 jika memang telah ada di K$2:K2
> dan menghasilkan 0 jika belum ada di K$2:K2. Padahal area K$2:K2 adalah
> hasil (output). Artinya, kalau sampai hasil CountIF adalah 0, maka data
> itu belum ada di area output alias data baru. Dengan demikian, fungsi
> Match tugasnya adalah selalu mendapatkan posisi data baru (data yang
> belum ada di output).
> Pada kondisi itu, tidak ada pengkondisian bahwa data di $H$3:$H$63
> bukanlah bernilai 0.
> Jelas sudah diketahui bahwa data baru (belum ada di output) adalah yang
> COUNTIF(K$2:K2,$H$3:$H$63)=0
> Kondisi data di $H$3:$H$63 bukanlah bernilai 0 dapat diekspresikan
> sebagai :
> ( $H$3:$H$63 <> 0 )
>
> Sampai disini, sudah ada 2 kondisi :
> 1. data baru (belum ada dioutput) :
> ( COUNTIF(K$2:K2,$H$3:$H$63)=0 )
> 2. tidak adanya nilai di data $H$3:$H$63
> ( $H$3:$H$63 <> 0 )
> Keduanya harus terpenuhi berarti setara AND yang setara perkalian.
> Maka array data dalam Match akan berupa :
> ( COUNTIF(K$2:K2,$H$3:$H$63)=0 ) * ( $H$3:$H$63 <> 0 )
> Hasilnya adalah 1 (untuk data baru dan nilai data di $H$3:$H$63 bukanlah
> 0 ) atau 0 (untuk salah satu dari kedua kondisi yang tak terpenuhi).
> Jadi, yang menjadi lookup value (dicari oleh match) bukan lagi angka 0,
> melainkan angka 1
> Nah... bagian Match akan menjadi :
> MATCH(1 , ( COUNTIF(K$2:K2,$H$3:$H$63)=0 ) * ( $H$3:$H$63 <> 0 ) , 0 )
>
> Array formula lengkap masing-masing :
> 1. untuk versi xl2007 :
> =IFERROR(INDEX($H$3:$H$63,MATCH(1 , ( COUNTIF(K$2:K2,$H$3:$H$63)=0 ) * (
> $H$3:$H$63 <> 0 ) , 0 ) ),"")
>
> 2. untuk semua versi :
> =IF(ROW()-2>$H$2,"",INDEX($H$3:$H$63,MATCH(1 , (
> COUNTIF(K$2:K2,$H$3:$H$63)=0 ) * ( $H$3:$H$63 <> 0 ) , 0 ) ))
>
> Kesimpulannya :
> Pengubahan formula karena adanya perubahan kondisi (bertambah atau
> berkurang jumlah kondisinya), difokuskan pada penyusunan kondisi-kondisi
> baru yang menyertakan kondisi awal (sebelum berubah) secara konsisten.
> Seperti kondisi bahwa jika CountIF 0 artinya sebagai data baru bergabung
> bersama kondisi baru berupa ada data di suatu area (dengan Len misalnya).
>
> Wassalam,
> Kid.
>
>
>
>
> 2012/10/18 M.Ade Alfarid <[email protected]>
>
> Dear,
>>
>>
>> Untuk menghilangkan angka 0 pada list index,
>> tambah rumus apa?
>>
>> Ade
>>
>>
>>
>>
>> On Mon, 15 Oct 2012 16:30:55 +0700, Mr. Kid <[email protected]> wrote:
>>
>>
>>> Coba file terlampir.
>>> Ada yang dibantu oleh sebuah kolom untuk menyusun data multi kolom
>>> menjadi
>>> data sekolom.
>>>
>>> Wassalam,
>>> Kid
>>>
>>>
>>> 2012/10/15 M.Ade Alfarid <[email protected]>
>>>
>>>> **
>>
>>>>
>>>>
>>>> BeExceller,
>>>>
>>>> Bagaimana untuk mengurut banyak data beberapa kolom,
>>>> data dobel dihitung sekali saja?
>>>> Terimakasih
>>>> --
>>>> ade
>>>>
>>
>
