Quantcast

[belajar-excel] Masalah pilih data dg List Box

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

[belajar-excel] Masalah pilih data dg List Box

Mulki Akbar
Assalamu'alaikum Wr. Wb,

Selamat sore ..

Sudah lama tidak mengikuti perkembangan milis B-Xl, semoga rekan2 selalu dlm keadaan sehat dan bersemangat :)

Mohon pencerahan rekan2 atas masalah saya ttg list box dan menampilkan data yg terhubung dg pilihannya.

Terimakasih atas pencerahan rekan2.

Wassalam,
Mulki akbar

kasus sort dg list-box.xls (45K) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [belajar-excel] Masalah pilih data dg List Box

Miss Jan Raisin
 وَعَلَيْكُمُ  لسَّلاَمُ وَرَحْمَةُ اللهِ وَبَرَكَاتُهُ

Pak Mulki, solusi dari Jan adalah seperti ini:

Jawaban untuk pertanyaan: *1. Saat list box di sel B27 di klik hanya
memunculkan 1 pilihan utk tiap data dlm kolom GROUP, misal : A, B dan C
saja bukan AAA, BBB, CCC*

>> Buat dahulu sebuah named range yang akan dipakai untuk data validasi,
Jan akan ajarkan cara membuat named range yang dinamis dan dapat dipanggil
melalui worksheet manapun selama masih berada dalam workbook yang sama

*Perhatian !*
*tutorial ini dibuat pada excel 2010 yang sama ribbonnya mirip dengan excel
2007 dan 2013, untuk pengguna excel 2003 ke bawah silakan dicari padanannya
karena Jan sudah tidak memiliki versi tersebut.. kalau ada yang ngotot
minta dibuatkan tutorial untuk versi 2003 ke bawah mungkin bisa Jan
usahakan tetapi Jan harus cari pinjaman komputer ke tetangga yang
(--mungkin--) masih menggunakan versi tersebut.*

caranya sbb:
>> Buat list data dengan sebuah header, letaknya kalo bisa dalam 1 buah
sheet yang terpisah yang khusus dibuat sebagai penampung list-list yang
akan digunakan.
dalam hal ini pilihannya hanya A, B, dan C saja.. untuk kedepannya karena
named range besifat dinamis maka setiap penambahan atau pengurangan data
akan otomatis mengubah Data Validasi yang akan digunakan
[image: Gambar sisip 1]

>> pada riibon Formula, blok Defined Names, klik Defined Name
[image: Gambar sisip 2]

sehingga muncul dialog box seperti berikut ini:
[image: Gambar sisip 3]

>> pada kotak nomor 1, beri nama range yang nanti akan dipanggil melalui
menu Data Validasi, contoh nama yang digunakan adalah *_myGroup_*
>> pada kotak nomor 2, pilih scopenya adalah Workbook, agar named range ini
dapat dipanggil melalui sheet mana pun selama masih berada dalam workbook
yang sama
>> pada kotak nomor 3, diisi formula sebagai berikut, (misal data terletak
pada sheet yang bernama List
=OffSet(List!A2, 0, 0, CountA(List!A:A) - 1, 1)
>> setelah itu klik OK

keterangan:
List!A2                    = letak data pertama berada di sheet List pada
cel A2
0                              = nomor index baris data yang digunakan
0                              = nomor index kolom yang digunakan
CountA(List!A:A) - 1 = banyaknya baris data yang digunakan, yaitu seluruh
data di sheet List pada kolom A dikurangi dengan header
1                              = banyaknya kolom data yang digunakan

>> bahasa manusianya adalah: ambil data pada sheet List mulai cel A2,
dengan jumlah baris yang digunakan adalah seluruh data di kolom A yang ada
isinya dikurangi dengan header dan jumlah kolom data adalah 1

>> setelah itu pasang named range yang telah dibuat pada sheet 1 di cell
B29 dengan menggunakan fitur Data Validasi
>> letakan cell aktif pada sheet1 cel B29
>> pada ribbon Data, blok Data Tools, klik Data Validation
[image: Gambar sisip 4]

>> sehingga akan muncul dialog box seperti di bawah ini:
[image: Gambar sisip 6]

>> pada nomor 1, pilih List
>> pada nomor 2, masukan named range yang tadi telah dibuat
>> setelah itu klik OK

Jawaban untuk pertanyaan: *2. Saat list box di sel B27 di pilih, misal A
maka pada sel B31 trus kebawah-nya bisa menampilkan list box yg datanya
bisa dipilih sesuai keinginan saya saat tiap2 sel dibawahnya saya klik,
misal saya klik di sel B31, B32, B33 & B34*

>> blok cel B33 sampai dengan cel B36, lalu diisi dengan formula:
Index($C$4:$C$12,Small(If($B$4:$B$12=$B$29,Row(1:9)),Row(1:9)))
lalu diakhiri dengan menekan tombol CTRL Shift Enter sehingga muncul tanda
kurung kurawal di awal dan akhir formula, jika setelah menulis formula
tidak menekan 3 tombol ajaib tersebut maka akan menghasilkan error #Value

formula di atas dinamakan Array Formula sekolom

*Perhatian !*
*untuk yang menggunakan regional setting Indonesian, ubah seluruh tanda
koma pada formula di atas dengan tanda titik koma*

formula di atas akan menghasilkan error berupa #NUM jika data tidak
ditemukan, untuk mengatasi hal ini digunakan cara:

>> untuk pengguna excel 2007 ke atas, tambahkan formula berikut:
=IfError(ini_formula_yang_tadi_sudah_dibuat,"") lalu diakhiri dengan
menekan tombol CTRL Shift Enter

>> untuk pengguna excel 2003 ke bawah, tambahkan formula berikut:
=If(IsErr(ini_formula_yang_tadi_sudah_dibuat),"",
ini_formula_yang_tadi_sudah_dibuat) lalu diakhiri dengan menekan tombol
CTRL Shift Enter

Pada kasus ini, IsErr dapat diganti dengan IsError yang akan menghasilkan
hasil yang sama

Bahasa manusianya adalah: ambil data pada cel C4 sampai dengan cel C12 (
Index($C$4:$C$12) dengan terurut secara Ascending (Small(...,Row(1:...)))
 dengan syarat bahwa pada nilai pada cell B4 sampai cel B12 harus sama
dengan nilai pada cel B29 (If($B$4:$B$12=$B$29) dan jumlah baris datanya
ada 9 baris (Row(1:9))

jawaban untuk pertanyaan: *3. Data Harga akan muncul sesuai pilihan list
box pada Type*

>> ubah Array Formula dengan data yang diambil adalah kolom D


file terlampir

وَسَّلاَمُ عَلَيْكُمْ وَرَحْمَةُ اللهِ وَبَرَكَاتُهُ

-Miss Jan Raisin-


Pada 22 Agustus 2013 16.39, Mulki <[hidden email]> menulis:

> **
>
>
> **
> Assalamu'alaikum Wr. Wb,
>
> Selamat sore ..
>
> Sudah lama tidak mengikuti perkembangan milis B-Xl, semoga rekan2 selalu
> dlm keadaan sehat dan bersemangat :)
>
> Mohon pencerahan rekan2 atas masalah saya ttg list box dan menampilkan
> data yg terhubung dg pilihannya.
>
> Terimakasih atas pencerahan rekan2.
>
> Wassalam,
> Mulki akbar
>
>
>

Pict_02.png (46K) Download Attachment
Pict_01.png (2K) Download Attachment
Pict_03.png (25K) Download Attachment
Pict_05.png (29K) Download Attachment
Pict_04.png (64K) Download Attachment
MJR_kasus sort dg list-box.xls (58K) Download Attachment
Kid
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [belajar-excel] Masalah pilih data dg List Box

Kid
Administrator
In reply to this post by Mulki Akbar
Wa'alaikumussalam Wr. Wb.

Untuk validation list terfilter, coba lihat di file yang ada
disini<https://app.box.com/s/slgzbd6ro34yi2jk3uh2>.
Pastikan susunan data referensinya menjadi 2 tabel terpisah, antara daftar
untuk cell B27, dan tabel untuk item terfilter berdasar inputan user.
Untuk harga, gunakan fungsi VLookUp jika berdasar sebuah kriteria, atau
array formula Index Match untuk multi kriteria, dan bisa juga dengan
SumProduct atau array formula Sum. Pada Excel 2007 ke atas, bisa
menggunakan SumIFs.

Wassalamu'alaikum Wr. Wb.
Kid.



2013/8/22 Mulki <[hidden email]>

> **
>
>
> **
> Assalamu'alaikum Wr. Wb,
>
> Selamat sore ..
>
> Sudah lama tidak mengikuti perkembangan milis B-Xl, semoga rekan2 selalu
> dlm keadaan sehat dan bersemangat :)
>
> Mohon pencerahan rekan2 atas masalah saya ttg list box dan menampilkan
> data yg terhubung dg pilihannya.
>
> Terimakasih atas pencerahan rekan2.
>
> Wassalam,
> Mulki akbar
>
>  
>
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [belajar-excel] Masalah pilih data dg List Box

Mulki Akbar
In reply to this post by Miss Jan Raisin
Assalamu'alaikum Wr. Wb.

Terimakasih ms.Jan atas pencerahannya yg pasti diketik dg penuh kesabaran :)

Oia, saya sempat otak atik sendiri dg menggunakan formula vlookup. Tp setelah melihat jawaban dari ms. jan saya berpikir formula yg saya buat kemungkinan penggunaannya mungkin saja terbatas, maaf blm sempat kirim formula yg dari saya  :)

Sekali lagi terimakasih ms. jan atas pencerahannya.


Wassalam,
Mulki akbar
  ----- Original Message -----
  From: Jan Raisin
  To: [hidden email]
  Sent: Friday, August 23, 2013 9:45 AM
  Subject: Re: [belajar-excel] Masalah pilih data dg List Box


   وَعَلَيْكُمُ  لسَّلاَمُ وَرَحْمَةُ اللهِ وَبَرَكَاتُهُ



  Pak Mulki, solusi dari Jan adalah seperti ini:


  Jawaban untuk pertanyaan: 1. Saat list box di sel B27 di klik hanya memunculkan 1 pilihan utk tiap data dlm kolom GROUP, misal : A, B dan C saja bukan AAA, BBB, CCC


  >> Buat dahulu sebuah named range yang akan dipakai untuk data validasi, Jan akan ajarkan cara membuat named range yang dinamis dan dapat dipanggil melalui worksheet manapun selama masih berada dalam workbook yang sama


  Perhatian !
  tutorial ini dibuat pada excel 2010 yang sama ribbonnya mirip dengan excel 2007 dan 2013, untuk pengguna excel 2003 ke bawah silakan dicari padanannya karena Jan sudah tidak memiliki versi tersebut.. kalau ada yang ngotot minta dibuatkan tutorial untuk versi 2003 ke bawah mungkin bisa Jan usahakan tetapi Jan harus cari pinjaman komputer ke tetangga yang (--mungkin--) masih menggunakan versi tersebut.


  caranya sbb:
  >> Buat list data dengan sebuah header, letaknya kalo bisa dalam 1 buah sheet yang terpisah yang khusus dibuat sebagai penampung list-list yang akan digunakan.
  dalam hal ini pilihannya hanya A, B, dan C saja.. untuk kedepannya karena named range besifat dinamis maka setiap penambahan atau pengurangan data akan otomatis mengubah Data Validasi yang akan digunakan




  >> pada riibon Formula, blok Defined Names, klik Defined Name




  sehingga muncul dialog box seperti berikut ini:




  >> pada kotak nomor 1, beri nama range yang nanti akan dipanggil melalui menu Data Validasi, contoh nama yang digunakan adalah _myGroup_
  >> pada kotak nomor 2, pilih scopenya adalah Workbook, agar named range ini dapat dipanggil melalui sheet mana pun selama masih berada dalam workbook yang sama
  >> pada kotak nomor 3, diisi formula sebagai berikut, (misal data terletak pada sheet yang bernama List
  =OffSet(List!A2, 0, 0, CountA(List!A:A) - 1, 1)
  >> setelah itu klik OK


  keterangan:
  List!A2                    = letak data pertama berada di sheet List pada cel A2

  0                              = nomor index baris data yang digunakan
  0                              = nomor index kolom yang digunakan
  CountA(List!A:A) - 1 = banyaknya baris data yang digunakan, yaitu seluruh data di sheet List pada kolom A dikurangi dengan header

  1                              = banyaknya kolom data yang digunakan


  >> bahasa manusianya adalah: ambil data pada sheet List mulai cel A2, dengan jumlah baris yang digunakan adalah seluruh data di kolom A yang ada isinya dikurangi dengan header dan jumlah kolom data adalah 1


  >> setelah itu pasang named range yang telah dibuat pada sheet 1 di cell B29 dengan menggunakan fitur Data Validasi
  >> letakan cell aktif pada sheet1 cel B29
  >> pada ribbon Data, blok Data Tools, klik Data Validation




  >> sehingga akan muncul dialog box seperti di bawah ini:




  >> pada nomor 1, pilih List
  >> pada nomor 2, masukan named range yang tadi telah dibuat
  >> setelah itu klik OK


  Jawaban untuk pertanyaan: 2. Saat list box di sel B27 di pilih, misal A maka pada sel B31 trus kebawah-nya bisa menampilkan list box yg datanya bisa dipilih sesuai keinginan saya saat tiap2 sel dibawahnya saya klik, misal saya klik di sel B31, B32, B33 & B34


  >> blok cel B33 sampai dengan cel B36, lalu diisi dengan formula:
  Index($C$4:$C$12,Small(If($B$4:$B$12=$B$29,Row(1:9)),Row(1:9)))

  lalu diakhiri dengan menekan tombol CTRL Shift Enter sehingga muncul tanda kurung kurawal di awal dan akhir formula, jika setelah menulis formula tidak menekan 3 tombol ajaib tersebut maka akan menghasilkan error #Value


  formula di atas dinamakan Array Formula sekolom


  Perhatian !
  untuk yang menggunakan regional setting Indonesian, ubah seluruh tanda koma pada formula di atas dengan tanda titik koma


  formula di atas akan menghasilkan error berupa #NUM jika data tidak ditemukan, untuk mengatasi hal ini digunakan cara:


  >> untuk pengguna excel 2007 ke atas, tambahkan formula berikut:
  =IfError(ini_formula_yang_tadi_sudah_dibuat,"") lalu diakhiri dengan menekan tombol CTRL Shift Enter


  >> untuk pengguna excel 2003 ke bawah, tambahkan formula berikut:
  =If(IsErr(ini_formula_yang_tadi_sudah_dibuat),"",ini_formula_yang_tadi_sudah_dibuat) lalu diakhiri dengan menekan tombol CTRL Shift Enter


  Pada kasus ini, IsErr dapat diganti dengan IsError yang akan menghasilkan hasil yang sama



  Bahasa manusianya adalah: ambil data pada cel C4 sampai dengan cel C12 (Index($C$4:$C$12) dengan terurut secara Ascending (Small(...,Row(1:...)))  dengan syarat bahwa pada nilai pada cell B4 sampai cel B12 harus sama dengan nilai pada cel B29 (If($B$4:$B$12=$B$29) dan jumlah baris datanya ada 9 baris (Row(1:9))


  jawaban untuk pertanyaan: 3. Data Harga akan muncul sesuai pilihan list box pada Type


  >> ubah Array Formula dengan data yang diambil adalah kolom D




  file terlampir


  وَسَّلاَمُ عَلَيْكُمْ وَرَحْمَةُ اللهِ وَبَرَكَاتُهُ



  -Miss Jan Raisin-



  Pada 22 Agustus 2013 16.39, Mulki <[hidden email]> menulis:

     

    Assalamu'alaikum Wr. Wb,

    Selamat sore ..

    Sudah lama tidak mengikuti perkembangan milis B-Xl, semoga rekan2 selalu dlm keadaan sehat dan bersemangat :)

    Mohon pencerahan rekan2 atas masalah saya ttg list box dan menampilkan data yg terhubung dg pilihannya.

    Terimakasih atas pencerahan rekan2.

    Wassalam,
    Mulki akbar



Pict_01.png (2K) Download Attachment
Pict_02.png (46K) Download Attachment
Pict_03.png (25K) Download Attachment
Pict_04.png (64K) Download Attachment
Pict_05.png (29K) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [belajar-excel] Masalah pilih data dg List Box

Mulki Akbar
In reply to this post by Kid
Assalamu'alaikum Wr. Wb.

Alhamdulillah dapat 2 pencerahan :)
Terimakasih atas solusi & link-nya mr.kid, meluncur ke TKP.

Wassalam,
Mulki
  ----- Original Message -----
  From: Mr. Kid
  To: BeExcel
  Sent: Sunday, August 25, 2013 1:14 AM
  Subject: Re: [belajar-excel] Masalah pilih data dg List Box


   

  Wa'alaikumussalam Wr. Wb.


  Untuk validation list terfilter, coba lihat di file yang ada disini. Pastikan susunan data referensinya menjadi 2 tabel terpisah, antara daftar untuk cell B27, dan tabel untuk item terfilter berdasar inputan user.

  Untuk harga, gunakan fungsi VLookUp jika berdasar sebuah kriteria, atau array formula Index Match untuk multi kriteria, dan bisa juga dengan SumProduct atau array formula Sum. Pada Excel 2007 ke atas, bisa menggunakan SumIFs.


  Wassalamu'alaikum Wr. Wb.
  Kid.





  2013/8/22 Mulki <[hidden email]>

     

    Assalamu'alaikum Wr. Wb,

    Selamat sore ..

    Sudah lama tidak mengikuti perkembangan milis B-Xl, semoga rekan2 selalu dlm keadaan sehat dan bersemangat :)

    Mohon pencerahan rekan2 atas masalah saya ttg list box dan menampilkan data yg terhubung dg pilihannya.

    Terimakasih atas pencerahan rekan2.

    Wassalam,
    Mulki akbar




 
Loading...