“名前ボックス”と“INDIRECT”関数で50音検索がより便利に

 

前回のブログ記事の後いろいろ調べていたらExcelの“名前ボックス”と“INDIRECT”関数を使うとさらに50音検索が迅速にできることがわかり、紆余曲折の末“出入金管理表”の完成に漕ぎつけたので紹介したい。

1. “INDIRECT”関数を使うためにテーブル“出入金管理表”に列の挿入を行う
   ★テーブルの「支払い/入金先」の前列に1行挿入する（I列）
   
   
2. テーブルの同じシートの右に作った50音表の行名をあ行~わ行を入力規則パネルで範囲選択（前回のブログの50音表参照）
   ★データ＝＞データツール＝＞データの入力規則＝＞リスト＝＞Q4:Z4を範囲指定
   
   
3. テーブルI5のセルに戻りドロップダウンリストを作成する
   ★データ＝＞データツール＝＞データの入力規則＝＞リスト＝＞=$Q$4:$Z$4を範囲指定＝＞I5セルの右に▼が出力＝＞クリック＝＞50音行の各行の名前がドロップダウンで出力（あいうえお、かきくけこ・・・）
   
   
4. ここでシート右上の50音一覧を各列ごとに範囲指定して名前ボックスで名前をつける
   ★“名前ボックス”はExcelシートの左上の関数ボックスの左側にある
   ★例えば「あいうえお」列入力されているセルを範囲指定＝＞“名前ボックス”に「あいうえお」と入力してenterキーで確定＝＞この作業を次の「か行」から「わ行」まで行う
   
   
5. テーブルの支払い/入金先（J列）に戻りJ5セルをクリックし“INDIRECT”関数を挿入する
   ★データ＝＞データツール＝＞データの入力規則＝＞設定＝＞リスト＝＞元の値＝＞
   　INDIRECT（I5）＝＞OKボタンクリック
   ★テーブルI5セルをクリック＝＞右の▼をクリック＝＞50音各行表示＝＞「あいうえお」行を選択＝＞J5セルををクリック＝＞右の▼をクリック＝＞あ行の「支払先/入金先」一覧が出力＝＞該当の支払い/入金先をクリック＝＞他の行も試すと同じように出力する（か行～わ行）
   実際の画像を示すと
   
   
   
   
   
6. これでは入力規則は1行のセルI5,J5セルだけなのでテーブルのI列、J列全体に入力規則をコピーする
   ★I5セルクリック＝＞コピー＝＞I5:I224まで範囲指定（反転）＝＞その上で右クリック＝＞形式を選択して貼り付け＝＞入力規則に☑を入れる
   ★同じ作業をJ5列でも行う
   これでI列とJ列が連動してリスト項目50音表の各列が出力する
   
   
7. この作業で感じたこと
   ★名前ボックスという便利な機能をはじめて使うことが出来、ここでつけられた名前は
   Excelシートとブックで活用できるとのことで大変便利
   ★“INDIRECT”関数はわかるまで苦労したがいろいろ試行錯誤して完成に漕ぎつけた。
   　大変良い経験だった
   ★なお出入金管理表はテーブル機能を使っておりテーブルの左側には年月日、勘定科目を入力することになっており、勘定科目は１００位あるためvlookup関数を使って別シートの勘定科目一覧から勘定科目NOを数字で入力できるようになっていることも参考にしてほしい。
   また勘定科目ごとの集計作業はpivotテーブルで簡単にできるので大変重宝している。

Excelのドロップダウンリストの数が多い場合の１つの工夫・・・50音一覧から探す

 

Excelで出入金管理表を作っていてドロップダウンリストで入力を簡単にできるようにしようとしたが支払先と入金先の数が30以上になり大変見にくく対象をさがすのに時間がかかり困っていた。

そこでいろいろ考えて50音で表をつくる方法を考えた。エクセルでは１行か１列でのリスト検索しかできないので１つの方法として紹介したい。

1. わたしのPC環境
   ★OS：win10pro  win10 vista    multiboot環境
   ★CPU：AMDAthron64×2　dual-core　processor　TK-53　1.7GHZ
   ★RAM：4GB
   ★DRIVE：256GB　ATA
   
   
2. セルへのドロップ・ダウンリストの設定
   ★データ＝＞データの入力規則＝＞入力値種類＝＞リスト＝＞元の値…範囲指定
   　で設定すると入力セルの右側に▼印が出力するのでクリックするとドロップダウン
   　リストが展開表示される。
   　
   
3. しかしこのリスト数が30以上になるとスクロールして該当の項目を探すのに大変時間がかかるのでそれを効率よく探す方法を考える
   ★検索リストとは別に「50音表」で検索項目を分類し項目にNOを振る方法を考える
   ★50音の表・・・元の値は同じExcelシートの見やすい右上に作成
   
   
   
4. この50音表で該当の項目とNOを覚え入力セルに戻り▼ボタンをクリックしスクロールし該当の項目とNOを選択する（アナログ的だが）
   これで多少早い選択ができるようになりイライラはなくなった。