Vlookupããã¯ããã«ç°¡åã§æŽç·ŽãããããŒã¿åŠçã®å°éããŒã«ããã¯ãŒã¯ãšãªããåŠãã§ ä»äºãåããŸãããïŒ
以åã«äœ¿ã£ããšã¯ã»ã«ãšæè¿ãšã¯ã»ã«ã®éãããåç¥ã§ããïŒåŸæ¥ã¯ãããŒã¿ãçµåãããšããããã ãã¬ããããããVlookupã䜿ãå Žåãã»ãšãã©ã§ãããã§ããããŒã¿ãå€ããšãã«ãªã©ã¯äœ¿ãã«ããã§ãããããè£ãããã«ãæè¿ãExcelã«ã¯ããŒã¿æœåºã®å°éæ©èœã§ããPowerQueryã 远å ãããŸããã
1 ãã¯ãŒã¯ãšãªãšã¯ïŒ
åŸæ¥ãMSã®ã¢ã¯ã»ã¹(Access)ã§äœ¿çšãããŠããããŒã¿æœåºæ©èœã§ãããã¯ãšãªããExcelã§äœ¿çšã§ããããã«ããæ©èœãããŸãã«ããã¯ãŒã¯ãšãªãã§ããç°¡åã«èšãã°ããã¬ããšVBAã®äžéããžã·ã§ã³ã«äœçœ®ããããŸã£ããã®ããŒã¿å å·¥ãè¡ããããŒã¿å å·¥å°éããŒã« ãšãããŸãã
ãªãã³ã¡ãã¥ãŒ > éçºããŒã«(ãªãã·ã§ã³-ãªãã³ãŠãŒã¶ãŒæå®-éçºããŒã«..ãã§ãã¯) > ãšã¯ã»ã«è¿œå æ©èœ > åæããŒã«ãæ¢çŽ¢ãã§ãã¯ããã¯ã¹ã«ãã§ãã¯ããã ãã§
å°éçµ±èšåæããŒã« ã§ã§ããã»ãŒã»ãšãã©ã®
å°éåææ©èœ ãæ±çšçãªãšã¯ã»ã«ããã°ã©ã ã§äœ¿çšã§ããŸãããã¡ããçµæå€ãåãã§ãã
ããã®åææ©èœã䜿ããããã§ãããã¯ãŒã¯ãšãªããã¡ããšåŠã³ãããªãã§ããïŒ
2 ãã¯ãŒã¯ãšãªãå¹ççã§è¯ãçç±ïŒ 1. æ¯æã®ããŒã¿éèšåŸã«å ±åãè¡ãå¿
èŠãããå Žåãç¹å®ã®ãã©ã«ãå
ã«å«ãŸããŠãã倿°ã®Excelãã¡ã€ã«ã®ããŒã¿ãäžã€ã«ãŸãšããå
äœæ¥ãå¿
èŠã§ãããã®æãæ¢åã®ãšã¯ã»ã«ãšææ°ã®ãšã¯ã»ã«ã¯ä»¥äžã®ãããªéãããããŸãã
æ¢åã®Excel
ãã¯ãŒã¯ãšãªïŒææ° ãšã¯ã»ã« ïŒ
- ãã¡ã€ã«åã蟌ã¿ã ã·ãŒãåãèŸŒã¿æ©èœãªã - ãã¬ãïŒ çŠã ä»ïŒãŸã㯠VBA ã³ãŒãã£ã³ã°ã§ãã©ãã«ã·ã¥ãŒãã£ã³ã°
- ãã¯ãŒã¯ãšãªãã¡ãã¥ãŒãšããŠæäŸããã®ã§ ã¯ãªãã¯ããã ãã§ãã¡ã€ã«çµ±åã ã·ãŒãçµ±åãäžå®€ã§è§£æ±º - å
ã®ããŒã¿ãå€ãã£ãå Žåã ã æŽæ°ã ãã¿ã³ãæŒãã ãã§èªåçã«æŽæ°
2. çŸæ¥ã§Vlookup颿°ã§ããŒã¿ãçµåããäœæ¥ã¯ãããããããŸãã ãã®æãæ¢åã®ãšã¯ã»ã«ãšæè¿ã®ãšã¯ã»ã«ã¯ä»¥äžã®ãããªéãããããŸãã
æ¢åã®Excel
ãã¯ãŒã¯ãšãªïŒææ° ãšã¯ã»ã« ïŒ
- çŸæ¥ã§ãã䜿ãããVlookup 颿°ã 䜿çšãããšã ããŒã¿ãå€ããšåŠçé床ãäœäžããŸãã - æ¥ç¶å
ããŒã¿ãç§»åãŸãã¯æ¶å€±ããå ŽåVlookup 颿°ã§èšå®ããããã¡ã€ã«ãšã©ãŒãçºçããŸãã
- ã¡ãã¥ãŒã¯ãªãã¯ã§ ããŒã¿ã®çµåãç¬ãããŸãã é£çµå
ããŒã¿ã®ç§»åãšæ¶å€±ã«å¯Ÿå¿ã§ããã¡ãã¥ãŒæäŸ
3. ãã®ä»ã«ããŠã§ãããŒã¿ããããã«ã€ã³ããŒãã§ãã颿°ã䜿ããã«ããã¹ãæœåºå¯èœã ããŒã¿ã®æŽåœ¢åãããããããŒãã«ã®æ¬ ç¹è£å®ãªã©ãè€éãªããŒã¿å å·¥ã容æã«ã§ããŸãã
3 ãã®è¬çŸ©ã ãã®åŒ·ã¿ çŸåšãšã¯ã»ã«ã®æããã¬ã³ãã£ãªããŒã¿å å·¥ãåæã¹ãã«æç€ºïŒ ãããŸã§ã®å°æ¥ã®ããŒã¿å€ã®äºæž¬ã¯ãå°éçµ±èšåæããŒã«ãïŒSASãRãMINITAP..ïŒäœ¿çšããããšãç¥ã£ãŠããéããã人ã«ã®ã¿å¯èœãªåéã ãšèããŠããŸããããªããªããExcelãã¯ãŒãŠãŒã¶ãŒãExcelã«ãã®ãããªæ©èœããããšã¯ç¥ã£ãŠããŸãããçµ±èšåŠã«åºã¥ããŠèšèšãããæ©èœã ãããåºæ¬çµ±èšç¥èããªããã°ãã®çµæå€ãè§£éã§ããŸãããäžè¬ãŠãŒã¶ãŒãåºæ¬çãªçµ±èšç¥èãæã£ãŠããå Žåãã»ãšãã©ãªãããã§ãã ãã®ã³ãŒã¹ã§ã¯ãExcelã®çµ±èšåŠã«åºã¥ãããåæãæ©èœ ãæè»ã«å®è·µã«é©çšã§ããããã«ããããã®åºæ¬ç㪠統èšç¥èã æããŠãããŸãã
4 ãã®è¬çŸ©ã§åŠã¹ããã®
Business Intelligence PlatformãšããŠã®Excelãèªããã¯ãŒã¯ãšãªã§ãçŸæ¥ã®æŽåœ¢ãéæŽåœ¢åãããŠããããŒã¿ãå å·¥ããæ¹æ³ãåŠç¿ããŸãã
ã¹ã©ã€ãµãŒãããããããŒãã«æ¥ç¶æ©èœãæ¡ä»¶ä»ãæžåŒã掻çšããŠãéèšããããããããèŠäºã«å¯èŠåããããããªvbaã³ãŒãã§èªåçã«ããããããŒãã«ããªãã¬ãã·ã¥ããŸãã
çµ±èšã¢ãã«ã«åºã¥ããŠèšèšãããExcelã®çµ±èšåææ©èœã®ãã¡ãæè¡çµ±èšæ³ã§ããŒã¿ã®å±æ§ãäžç®ã§ææ¡ããååž°åæã§ããŒã¿ã®ç§åŠçãªå°æ¥äºæž¬ ããŸããåŠç¿ã¯çµ±èšã®åºæ¬ç¥èãç°¡åã«åŠã³ãã¬ããŒããç§åŠç ã«æžãããšãã§ããŸãã
ããŒã¿ã®æé©ãªçµã¿åãããèŠã€ããæ¹æ³ãç°¡åã«çè§£ããå®åã«é©çšã§ããŸããæ€çŽ¢æ©èœã䜿çšãããšãããžãã¹ã®äžçã§æéã®ãªãœãŒã¹ãæã€æé©ãªããŒã¿ã®çµã¿åãããèŠã€ããŸãã