エクセルで備品管理更新設定の仕方を教えてください

質問

どなたか教えてください＞＜

エクセル2010で備品管理票を作っています。
同じ商品には枝番をふり、新しい品が入った場合はソートで更新をすると枝番が自動的にふられる、という表を作成したいのですが方法が
わからなくこまっています。。

例：

管番　枝番　　分類　　　品名　　　　　　　　　　　　　　　賞味期限
　1　　　1　　　お茶　　　麦茶　　　　　　　　　　　　　　　　2012/3/20
　　　　　2　　　お茶　　　麦茶　　　　　　　　　　　　　　　　
　2　　　1　　　お茶　　　静岡茶
　　　　　2　　　お茶　　　静岡茶
　　　　　3　　　お茶　　　静岡茶
　3　　　1　　　ジュース　オレンジジュース
　4　　　1　　　ジュース　　アップルジュース
　5　　　1　　　お菓子　　　かりんとう
　　　　　2　　　お菓子　　　かりんとう
　6　　　1　　　レトルト　　　カレー
　6　　　2　　　レトルト　　　カレー
　6　　　3　　　レトルト　　　カレー
　6　　　4　　　レトルト　　　カレー

このようなかんじで表を作り、静岡茶が1つ増えたら静岡茶枝番3の下に入力し、ソートで更新をかけると管理番号2の枝番4と自動でふられる、新しい商品が増えた場合はたとえばやきそばと入力しソートをかけると管理番号7　枝番1　と自動的にふられる感じです。　また、各項目だけにしぼり見たいときは、フィルターがよいでしょうか？
ピボットテーブルを使用したことがないのですが、ピボットだと両方の機能を使えますでしょうか？
ちなみに、賞味期限がすぎると日付の色が変わる設定もしてあります。
ソートをかけて更新できるようにとの依頼をうけたのですが、不慣れにつき設定がうまくできません。。お知恵のあるかたぜひ教えてください（m_　_m）　　よろしくお願いいたします。

回答No.7

bunjii · ベストアンサー

>こちらに　「お菓子　キャンディー」　が追加になるとします。
>そうすると管理番号6　枝番1　となり、それ以降のもともと管理番号6だったレトルトカレーが管理番号7に更新。
>また、「お茶　静岡茶」　がひとつ増える場合は分類と品名を入力すると管理番号が2　枝番が4　と自動に更新。
>現在フィルターは全項目にかけてあり、管理番号と枝番にはフィルター設定で昇順の指定のみしてあります。
管理番号と枝番を自動で設定する訳ですからソートは別の項目で実行しないと目的に合いません。
貼付画像では分類と品名でソートする方法です。
下の空きレコード（行）へ「お菓子　キャンディー」と「お茶　静岡茶」を追記して分類と品名でソートを再実行すれば目的に合うでしょう。

お礼

bunjiiさん　ありがとうございました！
表の方は無事できあがりました！

お礼が遅くなってしまい、申し訳ありませんｍ（_　_）ｍ
アドバイス、とても参考になりました。

本当にありがとうございます。別の方のところにも書かせていただいたのですが、
番号の更新+今ある表の並び順も変えたくなかったのですが、よく考えたらはじめにアイウエオ順で
作っておけばソートをかけても並び順は変わらないなーとおもい、それで使ってみることにしました。

ご返答いただき、本当にありがとうございますｍ（_　_）ｍ

回答No.10

MarcoRossiItaly

＞「お菓子　キャンディー」が追加になるとして、……そうすると管理番号6　枝番1　となり、それ以降のもともと管理番号6だったレトルトカレーが管理番号7に更新。
　このようなことはできるでしょうか・・？

どのような規則性により「お菓子　キャンディー」は「管理番号6　枝番1」となるのか、そのロジックがしっかりしていれば、大抵の処理は可能だと思います。

ただし、データベースとしてご質問の表を整備しようとされているならば、既存データ（レトルトカレーの番号）が簡単に知らぬ間に書き換わってしまいそうなシステムを作ることは、お勧めしにくいです。No.4 にて「数式を除去しておくのが望ましい」と言っているのと、同じ理由です。

お礼

MarcoRossiItaly　さん　ご返信、ありがとうございました！
エクセルお詳しいんですね、とても参考になりました。

VBAは使い慣れていないと管理していくことが難しいとおもい表は別の方法で完成したのですが、
VBAやアドバイスいただいたこと、大変勉強になりました！

これから学んでみたいと思っていたので、これをよいきっかけにわたしもVBAをはじめてみようと思います！
お礼が遅くなってしまい、申し訳ありませんｍ（_　_）ｍ

本当に、ありがとうございました（＾＾）

回答No.9

tom04

No.3・8です。
たびたびごめんなさい。

前回の方法では追加された分類があった場合、お望み通りにならないと思います。
前回説明通りAlt＋F11キーで標準モジュールを出し、前回のコードがある場合はすべて削除して
↓のコードに変更してください。

Sub Sample2() 'この行から
Dim i As Long, k As Long, cnt As Long
Dim lastRow As Long, lastCol As Long, endRow2 As Long, endRow3 As Long, endCol As Long
Dim myRng As Range, myArea As Range
Dim wS2 As Worksheet, wS3 As Worksheet
Set wS2 = Worksheets("Sheet2")
Set wS3 = Worksheets("Sheet3")
Application.ScreenUpdating = False
wS2.Cells.Clear
With Worksheets("Sheet1")
lastRow = .Cells(Rows.Count, "A").End(xlUp).Row
lastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
Range(.Cells(1, "A"), .Cells(1, lastCol)).Copy wS2.Range("C1")
wS2.Range("A1") = "管番"
wS2.Range("B1") = "枝番"
Set myRng = Range(.Cells(2, "A"), .Cells(lastRow, lastCol))
.Range("A:A").AdvancedFilter Action:=xlFilterCopy, copytorange:=wS3.Range("A1"), unique:=True
For i = 2 To wS3.Cells(Rows.Count, "A").End(xlUp).Row
endRow2 = wS2.Cells(Rows.Count, "B").End(xlUp).Row + 1
.Range("A1").AutoFilter field:=1, Criteria1:=wS3.Cells(i, "A")
myRng.SpecialCells(xlCellTypeVisible).Copy wS3.Range("D1")
endRow3 = wS3.Cells(Rows.Count, "D").End(xlUp).Row
endCol = wS3.Cells(1, Columns.Count).End(xlToLeft).Column
Range(wS3.Cells(1, "C"), wS3.Cells(endRow3, "C")).Formula = "=MATCH(E1,Sheet1!B:B,FALSE)"
Set myArea = Range(wS3.Cells(1, "D"), wS3.Cells(endRow3, endCol))
wS3.Range("C1").CurrentRegion.Sort key1:=wS3.Range("C1"), order1:=xlAscending, Header:=xlNo
endRow2 = wS2.Cells(Rows.Count, "C").End(xlUp).Row + 1
myArea.Copy wS2.Cells(endRow2, "C")
wS3.Range("C1").CurrentRegion.Clear
wS2.Cells(endRow2, "A") = WorksheetFunction.Max(wS2.Range("A:A")) + 1
cnt = 0
For k = endRow2 To wS2.Cells(Rows.Count, "C").End(xlUp).Row
cnt = cnt + 1
wS2.Cells(k, "B") = cnt
Next k
Next i
.AutoFilterMode = False
wS3.Cells.Clear
End With
Application.ScreenUpdating = True
End Sub 'この行まで

そしてExcel画面に戻り、Sheet1のデータ変更があるたびにマクロを実行してください。
これでSheet2に管番・枝番付きで出現順にデータが表示されるはずです。
尚、Sheet1のデータはNo.3の画像のように
1行目は項目行でA2セル以降にデータがあり、管番・枝番は入力しないものとします。
（今回もSheet3を作業用のSheetとして使用しています）

どうも失礼しました。m（_ _）m

お礼

No.3・8　さん　いろいろと教えていただき、ありがとうございました！

マクロは慣れていないので、管理していくことがむずかしいと思い表はみなさんのお知恵を借りて別の方法で無事作成できたのですが、せっかくだからと思い教えていただいたマクロを使ってみたりあれこれためしてみました。

こんなに高度なことができて、すごいですね！！
とても参考になりました。

わたしも、今後勉強してみたいと思います！
お礼がおそくなってしまい、すみません（＞＜）

本当に、ありがとうございました（＾＾）

回答No.8

tom04

No.3です。

どこにコードを書くのか？は前回説明した通りです。

＞Alt＋F11キー　→　メニュー　→　挿入　→　標準モジュール　→　VBE画面のカーソルが点滅しているところに
＞↓のコードをコピー＆ペースト　→　Excel画面に戻りマクロを実行してみてください。
＞（Alt＋F8キー　→　マクロ　→　マクロ実行です。

※　注意点として　※

＞Sheet1のデータは↓の画像のように必要項目だけ入力するとします（管番　枝番　は入力しない）
データをSheet2に表示するようにしてみました。
＞尚、Sheet3を作業用のSheetとして使用していますので、Sheet3は全く使用していない状態にしておいてください。

も一緒に載せていました。
今一度試してみてください。m(_ _)m

回答No.6

keithin

ん？
＞品名+分類を入力したときに管理番号と枝番が更新

かならず品名を入れてから分類を記入するという癖がついてるなら、簡単に数式を
Ａ２：
=IF(OR(D2="",AND(C1=C2,D1=D2)),"",COUNT($A$1:A1)+1)
と変えるだけです。
どっちも記入した後全体を並べ替えるというご相談の前提でしたので、回答済みの方式でもなんら問題ないはずですが。

それともご相談からは読み取れませんが「絶対必ず品名と分類は両方とも記入する」というのが暗黙の前提なのでしたら、そのうえで「２個とも記入したら始めて計算を開始する」という事を言いたかったのでしたら
Ａ２：
=IF(OR(COUNTA(C2:D2)<2,AND(C1=C2,D1=D2)),"",COUNT($A$1:A1)+1)
Ｄ２：
=IF(COUNTA(C2:D2)<2,"",IF(A2="",SUM(B1))+1)
とかの具合に工夫してみて下さい。
ANo1の回答は、分類は

＞こちらに　「お菓子　キャンディー」　が追加になるとし
　…

前述した通り、ご相談の前提は分類＋品名を記入した後、全体を並べ替えることです。
その通り操作すれば、こちらで回答したどの数式でも欲しいが現れます。いったい何ができなくて、どうなって欲しかったのですか？
それとも最初のご相談はヤメにして、何か違う事をご質問なさっているのでしょうか。

お礼

keithin　さん　ありがとうございました！
ご返答いただいていたのにお礼が遅くなってしまい、すみません（＞＜）
いろいろためしてみて、表の方は無事にできました！
できたあとも、今回みなさんからいただいたものをヒントに試行錯誤ためしていたら、大分時間がかかってしまいました。

私がうまく伝えれていなかったのですが、やりたかったのは番号の更新のあと、並び順も元々と変わらないしたかったんです。
しかし、あれこれやるうちに一番初めにあいうえお順で表を作成しておけばソートかけても追加分が更新されるだけで並び順は変わらないと初歩的なことにきづきました（＞＜）

いろいろな方法があるのだなーと勉強になりました。
高度な技を教えていただき、本当にありがとうございました（＾＾）

回答No.5

MarcoRossiItaly

No.4 です。度々すみません。説明がちょっと舌足らずだったようなので、補足します。

No.4 の最後に載せたコードは、A、B 列への番号の記入を自動にするためのものです。ワークシートの C 列または D 列に何らかの値が記入されると、Excel が勝手に番号を計算して A、B 列に記入します。

回答No.4

MarcoRossiItaly

★数式を使うことにより、既存データに対してまとめて付番すること自体には
　全く問題がないのですが、それが終わったら、値複写で数式を除去しておくのが
　望ましいと思います。
　数式を残していると、適切でない方法で「並べ替え」（ソート）を行ったために、
　知らない間に番号が変化してしまうリスクがありそうです。
　並べ替えは行わない前提だという場合は、数式が残っていても構いません。

●管理番号など ID の列が空白になっている行を作らないことをお勧めします。
　全ての行が埋まっているほうが、絞り込みなどをするのもラクです。
　空白を含む表が欲しくなったときは、それこそピボットテーブルを
　空白のない表に適用することにより、いつでも作製できます。

●「登録日時」といった列も設けておくと、記録としての価値が高まると思います。

●ご質問の表くらいでしたら Excel で十分ですが、何万、何十万といった行数に
　なったり、プロパティ（属性、項目）の種類が何百にもなるなど、データ量が
　膨大になりそうな場合は、Access の利用も検討しましょう。

既存データにまとめて番号を振るには、次式などでパッと計算します。それができたら、A、B 列をコピーして「形式を選択して貼り付け」で「値」を指定して貼り付けます。

A2　=n(a1)+or(c1<>c2,d1<>d2)
B2　=countifs(C$1:c2,c2,D$1:d2,d2)

新しい行に新規データを追加する際は、フィルタで絞り込みをしながら記入するなどすれば正しい番号を手入力していけると思いますが、セルに数式を記入しておかずに番号の取得を自動化したいという場合は、マクロになります。シートタブを右クリック、「コードの表示」で現れるコードウィンドウに次のコードを貼り付けます。

Private Sub Worksheet_Change(ByVal Target As Range)
　　Dim i As Long, j As Long
　　If Intersect(Target, Columns("c:d")) Is Nothing Or Target.Columns.Count > 2 Or _
　　Target(1).Row = 1 Then
　　　　Exit Sub
　　End If
　　For i = 1 To Target.Count
　　　　With Target(i)
　　　　　　If Cells(.Row, "c").Value & Cells(.Row, "d").Value <> "" Then
　　　　　　　　Cells(.Row, "b").Value = WorksheetFunction.CountIfs( _
　　　　　　　　Range(Range("c1"), Cells(.Row - 1, "c")), Cells(.Row, "c"), _
　　　　　　　　Range(Range("d1"), Cells(.Row - 1, "d")), Cells(.Row, "d")) + 1
　　　　　　　　Cells(.Row, "a").ClearContents
　　　　　　　　If Cells(.Row, "b").Value = 1 Then
　　　　　　　　　　Cells(.Row, "a").Value = WorksheetFunction.Max(Range("a:a")) + 1
　　　　　　　　Else
　　　　　　　　　　For j = .Row - 1 To 1 Step -1
　　　　　　　　　　　　If Cells(.Row, "a").Value <> "" Then
　　　　　　　　　　　　　　Exit For
　　　　　　　　　　　　End If
　　　　　　　　　　　　If Cells(j, "c").Value = Cells(.Row, "c").Value And _
　　　　　　　　　　　　Cells(j, "d").Value = Cells(.Row, "d").Value Then
　　　　　　　　　　　　　　Cells(.Row, "a").Value = Cells(j, "a").Value
　　　　　　　　　　　　End If
　　　　　　　　　　Next j
　　　　　　　　End If
　　　　　　End If
　　　　End With
　　Next i
End Sub

補足

ご返信ありがとうございます！！

教えていただいた方法でやってみたのですが、
例えば

すでにあるものがふえた場合の枝番は更新されるのですが、
（例：お茶　静岡茶　を入力すると管理番号2　枝番4とう風に）

新しい項目
例えば下記でいうと

管番　枝番　　分類　　　品名　　　　　　　　　　　　　　　賞味期限
　1　　　1　　　お茶　　　麦茶　　　　　　　　　　　　　　　　2012/3/20
　　　　　2　　　お茶　　　麦茶　　　　　　　　　　　　　　　　
　2　　　1　　　お茶　　　静岡茶
　　　　　2　　　お茶　　　静岡茶
　　　　　3　　　お茶　　　静岡茶
　3　　　1　　　ジュース　オレンジジュース
　4　　　1　　　ジュース　　アップルジュース
　5　　　1　　　お菓子　　　かりんとう
　　　　　2　　　お菓子　　　かりんとう
　6　　　1　　　レトルト　　　カレー
　6　　　2　　　レトルト　　　カレー
　6　　　3　　　レトルト　　　カレー
　6　　　4　　　レトルト　　　カレー

　「お菓子　キャンディー」　が追加になるとして、
そのさい、分るいにお菓子を入力し、品名にキャンディーを入力。
（分類と品名はセットで自分で入力します）
そうすると管理番号6　枝番1　となり、それ以降のもともと管理番号6だったレトルトカレーが管理番号7に更新。

このようなことはできるでしょうか・・？
もしわかりましたら、教えていただけたら幸いです。

回答No.3

tom04

こんばんは！
VBAでの一例です。

Sheet1のデータは↓の画像のように必要項目だけ入力するとします（管番　枝番　は入力しない）
データをSheet2に表示するようにしてみました。
尚、Sheet3を作業用のSheetとして使用していますので、Sheet3は全く使用していない状態にしておいてください。

Alt＋F11キー　→　メニュー　→　挿入　→　標準モジュール　→　VBE画面のカーソルが点滅しているところに
↓のコードをコピー＆ペースト　→　Excel画面に戻りマクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です。

Sub Sample1() 'この行から
Dim i As Long, k As Long, cnt As Long
Dim lastRow As Long, endRow As Long, lastCol As Long
Dim wS2 As Worksheet, wS3 As Worksheet
Set wS2 = Worksheets("Sheet2")
Set wS3 = Worksheets("Sheet3")
Application.ScreenUpdating = False
wS2.Cells.Clear
With Worksheets("Sheet1")
lastRow = .Cells(Rows.Count, "A").End(xlUp).Row
.Range("A:A").Insert
.Range("A1") = "ダミー"
lastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
Range(.Cells(1, "B"), .Cells(1, lastCol)).Copy wS2.Range("C1")
With Range(.Cells(2, "A"), .Cells(lastRow, "A"))
.Formula = "=B2&""_""&C2"
.Value = .Value
End With
.Range("A:A").AdvancedFilter Action:=xlFilterCopy, copytorange:= _
wS3.Range("A1"), unique:=True
wS2.Range("A1") = "管番"
wS2.Range("B1") = "枝番"
For i = 2 To wS3.Cells(Rows.Count, "A").End(xlUp).Row
.Range("A1").AutoFilter field:=1, Criteria1:=wS3.Cells(i, "A")
endRow = wS2.Cells(Rows.Count, "C").End(xlUp).Row + 1
Range(.Cells(2, "B"), .Cells(lastRow, lastCol)).SpecialCells(xlCellTypeVisible).Copy wS2.Cells(endRow, "C")
wS2.Cells(endRow, "A") = WorksheetFunction.Max(wS2.Range("A:A")) + 1
For k = endRow To wS2.Cells(Rows.Count, "C").End(xlUp).Row
cnt = cnt + 1
wS2.Cells(k, "B") = cnt
Next k
cnt = 0
Next i
wS2.Columns.AutoFit
.AutoFilterMode = False
.Range("A:A").Delete
wS3.Cells.Clear
End With
Application.ScreenUpdating = True
End Sub 'この行まで

※　Sheet1のデータは並び替えする必要はありません。
バラバラで良いのでどんどんデータを入力していきます。
出現順に表示されるはずです。

※　関数でないので、データ変更があるたびにマクロを実行する必要があります。

こんなんではどうでしょうか？m(_ _)m

補足

ありがとうございます！！

やってみたのですが、うまくできず、
こちらのコードを入力するのは、シート1～3のどちらになりますでしょうか？

全部にかけてやってみたのですが、うまく反応しませんでした。。
ご面倒でなければご返信頂けると幸いです。

よろしくお願いします。

回答No.2

bunjii

>このようなかんじで表を作り、静岡茶が1つ増えたら静岡茶枝番3の下に入力し、ソートで更新をかけると管理番号2の枝番4と自動でふられる、新しい商品が増えた場合はたとえばやきそばと入力しソートをかけると管理番号7　枝番1　と自動的にふられる感じです。
ソートはどの項目で実行しますか？
分類を優先し、品名と2つの項目でソートすると提示の並びになりませんので確認します。

サンプルデータの並びが変わっても良ければ次の数式で良いでしょう。
A2=IF(D2="","",IF(COUNTIF(D$1:D1,D2),"",MAX(A$1:A1)+1))
B2=IF(D2="","",IF(D1=D2,B1+1,1))

貼付画像はExcel 2013で検証した結果です。

補足

ありがとうございます！！　

教えていただいた関数を使い、見事に管理番号と枝番の自動更新はできたのですが、
品名+分類を入力したときに管理番号と枝番が更新されるようにしたいんです。

例えば
管番　枝番　　分類　　　品名　　　　　　　　　　　　　　　賞味期限
　1　　　1　　　お茶　　　麦茶　　　　　　　　　　　　　　　　2012/3/20
　　　　　2　　　お茶　　　麦茶　　　　　　　　　　　　　　　　
　2　　　1　　　お茶　　　静岡茶
　　　　　2　　　お茶　　　静岡茶
　　　　　3　　　お茶　　　静岡茶
　3　　　1　　　ジュース　オレンジジュース
　4　　　1　　　ジュース　　アップルジュース
　5　　　1　　　お菓子　　　かりんとう
　　　　　2　　　お菓子　　　かりんとう
　6　　　1　　　レトルト　　　カレー
　6　　　2　　　レトルト　　　カレー
　6　　　3　　　レトルト　　　カレー
　6　　　4　　　レトルト　　　カレー

こちらに　「お菓子　キャンディー」　が追加になるとします。
そのさい、分るいにお菓子を入力し、品名にキャンディーを入力します。
（分類と品名はセットで自分で入力します）
そうすると管理番号6　枝番1　となり、それ以降のもともと管理番号6だったレトルトカレーが管理番号7に更新。
また、「お茶　静岡茶」　がひとつ増える場合は分類と品名を入力すると管理番号が2　枝番が4　と自動に更新。
このようなことはできるでしょうか。

現在フィルターは全項目にかけてあり、管理番号と枝番にはフィルター設定で昇順の指定のみしてあります。
ご返信いただけたら幸いです（m_　_m）

回答No.1

keithin

並べ替えで行った方が簡単です。

Ａ２：Ｃ，Ｄが新しくなったら＋１
=IF(OR(C2="",AND(C1=C2,D1=D2)),"",COUNT($A$1:A1)+1)
以下コピー

Ｂ２：管理が空白の時に連番
=IF(D2="","",IF(A2="",SUM(B1))+1)
以下コピー

ピボットテーブルは「集計する（個数を数えたり合計したりする）機能」で、リストを並べるのに使うものではありません。

補足

ありがとうございます。

関数を使わせていただき、番号は更新されるようになったのですが、
品名+分類を入力したときに管理番号と枝番が更新されるようにしたいんです。。
上記の方にも補足させていただいたのですが

例えば
管番　枝番　　分類　　　品名　　　　　　　　　　　　　　　賞味期限
　1　　　1　　　お茶　　　麦茶　　　　　　　　　　　　　　　　2012/3/20
　　　　　2　　　お茶　　　麦茶　　　　　　　　　　　　　　　　
　2　　　1　　　お茶　　　静岡茶
　　　　　2　　　お茶　　　静岡茶
　　　　　3　　　お茶　　　静岡茶
　3　　　1　　　ジュース　オレンジジュース
　4　　　1　　　ジュース　　アップルジュース
　5　　　1　　　お菓子　　　かりんとう
　　　　　2　　　お菓子　　　かりんとう
　6　　　1　　　レトルト　　　カレー
　6　　　2　　　レトルト　　　カレー
　6　　　3　　　レトルト　　　カレー
　6　　　4　　　レトルト　　　カレー

、 こちらに　「お菓子　キャンディー」　が追加になるとし、
そのさい、分るいにお菓子を入力し、品名にキャンディーを入力します。
（分類と品名はセットで自分で入力します）
そうすると管理番号6　枝番1　となり、それ以降のもともと管理番号6だったレトルトカレーが管理番号7に更新。また、「お茶　静岡茶」　がひとつ増える場合は分類と品名を入力すると管理番号が2　枝番が4　と自動に更新。

このような方法はなにかわかりますか？

何度もすみません。
ご返答いただけたら幸いです。