Nếu bạn yêu thích toán học và cần khám phá các điểm dữ liệu chưa biết từ một mẫu khảo sát về các điểm dữ liệu đã biết, thì nội suy bằng Excel là một lựa chọn dễ dàng.
Đang xem: Công thức nội suy trong excel
Excel là một máy tính mạnh mẽ với khả năng hiển thị dữ liệu. Do đó, đối với các phân tích toán học và thống kê khác nhau, bạn sẽ luôn thấy nó hữu ích. Mặc dù không có hàm đơn giản cho phép nội suy, bạn có thể sử dụng các hàm khác nhau để ước tính các điểm dữ liệu mới từ một bảng dữ liệu có giá trị X và Y nhất định.
Đọc để biết các phương pháp nội suy dữ liệu phổ biến và dễ dàng trong Excel.
Mục Lục
1. Sử dụng một phương trình toán học
Một trong những cách tiếp cận cơ bản của nội suy dữ liệu trong Microsoft Excel là áp dụng công thức toán học của nội suy tuyến tính. Bạn có thể sử dụng phương pháp này nếu nhận được đường xu hướng tuyến tính cho biểu đồ.
Y= Y1 + (X-X1)⨯(Y2-Y1)/(X2-X1)Ví dụ, bạn muốn tìm giá trị của Y khi X là 15 từ bảng này. Đây là cách thực hiện:
Tạo bảng gồm các giá trị X1, X2, Y1 và Y2 bằng cách phân tích bảng.
Bạn phải chọn các điểm dữ liệu một bước ở trên và dưới giá trị X mới mà bạn đang tìm kiếm. Bây giờ, hãy áp dụng công thức sau trong bất kỳ ô nào để tìm giá trị nội suy của Y tương ứng với giá trị X 15.
=G5+(K4-E5)*(H5-G5)/(F5-E5)Quá trình này khá đơn giản và thủ công. Đối với các tập dữ liệu lớn hơn, bạn sẽ phải đầu tư nhiều thời gian vào việc nội suy các điểm dữ liệu.
Tải xuống: Nội suy trong bảng tính Excel (Miễn phí)
Liên quan: Cách tạo biểu đồ phân tán trong Excel và trình bày dữ liệu của bạn
2. Sử dụng phương pháp nối đường cong
Chỉnh đường cong là một phương pháp nhanh chóng nhưng thủ công khác để nội suy với Excel. Chỉ cần tạo một biểu đồ phân tán và tạo ra phương trình đồ thị. Excel sử dụng hồi quy tuyến tính để điền phương trình. Bạn cần áp dụng phương trình phù hợp với đường cong này để tìm giá trị của Y:
Y= Slope * X + Y-interceptĐể khám phá giá trị của Y từ một giá trị cho trước của X, là 15, bạn có thể thử các bước sau:
Chọn bảng và chèn biểu đồ phân tán. Nhấp chuột phải vào đường xu hướng và sau đó chọn Định dạng đường xu hướng. Đánh dấu vào các hộp bên cạnh Hiển thị giá trị bình phương R trên biểu đồ và Hiển thị phương trình trên biểu đồ. Ngoài ra, hãy chọn Tuyến tính đường xu hướng. Khi đồ thị phân tán hiển thị phương trình, hãy lắp giá trị của X vào đó để tìm giá trị của Y. Công thức sau đây cho thấy giá trị của Y.
=0.9889*K4+3.5333Phương pháp nối đường cong chỉ phù hợp với các tập dữ liệu nhỏ. Không có tự động hóa dễ dàng cho quá trình này và bạn chỉ có thể dựa vào macro Microsoft Excel để tự động hóa dựa trên phím nóng.
3. Sử dụng các hàm Excel: Độ dốc và Chặn
Bạn có thể sử dụng các hàm SLOPE và INTERCEPT trong Excel để nội suy tuyến tính giá trị Y. Bạn có thể dễ dàng áp dụng các hàm này vì chúng chỉ yêu cầu các giá trị của X và Y đã biết. Sau đây là các cú pháp mà bạn sẽ cần sử dụng:
=SLOPE(known_y”s, known_x”s)=INTERCEPT(known_y”s, known_x”s)Đây là cách bạn có thể nội suy các điểm dữ liệu thông qua phương pháp này:
Đổi tên hai ô bằng Dốc và Đánh chặn. Áp dụng DỐC bên dưới tiêu đề Độ dốc và chọn dữ liệu có liên quan từ bảng. Tương tự, áp dụng GIAO LƯU chức năng và điền giá trị của điểm đánh chặn. Bây giờ, hãy áp dụng Y = Độ dốc * X + Y-chặn công thức để khám phá giá trị của Y. Bạn có thể tạo công thức Excel cho phương trình trên, nơi bạn có thể chỉ cần thay đổi giá trị của X để tìm ra các điểm dữ liệu chưa biết khác.
Bạn có thể nội suy nhanh hơn nếu bạn tạo ô cho giá trị X, Độ dốc và Điểm chặn. Tuy nhiên, quá trình này có thể không tìm nạp dữ liệu chính xác cho phép nội suy phi tuyến tính.
4. Sử dụng Hàm Excel: DỰ BÁO
Đây là một hàm Excel phổ biến để dự đoán hiệu suất bằng cách phân tích một tập hợp các điểm dữ liệu trong thế giới thực. Bạn sẽ tìm thấy FORECAST và FORECAST.LINEAR trong Excel 2016 và các phiên bản mới hơn. Thật tốt khi sử dụng FORECAST.LINEAR để có kết quả đáng tin cậy.
Hàm sử dụng ba đối số để tạo ra một điểm dữ liệu được dự đoán. Các đối số này là một giá trị đã cho của X, các giá trị đã biết của Y và các giá trị của X từ một bảng. Kiểm tra cú pháp dưới đây:
=FORECAST.LINEAR(x, known_y”s, known_x”s)Bạn có thể nhanh chóng xác định giá trị của Y bằng cách làm theo các bước sau:
Ô mà bạn muốn điền giá trị cho Y phải có giá trị bằng (=) ra hiệu. Bây giờ, hãy gõ Dự báo và chọn FORECAST.LINEAR chức năng từ menu thả xuống công thức. Nhấp vào giá trị của X và đặt dấu phẩy. Sau đó, chọn phạm vi ô giá trị Ys ‘đã biết, theo sau là dấu phẩy. Chọn phạm vi ô Xs ‘đã biết và đóng công thức bằng dấu ngoặc đơn. nhấn đi vào để truy xuất giá trị nội suy của Y. Bạn có thể tiếp tục thay đổi giá trị của X trong phạm vi của bảng đã cho để tìm nạp các giá trị Y. Công thức DỰ BÁO của hướng dẫn này như sau:
=FORECAST.LINEAR(K4,C2:C10,B2:B10)Các hàm FORECAST.LINEAR cung cấp dự đoán dữ liệu chính xác cao cho các điểm dữ liệu tuyến tính và hơi phi tuyến. Nó sử dụng các giá trị đầu tiên và cuối cùng cho một giá trị X nhất định và tính đến toàn bộ tập dữ liệu.
Do đó, bạn sẽ thấy việc sử dụng chức năng này để phân tích dữ liệu quan trọng như dự báo lưu lượng truy cập web, lập mô hình dữ liệu tài chính và tính toán hiệu suất kinh doanh.
5. Hàm GROWTH cho phép nội suy phi tuyến
Khi bạn có một bảng dữ liệu trong thế giới thực ở dạng hàm mũ, FORECAST.LINEAR sẽ không mang lại các điểm dữ liệu nội suy chính xác. Excel có một chức năng tuyệt vời khác để bạn dự đoán dữ liệu và đó là TĂNG TRƯỞNG. Giống như các hàm khác được đề cập ở trên, công thức này cũng dễ áp dụng.
Ngoài các đối số quen thuộc như phạm vi ô Xs ‘đã biết, giá trị Y đã biết và giá trị đã cho của X, GROWTH sử dụng một giá trị logic bổ sung. Thông thường, bạn cần nhập một cho hằng số logic này. Đây là cách cú pháp trông:
=GROWTH(known_y’s,
Chèn SỰ PHÁT TRIỂN hàm trong bất kỳ ô nào bạn muốn giá trị nội suy cho Y. Nhập phạm vi ô cho các giá trị Y đã biết, theo sau là dấu phẩy. Lặp lại bước trên để nhập phạm vi ô cho các giá trị X đã biết. Bây giờ, hãy nhập giá trị của X đã cho và đặt dấu phẩy. Nhập 1 làm đối số cuối cùng và đóng công thức. Đánh đi vào để tạo ra giá trị của Y được nội suy. Bạn có thể sử dụng tham chiếu ô thay vì giá trị thực tế để khám phá thêm giá trị bằng cách thay đổi dữ liệu đã cho. Công thức cuối cùng sẽ giống như sau: =GROWTH(B2:B12,A2:A12,E2,1)Khi so sánh với hàm FORECAST, GROWTH tạo ra các điểm dữ liệu nội suy chính xác và đáng tin cậy hơn cho các tập dữ liệu theo cấp số nhân chủ yếu là phi tuyến tính. Tương tự như FORECAST, bạn cũng có thể tự động hóa chức năng TRƯỞNG THÀNH mà không cần tốn nhiều công sức.
Dễ dàng nội suy và dự báo dữ liệu
Giờ đây, bạn có thể nội suy dữ liệu một cách hiệu quả cho các nhiệm vụ sắp tới của mình từ cơ quan hoặc trường học.
Những thông tin chi tiết về dữ liệu nhanh chóng như vậy sẽ giúp bạn theo nhiều cách khác nhau, chẳng hạn như cắt một vài bước lặp đi lặp lại của nghiên cứu dữ liệu hoặc ước tính tải công việc của bạn trước khi thực hiện bất kỳ dự án nào khác.