
ð ã¹ãã¬ããã·ãŒãã§éšåäžèŽã»æ£èŠè¡šçŸã䜿ãå§ãããREGEXMATCHãå¿ èŠã«ãªãç¬éã
ã¹ãã¬ããã·ãŒããäžçŽã¬ãã«ã§äœ¿ã£ãŠãããšã
ãå®å
šäžèŽã ãšæŸããªãããŒã¿ãå¢ããŠããã
ã衚èšããã®ããã§éèšãåããªãã
ãšããå£ã«å¿
ãåœãããŸãã
ãã®ãšãã«ç»å Žããã®ã REGEXMATCHïŒæ£èŠè¡šçŸïŒ ã§ãã
é£ãããã«èŠããŸããã䜿ãã©ãããéå®ããã°äžæ°ã«æŠåšã«ãªããŸãã
ãã®èšäºã§ã¯ã
ãªãå®å
šäžèŽã§ã¯è¶³ããªããªãã®ã
REGEXMATCHãå¿
èŠã«ãªãç¬é
ããQUERYã»FILTERãšçµ¡ããŠæŽçããŸãã
ð å®å šäžèŽãéçšããªããªãçç±
åå¿è ã®ãã¡ã¯ããããªæ¡ä»¶ã§ååã§ãã
=B2="Tã·ã£ã"
ã§ãå®åã§ã¯ãããã«éçãããŸãã
Tã·ã£ã
ããŽTã·ã£ã
ããªã³ãT
Tã·ã£ãïŒçœïŒ
人ãèŠãã°åãã«ããŽãªã§ãã
ã¹ãã¬ããã·ãŒãçã«ã¯ å
šéšå¥ç© ã§ãã
ð ããã§å¿
èŠã«ãªãã®ã
ãå«ãŸããŠããã°OKããšããèãæ¹ ã§ãã
ð ãµã³ãã«ããŒã¿ïŒååäžèЧïŒ
ãŸãã¯ãA1:C9 ã«ãã®ãŸãŸè²Œãä»ããŠãã ããã
æ¥ä» ååå éé¡
2025-01-05 Tã·ã£ã 2800
2025-01-08 ããŽTã·ã£ã 3200
2025-01-10 ããªã³ãT 3500
2025-01-12 ããã 9800
2025-01-15 Tã·ã£ãïŒçœïŒ 3000
2025-01-18 ã¹ãŠã§ãã 7200
2025-01-20 ãã³ã 5200
2025-01-22 Tã·ã£ã 2600

ãã®ããŒã¿ã䜿ã£ãŠèª¬æããŸãã
ð éšåäžèŽã®åºæ¬ã¯REGEXMATCH
REGEXMATCHã¯
ããã®æååãå«ãŸããŠããããã TRUE / FALSE ã§è¿ããŸãã
äŸïŒåååã«ãTã·ã£ãããå«ãŸããŠããã
=REGEXMATCH(B2,"Tã·ã£ã")

çµæ
å«ãŸããŠãã â TRUE
å«ãŸããŠããªã â FALSE
ð å®å
šäžèŽã§ã¯æŸããªãã£ã
ãããŽTã·ã£ãããTã·ã£ãïŒçœïŒã
ã TRUE ã«ãªããŸãã
ð§ FILTER à REGEXMATCHïŒæœåºç²ŸåºŠã¢ããïŒ
FILTERãšçµã¿åããããšã
éšåäžèŽæœåº ãäžæ°ã«å®åã¬ãã«ã«ãªããŸãã
äŸïŒãTã·ã£ãããå«ãè¡ã ãæœåº
=FILTER(A2:C, REGEXMATCH(B2:B,"Tã·ã£ã"))

ããã ãã§ã
衚èšãã
è£è¶³èª¬æä»ãååå
ããŸãšããŠæŸããŸãã
ð FILTERã®æ¡ä»¶ã
ã=ããããREGEXMATCHãã«å€ããã ã
ãšèŠãããšæ¥œã§ãã
ð QUERY à REGEXMATCHïŒäžçŽè ã®æ¬åœïŒ
QUERYã§ãéšåäžèŽã¯äœ¿ããŸãã
äŸïŒåååã«ãTã·ã£ãããå«ãããŒã¿ã ã衚瀺
=QUERY(A2:C,
"select * where B matches 'Tã·ã£ã'")

QUERYã§ã¯
matches â æ£èŠè¡šçŸ
= â å®å šäžèŽ
ãšããéãããããŸãã
ð§© ORæ¡ä»¶ãäžæ°ã«æžããã®ãæ£èŠè¡šçŸ
REGEXMATCHã®åŒ·ã¿ã¯
ORæ¡ä»¶ã1ã€ã«ãŸãšããããããš ã§ãã
äŸïŒãTã·ã£ãããŸãã¯ãã¹ãŠã§ããããå«ã
=REGEXMATCH(B2,"Tã·ã£ã|ã¹ãŠã§ãã")

FILTERã§äœ¿ããšãããªããŸãã
=FILTER(A2:C, REGEXMATCH(B2:B,"Tã·ã£ã|ã¹ãŠã§ãã"))

ð IFãORãéããªããŠæžãã®ã倧ããªã¡ãªããã§ãã
â ïž æ£èŠè¡šçŸã䜿ããšãã®æ³šæç¹
䟿å©ã§ããã䜿ãããã¯NGã§ãã
â æžããæ¬äººããèªããªããªã
è€éãªæ£èŠè¡šçŸã¯ã
æ°ãæåŸã®èªåãèªããŸããã
ð æ¡ä»¶ã¯ ã·ã³ãã«ãªåèªæå® ãåºæ¬ã
â¡ å šåæå®ã¯éããªãããã
REGEXMATCH(B:B,"Tã·ã£ã")
ã¯äŸ¿å©ã§ããã
ããŒã¿ãå¢ãããšéããªããŸãã
ð å®åã§ã¯
B2:B ïŒ å å·¥ã·ãŒã
ãããããã§ãã
ð¡ REGEXMATCHã䜿ãå€æåºæº
次ã®ã©ããã«åœãŠã¯ãŸã£ãããREGEXMATCHã®åºçªã§ãã
衚èšããããã
åååã»èª¬ææãæ¡ä»¶ã«ããã
ORæ¡ä»¶ãå¢ããŠãã
å®å šäžèŽã ãšæŸããããªã
éã«ã
ãã¹ã¿ãããã
å€ãåºå®
ãªããå®å šäžèŽã®ãŸãŸã§OKã§ãã
ð§ QUERYã»FILTER匷åã®èãæ¹
äžçŽè
ã«ãªããšå€§äºãªã®ã¯ã
ãå
šéšãå³å¯ã«æããããã
ãæŸããã粟床ãäžãããããšã
REGEXMATCHã¯ã
ããŒã¿ã äººã®æèŠã«è¿ã¥ããããã®é¢æ°
ãšèãããšäœ¿ãã©ãããèŠããŠããŸãã
ð ä»åã®ãŸãšã
REGEXMATCHã¯ã
å®å
šäžèŽã§ã¯è¶³ããªããªã£ãç¬éã«äœ¿ã颿°ã§ãã
ãã€ã³ãã¯ãã®3ã€ã
éšåäžèŽïŒREGEXMATCH
FILTERã»QUERYãšçµã¿åããã
ORæ¡ä»¶ãã·ã³ãã«ã«æžã
REGEXMATCHã䜿ããããã«ãªããšã
QUERYãšFILTERã®ç²ŸåºŠãäžæ®µäžããã
ãå®åã§äœ¿ããæœåºãã«å€ãããŸãã
ãŸãã¯
ãå®å
šäžèŽã§è¶³ããªãå Žé¢ã ãèŠã€ãããšããããã
æ£èŠè¡šçŸã«è§ŠããŠã¿ãŠãã ãã ðâš