[belajar-excel] Rumus Mengambil Nilai dengan mengabaikan kriteria tertentu [1 Attachment]

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

[belajar-excel] Rumus Mengambil Nilai dengan mengabaikan kriteria tertentu [1 Attachment]

Kamirin Faqoth
Dear All Member Be-Exceller
Mohon bantuannya, saya ingin mengambil suatu nilai dari tabel tertentu, tapi dengan mengabaikan baris yang saya tandai warna biru & merah.Berikut saya kirimkan Contoh Filenya



Terima KasihKamirin

File_Contoh.xlsx (16K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: [belajar-excel] Rumus Mengambil Nilai dengan mengabaikan kriteria tertentu

ghozi alkatiri
 tulis di G4 (array formula)
 =IFERROR(IF(INDEX(A$4:A$24,SMALL(IF(LEN($D$4:$D$24),ROW($D$4:$D$24)),ROW(1:1))-ROW($D$3))=0,G3,INDEX(A$4:A$24,SMALL(IF(LEN($D$4:$D$24),ROW($D$4:$D$24)),ROW(1:1))-ROW($D$3))),"")

copy ke kanan dan ke awah
wassalam
Ghozi Alkatiri
    Pada Sabtu, 26 Oktober 2019 19.42.19 WIB, Kamirin Faqoth [hidden email] [belajar-excel] <[hidden email]> menulis:  
 
    
   
   

Dear All Member Be-Exceller
Mohon bantuannya, saya ingin mengambil suatu nilai dari tabel tertentu, tapi dengan mengabaikan baris yang saya tandai warna biru & merah.Berikut saya kirimkan Contoh Filenya



Terima KasihKamirin
  #yiv1529015843 -- #yiv1529015843ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv1529015843 #yiv1529015843ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv1529015843 #yiv1529015843ygrp-mkp #yiv1529015843hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv1529015843 #yiv1529015843ygrp-mkp #yiv1529015843ads {margin-bottom:10px;}#yiv1529015843 #yiv1529015843ygrp-mkp .yiv1529015843ad {padding:0 0;}#yiv1529015843 #yiv1529015843ygrp-mkp .yiv1529015843ad p {margin:0;}#yiv1529015843 #yiv1529015843ygrp-mkp .yiv1529015843ad a {color:#0000ff;text-decoration:none;}#yiv1529015843 #yiv1529015843ygrp-sponsor #yiv1529015843ygrp-lc {font-family:Arial;}#yiv1529015843 #yiv1529015843ygrp-sponsor #yiv1529015843ygrp-lc #yiv1529015843hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv1529015843 #yiv1529015843ygrp-sponsor #yiv1529015843ygrp-lc .yiv1529015843ad {margin-bottom:10px;padding:0 0;}#yiv1529015843 #yiv1529015843actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv1529015843 #yiv1529015843activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv1529015843 #yiv1529015843activity span {font-weight:700;}#yiv1529015843 #yiv1529015843activity span:first-child {text-transform:uppercase;}#yiv1529015843 #yiv1529015843activity span a {color:#5085b6;text-decoration:none;}#yiv1529015843 #yiv1529015843activity span span {color:#ff7900;}#yiv1529015843 #yiv1529015843activity span .yiv1529015843underline {text-decoration:underline;}#yiv1529015843 .yiv1529015843attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv1529015843 .yiv1529015843attach div a {text-decoration:none;}#yiv1529015843 .yiv1529015843attach img {border:none;padding-right:5px;}#yiv1529015843 .yiv1529015843attach label {display:block;margin-bottom:5px;}#yiv1529015843 .yiv1529015843attach label a {text-decoration:none;}#yiv1529015843 blockquote {margin:0 0 0 4px;}#yiv1529015843 .yiv1529015843bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv1529015843 .yiv1529015843bold a {text-decoration:none;}#yiv1529015843 dd..yiv1529015843last p a {font-family:Verdana;font-weight:700;}#yiv1529015843 dd.yiv1529015843last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv1529015843 dd.yiv1529015843last p span.yiv1529015843yshortcuts {margin-right:0;}#yiv1529015843 div.yiv1529015843attach-table div div a {text-decoration:none;}#yiv1529015843 div.yiv1529015843attach-table {width:400px;}#yiv1529015843 div.yiv1529015843file-title a, #yiv1529015843 div.yiv1529015843file-title a:active, #yiv1529015843 div.yiv1529015843file-title a:hover, #yiv1529015843 div.yiv1529015843file-title a:visited {text-decoration:none;}#yiv1529015843 div.yiv1529015843photo-title a, #yiv1529015843 div.yiv1529015843photo-title a:active, #yiv1529015843 div.yiv1529015843photo-title a:hover, #yiv1529015843 div.yiv1529015843photo-title a:visited {text-decoration:none;}#yiv1529015843 div#yiv1529015843ygrp-mlmsg #yiv1529015843ygrp-msg p a span.yiv1529015843yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv1529015843 .yiv1529015843green {color:#628c2a;}#yiv1529015843 .yiv1529015843MsoNormal {margin:0 0 0 0;}#yiv1529015843 o {font-size:0;}#yiv1529015843 #yiv1529015843photos div {float:left;width:72px;}#yiv1529015843 #yiv1529015843photos div div {border:1px solid #666666;min-height:62px;overflow:hidden;width:62px;}#yiv1529015843 #yiv1529015843photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv1529015843 #yiv1529015843reco-category {font-size:77%;}#yiv1529015843 #yiv1529015843reco-desc {font-size:77%;}#yiv1529015843 .yiv1529015843replbq {margin:4px;}#yiv1529015843 #yiv1529015843ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv1529015843 #yiv1529015843ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv1529015843 #yiv1529015843ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv1529015843 #yiv1529015843ygrp-mlmsg select, #yiv1529015843 input, #yiv1529015843 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv1529015843 #yiv1529015843ygrp-mlmsg pre, #yiv1529015843 code {font:115% monospace;}#yiv1529015843 #yiv1529015843ygrp-mlmsg * {line-height:1..22em;}#yiv1529015843 #yiv1529015843ygrp-mlmsg #yiv1529015843logo {padding-bottom:10px;}#yiv1529015843 #yiv1529015843ygrp-msg p a {font-family:Verdana;}#yiv1529015843 #yiv1529015843ygrp-msg p#yiv1529015843attach-count span {color:#1E66AE;font-weight:700;}#yiv1529015843 #yiv1529015843ygrp-reco #yiv1529015843reco-head {color:#ff7900;font-weight:700;}#yiv1529015843 #yiv1529015843ygrp-reco {margin-bottom:20px;padding:0px;}#yiv1529015843 #yiv1529015843ygrp-sponsor #yiv1529015843ov li a {font-size:130%;text-decoration:none;}#yiv1529015843 #yiv1529015843ygrp-sponsor #yiv1529015843ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv1529015843 #yiv1529015843ygrp-sponsor #yiv1529015843ov ul {margin:0;padding:0 0 0 8px;}#yiv1529015843 #yiv1529015843ygrp-text {font-family:Georgia;}#yiv1529015843 #yiv1529015843ygrp-text p {margin:0 0 1em 0;}#yiv1529015843 #yiv1529015843ygrp-text tt {font-size:120%;}#yiv1529015843 #yiv1529015843ygrp-vital ul li:last-child {border-right:none !important;}#yiv1529015843
Reply | Threaded
Open this post in threaded view
|

RE: [belajar-excel] Rumus Mengambil Nilai dengan mengabaikan kriteria tertentu [2 Attachments]

Zainul Ulum
In reply to this post by Kamirin Faqoth
File terlampir menggunakan 2 cara:
1. Dengan formula excel
2. Deangan power query.
Semoga sesuai dengan yang diinginkan.
Wassalam
-zainul

Sent from Mail for Windows 10

From: Kamirin Faqoth [hidden email] [belajar-excel]
Sent: Saturday, October 26, 2019 7:42 PM
To: [hidden email]
Subject: [belajar-excel] Rumus Mengambil Nilai dengan mengabaikan kriteria tertentu [1 Attachment]

 
Dear All Member Be-Exceller

Mohon bantuannya, saya ingin mengambil suatu nilai dari tabel tertentu, tapi dengan mengabaikan baris yang saya tandai warna biru & merah.
Berikut saya kirimkan Contoh Filenya



Terima Kasih
Kamirin



re-File_Contoh.xlsx (35K) Download Attachment
2FCED4A06A5A4CBF838A0A268649656E.png (194 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: [belajar-excel] Rumus Mengambil Nilai dengan mengabaikan kriteria tertentu

Kamirin Faqoth
In reply to this post by ghozi alkatiri
 Maaf Pa Ghazi, rumusnya tidak berjalan sesuai dengan yang hasil yang diingkan, mungkin bisa diperiksa kembali.

Terima Kasih,Kamirin

    Pada Minggu, 27 Oktober 2019 12.54.51 WIB, ghozi alkatiri [hidden email] [belajar-excel] <[hidden email]> menulis:

  

 tulis di G4 (array formula)
 =IFERROR(IF(INDEX(A$4:A$24,SMALL(IF(LEN($D$4:$D$24),ROW($D$4:$D$24)),ROW(1:1))-ROW($D$3))=0,G3,INDEX(A$4:A$24,SMALL(IF(LEN($D$4:$D$24),ROW($D$4:$D$24)),ROW(1:1))-ROW($D$3))),"")

copy ke kanan dan ke awah
wassalam
Ghozi Alkatiri
    Pada Sabtu, 26 Oktober 2019 19.42.19 WIB, Kamirin Faqoth [hidden email] [belajar-excel] <[hidden email]> menulis:

  



Dear All Member Be-Exceller
Mohon bantuannya, saya ingin mengambil suatu nilai dari tabel tertentu, tapi dengan mengabaikan baris yang saya tandai warna biru & merah.Berikut saya kirimkan Contoh Filenya



Terima KasihKamirin
Reply | Threaded
Open this post in threaded view
|

Re: [belajar-excel] Rumus Mengambil Nilai dengan mengabaikan kriteria tertentu

Kamirin Faqoth
In reply to this post by Kamirin Faqoth
 Dear Pa Zainul,

Terima Kasih atas bantuannya, walaupun belum 100% mendekati hasil yang diinginkan, tapi ini sudah sangat membantu sekali, tapi mohon dijelaskan Pak, maksud dari formula dibawah ini, biar saya & anggota grup bisa memahami maksudnya.

=REPT(LOOKUP("ZZZzz";$A$4:$A4);D4>0)


Terima Kasih,Kamirin
    Pada Minggu, 27 Oktober 2019 13.37.44 WIB, Zainul Ulum [hidden email] [belajar-excel] <[hidden email]> menulis:

  


File terlampir menggunakan 2 cara:

   - Dengan formula excel
   - Deangan power query.

Semoga sesuai dengan yang diinginkan.

Wassalam

-zainul

  

Sent from Mail for Windows 10

  

From: Kamirin Faqoth [hidden email] [belajar-excel]
Sent: Saturday, October 26, 2019 7:42 PM
To: [hidden email]
Subject: [belajar-excel] Rumus Mengambil Nilai dengan mengabaikan kriteria tertentu [1 Attachment]

  

 

Dear All Member Be-Exceller

  

Mohon bantuannya, saya ingin mengambil suatu nilai dari tabel tertentu, tapi dengan mengabaikan baris yang saya tandai warna biru & merah.

Berikut saya kirimkan Contoh Filenya

  

  

  

Terima Kasih

Kamirin



  
  #yiv0924233682 #yiv0924233682 -- #yiv0924233682ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv0924233682 #yiv0924233682ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv0924233682 #yiv0924233682ygrp-mkp #yiv0924233682hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv0924233682 #yiv0924233682ygrp-mkp #yiv0924233682ads {margin-bottom:10px;}#yiv0924233682 #yiv0924233682ygrp-mkp .yiv0924233682ad {padding:0 0;}#yiv0924233682 #yiv0924233682ygrp-mkp .yiv0924233682ad p {margin:0;}#yiv0924233682 #yiv0924233682ygrp-mkp .yiv0924233682ad a {color:#0000ff;text-decoration:none;}#yiv0924233682 #yiv0924233682ygrp-sponsor #yiv0924233682ygrp-lc {font-family:Arial;}#yiv0924233682 #yiv0924233682ygrp-sponsor #yiv0924233682ygrp-lc #yiv0924233682hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv0924233682 #yiv0924233682ygrp-sponsor #yiv0924233682ygrp-lc .yiv0924233682ad {margin-bottom:10px;padding:0 0;}#yiv0924233682 #yiv0924233682actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv0924233682 #yiv0924233682activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv0924233682 #yiv0924233682activity span {font-weight:700;}#yiv0924233682 #yiv0924233682activity span:first-child {text-transform:uppercase;}#yiv0924233682 #yiv0924233682activity span a {color:#5085b6;text-decoration:none;}#yiv0924233682 #yiv0924233682activity span span {color:#ff7900;}#yiv0924233682 #yiv0924233682activity span .yiv0924233682underline {text-decoration:underline;}#yiv0924233682 .yiv0924233682attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv0924233682 .yiv0924233682attach div a {text-decoration:none;}#yiv0924233682 .yiv0924233682attach img {border:none;padding-right:5px;}#yiv0924233682 .yiv0924233682attach label {display:block;margin-bottom:5px;}#yiv0924233682 .yiv0924233682attach label a {text-decoration:none;}#yiv0924233682 blockquote {margin:0 0 0 4px;}#yiv0924233682 .yiv0924233682bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv0924233682 .yiv0924233682bold a {text-decoration:none;}#yiv0924233682 dd.yiv0924233682last p a {font-family:Verdana;font-weight:700;}#yiv0924233682 dd.yiv0924233682last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv0924233682 dd.yiv0924233682last p span.yiv0924233682yshortcuts {margin-right:0;}#yiv0924233682 div.yiv0924233682attach-table div div a {text-decoration:none;}#yiv0924233682 div.yiv0924233682attach-table {width:400px;}#yiv0924233682 div.yiv0924233682file-title a, #yiv0924233682 div.yiv0924233682file-title a:active, #yiv0924233682 div.yiv0924233682file-title a:hover, #yiv0924233682 div.yiv0924233682file-title a:visited {text-decoration:none;}#yiv0924233682 div.yiv0924233682photo-title a, #yiv0924233682 div.yiv0924233682photo-title a:active, #yiv0924233682 div.yiv0924233682photo-title a:hover, #yiv0924233682 div.yiv0924233682photo-title a:visited {text-decoration:none;}#yiv0924233682 div#yiv0924233682ygrp-mlmsg #yiv0924233682ygrp-msg p a span.yiv0924233682yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv0924233682 .yiv0924233682green {color:#628c2a;}#yiv0924233682 .yiv0924233682MsoNormal {margin:0 0 0 0;}#yiv0924233682 o {font-size:0;}#yiv0924233682 #yiv0924233682photos div {float:left;width:72px;}#yiv0924233682 #yiv0924233682photos div div {border:1px solid #666666;min-height:62px;overflow:hidden;width:62px;}#yiv0924233682 #yiv0924233682photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv0924233682 #yiv0924233682reco-category {font-size:77%;}#yiv0924233682 #yiv0924233682reco-desc {font-size:77%;}#yiv0924233682 .yiv0924233682replbq {margin:4px;}#yiv0924233682 #yiv0924233682ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv0924233682 #yiv0924233682ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv0924233682 #yiv0924233682ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv0924233682 #yiv0924233682ygrp-mlmsg select, #yiv0924233682 input, #yiv0924233682 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv0924233682 #yiv0924233682ygrp-mlmsg pre, #yiv0924233682 code {font:115% monospace;}#yiv0924233682 #yiv0924233682ygrp-mlmsg * {line-height:1.22em;}#yiv0924233682 #yiv0924233682ygrp-mlmsg #yiv0924233682logo {padding-bottom:10px;}#yiv0924233682 #yiv0924233682ygrp-msg p a {font-family:Verdana;}#yiv0924233682 #yiv0924233682ygrp-msg p#yiv0924233682attach-count span {color:#1E66AE;font-weight:700;}#yiv0924233682 #yiv0924233682ygrp-reco #yiv0924233682reco-head {color:#ff7900;font-weight:700;}#yiv0924233682 #yiv0924233682ygrp-reco {margin-bottom:20px;padding:0px;}#yiv0924233682 #yiv0924233682ygrp-sponsor #yiv0924233682ov li a {font-size:130%;text-decoration:none;}#yiv0924233682 #yiv0924233682ygrp-sponsor #yiv0924233682ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv0924233682 #yiv0924233682ygrp-sponsor #yiv0924233682ov ul {margin:0;padding:0 0 0 8px;}#yiv0924233682 #yiv0924233682ygrp-text {font-family:Georgia;}#yiv0924233682 #yiv0924233682ygrp-text p {margin:0 0 1em 0;}#yiv0924233682 #yiv0924233682ygrp-text tt {font-size:120%;}#yiv0924233682 #yiv0924233682ygrp-vital ul li:last-child {border-right:none !important;}#yiv0924233682