A 回答 (10件)
- 最新から表示
- 回答順に表示
No.1
- 回答日時:
自分ならその横に長い範囲のデータを日付単位でコピーして
同じ時間に対応するセルに貼り付ける。
これを繰り返すかな。
データが無くなった列は後でまとめて削除する。
でもって、最後に日付の列を追加して日付を入力する。
関数やらマクロやらを使えないなら、
こういうやり方で一つずつ処理するしかないと思うんだ。
また、マクロで一発変換できるけど、
そのマクロを作り動作確認を完了するまでの時間と
手作業でちまちま処理した場合の時間を比較して、
どちらが早いかを考え、早い方を選択すると良いでしょう。
特に一回限りの処理だったらマジでこういったことを検討しなきゃならない。
関数を使うなら、日付と時間をあらかじめ記入した表を作り、
その日付と時間に対応したセルを参照させるような数式を作ればいい。
OFFSET関数とCOLUMN関数とROW関数で実現できる。
マクロや関数わかんないから「代わりに作ってwww」…という事であれば、作業依頼はQ&Aサイトでは受けられないので他を当たりましょう。
作業依頼じゃなくて自分で解決できるようになりたいというなら、
・どんなやり方をしたいのか、
・そのやり方の中で何が分からないのか、
を補足に書いてください。
可能な範囲でアドバイスをしますよ。
有難うございます。
非常に参考になります。
今回と同様のデータが複数あり、表に加工したいデータも複数ある為、僕は銀鱗さんがおっしゃるところの
『関数を使い、日付と時間をあらかじめ記入した表を作り、その日付と時間に対応したセルを参照させるような数式を作る。OFFSET関数とCOLUMN関数とROW関数で実現』をやりたいと思います。
アドバイス頂く事は可能でしょうか。
宜しくお願い致します。
No.2
- 回答日時:
こう言う事?
代表式、
=IFERROR(OFFSET(Sheet1!$A$2,1,SUMPRODUCT((B$1=Sheet1!$2:$2)*($A2=Sheet1!$1:$1)*COLUMN(OFFSET(Sheet1!$A$1,1,0,1,COUNTA(Sheet1!$2:$2))))-1,1,1),"")
ファイル、
https://1drv.ms/x/s!AjviygfJDgV_21EwJEsoz18q2wFG
尚、
必ず 1度、
Excelで 開き、
ローカルに 別名保存してから、
其れを 扱ってくださいね。
別名保存しないと、
意味が ないですよ。
そうすれば、
閲覧も、編集も、
叶うと 思います。
有難うございます。
エクセルonlineのような表にしたいと思います。
記載の代表式を別シートに数式コピーすれば元データを参照して表ができあがるのでしょうか?
縦軸の日付は2018年12月27日~2019年3月8日で土日祝祭日以外の平日のデータで、横軸の時間は9時00分から5分刻みで15時00分までのデータですので列はBからBJまでの62列の表です。
宜しくお願い致します。
No.4
- 回答日時:
添付図参照
B7: =OFFSET($A$1,2,COLUMN(A1)-1+(ROW(A1)-1)*73)
有難うございます。
B7: =OFFSET($A$1,2,COLUMN(A1)-1+(ROW(A1)-1)*73)を張り付けてオートフィルで試しました。
添付図のようにはならず、最新日付の3月8日の数値だけが正しいですが後は、でたらめな数値が並びます。A列の日付項目と1行目の時間項目はすべて#REFになってしまいます。
セルの書式設定が原因ではありません。
縦軸の日付は2018年12月27日~2019年3月8日(直近)で土日祝祭日以外の平日のデータです、横軸の時間は9時00分から5分刻みで15時00分までのデータですので列はBからBJまでの62列の表を作りたく思います。
宜しくお願い致します。
No.5
- 回答日時:
》 でたらめな数値が並びます
そうでしたか。大変、失礼しました。
「土日祝祭日以外の平日のデータ」等の情報は出し惜しみしないで最初から仰るべきでしたね。
》 横軸の時間は9時00分から5分刻みで15時00分までのデータですので列はBからBJまでの62列
私は15時00分から9時00分までの73列としていました。(;^_^;)
》 宜しくお願い致します。
元気を無くした私は退散するので、他の方の回答に学んでください。
先刻は失礼致しました。
『 横軸の時間は9時00分から5分刻みで15時00分までのデータですので列はBからBJまでの62列』は僕の間違いでした。
すみません。
mike_gさんのおっしゃる通り、15時00分から9時00分までは73列です。
それを僕自身が見落としてました(^_^;)
お昼の1時間が抜かれての62列でした。
ほんとうにスミマセン!
表自体はmike_gさんの教えてくれた数式を元に完成し、すでに複数のデータをぶち込んで、今、表を量産しているところです。
本当に助かりました。
有難うございます!!
No.6
- 回答日時:
ご質問 有難うございます。
此の式は 位置異存が、
多少 ありますので、
先ずは、
シート1の データ値を、
改変される 所から、
確認され、
データセルを 動かしたり、
等々 様々に、
構造を 理解されてから、
実際の 適応動作に、
移られたが、
良いかと 思いますよ。
No.7
- 回答日時:
少し 変更しました、
式、
=IFERROR(OFFSET(Sheet1!$A$2,1,SUMPRODUCT(
(B$1=OFFSET(Sheet1!$A$1,1,0,1,COUNTA(Sheet1!$2:$2)))
*($A2=OFFSET(Sheet1!$A$1,0,0,1,COUNTA(Sheet1!$2:$2)))
*COLUMN(OFFSET(Sheet1!$A$1,2,0,1,COUNTA(Sheet1!$2:$2)))
)-1,1,1),"")
後、
対応件数を、
約500日から、約230日に、
減らしました、
やや、
処理 過多でしたからね〜。
所で、
変更を ファイルに、
適応させて おきましたので、
ファイルの 別名保存更新を、
お願いします。
No.8
- 回答日時:
なんか数式を見てそれを理解していないように思うので、
思いっきり簡単な手抜きの数式で考え方の説明をしたほうが良さそうですね。
また、手抜きではありますが長いので心してください。
まず使うのはOFFSET関数。
=OFFSET( 基準セル , 行方向 , 列方向 )
のようにして使う。
=OFFSET(A1,2,4)
なら、
=E3
と同じことになります。
「A1セルから、下へ2セル、右へ4セルの場所のセル」
という意味になります。
…これ、基本です…
・・・
では、この数式で横長に並んだデータを
質問にあるように並べ替えてみましょう。
ただしデータの数が多いと考え方が分からなくなりますので、
1日分のデータは4個(質問の例では73個)と仮定して説明します。
元のデータがA1セルから並んでいるとすると、
並べ替えたいデータはA3セルから右に並んでいるという事になります。
そして並べ替えるデータはB6セルからとします。
まず「B6セル」に入力する数式は
=A3
になります。
これをOFFSET関数を使って表現してみましょう。
基準のセルをA1セルとするならば、
「A1セルから、下へ2セル、右へ0セルの場所のセル」
という事ですから
=OFFSET(A1,2,0)
となります。
では「C6セル」に入力する数式はどうなるかを考えてください。
=OFFSET(A1,2,1)
とする事ができます。
同様に「D6セル」「E6セル」はそれぞれ
=OFFSET(A1,2,2)
=OFFSET(A1,2,3)
となります。
何のことはない。OFFSET関数の3つ目のパラメータが1つずつ増えるだけです。
これは、
=OFFSET( 基準セル , 行方向 , 列方向 )
として指定するため当然のことです。
ならば、横方向に横着して数字を増やすように関数でこの数字を変えてやればいい。
ここはCOLUMN関数を使います。
COLUMN関数は指定したセルが何列目にあるかを返す関数です。
=COLUMN(F10)
としたならば、F列は左から6列目なので「6」が値として返ってきます。
そして、COLUMN関数にはもう一つの使い方があります。
それはパラメータを与えないで使う方法です。
=COLUMN()
こう使う。
すると、この数式が入力されている列が何列目になるかを返してきます。
この数式を
「H6セル」に入力すると「8」
「T6セル」に入力すると「20」
が値として返ってきます。
「B6セル」に入力すると「2」が返るという事です。
ならば「B6セル」には
=OFFSET(A1,2,COLUMN()-2)
とする事で
=OFFSET(A1,2,0)
と同じ結果を得られるという事になります。
この数式の良いところは、はC6、D6、E6の各セルでまったく同じ数式が使えるという事です。
C6セルでは3つ目のパラメータに使っている「COLUMN()」が「3」を返すので「3-2」となって「1」が指定されることになります。
D6、E6セルでも同様です。
…これはCOLUMN関数を使った数字を増やすやり方です…
では「B7セル」はどうなるのか、
=OFFSET(A1,2,4)
と、なります。
やはり3番目のパラメータが増えます。
しかし
=OFFSET(A1,2,COLUMN()-2)
では「B6セル」と同じ値を拾ってきます。
これは当然です。
縦方向へ1つずれたら、B6セルが参照するセルより「4つ」右のセルを参照させたいところです。
そこで出てくるのがROW関数。
COLUMN関数が何列目にあるかを返すのに対し、ROW関数は何行目にあるかを返す関数です。
1行下のセルなら「4」を加えろと加えてやれば良いわけです。
=OFFSET(A1,2,COLUMN()-2+(ROW()-6)*4)
はい。完成。
B6セルでは「4」を足す必要が無いのでROW()関数が返す「6」に対して「-6」してやればいい。
あとは下の行へ行けば1行ずつ「4」が増える式になります。
長々と書きましたが、こんだけですよ。
考え方をまとめると数式は
=OFFSET(A1,2,COLUMN()-2+(ROW()-6)*4)
となるんです。
順を追って考えるととてもシンプル。
どんな関数があるのかさえ知っていれば、それを組み合わせるだけです。
最後に「B6セル」に数式を入力したあと、「B6セル」をコピーして楽をするために少し数式を変えます。
=OFFSET(A1,2,COLUMN()-2+(ROW()-6)*4)
↓
=OFFSET($A$1,2,COLUMN()-2+(ROW()-6)*4)
基準セルを絶対参照させます。
あとはエラー対策や値が無い場合の処理を加えるだけですが、
とりあえずは
=OFFSET($A$1,2,COLUMN()-2+(ROW()-6)*4)
のような数式で対処可能でしょう。
・・・
調手抜きな説明をしましたので、実際のデータに合わせて「自身で修正」を加えてください。
ここまで読んで理解されていれば難しいことではありませんね。
No.4の回答を見たときに数式を理解できるかがこれで決まります。
・・・余談・・・
>横軸の時間は9時00分から5分刻みで15時00分までのデータですので列はBからBJまでの62列の表を作りたく思います。
矛盾してるよ。
9時から15時まで5分刻みのデータなら73列になる。
1時間に12個データがあるんだ、
9時台、10時台・・・14時台、15時0分
のデータの数の合計は73個だろ?
62列とするなら、
10時から15時まで、または9時から14時までの61個。
B列からデータを入れるから1日あたり61列。A列は日付が入るので、62列目のBJ列までの表にしたい。
…という事になる。
さて、全部読んで理解できたかな。
有難うございます。
丁寧なご説明ありがとうございます。
でも理解するまでには数回読み直さなくてはなりません(^_^;)
表自体はmike_gさんの教えてくれた数式を元に、完成しました。
さっそく複数のデータはぶち込んでは表を量産してます。
※矛盾してました。
横軸の時間は「9時00分から5分刻みで15時00分まで62列」はお昼の1時間を抜いてのデータ数でした。
失礼致しました。
ご丁寧な説明ありがとうございます。
No.10
- 回答日時:
上の表がA1から始まっている。
下の表が時刻がB8から横に日付がA9から下に入っているとして
B9=SUMIFS($A$3:$T$3,$A$1:$T$1,$A9,$A$2:$T$2,B$8)
右と下にコピー
面倒なので範囲を小さくしてますが実際の表に合わせて作って下さい。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) エクセル「社員の重なっている仕事時間の算出方法について」教えてください。 6 2023/02/06 00:10
- Visual Basic(VBA) 3つのプロシージャをまとめたら実行時エラー発生で対応不能 6 2022/05/17 01:47
- その他(Microsoft Office) エクセル 条件付き書式 日をまたぐ塗りつぶし 1 2023/01/13 18:00
- Excel(エクセル) エクセルでフィルタ後、考えている場所に値コピーができない。 1 2022/05/02 21:01
- Excel(エクセル) 再質問 エクセル「社員の重なっている仕事時間の算出方法について」教えてください。 10 2023/02/07 19:16
- Visual Basic(VBA) 列と行の名前(重複あり)が交差するセルに、データを入力したい 3 2022/06/12 11:17
- Excel(エクセル) 特定のデータの抽出方法を教えてください@Excel 4 2023/06/13 18:38
- Excel(エクセル) Excel 数行を1組とする300組ほどあるデータの項目を揃えたいです。 3 2023/01/26 19:38
- Excel(エクセル) VBAで重複データを合算したい(時間) 1 2022/12/08 23:06
- Excel(エクセル) Excel あらかじめ予定表があり、その月毎のセルに、リストの連続データを入れたい 2 2022/04/07 14:20
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセル初心者です 関数の入れ...
-
Microsoft1Officeの互換ソフト...
-
Excel ピボットテーブルで日付...
-
エクセル関数を教えてください
-
【マクロ】その時、その時で変...
-
【マクロ】読取専用のファイル...
-
LOOKUP関数を使えばいいのでし...
-
エクセル 白黒印刷で白線を印刷...
-
【関数】先頭だけにある、半角...
-
【関数】適切な文字数の数字を...
-
Excelのチェックボックスの使い...
-
エクセルでの作業計算方法について
-
Excelのpivotについて質問です
-
WPS OFFICEでの縦書きについて
-
時間によってファイル名が変わ...
-
エクセルのセルに同じ大きさの...
-
Aというブックの1というシート...
-
エクセルの順位別一覧表の自動...
-
西暦や和暦の表示をyyyymmdd表...
-
【マクロ】エクセルにかいてあ...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excel 2019 のピボットテーブル...
-
[関数得意な方]教えて下さい・...
-
Excelにてある膨大なデータを管...
-
[関数について]わかる方教えて...
-
Excel初心者です。 詳しい方、...
-
excelの不要な行の削除ができな...
-
エクセル関数に詳しい方教えて...
-
INDIRECTを使わず excelで複数...
-
[オートフィルタ]で抽出された...
-
エクセルの神よ、ご回答を! エ...
-
エクセル関数に詳しい方、教え...
-
各ページの1番上の表示について
-
Excelで写真のような表を作った...
-
エクセルで不等号記号(≠)が上に...
-
数学 Tan(θ)-1/Cos(θ)について...
-
Excel 2019 は、SPILL機能があ...
-
Excelで全角を半角にしたいので...
-
条件付き書式を教えてください
-
Excel フィルターを掛けた状態...
-
[オートフィルタ]の適用範囲の...
おすすめ情報