@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 ...
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.
