ちちくろ目線 
こんにちは、ちちくろです♪
前回の記事で・・Excelのセル入力時にリスト表示をご紹介したのですが・・少々使い勝手が悪そうですよね・・。
Excelのセルにデータを入力する際に・・タイピングの量を減らせると・・とても楽ちんなのですが♪
そこで・・リスト表示の内容を状態次第で切り替わるようになったら・・なんか便利そうですよね♪
今回は・・状況によりリスト表示の内容が切り替わり・・タイピングが少なくてセルのデータ入力がとても楽になる・・その方法をご説明しようかと思います♪
目次
INDIRECT関数
INDIRECT関数とは・・参照先のセルの文字列を返す関数です・・。
それだと・・ただ単に”=B1″と書き込めばよいとなるのですが・・。
INDIRECT関数を使うと数式に参照先のセルの文字列を使うことが出来ます。
詳しいことを説明することに・・。
INDIRECT関数の使い方
上の図を見てください・・セルF1の内容は１が・・セルF2にCONCAT関数で・・文字AとセルF1の文字を連結すると評される内容はA1となります。
そして・・セルD2にINDIRECT関数でC2とA1のセルを足し算する数式を・・記入例は＝C2+INDIRECT(F2)と記入
すると・・セルD2の数式はC2＋A1で11という結果に・・。
そしてセルF１の数値を2変更すると・・セルD2の数式は＝C2＋A2となり・・12が表示されることに・・。
更に・・セルF１の数値を3に変更すると・・セルD2の数式は＝C2＋A3となり13が表示されることに・・
セルF2のCONCAT関数の結果で・・セルD2結果が変化しています。
INDIRECT関数を使えば・・参照先セルの数式次第で結果を変動させることが出来ます。
データ入力規則に応用
INDIRECT 関数はデータ入力規則にも利用できます・・。
INDIRECT関数を使って・・参照先セルからリスト表示させるセル範囲の名前を取得し・・リストの内容を切り替えることが出来れば・・。
リスト表示をより便利に使うことが出来ますね♪
セル範囲に名前を付ける
では・・具体的に説明します・・説明には以前に公開した見積書・請求書の明細作成のExcelシートを使用したいと思います♪
見積書・請求書の明細作成のExcelシートって何？
という方は・・下のリンクからどうぞ・・。
まず・・リスト表示させるための・・新しいワークシートを作成します。
シート名は・・なんでもよいのですがここではdataとすることに ・・。
A列にカテゴリのリストに表示させる・・【果物】・【野菜】・・・と記載し・・A2～A21のセル範囲を選択し名前ボックスに”カテゴリ”と名前を付けます♪
同様に D2～D21を【果物】・・G2～G21を 【野菜】と・・。
この時に・・セル範囲は少し多めに選択しましょう・・リスト表示する時に空白のセルは表示去らないので大丈夫・・。
少し多めに選択しておけば、表示内容を増やしたくなったら・・範囲内に項目を追加するだけでOKです。
更に・・最後に必ず”小計”を入れるようにしましょう・・なぜ小計が必要なのか？
それは後程・・。
IFS関数
IFS関数とは・・複数の理論式を記入できる関数です。
使い方は・・IFS（理論式１，真の場合１,理論式２,真の場合２・・・という感じ・・。
理論式Nの代わりに”TRUE”を記載すると・・理論式に当てはまらない場合の処理となります。
IF関数を複数使っても良いのですが・・そうなると複雑になってわけがわからなくなることが・・そんな時はIFS関数が便利です♪
次項でK列の数式を詳しく説明したいと・・。
K列の数式
それでは・・メインのワークシートに戻り・・K列に数式を書き込みます。
K列の数式を説明すると・・書き込む数式は”=IFS(D1=”小計”,”カテゴリ”,LEFT(D1,1)=”【”,D1,TRUE,J1)”と書き込みます。
この数式の理論式１と真の場合は・・D1=”小計”,”カテゴリ”となり・・意味はセルD1内のテキストが”小計”なら”カテゴリ”にしろ・・というもの・・。
次に、理論式２と真の場合は・・ LEFT(D1,1)=”【”,D1・・この意味は・・セルD１内の１文字目が”【”ならばD1内のテキストを参照するというもの・・。
最後に・・理論式１および理論式２に該当しない場合は・・TRUE,J1で・・この場合一つ上のセルJ1を参照するということになります。
後はK2のセルをコピーし・・必要な行数ペーストします・・ここではK3～K22迄コピーすることに
リストのセル範囲指定
次に・・データ入力規則でリスト使用時にセル範囲の指定方法を説明します。
セル範囲の指定は・・INDIRECT関数を使って指定・・。
セル範囲にINDIRECT関数を使う
まずは・・セルD2を選択しデータ入力規則のダイヤログを表示させます♪
入力値の種類をリストとし・・元の値には・・”INDIRECT(J1)”と記載します。
この時INDIRECT関数の記載はセルの相対関数を使用することにしましょう、相対参照にすることで・・D列の行によって・・参照先の行も移動してくれます。
ここまで出来たら【OK】をクリック・・その後セルD2を選択して必要な行数コピーします。
ここではD22迄オートフィル等でコピーすることに・・。
これでカテゴリ選択時にリスト内容を変えることが出来ます
動作確認
では作成した表の動作を確認します♪
本来は・・K列を非表示にするのですが・・わかりやすくするためにこのままで・・実際に運用する時はK列を非表示にしてください♪
カテゴリ入力
D列に何もない状態では・・K列すべて”カテゴリ”と表示されています。
D2のリストを表示すると・セル範囲”カテゴリ”の内容の・・【果物】・【野菜】・【鮮魚】・【精肉】が表示されます。
これは・・INDIRECT関数でセル範囲カテゴリが選択されているため・・。
そして・・カテゴリの【果物】を選択すると・・。
セルK3以降が【果物】な変わります。
セルD3のリストにはセル範囲【果物】の内容が表示されることに・・。
こうすることでカテゴリを選択することで・・リスト内容の品名を変更することが出来ましたね♪
小計でカテゴリに戻す
続いてセルD3～D5に品名を・・リンゴ・バナナ・ブドウという感じで入力・・。
最後にセルD6に”小計”を選択すると・・セルK７以後がカテゴリに変化します♪
これで、D７以後で再びカテゴリを選択することが出来るようになります、なので・・別のカテゴリを選択し・・繰り返し作業が可能に・・。
このようにカテゴリ選択から 品名を選択し 最後に”小計”を選択することでカテゴリに戻ることが出来ます。
まとめ
説明用のYouTube動画を作成したのでリンクを・・
下のリンクからどうぞ♪
リスト表示を使うとタイピングの量を減らすことが出来ることが・・でも・・まだまだタイピングを減らしたいと思うかもしれません・・。
そうなると・・数量は・・少し無理がありそう・・では単価は・・。
なんか行けそうと思いますよね♪
次回は単価をタイピングなしで入力する方法を紹介したいと思います。
今回の方法を採用する場合も・・個人責任でお願いします♪
