圣诞树 圣诞树 圣诞树_Excel圣诞树2015

圣诞树 圣诞树 圣诞树

It's only a week until Christmas Eve, and to help you celebrate the holidays, I have updated my Scroll Bar Christmas tree. No macros are needed, but this version uses customizable icon sets that are available in Excel 2010 and later versions.

离圣诞节前夕只有一个星期，为了帮助您庆祝假期，我更新了滚动条圣诞树。 不需要宏，但是此版本使用Excel 2010和更高版本中可用的可自定义图标集 。

This will be my last blog post until the new year, so happy holidays, and remember to cell-ebrate safely!

这将是我直到新年的最后一篇博客文章，节日快乐，并请记住安全地放松身心！

Excel圣诞树 (The Excel Christmas Tree)

Here is a screen shot of the updated Christmas Tree workbook.

这是更新后的圣诞树工作簿的屏幕截图。

This version uses the conditional formatting icons from the 2009 version, and the gifts and sequential tree trimming from the 2010 version.

这个版本使用条件格式图标， 从2009年的版本 ，以及礼品和顺序修剪树木从2010年的版本 。

链接图片 (Linked Picture)

In the 2015 version, there is a picture of Santa that appears at the end of the sequence. John Walkenbach suggested that in 2009, and better late than never!

在2015版中，序列末尾出现了圣诞老人的图片。 约翰·沃肯巴赫(John Walkenbach)建议，在2009年，迟到总比没有好！

This is a linked picture, that refers to the SantaPic named range. That range is an empty cell (J1), until the Spin Button reaches a setting of 59. After that, it shows the picture from cells J4:L12.

这是一个链接的图片，它引用了SantaPic命名范围。 该范围是一个空单元格( J1 )，直到“旋转按钮”达到59的设置为止。之后，它将显示来自单元格J4：L12的图片 。

=IF(Tree!$G$3>=59,Lists!$J$4:$L$12,Lists!$J$1)

= IF(树！$ G $ 3> = 59， 列表！$ J $ 4：$ L $ 12 ， 列表！$ J $ 1 )

条件格式的图标 (Conditional Formatting Icons)

On the tree, the lights, tinsel and star at the top all appear based on conditional formatting settings.

在树上，顶部的灯光，金属丝和星号均根据条件格式设置出现。

In a merged cell at the top, a Star icon appears if that cell's formula returns a 1. I don't usually use merged cells, but will make an exception for Christmas.

在顶部的合并单元格中，如果该单元格的公式返回1，则会显示一个星形图标。我通常不使用合并单元格，但是圣诞节会例外。

=IF($G$3>=VLOOKUP(ROW(),RowLU,3,TRUE),1,0)

= IF($ G $ 3> = VLOOKUP(ROW()，RowLU，3，TRUE)，1,0)

Then, using customized icon sets, a value of 1 or greater shows a star, and other values show no cell icon.

然后，使用自定义的图标集，值为1或更大的值将显示一个星星，而其他值则不显示任何单元格图标。

灯和金属丝 (Lights and Tinsel)

The conditional formatting for the tree lights and tinsel strings works the same way. A lookup table sets the values for each row number, with higher numbers appearing later in the sequence.

树灯和金属丝串的条件格式以相同的方式工作。 查找表为每个行号设置值，更高的数字出现在序列的后面。

=IF(VLOOKUP(ROW(),Lists!$F$2:$G$17,2,TRUE)>$G$3,0,(RAND()*100)+1)

= IF( VLOOKUP(ROW()，列表！$ F $ 2：$ G $ 17,2，TRUE)> $ G $ 3，0 ，(RAND()* 100)+1)

There are 3 possible tree ornaments/lights, and the RAND function is used to mix them up. I add 1 to the result, to ensure that the result is never zero.

有3种可能的树装饰/灯，并且使用RAND功能将它们混合在一起。 我将结果加1，以确保结果永远不会为零。

=IF(VLOOKUP(ROW(),Lists!$F$2:$G$17,2,TRUE)>$G$3,0,(RAND()*100)+1)

= IF(VLOOKUP(ROW()，列表！$ F $ 2：$ G $ 17,2，TRUE)> $ G $ 3,0， (RAND()* 100)+1 )

The tinsel strings use the Zero Filled Boxes icon.

金属丝串使用“零填充框”图标。

开始修剪树木 (Start the Tree Trimming)

There is a spin button on the worksheet, and you can click that to move up or down in the tree trimming sequence. The previous version had a scroll bar, but that was acting very flakey (getting stuck, disappearing, etc.), so I got rid of it.

工作表上有一个旋转按钮，您可以单击该按钮在树修剪序列中向上或向下移动。 以前的版本有一个滚动条，但是它表现得非常好处理(卡住，消失等)，所以我摆脱了它。

The spin button is linked to cell G3, and you can clear that cell if you want to start over. Or, if you're impatient, type 60 in that cell, to see the completed tree.

旋转按钮链接到单元格G3，如果要重新开始，则可以清除该单元格。 或者，如果您不耐烦，请在该单元格中键入60，以查看完整的树。

树修剪消息 (Tree Trimming Messages)

Just like the 2010 version, this tree has messages that appear, based on the Spin Button value. You can change the messages, or add new ones, in the table in the workbook.

与2010版本一样，该树根据“旋转按钮”值显示出现的消息。 您可以在工作簿的表中更改消息或添加新消息。

下载2015年圣诞树工作簿 (Download the Christmas Tree 2015 Workbook)

To see you the Christmas Tree works, you can download the sample file from my Contextures website. On the Excel Scroll Bar Christmas Tree page, go to the Download section, and get the 2015 version of the file. The zipped file is in xlsx format, and does not contain any macros.

若要查看圣诞树的工作原理，可以从Contextures网站下载示例文件。 在“ Excel滚动条圣诞树”页面上 ，转到“下载”部分，并获取文件的2015版本。 压缩文件为xlsx格式，不包含任何宏。

Happy Holidays!

节日快乐！

翻译自: https://contexturesblog.com/archives/2015/12/17/excel-christmas-tree-2015/

圣诞树 圣诞树 圣诞树