Em xin giúp công thức đếm dữ liệu cả số và chữ (1 người xem)

[trandaithangckoto]

Em đếm B5:BK5. trong B5:BK5 có 3 dữ liệu M1:30 , M2:00, M2:45. Em đã dùng công thức =countif(B5:BK5),Mid(B5:BK5,1,1)) nhưng ko được và em dùng cả hàm LEft () để tách chữ ra rồi đếm nó nhưng công thức thì quá dài vì chứa tận 30 ô cel. Các bác có công thức nào thì giúp em với.

Vấn đề thứ 2 của em là em muốn tính tổng của các dữ liệu này (1:30 + 2:00+ 2:45) thì em phải tách chữ ra. E có dùng công thức kết hợp =SUM(--IF(ISNUMBER(SUBSTITUTE(...Nhưng vì công thức này phải chuyển sang mảng, ấn Shilft + Ctrl+Enter thì mới dùng được và khá bất tiện. Các bác có công thức nào phù hợp thì giúp em với ạ.

Em có file đính kèm ở dươi.Em cám ơn mọi người ạ.

 

[befaint]

Công thức đếm:

=COUNTIF($B5:$BK5,"M*")

Công thức tính tổng: Không hiểu sao cho vào Index(), Transpose vẫn không ra kết quả (F9 thì được).

eke_rula làm mau đi...

 

[trandaithangckoto]

Cám ơn bác. Nếu có cùng dãy ô cel đó có các dữ liệu M, M1:20,M2:00,M2:45 thì đếm như thế nào vậy bác. Em dùng công thức:
=COUNTIF($B5:$BK5,"M*")+ countif($B5:$BK5,"M" thì cho ra kết quả là 5. Nhưng đúng thì kết quả là 4 bác ạ

 

[befaint]

Cám ơn bác. Nếu có cùng dãy ô cel đó có các dữ liệu M, M1:20,M2:00,M2:45 thì đếm như thế nào vậy bác. Em dùng công thức:
=COUNTIF($B5:$BK5,"M*")+ countif($B5:$BK5,"M" thì cho ra kết quả là 5. Nhưng đúng thì kết quả là 4 bác ạ

Chỗ màu đỏ = 4 (không cần đoạn sau)

 

[eke_rula]

Em đếm B5:BK5. trong B5:BK5 có 3 dữ liệu M1:30 , M2:00, M2:45. Em đã dùng công thức =countif(B5:BK5),Mid(B5:BK5,1,1)) nhưng ko được và em dùng cả hàm LEft () để tách chữ ra rồi đếm nó nhưng công thức thì quá dài vì chứa tận 30 ô cel. Các bác có công thức nào thì giúp em với.

Vấn đề thứ 2 của em là em muốn tính tổng của các dữ liệu này (1:30 + 2:00+ 2:45) thì em phải tách chữ ra. E có dùng công thức kết hợp =SUM(--IF(ISNUMBER(SUBSTITUTE(...Nhưng vì công thức này phải chuyển sang mảng, ấn Shilft + Ctrl+Enter thì mới dùng được và khá bất tiện. Các bác có công thức nào phù hợp thì giúp em với ạ.

Em có file đính kèm ở dươi.Em cám ơn mọi người ạ.

@ befaint Có lẽ do dùng IF hoặc IFERROR nên không dùng INDEX để loại bỏ CTRL+SHIFT+ENTER được!!!
@trandaithangckoto bạn dùng thử CT này xem:

=SUMPRODUCT(AGGREGATE(15,6,--SUBSTITUTE(B5:BK5,"M",""),ROW(INDIRECT("1:"&COUNTIF(B5:BK5,"M*")))))

Mà CTRL+SHIFT+ENTER sao bất tiện,bản chất nó là mảng thì nên chấp nhận chứ, CT thực ra đơn giản vì bất tiện nên phải đưa ra công thức dài phức tạp hơn, bình thường chỉ cần vầy là đủ:

=SUM(IFERROR(--SUBSTITUTE(B5:BK5,"M",""),))

CTRL+SHIFT+ENTER

 

[quocgiacan]

@ befaint Có lẽ do dùng IF hoặc IFERROR nên không dùng INDEX để loại bỏ CTRL+SHIFT+ENTER được!!!
........................
CTRL+SHIFT+ENTER

INDEX(...,) chỉ có hiệu lực với những mảng nguyên, tức mảng được tạo từ 1 vùng trọn hay từ sự tính toán mà không bị phân khúc.
Ví dụ: E1= "1" hay "2", nếu E1 = 1 thì hãy lấy vùng (A1:A100>=15)*C1:C100, nếu E1= 2 thì lấy B1:B100*D1100, sau đó công lại kết quả.
Thường gặp bài toán trên ta hay làm công thức mảng: {=SUM(IF(E1, (A1:A100>=15)*C1:C100, B1:B100*D1100) )}, do cả 2 mảng đều là vùng trọn không phân khúc nên ta có thề gắn thêm INDEX(....,), như:
=SUM(IF(E1, INDEX((A1:A100>=15)*C1:C100,) , INDEX(B1:B100*D1100,) ) ) và chỉ nhấn ENTER.

Còn nếu có sự phân khúc (tức động thái: phân ra thỏa điều kiện thì cho giá trị, và không thỏa thì giá trị khác =0 hay FALSE) trong 1 vùng/mảng thì sử dụng INDEX(...,) không có tác dụng, mà buộc phải dùng CSE. Vi dụ: {=SUM(IF(A1:A100>=15,C1:C100))} phải nhấn CSE, =SUM(IF(A1:A100>=15,INDEX(C1:C100,))) không tác dụng.

Tương tự hàm TRANSPOSE(Array), tuy đối số là Array nhưng nó bị phân khúc và chuyển từ dọc sang ngang hay ngược lại nên buộc phải nhấn CSE, mà không thể sử dụng INDEX(...,) để chỉ nhấn Enter.

Chúc hai em ngày vui.
(Vài dòng trích đoạn....)

 

[eke_rula]

INDEX(...,) chỉ có hiệu lực với những mảng nguyên, tức mảng được tạo từ 1 vùng trọn hay từ sự tính toán mà không bị phân khúc.
Ví dụ: A1= "1" hay "2", nếu A1 = 1 thì hãy lấy vùng (A1:A100>=15)*C1:C100, nếu A1= 2 thì lấy B1:B100*D1100, sau đó công lại kết quả.
Thường gặp bài toán trên ta hay làm công thức mảng: {=SUM(IF(A1, (A1:A100>=15)*C1:C100, B1:B100*D1100) )}, do cả 2 mảng đều là vùng trọn không phân khúc nên ta có thề gắn thêm INDEX(....,), như:
=SUM(IF(A1, INDEX((A1:A100>=15)*C1:C100,) , INDEX(B1:B100*D1100,) ) ) và chỉ nhấn ENTER.

Còn nếu có sự phân khúc (tức động thái: phân ra thỏa điều kiện thì cho giá trị, và không thỏa thì giá trị khác =0 hay FALSE) trong 1 vùng/mảng thì sử dụng INDEX(...,) không có tác dụng, mà buộc phải dùng CSE.

Tương tự hàm TRANSPOSE(Array), tuy đối số là Array nhưng nó bị phân khúc và chuyển từ dọc sang ngang hay ngược lại nên buộc phải nhấn CSE, mà không thể sử dụng INDEX(...,) để chỉ nhấn Enter.

Chúc hai em ngày vui.
(Vài dòng trích đoạn....)

Vậy Iferror thì sao anh!!!

 

[trandaithangckoto]

Bởi vì khi ai đó dùng file của mình mà nhấp đúp chuột vào công thức mảng đó thì nó ko có kết quả bác ạ. Nên nó bị bất tiện ở chỗ đó.Công thức =SUMPRODUCT(AGGREGATE(15,6,--SUBSTITUTE(B5:BK5,"M",""),ROW(INDIRECT("1:"&COUNTIF(B5:BK5,"M*"))))) của bác eke_rula rất chuẩn. Nếu có thêm cả dữ liệu M nghĩa là M,S,O, M1:20,M2:00,M2:45 ở trong B5:BK5 thì nó lại ko đúng bác ạ. Vì nó ko hiểu M,S và O. Có cách nào thêm vào các chữ M,S và O mà kết quả vẫn giữ nguyên ko bác.

 

[quocgiacan]

Vậy Iferror thì sao anh!!!

Iferror() cũng giống tình trạng phân khúc như ví dụ SUM(IF()) anh vừa mới ghi điều chỉnh lại ở bài #6.

Chúc em ngày vui

 

[befaint]

Bởi vì khi ai đó dùng file của mình mà nhấp đúp chuột vào công thức mảng đó thì nó ko có kết quả bác ạ. Nên nó bị bất tiện ở chỗ đó.Công thức =SUMPRODUCT(AGGREGATE(15,6,--SUBSTITUTE(B5:BK5,"M",""),ROW(INDIRECT("1:"&COUNTIF(B5:BK5,"M*"))))) của bác eke_rula rất chuẩn. Nếu có thêm cả dữ liệu M nghĩa là M,S,O, M1:20,M2:00,M2:45 ở trong B5:BK5 thì nó lại ko đúng bác ạ. Vì nó ko hiểu M,S và O. Có cách nào thêm vào các chữ M,S và O mà kết quả vẫn giữ nguyên ko bác.

Có nhưng hãy đưa dữ liệu lên và lần sau thì đưa luôn yêu cầu đó ở bài đầu tiên ấy...

 

[befaint]

Chúc hai em ngày vui.
(Vài dòng trích đoạn....)

Cảm ơn anh.

Chúc anh ngày vui.

 

[eke_rula]

Bởi vì khi ai đó dùng file của mình mà nhấp đúp chuột vào công thức mảng đó thì nó ko có kết quả bác ạ. Nên nó bị bất tiện ở chỗ đó.Công thức =SUMPRODUCT(AGGREGATE(15,6,--SUBSTITUTE(B5:BK5,"M",""),ROW(INDIRECT("1:"&COUNTIF(B5:BK5,"M*"))))) của bác eke_rula rất chuẩn. Nếu có thêm cả dữ liệu M nghĩa là M,S,O, M1:20,M2:00,M2:45 ở trong B5:BK5 thì nó lại ko đúng bác ạ. Vì nó ko hiểu M,S và O. Có cách nào thêm vào các chữ M,S và O mà kết quả vẫn giữ nguyên ko bác.

Bạn sửa lại vầy xem:

=SUMPRODUCT(AGGREGATE(15,6,--SUBSTITUTE(B5:BK5,"M",0),ROW(INDIRECT("1:"&COUNTIF(B5:BK5,"M*")))))

 

[eke_rula]

Iferror() cũng giống tình trạng phân khúc như ví dụ SUM(IF()) anh vừa mới ghi điều chỉnh lại ở bài #6.

Chúc em ngày vui

Ok,cám ơn ánh, đó giờ ít xài index() để loại bỏ Ctrl+Shift+Enter nên không rành về nó lắm!!!

 

[quocgiacan]

Cảm ơn anh.

(Anh sửa lại chỗ phím ESC đi)

Chúc anh ngày vui.

Là CSE (Ctrl+Shift+Enter) chứ không phải E,C,S .

Trong các diễn đàn phía ngoài hay ghi cách này vắn tắt.

Chúc em ngày vui.

 

[trandaithangckoto]

Trình độ của em còn học hỏi nhiều. Cám ơn bác eke_rula và các bác ạ.

 

[quocgiacan]

Ok,cám ơn ánh, đó giờ ít xài index() để loại bỏ Ctrl+Shift+Enter nên không rành về nó lắm!!!

Không phải em ít xài, mà vì em là "tiên phuông" xăm xăm tiến vào vùng đất hoang sơ, chỉ mong khai phá và làm thông thoáng đường sá. Nên không quan tâm đến tiểu tiết.

HieuCD và befaint tiếp tục phát huy "kiểm tra" và gầy dựng lại thành hình con đường.

Anh đi "hậu cần", phân tích, đối chiếu, thống kê lại những kết quả quá trình của em "khai hoang" rồi rút ra những dạng chung nhất, đưa về cốt lõi để dễ hiểu, rồi chia sẻ công việc của anh em. khà khà khà .

Anh nói ví dụ: em rất rành về FREQUENCY(), MMULT(), cứ tưởng nó lúc nào cũng buộc nhấn CSE, nhưng em có tin rằng vì đối số của nó là Array, nên khi đưa vùng (Range) vào thì cơ bản nó không cần nhấn CSE, cũng giống như SUMPRODUCT() và INDEX() vậy.

Anh ví dụ: anh lấy Vùng từ A1:A7 = 0;5;10;18;32;52;80 , anh sử dụng: =SUM(FREQUENCY(ROW(1:80),A1:A7)) chỉ nhấn Enter nó ra 80.

Vài dòng chia sẻ.

Chúc anh em mình ngày thiệt vui.

 

[eke_rula]

Không phải em ít xài, mà vì em là "tiên phuông" xăm xăm tiến vào vùng đất hoang sơ, chỉ mong khai phá và làm thông thoáng đường sá. Nên không quan tâm đến tiểu tiết.

HieuCD và befaint tiếp tục phát huy "kiểm tra" và gầy dựng lại thành hình con đường.

Anh đi "hậu cần", phân tích, đối chiếu, thống kê lại những kết quả quá trình của em "khai hoang" rồi rút ra những dạng chung nhất, đưa về cốt lõi để dễ hiểu, rồi chia sẻ công việc của anh em. khà khà khà .

Anh nói ví dụ: em rất rành về FREQUENCY(), MMULT(), cứ tưởng nó lúc nào cũng buộc nhấn CSE, nhưng em có tin rằng vì đối số của nó là Array, nên khi đưa vùng (Range) vào thì cơ bản nó không cần nhấn CSE, cũng giống như SUMPRODUCT() và INDEX() vậy.

Anh ví dụ: anh lấy Vùng từ A1:A7 = 0;5;10;18;32;52;80 , anh sử dụng: =SUM(FREQUENCY(ROW(1:80),A1:A7)) chỉ nhấn Enter nó ra 80.

Vài dòng chia sẻ.

Chúc anh em mình ngày thiệt vui.

Không phải em ít xài, mà vì em là "tiên phuông" xăm xăm tiến vào vùng đất hoang sơ, chỉ mong khai phá và làm thông thoáng đường sá. Nên không quan tâm đến tiểu tiết.

HieuCD và befaint tiếp tục phát huy "kiểm tra" và gầy dựng lại thành hình con đường.

Anh đi "hậu cần", phân tích, đối chiếu, thống kê lại những kết quả quá trình của em "khai hoang" rồi rút ra những dạng chung nhất, đưa về cốt lõi để dễ hiểu, rồi chia sẻ công việc của anh em. khà khà khà .

Anh nói ví dụ: em rất rành về FREQUENCY(), MMULT(), cứ tưởng nó lúc nào cũng buộc nhấn CSE, nhưng em có tin rằng vì đối số của nó là Array, nên khi đưa vùng (Range) vào thì cơ bản nó không cần nhấn CSE, cũng giống như SUMPRODUCT() và INDEX() vậy.

Anh ví dụ: anh lấy Vùng từ A1:A7 = 0;5;10;18;32;52;80 , anh sử dụng: =SUM(FREQUENCY(ROW(1:80),A1:A7)) chỉ nhấn Enter nó ra 80.

Vài dòng chia sẻ.

Chúc anh em mình ngày thiệt vui.

Thực tế thì em không thích sài INDEX() vì sẽ gây ngộ nhận cho người dùng công thức mảng là công thức bình thường, nhưng nó lại phù hợp với phần lớn người dùng văn phòng vì họ chỉ cần Enter nên xem như chấp nhận vì nó phù hợp với thực tế, chứ những người biết về mảng thì họ lại không thích điều này, anh lên mấy trang nước ngoài, khi họ đưa ra array formula, anh chỉ cấn quote bài họ và nói rằng index() sẽ tiện dụng hơn, họ sẽ trả lời cho anh một bài thật dài tại sao họ không cần INDEX(), có lẽ họ thích cái nào ra cái đó!!!

FREQUENCY(), MMULT() thông thường không cần phải Ctrl+Shift+Enter!!
​

Thực sự mà nói, lúc trước em có ghé vào đây những vấn đề về công thức được nói nhiều, nhưng những năm gần đây những kiến thức mới ít được cập nhập và phần lớn những vấn đề phức tạp ở đây đều hướng về VBA để giải quyết, công thức phần lớn có thể giải quyết được được các vấn đề ở mức phức tạp vừa phải, vấn đề chậm của việc sử dụng công thức có thể giải quyết trong tương lai gần, sợ lúc đó không ai sài excel nữa thôi, chứ bây giờ excel gần như là sản phẩm cuối cùng của các ngôn ngữ lập trình khác rồi, nó có thể có ra kết quả mà anh mong muốn mà không cần phải tính toán thêm gì nữa, nên đây chủ yếu là giúp đỡ người khác, học thêm cái mới và cho mình bớt ngu đi mà thôi, có thể xem là relax cũng được!!!
Và em nói luôn là em không phải leoheocon đâu anh!!!!

 

[quocgiacan]

Thực tế thì em không thích sài INDEX() vì sẽ gây ngộ nhận cho người dùng công thức mảng là công thức bình thường, nhưng nó lại phù hợp với phần lớn người dùng văn phòng vì họ chỉ cần Enter nên xem như chấp nhận vì nó phù hợp với thực tế, chứ những người biết về mảng thì họ lại không thích điều này, anh lên mấy trang nước ngoài, khi họ đưa ra array formula, anh chỉ cấn quote bài họ và nói rằng index() sẽ tiện dụng hơn, họ sẽ trả lời cho anh một bài thật dài tại sao họ không cần INDEX(), có lẽ họ thích cái nào ra cái đó!!!

FREQUENCY(), MMULT() thông thường không cần phải Ctrl+Shift+Enter!!
​

Thực sự mà nói, lúc trước em có ghé vào đây những vấn đề về công thức được nói nhiều, nhưng những năm gần đây những kiến thức mới ít được cập nhập và phần lớn những vấn đề phức tạp ở đây đều hướng về VBA để giải quyết, công thức phần lớn có thể giải quyết được được các vấn đề ở mức phức tạp vừa phải, vấn đề chậm của việc sử dụng công thức có thể giải quyết trong tương lai gần, sợ lúc đó không ai sài excel nữa thôi, chứ bây giờ excel gần như là sản phẩm cuối cùng của các ngôn ngữ lập trình khác rồi, nó có thể có ra kết quả mà anh mong muốn mà không cần phải tính toán thêm gì nữa, nên đây chủ yếu là giúp đỡ người khác, học thêm cái mới và cho mình bớt ngu đi mà thôi, có thể xem là relax cũng được!!!
Và em nói luôn là em không phải leoheocon đâu anh!!!!

Có lẽ chắc tâm lý người sử dụng đúng như em nói, tức nước ngoài không thích INDEX(...,), anh em VN thì thấy nó hữu dụng gần gũi! Âu, cũng là sở thích cũng là cái hay muôn màu muôn vẻ của Excel vậy.

Anh không nhấn mạnh lắm vào INDEX(...,) mà ở đây muốn đưa ra nhận xét qua việc phân tích để thấy rõ "bản chất" tại sao phải dùng tổ phím CSE? khi nào phải dùng và khi nào không cần dùng. Một số cách hiểu trước: là do nó xuất ra mảng (kết quả đa ô) nên buộc lòng phải nhấn CSE, nhưng với ví dụ anh đưa thì không phải, cũng như khi trong đối số của công thức là hằng số mảng lại càng không sử dụng CSE, chứ không riêng gì phải sử dụng INDEX(...,) mới không cần thao tác CSE. Nhưng thôi! đó là chuyện khác không phải chủ đề này.

Sau cùng, thật sự rất tiếc và xin lỗi, nếu em thấy điều này phiền đến em, vì đã hiểu nhầm em là leoheocon (lenongree), một người em trai mà anh ngưỡng mộ và luôn khâm phục về tính cách lẫn tư duy.

Chúc em ngày thiệt vui.