查询一个表并将数据输出到多列 [英] Query one table and output data into multiple columns

问题描述

简单地说，我正在尝试获取单列查询结果，并将其输出到5乘x长的表中.这是主表的组织方式.

Simply put I am trying to take a single column query result and output it into a 5 wide by × long table. This is how the main table is organized.

在单独的标签上，我想单独列出所有被捕获和看到的Pokemon，以便于搜索.虽然我可以使用

On separate tabs, I want to list all of the caught and seen Pokemon on their own for easy search. While I can get it to output something like this with

=query(NatDex, "Select C Where F <> ''",1) 

我希望它输出类似这样的数据以便于阅读，因此最终不会超过100个条目:

I would like it to output the data something like this for easy reading so it's not eventually 100+ entries long:

如果您可以给我公式/可以做的事情，我可以在此奖励我可以改变第二张桌子的宽度.但这对我来说远没有那么重要.我曾尝试查找数据透视表或转置之类的东西，但它们似乎都没有我需要的功能.

Bonus points if you can give me formula/something to do it where I can vary how wide the second table is. But this is far less important to me. I've tried looking up stuff like Pivot tables or Transpose, but neither of them seems to have the functions I need to pull this off.

推荐答案

如果将查询输出放在某些辅助列中，则可以使用此公式并向下拖动:

if you put your query output in some auxiliary column, you can use this formula and drag down:

=ARRAY_CONSTRAIN(TRANSPOSE(INDIRECT("A"&6+(ROW()-ROW($A$2))*5&":A")), 1, 5)

6列:

=ARRAY_CONSTRAIN(TRANSPOSE(INDIRECT("A"&7+(ROW()-ROW($A$2))*6&":A")), 1, 6)

3列:

=ARRAY_CONSTRAIN(TRANSPOSE(INDIRECT("A"&4+(ROW()-ROW($A$2))*3&":A")), 1, 3)

5列，但从第10行开始:

for 5 columns but starting on 10th row:

=ARRAY_CONSTRAIN(TRANSPOSE(INDIRECT("A"&6+(ROW()-ROW($A$2)-9)*5&":A")), 1, 5)

等

这篇关于查询一个表并将数据输出到多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！