Senin, 02 Januari 2012

[belajar-excel] Digest Number 1459

Messages In This Digest (2 Messages)

Messages

1a.

Re: Fungsi SUBTOTAL dan Fungsi OFFSET

Posted by: "Kid Mr." mr.nmkid@gmail.com   nmkid.family@ymail.com

Mon Jan 2, 2012 4:21 am (PST)



mbake eh mase onto sepertinya kelupaan attachment file.

Sambil menunggu attachment filenya,
Kira-kira gambaran tentang SubTotal : mengkalkulasi seluruh nilai yang
tampak saja (tidak disembunyikan)
=SubTotal( jenis_agregat , range_yang_diagregat )

Jenis agregat ada 2 macam, yaitu yang 3 digit dan yang 2 digit
Jenis agregat 3 digit akan mengkalkulasi semua nilai yang tampak saja (yang
tidak disembunyikan oleh autofilter atau oleh user dengan hide row)
Jenis agregat 2 digit akan mengkalkulasi semua nilai yang tidak
disembunyikan oleh user.
2 digit terakhir dari jenis agregat adalah macam-macam fungsi agregat yang
digunakan, misal 2 untuk Count atau 9 untuk Sum dsb.
3 digitnya menjadi 102 untuk count atau 109 untuk Sum

Coba lah di A1 beri nama data dengan Data
mulai A2, isi dengan =mod(row(),2)
copy sampai cell A10

Kemudian di
C1 tulis : 2 digit jenis agregat :
D1 isi dengan formula : =SubTotal( 2 , A2:A10 )
F1 tulis : 3 digit agregat :
G1 isi dengan formula : =SubTotal( 102 , A2:A10 )

Kemudian, coba blok baris 3 dan 6, lalu klik kanan dan pilih Hide
Lihat apa yang terjadi dengan nilai di D1 dan G1
Jika sudah puas, unhide baris 3 dan 6

Kemudian, blok A1 sampai A10, aktifkan autofilter
lalu pilih filter yang bernilai 1 saja (hilangkan centang pada yang selain
1)
Lalu lihat hasil di D1 dan G1

Selain itu, fungsi SubTotal juga akan bermanfaat dalam suatu array formula
yang membutuhkan nilai Sum dari suatu range tertentu.
Untuk yang satu ini, mungkin bisa menyusul saja ya... soalnya waktunya udah
habis dan pengawasnya udah dateng...

permisi...

Kid.

2012/1/2 o' Seno <ontoseno84@gmail.com>

> **
>
>
> bukan pengasuh milis boleh menanggapi nggak ya ??
> terlampir contoh penggunaan fungsi subtotal
>
>
>
> 2011/12/31 Giyanto Widodo <widodomuh2@yahoo.co.id>
> > Yth. Pengasuh Be-Excel,
> >
> > Mohon penjelasan mengenai kegunaan dan contoh Fungsi SUBTOTAL dan Fungsi
> OFFSET,
> > Terima kasih sebelumnya.
> > Salam
> > Giyanto Widodo
>
>
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (4)
2a.

Re: Bgm cara lookup dg nilai yg sama

Posted by: "Kid Mr." mr.nmkid@gmail.com   nmkid.family@ymail.com

Mon Jan 2, 2012 4:40 am (PST)



Mumpung dapat ijin tambahan 15 menit.

Kasus ini mungkin bahasa sederhananya adalah ambil item ke-n

Salah satu caranya adalah dengan array formula :
=INDEX($D$9:$D$18,*
SMALL(IF($C$9:$C$18=F9,ROW($C$9:$C$18)-ROW($C$8)),COUNTIF(F$9:F9,F9))*)
Yang biru adalah bagian yang menentukan item ke-n yang dimaksud.

Array formula di atas akan menghasilkan error jika ternyata kriterianya
adalah
>> blank
>> tidak ada di tabel data
>> nomor item yang akan diambil melebihi jumlah item tersebut di tabel data
(misal ambil item B ke 7 padahal di tabel B hanya ada 3)

Untuk trap error, bisa digunakan kondisi :
(COUNTIF(F$9:F9,F9)>COUNTIF($C$9:$C$18,F9))+(LEN(F9)=0)
"jumlah item diminta > jumlah item tersedia atau kriteria blank"

Maka susunan formula beserta error trapnya menjadi :
=IF(*(COUNTIF(F$9:F9,F9)>COUNTIF($C$9:$C$18,F9))+(LEN(F9)=0)*,"",*
INDEX($D$9:$D$18,SMALL(IF($C$9:$C$18=F9,ROW($C$9:$C$18)-ROW($C$8)),COUNTIF(F$9:F9,F9)))
*)
Yang biru adalah array formula dasarnya (di atas sana)
yang hijau adalah kondisi error trapnya.

Moga-moga cukup jelas.

Kid.

2012/1/2 Suyatno <ocha.yatno@gmail.com>

> **
>
>
> Dear All****
>
> Maaf jika kurang informasinya tetapi kurang lebih spt yg dimaksud
> M'Sudarsono ****
>
> Jadi data yg diambil data yg paling atas kemudian jika diketik berikuttnya
> maka data kedua yg diambil dst****
>
> Terima kasih sebelum dan sesudahnya****
>
> ** **
>
> Salam****
>
> Ocha****
>
> ** **
>
> *From:* belajar-excel@yahoogroups.com [mailto:
> belajar-excel@yahoogroups.com] *On Behalf Of *jkssxls Sudarsono
> *Sent:* 02 Januari 2012 11:30
> *To:* Belajar-Excel Yahoo Groups
> *Subject:* RE: [belajar-excel] Bgm cara lookup dg nilai yg sama****
>
> ** **
>
> ****
>
> ****
>
> ****
>
> ****
>
> Hasil yg diinginkan****
>
> ****
>
> ****
>
> ****
>
> Item****
>
> Lot****
>
> ****
>
> Item****
>
> Lot****
>
> ****
>
> ****
>
> A****
>
> 1235****
>
> ****
>
> A****
>
> 1235****
>
> ****
>
> 9****
>
> A****
>
> 564****
>
> ****
>
> A****
>
> 564****
>
> ****
>
> 10****
>
> A****
>
> 123****
>
> ****
>
> B****
>
> A124****
>
> ****
>
> 13****
>
> A****
>
> 9856****
>
> ****
>
> C****
>
> 564****
>
> ****
>
> 16****
>
> B****
>
> A124****
>
> ****
>
> C****
>
> V125****
>
> ****
>
> 17****
>
> B****
>
> B256****
>
> ****
>
> C****
>
> N546****
>
> ****
>
> 18****
>
> B****
>
> 265****
>
> ****
>
> ****
>
> ****
>
> ****
>
> ****
>
> C****
>
> 564****
>
> ****
>
> ****
>
> ****
>
> ****
>
> ****
>
> C****
>
> V125****
>
> ****
>
> ****
>
> ****
>
> ****
>
> ****
>
> C****
>
> N546****
>
> ****
>
> ****
>
> ****
>
> ****
>
> ****
>
>
> Maksudnya sebanyak item yang diisi ya ? , misal :
> jika diisi A maka
> A pertama yaitu v125
> jika diisi A maka
> A kedua , yaitu 564
> jika diisi B , maka
> B pertama yaitu A124 ,
> dst
>
> rumus di I9 :
> ****
>
> ****
>
> ****
>
>
> =MAX(IF(COUNTIF(INDIRECT("C9:C"&ROW($F$9:$F$18)),$F9)=COUNTIF(F$9:F9,F9),ROW($F$9:$F$18)))
> ****
>
>
> =MAX(IF(COUNTIF(INDIRECT("C9:C"&ROW($F$9:$F$18)),$F10)=COUNTIF(F$9:F10,F10),ROW($F$9:$F$18)))
> ****
>
>
> =MAX(IF(COUNTIF(INDIRECT("C9:C"&ROW($F$9:$F$18)),$F11)=COUNTIF(F$9:F11,F11),ROW($F$9:$F$18)))
> ****
>
>
> =MAX(IF(COUNTIF(INDIRECT("C9:C"&ROW($F$9:$F$18)),$F12)=COUNTIF(F$9:F12,F12),ROW($F$9:$F$18)))
> ****
>
>
> =MAX(IF(COUNTIF(INDIRECT("C9:C"&ROW($F$9:$F$18)),$F13)=COUNTIF(F$9:F13,F13),ROW($F$9:$F$18)))
> ****
>
>
> =MAX(IF(COUNTIF(INDIRECT("C9:C"&ROW($F$9:$F$18)),$F14)=COUNTIF(F$9:F14,F14),ROW($F$9:$F$18)))
> ****
>
> ****
>
> ****
>
> ****
>
> ****
>
>
> adapun rumus G9 :
> ****
>
> =INDIRECT("D"&I9)****
>
> =INDIRECT("D"&I10)****
>
> =INDIRECT("D"&I11)****
>
> =INDIRECT("D"&I12)****
>
> =INDIRECT("D"&I13)****
>
> =INDIRECT("D"&I14)****
>
>
> rumus Array Formula :
>
> =MAX(IF(COUNTIF(INDIRECT("C9:C"&ROW($F$9:$F$18)),$F9)=COUNTIF(F$9:F9,F9),ROW($F$9:$F$18)))
>
> *INDIRECT(* "C9:C"&ROW($F$9:$F$18 *)* berarti :
> INDIRECT("C9:C"&ROW($F$9) ==> C9:C9
> INDIRECT("C9:C"&ROW($F$10) ==> C9:C10
> INDIRECT("C9:C"&ROW($F$11) ==> C9:C11
> INDIRECT("C9:C"&ROW($F$12) ==> C9:C12
> INDIRECT("C9:C"&ROW($F$13) ==> C9:C13
> INDIRECT("C9:C"&ROW($F$14) ==> C9:C14
> INDIRECT("C9:C"&ROW($F$15) ==> C9:C15
> INDIRECT("C9:C"&ROW($F$16) ==> C9:C16
> INDIRECT("C9:C"&ROW($F$17) ==> C9:C17
> INDIRECT("C9:C"&ROW($F$18) ==> C9:C18
> sehingga *COUNTIF( *INDIRECT("C9:C"&ROW($F$9:$F$18)),$F9 *)* berarti :
> COUNTIF ( C9:C9 , $F9 )
> COUNTIF ( C9:C10 , $F9 )
> COUNTIF ( C9:C11 , $F9 )
> COUNTIF ( C9:C12 , $F9 )
> COUNTIF ( C9:C13 , $F9 )
> COUNTIF ( C9:C14 , $F9 )
> COUNTIF ( C9:C15 , $F9 )
> COUNTIF ( C9:C16 , $F9 )
> COUNTIF ( C9:C17 , $F9 )
> COUNTIF ( C9:C18 , $F9 )
> hasilnya :****
>
> 1****
>
> 2****
>
> 3****
>
> 4****
>
> 4****
>
> 4****
>
> 4****
>
> 4****
>
> 4****
>
> 4****
>
> 4****
>
>
>
> *COUNTIF( *F$9:F9,F9 *)* , berarti :
> menghitung jumlah kemunculan isian : ( misal A ,A , B ,C,C )
> jika rumusnya COUNTIF(F$9:F9,F9) berarti kemunculan A ==> hasil 1
> jika rumusnya COUNTIF(F$9:F10,F10) berarti kemunculan A ==> hasil 2
> jika rumusnya COUNTIF(F$9:F11,F11) berarti kemunculan B ==> hasil 1
> jika rumusnya COUNTIF(F$9:F12,F12) berarti kemunculan C ==> hasil 1
> jika rumusnya COUNTIF(F$9:F13,F13) berarti kemunculan C ==> hasil 2
>
>
> *ROW (* $F$9:$F$18 *)* menghasilkan {9, 10, 11, 12, 13, 14, 15, 16, 17,
> 18}
>
> karena *COUNTIF( *F$9:F9,F9* )* hasilnya 1 , maka
> *IF(* COUNTIF( INDIRECT( "C9:C"& ROW( $F$9:$F$18 ) ),$F9) =COUNTIF(
> F$9:F9,F9 ), ROW( $F$9:$F$18 ) *) *
> menghasilkan :
> ****
>
> 9****
>
> FALSE****
>
> FALSE****
>
> FALSE****
>
> FALSE****
>
> FALSE****
>
> FALSE****
>
> FALSE****
>
> FALSE****
>
> FALSE****
>
> FALSE****
>
> untuk mengambil nilai 9 saja , gunakan MAX :
>
> =*MAX **(*IF(COUNTIF(INDIRECT("C9:C"&ROW($F$9:$F$18)),$F9)=COUNTIF(F$9:F9,F9),ROW($F$9:$F$18))
> *)*
>
> *Kasus :*
> 1. jika yang diisi tak ada pada data , misal E
> 2. jika yang diisi , melebihan jumlah yang ada pada data, misal A ke
> lima padahal A hanya ada 4 .
>
>
> =MAX(IF(COUNTIF(INDIRECT("C9:C"&ROW($F$9:$F$18)),$F17)=COUNTIF(F$9:F17,F17),ROW($F$9:$F$18)))
>
> menghasilkan *0 *, untuk itu :
> *rumus :*
> =*INDIRECT(* "D"&I9 *)* , perlu diganti :
>
> =*IF(* I9>0,INDIRECT("D"&I9) ,"" *)*
>
>
> *Selamat tahun baru 2012 , *
> *Semoga Kebahagiaan , Kesehatan dan Kesejahteraan dapat kita raih*
>
>
>
>
>
>
>
>
>
>
>
> ****
> ------------------------------
>
> To: belajar-excel@yahoogroups.com
> From: hapsari.stlizbeth@gmail.com
> Date: Mon, 2 Jan 2012 04:10:00 +0700
> Subject: Re: [belajar-excel] Bgm cara lookup dg nilai yg sama
>
> ****
>
> Setelah melihat tabel²nya, ternyata saya ndak tahu babar-blass apa yg
> dimaksudkan ****
>
> oleh Ocha / Yatno, Mohon maaf....****
>
> ** **
>
> [image: Pertanyaan yg terlalu ringkas tidak menguntungkan.PNG]****
>
> ** **
>
> Jika berkenan mohon ditambahkan penjelasan:****
>
> * SYARAT APA yg dikenakan kpd data di tabel SUMBER *agar BOLEH di"pindah"*
> ****
>
> * ke tabel HASIL* (yg diinginkan)****
>
> (syarat boleh banyak namun harus logis)****
>
> ** **
>
> Di Tabel SUMBER, nilai baris pertama (cell D9) = tidak sama dengan nilai
> MANAPUN ****
>
> di Tabel Sumber tsb, tetapi kok "boleh" Pindah ke tabel HASIL ya..?****
>
> ( padahal petanyaannya : *"bgmana u/transfer data yg nilainya sama"* )****
>
> ** **
>
> Sepertinya* pertanyaan 7_kata* itu kok (bagi saya ) masih sulit dicerna...
> ****
>
> ** **
>
> Terima kasih...****
>
> - iHaps****
>
> ( ibu Hapsah ? tukang gadho-gadho gang sebelah..? )****
>
> ** **
>
> ** **
>
> ** **
>
> *2012/1/1 Suyatno <ocha.yatno@gmail.com>*****
>
> *Dear All*****
>
> *Mohon **pencerahannya bagaimana u/ transfer data yg nilainya sama*****
>
> *Salam*****
>
> *Ocha*****
>
> ** **
>
> ****
> ------------------------------
>
> No virus found in this message.
> Checked by AVG - www.avg.com
> Version: 10.0.1416 / Virus Database: 2109/4117 - Release Date: 01/01/12***
> *
>
>
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (6)
Recent Activity
Visit Your Group
Yahoo! Finance

It's Now Personal

Guides, news,

advice & more.

Dog Zone

on Yahoo! Groups

Join a Group

all about dogs.

Yahoo! Groups

Parenting Zone

Tips for a happy,

healthy home

Need to Reply?

Click one of the "Reply" links to respond to a specific message in the Daily Digest.

Create New Topic | Visit Your Group on the Web
---------------------------------------------------------------------
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
---------------------------------------------------------------------
MARKETPLACE

Stay on top of your group activity without leaving the page you're on - Get the Yahoo! Toolbar now.

Diposting oleh EXCEL FEED LEARNING di 06.34

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)