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

问题描述

简单地说，我试图将单列查询结果输出到一个 5 宽 × 长的表中.这就是主表的组织方式.

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.

在单独的选项卡上，我想单独列出所有捕获和看到的口袋妖怪，以便于搜索.虽然我可以用

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屋！