複数のワークシートの最終行データを検知する

(この記事は「とも」さんの質問コメントに対する回答です)

【質問】
エクセルの複数シートに時系列でデータが入力されています。どこまで更新されているか、それぞれのシートの該当範囲の最終行を調べ、その先頭列に入っている日付を別ブックのシートに表でまとめたいです。

【回答】
ともさん、ご質問の書き込みありがとうございます。

実際にどのような仕事をしたいのか、よく見えない点があるので、ズバリの回答になっていないかも知れませんが、ヒントとして考えていただければ幸いです。

・質問の整理

こんなふうにモデル化してみました。

【sheet1】
ワークシート1

【sheet2】
ワークシート2

【sheet3】
ワークシート3

sheet1、sheet2、sheet3 とも売上明細を入力していますが、数万行のデータ入力をして、任意の日付で、ワークシート2に切り替えて続きを入力しています。sheet2も同様に任意の日付でsheet3に続きを入力します。

何月何日までをどのワークシートに入力したのか管理したいため、データの最終行の日付を検知したいのです。

たとえば、sheet3ならば、「11月5日」です。

このとき、厄介だと思うのは、B列のデータが何行目まで入力されているのか、上から順番に見ていくのはたいへんだ、これはマクロを組むほうがいいのかな、と考えてしまいます。

−−−−質問の整理、ここまで−−−−

sheet4を挿入して、こんなワークシートを作ってみました。

ワークシート最終行データを抽出

セルC4、C5、C6には次の計算式が入っています。

  セルC4:=MAX(Sheet1!$B$4:$B$65536)
  セルC5:=MAX(Sheet2!$B$4:$B$65536)
  セルC6:=MAX(Sheet3!$B$4:$B$65536)

セルC4の式は
「sheet1のB4:B65536のデータから最大値を探せ」という意味です。「最大値」とは、時系列順に並んだ日付の最新日付です。

「B65536」はB列の最下行、Excel2007の最大行です。

ご質問のように、あくまでも、データが時系列順に入力されている
ことが条件ですが、この式で、入力されている最下段の日付を取り出すことが出来ます。

ご質問の中に「関数を使いたくない」と書いてありましたが、この程度の関数がコンピュータに負担をかけることはありません。

むしろ、マクロは意外に負担が大きいのです。

マクロを作るより、数式・関数式を駆使して考えてみてください。

それから、別のbookにこの管理表を作りたいと考えているようですが、もちろん、Excelでは他のbookを参照することは可能ですが、後々の管理がたいへん面倒になります。

同じbookの中で完結させるほうが絶対にいいです。


ヒントになりますでしょうか?

参考にして、是非いいシステムを構築してください。


質問の趣旨と食い違っていましたら、もう一度、書き込みをお願いします。

 

【2011.09追記】


「はな」さんから質問がありました。
ともさんの質問と同じように行の最終セルのデータを別のシートに抽出したいのですが、関数式でできますでしょうか?

空白のセルがあり、抽出したいデータは数字だけではありません。

エクセルのワークシート関数に、
「表の最終行の行番号を検知する」ような関数があればラクなのですが、そのような関数ははありません。

いろいろ工夫して、関数を組み合わせて検知することになります。

マクロを使うと、ラクに仕組みを構築できそうですが、
ここでは、ワークシート関数のみを使って考えてみます。

こんな表を作ってみました。

saishugyo.gif

ともさんへの回答で使った表の上部に、
最終行のデータを抽出した表を挿入しました。

各セルには次の式が入っています。
  B3 =MAX(B8:B26)
  C3 =VLOOKUP($B$3,$B$8:$G$26,2)
  D3 =VLOOKUP($B$3,$B$8:$G$26,3)
  E3 =VLOOKUP($B$3,$B$8:$G$26,4)
  F3 =VLOOKUP($B$3,$B$8:$G$26,5)
  G3 =VLOOKUP($B$3,$B$8:$G$26,6)
絶対参照($)は式のコピーの際の省力化を考えてのものです。

この式の意味は、
・B列の最大値をセルB3に表示する。
・セルB3の値をVLOOKUPで検索し、その
  2番目の値をセルC3に、
  3番目の値をセルD3に、
   ・・・ 表示します。

この表を使うことが出来る前提は、
・データの中に昇順で並んでいるデータがある、
ことです。

もしも、データが昇順で並んでいない場合は、
たとえば、A列に、単純な連番を入力するなどにより、
昇順データを作ってしまえばいいですね。

ほかにも、いろいろな方法が考えられますが、
一例として挙げてみました。


【2011.09 再度追記】

「はな」さんから質問の追記がありました。
今回使っている表は入出庫の管理用の表で、たとえば、
A列に品名が入り、B列に入庫の日付と重量、C列に出庫の日付と使用者を入力しています。(A3,4は結合して品名、B3に日付、B4に重量、C3に日付C4に使用者)
入出庫の管理なので物によっては出し入れが少ないのもあります。(A品はZ列、B品はD列)。
このときに抽出したいセルが
A品はZ3,4
B品はD3,4 のデータになります。
 
「はな」さんはこんな表を作っているのだと思います。

zaiko_n.gif
(F列からY列を非表示にしています)

「はな」さんの悩みは、
・A品はZ列まで行っちゃうし、B品は動きが無いからD列で止まっている、
 ワークシートをその都度、大幅にスクロールするのが面倒だなあ。
といったところでしょうか?

「はな」さんは、説明を省略している点もあると思いますが、
この表で在庫管理が充分にできるとは思われません。
項目が不足しているし、
エクセルを使う上で、一列の中に異なった項目があるのは、私は好みません。
たとえば、この表だと、入庫数量の合計を計算することすら、たいへん面倒です。

そこで、
私ならこんな表を作るかな、という案を考えてみました。

新しいBookを開き、
「sheet2」にこんな表を作りました。

zaiko_a.gif

このシステムでは、1品名で1枚のワークシートを使います。
セルF2には品名「A」が入力され、
ワークシート名も「A」と変更します。

表はセルB8からF1000までの大きな表になっています。
画面は13行目から998行目を非表示にしています。

また、8行目と9行目の間に横線がありますが、
これは、8行目までを【ウィンドウ枠の固定】としているための線です。

したがって、この表では、表の下部、たとえば900行目を表示していても、
1行目から8行目は、常に画面表示されることになります。

この表に、新しいデータを入力します。

B列からF列までは入力列です。
セルF9には次の式が入っています。
 =D9-E9
セルF10の式は、
 =IF(B10="","",F9+D10-E10)
セルF11には、
 =IF(B11="","",F10+D11-E11)
以下、行番号が一つずつ増えながら、表全体(F1000まで)に式を入力しておきます。

式の中で、
 IF(B10="","",
は日付の欄に何も入力されていない場合は、何も表示するな、という式です。
(B品の表で再度説明します)


そして、
セルB5からF6に作っている表は、
入力されたデータの最終行を表示するための表です。

データの最終行を検索するためのキーは、
前回の回答でも示したように、日付を使っています。
データを入力するのは、当然、時系列によるものと考えたからです。

6行目には次の式が入力されています。

 B6: =MAX(B9:B10000)
 C6: =VLOOKUP($B$6,$B$9:$F$1000,2)
 D6: =VLOOKUP($B$6,$B$9:$F$1000,3)
 E6: =VLOOKUP($B$6,$B$9:$F$1000,4)
 F6: =VLOOKUP($B$6,$B$9:$F$1000,5)
C6〜F6の式で、異なっているのは、2,3,4,5のところだけです。
$の活用で、コピペを使って、ラクに式を作ることが出来ます。

こうしておけば、
どのワークシートでも、
6行目に最新のデータ(最後の行のデータ)が表示されていることになります。


ここまでできたら、
新しい「sheet3」にワークシート全体のコピーを作って、
ワークシート名を「B」に変更します。

「A」品のデータが入っていますので、入力されている式は消さないように注意して、
「B」品のデータを入力します。

出来上がり例です。

zaiko_b.gif

セルF12〜F1000にも式が設定されています。
セルF12の式は、
 =IF(B12="","",F11+D12-E12)

現在庫の計算ですから、
 =F11+D12-E12 でも正しい結果が得られるのですが、
入庫・出庫に数字が入力されていないとき、
F12からF1000には「0」(ゼロ)が表示されてしまいます。

「0」が邪魔ですので、
“日付が入力されていなければ、何も表示しない、
 日付が入力されたら、在庫の計算をしなさい”
という意味の式になっています。


こんなふうに、品名の数だけ、ワークシートを増やします。

さて、仕上げです。

品名が多くなってくると、ワークシートが増えて、管理がたいへんになってきます。

そこで、「sheet1」にこんな表を作りました。

zaiko_s.gif

シート名は「総括」にしました。

それぞれのワークシートから、現在庫とその日付だけを一覧にしています。
  セルC5=A!$F$6  セルD5=A!$B$6
  セルC6=B!$F$6  セルD6=B!$B$6

品目欄の「A」や「B」の文字をクリックすると、
それぞれのワークシートに移動するように、
ブック内の「リンク」を設定しています。

各品名のワークシートにも、この「総括」に戻って来るリンクを設定しておけば、
品名が増えて、ワークシートが増えても、使いやすいシステムになると思います。


まわりくどい説明の個所もありますが、
実際に表を作ってみると理解できると思います。

このまま使えないかもしれませんが、
若干のアイディアは参考になると思います。


【2011.09.30 再々度追記】

「はな」さんから質問の追記がありました。

「図書館の本の貸し出し表」なんですね、
たしかに、よく読まれる本や、いつも本棚にある本などがあって、
記載される貸出・返却記録のデータ数がまったく違いますよね。

Vectorなどには、「図書管理システム」が無償でダウンロードできるようになっています。
VBA・マクロを使ったシステムで、
バーコードリーダーを使えるほどの本格的なシステムもあります。

そこまでは必要なく、シンプルなもので、
後々の拡張やメンテナンスを考えると、自分なりに作るほうがいいと思うなら、
是非、自分で考えてみてください。
あれこれ考えて、システムを作りあげるのも楽しいですよ!

さて、
リンク」についてです。

zaiko_link.gif

この図は、セルB5にリンクを貼るために、
セルB5を右クリックして「ハイパーリンク」をクリックし、
「ハイパーリンクの挿入」のダイアログボックスを表示し、
さらに、左側の「リンク先:」から
「このドキュメント内(A)」を選んだ状態です。

右には、このブック内のワークシートの一覧が表示されますから、
「A」のワークシートを選び、右下のOKボタンをクリックして、ダイアログボックスを閉じると、リンク設定完了です。

総括ワークシートのセルB5の「A」の文字にはリンクが設定されていることを示すアンダーラインが引かれ、文字色が青になります。
セルB5「A」をクリックすると、リンク先のワークシートAが表示されます。

実際にやってみると、意外に簡単です。

  
この記事へのコメント
どちらで質問をしていいのかわからなかったので、こちらから質問をさせていただきます。

ともさんの質問と同じように行の最終セルのデータを別のシートに抽出したいのですが、関数式でできますでしょうか?

空白のセルがあり、抽出したいデータは数字だけではありません。


よろしくお願いします。
Posted by はな at 2011年09月02日 16:51
はなさん、質問の書き込み、ありがとうございます。
どんな表を作っているのか、よくわからないのですが、簡単なモデルを考えて、本文に追記しておきました。
このまま使えるとは思いませんが、何かのヒントになれば幸いです。
質問からピントが外れていましたら、また書き込みをお願いします。
Posted by 管理人GG at 2011年09月05日 16:14
早速の回答ありがとうございます。
とても参考になります。

ただ、今回使っている表は入出庫の管理用の表で、たとえば、
A列に品名が入り、B列に入庫の日付と重量、C列に出庫の日付と使用者を入力しています。(A3,4は結合して品名、B3に日付、B4に重量、C3に日付C4に使用者)
入出庫の管理なので物によっては出し入れが少ないのもあります。(A品はZ列、B品はD列)。
このときに抽出したいセルが
A品はZ3,4
B品はD3,4 のデータになります。


マクロは挑戦したことがないので関数式でお願いします。
あと、参考になる表がありましたらご紹介ください。
Posted by はな at 2011年09月12日 14:37
はなさん、質問の追記ありがとうございます。
少し具体的になったので、具体的な例を考えてみました。
とは言っても、モデル的にしか説明できませんが、参考にしていただいて、いいシステムを作ってみてください。
Posted by 管理人GG at 2011年09月16日 18:18
回答ありがとうございます。

管理人さんの説明は、わかりやすくて助かります。
私が作成中のものは、図書館の本の貸し出し表みたいなものです。

二度目の回答を参考に自分なりに考えて作ってみようと思っています。
今回は、二度目の回答にある、リンクの貼り方を詳しく教えていただきたいです。

また、参考になるものがありましたらおしえてください。
よろしくお願いします。
Posted by はな at 2011年09月28日 14:12
コメントを書く
お名前:

メールアドレス:

ホームページアドレス:

コメント:

認証コード: [必須入力]


※画像の中の文字を半角で入力してください。

この記事へのトラックバック

【関連商品】


  
×

この広告は1年以上新しい記事の投稿がないブログに表示されております。