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!

 

[BNTT]

To BNTT: Thế anh không thể dùng Vlookup được trong phần em đã trích ra được 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) đó!

Có nhiều cách giải mà anh!

Thì cũng đúng. Tuy nhiên cái vùng tham chiếu trích ra của Ca_Dafi, nó phải cố định (là cái vùng nằm hai cái dãi đen đen phải không).

Và nếu làm như vậy, thì biết đâu mà lần? Dùng nguyên cả Sheet 2 để làm vùng đệm à, lỡ chỉ có vài mặt hàng, thì phí quá; còn nếu như dùng tạm chừng 200 hàng để làm vùng đệm đó, mà người ta cần đến 201 mặt hàng, thì lại thiếu...

Vì thế cho nên anh muốn có một cái Vùng tham chiếu động, ứng với mặt hàng nào thì Vùng tham chiếu này sẽ chứa môt số lượng ô vừa đủ, không thừa, không thiếu, để mình dùng làm bảng dò tìm. Chẳng hạn như cái Rng của anh Ndu là đạt yêu cầu.

Thân.

 

[ndu96081631]

Đây là UDF:

Option Explicit
Function Tim(Rng As Range, Nhom As Variant, Ma As Variant, Col_Ma As Long, Col_KQ As Long) As Variant
  Dim WF As WorksheetFunction
  Dim Pos As Long, k As Long
  Dim Rng1 As Range, Rng2 As Range, Clls As Range
  Set WF = Application.WorksheetFunction
  With Rng
    Pos = WF.Match(Nhom, .Resize(, 1), 0)
    Set Rng1 = .Resize(, 1)(Pos + 1).Resize(.Rows.Count - Pos)
    For Each Clls In Rng1
      k = k + 1
      If Clls <> "" Then Exit For
    Next
    Set Rng2 = Rng1(, Col_Ma).Resize(k, .Columns.Count - Col_Ma + 1)
  End With
  Tim = WF.VLookup(Ma, Rng2, Col_KQ - 1, 0)
End Function

Nếu bạn chỉ muốn lấy Range thôi thì cũng có thể sửa lại UDF này... ngay đoạn:

Set Rng2 = Rng1(, Col_Ma).Resize(k, .Columns.Count - Col_Ma + 1)

chính là Range mà bạn cần tìm... và khi ấy sẽ sửa lại dòng cuối của Function là

Tim = Rng2.Address

(đương nhiên cũng sẽ phải bỏ bớt những chổ thừa đi)

 

[BNTT]

Bonus: Đánh số thứ tự theo từng nhóm

Vì câu hỏi tiếp theo này liên quan trực tiếp đến bài toán tôi đã hỏi ở trên, nên xin post vào chung một topic luôn.

Trong file đính kèm, là cái bảng tính đó, đã được cụ thể hơn:

|

A​

​

|

B​

​

|

C​

​

|

D​

​

|

E​

​

|

F​

​

|

1​

​

|

Nhóm hàng​

​

|

​

​

|

Mã hàng​

​

|

Số lượng​

​

|

Đơn vị tính​

​

|

Đơn giá​

​

|

2​

​

|Nhóm A| | | | | |

3​

​

| |G1|Gỗ sao|

3​

|

m3​

|

50,000​

|

4​

​

| |G2|Gỗ căm xe|

5​

|

m3​

|

45,000​

|

5​

​

| |G3|Gỗ huỳnh đàn|

2​

|

m3​

|

24,908​

|

6​

​

| |S1|Sơn ICI|

8​

|

kg​

|

24,100​

|

7​

​

| |D1|Đinh 10|

2​

|

kg​

|

23,456​

|

8​

​

| |D2|Đinh 8|

1​

|

kg​

|

98,402​

|

9​

​

| |D3|Đinh 6|

1​

|

kg​

|

9,843​

|

10​

​

| |S2|Sơn Bạch Tuyết|

5​

|

kg​

|

31,401​

|

11​

​

|Nhóm B| | | | | |

12​

​

| |G1|Gỗ huỳnh đàn|

2​

|

m3​

|

24,908​

|

13​

​

| |G2|Gỗ căm xe|

5​

|

m3​

|

45,000​

|

14​

​

| |S1|Sơn ICI|

8​

|

kg​

|

24,100​

|

15​

​

|Nhóm C| | | | | |

16​

​

| |G1|Gỗ sao|

3​

|

kg​

|

50,000​

|

17​

​

| |D1|Đinh 10|

4​

|

cái​

|

23,456​

|

18​

​

| |D2|Đinh 8|

7​

|

cái​

|

98,402​

|

19​

​

| |S1|Sơn Bạch Tuyết|

2​

|

kg​

|

31,401​

|

20​

​

| |S2|Sơn ICI|

3​

|

kg​

|

88,690​

|

Như đã nói, tôi muốn tính cho được để làm Nhóm hàng 1 thì mất hết bao nhiêu gỗ, bao nhiêu sơn, bao nhiêu đinh... Do đó phải thống kê theo từng mặt hàng. Mà vì cái vùng tham chiếu không thể biết trước là có bao nhiêu mặt hàng trong đó, có thể có 1, 2 hoặc cũng có thể có hàng trăm... Và khó nhất là không biết được mặt hàng đó tên là gì (có thể gỗ nằm trước sơn, cũng có thể đinh nằm sau gỗ...) nên không thể dựa vào tên hàng mà dò tìm.

Quay lại cái file của anh Ndu đã làm. Trong ô L7, anh lấy giá trị trong K7 đi dò với bảng Rng. Nhưng giá trị trong K7 là nhập thủ công vào. Lỡ mặt hàng của Nhóm 1 không có cái tên đó thì sao, VLOOKUP sẽ báo lỗi ngay. Do đó, tôi nghĩ, phải tạo một bảng mã số cho các mặt hàng này, dựa vào ký tự đầu (G = Gỗ, S= Sơn, v.v... mà cũng may là số mặt hàng không nhiều, chỉ khác chủng loại, khác tên hiệu), và phải đánh số cho nó.

Ý tôi là sẽ làm một cái cột phụ B như trong hình trên.

Xin nói rõ hơn ý của tôi: Đánh số thứ tự theo từng nhóm. Ví dụ, với gỗ, là G1, G2 rồi G3, G4... cho đến hết những cái gì là gỗ trong Nhóm 1; nhưng sang nhóm khác, thì lại đánh số lại, lại G1, G2...

Và, khi dò tìm, để xác định G1, G2 gì đó thì mình dùng ký tự đầu của mặt hàng, ghép với hàm ROW() để lấy ra, rồi đem cái mã số tự tạo thêm này đi dò trong Rng, mà như vậy thì có thể có luôn cái tên mặt hàng, chính xác theo từng nhóm.

Vậy, nhờ các bạn giúp, làm sao để đánh số được như tôi muốn trong cột B?

 

[ptm0412]

BNTT cho hỏi cái này liên quan đến cả 2 câu hỏi: (Câu hỏi này là bonus đúng nghĩa)
1. Câu hỏi 2, có dùng 1 cột phụ mã hàng, vậy sao trong câu hỏi 1 không dùng 1 cột phụ mã nhóm?
Nếu câu hỏi 1 dùng cột phụ mã nhóm tức là có thể sử dụng cấu trúc bảng như Thanh Phong. Công việc sẽ dễ dàng hơn.
2. Câu hỏi 2, nếu dùng mã hàng, thì nên có 1 bảng mã hàng + tên hàng + đơn vị tính + đơn giá. Và đừng cho rằng đây là bảng phụ. Bảng này sẽ dùng cho rất nhiều việc. Thí dụ như tính hàng tồn. Và bảng này có thể dùng để điền tên VT tự động tránh được việc gõ sai chính tả.

Trở lại câu hỏi 2, khi có bảng Danh mục Vật Tư chỉ cần tìm mã VT cho nhóm, và số lượng theo định mức, sau đó truy tìm những thứ còn lại.

 

[BNTT]

BNTT cho hỏi cái này liên quan đến cả 2 câu hỏi:
1. Câu hỏi 2, có dùng 1 cột phụ mã hàng, vậy sao trong câu hỏi 1 không dùng 1 cột phụ mã nhóm?
Nếu câu hỏi 1 dùng cột phụ mã nhóm tức là có thể sử dụng cấu trúc bảng như Thanh Phong. Công việc sẽ dễ dàng hơn.
2. Câu hỏi 2, nếu dùng mã hàng, thì nên có 1 bảng mã hàng + tên hàng + đơn vị tính + đơn giá. Và đừng cho rằng đây là bảng phụ. Bảng này sẽ dùng cho rất nhiều việc. Thí dụ như tính hàng tồn. Và bảng này có thể dùng để điền tên VT tự động tránh được việc gõ sai chính tả.

Trở lại câu hỏi 2, khi có bảng Danh mục Vật Tư chỉ cần tìm mã VT cho nhóm, và số lượng theo định mức, sau đó truy tìm những thứ còn lại.

Em xin trả lời:

1. Cái này không phải là dữ liệu do em lập ra. Do đó, nếu chỉ thêm một cột phụ mã hàng (để tiện làm công thức cho cái bảng thống kê), rồi Hide cái cột phụ này đi, thì sẽ không làm hư cấu trúc ban đầu của bảng tính. Còn nếu làm lại như Phong nói (và ngay em cũng luôn làm như vậy) thì phải định dạng lại cả bảng tính của người ta. Cho dù người ta đồng ý, nhưng nếu từ cái bảng ban đầu (có cấu trúc như em đã gửi lên), chuyển thành một bảng bình thường (như của Phong), hỏi bác rằng, có đơn giản không? Với một file được gửi đến có hàng trăm nhóm khác nhau, và mỗi nhóm lại có hàng chục cái mã hàng khác nhau?
   
   Nếu làm được, em sẽ nhận cái file này của người ta hằng tuần (qua email). Mỗi tuần mỗi khác. Và phải gửi lại bảng kết quả trong vòng 6 tiếng. Cho nên em mới tìm cách nào để không thay đổi cấu trúc của họ, mà chỉ cần chèn thêm một cái cột phụ vào, nhập công thức để tạo ra mã, sau đó dùng cái mã hàng hàng đó đi dò với cái Dãy động mà em đã hỏi. Nhanh hơn là phải ngồi định dạng lại cái bảng tính của người ta.
   
   Em có khuyên người ta đổi lại cách trình bày, nhưng không được. Mấy năm nay họ đã làm như vậy, và không muốn thay đổi: Có làm được hay không thì bảo, chứ đừng có ý kiến ý cò gì hết. Em thì lại không muốn sổng mất cái con cá lớn này...
   
   
2. Không thể tạo một bảng danh mục hàng hóa. Bởi vì, mình không lường trước được họ sẽ sử dụng loại gì cho Nhóm hàng nào đó. Nếu có tự làm, thì cũng chỉ có mỗi đơn vị tính là cố định, còn giá thì thay đổi theo thị trường, không lần nào giống lần nào. Thậm chí, giá của gỗ sao trong Nhóm hàng 1 và giá của gỗ sao trong Nhóm hàng 2 còn không giống nhau (lý do: xài hết 1m3 gỗ thì giá khác, mà chỉ xài có 0,5m3 gỗ thì giá khác...)
   
   Tạm thời em chấp nhận cả chuyện họ nhập sai chính tả. Và để ngừa chuyện đó, em mới nghĩ ra cái trò làm cột phụ, dựa theo chữ cái đầu tiên của mặt hàng. Vì em nghĩ rằng, có gõ sai đi nữa thì chữ đầu tiên của gỗ cũng là G, chữ đầu tiên của Sơn cũng là S... Sau đó em dùng cái mã này móc ra cái tên thật của nó (có sai chính tả thì cũng cho sai chính tả luôn).
   
   Em không quan tâm đến chuyện hàng tồn. Nhiệm vụ của em chỉ là bóc ra từng Nhóm hàng, in riêng mỗi Nhóm hàng ra một tờ giấy, và một bảng tổng hợp lại cho họ... Rồi lấy tiền công. Thế thôi.

 

[rollover79]

Cho tôi hỏi thêm 1 chút, vậy nếu có thêm 1 mặt hàng là Gạch chẳng hạn thì cái cột B sẽ đánh thế nào? Vì G đã dùng cho Gỗ mất rồi. Ý tôi muốn hỏi trong trường hợp có nhiều vật liệu trùng ký tự đầu thì nguyên tắc để điền dữ liệu vào cột thứ tự B là thế nào?

 

[BNTT]

Cho tôi hỏi thêm 1 chút, vậy nếu có thêm 1 mặt hàng là Gạch chẳng hạn thì cái cột B sẽ đánh thế nào? Vì G đã dùng cho Gỗ mất rồi. Ý tôi muốn hỏi trong trường hợp có nhiều vật liệu trùng ký tự đầu thì nguyên tắc để điền dữ liệu vào cột thứ tự B là thế nào?

Cảm ơn bạn.
Nhưng tôi hỏi bài toán này là cho một trường hợp cụ thể nhất định. Sản phẩm làm ra chỉ có bàn ghế, tủ, giường, tượng gỗ, cầu thang, cửa... nói chung liên quan đến gỗ là chính. Tôi đã biết hết các "nguyên liệu đầu vào" của họ, và không có mặt hàng nào trùng nhau chữ cái đầu tiên hết ạ.

Nguyên tắc để làm cái cột phụ B đó là =LEFT(C...)& "cái gì đó tôi chưa nghĩ ra"

"Cái gì đó" là thứ tự của các mặt hàng cùng loại trong một nhóm.

 

[viendo]

Cho góp vào 1 bài của yêu cầu 1, yêu cầu 2 chưa biết làm.

 

[cadafi]

|

A​

|

B​

|

C​

|

D​

|

E​

|

F​

|

1​

|

Nhóm hàng​

||

Mã hàng​

|

Số lượng​

|

Đơn vị tính​

|

Đơn giá​

|

2​

|Nhóm A| | | | | |

3​

| |G1|Gỗ sao|

3​

|

m3​

|

50,000​

|

4​

| |G2|Gỗ căm xe|

5​

|

m3​

|

45,000​

|

5​

| |G3|Gỗ huỳnh đàn|

2​

|

m3​

|

24,908​

|

6​

| |S1|Sơn ICI|

8​

|

kg​

|

24,100​

|

7​

| |D1|Đinh 10|

2​

|

kg​

|

23,456​

|

8​

| |D2|Đinh 8|

1​

|

kg​

|

98,402​

|

9​

| |D3|Đinh 6|

1​

|

kg​

|

9,843​

|

10​

| |S2|Sơn Bạch Tuyết|

5​

|

kg​

|

31,401​

|

11​

|Nhóm B| | | | | |

12​

| |G1|Gỗ huỳnh đàn|

2​

|

m3​

|

24,908​

|

13​

| |G2|Gỗ căm xe|

5​

|

m3​

|

45,000​

|

14​

| |S1|Sơn ICI|

8​

|

kg​

|

24,100​

|

15​

|Nhóm C| | | | | |

16​

| |G1|Gỗ sao|

3​

|

kg​

|

50,000​

|

17​

| |D1|Đinh 10|

4​

|

cái​

|

23,456​

|

18​

| |D2|Đinh 8|

7​

|

cái​

|

98,402​

|

19​

| |S1|Sơn Bạch Tuyết|

2​

|

kg​

|

31,401​

|

20​

| |S2|Sơn ICI|

3​

|

kg​

|

88,690​

|

Xin nói rõ hơn ý của tôi: Đánh số thứ tự theo từng nhóm. Ví dụ, với gỗ, là G1, G2 rồi G3, G4... cho đến hết những cái gì là gỗ trong Nhóm 1; nhưng sang nhóm khác, thì lại đánh số lại, lại G1, G2...

Và, khi dò tìm, để xác định G1, G2 gì đó thì mình dùng ký tự đầu của mặt hàng, ghép với hàm ROW() để lấy ra, rồi đem cái mã số tự tạo thêm này đi dò trong Rng, mà như vậy thì có thể có luôn cái tên mặt hàng, chính xác theo từng nhóm.

Vậy, nhờ các bạn giúp, làm sao để đánh số được như tôi muốn trong cột B?

Ô B2 anh gõ công thức:

B2=IF(A2<>"","",IF(B1="",LEFT(C2,1)&"1",LEFT(C2,1)&IF(LEFT(C2,1)=LEFT(B1,1),RIGHT(B1,LEN(B1)-1)+1,1)))

Sau đó fill công thức xuống! Anh xem thêm file đính kèm nhé!

 

[rollover79]

To ca_di: Công thức của ca_di chỉ đúng khi tên vật liệu của mỗi nhóm là liền nhau(hết gạch đến gỗ, hết gỗ đến sơn, chứ trộn lẫn nhau là có vấn đề)
- Không biết có được dùng cột giả không, nếu được phép làm cột giả thì bạn thử xem file này xem có đúng ý không nhé.

 

[cadafi]

To ca_di: Công thức của ca_di chỉ đúng khi tên vật liệu của mỗi nhóm là liền nhau(hết gạch đến gỗ, hết gỗ đến sơn, chứ trộn lẫn nhau là có vấn đề)
- Không biết có được dùng cột giả không, nếu được phép làm cột giả thì bạn thử xem file này xem có đúng ý không nhé.

Vâng, đúng như vậy! Do đó để tránh việc này và không cần cột phụ thì dữ liệu cột C phải được sort trước!

 

[ndu96081631]

Cho đồng chí cái UDF xài cho gọn... Cú pháp:

STT(Rng,Ten)

Code:

Option Explicit
Function STT(Rng As Range, Ten As String) As String
   Dim TempRng As Range, Cll1 As Range, Cll2 As Range
   Dim k As Long, l As Long, Temp As Long
   Temp = 1
   For Each Cll1 In Rng
     k = k + 1
     If Cll1 = "" Then Temp = k
   Next
   Set TempRng = Rng(Temp).Resize(Rng.Rows.Count - Temp + 1)
   For Each Cll2 In TempRng
     If Left(Cll2, 1) = Left(Ten, 1) Then: l = l + 1
   Next
   If Ten = "" Then
     STT = ""
   Else:
     STT = Left(Ten, 1) & l
   End If
End Function

 

[ALOAN]

Em thấy chổ này chưa đúng ý anh BNTT:
Đ1 D1 Đinh 10
Đ2 D2 Đinh 8
Đ3 D3 Đinh 6

 

[ndu96081631]

Em thấy chổ này chưa đúng ý anh BNTT:
Đ1 D1 Đinh 10
Đ2 D2 Đinh 8
Đ3 D3 Đinh 6

Cái đó không quan trọng đâu Đ với D cũng vậy... Mà tôi nghĩ cũng nên phân biệt Đ và D chứ nhỉ !!! ví dụ Đinh và Dầu
Còn như thích Dê và không chịu Đê thì... TỰ SỬA
Ẹc... Ec...

 

[rollover79]

Nếu dùng UFD thì xài thử đoạn code này xem

Function STT(Rng As Range, strTen As String)
    Dim i As Long
    Dim iSTT As Long
    Dim strRet As String
    For i = Rng.Rows.Count To 1 Step -1
        If Rng.Cells(i,1) & "" = "" Then Exit For
        If Left(Rng.Cells(i,1), 1) = Left(strTen, 1) Then iSTT = iSTT + 1
    Next
    strRet = Left(strTen, 1)
    If strRet <> "" Then strRet = strRet & iSTT
    STT = strRet
End Function

 

[viendo]

Quay lại cái file của anh Ndu đã làm. Trong ô L7, anh lấy giá trị trong K7 đi dò với bảng Rng. Nhưng giá trị trong K7 là nhập thủ công vào. Lỡ mặt hàng của Nhóm 1 không có cái tên đó thì sao, VLOOKUP sẽ báo lỗi ngay. Do đó, tôi nghĩ, phải tạo một bảng mã số cho các mặt hàng này, dựa vào ký tự đầu (G = Gỗ, S= Sơn, v.v... mà cũng may là số mặt hàng không nhiều, chỉ khác chủng loại, khác tên hiệu), và phải đánh số cho nó.

Ý tôi là sẽ làm một cái cột phụ B

Thật tình tôi cũng chưa hiểu được tại sao lại phải dùng cái cột phụ B nữa, nếu gọi cột này là mã thì cũng không đúng vì cùng là G1 ở nhóm A là "Gỗ sao" nhưng G1 ở nhóm B lại là "Gỗ huỳnh đàn".

Theo tôi thì khi xác định được dãy tham chiếu động theo từng nhóm hàng thì có thể xác định được dễ dàng dãy mã hàng của nhóm đó, muốn trích ra hay dùng làm nguồn cho validation đều được mà.

 

[BNTT]

Thật tình tôi cũng chưa hiểu được tại sao lại phải dùng cái cột phụ B nữa, nếu gọi cột này là mã thì cũng không đúng vì cùng là G1 ở nhóm A là "Gỗ sao" nhưng G1 ở nhóm B lại là "Gỗ huỳnh đàn".

Theo tôi thì khi xác định được dãy tham chiếu động theo từng nhóm hàng thì có thể xác định được dễ dàng dãy mã hàng của nhóm đó, muốn trích ra hay dùng làm nguồn cho validation đều được mà.

G1, G2 không phải là mã số cố định..
Nó chỉ nói lên rằng: G1 = mặt hàng gỗ thứ 1 trong danh sách (gỗ gì không cần biết), G2 = mặt hàng gỗ thứ 2 trong danh sách.

Vấn đề này tôi giải quyết gần tạm xong, được chừng 80% rồi. Đang tạm gác lại để xử cái bài bên kia, có cùng nội dung, hơi khác hình thức một chút, và dễ hơn, bởi vì người ta chia đơn giá thành 3 nhóm, và thay vì gán mã số (ở cột phụ B) cho mã hàng, tôi xoay sang gán mã số cho... đơn giá. Nghe tức cười thế, nhưng đã thành công.

Trong khi chờ đợi tôi thông báo kết quả bên này, mời các bạn ngó thử dùm tôi cái file bên kia: http://www.giaiphapexcel.com/forum/showthread.php?t=14065 (bài 29)