Dãy tham chiếu động?

BNTT · 24/9/08

ca_dafi đã viết:
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 · 24/9/08

Đây là UDF:

PHP:

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 · 24/9/08

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 · 24/9/08

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 · 24/9/08

ptm0412 đã viết:
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:

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...
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 · 24/9/08

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 · 24/9/08

rollover79 đã viết:
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 · 24/9/08

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 · 24/9/08

BNTT đã viết:
| 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 · 24/9/08

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 · 24/9/08

rollover79 đã viết:
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 · 24/9/08

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

STT(Rng,Ten)

Code:

PHP:

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 · 24/9/08

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

ndu96081631 · 24/9/08

ALOAN đã viết:
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 · 24/9/08

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

Mã:

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 · 24/9/08

BNTT đã viết:
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 · 24/9/08

viendo đã viết:
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)

Dãy tham chiếu động?

Bùi Nguyễn Triệu Tường

Huyền thoại GPE

File đính kèm

Bùi Nguyễn Triệu Tường

File đính kèm

Bad Excel Member

Bùi Nguyễn Triệu Tường

Thành viên tiêu biểu

Bùi Nguyễn Triệu Tường

Tầm sư học Excel

File đính kèm

Hành động từ trái tim

File đính kèm

Thành viên tiêu biểu

File đính kèm

Hành động từ trái tim

Huyền thoại GPE

File đính kèm

Thành viên chính thức

Huyền thoại GPE

Thành viên tiêu biểu

Tầm sư học Excel

Bùi Nguyễn Triệu Tường

Facebook

Group