Bạn đang sử dụng hàm GoogleFinance để lấy dữ liệu lịch sử từ Google Finance? Vậy thì bạn có thể muốn biết cách tính đơn giản trung bình động (SMA) trong Google Sheets.
Nhưng điều đó không có nghĩa rằng công thức của tôi chỉ hoạt động với thông tin chứng khoán được trả về từ trang web Google Finance. Tôi đã sử dụng chức năng tương ứng (GoogleFinance) để nhập một số dữ liệu lịch sử để kiểm tra.
Công thức chỉ đòi hỏi điểm dữ liệu (số) trong một cột để hoạt động.
Trong hướng dẫn này, bạn sẽ được cung cấp một công thức kết hợp Truy vấn và GoogleFinance để tính đơn giản trung bình động trong Google Sheets.
Đừng lo lắng về độ phức tạp của công thức. Bạn có thể sử dụng nó ngay mà không cần chỉnh sửa.
SMA: Hiểu thuật ngữ
Đơn giản Trung bình động (SMA) là trung bình của một tập hợp các số đã cho. Khác với việc tính trung bình, trong SMA, tập hợp số đã cho tiếp tục di chuyển với dữ liệu mới.
Chúng ta có SMA bằng cách cộng tất cả các giá đóng của n ngày CUỐI CÙNG (nếu chúng ta coi dữ liệu cổ phiếu) và chia số đó cho n.
Chúng ta sẽ sử dụng hàm GoogleFinance để lấy giá đóng cửa của một ký hiệu chứng khoán cụ thể trong một khoảng thời gian cho trước và tính đơn giản trung bình.
Hãy quên đi về dữ liệu chứng khoán và hiểu SMA với một ví dụ cơ bản.
Dưới đây là một ví dụ về việc tính đơn giản trung bình của ba điểm dữ liệu cuối cùng trong một phạm vi trong Google Sheets.
Trong ô C3, tôi đã tính trung bình của ba điểm dữ liệu CUỐI CÙNG trong cột A bằng công thức Trung bình sau.
=average(A6:A8)
Sau đó, một giá trị mới có sẵn trong ô A9. Vì vậy, chúng ta phải loại bỏ một điểm dữ liệu cũ nhất (ô A6) và thêm một điểm dữ liệu mới (A9). Đó là công thức thứ hai trong hình ảnh bên trên.
=average(A7:A9)
Trên đây là một ví dụ về tính toán đơn giản trung bình trong Google Sheets.
Nhưng làm thế nào chúng ta tự động hóa điều đó? Ý tôi là tránh chỉnh sửa phạm vi thủ công trong công thức.
Cách tính đơn giản trung bình một cách linh hoạt trong Google Sheets
Như đã đề cập ở trên, để tính đơn giản trung bình trong Google Sheets, bạn cần một công thức, có thể trích xuất các giá trị cuối cùng N trong một cột hoặc hàng.
Chúng ta có thể sử dụng công thức sau đây cho cùng.
=ArrayFormula(iferror(average(query(if(len(A1:A),{ROW(A1:A),A1:A},),”Select Col2 where Col2>0 order by Col1 Desc limit N”))))
Trong công thức này, N là 3. Vui lòng xem phần được làm nổi bật ở cuối Truy vấn.
Chi tiết, vui lòng kiểm tra hướng dẫn này: Tìm trung bình của N giá trị cuối cùng trong Google Sheets.
Vì vậy, bạn đã học được cách tính đơn giản trung bình động trong Google Sheets.
Bây giờ hãy nhập một số dữ liệu lịch sử từ Google Finance và tính đơn giản trung bình động một cách linh hoạt trong Google Sheets.
Đơn giản Trung bình động liên quan đến Truy vấn và GoogleFinance trong Google Sheets
Ở đây, tôi sẽ tính SMA bằng cách thêm giá đóng cửa của chứng khoán, mã HDFC, trong 10 ngày qua.
Cú pháp GoogleFinance: GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date|num_days], [interval])
ticker: “HDFC”
attribute: close
start_date: today()-10
end_date: today()
interval: “daily”
Công thức (để cung cấp dữ liệu chứng khoán) trong ô A1:
=GOOGLEFINANCE(“HDFC”,”close”,today()-10,today(),”daily”)
Kết quả:
Bạn sẽ thấy kết quả khác nhau, ngay cả khi ký hiệu mã chứng khoán giống nhau vì start_date và end_date không giống nhau khi bạn kiểm tra ví dụ này.
Chú ý:- Để ngạc nhiên bạn, bạn có thể thấy rằng chỉ có một số điểm dữ liệu ít hơn! Điều đó là do công thức GoogleFinance bỏ qua các ngày giao dịch. Tôi sẽ đề xuất một giải pháp cho vấn đề này sau.
Bây giờ sử dụng Truy vấn, chúng ta có thể tính trung bình đơn giản của các điểm dữ liệu trong cột B, vì nó có thể xóa cột “Ngày” A và tổng hợp cột “Đóng” B.
Công thức (để tính trung bình đơn giản từ dữ liệu chứng khoán):
Bao quanh công thức trên bằng Query như sau.
=query(GOOGLEFINANCE(“HDFC”,”close”,today()-10,today(),”daily”),”Select Avg(Col2) label Avg(Col2)””)
Giải pháp khi GoogleFinance trả về ít điểm dữ liệu hơn
Như bạn có thể thấy trong ví dụ trên, GoogleFinance trả về ít điểm dữ liệu hơn.
Nếu bạn quan tâm đến số lượng điểm dữ liệu, bạn phải sửa đổi phần today()-10 của công thức.
Thay thế 10 bằng 30, tức today()-30 hoặc một số lớn hơn nữa. Vì vậy, GoogleFinance sẽ nhập dữ liệu chứng khoán trong hơn mười ngày.
Ở đây chúng ta cần thực hiện hai bước để tính đơn giản trung bình động trong Google Sheets.
Bước 1:
Vui lòng chèn công thức sau vào ô A1 để lấy dữ liệu chứng khoán cho 30 ngày qua (mặc dù chúng ta muốn mười ngày).
=GOOGLEFINANCE(“HDFC”,”close”,today()-30,today(),”daily”)
Bước 2:
Chúng ta sẽ sử dụng một biến thể của công thức dưới tiêu đề (vui lòng cuộn lên để xem) “Cách tính đơn giản trung bình động một cách linh hoạt trong Google Sheets” như sau.
=ArrayFormula(iferror(average(query(if(len(A1:A),{ROW(A1:A),B1:B},),”Select Col2 where Col2>0 order by Col1 Desc limit 10″))))
Tôi đã đề cập nơi để tìm giải thích công thức này. Đó là tất cả những gì cần biết về cách tính đơn giản trung bình động trong Google Sheets.
