excel 条件格式数据条_Excel条件格式的天气数据

excel 条件格式数据条

At work, we use Excel for serious projects, like financial reports or marketing forecasts. Excel is useful at home too, for personal tasks. I keep daily notes, and I added conditional formatting for weather data, to make the columns easier to read.

在工作中，我们使用Excel处理重大项目，例如财务报告或市场预测。 Excel在家里也很有用，可以完成个人任务。 我会保留日常笔记，并添加了天气数据的条件格式，以使列更易于阅读。

格式化天气数据 (Weather Data With Formatting)

Here's a screen shot of my conditional formatting for weather data. It's easy to see the warmer and cooler days, and what the sky was like each day.

这是我的天气数据条件格式的屏幕截图。 很容易看到温暖和凉爽的日子，以及每天的天空。

I wanted to see what last October was like, and we had a couple of hot sunny days back then!

我想看看去年十月的情况，那时我们有几个炎热的晴天！

获取天气 (Get the Weather)

I get the daily temperature and weather conditions from the Government of Canada Weather page. Click any city, province or territory on that map, to see the current conditions and the forecast.

我可以从“加拿大政府天气”页面获取每日温度和天气状况。 单击该地图上的任何城市，省或地区，以查看当前状况和预报。

Near the top of the City page, there's a forecast, with the maximum temperature and weather conditions.

在“城市”页面顶部附近，有一个天气预报，其中包含最高温度和天气条件。

At the bottom of the page, you can find the actual maximum from the previous day. That's the number that I store in my weather log, along with my own description of the conditions.

在页面底部，您可以找到前一天的实际最大值。 那就是我存储在天气日志中的数字，以及我自己对状况的描述。

If you're a real weather nerd, there's daily or historical data to download too. We won't go down that rabbit hole though! Well, not today.

如果您是一个真正的书呆子，那么也可以下载每日或历史数据。 但是我们不会掉下去的！ 好吧，今天不行。

天气状况 (Weather Conditions)

Besides the weather log sheet, there's another sheet in my Excel file – Admin_Lists. That sheet has two named Excel tables – one for weather types, and one for weather descriptions.

除了天气日志表外，我的Excel文件中还有另一个表-Admin_Lists。 该工作表有两个命名的Excel表 -一个用于天气类型，一个用于天气描述。

The weather types table is named tblWType, and it has 2 columns.

天气类型表名为tblWType，它有2列。

• Types are listed in the first column

  类型列在第一栏中

• Colours are shown in the second column. These are for info only. We can't use them in the conditional formatting rules, unfortunately.

  颜色显示在第二栏中。 这些仅供参考。 不幸的是，我们不能在条件格式规则中使用它们。

The weather descriptions table is named tblWeather, and it also has two columns.

天气描述表名为tblWeather，它也有两列。

• The weather descriptions that I use are in the first column.

  我使用的天气描述在第一栏中。

• A weather type for each description is selected in the second column.

  在第二列中为每个描述选择一种天气类型 。

命名范围 (Named Ranges)

There are also 3 named ranges on the Admin_Lists sheet:

Admin_Lists工作表上还有3个命名范围 ：

• WthTypeMain – Items in the Wtype column, in the Weather Types table

  WthTypeMain –天气类型表中Wtype列中的项目

• WthDescList – Items in the WthDesc column, in the Weather Descriptions table

  WthDescList –天气描述表中WthDesc列中的项目

• WthTypeList – Items in the WthType column, in the Weather Descriptions table

  WthTypeList –天气描述表中WthType列中的项目

天气类型下拉 (Weather Type Drop Down)

In the WthType column, in the Weather Descriptions table, there are drop down lists, created with data validation.

在“天气类型”表中的“ WthType”列中，有使用数据验证创建的下拉列表 。

The list is based on the named range – WthTypeMain.

该列表基于命名范围– WthTypeMain。

天气记录表 (Weather Log Sheet)

On the WeatherLog sheet, there's one named table – tblWthLog.

在WeatherLog表上，有一个命名表– tblWthLog 。

• Date is typed in column 1

  在第1栏中键入日期

• A TEXT formula shows the day name in column 2:   =TEXT(A4,"ddd")

  TEXT公式在第 2列中显示日期名称 ：= TEXT(A4，“ ddd”)

• Maximum temperature is typed in column 3

  最高温度在第3栏中键入

• Weather description is selected in column 4

  在第4栏中选择了天气描述

• The weather drop down is based on the named range – WthDescList

  天气下拉列表基于命名范围– WthDescList

温度条件格式 (Temperature Conditional Formatting)

In the Temp column, I used colour scale conditional formatting. Here's how to set that up:

在“临时”列中，我使用了色标条件格式 。 设置方法如下：

• In the Weather Log table, click at the top of the Temp column heading, to select all the temperatures (not the heading)
  在“天气日志”表中，单击“温度”列标题顶部的，以选择所有温度(而不是标题)
• On the Excel Ribbon's Home tab, click Conditional Formatting
  在“ Excel功能区”的“主页”选项卡上，单击“条件格式”
• Point to Color Scales
  指向色阶
• Click on the Red-White-Blue color scale
  单击红色，白色，蓝色色标

There's a video at the end of this post, that shows another example of using a color scale for temperatures.

这篇文章的结尾有一个视频 ，显示了另一个使用色标作为温度的示例。

检查色阶结果 (Check the Color Scale Results)

After you apply the conditional formatting color scale, scroll through the weather log, to see the results.

应用条件格式的色标后，滚动浏览天气日志以查看结果。

Instead of trying to read a long list of numbers, you can just check for dark red or dark blue cells, to find the highs and lows in the Temp column.

无需尝试读取一长串数字，您只需检查深红色或深蓝色单元格，即可在“温度”列中找到高点和低点。

天气条件格式设置规则 (Weather Conditional Formatting Rules)

It takes a bit more work to set up the conditional formatting rules for the Weather column.

为“天气”列设置条件格式设置规则需要花费更多的工作。

There are 4 weather types – Sun, Cloud, Rain, and Snow – and we'll need a separate rule for each of those types.

有4种天气类型-太阳，云，雨和雪-每种类型我们都需要一个单独的规则。

In the rule, we'll use an INDEX/MATCH formula, to find the weather type for each description.

在规则中，我们将使用INDEX / MATCH公式来查找每个描述的天气类型。

We'll test the formula on the worksheet first, before creating the conditional formatting rule.

在创建条件格式设置规则之前，我们将首先在工作表上测试公式。

Type this formula in cell F4 on the WorksheetLog sheet:

在WorksheetLog工作表的F4单元格中键入以下公式：

=INDEX(WthTypeList, MATCH(D4, WthDescList,0))

= INDEX(WthTypeList，MATCH(D4，WthDescList，0))

Then, drag the formula down a few rows, to see the results. It finds the weather type for each description in column D.

然后，将公式向下拖动几行以查看结果。 它在D列中找到每个描述的天气类型。

Leave the formula in column F for a few minutes, while you create the conditional formatting rules. You can clear those cells later.

在创建条件格式设置规则时，将公式保留在F列中几分钟。 您可以稍后清除这些单元格。

设置规则 (Set Up the Rules)

Follow these steps to set up the first rule, for Sun:

请按照以下步骤为Sun设置第一条规则：

• On the worksheet, copy the INDEX/MATCH formula from cell F4
  在工作表上，从单元格F4复制INDEX / MATCH公式

• In the Weather Log table, click at the top of the Weather column heading, to

  在“天气日志”表中，单击“天气”列标题顶部的

  select all the description cells (not the heading)

  选择所有描述单元格(而不是标题)

• NOTE: Cell D4 will be the active cell – you can see its address in the Name Box, to the left of the Formula Bar
  注意：单元格D4将是活动单元格–您可以在公式栏左侧的名称框中看到其地址。
• On the Excel Ribbon's Home tab, click Conditional Formatting, then click New Rule
  在Excel功能区的“主页”选项卡上，单击“条件格式”，然后单击“新建规则”
• Under "Select a Rule Type", click on "Use a formula to determine which cells to format"
  在“选择规则类型”下，单击“使用公式确定要格式化的单元格”
• Click in the Formula box, and press Ctr+V to paste in the INDEX/MATCH formula
  在“公式”框中单击，然后按Ctr + V粘贴在INDEX / MATCH公式中

• Click at the end of the formula, and type: ="Sun"

  单击公式末尾，然后键入： =“ Sun”

• Click the Format button, and click the Fill tab
  单击格式按钮，然后单击填充选项卡
• Click the fill colour that you want for sunny days
  单击您想要晴天的填充色
• Click OK, twice, to apply the conditional formatting
  单击确定两次，以应用条件格式

In the Weather column, the Sun/cloud days show the colour that you selected. Those rows also show "Sun" in the temporary formulas in column F.

在“天气”列中，“星期日/阴天”显示您选择的颜色。 这些行还在F列的临时公式中显示“ Sun”。

再创建3条规则 (Create 3 More Rules)

Next, follow the same steps, to create 3 more rules, for the other weather types:

接下来，按照相同的步骤，为其他天气类型再创建3条规则：

Cloud (light grey)

云(浅灰色)

• =INDEX(WthTypeList, MATCH(D4,WthDescList,0))="Cloud"
  = INDEX(WthTypeList，MATCH(D4，WthDescList，0))=“云”

Rain (dark grey)

雨(深灰色)

• =INDEX(WthTypeList, MATCH(D4,WthDescList,0))="Rain"
  = INDEX(WthTypeList，MATCH(D4，WthDescList，0))=“雨水”

Snow (light blue)

雪(浅蓝色)

• =INDEX(WthTypeList, MATCH(D4,WthDescList,0))="Snow"
  = INDEX(WthTypeList，MATCH(D4，WthDescList，0))=“雪”

The Weather column should show the colours that you selected for each weather type.

天气列应显示为每种天气类型选择的颜色。

For example, here's the delightful weather that we had last February. We were certainly glad to see a bit of sun on February 23rd!

例如，这是我们去年二月的宜人天气。 我们当然很高兴在2月23日看到一点阳光！

You can clear the temporary formulas from column F now. They were just there as "helpers" while creating and checking the rules.

您现在可以清除F列中的临时公式。 在创建和检查规则时，他们只是作为“帮助者”而已。

查看所有规则 (See All the Rules)

To see all the rules that you've set up, follow these steps:

要查看已设置的所有规则，请按照下列步骤操作：

• On the Excel Ribbon's Home tab, click Conditional Formatting, then click Manage Rules
  在Excel功能区的“主页”选项卡上，单击“条件格式”，然后单击“管理规则”。
• At the top of the Conditional Formatting Rules Manager, select "This Worksheet"
  在条件格式规则管理器的顶部，选择“此工作表”

The Rules Manager shows a list of the rules set for the active sheet.

规则管理器显示为活动工作表设置的规则的列表。

• The Graded Color Scale rule is in the list, and applies to cells in column C.
  渐变色阶规则在列表中，并应用于列C中的单元格。
• The INDEX/MATCH formula rules are listed too, and they apply to cells in column D.
  INDEX / MATCH公式规则也已列出，它们适用于D列中的单元格。

TIP: To see the full formula for a rule, point to it in the list of rules. A popup appears, to show the full rule.

提示 ：要查看规则的完整公式，请在规则列表中指向它。 出现一个弹出窗口，显示完整规则。

获取样本文件 (Get the Sample File)

To get the completed sample file with the conditional formatting for Weather Data, go to the Conditional Formatting Examples page on my Contextures site.

要获取具有天气数据条件格式的完整示例文件，请转到Contextures网站上的“条件格式示例”页面 。

The zipped file is in xlsx format, and does not contain any macros.

压缩文件为xlsx格式，不包含任何宏。

视频：温度色标 (Video: Temperature Color Scale)

This video shows another example of using a conditional formatting color scale to highlight low and high temperatures.

该视频显示了另一个使用条件格式色标突出显示低温和高温的示例。

See the setup details for this example.

请参阅此示例的设置详细信息 。

演示地址

翻译自: https://contexturesblog.com/archives/2019/10/24/excel-conditional-formatting-for-weather-data/

excel 条件格式数据条