Tìm hiểu hàm offset là gì trong excel

Tìm hiểu hàm offset là gì trong excel

Hiện nay có rất nhiều người chưa hiểu hàm offset là gì, cách sử dụng hàm này ra sao. Để giúp các bạn có thể nắm vững cách sử dụng hàm này hơn thì mình đã tổng hợp một số thông tin cần thiết từ rất nhiều nguồn khác nhau có chuyên môn về excel. Các bạn hãy tham khảo ngay nhé.

 

Định nghĩa hàm offset là gì

Để có được một ý tưởng cơ bản về những gì bù đắp đang làm, chúng ta hãy xem một bảng đơn giản. Nó có một cột numelic và một cột Letter.

Chúng ta hãy xem xét một ví dụ đơn giản, trước tiên hãy chọn B2 làm đối số tham chiếu của chúng ta và sau đó là 1 cho chuỗi của chúng ta và 0 cho bắp cải của chúng ta.

Điều này sẽ trả về giá trị của ô B3 (1 hàng xuống từ B2 trong cùng một cột).

Bây giờ chúng ta hãy xem điều gì sẽ xảy ra khi kết quả của công thức OFFSET của chúng ta là một ô hoàn toàn không chứa giá trị.

Ba hàng xuống và hai cột ở bên trái của ô C5 là một ô A8 không quan trọng. Do đó, công thức trả về giá trị của 0.

Bởi vì các đối số cho chuỗi và bắp cải là giá trị số, bạn cũng có thể sử dụng liên kết đến các ô.

Trong trường hợp này, giá trị của ô A5 là 4, vì vậy đây là dòng đối số của chúng ta. 4 hàng xuống và 0 ở bên phải của B2 là B6.

Sau đó, công thức OFFSET của chúng tôi trả về giá trị của E.

Bây giờ chúng ta hãy xem điều gì sẽ xảy ra khi chúng ta chọn một giá trị chuỗi hoặc cụm từ không nằm trong phạm vi trang tính của chúng ta.

Chúng ta có #REF! lỗi ở đây vì không có phạm vi tồn tại hai hàng phía trên hoặc hai cột ở bên trái của ô B2.

Làm thế nào về sự trở lại của một số ô

Cho đến nay, tất cả những gì chúng ta đã thấy là các công thức trong đó chúng ta hạ thấp các đối số về chiều cao và chiều rộng.

Bằng cách cung cấp các đối số này, bạn có thể làm được nhiều việc hơn với OFFSET.

Giả sử chúng ta sử dụng OFFSET để chọn một phạm vi ô trong bảng của chúng ta có chứa số.

Chúng ta cũng có thể sử dụng một tính năng bổ sung để tạo một công thức cho tổng của tất cả các số trong phạm vi đó.

Tất cả những gì chúng tôi đã làm ở đây là OFFSET 1 hàng xuống từ B2 và chỉ ra phạm vi 5 hàng và chiều rộng của một cột.

Sau đó, chúng tôi bọc nó trong một công thức SUM đơn giản.

Do đó, chúng ta nhận được tổng của tất cả các giá trị số trong phạm vi này (15 x 1 và 2 và 3 và 4 và 5).

Tìm hiểu thêm về cách sử dụng OFFSET với các tính năng khác

Hãy tiếp tục sử dụng OFFSET với các tính năng khác để tạo ra một số công thức thú vị.

Hãy xem xét bảng sau đây với điểm trung bình của lớp trong phạm vi các lớp A, B, C và D này.

Chúng ta có thể sử dụng tính năng AVERAGE để có được kết quả ở cột Middle ở bên phải.

Với một chút dí dỏm, chúng ta có thể sử dụng tính năng OFFSET để đạt được kết quả tương tự như số lượng mẫu của chúng ta ở trên.

Trong tập hợp các công thức này, chúng tôi đã thiết lập B2 làm đối số tham chiếu của chúng tôi.

Lưu ý rằng chúng tôi thay đổi đường đối số trong mỗi hàng tiếp theo để phù hợp với điều này.

Giá trị của đối số cols di chuyển OFFSET sang cột bên phải của cột Letter Grade.

Giá trị chiều cao và chiều rộng đặt phạm vi trả về kích thước của một hàng và ba cột rộng.

Điều này bao gồm phạm vi của mỗi điểm trung lưu theo lớp chữ cái.

Bằng cách kết thúc điều này vào công thức AVERAGE, chúng tôi nhận được điểm trung bình cho cả ba lớp cho mỗi chữ cái lớp.

Mở rộng sử dụng OFFSET

Việc sử dụng OFFSET đặc biệt phổ biến là tạo ra một loạt các tên động trong Excel.

Dải tên động mở rộng hoặc chia đôi khi người dùng thêm hoặc xóa dữ liệu khỏi dữ liệu hiện có.

Điều này rất hữu ích để tự động hóa những thứ như danh sách thả menu và hộp tổ hợp.

Chèn hàm COUNTA dưới dạng đối số chiều cao hoặc chiều rộng vào công thức OFFSET.

Kích thước của phạm vi trả về có thể thay đổi tùy thuộc vào kết quả.

Nếu bạn không quen thuộc với COUNTA, bạn nên biết rằng nó đếm số lượng ô trong phạm vi chứa dữ liệu.

Nếu chúng ta có danh sách tên trong cột A, COUNTA sẽ đếm số lượng tên trong cột này.

Khi tên được thêm hoặc xóa, kết quả của công thức COUNTA của chúng tôi thay đổi. Điều này cho phép chiều cao của phạm vi kết quả tự động mở rộng hoặc chia nhỏ.

Chúng ta hãy xem xét kỹ hơn công thức này sẽ trông như thế nào.

Mặc dù công thức này khá phức tạp, nhưng kết luận là điều này.

Counta turbo tải xuống công thức OFFSET để tự động hóa ưu đãi thủ công theo một cách khác.

Công thức này cho phép người dùng thêm và loại bỏ tên khỏi danh sách mà không phải tạo lại chúng mỗi lần.

 

Cách sử dụng hàm offset

Tóm tắt

Excel OFFSET trả về nối kết đến một phạm vi được xây dựng với năm đầu vào: (1) điểm bắt đầu, (2) dịch chuyển dòng, (3) dịch chuyển cột, (4) chiều cao theo hàng, (5) chiều rộng trong cột. OFFSET rất hữu ích trong các công thức yêu cầu dải động.

Formulas to query a table

Mục tiêu

Tạo thiên vị tham chiếu từ điểm bắt đầu này

Giá trị trả về

Nối kết đến ô.

Cú pháp

OFFSET (tham chiếu, hàng, bắp cải, “chiều cao”, “chiều rộng”)

Đối số

Liên kết: Điểm bắt đầu được cung cấp dưới dạng tham chiếu hoặc phạm vi ô.

Dòng: Số dòng đã hết hạn bên dưới liên kết ban đầu.

cụm từ: Số cột đã hết hạn ở bên phải của liên kết ban đầu.

Chiều cao – chiều cao “tùy chọn” trong hàng ngũ của liên kết trả về.

chiều rộng – chiều rộng “tùy chọn” trong các cột của liên kết trả về.

Phiên bản

Excel 2003

Ghi chú để sử dụng

Excel OFFSET trả về một dải động được xây dựng với năm đầu vào: (1) điểm bắt đầu, (2) dịch chuyển dòng, (3) dịch chuyển cột, (4) chiều cao theo hàng, (5) chiều rộng trong cột.

Điểm bắt đầu (đối số) có thể là một ô hoặc một số ô. Đối số chuỗi và cụm từ là số ô “di chuyển” từ điểm bắt đầu. Đối số chiều cao và chiều rộng là tùy chọn và xác định kích thước của phạm vi được tạo. Khi chiều cao và chiều rộng được hạ xuống, mặc định là ở mức chiều cao và chiều rộng tham chiếu.

Ví dụ: đối với liên kết A1 C5, liên kết A1, dòng 4 và cols 2:

Xem thêm:  Mẫu đề thi hsg địa 9 năm 2021

=OFFSET(A1,4,2) // returns reference to C5

Đối với liên kết C1:C5 từ A1, liên kết A1, dòng 0, cols 2, chiều cao 5 và chiều rộng 1:

=OFFSET(A1,0,2,5,1) // returns reference to C1:C5

Lưu ý: Chiều rộng có thể được hạ xuống vì nó sẽ được đặt ở mức 1 theo mặc định.

Bạn thường có thể thấy rằng OFFSET được bao bọc trong một tính năng khác đang chờ phạm vi. Ví dụ: SUM C1:C5, bắt đầu bằng A1:

=SUM(OFFSET(A1,0,2,5,1)) // SUM C1:C5

Mục đích chính của OFFSET là cho phép các công thức tự động khớp với dữ liệu có sẵn hoặc đầu vào của người dùng. OFFSET có thể được sử dụng để tạo một dải tên động cho sơ đồ hoặc PivotTable để đảm bảo rằng dữ liệu gốc luôn được cập nhật.

Lưu ý: Tài liệu Excel chỉ ra rằng chiều cao và chiều rộng có thể không âm, nhưng các giá trị âm dường như đã hoạt động tốt kể từ đầu những năm 1990. GOOGLE Trang tính OFFSET không cho phép giá trị âm của đối số chiều cao hoặc chiều rộng.

Ví dụ

Các ví dụ sau đây cho thấy cách OFFSET có thể được cấu hình để trả về các loại phạm vi khác nhau. Các màn hình này đã bị loại bỏ khỏi Excel 365, vì vậy OFFSET trả về một mảng động khi kết quả có nhiều ô. Trong các phiên bản Excel cũ hơn, bạn có thể sử dụng phím F9 để kiểm tra kết quả OFFSET trả về.

Một ví dụ về #1

Trên màn hình tiếp theo, chúng ta sử dụng OFFSET để trả về giá trị thứ ba (tháng 3) về cột thứ hai (West). Công thức trong H4:

=OFFSET(B3,3,2) // returns D6

Một ví dụ về #2

Trên màn hình tiếp theo, chúng ta sử dụng OFFSET để trả về giá trị cuối cùng (tháng 6) trong cột thứ ba (phía bắc). Công thức trong H4:

=OFFSET(B3,6,3) // returns E9

Ví dụ #3

Sau đó, chúng tôi sử dụng OFFSET để trả về tất cả các giá trị trong cột thứ ba (bắc). Công thức H4:

=OFFSET(B3,1,3,6) // returns E4:E9

Một ví dụ về #4

Sau đó, chúng ta sử dụng OFFSET để trả về tất cả các giá trị tháng 5 (dòng thứ năm). Công thức trong H4:

=OFFSET(B3,5,1,1,4) // returns C8:F8

Một ví dụ về #5

Sau đó, chúng tôi sử dụng OFFSET để trả lại tháng 4, tháng 5 và tháng 6 cho khu vực phía Tây. Công thức trong H4:

=OFFSET(B3,4,2,3,1) // returns D7:D9

Một ví dụ về #6

Sau đó, chúng tôi sử dụng OFFSET để trả lại giá trị của tháng 4, tháng 5 và tháng 6 cho phương Tây và miền Bắc. Công thức trong H4:

=OFFSET(B3,4,2,3,2) // returns D7:E9

 

Mẫu hàm offset

Excel OFFSET: Sử dụng cú pháp và khóa

Tính năng OFFSET trong Excel trả về một ô hoặc phạm vi ô, tức là một số hàng và cột nhất định trong một ô hoặc phạm vi đã cho.

Cú pháp của hàm offset như sau:

OFFSET (tham chiếu, hàng, bắp cải, “chiều cao”, “chiều rộng”)

Ba đối số đầu tiên là cần thiết và hai đối số cuối cùng là tùy chọn. Tất cả các đối số có thể là tham chiếu đến các ô hoặc kết quả khác được trả về bởi các công thức khác.

Có vẻ như Microsoft đã nỗ lực tốt để đưa một số ý nghĩa vào cài đặt tên và họ đưa ra một gợi ý về những gì bạn nên chỉ ra trong mỗi cài đặt.

Đối số là bắt buộc:

Trợ giúp là một ô hoặc phạm vi của các ô liền kề mà từ đó bù đắp dựa trên. Bạn có thể nghĩ về điều này như một điểm khởi đầu.

Dòng: Số dòng di chuyển từ điểm bắt đầu, lên hoặc xuống. Nếu các đường là số dương, công thức sẽ di chuyển bên dưới liên kết ban đầu nếu số âm vượt quá liên kết ban đầu.

Thông tin đại chúng: Số cột bạn muốn công thức di chuyển từ điểm bắt đầu. Ngoài các chuỗi, cols có thể dương (ở bên phải của liên kết gốc) hoặc âm bản (bên trái của liên kết gốc).

Đối số bổ sung:

Chiều cao – chiều cao, theo số hàng trả về liên kết.

Chiều rộng: Chiều rộng, theo số cột được trả về.

Các đối số về chiều cao và chiều rộng phải luôn là số dương. Nếu bất kỳ trong số chúng bị bỏ qua, chiều cao hoặc chiều rộng của liên kết ban đầu được sử dụng.

Bây giờ chúng ta hãy minh họa lý thuyết với công thức OFFSET đơn giản nhất.

Ví dụ về công thức thiên vị Excel

Công thức OFFSET sau trả về nối kết ô dựa trên điểm bắt đầu, hàng và cổ phần bạn chỉ định:

– BÙ ĐẮP (A1,3,1)

Công thức yêu cầu Excel lấy ô A1 làm điểm bắt đầu (nối kết) rồi di chuyển 3 hàng xuống dưới (đường đối số) và 1 cột sang trái (đối số cols). Do đó, công thức OFFSET này trả về giá trị trong ô B4.

Hình ảnh bên trái hiển thị đường dẫn của hàm và ảnh chụp màn hình ở bên phải cho thấy cách bạn có thể sử dụng công thức OFFSET trong dữ liệu thực. Sự khác biệt duy nhất giữa hai công thức là công thức thứ hai (phải) bao gồm một nối kết đến ô (E1) trong đường đối số. Nhưng vì ô E1 chứa số 3 và chính xác cùng một số xuất hiện trong đối số dòng công thức đầu tiên, cả hai sẽ trả về cùng một kết quả – giá trị trong B4.

Công thức thiên vị Excel – những điều cần nhớ

Tính năng OFFSET là Excel không di chuyển ô hoặc phạm vi, nó chỉ trả về một liên kết.

Khi công thức OFFSET trả về một số ô, các đường và đối số của các cols luôn tham chiếu đến ô phía trên bên trái trong cơn thịnh nộ trả về.

Đối số nền phải bao gồm một ô hoặc một phạm vi các ô liền kề, nếu không công thức của nó sẽ #VALUE! Lỗi.

Nếu các dòng và/hoặc cols này di chuyển liên kết qua ranh giới của trang tính, Excel OFFSET sẽ trả về #REF! Lỗi.

Tính năng OFFSET có thể được sử dụng trong bất kỳ tính năng Excel nào khác chấp nhận nối kết ô/dải trong các đối số của nó.

Ví dụ: nếu bạn cố gắng sử dụng công thức -OFFSET (A1,3,1,1,3) của chính mình, nó sẽ bắt đầu #VALUE! vì phạm vi cần được trả về (1 hàng, 3 cột) không vừa với một ô. Tuy nhiên, nếu bạn nhúng nó vào hàm SUM, như sau:

TỔNG (OFFSET (A1,3,1,1,3))

Công thức sẽ trả về số lượng giá trị trong phạm vi từ 1 dòng đến 3 cột, là 3 hàng bên dưới và 1 cột ở bên phải ô A1, nghĩa là tổng giá trị trong các ô B4:D4.

Tại sao tôi sử dụng OFFSET trong Excel?

Bây giờ bạn đã biết OFFSET làm gì, bạn có thể tự hỏi, “Tại sao lại sử dụng nó?”, tại sao không chỉ viết một liên kết trực tiếp như B4: D4?

Bù đắp công thức Excel rất tốt cho:

Tạo dải tương phản động: Các liên kết như B1:C4 là tĩnh, có nghĩa là chúng luôn thuộc về một phạm vi nhất định. Nhưng một số tác vụ dễ thực hiện hơn với dải tương phản động. Điều này đặc biệt đúng khi làm việc với việc thay đổi dữ liệu, ví dụ: bạn có một trang tính nơi một dòng hoặc cột mới được thêm vào mỗi tuần.

Xem thêm:  Mẫu giáo án dạy học theo chủ đề môn ngữ văn 8 năm 2021

Lấy phạm vi của ô gốc. Đôi khi bạn có thể không biết hướng thực tế của phạm vi, mặc dù bạn biết rằng nó bắt đầu với một ô nhất định. Trong những trường hợp như vậy, sử dụng OFFSET trong Excel là cách đúng đắn để đi.

Cách sử dụng OFFSET trong Excel – ví dụ công thức

Tôi hy vọng bạn không cảm thấy mệt mỏi với quá nhiều lý thuyết. Dù sao, bây giờ chúng ta đang đi đến phần thú vị nhất – việc sử dụng thực tế tính năng OFFSET.

Các tính năng của Excel OFFSET và SUM

Ví dụ chúng ta đã thảo luận cách đây một phút cho thấy việc sử dụng OFFSET SUM đơn giản nhất. Bây giờ chúng ta hãy xem xét các tính năng này từ một góc độ khác và xem chúng có thể làm gì khác.

Ví dụ 1. Công thức động SUM/OFFSET

Khi bạn đang làm việc trên các trang tính được cập nhật liên tục, bạn có thể cần một công thức SUM tự động chọn tất cả các dòng mới được thêm vào.

Giả sử bạn có dữ liệu nguồn tương tự như dữ liệu bạn thấy trong ảnh chụp màn hình tiếp theo. Mỗi tháng, một dòng mới được thêm ngay phía trên công thức SUM và tất nhiên bạn muốn đưa nó vào tổng số. Nói chung, có hai tùy chọn: cập nhật phạm vi trong công thức SUM mỗi khi theo cách thủ công hoặc yêu cầu công thức OFFSET thực hiện cho bạn.

Vì ô phạm vi tổng đầu tiên sẽ được liệt kê trực tiếp trong công thức SUM, bạn chỉ cần quyết định các tham số của tính năng OFFSET Excel, sẽ nhận được ô mới nhất trong phạm vi:

Trợ giúp là một ô chứa tổng số B9 trong trường hợp của chúng tôi.

Các đường thẳng là một ô ngay trên tổng số, đòi hỏi số âm -1.

Cols là 0 vì bạn không muốn thay đổi cột.

Vì vậy, đây là mẫu công thức SUM/OFFSET:

SUM (ô đầu tiên: (OFFSET (ô có tổng số tiền, -1,0)

Công thức đã thay đổi cho ví dụ trước trông như sau:

TỔNG (B2:(OFFSET (B9, -1, 0)

Và, như được hiển thị trong ảnh chụp màn hình, nó chạy trơn tru:

Ví dụ 2. Công thức thiên vị Excel cho các dòng N cuối cùng

Trong ví dụ trên, giả sử bạn muốn biết số tiền thưởng trong N tháng qua thay vì một số tiền lớn. Bạn cũng muốn công thức tự động bao gồm các dòng mới mà bạn thêm vào trang tính.

Để hoàn thành nhiệm vụ này, chúng ta sẽ sử dụng Excel Offset kết hợp với SUM và COUNT/COUNTA:

SỐ TIỀN (OFFSET (B1,COUNT (B:B)-E1 – 1.0,E1,1))

Hoặc

SỐ TIỀN (OFFSET (B1, COUNTA (B:B)-E1,0,E1,1))

Các chi tiết sau đây có thể giúp bạn hiểu rõ hơn về các công thức:

Liên kết: Tiêu đề của cột bạn muốn tóm tắt là ô B1 trong ví dụ này.

Dòng – Sử dụng COUNT hoặc COUNTA để tính toán số hàng cần di chuyển.

COUNT trả về số ô trong cột B có chứa các số mà từ đó N tháng cuối cùng vẫn còn (số ô E1) và thêm 1.

Nếu COUNTA là tính năng ưa thích, bạn không cần thêm 1 vì tính năng này tính tất cả các ô không trống và dòng tiêu đề giá trị văn bản thêm ô bổ sung mà công thức của chúng tôi cần. Lưu ý rằng công thức này sẽ chỉ hoạt động chính xác trong cấu trúc bảng tương tự: dòng tiêu đề theo sau là số. Đối với các bố cục bảng khác nhau, bạn có thể cần thực hiện một số điều chỉnh đối với công thức OFFSET/COUNTA.

Thông đồng – Số cột bị mất thứ tự bằng không (0).

Chiều cao: Tổng số hàng được chỉ định trong E1.

Chiều rộng – 1 cột.

Sử dụng OFFSET với AVERAGE, MAX, MIN

Giống như chúng tôi tính toán tiền thưởng cho các tháng N cuối cùng, bạn có thể nhận được trung bình cho những ngày cuối cùng của N, tuần hoặc năm và tìm thấy giá trị tối đa hoặc tối thiểu của chúng. 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:

TRUNG BÌNH (OFFSET (B1, COUNT(B:B)-E1 – 1.0, E1,1))

MAKS (BÙ ĐẮP (B1,COUNT (B:B)-E1’1,0,E1,1))

SMIN (BÙ ĐẮP (B1, COUNT (B:B)-E1-1,0,E1,1))

Ưu điểm chính của các công thức này so với AVERAGE thông thường (B5:B8) hoặc MAX (B5:B8) là bạn không phải cập nhật công thức mỗi khi bảng gốc được cập nhật. Cho dù có bao nhiêu dòng mới được thêm hoặc loại bỏ trong trang tính, công thức OFFSET sẽ luôn tham chiếu đến số lượng ô cuối cùng (thấp nhất) được chỉ định trong cột.

Công thức bù excel để tạo dải tương phản động

Tính năng OFFSET, được chia sẻ với COUNTA, có thể giúp bạn tạo một dải động có thể hữu ích trong nhiều trường hợp, chẳng hạn như tạo danh sách bỏ học tự động.

Công thức OFFSET cho dải động như sau:

– OFFSET (Sheet_Name!$A $1, 0, 0, COUNTA (Sheet_Name!$A:$A), 1)

Ở trung tâm của công thức này, hãy dùng hàm COUNTA để lấy số ô không trống trong cột đích. Con số này đi đến chiều cao của đối số OFFSET hướng dẫn bạn trả về bao nhiêu dòng.

Ngoài ra, nó là một công thức dịch chuyển thông thường trong đó:

Liên kết là điểm khởi đầu mà từ đó bạn căn cứ vào sự thiên vị, chẳng hạn như Sheet1!$A $1.

Hàng và bắp cải 0 vì không có cột hoặc chuỗi để tháo gỡ.

Chiều rộng 1 cột.

Lưu ý. Nếu bạn tạo một dải động trong trang tính hiện tại, bạn không cần phải bao gồm tên của trang tính trong các nối kết, Excel sẽ tự động làm như vậy khi tạo tên của phạm vi. Nếu không, hãy chắc chắn bao gồm tên của trang tính, tiếp theo là dấu chấm than, như trong ví dụ này về công thức.

Sau khi tạo một phạm vi tên động với công thức bù đắp ở trên, bạn có thể sử dụng xác minh dữ liệu để tạo danh sách bỏ học động sẽ được cập nhật tự động ngay khi bạn thêm hoặc xóa các mục khỏi danh sách nguồn.

Hướng dẫn từng bước chi tiết để tạo danh sách bỏ học Excel có thể được tìm thấy trong các sách giáo khoa sau:

Tạo danh sách bỏ học trong Excel – tĩnh, động, từ một sách làm việc khác

Tạo danh sách bỏ học phụ thuộc

Excel Shift và VLOOKUP

Như bạn đã biết, với chức năng VLOOKUP hoặc HLOOKUP thực hiện các tìm kiếm dọc và ngang đơn giản tương ứng. Tuy nhiên, các tính năng này có quá nhiều hạn chế và thường phải đối mặt với các công cụ tìm kiếm mạnh mẽ và phức tạp hơn. Do đó, để thực hiện các tìm kiếm phức tạp hơn trong bảng Excel, bạn cần tìm kiếm các lựa chọn thay thế như INDEX, MATCH và OFFSET.

Ví dụ 1. CÔNG THỨC BÙ đắp cho Vlookup trái trong Excel

Một trong những hạn chế nổi tiếng nhất của VLOOKUP là không có khả năng nhìn sang trái, điều đó có nghĩa là VLOOKUP chỉ có thể trả về giá trị ở bên phải của cột tìm kiếm.

Có hai cột trong bảng dạng xem gần đúng của chúng tôi: tên của các tháng (cột A) và tiền thưởng (cột B). Nếu bạn muốn nhận tiền thưởng cho tháng này, công thức VLOOKUP đơn giản này sẽ chạy trơn tru:

Xem thêm:  Đánh số thứ tự trong excel -8 Bước chi tiết nhất + Link tải mới nhất

VLUCUP (B1, A5:B11, 2, FALSE)

Tuy nhiên, một khi bạn thay đổi các cột trong bảng kiểm tra, điều này sẽ ngay lập tức gây ra #N/A:

Để xử lý tìm kiếm ở phía bên trái, bạn cần một tính năng linh hoạt hơn không thực sự quan trọng cột ngược ở đâu. Một giải pháp khả thi là sử dụng kết hợp các tính năng INDEX và MATCH. Một cách tiếp cận khác là sử dụng OFFSET, MATCH và ROWS:

OFFSET (LOOKUP_TABLE, MATCH (LOOKUP_VALUE, OFFSET (LOOKUP_TABLE, 0, LOOKUP_COL_OFFSET, ROWS (lookup_table), 1) -0) -1, return_col_offset, 1, 1)

Ở đâu:

Lookup_col_offset: Đây là số cột di chuyển từ điểm bắt đầu đến cột tìm kiếm.

Return_col_offset: Đây là số cột di chuyển từ điểm bắt đầu đến cột trả về.

Trong ví dụ của chúng ta, bảng tìm kiếm A5:B9 và giá trị tìm kiếm nằm trong B1, ca cột tìm kiếm là 1 (vì chúng ta đang tìm kiếm một giá trị tìm kiếm trong cột thứ hai (B), chúng ta cần di chuyển 1 cột sang phải của đầu bảng, dịch chuyển cột ngược lại là 0, bởi vì chúng ta đang trả về các giá trị từ cột đầu tiên (A) :

OFSET (A5:B9, MATCH (B1, OFFSET (A5:B9, 0, 1, RYDS (A5:B9), 1) ,0) -1, 0, 1, 1)

Tôi biết công thức trông hơi rườm rà, nhưng nó hoạt động 🙂

Ví dụ 2. Cách thực hiện tìm kiếm hàng đầu trong Excel

Như với VLOOKUP, nếu không thể nhìn sang trái, đối tác ngang của nó – tính năng HLOOKUP – không thể tra cứu để trả về giá trị.

Nếu bạn cần quét hàng trên cùng để tìm kết quả khớp, công thức OFFSET MATCH có thể giúp ích một lần nữa, nhưng lần này bạn sẽ cần cải thiện nó bằng tính năng COLUMNS như sau:

OFFSET (LOOKUP_TABLE, RETURN_ROW_OFFSET, MATCH (lookup_value, OFFSET (lookup_table, LOOKUP_ROW_OFFSET, 0, 1, CỘT (lookup_table)), 0) -1, 1, 1)

Ở đâu:

Lookup_row_offset: Số dòng di chuyển từ điểm bắt đầu đến thanh tìm kiếm.

Return_row_offset: Số dòng di chuyển từ điểm bắt đầu đến dòng trả về.

Giả sử rằng bảng kiểm tra B4:F5 và giá trị kiểm tra nằm trong ô B1, công thức trông như thế này:

BÙ ĐẮP (B4:F5, 0, MATCH (B1, OFFSET(B4:F5, 1, 0, 1, COLUMNS (B4:F5)), 0) -1, 1, 1)

Trong trường hợp của chúng tôi, ca thanh tìm kiếm là 1, vì phạm vi tìm kiếm của chúng tôi giảm 1 hàng từ điểm bắt đầu, ca đường trả về là 0 vì chúng tôi trả về các kết quả khớp từ hàng trước của bảng.

Ví dụ 3. Tìm kiếm hai chiều (theo giá trị cột và chuỗi)

Tìm kiếm hai chiều trả về giá trị dựa trên các hàng và cột phù hợp. Và bạn có thể sử dụng công thức ma trận hai hình thức sau đây để tìm giá trị tại giao điểm của chuỗi và cột này:

OFFSET (bảng tìm kiếm, KHỚP (giá trị tìm kiếm dòng, OFFSET (bảng tìm kiếm, 0, 0, HÀNG (bảng tìm kiếm), 1), 0) -1, KHỚP (giá trị tìm kiếm cột, OFFSET (bảng tìm kiếm, 0, 0, 1, CỘT (bảng tìm kiếm)) -1)

Với:

Tìm kiếm bảng A5:G9

Giá trị khớp với các dòng nằm trong B2

Giá trị sẽ khớp trong các cột nằm trong B1

Bạn sẽ nhận được công thức tìm kiếm hai chiều sau:

OFSET (A5:G9, MATCH (B2, OFFSET (A5:G9, 0, 0, RYADS (A5:G9), 1), 0)-1, MATCH (B1, OFFSET (A5:G9, 0, 0, 1, COLUMN (A5:G9)), 0) -1)

Đó không phải là điều dễ nhớ nhất, phải không? Ngoài ra, đó là một công thức mảng, vì vậy hãy chắc chắn nhấp vào Ctrl và Shift – Nhập để nhập chính xác.

Tất nhiên, công thức OFFSET dài này không phải là cách duy nhất có thể để thực hiện tìm kiếm kép trong Excel. Bạn có thể nhận được kết quả tương tự bằng cách sử dụng VLOOKUP và MATCH, SUMPRODUCT hoặc INDEX và MATCH. Thậm chí còn có một công thức dạng tự do: việc sử dụng các phạm vi này và giao điểm (không gian) của người vận hành. Hướng dẫn sau đây chi tiết tất cả các giải pháp thay thế: cách thực hiện tìm kiếm hai chiều trong Excel.

OFFSET – Hạn chế và lựa chọn thay thế

Hãy hy vọng rằng các ví dụ công thức trên trang này đã làm sáng tỏ cách sử dụng OFFSET trong Excel. Tuy nhiên, để sử dụng hiệu quả vai trò trong sổ làm việc của riêng bạn, bạn không chỉ phải nhận thức được điểm mạnh của mình mà còn phải cẩn thận với điểm yếu của mình.

Những hạn chế quan trọng nhất của tính năng OFFSET Excel là:

OFFSET là một chức năng khao khát tài nguyên. Bất cứ khi nào có một số thay đổi trong dữ liệu gốc, các công thức bù đắp được tính toán lại, giữ cho Excel bận rộn lâu hơn một chút. Đây không phải là vấn đề đối với một công thức trong một bảng nhỏ. Nhưng nếu có hàng chục hoặc hàng trăm công thức trong sổ làm việc, Microsoft Excel có thể mất một chút thời gian để tính toán lại.

Công thức thiên vị Excel rất khó sửa đổi. Bởi vì các liên kết được trả về bởi OFFSET là động, các công thức lớn (đặc biệt là với OFFSETs đầu tư) có thể khá khó sửa chữa.

Các lựa chọn thay thế để sử dụng OFFSET trong Excel

Như thường xảy ra trong Excel, kết quả tương tự có thể đạt được theo nhiều cách khác nhau. Vì vậy, đây là ba lựa chọn thay thế thanh lịch cho OFFSET.

Bảng Excel

Với Excel 2002, chúng tôi có một tính năng thực sự tuyệt vời – một cú nhấp chuột đầy đủ của các bảng Excel, không giống như các phạm vi thông thường. Để tạo bảng từ dữ liệu có cấu trúc, chỉ cần bấm Chèn bảng > tab Trang đầu hoặc bấm Ctrl và T.

Khi bạn nhập công thức vào ô trong bảng Excel, bạn có thể tạo cái gọi là “cột được tính toán” tự động sao chép công thức vào tất cả các ô khác trong cột đó và điều chỉnh công thức cho từng dòng trong bảng.

Ngoài ra, bất kỳ công thức nào trích dẫn dữ liệu trong bảng sẽ tự động được điều chỉnh để bao gồm các dòng mới mà bạn thêm vào bảng hoặc để loại trừ các dòng bạn loại bỏ. Về mặt kỹ thuật, các công thức này hoạt động trên các cột hoặc hàng bảng là dải động của tự nhiên. Mỗi bảng trong sổ làm việc có một tên duy nhất (bảng mặc định1, table2, v.v.) và có thể tự do đổi tên bảng thông qua bảng tab Bố trí > Nhóm Thuộc tính > tên bảng của cửa sổ văn bản.

Ảnh chụp màn hình tiếp theo hiển thị công thức SUM, đề cập đến cột Phần thưởng Table3. Lưu ý rằng công thức bao gồm cột bảng

name rather than a range of cells.

Chỉ số Excel Функция

Mặc dù nó không hoàn toàn giống với OFFSET, chỉ số Excel cũng có thể được sử dụng để tạo các liên kết dải động. Không giống như OFFSET, INDEX không biến động, vì vậy nó sẽ không làm chậm Excel.

Excel INDIRECT

Với INDIRECT, bạn có thể tạo các liên kết dải động từ nhiều nguồn, chẳng hạn như giá trị ô, giá trị ô và văn bản, được gọi là dải ô. Bạn cũng có thể tự động tham khảo một trang tính Excel hoặc sổ làm việc khác.

123doc