Google Sheets – 在多列上查询计数

huangapple go评论61阅读模式
英文:

Google Sheets - Query Count over multiple columns

问题

我正在尝试在Google Sheets中编写一个查询公式,以统计字符串的任何出现,如果该行的另一个单元格以特定字符串开头。

表格有数百行,但看起来像这样:

现在我想要统计列DJ中出现名称Alex的任何情况,如果相应的类别列(AG)以WEB开头。

所以我写了以下公式:

=QUERY({A2:L481},"SELECT COUNT(Col1) WHERE ((LOWER(Col4) LIKE '%alex%' AND LOWER(Col1) LIKE '%web%') OR (LOWER(Col10) LIKE '%alex%' AND LOWER(Col7) LIKE '%web%') OR (LOWER(Col16) LIKE '%alex%' AND LOWER(Col13) LIKE '%web%'))",0)

不幸的是,总数不正确。

也许是因为我有点过度疲劳,所以我看不清楚了。但我就是找不到错误。

提前感谢任何帮助。

问候
Lars

英文:

I am trying to write a query formula in Google Sheets to count any appearances of a string, if another cell in that row begins with a specific string.

The table has hundreds of rows, but looks like this:

A B C D E F G H I J K L M N O  P Q R
WEB – General 1080 1080 Michael JPEG 2 Mb Display - Business 1920 1080 Alex PNG 2
WEB – General 560 660 Martin JPEG 50 Web - Mixed 1920 1080 Alex PNG 2
Display – Mixed 1700 300 Matteo JPEG Display - General 1920 1080 Bill PNG 2
WEB – Business 800 500 Michael JPEG WEB - General 800 500 Alex JPEG 250kb
WEB - General 1440 480 Matteo PNG Display - Mixed 700 600 MATTEO PNG
WEB – Mixed 297 210 Alex JPEG 4000 WEB - General 297 210 Maluma JPEG 4000

Here Columns A - F and G - L belong together in each case.

Now I want to count any appearance of the name Alex in column D & J, if the corresponding category column (A & G) begins with WEB.

So I wrote the following formula:

=QUERY({A2:L481};"
SELECT COUNT(Col1) WHERE ((LOWER(Col4) LIKE '%alex%' AND LOWER(Col1) LIKE '%web%') 
OR (LOWER(Col10) LIKE '%alex%' AND LOWER(Col7) LIKE '%web%') 
OR (LOWER(Col16) LIKE '%alex%' AND LOWER(Col13) LIKE '%web%'))";0)

Unfortunately the sum is not correct.

Maybe I just can't see through it any more because I'm a bit overtired. But I just can't find the mistake.

Thanks in advance for any help.

Regards
Lars

答案1

得分: 2

=SUM(INDEX(IF((LEFT(A2:O, 3)="web")*(D2:R="alex"), 1, )))

如果需要部分匹配 Alex 以匹配 Alex DunphyAlexander

=SUM(INDEX(IF((LEFT(A2:O, 3)="web")*(IFERROR(SEARCH("alex", D2:R))), 1, )))

如果需要部分匹配 Alex 以匹配 Alex Dunphy 但不匹配 Alexander

=SUM(INDEX(IF((LEFT(A2:O, 3)="web")*(IFERROR(REGEXMATCH(D2:R, "(?i)\balex\b"))), 1, )))

英文:

You may try:

=sum(index(if((left(A2:O;3)="web")*(D2:R="alex");1;)))

Google Sheets – 在多列上查询计数

IF Alex needs to be also partial-matched to say Alex Dunphy and Alexander as well:

=sum(index(if((left(A2:O,3)="web")*(iferror(search("alex",D2:R))),1,)))

IF Alex needs to be also partial-matched to say Alex Dunphy and NOT Alexander:

=sum(index(if((left(A2:O,3)="web")*(iferror(regexmatch(D2:R,"(?i)\balex\b"))),1,)))

huangapple
  • 本文由 发表于 2023年6月9日 02:12:39
  • 转载请务必保留本文链接:https://go.coder-hub.com/76434636.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定