Tambahin dikit ya miss Jan,
*Untuk pakai pivot table...*
1. kalau *ndak mau *pakai kolom Cek, lakukan fileter pada field bernama
Data. Caranya :
:: klik kanan salah satu nilai dari field Data dalam pivot table ->
pilih Filter -> pilih Label Filter -> pilih is greater than -> isi 0 -> OK
2. kalau nilai data bisa berulang dan ditampilkan seluruhnya (bukan unique
values si data), letakkan field bernama No dikiri field bernama data (kalau
di field list bagian Row, letakkan field No diatas field Data). Lalu ubah
layout pivot table menjadi tabular (menu PivotTable Tools -> ribbon Design
-> grup Layout -> klik Report Layout -> pilih Show in tabular)
*Untuk Autofilter...*
setelah melakukan filter agar si 0 gak tampak seperti petunjuk miss Jan,
lakukan Copy ke lokasi lain dengan cara :
blok kolom Data yang terfilter dari header sampai paling bawah (bila perlu
ditambah 1 cell dibawahnya)
-> Copy (tekan CTRL C)
-> Paste ke cell hasil (klik cell hasil dan tekan CTRL V)
-> Non aktifkan autofilter (ribbon Data -> grup Sort & Filter -> klik
Filter)
cara ini ada syaratnya, yaitu :
Sebelum Paste, pastikan setelah Copy, pada kolom data yang nomor baris
Excel nya gak urut (akibat terfilter) ada semut jalan horisontal.
Kalau ndak ada, sebelum Paste saat tepat habis copy dan cek, lakukan
pemilihan khusus hasil filter (yang tampak saja). Jadi urutan proses diatas
akan menjadi :
blok kolom Data yang terfilter dari header sampai paling bawah (bila perlu
ditambah 1 cell dibawahnya)
-> Aktifkan fitur Goto (tekan CTRL G)
-> menuju dialog Special (tekan Special)
-> pilih opsi Visible Cells Only
-> OK
-> Copy (tekan CTRL C)
-> Paste ke cell hasil (klik cell hasil dan tekan CTRL V)
-> Non aktifkan autofilter (ribbon Data -> grup Sort & Filter -> klik
Filter)
Kalau hasilnya ndak harus unique values, manfaatkan fitur Remove Duplicate.
Caranya :
blok range hasil -> ribbon Data -> grup Data Tools -> klik Remove Duplicate
-> centang My table has header -> klik UnSelect All -> centang kolom-kolom
syarat yang harus unique -> OK
*Cara lain 1 :* *Advanced Filter * (kalau mau diotomasi, coba pakai Record
Macro)
(lakukan 1 kali saja)
1. copy header dari kolom bernama Data ke cell kosong (misal ke Z1)
2. copy header dari kolom bernama Data ke cell header hasil (misal ke M1)
-> kalau hasilnya ada beberapa kolom, copy juga kesini headernya
(ubah nilainya kalau mau ganti syaratnya)
3. tulis syaratnya di Z2 teks berbunyi >0 (yang percis sama yak)
(lakukan setiap kali mau pakai advanced filter)
4. blok kolom M1 (semua kolom yang ada teks header kolom-kolom hasil kalau
ada lebih dari 1 kolom hasil)
5. aktifkan advanced filter dengan cara :
ribbon Data -> grup Sort & Filter -> klik Advanced Filter
6. atur kondisi advanced filter :
> Action : pilih Copy to another location
> List Range : blok seluruh tabel data (boleh di sheet lain tapi
workbook yang sama)
> Criteria Range : blok Z1 sampai Z2 (kalau ada banyak kriteria, blok
semua yang ada teks syaratnya termasuk header di kolom Z)
> Copy To : blok lagi kolom M1 atau seluruh kolom yang ada header
kolom hasilnya (kalau belum muncul dengan sendirinya)
[ini kondisi khusus ketika hasilnya harus unique record dari seluruh
kolom hasil (bukan unique dari kolom tertentu saja)]
> centang Unique Record Only (abaikan atau jangan dicentang kalau
bukan untuk kondisi merah
7. tekan OK
*Cara lain 2 : fitur-fitur Excel yang sering diabaikan atau enggan
dipelajari dan digunakan* (termasuk dalam VBA sekalipun)
*** jadi bukan masalah bisa menyelesaikan kasus ini dengan cara ini yak,
tapi sekedar mengingatkan saja bahwa Excel punya banyak fitur ***
*Option ModeRibet 1
*
:D:D:D:D:D
ikuti langkahnya yak...
1. blok header dan seluruh baris dari kolom yang akan dijadikan hasil.
(kalau cuma kolom Data saja, maka blok kolom Data saja)
2. copy (tekan CTRL C)
3. ke lokasi hasil (klik cell pertama header di tabel hasil)
(ini fitur Paste Special Values and Format)
4. lakukan paste special values beserta format dengan cara :
klik kanan cell pilihan nomor 3 tadi -> pilih Paste Special -> pilih
Values and Number Formats -> OK
(ini fitur Replace)
5. hapus semua angka 0 (yang isinya percis) dari kolom syarat. Caranya :
> di tabel hasil, blok kolom yang jadi syarat harus dihilangkan (misal
kolom Data jadi syarat dan ada di kolom M, maka blok kolom M)
> aktifkan fitur Replace (tekan CTRL H)
> bagian Find What : isi dengan nilai yang harus hilang (kasus ini
berarti diisi angka 0)
> tampilkan Option (tekan tombol Option kalau tulisannya masih *Option
>>*)
> centang opsi Match Entire Cell Contents
> tekan Replace ALL lalu tutup (tekan tombol close (x) dipojok kanan
atas dialog Replace)
* *** yang angka 0 dah jadi blank*
*(nomor 6 sampai 9 khusus untuk asumsi : hanya ada 1 kolom di tabel hasil)*
(ini fitur Goto Special)
6. pilih yang ada values nya (isinya nilai constant)
> aktifkan fitur Goto (CTRL G)
> ke opsi special (tekan Special)
> pilih Constant
> pastikan semua item tercentang (Number, Text, Logical, Errors)
> OK
(bisa jadi bakal pakai fitur Sort pada kondisi AutoFilter harus ON)
7. Cut dengan menekan CTRL X
> kondisi : muncul pesan error,
-> berarti nomor 6 menghasilkan lebih dari 2^13 area, maka lakukan
sort descending saja dan selesai (ndak usah ke nomor 8 dst)
-> caranya :
*blog seluruh tabel hasil
*aktifkan autofilter (ribbon Data -> grup Sort & Filter ->
klik Filter berulang kali sampai muncul segitiga kecil di area yang diblok)
*lakukan sort pada kolom kunci (kasus ini di kolom Data)
secara Descending
8. Paste ke cell header
> klik cell header lalu tekan CTRL V
9. ke nomor 13
*(nomor 10 sampai 12 khusus untuk asumsi : ada banyak kolom di tabel hasil)
*
(ini fitur Goto Special)
10. pilih yang blank
> aktifkan fitur Goto (CTRL G)
> ke opsi special (tekan Special)
> pilih Blanks
> OK
11. hapus seluruh baris blank dengan cara :
> klik kanan hasil nomor 9 yang terblok
> pilih Delete
> pilih Entire Row
> OK
kondisi : muncul pesan error,
-> berarti nomor 6 menghasilkan lebih dari 2^13 area, maka lakukan
sort descending dulu
*caranya :
*blog seluruh tabel hasil
*aktifkan autofilter (ribbon Data -> grup Sort & Filter ->
klik Filter berulang kali sampai muncul segitiga kecil di area yang diblok)
*lakukan sort pada kolom kunci (kasus ini di kolom Data)
secara Descending
-> blok kolom Data (yang di-replace tadi, seluruh kolom)
-> ulangi nomor 9 dan 10
(yang nomor 9) : pilih yang blank dengan cara :
> aktifkan fitur Goto (CTRL G)
> ke opsi special (tekan Special)
> pilih Blanks
> OK
(yang nomor 10) : pilih yang blank dengan cara :
> klik kanan hasil nomor 9 yang terblok
> pilih Delete
> pilih Entire Row
> OK
12. ke nomor 13
*(finalisasi)*
13. minum obat puyeng
14. turn off mode nya
* Option ModeRibet 0*
15. bubuk sampai pagi lagi
;)
hehehe... maap ye, urunan solusinya gak praktis... wakakakakakakaka....
tapi kalo berhadapan dengan olah data hasil crosstab, bisa kerasa epeknya.
apalagi kalo pake pi bi e
Wassalam,
Kid.
2013/10/2 Jan Raisin <[email protected]>
> **
>
>
> hai Ferry,
>
> coba lihat file terlampir..
> ada 4 macam solusi yang ditawarkan, solusi nomor 1 adalah cara yang
> digunakan oleh pak Bagus, hanya saja di file tersebut menambahkan
> penjelasan cara menyusun formula tersebut
>
> solusi lainnya adalah menggunakan Filter, VBA, dan Pivot Table
>
> Salam,
>
> -Miss Jan Raisin-
>
>
> Pada 2 Oktober 2013 09.50, Bagus <[email protected]> menulis:
>
>> **
>>
>>
>> **
>> Dear Ferry;
>>
>> Mungkin begini;
>>
>> Array formula ya..
>>
>> =IFERROR(INDEX($A$1:$A$8,SMALL(IF(A1:A8>0,ROW(1:8)),ROW(1:8)),0),"")
>> jangan lupa akhiri dengan Crtl+Shift+Enter
>>
>> Copy paste kebawah
>>
>>
>> Wassalam
>>
>> ~ Bagus ~
>>
>>
>> ----- Original Message -----
>> *From:* Ferry Ferdian <[email protected]>
>> *To:* [email protected]
>> *Sent:* Wednesday, October 02, 2013 9:02 AM
>> *Subject:* [belajar-excel] gimana caranya memindahkan dan merubah suatu
>> kolom yang berisi data menjadi data yang kita inginin saja di kolom lain
>>
>>
>>
>> salam kenal,
>>
>> yth,
>> Pimpinan komunitas Belajar Excel
>>
>> mohon nanya gimana caranya memindahkan dan merubah suatu kolom yang
>> berisi data menjadi data yang kita inginin saja di kolom lain menggunakan
>> rumus fx, seperti contoh
>>
>> kolom A1 kolom B2 (hasil)
>> A1 0 5
>> A2 0 2
>> A3 0 35
>> A4 5
>> A5 0
>> A6 2
>> A7 35
>> A8 0
>> atau angka 0 pada kolom A1 terdapat #N/A yang tentunya hasil tersebut
>> tidak kita ingin tampilkan di kolom baru dan baris baru.
>>
>> terima kasih,
>> Ferry
>>
>> __
>>
>
>
