Excel総合相談所 91 - 暇つぶし2ch

Excel総合相談所 91 at BSOFT

898:名無しさん＠そうだ選挙にいこう
10/05/21 02:47:34
質問させて下さい。2007です。

外部からコピペした表の中の金額欄が、数値ではなく半角数字文字列で入力されており、末尾に半角スペースが入っています。
(LEN関数で文字数を観測したら、数字の桁数+1になっていた。手作業で末尾の空白を消すと、数値として扱えるようになった)
この金額欄の数字を利用して計算式をたてたいのですが、数が多いので何らかの方法で数値として扱えるようにしたいのです。

ただ、色々やってみたところ
・末尾の半角スペースを取り除かなければならない→TRIM関数で空白を削除したものの、VALUE関数で変換しようとしても関数を参照しているのでエラーになってしまう

・置き換えで半角スペースをなくそうとしたけど、何故か検索にひっかからない→半角スペースが取り除けない

何かいい方法ないですか？

899:名無しさん＠そうだ選挙にいこう
10/05/21 04:45:40
【1 OSの種類　　　　 .】 WindowsXP
【2 Excelのバージョン　】 Excel2007
【3 VBAが使えるか　　 .】いいえ

Ａ列に数値が並んでいてＢ列には=IF(A2>A1,"△”),IF(A2<A1,"▼"),""　　（←オートフィルで下行まで）
と入っているのですが

数値の増減が滑らかなカーブを描く場合
△△△▼▼▼▼▼▼△△△△△△▼▼▼▼▼▼▼▼▼▼△△△△△△　　（←列として見てください）
という具合に同じ三角がある程度連続して現われます

逆に数値の増減が滑らかなカーブを描かない場合
△△△▼▼△▼▼▼△▼△△▼▼△△△▼△▼▼△▼△▼▼△△△▼▼　　（←列として見てください）
という具合に三角がランダムに現われます

以上は視覚的にある程度判断できるのですがシート数が多くあるため
この列の三角の並び方（滑らかorランダム）を調べる方法はないでしょうか？
角シート１０００行ぐらいあるのですがどのような関数で調べることができるか
アドバイスいただけないでしょうか？

アイデアとしては
・５０行ごとに３個以下の連続が幾つ含まれているかを数える
ぐらいしか思いつかないのですが曲線の滑らかさを測る指標とかあるでしょうか？
ヒントだけでもいただければありがたいです。

900:名無しさん＠そうだ選挙にいこう
10/05/21 04:56:10
>>899
書いてから気付きましたが別に５０行と区切らなくても
１０００行すべてでカウントすれば良いですね

例えば「全範囲（１０００行）で同じ三角が３個以下の連続（１個も一つと数える）が幾つあるか数える」
という関数はどのようになるでしょうか？

901:名無しさん＠そうだ選挙にいこう
10/05/21 07:22:43
>>899
求める答えではないけど、△▲を0,1に変換して自己相関取るのが
スマートと思うよ。A1:A31に0,1のデータが入っていたら
=correl(A1:A30,A2:A31)
答えが0に近いほどランダムを表します。

902:名無しさん＠そうだ選挙にいこう
10/05/21 10:42:53
すみません。マウスのポインタに対してXYの座標に色がつくマクロのサンプルはどこかにないでしょうか。
イメージとしては　H40マスにポインタがあったら、H列と40行全部に色がつき、H40でクロスするといったものです。
当然マウスを動かすと移動するものです。ぐぐったんですがサンプルがなかなかなくて困っています。
エクセル2003でうごけばいいです。

903:名無しさん＠そうだ選挙にいこう
10/05/21 10:58:03
セクセルに詳しい方助けてください
=IF(COUNTIF(C2:C31,"<30")=0,"",COUNTIF(C2:C31,"<30"))
はちゃんと数値が出るのに
=IF(COUNTIF(C2:C31,"<c33")=0,"",COUNTIF(C2:C31,"<c33"))
はちゃんとした数値が出ません。
何が悪いのでしょうか？

904:903
10/05/21 11:12:14
ヤフー知恵袋の勝ちということで
無事解決しました

905:名無しさん＠そうだ選挙にいこう
10/05/21 11:48:14
>898
＞置き換えで半角スペースをなくそうとしたけど、何故か検索にひっかからない→半角スペースが取り除けない
置き換えダイアログで、検索側の欄に自分で半角空白を入れるのではなく、コピペでもダメでしたか？
特殊文字（改行などの制御文字）かもしれないですね。
末尾の「空白のようなもの」の文字コードを調べてみたら？
=CODE(RIGHT(A1,1))　←Ａ１セルの文字列右端の「空白のようなもの」の文字コードが表示される
=CODE(" ") ←半角スペースを自分で入れた文字コードが表示される

906:名無しさん＠そうだ選挙にいこう
10/05/21 13:44:56
>>897
Sub 会計下線()
Selection.Font.Underline = xlUnderlineStyleSingleAccounting
End Sub

ツール→ユーザー設定→コマンド→マクロ→ユーザ設定ボタン
で、そのボタンに上のマクロを割り当て、ツールバーに追加するとか？
私だったらｃｔｒｌ＋ｚとかのショートカットキーを割り当てるけど。

907:名無しさん＠そうだ選挙にいこう
10/05/21 13:53:14
>>902
ポインタじゃなくてセルを選択してないと出来ないけど・・

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
'選択セルの行列に色をつけます。
Cells.Interior.ColorIndex = xlNone
r = Target.Row
co = Target.Column
Rows(r).Interior.ColorIndex = 36
Columns(co).Interior.ColorIndex = 36
End Sub

908:名無しさん＠そうだ選挙にいこう
10/05/21 15:41:28
>>901
ありがとうございます
相関係数 CORREL 　・・・2つの配列の相関係数を返します。
これは調べる範囲が1000行ある場合
=correl(A1:A999,A2:A1000) とすれば良いのでしょうか？
それとも=correl(A1:A30,A2:A31) をオートフィルで下までという形でしょうか？

909:複乳
10/05/21 20:02:00
>>898
>>905の言うとおり制御文字か何かだろう
それをコピーしてsubstituteでもいいけど
=LEFT(A1,LEN(A1)-1)*1
でよくない。ちなみに*1は数値にするためのもの

910:複乳
10/05/21 20:02:52
>>902
Worksheet_mousemoveなんてあればいけるんだろうけど
↓はURLﾘﾝｸ(yunatip.exblog.jp)と>>907の合体。
'↓標準モジュールに記述
Public Type POINTAPI
X As Long
Y As Long
End Type

Public Declare Function GetCursorPos Lib "USER32" _
(lpPoint As POINTAPI) As Long

Public Declare Function SetCursorPos Lib "USER32" _
(ByVal X As Long, ByVal Y As Long) As Long

'↓sheet1に記述
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim poi As POINTAPI
Dim mycl As Range

Call GetCursorPos(poi)
Set mycl = ActiveWindow.RangeFromPoint(poi.x, poi.y)

Cells.Interior.ColorIndex = xlNone
r = mycl.Row
co = mycl.Column
Rows(r).Interior.ColorIndex = 36
Columns(co).Interior.ColorIndex = 36

End Sub

911:901
10/05/21 21:34:14
>>908
元データがどんな系列か、また何を解析したいかの最終目的次第だと思う
（もし教えて下さればより明確な答えが出るかも）
ある数nまでの周期性が存在するか調べたいなら、ラグをn
まで取った自己相関を調べると良いよ。

n=10とすると、データがA1:A1000に入っているとして、
C1:C10に1～10を入れて
D1に=correl(offset($A$1,0,0,1000-C1):offset($A$1,C1,0,1000-C1))
これをD10までコピー。

912:908
10/05/21 21:59:31
>>911
ありがとうございます。
調べたいのは株価に関する数値（以下Ｚ値とします。株価そのものではありません）です。

Ｚ値がどのように変化しているのか各銘柄ごとに調べて
もし△△△▼▼▼▼▼▼△△△△△△▼▼▼▼▼▼▼▼▼▼△△△△△△　
のような変化ならＺ値が滑らかに上昇下降しやすい銘柄

あるいは△△△▼▼△▼▼▼△▼△△▼▼△△△▼△▼▼△▼△▼▼△△△▼▼
という変化ならＺ値が不規則にに上げ下げを繰り返す銘柄

というふうに銘柄ごとのＺ値の動きのクセを調べたいというのが目的です。
そして複数銘柄のＺ値の動き方を数値化できればそれを比較して
滑らかな動き～不規則な動きという具合にグループ分けするのが最終目的です。

理系の知識が殆どなくつたない説明で申し訳ありませんが
アドバイスいただければありがたいです。

913:名無しさん＠そうだ選挙にいこう
10/05/21 22:55:44
【1 OSの種類　　　　 .】 WindowsXP SP2
【2 Excelのバージョン　】 Excel2002
【3 VBAが使えるか　　 .】いいえ
【4 VBAでの回答の可否】不可
【5 検索キーワード　　】 IF文
宜しければ知恵を授けてください…
　Ａ　Ｂ
１×　▲
２×　×
３×　×
４×　●

=IF(OR($A$1<>"×",$A$2<>"×",$A$3<>"×",$A$4<>"×"),"ＯＫ","")
A1～A4までが全て×の時は、""にしてB1～B4の様に×以外がある場合は
"ＯＫ"と表示させているのですが、セル一つ一つを指定して判定してる為
２０ｺ、３０ｺっとなってくると非常に大変な事になってしまいました。
↑の式をIF(OR($A$1:$A$4)<>"×"),"ＯＫ","")
の様に範囲で指定する方法はないでしょうか？
宜しくお願いします。

914:901
10/05/21 23:07:50
>>912,908
「滑らかに上昇下降」や「不規則に上げ下げ」や「動きのクセ」の定義（計算方法）
をお伝え頂ければ、手段としてのExcelの解法をお伝えできますが・・・。

これ以上はExcelとは関係ない内容でスレ違いですから、どこか
誘導していただければそちらで答えられるかもしれません。

915:名無しさん＠そうだ選挙にいこう
10/05/21 23:14:18
>913
=IF((COUNTIF(A1:A20,"×")-ROWS(A1:A20))=0,"","ＯＫ")
ではどうですか？

916:915
10/05/21 23:19:44
別に引き算しなくてもよかった
=IF(COUNTIF(A1:A20,"×")=ROWS(A1:A20),"","ＯＫ")

917:名無しさん＠そうだ選挙にいこう
10/05/21 23:29:38
>>915
あんなに悩んでいたのが嘘のようです。
そうかCOUNTIFで個数数えてセルと同数なら･･･って考えれば良かったんだ
柔軟な発想がとても羨ましいです。
本当にありがとうございます。

918:915,916
10/05/21 23:37:41
>917
ヒントは少し前の質問といくつかの回答にありました。
>885-894　を見てください。