Excelで質問ですが何処が間違ってますか？

質問

Excel2007のマクロで置き換えと言うか代入という表現が正しいのかは不明なんですが、次のことがしたいです。
１、セルK29からZ29までかそれ以上のセルの1つずつにデータが4つずつ入ってます。
２、それらのデータを、入ってる形によって別な記号に変えてK30からZ30までかそれ以上に入れたいです。
K29アイウエ→K30にA
L29アイエウ→L30にB
M29アウイエ→M30にC
N29アイウエ→N30にA
O29アウエイ→O30にD
P29アエイウ→P30にE
Q29イアウエ→Q30にF
R29イアエウ→R30にG
S29イウアエ→S30にH
T29イウエア→T30にI
U29イエアウ→U30にJ
V29イエウア→V30にK
W29ウアイエ→W30にL
X29ウアエイ→X30にM
Y29ウイアエ→Y30にN
Z29ウイエア→Z30にO
………
と、この様になんですが。
で、
=IF(K29="","",MID("ABCDEFGHIJKLMNO",FIND(","&K29&",",",アイウエ,アイエウ,アウイエ,アウエイ,アエイウ,イアウエ,イアエウ,イウアエ,イウエア,イエアウ,イエウア,ウアイエ,ウアエイ,ウイアエ,ウイエア,")/4+0.75,1))
と関数でこの様に書きましたがなぜかズレていました。
何処が間違ってるか教えてください。

回答No.7

bunjii · ベストアンサー

>関数でこの様に書きましたがなぜかズレていました。
>何処が間違ってるか教えてください。
FIND関数での文字位置の計算方法に誤りがあります。
検索対象の文字列が「,アイウエ,アイエウ,アウイエ,アウエイ,アエイウ,イアウエ,イアエウ,イウアエ,イウエア,イエアウ,イエウア,ウアイエ,ウアエイ,ウイアエ,ウイエア,」で検査値の値の先頭に「,」を付加していますのでFIND関数の戻り値は1、6、11、・・・・のように5文字間隔になるはずです。しかし、提示の数式では4で除算して0.75を加算するような論理に合わない計算をしていますので誤差が生じてしまいます。
FIND関数の戻り値から1を減じて5で除算し端数を切り捨てた値に1を加えれば正しい位置になるはずです。
FIND(","&K29&",",",アイウエ,アイエウ,アウイエ,アウエイ,アエイウ,イアウエ,イアエウ,イウアエ,イウエア,イエアウ,イエウア,ウアイエ,ウアエイ,ウイアエ,ウイエア,")/4+0.75
　　　　　↓
INT((FIND(","&K29,",アイウエ,アイエウ,アウイエ,アウエイ,アエイウ,イアウエ,イアエウ,イウアエ,イウエア,イエアウ,イエウア,ウアイエ,ウアエイ,ウイアエ,ウイエア")-1)/5)+1
または
INT((FIND(K29,"アイウエ,アイエウ,アウイエ,アウエイ,アエイウ,イアウエ,イアエウ,イウアエ,イウエア,イエアウ,イエウア,ウアイエ,ウアエイ,ウイアエ,ウイエア")-1)/5)+1

提示の数式はタイプミスが起こり易いので別の方法にされた方が良いでしょう。
尚、マクロ（VBA等）で処理する場合でも考え方を誤ると同様な結果になりますので注意してください。

お礼

bunjiiさん、ありがとうございました。

回答No.6

imogasi

（１）このタイプの問題はVLOOKUP関数のFALSE型を勉強するほうが良い
（２）元データがあって、その右隣列に結果を出すのが常識。
　　　質問では直下行に出している。
エクセルの本など読み、世の中のエクセルの標準というものを学ぶことを勧める。
初心者じゃないかと思うが、むつかしいやり方を考えて、質問していると思う。
ーー
ＶＬＯＯＫＵＰ関数ＦＡＬＳＥ型利用例。
D、E、Ｆ列　Ｄ２：Ｆ１６に下記データを作る。
アイウエ 1 A
アイエウ 2 B
アウイエ 3 C
アウエイ 4 D
アエイウ 5 E
イアウエ 6 F
イアエウ 7 G
イウアエ 8 H
イウエア 9 I
イエアウ 10 J
イエウア 11 K
ウアイエ 12 L
ウアエイ 13 M
ウイアエ 14 N
ウイエア 15 O
ーー
Ａ列にデータ
Ａ２：Ｃ５
アイウエ A
アウイエ C
イアウエ F
ウイエア O
Ｂ２の式は
=CHAR(64+VLOOKUP(A2,$D$2:$E$16,2,FALSE))
本質部分を見失わないように、見つからない時のエラー対応は式に入れてない。
または、Ｆ列にＡ，Ｂ，Ｃ・・を入れるなら
=VLOOKUP(A2,$D$2:F$16,3)
ーー
ＶＢＡでは
Ａ１セルに、テーブル的データ
アイウエ,アイエウ,アウイエ,アウエイ,アエイウ,イアウエ,イアエウ,イウアエ,イウエア,イエアウ,イエウア,ウアイエ,ウアエイ,ウイアエ,ウイエア,
Ａ２：Ｂ５　Ｂ列は下記ＶＢＡ実行結果
アイウエ　 A
アウイエ 　　C
イアウエ　 F
ウイエア 　　O
ーーー
標準モジュールに
Sub test01()
'MsgBox Range("A1")
For i = 2 To 5
'MsgBox Cells(i, "A")
p = InStr(Range("A1"), Cells(i, "A"))
MsgBox p
Cells(i, "B") = Chr(64 + Int(p / 5) + 1)
Next i
End Sub
ーー
Chr(64 + Int(p / 5) + 1)の部分で、文字コードの知識を使っている。
突然出てくる、６４は何かわかりますか。
５で割っているのは、原データ４文字とカンマ１文字の５文字で、規則性があるから。Ａ，Ｂ，Ｃ・・の文字コードの何番目の文字かを割り出すのに使えるから。

お礼

imogasiさん、熱の入った説明をありがとうございました。

回答No.5

watabe007

>Excel2007のマクロで
マクロなら
Sub Test()
　　Dim c As Range, myArr As Variant, n As Long
　　myArr = Array("アイウエ", "アイエウ", "アウイエ", "アウエイ", "アエイウ", _
　　　　"イアウエ", "イアエウ", "イウアエ", "イウエア", "イエアウ", _
　　　　"イエウア", "ウアイエ", "ウアエイ", "ウイアエ", "ウイエア")
　　For Each c In Range("K29:Z29")
　　　　n = Application.Match(c.Value, myArr, 0)
　　　　c.Offset(1).Value = Chr(64 + n)
　　Next
End Sub

お礼

お礼遅れました。WATABE007さんありがとうございます。

回答No.4

watabe007

>お尻の方の数字ってどのようにして決める物なんですか？
,アイウエ　,アイエウ　,アウイエ　,アウエイ
,を含めて５文字づつ有りますね、なのでFINDで得た数字を5で割って
1を足して補正しています。

お礼

お礼します。

回答No.3

mt2015

質問文中にはマクロでやりたいような事が書いてありますが、関数で良いのですよね？

パッと見て解りにくい式を使うより、対応表を作り(添付の図ではK32:L46）、Vlookup関数を使った方が良いと思います。
添付の図の例ではK30に以下の式を入れています。
=IFERROR(VLOOKUP(K29,$K$32:$L$46,2,FALSE),"")

お礼

mt2015さんお早うございます。丁寧にしてくださってありがとうございました。

回答No.2

watabe007

=IF(Z29="","",MID("ABCDEFGHIJKLMNO",FIND(","&Z29&",",",アイウエ,アイエウ,アウイエ,アウエイ,アエイウ,イアウエ,イアエウ,イウアエ,イウエア,イエアウ,イエウア,ウアイエ,ウアエイ,ウイアエ,ウイエア,")/5+1,1))

お礼

watabe007さん、いつもありがとうございます。お尻の方の数字ってどのようにして決める物なんですか？

回答No.1

watabe007

=IF(K29="","",MID("ABCDEFGHIJKLMNO",INT(FIND(","&K29&",",",アイウエ,アイエウ,アウイエ,アウエイ,アエイウ,イアウエ,イアエウ,イウアエ,イウエア,イエアウ,イエウア,ウアイエ,ウアエイ,ウイアエ,ウイエア,")/5+1),1))