Hàm OFFSET – cú pháp và áp dụng cơ bản:
Cú pháp của hàm OFFSET như sau:OFFSET(reference, rows, cols,
Trong đó, 3 đối số trước tiên là bắt buộc còn 2 cái sau thì không. Toàn bộ các đối số có thể là các tham chiếu đến các ô khác hay những các kết quả được trả về bởi các công thức Excel khác.
Bạn đang xem: Cách dùng hàm offset trong excel
Những đối số bắt buộc:
Reference– một ô hoặc một dải ô liền kề mà tiếp nối nó có thể bị thêm/bớt (Có thể nói đó là điểm khởi đầu/làm mốc) Rows – Số hàng dịch chuyển từ điểm làm cho gốc, lên hoặc xuống. Nếu những hàng là một trong những dương, tức thị công thức dịch chuyển xuống bên dưới tham chiếu, vào trường hợp một trong những âm thì dịch rời lên bên trên tham chiếu. Cols – Số cột bạn muốn công thức di chuyển, lấy cội là trường đoản cú điểm xuất phát. Cũng như rows, cols hoàn toàn có thể là số dương (ở bên buộc phải tham chiếu) hoặc số âm (ở bên trái tham chiếu).Những đối số tùy chọn:
height – chiều cao, tính bằng số hàng, mà bạn muốn tham chiếu trả về Width – chiều rộng, tính bằng số cột, mà bạn có nhu cầu tham chiếu trả về.Cả nhị đối số height cùng width luôn luôn phải là số dương. Nếu một trong các hai bị vứt qua, thì height hoặc width của tham chiếu gốc được sử dụng.
Và bây giờ, tôi tất cả một ví dụ đơn giản về phương pháp OFFSET nhằm minh hoạ triết lý trên:
Ví dụ phương pháp OFFSET:Công thức OFFSET sau trả về một tham chiếu ô dựa trên một điểm làm mốc, những hàng và cột mà bạn chỉ định:
= OFFSET (A1,3,1)
Công thức rước ô A1 làm cho điểm có tác dụng mốc (tham chiếu), sau đó di chuyển 3 mặt hàng xuống (đối số rows) cùng 1 cột bên trái (đối số cols). Tác dụng là, phương pháp OFFSET này trả về quý giá trong ô B4.
Hình hình ảnh ở phía trái cho thấy cách thức hàm vận động và ngơi nghỉ bên nên thể hiện nay cách chúng ta cũng có thể sử dụng phương pháp OFFSET trên dữ liệu thực tế. Sự biệt lập duy nhất giữa hai bí quyết là cách làm thứ hai (bên phải) gồm 1 tham chiếu ô (E1) trong đối số mặt hàng (rows). Tuy vậy nếu ô E1 bao gồm chứa số 3 và đúng chuẩn cùng một số xuất hiện trong những đối số mặt hàng của công thức đầu tiên, thì cả hai sẽ các trả về cùng một kết quả – cực hiếm trong B4.
Công thức Excel OFFSET – những điều cần nhớ:
Hàm OFFSET vào Excel không đích thực di chuyển bất kỳ ô hoặc dải nào, mà điều nó làm chỉ là trả về một tham chiếu. Lúc một công thức OFFSET trả về một dải ô, những đối số rows và cols luôn luôn đề cập mang đến ô bên trên bên trái. Đối số tham chiếu phải gồm 1 ô hoặc một dải ô lập tức kề, nếu không công thức của bạn sẽ trả về #VALUE! lỗi. Nếu những hàng (rows) được chỉ định và hướng dẫn và/hoặc cột (cols) dịch chuyển một tham chiếu mang đến rìa của bảng tính, thì công thức Excel OFFSET của bạn sẽ trả về #REF! lỗi. Hàm OFFSET hoàn toàn có thể được sử dụng trong bất kỳ hàm Excel như thế nào khác nhưng mà có gật đầu đồng ý một tham chiếu ô hoặc dải trong những đối số của nó.Ví dụ, ví như bạn nỗ lực sử dụng công thức tương tự như thế này: = OFFSET (A1,3,1,1,3), thì nó đã trả về # VALUE! Lỗi, bởi một dải để gửi thành (1 hàng, 3 cột) không phù hợp với bài toán đặt chỉ vào một ô duy nhất. Mặc dù nhiên, nếu như khách hàng nhúng nó vào hàm SUM, như nạm này:
= SUM (OFFSET (A1,3,1,1,3))
Công thức sẽ trả lại tổng các giá trị trong dải có một hàng bởi dải tất cả 3 cột, bao gồm 3 hàng bên dưới và 1 cột bên bắt buộc ô A1, tức là tổng quý hiếm có trong các ô B4: D4.
Sử dụng OFFSET trong trường đúng theo nào:
Bây giờ bạn đã biết hàm OFFSET làm được phần nhiều gì, vậy nên bạn có thể tự hỏi bản thân “Tại sao lại cần dùng nó?” lý do không chỉ dễ dàng và đơ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: những tham chiếu như B1: C4 là tĩnh, tức là chúng luôn tham chiếu mang lại một dải tốt nhất định. Tuy nhiên với một số công việc thì sẽ dễ triển khai hơn khi làm với dải động. Đây là ngôi trường hợp đặc biệt khi bạn thao tác với việc biến đổi dữ liệu, ví dụ: chúng ta có một bảng tính, và mỗi tuần sẽ có được một sản phẩm hoặc cột mới được tiếp tế trong đó.
Lấy dải từ ô gốc: Đôi khi, chúng ta cũng có thể không biết địa chỉ thực của tất cả dải, mặc dù bạn tất cả biết rằng nó bước đầu từ một số ô. Vào các trường hợp như vậy, thực hiện OFFSET vào Excel là một cách làm đúng.
Làm cố gắng 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 định hướng ở trên. Mặc dù sao, hiện nay chúng ta đang thực hiện phần thú vui nhất – vận dụng hàm OFFSET trong tình huống thực tế.
Hàm OFFSET cùng hàm SUM:
Ví dụ bọn họ đã trao đổi cách đây một phút diễn tả cách sử dụng OFFSET với SUM trong Excel đơn giản nhất. Bây giờ, bọn họ hãy nhìn vào đa số hàm này ngơi nghỉ một góc nhìn khác và xem phần đa gì chúng hoàn toàn có thể làm.
Ví dụ 1. Một cách làm 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, chúng ta có thể muốn tất cả một công thức SUM tự động hóa chọn tất cả các hàng bắt đầu được thêm vào?
Giả sử các bạn có dữ liệu nguồn tựa như như đông đảo gì chúng ta thấy vào hình bên dưới. Mỗi tháng sẽ có một dòng new được cấp dưỡng ngay bên trên công thức SUM, với tự nhiên, bạn có nhu cầu nó được bao gồm trong tổng số. Bên trên toàn bộ, có hai tuyển lựa – 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 vấn đề đó cho bạn.
Xem thêm: Tinh Dầu Húng Chanh Cách Dùng Tinh Dầu Húng Chanh, Tinh Dầu Húng Chanh: Công Dụng, Lưu Ý Và Cách Làm
Khi ô thứ nhất của dải được tính tổng (SUM) nhưng mà được tham chiếu thẳng trong công thức SUM, bạn chỉ buộc phải đưa ra những tham số mang lại hàm OFFSET, và tiếp nối nó vẫn tham chiếu cho tới ô sau cuối của dải:
Reference – ô chứa tổng số, trong lấy ví dụ như ở đó 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 tất cả mẫu bí quyết SUM / OFFSET:
= SUM (first cell: (OFFSET (cell with total, -1,0)
Rút gọn ví dụ trên, ta sẽ có được công thức trông như sau:
= SUM (B2: (OFFSET (B9, -1, 0)))
Và nó thực sự chuyển động tốt như trong hình được minh họa bên dưới đây:
Trong lấy một ví dụ trên, giả sử bạn có nhu cầu biết số tiền thưởng (Bonus) mang đến N tháng sớm nhất chứ chưa phải là vớ cả. Và bạn vẫn muốn công thức chạy tự động hóa (không cân nhắc liệu có ngẫu nhiên hàng bắt đầu được sản xuất trang tính tốt không).
Với trách nhiệm này, chúng ta sẽ sử dụng Excel OFFSET kết phù hợp với các hàm SUM với 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 hoàn toàn có thể giúp bạn hiểu rõ các công thức giỏi hơn:
Reference – tiêu đề của cột có những giá trị mà bạn có nhu cầu tính tổng, trong ví dụ như này là ô B1. Rows – để tính số hàng nhằm tính offset, và bạn sử dụng thêm hàm COUNT hoặc COUNTA.COUNTA trả về số ô vào cột B có chứa số, từ đó bạn trừ đi các N tháng sau cuối (giá trị làm việc trong ô E1) cùng thêm 1.
Nếu bạn muốn chọn hàm COUNTA, thì lúc ấy bạn không cần phải thêm 1, vày hàm này tính toàn bộ các ô không chứa giá trị rỗng, cùng hàng tiêu đề có giá trị văn phiên bản được thêm một ô thêm bắt đầu mà công thức đề nghị đến. Xin lưu ý rằng công thức này vẫn chỉ hoạt động đúng mực trên một cấu tạo bảng tính tương tự – và cái tiêu đề tiếp sau là hàng tất cả số. Đối cùng với các kết cấu bảng không giống nhau, chúng ta có thể cần thực hiện một số điều chỉnh trong phương pháp OFFSET / COUNTA.
Cols – số cột kiểm soát và điều chỉnh là 0. Chiều cao – số mặt hàng được tổng hòa hợp được chỉ định trong E1. Chiều rộng – 1 cột.Sử dụng hàm OFFSET cùng với AVERAGE, MAX, MIN
Theo cách tương tự như như shop chúng tôi có thể tính tiền thưởng mang đến N mon qua, bạn cũng có thể nhận được giá trị trung bình mang đến N ngày, tuần hoặc năm cuối cùng cũng giống như tìm những giá trị tối đa hoặc buổi tối thiểu. Sự khác biệt duy độc nhất giữa những công thức là tên gọi 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ủ yếu mà những công thức này hơn hàm AVERAGE(B5: B8) hoặc MAX (B5: B8) là các bạn sẽ không phải update công thức mỗi lúc bảng gốc của doanh nghiệp được cập nhật. Các bạn sẽ không cần phải quan trung tâm rằng từng nào dòng bắt đầu được cấp dưỡng hoặc xóa trong bảng tính Excel của bạn, mà sẽ sở hữu các bí quyết OFFSET sẽ luôn luôn tham chiếu mang đến tận phần đông ô sau cuối (đã nguyên tắc trước giới hạn bé dại nhất và khủng nhất) trong cột.
Công thức OFFSET để tạo ra một dải động
Được sử dụng cùng cùng với COUNTA, hàm OFFSET rất có thể giúp các bạn tạo một dải hễ hữu ích trong vô số nhiều tình huống, ví như khi bạn có nhu cầu tạo list theo mục có thể tự động hóa cập nhật.
Công thức OFFSET cho 1 dải hễ như sau:
= OFFSET (Sheet_Name! $A$1, 0, 0, COUNTA (Sheet_Name! $A:$A), 1)
Trong đó:
Reference: được màn biểu diễn dưới dạng thương hiệu bảng và theo sau là vết chấm than, và địa chỉ của ô bao gồm chứa mục đầu tiên có mặt trong vào dải (-đã được đặt tên). Xin lưu ý việc sử dụng những tham chiếu ô hoàn hảo ($), ví dụ như Sheet1! $A$1. Những tham số Rows và Cols hầu hết là 0, vì không tồn tại các cột hoặc những hàng nhận thêm vào. Height là điểm mấu chốt sinh sống đây. Tình huống là bạn sử dụng hàm COUNTA để giám sát và đo lường số ô không rỗng vào cột bao gồm chứa các mục của dải được để tên. Cùng với đó, hãy xem xét rằng trong thông số của COUNTA, bạn nên có thể định thương hiệu bảng sát bên tên cột, ví dụ: COUNTA (Sheet_Name!$A: $A). Width là 1 trong những cột.Một khi chúng ta đã tạo một dải rượu cồn (đã đặt tên) với công thức OFFSET ngơi nghỉ trên, bạn cũng có thể sử dụng Excel Data Validation nhằm tạo danh sách theo mục – luôn tự động hóa cập nhật ngay khi bạn thêm hoặc xoá những mục từ danh sách nguồn (bảng tính gốc).
Hàm OFFSET và VLOOKUP:
Như các bạn đã biết, những tra cứu đơn giản theo chiều dọc và ngang trong Excel được tiến hành với hàm VLOOKUP hoặc hàm HLOOKUP. Mặc dù nhiên, các hàm này có không ít hạn chế với thường là băn khoăn lớn trong câu hỏi tạo cách làm tra cứu mạnh mẽ hơn và phức tạp hơn. Vị vậy, để triển khai tra cứu giúp “một giải pháp tinh vi hơn” trong những bảng Excel, các bạn phải search kiếm các lựa chọn sửa chữa thay thế như hàm INDEX, MATCH cùng OFFSET.
Ví dụ 1. Bí quyết OFFSET cho 1 Vlookup dò bên trái trong Excel
Một trong những hạn chế đáng kể nhất của hàm Excel VLOOKUP là không có khả năng dò giá tốt trị phía bên trái của nó, tức là VLOOKUP chỉ có thể trả lại một quý giá ở bên nên cột tra cứu.
Trong bảng tra cứu mẫu của chúng tôi, tất cả hai cột – thương hiệu tháng (month) (cột A) với tiền thưởng (Bonus) (cột B). Nếu bạn có nhu cầu nhận được chi phí thưởng cho 1 tháng duy nhất định, bí quyết VLOOKUP dễ dàng và đơn giản này sẽ vận động mà không gặp gỡ trở ngại:
= VLOOKUP (B1, A5: B11, 2, FALSE)
Tuy nhiên, ngay khi chúng ta tráo đổi những cột trong bảng tra cứu, vấn đề này sẽ ngay mau lẹ dẫn cho lỗi #N/A:
Để cách xử trí một tra cứu mặt trái, bạn phải một hàm linh động hơn mà không thực sự suy xét vị trí của cột kia ở đâu. Một trong những những chiến thuật có thể sử dụng chính là kết hợp những hàm INDEX cùng MATCH. Một biện pháp làm khác chính là sử dụng OFFSET, MATCH cùng ROWS:
=OFFSET(lookup table, MATCH(lookup value, OFFSET(lookup table, 0, 1, ROWS(lookup table), 1) ,0) -1, 0, 1, 1)
Trong lấy một ví dụ của chúng tôi, bảng tra cứu giúp là A5: B9 và giá trị tra cứu bên trong ô B1, vì chưng vậy phương pháp trên sẽ triển khai thành:
= OFFSET (A5: B9, MATCH (B1, OFFSET (A5: B9, 0, 1, ROWS (A5: B9), 1), 0) -1, 0, 1, 1)
Tôi biết cách làm trông hơi hậu đậu về, dẫu vậy nó thực sự được câu hỏi
