エクセル関数を使った集計

質問

添付ファイルの様に、エクセルのsheet1と2に同様の表があります。sheet1に於いて
A列が四角になっているC列の番号と同じ番号を、sheet2のC列を検索し、
その中で、Ｂ列がＳになっている件数と、その行のD列の合計を求める
関数式を教えて下さい。（実際の表自体は3000行ぐらいあります）
例で求めたい結果は　件数＝2件、合計＝11,000- になります。
以上宜しくお願いします。

回答No.5

excels

連ツイしていますので、参考URLを見てみてください。

会話（あるいはタイムライン）を見て頂くと、（私が考えたもの以外にも）何パターンか方法が出てきます。

最初に考えた方法の概略を書いておきます。

・Sheet2のB列→Sheet2のE列に転記

・vlookup関数でSheet2のD列、E列（＝B列）をSheet1のE列、F列に転記

・iferror関数を使って、Sheet1のE列→G列、F列→H列にエラーを消しつつ内容を転記

・sumifs関数、countifs関数を使って条件に合う（Sheet1のA列が「■」、Shee1のG列が「S」）行のみ金額、あるいは件数を集計

という流れです。

ツイッターのほうでは、画像も添付してありますので、合わせて見てみてください。

参考URL：

https://twitter.com/excelspeedup/status/970463054608543744

回答No.4

msMike

》 例で求めたい結果は　件数＝2件、合計＝11,000- になります
その結果を求める式は何処に入力するのですか？

回答No.3

bunjii

>A列が四角になっているC列の番号と同じ番号を、sheet2のC列を検索し、
その中で、Ｂ列がＳになっている件数と、その行のD列の合計を求める
関数式を教えて下さい。（実際の表自体は3000行ぐらいあります）
件数は次の数式で良いでしょう。但し、配列数式なのでCTRL+Sift+Enterキーの打鍵で確定してください。
=SUM((Sheet2!C$1:C$3000=TRANSPOSE((Sheet1!A$1:A$3000="■")*Sheet1!C$1:C$3000))*(Sheet2!B$1:B$3000="S"))
合計は次の数式で良いでしょう。これも配列数式です。
=SUM((Sheet2!C$1:C$3000=TRANSPOSE((Sheet1!A$1:A$3000="■")*Sheet1!C$1:C$3000))*(Sheet2!B$1:B$3000="S")*Sheet2!D$1:D$3000)

Sheet1またはSheet2の何れかの行と列が反転しているとTRANSPOSE関数が不要になり、SUMPRODUCT関数を使えば配列数式を回避できます。
尚、自動再計算になっていると動作が緩慢になりますのでご注意ください。

お礼

ご回答有り難うございます。只、TRANSPOSE関数について私の方が良く理解できていない為、編集が困難となります。今回は当方都合となりますが、シンプルな回答を頂いた、mt2015さんをベストアンサーとさせていただきます。お手数を掛け申し訳ありませんが、bunjiiさんから回答いただいた関数については時間が取れ次第勉強させていただき、問題解決の幅を広げたいと思います。 本当に有り難うございました。

回答No.2

mt2015

作業列を2つ使います。
1つ目の作業列をSheet1のE列とします。
E1に↓の式を入れ、下にコピーしてください。
=IF(A1="■",C1,"")

2つ目はSheet2のE列。E1に↓を入れ、下にコピーしてください。
=(0<COUNTIF(Sheet1!E:E,C1))*(B1="S")*D1

これで件数は　=COUNTIF(Sheet2!E:E,">0")
合計は　=SUM(Sheet2!E:E)
と、なります。

作業列が邪魔なら非表示にしてください。

お礼

ご回答有り難うございます。関数がシンプルで当方でも編集活用でき問題解決できそうです。感謝！！

回答No.1

Nouble

回答では、無く
お願いなのですが
此の点、ご容赦ください。

シート１の、検索結果に
TRANSPOSE関数を、施し
二次元表にし、

併せて
総当たりを、させれば、
件数は、出るように
思います。

しかし、
総計には、参照が
必須ですが、

過去、一時
こう言った、参照が
叶わなくなっていた、時期が
ありまして、

今、叶うかは
未確認です。

併せて、
記述には、少なからず
時間が、掛かります。

以上、踏まえ
ご猶予を、頂ければ
幸いです。

お礼

ご回答有り難うございます。配列関数は当方が良く理解できておらず、編集等困難になりそうです。mt2015さんのご回答で内容がシンプルなため編集活用でき問題解決出来そうです。お手数を掛け申し訳ありませんでした。まだまだスキル不足のため、又質問する機会があると思いますので、その際は宜しくお願いします。