Hàm Offset và Validation

Liên hệ QC
Status
Không mở trả lời sau này.

TranThanhPhong

Ngày mai trời lại sáng!
Thành viên danh dự
Tham gia
16/3/07
Bài viết
2,104
Được thích
19,153
Giới tính
Nam
Offset truyền kỳ

Offset truyền kỳ


Kỳ 1: Giới thiệu hàm Offset


Công dụng:
Trả về tham chiếu đến một vùng nào đó, cách một ô hoặc một dãy ô một khoảng cách với số dòng hoặc số cột được chỉ định trước. Chúng ta có thể chỉ định số dòng, số cột của vùng tham chiếu trả về.


Cú pháp:
=OFFSET(reference, rows, cols, height, width)

Trong đó:
  • reference: là vùng tham chiếu làm cơ sở cho hàm (làm điểm xuất phát) để tạo vùng tham chiếu mới. reference phải chỉ đến một ô hoặc một dãy ô liên tục, nếu không hàm sẽ trả về lỗi #VALUE!.
  • rows: là số dòng bên trên hoặc bên dưới reference, tính từ ô đầu tiên (ô ở góc trên bên trái) của reference. Ví dụ nếu rows là 3, sẽ có 3 dòng trả về và nằm bên dưới reference. Khi rows là số dương thì các dòng trả về nằm bên dưới reference, khi rows là số âm thì các dòng trả về nằm bên trên reference.
  • cols: là số cột bên trái hoặc bên phải reference, tính từ ô đầu tiên (ô ở góc trên bên trái) của reference. Ví dụ nếu cols là 4 sẽ có 4 cột trả về và nằm bên phải của reference. Khi cols là số dương thì các cột trả về nằm bên phải reference, khi cols là số âm thì các cột trả về nằm bên trái reference.
  • height: là số dòng của vùng tham chiếu cần trả về. Height phải là số dương.
  • width: là số cột của vùng tham chiếu cần trả về. Width phải là số dương.
Lưu ý:
  • Nếu rows và cols làm cho tham chiếu trả về vượt ra ngoài phạm vi của một worksheet, hàm Offset sẽ báo lỗi #REF!
  • Nếu bỏ qua height và width, thì height và width sẽ có kích thước mặc định là height và width của reference.
  • Offset thật sự không di chuyển bất cứ ô nào, cũng không thay đổi bất kỳ phần chọn nào, nó chỉ trả về tham chiếu mà thôi. Chúng ta có thể sử dụng Offset với bất kỳ hàm nào cần đối số là một tham chiếu. Ví dụ, công thức Sum(Offset(C10,1,2,3,1)) sẽ tính tổng các giá trị của một dãy gồm ba dòng, một cột; đây là dãy nằm bên dưới ô C10 một dòng và bên phải ô này hai cột.
offset01.jpg





Các ví dụ


offset02.jpg



TP.


Kỳ 2: Offset và Validation List
 

File đính kèm

  • Offset.xls
    36 KB · Đọc: 20,945
Offset truyền kỳ

Kỳ 2: Offset và Validation List

a. Có bao giờ bạn muốn tạo một Validation List mà nội dung của nó thay đổi theo sự thay đổi của một List khác (danh sách). Bài viết này sẽ hướng dẫn từng bước cách làm:

Giả sử bạn có danh sách gồm 2 cột: OS (hệ điều hành) và Versions (Phiên bản) (Xem file đính kèm). Bây giờ bạn muốn tạo một Validation List để khi tại ô G3 ta chọn một OS nào đó thì danh sách trong ô H3 sẽ chỉ hiển thị các Versions của OS mà ta đang chọn (xem hình 6).

offset03.jpg


Qui trình thực hiện:

B1. Tạo một danh sách A1:B34 (xem file đính kèm), với 2 cột OS và Versions. Lưu ý một OS có thể có nhiều Version.

B2. Đặt tên cho vùng A2:A34 với tên là “OS”. Bạn quét chọn vùng A2:A34 | nhấn tổ hợp <Ctrl+F3> | Chọn New… gõ OS vào hộp Name, chọn Scope là Worksheet Offset2 hoặc Workbook đều được và tại Refers to gõ vào =A2:A34 | Nhấn nút OK (Excel 2007). Nếu dùng các phiên bản Excel trước thì sau khi nhấn <Ctrl+F3> là có thể khai báo việc đặt tên vùng và sao đó nhấn nút Add để hoàn thành.

offset04.jpg


B3. Tạo một danh sách chứa tên các hệ điều hành được trích ra từ cột OS (danh sách này không trùng lặp). Bạn quét chọn các ô D2:D34 dùng để chứa danh sách kết quả trích lọc không trùng | nhập vào công thức mảng trích lọc sau:

=IF(ISERR(INDEX(OS,SMALL(IF(MATCH(OS,OS,0)=ROW(INDIRECT("1:"&ROWS(OS))), MATCH(OS,OS,0),""),ROW(INDIRECT("1:"&ROWS(OS)))))),"",
INDEX(OS,SMALL(IF(MATCH(OS,OS,0)=ROW(INDIRECT("1:"&ROWS(OS))), MATCH(OS,OS,0),""),ROW(INDIRECT("1:"&ROWS(OS))))))

Kết thúc công thức bằng tổ hợp phím <Ctrl+Shift+Enter>. Công thức trên sẽ trích ra các loại hệ điều hành (OS) và mỗi loại chỉ lấy một lần, các dòng trùng sẽ thay bằng “”.

offset05.jpg


B4. Sau khi đã có danh sách các OS duy nhất, bạn hãy đặt tên cho danh sách này. Lưu ý, có khả năng có những loại OS khác do vậy ở đây khi đặt tên là dùng hàm Offset để tham chiếu đến vùng dữ liệu động.

Bạn nhấn <Ctrl+F3> | Chọn New… | đặt tên cho vùng là OSValList tại Name | tại Refers to nhập vào công thức sau;
=OFFSET(Offset2!$D$2,0,0,COUNTA(Offset2!$D$2:$D$34)-COUNTIF(Offset2!$D$2:$D$34,""),1)

Công thức trên giúp trả về vùng tham chiếu các OS duy nhất một cách động.

offset06.jpg


B5. Tạo bảng nhỏ như hình bên dưới. Chọn ô G3 và đặt tên là Val1Cell, chọn ô H3 đặt tên là Val2Cell (theo cách đặt tên nêu trên hoặc có thể đặt tên bằng cách gõ trực tiếp vào Name Box).

offset07.jpg


B6. Đây là bước quan trọng giúp cho danh sách chọn tại H3 sẽ thay đổi phụ thuộc vào tùy chọn tại G3. Bạn đặt tên cho vùng Versions với tham chiếu trả về thay đổi theo tùy chọn của G3 như sau:

Nhấn <Ctrl+F3> | chọn New… | tại hộp Name nhập vào tên là Versions | tại Refers to nhập vào công thức sau;

=OFFSET(INDIRECT(ADDRESS(MATCH(Val1Cell,OS,0)+1,2,,,)),0,0,COUNTIF(OS,Val1Cell),1)

Công thức này giúp chọn ra vùng Versions tương ứng với việc chọn OS tại ô G3.

B7. Áp Validation cho 2 ô G3 và H3:

Chọn ô G3 | vào Data | Data Validation | Settings | chọn List | tại Source nhập vào =OSValList | nhấn nút Ok hoàn tất.

Chọn ô H3 | vào Data | Data Validation | Settings | chọn List | tại Source nhập vào =Versions | nhấn nút Ok hoàn tất.

offset08.jpg


Các bước đã hoàn tất, các bạn hãy chọn thử một OS nào đó tại G3 và qua ô H3 xem danh sách Version tương ứng.

Các cải tiến cho công cụ này chúng ta có thể tiếp tục thảo luận trên GPE.

TP.
 

File đính kèm

  • Offset.rar
    12.1 KB · Đọc: 14,849
Lần chỉnh sửa cuối:
Công thức này:
=IF(ISERR(INDEX(OS,SMALL(IF(MATCH(OS,OS,0)=ROW(IND IRECT("1:"&ROWS(OS))), MATCH(OS,OS,0),""),ROW(INDIRECT("1:"&ROWS(OS)))))) ,"",
INDEX(OS,SMALL(IF(MATCH(OS,OS,0)=ROW(INDIRECT("1:" &ROWS(OS))), MATCH(OS,OS,0),""),ROW(INDIRECT("1:"&ROWS(OS))))))
Nếu đã dùng Excel2007 để làm, thì tại sao không dùng IFERROR:
=IFERROR(INDEX(OS, SMALL(IF(MATCH(OS, OS, 0) = ROW(INDIRECT("1:" & ROWS(OS))), MATCH(OS, OS, 0), ""), ROW(INDIRECT("1:" & ROWS(OS))))),"")
 
Chỉnh sửa lần cuối bởi điều hành viên:
Status
Không mở trả lời sau này.
Web KT
Back
Top Bottom