今天想简单设计一个模板,记录自己的学习记录,大致包含学习时间及内容等,里面用到了一些技巧,特别是表的技巧,写下来分享一下~
Excel中的表,不是工作表sheet,也不是单元格区域range,它可以通过快捷键Ctrl+T生成,或者在插入选项卡中插入表格,表在下拉菜单中应用的非常广泛。
1. 需求
回归正题,现在我要做一张记录表,样式如下:
其中E列学科分类:Python、R、统计学、Excel;内容分类:网络视频、微信文章、博文学习、写博文;
根据不同的内容分类,对应不同的学习历程,如下:
以上介绍完毕,我们来看制作过程。
2. 制作过程
2.1 生成一张表
选中A1:I3单元格区域,按Ctrl+T生成一张表,这时在左上角的菜单栏中可以输入表名称study_record,按enter键完成,此时可以套用表格样式等操作。
2.2 输入日期时间
可以通过快捷键Ctrl+;输入日期,Ctrl+Shift+;输入时间。
2.3 设置数据有效性
13版之前的Excel,称为数据有效性,13版及之后称为数据验证,其实是指的一个功能。
数据验证应用最多的场景就是下拉菜单,此外还可以做输入提示,限制输入(如其名,也非常常用)等功能。
2.1生成了study_record表,根据表的特性,我们在单元格中的公式及设置在列的维度上是自动扩展的,同时会自动套用表格式,不用再手工设置,非常省心。
先将学科分类和内容分类设置为表,分别命名为class和contents,如下:
然后选中study_record表中的E2:E3单元格区域,在数据选显卡→数据验证→设置:
确定即可,即可得到下拉效果:
因为我们使用了表class,所以当我们对学科类别进行增加或删除的时候,并不会影响数据有效性!比如我现在新增一个“数据库”的选项:
直接在M6单元格中输入“数据库”,不用更改任何设置,E列中的下拉菜单会自动更新:
同理,因为我们使用了表study_record,所以当我们新增学习记录的时候,我们设置的数据有效性也会自动扩展!
同样可以设置F列内容分类的数据有效性。
2.4 动态二级下拉菜单
如1. 中所述:不同的内容分类对应不同的学习历程,我们来制作一个二级下拉菜单,来实现以下效果:
即:选择不同的内容分类,后面对应不同的学习历程下拉菜单。
定义一个名称:xxlc:=OFFSET(study_process[[#标题],[内容分类]],MATCH(study_record[@内容分类],study_process[内容分类],0)-1,1,COUNTIF(study_process[内容分类],study_record[@内容分类]),1),因为使用了表的缘故,所以公式中的参数会自动变更为表的内容样式。
offset+match+countif是一个非常常用的嵌套组合!
定义好xxcl名称之后,设置G列的数据有效性:序列,值=xxlc,即可
当内容分类:学习历程更新了,G列的下拉菜单也会自动更新,如下所示:
以上,一个简单的学习记录表就制作完成了,因为使用了表,所以扩展性非常好,我们只需要更新如下所示自定义的分类,所有的有效性和下来菜单都会自动更新!
以上,感谢你长得这么好看还关注我的博客,也请各位多多指教,有疑问欢迎留言沟通,谢谢~