[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『名前毎の最小値の表示』(KAI)
例表)
A B C - a -0.3 0.5 a 0.5 - a 0.4 1.7 b 1.7 - b 0.3 - c -0.6 1.3 c 1.3 - c 0.4 例表にてB列の名前毎の最大値をA列に表示する場合の一例として、下記式があります。 A=IF(C2="","",IF(MAX(INDEX(($C$2:$C$9)*($B$2:$B$9=B2),))=C2,C2,"-"))
が、最小値を表示させるのに「MAX」を「MIN」に代えるだけではうまくいきません。
上式をベースに解決策はありますか?
< 使用 Excel:Excel2010、使用 OS:Windows7 >
A B C D 1 MAX MIN 2 - -0.3 a -0.3 3 0.5 - a 0.5 4 - - a 0.4 5 1.7 - b 1.7 6 - 0.3 b 0.3 7 - -0.6 c -0.6 8 1.3 - c 1.3 9 - - c 0.4
A2 =IF(D2="","",IF(MAX(INDEX((C$2:C$9=C2)*D$2:D$9,))=D2,D2,"-")) B2 =IF(D2="","",IF(MIN(INDEX((C$2:C$9<>C2)*10^16+D$2:D$9,))=D2,D2,"-"))
A2:B2下へコピー。
※ってか、ピボットテーブルじゃだめなの? (GobGob) 2014/04/14(月) 17:52
確認しましたが、“空欄”の時はMINはうまくいきません。
また、確認時に気付いたのですが、実際のデータは“空欄”以外に空白や記号が混じってました。
MAX含め、これに対応することは可能でしょうか?
データは2000行程度になります。
※申訳ないですが、ピボットは避けたいです。(未熟なので)
(KAI) 2014/04/15(火) 12:31
>確認しましたが、“空欄”の時はMINはうまくいきません。 >また、確認時に気付いたのですが、実際のデータは“空欄”以外に空白や記号が混じってました。 >MAX含め、これに対応することは可能でしょうか?
まず質問。
1:「空欄」と「空白」の違いは? 2:どの列が「空欄」の場合なの? 3:空欄、空白の場合、どんな結果を求めてるの?
「うまくいかない」って言われても「うまくいってる」結果の説明もないので わかりまへん。 (GobGob) 2014/04/16(水) 08:11
>“空欄”の時はMINはうまくいきません→「問題ありません」でした。
D2のデータを「空欄」と「#」にした結果です。
「#」は「空白」にしても結果は同じです。
A B C D MAX MIN a 0.5 - a 0.5 - - a 0.4 1.7 - b 1.7 - 0.3 b 0.3 - -0.6 c -0.6 1.3 - c 1.3 - - c 0.4
MAX MIN #VALUE! #VALUE! a # #VALUE! #VALUE! a 0.5 #VALUE! #VALUE! a 0.4 #VALUE! #VALUE! b 1.7 #VALUE! #VALUE! b 0.3 #VALUE! #VALUE! c -0.6 #VALUE! #VALUE! c 1.3 #VALUE! #VALUE! c 0.4
・「空欄」:データなし(「Delete」した状態?)
・「空白」:「スペース」入力あり
上記(数値以外)の場合は、「-」にしたいです。
よろしくお願いします。
(KAI) 2014/04/16(水) 10:48
スペース入力があるってことは見た目が「空白」なだけで「データがスペース」 ってことっすね。
・・・だったらうまくいかないような気がするんだけどねぇ。
空白or文字列を除く数値のみの範囲で大小判断。
A B C D 1 MAX MIN 2 a # 3 - -0.5 a -0.5 4 -0.4 - a -0.4 5 1.7 - b 1.7 6 - 0.3 b 0.3 7 - -0.6 c -0.6 8 1.3 - c 1.3 9 - - c 0.4
A2 =IF(COUNT(D2),IF(MAX(INDEX(-(C$2:C$9<>C2)*10^16+TEXT(D$2:D$9&"","標準;-標準;0;!-!1!E!+!1!6"),))=D2,D2,"-"),"")
B2 =IF(COUNT(D2),IF(MIN(INDEX((C$2:C$9<>C2)*10^16+TEXT(D$2:D$9&"","標準;-標準;0;!1!E!+!1!6"),))=D2,D2,"-"),"")
A2:B2 下へコピー (GobGob) 2014/04/16(水) 11:41
が、TEXT関数のところがよくわかりません。
数値とそれ以外の処理の“キモ”のところだと思うのですが
さっぱり???です。
よろしければ、ご教授願えないでしょうか?
(KAI) 2014/04/16(水) 21:41
TEXTは第一引数のデータをセルの表示形式型の文字列で返す関数。 ってのはOK?
んで、最大値を出す方のTEXT関数
TEXT(D$2:D$9&"","標準;-標準;0;!-!1!E!+!1!6")
これはD2:D9の値を 標準;-標準;0;-1E+16 の表示形式に沿って文字列で返すんですな。
表示形式は 正数;負数;ゼロ値;文字列 の順で設定するんで これに当てはめると文字列は「-1E+16」を返す ⇒ D列が文字列なら「すごい小さい数値」を返す。 ってことっす。
なんでこんな処理が必要かというと、最大値が負数の場合、D列が空白(データなし) のとき「0」を処理してしまい、最大値が「0」となってしまうからなんですな。 なので、D2:D9&"" で一度すべて「文字列」に変換してからTEXT処理してます。
最小値のほうは逆に「すごい大きい数値」を返してますわ。 (GobGob) 2014/04/17(木) 08:13
>これはD2:D9の値を標準;-標準;0;-1E+16 の表示形式に沿って文字列で返す..
ここのところを理解するのに随分と時間がかかっています。
表示形式の“基本中の基本?”がわからず恥ずかしい限りですが、
「!-!1!E!+!1!6」が「-1E+16」になるのが???です。
>「すごい小さい数値」..「すごい大きい数値」..
ここはすごくわかりやすい説明で、“ストン”っとアタマに入ってきました。
「!」の部分、教えてもらえますか?
(KAI) 2014/04/17(木) 19:13
たとえば表示形式では
0 … 数値を表示 s … 秒を表示
とか、表示形式にあわせた記号(?)があるんですが、
sとか0とか、そのまま表示したい場合は その文字列の前に「!」を付加するんですわ。
たとえば A1セルに 1 と入力して
・表示形式 0;-0;0 ⇒ セルには「1」と表示 ・表示形式 !0;-0;0 ⇒ セルには「0」(0をそのまま表示)
こんな感じですわ。
!-!1!E!+!1!6 の !を外すとエラーになるのはこの記号が邪魔するから すべて「文字として」設定してるってことっす。
※まぁ、すべてに「!」をつける必要もないんだけど念のため。 (GobGob) 2014/04/17(木) 19:22
補足。。。
A1セルのデータ 「0:00:05」、A1に「5s」と表示したいとして・・・
表示形式 ss → 「05」と表示
表示形式 s"s" → 「5s」と表示
表示形式 s!s → 「5s」と表示
文字をそのまま表示したい場合は引用符「″」(ダブルクォーテーション)を用いるんだけど 感嘆符「!」を直前につける方法でも代用できるんですわ。 (GobGob) 2014/04/18(金) 08:24
>..!を外すとエラーになるのはこの記号が邪魔するから.. →「この記号」とは?? 何を邪魔する??
>感嘆符「!」を直前につける方法でも代用できる..→??? 「!-!1!E!+!1!6」を「"-1E+16"」や「"-""1""E""+""1""6"」にしてもダメですよね? [!」を代用した表現にするには??
低レベルな話ですみませんが、よろしくお願いします。
(KAI) 2014/04/18(金) 11:00
>「この記号」とは??
これは
>たとえば表示形式では > >0 … 数値を表示 >s … 秒を表示 > >とか、表示形式にあわせた記号(?)があるんですが、
このことっす。
>何を邪魔する??
文字列表示形式で「E」を設定するとエラー。 Eは和暦を表示する記号(?)だからね。
>「!-!1!E!+!1!6」を「"-1E+16"」や「"-""1""E""+""1""6"」にしてもダメですよね?
これはねー、ちょいややこしいんだけど 数式処理をするからなんだよね。
文字列定数は ダブルクォーテーションで囲むんだけど (たとえば ="A" とかね) ダブルクォーテーション自体を文字列定数として処理するには
=""""
と入力するんですな。 (結果は 「"」を表示)
TEXT関数の第2引数は文字列で設定。んで、ダブルクォーテーションで 処理するには文字列内にきちんと「"」が反映されていないといけない。
=""-""1""E""+""1""6""と入力するとエラーになるのは ダブルクォーテーションの処理が間違ってるんですわ。
"""-""""1""""E""""+""""1""""6"""
→ =TEXT(D$2:D$9&"","標準;-標準;0;""-""""1""""E""""+""""1""""6""")
これならエラーにならないよね? (GobGob) 2014/04/18(金) 11:22 ⇒ 11:27修正
またまた補足。
>="""" > >と入力するんですな。 (結果は 「"」を表示)
文字列定数としてダブルクォーテーションを処理するには 「""」と2個連続で入力すると1個の「"」が処理されますわ。
2個処理したい場合
="""""" (GobGob) 2014/04/18(金) 12:14
「E」のことや「"」は2個で1個を表す..なんて初めて知りました。
ようやく提示の式の意味がわかりました。(と、思います^^;)
これでいい週末が送れそうです..
貴重なお時間、本当にありがとうございました!
(KAI) 2014/04/18(金) 14:18
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.