Cho em hỏi INDIRECT("Sheet"&{1;2;3}&"!A2:A100") gồm 300 phần tử (hay3 phần tử)? (1 người xem)

[LinDan]

Theo em hiểu thì A2:A100 bao gồm 100 phần tử, như vậy tính trên 3 sheet nó sẽ bao gồm (3x100=300) phần tử.

Tuy nhiên, khi em đang nghiên cứu công thức bài thày Ndu tặng diễn đàn:

=VLOOKUP($A2;INDIRECT("Sheet"&MATCH(1;COUNTIF(INDIRECT("Sheet"&{1;2;3}&"!A2:A100");$A2);0)&"!A2:C100");3;0)

do em chưa quen lắm với em Indirect, em tách riêng từng cụm nhỏ ra tìm hiểu, cụ thể:

INDIRECT("Sheet"&{1;2;3}&"!A2:A100")

, khi bôi đen bấm F9 thì em thấy nó chỉ có 3 phần tử?

Xin nhờ sự trợ giúp của diễn đàn, như vậy công thức INDIRECT("Sheet"&{1;2;3}&"!A2:A100") có bao nhiêu phần tử?

 

[ndu96081631]

Xin nhờ sự trợ giúp của diễn đàn, như vậy công thức INDIRECT("Sheet"&{1;2;3}&"!A2:A100") có bao nhiêu phần tử?

Đương nhiên là mảng 3 phần tử rồi ---> Bôi đen đoạn "Sheet"&{1;2;3}&"!A2:A100" rồi bấm F9 sẽ biết

 

[LinDan]

Đương nhiên là mảng 3 phần tử rồi ---> Bôi đen đoạn "Sheet"&{1;2;3}&"!A2:A100" rồi bấm F9 sẽ biết

Em vẫn biết khi ấn F9 nó là 3 phần tử, nhưng em không hiểu tại sao nó không là 300 phần tử (bởi nếu không là 300 thì hàm Countif lẽ ra chỉ tìm trong mỗi Sheet 01 dòng thôi, nhưng đây nó lại tìm trong toàn bộ từ A2:A100 mỗi Sheet 100 dòng)?

 

[ndu96081631]

Em vẫn biết khi ấn F9 nó là 3 phần tử, nhưng em không hiểu tại sao nó không là 300 phần tử (bởi nếu không là 300 thì hàm Countif lẽ ra chỉ tìm trong mỗi Sheet 01 dòng thôi, nhưng đây nó lại tìm trong toàn bộ từ A2:A100 mỗi Sheet 100 dòng)?

Vậy hỏi lại:
=COUNTIF(A1:A100, D1)
=SUM(A1:A100)
Chẳng lẽ bạn cũng cho đó là mảng 100 phần tử?

 

[LinDan]

Vậy hỏi lại:
=COUNTIF(A1:A100, D1)
=SUM(A1:A100)
Chẳng lẽ bạn cũng cho đó là mảng 100 phần tử?

Em chưa biết, mong sư phụ chỉ dùm
Em nghĩ sum và Countif đối số của nó là mảng nhưng kết quả ra 1 số duy nhất, nhưng Indirect nếu đối số là "dạng mảng" thì kết quả là mảng chứ, ví dụ:

=Indirect("A1:A100")

khi ấn F9 nó vẫn là 100 phần tử đấy chứ ah.

Thế thì

INDIRECT("Sheet"&{1;2;3}&"!A2:A100")

phải ra 300 phần tử?

 

[VMH0307]

Bạn so sánh thử các công thức sau (cả kết quả khi ấn F9 nhé)

[COLOR=#000000][FONT=Courier New][COLOR=#0000bb]INDIRECT[/COLOR][COLOR=#007700]([/COLOR][COLOR=#dd0000]"Sheet1[/COLOR][COLOR=#dd0000]"&"!A2:A100")[/COLOR][/FONT][/COLOR]

[COLOR=#000000][FONT=Courier New][COLOR=#0000bb]INDIRECT[/COLOR][COLOR=#007700]([/COLOR][/FONT][/COLOR][FONT=Courier New]"Sheet"[/FONT][COLOR=#000000][FONT=Courier New][COLOR=#007700]&[/COLOR][/FONT][/COLOR][COLOR=#ff0000][FONT=Courier New]row(1:3)&[/FONT][/COLOR][FONT=Courier New]"!A2:A100"[/FONT][COLOR=#000000][FONT=Courier New][COLOR=#007700]) [/COLOR][/FONT][/COLOR]

=[COLOR=#0000ff]INDEX(INDIRECT[/COLOR]("Sheet"&COLUMN(A:A)&"!A2:A100"),ROW(1:1),[COLOR=#ff0000]1[/COLOR])

(copy xuống dưới và sang ngang)

=[COLOR=#0000ff]INDEX(INDIRECT[/COLOR]("Sheet"&row(1:3)&"!A2:A100"),ROW(1:1),[COLOR=#ff0000]2[/COLOR])

Rồi rút ra kết luận nhé

 

[ndu96081631]

Em chưa biết, mong sư phụ chỉ dùm
Em nghĩ sum và Countif đối số của nó là mảng nhưng kết quả ra 1 số duy nhất, nhưng Indirect nếu đối số là "dạng mảng" thì kết quả là mảng chứ, ví dụ:

=Indirect("A1:A100")

khi ấn F9 nó vẫn là 100 phần tử đấy chứ ah.

Nên biết rằng INDIRECT có tác dụng biến 1 chuổi có dạng tham chiếu thành 1 tham chiếu thật sự
Giống như ta có:
"A1:A10" là 1 chuổi
Range("A1:A10") sẽ biến chuổi "A1:A10" thành 1 tham chiếu (tức là vùng A1:A10)
Khi bấm F9, Excel sẽ tính toán giá trị trong vùng ấy ---> Tương đương với Range("A1:A10").Value
Vì thế không thể nói Range("A1:A10") là mảng 10 phần tử được
Tương tự thế với hàm INDIRECT (vì INDIRECT cũng giống như Range(...) trong VBA)

Thế thì

INDIRECT("Sheet"&{1;2;3}&"!A2:A100")

phải ra 300 phần tử?

Phân tích "Sheet"&{1;2;3}&"!A2:A100" sẽ là mảng 3 phần tử ---> kết quả: {"Sheet1!A2:A100"; "Sheet2!A2:A100"; "Sheet3!A2:A100"} --->Đây là 3 phần tử dạng chuổi
Khi lồng INDIRECT vào, các chuổi trên sẽ biến thành 1 tham chiếu thật sự <==> Array(Sheet1.Range("A2:A100"), Sheet2.Range("A2:A100"), Sheet3.Range("A2:A100"))
Tham chiếu vẫn cứ là tham chiếu thôi. Khi bạn bấm F9, các tham chiếu ấy đã bị biến đổi thành giá trị nên ta nhìn thấy có 300 phần tử nhưng bản chất vấn đề không phải vậy
Tóm lại: A1:A10 chỉ là 1 vùng, không thể xem nó là mảng 10 phần tử (khi bấm F9 nó đã bị biến đổi rồi)
-----------
(Tôi dùng VBA để minh họa cho dễ hiểu)

 

[LinDan]

Bạn so sánh thử các công thức sau (cả kết quả khi ấn F9 nhé)

[COLOR=#000000][FONT=Courier New][COLOR=#0000bb]INDIRECT[/COLOR][COLOR=#007700]([/COLOR][COLOR=#dd0000]"Sheet1[/COLOR][COLOR=#dd0000]"&"!A2:A100")[/COLOR][/FONT][/COLOR]

[COLOR=#000000][FONT=Courier New][COLOR=#0000bb]INDIRECT[/COLOR][COLOR=#007700]([/COLOR][/FONT][/COLOR][FONT=Courier New]"Sheet"[/FONT][COLOR=#000000][FONT=Courier New][COLOR=#007700]&[/COLOR][/FONT][/COLOR][COLOR=#ff0000][FONT=Courier New]row(1:3)&[/FONT][/COLOR][FONT=Courier New]"!A2:A100"[/FONT][COLOR=#000000][FONT=Courier New][COLOR=#007700]) [/COLOR][/FONT][/COLOR]

=[COLOR=#0000ff]INDEX(INDIRECT[/COLOR]("Sheet"&COLUMN(A:A)&"!A2:A100"),ROW(1:1),[COLOR=#ff0000]1[/COLOR])

(copy xuống dưới và sang ngang)

=[COLOR=#0000ff]INDEX(INDIRECT[/COLOR]("Sheet"&row(1:3)&"!A2:A100"),ROW(1:1),[COLOR=#ff0000]2[/COLOR])

Rồi rút ra kết luận nhé

---

Nên biết rằng INDIRECT có tác dụng biến 1 chuổi có dạng tham chiếu thành 1 tham chiếu thật sự
Giống như ta có:
"A1:A10" là 1 chuổi
Range("A1:A10") sẽ biến chuổi "A1:A10" thành 1 tham chiếu (tức là vùng A1:A10)
Khi bấm F9, Excel sẽ tính toán giá trị trong vùng ấy ---> Tương đương với Range("A1:A10").Value
Vì thế không thể nói Range("A1:A10") là mảng 10 phần tử được
Tương tự thế với hàm INDIRECT (vì INDIRECT cũng giống như Range(...) trong VBA)

Phân tích "Sheet"&{1;2;3}&"!A2:A100" sẽ là mảng 3 phần tử ---> kết quả: {"Sheet1!A2:A100"; "Sheet2!A2:A100"; "Sheet3!A2:A100"} ---> Đây là 3 phần tử dạng chuổi
Khi lồng INDIRECT vào, các chuổi trên sẽ biến thành 1 tham chiếu thật sự <==> Array(Sheet1.Range("A2:A100"), Sheet2.Range("A2:A100"), Sheet3.Range("A2:A100"))
Tham chiếu vẫn cứ là tham chiếu thôi. Khi bạn bấm F9, các tham chiếu ấy đã bị biến đổi thành giá trị nên ta nhìn thấy có 300 phần tử nhưng bản chất vấn đề không phải vậy
Tóm lại: A1:A10 chỉ là 1 vùng, không thể xem nó là mảng 10 phần tử (khi bấm F9 nó đã bị biến đổi rồi)
-----------
(Tôi dùng VBA để minh họa cho dễ hiểu)

Giờ thì em đã cơ bản hơi hiểu được bản chất của nó rồi, quả thực đúng là cái này hơi trừu tượng đối với em (do em nhận thức chậm quá).

Trước do em không nhận thức được như đoạn thày giảng

A1:A10 chỉ là 1 vùng, không thể xem nó là mảng 10 phần tử

Em hiểu không biết có đúng không:
- Mảng khi Copy đi đâunó vẫn hiện ra kết quả như thế, ví dụ {2;3;4;5;6;7;8;9};
- Vùng khi di chuyển nó hiện ra KQ khác nhau, ví dụ Name tt=Sheet1!$A$1:$A$8
(nếu bấm F9 sẽ ra kêt quả như nhau nếu giá trị của các phần tử thuộc mảng chính là giá trị từ A1:A8)

 

[LinDan]

Em đang mày mò tìm khái niệm về phần tử, phần tử dạng chuỗi trên GPE về vấn đề này, em xin hỏi 2 cách viết sau đây có giống nhau không? (Theo em hiểu ý nghĩa của Indirect là giống nhau), nhưng khi ấn F9 nó khác nhau?

Cách 1:

=INDIRECT("Sheet"&ROW(1:1)&"!A2:A100")

Cách 2:

=Sheet1!A2:A100

 

[ndu96081631]

Em đang mày mò tìm khái niệm về phần tử, phần tử dạng chuỗi trên GPE về vấn đề này, em xin hỏi 2 cách viết sau đây có giống nhau không? (Theo em hiểu ý nghĩa của Indirect là giống nhau), nhưng khi ấn F9 nó khác nhau?

Cách 1:

=INDIRECT("Sheet"&ROW(1:1)&"!A2:A100")

Cách 2:

=Sheet1!A2:A100

Đã bao nhiêu lần tôi nói tránh dùng hàm ROW (nếu không cần thiết) rồi mà
Nếu bạn sửa ROW(1:1) thành ROWS($1:1) thì 2 cách trên cho kết quả y chang nhau

 

[LinDan]

Đã bao nhiêu lần tôi nói tránh dùng hàm ROW (nếu không cần thiết) rồi mà
Nếu bạn sửa ROW(1:1) thành ROWS($1:1) thì 2 cách trên cho kết quả y chang nhau

Đúng là vấn đề em đang thắc mắc đây rồi, mong thày giúp em chút nữa tại sao cái

INDIRECT("Sheet"&ROW($1:1)&"!A2:A100")

nó lại khác hoàn toàn so với cái

INDIRECT("Sheet"&ROWS($1:1)&"!A2:A100")

----
Cảm ơn thày em tìm ra vấn đề rồi, vì Row là hàm mảng, Rows không phải mảng

 

[ndu96081631]

----
Cảm ơn thày em tìm ra vấn đề rồi, vì Row là hàm mảng, Rows không phải mảng

Chính xác là vậy!
ROW là hàm mảng, giờ mảng trong mảng thì F9 nó chẳng tính được đâu (chỉ tự mình suy đoán kết quả)

 

[LinDan]

Nếu bạn sửa ROW(1:1) thành ROWS($1:1) thì 2 cách trên cho kết quả y chang nhau
ROW là hàm mảng, giờ mảng trong mảng thì F9 nó chẳng tính được đâu (chỉ tự mình suy đoán kết quả)

Cảm ơn thày rất nhiều, nếu không có thày giúp em nguyên nhân tại ở chỗ đó chắc chả bao giờ em hiểu được ra.

 

[LinDan]

Phân tích "Sheet"&{1;2;3}&"!A2:A100" sẽ là mảng 3 phần tử ---> kết quả: {"Sheet1!A2:A100"; "Sheet2!A2:A100"; "Sheet3!A2:A100"} --->Đây là 3 phần tử dạng chuổi
Khi lồng INDIRECT vào, các chuổi trên sẽ biến thành 1 tham chiếu thật sự <==> Array(Sheet1.Range("A2:A100"), Sheet2.Range("A2:A100"), Sheet3.Range("A2:A100"))
Tham chiếu vẫn cứ là tham chiếu thôi. Khi bạn bấm F9, các tham chiếu ấy đã bị biến đổi thành giá trị nên ta nhìn thấy có 300 phần tử nhưng bản chất vấn đề không phải vậy
Tóm lại: A1:A10 chỉ là 1 vùng, không thể xem nó là mảng 10 phần tử (khi bấm F9 nó đã bị biến đổi rồi)
-----------
(Tôi dùng VBA để minh họa cho dễ hiểu)

Em vẫn muốn tìm hiểu vấn đề này thêm chút nữa, phiền thày chỉ dạy giúp cho em rõ hơn về đoạn bôi xanh ở trên được không ah?

Em chưa hiểu sâu vấn đề lắm, nhưng qua đoạn trên em có cảm giác hình như Name và Mảng là hai khái niệm khác nhau (bởi từ trước đến nay đang em nghĩ chúng là một)?

 

[ndu96081631]

Em vẫn muốn tìm hiểu vấn đề này thêm chút nữa, phiền thày chỉ dạy giúp cho em rõ hơn về đoạn bôi xanh ở trên được không ah?

Em chưa hiểu sâu vấn đề lắm, nhưng qua đoạn trên em có cảm giác hình như Name và Mảng là hai khái niệm khác nhau (bởi từ trước đến nay đang em nghĩ chúng là một)?

Đúng ra bạn phải hỏi sự khác nhau giữa Vùng và Mảng
- Vùng ngoài việc có chứa giá trị ra thì còn có nhiều thuộc tính khác như: màu font, màu nền, size chữ... vân vân...
- Mảng chỉ chứa giá trị trong đó
Khi bạn bôi đen 1 đoạn địa chỉ vùng trên thanh formula rồi F9 thì Excel hiểu rằng bạn đang muốn biết trong vùng ấy có chứa giá trị nào ---> Nó sẽ quy đổi vùng ấy thành 1 mảng rồi hiển thị cho bạn thấy ---> Điều ấy không đồng nghĩa Vùng giống với mảng (chỉ khi bạn F9 để tính toán thì mới giống thôi)
Tất nhiên trong 1 công thức mảng có chứa địa chỉ 1 vùng thì khi tính toán, đương nhiên Excel sẽ tự quy đổi vùng ấy thành mảng
Ví dụ:
- Ta gõ công thức =$A$1:$A$10 + 1 rồi Ctrl + Shift + Enter, Excel sẽ hiểu bạn muốn lấy từng giá trị của vùng A1:A10 cộng với 1 ---> Từ bây giờ, kết quả của công thức ấy đã trở thành 1 mảng 10 phần tử
- Bạn đặt 1 name tên là Data, có công thức ở Refers To =$A$1:$A$10 thì đương nhiên cái Data ấy là 1 vùng (không phải mảng) ---> Chỉ khi tham gia tính toán, nó mới trở thành mảng
--------------------
Để biết 1 đoạn công thức nào đó là 1 Vùng hay 1 Mảng, bạn có thể kiểm tra rất dễ:
- Tạo 1 Name (tên tùy ý) với công thức ở Refers to bằng chính đoạn công thức ấy
- Đặt con trỏ chuột vào bất kỳ vị trí nào trong đoạn công thức trên (ở khung Refes to)... Nếu bạn nhìn thấy 1 đường đứt nét bao quanh 1 vùng trên bảng tính thì đấy chính là Vùng, ngược lại thì không phải
Ví dụ:
- Đặt name Data, có Refers To =$A$1:$A$10 ---> Đặt con trỏ vào giữa đoạn công thức trên, bạn sẽ thấy đường đứt nét bao quanh vùng A1:A10 ===> Suy ra Data là 1 Vùng
- Đặt name Data, có Refers To =$A$1:$A$10 +1---> Đặt con trỏ vào giữa đoạn công thức trên, bạn sẽ chẳng thấy đường đứt nét nào xuất hiện ===> Suy ra Data là 1 mảng
Xem hình minh họa:





































































Tất nhiên vẫn có công thức mà kết quả của nó không phải Vùng cũng không phải Mảng (là 1 số cụ thể chẳng hạn) ---> Điều này bạn tự biết chứ?