質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

Q&A

解決済

1回答

5491閲覧

Excelで時系列データから目的の値を自動的に抜き出す方法

minato_hy

総合スコア68

VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

0グッド

0クリップ

投稿2016/10/04 12:35

Excelで以下のようなデータが数百件ほど並んでいるとします。値Aを約10ずつ上昇させていくと、値B以降の値も同時に変化していく、というようなデータです。
このとき、例えば下記のような処理をしたい場合にどんな方法があるでしょうか?
解りにくい例と説明で申し訳ないですが、

  • 値Aの"100.1", "110.6", "120.4", ……ごとに値Bの平均を計算する。

つまり値Aが"100.1"のときの値Bの平均値は"(65+67+65+76)/4"で……、といった処理をする。

  • 値Aに対して値Bをひとつ抜き出す。

例えば値Aが"110.6"のときは値Bから83を抜き出す。
ただし、値Aはほぼ10ずつ上昇していくのだが、例えば値Aが"120.4"に変化する直前だと値Bが変化し始めている可能性があるので、"100.1"側にも"120.4"側にも近くなく値Bが安定している中央に近い値、下のデータのでいうと21時0分35秒のときの値Bである83を抜き出したい。これを値Aの各値について自動的に処理したい。

Excelの関数、VBA、またはpythonなど他の言語でも、なにか自動化する手段があれば教えていただけますでしょうか。

###対象のデータの例

時刻 値A 値B
2016_21:00_30 100.1 67 ……
2016_21:00_31 100.1 65 ……
2016_21:00_32 100.1 70 ……
2016_21:00_33 100.8 78 ……
2016_21:00_34 110.6 84 ……
2016_21:00_35 110.6 83 ……
2016_21:00_36 110.6 89 ……
2016_21:00_37 120.4 92 ……
2016_21:00_38 120.4 94 ……
……

###補足情報(言語/FW/ツール等のバージョンなど)
Windows7
Excel 2007

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答1

0

ベストアンサー

ピボットで簡単に出来そうな気がしますが、環境が手元に無くて試せないため説明が簡単な配列数式で
値Aの列がB、値Bの列がCでデータが100行の場合
= MEDIAN(IF((B1:B100>=100)(B1:B100<110),C1:C100,""))
と何処かに入れてSHIFTとCTRLとENTERを同時に押すと、値Aが100以上110未満の値Bの中央値が取れると思います。
100,110,120,130..... のような連続の値をD列にでも作成して
= MEDIAN(IF(($B$1:$B$100>=D1)
($B$1:$B$100<D1+10),$C$1:$C$100,""))
その隣のセルにこのように書いて縦に複製すれば、それぞれの値の中央値が取れるかも?(+10もインクリメントしてしまうかも。環境が無いのでちょっと分かりません)

平均はちょっと自信ないですが、こんな感じで取れないでしょうか?
= SUM(IF((B1:B100>=100)(B1:B100<110),C1:C100,0)) / COUNTIF((B1:B100>=100)(B1:B100<110))

投稿2016/10/04 15:20

編集2016/10/04 15:26
hirohiro

総合スコア2068

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

minato_hy

2016/10/15 05:56

中央値を取るほうですが、縦に複製したところでは値がエラーになってしまいますね……式の意味がまだよく理解できてないので調べ中ですが。
hirohiro

2016/10/15 07:35 編集

あら、複製前の1つ目のセルは希望通りの値が取れていますか? 複製セルだけエラーになるなら、式内のD1の部分以外もインクリメントされているのかも知れませんね。 手元に環境が無いので試せません。 MEDIANは指定範囲の中央値を出す関数です。 配列数式は、式内の範囲(配列)の値を一つずつ処理するものです。  例えば「=sum(A1:A10*B1:B10)」を配列数式にするとA1*B1+A2*B2+...A10*B10の値が表示されます。 私の投稿したものは、指定範囲のうちIFで条件に合わない値を空白に変えて、残った値の中央値を計算するという意図の式です。(空白じゃなくて0にすると、中央値が変わると思いますので) 配列数式について https://support.office.com/ja-jp/article/%E9%85%8D%E5%88%97%E6%95%B0%E5%BC%8F%E3%81%AE%E3%82%AC%E3%82%A4%E3%83%89%E3%83%A9%E3%82%A4%E3%83%B3%E3%81%A8%E4%BE%8B-3be0c791-3f89-4644-a062-8e6e9ecee523 
minato_hy

2016/10/15 10:06 編集

1つ目のセルは正しい値でした。 ……と、今こちらのexcelで試していると、いつの間にか式が `{=MEDIAN(IF(($B$1:$B$20>=D1)*($B$1:$B$20<D1+10),$C$1:$C$20,""))}` というように波括弧でくくられており、この波括弧つきの式を複製してみると、1つ目のセル以外も正しい値が取れました。最初にShift+Ctrl+Enterを押したときは特に変化がなかった気がしたのですが、押し間違いだったのかもしれません……? なるほど、配列数式というものがあったのですね。検索のしようもなかったので非常にありがたいです。 詳しい解説まで誠にありがとうございました。
hirohiro

2016/10/15 10:20 編集

> よく解りませんが結果オーライ ドラッグでの複製では配列数式のコピーができなかったのかも知れませんね。 > アスタリスクの意味がイマイチ解らず アスタリスクは掛け算の記号です。 IFは第一引数が1とかtrueなら第二引数、0とかfalseなら第三引数を返す関数です。 (正確には0以外の数値なら真、0なら偽だったと思います)  「=IF(1,0,1)」これなら0を返すはずです。 等号や比較記号で数式を作ると、結果が0か1(trueかfalse)で返されます。  「=(1<2)」これなら1が表示されると思います。多分 なので、「=(1<2)*(3>2)」だと両方1なので1*1で結果も1というわけですね。 どちらかでも条件に合わないと結果は0になります。 つまり、条件が全部真の場合だけ、IF関数の第二引数を採用するということです。 > この波括弧つきの式を複製してみると 説明ページを読むと分かりますが、その波括弧で包まれた状態が配列数式です。 SHIFTとCTRLとENTERを同時に押すと選択セルの中の数式を波括弧で包んでくれるというだけのことです。タイプミスが起きないくらいの利点ですね。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問