Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 7 Professional : Excel 2013)
IF関数にFIND関数をネスト うまくいきません
投稿日時: 17/12/15 15:09:36
投稿者: shinx

そもそもの考え方が間違っているのでしょうか
 
セルAD5に 岡山県岡山市北区と入力されています。
AE5に次の式をいれました
 
=IF(FIND("北海道札幌市",AD5)=1,"北海道札幌市",IF(FIND("宮城県仙台市",AD5)=1,"宮城県仙台市",IF(FIND("埼玉県さいたま市",AD5)=1,"埼玉県さいたま市",IF(FIND("千葉県千葉市",AD5)=1,"千葉県千葉市",IF(FIND("神奈川県横浜市",AD5)=1,"神奈川県横浜市",IF(FIND("神奈川県川崎市",AD5)=1,"神奈川県川崎市",IF(FIND("神奈川県相模原市",AD5)=1,"神奈川県相模原市",IF(FIND("新潟県新潟市",AD5)=1,"新潟県新潟市",IF(FIND("静岡県静岡市",AD5)=1,"静岡県静岡市",IF(FIND("静岡県浜松市",AD5)=1,"静岡県浜松市",IF(FIND("愛知県名古屋市",AD5)=1,"愛知県名古屋市",IF(FIND("京都府京都市",AD5)=1,"京都府京都市",IF(FIND("大阪府大阪市",AD5)=1,"大阪府大阪市",IF(FIND("大阪府堺市",AD5)=1,"大阪府堺市",IF(FIND("兵庫県神戸市",AD5)=1,"兵庫県神戸市",IF(FIND("岡山県岡山市",AD5)=1,"岡山県岡山市",IF(FIND("広島県広島市",AD5)=1,"広島県広島市",IF(FIND("福岡県北九州市",AD5)=1,"福岡県北九州市",IF(FIND("福岡県福岡市",AD5)=1,"福岡県福岡市",IF(FIND("熊本県熊本市",AD5)=1,"熊本県熊本市",AD5))))))))))))))))))))
 
#VALUE! と表示されます。
 
AD5に入力されている住所のうち、政令指定都市については、区名は含めず市までの住所を取り出したいのです。
政令指定都市以外の住所であれば、そのままの住所地を取り出すというものです。
AD5には必ず都道府県名から入力されているので、1が返るものとし、=1としています。
 
IFの考え方はあっていると思いますし、最後に条件に合わない場合はAD5をそのまま取り出すことにしているのに、なぜか#VALUE!です
 
よろしくお願いします。

回答
投稿日時: 17/12/15 16:28:23
投稿者: んなっと

AH列に政令指定都市一覧。
 
          AD         AE AE AG       AH
 1                          北海道札幌市
 2                          宮城県仙台市
 3                        埼玉県さいたま市
 4                          千葉県千葉市
 5  岡山県岡山市北区    岡山県岡山市     神奈川県横浜市
 6 静岡県浜松市浅田町    静岡県浜松市     神奈川県川崎市
 7 埼玉県春日部市栄町 埼玉県春日部市栄町    神奈川県相模原市
 8                          新潟県新潟市
 9                          静岡県静岡市
10                          静岡県浜松市
11                         愛知県名古屋市
12                          京都府京都市
13                          大阪府大阪市
14                           大阪府堺市
15                          兵庫県神戸市
16                          岡山県岡山市
17                          広島県広島市
18                         福岡県北九州市
19                          福岡県福岡市
20                          熊本県熊本市
21                                
 
AE5
=LEFT(AD5,TEXT(AGGREGATE(14,6,LEN(AH$1:AH$30)/(FIND(AH$1:AH$30,AD5)=1),1),"0;;999"))
下方向・↓
 
$30はAH列の個数20より少し多めであればいい。

回答
投稿日時: 17/12/15 16:39:11
投稿者: WinArrow
投稿者のウェブサイトに移動

ユーザー定義関数(案)
 
Function 検索(文字 As String)
Dim myrng As Range
     
    For Each myrng In Range("住所リスト")
        If myrng = Left$(文字, Len(myrng)) Then Exit For
    Next
    If Not myrng Is Nothing Then
        検索 = myrng
    Else
        検索 = 文字
    End If
End Function
 
住所リストは、政令都市も一覧表です。

回答
投稿日時: 17/12/15 16:44:32
投稿者: YuuSa

こんにちわ。
 

引用:
IFの考え方はあっていると思いますし、最後に条件に合わない場合はAD5をそのまま取り出すことにしているのに、なぜか#VALUE!です

 
いえ、IF関数の論理式はtrueかfalseを返さなければなりません。
#VALUE!=1 は評価されず#VALUE!になります。
 
よって対策は
1.#VALUE!をtrueかfalseに評価する関数を入れる。
2.#VALUE!を他の値、たとえば0に置き換えて1と比較する。
 
になると思います。
 
1.なら not関数とiserror関数を
2.なら iferror関数を
調べて各find関数を評価して見て下さい。

回答
投稿日時: 17/12/15 19:36:16
投稿者: WinArrow
投稿者のウェブサイトに移動

FINDで合致しないことを想定した参考数式
 
1つの場合
=IF(NOT(ISERROR(FIND("神奈川県横浜市",A1))),"神奈川県横浜市",A1)
2つの場合
=IF(NOT(ISERROR(FIND("神奈川県横浜市",A1))),"神奈川県横浜市",IF(NOT(ISERROR(FIND("静岡県静岡市",A1))),"静岡県静岡市",A1))
 
これを現在の数式に組み込むとかなり数式がややこしくなります。
メンテナンスも難しくなります。
 

回答
投稿日時: 17/12/15 21:44:29
投稿者: WinArrow
投稿者のウェブサイトに移動

ユーザー定義関数を若干修正します。
 
ユーザー定義関数(案)
  
Function 検索(文字 As String)
 Dim myrng As Range
       
     For Each myrng In Range("住所リスト")
         If myrng.Value = Left$(文字, Len(myrng.Value)) Then Exit For
     Next
     If Not myrng Is Nothing Then
         検索 = myrng.Value
     Else
         検索 = 文字
    End If
 End Function
   
住所リストは、政令都市の一覧表です。
※政令都市の一覧表には、今後の増減発生時のメンテナンスを容易にするため、名前を設定しておきます。

投稿日時: 17/12/18 10:53:11
投稿者: shinx

んなっと さんの引用:
AH列に政令指定都市一覧。
 
          AD         AE AE AG       AH
 1                          北海道札幌市
 2                          宮城県仙台市
 3                        埼玉県さいたま市
 4                          千葉県千葉市
 5  岡山県岡山市北区    岡山県岡山市     神奈川県横浜市
 6 静岡県浜松市浅田町    静岡県浜松市     神奈川県川崎市
 7 埼玉県春日部市栄町 埼玉県春日部市栄町    神奈川県相模原市
 8                          新潟県新潟市
 9                          静岡県静岡市
10                          静岡県浜松市
11                         愛知県名古屋市
12                          京都府京都市
13                          大阪府大阪市
14                           大阪府堺市
15                          兵庫県神戸市
16                          岡山県岡山市
17                          広島県広島市
18                         福岡県北九州市
19                          福岡県福岡市
20                          熊本県熊本市
21                                
 
AE5
=LEFT(AD5,TEXT(AGGREGATE(14,6,LEN(AH$1:AH$30)/(FIND(AH$1:AH$30,AD5)=1),1),"0;;999"))
下方向・↓
 
$30はAH列の個数20より少し多めであればいい。

 
 
ありがとうございます。
AGGREGATE 使うと、うまくいきました。
AGGREGATE関数、こんなのがあるなんて、知りませんでした。
すっきり、解決しました。
 
関数、もっと勉強します。
 
ありがとうございました。