Làm thế nào để tính trung bình số liệu 5 phút 1 lần (1 người xem)

[tamngoan]

Xin chào các huynh đệ, tỉ muội!

Mình có file số liệu excel gồm 3 cột, cột ngày tháng năm giờ phút, cột nồng độ CH4 và cột nồng độ NMHCs (xem file đính kèm)
View attachment Tính TB 5 phút.xlsx


Mình muốn xử lý số liệu để thay đổi hiển thị cột ngày tháng thời gian theo kiểu các con số nồng độ từ 0h 0 phút đến 00h 05 phút thành 1 số (lấy trung bình) và hiển thị là 00:05; tương tự, các dữ liệu từ 00:06 đến 00:10 được quy thành 1 giá trị trung bình và hiển thị là 00:10....

Vấn đề là chuỗi số liệu của mình rất dài, có cách nào thao tác nhanh gọn không ạ?

Rất mong được các bạn trợ giúp!!!

Xin chân thành cảm ơn ạ!

 

[nhapmon]

Xin chào các huynh đệ, tỉ muội!

Mình có file số liệu excel gồm 3 cột, cột ngày tháng năm giờ phút, cột nồng độ CH4 và cột nồng độ NMHCs (xem file đính kèm)
View attachment 106276


Mình muốn xử lý số liệu để thay đổi hiển thị cột ngày tháng thời gian theo kiểu các con số nồng độ từ 0h 0 phút đến 00h 05 phút thành 1 số (lấy trung bình) và hiển thị là 00:05; tương tự, các dữ liệu từ 00:06 đến 00:10 được quy thành 1 giá trị trung bình và hiển thị là 00:10....

Vấn đề là chuỗi số liệu của mình rất dài, có cách nào thao tác nhanh gọn không ạ?

Rất mong được các bạn trợ giúp!!!

Xin chân thành cảm ơn ạ!

hóa phân tích hả bạn...............
món này ngày xưa mình học ở đại học...............nhưng từ ngay ra trường thất nghiệp quá chuyển qua món khác rùi.......ihixhix.

bạn cho kết quả mong muốn vào cột D xem, đọc ko hình dung ra được............

 

[leonguyenz]

Ví dụ D2 bạn gõ ngày bắt đầu: 01/04/2013
Công thức đặt tại D4:

=ROUNDUP((A4-D$2)*1440/5,0)*5/1440

Bạn định dạng "hh:mm".

 

[tamngoan]

bạn có thể nói rõ hơn được không leonguyenz ơi, mình ko làm được

 

[tamngoan]

hóa phân tích hả bạn...............
món này ngày xưa mình học ở đại học...............nhưng từ ngay ra trường thất nghiệp quá chuyển qua món khác rùi.......ihixhix.

bạn cho kết quả mong muốn vào cột D xem, đọc ko hình dung ra được............

đây nhé, xem qua giúp mình nhé

 

[leonguyenz]

bạn có thể nói rõ hơn được không leonguyenz ơi, mình ko làm được

Ví dụ của bạn tính hinh như chưa đúng, 00:05:16, 00:05:36 và 00:05:56 nó phải thuộc nhóm 00:10 mới đúng.
Bạn xem trong file.
Cách khác:
Đặt công thức trực tiếp:

=SUMPRODUCT(((MINUTE($A$4:$A$21)+SECOND($A$4:$A$21)/60)>ROW(A1)*5-5)*((MINUTE($A$4:$A$21)+SECOND($A$4:$A$21)/60)<=ROW(A1)*5)*($B$4:$B$21))/SUMPRODUCT(((MINUTE($A$4:$A$21)+SECOND($A$4:$A$21)/60)>ROW(A1)*5-5)*((MINUTE($A$4:$A$21)+SECOND($A$4:$A$21)/60)<=ROW(A1)*5))

 

[tamngoan]

Ví dụ của bạn tính hinh như chưa đúng, 00:05:16, 00:05:36 và 00:05:56 nó phải thuộc nhóm 00:10 mới đúng.

Đồng ý với bạn về góp ý này, mình có hơi nhầm tí!

Cách khác:
Đặt công thức trực tiếp:

=SUMPRODUCT(((MINUTE($A$4:$A$21)+SECOND($A$4:$A$21)/60)>ROW(A1)*5-5)*((MINUTE($A$4:$A$21)+SECOND($A$4:$A$21)/60)<=ROW(A1)*5)*($B$4:$B$21))/SUMPRODUCT(((MINUTE($A$4:$A$21)+SECOND($A$4:$A$21)/60)>ROW(A1)*5-5)*((MINUTE($A$4:$A$21)+SECOND($A$4:$A$21)/60)<=ROW(A1)*5))

Mình chưa hiểu lắm CT, bạn có thể nói rõ được ko?

Mình tham khảo cách bạn đặt CT trong file bạn đính kèm
CT nhóm số liệu giờ phút lại thành giờ phút - trong đó phút là bội số của 5 bạn đặt như sau:
=ROUNDUP((A4-D$2)*1440/5;0)*5/1440

bạn có thể cho mình biết ý nghĩa của số 1440 ko?

 

[tamngoan]

Tình hình là mình đã hiểu cách làm (một cách máy móc), tuy nhiên trong quá trình xử lý số liệu, một số cột lại báo lỗi dạng #DIV/0.

Mình không biết xử trí thế nào với lỗi đó, xin nhờ các bạn giúp dùm!!!

 

[leonguyenz]

Đồng ý với bạn về góp ý này, mình có hơi nhầm tí!



Mình chưa hiểu lắm CT, bạn có thể nói rõ được ko?

Mình tham khảo cách bạn đặt CT trong file bạn đính kèm
CT nhóm số liệu giờ phút lại thành giờ phút - trong đó phút là bội số của 5 bạn đặt như sau:
=ROUNDUP((A4-D$2)*1440/5;0)*5/1440

bạn có thể cho mình biết ý nghĩa của số 1440 ko?

Về công thức =ROUNDUP((A4-D$2)*1440/5;0)*5/1440, là lấy phần dư của 1 ngày (giờ, phút, giây), nhân với 1440 (một ngày 1440 phút), tức là tính phần dư trong ngày có bao nhiêu phút, làm tròn bội số của 5 rồi nhân lại với 5, cuối cùng là chia cho 1440 để quy số đó ra ngày và định dạng "hh:mm".
Tuy nhiên kết quả có chênh lệch phút 35 là một con số rất nhỏ, ví dụ 12:35:00 AM mình tính là 35 phút, cũng là =35/1440, nhưng so sánh thì kết quả FALSE. Mình chưa thử các kết quả khác, những kết quả trong ví dụ còn lại đều đúng.
Có 2 cách:
- Một là bạn sửa cột E, =row(a1)*5/1440 rồi kéo xuống, công thức F2:

=IF(COUNTIF($D$2:$D$171,E2)=0,0,AVERAGEIF($D$2:$D$171,$E2,B$2:B$171))

- Hai là sử dụng công thức trực tiếp, bẫy lỗi:

=IF(SUMPRODUCT(((MINUTE($A$2:$A$221)+SECOND($A$2:$A$221)/60)>ROW(A1)*5-5)*((MINUTE($A$2:$A$221)+SECOND($A$2:$A$221)/60)<=ROW(A1)*5))=0,0,SUMPRODUCT(((MINUTE($A$2:$A$221)+SECOND($A$2:$A$221)/60)>ROW(A1)*5-5)*((MINUTE($A$2:$A$221)+SECOND($A$2:$A$221)/60)<=ROW(A1)*5)*($B$2:$B$221))/SUMPRODUCT(((MINUTE($A$2:$A$221)+SECOND($A$2:$A$221)/60)>ROW(A1)*5-5)*((MINUTE($A$2:$A$221)+SECOND($A$2:$A$221)/60)<=ROW(A1)*5)))

 

[tamngoan]

Bạn leonguyenz ơi, trong chuỗi số liệu dài của mình, có cả những giá trị âm, mình muốn khi tính trung bình, ko tính đến những ô có giá trị âm đó, thì phải làm sao ạ?

 

[leonguyenz]

hic, mình không biết excel bị lỗi gì, khi nhập CT theo cách 1 mà bạn chỉ ở trên, vào ô đầu tiên thì okie, kéo dãn để tính tự động các ô tiếp theo thì nó copy lại y chang số ở ô đầu tiên ( là sao nhỉ?

Bạn vào Formulas > Calculation Options xem hiện tại đang check Automatic hay Manual? Bạn chọn lại Automatic.

 

[tamngoan]

Bạn vào Formulas > Calculation Options xem hiện tại đang check Automatic hay Manual? Bạn chọn lại Automatic.

Bạn leonguyenz ơi, trong chuỗi số liệu dài của mình, có những ô mang giá trị = 0 hoặc nhỏ hơn 0 mình đã loại bỏ và để ô trống, mình muốn khi tính trung bình thì loại bỏ những ô trống đó thì phải làm sao ạ?​

 

[leonguyenz]

Bạn leonguyenz ơi, trong chuỗi số liệu dài của mình, có những ô mang giá trị = 0 hoặc nhỏ hơn 0 mình đã loại bỏ và để ô trống, mình muốn khi tính trung bình thì loại bỏ những ô trống đó thì phải làm sao ạ?​

Bạn sử dụng hàm AVERAGEIF để tính trung bình có điều kiện, bạn tham khảo hàm này hoặc gửi file lên.

Hàm AVERAGEIF()

Trả về trung bình cộng (số học) của tất cả các ô được chọn thỏa mãn một điều kiện cho trước.
Cú pháp: = AVERAGEIF(range, criteria, average_range)
range : Là một hoặc nhiều ô cần tính trung bình, có thể bao gồm các con số, các tên vùng, các mảng hoặc các tham chiếu đến các giá trị...
criteria : Là điều kiện dưới dạng một số, một biểu thức, địa chỉ ô hoặc chuỗi, để qui định việc tính trung bình cho những ô nào...
average_range : Là tập hợp các ô thật sự được tính trung bình. Nếu bỏ trống thì Excel dùng range để tính.
Lưu ý:

• Các ô trong range nếu có chứa những giá trị luận lý (TRUE hoặc FALSE) thì sẽ được bỏ qua.
• Những ô rỗng trong average_range cũng sẽ được bỏ qua.
• Nếu range rỗng hoặc có chứa dữ liệu text, AVERAGEIF sẽ báo lỗi #DIV/0!
• Nếu có một ô nào trong criteria rỗng, AVERAGEIF sẽ xem như nó bằng 0.
• Nếu không có ô nào trong range thỏa mãn điều kiệu của criteria, AVERAGEIF sẽ báo lỗi #DIV/0!
• Bạn có thể các ký tự đại diện như ?, * trong criteria (dấu ? thay cho một ký tự nào đó, và dấu * thay cho một chuỗi nào đó). Khi điều kiện trong criteria là chính các dấu ? hoặc *, thì bạn gõ thêm dấu ~ trước nó.
• average_range không nhất thiết phải có cùng kích thước với range, mà các ô thực sự được tính trung bình sẽ dùng ô trên cùng bên trái của average_range làm ô bắt đầu, và bao gồm thêm những ô tương ứng với kích thước của range.

 

[tamngoan]

File ví dụ như sau:

Mình muốn tính trung bình mỗi 15 phút ra 1 số liệu, bỏ qua những ô trống (tô vàng) thì phải đặt CT chính xác như thế nào?

Thanks bạn, chúc luôn vui khỏe!

 

[leonguyenz]

File ví dụ như sau:

Mình muốn tính trung bình mỗi 15 phút ra 1 số liệu, bỏ qua những ô trống (tô vàng) thì phải đặt CT chính xác như thế nào?

Thanks bạn, chúc luôn vui khỏe!

Đặt công thức tại E6:

=IFERROR(AVERAGEIFS(B$6:B$141,$A$6:$A$141,">"&(ROW($A1)*15-15)/1440,$A$6:$A$141,"<="&ROW($A1)*15/1440,B$6:B$141,"<>"),0)

Kéo qua cột F cho ngày 3.