Excel2010の計算

質問

Excel2010で、同じ「2001」（A2～A5）、「1」（B2～B5）、「1」（C2～C5）、「0:00」（D2～D5）（時刻）でも、E2～E5など数値が異なります。（これはSheet1、添付画像左側）

これを、Sheet2（添付画像右側）に同じ日時で、
2001：A2、薄い黄色塗りつぶし（同じ「2001」「1」「1」「0:00」の中で最も上の数値）
1：B2、薄い黄色塗りつぶし（同じ「2001」「1」「1」「0:00」の中で最も上の数値）
1：C2、薄い黄色塗りつぶし（同じ「2001」「1」「1」「0:00」の中で最も上の数値）
0:00：D2、薄い黄色塗りつぶし（同じ「2001」「1」「1」「0:00」の中で最も上の数値）
101：E2、赤塗りつぶし（同じ「2001」「1」「1」「0:00」の中で最も上の数値）
110：F2、黄色塗りつぶし（同じ「2001」「1」「1」「0:00」の中で最も高い数値）
100：G2、青塗りつぶし（同じ「2001」「1」「1」「0:00」の中で最も低い数値）
106：H2、緑塗りつぶし（同じ「2001」「1」「1」「0:00」の中で最も下の数値）
15：I2、薄い青塗りつぶし（同じ「2001」「1」「1」「0:00」の中で最も高い数値）

同様に、「2001、1、1、0:01」、「2001、1、1、0:02」～も同じような計算をしたいのですが、計算式（関数）を教えてください。

説明下手ですいませんが、回答よろしくお願いします。

回答No.3

kagakusuki · ベストアンサー

御質問文の添付画像から推測致しますと、

>2001：A2、薄い黄色塗りつぶし（同じ「2001」「1」「1」「0:00」の中で最も上の数値）
>1：B2、薄い黄色塗りつぶし（同じ「2001」「1」「1」「0:00」の中で最も上の数値）
>1：C2、薄い黄色塗りつぶし（同じ「2001」「1」「1」「0:00」の中で最も上の数値）
>0:00：D2、薄い黄色塗りつぶし（同じ「2001」「1」「1」「0:00」の中で最も上の数値）
>101：E2、赤塗りつぶし（同じ「2001」「1」「1」「0:00」の中で最も上の数値）
>110：F2、黄色塗りつぶし（同じ「2001」「1」「1」「0:00」の中で最も高い数値）
>100：G2、青塗りつぶし（同じ「2001」「1」「1」「0:00」の中で最も低い数値）
>106：H2、緑塗りつぶし（同じ「2001」「1」「1」「0:00」の中で最も下の数値）
>15：I2、薄い青塗りつぶし（同じ「2001」「1」「1」「0:00」の中で最も高い数値）

ではなく、
「Sheet1には、A列に西暦年数、B列に月、C列に日、D列に時刻、E列～I列に各種の数値データが入力されていて、
Sheet1のデータを基にして、Sheet2のA列に西暦年数、B列に月、C列に日、D列に時刻を重複の無い様に表示し、
Sheet2のE列には、Sheet1において同じ日時の行範囲のE列の数値データの中で、最も上の行にあるデーターを表示し、
Sheet2のF列には、Sheet1において同じ日時の行範囲のF列の数値データの中の、最大値を表示し、
Sheet2のG列には、Sheet1において同じ日時の行範囲のG列の数値データの中の、最小値を表示し、
Sheet2のH列には、Sheet1において同じ日時の行範囲のH列の数値データの中で、最も下の行にあるデーターを表示し、
Sheet2のI列には、Sheet1において同じ日時の行範囲のI列の数値データの中の、最大値を表示する。」
という事なのではないでしょうか？

　そのための方法の一例は、以下の様なものです。
　今仮に、Sheet3のA列を作業列として使用するものとします。

　まず、Sheet3のA2セルに次の関数を入力して下さい。

=IF(AND(ISNUMBER((Sheet1!$A2&"/"&Sheet1!$B2&"/"&Sheet1!$C2)/(Sheet1!$D2=TEXT(Sheet1!$D2,"h:m:s")+0)),Sheet1!$D2<>""),(Sheet1!$A2&"/"&Sheet1!$B2&"/"&Sheet1!$C2)+Sheet1!$D2,"")

　次に、Sheet3のA2セルをコピーして、Sheet3のA3以下に貼り付けて下さい。

　次に、Sheet2のA2セルに次の関数を入力して下さい。

=IF(COUNT(Sheet3!$A:$A),IF(IF(ISNUMBER(($A1&"/"&$B1&"/"&$C1)+$D1),DATE($A1,$B1,$C1)+$D1=MAX(Sheet3!$A:$A),ROWS($2:2)>1),"",YEAR(SMALL(Sheet3!$A:$A,IF(ROWS($2:2)=1,1,COUNTIF(Sheet3!$A:$A,"<="&DATE($A1,$B1,$C1)+$D1)+1)))),"")

　次に、Sheet2のB2セルに次の関数を入力して下さい。

=IF(ISNUMBER($A2),MONTH(SMALL(Sheet3!$A:$A,IF(ROWS($2:2)=1,1,COUNTIF(Sheet3!$A:$A,"<="&DATE($A1,$B1,$C1)+$D1)+1))),"")

　次に、Sheet2のC2セルに次の関数を入力して下さい。

=IF(ISNUMBER($A2),DAY(SMALL(Sheet3!$A:$A,IF(ROWS($2:2)=1,1,COUNTIF(Sheet3!$A:$A,"<="&DATE($A1,$B1,$C1)+$D1)+1))),"")

　次に、Sheet2のD2セルに次の関数を入力して下さい。

=IF(ISNUMBER($A2),MOD(SMALL(Sheet3!$A:$A,IF(ROWS($2:2)=1,1,COUNTIF(Sheet3!$A:$A,"<="&DATE($A1,$B1,$C1)+$D1)+1)),1),"")

　次に、Sheet2のE2セルに次の関数を入力して下さい。

=IF(ISNUMBER($A2),INDEX(Sheet1!$E:$E,MATCH(DATE($A2,$B2,$C2)+$D2,Sheet3!$A:$A,0)),"")

　次に、Sheet2のF2セルに次の関数を入力して下さい。

=IF(ISNUMBER($A2),MAX(INDEX(Sheet1!$F:$F,MATCH(DATE($A2,$B2,$C2)+$D2,Sheet3!$A:$A,0)):INDEX(Sheet1!$F:$F,MATCH(DATE($A2,$B2,$C2)+TEXT($D2,"h:m")+"0:01"-0.0000001,Sheet3!$A:$A))),"")

　次に、Sheet2のG2セルに次の関数を入力して下さい。

=IF(ISNUMBER($A2),MIN(INDEX(Sheet1!$G:$G,MATCH(DATE($A2,$B2,$C2)+$D2,Sheet3!$A:$A,0)):INDEX(Sheet1!$G:$G,MATCH(DATE($A2,$B2,$C2)+TEXT($D2,"h:m")+"0:01"-0.0000001,Sheet3!$A:$A))),"")
H2=IF(ISNUMBER($A2),INDEX(Sheet1!$H:$H,MATCH(DATE($A2,$B2,$C2)+TEXT($D2,"h:m")+"0:01"-0.0000001,Sheet3!$A:$A)),"")

　次に、Sheet2のI2セルに次の関数を入力して下さい。

=IF(ISNUMBER($A2),MAX(INDEX(Sheet1!$I:$I,MATCH(DATE($A2,$B2,$C2)+$D2,Sheet3!$A:$A,0)):INDEX(Sheet1!$I:$I,MATCH(DATE($A2,$B2,$C2)+TEXT($D2,"h:m")+"0:01"-0.0000001,Sheet3!$A:$A))),"")

　次に、Sheet2のA2～I2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。

　以上です。

お礼

回答ありがとうございます。

自分の説明が不十分だったと思うのですが、察していただきありがとうございます。
その通りでございます。

複雑な関数は苦手なため、理解不足ですいませんがそのままコピーさせて試したいと思います。

ちゃんとしたお礼ができず申し訳ありません。

ありがとうございました。

回答No.2

某HN クロメート(Chromate)（@CoalTar）

D列秒があることを嫌って
J2セル 作業列
=DATE(A2,B2,C2)+TEXT(D2,"h:mm")
フィルハンドル ダブルクリック

作業列(時刻)が昇順であるとして
K1セル 1
K2セル
=IF(J2<>J1,SUM(K1,1),K1)
フィルハンドル ダブルクリック

M列 作業列
M1セル =MAX(K:K)
M2セル
=IF($M$1<ROW()-1,"",MATCH(ROW()-1,K:K)+1)
下へ オートフィル

N2列 最上
=IF($M$1<=ROW()-1,"",INDEX(A:A,$M2))
R列まで 右へ 下へ オートフィル

S2セル 最大値
=IF($M$1<=ROW()-1,"",MAX(INDEX(G:G,$M2):INDEX(G:G,$M3-1)))

T2セル 最小値
=IF($M$1<=ROW()-1,"",MIN(INDEX(H:H,$M2):INDEX(H:H,$M3-1)))

U2セル 最下
=IF($M$1<=ROW()-1,"",INDEX(I:I,$M3-1))

M:U列を切り取って 別シートへ貼り付け
作業セルを頻繁に使うことで、計算速度を高めてみました。

お礼

回答ありがとうございます。

複雑な関数は苦手なため、理解不足ですいませんがそのままコピーさせて試したいと思います。

計算速度を高めた方法で教えていただきありがとうございます。

ちゃんとしたお礼ができず申し訳ありません。

ありがとうございました。

回答No.1

KURUMITO

式が複雑になりますのでシート1にお示しのデータが有るとしてシート1のK列を作業列としてK2セルには次の式を入力して下方にドラッグコピーします。

=IF(AND(D2="",D1=""),"",IF(ROW(A1)=1,1,IF(OR(D2<>D1,AND(D2="",D1<>"")),MAX(K$1:K1)+1,"")))

お求めの表をシート2に表示させることにしてシート2のA2セルには次の式を入力してI2セルまで横にドラッグコピーしたのちに下方にもドラッグコピーします。

=IF(ROW(A1)>MAX(Sheet1!$K:$K)-1,"",IF(COLUMN(A1)<=COLUMN($E1),INDEX(Sheet1!$A:$I,MATCH(ROW(A1),Sheet1!$K:$K,0),COLUMN(A1)),IF(OR(COLUMN(A1)=COLUMN($F1),COLUMN(A1)=COLUMN($I1)),MAX(INDEX(Sheet1!A:A,MATCH(ROW(A1),Sheet1!$K:$K,0)):INDEX(Sheet1!A:A,MATCH(ROW(A1)+1,Sheet1!$K:$K,0)-1)),IF(COLUMN(A1)=COLUMN($G1),MIN(INDEX(Sheet1!A:A,MATCH(ROW(A1),Sheet1!$K:$K,0)):INDEX(Sheet1!A:A,MATCH(ROW(A1)+1,Sheet1!$K:$K,0)-1)),IF(COLUMN(A1)=COLUMN($H1),INDEX(Sheet1!$H:$H,MATCH(ROW(A1)+1,Sheet1!$K:$K,0)-1),"")))))

お礼

回答ありがとうございます。

複雑な関数は苦手なため、理解不足ですいませんがそのままコピーさせて試したいと思います。

ちゃんとしたお礼ができず申し訳ありません。

ありがとうございました。