英文:
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, "Linie (\d+)")
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, "Linie (\d+)")*1)<=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, "Linie (\d+)")*1)<=20)+(INDEX(REGEXEXTRACT(Tabellenblatt1!C:C, "Linie (\d+)")*1)>=100))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论