SUMIF関数で先頭の1文字が同じセルの売上を集計する

Excelで顧客番号や商品番号の先頭の1文字が同じセルの合計をしたいという場合の方法です。

SUMIF関数を使用します。

指定された検索条件に一致するセルの値を合計する関数です。

参考SUMIF関数を使用せずに、LEFT関数を使って先頭文字を抜き出し、ピボットテーブルで求めることもできます。

サンプル表の確認

下のような表があります。

商品番号の先頭が［1］の売上合計をセル［E2］に求めます。

同じように先頭番号が［2］の集計をセル［E3］に、先頭番号が［3］の集計をセル［E4］に求めます。

商品番号は文字列になっています。

SUMIF関数を入力

セル［E2］に下のようにSUMIF関数を使って入力します。

これだけで分かる方はここでお終いです。

参考複数の検索条件を満たすセルの合計値を求めるには、SUMIFS関数を使います。

SUMIFS関数については、以下の記事で解説しています。

以下は、関数を使うのが不慣れな方への説明です。

関数の入力

関数を入力するときは、集計値を表示したいセルをアクティブにして半角で［=su］まで入力すると、下のように関数の候補が表示されますので、［SUMIF］を選択します。

方向キー↓でもいいですし、マウスでクリックしてもいいです。

そして、［Tab］キーを押します。

注意ここで［Enter］キーを押さないように気をつけてください。

もし、数式を入力しているときに変になってしまったら、［Esc］キーを押して解除してください。

また、数式バーにも同じものが入力されていきますので、数式バーで入力しても構いません。

関数はオートコンプリート機能で入力

範囲の指定

そうすると、下のようにポップヒントが表示されます。

このポップヒントに合わせて入力していきます。

まず、範囲を指定します。

範囲は、セル［A2］からセル［A10］ですので、ドラッグで範囲指定します。

参考［sumif］は直接入力しても構いません。小文字になっていても、関数名が正しければ、確定した時に大文字になります。

範囲を固定するため、［F4］キーを押します。

下のように絶対参照($)が表示されます。

範囲の設定はこれで終わりなので、カンマ(,)を入力します。

参考絶対参照については、以下の記事で解説しています。

検索条件の指定

次に検索条件を設定します。

［1］で始まる商品番号を検索したいので、［"1*"]と入力します。

完全一致ではないので、検索条件にワイルドカード［*］を使用します。

検索条件の設定はこれで終わりなので、カンマ(,)を入力します。

合計範囲の指定

最後は［合計範囲］です。

合計を求めたい範囲は、セル［B2］からセル［B10］なので、ドラッグで範囲指定します。

範囲を固定するため、［F4］キーを押します。

下のように絶対参照($)が表示されます。

範囲の設定はこれで終わりなので、閉じ括弧［)］を入力して、［Enter］キーで確定します。

これで先頭が1の商品番号のセルを集計することができたと思います。

商品番号の先頭が2と3の合計をSUMIF関数で求める

セル［E3］に［商品番号の先頭が2の売上合計］、セル［E4］に［商品番号の先頭が3の売上合計］を求めるには、関数の検索条件のみを編集するといいですね。

セル［E2］に入力した関数をコピーして編集すると早いと思います。

完成した後の関数の編集

数式バーの［fx］ボタンをクリックすると、［関数の引数］ダイアログボックスが表示されます。

ショートカットキーは、［Shift］+［F3］です。

［検索条件］や［範囲］などを編集する場合は、このダイアログボックスを表示して編集してもいいと思います。

SUMIF関数については、SUMIF関数でも解説しています。

検索条件をセル参照にしています。

集計値が0になっている場合は［文字列］へ変換

もし、集計値が0になっている場合は、商品番号が文字列ではない可能性があります。

商品番号を［区切り位置指定ウィザード］を使用して文字列に変換すると解決するかもしれません。

商品番号の範囲を選択して、［データ］タブの［データツール］グループにある［区切り位置］をクリックします。

［区切り位置指定ウィザード］が表示されますので、［次へ］をクリックします。

次に［区切り位置指定ウィザード-2/3］になりますので、このまま［次へ］ボタンをクリックします。

次に［区切り位置指定ウィザード-3/3］になりますので、ここでデータ形式から［文字列］を選択します。

［完了］ボタンをクリックします。

参考［セルの書式設定］で［文字列］に変換しただけでは、うまくいかないと思いますので、上記の方法をお試しください。

また、日付を文字列に変換する方法は、以下で解説しています。

日付を文字列に変換するには

LEFT関数で先頭文字を抜き出しピボットテーブルで求める

商品番号の先頭文字を抜き出したフィールドを作成して、それでピボットテーブルを作成して求めることもできます。

商品番号の右へ列を挿入して、項目名を付けます。

そして、LEFT関数を使って先頭文字を抜き出します。

あとは、［挿入］タブの［ピボットテーブル］をクリックして、ピボットテーブルを作成して求めます。

下のように［行］に［商品番号先頭文字］と［商品番号］、［値］に［売上］をレイアウトすればいいですね。

参考Excel2021とMicrosoft 365のExcelではUNIQUE関数が使用できます。

SUMIF関数の引数にUNIQUE関数のスピル範囲演算子を使用して自動集計することができます。