<p>ãã¾ã§ããããã«ã¡ã¯</p>
<p>ãä»äºã§ã¨ã¯ã»ã«ã使ã£ã¦ããã¨ã</p>
<p>ã顧客åç°¿ãªã©ãä½æé²ã®ä½æããé½éåºçã ããæãåºããããã<br />
ãä½æããé½éåºçåã ããæãåºãã«ã¯ã©ããããããã®ã§ãããï¼ã</p>
<p>ã¨ãã質åãåãããã¨ãè¯ãããã¾ãã</p>
<p>ãã§ããã ãç°¡åã«ã§ããæ¹æ³ã§VBAãªã©ã使ããã«æ¸ãæ¹æ³ãæãã¦ã»ããã»ã»ã</p>
<p>ãããªãè¦æã«ãå¿ããã¦ãç°¡åãã¤ã·ã³ãã«ã«ã§ããæ¹æ³ããç´¹ä»ãã¾ãã</p>
<p><!--more--></p>
<h3>é½éåºçãåãåºãã«ã¯</h3>
<p>ã¾ãåºæ¬çãªã¨ããããã</p>
<p>ã¨ã¯ã»ã«ã§æååãæãåºãã«ã¯&#8221;LEFT,MID,RIGHTé¢æ°&#8221;ã使ãã¾ãã</p>
<p>LEFTãä¾ã«èª¬æãã¾ãããã</p>
<p>LEFTé¢æ°ã®æ§æã¯</p>
<p><strong>=LEFT(æåå,[æåæ°])</strong>ã§ãã</p>
<p><a href="http://kumano-okini.com/wp-content/uploads/2015/10/LEFT.png"><img class="aligncenter size-full wp-image-260" src="http://kumano-okini.com/wp-content/uploads/2015/10/LEFT.png" alt="LEFT" width="554" height="182" /></a></p>
<p>å³ãè¦ã¦ããã ããã¨åããã¾ããã<br />
ããã ã¨3æåã®é½éåºçã¯ãããã«æãåãã¾ããã<br />
&#8220;ç¥å¥å·ç&#8221;ã&#8221;åæå±±ç&#8221;ã&#8221;鹿å 島ç&#8221;ãªã©ã®4æåã®çã¯ãçãã®æåãåãã¦ãã¾ã£ã¦ãã¾ãã</p>
<p><strong><span style="background-color: #ffff99;">FINDé¢æ°ã¨çµã¿åããã</span></strong></p>
<p>&#8220;FINDé¢æ°&#8221;ã¨ã¯æååããç¹å®ã®æåãæ¤ç´¢ãã¦ä½çªç®ã®æåããæãã¦ãããé¢æ°ã§ãã</p>
<p>FINDé¢æ°ã®æ§æã¯</p>
<p><strong>=FIND(&#8220;æ¤ç´¢æåå&#8221;,[対象],[éå§ä½ç½®]ï¼çç¥å¯)</strong>ã§ãã</p>
<p>ããã§ã¯æ¤ç´¢æååã&#8221;ç&#8221;ã¨ãã¦ã¿ã¾ãããã<br />
対象æåãã¼ã¿ã®ä¸ã§ããçããå«ãå ´åãä½çªç®ããæãã¦ããã¾ãã</p>
<p><a href="http://kumano-okini.com/wp-content/uploads/2015/10/FIND1.png"><img class="aligncenter size-full wp-image-257" src="http://kumano-okini.com/wp-content/uploads/2015/10/FIND1.png" alt="FIND1" width="592" height="174" /></a></p>
<p>å³ã®ããã«ã該å½ããæååãç¡ãå ´åã¯ã&#8221;#VALUE!&#8221;ã¨è¡¨ç¤ºããã¨ã©ã¼ã«ãªãã¾ããã<br />
該å½ããæååãããå ´åã¯ããã®æåãå·¦ããæ°ãã¦ä½çªç®ããæãã¦ããã¾ãã</p>
<p>&#8220;ï¼&#8221;ã&#8221;ï¼&#8221;ã®æ°åã表示ããã¦ãã¾ãããç¥å¥å·çã®å ´åãå·¦ãã4çªç®ã¨ãããã¨ã§ãã<br />
å³ã§è¨ãã¨ã2è¡ç®ã®ãã¼ã¿ãçã¯4æåç®ã«ããã¾ãã</p>
<p>ã§ããé½éåºçã¯ãé½ãéãåºãçãã®ï¼ç¨®é¡ã®æåãããããæ¢ããªãã¨ããã¾ããã</p>
<p>ãã®å ´åã¯ã©ãããã°ããã§ãããï¼</p>
<p>ããã¯è¡åã®èãæ¹ãé¢æ°ã«çµã¿è¾¼ããã¨ã§å¯è½ã«ãªãã¾ãã</p>
<p>ãã®æ¹æ³ã¯æ¡ä»¶ã«ï½ï½ã使ãã¾ã<br />
ãã£ãã®å½¢ãå°ãéãã¾ããã</p>
<p><strong>=FIND({&#8220;é½&#8221;,&#8221;é&#8221;,&#8221;åº&#8221;,&#8221;ç&#8221;},B3)</strong></p>
<p>ã¨ãããããã®æ°å¼ãåä½ã§ä½¿ãã¨ä¸çªå·¦ã®æåãããã§ã¯&#8221;é½&#8221;ãå«ãå ´å以å¤ã¯ã¨ã©ã¼ã«ãªã£ã¦ãã¾ãã¾ãã<br />
ãããåé¿ããã«ã¯ããã®è¨ç®å¼ã&#8221;COUNTé¢æ°&#8221;ã¨çµã¿åããããã¨ã§å¯è½ã«ãªãã¾ãã</p>
<p>ãªããããªãããã¯ããããç©ã ã¨æã£ã¦ãã ããã</p>
<p>COUNTé¢æ°ã¯ãç¯å²å ã®æ°å¤ã®æ°ãæ°ããé¢æ°ã§ãã<br />
é常ã¯=COUNT(æ°å¤1,æ°å¤2･･･ï¼åã¯=COUNT(ç¯å²ï¼ãæå®ãã¾ãã</p>
<p>å ·ä½çã«æ°å¼ã«çµã¿è¾¼ãã«ã¯</p>
<p><strong>=(COUNT(FIND({&#8220;é½&#8221;,&#8221;é&#8221;,&#8221;åº&#8221;,&#8221;ç&#8221;},B3)))</strong>ã¨ãã¾ãã<br />
çµæã¯å¯¾è±¡æåå&#8221;B3&#8243;ã®ãªãã«ãè¡åã§æå®ããæ¡ä»¶ã«ä¸è´ããæåã®æ°ãæãã¦ããã¾ãã</p>
<p>å³ã§ã¿ãã¨åãããããã§ããã</p>
<p><a href="http://kumano-okini.com/wp-content/uploads/2015/10/COUNT-FIND1.png"><img class="aligncenter size-full wp-image-255" src="http://kumano-okini.com/wp-content/uploads/2015/10/COUNT-FIND1.png" alt="COUNT+FIND1" width="730" height="183" /></a></p>
<p>ããã¾ã§ã§ãã£ããã¨ã¯ãä½æãã¼ã¿ã®ä¸ã«ãé½ãéãåºãçã®åæåãå«ã¾ãããã®å¤å®ã§ãã</p>
<p>çµæã0ãã大ãããã°æå®ããæååãå«ãï¼é½éåºçã®ããããã§ãããã¨ãããã¨ãåããã¾ãã</p>
<p><a href="http://kumano-okini.com/wp-content/uploads/2015/10/COUNT-FIND2.png"><img class="aligncenter size-full wp-image-256" src="http://kumano-okini.com/wp-content/uploads/2015/10/COUNT-FIND2.png" alt="COUNT+FIND2" width="729" height="193" /></a></p>
<p>ã¡ãªã¿ã«ãå¤å½ã®å½åãå ¥ããå ´åã¯çµæã¯å³ã®éãã0ã¨ãªãã¾ãã</p>
<h3>é½éåºçã®æåæ°ã¯ã©ãå¤æããï¼</h3>
<p>ãã¦ãããããé½éåºçåãæãåºãé¢æ°ã¸ã¨é²åãããããã§ããã</p>
<p>é½éåºçåã«ã¯ãããæ³åã¨ããããç¹å¾´ãããã¾ãã</p>
<p>ããã¯ã3æåã¾ãã¯4æåã§ããã¨ãããã¨ã§ãã</p>
<p>47é½éåºçã®ãã¡ã4æåã®é½éåºçã¯<br />
ãç¥å¥å·çãããåæå±±çããã鹿å 島çãã®ï¼ã¤ã ãã§ãã</p>
<p>å ¨ã¦ãçãªã®ã§é½åãè¯ãã§ããã</p>
<p>ã¤ã¾ããä½æã®ãã¼ã¿ã®4æåç®ãæãåºããã¨ãã«ããã®æåããçãã§ããã°<br />
ä¸è¨3ã¤ã®çã®ããããã§ãã£ã¦ãããã¯4æåã ã¨ãããã¨ã§ãã®ã§ã<br />
ãããè¨ç®å¼ã«çµã¿è¾¼ãã§ããã¾ãã</p>
<p><strong><span style="background-color: #ffff99;">4æåç®ãæãåºãã«ã¯&#8221;MIDé¢æ°&#8221;ã使ãã¾ãã</span></strong></p>
<p>MIDé¢æ°ã¯ãæååã®ä¸ã»ã©ã«ããæåãæå®æåæ°åæãåºãé¢æ°ã§ãã</p>
<p>MIDé¢æ°ã®æ§æã¯</p>
<p><strong>=MID(対象æåå,éå§ä½ç½®,æåæ°)</strong>ã§ã</p>
<p><a href="http://kumano-okini.com/wp-content/uploads/2015/10/MID.png"><img class="aligncenter size-full wp-image-261" src="http://kumano-okini.com/wp-content/uploads/2015/10/MID.png" alt="MID" width="534" height="195" /></a></p>
<p>å³ã§è¦ãéããçµæã«ãçãã表示ããã¦ããã®ã¯ç¥å¥å·çã¨åæå±±çã ãã§ããã</p>
<p>ããã¦çµæããçãã§ãããã©ããã®å¤å®ã«ã¯&#8221;IFé¢æ°&#8221;ã使ãã¾ãã</p>
<p>IFé¢æ°ã¯ãæ¡ä»¶ã«ä¸è´ããå ´åã¨ä¸è´ããªãå ´åã«ããããã®çµæãåæ ãããé¢æ°ã§ãã<br />
ãæ¡ä»¶ã«ä¸è´ãããã¨ããã®ããçã®çµæãã¨å¼ã³ã¾ã<br />
ãæ¡ä»¶ã«ä¸è´ããªããã¨ããã®ã¯ãå½ã®çµæãã¨å¼ã³ã¾ãã</p>
<p>IFé¢æ°ã®æ§æã¯</p>
<p><strong>=IF(æ¡ä»¶ï¼è«çå¼ï¼,ä¸è´ããå ´åã®çµæï¼çã®å ´åï¼,ä¸è´ããªãå ´åã®çµæï¼å½ã®å ´åï¼ï¼</strong></p>
<p>å ã»ã©ã®å³ã«å ããå½¢ã§è¦ã¦ã¿ã¾ãããã</p>
<p><a href="http://kumano-okini.com/wp-content/uploads/2015/10/MID2.png"><img class="aligncenter size-full wp-image-262" src="http://kumano-okini.com/wp-content/uploads/2015/10/MID2.png" alt="MID2" width="765" height="195" /></a></p>
<p>ã覧ã®ããã«ç¥å¥å·çã¨åæå±±çã¯ã4æåãã§ãã以å¤ã¯ã3æåï¼ãã¨ãªã£ã¦ãã¾ãã</p>
<p>ã ãã¶è¤éã«ãªã£ã¦ãã¾ãããã大ä¸å¤«ã§ããï¼¾ï¼ï¼¾ï¼</p>
<p>ä¸ã®å³ã§ã¯ãçµæãããããã4æåããã3æåï¼ãã§ããããã¦ãã¾ããã<br />
欲ããçµæã¯é½éåºçåãªã®ã§ãããããã®æåæ°ãæãåºãé¢æ°ã«ç½®ãæãã¾ãã<br />
åæã«ã4æåç®ã®å¤å®ãæ°å¼ã«çµã¿è¾¼ã¿ã¾ãã</p>
<p>ãããªæ°å¼ã«ãªãã¾ãã<br />
<strong>=IF(MID(B3, 4, 1)=&#8221;ç&#8221;, LEFT(B3, 4), LEFT(B3, 3))</strong></p>
<p>å³ã§è¦ã¦ã¿ã¾ãããã</p>
<p><a href="http://kumano-okini.com/wp-content/uploads/2015/10/IF-MID-LEFT.png"><img class="aligncenter size-full wp-image-258" src="http://kumano-okini.com/wp-content/uploads/2015/10/IF-MID-LEFT.png" alt="IF+MID+LEFT" width="697" height="191" /></a></p>
<p>ããã§ã»ã¼åé¡ããã¾ããããæå¾ã®ã·ã³ã¬ãã¼ã«ã ãã¯ããããã§ããã</p>
<p><br />

<!-- WP QUADS Content Ad Plugin v. 3.0.5.1 -->
<div class="quads-location quads-ad1" id="quads-ad1" style="float:none;margin:10px 0 10px 0;text-align:center;">

</div>
</p>
<h3>é½éåºçãå«ã¾ãªãæååãå ¨é¨è¡¨ç¤ºãã</h3>
<p>ããã解決ããã«ã¯ãå ã»ã©èª¬æããCOUNTé¢æ°ãçµã¿è¾¼ã¿ã¾ãã</p>
<p>説æãããå³ãè¦ã¦ã¿ã¾ãããã</p>
<p><a href="http://kumano-okini.com/wp-content/uploads/2015/10/IF-MID-LEFT-count.png"><img class="aligncenter size-full wp-image-259" src="http://kumano-okini.com/wp-content/uploads/2015/10/IF-MID-LEFT-count.png" alt="IF+MID+LEFT+count" width="1072" height="193" /></a></p>
<p><strong>=IF(COUNT(FIND({&#8220;é½&#8221;,&#8221;é&#8221;,&#8221;åº&#8221;,&#8221;ç&#8221;},B3))>;0,IF(MID(B3, 4, 1)=&#8221;ç&#8221;, LEFT(B3, 4), LEFT(B3, 3)),B3)</strong></p>
<p>ã ãã¶ãé·ãæ°å¼ã«ãªãã¾ããããä¸å¯§ã«ä¸èº«ãã¿ã¦ããã¨<br />
IFé¢æ°ã®ä¸ã«å ã»ã©èª¬æããå ¨ã¦ã®é¢æ°ã並ãã§ãããã¨ãåããã¨æãã¾ãã</p>
<p>ç°¡åã«èª¬æããã¨ã</p>
<p>ãããæååã®ä¸ã«ãé½ãéãåºãçãã®ããããã®æåãå«ã¾ãã¦ãããã<br />
4æåç®ãæãåºãããã®çµæããçãã§ãã£ãããæååã®å·¦ãã4æåæãåºãã¾ãã<br />
4æåç®ã®çµæããçãã§ãªãå ´åã¯ãæååã®å·¦ãã3æåæãåºãã¾ãã<br />
æååã®ä¸ã«ãé½ãéãåºãçããå«ã¾ããªãå ´åã¯ã<br />
æååããã®ã¾ã¾è¡¨ç¤ºãã¾ãã</p>
<p>ã¨ããäºã¤ã®IFæã§ã§ãã¦ãã¾ãã</p>
<p>ãã®ããã«é¢æ°ã®ä¸ã§å¥ã®é¢æ°ã使ç¨ãããã¨ããã¹ããããªãã¦è¨ã£ãããã¾ããï¼å·£ã®ä¸ã«åµãå ¥ã£ã¦ããæ§åããæ¥ã&#8221;å ¥ãå&#8221;ã®æã§ããï¼</p>
<h3>å½å ã®ä½æã ãã§è¯ãå ´å</h3>
<p>ãããå¤å½ã®å称ãªã©ãå¿ è¦ãªãå ´åã¯ã</p>
<p><strong>=IF(MID(B3, 4, 1)=&#8221;ç&#8221;, LEFT(B3, 4), LEFT(B3, 3))</strong></p>
<p>ããã ãã§ååã§ãã</p>
<p>ãã®ããã«ãåºæ¬çãªé¢æ°ã«å°ã工夫ãã¦ãçµã¿åãããã ãã§ãè¤éãªæ¡ä»¶ãã²ã¨ã¤ã®æ°å¼ã§æºãããã¨ãåºæ¥ã¾ãã</p>
<p>å°ãé£ããé¨åããã£ãããããã¾ãããããã®ä»ã®ããæ¹ãããã¾ãã®ã§è¦ã¤ãã¦ã¿ã¦ãã ãã＾＾♪</p>

<!-- WP QUADS Content Ad Plugin v. 3.0.5.1 -->
<div class="quads-location quads-ad2" id="quads-ad2" style="float:none;margin:0px 0 0px 0;text-align:center;">

</div>


エクセル関数 住所から都道府県名だけを抜き出す方法は？
