Cách sử dụng Hàm Offset trong excel

Thứ sáu - 27/09/2013 09:28

Hàm offset trong excel

Hàm offset trong excel
Hàm offset: Trả về giá trị của một ô hoặc một vùng các ô căn cứ vào số cột, số dòng, độ lớn các dòng, các cột được chỉ định trước
Hàm offset: Trả về giá trị của một ô hoặc một vùng các ô căn cứ vào số cột, số dòng, độ lớn các dòng, các cột được chỉ định trước
Cú pháp hàm OFFSET có các đối số sau đây:

OFFSET(reference,rows,cols,[height],[width])

Trong đó:

Reference: Vùng tham chiếu hay tập hợp các ô liền kề nhau trong bảng tính excel mà bạn muốn trả về giá trị tham chiếu. Vùng tham chiếu phải tham chiếu tới một ô hoặc một phạm vi các ô liền kề; nếu không hàm OFFSET trả về giá trị lỗi #VALUE! .

Rows: Tham số này bắt buộc. chỉ Số hàng kể từ ô referene cần phải dịch lên trên nếu là số âm và dịch xuống dưới nếu là số dương.

Cols: Tham số này bắt buộc. Bắt buộc. Số cột cần dịch chuyển về bên trái ô reference nếu cols= số âm hoặc dịch chuyển sang phải nếu là số dương.

Height: Tùy chọn. Chiều cao, tính bằng số hàng, mà bạn muốn có cho tham chiếu trả về. Chiều cao phải là số dương.

Width: Tùy chọn. Độ rộng, tính bằng số cột, mà bạn muốn có cho tham chiếu trả về. Độ rộng phải là số dương.
Ghi chú

Nếu đối số hàng và cột làm cho vùng tham chiếu trả về vượt ra ngoài đường biên của trang tính, thì hàm OFFSET trả về giá trị lỗi #REF! .

Nếu đối số chiều cao và độ rộng được bỏ qua, thì nó được giả định có cùng chiều cao và độ rộng với vùng tham chiếu.

Nếu chúng ta chỉ thực hiện việc tham chiếu và xuất ra một giá trị trong bảng tính thì giá trị Height và Width luôn luôn phải bắng 1 hoặc bỏ trống, nếu không hàm sẽ trả về giá trị lỗi:#REF! .

Khi chúng ta kết hợp hàm offset với hàm tính toán khác như Sum; Count, Counta thì Height và Width mới được phép mang giá trị lớn hơn 1.
Ta xét ví dụ sau

Ở ô B1: ta đặt công thức: =OFFSET(D1,B2,B3,B4,B5)

Hàm Offset trong excel

Reference = D1: D1 là ô mốc để từ đó khi giá trị rows và cols xuất hiện hàm Offset sẽ dịch chuyển lên trên, xuống dưới, sang phải hoặc sang trái ô D1 và trả về giá trị.
Rows = B2 = 2
Cols = B3 = 1
Height = B4 = 1
Width = B5 = 1

Mô tả: Từ ô D1, dịch chuyển xuống 2 dòng và dịch chuyển sang phải 1 cột chính là giá trị của E3
Với các giá trị trên thì ô B1= E3="Small"

Ở đây chúng ta đặt các tham số vào các ô riêng biệt chứ không nhập trực tiếp vào hàm để dễ thấy sự thay đổi của nó. Tuy nhiên các bạn phải lưu ý rằng nếu làm theo cách này để xuất hiện tham chiếu thì giá trị Height và Width phải bằng 1; nếu lồng vào các công thức khác thì có thể khác 1.

Bây giờ chúng ta kết hợp hàm đếm counta sẽ đếm xem có bao nhiêu ô được trả về với giá trị Rows = B2 = 2; Cols = B3 = 1;Height = B4 = 2; Width = B5 = 3
B1=Counta(offset(D1,B2,B3,B4,B5)= 6

Mô tả: từ ô E3 (Như trên) sẽ quét xuống 2 hàng và quét sang phải 3 cột ta được vùng dữ liệu E3:G4; Như vậy giá trị trả về của hàm offset là một mảng chứa các ô liền kề nhau: E3:G4 lúc này đối số của  Hàm CountA sẽ là =counta(E3:G4)
Và tiếp theo Hàm sẽ đếm từ ô E3:G4 tổng cộng có 6 ô trong vùng dữ liệu này.

Bạn hãy copy công thức và vùng dữ liệu rồi dán vào ô A1 của trang tính và thực hành nhé
 
Fomulas =COUNTA(OFFSET(D1,B2,B3,B4,B5))     1 2 3 4 5
rows 2   1 big red horse paper fish
cols 1   2 small brown giraffe plastic squid
height 2   3 tall yellow zebra iron star fish
width 3   4 short green bee copper prawn
      5 high blue buterfly gold turtles
      6 low orange flies silver shark
      7 fast purple rino diamon whale
      8 slow white seal lead dolphin
Chúc các bạn thành công

 

Tác giả bài viết: Minh Phú

Chú ý: Việc đăng lại bài viết trên ở website hoặc các phương tiện truyền thông khác mà không ghi rõ nguồn http://aneedz.com là vi phạm bản quyền

Tổng số điểm của bài viết là: 5 trong 1 đánh giá

Xếp hạng: 5 - 1 phiếu bầu
Click để đánh giá bài viết

  Ý kiến bạn đọc

Mã bảo mật   
Thống kê
  • Đang truy cập61
  • Máy chủ tìm kiếm2
  • Khách viếng thăm59
  • Hôm nay7,632
  • Tháng hiện tại43,691
  • Tổng lượt truy cập1,564,543
Bạn đã không sử dụng Site, Bấm vào đây để duy trì trạng thái đăng nhập. Thời gian chờ: 60 giây