質問: excelで特定の文字を含む列の数値と、特定列の掛け算を合計したい excelで、特定の文字を含む列にある数値と、 その数値のある行の特定列の掛け算の合計を、 集計してくれる関数はあるでしょうか。 sumproductやmatch関数で計算しようとしているのですが、うまくできません。 1行目はタイトル行で、左から、商品名、単価、以下たくさん支店名(○支店、×支店、△支店、□支店...)のように並んでいます。 ただし、1か月の集計期間にまったく販売がなかった支店は、表記がなくなります。 つまり、どの列に、どの支店が掲載されるかは、月によって異なります。 2行目より下は、販売商品がずらっと並んでいます。 こちらも販売がなかった商品は掲載されませんが、最大でも2000個です。 最大2000行の指定で足ります。 ある月の集計表(sheetbとします)が、次のようだったとします。 支店によって販売がなかった商品は空白となっています。 商品名 単価 ○支店 ×支店 □支店 りんご 100円 1 (空白) 2 みかん 80円(空白) 2 (空白) バナナ 120円 2 1 1 それをたとえばsheetaから参照して、 次のように各支店別の売上高を、関数で自動計算したいのです。 ○支店 340円 ×支店 280円 △支店 0円 □支店 320円 ▽支店 0円 よい方法があれば、教えていただけますか。
回答: no.1です。 とりあえずは解決したようなので良かったです。 >offset関数が理解できていませんし、 >match関数のあとにある-1も意味がわかりませんが...。 というコトなので若干の説明を・・・ 実は前回 >最大でも2000個です。 の部分を見逃していましたので、 数式を↓のように変更してください。 =iferror(sumproduct((sheet1!b$2:b$3000)*(offset(sheet1!a$2:a$3000,,match(a2,sheet1!$1:$1,0)-1))),"") (前回の1000の部分を3000に変更しただけです) sumproduct関数は配列数式になってしまいますので、極端にデータ量が多い場合はオススメしませんが、 この程度の範囲であれば問題ないと思います。 さてoffset関数についてですが、 仮に前回の配置で「○支店」の場合を考えてください。 数式は =sumproduct((sheet1!b2:b3000)*(sheet1!c2:c3000)) で集計ができます。 今回は後半部分の >(sheet1!c2:c3000) が行によって変動しますので、ここにoffset関数を使っています。 offset関数は (参照,行数,列数,高さ,幅) というコトになりますので 参照(基準)はa2~a3000 の範囲となります。 今回行数は無視していますので、列数だけが必要になります。 =match(a2,sheet1!$1:$1,0) としてしまうと、「3」という値が返ってきます。 先ほど書いたように参照(基準)はa列にしていますので、 その3列右になればd列となり1列ずれてしまいます。 本来であれば「2」という結果がここでほしいので「-1」を付け加えているだけです。 以上長々と書きましたが、この程度で・・・m(_ _)m