[[20150514224231]]　『「直近～カ月」分のデータのみを抽出する方法を教』（キャッサバ）　ページの最後に飛ぶ

『「直近～カ月」分のデータのみを抽出する方法を教えてください。』（キャッサバ）

・sheet1には20種類の数値をそれぞれ週一回入力していきます。
・sheet2～sheet21には20種類の内の1つに着目し、それ以外の19種類とのそれぞれの相関係数が期間別に自動で表示されるようにしたいのですが、その際、直近1カ月分、直近3カ月分、直近6カ月分、直近1年分と区切ってデータを抽出する事ができず、今まで入力した全てのデータから算出された相関係数のみが表示されてしまっています。

解決法がありましたらご教示下さい。
よろしくお願いします。

＜使用 Excel：Excel2010、使用 OS：Windows7 ＞

 回答する側は、表を見ながら説明を受けているわけではないので
 キャッサバさんの説明ではよくわかりません。
 まず、シート１の表の形を提示頂いて、
 「こうあってほしい」と思う、シート２以降の表の形を提示してみてください。

 表を作る上で、下記を考慮してください。

 ＞sheet1には20種類の数値をそれぞれ週一回入力していきます。 
 どこに入力ですか？　ランダムですか？

 文字通り20種類の数値（1～20）までの数字？　
 それとも、20項目あって、それぞれ計測した値を入力している？

 ＞・sheet2～sheet21には20種類の内の1つに着目し、
 その着目すべき種類は、シート2～21のどこを見ればわかりますか？

（稲葉） 2015/05/15(金) 08:36

稲葉さん、ご指摘ありがとうございます。
おっしゃる通り形を提示するべきでした。
また、質問の仕方も的外れなものでした。
失礼しました。

「sheet1」　週に1度測定したデータを入力するシート
A列には「2015年第1週」「2015年第2週」というように週単位で日付を入力します。
B～U列には、1行目にそれぞれの項目名を入力し、
2行目以降はA列の日付に対応した測定データ（2,3桁の数字です）を入力します。
データを入力しやすくする為に、
2行目に常に直近の週が来るように毎回行を挿入して使用するので、
日付は降順になります。

「sheet2～sheet21」　sheet1のB～U列の項目それぞれに対する相関係数を表示するシート
例えば、sheet2であればsheet1のB列の項目に対するC～U列それぞれとの相関係数を、
sheet3であればsheet1のC列の項目に対するB列とD～U列それぞれとの相関係数を、
というような形で自動で参照して表示したいです。
その際、直近1カ月、直近3カ月、直近6カ月、直近1年分の、
それぞれの相関係数を表示したいです。

再度よろしくお願います。
（キャッサバ） 2015/05/15(金) 11:13

 ＞A列には「2015年 第1週」「2015年 第2週」というように週単位で日付を入力します。 
 とありますが、データ型はなんですか？
 日付型ですか？　それとも文字列ですか？

 ＞というような形で自動で参照して表示したいです。
 相変わらず、出力をどのように行いたいのかわかりません。
 シート２のどのセルに、結果を表示させたいのですか？

 測定データと相関係数は別ものですか？
 別なら、測定データからどのように求めているのですか？

 ＞その際、直近1カ月、直近3カ月、直近6カ月、直近1年分の、 
 ＞それぞれの相関係数を表示したいです。 
 直近1カ月の「平均」ですか？　「合計」ですか？　それとも個別のデータをすべてですか？

 また、これらは動的（ユーザーが選択して表示を切り替える）ですか？
 そうでなければ、どのように表示させればよろしいのですか？
 動的の場合でも、どのセルを参照して切り替えればよろしいですか？
（稲葉） 2015/05/15(金) 12:51

何度も申し訳ありません。

＞とありますが、データ型はなんですか？
＞日付型ですか？　それとも文字列ですか？
データは文字列です。

＞相変わらず、出力をどのように行いたいのかわかりません。
＞シート２のどのセルに、結果を表示させたいのですか？
A1は空白
A2：直近1カ月、A3：直近3カ月、A4：直近6カ月、A5：直近1年、と入力しました。
B1～T1にはそれぞれの項目名を入力しました。
B2～T2に直近1カ月の相関係数を表示
B3～T3に直近3カ月の相関係数を表示
B4～T4に直近6カ月の相関係数を表示
B5～T5に直近1年の相関係数を表示
このようにしたいです。

＞測定データと相関係数は別ものですか？
＞別なら、測定データからどのように求めているのですか？
紛らわしい書き方をして申し訳ありませんでした。
測定データとは私が測定してきたデータを、
週に一度手動でシート1に入力しただけのものです。
とりあえずシート2のB2に「=CORREL(sheet1!B2:B53,sheet1!C2:C53)」
と入力してシート1のB列とC列の相関係数を求めてみました。
2～53行目まで範囲指定しているのは、現状でデータが52週分あったからです。
しかし、これでは52週（1年）における相関係数しか算出できず困っていました。
また、次の週のデータを入力する為にシート1の2行目に新しい行を挿入した際に、
先ほどの「=CORREL(sheet1!B2:B53,sheet1!C2:C53)」がズレて
「=CORREL(sheet1!B3:B54,sheet1!C3:C54)」
になるのではないかということも素人考えで懸念していました。
欲しいのは直近1カ月、直近3カ月、直近6カ月、直近1年分の相関係数です。

＞また、これらは動的（ユーザーが選択して表示を切り替える）ですか？
＞そうでなければ、どのように表示させればよろしいのですか？
＞動的の場合でも、どのセルを参照して切り替えればよろしいですか？
現状で必要なのは直近1カ月、直近3カ月、直近6カ月、直近1年分だけなので、
常に同じセルを参照することになるので切り替えることはありませんが、
今後可能であれば自分で指定した期間の相関係数を求める、
といこともしたいと考えていましたので、その方法があればご教示いただきたいです。

お手数おかけします。
（キャッサバ） 2015/05/15(金) 14:00

 途中で投げ出すのは、心苦しいのですが
 仕事でトラブルが重なりまして、すぐに回答できません。
 他の方の回答をお待ちください・・・
（稲葉） 2015/05/15(金) 15:22

お忙しい中貴重な時間を割いて頂きありがとうございました。
質問していく中で自分でもどうすればいいのか多少明確になってきた部分もありましたので、
再度トライしてみようと思います。
トラブルの早期解決を願っています。
本当にありがとうございました。

（キャッサバ） 2015/05/15(金) 15:35

判らない部分は何処ですか？　全部作って、という依頼はお断りです。

1ヶ月、3ヶ月、1年分のデータ範囲を知りたい、という部分だけ判れば良いように見えますが、
1行1週なのですから、4行、12行、52行分を固定で指定するだけでは？
そして、行挿入は範囲を変えてしまうので、マクロで毎週計算式を設定し直せば良いでしょう。

1ヶ月が5週のときもある、とか小難しいことを言うならば、1週1行という表現が悪いのです。年月日にすべきでしょうね。
（???） 2015/05/15(金) 15:41

コメントありがとうございます。

？？？さんのおっしゃる通りです。
必要な週の分を固定で指定するだけでした。
行の挿入のところで今悩んでいたのですが、マクロを使用すればよかったのですね。
エクセル初心者なので早速マクロの勉強をして設定をしてみたいと思います。

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

（キャッサバ） 2015/05/15(金) 19:10

セル範囲に名前定義する案

 名前は、こんな感じ。項目名は、１行目の値。
 項目名_1:Sheet1!B2:B5
 項目名_3:Sheet1!B2:B13
 項目名_6:Sheet1!B2:B27
 項目名_12:Sheet1!B2:B53

 で、各シートの式入力、例えばこんな感じ。
B2＝CORREL（項目4_1,項目1_1)
B3＝CORREL（項目4_3,項目1_3)
B4＝CORREL（項目4_6,項目1_6)
B5＝CORREL（項目4_12,項目1_12)
C2＝CORREL（項目4_1,項目2_1)
C3＝CORREL（項目4_3,項目2_3)
C4＝CORREL（項目4_6,項目2_6)
C5＝CORREL（項目4_12,項目2_12)

 準備１：
式の入力は面倒なので、下記のマクロを標準モジュールにおいて実行
各シート名は、基準とする項目名に変更して下さい。
activeなシートにだけ式が挿入されます。
従って、式を入れたいシートすべてで実行する必要あり。
（ただし１回だけ実行すればOK）

 Sub test()
    Dim i As Long
    Dim n1 As String
    Dim n2 As String

    n1 = ActiveSheet.Name

    For i = 2 To 20
        n2 = Cells(1, i).Value
        Cells(2, i).Formula = "=correl(" & n1 & "_1," & n2 & "_1)"
        Cells(3, i).Formula = "=correl(" & n1 & "_3," & n2 & "_3)"
        Cells(4, i).Formula = "=correl(" & n1 & "_6," & n2 & "_6)"
        Cells(5, i).Formula = "=correl(" & n1 & "_12," & n2 & "_12)"
    Next

 End Sub

 準備２：
測定データのシート（Sheet1）のシートモジュールに下記マクロをコピペ。

 Private Sub Worksheet_Deactivate()
    Dim c As Range
    Dim n As Long

    For Each c In Range("B1:U1")
        n = n + 1
        With Range("A2").Offset(, n)
            .Resize(4).Name = c.Value & "_1"
            .Resize(12).Name = c.Value & "_3"
            .Resize(26).Name = c.Value & "_6"
            .Resize(52).Name = c.Value & "_12"
        End With
    Next
 End Sub

（マナ） 2015/05/15(金) 21:57

マナさん、名前定義の案ありがとうございます。
その上マクロまで提示していただいてとても助かりました。

本当にありがとうございました。
（キャッサバ） 2015/05/16(土) 10:12

コメント返信：

[ 一覧(最新更新順) ]

[[20150514224231]] 『「直近～カ月」分のデータのみを抽出する方法を教』（キャッサバ） ページの最後に飛ぶ

[[20150514224231]]　『「直近～カ月」分のデータのみを抽出する方法を教』（キャッサバ）　ページの最後に飛ぶ