トップ > スキル : アプリケーション > Excel(エクセル) > 基礎編(関数をマスターする)

Excel

関数をマスターする

条件付書式と入力規則と連携する

別の機能の中で関数を使うと、その機能の応用範囲が広がります。

「条件付き書式」と合わせて活用

◆ 四半期の売上が最大のセルに色を塗る(MAX関数)

四半期ごとにまとめた表があり、各四半期で最大の売上金額のセルに色を付けて強調したい時には、条件付き書式の中で関数を使います。

まず、一覧表の数値部分をすべて選択します。(セル範囲B3:F10)メニュー[書式]-[条件付き書式]を選択します。

左から「セルの値が」、「次の値に等しい」を選び、右の欄に「=MAX(B$3:B$10)」と入力します。「書式」ボタンをクリックして、好きなパターンの色を設定します。後は、[OK]ボタンを押して設定を完了します。
行番号の前に「$」を付けるの、他のセルでは列番号は「C」「D」…と変更しますが、行番号は「3から10」に固定するためです。


◆ 重複したデータを書体を変えて知らせる(COUNTIF関数)

顧客名簿などを作成しているときに、同じデータを2度登録してしまうミスもあるかと思います。このような時に、入力している段階で、すでに登録済みであることが判明すれば助かります。条件付き書式の中でCOUNTIF関数を設定すれば、2重登録のような失敗を防げます。

例題で、会員NOが同じ番号が入力されたら太字で表示されるように設定していきます。まず、A列すべてを選択し、メニュー[書式]-[条件付き書式]を選択します。

左端は「数式が」を選びます。右側の空欄に「=COUNTIF(A:A,A1)>1」と入力します。これは、A列の各セルごとに、A列に同じデータがいくつあるかをCOUNTIF関数で数え、それが「1」より大きい、つまり同じデータが2つ以上あることを表す条件式になります。あとは、「書式」ボタンで「太字斜体」を選んで、設定を完了させます。
設定した直後に、同じデータがない場合は、文字列に変化がありませんが、セルA11に「S1386」と入力すると、既に登録されている行の会員NOが太字斜体になります。


◆ カレンダーで「土曜は青、日曜は赤」と塗り分ける(WEEKDAY関数)

カレンダーを作成して、土日に色を付けたいケースがあります。日付データの曜日を調べる関数はWEEKDAY関数を使います。条件付き書式にこの関数を使うと、曜日ごとに色を付けることが出来ます。

まず、日にちと時間のセルをすべて選択します。(セル範囲A3:B33
条件付き書式のダイアログボックスを表示させます。

左の欄は「数式が」を選択し、右側の欄に「=WEEKDAY($A3,2)=6」と入力します。これは日付データの曜日番号が「6」、つまり「土曜日」という条件式になります。書式ボタンを使って、パターンの色を薄い青に設定します。次に、下の[追加]ボタンをクリックして、条件2を追加します。同様に、左側は「数式が」、右側の欄に「=WEEKDAY($A3,2)=7」と入力して、日曜日を指定する条件式を入れます。書式をピンク色に設定したら、[OK]ボタンをクリックして設定を完了します。

関数を使い「入力ミス」を防ぐ

◆ 重複データ入力時に警告画面を表示する(COUNTIF関数)

データが重複して入力された時に、警告を表示させたい場合があります。これには、「入力規則」機能を使います。
例題で、入力済みの「会員NO」と同じ番号を入力されたら、警告が出るようにしてみます。

まず、「会員NO」のA列全体を選択し、メニュー[データ]-[入力規則]を選択します。

[データの入力規則]ダイアログボックスが表示されます。[設定]タブを選択し、[入力値の種類]を「ユーザー設定」にします。[数式]欄に「=COUNTIF(A:A,A1)=1」と入力します。これは、「セルに入力したデータがA列の中に1件だけある」つまり「入力済みの会員NOと重複していない」という条件になります。この条件を満たすデータだけが入力できるようになります。
次に[エラーメッセージ]タブを選択します。

入力した会員NOが重複する場合に、画面に表示される警告メッセージを指定します。
セルA14に既に入力されている会員NOを入力すると警告画面が表示されることを確認します。


◆ 「15分単位」以外の時刻を入力できないようにする(MINUTE、MOD関数)

勤務時間を15分単位で計算するために、出退勤簿に15分単位で時刻を入力させるようにしたい場合、指定したデータしか入力できないようにする入力規則機能を利用します。

まず、時刻を入力するセル(セル範囲B4:D18)を選択し、メニュー[データ]-[入力規則]を選択します。

[設定]タブを選択し、[入力値の種類]は「ユーザー設定」を指定します。[数式]の欄に「=MOD(MINUTE(B4),15)=0」と入力します。これは「入力された時刻の「分」を15で割った余りがゼロである」という式になります。これで、「分」の値が15の倍数の時刻しか入力できないようになります。後は、エラーメッセージに条件を満たさないデータが入力された時に表示されるメッセージを指定します。

設定が完了したら、セルB4に「9:40」と入力してエラーメッセージが表示されるかを確認します。


◆ 半角文字のみ入力を許可する(LEN、LENB関数)

=LEN(文字列)

指定した文字列が全部で何文字かを数えます。全角文字も半角文字も1文字として計算します。

=LENB(文字列)

指定した文字列が半角文字換算で何文字かを数えます。半角文字は1文字、全角文字は2文字分として計算します。

入力するIDやNOには、半角文字しか入力できないようにしたいケースがあります。これも入力ミスを防げる仕組みと同じで、入力規則機能と関数の組み合わせで実現できます。
まず、IDを入力するセルB6を選択し、メニュー[データ]-[入力規則]を選択します。

[設定]タブを選択し、[入力値の種類]は「ユーザー設定」を指定します。[数式]の欄に「=LEN(B6)=LENB(B6)」と入力します。文字の数を数えるLEN(レン)関数とLENB(レンビー)関数を使うことで、全角文字も半角文字も1文字として数えたものと、半角文字は1文字、全角文字は2文字として数えたものが等しければ、すべて半角であることが判明できます。あとは、半角以外の文字が入力された時のエラーメッセージを設定します。

ここまで、さまざまな関数を紹介しました。ここで取り上げた関数を覚えておけば、ビジネス文書全般に応用できます。ポイントは、関数を組み合わせることで、柔軟な活用が実現できることです。職種によって、よく利用する関数、ほとんど利用しない関数が異なると思いますが、幅広い関数の知識を持てば、今以上の応用力のあるエクセル術が身につきます。また、条件付き書式や入力規則などとも連携して、作業効率が計れるような仕組みを是非マスターしていただきたいと思います。