- ベストアンサー
Excelで数値を並べ替えたい
Excelで、A列に西暦年が1960年から20年分、B列に月、C列に日、D列に時刻(1時間単位)、E列に数値が入ったシートがあります。 間には閏年もあります。 7000行強の列があることになります。 これを年ごとに別シート(20シート)に分けたいです。 シート内ではA列に年月日および時刻(1960/1/1 1:00)、B列に元のE列の値を並べます。 複数ファイルあるので、可能ならばファイルごとに列方向(C列、D列、E列…)に順次整列もできればなおありがたいです。 VBAで処理できますでしょうか。 参考になりそうな記述をご教示頂ければ幸いです。
- tokyowar
- お礼率53% (82/154)
- Excel(エクセル)
- 回答数3
- ありがとう数3
- みんなの回答 (3)
- 専門家の回答
質問者が選んだベストアンサー
こんばんは! とりあえず1Bookだけの操作です。 Sheet2を作業用のSheetとして使用していますので、 最低Sheet数は2つあり、「Sheet2」というSheet名のSheetがあるという前提です。 標準モジュールの一例です。 Sub Sample1() Dim i As Long, k As Long, endRow As Long, myFlg Dim Ws1 As Worksheet, wS2 As Worksheet Set Ws1 = Worksheets("Sheet1") '←元データのSheet Set wS2 = Worksheets("Sheet2") '←作業用のSheet Application.ScreenUpdating = False endRow = Ws1.Cells(Rows.Count, "A").End(xlUp).Row Range(Ws1.Cells(1, "A"), Ws1.Cells(endRow, "A")).AdvancedFilter Action:=xlFilterInPlace, unique:=True Ws1.Range("A:A").Copy wS2.Range("A1") Ws1.ShowAllData wS2.Range("A:A").Sort Key1:=wS2.Range("A1"), Order1:=xlAscending, Header:=xlYes For i = 2 To wS2.Cells(Rows.Count, "A").End(xlUp).Row For k = 3 To Worksheets.Count If Worksheets(k).Name = wS2.Cells(i, "A") & "年" Then myFlg = True End If Next k If myFlg = False Then Ws1.Range("A1").CurrentRegion.AutoFilter field:=1, Criteria1:=wS2.Cells(i, "A") Worksheets.Add After:=Worksheets(Worksheets.Count) Worksheets(Worksheets.Count).Name = wS2.Cells(i, "A") & "年" Ws1.Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy Worksheets(Worksheets.Count).Range("A1") End If With Worksheets(Worksheets.Count) endRow = .Cells(Rows.Count, "A").End(xlUp).Row .Range("A:A").Insert .Range("A1") = "年月日 時刻" With Range(.Cells(2, "A"), .Cells(endRow, "A")) .Formula = "=DATE(B2,C2,D2)+E2" .Value = .Value .NumberFormatLocal = "yyyy/m/d h:mm" End With .Range("B:E").Delete .Range("A1").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes End With Next i wS2.Cells.Clear Ws1.AutoFilterMode = False Application.ScreenUpdating = True MsgBox "処理完了" End Sub こんな感じではどうでしょうか?m(_ _)m
その他の回答 (2)
- bunjii
- ベストアンサー率43% (3589/8248)
作業用に1列使えれば関数でも比較的簡単に処理できます。 元のデータがSheet1とします。 結果を求めるシートは任意の名前でかまいません。 G列を作業用として目的の年数をG1セルにセットし、G2セルに次の式を入力します。 =IFERROR(SMALL(IF((Sheet1!$A$2:$A$10000=G$1),ROW($2:$10000),""),ROWS($2:2)),"") この式は返り値が配列になりますのでCtrl+Shift+Enterで確定します。 G2セルを必要数下へコピーします。 A2セルには次の式を入力し、必要数を下へコピーします。 =IFERROR(DATE(INDEX(Sheet1!A:A,G2),INDEX(Sheet1!B:B,G2),INDEX(Sheet1!C:C,G2))+INDEX(Sheet1!D:D,G2),"") B2セルには次の式で、同様に必要数を下へコピーします。 =IFERROR(INDEX(Sheet1!E:E,G2),"") 出来上がったシートをコピーしてG1セルの西暦年を入力すれば完了です。 添付画像は同一シート内で試したものです。 尚、元データは日時が順不同になっており、データの行数が少ない状態です。
お礼
試行は後刻になるので、先ずは回答を頂いた御礼まで。お手数をおかけ頂きありがとうございました。
- soixante
- ベストアンサー率32% (401/1246)
データが入っているシートがSheet1として、このブックにはこのシートしか入っていないとします。 ************************************************************************* Option Explicit Sub zzz() Dim r As Long, c As Integer Dim i As Integer, j As Integer, k As Integer Dim Nen As Integer Dim Lstrow As Long Dim Ws1 As Worksheet Set Ws1 = ActiveSheet Worksheets.Add After:=Ws1, Count:=20 For i = 2 To Worksheets.Count With Worksheets(i) .Name = 1958 + i .Cells(1, 1).Value = "YMDH" .Cells(1, 2).Value = "Value" End With Next i Application.ScreenUpdating = False r = 2 Do While Ws1.Cells(r, 1).Value <> "" With Ws1 Nen = .Cells(r, 1).Value .Range(.Cells(r, 1), .Cells(r, 5)).Copy Lstrow = Worksheets(Nen - 1958).Cells(Rows.Count, 1).End(xlUp).Row + 1 Worksheets(Nen - 1958).Cells(Lstrow, 1).PasteSpecial Paste:=xlPasteAll End With r = r + 1 Loop Application.ScreenUpdating = True Application.ScreenUpdating = False For i = 2 To Worksheets.Count Worksheets(i).Select j = 2 Do While Cells(j, 1).Value <> "" Cells(j, 6).Value = DateSerial(Cells(j, 1), Cells(j, 2), Cells(j, 3)) + TimeSerial(Cells(j, 4), 0, 0) Cells(j, 7).Value = Cells(j, 5).Value j = j + 1 Loop Range(Columns(1), Columns(5)).Delete Cells(1, 1).Value = "YMDH" Cells(1, 2).Value = "Value" Columns(1).NumberFormatLocal = "yyyy/m/d h:mm" Columns.Sort _ Key1:=Cells(1, 1), _ Order1:=xlAscending, _ Header:=xlYes, _ Orientation:=xlTopToBottom Cells(1, 1).Select Next i Worksheets(2).Select Application.ScreenUpdating = True Set Ws1 = Nothing MsgBox "Completed." End Sub *********************************************************************** 大まかに流れを書くと以下の通りです。 Sheet1の後ろに、新たに20枚シートを作成する。 元データを1行ずつ、それぞれの年にコピペ データがそれぞれの年のシートにコピーされたら、1960年シートから順に体裁を整えています。 年月日・時刻の統合 A列の昇順並び替え 終了。 >可能ならばファイルごとに列方向(C列、D列、E列…)に順次整列もできれば → ここはちょっと意味が読み取れなかったので対応していません。 7000行ですと若干時間がかかるかもしれません。私も7000行でテストしてみましたが、私のPCは少し重いので15秒くらいかかりました。 原始的な方法なのですみませんm(_ _)m 元データのバックアップは必ず取ったうえで試してください。 もっとスマートな回答があればそちらをご参照ください。
お礼
まさかこんなに長くなるとは…回答頂き感謝しております。試行は後刻になるので、先ずは回答を頂いた御礼まで。ありがとうございました。
関連するQ&A
- 一番大きな数値がどこにあるか返す
Excelで次のようなデータが入っているシート上で、一番大きな数値が入っているセルの位置をe列に返す関数の組み方がわかりません。 A B C D E 30 45 42 51 だったら、e列から1列隣の51が一番大きいので A B C D E 30 45 42 51 1 同じく、 A B C D E 23 40 32 31 だったら A B C D E 23 40 32 31 3 といった感じの関数の組み方を教えてください。
- ベストアンサー
- オフィス系ソフト
- エクセル:データ一致したとき1を足す
教えて下さい。 シート(1)A列とB列に Aさん 2 Bさん 1 Cさん 3 Dさん 5 と1000行くらいあります。 シート(2)A列に Aさん Dさん Eさん とあります。 シート(2)にある人のみシート(1)のB列の数字に1足したいのですが何か方法はありますか? 自慢じゃないけどVBAは分かりません。 どうぞよろしくm(__)m
- ベストアンサー
- オフィス系ソフト
- Excelのセルにおける変則的なコピー
VBAを使わずにこんなことができますか? いい方法があれば教えて下さい。 言葉でしか伝えられませんが、これでやりたいことがわかっていただけますでしょうか? 1.[Sheet1]のA列~E列に数値が入力されているとします。 2.これを[Sheet2]に1行を2行に分割しながら貼り付けたいんです。 ・[Sheet1]A1~C1を[Sheet2]A1~C1へ、[Sheet1]D1~E1を[Sheet2]B2~C2へ。 ・[Sheet1]A2~C2を[Sheet2]A3~C3へ、[Sheet1]D2~E2を[Sheet2]D4~C4へ。 ・[Sheet1]A3~C3を[Sheet2]A5~C5へ、[Sheet1]D3~E3を[Sheet2]B6~C6へ。 ・ ・ ・ イメージはこんな感じです。 実際にはA列からDC列まで100行程あるものを、4行ずつに分割したいんです。 2列に分ける方法の応用でできると思っているのですが、いかがでしょうか? どうかよろしくお願いします。
- ベストアンサー
- オフィス系ソフト
- EXCELについて
iwao32と申します。EXCELについて教えてください。(全くの初心者です。) sheet 1 sheet 2 A列 B列 A列 B列 1 10 4 d 2 20 5 e 3 30 2 b 4 40 3 c 5 50 1 a という2つのsheetがあるとき(A列は共通だが並んでいる順番が違い、B列はそれぞれ別のもの)、sheet3に、 A列 B列 C列 1 10 a 2 20 b 3 30 c 4 40 d 5 50 e のように、結合したいのですが、なるべく簡単なやり方をお教え下さい。
- 締切済み
- その他MS Office製品
- sheet2からsheet1へのデータの飛ばし方(エクセル関数)
Sheet1 A B C D E (列) ―――――――――――――――――――――――― 1| 番号 氏名 所属 入社 生年月日 2| 1000 斉藤 東京 10/4 (入力箇所) sheet2 A B C D (列) ――――――――――――――――――― 1| 番号 氏名 所属 生年月日 2| 1000 斉藤 東京 1988/11/1 sheet1のE行(生年月日)にsheet2を参照し、同じ番号の人の生年月日が表示されるようにしたいのですが、どのような式(関数)を入れたら表示されるでしょうか? ほかの似たような質問の解答を見てMatchやvlookupを使ってみましたが、できなかったので教えてください。 よろしくお願いします。
- ベストアンサー
- その他(インターネット・Webサービス)
- Excel 一括変換
A列 B列 C列 D列 28 6 5 2016/6/5 29 10 15 2017/10/15 年月日で分かれているのを一つのセルに西暦に変換して入れたいです。 たくさんあるので一括で簡単にできる方法を教えてください。 関数でもマクロでもいいです。
- ベストアンサー
- Excel(エクセル)
- エクセルでの条件式等による数値集計
エクセルの条件式&集計についての質問です。 A列に売上年月日 C~F列には1行目は仕入年月日、2行目以降は仕入額が入っています。C~F列は同じ年月日は発生しません。 A列が07/2/28で商品1の場合、A列と同じ年月日のD列までの仕入額合計”30”をG列に表示させたいのです。 G列にどのような計算式を入力すればよいのでしょうか? マクロなどを使用せずに処理したいのですが…宜しくお願い致します。 A:売上日 B:品名 C:07/1/31 D:07/2/28 E:07/3/31 F:07/4/30 G:計 07/2/28 商品1 10 20 15 5 30 07/2/28 商品2 60 90 20 70 150 07/3/31 商品3 20 50 10 90 80 07/4/30 商品4 0 200 10 80 290
- ベストアンサー
- オフィス系ソフト
- Excelで複数のSheetにある数字をひとつのSheetにまとめる
あるExcelファイルに5個のSheetがあります。Sheet1~Sheet4までは週間の項目実績です。A列はアイテム、B列は目標、C列は実績、D列は差異、E列は達成率。 このSheet1~4を合算したものをSheet5に表示したいのですが、VBAでどのように作成すれば良いのですか? =Sheet1A1+Sheet2A2+ .....ってやるの面倒なので。
- 締切済み
- その他(インターネット・Webサービス)
- エクセルマクロで条件に該当する数値を探す
エクセル初心者です。 シート1のA1~A18に1~18の数値がランダムに入っています。(重複した値は無し) シート2の3行目C3~T3にも1~18の数値がランダムに入っています。(空欄あり、重複した数値は無し) シート2の2行目C2~T2までは左から順に1~18の数値が入っています。(順位表示) 以上の条件下で下記の作業をマクロで実行させる式を教えてください。 シート1のA1~A18の値と同じ値を、 シート2の3行目C3~T3の中からどのセル(列)に入っているかを確認して、さらに該当する列の2行目C2~T2の値を、シート3の1行目A1~R1に表示させる。 例:シート1のA1=3、A2=2、A3=1の時、 シート2のC3=3、D3=1、E3=2でした。 シート2のC2=1、D2=2、E2=3です(この行の数値は毎回同じ) 上記の時シート3にはA1=1、B1=3、C1=2となります。 *シート1のA列A1~A18とシート3の1行目A1~R1の位置関係はリンクしています。(列→行) シート1 A列 1 3 2 シート2 A列 B列 C列 D列 E列 2行見出し見出し1 2 3 3行見出し見出し3 1 2 シート3 A列 B列 C列 2 1 3 ご理解頂けたでしょうか? 初心者ですので分かりやすい回答を宜しくお願いいたします。
- ベストアンサー
- オフィス系ソフト
- EXCELのデータベース利用について(VBA)
VBA初心者の者です。 下記の様なEXCELファイルを作りたいのですが、どのようにすれば良いのか まったくわかりません。 ACCESSでの構築が簡単なのかもしれませんが、データ量がそれほど多くないことと 職場にACCESSがないため、可能であればEXCELで構築したいと考えています。 私自身、プログラムの知識がなく、EXCEL VBAのサイトを確認するのですが、いまいち どうすれば良いのかわかりません。 ぜひご教授の程よろしくお願いします。 3つのシートの構成は以下になります。 【入力シート】 A1セルに文字列(A~Z)入力欄 【○○データシート】 A列に A~Z の文字列 B列に 001~100までの数値 C列に 001~100までの数値 D列に 001~100までの数値 E列に 001~100までの数値 例) A列 B列 C列 D列 E列 1 A 001 2 B 001 002 003 004 3 C 003 4 D 002 003 【▽コマンドシート】 A列に 001~100までの一意の数字 B列に 文字列(コマンド) C列に 文字列(コマンド詳細) 例) A列 B列 C列 1 001 xx blank 2 002 xxx△ xxx 3 003 xxxx x○ 4 004 xxxxx xxx 【欲しいVBA】 1、入力シートのA1セルに○○データシートのA列に該当するA~Zの 文字列を入力。 2、○○データシートのB列~F列までの数値を参照 3、2の数値において▽コマンドシートのA列に記載ある番号と紐づけを行い、 ▽コマンドシートのB列、C列に記載がある文字列を入力シートのB列、C列にコピー ※コピー時は▽コマンドシートの書式や体裁情報も含めてコピー。 例としては以下になります。 入力シートの A1セルにDを入力した場合は 入力シート A列 B列 C列 1 D xxx△ xxx 2 xxxx x○ 3 以上、ご教授よろしくお願いします。
- ベストアンサー
- その他(業務ソフトウェア)
お礼
まさかこんなに長くなるとは…回答頂き感謝しております。試行は後刻になるので、先ずは回答を頂いた御礼まで。ありがとうございました。