excel 魔法卡片
Yes, it's Valentine's Day today, and if you were too busy to buy your sweetie a card yesterday, you can make one in Excel. Phew!
是的,今天是情人节,如果您昨天忙于购买甜心卡,则可以在Excel中制作。 !
Your boss won't mind if you spend a couple of hours working on this today, because it's an Excel project! This Excel Valentine card uses a named range, data validation, a formula, and conditional formatting (to change the heart from white to pink to red).
您的老板不介意您今天是否花几个小时从事此工作,因为这是一个Excel项目! 这张Excel Valentine卡使用命名范围,数据验证,公式和条件格式(将心脏从白色变成粉红色再变成红色)。
If you won't have time, or if your drawing skills are worse than mine, you can download the sample Excel Valentine file, at the end of this blog post.
如果您没有时间,或者您的绘画技能比我的差,则可以在此博文末尾下载示例Excel Valentine文件。
And if you want some romantic music in the background, while you work on your Excel Valentine card, you can listen to the YouTube playlist, compiled by John Walkenbach and his blog readers.
而且,如果您想在后台播放一些浪漫的音乐,那么在使用Excel Valentine卡时,您可以收听由John Walkenbach及其博客读者编写的YouTube 播放列表 。
设置工作表 (Set Up the Worksheet)
To create the heart shape,
要创造心形,
- Start by making columns A:M narrower, to create square cells 首先将A:M列变窄,以创建方形单元格
- Then, add red fill colour to cells in rows 5:14, to create a heart shape 然后,向第5:14行的单元格添加红色填充颜色,以创建心形
Select the coloured cells, and name the range as Heart
选择有色单元格,并将范围命名为Heart
添加公式 (Add the Formula)
The formula will count how many text items have been added at the top of the worksheet, and the result is used for conditional formatting.
该公式将计算在工作表顶部添加的文本项的数量,并将结果用于条件格式。
- Select the Heart range 选择心跳范围
Type the following formula, then press Ctrl+Enter, to enter the formula in all the selected cells:
键入以下公式,然后按Ctrl + Enter ,在所有选定的单元格中输入公式:
=COUNTA($E$1:$E$3)
= COUNTA($ E $ 1:$ E $ 3)
添加条件格式 (Add Conditional Formatting)
With the Heart range still selected, set up the following conditional formatting:
- =1, light pink fill and font = 1,浅粉红色填充和字体
- =2, dark pink fill and font = 2,深粉红色填充和字体
- =3, red fill and font = 3,红色填充和字体
隐藏心 (Hide the Heart)
The heart shape will be hidden, and only revealed when the Valentine message is selected.
心形将被隐藏,并且仅在选择Valentine消息时才会显示。
To hide the heart:
隐藏心脏:
- Select the Heart range 选择心跳范围
- Format the cells with white fill and font. 用白色填充和字体格式化单元格。
添加数据验证下拉列表 (Add the Data Validation Drop Downs)
Next, you'll create three drop downs, for the Valentine message at the top of the worksheet.
接下来,在工作表顶部,为Valentine消息创建三个下拉菜单。
To prepare the cells for the drop down lists:
要为下拉列表准备单元格:
-
- Tip: After you merge E1:I1, drag the Fill Handle, to copy the formatting down to the next two rows. 提示:合并E1:I1后,请拖动“填充手柄”,将格式复制到下两行。
-
- Add a bottom border to each merged cell, with red or dark pink border colour. 为每个合并的单元格添加底边框,边框颜色为红色或深粉红色。
Create the following data validation drop down lists:
创建以下数据验证下拉列表:
- E1: I, You, Everyone E1:我,你,每个人
- E2: Love, Loves, ?, Heart, Hearts E2:爱,爱,?,心,心
- E3: You, Me, Excel E3:您,我,Excel
Tip: To type a heart shape, press Alt and type a 3 on the number keypad (if no number keypad, try Fn+Alt+L). On a Mac, another key combination might be needed.
提示:要键入心脏形状,请按Alt并在数字小键盘上键入3(如果没有数字小键盘,请尝试按Fn + Alt + L)。 在Mac上,可能需要另一个组合键。
使用Excel Valentine (Use the Excel Valentine)
The Excel Valentine heart has white fill and white font, so it's not visible. To see the heart:
Excel Valentine心具有白色填充和白色字体,因此不可见。 看心:
- Select one item from the drop down lists, to colour the valentine light pink 从下拉列表中选择一项,为情人节浅粉红色上色
- Select two items from the drop down lists, to colour the valentine dark pink 从下拉列表中选择两项,为情人节深粉色
- Select three items from the drop down lists, to colour the valentine red 从下拉列表中选择三个项目,将情人节红色
下载Excel Valentine Card (Download the Excel Valentine Card)
To see how the card works, you can download the Excel Valentine Card sample file.
要查看卡的工作方式,可以下载Excel Valentine Card示例文件 。
The file is in Excel 2007 format, and zipped, and it contains no macros. _____________
该文件为Excel 2007格式,并经过压缩,并且不包含宏。 ______________
翻译自: https://contexturesblog.com/archives/2011/02/14/make-a-valentine-card-in-excel/
excel 魔法卡片