Ứng dụng hàm concatenate!

[ahappygoluckygirl]

[FONT=&quot]Mình mới tìm hiểu qua về hàm concatenate và thấy có nhiều ứng dụng rất hay đặc biệt là có thể dùng với hàm if được. Tiện đây mình gửi bài excel này mình vừa làm xong có tính đến điểm trung bình mà dùng hàm if vượt quá 7 cấp không được. Cách làm này cũng khá ngắn gọn mà không phải lập thêm hàng cột hay bảng phụ nào khó hiểu cả. Các bạn vào tham khảo và cho mình xin ý kiến nha.


[/FONT]

 

[ptm0412]

CONCATENATE(IF(C5="Toán";D5;" ");IF(C5="Lý";E5;" ");IF(C5="Hóa";F5;" ");IF(C5="Văn";G5;" ");IF(C5="Sử";H5;" ");IF(C5="Địa";I5;" ");IF(C5="Anh";J5;" ");IF(C5="Pháp";K5;" ");IF(C5="Trung";L5;" ");IF(C5="Sinh";M5;" "))

Có thể thay bằng
IF(C5="Toán";D5;" ")&IF(C5="Lý";E5;" ")&IF(C5="Hóa";F5;" ")&IF(C5="Văn";G5;" ")&IF(C5="Sử";H5;" ")&IF(C5="Địa";I5;" ")&IF(C5="Anh";J5;" ")&IF(C5="Pháp";K5;" ")&IF(C5="Trung";L5;" ")&IF(C5="Sinh";M5;" ")

Hiệu quả tương đương. Nhưng không phải hay nhất:
- Tại sao không là "" (chuỗi rỗng) mà lại là " " (1 khoảng trắng)?
- Tại sao lại dùng biện pháp nối chuỗi trong khi bản chất của nó là số, chuyển thành chuỗi, rồi khi tính trung bình lại ép Excel phải chuyển ngược lại?

Thí dụ như vầy, số thì cứ cư xử như số:
IF(C5="Toán";D5;0)+IF(C5="Lý";E5;0)+IF(C5="Hóa";F5;0)+IF(C5="Văn";G5;0)+IF(C5="Sử";H5;0)+IF(C5="Địa";I5;0)+IF(C5="Anh";J5;0)+IF(C5="Pháp";K5;0)+IF(C5="Trung";L5;0)+IF(C5="Sinh";M5;0)

Có 1 phương án rút ngắn công thức như sau: Tìm xem môn chuyên (C5) ở cột nào, lấy điểm ở cột đó:
Index(D5:M5; 1; Match(C5;$D$4:$M$4;0))

Ngoài ra, nếu dùng SumIf thì ngắn hơn rất nhiều:
SUMIF($D$4:$M$4;C5;D5:M5)

Nguyên công thức chỉ có bi nhiu:
=ROUND(AVERAGE(D5:M5;SUMIF($D$4:$M$4;C5;D5:M5));1)

 

[ahappygoluckygirl]

Mình cám ơn bạn rất nhiều vì đã đóng góp ý kiến giúp bài viết của mình thêm hoàn thiện hơn, mình đã xem lại bài viết của mình, quả thực mình đã hiểu sai bản chất giữa số và chữ như vậy. Mình đã làm thử theo cách bạn mách cho và thấy đúng là rất ngắn gọn và dễ hiểu. Mình còn là thành viên mới quá lại là lần đầu tiên post bài lên diễn đàn nên không tránh khỏi những sai sót nên nếu mình có gặp khó khăn gì về excel thì mong bạn và các thành viên của GPE hãy nhiệt tình giúp đỡ mình với nhé. Cám ơn ngôi nhà chung GPE, cám ơn các thành viên của đại gia đình này rất nhiều.

 

[dat_butmuc]

Mình cám ơn bạn rất nhiều vì đã đóng góp ý kiến giúp bài viết của mình thêm hoàn thiện hơn, mình đã xem lại bài viết của mình, quả thực mình đã hiểu sai bản chất giữa số và chữ như vậy. Mình đã làm thử theo cách bạn mách cho và thấy đúng là rất ngắn gọn và dễ hiểu. Mình còn là thành viên mới quá lại là lần đầu tiên post bài lên diễn đàn nên không tránh khỏi những sai sót nên nếu mình có gặp khó khăn gì về excel thì mong bạn và các thành viên của GPE hãy nhiệt tình giúp đỡ mình với nhé. Cám ơn ngôi nhà chung GPE, cám ơn các thành viên của đại gia đình này rất nhiều.

Thêm nữa, thấy 02 công thức về "Ghi chú" & "Môn Thi lại" tôi thấy hơi dài, sai (ví dụ môn sử-chuyên sử) & rườm rà, làm như sau thì gọn hơn 1 chút (từ dòng thứ 5)
- Ghi chú:

=IF(OR(SUMIF($D$4:$M$4,C5,D5:M5)<5,COUNTIF(D5:M5,"<5")>1),"Hỏng",IF(MIN(D5:M5)>=5,"Đạt","Thi lại"))

- Môn thi lại:

=IF(O5="Thi lại",INDEX($D$4:$M$4,,MATCH(MIN(D5:M5),D5:M5,0)),"")

 

[ahappygoluckygirl]

Mình lại cảm ơn bạn một lần nữa nha, ý kiến của bạn đóng góp cho mình rất hay. Mình đã xem và hiểu rồi. Lần sau nếu làm bất cứ bài excel nào mình sẽ cố gắng nghĩ cách để làm cho nó ngắn gọn và dễ hiểu nhất ha. Và cũng rất mong bạn hãy tích cực đóng góp ý kiến và phê bình giúp cho bài viết của mình thêm hoàn thiện hơn nha. Chúc người bạn cách tôi gần 2000 cây số sẽ thành công hơn nữa trong lĩnh vực về excel nha.

 

[anh2hn]

Index(D5:M5; 1; Match(C5;$D$4:$M$4;0))

sao có công thức này ạ, tại hạ k bit, cho xin hỏi, mà k thấy dấu =

 

[ptm0412]

sao có công thức này ạ, tại hạ k bit, cho xin hỏi, mà k thấy dấu =

Index(D5:M5; 1; Match(C5;$D$4:$M$4;0))

thay thế cho cái đoàn tàu hoả xuyên việt sau:

CONCATENATE(IF(C5="Toán";D5;" ");IF(C5="Lý";E5;" ");IF(C5="Hóa";F5;" ");IF(C5="Văn";G5;" ");IF(C5="Sử";H5;" ");IF(C5="Địa";I5;" ");IF(C5="Anh";J5;" ");IF(C5="Pháp";K5;" ");IF(C5="Trung";L5;" ");IF(C5="Sinh";M5;" "))

Công thức cuối cùng là:

=ROUND(AVERAGE(D5:M5;Index(D5:M5; 1; Match(C5;$D$4:$M$4;0)));1)

Có dấu bằng (=) rồi nhé!

Mà xin hỏi lại: cho xin hỏi là sao? Vừa cho vừa xin?

 

[pomete]

Mình nghĩ là có thể dùng hàm HLOOKUP được mà, dùng Index kết hợp với match thì hơi rắc rối.
ROUND(AVERAGE(D5:M5,HLOOKUP(C5,$D$4:M5,ROWS($1:2),0)),1)

 

[ahappygoluckygirl]

Bạn ah, mình xem phần hỏi đáp của bạn thì thấy có phần bạn bảo mình là môn ghi chú thi lại của môn sử sai vì nhẽ ra môn đó phải là hỏng thì đúng rùi. Nhưng mà mình thấy cái công thức =IF(OR(SUMIF($D$4:$M$4,C5,D5: M5)<5,COUNTIF(D5:M5,"<5")>1),"Hỏng",IF(MIN(D5:M5)>=5,"Đạt","Thi lại")) bạn đưa ra để ghi các môn thi lại, đạt hay hỏng ở phần ghi chú là không ổn. Mình đã thử làm theo cách đó nhưng đúng là đến dòng thứ 5 môn sử thì nó vẫn cho ra kết quả là "Thi lại" chứ không phải là "Hỏng". Trong khi đó áp dụng công thức này cho tất cả thì nó vẫn cứ sai. Mà mình thấy là áp dụng công thức thì dùng một loạt từ ô đầu cho đến ô cuối chứ ai lại "Đứt gánh giữa đường" rồi lại chuyển sang công thức khác như thế. Như thế thì làm bài trên lớp như vậy các thầy cô cũng không chấp nhận cho mình thì sao?
Mình đã làm lại cái phần ghi chú đó và đã thay cái công thức của bạn bằng công thức sau:
=IF(COUNTIF(D5:M5,">=5")=10,"Đạt",IF(OR(INDEX(D5:M5,MATCH(C5,$D$4:$M$4,0))<5,COUNTIF(D5:M5,"<5")>1),"Hỏng","Thi lại")).
Mình đã dùng công thức này và đã kiểm tra lại thấy đúng. Bạn xem thử có ổn không nha?

 

[boyxin]

[FONT=&quot;]Mình mới tìm hiểu qua về hàm concatenate và thấy có nhiều ứng dụng rất hay đặc biệt là có thể dùng với hàm if được. Tiện đây mình gửi bài excel này mình vừa làm xong có tính đến điểm trung bình mà dùng hàm if vượt quá 7 cấp không được. Cách làm này cũng khá ngắn gọn mà không phải lập thêm hàng cột hay bảng phụ nào khó hiểu cả. Các bạn vào tham khảo và cho mình xin ý kiến nha.
[/FONT]

Các công thức đã rất gọn và chuẩn
Cột ĐTB: ptm0412

N5 =ROUND(AVERAGE(D5:M5,SUMIF($D$4:$M$4,C5,D5:M5)),1)

Cột Môn Thi Lại: dat_butmuc

P5 =IF(O5="Thi lại",INDEX($D$4:$M$4,,MATCH(MIN(D5:M5),D5:M5,0)),"")

----------------------
Cột Ghi chú có thể dùng công thức sau sẽ gọn hơn chút xíu: boyxin

O5 =IF(MIN(D5:M5)>=5,"Đạt",IF(COUNTIF(D5:M5,"<5")=1,"Thi lại","Hỏng"))

 

[dat_butmuc]

----------------------
Cột Ghi chú có thể dùng công thức sau sẽ gọn hơn chút xíu: boyxin

O5 =IF(MIN(D5:M5)>=5,"Đạt",IF(COUNTIF(D5:M5,"<5")=1,"Thi lại","Hỏng"))

Cái này không thỏa mãn điều kiện

' - Học sinh học lớp chuyên mà thi môn chuyên có điểm < 5 hoặc có trên 1 môn thi có điểm < 5 thì ghi là "Hỏng"

Trong bảng của tác giả gửi đến dòng thứ 9 (chuyên Sử) đúng ra là phải hỏng, hoặc nếu thay một môn chuyên nào đó <5 thì cũng chỉ ghi chú là thi lại <-> đúng ra là hỏng.
Thế nên, vẫn nên giữ lại công thức:

=IF(OR(SUMIF($D$4:$M$4,C5,D5:M5)<5,COUNTIF(D5:M5,"<5")>1),"Hỏng",IF(MIN(D5:M5)>=5,"Đạt","Thi lại"))

 

[garj_cuong]

Cho mình hỏi nếu dùng hàm concatenate này để tổng hợp các text của các ô vào mà mình muốn cái tiêu đề luôn đi gắn trước nội dung ô text đó thì làm thế nào nhỉ?
Mình không tìm được chỗ đưa file lên để ví dụ thông cảm nhé

 

[n78nokia81]

Cho mình hỏi nếu dùng hàm concatenate này để tổng hợp các text của các ô vào mà mình muốn cái tiêu đề luôn đi gắn trước nội dung ô text đó thì làm thế nào nhỉ?
Mình không tìm được chỗ đưa file lên để ví dụ thông cảm nhé

Bạn lấy địa chỉ tuyệt đối của tiều đề gắn vào là được mà