Google Sheets:筛选文本和数值大于x的单元格。

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

Google Sheets: Filter cells with text and numeric value greater than x

问题

我为本地交通网络的Twitter帐户构建了一个网页抓取工具,以查找每天有多少公交车和有轨电车取消了行程。所有结果都列在Google Sheets表格中。我现在想要构建一个包含有关所有有轨电车信息的表格,以及另一个包含有关所有公交车信息的表格。它们在编号上有所不同:有轨电车线路编号为1-20(德语中为“Linie 1”,“Linie 2”等),而公交车编号大于100。

如何编写一个过滤命令来结合文本和数字范围?到目前为止,这是我得到的,但我不知道如何在单词“Linie”后面插入1-20或>100的范围...

额外说明:由于每个推文中都有其他数字,所以数字必须直接跟在单词“Linie”后面。

=FILTER(Tabellenblatt1!C:D; REGEXMATCH(Tabellenblatt1!C:C; "Linie [1-9]|Linie 1[0-9]|Linie 20|Linie [1-9][0-9][0-9]"))

请注意,上述公式将过滤以"Linie"开头并且后面跟着1-20之间的数字或大于100的数字的行。

英文:

I built a scraper for the Twitter account of a local transportation network to find out how many bus and tram rides are cancelled each day. All my results are listed in a Google Sheets table. I now want to build one table sheet with the information on all trams and another one with the information on all busses. They differ in their numeration: tram lines are numbered 1 - 20 (in German "Linie 1", "Linie 2", and so on), whereas the bus numbers are greater than 100.

How can I write a filter command to combine text and number range? That*s what I've got so far, but I don't know how to insert the 1-20 or >100 range behind the word "Linie"...

Additional note: As there are other numbers in each tweet, the number has to follow directly after the word "Linie".

=FILTER(Tabellenblatt1!C:D; REGEXMATCH(Tabellenblatt1!C:C; "Linie"))

答案1

得分: 0

你可以使用REGEXEXTRACT来获取"Linie"后面的数字,如下所示:

REGEXEXTRACT(Tabellenblatt1!C:C, "Linie (\d+)")

使用INDEX函数,你可以检查整个范围,由于REGEXEXTRACT返回一个字符串,你可以乘以1来获取其值,并与你所需的数字进行比较:

INDEX(REGEXEXTRACT(Tabellenblatt1!C:C, "Linie (\d+)")*1)<=20

然后,你可以使用两个条件相加来获取小于20且大于100的那些值:

=FILTER(Tabellenblatt1!C:D, (INDEX(REGEXEXTRACT(Tabellenblatt1!C:C, "Linie (\d+)")*1)<=20)+(INDEX(REGEXEXTRACT(Tabellenblatt1!C:C, "Linie (\d+)")*1)>=100))
英文:

You can use REGEXEXTRACT to get the numbers after Linie like this:

REGEXEXTRACT(Tabellenblatt1!C:C, &quot;Linie (\d+)&quot;)

With INDEX you'll be able to check the whole range, and since REGEXEXTRACT returns a string you can multiply by one to get its value and compare to your desired numbers:

INDEX(REGEXEXTRACT(Tabellenblatt1!C:C, &quot;Linie (\d+)&quot;)*1)&lt;=20

Then you can use both conditions summed to get those that are under 20 and more than 100:

=FILTER(Tabellenblatt1!C:D, (INDEX(REGEXEXTRACT(Tabellenblatt1!C:C, &quot;Linie (\d+)&quot;)*1)&lt;=20)+(INDEX(REGEXEXTRACT(Tabellenblatt1!C:C, &quot;Linie (\d+)&quot;)*1)&gt;=100))

huangapple
  • 本文由 发表于 2023年1月9日 18:49:46
  • 转载请务必保留本文链接:https://go.coder-hub.com/75056187.html
匿名

发表评论

匿名网友

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

确定