Thành thạo Excel ngày nay là điều mà khá nhiều doanh nghiệp yêu cầu từ khía cạnh năng lực của ứng viên. Và trong số các hàm cơ bản của Excel, bạn không thể không biết hàm OFFSET…
Hàm OFFSET trong các hàm Excel là một hàm trả về dữ liệu tham chiếu đến một phạm vi nhất định, được tính bằng 1 ô hoặc một phạm vi ô tương ứng bắt đầu và số hàng hoặc cột được chỉ định bởi người tạo hàm. Bạn có thể chỉ định số lượng thông tin hàng và cột của phạm vi tham chiếu được trả về trong hàm OFFSET.
I. Hàm OFFSET – cú pháp và cách sử dụng cơ bản
1. Các đối số bắt buộc
Tham chiếu – đây là một ô hoặc một phạm vi liền kề của hàm OFFSET trong các hàm cơ bản của Excel, sau đó nó có thể được cộng / trừ (Có thể nói nó là điểm bắt đầu / điểm đánh dấu)
Hàng – Số hàng để di chuyển bắt đầu từ điểm gốc, lên hoặc xuống. Nếu các hàng đã cho là một số dương, điều đó có nghĩa là công thức hàm OFFSET sẽ di chuyển xuống dưới tham chiếu, trong trường hợp là số âm, ngược lại, di chuyển lên trên tham chiếu.
Cols – Số cột bạn muốn thực hiện công thức di chuyển bằng hàm OFFSET, lấy gốc từ điểm bắt đầu. Giống như các hàng, cols trong hàm Excel cơ bản có thể là số dương (ở bên phải của tham chiếu) hoặc âm (ở bên trái của tham chiếu).
2. Các đối số tùy chọn của OFFSET. hàm số
height – chiều cao, tính theo số hàng trong Excel, mà bạn muốn tham chiếu trả về
Width – chiều rộng, về số lượng cột trong Excel mà bạn muốn tham chiếu trả về.
Cả đối số chiều cao và chiều rộng của hàm OFFSET trong các hàm cơ bản của Excel phải luôn là số dương. Nếu một trong hai đối số này bị bỏ qua, chiều cao hoặc chiều rộng của tham chiếu ban đầu, tương ứng, sẽ được sử dụng.
II. Công thức OFFSET trong Excel – những điều cần nhớ
Hàm OFFSET trong các hàm Excel cơ bản không thực sự di chuyển bất kỳ ô hoặc phạm vi nào, những gì nó thực hiện chỉ trả về một tham chiếu.
Khi một công thức hàm OFFSET trả về một dải ô, các đối số hàng và cột tương ứng luôn tham chiếu đến ô phía trên bên trái.
Đối số tham chiếu trong Microsoft Excel phải bao gồm một ô hoặc một dải ô liền kề, nếu không kết quả của công thức cuối cùng của bạn sẽ trả về lỗi #VALUE! lỗi.
Nếu các hàng và / hoặc cột (cols) được chỉ định di chuyển một tham chiếu đã chọn đến cạnh của trang tính, thì hàm OFFSET công thức Excel của bạn sẽ trả về lỗi #REF! lỗi.
Hàm OFFSET có thể được sử dụng trong bất kỳ hàm Excel nào khác chấp nhận dữ liệu dưới dạng tham chiếu ô hoặc phạm vi trong các đối số của nó.
II. Cách sử dụng hàm OFFSET trong Excel – ví dụ công thức
1. Hàm OFFSET và SUM. hàm số
VÍ DỤ CHỨC NĂNG THAY ĐỔI SUM / OFFSET:
Vấn đề ở đây là khi làm việc với một bảng tính cập nhật liên tục, bạn có thể muốn 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ó một dữ liệu nguồn đã cho tương tự như những gì bạn thấy trong hình bên dưới. Mỗi tháng, một hàng dữ liệu mới được thêm ngay trên công thức SUM và đương nhiên, bạn muốn nó được đưa vào tổng số. Về cơ bản, có hai lựa chọn – hoặc thực hiện cập nhật các phạm vi trong công thức SUM mỗi khi nó thay đổi theo cách thủ công hoặc bạn sẽ ngay lập tức áp dụng chức năng OFFSET để thực hiện việc đó cho mình.
Áp dụng ngay OFFSET. hàm số
Khi dữ liệu trong ô đầu tiên của phạm vi được tính tổng (SUM) được tham chiếu trực tiếp trong công thức hàm SUM, bạn chỉ phải cung cấp các tham số cụ thể cho hàm OFFSET và sau đó nó sẽ thực hiện đối số. chiếu tới ô cuối cùng của phạm vi:
Tham chiếu – ô chứa tổng, như trong ví dụ ở đây là ô B9.
Hàng – ô phía trên ô chứa tổng và được đặt từ trái sang phải, bắt buộc phải có một số âm: -1.
Cols – là 0 vì bạn không muốn thay đổi cột.
Từ đó, chúng ta có hàm SUM / hàm OFFSET:
= SUM (ô đầu tiên: (OFFSET (ô có tổng, -1,0))
Đơn giản hóa ví dụ trên, chúng ta sẽ có công thức cuối cùng giống như sau:
= SUM (B2: (OFFSET (B9, -1, 0)))
Và nó thực sự hoạt động khá tốt như trong hình ảnh bên dưới:
Nó thực sự hoạt động khá tốt
2. Sử dụng chức năng OFFSET với AVERAGE, MAX, MIN
Trong ví dụ trên, giả sử bạn muốn xác định số tiền thưởng (Bonus) cho N tháng gần nhất, nhưng không phải tất cả. Và bạn cũng muốn các công thức của mình chạy tự động (bất kể có thêm hàng mới tương ứng nào vào trang tính hay không).
Đối với tác vụ này, chúng tôi sẽ sử dụng một trong các hàm Excel cơ bản – 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
= SUM (OFFSET (B1, COUNTA (B: B) -E1,0, E1,1)))
Sử dụng chức năng OFFSET với AVERAGE, MAX, MIN
IV. Công thức OFFSET để tạo phạm vi động
Được áp dụng thực tế với COUNTA, hàm OFFSET trong các hàm Excel có thể giúp bạn tạo một dải động hữu ích trong nhiều trường hợp, chẳng hạn khi bạn muốn tạo ngay một danh sách thành từng mục có thể tự động cập nhật liên tục. .
Công thức của hàm OFFSET cho một phạm vi động như sau:
= OFFSET (Tên trang tính! $ A $ 1, 0, 0, COUNTA (Tên trang tính! $ A: $ A), 1)
Trong đó:
Tham chiếu: là phần dữ liệu được biểu diễn dưới dạng tên bảng, theo sau là dấu chấm than và địa chỉ của ô trong hàm OFFSET có chứa mục đầu tiên có trong phạm vi (-name). Hãy cẩn thận trong việc sử dụng tham chiếu kiểu ô tuyệt đối ($), ví dụ: Sheet1! $ A $ 1.
Các tham số của Rows và Cols đều bằng 0, vì không có cột hoặc hàng nào được thêm vào.
Chiều cao là phần dữ liệu được coi là điểm mấu chốt ở đây. Tình hình là bạn sử dụng hàm COUNTA trong phần mềm Excel để tính số ô không trống trong cột chứa các mục của dãy đã đặt tên. Cùng với đó, một lưu ý nhỏ là trong tham số của hàm COUNTA, bạn nên chỉ định rõ ràng tên bảng bên cạnh tên cột, ví dụ: COUNTA (Tên_bảng! $ A: $ A).
Chiều rộng là 1 cột.
Khi bạn đã thực hiện phạm vi động (được đặt tên) với công thức hàm OFFSET ở trên, bạn có thể sử dụng chức năng Xác thực dữ liệu Excel để tạo trực tiếp danh sách theo mục – luôn tự động cập nhật ngay cả khi bạn thêm hoặc xóa thủ công các mục khỏi Danh sách nguồn ( sổ làm việc gốc).
Công thức OFFSET để tạo phạm vi động
V. OFFSET và VLOOKUP. chức năng
1. Ví dụ 1. Công thức hàm OFFSET cho một Vlookup nhìn bên trái trong Excel
Một trong những hạn chế đáng kể nhất của hàm VLOOKUP trong Excel là không có khả năng phát hiện các giá trị ở bên trái của nó, có nghĩa là hàm VLOOKUP chỉ có thể trả về một giá trị dữ liệu cụ thể ở bên phải. cột tra cứu.
Trong bảng tra cứu mẫu của chúng tôi, có hai cột – tên tháng (cột A) và tiền thưởng (Bonus) (cột B). Nếu bạn muốn biết bạn sẽ nhận được bao nhiêu tiền thưởng trong một tháng nhất định, công thức của hàm VLOOKUP đơn giản này sẽ hoạt động mà không gặp khó khăn:
= VLOOKUP (B1, A5: B11, 2, FALSE)
Tuy nhiên, ngay sau khi bạn hoán đổi các cột trong bảng tra cứu, điều này sẽ trực tiếp dẫn đến lỗi # N / A:
Trực tiếp dẫn đến # N / A. lỗi
Để xử lý dữ liệu tra cứu phần bên trái, bạn cần một chức năng linh hoạt hơn mà không thực sự quan tâm đến vị trí của cột. Một trong những giải pháp tối ưu nhất có thể được sử dụng là kết hợp giữa hàm INDEX và MATCH. Một cách khác là sử dụng các hàm OFFSET, MATCH và ROWS:
= OFFSET (bảng tra cứu, MATCH (giá trị tra cứu, OFFSET (bảng tra cứu, 0, 1, ROWS (bảng tra cứu), 1), 0) -1, 0, 1, 1)
Trong ví dụ, bảng tra cứu là A5: B9 và giá trị tra cứu nằm trong ô B1, vì vậy công thức trên sẽ có thể triển khai được đối với:
= OFFSET (A5: B9, MATCH (B1, OFFSET (A5: B9, 0, 1, ROWS (A5: B9), 1), 0) -1, 0, 1, 1)
Công thức hàm OFFSET cho một Vlookup nhìn về bên trái trong Excel
2. Ví dụ 2. Cách sử dụng Vlookup thăm dò ở trên trong Excel
Như trường hợp của hàm Vlookup trong Excel không thể phát hiện giá trị ở bên trái, hàm ngang – và tương tự như hàm HLOOKUP – không thể thăm dò giá trị ở trên để xuất ra giá trị.
Nếu bạn cần quét một hàng ở trên để tìm kết quả phù hợp, công thức hàm OFFSET / MATCH / ROWS có thể giúp bạn một lần nữa, nhưng lần này bạn sẽ phải tăng cường hàm COLUMNS như sau:
= OFFSET (bảng tra cứu, 0, MATCH (giá trị tra cứu, OFFSET (bảng tra cứu, ROWS (bảng tra cứu)) -1, 0, 1, COLUMNS (bảng tra cứu)), 0) -1, 1, 1)
Giả sử rằng bảng tra cứu dữ liệu hàm OFFSET là E4: I5 và giá trị tra cứu là vị trí trong ô E1, công thức sẽ như sau:
= OFFSET (E4: I5, 0, MATCH (E1, OFFSET (E4: I5, ROWS (E4: I5)) -1, 0, 1, COLUMNS (E4: I5)), 0) -1, 1, 1)
Cách sử dụng Vlookup thăm dò ở trên trong Excel
TẠI VÌ. Chức năng OFFSET – các hạn chế và lựa chọn thay thế
1. Các hạn chế quan trọng nhất của chức năng OFFSET như sau
Excel OFFSET là một hàm thực sự “ngốn dữ liệu”: Bất kỳ thay đổi nào đối với dữ liệu gốc, các công thức hàm OFFSET của bạn sẽ được tính toán lại, khiến “bộ não” của Excel càng bận rộn hơn. Đây không phải là một vấn đề thực sự lớn đối với một công thức trong một sổ làm việc nhỏ. Nhưng nếu có hàng chục hoặc hàng trăm công thức hàm OFFSET, VLOOKUP … trong sổ làm việc, có thể mất một khoảng thời gian để Microsoft Excel tính toán lại.
Excel xây dựng công thức hàm OFFSET rất khó để xem lại. Bởi vì các tham chiếu dữ liệu được trả về bởi hàm OFFSET trong các hàm Excel là động, đặc biệt là với các công thức thực sự lớn (đặc biệt là với các hàm OFFSET lồng nhau) nên người dùng có thể khá khó gỡ lỗi.
2. Các giải pháp thay thế để sử dụng OFFSET trong Excel
Bảng trong Excel
Hàm chỉ mục: Mặc dù về cơ bản hàm này không giống với hàm OFFSET, nhưng hàm INDEX cũng được sử dụng để thực hiện các tham chiếu dải động. Không giống như hàm OFFSET, hàm INDEX không thay đổi dữ liệu trong đó, vì vậy nó có ưu điểm là không làm chậm Excel của bạn.
Hàm gián tiếp: Sử dụng hàm INDIRECT cơ bản của Excel giúp bạn có thể tạo tham chiếu đến phạm vi động, từ nhiều nguồn chẳng hạn như ô giá trị, giá trị ô và văn bản, và thậm chí đặt phạm vi ô. Tên. Nó cũng có thể tự động thực hiện tham chiếu dữ liệu trong sổ làm việc hoặc trang tính Excel.
VII. Kết luận
Những kiến thức trên là hành trang quan trọng để bạn chinh phục các hàm trong Excel, đặc biệt là hàm OFFSET. Hãy đón đọc những bài viết dưới đây của 123job để học thêm những kỹ năng quan trọng nhé!
Bài viết này giúp bạn nắm bắt được thông tin về các thủ thuật khi sử dụng các ứng dụng văn phòng. Để từ đó có thể sử dụng trực tiếp những thủ thuật này để đáp ứng các yêu cầu của bạn. Việc hiểu biết các thông tin trên sẽ giúp cho công việc của bạn được thực hiện dễ dàng và thuận tiện hơn. Ngoài ra, nếu bạn còn điều gì thắc mắc, hãy truy cập website: https://www.amorstay.com.vn/ để biết thêm các thông tin chi tiết hơn nhé.