Ringkasan
Untuk menukar perbelanjaan dalam unit satu masa (iaitu harian, mingguan, bulanan, dan lain-lain) kepada unit waktu yang lain, anda boleh menggunakan formula INDEX dan MATCH dua arah. Dalam contoh yang ditunjukkan, formula di E5 (disalin di seberang dan bawah) adalah:
=$C5*INDEX(data,MATCH($D5,vunits,0),MATCH(F$4,hunits,0))
di mana data (O5: S9), vunits (N5: N9), dan hunits (O4: S4) dinamakan julat, seperti yang dijelaskan di bawah.
Penjelasan
Untuk menukar perbelanjaan dalam unit satu masa (iaitu harian, mingguan, bulanan, dan lain-lain) kepada unit waktu yang lain, anda boleh menggunakan formula INDEX dan MATCH dua arah. Dalam contoh yang ditunjukkan, formula di E5 (disalin di seberang dan bawah) adalah:
=$C5*INDEX(data,MATCH($D5,vunits,0),MATCH(F$4,hunits,0))
Rumus ini menggunakan jadual carian dengan julat bernama seperti yang ditunjukkan di bawah:
Julat dinamakan: data (O5: S9), vunits (N5: N9), dan hunits (O4: S4).
Pengenalan
Tujuannya adalah untuk menukar perbelanjaan dalam satuan masa, menjadi perbelanjaan yang setara dalam unit masa yang lain. Contohnya, jika kita mempunyai perbelanjaan bulanan sebanyak $ 30, kita ingin mengira perbelanjaan tahunan sebanyak $ 360, perbelanjaan mingguan sebanyak $ 7.50, dll.
Seperti banyak cabaran di Excel, banyak bergantung pada cara anda mengatasi masalah tersebut. Anda mungkin terdorong untuk mempertimbangkan rangkaian formula IF bersarang. Ini boleh dilakukan, tetapi anda akan berakhir dengan formula yang panjang dan rumit.
Pendekatan yang lebih bersih adalah dengan membina jadual pencarian yang mengandungi faktor penukaran untuk semua kemungkinan penukaran, kemudian gunakan formula INDEX dan MATCH dua arah untuk mendapatkan nilai yang diperlukan untuk penukaran tertentu. Setelah anda mempunyai nilai, anda boleh menggandakan dengan jumlah asalnya.
Jadual penukaran
Jadual penukaran mempunyai nilai yang sama untuk label menegak dan mendatar: harian, mingguan, dua minggu, bulanan dan tahunan. Unit "dari" disenaraikan secara menegak, dan unit "hingga" disenaraikan secara mendatar. Untuk tujuan contoh ini, kita mahu memadankan baris terlebih dahulu, kemudian lajur. Oleh itu, jika kita mahu menukar perbelanjaan bulanan menjadi perbelanjaan tahunan, kita sepadan dengan baris "bulanan", dan lajur "tahunan", dan pulangan 12.
Untuk mengisi jadual itu sendiri, kami menggunakan campuran formula dan pemalar ringkas:
Catatan: Sesuaikan nilai penukaran untuk memenuhi keperluan khusus anda. Memasukkan nilai sebagai = 1/7 adalah cara mudah untuk mengelakkan memasukkan nilai perpuluhan panjang.
Formula carian
Oleh kerana kita perlu mencari nilai penukaran berdasarkan dua input, satuan masa "dari" dan satuan masa "ke", kita memerlukan formula pencarian dua hala. INDEX dan MATCH memberikan penyelesaian yang bagus. Dalam contoh yang ditunjukkan, formula di E5 adalah:
=$C5*INDEX(data,MATCH($D5,vunits,0),MATCH(F$4,hunits,0))
Berfungsi dari dalam ke luar, fungsi MATCH pertama menempatkan baris yang betul:
MATCH($D5,vunits,0) // find row, returns 4
Kami menarik unit masa "dari" yang asal dari lajur D, yang kami gunakan untuk mencari baris yang tepat di vunits range yang dinamakan (N5: N9). Catatan $ D5 adalah rujukan campuran dengan lajur terkunci, sehingga formula dapat disalin di seberang.
Fungsi MATCH kedua menempatkan lajur:
MATCH(F$4,hunits,0) // find column, returns 5
Di sini, kita akan mendapat nilai lookup dari pengepala lajur berturut-turut 4, dan menggunakan ini untuk mencari yang betul "kepada" lajur dalam julat yang dinamakan hunits (O4: S4). Sekali lagi, perhatikan F $ 4 adalah rujukan campuran dengan baris terkunci, sehingga formula dapat disalin.
Setelah kedua-dua formula MATCH mengembalikan hasil ke INDEX, kami mempunyai:
=$C5*INDEX(data,4,5)
Array yang disediakan untuk INDEX adalah data range bernama , (O5: S9). Dengan baris 4 dan lajur 5, INDEX mengembalikan 12, jadi kami mendapat hasil akhir 12000 seperti ini:
=$C5*INDEX(data,4,5) =1000*12 =12000
