手动数据标记和查找 [英] Manual data tagging and lookup

问题描述

我需要一个允许通过不同的文本标签过滤条目的系统.

假设我们存储有关某些产品的信息.我们有红苹果、梨、西瓜、黄瓜、辣椒和面包.我们将它们存储在 A 列中名为Data"的工作表中.下一列由标签占据，例如 Red Apples 是 Red、Sweet、Fruit、Unpacked；辣椒是红色的，辣的，蔬菜的，包装好的；面包刚刚包装好.

然后在另一张纸上，我们有一个专用范围，例如 A1:A10，它可以接受任何数据标签，例如 Spicy 或 Packed.我需要的是，当有人在此范围内输入 Spicy and Packed 时，它会查找所有 Spicy 和 Packed 项并显示它们，因此在这种情况下，它会在 B1 单元格中显示 Peppers.

回顾一下:Data!A:A - 条目名称，Data!B:Z - 标签，Main!A1:A10 - 用户输入的标签，Main!B:B - 带有标签的条目，对应于在A1:A10.

我试图使用 FILTER，但我不知道如何选择合适的条件范围.我觉得这在这个系统中应该是可能的，我真的不想钻研脚本领域.

解决方案

这可以通过使用辅助列收集所有标签，然后使用 =query() 公式来实现.

1)首先使用 =join() 或 =textjoin() 创建一个多标签列，捕获每个产品的所有潜在标签.

2)然后使用

I need a system that allows to filters entries by different text tags.

Say we store info about some products. We have Red Apples, Pears, Watermelons, Cucumbers, Peppers and Bread. We store them in sheet named "Data" in column A. Next columns are occupied by tags, like Red Apples are Red, Sweet, Fruit, Unpacked; Peppers are Red, Spicy, Veggie, Packed; Bread is just Packed.

Then on another sheet we have a dedicated range, say A1:A10, which can accept any data tag, like Spicy or Packed. What I need is when somebody enters Spicy and Packed in this range, it looks up all items that are Spicy and Packed and displays them, so in this case it would display Peppers in B1 cell.

To recap: Data!A:A - entry names, Data!B:Z - tags, Main!A1:A10 - tags entered by user, Main!B:B - entries with tags, that correspond to those entered in A1:A10.

I was trying to use FILTER, but I can't figure out how to select proper condition ranges. I feel like this should be possible within this system and I really don't want to delve into scripting field.

解决方案

This can be achieved using a helper column to collect all the tags and then a =query() formula.

1) Start by creating a multi-tag column using either =join() or =textjoin(), capturing all the potential tags for each product.

2) Then use this answer to help you create the =query() formula needed.

这篇关于手动数据标记和查找的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！