Vấn đề với SUMPRODUCT kết hợp với MID

[yore]

Chào các anh em.

Yore có dữ liệu như sau:

A B​

1 A+10 A
2 B+4
3

Tại cell A3 lặp công thức: =SUMPRODUCT((MID(A1:A2,3,2)>="8")*(LEFT(A1:A2,1)=B1)) thì kết quả trả về lại bằng 0. Nếu chỉnh dữ liệu đầu vào ở cell A1 thành A+9 thì kết quả trả về lại đúng: =1.

Phân tích trong hàm thì thấy có vấn đề tại hàm MID(A1:A2,3,2)>="8". Khi dữ liệu là A+10 đúng ra hàm MID phải trả về giá trị TRUE, đằng này nó lại trả về FALSE. Còn nếu dữ liệu là A+9 thì nó trả về TRUE--> kết quả đúng.

Vậy xin hỏi hàm này phải xử lý như thế nào và tại sao nó lại như vậy nhỉ?

 

[dhn46]

Công thức của bạn

[COLOR=#000000]=SUMPRODUCT([/COLOR][B][COLOR=#ff0000](MID(A1:A2,3,2)[/COLOR][/B][COLOR=#000000]>="8")*(LEFT(A1:A2,1)=B 1))

Đoạn màu đỏ sẽ lấy ra các giá trị tương ứng tại A1, A2 là "10" và "4" nhưng các giá trị này lấy ra sẽ là dạng [/COLOR]Text

- Với dạng Text khi bạn so sánh với "9" (dạng text) sẽ như sau: "10"<"9", "4"<9 => kết quả của hàm sẽ bằng 0 là chuẩn xác.
Bạn có thể thử test phép so sánh dạng text trực tiếp trên bảng tính sẽ thấy kết quả

Cách khắc phục: bạn sửa đổi hàm của bạn 1 chút chút sẽ được

[COLOR=#000000]=SUMPRODUCT(([/COLOR][SIZE=4][COLOR=#ff0000][B]--[/B][/COLOR][/SIZE][COLOR=#000000]MID(A1:A2,3,2)>=8)*(LEFT(A1:A2,1)=B 1))
[/COLOR]

Mục đích: chuyển phần Mid(...) sang dạng số và làm phép so sánh với số

 

[Nguyễn Duy Tuân]

Chào các anh em.

Yore có dữ liệu như sau:

A B​

1 A+10 A
2 B+4
3

Tại cell A3 lặp công thức: =SUMPRODUCT((MID(A1:A2,3,2)>="8")*(LEFT(A1:A2,1)=B1)) thì kết quả trả về lại bằng 0. Nếu chỉnh dữ liệu đầu vào ở cell A1 thành A+9 thì kết quả trả về lại đúng: =1.

Phân tích trong hàm thì thấy có vấn đề tại hàm MID(A1:A2,3,2)>="8". Khi dữ liệu là A+10 đúng ra hàm MID phải trả về giá trị TRUE, đằng này nó lại trả về FALSE. Còn nếu dữ liệu là A+9 thì nó trả về TRUE--> kết quả đúng.

Vậy xin hỏi hàm này phải xử lý như thế nào và tại sao nó lại như vậy nhỉ?

Công thức so sánh của bạn sai với ý đồ bạn muốn. Lý do như sau:

Sắp xếp tăng dần của văn bản thì:
"10"
"8"
tức là "10" > "8" = False

Sắp xếp tăng dần của số học thì:
8
10

Vậy với ý đồ của bạn thì cần chuyển giá trị về số học rồi so sánh sẽ đúng. Ví dụ sửa như sau
=SUMPRODUCT((VALUE(MID(A1:A2,3,2))>=8)*(LEFT(A1:A2,1)=B 1))
Vì kết quả của hàm MID trả về giá trị văn bản nên càn hàm VALUE để chuyển về số, sau đó so sánh vớ số 8 (không pải là "8")

 

[yore]

Nếu kiểu dữ liệu là text thì khi thay giá trị bằng A+9, nếu so sánh text "9" với "8" thì cũng như trường hợp "10" và "8", nghĩa là text "8" vẫn lớn hơn "9" chứ. Nhưgn sao trong trường hợp này excel lại hiểu là "8" < "9". Giải thích dùm mình trường hợp này với.

Cảm ơn sự nhiệt tình của bạn dhn46 và bạn Nguyễn Duy Tuân nhiều nhé.

@dhn46. Dấu "--" trước hàm MID mục đích chuyển text thành số, vậy "--" có quy định nào trong excel không? Hay tất cả số liệu là text mà để dấu "--" đằng trước đều được chuyển sang dạng số ??

Có 1 phát sinh xảy ra, nếu dữ liệu ở column A có cell rỗng sẽ trả về #VALUE! cách nào khắc phục đây ?

 

[yore]

Mục đích của vấn đề ở đây là mình muốn đếm tất cả các giá trị trong 1 mảng thỏa điều kiện: giá trị sau dấu "+" lớn hơn 1 giá trị cho trước. Ngoài dùm sumproduct thì có thể dùm hàm nào khác được không.

Hiện tại file của mình quá nặng, khoảng 5MB, dữ liệu lại link từ sheet này qua sheet kia lung tung, để hiểu được ý đồ thì cả vấn đề, nên mình không up file lên, mà chỉ tóm tắt lại vấn đề cho mọi người hiểu thôi.

 

[VetMini]

Dấu -- chính thức không có nghĩa là chuyển text thành số. Nó chỉ là một mẹo lợi dụng tính chất ép kiểu của Excel.
Excel phân biệt được toán tử số và toán tử chuỗi. Nhìn thấy dấu -, Excel tự động hiểu đây là toán tử chuyển dấu (tương đương với nhân cho -1). Và vì vậy nó tự động ép kiểu biểu thức từ chuỗi thành số để thực hiện phép chuyển dấu. Tuy nhiên nếu chỉ chuyển dấu thì sô trở thành âm? Vì vậy dấu trừ thứ 2 dùng để chuyển dấu ngược lại.

 

[yore]

help me, plz. Đổi qua hàm sum kết hợp với if cũng dính lỗi #value luôn !!!

 

[bebo021999]

Thử cái này xem:

=SUMPRODUCT((IF(ISNUMBER(SEARCH("+",A1:A2)),--MID(A1:A2,SEARCH("+",A1:A2)+1,10),0)>8)*(LEFT(A1:A2)=B1))

Nhấn Ctrl-shift-enter thay vì enter nhé

 

[heocontihon]

Xin cac anh chi ở diễn đàn giùp em ah, em dốt về excel, lên diễn đàn có down về một file của các anh chị trên đây thấy phù hợp yêu cầu mà muốn chỉnh sửa tí theo ngày tháng phát sinh giao dịch , vi dụ sau ngày 05/02/2014 em sẽ tổng kết xem hôm đó thu chi bao nhiêu ah? và chỉ cần mình đánh ngày sẽ hiện lên mà em ko biết làm. Nhờ các anh chị cao thủ ra tay chỉ giúp em với ah, e chân thanh cảm ơn, em đagn làm file ở tháng 01. Các anh chị giúp em nhé

em danh xong roi thi moi thay ko biet cach gui file di kem

 

[nghiaphuc]

Xin cac anh chi ở diễn đàn giùp em ah, em dốt về excel, lên diễn đàn có down về một file của các anh chị trên đây thấy phù hợp yêu cầu mà muốn chỉnh sửa tí theo ngày tháng phát sinh giao dịch , vi dụ sau ngày 05/02/2014 em sẽ tổng kết xem hôm đó thu chi bao nhiêu ah? và chỉ cần mình đánh ngày sẽ hiện lên mà em ko biết làm. Nhờ các anh chị cao thủ ra tay chỉ giúp em với ah, e chân thanh cảm ơn, em đagn làm file ở tháng 01. Các anh chị giúp em nhé

em danh xong roi thi moi thay ko biet cach gui file di kem :(

Bạn nhấn vào nút Đổi sang khung lớn, sẽ thấy nút lệnh đính kèm file ở trên thanh soạn thảo.
Bạn nhớ gõ bằng tiếng Việt có dấu nhé.

 

[heocontihon]

Xin cac anh chi ở diễn đàn giùp em ah, em dốt về excel, lên diễn đàn có down về một file của các anh chị trên đây thấy phù hợp yêu cầu mà muốn chỉnh sửa tí theo ngày tháng phát sinh giao dịch , vi dụ sau ngày 05/02/2014 em sẽ tổng kết xem hôm đó thu chi bao nhiêu ah? và chỉ cần mình đánh ngày sẽ hiện lên mà em ko biết làm. Nhờ các anh chị cao thủ ra tay chỉ giúp em với ah, e chân thanh cảm ơn, em đagn làm file ở tháng 01. Các anh chị giúp em nhé

em danh xong roi thi moi thay ko biet cach gui file di kem

SAo ko anh chị nào giúp đỡ cho em với ah, please help me

 

[quancui]

SAo ko anh chị nào giúp đỡ cho em với ah, please help me

Giúp heo con nè, tự dưng hỏi ngang xương chủ đề người ta vậy ai mà giúp.

 

[VetMini]

SAo ko anh chị nào giúp đỡ cho em với ah, please help me

Ly heo mi cái gì. Tự dưng chen vào thớt của người khác, đề tài khác là kém lịch sự tối thiểu. Bài không bị mót xoá mới là lạ.

 

[heocontihon]

Ly heo mi cái gì. Tự dưng chen vào thớt của người khác, đề tài khác là kém lịch sự tối thiểu. Bài không bị mót xoá mới là lạ.

Thành thật sorry anh( chị), em xưa nay toàn vào kiếm thông tin ít khi gửi bài bao giờ nên ko biết qui tắc chứ em ko cố tình chen ngang vào bài viêt của anh chị đâu ah, bị nói thế thì em sẽ rút kinh nghiệm mà.

 

[heocontihon]

Giúp heo con nè, tự dưng hỏi ngang xương chủ đề người ta vậy ai mà giúp.

Em cám ơn anh ( chị ) nhiều lắm ah, em sẽ rút kinh nghiệm lần sau cần gì sẽ tự mở đề tài ko dám chen ngang nửa đâu ah ko thì nhiều gạch quá em ko biết xây mấy căn nhà mới đủ