Warning: Undefined array key 4 in /home/fastplus/fastclassinfo.com/public_html/wp-content/themes/magazine-pro/functions.php on line 387
Warning: Undefined variable $nlink in /home/fastplus/fastclassinfo.com/public_html/wp-content/themes/magazine-pro/functions.php on line 302
Warning: Undefined variable $nlink in /home/fastplus/fastclassinfo.com/public_html/wp-content/themes/magazine-pro/functions.php on line 302
Warning: Undefined variable $nlink in /home/fastplus/fastclassinfo.com/public_html/wp-content/themes/magazine-pro/functions.php on line 302
Warning: Undefined variable $nlink in /home/fastplus/fastclassinfo.com/public_html/wp-content/themes/magazine-pro/functions.php on line 302
基本のエクセル関数であるIF関数、SUMIF関数、VLOOKUP関数などは認知度が高く、様々なウェブページで紹介されています。
このような基本関数を知っておくことは重要です。
しかし「エクセル関数を仕事での活用方法」や「作業効率化に直結する関数の使い方」を理解しておくことは、はるかに役立ちます。
たとえば、「IF関数は条件分岐の関数」として理解するよりも、「IF関数とCOUNTIF関数を組み合わせし、データの重複数を調べることができる」というように、エクセル関数を使って出来ることを理解しておくと、本当の意味でエクセル関数を活用することができます。
実際にエクセル関数で出来ることは多岐にわたり、簡単に作業効率化してくれる「すごい関数」や「面白い関数」がいくつも存在します。
イメージしやすいところで言えば、以下のことが可能です。
しかし上記のことができるのは分かっていても、具体的にどのようにエクセル関数を組めばいいのか理解するのは難しいものです。
このページでは、エクセル関数を使って作業効率化できることを紹介し、具体的な方法論(エクセル関数の作り方)を解説していきます。
目次
- 1 エクセル関数の正しい使い方を学ぶには？
- 2 エクセル関数で出来ることを一覧で紹介｜連番、重複削除、抽出、分析、並び替え、翻訳
- 3 1. 数値の連番表示｜Row関数
- 4 2. 毎月の連番表示｜Edate関数
- 5 3. 毎週の連番表示｜+7の数式を活用
- 6 4. 重複回数の自動計算｜Countif関数とIf関数
- 7 5. 重複削除したプルダウンリストの作成｜Unique関数
- 8 追加. 日付データを年月ごとにまとめる｜UNIQUE関数とTEXT関数
- 9 追加. 重複を削除したリストを列で表示(横に並べる)｜UNIQUE関数とTranspose関数
- 10 6. 条件に合致するデータを抽出｜Filter関数
- 11 7. データ並び替え｜Sort関数
- 12 8. 四半期を出力｜IF関数とChoose関数
- 13 9. 指定した列の最終行を取得｜Offset関数とCounta関数
- 14 10. シート全体のデータを取得｜Offset関数とCounta関数
- 15 11. エクセル関数でウェブページのタイトルを自動取得｜PageTitle関数(自作)
- 16 12. エクセル関数で自動翻訳｜GoogleTranslate関数(自作)
- 17 実務での応用事例
- 18 エクセルマクロVBAでできること
エクセル関数の正しい使い方を学ぶには？
エクセル関数の有名どころとしてIF関数、SUMIF関数、VLOOKUP関数などがあります。
これらはエクセルの基本関数として、多くの書籍やウェブページで紹介されています。
しかしながらエクセル関数は関数そのものを知っておくよりも、仕事での使い方・活用方法を理解しておく方がはるかに重要です。
なぜなら関数は手段であって、目的ではないからです。
関数の知識よりも応用方法を学ぶ
そもそも仕事を進めるにあたって、「VLOOKUP関数を使って、仕事を効率化してほしい」といった業務指示を出されることはありません。
よくて「取引先マスタを作って、請求書テンプレートからマスタ参照できるようにしてほしい」といった業務指示を出される程度のはずです。
実際にはそのような指示をもらうことはなく、「面倒な作業に自ら気づいて関数を使って改善していく」というのが通常かと思います。
すなわち、実際の仕事では関数の知識を問われることは少なく、関数の応用方法(自ら問題に気づき、改善する力)を求められるのです。
関数の応用方法は事例から学べる
IF関数は理解しているけれど、「どのような場面で使うと仕事で効果的なのか？」が分からない人は少なくありません。
たとえば本ページでは、「IF関数とCOUNTIF関数を活用し、データの重複数を調べる方法」を紹介しています。
実際、重複数を調べて、フィルター機能を使うことで、重複データだけを削除することができます。
このような効率的なエクセル作業の方法は、IF関数の知識だけを持っていても身につくものではありません。
そのため本ページでは、事例を使って、仕事での活用方法をという切り口で、エクセル関数でできることを紹介していきます。
エクセル関数で出来ることを一覧で紹介｜連番、重複削除、抽出、分析、並び替え、翻訳
「エクセル関数で出来ることをは何か？」について一覧で紹介します。
2. 毎月の連番表示｜Edate関数
3. 毎週の連番表示｜+7の数式を活用
4. 重複回数の自動計算｜Countif関数とIf関数
5. 重複削除したプルダウンリストの作成｜Unique関数
6. 条件に合致するデータを抽出｜Filter関数
7. データ並び替え｜Sort関数
8. 四半期を出力｜IF関数とChoose関数
9. 指定した列の最終行を取得｜Offset関数とCounta関数
10. シート全体のデータを取得｜Offset関数とCounta関数
11. エクセル関数でウェブページのタイトルを自動取得｜PageTitle関数(自作)
12. エクセル関数で自動翻訳｜GoogleTranslate関数(自作)
このページでは上記の関数を紹介していきます。
1. 数値の連番表示｜Row関数
関数の解説
2行目から連番を出力する場合、「=row()-1」と入力することで連番を出力することが可能です。
なお3行目から連番を出力したい場合は「=row()-2」、4行目から連番を出力したい場合は「=row()-3」となります。
連番を下のセルに反映させるときは、[Shift] + [↓]でショートカットキーで選択範囲を広げて、[Ctrl] + [D]でコピーすることができます。
セルA2を[Ctrl] + [C]でコピーして、[Ctrl] + [V]で貼り付けることも可能ですが、ショートカットキーを2回実行する必要があるため、[Ctrl] + [D]の一回で処理する方が簡単です。
Row関数とは
ROW関数の書式には、[範囲]を引数として設定することができます。
[範囲]にセル番号を入力すると、そのセルの行番号を返します。
[範囲]の引数を省略すると、行番号を調べることができます。
2. 毎月の連番表示｜Edate関数
Edate関数を使うと、月を連番で表示することができます。
Edate関数の引数
Edate関数は上記の引数を取ります。
この説明だけだとわかりづらいので、本事例の場合を説明していきます。
Edate関数のよる日付出力
引数1「開始日」に、引数2「月」分を加算した日付を返します。
| 引数 | 引数の値 | 説明 |
|---|---|---|
| 開始日 | A2 | 起算日を表す日付を指定します。 日付は、DATE 関数を使って入力するか、他の数式または他の関数の結果を指定します。日付を文字列として入力した場合、エラーが発生することがあります。 |
| 月 | 1 | 開始日から起算した月数を指定します。 正の数を指定すると起算日より後の日付を返し、負の数を指定すると起算日より前の日付を返します。 |
この事例では、開始日はセルA2の2021/8/1で、月は1なので、2021/9/1を返します。
A3に入力した関数をA7までコピペしたいので、Shift + ↓ でセル範囲を選択して、Ctrl + Dでコピペします。
そうすると、以下のようにA3で設定したEDATE関数をA7まで適用させることができます。
これで月の連番表示を簡単に行うことができます。
3. 毎週の連番表示｜+7の数式を活用
日付の入力されたセル(A2)に+7を加算することで、週を連番で表示することができます。
この事例では、開始日はセルA2の2021/8/2です。
1週間後は必ず7日後なので「+7」することで、1週間後の日付「2021/8/9」を出力することができます。
A3に入力した「=A2+7」をA7までコピペしたいので、Shift + ↓ でセル範囲を選択して、Ctrl + Dでコピペします。
そうすると、以下のようにA3で設定した数式をA7まで適用させることができます。
これで週の連番表示を簡単に行うことができます。
4. 重複回数の自動計算｜Countif関数とIf関数
重複回数を計算するときは、IF関数とCOUNTIF関数を組み合わせることで調べることが可能です。
具体的には以下のように形となります。
・B2:B9で重複がなければ、C列を空欄
このように上記の関数を使うことで、B列のそれぞれの値が出現する回数をC列に出力します。
関数の解説
ここではIF関数とCOUNTIF関数を組み合わせています。
具体的にはIF関数を基本にして、IF関数の中でCOUNTIFを使って重複を調べます。
IF関数の基本の形をもとに説明していきます。
論理式の条件がTrueの場合は[値が真の場合]、Falseの場合は[値が偽の場合]を出力します。
IF関数のMicrosoftのReferenceはこちらです。
論理式｜COUNTIF($B$2:$B$9,B2)>1
・セル範囲｜$B$2:$B$9
・検索値｜B2
セル範囲である「$B$2:$B$9」の中に、B2の値が出現する回数を調べることができます。
ここでは「$」を付けていますが、これにより絶対参照としています。
これが1より大きければ値が真の場合を出力し、1以下であれば値が偽の場合を出力します。
値が真の場合｜COUNTIF($B$2:$B$9,B2)
論理式の結果が、1より大きければ、以下の「値が真の場合」を出力します。
・セル範囲｜$B$2:$B$9
・検索値｜B2
セル範囲である「$B$2:$B$9」の中に、B2の値が出現する回数を所定のセルに出力します。
値が偽の場合｜””
論理式の結果が、1以下であれば[値が偽の場合]を出力します。
ここでは「””」なので、空欄となります。
5. 重複削除したプルダウンリストの作成｜Unique関数
3-2. プルダウンリストの作成
以下で解説します。
3-1. 重複削除したリストの作成unique関数
B2:B6において重複を削除したリストを、セルD3に作成します。
注意点
なおunique関数は、Office365でのみ使用できる関数のため、Office 2016やOffice 2019などでは使用できません。
3-2. プルダウンリストの作成
D3で作成したリストを使って、プルダウンリストを作成していきます。
重複削除したプルダウンリストの作成手順
2. リボンの「データ」を選択
3. 「データの入力規則」を選択
4. 「データの入力規則(V)」を選択
5. 「入力値の種類(A)」でリストを選択
6. 「元の値(S)」で「=D3#」と入力
これで以下のとおり、D2にプルダウンリストを作成することができます。
6で「=D3#」と入力しますが、UNIQUE関数はスピルという機能で動き、数式を入力したセルだけではなく、隣接するセルにも値が出力されます。
このようなスピル機能を有する関数を扱う場合、#を付けないと、値を参照することができません。
B列のデータが増えた場合の対処法
この場合、UNIQUE関数、OFFSET関数、COUNTA関数を以下のように組み合わせます。
上記のように関数を組み合わせることで、B列の最終行までUNIQUE関数が読み込んでくれるようになります。
これでデータが増えていけば、自動的にプルダウンリストを増減させることができます。
追加. 日付データを年月ごとにまとめる｜UNIQUE関数とTEXT関数
UNIQUE関数の必須引数として「配列」を取ります。
この配列の部分に「TEXT(B2:B18,”yyyy-mm”)」にTEXT関数でyyyy-mmの表示形式を使うと、年月にまとめることができます。
UNIQUE関数単体では同じ日付を除去するのが限界
UNIQUE関数は以下の引数を取ります。
「配列」=B2:B18
UNIQUE関数は引数「配列」に含まれる値の内、重複している値を削除させることが可能です。
したがって引数「配列」にB2:B18を入れると、「=UNIQUE(B2:B18)」となると同じ日付を削除してくれます。
しかし年月でまとめることはできません。
TEXT関数で対象範囲の日付形式をyyyy-mmに変更
TEXT関数は以下の引数を取ります。
「値」=B2:B18
「表示形式」=”yyyy-mm”
「値」にB2:B18の範囲を入力し、「表示形式」に”yyyy-mm”と入力すると、上記のようにB2:B18の日付を年月(yyyy-mm)形式で表示させることが可能です。
UNIQUE関数とTEXT関数で日付データを年月表示させる
最終的に以下のようにUNIQUE関数の引数「配列」に「(TEXT(B2:B18,”yyyy-mm”)」を入力すると、年月重複を削除してくれます。
これで日付データから、重複削除した年月を取得することができます。
これ単体では威力を発揮しませんが、sumifs関数などのデータ集計を行う関数と組み合わせると威力を発揮します。
追加. 重複を削除したリストを列で表示(横に並べる)｜UNIQUE関数とTranspose関数
TRANSPOSE関数は必須引数として「配列」を取ります。
この配列の部分に「UNIQUE(B2:B11)」を入れ込むことで列(横)に表示することができます。
UNIQUE関数単体では行表示
UNIQUE関数単体では、行表示(縦に並べる)されます。
したがって行列を入れ替えるために以下の関数を組み合わせます。
TRANSPOSE関数で縦横(行列)を入れ替える
TRANSPOSE関数は、引数「配列」に入れ込んだ値の縦横(行列)を入れ替えることができます。
したがって引数「配列」にUNIQUE(B2:B11)を入れることで、重複を除外したリストを列表示させることができます。
これ単体では威力を発揮しませんが、sumifs関数などのデータ集計を行う関数と組み合わせると威力を発揮します。
6. 条件に合致するデータを抽出｜Filter関数
上記のように条件に合致するデータのみを抽出したい場合は、Filter関数を使います。
Filter関数で別シートへデータを抽出
Filter関数は以下の3つの引数を取ります。
以下で解説します。
1. 配列(データ範囲)｜Sheet4!A2:D11
「配列(データ範囲)」として、Sheet4のA2:D11の範囲を取得します。
2. 含む(抽出条件)｜Sheet4!D2:D11=”愛知販売”
Sheet4のD2:D11が「愛知販売」の場合を抽出条件とします。
3. 空の場合｜”対象なし”
「2. 含む(抽出条件)」に合致するデータがなければ、「対象なし」と出力します。
注意点
Filter関数は、Office365でのみ使用できる関数のため、Office 2016やOffice 2019などでは使用できません。
上級ワザ｜Filter関数とUNIQUE関数を組み合わせる
抽出条件をプルダウンリストで選択することで、抽出データを変更することができます。
UNIQUE関数
セルF3に上記のようにUNIQUE関数を入力することで、Sheet4のD列の値をプルダウンリストを作成することができます。
ここではセルF2にプルダウンリストを作成しています。
プルダウンリストの作成は本ページの事例3にて説明していますので、そちらをご覧ください。
FILTER関数
2番目の引数で、「Sheet4!D2:D11=F2」とすることで、プルダウンリストのセルF2の値でデータ抽出できるようになります。
実際にプルダウンリストで選択すると、以下のように抽出データをリストから選択できるようになります。
プルダウンリストで選択した値でデータ抽出できる
空欄の場合は「対象無し」
このようにFILTER関数とUNIQUE関数を組み合わせると、FILTER関数の値を変更せずにプルダウンリストで変更するだけで、抽出データを変えることができます。
FILTER関数の応用
FILTER関数は幅広い使い方ができます。
この場合UNIQUE関数、OFFSET関数、COUNTA関数を以下のように組み合わせます。
・抽出条件を複数設定する(「*」や「+」を使う)
・抽出したデータを並び替える(Sort関数を組み合わせる)
上記の組み合わせによって、より効率的にデータを抽出できます。
7. データ並び替え｜Sort関数
Sheet4のデータをSheet6に金額の大きい順に並び替えを行います。
Sort関数の引数
Sort関数は上記の引数を取ります。
この説明だけだとわかりづらいので、本事例の場合を説明していきます。
注意点
Filter関数は、Office365でのみ使用できる関数のため、Office 2016やOffice 2019などでは使用できません。
本事例ではSort関数に以下の引数をあてています。
Sort関数による並び替え
引数とその値の説明は、以下の表をご覧いただくと分かりやすいかと思います。
| 引数 | 引数の値(本事例) | 説明 |
|---|---|---|
| 配列 | Sheet4!A2:D11 | 並べ替える範囲または配列 |
| 並び替えインデックス | 3 | 並べ替えの基準となる行または列を示す数値(行番号か列番号を選択) |
| 並び替え順序 | -1 | 目的の並べ替え順序を示す数値、昇順の場合は 1 (既定)、降順の場合は -1(1か-1を選択) |
| 並び替え基準 | False | 目的の並べ替え方向を示す論理値。FALSE の場合は、行で並べ替え (既定値)、TRUE の場合は、列で並べ替え(TRUEかFALSEを選択) |
上記をまとめると、本事例では「Sheet4のA2:D11の3列目(C列)を-1(降順)で行の並び替え」を行います。
以下のようにC列の金額の大きい順で並び替えが行われます。
このようにデータを並び替えて出力したい場合は、Sort関数を使います。
Sort関数の応用
Sort関数は幅広い使い方ができます。
たとえば、以下のような組み合わせを行うことが可能です。
・データを抽出して並び替える(Filter関数を組み合わせる)
・複数条件で並び替える(Sortby関数を使う)
上記の使い方も理解しておくと、より効率的にデータを並び替え可能です。
8. 四半期を出力｜IF関数とChoose関数
上記の関数で、B列の日付データから四半期を出力します。
以下のステップで四半期情報を取得していきます。
以下で詳しく解説していきます。
1. Choose関数で四半期判定
ここでは以下のように四半期を定義しています。
ここでは上記のように、毎年4月1日から3月31日までが一会計年度の企業を事例しています。
4月から6月までが第1四半期、7月から9月までが第2四半期、10月から12月までが第3四半期、1月から3月までが第4四半期と仮定します。
この前提で、Choose関数で四半期(1~4)を出力する方法を説明していきます。
Choose関数の引数
第1引数のインデックスの値として数値を取得し、その数値に対応する値を「値1~値N」を返します。
この説明だけだとわかりづらいので、本事例の場合を説明していきます。
本事例ではChoose関数に以下の引数をあてています。
インデックスに「MONTH(B2)」、値1~値3に4、値4~値6に1、値7~値9に2、値10~値12に3をそれぞれ設定しています。
以下の表をご覧いただくと分かりやすいかと思います。
| 引数 | 引数の値 | 説明 |
|---|---|---|
| インデックス | MONTH(B2) | セルB2の日付データの月(Month)の値を取得 |
| 1 | 4 | 引数1「MONTH(B2)」の値が1のとき、4を返す |
| 2 | 4 | 引数1「MONTH(B2)」の値が2のとき、4を返す |
| 3 | 4 | 引数1「MONTH(B2)」の値が3のとき、4を返す |
| 4 | 1 | 引数1「MONTH(B2)」の値が4のとき、1を返す |
| 5 | 1 | 引数1「MONTH(B2)」の値が5のとき、1を返す |
| 6 | 1 | 引数1「MONTH(B2)」の値が6のとき、1を返す |
| 7 | 2 | 引数1「MONTH(B2)」の値が7のとき、2を返す |
| 8 | 2 | 引数1「MONTH(B2)」の値が8のとき、2を返す |
| 9 | 2 | 引数1「MONTH(B2)」の値が9のとき、2を返す |
| 10 | 3 | 引数1「MONTH(B2)」の値が10のとき、3を返す |
| 11 | 3 | 引数1「MONTH(B2)」の値が11のとき、3を返す |
| 12 | 3 | 引数1「MONTH(B2)」の値が12のとき、3を返す |
これで各月(1~12)に対応した四半期(1~4)を設定できます。
各月の情報はインデックスのMONTH(B2)で日付データからMONTH(月)を取得させています。
これで四半期の数値を取得できます。
セル範囲を選択して、関数を下のセルにコピペする
セルC2に入力したChoose関数をC3~C13に反映させたいので、Shift + ↓ でセル範囲を選択肢、Ctrl + D でコピペします。
これで四半期の数値を出力することができました。
しかし実際の業務では「2021-Q1」のように「年」と「Q」も同時に出力させたいところです。
そこでIF関数などを使ってさらにブラッシュアップしていきます。
2. IF関数で年度判定
会計年度が以下のように4月から開始するとなると、1~3月は年度として扱う必要が発生します。
たとえば2021/01/01は2020-Q4、2021/04/01は2021-Q1となります。
したがって1~3月の場合は、年を-1する必要させないといけません。
これを考慮し、IF文を作成します。
この事例では、以下のように関数を設定しています。
| 引数 | 引数の値 | 説明 |
|---|---|---|
| 論理式 | MONTH(B2)<=3 | セルB2の日付データの月(Month)の値が3以下の場合 |
| 値が真の場合 | YEAR(B2)-1 | セルB2の日付データの年(Year)の値から1引いて取得 |
| 値が偽の場合 | YEAR(B2) | セルB2の日付データの年(Year)の値をそのまま取得 |
このIF関数で、1~3月の場合は、年を-1して年度として取得することができるようになります。
実際に以下のようにB列の日付データから年度を取得できているのが分かります。
なおChoose関数のときに説明しており繰り返しになりますが、セルD3に入力したIf関数をD3~D13に反映させたいので、Shift + ↓ でセル範囲を選択肢、Ctrl + D でコピペします。
最後に「2021-Q1」のように「年度-Q」を出力させます。
3. &で関数と文字列を結合
上記の「&”-Q”&」をIF関数とCHOOSE関数の間に入力することで、関数と文字列を結合させ「年度-Q」を出力させます。
セルE3に入力した関数をE3~E13に反映させたいので、Shift + ↓ でセル範囲を選択肢、Ctrl + D でコピペします。
これで以下のように日付データから「年度-Q」を出力させることができました。
ちなみに以下の関数は、C列(四半期)やD列(年度)を削除しても、この関数単体で「年度-Q」を出力可能です。
9. 指定した列の最終行を取得｜Offset関数とCounta関数
Offset関数とCounta関数の組み合わせることで、最終行までデータを取得することができます。
この事例では6行目までデータがあるので、D2:D6まで取得することができます。
上記のとおり最終行の取得は、Offset関数を基本骨格としてCountaを組み合わせています。
以下でOffset関数をベースとして説明していきます。
Offset関数とCounta関数の組み合わせ
OFFSET関数の基本は以下です。
以下でOffset関数について詳しく説明します。
| 引数 | 必須/省略 | 説明 | 本事例の引数の値 |
|---|---|---|---|
| 参照 | 必須 | オフセットの基準となる参照を指定します。 セルまたは隣接するセル範囲を参照する必要あり |
D2 |
| 行数 | 必須 | 基準の左上隅のセルを上方向または下方向へシフトする距離を行数単位で指定します。 たとえば、行数に 5 を指定すると、オフセット参照の左上隅のセルは、基準の左上隅のセルから 5 行下方向へシフトします。 行数に正の数を指定すると開始位置の下方向へシフトし、負の数を指定すると開始位置の上方向へシフトします。 |
0 |
| 列数 | 必須 | 結果の左上隅のセルを左方向または右方向へシフトする距離を列数単位で指定します。 たとえば、列数に 5 を指定すると、オフセット参照の左上隅のセルは、基準の左上隅のセルから 5 列右方向へシフトします。 列数に正の数を指定すると開始位置から右方向へシフトし、負の数を指定すると開始位置から左方向へシフトします。 |
0 |
| 高さ | 省略可能 | オフセット参照の行数を指定します。 高さは正の数である必要があります。 |
COUNTA(D:D)-1 |
| 幅 | 省略可能 | オフセット参照の列数を指定します。 幅は正の数である必要があります。 |
1 |
この事例では、Offset関数は以下のようになります
つまりD2:D6まで取得することができるのです。
高さ「Counta(D:D)-1」の意味
「D列に文字列が入力されているセル数」から-1した数を取得します。
-1している理由はD1がヘッダー情報なので、それを除外するためです。
D列のデータ増減すると、データ範囲を自動変更
上記のように7行目から9行目までデータを追加すると、Offset関数によって自動でデータ範囲が変更されます。
これで関数のメンテナンスなしのまま、データ追加作業のみで、列のデータ範囲を自動更新してくれます。
10. シート全体のデータを取得｜Offset関数とCounta関数
Offset関数とCounta関数の組み合わせることで、シート全体のデータを別シートへ取得できます。
この事例では2行目~9行目までのデータを別シートへ取得していきます。
上記のとおり最終行の取得は、Offset関数を基本骨格としてCountaを組み合わせています。
以下でOffset関数をベースとして説明していきます。
Offset関数とCounta関数の組み合わせ
OFFSET関数の基本は以下です。
以下でOffset関数について詳しく説明します。
| 引数 | 必須/省略 | 説明 | 本事例の引数の値 |
|---|---|---|---|
| 参照 | 必須 | Offset関数の基準となるセル参照を指定します。 セルまたは隣接するセル範囲を参照する |
Sheet1!A2 |
| 行数 | 必須 | 基準の左上隅のセルを上方向または下方向へシフトする距離を行数単位で指定します。 | 0 |
| 列数 | 必須 | 結果の左上隅のセルを左方向または右方向へシフトする距離を列数単位で指定します。 | 0 |
| 高さ | 省略可能 | オフセット参照の行数を指定します。 高さは正の数である必要があります。 |
COUNTA(Sheet1!A:A)-1 |
| 幅 | 省略可能 | オフセット参照の列数を指定します。 幅は正の数である必要があります。 |
COUNTA(Sheet1!1:1) |
この事例では、Offset関数は以下のようになります
つまり「=OFFSET(Sheet1!A2,0,0,8,4)」となります。
高さ「COUNTA(Sheet1!A:A)-1」の意味
「Sheet1のA列に文字列が入力されているセル数」から-1した数を取得します。
Sheet1のA列はA1~A9までデータが存在しているため、「COUNTA(Sheet1!A:A)」=9となる。
これに-1しているため、高さ「COUNTA(Sheet1!A:A)-1」=8となります。
なお-1の理由はA1がヘッダー情報なので、それを除外するためです。
幅「COUNTA(Sheet1!1:1)」の意味
「Sheet1の1行目に文字列が入力されているセル数」を取得します。
Sheet1の1行目はA1~D1までデータが存在しているため、「COUNTA(Sheet1!1:1)」=4となります。
データ増減すると、データ範囲を自動変更
上記のようにデータを追加すると、Offset関数によって自動でデータ範囲が変更されます。
これで関数のメンテナンスなしのまま、データ追加作業のみで、取得するデータ範囲を自動更新してくれます。
11. エクセル関数でウェブページのタイトルを自動取得｜PageTitle関数(自作)
ExcelでウェブURLからページタイトルを取得したいときがあります。
実はウェブURLからページ名を取得するExcel関数は標準では実装されていませんが、Excelの機能を上手く活用すれば、Excel関数を自作することができます。
– 引数1:セルの指定
ここではVBAプログラムをFunctionプロシージャとして、PageTitle関数を作成し、ウェブリンクからウェブページのタイトルを取得を試みます。
引数1で指定したセルのウェブURLの情報をもとに処理を行い、ウェブページのタイトルを出力します。
詳しい解説は以下のページで行っています。
12. エクセル関数で自動翻訳｜GoogleTranslate関数(自作)
上記の関数を入力すると、翻訳結果が以下のように出力されます。
このようなエクセル関数をつかって、翻訳作業を効率化することができます。
通常のエクセルでは使えない
これはVBAを活用したオリジナル関数です。
エクセルVBAのFunctionプロシージャを作って、エクセル関数からVBAを呼び出すことで使えるようにしています。
したがって通常のエクセルには実装されておりません。
つまり通常のエクセルで「=GoogleTranslate(A2,”en”,”ja”)」と入力しても翻訳は実行されません。
GoogleTranslate関数を実装する方法
– 引数1:A2 セルの指定
– 引数2:”en” 現在の言語(enは英語)
– 引数3:”ja” 翻訳したい言語(jaは日本語)
上記の関数を実装する方法は以下のページで説明をしています。
上記のページでは、GoogleTranslate関数が使えるようになるエクセルファイルをダウンロードすることもできます。
実装するのが面倒であったり、不安がある方は、ファイルをダウンロードしてそのまま活用することも可能です。
実務での応用事例
ここからは実務で、具体的にエクセル関数を応用していく方法を紹介します。
なおエクセル関数だけではなく、条件付き書式などのエクセル機能も組み合わせています。
1. ガントチャート
エクセル関数を使ったガントチャートの作り方を解説しています。
条件付き書式を上手に活用して、入力した日付に対応して色付けしたり、休日を灰色表示したりする方法を詳しく紹介しています。
2. グラフと売上表
エクセル関数を使った売上表とグラフを自動作成する方法を解説しています。
sumifs関数を活用して、表のデータが自動集計されグラフが自動更新されるように設定する方法を詳しく紹介しています。
3. タスク進捗管理表
エクセル関数を使ったタスク進捗管理表の作り方を解説しています。
進捗のステータスを変更することで、色が変えたり、プロジェクト全体の進捗を表にしたりする方法を詳しく紹介しています。
4. 在庫管理表
エクセル関数を使った在庫管理表の作り方を解説しています。
入出庫数を入力することで、在庫状況を色で教えてくれるような機能を持たせる方法を詳しく紹介しています。
5. 2段階プルダウンリスト
エクセル関数を使った2段階プルダウンリストの作り方を解説しています。
一段階目は部署で分類し、二段階目は部員を選択できるようにする方法を詳しく紹介しています。
一段階目で選択した部署に応じて、二段階目で選択できる人が変更されます。
6. スコアによってランキング順に並び替えする
エクセル関数を使って、スコアで順位を並びかえる方法を解説しています。
売上による順位付けや、点数によるランク付けをしたいときに使える関数を使い方を紹介しています。
7. 実績管理表
エクセルでチーム実績(営業件数など)の管理を効率化する表を作成しています。
2種類の軸を1つのグラフに表示する方法を紹介します。
8. 請求書など(フォーマットがある資料)の作成効率化
エクセル関数で請求書作成を効率化する方法を紹介しています。
9. シフト表
エクセル関数や条件付き書式を使ってシフト表(当番表)を作成する方法を紹介します。
エクセルマクロVBAでできること
このページではエクセル関数でできることを紹介しました。
しかしエクセルVBAを活用すると、仕事を効率化できる幅を広げることができます。
実際にVBAを活用して効率化してきた作業は以下のページで紹介しています。
興味がある人は以下の記事もご覧ください。
動画でも解説しています。
エクセルマクロVBAで出来ることを15の事例で紹介｜日常業務をラクにするヒントを見つけよう！
(音声が小さいので、ボリュームを上げてご覧いただければと思います)
VBAの勉強方法
私はプログラミング初心者からVBAを勉強を始めて少しずつレベルアップしていきました。
少しずつレベルアップしながら、難しい内容に挑戦していくと効率的に学ぶことができます。
上記のリンクでは、VBA勉強に役立つ内容を紹介しています。
興味がある人はご覧ください。