魚市場の競り管理について、Excelを使いたいです

質問

こんばんは。早速ですが詳しい方ご回答お願いできませんでしょうか。

魚市場で競りがあるとして、私が市場主とします。
昔から使っているシステムも限界で、かといって何十万もするシステム導入はできません。
一番安価な方法として、Excelを活用して管理できないか思案しています。

内容
---------
売り手【漁師さん】
予め登録してある１０社程度（A社は01番、B社は02番と番号がふってあります）
日によって水揚げされる（ヒラメ・ハマチ・鯛・鰯・鰹等）種類や数量が異なります。

買い手【魚屋さん】
予め登録してある２０社程度（X社は101番、Y社は102番と番号がふってあります）
一品一品、競りを行うため、値付価格が異なります。

午前５時に市場が始まって、午前７時半には市場は終了します。
ちなみに競り場では、
どの売り手が、どの買い手に、いくらで何を何匹売ったか
例）A社が、X社に、1000円で、ヒラメを売った
と紙とペンで記載してあります。

市場が終了するとそれぞれ、売り手・買い手が現金精算に一人づつ精算所に来て精算します。
精算時に一人づつ、明細をプリントアウトして渡します。

たとえば、X社の精算時の明細書には日時等の他に
例）A社からヒラメを1000円で買った
B社から鰹を2000円で買った
という情報が記されています。

可能な限りExcelのみで済ませたいのですが
みなさんならどのような方法で作製しますか？
ちなみに私ですがデータベース関数等を駆使するほどExcelの経験値は高くありません。
一般関数や、vlookupまでが使用できる程度の能力です。

もしも可能でしたらわかりやすいサイトや、サンプル実例ファイルを教えていただけたらとても助かります。
お詳しい方よろしくお願い致します。

※なお、エクセルデータが不具合をおこしても、相手から損害賠償や保証を求められることはありません。わからないならしっかりとしたシステム会社に依頼するべきといった答えは望んでおりません。

回答No.7

MackyNo1 · ベストアンサー

>L列に数式を入力し、それをドロップダウンリスト化する方法なのですが、
・手打ちテンキー入力のほうが効率がいい（打ち間違いリスクはありますが速い）

テンキー入力ということはコード番号を入力するということですね。
この場合は、この数字からVLOOKUP関数で会社名を表記するまたは数式内で対応するのが簡単なように思います。

＞・基本的に売り手側は連続して獲れた海産物を競り場に出品していきます

同じデータを連続入力するなら、Ctrl+Dのショートカット操作で上と同じデータを入力するような対応が簡単かもしれません。

＞上記のため、わたくしとしましては、売り手、買い手についてはDDL化せずに進めたいと思っています。

私の提示したL列の重複のないデータを利用する主目的は、シートに入力されている業者名（当日取引のあった業者）だけをドロップダウンリストから選択できるようにすることです。

この設定によって、業者ごとの内訳を表示するシートでドロップダウンリストから業者名を選択すれば、詳細データが一発で表示でき印刷できるようにすることができます。

＞明細書発行時に、売り手、買い手を各業者正式名
例）
売り手101を、株式会社Macky
買い手501を、株式会社aiueoosaka
としてヒモ付して、会社名をシート一覧に書き出して、クリックすることで、売買内容を印刷する方式を思案しております。

(ダブル？）クリックすることで売買内容を印刷するというのは、誤操作を行う可能性もあり、あまりお勧めできません。
上に述べたように、印刷したい業者をドロップダウンリストから選択して印刷（マクロボタンで対応）するほうがこのようなケースでは適切です。

>MackyNo1さんでしたら、どのような方法を取るのか、恐れ入りますがお時間ありましたらお答えいただけましたら幸いです。

今回は１日分だけのデータで処理するシートを作成しましたが、私なら日付の列をいれて、縦方向に日々のデータを追加しておき、１か月分の集計などが簡単にできるようにします。

この場合、内訳の印刷は売買当日に行うなら、TODAY関数を併用した数式で絞り込んで、当日分だけの内訳を印刷できるような対応にします。

なお、データ範囲が大きくなると、配列数式の再計算に時間がかかりシートの動きが重くなるので、１日分の帳票印刷ブックからデータベースシートにマクロでデータを送るような対応をするほうが良いかもしれません。

お礼

お答えをいただきまして感謝致します。

＞私の提示したL列の重複のないデータを利用する主目的は、シートに入力されている業者名（当日取引のあった業者）だけをドロップダウンリストから選択できるようにすることです。この設定によって、業者ごとの内訳を表示するシートでドロップダウンリストから業者名を選択すれば、詳細データが一発で表示でき印刷できるようにすることができます。

なるほど便利ですね。

＞(ダブル？）クリックすることで売買内容を印刷するというのは、誤操作を行う可能性もあり、あまりお勧めできません。
上に述べたように、印刷したい業者をドロップダウンリストから選択して印刷（マクロボタンで対応）するほうがこのようなケースでは適切です。

MackyNo1さんならば、マクロボタンをクリックする手法を取られるということですね。

＞今回は１日分だけのデータで処理するシートを作成しましたが、私なら日付の列をいれて、縦方向に日々のデータを追加しておき、１か月分の集計などが簡単にできるようにします。

この場合、内訳の印刷は売買当日に行うなら、TODAY関数を併用した数式で絞り込んで、当日分だけの内訳を印刷できるような対応にします。

なお、データ範囲が大きくなると、配列数式の再計算に時間がかかりシートの動きが重くなるので、１日分の帳票印刷ブックからデータベースシートにマクロでデータを送るような対応をするほうが良いかもしれません。

トレーサビリティに優れていますので、教えていただいた方法をとれば抽出や、訂正が簡便に行えそうです。
いろいろおしえていただきましてありがとうございました。

Excelの関数は単純なものしかできなかったので、ガヤガヤといろんな関数でひねりを加えるのではなく
ほしい情報を一行で簡潔にまとめてしまう手法に驚きました。
今回非常に助かりました。
大切な情報をいただきまして本当に感謝しております、MackyNo1さん、ありがとうございます。

回答No.6

MackyNo1

No5の回答の補足です。

L1セルに入力する数式を提示していませんでした。
以下の式を入力して右方向に1つおよび下方向にオートフィルしてください。

=INDEX(A:A,SMALL(INDEX((MATCH($A$2:$A$100&"",$A$2:$A$20&"",0)<>ROW($2:$100)-1)*1000+ROW($2:$100),),ROW(1:1)))&""

回答No.5

MackyNo1

＞他ソリューションがありましたらご教授ください。

このようなシートでの運用で最も気を付けなければならないことは売り手や買い手のデータの入力ミス（同じデータで２つの表記をしてしまうなど）で必要なデータが抽出できなくなる可能性があることです。

それを避けるには入力規則のリストからドロップダウンリストで選択するようにします。

たとえば、現在のシートのA列やB列に入力された売り手または買い手データだけを印刷対象にしたいなら、たとえばL1セルに以下の式を入力して右方向および下方向にオートフィルすれば、重複のない売り手および買い手のリストを作成できます。

このリストを使用して、たとえば売り手を入力するセル（F1セル）で入力規則を設定し、「リスト」から以下の式を入力すれば、ドロップダウンリストから入力されている売り手を選択できるようになります。

=L$1:INDEX(L:L,SUMPRODUCT((L$1:L$100<>"")*1))

また入力シートのA列やB列に売り手や買い手をドロップダウンリストから選択できるようにするなら（過去の重複のあるデータベースがある場合）、A2：B100セルを選択して条件付き書式で上記の設定をすれば（重複のないデータリストがあるなら、その範囲をそのままリストに指定できます）、添付画像のように間違えのない入力をすることができます。

お礼

＞このようなシートでの運用で最も気を付けなければならないことは売り手や買い手のデータの入力ミス（同じデータで２つの表記をしてしまうなど）で必要なデータが抽出できなくなる可能性があることです。

本当に仰るとおりで、その部分が最も懸念している点です。

L列に数式を入力し、それをドロップダウンリスト化する方法なのですが、
・手打ちテンキー入力のほうが効率がいい（打ち間違いリスクはありますが速い）
・基本的に売り手側は連続して獲れた海産物を競り場に出品していきます

上記のため、わたくしとしましては、売り手、買い手についてはDDL化せずに進めたいと思っています。

明細書発行時に、売り手、買い手を各業者正式名
例）
売り手101を、株式会社Macky
買い手501を、株式会社aiueoosaka

としてヒモ付して、会社名をシート一覧に書き出して、クリックすることで、売買内容を印刷する方式を思案しております。

一部の方は、売りメインですが、場合によっては必要なものも買っていきます（あるいはその逆も）。

No.3にてご回答いただきました方法をメインに進めて行きたいと思いますが
その場合、F1に会社名を記入し、買いと、売りを上下に分割して、それを各シートに割り振って印刷マクロを組む方法をと考えています。

MackyNo1さんでしたら、どのような方法を取るのか、恐れ入りますがお時間ありましたらお答えいただけましたら幸いです。

回答No.4

tom04

No.2です。

＞プリントアウトする時にやや時間が掛かりそうなんですが、タイムロスを回避する方法は・・・

VBAでやってみました。
今回は↓の画像のようにSheet1の配置を変えています。
（実データは2行目から）
ただどこまでのデータが必要なのか判りませんので、勝手に前回の表通りとしました。

Sheet2を印刷用のSheetとしています。尚、Sheet3は作業用のSheetとして使用していますので
使っていない状態にしてみてください。

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

Dim i As Long, lastRow As Long, wS2 As Worksheet, wS3 As Worksheet 'この行から
Sub 売り手()
Set wS2 = Worksheets("Sheet2")
Set wS3 = Worksheets("Sheet3")
With Worksheets("Sheet1")
lastRow = .Cells(Rows.Count, "A").End(xlUp).Row
.Range("E:E").AdvancedFilter Action:=xlFilterCopy, copytorange:=wS3.Range("A1"), unique:=True
For i = 2 To wS3.Cells(Rows.Count, "A").End(xlUp).Row
wS2.Cells.Clear
.Range("A1").AutoFilter field:=5, Criteria1:=wS3.Cells(i, "A")
If .Cells(Rows.Count, "A").End(xlUp).Row > 1 Then
wS2.Range("A1") = wS3.Cells(i, "A") & "様"
wS2.Range("C1") = "支払明細"
wS2.Range("F1") = Format(Now(), "yyyy/mm/dd h:mm")
Range(.Cells(1, "A"), .Cells(lastRow, "H")).SpecialCells(xlCellTypeVisible).Copy
wS2.Range("A2").PasteSpecial Paste:=xlPasteValues
wS2.Cells(Rows.Count, "A").End(xlUp).Offset(1) = "合計"
wS2.Cells(Rows.Count, "H").End(xlUp).Offset(1) = WorksheetFunction.Sum(wS2.Range("H:H"))
wS2.Range("D:E").Delete
wS2.Range("A1").CurrentRegion.Borders.LineStyle = xlContinuous
wS2.Columns.AutoFit
wS2.PrintPreview
End If
Next i
.AutoFilterMode = False
End With
wS3.Cells.Clear
End Sub

Sub 買い手()
Set wS2 = Worksheets("Sheet2")
Set wS3 = Worksheets("Sheet3")
With Worksheets("Sheet1")
lastRow = .Cells(Rows.Count, "A").End(xlUp).Row
.Range("G:G").AdvancedFilter Action:=xlFilterCopy, copytorange:=wS3.Range("A1"), unique:=True
For i = 2 To wS3.Cells(Rows.Count, "A").End(xlUp).Row
wS2.Cells.Clear
.Range("A1").AutoFilter field:=7, Criteria1:=wS3.Cells(i, "A")
If .Cells(Rows.Count, "A").End(xlUp).Row > 1 Then
wS2.Range("A1") = wS3.Cells(i, "A") & "様"
wS2.Range("C1") = "請求明細"
wS2.Range("D1") = Format(Now(), "yyyy/mm/dd h:mm")
Range(.Cells(1, "A"), .Cells(lastRow, "H")).SpecialCells(xlCellTypeVisible).Copy
wS2.Range("A2").PasteSpecial Paste:=xlPasteValues
wS2.Cells(Rows.Count, "A").End(xlUp).Offset(1) = "合計"
wS2.Cells(Rows.Count, "H").End(xlUp).Offset(1) = WorksheetFunction.Sum(wS2.Range("H:H"))
wS2.Range("F:G").Delete
wS2.Range("A1").CurrentRegion.Borders.LineStyle = xlContinuous
wS2.Columns.AutoFit
wS2.PrintPreview
End If
Next i
.AutoFilterMode = False
End With
wS3.Cells.Clear
End Sub 'この行まで

※　印刷プレビューで止めています。
とりあえず印刷プレビューで確認し、手動で「印刷」をクリックしてください。

まずはこの程度で・・・m(_ _)m

お礼

なるほど、こういうやり方があるんですね。
ワンボタンで印刷までいけるとはありがたいです。
こういう考え方があるとは思いませんでした。
ありがとうございます！

回答No.3

MackyNo1

F１セルに売り手の名前を入力した場合（実際は入力規則でドロップダウンリストから選択するほうが簡単）、G4セルに以下の数式を入力して、セルの書式設定で表示形式をユーザー定義にして「0;;;@」右方向および下方向にオートフィルすればご希望の詳細が表示されます。

=INDEX(B:B,SMALL(INDEX(($A$2:$A$1000<>$F$1)*1000+ROW($A$2:$A$1000),),ROW(1:1)))

買い手の内訳を表示する場合は、上記の式のA列とB列を入れ替えた数式にしてください（この場合は右方向にオートフィルして２列目を削除してください。

お礼

早速ありがとうございます！
すごくスマートですね。

この後は

売り手、買い手ごとにシートを作成して
↓
プリントアウトのマクロを組む

という方法が一番楽チンな方法なんでしょうか。
他ソリューションがありましたらご教授ください。

回答No.2

tom04

こんばんは！

忙しいときの入力になると思いますので、極力クリックだけで済ます方法が良いと思います。
一案です。

↓の画像のような表を作成しておきます。
A・D・F列は入力規則の「リスト」を設定しておきます。
そうすればいちいち入力する必要はなく、リストから選択できます。

A列必要行を範囲指定　→　データ　→　データの入力規則　→　「入力値の種類」で「リスト」を選択
→　「元の値」の欄をクリック　→　「品名」のJ列必要行だけ範囲指定　→　OK
これでA列にプルダウンで「品名」が表示されますので、それをクリックするだけです。

同様にD列も「リスト」表示させます。「元の値」は「売り手」のK列を指定　→　OK
F列　→　「リスト」　→　「元の値」は「買い手」のM列を指定　→　OK
これで売り手・書いての入力は不要となります。
そしてE3セルに
=IF(D3="","",VLOOKUP(D3,K:L,2,0))
G3セルに
=IF(F3="","",VLOOKUP(F3,M:N,2,0))
H3セルに
=IF(COUNTBLANK(B3:C3),"",B3*C3)
という数式を入れそれぞれをフィルハンドルでずぃ～～～！っと下へコピー！

尚、「数量」と「単価」の列は手入力する必要があります。

ただこれでは単にデータを表示しているだけですので、
各「売り手」・「買い手」の集計が必要になると思います。

その場合はオートフィルタをしても非表示にならない行
例えば1行目のどこかに
=SUBTOTAL(9,H:H)
という数式を入れておき、2行目すべてを範囲指定　→　必要列でオートフィルタを掛けます。
これで表示されているデータだけの合計が表示されます。

※　あくまで一案ですので、
他に良い方法があればごめんなさいね。m(_ _)m

お礼

ご提案を頂きとても助かります。
なるほど、最終的にSUBTOTAL関数をつかうんですね。

プリントアウトする時にやや時間が掛かりそうなんですが、タイムロスを回避する方法はありますでしょうか。

回答No.1

yasuto07

リレーションが必要な場合は、アクセスだっけ、、、それかファイルメーカーが扱いやすいです。

リレーショナルデーターベースと言います。

一枚の伝票の項目を、10枚用意して、積み重ねて、各項目を行き来して、積算できるのです、意味わかるかな。

お礼

他のところではExcelのみで管理しているようでした。
別角度からの提案ありがとうございました。