[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『日付毎の最大値』(うりぼー)
以下のように、A列に日付、B列に値があります。
20061229 32855
20061229 42984
20061229 1
20061229 4
20061228 33083
20061228 42367
20061228 1
20061227 33521
20061227 42568
20061226 32413
20061226 41878
20061226 4
20061222 32259
20061222 41925
20061222 1
20061222 4
これを以下のような形式で同じシートに日付ごとの最大値を抽出するにはどのようにすべきなのでしょうか?
20061229 42984
20061228 42367
20061227 42568
20061226 41878
20061222 41925
20061222 4
皆様のお知恵をお貸し頂きたく存じます。
ピボットテーブルで最大値 http://www2.odn.ne.jp/excel/waza/pivot.html#SEC4 が参考になりませんか? (じゅんじゅん)
今度は、抽出ではなく、元データの最大値に色付けするにはどのような方法があるのでしょうか?
A列・B列に対して抽出した表を元に条件付き書式
仮にA1〜B10を選択、条件付き書式、「数式が」で =AND(COUNTIF(抽出した日付のセル範囲,$A1),COUNTIF(抽出した最大値のセル範囲,$B1)) で書式設定
抽出した日付のセル範囲 例えば $D$1:$D$5 〃 最大値のセル範囲 〃 $E$1:$E$5 と言うのではどうでしょうか?
>元データの最大値に色付けするには 日付に色づけが必要なければ、セル選択をB1〜B10で条件付き書式では。 (じゅんじゅん)
解決したようですが、老婆心で
(じゅんじゅん)さんの回答では 例題の場合でB1が42367の場合おかしくなりませんか?
B列に対して抽出した表を元に条件付き書式 セル選択をB1〜B10で条件付き書式 =B1=SUMIF($D:$D,A1,$E:$E)
By しげちゃん
By しげちゃんさん、フォローありがとうございます。
>(じゅんじゅん)の回答では >例題の場合でB1が42367の場合おかしくなりませんか? B1がと言うより、違う日で同じ最大値があるとなりますね。すいません。
>=AND(COUNTIF(抽出した日付のセル範囲,$A1),COUNTIF(抽出した最大値のセル範囲,$B1)) で書式設定
=MATCH($A1,$D$1:$D$5,0)=MATCH($B1,$E$1:$E$5,0) で書式設定はどうでしょうか? (じゅんじゅん)
>B1がと言うより、違う日で同じ最大値があるとなりますね。すいません。 どのように表現すればいいのか迷い、記載しませんでした。 「B列の値がその日の最大値ではなく、他の日付の最大値と同じ場合」と言うことでしょうかね
>=MATCH($A1,$D$1:$D$5,0)=MATCH($B1,$E$1:$E$5,0) で書式設定はどうでしょうか? 最大値に重複があった場合にはだめでしょうね
例 日付 最大値 20061229 42984 20061228 42984 20061227 42568 この場合20061228の最大値が条件に当てはまらなくなります。
By しげちゃん
>>=MATCH($A1,$D$1:$D$5,0)=MATCH($B1,$E$1:$E$5,0) で書式設定はどうでしょうか? >最大値に重複があった場合にはだめでしょうね 一般関数も中々難しいのですね。 (じゅんじゅん)
じつは、もともとのデータは以下のようになっていまして、最大値を含む行を抽出したかったのです。
最大値の列がわかればあとは、自分で調べようとおもっていたのですが、どのようにすれば良いのかわかりませんでした。
迷える子羊をお救いください。(一番右の数字が最大値です。)
20061229 9 2007 15870 16015 32855
20061229 12 2007 15950 16040 42984
20061229 3 2008 0 0 1
20061229 9 2007 15835 15910 4
20061228 12 2007 15865 15950 33083
20061228 3 2008 0 0 42367
20061228 9 2007 16000 16000 1
20061227 12 2007 15850 16000 33521
20061227 3 2008 0 0 42568
20061226 9 2007 15740 15800 32413
20061226 12 2007 15870 15870 41878
20061226 3 2008 0 0 4
20061222 9 2007 0 15840 32259
20061222 12 2007 15810 15810 41925
20061222 3 2008 0 0 1
20061222 9 2007 16170 16240 4
うりぼー
>一番右の数字が最大値です。 一番右とは、F列という事ですか? セルの配置も提示された方が宜しいかと。 (じゅんじゅん)
A列とB列で行なった作業を、A列とF列で行なうのでは、ダメでしょうか? (じゅんじゅん)
少し確認させてください。
最初の御質問は >日付ごとの最大値を抽出 でしたが、それは必要なのですか?
次に >一番右の数字が最大値です 常にF列にのみ最大値があるのですか? それとも、常に最終列(今回はたまたまF列)と言う事ですか? また、行毎に最終列が違ったりしますか?
以下は質問ではないのですが 1.実際には項目があって、実際には特定の行から始まっていると思います。 それらを「仮」でも良いので、決定してください。 (話しがはやくなります。) 2.コメント記入の際は、その都度ご署名を御願いします。 (自動的にはつきませんので。) 3.行頭に半角スペースを入れると、改行がそのまま表示されます。 A←ココに半角スペースです。
貴方が不必要だと思う情報でも、貴方が公開出来る最大の情報を 教えて下さい。 より実状にあったアドバイスがもらえると思いますよ。 宜しく御願いします。
(HANA)
>日付ごとの最大値を抽出 でしたが、それは必要なのですか? うりぼー>抽出に必要です。
次に >一番右の数字が最大値です 常にF列にのみ最大値があるのですか? それとも、常に最終列(今回はたまたまF列)と言う事ですか? うりぼー>最終列です。簡略化のため、F列を最終列にしました。
また、行毎に最終列が違ったりしますか? うりぼー>すべての行で最終列は同じです。
以下は質問ではないのですが 1.実際には項目があって、実際には特定の行から始まっていると思います。 それらを「仮」でも良いので、決定してください。 (話しがはやくなります。)
うりぼー>すみません。以下の通りです。
日付 月 年 始値 高値 最大値 20061229 9 2007 15870 16015 32855 20061229 12 2007 15950 16040 42984 20061229 3 2008 0 0 1 20061229 9 2007 15835 15910 4 20061228 12 2007 15865 15950 33083 20061228 3 2008 0 0 42367 20061228 9 2007 16000 16000 1 20061227 12 2007 15850 16000 33521 20061227 3 2008 0 0 42568 20061226 9 2007 15740 15800 32413 20061226 12 2007 15870 15870 41878 20061226 3 2008 0 0 4 20061222 9 2007 0 15840 32259 20061222 12 2007 15810 15810 41925 20061222 3 2008 0 0 1 20061222 9 2007 16170 16240 4
(うりぼー)
>>日付ごとの最大値を抽出 >抽出に必要です。 これは、 「どこかにピボットテーブルを使用して 最大値を求める」 と言う事ですよね?
>>常にF列にのみ最大値があるのですか? >最終列です。簡略化のため、F列を最終列にしました。 御提示の表ですと「常に(便宜的に)F列に最大値」 と言えそうな気がしますが、どうなのですか? もう少し言葉を足して質問させてもらうとすると ●最終列の列は固定ですか?変動しますか? と言う質問です。
じゅんじゅんさんも言っておられますが >A列とB列で行なった作業を、A列とF列で行なうの で良いと思いますが、何処が問題で 止まっているのか教えて下さい。
○最大値を求める所ですか? ○条件付き書式の設定の所ですか? ○条件付き書式を特定のセルだけではなく 行全てに設定する所ですか? ○現在提示されている方法では全く上手く行かないのですか?
(HANA)
●最終列の列は固定ですか?変動しますか?
うりぼー>固定です。
F列が最大値の行のすべての情報が必要になります。
例)日付が20061229であれば、最大値は42984となります。
しかし、その行の月/年/始値/高値の情報も必要になります。
今度はどうでしょうか。。。。。。(汗)
うりぼー
再度掲載します。 お気を悪くなさらないで下さい。
>何処が問題で止まっているのか教えて下さい。 > >○最大値を求める所ですか? >○条件付き書式の設定の所ですか? >○条件付き書式を特定のセルだけではなく > 行全てに設定する所ですか? >○現在提示されている方法では全く上手く行かないのですか?
(HANA)
>何処が問題で止まっているのか教えて下さい。
> >○最大値を求める所ですか? うりぼー>ここは問題なしです。
>○条件付き書式の設定の所ですか? うりぼー>ここは問題なしです。
>○条件付き書式を特定のセルだけではなく > 行全てに設定する所ですか? うりぼー>最大値を含む行すべてを色つきにする方法はわかりません。 ただ、これがわかっても、最大値を含む行すべての列情報が必要になります。
>○現在提示されている方法では全く上手く行かないのですか? うりぼー>うまくいかないと思います。
宜しくお願い致します。
うりぼー
>>○最大値を求める所ですか? >ここは問題なしです。 作成したピボットテーブルの範囲を教えて下さい。 別シートに作成したなら、そのシート名も合わせて。
>>○条件付き書式の設定の所ですか? >ここは問題なしです。 この時設定した条件と、その数式が入った セル番地を教えて下さい。
>最大値を含む行すべての列情報が必要になります。 この部分がちょっとよく分からないのですが、 例えば、F3が最大値の場合 A3:F3(F3を含む行)に色が付けばよいのか A3:F3とF列(更に列にも)色が付くのか・・・? (単なる書き間違いですかね?でしたら良いのですが。)
私やじゅんじゅんさんやしげちゃんさんの想像があっていれば この方法で上手く行くと思うんですけどね。 何か想像違いがありそうな点が分かったら、直ぐに教えて下さい。 現在の方法で上手く行くかどうか、再検討しますので。
(HANA)
>>○最大値を求める所ですか? >ここは問題なしです。 作成したピボットテーブルの範囲を教えて下さい。 うりぼー>まず、F列の最大値を下記のように、B列に移動して テーブル範囲を$A$1:$B$17としました。 日付 最大値 20061229 32855 20061229 42984 20061229 1 20061229 4 20061228 33083 20061228 42367 20061228 1 20061227 33521 20061227 42568 20061226 32413 20061226 41878 20061226 4 20061222 32259 20061222 41925 20061222 1 20061222 4
ピボットテーブルの結果は以下の通りです。
日付 最大値 20061222 41925 20061226 41878 20061227 42568 20061228 42367 20061229 42984
>>○条件付き書式の設定の所ですか? >ここは問題なしです。 うりぼー>上記のピボットテーブルの結果を元データと同じシートにコピーしました。 日付(A) 月(B) 年(C) 始値(D) 高値(E) 最大値(F) 日付(I) 最大値(J) 20061229 9 2007 15870 16015 32855 20061222 41925 20061229 12 2007 15950 16040 42984 20061226 41878 20061229 3 2008 0 0 1 20061227 42568 20061229 9 2007 15835 15910 4 20061228 42367 20061228 12 2007 15865 15950 33083 20061229 42984 20061228 3 2008 0 0 42367 20061228 9 2007 16000 16000 1 20061227 12 2007 15850 16000 33521 20061227 3 2008 0 0 42568 20061226 9 2007 15740 15800 32413 20061226 12 2007 15870 15870 41878 20061226 3 2008 0 0 4 20061222 9 2007 0 15840 32259 20061222 12 2007 15810 15810 41925 20061222 3 2008 0 0 1 20061222 9 2007 16170 16240 4
この時設定した条件と、 うりぼー>=F2=SUMIF($I:$I,A2,$J:$J)です。 その数式が入った セル番地を教えて下さい。 F2には、=F2=SUMIF($I:$I,A2,$J:$J) F3には、=F3=SUMIF($I:$I,A2,$J:$J) . . . F17には、=F3=SUMIF($I:$I,A2,$J:$J)1としました。
>最大値を含む行すべての列情報が必要になります。 この部分がちょっとよく分からないのですが、 例えば、F3が最大値の場合 A3:F3(F3を含む行)に色が付けばよいのか うりぼー>表現が良くなかったですね、すみません。こちらの解釈になります。 色をつけた後に、その行の抽出も行いたく思っています。 その後に自分で色付き部分の抽出を試みたのですがうまくいきませんでした。 よって、色をつけることよりも、最大値を含む行の抽出ができれば目的の結果が得られます。 なにとぞ、よろしくお願い致します。
うりぼー
分かりました。 まず、一番の問題点は 条件付き書式の条件です。 F2セルに設定した↓の式は _____________________1 =F2=SUMIF($I:$I,A2,$J:$J) ~~~~~~~~~~~~~~~~~~~~~~~~2 1.ピボットテーブル結果であるI列(日付)から 該当行の日付 【A2】 を探しだし、そのJ列(最大値) を求め 2.該当行の数値 【F2】 と見比べます。 これが同じであれば「TRUE」違えば「FALSE」となります。
ですから、F3セルに入れる数式は =F3=SUMIF($I:$I,A3,$J:$J) ~~ ~~ 2か所の参照先が変わっている必要があります。
さて、最終目的は >色をつけることよりも、最大値を含む行の抽出 と言う事ですので、途中の手順も合わせて 以下のようにしてみるのはどうでしょう。 【1】ピボットテーブルで最大値を求める 1.元データの範囲内のどこかのセルをアクティブにして メニュー・データ(D) →ピボットテーブルとピボットグラフ レポート(P) [次へ]→[次へ](範囲等正しい事を確認してください。)
2.ウィザードの「3/3」で[ レイアウト(L) ] 行(R) に [日付] データ(D) に [ 最大値 ] をおとすと、データ(D)が[ 合計/最大値 ]となるので これをダブルクリック。 集計の方法(S) で、「最大値」を選んでください。 データ(D)が[ 最大値/最大値 ]となれば [ OK ]を押します。
3.ウィザードの「3/3」に戻りますので、 ピボットテーブル レポートの作成先を ●既存のワークシート(E) [ I1 田] として、[ 完了 ] これで、現在御提示の形式になると思います。 (データを あっちへやったり、こっちへやったりしなくても。) 【2】最大値となる行に表示をつける。 G列を作業列にしてください。 G2に =IF(F2=SUMIF($I:$I,A2,$J:$J),"○","")を入れて 下にフィルドラッグ(G2をコピーして貼り付け)すると 該当の行に「○」がつきます。 この行のみが必要であればオートフィルタで絞り込んでください。 【3】該当行に色を付ける。 条件付き書式で、A2セルの書式設定を 条件1 数式が [ =$G2="○" ] [ お好みの書式を設定 ] として、このセルをコピー。 この書式を設定したい範囲を選択して 右クリック→形式を選択して貼り付け→書式 としてください。
上手く行きますか?
(HANA)
~~ ~~ 2か所の参照先が変わっている必要があります。 うりぼー>ここは私の書き間違いで、2箇所変更していました。申し訳ありませんでした。
=IF(F2=SUMIF($I:$I,A2,$J:$J),"○","")→これを思いつきませんでした。
おかげさまで必要なデータ抽出できるようになりました。
大変勉強になりました、本当にどうもありがとうございました。
うりぼー
あらら、衝突しちゃいました。 まだ確認して頂けるかどうか分かりませんが そのまま載せておきます。 (かなりタイムラグのある文章なのですが・・・。) 以下、衝突前の文章。
失礼しました >>○条件付き書式の設定の所ですか? >ここは問題なしです。 >>○条件付き書式を行全てに設定する所ですか? >最大値を含む行すべてを色つきにする方法はわかりません。 と言うやりとりから、 提示して頂いた、各セルに入力されている条件付き書式の 数式部分は、記載ミスであり、実際は正しい式が入っていると 思われます。
最大値を含む行全てを色つきにする場合 =$F2=SUMIF($I:$I,$A2,$J:$J) ~~~ ~~~列方向を絶対参照にしてください。 これで列方向へ書式をコピーした場合も 参照先が常にA列とF列を見るので、色が付くと思います。 http://www.excel.studio-kazu.jp/mag2/backnumber/mm20040720.html
話しが前後しますが、上で載せた事とどちらが正しいのか (記載ミスか、そうでないのか)いまいち判断が つかないところがありますので、書き換えずに下に追加として 載せておきます。
ただし「行を抽出」が目的であれば 抽出するためのフラグをつけるのが良いと思いますし(G列) それを表示するのなら、条件付き書式の数式部分に 個別に計算式を入れなくても、 G列の値を確認する数式を入れれば良いと思います。 (先に提案した方法。)
以上。
(HANA)
>最大値を含む行の抽出ができれば目的の結果が得られます
日付(A) 月(B)年(C)始値(D) 高値(E) 最大値(F) 日付(I)最大値(J) 月(K)年(L)始値(M)高値(N) 20061229 9 2007 15870 16015 32855 20061222 41925 20061229 12 2007 15950 16040 42984 20061226 41878 20061229 3 2008 0 0 1 20061227 42568 20061229 9 2007 15835 15910 4 20061228 42367 20061228 12 2007 15865 15950 33083 20061229 42984 20061228 3 2008 0 0 42367 20061228 9 2007 16000 16000 1 20061227 12 2007 15850 16000 33521 20061227 3 2008 0 0 42568 20061226 9 2007 15740 15800 32413 20061226 12 2007 15870 15870 41878 20061226 3 2008 0 0 4 20061222 9 2007 0 15840 32259 20061222 12 2007 15810 15810 41925 20061222 3 2008 0 0 1 20061222 9 2007 16170 16240 4
K2=LOOKUP(1,1/(($A$2:$A$17=$I2)*($F$2:$F$17=$J2)),B$2:B$17) といれ K2:N6 にコピー
Byしげちゃん
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.