[Help] Hiển thị dữ liệu bằng với ngày gần nhất (ngày không liên tục) và tính Trung Bình theo điều kiện! (1 người xem)

forever_7610 · 9/6/18

Các sư phụ ơi giúp em bài này với ạ!
- Điều kiện:
+ Ở cột LOT CTROL N nếu C3 có số liệu thì bên cột B3 hiển thị kết quả = với kết quả của LOT CONTRL ngày gần nhất có số liệu .Nếu C3 không có số liệu thì hiển thị B3 là ô trắng.
+ Ở cột Mean D2= trung bình của 10 ngày có số liệu liên tiếp ,tính từ C2. Nếu LOT CONTROL N không đổi tức B3=B2(B3,B2<>"") thì D3=D2(tức là toàn bộ Mean của các giá trị có cùng LOT CONTROL sẽ giống nhau và = trung bình của 10 điểm đầu tiên từ lúc bắt đầu LOT CTROL ). Nếu từ ngày nào giá trị bên cột LOT CONTROL thay đổi ((Điền LOT # bằng tay khi thay LOT mới) thì giá trị Mean sẽ được xây dựng lại tính từ từ thời điểm đó đến 10 ngày có số liệu tiếp theo.

Em chúc các anh chị trong giaiphapexcel.com một ngày mới tốt lành và may mắn. Em xin chân thành cảm ơn ạ!

dazkangel · 9/6/18

forever_7610 đã viết:
Các sư phụ ơi giúp em bài này với ạ!
- Điều kiện:
+ Ở cột LOT CTROL N nếu C3 có số liệu thì bên cột B3 hiển thị kết quả = với kết quả của LOT CONTRL ngày gần nhất có số liệu .Nếu C3 không có số liệu thì hiển thị B3 là ô trắng.
+ Ở cột Mean D2= trung bình của 10 ngày có số liệu liên tiếp ,tính từ C2. Nếu LOT CONTROL N không đổi tức B3=B2(B3,B2<>"") thì D3=D2(tức là toàn bộ Mean của các giá trị có cùng LOT CONTROL sẽ giống nhau và = trung bình của 10 điểm đầu tiên từ lúc bắt đầu LOT CTROL ). Nếu từ ngày nào giá trị bên cột LOT CONTROL thay đổi ((Điền LOT # bằng tay khi thay LOT mới) thì giá trị Mean sẽ được xây dựng lại tính từ từ thời điểm đó đến 10 ngày có số liệu tiếp theo.
View attachment 197059
Em chúc các anh chị trong giaiphapexcel.com một ngày mới tốt lành và may mắn. Em xin chân thành cảm ơn ạ!

Như vầy đúng chưa ta:

Ba Tê · 9/6/18

forever_7610 đã viết:
Các sư phụ ơi giúp em bài này với ạ!
- Điều kiện:
+ Ở cột LOT CTROL N nếu C3 có số liệu thì bên cột B3 hiển thị kết quả = với kết quả của LOT CONTRL ngày gần nhất có số liệu .Nếu C3 không có số liệu thì hiển thị B3 là ô trắng.
+ Ở cột Mean D2= trung bình của 10 ngày có số liệu liên tiếp ,tính từ C2. Nếu LOT CONTROL N không đổi tức B3=B2(B3,B2<>"") thì D3=D2(tức là toàn bộ Mean của các giá trị có cùng LOT CONTROL sẽ giống nhau và = trung bình của 10 điểm đầu tiên từ lúc bắt đầu LOT CTROL ). Nếu từ ngày nào giá trị bên cột LOT CONTROL thay đổi ((Điền LOT # bằng tay khi thay LOT mới) thì giá trị Mean sẽ được xây dựng lại tính từ từ thời điểm đó đến 10 ngày có số liệu tiếp theo.
View attachment 197059
Em chúc các anh chị trong giaiphapexcel.com một ngày mới tốt lành và may mắn. Em xin chân thành cảm ơn ạ!

Có 60 dòng, bạn điền kết quả thủ công để mọi người hiểu bạn muốn thế nào (ghi chú cái nào là nhập bằng tay, cái nào là kết quả muốn có của công thức

HieuCD · 9/6/18

dazkangel đã viết:
Như vầy đúng chưa ta:

Bị #NA những dòng cuối. Dùng Match(...,...,1) loại bỏ #NA và tính lại số 10 theo số dòng còn lại khác ""

dazkangel · 9/6/18

HieuCD đã viết:
Bị #NA những dòng cuối. Dùng Match(...,...,1) loại bỏ #NA và tính lại số 10 theo số dòng còn lại khác ""

Làm thì cũng được nhưng trên yêu cầu của chủ bài rồi anh ^^

forever_7610 · 9/6/18

Em cảm ơn các sư phụ nhiều ạ. Hihi. Em đang phải ra ngoài chưa check đc bài. Lúc nào check gặp vẫn đề gì em xin đc các sư phụ chỉ giáo tiếp ạ. :yahoo:

forever_7610 · 11/6/18

dazkangel đã viết:
Làm thì cũng được nhưng trên yêu cầu của chủ bài rồi anh ^^

Các sư phụ ơi em muốn hỏi thêm điều này ạ.
- Nếu em thay hàm SUM(..)/min(..) bằng hàm average thì có được không ạ.
-ở cột mean e còn bị sót một điều kiện mà bài đầu em quên chưa đưa vào là khi ta thêm một cột tên LOT PT nữa và chỉ cần một trong hai cột LOT CONTROL N hoặc LOT PT thay đổi thì trung bình được tính lại chứ không phải như ban đầu chỉ riêng mình cột LOT CONTROL N
- Và ở cột Mean em muốn tất cả các giá thị đều được hiện liên tục. VD hiện tại D5=0 => E5=""..Nhưng em đang muốn là D5 không có dữ liệu thì E5 vẫn hiển thị giá trị bằng E4. (các giá trị E59,E60,... cũng hiển thị luôn ạ)

Hôm nay em ms check đc bài..Các sư phụ giúp em thêm mấy vấn đề này nữa nhé.
Em cảm ơn các sư phụ.

dazkangel · 11/6/18

forever_7610 đã viết:
Các sư phụ ơi em muốn hỏi thêm điều này ạ.
- Nếu em thay hàm SUM(..)/min(..) bằng hàm average thì có được không ạ.
-ở cột mean e còn bị sót một điều kiện mà bài đầu em quên chưa đưa vào là khi ta thêm một cột tên LOT PT nữa và chỉ cần một trong hai cột LOT CONTROL N hoặc LOT PT thay đổi thì trung bình được tính lại chứ không phải như ban đầu chỉ riêng mình cột LOT CONTROL N
- Và ở cột Mean em muốn tất cả các giá thị đều được hiện liên tục. VD hiện tại D5=0 => E5=""..Nhưng em đang muốn là D5 không có dữ liệu thì E5 vẫn hiển thị giá trị bằng E4. (các giá trị E59,E60,... cũng hiển thị luôn ạ)
View attachment 197167
Hôm nay em ms check đc bài..Các sư phụ giúp em thêm mấy vấn đề này nữa nhé.
Em cảm ơn các sư phụ.

À thay hàm AVERAGE được, thói quen F9 xem trước kết quả dẫn đến hơi ngố 1 tí.
Nhấn Ctrl+Shift+Enter khi nhập xong:

PHP:

=IF(B2="","",IFERROR(VLOOKUP(B2&"-"&C2,CHOOSE({1,2},B$1:B1&"-"&C$1:C1,E$1:E1),2,0),AVERAGE(OFFSET(D2,,,MATCH(10,INDEX(COUNTIF(OFFSET(D2,,,ROW($1:$50)),">0"),),1)))))

forever_7610 · 11/6/18

dazkangel đã viết:
À thay hàm AVERAGE được, thói quen F9 xem trước kết quả dẫn đến hơi ngố 1 tí.
Nhấn Ctrl+Shift+Enter khi nhập xong:

PHP:

=IF(B2="","",IFERROR(VLOOKUP(B2&"-"&C2,CHOOSE({1,2},B$1:B1&"-"&C$1:C1,E$1:E1),2,0),AVERAGE(OFFSET(D2,,,MATCH(10,INDEX(COUNTIF(OFFSET(D2,,,ROW($1:$50)),">0"),),1)))))

Hihi.Vâng. Sư phụ ơi ở bài #7 còn thiếu cái này nữa ! "- Và ở cột Mean em muốn tất cả các giá thị đều được hiện liên tục. VD hiện tại D5=0 => E5=""..Nhưng em đang muốn là D5 không có dữ liệu thì E5 vẫn hiển thị giá trị bằng E4. (các giá trị E59,E60,... cũng hiển thị luôn ạ) "

Bài đã được tự động gộp: 11/6/18

forever_7610 đã viết:
Hihi.Vâng. Sư phụ ơi ở bài #7 còn thiếu cái này nữa ! "- Và ở cột Mean em muốn tất cả các giá thị đều được hiện liên tục. VD hiện tại D5=0 => E5=""..Nhưng em đang muốn là D5 không có dữ liệu thì E5 vẫn hiển thị giá trị bằng E4. (các giá trị E59,E60,... cũng hiển thị luôn ạ) "

Em mày mò làm đc cái này rồi SP ạ.

Em cảm ơn SP nhiều nhé..

dazkangel · 11/6/18

forever_7610 đã viết:
Hihi.Vâng. Sư phụ ơi ở bài #7 còn thiếu cái này nữa ! "- Và ở cột Mean em muốn tất cả các giá thị đều được hiện liên tục. VD hiện tại D5=0 => E5=""..Nhưng em đang muốn là D5 không có dữ liệu thì E5 vẫn hiển thị giá trị bằng E4. (các giá trị E59,E60,... cũng hiển thị luôn ạ) "

Bài đã được tự động gộp: 11/6/18

Em mày mò làm đc cái này rồi SP ạ.
Em cảm ơn SP nhiều nhé..

Mình không phải SP(Support) của bạn, bạn làm ra thì mừng quá..

forever_7610 · 11/6/18

dazkangel đã viết:
Mình không phải SP(Support) của bạn, bạn làm ra thì mừng quá..

Em cảm ơn bác đã chỉ bảo em tận tình ạ..Em chúc bác và các anh em giaiphapexcel một ngày vui vẻ và may mắn nhé.

forever_7610 · 1/3/19

dazkangel đã viết:
Mình không phải SP(Support) của bạn, bạn làm ra thì mừng quá..

Em áp dụng bao lâu rồi mà bây giờ mới phát hiện ra lỗi này bác có thể nhòm lại giúp em đc không ạ.
VD. Ngày trước là khi cột LOT CTROL N hoặc LOT PT thay đổi thì Mean được tính là trung bình của cột PTs từ thời điểm LOT CTROL N hoặc LOT PT thay đổi đến 10 điểm tiếp theo..Nhưng em phát hiện ra là khi LOT CTROL N hoặc LOT PT thay đổi lần tiếp theo nhưng số điểm PTs của đợt trước chưa đủ 10 điểm thì bên cột Mean đã tính của đợt trước đó nó vẫn cập nhật theo giá trị PTs mới đến khi đủ 10 điểm..Như vậy nó sẽ làm sai giá trị Mean của LOT trước (vì nó tính cả các điểm của LOT mới..
Em muốn khi đã chuyển sang LOT (CONTROL N or PT) mới thì giá trị Mean của LOT cũ sẽ ko cập nhật nữa. (trường hợp chưa đủ 10 điểm)
Em mong bác giúp em lỗi này nhé!
Em cảm ơn Bác và chúc Bác và gia đình nhiều sức khỏe ạ!

dazkangel · 1/3/19

forever_7610 đã viết:
Em áp dụng bao lâu rồi mà bây giờ mới phát hiện ra lỗi này bác có thể nhòm lại giúp em đc không ạ.
VD. Ngày trước là khi cột LOT CTROL N hoặc LOT PT thay đổi thì Mean được tính là trung bình của cột PTs từ thời điểm LOT CTROL N hoặc LOT PT thay đổi đến 10 điểm tiếp theo..Nhưng em phát hiện ra là khi LOT CTROL N hoặc LOT PT thay đổi lần tiếp theo nhưng số điểm PTs của đợt trước chưa đủ 10 điểm thì bên cột Mean đã tính của đợt trước đó nó vẫn cập nhật theo giá trị PTs mới đến khi đủ 10 điểm..Như vậy nó sẽ làm sai giá trị Mean của LOT trước (vì nó tính cả các điểm của LOT mới..
Em muốn khi đã chuyển sang LOT (CONTROL N or PT) mới thì giá trị Mean của LOT cũ sẽ ko cập nhật nữa. (trường hợp chưa đủ 10 điểm)
Em mong bác giúp em lỗi này nhé!
Em cảm ơn Bác và chúc Bác và gia đình nhiều sức khỏe ạ!
View attachment 213009

Tức là như sau: Chỉ lấy số PTs tối đa 10 điểm nhưng nếu 1 trong 2 Lot thay đổi thì chỉ lấy đến đó đúng không ?
Giả sử tuy thay đổi nhưng số Lot bên dưới có còn quay lại số Lot cũ không ?

forever_7610 · 1/3/19

dazkangel đã viết:
Tức là như sau: Chỉ lấy số PTs tối đa 10 điểm nhưng nếu 1 trong 2 Lot thay đổi thì chỉ lấy đến đó đúng không ?
Giả sử tuy thay đổi nhưng số Lot bên dưới có còn quay lại số Lot cũ không ?

Đúng rồi bác ạ..lấy tối đa 10 điểm..
LOT mới có thể là LOT mới tinh hoặc có thể trùng lại với một LOT đã sử dụng rồi bác ạ.

dazkangel · 1/3/19

forever_7610 đã viết:
Đúng rồi bác ạ..lấy tối đa 10 điểm..
LOT mới có thể là LOT mới tinh hoặc có thể trùng lại với một LOT đã sử dụng rồi bác ạ.

Bạn thử công thức sau, giờ hơi bận nên chưa làm rõ được, nhấn Ctrl+Shift+Enter:

Mã:

=IF(B2="",E1,IFERROR(VLOOKUP(B2&"-"&C2,IF({1,0},B$1:B1&"-"&C$1:C1,E$1:E1),2,0),AVERAGE(OFFSET(D2,,,MIN(MATCH(1,(B2&"-"&C2<>B2:B$60&"-"&C2:C$60)*(B2:B$60&C2:C$60<>""),0),MATCH(10,COUNTIF(OFFSET(D2,,,ROW($1:$60)),">0"),1))))))

forever_7610 · 1/3/19

dazkangel đã viết:
Bạn thử công thức sau, giờ hơi bận nên chưa làm rõ được, nhấn Ctrl+Shift+Enter:

Mã:

=IF(B2="",E1,IFERROR(VLOOKUP(B2&"-"&C2,IF({1,0},B$1:B1&"-"&C$1:C1,E$1:E1),2,0),AVERAGE(OFFSET(D2,,,MIN(MATCH(1,(B2&"-"&C2<>B2:B$60&"-"&C2:C$60)*(B2:B$60&C2:C$60<>""),0),MATCH(10,COUNTIF(OFFSET(D2,,,ROW($1:$60)),">0"),1))))))

Dạ ko đc bác ạ. Em làm nó toàn ra thê này!
oke Bác lúc nào bác rảnh bác giúp em cũng được ạ!

dazkangel · 2/3/19

Sau khi sửa điều kiện, bài toán khá phức tạp phải được mốc vị trí mỗi lần thay đổi haizzz
Thử:

Mã:

=IF(B2="",N(E1),IFERROR(LOOKUP(2,1/(B$1:B1=B2)/(C$1:C1=C2),E$1:E1),AVERAGE(OFFSET(D2,,,MIN(IFERROR(MATCH(1,(B2&"-"&C2<>B2:B$60&"-"&C2:C$60)*(B2:B$60&C2:C$60<>"")-1,0),COUNTIF(D2:D$60,"<>=")),MATCH(10,COUNTIF(OFFSET(D2,,,ROW($1:$60)),">0"),1))))))

forever_7610 · 2/3/19

dazkangel đã viết:
Sau khi sửa điều kiện, bài toán khá phức tạp phải được mốc vị trí mỗi lần thay đổi haizzz
Thử:

Mã:

=IF(B2="",N(E1),IFERROR(LOOKUP(2,1/(B$1:B1=B2)/(C$1:C1=C2),E$1:E1),AVERAGE(OFFSET(D2,,,MIN(IFERROR(MATCH(1,(B2&"-"&C2<>B2:B$60&"-"&C2:C$60)*(B2:B$60&C2:C$60<>"")-1,0),COUNTIF(D2:D$60,"<>=")),MATCH(10,COUNTIF(OFFSET(D2,,,ROW($1:$60)),">0"),1))))))

Khi chưa đủ 10 điểm Mean nó vẫn cập nhật các điểm của LOT sau bác ạ.

Vâng. Cái này phức tạp mà bác. Tại mỗi đợt LOT hóa chất có hạn dài ngắn khác nhau tùy theo nhà sản xuất giao nên ko cố định được bác ạ.. ko XD đc công thức chắc em phải tính kiểu nông dân bằng tay mất.

dazkangel · 2/3/19

forever_7610 đã viết:
Khi chưa đủ 10 điểm Mean nó vẫn cập nhật các điểm của LOT sau bác ạ.
Vâng. Cái này phức tạp mà bác. Tại mỗi đợt LOT hóa chất có hạn dài ngắn khác nhau tùy theo nhà sản xuất giao nên ko cố định được bác ạ.. ko XD đc công thức chắc em phải tính kiểu nông dân bằng tay mất.

Là cập nhật các điểm sau cùng số Lot thì vẫn tính trung bình tối đa 10 điểm à?
Đang hơi bận nên copy nhầm công thức:

Mã:

=IF(B2="",N(E1),IFERROR(LOOKUP(2,1/(B$1:B1=B2)/(C$1:C1=C2),E$1:E1),AVERAGE(OFFSET(D2,,,MIN(IFERROR(MATCH(1,(B2&"-"&C2<>B2:B$60&"-"&C2:C$60)*(B2:B$60&C2:C$60<>""),0)-1,COUNTIF(D2:D$60,"<>=")),MATCH(10,COUNTIF(OFFSET(D2,,,ROW($1:$60)),">0"),1))))))

Công thức như yêu cầu trên bạn kiểm tra lại:

Mã:

=IF(B2="",N(E1),IFERROR(LOOKUP(2,1/(B$1:B1=B2)/(C$1:C1=C2),E$1:E1),SUM(IFERROR(MOD(AGGREGATE(15,6,(ROW(1:$60)*1000+D2:D$60)/(B2:B$60=B2)/(C2:C$60=C2),ROW($1:$10)),1000),))/MIN(COUNTIFS(B2:B$60,B2,C2:C$60,C2),10)))

forever_7610 · 2/3/19

dazkangel đã viết:
Là cập nhật các điểm sau cùng số Lot thì vẫn tính trung bình tối đa 10 điểm à?
Đang hơi bận nên copy nhầm công thức:

Mã:

=IF(B2="",N(E1),IFERROR(LOOKUP(2,1/(B$1:B1=B2)/(C$1:C1=C2),E$1:E1),AVERAGE(OFFSET(D2,,,MIN(IFERROR(MATCH(1,(B2&"-"&C2<>B2:B$60&"-"&C2:C$60)*(B2:B$60&C2:C$60<>""),0)-1,COUNTIF(D2:D$60,"<>=")),MATCH(10,COUNTIF(OFFSET(D2,,,ROW($1:$60)),">0"),1))))))

Công thức như yêu cầu trên bạn kiểm tra lại:

Mã:

=IF(B2="",N(E1),IFERROR(LOOKUP(2,1/(B$1:B1=B2)/(C$1:C1=C2),E$1:E1),SUM(IFERROR(MOD(AGGREGATE(15,6,(ROW(1:$60)*1000+D2:D$60)/(B2:B$60=B2)/(C2:C$60=C2),ROW($1:$10)),1000),))/MIN(COUNTIFS(B2:B$60,B2,C2:C$60,C2),10)))

Công thức Trung Bình chạy mượt rồi bác ạ. Em cảm ơn Bác nhé!

[Help] Hiển thị dữ liệu bằng với ngày gần nhất (ngày không liên tục) và tính Trung Bình theo điều kiện! (1 người xem)

Người dùng đang xem chủ đề này

Thành viên mới

File đính kèm

<New Horizons>

File đính kèm

Cạo Rồi Khỏi Gội

Chuyên gia GPE

<New Horizons>

File đính kèm

Thành viên mới

Thành viên mới

File đính kèm

<New Horizons>

Thành viên mới

<New Horizons>

Thành viên mới

Thành viên mới

<New Horizons>

Thành viên mới

<New Horizons>

Thành viên mới

<New Horizons>

Thành viên mới

<New Horizons>

Thành viên mới

Đếm ngược thời gian