Động rộng của mảng khi dùng Offset ? (3 người xem)

[vumian]

Dear all,

Làm ơn xem giúp mình cái file này, mình suy nghĩ mãi mà chưa có ra, hichic
Trường hợp trong file, dộ rộng mảng >=3 là ra kết quả đúng, nếu cột A có dữ liệu thì dộ rộng mãng này sẽ bị thay dổi

Thanks,
vumian

 

[Cao Mạnh Sơn]

Bạn xem file này và theo ý tưởng của bạn phát triển thêm nhé

 

[salam]

Bạn thay độ rộng (5) bằng ct:
=MATCH(9.99999999999999E+307,Sheet1!$E$8:$IV$8)

 

[vumian]

@CaoManhSơn & @Salam

Mình đã xem qua cách giải quyết của 2 bác, cách giải quyết gần đúng rồi ,nhưng dữ liệu không nhất thiết bắt đầu bằng B6 hay B8, nếu B6 hay B8 mà rỗng thì côgn thức là 0 , hic
Vậy thì mình có giải pháp nào khác ?

Ô kết quả có thể đặt ở D7, hay D6, nhưng theo mình nghĩ là làm sao mình biết được vị trí bắt đầu và kết thúc trong vùng mảng ? Khi ấy chắc giải quyết được vấn đề

Thanks

 

[ndu96081631]

Cũng khó hình dung quá! Bạn đang muốn giãi quyết chuyện gì? Có thể nói rõ hơn 1 chút không?
Bài toán chỉ có thể giãi quyết được nếu có dử liệu cụ thể và nhưng dấu hiệu nhận biết!
Bạn tham khảo về công thức độc đáo tại đây xem có giúp gì được không:
http://www.giaiphapexcel.com/forum/showthread.php?t=7669
Nếu cần phải xác định mãng nằm trên 1 dòng nào đó xuất phát từ điểm nào và kết thức tại điểm nào thì tôi nghĩ tốt nhất cột kết quả nên "né" ra chổ khác. Chứ chung đụng thế thì dể gây nhầm lẩn lắm!

 

[vumian]

Dear ndu,

- Mình đã xem các công thức độc đáo rồi, nhưng mình chưa có giải pháp
- Cái ô kết quả màu hồng là 0 là sai, phải là 2.1 mới đúng
- Cái cell kết quả có thể nằm trên, sao cho có thể fill nó xuống hoặc lên, vì cái dữ liệu bên phải là dạng như vậy,
- Mình up lại file để bác dễ hình dung nha, Cám ơn nhiều

 

[boyxin]

Dear ndu,

- Mình đã xem các công thức độc đáo rồi, nhưng mình chưa có giải pháp
- Cái ô kết quả màu hồng là 0 là sai, phải là 2.1 mới đúng
- Cái cell kết quả có thể nằm trên, mình up lại file để bác dễ hình dung nha, Cám ơn nhiều

xem lại kết quả đã đúng chưa?

 

[vumian]

Boyxin ơi,
- Kết quả thì đúng, nhưng kéo lên hoặc kéo xuống thì kết quả lại sai,
- Vì là dạng dữ liệu cột mà, nên phải fill cái ô kết quả xuống

Mong xem giúp lần nữa, Cám ơn,

 

[ndu96081631]

Boyxin ơi,
- Kết quả thì đúng, nhưng kéo lên hoặc kéo xuống thì kết quả lại sai,
- Vì là dạng dữ liệu cột mà, nên phải fill cái ô kết quả xuống

Mong xem giúp lần nữa, Cám ơn,

Khóa cell H2 lại, có nghĩa là sửa H2 thành $H$2

 

[boyxin]

Boyxin ơi,
- Kết quả thì đúng, nhưng kéo lên hoặc kéo xuống thì kết quả lại sai,
- Vì là dạng dữ liệu cột mà, nên phải fill cái ô kết quả xuống

Mong xem giúp lần nữa, Cám ơn,

kéo lên xuông là thế nào? kéo fill lên cho mản ở trên à?
kết quả vẫn để ở dòng giữa (lấy dòng dưới trù dòng trên của dòng kết quả) thì sửa lại công thức trong ô kết quả

=SUMPRODUCT(OFFSET(F11,,,1,$H$2)-OFFSET(F11,-2,,1,$H$2))

khi để H2 kéo fill lên xuông thì giá trị này thây đổi dẫn đến kết quả sai thôi mà
---------------
oài
ndu96081631

10th Excel Member

nhanh tay ghê, trước mình tíc tắc

 

[vumian]

Ôi, 2 bác thật tuyệt, Cái vụ lấy địa chỉ tuyệt đối mà lại không để ý, bị sì trét quá rồi

Nhưng mà sao mình gộp công thức lại nó báo lỗi nhỉ ?, không muốn dùng ô phụ

Cám ơn 2 bác nhiều,

 

[ndu96081631]

Ôi, 2 bác thật tuyệt, Cái vụ lấy địa chỉ tuyệt đối mà lại không để ý, bị sì trét quá rồi

Nhưng mà sao mình gộp công thức lại nó báo lỗi nhỉ ?, không muốn dùng ô phụ

Cám ơn 2 bác nhiều,

Bạn gộp thế nào? Và báo lổi thế nào?
Có thể đưa file lổi lên được không?

 

[vumian]

NDu ơi

Mình chép ct ở name er, rùi cho vào H2, chỉnh sửa địa chỉ thành tương đối, hôg hiểu sao nữa

Thanks,

 

[ndu96081631]

NDu ơi

Mình chép ct ở name er, rùi cho vào H2, chỉnh sửa địa chỉ thành tương đối, hôg hiểu sao nữa

Thanks,

Bạn chỉ cần sửa lại:

MATCH(9.99999999999999E+307,Sheet1!$8:$8)-COLUMN(Sheet1!$E$8)

thành

MATCH(9.99999999999999E+307,$8:$8)-5

Là hết lổi ngay!

 

[ndu96081631]

Nói thêm 1 chút về lổi này:
Vì hàm COLUMN trả về giá trị mãng, cho dù bạn Enter hay Ctrl + Shift + Enter gì thì nó vẩn cứ là MÃNG
Thử nghiệm bằng cách gỏ hàm COLUMN() vào 1 cell nào đó, quét công thức trên thanh Formula rồi F9, bạn sẽ thấy xuất hiện dấu {}
Ví dụ hàm COLUMN() nằm tại cột E sẽ trả về giá trị ={5} chứ không phải =5
Vấn đề nằm ở chổ đó!
Tương tự thế với hàm ROW()
-----------------
Từ đây ta có 1 bài toán đặt ra là:
Làm cách nào để biến {5} thành 5 ,tức biến mãng 1 phần tử thành giá trị đơn bình thường
Vẫn chưa nghĩ ra được giãi pháp! Các bạn nghiên cứu thử xem!
???

 

[vumian]

Mình thêm nhiều dữ liệu vào, Hông hiểu nó vẫn lỗi , kỳ thiệt nha
Ah, Nếu dữ liệu ở dòng B khôg có, thì cái hàm MATCH sẽ trả về con số > thứ tự cột ô đầu tiên

Cụ thể là D13 chưa chính xác ??

 

[boyxin]

Mình thêm nhiều dữ liệu vào, Hông hiểu nó vẫn lỗi , kỳ thiệt nha
Ah, Nếu dữ liệu ở dòng B khôg có, thì cái hàm MATCH sẽ trả về con số > thứ tự cột ô đầu tiên

Cụ thể là D13 chưa chính xác ??

Kiểm tra lại xem còn sai nữa không nhé (chắc là hết sai rồi)​

 

[vumian]

Đúng là hết sai, nhưng còn 1 trường hợp mà dữ liệu dòng A và B đều rỗng , hihi, thì nó sẽ ra #REF đó, dù sao cũng có hướng cho mình đi rồi, cám ơn 2 bác nhiều

 

[boyxin]

Đúng là hết sai, nhưng còn 1 trường hợp mà dữ liệu dòng A và B đều rỗng , hihi, thì nó sẽ ra #REF đó, dù sao cũng có hướng cho mình đi rồi, cám ơn 2 bác nhiều

xóa hết name thừa (chỉ để lại 3 name er, er_st, er_sbt)
sửa công thức tính như sau

D7=IF(MOD(ROWS($7:7),3)=1,IF(er=0,"",SUMPRODUCT(OFFSET(F8,,,1,er)-OFFSET(F8,-2,,1,er))),"")

rồi kéo fill xuống
bi giờ chắc là không còn #REF

 

[ndu96081631]

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