Sabtu, 03 Oktober 2015

[belajar-excel] Digest Number 3586

4 Messages

Digest #3586
1a
Re: ubah formula array ke formula biasa? by "Mr. Kid" nmkid.family@ymail.com
2a
Re: Dimana Sintax Errornya by "Mr. Kid" nmkid.family@ymail.com
2b
Re: Dimana Sintax Errornya by "Heru Safrudin" superheruu

Messages

Fri Oct 2, 2015 2:25 am (PDT) . Posted by:

"Mr. Kid" nmkid.family@ymail.com

Hai Ivan,

Array formula disebuah cell bisa dicopy ke cell lain.
Jika ada array formula di A1, dan ingin di-copy ke A2:A9, klik cell A1,
tekan CTRL C, blok A2:A9 (A1 tidak ikut diblok), tekan CTRL V

Dari array formula berbunyi :
=VLOOKUP($L2;IF($D$6:$D$21=Sheet1!M2;$B$6:$D$21);2;FALSE)

1. Andaikan kolom B:D berisi data bukan teks semua dan tidak ada error
value alias datanya bertipe numerik atau datetime atau boolean
(true/false), maka bisa tanpa fungsi IF, sehingga array formula dengan
vLookUp tersebut bisa diubah menjadi formula biasa seperti :
=vLookUp( $L2 , Index( ($D$6:$D$21=Sheet1!M2) * $B$6:$D$21 , 0 , 0 ) , 2 ,
FALSE )
bahkan bisa menggunakan SumIFs.

2. Andaikan antara kolom B:D, ada minimal 1 kolom berisi data yang bertipe
teks, maka susunan formula vLookUp nya menjadi :
=vLookUp( Trim($L2) , Index( Rept( $B$6:$D$21 , ($D$6:$D$21=Sheet1!M2) )
, 0 , 0 ) , 2 , FALSE )
Jika hasil vLookUp yang diambil (dalam hal ini kolom ke-2 dari B:D alias
nilai di kolom C) adalah bertipe numerik, maka hasil formula vLookUp di
atas perlu dikonversi menjadi bertipe numerik, seperti ini :
= 1 * vLookUp( Trim($L2) , Index( Rept( $B$6:$D$21 , ($D$6:$D$21=Sheet1!M2)
) , 0 , 0 ) , 2 , FALSE )

Fungsi Index Match juga bisa digunakan dalam kasus ini, seperti :
1. array formula karena ada IF-nya
=Index( $C$6:$C$21 , Match( $L2 , IF($D$6:$D$21=Sheet1!M2,$B$6:$B$21) ,
0 ) ) -> perhatikan jumlah kolom setiap reference

2. bila kolom B dan cell L2 bertipe numerik
a> array formula tanpa IF :
=Index( $C$6:$C$21 , Match( $L2 , ($D$6:$D$21=Sheet1!M2) * $B$6:$B$21 ,
0 ) )
b> formula biasa : (perhatikan cara mengubah formula 2a)
=Index( $C$6:$C$21 , Match( $L2 , INDEX( ($D$6:$D$21=Sheet1!M2) *
$B$6:$B$21 , 0 ) , 0 ) )

3. bila kolom B dan L2 bertipe teks
a> array formula tanpa IF :
=Index( $C$6:$C$21 , Match( $L2 , Rept( $B$6:$B$21 ,
($D$6:$D$21=Sheet1!M2)
) , 0 ) )
b> formula biasa : (perhatikan cara mengubah formula 2a)
=Index( $C$6:$C$21 , Match( $L2 , INDEX( Rept( $B$6:$B$21 ,
($D$6:$D$21=Sheet1!M2) ) , 0 ) , 0 ) )

btw,
Array formula yang diletakkan pada banyak cells dan merujuk ke banyak baris
data (apalagi ke cells yang belum ada datanya), akan memberi kesan Excel
bekerja lamban. Jadi, mohon ditanamkan sejak sekarang bahwa yang lamban
bukan Excel-nya, tapi yang memilih metode pengolahan datanya.

Untuk menghindari hal yang demikian, andaikan memungkinkan merujuk ke data
terurut dan pengolahan datanya bisa disusun per blok data, maka aturlah
demikian lalu menggunakan rujukan dinamis. Contoh pada kasus ini, nilai M2
yang berelasi dengan kolom D bisa menjadi penyusun blok, dengan cara
mengurutkan data di B:D berdasar kolom D ASC, lalu berdasar kolom B ASC (2
kriteria sort).
Formula Index bisa digunakan untuk mendapatkan area blok range yang sesuai
untuk nilai M tertentu, yang kemudian bisa digunakan dalam formula vLookUp
atau Index Match.

Wassalam,
Kid

2015-10-01 22:48 GMT+07:00 Ivan Sebastian layonardo@yahoo.co.id
[belajar-excel] <belajar-excel@yahoogroups.com>:

>
>
> master2 excel... butuh bantuan lagi nih...
> kalo ada rumus formula arraynya seperti ini..
> {=VLOOKUP($L2;IF($D$6:$D$21=Sheet1!M2;$B$6:$D$21);2;FALSE)}
>
> apa bisa diubah ke bentuk formula biasa ya??
>
> itu rumus vlookup dengan 2 kriteria kan ya... saya lagi cari2 rumus
> formula untuk vlookup 2 kriteria yg simple sebenernya pingin pake index dan
> match... tapi gagal paham.. dicoba2 tetep aja gagal mulu..
>
> kalo formula yg ini pas datanya banyak jadi repot jg mesti harus cse satu
> persatu xixixixi...
>
> thx
>
>
>

Fri Oct 2, 2015 1:56 pm (PDT) . Posted by:

"Ivansl" layonardo

Terima kasih mr kid...
Sudah berjalan dngan baik rumusnya.. Saya pakai index yg nomer 2 itu... Sempurna... Topppp abis mr kid..
Thx...
Sent from my iPad mini

> On 2 Okt 2015, at 16.17, 'Mr. Kid' mr.nmkid@gmail.com [belajar-excel] <belajar-excel@yahoogroups.com> wrote:
>
> Hai Ivan,
>
> Array formula disebuah cell bisa dicopy ke cell lain.
> Jika ada array formula di A1, dan ingin di-copy ke A2:A9, klik cell A1, tekan CTRL C, blok A2:A9 (A1 tidak ikut diblok), tekan CTRL V
>
> Dari array formula berbunyi :
> =VLOOKUP($L2;IF($D$6:$D$21=Sheet1!M2;$B$6:$D$21);2;FALSE)
>
> 1. Andaikan kolom B:D berisi data bukan teks semua dan tidak ada error value alias datanya bertipe numerik atau datetime atau boolean (true/false), maka bisa tanpa fungsi IF, sehingga array formula dengan vLookUp tersebut bisa diubah menjadi formula biasa seperti :
> =vLookUp( $L2 , Index( ($D$6:$D$21=Sheet1!M2) * $B$6:$D$21 , 0 , 0 ) , 2 , FALSE )
> bahkan bisa menggunakan SumIFs.
>
> 2. Andaikan antara kolom B:D, ada minimal 1 kolom berisi data yang bertipe teks, maka susunan formula vLookUp nya menjadi :
> =vLookUp( Trim($L2) , Index( Rept( $B$6:$D$21 , ($D$6:$D$21=Sheet1!M2) ) , 0 , 0 ) , 2 , FALSE )
> Jika hasil vLookUp yang diambil (dalam hal ini kolom ke-2 dari B:D alias nilai di kolom C) adalah bertipe numerik, maka hasil formula vLookUp di atas perlu dikonversi menjadi bertipe numerik, seperti ini :
> = 1 * vLookUp( Trim($L2) , Index( Rept( $B$6:$D$21 , ($D$6:$D$21=Sheet1!M2) ) , 0 , 0 ) , 2 , FALSE )
>
> Fungsi Index Match juga bisa digunakan dalam kasus ini, seperti :
> 1. array formula karena ada IF-nya
> =Index( $C$6:$C$21 , Match( $L2 , IF($D$6:$D$21=Sheet1!M2,$B$6:$B$21) , 0 ) ) -> perhatikan jumlah kolom setiap reference
>
> 2. bila kolom B dan cell L2 bertipe numerik
> a> array formula tanpa IF :
> =Index( $C$6:$C$21 , Match( $L2 , ($D$6:$D$21=Sheet1!M2) * $B$6:$B$21 , 0 ) )
> b> formula biasa : (perhatikan cara mengubah formula 2a)
> =Index( $C$6:$C$21 , Match( $L2 , INDEX( ($D$6:$D$21=Sheet1!M2) * $B$6:$B$21 , 0 ) , 0 ) )
>
> 3. bila kolom B dan L2 bertipe teks
> a> array formula tanpa IF :
> =Index( $C$6:$C$21 , Match( $L2 , Rept( $B$6:$B$21 , ($D$6:$D$21=Sheet1!M2) ) , 0 ) )
> b> formula biasa : (perhatikan cara mengubah formula 2a)
> =Index( $C$6:$C$21 , Match( $L2 , INDEX( Rept( $B$6:$B$21 , ($D$6:$D$21=Sheet1!M2) ) , 0 ) , 0 ) )
>
> btw,
> Array formula yang diletakkan pada banyak cells dan merujuk ke banyak baris data (apalagi ke cells yang belum ada datanya), akan memberi kesan Excel bekerja lamban. Jadi, mohon ditanamkan sejak sekarang bahwa yang lamban bukan Excel-nya, tapi yang memilih metode pengolahan datanya.
>
> Untuk menghindari hal yang demikian, andaikan memungkinkan merujuk ke data terurut dan pengolahan datanya bisa disusun per blok data, maka aturlah demikian lalu menggunakan rujukan dinamis. Contoh pada kasus ini, nilai M2 yang berelasi dengan kolom D bisa menjadi penyusun blok, dengan cara mengurutkan data di B:D berdasar kolom D ASC, lalu berdasar kolom B ASC (2 kriteria sort).
> Formula Index bisa digunakan untuk mendapatkan area blok range yang sesuai untuk nilai M tertentu, yang kemudian bisa digunakan dalam formula vLookUp atau Index Match.
>
> Wassalam,
> Kid
>
>
>
>
> 2015-10-01 22:48 GMT+07:00 Ivan Sebastian layonardo@yahoo.co.id [belajar-excel] <belajar-excel@yahoogroups.com>:
>>
>> master2 excel... butuh bantuan lagi nih...
>> kalo ada rumus formula arraynya seperti ini..
>> {=VLOOKUP($L2;IF($D$6:$D$21=Sheet1!M2;$B$6:$D$21);2;FALSE)}
>>
>> apa bisa diubah ke bentuk formula biasa ya??
>>
>> itu rumus vlookup dengan 2 kriteria kan ya... saya lagi cari2 rumus formula untuk vlookup 2 kriteria yg simple sebenernya pingin pake index dan match... tapi gagal paham.. dicoba2 tetep aja gagal mulu..
>>
>> kalo formula yg ini pas datanya banyak jadi repot jg mesti harus cse satu persatu xixixixi...
>>
>> thx
>>
>
>

Fri Oct 2, 2015 2:26 am (PDT) . Posted by:

"Mr. Kid" nmkid.family@ymail.com

Alhamdulillah... turut senang.

Berhubung SQL adalah bahasa yang Structured (kan SQL = Structured Query
Language), maka pengolahan data yang memiliki struktur yang jelas akan
relatif mudah. Format yang tetap bisa menjadi kriteria karena terstruktur.

Jadi, andaikan bisa ditentukan kriteria identiknya yang berlaku untuk
seluruh record di kolom tersebut, maka bisa disusun ekspresinya ataupun
adjective-nya.
Contho, bila pasti bahwa 3 karakter pertama adalah kunci identik, maka
bunyi bagian ON bisa :
ON Left(a.GR,3)=Left(b.GR)
dan dipastikan bahwa hasil join menghasilkan data unique berdasar
kolom-kolom di bagian ON dan pasti 1 record untuk setiap kombinasi
kolom-kolom yang ada di bagian ON.

Bisa juga ketika polanya adalah bahwa a.GR ada didalam b.GR

ON b.GR like '%' + a.gr + '%'
atau menggunakan fungsi Instr
ON instr(b.gr , a.gr)<>0
atau mungkin bisa pakai replace dan sebagainya...
banyak sekali yang mungkin dipakai. Dan akhirnya nanti, akan bermuara,
bunyi query yang mana yang efisien.

Wassalamu'alaikum wr wb,
Kid

2015-10-02 16:02 GMT+07:00 Heru Safrudin superheruu@gmail.com
[belajar-excel] <belajar-excel@yahoogroups.com>:

>
>
> Alhamdulillah udah sukses Mr.
> Ada pertanyaan lagi sbb :
>
> Contoh di kasus Anda :
> UPDATE [\\DATABASE\RM.MDB].REF2 as a
> INNER JOIN [\\DATABASE\RM.MDB].GR as b
> ON a.GR=b.GR <------ apakah datanya harus identik ??? Bagaimana
> penulisannya jika data tidak identik tapi ada sebagian saja data yg
> identik, misal a.GR datanya 'ABC1' dan b.GR datanya 'ABC2' dianggap data yg
> identik
> SET a.HARVEST=b.GR_DATE
>
> Terima kasih pengetahuannya
> Wassalamu 'alaikum wr. wb.,
>
> Heru Safrudin
>
> Pada 2015 10 1 14:04, "'Mr. Kid' mr.nmkid@gmail.com
> [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:
> >
> >
> >
> > Wa'alaikumussalam wr wb
> >
> > Hai Heru,
> >
> > Syntax umum :
> > (digunakan oleh sebagian besar database engine, kecuali MS SQL Server
> yang memerlukan pendefinisian klausa FROM)
> > UPDATE target as alias_target
> > INNER JOIN source as another
> > ON alias_target.colskey=another.colskey
> > SET alias_target.col1_target = another.col1
> >
> > Contoh di kasus Anda :
> > UPDATE [\\DATABASE\RM.MDB].REF2 as a
> > INNER JOIN [\\DATABASE\RM.MDB].GR as b
> > ON a.GR=b.GR
> > SET a.HARVEST=b.GR_DATE
> >
> > Jadi, perbedaan dengan yang Anda buat adalah pada struktur bunyi
> bahasanya...
> >
> >
> > Wassalamu'alaikum wr wb
> > Kid
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > 2015-10-01 12:39 GMT+07:00 Heru Safrudin superheruu@gmail.com
> [belajar-excel] <belajar-excel@yahoogroups.com>:
> >>
> >>
> >>
> >> Dear master,
> >>
> >> Saya mau update database mdb file, dari excel dengan syntax sql sbb:
> >>
> >> "update `\\DATABASE\RM.MDB`.REF2 SET REF2.HARVEST=GR.GR_DATE FROM
> `\\DATABASE\RM.MDB`.GR WHERE REF2.GR=GR.GR"
> >>
> >> Kenapa kok syntex error (missing operator ) in query expression, saya
> pingin tahu dimana letak syntax errornya
> >> Mohon petunjuknya
> >>
> >> Terima kasih
> >> Wassalamu 'alaikum wr. wb.,
> >>
> >> Heru Safrudin
> >
> >
> >
>
>
>

Fri Oct 2, 2015 9:31 pm (PDT) . Posted by:

"Heru Safrudin" superheruu

Subhanallah....
Ya seperti ini yang saya maksud dan sudah saya otak-atik tapi belum berhasil

Terima kasih Mr...
Wassalamu 'alaikum wr. wb.,

Heru Safrudin
Pada 2015 10 2 16:26, "'Mr. Kid' mr.nmkid@gmail.com [belajar-excel]" <
belajar-excel@yahoogroups.com> menulis:

>
>
> Alhamdulillah... turut senang.
>
> Berhubung SQL adalah bahasa yang Structured (kan SQL = Structured Query
> Language), maka pengolahan data yang memiliki struktur yang jelas akan
> relatif mudah. Format yang tetap bisa menjadi kriteria karena terstruktur.
>
> Jadi, andaikan bisa ditentukan kriteria identiknya yang berlaku untuk
> seluruh record di kolom tersebut, maka bisa disusun ekspresinya ataupun
> adjective-nya.
> Contho, bila pasti bahwa 3 karakter pertama adalah kunci identik, maka
> bunyi bagian ON bisa :
> ON Left(a.GR,3)=Left(b.GR)
> dan dipastikan bahwa hasil join menghasilkan data unique berdasar
> kolom-kolom di bagian ON dan pasti 1 record untuk setiap kombinasi
> kolom-kolom yang ada di bagian ON.
>
> Bisa juga ketika polanya adalah bahwa a.GR ada didalam b.GR
>
> ON b.GR like '%' + a.gr + '%'
> atau menggunakan fungsi Instr
> ON instr(b.gr , a.gr)<>0
> atau mungkin bisa pakai replace dan sebagainya...
> banyak sekali yang mungkin dipakai. Dan akhirnya nanti, akan bermuara,
> bunyi query yang mana yang efisien.
>
> Wassalamu'alaikum wr wb,
> Kid
>
>
> 2015-10-02 16:02 GMT+07:00 Heru Safrudin superheruu@gmail.com
> [belajar-excel] <belajar-excel@yahoogroups.com>:
>
>>
>>
>> Alhamdulillah udah sukses Mr.
>> Ada pertanyaan lagi sbb :
>>
>> Contoh di kasus Anda :
>> UPDATE [\\DATABASE\RM.MDB].REF2 as a
>> INNER JOIN [\\DATABASE\RM.MDB].GR as b
>> ON a.GR=b.GR <------ apakah datanya harus identik ??? Bagaimana
>> penulisannya jika data tidak identik tapi ada sebagian saja data yg
>> identik, misal a.GR datanya 'ABC1' dan b.GR datanya 'ABC2' dianggap data yg
>> identik
>> SET a.HARVEST=b.GR_DATE
>>
>> Terima kasih pengetahuannya
>> Wassalamu 'alaikum wr. wb.,
>>
>> Heru Safrudin
>>
>> Pada 2015 10 1 14:04, "'Mr. Kid' mr.nmkid@gmail.com
>> [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:
>> >
>> >
>> >
>> > Wa'alaikumussalam wr wb
>> >
>> > Hai Heru,
>> >
>> > Syntax umum :
>> > (digunakan oleh sebagian besar database engine, kecuali MS SQL Server
>> yang memerlukan pendefinisian klausa FROM)
>> > UPDATE target as alias_target
>> > INNER JOIN source as another
>> > ON alias_target.colskey=another.colskey
>> > SET alias_target.col1_target = another.col1
>> >
>> > Contoh di kasus Anda :
>> > UPDATE [\\DATABASE\RM.MDB].REF2 as a
>> > INNER JOIN [\\DATABASE\RM.MDB].GR as b
>> > ON a.GR=b.GR
>> > SET a.HARVEST=b.GR_DATE
>> >
>> > Jadi, perbedaan dengan yang Anda buat adalah pada struktur bunyi
>> bahasanya...
>> >
>> >
>> > Wassalamu'alaikum wr wb
>> > Kid
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> > 2015-10-01 12:39 GMT+07:00 Heru Safrudin superheruu@gmail.com
>> [belajar-excel] <belajar-excel@yahoogroups.com>:
>> >>
>> >>
>> >>
>> >> Dear master,
>> >>
>> >> Saya mau update database mdb file, dari excel dengan syntax sql sbb:
>> >>
>> >> "update `\\DATABASE\RM.MDB`.REF2 SET REF2.HARVEST=GR.GR_DATE FROM
>> `\\DATABASE\RM.MDB`.GR WHERE REF2.GR=GR.GR"
>> >>
>> >> Kenapa kok syntex error (missing operator ) in query expression, saya
>> pingin tahu dimana letak syntax errornya
>> >> Mohon petunjuknya
>> >>
>> >> Terima kasih
>> >> Wassalamu 'alaikum wr. wb.,
>> >>
>> >> Heru Safrudin
>> >
>> >
>> >
>>
>>
>
>
============================================================
Pojok Lowongan Kerja yang disediakan milis :
http://milis-belajar-excel.1048464.n5.nabble.com/Pojok-Lowongan-Kerja-f5725753.html
*** Posting lowongan kerja : ke link tersebut dan klik New Topic
============================================================
bergabung ke milis (subscribe), kirim mail kosong ke: belajar-excel-subscribe@yahoogroups.com
posting ke milis, kirimkan ke: belajar-excel@yahoogroups.com
berkunjung ke web milis : http://tech.groups.yahoo.com/group/belajar-excel/messages
melihat file archive / mendownload lampiran : http://www.mail-archive.com/belajar-excel@yahoogroups.com/
atau (sejak 25-Apr-2011) bisa juga di : http://milis-belajar-excel.1048464.n5.nabble.com/
menghubungi moderators & owners: belajar-excel-owner@yahoogroups.com
keluar dari membership milis (UnSubscribe), kirim mail kosong ke : belajar-excel-unsubscribe@yahoogroups.com
---------------------------------------------------------------------

Tidak ada komentar:

Posting Komentar