夜雨聆风学习资料网

ARTICLE · 1378685

Excel花名册的下拉和标红,WorkBuddy十分钟配好

Excel花名册的下拉和标红,WorkBuddy十分钟配好

九月花名册收上来,「部门」一栏有三种写法:销售部、销售、销售事业线。你想数一数销售有多少人,筛选「销售部」,出来 12 个,实际有 21 个。

「合同到期」那一列更安静。有一栏已经过期两周,没人发现,因为那格就是普通日期,长得和别的格一模一样。

这两件事看着不相干,根子上是同一个毛病:这张表谁都能填,填成什么样都收得下。

花名册出错,多数的责任在表,不在填表的人。下拉管住入口,颜色管住注意力,这两样配完,大概十分钟。

01 下拉的真实作用,是让错的选项填不进去

很多人配下拉是为了「点着方便」,这个理解偏了。它真正的价值是把可选项收窄到一个封闭集合,手打不进去,复制粘贴也会被拦。

做法是先在文件里加一张「参数」sheet。A 列部门、B 列合同类型、C 列员工状态,一行一个值,别加标题以外的任何装饰。

然后选中这些区域,在「公式」里的名称管理器新建名称,比如 部门列表,引用位置写 =参数!$A$2:$A$30。范围留出余量,别只框到现有数据的最后一行。

回到花名册,选中部门列的数据区,「数据」里的数据验证,允许选「序列」,来源填 =部门列表。出错警告那一页别跳过,标题写「部门不在清单里」,内容写「请从下拉里选,或先到参数表新增」。这句提示是给下一个填表的人看的。

为什么不直接写 =参数!A:A:整列引用会连空白一起算进下拉,下拉里滚半天没到底。另外参数表一旦改名或被移动,直写的工作簿路径引用容易失效,定义名称会跟着走。

02 颜色只干一件事,让眼睛先看到快到期的人

选中整个数据区,比如 $A$2:$J$500,条件格式新建规则,选「使用公式确定要设置格式的单元格」,公式填:

=AND($H2<>"",$H2-TODAY()<=30)

H 列是合同到期日。这行公式做了两件事:先确认这格不是空的,再算它减今天还剩几天,小于等于 30 就变色。

重点在 $H2 这个写法。列标加美元符号,行号不加。意思是「每一行都去看自己那一行的 H 列」。写成 $H$2,整片区域全按第一行判断,结果要么全红要么全白。

格式建议只用一种:浅红底。别上三种颜色分级,花名册不是仪表盘,一眼扫过去能挑出快到期的那几行就够了。分档这件事留给专门做提醒的表,后面会讲。

条件格式里的公式,起始行必须和「应用于」范围的起始行对齐。范围从第 2 行开始,公式里就得写第 2 行。

03 交给 WorkBuddy,有两条路

这两样设置手工点不算难,难在你得记住每一个对话框在哪一层。让 WorkBuddy 做,有两条路,代价不一样。

做法
适合谁,代价是什么
让它直接改文件
快,一次到位,适合表结构已经定下来的。缺点是它写进去的规则你在界面上看不到细节,改口径时要重新交代一遍
让它给配置清单,你自己点
慢 5 分钟,但每一步都长在你手上,以后改起来知道从哪下手。适合这张表要用好几年

第一次配建议走后一条,配熟了一次性交给它

走第一条路,提示词要把它容易含糊的地方全部钉死:哪个 sheet、哪一列、起止行、公式原文、格式颜色。

▍发给 WorkBuddy 的提示词(文件拖进对话框,复制下面这段粘贴发送)

打开「员工花名册.xlsx」。里面有一张「参数」sheet,A2:A30 是部门,B2:B30 是合同类型,C2:C30 是员工状态。请先给这三个区域各建一个定义名称,分别叫 部门列表、合同类型列表、状态列表,然后停下来告诉我名称是否建好,其他什么都不要做。

它建完名称会回话,你再发第二段。

接着在「花名册」sheet 加三处数据验证,都用序列:C2:C500 来源 =部门列表,G2:G500 来源 =合同类型列表,J2:J500 来源 =状态列表。再加一条条件格式,作用于 A2:J500,公式 =AND($H2<>"",$H2-TODAY()<=30),格式为浅红填充,不改变字体颜色。做完给我三行回执:验证加在哪些范围、条件格式的公式原文、以及现在有几行会被标红。

最后那句「有几行会被标红」是我加的,它逼着 WorkBuddy 回头验一遍公式生效没有。数字对得上,你就不用逐格点开检查了。

04 四个配错的地方

范围从第 2 行起,公式里写了 $H$2。这是最常见的错,现象是全表变红或者一片不变,你会以为是颜色设错了。

忘了排除空值,整片空白区全变红。空单元格参与减法等于 0 减今天,结果是负几万,当然满足条件。AND 里那个 $H2<>"" 就是为这个写的。

在参数表里直接删掉了离职员工所在的部门。删行会让后面所有值往上窜一格,下拉内容整体错位,已经填好的数据又不会跟着改。正确做法是加一列「状态」,停用的部门标成停用,不删行。

以为下拉能修历史数据。数据验证只管新输入,之前手打的「销售事业线」还在那儿躺着。配完之后要单独跑一次清洗:把这一列的值按参数表做一次替换,替换不掉的列出来人工看。

05 三句实话

第一,我没在 500 人以上的花名册上完整跑过这套。表越大,条件格式越该只作用于实际数据行,不要图省事写成整列引用,翻页会明显卡。

第二,颜色只是提示,替代不了流程。标红的意思应该提前跟大家说清楚,是「两周内要启动续签」,不是「这个人的合同有问题」,否则会被理解成 HR 的失职标记。

第三,如果你的花名册已经在用在线表格,且多人同时编辑,数据验证的体验会比 Excel 本地差一些,跨表引用的下拉尤其容易失效。那种情况建议把参数表和数据放同一张 sheet 的下半部分。

你们公司花名册的「部门」一栏,现在有几种写法?评论区报个数。

这里是廖若水,每周实测一个 AI 工具。

回首页,搜索你想要的文档

在首页搜索框输入关键词,快速定位资料

文库首页搜索指引 立即搜索

相关学习资料