Sử dụng Offset để tạo mảng trong Name

[VMH0307]

Kính gửi: các Thầy, các Bác!
Xin mọi người giải đáp giúp em vấn đề này:
trên bảng tính em có công thức sau:

=OFFSET($A$1,ROW(1:8),)

ấn F9 cho ra: {0;0;0;0;0;0;0;0} (do A2:A9 rỗng)
Nhưng sau khi đặt công thức kia thành một Name: ví dụ

Mang=OFFSET($A$1,ROW(1:8),)

thì kết quả không còn là mảng nữa, chỉ có một phần tử.
Em muốn mọi người giải đáp giúp em tại sao lại có sự khác biệt như vậy.
Và nếu muốn tạo mảng bằng Offset kiểu này thì làm như thế nào trong Name.
Cám ơn mọi người đã quan tâm!

 

[nghiaphuc]

Kính gửi: các Thầy, các Bác!
Xin mọi người giải đáp giúp em vấn đề này:
trên bảng tính em có công thức sau:

=OFFSET($A$1,ROW(1:8),)

ấn F9 cho ra: {0;0;0;0;0;0;0;0} (do A2:A9 rỗng)
Nhưng sau khi đặt công thức kia thành một Name: ví dụ

Mang=OFFSET($A$1,ROW(1:8),)

thì kết quả không còn là mảng nữa, chỉ có một phần tử.
Cám ơn mọi người đã quan tâm!

Bạn nhập dữ liệu vào cột A, sau đó chọn vùng B1:B10, nhập =Mang và nhấn Ctrl+Enter xem nó ra cái gì.

 

[VMH0307]

Bạn nhập dữ liệu vào cột A, sau đó chọn vùng B1:B10, nhập =Mang và nhấn Ctrl+Enter xem nó ra cái gì.

Vâng, cái này em biết.
Em muốn hỏi tại sao lại có sự khác biệt đó; tại sao bản thân Name Mang không cho kết quả mảng giống như khi gõ công thức trực tiếp trên bảng tính?

 

[dhn46]

Vâng, cái này em biết.
Em muốn hỏi tại sao lại có sự khác biệt đó; tại sao bản thân Name Mang không cho kết quả mảng giống như khi gõ công thức trực tiếp trên bảng tính?

Theo mình vấn đề này do hàm ROW
Hàm ROW trả về vị trí cell đầu tiên trong vùng => trong Name sẽ thực hiện tính toán và đưa ra kết quả cuối cùng, trong khi đó nếu gõ trên bảng tính thì ROW sẽ phải liệt kê các dòng rồi mới đưa ra vị trí đầu tiên.
Qua Name các giá trị tính toán trong hàm đã được thực hiện do đó nó bỏ qua quá trình trung gian!

 

[VMH0307]

Theo mình vấn đề này do hàm ROW
Hàm ROW trả về vị trí cell đầu tiên trong vùng => trong Name sẽ thực hiện tính toán và đưa ra kết quả cuối cùng, trong khi đó nếu gõ trên bảng tính thì ROW sẽ phải liệt kê các dòng rồi mới đưa ra vị trí đầu tiên.
Qua Name các giá trị tính toán trong hàm đã được thực hiện do đó nó bỏ qua quá trình trung gian!

Mình thử nghiệm như thế này

mang=OFFSET($A$1,ROW(1:8),,8,)

thì mới OK. Phải chăng thực tế nó vẫn tạo mảng nhưng do để chế độ mặc định Height =1 nên lại trả về 1 phần tử trong mảng đó.
Có lẽ trên bảng tính chỉ show cho ta xem thôi, chứ cũng chẳng tác động được gì từ mảng đó nếu không thiết lập Height =8

 

[ndu96081631]

Mình thử nghiệm như thế này

mang=OFFSET($A$1,ROW(1:8),,8,)

thì mới OK. Phải chăng thực tế nó vẫn tạo mảng nhưng do để chế độ mặc định Height =1 nên lại trả về 1 phần tử trong mảng đó.
Có lẽ trên bảng tính chỉ show cho ta xem thôi, chứ cũng chẳng tác động được gì từ mảng đó nếu không thiết lập Height =8

Theo suy đoán của tôi thì các hàm OFFSET, INDEX, INDIRECT đều trả về kết quả là Range (không phải Array)... Thành ra OFFSET($A$1,ROW(1:8),) nếu nằm trong name nó cũng chỉ trả về kết quả là 1 cell (bạn không nói mở rộng mấy dòng, đương nhiên Excel mặc định là 1 dòng)
Muốn trở thành 1 mảng thật sự, ta kết hợp với SUBTOTAL
Ví dụ:

Mang =SUBTOTAL(109,OFFSET($A$1,ROW($1:$8),))

Yêu cầu cột A phải là Number (nếu là text thì... ẹc... ẹc... chưa nghĩ ra)

 

[VMH0307]

Muốn trở thành 1 mảng thật sự, ta kết hợp với SUBTOTAL

Hàm Subtotal của thầy hay quá.
Cái nút F9 em thấy nó cũng lạ nha:
thử với công thức sau:

=ROWS(OFFSET($A$1,ROW($1:$8),))

nếu ấn F9 thì cho 8
nếu Enter trên bảng tính hiện 1.
Em nghĩ Có lẽ sự khác biệt này nguyên nhân từ thằng F9 chăng, chứ thực tế bảng tính và Name vẫn giống nhau.

 

[Hoàng Trọng Nghĩa]

Theo suy đoán của tôi thì các hàm OFFSET, INDEX, INDIRECT đều trả về kết quả là Range (không phải Array)... Thành ra OFFSET($A$1,ROW(1:8),) nếu nằm trong name nó cũng chỉ trả về kết quả là 1 cell (bạn không nói mở rộng mấy dòng, đương nhiên Excel mặc định là 1 dòng)
Muốn trở thành 1 mảng thật sự, ta kết hợp với SUBTOTAL
Ví dụ:

Mang =SUBTOTAL(109,OFFSET($A$1,ROW($1:$8),))

Yêu cầu cột A phải là Number (nếu là text thì... ẹc... ẹc... chưa nghĩ ra)

Cho em hỏi, không biết có đúng không, khi đã là Name thì nó mang tính của Mảng, bởi công thức em viết là một mảng (Ctrl+Shift+Enter), nhưng chép công thức đó vô Name thì không cần phải có các dấu {}.

 

[ndu96081631]

Em nghĩ Có lẽ sự khác biệt này nguyên nhân từ thằng F9 chăng, chứ thực tế bảng tính và Name vẫn giống nhau.

Cái vụ F9 ấy chẳng nói lên được điều gì cả

Cho em hỏi, không biết có đúng không, khi đã là Name thì nó mang tính của Mảng, bởi công thức em viết là một mảng (Ctrl+Shift+Enter), nhưng chép công thức đó vô Name thì không cần phải có các dấu {}.

Xem bài viết này (viết về Range và Array)
http://www.giaiphapexcel.com/forum/...00-phần-tử-(hay3-phần-tử)&p=468256#post468256

 

[ndu96081631]

Thêm 1 chiêu khác áp dụng cho cả number và text

Mang =IF(T(OFFSET($A$1,ROW($1:$8),))="",N(OFFSET($A$1,ROW($1:$8),)),T(OFFSET($A$1,ROW($1:$8),)))

 

[Hoàng Trọng Nghĩa]

Thêm 1 chiêu khác áp dụng cho cả number và text

Mang =IF(T(OFFSET($A$1,ROW($1:$8),))="",N(OFFSET($A$1,ROW($1:$8),)),T(OFFSET($A$1,ROW($1:$8),)))

Với những công thức "zắc zối" như vậy, em mới nghiệm ra rằng, sử dụng công thức khó hơn cả sử dụng VBA!