Xin hỗ trợ "Tìm chỗ sai khi dùng công thức mảng để thống kê học sinh" (1 người xem)

[nmhungcncm]

Mình đang chia lớp cho năm học 2013-2014 bằng excel.
Sau khi chia xong, mình dùng công thức mảng để thống kê. Không hiểu mình làm sai ở khâu nào mà khi thống kê khối 10 thì được nhưng sang khối 11 lại không được.
Mình nhờ các anh chị chỉ giúp cho mình xem mình sai ở chỗ nào?
Chi tiết cụ thể mình gửi file kèm theo.
Xin cám ơn.

 

[siwtom]

Không hiểu mình làm sai ở khâu nào mà khi thống kê khối 10 thì được nhưng sang khối 11 lại không được.

Đỏ đỏ - do bạn có khoimoi (lopmoi cũng thế) là số còn lop10moi là text

Bạn lập công thức mảng và là

=SUMPRODUCT((khoimoi="11")*(banmoi="CB")*(lopmoi="1"))

trong khi khoimoi và lopmoi của bạn ở dạng số.

Hãy đổi thành

=SUMPRODUCT((khoimoi=11)*(banmoi="CB")*(lopmoi=1))

Mà bạn gõ từng công thức cho từng ô đấy à? Bạn kiên nhẫn lắm. Nhưng ...

Nếu tôi đoán được thì 11, CB, 1 là bạn lọc từ ô cùng hàng nhưng ở cột B. Nếu đúng thế thì
1. Kiên nhẫn đi toi. Khi bạn sửa lại cột B thì lại phải sửa bằng tay cột C?
2. Tôi nhìn qua dữ liệu ở khoimoi, banmoi và lopmoi thì bạn có thể làm như sau (nhìn qua có thể chưa thấy hết, bạn kiểm tra lại):

công thức cho C14

=SUMPRODUCT(--(khoimoi&LEFT(banmoi;1)&lopmoi=$B14))

Kéo xuống dưới tới dòng 22

Công thức cho ô D14

=SUMPRODUCT((khoimoi&LEFT(banmoi;1)&lopmoi=$B14)*(gioi="x"))

Kéo xuống dưới tới dòng 22
--------------
Những chỗ còn lại bạn không hỏi (không tô mầu vàng) nhưng tôi đề nghị:

Công thức cho C4

=SUMPRODUCT(--(LEFT(ban10moi;1)&lop10moi=RIGHT($B4;2)))

thay cho như bây giờ bạn có là

{=SUM(IF(ban10moi="CB";1;0)*IF(lop10moi="1";1;0))}

với điều kiện là tôi đoán đúng, tức "CB" và "1" bạn lọc từ ô B4

Kéo công thức xuống tới dòng 13

Công thức cho D4

=SUMPRODUCT((LEFT(ban10moi;1)&lop10moi=RIGHT($B4;2))*(nu10moi="x"))

thay cho như bây giờ bạn có là

{=SUM(IF(ban10moi="CB";1;0)*IF(lop10moi="1";1;0)*IF(nu10moi="x";1;0))}

với điều kiện là tôi đoán đúng, tức "CB" và "1" bạn lọc từ ô B4

Kéo công thức xuống tới dòng 13

Nhưng sao các giá trị số bạn lại chuyển thành text?
---------------
Chú ý:
1. dấu phân cách trong công thức tôi dùng ký tự ";" - dấu chấm phẩy. Rất có thể trên máy bạn đó là ký tự "," - dấu phẩy. Nếu cần thiết thì đổi lại.

2. Các công thức ở trên không phải là công thức mảng.

 

[nmhungcncm]

Mình rất cám ơn bạn siwtom, mình không ngờ vào giờ đó mà bạn còn thức, tận tâm trả lời cho mình, mình xin giải tỏa "nếu" của bạn một số ý như sau:
1. Đúng là do lỗi định dạng số và định dạng chữ mà làm cho công thức không cho kết quả.
2. Sở dĩ có định dạng như vậy là mình lấy dữ liệu từ phần mềm quản lí nhà trường xuất ra (nó xuất định dạng chữ) mình sơ ý không để ý đến nó.
3. Mình gà mờ lắm, học được cái gì (trên diễn đàn này) thì làm cái đó và cũng chỉ học để phục vụ cho công việc chút xíu. Thực tình mà nói: công thức bạn cho, mình không hiểu chỗ có "hai dấu gạch nối --" nó là cái gì bạn ạ. Bạn có thể "Việt hóa"-Giải nghĩa dùm mình cái công thức có 2 dấu -- được không?.
4. Đúng là công thức mình phải coppi và sửa từng ô, lúc đầu mình định làm mấy cột phụ rồi nhập điều kiện nhưng không quen cách đó nên cứ phải làm như thế (làm kiểu khác sợ sai )
5. Mình tạo bảng thống kê này để so sánh xem đã chia các lớp đồng đều về giới tính, hoc lực hoặc hạnh kiểm hay chưa (trường mình năm nào cũng phải chia lại như vậy để các thầy cô không phải so bì "lớp hay" "lớp dở")
6. Lúc đầu mình dùng hàm sumif không ra kết quả nên sau đó mình dùng hàm sumproduct nên mới có chuyện dùng 2 loại hàm bạn ạ
Một lần nữa mình cám ơn bạn nhiều lắm, mình học thêm được một bài học nữa.

 

[vanle33]

3. Mình gà mờ lắm, học được cái gì (trên diễn đàn này) thì làm cái đó và cũng chỉ học để phục vụ cho công việc chút xíu. Thực tình mà nói: công thức bạn cho, mình không hiểu chỗ có "hai dấu gạch nối --" nó là cái gì bạn ạ. Bạn có thể "Việt hóa"-Giải nghĩa dùm mình cái công thức có 2 dấu -- được không?.
.

Dấu -- là để chuyển Text thành Number. Vấn đề này đã nói nhiều lần trên diễn đàn.

 

[siwtom]

Dấu -- là để chuyển Text thành Number. Vấn đề này đã nói nhiều lần trên diễn đàn.

Ở trường hợp cụ thể này không có TEXT đâu

3. mình không hiểu chỗ có "hai dấu gạch nối --" nó là cái gì bạn ạ. Bạn có thể "Việt hóa"-Giải nghĩa dùm mình cái công thức có 2 dấu -- được không?.

Bạn phải chịu khó "voc" thì mới hiểu.

1. Bạn thử bỏ "--" rồi xem kết quả thế nào. Kết quả là 0 đúng không? Điều này chứng tỏ rằng 99% chắc chắn mảng của ta hoặc có toàn số 0 hoặc không có số nào cả. Vì SUMPRODUCT chỉ thao tác với các giá trị số. - những giá trị không phải là số vd. Text, lôgíc - TRUE, FALSE - sẽ bị "lờ" đi, không tính, coi chúng như là không khí.

Thế thì ... đúng rồi. Ta có:

khoimoi&LEFT(banmoi;1)&lopmoi=$B14

"A = B" là gì? Là mệnh đề, là biểu thức lôgíc. Mà mệnh đề, biểu thức lôgíc thì có giá trị lôgíc, tức TRUE hoặc FALSE

Ở trên ta có mảng mệnh đề do khoimoi, banmoi và lopmoi là mảng. Cái kiểu như (vd. mảng có n phần tử):

mảng
khoimoi(1)&LEFT(banmoi(1);1)&lopmoi(1) = $B14
khoimoi(2)&LEFT(banmoi(2);1)&lopmoi(2) = $B14
...
khoimoi(n)&LEFT(banmoi(n);1)&lopmoi(n) = $B14

tức là mảng mệnh đề "A = B". Vậy mảng đó là mảng các giá trị TRUE/ FALSE
Khi mảng khoimoi&LEFT(banmoi;1)&lopmoi=$B14 chỉ có giá trị TRUE/FALSE thì rõ ràng nó bị SUMPRODUCT "lờ" đi nên kết quả trả về là 0 - mảng không có số nào (numeric)

2. "--" chẳng qua là 2 dấu trừ "-". Thế thôi.
Khi bạn viết

=SUMPRODUCT(-(khoimoi&LEFT(banmoi;1)&lopmoi=$B14))

thì do "-" là toán tử của phép tính số học, tức phép tính toán trên các con số nên chàng trai thông minh Excel hiểu ngay ý ta và biến TRUE thành 1 và FALSE thành 0 để "giúp" ta có thể thực hiện việc tính toán của mình. Nhưng bạn để ý là do ta dùng 1 dấu trừ "-" nên TRUE bị biến thành -1. Kết quả là ta có -36 học sinh. Hơi ngồ ngộ, đúng không? Nhưng ta biết là

-(-a) = a
-(-TRUE) = -(-1) = 1

he he

Vậy thì ta thêm dấu trừ nữa để có -(-a) = a. Tức muốn có số cho trước (cả về dấu và trị tuyệt đối) thì ta phải có 2k dấu trừ với k = 1, 2, 3, ...

Nhưng chỉ cần 2 dấu trừ là đủ vì ta có ấm đầu đâu mà cho 10 dấu trừ mặc dù vẫn đúng. Nếu đồng nghiệp gà hơn mình thì bạn viết cho anh ta công thức và bạn gõ 10 dấu trừ cho anh ta lé mắt.
-------------
Ngoài việc cho 2 dấu trừ thì bạn có thể nhân với 1 (*1), cộng với 0 (+0), nâng lên lũy thừa 1 (^1). Các phép tính như thế không làm thay đổi giá trị, nhưng do chúng là các toán tử (cộng, trừ, nhân, lũy thừa) của phép tính số học nên thêm vào để "báo" cho anh chàng thông minh Excel biết là ta có "ý đồ" thực hiện phép tính số học. Lúc đó anh ta sẽ biến TRUE thành 1, "1" thành 1. Tất nhiên "mẹo" chỉ dùng được khi "giá trị" có thể biến thành số được. Chứ "hichic" thì có thêm gì vào thì dù anh Excel rất muốn giúp thì anh ta cũng không thể biến thành số được.

----------------------
Cũng cần nói thêm ở đây cho chính xác. Dấu trừ "-" mà ta dùng ở trên chính xác là ... Nói nôm na thì nó là "toán tử" của phép đổi dấu. Đổi giá trị thành dấu ngược lại.

1. a - b
Dấu trừ là "toán tử" của phép trừ.

2. -a
Ở đây không có phép trừ nào cả. Dấu trừ ở đây là "toán tử" đổi dấu.

Như vậy dấu trừ "-" dùng với 2 ý nghĩa khác nhau.
Dấu cộng "+" chỉ là "toán tử" của phép cộng thôi.

Vì thế khi ta dùng dấu trừ là ta báo cho Excel biết là ta muốn đổi dấu. Mà đổi dấu thì "đối tượng" phải là số nên Excel biến TRUE thành 1.

Còn nếu ta dùng dấu cộng "+" thay cho dấu trừ "-" thì do dấu cộng không có nghĩa là "đổi dấu" nên Excel không hiểu. Chỉ khi ta viết + 0 thì rõ ràng lúc này ta có phép cộng nên Excel hiểu được ý.

 

[hua_tier1tk5_quy]

Cảm ơn các bạn đã nhiệt liệt san sẻ và đóng góp vào diễn đàn của chúng ta.
Tôi tin rằng diễn đàn sẽ càng ngày càng mạnh mẽ và phong phú hơn nếu chúng ta cùng gắng.
Cảm ơn đã ủng hộ và đi cùng chúng tôi suốt thời gian vừa qua nhé.

 

[SA_DQ]

Tiếp theo mình xin góp í với chủ topic một khía cạch khác, đó là CSDL của bạn

Ở các trang tính bạn thường gộp chung cả fần mà mình thường gọi là lích lịch trích ngang (như nơi sinh, . . .) vô fần chứa dữ liệu học lực (điểm, nghỉ học, lên lớp, ở lại,. . .)
Việc này sẽ làm cho fần tính toán luôn thêm nặng nề.

Nhưng để tách các fần ra với nhau thì quả không dễ chút nào.
Khó khăn ở chổ mã duy nhất của bạn chưa fù hợp để xài trong excel

Vài góp í nhỏ; Nếu bạn nào hay tác giả topic quan tâm, ta tiếp tục thảo luận!

 

[nmhungcncm]

Ở các trang tính bạn thường gộp chung cả fần mà mình thường gọi là lích lịch trích ngang (như nơi sinh, . . .) vô fần chứa dữ liệu học lực (điểm, nghỉ học, lên lớp, ở lại,. . .)
Việc này sẽ làm cho fần tính toán luôn thêm nặng nề.

Nhưng để tách các fần ra với nhau thì quả không dễ chút nào.
Khó khăn ở chổ mã duy nhất của bạn chưa fù hợp để xài trong excel

Vài góp í nhỏ; Nếu bạn nào hay tác giả topic quan tâm, ta tiếp tục thảo luận!

Cảm ơn bạn đã chia sẻ, mình xin lí giải để bạn hiểu
1. Sở dĩ có cả lí lịch và điểm trong 1 bảng tinh là như thế này:
- Lý lịch học sinh mình xuất từ phần mềm quản lí điểm (PMQL) ra
- Kết quả học lực cũng xuất từ PMQL ra (vậy nên mới có chuyện dữ liệu "số" mà định dạng "chữ")
- Mình coppi ghép 2 cái này vào làm 1 để mình chia lớp (mình chỉ dùng excel giúp mình công đoạn này thôi, việc tính điểm do PMQL làm)
- Sau khi chia lớp xong thì mình lại up dữ liệu mới vào PMQL cho năm học mới
- Sở dĩ phải ghép 2 nội dung bạn đề cập vào 1 bảng tính để sau khi chia lại lớp thì phần lí lịch học sinh nó cũng "chạy" theo, mình không mất công nhập lại phần lí lịch học sinh nữa (mỗi năm hơn 1000 học sinh, nhập lại dù cẩn thận cỡ nào cũng bị sai và mất nhiều thời gian lắm)
2. Về mã học sinh là do PMQL nó tự sinh ra bạn ạ, cái đó không quan trọng lắm vì trước khi úp lên mình cũng xóa cái mã học sinh này. (nói UP cho quan trọng chứ thực tế thì mình xuất cái mẫu trong PMQL ra rồi coppi nội dung cần thiết vào rồi mới up lên- không UP trực tiếp từ file excel mà bạn đã thấy)
@@@siwtom
- Mình rất cám ơn bạn đã nhiệt tình, mình xin học dần dần, tuổi lớn rồi, giáo viên môn khoa học xã hội nên thú thực các thuật ngữ excel mà bạn nói với bạn là đơn giản nhưng với mình là "rừng" bạn ạ.