大体でIT

-ちょっと使えるネタを紹介-

大体でIT

セル結合された残念な台帳!あなたの職場にもあるかもしれません。横並びにした台帳とExcel VBAを使ったフォーム作成で台帳の問題点を改善します。

はじめに

商品の在庫などを管理するためにExcelを使って台帳を作成する場面が仕事では多々あるかと思います。

ただ、台帳の作成の仕方があまり良くないと入力や確認に時間がかかったり、入力ミスをしてしまうこともあります。その例として少し残念な台帳とその改善方法についてご紹介します!

もしかしたらあなたの職場にもその残念な台帳があるかもしれません。その改善に役立てればと思います。

目次から見たい項目へ移動すると便利ですよ。

目次

セル結合された残念な台帳

商品の在庫管理などに使われる台帳ですが、セル結合している残念な台帳を見たことがあります。

セル結合している台帳

この台帳で管理しているものは、支店とそこで扱っている部品名、そしてその部品に内臓される内部部品といった内容なります。

残念な台帳

おそらく見やすくするためにセルを結合したんだと思いますがセル結合したセルはフィルタできない問題があります。

フィルタできない問題点

例えば、部品Aの内部部品がみたいので、部品をAでフィルタしてみると、最初の行しか表示されないんですね。残念。

残念な点

では、シンプルに結合セルをなくしてしまえばいいんじゃないか!

と思いますよね。では結合セルをなくしてみます。

結合セルをなくす案(でもやっぱり問題が。。。)

支店名はいいとして内部部品の数だけ部品名を重複して入力しないといけなくなります。

入力に時間もかかってしまいますし、入力ミスをしやすくなってしまいます。

結合セルをなくす案(でもやっぱり問題が。。。)

少ないデータならまだいいですが、おそらく職場で管理しているデータとなるとある程度大きなデータになると思います。

そこで改善策として横並びにした台帳をご提案します。

【改善策】台帳を横並びにする

内部部品だけを横並びにしてみます。

横並びにした台帳

台帳の一番最後の部分を横並びにすることで、部品名の重複がなくなりました。

Excel VBAを活用した台帳

しかし、右方向へは入力しづらいんじゃないと思われるかもしれません。

ここで、Excel VBAを使い入力と出力がしやすいフォーム作成します。

【改善策】Excel VBAを使った入出力フォーム

作成する入出力フォームは、台帳⇒フォームへ転記、またフォーム⇒台帳へ書込みの機能を作成しています。

Excel VBAを活用した入出力フォーム

修正したい台帳をダブルクリックすると1行分データを入出力フォームに転記します

また入出力フォーム上で入力した内容を台帳へ書込こめるという機能を持っています。

Excel VBAを活用した入出力フォーム

転記した台帳を変更したい場合は、黄色のセル部分を変更し、『変更』ボタンをクリックすると台帳へ書込されます。

また、新規で登録したい場合は、『新規登録』ボタンをクリックすることで、新規に台帳へ登録することができます。

これで、重複してデータを入力することなく、また、入力のしやすさもアップした台帳が出来上がります!

書いたコードは次のようになっていますので、どうぞご参考までにどうぞ。

VBAコード『Worksheet_BeforeBoubleClick』

変更したい行をダブルクリックすることでマクロを実行します。(Sheetモジュールへ記載)

  '該当シートのセルをダブルクリックすると実行するマクロです
  Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)  
    
    'シートのアクティブ状態を解除します
    Cancel = True
  
    'ダブルクリックで実行される行を設定します
    Dim aRow
    aRow = Target.Row
  
    '2行目から実行します
    If aRow >= 3 Then
      
        '行No.を保存
        Worksheets("入出力フォーム").Cells(3, "F") = aRow
      
        '入出力フォームにデータを転記します
        Call GetData
      
    End If
 
  End Sub

VBAコード『GetData』

ダブルクリックした行の台帳を入出力フォームへ転記します。(標準モジュールへ記載)

  '入出力フォームへデータを転記します
  Sub GetData()
    
    '行No.を取得します
    Dim aRow
    aRow = Worksheets("入出力フォーム").Cells(3, "F")
  
    '『DB』シートから支店名、部品名、内部部品1~6を取得します
    Dim Hozon As Variant
    With Worksheets("DB")
        Hozon = .Range(.Cells(aRow, "A"), .Cells(aRow, "H"))
    End With
  
    '『入出力フォーム』シートに支店名、部品名、内部部品1~6、行No.を転記します。  
    With Worksheets("入出力フォーム")
        .Cells(3, "C") = Hozon(1, 1) '支店名
        .Cells(4, "C") = Hozon(1, 2) '部品名
        For i = 1 To 6
            .Cells(5, "C").Offset(i, 0) = Hozon(1, 2 + i) '内部部品1~6
        Next
    End With
      
    With Worksheets("入出力フォーム")
        .Activate
    End With
  
  End Sub

VBAコード『WriteData』

入出力フォームのシートに入力した内容を部品管理台帳へ書込みするマクロです。(標準モジュールへ記載)

  '『DB』シートへ書込するマクロです
  Sub WriteData()
     
    '『入出力フォーム』シートから支店名、部品名、内部部品1~6、行No.を取得します  
    Dim Hozon As Variant
    ReDim Hozon(1 To 1, 1 To 8) As Variant
    Dim aRow
    With Worksheets("入出力フォーム")
        Hozon(1, 1) = .Cells(3, "C") '支店名
        Hozon(1, 2) = .Cells(4, "C") '部品名
        For i = 1 To 6
            Hozon(1, 2 + i) = .Cells(5, "C").Offset(i, 0) '内部部品1~6
        Next
        aRow = .Cells(3, "F") '行No.
    End With
  
    '『DB』シートへ支店名、部品名、内部部品1~6を書き込みます
    With Worksheets("DB")
        .Range(.Cells(aRow, "A"), .Cells(aRow, "H")) = Hozon
    End With
  
    '最取込をして、書き込んだデータを確認します
    Call GetData
  
  End Sub

VBAコード『WriteDataNew』

入出力フォームのシートに入力した内容を新規で部品管理台帳へ書込みするマクロです。(標準モジュールへ記載)

  '新規登録
  Sub WriteDataNew()
     
    '最後の行を取得する
    Dim LastRow
    With Worksheets("DB")
        LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
        LastRow = LastRow + 1
    End With
  
    '入出力フォームへ最終行を入力
    Worksheets("入出力フォーム").Cells(3, "F") = LastRow  
  
    '書き込み
    Call WriteData
  
    '最取込をして、書き込んだデータを確認します
    Call GetData
      
  End Sub

おわりに

Excel VBAについては多くのサイトや本などで紹介されていますが、重要なのはどのように使うかだと思います。

今回紹介した横並びにした台帳入出力フォームは簡単なExcel VBAのコードを使うだけで簡単に作成でき、そして、入力しやすさをアップさせることができます。

もしあなたの職場にも同じような台帳があってどうにかしたいなと思っているのなら、思い切って紹介した台帳を作成してもいいかもしれません。そうすれば、台帳管理に費やす時間も減るかもしれませんね。

ご参考にしていただければと思います。最後までご覧いただきありがとうございました。

関連する記事から探す

カテゴリから探す

カテゴリから見たい項目を探すと便利ですよ。

アーカイブから探す