yoo abdul ritno,
silakan di pelajari coretan mr. kid, tentang filtering formula.
namun untuk kasus anda bisa lebih di perhatikan hal-hal dibawah ini.
coba isi formula :
=INDEX ( DATA ! $ B $ 7 : $ B $ 1 0 00 ; M A T C H ( 0 ; I NDEX( COUNTIF (
DENAH ! $ G3 : G3 ; DATA ! $ B$7 : $ B $ 1000) + ( D ATA !$ A$7 : $A $1000 < >
D ENAH ! $ G3) ; 0 )
ubah karakter titik koma " ; " menjadi koma " , " bila regional seting komp
english
pada cell H3, (rak sebelah kanan)untuk rak sebelah kiri, daerah yang terarsir
kuning bisa di sesuaikan.
agar rak yang tidak terisi, akan blank, maka diperlukan fungsi IF dengan
logical test diisi dengan ISERROR "=IF(ISERROR(value);true;false)", value diisi
dengan formula yang ada di atas.
untuk jumlah yang ada di bawahnya (qty) dapat menggunakan fungsi sumifs, namun
bila office setempat 2007 kebawah maka dapat menggunakan fungsi sumproduct
:=SUMPRODUCT ( ( DATA!$A$7:$A$1000 = DENAH!$G3 ) * ( DATA! $B$7 : $B$1000 =
DENAH! H 3 ) * DATA! $C$7 : $C$1000 )
agar hasil sumproduct / sumifs bila tidak memiliki nilai alisa 0,00 menjadi
blank maka atur custom format cell menjadi " #,00;-#,00; " tanpa tandapetik br,
odong nando
Pada Rabu, 25 November 2015 8:33, "ab readnow [email protected]
[belajar-excel]" <[email protected]> menulis:
Dear Suhu Excel,
Saya baru gabung milis ini karena cukup pusing harus menggunakan rumus apa
untuk pengisian rak otomatis berdasarkan data yg tersedia. Ketentuannya sebagai
berikut:- Satu rak berisi maksimal 4 item & 4 Qty
- Rak kosong tetap terlihat kosong (tanpa tulisan 0)- Pengisian lebih 1 item di
rak sebelah Kanan adalah arah kiri ke kanan- Pengisian lebih 1 item di rak
sebelah Kiri adalah arah kanan ke kiri
keterangan lebih lengkap ada di lampiran.
Terima kasihAbdul Ritno
#yiv9710537766 -- #yiv9710537766ygrp-mkp {border:1px solid
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv9710537766
#yiv9710537766ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv9710537766
#yiv9710537766ygrp-mkp #yiv9710537766hd
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}#yiv9710537766 #yiv9710537766ygrp-mkp #yiv9710537766ads
{margin-bottom:10px;}#yiv9710537766 #yiv9710537766ygrp-mkp .yiv9710537766ad
{padding:0 0;}#yiv9710537766 #yiv9710537766ygrp-mkp .yiv9710537766ad p
{margin:0;}#yiv9710537766 #yiv9710537766ygrp-mkp .yiv9710537766ad a
{color:#0000ff;text-decoration:none;}#yiv9710537766 #yiv9710537766ygrp-sponsor
#yiv9710537766ygrp-lc {font-family:Arial;}#yiv9710537766
#yiv9710537766ygrp-sponsor #yiv9710537766ygrp-lc #yiv9710537766hd {margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv9710537766
#yiv9710537766ygrp-sponsor #yiv9710537766ygrp-lc .yiv9710537766ad
{margin-bottom:10px;padding:0 0;}#yiv9710537766 #yiv9710537766actions
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv9710537766
#yiv9710537766activity
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv9710537766
#yiv9710537766activity span {font-weight:700;}#yiv9710537766
#yiv9710537766activity span:first-child
{text-transform:uppercase;}#yiv9710537766 #yiv9710537766activity span a
{color:#5085b6;text-decoration:none;}#yiv9710537766 #yiv9710537766activity span
span {color:#ff7900;}#yiv9710537766 #yiv9710537766activity span
.yiv9710537766underline {text-decoration:underline;}#yiv9710537766
.yiv9710537766attach
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
0;width:400px;}#yiv9710537766 .yiv9710537766attach div a
{text-decoration:none;}#yiv9710537766 .yiv9710537766attach img
{border:none;padding-right:5px;}#yiv9710537766 .yiv9710537766attach label
{display:block;margin-bottom:5px;}#yiv9710537766 .yiv9710537766attach label a
{text-decoration:none;}#yiv9710537766 blockquote {margin:0 0 0
4px;}#yiv9710537766 .yiv9710537766bold
{font-family:Arial;font-size:13px;font-weight:700;}#yiv9710537766
.yiv9710537766bold a {text-decoration:none;}#yiv9710537766 dd.yiv9710537766last
p a {font-family:Verdana;font-weight:700;}#yiv9710537766 dd.yiv9710537766last p
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv9710537766
dd.yiv9710537766last p span.yiv9710537766yshortcuts
{margin-right:0;}#yiv9710537766 div.yiv9710537766attach-table div div a
{text-decoration:none;}#yiv9710537766 div.yiv9710537766attach-table
{width:400px;}#yiv9710537766 div.yiv9710537766file-title a, #yiv9710537766
div.yiv9710537766file-title a:active, #yiv9710537766
div.yiv9710537766file-title a:hover, #yiv9710537766 div.yiv9710537766file-title
a:visited {text-decoration:none;}#yiv9710537766 div.yiv9710537766photo-title a,
#yiv9710537766 div.yiv9710537766photo-title a:active, #yiv9710537766
div.yiv9710537766photo-title a:hover, #yiv9710537766
div.yiv9710537766photo-title a:visited {text-decoration:none;}#yiv9710537766
div#yiv9710537766ygrp-mlmsg #yiv9710537766ygrp-msg p a
span.yiv9710537766yshortcuts
{font-family:Verdana;font-size:10px;font-weight:normal;}#yiv9710537766
.yiv9710537766green {color:#628c2a;}#yiv9710537766 .yiv9710537766MsoNormal
{margin:0 0 0 0;}#yiv9710537766 o {font-size:0;}#yiv9710537766
#yiv9710537766photos div {float:left;width:72px;}#yiv9710537766
#yiv9710537766photos div div {border:1px solid
#666666;height:62px;overflow:hidden;width:62px;}#yiv9710537766
#yiv9710537766photos div label
{color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv9710537766
#yiv9710537766reco-category {font-size:77%;}#yiv9710537766
#yiv9710537766reco-desc {font-size:77%;}#yiv9710537766 .yiv9710537766replbq
{margin:4px;}#yiv9710537766 #yiv9710537766ygrp-actbar div a:first-child
{margin-right:2px;padding-right:5px;}#yiv9710537766 #yiv9710537766ygrp-mlmsg
{font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv9710537766
#yiv9710537766ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv9710537766
#yiv9710537766ygrp-mlmsg select, #yiv9710537766 input, #yiv9710537766 textarea
{font:99% Arial, Helvetica, clean, sans-serif;}#yiv9710537766
#yiv9710537766ygrp-mlmsg pre, #yiv9710537766 code {font:115%
monospace;}#yiv9710537766 #yiv9710537766ygrp-mlmsg *
{line-height:1.22em;}#yiv9710537766 #yiv9710537766ygrp-mlmsg #yiv9710537766logo
{padding-bottom:10px;}#yiv9710537766 #yiv9710537766ygrp-msg p a
{font-family:Verdana;}#yiv9710537766 #yiv9710537766ygrp-msg
p#yiv9710537766attach-count span {color:#1E66AE;font-weight:700;}#yiv9710537766
#yiv9710537766ygrp-reco #yiv9710537766reco-head
{color:#ff7900;font-weight:700;}#yiv9710537766 #yiv9710537766ygrp-reco
{margin-bottom:20px;padding:0px;}#yiv9710537766 #yiv9710537766ygrp-sponsor
#yiv9710537766ov li a {font-size:130%;text-decoration:none;}#yiv9710537766
#yiv9710537766ygrp-sponsor #yiv9710537766ov li
{font-size:77%;list-style-type:square;padding:6px 0;}#yiv9710537766
#yiv9710537766ygrp-sponsor #yiv9710537766ov ul {margin:0;padding:0 0 0
8px;}#yiv9710537766 #yiv9710537766ygrp-text
{font-family:Georgia;}#yiv9710537766 #yiv9710537766ygrp-text p {margin:0 0 1em
0;}#yiv9710537766 #yiv9710537766ygrp-text tt {font-size:120%;}#yiv9710537766
#yiv9710537766ygrp-vital ul li:last-child {border-right:none
!important;}#yiv9710537766
