よく見られているExcel即効テクニック10選
「vlookup 使い方」で何回検索しましたか？
Excelを使っていてわからないことがあれば検索して調べるのが一般的なやり方です。Excelの関数は全部で500個ほどあるといわれています。さらに関数と関数を組み合わせる応用技などもあり、使いこなせてないことが多いのではないでしょうか。意外と知らない便利な関数や機能、さらにショートカットを使えば作業時間が10分から5分へ短縮できます。
mougでは、知っていると作業が楽になるテクニックを2,500個以上ご紹介しています。その中でもよく見られているExcelの即効テクニックをまとめました。あなたのスキルアップにお役立てください。
すぐに役立つExcelテクニック
＜目次＞
- [COUNTIF]＋[あいまいな条件]指定した文字列が含まれるかどうかを判断する
- [DATEDIF]生年月日から年齢を計算する
- [COUNTIF]＋[IF]指定したデータがリスト内に存在するかどうかを確認する
- [IFERROR]VLOOKUP関数で#N/Aエラーを表示させない方法
- [ショートカットメニューまたは名前ボックス]非表示にした1行目やA列を再表示する
- [DATEDIF]指定期間の年月数を求め○年○ヶ月と表示する
- [ジャンプ機能]不要な空白セルを一括で削除する
- [置換]数式の参照先を一気に変更する
- [AVERAGEIF]0(ゼロ)のデータを無視して平均を求める
- [クリップボード]セルを図としてコピーする
1.[COUNTIF]＋[あいまいな条件]指定した文字列が含まれるかどうかを判断する
数値の場合、指定した条件を満たすかどうかはIF関数だけで簡単に求めることができますが、文字列の場合、「○○という文字列を含む場合は～」といったようなあいまいな条件指定がIF関数だけではできません。これは、条件の指定にワイルドカードを使えないからなのですが、COUNTIF関数を組み合わせることにより、あいまいな条件指定が可能になります。
＜手順＞
- 結果を表示するセルを選択します。
- 選択したセルに、=IF(COUNTIF(範囲,条件),真の場合,偽の場合)を入力します。
（例）セルA1に「Excel」という文字列が含まれている場合は「○」を表示し、そうでない場合は空白にする場合、 =IF(COUNTIF(A1,"*Excel*"),"○","")と入力。 - 数式を入力したセルに判断結果が表示されます。
＜ポイント＞
文字列の全角半角、大文字小文字も区別されるため、条件を指定するときは注意してください。
2.[DATEDIF]生年月日から年齢を計算する
生年月日を計算するには、DATEDIF関数を利用すると便利です。開始日から終了日までの期間を指定した単位で表します。ここでは、セルA1に生年月日、セルB1に本日の日付を入力し、セルC1に本日の満年齢を表示する方法を解説します。
＜手順＞
- セルA1に生年月日を入力します。
- セルB1に「=TODAY()」と入力します。
- セルC1に「=DATEDIF(A1,B1,"Y")」と入力します。
＜ポイント＞
「"Y"」は期間内の満年数です。ここを「"D"」にすると期間内の日数を表示することもできます。単位の引数によって違うデータを出すこともできます。
3.[COUNTIF]＋[IF]指定したデータがリスト内に存在するかどうかを確認する
指定範囲のセルの中から、検索条件に一致するセルの個数を求めるCOUNTIF関数と、論理式の結果によって指定した値を返すIF関数を使い、指定したデータが指定したセル範囲内に存在するかどうかを判定する方法を紹介します。
＜手順＞
- 結果を表示するセルを選択します。
- 選択したセルに、=IF(COUNTIF(範囲,検索条件),"真の場合に表示する値","偽の場合に表示する値")を入力します。
（例）セル範囲A3:F100に、セルA1に入力したデータと一致するデータがあるかないかを確認し、"ある" "なし"で返す場合、 =IF(COUNTIF(A3:F100,A1),"あり","なし")と入力。 - これで、指定したデータが指定したセル範囲内に存在するかどうかが求められ、結果が表示されます。
＜ポイント＞
入力したデータが検索範囲内にいくつ存在するかを求めたい場合は、IF関数の中にCOUNTIF関数を使うことはせず、COUNTIF関数のみで数式を作成します。
4.[IFERROR]VLOOKUP関数で#N/Aエラーを表示させない方法
VLOOKUP関数を使用して完全に一致するデータを検索した場合、falseが返ると#N/Aエラーが表示されます。そこで、Excel 2007以降追加されたIFERROR関数を使用して#N/Aエラーを表示させない方法を紹介します。
＜手順＞
- VLOOKUP関数を入力したセルを選択します。
- 入力済みの数式に、IFERROR関数を追加します。
=IFERROR(VLOOKUP(検索値，範囲，列番号，FALSE),"")
＜ポイント＞
#N/Aエラーの代わりに「該当なし」と表示させたい場合は、「""」を「"該当なし"」と入れると、セルに表示させることができます。
5.[ショートカットメニューまたは名前ボックス]非表示にした1行目やA列を再表示する
ワークシートの1行目やA列を非表示にしたものの、再表示する方法がわからなくて困ったことありませんか？非表示にした行や列を再表示させるには、行であれば、非表示となっている行を挟む上下の行を範囲選択し、列であれば、非表示となっている列を挟む左右の列を範囲選択し、再表示を実行すればよいのですが、シートの端にあるA列や1行目が非表示となっている場合、上の行や左の列がないので、この方法で再表示させることができません。そこで、再表示する方法を3つご紹介します。
ショートカットメニューを使う方法【1】
マウスポインタを全セル選択ボタンの右または下に近づけると、ポインタの形状が「←||→」になりますので、右クリックして一覧から「再表示(U)」をクリックすると、非表示だったA列や1行目が再表示されます。
ショートカットメニューを使う方法【2】
マウスポインタを全セル選択ボタンの右または下に近づけると、ポインタの形状が「←||→」になりますので、そのまま右または下へドラッグすると、非表示だったA列や1行目が再表示されます。
名前ボックスを使う方法
- [名前ボックス]に、A1と入力し、[Enter]キーを押します。これで、セルA1が選択されている状態になります。
- [ホーム]タブ－[セル]－[書式]－[非表示/再表示]を選択し、[列の再表示]または[行の再表示]をクリックすると、非表示だったA列や1行目が再表示されます。
＜ポイント＞
非表示になっているすべての行や列を再表示したい場合は、ワークシート全体を選択し、列番号の上または行番号の上で右クリック－［再表示］を選択します。
6.[DATEDIF]指定期間の年月数を求め○年○ヶ月と表示する
DATEDIF関数を使い、指定した2つの日付の期間の年数と月数を求め、更に、○年○ヶ月と表示させる方法を紹介します。年数だけでなく、1年に満たない月数も同時に求めたいとき、年数と月数をひとつのセルにわかりやすく表示させることができるので、大変便利です。
＜手順＞
- 年月数を求めたいセルを選択します。
- 選択したセルに、=DATEDIF(開始年月日,終了年月日,"Y")&"年"&DATEDIF(開始年月日,終了年月日,"YM")&"ヶ月"と入力します。
（例）入社日から本日までの年月を求めて○年○ヶ月と表示する場合、セルE2に以下の式が入ります。
=DATEDIF(D2,TODAY(),"Y")&"年"&DATEDIF(D2,TODAY(),"YM")&"ヶ月"
＜ポイント＞
DATEDIF関数は、[関数の挿入]や[関数の貼り付け]ダイアログの関数名一覧には表示されない関数のため、利用する際は直接数式バーに入力してください。
7.[ジャンプ機能]不要な空白セルを一括で削除する
ジャンプ機能を使って、選択範囲内の空白セルを一括で削除し、隣接しているデータを左または上方向へシフトさせることができます。
＜手順＞
- 削除したい空白セルを含むセル範囲を選択します。
- ［Ctrl］+［G］キーを押し、［ジャンプ］ダイアログボックスを表示します。
- ［ジャンプ］ダイアログボックスの［セル選択(S)...］ボタンをクリックします。
- ［選択のオプション］ダイアログボックスの、［空白セル(K)］オプションボタンをクリックし、［OK］ボタンをクリックします。
- ［Ctrl］+［-］キーまたは右クリック－［削除(D)...］で［削除］ダイアログボックスを表示します。
- ［左方向へシフト(L)］または［上方向へシフト(U)］オプションボタンをクリックし、［OK］ボタンをクリックします。
＜ポイント＞
空白セルを含む行または列全体を削除したい場合は、(6)で表示される［削除］ダイアログの、［行全体(R)］または［列全体(C)］オプションボタンをクリックしてください。
8.[置換]数式の参照先を一気に変更する
数式も置換え機能の対象となることを利用すると、数式の参照先や、使用している関数を一気に置換えることが可能です。以下の例では、ワークシート「Sheet1」を参照している数式を、「Sheet2」の同アドレスを参照する数式に置き換えます。
＜手順＞
- ［ホーム］タブ－［検索と選択▼］－［置換(R)...］を選択するか、［Ctrl］+［H］のショートカットキーにより、［検索と置換］ダイアログボックスを表示。
- ［検索する文字列(N)］ボックスに「Sheet1!」を入力、［置換後の文字列(E)］に「Sheet2!」を入力します。
- ［オプション(T)>>］をクリック、［完全に同一なセルだけを検索する(O)］または［セル内容が完全に同一であるものを検索する(O)］チェックボックスがオフであることを確認し、［OK］ボタンを押す。
- そのシートに含まれる、全ての「Sheet1!」が「Sheet2!」に置換わり、結果として、数式の参照先も変更されます。
＜ポイント＞
数式の参照先を変更する際には、置換え前の文字列が、対象とする参照先以外の文字列と重ならないように注意してください。例えば、「Sheet1!」と「Sheet2!」を置換える際に、「Sheet」と「!」の部分が同じだからと言って、「1」を「2」に置換えようとすると、全ての「1」が「2」に置換わってしまい、意図した結果を得られない場合があります。
9.[AVERAGEIF]0(ゼロ)のデータを無視して平均を求める
平均を求めるというと、AVERAGE関数が思い浮かびますが、AVERAGE関数は数値データの平均を求める関数のため、引数に含まれる文字列、論理値、空白セルは無視されても、0(ゼロ)という値が入力されているセルは平均の対象に含まれてしまいます。0(ゼロ)のデータは無視して平均を求めたい場合は、AVERAGE関数ではなく別の関数を使用します。
＜手順＞
- 計算結果を表示したいセルを選択します。
- 選択したセルに、=AVERAGEIF（範囲,条件,[平均対象範囲]）を入力します。
（例）平均を求めるセル範囲が C24:N24 の場合は、 =AVERAGEIF(C24:N24,"<>0")と入力。
＜ポイント＞
「"<>0"」は「0以外」という条件になります。
10.[クリップボード]セルを図としてコピーする
ワークシート上のセルは、図としてコピーすることができます。コピーした図は、Wordなどの他のアプリケーションのファイルへ貼り付けることもできるので、資料作成等、幅広くご活用ください。
＜手順＞
- 図としてコピーしたいセル範囲をドラッグして選択します。
- [ホーム]タブ－[コピー]の▼をクリック、[図としてコピー(P)...]を選択します。
- [図のコピー]ダイアログボックスの[表示]と[形式]は、それぞれ任意のものを選択し、[OK]ボタンをクリックします。
- コピーした図は、[Office クリップボード]に保持されているのでExcelほか、WordやPowerPointへ貼り付けることができます。
＜ポイント＞
図として貼り付けられるため、元データに変更があっても、貼り付けた図に反映されません。
スキルアップに役立つサイト
PC作業効率のヒントを動画で紹介する“KEZURU“
仕事でパソコンを使うシーンは様々ですが、もっといろいろな機能を知っていれば効率よく作業ができるのに・・・と思ったことはありませんか？Excelの関数や機能などをどのように実務に活かしていくのか動画でご紹介します。
スキルを定着させよう
Excelの利用スキルを証明できる資格「MOS（マイクロソフト オフィス スペシャリスト）」を目指してみませんか？
資格対策教材は体系的にExcelについて学習することができるので、さらにスキルアップできます。
▶ MOS公式サイト
