Excel2000 应用案例之四十七

10.3  股票收益计算器

    在买卖股票的操作中，如何计算某个股票的收益有时是比较麻烦的，除了买卖股票的价格外，还要计算印花税、手续费、委托费等，如果再加上送股、配股和派息等，则更为复杂。利用Excel 2000制作一个股票收益计算器，可以使得上述工作大大简化。

10.3.1  公式的建立

    首先新建一个工作表，输入有关的数据。其中委托费、成交费等项各股票交易所不完全相同，可以根据实际数据输入。建立好的股票收益计算器如图10－23所示。

图10－23

    该工作表中最关键的是C8单元格中的内容，其内容是计算股票收益的公式：
    ＝F7*(1-B3-B4)-F3*(1+B3+B4)-F5+F6-(SIGN(F3)+SIGN(F7))*(B5+B6+B7)
    其中B3和B4分别是印花税和手续费，F7*(1-B3-B4)为卖出股票的收益（已扣除印花税和手续费）；F3*(1+B3+B4)为买入股票的支出（含印花税和手续费）；F5为配股的支出；F6为派息的收益；最后一项(SIGN(F3)+SIGN(F7))*(B5+B6+B7)为委托费、成交费和通信费，其中SIGN函数为符号函数，当F3或F7大于0时值为1，等于0时值为0。
    利用该计算器，只要输入买入和卖出股票的价格、数量，以及送、配股和派息数据，即可立刻计算出相应的收益。

10.3.2  窗体的应用

    上例的计算器虽已可以自动完成股票收益的计算，但是还存在一些不足。例如当用户输入股票价格时，如果忘记输入小数点，将会得到一个不着边际的计算结果。为此我们利用Excel 2000提供的滚动条、单选钮等多种窗体控件，对该计算器加以改造，防止其出现明显的错误，并使其操作更为方便。首先单击视图菜单中的工具栏命令，选定窗体工具栏。则工作表上将出现窗体工具栏，如图10－24所示。

图10－24

    先为买入股票数量等单元格添加滚动条控件（位于图10—24中第2行的第3个控件）。具体操作步骤如下：
    在数量单元格后面插入一列。
    单击窗体工具栏的滚动条控件。
    在E3单元格上拖拽出一个矩形。
    右击刚刚建立的滚动条控件；在弹出的快捷菜单中单击设置控件格式命令。将出现对象格式对话框。
    根据需要设置有关参数，这里设置当前值为1000，最小值为100，最大值为10000，步长为10，页步长为100。并指定单元格链接为D3（当改变控件值时，D3单元格的值相应改变）。如图10－25所示。
    类似地为送股、配股和卖出数量建立滚动条控件。
    为股票价格等单元格添加滚动条控件。
    建立股票价格的滚动条较股票数量的滚动条要复杂一些。因为滚动条变化的步长只能是整数，而价格可能需要按0.01元的步长变化。为此需要借助其它的单元格作为中间单元。例如指定G3单元格的滚动条控件与I3单元格链接，其值的变化范围为100～10000，步长为1，而在F3单元格中输入公式“＝I3/100”，即可实现当滚动条控件变化一个单位时，股票价格单元格F3能按0.01元的步长变化。类似地为配股价、派息和卖出价添加滚动条控件。

图10－25

    因为股市上还有多种基金可以买卖，而基金买卖时是不上印花税的，为此再为计算器添加两个单选钮控件，使其计算股票时，印花税为0.50%，而计算基金时，印花税为0。具体操作步骤如下：
    单击窗体工具栏中的单选钮控件（位于图10—24中第1行的第6个控件）。；
    在工作表中的适当位置拖拽出一个矩形；
    将建立的单选钮控件的名称改为“股票”；
    右击该单选钮控件，然后在弹出的快捷菜单中单击设置控件格式命令；
    在弹出的对象格式对话框中设置单元格链接等有关参数，这里设置其与I5单元格链接。
    按照类似的方法在股票单选钮旁边在建立一个基金单选钮，并使其也与I5单元格链接。
    这样当单击股票时，I5单元格的值为1，而单击基金时，I5单元格的值为2。然后还需要将单选钮的结果，即I5的结果与印花税单元格相连。为此，在J5和J6单元格分别输入0.005和0，而在印花税单元格B3输入公式“=INDEX(J5:J6, I5)”。这样，当选定股票时，I5单元格的值为1，相应的B3单元格的值将取J5:J6单元格区域的第一个值，即为0.005；而当选定基金时，I5单元格的值为2，相应的B3单元格的值则取J5:J6单元格区域的第二个值0。
    各个控件添加完毕后，再适当地重新组织各单元格区域，并进行一些修饰，完成后的计算器如图10－26所示。

图10－26

    该计算器比最初的要好用的得多。例如单击价格滚动条两端的滚动箭头，价格数据将会按0.01元的步长增加或减少；而单击价格滚动条（滚动块两侧），价格数据将会按0.10元的步长增加或减少；当需要快速增加或减少价格数据时，还可以直接拖拽滚动块。当计算的是基金时，只要单击基金单选钮，即可自动按基金的计算公式完成计算。

10.3.3  工作表的保护

    这时的计算器虽已可以方便地使用了，但是还有一点美中不足，就是如果使用者直接在某个公式单元格中键入数据，将会使得精心设计的控件失灵。更有甚者，如果使用者在总收益单元格中键入数据，将使整个计算器失效。为了防止以上问题的发生，还需要为该计算器加上必要的保护。其具体操作步骤如下：
    选定不需保护的单元格，例如买入、卖出、送配股的数量单元格，与买入、卖出、配股价格的滚动条链接的单元格；
    单击格式菜单中的单元格命令，在保护选项卡下，清除锁定复选框；
    单击工具菜单中保护命令的保护工作表子命令。将出现图10－27所示的保护工作表对话框。

图10－27

    从中选择需要保护的项，如果需要，还可以输入密码，单击确定。
    这样，工作表中除了刚才解除锁定的单元格都不能修改其内容，精心建立的公式和修饰的格式就都不会被破坏了。如果不按上述步骤操作，只是简单地执行保护工作表命令，则保护的工作表的所有单元格都不能改变，计算器就只能计算当前锁定的一种情况了。所以应将不是公式的且需要变化的单元格，都解除锁定，然后再对工作表设置保护。
    到此为止，股票收益计算器全部制作完成。读者在此基础上还可以根据需要进行各种改进。例如送、配股可以只输入百分比，然后自动计算相应的股数。还可以设置累加器，将多次买卖的结果累计，最后计算总的收益等等。