[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『直近の平均を求める』（たらこ）
うちの会社から業者に発注する品物は、そのときによって単価が変動します。 現在、注文するたびに以下の台帳に入力しています。 品名 注文番号 単価 -------------------------------- ○○ A001 ××円 □□ A002 ××円 △△ A003 ××円 ○○ A004 ××円
別シートに台帳のデータをもとに、同じ品物の直近4回注文分の金額と その平均金額を示した以下のような表を作成したいのですが、どうすればよいかわかりません。 品名 注文番号1 単価1 注文番号2 単価2 注文番号3 単価3 注文番号4 単価4 平均単価 ------------------------------------------------------------------------------- ○○ A001 ××円 A004 ××円 A016 ××円 A023 ××円 ××円 直近4回分のデータが自動で更新されるように表を作成することはできますか？ どなたか、良い方法があれば教えてください。 宜しくお願いします。
その直近4回分のデータはどこにどんな形で残っていますか? レイアウトが分からないと回答は難しいです。 (sato)
現在、注文したものは次々と以下の台帳に入力しているだけの状態です。
品名 注文番号 単価
-------------------------------- ○○ A001 ××円 □□ A002 ××円 △△ A003 ××円 ○○ A004 ××円
この台帳をもとにして、別シートに直近4回分のデータが自動更新される表を
作ることは可能でしょうか。
こんにちは～♪
こんな表の場合です。。。
Sheet1↓
┌─┬────┬────┬────┬────┐ │ │ A │ B │ C │ D │ ├─┼────┼────┼────┼────┤ │ 1│品名 │注文番号│単価 │作業列 │ ├─┼────┼────┼────┼────┤ │ 2│○○ │A001 │ 10│ │ ├─┼────┼────┼────┼────┤ │ 3│□□ │A002 │ 20│□□ │ ├─┼────┼────┼────┼────┤ │ 4│△△ │A003 │ 30│△△ │ ├─┼────┼────┼────┼────┤ │ 5│○○ │A004 │ 40│ │ ├─┼────┼────┼────┼────┤ │ 6│○○ │A005 │ 120│○○ │ ├─┼────┼────┼────┼────┤ │ 7│○○ │A006 │ 130│○○ │ ├─┼────┼────┼────┼────┤ │ 8│□□ │A007 │ 200│□□ │ ├─┼────┼────┼────┼────┤ │ 9│○○ │A008 │ 140│○○ │ ├─┼────┼────┼────┼────┤ │10│○○ │A009 │ 140│○○ │ ├─┼────┼────┼────┼────┤ │11│ │ │ │ │ └─┴────┴────┴────┴────┘
★最初に、Sheet1のD列に作業列を作ります。。 D2セルへ =IF(COUNTA(A2:C2)<3,"",REPT(A2,COUNTIF(A2:$A$100,A2)<5)) 下へコピー。。。
別シート↓
┌─┬────┬─────┬────┬─────┬────┬─────┬────┬─────┬────┬────┐ │ │ A │ B │ C │ D │ E │ F │ G │ H │ I │ J │ ├─┼────┼─────┼────┼─────┼────┼─────┼────┼─────┼────┼────┤ │ 1│品名 │注文番号1 │単価1 │注文番号2 │単価2 │注文番号3 │単価3 │注文番号4 │単価4 │平均単価│ ├─┼────┼─────┼────┼─────┼────┼─────┼────┼─────┼────┼────┤ │ 2│○○ │A005 │ 120│A006 │ 130│A008 │ 140│A009 │ 140│ 132.5│ ├─┼────┼─────┼────┼─────┼────┼─────┼────┼─────┼────┼────┤ │ 3│□□ │A002 │ 20│A007 │ 200│ │ │ │ │ 110│ ├─┼────┼─────┼────┼─────┼────┼─────┼────┼─────┼────┼────┤ │ 4│△△ │A003 │ 30│ │ │ │ │ │ │ 30│ ├─┼────┼─────┼────┼─────┼────┼─────┼────┼─────┼────┼────┤ │ 5│ │ │ │ │ │ │ │ │ │ │ └─┴────┴─────┴────┴─────┴────┴─────┴────┴─────┴────┴────┘
★A列の品名は、手入力しておいてください。。
B2セルへ =IF(COUNTIF(Sheet1!$D:$D,$A2)<INT(COLUMN(B1)/2),"",INDEX(Sheet1!$B:$C,SMALL(INDEX((Sheet1!$D$1:$D$100<>$A2)* 10^4+ROW($1:$100),),INT(COLUMN(B1)/2)),MOD(COLUMN(B1),2)+1))
右はI列迄までと、下へコピーです。。。
★平均は。。。 J2セルへ =IF(A2="","",SUM(C2:I2)/COUNT(C2:I2)) 下へコピーです。。。
Sheet1 Sheet2の式は、データ範囲を100行にしてあります。 必要行に変更して下さい。。。
ただし、データ行が多いと処理が重くなりますから。 個人的には、マクロが良いと思います。。
ご参考にどうぞ。。。
。。。Ms.Rin～♪♪
ありゃ、衝突☆。 こんなんでもいいかも～？とUPしてみます。 台帳がこんな具合、 A B C D 1 品名 注文番号 単価 作業列 2 ○○ A001 57 ○○7 3 □□ A002 11 □□4 4 △△ A003 53 △△4 5 ○○ A004 31 ○○6 6 ○○ A005 35 ○○5 7 □□ A006 80 □□3 8 △△ A007 43 △△3 9 ○○ A008 36 ○○4 10 ○○ A009 12 ○○3 11 □□ A010 92 □□2 12 △△ A011 99 △△2 13 ○○ A012 34 ○○2 14 ○○ A013 42 ○○1 15 □□ A014 19 □□1 16 △△ A015 81 △△1
平均を出すシートがこんな具合だとして。 A B C D E F G H I J 1 品名 注文番号1 単価1 注文番号2 単価2 注文番号3 単価3 注文番号4 単価4 平均単価 2 ○○ A008 36 A009 12 A012 34 A013 42 31 3 □□ A002 11 A006 80 A010 92 A014 19 50.5 4 △△ A003 53 A007 43 A011 99 A015 81 69
台帳のD列を作業列として、=A2&COUNTIF(A2:A$100,A2) 下へコピー。 B2 =INDEX(台帳!$B$2:$B$100,MATCH($A2&(5-ROUND(COLUMN(A1)/2,0)),台帳!$D$2:$D$100,0)) C2 =INDEX(台帳!$C$2:$C$100,MATCH($A2&(5-ROUND(COLUMN(A1)/2,0)),台帳!$D$2:$D$100,0)) B2:C2を選択して右へコピー、下へコピー。
とりあえず台帳の入力範囲を100行目までとしましたけど、実情に合わせてください。 (かなれっと)
Ms. Rinさん、かなれっとさん、
丁寧に教えていただき、本当にありがとうございます。
もう一つ教えていただきたいのですが、直近5回分のデータだと式はどうなりますか？？
本当に初心者で、すみません。
関数の内容、理解できるように只今勉強中です！
たらこ
かなれっとのだと、、、例えば
=INDEX(台帳!$B$2:$B$100,MATCH($A2&(5-ROUND(COLUMN(A1)/2,0)),台帳!$D$2:$D$100,0))
~~~
5-ってところを6-にすればいいです。
右にコピーすると2列ごとに4,3,2,1ってなるように細工しているだけなので、6-にすれば5,4,3…ってなるはずです～
(かなれっと)
ふたたび～です。。。♪
>直近5回分のデータだと式はどうなりますか？？
の場合は。。。 Sheet1の作業列の式を。。 <5 を <6 に変えて。。
D2セルへ =IF(COUNTA(A2:C2)<3,"",REPT(A2,COUNTIF(A2:$A$100,A2)<6)) 下へコピー。。。
です。。。
それから。。。 別シート私の式は、平均を取りたい品目ごとの 直近(この場合は5回分)のデータが入力されていない場合も 考慮してデータを左に詰めたり 空白処理をしたりして、式が長くなっています。。。
すでに、品目ごとの直近(この場合は5回分)のデータが入力されていたり 5回に足りない場合も、そういう処理が必要なければ
かなれっとさんの式をお勧めします。。。 データ数が多くても重くならず、大丈夫ですから。。。
。。。Ms.Rin～♪♪
Ms. Rinさん、かなれっとさん、 本当にありがとうございました！！ まだ表は完成していないのですが、頑張ってやってみます。 たらこ
遅くなりましたが、ようやく表の作成にとりかかっています。 Ms. Rinさんのアドバイスもあり、かなれっとさんの式で作成しているのですが、 =INDEX(台帳!$B$2:$B$100,MATCH($A2&(5-ROUND(COLUMN(A1)/2,0)),台帳!$D$2:$D$100,0)) の式で#N/Aが出てしまいます。 これはどうすればよいでしょうか。 品名に“P12D-3A4-56-789”のような数字が入っているのが原因でしょうか。 普通に「りんご」「いちご」などと入力すると、#N/Aはでません。 P12D-3A4-56-789といった商品名で入力する必要があるのですが、可能でしょうか。 教えてください。 たらこ
ということは、注文数が４回未満の品物があるってことですかね。 それだったら、 =IF(COUNTIF(台帳!$D$2:$D$100,$A2&(5-ROUND(COLUMN(A1)/2,0)))=0,"",INDEX(台帳!$B$2:$B$100,MATCH($A2&(5-ROUND(COLUMN(A1)/2,0)),台帳!$D$2:$D$100,0))) のようにして、該当ない場合は空欄表示にしてしまうとか。 直近４回までのうちで最も古い回数のデータを左に詰めて表示したいのであれば、かなれっとの案だとダメです～ Rinさんの案だと色々配慮されてますよｂ
なんて書いていたらたらこさんと衝突☆
なるほど。 それでは、作業列と検索値をちょっと変えて、 作業列に =A2&"♪"&COUNTIF(A2:A$100,A2) B2 =INDEX(台帳!$B$2:$B$100,MATCH($A2&"♪"&(5-ROUND(COLUMN(A1)/2,0)),台帳!$D$2:$D$100,0)) C2 =INDEX(台帳!$C$2:$C$100,MATCH($A2&"♪"&(5-ROUND(COLUMN(A1)/2,0)),台帳!$D$2:$D$100,0)) とするとどうでしょう。。。？ (かなれっと)
かなれっとさん、ありがとうございます☆☆ &"♪"を加える方法で無事解決しました。こんな方法があるんですね！！ もう一つだけ教えてほしいのですが、平均単価を出す式がわかりません。 （何度も質問ばかりですみません。）
=IF(A2="","",SUM(C2:K2)/COUNT(C2:K2))だと、4回未満の場合は#N/Aになってしまいますよね。
4回未満の場合でも計算させるためには、どうすればよいでしょうか。
AVERAGE関数はどうですか？ ４回未満の場合があるなら、注文番号や単価を =IF(COUNTIF(台帳!$D$2:$D$100,$A2&"♪"&(5-ROUND(COLUMN(A1)/2,0)))=0,"",INDEX(台帳!$B$2:$B$100,MATCH($A2&"♪"&(5-ROUND(COLUMN(A1)/2,0)),台帳!$D$2:$D$100,0))) のようにしておいて空白文字を返しておき、 平均を =AVERAGE(C2:I2) で計算すれば、文字列や空白セルは除いて計算してくれます～ (かなれっと)
かなれっとさん、 無事できました！本当にありがとうございました。 超初心者のたらこに何度も丁寧に教えていただいて、本当に感謝感謝です！！！ たらこ
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.