こんばんは。早速ですが詳しい方ご回答お願いできませんでしょうか。
魚市場で競りがあるとして、私が市場主とします。
昔から使っているシステムも限界で、かといって何十万もするシステム導入はできません。
一番安価な方法として、Excelを活用して管理できないか思案しています。
内容
---------
売り手【漁師さん】
予め登録してある10社程度(A社は01番、B社は02番と番号がふってあります)
日によって水揚げされる(ヒラメ・ハマチ・鯛・鰯・鰹等)種類や数量が異なります。
買い手【魚屋さん】
予め登録してある20社程度(X社は101番、Y社は102番と番号がふってあります)
一品一品、競りを行うため、値付価格が異なります。
午前5時に市場が始まって、午前7時半には市場は終了します。
ちなみに競り場では、
どの売り手が、どの買い手に、いくらで何を何匹売ったか
例)A社が、X社に、1000円で、ヒラメを売った
と紙とペンで記載してあります。
市場が終了するとそれぞれ、売り手・買い手が現金精算に一人づつ精算所に来て精算します。
精算時に一人づつ、明細をプリントアウトして渡します。
たとえば、X社の精算時の明細書には日時等の他に
例)A社からヒラメを1000円で買った
B社から鰹を2000円で買った
という情報が記されています。
可能な限りExcelのみで済ませたいのですが
みなさんならどのような方法で作製しますか?
ちなみに私ですがデータベース関数等を駆使するほどExcelの経験値は高くありません。
一般関数や、vlookupまでが使用できる程度の能力です。
もしも可能でしたらわかりやすいサイトや、サンプル実例ファイルを教えていただけたらとても助かります。
お詳しい方よろしくお願い致します。
※なお、エクセルデータが不具合をおこしても、相手から損害賠償や保証を求められることはありません。わからないならしっかりとしたシステム会社に依頼するべきといった答えは望んでおりません。
No.7ベストアンサー
- 回答日時:
>L列に数式を入力し、それをドロップダウンリスト化する方法なのですが、
・手打ちテンキー入力のほうが効率がいい(打ち間違いリスクはありますが速い)
テンキー入力ということはコード番号を入力するということですね。
この場合は、この数字からVLOOKUP関数で会社名を表記するまたは数式内で対応するのが簡単なように思います。
>・基本的に売り手側は連続して獲れた海産物を競り場に出品していきます
同じデータを連続入力するなら、Ctrl+Dのショートカット操作で上と同じデータを入力するような対応が簡単かもしれません。
>上記のため、わたくしとしましては、売り手、買い手についてはDDL化せずに進めたいと思っています。
私の提示したL列の重複のないデータを利用する主目的は、シートに入力されている業者名(当日取引のあった業者)だけをドロップダウンリストから選択できるようにすることです。
この設定によって、業者ごとの内訳を表示するシートでドロップダウンリストから業者名を選択すれば、詳細データが一発で表示でき印刷できるようにすることができます。
>明細書発行時に、売り手、買い手を各業者正式名
例)
売り手101を、株式会社Macky
買い手501を、株式会社aiueoosaka
としてヒモ付して、会社名をシート一覧に書き出して、クリックすることで、売買内容を印刷する方式を思案しております。
(ダブル?)クリックすることで売買内容を印刷するというのは、誤操作を行う可能性もあり、あまりお勧めできません。
上に述べたように、印刷したい業者をドロップダウンリストから選択して印刷(マクロボタンで対応)するほうがこのようなケースでは適切です。
>MackyNo1さんでしたら、どのような方法を取るのか、恐れ入りますがお時間ありましたらお答えいただけましたら幸いです。
今回は1日分だけのデータで処理するシートを作成しましたが、私なら日付の列をいれて、縦方向に日々のデータを追加しておき、1か月分の集計などが簡単にできるようにします。
この場合、内訳の印刷は売買当日に行うなら、TODAY関数を併用した数式で絞り込んで、当日分だけの内訳を印刷できるような対応にします。
なお、データ範囲が大きくなると、配列数式の再計算に時間がかかりシートの動きが重くなるので、1日分の帳票印刷ブックからデータベースシートにマクロでデータを送るような対応をするほうが良いかもしれません。
お答えをいただきまして感謝致します。
>私の提示したL列の重複のないデータを利用する主目的は、シートに入力されている業者名(当日取引のあった業者)だけをドロップダウンリストから選択できるようにすることです。この設定によって、業者ごとの内訳を表示するシートでドロップダウンリストから業者名を選択すれば、詳細データが一発で表示でき印刷できるようにすることができます。
なるほど便利ですね。
>(ダブル?)クリックすることで売買内容を印刷するというのは、誤操作を行う可能性もあり、あまりお勧めできません。
上に述べたように、印刷したい業者をドロップダウンリストから選択して印刷(マクロボタンで対応)するほうがこのようなケースでは適切です。
MackyNo1さんならば、マクロボタンをクリックする手法を取られるということですね。
>今回は1日分だけのデータで処理するシートを作成しましたが、私なら日付の列をいれて、縦方向に日々のデータを追加しておき、1か月分の集計などが簡単にできるようにします。
この場合、内訳の印刷は売買当日に行うなら、TODAY関数を併用した数式で絞り込んで、当日分だけの内訳を印刷できるような対応にします。
なお、データ範囲が大きくなると、配列数式の再計算に時間がかかりシートの動きが重くなるので、1日分の帳票印刷ブックからデータベースシートにマクロでデータを送るような対応をするほうが良いかもしれません。
トレーサビリティに優れていますので、教えていただいた方法をとれば抽出や、訂正が簡便に行えそうです。
いろいろおしえていただきましてありがとうございました。
Excelの関数は単純なものしかできなかったので、ガヤガヤといろんな関数でひねりを加えるのではなく
ほしい情報を一行で簡潔にまとめてしまう手法に驚きました。
今回非常に助かりました。
大切な情報をいただきまして本当に感謝しております、MackyNo1さん、ありがとうございます。
No.6
- 回答日時:
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
- 回答日時:
>他ソリューションがありましたらご教授ください。
このようなシートでの運用で最も気を付けなければならないことは売り手や買い手のデータの入力ミス(同じデータで2つの表記をしてしまうなど)で必要なデータが抽出できなくなる可能性があることです。
それを避けるには入力規則のリストからドロップダウンリストで選択するようにします。
たとえば、現在のシートのA列やB列に入力された売り手または買い手データだけを印刷対象にしたいなら、たとえばL1セルに以下の式を入力して右方向および下方向にオートフィルすれば、重複のない売り手および買い手のリストを作成できます。
このリストを使用して、たとえば売り手を入力するセル(F1セル)で入力規則を設定し、「リスト」から以下の式を入力すれば、ドロップダウンリストから入力されている売り手を選択できるようになります。
=L$1:INDEX(L:L,SUMPRODUCT((L$1:L$100<>"")*1))
また入力シートのA列やB列に売り手や買い手をドロップダウンリストから選択できるようにするなら(過去の重複のあるデータベースがある場合)、A2:B100セルを選択して条件付き書式で上記の設定をすれば(重複のないデータリストがあるなら、その範囲をそのままリストに指定できます)、添付画像のように間違えのない入力をすることができます。
>このようなシートでの運用で最も気を付けなければならないことは売り手や買い手のデータの入力ミス(同じデータで2つの表記をしてしまうなど)で必要なデータが抽出できなくなる可能性があることです。
本当に仰るとおりで、その部分が最も懸念している点です。
L列に数式を入力し、それをドロップダウンリスト化する方法なのですが、
・手打ちテンキー入力のほうが効率がいい(打ち間違いリスクはありますが速い)
・基本的に売り手側は連続して獲れた海産物を競り場に出品していきます
上記のため、わたくしとしましては、売り手、買い手についてはDDL化せずに進めたいと思っています。
明細書発行時に、売り手、買い手を各業者正式名
例)
売り手101を、株式会社Macky
買い手501を、株式会社aiueoosaka
としてヒモ付して、会社名をシート一覧に書き出して、クリックすることで、売買内容を印刷する方式を思案しております。
一部の方は、売りメインですが、場合によっては必要なものも買っていきます(あるいはその逆も)。
No.3にてご回答いただきました方法をメインに進めて行きたいと思いますが
その場合、F1に会社名を記入し、買いと、売りを上下に分割して、それを各シートに割り振って印刷マクロを組む方法をと考えています。
MackyNo1さんでしたら、どのような方法を取るのか、恐れ入りますがお時間ありましたらお答えいただけましたら幸いです。
No.4
- 回答日時:
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
- 回答日時:
F1セルに売り手の名前を入力した場合(実際は入力規則でドロップダウンリストから選択するほうが簡単)、G4セルに以下の数式を入力して、セルの書式設定で表示形式をユーザー定義にして「0;;;@」右方向および下方向にオートフィルすればご希望の詳細が表示されます。
=INDEX(B:B,SMALL(INDEX(($A$2:$A$1000<>$F$1)*1000+ROW($A$2:$A$1000),),ROW(1:1)))
買い手の内訳を表示する場合は、上記の式のA列とB列を入れ替えた数式にしてください(この場合は右方向にオートフィルして2列目を削除してください。
早速ありがとうございます!
すごくスマートですね。
この後は
売り手、買い手ごとにシートを作成して
↓
プリントアウトのマクロを組む
という方法が一番楽チンな方法なんでしょうか。
他ソリューションがありましたらご教授ください。
No.2
- 回答日時:
こんばんは!
忙しいときの入力になると思いますので、極力クリックだけで済ます方法が良いと思います。
一案です。
↓の画像のような表を作成しておきます。
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関数をつかうんですね。
プリントアウトする時にやや時間が掛かりそうなんですが、タイムロスを回避する方法はありますでしょうか。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- その他(お金・保険・資産運用) 至急!【Wolt】各メニューの価格設定の簡単な計算方法 3 2023/03/05 11:58
- 相続・譲渡・売却 自己所有の土地建物の名義を配偶者に変更したいが可能ですか?手続き方法やかかる税金などを教えてください 3 2023/03/05 12:30
- 弁護士・行政書士・司法書士・社会保険労務士 売買契約とそれに付随する公正証書の記載について 2 2023/08/23 14:30
- 会計ソフト・業務用ソフト Excelで売上げデータの中の任意の商品の合計を出したい 3 2023/01/18 18:19
- 分譲マンション 中古マンション購入の諸費用の予算について 「日本一わかりやすいマンションの選び方がわかる本」 という 3 2022/09/14 16:51
- その他(プログラミング・Web制作) プログラムの起動、利用について、使用期間を設定する方法 3 2023/08/06 21:03
- 相続税・贈与税 相続税について質問です。相続財産の中にデパートで購入した絵や壺がある場合の申告について 4 2022/07/06 05:32
- 相続税・贈与税 相続税の、土地の計算法に関して、の質問です。 4 2022/07/05 23:12
- 相続・譲渡・売却 500万円の不動産の無償譲渡と売買どちらが得でしょうか 5 2023/05/04 16:17
- 財務・会計・経理 【仕訳】集計されて引かれる販売手数料について 5 2023/04/26 15:32
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルでの作業計算方法について
-
はがきについて。
-
エクセル 文字を増やしたい。
-
セルの内容表示が邪魔になる
-
Microsoft365に変えたのですが...
-
エクセルの計算
-
Microsoft1Officeの互換ソフト...
-
【マクロ】その時、その時で変...
-
【マクロ】読取専用のファイル...
-
エクセル初心者です 関数の入れ...
-
Excel ピボットテーブルで日付...
-
【関数】適切な文字数の数字を...
-
LOOKUP関数を使えばいいのでし...
-
Aというブックの1というシート...
-
エクセル関数を教えてください
-
Excelのチェックボックスの使い...
-
エクセル 白黒印刷で白線を印刷...
-
時間によってファイル名が変わ...
-
WPS OFFICEでの縦書きについて
-
エクセルの条件付き書式につい...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excel 2019 のピボットテーブル...
-
[関数得意な方]教えて下さい・...
-
Excelにてある膨大なデータを管...
-
[関数について]わかる方教えて...
-
Excel初心者です。 詳しい方、...
-
excelの不要な行の削除ができな...
-
エクセル関数に詳しい方教えて...
-
INDIRECTを使わず excelで複数...
-
[オートフィルタ]で抽出された...
-
エクセルの神よ、ご回答を! エ...
-
エクセル関数に詳しい方、教え...
-
各ページの1番上の表示について
-
Excelで写真のような表を作った...
-
エクセルで不等号記号(≠)が上に...
-
数学 Tan(θ)-1/Cos(θ)について...
-
Excel 2019 は、SPILL機能があ...
-
Excelで全角を半角にしたいので...
-
条件付き書式を教えてください
-
Excel フィルターを掛けた状態...
-
[オートフィルタ]の適用範囲の...
おすすめ情報