Cara interpolasi data yang hilang di excel

Untuk melakukan interpolasi linier di Excel, gunakan fungsi FORECAST untuk menginterpolasi antara dua pasang nilai x dan y secara langsung

Pada contoh di bawah, rumus untuk menginterpolasi dan menemukan nilai y yang sesuai dengan nilai x 1. 4 adalah

=PERKIRAAN(F2,C3. C4,B3. B4)

Metode sederhana ini bekerja ketika hanya ada dua pasang nilai x dan y. Jika lebih dari 2 pasang, perhitungannya lebih rumit

Isi

Interpolasi Linier di Excel

Tidak ada fungsi interpolasi linier di Excel, tetapi fungsi FORECAST dapat digunakan untuk interpolasi linier bila hanya ada dua pasang nilai x dan y

Ini memiliki sintaks berikut

=PERKIRAAN(x,tahu_ys,tahu_xs)

di mana

• x adalah nilai masukan
• known_ys adalah nilai y yang diketahui
• known_xs adalah nilai x yang diketahui

Fungsi FORECAST bekerja dengan menggunakan regresi linier untuk memperkirakan nilai y yang sesuai dengan nilai masukan x. Ketika hanya ada dua titik data, hasil regresi linier sama dengan interpolasi linier

Namun, jika jumlah nilai x dan nilai y lebih besar dari 2, hasil fungsi FORECAST TIDAK akan menjadi nilai y interpolasi

Untuk menginterpolasi antara nilai x dan y dalam kumpulan data besar (lebih dari 2 pasang nilai), gunakan XLOOKUP atau INDEX dan MATCH untuk mengekstrak sepasang nilai x dan y untuk interpolasi antara

Interpolasi linier mengasumsikan bahwa perubahan y untuk perubahan x tertentu adalah linier. Dalam kebanyakan kasus, interpolasi linier di Excel akan memberikan hasil yang cukup akurat. Namun, jika Anda membutuhkan akurasi yang lebih tinggi lagi, Anda mungkin ingin mempertimbangkan metode yang lebih canggih seperti splines kubik

Interpolasi di Excel dengan XLOOKUP

Jika Anda adalah pelanggan Microsoft 365, atau menggunakan Excel 2021, Excel untuk web, atau Excel untuk perangkat iOS atau Android, Anda bisa menggunakan fungsi XLOOKUP untuk mengekstrak nilai. Jika Anda menggunakan versi Excel yang lebih lama, gunakan metode INDEX/MATCH yang dijelaskan di bawah ini

Di beberapa posting lain tentang mencari data tabular di Excel, saya berfokus pada cara mengekstrak nilai x dan y yang diketahui dari sebuah tabel. Tapi bagaimana jika Anda perlu menginterpolasi data yang hilang di Excel untuk akurasi yang lebih baik?

Lihat juga   Penyesuaian Kurva di Excel

Mari kita lihat cara menginterpolasi di Excel pada beberapa data nyata

Tabel di bawah mencantumkan kerapatan udara sebagai fungsi suhu dengan kenaikan 20 derajat Celcius. Untuk mendapatkan data pada suhu berapa pun antara 0 dan 100 C, kita harus melakukan interpolasi

Untuk memperkirakan massa jenis pada 53 derajat Celcius, gunakan XLOOKUP untuk mencari nilai x1=40, y1=1. 127, x2=60, dan y2=1. 067 dalam tabel, lalu masukkan nilai tersebut ke dalam fungsi FORECAST untuk melakukan interpolasi

Gunakan XLOOKUP untuk Menemukan Nilai untuk Interpolasi di Excel

XLOOKUP memiliki sintaks berikut

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

di mana

• lookup_value adalah nilai untuk ditemukan
• lookup_array adalah larik tempat lookup_value dicari
• return_array adalah array dari mana hasil dikembalikan
• if_not_found adalah nilai yang dikembalikan jika tidak ada yang ditemukan (opsional)
• match_mode memberi tahu fungsi apa yang harus dilakukan jika kecocokan persis tidak ditemukan (opsional)
• search_mode memberi tahu fungsi cara mencari array (opsional)

Untuk menginterpolasi di Excel, gunakan fungsi XLOOKUP untuk menemukan nilai x di kedua sisi nilai input x serta nilai y yang sesuai

Untuk mencari nilai x pertama dari tabel data, gunakan rumus berikut

=XLOOKUP(F3,B5. B10,B5. B10,,-1,1)

F3 adalah lookup_value, dan array B5. B10 adalah lookup_array dan return_array. Mengatur match_mode ke -1 memberi tahu fungsi untuk mengembalikan item berikutnya yang lebih kecil dalam larik pengembalian dan menyetel mode_pencarian ke 1 memulai pencarian dari bagian atas larik

Nilai x1 yang dihasilkan adalah 40, yang merupakan suhu terbesar yang kurang dari 53, nilai x

Rumus yang sangat mirip digunakan untuk mencari nilai x2

Lihat juga   Kalkulator Bilangan Reynolds di Excel

=XLOOKUP(F3,B5. B10,B5. B10,,1,1)

Satu-satunya perbedaan adalah match_mode disetel ke 1 (bukan -1), karena x2 adalah nilai terbesar berikutnya dalam larik pengembalian

Nilai x2 yang dihasilkan adalah 60, suhu terendah lebih besar dari 53 dalam larik pencarian

Selanjutnya, temukan nilai y yang sesuai y1 dan y2 menggunakan XLOOKUP. Untuk y1, F3 adalah lookup_value dan B5. B10 adalah lookup_array, seperti sebelumnya. Untuk mengembalikan nilai y yang sesuai dengan nilai x kurang dari lookup_value, C5. C10 adalah return_array dan match_mode adalah -1

=XLOOKUP(F3,B5. B10,C5. C10,,-1,1)

y1 sama dengan 1. 127

Nilai terakhir yang dicari adalah y2. Rumusnya hanya sedikit berbeda dengan rumus y1. match_mode diatur ke 1 untuk mengembalikan nilai y terbesar berikutnya

=XLOOKUP(F3,B5. B10,C5. C10,,1,1)

Gunakan PRAKIRAAN untuk Interpolasi Excel

Setelah 2 pasang nilai x dan y diketahui, PRAKIRAAN dapat digunakan untuk menginterpolasi di antara keduanya untuk memperkirakan nilai y untuk nilai x tertentu dengan rumus berikut

=PERKIRAAN(F3,F6. F7,F4. F5)

Taksiran untuk y adalah 1. 088

Cegah Kesalahan Interpolasi Excel

Kesalahan bisa terjadi saat nilai x sama dengan salah satu nilai x dalam larik pencarian. Ini menyebabkan x1 sama dengan x2 dan y1 sama dengan y2. Ketika ini terjadi, PERKIRAAN tidak dapat menghitung kemiringan dan mengembalikan kesalahan

Kesalahan dapat dicegah dengan membungkus fungsi FORECAST dalam fungsi IF yang memeriksa apakah nilai x dan x1 sama

Jika x dan x1 sama, y ​​sama dengan y1

=JIKA(F3=F4,F6,PREDIKSI(F3,F6. F7,F4. F5))

Rumus ini dengan benar mengembalikan hasil 1. 127 ketika x sama dengan 40

Gunakan Fungsi INDEX/MATCH untuk Interpolasi di Excel

Jika Anda bukan pelanggan Microsoft 365 atau menggunakan versi Excel yang lebih lama, fungsi XLOOKUP tidak disertakan

Tapi jangan khawatir

Anda dapat menggunakan fungsi INDEX dan MATCH untuk mendapatkan hasil yang sama. Sintaks untuk fungsi-fungsi ini dijelaskan di sini, jadi jika Anda memerlukan penyegaran, pastikan untuk memeriksanya

TERKAIT. MENGGUNAKAN FUNGSI INDEKS DAN MATCH EXCEL UNTUK MENCARI DATA TEKNIK

MATCH mengembalikan lokasi nilai (n) dalam kolom atau baris data

INDEX mengembalikan nilai aktual di posisi ke-n dari baris atau kolom data

Lihat juga   Mengonversi Grafik ke Tabel Data

Dengan menggunakan fungsi-fungsi ini bersama-sama, kita dapat mengekstraksi nilai x1, y1, x2, dan y2 yang kita perlukan untuk interpolasi

Temukan nilai x1 dengan rumus ini

=INDEKS(B5. B10,COCOK(F3,B5. B10,1))

Rumus mengembalikan 40 karena argumen match_type dalam fungsi MATCH diatur ke 1. Ini memberi tahu fungsi MATCH untuk mengembalikan posisi dari larik suhu yang kurang dari nilai lookup_value saat pencocokan persis tidak ditemukan

Temukan nilai x2 dengan rumus ini

=INDEKS(B5. B10,COCOK(F3,B5. B10,1)+1)

Rumus untuk x2 mengembalikan 60 karena hasil yang dikembalikan oleh fungsi MATCH dinaikkan 1 untuk memberi tahu Excel untuk mencari baris berikutnya dalam larik

Untuk y1 dan y2 rumusnya sangat mirip. Satu-satunya perbedaan adalah argumen array untuk fungsi INDEX adalah kolom C

Untuk y1, rumusnya adalah

=INDEKS(C5. C10,COCOK(F3,B5. B10,1))

Untuk y2, rumusnya adalah

=INDEKS(C5. C10,COCOK(F3,B5. B10,1)+1)

Terakhir, gunakan fungsi FORECAST seperti dijelaskan di atas untuk mengembalikan nilai estimasi y

=PERKIRAAN(F3,F6. F7,F4. F5)

Cara Interpolasi di Excel dengan Rumus Interpolasi

Mungkin juga melakukan interpolasi linier di Excel tanpa menggunakan fungsi FORECAST. Alih-alih, gunakan rumus interpolasi di bawah ini, di mana y adalah nilai yang akan dikembalikan, dan x adalah variabel bebas

Rumus ini dapat digunakan di Excel untuk menginterpolasi nilai y secara linier

=F6+(F3-F4)*(F7-F6)/(F5-F4)

[Petunjuk] Gunakan Sel Bernama untuk membuat rumus lebih mudah dipahami

Bagaimana Anda menginterpolasi grafik di Excel?

Hasil interpolasi dapat ditampilkan pada grafik dengan membuat grafik Excel dengan dua seri data. Seri pertama terdiri dari larik nilai x dan y. Deret kedua berisi nilai x untuk dicari dan hasil perkiraan y

Hal hebat tentang pengaturan rumus seperti yang ditunjukkan di atas adalah Anda dapat menginterpolasi dengan benar antara pasangan nilai x dan y yang diketahui

Jadi begitulah – beberapa metode alternatif untuk melakukan interpolasi linier di Excel. Mudah-mudahan Anda dapat menggunakan salah satu metode ini untuk menghemat waktu saat Anda perlu menginterpolasi data dari kumpulan data yang besar

Bagaimana cara menginterpolasi nilai yang hilang di Excel?

Bagaimana Anda menginterpolasi data yang hilang?

Apakah ada fungsi interpolasi di Excel?

Bagaimana Anda menginterpolasi data secara manual?