メインコンテンツへスキップ
見出し画像

Excel関数はこの4つから覚えればいい|SUMIF・COUNTIF・IF・XLOOKUPを仕事の例で使えるようになる入門

毎月の集計や転記に時間がかかっているなら、まず「何をくり返しているか」を書き出してみましょう。

Excel関数は、複雑な計算をするためだけの道具ではありません。担当者別に売上を足す、未処理の件数を数える、商品コードから単価を呼び出す——こうした毎回同じ作業を、決まったルールで処理してくれます。最初は4つの関数だけで十分です。

この記事では、SUMIF・COUNTIF・IF・XLOOKUPを、実務で使う流れに沿って解説します。

有料部分では、式を書くと自動で正解かどうかがわかるExcel関数 練習シート(Googleスプレッドシート)と、スマホで式を組み立てながら覚えられるスマホアプリ「関数ドリル」をお渡しします。


第1章|関数を覚えるより、作業を減らす【無料】

効率化する作業を選ぶ3つの基準

  1. くり返す回数が多い:毎日10回する作業は、1回の短縮が小さくても1年では大きな差になります

  2. 入力ミスが起こりやすい:数字を手で写す作業は、まちがいが見つけにくいので優先候補です

  3. 元の情報が表になっている:紙の資料や自由なメモから読み取る作業は、関数を入れただけでは解決しません

たとえば毎週金曜日に担当者別の売上を電卓で合計しているなら、売上の明細の形がそろっていればSUMIFに置きかえやすい作業です。一方、日報の文章から顧客の気持ちを読み取る作業は、関数の得意分野ではありません。自動にできるところと、人が確認すべきところを分けることが大切です。

最初に測っておく数字

改善の前に、「集計に何分かかっているか」「何件のデータを扱うか」「どんなミスが多いか」をメモしておきます。関数を入れたあとに同じ条件で比べれば、効果を感覚ではなく数字で判断できます。

ただし、作業時間が短くなっても集計がまちがっていれば改善とはいえません。検算と入力のルールも一緒に作ることが前提です。


第2章|関数が使いやすい表に整える【無料】

1行に1件

1つの注文に商品が3種類あるなら、商品ごとに1行ずつ記録します。注文番号を共通にしておけば、同じ注文だとわかります。「ノート・ペン・ファイル」と1つのセルに並べると、単価の参照も数量の計算もできなくなります。

見出しと値のルール

  • 見出しは1行目に置き、途中で名前を変えない(「担当」「担当者」「営業担当」を混ぜない)

  • 途中に空白の行や小計の行を入れない

  • 金額は数字だけを入れ、「円」は表示形式でつける(「1,200円」という文字は計算に使えないことがあります)

マスターと取引の記録を分ける

商品コード・商品名・単価のように何度も参照する情報は「商品マスター」にまとめます。日付・担当者・数量・状態など、毎回変わる情報は「売上入力」に記録します。こうすると、商品名や単価を毎回打つ必要がなくなります。

空欄と0の意味を決める

数量が空欄なら「まだ入力していない」、0なら「注文数が0」と区別します。入力途中の行が0円の注文として集計に混ざらないよう、どの列が必須か、空欄を許すかを先に決めておきましょう。


第3章|SUMIFとCOUNTIFの基本【無料】

SUMIF:条件に合う行だけ合計する

=SUMIF(条件を調べる範囲, 条件, 合計する範囲)

担当者がB列、金額がF列なら、田中さんの売上の合計は次のとおりです。

=SUMIF(B2:B101,"田中",F2:F101)

条件を式に直接書かず、集計表のA列に担当者名を入れて =SUMIF(売上入力!$B$2:$B$101,A4,売上入力!$F$2:$F$101) とすれば、名前を変えるだけで集計を切り替えられます。

条件を調べる範囲と合計する範囲は、はじめの行と終わりの行をそろえるのが基本です。B2:B101 に対して F3:F102 を指定すると、1行ずれた値を足してしまいます。式を下にコピーするときは、元データの範囲に $ をつけて固定し、条件のセルだけが動くようにします。

COUNTIF:条件に合う件数を数える

=COUNTIF(範囲, 条件)

状態がG列なら、未処理の件数は =COUNTIF(G2:G101,"未処理") です。条件が2つ以上あるときは、COUNTIFS(担当者が田中で、かつ未処理、など)を使います。

よくある落とし穴

「田中」と「田中さん」は別の文字です。前後に空白が入っていても一致しません。集計が少ないと感じたら、まず元のデータをフィルターで絞り込んで、実際の書き方を確認しましょう。担当者名はリストから選ぶようにしておくと、表記ゆれを防げます。

画像

第4章|IFで表示を分ける【無料】

=IF(条件, 合うとき, 合わないとき)

数量が0なら「要確認」、それ以外なら「OK」と出す式は =IF(D2=0,"要確認","OK") です。ただし、空欄のセルも0として扱われることがあります。そこで空欄を先に調べます。

=IF(D2="","未入力",IF(D2=0,"要確認","OK"))

式を作る前に、日本語で順番を書く

「数量が空欄なら未入力」→「0なら要確認」→「それ以外はOK」。この順番を先に決めておけば、IFが重なっても意味を追いやすくなります。判定が5段階以上になるなら、判定の基準を別の表にして参照するほうが、あとから直しやすくなります。

IFが確認するのは、指定した条件だけ

「OK」と表示されても、その取引が業務として正しいとは限りません。数量が正しくても、商品コードがまちがっているかもしれません。何を検査していて、何を検査していないかを意識しましょう。

ここから先の有料部分では、次のものをお渡しします。

  • Excel関数 練習シート(Googleスプレッドシート・11問の自動採点つき練習、売上入力→集計→検算の実例)

  • スマホアプリ「関数ドリル」(見本の表で式を組み立てて、どの行が合計されたか色で確認できる)

  • XLOOKUPの使い方、検算の手順、エラーの直し方


 
 
家計簿・シフト・パソコンの困った・転職・車のお金。毎日の「めんどう」を、コピーするだけで使える表とスマホアプリでラクにします。どの記事も前半は無料。毎日20時ごろ更新中。はじめての方は固定記事「はじめての方へ」からどうぞ。

あなたへのおすすめ