PHPバックエンドエンジニアのためのExcel関数入門
概要
PHPバックエンドエンジニアの皆様にとって、データベースやAPI、各種ファイルフォーマットを扱うことは日常茶飯事でしょう。しかし、意外と見落とされがちなのが「Excel関数」のスキルです。一見するとプログラミングとは異なる領域に思えますが、Excel関数はデータ処理、分析、レポーティング、そして何よりも他部署との円滑な連携において、強力なツールとなり得ます。
バックエンドエンジニアがExcel関数を学ぶ意義は多岐にわたります。例えば、システムから出力されたCSVデータの簡易的な整形や検証、データベースに投入する前の前処理、あるいはビジネスサイドが作成したレポートのロジック理解、さらには自身でアドホックなデータ分析を行う際など、様々な場面でその知識が役立ちます。Excel関数は、SQLのクエリやPHPの配列操作、条件分岐といったプログラミング的思考と共通する部分が多く、学習コストも決して高くありません。本記事では、PHPバックエンドエンジニアが実務で役立つExcel関数の基礎と応用について、詳細に解説していきます。
詳細解説
Excel関数は、特定の処理を行うための組み込みコマンドであり、引数と呼ばれる値を受け取り、結果を返します。複数の関数を組み合わせることで、複雑なデータ処理も実現可能です。ここでは、エンジニアが特に知っておくべき主要な関数グループとその活用例を挙げます。
1. 論理関数 (IF, AND, OR)
条件に基づいて異なる処理を行いたい場合に利用します。プログラミングにおけるif文、&&演算子、||演算子に相当します。
- IF関数: 指定した条件が真の場合と偽の場合で異なる値を返します。
=IF(条件式, 真の場合の値, 偽の場合の値)
例:=IF(A2>100, "高", "低") - AND関数: 複数の条件がすべて真の場合に真を返します。
=AND(条件1, 条件2, ...)
例:=IF(AND(B2="完了", C2>=DATE(2023,1,1)), "対象", "非対象") - OR関数: 複数の条件のうちいずれかが真の場合に真を返します。
=OR(条件1, 条件2, ...)
例:=IF(OR(D2="エラー", E2="警告"), "要確認", "正常")
エンジニア視点: データのバリデーション、ステータス判定、フラグ付けなど、条件分岐を用いたデータ整形に頻繁に利用します。SQLのWHERE句やCASE文、PHPのif/else if/else文と概念が一致するため、理解しやすいでしょう。
2. 検索/参照関数 (VLOOKUP, XLOOKUP, INDEX, MATCH)
別のシートやテーブルから特定の条件に合致するデータを検索し、取得する際に使用します。データベースにおけるJOIN操作に似ています。
- VLOOKUP関数: 垂直方向にデータを検索し、指定した列の値を返します。
=VLOOKUP(検索値, 範囲, 列番号, 検索方法)
例:=VLOOKUP(A2, 商品マスタ!A:C, 2, FALSE)(商品IDから商品名を取得) - XLOOKUP関数: VLOOKUPの後継関数で、より柔軟な検索が可能です。左右どちらの方向にも検索でき、複数列の返却も容易です。
=XLOOKUP(検索値, 検索範囲, 返却範囲, [見つからない場合], [一致モード], [検索モード])
例:=XLOOKUP(A2, 商品マスタ!A:A, 商品マスタ!B:B, "該当なし") - INDEX関数とMATCH関数の組み合わせ: VLOOKUPよりも柔軟で高速な検索が可能です。VLOOKUPではできない左方向の検索も行えます。
=INDEX(返却したい範囲, MATCH(検索値, 検索したい範囲, 検索方法))
例:=INDEX(商品マスタ!B:B, MATCH(A2, 商品マスタ!A:A, 0))
エンジニア視点: マスタデータとの結合、外部キーによる関連データ取得、IDに基づく情報の補完など、データベースのJOIN操作や連想配列からの値取得と共通の目的で利用します。特にCSVデータでマスタとトランザクションを結合する際に重宝します。
3. 文字列操作関数 (LEFT, RIGHT, MID, LEN, FIND, REPLACE, CONCAT)
文字列の抽出、結合、置換など、データの整形に利用します。PHPの`substr()`, `strpos()`, `str_replace()`, `implode()`などに対応します。
- LEFT関数: 文字列の左端から指定した文字数を抽出します。
=LEFT(文字列, 文字数) - RIGHT関数: 文字列の右端から指定した文字数を抽出します。
=RIGHT(文字列, 文字数) - MID関数: 文字列の指定した位置から指定した文字数を抽出します。
=MID(文字列, 開始位置, 文字数) - LEN関数: 文字列の長さを返します。
=LEN(文字列) - FIND関数: 文字列内で特定の文字が最初に現れる位置を返します。
=FIND(検索文字列, 対象文字列, [開始位置]) - REPLACE関数: 文字列の一部を別の文字列に置換します。
=REPLACE(対象文字列, 開始位置, 文字数, 置換文字列) - TEXTJOIN関数 / CONCAT関数: 複数の文字列を結合します。
=TEXTJOIN(区切り記号, 空白セルを無視, 文字列1, 文字列2, ...)
=CONCAT(文字列1, 文字列2, ...)(旧VERではCONCATENATE)
エンジニア視点: CSVデータのパース、ログファイルの整形、URLの分解、特定のフォーマットへの変換など、データの前処理や後処理で非常に役立ちます。正規表現の簡易版として利用することも可能です。
4. 集計関数 (SUM, AVERAGE, COUNT, SUMIFS, COUNTIFS)
数値データの合計、平均、個数などを計算します。SQLの`SUM()`, `AVG()`, `COUNT()`, `GROUP BY`句に似ています。
- SUM関数: 数値の合計を計算します。
=SUM(範囲) - AVERAGE関数: 数値の平均を計算します。
=AVERAGE(範囲) - COUNT関数: 数値が含まれるセルの数を数えます。
=COUNT(範囲) - COUNTA関数: 空白ではないセルの数を数えます。
=COUNTA(範囲) - SUMIFS関数: 複数の条件に合致する数値の合計を計算します。
=SUMIFS(合計範囲, 条件範囲1, 条件1, [条件範囲2, 条件2], ...)
例:=SUMIFS(C:C, A:A, "A支店", B:B, "2023/01/*") - COUNTIFS関数: 複数の条件に合致するセルの数を数えます。
=COUNTIFS(条件範囲1, 条件1, [条件範囲2, 条件2], ...)
例:=COUNTIFS(D:D, "完了", E:E, ">="&DATE(2023,1,1))
エンジニア視点: レポート作成、KPI集計、データセットの概要把握など、条件に基づいた柔軟な集計処理を行う際に不可欠です。SQLのGROUP BYとHAVING句を組み合わせたような複雑な集計も、SUMIFSやCOUNTIFSを組み合わせることで実現できます。
5. 日付/時刻関数 (TODAY, NOW, YEAR, MONTH, DAY, DATEDIF)
日付や時刻の計算、抽出、フォーマット変換に利用します。PHPの`DateTime`オブジェクトや`date()`関数に対応します。
- TODAY関数: 今日の日付を返します。
=TODAY() - NOW関数: 現在の日付と時刻を返します。
=NOW() - YEAR, MONTH, DAY関数: 日付から年、月、日を抽出します。
=YEAR(日付) - DATEDIF関数: 2つの日付間の期間を計算します（非公開関数ですが非常に便利）。
=DATEDIF(開始日, 終了日, 単位)
単位: “Y” (年), “M” (月), “D” (日), “YM” (年を除いた月数), “YD” (年を除いた日数), “MD” (月を除いた日数)
エンジニア視点: ログデータの期間分析、契約期間の計算、レポートの日付フィルタリングなど、時系列データの処理に役立ちます。日付フォーマットの揺れを吸収したり、特定の期間に絞り込んだりする際に重宝します。
サンプルコード
ここでは、上記で解説した関数を組み合わせた具体的な使用例をいくつか示します。
例1: 顧客ステータスの自動判定と割引額計算
「購入回数が5回以上かつ総購入額が10万円以上の顧客」を「VIP顧客」とし、VIP顧客には購入額の10%を割引、それ以外には5%を割引するケース。
// C列: 購入回数、D列: 総購入額
// E列: 顧客ステータス判定
=IF(AND(C2>=5, D2>=100000), "VIP顧客", "一般顧客")
// F列: 割引額計算
// E列の判定結果に基づいて割引率を適用
=IF(E2="VIP顧客", D2*0.1, D2*0.05)
例2: 商品IDから商品名と単価を取得し、売上合計を計算
トランザクションデータ（商品IDと数量）に、商品マスタ（商品ID、商品名、単価）を結合し、各行の売上と全体の売上合計を計算する。
// トランザクションシート: A列: 商品ID, B列: 数量
// 商品マスタシート: Sheet2!A列: 商品ID, Sheet2!B列: 商品名, Sheet2!C列: 単価
// C列: 商品名 (XLOOKUPを使用)
=XLOOKUP(A2, Sheet2!A:A, Sheet2!B:B, "不明")
