『請求書作成』（会計） 請求書を作成しています。 入力用と印刷用を作成したいのですが宜しくお願いします。 印刷用シート J12に月を入力します。 E F G H I LMN O P 16 No　月　日　伝種　伝表No　金額　品番　備考　 17 1 18 2 〜 41 25 R S T U V YZAA AB AC 16 No　月　日 伝種 伝表No 金額 品番 備考 17 26 18 27 〜 41 50 Noは1から25　26から50あります。 入力用 B5に１月 B C D E F IJK L M 7 No　月　日 伝種 伝表No 金額 品番 備考 8 1 9 2 〜 32 25 O P Q R S VWX Y Z 7 No　月　日 伝種 伝表No 金額 品番 備考 8 26 9 27 〜 32 50 B38に2月　　　B71に３月 　　B104 に４月 　B137に５月　 B170 に６月 B40　O40 B73:Z98 B106:Z131 　B139:Z164 B172:Z197 〜 〜 B65 O65 　 B203に７月 　　 B236に８月 　　B269に９月 　　B302 に１０月 B205 :Z230 B238:Z263 B271:Z296 B304:Z329 B335に１１月　 B368に１２月 B337:Z362 B370:Z395 分かりずらいですが以上の範囲に入力セルがあります。 それで、印刷シートの月を変えると入力シートの月の範囲に入力された項目が表示 させたいのですが宜しくお願いします。 ---- 印刷シートに入力された月から入力シートの場所を特定するのは簡単、かつ、転記も単純なので 普通の参照式を印刷シートの各セルに埋め込めばいいと思うし、 印刷シートのJ12にいれるのが 「数字」なのか、「日付型」なのか、「文字列」なのかを明確にすればすぐに回答があると思うけど 自分でもできそうじゃないかな。 (印刷シートの月-1) * 33 + 8 が入力シートのデータ行の開始行（たぶん・・・？） 空欄だったら転記しない。 参照セルはINDEXあたりで指定かな。<==追記　Indexなんか使わなくても普通の参照でＯＫだったね。 私は面倒なのでパス。（ＶＢＡなでいいならアップしますが） ところで、入力シートの構成。一度に、複数の月の入力を行うのならこれでいいけど、１回の作業では 特定の月の入力しかしないのであれば、目的の月までシートスクロール、大変だろうなぁ。 月別にシートをわけたほうが、入力作業も簡単だと思うし、今回のテーマ、関数にしろＶＢＡにしろ すっきりできると思うよ。 ぶらっと立ち寄り。 ---- ぶらっと立ち寄りさん ＶＢＡは、分からないので関数でやりたいのですが。 J12に入るのは、月の数字（1〜12）です。 宜しくお願いします。　（会計） ---- 印刷シートのF17にこんな式を入れて、それを必要セルに縦横ひっぱってみた。 もっとスマートな式もあるだろうけど、関数は不得意だし、好きじゃなくって。 =IF(OFFSET(入力!C$1,($J$12-1)*33+8-1+ROW()-ROW(F$17),0)="","",OFFSET(入力!C$1,($J$12-1)*33+8-1+ROW()-ROW(F$17),0)) ぶらっと立ち寄り ---- ぶらっと立ち寄りさん 有り難うございます。 印刷シートの月が入力されていない時のエラー表示をさせない方法はありますか。　（会計） ---- =IF($J$12="","",IF以降の上記の式) でも、これでもJ12に文字が入ったり、0 とか、13以上が入ったり、それらもカバーすると、どんどん めんどうになるね。J12に入力規則で1〜12以外は選ばせないようにすればいいかな。 ぶらっと立ち寄り ---- ぶらっと立ち寄りさん 有り難うございます。 印刷シートJ14に合計 入力シートT5 T38 T71 T104 T137 T170 T203 T236 T269 T302 T335 T368　に合計もあります。 合計させる式は入っているのですが空白があるとエラーになるので全て入力シートから参照するには どのようにすれば良いですか。段々面倒になりすいません。 （会計） ---- 「空白があるとエラーになる合計式」って、どんな式を入力しているのでしょうか。 L17:L41の金額の合計なら、=SUM(L17:L41) で少なくとも#VALUE!エラーはなくなるけど。 　 ※こう言う場合は、具体的な式、どんなエラーが表示されるのか、ということも 書いてください。 　 ぶらっと立ち寄りさん ＞J12に文字が入ったり、0 とか、13以上が入ったり、それらもカバーすると、 目的以外の入力ですし、そのような時は印刷しないと思うので、 私なら対処せずにほうっておきます（笑） (みやほりん)(-_∂)b ---- (みやほりん)さん すみません。 金額を入れるセルが３つに別れていて、3つ合わせて１つの金額になります。 合計　J14には　=IF(SUMPRODUCT((L17:N41+Y17:AA41)*{1000000,1000,1})=0,"",(SUMPRODUCT((L17:N41+Y17:AA41)*{1000000,1000,1}))) 小計は、 Y42 =MID(TEXT($J14,"????????0"),COLUMN(A1)*3-2,3) Z42 =MID(TEXT($J14,"????????0"),COLUMN(B10)*3-2,3) AA42 =MID(TEXT($J14,"????????0"),COLUMN(C12)*3-2,3)　 こちらは、ぶらっと立ち寄りさんの式を入れればエラー表示されないので大丈夫ですが、 以上の式を修正するには質問違いになりますのでとりあえず我慢します。　　 （会計） 　 ---- なるほど、つまり、３桁ずつ、別々のセルに表示しているために、そのようにしているのですね。 次のようにも置き換えることは出来ると思われます。 =IF(SUM(L17:N41,Y17:AA41)=0,"",SUM(L17:L41,Y17:Y41)*10^6+SUM(M17:M41,Z17:Z41)*10^3+SUM(N17:N41,AA17:AA41)) (みやほりん)(-_∂)b ---- >目的以外の入力ですし、そのような時は印刷しないと思うので、 同意！！ やるとしても =IF(AND($J$12>=1,$J$12<=12),IF(OFFSET(入力!C$1,($J$12-1)*33+8-1+ROW()-ROW(F$17),0)="","",OFFSET(入力!C$1,($J$12-1)*33+8-1+ROW()-ROW(F$17),0)),"") さらに、式自体がいいかわるいか別にして（ほんとは、「ださい」） OFFSET(入力!C$1,($J$12-1)*33+8-1+ROW()-ROW(F$17),0) が２箇所も登場している。 これを、GetItemなんていう名前で登録して、それを記述してもいいかも。 この利点は、式が短くなるということより、このださい式にかわって、もっとスマートな式が提示されたら シート側を修正しないで名前に登録されている式だけを変更すれば（きっと）ＯＫ． 少なくとも、このださい式を見ながら、最初にちょっとつぶやいたIndexを使ったほうがよかったかもと 思っている。 ぶらっと立ち寄り ---- みやほりんさん、 J14に入れましたが、何も表示しません、エラーも。 ぶらっと立ち寄りさん 合計のセルに =IF($J$12="","",IF(OFFSET(明細書入力!T$1,($J$12-1)*33+8-1+ROW()-ROW(W$17),0)="","",OFFSET(明細書入力!T$1,($J$12-1)*33+8-1+ROW()-ROW(W$17),0))) 式を入れて出来ました。 >Indexを使ったほうが その場合と、 もし、入力シートを別々にしたらどのような式になるのですか？ １月〜１２月のシートにして、 １月シート B C D E F IJK L M 7 No　月　日 伝種 伝表No 金額 品番 備考 8 1 9 2 〜 32 25 O P Q R S VWX Y Z 7 No　月　日 伝種 伝表No 金額 品番 備考 8 26 9 27 〜 32 50 (会計） ---- >Indexを使ったほうが >その場合と、 >もし、入力シートを別々にしたらどのような式になるのですか？ いやぁ。とにかく関数音痴なので、他のかたがたからの回答があったらいいなぁ・・・・ 上で言った、「名前に登録して・・」なんてくだりも、実際にどうかわからないし。 入力シートを別々にした場合は、($J$12-1)*33+8-1　の計算が不要。常に一定の場所。 えっ？でも、シート名はどう指定するんだろ？？　ＶＢＡなら、ちょいちょいってなもんだけど。 Index版も含めて、ちょっと頭の体操をしてみるけど、その前にみやほりんさんあたりが、さらっと回答してくれたらいいなぁ・・。 ぶらっと立ち寄り ---- ごめん。ギブアップ！！　エクセルは超むずかしいねぇ。 なれないことはしちゃいけなかったね。 エキキスパートの皆さんからの回答があることを切に祈りながら。 おわびに（？）　ＶＢＡコードを。（使いたくないだろうけど） 印刷シートのシートタブを右クリックしてコードの表示を選んででてくるモジュールに以下を貼り付けて試してみて。 （月別シート対応版です） Private Sub Worksheet_Change(ByVal Target As Range) Dim sName As Variant Dim done As Boolean If Intersect(Target, Range("J12")) Is Nothing Then Exit Sub Application.EnableEvents = False sName = Range("J12").Value If IsNumeric(sName) Then If sName >= 1 And sName <= 12 Then sName = sName & "月" If IsObject(Evaluate(sName & "!A1")) Then done = True With Sheets(sName) Range("F17:P41").Value = .Range("C8:M32").Value Range("S17:AC41").Value = .Range("P8:Z32").Value End With End If End If End If If Not done Then MsgBox "入力された月が適切ではありません" Application.Undo End If Application.EnableEvents = True End Sub ぶらっと立ち寄り@懺悔中 ---- 素朴な疑問　何故金額欄を３つに分割しているのですか。 ---- > J14に入れましたが、何も表示しません、エラーも。 ということは、L17:N41,Y17:AA41の範囲がすべて文字列、ということですね。 　 対象範囲に数値、空白文字列、空白セルしかないという前提であれば、下記の式が有効 　 =SUM((IF(L17:N41<>"",L17:N41*1,)+IF(Y17:AA41<>"",Y17:AA41*1,))*{1000000,1000,1}) 　 上記式を確定時にShiftとCtrlキーを押しながらEnterとする配列数式。 　 あとは、入力用シートと印刷用シートの行列範囲が同じサイズなら、 入力用シートの範囲に名前定義（1月なら、"_1月"とか、"月1"とか）すると、 INDIRECT("_"&$J$12&"月") などの式で参照できます。 あとは、INDEX関数で行位置、列位置を指定するだけですから、 =IF(INDEX(INDIRECT("_"&$J$12&"月"),ROW(A1),COLUMN(A1))="","",INDEX(INDIRECT("_"&$J$12&"月"),ROW(A1),COLUMN(A1))) かな？ 　 ちなみに、私の好みだと、 入力用シートのNoは、例えば、５月のNo.3なら0503（実際の入力値503）などとしておいて、 VLOOKUP関数で検索させるかな。 J12に1〜12の数値入力とすると F17の式は=VLOOKUP($J$12,入力!$B$1:$M$395,COLUMN(B1),FALSE) などとするかな・・・。 (みやほりん)(-_∂)b ---- 横入り みやほりんさんの提案、入力シートの各月の範囲に名前をつけることや、No.を工夫する件、 シートの構成を、処理しやすくすることも、大事なことで、ある意味、これも、プログラム作成（関数処理もプログラムの一種） の一部だと思うけど、それとは別に、入力者にとっては、同じシート上で、シートをずぅぅっとスクロールさせて 目的の月の部分を捜すのも面倒だろうし、別のブロックに入力してしまうリスクも。 なので、提案としては、月ごとにシートをわける運用はいかがですかと。 ただ、そうなったとたん、そのシートをセルJ12の値からもってきて、どう関数にいれこむのか、 もう、素人には手におえなくなって。 INDIRECTをつけたりはずしたり、ずぅっと格闘してたけど、ギブアップ。 ぶらっと立ち寄り　　 ---- いろいろ、ひっかきまわして反省。 反省しつつも、くやしいのでなんとか、シート名を変数にして、各月の入力シートから印刷シートに値を転記。 なんだか、すごく長く、ダサイ式になったけど、一応、機能はするみたい。 印刷シートの　転記すべきセルに以下の式を貼り付けてみて。 =IF(AND($J$12>=1,$J$12<=12),IF(INDIRECT(ADDRESS(ROW()-9,COLUMN()-3,,,$J$12&"月"))="","",INDIRECT(ADDRESS(ROW()-9,COLUMN()-3,,,$J$12&"月"))),"") ぶらっと立ち寄り@性懲りもなく ---- みやほりんさん 　有り難うございます。 =SUM((IF(L17:N41<>"",L17:N41*1,)+IF(Y17:AA41<>"",Y17:AA41*1,))*{1000000,1000,1}) ですが、未入力の時の０を表示させないようにしたいのですが、出来ますか？ (会計） ---- ぶらっと立ち寄りさん 何度もすいません。 VBAは全然分からないので弄れるようになるまでコピーしておきます。 >=IF(AND($J$12>=1,$J$12<=12),IF(INDIRECT(ADDRESS(ROW()-9,COLUMN()-3,,,$J$12&"月"))="","",INDIRECT(ADDRESS(ROW()-9,COLUMN()-3,,,$J$12&"月"))),"") 月別にしてやってみました。出来ました。 入力シートを１年分にしたのは、このブックを長く使うためです。入力シートをコピーして名前を今年の分 なら23年に変えて増やしていく予定です。 月別シートを年に変えて利用できないですか？ 入力シートは一年分でシート名を23年、24年・・・ 印刷用のシートG12に年、J12月 入力シートの B5 B38 B71 B104 B137 B170 B203 B236 B269 B302 B335 B368が月（1〜12）です。 印刷用の年と月を変えると23年だったら２３年１月を入力すると23年シートの1月が呼び出すように。無理な事を言ってすみません。 （会計） ---- ふぅぅぅ〜。 素人の涙ぐましい努力。また長くなってしまうけど。 =IF(AND($J$12>=1,$J$12<=12,$G$12>=23,$G$12<=99),IF(INDIRECT(ADDRESS(ROW()-9+($J$12-1)*33,COLUMN()-3,,,$G$12&"年"))="","",INDIRECT(ADDRESS(ROW()-9+($J$12-1)*33,COLUMN()-3,,,$G$12&"年"))),"") ぶらっと立ち寄り@へたな鉄砲 ---- ぶらっと立ち寄りさん 大変有り難うございます。 素晴らしいです。 一つだけ出せない場所があるんですが何を変えればよいか分かりません。 合計金額の場所　印刷用　J14 に 　 入力用　T5　T38　T71　T104　T137　T170　T203　T236　T269　T302　T335　T368を 出したいのですが宜しくお願いします。　　（会計） ---- あぁ、みやほりんさんとコミュしてた件だね。全くフォローしてなかった。 このセルだけは列変位が違うので、２ヶ所ある　COLUMN()-3 を COLUMN()+10 にすればＯＫ（かな？） あまり、しつこいて嫌われるけど、やっぱり、月別シートにして各シート同じフォーマットにしたほうが 串刺し集計なんかで年度分析とか、四半期分析とか、やりやすいと思うけどなぁ。 シート名は23年6月とかにして。 ぶらっと立ち寄り ---- ぶらっと立ち寄り 大変お世話になりました。 完璧です！出来ました。 あと、０表示の件ですが、 >=SUM((IF(L17:N41<>"",L17:N41*1,)+IF(Y17:AA41<>"",Y17:AA41*1,))*{1000000,1000,1}) みやほりんさんの式に IF を使って出来ました。 ぶらっと立ち寄りさん、みやほりんさん 大変お世話になりました。有り難うございました。 (会計）