Công thức bù đắp trong Microsoft Excel

Posted on: 2020年11月13日 2020年11月13日
Tags: tips

VLOOKUP, HLOOKUP và INDEX-MATCH là những cách nổi tiếng và phổ biến để tra cứu giá trị trong bảng Excel. Nhưng đây không phải là cách duy nhất để tra cứu giá trị trong Excel. Trong bài viết này, chúng ta sẽ học cách sử dụng hàm OFFSET cùng với hàm MATCH trong Excel. Vâng, bạn đã đọc đúng, chúng tôi sẽ sử dụng hàm OFFSET khét tiếng của Excel để tra cứu một giá trị nhất định trong Bảng Excel.

Công thức chung

Đọc kỹ phần này:

StartCell: Đây là ô bắt đầu của Bảng tra cứu. Giả sử nếu bạn muốn tra cứu trong phạm vi A2: A10, thì StartCell sẽ là A1.

RowLookupValue: Đây là giá trị tra cứu mà bạn muốn tìm trong các hàng bên dưới StartCell. RowLookupRange: Đây là phạm vi mà bạn muốn tra cứu RowLookupValue. Nó là phạm vi bên dưới StartCell (A2: A10). ColLookupValue: Đây là giá trị tra cứu mà bạn muốn tìm trong các cột (tiêu đề).

ColLookupRange: Đây là phạm vi mà bạn muốn tra cứu ColLookupValue. Nó là phạm vi ở phía bên phải của StartCell (như B1: D1).

Vậy là đủ về lý thuyết. Hãy bắt đầu với một ví dụ.

Ví dụ: Tra cứu Doanh số sử dụng hàm OFFSET và MATCH Từ Bảng Excel Ở đây chúng ta có một bảng mẫu về doanh số được thực hiện bởi các nhân viên khác nhau trong các tháng khác nhau.

Bây giờ sếp của chúng tôi đang yêu cầu chúng tôi cung cấp doanh số bán hàng được thực hiện bởi Id 1004 trong tháng 6. Có nhiều cách để thực hiện nhưng vì chúng ta đang tìm hiểu về công thức OFFSET-MATCH nên chúng ta sẽ sử dụng chúng để tra cứu giá trị mong muốn.

Chúng ta đã có công thức chung ở trên. Chúng ta chỉ cần xác định các biến này trong bảng này.

StartCell là B1 ở đây. RowLookupValue là M1. RowLookupRange là B2: B1o (phạm vi bên dưới ô bắt đầu).

ColLookupValue nằm trong ô M2. ColLookupRange là C1: I1 (Phạm vi tiêu đề ở bên phải của StartCell).

Chúng tôi đã xác định tất cả các biến. Hãy đặt chúng vào một Công thức Excel.

Viết công thức này vào Ô M3 và nhấn nút enter.

Khi bạn nhấn nút enter, bạn sẽ nhận được kết quả ngay lập tức. Bán được ID 1004 trong Tháng 6 là 177.

Nó hoạt động như thế nào?

Ở đây, Chúng tôi có công thức

OFFSET(B1,MATCH(M1,B2:B10,0),MATCH(M2,C1:I1,0))

Bây giờ công thức là

OFFSET(B1,4,MATCH(M2,C1:I1,0))

Hàm MATCH tiếp theo trả về chỉ số của M2 (‘Jun’) trong phạm vi C1: I1, I7. Bây giờ công thức là OFFSET (B1,4,7). Bây giờ, hàm OFFSET chỉ cần di chuyển 4 ô xuống ô B1 đưa nó đến ô B5. Sau đó, hàm OFFSET di chuyển sang trái 7 sang B5, đưa nó đến I5. Đó là nó. Hàm OFFSET trả về giá trị từ ô I5 là 177.

Ưu điểm của kỹ thuật tra cứu này?

Điều này là nhanh chóng. Ưu điểm duy nhất của phương pháp này là nó nhanh hơn các phương pháp khác. Điều tương tự có thể được thực hiện bằng cách sử dụng hàm INDEX-MATCH hoặc hàm VLOOKUP. Nhưng thật tốt nếu biết một số lựa chọn thay thế.

Nó có thể hữu ích vào một ngày nào đó.

Dưới đây là một số lưu ý về việc sử dụng công thức bù đắp trong Excel.

Ghi chú:

OFFSET chỉ trả về một tham chiếu, không có ô nào được di chuyển.
Cả hai hàng và cols đều có thể được cung cấp dưới dạng số âm để đảo ngược hướng bù trừ thông thường của chúng – cols âm bù sang trái và các hàng âm bù ở trên.
OFFSET là một “hàm dễ bay hơi” – nó sẽ tính toán lại với mỗi lần thay đổi trang tính. Các chức năng linh hoạt có thể làm cho các sổ làm việc lớn hơn và phức tạp hơn chạy chậm.
OFFSET sẽ hiển thị lỗi #REF! giá trị lỗi nếu phần bù nằm ngoài cạnh của trang tính.
Khi chiều cao hoặc chiều rộng bị bỏ qua, chiều cao và chiều rộng của tham chiếu sẽ được sử dụng.
OFFSET có thể được sử dụng với bất kỳ chức năng nào khác mong đợi nhận được tham chiếu.
Tài liệu Excel cho biết chiều cao và chiều rộng không được âm, nhưng giá trị âm vẫn hoạt động.

Hy vọng bài viết này về Cách sử dụng Công thức bù đắp trong Microsoft Excel là giải thích. Tìm thêm các bài viết về giá trị tra cứu và công thức Excel liên quan tại đây. Nếu bạn thích blog của chúng tôi, hãy chia sẻ nó với bạn bè của bạn trên Facebook. Và bạn cũng có thể theo dõi chúng tôi trên Twitter và Facebook. Chúng tôi rất muốn nghe ý kiến của bạn, hãy cho chúng tôi biết cách chúng tôi có thể cải thiện, bổ sung hoặc đổi mới công việc của mình và làm cho nó tốt hơn cho bạn. Viết thư cho chúng tôi tại [email protected].

Bài viết phổ biến:

liên kết: / công thức-và-chức-năng-giới-thiệu-của-vlookup-chức-năng [Excel]

Bạn không cần phải lọc dữ liệu của mình để đếm các giá trị cụ thể. Hàm COUNTIF rất cần thiết để chuẩn bị trang tổng quan của bạn.