エクセルの関数の質問

Question

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

nishi6 · Accepted Answer

商品が３種の場合の例です。関数で計算してみました。

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列にコピーします。

以上商品が３種類の場合の例です。
関数を使用するには、事前に式を登録しておく必要があります。ご参考に。

imogasi · Answer

他のご解答を見るて（１）ＶＢＡ可（２）長くてもがありなら
エクセルＶＢＡらしい解答を上げます。他のご解答はＶＢ的。
マクロの記録を大幅に手を加えています。
(シートにボタンを１
つ作り、それをクリックすると、瞬時に結果がシート２に出来ます。本件はボタンを作っていませんし、その説明をしていません。)
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で強制改行される個所があり、復元しないと動かないことがあります。）

imogasi · Answer

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

kazuhiko5681 · Answer

はじめまして。
ピポットテーブルを使う方法もありますが、データーを入力するだけで自動的にあなたのやりたいことを実現する方法をご紹介いたします。以下の方法で操作してみて下さい。

１.新規ブックを開き、Ａ１に日付・Ｂ１に商品名・Ｃ１に個数と入力する。２.ＶＢＥの画面を開き、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
　
もし何かありましたら、またお知らせ下さい。私でよろしければ、あなた様のやりたいことが実現するまでお手伝いさせていただきたいと思います。

goo_noc · Answer

入出庫表から、コーヒーと書かれた数量の合計値を出すのであれば、
SUMIF(範囲, 検索条件, 合計範囲)関数でできますが、

商品表のように表示するであれば、マクロが必要になります。
どちらにします？

oraho · Answer

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

エクセルの関数の質問

商品が３種の場合の例です。

他のご解答を見るて（１）ＶＢＡ可（２）長くてもがありなら

●なぜ関数で解決するのが難しいか説明したくて下記記します。

はじめまして。

入出庫表から、コーヒーと書かれた数量の合計値を出すのであれば、

EXCELならピボットテーブルを使用すれば簡単に作成できます。

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング