「EXCEL関数久しぶり」のときに

2024年11月2日 17:50

システムや業務自動化で最近はEXCELをあまり触らなくなっていませんか。
たまに簡単な作業をしようと思ったときに、ふと、「あれどうやるんだったかな」と記憶があやふやな方もいるかも知れません。

そんなときの記憶が戻るように、EXCELの関数情報をまとめてみました。
ぜひ、ご活用ください。

基本を振り返ったあとは、ExcelのVLOOKUP関数などを使って注文データをまとめて、発注データと売上管理データを作成します。

０．EXCEL関数のまとめ

　=SUM(B2:B100)　　戻り値=SUM←関数（引数,で区切る）
1️⃣　SUMIFS関数（サムイフズ）
　　指定した条件の一致するデータを合計する

=SUMIFS(合計対象範囲,条件範囲１,条件１,条件範囲２,条件２…)

2️⃣　COUNTIFS関数（カウントイフズ）
　　指定した条件に一致するデータの個数（件数等）を数える

=COUNTIFS(合計対象範囲,条件範囲１,条件１,条件範囲２,条件２…)

3️⃣　VLOOKUP関数（ブイルックアップ）　　データ転記等に
　　条件に一致するデータの指定した列にある内容を取り出す

=VLOOKUP(検索値,範囲,列番号,検索の型)

4️⃣　IF関数（イフ）
　　条件に応じて処理を変える関数

=IF(論理式,真の場合,偽の場合)

5️⃣　SUM関数（サム）
　　指定した範囲のセルの数値を合計

＝SUM関数(数値１,数値２…)

○条件処理
　AND関数（複数の条件すべてが満たされているか調べる）
　OR関数（複数の条件でどれか１つでも満たされているか調べる）
○エラー処理
　IFERROR関数（エラー値があるときに返す値を変える）
○日付処理
　DATE関数（指定した日付の日付データを求める）
　DAY関数（日付データから日を取り出す）
　MONTH関数（日付データから月を取り出す）
　YEAR関数（日付データから年を取り出す）
○文字列処理
　LEFT関数（文字列の左端から指定した文字数分だけ文字を取り出す）
　MID関数（文字列の指定した位置から文字数分だけ文字を取り出す）
　RIGHT関数（文字列の右端から指定した文字数分だけ文字を取り出す）
○端数処理
　ROUND関数（指定した桁数に数値を四捨五入する）
　ROUNDDOWN関数（指定した桁数に数値を切り捨てる）
　ROUNDUP関数（指定した桁数に数値を切り上げる）

覚えておくと便利な関数

オートSUM　
合計値を求めたいセルで[ Alt + sifut + = ]　Enterで確定
範囲の設定　
① [ =SUM ]と手入力　[ Tab ] =SUM() まで自動入力される　　　　　　
② [ ↑ ]　範囲選択　始点　　　　　　
③ [ Ctrl + Shift + ↑ ]　セルの範囲全選択　Enterで確定
表全体を選択　
①表内のセルをクリック　　　　　　　
② [ Ctrl + Shift + ↑ ]　表の範囲全選択
離れたセルを同時に選択　
①　範囲(B2~B6)をドラッグ　　　　　　　　　　　　
②　[ Ctrl ]を押しながら範囲(D2~D6)をドラッグ
例：上期と下期の合計を簡単に求める

ショートカット

数列に文字入力　"東京都"　半角ダブルクオーテーションで囲む
文字列の結合　=A1＆B1　市町村住所に県をつける　=”福岡県”＆A1
絶対参照　$B$4　列だけ固定　＄B4　行だけ固定　B$1
A1で [ F4 ] キーを１回　押すと $A$1 ２回→ A$1 3回→ $A1

鉄則

1. データの準備

まず、以下のようなシートを作成します:

《テーブル化のメリット》
・フィールド・レコードを追加すると、テーブル範囲が自動拡張する
・レコードを追加すると、同じフィールドで設定していた書式や数式、入力規則も自動的に引き継ぐ
・他の機能・数式でテーブル範囲を参照しておくと、テーブルの拡張に合わせて参照範囲も自動的に拡張してくれる
・デフォルトでテーブル化した表にフィルターボタンがつく
・表のスタイルや体裁をクリック操作で簡単に設定・変更できる
・表を下にスクロールしても、見出しが固定される

①注文データシート　
※チャットGPTでデモデータ作成　テーブル化　主キー【注文ID】

「テーブルデザイン」タブ内の左端にある「テーブル名」ボックスに、新しいテーブル名を入力

データの内容は架空　**金額に”円”などはつけない　数値、日付、時刻等決まった形式で入力**

②商品マスターシート（通常は販売前に作成済み）

③発注データシート　　以下参照

④売上管理シート　　　以下参照

2. 注文データの集計

発注データの作成

①発注データシートに以下の列を作成します　※テーブル化

商品ID
商品名
発注数量
仕入単価
仕入合計

②商品コードごとの発注数をまとめます

=SUMIF(T注文データ!D:D, A2, T注文データ!F:F)

発注データシート：T発注データシートのD列で、A2と同じ商品IDのF列の個数を合計
テーブル化しておくと、２行目以降も全て関数が反映される

VLOOKUPを使って商品マスターから商品名と価格を取得

=VLOOKUP(A2, 商品マスター!A:D, 2, FALSE)

=VLOOKUP(A2, 商品マスター!A:D, 4, FALSE)

商品マスタのA~D列のうち、A2と一致する商品IDの２列目のデータを入力
発注データシートもテーブル化しておくと、２行目以降も全て関数が反映される

商品IDが無い場合　SUNIFSを使って商品名で合計個数を取得

=SUMIFS(注文241101[個数],注文241101[商品名],$A2)

発注データシートの商品名と同じ商品名の個数を合計　＄A2　A列は固定
（注文241101　は　T注文データシート　と同じです）

3. 売上管理データの作成

ピボットテーブルの作成

注文データのテーブルから設定
挿入タブ　ピポットテーブル

行程に「日付」と「商品名」、金額に「発注個数」を設定

分析タブ→グループ化の選択　「日付」フィールドをクリックして、グループ化を選択して月単位でグループ化

5. グラフの作成

ピボットテーブルを選択し、「挿入」タブから正しいグラフを選択します。
グラフのタイトルや軸ラベルを正しく設定します。

以上の手順で、注文データを元に発注データと売上管理データを作成し、さらに視覚分析も可能になります。これにより、在庫管理や売上傾向の把握が容易になります。

最後まで読んでくださり、ありがとうございます。
少しでもお役に立てることができたでしょうか。

また、様々な視点から記事作成して行きます！