Google sheet, select columns within a named range
nujabes
I'm trying to select a range of data within a named range on a google sheet but after searching I still can't figure it out how to do it.
In this case, I would like to count the amount of time a club appeared within a gameweek.
Here I can do it for all the gameweek tables I have, there is no problem.
=QUERY({B:C;D:E;F:G;H:I;J:K}; "SELECT Col1, SUM(Col2) WHERE Col1 <> '' AND Col1 <> 'GK' AND Col1 <> 'DEF' AND Col1 <> 'MID' AND Col1 <> 'FW' AND Col1 <> 'EXTRA' AND Col1 <> 'Club' AND Col1 <> 'count' AND Col1 MATCHES '^.{0,5}$' GROUP BY Col1 ORDER BY SUM(Col2) DESC LABEL Col1 'Club', SUM(Col2) 'count'")
But I'd like to do the same for each of the gameweek table. So far I have this
=QUERY({(B6:B15):(C6:C15);(D6:D15):(E6:E15);(F6:F15):(G6:G15);(H6:H15):(I6:I15);(J6:J15):(K6:K15)}; "SELECT Col1, SUM(Col2) WHERE Col1 <> '' GROUP BY Col1 ORDER BY SUM(Col2) DESC LABEL Col1 'Club', SUM(Col2) 'count'")
but it is not very usable as I'd have to change each range for every next table.
So I tried to make the "Gameweek 1" table as a named range gw1_clubcount and only select its adequate columns similarly as the total so that I would just have to change that for each gameweek but obviously it is not working but that's what I would like to reach.
=QUERY(gw1_clubcount {B:C;D:E;F:G;H:I;J:K}; "SELECT Col1, SUM(Col2) WHERE Col1 <> '' GROUP BY Col1 ORDER BY SUM(Col2) DESC LABEL Col1 'Club', SUM(Col2) 'count'")
I've found the INDEX function where you can select the desired row and column of the named range.That would give something like this below but that's not how you use it.
=QUERY({INDEX(gw1_clubcount,,1):INDEX(gw1_clubcount,,2);INDEX(gw1_clubcount,,3):INDEX(gw1_clubcount,,4);INDEX(gw1_clubcount,,5):INDEX(gw1_clubcount,,6);INDEX(gw1_clubcount,,7):INDEX(gw1_clubcount,,8);INDEX(gw1_clubcount,,9):INDEX(gw1_clubcount,,10)} ; "SELECT Col1, SUM(Col2) WHERE Col1 <> '' GROUP BY Col1 ORDER BY SUM(Col2) DESC LABEL Col1 'Club', SUM(Col2) 'count'")
I'm not very experienced with sheets and a little help would be very appreciated !
Nikko J.
Here I created a solution that requires custom function:
To write a custom function:
- Create or open a spreadsheet in Google Sheets.
- Select the menu item Tools > Script editor.
- Delete any code in the script editor.
- For this problem, simply copy and paste the code below (stack function) into your script editor.
- At the top, click Save save.
To use custom function:
- Click the cell where you want to use the function.
- Type an equals sign (=) followed by the function name and any input value — for example, =DOUBLE(A1) — and press Enter.
- The cell will momentarily display Loading..., then return the result.
Code:
function stack(arr) {
var flatArr = arr.flat();
var filtered = flatArr.filter(function (el) {return el != "";});
const newArr = [];
while(filtered.length) newArr.push(filtered.splice(0,2));
return(newArr);
}
Named Ranges:
Example usage:
GAMEWEEK1:
=QUERY(stack(FLATTEN(gw1_clubcount)), "SELECT Col1, SUM(Col2) WHERE Col1 <> '' GROUP BY Col1 ORDER BY SUM(Col2) DESC LABEL Col1 'Club', SUM(Col2) 'count'")
GAMEWEEK2:
=QUERY(stack(FLATTEN(gw2_clubcount)), "SELECT Col1, SUM(Col2) WHERE Col1 <> '' GROUP BY Col1 ORDER BY SUM(Col2) DESC LABEL Col1 'Club', SUM(Col2) 'count'")
References:
Este artigo é coletado da Internet.
Se houver alguma infração, entre em [email protected] Delete.
editar em
Artigos relacionados
TOP lista
- 1
R Shiny: use HTML em funções (como textInput, checkboxGroupInput)
- 2
UITextView não está exibindo texto longo
- 3
Dependência circular de diálogo personalizado
- 4
Acessando relatório de campanhas na AdMob usando a API do Adsense
- 5
Como assinar digitalmente um documento PDF com assinatura e texto visíveis usando Java
- 6
R Folheto. Dados de pontos de grupo em células para resumir muitos pontos de dados
- 7
Setas rotuladas horizontais apontando para uma linha vertical
- 8
O Chromium e o Firefox exibem as cores de maneira diferente e não sei qual deles está fazendo certo
- 9
Definir um clipe em uma trama nascida no mar
- 10
Por que meus intervalos de confiança de 95% da minha regressão multivariada estão sendo plotados como uma linha de loess?
- 11
Como dinamizar um Dataframe do pandas em Python?
- 12
regex para destacar novos caracteres de linha no início e no fim
- 13
Why isn't my C# .Net Core Rest API route finding my method?
- 14
Como obter a entrada de trás de diálogo em treeview pyqt5 python 3
- 15
Tabela CSS: barra de rolagem para a primeira coluna e largura automática para a coluna restante
- 16
How to create dynamic navigation menu select from database using Codeigniter?
- 17
Como recuperar parâmetros de entrada usando C #?
- 18
Changing long, lat values of Polygon coordinates in python
- 19
Livros sobre criptografia do muito básico ao muito avançado
- 20
Método \ "POST \" não permitido no framework Django rest com ações extras & ModelViewset
- 21
Pesquisa classificada, conte números abaixo do valor desejado
deixe-me dizer algumas palavras