中企动力 > 商学院 > excel创建模板
  • ?

    1个案例教你学会Excel中的VBA

    Geoffrey

    展开

    可能很多朋友不知道Excel中VBA的用途。那什么是VBA呢?

    一句话概括:在Excel中想实现什么功能,就可以用VBA语言编写一段程序去完成。

    今天通过例子带大家了解Excel中的VBA!

    软件说明:Excel 2010版本

    场景再现:表格中点击按钮,向单元格E1中输入数字 “233”,并且能够实现清除!

    第一步、打开编写VBA代码的窗口

    在Excel表格中编写VBA的窗口叫VBE编辑器,有两种打开的方法。

    在工作表标签上右键 - 查看代码按Alt+F11

    第二步、创建写VBA代码的地方

    有朋友肯定会问:“我没学会编程怎么办?”、“我都没见过代码能行吗?”;别急!我的回复是肯定的!

    在哪编写代码呢?有好几种方式,今天先学最常用的:“插入” - “模块”。新建一个模块用来存放编写的代码;记住:修改模板的名称:我的VBA模板【如下图虚线标注】

    第三步、开始写代码

    新建模板后,在右侧的空白位置就是编写代码的地方。可以执行的VBA代码,结构是这样的:

    Sub 程序名(参数)可以执行任务的代码End Sub

    因为例子中我要进行输入与清除的操作,输入Sub 程序名()然后回车,End Sub就会自动输入;如下图:

    在开始和结束语句之间输入代码,执行在单元格A1中输入数字"233"

    Range("E1") = 123

    备注:在VBA中,Range("单元格地址")来表示单元格。在单元格中输入值,直接用=值 即可(字符串两边要加双引用),如果清空则 =""。

    再编写一段清空代码:

    第四步、测试运行代码

    在编写代码时经常要测试是否正确。测试方式是把光标放在代码行的任意位置【下图中“测试按钮”的位置】,点击运行小按钮进行测试;同样清空代码也一样。

    第五步、点击按钮执行VBA代码

    在Excel中插入的图形、图片、按钮控件都可以执行VBA代码。让它们执行很简单,点击“插入” - “矩形” - “指定宏”

    只需要右键菜单中点击指定宏 - 选取编写的宏名称,选择“输入”,添加输入按钮。

    同样添加清除按钮:

    当然自己可以调整字体的颜色、字体,让其变得更加美观!

    此时,通过VBA设置的输入“233”,并可以点击清除!

    第六步、保存VBA代码

    此时的VBA代码应该是既有输入代码又有清空代码;最后需要保存为“启用宏的工作簿"类型【注意下图中虚线框标注】,VBA代码才能保存下来。

    好了!今天的分享就到这里,是不是感觉VBA也没有想象的那么难?

    赶快转发、关注吧,更多技巧尽在头条号中!

  • ?

    Excel工资表制作要点介绍

    鲸鱼

    展开

    我们常见的工资表,其中包含的管理项目主要有工号、职工姓名、基本工资、职务工资、福利费、住房基金、应发工资、个人所得税和实发工资这九大项。今天蚂蚁小编给大家讲讲,Excel工资表怎么做。

    1、创建工资表的管理项目

    打开Excel,创建一个工资表,也就是把以上那一组的项目构成表格的上表头内容罗列在首行。而在上表头各管理项目中,“工号”项及“职工姓名”项是这一组管理项目中的关键字段,通常应当作“左表头”字段名,排列于表格区域的左侧第一列与第二列位置其它管理项目依次向右排列。

    2.工资表的运算关系设置

    表格的结构一旦确定,下一步将是为表格中各管理项目建立运算关系。针对该工资表,需要设置运算式的区域包括应发工资、个人所得税实发工资这三项,其中:

    (1)应发工资的计算方法:应发工资包括基本工资、职务工资、福利费等。公式可以先设置在“应发工资”字段下方的G5单元格中,然后使用拖拉复制法将此公式复制给其它的“应发工资”单元格中,算法G5=C5+D5+E5+F5,此公式的含意为第一行工资记录中的“应发工资”=基本工资+职务工资+福利费+住房基金(前提是住房基金的数据应以负值方式输入)。

    (2)实发工资的计算方法:实发工资是工资表中“应发工资”与“个人所得税”项目之差。公式可以先设置在“实发工资”字段下方的I5单元格中,然后使用拖拉复制法将此公式复制给其它“实发工资”的单元格中,算法I5=G5-H5。

    (3)“个人所得税”的计算方法:是按国家规定,超过一定收入后应向国家上缴的税金值。此值按超过部分的多少而有九个级别的标准,这里就不详细介绍。

    3.测算表格的运行状态

    为确保上述各管理项目的运算式创建正确,原则在设置运算式后,应填入一组实际数值,并检验。

    4.表格的修饰

    为使工资表在填写与管理中显示得更直观易读,通常需要对表格进行修饰。如对表格外观的修饰和数字格式的修饰。

    5.数据保护区的设置

    在工资表的管理中需要对表中数据进行保护设置,除要经常性手工填入数据的区域:工号、职工姓名、基本工资、福利费和住房基金各列(即此区应开放)外,其他所有单元格均需设置保护。操作:

    (1)拖拉选择上述一组数据区,如B5:F16即“职工姓名”至“住房基金”字段下面的数据区,使其显示为反白状态。

    (2)在此区内单击鼠标右键,显示快速命令列表。

    (3)选择“设置单元格格式”命令,显示“单元格格式”对话框。

    (4)单击“保护”标签,进入保护格式选项页。

    (5)将其中“锁定”复选框中的勾取消。

    (6)最后,单击对话框中的“确定”按钮。

    但一定要使保护操作得以实现,原则上必须对此保护操作进行加密。加密的目的在于:填表人即表格保护设置人以外的其他人,不能修改表格的结构,只能在允许的位置输入数据。为“表单”加密(设置口令)的操作如下:

    (1)单击“工具”菜单并选择“保护”命令,显示二级菜单。

    (2)再选择“保护工作表”命令,显示“保护工作表”对话框。

    (3)在对话框中的“密码”区输入保护此表单的口令,如“”aaa”(口令通常以“*”号显示)。按下“确定”按钮,将显示“确认密码”对话框。

    (4)在“确认密码”对话框中,通过“重新输入密码”区再次输入保护口令“aaa”,并按下“确定”按钮,以便确认口令的正确性。

    此后,工资表文件中的相应表单,就被保护起来。

    6.保存为模板文件

    在“另存为”对话框中的“保存类型”列表中选择“模板”选项。同时,文件的保存位置自动从“My Documents”区切换到“Template”区,然后在“文件名”区确认此模板的名称就可以。

    本文由蚂蚁工资条整理发布,转载请注明出处。

    蚂蚁工资条可将Excel工资表自动生成工资条,并以短信、邮件或微信的形式发送给员工。使用蚂蚁工资条,将极大地提升HR的工作效率,也能减少与员工的沟通成本,促进员工满意度的提升。

  • ?

    不想工作生活复杂,试试这6种电子表格模板,第一种收入很有帮助

    乐安

    展开

    虽然有大量的应用程序可以帮助您实现财务自动化,但有些人更喜欢老式的电子表格方法。电子表格是一种出色的金融工具,因为您可以输入您的财务目标和数字,并允许电子表格完成剩余的工作。我们针对每个预算需求收集了一系列免费电子表格模板。

    1.收入追踪

    作为一个小企业主,Single Moms Income的博主Alexa Mason需要一个电子表格来跟踪她的所有业务收入和支出。所以她创建了收入跟踪器电子表格。虽然您可以轻松更换订单项以满足您的业务需求,但此电子表格对赚取收入的博主尤其有用。

    2.每周预算模板

    想要进一步细分每月预算吗?试试免费的每周预算模板。这个模板可以帮助您快速了解每周的支出情况,让您了解可以减少的消息。(另请参阅:通过这个简单的预算系统提高您的储蓄)

    3.大学生预算

    大学生的预算需求与成人世界的其他人不同。如果您是大学生,您可以使用Mint的免费电子表格下载,在一个方便的地方跟踪您的所有费用,学费和收入。

    4.假期支出预算

    开始准备假日季节预算永远不会太早。你知道你会有人去购物,如果你等到最后一刻,你的预算很快就会变成一团糟。幸运的是,您可以使用Microsoft Office中的此电子表格计算所有假期成本。

    5.旅行电子表格

    计划在不久的将来打开公路或前往海外?请看看Lifehacker的免费旅行电子表格。它可以帮助您跟踪您的确认号码,日程安排和紧急联系信息,此外,您还可以添加所有费用,以便全面了解您的旅行预算。(另请参阅:如何制定最佳旅行预算)

    6.婚礼预算

    婚礼总是花费相当一分钱,但为重要日子制定预算目标并找到明智的储蓄方式可以让你在发出誓言之前不会陷入债务危机。

  • ?

    Excel必备技巧之做一个漂亮的数据表格

    项荧荧

    展开

    昨天我们介绍了如何来设置excel的默认字体以及字体大小的问题,晚点的时候,有朋友问我为什么设置不生效,昨天的教程里面少介绍了一步,我们做的修改,是修改的excel的默认模板,在修改完成之后,选择新建的excel模板的方式是有技巧的。

    在桌面或者文件夹内点击鼠标右键,选择新建->XLSX工作表,这样新建的文件打开之后,还是系统默认的字体以及字体的大小,并不是我们设置的。如下图:

    右键新建XLSX文件

    打开新创建的excel文件,输入字体,我们会发现,字体的样式还是宋体,大小还是10号,如图:

    字体样式以及大小并未改变

    2. 正确的方式是打开excel软件之后,点击选项卡右侧 + 号右下方的下拉按钮,在弹出的选择项目中,选择从默认模板新建,如图:

    从默认模板新建xlsx文件

    新建excel文件之后,我们在单元格里面输入一些内容来查看效果。

    现在默认的字体以及大小已经更换

    3. 我们在新建的时候,如果之前有一个是用默认模板创建的excel表格,可以直接点击 + 按钮直接创建,这样创建的也是以默认模板创建的。

    从默认模板创建

    简单来讲,把鼠标放到+按钮上,如果显示的是“从默认模板新建”那么就是正确的,如果显示的是“新建”,那么就是系统默认的字体样式以及大小。

    下面我们讲下如何对Excel表格进行美化,我们新制作一个excel的数据文件,文件样式如图所示:

    初始excel数据文件

    我们的目的是将此excel表格进行美化,这个表格的特点是标题栏、正文单元格里面的文字是同样的字体、同样的颜色,每个行之间也都是同样的色调,不符合我们的审美的观点,我们的目的是将标题栏的背景、标题栏字体进行调整,同时因数据行较多,让数据行的颜色隔行显示比较好,同时我们给excel表格增加筛选按钮,如此多的调整,我们只需要两秒钟即可搞定,下面我们来介绍下具体操作。

    第一步我们全选工作表,全选工作表的快捷键是 CTRL+A 。

    全选工作区域

    2.第二步我们使用快捷键CTRL+T对excel进行美化,这时选择“表包含标题”以及“筛选按钮”两个选项

    对表格进行美化

    3. 第三步,查看结果,美化后的效果是相当明显,首先标题栏的字体大小、颜色以及单元格背景都做了调整,而且增加了筛选按钮,数据行都做了隔行变色。

    美化后的结果

    上面我们介绍了对Excel表格进行美化的方法,主要使用了 CTRL + A 、CTRL+T 两个快捷键,感兴趣的朋友可以打开个excel表格试验下。

  • ?

    奇妙的VBA代码:如何根据需要创建EXCEL工作簿文件

    zzzzzz

    展开

    各位好,今日我们讲VBA代码的第三十一讲,如何新建EXCEL工作薄文件。对于工作薄的解释,我们在之前的文章中已经讲解过,要注意工作薄和工作表的概念。工作薄是文件,工作表是工作薄的工作页。在VBA中使用Add方法新建工作簿。

    一: ADD方法:使用Workbooks对象的Add方法创建新工作簿,其语法为:Workbooks对象.Add(Template).参数Template可选,确定如何创建新工作簿。如果该参数指定的字符串是已有的Excel文件名,那么使用指定的文件作为模板创建新工作簿。如果该参数指定为xlWBATemplate常量之一,则新工作簿包含单个指定类型的工作表。如果忽略该参数,那么新工作簿包含标准的空工作表,可以使用SheetsInNewWorkbook属性设置工作表数。xlWBATemplate常量为:xlWBATChart,xlWBATExcel4IntlMacroSheet,xlWBATExcel4MacroSheet,xlWBATWorksheet。分别代表图表工作表、宏工作表以及标准工作表。

    二 下面我们来详细讲解一下如何创建工作薄文件,如下面的代码所示。

    Sub MyAddNowbook()

    Dim Nowbook As Workbook

    Dim ShName As Variant

    Dim Arr As Variant

    Dim i As Integer

    Dim myNewWorkbook As Integer

    myNewWorkbook = Application.SheetsInNewWorkbook

    ShName = Array("余额数", "单价数", "数量", "金额数")

    Arr = Array("1月", "2月", "3月", "4月", "5月", "6月", "7月", "8月", "9月", "10月", "11月", "12月")

    Application.SheetsInNewWorkbook = 4

    Set Nowbook = Workbooks.Add

    With Nowbook

    For i = 1 To 4

    With .Sheets(i)

    .Name = ShName(i - 1)

    .Range("B1").Resize(1, UBound(Arr) + 1) = Arr

    .Range("A2") = "品名"

    End With

    Next

    .SaveAs Filename:=ThisWorkbook.Path & "\" & "库存.xls"

    .Close Savechanges:=True

    End With

    Set Nowbook = Nothing

    Application.SheetsInNewWorkbook = myNewWorkbook

    End Sub

    代码解析:MyAddNowbook过程使用Add方法建立新的工作簿并对新建工作簿进行操作。

    a) 第2行到第6行代码声明变量类型。b) 第7行代码保存Excel自动插入到新工作簿中的工作表数目。c) 第8、9行代码将数组元素赋值给变量。d) 第10行代码将Application对象的SheetsInNewWorkbook属性设置为4,在新建工作簿时插入4张工作表。e) 第11行代码使用Add方法建立新的工作簿,应用于Workbooks对象的Add方法新建工作簿,新建的工作簿将成为活动工作簿。f) 第12行到第22行代码操作新建工作簿。g) 第15行到第17行代码将新建工作簿的工作表进行重命名并给单元格赋值。h) 第20行代码使用SaveAs方法将新建工作簿重命名为“库存.xls”保存在同一目录中。i) 第21行代码使用Close方法关闭工作簿。j) 第24行代码恢复工作簿的默认设置。

    三:实测代码, 代码窗口如下:

    运行后:新添了库存文件。

    库存文件的内容:

    今日内容回向:

    1 ADD方法的意义是什么?

    2 Range("B1").Resize(1, UBound(Arr) + 1) = Arr 是什么意义?

  • ?

    你知道Excel里一对多筛选模板如何设置吗?

    Zhong

    展开

    以下是公司的销售细节,现在如果你想根据购买单位单独打印清单要如何操作。

    效果如下面的图所示

    怎么做?看下面的具体步骤。

    1、首先准备客户列表表格,将其插入超级表格,选择A2:A9单元格,将名称定义为“客户列表” 。

    由于超级表具有自动扩展区域的功能,因此在将客户列表应用于超级表之后,增加或减少客户列表可实现动态数据可用性。

    2、设计这样的表格。

    3、在单元格C3中创建一个下拉菜单

    将鼠标放在C3单元格中,单击数据验证(或数据有效性),从下拉菜单中选择序列,然后输入刚刚定义的名称“= 客户名单”。

    A1表格输入公式= C3&“销售明细”;,选择A1:F1,单元格格式对齐以列为中心

    4、在数据源A列中插入一列辅助列,输入公式=C2&"_"&COUNTIF(C$1:C2,C2)

    公式COUNTIF(C $ 1:C2,C2)计算指定区域中C $ 1:C2中指定条件的单元格C2,这相当于为每个公司分配一个数字,以便每个公司出现的次数是唯一值。

    5、返回筛选模板表,在A列的列号中输入公式=IF(ROW(A1)>COUNTIF(数据源!C:C,C$3),"",ROW(A1))

    数据源

    如果ROW(A1)行号大于C3客户端列表出现在数据源的C列中的次数,则为空,否则返回ROW(A1)行号。

    6、在B2 = IFERROR中输入公式(VLOOKUP($ C $ 3&" _"& $ A6,数据源!$ A:$ F,COLUMN(B:B),0),“"”右拉落下

    最后,F3输入公式= SUM(E:E)&"元"

    7,模板制作完成后,您只需在筛选模板C3单元格的下拉菜单中选择客户即可完成客户在数据源中的过滤。

    当客户需要增加时,只需在超级表格后添加新客户名称。

  • ?

    如何简单快速地在Excel中制作基本的甘特图?包你两分钟学会!

    精灵王

    展开

    提到甘特图,一般大家都会想到用Project来做,但是还要安装Project、学习使用Project,就有点打退堂鼓了。不用担心,在Excel中照样可以做出甘特图的,无需额外安装任何插件,而且十分简单,一学就会!

    首先,我们要准备甘特图用到的项目计划基础数据:

    1

    基础数据中包含了项目分解出来的各项任务的名称,以及每项任务的开始日期、持续天数和结束日期,其实之后创建甘特图时,我们并不需要使用“结束日期”,这里仅仅作为信息参考,提醒大家在做基础数据时,别忘了考虑刨开休息日哦。

    另外,日期格式一定要正确,2018.1.1、2018~1~1之类的格式,Excel并不认识!选中输入日期的某个单元格,CTRL+1,选择数字页签,点击“分类”下的“日期”,看看右侧的“类型”列表中的那些个格式,这些就是Excel认识的日期格式:

    2

    还要提下,日期在Excel中实际对应一个数字,常规数值的1个单位代表1天,“开始日期”+“持续天数”=“结束日期”,即结束日期可以直接使用公式:=B2+C2,试试看吧。

    好了,下面我们要正式制作甘特图了:

    1、 选中A1:C11区域,插入堆积条形图,并调整条形图的大小:

    3

    2、 对条形图的数据源进行设置,添加图例项,修改水平(分类)轴标签,以在图表中显示出各项任务,以及对应的开始日期、持续天数数据条:

    4

    3、 将垂直(类别)轴设置为“逆序”,即按时间顺序从上到下排列各项项目任务:

    4、 设置水平(值)轴的范围和间隔,即项目的整个持续周期,以更清楚地显示甘特任务条:

    6

    注意:这里将边界的最小值修改为“43101”,对应的就是第一个任务的开始日期,选中第一个开始日期,CTRL+1,选择分类为“常规”,即可看到这个日期对应的数值:

    7

    5、 将橙色的开始时间数据条进行隐藏:

    6、 最后,我们还可以对甘特图进行进一步的美化,在持续天数的数据条上显示任务名称和持续天数、修改数据条的颜色等等,这步就看大家的个人喜好了:

    OK,至此,我们的甘特图就已经做好了,是不是很简单?你学会了吧:)

  • ?

    怎么用excel制作表格

    西门悒

    展开

    1、打开EXCEL软件,新建EXCEL空白文件,也可以根据模板创造建。创建完成,制作表头即通常“标题”,然后制作表的内容,对表进行美化。

    2、操作详解如下所述。

    1新建“空白工作簿”2点击“视图”3将视图设置成“面面布局”4根据视图的大小制作“标题”5制作表的内容,并进行美化;完成后保存。

    (本文内容由百度知道网友344169037贡献)

  • ?

    Excel图表也能模板?10秒也能做出好看的图表

    浮华

    展开

    一、制作一份漂亮的Excel图表

    制作一份图表并不难,最难的是图表配色以及各种细节的处理。

    根据左边的数据创建一份柱形图10秒钟,而要做出一份漂亮的图表,要几分钟。有没办法,以后创建的柱形图都使用右边的图表模板?

    Step 01选择图表,右键,另存为模板。

    Step 02 选择区域,插入图表模板。

    Step 03 调整图表的宽度,以及修改图表的标题即可搞定。

    如果已经创建好了图表,如何将模板的样式运用到默认的图表呢?

    其实,简单到你不敢相信,只需借助Ctrl+C即可搞定。

    不知道没关系,看动画。

    二、快速给工作表命名

    现在,一起来看看如何用Excel中的数据透视表快速给工作表命名。

    Step 01 创建数据

    Step 02 创建数据透视表

    将鼠标定位在数据中,在“插入”选项卡下选择“数据透视表”:

    将“月份”拖到“筛选器”:

    Step 03 报表筛选页

    将鼠标定位在数据透视表中,选择数据透视表工具下的“分析”选项卡,点击图示位置的“选项”——显示报表筛选页

    在弹出的对话框中,点击确定:

    睁大眼睛看:奇迹发生了!

    是不是突然就多了12张按每个月份命名的工作表?

    但是,好像不对,每张表的左上角还有数据透视表存在着?

    Step 04清除格式

    按住Shift选中第一张表格和最后一张表格,点击图示左上角位置:

    在“开始”选项卡下面选择“清除”——“全部清除”:

    一下子就清除12张工作表中掉数据透视表的筛选页啦~

    End.

    作者:卢子

    来源:Excel不加班/office女神

  • ?

    「Excel实用技巧」你会用模板批量创建并命名Excel文件吗?

    毒药

    展开

    前言:原在一家集团公司工作,经常给下属几十家公司发各种表格模块。如果不会简便方法,就需要手工一个个的复制文件,然后改名。

    一定要用VBA吗?不用!

    【例】有N个分公司,需要用模板.xlsx 文件创建excel表格放在 当前文件夹中。(其他类型文件操作相同)

    操作步骤:

    1、在表格中用公式生成代码

    ="copy 模板.xlsx "&C2&".xlsx"

    注:在模板.xlsx后有一个空格,别少了。

    2、把生成的代码复制粘贴到新建的txt文件中,然后修改为bat后辍。

    见证神奇的时刻到了,双击bat文件,所有分公司模板已自动生成。

    Excel解释说明:批处理命令对于不懂编程的用户来说,是一个很好用、易用的工具。如果同学们觉得有用就点心支持,将继续分享批处理命令的神奇用法。

所有视频需要登录后,才能观看

请先登录您的帐号,即可完整播放,如果您尚未注册帐号,请先点击注册。