Excel「名前の管理」ボタンで解決…リスト範囲の項目増

 この間「Excelの名前ボックス」について2回記事を書いてきたが、名前ボックスで初めに選択したリスト範囲に増加がある場合参照用リストに項目の増入力しただけでは増が反映されないことに半日苦しんだ。（範囲指定しなおしても古い範囲が出力）なにか自分のやり方が間違っているかと何回もやり直してみるがダメだった。いろいろ調べてやっと解決ができたので紹介したい。

1. データが増えた場合リストにその項目を追加入力して保存をいくらかけてももとの範囲指定に戻ってしまう
   ★作成したリスト…私の例では50音の一つの行＿「あいうえお」
   　　　　　　　　　始めに登録は5項目とすると6項目に○○を増やしたい場合
   ★リストに○○を追加＝＞名前のボックスで前につけた＿「あいうえお」を選択
   　　　　　　　　　　＝＞保存をかける＝＞ドロップダウンリスト＝＞出力しない
   　　　　　　　　　　＝＞もとの範囲指定に戻ってしまう・・・もとの範囲指定の
   　　　　　　　　　　　　反転表示に戻ってしまう　
   　
2. リボンの［数式］タブに「名前の管理」ボタンがあることがわかる
   ★名前をつけておいた範囲を変更したい時には［数式］タブ＝＞［定義された名前］グループの＝＞［名前の管理］ボタンをクリックすると、その文書に登録されている名前が一覧になったダイアログボックスが表示される
   名前管理は下記参照（クリックすると拡大します）
   
   
   
   　
3. 手順
   

   ★まず範囲の変更を行いたい名前を選択＝＞ダイアログボックス下部の［参照範囲］
   ＝＞その名前に登録されている範囲が表示される＝＞反転の囲み線はリストに増加した範囲指定されていない＝＞範囲指定をやり直す＝＞OKボタン＝＞保存するかどうか＝＞保存
4. これでドロップダウンリストに出力するようになる

“名前ボックス”と“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”関数はわかるまで苦労したがいろいろ試行錯誤して完成に漕ぎつけた。
   　大変良い経験だった
   ★なお出入金管理表はテーブル機能を使っておりテーブルの左側には年月日、勘定科目を入力することになっており、勘定科目は50位あるため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を選択する（アナログ的だが）
   これで多少早い選択ができるようになりイライラはなくなった。