[belajar-excel] bagaimana membuat rumus Akumulasi Penjualan

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

[belajar-excel] bagaimana membuat rumus Akumulasi Penjualan

A. Hendri Bp. Algar
Dear Master Excel, saya punya problem untuk membuat rumus data akumulasi
penjualan dari Bulan januari s/d Bulan yang kita inginkan. Filenya seperti
di attachment. File tersebut memiliki 2 sheet. yaitu Sheet Data yang
merupakan data budget penjualan beberapa produk dari Jan - Des 2011, serta
data aktual penjualan dari Jan-Des 2011. kedua data tesebut tidak urut.
Sementara sheet satunya lagi adalah sheet Summary, dimana sheet ini ingin
menampilkan data aktual penjualan dan budgetnya berdasarkan bulan yang
kita yang kita minta (ditulis di sel C5). serta data akumulasi aktual
penjualan dan budgetnya dari Bulan januari 2011 s/d bulan yang kita input.
Jadi kalau C5 = 9 Maka kolom G dan H terisi data jumlah penjulan
masing-masing barang dari Januari hingga September. Mohon bantuannya. dan
Jika Sel C5 kita ganti menjadi 10, maka   kolom G dan H terisi data jumlah
penjulan masing-masing barang dari Januari hingga Oktober.

Terima kasih sebelumnya

Salam,
Agus H

TES-CEK-PENJUALAN-2011.xlsx (33K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: [belajar-excel] bagaimana membuat rumus Akumulasi Penjualan

iHaps
Ada kemudahan yg sudah dibuat oleh pak Agus, antara lain:
Range yg datanya ingin dijumlah selalu dimulai dari Bulan JANUARI (angka
bulan = 1) dan
penulisan header Bulan sudah wajar (urut Jan -to- Des ) walaupun hanya
berupa data Text bukan data Date.
Terima kasih pak Agus.

Rumus yg menjawab keinginan pak Agus cukup sederhana:
=*SUM(*OFFSET(data!$C$20,MATCH($B10,data!$B$21:$B$35,0),1,1,*$C$5*)*)*

OFFSET <http://www.box.com/shared/m8xzyafe5g> akan menghasilkan RANGE
setinggi 1 baris (menggambarkan 1 item) yg selalu dimulai dari Kolom
Ke1(Januari) &  selebar
(C5) kolom (Nilai AngkaBulan).
Kolom Jan kok dianggap Kolom ke 1, Apa sampeyan ndak ngawurr ??
Bisa !, kalau kita nongkronginya dari cell C20 (di sheet data)

*Kalau mauk tauk ceritaknya begini:*
(kalau ndak mauk tauk??... ya ndak usah dibaca !! ...gitu aja kok repot! )

* =MATCH($B10, data!$B$21:$B$35, *0*)
Mencari AngkaPosisiBaris (indexBaris) KodeBarang yg ada di B10 dilihat di
Kolom (data!$B$21:$B$35) dengan cara *exact* (*0*).
Yg dimaksud dng *cara exact *di sini adalah, kalau ketemu (persis seperti
yg dicari, Tampilkan nomorUrutPosisi-nya!, kalau ndak ketemu ndak usah
macem-macem, jangan pakai acara mengira-kira sendiri, terus terang saja
bilang *#N/A* (not available) !

Pada contoh (KodeBarang di cell B10 = *"A15"*) hasilnya 2, maksudnya A15
berada di Records yg ber-IndexbBaris *2* (=urutan ke 2 dari data yg ada /
pada tabel yg dirujuk.

* =$C$5 : dlm contoh saat ini = *9*
Ini akan digunakan untuk memberi *lebar (cacah Kolom)* pada Range yg akan
dihasilkan oleh fungsi OFFSET nantinya.

* jadi, bagian:  OFFSET(data!$C$20, MATCH($B10,data!$B$21:$B$35,0), 1, 1,
$C$5)
untuk sementara dapat dibaca sebagai :   *OFFSET(Data!C20, 2, 1, 1, 9)*
yang dlm "bahasa manusia", mungkin spt ini:

*Rujuk (refer) lah sebuah Range: *

***1*:  Yang Jika dilihat dari  *C20* (di sheet data)
*Letak Range itu dimulai dari:*
***2*:  Jarak vertikalnya (jika *+* = kebawah /jika *- *= ke atas)  =* 2*
***3*:  Jarak horisontalnya (jika *+* = kebawah /jika *- *= ke atas)  = *1*
*Dimensi Range tsb adalah:*
***4*:  Tinggi (cacah Baris) nya = *1*
***5*:  Lebar (cacah Kolom) nya = *9*

( mudah-mudahan terlihat bahwa fungsi OFFSET punya 5 arguments ! )

Walhatsil = range yg berhasil dirujuk dengan
OFFSET(data!$C$20,MATCH($B10,data!$B$21:$B$35,0),1,1,$C$5)
adalah  Range(yg berada di sheet data) * D22:L22*

Formula belum selesai temans, dia masih ingin menjumlah bilangan yg ada di
Range tsb
=*SUM(*OFFSET(data!$C$20,MATCH($B10,data!$B$21:$B$35,0),1,1,$C$5)*)*
dapat dapat dianggap  =SUM(data!D22:L22)
Jadi kalau *C5 sedang berisi : 9* ; dan * B10  sedang berisi kode barang
"A15"*.
(dua variable ini akan sering berubah lho!);  Kita ndak ferlu heran kalau
hasilnya = *49171*

-i-

oops ada yg lupa:
untuk hasil kolom H (Budget); kalau yg dimaksud dengan *budget *adalah
tabel Target Penjualan, maka...
rumusnya ditambah IFERROR berhubung tidak semua Kode Barang  di tabel
Summary ada di Tabel Data
Jika rumus menghasilkan error, tampilkan nilai 0 !
=IFERROR ( SUM(OFFSET(data!$C$5,MATCH($B10,data!$B$6:$B$15,0),1,1,$C$5)), 0
)


*

2012/2/15 A. Hendri Bp. Algar <[hidden email]>
>
> Dear Master Excel, saya punya problem untuk membuat rumus data akumulasi
penjualan dari Bulan januari s/d Bulan yang kita inginkan. Filenya seperti
di attachment. File tersebut memiliki 2 sheet. yaitu Sheet Data yang
merupakan data budget penjualan beberapa produk dari Jan - Des 2011, serta
data aktual penjualan dari Jan-Des 2011. kedua data tesebut tidak urut.
Sementara sheet satunya lagi adalah sheet Summary, dimana sheet ini ingin
menampilkan data aktual penjualan dan budgetnya berdasarkan bulan yang
kita yang kita minta (ditulis di sel C5). serta data akumulasi aktual
penjualan dan budgetnya dari Bulan januari 2011 s/d bulan yang kita input.
Jadi kalau C5 = 9 Maka kolom G dan H terisi data jumlah penjulan
masing-masing barang dari Januari hingga September. Mohon bantuannya. dan
Jika Sel C5 kita ganti menjadi 10, maka   kolom G dan H terisi data jumlah
penjulan masing-masing barang dari Januari hingga Oktober.
>
> Terima kasih sebelumnya
> Salam,
> Agus H*

TES-CEK-PENJUALAN-2011 (sum OFFSET - iHaps).xlsx (141K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: [belajar-excel] bagaimana membuat rumus Akumulasi Penjualan

M. Ferdiansyah
In reply to this post by A. Hendri Bp. Algar
Hasil coba-coba,

Semoga seperti yang diharapkan.

Wassalam,

Fdy

> Dear Master Excel, saya punya problem untuk membuat rumus data akumulasi
> penjualan dari Bulan januari s/d Bulan yang kita inginkan. Filenya seperti
> di attachment. File tersebut memiliki 2 sheet. yaitu Sheet Data yang
> merupakan data budget penjualan beberapa produk dari Jan - Des 2011, serta
> data aktual penjualan dari Jan-Des 2011. kedua data tesebut tidak urut.
> Sementara sheet satunya lagi adalah sheet Summary, dimana sheet ini ingin
> menampilkan data aktual penjualan dan budgetnya berdasarkan bulan yang
> kita yang kita minta (ditulis di sel C5). serta data akumulasi aktual
> penjualan dan budgetnya dari Bulan januari 2011 s/d bulan yang kita input.
> Jadi kalau C5 = 9 Maka kolom G dan H terisi data jumlah penjulan
> masing-masing barang dari Januari hingga September. Mohon bantuannya. dan
> Jika Sel C5 kita ganti menjadi 10, maka   kolom G dan H terisi data jumlah
> penjulan masing-masing barang dari Januari hingga Oktober.
>
> Terima kasih sebelumnya
>
> Salam,
> Agus H
>



____________ _________ _________ _________ _________ _________ _________
Notice  : This e-mail (and any attachment) is strictly confidential and
is intended only for the addressee(s)above. Is strictly prohibited to
forward, print, copy, or otherwise reproduce this message that would
allow it to be viewed by any individual not originally listed as
a recipient(s). If you have received this e-mail in error, please
immediately notify the sender and delete this message. The opinions
expressed in this e-mail (and any attachment) are those of the individual
sender and may not necessarily reflect the views of PT Pusri.
____________ _________ _________ _________ _________ _________ _________

Fdy - TES-CEK-PENJUALAN-2011.xlsx (40K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: [belajar-excel] bagaimana membuat rumus Akumulasi Penjualan

iHaps
In reply to this post by iHaps
maafkan, koreksi salah tulis...

tertulis
***3*:  Jarak horisontalnya (jika *+* = kebawah /jika *- *= ke atas)  = *1*

seharusnya
***3*:  Jarak horisontalnya (jika *+* = keKANAN /jika *- *= ke KIRI)  = *1*

[image: arg fungsi offset.PNG]


2012/2/15 i Haps <[hidden email]>

> yang dlm "bahasa manusia", mungkin spt ini:
>
> *Rujuk (refer) lah sebuah Range: *
>
> ***1*:  Yang Jika dilihat dari  *C20* (di sheet data)
> *Letak Range itu dimulai dari:*
> ***2*:  Jarak vertikalnya (jika *+* = kebawah /jika *- *= ke atas)  =* 2*
> ***3*:  Jarak horisontalnya (jika *+* = kebawah /jika *- *= ke atas)  = *1
> *
> *Dimensi Range tsb adalah:*
> ***4*:  Tinggi (cacah Baris) nya = *1*
> ***5*:  Lebar (cacah Kolom) nya = *9*
>
> ( mudah-mudahan terlihat bahwa fungsi OFFSET punya 5 arguments ! )
>
> Walhatsil = range yg berhasil dirujuk dengan
> OFFSET(data!$C$20,MATCH($B10,data!$B$21:$B$35,0),1,1,$C$5)
> adalah  Range(yg berada di sheet data) * D22:L22*
>

arg fungsi offset.PNG (32K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: [belajar-excel] bagaimana membuat rumus Akumulasi Penjualan

A. Hendri Bp. Algar
In reply to this post by A. Hendri Bp. Algar
Ok. Terima kasih atas bantuan Ibu Hapsari dan Bp. Ferdiansyah. Butuh waktu
1/2 hari juga bagi saya memhami rumus yang kalian rekomendasikan. Terutama
untuk Fungsi OFFSET yang kebetulan saya memang kurang familiar. Ternyata
simple sekali rumusnya. Padahal saya sudah berfikir bahwa rumusnya akan
kompleks sekali sebagaimana rumus yang saya buat di kolom sebelumnya (kolom
penjualan per bulan).
Jadi saya memang harus banyak belajar lagi nih ttg. variasi-variasi rumus

Salam,
Agus Hendri