明細から別表への転機について

質問

シート１には下記の表があります

　　　　　　　　　　４月　　　　　　　　　　　　　　５月・・・９月　　　１０月・・・３月
氏名　使用量累積　使用量　料金　　
Ａ　　　　１００　　　　１０　　　３０００　使用量にある一定の単価をかけて料金を出しています
Ｂ
Ｃ
（上期の場合は下期は非表示にして下期に入ったら上期は非表示にして一覧を印刷しています）

シート２には　４月分　使用量
氏名　料金
Ａ　　　３０００として
Ｂ
Ｃ

氏名はほぼ固定しており、現在毎月の料金をシート１からコピーしてシート２へ貼り付けしています。
シート２は４月分、シート３は５月分とすれば簡単なのですが、それはしたくありません。

４月にはいても５月には居ない場合もありますので空白になる場合もあります。
VBAで翌年３月の右の列にデータのある最終列を指定して
thisworkbookにRange("A1").End(xlToRight).Selectを使ったら良いかと思いやってますが
VBA習い始めでうまくいきません。
もしくは他にお勧めの方法があればご教授お願いします。

回答No.10

CC_T · ベストアンサー

> セルの書式設定では0"月分請求額"
ここの「0」は「整数の表示方法」を指定しているわけで、「平成23年・・・」と”数字でない入力”に対しては丸ごと適用されません。
書式設定を「@"月分請求額”」と文字型で指定するか、更にセルの数式に＆で”月分請求額”を追加すれば良いかと。

お礼

お陰で助かりました。
色々とどうもありがとうございました。

回答No.9

CC_T

>　COUNTIF(Sheet1!B1:AK1,"=0")
これでB1からAK1までのセルについて、ゼロの値が入っているセルの数をカウントしています。
例えば６月なら、残る７～翌年３月までの９つのセルにゼロが入っているので、
　４（月）＋１１－９＝　６　月としてます。
だから翌年１月はゼロ値のセルの数が2月と3月の２つしかないので
　４（月）＋１１－２＝　１３　月になっちゃうのですね。

やっつけ感ありありですが、とりあえず
=IF（COUNTIF(Sheet1!B1:AK1,"=0")<=2,3-COUNTIF(Sheet1!B1:AK1,"=0"),15-COUNTIF(Sheet1!B1:AK1,"=0")）
とでもすれば１～３月表示も出せるはずです。
（※IF文で場合分けして、カウント結果が２以下ならば３からその数を引いたもの、それ以外は15(=4+11）からその数を引いたものにする）

>　それから=IFERROR(VLOOKUP(H8,Sheet1!C$6:CN$6,(C$2-4)*3+5,FALSE),"")
>　と下記の式を繋ぎたいのですが、どのようにすればよいですか？
>　=JIS(TEXT(DATE(年月日!$A$1,年月日!$A$2,年月日!$A$3),ggge年　"))

「平成23年　\500」
などしたいということであれば、両者を＆でつなげば良いはずです。
=JIS(TEXT(DATE(年月日!$A$1,年月日!$A$2,年月日!$A$3),ggge年　\"))＆IFERROR(VLOOKUP(H8,Sheet1!C$6:CN$6,(C$2-4)*3+5,FALSE),"")
しかしそれでは使用料がゼロで本来空白にしたい人の部分にも平成●年というのが表示されるのでは？
さらにIF文組んで料金がゼロなら表示しない（""）とすればいいでしょうが、冗長な数式になりますね。。

お礼

あ、ごめんなさい。
これで良かったんです。
最後に
=IF(COUNTIF(水道!E86:AN86,"=0")<=2,3-COUNTIF(水道!E86:AN86,"=0"),15-COUNTIF(水道!E86:AN86,"=0"))にJIS関数をくっつけたいんですが、
=JIS(IF(COUNTIF(水道!E86:AN86,"=0")<=2,3-COUNTIF(水道!E86:AN86,"=0"),15-COUNTIF(水道!E86:AN86,"=0")))としますが数字しか表示しません
これだけ、お願いします。

補足

解答ありがとうございます。
成る程、そういうことだったんですね。
ホント、素晴らしいですね。

それで、一応出来ることは出来たんですが、
接続の相手を間違ってました。
したいのは、=JIS(TEXT(DATE(年月日!$A$1,年月日!$A$2,年月日!$A$3),"ggge年　"))
と=IF（COUNTIF(Sheet1!B1:AK1,"=0")<=2,3-COUNTIF(Sheet1!B1:AK1,"=0"),15-COUNTIF(Sheet1!B1:AK1,"=0")）との接続でした。
つまり、sheet2で表題として平成２３年　月分請求額・・・とたいのです。
接続は&出来たのですが、現在平成２３年９とまでしか表示していません。

現在、=JIS(TEXT(DATE(年月日!$A$1,年月日!$A$2,年月日!$A$3),"ggge年　"))&IF(COUNTIF(水道!E86:AN86,"=0")<=2,3-COUNTIF(水道!E86:AN86,"=0"),15-COUNTIF(水道!E86:AN86,"=0"))となってます。で、セルの書式設定では0"月分請求額"なっています。
どうやっても表示しません、宜しくお願いします。

回答No.8

CC_T

>　ところで、=4+11-COUNTIF(Sheet1!B1:AK1,"=0")
>　これで何故合計の入ってる月が表示されますか？4+11にしている意味は？

”４”は4月のデータが入っているセル位置、すなわち初期値ですね。、
これにゼロ値カウントの結果（月が進むほどに減っていくので、「11」か月からゼロ値を引いたもの）を
足しているわけです。
あれ、そうすると翌年分が１３月などになっちゃうか？　(^^;しまった。
料金値と同じく、「月」の値が入っているセルを見に行った方が良さそうですね。

>　とこの=IFERROR(VLOOKUP(H8,Sheet1!C$6:CN$6,(C$2-4)*3+5,FALSE),"")
>　でc2から4引いて、*3+5にしている意味は？+5は右に５移動でしょうか？
*3は月ごとの移動、+5は初期値（4月のデータが5列目に入っているから）ですね。

補足

うーん、難しいですねえ、良くわかりません。
=4、列のことでしょうか？こちらはA列、B列は管理ナンバーと番号を振っていますので
C列が氏名、D列が前年度３月末のデータ、E使用量累計、F使用量、G料金となってるんですが、
=4で良いのでしょうか？とういかそれでちゃんと表示されてるからあってるんでしょうね？

ゼロ値カウントの結果（月が進むほどに減っていくので、「11」か月からゼロ値を引いたもの）を
足しているわけです。
これが何の事かわからないです。
ゼロ値カウント？11ヶ月からゼロ値を引いたものって？

あれ、そうすると翌年分が１３月などになっちゃうか？　(^^;しまった。
そうですね、１３月になっちゃいます。

料金値と同じく、「月」の値が入っているセルを見に行った方が良さそうですね。
ちなみにどうすればよいですか？

それから=IFERROR(VLOOKUP(H8,Sheet1!C$6:CN$6,(C$2-4)*3+5,FALSE),"")
と下記の式を繋ぎたいのですが、どのようにすればよいですか？
=JIS(TEXT(DATE(年月日!$A$1,年月日!$A$2,年月日!$A$3),ggge年　"))
どうやっても駄目なので。。。。

宜しくお願いします。

回答No.7

MackyNo1

＞ちなみに10^10はどういう意味なんでしょうか？
調べましたがよくわかりません。

10の10乗という意味で通常入力する数字よりも大きい数字（を検索する）という意味です。
LOOKUP関数は昇順に並んだデータにおいて検索値よりも小さい値しか範囲にない場合は、より後方のデータを調べていきますが、最終的に10の10乗よりも大きい値が見つからないので、一番右のセルがヒットすることになるわけです。

＞それから当月がゼロの場合は前月の数値がそのまま表示されますが
ゼロの場合、空白にするには何か方法はないでしょうか・

例えば、データベースのすべての行でデータが入力されている最大セル数（列数）は以下の数式で表示できます。

=MAX(INDEX((Sheet1!A3:AN10<>"")*COLUMN(A3:AN10),))

したがって、その行のデータ数が上記の列数に等しくない場合には空白を表示する以下のような数式になります。

=IF(COUNTA(Sheet1!A3:AN3)=MAX(INDEX((Sheet1!$A$3:$AN$10<>"")*COLUMN(A3:AN10),)),LOOKUP(10^10,Sheet1!A3:AN3),"")

ただし、「MAX(INDEX((Sheet1!$A$3:$AN$10<>"")*COLUMN(A3:AN10),))」の部分の数式は配列数式ですのでこの数式を多用するとシートの動きが重くなりますので、この部分は上記のMAX関数をどこかのセルに入力して、そのセルを絶対参照するほうが良い場合もあります。

また、表示したい月をどこかに「6月」のように入力して、その値から抽出したい列番号を計算し（MATCH関数を利用）、その値からVLOOKUP関数やINDEX関数などで、その列のデータを取得するような方法の方が簡単な関数で対応できるのでわかりよいかもしれません。

お礼

解答ありがとうございます。
なるほどそういう意味なんですね。
わかりました、ありがとうございました。

回答No.6

CC_T

さらに補足。画像がつぶれて見えない（－－；

sheet１では1行目を作業行にして使用量の合計を入れています。
年度途中で単価変わる可能性考えたら、料金合計したほうがよかったですね。

sheet２を再添付しますが、つぶれるだろうなぁ。。
～～～　
　セルB1には =4+11-COUNTIF(Sheet1!B1:AK1,"=0")
セルの書式で、ユーザ書式「0 "月"」を設定してます。
　セルB3にあｈ =IFERROR(VLOOKUP(A3,Sheet1!A$4:AK$15,(B$1-4)*3+2,FALSE)*100,"")

Ａ列にはsheet１の氏名を持ってきています。
↑
=Sheet1!A15
名前の表示用。
～～～
以上。

お礼

こんな表迄作って頂き、画像まで添付していただき、誠に誠ににありがとうございました。
見えにくい画像を何とか見ながら、やってみました、単価は途中で変わりませんから
之でよいですよ。
所々、わからない処もありますが、出来ました。これですと、空白も表示しないしいいですね。
最終的に全部式を入れて重くなるかどうかですが。。。
いやあ、素晴らしいです。

ところで、=4+11-COUNTIF(Sheet1!B1:AK1,"=0")
これで何故合計の入ってる月が表示されますか？4+11にしている意味は？

とこの=IFERROR(VLOOKUP(H8,Sheet1!C$6:CN$6,(C$2-4)*3+5,FALSE),"")
でc2から4引いて、*3+5にしている意味は？+5は右に５移動でしょうか？
これだけ宜しくお願いします。

補足

それから=IFERROR(VLOOKUP(H8,Sheet1!C$6:CN$6,(C$2-4)*3+5,FALSE),"")
と下記の式を繋ぎたいのですが、どのようにすればよいですか？
=JIS(TEXT(DATE(年月日!$A$1,年月日!$A$2,年月日!$A$3),ggge年　"))
どうやっても駄目なので。。。。

回答No.5

CC_T

No.3補足
画像添付失敗したようです。
最添付。

回答No.4

CC_T

Range("A1").End(xlToRight).Select
ではタイトル行を見るから、翌年3月を参照しているのでは？

使用量なしの空白セルが誰のどの月に入るかわからない状況ですから、料金の全員分合計を取り、
それがゼロでない月を探す方法としたほうが良いでしょう。
シート１に作業行を設けて合計表示してもいいですし、
マクロで36（AJ列）から３（C列）までstep-3でさかのぼりながら合計がゼロにならない列を求めましょう。
その列で氏名の下までシート２に値を入れていけばOKです。
その月の料金ゼロの人を飛ばして作成することもできますね。

ちなみに、添付画像にマクロ無で実装した例を入れておきます。
参照ください。

回答No.3

MackyNo1

補足データと最初の例示データが違うようですが（補足データの翌年の3月の最終列はANではなくてAKだと思うのですが）、Sheet1の3行目（A3:AN3)に入力されている一番右のデータをSheet2に表示するだけなら以下のような関数で簡単に表示できます。

=LOOKUP(10^10,Sheet1!A3:AN3)

お礼

解答ありがとうございます。
前年繰り越しとか他の列もありますのでANになるのですよ。

あー、こんな関数もあったんですね、調べるべきでした。
ちなみに10^10はどういう意味なんでしょうか？
調べましたがよくわかりません。
それから当月がゼロの場合は前月の数値がそのまま表示されますが
ゼロの場合、空白にするには何か方法はないでしょうか・
よろしくお願いします。

回答No.2

CC_T

・・・何がしたいのか、目的がはっきりしません。補足される事をお勧めします。

sheet1に
氏名｜4月使用量累積｜4月使用量｜4月料金｜5月使用量累積｜5月使用量｜5月料金｜6月・・・
と１か月につき３つのデータが並んでいる。

これに対して、sheet2に
●月分使用量　＜-これはタイトル？
氏名　使用料金
…の一覧を入れる。

というのが作業のアウトプットでしょうか？
ちなみにsheet1では上期中は下期、下期に入ったら上期をそれぞれ非表示にするとのことですが、
それを自動化していると次半期に入る時に入力セルが表示されておらず入力できないはずでので、
そこは手動で操作されるのでしょうね・・・。

で、sheet1にデータを手入力していって、新たな月の列にデータが入力されたらそれに合わせて
sheet2のデータが自動的にその月のものに更新されるようにしたい、といのが制作マクロの目的
でしょうかね？

> VBAで翌年３月の右の列にデータのある最終列を指定して
> thisworkbookにRange("A1").End(xlToRight).Selectを使ったら良いかと
…特にこの部分がそこ依然とつながりが無く、何の操作をしたいのやらさっぱりです。
何をどうしたいのか、補足されたほうがいいですね。

あと、バージョンによってコマンドが違うものがあるので、使用されているExcelのバージョンも書いておくようにしたほうが良いですよ。

補足

解答ありがとうございます。
わかりづらくて申し訳ありません。再度説明します。

これに対して、sheet2に
●月分使用量　＜-これはタイトル？
氏名　使用料金
…の一覧を入れる。
一覧でなくて料金だけでよいです。

上期下期の非表示は手動でもよいです。

で、sheet1にデータを手入力していって、新たな月の列にデータが入力されたらそれに合わせて
sheet2のデータが自動的にその月のものに更新されるようにしたい、といのが制作マクロの目的
でしょうかね？
まさにそのとおりです。

VBAで翌年３月の右の列にデータのある最終列を指定して
> thisworkbookにRange("A1").End(xlToRight)
7039337

CC_T
・・・何がしたいのか、目的がはっきりしません。補足される事をお勧めします。

sheet1に
氏名｜4月使用量累積｜4月使用量｜4月料金｜5月使用量累積｜5月使用量｜5月料金｜6月・・・
と１か月につき３つのデータが並んでいる。

これに対して、sheet2に
●月分使用量　＜-これはタイトル？
氏名　使用料金
…の一覧を入れる。

というのが作業のアウトプットでしょうか？
ちなみにsheet1では上期中は下期、下期に入ったら上期をそれぞれ非表示にするとのことですが、
それを自動化していると次半期に入る時に入力セルが表示されておらず入力できないはずでので、
そこは手動で操作されるのでしょうね・・・。

で、sheet1にデータを手入力していって、新たな月の列にデータが入力されたらそれに合わせて
sheet2のデータが自動的にその月のものに更新されるようにしたい、といのが制作マクロの目的
でしょうかね？

> VBAで翌年３月の右の列にデータのある最終列を指定して
> thisworkbookにRange("A1").End(xlToRight).Selectを使ったら良いかと
…特にこの部分がそこ依然とつながりが無く、何の操作をしたいのやらさっぱりです。
ANが最終列となりますのでA1列から最終列を検索してそのデータをAOに置いて
sheet2に=sfeet1!AO3とかとすればよいかと思いました。

エクセルは２００７です。
宜しくお願いします。

回答No.1

MackyNo1

具体的に、どのようなことがしたいのかよくわかりません。

まず、元データのレイアウトに不明なところがありますが、4月のデータはA列に氏名、B列に使用量累積、C列に使用量、D列に料金が入力されていて、5月のデータは（氏名の欄はなくて）F列に使用量累積、G列に使用量、F列に料金が入力されているのでしょうか？
その場合、「4月」などの月はどのセルに入力されているのでしょうか？

そのようなデータベースがあった場合、最終的に1つのシートに「4月」と入力すると、そのデータ(名前が記入されているデータだけ）の名前と、料金を表示したいということでしょうか？

いずれにしろ、元データのレイアウトや、最終的にご希望の処理がよくわかりませんので、具体的な回答ができませんが、どのようなことをしたいのかを（どの部分ができないのかを）具体的かつポイントを絞って質問されたほうが皆さんからの的確な回答が期待できると思います。

補足

解答ありがとうございます。言葉足らずですみませんです。
sheet１
　　　　　４月　　　　　　　５月　　　　　　　　　　　　　６月,７,８月と来年の３月迄
　　　使用量　料金　　　使用量累計　使用量　料金　使用量累計　使用量　料金
山元　　10　　1000　　　　10　　　　7　　　700　　　17　　　　2　　200
山田　　5　　　500　　　　5　　　　5　　　500　　　　10　　　3　　　300
山口　　7　　　700　　　　7　　　　3　　　300　　　10　　　　7　　　700
山根　　4　　　400　　　　4　　　　2　　　200　　　6　　　　　9　　　900

Sheet2
６月分　請求額と当月分の表を料金だけコピーして毎月更新しています
氏名　料金
山元　200
山田　300
山口　700
山根　900

毎月コピーでなく自動でシート２の料金欄に料金を入力したいのです。
sheetを12sheet作って式を入れれば簡単なのですが、他にも計算させているsheetが多数あるので
それはやりたくないのです。
最終列がANなのでAOにデータのある最終列を検索したデータを表示させて
sheet2の料金セルに数式を入れたらどうかと思いますがどうでしょか？

宜しくお願いします。