Động rộng của mảng khi dùng Offset ?

[boyxin]

Thế còn câu hỏi tại bài #15 thì sao nhỉ?
Tôi đưa lên thêm 1 file giã lập như thế này nhé:
-Dử liệu là number nằm ở A1:A10
-Dùng hàm =SUMPRODUCT(OFFSET($A$1,,,ROW($B$5),)) trả về lổi #VALUE
-Nếu dùng SUM(OFFSET($A$1,,,ROW($B$5),))thì giá trị trả về = 15 (chính xác)
Không hiểu
???
Thêm 1 chuyện hơi lạ:
-Quét đoạn công thức OFFSET($A$1,,,ROW($B$5),) trên thanh Formula rồi bấm F9 thì ta thấy giá trị tính toán được =1 (kỳ lạ)
-Nếu thay đoạn trên thành OFFSET($A$1,,,5,) rồi cũng quét nó trên thanh Formula và F9 thì giá trị tính toán được = {1;2;3;4;5} (cái này chính xác)
???
Các bạn xem file thử! Thật không hiểu nổi

Với topic này Lổi kỳ lạ với hàm OFFSET thì chắc tạm ổn rồi nhỉ​

 

[ndu96081631]

Với topic này Lổi kỳ lạ với hàm OFFSET thì chắc tạm ổn rồi nhỉ​

Chưa ổn đâu, vì SUM không bị lổi tại sao SUMPRODUCT lại lổi

 

[boyxin]

Chưa ổn đâu, vì SUM không bị lổi tại sao SUMPRODUCT lại lổi

Lỗi thế nào, tui lồng bên ngoài là hàm Sum và SumProduct có thấy lỗi thế nào đâu

 

[vumian]

Thế còn câu hỏi tại bài #15 thì sao nhỉ?
Tôi đưa lên thêm 1 file giã lập như thế này nhé:
-Dử liệu là number nằm ở A1:A10
-Dùng hàm =SUMPRODUCT(OFFSET($A$1,,,ROW($B$5),)) trả về lổi #VALUE
-Nếu dùng SUM(OFFSET($A$1,,,ROW($B$5),))thì giá trị trả về = 15 (chính xác)
Không hiểu
???
Thêm 1 chuyện hơi lạ:
-Quét đoạn công thức OFFSET($A$1,,,ROW($B$5),) trên thanh Formula rồi bấm F9 thì ta thấy giá trị tính toán được =1 (kỳ lạ)
-Nếu thay đoạn trên thành OFFSET($A$1,,,5,) rồi cũng quét nó trên thanh Formula và F9 thì giá trị tính toán được = {1;2;3;4;5} (cái này chính xác)
???
Các bạn xem file thử! Thật không hiểu nổi

NDU ơi,

Theo mình nghĩ, các đối số của SUMPRODUCT nên phải là mảng, sở dĩ SUMPRODUCT(OFFSET($A$1,,,ROW($B$5),)) của bác lỗi có lẽ là row(B5) chỉ đơn thuần không phải là dối số mảng, vì thế
- Muốn hết lỗi thì công thức phải là :

SUMPRODUCT(OFFSET($A$1,,,ROWS(5:5),))

Công thức trên chỉ đúng về mặt công thức, nhưng về đáp số thì sai, vì rows() trả về số hàng của 1 mảng, còn rows(5:5) thì đương nhiên là 1 hàng. Vì thế kết quả lại sai
- muốn ra đáp số thì công thức phải thế này

=SUMPRODUCT(OFFSET($A$1,,,ROWS(A1:A5),))

Mình lý luận thế này, bác có cao kiến gì thêm không

Regards,
vumian

 

[ndu96081631]

Lỗi thế nào, tui lồng bên ngoài là hàm Sum và SumProduct có thấy lỗi thế nào đâu

File này không bị lổi là điều đương nhiên. Bạn xem file trong bài #20 ấy!
Bạn vumian đưa ra giãi pháp là đúng rồi, nhưng ý tôi không phải là tìm giãi pháp mà là tìm nguyên nhân!
Để ý cùng 1 biểu thức như nhau, nhưng khi lồng hàm SUM và thì bình thường trong khi với SUMPRODUCT thì lại báo lổi!
Cái tôi muốn các bạn quan tâm là ở chổ này đây!
Còn nữa nha: Tôi đang dùng ROW chứ không phải ROWS

 

[boyxin]

xem file trong bài #20 ấy!
ý tôi không phải là tìm giãi pháp mà là tìm nguyên nhân!
Để ý cùng 1 biểu thức như nhau, nhưng khi lồng hàm SUM và thì bình thường trong khi với SUMPRODUCT thì lại báo lổi!
Cái tôi muốn các bạn quan tâm là ở chổ này đây!
Còn nữa nha: Tôi đang dùng ROW chứ không phải ROWS

Tài liệu tham khảo

1. SUM và SUMPRODUCT khác nhau điểm nào trong trường hợp này?
2. http://www.webketoan.vn/forum/showthread.php?t=11790#Scene 1
3. http://webketoan.com/forum/showthread.php?p=32110#Scene 1
4. http://www.google.com.vn/search?hl=vi&q=SUM+và+SUMPRODUCT&btnG=Tìm+với+Google&meta=lr=lang_vi
5. ..........&.........

Em đọc mãi mà chưa tìm ra nguyên nhân của căn bệnh ở bài #20. ai biết thì chia sẻ để anh em học hỏi nha

 

[ndu96081631]

Hai điểm mấu chốt mà tôi quan tâm là:
-Tại sao SUMPRODUCT bị lổi
-Quét chọn khối công thức OFFSET và F9 thì kết quả thu được hết sức kỳ lạ, thế mà SUM nó vẫn tính ra

 

[vumian]

Bác Ndu, Boyxin ơi,


Mình muón cout có bao nhiêu phần tử số thôi,
File mình bị lỗi mà cũng hông hiểu luôn, bác xem giúp

Thanks,

 

[ndu96081631]

Nếu bạn muốn đếm dòng 1 có bao nhiêu phần tử số thì làm vậy đâu có được!
COUNT($A$1&":"&H11) và COUNTA($A$1&":"&H11) chẳng có ý nghĩa gì cả. Ít nhất cũng phải:
=COUNT(INDIRECT("A1:"&$H$11))

 

[vumian]

Nếu thì mình đầu có fill down được, chắc phải dùng offset ?

 

[boyxin]

Bác Ndu, Boyxin ơi,


Mình muón cout có bao nhiêu phần tử số thôi,
File mình bị lỗi mà cũng hông hiểu luôn, bác xem giúp

Thanks,

Công thức tại A2

A2 =OFFSET($A1,,,1,COUNTA($A$1&":"&H11))
COUNTA($A$1&":"&H11) sai cấu trúc lệnh COUNTA

Sửa thành

A2 =OFFSET(Sheet1!$A$1,,,1,COUNTA(Sheet1!$A$1:$H$11))

Rồi chép thành công thức của 1 name trong Insert\Name\Define... (VD: Name1)
Công thức tại A3

A3 =OFFSET($A1,,,1,COUNT($A$1&":"&H11))
COUNT($A$1&":"&H11) sai cấu trúc lệnh COUNT

Sửa thành

A3 =OFFSET(Sheet1!$A$1,,,1,COUNT(Sheet1!$A$1:$H$11))

Rồi chép thành công thức của 1 name trong Insert\Name\Define...(VD: Name2)

KIỂM TRA KẾT QUẢ LÀ THẤY LIỀN​

 

[ndu96081631]

Không hiểu:
COUNT(Sheet1!$A$1:$H$11)
COUNTA(Sheet1!$A$1:$H$11)
là COUNT cái gì vậy ta?

 

[boyxin]

Không hiểu:
COUNT(Sheet1!$A$1:$H$11)
COUNTA(Sheet1!$A$1:$H$11)
là COUNT cái gì vậy ta?

He he he, tại A12 của Sheet1 gõ các công thức =COUNT(Sheet1!$A$1:$H$11)hoặc COUNTA(Sheet1!$A$1:$H$11) là ra kết quả <=> đoán ra câu trả lời liền

 

[vumian]

Ủa, hình như BoyXin lạc đề thì phải, mình đang bàn nhau về đếm 1 hàng thôi mà

 

[ndu96081631]

COUNT và COUNTA thì đương nhiên hiểu. Nhưng tôi không hiểu ý nghĩa thật sự của hàm này trong cả công thức!
Ra kết quả =10 ===> vẩn không hiểu là cái gì
???
Vì thực chất ngay chổ:

A3 =OFFSET(Sheet1!$A$1,,,1,COUNT(Sheet1!$A$1:$H$11))

Đâu có gõ công thức này được!
Xin boyxin giãi thích thêm!
Còn bạn vumian: Có thể nói thêm về mục đích của bạn không? Bạn cần đếm các phần tử số trong 1 hàng uh? thế sao tại cell A2 bạn không gỏ: =COUNT(1:1) cho gọn? Sao phải OFFSET rồi tùm lum các thứ cả

 

[boyxin]

COUNT và COUNTA thì đương nhiên hiểu. Nhưng tôi không hiểu ý nghĩa thật sự của hàm này trong cả công thức!
Ra kết quả =10 ===> vẩn không hiểu là cái gì
???
Vì thực chất ngay chổ:

Đâu có gõ công thức này được!
Xin boyxin giãi thích thêm!
Còn bạn vumian: Có thể nói thêm về mục đích của bạn không? Bạn cần đếm các phần tử số trong 1 hàng uh? thế sao tại cell A2 bạn không gỏ: =COUNT(1:1) cho gọn? Sao phải OFFSET rồi tùm lum các thứ cả

1. Thì tại bạn vumian thắc mắc không biết tại sao bị lỗi, tui chỉ cho cách nhìn thấy lỗi, vậy thôi
2. A2, A3 nằm trong vùng xác định bởi vùng đếm của COUNT và COUNTA để xác định làm 1 tham số của hàm OFFSET <=> LỖI
3. Mình đoán ý đồ của bạn vumian định dùng hàm OFFSET động (thay đổi độ rộng vùng khi kéo fill ...)

 

[vumian]

Ý, Mình muốn đếm ngay từ trên đầu cell trờ về sau thôi, cho nên mói cái H11,

ví dụ số bắt đầu từ A3 - B3 - B3 .... .....
ngay a4 là kết quả của dòng 3

 

[boyxin]

Ủa, hình như BoyXin lạc đề thì phải, mình đang bàn nhau về đếm 1 hàng thôi mà

hic hic hic
đếm 1 hàng mà lại count(A1&":"&H11); count(A1&":"&H11)
đáng ra phải là H1 chứ nhẩy
với lại cú pháp câu lệnh sai thì lỗi

 

[vumian]

Đâu có, H11 là lấy ô cuối cùng của dòng mà, tính offset tới đó,

 

[ndu96081631]

Ý, Mình muốn đếm ngay từ trên đầu cell trờ về sau thôi, cho nên mói cái H11,

ví dụ số bắt đầu từ A3 - B3 - B3 .... .....
ngay a4 là kết quả của dòng 3

Nhưng nếu đếm 1 hàng thì tại sao phải cần OFFSET?
Vì đàng nào khi bạn gỏ dử liệu ở đâu đó thì công thức tại H11 cũng sẽ lấy địa chỉ cell cuối cùng có dử liệu dạng số. Vậy cần gì OFFSET?
Ý tôi muốn nói tại sao tại A2 bạn không gỏ =COUNT(1:1) rồi fill xuống
Có gì khác nhau đâu? Bạn thử đưa 1 ví dụ về sự khác nhau giữa việc dùng hàm COUNT(1:1) của tôi và OFFSET của bạn xem nào?