欢乐生肖

新闻动态

《HR数据更高效 EXCEL体验营》讲座活动精彩回顾

发布时间:2019-11-08 点击数:275


如今,办公软件已成为每个职场人都不可或缺的操作技能


作为财务人士需要每个月给员工核算相对应的工资


作为HR人士需要每个月给员工制作考勤


…………


所以在10月22日的OFFICE公益系列讲座活动中


徐老师就结合了日常工作中涉及到的Excelz知识,向大家讲解且演示了最实用、最易操作的Excel技能操作知识。


DOUYOUNAXIEJINENGFENXIANGNI?

XULAOSHIZAI JINENGZHISHIDIANDEJIANGJIESHANGTIAOLIQINGXI,NEIRONGCHONGSHI,LUOJIZHENMI,XINXIFENGFU;BINGQIEZAIXIZHITIEXINDEJIANGJIEZHONGXIANGXUEYUANCHUANSHOUGAOXIAODEXUEXIFANGSHI,QIANGHUAJINENGYINGYONG。 


1、RENSHISHUJUHEDUI

ZAIRENSHIHUOCAIWUGONGZUOZHONGYOUSHIYAOHEDUILIANGZHANGSHUJUDEYITONG,RUGUOZHIYIGEYIGEHEDUIXIANRANHENMAFAN,ERQIERENGONGHEDUIJIQIRONGYICHUCUO,WEILETIGAOZHUNQUELVHEGONGZUOXIAOLV,

用重复值功能标注

SHOUXIANJIANGLIANGGEBIAODEDUIBILIEFANGZAITONGYIGEGONGZUOBIAOZHONG,XUANZELIANGLIE(KUAFANWEIZEXUANDIERLIESHIANCtrlJIAN),RANHOUDIANJIKAISHI→TIAOJIANGESHI→TUCHUXIANSHIDANYUANGEGUIZE→ZHONGFUZHI

使用合并计算核对

SHIYONGHEBINGJISUANHEDUINENGGOUQINGCHUDEKANDAOTONGYIGONGHAOZAILIANGGEYUEZHIJIANDEBIANHUA,CAOZUOBUZHOUWEISHOUXIANJIANGLIANGBIAOXINGMINGTIQUFANGZAIYIQI,BINGQIETIANJIAYUEFENLIE,XUANZESHUJU→HEBINGJISUAN→HUIZONGFANGSHIXUANFANGCHA→TIANJIALIANGGEYUEFENDESHUJUYUAN。

2、GESHUIJISUANHEBIAOGESHEJI

根据个税表创建的几个公式

SHIYONGIFHANSHU

欢乐生肖 SHIYONGMAXHANSHU

欢乐生肖 SHIYONGDUIZHAOBIAO+VLOOKUPHANSHU


欢乐生肖 3、JIAQIHECHUQINJISUAN

日期函数

NIANYUERIXINGQIHANSHU

=TODAY()DEDAOJINTIANDERIQI

欢乐生肖 =YEAR(A1)TIQUCHURIQIDENIAN

欢乐生肖 =MONTH(A1)TIQURIQIDEYUE

欢乐生肖 =DAY(A1)TIQUCHURIQIDERI

欢乐生肖 =WEEKDAY(RIQI,2)YONGSHUZILAIBIAOSHIXINGQI

QIANHOUMOUGERIQI

=EOMONTH(A1,X)A1RIQIxGEYUEHOUDEYUEFENZUIHOUYITIAN

欢乐生肖 =EDATE(A1,X)XGEYUEHOUDERIQI

欢乐生肖 ZHU:XKEYISHIFUSHU,BIAOSHIZHIQIAN

工作日函数

欢乐生肖 JISUANLIANGGERIQIJIANDEGONGZUORI

=NETWORKDAYS(KAISHIRIQI,JIESHURIQI)

欢乐生肖 TIANJIAJIAQI

欢乐生肖 =NETWORKDAYS(KAISHIRIQI,JIESHURIQI,JIAQI)

欢乐生肖 KAOLVDIAOXIU

=NETWORKDAYS(KAISHIRIQI,JIESHURIQI,JIAQI)+COUNTIFS(SUOYOUDIAOXIURIQI,“>=”&KAISHIRIQI,SUOYOUDIAOXIURIQI,“<=”&JIESHURIQI)


欢乐生肖 4、XINCHOUYUCHENGBENDONGTAIFENXIBAOGAO

透视表+动态按钮

欢乐生肖 SHOUXIANANZHAOTOUSHIBIAOCHANGGUIBUZHOUCHUANGJIANHUIZONGBIAO,RANHOUZAITOUSHIBIAOJICHUSHANGCHARUQIEPIANQI。

添加关键数据的图标效果

欢乐生肖 XUANZESHUJU→TIAOJIANGESHI→TUBIAOJI

核心数据提取显示

CHARUXINGZHUANGHUOWENBENKUANG→DIANJIWENBENKUANGHOUDIANJIBIANJILAN→SHURULIANJIEDANYUANGEDEGONGSHI,RUGUOSHITOUSHIBIAO,XUYAOJIEZHUGUODUDANYUANGE。

根据透视表创建透视图

欢乐生肖 DIANJITOUSHIBIAOCHARUTUBIAO(HUOZAITOUSHITUGONGNENGQUCHUANGJIANTOUSHITU)

你学会了吗

下期公益活动预告


NISHIFOUYOUDALIANGZHONGFUXINGDESHOUGONGSHUJUGONGZUOXUYAOZHENGLI?


欢乐生肖 NISHIFOUMEICIZUOBAOGAOSHIDOUYAOMIANLINBIANGENGGEZHONGXUQIU?


NISHIFOUYEXIANGRANGZIJIDESHUJUHUIJIANGJINGCAIDEGUSHI?


………


POWER BIJIUKEYIBANGNIJIEJUEYISHANGWENTI


欢乐生肖 RUGUONIYIJINGZAIYONGExcelLE,ZAIZHANGWOPOWER BIDEHUA


欢乐生肖 JIUSHILAOHUCHASHANGCHIBANGLE


欢乐生肖 RUGUONIYEXIANGRANGZIJIFEIQILAI,NEIZHEILIYOUGEPOWER BIDEMIANFEIGONGYIKECHENG,WOQUANNIHAISHILAIBA


NIHUIXUEDAOSHENMENI?

讲座主题

ExcelSHUJUZHENGLIYUJIANMOFENXIJIPower BIKESHIHUACHENGXIAN


讲师

欢乐生肖 WEIRUANZUIYOUJIAZHIZHUANJIA(MVP)—ZHAOBAOHENGLAOSHI


时间

欢乐生肖 2019NIAN11YUE23RI(ZHOULIU)13:30-17:00


地点

地点南山区高新南一道TCL大厦B座309