在Excel中使用rank函数进行自动统计排序和颜色自动变化的步骤如下：

一、设定评价项目和权重：

项目A权重40%；项目B权重40%；项目C权重10%；项目D权重10%

二、设定综合得分计算公式：

综合得分=项目A完成率*100*40% 项目B完成率*100*40% 项目C完成率*100*10% 项目D完成率*100*10%

三、使用rank函数进行综合得分自动统计排名：

1. 输入rank函数的三个参数：数值、引用和排位方法。其中，数值是要排序的单元格，引用是所有需要排序的单元格的范围，排位方法是表示升序还是降序排名的数字（0或1）。

2. 在指定的单元格中输入“=RANK(B4,$B$4:$B$10,0)”，其中B4是你要排序的总分所在的单元格，$B$4:$B$10表示排名的范围，0就表示降序排名了。

3. 按下回车键，就能得到按照指定总分和排位进行的升序或降序排列的数字。

注意：$B$4:$B$10一定要绝对引用，否则得出排序会错乱。

四、使用条件格式进行颜色自动变化

1.选定需要需要自动变化单元格或区域

2. 使用条件格式公式：点击公式栏上的“条件格式”按钮，然后选择“新建规则”。在弹出的对话框中，输入条件格式的规则，例如大于、小于等。完成规则输入后，点击“确定”。

3. 使用自定义颜色：选择包含要应用条件格式的单元格或区域。点击开始选项卡中的“填充颜色”按钮。在弹出的对话框中，选择一个颜色作为自定义颜色，然后点击“确定”。

如案例中我们需要对排名前3名的自动显示为绿色，后3名自动显示为深红色。

第一步：选中C4：C10，点击“条件格式”-“突出显示单元格规则”-“小于”，弹出的对话框输入“4”，设置为“绿填充色深绿色文本”

第二步：选中C4：C10，点击“条件格式”-“突出显示单元格规则”-“大于”，弹出的对话框输入“4”，设置为“浅红填充色深红色文本”

这些方法可以帮助你在Excel中根据特定的条件格式改变文本的颜色。根据你的具体需求，可以选择其中的一种或多种方法来应用条件格式。