[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『在庫表を作成しています』（kiyo）
在庫表を作成しています。
A B C D E F G H I J K L M N O P Q R S T
日付 品名 ｻｲｽﾞ1 ｻｲｽﾞ2 ｻｲｽﾞ3 入庫数 出庫数 品名 ｻｲｽﾞ1 ｻｲｽﾞ2 ｻｲｽﾞ3 総入庫数 総出庫数 在庫数 品名 ｻｲｽﾞ1 ｻｲｽﾞ2 ｻｲｽﾞ3
Ａ2～Ｇ２(テーブル：在庫情報）
Ｉ2～Ｏ２(テーブル：在庫集計）
Ｑ2 (テーブル：リスト品名）
Ｒ２ (テーブル：リストサイズ1）
Ｓ2 (テーブル：リストサイズ2）
Ｔ２ (テーブル：リストサイズ3）
として、
データ入力規則で B =INDIRECT("リスト品名")
C,D、Eも同様にしました。
手入力するのは、テーブル：在庫情報で、Ｂセル2行以降に、品名、サイズ1、サイズ2、サイズ3をプルダウンで選択し、入庫数、出庫数を入力した際、
テーブル：在庫集計に反映され、なおかつ、品名、サイズ1.2.3すべてが一致したものは、抽出されて、Ｉ行に品名追加されずに
入庫数、出庫数のみがカウントされ、ＭＮ行総入庫数、総出庫数に
カウントされるのは可能でしょうか？
＜ 使用 Excel：Excel2019、使用 OS：Windows10 ＞
ってどういう意味？？ テーブルが一列だったり 1 セルだったりする？？
＞データ入力規則で B =INDIRECT("リスト品名")
これも理解不能。左辺の B は何？ 「リスト品名」って何？
（xlg） 2023/09/28(木) 13:38:49
＞テーブル：在庫集計に反映され、なおかつ、品名、サイズ1.2.3すべてが一致したものは、抽出されて、Ｉ行に品名追加されずに ＞入庫数、出庫数のみがカウントされ、ＭＮ行総入庫数、総出庫数にカウントされる
ここ、よく分からないです。 では、「品名、サイズ1.2.3すべては一致しなかったもの」はどうなるのですか？
（半平太） 2023/09/28(木) 14:10:19
「品名、サイズ1.2.3すべては一致しなかったもの」は、テーブル：在庫集計に
そのまま反映されて、行数が増えていくということです。
例えば、
Ｂ２セルに品名、Ｃ２セルにサイズ1，Ｄ２セルにサイズ2，Ｅ２セルにサイズ3をおそれぞれ選択し、
Ｆ２セルの入庫数に1と入力すると、
テーブル：在庫集計の
Ｉ２セルにＢ２セルで選択した品名が反映、Ｃ２セルはＪ２セル、Ｄ２セルはＫ２セル、Ｅ２セルはＬ２セル、
Ｆ２セルに入力した1が、Ｍ２セルに反映、Ｏ２セルが集計
Ｂ３セルに品名、Ｃ３セルにサイズ1，Ｄ３セルにサイズ2，Ｅ３セルにサイズ3をそれぞれ選択し、
入庫数に1と入力すると
テーブル：在庫集計の
Ｉ３セルにＢ３セルで選択した品名が反映、Ｃ３セルはＪ３セル、Ｄ３セルはＫ３セル、Ｅ３セルはＬ３セル、
Ｆ３セルに入力した1が、Ｍ３セルに反映、Ｏ３セルが集計
そして、次、Ｂ４セル、Ｃ４セル、Ｄ４セル、Ｅ４セルをそれぞれ選択したときに、テーブル：在庫集計にすべてが(品名、サイズ1.2.3）一致するものがあった場合、一致したところに、入庫数や出庫数のみが反映されていくということです。
（kiyo） 2023/09/28(木) 14:44:53
なるほどです。 ・・が、バージョンが365じゃないと、ちょっと厄介な構想ですね。
しばらく、私は引っ込みます。
他の回答者のレスをお待ちください。m(__)m
（半平太） 2023/09/28(木) 17:42:10
参考出品です。
ひとつのシートに集約したものです。
|[A] |[B] |[C] |[D]|[E]|[F]|[G] |[H] |[I] |[J]
[1]|入出庫記録 | | | | | |在庫管理 | | |
[2]|品名／サイズ |入庫数|出庫数| | | |品名／サイズ |入庫数|出庫数|在庫数
[3]|ダンボール箱／大| 100| | | | |ダンボール箱／大| 100| 20| 80
[4]|ダンボール箱／小| 250| | | | |ダンボール箱／中| 0| 0| 0
[5]|印刷用紙／A3 | 500| | | | |ダンボール箱／小| 250| 0| 250
[6]|印刷用紙／A5 | 350| | | | |印刷用紙／A3 | 500| 0| 500
[7]|ダンボール箱／大| | 20| | | |印刷用紙／A4 | 0| 0| 0
[8]|印刷用紙／A5 | | 50| | | |印刷用紙／A5 | 350| 50| 300
リスト=OFFSET($G$3,0,0,COUNTA($G$3:$G1000),1)
入庫=IF(G3="","",SUMIF($A$3:$B$10,G3,$B$3:$B$10))
出庫=IF(G3="","",SUMIF($A$3:$C$10,G3,$C$3:$C$10))
在庫=H3-I3
入出庫記録はプルダウン設定
在庫管理はリストの登録も含む
（IT） 2023/09/20(水) 21:34:14
（IT） 2023/09/28(木) 21:31:38
品名、サイズ1.2.3と3か所あるのは、
1つの商品に対して、サイズが3か所あるんです。
例えば、『あ』という商品にサイズが10.20.30というものもあれば、
『あ』10.10.30
『あ』20.40.10
『い』40.20.90
というように、1つの商品に対して無数の組み合わせがあるんです。
半平太さんがおっしゃるように、やはり難しいでしょうか。
（kiyo） 2023/09/28(木) 21:54:47
（?） 2023/09/29(金) 08:59:16
>テーブル範囲じゃないでしょ。 >ビボットテーブルじゃあるまいし。 テーブルとして書式設定された テーブル名が在庫情報 列名が日付 品名 ｻｲｽﾞ1･･･など ではないの？ （どん） 2023/09/29(金) 09:42:55
斜め読みしただけで、よく分っていませんが.....
在庫情報テーブルの「品名、サイズ1、2、3」毎に「入庫数/出庫数」を集計したいという事ですか？
↑の認識でOKなら、 VBAの使用はOKですか？ 関数しかダメな場合、作業列の追加はOKですか？
（まる2021） 2023/09/29(金) 12:22:25
もう、昼休みが、終わりなので投稿しっぱなしになりますが...↑の認識で合ってるなら、 *集計はリアルタムではありません。ボタンクリック等で集計したいタイミングで実行する仕様です。
Sub test()
Const header$ = "品名|ｻｲｽﾞ1|ｻｲｽﾞ2|ｻｲｽﾞ3|総入庫数|総出庫数|在庫数"
Dim v, r&
Dim dic As Object, k
Set dic = CreateObject("Scripting.Dictionary")
With ActiveSheet
v = .Range("在庫情報")
For r = 1 To UBound(v, 1)
k = v(r, 2) & "♪" & v(r, 3) & "♪" & v(r, 4) & "♪" & v(r, 5)
If dic.Exists(k) Then
dic(k) = Array(dic(k)(0) + v(r, 6), dic(k)(1) + v(r, 7))
Else
dic(k) = Array(v(r, 6), v(r, 7))
End If
Next
On Error Resume Next
.ListObjects("在庫集計").Delete
On Error GoTo 0
.Range("I1:O1").Value = VBA.Split(header, "|")
Dim cnt&
For Each k In dic
.Range("I2:L2").Offset(cnt).Value = VBA.Split(k, "♪")
.Range("M2:N2").Offset(cnt).Value = dic(k)
.Range("O2").Offset(cnt).Value = dic(k)(0) - dic(k)(1)
cnt = cnt + 1
Next
With .ListObjects.Add(SourceType:=xlSrcRange, _
Source:=.Range("I1").CurrentRegion.Columns(1).Resize(, 7), XlListObjectHasHeaders:=xlYes)
.Name = "在庫集計"
.TableStyle = "TableStyleMedium16"
End With
End With
End Sub
（まる2021） 2023/09/29(金) 13:05:44
＞在庫情報テーブルの「品名、サイズ1、2、3」毎に「入庫数/出庫数」を集計したいという事ですか？
在庫情報テーブルの「品名、サイズ1.2.3」に入庫数、出庫数を入力すると、
在庫集計テーブルの「品名、サイズ1.2.3」と、入庫数、出庫数に集計されてほしいということです。
＞ 関数しかダメな場合、作業列の追加はOKですか？
追加ＯＫです。
どうぞよろしくお願いいたします。
（kiyo） 2023/09/29(金) 14:00:57
（IT） 2023/09/29(金) 15:12:04
了解しました。 只、自分の認識で合っているのか、それが不安で...
ちなみに、「Power Query」もNGですか？ 「Power Query」ならメニュ-操作だけで、できるので一番、簡単かも。
関数しかダメなら、夜、考えます。
（まる2021） 2023/09/29(金) 15:23:51
＞日付 品名 ｻｲｽﾞ1 ｻｲｽﾞ2 ｻｲｽﾞ3 入庫数 出庫数 総入庫数 総出庫数 在庫数
このパターンだと品名の数とサイズの組み合わせが多数過ぎて、入庫数、出庫数を
入力する際、見つけるのが大変になりませんか？
（まる2021さん）
何度もありがとうございます。
Power Queryもできません。
少し調べてみましたが、とても便利そうですね。
ただまだまだ無知な私には、ちょっと無理な気がします。
（kiyo） 2023/09/29(金) 16:21:50
（IT） 2023/09/29(金) 17:24:19
運用方法がわかりませんが
テーブル：在庫情報 の過去データを削除しない運用であれば テーブル：在庫集計 は、ピボットテーブル（表形式）にしてはどうでしょうか。
（マナ） 2023/09/29(金) 18:30:52
関数案です。「在庫情報(A～H列)」「在庫集計(J～Q列)」両方共、作業列(H列とJ列)を追加しています。
又、「在庫情報(A～H列)」はテーブルですが、「在庫集計(J～Q列)」はテーブルではありません。(S～V列)は入力規則用のテーブルです。
只、質問がややこしいので、自分の認識で合っているのか、それが不安で...
文章で書くより、↓のように表形式で「現況」と「希望結果」を書いてもらえるとありがたいです。
__A____ __B__ __C__ __D__ __E__ __F___ __G___ __H_________________ __I __J_________________ __K__ __L__ __M__ __N__ __O_____ __P_____ __Q___ __R __S__ __T__ __U__ __V__
1 日付 品名 ｻｲｽﾞ1 ｻｲｽﾞ2 ｻｲｽﾞ3 入庫数 出庫数 連結Key 連結Key 品名 ｻｲｽﾞ1 ｻｲｽﾞ2 ｻｲｽﾞ3 総入庫数 総出庫数 在庫数 品名 ｻｲｽﾞ1 ｻｲｽﾞ2 ｻｲｽﾞ3
2 9月11日 品名1 10 100 1000 10 5 品名1♪10♪100♪1000 品名1♪10♪100♪1000 品名1 10 100 1000 50 25 25 品名1 10 100 1000
3 9月12日 品名1 10 200 2000 20 10 品名1♪10♪200♪2000 品名1♪10♪200♪2000 品名1 10 200 2000 20 10 10 品名2 20 200 2000
4 9月13日 品名1 20 300 3000 30 15 品名1♪20♪300♪3000 品名1♪20♪300♪3000 品名1 20 300 3000 30 15 15 品名3 30 300 3000
5 9月14日 品名1 10 100 1000 40 20 品名1♪10♪100♪1000 品名2♪10♪100♪1000 品名2 10 100 1000 50 25 25
6 9月15日 品名2 10 100 1000 50 25 品名2♪10♪100♪1000
[H2] =TEXTJOIN("♪",FALSE,在庫情報[@[品名]:[ｻｲｽﾞ3]]) ↓にコピー
[J2] =IFERROR(INDEX(在庫情報[連結Key],AGGREGATE(15,6,(ROW(在庫情報[連結Key])-1)/(COUNTIF(INDIRECT("H2:H"&ROW(在庫情報[連結Key])),在庫情報[連結Key])=1),ROW(A1))),"")
[K2] =IF($J2="","",FILTERXML("<root><x>"&SUBSTITUTE($J2,"♪","</x><x>")&"</x></root>","//x["&COLUMN(A1)&"]")) →に[N2](ｻｲｽﾞ3)までコピー
[O2] =IF($J2="","",SUMIFS(在庫情報[入庫数],在庫情報[品名],$K2,在庫情報[ｻｲｽﾞ1],$L2,在庫情報[ｻｲｽﾞ2],$M2,在庫情報[ｻｲｽﾞ3],$N2))
[P2] =IF($J2="","",SUMIFS(在庫情報[出庫数],在庫情報[品名],$K2,在庫情報[ｻｲｽﾞ1],$L2,在庫情報[ｻｲｽﾞ2],$M2,在庫情報[ｻｲｽﾞ3],$N2))
[Q2] =IF($J2="","",O2-P2)
[J2]から[Q2]までを選択して、適当な範囲まで↓にコピー
※はっきり言って、Excel2019なら、マナさんが言われるように、 「ピボットテーブル」や1つ前で提案した「Power Query」の方が簡単ですよ。 Excelが標準で提供している機能なので、習得されることをお勧めします。 （まる2021） 2023/09/29(金) 21:01:20
＞ テーブル：在庫集計 は、ピボットテーブル（表形式）にしてはどうでしょうか。
習得できるように勉強してみます。
提案ありがとうございました。
(興味津々さん)
＞1つの商品に対して無数の組み合わせがあるんです。
どんな商品だろうね。
ダクトの継手です(#^^#)
ＲＴ管やＲＹ管の口径で、100φ*125φ*200φといったように25φ刻みで最大600φまで
あるんです。
在庫としてあるのはその膨大な組み合わせの中の１部なので、すべての組み合わせを
品名にする必要もなく、今回質問させていただきました。
(まる2021さん)
本当に本当にありがとうございます！
＞文章で書くより、↓のように表形式で「現況」と「希望結果」を書いてもらえるとありがたいです。
そうですよね。まる2021さんのはとても分かりやすいです。
今後質問させていただく際は、そうさせていただきます。
まだまだ初心者で、まる2021さんの関数で在庫表作成できていませんが、
一度この通り作成してみたいと思います。
まる2021さんが作成してくれた表は、私のイメージした希望結果通りです。
一度このご提案で頑張ってみます。
そして、「ピボットテーブル」や「Power Query」も習得できるように頑張ります！
本当にありがとうございました。
（kiyo） 2023/09/29(金) 23:38:21
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.