KE1

Excelを活用する際に、最初に覚えておくべき集計するための関数を７個紹介していきます。

1. SUM：数値の合計を求める

指定した範囲内の数値の合計を求める場合は、SUM関数を使います。

=SUM(B2:C7)

指定した範囲内に文字列を含んでいたとしても、集計することができます。
離れている範囲を指定する場合は、以下のようにカンマ区切りで指定します。

=SUM(B2:C3,B6:C7,D9)

2. COUNT：数値が入力されているセルの数を求める

指定した範囲内の数値が入力されているセルの数を求める場合は、COUNT関数を使います。

=COUNT(B2:C7)

離れている範囲を指定する場合は、以下のようにカンマ区切りで指定します。

=COUNT(B2:C3,B6:C7,D9)

3. COUNTA：何かしらの値が入力されているセルの数を求める

指定した範囲内の何かしらの値が入力されているセルの数を求める場合は、COUNTA関数を使います。

=COUNTA(B2:C7)

離れている範囲を指定する場合は、以下のようにカンマ区切りで指定します。

=COUNTA(B2:C3,B6:C7,D9)

4. SUMIF：特定の条件を満たした数値の合計を求める

指定した範囲内で特定の条件を満たしている数値の合計を求める場合は、SUMIF関数を使います。

=SUMIF(B2:B7,"A",C2:C7)
// B2:B7：条件を確認する項目
// "A"：上記で指定した項目で集計する対象
// C2:C7：合計する項目

条件には、以下のような条件を指定することもできます。

・"A"：Aである
・"*A"：Aで終わる
・"A*"：Aで始まる
・"*A*"：Aを含む
・"<>A"：Aでない
・">5"：5より大きい
・">=5"：5以上
・"<5"：5より小さい
・"<=5"：5以下

5. COUNTIF：特定の条件を満たしたセルの数を求める

指定した範囲内で特定の条件を満たしているセルの数を求める場合は、COUNTIF関数を使います。

=COUNTIF(B2:C7,"A")
// B2:C7：条件を確認する範囲
// "A"：上記で指定した範囲で数える対象

SUMIF関数と同様に、色んな条件（「含む」や「より大きい」など）を指定することができます。

6. SUMIFS：複数の条件を満たした数値の合計を求める

指定した範囲内で複数の条件を満たしている数値の合計を求める場合は、SUMIFS関数を使います。

=SUMIFS(D2:D7,B2:B7,"A",C2:C7,"B")
// D2:D7：合計する項目
// B2:B7：1つ目の条件を確認する項目
// "A"：1つ目に指定した項目で集計する対象
// C2:C7：2つ目の条件を確認する項目
// "B"：2つ目に指定した項目で集計する対象

SUMIF関数と同様に、色んな条件（「含む」や「より大きい」など）を指定することができます。

以下のように、３つ以上の条件を指定することもできます。

=SUMIFS(E2:E7,B2:B7,"A",C2:C7,"B",D2:D7,">10")

7. COUNTIFS：複数の条件を満たしたデータの数を求める

指定した範囲内で複数の条件を満たしているデータの数を求める場合は、COUNTIFS関数を使います。

=COUNTIFS(B2:B7,"A",C2:C7,"B")
// B2:B7：1つ目の条件を確認する項目
// "A"：1つ目に指定した項目で数える対象
// C2:C7：2つ目の条件を確認する項目
// "B"：2つ目に指定した項目で数える対象

SUMIF関数と同様に、色んな条件（「含む」や「より大きい」など）を指定することができます。

以下のように、３つ以上の条件を指定することもできます。

=COUNTIFS(B2:B7,"A",C2:C7,"B",D2:D7,">10")

まとめ

以上の７個の関数を知っているだけで、集計の幅が広がるため、ぜひ覚えるとよいです。

海外の企業から送られてくる資料などでは、以下のように英語で書かれていることがあります。

英語に慣れていない場合は、内容を読むのに苦戦します。
今回は、そんな時に使える翻訳する関数について解説していきます。

TRANSLATE関数

翻訳する関数、それはTRANSLATE関数です。
以下のように、対象の文字列、翻訳前と翻訳後の言語を指定します。

=TRANSLATE(B4,"en","ja")
// B4：対象の文字列
// "en"：翻訳前の言語（英語）
// "ja"：翻訳後の言語（日本語）

言語はリストから選択することができます。

上記の数式を入力するだけで、以下のように翻訳された結果が表示されます。

後は、他のセルにもコピーするだけで、全体を翻訳することができます。

※「2024/7/1」に対応した値を表示するセルには、表示形式を設定していないため、シリアル値（45474）が表示されています。

ただ直訳になるため、「Status」が「地位」のように、本来とは異なる翻訳がされることもあります。

他の言語にも翻訳することができるので、海外の企業に資料を共有する際にも活用できます。

=TRANSLATE(B4,"en","th")
// 英語をタイ語に翻訳

まず前提として、可能ならば１行１データの表に変換した方が良いです。
しかし、いろんな理由（入力しやすさ、見やすさなど）で、以下のような複数行１データの表で管理することがあります。

このような複数行１データの表から、特定の項目で検索して値を抽出する方法について、以下の表の「会員番号」で検索し、「氏名」を抽出する数式を例に解説していきます。

1. 法則性を見つける

理想の値を抽出するためには、まずは法則性を見つける必要があります。

以下の表の場合は、次のような法則性があります。

・「会員番号」の下のセルが「氏名」

そのため、該当する「会員番号」の行番号が分かれば、その１つ下の行番号の、同じ列のセルの値を抽出すれば良いです。

2. 対象の行番号を取得する

特定の値が入力されているセルの行番号を取得するには、MATCH関数が便利です。
MATCH関数を使った以下のような数式で、行番号を取得することができます。

=MATCH(F3,C:C,0)
// F3：検索値
// C:C：検索範囲
// 0：検索方法（完全一致）
// 指定した「検索範囲」の先頭から指定した「検索値」と完全一致する値の位置情報を返す

上記では、「C:C」と列全体を指定していますが、表の先頭からの相対的な行番号でも問題ないです。
表の先頭からの相対的な行番号を取得する場合、以下のような数式になります。

=MATCH(F3,C4:C21,0)

3. 指定した行番号のセルの値を取得する

次は、先ほど取得した行番号に１を加えた行番号の、同じ列のセルの値を抽出します。

指定した範囲の相対的な行番号のセルの値を抽出するには、INDEX関数が便利です。
先ほど抽出した行番号に１を加えた行番号に位置するセルの値を抽出する場合、以下のような数式になります。

=INDEX(C4:C21,G3+1)
// C4:C21：抽出対象となる範囲
// G3+1：指定した範囲から抽出する値の位置情報
// 指定した「抽出対象となる範囲」の先頭から指定した「位置情報」の位置にある値を返す

ちなみに、考え方によっては、MATCH関数で取得した行番号に１を加えなくても、相対的な範囲を１つ下に移動することでも抽出することができます。

=INDEX(C5:C21,G3)

ただ、前者の以下の数式の方が直感的に分かりやすく感じます。（好みに合わせてください）

=INDEX(C4:C21,G3+1)

最後に、MATCH関数の数式とINDEX関数の数式を、以下のように組み合わせることで完成です。

=INDEX(C4:C21,MATCH(F3,C4:C21,0)+1)

4. まとめ

今後の保守を考えると、データを管理する表は１行１データに揃えた方がよいです。
しかし、一時的な表や見やすさを重視した表で、集計する必要のない表の場合は、今回のような複数行１データの表で管理しても問題になることは少ないです。

そのような表形式を保ったまま、特定の値を検索&抽出する際は、今回のように法則性を見つけることが大切になります。
※法則性がない場合は、数式での抽出は困難です。

ぜひ試してみてください。

以下のように、棒グラフの一番大きい棒のみを自動で色付けする方法について紹介していきます。

1. 一番大きい棒のみを色付けする方法とは

残念ながら、標準の機能には「特定の棒のみを自動で色付けする機能」は用意されていません。
そのため、少し工夫が必要です。

そこで今回は、棒グラフを積み上げ棒グラフにし、片方の棒グラフの色を変更して対応していきます。
要するに、積み上げ棒グラフの片方の参照データを、最大値が「０」の表にし、もう片方はその逆で、最大値以外が「０」の表にします。
具体的には、以下のイメージです。

そのようにして、２つの表を積み上げることで、常に片方のグラフのみを表示することができます。

2. 参照元の表を用意

まずは、最大値が「０」の表と、最大値以外が「０」の表を用意します。
これらは数式で簡単に用意することができます。

具体的には、最大値はMAX関数で取得することができるので、IF関数で最大値かどうかで抽出値を変更するという以下のような数式になります。

▼最大値が「０」の表▼

=IF(MAX($C$3:$C$6)<>C3,C3,0)
// MAX($C$3:$C$6)：最大値の取得（表の範囲は固定）
// <>C3：対象行の値と等しくない場合

▼最大値以外が「０」の表▼

=IF(MAX($C$3:$C$6)=C3,C3,0)
// MAX($C$3:$C$6)：最大値の取得（表の範囲は固定）
// =C3：対象行の値と等しい場合

それぞれの数式を先頭に入力したら、後は末尾までコピーすることで、表の完成です。

3. グラフの用意

次に、グラフを用意します。
表の中を選択し、[挿入]タブから[積み上げ縦棒]を選択します。

必要に応じて、グラフのサイズなどを修正し、グラフのデータ範囲を先ほど作成した表のみに変更することで、グラフの完成です。

4. 補足

標準設定のままですと、作成した表を非表示にするとグラフのデータが消えてしまいます。

作成した表を非表示にしたい場合は、グラフを選択して、[グラフのデザイン]タブから[データの選択]を選択します。

表示された画面で[非表示および空白のセル]を選択して、次に表示された画面で[非表示の行と列のデータを表示する]にチェックし、確定します。

以上の設定を行うことで、作成した表を非表示にしたとしても、グラフのデータは表示されたままになります。

以下の表では、セルB2に指定した人数を超えて、セルB5以降の範囲に氏名が入力できないようになっています。

今回は、上記のような指定したセル数までしか値を入力できないようにする仕組みの作り方について解説していきます。

※こちらで作成したファイルは、記事の最後にて配布しています。

1. データの入力規則に設定する条件式

特定の条件を満たしている状態でないと入力することができないセルを用意するには、「データの入力規則」を活用します。

今回は、以下のセルB2に入力されている人数分のセルだけ、セルB5以降の範囲に、値（氏名）の入力を許可するという設定になります。

これを条件式で表すと、以下のような式になります。

=COUNTA($B$5:$B$1000)<=$B$2
// COUNTA(…)：セルB5以降（仮で1000行目まで）の空でないセルの数を取得
// <=$B$2：COUNTAで取得した数がセルB2に入力されている数値以下である

上記の条件式を満たしているときだけ、セルB5以降への値の入力を許可するといった設定を行います。
セルB5以降だと範囲が広すぎるため、今回は、仮でセルB5からセルB1000までに設定します。

2. データの入力規則の設定

まずは、入力を制限するセルB5からセルB1000を選択します。
広範囲を選択するときは「名前ボックス」を活用するとよいです。
以下では、「名前ボックス」に「B5:B1000」と入力してEnterで確定することで、対象の範囲を選択しています。

次に、[データ]タブから[データの入力規則]を選択します。

データの入力規則の設定画面が表示されたら、[入力値の種類]を[ユーザー設定]にし、先ほどの条件式を入力します。

=COUNTA($B$5:$B$1000)<=$B$2

次に、[エラーメッセージ]タブを選択し、以下のように設定します。

ここでは、先ほど設定した条件を満たさなかったときに表示するメッセージを設定しています。
上記の設定で確定することで完成です。

3. 完成

以上の手順を行うことで、セルB2に指定した人数までしか、セルB5以降に値（氏名）を入力することができなくなります。

▼サンプルファイル▼

以下のように、「指定した表」から「指定した項目」のみを『XLOOKUP関数』を用いて抽出する方法について解説していきます。

※こちらで実現したファイルは、記事の最後にて配布しています。

抽出方法

まずは、抽出先のシートに、抽出対象の項目名を入力します。
以下のシートでは、項目名を簡単に変更できるようにドロップダウンリストにしています。

次に、先頭の項目名の下のセルに以下の数式を入力します。

=XLOOKUP(A1,顧客管理!$A$1:$I$1,顧客管理!$A$2:$I$21)
// A1：抽出対象の項目名のセル
// 顧客管理!$A$1:$I$1：抽出対象の表の見出しの範囲（絶対参照）
// 顧客管理!$A$2:$I$21：抽出対象の表のデータの範囲（絶対参照）

XLOOKUP関数では、縦方向の検索だけではなく、横方向の検索にも対応しています。
そのため、上記の数式を入力するだけで、該当する項目のデータを全て抽出することができます。

検索値のセルの参照のみを相対参照（「$」がない参照）にしているため、セルA2に入力した数式を横方向に必要な列数分コピーするだけで、複数項目でも瞬時に抽出することができます。

まとめ

XLOOKUP関数では、指定した検索範囲に合わせて、縦方向だけではなく横方向にも検索することができます。
また、その検索方向に合わせて、抽出範囲（戻り範囲）から縦方向や横方向の複数の値を一括で抽出することができます。

▼サンプルファイル▼

以下の表は、各クラスの人数分の氏名を登録する用の表です。

この表をもとに、指定した人数以外のセルを自動でグレーにする方法について解説していきます。

※こちらで実現したファイルは、記事の最後にて配布しています。

1. 設定手順

まずは、氏名を入力する範囲（色付けする可能性のある範囲）を選択します。

次に、[ホーム]タブの中の[条件付き書式]から[新しいルール]を選択します。

条件付き書式の設定画面にて、[ルールの種類]を[数式を使用して、書式設定するセルを決定]にし、以下の数式を入力します。

=ROW(C4)-3>C$3
// C4：選択基準のセル
// ROW(C4)-3：基準のセルが氏名を入力する何人目のセルなのか取得
// >C$3：取得した数値が該当列の人数より大きい場合（行番号は固定）

最後に、[書式]を選択し、[書式設定]の画面から[塗りつぶし]にてグレーを選択します。

以上の設定で確定します。

2. 完成

これで不要なセルを自動でグレーにすることができます。
常に、人数のセルを参照しているため、人数の変更にも対応しています。

▼サンプルファイル▼

3. まとめ

条件付き書式を活用して、入力が不要なセルの色を変えることで、誤った入力を防ぐことができます。
直接色付けするのとは異なり、条件付き書式を活用することで、人数の変更にも対応でき、繰り返し使える資料になります。

以下のような表にデータを登録するときに、勝手に、過去に入力した値が表示されて困っている方いませんか？

今回は、この自動入力を無効にする方法について紹介していきます。

自動入力を無効にする

まずは、[ファイル]タブから[オプション]を選択し、「Excelのオプション」を表示します。

オプション画面から[詳細設定]を開き、[オートコンプリートを使用する]のチェックを外します。

以上の手順で無効にできます。

まとめ

自動入力は便利な場面もありますが、毎回異なるデータを登録する表などでは、邪魔になることが多いです。
そのため、邪魔に感じることが多い方は、ぜひ試してみてください。