新年あけましておめでとうございます。
先の投稿にある問題集の回答例です。
このページを先に開かれた方は、前の投稿をご覧ください。
問題①
B列に
=PHONETIC(C２) と入れて下へコピー
C列に IMEの郵便番号＝＞住所変換の機能を使って入力するのがコツ
PHONETIC関数はフリガナを返しますが、IMEで変換する前の入力した文字を返します。
郵便番号から住所に変換できない方は、右下のIMEからIME詳細設定で 郵便番号辞書に
チェックを入れてください。詳しくはネットで調べる。
問題②
=IF(MID(C2,4,1)="県",LEFT(C2,4),LEFT(C2,3))
住所の４文字目が 県 の場合は、最初の４文字、それ以外は３文字で正しく出ます。
問題③－１
A4セルに
=IF(COUNTIF(住所!$E:$E,$E$2)>=ROW(A1),INDEX(住所!A:A,MATCH($E$2,住所!$E:$E,FALSE)+ROW(A1)-1),"")
と入れて右へコピー、下へコピーします。
これも説明が難しい。仮に空いているセルに
=MATCH(E2,住所!E:E,FALSE) と入れると抽出データの県名が最初の行番号が表示されます。
=COUNTIF(住所!E:E,E2) と入れると抽出する県名の数が表示されます。
INDEX関数を使って、表示したい行番号
下へコピーして一行づつずらすために、)+ROW(A1)-1),"")
Row関数？何に使うのと思っていた人、結構使えますよ。
IF文で県名の数を超えたら空白にしています。
ちょっと頑張ってみて下さい。理屈がわかると、あ～っ と思うはずです。
問題③－２
A4セルに
=IF(COUNTIF(住所!$E:$E,$E$2)>=ROW(A1),INDEX(住所!A$1:A$200,SMALL(IF(住所!$E$1:$E$200=$E$2,ROW(A$1:A$200),""),ROW(A1))),"")
と入れて Ctrl＋Shift＋Enter で決定
式が｛｝でくくられて配列関数として認識された表示が出ます。
これも説明が難しい。
住所のシートのF列に =IF(E2=Sheet2!E$2,ROW(),"") と入れて下までコピー
条件に合った行だけにその行番号が出ます。
この関数列を作業列と呼びます。
この部分の関数を配列関数に取り入れると
IF(住所!$E$1:$E$200=$E$2,ROW(A$1:A$200),"")
となります。詳しくは、ネットで 配列関数で勉強してみて下さい。
Small関数もこうやって使いますよ と云う事例でもあります。
難易度は、非常に高いです。
これが理解できたという人、先端技術のプログラマーになれるでしょう。
問題③－３
抽出するシート名のタブを右クリックして、コードの表示をクリック
VBエディターが起動したら
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$E$2" Then
Sheets("住所").Columns("A:E").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("E1:E2"), CopyToRange:=Range("A3:D3"), Unique:=False
End If
End Sub
を張り付けて閉じる。E2セルの県名を変えると希望のデータが表示されます。
コード自体は、わずか３行です。
If～EndIfの２行 とフィルターオプション（AdvancedFilter）です。
Sheets("住所").Columns("A:E").AdvancedFilter・・・・ と長いコードがありますが
手入力する必要はありません、マクロの記録で作成されます。
コツは、出来たコードをシートモジュールのChangeイベントで実行する。
これまた、全ての説明は無理なので
エクセル フィルターオプション VBA などで検索してみて下さい。
一般的は、データ抽出方法なので、是非、勉強してみて下さい。
県名に関数の作業列がありますが、住所１を使っても可能です。
その場合は、E1セルを 住所１ とするだけで検索条件が変更できます。
VBAユーザーには、簡単な話です。
基本的な勉強をしなくてもネット上で公開されています。
エクセル効果を早く出したい方は必見ですので、じっくりと調べてみて下さい。
問題③－２の関数法よりこちらの方が活用術があります。
エクセルの機能（フィルターオプション）とマクロの記録、VBエディターで編集
この三つがキーポイントです。
問題④－１
A列を選択
リボン データ
中央にある データの入力規則でウィザードが起動します。
全ての値 ＝＞ユーザー設定
関数に =COUNTIF(A:A,A1)<2
OKすると Ar列には同じ文字が重複して入力できなくなります。
問題④－２
同様に入力規則で 関数に
=LEN(A1)=LENB(A1)
LEN関数は、単純に文字数、LENB関数は 全角文字を ２で数えますので
全角が含まれていると =LEN(A1)=LENB(A1) ×(FALSE)になります。
問題④－３
同様に入力規則で 関数に
=LEN(A1)=LENB(PHONETIC(A1))
PHONETIC関数は 半角ｶﾅ を全角に変換する癖があるので、それを利用しました。
問題④－３
同様に入力規則で 関数に
=TYPE(A1)=1
あまり見ない関数ですが、入力した内容に対して
数値、文字などを１，２・・で表示してくれます。
いずれもあまり知られていない関数ですが、こんな使い方があったのか
と云うレベルです。