anya'z blog

Formula Untuk Sheet 1

kolom A2=PROPER("kode pelanggan")
kolom B2=PROPER("nama pelanggan")
kolom C2=PROPER("alamat pelanggan")
kolom D2=PROPER("no tlp")

Formula Untuk Sheet 3
kolomD4 =IF(D3="AAA",VLOOKUP(Sheet1!A3,Sheet1!$A$2:$D$7,2,0),
IF(D3="ABB",VLOOKUP(Sheet1!A4,Sheet1!$A$2:$D$7,2,0),IF(D3="ABC",VLOOKUP
(Sheet1!A5,Sheet1!$A$2:$D$7,2,0),IF(D3="ABD",VLOOKUP(Sheet1!A6,Sheet1!$A$2:$D$7,2,0),
IF(D3="ABE",VLOOKUP(Sheet1!A7,Sheet1!$A$2:$D$7,2,0))))))

kolomD5 =IF(D3="AAA",VLOOKUP(Sheet1!A3,Sheet1!$A$2:$D$7,3,0),IF(D3="ABB",VLOOKUP
(Sheet1!A4,Sheet1!$A$2:$D$7,3,0),IF(D3="ABC",VLOOKUP(Sheet1!A5,Sheet1!$A$2:$D$7,3,0),
IF(D3="ABD",VLOOKUP(Sheet1!A6,Sheet1!$A$2:$D$7,3,0),
IF(D3="ABE",VLOOKUP(Sheet1!A7,Sheet1!$A$2:$D$7,3,0))))))

KolomD6 =IF(D3="AAA",VLOOKUP(Sheet1!A3,Sheet1!$A$2:$D$7,4,0),IF(D3="ABB",VLOOKUP
(Sheet1!A4,Sheet1!$A$2:$D$7,4,0),IF(D3="ABC",VLOOKUP(Sheet1!A5,Sheet1!$A$2:$D$7,4,0),
IF(D3="ABD",VLOOKUP(Sheet1!A6,Sheet1!$A$2:$D$7,4,0),IF(D3="ABE",VLOOKUP
(Sheet1!A7,Sheet1!$A$2:$D$7,4,0))))))

kolomD9 =VLOOKUP(C9,Sheet2!$A$3:$D$7,2)

KolomE9 =VLOOKUP(C9,Sheet2!$A$3:$D$7,3)

KolomG9 =VLOOKUP(C9,Sheet2!$A$3:$D$7,4)

KolomH9 =F9*G9

KolomH15 =SUM(H9:H13)

KolomH19 =(H17-H15)

KolomC24 =terbilang(H19)

ups maaf da dkit yang kurang,,,
nie lho syarat-syarat diskonnya

1 . Jika jumlah produk yang di beli kurang dari 25 unit dan lebih dari 10 unit maka akan mendapatkan diskon 5%
2 . Jika jumlah produk yang dibeli lebih dari 25 unit dan kurang dari 50 unit maka akan mendapatkan diskon 10%
3 . Jika jumlah produk yang dibeli lebih dari sama dengan 50 unit maka akan mendapatkan diskon 25%

kolomD4
=rincian!F4

kolomA36
=PROPER("total keseluruhan pemasukan sebelum diskon")
kolomD36
=rincian!F36



Formula Untuk Sheet Rincian
kolomA4
=total!A4



kolomB4
=total!B4



kolomC4
=total!C4



kolomE4
=VLOOKUP(C4,ketentuan!$A$5:$B$12,2)
kolomF4
=D4*E4



kolomA36
=total!A36


kolomF36
=SUM(F4:F35)



Formula Untuk Sheet Diskon
kolomA4
=total!A4







kolomB4
=total!B4







kolomC4
=total!C4







kolomE4
=rincian!E4






kolomF4
=IF(AND(D4>=10,D4<25),"5%",IF(AND(D4>=25,D4<50),"10%",IF(D4>=50,"25%","0")))
kolomG5
=total!D4-(total!D4*diskon!F4)




kolomA36
=PROPER("total pemasukan setelah diskon")



kolomG36
=SUM(G4:G35)







Formula Untuk Sheet Top Five
kolomA4
=total!A4


kolomB4
=total!B4


kolomC4
=total!C4


kolomD4
=total!D4


kolomE4
=D4>=LARGE($D$4:$D$35,5)
kolomD36
=rincian!F36


Formula Untuk Sheet Gudang
kolomC4
=COUNTIF('Top Five'!B4:B35,"Anugrah")


kolomC5
=COUNTIF('Top Five'!B4:B35,"Elite")


kolomC6
=COUNTIF('Top Five'!B4:B35,"Joy Stationery")

kolomC7
=COUNTIF('Top Five'!B4:B35,"Peony")


kolomC8
=COUNTIF('Top Five'!B4:B35,"Pink - Ponk")

kolomC9
=SUM(C4:C8)




kolomC15
=SUMIF(rincian!C4:C35,"Binder",rincian!D4:D35)

kolomC16
=SUMIF(rincian!C4:C35,"Bolpoint",rincian!D4:D35)

kolomC17
=SUMIF(rincian!C4:C35,"Bolpoint",rincian!D4:D35)

kolomC18
=SUMIF(rincian!C4:C35,"Eraser",rincian!D4:D35)

kolomC19
=SUMIF(rincian!C4:C35,"Pen set",rincian!D4:D35)

kolomC20
=SUMIF(rincian!C4:C35,"Pencil",rincian!D4:D35)

kolomC21
=SUMIF(rincian!C4:C35,"Ruler",rincian!D4:D35)

kolomC22
=SUMIF(rincian!C4:C35,"Write book",rincian!D4:D35)
kolomC23
=SUM(C15:C22)