Ringkasan
Untuk mengubah pengeluaran dalam satu unit waktu (yaitu harian, mingguan, bulanan, dll.) Ke unit waktu lain, Anda dapat menggunakan rumus INDEX dan MATCH dua arah. Dalam contoh yang diperlihatkan, rumus dalam E5 (disalin ke seberang dan ke bawah) adalah:
=$C5*INDEX(data,MATCH($D5,vunits,0),MATCH(F$4,hunits,0))
di mana data (O5: S9), vunit (N5: N9), dan hunits (O4: S4) diberi nama rentang, seperti yang dijelaskan di bawah ini.
Penjelasan
Untuk mengubah pengeluaran dalam satu unit waktu (yaitu harian, mingguan, bulanan, dll.) Ke unit waktu lain, Anda dapat menggunakan rumus INDEX dan MATCH dua arah. Dalam contoh yang diperlihatkan, rumus dalam E5 (disalin ke seberang dan ke bawah) adalah:
=$C5*INDEX(data,MATCH($D5,vunits,0),MATCH(F$4,hunits,0))
Rumus ini menggunakan tabel pencarian dengan rentang bernama seperti yang diperlihatkan di bawah ini:
Rentang bernama: data (O5: S9), vunits (N5: N9), dan hunits (O4: S4).
pengantar
Tujuannya adalah untuk mengubah pengeluaran dalam satu satuan waktu, menjadi pengeluaran yang setara dalam satuan waktu lainnya. Misalnya, jika kita memiliki pengeluaran bulanan $ 30, kita ingin menghitung pengeluaran tahunan $ 360, pengeluaran mingguan $ 7,50, dll.
Seperti banyak tantangan di Excel, banyak hal bergantung pada bagaimana Anda mendekati masalah tersebut. Anda mungkin tergoda untuk mempertimbangkan rangkaian rumus IF bertumpuk. Ini bisa dilakukan, tetapi Anda akan mendapatkan rumus yang panjang dan rumit.
Pendekatan yang lebih rapi adalah dengan membuat tabel pencarian yang berisi faktor konversi untuk semua kemungkinan konversi, lalu gunakan rumus INDEX dan MATCH dua arah untuk mengambil nilai yang diperlukan untuk konversi tertentu. Setelah Anda mendapatkan nilainya, Anda dapat mengalikan dengan jumlah aslinya.
Tabel konversi
Tabel konversi memiliki nilai yang sama untuk label vertikal dan horizontal: harian, mingguan, dua mingguan, bulanan, dan tahunan. Unit "dari" dicantumkan secara vertikal, dan unit "hingga" dicantumkan secara horizontal. Untuk keperluan contoh ini, kami ingin mencocokkan baris terlebih dahulu, lalu kolomnya. Jadi, jika kita ingin mengubah pengeluaran bulanan menjadi pengeluaran tahunan, kita mencocokkan baris "bulanan", dan kolom "tahunan", dan mengembalikan 12.
Untuk mengisi tabel itu sendiri, kami menggunakan campuran rumus dan konstanta sederhana:
Catatan: Sesuaikan nilai konversi untuk memenuhi kebutuhan spesifik Anda. Memasukkan nilai sebagai = 1/7 adalah cara mudah untuk menghindari memasukkan nilai desimal yang panjang.
Rumus pencarian
Karena kita perlu menemukan nilai konversi berdasarkan dua masukan, satuan waktu "dari" dan satuan waktu "ke", kita memerlukan rumus pencarian dua arah. INDEX dan MATCH memberikan solusi yang bagus. Dalam contoh yang ditampilkan, rumus dalam E5 adalah:
=$C5*INDEX(data,MATCH($D5,vunits,0),MATCH(F$4,hunits,0))
Bekerja dari dalam ke luar, fungsi MATCH pertama menempatkan baris yang benar:
MATCH($D5,vunits,0) // find row, returns 4
Kami menarik unit waktu "dari" asli dari kolom D, yang kami gunakan untuk menemukan baris yang tepat dalam vunit rentang bernama (N5: N9). Catatan $ D5 adalah referensi campuran dengan kolom terkunci, sehingga rumus dapat disalin.
Fungsi MATCH kedua menempatkan kolom:
MATCH(F$4,hunits,0) // find column, returns 5
Di sini, kita mendapatkan nilai pencarian dari tajuk kolom di baris 4, dan menggunakannya untuk menemukan kolom "ke" yang tepat dalam hunit rentang bernama (O4: S4). Sekali lagi, catatan F $ 4 adalah referensi campuran dengan baris terkunci, sehingga rumus dapat disalin.
Setelah kedua rumus MATCH mengembalikan hasil ke INDEX, kami memiliki:
=$C5*INDEX(data,4,5)
Larik yang disediakan untuk INDEX adalah data rentang bernama , (O5: S9). Dengan baris 4 dan kolom 5, INDEX mengembalikan 12, jadi kita mendapatkan hasil akhir 12000 seperti ini:
=$C5*INDEX(data,4,5) =1000*12 =12000
