超便利‼ Excelでドロップダウンリストの項目を簡単に増減させる裏技。

*2020年7月5日追記*

昨日の記事を見ていて、タイトルに“裏技”って書いてしまっている割にこの方法は正当な方法だなぁ・・・、と思いました。

ちょっと反省して、

次回は本当の裏技――テーブルを使ったリスト(項目が増減できる)

をご紹介しますね~。

今日はえみゅママの誕生日で、久々X100Vで写真撮ったりして忙しかったので、また今度ね。

(なるべく早く書きます)

↓ 書きました(^O^)/

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

こんにちは、えみゅママです!

今日は目先を変えて、マイクロソフトExcelの裏技のおはなしです。

この年まで生きてくると、『どんだけ人間がパソコンによって楽するか』が毎日の命題のような感じになってきます。(えみゅママだけ??)

なにせ、老眼は進んでくる。集中力が欠ける。

事務作業も思ったように進みません。

事務作業を効率化させるうえで、最も身近に感じるソフトウェアはやはり、Microsoft Officeでしょう。

えみゅママは、ExcelとWordのMOT(マイクロソフトオフィシャルトレーナー)の資格を持っています。が、MOTは2010年に終了していますので、正確に言うと、『持っていました』になるのかな?

その私が、

Excelで最も便利だと思う裏技のひとつ

と考えるのが、今日ご紹介する、

ドロップダウンリストの項目を自在に増減させる裏技

です。

企業にお勤めの場合、見積書や請求書、出納帳といった、定型のひな型って絶対にお使いになるはず。

定型文書で、しかも決まりきった項目をいちいち入力するのは、非常に面倒なものです。

そんな時省力化を助けてくれるのが、Excelの『データの入力規則』の中の『リスト』

その『リスト』の設定で一番頭を悩ませるのが、リストの項目をどう入力するか?です。通常考えられるのが、

1. リストの設定画面で元の値をカンマ入力していく

2. 別シートに項目を入力し、その範囲を元の値の設定画面に入力する

のふた通り。

しかし、リストの項目って、少し経つと増やしたい!あるいは減らしたい! あるいは、別の項目に修正したい!

と、日々変化していくんですよね~。

ところが今回ご紹介する裏技を知っていれば、簡単に、しかも、Excelに精通していない別の職員の方も楽々作業できるんです!

実は、今日もえみゅママは会社でそんなExcel文書を作成してきました!(それで、本記事を思いついています)

この裏技のポイントは

◎OFFSET(オフセット)関数とCOUNTA(カウントエー)関数を使う

◎名前の定義を使う

のふたつだけです。

それでは、さっそく見ていきましょう。

今日はこのひな形を使っていきます。

くるみ花子さんが経営する、PHOTO KURUMI。今度、小豆太郎さんの結婚式で、写真撮影を請け負うことになりました。

その見積書を作成しなければいけないのですが、毎回結婚式の撮影の見積書には、だいたい決まった項目を入力することになります。

で、今回は『リスト機能』を利用して、『摘要』と『単位』の欄にドロップダウンリスト(プルダウンリスト)を作成し、▽をクリックするだけで、定型単語が出てきて入力を補助してくれる機能を付加することにします。

(以下の見本は、Excel2016にて作成しています。バージョンの違いによって、多少見た目は違います。が、私自身、この機能はExcel2000か2002あたりから使用していますので、基本どんなバージョンでも同じように設定できます)

1. 見積書とは別のシートに『データシート』を作成します。

Excel2016は+でシートを増やしますが、昔のバージョンだと、sheetタブが3つほどデフォルトであるはずです。

sheet1を見積書に変更していますので、必然的にsheet2をデータシートにします。

2. データシートに摘要と単位を記入します。

データシートのA列とB列を使って、表示したい項目を行方向に入力していきます。

3. 入力した摘要と単位に『名前の定義』を設定します。

リボンの『数式』→名前の定義→名前の定義をクリックします。

新しい名前の設定ウインドウが表示されるので、適当な名前(今回は『摘要可変』とします)をつけて参照範囲を入力します。

4. OFFSET関数とCOUNTA関数を組み合わせた数式を参照範囲に入力します。

OFFSET関数とは―――
  指定したセルまたはセル範囲から指定された行数と列数だけ移動した位置にあるセル範囲の参照を返します。

いや、こう書いてもさっぱりわかんないと思いますが、

=OFFSET(参照(基準),行数,列数,[高さ], [幅])

(1) 摘要の基準はデータシートの$A$2です。(A2から項目は始まっています)

  ―――$A$2の$マークは絶対参照といって、コピーしようが何をしようが動くことがないセル位置にしたい場合につけます。

  単位の基準はデータシートの$B$2です。(B2から項目は始まっています)

(2) 摘要の項目は、A2(もしくはB2)と同一列内にありますので、移動する行も列もありません。従って、ここは”0”値を指定します。

(3) 高さ(参照の行数)を指定するのに利用しているのが、COUNTA関数

COUNTA関数とは―――
  範囲に含まれる空白ではないセルの個数を返します。

ここが一番のミソですね。

=COUNTA(値 1, [値 2], …)

  引数は255も設定できるそうです\(◎o◎)/!

  高さ(参照の行数)を求めるのはデータシートのA列ですので$A:$Aとし(単位の場合はB列ですので$B:$B)、1行目はデータタイトル(摘要もしくは単位というタイトル)を入れてしまってますので、『-1』を足して、2行目から始まることを示します。

(4) 幅(参照の列数)は1列ですので、『1』を引数とします。

以上の理由から、

A列の空白ではない(摘要の)項目を求める数式は、

=OFFSET(データシート!$A$2,0,0,COUNTA(データシート!$A:$A)-1,1)

B列の空白ではない(単位の)項目を求める数式は、

=OFFSET(データシート!$B$2,0,0,COUNTA(データシート!$B:$B)-1,1)

となります。

単位の列にも名前の定義をします。

名前の定義が出来ました!

5. 見積書のシートに戻ってデータの入力規則を設定します。

(1) 摘要の一番上のセル(B18)を選択し、『データ』→『データの入力規則』→『データの入力規則』をクリックします。

B18を選択状態に。

(2) データの入力規則の『4つのタブ』を順に設定します。

設定の元の値に『=摘要可変』と入力。

入力時メッセージタブの『セルを選択したときに入力時メッセージを表示する』のチェックを外します。

こうすることによって、リスト内の項目以外の文字も自由に入力できるようになります。

エラーメッセージタブの『無効なデータが入力されたらエラーメッセージを表示する』のチェックを外します。

こうすることによって、リスト内の項目以外の文字も自由に入力できるようになります。

日本語入力タブはIME(日本語入力ソフト)をオンにするかオフにするか等の設定ができます。摘要は日本語入力したいので、オンにしておきます。

入力し終えたら、OKをクリック。

6. ドロップダウンで摘要が出てきたら、残りのセルにドラッグコピーしておきます。

セルの右下の+を下にドラッグするとコピーできるよ♪

7. 同様に単位にも同じ設定をしていきます。

設定出来たら、G29までドラッグコピーしておく。

8. 試しにデータシートの項目を増やしてみよう♪

9. 完成!!

来実さん・・・・・ちょっと高い??(笑) いや、結婚式ってこんなもんかも(笑)。

入力が省力化されて、時短になりました。

私も、現金出納帳や見積書、シフト表などに(今でもバリバリ)利用しております!!

でも、このOFFSET関数の式はすぐ『0やっけ?-1やっけ?』とわからんようになるので、備忘録として記事を残しておきます。

*余談*

見積書とかの数式で、数字を入力していないところに『0』が出てくるのは、鬱陶しいもんです。

1. エクセルの設定画面(オプション)でゼロ値が出てこないようにする。

詳細設定の表示設定で、『ゼロ値のセルにゼロを表示する』のチェックを外す。

もしくは、

2. =IF(〇〇=””,””,〇〇)のIF関数を使う。

かです。ゼロという数字をあえて表示する必要がない文書には、1.のオプションで消しておくのが一番簡単です。