「120、180、200、310、190のうち、どれを足すと500になるか」。この問いは、一覧を全部足す作業とは違います。必要なのは、各行を使うか、使わないかを決めることです。ExcelのSUMを別の関数に置き換えるだけでは、その選択は決まりません。
この記事では、ITtripが提供する「合計さがし」を使う手順も紹介します。金額の組み合わせを探す道具として使い、元の明細や取引の内容は手元の資料で確認してください。
まず、集計と組み合わせ探しを分ける
| 知りたいこと | 必要な操作 |
|---|---|
| 一覧全体はいくらか | 金額列を合計する |
| 取引先Aだけでいくらか | 取引先という既知の条件で集計する |
| どの明細を足すと500か | 行を選び、その合計が500になる組み合わせを探す |
共通の請求番号がある場合は、その番号で照合するほうが先です。番号などの共通キーがないときに、合計金額から候補を探す方法が役立ちます。条件がすでに決まっている集計は、条件が決まっているデータを Power Query で集計するも参考になります。
Excelでは「使う＝1、使わない＝0」の列を作る
B2:B6に金額、C2:C6に選択用の0を入れます。C列は元の金額を変更せず、採用する行を記録するための列です。E2には次の式を入力します。
=SUMPRODUCT(B2:B6,C2:C6)| 金額（B列） | 選択（C列）の例 |
|---|---|
| 120 | 1 |
| 180 | 1 |
| 200 | 1 |
| 310 | 0 |
| 190 | 0 |
この選択なら、120×1＋180×1＋200×1＋310×0＋190×0＝500です。SUMPRODUCTは対応する値の積を合計する関数で、この「金額×選択」の形に使えます。SUMPRODUCT 関数（Microsoft）で関数の仕様を確認できます。
ソルバーで選択列の0と1を決める
Microsoft公式のソルバーは、目的セルと変数セル、制約を指定するアドインです。上の表は、金額を固定し、選択列だけを変更するモデルとして作れます。
- ［データ］の［ソルバー］を開きます。表示されない場合は、利用環境でソルバー アドインを有効にできるか確認します。
- 目的セルをE2にし、目的の値を500に設定します。
- 変更する変数セルをC2:C6にします。
- C2:C6にbin（バイナリ）の制約を加え、選択を0または1に限定します。
- この式は固定金額と選択変数の積の合計なので、線形モデルとしてシンプレックス LPを選び、解決します。
- 得られたC列を確認し、1の行の金額を手元で再合計します。
binを付けずに小数を許すと、たとえば一つの請求書を0.5件だけ採用するような結果が混ざり、明細の組み合わせという目的から外れます。Microsoftのソルバーを使って問題を定義し、解決する（Microsoft）では制約と目的値の指定を確認できます。Excel for the webでは、この標準のソルバー アドインを利用できません。
候補を複数見比べる単発作業なら合計さがしを使う
合計さがしの初期版は、整数の金額を30件まで貼り付け、目標金額に一致する候補を表示します。この例には、120＋180＋200と310＋190の2候補があります。一つ見つかった段階で唯一の答えと決めず、元の明細を比べます。
- 金額一覧に120、180、200、310、190を1行ずつ貼り付けます。
- 目標金額を500にし、［この合計を探す］を押します。
- 候補の行と金額を確認します。完了と打ち切りの表示を読み分けます。
どちらの方法を選ぶか
| 作業の条件 | 選び方 |
|---|---|
| 同じブックで条件を変えて繰り返す | 選択列とソルバーのモデルを残す |
| 30件以内の整数を一度だけ照合する | 貼り付け検索で候補を比較する |
| 納期や個数など別の制約も必要 | 制約を設定できるモデルを検討する |
| 小数、31件以上、業務全体の継続照合 | 初期版の検索範囲を超えるため、別の方法を選ぶ |
ソルバーの一回の結果は、すべての一致候補を列挙した証拠にはなりません。合計さがしも約5秒・100候補の上限で打ち切る場合があります。必要なのは、見つかった候補と、まだ調べていない範囲を分けて扱うことです。
よくある質問
SUMPRODUCTだけで行を選べますか？
選択列を与えたときの合計は計算できますが、どの行を1にするかは別に決める必要があります。上の例ではソルバーが選択列を変更します。
表示を整数にすれば小数の明細を検索できますか？
表示形式を変えるだけで元の値が整数になるとは限りません。小数を勝手に四捨五入すると照合対象が変わるので、元データの単位と端数処理を確認してください。
コメント