[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『管理台帳への固定セルへの入力と棚卸について』（あんぱん）
テープの台帳をもとに棚卸をしています。
現在はそれを紙出しし、手でレ点しながら見ています。
本数が肥大しつつあるのと将来的にバーコード管理したく、
いい案ありましたらどうか教えてください。
あまり知識が乏しいですが、がんばって解読したいと思います。
<現行フォーマット>
セルH セルI セルJ セルK
テープ種別 本数 テープ名 チェック欄
MO 1 媒体1001 レ
DAT 1 媒体1002 レ
MO 1 媒体1003 レ
CD 1 媒体1004
CD 1 媒体1005 レ
DVD 1 媒体1006
MO 1 媒体1007 レ
・
・
・ ↑
手動でチェック
<希望な動き> セルH セルI セルJ セルK テープ種別 本数 テープ名 チェック欄 MO 1 1001 OK DAT 1 1002 MO 1 1003 CD 1 1004 CD 1 1005 DVD 1 1006 MO 1 1007 ・ △ ・ ｜ ・ ｜ ｜ 別シート？ ｜ セルA ｜ チェック対象テープ ｜ 1001 ｜ ｜ 1001のテープを確認できれば上記、チェック対象テープ欄のセルに「1001」 と打ち込むとチェック欄に「OK」が入る。 (これはもしかするとvlookup関数が使える？)
同時にチェック対象テープ欄はクリア(またはそのまま上書き)し、
次々チェック対象テープ名を入れれるようにする。
※「Enter」を押すと、カーソルが下に下がると思いますが、
入力セルは固定したい。
そうなればだいぶ工数的に楽になるはず。
また間違えも少なくなると考えています。
余り知っていない物がかなり無理を言っているかもしれませんが
どうか、助けていただけませんでしょうか？
よろしくお願いします。
＜ 使用 Excel：Excel2013、使用 OS：Windows7 ＞
入力項目に「テープ種別」が無いみたいですけど、テープ名だけで判定していいんですか？
入力した番号(足跡)は、どこかに残すべきだと思います。 (打鍵時刻なんかもついで記録した方が良いかも。まぁ、どう足跡を残すかとの兼ね合いもありますが・・）
さもないと、ミスインプットであらぬテープにチェックが入ったと分かった時、 回復の手がかりがなくなります。(何番入れたっけ? てな事になります）
（半平太） 2016/11/26(土) 23:23
ところで、現行フォーマットと希望な動きで、テープ名が違うのですが、 「媒体1001」「1001」どっちが正しいんですか？
かたや文字データ、かたや数値。 こう云うのは明確にしておかないと間違いの元になりますけど。
（半平太） 2016/11/26(土) 23:28
早速のご指摘、ありがとうございます。
おっしゃる通りで下記に訂正いたします。
<現行フォーマット>
セルH セルI セルJ セルK
テープ種別 本数 テープ名 チェック欄
MO 1 1001 レ
DAT 1 1002 レ
MO 1 1003 レ
CD 1 1004
CD 1 1005 レ
DVD 1 1006
MO 1 1007 レ
・
・
・ ↑
手動でチェック
<希望な動き> セルH セルI セルJ セルK セルL テープ種別 本数 テープ名 チェック欄 チェック日時 MO 1 1001 OK 2016/11/27 1:15 DAT 1 1002 MO 1 1003 CD 1 1004 CD 1 1005 DVD 1 1006 MO 1 1007 ・ ・ ・
別シート？
セルA チェック対象テープ 1001 ＞入力項目に「テープ種別」が無いみたいですけど、テープ名だけで判定していいんですか？
はい。棚卸の時は、テープ名の確認のみでとりあえず考えています。
管理台帳としてはテープ種別も必要ですので、載せていますが。
＞入力した番号(足跡)は、どこかに残すべきだと思います。
＞(打鍵時刻なんかもついで記録した方が良いかも。まぁ、どう足跡を残すかとの兼ね合い
＞もありますが・・）
＞さもないと、ミスインプットであらぬテープにチェックが入ったと分かった時、
＞回復の手がかりがなくなります。(何番入れたっけ? てな事になります）
おっしゃる通りでチェックの証跡が必要でした。
入力間違うと結局最初から手戻り…。ご指摘いただき助かりました。
なお、別シートのセルAにチェック対象テープ「1001」を入力し
vlookup関数かmatch関数かでヒットしたテープ列のチェック欄にOKかを考えていました。
よくよく考えると、「1001」の後に仮に「1002」を入れた場合、
最初の「1001」分のOKが消えてしまう…。
OKが入ったところは文字に置き換えるか必要に感じました。
さらに高度になっていく。。。
調べてみます。
（あんぱん） 2016/11/27(日) 01:24
＞なお、別シートのセルAにチェック対象テープ「1001」を入力し ＞vlookup関数かmatch関数かでヒットしたテープ列のチェック欄にOKかを考えていました。 ＞よくよく考えると、「1001」の後に仮に「1002」を入れた場合、 ＞最初の「1001」分のOKが消えてしまう…。
そこはマクロで対処するから大丈夫です。(こちらで考えます）
１．あと、データ量ですけど、管理台帳は何行ありますか？ （今後の増加を見込んで、ざっくりで結構です）
２．本数のチェックはどうなっているんでしょうか？ 全部1本のままですか？
それとも、入力する都度（自動更新の都度）、本数を自動的に増やすんですか？
それとも、入力項目に本数も追加し、その本数を転記（または加算）するんですか？
私としては、以下の様な出来上がりを考えています。
[作業開始前] <管理台帳>サンプル <入力> 行 _____H_____ __I__ ____J____ _____K_____ 行 _________A_________ _____________B_____________ __C__ __D__ ____ E ____ 1 テープ種別 本数 テープ名 チェック欄 1 チェック対象テープ 更新→B2セル(番号)を右クリ 8 2 MO 1 1001 2 次入力(更新終了) 証跡 時刻 証跡クリア 3 DAT 1 1002 3 4 MO 1 1003
[1個目のテープ名(1002）を入力] <入力> 行 _________A_________ _____________B_____________ __C__ __D__ _____E_____ 1 チェック対象テープ 更新→B2セル(番号)を右クリ 8 2 1002 3 証跡 時刻 証跡クリア
[B2セルを右クリックする] <管理台帳〉 結果図 <入力 結果図> 行 _____H_____ __I__ ____J____ _____K_____ 行 _________A_________ _____________B_____________ __C__ _____D_____ ____ E ____ 1 テープ種別 本数 テープ名 チェック欄 1 チェック対象テープ 更新→B2セル(番号)を右クリ 8 2 MO 1 1001 2 次入力(更新終了) 証跡 時刻 証跡クリア 3 DAT 1 1002 ○ 3 1002 7:50:24 AM 4 MO 1 1003
・・・・・・・・・以上の繰り返し・・・・・・・・・・・・
［1003を入力した場合の結果図のみ］
<管理台帳 結果図> <入力 結果図> 行 _____H_____ __I__ ____J____ _____K_____ 行 _________A_________ _____________B_____________ __C__ _____D_____ ____ E ____ 1 テープ種別 本数 テープ名 チェック欄 1 チェック対象テープ 更新→B2セル(番号)を右クリ 8 2 MO 1 1001 2 次入力(更新終了) 証跡 時刻 証跡クリア 3 DAT 1 1002 ○ 3 1002 7:50:24 AM 4 MO 1 1003 ○ 4 1003 7:54:30 AM
[証跡が不要になったと判断したら、E2セルを右クリックする。→ C3:D3より下の記録がクリアされる］
（半平太） 2016/11/27(日) 08:05
それとも、入力確認後の「右クリック」動作は効率を落とすので、テープ名を入れた途端に、 管理台帳に反映させる方がスピーディでいいですかねぇ・・？
（半平太） 2016/11/27(日) 08:31
大変遅くなり、申し訳ございません。
＞ １．あと、データ量ですけど、管理台帳は何行ありますか？
＞ （今後の増加を見込んで、ざっくりで結構です）
ざっくりですと約3000行程度です。
※この後に約2000本ほど媒体追加があるかもしれないとは聞いていますが…。
＞ ２．本数のチェックはどうなっているんでしょうか？
＞ 全部1本のままですか？
＞ それとも、入力する都度（自動更新の都度）、本数を自動的に増やすんですか？
＞ それとも、入力項目に本数も追加し、その本数を転記（または加算）するんですか？
すべて1本単位で確認しています。棚自体もそれと同じく1棚に1本と言う感じです。
また枠は棚を設置ごとに割り当てており、1本追加したから1行追加すると言う感じでは
ありません。
棚を設置＝その棚分の枠をあらかじめ挿入しておくと言うやり方です。
[1個目のテープ名(1002）を入力] <入力> 行 _________A_________ _____________B_____________ __C__ __D__ _____E_____ 1 チェック対象テープ 更新→B2セル(番号)を右クリ 8 2 1002 3 証跡 時刻 証跡クリア
そうですね、速度的に考え
「 それとも、入力確認後の「右クリック」動作は効率を落とすので、テープ名を入れた途端に、管理台帳に反映させる方がスピーディでいいですかねぇ・・？」
これが理想です。
分かっていないのに注文し、すいません。
どうぞよろしくお願いします。
（あんぱん） 2016/12/03(土) 23:49
１．シート名が「管理台帳」および「入力」となっていることを確認してください。
２．管理台帳にデータ(サンプルまたは本番テスト用）が実在することを確認してください。
<管理台帳 データ例>
行 _G_ _____H_____ __I__ ____J____ _____K_____ 1 テープ種別 本数 テープ名 チェック欄 2 MO 1 1001 3 DAT 1 1002
３.入力シートの「シート見出し」を右クリックして、 「コードの表示(V)」を選択すると、VBE画面になります。
画面中央の白いエリアに以下のコードを貼り付けたら、F5キー(実行）を押す。 それで、入力シートのセットは終わりです。
実行は一回こっきりなので、貼り付けたコードは消去してください。
Private Sub onlyOnce()
Rem ' Range("$A$1:$E$7").Clear
Rem 結合状態を処理
Rem 生データのセルをまとめて処理
Range("A1").Value = "チェック対象テープ"
Range("B1").Value = "最終更新テープ名"
Range("C1").Value = "証跡クリア(R-C)"
Range("A2").Value = "次を入力してください"
Range("C2").Value = "証跡"
Range("D2").Value = "時刻"
Range("E2").Value = "取消時刻"
Range("B3").Value = "直前操作の取消 (R-Click)"
Range("B4").Value = "下のデータを取消(R-Click)"
Rem 数式セルをまとめて処理
Range("B2").FormulaR1C1Local = "=IFERROR(LOOKUP(10^17,C[1]),""初期状態です"")"
Range("B6").FormulaR1C1Local = "=IF(R[-1]C="""","""",IFERROR(MATCH(R[-1]C,C[1],0),""指定不可(該当なし ↑""))"
Range("B7").FormulaR1C1Local = "=MATCH(9^16,管理台帳!C[8])"
Rem 標準外書式セルをまとめて処理
Range("D:E").NumberFormatLocal = "m/d h:mm:ss;@"
Range("B6").NumberFormatLocal = """証跡該当行""0"
Range("B7").NumberFormatLocal = """対象行数 ""0"
Rem 塗りつぶしセルをまとめて処理
Range("C1,B3:B4").Interior.ColorIndex = 24
Range("A2,B5").Interior.ColorIndex = 6
End Sub
４．上記３のコードを消去したら、同じ場所に以下のコードを貼り付けてください。 貼り付け後、Altキーを押しながらとF11キーを押して、エクセル画面に戻ってください。
Const Inventory As String = "管理台帳"
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Dim rowNum As Variant
If Not Intersect(Target, Range("B3:B4,C1")) Is Nothing Then
Cancel = True
Select Case Target.Address
Case "$B$3"
If MsgBox("最終更新を元に戻します", vbOKCancel) = vbOK Then
rowNum = [IFERROR(MATCH(B2,管理台帳!J1:INDEX(管理台帳!J:J,入力!B7),0),"該当なし")]
If IsNumeric(rowNum) Then 'Sheet1 元に戻す
Sheets(Inventory).Cells(rowNum, "I").Offset(0, 2).ClearContents
Application.EnableEvents = False
Cells(Rows.Count, "C").End(xlUp).Resize(1, 2).ClearContents
Application.EnableEvents = True
End If
End If
Case "$B$4"
If MsgBox("過去データを取り消します", vbOKCancel) = vbOK Then
rowNum = [IFERROR(MATCH(B5,管理台帳!J1:INDEX(管理台帳!J:J,入力!B7),0),"該当なし")]
If IsNumeric(rowNum) Then ' 元に戻す
Sheets(Inventory).Cells(rowNum, "I").Offset(0, 2).ClearContents
If IsNumeric(Range("B6").Value) Then
Application.EnableEvents = False
Cells(Range("B6").Value, "E") = Now
Application.EnableEvents = True
End If
End If
End If
Case "$C$1" '証跡クリア
If MsgBox("証跡をクリアします", vbOKCancel) = vbOK Then
Application.EnableEvents = False
Range("C3:F60000").ClearContents
Application.EnableEvents = True
End If
End Select
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rowNum As Variant
Dim rngChaged As Range
Set rngChaged = Intersect(Range("A2"), Target)
If rngChaged Is Nothing Then
Exit Sub
Else
Set rngChaged = Range("A2")
If rngChaged.Value <> Empty Then '空白データは無視
rowNum = [IFERROR(MATCH(A2,管理台帳!J1:INDEX(管理台帳!J:J,入力!B7),0),"該当なし")]
If IsNumeric(rowNum) Then 'Sheet1 更新
Sheets(Inventory).Cells(rowNum, "I").Offset(0, 2).Value = "○"
Application.EnableEvents = False
Cells(Rows.Count, "C").End(xlUp).Offset(1).Resize(1, 2).Value _
= Array(Range("A2").Value, Now)
Range("A2").Value = "次を入力してください"
Application.EnableEvents = True
Else
Application.EnableEvents = False
Range("A2").Value = Range("A2").Value & "(該当無し)"
Application.EnableEvents = True
End If
End If
Range("A2").Select
End If
End Sub
５．使い方
(1)通常使用 入力シートのA2セルが「次を入力してください」となっていますので、そこにテープ名を入れます。 入力した途端に、「管理台帳」に○印が記入されます。
A2セルは直後に「次を入力してください」に戻りますので、連続的にデータを打ち込んでください。
(2)入力ミスをして、間違ったデータに○印が入った事に入力直後に気付いた場合 B3セルが「直前操作の取消(R-Click)」と表示されていますので、そのB3セルを右クリックします。
「最終更新を取り消すか」聞いてきますので、[OK]ボタンをクリックすれば、 「管理台帳」と「証跡」が元の状態に戻ります。
(3)入力ミスに気が付くのが遅かった場合 何番を間違ったのか調べて、B5セルにその番号を入力してください。
次に、B4セル(「下のデータを取消(R-Click)」と表示されています）を右クリックします。
「過去データを取り消しすか」聞いてきますので、[OK]ボタンをクリックすれば、 「管理台帳」は元の状態に戻ります。 ただし、「証跡」は元には戻らず、代わりに取消し欄(E列）に取消し時刻が入ります。
(4)「証跡」データが不要になった場合 C1セルを右クリックして消去してください。
<管理台帳 サンプル結果図> 行 _G_ _____H_____ __I__ ____J____ _____K_____ 1 テープ種別 本数 テープ名 チェック欄 2 MO 1 1001 ○ 3 DAT 1 1002 4 MO 1 1003 5 CD 1 1004 6 CD 1 1005 ○ 7 DVD 1 1006 8 MO 1 1007 ○
<入力 サンプル結果図> 行 __________A__________ ____________B____________ _______C_______ ______D______ ______E______ 1 チェック対象テープ 最終更新テープ名 証跡クリア(R-C) 2 次を入力してください 1005 証跡 時刻 取消時刻 3 直前操作の取消 (R-Click) 1001 12/4 11:30:27 4 下のデータを取消(R-Click) 1007 12/4 11:30:53 5 1004 1004 12/4 11:30:58 12/4 11:31:27 6 証跡該当行5 1005 12/4 11:31:03 7 対象行数 8
以上
（半平太） 2016/12/04(日) 11:39
かなり高度なロジックで色々大変でしたでしょう。
大変助かりますが、反面、今必死に読解しております。。。
丁寧な取説のおかげで動きは理解しつつあります。
が、1点教えてください。
「MATCH(9^16,管理台帳!C[8])"」の”9^16”は何を表しているのでしょうか？
動き的には入力シートのA2で入力した値を管理台帳より行数を引っ張るように
判断していますが、検索値が分かりません。
調べると否定と思えますが、前後の9、16が何を指しているのか。
※通常は検索セルを指定するのではと思っていたので。
またこちらの管理台帳の設計書を広げて見ていたのですが
将来的にはVOL名は数字だけでなく、漢字〜英数字まで割当可能と記載があり。
テスト踏まえ、試してみます。
レベル低い質問かもしれませんが、すいません。
（ためごろう） 2016/12/04(日) 21:31
＞「MATCH(9^16,管理台帳!C[8])"」の”9^16”は何を表しているのでしょうか？
まず、テープ名は「数値」との前提です。(そうじゃないことは今わかりましたけど・・）
Vol名は幾らなんでも9^16(つまり、1,853,020,188,851,840）よりは小さいハズです。
数値配列中のどの数値より大きい検索値を使って、Match関数で該当位置を調べると (第三引数を省略もしくはTrueにした場合ですが）、数値配列の最後の位置を返してくれます。
数値配列のMax+1なんてのを検索値にしても同じことが(しかも確実に）出来るのですが、 それには、一度、配列の全数値を舐めないとならない訳で、時間が掛かるのが嫌なんです(私は）。
まぁ、1回計算するだけなので、微々たるものでしょうけども・・
＞将来的にはVOL名は数字だけでなく、漢字〜英数字まで割当可能と記載があり。 ＞テスト踏まえ、試してみます。
本レスの冒頭で述べましたが、数値しか想定しておりません。 なので、私のプログラムは正常に動きません。
私は二度手間な話は好まないので、ここで降ります。
面白いテーマなので、他の回答者からレスが付くと思われます。 それまでしばらくお待ちください。
もし、レスが付きにくいと感じられたら、ここは一旦閉じて(閉じる旨は書き込んでください）、 新規に質問を立ててください。
（半平太） 2016/12/04(日) 22:25
色々ご指導、ありがとうございました。
＞ まず、テープ名は「数値」との前提です。(そうじゃないことは今わかりましたけど・・）
こちらの思慮不足でいらぬ心配おかけし申し訳ございませんでした。
＞もし、レスが付きにくいと感じられたら、ここは一旦閉じて(閉じる旨は書き込んでください）、
＞ 新規に質問を立ててください。
分かりました。こちらももう少し勉強します。
現状では、全く太刀打ちできず。レスが付くことを祈っています。
文字列に変換が自己で達成できそうになければ、ご指導通り、一旦〆
再度質問を立てたいと思います。
どうぞその時もよろしくお願いします。(かなり勝手を承知で)
（あんぱん） 2016/12/04(日) 23:06
If rngChaged.Value <> Empty Then '空白データは無視
rowNum = [IFERROR(MATCH(A2,管理台帳!J1:INDEX(管理台帳!J:J,入力!B7),0),"該当なし")]
If IsNumeric(rowNum) Then 'Sheet1 更新
Sheets(Inventory).Cells(rowNum, "I").Offset(0, 2).Value = "○"
最初のIf分より"rowNum"へ行番号をセット
後続のIf分へ渡しOffsetでその後続行に"○"をセットする。
ということは"rowNum"へ対象行を入れさえすればと思い
入力シートセルB8へ
=MATCH(A2,管理台帳!J:J)
↑
入力文字を入れるところへ変更し、
rowNum = [IFERROR(MATCH(A2,管理台帳!J1:INDEX(管理台帳!J:J,入力!B7),0),"該当なし")]
も
Set rowNum = Range("B8")へ変更。
一瞬、思うとおり動いたと思ったのもつかの間、エラー判定ができておらず、
管理台帳上あってもなくても、動いてしまう。
考えが無知であさはかかも。。。
（あんぱん） 2016/12/04(日) 23:33
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rowNum As Variant
Dim rngChaged As Range
Dim a As String
Set rngChaged = Intersect(Range("A2"), Target)
If rngChaged Is Nothing Then
Exit Sub
Else
Set rngChaged = Range("A2")
a = rngChaged
If a <> Empty Then '空白データは無視
'If rngChaged.Value <> Empty Then '空白データは無視
rowNum = [IFERROR(MATCH(A2,管理台帳!J1:INDEX(管理台帳!J:J,入力!B7),0),"該当なし")]
'Set rowNum = Range("B8")
If IsNumeric(rowNum) Then 'Sheet1 更新
Sheets(Inventory).Cells(rowNum, "I").Offset(0, 2).Value = "○"
Application.EnableEvents = False
Cells(Rows.Count, "C").End(xlUp).Offset(1).Resize(1, 2).Value _
= Array(Range("A2").Value, Now)
Range("A2").Value = "次を入力してください"
Application.EnableEvents = True
Else
Application.EnableEvents = False
Range("A2").Value = Range("A2").Value & "(該当無し)"
Application.EnableEvents = True
End If
End If
Range("A2").Select
End If
End Sub
数値に加え、文字列が判断できればと
数値→文字列へ変換する分を追加してみました。
頭でstringを宣言。
中段で数値から文字列へ変換。
これでいくと
＜テストデータ＞
テープ種別 本数 テープ名
MO 1 1001
LTO 1 test1
DAT 1 1003
LTO 1 1004
LTO 1 LTO1
MO 1 LTO2
LTO 1 LTO3
DAT 1 LTO4
LTO 1 test2
LTO 1 MO1
MO 1 test4
MO 1 1005
MO 1 テスト1 ←1のみ数値
MO 1 テスト１ ←全て全角
MO 1 不安
ここまでは半平太様のロジックで判断できました。
ただ、こんな感じで大丈夫でしょうか？
人の手をほぼ借りただけの作成のため…。
思った反応を見ると、楽しくなってきますが。
（あんぱん） 2016/12/05(月) 05:40
Sheets(Inventory).Cells(rowNum, "I").Offset(0, 2).Value = "○"についてですが
入力シートセルA2で入力した値と管理台帳シートでヒットすれば
その行(rowNum)の値よりOffsetで同行の2つ横に"○"を入力すると認識しています。
※Offsetでなぜかテープ名の1つ横に"○"がくるのですが…。
この時の"I"が何者かが分かりません。
色々WEBでも調べたのですが、通常セル指定の為、数字がくると思っているのですが。
この辺り、解説お願いできませんでしょうか。
よろしくお願いします。
（あんぱん） 2016/12/05(月) 06:03
長いトピですので、読んでいません。 最後の書き込みのみ、目に留まりましたので。
Cells は Cells(行番号,列番号) という記述の他に Cells(行番号,列記号) という書き方ができます。 ですから "I" は I列 のことです。
（β） 2016/12/05(月) 07:38
おー、本当に解読しようとしているんですね。 (社交辞令で、ただ言ってみるだけの人が多いんですけど、珍しい・・・）
他の回答者から案が出されるまで、ここでお付き合いします。
１．B7セルは管理台帳のデータ行数を求めるものです。
何故、そんなものを求めるかと言うと、出来るだけ範囲を限定したい為です。 範囲が限定されると計算負担がそれだけ減らす事ができます。
ここで、テープ名が数値とは限らなくなったので、対象を本数欄(I列)に変えれば、 この問題は解決されます。(本数は絶対に数値のハズです)
つまり、 ＞=MATCH(9^16,管理台帳!J:J)
↓ 変更 =MATCH(9^16,管理台帳!I:I)
２．B2セルは証跡の最終行、つまり最終更新データを調べるものです。 テープ名が数値とが限らなくなったので、これも変更する必要があります。
上記１と同様に考えると、検索は時刻欄(D列）にし、返すデータは証跡欄(C列）にすれば解決します。
つまり、 ＞=IFERROR(LOOKUP(10^17,C:C),"初期状態です")
↓変更 =IFERROR(LOOKUP(10^17,D:D,C:C),"初期状態です")
この二つで、全面解決しそうな気がします。
当方ではテストしておりません。 前回と同じ様なテストをまたやる気が起きないので・・
（半平太） 2016/12/05(月) 12:26
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.