Sử dụng hàm Offset trong Excel [Offset - Match Combo, Dynamic Range]

Hôm nay tôi muốn giới thiệu với bạn về Hàm OFFSET của Excel với 3 ví dụ thực tế.

Đầu tiên, tôi sẽ mô tả cú pháp công thức và sau đó tôi sẽ nói về cách hàm OFFSET có thể được sử dụng để giải quyết các vấn đề trong cuộc sống thực.

Giới thiệu

Hàm OFFSET có thể trả về một tham chiếu đến một ô (hãy gọi nó là ô đích) hoặc phạm vi (phạm vi mục tiêu) là một số hàng và cột được chỉ định cách xa ô khác (ô tham chiếu) hoặc phạm vi (phạm vi tham chiếu).

Hình bên dưới minh họa cách sử dụng hàm OFFSET để trả về tham chiếu đến một ô (phần bên trái) hoặc một dải ô (phần bên phải).

Nó sẽ cho bạn ấn tượng trực quan về ô đích và ô tham chiếu là gì.

Ô được đánh dấu bằng màu xanh lá cây là ô đích trong khi các ô được đánh dấu bằng màu vàng bao gồm một phạm vi mục tiêu.

Các ô được đánh dấu màu xanh lam là các ô tham chiếu.

Hình 1

OFFSET có nghĩa là gì trong Excel (cú pháp)?

Đây là cú pháp của Hàm Offset: OFFSET (tham chiếu, hàng, cột, [chiều cao], [chiều rộng])

Bây giờ, hãy để tôi chỉ cho bạn cách sử dụng hàm OFFSET để giải quyết các vấn đề trong cuộc sống thực.

Trường hợp 1:Tra cứu từ phải sang trái bằng cách kết hợp các hàm OFFSET và MATCH

Ai cũng biết rằng bạn chỉ có thể thực hiện tra cứu từ trái sang phải với hàm VLOOKUP.

Giá trị cần tìm phải được đặt trong cột đầu tiên của mảng bảng của bạn.

Bạn phải chuyển toàn bộ phạm vi bảng của mình sang bên phải một cột nếu bạn muốn thêm giá trị tra cứu mới hoặc bạn cần thay đổi cấu trúc dữ liệu của mình nếu bạn muốn sử dụng một cột khác làm giá trị tra cứu.

Nhưng bằng cách kết hợp OFFSET với hàm Match, giới hạn của hàm VLOOKUP có thể được loại bỏ.

Hàm MATCH là gì và làm cách nào chúng ta có thể kết hợp hàm OFFSET với hàm Khớp để thực hiện tra cứu?

Vâng, hàm Đối sánh tìm kiếm một mục được chỉ định trong một phạm vi ô và sau đó trả về vị trí tương đối của mục đó trong phạm vi.

Hãy lấy phạm vi B3:B8 từ Hình 2.1 (cho thấy doanh thu của các quốc gia khác nhau trong những năm khác nhau) làm ví dụ.

Công thức “=MATCH (“ USA ”, B3:B8, 0)” sẽ trả về 1 vì Hoa Kỳ là mục đầu tiên trong phạm vi (xem ô B10 và C10).

Đối với một dải ô khác C2:F2, công thức “=MATCH (2015, C2:F2, 0)” trả về 3 vì 2015 là mục thứ ba trong phạm vi (xem ô B11 và C11).

Quay lại chức năng OFFSET.

Nếu chúng ta đặt ô B2 làm ô tham chiếu và lấy ô E3 làm ô đích, làm cách nào chúng ta có thể viết công thức OFFSET?

E3 là 1 hàng bên dưới B2 và 3 cột bên phải B2.

Do đó, công thức có thể được viết là “=OFFSET (B2, 1 , 3 ) ”. Nhìn kỹ các con số có màu đỏ, bạn có thấy chúng trùng khớp nhau không?

Đó là câu trả lời cho câu hỏi - Cách kết hợp hàm OFFSET với hàm Match - Có thể áp dụng hàm Match để làm đối số thứ hai hoặc thứ ba của hàm OFFSET (xem ô C13).

Ô C14 trình bày cách sử dụng hàm VLOOKUP để truy xuất cùng một dữ liệu.

Chúng ta phải biết doanh thu năm 2015 được ghi nhận vào thứ 4 cột của mảng bảng B2:F8 trước khi viết hàm VLOOKUP.

Có nghĩa là chúng ta phải biết rất rõ về cấu trúc dữ liệu khi sử dụng hàm VLOOKUP.

Đây là một hạn chế khác đối với hàm VLOOKUP. Tuy nhiên, bằng cách sử dụng hàm MATCH làm đối số của hàm OFFSET, chúng tôi không cần biết chỉ số cột.

Điều này rất hữu ích nếu có nhiều cột.

Hình 2.1

Bây giờ, hãy tiếp tục và xem một ví dụ phức tạp hơn.

Giả sử rằng chúng ta có một bảng chứa Tên công ty, Tên liên hệ và Địa chỉ email cho các công ty khác nhau.

Và chúng tôi muốn lấy tên công ty từ một tên liên hệ đã biết hoặc lấy tên liên hệ từ một địa chỉ email đã biết. Chúng ta có thể làm gì？

Xem Hình 2.2, phạm vi B5:E8 bao gồm thông tin công ty. Bằng cách đặt đầu vào trong ô C2 và ô B3, với sự trợ giúp của công thức trong ô vuông màu đỏ, tôi có thể truy xuất tên công ty nếu tôi biết tên liên hệ.

Phạm vi D2:E4 cho biết cách lấy tên liên hệ với một địa chỉ email đã biết.

Tóm lại, hai ví dụ này minh họa rằng chúng ta có thể thực hiện tra cứu từ phải sang trái và giá trị tìm kiếm không cần đặt ở cột ngoài cùng bên phải. Bất kỳ cột nào trong mảng bảng đều có thể chứa giá trị tìm kiếm.

Hình 2.2

Trường hợp 2:Tính toán tự động kết hợp các hàm OFFSET và COUNT

Trước khi giới thiệu về cách tự động tính toán bất cứ khi nào chúng ta thêm một số mới vào một cột, trước tiên hãy bắt đầu với cách trả về số cuối cùng trong cột một cách tự động.

Nhìn vào hình bên dưới cho thấy các mục nhập từ Bộ phận Nhân sự. Giả sử rằng chúng ta muốn lấy số cuối cùng trong Cột B, công thức sẽ là “=OFFSET (C2, 9 , 0) ”nếu chúng tôi áp dụng chức năng OFFSET.

Từ công thức, chúng ta có thể biết rằng 9 là số chính.

Miễn là chúng tôi có thể tự động trả lại số này, chúng tôi có thể tự động tìm số cuối cùng trong cột.

9 chỉ là số ô chứa các số trong cột C.

Nếu bạn đã quen thuộc với hàm COUNT, bạn sẽ biết rằng hàm COUNT có thể đếm số ô chứa số trong một phạm vi.

Ví dụ:công thức “=COUNT (C3:C11)” sẽ đếm số ô chứa các số trong các ô từ C3 đến C11.

Trong trường hợp của chúng tôi, chúng tôi muốn biết có bao nhiêu số trong toàn bộ cột, do đó, nên sử dụng tham chiếu như C:C bao gồm tất cả các hàng trong cột C.

Vui lòng nhìn vào ô G4 và H4, số được trả về bởi “=COUNT (C:C)” chính xác bằng 9 .

Do đó, bằng cách thay thế 9 bằng COUNT (C:C) trong hàm OFFSET ở trên, chúng ta có thể nhận được một công thức mới “=OFFSET (C2, COUNT (C:C) , 0) ”(trong ô H5).

Số nó trả về là 87000, chính xác là số cuối cùng trong cột C.

Bây giờ hãy chuyển sang tính toán tự động. Giả sử rằng chúng ta muốn tổng tất cả các số trong cột C.

Công thức sẽ là “=SUM (OFFSET (C2, 1, 0, 9 , 1)) ”nếu chúng ta sử dụng SUM cùng với OFFSET.

9 là tổng số hàng trong phạm vi C3:C11 và cũng là tổng số ô chứa các số trong cột C.

Do đó, chúng ta có thể viết công thức theo một cách mới như “=SUM (OFFSET (C2,1, 0, COUNT (C:C), 1))”.

Nhìn vào ô G10 và H10, tổng số tiền lương của 9 nhân viên này là $ 521,700.

Bây giờ, nếu bạn đặt một số như 34.000 đô la vào ô C12, cả số trong ô G5 và G10 sẽ lần lượt được thay đổi thành 34.000 đô la và 555.700 đô la.

Đây là cái mà tôi gọi là tự động hóa vì bạn không phải cập nhật công thức trong ô G5 hoặc G10.

Bạn phải cẩn thận khi sử dụng hàm COUNT vì hàm COUNT chỉ trả về số ô chứa số.

Ví dụ:“=COUNT (B:B)” trả về 0 thay vì 9 vì không có ô nào trong cột B chứa số (xem ô G3 và H3).

Cột D bao gồm 10 ô chứa các số và số được trả về bởi “COUNT (D:D)” cũng là 10.

Nhưng nếu chúng ta muốn truy xuất số cuối cùng trong cột D như chúng ta đã làm cho cột C, chúng ta sẽ nhận được số 0 (xem ô G8 và H8).

Rõ ràng, con số 0 không phải là điều chúng ta muốn. Chuyện gì vậy? Ô D13 cách ô D2 11 hàng thay vì 10 hàng.

Điều này cũng có thể được chứng minh bằng công thức “=OFFSET (D2, COUNT (D:D) + 1 , 0) ”trong ô G7.

Tóm lại, các số phải liền kề nhau nếu chúng ta muốn sử dụng hàm COUNT cùng với hàm OFFSET để cho phép tự động hóa tính toán.

Hình 3

Trường hợp 3:Sử dụng hàm OFFSET để tạo phạm vi động

Giả sử rằng chúng ta muốn lập biểu đồ doanh số bán hàng tháng của một công ty và Hình 4.1 hiển thị dữ liệu hiện tại và một biểu đồ được tạo dựa trên dữ liệu hiện tại.

Mỗi tháng, doanh số bán đơn vị của tháng gần đây nhất sẽ được thêm vào bên dưới số cuối cùng trong cột C.

Có cách nào dễ dàng để cập nhật biểu đồ tự động không?

Chìa khóa để cập nhật biểu đồ là sử dụng hàm OFFSET để tạo tên phạm vi động cho cột Đơn vị đã bán.

Phạm vi động cho doanh số bán hàng của đơn vị sẽ tự động bao gồm tất cả dữ liệu bán hàng khi dữ liệu mới được nhập.

Hình 4.1

Để tạo phạm vi động, hãy nhấp vào Công thức và sau đó chọn Trình quản lý tên hoặc Xác định tên .

Bên dưới Tên mới hộp thoại sẽ nhắc nếu bạn nhấp vào Xác định tên .

Nếu bạn chọn Người quản lý tên , bạn cũng cần nhấp vào Mới để tạo Tên mới bên dưới hộp thoại xuất hiện.

Hình 4.2

Trong phần “ Tên: "Hộp nhập liệu, tên phạm vi động phải được điền vào. Và trong" Đề cập đến:" hộp nhập liệu, chúng ta cần nhập công thức OFFSET “=OFFSET (Hình4! $ C $ 2, 1, 0, COUNT (! $ C:$ C), 1)” sẽ tạo ra một dải giá trị động dựa trên các giá trị Đơn vị đã Bán được đánh vào cột C.

Theo mặc định, một tên sẽ áp dụng cho toàn bộ sổ làm việc và phải là duy nhất trong sổ làm việc.

Tuy nhiên, chúng tôi muốn giới hạn phạm vi trong một trang tính cụ thể.

Do đó, chúng tôi chọn Hình 4 ở đây trong “ Phạm vi: ”Hộp nhập liệu. Sau khi nhấp vào OK , phạm vi động được tạo.

Nó sẽ tự động bao gồm tất cả dữ liệu bán hàng khi dữ liệu mới được nhập.

Bây giờ hãy nhấp chuột phải vào bất kỳ điểm nào trong biểu đồ và sau đó chọn “Chọn dữ liệu”.

Hình 4.3

Trong mục Chọn dữ liệu được nhắc Nguồn, chọn Series1 và sau đó Chỉnh sửa.

Hình 4.4

Và sau đó nhập “=Hình4! Đơn vị” như Hình 4.5 cho thấy.

Hình 4.5

Cuối cùng, hãy thử và nhập 11 vào ô C13. Bạn có thể thấy biểu đồ đã thay đổi và giá trị 11 đã được bao gồm.

Biểu đồ sẽ tự động thay đổi khi dữ liệu mới được thêm vào.

Hình 4.6

Đọc thêm…

• Hàm Offset (…) trong Excel với các ví dụ

Tải xuống các tệp đang làm việc

Tải xuống các tệp hoạt động từ liên kết bên dưới.