Query颿°ã䜿ã£ãŠããŒã¿ãéèšããã - å ¶ã®å£±
â»ä»åã®é¢æ°ã¯éåžžã«å€æ©èœãªã®ã§ïŒåã«åããŠç޹ä»ããŠããŸãã
ç®æ¬¡
- 1 ä»å䜿çšãããµã³ãã«ã¹ãã¬ããã·ãŒã
- 2 Query颿°ã®äœ¿ãæ¹
- 3 Query颿°ã®çµæãå¥ã®é¢æ°ã«é£ããã
- 4 ã¯ãã¹éèšãè¡ã
- 5 é¢é£ãªã³ã¯
ä»å䜿çšãããµã³ãã«ã¹ãã¬ããã·ãŒã
ããé«åºŠãªäœ¿ãæ¹ã®ãã®ïŒã«ã€ããŠã¯ä»¥äžã®ãšã³ããªãŒã«ãªããŸãã
Query颿°ã䜿ã£ãŠããŒã¿ãéèšããã - å ¶ã®åŒ
Query颿°ã®äœ¿ãæ¹
æŠèŠ
ãã®é¢æ°ã¯ä»ã®ïŒã€ïŒFilter颿°ãImportRange颿°ïŒãšæ¯èŒãããšãéåžžã«äœ¿ãã«ãã颿°ã§ãããŸãã颿°ã®çµã¿æ¹ã¹ã¿ã€ã«ãç¬ç¹ãªã®ã§ãšã£ã€ãé£ã人ãå€ãã§ããããããããSQLã®äœ¿ããªãã¹ãã¬ããã·ãŒãã«æŒããŠããã®é¢æ°ã¯éåžžã«ããããã颿°ã§ããæ°åŒãéåžžã«SQLã©ã€ã¯ãªæããªã®ã§ãVBAãªã©ã§SQLæãæžããŠã人ã¯å²ããšç¿åŸããããã®ã§ã¯ãªãã§ããããïŒç¹åŸŽãšããŠã
- 銬鹿ã§ããããŒã¿ã®å¡ã«å¯ŸããŠå®è¡ããŠãéåžžã«é«éã«å€ãè¿ããŠãããã
- éåžžã®é¢æ°ãšã¯ç°ãªããããªã颿°å ã§ã®ãã©ã¡ãŒã¿ã®æå®æ¹æ³ãç¬ç¹ã§ããã
- SQLããã¯ãªãã©ã¡ãŒã¿æå®ã§ãããããéåžžã«ãšã£ã€ãã«ããã
- ããŒãšãªããã©ã¡ãŒã¿ã®å€ãç¹å®ã®ã»ã«ããåãããå Žåãæ¥ä»ã»æ°å€ã»ããã¹ãã§æå®æ¹æ³ã«éããããã
- SQLããã¯ãªæç« ã®äžã§éèšãã«ãŠã³ããªã©ã®æå®ãåºæ¥ãŸãããåŠãªæååã1è¡ç®ã«åºãã®ãéåžžã«æ°ã«ãªãïŒ9.ãåç §ïŒ
- Query颿°ã§åž°ã£ãŠããå€ãéåžžã®SUM颿°ãªã©ã«é£ãããŠãéèšãªã©ãåºæ¥ããSUMIFãDSUMãªã©ã¯äžèŠã«ãªãã
- ãã©ã¡ãŒã¿ã®æå®ãã¬ã³ãžã§ã¯ãªããã»ã«åäœã§æå®ãå¯èœãªã®ã§ãDSUM颿°ã®ãããªãã©ã¡ãŒã¿ãç¹å®ã¬ã³ãžã§æå®ããªããšãããªããšãã£ãããšããªããããéåžžã«èéãããã
- ããããã¿ã€ãïŒã¯ãã¹éèšïŒã®éèšãå¯èœã§ããã
- ããŒã¿ã®ã¿ã€ãã«éšåã颿°å ã§æ§ç¯ãå¯èœã§ããã
泚æç¹
ãã®é¢æ°ã䜿ãäžã§ã®æ³šæç¹ãçµæ§ãããŸãã以äžã«ããããŸãšããŠã¿ãŸããã
- Query颿°å ã§ã¯ã«ã©ã è¡ã¯äœ¿çšããªãã®ã§ãç¯å²æå®ã§ããããã®è¡ã¯å«ããªãïŒãã®ãããA2ããæå®ããŠããïŒã
- AåãBåãªã©ã§æå®ããããselectæã®äžã§sum(C)ãšãã£ããããªæžãæ¹ãå¯èœã§ã¯ããããããã1è¡ç®ã«sumãšããæååãåºåããããããã®ã§ãéåžžã¯1.ã®ããã«sum颿°ã«é£ãããã®ãéåžžã
- Where以å€ã«ãGroup Byãªã©ã䜿çšã§ããã
- æ¡ä»¶åŒã®æå®ã«ç¹å®ã®ã»ã«ã®å€ãåç §ããããå Žåã«ã¯ãââã§æ¬ã£ãŠãäžã«&&ã§æ¬ã£ãã»ã«çªå°ãæå®ããã
- äœããæ°å€ã§ã¯ãªãå ŽåïŒæååïŒã«ã¯ãâã§æ¬ã£ãäžã«4.ã®æžãæ¹ãããªããã°ãªããªãïŒããéèŠïŒãæ¥ä»ã®å Žåãåãã
- æŽã«æ¥ä»ãæå®ããå Žåã«ã¯ã5.ã®åã«dateãæå®ããã®ã決ãŸãã«ãªã£ãŠããã
- selectã§å§ãŸãæ¡ä»¶åŒã¯ââã§æ¬ã£ãŠçœ®ããªããã°ãªããªãã
- andãorã䜿çšã§ããããã§ããã°ããããã®æ¡ä»¶åäœããšã«()ã§æ¬ã£ãŠãããšããããããããªãã
- ç¯å²æå®ããäžã®æ¥ä»ãå ã«äœããæœåºãããå Žåã¯èŠæ³šæãQueryã®æ¡ä»¶åŒåŽã¯ã2014-10-06ããšãã£ããã¿ãŒã³ã§æå®ãããŠããªããšåãä»ããŠãããªãããã¡ãããã»ã«ã®äžã®å€ã«è¡šç€ºãšããŠãã®ãããªåœ¢ã«ããŠããã¡ãæžåŒãªãããã¹ãã«ããŠæ ŒçŽããŠããã¹ããã2014/10/06ããšãã£ãæå®ããã¡ã
- ããã«ãæœåºãããåŽã®æ¥ä»ããŒã¿ã«ã泚æãå¿ èŠã§ããããã¡ãã¯ã2014/10/06ããšãã£ãããŒã¿ã§ãã£ãŠãäœãåé¡ããªããããã®ä»£ããã衚瀺ãããã§ããæå»ããŒã¿ãå ¥ã£ãŠãããšãã¡ã
- æ ã«ã©ããããã³ããŒããŠããŠsetValuesãªããã§è²Œãä»ããåŸã®ããŒã¿ãèŠããšã衚瀺圢åŒã§2014/10/06ãšãªã£ãŠããŠããã»ã«ã®äžã®ããŒã¿ã¯ã2014/10/06 7:00:00ããªããŠåœ¢ã§ãæ¥ä»ããŒã¿ãåæã«æ··ãã£ãŠããããšããããsetValuesã§ã»ããããæã«ãããèµ·ãããããã§èªåã¯å€§åµãŸãããŠãŸãããããŒã¿ããæå»éšåãåã£ãŠæ»ããŠãããªããšãããªãã
- æ£çŽèšã£ãŠãäœåããã®é¢æ°ã䜿ããšãæ°çŸã¬ã³ãŒãã§æ¢ã«éããããŸããŠãã倿°ã®ã»ã«ã«åæ§ã®èšç®åŒãå ¥ãããšæŽã«éããsum颿°ã«é£ãããŠã»ã«ã«åæ ã倿§ããã«ã¯å®çšçã§ã¯ãªãã
- å ¥ãåã§Query颿°å ã§ãŠãŒã¶å®çŸ©é¢æ°ã¯äœ¿ããŸãããéã«ãŠãŒã¶å®çŸ©é¢æ°ã«é£ãããããäºããŠãŒã¶å®çŸ©ã§ããŒã¿ãæºåããŠããããã®ãQuery颿°ã«é£ãããã®ãå¯ã§ãã
æãã·ã³ãã«ãªäœ¿ãæ¹
ç°¡åãªäœ¿ãæ¹ãããŸãã*ïŒã¯ã€ã«ãã«ãŒãïŒã䜿çšå¯èœãªã®ã§ãæå®ç¯å²ã®åãå šãŠåºãå Žåã«ã¯ã掻çšããŸããããæ¡ä»¶åŒãšããŠFåã®æ°å€ã100ããäžã®ã¬ã³ãŒããæœåºãããšãã£ãäºäŸã®å Žåã«ã¯ã以äžã®ãããªæ°åŒãšãªããŸãã
1 |
=query('ã·ãŒãå'!A2:F,"select * where F>100") |
è€æ°æ¡ä»¶ã§æœåº
ãŸããç¹å®ã®ã»ã«ã®å€ãæœåºæ¡ä»¶ãšããŠåããããã«æ¥ä»ãæœåºæ¡ä»¶ãšããŠåããããªã±ãŒã¹ã§ã¯ã
1 |
=query(A2:H20,"select E where (C = '"&$D24&"') and (date '"&$B24&"'<=B)") |
ãããªæžãæ¹ã«ãªããŸããD24ã®å€ãæååã®æœåºã§ããã€ã³ãã®4,5ãã³ã¬ã«è©²åœããŸãããŸããæ¥ä»ã®å Žådateãé ã«ä»ããŠãããããããã€ã³ãã®6ã«è©²åœããŠããŸãããã®èšç®çµæãæŽã«SUM颿°ã«é£ãããŠåèšãåºããšãã£ãäºãå¯èœã§ããVBAå ã§å€æ°ãæœåºæ¡ä»¶ãšããŠäœ¿ãæèŠãªã®ã§ãããã°ã©ãã³ã°çãªæ°åŒã®äœãæ¹ã ãšèšããŸãã
ããšããšQueryãªã®ã§ãANDãORã§ç°¡åã«æ¡ä»¶åŒãæ§ç¯åºæ¥ãã®ã§ãè€æ°æ¡ä»¶ã§ã®æœåºã¯Filter颿°ããããæã®ç©ã§ãã
æå®ç¯å²å ã§æœåº
æ¥ä»ãªã©ã«æŒããŠããããããããŸã§ã®æéã§æœåºãšãã£ãå Žåã«ã¯ãdateãé ã«ã€ããŠANDã§ãããã以äžã»ä»¥äžã§æå®ãäœã£ãŠäžãããšæœåºãå¯èœã§ãããã¡ããç¹å®ã®ã»ã«ãåç §ããããšå©äŸ¿æ§ãåäžããã§ããããAãæ¥ä»ã®ããŒã¿ã®å ¥ã£ãŠãåã§ãã
1 |
=query('売äžäžèЧ'!A2:F,"select * where (date '"&$H2&"'<=A) and (date '"&$H3&"'>=A)") |
æ¯èŒæŒç®åã®äœ¿ãæ¹ãããŸãã9.ã®æ³šæç¹ã«ããããã«ãæå®ããæ¡ä»¶åŽã®æ¥ä»åœ¢åŒã¯yyyy/mm/ddã§ã¯ãšã©ãŒã«ãªãã®ã§ãyyyy-mm-dd圢åŒã«ããŠãããå¿ èŠããããŸããæ®éã«æ¥ä»ãå ¥åãããšã¹ã©ãã·ã¥ã«ãªã£ãŠããŸãã®ã§ãã»ã«ã®è¡šç€ºåœ¢åŒã«æŒããŠããæžåŒãªãããã¹ããã«ããŠããããšã¹ã ãŒãºã§ãã
å³ïŒæ¥ä»ã®æå®æ¹æ³ã ãæ³šæãå¿ èŠ
第äžã®åŒæ°
èªåã¯ããŸã䜿ãããšããªãã®ã§ãããQuery颿°ã¯ç¬¬äžã®åŒæ°ããããŸããç¯å²ãã¯ãšãªæããããŠ3ã€ç®ã¯èŠåºãã®æ±ãã§ãããã®èŠåºãã®åŒæ°ã¯ãèŠåºãã®è¡ç®æå®ã衚ããŠããŠãç¯å²æå®ããäžã§èŠåºãã«äœ¿ãã®ã¯äœè¡ç®ãªã®ãïŒãæå®ãããã®ã§ãã2è¡ç®ãèŠåºãã«äœ¿ããªãã2ãæå®ããçç¥ããå Žåã-1ãæå®ããæã¯ãªãŒãã§å€å®ãšãªããŸãã
ãã¡ããã§ãããèŠåºãè¡ãå«ãŸãã«ç¯å²æå®ããå Žåã«ã¯ãåŒæ°ãçç¥ãããšãèŠåºãè¡ãçŸããŸããããã ããåºæ¬çãªäœ¿çšã®å ŽåïŒ1è¡ç®ãèŠåºãã«ãªã£ãŠããŠãç¯å²æå®ã«å«ããŠããå ŽåïŒã¯ãåŒæ°ã¯ã€ããªããŠOKã§ãã
ãŸããåé ã®ããã«è©²åœããèŠåºãã®ãªãèªåã§åãçæããå Žåã«ã¯ãLABELå¥ã«ãŠèŠåºããå ããäºãå¯èœãããšãã°ãF*100ãšããèšç®åãå ãããšé©åœãªååãã€ããŠããŸããŸãããLABEL F*100 '売äžéé¡'ãšãããšã売äžéé¡ãšããã©ãã«ãä»ããŠãããŸãã
éèšãã«ãŠã³ã
SQLæãšåãããQuery颿°å ã§ãéèšãã«ãŠã³ããããäºãå¯èœã§ãã䜿çšã§ããéèšé¢æ°ã¯ãavg(), sum(), count(), max(), min()ã®ïŒã€ãšãªã£ãŠããŸããsumãšcountãå©çšé »åºŠãæãé«ãã§ãããããããããã®åã®åèšãåã£ãŠéèšããäŸãäœã£ãŠã¿ãŸããã
1 |
=query('売äžäžèЧ'!A2:F,"select sum(B),sum(C),sum(D),sum(E) where F>100 LABEL sum(B) 'Aåå売äžé«èš', sum(C) 'Båå売äžé«èš', sum(D) 'Cåå売äžé«èš', sum(E) 'Dåå売äžé«èš'") |
ããããã®å£²äžéé¡ã®ããåã®å šåèšïŒãã ã売äžåèšã100ããäžã®ãã®ïŒãéèšããŠããŸããéèšçãè¡ãå Žåããã€ã³ãã®5.ã«ããããã«ã¿ã€ãã«è¡ã«sumãšåºãŠããŸãã®ã§ãLABELãã€ããŠäžããŠããŸããcountã§ããã°ä»¶æ°ãåºãŠããããã§ããéèšçµæãå¥éSum颿°ã«é£ããããããå¿ èŠããããŸããã
åã®å ¥æ¿ããšèšç®
Access䜿ãã¯ããªãã¿ã®åã®å ¥æ¿ããç°¡åã«ã§ããŸããQuery颿°ã§ã®selectæã¯å¥ã«é çªã«åã䞊ã¹ãå¿ èŠã¯ãªãããŸãå šéšã®åã衚瀺ããŠãããå¿ èŠããªããå¿ èŠãªåãå¿ èŠãªé çªã§å¥œãã«äžŠã¹ãããã®ãç¹åŸŽã§ãããŸããæ¢åã®åã«ã¯ãªãèšç®åãäœã£ãŠè¿œå ããäºãå¯èœããŸãã«ãããã¯ãšãªãŒãšããäœ¿ãæ¹ã§ããã
ã¡ãªã¿ã«ãåã®æå®ãããã«ãã*ããæå®ãããšãå šå衚瀺ã«ãªããŸãã(select *ãšãã£ãå ·åïŒããŸãèšç®åŒã§ã¯å¥ã®é¢æ°ã䜿ã£ãŠã®èšç®ãå¯èœã§ãã
1 2 |
//åãå
¥æ¿ããŠãåæ°åã«100ãæããæ°å€ã売äžåãšããäºäŸ =query(A1:F100,"select C,B,E,F*100 LABEL F*100 '売äžéé¡'") |
å³ïŒåå ¥æ¿ããšå£²äžéé¡åã远å ããŠã¿ã
æœåºçµæãäžŠã¹æ¿ããã
éåžžã®äžŠã³æ¿ã
ãªãŒããã£ã«ã¿ãªã©ã§ããã䜿ãäžŠã¹æ¿ãããã ããªãªãžãã«ã®ããŒã¿ãäžŠã¹æ¿ãããããé£çªãä»ããªãã£ããçºã«ããªãªãžãã«ã®é çªã«çŽããªããªã£ããªããŠããšããã°ãã°ãã ããããããªãªãžãã«ããŒã¿ã¯åŒããã«Query颿°ã䜿ã£ãŠæäœããäžŠã¹æ¿ããããã®ããã®é¢æ°ã®äœ¿ãæã®ïŒã€ã§ãããããå®çŸããã®ããORDER BYå¥ã§åã®æå®ã®åŸã«ãdescã§éé ãascã§æé ãšãªããŸãã
ãŸããWHEREæ¡ä»¶ã§æœåºåŸã®ãã®ãäžŠã¹æ¿ããå¯èœãªã®ã§ããªãŒããã£ã«ã¿ã§æåã§ãããããæ°åŒã§ã³ã³ãããŒã«ããã»ããè²ã ãšäŸ¿å©ã§ãã
1 2 |
//ã¢ãŠã»ã³ãŽã±ã§æœåºããŠãFåãéé ã§äžŠã³æ¿ã =query(A1:F100,"select * where E = 'ã¢ãŠã»ã³ãŽã±' order by F desc") |
å³ïŒæœåºçµæãåæ°ã§éé äžŠã³æ¿ãããŠã¿ã
颿°ã®èšç®çµæãäžŠã³æ¿ã
颿°ã§åŠçãããã®ãQuery颿°ã«é£ããããšããèšç®çµæã«å¯ŸããŠQuery颿°ã䜿ã£ãã±ãŒã¹ã§ã¯ãorder byã«ãŠåã§æå®ãåºæ¥ãŸããããã®å Žå3åç®ãªãã°ãcol3ãšãªããããselect * order by Col3 Descãšãããš3åç®ã§éé ãšãã圢ã«ãªããŸããselect * order by C descã§ã¯ãªãã®ã§æ³šæã§ãã
äŸãã°ä»¥äžã®ããã«{ }ã§æ¬ãããè€éãªé¢æ°ã®èšç®çµæã¯é åã§åž°ã£ãŠæ¥ãŸãããããã£ãå Žåã«ã¯ Colã§åãæå®ããããšã§ãœãŒããå¯èœã«ãªããŸãã
1 2 |
//Memberã·ãŒããšemployeeã·ãŒããã¡ã¢ãã§çµå =query({member!A2:B,iferror(arrayformula(vlookup(member!B2:B,employee!A2:D,{2,3,4},false)))},"select * order by Col3 Desc") |
åããQuery颿°ã«Importrange颿°ãé£ãããŠã®äžŠã³æ¿ããåã®éžæãColã§æå®ããå¿ èŠããããŸãã
1 2 |
//importrangeã®çµæãé£ããã =query(importrange("https://docs.google.com/spreadsheets/d/xxx","売äžäžèЧ!A:F"),"select Col1, Col2") |
å³ïŒãœãŒãããå Žåã¯ä»æ§ã«æ³šæãå¿ èŠ
æååãšæ°åã®æ··åšããåã§çºçãããã°
éåžžãåã«æŒããŠããŸãæååãšæ°å€ãæ··åšããã±ãŒã¹ã¯å€ããããŸããããæ±ºããŠç¡ãããã§ã¯ãããŸããããã®ãããªåãæã£ãŠããããŒã¿çŸ€ã«å¯ŸããŠãQuery颿°ãå©çšãããšãã«ãããŒã¿ãååšããªããããã¯ç©ºçœã§è¿ã£ãŠããè¬çŸè±¡ããããŸãã
ãã®çŸè±¡ã¯ãåå ã§æååã®å€ãæ°å€ã®åããããæååã®åã®ååšæ°ãå°ãªãå Žåãèªåã§æžåŒèšå®ãåããã空çœããšèŠåãããŠããŸãèµ·ããŠãçŸè±¡ã§ããStackOverFlowã§ããã®åé¡ãšè§£æ±ºçãæç€ºãããŠããŸãããã ããã®è§£æ±ºçã¯æ°åŒãé£è§£ãªãã®ã«ãªã£ãŠããŸãã®ã§ã以äžã®ãããªææ®µã䜿ã£ãŠè§£æ±ºããããšè¯ãã§ãããã
- ãããããããã®åã¯ãã¹ãŠæžåŒèšå®ã«æŒããŠãæžåŒãªãããã¹ããã«å€æŽãè¡ã£ãŠããã
- æžåŒèšå®ãé£ããå Žåã«ã¯ãæå®ã®åããŸãã¯å¥ã®é¢æ°ã§ãTO_TEXT颿°ãã«ãŠãæåååã«å€æãããŠããããã®çµæãQuery颿°ã«é£ãããæ¹æ³
åè ã¯ç°¡åãªæ¹æ³ã§ãããããããããªããšããã±ãŒã¹ãããã®ã§ããã®å Žåã¯2çªç®ã®ææ³ãå©çšããããšã«ãªããŸãããŸãã2çªç®ã®ææ³ã®å ŽåãTo_TEXT颿°ã§å€æãããããã®ã«å¯ŸããŠãQuery颿°ã䜿ãã®ã§ã1åç®ã¯Col1, Col2ãšååãå€ããã®ã§æ³šæïŒïŒ
1 2 |
//確ãã«ããŒã¿ã¯ããã®ã«å€±æããäºäŸ =QUERY(A2:C7,"select A, B, C where C='A200'") |
æ°åã»æåæ··åšã®åã«å¯ŸããŠãCåã«ãŠæååã§ã®ããŒã¿æœåºãæãããšN/Aã«ãªããæååã®ããŒã¿ãå¢ãããšãã¡ããšãä»åºŠã¯é¢æ°ãæ£ããåãã»ã»ã»æååããŒã¿ã¯æ°å€ãšå€æãããŠãèšç®äžã¯ç©ºçœã®æ±ãã«ãªãããã ã
1 2 |
//TO_TEXTã§å€æããŠããQuery颿°ã§åŠç =QUERY(ARRAYFORMULA(TO_TEXT(A2:C7)),"select Col1, Col2, Col3 where Col3='A200'") |
å³ïŒæåãšæ°å€ã¯ãªãã¹ãæ··åšããªãããã«ããªããã°
ãã®æ¹æ³ã§ãã£ãŠããæ¥ä»åã®å€ãããã«å ¥ã£ãŠããå Žåãæ¥ä»ã«ãŠWhereæ¡ä»¶äžæœåºãã§ããªããªã£ããããã®ã§ãããšããšã®èšè𿮵éã«ãŠããã®åã«ã¯æå®ã®å以å€ã®å€ã¯å ¥ããªãããã«æ³šæãã¹ãã ãšæããŸãã
Query颿°ã®çµæãå¥ã®é¢æ°ã«é£ããã
Query颿°ã®å Žåã颿°åäœã§æ®ã©ã®äºãå®çŸå¯èœãªã®ã§ã䜿ãã·ãŒã³ã¯å°ãªãã§ãããä»ã®é¢æ°åæ§è¿ã£ãŠããé åããŒã¿ãæŽã«å¥ã®é¢æ°ã«é£ãããŠåŠçãè¡ãããäºãå¯èœã§ããSumãªã©å Žåã¯Query颿°èªäœã«éèšæ©èœãåãã£ãŠãã®ã§ãå¥éé£ããããšãã£ãããšãããŸãããããã®ä»ã®é¢æ°ã䜿ãå Žåã«ã¯æå¹ã§ãã
ãŸããé åã§çµæãè¿ã£ãŠãããã®ãå¥ã®é¢æ°ã«é£ãããŠããç¹ãExcelã«ã¯ãªãæèŠã§ãããä»åã¯å¹³åå€ã§ã¯ãªãçµ±èšãªã©ã§å©çšãããäžå€®å€ïŒã¡ãžã¢ã³ïŒã®é¢æ°ã§çµã¿ç«ãŠãŠã¿ãŸããã
1 |
=MEDIAN(query('売äžäžèЧ'!A:E,"select E")) |
EåïŒDåå売äžé«ïŒã®æ°å€ã®é åã«å¯ŸããŠãMedian颿°ã§äžå€®å€ãåã£ãŠããŸããçµ±èšã§ã¯ããå©çšããã颿°ã§ãããæ¥µç«¯ã«é«ãæ°å€ããããšå¹³åå€ãäžãã£ãŠããŸããçŸå®çã§ã¯ãªãæ°åã«ãªããŸããããã§ããéãŸã£ãŠãå¡ã«å¯ŸããŠã ãå¹³åãåããããªé¢æ°ã§ãå¹³åå€ãããçŸå®çãªæ°å€ãæ±ããããŸãã
å³ïŒå¹³åå€ãšæ¯ã¹ãŠã¿ãã
ã¯ãã¹éèšãè¡ã
Googleã¹ãã¬ããã·ãŒãã«ã¯ãExcelåæ§ã«ããããããŒãã«æ©èœãçšæãããŠããã®ã§ãåé¡ãªãã¯ãã¹éèšãå¯èœã«ãªã£ãŠããŸããããããããŒã¿ã®äºæ¬¡å©çšãšãªããšããã®åœ¢åŒã§ã¯æ£çŽäœ¿ãã«ãããAccessçã§ã¯ãã¯ãã¹éèšã¯ãšãªããŸãããŒãã«ãšããŠæ±ããã®ã§ãæŽãªãèšç®ãªã©ãå¯èœã«ãªã£ãŠããããã©ããããGoogle Spreadsheetäžã§ããããã«ã¯ãããããããŒãã«æ©èœã䜿ã£ãŠã®äœæã§ã¯ã¡ãã£ãšå°é£ã§ãã
ãšããããšã§ãQuery颿°ãæšãããšæŽ»çšããããšã§ãAccessã®ããã«äºæ¬¡å©çšã«æé©ãªåœ¢ã§ãæ¥ã ã®å ¥åããŒã¿ãã¯ãã¹éèšåããã·ãŒãã«å±éããäºãåºæ¥ãŸããè€éã§ã¯ããã®ã§äžäººã«ãªã¹ã¹ã¡ãšããèš³ã§ã¯ãããŸããããæšæºã®é¢æ°ã®ã¿ã§è¡ããã®ã§ãã¹ããŒããæ©ãç¹ããããŠäœãããã¹ã¯ãªãããçµãŸãã«å±éãåºæ¥ãã¹ã¯ãªããããäºæ¬¡å©çšãåºæ¥ããšããã®ã¯éåžžã«éœåã®è¯ããã®ã§ããã°ã©ãäœæã§ããæè»ã«èªåçã«ã°ã©ãå±éãæåŸ ã§ããã®ã§ããªã¹ã¹ã¡ã§ããã
äºåæºå
ä»åæ³å®ããŠããã®ã¯ã以äžã®ãããªããŒã¿ã«ã©ã ã®æ¥ã ã®å ¥åããŒã¿ãããã¯ãã¹éèšåããŠå¥ã®ã·ãŒãã«å±éããã±ãŒã¹ã§ããä»å䜿çšããã¹ãã¬ããã·ãŒããå šãåãããŒã¿æ§é ãªã®ã§ããã¡ããèŠãŠããã£ãã»ãããçè§£ã¯æ©ããããããŸããããäžèšã®ããŒã¿ããŒãã«ããããåååããåºæºã«ã¯ãã¹éèšãè¡ã£ãŠè¡ããŸãã
販売å ã³ãŒã | æ¯åºå | æ¥ä» | ID | ååå | 売äžåæ° |
---|---|---|---|---|---|
101 | æžè°·æ¯åº | 2015/5/1 | 1 | ããšããªãœãŠ | 300 |
102 | 梶ãè°·æ¯åº | 2015/6/2 | 2 | ãµã©ã»ã㢠| 150 |
ãããã¯ãã¹éèšããŠå±éããæã«ã¯ä»¥äžã®ãããªæ§é ã§è¡åãèšå®ããå±éãè¡ããŸããåå忝ã«å£²äžåæ°ãéèšããèš³ã§ãã
販売å ã³ãŒã | æ¯åºå | ããšããªãœãŠ | ãµã©ã»ã㢠|
101 | æžè°·æ¯åº | 30 | 40 |
101 | æžè°·æ¯åº | 25 | 12 |
ããŠãããã§ã¯ãã¹éèšåããæã®ã€ã¡ãŒãžã¯åºæ¥ãã®ã§ãå®éã«å®ããŒã¿ãåºã«ãQuery颿°ã䜿çšããŠããããåãè¡ããŸããæ®éã¯ã³ã³ã§ãããããããŒãã«ã䜿ãæã§ãããä»åã¯äœ¿çšããŸããããããŸã§ãQuery颿°ã§äºæ¬¡å©çšã®çºã«ããŒã¿ããŒãã«ãäœãã®ãç®çã§ãããã
ã¯ãã¹éèšãããŠã¿ã
ããŠå®éã«äœ¿çšããŸããQuery颿°èªäœã®äœ¿ç𿹿³ã«ã€ããŠã¯ãããä»ã®ç¬èªã®é¢æ°ãšãŸãšããŠç޹ä»ããããšæããŸãã
ä»åã®æ°åŒã¯ä»¥äžã®ããã«ãªããŸããSQLããã¯ã§ãããç¬èªã®Pivotãšããé ç®ãä»åã®èã«ãªããŸããGroup Byã§ã©ããŸã§ã°ã«ãŒãã³ã°ãããã¯ããŠãŒã¶ãã©ããŸã§åãæ®ããã«ãããŸããä»åã¯ãæ¥ä»ã®éšåãå€ããã°ã«ãŒãåã®å¯Ÿè±¡ã«ã¯ããŸããã§ããã
1 |
=QUERY(database!A2:F, "SELECT A,B,sum(F) Group By A,B Pivot E LABEL A '販売å
ã³ãŒã', B 'æ¯åºå'") |
å³ïŒã¹ã¯ãªããããã®äºæ¬¡å©çšããšãŠããããã
ã«ã©ã ã®ãœãŒãã¯ã§ããªãã®ã§ã»ã»ã»
Query颿°ã¯ãæ§æã®äžã§Pivotã䜿ã£ãå ŽåãORDER BYãå©çšã§ããŸããããã®çºãPivotã䜿ã£ãã¯ãã¹éèšã䜿ã£ãæã«ãäŸãã°ãæ¥ä»ããã«ã©ã ã«åã£ãå Žåãé çªããªã«ã·ãäºã«ãªããŸãïŒ1æ¥ã®æ¬¡ã10æ¥ã2æ¥ã®æ¬¡ã20æ¥ãšãã£ããããªæãã§åã䞊ãã§ããŸãïŒã
åºå€ç°¿ããæ¥ä»ã暪ã«ãšã£ãŠèŠãŠããæã«ããã§ã¯å ·åãæªãã§ããäœããæ°åã®ã¿ã§ãããªãã°ããã¡ããšäžŠãã ãããã®ã§ããæ¥ããšãã£ãæåãå ¥ããšãé çªããããããªãããã§ãããã®å Žåãæ°åã®éšåã«æŒããŠ1æ¡ã®æ°åã«ã¯0ãé ã«å ããŠã01æ¥ããšããŠãããŠã2æ¡ã®æ°åã¯æ®éã«ã10æ¥ããšããŠãããããšã§ããã¡ããšäžŠã³ãŸãã
äžèšã®å³ã¯ããããåãæ§æã§ããã䞊ã³ãéãã®ãããããšæããŸããQuery颿°ã§ãã¡ããšPivotã§äžŠã¹ããæã¯ã颿°ã§ã¯ãªãããŒã¿åŽã«çް工ãå¿ èŠãªã®ã§æ³šæãå¿ èŠã§ãã
å³ïŒè¯ãäºäŸ
å³ïŒæªãäºäŸ
ãã€ã³ã
- 䜿çšããåã¯ãDå以å€ã®å šãŠã§ããã衚瀺ããè¡ã¯ãAïœCãããŠãéèšãšããŠFåã®å€ãæå®ããŠããŸãã
- Group Byã«ãŠã°ã«ãŒãåãæå®ããŸããAïœCãŸã§ãã°ã«ãŒãåã®å¯Ÿè±¡ãšããŠæå®ããŠããŸãããã®èŸºãã¯ãAccessã§ã¯ã銎æã¿ã§ããã
- Pivotã«ãŠåæå®ãšããŠEåãæå®ããŠããŸãã1.ã§éèšå¯Ÿè±¡ã«ããå 容ããããã§åå忝ã«éèšè¡šç€ºãããããã§ãã
- LABEL以äžã¯èŠããªããšèšãã°èŠããªãã®ã§ãããä»»æã®åã©ãã«ãä»ããäºãã§ããŸããæšæºã ãšã©ãã«ããªãçºãAïœCãŸã§ã©ãã«æå®ããŠãŸãã
- äœããEåã®Pivotã§æå®ããç®æã¯ãèªåçã«ååãšããŠåååãä»äžãããããã«ãªã£ãŠããŸãã
- å€ã¯é åã§è¿ãããã®ã§ãFilter颿°åæ§èªåçã«ã¹ãã¬ããã·ãŒããæ¡åŒµãããA1ã«åŒãå ¥ããå Žåããããåºæºã«å€ãèªåçã«å±éãããŸãã®ã§ã誀ã£ãŠæ¶ããŠã埩å ãããŸãã
- ããšã¯äœæãããããŒã¿ããŒãã«ã®å€ãä»ã®ã¬ããŒãçšã·ãŒãããvlookupã§åã£ãããã«ãŠã³ãããããã°ã©ããäœãã°OKã§ãã
é¢é£ãªã³ã¯
- GAS:GASã§SpreadSheetã®çµã¿èŸŒã¿é¢æ°ïŒQuery颿°ïŒã䜿ã
- google spreadsheetã®query颿°
- Query颿°ã«ã€ã㊠â Google Developer
- Query a Google Spreadsheet like a Database with Google Visualization API Query Language
- Google Spreadsheets ãç°¡æ SQL DB ã«ïŒãGoogle Visualization APIã
- Microsoft Power Query for Excel ã®æŠèŠ
- å¹³åå€ãšäžå€®å€ã®éã
- MEDIANïœããŒã¿ç¯å²ã«ãããæ°å€ã®äžå€®å€
- ã¹ãã¬ããã·ãŒãã®QUERY颿°ã«ãŠãSELECTããéã«çœ®æïŒREGEXREPLACEïŒãããã