売上表から20日〆の請求書に転記

質問

いつもお世話になります。
WINDOWS7 EXCELL2010です。

添付図で説明させていただきます。
上段がシート「売上表」です。
下段はシート「請求書」です。

日毎に「売上表」に記録していきます。
前月の21日～当月の20日締めで21日に請求書を作成します。

この時添付図でいうと、9/21～10/20を10月分として
「請求書」A2　に　ID 0030　を入力
納品日 　「売上表」B2 が　「請求書」B15　に
作業内容　「売上表」C2　が　「請求書」C15　に
と「売上表」から「請求書」に転記する数式をご教授いただけないでしょうか。

「売上表」A2 に　※これを手がかりと思ったのですがどんな数式かが思いつきません。
=IF(B2="","",MONTH(IF(DAY(B2)<=20,EDATE(B2,1),EDATE(B2,2))))

因みに　10/1～10/31　を10月分とするならできたのですが。
請求書　B15
=IFERROR(INDEX(売上表!B:B,SMALL(売上表!AC:AC,ROW(A1))),"")
売上表　AC2
=IF(AND(B2=請求書!$B$1,MONTH(B2)=MONTH($R$1)),ROW(),"")

回答No.2

tom04 · ベストアンサー

No.1です。

投稿後間違いに気づきました。
前回の数式ではA2のIDの条件が入っていませんでした。
A15セルの数式はそのままで

B15セルの数式（前回同様で配列数式）を
=IFERROR(INDEX(売上表!$A$1:$I$1000,SMALL(IF((売上表!$F$1:$F$1000=$A$2)*(売上表!$G$1:$G$1000=$H$3)*(売上表!$B$1:$B$1000>EOMONTH($H$5,-2)+20)*(売上表!$B$1:$B$1000<=EOMONTH($H$5,-1)+20),ROW($A$1:$A$1000)),ROW(A1)),MATCH(B$14,売上表!$A$1:$I$1,0)),"")
にしてください。

後の操作方法（B15セルをフィル＆コピー）は一緒です。

どうも失礼しました。m(_ _)m

補足

おはようございます。
いつもいつも良き御指導を誠にありがとうございます。
色々と試させていただきました。

その中で私の説明不足で申し訳ありません。
というのはtom04さんの添付図でいうとB列ですが同じIDで日にちこそ違え別の日に商いが発生します。
そこでお許しいただきたいのですが御指導の数式を基本に下記のように変更させていただきました。
勝手ですがお許しください。

売上表
A2 =IF(B2="","",MONTH(IF(DAY(B2)<=20,EDATE(B2,1),EDATE(B2,2))))
11
J2 =IF(F2="","",TEXT(B2,"yyyy")&A2&F2)
2014110030

請求書
H3 =TEXT(I2,"yyyymm")&A2
2014110030
H5 10月21日
I2　=DATE(YEAR(H5),MONTH(H5)+1,1)
2014/11/1
B15
=IFERROR(INDEX(売上表!$A$1:$I$1000,SMALL(IF((売上表!$J$1:$J$1000=$H$3)*(売上表!$B$1:$B$1000>EOMONTH($H$5,-2)+20)*(売上表!$B$1:$B$1000<=EOMONTH($H$5,-1)+20),ROW($A$1:$A$1000)),ROW(A1)),MATCH(B$14,売上表!$A$1:$I$1,0)),"")

心苦しいですがうまくできました。
基本になる数式を教えていただいたおかげです。

回答No.5

ExcelDevTool

これを対応するための機能があります。台帳から個別文書へ出力することは
設定ファイルで線を引くだけで実現することができます。
初回の利用は難しいと思いますが、試していただければと思います。
http://superdbtool.com/blog/archives/337
ご参考まで。

お礼

ありがとうございます。
難しそうですがじっくり勉強します。

回答No.4

tom04

No.1・2です。

＞そこでお許しいただきたいのですが御指導の数式を基本に下記のように変更させていただきました。
＞勝手ですがお許しください。

全く気にする必要はありません。
一番大切なことは、こちらではそちらの配置・使用方法がまったく判りませんので
ご自身で使いやすいようにできるのが一番だと思います。
他の方の回答（数式）をそのままコピー＆ペーストして「問題解決！」と思っているより
回答した側としては逆に好ましい事だと思っています。

＞心苦しいですがうまくできました

結論として、何とかご希望通りになったのですね？
そうであれば一安心です。m(_ _)m

お礼

いつもご指導ありがとうございます。
激励のお言葉と理解しこれからも頑張ります。

急な用事ができてお礼が遅くなりました。

回答No.3

bunjii

考え方は回答No.1と同じですが数式の使い方を変えてみました。
貼付画像はExcel 2013で検証した結果ですがExcel 2007以降のバージョンで再現できるはずです。
EOMONTH関数を使うと基準日からの前後指定月数の月末を算出できるので前月21日を次のように算出します。
前月21日=EOMONTH(当月の特定日,-2)+21　（前々月の末日の21日後）
シート名：請求書
A15=IF(ROWS(A$15:A15)<=COUNTIFS(売上表!$B$2:$B$1000,"<"&$H$4,売上表!$B$2:$B$1000,">"&EOMONTH($H$4,-2)+20),ROWS(A$15:A15),"")

B15=IF($A15="","",INDEX(売上表!$B:$I,SUMPRODUCT(LARGE((売上表!$B$2:$B$1000<$H$4)*(売上表!$B$2:$B$1000>EOMONTH($H$4,-2)+20)*ROW(B$2:B$1000),COUNTIFS(売上表!$B$2:$B$1000,"<"&$H$4,売上表!$B$2:$B$1000,">"&EOMONTH($H$4,-2)+20)+1-$A15)),MATCH(B$14,売上表!$B$1:$I$1,0)))
この数式は入力後Enterキーのみの打鍵で確定しても正常に抽出できます。

B15セルをC15、D15、H15セルへコピーし、A15からH15セルを纏めて下へ必要数コピーします。
H23=SUM(H15:H22)*0.08　（消費税8%）
H24=SUM(H15:H23)　（合計）
御請求金額（消費税込）は作業用のセルで桁毎に漢数字に変換した文字列を連結して作成しました。

お礼

親切丁寧なご指導ありがとうございます。
上手くできました。

回答No.1

tom04

こんばんは！

一例です。

「売上表」Sheetの列番号が判らないので
↓の画像のような配置だとすると・・・
「売上表」SheetとSheet2（請求書）の14行目項目名は一致しているとします。

Sheet2のA15セルには
=IF(B15="","",ROW(A1))

B15セルに
=IFERROR(INDEX(売上表!$A$1:$I$1000,SMALL(IF((売上表!$G$1:$G$1000=$H$3)*(売上表!$B$1:$B$1000>EOMONTH($H$5,-2)+20)*(売上表!$B$1:$B$1000<=EOMONTH($H$5,-1)+20),ROW($A$1:$A$1000)),ROW(A1)),MATCH(B$14,売上表!$A$1:$I$1,0)),"")
これは配列数式になりますので、Ctrl＋Shift＋Enterで確定！

B15セルをE15セルまでフィル＆コピー
B15セルの表示形式を好みの「日付」にします。

最後にA15～E15セルを範囲指定　→　E15セルのフィルハンドルで下へコピー！
これで画像のような感じになります。m(_ _)m