Tính tổng các ô có chữ và số!

[trandaithang]

Xin chào mọi người!

Mình là thành viên mới của diễn đàn.Mình có một vấn đề mong mọi người giúp đỡ mình.Đó là:
Mình muốn tính Tổng của 1 dãy,mà dãy đó có chữ và số.Mình đã sử dụng hàm tách số và tính tổng của dãy đó nhưng công thức lại không phù hợp nên không cho ra kết quả.Cụ thể là như thế này:

Có 1 dãy C7:AG7,trong dãy đó gồm các ô ví dụ như:

C7 là 0:20m (0:20 là 20 phút tính bằng thời gian)
D7 là 0:40m (0:40 là 40 phút tính bằng thời gian)
Những ô từ D7 đến AG7 không có dữ liệu.

Mình tính tổng dãy C7:AG7 thì tính như sau: =sum(left(C7,4)+left(D7,4)+...+left(AG7,4)) =>cho ra kết quả là #value! Bời vì công thức đó chỉ hiểu là chỉ có những ô trong dãy C7:AG7 có dữ liệu mới cho ra kết quả đúng.Còn ô bất kỳ nào trong dãy đó không có dữ liệu thì cho ra kết quả là #value!

Mình có đính kèm file,mong mọi người giúp mình tính được tổng của 1 dãy mà trong ô có chữ và số như mình lấy ví dụ trên.Và trong dãy đó có ô không có dữ liệu.

Công thức mình tính trong file đính kềm ở mục:đi muộn

Cám ơn mọi người!

 

[khunglongcodon]

Tại ô AP7, bạn dùng công thức này xem sau nhé:
{=SUM(IF(ISERROR(HOUR(LEFT(C7:AG7,4))*60+MINUTE(LEFT(C7:AG7,4))),0,HOUR(LEFT(C7:AG7,4))*60+MINUTE(LEFT(C7:AG7,4))))}
(đây là công thức mảng nên bạn kết thúc bằng tổ hợp phím CTRL + SHIFT + ENTER nhé).
Chúc bạn thành công.

 

[gaunuicr]

Xin chào mọi người!

Có 1 dãy C7:AG7,trong dãy đó gồm các ô ví dụ như:

C7 là 0:20m (0:20 là 20 phút tính bằng thời gian)
D7 là 0:40m (0:40 là 40 phút tính bằng thời gian)
Những ô từ D7 đến AG7 không có dữ liệu.

Mình tính tổng dãy C7:AG7 thì tính như sau: =sum(left(C7,4)+left(D7,4)+...+left(AG7,4)) =>cho ra kết quả là #value! Bời vì công thức đó chỉ hiểu là chỉ có những ô trong dãy C7:AG7 có dữ liệu mới cho ra kết quả đúng.Còn ô bất kỳ nào trong dãy đó không có dữ liệu thì cho ra kết quả là #value!

Chỉnh sửa lại CT của bạn một chút cũng được: Thay left(C7,4)... bởi LEFT(REPLACE(C7,1,1,"0"),4) ...

 

[huuthang_bd]

Chỉnh sửa lại CT của bạn một chút cũng được: Thay left(C7,4)... bởi LEFT(REPLACE(C7,1,1,"0"),4) ...

Làm vậy đâu có được đâu bạn, làm như bạn thì chỉ cộng được số phút còn giờ thì bằng 0 hết.
Chủ topic dùng công thức này thử xem.

=SUMPRODUCT(--LEFT(C7:AG7&"0:00",4))

 

[trandaithang]

Mình cám ơn ba bạn đã giúp đỡ mình,đã thành công.Chúc mọi người có dịp Valentine thật vui vẻ!

 

[trandaithang]

Xin hỗ trợ tiếp với!!!

Mình đã tính thành công công thức ở mục " đi muộn" bằng công thức:=SUMPRODUCT(--LEFT(C7:AG7&"0:00",4))

Nhưng khi mình tính công thức ở mục "về sớm" thì có 2 vấn đề:

+ thứ nhất là: thay đổi lại công thức thành:=SUMPRODUCT(--RIGHT(C7:AG7&"0:00",4)) thì công thức không đúng.Bởi vì:công thức đó không lấy dữ liệu ở bên phải để tính,nên ko phù hợp.

+thứ hai là: Khi ta nhập dữ liệu,ví dụ trong 1 ô: là s0:40 hoặc 0:40m (nghĩa là sớm 40 phút hoặc muộn 40 phút) thì ở ô đi muộn công thức lại bị value!.Có nghĩa là 1 ô đánh dữ liệu s0:40 hoặc 0:40m thì công thức ở ô đi sớm và về muộn sẽ không hiểu.Hay là công thức của 2 ô đi sớm và về muộn bị đá nhau.

Mong các bạn giúp mình tìm được công thức hợp lý ở ô "đi sớm" và giải thích họ mình về hàm Sumproduct,vì mình tìm hiểu hàm này mà vẫn chưa hiểu được công dụng của hàm đó sử dụng trong điều kiện như thế nào và kết hợp với hàm left,right ra sao.

Rất mong sự giúp đỡ của các bạn.Mính cám ơn nhiều.

 

[huuthang_bd]

Mình đã tính thành công công thức ở mục " đi muộn" bằng công thức:=SUMPRODUCT(--LEFT(C7:AG7&"0:00",4))

Nhưng khi mình tính công thức ở mục "về sớm" thì có 2 vấn đề:

+ thứ nhất là: thay đổi lại công thức thành:=SUMPRODUCT(--RIGHT(C7:AG7&"0:00",4)) thì công thức không đúng.Bởi vì:công thức đó không lấy dữ liệu ở bên phải để tính,nên ko phù hợp.

+thứ hai là: Khi ta nhập dữ liệu,ví dụ trong 1 ô: là s0:40 hoặc 0:40m (nghĩa là sớm 40 phút hoặc muộn 40 phút) thì ở ô đi muộn công thức lại bị value!.Có nghĩa là 1 ô đánh dữ liệu s0:40 hoặc 0:40m thì công thức ở ô đi sớm và về muộn sẽ không hiểu.Hay là công thức của 2 ô đi sớm và về muộn bị đá nhau.

Mong các bạn giúp mình tìm được công thức hợp lý ở ô "đi sớm" và giải thích họ mình về hàm Sumproduct,vì mình tìm hiểu hàm này mà vẫn chưa hiểu được công dụng của hàm đó sử dụng trong điều kiện như thế nào và kết hợp với hàm left,right ra sao.

Rất mong sự giúp đỡ của các bạn.Mính cám ơn nhiều.

Theo tôi cách nhập liệu của bạn chưa ổn. Dữ liệu cần phải đồng nhất. Ví dụ: M00:10, S00:20.
Còn một vấn đề nữa là nếu trong một ngày, một người vừa đi muộn vừa về sớm thì bạn nhập liệu như thế nào.

 

[trandaithang]

Cám ơn bạn đã giúp mình.

Ý của mình là: 1 ô sử dụng chung cho 2 công thức.Ví dụ ô D7, thì thì sử dụng chung cho mục tính "đi sớm" và "về muộn" vì vậy mình mới nhập dữ liệu là:

+00:10M : có nghĩa là đi muộn 10 phút
+S00:20 : có nghĩa là đi sớm 20 phút.

Vì vậy mà sử dụng 2 công thức left và right cùng 1 ô dữ liệu thì ko được.Ví dụ như nếu ô D7 là:00:10M thì công thức ở "đi muộn" sẽ đúng. Nhưng ở "về sớm" thì công thức sẽ là VALUE!

Vậy bạn có cách nào để thay đổi lại công thức cho đúng ko.Hoặc nếu nhập dữ liệu (M00:10, S00:20) như bạn thì có thể dùng công thức nào đúng cho cả về : "đi muộn" và "về sớm"

Rất mong được sự giúp đỡ của các bạn.Xin cảm ơn.

 

[huuthang_bd]

Cám ơn bạn đã giúp mình.

Ý của mình là: 1 ô sử dụng chung cho 2 công thức.Ví dụ ô D7, thì thì sử dụng chung cho mục tính "đi sớm" và "về muộn" vì vậy mình mới nhập dữ liệu là:

+00:10M : có nghĩa là đi muộn 10 phút
+S00:20 : có nghĩa là đi sớm 20 phút.

Vì vậy mà sử dụng 2 công thức left và right cùng 1 ô dữ liệu thì ko được.Ví dụ như nếu ô D7 là:00:10M thì công thức ở "đi muộn" sẽ đúng. Nhưng ở "về sớm" thì công thức sẽ là VALUE!

Vậy bạn có cách nào để thay đổi lại công thức cho đúng ko.Hoặc nếu nhập dữ liệu (M00:10, S00:20) như bạn thì có thể dùng công thức nào đúng cho cả về : "đi muộn" và "về sớm"

Rất mong được sự giúp đỡ của các bạn.Xin cảm ơn.

Tôi nhắc lại: Còn một vấn đề nữa là nếu trong một ngày, một người vừa đi muộn vừa về sớm thì bạn nhập liệu như thế nào.
Bạn nên gửi lên 1 file khác có đầy đủ các kiểu dữ liệu mà bạn sẽ nhập. Như vậy sẽ đỡ mất công sửa đi sửa lại.

 

[trandaithang]

Cám ơn Bạn đã góp ý.Mình đã gửi 1 file EXCEL để bạn có thể hiểu rõ hơn.

Rất mong sự giúp đỡ của bạn.

 

[ndu96081631]

Cám ơn Bạn đã góp ý.Mình đã gửi 1 file EXCEL để bạn có thể hiểu rõ hơn.

Rất mong sự giúp đỡ của bạn.

Bạn nhập liệu kỳ cục quá đi. Cái thì nhập là S0:20 (tại cell I7)... cái thì lại nhập là 0:20S (tại cell I9) ---> Phải thống nhất 1 kiểu chứ bạn

 

[solomon2211]

Bạn thử công thức như đính kèm nhé.

 

[trandaithang]

Cám ơn các bạn solomon2211 và mọi người nhé.Mình đã nhập thành công rồi.

 

[trandaithang]

Xin chào các bạn.Topic của mình hỏi về vấn đề tách số và chữ để tính tổng các số có dữ liệu cùng với chữ.
Như bạn ndu96081631
nói là trong 1 ô có cả "đi sớm" và "về muộn" thì tính tổng các ô đó như thế nào thì mình chưa tính được trường hợp đó.Rất mong sự giúp đỡ của mọi người.
Mình thấy cách nhập dữ liệu của mình cũng chưa phù hợp.Bởi vì chữ và số phải để cùng phía với nhau như: S0:20 hay M0:20 thì hợp lý hơn.Nhưng khi mình sửa lại công thức: =SUM(--IF(ISNUMBER(SUBSTITUTE(C19:AG19,"M","")*1),SUBSTITUTE(C19:AG19,"M","")*1,0)) (công thức đó là dùng cho cách nhập là 0:20M và S0:20) Khi mình sửa lại công thức trên với cách nhập S0:20 hay M0:20( chữ và số cùng 1 phía) thì công thức lại ko đúng.
Các bạn có cách nào để sửa lại cho đúng ko.VÀ với trường hợp cả "đi sớm" và "về muộn" cùng 1 ô thì tính tổng như thế nào.Mình đã gửi 1 bản excel để các bạn hiểu rõ hơn.
Xin cám ơn các bạn!

 

[trandaithang]

Các bạn ơi! Mình giúp mình với nhé.

 

[gaunuicr]

Các bạn ơi! Mình giúp mình với nhé.

Nếu tôi là bạn, tôi sẽ dùng công thức của bạn solomon2211 và cấu trúc lại bảng tính bằng cách thêm vào 2 Sheet dữ liệu phụ (trong file đính kèm tôi đặt tên 2 sheet này là M và S - Tôi tạo 2 sheet này chỉ mất khoảng 6 phút). Mục đích thêm 2 sheet này là để giải quyết vấn đề trong 1 cell bạn nhập cả M và S (vd: 0:40M S0:10, 0:40M 0:10S, M0:40 S0:10, M0:40 0:10M hoặc thay đổi vị trí của M và S trong các cách nhập này) mà vẫn tính toán được. Bạn tham khảo File đính kèm xem thử có giúp gì được cho bạn không nhé!
Chúc bạn thành công!