Rumus sudah jalan. Terima kasih buat Pak Odong dan Pak Kid.
Untuk yang SUMPRODUCT, saya gak ikut-ikut deh...Takut pusing.
;)
Dari: "odong nando odongna...@yahoo.com [belajar-excel]"
<belajar-excel@yahoogroups.com>
Kepada: "belajar-excel@yahoogroups.com" <belajar-excel@yahoogroups.com>
Dikirim: Senin, 7 Desember 2015 18:16
Judul: Bls: [belajar-excel] Rumus gak jalan kombinasi IF dan OFFSET gak jalan.
dear bos kid,
kok yang pake sumproduct tidak sesuai jawaban dengan formula yang lain hasilnya
(saya blm coba yang SUM) ya.. ?
=INDEX ( TGL! $ M $ 2 : $ N $ 8 ; $ I $ 2 2 ; 2 - ( SUMPRODUCT ( INDEX (
TGL!$K$2:$L$8;$I$22;0 ) * { 1 ; - 1 } ) > 0 ) )
awalnya 0 yang saya pikir bermasalah trus saya ganti jadi 1, tp masih sama,
saya breakdown, malah jadi pusing, mohon pencerahannya... :) br,
odong nando
Pada Senin, 7 Desember 2015 15:32, "'Mr. Kid' mr.nm...@gmail.com
[belajar-excel]" <belajar-excel@yahoogroups.com> menulis:
hmmm... sudah banyak yang ikutan nimbrung, dan tampaknya kasus ini sudah
mendekati kelar. Tinggal nunggu file lampiran hasil uji coba mbak Nora deh...
Sambil menunggu, ikutan nimbrung ah...
Deskripsi kasus sudah lebih jelas dengan adanya konfirmasi dari mbak Nora.
Cells i22 pada sheet 'Surat' adalah nilai yang menunjukkan nomor record yang
akan diambil.
Sumber data di sheet 'Tgl' dengan header di baris 1.
Kolom-kolom di sheet 'Tgl' yang dibutuhkan adalah :
a. kolom K dan L sebagai penentu kondisi untuk mengambil suatu nilai di kolom
tertentu
b. kolom M dan N sebagai kolom data yang akan diambil
Kondisi alias kriteria pengambilan data :
1. kolom K > L maka ambil dari kolom M
2. selainnya, ambil dari kolom N
3. record yang dibandingkan dan diambil akan tergantung nilai i22
Jadi, sebagian besar proses akan terfokus pada sheet 'Tgl'
Formula yang sudah dibuat mbak Nora adalah formula yang memanfaatkan fungsi
Offset dalam suatu kondisi (di dalam IF) dengan bunyi :
=IF( tgl!k2>L2 , offset( tgl!$m$1 , $i$22 , 0 ) , offset( tgl!$n$1
, $i$22 , 0 ) )
Formula buatan mbak Nora dipecah-pecah menjadi beberapa part berikut :
1. offset( tgl!$m$1 , $i$22 , 0 )
> ini adalah ekspresi formula untuk mengambil dari kolom M sheet tgl di
nomor record i22
> part ini sudah berjalan dengan baik
> part ini dikerjakan kalau memenuhi kriteria K > L
2. offset( tgl!$n$1 , $i$22 , 0 )
> ini adalah ekspresi formula untuk mengambil dari kolom N sheet tgl di
nomor record i22
> part ini sudah berjalan dengan baik
> part ini dikerjakan kalau TIDAK memenuhi kriteria K > L
3. IF( kondisi , nilai kondisi TRUE , nilai kondisi FALSE )
> part nomor 1 ada di bagian nilai kondisi TRUE > part nomor 2 ada di
bagian nilai kondisi FALSE > yang jelas untuk kondisi berbunyi K > L
> jadi, penyusunan pengkondisian (penggunaan fungsi IF), telah meletakkan
dengan tepat
4. dari nomor 3, diketahui bahwa susunan IF di nomor 3 akan berjalan baik bila
menggunakan ekspresi kondisi berbunyi K > L
> bunyi kondisi yang dibuat mbak Nora adalah :
tgl!k2>L2 > yang sekilas tampak OK, tetapi sebenarnya rada
jauh bingitz dari OK, oleh sebab itu kemudian ditanyakan ke milis
> yang membuat kondisi buatan mbak Nora tidak OK adalah :
a. L2 tidak merujuk ke sheet 'Tgl' dengan baik (baik artinya tidak
sekedar asal merujuk saja loh ya)
b. tgl!k2 sudah merujuk ke sheet 'Tgl', tetapi kurang baik
c. faktor kebutuhan akan i22 tidak disertakan di dalam kondisi di atas,
padahal part pengambil nilainya (yang sudah baik) membutuhkan kehadiran i22
> jadi, sederhananya, kondisinya juga disusun dalam formula Offset juga.
Kira-kira bunyinya begini :
# sisi tgl!K2
offset( tgl!$k$1 , $i$22 , 0 )
# sisi L2
offset( tgl!$L$1 , $i$22 , 0 )
# bunyi tgl!K2>L2
offset( tgl!$k$1 , $i$22 , 0 ) > offset( tgl!$L$1 , $i$22 , 0
)
> dan kondisi yang bunyinya baru ini, akan sesuai dengan nomor record
terpilih dengan kehadiran i22 di dalam fungsi Offset
5. Dari part kondisi yang telah diperbaiki di nomor 4, maka formula utuh buatan
mbak Nora akan berubah menjadi keisengan Kid dengan bunyi begini :
=IF( offset( tgl!$k$1 , $i$22 , 0 ) > offset( tgl!$L$1 , $i$22 ,
0 ) , offset( tgl!$m$1 , $i$22 , 0 ) , offset( tgl!$n$1 , $i$22 , 0 ) )
nah... tampaknya, penggunaan fungsi Offset ini hanya di sebuah cells saja,
sehingga kalkulasi Excel tidak dikuatirkan akan terasa berat akibat kehadiran
fungsi Offset yang bengal bersifat volatile.
waktu berlalu, kemudian muncullah ide penggunaan Index.
Formula yang padat Offset di atas (yang formula isengnya Kid loh ya), bisa
diubah supaya pakai Index. Bunyinya begini : (misal data di baris 2 sampai 8)
1. part offset( tgl!$m$1 , $i$22 , 0 ) Index(
tgl!$m$2:$m$8 , $i$22 )
2. part offset( tgl!$n$1 , $i$22 , 0 )
Index( tgl!$n$2:$n$8 , $i$22 )
3. part kondisi sisi K : offset( tgl!$k$1 , $i$22 , 0 ) Index(
tgl!$k$2:$k$8 , $i$22 )
4. part kondisi sisi L : offset( tgl!$L$1 , $i$22 , 0 ) Index(
tgl!$L$2:$L$8 , $i$22 )
lalu dikumpulkan menjadi satu mengikuti struktur formula IF yang ada :
=IF( Index( tgl!$k$2:$k$8 , $i$22 ) > Index( tgl!$L$2:$L$8 ,
$i$22 ) , Index( tgl!$m$2:$m$8 , $i$22 ) , Index( tgl!$n$2:$n$8 , $i$22
) )
Dah... selesai deh...
Waktu yang terus berjalan, mengkabarkan bahwa pola pengambilan kolom M dan N
berupa :
a. andaikan saja di M disebut oknum 1 dan si N disebut oknum 2
b. proses perbandingan di Excel menghasilkan TRUE (setara 1) atau FALSE
(setara 0)
c. hasil K > L bernilai TRUE berarti K <= L akan bernilai FALSE dan akan
ambil oknum 1
d. hasil K > L bernilai FALSE berarti K <= L akan bernilai TRUE dan akan
ambil oknum 2
e. maka oknum 2 akan diambil jika K <= L atau jika pakai IF akan berbunyi :
IF( K<=L , oknum 2 , oknum 1 ) atau IF( K<=L , ambil N , ambil M
)
yang bisa disederhanakan menjadi :
( K <= L ) + 1
yang kalau di tes, ketika :
K<=L adalah TRUE alias setara bernilai 1, maka akan ditambah 1 dan
menjadi TRUE + 1 atau 1 + 1 yang menghasilkan 2 alias oknum 2 alias nilai N
K<=L adalah FALSE alias setara bernilai 0, maka akan ditambah 1 dan
menjadi FALSE + 1 atau 0 + 1 yang menghasilkan 1 alias oknum 1 alias nilai M
f. part pengambil nilai kolom M dan N yang tadinya pakai 2 formula Index
berikut :
Index( tgl!$m$2:$m$8 , $i$22 )
dan Index( tgl!$n$2:$n$8 , $i$22 )
disatukan menjadi Index( tgl!$m$2:$n$8 , $i$22 , ( K <= L ) + 1 )
yang part ( K <= L ) + 1 nya berbunyi :
( Index( tgl!$k$2:$k$8 , $i$22 ) <= Index( tgl!$L$2:$L$8 , $i$22 ) )
+ 1
g. yang akhirnya, susunan lengkap si Index akan berubah menjadi :
Index( tgl!$m$2:$n$8 , $i$22 , ( Index( tgl!$k$2:$k$8 , $i$22 )
<= Index( tgl!$L$2:$L$8 , $i$22 ) ) + 1 )
yang disusun oleh mas Odong dengan logat Indonesian nya menjadi :
=index(_range array yang akan di ambil nilainya_ ; _nilai refrensi_
; ( index ( _range pembanding 1_ ; _nilai refrensi_ ) < = ( index ( _range
pembanding 2_ ; _nilai refrensi_ ) ) + 1)
Kalau usulannya mas Odong akan dipertahankan dengan kondisi K>L, jadinya :
(pura-puranya Kid ikut urun solusi... wakakakaka...)
=index(_range array yang akan di ambil nilainya_ ; _nilai refrensi_
; 2- ( index ( _range pembanding 1_ ; _nilai refrensi_ ) > ( index ( _range
pembanding 2_ ; _nilai refrensi_ ) ))
yang bunyi lengkapnya : (wkwkwkwk.... akhirnya Kid bisa seakan-akan ikut urun
solusi juga deh....)
=Index( tgl!$m$2:$n$8 , $i$22 , 2- ( Index( tgl!$k$2:$k$8 , $i$22
) > Index( tgl!$L$2:$L$8 , $i$22 ) ) )
buat yang sedang cari alasan untuk bisa minum obat pusing, formula terakhir di
atas bisa juga dipendekin dikit menjadi :
=Index( tgl!$m$2:$n$8 , $i$22 , 2- ( SumProduct( index(
tgl!$k$2:$l$8 , $i$22 , 0 ) * {1,-1} )>0 ) )
Kalau mau pakai array formula (alias nulis formulanya terus diakhir pencet CTRL
SHIFT ENTER berbarengan), formulanya bisa begini :
=Index( tgl!$m$2:$n$8 , $i$22 , 2- ( Sum( index( tgl!$k$2:$l$8 ,
$i$22 , 0 ) * {1,-1} )>0 ) )
Tujuan dari formula yang terakhir ini, sekedar mengungkit kemampuan si SUM yang
bisa menghitung net (selisih) suatu nilai-nilai data.
17 - 10 = 7 bisa diformulasikan dengan :
=17 - 10
atau iseng :
=Sum( 17 ) - Sum( 10 )
atau diisengin lagi :
=Sum( 17 , -10 )
atau masih pengen iseng lagi :
=Sum( { 17 , 10 } * { 1 , -1 } ) tapi sebagai array formula
atau karena ogah sama array formula, maka pakai SumProduct
=SumProduct( { 17 , 10 } * { 1 , -1 } )
btw, direkomendasikan, untuk tidak sampai kesenengen iseng
;)
udah ah... capek..
kayanya dah cukup isengnya..
Regards,
Kid
On Mon, Dec 7, 2015 at 5:16 AM, Nora Ismanti noraisma...@yahoo.com
[belajar-excel] <belajar-excel@yahoogroups.com> wrote:
Terima kasih buat para Master dan segenap anggota milis. Seiring dengan
terbitnya matahari pagi ini nampaknya mulai nampak cahaya terhadap persoalan
yang Nora hadapi - setelah membaca pemetaan masalah yang disampaikan oleh
Master Kid. Saya ingin jawab pemetaan masalah oleh Pak Kid:
Pak Kid menulis:
Bunyi kriteria saat i22 bernilai 1 : (digambar baris Aturannya blabla)
1. K2 > L2 -> ambil M2
2. K2 < L2 -> ambil N2
3. K2 = L2 -> tidak tahu (terserah yang urun sajah)
4. begitu seterusnya, yang diartikan kalau i22 jadi bernilai 2, maka merujuk
ke baris 3
Jawaban Nora:
1. K2 > L2 -> ambil M2 = Betul2. K2 < L2 -> ambil N2
= Betul3. K2 = L2 -> tidak tahu (terserah yang urun sajah) = Ambil N2,
maaf lupa. Makasih ya Pak Kid udah diingatkan
4. begitu seterusnya, yang diartikan kalau i22 jadi bernilai 2, maka merujuk ke
baris 3 = Betul banget. Justru inilah yang penting.
= kalau i22 jadi bernilai 2, maka merujuk ke baris 3,kalau i22 jadi bernilai
3, maka merujuk ke baris 4, kalau i22 jadi bernilai 4, maka merujuk ke baris 5.
= Begitu seterusnya, sampai baris berapapun aturannya seperti ini.
Terima kasih banyak.
Dari: "'Mr. Kid' mr.nm...@gmail.com [belajar-excel]"
<belajar-excel@yahoogroups.com>
Kepada: BeExcel <belajar-excel@yahoogroups.com>
Dikirim: Minggu, 6 Desember 2015 21:46
Judul: Re: [belajar-excel] Rumus gak jalan kombinasi IF dan OFFSET gak jalan.
Hai Nora,
boleh ikutan ya...
Bunyi formula yang sudah dibuat : (digambar baris =IF blabla)
saat i22 bernilai 1, ambil data dari sheet tgl, mulai M1 atau N1 lompat senilai
i22 tergantung kondisi IF nilai K2 dengan L2 di sheet tgl.
Bunyi kriteria saat i22 bernilai 1 : (digambar baris Aturannya blabla)
1. K2 > L2 -> ambil M2
2. K2 < L2 -> ambil N2
3. K2 = L2 -> tidak tahu (terserah yang urun sajah)
4. begitu seterusnya, yang diartikan kalau i22 jadi bernilai 2, maka merujuk
ke baris 3
gitu kali ya...
Silahkan yang bersemangat berbagi maupu belajar...
;)
Kid
2015-12-06 9:17 GMT+07:00 Nora Ismanti noraisma...@yahoo.com [belajar-excel]
<belajar-excel@yahoogroups.com>:
Mohon bantuan dari para Master dan segenap anggota milis. Saya coba
menggabungkan IF dgn OFFSET tetapi tidak jalan. Selengkapnya dapat dilihat pada
lampiran.
Terima kasih
#yiv2691025331 #yiv2691025331 -- #yiv2691025331ygrp-mkp {border:1px solid
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv2691025331
#yiv2691025331ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv2691025331
#yiv2691025331ygrp-mkp #yiv2691025331hd
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}#yiv2691025331 #yiv2691025331ygrp-mkp #yiv2691025331ads
{margin-bottom:10px;}#yiv2691025331 #yiv2691025331ygrp-mkp .yiv2691025331ad
{padding:0 0;}#yiv2691025331 #yiv2691025331ygrp-mkp .yiv2691025331ad p
{margin:0;}#yiv2691025331 #yiv2691025331ygrp-mkp .yiv2691025331ad a
{color:#0000ff;text-decoration:none;}#yiv2691025331 #yiv2691025331ygrp-sponsor
#yiv2691025331ygrp-lc {font-family:Arial;}#yiv2691025331
#yiv2691025331ygrp-sponsor #yiv2691025331ygrp-lc #yiv2691025331hd {margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv2691025331
#yiv2691025331ygrp-sponsor #yiv2691025331ygrp-lc .yiv2691025331ad
{margin-bottom:10px;padding:0 0;}#yiv2691025331 #yiv2691025331actions
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv2691025331
#yiv2691025331activity
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv2691025331
#yiv2691025331activity span {font-weight:700;}#yiv2691025331
#yiv2691025331activity span:first-child
{text-transform:uppercase;}#yiv2691025331 #yiv2691025331activity span a
{color:#5085b6;text-decoration:none;}#yiv2691025331 #yiv2691025331activity span
span {color:#ff7900;}#yiv2691025331 #yiv2691025331activity span
.yiv2691025331underline {text-decoration:underline;}#yiv2691025331
.yiv2691025331attach
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
0;width:400px;}#yiv2691025331 .yiv2691025331attach div a
{text-decoration:none;}#yiv2691025331 .yiv2691025331attach img
{border:none;padding-right:5px;}#yiv2691025331 .yiv2691025331attach label
{display:block;margin-bottom:5px;}#yiv2691025331 .yiv2691025331attach label a
{text-decoration:none;}#yiv2691025331 blockquote {margin:0 0 0
4px;}#yiv2691025331 .yiv2691025331bold
{font-family:Arial;font-size:13px;font-weight:700;}#yiv2691025331
.yiv2691025331bold a {text-decoration:none;}#yiv2691025331 dd.yiv2691025331last
p a {font-family:Verdana;font-weight:700;}#yiv2691025331 dd.yiv2691025331last p
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv2691025331
dd.yiv2691025331last p span.yiv2691025331yshortcuts
{margin-right:0;}#yiv2691025331 div.yiv2691025331attach-table div div a
{text-decoration:none;}#yiv2691025331 div.yiv2691025331attach-table
{width:400px;}#yiv2691025331 div.yiv2691025331file-title a, #yiv2691025331
div.yiv2691025331file-title a:active, #yiv2691025331
div.yiv2691025331file-title a:hover, #yiv2691025331 div.yiv2691025331file-title
a:visited {text-decoration:none;}#yiv2691025331 div.yiv2691025331photo-title a,
#yiv2691025331 div.yiv2691025331photo-title a:active, #yiv2691025331
div.yiv2691025331photo-title a:hover, #yiv2691025331
div.yiv2691025331photo-title a:visited {text-decoration:none;}#yiv2691025331
div#yiv2691025331ygrp-mlmsg #yiv2691025331ygrp-msg p a
span.yiv2691025331yshortcuts
{font-family:Verdana;font-size:10px;font-weight:normal;}#yiv2691025331
.yiv2691025331green {color:#628c2a;}#yiv2691025331 .yiv2691025331MsoNormal
{margin:0 0 0 0;}#yiv2691025331 o {font-size:0;}#yiv2691025331
#yiv2691025331photos div {float:left;width:72px;}#yiv2691025331
#yiv2691025331photos div div {border:1px solid
#666666;height:62px;overflow:hidden;width:62px;}#yiv2691025331
#yiv2691025331photos div label
{color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv2691025331
#yiv2691025331reco-category {font-size:77%;}#yiv2691025331
#yiv2691025331reco-desc {font-size:77%;}#yiv2691025331 .yiv2691025331replbq
{margin:4px;}#yiv2691025331 #yiv2691025331ygrp-actbar div a:first-child
{margin-right:2px;padding-right:5px;}#yiv2691025331 #yiv2691025331ygrp-mlmsg
{font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv2691025331
#yiv2691025331ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv2691025331
#yiv2691025331ygrp-mlmsg select, #yiv2691025331 input, #yiv2691025331 textarea
{font:99% Arial, Helvetica, clean, sans-serif;}#yiv2691025331
#yiv2691025331ygrp-mlmsg pre, #yiv2691025331 code {font:115%
monospace;}#yiv2691025331 #yiv2691025331ygrp-mlmsg *
{line-height:1.22em;}#yiv2691025331 #yiv2691025331ygrp-mlmsg #yiv2691025331logo
{padding-bottom:10px;}#yiv2691025331 #yiv2691025331ygrp-msg p a
{font-family:Verdana;}#yiv2691025331 #yiv2691025331ygrp-msg
p#yiv2691025331attach-count span {color:#1E66AE;font-weight:700;}#yiv2691025331
#yiv2691025331ygrp-reco #yiv2691025331reco-head
{color:#ff7900;font-weight:700;}#yiv2691025331 #yiv2691025331ygrp-reco
{margin-bottom:20px;padding:0px;}#yiv2691025331 #yiv2691025331ygrp-sponsor
#yiv2691025331ov li a {font-size:130%;text-decoration:none;}#yiv2691025331
#yiv2691025331ygrp-sponsor #yiv2691025331ov li
{font-size:77%;list-style-type:square;padding:6px 0;}#yiv2691025331
#yiv2691025331ygrp-sponsor #yiv2691025331ov ul {margin:0;padding:0 0 0
8px;}#yiv2691025331 #yiv2691025331ygrp-text
{font-family:Georgia;}#yiv2691025331 #yiv2691025331ygrp-text p {margin:0 0 1em
0;}#yiv2691025331 #yiv2691025331ygrp-text tt {font-size:120%;}#yiv2691025331
#yiv2691025331ygrp-vital ul li:last-child {border-right:none
!important;}#yiv2691025331
