• ベストアンサー

エクセルの関数の質問

一つの表には全商品の入出庫の表を作って、別に商品別の在庫表を作って 入出庫表に数量を入力すると在庫表に自動的に入力されるようにするには どういう関数を使えばいいでしょうか。 質問がわかりにくかったらすいません。お願いします<(_ _)> 例) 入出庫表              商品別表 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
  • ベストアンサー率67% (869/1280)
回答No.4

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

chiharuhotei
質問者

お礼

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

その他の回答 (5)

  • imogasi
  • ベストアンサー率27% (4737/17069)
回答No.6

他のご解答を見るて(1)VBA可(2)長くてもがありなら エクセルVBAらしい解答を上げます。他のご解答はVB的。 マクロの記録を大幅に手を加えています。 (シートにボタンを1 つ作り、それをクリックすると、瞬時に結果がシート2に出来ます。本件はボタンを作っていませんし、その説明をしていません。) 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 シート画面で(1)ALT+F11キー(2)ALT+I (3)ALT+Mのコード画面に上記をコピーして、F5 で実行してください。 (注意:OKWEBで強制改行される個所があり、復元しないと動かないことがあります。)

chiharuhotei
質問者

お礼

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

  • imogasi
  • ベストアンサー率27% (4737/17069)
回答No.5

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

chiharuhotei
質問者

お礼

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

回答No.3

はじめまして。 ピポットテーブルを使う方法もありますが、データーを入力するだけで自動的にあなたのやりたいことを実現する方法をご紹介いたします。以下の方法で操作してみて下さい。 1.新規ブックを開き、A1に日付・B1に商品名・C1に個数と入力する。2.VBEの画面を開き、Sheet1モジュールに下記のコードをコピー・ペーストする。 3.データーを入力してみる。C列がフォーカスを失うと自動的に商品別表ができるように作ってあります。 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   もし何かありましたら、またお知らせ下さい。私でよろしければ、あなた様のやりたいことが実現するまでお手伝いさせていただきたいと思います。

chiharuhotei
質問者

お礼

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

  • goo_noc
  • ベストアンサー率0% (0/7)
回答No.2

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

chiharuhotei
質問者

お礼

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

  • oraho
  • ベストアンサー率43% (7/16)
回答No.1

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

chiharuhotei
質問者

お礼

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

関連するQ&A

専門家に質問してみよう