- ベストアンサー
複数条件での検索
エクセルでワークシートの A列に開始日が日付形式で入っています B列に終了日が日付形式で入っています C列に商品番号がAA2035のような文字列で入っています。 D列に価格が数値で入っています。 各列とも、重複する値があります。 E1セルに日付、F1セルに商品番号があった場合のその日付(E1)が開始日(A列)から同じ行の終了日(B列)の間にあるその商品の価格を求めたいのです。なお、この条件に該当する価格は各商品ともひとつしかありません。 作業列を使用する方法はわかりますので使用しない方法を教えてください。
- emaxemax
- お礼率100% (834/834)
- Excel(エクセル)
- 回答数9
- ありがとう数9
- みんなの回答 (9)
- 専門家の回答
質問者が選んだベストアンサー
こんな感じでどうでしょう =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)
その他の回答 (8)
- nishi6
- ベストアンサー率67% (869/1280)
Excelの算式は便利なんですが、理解度は結構個人差があります。 自分しか使わないBookで絶対変更しないなら、難しい算式を使ってもいいんでしょうが、複数人で使う場合は気を使います。 ネットから拾ってきた算式を使って、後日問題になったケースが多々ありました。 配列数式は強力なんですが、同じような機能のSumproduct関数がお勧めです。ネットに情報が沢山あります。 この質問は何らかの理由で算式が必要なのかもしれませんが、私は「並べ替えとフィルターの詳細設定」を使います。 何よりも、設定が簡単で(慣れればですが)、AndやOrにも対応しています。 添付図は左上のようなテーブルに対して条件を設定して価格を抽出しています。 日付は条件に組み込むのが少し面倒なので、算式を使っています。 同じ条件で繰り返すなら、「日付」と「商品番号」を入力して、データタブ→詳細設定→「指定した範囲」にして「Ok」 Excel2010です。ご参考に。
お礼
ありがとうございます。 =INDEX($D:$D,MATCH(1,INDEX(($A:$A<=$E$1)*($E$1<=$B:$B)*($C:$C=$F$1),0),0)) で解決しました。
- imogasi
- ベストアンサー率27% (4737/17068)
また(文章だけで説明して)データ例も挙げない質問で、読者・回答者にわかってもらうという点を、どう考えているのかと思っちゃう。 下記回答は、推測しての部分が多いから、外れていたら無視して結構。 「複数列ごとのデータに、それぞれの列の一致条件を指定して、AND条件で該当行を探し、該当行の指定列セルのデータを採る。」と、文書で表現すれば、なるのかな。 -- 例データ A-D 列 E,F列は検索(指定)条件のつもり。 開始日 終了日 商品番号 価格 開始日 商品番号 2019/7/12 2019/7/23 AA12 55 2019/7/17 AA15 2019/7/13 2019/7/24 AA13 58 2019/7/17 AA17 2019/7/14 2019/7/25 AA14 61 2019/7/18 AA18 2019/7/15 2019/7/26 AA15 64 2019/7/16 2019/7/27 AA16 67 2019/7/17 2019/7/28 AA17 70 2019/7/18 2019/7/29 AA18 73 2019/7/19 2019/7/30 AA19 76 2019/7/19 2019/7/30 AA20 77 2019/7/15 2019/7/31 AA15 78 2019/7/16 2019/8/1 AA15 79 2019/7/17 2019/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週間かけていたら、はかどらないだろう。
お礼
ありがとうございます。 =INDEX($D:$D,MATCH(1,INDEX(($A:$A<=$E$1)*($E$1<=$B:$B)*($C:$C=$F$1),0),0)) で解決しました。
- msMike
- ベストアンサー率20% (363/1775)
確認させてください。 》 各列とも、重複する値があります ということなら、下記は矛盾しませんか? 》 該当する価格は各商品ともひとつしかありません
お礼
早速ありがとうございます。 いいえ、A~D各列だけで見れば、同じデータは何度も出てきます。(日付、商品番号、価格) しかし、E1が開始日(A列)から同じ行の終了日(B列)の間にあるその商品は常にひとつだけです。
- bunjii
- ベストアンサー率43% (3589/8248)
検証のためのサンプルデータを提示してください。 回答No.1に添付のサンプルデータであれば商品番号に対する価格を求めれば良いので複数条件で検索する必要はありません。 一般的に同一商品番号で価格が異なるような商品管理をする業務は無いと思います。 的確な回答を得たいときはサンプルデータも提示すべきです。
お礼
ありがとうございます。 =INDEX($D:$D,MATCH(1,INDEX(($A:$A<=$E$1)*($E$1<=$B:$B)*($C:$C=$F$1),0),0)) で解決しました。
補足
>一般的に同一商品番号で価格が異なるような商品管理をする業務は無い bunjiiさん、いつもありがとうございます。 いえいえ、それが結構あるんです。期間(A列の開始日からB列までの間)によって同一の商品の価格を変えるのです。ですから、あとから何年何月何日の該当商品の値段を簡単に調べるるためにこの関数が必要だったのです。
- t_ooishi
- ベストアンサー率35% (7/20)
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関数を使った複数条件付き『検索』で、複数の価格があった場合は一番上の価格が表示されるという意味で計算結果が異なります。
お礼
ありがとうございます。 わかりにくいわたしの説明をご理解していただき感謝いたします・ ご提示の配列数式で正しく答えが返るのを確認しました。 ありがとうございました。
- watabe007
- ベストアンサー率62% (476/760)
同じ日付、商品が複数あると答えが変わってきますので {=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))}
お礼
ありがとうございます。 残念ながら正しい答えが返りませんでした。
- t_ooishi
- ベストアンサー率35% (7/20)
こんなところでしょうか? =INDEX($D:$D,MATCH(1,INDEX(($A:$A<=$E$1)*($E$1<=$B:$B)*($C:$C=$F$1),0),0))
お礼
ありがとうございます。正しい答えが返りました。
関連するQ&A
- IF関数(複数条件)
E1:検索したい商品番号 E2:検索したい商品棚コード A列:商品番号 B列:商品棚コード C列:価格 とある場合、A列の商品番号もB列の商品棚コードもE1、2のものと一致する場合に限り、C列の価格をD列に表示するようにしたいです。 どう入力すれはいいでしょうか。
- ベストアンサー
- Excel(エクセル)
- エクセル 複数条件の書式設定について
A B C D E F 01 02 03 04 05 06 07 08 09 10(日付) 2011/01/01 2011/01/05 a 2011/01/03 2011/01/08 b 2011/01/03 2011/01/10 c 上記のような配列(1行目D列から1日~31日までの日付、A列に開始日、B列に終了日、C列に種類)でD2から開始日から終了日の期間を条件がaなら赤、bなら青、cなら黄色というように色付けするにはどうしたらよいでしょうか。 例えば2行目なら、D2からH2まで赤、3行目ならF3からK3まで青、という感じです。 何卒よろしくお願い致します。
- ベストアンサー
- インターネットビジネス
- SUMPRODUCT関数で複数条件適用されない
Sheet1 A列:日付 B列:注文番号 C列:品名 D列:整理番号 E列:数量 集計 A列:品名 B~AF列:日付(1~31日) 【集計】B~AF列の日付、A列の品名が 【Sheet1】A列の日付、C列の品名と一致したら 【Sheet1】E列の数量を【集計】の該当するセルに抽出する 式をSUMPRODUCT関数で作成しました。 例)10/1 りんご 20 【集計】B3セルに以下の数式を入れましたが なぜか数量が「0」のままになります。 =SUMPRODUCT('Sheet1'!$A$3:$A$200='集計'!B$2)*('Sheet1'!$C$3:$C$200="りん ご")*('Sheet1'!$E$3:$E$200) 特に数式のミスはないと思うのですが どうしても正しい値が入りません。 数式に間違いがあればご指摘いただけませんでしょうか。 また、他に良き方法があればご教示いただけるとありがたいです。
- ベストアンサー
- その他MS Office製品
- エクセルでの数値検索方法
A列に日付が上から順に入力されており、B列には数が入力されている時、同一シート上のセルに開始日を、もう一つのセルに終了日を入れると、その期間の数の合計が表示される計算式を教えてほしい。 (イメージ) A B 開始日→□ 1__ 1____18 終了日→□ 2__ 2____10 合 計→□ 3__ 3____14 4__ 4____ 9
- 締切済み
- その他(業務ソフトウェア)
- IF関数?いくつかの条件を纏める
エクセルの関数について教えて下さい。 セルAの番号をもとにその番号が使えるかどうかを調べたいのです。 使用履歴もとっておきたいです。 番号123456はいったん終了後、次の人が使っているため使用不可。 123457は開始も終了も日付が入っていないので使用可。 A B C D 開始 終了 123456 10月20日 10月25日 不可 123456 10月25日 不可 123457 可 IFANDの関数を使ってみたのですが、Dに結果が出てきません。 どのような関数を入れたらよいか教えて下さい。
- ベストアンサー
- オフィス系ソフト
- 複数の条件にあったセルをカウントする。
すみません。 office2007を使用しています。 下記の条件にあったセルをカウントしたいのですが、SUMPRODUCTやCOUNTIFなどを使って試したのですが、うまくいきませんでした。どなたか教えていただけますでしょうか。お願いいたします。 条件1:A列がBlueである。 条件2:B列になんらかの値が入力されている。 条件3:開始日が2009/7/1~2009/10/30の間 上記の条件を満たした、セルをカウントしたい。 A B C Product PO番号 開始日 Red NNN-00 2009/10/28 Blue 2009/9/22 Red NNN-01 2009/8/12 Blue 2009/7/3 Blue NNN-02 2009/6/5 何卒、よろしくお願いいたします。
- 締切済み
- その他(ITシステム運用・管理)
- Excelでの複数結果抽出がわかりません
商品の日毎受注個数の一覧表をExcelで作成しています。 A列には日付 B列にはその日の受注個数 が並んでいます。 そしてB列中の最大値、最小値をE1、E2に関数で表示させています。 B列からE1、E2セル参照で最小値を検索し、隣A列の日付をE1の右隣セルから右方向に並べて複数抽出したいのですが、(該当する日付をすべて表示)どのような関数、マクロを書けば良いでしょうか?
- 締切済み
- オフィス系ソフト
- エクセル 複数条件での検索を関数を使って解決したいです。
エクセル関数を使って複数の条件を満たしたときの 特定のセルの文字列を返す方法を考えています。 例を挙げると A B C D E F 1 金額 日付 名称 金額 日付 名称 2 100 1月5日 a 100 2月1日 =??? 3 200 1月5日 b 4 300 1月6日 c 5 400 2月1日 d 6 100 2月1日 XYZ 7 100 2月3日 e 8 200 2月3日 f 上のような表のときに 金額=「100」 かつ 日付=「2月1日」 という2つの条件を 満たしたときのC列の文字列「XYZ」を返すということをやりたいのです。 金額だけですとVLOOKUP関数を使って =VLOOKUP(D2,A2:C8,3,false) とやって「a」という文字列はかえってくるのですが どうやっても「XYZ」という文字列が返せません。 (日付の条件を入れていないので当たり前ですが...) なにかいい方法がありましたらお願いします。
- ベストアンサー
- オフィス系ソフト
- 曜日を条件に検索をしたいです。
A列には「yy/m/d (aaa)」形式で(今日なら07/10/25 (木)) 土、日、祝日を抜いた日付が A1セルからA1000セルまで入力されています。 (データは日々増えていきます) B列にはランダムな数字が入っています。 やりたいこと1 オートフィルタで 「(月)」のデータを摘出したいです。 オートフィルタのオプションで(月)を含むで検索しても 何もヒットしません。 やりたいこと2 月火水木金それぞれのB列の合計が知りたいです。 SUMIF関数で「=SUMIF(A:A,"月",B:B)」 と入力しても0になってしまいます。 よろしくお願いします。
- ベストアンサー
- オフィス系ソフト
- Excel 複数条件一致による文字列取得
途中まで作成してみましたが、できなかったのでお教え願います。 (1)シート<入力用>に以下のように入力されています。 ┃ ┃A ┃B ┃C ┃D ┃E ┃ ━━━━━━━━━━━━━━━━━━━━━━━ ┃1┃日付┃社員番号┃休暇等┃業務開始時間┃業務終了時間┃ ┃2┃0926┃11111111┃休 ┃ ┃ ┃ ┃3┃0927┃11111111┃直 ┃8:30 ┃17:30 ┃ ┃4┃0928┃11111111┃ ┃8:30 ┃18:30 ┃ ┃5┃0926┃22222222┃研 ┃8:30 ┃17:30 ┃ といった状態で約120名のランダムなデータがべた打ちされてます。 (2)(1)を以下のような集計表に飛ばしたいのです。 ┃ ┃A ┃B ┃C ┃D ┃E ┃・・・・・・ ━━━━━━━━━━━━━━━━━━━━━━━ ┃1┃ ┃0926┃0927┃0928┃0929┃・・・・・・ ┃2┃11111111┃休 ┃直 ┃ ┃・・・・・・ ┃3┃22222222┃研 ┃ ┃ ┃・・・・・・ ┃4┃33333333┃・ ┃・ ┃ ┃・・・・・・ ┃5┃44444444┃・ ┃・ ┃ ┃・・・・・・ 行:日付 列:社員番号 飛ばしたい情報は、(1)の<休暇等>文字列です。 ためしに、(2)のB2に以下の式を入れてみました。 B2=index(入力用!A2:E5,match(B1,入力用!A2:A5,0),match(A2,入力用!B2:B5,0)) 当然ですが、日にちが帰ってきました。が、なぜか0927が帰ってきました。 このINDEXとMATCHを利用して(1)のC2:C5にある文字列を拾えないでしょうか? 宜しくお願いいたします。
- 締切済み
- オフィス系ソフト
お礼
> 条件に一致する物が1つでない場合はNGと表示します これ、素晴らしいです! もし2つ以上あった場合、データが間違っているのです。 その検証もできるのでたすかります。 ありがとうございました。