INDEX和MATCH跨两个表:详细说明

布雷特埃里克
布雷特埃里克 ✭✭✭✭
编辑11/09/21 最佳实践

大家好,希望能得到一些关于本教程的反馈。

我正在回答一个用户关于INDEX和MATCH的问题(线程发布在这里),并认为将我的例子分享给更大的Smartsheet社区将是有益的,该社区介绍了如何轻松地利用INDEX和MATCH函数,通过使用共享的“标识符”值从另一个表中引用单元格。

我找不到任何关于如何创建第二个工作表范围的工作表引用,然后展示如何利用INDEX/MATCH从这些范围获取信息的真正循序渐进教程的来源。

本教程包括一个逐步的例子,如何创建一个工作表作为“数据源”工作表,以及第二个工作表用于更“公共”的视图。这里的想法是让数据在源表中有一个“家”,在另一个表中有一组可选择的列(当你不想共享时,这很有用)所有表格中的数据)。

----

文档让你更有悟性:

下面是来自Smartsheet的索引和匹配的文档。

下面,我将尝试解释使用INDEX MATCH的外行方式——我建议使用INDEX/MATCH函数而不是VLOOKUP,因为如果你的数据移动了(例如,如果移动了一行,VLOOKUP可能会“中断”),INDEX/MATCH函数会更有帮助。

对于您的用例,您将使用Smartsheet所称的“交叉表引用”。你可以在这里阅读交叉表格公式(有一个视频):https://help.smartsheet.com/learning-track/smartsheet-advanced/cross-sheet-formulas

----

由于帖子的字符限制,我将添加我的解决方案作为这篇讨论帖子的几个回应。

如果这个答案回答了你的问题,请按上面的“是”-它有助于社区(和那些随机的谷歌人在那里)找到像你这样的解决方案更快。

布雷特埃里克;您友好的社区自由顾问和Smartsheet助手。

❓需要更多帮助吗?想要自动化智能表之外的系统以连接到您的智能表?想让您的报表自动化,从而更容易地相互交谈吗?其他问题吗?给我发邮件在LinkedIn上和我联系.‎

«1

评论

  • 例子:A Tale of Two Sheets

    对于这个例子,我们将有两个表,每个表都有相同的信息,减去“公共”表的一列。

    • 我们将第一张纸命名为companyes_private
    • 第二张表格将命名为“Companies_Public”。

    下面是“companyes_private”表中的数据。粗体和斜体文本=列名。

    ____________________________________________________________

    Companies_Private表:

    复制/粘贴数据在自己的工作表,如果你想:

    列:

    公司‎股票│网站│CEO

    3米‎‎‎‎‎‎‎‎‎‎‎‎‎‎‎ ‎ ‎ ‎3 m.com‎‎‎‎‎‎‎‎迈克罗马

    Adobe‎‎‎‎‎‎‎‎‎‎‎‎‎‎‎‎adobe.com‎‎‎‎‎Shantanu Narayen

    字母‎‎‎‎‎‎‎‎‎google‎ ‎ ‎ ‎google.com‎‎‎‎‎Sundar Pichai

    亚马逊‎‎‎‎‎‎‎‎‎‎amazon‎ ‎ ‎ ‎amazon.com‎‎‎安迪雅西

    苹果‎‎‎‎‎‎‎‎‎‎‎‎apple‎ ‎ ‎ ‎apple.com‎‎‎‎‎蒂姆•库克

    百思买‎‎‎‎‎‎‎‎‎‎百思‎‎‎‎‎‎bestbuy.com‎ ‎ ‎ ‎Corie巴里

    Etsy‎‎‎‎‎‎‎‎‎‎‎‎‎‎ETSY‎‎‎‎‎Etsy.com‎‎‎‎‎‎‎Josh Silverman

    惠普‎‎‎惠普‎‎‎‎‎‎HP.com‎‎‎‎‎‎‎‎恩里克传说

    ____________________________________________________________

    用户:“image.png”

    因此,对于我们的第二个工作表,我们将命名为“companyes_public”,我们只需要一个具有相同列的工作表,减1(在这个例子中,我们将删除“CEO”列——假设您不想让特定用户看到这一列)。

    ____________________________________________________________

    Companies_Public表:

    集团 ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ 股票 ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ 网站 ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎

    ____________________________________________________________

    因此,要使其工作,您需要利用INDEX MATCH函数并创建一些“引用”到“Private”表“公共”表。

    如果这个答案回答了你的问题,请按上面的“是”-它有助于社区(和那些随机的谷歌人在那里)找到像你这样的解决方案更快。

    布雷特埃里克;您友好的社区自由顾问和Smartsheet助手。

    ❓需要更多帮助吗?想要自动化智能表之外的系统以连接到您的智能表?想让您的报表自动化,从而更容易地相互交谈吗?其他问题吗?给我发邮件在LinkedIn上和我联系.‎

  • 嗨,布雷特,

    对Smartsheet非常陌生。

    我想使用这种技术复制列从一个Smartsheet到另一个作为“复制行”选项,接管更多的信息比我想要的。

    这是可能的吗?如果可以的话,您能更详细地为我解释索引匹配吗?

    谢谢!

  • Hi Brett,<\/p>

    Very new to Smartsheet.<\/p>

    I want to use this technique to copy columns over from one Smartsheet to another as the 'copy rows' option, takes over more information than i would like.<\/p>

    Is this possible, and if so, can you explain the index match in simpler detail for me?<\/p>

    Thanks!<\/p>","bodyRaw":"[{\"insert\":\"Hi Brett,\\nVery new to Smartsheet.\\nI want to use this technique to copy columns over from one Smartsheet to another as the 'copy rows' option, takes over more information than i would like.\\nIs this possible, and if so, can you explain the index match in simpler detail for me?\\nThanks!\\n\"}]","format":"rich","dateInserted":"2021-11-11T13:51:19+00:00","insertUser":{"userID":140477,"name":"Daniel Gill 97","url":"https:\/\/community.smartsheet.com\/profile\/Daniel%20Gill%2097","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ZN_qGWpEbPM!bLEoW15ZTEs!24FD2KcI1MS","dateLastActive":"2021-12-03T14:52:27+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/310662#Comment_310662","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/310662#Comment_310662

    我认为它说的新添加行,如果你可以覆盖它会回答我的问题!

  • 布雷特埃里克
    布雷特埃里克 ✭✭✭✭
    编辑11/11/21

    你好在那里@Daniel吉尔97谢谢你的评论。

    就“复制行”自动化而言,没有办法简单地复制特定的单元格值从一个“工作表”到另一个工作表。换句话说,你不能选择你想要的列。要么全部照搬,要么什么都不照搬。

    因此,您可以复制行,然后将您不想要的所有列移动到工作表的最右边,然后选择它们并隐藏它们。这不是一个很好的解决方案,但它会起作用。

    其他人之前也问过你同样的问题,但是在Smartsheet中并没有直接的方法:https://community.smartsheet.com/discussion/69496/copy-rows-automation-only-certain-columns

    在我学习INDEX / MATCH的时候,这里有一个很有用的教程:https://www.youtube.com/watch?v=cabxWacMhKQ.它展示了如何在工作表上使用INDEX/MATCH,以及如何使用它引用另一个工作表。

    ----

    但是…

    然而,有很多方法可以让你做你想做的事情。如果你感兴趣,你只需要使用Smartsheet API。基本上,这个API允许用户做定制的操作,就像你想要的,甚至更多(在每个工作表中添加一个列,每天晚上自动隐藏列,等等)。

    我利用微软的电力自动化(类似于ZapierAutomate.io等)来实现您想要的自定义自动化。如果你感兴趣的话,我可以教你怎么做。

    我目前正在工作的Smartsheet咨询自由职业业务,所以我正在寻找一些证明。这样我就可以免费安装了。

    但是,为了让我设置它,您需要订阅其中一个自动化服务。如果你的组织使用Microsoft 365,你可能已经通过你现有的许可证访问了Power automation -检查这里,看看你是否可以使用它:https://powerautomate.microsoft.com/en-us/

    让我知道!

    如果这个答案回答了你的问题,请按上面的“是”-它有助于社区(和那些随机的谷歌人在那里)找到像你这样的解决方案更快。

    布雷特埃里克;您友好的社区自由顾问和Smartsheet助手。

    ❓需要更多帮助吗?想要自动化智能表之外的系统以连接到您的智能表?想让您的报表自动化,从而更容易地相互交谈吗?其他问题吗?给我发邮件在LinkedIn上和我联系.‎

  • 她名叫Stara
    她名叫Stara ✭✭✭✭✭✭

    @Daniel吉尔97

    我希望你平安无事!

    为了补充布雷特的建议/回答。

    您可以使用交叉工作表公式结合VLOOKUP或INDEX/MATCH结构来连接工作表,当您更新源工作表时,它将反映在目标工作表上。

    另一种选择是使用所谓的助手表。简而言之,将行复制到帮助表中,然后使用我前面描述的方法将需要的值复制到另一个帮助表中,然后将该行从该工作表复制/移动到主目标工作表中。

    工作/帮助吗?

    我希望这对你有帮助!

    注意安全,祝你度过美好的一周!

    最好的

    她名叫Stara|工作流顾问/ CEO @工作大胆

    我的帖子是否帮助或回答了你的问题或解决了你的问题?请支持社区通过将其标记为有洞察力/投票支持或/和作为公认的答案.这将使别人更容易找到解决方案或帮助回答!

    Smartsheet专家顾问和合作伙伴

    她名叫Stara|工作流顾问/ CEO @工作大胆

    W:www.workbold.com| E:(电子邮件保护)| p: +46 (0) - 72 - 510 99 35

    如果您需要Smartsheet、集成、一般工作流建议或其他方面的帮助,请随时与我联系。

  • @Brett埃里克-我对Smartsheet很新,想知道我是否可以使用索引匹配函数来做以下-

    在包装单上的测试栏中—如果包装单上的打印机与打印机交货周期表中的打印机相匹配,则从打印机交货周期表中的总天数栏中提取数字

  • 在本指南,你将学会如何

    ✅

    仅在谷歌表中使用Index函数

    ✅

    只在谷歌表中使用Match函数

    ✅

    在谷歌表中使用两个索引匹配

    索引匹配公式可以是一个很好的选择Vlookup而且Hlookup在谷歌表。

  • 我是非常新的smartsheet,但我可以用这个更新单元格的“公共”表?

    或者您是说您不能只更新一个更改的单元格吗?

  • Stefan
    Stefan ✭✭✭✭
    I am very new to smartsheet, but can I use this to update cells on the "public" sheet?<\/p>

    Or are you saying that you cannot update just one changed cell?<\/p>","bodyRaw":"[{\"insert\":\"I am very new to smartsheet, but can I use this to update cells on the \\\"public\\\" sheet?\\nOr are you saying that you cannot update just one changed cell?\\n\"}]","format":"rich","dateInserted":"2022-07-21T19:37:10+00:00","insertUser":{"userID":150427,"name":"SEBERCAW","url":"https:\/\/community.smartsheet.com\/profile\/SEBERCAW","photoUrl":"https:\/\/lh3.googleusercontent.com\/a-\/AFdZucpba5VVBAHyaGW4Sjhx0LlZJJH1Otj6Fsbq5h6BAw=s96-c","dateLastActive":"2022-08-20T16:50:51+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/337078#Comment_337078","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/337078#Comment_337078

    @SEBERCAW

    你可以使用单元格链接来做到这一点。

    Cell Linking | Smartsheet Learning Center


    希望这有助于

    Stefan

    Smartsheet咨询,解决方案建设,培训和支持。

    为过程和人为的项目。

  • 我找到了正确的公式,但如果没有日期,它有一个#无效值。我试着用IFERROR修复它,但那不工作。除了添加IFERROR和左括号和右括号外,一切都能正常工作。


    = IFERROR(指数(收集({均等就业机会/ HARRASSEMENT},{名称},(电子邮件保护), {eeo /骚扰},<>""),1))

  • Stefan
    Stefan ✭✭✭✭

    @SEBERCAW

    如果其他一切都在工作,那么我看到一个不必要的括号在最后,这是需要在你的INDEX公式的结尾:

    = IFERROR(指数(收集({均等就业机会/ HARRASSEMENT},{名称},(电子邮件保护), {eeo /骚扰},<>"")),1)

    此外,您的公式还被构建为在抛出错误时显示1。1只会工作,如果与你的公式列是复选标记的类型。否则,你需要把消息显示在引号:

    = IFERROR(指数(收集({均等就业机会/ HARRASSEMENT},{名称},(电子邮件保护), {EEO/HARRASSEMENT}, <>"")), "无值present")


    希望这个有帮助。

    Stefan

    Smartsheet咨询,解决方案建设,培训和支持。

    为过程和人为的项目。

  • 谢谢,这帮助。

  • @Brett埃里克和每一个人,

    我是Smartsheet的新手。我可以使用索引/匹配来提供我需要的数据吗400行的智能表格一个2000行Smartsheet,如果400行是2000行Smartsheet的子集?我在某处读到索引/匹配最大有100行?TIA的任何信息/建议

  • 她名叫Stara
    她名叫Stara ✭✭✭✭✭✭

    @Edith奥伦斯坦

    我希望你平安无事!

    是的,应该没问题。(没有100行的限制,但是有100个不同的引用的限制)

    我希望这对你有帮助!

    注意安全,祝你度过美好的一周!

    最好的

    她名叫Stara|工作流顾问/ CEO @工作大胆

    我的帖子是否帮助或回答了你的问题或解决了你的问题?请支持社区通过将它标记为有洞察力/投票支持,棒极了,或/和作为公认的答案.这将使别人更容易找到解决方案或帮助回答!

    Smartsheet专家顾问和合作伙伴

    她名叫Stara|工作流顾问/ CEO @工作大胆

    W:www.workbold.com| E:(电子邮件保护)| p: +46 (0) - 72 - 510 99 35

    如果您需要Smartsheet、集成、一般工作流建议或其他方面的帮助,请随时与我联系。