ここから質問投稿すると、最大4000ポイント当たる!!!! >>

Excelで、特定の文字を含む列にある数値と、
その数値のある行の特定列の掛け算の合計を、
集計してくれる関数はあるでしょうか。

SUMPRODUCTやmatch関数で計算しようとしているのですが、うまくできません。

1行目はタイトル行で、左から、商品名、単価、以下たくさん支店名(○支店、×支店、△支店、□支店...)のように並んでいます。

ただし、1か月の集計期間にまったく販売がなかった支店は、表記がなくなります。
つまり、どの列に、どの支店が掲載されるかは、月によって異なります。

2行目より下は、販売商品がずらっと並んでいます。
こちらも販売がなかった商品は掲載されませんが、最大でも2000個です。
最大2000行の指定で足ります。

ある月の集計表(SheetBとします)が、次のようだったとします。
支店によって販売がなかった商品は空白となっています。

商品名 単価 ○支店 ×支店 □支店
りんご 100円 1   (空白)  2
みかん  80円(空白)  2   (空白)
バナナ 120円 2    1    1

それをたとえばSheetAから参照して、
次のように各支店別の売上高を、関数で自動計算したいのです。

○支店 340円
×支店 280円
△支店  0円
□支店 320円
▽支店  0円

よい方法があれば、教えていただけますか。

このQ&Aに関連する最新のQ&A

A 回答 (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
    • good
    • 1
この回答へのお礼

続けて詳しいご説明をしてくださり、ありがとうございます!

> 今回は後半部分の
> >(Sheet1!C2:C3000)
> が行によって変動しますので、ここにOFFSET関数を使っています。

という部分と、

> 参照(基準)はA列にしていますので、
> その3列右になればD列となり1列ずれてしまいます。

というご説明が、自分の勘違いを見事に補ってくださいました。

これからは自分でもOFFSET関数を使えそうな気がして、うれしい気分です。
本当にありがとうございました!!

お礼日時:2015/05/26 23:48

こんにちは!


一例です。

↓の画像のように元データが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
「Excelで特定の文字を含む列の数値と、」の回答画像1
    • good
    • 0
この回答へのお礼

できました! ありがとうございます!
こんなにすぐ回答いただけるなんて、すごいですね。

自分では、OFFSET関数が理解できていませんし、
MATCH関数のあとにある-1も意味がわかりませんが...。

それでも集計したかったことは見事にできました。
助かりました。ありがとうございます!

お礼日時:2015/05/25 23:35

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

Qエクセルで複数の条件で掛け算する関数

以下の場合、どのような関数で求められるか、教えてください。

5というある数字にAという条件の場合は2倍、Bという条件の場合は3倍、Cという条件なら5倍
のように条件によってかける数字を変更する方法です。

よろしくお願いします

Aベストアンサー

例えばA1セルに5、B1セルにA,B,Cなどの文字を入力したときに答えをC1セルに表示させるとしたらC1セルには次の式を入力します。

=IF(B1="A",A1*2,IF(B1="B",A1*3,IF(B1="C",A1*5,"")))

QEXCELで特定の文字がある行の数値を合計する方法

EXCELで特定の文字がある行の数値を合計する方法

教えてください。
EXCELで

1列      2列     3列
●●HP  ●●   10,000
●●MO  ●●   20,000
HP●●  ●●   30,000
●●WB  ●●   10,000
●HP    ●●   5,000

等のように1列目で『HP』が付いている行の3列目の金額を集計したいのですが、
どのようにすれば良いでしょうか。

Aベストアンサー

例えば次のような式でどうでしょう。

=SUMPRODUCT(ISNUMBER(FIND("HP",A1:A100))*C1:C100)

QエクセルのIF関数で、文字が入力されていたならば~

エクセルのIF関数で文字が入力されていたならば~、という論理式を組み立てたいと思っています。

=IF(A1="『どんな文字でも』","",+B1-C1)

A1セルに『どんな文字でも』入っていたならば、空白に。
文字が入っていなければB1セルからC1セルを引く、という状態です。

この『どんな文字でも』の部分に何を入れればいいのか教えてください。

またIF関数以外でも同様のことができれば構いません。

宜しくお願いします。

Aベストアンサー

=IF(ISTEXT(A1),"",B1-C1)

でどうでしょうか?

Qエクセルでの指定文字 カウントについて

エクセルで並んだデータでの指定した名前だけの個数をカウントするにはどうすればいいのでしょうか?

山田 高橋 佐藤
高橋 梅田 赤田
 西 山田 梅田
佐藤 山田 梅田

名前が並んだデータで「高橋」という名前が何個あるのかをカウントしたいのですがどうすればいいのでしょうか?

Aベストアンサー

 データは入力されているセルの範囲を「A1:C4」とすれば、

=COUNTIF(A1:C4,"高橋")

Qエクセル数字と文字が入った列の積は?

エクセルで下のような数字と文字が入ったセルがあります。

   A  B  C D(A~Cの合計) E(重量) F(D×E)   
1  2 | 2 | 2 | 6 | 2.5 | 15.0     
2  - | - | - | 1SET | 21.0 | 21.0
3  4 | 4 | 4 | 12 | 3.0 | 36.0

2行目のが問題なのですが、SETの部分だけ省いてEの重量とかけてくれるような関数ってありますか?

Aベストアンサー

=SUBSTITUTE(D1,"SET","")*E1 として下に式をフィルドラッグしてみてください。
あるかどうかにかかわらず、SETという文字列を省いた値にE列の値御を掛けます。

Qエクセルのsumifでかけ算してから合計するには

エクセルで以下のように書いたとき、
 =SUMIF(A:A,"★",B:B)
A列のセルの内容が「★」になっているときのみ、
B列のセルを足していくのは分かりました。

これを、
「A列のセルの内容が「★」になっているときのみ、
B列のセルとC列のセルをかけ算して、その結果を足していく」
という風にしたいのですが、どうすれば良いのでしょうか。

あらかじめD列に、B列とC列をかけたものを入れておく方法は、無しでおねがいします。
よろしくお願いいたします。

Aベストアンサー

こんばんは!

表示したいセルに
=SUMPRODUCT((A1:A1000="★")*(B1:B1000)*(C1:C1000))
としてみてください。

※ SUMPRODUCT関数は配列数式になってしまいますので、データ量が多い場合は計算速度がかなり遅くなります。
1000行程度であれば問題ないと思いますので、数式は1000行目まで対応できる数式にしています。m(_ _)m

Qエクセルで、条件に一致した行を別のセルに抜き出す方法

エクセルで、指定した条件に一致するセルを含む行をすべて抜き出す方法が知りたいです。

たとえば、

<A列> <B列> <C列>
7/1 りんご 100円
7/2 ぶどう 200円
7/2 すいか 300円
7/3 みかん 100円

このような表があって、100円を含む行をそのままの形で、
別のセル(同じシート内)に抜き出したいのですが。

7/1 りんご 100円
7/3 みかん 100円

抽出するだけならオートフィルターでもできますが、
抽出結果を自動的に、別の場所に、常に表示させておきたいのです。

初歩的な質問だと思いますが、検索しても分からなかったので、よろしくお願いします。

Aベストアンサー

同じ質問が結構よく出てますが、そんなに初歩的でもありません
別シートのA1セルに「100円」と入力し、そのシートの任意のセルに以下の式を貼り付けて下さい。後は、下方向、右方向にコピー。
日付のセル書式は「日付」形式に再設定してください

=IF(COUNTIF(Sheet1!$C:$C,$A$1)>=ROW(A1),INDEX(Sheet1!A:A,LARGE(INDEX((Sheet1!$C$1:$C$500=$A$1)*ROW(Sheet1!$C$1:$C$500),),COUNTIF(Sheet1!$C:$C,$A$1)-ROW(A1)+1)),"")

データ範囲は500行までとしていますが、必要に応じて変更して下さい

Q【Excel】文字を含んだ数値の計算

Excel2003を使用しています。

文字を含んでいる数値の合計を出したいのですが、関数を使用すれば可能でしょうか?
表示は『10件』となっていて、半角数字と“件”です。
これが、Sheet1~Sheet8のE2セルに入力されているので、この8枚のシートの合計件数を出したいのですが。。。
よろしくお願いします。

Aベストアンサー

どこか空いているセル(ここではF2セル)を作業列とします。

F2に、
=VALUE(LEFT(E2,LEN(E2)-1))
後はF2を串刺し計算してください。
作業グループ化すればより手っ取り早いでしょう・

Q【Excel】複数の列を参照して、特定の文字列を含む行全体を別シートに抽出する関数

左画面(Book2)のようなシートがあります。

Book2の中で列D・列F・列Iの3列を参照し、別シート(Book4)のA1セルに入力した文字列と一致する文字を含む行全体を、Book4のA3セル以下に抽出するための関数を探しています。

画像の例は、「北海道」という文字列を3列のいずれかに含む行だけを手動で貼り付けたものなのですが、この処理を関数を用いて自動で行いたいのです。

参照したい文字列(北海道)は、3つの列にランダムに含まれている状態です。

元データ(Book2)を更新すれば、別シート(Book4)がリアルタイムに自動で生成されるようにしたいので、関数を使った方法で行いたいと思っています。

ご教示お願い申し上げます。

Aベストアンサー

こんばんは!
一例です。
元データは「Book2」のSheet1にあり、表示するのは「Book4」のSheet1とします。
Book4・Sheet1のA1セルに検索したい文字列を入力し、そのデータを表示させます。
(今回は画像通り”北海道”と入力しておいてください)

Book2のSheet1のJ列を作業用の列として使用します。
J3セルに
=IF(COUNTIF(D3:H3,[Book4.xlsx]Sheet1!$A$1),ROW(),"")
という数式を入れフィルハンドルでこれ以上データはない!という位まで下へコピーしておきます。

そしてBook4、Sheet1のA4セルに
=IFERROR(INDEX([Book2.xlsx]Sheet1!A:A,SMALL([Book2.xlsx]Sheet1!$J:$J,ROW(A1))),"")
という数式を入れ、列・行方向にフィルハンドルでコピーしてみてください。

※ 作業列が目障りであれば遠く離れた列に設けるか
非表示にしておきます。m(_ _)m

QExcel 指定の文字列を含むとカウントするようにしたい場合は?

ただいまエクセルと格闘中の初心者です。
関数等はオートSUMくらいしか使ったことがなく、必要時にいちいち調べているような状態です。
今回は文字列を含むとカウントするところで躓いております。

表の中から特定の文字列を含むとカウントするようにしたい時COUNTIFを使うと思うのですが、この場合正確にその言葉じゃなければカウントされませんよね?

例えば「ボールペン」「サインペン」「シャープペン」のようなペン類をまとめてカウントしたい時、ペンだけで認識してくれるようにすることは可能なのでしょうか?
COUNTIF関連で探してみているのですが、中々思うような検索ができていません(もしくはわからずに見逃しているのかも?)

エクセルにお詳しい方、アドバイスを頂ければと思います。
よろしくお願い致します。

Aベストアンサー

検索条件に「*ペン*」としてみてはいかがですか。

ちなみに上記は「ペン」がどこかにあるものを対象とする。
「*ペン」は文字列がペンで終わるものを対象にする。
「ペン*」は文字列がペンで始まるものを対象とする。


人気Q&Aランキング