excel関数2

以前の質問内容追記

Kagakusukiさんに教えていただき
複数条件で抽出し最小値と最大値、平均を出すことが出来ました。
(添付の画像ファイルをご覧下さい)

追加で教えていただきたく、質問をさせていただきます。
sheet1のd列に日付、e列に担当者が入っている場合
Sheet2の最小値と最大値に対して日付、担当者も入れる事は出来ますか？
マクロは組めないので関数で教えて下さい。
よろしくお願いします。

No.1 回答者： keithin 回答日時： 2013/11/14 21:55

＞Sheet2の最小値と最大値に対して日付、担当者も入れる事は出来ますか？

何がしたいのでしょうか。

○指定の日付、担当者に絞ってコードごとの最大・最小・平均を求めたい
　→日付や担当者をどのように指定したいのか、説明不足

○Ａ１ならＡ１の最小値を与える日付、担当者、最大値を与える日付、担当者…を併記したい
　→たとえば２つの行が条件に合致するといった場合にどうしたいのか、説明不足

折角絵を描いたなら、「こういう結果を出したい」という所まで、目に見えるようにして説明してくれると助かります。今後のご参考に。



とりあえずやってください。
作成例：
シート２のＡ１に日付（ピンポイントに、ある１日）
シート２のＢ１に担当者名

に該当するコードＡ１の最小値：
=MIN(IF((Sheet1!A:A=A2)*(Sheet1!D:D=A1)*(Sheet1!E:E=B1)*(Sheet1!C:C>0),Sheet1!C:C))
と記入、コントロールキーとシフトキーを押しながらＥｎｔｅｒで入力。

コードＡ１の最大値：
=MAX(IF((Sheet1!A:A=A2)*(Sheet1!D:D=A1)*(Sheet1!E:E=B1),Sheet1!C:C))
と記入、コントロールキーとシフトキーを押しながらＥｎｔｅｒで入力。

コードＡ１の平均値：
=AVERAGEIFS(Sheet1!C:C,Sheet1!A:A,A2,Sheet1!D:D,A1,Sheet1!E:E,B1)
と記入、ふつーにＥｎｔｅｒで入力。

この回答へのお礼

回答ありがとうございました。
質問内容が分かりにくく大変申し訳ありませんでした。
本日出張の為明日試してみますって

お礼日時：2013/11/15 12:01

No.2 回答者： tom04 回答日時： 2013/11/14 22:19

こんばんは！

前回の質問は判らないのですが、
今回の質問に関して・・・

↓の画像で上側がSheet1で下側のSheet2に表示するとします。
尚、Sheet2のA列コードは入力済みだとします。

Sheet1に作業用の列を1列設けます。
作業列F2セルに
=IF(A2="","",A2&"_"&C2)
という数式を入れオートフィルでしっかり下へコピーしておきます。

次にSheet2のB2セルに
=MIN(IF(Sheet1!A$1:A$1000=A2,Sheet1!C$1:C$1000))
これは配列数式になってしまいますので、Shift＋Ctrl＋Enterで確定！
この画面からコピー＆ペーストする場合は
上記数式をドラッグでコピー＆ペースト　→　F2セルを選択　→　数式バー内に貼り付け　→　そのまま（編集可能なまま）
Shift＋Ctrlキーを押しながらEnterキーで確定！
数式の前後に｛　｝マークが入り配列数式になります。

次にC2セル（配列数式ではありません）に
=INDEX(Sheet1!D:D,MATCH($A2&"_"&$B2,Sheet1!$F:$F,0))
という数式を入れ隣りのD2セルまでコピー！

E2セル（←配列数式です）に
=MAX(IF(Sheet1!A$1:A$1000=A2,Sheet1!C$1:C$1000))
としてShift＋Ctrl＋Enterで確定

F2セル（配列数式ではありません）に
=INDEX(Sheet1!D:D,MATCH($A2&"_"&$E2,Sheet1!$F:$F,0))
という数式を入れ隣りのG2セルまでコピー！

H2セルには
=AVERAGEIF(Sheet1!A:A,A2,Sheet1!C:C)
という数式を入れておきます。

日付列の書式は「日付」にしておいて、
最後にB2～H2セルを範囲指定　→　H2セルのフィルハンドルで下へコピー！

これで画像のような感じになります。

※　極端にデータ量が多い場合は配列数式はおススメしません。
作業列を増やすなりして他の方法を考える必要があります。m(_ _)m

No.3 回答者： tom04 回答日時： 2013/11/14 22:38

No.2です！

前回の投稿で誤記がありました。
すでにお判りかと思いますが、

＞上記数式をドラッグでコピー＆ペースト　→　F2セルを選択　→　数式バー内に貼り付け・・・
は
＞F2セルではなく、B2セルの間違いです。

何度も失礼しました。m(_ _)m

No.4 回答者： kagakusuki 回答日時： 2013/11/15 06:41

>追加で教えていただきたく、質問をさせていただきます。

　その様な場合には、下記の様に前回の質問が掲載されているサイトのページのURLを記載する様にして下さい。

【参考URL】
　質問No.8319482 excel関数 | 【OKWave】
　　http://oshiete.goo.ne.jp/qa/8319482.html


　偶々、前回回答した私が本御質問を見かけたから良かったものの、大部分の回答者は前回の質問がどの様なものであったのかという事や、どの様な方法で解決したのかという事など知る由も無いのですから、御質問欄に掲載されている添付画像から得られる情報だけでは、何が行われているのか判断する事は、相当難しいと思います。
　もし、私が本質問の存在を見逃してしまっていたならば、他の回答者の方々には、B列が空欄となっているデータのみを集計しているとは判らないために、誤った回答が寄せられてしまい、問題を解決する事が出来なかった恐れもあります。


　さて本題ですが、Sheet3で使用する作業列に関して、既存のものに加えてE列とF列の2列を新たな作業列として使用するものとします。
　又、Sheet2の表は、日付と担当者名を表示させる様にする都合上、若干レイアウトを変更する必要がありますので、A列にコード番号、B列に最小値、C列に最小値が得られた日付、D列に最小値が得られた時の担当者名、E列に最大値、F列に最大値が得られた日付、G列に最大値が得られた時の担当者名、H列に平均値をそれぞれ表示するものとし、Sheet2の1行目と2行目は項目名を入力するために使用し、実際のデータは3行目以下に表示させるものとします。
　尚、同一コードで文字列欄が空欄となっているものの中において、数字欄が最大値或いは最小値となっている箇所が複数個所存在する場合には、日付が最も

　まず、Sheet3のA2セルに入力する関数を次のものに変更してから、Sheet3のA2セルをコピーして、Sheet3のA3以下に貼り付けて下さい。

=IF(AND(INDEX(Sheet1!$A:$A,ROW())<>"",INDEX(Sheet1!$B:$B,ROW())="",ISNUMBER(INDEX(Sheet1!$C:$C,ROW())),ISNUMBER(1/(YEAR(INDEX(Sheet1!$D:$D,ROW()))>1904))),COUNTIF(Sheet1!$A:$A,"<"&INDEX(Sheet1!$A:$A,ROW()))+COUNTIF(Sheet1!$A$1:INDEX(Sheet1!$A:$A,ROW()),INDEX(Sheet1!$A:$A,ROW())),"")

　尚、Sheet3のB列～D列の関数は、そのままで結構です。（本当はA列の関数も変更せずとも、通常は正しく動作する筈なのですが、万が一、最小値や最大値となっている行において、日付が入力されていなかったり、日付ではなく文字列が入力されていたりしますと、正しい結果が得られなくなる恐れがあるため、念の為に変更する事にしました）
　次に、Sheet3のE2セルに次の関数を入力して下さい。

=IF(AND(ISNUMBER(FIND("◆",$C2)),ISNUMBER($D2)),IF(ISNUMBER(1/(YEAR(INDEX(Sheet1!$D:$D,MATCH(ROWS($2:2),$B:$B,0)))>1904)),LEFT($C2,FIND("◆",$C2))&$D2&"◇"&TEXT(INDEX(Sheet1!$D:$D,MATCH(ROWS($2:2),$B:$B,0)),"yyyy/mm/dd"),""),"")

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

=IF($E2="","",COUNTIF($E:$E,"<"&$E2)-COUNTIF($E:$E,"<*?"))

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


※まだ途中なのですが、サイトのシステムの調子がおかしくなっている様で、回答欄に入力可能な文字数が通常よりも少なくなっているため、全てを書き込む事が出来ません。
　ですから、残りは又後で投稿させて頂きます。

No.5 ベストアンサー 回答者： kagakusuki 回答日時： 2013/11/15 06:45

　回答No.4の続きです。

　次に、Sheet2のA1セルに「コード」、B1セルに「最小値」、E1セルに「最大値」、Hセルに「平均値」、B2セルとE2セルに「値」、C2セルとF2セルに「日付」、D2セルとG2セルに「担当者」と入力して下さい。
　次に、Sheet2のB3セルに次の関数を入力して下さい。（レイアウト変更前のB2セルの関数と同じ）

=IF(INDEX($A:$A,ROW())="","",IF(COUNTIF(Sheet3!$C:$C,INDEX($A:$A,ROW())&"◆*"),MIN(INDEX(Sheet3!$D:$D,MATCH(INDEX($A:$A,ROW())&"◆1",Sheet3!$C:$C,0)):INDEX(Sheet3!$D:$D,MATCH(INDEX($A:$A,ROW())&"◆"&COUNTIF(Sheet3!$C:$C,INDEX($A:$A,ROW())&"◆*"),Sheet3!$C:$C,0))),"-"))

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

=IF(ISNUMBER(INDEX($B:$B,ROW())),IF(COUNTIF(Sheet3!$E:$E,INDEX($A:$A,ROW())&"◆"&INDEX($B:$B,ROW())&"◇*"),INDEX(Sheet1!$D:$D,MATCH(MATCH(COUNTIF(Sheet3!$E:$E,"<"&INDEX($A:$A,ROW())&"◆"&INDEX($B:$B,ROW())&"◇")-COUNTIF(Sheet3!$E:$E,"<*?"),Sheet3!$F:$F,0)-ROW(Sheet3!$E$2)+1,Sheet3!$B:$B,0)),""),"")

　次に、Sheet2のC3セルの書式設定の表示形式を[日付]に設定して下さい。
　次に、Sheet2のD3セルに次の関数を入力して下さい。

=IF(ISNUMBER(INDEX($C:$C,ROW())),IF(COUNTIF(Sheet3!$E:$E,INDEX($A:$A,ROW())&"◆"&INDEX($B:$B,ROW())&"◇"&TEXT(INDEX($C:$C,ROW()),"yyyy/mm/dd")),INDEX(Sheet1!$E:$E,MATCH(MATCH(INDEX($A:$A,ROW())&"◆"&INDEX($B:$B,ROW())&"◇"&TEXT(INDEX($C:$C,ROW()),"yyyy/mm/dd"),Sheet3!$E:$E,0)-ROW(Sheet3!$E$2)+1,Sheet3!$B:$B,0)),""),"")

　次に、Sheet2のE3セルに次の関数を入力して下さい。（レイアウト変更前のC2セルの関数と同じ）

=IF(INDEX($A:$A,ROW())="","",IF(COUNTIF(Sheet3!$C:$C,INDEX($A:$A,ROW())&"◆*"),MAX(INDEX(Sheet3!$D:$D,MATCH(INDEX($A:$A,ROW())&"◆1",Sheet3!$C:$C,0)):INDEX(Sheet3!$D:$D,MATCH(INDEX($A:$A,ROW())&"◆"&COUNTIF(Sheet3!$C:$C,INDEX($A:$A,ROW())&"◆*"),Sheet3!$C:$C,0))),"-"))

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

=IF(ISNUMBER(INDEX($E:$E,ROW())),IF(COUNTIF(Sheet3!$E:$E,INDEX($A:$A,ROW())&"◆"&INDEX($E:$E,ROW())&"◇*"),INDEX(Sheet1!$D:$D,MATCH(MATCH(COUNTIF(Sheet3!$E:$E,"<"&INDEX($A:$A,ROW())&"◆"&INDEX($E:$E,ROW())&"◇")-COUNTIF(Sheet3!$E:$E,"<*?"),Sheet3!$F:$F,0)-ROW(Sheet3!$E$2)+1,Sheet3!$B:$B,0)),""),"")

　次に、Sheet2のF3セルの書式設定の表示形式を[日付]に設定して下さい。
　次に、Sheet2のG3セルに次の関数を入力して下さい。

=IF(ISNUMBER(INDEX($F:$F,ROW())),IF(COUNTIF(Sheet3!$E:$E,INDEX($A:$A,ROW())&"◆"&INDEX($E:$E,ROW())&"◇"&TEXT(INDEX($F:$F,ROW()),"yyyy/mm/dd")),INDEX(Sheet1!$E:$E,MATCH(MATCH(INDEX($A:$A,ROW())&"◆"&INDEX($E:$E,ROW())&"◇"&TEXT(INDEX($F:$F,ROW()),"yyyy/mm/dd"),Sheet3!$E:$E,0)-ROW(Sheet3!$E$2)+1,Sheet3!$B:$B,0)),""),"")

　次に、Sheet2のH3セルに次の関数を入力して下さい。（レイアウト変更前のD2セルの関数と同じ）

=IF(INDEX($A:$A,ROW())="","",IF(COUNTIF(Sheet3!$C:$C,INDEX($A:$A,ROW())&"◆*"),AVERAGE(INDEX(Sheet3!$D:$D,MATCH(INDEX($A:$A,ROW())&"◆1",Sheet3!$C:$C,0)):INDEX(Sheet3!$D:$D,MATCH(INDEX($A:$A,ROW())&"◆"&COUNTIF(Sheet3!$C:$C,INDEX($A:$A,ROW())&"◆*"),Sheet3!$C:$C,0))),"-"))

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

　以上です。
　因みに、Sheet2のA3セルに次の関数を入力してから、Sheet2のA3セルをコピーして、Sheet2のA4以下に貼り付けますと、Sheet1のA列に入力されているコード番号の内、B列が空欄となっているもののみを抽出して、コード番号を重複無しに昇順に並べ替えたものが表示されます。（A列に存在するコードであっても、B列が空欄となっている箇所が1つも存在しないものに関しては表示されません）

=IF(ROWS($3:3)>COUNTIF(Sheet3!$C:$C,"*◆1"),"",SUBSTITUTE(VLOOKUP("*◆1",IF(ROWS($3:3)=1,Sheet3!$C:$C,INDEX(Sheet3!$C:$C,MATCH(INDEX(A:A,ROW()-1)&"◆1",Sheet3!$C:$C,0)+1):INDEX(Sheet3!$C:$C,ROW(Sheet3!$C$2)+COUNT(Sheet3!$B:$B))),1,FALSE),"◆1",))

この回答へのお礼

回答ありがとうございました。
質問内容に前回の内容を記載せず、非常に分かりにくく、大変申し訳ありませんでした。urlのリンクありがとうございます。
本日出張の為明日試してみます。

お礼日時：2013/11/15 11:59

No.6 回答者： tom04 回答日時： 2013/11/15 08:37

No.2・3です！

No.4さんの投稿を拝見して・・・
前回の質問内容を確認しました。
なぜB列に空白セルとそうでないセルがあるのかな？
という疑問を持ちながらの前回の投稿でした。

結局B列が空白で、各コードの最小値・日付・担当と最大値・日付・担当を表示すれば良い訳ですよね？
平均に関してもB列が空白セルで良いという解釈で・・・

No.2の配置そのままを利用します。
（作業列の数式もそのまま）

Sheet2のB2セル（←配列数式です）に
=MIN(IF(Sheet1!A$1:A$1000=A2,IF(Sheet1!B$1:B$1000="",Sheet1!C$1:C$1000)))
としてShift＋Ctrl＋Enterで確定！

C2セル（配列数式ではありません）はそのままの数式でOKです。
それと隣りのD2セルまでコピー！

E2セル（←配列数式）に
=MAX(IF(Sheet1!A$1:A$1000=A2,IF(Sheet1!B$1:B$1000="",Sheet1!C$1:C$1000)))
としてShift＋Ctrl＋Enterで確定！

F2セルは前回のままG2セルまでコピー！

H2セル（B列が空白の場合の平均としています）は
=AVERAGEIFS(Sheet1!C:C,Sheet1!A:A,A2,Sheet1!B:B,"")

という数式を入れB2～H2セルを範囲指定　→　H2セルのフィルハンドルで下へコピー！
おそらくこれで大丈夫だと思います。m（_ _）m

この回答へのお礼

回答ありがとうございました。
質問内容に前の質問を記載せず、分かりにくく本当に申し訳ありませんでした。
本日出張の為、明日試してみたいと思います。

お礼日時：2013/11/15 11:56