エクセルの数式・関数を教えてください。

質問

入力シートにデータがあり、請求書シートに期間を参照して日付を埋め込みたいと考えています。
期間というものは、請求シートの請求日の前月１ヶ月間を考えています。

入力シート
　　　a　　　　b　　　　　c　　　　　d
1　日付　　商品１　　商品2　　商品3
2　12/7　　　　100　　　200　　　300
3　12/15　　　　40　　　　50　　　60
4　1/7　　　　　 70　　　　80　　　90
請求シート
　請求日（AR3）
　請求内訳日付欄（C12：C42）

C12などの欄の数式で、セルAR3の日付の前月の日付範囲で入力シートの日付欄を抽出したいのです。
いろいろ調べましたが、なかなかうまくいかず困っています。
参考となる数式を教えていただければ、助かります。

回答No.1

kagakusuki · ベストアンサー

今仮に、入力シートのシート名が

入力

であるものとします。

　まず、請求シートのC13セルに次の関数を入力して下さい。

=IF(ISNUMBER(1/DAY($AR$4)),IF(ROWS($C$13:$C13)>COUNTIF(入力!$A:$A,"<"&DATE(YEAR($AR$4),MONTH($AR$4),1))-COUNTIF(入力!$A:$A,"<"&DATE(YEAR($AR$4),MONTH($AR$4)-1,1)),"",SMALL(入力!$A:$A,ROWS($C$13:$C13)+COUNTIF(入力!$A:$A,"<"&DATE(YEAR($AR$4),MONTH($AR$4)-1,1)))),"")

　次に、請求シートのD13セルに次の関数を入力して下さい。

=IF($C13="","",VLOOKUP($C13,入力!$A:$D,MATCH(D$12,入力!$A$1:$D$1,0),FALSE))

　次に、請求シートのD13セルをコピーして、請求シートのE13～F13の範囲に貼り付けて下さい。
　次に、請求シートのC13～F13の範囲をコピーして、同じ列の14行目以下に貼り付けて下さい。

　以上です。

回答No.4

tom04

こんばんは！
一例です。

↓の画像で左側が「入力シート」右側が「請求シート」だとします。
「入力シート」に作業用の列を設けます。
作業列E2セルに
=IF(請求シート!$AR$3="","",IF(A2>=EDATE(請求シート!$AR$3,-1),ROW(),""))
という数式を入れオートフィルでずぃ～～～！っと下へコピーしておきます。

そして「集計シート」のC12セルに
=IF(COUNT(入力シート!$E:$E)<ROW(A1),"",INDEX(入力シート!A:A,SMALL(入力シート!$E:$E,ROW(A1))))
という数式を入れF12セルまで列方向にオートフィルでコピー！
C12セルの表示形式は「日付」にしておいて、
C12～F12セルを範囲指定　→　F12セルのフィルハンドルで下へコピー！
これで画像のような感じになります。

※　EDATE関数を使用するにあたり、Excel2003以前のバージョンですと
メニュー　→　ツール　→　アドイン　→　「分析ツール」にチェックを入れておく必要があります。m(_ _)m

回答No.3

kagakusuki

回答:No.1です。
　説明し忘れていた事がありました。
　入力シートの日付欄に入力されている日付は、必ず年も付けて

2011/12/7
2011年12月7日
H23.12.7
平成23年12月7日

の様な形式で表示される様に、セルの書式設定の表示形式を[日付]の

2001/3/14
2001年3月14日
H13.3.14
平成13年3月14日

等に設定して下さい。

　それから、先程の回答させて頂いたANo.1において挙げさせて頂いた方法は、入力シートの日付欄に入力されている日付に重複して入力されたもの（例えば、2行目に2011/12/7が入力されているにも関わらず、2行目以下のどこかの行にも再び2011/12/7が入力されている等）がありますと、正しい値を表示出来ない恐れがあります。
　もし、各商品欄に入力される値が数値のみである場合には、関数を入力する際に、請求シートのC13セルとD13セルに入力する関数を、それぞれ次の様な関数としておきますと、日付が重複して入力されていた場合においても正しい値を表示する事が出来ます。（但し、こちらの場合には、該当日における数値の合計のみが表示され、文字情報等は無視されます）

請求シートのC13セルに入力する関数

=IF(AND(ISNUMBER(1/DAY($AR$4)),C12<>"",COUNT(入力!$A:$A)),IF(COUNTIF(C$12:C12,SMALL(入力!$A:$A,COUNTIF(入力!$A:$A,"<"&DATE(YEAR($AR$4),MONTH($AR$4),1)))),"",SMALL(入力!$A:$A,COUNTIF(入力!$A:$A,"<"&INT(IF(ISNUMBER(1/DAY(C12)),C12+1,DATE(YEAR($AR$4),MONTH($AR$4)-1,1))))+1)),"")

請求シートのD13セルに入力する関数

=IF($C13="","",SUMIF(入力!$A:$A,$C13,OFFSET(入力!$A:$A,,MATCH(D$12,入力!$1:$1,0)-COLUMN(入力!$A$1))))

回答No.2

keithin

簡単な方法：
請求シートのＡ２セルに
=IF(AND(DATE(YEAR($AR$3),MONTH($AR$3)-1,DAY($AR$3))<=入力シート!A2,入力シート!A2<=$AR$3),入力シート!A2,"")
と記入，以下コピーして埋めておきます。
入力シートの日付から，期間内の日付が並びます。

＃「前月１ヶ月間」のイミが違うなら，適切な期間を設定した数式に修正して下さい。
＃請求シートを汚したくなければ，別のシート３とか使ってください。

Ｃ１２には
=IF(COLUMN(A12)>COUNT($A:$A),"",SMALL($A:$A,COLUMN(A12)))
と記入，日付の書式を取り付けて右にコピーして並べておきます。