Dùng Hàm OFFSET và cấu trúc Data Validation để tạo danh sách thay đổi theo lựa chọn

[tr_giang1805]

Mình có dựa theo "Tuyệt chiêu 25:Tạo một danh sách xác thực thay đổi theo sự lựa chọn từ một danh sách khác" để thử tạo hai danh sách lựa chọn. Mình đã dựa theo hướng dẫn và làm thành công. Nhưng sau khi tham khảo tài liệu tìm hiểu về hàm thì mình vẫn còn mơ hồ về phần hàm:
=OFFSET($A$2,0,0,COUNTA($A$2:$A$20),1)
=OFFSET(INDIRECT(ADDRESS(MATCH(Val1Cell,Objects,0) +1,2,,,"Lists")),0,0,COUNTIF(Objects,Val1Cell),1)Do không hiểu nhiều về cấu trúc các hàm này nên mình làm xong thì chỉ có thể điều khiển 1 dòng thôi.Mà không biết làm sao để mở rộng làm tiếp dòng tiếp theo để nó điều khiển như dòng 1.
Mình mới học Excel căn bản nên mấy cái này hơi quá tầm, mà thích học hỏi nên mong các bạn nào am hiểu xin chỉ dùm, cám ơn!

 

[leonguyenz]

Bạn tham khảo hàm OFFSET nhé:

Hàm OFFSET

Chức năng: Hàm OFFSET sẽ trả về một tham chiếu đến một vùng nào đó, vị trí bắt đầu của tham chiếu từ một ô hay một vùng nào đó cho trước với độ lớn của vùng được chỉ định.

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

- Reference: là vùng hay ô được chọn làm điểm xuất phát (điểm mốc) của vùng tham chiếu sẽ trả về trong công thức.

- Rows: là số chỉ số dòng lệch lên hay lệch xuống của vùng tham chiếu trả về so với vùng được chọn làm mốc. Rows có giá trị âm "-" thì vùng trả về sẽ lệch lên trên so với vùng chọn làm mốc và ngược lại.

- Cols: là số chỉ số cột lệch qua trái hay qua phải của vùng tham chiếu trả về so với vùng được chọn làm mốc. Cols có giá trị âm "-" thì vùng trả về sẽ lệch sang trái so với vùng chọn làm mốc và ngược lại.

- Height: là số chỉ số dòng (độ cao) có trong vùng tham chiếu sẽ trả về trong công thức.

- Width: là số chỉ số cột (độ rộng) có trong vùng tham chiếu sẽ trả về trong công thức.

Lưu ý: Nếu trong công thức không nhập giá trị của height và width thì xem như nó bằng với độ lớn của Reference.
Tài liệu GPE.

Ở hàm trên có chèn hàm COUNTA ở vị trí Height, tức là có thay đổi độ cao của cột (hoặc là của 1 vùng). Như vậy ta sẽ có 1 "List" với chiều dài (độ cao) được tạo bởi hàm COUNTA. Việc lồng nhiều hàm phức tạp với nhau bạn phải tìm hiểu từng hàm trước. Chúc bạn thành công với GPE!

 

[tr_giang1805]

ý mình là mặc dù có tìm hiểu nhưng không hiểu hết được ý nghĩa của nó nhưng mong các bạn xem file giúp đỡ làm cách nào tạo ra nhiều ô nhập liệu không. chứ mình chỉ tạo được 1 dòng duy nhất nhập xong kéo xuống, nhập vào dòng 2,3,.......kết quả chọn y chan dòng 1, không thể chọn kết quả khác được. giúp giùm mình cần gấp để nhập liệu, cám ơn.
ví dụ:
-mình nhập dỏng 1 là: 8-1-đ ứng với cái này thì danh sách có 6 lựa chọn.
-mình nhập dòng tiếp theo: 8-1-g thì danh sách có 1 cài lựa chọn. (nhưng không được), nó vẫn hiện 6 lựa chọn như cái trên
-..............
mình đã coi công thức nhưng không hiểu lắm, nên cầu cứu các bạn hướng dẫn cho mình để làm danh sách gấp. chân thành cám ơn

 

[linhngoc]

ý mình là mặc dù có tìm hiểu nhưng không hiểu hết được ý nghĩa của nó nhưng mong các bạn xem file giúp đỡ làm cách nào tạo ra nhiều ô nhập liệu không. chứ mình chỉ tạo được 1 dòng duy nhất nhập xong kéo xuống, nhập vào dòng 2,3,.......kết quả chọn y chan dòng 1, không thể chọn kết quả khác được. giúp giùm mình cần gấp để nhập liệu, cám ơn.
ví dụ:
-mình nhập dỏng 1 là: 8-1-đ ứng với cái này thì danh sách có 6 lựa chọn.
-mình nhập dòng tiếp theo: 8-1-g thì danh sách có 1 cài lựa chọn. (nhưng không được), nó vẫn hiện 6 lựa chọn như cái trên
-..............
mình đã coi công thức nhưng không hiểu lắm, nên cầu cứu các bạn hướng dẫn cho mình để làm danh sách gấp. chân thành cám ơn

Với công thức trên bạn xem dần dần sẽ hiểu. Mình giúp bạn cách khác không dùng indirect, chắc dễ hiểu hơn.
bạn sửa lại nameCorrespondinglist của bạn như sau:
=OFFSET(Sheet1!$A$1,MATCH(Sheet2!B5,Objects,0),1,COUNTIF(Objects,Sheet2!B5),)
Bạn xem thêm trong file.

 

[ptm0412]

Việc hiểu công thức để từ từ hiểu. Nếu khó quá thì làm theo linhngoc sẽ thấy dễ hơn 1 chút.

Còn việc tại sao bạn làm không được nhiều dòng là vì nguyên nhân:

Trong Name Val1Cell bạn đã để địa chỉ tuyệt đối: =Sheet2!$B$5
Bạn thử đứng vào 1 ô bất kỳ trên dòng 5 (đừng đứng dòng khác), và sửa Name Val1Cell thành =Sheet2!$B5, bạn sẽ thấy tác dụng của tuyệt chiêu.

 

[tr_giang1805]

thank các bạn nhiều.
tiện đây cho mình hỏi có cách làm nào khác nữa không?

 

[cuminhphuock15m]

Bạn thử nhập công thức này vào ô C5. Công thức chỉ đúng khi các ô giống nhau trong cột objects phải nằm liền kề nhau (xem kỹ hàm countif giá trị trước tuyệt đối còn giá trị sau tương đối kết quả mới đúng.
"=OFFSET(Sheet1!$A$1,MATCH(Sheet2!B5,Sheet1!$A$2:$A$33,0)+COUNTIF($B$5:B5,B5)-1,1,1,)"
Copy công thức này xuống các ô bên dưới. Chỉ cần chọn giá trị bên cột B thì cột C sẽ có giá trị tương ứng.

 

[ha nguyet]

Chào các anh/ chị,
Em cũng đang cần thiết lập một file dùng theo tuyệt chiêu 25. Em đã làm theo hướng dẫn như trong bài viết, nhưng không hiểu sao đến bước cuối cùng là thiết lập data validation ở ô E6 thì lại bị báo lỗi như sau : " The list source must be a delimited list, or a reference to single row or column" Em gửi kèm theo file nhờ anh/ chị xem giúp em ạ. Em xin chân thành cảm ơn!

 

[ha nguyet]

Em thử lại theo công thức chị gửi vẫn bị lỗi chị ạ. Không biết là trong file của em có bị lỗi phần nào khác không ạ. Nhờ anh/chị xem giúp em ạ.
Ngoài ra em cũng muốn hỏi thêm: Nếu như file dữ liệu của em cần có thêm một cột nữa cần lựa chọn theo điều kiện lựa chọn của cột " Corresponding List" thì làm thế nào ạ.

 

[Kiều Mạnh]

E6 bạn sử dụng Data Validation với công thức như sau/ hoặc bạn gán cho nó vài cái Name rồi đưa là source của Data validation

 =OFFSET(LIST!$B$1,MATCH(Sheet1!$D6,LIST!IV$2:IV$17,0),,COUNTIF(LIST!IV$2:IV$17,Sheet1!$D6))

Hàm này nếu mình chuyển thành code tạo một cái Name động tên X xong Add hàm Offset vào được không Bạn

ý Mạnh nói là mình tạo thành Name động bằng code ấy ... xong dùng Hàm Add nó vào tại [D6] Có validation List Luôn ấy ...

Chứ viết 1 cái Dic Add nó vào thì trên GPE có nhiều rồi

 

[Kiều Mạnh]

Bạn mạnh hỏi chơi ah? Có khó gì đâu với chàng mạnh nhỉ???

TẠI ít khi chơi tới hàm nên hông Tự tin nữa .....Mới làm vậy Bạn xem thế nào

Keo Bác Bill làm dùm thì Vầy:

Sub Macro1()
    Range("D6").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=list"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub

Chỉ cần chạy code lần đầu thôi là có 1 cái Name động... Nếu ai làm biếng làm Name...

Sub ValidationList()
    ThisWorkbook.Names.Add "List", "=OFFSET('LIST'!$A$3,,,COUNTA('LIST'!$A$3:$A$5000),1)"
    Sheet2.Range("D6").Validation.Delete
    Sheet2.Range("D6").Validation.Add 3, , , Formula1:="=List"
End Sub