Với nhiều bạn, Excel luôn là một bài toán vô cùng khó khăn với vô vàn các hàm tính toán khác nhau mà bạn không thể nhớ hết. Vậy hôm nay, Gamehot24h xin giới thiệu với bạn một số mẹo để sử dụng hàm Offset trong Excel một cách dễ dàng nhất. Dưới đây sẽ là hướng dẫn phương pháp tham chiếu chỉ bằng 1 công thức duy nhất.
Hàm Offset là gì? Ứng dụng của hàm Offset trong Excel
Hàm Offset dùng để trả về tham chiếu tới một phạm vi cách một ô hoặc phạm vi ô một số hàng và một số cột đã xác định. Tham chiếu được trả về có thể là một ô đơn hoặc một phạm vi ô. Bạn có thể chỉ rõ số hàng và số cột cần trả về.
Cú pháp
OFFSET(reference, rows, cols, [height], [width])
Cú pháp hàm OFFSET có các đối số sau đây:
- Reference Bắt buộc. Vùng tham chiếu mà bạn muốn căn cứ khoảng cách tham chiếu vào đó. Vùng tham chiếu phải tham chiếu tới một ô hoặc một phạm vi các ô liền kề; nếu không hàm OFFSET trả về giá trị lỗi #VALUE! .
- Rows Bắt buộc. Số hàng, lên hoặc xuống, mà bạn muốn ô ở góc trên bên trái tham chiếu tới. Dùng 5 làm đối số hàng sẽ chỉ ra rằng ô ở góc trên bên trái trong tham chiếu cách vùng tham chiếu 5 ô về phía dưới. Hàng có thể là số dương (có nghĩa là ở dưới vùng tham chiếu bắt đầu) hoặc số âm (có nghĩa là ở trên vùng tham chiếu bắt đầu).
- Cols Bắt buộc. Số cột, về bên trái hoặc phải, mà bạn muốn ô ở góc trên bên trái của kết quả tham chiếu tới. Dùng 5 làm đối số cột sẽ chỉ ra rằng ô ở góc trên bên trái trong tham chiếu cách vùng tham chiếu 5 cột về bên phải. Cột có thể là số dương (có nghĩa là ở bên phải vùng tham chiếu bắt đầu) hoặc số âm (có nghĩa là ở bên trái vùng tham chiếu bắt đầu).
- Height Tùy chọn. Chiều cao, tính bằng số hàng, mà bạn muốn có cho tham chiếu trả về. Chiều cao phải là số dương.
- Width Tùy chọn. Độ rộng, tính bằng số cột, mà bạn muốn có cho tham chiếu trả về. Độ rộng phải là số dương.
Chú thích
- Nếu đối số hàng và cột làm cho vùng tham chiếu trả về vượt ra ngoài đường biên của trang tính, thì hàm OFFSET trả về giá trị lỗi #REF! .
- Nếu đối số chiều cao và độ rộng được bỏ qua, thì nó được giả định có cùng chiều cao và độ rộng với vùng tham chiếu.
- Hàm OFFSET không thực sự di chuyển bất kỳ ô nào hoặc thay đổi vùng chọn; nó chỉ trả về một tham chiếu. Có thể dùng hàm OFFSET với bất kỳ hàm nào đang cần đến một đối số tham chiếu. Ví dụ, công thức SUM(OFFSET(C2,1,2,3,1)) tính toán tổng giá trị của một phạm vi gồm 3 hàng 1 cột, mà phạm vi đó cách ô C2 1 hàng về phía dưới và 2 cột về bên phải.
Ví dụ minh họa:
Sao chép dữ liệu ví dụ trong bảng sau đây và dán vào ô A1 của một bảng tính Excel mới. Để công thức hiển thị kết quả, hãy chọn chúng, nhấn F2 và sau đó nhấn Enter. Nếu cần, bạn có thể điều chỉnh độ rộng cột để xem tất cả dữ liệu.
| Công thức | Mô tả | Kết quả |
|---|---|---|
| =OFFSET(D3,3,-2,1,1) | Hiển thị giá trị tại ô B6 (4) | 4 |
| =SUM(OFFSET(D3:F5,3,-2, 3, 3)) | Tính tổng phạm vi B6: D8 | 34 |
| =OFFSET(D3, -3, -3) | Trả về lỗi, vì tham chiếu đến phạm vi không tồn tại trên trang tính. | #REF! |
| Dữ liệu | Dữ liệu | |
| 4 | 10 | |
| 8 | 3 | |
| 3 | 6 |
SỬ DỤNG OFFSET TRONG TRƯỜNG HỢP NÀO:
Bây giờ bạn đã biết hàm OFFSET làm được những gì, vậy nên bạn có thể tự hỏi mình “Tại sao lại phải dùng nó?” Tại sao không chỉ đơn giản là viết một tham chiếu trực tiếp như B4: D4?
Công thức OFFSET rất hữu ích nếu bạn muốn:
Tạo các dải động: Các tham chiếu như B1: C4 là tĩnh, có nghĩa là chúng luôn tham chiếu đến một dải nhất định. Nhưng với một số công việc thì sẽ dễ thực hiện hơn khi làm với dải động. Đây là trường hợp đặc biệt khi bạn làm việc với việc thay đổi dữ liệu, ví dụ: Bạn có một bảng tính, và mỗi tuần sẽ có một hàng hoặc cột mới được thêm vào trong đó.
Lấy dải từ ô gốc: Đôi khi, bạn có thể không biết địa chỉ thực của cả dải, mặc dù bạn có biết rằng nó bắt đầu từ một số ô. Trong các tình huống như vậy, sử dụng OFFSET trong Excel là 1 cách làm đúng.
LÀM THẾ NÀO ĐỂ SỬ DỤNG HÀM OFFSET TRONG EXCEL – CÁC CÔNG THỨC VÍ DỤ:
Tôi hy vọng bạn đã không cảm thấy nản với đống lý thuyết ở trên. Dù sao, bây giờ chúng ta đang thực hiện phần thú vị nhất – áp dụng hàm OFFSET trong tình huống thực tế.
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.
VÍ DỤ 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, 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:
VÍ DỤ 2. CÔNG THỨC 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 Excel 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.
Nếu bạn muốn chọn hàm COUNTA, thì khi đó bạn không cần phải thêm 1, vì hàm này tính tất cả các ô không chứa giá trị rỗng, và hàng tiêu đề có giá trị văn bản được thêm một ô thêm mới mà công thức cần đến. Xin lưu ý rằng công thức này sẽ chỉ hoạt động chính xác trên một cấu trúc bảng tính tương tự – và dòng tiêu đề tiếp theo là hàng có số. Đố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.
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))
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.
CÔNG THỨC OFFSET ĐỂ TẠO RA MỘT DẢI ĐỘNG
Được sử dụng cùng với COUNTA, hàm OFFSET có thể giúp bạn tạo một dải động hữu ích trong nhiều tình huống, ví dụ như khi bạn muốn tạo danh sách theo mục có thể tự động cập nhật.
Công thức OFFSET cho một dải động như sau:
= OFFSET (Sheet_Name! $A$1, 0, 0, COUNTA (Sheet_Name! $A:$A), 1)
Trong đó:
- Reference: được biểu diễn dưới dạng tên bảng và theo sau là dấu chấm than, và địa chỉ của ô có chứa mục đầu tiên có mặt trong trong dải (-đã được đặt tên). Xin lưu ý việc sử dụng các tham chiếu ô tuyệt đối ($), ví dụ Sheet1! $A$1.
- Các tham số Rows and Cols đều là 0, vì không có các cột hoặc các hàng được thêm vào.
- Height là điểm mấu chốt ở đây. Tình huống là bạn sử dụng hàm COUNTA để tính toán số ô không rỗng trong cột có chứa các mục của dải được đặt tên. Với đó, hãy lưu ý rằng trong tham số của COUNTA, bạn nên chỉ định tên bảng bên cạnh tên cột, ví dụ: COUNTA (Sheet_Name!$A: $A).
- Width là 1 cột.
Trên đây là một số mẹo nhỏ giúp bạn dễ dàng chinh phục và sử dụng thuần thục các thao tác của hàm Offset trong Excel. Chúc bạn luôn thành công!
source https://gamehot24h.com/kien-thuc/cach-su-dung-ham-noi-chuoi-trong-offset-don-gian-nhat-756536.html
Không có nhận xét nào:
Đăng nhận xét