語文及格人數

=COUNTIF(C3:C7,">=60")

語文不及格人數

=COUNTIF(C3:C7,"

數學及格人數

=COUNTIF(D3:D7,">=60")

數學不及格人數

=COUNTIF(D3:D7,"

優秀人數

=COUNTIF(F3:F7,">=90")

或

=COUNTIF(G3:G7,"優秀")

良好人數

=COUNTIF(F3:F7,">=75")-COUNTIF(F3:F7,">=90")

或

=COUNTIF(G3:G7,"良好")

合格人數

=COUNTIF(F3:F7,">=60")-COUNTIF(F3:F7,">=75")

或

=COUNTIF(G3:G7,"合格")

如果合格人數是指平均分在60及以上的全部人數,則公式為

=COUNTIF(F3:F7,">=60")

不合格人數

=COUNTIF(F3:F7,"

G3單元格公式

=IF(F3="","",IF(F3

或

=IF(F3="","",LOOKUP(F3,{0,"不合格";60,"合格";75,"良好";"90,"優秀"}))

下拉填充公式

I3單元格公式

=VLOOKUP(H3,B$3:G$7,6,)

下拉填充公式

總體如圖

https://iknow-pic.cdn.bcebos.com/024f78f0f736afc32c0eaad2b019ebc4b7451202