[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『同一商品で過去直近の日付を抽出する関数』(はこ)
よろしくお願いします。
返品された商品の出荷日がいつか知りたいのですが、リサイクル品なので何度も出荷履歴があります。
返品された日に一番近い出荷日をリストから抽出したいです。
【1シート目】
[A] [B] [C] [D] [E] [F] [G]
[1]担当者 商品名 番号 顧客名 理由 返品日 最終出荷日
[2]足立 A 1234 山田 破損 2012/11/30 ■■■
[3]田中 B 5678 佐藤 破損 2012/11/29
[4]伊東 C 9012 市川 破損 2013/1/3
[5]加藤 D 3456 木村 破損 2014/10/14
[6]足立 E 7890 飯田 破損 2012/8/22
[7]足立 F 9876 岡田 破損 2014/9/17
【2シート目】
[A] [B] [C] [D] [E] [F]
[1]商品名 出荷日
[2]A 2010/11/10
[3]B 2011/10/2
[4]B 2013/11/3
[5]C 2012/12/10
[6]C 2011/11/11
[7]B 2012/10/10
[8]D 2013/11/10
となっており、
商品Aで求めたいのは返品された 2012/11/30 以前に出荷された日なので
2010/11/10となりますが、
商品Bでも同様だと2011/10/2、2013/11/3、2012/10/10のうち、
返品された2012/11/29以前の一番近い日なので2012/10/10ということになります。
それを
【1シート目】のG列「■■■」のところにB列の商品名とF列の日付をもとに
【2シート目】の出荷日から上記の条件に値する日付を表示させたいのです。
関数で表示させたいのですがご教示ください。
宜しくお願いいたします。
【追記】
このデータ、商品名が「A」となっていますが、実際の帳票は「6ケタの数字のみ」「アルファベットと数字の混合」「数字5ケタ-(ハイフン)数字2ケタ」の
3種類あります。
ねむねむさんにご教示いただいた数式(2つめ)で値が表示されるのは商品名が「6ケタの数字」の場合のみです。
< 使用 Excel:Excel2010、使用 OS:Windows7 >
Sheet1のG2セルに
=IF(COUNTIFS(Sheet2!A$2:A$100,B2,Sheet2!B$2:B$100,"<"&F2),MAX(INDEX((Sheet2!A$2:A$100=B2)*(Sheet2!B$2:B$100<F2)*Sheet2!B$2:B$100,0)),"")
と入力して下へフィルコピーしてみてくれ。
なお、上記式ではSheet2のほうのデータが最大100行目まで入力されるものとしている。
あるいはG2セルに
=MAX(INDEX((Sheet2!A$2:A$100=B2)*(Sheet2!B$2:B$100<F2)*Sheet2!B$2:B$100,0))
と入力し、下へフィルコピー、G列の表示形式を「分類」にユーザー定義、種類に「yyyy/m/d;;」とする方法もある。
追記
式では「出荷日<返品日」としているので「出荷日<=返品日」である場合は式中の「<」を「<=」にしてくれ。
(ねむねむ) 2015/07/02(木) 10:31
ねむねむさん
ありがとうございます!
試してみました!
実は【2シート目】のデータ量が80000行くらいまであるので、B$2:B$100を84000に変えたんですがエラーになってしまいます。
何が違うのかわかりません<m(__)m>
2つ目の式で変更したらすべて数値が0になってしまいます。
とてもわかりやすく記載していただいたのに勉強不足で申し訳ありません。
ご教示ください。
(はこ) 2015/07/02(木) 11:43
エラーになる場合はなんというエラーになるかも書いてくれ。
(エラー内容も判断の材料になる)
>B$2:B$100を84000に変えたんですが
とあるがA$2:A$100も同じように変更しているだろうか?
もし、A列も同じように変更したのであれば変更後の式を示してみてくれ。
(ねむねむ) 2015/07/02(木) 11:49
エラーというより「0」表示です。
表示形式を変更すると1900/1/0となります。
$A1:$A100も同じ行数に変更しています。
追記をしましたが何か原因がありますでしょうか?
変更後の式は
=MAX(INDEX((Sheet2!B$2:B$84500=C20)*(Sheet2!C$2:C$84500<=G20)*Sheet2!C$2:C$84500,0))
としました。
C=20・・・Cが商品名
G=20・・・Gが仕入日で、20行目の数式です。
シート1とシート2の商品名は同じものになっているだろうか? (前後に余計なスペースが入っていたりはしないだろうか?) (ねむねむ) 2015/07/02(木) 12:59
ご返信ありがとうございます。
両シートの商品名は同じ書式となっています。
(はこ) 2015/07/02(木) 13:14
ありがとうございました。
スペースを削除したことと、もともとの出荷日に未入力を修正し
思い通りの結果ができました。
感謝です!!!!
とっても助かりました
(はこ) 2015/07/02(木) 15:58
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.