Excel基础与应用 罗新密 30580-0011

EXCEL基础及应用,第十一章股票收益分析,第十一章股票收益计算,案例说明,知识点,案例制作,1,2,3,知识拓展,4,11.1案例说明,由于股票收益的计算方法比较复杂，很难像银行存款利息那样处理。,利用Excel进行股票收益管理，为股票交易提供科学的依据。,利用Excel打造自己的“股票投资分析软件”,11.2知识点,11.2.1工作表的建立和基本操作,11.2.2有效性条件的建立,11.2.3单变量求解,11.2.1工作表的建立和基本操作,增加工作表在当前工作表标签“sheet1”上用鼠标右键单击，选择【插入】命令，弹出【插入】对话框，在【常用】选项卡的列表中选择【工作表】，单击【确定】，在sheet1前边出现新的工作表，默认的名称为：Sheet4。删除工作表可以在”sheet3”上用右键单击，选择【删除】，可以看到原来的标签不见了。,11.2.2有效性条件的建立,请在【允许】框中，单击【整数】或【小数】。在【数据】框中，单击所需的限制类型。例如，若要设值上限或下限，请单击【介于】。输入允许的最大值、最小值或特定值。,11.2.3单变量求解,如果已知单个公式的预期结果，而用于确定此公式结果的输入值未知，则可使用“单变量求解”功能。单变量求解是通过调整另一个单元格中的值，从而可求得指定单元格中的特定值的方法。,11.3案例制作,11.3.1建立交易工作表,11.3.2股票买入计算公式,11.3.3股票卖出计算,11.3.4股票盈亏计算,11.3.5收益率计算,11.3.1建立交易工作表,一、打开一个空白的Excel工作表，在第一行输入“股票收益计算”；第2行各单元格输入列标记。,图11-1新建工作表,11.3.1建立交易工作表,二、鼠标双击工作表标签，将其更名为“股票收益计算”，然后在工作表标签上单击鼠标右键，打开快捷菜单并执行如图11-2所示的。,图11-2：设置工作表标签颜色,11.3.1建立交易工作表,三、打开“设置工作表标签颜色”对话框，在“工作表标签颜色”列表中选择“红色”，如图11-3所示，设置完成后单击【确定】按钮。,图11-3选择颜色,11.3.1建立交易工作表,四、删除sheet2和sheet3两个工作表。单击sheet2，选择【删除】，同样方法删除sheet3，如图11-4所示,图11-4删除工作表,11.3.2股票买入计算公式,计算公式买入金额=买入价买入数量+股票交易印花税+交易佣金+5(1)股票交易印花税=买入价买入数量印花税率（2）交易佣金=买入价买入数量印花税率(3)注意：公式（1）中的“5”则是买入股票需要支付的手续费。,11.3.2股票买入计算公式,一、在C2、D2单元格分别输入“佣金率”和“印花税率”，然后在C3、D3单元格输入当前佣金率0.003和印花税率0.002,图11-5买入股票的总金额,二、再次选中G3单元格，输入公式“=E3*B3*C3+E3*B3*D3+5”,11.3.2股票买入计算公式,输入股票的买入价格和数量，就可以计算出本次交易需要支付的买入成本，如图11-6所示,图11-6多种股票的买入金额,1.3.3股票卖出计算,一、在H3单元格输入公式“=F3*B3*C3+F3*B3*D3+5”二、在K3单元格，输入公式“=F3*B3-H3,图11-7股票的卖出金额,1.3.3股票卖出计算,通过复制公式可以得到其他股票的卖出金额，如图11-8所示。,图11-8多种股票的卖出金额,11.3.4股票盈亏计算,一、选中图11-8中的L3单元格，在其中输入公式“=K3-J3”。二、可以选中L列单元格，点击【单元格】、【数字】、【分类】、【数值】选项，然后选中“负数”列表框中的负数格式，如图11-9所示。,图11-4删除工作表,11.3.4股票盈亏计算,只要计算出的交易结果是亏的，其结果都会用红色数字显示。如图11-10所示。,图11-10设置红色格式,11.3.5收益率计算,收益率”就是股票的卖出收入与买入投资的百分比，我们在图11-11的M3单元格输入“=L3/J3”,图11-11股票收益计算,11.4知识拓展,股民有必要知道支付交易成本之后，买卖收支相抵时的卖出底价是多少。下面介绍Excel解决这一问题的两种方法，一是单变量求解工具，二是直接利用公式计算。,11.4.1设定求解目标,选中K5单元格，点击【工具】|【单变量求解】菜单命令，打开“单变量求解”对话框。根据上面确定的求解目标，“卖出金额”与买卖成本至少要相等，所以“目标值”至少是“56816.5”点击【可变单元格】框，然后选中F5单元格(其中存放着卖出价)，如图11-12所示。,图11-12单变量求解,11.4.2显示求解结果,在单变量求解的各项参数确定以后，点击【确定】按钮会出现相应的对话框，说明单变量求解已经找到了符合约束条件的结果。,图11-13单变量求解结果显示,11.4.3股票保本价计算公式,“参考保本价=买入价格+(买入成本+卖出成本)/股票交易数量”，操作时在I3单元格输入公式“=E3+(G3+H3)/B3”即可。