時系列で表示された2つのデータA,Bがあります(画像参照願います)。
時間、値A で縦方向にずらり、
時間、値B で縦方向のずらりと並んでいます。そのデータを同時性で検索して結合したいと思います。
つまり、最終形としては時間、値A, 値Bの3列でずらっと並べるということです。値Aのシートの3列目に値Bをコピーするということです。ということは2つのデータセットの時間を検索していくことになります。またその同時性という意味ですが、それほど厳密ではありません。値Aの方は1時間ごとに自動計測されたもので、値Bは計測値ですが人力なので時間的に不規則です。例えばある年月日の13時25分に計測されたB値はA値の同年月日の13時の欄に入ればいいという感じです。
VLOOKUPとか使うのかなと思ったのですが、ダメですね。また日時データとしては例えば1900年1月1日からの経過時間(HR)とかで序列化はできるものと思ってはいます。
※コンサルタント会社はこういう手法がわからないとき、人海戦術でやるようです。こういう操作って人間のすることではないように思うのですが。エクセルによる新女工哀史かと。
A 回答 (9件)
- 最新から表示
- 回答順に表示
No.1
- 回答日時:
データの規則性と具体的イメージが分かりません。
例えば、
2013.07.14 の16時台のデータは2つあります。
Aと結合した時、どう表示されているイメージなのですか?
見た感じ、Bデータの日と時間を結合して加工すれば、済む話だと
思うんですが。
だから、問題は同じ『時』のデータはどのように表示するかです。
同じ1時間の間の合計値とか、平均値とか…
データの意味が分からないので、分かりません。
コンサル会社?、何をしたいか分からないことを解き明かすのが、
コンサル会社の役目なんですけどね。A^^;)
で、どうなんでしょう?
回答ありがとうございます。Aの方は自動計測であり、Bの方は人間がやっているので同じ時間に2回やるってことも生じます。そのためBの方が2つデータがあるということになり、おかしくなりますね。Bの方の整理がついていないので1つの時間に2つのデータがないように工夫します。実際に手で処理してもそこは判断しないといけないことなので。Bの方は再度精査して1つの時間に2つのデータがないようにアレンジします。そうなっていると言う前提でお願いします。Aはちゃんと刻んでますね。
コンサルの方はとにかく結果を出すことになるので技術でやる時間がなかったら再契約して人に頼るようですが。
No.2
- 回答日時:
VLOOKUP関数をご存知でしょうか?、この関数には近似値を取得するという機能があるため、一般的に使われている完全一致ではなくてもデータが取得出来ます。
方法としてはまず、Bデータの日付と時間を結合し、それに対する値をAデータ側に引用する方法です。
例としてBデータの1列目に列挿入して、日付と時間を結合します。
この時の注意点として、双方の書式が一致していなければなりません。
Aデータの日時が文字列の場合には、Bデータの方も文字列として結合するだけですが、日付形式の場合にはBデータの日付+時間としなければならないので注意してください。
定義:
AB:Aデータ
C列: 参照元
E列:Bデータ結合日時
F列: 日付
G列: 時間
H列: 計測値
Aデータ日時が文字列の場合
E2『=F2&" "&G2』
Aデータ日時が日付の場合
E2『=F2+G2』
次に参照側ですが
C2『=VLOOKUP($E:$H,4,1)』
LOOKUP関数で4列目を取得します、この時に3番目の引数は0にすると完全一致ですが、1にすると近似値を取得します。
お試しください
回答ありがとうございます。VLOOKUPによる検索は完全な一致だとばかり思っていました。顧客名とか品番とかで検索するときに全角と半角英数とか姓と名の間に半角全角での違いとかギリシャ文字の数字と半角英数とかをどう判断するのだろうと思っていましたが。そういう意味で検索って実は難しいし、正確でもないと思っていました。再度調べてみます。
No.3
- 回答日時:
こんにちは
もう少しなさりたい内容を整理されれば、さほど難しくはないのではないかと想像します。
>時間、値A で縦方向にずらり、
>時間、値B で縦方向のずらりと
ご提示の図はその順ではないように見受けられますが??
>値Aの方は1時間ごとに自動計測されたもので、~~
>~B値はA値の同年月日の13時の欄に入ればいいという感じです。
最終的に(A値を基準に)1時間間隔でデータをまとめれば良いということですよね?
No1様も指摘なさっていらっしゃいますが、B値の時刻を丸めた際に、同じ時間帯に入る値が複数存在する場合はどうするのかを決めておく必要があります。
そのうえで大雑把な考え方として・・・
1)B値の時刻を1時間単位でまとめた時刻を(作業列に)算出しておく
2)まとめのシート(の例えばA列)に1時間間隔で時刻を記入
3)上記2)の時刻をキーにAデータ、Bデータからそれぞれ検索しデータ値を参照
(一致検索で良さそうに思われますし、VLOOKUPまたはMATCH-INDEXで対応できるでしょう)
回答ありがとうございます。作業の内容ですが、手作業でやるイメージでは、Bの値をAの表の近いところにコピペする、というだけです。Bもある程度大量にあります。
データですがAは定時でずらり、Bは飛び飛びです。Bが同じ時間に2つデータがあると言う指摘がありましたが、それは確かに作業上問題ですね(手作業でも決めなければならないので)。ハッキリしなくて申し訳ありませんがBのデータは上から下に時間が飛び飛びに流れていると思って下さい。飛びの間隔も不規則です。マッチングさせる背景の考え方ですが、ある年月日時からの経過時間(長さ)として見ているということでしょうか。だから値を使って検索できるということですかね。また、マッチングという意味では2つありますね。1時25分のデータを1時と見るか、2時と見るかです。ここはあまり神経質ではありません。どちらでもいいのですが、近い方がベターではありますが、小数切り捨てと同じで1時45分も2時とはせず1時でもいいです。トータルでは5年とか10年のデータがあり、Aデータは1時間ぐらい(つまり1個ずれ)では大きな変化はありません。そういうデータなのです。
No.4
- 回答日時:
やり方として簡単なのは、
Bデータの日時を加工してしまうことです。
下記の添付の例では、時間を1時間単位のデータに加工した例です。
=DATE(MID(A1,1,4),MID(A1,6,2),MID(A1,9,2))+TIME(B1*24,,)
こうすれば、Aデータと日時が一致する(想定される)ので、
下記の添付の例で、
F列は、SUMIF
=SUMIF(E:E,E1,C:C)
G列は、AVERAGEIF
=AVERAGEIF(E:E,E1,C:C)
H列は,INDEX & MATCH
=IFERROR(INDEX(C:C,MATCH(E6,E:E,0)),"該当無")
以下の1オプション
=IFERROR(INDEX(C:C,MATCH(E6,E:E,1)),"該当無")
※H4の例
とかで、データを埋めていけばよいです。
データの意味合いから仕様を固めればよろしいかと。
セルの書式設定により、年月日、時分秒の加工と整合性を
探るのが、こちら側から見えない難しさだと思います。
いかがでしょうか?
回答ありがとうございます。Bデータの日時について加工してAデータとそろえることが先決というのは、なるほどと思います。もともとBデータの日時が文字列じゃないのかなと思っていました。その場合、がんばってもAデータのような意味にならないのではないかと。年月日時分秒の書き方っていろんな可能性があるので関数DATEを使えば文字列が年月日時分秒になるということなのでしょうか。IFERRORという関数は”もしエラーなら”ということなので例外処理と言う風に見えますが。MATCHという関数の仕様についても十分知りませんでした。
余談ですが、こういう風に関数仕様に関する引き出しの多さがエクセル攻略のカギということでしょうか。どうしようもなくなってVBAということもあるのかなと思いますが。こうなると本格的なプログラミングですが、そこでも関数のワザが効くのでしょうか。
No.5
- 回答日時:
No3です
追加情報はいただけないようですし、かといって、回答内容を理解しようという様子もなさそうですので、勝手な解釈で「こんなことなら可能」というものをご参考までに示しておしまいにいたします。
AB列が「Aデータ」で、そのシートのF~H列に「Bデータ」をコピペします。
するとあらかじめ設定されていた関数によりJ列とD列が自動計算されるという仕組みです。
それぞれには以下の式を設定してあります
J3:=MROUND(F3+G3,1/24)
D3:=IFERROR(INDEX(H:H,MATCH(MROUND(A3,1/24),J:J,0)),"")
回答ありがとうございます。
エクセルの関数の使用について私の理解を超えているので理解するのにしばらく時間がかかりそうです。
”追加情報”がないとのことですが、このサイトでは”お礼”以外に書くところがないと思っておりますが。
No.6
- 回答日時:
>Bデータの日時が文字列じゃないのかなと
Bデータに限らず、Aデータもです。
EXCELの各セルの書式を見極めて、同じデータになっていることが
データを結合させるためのポイントです。
文字列なら、DATE関数に文字列の数字も埋め込めば、年月日になります。
もしかしたら、時間の方もTIME関数に『時』の文字列を埋め込む必要が
あるかもしれません。
IFERRORが付いているのは、その『年/月/日 時:00』が一致しない場合の
処理をするためです。
=IFERROR(INDEX(C:C,MATCH(E6,E:E,0)),"該当無")
の、『該当無』をスペース『"”』にすれば、何も出なくなります。
>関数仕様に関する引き出しの多さ
私は、はっきり言って、ずっと初心者です。
必要にせまられて、その場で、こんな関数ないかなってさぐるだけです。
ですから、EXCELでまともにマクロやコードなど書いたことありません。
自分の作ったEXCELでさえ、何してるか分からなくなることもあるので。
できるだけシンプルにしておきたい。というのが、モットーです。
ということで、
Aの『年/月/日 時:00』と
Bの『年.月.日』+『時:分』
が、一致するようにデータを
加工できるか否かが、結合のカギです。
いかがでしょう?
回答ありがとうございます。関数の仕様を調べればわかることだと思いますが、IFERROR()関数は”該当なし”のときにそれを表示するか否かですね。
作業の流れではAのデータに向けてBを寄せていくわけですが、Aのデータは数千であり、Bのデータは数百です。数千のAのデータの値の横のセルに該当したBの値が付加されるのですが、Aの方の大部分はBが寄ることがないのですが、Bの方は必ずAに当てたいわけです。最終的な仕上がりはAデータのシートのみなのですが、”該当なし”とはAデータに対する属性なのでしょうか(Bがないという意味で)。Bに対して該当するAがないのは問題です。
また、1つのAデータに複数のBデータが当たったらどうなるの?ということですが、それが起きないようにBをアレンジしなおします。
とにかくまずAもBも日時を比較できるように設定することが先ですね。
No.7
- 回答日時:
人海戦術で『どうやる』のかがすっきりしません。
画像のBデータの
❶2013.07.14 16:45 69.9
❷2013.07.14 16:55 156 は、
Aデータの
①2013/07/14 15:00
②2013/07/14 16:00
③2013/07/14 17:00 の行の三列目にどう入れれば良いのでしょうか。
画像にはないですが、Bデータが
❶2013.07.14 16:45 69.9
❷2013.07.14 16:55 156
❸2013.07.14 17:00 88.3
❹2013.07.14 17:15 20.7
❺2013.07.14 17:30 34.6 のようであったときは、どのようにしたいのでしょうか。
実測データ(Bデータ)を削除(無視)するかどうか、どのように扱うのか、示すと、わかりやすくなると思います。
No.8
- 回答日時:
次回から、こういうの質問する時は、サンプルで項目とデータも多少貼ってくれるといいんだけどね。
項目ないと何が何だかわかんないからね。
それと何をどうしたいかはダラダラ書かずに、箇条書きで書いてね。
こんなの人海戦術なんかしないから。
日付、時間、計測値なのかな?
それをまとめたいだけ?
Aデータをスペース区切りして、日付けと時間を分けるだろ?
Aデータの計測値の列を右に移動するだろ?
日付の表示フォーマットをどちらかに揃えるだろ?
AデータをBデータの下にくっつけるだろ?
テーブル化して、ソートするだろ?
以上だよ
No.9
- 回答日時:
>Bの方は必ずAに当てたいわけです。
Aの日時データが期間的にも網羅されており、
データの書式もあっているなら、
Bは必ず当たります。
データがあるかないかは、質問の内容からは分からないです。
サンプルデータも全く期間が違うようですし。
ですから、データのそれぞれの意味(定義)や条件が分かっていないと、
全てが無駄になります。
何をやりたいか? その意図することが、見えてこないです。
それがこの作業をするうえで、最も重要なことです。
EXCELでやるにしても、人海戦術だとしても、それを理解してやらないと
作業そのものが無駄になります。
データが数千あっても、データの意味とやりたいことが分かっていて、
データそのものの信頼性や書式等の課題がクリアされていれば、
正直言って、数分、データの補正なども考えて1時間もあれば、
できる内容ですし、多少『やりたいこと』がブレても、
同じ程度の時間でやり直しが効くでしょうし、
もっと突き詰めたデータ解析ができる内容だと思います。
だって、2つのデータを合わせるだけなんですから。
ですから、肝心なことは、
データの意味とやりたいこと
データそのものの信頼性と書式等の課題
というなのです。
『やりたいこと』は、あなたしか分かりません。
EXCELの関数がどうのは、大した話ではないのです。
添付は、AにBをぶつけた一部サンプルです。
INDEX & MATCH
SUMIF
の例です。
回答ありがとうございました。やりたいことが不明確ということでした。最も手短に言います。私は説明が冗長すぎるようで(人海戦術とか書いたりして)反省しています。”年月日時をキーにして検索してAデータの横にBデータを当てる” それだけです。量が多いと大変なので。これだけなのですが。時間をキーにして、ということですが、exactにはならないのは仕方ないです(データサンプリングのタイミングが全く違うから)。とにかく教えて頂いた方法を習得できるようにします。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) 3つのプロシージャをまとめたら実行時エラー発生で対応不能 6 2022/05/17 01:47
- 統計学 どの統計を使えばいいのか教えてください(EZ-Rを使用) 5 2022/10/11 13:28
- Visual Basic(VBA) 顧客ごとに違う点検案内を作成するマクロ 4 2022/09/16 05:34
- Excel(エクセル) エクセルでフィルタ後、考えている場所に値コピーができない。 1 2022/05/02 21:01
- その他(教育・科学・学問) エクセル関数について 2 2022/12/23 08:59
- システム CSVファイルのマッピング処理の省力化 1 2022/11/24 00:01
- Excel(エクセル) エクセルの散布図で新たに入力した値のデータラベルが空欄になる現象 1 2022/04/26 09:31
- 数学 賃料と専有面積のデータが60部屋分ほどがあり、 賃料÷専有面積(=1㎡あたりの賃料)の数式で計算する 2 2023/02/18 20:33
- Excel(エクセル) 数式→数値固定する方法ありますか? 5 2023/06/18 15:22
- 大学受験 国立受験 11月からの大逆転劇を起こすには 7 2022/11/14 19:24
このQ&Aを見た人はこんなQ&Aも見ています
-
外出時に「待たせる妻」vs イライラする「待つ夫」は日本だけ?見習いたい海外事情
夫の家事参加に積極的なイメージのある海外でも、同様の事例はあるのか。結婚カウンセラーの佐竹悦子さんに伺ってみた。
-
【エクセル】測定時間がバラバラのデータを統一したい
その他(Microsoft Office)
-
エクセルについての質問です。サンプルレートの違うデータの処理の仕方を教えてください。
その他(教育・科学・学問)
-
エクセルで2つの表をまとめたグラフを作りたい!
Excel(エクセル)
-
-
4
エクセルのグラフ作成で軸を等間隔にしない方法
その他(Microsoft Office)
-
5
エクセルグラフで時間の区切りの異なるデータを一緒に書く
Excel(エクセル)
-
6
一列に並んでいる大量のデータを10個ずつ平均(A1~A2000まである
Excel(エクセル)
-
7
EXCELで2列を参照し、重複するものを横に並べたい
Excel(エクセル)
-
8
エクセルで条件に一致したセルの隣のセルを取得したい
その他(Microsoft Office)
-
9
同一セルに日時があるものを日付と時間に分ける方法
その他(ソフトウェア)
-
10
エクセルグラフについて(日付の間隔が一定でない)
Excel(エクセル)
-
11
トルク定数と誘起電圧定数について
その他(教育・科学・学問)
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
教えて下さい
-
エクセルで2つの時系列のデー...
-
fortranでフーリエ変換→パワー...
-
EXCELVBAでSQLserverからデータ...
-
VBAでシートからコンボボックス...
-
特定のデータの抽出方法を教え...
-
GETはできるがPOSTができない、...
-
メモ帳(テキストデータ)をExc...
-
多量のSUMIF式を軽くしたい
-
Excelのマクロでワードのテキス...
-
【エクセル】測定時間がバラバ...
-
家計簿プログラム
-
VBA 毎日取得するデータを順番...
-
配列でデータが入っている要素...
-
クッキーの値のエンコード/デコ...
-
TBWに達するまで後5GBの時に100...
-
【EXCEL VBA】ブック内の特定の...
-
カンマからスラッシュに
-
ページ数を求めたい
-
cakePHPでバルクインサートする...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
教えて下さい
-
【エクセル】測定時間がバラバ...
-
配列でデータが入っている要素...
-
多量のSUMIF式を軽くしたい
-
エクセルで2つの時系列のデー...
-
Excelのマクロでワードのテキス...
-
メモ帳(テキストデータ)をExc...
-
VBA 空白セルを削除ではない方...
-
VBAを使ってOutlookメール本文...
-
Accessで該当データにフラグを...
-
合計3TBのデータのハッシュ値を...
-
シーケンサにパソコンからアク...
-
S9タイプからXタイプにデータ...
-
カンマからスラッシュに
-
VBAでシートからコンボボックス...
-
VBA 毎日取得するデータを順番...
-
ビットシフトについて
-
ユーザーフォームのテキストボ...
-
EXCELVBAでSQLserverからデータ...
-
特定のデータの抽出方法を教え...
おすすめ情報