Tìm vị trí thỏa mãn điều kiện (1 người xem)

[nphunghung]

Chào các bác, em có câu hỏi như thế này ạ:
Giả sử em có một hàng dữ liệu như hình đính kèm, các số là mình nhập vào (Em để Format là accounting nên số 0 nó chuyển thành dấu -)

Em cần tính tổng dãy số đó theo yêu cầu như sau:
Tính tổng của dãy số, nếu tổng <=15 thì tiếp tục cộng, đến khi tổng >15 thì thực hiện trừ từ ô mà tổng đạt giá trị là 15.

Nói như thế này cho dễ hiểu:
Em có bảng trong hình đính kèm. Ô A2 là để tính tổng các ô từ B2 đến hết hàng. Ta có SUM(B2:F2)=14 thỏa mãn <=15. Từ H2 trở đi, nếu tiếp tục cộng thì kết quả sẽ >15, do đó, ta lại thực hiện phép trừ từ ô H2 trở đi. Vấn đề là làm sao xác định được ô H2 hay ô nào đó thỏa mãn được tổng =15 để chuyển sang thực hiện phép trừ ạ.

Câu hỏi hơi khó trình bày, có hỏi ngu thì các bác cũng đừng gạch đá.
Em xin cảm ơn!

 

[VMH0307]

Chào các bác, em có câu hỏi như thế này ạ:
Giả sử em có một hàng dữ liệu như hình đính kèm, các số là mình nhập vào (Em để Format là accounting nên số 0 nó chuyển thành dấu -)

Em cần tính tổng dãy số đó theo yêu cầu như sau:
Tính tổng của dãy số, nếu tổng <=15 thì tiếp tục cộng, đến khi tổng >15 thì thực hiện trừ từ ô mà tổng đạt giá trị là 15.

Nói như thế này cho dễ hiểu:
Em có bảng trong hình đính kèm. Ô A2 là để tính tổng các ô từ B2 đến hết hàng. Ta có SUM(B2:F2)=14 thỏa mãn <=15. Từ H2 trở đi, nếu tiếp tục cộng thì kết quả sẽ >15, do đó, ta lại thực hiện phép trừ từ ô H2 trở đi. Vấn đề là làm sao xác định được ô H2 hay ô nào đó thỏa mãn được tổng =15 để chuyển sang thực hiện phép trừ ạ.

Câu hỏi hơi khó trình bày, có hỏi ngu thì các bác cũng đừng gạch đá.
Em xin cảm ơn!

Bạn thử công thức sau tại ô A2:

=SUMPRODUCT(IF(SUBTOTAL(109,OFFSET($B$2,,,,COLUMN(B2:L2)-1))<=15,1,-1),B2:L2)

 

[nphunghung]

Bạn thử công thức sau tại ô A2:

=SUMPRODUCT(IF(SUBTOTAL(109,OFFSET($B$2,,,,COLUMN(B2:L2)-1))<=15,1,-1),B2:L2)

Tình hình là ko được bác à. Em Evaluate để kiểm tra step by step thì được

=SUMPRODUCT(1,B2:L2)

cho kết quả #VALUE!

Với lại tại sao cái COLUMN(B2:L2) lại cho kết quả là 2 ạ?

 

[VMH0307]

Tình hình là ko được bác à.

cho kết quả #VALUE!

Do đây là công thức mảng nên thay vì kết thúc bằng enter thì dùng Ctrl + Shift+ Enter

 

[leonguyenz]

Tình hình là ko được bác à. Em Evaluate để kiểm tra step by step thì được

=SUMPRODUCT(1,B2:L2)

cho kết quả #VALUE!
Với lại tại sao cái COLUMN(B2:L2) lại cho kết quả là 2 ạ?

Đây là hàm mảng, bạn phải kết thúc công thức với tổ hợp phím Ctrl+Shift+Enter chứ không phải Enter thông thường.
Nhưng bạn diễn đạt hơi khó hiểu nên tôi chưa chắc công thức trên đã đúng, nếu gõ H2 là 2 thì kết quả công thức là 5. Nếu đúng là 1 (tổng đến H2 là 16, vậy số trừ ra là 1) có phải vậy không?
Nếu lấy vị trí của ô cần thay đổi (tới hạn của tổng 15), bạn tham khảo công thức, gõ Enter bình thường:

=ADDRESS(2,1+LOOKUP(15,SUBTOTAL(109,OFFSET($B$2,,,,COLUMN(B2:L2)-1)),COLUMN(B2:L2)),4)

 

[nphunghung]

Cảm ơn hai bác, trước mắt em dùng hàm address của bác Mr Bum xác định được ô vị trí rồi ạ. Để em nghiên cứu tiếp, có gì em hỏi lại các bác ạ.

Ah, cho em hỏi tí nữa là Ví dụ ở ô A2 mình có giá trị là "H2". Giờ mình muốn dùng giá trị ở ô A2 để đưa vào hàm có được ko ạ? Ví dụ hàm đếm:
Em muốn đếm từ H2 đến AZ2 có bao nhiêu ô rỗng. Nhưng em muốn lấy H2 là giá trị từ ô A2 chứ ko phải do mình nhập vào thì làm thế nào ạ?

 

[leonguyenz]

Cảm ơn hai bác, trước mắt em dùng hàm address của bác Mr Bum xác định được ô vị trí rồi ạ. Để em nghiên cứu tiếp, có gì em hỏi lại các bác ạ.

Ah, cho em hỏi tí nữa là Ví dụ ở ô A2 mình có giá trị là "H2". Giờ mình muốn dùng giá trị ở ô A2 để đưa vào hàm có được ko ạ? Ví dụ hàm đếm:
Em muốn đếm từ H2 đến AZ2 có bao nhiêu ô rỗng. Nhưng em muốn lấy H2 là giá trị từ ô A2 chứ ko phải do mình nhập vào thì làm thế nào ạ?

Công thức:

=COUNTIF(INDIRECT(ADDRESS(2,1+LOOKUP(15,SUBTOTAL(109,OFFSET($B$2,,,,COLUMN(B2:L2)-1)),COLUMN(B2:L2)),4)&":AZ2"),"")