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

Question

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

エクセル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）　　よろしくお願いいたします。

bunjii · Accepted Answer

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

MarcoRossiItaly · Answer

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

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

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

tom04 · Answer

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

tom04 · Answer

No.3です。

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

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

※　注意点として　※

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

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

keithin · Answer

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

かならず品名を入れてから分類を記入するという癖がついてるなら、簡単に数式を
Ａ２：
=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の回答は、分類は

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

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

MarcoRossiItaly · Answer

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

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

MarcoRossiItaly · Answer

★数式を使うことにより、既存データに対してまとめて付番すること自体には

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

●管理番号など 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

tom04 · Answer

こんばんは！
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

bunjii · Answer

>このようなかんじで表を作り、静岡茶が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で検証した結果です。

keithin · Answer

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

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

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

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

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

>こちらに 「お菓子 キャンディー」 が追加になるとします。

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

No.3・8です。

No.3です。

ん？

No.4 です。

★数式を使うことにより、既存データに対してまとめて付番すること自体には

この回答への補足

こんばんは！

この回答への補足

この回答への補足

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

この回答への補足

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング

>こちらに　「お菓子　キャンディー」　が追加になるとします。

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