エクセルの関数の質問

質問

一つの表には全商品の入出庫の表を作って、別に商品別の在庫表を作って
入出庫表に数量を入力すると在庫表に自動的に入力されるようにするには
どういう関数を使えばいいでしょうか。
質問がわかりにくかったらすいません。お願いします<(_ _)>

例）
入出庫表　　　　　　　　　　　　　　商品別表
9/10　コーヒー　　10　　　　　　　　コーヒー　　紅茶　　　　カプチーノ
9/10　紅茶　　　　20　　　　　　　　9/10　10　　9/10　20　　9/10　10
9/10　カプチーノ　10　　　　　　　　9/11　-1　　9/11　-2
9/11　コーヒー　　-1
9/11　紅茶　　　　-2

回答No.4

nishi6 · ベストアンサー

商品が３種の場合の例です。関数で計算してみました。

A1:入出庫表
A2:9/10
B2:コーヒー
C2:10
　以下、A～C列は個々のデータです。

K1:商品別表
　K2:コーヒー
　M2:紅茶
　O2:カプチーノ
これは表題です。

途中に補助の算式を書きます。
E2:=COUNTIF($B$2:$B2,K$2)　以下、行方向下にコピー。
E列をG列、I列にコピーします。各商品別の累計個数を計算しています。

K3:=IF(ISERROR(INDEX($A:$A,MATCH(ROW()-2,E:E,0))),"",INDEX($A:$A,MATCH(ROW()-2,E:E,0)))
L3:=IF(ISERROR(INDEX($C:$C,MATCH(ROW()-2,E:E,0))),"",INDEX($C:$C,MATCH(ROW()-2,E:E,0)))
以下、行方向下にコピーします。コーヒーの一覧を作っています。
出力行がどこで終わるか分からないので、ISERRORでエラー判定をしているため式が長くなりました。

K列を、M、O列にコピーします。
L列を、N、P列にコピーします。

以上商品が３種類の場合の例です。
関数を使用するには、事前に式を登録しておく必要があります。ご参考に。

お礼

こういうのが欲しかったんです(^O^)ありがとうございました<(_ _)>

回答No.6

imogasi

他のご解答を見るて（１）ＶＢＡ可（２）長くてもがありなら
エクセルＶＢＡらしい解答を上げます。他のご解答はＶＢ的。
マクロの記録を大幅に手を加えています。
(シートにボタンを１
つ作り、それをクリックすると、瞬時に結果がシート２に出来ます。本件はボタンを作っていませんし、その説明をしていません。)
Sub Macro1()
'-----行数変動に対処
d = Worksheets("sheet1").Range("a1").CurrentRegion.Rows.Count
s = "a1:c" & d
'-----copy
Worksheets("sheet1").Range(s).Copy
Worksheets("sheet2").Activate
Worksheets("sheet2").Range("a1").Select
Worksheets("Sheet2").Paste
'-----sort
Application.CutCopyMode = False
Worksheets("Sheet2").Range(s).Sort Key1:=Range("B2"), Order1:=xlAscending, Key2:=Range("A2") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, SortMethod:=xlPinYin
'----subtotal
Worksheets("Sheet2").Range(s).Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(3), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End Sub
シート画面で（１）ＡＬＴ＋Ｆ１１キー（２）ＡＬＴ＋Ｉ
（３）ＡＬＴ＋Ｍのコード画面に上記をコピーして、Ｆ５
で実行してください。
（注意：OKWEBで強制改行される個所があり、復元しないと動かないことがあります。）

お礼

すいません。皆さんにこんなに詳しく答えて貰うなんて思いませんでした。
ありがとうございました<(_ _)>

回答No.5

imogasi

●なぜ関数で解決するのが難しいか説明したくて下記記します。少し長く表現が微妙になりますがよろしく。
●エクセルでビジネスの問題に使うとき
（１）操作で解決（簡単処理）四則演算＋Ｓｕｍ程度を使う
（２）操作で解決（並び替え・フィルタ・ピボット・集計等の　　　表を結果表で置きかえるものを使う）
（３）関数を利用する（Ｓｕｍ関数以外）
（４）関数を利用する（ＤａｔａＢａｓｅ関数、Ｖｌｏｏｋｕ　　　ｐなど）
（５）ＶＢＡ・ＳＱＬなどを使うが考えられます。
●関数の限界は
（Ａ）関数はあるセルに関数を入れることによって、他のセルに値や関数や他のセルのコピーをセットできない。
　受け手の側のセルに関数をセットしないとならない。
　それで検索の結果条件該当分など、いくら受けるべきか数が　不定の場合は困ってしまう。（本質問にも該当）
（Ｂ）行、列、シートを増やしたり、削除したり出来ない。
（Ｃ）ＤａｔａＢａｓｅ関数も計数の合計しか出さず、条件に　　　合うレコードや項目（セル）を一括して採って指定個所　　　にセットしてくれない。
（Ｄ）関数は１セルに２つ以上セットできない。
（Ｅ）あることをしたいとき、関数式は結構複雑になってしま　　　い、他人や素人による可読性はそんなに良くない。
　　　他のＯＫＷＥＢの解答も結構判らないこともありません　　　か。
●さて本件では
　（１）ソート（キーは商品＋日付）すると、一番問題解決題に近づくが、別シートに手操作でコピーするとかは好みじゃないですね。それと別範囲にコピーしないと、元データが崩れる（変わる）ので、都合が悪い。ソート結果を別範囲や別シートに出してくれれば良いが、出来ない。操作の（アドバンス・）フィルタはできるが、別シートに結果を持って行くことが出来ない。
コピーはコピー元が変動するので手作業が必要で、関数では難しい。
（２）データ－集計もこれに近いです。
（２）それと操作によると、一日分とかを入力し終わってからまとめて１回操作をやることになるが、これでは好みではない。（入力操作中は結果に反映してない）。
（３）関数は元になるデータが変わる（入る）と、即座に結果
も変わるのでそういう点では良い。
（４）本質問では、商品別在庫表のコーヒーの欄は日々在庫が変わっても固定したいのでしょうが、それはＶＢＡで無いと
難しい。
●操作では（１）コピー－＞（２）ソート－＞（３）データ－集計（集計行を挿入にチェック）が一番近い。
しかし関数では難しい。
●本件は（１）アクセスで処理する（２）アクセスＶＢＡで処理する（３）エクセルＶＢＡで処理する。に適した課題でしょう。後任者への引継ぎに心配はあるものの、その方向に進むべきでは。

お礼

要するにアクセスでつくるか、エクセルVBで作ったほうが良いということですね。
エクセルのVBは、私使ったことないんでわからないんです。
アクセスは少しわかりますが他の方がわかりません。
小企業なので次に使う方が必ずしもアクセスが出来るとは思いませんので後で修正できなくなっても困ります。
現に前の会社ではそうだったのでたまに電話があります。
もうそんなことはしたくありませんので。。。

回答No.3

kazuhiko5681

はじめまして。
ピポットテーブルを使う方法もありますが、データーを入力するだけで自動的にあなたのやりたいことを実現する方法をご紹介いたします。以下の方法で操作してみて下さい。

１.新規ブックを開き、Ａ１に日付・Ｂ１に商品名・Ｃ１に個数と入力する。２.ＶＢＥの画面を開き、Sheet1モジュールに下記のコードをコピー・ペーストする。
３.データーを入力してみる。Ｃ列がフォーカスを失うと自動的に商品別表ができるように作ってあります。

Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRow As Long
Dim myRange As Range
Dim myCell As String
Dim myCell1 As String
Dim myCell2 As String

If Target.Row = 1 Then Exit Sub
myRow = Target.Row
myCell = Cells(1, Columns.Count).Address
If Range("C" & myRow).Address = Target.Address Then
If Worksheets.Count = 1 Then
Worksheets.Add after:=Worksheets(Worksheets.Count)
Worksheets(2).Range("A1:B1").MergeCells = True
Worksheets(2).Range("A1").Value = Target.Offset(0, -1).Value
Worksheets(2).Range("A2").Value = "日付"
Worksheets(2).Range("A3").Value = Target.Offset(0, -2).Value
Worksheets(2).Range("B2").Value = "個数"
Worksheets(2).Range("B3").Value = Target.Value
Else
Set myRange = Worksheets(2).Range("A1:" & myCell).Find(Target.Offset(0, -1).Value, lookat:=xlWhole)
If myRange Is Nothing Then
myCell1 = Worksheets(2).Range(myCell).End(xlToLeft).Offset(0, 1).Address
myCell2 = Worksheets(2).Range(myCell).End(xlToLeft).Offset(0, 2).Address
Worksheets(2).Range(myCell1 & ":" & myCell2).MergeCells = True
Worksheets(2).Range(myCell1).Value = Target.Offset(0, -1).Value
Worksheets(2).Range(myCell1).Offset(1, 0).Value = "日付"
Worksheets(2).Range(myCell1).Offset(2, 0).Value = Target.Offset(0, -2).Value
Worksheets(2).Range(myCell2).Offset(1, 0).Value = "個数"
Worksheets(2).Range(myCell2).Offset(2, 0).Value = Target.Value
Else
Worksheets(2).Cells(Rows.Count, myRange.Column).End(xlUp).Offset(1, 0).Value = Target.Offset(0, -2).Value
Worksheets(2).Cells(Rows.Count, myRange.Column).End(xlUp).Offset(0, 1).Value = Target.Value
End If
End If
Worksheets(1).Activate
Range(Target.Address).Offset(1, -2).Select

End If

End Sub

もし何かありましたら、またお知らせ下さい。私でよろしければ、あなた様のやりたいことが実現するまでお手伝いさせていただきたいと思います。

お礼

すごいですね。驚きましたこんな丁寧な回答が戻ってくるとは思いませんでした。
でも、私がVBがわからないんで、やめておきます。
質問されても私が答えられません。すいません、ありがとうございました。

回答No.2

goo_noc

入出庫表から、コーヒーと書かれた数量の合計値を出すのであれば、
SUMIF(範囲, 検索条件, 合計範囲)関数でできますが、

商品表のように表示するであれば、マクロが必要になります。
どちらにします？

お礼

やっぱり表にするにはマクロが必要なんですかね。。。
どうにかして関数にしたいんです。
マクロを使うと私がいない時何かあったら困るんです。
関数だったら少しはわかる人がいるからどうにか出来ないかな？と思ってます。

回答No.1

oraho

EXCELならピボットテーブルを使用すれば簡単に作成できます。
１．まず、入出庫表を作成します。（日付/品名/数量）
２．データ（D)のピボットテーブル（P)を選択
３．入出庫表の全体を選択
４．行のフィールドには品名、日付の順で入れ、データアイテムに数量を指定します。

関数を使用すると、品名・日付ごとにVLOOKを使用しなければいけないのでかなり大変です。

お礼

ピボットテーブルまだ使ったことないんですけど今からやってみます。
ありがとうございました。