Lỗi công thức khi xử lí 1 ô nhiều giá trị

[kiaconchim]

Mọi ng giúp chim với nhé :">

Đây là bảng survey 50 người của chim, mỗi survey 9 câu hỏi. Các já trị điền vào mỗi tươngứng A= 1, B =2 , C=3, D=4, v...v... Tức là, ví dụ, ở ô 1-1, ng thứ nhất trả lời câu hỏi thứ nhất đáp án A.


Vấn đề là khi xử lý dữ liệu, lấy ví dụ đơn jản như cộng tổng theo cột dọc (tức là theo từng câu), những ô ghi nhiều já trị như vầy (tức là ng làm survey đc chọn nhiều đáp án cho 1 câu) nó ko cộng vào, mà cho = 0 chỉ những ô nào có 1 já trị nó mới cộng.

Cụ thể hơn. Ở pic trên, khi em thử cộng ô ở dòng 17 cột thứ 2 (có con trỏ đó) với ô dòng 18 ngay dưới nó, nó ra tổng =2, trong khi ý của em muốn nó ra là 5.

Ai biết làm thế nào để nó cộng các giá trị trong các ô kia ko Chim thử dùng dấu , với dấu ; để fân cách các giá trị ra rồi mà nó vẫn ko chịu cộng vô

Có cách nào mà, 1 ô vẫn ghi rõ 2 giá trị (trở lên) như ở trong hình mà khi cộng nó vẫn tính vào ko. Chứ h bảo cộng sẵn các já trị trong ô lại rồi ghi mỗi kết quả vào ô thôi thì chả nói làm j

 

[khoavu87]

Đọc chưa hiểu rõ ý bạn lắm, thử đoán thế này xem:
bạn thử cộng cột thứ hai là cột Q1.
đặt công thức tại cột hai dòng 20 = sum (B1:B19>0,B1:B19). kẾT thúc công thức bằng tổ hợp phím Ctr+shift+Entter

 

[kiaconchim]

Đọc chưa hiểu rõ ý bạn lắm, thử đoán thế này xem:
bạn thử cộng cột thứ hai là cột Q1.
đặt công thức tại cột hai dòng 20 = sum (B1:B19>0,B1:B19). kẾT thúc công thức bằng tổ hợp phím Ctr+shift+Entter

Ý của em là. Trong pic trên chẳng hạn. em cộng B17 và B18 nó ra kết quả là 2. Trong khi ý của em muốn là B17 (=1+2) + B18 (=2) = 5 cơ

 

[khoavu87]

Ý của em là. Trong pic trên chẳng hạn. em cộng B17 và B18 nó ra kết quả là 2. Trong khi ý của em muốn là B17 (=1+2) + B18 (=2) = 5 cơ

Cái này tạm thời chưa nghĩ ra cách ưng ý. Nếu làm thêm cột phụ sẽ có cách làm. Nhưng thấy hơi cùi bắp.hihi.

 

[vodoi2x]

Cái này tạm thời chưa nghĩ ra cách ưng ý. Nếu làm thêm cột phụ sẽ có cách làm. Nhưng thấy hơi cùi bắp.hihi.

cùi bắp to luôn, khoavu ah, (sao tự làm khó mình thế hihiiii)

Nếu chỉ lấy tổng 1 ô thì sử dụng đơn giản thế này thôi, KHÔNG phải name hay macro4 làm gì

ví như B17 chứa chuỗi đó thì giá trị tổng cần tìm của B17 là công thức sau
=SUMPRODUCT(--MID(SUBSTITUTE(B17,",",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(B17,",","")))),1))

Tổng cần tìm 2 ô B17, B18 sẽ là
=SUMPRODUCT(--MID(SUBSTITUTE(B17,",",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(B17,",","")))),1))+ SUMPRODUCT(--MID(SUBSTITUTE(B18,",",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(B18,",","")))),1))

Nhưng có cách đơn giản hơn cho tổng quát SUM cho 1 vùng luôn

 

[vodoi2x]

Ý của em là. Trong pic trên chẳng hạn. em cộng B17 và B18 nó ra kết quả là 2. Trong khi ý của em muốn là B17 (=1+2) + B18 (=2) = 5 cơ

Chót khuya rồi, làm luôn cho bạn cho nhẹ gánh vai sần

Giả sử vùng cần tính tổng là B10:B20, thì công thức cần tính tổng là

=15*SUMPRODUCT(LEN($B$10:$B$20))-SUMPRODUCT(LEN(SUBSTITUTE($B$10:$B$20,1,"")))-2*SUMPRODUCT(LEN(SUBSTITUTE($B$10:$B$20,2,"")))-3*SUMPRODUCT(LEN(SUBSTITUTE($B$10:$B$20,3,"")))-4*SUMPRODUCT(LEN(SUBSTITUTE($B$10:$B$20,4,"")))-5*SUMPRODUCT(LEN(SUBSTITUTE($B$10:$B$20,5,"")))

lưu ý:
+ Ở đây theo bảng số liệu trên thì có các phương án chọn câu hỏi Survey là 1 2 3 4 5, nên số 15 đầu tiên đó chính là 15=1+2+3+4+5

+ Nếu có thêm 6 7 8 9 (chắc không có trường hợp này) thì bạn thêm vào tương tự ah, hoặc bớt đi cũng vậy

Bạn cứ test , chắc mọi việc đơn giản giờ chỉ việc ngồi đếm kết quả ra $


@all: các thành viên khác vẫn còn cửa rút gọn công thức đó, hoặc phương án khác

 

[bebo021999]

Dùng cách này xem:

View attachment 72998

 

[hoamattroicoi]

Dùng cách này xem:

View attachment 72998

Nếu chỉ Enter thì kẹp cái Sumproduct vào :
Công thức tại B21 :

=SUMPRODUCT((--MID((B2:B20)&","&0&","&0&","&0&","&0,{1,3,5,7,9},1))*1)

 

[vodoi2x]

Nếu chỉ Enter thì kẹp cái Sumproduct vào :
Công thức tại B21 :

=SUMPRODUCT((--MID((B2:B20)&","&0&","&0&","&0&","&0,{1,3,5,7,9},1))*1)

Thế thì tại sao không là

=SUMPRODUCT(--MID((B2:B20)&",0,0,0,0",{1,3,5,7,9},1))

Hoặc sử dụng công thức sau, cũng được:

=SUM($B$2:$B$20)+SUMPRODUCT(ROW($1:$5)*COUNTIF($B$2:$B$20,"*"&ROW($1:$5)&"*"))

 

[huuthang_bd]

Làm vầy cho gọn nè:

=SUM(--(0&MID((B2:B20),{1,3,5,7,9},1)))

Công thức mảng.

 

[kiaconchim]

cảm ơn mọi người đã nhiệt tình giúp đỡ ạ :x