[milis-belajar-excel] Memilih secara otomatis dgn kriteria yg bnyk (ada horizontal dan vertkal)

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

[milis-belajar-excel] Memilih secara otomatis dgn kriteria yg bnyk (ada horizontal dan vertkal)

i jayadi

Dear rekan rekan milis,
Mohon dibantu untuk case berikut dimana Saya ingin mencari hasil secara otomatis memilih dari sumber data dengan banyak kriteria (horizaonyal dan vertikal) sehingga tidak manual satu persatu.
lengkapnya terlampir contoh file yg dimaksud sekiranya sedulur disini bisa membantu ilmunya..terimakasih

--
You received this message because you are subscribed to the Google Groups "milis Belajar-Excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/1f465089-dd74-4b32-a86c-1e8b811a3b7e%40googlegroups.com.
Reply | Threaded
Open this post in threaded view
|

Re: [milis-belajar-excel] Memilih secara otomatis dgn kriteria yg bnyk (ada horizontal dan vertkal)

i jayadi


On Mon, Mar 30, 2020 at 6:08 PM i jayadi <[hidden email]> wrote:

Dear rekan rekan milis,
Mohon dibantu untuk case berikut dimana Saya ingin mencari hasil secara otomatis memilih dari sumber data dengan banyak kriteria (horizaonyal dan vertikal) sehingga tidak manual satu persatu.
lengkapnya terlampir contoh file yg dimaksud sekiranya sedulur disini bisa membantu ilmunya..terimakasih

--
You received this message because you are subscribed to the Google Groups "milis Belajar-Excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/1f465089-dd74-4b32-a86c-1e8b811a3b7e%40googlegroups.com.

Maaf kelupaan attachement filenya, tulung pencerahaannya ya dear para master

--

Ijay

--
You received this message because you are subscribed to the Google Groups "milis Belajar-Excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/CADRUV1r0GdVRp0keWLJGxM%2BsZRSHAb0d4VXAbY84%3D2zt7GRySg%40mail.gmail.com.

Automatis milih.xlsx (56K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: [milis-belajar-excel] Memilih secara otomatis dgn kriteria yg bnyk (ada horizontal dan vertkal)

Sandy-2

Saya coba bantu ya...

Sandy


On Monday, March 30, 2020, 10:36:30 PM GMT+8, i jayadi <[hidden email]> wrote:




On Mon, Mar 30, 2020 at 6:08 PM i jayadi <[hidden email]> wrote:

Dear rekan rekan milis,
Mohon dibantu untuk case berikut dimana Saya ingin mencari hasil secara otomatis memilih dari sumber data dengan banyak kriteria (horizaonyal dan vertikal) sehingga tidak manual satu persatu.
lengkapnya terlampir contoh file yg dimaksud sekiranya sedulur disini bisa membantu ilmunya..terimakasih

--
You received this message because you are subscribed to the Google Groups "milis Belajar-Excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/1f465089-dd74-4b32-a86c-1e8b811a3b7e%40googlegroups.com.

Maaf kelupaan attachement filenya, tulung pencerahaannya ya dear para master

--

Ijay

--
You received this message because you are subscribed to the Google Groups "milis Belajar-Excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/CADRUV1r0GdVRp0keWLJGxM%2BsZRSHAb0d4VXAbY84%3D2zt7GRySg%40mail.gmail.com.

--
You received this message because you are subscribed to the Google Groups "milis Belajar-Excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/471492273.1413780.1585607885071%40mail.yahoo.com.

Automatis milih - jawab.xlsx (57K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: [milis-belajar-excel] Memilih secara otomatis dgn kriteria yg bnyk (ada horizontal dan vertkal)

Bagus-2
In reply to this post by i jayadi
Dear Jayadi;

Coba formula berikut:
=SUMPRODUCT((C4='Data base'!$C$1:$AC$1)*(D4='Data base'!$A$4:$A$68)*(E4='Data base'!$C$2:$AC$2)*(F4='Data base'!$B$4:$B$68)*(G4='Data base'!$C$3:$AC$3)*'Data base'!$C$4:$AC$68)

atau
=INDEX('Data base'!$C$4:$AC$68,MATCH(D4&F4,'Data base'!$A$4:$A$68&'Data base'!$B$4:$B$68,0),MATCH(C4&E4&G4,'Data base'!$C$1:$AC$1&'Data base'!$C$2:$AC$2&'Data base'!$C$3:$AC$3,0))

Copy kebawah.

Semoga sesuai harapan

Wassalam
   Bagus


Pada tanggal Sen, 30 Mar 2020 pukul 21.36 i jayadi <[hidden email]> menulis:


On Mon, Mar 30, 2020 at 6:08 PM i jayadi <[hidden email]> wrote:

Dear rekan rekan milis,
Mohon dibantu untuk case berikut dimana Saya ingin mencari hasil secara otomatis memilih dari sumber data dengan banyak kriteria (horizaonyal dan vertikal) sehingga tidak manual satu persatu.
lengkapnya terlampir contoh file yg dimaksud sekiranya sedulur disini bisa membantu ilmunya..terimakasih

--
You received this message because you are subscribed to the Google Groups "milis Belajar-Excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/1f465089-dd74-4b32-a86c-1e8b811a3b7e%40googlegroups.com.

Maaf kelupaan attachement filenya, tulung pencerahaannya ya dear para master

--

Ijay

--
You received this message because you are subscribed to the Google Groups "milis Belajar-Excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/CADRUV1r0GdVRp0keWLJGxM%2BsZRSHAb0d4VXAbY84%3D2zt7GRySg%40mail.gmail.com.

--
You received this message because you are subscribed to the Google Groups "milis Belajar-Excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/CAEPgWdkJQCU-pagn44oc9Qg_x-4MAExyGkOuRgsMhgknDS%2BApw%40mail.gmail.com.
Reply | Threaded
Open this post in threaded view
|

Re: [milis-belajar-excel] Memilih secara otomatis dgn kriteria yg bnyk (ada horizontal dan vertkal)

i jayadi
Formula dari pak Bagus dan pak Sandy bekerja dengan baik sesuai harapan, terima kasih bantuannya ya..cuma yg menarik dari pilihan formula ada dikit perbedaan dan kebetulan sy tidak paham☺

dari pak Bagus
=INDEX('Data base'!$C$4:$AC$68,MATCH(D4&F4,'Data base'!$A$4:$A$68&'Data base'!$B$4:$B$68,0),MATCH(C4&E4&G4,'Data base'!$C$1:$AC$1&'Data base'!$C$2:$AC$2&'Data base'!$C$3:$AC$3,0))


















dari pak Sandy








=INDEX('Data base'!$C$4:$AC$68,MATCH(1,($D4='Data base'!$A$4:$A$68)*($F4='Data base'!$B$4:$B$68),0),MATCH(1,($C4='Data base'!$C$1:$AC$1)*($E4='Data base'!$C$2:$AC$2)*($G4='Data base'!$C$3:$AC$3),0))

dgn cara berbeda namun mirip ke 2 nya adalah solusi, cuma cara mengartikan ke 2 formula ini dalam pemahaman bahasa bagaimana yah..

salam,
jayadi



On Tue, Mar 31, 2020 at 8:33 AM Bagus <[hidden email]> wrote:
Dear Jayadi;

Coba formula berikut:
=SUMPRODUCT((C4='Data base'!$C$1:$AC$1)*(D4='Data base'!$A$4:$A$68)*(E4='Data base'!$C$2:$AC$2)*(F4='Data base'!$B$4:$B$68)*(G4='Data base'!$C$3:$AC$3)*'Data base'!$C$4:$AC$68)

atau
=INDEX('Data base'!$C$4:$AC$68,MATCH(D4&F4,'Data base'!$A$4:$A$68&'Data base'!$B$4:$B$68,0),MATCH(C4&E4&G4,'Data base'!$C$1:$AC$1&'Data base'!$C$2:$AC$2&'Data base'!$C$3:$AC$3,0))

Copy kebawah.

Semoga sesuai harapan

Wassalam
   Bagus


Pada tanggal Sen, 30 Mar 2020 pukul 21.36 i jayadi <[hidden email]> menulis:


On Mon, Mar 30, 2020 at 6:08 PM i jayadi <[hidden email]> wrote:

Dear rekan rekan milis,
Mohon dibantu untuk case berikut dimana Saya ingin mencari hasil secara otomatis memilih dari sumber data dengan banyak kriteria (horizaonyal dan vertikal) sehingga tidak manual satu persatu.
lengkapnya terlampir contoh file yg dimaksud sekiranya sedulur disini bisa membantu ilmunya..terimakasih

--
You received this message because you are subscribed to the Google Groups "milis Belajar-Excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/1f465089-dd74-4b32-a86c-1e8b811a3b7e%40googlegroups.com.

Maaf kelupaan attachement filenya, tulung pencerahaannya ya dear para master

--

Ijay

--
You received this message because you are subscribed to the Google Groups "milis Belajar-Excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/CADRUV1r0GdVRp0keWLJGxM%2BsZRSHAb0d4VXAbY84%3D2zt7GRySg%40mail.gmail.com.

--
You received this message because you are subscribed to the Google Groups "milis Belajar-Excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/CAEPgWdkJQCU-pagn44oc9Qg_x-4MAExyGkOuRgsMhgknDS%2BApw%40mail.gmail.com.


--

Ijay

--
You received this message because you are subscribed to the Google Groups "milis Belajar-Excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/CADRUV1pR1DT4-RGV-kuyagO3HBHpGopYu-U3ymsMhgqbSEDUzw%40mail.gmail.com.
Reply | Threaded
Open this post in threaded view
|

Re: [milis-belajar-excel] Memilih secara otomatis dgn kriteria yg bnyk (ada horizontal dan vertkal)

Bagus-2
Dear jayadi;

Fungsi Index adalah sbb:
=INDEX(array,row_num,[column_num])

INDEX('Data base'!$C$4:$AC$68 ==> ini adalah array (range) yang mau dicari nilainya
MATCH(D4&F4,'Data base'!$A$4:$A$68&'Data base'!$B$4:$B$68,0) ==> ini adalah baris keberapa dari nilai yang mau dicari
MATCH(C4&E4&G4,'Data base'!$C$1:$AC$1&'Data base'!$C$2:$AC$2&'Data base'!$C$3:$AC$3,0))  ==> ini adalah kolom keberapa dari nilai yang mau dicari.   

Kertika baris dan kolom sudah ketemu, maka akan merujuk pada array (range) tempat nilai itu berada.

Demikian semoga bisa dipahami.

Wassalam
   Bagus


Pada tanggal Sel, 31 Mar 2020 pukul 09.35 i jayadi <[hidden email]> menulis:
Formula dari pak Bagus dan pak Sandy bekerja dengan baik sesuai harapan, terima kasih bantuannya ya..cuma yg menarik dari pilihan formula ada dikit perbedaan dan kebetulan sy tidak paham☺

dari pak Bagus
=INDEX('Data base'!$C$4:$AC$68,MATCH(D4&F4,'Data base'!$A$4:$A$68&'Data base'!$B$4:$B$68,0),MATCH(C4&E4&G4,'Data base'!$C$1:$AC$1&'Data base'!$C$2:$AC$2&'Data base'!$C$3:$AC$3,0))


















dari pak Sandy








=INDEX('Data base'!$C$4:$AC$68,MATCH(1,($D4='Data base'!$A$4:$A$68)*($F4='Data base'!$B$4:$B$68),0),MATCH(1,($C4='Data base'!$C$1:$AC$1)*($E4='Data base'!$C$2:$AC$2)*($G4='Data base'!$C$3:$AC$3),0))

dgn cara berbeda namun mirip ke 2 nya adalah solusi, cuma cara mengartikan ke 2 formula ini dalam pemahaman bahasa bagaimana yah..

salam,
jayadi



On Tue, Mar 31, 2020 at 8:33 AM Bagus <[hidden email]> wrote:
Dear Jayadi;

Coba formula berikut:
=SUMPRODUCT((C4='Data base'!$C$1:$AC$1)*(D4='Data base'!$A$4:$A$68)*(E4='Data base'!$C$2:$AC$2)*(F4='Data base'!$B$4:$B$68)*(G4='Data base'!$C$3:$AC$3)*'Data base'!$C$4:$AC$68)

atau
=INDEX('Data base'!$C$4:$AC$68,MATCH(D4&F4,'Data base'!$A$4:$A$68&'Data base'!$B$4:$B$68,0),MATCH(C4&E4&G4,'Data base'!$C$1:$AC$1&'Data base'!$C$2:$AC$2&'Data base'!$C$3:$AC$3,0))

Copy kebawah.

Semoga sesuai harapan

Wassalam
   Bagus


Pada tanggal Sen, 30 Mar 2020 pukul 21.36 i jayadi <[hidden email]> menulis:


On Mon, Mar 30, 2020 at 6:08 PM i jayadi <[hidden email]> wrote:

Dear rekan rekan milis,
Mohon dibantu untuk case berikut dimana Saya ingin mencari hasil secara otomatis memilih dari sumber data dengan banyak kriteria (horizaonyal dan vertikal) sehingga tidak manual satu persatu.
lengkapnya terlampir contoh file yg dimaksud sekiranya sedulur disini bisa membantu ilmunya..terimakasih

--
You received this message because you are subscribed to the Google Groups "milis Belajar-Excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/1f465089-dd74-4b32-a86c-1e8b811a3b7e%40googlegroups.com.

Maaf kelupaan attachement filenya, tulung pencerahaannya ya dear para master

--

Ijay

--
You received this message because you are subscribed to the Google Groups "milis Belajar-Excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/CADRUV1r0GdVRp0keWLJGxM%2BsZRSHAb0d4VXAbY84%3D2zt7GRySg%40mail.gmail.com.

--
You received this message because you are subscribed to the Google Groups "milis Belajar-Excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/CAEPgWdkJQCU-pagn44oc9Qg_x-4MAExyGkOuRgsMhgknDS%2BApw%40mail.gmail.com.


--

Ijay

--
You received this message because you are subscribed to the Google Groups "milis Belajar-Excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/CAEPgWd%3DaHJUad5Swecmt2yMhX_4rezncDQE%3DceQ%3Dp3NpjV2r%2BQ%40mail.gmail.com.
Reply | Threaded
Open this post in threaded view
|

Re: [milis-belajar-excel] Memilih secara otomatis dgn kriteria yg bnyk (ada horizontal dan vertkal)

i jayadi
Pak Bagus,
Penjelasannya bisa dipahami terimakasih atas bantuan nya utk solve excel sy....

On Tue, Mar 31, 2020 at 10:10 AM Bagus <[hidden email]> wrote:
Dear jayadi;

Fungsi Index adalah sbb:
=INDEX(array,row_num,[column_num])

INDEX('Data base'!$C$4:$AC$68 ==> ini adalah array (range) yang mau dicari nilainya
MATCH(D4&F4,'Data base'!$A$4:$A$68&'Data base'!$B$4:$B$68,0) ==> ini adalah baris keberapa dari nilai yang mau dicari
MATCH(C4&E4&G4,'Data base'!$C$1:$AC$1&'Data base'!$C$2:$AC$2&'Data base'!$C$3:$AC$3,0))  ==> ini adalah kolom keberapa dari nilai yang mau dicari.   

Kertika baris dan kolom sudah ketemu, maka akan merujuk pada array (range) tempat nilai itu berada.

Demikian semoga bisa dipahami.

Wassalam
   Bagus


Pada tanggal Sel, 31 Mar 2020 pukul 09.35 i jayadi <[hidden email]> menulis:
Formula dari pak Bagus dan pak Sandy bekerja dengan baik sesuai harapan, terima kasih bantuannya ya..cuma yg menarik dari pilihan formula ada dikit perbedaan dan kebetulan sy tidak paham☺

dari pak Bagus
=INDEX('Data base'!$C$4:$AC$68,MATCH(D4&F4,'Data base'!$A$4:$A$68&'Data base'!$B$4:$B$68,0),MATCH(C4&E4&G4,'Data base'!$C$1:$AC$1&'Data base'!$C$2:$AC$2&'Data base'!$C$3:$AC$3,0))


















dari pak Sandy








=INDEX('Data base'!$C$4:$AC$68,MATCH(1,($D4='Data base'!$A$4:$A$68)*($F4='Data base'!$B$4:$B$68),0),MATCH(1,($C4='Data base'!$C$1:$AC$1)*($E4='Data base'!$C$2:$AC$2)*($G4='Data base'!$C$3:$AC$3),0))

dgn cara berbeda namun mirip ke 2 nya adalah solusi, cuma cara mengartikan ke 2 formula ini dalam pemahaman bahasa bagaimana yah..

salam,
jayadi



On Tue, Mar 31, 2020 at 8:33 AM Bagus <[hidden email]> wrote:
Dear Jayadi;

Coba formula berikut:
=SUMPRODUCT((C4='Data base'!$C$1:$AC$1)*(D4='Data base'!$A$4:$A$68)*(E4='Data base'!$C$2:$AC$2)*(F4='Data base'!$B$4:$B$68)*(G4='Data base'!$C$3:$AC$3)*'Data base'!$C$4:$AC$68)

atau
=INDEX('Data base'!$C$4:$AC$68,MATCH(D4&F4,'Data base'!$A$4:$A$68&'Data base'!$B$4:$B$68,0),MATCH(C4&E4&G4,'Data base'!$C$1:$AC$1&'Data base'!$C$2:$AC$2&'Data base'!$C$3:$AC$3,0))

Copy kebawah.

Semoga sesuai harapan

Wassalam
   Bagus


Pada tanggal Sen, 30 Mar 2020 pukul 21.36 i jayadi <[hidden email]> menulis:


On Mon, Mar 30, 2020 at 6:08 PM i jayadi <[hidden email]> wrote:

Dear rekan rekan milis,
Mohon dibantu untuk case berikut dimana Saya ingin mencari hasil secara otomatis memilih dari sumber data dengan banyak kriteria (horizaonyal dan vertikal) sehingga tidak manual satu persatu.
lengkapnya terlampir contoh file yg dimaksud sekiranya sedulur disini bisa membantu ilmunya..terimakasih

--
You received this message because you are subscribed to the Google Groups "milis Belajar-Excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/1f465089-dd74-4b32-a86c-1e8b811a3b7e%40googlegroups.com.

Maaf kelupaan attachement filenya, tulung pencerahaannya ya dear para master

--

Ijay

--
You received this message because you are subscribed to the Google Groups "milis Belajar-Excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/CADRUV1r0GdVRp0keWLJGxM%2BsZRSHAb0d4VXAbY84%3D2zt7GRySg%40mail.gmail.com.

--
You received this message because you are subscribed to the Google Groups "milis Belajar-Excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/CAEPgWdkJQCU-pagn44oc9Qg_x-4MAExyGkOuRgsMhgknDS%2BApw%40mail.gmail.com.


--

Ijay

--
You received this message because you are subscribed to the Google Groups "milis Belajar-Excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/CAEPgWd%3DaHJUad5Swecmt2yMhX_4rezncDQE%3DceQ%3Dp3NpjV2r%2BQ%40mail.gmail.com.


--

Ijay

--
You received this message because you are subscribed to the Google Groups "milis Belajar-Excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/CADRUV1ozY69oCO%2BSZ%3D-nNRGss7mofrOQx-HeyzwzgBJDqoojLA%40mail.gmail.com.