Re: [belajar-excel] Rumus Total Harga
hehehe
bener-bener repot kalau ada banyak ya
Walau ndak tahu hasilnya bener atau tidak,
coba ubah *array formula* ini : (jangan lupa tulis formula tanpa {} dan
jangan ENTER melainkan CTRL SHIFT ENTER)
*** Perhatikan bagian yang diwarna *biru* dan *merah* ***
=SUM(IFERROR(MID(SUBSTITUTE(LOWER(VLOOKUP($H7,$B$4:$D$12,3,0))&1,"/","
"),SEARCH({*"dus","bal","pck","box","pcs"*},SUBSTITUTE(LOWER(VLOOKUP($H7,$B$4:$D$12,3,0)),"/","
"))+3,5),0)*SUMPRODUCT(($B$4:$B$12=$H7)*$E$4:$E$12/$C$4:$C$12)*({*1,1,2,2,3*
}={1;2;3})*TRANSPOSE($I7:$K7))
Jadi,
1. Yang *biru* dan *merah* itu berpasangan
2. Yang *biru* adalah bunyi teks satuan
3. Yang *merah* adalah kelompok satuan
satuan berbunyi : *"dus","bal","pck","box","pcs"*
di kelompok : *1 , 1, 2, 2 ,3*
Bagaimana jika ada tambahan teks kelompok seperti satuan 'Kid' masuk satu
kelompok dengan dus, bal, dsb
*"dus","bal","pck","box","pcs", "kid"*
*1 , 1, 2, 2 ,3* ,*1*
Berarti bunyi formula menjadi :
=SUM(IFERROR(MID(SUBSTITUTE(LOWER(VLOOKUP($H7,$B$4:$D$12,3,0))&1,"/","
"),SEARCH({*"dus","bal","pck","box","pcs",
"kid"*},SUBSTITUTE(LOWER(VLOOKUP($H7,$B$4:$D$12,3,0)),"/","
"))+3,5),0)*SUMPRODUCT(($B$4:$B$12=$H7)*$E$4:$E$12/$C$4:$C$12)*({*1,1, 2 ,2
, 3*, *1*}={1;2;3})*TRANSPOSE($I7:$K7))
Bagusnya lagi kalau teks satuan dibentuk menjadi tabel referensi
tersendiri, jadi bisa dirujuk dengan range
Kalau sama-sama buat tabel referensi, capeknya nulis
satuanXX/satuanYY/satuan sama dengan membuat tabel referensi yang baik
btw,
namanya opsi... ya kan...
2015-06-01 18:22 GMT+10:00 Dede pmaho_l...@nabatisnack.co.id
[belajar-excel] :
>
>
> Dear Mr Zul,
>
>
>
> Coba formula ini di cell L8 :
>
>
>
>
> =VLOOKUP(H8;$B$5:$E$13;4;)*I8+VLOOKUP(H8;$B$5:$E$13;4;)/IFERROR(IF(LEN(VLOOKUP(H8;$B$5:$E$13;3;))=9;LEFT(RIGHT(VLOOKUP(H8;$B$5:$E$13;3;);FIND("/";VLOOKUP(H8;$B$5:$E$13;3;)));FIND("/";RIGHT(VLOOKUP(H8;$B$5:$E$13;3;);FIND("/";VLOOKUP(H8;$B$5:$E$13;3;-1);MID(LEFT(VLOOKUP(B5;$B$5:$E$13;3;);FIND("/Pcs";VLOOKUP(B5;$B$5:$E$13;3;))-1);FIND("/Pck";VLOOKUP(B5;$B$5:$E$13;3;))+4;50));MID(LEFT(D5;FIND("/Pcs";D5)-1);FIND("/Rtg";D5)+4;50))*J8+VLOOKUP(H8;$B$5:$E$13;4;)/VLOOKUP(H8;$B$5:$E$13;2;)*K8
>
>
>
> Dengan komposisi ke 5 jenis dibawah yah.
>
> Semoga sesuai harapan.
>
>
>
> Salam,
>
> DD
>
> *From:* Dede pmaho_l...@nabatisnack.co.id [belajar-excel] [mailto:
> belajar-excel@yahoogroups.com]
> *Sent:* 01 Juni 2015 13:38
> *To:* belajar-excel@yahoogroups.com
> *Subject:* RE: [belajar-excel] Rumus Total Harga
>
>
>
>
>
> Dear Mr Zul,
>
>
>
> Formula saya hanya bisa baca dengan komposisi yang mengandung karakter :
>
> “/Pck” dan “Pcs” dalam satu cell, dan dengan komposisi “/Pcs” di akhir
> cell.
>
>
>
> Mungkin agar lebih tepatnya ada berapa jenis komposisi pastinya ?
>
> Contoh :
>
> 1. Dus30/Pck15/Pcs
>
> 2. Bal80/Pck20/Pcs
>
> 3. Dus30/Pcs
>
> 4. Ball30/Pck15/Pcs
>
> 5. Dus30/Rtg15/Pcs
>
> 6. ?
>
> 7. ?
>
> 8. ?
>
> 9. Dst...
>
>
>
> Barulah akan ketahuan tepatnya formula untuk case tersebut.
>
>
>
> Salam,
>
> DD
>
>
>
> *From:* Zulfikar N zulfikar_rout...@yahoo.com [belajar-excel] [mailto:
> belajar-excel@yahoogroups.com]
> *Sent:* 01 Juni 2015 09:44
> *To:* belajar-excel@yahoogroups.com
> *Subject:* Bls: [belajar-excel] Rumus Total Harga
>
>
>
>
>
> Dear Master DD,
>
> Sebelumnya di kolom Komposit Dus30/Pck15/Pcs, pertanyaan saya bagaimana
> jika isi pada kolom komposit berisi Ball30/Pck15/Pcs, Dus30/Rtg15/Pcs,
> Dus30/Cup dll. Mohon pencerahannya.
>
>
>
>
> salam,
>
> Zulfikar
>
>
>
>
>
>
>
> Pada Jumat, 29 Mei 2015 14:49, "Dede pmaho_l...@nabatisnack.co.id
> [belajar-excel]" menulis:
>
>
>
>
>
> Dear Mr Zul,
>
>
>
> Coba formula ini di cell L8 :
>
>
>
>
> =VLOOKUP(H8;$B$5:$E$13;4;)*I8+VLOOKUP(H8;$B$5:$E$13;4;)/IFERROR(MID(LEFT(VLOOKUP(H8;$B$5:$E$13;3;);FIND("/Pcs";VLOOKUP(H8;$B$5:$E$13;3;))-1);FIND("/Pck";VLOOKUP(H8;$B$5:$E$13;3;))+4;50);LEFT(RIGHT(VLOOKUP(H8;$B$5:$E$13;3;);FIND("/Pcs";VLOOKUP(H8;$B$5:$E$13;3;)));FIND("/Pcs";RIGHT(VLOOKUP(H8;$B$5:$E$13;3;);FIND("/Pcs";VLOOKUP(H8;$B$5:$E$13;3;-1))*J8+VLOOKUP(H8;$B$5:$E$13;4;)/VLOOKUP(H8;$B$5:$E$
RE: [belajar-excel] Rumus Total Harga
Dear Mr Zul,
Sori revisi :
=VLOOKUP(H8;$B$5:$E$13;4;)*I8+VLOOKUP(H8;$B$5:$E$13;4;)/IFERROR(IF(LEN(VLOOKUP(H8;$B$5:$E$13;3;))=9;LEFT(RIGHT(VLOOKUP(H8;$B$5:$E$13;3;);FIND("/";VLOOKUP(H8;$B$5:$E$13;3;)));FIND("/";RIGHT(VLOOKUP(H8;$B$5:$E$13;3;);FIND("/";VLOOKUP(H8;$B$5:$E$13;3;-1);MID(LEFT(VLOOKUP(H8;$B$5:$E$13;3;);FIND("/Pcs";VLOOKUP(H8;$B$5:$E$13;3;))-1);FIND("/Pck";VLOOKUP(H8;$B$5:$E$13;3;))+4;50));MID(LEFT(VLOOKUP(H8;$B$5:$E$13;3;);FIND("/Pcs";VLOOKUP(H8;$B$5:$E$13;3;))-1);FIND("/Rtg";VLOOKUP(H8;$B$5:$E$13;3;))+4;50))*J8+VLOOKUP(H8;$B$5:$E$13;4;)/VLOOKUP(H8;$B$5:$E$13;2;)*K8
Salam,
DD
From: Dede pmaho_l...@nabatisnack.co.id [belajar-excel]
[mailto:belajar-excel@yahoogroups.com]
Sent: 01 Juni 2015 15:22
To: belajar-excel@yahoogroups.com
Subject: RE: [belajar-excel] Rumus Total Harga
Dear Mr Zul,
Coba formula ini di cell L8 :
=VLOOKUP(H8;$B$5:$E$13;4;)*I8+VLOOKUP(H8;$B$5:$E$13;4;)/IFERROR(IF(LEN(VLOOKUP(H8;$B$5:$E$13;3;))=9;LEFT(RIGHT(VLOOKUP(H8;$B$5:$E$13;3;);FIND("/";VLOOKUP(H8;$B$5:$E$13;3;)));FIND("/";RIGHT(VLOOKUP(H8;$B$5:$E$13;3;);FIND("/";VLOOKUP(H8;$B$5:$E$13;3;-1);MID(LEFT(VLOOKUP(B5;$B$5:$E$13;3;);FIND("/Pcs";VLOOKUP(B5;$B$5:$E$13;3;))-1);FIND("/Pck";VLOOKUP(B5;$B$5:$E$13;3;))+4;50));MID(LEFT(D5;FIND("/Pcs";D5)-1);FIND("/Rtg";D5)+4;50))*J8+VLOOKUP(H8;$B$5:$E$13;4;)/VLOOKUP(H8;$B$5:$E$13;2;)*K8
Dengan komposisi ke 5 jenis dibawah yah.
Semoga sesuai harapan.
Salam,
DD
From: Dede pmaho_l...@nabatisnack.co.id [belajar-excel]
[mailto:belajar-excel@yahoogroups.com]
Sent: 01 Juni 2015 13:38
To: belajar-excel@yahoogroups.com
Subject: RE: [belajar-excel] Rumus Total Harga
Dear Mr Zul,
Formula saya hanya bisa baca dengan komposisi yang mengandung karakter :
“/Pck” dan “Pcs” dalam satu cell, dan dengan komposisi “/Pcs” di akhir cell.
Mungkin agar lebih tepatnya ada berapa jenis komposisi pastinya ?
Contoh :
1. Dus30/Pck15/Pcs
2. Bal80/Pck20/Pcs
3. Dus30/Pcs
4. Ball30/Pck15/Pcs
5. Dus30/Rtg15/Pcs
6. ?
7. ?
8. ?
9. Dst...
Barulah akan ketahuan tepatnya formula untuk case tersebut.
Salam,
DD
From: Zulfikar N zulfikar_rout...@yahoo.com [belajar-excel]
[mailto:belajar-excel@yahoogroups.com]
Sent: 01 Juni 2015 09:44
To: belajar-excel@yahoogroups.com
Subject: Bls: [belajar-excel] Rumus Total Harga
Dear Master DD,
Sebelumnya di kolom Komposit Dus30/Pck15/Pcs, pertanyaan saya bagaimana jika
isi pada kolom komposit berisi Ball30/Pck15/Pcs, Dus30/Rtg15/Pcs, Dus30/Cup
dll. Mohon pencerahannya.
salam,
Zulfikar
Pada Jumat, 29 Mei 2015 14:49, "Dede pmaho_l...@nabatisnack.co.id
[belajar-excel]" menulis:
Dear Mr Zul,
Coba formula ini di cell L8 :
=VLOOKUP(H8;$B$5:$E$13;4;)*I8+VLOOKUP(H8;$B$5:$E$13;4;)/IFERROR(MID(LEFT(VLOOKUP(H8;$B$5:$E$13;3;);FIND("/Pcs";VLOOKUP(H8;$B$5:$E$13;3;))-1);FIND("/Pck";VLOOKUP(H8;$B$5:$E$13;3;))+4;50);LEFT(RIGHT(VLOOKUP(H8;$B$5:$E$13;3;);FIND("/Pcs";VLOOKUP(H8;$B$5:$E$13;3;)));FIND("/Pcs";RIGHT(VLOOKUP(H8;$B$5:$E$13;3;);FIND("/Pcs";VLOOKUP(H8;$B$5:$E$13;3;-1))*J8+VLOOKUP(H8;$B$5:$E$13;4;)/VLOOKUP(H8;$B$5:$E$13;2;)*K8
Semoga sesuai harapan.
Salam,
DD
From: Zulfikar N zulfikar_rout...@yahoo.com [belajar-excel]
[mailto:belajar-excel@yahoogroups.com]
Sent: 29 Mei 2015 09:57
To: belajar-excel@yahoogroups.com
Subject: [belajar-excel] Rumus Total Harga [1 Attachment]
Dear Master,
Mohon bantuannya untuk kasus saya terlampir, rencananya saya akan membuat tools
sederhana untuk melihat total harga barang jika saya input di kolom kuantiti
barang sesuai orderan pelanggan akan terakumulasi total harganya.
Untuk lebih jelasnya bisa di lihat di file terlampir.
Terima kasih,
salam
Zulfikar
Palopo, Sulsel
_
This e-mail and any files transmitted with it are confidential and intended
solely for the use of the individual to whom it is addressed. If you have
received this email in error please send it back to the person that sent it to
you. Any views or opinions presented are solely those of its author and do not
necessarily represent those of NABATI or any of its subsidiary companies.
Unauthorized publication, use, dissemination, forwarding, printing or copying
of this email and its associated attachments is strictly prohibited.
RE: [belajar-excel] Rumus Total Harga
Dear Mr Zul,
Coba formula ini di cell L8 :
=VLOOKUP(H8;$B$5:$E$13;4;)*I8+VLOOKUP(H8;$B$5:$E$13;4;)/IFERROR(IF(LEN(VLOOKUP(H8;$B$5:$E$13;3;))=9;LEFT(RIGHT(VLOOKUP(H8;$B$5:$E$13;3;);FIND("/";VLOOKUP(H8;$B$5:$E$13;3;)));FIND("/";RIGHT(VLOOKUP(H8;$B$5:$E$13;3;);FIND("/";VLOOKUP(H8;$B$5:$E$13;3;-1);MID(LEFT(VLOOKUP(B5;$B$5:$E$13;3;);FIND("/Pcs";VLOOKUP(B5;$B$5:$E$13;3;))-1);FIND("/Pck";VLOOKUP(B5;$B$5:$E$13;3;))+4;50));MID(LEFT(D5;FIND("/Pcs";D5)-1);FIND("/Rtg";D5)+4;50))*J8+VLOOKUP(H8;$B$5:$E$13;4;)/VLOOKUP(H8;$B$5:$E$13;2;)*K8
Dengan komposisi ke 5 jenis dibawah yah.
Semoga sesuai harapan.
Salam,
DD
From: Dede pmaho_l...@nabatisnack.co.id [belajar-excel]
[mailto:belajar-excel@yahoogroups.com]
Sent: 01 Juni 2015 13:38
To: belajar-excel@yahoogroups.com
Subject: RE: [belajar-excel] Rumus Total Harga
Dear Mr Zul,
Formula saya hanya bisa baca dengan komposisi yang mengandung karakter :
“/Pck” dan “Pcs” dalam satu cell, dan dengan komposisi “/Pcs” di akhir cell.
Mungkin agar lebih tepatnya ada berapa jenis komposisi pastinya ?
Contoh :
1. Dus30/Pck15/Pcs
2. Bal80/Pck20/Pcs
3. Dus30/Pcs
4. Ball30/Pck15/Pcs
5. Dus30/Rtg15/Pcs
6. ?
7. ?
8. ?
9. Dst...
Barulah akan ketahuan tepatnya formula untuk case tersebut.
Salam,
DD
From: Zulfikar N zulfikar_rout...@yahoo.com [belajar-excel]
[mailto:belajar-excel@yahoogroups.com]
Sent: 01 Juni 2015 09:44
To: belajar-excel@yahoogroups.com
Subject: Bls: [belajar-excel] Rumus Total Harga
Dear Master DD,
Sebelumnya di kolom Komposit Dus30/Pck15/Pcs, pertanyaan saya bagaimana jika
isi pada kolom komposit berisi Ball30/Pck15/Pcs, Dus30/Rtg15/Pcs, Dus30/Cup
dll. Mohon pencerahannya.
salam,
Zulfikar
Pada Jumat, 29 Mei 2015 14:49, "Dede pmaho_l...@nabatisnack.co.id
[belajar-excel]" menulis:
Dear Mr Zul,
Coba formula ini di cell L8 :
=VLOOKUP(H8;$B$5:$E$13;4;)*I8+VLOOKUP(H8;$B$5:$E$13;4;)/IFERROR(MID(LEFT(VLOOKUP(H8;$B$5:$E$13;3;);FIND("/Pcs";VLOOKUP(H8;$B$5:$E$13;3;))-1);FIND("/Pck";VLOOKUP(H8;$B$5:$E$13;3;))+4;50);LEFT(RIGHT(VLOOKUP(H8;$B$5:$E$13;3;);FIND("/Pcs";VLOOKUP(H8;$B$5:$E$13;3;)));FIND("/Pcs";RIGHT(VLOOKUP(H8;$B$5:$E$13;3;);FIND("/Pcs";VLOOKUP(H8;$B$5:$E$13;3;-1))*J8+VLOOKUP(H8;$B$5:$E$13;4;)/VLOOKUP(H8;$B$5:$E$13;2;)*K8
Semoga sesuai harapan.
Salam,
DD
From: Zulfikar N zulfikar_rout...@yahoo.com [belajar-excel]
[mailto:belajar-excel@yahoogroups.com]
Sent: 29 Mei 2015 09:57
To: belajar-excel@yahoogroups.com
Subject: [belajar-excel] Rumus Total Harga [1 Attachment]
Dear Master,
Mohon bantuannya untuk kasus saya terlampir, rencananya saya akan membuat tools
sederhana untuk melihat total harga barang jika saya input di kolom kuantiti
barang sesuai orderan pelanggan akan terakumulasi total harganya.
Untuk lebih jelasnya bisa di lihat di file terlampir.
Terima kasih,
salam
Zulfikar
Palopo, Sulsel
_
This e-mail and any files transmitted with it are confidential and intended
solely for the use of the individual to whom it is addressed. If you have
received this email in error please send it back to the person that sent it to
you. Any views or opinions presented are solely those of its author and do not
necessarily represent those of NABATI or any of its subsidiary companies.
Unauthorized publication, use, dissemination, forwarding, printing or copying
of this email and its associated attachments is strictly prohibited.
RE: [belajar-excel] Rumus Total Harga
Dear Mr Zul,
Opsi dari Mr Kid itu lebih simple loh.
Rada puyeng juga sih klo maksain dengan tabel yang itu, hehehehe.
Salam,
DD
From: 'Mr. Kid' mr.nm...@gmail.com [belajar-excel]
[mailto:belajar-excel@yahoogroups.com]
Sent: 01 Juni 2015 13:50
To: BeExcel
Subject: Re: [belajar-excel] Rumus Total Harga
Oh... contoh tabel referensi yang akan memudahkan yang ada di file lampiran
dalam trit ini :
https://groups.yahoo.com/neo/groups/belajar-excel/conversations/messages/36899
ndak cocok ya...
Sepertinya lebih mudah menyusun tabel demikian (tabel yang kiri bawah) dari
pada memaksakan akan menggunakan tabel referensi yang Anda miliki (yang kiri
atas).
btw,
itu semua sekedar opsi kok.
Wassalam,
Kid
On Mon, Jun 1, 2015 at 12:43 PM, Zulfikar N zulfikar_rout...@yahoo.com
[belajar-excel] wrote:
Dear Master DD,
Sebelumnya di kolom Komposit Dus30/Pck15/Pcs, pertanyaan saya bagaimana jika
isi pada kolom komposit berisi Ball30/Pck15/Pcs, Dus30/Rtg15/Pcs, Dus30/Cup
dll. Mohon pencerahannya.
salam,
Zulfikar
Pada Jumat, 29 Mei 2015 14:49, "Dede pmaho_l...@nabatisnack.co.id
[belajar-excel]" menulis:
Dear Mr Zul,
Coba formula ini di cell L8 :
=VLOOKUP(H8;$B$5:$E$13;4;)*I8+VLOOKUP(H8;$B$5:$E$13;4;)/IFERROR(MID(LEFT(VLOOKUP(H8;$B$5:$E$13;3;);FIND("/Pcs";VLOOKUP(H8;$B$5:$E$13;3;))-1);FIND("/Pck";VLOOKUP(H8;$B$5:$E$13;3;))+4;50);LEFT(RIGHT(VLOOKUP(H8;$B$5:$E$13;3;);FIND("/Pcs";VLOOKUP(H8;$B$5:$E$13;3;)));FIND("/Pcs";RIGHT(VLOOKUP(H8;$B$5:$E$13;3;);FIND("/Pcs";VLOOKUP(H8;$B$5:$E$13;3;-1))*J8+VLOOKUP(H8;$B$5:$E$13;4;)/VLOOKUP(H8;$B$5:$E$13;2;)*K8
Semoga sesuai harapan.
Salam,
DD
From: Zulfikar N zulfikar_rout...@yahoo.com [belajar-excel]
[mailto:belajar-excel@yahoogroups.com]
Sent: 29 Mei 2015 09:57
To: belajar-excel@yahoogroups.com
Subject: [belajar-excel] Rumus Total Harga [1 Attachment]
Dear Master,
Mohon bantuannya untuk kasus saya terlampir, rencananya saya akan membuat tools
sederhana untuk melihat total harga barang jika saya input di kolom kuantiti
barang sesuai orderan pelanggan akan terakumulasi total harganya.
Untuk lebih jelasnya bisa di lihat di file terlampir.
Terima kasih,
salam
Zulfikar
Palopo, Sulsel
_
This e-mail and any files transmitted with it are confidential and intended
solely for the use of the individual to whom it is addressed. If you have
received this email in error please send it back to the person that sent it to
you. Any views or opinions presented are solely those of its author and do not
necessarily represent those of NABATI or any of its subsidiary companies.
Unauthorized publication, use, dissemination, forwarding, printing or copying
of this email and its associated attachments is strictly prohibited.
Re: [belajar-excel] Rumus Total Harga
Oh... contoh tabel referensi yang akan memudahkan yang ada di file lampiran
dalam trit ini :
https://groups.yahoo.com/neo/groups/belajar-excel/conversations/messages/36899
ndak cocok ya...
Sepertinya lebih mudah menyusun tabel demikian (tabel yang kiri bawah) dari
pada memaksakan akan menggunakan tabel referensi yang Anda miliki (yang
kiri atas).
btw,
itu semua sekedar opsi kok.
Wassalam,
Kid
On Mon, Jun 1, 2015 at 12:43 PM, Zulfikar N zulfikar_rout...@yahoo.com
[belajar-excel] wrote:
>
>
> Dear Master DD,
> Sebelumnya di kolom Komposit Dus30/Pck15/Pcs, pertanyaan saya bagaimana
> jika isi pada kolom komposit berisi Ball30/Pck15/Pcs, Dus30/Rtg15/Pcs,
> Dus30/Cup dll. Mohon pencerahannya.
>
> salam,
> Zulfikar
>
>
>
>
> Pada Jumat, 29 Mei 2015 14:49, "Dede pmaho_l...@nabatisnack.co.id
> [belajar-excel]" menulis:
>
>
>
> Dear Mr Zul,
>
> Coba formula ini di cell L8 :
>
>
> =VLOOKUP(H8;$B$5:$E$13;4;)*I8+VLOOKUP(H8;$B$5:$E$13;4;)/IFERROR(MID(LEFT(VLOOKUP(H8;$B$5:$E$13;3;);FIND("/Pcs";VLOOKUP(H8;$B$5:$E$13;3;))-1);FIND("/Pck";VLOOKUP(H8;$B$5:$E$13;3;))+4;50);LEFT(RIGHT(VLOOKUP(H8;$B$5:$E$13;3;);FIND("/Pcs";VLOOKUP(H8;$B$5:$E$13;3;)));FIND("/Pcs";RIGHT(VLOOKUP(H8;$B$5:$E$13;3;);FIND("/Pcs";VLOOKUP(H8;$B$5:$E$13;3;-1))*J8+VLOOKUP(H8;$B$5:$E$13;4;)/VLOOKUP(H8;$B$5:$E$13;2;)*K8
>
> Semoga sesuai harapan.
>
> Salam,
> DD
>
> *From:* Zulfikar N zulfikar_rout...@yahoo.com [belajar-excel] [mailto:
> belajar-excel@yahoogroups.com]
> *Sent:* 29 Mei 2015 09:57
> *To:* belajar-excel@yahoogroups.com
> *Subject:* [belajar-excel] Rumus Total Harga [1 Attachment]
>
>
> Dear Master,
> Mohon bantuannya untuk kasus saya terlampir, rencananya saya akan
> membuat tools sederhana untuk melihat total harga barang jika saya input di
> kolom kuantiti barang sesuai orderan pelanggan akan terakumulasi total
> harganya.
>
> Untuk lebih jelasnya bisa di lihat di file terlampir.
> Terima kasih,
> salam
>
> Zulfikar
> Palopo, Sulsel
>
> --
> This e-mail and any files transmitted with it are confidential and
> intended solely for the use of the individual to whom it is addressed. If
> you have received this email in error please send it back to the person
> that sent it to you. Any views or opinions presented are solely those of
> its author and do not necessarily represent those of NABATI or any of its
> subsidiary companies. Unauthorized publication, use, dissemination,
> forwarding, printing or copying of this email and its associated
> attachments is strictly prohibited.
>
>
>
>
RE: [belajar-excel] Rumus Total Harga
Dear Mr Zul,
Formula saya hanya bisa baca dengan komposisi yang mengandung karakter :
“/Pck” dan “Pcs” dalam satu cell, dan dengan komposisi “/Pcs” di akhir cell.
Mungkin agar lebih tepatnya ada berapa jenis komposisi pastinya ?
Contoh :
1. Dus30/Pck15/Pcs
2. Bal80/Pck20/Pcs
3. Dus30/Pcs
4. Ball30/Pck15/Pcs
5. Dus30/Rtg15/Pcs
6. ?
7. ?
8. ?
9. Dst...
Barulah akan ketahuan tepatnya formula untuk case tersebut.
Salam,
DD
From: Zulfikar N zulfikar_rout...@yahoo.com [belajar-excel]
[mailto:belajar-excel@yahoogroups.com]
Sent: 01 Juni 2015 09:44
To: belajar-excel@yahoogroups.com
Subject: Bls: [belajar-excel] Rumus Total Harga
Dear Master DD,
Sebelumnya di kolom Komposit Dus30/Pck15/Pcs, pertanyaan saya bagaimana jika
isi pada kolom komposit berisi Ball30/Pck15/Pcs, Dus30/Rtg15/Pcs, Dus30/Cup
dll. Mohon pencerahannya.
salam,
Zulfikar
Pada Jumat, 29 Mei 2015 14:49, "Dede pmaho_l...@nabatisnack.co.id
[belajar-excel]" menulis:
Dear Mr Zul,
Coba formula ini di cell L8 :
=VLOOKUP(H8;$B$5:$E$13;4;)*I8+VLOOKUP(H8;$B$5:$E$13;4;)/IFERROR(MID(LEFT(VLOOKUP(H8;$B$5:$E$13;3;);FIND("/Pcs";VLOOKUP(H8;$B$5:$E$13;3;))-1);FIND("/Pck";VLOOKUP(H8;$B$5:$E$13;3;))+4;50);LEFT(RIGHT(VLOOKUP(H8;$B$5:$E$13;3;);FIND("/Pcs";VLOOKUP(H8;$B$5:$E$13;3;)));FIND("/Pcs";RIGHT(VLOOKUP(H8;$B$5:$E$13;3;);FIND("/Pcs";VLOOKUP(H8;$B$5:$E$13;3;-1))*J8+VLOOKUP(H8;$B$5:$E$13;4;)/VLOOKUP(H8;$B$5:$E$13;2;)*K8
Semoga sesuai harapan.
Salam,
DD
From: Zulfikar N zulfikar_rout...@yahoo.com [belajar-excel]
[mailto:belajar-excel@yahoogroups.com]
Sent: 29 Mei 2015 09:57
To: belajar-excel@yahoogroups.com
Subject: [belajar-excel] Rumus Total Harga [1 Attachment]
Dear Master,
Mohon bantuannya untuk kasus saya terlampir, rencananya saya akan membuat tools
sederhana untuk melihat total harga barang jika saya input di kolom kuantiti
barang sesuai orderan pelanggan akan terakumulasi total harganya.
Untuk lebih jelasnya bisa di lihat di file terlampir.
Terima kasih,
salam
Zulfikar
Palopo, Sulsel
_
This e-mail and any files transmitted with it are confidential and intended
solely for the use of the individual to whom it is addressed. If you have
received this email in error please send it back to the person that sent it to
you. Any views or opinions presented are solely those of its author and do not
necessarily represent those of NABATI or any of its subsidiary companies.
Unauthorized publication, use, dissemination, forwarding, printing or copying
of this email and its associated attachments is strictly prohibited.
Re: [belajar-excel] Rumus Total Harga [1 Attachment]
Hai Zulfikar, Dengan tabel daftar harga yang kurang sederhana buat Excel seperti demikian, maka array formulanya akan menjadi rumit. Andai bersedia menyusunkan tabel daftar harga yang sederhana buat Excel, maka formulanya akan lebih sederhana. File terlampir (mumpung boleh buka Excel) Wassalam, Kid 2015-05-29 12:57 GMT+10:00 Zulfikar N zulfikar_rout...@yahoo.com [belajar-excel] : > > > Dear Master, > Mohon bantuannya untuk kasus saya terlampir, rencananya saya akan membuat > tools sederhana untuk melihat total harga barang jika saya input di kolom > kuantiti barang sesuai orderan pelanggan akan terakumulasi total harganya. > > Untuk lebih jelasnya bisa di lihat di file terlampir. > Terima kasih, > salam > > Zulfikar > Palopo, Sulsel > > > re-Total Harga.xlsx Description: MS-Excel 2007 spreadsheet
RE: [belajar-excel] Rumus Total Harga
Dear Mr Zul,
Coba formula ini di cell L8 :
=VLOOKUP(H8;$B$5:$E$13;4;)*I8+VLOOKUP(H8;$B$5:$E$13;4;)/IFERROR(MID(LEFT(VLOOKUP(H8;$B$5:$E$13;3;);FIND("/Pcs";VLOOKUP(H8;$B$5:$E$13;3;))-1);FIND("/Pck";VLOOKUP(H8;$B$5:$E$13;3;))+4;50);LEFT(RIGHT(VLOOKUP(H8;$B$5:$E$13;3;);FIND("/Pcs";VLOOKUP(H8;$B$5:$E$13;3;)));FIND("/Pcs";RIGHT(VLOOKUP(H8;$B$5:$E$13;3;);FIND("/Pcs";VLOOKUP(H8;$B$5:$E$13;3;-1))*J8+VLOOKUP(H8;$B$5:$E$13;4;)/VLOOKUP(H8;$B$5:$E$13;2;)*K8
Semoga sesuai harapan.
Salam,
DD
From: Zulfikar N zulfikar_rout...@yahoo.com [belajar-excel]
[mailto:belajar-excel@yahoogroups.com]
Sent: 29 Mei 2015 09:57
To: belajar-excel@yahoogroups.com
Subject: [belajar-excel] Rumus Total Harga [1 Attachment]
Dear Master,
Mohon bantuannya untuk kasus saya terlampir, rencananya saya akan membuat tools
sederhana untuk melihat total harga barang jika saya input di kolom kuantiti
barang sesuai orderan pelanggan akan terakumulasi total harganya.
Untuk lebih jelasnya bisa di lihat di file terlampir.
Terima kasih,
salam
Zulfikar
Palopo, Sulsel
_
This e-mail and any files transmitted with it are confidential and intended
solely for the use of the individual to whom it is addressed. If you have
received this email in error please send it back to the person that sent it to
you. Any views or opinions presented are solely those of its author and do not
necessarily represent those of NABATI or any of its subsidiary companies.
Unauthorized publication, use, dissemination, forwarding, printing or copying
of this email and its associated attachments is strictly prohibited.
