Hỏi cách đặt Name động tự động thay đổi tham chiếu theo một giá trị cho trước (1 người xem)

[BNTT]

Tôi có bảng tính sau đây:

Từ cột D đến cột O, là các dữ liệu tương ứng với từng con giáp, và tôi sẽ truy xuất dữ liệu dựa theo các cột này.
Dĩ nhiên, tôi có thể tạo ra 12 Name động tương ứng với 12 con giáp đó. Ví dụ Name Tý:

=OFFSET(Sheet1!$D$1,0,0,COUNTA(Sheet1!$D:$D),1)​

Nhưng, nếu tạo 12 Name (và có thể hơn nữa, vì đây chỉ là file ví dụ), thì sợ rằng Excel sẽ ì ạch, nên tôi nghĩ ra chuyện này:

Tại ô B1, tôi dùng Data Validation để chọn ra một con giáp từ danh sách ở A1:A12.
Và tôi muốn rằng, tôi chỉ đặt một Name mà thôi, và Name này tự động thay đổi tham chiếu, tùy thuộc vào ô B1.
Nghĩa là, cái công thức OFFSET ở trên sẽ tự động thay đổi mấy cái tôi tô đậm màu đỏ.

Ví dụ, B1="Tý", thì công thức y như ở trên đây.
B1 = "Thìn" như trong hình, thì công thức sẽ tự thay đổi như vầy:

=OFFSET(Sheet1!$H$1,0,0,COUNTA(Sheet1!$H:$H),1)​

Để xác định cái $H$1, tôi sẽ dùng công thức này:

=ADDRESS(1,MATCH($B$1,$D$1:$O$1,0))​

Nhưng để xác định cái $H:$H, thì tôi mò hoài hổng ra.

Làm sao để lấy giá trị trong B1, đi dò với dãy D1:O1, xem giá trị này đang nằm ở cột nào, thì lấy cái tên cột đó thay vào trong công thức OFFSET của Name?

Nhờ các bạn giúp dùm. Và cả cái công thức OFFSET cũng như ADDRESS tôi đã ghi ra ở trên, nếu có cái nào hay hơn, cũng xin chỉ dùm.

Xin cảm ơn trước.

 

[Đào Việt Cường]

Dear BNTT,
----------
Em chỉ nói theo cách hiểu của em thôi nhé (xin lỗi vì em rất lười mở file attach): Anh sử dụng kết hợp các hàm: MATCH, INDIRECT và OFFSET thế nào cũng ra, với điều kiện số thứ tự của 12 con giáp giữa dòng và cột phải tương ứng nhau.

Nếu mà anh hỏi nữa thì em sẽ đưa minh họa nên!!!

 

[Po_Pikachu]

Xin phép được đăng 1 cách cùi bắp, nhưng không tự động load được. Bác xem lại có chỉnh sửa gì không? Thân.

 

[Cao Mạnh Sơn]

To:BNTT
em làm thử 1 cách đế xác định số dòng theo từng name,công thức để tại cell B2,công thức này cũng có thể chuyển thành name để đưa vào công thức Offset nhưng em không đưa vào.Bác xem thử nhé

 

[salam]

Bác chỉ cần thêm hàm Substitute là được rồi mà.

=SUBSTITUTE(ADDRESS(1,MATCH($B$1,D1:$O$1,0),3),"1","")&":"&SUBSTITUTE(ADDRESS(1,MATCH($B$1,D1:$O$1,0),3),"1","")

Trong file em đặt name congiap Bác kiểm tra có đúng không.

 

[ptm0412]

Đang vội , không nói nhiều, xem file.
Dư 1 name, xoá và thay file đính kèm.
Dùng address, left, find và indirect. Name dễ hiểu, dễ làm, dễ sửa, ...
hihi!

 

[BNTT]

Xin cảm ơn tất cả.
Sau đây là một ít nhận xét: Bài của ai cũng đúng hết.
Tuy nhiên, nếu gọi là đúng chính xác, thì tôi chọn bài giải của Ptm0412 và Cao Mạnh Sơn.
Còn của Po_Pikachu và của Salam, thì chọn đúng cột nhưng số dữ liệu có trong mảng của Name tạo ra không chính xác (có lúc thì thừa, có lúc thì thiếu).
Riêng Đào Việt Cường thì không nhận xét được, vì không có nộp bài ...

 

[salam]

Em kiểm tra lại rồi của em đúng đấy chứ Bác.

 

[hoangdanh282vn]

Anh th­ử cái này xem ra sao : chỉ 1 Name ngắn gọn duy nhất

=OFFSET(INDIRECT(ADDRESS(1,MATCH($B$1,$A$1:$A$12,0)+3)),1,,COUNTA(OFFSET($A$1:$A$65535,,MATCH($B$1,$A$1:$A$12,0)+2))-1)

 

[BNTT]

Em kiểm tra lại rồi của em đúng đấy chứ Bác.

Lần này thì đúng, Salam à. Không hiểu sao lúc trưa test cái file kia thì lại thấy có lúc nó chọn sót dữ liệu.

-------------------------------

@HoangDanh: Cái công thức của em chỉ chọn được mỗi ô đầu tiên của dãy dữ liệu cần lấy ra. Em thử xem mấy bài của Ptm0412, Salam và Cao Mạnh Sơn, là em hiểu liền.

 

[hoangdanh282vn]

Lần này thì đúng, Salam à. Không hiểu sao lúc trưa test cái file kia thì lại thấy có lúc nó chọn sót dữ liệu.

-------------------------------

@HoangDanh: Cái công thức của em chỉ chọn được mỗi ô đầu tiên của dãy dữ liệu cần lấy ra. Em thử xem mấy bài của Ptm0412, Salam và Cao Mạnh Sơn, là em hiểu liền.

Do em ghi thiếu "$" nên khi anh copy vào sử dụng sẽ sai , em gửi lại :

=OFFSET(INDIRECT(ADDRESS(1,MATCH($B$1,$A$1:$A$12,0)+3)),1,,COUNTA(OFFSET($A$1:$A$65535,,MATCH($B$1,$A$1:$A$12,0)+2))-1)

 

[ndu96081631]

Quá dể... Làm thành 2 name sẽ dể hiểu hơn:

Rng1 =OFFSET(Sheet1!$C$1:$C$100,,MATCH(Sheet1!$B$1,Sheet1!$D$1:$O$1,0))

và name chính

Rng =OFFSET(Sheet1!$C$1,,MATCH(Sheet1!$B$1,Sheet1!$D$1:$O$1,0),COUNTA(Rng1),)

Nếu muốn thành 1 name thì thế Rng1 vào Rng (nhưng dài hơn)

Rng=OFFSET(Sheet1!$C$1,,MATCH(Sheet1!$B$1,Sheet1!$D$1:$O$1,0),COUNTA(OFFSET(Sheet1!$C$1:$C$100,,MATCH(Sheet1!$B$1,Sheet1!$D$1:$O$1,0))),)

Name động đã xong! Phần còn lại là chuyện nhỏ!
Các bạn chỉ nên dùng INDIRECT trong trường hợp chắc ăn 100% dử liệu trong tương lai sẽ không bị insert thêm dòng cột gì đó (nếu không sẽ sai bét)