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.2ベストアンサー
- 回答日時:
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
続けて詳しいご説明をしてくださり、ありがとうございます!
> 今回は後半部分の
> >(Sheet1!C2:C3000)
> が行によって変動しますので、ここにOFFSET関数を使っています。
という部分と、
> 参照(基準)はA列にしていますので、
> その3列右になればD列となり1列ずれてしまいます。
というご説明が、自分の勘違いを見事に補ってくださいました。
これからは自分でもOFFSET関数を使えそうな気がして、うれしい気分です。
本当にありがとうございました!!
No.1
- 回答日時:
こんにちは!
一例です。
↓の画像のように元データがSheet1にあり、Sheet2に集計するとします。
Sheet2のA列は入力済みだという前提で・・・
Sheet2のB2セルに
=IFERROR(SUMPRODUCT((OFFSET(Sheet1!A$2:A$1000,,MATCH(A2,Sheet1!$1:$1,0)-1)*Sheet1!B$2:B$1000)),"")
という数式を入れ、フィルハンドルで下へコピーしています。m(_ _)m
できました! ありがとうございます!
こんなにすぐ回答いただけるなんて、すごいですね。
自分では、OFFSET関数が理解できていませんし、
MATCH関数のあとにある-1も意味がわかりませんが...。
それでも集計したかったことは見事にできました。
助かりました。ありがとうございます!
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
ステータスバーの合計に表示さ...
-
会社PCのメールが更新されない
-
あらかじめ用意したテンプレー...
-
別シートの年間行事表をカレン...
-
英数字のみ全角から半角に変換
-
【Excel VBA】PDFを作成して,...
-
マイクロソフト 一時使用コード...
-
エクセルで英文字に入れた下線...
-
Outlook で宛先が複数の場合の人数
-
Microsoft Formsの「個人情報や...
-
Formsにて、匿名にて回答する方...
-
Office 2021 Professional Plus...
-
Microsoft Officeを2台目のPCに...
-
エクセルでレーダーチャートの...
-
Officeを開くたびの「再起動メ...
-
マクロ自動コピペ 貼り付ける場...
-
outlookのメールが固まってしま...
-
エクセルの貼り付け「リンクさ...
-
Microsoft365で写真をアルバム...
-
PDFのハイパーリンクを自動的に変更し...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Officeを開くたびの「再起動メ...
-
英数字のみ全角から半角に変換
-
outlookのメールが固まってしま...
-
大学のレポート A4で1枚レポー...
-
マクロの書き方を教えて下さい
-
エクセルにおいて品名コードを...
-
マイクロソフト 一時使用コード...
-
マクロのコードを教えてください。
-
Microsoft Formsの「個人情報や...
-
一太郎でカーソルを次の行の先...
-
Microsoft365搭載Windows11PCへ...
-
エクセルでXLOOKUP関数...
-
エクセルやワードを無料で使え...
-
会社PCのメールが更新されない
-
teams設定教えて下さい。 ①ビデ...
-
現在、PC2台でMicrosoft 365 Pe...
-
Microsoft365で写真をアルバム...
-
office365って抵抗感ないですか?
-
Outlook で宛先が複数の場合の人数
-
Office2021を別のPCにインスト...
おすすめ情報