arrow_back

在 Google 表格中进行数据验证

登录 加入
访问 700 多个实验和课程

在 Google 表格中进行数据验证

实验 1 小时 universal_currency_alt 免费 show_chart 中级
info 此实验可能会提供 AI 工具来支持您学习。
访问 700 多个实验和课程

GSP1062

Google Cloud 自学实验的徽标

概览

在本实验中，您需要通读一个虚构的业务场景。通过完成各项实验活动，您将协助其中的人物使用 Google 表格。

on-the-rise-bakery-logo.png

为了传递对国际美食和传统烘焙食品的热爱，Thomas Omar 和 Seroja Malone 创办了一家小型家族企业 On the Rise Bakery。他们的面包店从纽约市起步，逐渐扩展到整个北美地区，如今已遍布世界各地。随着公司业务增长，他们招聘了员工来协助管理各个面包店的日常运营。

Google 表格提供了多种内置工具，可以帮助您对电子表格中的数据进行验证。

在本实验中，您将使用电子表格来设置面包店商品价格标签上打印的文本和数字的格式。您还将学习如何删除重复数据，并创建数据验证规则来帮助 On the Rise Bakery 向客户发送营销电子邮件。

目标

您将学习如何执行以下任务：

  • 对数据进行排序和过滤。
  • 设置数字和文本的格式。
  • 使用函数来清理数据。
  • 创建数据验证规则。

前提条件

如果您还不熟悉 Google 表格，建议您先学习以下课程：Google 表格、Google 表格 - 高级主题。

完成以下实验可能也会有所帮助：Google 表格：使用入门。

设置和要求

点击“开始实验”之前

请阅读以下说明。实验是计时的，并且您无法暂停实验。计时器在您点击开始实验后即会开始计时，并会显示 Google Workspace 资源可供使用的时间。

本 Google Workspace 实操实验可让您在真实的云环境中开展实验活动，免受模拟或演示环境的局限。我们会为您提供新的临时凭据，您可以在实验规定的时间内使用此凭据登录和访问 Google Workspace。

所需条件

为完成此实验，您需要：

  • 能够使用标准的互联网浏览器（建议使用 Chrome 浏览器）。
  • 时间。请注意左侧面板顶部显示的时间，这是预计完成所有步骤所需的时间。请安排好您的日程，留出足够的时间来完成本实验。实验一旦开始就不能暂停，也无法稍后继续（每次启动实验都会从第 1 步开始）。
  • 无需使用 Google Workspace 账号。在本实验中，我们将为您提供账号。
打开无痕式窗口以运行本实验。 如果在实验中收到登录控制台的提示，请仅使用我们提供给您的学生账号。这是为了进行活动跟踪，以便核实您是否已完成实验活动。

开始实验

  1. 准备就绪后，请点击开始实验。

    此时，您会看到“实验室详细信息”窗格，其中包含您在该实验中登录 Gmail 所需的临时凭据。

    如果该实验需要付费，系统会打开一个弹出式窗口供您选择付款方式。

  2. 点击打开 Google 表格。

    该实验会启动资源，并打开另一个标签页来显示“登录”页面。

    提示：请在不同的窗口中打开这两个标签页，并将它们并排显示。

  3. 如有必要，请复制下方的用户名，然后将其粘贴到登录对话框中：

    {{{user_0.username | "<用户名>"}}}

  4. 点击下一步。

  5. 复制下面的密码，然后将其粘贴到欢迎对话框中：

    {{{user_0.password | "<密码>"}}}

  6. 点击下一步。

  7. 根据提示接受所有条款及条件。

Google 表格随即打开，并且登录的是学生 Google 账号。

任务 1.将数据排序

在此任务中，您需要打开一个 Google 表格文件，并使用排序功能来整理数据。

复制电子表格

  1. 在实验说明的左侧面板中，右键点击打开 Google 表格，然后点击在无痕式窗口中打开链接，登录您的学员账号。

  2. 右键点击 On The Rise Bakery Customers and Items，然后点击在无痕式窗口中打开链接，打开电子表格。

  3. 依次点击文件 > 复制。

  4. 在模态窗口中点击复制，将表格副本保存到我的云端硬盘中。

对选定范围进行排序

在处理大型数据集时，对数据进行排序可以帮助您快速找到所需的单元格。Google 表格支持按字母顺序或数字顺序对数据进行排序。您可以按列对整个工作表进行排序，也可以只对选定范围内的单元格进行排序。

  1. 在电子表格的左下角，点击标签为 Items 的工作表，查看 On The Rise Bakery 的商品数据。

Items 工作表中

  1. 如需冻结第一行，请在顶部菜单栏中依次点击视图 > 冻结 > 1 行。

    冻结顶部行可以确保在对数据排序时表头保持不变。

  2. 如需按商品数量以升序排序，请右键点击 C 列的列标题，然后点击以 A 到 Z 的顺序对工作表排序。

注意：您也可以先对数据范围添加过滤器，然后在任意列上进行排序。在任务 2 中，您将学习更多关于过滤器的内容。

使用 SORT 函数

SORT 函数可以根据一个或多个列的值对电子表格中的行进行排序。使用 SORT 公式时，您需要提供以下参数：

  • range：要排序的数据。
  • sort_column：要排序的值范围。
  • sort_column：排序方向；TRUE 表示升序，FALSE 表示降序。

  1. 在电子表格的左下角，点击添加工作表 (+) 以添加另一个工作表。

  2. 右键点击新工作表的名称，点击重命名，然后输入 Items Sorted By Unit Price（按单价对商品进行排序）

  3. 在新工作表的 A1 单元格中，输入或粘贴以下公式：=SORT(Items!A1:Items!C15, Items!B1:Items!B15, FALSE)

    在此公式中，范围是 Items 工作表的 A1:C15，排序列是包含各商品单价的 B 列。FALSE 表示数据将按降序排序。

SORT 函数还支持按多个列进行排序。要使用此功能，只需要在公式末尾为每个额外的排序列添加对应的“排序列”和“升序”值。如需了解详情，请访问帮助中心。

注意：SORT 函数仅适用于排序后不需要更新的数据，因为在数据排序后修改单元格会导致 #REF! 错误。

点击检查我的进度，验证已完成以下目标： 将数据排序

任务 2.过滤数据

由于面包店的肉桂粉缺货，需要在不删除相关行的情况下，将肉桂面包从电子表格中暂时隐藏。使用过滤功能可以帮助您分析电子表格中的特定数据子集。在此任务中，您将创建一个过滤器和一个过滤视图，帮助 On The Rise Bakery 更新电子表格。

注意：创建过滤器会改变所有可以查看该电子表格的用户所看到的数据。过滤视图则会在电子表格中创建一个不公开的视图，您可以在其中进行过滤和排序，而不会影响其他协作者或改变原始数据。

创建过滤器

  1. 返回到 Items 工作表。

  2. 选中 A 列，然后在顶部菜单中依次点击数据 > 创建过滤器。

  3. 点击 A1 单元格中出现的过滤器图标 (filter-list) 来显示过滤条件选项。

  4. 将鼠标悬停在或点击菜单中的以下选项来了解可用的条件：

    • 按颜色过滤
    • 按条件过滤
    • 按值过滤

  5. 要过滤掉肉桂面包，请在按值过滤下取消选中 Cinnamon bun 旁边的对勾标记，然后点击确定。

点击检查我的进度，验证已完成以下目标： 创建过滤器

  1. 如需移除过滤器，请在顶部菜单中依次点击数据 > 移除过滤器。

创建过滤视图

  1. 选中 A1 单元格。

  2. 在顶部菜单中依次点击数据 > + 创建过滤视图。

  3. 如需过滤掉肉桂面包，请点击 A1 单元格中的过滤器图标，然后按照上述的步骤操作。

  4. 如需保存过滤视图，依次点击数据 > 视图选项 > 保存视图 > 保存。

请确保在创建过滤视图之前先选中相应的单元格。

点击检查我的进度，验证已完成以下目标： 创建过滤视图

  1. 如需移除过滤视图，请在顶部菜单中依次点击数据 > 视图选项 > 删除标签页中的所有视图。

任务 3.设置数据格式

Google 表格中的函数用于处理文本和执行计算。On The Rise Bakery 需要使用 Google 表格中的数据来创建货架价格标签。在此任务中，您将使用函数来设置数据格式并为打印标签做准备。

截断数字

  1. 在 E1 单元格中，输入 Truncated Unit Prices（截断后的单价）

    10 donut holes 的单价显示了小数点后五位数字，而其他商品只显示了四位。您可以使用截断函数来限制显示的小数位数。

  2. 在 E2 单元格中，输入或粘贴以下公式：=TRUNC(B2, 4)

  3. 点击单元格右下角的蓝色小方框，然后按住向下拖动光标来将公式应用到该列中的其余单元格。

设置货币格式

  1. 如需计算每个菜单单品的总成本，请在 D2 单元格中输入或粘贴以下公式：=B2*C2

    此公式会将单价乘以商品数量。

  2. 如需将总成本四舍五入到小数点后两位，请将 D2 单元格中的公式改为：=ROUND(B2*C2, 2)

  3. 将该公式应用到 D 列中的其余单元格。

  4. 如需将数字设置为货币格式，请点击 D 列的灰色列标签来选中整列，然后在顶部菜单中依次点击格式 > 数字 > 货币。

    Google 表格中的货币格式选项还可以用来调整小数点后显示的位数，并添加货币符号。与之类似，工具栏中的减少小数位数选项 (decrease-decimal) 可用来减少显示的小数位数，而不会改变单元格中的原始数据。

在输入本实验中的公式时，您可能注意到了自动填充列中剩余行的选项。如需了解详情，请参阅自动创建数列或列表。

设置文字格式

On The Rise Bakery 还需要为餐饮服务菜单中的产品说明设置正确的大小写格式。

  1. 如需插入新列，请右键点击 B 列标签，然后点击在左侧插入 1 列。

  2. 在 B1 单元格中，输入 Formatted Name（格式化名称）

  3. 如需将每个食物单品名称的格式设置为每个单词首字母大写，请在 B2 单元格中输入或粘贴以下公式：=PROPER(A2)

  4. 将此函数应用到 B 列中的其余单元格。

点击检查我的进度，验证已完成以下目标： 设置数字和文本格式

任务 4. 验证数据

On The Rise Bakery 需要确保客户邮寄名单中只包含有效的电子邮件地址。在此任务中，您将创建一个数据验证规则，用于检查所有输入的电子邮件地址格式是否正确。

使用函数来验证数据

  1. 在 Customers 工作表中，点击 D2 单元格，然后在顶部菜单中依次点击插入 > 函数 > 信息 > ISEMAIL。

  2. 在公式的括号内输入 C2，最终公式应为：=ISEMAIL(C2)

  3. 将此公式应用到该列中的其余行。

使用规则来验证数据

  1. 选中单元格范围 D2:D100，然后在顶部菜单中依次点击数据 > 数据验证 > + 添加规则。

    此操作会打开一个显示数据验证规则选项的边栏。应用范围字段中应该已经自动填入了 Customers!D2:D100。

  2. 在条件旁边的下拉菜单中点击文本包含。

注意：您也可以使用数据验证规则来检查单元格中的文本是否是有效的电子邮件地址或网址。
  1. 在输入框中输入 True，然后点击完成。

您创建的验证规则应如下所示：

completed-data-validation-rule

Google 表格中的数据验证规则可以帮助您限制工作表单元格中可以输入的值。您可以为工作表创建一个或多个数据验证规则。一般来说，对于工作表中每一个需要限制用户输入值的列，您都需要定义一个单独的数据验证规则。

检验您的掌握情况

点击检查我的进度，验证已完成以下目标： 使用函数和相应的规则来验证数据

  1. （可选）尝试使用 ISDATE 或 ISNUMBER 函数来创建数据验证规则。在 E 列中输入示例数据，然后在 F 列中对数据进行验证。

任务 5.清理数据

在此任务中，您将帮助 On The Rise Bakery 通过删除重复的行和重复项来清理数据。

突出显示重复项

On The Rise Bakery 还需要确认是否存在一个电子邮件地址关联了多个客户的情况。

  1. 在顶部菜单中依次点击格式 > 条件格式。此操作会打开一个显示条件格式规则选项的边栏。

  2. 在应用范围中，输入 C1:C100

  3. 在格式规则下找到符合以下条件时为单元格应用格式选项，选择自定义公式为。

  4. 在值或公式中，输入或粘贴以下公式：=COUNTIF(C:C,C1)>1

    您可以将单元格、行或列的格式设置为在满足特定条件时自动更改文本或背景颜色。此公式用于检查某个电子邮件地址在该列中是否出现了多次。

  5. 在填充颜色 (format-color-fill.png) 中选择红色。

  6. 点击完成。

去除空格

On The Rise Bakery 希望在电子邮件中用客户的名字来称呼他们。

  1. 在 G1 单元格中，输入 Personalized Greeting（个性化问候语）

  2. 在 G2 单元格中，输入或粘贴以下公式：=CONCATENATE("Hello ", A2, ",")，然后将该公式应用到该列中的其余单元格。

    CONCATENATE 函数可以帮助您组合多个文本字符串。在本例中，公式将 “Hello”、客户姓名和逗号组合在一起。但是，G2 单元格的显示效果与列中其他单元格不一样。

  3. 要去除空格，请将 G2 单元格中的公式更新为：=CONCATENATE("Hello ", TRIM(A2),",")

    TRIM 函数可以删除文本中的前导空格、尾随空格和重复空格。同时使用 TRIM 和 CONCATENATE 函数可以确保个性化问候语的格式正确无误。

移除重复项

  1. 选中 C 列，然后在顶部菜单中依次点击数据 > 数据清理 > 移除重复项。

  2. 点击扩展到 A:D。

  3. 在移除重复项对话框中，勾选数据具有标题行，并在要分析的列下点击全选。

  4. 点击移除重复项。

  5. 注意已移除的重复行数量，然后点击确定或 X 关闭对话框。

注意：在完成此任务的过程中，您可能注意到了一些清理建议或列统计数据。如需了解详情，请参阅使用 Google 表格智能清理功能准备数据以进行分析。

点击检查我的进度，验证已完成以下目标： 清理数据

恭喜！

您已经成功地为价格标签上的文本和数字设置了格式。您还学习了如何移除重复项，并创建了数据验证规则。

后续步骤/了解详情

您可以查看以下内容，了解有关 Google 表格的更多信息：

Google Cloud 培训和认证

…可帮助您充分利用 Google Cloud 技术。我们的课程会讲解各项技能与最佳实践，可帮助您迅速上手使用并继续学习更深入的知识。我们提供从基础到高级的全方位培训，并有点播、直播和虚拟三种方式选择，让您可以按照自己的日程安排学习时间。各项认证可以帮助您核实并证明您在 Google Cloud 技术方面的技能与专业知识。

上次更新手册的时间：2024 年 5 月 29 日

上次测试实验的时间：2024 年 5 月 29 日

版权所有 2025 Google LLC 保留所有权利。Google 和 Google 徽标是 Google LLC 的商标。其他所有公司名和产品名可能是其各自相关公司的商标。

准备工作

  1. 实验会创建一个 Google Cloud 项目和一些资源，供您使用限定的一段时间
  2. 实验有时间限制，并且没有暂停功能。如果您中途结束实验，则必须重新开始。
  3. 在屏幕左上角，点击开始实验即可开始

使用无痕浏览模式

  1. 复制系统为实验提供的用户名和密码
  2. 在无痕浏览模式下，点击打开控制台

登录控制台

  1. 使用您的实验凭证登录。使用其他凭证可能会导致错误或产生费用。
  2. 接受条款，并跳过恢复资源页面
  3. 除非您已完成此实验或想要重新开始，否则请勿点击结束实验，因为点击后系统会清除您的工作并移除该项目

此内容目前不可用

一旦可用，我们会通过电子邮件告知您

太好了！

一旦可用，我们会通过电子邮件告知您

一次一个实验

确认结束所有现有实验并开始此实验

使用无痕浏览模式运行实验

请使用无痕模式或无痕式浏览器窗口运行此实验。这可以避免您的个人账号与学生账号之间发生冲突，这种冲突可能导致您的个人账号产生额外费用。