当使用QUERY时，如何使它在使用过滤器时使数据一起移动? [英] When using QUERY, how can I make it so that data moves together when using filter?

问题描述

我正在为我玩的视频游戏创建工具.

I am creating a tool for a video game I play.

链接到示例电子表格(请复制到编辑，以使此副本完整无缺以供其他助手使用.)

Link to the example spreadsheet (Please make a copy to edit so that this copy stays intact for additional helpers).

第1张表是"已选择".它包含游戏中所有可用冠军的列表，并包括其属性.

Sheet 1 is "Choose Owned". It contains a list of all of the champions available in the game and includes their attributes.

• 列A包含复选框.选中此复选框表示用户拥有该冠军，并将其带到工作表2.

第二页称为"拥有".它包含在选择拥有的"中选中的冠军(也就是用户拥有的冠军)的列表.拥有"也包括冠军属性，如选择拥有"中首次看到的那样.

Sheet 2 is called "Owned". It contains a list of the champions checked off in "Choose Owned" (aka the champions the user owns). "Owned" includes the champion attributes too, as first seen in "Choose Owned".

• 除了这些相同的属性外，拥有的"还包含8个其他列.这些列来自G:N列，分别标记为级别"，排名"，提升"和团队标签"(团队标签"占J:N列).此数据是唯一信息，需要用户根据自己输入信息冠军.

由于拥护者太多，我希望用户能够在拥有的"中使用过滤器功能，以便他们可以轻松地找到所需的拥护者或对他们希望的桌子进行排序.

Because there are so many champions, I want the user to be able to use the Filter function in "Owned" so they can easily locate the champion they need or sort the table however they wish.

但是，由于我使用 QUERY 函数从自选"中获取数据，因此Filter函数趋向于崩溃.当您尝试对A-Z或Z-A进行排序时，最明显的错误就会出现.这根本无法完成.我对此表示满意，甚至在顶部添加了一个便条，告诉用户避免按字母顺序排序.

However, because I use the QUERY function to get the data from "Choose Owned", the Filter function tends to break. The most obvious error comes when you try to sort A-Z or Z-A; this simply cannot be done. I was fine with this, and have even included a note at the top telling the user to avoid sorting alphabetically.

在用户尝试从选择拥有的"中添加新冠军之前，其他所有东西都可以正常工作.将冠军添加到拥有的"后，G:N列中的其他唯一数据会乱序，因为它们不会与原始冠军一起移动.

Everything else works correctly until the user tries to add a new champion from "Choose Owned". When the champion is added to "Owned", the additional, unique data in columns G:N go out of order because they don’t move with their original champions.

示例:

• 我选择我的冠军.这些冠军将被复制到拥有的".
• 我在G:N列中选择他们的相对数据.
• 几天后，我获得了新的冠军，并在选择拥有的"中选中了他们，因此将它们添加到拥有的"中.但是，当我这样做时，冠军将保持与选择拥有"中的顺序相同，并且G:N列不会随冠军而移动，所以现在，信息是错误的冠军.

由于要添加冠军，我希望在编辑表格时附加数据(G:N)与冠军一起移动.或者，换句话说，我希望这些列保持与第一列的链接.

I want the additional data (G:N) to move with their champions when the table is edited due to champions being added. Or, in other words, I want those columns to stay linked to the first columns.

如果有其他方法可以实现所有这些目的，例如我必须使用除 QUERY 之外的其他功能，那很好！

If there is a different way to achieve all of this like if I have to use a function other than QUERY, that is fine!

请分享您可能拥有的所有解决方案.我宁愿不使用脚本，但会考虑该想法是否可行.

Please share any solutions you may have. I would prefer to not use a script but will consider the idea if it works.

推荐答案

这是Google表格中的常见问题，可以通过两种方式解决:

this is a common issue within Google Sheets and it's solvable in 2 ways:

• 或者通过引入一个公共值(唯一ID)，然后将手动输入链接到查询并通过ID对其进行对齐

• either by introducing a common value (unique ID) and then linking the manual input to query and aligning it by ID

或更简单的方法-使用时间戳/线性ID，以便将每个新条目添加到底部，然后根据此顺序对查询进行排序.

or easier approach in your case - using timestamp/linear ID so every new entry would be added to the bottom and then the query would be sorted based on this order.

这篇关于当使用QUERY时，如何使它在使用过滤器时使数据一起移动?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！