google-sheets - Array Formula messing with Query Table
问题描述
When I add an array formula to a column which is used to generate a query table, the query table doesn't sort the data as expected. When I remove the array formula it displays correctly.
The document is here: https://docs.google.com/spreadsheets/d/1r3bpNFy9k1h8anZJfefk6KrGYSy7mW2izxKQZb9mWoU/edit?usp=sharing
An example of the error: If I add an array formula to 'Book Rating'!J:J, the results of the query at 'Book League'!K1 (and E13 and H13) no longer order the books in the desired Desc order. When I remove the array, they order correctly. This type of problem is repeated throughout the sheet for all of the respective League tabs - e.g. at 'Chefs League'!A1.
Can someone help me understand why these Query tables are being messed up by the Array formulas?
解决方案
The issue is happening because of the nature of QUERYs, in that each column of a QUERY can only return one type of data (e.g., text or numbers, but not both). In the case where multiple data types exist in one column, QUERY will return the most populous type for the column. In your case, you've inserted "-" in place of null, and that is text. I'm guessing that your array formula filled the entire column of empty cells after your data set with that hyphen, making text the most populous type for the column. Therefore, all of your percentages were being converted to text. And in descending order of text, for instance, 9.25% (as a string) is "higher" than 25%, because the former begins with "9" and the latter begins with "2."
One way to resolve the issue would be to remove the "-" from your 'Book Rating'!J2 array formula and replace it with IFERROR(1/0), which will leave those cells null instead of filled with a hyphen. This will leave numbers as the most populous type for the column and your QUERY will work as expected.
Using E13 as an example, here was your original formula:
=Query('Book Rating'!$A$1:$K,"Select A,J where A<>'' Order by J Desc Limit 10")
If you want to leave that hyphen running in the array formula, here are some ways to leave the 'Book Rating'!J2 array formula as I suspect you had it, instead changing your QUERY formula:
1.) Pre-FILTER the 'Book Rating' data before performing the QUERY:
=Query(FILTER('Book Rating'!$A:$K,'Book Rating'!J:J<>"-"),"Select Col1,Col10 Where Col1 <> '' Order by Col10 Desc Limit 10",1)
2.) Use SORTN and FILTER together instead of QUERY, since FILTER can handle multiple data types in the same column:
=ArrayFormula({"Books","6 Stars";SORTN(FILTER({'Book Rating'!A2:A,'Book Rating'!J2:J},ISNUMBER('Book Rating'!J2:J)),10,0,2,0)})
推荐阅读
- linux - 如何在 NCDU 命令中排除特定目录
- delphi - 设置 Form Parent 会导致控件处于非活动状态
- python - 将 HOCR 输出转换为字符串(用于正则表达式)的策略是什么?
- docker - 无法连接到远程 Ubuntu 服务器上运行的 elasticsearch 或 kibana
- javascript - Javascript 画布 - 从加载的 4 波段 RGB 图像中删除 alpha 到 3 波段 JPEG 图像?
- javascript - Google Map-SDK:多边形不会出现在 Android 设备上
- android - 在 Ionic 4 应用程序中进行的 API 调用无法在 Android 设备上运行
- c++ - 构造模板化元组类型
- r - 如何有条件地从 r 中的观察中删除字符(例如“ed”、“s”词尾)
- php - PHP Domdocument 使用 saveXML 而不是 save