Excel｜関数活用編

毎日、データのコピペや計算チェックに時間を奪われていませんか？ 「関数」と聞くと、「難しそう」「数学は苦手」と敬遠してしまう方も多いかもしれません。しかし、Excel関数は数学ではなく、仕事を楽にするための『魔法の道具』です。

この「関数活用編」では、覚えるだけで作業時間が1/10になる、実務で本当に使えるテクニックだけを厳選しました。脱・手入力で、あなたの時間を賢く使いましょう。

行を削除すると自動更新で連番を作る

名簿や住所録の中にデータに①②③と連番を打って管理することがよくあります。
しかし、100件、500件、1000件の大量のデータを連番を入力しても行を削除すると連番が崩れてしまします。Excelで行を削除すると連番が崩れないフォーマットの作成をします。

住所録に自動で連番を作成する設定

この様な連番の住所録が有ります。

この様な連番が打って有ります。

5行目の島田さんを削除します。

5行目の島田さんは削除されましたが連番が崩れてしましました。

ROW関数を使って連番が崩れない方法

Aの2行目をアクティブセルにして【ROW】関数を入力してキーボードの【TAB】キーを押します。

すると、山田花子さんのNoが2と入力されました。通常では、1と入力したいです。
ROW関数が2行目なので2行目と表示されました。

1行目と表示したい場合は
＝Row()-1と表示させ、キーボードの【Enter】キーを押します。

すると、山田花子さんのNoは1とにゅうりょくされます。

A2のセルの下をダブルクリックします。

連番が入力されました。

又、5行目の島田さんを削除します。

5番の連番が自動的に表示されました。
他の所も削除すると連番が自動更新になります。

本日はエクセルで連番を削除すると自動更新するフォーマットを作成しました。

エクセル納期管理を作成しよう（条件付き書式）・エクセルの重複データを削除する方法も現場の時短に繋がります。ぜひ参考にしてください。

フィルターの抽出データをSUBTOTAL関数

下記の記事を参考にしてください。

ワイルドカードの活用テク（データ管理）

「*」を使用してみよう

この様なExcelの従業員名簿が有ります。
このリストから30代の人数を調べるとき、皆さんはどうされてますか？

ワイルドカードを使うと、とても便利です。

COUNTIF関数の検索値を「”3*”」とすればとすれば良いです。
「*」は任意の文字を意味します。3の後に、何の数値が来てもカウントされます。

実際操作してみます。

=COUNTIF(D2:D25,”3*”)と言う数式を入力します。
キーボードの【Enter】キーを押します。

すると30代の人数が10人居ると言う事が解ります。

このようにワイルドカード「*」は一致する検索値の幅を広げ大変便利になります。

ワイルドカードは関数と組み合わせて使うこともできます。
特にCOUNTIF関数との相性がとてもよく、覚えておくととても重宝します。

住所録で東京都だけを検索したい

住所録で「東京都」の社員の数を調べたいと言う時、東京都以外の文字列がはいっていますので、東京都を調べることが出来ません。そんな時ワイルドカードを使用します。

=COUNTIF(F2:F25,”東京都*”)この様な数式を入力します。

キーボードの【Enter】を押します。

こうする事によって東京都で始まる文字列で検索する事が出います。

東京都人数が10名居る事が解りました。ワイルドカードのアスタリスクの方法をご紹介しました。これを使えば関数の組み合わせによって様々なデータを抽出することが出来ます。

条件に一致する値の合計

Excelには指定条件を満たす数値の合計を取得するDSUM関数が用意されています。ここではDSUM関数の機能と使い方について解説します。

DSUM関数とは

ExcelのDSUM関数は、条件に一致する値の合計を返します。合計する値は、データベースの特定のフィールドから抽出されます。

DSUM関数の構文

＝DSUM（データベース,フィールド,条件）となっています。※見出しの部分を必ず入れます。

データベース：データベースはこの様な、表の事です。選択する際は必ず項目名も含めて選択します。

フィールド：データベースの合計は出したい列の見出しの部分をクリックします。

条件：検索条件が入力された範囲を指定します。検索条件は必ず項目名も含めて選択します。
注意：データーベース上の見出しと、条件の見出しはかならず一致させます。

DSUM関数を使用

この様な数式を入力します。

そうすると、全体の売上が分かります。

※注意：データベース内の列全体に対して検索条件を指定するには、検索条件範囲の列見出しの下に空白行を1行挿入します。

抽出の仕方

この様に、抽出条件を入力すると、一括で売上金額が分かります。

XLOOKUP関数

下記の記事を参考にしてください。

IFS関数

Excelでよく使う関数にIF関数があります。IF関数は、指定した条件によって表示内容を変えることができる関数で、現場で活用されている方は多いと思います。

複数の条件を指定したい場合、IF関数の中にさらに新たなIF関数を入れる事となり、とても数式が複雑になってしまいます。今回は複数の条件を順に調べた結果に応じて異なる値を返すIFS関数の使い方を解説します。

IF関数の引数が複雑

例えば、購入合計金額が300000以上の場合は、「ゴールド」、150000以上の場合は「シルバー」、それ以外は「一般」と表示します。

会員レベルの基準は下記のようになってます。

【=IF(D4>=300000,”ゴールド”,IF(D4>=150000,”シルバー”,”一般”))】という式を入力する必要があります。IF関数を何回も使いとても複雑です。

IFS関数

そんなときに便利な関数がIFS関数です。IF関数に比べて、引数の指定がシンプルにできるので、書きやすく見やすいです。今回は、このIFS関数の使用方法について解説します。尚、IFS関数は、Excel 2019またはMicrosoft 365版で使用する事が出来ます。

先程の条件をIFS関数を使って使用してみます。

IFS関数は、上記ような書式で記述します。

IF関数では、複数の条件を記述するときに、IF関数の中に新たなIF関数を書いていましたが、IFS関数は、条件と表示内容のペアを並べていけばよいのでシンプルかつわかりやすいです。

条件分岐の流れ

IF関数を入れ子にした場合、下記のようにフローチャート（流れ図）で考えると判定の流れが見えるのでわかりやすくなります。試してみましょう。

スピルとは？

スピルは、いわゆる「行と列で表現された表」を作るときに使うと、とても便利です。
ほとんどの場合、複合参照を使わないでも、「行と列で表現された表」が作れるようになります。

通常、単一のセルや値を入れるべき場所に「セル範囲」を指定することで数式が、その「セル範囲」の広さだけ、勝手に展開（＝スピル）されます。

スピル機能の概要

＝セルの範囲を指定します。

この様になります。

この黄色のセル　F3は、動的配列数式といいます。

結果 (緑のセル) をゴーストといいます。

スピル領域 (緑のセル) のゴーストは編集できません。数式を編集するときには、動的配列数式つまり最初に作った親分を修正します。

ゴーストのセルを直接編集してしまうと、動的配列数式の入っているセルに「#SPILL!」というエラーが表示されます。

この3を消したらどうなるでしょうか？

元に戻ります。

スピル機能には絶対参照・相対参照はいらない

この様な表があります。
消費税率を固定（絶対参照）しないで、スピル機能を使ってみます。

この様に数式を入力します。そして【Enter】キーを押します。

この様に一発でマトリックス表が完成します。

FILTER関数

FILTER関数も合わせてお読みください。

SUMIF関数

Excelでのデータ管理において、特定の条件に合致する数値のみを合計したい場面は頻繁に発生します。例えば、売上管理表から特定商品の売上高のみを算出したり、家計簿から特定の費目の合計を求めたりする場合などが挙げられます。

本記事では、こうした条件付き集計を可能にするSUMIF（サミフ）関数について、基本的な仕様から実務での活用方法までを、図解を用いて体系的に解説します。

SUMIF関数とは？

SUMIF関数は、「指定した範囲の中で、条件に合ったセルの数値だけを合計する」関数です。

• SUM（合計する）
• IF（もし～だったら）

この2つが組み合わさった名前の通り、「もし〇〇だったら合計する」という処理を行ってくれます。

SUMIF関数の基本構文

SUMIF関数を使うための「式」の形（構文）は以下の通りです。

実践！SUMIF関数を使ってみよう

この様な表があり、【I3】に集計表を出す際、【愛媛支店】の売上の合計の式は上記になります。

引数の設定を理解する

この数式が何をしているか、3つの要素に分解して解説します。

範囲 (C:C)

まず、「住所」が書かれている列全体を探す場所に指定します。
「この中から探してね」という範囲です。

検索条件 (H3)探したいキーワードを指定します。ここでは「愛媛支店」と書かれているセル E2 を指定しています。

直接 ”愛媛支店” と文字で入力してもOKですが、セル参照（H3）にしておくと、H3を「東京本社」に変えただけで計算結果も変わるので便利です。

合計範囲 (F:F)
条件に合ったとき、「実際に足し算をする数値」が入っている列を指定します。
ここでは「売上金額」が入っているF列の範囲を指定します。

応用テクニック

数値の条件で合計する（勤続年数が10年以下）

支店名だけでなく、金額自体を条件にすることもできます。
例えば、「勤続年数が10年以下のデータだけ合計したい」場合：

ワイルドカードを使う（「〜を含む」）

「支店」という文字が含まれる商品だけを合計したい場合：*（アスタリスク）を使います。

• “支店*”から始まるもの
• “*支店”で終わるもの
• “支店”を含むもの

SUMIF関数は、大量のデータから必要な情報だけを抜き出して集計するのに非常に便利なツールです。

• 範囲：どこから探す？
• 検索条件：何を探す？
• 合計範囲：何を足す？

この3つのリズムを覚えれば、もう迷うことはありません。

SUMIFS関数を使ったマトリックス集計にも「スピル」が便利

日付ごとに金額を集計します。
従来の数式だと、複合参照を使わないといけないのですが、スピルを使うと、直感的な数式で計算できます。

【UNIQUE】関数で、経費一覧表の【費目】を選択します。キーボードの【Enter】キーを押します。

すると、この様に、重複しない、費目が取り出されます。

G1セルに【=SUMIFS(D4:D39,C4:C39,F3#,B4:B39,G2:U2)】と関数を入力します。

【＃】については下記の記事を参考にしてください。

すると、この様に、マトリックス表が完成されます。

まとめ