在Google Spreadsheets中使用名称而不是字母来选择列，而不是字母? [英] Select columns by name rather than letter in Google Query Language (GQL) with Google Spreadsheets?

问题描述

新手问题，当将Google查询语言与Google Spreadsheets结合使用时，是否可以按名称而不是字母来选择列?

newbie question, is it possible to select columns by name rather than letter when using Google Query Language with Google Spreadsheets?

这对我来说很好:"SELECT A，COUNT(B)GROUP BY A"

This works fine for me: "SELECT A, COUNT(B) GROUP BY A"

如果我可以在第一行中使用列标题，就像在数据库中那样，那就太好了，

It'd be great if I could use the column headers in the first row, more like a database, as in:

选择学生，按学生计数(拘留)的GROUP"

"SELECT student, COUNT(detention) GROUP BY student"

我怀疑这是不可能的，但是希望这又是我的Internet搜索技能使我失败的另一种情况.

I suspect it's not possible, but hope this yet another case where my Internet search skills failed me.

推荐答案

目前无法实现. GQL文档指出[1]列是由标识符(而不是标签)引用的.例如，在Google Spreadsheet中，列标识符是一或两个字符的列字母(A，B，C，...). "

This is currently not possible. The GQL documentation states[1] "Columns are referenced by the identifiers (not by labels). For example, in a Google Spreadsheet, column identifiers are the one or two character column letter (A, B, C, ...)."

如果要在电子表格中执行此操作，则可以使用以下公式将列标题名称转换为字母(可能需要+1进行一些调整(可能为+2)).它还依赖于列标题的唯一性，并且不包含逗号

If you want to do this in a spreadsheet it is possible with the following formula to convert a column header name into a letter (some tweaking might be required +1 (might be +2)). It also relies on column headers being unique and not containing commas

=REGEXEXTRACT(ADDRESS(1,COUNTA(SPLIT(LEFT(JOIN(",",TRANSPOSE(1:1)),FIND("your_column_name",JOIN(",",TRANSPOSE(1:1)))),","))+1,4);"[a-zA-Z]+")

[1] https://developers.google.com/chart/interactive /docs/querylanguage#Select

这篇关于在Google Spreadsheets中使用名称而不是字母来选择列，而不是字母?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！