こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

複数条件での検索

エクセルでワークシートの
A列に開始日が日付形式で入っています
B列に終了日が日付形式で入っています
C列に商品番号がAA2035のような文字列で入っています。
D列に価格が数値で入っています。
各列とも、重複する値があります。

E1セルに日付、F1セルに商品番号があった場合のその日付(E1)が開始日(A列)から同じ行の終了日(B列)の間にあるその商品の価格を求めたいのです。なお、この条件に該当する価格は各商品ともひとつしかありません。
作業列を使用する方法はわかりますので使用しない方法を教えてください。

投稿日時 - 2019-08-08 21:00:05

QNo.9643862

困ってます

質問者が選んだベストアンサー

こんな感じでどうでしょう
=IF(COUNTIFS(A:A,"<="&E1,B:B,">="&E1,C:C,F1)=1,SUMIFS(D:D,A:A,"<="&E1,B:B,">="&E1,C:C,F1),"NG")

条件に一致する物が1つでない場合はNGと表示します
絶対に条件に一致する物が1つしかないと断言できるのでしたらこれでもいいです
=SUMIFS(D:D,A:A,"<="&E1,B:B,">="&E1,C:C,F1)

投稿日時 - 2019-08-09 00:08:06

お礼

> 条件に一致する物が1つでない場合はNGと表示します

これ、素晴らしいです!
もし2つ以上あった場合、データが間違っているのです。
その検証もできるのでたすかります。
ありがとうございました。

投稿日時 - 2019-08-10 16:19:30

ANo.5

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(9)

ANo.9

Excelの算式は便利なんですが、理解度は結構個人差があります。
自分しか使わないBookで絶対変更しないなら、難しい算式を使ってもいいんでしょうが、複数人で使う場合は気を使います。
ネットから拾ってきた算式を使って、後日問題になったケースが多々ありました。

配列数式は強力なんですが、同じような機能のSumproduct関数がお勧めです。ネットに情報が沢山あります。

この質問は何らかの理由で算式が必要なのかもしれませんが、私は「並べ替えとフィルターの詳細設定」を使います。
何よりも、設定が簡単で(慣れればですが)、AndやOrにも対応しています。
添付図は左上のようなテーブルに対して条件を設定して価格を抽出しています。
日付は条件に組み込むのが少し面倒なので、算式を使っています。

同じ条件で繰り返すなら、「日付」と「商品番号」を入力して、データタブ→詳細設定→「指定した範囲」にして「Ok」

Excel2010です。ご参考に。

投稿日時 - 2019-08-09 13:59:15

お礼

ありがとうございます。
=INDEX($D:$D,MATCH(1,INDEX(($A:$A<=$E$1)*($E$1<=$B:$B)*($C:$C=$F$1),0),0))
で解決しました。

投稿日時 - 2019-08-10 16:34:43

ANo.8

また(文章だけで説明して)データ例も挙げない質問で、読者・回答者にわかってもらうという点を、どう考えているのかと思っちゃう。
下記回答は、推測しての部分が多いから、外れていたら無視して結構。
「複数列ごとのデータに、それぞれの列の一致条件を指定して、AND条件で該当行を探し、該当行の指定列セルのデータを採る。」と、文書で表現すれば、なるのかな。
--
例データ A-D 列
E,F列は検索(指定)条件のつもり。
開始日終了日商品番号価格  開始日 商品番号
2019/7/122019/7/23AA12552019/7/17AA15
2019/7/132019/7/24AA13582019/7/17AA17
2019/7/142019/7/25AA14612019/7/18AA18
2019/7/152019/7/26AA1564
2019/7/162019/7/27AA1667
2019/7/172019/7/28AA1770
2019/7/182019/7/29AA1873
2019/7/192019/7/30AA1976
2019/7/192019/7/30AA2077
2019/7/152019/7/31AA1578
2019/7/162019/8/1 AA15 79
2019/7/172019/8/2 AA15 80
G2セルの式は、
=INDEX($D$1:$D$20,SUMPRODUCT((A$1:A$20=E2)*(C$1:C$20=F2),ROW(D$1:D$20)))
G3,G4以下には、下方向に式を複写する。
行に関する範囲をそろえたり、$で固定したり、の点に注意が必要。
ーーー
質問文にある表現の
>(A列)から同じ行の終了日(B列)の間にある
が、理解しにくかったので、2条件の式にしたが、3条件でうまくいくなら、式のSUMPRODUCT((A$1:A$20=E2)*(C$1:C$20=F2)の次(あと)に1条件を加えてみて。
==
質問は、関数式での回答を希望と解釈したが(それさえ質問に明確に書いてない)、関数式では、式(のタイプ)を思いつくのが難しい。解説本やWEBや先輩に教えてもらわなければ、エクセルを10年やっていても、自然には思いつかないだろう。
本件もSUMPRODUCT関数の本筋の使い方ではないので、どこかで要領を教わったということ。
またエクセルでは、条件で抜出し問題は、式が複雑になるのを知らない人の質問が多い。直感では、すぐにイメージできるので簡単だろうと思ってしまうが。
本件も該当が複数ある問題なら、このやり方では、アウトでしょう。
ーー
VBAとかSQLなら、そういう思い付きの部分は少なくて済む。処理の繰り返しが使える。エクセルを使うならそういうものにも広げないと、質問と回答に1週間かけていたら、はかどらないだろう。

投稿日時 - 2019-08-09 13:33:49

お礼

ありがとうございます。
=INDEX($D:$D,MATCH(1,INDEX(($A:$A<=$E$1)*($E$1<=$B:$B)*($C:$C=$F$1),0),0))
で解決しました。

投稿日時 - 2019-08-10 16:33:09

ANo.7

確認させてください。
》 各列とも、重複する値があります
ということなら、下記は矛盾しませんか?
》 該当する価格は各商品ともひとつしかありません

投稿日時 - 2019-08-09 08:13:44

お礼

早速ありがとうございます。
いいえ、A~D各列だけで見れば、同じデータは何度も出てきます。(日付、商品番号、価格)
しかし、E1が開始日(A列)から同じ行の終了日(B列)の間にあるその商品は常にひとつだけです。

投稿日時 - 2019-08-09 09:17:48

ANo.6

検証のためのサンプルデータを提示してください。
回答No.1に添付のサンプルデータであれば商品番号に対する価格を求めれば良いので複数条件で検索する必要はありません。
一般的に同一商品番号で価格が異なるような商品管理をする業務は無いと思います。
的確な回答を得たいときはサンプルデータも提示すべきです。

投稿日時 - 2019-08-09 06:28:21

補足

>一般的に同一商品番号で価格が異なるような商品管理をする業務は無い

bunjiiさん、いつもありがとうございます。
いえいえ、それが結構あるんです。期間(A列の開始日からB列までの間)によって同一の商品の価格を変えるのです。ですから、あとから何年何月何日の該当商品の値段を簡単に調べるるためにこの関数が必要だったのです。

投稿日時 - 2019-08-11 09:27:39

お礼

ありがとうございます。
=INDEX($D:$D,MATCH(1,INDEX(($A:$A<=$E$1)*($E$1<=$B:$B)*($C:$C=$F$1),0),0))
で解決しました。

投稿日時 - 2019-08-10 16:30:22

ANo.4

No.1と3の方は、配列数式を使った複数条件付き『合計』ですね。
質問文では「日付(E1)が開始日(A列)から同じ行の終了日(B列)『の間にある』その商品の価格を求めたい」とあるのに開始日だけで判定しちゃってます。
正しくは次の通りになると思います。

{=SUM((A2:A16<=E1)*(E1<=B2:B16)*(C2:C16=F1)*D2:D16)/SUM((A2:A16<=E1)*(E1<=B2:B16)*(C2:C16=F1))}

なお、質問文では、「条件に該当する価格は各商品ともひとつしかありません」とありますが、もし、複数の価格があった場合はその平均が表示されます。

私の回答(No.2)では、MATCH関数を使った複数条件付き『検索』で、複数の価格があった場合は一番上の価格が表示されるという意味で計算結果が異なります。

投稿日時 - 2019-08-08 23:32:50

お礼

ありがとうございます。
わかりにくいわたしの説明をご理解していただき感謝いたします・
ご提示の配列数式で正しく答えが返るのを確認しました。
ありがとうございました。

投稿日時 - 2019-08-10 16:23:51

ANo.3

同じ日付、商品が複数あると答えが変わってきますので
{=SUM((A2:A16=E1)*(C2:C16=F1)*D2:D16)}
  ↓
{=SUM((A2:A16=E1)*(C2:C16=F1)*D2:D16)/SUM((A2:A16=E1)*(C2:C16=F1))}

投稿日時 - 2019-08-08 22:59:50

お礼

ありがとうございます。
残念ながら正しい答えが返りませんでした。

投稿日時 - 2019-08-10 16:28:39

ANo.2

こんなところでしょうか?

=INDEX($D:$D,MATCH(1,INDEX(($A:$A<=$E$1)*($E$1<=$B:$B)*($C:$C=$F$1),0),0))

投稿日時 - 2019-08-08 22:50:34

お礼

ありがとうございます。正しい答えが返りました。

投稿日時 - 2019-08-10 16:26:05

ANo.1

添付図を参考に

投稿日時 - 2019-08-08 22:38:22

お礼

ありがとうございます。
残念ながら正しい答えが返りませんでした。

投稿日時 - 2019-08-10 16:27:04

あなたにオススメの質問