[belajar-excel] multi pencarian menggunakan " * " ???

classic Classic list List threaded Threaded
17 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

[belajar-excel] multi pencarian menggunakan " * " ???

Milis Belajar Excel mailing list
haloo master, gimana ya cara membuat multi pencarin menggunakan " * "
saya sudah mencoba menggunakan rumus ini tapi ga bisa
countif(B5,or("aajj"&"*","bbjj"&"*")
 

 kalo menggunakan OR pasti salah, sementara yg mau saya cari lebh dari 1 kata
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Bls: [belajar-excel] multi pencarian menggunakan " * " ???

Milis Belajar Excel mailing list
hai shenly,
mungkinkah yang anda maksud itu :
=COUNTIF(B5;"aajj*")+COUNTIF(B5;"bbjj*")
 br,

odong nando


      Dari: "[hidden email] [belajar-excel]" <[hidden email]>
 Kepada: [hidden email]
 Dikirim: Sabtu, 1 Juli 2017 0:41
 Judul: [belajar-excel] multi pencarian menggunakan " * " ???
   
    haloo master, gimana ya cara membuat multi pencarin menggunakan " * "
saya sudah mencoba menggunakan rumus ini tapi ga bisa 
countif(B5,or("aajj"&"*","bbjj"&"*")
kalo menggunakan OR pasti salah, sementara yg mau saya cari lebh dari 1 kata  #yiv8287291338 #yiv8287291338 -- #yiv8287291338ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv8287291338 #yiv8287291338ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv8287291338 #yiv8287291338ygrp-mkp #yiv8287291338hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv8287291338 #yiv8287291338ygrp-mkp #yiv8287291338ads {margin-bottom:10px;}#yiv8287291338 #yiv8287291338ygrp-mkp .yiv8287291338ad {padding:0 0;}#yiv8287291338 #yiv8287291338ygrp-mkp .yiv8287291338ad p {margin:0;}#yiv8287291338 #yiv8287291338ygrp-mkp .yiv8287291338ad a {color:#0000ff;text-decoration:none;}#yiv8287291338 #yiv8287291338ygrp-sponsor #yiv8287291338ygrp-lc {font-family:Arial;}#yiv8287291338 #yiv8287291338ygrp-sponsor #yiv8287291338ygrp-lc #yiv8287291338hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv8287291338 #yiv8287291338ygrp-sponsor #yiv8287291338ygrp-lc .yiv8287291338ad {margin-bottom:10px;padding:0 0;}#yiv8287291338 #yiv8287291338actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv8287291338 #yiv8287291338activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv8287291338 #yiv8287291338activity span {font-weight:700;}#yiv8287291338 #yiv8287291338activity span:first-child {text-transform:uppercase;}#yiv8287291338 #yiv8287291338activity span a {color:#5085b6;text-decoration:none;}#yiv8287291338 #yiv8287291338activity span span {color:#ff7900;}#yiv8287291338 #yiv8287291338activity span .yiv8287291338underline {text-decoration:underline;}#yiv8287291338 .yiv8287291338attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv8287291338 .yiv8287291338attach div a {text-decoration:none;}#yiv8287291338 .yiv8287291338attach img {border:none;padding-right:5px;}#yiv8287291338 .yiv8287291338attach label {display:block;margin-bottom:5px;}#yiv8287291338 .yiv8287291338attach label a {text-decoration:none;}#yiv8287291338 blockquote {margin:0 0 0 4px;}#yiv8287291338 .yiv8287291338bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv8287291338 .yiv8287291338bold a {text-decoration:none;}#yiv8287291338 dd.yiv8287291338last p a {font-family:Verdana;font-weight:700;}#yiv8287291338 dd.yiv8287291338last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv8287291338 dd.yiv8287291338last p span.yiv8287291338yshortcuts {margin-right:0;}#yiv8287291338 div.yiv8287291338attach-table div div a {text-decoration:none;}#yiv8287291338 div.yiv8287291338attach-table {width:400px;}#yiv8287291338 div.yiv8287291338file-title a, #yiv8287291338 div.yiv8287291338file-title a:active, #yiv8287291338 div.yiv8287291338file-title a:hover, #yiv8287291338 div.yiv8287291338file-title a:visited {text-decoration:none;}#yiv8287291338 div.yiv8287291338photo-title a, #yiv8287291338 div.yiv8287291338photo-title a:active, #yiv8287291338 div.yiv8287291338photo-title a:hover, #yiv8287291338 div.yiv8287291338photo-title a:visited {text-decoration:none;}#yiv8287291338 div#yiv8287291338ygrp-mlmsg #yiv8287291338ygrp-msg p a span.yiv8287291338yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv8287291338 .yiv8287291338green {color:#628c2a;}#yiv8287291338 .yiv8287291338MsoNormal {margin:0 0 0 0;}#yiv8287291338 o {font-size:0;}#yiv8287291338 #yiv8287291338photos div {float:left;width:72px;}#yiv8287291338 #yiv8287291338photos div div {border:1px solid #666666;min-height:62px;overflow:hidden;width:62px;}#yiv8287291338 #yiv8287291338photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv8287291338 #yiv8287291338reco-category {font-size:77%;}#yiv8287291338 #yiv8287291338reco-desc {font-size:77%;}#yiv8287291338 .yiv8287291338replbq {margin:4px;}#yiv8287291338 #yiv8287291338ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv8287291338 #yiv8287291338ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv8287291338 #yiv8287291338ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv8287291338 #yiv8287291338ygrp-mlmsg select, #yiv8287291338 input, #yiv8287291338 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv8287291338 #yiv8287291338ygrp-mlmsg pre, #yiv8287291338 code {font:115% monospace;}#yiv8287291338 #yiv8287291338ygrp-mlmsg * {line-height:1.22em;}#yiv8287291338 #yiv8287291338ygrp-mlmsg #yiv8287291338logo {padding-bottom:10px;}#yiv8287291338 #yiv8287291338ygrp-msg p a {font-family:Verdana;}#yiv8287291338 #yiv8287291338ygrp-msg p#yiv8287291338attach-count span {color:#1E66AE;font-weight:700;}#yiv8287291338 #yiv8287291338ygrp-reco #yiv8287291338reco-head {color:#ff7900;font-weight:700;}#yiv8287291338 #yiv8287291338ygrp-reco {margin-bottom:20px;padding:0px;}#yiv8287291338 #yiv8287291338ygrp-sponsor #yiv8287291338ov li a {font-size:130%;text-decoration:none;}#yiv8287291338 #yiv8287291338ygrp-sponsor #yiv8287291338ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv8287291338 #yiv8287291338ygrp-sponsor #yiv8287291338ov ul {margin:0;padding:0 0 0 8px;}#yiv8287291338 #yiv8287291338ygrp-text {font-family:Georgia;}#yiv8287291338 #yiv8287291338ygrp-text p {margin:0 0 1em 0;}#yiv8287291338 #yiv8287291338ygrp-text tt {font-size:120%;}#yiv8287291338 #yiv8287291338ygrp-vital ul li:last-child {border-right:none !important;}#yiv8287291338

   
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

[belajar-excel] Re: multi pencarian menggunakan " * " ???

Milis Belajar Excel mailing list
In reply to this post by Milis Belajar Excel mailing list
ooh jadi kaya IF bercabang ya mas, tadinya saya mau meringkas rumusnya biar ga gtu panjg hehe, tapi malah ga jalan. Tapi makasih byk ya mas buat jawabnnya ^.^
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [belajar-excel] Re: multi pencarian menggunakan " * " ???

Milis Belajar Excel mailing list
Yth. Rekan be-exceler
Mohon kiranya berkenan menjelaskan rumus tersebut ,, ada asterik2 nya ..terima kasih


    On Wednesday, July 5, 2017 6:11 PM, "[hidden email] [belajar-excel]" <[hidden email]> wrote:


     ooh jadi kaya IF bercabang ya mas, tadinya saya mau meringkas rumusnya biar ga gtu panjg hehe, tapi malah ga jalan. Tapi makasih byk ya mas buat jawabnnya ^.^  #yiv5182603333 #yiv5182603333 -- #yiv5182603333ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv5182603333 #yiv5182603333ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv5182603333 #yiv5182603333ygrp-mkp #yiv5182603333hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv5182603333 #yiv5182603333ygrp-mkp #yiv5182603333ads {margin-bottom:10px;}#yiv5182603333 #yiv5182603333ygrp-mkp .yiv5182603333ad {padding:0 0;}#yiv5182603333 #yiv5182603333ygrp-mkp .yiv5182603333ad p {margin:0;}#yiv5182603333 #yiv5182603333ygrp-mkp .yiv5182603333ad a {color:#0000ff;text-decoration:none;}#yiv5182603333 #yiv5182603333ygrp-sponsor #yiv5182603333ygrp-lc {font-family:Arial;}#yiv5182603333 #yiv5182603333ygrp-sponsor #yiv5182603333ygrp-lc #yiv5182603333hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv5182603333 #yiv5182603333ygrp-sponsor #yiv5182603333ygrp-lc .yiv5182603333ad {margin-bottom:10px;padding:0 0;}#yiv5182603333 #yiv5182603333actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv5182603333 #yiv5182603333activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv5182603333 #yiv5182603333activity span {font-weight:700;}#yiv5182603333 #yiv5182603333activity span:first-child {text-transform:uppercase;}#yiv5182603333 #yiv5182603333activity span a {color:#5085b6;text-decoration:none;}#yiv5182603333 #yiv5182603333activity span span {color:#ff7900;}#yiv5182603333 #yiv5182603333activity span .yiv5182603333underline {text-decoration:underline;}#yiv5182603333 .yiv5182603333attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv5182603333 .yiv5182603333attach div a {text-decoration:none;}#yiv5182603333 .yiv5182603333attach img {border:none;padding-right:5px;}#yiv5182603333 .yiv5182603333attach label {display:block;margin-bottom:5px;}#yiv5182603333 .yiv5182603333attach label a {text-decoration:none;}#yiv5182603333 blockquote {margin:0 0 0 4px;}#yiv5182603333 .yiv5182603333bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv5182603333 .yiv5182603333bold a {text-decoration:none;}#yiv5182603333 dd.yiv5182603333last p a {font-family:Verdana;font-weight:700;}#yiv5182603333 dd.yiv5182603333last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv5182603333 dd.yiv5182603333last p span.yiv5182603333yshortcuts {margin-right:0;}#yiv5182603333 div.yiv5182603333attach-table div div a {text-decoration:none;}#yiv5182603333 div.yiv5182603333attach-table {width:400px;}#yiv5182603333 div.yiv5182603333file-title a, #yiv5182603333 div.yiv5182603333file-title a:active, #yiv5182603333 div.yiv5182603333file-title a:hover, #yiv5182603333 div.yiv5182603333file-title a:visited {text-decoration:none;}#yiv5182603333 div.yiv5182603333photo-title a, #yiv5182603333 div.yiv5182603333photo-title a:active, #yiv5182603333 div.yiv5182603333photo-title a:hover, #yiv5182603333 div.yiv5182603333photo-title a:visited {text-decoration:none;}#yiv5182603333 div#yiv5182603333ygrp-mlmsg #yiv5182603333ygrp-msg p a span.yiv5182603333yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv5182603333 .yiv5182603333green {color:#628c2a;}#yiv5182603333 .yiv5182603333MsoNormal {margin:0 0 0 0;}#yiv5182603333 o {font-size:0;}#yiv5182603333 #yiv5182603333photos div {float:left;width:72px;}#yiv5182603333 #yiv5182603333photos div div {border:1px solid #666666;min-height:62px;overflow:hidden;width:62px;}#yiv5182603333 #yiv5182603333photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv5182603333 #yiv5182603333reco-category {font-size:77%;}#yiv5182603333 #yiv5182603333reco-desc {font-size:77%;}#yiv5182603333 .yiv5182603333replbq {margin:4px;}#yiv5182603333 #yiv5182603333ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv5182603333 #yiv5182603333ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv5182603333 #yiv5182603333ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv5182603333 #yiv5182603333ygrp-mlmsg select, #yiv5182603333 input, #yiv5182603333 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv5182603333 #yiv5182603333ygrp-mlmsg pre, #yiv5182603333 code {font:115% monospace;}#yiv5182603333 #yiv5182603333ygrp-mlmsg * {line-height:1.22em;}#yiv5182603333 #yiv5182603333ygrp-mlmsg #yiv5182603333logo {padding-bottom:10px;}#yiv5182603333 #yiv5182603333ygrp-msg p a {font-family:Verdana;}#yiv5182603333 #yiv5182603333ygrp-msg p#yiv5182603333attach-count span {color:#1E66AE;font-weight:700;}#yiv5182603333 #yiv5182603333ygrp-reco #yiv5182603333reco-head {color:#ff7900;font-weight:700;}#yiv5182603333 #yiv5182603333ygrp-reco {margin-bottom:20px;padding:0px;}#yiv5182603333 #yiv5182603333ygrp-sponsor #yiv5182603333ov li a {font-size:130%;text-decoration:none;}#yiv5182603333 #yiv5182603333ygrp-sponsor #yiv5182603333ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv5182603333 #yiv5182603333ygrp-sponsor #yiv5182603333ov ul {margin:0;padding:0 0 0 8px;}#yiv5182603333 #yiv5182603333ygrp-text {font-family:Georgia;}#yiv5182603333 #yiv5182603333ygrp-text p {margin:0 0 1em 0;}#yiv5182603333 #yiv5182603333ygrp-text tt {font-size:120%;}#yiv5182603333 #yiv5182603333ygrp-vital ul li:last-child {border-right:none !important;}#yiv5182603333

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [belajar-excel] Re: multi pencarian menggunakan " * " ???

Milis Belajar Excel mailing list
Hai Arie,

Wildcard Character :
* artinya sebarang characters
? artinya sebuah karakter sebarang

Regards,
Kid



On Thu, Jul 6, 2017 at 10:40 AM, Eira I Arie [hidden email]
[belajar-excel] <[hidden email]> wrote:

>
>
> Yth. Rekan be-exceler
>
> Mohon kiranya berkenan menjelaskan rumus tersebut ,, ada asterik2 nya ..
> terima kasih
>
>
> On Wednesday, July 5, 2017 6:11 PM, "[hidden email]
> [belajar-excel]" <[hidden email]> wrote:
>
>
>
> ooh jadi kaya IF bercabang ya mas, tadinya saya mau meringkas rumusnya
> biar ga gtu panjg hehe, tapi malah ga jalan. Tapi makasih byk ya mas buat
> jawabnnya ^.^
>
>
>
>
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [belajar-excel] multi pencarian menggunakan " * " ???

Milis Belajar Excel mailing list
In reply to this post by Milis Belajar Excel mailing list
Kalau kriterianya ada disuatu range tertentu, bisa begini :

[bukan array formula]
=SumProduct( CountIF( range_data , range_kriteria & "*" ) )

*** Jika fungsi SumProduct diganti dengan Sum, enter formula sebagai array
formula.
*** Jika regional setting komputer setempat adalah Indonesian, ganti
karakter koma dengan titik koma

Regards,
Kid





On Wed, Jul 5, 2017 at 10:50 AM, odong nando [hidden email]
[belajar-excel] <[hidden email]> wrote:

>
>
> hai shenly,
>
> mungkinkah yang anda maksud itu :
>
> =COUNTIF(B5;"aajj*")+COUNTIF(B5;"bbjj*")
>
> br,
>
> odong nando
>
>
> ------------------------------
> *Dari:* "[hidden email] [belajar-excel]" <
> [hidden email]>
> *Kepada:* [hidden email]
> *Dikirim:* Sabtu, 1 Juli 2017 0:41
> *Judul:* [belajar-excel] multi pencarian menggunakan " * " ???
>
>
> haloo master, gimana ya cara membuat multi pencarin menggunakan " * "
> saya sudah mencoba menggunakan rumus ini tapi ga bisa
> countif(B5,or("aajj"&"*","bbjj"&"*")
>
> kalo menggunakan OR pasti salah, sementara yg mau saya cari lebh dari 1
> kata
>
>
>
>
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Bls: [belajar-excel] multi pencarian menggunakan " * " ???

Milis Belajar Excel mailing list
iya..ya boz kid,
bagi yang males bwt range sendiri untuk range_kriteria nya, bisa di tulis juga kok ternyata di formulanya, klo masih di bawah 5 kriteria mungkin masih enak ditulis di formula, cuma klo dah lebih kayaknya enakan di buat range_kriteria sendiri dah...
klo mo di tulis di formula nya sekaligus "range_kriterianya", pada formula yang boz kid utarakan bisa ditulis dengan :
=SUMPRODUCT(COUNTIF(B5;{"aajj"\"bbjj"\"ccjj"}&"*"))

jika regional seting kompi setempat english maka ubah karakter titik koma ";" dan backslash "\" menjadi koma ","
NOTE : yang backslash saya lupa2 inget bener atau tidak, mohon untuk di koreksi bila memang salah...  br,

odong nando


      Dari: "'Mr. Kid' [hidden email] [belajar-excel]" <[hidden email]>
 Kepada: BeExcel <[hidden email]>
 Dikirim: Kamis, 6 Juli 2017 15:44
 Judul: Re: [belajar-excel] multi pencarian menggunakan " * " ???
   
    Kalau kriterianya ada disuatu range tertentu, bisa begini :

[bukan array formula]
=SumProduct( CountIF( range_data , range_kriteria & "*" ) )

*** Jika fungsi SumProduct diganti dengan Sum, enter formula sebagai array formula.
*** Jika regional setting komputer setempat adalah Indonesian, ganti karakter koma dengan titik koma

Regards,
Kid





On Wed, Jul 5, 2017 at 10:50 AM, odong nando [hidden email] [belajar-excel] <[hidden email]> wrote:

     hai shenly,
mungkinkah yang anda maksud itu :
=COUNTIF(B5;"aajj*")+COUNTIF( B5;"bbjj*")
 br,

odong nando


      Dari: "[hidden email] [belajar-excel]" <[hidden email] >
 Kepada: [hidden email]
 Dikirim: Sabtu, 1 Juli 2017 0:41
 Judul: [belajar-excel] multi pencarian menggunakan " * " ???
 
    haloo master, gimana ya cara membuat multi pencarin menggunakan " * "
saya sudah mencoba menggunakan rumus ini tapi ga bisa 
countif(B5,or("aajj"&"*"," bbjj"&"*")
kalo menggunakan OR pasti salah, sementara yg mau saya cari lebh dari 1 kata  

     

  #yiv2237615521 #yiv2237615521 -- #yiv2237615521ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv2237615521 #yiv2237615521ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv2237615521 #yiv2237615521ygrp-mkp #yiv2237615521hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv2237615521 #yiv2237615521ygrp-mkp #yiv2237615521ads {margin-bottom:10px;}#yiv2237615521 #yiv2237615521ygrp-mkp .yiv2237615521ad {padding:0 0;}#yiv2237615521 #yiv2237615521ygrp-mkp .yiv2237615521ad p {margin:0;}#yiv2237615521 #yiv2237615521ygrp-mkp .yiv2237615521ad a {color:#0000ff;text-decoration:none;}#yiv2237615521 #yiv2237615521ygrp-sponsor #yiv2237615521ygrp-lc {font-family:Arial;}#yiv2237615521 #yiv2237615521ygrp-sponsor #yiv2237615521ygrp-lc #yiv2237615521hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv2237615521 #yiv2237615521ygrp-sponsor #yiv2237615521ygrp-lc .yiv2237615521ad {margin-bottom:10px;padding:0 0;}#yiv2237615521 #yiv2237615521actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv2237615521 #yiv2237615521activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv2237615521 #yiv2237615521activity span {font-weight:700;}#yiv2237615521 #yiv2237615521activity span:first-child {text-transform:uppercase;}#yiv2237615521 #yiv2237615521activity span a {color:#5085b6;text-decoration:none;}#yiv2237615521 #yiv2237615521activity span span {color:#ff7900;}#yiv2237615521 #yiv2237615521activity span .yiv2237615521underline {text-decoration:underline;}#yiv2237615521 .yiv2237615521attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv2237615521 .yiv2237615521attach div a {text-decoration:none;}#yiv2237615521 .yiv2237615521attach img {border:none;padding-right:5px;}#yiv2237615521 .yiv2237615521attach label {display:block;margin-bottom:5px;}#yiv2237615521 .yiv2237615521attach label a {text-decoration:none;}#yiv2237615521 blockquote {margin:0 0 0 4px;}#yiv2237615521 .yiv2237615521bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv2237615521 .yiv2237615521bold a {text-decoration:none;}#yiv2237615521 dd.yiv2237615521last p a {font-family:Verdana;font-weight:700;}#yiv2237615521 dd.yiv2237615521last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv2237615521 dd.yiv2237615521last p span.yiv2237615521yshortcuts {margin-right:0;}#yiv2237615521 div.yiv2237615521attach-table div div a {text-decoration:none;}#yiv2237615521 div.yiv2237615521attach-table {width:400px;}#yiv2237615521 div.yiv2237615521file-title a, #yiv2237615521 div.yiv2237615521file-title a:active, #yiv2237615521 div.yiv2237615521file-title a:hover, #yiv2237615521 div.yiv2237615521file-title a:visited {text-decoration:none;}#yiv2237615521 div.yiv2237615521photo-title a, #yiv2237615521 div.yiv2237615521photo-title a:active, #yiv2237615521 div.yiv2237615521photo-title a:hover, #yiv2237615521 div.yiv2237615521photo-title a:visited {text-decoration:none;}#yiv2237615521 div#yiv2237615521ygrp-mlmsg #yiv2237615521ygrp-msg p a span.yiv2237615521yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv2237615521 .yiv2237615521green {color:#628c2a;}#yiv2237615521 .yiv2237615521MsoNormal {margin:0 0 0 0;}#yiv2237615521 o {font-size:0;}#yiv2237615521 #yiv2237615521photos div {float:left;width:72px;}#yiv2237615521 #yiv2237615521photos div div {border:1px solid #666666;min-height:62px;overflow:hidden;width:62px;}#yiv2237615521 #yiv2237615521photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv2237615521 #yiv2237615521reco-category {font-size:77%;}#yiv2237615521 #yiv2237615521reco-desc {font-size:77%;}#yiv2237615521 .yiv2237615521replbq {margin:4px;}#yiv2237615521 #yiv2237615521ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv2237615521 #yiv2237615521ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv2237615521 #yiv2237615521ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv2237615521 #yiv2237615521ygrp-mlmsg select, #yiv2237615521 input, #yiv2237615521 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv2237615521 #yiv2237615521ygrp-mlmsg pre, #yiv2237615521 code {font:115% monospace;}#yiv2237615521 #yiv2237615521ygrp-mlmsg * {line-height:1.22em;}#yiv2237615521 #yiv2237615521ygrp-mlmsg #yiv2237615521logo {padding-bottom:10px;}#yiv2237615521 #yiv2237615521ygrp-msg p a {font-family:Verdana;}#yiv2237615521 #yiv2237615521ygrp-msg p#yiv2237615521attach-count span {color:#1E66AE;font-weight:700;}#yiv2237615521 #yiv2237615521ygrp-reco #yiv2237615521reco-head {color:#ff7900;font-weight:700;}#yiv2237615521 #yiv2237615521ygrp-reco {margin-bottom:20px;padding:0px;}#yiv2237615521 #yiv2237615521ygrp-sponsor #yiv2237615521ov li a {font-size:130%;text-decoration:none;}#yiv2237615521 #yiv2237615521ygrp-sponsor #yiv2237615521ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv2237615521 #yiv2237615521ygrp-sponsor #yiv2237615521ov ul {margin:0;padding:0 0 0 8px;}#yiv2237615521 #yiv2237615521ygrp-text {font-family:Georgia;}#yiv2237615521 #yiv2237615521ygrp-text p {margin:0 0 1em 0;}#yiv2237615521 #yiv2237615521ygrp-text tt {font-size:120%;}#yiv2237615521 #yiv2237615521ygrp-vital ul li:last-child {border-right:none !important;}#yiv2237615521

   
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Bls: [belajar-excel] multi pencarian menggunakan " * " ???

Milis Belajar Excel mailing list
Yth. Rekan Be-Exceller
Terkait rumus tersebut, bisakah memberikan contoh kasusnya ?Terima kasih
Ari


    On Thursday, July 6, 2017 4:11 PM, "odong nando [hidden email] [belajar-excel]" <[hidden email]> wrote:


     iya..ya boz kid,
bagi yang males bwt range sendiri untuk range_kriteria nya, bisa di tulis juga kok ternyata di formulanya, klo masih di bawah 5 kriteria mungkin masih enak ditulis di formula, cuma klo dah lebih kayaknya enakan di buat range_kriteria sendiri dah...
klo mo di tulis di formula nya sekaligus "range_kriterianya", pada formula yang boz kid utarakan bisa ditulis dengan :
=SUMPRODUCT(COUNTIF(B5;{"aajj"\"bbjj"\"ccjj"}&"*"))

jika regional seting kompi setempat english maka ubah karakter titik koma ";" dan backslash "\" menjadi koma ","
NOTE : yang backslash saya lupa2 inget bener atau tidak, mohon untuk di koreksi bila memang salah...  br,

odong nando


      Dari: "'Mr. Kid' [hidden email] [belajar-excel]" <[hidden email]>
 Kepada: BeExcel <[hidden email]>
 Dikirim: Kamis, 6 Juli 2017 15:44
 Judul: Re: [belajar-excel] multi pencarian menggunakan " * " ???

    Kalau kriterianya ada disuatu range tertentu, bisa begini :

[bukan array formula]
=SumProduct( CountIF( range_data , range_kriteria & "*" ) )

*** Jika fungsi SumProduct diganti dengan Sum, enter formula sebagai array formula.
*** Jika regional setting komputer setempat adalah Indonesian, ganti karakter koma dengan titik koma

Regards,
Kid





On Wed, Jul 5, 2017 at 10:50 AM, odong nando [hidden email] [belajar-excel] <[hidden email]> wrote:

     hai shenly,
mungkinkah yang anda maksud itu :
=COUNTIF(B5;"aajj*")+COUNTIF( B5;"bbjj*")
 br,

odong nando


      Dari: "[hidden email] [belajar-excel]" <[hidden email] >
 Kepada: [hidden email]
 Dikirim: Sabtu, 1 Juli 2017 0:41
 Judul: [belajar-excel] multi pencarian menggunakan " * " ???

    haloo master, gimana ya cara membuat multi pencarin menggunakan " * "
saya sudah mencoba menggunakan rumus ini tapi ga bisa 
countif(B5,or("aajj"&"*"," bbjj"&"*")
kalo menggunakan OR pasti salah, sementara yg mau saya cari lebh dari 1 kata





     #yiv8042069645 #yiv8042069645 -- #yiv8042069645ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv8042069645 #yiv8042069645ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv8042069645 #yiv8042069645ygrp-mkp #yiv8042069645hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv8042069645 #yiv8042069645ygrp-mkp #yiv8042069645ads {margin-bottom:10px;}#yiv8042069645 #yiv8042069645ygrp-mkp .yiv8042069645ad {padding:0 0;}#yiv8042069645 #yiv8042069645ygrp-mkp .yiv8042069645ad p {margin:0;}#yiv8042069645 #yiv8042069645ygrp-mkp .yiv8042069645ad a {color:#0000ff;text-decoration:none;}#yiv8042069645 #yiv8042069645ygrp-sponsor #yiv8042069645ygrp-lc {font-family:Arial;}#yiv8042069645 #yiv8042069645ygrp-sponsor #yiv8042069645ygrp-lc #yiv8042069645hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv8042069645 #yiv8042069645ygrp-sponsor #yiv8042069645ygrp-lc .yiv8042069645ad {margin-bottom:10px;padding:0 0;}#yiv8042069645 #yiv8042069645actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv8042069645 #yiv8042069645activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv8042069645 #yiv8042069645activity span {font-weight:700;}#yiv8042069645 #yiv8042069645activity span:first-child {text-transform:uppercase;}#yiv8042069645 #yiv8042069645activity span a {color:#5085b6;text-decoration:none;}#yiv8042069645 #yiv8042069645activity span span {color:#ff7900;}#yiv8042069645 #yiv8042069645activity span .yiv8042069645underline {text-decoration:underline;}#yiv8042069645 .yiv8042069645attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv8042069645 .yiv8042069645attach div a {text-decoration:none;}#yiv8042069645 .yiv8042069645attach img {border:none;padding-right:5px;}#yiv8042069645 .yiv8042069645attach label {display:block;margin-bottom:5px;}#yiv8042069645 .yiv8042069645attach label a {text-decoration:none;}#yiv8042069645 blockquote {margin:0 0 0 4px;}#yiv8042069645 .yiv8042069645bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv8042069645 .yiv8042069645bold a {text-decoration:none;}#yiv8042069645 dd.yiv8042069645last p a {font-family:Verdana;font-weight:700;}#yiv8042069645 dd.yiv8042069645last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv8042069645 dd.yiv8042069645last p span.yiv8042069645yshortcuts {margin-right:0;}#yiv8042069645 div.yiv8042069645attach-table div div a {text-decoration:none;}#yiv8042069645 div.yiv8042069645attach-table {width:400px;}#yiv8042069645 div.yiv8042069645file-title a, #yiv8042069645 div.yiv8042069645file-title a:active, #yiv8042069645 div.yiv8042069645file-title a:hover, #yiv8042069645 div.yiv8042069645file-title a:visited {text-decoration:none;}#yiv8042069645 div.yiv8042069645photo-title a, #yiv8042069645 div.yiv8042069645photo-title a:active, #yiv8042069645 div.yiv8042069645photo-title a:hover, #yiv8042069645 div.yiv8042069645photo-title a:visited {text-decoration:none;}#yiv8042069645 div#yiv8042069645ygrp-mlmsg #yiv8042069645ygrp-msg p a span.yiv8042069645yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv8042069645 .yiv8042069645green {color:#628c2a;}#yiv8042069645 .yiv8042069645MsoNormal {margin:0 0 0 0;}#yiv8042069645 o {font-size:0;}#yiv8042069645 #yiv8042069645photos div {float:left;width:72px;}#yiv8042069645 #yiv8042069645photos div div {border:1px solid #666666;min-height:62px;overflow:hidden;width:62px;}#yiv8042069645 #yiv8042069645photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv8042069645 #yiv8042069645reco-category {font-size:77%;}#yiv8042069645 #yiv8042069645reco-desc {font-size:77%;}#yiv8042069645 .yiv8042069645replbq {margin:4px;}#yiv8042069645 #yiv8042069645ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv8042069645 #yiv8042069645ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv8042069645 #yiv8042069645ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv8042069645 #yiv8042069645ygrp-mlmsg select, #yiv8042069645 input, #yiv8042069645 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv8042069645 #yiv8042069645ygrp-mlmsg pre, #yiv8042069645 code {font:115% monospace;}#yiv8042069645 #yiv8042069645ygrp-mlmsg * {line-height:1.22em;}#yiv8042069645 #yiv8042069645ygrp-mlmsg #yiv8042069645logo {padding-bottom:10px;}#yiv8042069645 #yiv8042069645ygrp-msg p a {font-family:Verdana;}#yiv8042069645 #yiv8042069645ygrp-msg p#yiv8042069645attach-count span {color:#1E66AE;font-weight:700;}#yiv8042069645 #yiv8042069645ygrp-reco #yiv8042069645reco-head {color:#ff7900;font-weight:700;}#yiv8042069645 #yiv8042069645ygrp-reco {margin-bottom:20px;padding:0px;}#yiv8042069645 #yiv8042069645ygrp-sponsor #yiv8042069645ov li a {font-size:130%;text-decoration:none;}#yiv8042069645 #yiv8042069645ygrp-sponsor #yiv8042069645ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv8042069645 #yiv8042069645ygrp-sponsor #yiv8042069645ov ul {margin:0;padding:0 0 0 8px;}#yiv8042069645 #yiv8042069645ygrp-text {font-family:Georgia;}#yiv8042069645 #yiv8042069645ygrp-text p {margin:0 0 1em 0;}#yiv8042069645 #yiv8042069645ygrp-text tt {font-size:120%;}#yiv8042069645 #yiv8042069645ygrp-vital ul li:last-child {border-right:none !important;}#yiv8042069645

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Bls: [belajar-excel] multi pencarian menggunakan " * " ???

Milis Belajar Excel mailing list
Lihat disini :
https://groups.yahoo.com/neo/groups/belajar-excel/conversations/messages/31920
Lalu buat sendiri contoh datanya atau search di postingan lawas milis tentang countif atau wildcard character

Regards,
Kid
Sent from my smart enough phone

> On Jul 7, 2017, at 08:57, Eira I Arie [hidden email] [belajar-excel] <[hidden email]> wrote:
>
> Yth. Rekan Be-Exceller
>
> Terkait rumus tersebut, bisakah memberikan contoh kasusnya ?
> Terima kasih
>
> Ari
>
>
> On Thursday, July 6, 2017 4:11 PM, "odong nando [hidden email] [belajar-excel]" <[hidden email]> wrote:
>
>
>  
> iya..ya boz kid,
>
> bagi yang males bwt range sendiri untuk range_kriteria nya, bisa di tulis juga kok ternyata di formulanya, klo masih di bawah 5 kriteria mungkin masih enak ditulis di formula, cuma klo dah lebih kayaknya enakan di buat range_kriteria sendiri dah...
>
> klo mo di tulis di formula nya sekaligus "range_kriterianya", pada formula yang boz kid utarakan bisa ditulis dengan :
>
> =SUMPRODUCT(COUNTIF(B5;{"aajj"\"bbjj"\"ccjj"}&"*"))
>
> jika regional seting kompi setempat english maka ubah karakter titik koma ";" dan backslash "\" menjadi koma ","
>
> NOTE : yang backslash saya lupa2 inget bener atau tidak, mohon untuk di koreksi bila memang salah...
>  
> br,
>
> odong nando
>
>
> Dari: "'Mr. Kid' [hidden email] [belajar-excel]" <[hidden email]>
> Kepada: BeExcel <[hidden email]>
> Dikirim: Kamis, 6 Juli 2017 15:44
> Judul: Re: [belajar-excel] multi pencarian menggunakan " * " ???
>
>  
> Kalau kriterianya ada disuatu range tertentu, bisa begini :
>
> [bukan array formula]
> =SumProduct( CountIF( range_data , range_kriteria & "*" ) )
>
> *** Jika fungsi SumProduct diganti dengan Sum, enter formula sebagai array formula.
> *** Jika regional setting komputer setempat adalah Indonesian, ganti karakter koma dengan titik koma
>
> Regards,
> Kid
>
>
>
>
>
> On Wed, Jul 5, 2017 at 10:50 AM, odong nando [hidden email] [belajar-excel] <[hidden email]> wrote:
>  
> hai shenly,
>
> mungkinkah yang anda maksud itu :
>
> =COUNTIF(B5;"aajj*")+COUNTIF( B5;"bbjj*")
>  
> br,
>
> odong nando
>
>
> Dari: "[hidden email] [belajar-excel]" <[hidden email] >
> Kepada: [hidden email]
> Dikirim: Sabtu, 1 Juli 2017 0:41
> Judul: [belajar-excel] multi pencarian menggunakan " * " ???
>
>  
> haloo master, gimana ya cara membuat multi pencarin menggunakan " * "
> saya sudah mencoba menggunakan rumus ini tapi ga bisa
> countif(B5,or("aajj"&"*"," bbjj"&"*")
>
> kalo menggunakan OR pasti salah, sementara yg mau saya cari lebh dari 1 kata
>
>
>
>
>
>
>
>
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

[belajar-excel] Re: multi pencarian menggunakan " * " ???

Milis Belajar Excel mailing list
aduh maaf maaf, baru online lagi akuh, kemarin pas akuh liat2 ga ada yg bls, kirain terlupakan hihihi
 terima kasih Mr.kid , Mr arie, dan Mr odong yang sudah membalas pertanyaan sayah, tapi maaf master, sayah masih belum mengerti dengan penjelasnnya hihihi, mungkin lebih jelasnya seperti ini,
 sayah mempunyai (5 master kode), 2 diataranya  5karakter , dan sisanya 4karakter huruf,
 contoh kodenya :
 xo011-(usernasabah)
 xo012-(usernasabah)
 xo01-(usernasabah)
 xo02-(usernasabah)
 xo03-(usernasabah)
 

 kodenya tercampur dengan user2 nasabah, jadi pertama disni sya mau memisahkn master kode.
 biasa sayah cuma menggunakan LEFT( dd , 4)  sajah, ketka jumlah karakternya sama smua,
 kemarin sempat coba2 pake, COUNTIF(B5,or("mastercod"&"*","mastercod&"*") tapih tidak bisah, dah akhirnya saya menggunakan IF(LEF( , ) = (masterkode) , dan hasilnya malah kaya sajak puisi, panjg bgt -_- ,
 mungkin disni para master bisa memerikan salah satu rumusan yg ga gtu panjng ketimpng yg syah gunakah hehe.....
 


Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [belajar-excel] Re: multi pencarian menggunakan " * " ???

Milis Belajar Excel mailing list
Hai Shenly,Jika yang diinginkan mengambil karakter di depan tanda "-" maka bisa coba pakai function :=Left(cellnya, find(tanda "-")-1).
Salam,HK

Sent from Yahoo Mail on Android
 
  On Fri, Jul 14, 2017 at 21:37, [hidden email] [belajar-excel]<[hidden email]> wrote:      
aduh maaf maaf, baru online lagi akuh, kemarin pas akuh liat2 ga ada yg bls, kirain terlupakan hihihi

terima kasih Mr.kid , Mr arie, dan Mr odong yang sudah membalas pertanyaan sayah, tapi maaf master, sayah masih belum mengerti dengan penjelasnnya hihihi, mungkin lebih jelasnya seperti ini,sayah mempunyai (5 master kode), 2 diataranya  5karakter , dan sisanya 4karakter huruf,contoh kodenya :xo011-(usernasabah)xo012-(usernasabah)xo01-(usernasabah)xo02-(usernasabah)xo03-(usernasabah)
kodenya tercampur dengan user2 nasabah, jadi pertama disni sya mau memisahkn master kode.biasa sayah cuma menggunakan LEFT( dd , 4)  sajah, ketka jumlah karakternya sama smua, kemarin sempat coba2 pake, COUNTIF(B5,or("mastercod"&"*","mastercod&"*") tapih tidak bisah, dah akhirnya saya menggunakan IF(LEF( , ) = (masterkode) , dan hasilnya malah kaya sajak puisi, panjg bgt -_- , mungkin disni para master bisa memerikan salah satu rumusan yg ga gtu panjng ketimpng yg syah gunakah hehe..... 
  #yiv3309008416 #yiv3309008416 -- #yiv3309008416ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv3309008416 #yiv3309008416ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv3309008416 #yiv3309008416ygrp-mkp #yiv3309008416hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv3309008416 #yiv3309008416ygrp-mkp #yiv3309008416ads {margin-bottom:10px;}#yiv3309008416 #yiv3309008416ygrp-mkp .yiv3309008416ad {padding:0 0;}#yiv3309008416 #yiv3309008416ygrp-mkp .yiv3309008416ad p {margin:0;}#yiv3309008416 #yiv3309008416ygrp-mkp .yiv3309008416ad a {color:#0000ff;text-decoration:none;}#yiv3309008416 #yiv3309008416ygrp-sponsor #yiv3309008416ygrp-lc {font-family:Arial;}#yiv3309008416 #yiv3309008416ygrp-sponsor #yiv3309008416ygrp-lc #yiv3309008416hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv3309008416 #yiv3309008416ygrp-sponsor #yiv3309008416ygrp-lc .yiv3309008416ad {margin-bottom:10px;padding:0 0;}#yiv3309008416 #yiv3309008416actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv3309008416 #yiv3309008416activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv3309008416 #yiv3309008416activity span {font-weight:700;}#yiv3309008416 #yiv3309008416activity span:first-child {text-transform:uppercase;}#yiv3309008416 #yiv3309008416activity span a {color:#5085b6;text-decoration:none;}#yiv3309008416 #yiv3309008416activity span span {color:#ff7900;}#yiv3309008416 #yiv3309008416activity span .yiv3309008416underline {text-decoration:underline;}#yiv3309008416 .yiv3309008416attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv3309008416 .yiv3309008416attach div a {text-decoration:none;}#yiv3309008416 .yiv3309008416attach img {border:none;padding-right:5px;}#yiv3309008416 .yiv3309008416attach label {display:block;margin-bottom:5px;}#yiv3309008416 .yiv3309008416attach label a {text-decoration:none;}#yiv3309008416 blockquote {margin:0 0 0 4px;}#yiv3309008416 .yiv3309008416bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv3309008416 .yiv3309008416bold a {text-decoration:none;}#yiv3309008416 dd.yiv3309008416last p a {font-family:Verdana;font-weight:700;}#yiv3309008416 dd.yiv3309008416last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv3309008416 dd.yiv3309008416last p span.yiv3309008416yshortcuts {margin-right:0;}#yiv3309008416 div.yiv3309008416attach-table div div a {text-decoration:none;}#yiv3309008416 div.yiv3309008416attach-table {width:400px;}#yiv3309008416 div.yiv3309008416file-title a, #yiv3309008416 div.yiv3309008416file-title a:active, #yiv3309008416 div.yiv3309008416file-title a:hover, #yiv3309008416 div.yiv3309008416file-title a:visited {text-decoration:none;}#yiv3309008416 div.yiv3309008416photo-title a, #yiv3309008416 div.yiv3309008416photo-title a:active, #yiv3309008416 div.yiv3309008416photo-title a:hover, #yiv3309008416 div.yiv3309008416photo-title a:visited {text-decoration:none;}#yiv3309008416 div#yiv3309008416ygrp-mlmsg #yiv3309008416ygrp-msg p a span.yiv3309008416yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv3309008416 .yiv3309008416green {color:#628c2a;}#yiv3309008416 .yiv3309008416MsoNormal {margin:0 0 0 0;}#yiv3309008416 o {font-size:0;}#yiv3309008416 #yiv3309008416photos div {float:left;width:72px;}#yiv3309008416 #yiv3309008416photos div div {border:1px solid #666666;min-height:62px;overflow:hidden;width:62px;}#yiv3309008416 #yiv3309008416photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv3309008416 #yiv3309008416reco-category {font-size:77%;}#yiv3309008416 #yiv3309008416reco-desc {font-size:77%;}#yiv3309008416 .yiv3309008416replbq {margin:4px;}#yiv3309008416 #yiv3309008416ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv3309008416 #yiv3309008416ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv3309008416 #yiv3309008416ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv3309008416 #yiv3309008416ygrp-mlmsg select, #yiv3309008416 input, #yiv3309008416 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv3309008416 #yiv3309008416ygrp-mlmsg pre, #yiv3309008416 code {font:115% monospace;}#yiv3309008416 #yiv3309008416ygrp-mlmsg * {line-height:1.22em;}#yiv3309008416 #yiv3309008416ygrp-mlmsg #yiv3309008416logo {padding-bottom:10px;}#yiv3309008416 #yiv3309008416ygrp-msg p a {font-family:Verdana;}#yiv3309008416 #yiv3309008416ygrp-msg p#yiv3309008416attach-count span {color:#1E66AE;font-weight:700;}#yiv3309008416 #yiv3309008416ygrp-reco #yiv3309008416reco-head {color:#ff7900;font-weight:700;}#yiv3309008416 #yiv3309008416ygrp-reco {margin-bottom:20px;padding:0px;}#yiv3309008416 #yiv3309008416ygrp-sponsor #yiv3309008416ov li a {font-size:130%;text-decoration:none;}#yiv3309008416 #yiv3309008416ygrp-sponsor #yiv3309008416ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv3309008416 #yiv3309008416ygrp-sponsor #yiv3309008416ov ul {margin:0;padding:0 0 0 8px;}#yiv3309008416 #yiv3309008416ygrp-text {font-family:Georgia;}#yiv3309008416 #yiv3309008416ygrp-text p {margin:0 0 1em 0;}#yiv3309008416 #yiv3309008416ygrp-text tt {font-size:120%;}#yiv3309008416 #yiv3309008416ygrp-vital ul li:last-child {border-right:none !important;}#yiv3309008416  
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

[belajar-excel] Re: multi pencarian menggunakan " * " ???

Milis Belajar Excel mailing list
terima kasih bpk hendrik, sudah menambah pengethuan sya, tapi maaf bpk hendrik, tand "-" cuma buat pemisah di penjelasan sya nya aja hehehe, kode yg sebenrnya tidak menggunakan "-" , mungkin bpk tau penggunakan rumus lain yg lebih ringkas dibanding dgn yg sya buat .....
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [belajar-excel] Re: multi pencarian menggunakan " * " ???

Milis Belajar Excel mailing list
Hai Shenly,

File terlampir adalah contoh.
Ketahui dulu seluruh pola data teks.
Misal :
a. maksimal jumlah karakter kode adalah 6 karakter
b. strukturnya adalah id[sesuatu]user, dengan [sesuatu] adalah sejumlah 0,
1, atau 2 karakter selain abjad dan angka
c. struktur id adalah diakhiri dengan angka

Jadi, pola data teks berbeda dengan ragam data teks.
Contoh ragam data teks yang ada di kasus ini :
1. ada kode yang terdiri dari 5 karakter atau 4 karakter
2. setelah kode ada yang menggunakan karakter '-', spasi, tab, koma, enter
sebelum teks user
3. ada juga setelah kode langsung bersambung dengan user
4. jumlah karakter data teks tidak pasti, terkadang sampai 255 karakter
tapi ada yang hanya 10 karakter bahkan kurang

Dari ragam teks yang diketahui akan diperoleh pola data teks.
Formula pengolahan data disusun berdasar pola data dan BUKAN berdasar ragam
data.

Regards,
Kid


2017-07-15 12:20 GMT+07:00 [hidden email] [belajar-excel] <
[hidden email]>:

>
>
> terima kasih bpk hendrik, sudah menambah pengethuan sya, tapi maaf bpk
> hendrik, tand "-" cuma buat pemisah di penjelasan sya nya aja hehehe, kode
> yg sebenrnya tidak menggunakan "-" , mungkin bpk tau penggunakan rumus lain
> yg lebih ringkas dibanding dgn yg sya buat .....
>
>

re-mengolah_teks_harus_tahu_pola_seluruh_data_teks_tersebut.xlsx (14K) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [belajar-excel] Re: multi pencarian menggunakan " * " ???

Milis Belajar Excel mailing list
hehe maaf mr.kid, ini contohnya sya lampirkan, dan untuk kasusnya saya tambh mr. karena seteleh membaca penjelasan mr. saya baru terpikir, kemungkinan jumlah karakter bisa berubah2

example 157.xlsx (11K) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

[belajar-excel] Re: multi pencarian menggunakan " * " ???

Milis Belajar Excel mailing list
aduh maaf mr, ternyata mr kid udah kasih file contohnya yah, hehe, tidak terlihat,  terima kasih byk mr kid, sya pelajari ddulu file nya
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Bls: [belajar-excel] Re: multi pencarian menggunakan " * " ???

Milis Belajar Excel mailing list
hai shenly,
terlampir cuma 1 dari beberapa solusi yang ada... br,

odong nando


      Dari: "[hidden email] [belajar-excel]" <[hidden email]>
 Kepada: [hidden email]
 Dikirim: Minggu, 16 Juli 2017 16:03
 Judul: [belajar-excel] Re: multi pencarian menggunakan " * " ???
   
    aduh maaf mr, ternyata mr kid udah kasih file contohnya yah, hehe, tidak terlihat, terima kasih byk mr kid, sya pelajari ddulu file nya  #yiv7635950398 #yiv7635950398 -- #yiv7635950398ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv7635950398 #yiv7635950398ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv7635950398 #yiv7635950398ygrp-mkp #yiv7635950398hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv7635950398 #yiv7635950398ygrp-mkp #yiv7635950398ads {margin-bottom:10px;}#yiv7635950398 #yiv7635950398ygrp-mkp .yiv7635950398ad {padding:0 0;}#yiv7635950398 #yiv7635950398ygrp-mkp .yiv7635950398ad p {margin:0;}#yiv7635950398 #yiv7635950398ygrp-mkp .yiv7635950398ad a {color:#0000ff;text-decoration:none;}#yiv7635950398 #yiv7635950398ygrp-sponsor #yiv7635950398ygrp-lc {font-family:Arial;}#yiv7635950398 #yiv7635950398ygrp-sponsor #yiv7635950398ygrp-lc #yiv7635950398hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv7635950398 #yiv7635950398ygrp-sponsor #yiv7635950398ygrp-lc .yiv7635950398ad {margin-bottom:10px;padding:0 0;}#yiv7635950398 #yiv7635950398actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv7635950398 #yiv7635950398activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv7635950398 #yiv7635950398activity span {font-weight:700;}#yiv7635950398 #yiv7635950398activity span:first-child {text-transform:uppercase;}#yiv7635950398 #yiv7635950398activity span a {color:#5085b6;text-decoration:none;}#yiv7635950398 #yiv7635950398activity span span {color:#ff7900;}#yiv7635950398 #yiv7635950398activity span .yiv7635950398underline {text-decoration:underline;}#yiv7635950398 .yiv7635950398attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv7635950398 .yiv7635950398attach div a {text-decoration:none;}#yiv7635950398 .yiv7635950398attach img {border:none;padding-right:5px;}#yiv7635950398 .yiv7635950398attach label {display:block;margin-bottom:5px;}#yiv7635950398 .yiv7635950398attach label a {text-decoration:none;}#yiv7635950398 blockquote {margin:0 0 0 4px;}#yiv7635950398 .yiv7635950398bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv7635950398 .yiv7635950398bold a {text-decoration:none;}#yiv7635950398 dd.yiv7635950398last p a {font-family:Verdana;font-weight:700;}#yiv7635950398 dd.yiv7635950398last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv7635950398 dd.yiv7635950398last p span.yiv7635950398yshortcuts {margin-right:0;}#yiv7635950398 div.yiv7635950398attach-table div div a {text-decoration:none;}#yiv7635950398 div.yiv7635950398attach-table {width:400px;}#yiv7635950398 div.yiv7635950398file-title a, #yiv7635950398 div.yiv7635950398file-title a:active, #yiv7635950398 div.yiv7635950398file-title a:hover, #yiv7635950398 div.yiv7635950398file-title a:visited {text-decoration:none;}#yiv7635950398 div.yiv7635950398photo-title a, #yiv7635950398 div.yiv7635950398photo-title a:active, #yiv7635950398 div.yiv7635950398photo-title a:hover, #yiv7635950398 div.yiv7635950398photo-title a:visited {text-decoration:none;}#yiv7635950398 div#yiv7635950398ygrp-mlmsg #yiv7635950398ygrp-msg p a span.yiv7635950398yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv7635950398 .yiv7635950398green {color:#628c2a;}#yiv7635950398 .yiv7635950398MsoNormal {margin:0 0 0 0;}#yiv7635950398 o {font-size:0;}#yiv7635950398 #yiv7635950398photos div {float:left;width:72px;}#yiv7635950398 #yiv7635950398photos div div {border:1px solid #666666;min-height:62px;overflow:hidden;width:62px;}#yiv7635950398 #yiv7635950398photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv7635950398 #yiv7635950398reco-category {font-size:77%;}#yiv7635950398 #yiv7635950398reco-desc {font-size:77%;}#yiv7635950398 .yiv7635950398replbq {margin:4px;}#yiv7635950398 #yiv7635950398ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv7635950398 #yiv7635950398ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv7635950398 #yiv7635950398ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv7635950398 #yiv7635950398ygrp-mlmsg select, #yiv7635950398 input, #yiv7635950398 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv7635950398 #yiv7635950398ygrp-mlmsg pre, #yiv7635950398 code {font:115% monospace;}#yiv7635950398 #yiv7635950398ygrp-mlmsg * {line-height:1.22em;}#yiv7635950398 #yiv7635950398ygrp-mlmsg #yiv7635950398logo {padding-bottom:10px;}#yiv7635950398 #yiv7635950398ygrp-msg p a {font-family:Verdana;}#yiv7635950398 #yiv7635950398ygrp-msg p#yiv7635950398attach-count span {color:#1E66AE;font-weight:700;}#yiv7635950398 #yiv7635950398ygrp-reco #yiv7635950398reco-head {color:#ff7900;font-weight:700;}#yiv7635950398 #yiv7635950398ygrp-reco {margin-bottom:20px;padding:0px;}#yiv7635950398 #yiv7635950398ygrp-sponsor #yiv7635950398ov li a {font-size:130%;text-decoration:none;}#yiv7635950398 #yiv7635950398ygrp-sponsor #yiv7635950398ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv7635950398 #yiv7635950398ygrp-sponsor #yiv7635950398ov ul {margin:0;padding:0 0 0 8px;}#yiv7635950398 #yiv7635950398ygrp-text {font-family:Georgia;}#yiv7635950398 #yiv7635950398ygrp-text p {margin:0 0 1em 0;}#yiv7635950398 #yiv7635950398ygrp-text tt {font-size:120%;}#yiv7635950398 #yiv7635950398ygrp-vital ul li:last-child {border-right:none !important;}#yiv7635950398

   

example 157_od.xlsx (14K) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

[belajar-excel] Re: multi pencarian menggunakan " * " ???

Milis Belajar Excel mailing list
ya ini sesuai yg saya mau hehehe, terima kasih byk pk odong ^.^
Loading...