Tecnologia, programação e muito Visual FoxPro.

terça-feira, 26 de fevereiro de 2013

Bls: ]] XL-mania [[ Weighted Average dengan kriteria

 

Dear Dody,

sekali lagi saya menyarankan untuk kasus seperti ini lebih baik menggunakan pivot table saja,..
btw,.. pivot table merupakan fitur yang sangat 'sakti' di excel... kalau buat laporan manual bisa berhari2, sedangkan menggunakan pivot table bisa dengan 5 menit saja tanpa formula yang ribet-ribet.. dengan pivot table nanti bisa dibuat sub totalnya berupa total, atau pun rata-rata seperti kasus ini,..

namun kalau memaksa menggunakan formula, bisa dicoba formula berikut :

=IF(OR(A2<>A1,B2<>B1),SUMPRODUCT(OFFSET(C2,0,0,COUNTIFS(A:A,A2,B:B,B2),1)
*OFFSET(D2,0,0,COUNTIFS(A:A,A2,B:B,B2),1))
/SUM(OFFSET(C2,0,0,COUNTIFS(A:A,A2,B:B,B2),1)),"")

prinsipnya mengganti range nya dengan fungsi offset dan countifs
fungsi di atas dengan asumsi data sudah urut berdasarkan kolom A lalu B

semoga membantu,
hachi


Dari: dody ginandjar <dody_gin@yahoo.com>
Kepada: "XL-mania@yahoogroups.com" <XL-mania@yahoogroups.com>
Dikirim: Selasa, 26 Februari 2013 9:21
Judul: ]] XL-mania [[ Weighted Average dengan kriteria

 

Selamat Pagi,

Salam kenal, (member baru)

Saya punya data pengeboran tanah seperti berikut :

---+---A------+---B--------+--C--------+----D--------------+---
1---+-Hole_ID-+-Tipe-+---Tebal---+-Kadar-+-Kadar Rata2 --+---
2---+-PA01----+-BS----+---1.5-----+--40---+-----40.50-----------+---
3---+-PA01----+-BS----+---0.5-----+--42---+------------------------+---   
4---+-PA01----+-GR----+---1.5-----+--47---+-----47.82-----------+---
5---+-PA01----+-GR----+---1.5-----+--48---+------------------------+---
6---+-PA01----+-GR----+---0.8-----+--49---+------------------------+   
7---+-PA01----+-YS----+---1.5-----+--48---+-----49.19--------------+---
8---+-PA01----+-YS----+---1.5-----+--48---+------------------------+---
9---+-PA01----+-YS----+---1.5-----+--48---+------------------------+---
10--+-PA01----+-YS----+---1.5-----+--48---+------------------------+---
11--+-PA01----+-YS----+---1.5-----+--50---+------------------------+---   
12--+-PA01----+-YS----+---0.5-----+--49---+------------------------+---   
13--+-PA01----+-GS----+---1.5-----+--30---+-----30.24--------------+---
14--+-PA01----+-GS----+---0.2-----+--32---+------------------------+---   

15--+-PA02----+-BS----+---1.5-----+--40---+-----41.14--------------+---
16--+-PA02----+-BS----+---1.5-----+--42---+------------------------+---   
17--+-PA02----+-BS----+---0.5-----+--42---+------------------------+---   
18--+-PA02----+-GR----+---1.5-----+--48---+-----47.70--------------+---
19--+-PA02----+-GR----+---0.8-----+--49---+------------------------+   
20--+-PA02----+-YS----+---1.5-----+--48---+-----49.30--------------+---
21--+-PA02----+-YS----+---1.5-----+--48---+------------------------+---
22--+-PA02----+-YS----+---1.5-----+--48---+------------------------+---
23--+-PA02----+-YS----+---0.5-----+--48---+------------------------+---
24--+-PA02----+-GS----+---1.5-----+--30---+-----30-----------------+---

>
>
>

2000 --+ PA200 +-BS.dst.

.
.

Secara Manual saya membuat formula untuk setiap Kadar Rata2 per litho seperti ini :
Cell D2  diisi : =SUMPRODUCT(B2:B3*C2:C3)/SUM(B2:B3)
Cell D4  diisi : =SUMPRODUCT(B4:B6*C4:C6)/SUM(B4:B6)
Cell D7  diisi : =SUMPRODUCT(B7:B12*C7:C12)/SUM(B7:B12)
Cell D13 diisi : =SUMPRODUCT(B13:B14*C13:C14)/SUM(B13:B14)
Cell D15 diisi : =SUMPRODUCT(C15:C17*D15:D17)/SUM(C15:C17)
Cell D18 diisi : =SUMPRODUCT(C18:C19*D18:D19)/SUM(C18:C19)
Cell D20 diisi : =SUMPRODUCT(C20:C23*D20:D23)/SUM(C20:C23)
Cell D24 diisi : =SUMPRODUCT(C24:C24*D24:D24)/SUM(C24:C24)

Tanya : Adakah Formula alternatif untuk menghitung kadar rata dengan kriteria nam Tipe ? mengingat data saya ada ribuan baris, sehingga repot kalau harus dengan cara diatas
(contoh file terlampir)

Terima kasih

Salam,

Dody 


__._,_.___
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (4)
Recent Activity:
+-:: XL-mania ::::::::::::::::::::----------------------------------+
| Merry Christmas. May God bless you and your family...             |
+-------------------------------------------------------------------+
| DILARANG : MLM, money game, OOT, iklan tanpa izin, SARA, testing, |
| pembicaraan pribadi, one line message,  melecehkan,  tidak sopan. |
+-------------------------------------------------------------------+
| Buat subjek yang kreatif, jangan : "tanya", "help", "mohon bantu" |
| Usahakan besar attachment < 200 kb. Gunakan  winzip  jika  perlu. |
+-------------------------------------------------------------------+
| Ajak teman-teman Anda bergabung dengan mengirim e-mail kosong ke  |
| XL-mania-subscribe@yahoogroups.com atau kirimkan mereka file dari |
| http://groups.yahoo.com/group/XL-mania/files/Promotion/           |
+-------------------------------------------------------------------+
| Berikan testimoni di :                                            |
| http://www.xl-mania.com/2008/06/testimoni-xl-mania.html           |
+-------------------------------------------------------------------+
| Message lama ada di :                                             |
| http://groups.yahoo.com/group/XL-mania/messages [perlu yahoo id]  |
| http://www.mail-archive.com/xl-mania@yahoogroups.com              |
+-------------------------------------------------------------------+
.

__,_._,___

Nenhum comentário:

Arquivo do blog