Hàm Offset và các kết hợp hàm của nó trong Excel - P1

CHÀO MỪNG BẠN ĐẾN VỚI THƯ VIỆN HỌC TẬP & ĐÀO TẠO CƠ BẢN

Hàm Offset và các kết hợp hàm của nó trong Excel – P1

Excel

Bắt đầu sử dụng Excel

Công thức và hàm

Nhập và phân tích dữ liệu

Định dạng dữ liệu

Xử lý sự cố

Excel 2016

Bây giờ chúng ta đang thực hiện phần thú vị nhất khi sử dụng hàm Offset – áp dụng hàm Offset và các kết hợp hàm của nó trong Excel

1. Hàm Offset và Hàm Sum

Ví dụ chúng ta đã thảo luận cách đây một phút thể hiện cách sử dụng OFFSET và SUM trong Excel đơn giản nhất. Bây giờ, chúng ta hãy nhìn vào những hàm này ở một góc độ khác và xem những gì chúng có thể làm.

1.1. Một công thức hàm sum / offset thay đổi

Vấn đề nữa là khi làm việc với bảng tính cập nhật liên tục, bạn có thể muốn có một công thức SUM tự động chọn tất cả các hàng mới được thêm vào?

Giả sử bạn có dữ liệu nguồn tương tự như những gì bạn thấy trong hình bên dưới. Mỗi tháng sẽ có một dòng mới được thêm vào ngay phía trên công thức SUM, và tự nhiên, bạn muốn nó được bao gồm trong tổng số. Trên toàn bộ, có hai lựa chọn – hoặc cập nhật các dải trong công thức SUM mỗi lần thay đổi bằng tay hoặc bạn sẽ dùng hàm OFFSET làm điều này cho bạn.

Khi ô đầu tiên của dải được tính tổng (SUM) mà được tham chiếu trực tiếp trong công thức SUM, bạn chỉ phải đưa ra các tham số cho hàm OFFSET trong Excel, và sau đó nó sẽ tham chiếu cho đến ô cuối cùng của dải:

  • Reference – ô chứa tổng số, trong ví dụ ở đây là ô B9.
  • Rows – ô trên ô chứa tổng số và nằm bên phải, bắt buộc 1 số âm: -1.
  • Cols – là 0 vì bạn không muốn thay đổi cột.

Từ đó, ta có mẫu công thức SUM / OFFSET:

= SUM (first cell: (OFFSET (cell with total, -1,0)

Rút gọn ví dụ trên, ta sẽ có công thức trông như sau:

= SUM (B2: (OFFSET (B9, -1, 0)))

Và nó thực sự hoạt động tốt như trong hình được minh họa dưới đây:

1.2. Hàm Offset để tổng hợp n các dòng cuối danh sách

Trong ví dụ trên, giả sử bạn muốn biết số tiền thưởng (Bonus) cho N tháng gần nhất chứ không phải là tất cả. Và bạn cũng muốn công thức chạy tự động (không quan tâm đến liệu có bất kỳ hàng mới được thêm vào trang tính hay không).

Với nhiệm vụ này, chúng ta sẽ sử dụng hàm OFFSET kết hợp với các hàm SUM và hàm COUNT / COUNTA:

= SUM (OFFSET (B1, COUNT (B: B) -E1 + 1,0, E1,1))

hoặc là

= SUM (OFFSET (B1, COUNTA (B: B) -E1,0, E1,1)))

Các thông tin sau đây có thể giúp bạn hiểu rõ các công thức tốt hơn:

  • Reference – tiêu đề của cột có các giá trị mà bạn muốn tính tổng, trong ví dụ này là ô B1.
  • Rows – để tính số hàng để tính offset, và bạn sử dụng thêm hàm COUNT hoặc COUNTA.

COUNTA trả về số ô trong cột B có chứa số, từ đó bạn trừ đi các N tháng cuối cùng (giá trị ở trong ô E1) và thêm 1.

 Đối với các cấu trúc bảng khác nhau, bạn có thể cần thực hiện một số điều chỉnh trong công thức OFFSET / COUNTA.

  • Cols – số cột điều chỉnh là 0.
  • Chiều cao – số hàng được tổng hợp được chỉ định trong E1.
  • Chiều rộng – 1 cột.

2. Sử dụng hàm Offset với Average, Max, Min

Theo cách tương tự như chúng tôi có thể tính tiền thưởng cho N tháng qua, bạn có thể nhận được giá trị trung bình cho N ngày, tuần hoặc năm cuối cùng cũng như tìm các giá trị tối đa hoặc tối thiểu. Sự khác biệt duy nhất giữa các công thức là tên của hàm đầu tiên:

= AVERAGE (OFFSET (B1, COUNT (B: B) -E1 + 1,0, E1,1))

= MAX (OFFSET (B1, COUNT (B: B) -E1 + 1,0, E1,1))

= MIN (OFFSET (B1, COUNT (B: B) -E1 + 1,0, E1,1))

Sử dụng hàm Offset với Average, Max, Min

Sử dụng hàm Offset với Average, Max, Min

Lợi ích chính mà các công thức này hơn hàm AVERAGE(B5: B8) hoặc MAX (B5: B8) là bạn sẽ không phải cập nhật công thức mỗi khi bảng gốc của bạn được cập nhật. Bạn sẽ không cần phải quan tâm rằng bao nhiêu dòng mới được thêm vào hoặc xóa trong bảng tính Excel của bạn, mà sẽ có các công thức OFFSET sẽ luôn luôn tham chiếu đến tận những ô cuối cùng (đã quy định trước giới hạn nhỏ nhất và lớn nhất) trong cột.

Bạn có thể thành thạo ngay kỹ năng tin học văn phòng từ giảng viên FPT- Arena chỉ với 280,000Đ