Fungsi If Dengan Beberapa Syarat

  1. 2 minggu lalu

    Dear Agan-agan, mau minta bantuan, gimana caranya kalo isi di "status report adira 1 jan - 30 sept 18 - 041018_tes" sheet data kolom AF "HPP" dengan mengambil data dari "3. Master HPP 2018 - (reguler) finas_tes", dengan kondisi

    • jika kolom K "Moda" air mengambil dari sheet "AIR", begitupun jika kolom K berisi land maka mengambil dari sheet "LAND".
    • lalu disesuaikan dengan kolom "City"
    • lalu jika kolom M "weight" ...angka sekian... diambil dari range yang ada di kolom T,U,V,W,X,Y (TOTAL HPP VIA AIR (SMU + HANDLING)
    • lalu harga tersebut dikali oleh kolom M "weight"

    Contoh :

    Kolom K (Moda) : Air
    Kolom F (Dest_Name) : Pekanbaru
    Kolom M (Weight) : 24

    maka hasilnya yang ada di kolom AF (HPP) : 7304 x 24 = 175.296
    diambil dari sheet Air, 24 masuk kategori 21-50 Kg Kolom W

    Tolong dibantu agan-agan untuk aplikasi di excelnya.

    Terima kasih

  2. Caton

    Okt 5 Terverifikasi Indonesia + 12.008 Poin
    Di sunting 2 minggu lalu oleh Caton

    @Dhimasanggoro ...

    Coba diperiksa file terlampir, mungkin dapat disesuaikan dengan yang diinginkan. Pelajari dan sesuaikan formula pada Name Manager, terutama nama dengan prefiks Master, karena nama range tersebut mengambil data eksternal yang nantinya perlu disesuaikan dengan lokasi dan nama file aktualnya.

    Sekali lagi, sesuaikan kembali formula dari nama range yang digunakan terhadap susunan tabel aktual yang mas @Dhimasanggoro gunakan. Silahkan diskusikan kembali jika mengalami kesulitan.

    Demikian.

  3. Caton

    Okt 5 Terverifikasi Indonesia + 12.008 Poin
    Di sunting 2 minggu lalu oleh Caton

    @Dhimasanggoro ...

    Hanya ingin meyakinkan saja, apakah nilai pada file data sheet Compare HPP AIR vs LAND sudah tepat? Karena menurut saya nilainya tidak sesuai dengan grup kolom datanya, misalkan nilai pada grup kolom TOTAL HPP VIA AIR (SMU + HANDLING). Coba bandingkan dengan file terlampir ...

    Demikian.

  4. gan, mohon maaf masih mau nanya, kayanya bakal panjang hehe

    1. kalo yang ini di Tes - Status report kolom AF (HPP, contoh: A174 Bandung), dia masih narik dari kolom K,L,M,N,O,P jadi angkanya 50.000, nah kalo ini nariknya dari kolom T,U,V,W,X gimana gan? jadinya angkanya 3.800 x 20 = 76.000

    2. Tolong gan bisa dijelasin sedikit fungsi ini, maaf maaf gan =IF(Data.Row;INDEX(Data.Fare;Data.Row;Data.Col)*$M174;"")

    Hanya ingin meyakinkan saja, apakah nilai pada file data sheet Compare HPP AIR vs LAND sudah tepat? Karena menurut saya nilainya tidak sesuai dengan grup kolom datanya, misalkan nilai pada grup kolom TOTAL HPP VIA AIR (SMU + HANDLING). Coba bandingkan dengan file terlampir ..

    3. betul sekali gan, setelah saya cek iya itu yang sheet Compare HPP Air dengan Total HPP Via air beda, yang betul memang angka yang Total HPP Via air, makasih gan koreksinya.

  5. Caton

    Okt 6 Terverifikasi Indonesia + 12.008 Poin

    @Dhimasanggoro ...

    [1]. Hal ini sudah saya sampaikan sebelumnya ...

    Hanya ingin meyakinkan saja, apakah nilai pada file data sheet Compare HPP AIR vs LAND sudah tepat? Karena menurut saya nilainya tidak sesuai dengan grup kolom datanya, misalkan nilai pada grup kolom TOTAL HPP VIA AIR (SMU + HANDLING). Coba bandingkan dengan file terlampir ...

    Beda.png

    Data diambil dari sheet Compare HPP AIR vs LAND file Tes - Master HPP 2018. Lihat perbedaan hasil sebelum dan sedudah formulanya diperbaiki. Dan bukankah sudah saya berikan contoh revisi formulanya. Coba periksa kembali file terakhir yang saya lampirkan, dan ikuti formula pada file tersebut...

    [2]. Sebelumnya, pahami dahulu bahwa data pada file Status Report ... diambil dari file Tes - Master HPP 2018 pada sheet Compare HPP AIR vs LAND melalui nama range:

    — Master.Dest        : Untuk kolom E (CITY)
    — Master.Mode.Air    : Untuk grup data TOTAL HPP VIA AIR (SMU + HANDLING)
    — Master.Mode.Land   : Untuk grup data TOTAL HPP VIA LAND (HOST + HANDLING)

    Sedangkan formula:

    =IF(Data.Row;INDEX(Data.Fare;Data.Row;Data.Col)*$M174;"")

    pada dasarnya hanyalah formula dengan fungsi INDEX + MATCH saja. Logikanya dasarnya:

    Jika ada baris data (Data,Row) yang sesuai (match), maka ambil nilai dari indeks kolom (Data.Col) kemudian kalikan dengan nilai di kolom Weight (M). Jika tidak ada baris data yang sesuai, maka kosongkan saja.

    Pada formula tersebut, ada 3 nama range yang digunakan, yakni:

    Data.Row. Nama range ini merupakan nama range dinamis, yang akan menyesuaikan hasilnya berdasarkan baris dimana nama range ini digunakan. Formulanya:

    =IFERROR(MATCH(Data!$F174;Master.Dest;0);IFERROR(MATCH(Data!$H174;Master.Dest;0);0))

    Perhatikan notasi Data!$F174. Formula nama range ini akan berubah jika kursor berada pada baris ke-2, misalkan sel A2 menjadi:

    =IFERROR(MATCH(Data!$F2;Master.Dest;0);IFERROR(MATCH(Data!$H2;Master.Dest;0);0))

    Formula pada nama range ini pada dasarnya bertujuan untuk mencari nilai pada sel F174 ke dalam array Master.Dest. Jika pencarian tidak menemukan kecocokan, maka hasilnya adalah kesalahan #N/A. Jika ada kesalahan, maka fungsi IFERROR pertama akan mengalihkan pencarian dengan menggunakan nilai pada sel H174 ke dalam array Master.Dest. Apabila pencarian masih juga tidak menemukan kecocokan, maka fungsi IFERROR kedua akan mengembalikan nilai 0. Jika pencarian pertama atau kedua berhasil, maka nama range Data.Row akan berisi nilai indeks baris data.

    Data.Col. Nama range ini fungsinya untuk menentukan indeks kolom data yang sesuai dengan perbadingan kondisi nilai berat (Weight) pada sel M174. Formula pada nama range in hanya akan dikalkulasikan jika nama range Data.Row bernilai > 0. Formulanya:

    =IF(Data.Row;VLOOKUP(Data!$M174;{0\1;2\2;11\3;20\4;51\5;100,1\6};2;1);0)

    Untuk menentukan indeks kolom berdasarkan nilai pada sel M174, saya gunakan fungsi VLOOKUP, bukan menggunakan fungsi IF bertingkat, dimana formulanya adalah:

    =VLOOKUP(Data!$M174;{0\1;2\2;11\3;20\4;51\5;100,1\6};2;1)

    Pada formula tersebut, Array pencarian disusun secara langsung dimana notasinya berupa {nilai\indeks kolom}. Pada sel M174, nilai pada sel tersebut adalah 20. Maka jika parameter terakhir dari fungsi VLOOKUP bernilai TRUE, fungsi VLOOKUP akan mencari nilai yang tepat (sesuai) atau yang mendekati nilai yang dicari. Jika nilai yang sesuai tidak ditemukan, maka fungsi ini akan mencari nilai terbesar selanjutnya (terakhir) namun tetap lebih kecil dari nilai yang dicari.

    Karena nilai 20 ada pada indeks ke-4 ({20\4}) maka hasil dari fungsi VLOOKUP tersebut adalah 4. Bagaimana jika nilai yang dicari adalah 37? maka hasilnya tetap sama yakni 4 ({20\4}) karena nilai 37 > 0, 37 > 2, 37 > 11, 37 > 20 dan 37 < 51. Dari kondisi tersebut, 20 adalah nilai terbesar terakhir yang tetap lebih kecil dari 37. Silahkan gunakan formula tersebut untuk menguji indeks kolom yang tepat untuk setiap baris data kolom Weight. Jika ada kesalahan, coba bereksperimen dengan nilai-nilai pada parameter Array pada fungsi VLOOKUP tersebut.

    [3]. Data.Fare. Tujuan dari nama range ini adalah untuk menentukan range data yang akan dirujuk melalui fungsi INDEX nantinya, berdasarkan nilai pada kolom Moda (kolom K). Formulanya:

    =IF(Data.Row;IF(Data!$K174="AIR";Master.Mode.Air;IF(Data!$K174="LAND";Master.Mode.Land;NA()));"")

    Pada formula tersebut, jika sel K174 bernilai AIR, maka nama range Data.Fare akan mengembalikan Array berupa range yang sudah ditentukan pada nama range Master.Mode.Air. Jika sel K174 bernilai LAND, maka nama range Data.Fare akan mengembalikan Array berupa range yang sudah ditentukan pada nama range Master.Mode.Land.

    Maka, dari formula yang ditanyakan:

    =IF(Data.Row;INDEX(Data.Fare;Data.Row;Data.Col)*$M174;"")

    fungsi INDEX hanya akan dikalkulasikan jika nilai Data.Row > 0. Dengan menguji nilai pada nama range Data.Row tersebut, dapat mengurangi waktu dan proses yang tidak diperlukan. Kalkulasi hanya akan dilakukan jika data ditemukan. Dengan nilai yang ada pada sel F174, maka:

    Data.Row   = 168
    Data.Col   = 4
    Data.Fare  = Master.Mode.Air = Compare HPP AIR vs LAND'!$G$3:$L$481
    
    =IF(Data.Row;INDEX(Data.Fare;Data.Row;Data.Col)*$M174;"")
    =IF(168;INDEX(Data.Fare;168;Data.Col)*$M174;"")
    =IF(TRUE;INDEX(Master.Mode.Air;168;Data.Col)*$M174;"")
    =IF(TRUE;INDEX(Compare HPP AIR vs LAND'!$G$3:$L$481;168;Data.Col)*$M174;"")
    =IF(TRUE;INDEX(Compare HPP AIR vs LAND'!$G$3:$L$481;168;4)*$M174;"")
    =IF(TRUE;3800*$M174;"")
    =IF(TRUE;3800*20;"")
    =IF(TRUE;3800*20;N/A)
    =3800*20
    =76000

    Demikian penjelasannya, semoga tidak bertambah bingung.

  6. Gan @Caton makasih, saya banyak dapet pencerahan, saya udah coba dijabarkan rumusnya, untuk Data.Row & Data.col udah bisa gan. nah pas yang data fare saya jabarin, hasilnya #VALUE! ya gan.

    ada yang salah kah?mohon koreksinya gan,

    =IF(IFERROR(MATCH(Data!$F9;'[HPP 2018 cobacoba.xlsx]Compare HPP AIR vs LAND'!$E$3:$E$481;0);IFERROR(MATCH(Data!$H9;'[HPP 2018 cobacoba.xlsx]Compare HPP AIR vs LAND'!$E$3:$E$481;0);0));IF(Data!$K9="AIR";'[HPP 2018 cobacoba.xlsx]Compare HPP AIR vs LAND'!$G$3:$L$481;IF(Data!$K9="LAND";'[HPP 2018 cobacoba.xlsx]Compare HPP AIR vs LAND'!$M$3:$R$481;NA()));"")

    terima kasih

  7. Caton

    Okt 8 Terverifikasi Indonesia + 12.008 Poin

    @Dhimasanggoro ... pas yang data fare saya jabarin, hasilnya #VALUE! ...

    Data.Fare akan mengembalikan Array berupa range data. Jika MODA = AIR, maka rangenya adalah G3:L481, dan jika MODA = LAND, maka rangenya adalah M3:R481. Kedua range berada pada file data (TEs -Master ...). Data.Fare tidak dapat digunakan secara mandiri karena tujuan dari nama range tersebut adalah untuk merujuk ke tabel data yang sesuai dengan kolom MODA. Silahkan lihat formula pada sel AF9:

    =IF(Data.Row;INDEX(Data.Fare;Data.Row;Data.Col)*$M9;"")

    Data.Fare digunakan sebagai rujukan terhadap parameter Array dari fungsi INDEX.

    Demikian.

  8. Makasih Mas @Caton sangat membantu sekali, makasih seklai lagi mas

 

atau Mendaftar untuk ikut berdiskusi!