Xin bài tập Hàm Offset và Data-Validation

[nguyenhongphong]

Chào các anh, chị thành viên trên forum GPE,

Em đọc bài Offset truyen kỳ của anh ttphong2007 e thấy rất hay, nhưng phần công thức:

=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))))))

Em đọc chẳng hiểu công thức này j cả. Anh chị có thể cho em xin 1 số bài tập từ cơ bản đến nâng cao của hàm offset và Data-Validation đc ko ạ?

Cám ơn anh chị rất nhiều.

 

[chucuoi92]

Chào các anh, chị thành viên trên forum GPE,

Em đọc bài Offset truyen kỳ của anh ttphong2007 e thấy rất hay, nhưng phần công thức:

=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))))))

Em đọc chẳng hiểu công thức này j cả. Anh chị có thể cho em xin 1 số bài tập từ cơ bản đến nâng cao của hàm offset và Data-Validation đc ko ạ?

Cám ơn anh chị rất nhiều.

Mình không biết cái "OS" là danh sách gì nhưng đây là công thức cơ bản trích lọc duy nhất và hàm offset lấy danh sách từ hàm này cho Data-Validation
Bạn muốn hiểu hàm này phải cắt từng hàm ra tìm hiểu đồng thời phải có chút kiến thức về hàm mảng nữa.Để hiểu hàm này không phải giải thích là hiểu ngay đâu.
Chịu khó vào GPE tìm hiểu về những bài trích lọc dữ liệu "nâu nâu" rồi hiểu ngay thôi.

 

[nguyenhongphong]

Mình không biết cái "OS" là danh sách gì nhưng đây là công thức cơ bản trích lọc duy nhất và hàm offset lấy danh sách từ hàm này cho Data-Validation
Bạn muốn hiểu hàm này phải cắt từng hàm ra tìm hiểu đồng thời phải có chút kiến thức về hàm mảng nữa.Để hiểu hàm này không phải giải thích là hiểu ngay đâu.
Chịu khó vào GPE tìm hiểu về những bài trích lọc dữ liệu "nâu nâu" rồi hiểu ngay thôi.

Cám ơn bạn,

Vậy bạn có link nào cho việc học cơ bản về Hàm Offset và Data-Validation ko vậy?

Mình cũng tìm đọc bài a BNTT nhưng cũng thấy chưa đã. Chưa thấm đc. Bạn có biết link nào hay làm ơn cho mình xin.

Cám ơn bạn

 

[chucuoi92]

Bài của mấy bác ấy viết là hay nhất rồi vừa rễ hiểu lại có ví dụ. còn như muốn thành thạo thì chỉ còn cách thực tập ngay tại GPE này thôi (đọc xong mà không có thực tập thì quên ngay thôi)

 

[nguyenhongphong]

Đúng là bài mấy anh chị admin là nhất rồi, nhìn chung bài các anh chị rất dể hiểu,nhưng đôi khi 1 vài chổ công thức lại rất lạ lẫm với mình. Nên mình cũng cần thêm những link giải thích bên cạnh (để hiểu sâu hơn) và bài tập để ứng dụng (để thực hành và ứng dụng).

Anh chị nào có link cho e về lý thuyết cơ bản và bài tập hàm offset và Data-Validation, cho e xin với!

Cám ơn anh chị.

 

[khoavu87]

Đúng là bài mấy anh chị admin là nhất rồi, nhìn chung bài các anh chị rất dể hiểu,nhưng đôi khi 1 vài chổ công thức lại rất lạ lẫm với mình. Nên mình cũng cần thêm những link giải thích bên cạnh (để hiểu sâu hơn) và bài tập để ứng dụng (để thực hành và ứng dụng).

Anh chị nào có link cho e về lý thuyết cơ bản và bài tập hàm offset và Data-Validation, cho e xin với!

Cám ơn anh chị.

bạn xem thử hai ví dụ này xem sao

 

[nguyenhongphong]

Rất cảm ơn bạn Khoavu87,
Bạn thật sự hiểu rõ và áp dụng rất thuần thục tất cả các hàm.
Ở đây mình muốn hỏi thêm phần name động, bạn có thể hướng dẫn giúp mình?
Tks bạn

 

[khoavu87]

Bạn tham khảo file

Rất cảm ơn bạn Khoavu87,
Bạn thật sự hiểu rõ và áp dụng rất thuần thục tất cả các hàm.
Ở đây mình muốn hỏi thêm phần name động, bạn có thể hướng dẫn giúp mình?
Tks bạn

Bạn xem trong file mình đã hướng dẫn cụ thể rùi đó

 

[hungpa]

Bạn xem trong file mình đã hướng dẫn cụ thể rùi đó

Cái này rất bổ ích, mình đã thử làm nhưng chỉ được với những vấn đề nhỏ, những công thức cần gõ trên 256 ký tự thì hình như không được.
Vấn đề của mình như thế này, mong các bạn giúp đỡ. Cũng dùng Validation, Offset và Vlookup, if nhưng chưa giải quyết được.
1. Mình có 1 list chứa tên các huyện trong tỉnh + List các xã trong huyện + các bản trong xã. Nói chung là theo một tổ chức hình cây.
2. Dùng Data Validation: Cột A chọn tên huyện --> Cột B tự động nhảy list các xã của huyện đó. Cái này dùng hàm if kết hợp với Data Validation đã ok nhưng chỉ phù hợp với độ lồng của hàm if ít và số ký tự trong hàm if khi dùng Data Validation nhỏ hơn 256 ký tự thì phải, gõ một lúc nó không cho gõ nữa.
3. Vì hàm if cũng chỉ để lấy tên vùng cần chọn hoặc địa chỉ vùng nên mình muốn đặt tên cho từng vùng rồi sử dụng vlookup nhưng hình như không được.

Cụ thể theo file gửi kèm.
Các anh / chị / em giúp với???

 

[ndu96081631]

Cái này rất bổ ích, mình đã thử làm nhưng chỉ được với những vấn đề nhỏ, những công thức cần gõ trên 256 ký tự thì hình như không được.
Vấn đề của mình như thế này, mong các bạn giúp đỡ. Cũng dùng Validation, Offset và Vlookup, if nhưng chưa giải quyết được.
1. Mình có 1 list chứa tên các huyện trong tỉnh + List các xã trong huyện + các bản trong xã. Nói chung là theo một tổ chức hình cây.
2. Dùng Data Validation: Cột A chọn tên huyện --> Cột B tự động nhảy list các xã của huyện đó. Cái này dùng hàm if kết hợp với Data Validation đã ok nhưng chỉ phù hợp với độ lồng của hàm if ít và số ký tự trong hàm if khi dùng Data Validation nhỏ hơn 256 ký tự thì phải, gõ một lúc nó không cho gõ nữa.
3. Vì hàm if cũng chỉ để lấy tên vùng cần chọn hoặc địa chỉ vùng nên mình muốn đặt tên cho từng vùng rồi sử dụng vlookup nhưng hình như không được.

Cụ thể theo file gửi kèm.
Các anh / chị / em giúp với???

Dữ liệu của bạn sao mà lung tung quá (nhìn như trời 30)
Bây giờ thế này nhé:
- Bạn làm 1 sheet riêng, trong đó liệt kê TỈNH
- Mổi TỈNH hãy liệt kê những HUYÊN của TỈNH đó
- Mổi HUYỆN hãy liệt kê làng xã của HUYỆN đó
Làm lại dữ liệu cho đàng hoàng trước đi rồi tính tiếp

 

[hungpa]

Dữ liệu của bạn sao mà lung tung quá (nhìn như trời 30)
Bây giờ thế này nhé:
- Bạn làm 1 sheet riêng, trong đó liệt kê TỈNH
- Mổi TỈNH hãy liệt kê những HUYÊN của TỈNH đó
- Mổi HUYỆN hãy liệt kê làng xã của HUYỆN đó
Làm lại dữ liệu cho đàng hoàng trước đi rồi tính tiếp

Mình đã tách riêng dữ liệu tỉnh/huyên/xã rồi đó. Bạn thử nghiên cứu giúp với nhé.

 

[thanh_tks]

Mình đã tách riêng dữ liệu tỉnh/huyên/xã rồi đó. Bạn thử nghiên cứu giúp với nhé.

Sửa lại DATA chút cho rễ làm!
Kiểu bài này đã có trên GPE

 

[leonardo208]

http://thuthuataccess.com/forum/printthread.php?tid=1052

Trong tài liệu này, ở phần cuối có hướng dẫn cách làm bằng cách kết hợp hàm OFFSET và hàm Match khá hay đấy, bạn tham khảo nhé.

 

[leonardo208]

Đã sửa lại theo cách dùng hàm OFFSET kết hợp hàm Match và Countif.

 

[chimaiyeunguoi]

Thầy ơi, Thầy có thể giải thích rõ công thức này cho em được không?
=OFFSET($B$1,(5*(ROW()-ROW(E$1)))+(COLUMN()-COLUMN($E1)),0,1,1)

 

[leonguyenz]

... có thể giải thích rõ công thức này cho em được không?
=OFFSET($B$1,(5*(ROW()-ROW(E$1)))+(COLUMN()-COLUMN($E1)),0,1,1)

Bạn nên trả lời kèm trích dẫn để biết nguồn và tác giả.
Muốn hiểu công thức thì cần nắm rõ cấu trúc từng hàm:
OFFSET(ô hoặc vùng tham chiếu, bước nhảy dòng, bước nhảy cột, chiều cao vùng tham chiếu, chiều rộng vùng tham chiếu)
ROW() vị trí dòng tại ô chứa công thức, tương tự COLUMN()
ROW(E$1) = 1; COLUMN($E1) = 5, có thể dùng hằng số thay cho 2 công thức này.
Kết quả công thức trên dựa vào vị trí đặt công thức, nên có file đính kèm để hiểu rõ hơn.

 

[chimaiyeunguoi]

File đính kèm em đưa lên, Thầy chỉ giúp em với.

 

[giaiphap]

File đính kèm em đưa lên, Thầy chỉ giúp em với.

Cái này được giải đáp như #16 là ổn rồi, để hiểu sâu hơn bạn chọn ô chứa công thức, chọn (tô khối) vào phần bạn chưa hiểu và nhấn F9 để xem kết quả. Theo tôi nghĩ đây là cách đơn giản nhất để hiểu công thức.