Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10全般 : Excel 2016)
複数条件で値の抽出方法
投稿日時: 20/05/13 21:09:37
投稿者: fukukato

お世話になります。
Excelの関数で下記のような条件のデータを抽出できるものでしょうか?
 
【Book1】に下記のようなExcelデータがあります。
A列(月) B列(番号) C列(金額)
202005    あ     1500
202004    あ     1000
202005    い     2000
202004    う     500
202004    え     2000
202005    え     1000
 
【Book2】別のブックのシートに下記のように番号の202005の金額をB列反映したい
A列(番号) B列(金額)
 あ     1500
 い     2000
 う
 え     1000
 
VLOOKUPだと最初のヒットした行の金額になっちゃうので え だと2000が表示されます。
IF関数とかで頑張ってみましたが力が足りずうまくできませんでした。
Book1のデータはフィルターはかけれますが行を増やしたりなどの加工はしたくなく
関数だけで抽出できるものでしょうか?
教示お願いします。

回答
投稿日時: 20/05/13 22:47:09
投稿者: 半平太

シート名が分からないと具体例は書けないですよ。
 
その組み合わせが一つしかないなら、C列は金額なのでSumifsが使えそう

例:B5セル
 =SUMIFS([Book1.xlsm]Sheet1!$C:$C,[Book1.xlsm]Sheet1!$A:$A,202005,[Book1.xlsm]Sheet1!$B:$B,"え")

回答
投稿日時: 20/05/14 08:29:11
投稿者: WinArrow
投稿者のウェブサイトに移動

条件として
番号:あ&月=202005
という組み合わせは1件しかないという条件として
 
半平太さん提案のSUMIFS関数
のほかに、SUMPRODUCT関数でも対応可能です。
 
しかし、BOOK2側のシートに検索キーである「月」を用意した方が拡張性があります。
 
Book2側のシートの1行目の項目名を配置するが、セルB1に月を入力します。
 
Book2側Sheet1
A1:番号、B1:202005
 
B2セルに入力する数式
(1)SUMIFS関数
=SUMIFS([Book1.xlsx]Sheet1!C:C,[Book1.xlsx]Sheet1!A:A,B$1,[Book1.xlsx]Sheet1!B:B,$A2)
 
(2)SUMRODUCT関数
=SUMPRODUCT(([Book1.xlsx]Sheet1!A:A=B$1)*([Book1.xlsx]Sheet1!B:B=$A2),([Book1.xlsx]Sheet1!C:C))
 

投稿日時: 20/05/14 12:43:01
投稿者: fukukato

半平太様
 WinArrow様
 
ご回答ありがとうございます。
SUMIFS関数でデータ抽出できました!
ちなみに参照ブックを閉じてしまうとエラーが出てしまうのですが
ネットで調べてSUM関数とIF関数の組み合わせで配列数式でエラーがでないとあったので
なんとか解読しようとしたのですができませんでした・・・。
エラーを出さない方法などありますでしょうか?

回答
投稿日時: 20/05/14 13:25:51
投稿者: 半平太

>SUM関数とIF関数の組み合わせで配列数式
 
=SUM(IF([Book1.xlsm]Sheet1!$A$2:$A$12=202005,IF([Book1.xlsm]Sheet1!$B$2:$B$12="え",[Book1.xlsm]Sheet1!$C$2:$C$12,0)))
 
※ Sumifsと違うので、列全体指定は避け、出来るだけ少な目にしてください。

回答
投稿日時: 20/05/14 13:30:02
投稿者: WinArrow
投稿者のウェブサイトに移動

fukukato さんの引用:

エラーを出さない方法などありますでしょうか?

 
SUMIFS / SUMI 関数は閉じたブックを参照時エラーになる
のは仕様のようですね・・・・
 
SUMPRODUCT関数を試してみましょう。

投稿日時: 20/05/14 20:48:43
投稿者: fukukato

半平太様
WinArrow様
 
再度回答ありがとうございました!!
大変勉強になりました。
 
半平太様
SUM関数とIF関数の組み合わの関数ありがとうございます。元データが可変するうえに、データ量が多いので今回はSUMPRODUCTの関数で抽出することにしました。今回教えていただいた事は、しっかり理解して使っていきたいと思います!!
 
WinArrow様
SUMPRODUCT関数で抽出する事ができました!聞いたことのない関数でネットで調べても??でしたが
こんな抽出方法もあるんだと勉強になりました。
本当にありがとうございました。