Dãy tham chiếu động? (1 người xem)

[BNTT]

Tôi có vấn đề như sau: Tôi muốn tạo ra một bảng tham chiếu động, nghĩa là tự động thay đổi khi cung cấp cho nó một thông tin gì đó.

Ví dụ, tôi có bảng tính sau:

|

A​

​

|

B​

​

|

C​

​

|

D​

​

|

E​

​

|

1​

​

|

Nhóm hàng​

​

|

Mã hàng​

​

|

Số lượng​

​

|

Đơn vị tính​

​

|

Đơn giá​

​

|

2​

​

|Nhóm A| | | | |

3​

​

| |

Mã 001​

|

3​

|

kg​

|

50,000​

|

4​

​

| |

Mã 002​

|

5​

|

mét​

|

45,000​

|

5​

​

| |

Mã 003​

|

2​

|

cái​

|

24,908​

|

6​

​

| |

Mã 004​

|

8​

|

cái​

|

24,100​

|

7​

​

|Nhóm B| | | | |

8​

​

| |

Mã 003​

|

2​

|

cái​

|

24,908​

|

9​

​

| |

Mã 004​

|

4​

|

cái​

|

24,100​

|

10​

​

| |

Mã 005​

|

8​

|

tấn​

|

49,305​

|

11​

​

|Nhóm C| | | | |

12​

​

| |

Mã 001​

|

2​

|

kg​

|

50,000​

|

13​

​

| |

Mã 003​

|

4​

|

cái​

|

24,908​

|

14​

​

| |

Mã 004​

|

7​

|

cái​

|

24,100​

|

15​

​

| |

Mã 006​

|

2​

|

tạ​

|

39,021​

|

16​

​

| |

Mã 007​

|

3​

|

cm​

|

88,690​

|

Trong bảng tính này, mỗi nhóm hàng có thể có nhiều mã hàng, đương nhiên là đơn vị tính và đơn giá của mỗi mã hàng là giống nhau, nhưng số lượng các mã trong mỗi nhóm thì khác nhau.

Đây là trường hợp dùng để tính giá thành cho một mặt hàng. Ví dụ:

• Để làm một cái bàn thì phải có 0,5m3 gỗ, 50 cây đinh, 2kg sơn
• Để làm một cái ghế thì phải có 0,1m3 gỗ, 10 cây đinh, 0,5kg sơn
• v.v...

Đơn giá của gỗ, đinh và sơn là giống nhau, nhưng số lượng thì khác nhau.

Để xác định giá vốn (cho nhiều nhóm), tôi phải xác định được số lượng, đơn giá và đơn vị tính của những mặt hàng này.

Tuy nhiên, tôi không thể dò tìm theo khối cell B1:E16, bởi có nhiều mặt hàng trùng nhau. Do đó, tôi muốn tìm như thế này:

• Khi tôi nhập Nhóm 1, thì khối cell dùng để dò tìm của tôi là B3:E6
• Khi tôi nhập Nhóm 2, thì khối cell dùng để dò tìm của tôi là B8:E10
• Khi tôi nhập Nhóm 3, thì khối cell dùng để dò tìm của tôi là B12:E16
• V.v...

Vậy có cách nào tạo ra một Name động, tự thay đổi vùng tham chiếu như vậy hay không?

 

[ndu96081631]

Cái này tôi đã có từng nghĩ qua nhưng chưa có giãi pháp tốt nhất!
Góp ý riêng: Tốt nhất đồng chí đưa luôn file lên (cái Table ấy chỉ để xem cho trực quan thôi)... chứ bắt mọi người nhìn vào table rồi gõ dử liệu vào bảng tính để giã lập thì cực quá
(Giã lập luôn đây!)

 

[BNTT]

Ồ, em xin lỗi. Sẽ gửi cái ví dụ đó lên ngay đây.
Làm vậy bởi vì dùng cái Addin của thầy Long nhấn một cái xong đem qua nhấn thêm một cái cho nó lẹ í mà.

Em có nghĩ ra hướng giải quyết là:

1. Xác định địa chỉ của Nhóm 1, Nhóm 2... trong cột A
2. Từ địa chỉ đó, dùng OFFSET dịch xuống 1 hàng, ngang qua 1 cột
3. Cái khó là xác định cái tham số "số hàng" và "số cột" cho cái OFFSET này. Bởi vì làm sao cho nó biết rằng: "Nếu gặp phải ô trống thì đó là hàng cuối cùng (hoặc cột cuối cùng) của dãy tham chiếu"? Ví dụ, với Nhóm 1, thì chạy xuống tới B7 là gặp ô trống, hoặc chạy qua tới cột F là gặp ô trống, và dừng lại ở đó?

 

[TranThanhPhong]

Anh Tường thiết kế lại CSDL thì giải quyết được rất dễ!

|

A​

​

|

B​

​

|

C​

​

|

D​

​

|

E​

​

|

1​

​

|

Nhóm hàng​

​

|

Mã hàng​

​

|

Số lượng​

​

|

Đơn vị tính​

​

|

Đơn giá​

​

|

2​

​

|Nhóm A|

Mã 001​

|

3​

|

kg​

|

50,000​

|

3​

​

|Nhóm A|

Mã 002​

|

5​

|

mét​

|

45,000​

|

4​

​

|Nhóm A|

Mã 003​

|

2​

|

cái​

|

24,908​

|

5​

​

|Nhóm A|

Mã 004​

|

8​

|

cái​

|

24,100​

|

6​

​

|Nhóm B|

Mã 003​

|

2​

|

cái​

|

24,908​

|

7​

​

|Nhóm B|

Mã 004​

|

4​

|

cái​

|

24,100​

|

8​

​

|Nhóm B|

Mã 005​

|

8​

|

tấn​

|

49,305​

|

9​

​

|Nhóm C|

Mã 001​

|

2​

|

kg​

|

50,000​

|

10​

​

|Nhóm C|

Mã 003​

|

4​

|

cái​

|

24,908​

|

11​

​

|Nhóm C|

Mã 004​

|

7​

|

cái​

|

24,100​

|

12​

​

|Nhóm C|

Mã 006​

|

2​

|

tạ​

|

39,021​

|

13​

​

|Nhóm C|

Mã 007​

|

3​

|

cm​

|

88,690​

|

Thanh Phong

 

[BNTT]

Nếu thiết kế như Phong nói thì ... có gì để hỏi đâu, Phong ơi?
Vấn đề là ở chỗ này:

1. Trong cái vùng trống ở cột A, bên dưới các Nhóm 1, Nhóm 2,... người ta có thể chua thêm chú thích gì đó vào... Ví dụ một cái hình mô tả cho sản phẩm đó, quy cách của sản phẩm đó...
2. Anh hỏi cái này là để xử một cái đơn đặt hàng, làm theo yêu cầu của khách hàng... Chứ nếu là anh làm cho anh thì đương nhiên anh sẽ thiết kế theo kiểu mà anh dễ xử lý nhất.

 

[rollover79]

Trong file này tôi chưa thấy yêu cầu cụ thể là như thế nào.

 

[BNTT]

Trong file này tôi chưa thấy yêu cầu cụ thể là như thế nào.

Yêu cầu cụ thể nằm ở bài #1. Bạn xem lại dùm. Đã trình bày rõ ràng ở đây rồi mà.

 

[ndu96081631]

Tạm thời xài cái này thử xem!
Chưa hay lắm nhưng có còn hơn không
Mục đích: Xác định "cái cần tìm" nằm ở vị trí thứ bao nhiêu.. và cái "nằm bên dưới cái cần tìm" nằm ở vị trí bao nhiêu!
Có 2 móc này thì hoàn toàn có thể xác định được Range
Tôi không ũng hộ cách bố trí dử liệu này nhưng thấy cũng có rất nhiều người hay xài.. Vì thế mình phải theo để tìm giãi pháp

 

[huuthang_bd]

Mình cũng xác định độ cao của vùng tham chiếu dựa vào loại hàng tiếp theo như ndu nhưng bằng cột phụ. Các bác xem thử nha.

 

[cadafi]

Các bác xem file đính kèm! Em làm theo hướng thế này có được không! Không cột phụ, không dùng Name. Chỉ cần xác định vị trí đầu tiên!

=IF(OR(A3=0,A3=""),"",INDIRECT("Data!"&ADDRESS(MATCH($A$1&" "&$B$1,Data!$A:$A,0)+ROW()-3,COLUMN()+1)))

Sau đó Ctrl+D, Ctrl+R

 

[rollover79]

Tôi đọc bài #01 rồi nhưng vẫn chưa hiểu rõ về yêu cầu lắm . Nhưng nếu nó giống như cái ví dụ của ndu thì bạn xem thử cách này xem có đơn giản hơn không.

 

[ThuNghi]

Nếu thiết kế như Phong nói thì ... có gì để hỏi đâu, Phong ơi?
Vấn đề là ở chỗ này:

1. Trong cái vùng trống ở cột A, bên dưới các Nhóm 1, Nhóm 2,... người ta có thể chua thêm chú thích gì đó vào... Ví dụ một cái hình mô tả cho sản phẩm đó, quy cách của sản phẩm đó...
2. Anh hỏi cái này là để xử một cái đơn đặt hàng, làm theo yêu cầu của khách hàng... Chứ nếu là anh làm cho anh thì đương nhiên anh sẽ thiết kế theo kiểu mà anh dễ xử lý nhất.

Người ta bảo sao thì phải làm vậy à, mình phải tư vấn, SMod của GPE không thể HD tạo giải pháp từ 1 cơ sở dữ liệu như vậy.
Mình thấy theo table của Phong là OK, nếu không thích thì có thể dùng CF để fill màu trắng cho không ai thấy. Dùng như vậy khi tính giá thành còn muôn "điên" nữa, mình đã hỏi vấn đề này trên GPE về tính giá thành theo định mức.
Còn muốn nhóm 1, 2 có hình và thông số KT thì phải dùng 1 table khác và nếu có thể dùng relation. (Quan hệ Main - Sub).
Còn dùng như trên thì không tổng quát mà rất khó, KH đòi thêm định mức thì thua.
Chúc BNTT thêm "TT"!

 

[ndu96081631]

Tôi nghĩ dạng dử liệu này cũng khá phổ biến nên tốt nhất các cao thủ nên biến nó thành 1 UDF cho gọn... Mục đích của UDF này cũng là xác định được vùng tham chiếu
(Tôi cũng đã làm ra bằng For nhưng thấy dở quá nên chưa đưa lên)
Đại khái UDF sẽ có cú pháp:

Tim(Rng As Rang, Lookup_Value As Variant, Col_Index1 As Integer, Col_Index2 As Integer) As Variant

Rng: vùng tham chiếu
Lookup_Value: giá trị tìm (chẳng hạn trong file đó là Mã hàng)
Col_Index1: là cột chứa giá trị Lookup_Value
Col_Index2: là cột cần lấy kết quả

 

[ndu96081631]

Tôi đọc bài #01 rồi nhưng vẫn chưa hiểu rõ về yêu cầu lắm . Nhưng nếu nó giống như cái ví dụ của ndu thì bạn xem thử cách này xem có đơn giản hơn không.

Cái này không được đâu! Vì mục đích của chúng ta là tìm xem vùng ấy là vùng nào mà
Ở đây bạn lại tự đặt name cho vùng... thế thì còn gì là động? Dử liệu người ta nhập vào vô chừng, nếu có khoảng 200 nhóm thì 200 name sao?
Hic

 

[cadafi]

To ndu96081631, rollover79, huuthang_bd:
Hình như file các anh chưa đúng ý với yêu cầu của tác giả thì phải! Khi người dùng chọn nhóm nào thì sẽ liệt kê ra các mã hàng, số lượng, đơn giá, đơn vị tính của nhóm hàng đó! Dạng liệu kê Bill Of Material /Thẻ tính giá thành! Xem thử file của em và cho ý kiến xem sao! Bài #10

 

[hoangminhtien]

các bác xem cách này có được không ạ?

 

[BNTT]

To ndu96081631, rollover79, huuthang_bd:
Hình như file các anh chưa đúng ý với yêu cầu của tác giả thì phải! Khi người dùng chọn nhóm nào thì sẽ liệt kê ra các mã hàng, số lượng, đơn giá, đơn vị tính của nhóm hàng đó! Dạng liệu kê Bill Of Material /Thẻ tính giá thành! Xem thử file của em và cho ý kiến xem sao! Bài #10

Đã xem thử file của Ca_Dafi. Ý tưởng rất hay, nhưng không đúng yêu cầu.
Yêu cầu ở đây là: Xác định vùng tham chiếu dựa theo tên nhóm:

• Khi tôi nhập Nhóm 1, thì khối cell dùng để dò tìm của tôi là B3:E6
• Khi tôi nhập Nhóm 2, thì khối cell dùng để dò tìm của tôi là B8:E10
• Khi tôi nhập Nhóm 3, thì khối cell dùng để dò tìm của tôi là B12:E16

Chứ không phải là trích lọc hay liệt kê ra dữ liệu. Ca_Dafi hiểu chứ?
Sau khi xác định được vùng tham chiếu, sẽ dùng vùng tham chiếu này để dò tìm dữ liệu, hoặc trích ra những thứ mình cần.

Cách của anh Ndu (bài #8) rất đúng rồi. Cảm ơn anh nhiều.

Các bạn mở cái file của anh Ndu ra xem đi. Khi thay đổi tên nhóm ở J7, thì vùng tham chiếu Rng được tự động xác định chính xác là vùng nào, theo đúng yêu cầu mà tôi vừa trích dẫn lại trên đây: Ứng với mỗi nhóm hàng, địa chỉ của Rng mỗi khác. Và các công thức VLOOKUP trong L7, M7, N7, lấy giá trị trong K7 để dò tìm trong cái bảng Rng tương ứng đó.

 

[huuthang_bd]

To ndu96081631, rollover79, huuthang_bd:
Hình như file các anh chưa đúng ý với yêu cầu của tác giả thì phải! Khi người dùng chọn nhóm nào thì sẽ liệt kê ra các mã hàng, số lượng, đơn giá, đơn vị tính của nhóm hàng đó! Dạng liệu kê Bill Of Material /Thẻ tính giá thành! Xem thử file của em và cho ý kiến xem sao! Bài #10

Mình nghĩ không phải ý tác giả vậy đâu. Bạn xem lại bài #1. Vấn đề ở đây nghĩ ra một công thức tạo ra một vùng tham chiếu động. Tác giả muốn dựa vào mã hàng và loại hàng để xác định Số lượng, Đơn vị tính, Đơn giá. Nghĩa là dò nhưng ứng với mỗi loại hàng sẽ có một vùng tham chiếu khác nhau chứ không phải liệt kê.

 

[BNTT]

Người ta bảo sao thì phải làm vậy à, mình phải tư vấn, SMod của GPE không thể HD tạo giải pháp từ 1 cơ sở dữ liệu như vậy.
Mình thấy theo table của Phong là OK, nếu không thích thì có thể dùng CF để fill màu trắng cho không ai thấy. Dùng như vậy khi tính giá thành còn muôn "điên" nữa, mình đã hỏi vấn đề này trên GPE về tính giá thành theo định mức.
Còn muốn nhóm 1, 2 có hình và thông số KT thì phải dùng 1 table khác và nếu có thể dùng relation. (Quan hệ Main - Sub).
Còn dùng như trên thì không tổng quát mà rất khó, KH đòi thêm định mức thì thua.
Chúc BNTT thêm "TT"!

Vâng! Khách hàng là thượng đế mà anh.
Em cũng không thích bố trí dữ liệu theo kiểu này. Nhưng sửa lại dữ liệu của người ta là điều không được phép.
Nếu mình cứ cố mà tư vấn, họ sẽ bỏ mình, đem đi nhờ chỗ nào đó "biết vâng lời mà không tư vấn"!
Đôi khi mình đúng, nhưng với người ta thì như vậy là không đúng.
Cuộc đời này còn biết bao điều vô lý giống như vậy, nhưng để được việc (để có thêm thu nhập chẳng hạn) thì mình phải chấp nhận thôi. Không phải khi nào tư vấn cho khách hàng cũng là một điều hay.

 

[cadafi]

Đã xem thử file của Ca_Dafi. Ý tưởng rất hay, nhưng không đúng yêu cầu.
Yêu cầu ở đây là: Xác định vùng tham chiếu dựa theo tên nhóm:

Chứ không phải là trích lọc hay liệt kê ra dữ liệu. Ca_Dafi hiểu chứ?
Sau khi xác định được vùng tham chiếu, sẽ dùng vùng tham chiếu này để dò tìm dữ liệu, hoặc trích ra những thứ mình cần.

Cách của anh Ndu (bài #8) rất đúng rồi. Cảm ơn anh nhiều.

Các bạn mở cái file của anh Ndu ra xem đi. Khi thay đổi tên nhóm ở J7, thì vùng tham chiếu Rng được tự động xác định chính xác là vùng nào, theo đúng yêu cầu mà tôi vừa trích dẫn lại trên đây: Ứng với mỗi nhóm hàng, địa chỉ của Rng mỗi khác. Và các công thức VLOOKUP trong L7, M7, N7, lấy giá trị trong K7 để dò tìm trong cái bảng Rng tương ứng đó.

To BNTT: Thế anh không thể dùng Vlookup được trong phần em đã trích ra hay sao? Phần đã trích lọc ra thì địa chỉ không thay đổi, đúng không anh!

Giải pháp trong bài em đề cập là gì!? là như thế này:
Trích ra phần dữ liệu thỏa mãn yêu cầu điều kiện nhóm sang một vùng khác đã biết trước. Sau đó, muốn tìm kiếm gì thì tìm kiếm trên vùng đã xác định (trích ra) đó!
Dù gì, muốn Vlookup cũng phải tham chiếu Range, em có Range Sheet2!A là luôn cố định. Vậy người dùng sử dụng Vlookup có tiện hơn không? (hàm khác cũng tương tự)

Có nhiều cách giải để giải quyết một bài toán mà anh!