alexa-tracking

[Ask][Excel] Kombinasi Rumus Excel yang tepat?

Main Content

1024
1024
KASKUS
51
244
https://www.kaskus.co.id/thread/53d8ebcfdc06bdce3c8b4868/askexcel-kombinasi-rumus-excel-yang-tepat
[Ask][Excel] Kombinasi Rumus Excel yang tepat?
Sebelumya, mohon maaf kalo ane salah kamar, silahkan momod/mimin pindahkan ke tempat yang seharusnya.

Salam kenal, salam jabat erat bagi para suhu, master di trit ini.
Ane perlu bantuan para Suhu-master, langsung aja ya:

contoh soal:
Ada 4 baris, A1, A2, A3, A4,
dengan kolom input : D1, D2, D3, D4; yg akan diisi angka dari 0-10.
A1, A2, A3, A4 berisikan data :A1=Jeruk, A2=Apel, A3=Nanas, A4=Pepaya, yg harus muncul pada
tempat lain, misal: di C1 s/d C20.

jika
-A1=1, A2=0, A3=0, A4=0, maka hanya data "Jeruk" yg muncul di C1 (C2-C20=blank)
-A1=1, A2=1, A3=0, A4=0, maka C1=jeruk, C2=Apel, C3-C20= blank
-A1=1, A2=3, A3=2, A4=1, maka C1=Jeruk, C2-C3-C4=Apel, C5-c6=Nanas, C7=Pepaya, C8-C20=blank

-dan berbagai varian lainnya, yang intinya, kolom output C1-c20 hanya akan terisi sesuai
dengan nilai input di kolom D1,D2,D3,D4.

-Ane gunakan IF function, cuma ane baru mampu hanya untuk 2 jenis data input (misal : Jeruk & Apel doang), dengan variasi nilai input : 1-10

-Ane mentok pada input ke-3 dan ke-4... pusing banget ama IF yg banyak banget..

Semoga ada master Excel yang bisa bantu ane..
*ini SS nya gan..
Salam
[Ask][Excel] Kombinasi Rumus Excel yang tepat?

Matur thankyu sebelumnya..
jangan ngejunk pliss
Spoiler for ts's post:

mendingan pake vba (visual basic for application) in excel gan. gimana cara pakenya silahkan googling dulu. vba punya ane gk bisa karna gk ane instal.
berikut ane kasih contoh pseudo-code-nya, silahkan dicoba dan diutak-atik karena blom ane test.
Spoiler for pseudo-code:


ingat gan, itu masih pseudo-code. kalo eror silahkan dicek apakah sintak-nya udah bener apa blom
KASKUS Ads
Sebelumnya.. emoticon-Shakehand2 agan Zackad
thanks sudah kasih respon, VBA lagi.. ane belum banyak tau VBA.. emoticon-Sorry

Tapi ane akan coba gan, itung2 nambah tau..


Spoiler for pseudo-code:


Quote:

Yaaaaahh.... emoticon-Sorry emoticon-Sorry

[Ask][Excel] Kombinasi Rumus Excel yang tepat?

  • Buat dulu kolom bantu yaitu pada kolom E
  • Cell E1 dikasih angka 0
  • Rumus di E2 :
    Code:
    =SUM(D$1emoticon-Big Grin1)

    dan dicopykan sampai ke E4
  • Rumus di C8 :
    Code:
    =IF((ROW()-ROW(A$8))>=SUM(D$1emoticon-Big Grin$4), "", LOOKUP((ROW()-ROW(A$8)),E$1:E$4,C$1:C$4) )

    dan dicopykan ke bawah sampai C27 (sebanyak 20 baris)


........emoticon-Ngacir emoticon-Ngacir
Quote:


wauh.... emoticon-Matabelo emoticon-Shakehand2
ternyata, sederhana sekali ya, hanya dengan 1 IF,
Sangat membantu..
Matur thank you gan karedog.

-problem solved-

mangtabs...
cek kulkasnye gan..
Quote:

Sama-mama gan emoticon-Shakehand2 emoticon-Shakehand2


Quote:

Setelah dicek, kulkas ane masih dingin seperti biasanya gan emoticon-Blue Guy Smile (S) emoticon-Blue Guy Smile (S)
Eh, tapi keknya rep. point deh yang dikamsud emoticon-Thinking emoticon-Thinking
Oh iya gan, ternyata ada kiriman cendol, thanks yach, buat buka puasa nanti emoticon-Shakehand2 emoticon-Shakehand2
( tapi kan sekarang sudah gak puasa emoticon-Thinking emoticon-Thinking auk ah gelap emoticon-Cool emoticon-Cool )


Mengenai penjelasan yang agan minta, bisa ane jelaskeun sbb :

Inti dari pemecahan masalah agan adalah sebenarnya menggunakan rumus lookup()

Syntax nya adalah :
Code:
=lookup(nilai_cari, range_pencarian, range_hasil)


Hasil dari perhitungan dari fungsi ini adalah batas bawah terdekat dari nilai_cari

Spoiler for Penjelasan lookup:



Sekarang masuk ke permasalahan, seperti dijelaskan pada point 2 pada kesimpulan, range_pencarian haruslah merupakan data yang menerus, bukan data yang terputus-putus. Sebagai contoh jika agan punya 2 buah jeruk, 3 buah apel, 4 buah nanas; jika data terputus, maka agan akan menyebutnya dengan buah jeruk nomor 1, buah jeruk nomor 2, buah apel nomor 1, buah apel nomor 2, buah apel nomor 3, buah nanas nomor 1 s/d 4. Data seperti ini tidak bisa dipakai oleh fungsi lookup() (dan juga fungsi pencarian Excel lainnya), jadi harus diubah menjadi data yang menerus. Anggaplah agan punya meja yang sangat panjang, lalu agan menjajarkan buah itu satu persatu. Setelah dijajarkan, maka buah-buah itu bisa dipanggil berdasarkan nomor urutnya (identitas nama buahnya sudah hilang). Sesuai contoh diatas setelah dijajarkan maka menjadi (1) jeruk, (2) jeruk, (3) apel, (4) apel, (5) apel, (6) nanas, (7) nanas, (8) nanas, (9) nanas, sekarang data nya sudah menjadi data menerus dan bisa digunakan oleh fungsi lookup.

Untuk keperluan menetapkan data menerus inilah maka kolom bantu E dibuat. Penomoran dimulai dari angka 0 (bisa juga dari angka 1 atau angka lainnya, tapi itu akan menyulitkan dalam rumus Excel nya).

Pada kasus agan, setelah dijajarkan, maka angkanya menjadi (0) jeruk, (1) jeruk, (2) apel, 3 (nanas), tapi yang dibutuhkan hanya nilai batas bawah saja yaitu (0) jeruk, (2) apel, (3) nanas, ini dicari dengan rumus sum() seperti pada posting sebelumnya. Jadi kolom E sebetulnya adalah kolom bantu untuk menghitung nilai batas bawah dari masing-masing item setelah dijajarkan.


Rumus =ROW()-ROW(A$8) adalah untuk mendapatkan nomor item yang sedang dicari. Untuk mempermudah penjelasan, sekarang gambar untuk kasus agan dibuat seperti ini :

[Ask][Excel] Kombinasi Rumus Excel yang tepat?

Rumus di A8 adalah :
Code:
=ROW()-ROW(A$8)
dan dicopykan sampai A27

Kelihatan kan bahwa ini adalah nomor item yang agan tetapkan (agan menetapkan ada 20 item, dengan dimulai dari angka 0 maka angka terakhirnya adalah angka 19).

Nah sekarang tinggal masukkan saja nilai pada kolom A ini pada rumus lookup yang sudah dijelaskan tadi di atas.

Rumus di B8 adalah :
Code:
=LOOKUP(A8,E$1:E$4,C$1:C$4)
dan dicopykan sampai B27

dimana A8 adalah nilai_pencarian, E$1:E$4 adalah range_pencarian (hasil dari perhitungan membuat data menerus di atas), dan C$1:C$4 adalah range_hasil


Sepintas rumus ini untuk item-item awal sudah benar, tapi untuk item akhir (pepaya dalam contoh ini) yang harusnya tidak muncul malah muncul. Penyebabnya adalah karena fungsi lookup selalu hanya melihat ke kiri (ke arah batas bawah), tidak pernah mengecek ke kanan (batas atas), akibatnya jika nilai_pencarian sudah melewati jumlah data, maka akan “kelebihan”. Karena itulah digunakan rumus if() untuk menjaga batas atas dari nilai_pencarian

Rumus di C8 :
Code:
=IF(A8>=SUM(D$1emoticon-Big Grin$4), "", LOOKUP(A8,E$1:E$4,C$1:C$4) )

dimana jika batas atas pada pernyataan A8>=SUM(D$1emoticon-Big Grin$4) telah dilewati, maka nilai cell dikosongkan, tapi jika tidak maka gunakan rumus yang tadi telah dicari (rumus pada B8)


Resume :
- Yang digunakan adalah fungsi lookup()
- Buat data menerus (kolom E)
- Masukkan data menerus ke dalam fungsi lookup
- Cegah batas atas terlampaui dengan fungsi if()


----------------------------
Gimana gan, panjang kan ? emoticon-Blue Guy Cendol (S) emoticon-Blue Guy Cendol (S)

Akhirnya malah tidak bisa ngejunk emoticon-Frown emoticon-Frown

Gagal maning, gagal maning …… emoticon-Cape d... (S) emoticon-Cape d... (S)

............emoticon-Ngacir emoticon-Ngacir



Quote:


Matur thankyu pencerahannya,
kalimatnya sederhana, mudah dimengerti, detil.