[[20031012231218]] 『検収チェックリストの作成』(うえさん) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『検収チェックリストの作成』(うえさん)

以前お世話になりありがとうございます

今回は検収チェックリストを作成しています
ある大きい取引先で月に2,3度の割合で伝票チェックと検収チェックが経理から依頼されます。
その度に注文書と納品書・受領書のチェックで半日から長い時は夜遅くまで掛かってしまいます。
そこでチェックリストを作ろうと思います。
注文番号や工事番号、商品名、担当者、数量、日付などいろんな所から検索出来る様にデータ入力しようと思ってます。
私は入力が苦ではないので構いませんが、PCに不慣れな方も入力するので、少しでも簡単に入力出来る様に、ドロップダウンリストにしようと思っています。

取引先の発注担当者は決った方は6名、そこは簡単にリストに出来るのですが、その後の商品などの入力です。
商品は100種類ほどあるので、全てをリストに載せるのもそこから探すのでは意味がありません。
ただし発注担当者の商品というのは決っています。
そこでリストで発注担当者を入力すると、その方の担当の商品のみをリストに載せる方法はないでしょうか?
各担当者自体の商品はそれぞれ10〜20種類ですので、それのみをリストに載せたいと思っています。

他にその様な事が出来るいい方法を教えて頂けないでしょうか?
よろしくお願いします


 VBAなら簡単ですが、リストの範囲やシートのレイアウトが分からないと
 具体的なコードを提示することは出来ません。
 それともマクロでは、都合が悪いでしょうか?
  (INA)

 簡略して書きます。
     A     B     C      D
 1 Aさん 商品1 商品2 商品3
 2 Bさん 商品4 商品5 商品6
 3 Cさん 商品7 商品8 商品9
このようにリストを作っておきます。
A1:A3を挿入>名前>定義で仮に担当者とします。B1:D1を名前をAさんB2:D2をBさんB3:D3をCさんと名前をつけます。
A10に担当者のドロップダウンリストを出すとして入力規則でリストで元の値を=担当者とします。
B10に商品のドロップダウンリストを出すとして同じく入力規則>リストで元の値を=INDIRECT(A10)
とします。試しにこの作業をしてみて下さい。お望み通りになると思います。(ケン)

 ありがとうございました。
 理想としていた物が出来ました。
 ついでに商品に単価等のデータも入力して自動計算出来る様にもしました。
 またよろしくお願いします。(うえさん)

もう一つ教えていただきたい事があります

 このチェックリストを使って検収済のシートを作りたいと思っています。
 検収された商品の検収日を入力すると別のシートにその行ごとコピーされ、その合計を出せるようにしたいと思っています。

    A      B      C    D     E    F   G   H   I    J
  1 注文番号  発注担当者  工事番号  製品名  製品番号  数量  単価  金額  納品日  検収日
    245864      A氏    56D-568I   ABC  0586-65L  20   30  600  10/10  10/25 
    245991       B氏    59c-8800   G01  JK-1037     10     150   1500    10/9    10/25

 こんな感じの表です。特に新製品や既製の物の寸法が少しだけ違う物などが多く、その様な製品は伝票の発行が取引先会社の方で遅く、納品された後の事後処理に時間が掛かってしまいます。
 検収チェックリストを作れば少しは早く探せると思います。また折角作るのですから、検収済の物の集計もしたいと思ってます。
 上の表の検収日を入力し検収されたものだけを別シートの集計の表にコピーされる様にしたいのです。
 エクセルは多少使えるのですがVBAは詳しくは分かりません。でもVBAでなければ出来ないのであれば勉強しますので教えてください。よろしくお願いします。(うえさん)

 オートフィルタでJ列を空白以外でフィルタにかけるのではダメなのでしょうか?
ダメならもう少し考えて見ますが・・・(ケン)

 と言いながら、ついつい考えてしまいました。
チェックリストをSheet1検収済リストをSheet2とします。
Sheet2のA1からJ1はSheet1の1行目と同じく見出しを付けます。
Sheet2のA2に
 =IF(COUNT(Sheet1!$J$2:$J$100)<ROW()-1,"",INDIRECT(ADDRESS(SMALL(IF(Sheet1!$J$2:$J$100<>"",ROW($A$2:$A$100)),ROW()-1),COLUMN(),,,"Sheet1")))
としてCtrl+Shift+Enterで配列式にします。数式が{}で囲まれれば成功です。
後はJ100までコピーして表示形式を適切に設定すれば歓声(完成)です。(ケン)

 おはようございます。
 ケンさん、割り込みごめんやで。
 私もマクロ作ってみましたんで、一応書いときますわ。
 ただ問題はJ列の検収日が歯抜けで無い事が前提になっとりますんで、もし歯抜けで
 シート2に転送する事があるんやったら、チョッと手直しが必要になりまっせぇ。
 それと、これ担当者別の合計を出せと仰有ってるんとちゃいまっしゃろなぁ。
 せやけど、なんや関数だけでいけるみたいでんな。
        ほな....(おいぼれ弥太郎)
'--------------------- 
 Sub tenki()
    Dim row_b As Long
    Dim ws1 As Object, ws2 As Object, ws As Object
    Dim n As Integer, i As Integer
    Set ws1 = Worksheets("sheet1")
    Set ws2 = Worksheets("sheet2")
    Application.ScreenUpdating = False
    Set ws = ws1
    n = 11
    row_b = ws1.Range("i65536").End(xlUp).Row

    For i = RowNo(ws, n) To row_b
        ws1.Select
        If ws1.Cells(i, 11) <> "済み" And ws1.Cells(i, 10) <> "" Then
            ws1.Cells(i, 11) = "済み"
            ws1.Range(Cells(i, 1), Cells(i, 10)).Copy
            ws2.Select
            Set ws = ws2
            n = 2
            Cells(RowNo(ws, n), 1).PasteSpecial Paste:=xlValue
        End If
    Next i

    Application.CutCopyMode = xlCut
    ws2.Select
    Set ws = ws2
    n = 2
    Cells(RowNo(ws, n), 1) = "合計"
    Cells(RowNo(ws, n), 8) = Application.Sum(Range(Cells(2, 8), Cells(RowNo(ws, n) - 1, 8)))
    Application.ScreenUpdating = True
 End Sub

 '-------------------- 
 Function RowNo(ByVal ws As Object, ByVal n As Integer) As Long
    Dim i As Long
    i = 1
    Do
        i = i + 1
    Loop While ws.Cells(i, n) <> ""
    RowNo = i
 End Function

 弥太郎さんおはようございます。VBA参考にさしてもらいます。
私の数式の方はJ列が歯抜けでも抽出出来るようにして有ります。VBAの方もそうして貰えると
私の勉強になります。是非見せて下さい。
(うえさん)さんへ担当者別の合計はSUMIF関数で=SUMIF(B2:B100,"A氏",H2:H100)のようにして
合計して下さい。(ケン)

 と言いながら作ってみました。ほとんど記録した物ですが・・
 Sub tenki2()
    Dim LastR As Long
    LastR = Cells(Application.Rows.Count, "A").End(xlUp).Row
        Range("J1").Select
        Selection.AutoFilter
        Selection.AutoFilter Field:=10, Criteria1:="<>"
        Range("A1:J" & LastR).Select
        Selection.Copy
        Sheets("Sheet2").Select
        Range("A1").Select
        Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
        Sheets("Sheet1").Select
        Application.CutCopyMode = False
        Selection.AutoFilter
 End Sub
何かおかしい所とか、ここはこうした方が良いとか有りますか?ご教授お願いします。
あっ!(うえさん)さん御免なさい。この場をお借りしています。m(_ _)m (ケン)

 ケンさん、歯抜けのコード上に差し替えましたわ。コレやとどっちでもいけますさかい
 この方が良かったっちゅう事になりまんなぁ。ご指摘おおきに。
 ほんで、まあ、早々とマクロ組んでもて...。早速勉強させて貰いま。
         (おいぼれや太郎)

 弥太郎さん、有難う御座います。私こそ勉強指して貰いました。(ケン)

 ちょっとケンさんにお尋ねしたいのですが、

 =IF(COUNT(Sheet1!$J$2:$J$100)<ROW()-1,"",INDIRECT(ADDRESS(SMALL(IF(Sheet1!
 $J$2:$J$100<>"",ROW($A$2:$A$100)),ROW()-1),COLUMN(),,,"Sheet1")))

 の数式のうち、small のところは、ケンさんの発明ですか? ぼくは他人の書いたもの
 はほとんど見ないものですから、これがケンさんの発明か、または、結構一般化した
 テクニックなのか、わかりません。もし、構わなかったらどちらか教えて下さい。

 というのも、これはすごい発想ですね。ぼくはこんなのは知らなかった。これはもう
 素晴らしいの一語につきます。row() を引数にして、 small をやっているところ
 です。(通りすがりの者)

 いえ。知っての通り私は正直エクセルにたずさわってから、まだ日が浅いです。(ここの掲示板に現れたのが
エクセルを触り初めです。)
何度か登場している宝箱があり、その中(フォルダ)には今まで教えて頂いた事や使えるなっと
思った物は保存して有ります。それらの1つです。結構いけるなっと思いっている1つです。
(ケン)

 早速ありがとうございました。それにしても、これに目を付けて取っておいたこと
 自体も素晴らしいじゃないですか。(通りすがりの者)

 通りすがりの者さんに、そう言って頂けるとうれしいです。有難う御座います。(ケン)


 スパム広告の書き込みがあったため広告を削除

 INDIRECT関数は嫌いなので
 =IF(COUNT(Sheet1!$J$2:$J$100)<ROW(A1),"",INDEX(Sheet1!A:A,SMALL(IF(Sheet1!$J$2:$J$100<>"",ROW($A$2:$A$100)),ROW(A1))))
 配列数式です

 こちらの式は、普通にENTERで 
 =IF(COUNT(Sheet1!$J$2:$J$100)<ROW(A1),"",INDEX(Sheet1!A:A,SMALL(INDEX((Sheet1!$J$2:$J$100="")*1000+ROW($A$2:$A$100),),ROW(A1))))

 余計なお世話より

コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.