Excelのプルダウン機能は入力者の負担を減らすだけでなく、入力者ごとの「表記ゆれ」や「タイプミス」を無くすという意味でExcel管理者にとっても重宝する便利機能です。
そんなプルダウンの項目を途中で変更したくなることが、実際の業務でもよく起こると思います。なるべくなら、最小限の手順で変更がしたいですよね。
この記事では「初心者から上位1%を目指す！ExcelCamp」の講師が、OFFSET関数とCOUNTA関数を使い、半自動かつ最速でプルダウンの項目を増やす方法を解説します。
ExcelCamp講師が研修で実際に説明している手順に沿って、解説いたします。
※Mac版Excelのショートカットは紹介していません。Windows版のショートカットのみを紹介しています。
ショートカットキーの表記について
ExcelCampではショートカットの表記を以下に統一しています。
・「＆」は同時に押すショートカットです。たとえばCtrl&Cなら、Ctrlキーを押しながら同時にCキーも押すことで発動します。
・「＋」は順番に押すショートカットです。たとえばAlt+E+Lなら、Altキーを押した後に一度指を離し、E、Lキーをそれぞれ順番に押すことで発動します。
プルダウンの項目を増やす2つの方法
まず、プルダウンの作成方法には「直接入力」と「セル参照(別シートとの連動)」の2つがあります。
項目を追加する方法はそれぞれ違いますので、以下分けて説明をします。
ExcelCamp講師 野沢先生様々方法はありますが、中でも「最速」でできる1番オススメなやり方を紹介します。
※そもそもプルダウンを作る方法が知りたい方は、まずこちらの記事を先に読んでください。
「直接入力」で作ったプルダウン項目を追加・編集する方法
直接入力の場合
「元の値」に書かれている内容：参加,不参加
「直接入力」でプルダウンを書いている場合、データの入力規則ボックス内の「元の値」には上記のように書かれているはずです。
項目を追加・編集するためには、元の値を直接編集すればOKです。
まず、Alt&Sで「元の値」のカーソル内をアクティブにします(Altキーを押したままSキーを押す)。
この状態で、F2キーを1度押します(PCによってはFnキーも同時押し)。
画面上は何も変化しませんが、F2キーを押すことで矢印キーでカーソル内を動けるようになります。
あとは、追加・編集したい箇所を修正するだけです。試しに今回は「不明」という項目を追加してみます。
このように,不明を追加するだけでOKです。
※項目は必ず「,(半角カンマ)」で区切ってください。「、(全角)」では反応しないので注意が必要です。
ExcelCamp講師 野沢先生F2キーを押さずに矢印キーを押してしまうと、下のようにセル参照が優先されてしまいます。
逆にカーソル内を動ける状態からセル参照に変えたい場合も、
F2キーを一度押すことでできますので、ぜひ覚えてくださいね！
「セル参照」で作ったプルダウン項目を追加・編集する方法
セル参照の場合
「元の値」に書かれている内容：=参照元!$A$3:$A$4
「セル参照」でプルダウンを書いている場合、データの入力規則ボックス内の「元の値」には上記のように書かれているはずです。
例えば項目に「不明」を追加したい場合は、まずセル参照元のシートに「不明」という項目を追加する必要があります。
ただ、このままではプルダウンに項目が反映されません。
通常の方法ではこの後にデータの入力規則ボックスを開き、参照範囲を「不明」のセルまで広げる必要があります。
これでOKを押して、ようやく項目が追加されました。
ExcelCamp講師 野沢先生この方法が一般的に紹介されている「プルダウンの項目追加」ですが、最速・最効率を目指す、ExcelCampの流派ではありません。
以下で紹介するOFFSET関数とCOUNTA関数の併用技をぜひ紹介させてください。
【結論】ExcelCampおすすめの「最速技」を紹介
結論、元の値に以下の関数を入力だけです。
=OFFSET(参照元!$A$3,0,0,COUNTA(参照元!$A$3:$A$100),1)
これでEnterキーでOKを押すと、参照元に値を追加すれば自動的にプルダウンの項目も追加されるようになります。
ExcelCamp講師 野沢先生OFFSET関数とCOUNTA関数の2つの関数を使用していますので、
①OFFSET関数の説明
②COUNTA関数の説明
③なぜこれで自動追加できるかの説明
に分けて説明していきます。
①OFFSET関数の説明
OFFSET関数は「セルの参照範囲」を決める関数です。
以下の要素で成り立っています。
OFFSET関数の要素説明
=OFFSET(基準の位置,ずらす行数,ずらす列数,高さ,幅)
開始セルの指定：「基準の位置」「ずらす行数」「ずらす列数」
終了セルの指定：「高さ」「幅」
簡単に言えば、「このセル(開始セル)」から「このセル(終了セル)」までセル参照してね。と指定する関数です。
なので例えば、
=OFFSET(A1,1,2,10,1)と書くと、
A1セルから下に1行、右に2行ずらしたセル(開始セル=C2)から、高さ10行、幅1行の範囲(終了セル=C12)を参照してね。という意味になるので、C2:C12と同じ意味になります。
ExcelCamp講師 野沢先生開始セルを「基準の位置」にしたい場合は、
OFFSET(基準の位置,0,0,~)と書きます。
②COUNTA関数の説明
COUNTA関数は「範囲内のデータが書かれたセルの個数」を数える関数です。
COUNTA関数の説明
=COUNTA(セル範囲)
※「数値データ」も「文字データ」もすべて数えます。
例えば以下のようにCOUNTA関数を書きます。
データが書かれたセルは「a」「b」「c」「1」「d」の合計5つあるので、結果は「5」と表示されます。
ExcelCamp講師 野沢先生似た関数にCOUNT関数がありますが、COUNT関数は数値データだけを数えるときに使います。
上記の範囲を＝COUNT()で入力すると、数値データが書かれているのは「1」だけなので、結果は「1」と表示されます。
③なぜこれで自動追加できるかの説明
まずは開始セルから見ていきます。
=OFFSET(基準の位置,ずらす行数,ずらす列数,高さ,幅)
=OFFSET(参照元!$A$3,0,0,COUNTA(参照元!$A$3:$A$100),1)
「基準の位置」は参照元シートの「A3」セルです。これはプルダウンの参照が始まるセルです。
続けて「ずらす行数」と「ずらす列数」がどちらも「0」なので、開始セルはそのままA3セルになります。
=OFFSET(参照元!$A$3,0,0,COUNTA(参照元!$A$3:$A$100),1)
=OFFSET(A3セルから参照開始,COUNTA(参照元!$A$3:$A$100),1)
という意味です。
ExcelCamp講師 野沢先生ここは、とにかく開始セルにA3セルを指定できればよいので、
=OFFSET(A1,2,0,~)
=OFFSET(A2,1,0,~)
=OFFSET(B1,-1,2,~)
など他の書き方でもうまくいきますが、=OFFSET(開始セル,0,0,~)が一番効率的でおすすめです。
次に、終了セルをみます。
=OFFSET(A3セルから参照開始,高さ,幅)
=OFFSET(参照元!$A$3,0,0,COUNTA(参照元!$A$3:$A$100),1)
「高さ」の項目にCOUNTA関数を書いています。
COUNTA関数の中身を見ると、A3からA100の範囲にあるデータの個数を数えています。
画像のように、プルダウンで範囲にしたいA3セルからA100までが選択されています。
ExcelCamp講師 野沢先生今回はA100セルまで範囲を選択していますが、これはA200セルでもA300セルでも、限界行数であるA1048576セルでも構いません。
プルダウンの項目数が、どんなに増えても収まるであろう範囲が選択できていればOKです。
データが書かれているセルは「参加」「不参加」の2つしか無いので、COUNTA関数の結果は「2」となります。
そして最後に「幅」の項目には「1」と書かれているので、OFFSET関数の結果はA3セルから高さ2行、幅1行の範囲を参照するという意味になり、A3:A4の範囲が参照されます。
例えばここから参照元シートに「不明」を追加すると、COUNTA関数の値が「3」に変わり、OFFSET関数の範囲もA3:A5に自動で変わるため、プルダウンの項目も自動で追加されるという仕組みです。
「セル参照」のプルダウン項目追加を自動化する上での3つの注意点
注意点1：「列全体」を選択するとうまくいかないことがある
OFFSET関数内に書くCOUNTA関数のセル範囲を$A:$Aのように列全体にして、「=COUNTA($A:$A-1)」と書く方法もありますが、Excelのバージョンによってはエラーになってしまうことがあります。
ExcelCamp講師 野沢先生プルダウンの項目が100行を超えることは早々ないと思うので、無難に100行ほどを選択する書き方をしておくのがおすすめです。
注意点2：途中に空白セルを入れるとうまくいかない
画像のように、セル参照元のシートに項目を記入するとき、途中に空白セルを入れるとうまくいかなくなります。
このように項目が壊れてしまいますので、セル参照元のシートに項目を書くときには間に空白セルが無いようにしてください。
注意点3：書いた順番通りにプルダウンも表示される
プルダウンの項目に表示される順番は、セル参照元のシートに書かれた順番が完全に反映されます。
例えば「参加」「不明」「不参加」の順番で書けば、プルダウン項目の順番も「参加」「不明」「不参加」になります。
ExcelCamp講師 野沢先生なのでプルダウン項目の順番を変えたいときには、参照元シートを変更するようにしてください。
休日「1日」を投資して、平日の生産性を最大「20倍」にするExcel研修
私たちが提供するExcel研修「上位1%を目指す！ExcelCamp」では、これまで延10,000名以上の方に研修を受講いただき、受講者満足度は9.71(※)という大変高い評価を頂いております。
※10点満点中の評価の平均値。
休日1日を研修に使うのは「やや重たい」というご意見もいただきます。
しかし、本当にExcelスキルを実践で使えるレベルで高めるとなると、通常数年はかかるものです。
実際にExcelCampで教える内容は、代表の中田が業務コンサルタントとして数年間、毎日10時間以上Excelと向き合った結果ようやく生まれたスキルです。
そのことを考えると、休日の「たった1日」を投資して、その後のExcel業務の生産性を圧倒的に高めることは決して悪くない選択だと思います。
参加者の中にはお伝えしたスキルを仕事の現場で活かし、生産性が「20倍」になったという事例もあります。
動画サービス「bizplay(ビズプレイ)」で研修の一部を無料公開中！
※全4回の動画のダイジェスト版です。
※第1~4回の内容をフルで視聴するには、bizplayへの無料会員登録が必要です。
現在セミナー動画見放題サービス「bizplay」で、ExcelCamp Day1の講義内容の一部を無料配信しています。
全4回に分けてExcelが得意になるための考え方や覚えておくべき基本操作を解説していますので、Day1への参加を迷われている方はぜひ一度ご覧ください。
著書「神速Excel」も絶賛発売中
