[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『データー集計を教えてください』（ちぃさん）
お世話になります。
以前から集めているデーターを解りやすくSheetに表示させたいのですが思うようにできません。
もし宜しければご教授いただけないでしょうか。
Sheet1にあるデーターをSheet2にまとめたいです。
Sheet1
A B C D E........
日時 品名 値1 値2 値3
1 2013/1/20 aaa 50 102 115
2 2013/1/20 bbb 52 50 250
3 2013/1/25 aaa 51 105 121
.
.
.
このような形でSheet1にデーターを集めています。
このデーターをSheet2に
Sheet2
A B C D E F G........
品名 値1 値2
1 MIN AVERAGE MAX MIN AVERAGE MAX
2 aaa 50 50.5 51 102 103.5 105
3 bbb 52 52 52 50 50 50
.
.
.
このように表示させたいです。
現在はSheet2のC2に平均を出すために
=IF(VLOOKUP(A2,Sheet1!B:I,2,0),AVERAGE(A2=Sheet1!B:B,Sheet1!C:C))
としているのですが全く思うようにできません。
解るかたおられましたらご教授いただけないでしょうか。
解りにくい説明で申し訳ありません。
どうぞ宜しくお願いいたします。
＜ 使用 Excel：Excel2003、使用 OS：WindowsXP ＞
Sheet1
A B C D E ・・・ 1 日時 品名 値1 値2 値3 2 1/20 aaa 50 102 115 3 1/20 bbb 52 50 250 4 1/25 aaa 51 105 121 5
Sheet2
A B C D E F G 1 品名 値1 値2 2 MIN AVERAGE MAX MIN AVERAGE MAX 3 aaa 50 50.5 51 102 103.5 105 4 bbb 52 52 52 50 50 50 5
B3 =MIN(INDEX((Sheet1!$B$2:$B$100<>$A3)*10^16+INDEX(Sheet1!$C$2:$L$100,0,INT((COLUMN(A1)-1)/3)+1),)) C3 =SUMIF(Sheet1!$B$2:B$100,$A3,INDEX(Sheet1!$C$2:$L$10,0,INT((COLUMN(B1)-1)/3)+1))/COUNTIF(Sheet1!$B$2:$B$100,$A3) D3 =MAX(INDEX((Sheet1!$B$2:$B$100=$A3)*INDEX(Sheet1!$C$2:$L$100,0,INT((COLUMN(C1)-1)/3)+1),))
B3:D3を選択して範囲コピー。
※Sheet1 値10まで対応 ※データ範囲 100行目まで
けど、ピボットテーブルでいいんでない？ （GobGob） 2014/03/20(木) 07:47 → 07:54修正 → 10:06 さらに修正
ピボットテーブル使った事がないので試してみましたがやはり使い方がよく解りませんでした。
なのでご教授頂いた式で進めたいと思います。
にしても私には難しいです。
もし宜しければ教えて頂けないでしょうか。
まず
B3 =MIN(INDEX((Sheet1!$B$2:$B$100<>$A3)*10^16+INDEX(Sheet1!$C$2:$L$100,0,INT((COLUMN(A1)-1)/3)+1),))
の
<>$A3はA1とA2が空白だから空白部分を除いてって意味ですか？
（ちぃさん） 2014/03/20(木) 11:13
＞INT((COLUMN(A1)-1)/3)+1)
今回は値ごとに「MIN」「AVERAGE」「MAX」の3つの結果を列3つを使って返す。 けど、Sheet1のデータは1列ごとに値が入っている。 このデータを3列おきに返さないといけないための数式っす。
COLUMN関数は列番号を返す。
COLUMN(A1) → A列なので「1」 COLUMN(B1) → B列なので「2」
となりますな。
んで、INT((列番号-1)/3)+1 は 見たまんま 「列番号から1を引いた数値を3で割って小数点を切り捨てて最後に1を足す」って意味っす。
INT((COLUMN(A1)-1)/3)+1 → INT((1-1)/3)+1 → INT(0/3)+1 → 0+1 → 1 INT((COLUMN(B1)-1)/3)+1 → INT((2-1)/3)+1 → INT(1/3)+1 → 0+1 → 1 INT((COLUMN(C1)-1)/3)+1 → INT((3-1)/3)+1 → INT(2/3)+1 → 0+1 → 1
INT((COLUMN(D1)-1)/3)+1 → INT((4-1)/3)+1 → INT(3/3)+1 → 1+1 → 2 INT((COLUMN(E1)-1)/3)+1 → INT((5-1)/3)+1 → INT(4/3)+1 → 1+1 → 2 INT((COLUMN(F1)-1)/3)+1 → INT((6-1)/3)+1 → INT(5/3)+1 → 1+1 → 2
てな感じで「Sheet1の値データを3列おきに返すための位置」を計算するんですな。
＞INDEX(Sheet1!$C$2:$L$100,0,INT((COLUMN(A1)-1)/3)+1)
先ほどの数値を使ってC:L列のデータを範囲で返す数式。行番号が0なので行単位は「範囲」で返すよ。
＞=MIN(INDEX((Sheet1!$B$2:$B$100<>$A3)*10^16+INDEX(Sheet1!$C$2:$L$100,0,INT((COLUMN(A1)-1)/3)+1),))
これは「配列数式」っす。
＞ <>$A3はA1とA2が空白だから空白部分を除いてって意味ですか？
そうでなくて、「A3と一致していないセル」って意味っす。
Sheet1のA列データがSheet2のA3と一致しなければTRUE、出なければFALSEを返す。 んで、TRUEは四則演算すると「1」、FALSEは「0」となるので
TRUE*10^16+値の範囲 → 探したい品名だと値のみ、出なければ値に10の16乗を足した結果が返る。 このデータから最小値を探すんですな。 （GobGob） 2014/03/20(木) 11:58
補足。
10^16を使う理由ですが、
最大値、最小値の基本的な考え方。
(Sheet1のB列がSheet2のA3に等しい行)×値1～
このデータから「MAX」「MIN」を計算するんですが、 この考え方だと「A3と等しくない」データはすべて「0」になってしまう。
MAXは「最大値」を返すので問題ないけど、最小値は「0」を返してしまう。 なので最小値の計算は
(Sheet1のB列がSheet2のA3に等しくない行)×10の16乗＋値1～
として、「等しくない = でかい数値」と処理するためのものっす。 （GobGob） 2014/03/20(木) 12:04
何が駄目なのか私が試すとエラー表示となります。
サンプルをそのまま試してもエラー表示となります。
表示内容
MINは#VALUE
AVERAGEは#DIV/O!
MAXは#VALUE
未熟すぎて申し訳ないです。
何処が駄目なのかご想像のつく限りでかまいませんので御指摘頂けないでしょうか。
どうぞ宜しくお願いいたします。
（ちぃさん） 2014/03/20(木) 13:10
サンプルは問題なくいけました。
いまいちまだ理解できていませんのでまた、つまずきましたら質問をしたいと思いますのでその際にご迷惑でなければご指導頂ければと思います。
（ちぃさん） 2014/03/20(木) 13:18
Sheet1
A B C D E ・・・ 1 日時 品名 値1 値2 値3 2 1/20 aaa 50 102 115 3 1/20 bbb - 50 250 4 1/25 aaa 51 制御 5 エクセルの学校のエラー値一覧で見ましたがご教授いただきました式をどのように変えればいいのかがわかりません。
B3 =MIN(INDEX((Sheet1!$B$2:$B$100<>$A3)*10^16+INDEX(Sheet1!$C$2:$L$100,0,INT((COLUMN(A1)-1)/3)+1),))
C3 =SUMIF(Sheet1!$B$2:B$100,$A3,INDEX(Sheet1!$C$2:$L$10,0,INT((COLUMN(B1)-1)/3)+1))/COUNTIF(Sheet1!$B$2:$B$100,$A3) D3 =MAX(INDEX((Sheet1!$B$2:$B$100=$A3)*INDEX(Sheet1!$C$2:$L$100,0,INT((COLUMN(C1)-1)/3)+1),))
こちらの式のどの部分にどのように変更すればよろしいでしょうか？
同じ質問を何度も申し訳ないのですが、もしよろしければ今一度ご教授いただければ幸いです。
どうぞ宜しくお願いいたします。
（ちぃさん） 2014/03/20(木) 23:04
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.