[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『CSVを表にする』（TOMTOM）
お世話になっております。
エクセル初心者です。
sheet1に表があり（CSVを貼り付けたものになります。）
Ｃ列に「2018/7/1」というように日付が入っています。
Ｄ列に「朝」「昼」「夕」と時間が入っています。
Ｇ列に「ハンバーグ」などのメニューが入っています。
そのSheet１の内容を別のシートに表の形で読み込ませたいです。
表にしたいシートは、1週目、2週目・・・6週目まで6シートあります。
各シート、5行目に曜日が手入力で入り、縦列はC列が月曜日、Hが火曜日、M列が水曜日、R列が木曜日、W列が金曜日、AB列が土曜日、AG列が日曜日となっています。
6行目に１、２、３・・・と日付けをあらわす数字を
手入力で入力していています。
「朝」が7行目から始まり、「昼」が19行目から、「夕」が29行目からで
それぞれに対応したsheetのG列にあるメニューを入れたいです。
どのような関数をいれれよいか、ご指導いただけますでしょうか？
1日のスタートの曜日が月によって違うので、それをうまくできたらいいのですが、複雑な構造になるようでしたら、関数をコピペして、各月手動でやります。
説明がうまくできず申し訳のざいません。
よろしくお願いいたします。
＜ 使用 Excel：Excel2016、使用 OS：Windows10 ＞
[C] [D] ・・・ ・・・ [G]
┌────┬──┬───┬───┬──────┐
[2] │2018/7/1│朝 │ │ │焼き魚 │
├────┼──┼───┼───┼──────┤
[3] │2018/7/1│昼 │ │ │から揚げ │
├────┼──┼───┼───┼──────┤
[4] │2018/7/1│夕 │ │ │ハンバーグ │
├────┼──┼───┼───┼──────┤
[5] │2018/7/2│朝 │ │ │ハムエッグ │
├────┼──┼───┼───┼──────┤
[6] │2018/7/2│昼 │ │ │スパゲッティ│
├────┼──┼───┼───┼──────┤
[7] │2018/7/2│夕 │ │ │グラタン │
├────┼──┼───┼───┼──────┤
[8] │2018/7/3│朝 │ │ │うどん │
├────┼──┼───┼───┼──────┤
[9] │2018/7/3│昼 │ │ │そば │
├────┼──┼───┼───┼──────┤
[10]│2018/7/3│夕 │ │ │ラーメン │
└────┴──┴───┴───┴──────┘
1週目（2週目？）>>
[C] [D] [E] [F] [G] [H] [I]
┌─┬────────────────────┬────┬───┬───┬───┬───┬───┐
5]│ │月 │火 │水 │木 │金 │土 │日 │
├─┼────────────────────┼────┼───┼───┼───┼───┼───┤
6]│ │ 7月2日│ 7月3日│7月4日│7月5日│7月6日│7月7日│7月8日│
├─┼────────────────────┼────┼───┼───┼───┼───┼───┤
│ │=INDEX(Sheet1!$G:$G,SUMPRODUCT((Sheet1!$│ │ │ │ │ │ │
7]│朝│C:$C='1週目'!C$6)*(Sheet1!$D:$D='1週目'!│うどん │ │ │ │ │ │
│ │$B7)*ROW(Sheet1!$G:$G))) │ │ │ │ │ │ │
├─┼────────────────────┼────┼───┼───┼───┼───┼───┤
8]│昼│スパゲッティ │そば │ │ │ │ │ │
├─┼────────────────────┼────┼───┼───┼───┼───┼───┤
9]│夕│グラタン │ラーメン│ │ │ │ │ │
└─┴────────────────────┴────┴───┴───┴───┴───┴───┘
無駄な空白列や空白行を入れると、
フィルコピーとかできなくなるけど、ま、コピペで対処できるかと思います。。
日付は、普通に入れておいて、あるいは参照しておいて、
セルの表示設定で日だけにした方がよいです。
日付も計算で出来ると思いますが、
また、思いついたら書くかもです＾＾；；
（まっつわん） 2018/06/12(火) 15:03
実際はCSVの並び方はこのようになります
[C] [D] [G] 2018/7/1 昼食 コロッケ 2018/7/1 昼食 煮物 2018/7/1 昼食 胡瓜ともやし辛子和え 2018/7/1 昼食 ごはん 2018/7/1 昼食 味噌汁 2018/7/1 夕食 豚肉ソテー 2018/7/1 夕食 茄子の田楽 2018/7/1 夕食 おくらお浸し 2018/7/1 夕食 ごはん
品数は日によって違います。なので日付けと「朝」「昼」「夕」の紐付けが必要な気がします
またちなみに、CSVの構造が「昼」「夕」が先に日付け順に並び、
その後「朝」だけ日付け順に並んでいました。
よろしくお願いいたします。
（TOMTOM） 2018/06/12(火) 15:59
（まっつわん） 2018/06/12(火) 16:10
また、朝昼夜の行位置ですが、等間隔でないのは本当ですか？ 規則性がないと、数式でもマクロでも余計な処理や計算が必要になり、面倒だと思います。
CSVファイルと出力シートは、どうやってブックに同居させるのでしょう？ 普通にCSVファイルをダブルクリックすると、1つのブックのようになるので、出力シートなんて何もない状態になってしまいます。 マクロで読み込むとかなら判りますが、どうやって1つのブックにCSVシートと週シートが含まれる状態になるのでしょう？
日付の並びも、必ず月曜始まりのカレンダーのようですが、毎月これを手作業で日付を埋めているのでしょうか？ 自動化すると良さそうですが…。
そして、数式ではいろいろ難しそうですが、マクロでの処理を考えてはいかがでしょう？
（???） 2018/06/12(火) 17:56
幾つかお聞きします。
1点目： CSVは1か月分のデータのみが記載されているんですか？ それとも数か月に跨った量のデータが記載されているんですか？
2点目: 1週目は1日が含まれる週で良いんですか？ 1日が日曜日とかでも、2日は2週目になるんですか？
3点目: CSVデータは、別にEXCELでCSVファイルを開いて、データだけをコピペしてるのか？ 外部データの取込機能で直接読み込んでいるのか？ どちらでしょうか？
上記3点の内容次第で、数式が大きく変わるのでお答えください。
（sy） 2018/06/12(火) 19:02
因みに、上レスの3点の内容は、
1点目は、データは1か月分のみ 2点目は、1日を含む週が必ず1週目 3点目は、別で開いてコピペ
であれば、比較的簡単に出来ます。
1点目はそうでもないけど、 2点目が変動的だと結構複雑な式になります。 3点目が外部データの取込だと、読込データの増減で数式の参照先が一部ずれたりするので、 揮発性関数nのINDIRECT関数が必要になるので、常に全部の式が再計算されるので、 数式の複雑さやデータ量によっては重くなります。
（sy） 2018/06/12(火) 19:11
ありがとうございます。
ご質問の件、下記ご確認ください。
出力シートのレイアウトがいまいち判らないのですが、行も列もずいぶんと間が空いていますが、セル連結していたりしますか？ 数式でもマクロでも、セットする列は連続している方が楽なのですが。
→出力シートのレイアウトはセルが連結されたり、見やすくするために列は余分に挿入されていたりします。
CSVファイルと出力シートは、どうやってブックに同居させるのでしょう？
→すみません、CSVで出力したものを貼り付けているだけです。
日付の並びも、必ず月曜始まりのカレンダーのようですが、毎月これを手作業で日付を埋めているのでしょうか？
→その通りです。1週目のシートは1日の曜日によって入力がない曜日があります。
数式ではいろいろ難しそうですが、マクロでの処理を考えてはいかがでしょう？
→できればその後、応用できるように理解ができる数式にしたいです。
ある程度の手作業が発生するのも仕方ないと思いますが、
マクロで、どのようにできるかも教えていただきたいです。
ｓｙ様
ありがとうございます。
1点目： CSVは1か月分のデータのみが記載されているんですか？ それとも数か月に跨った量のデータが記載されているんですか？ →1ヶ月まとめたデータになります。 逆に期間指定してCSVを出せば、短期間のものも出力できます。
2点目: 1週目は1日が含まれる週で良いんですか？ 1日が日曜日とかでも、2日は2週目になるんですか？ →その通りです。1日が日曜の場合は1週目のシートは1日分だけの献立になります。
3点目: CSVデータは、別にEXCELでCSVファイルを開いて、データだけをコピペしてるのか？ 外部データの取込機能で直接読み込んでいるのか？ どちらでしょうか？ →データだけ、コピペしています。
説明不足で申し訳ございません。
よろしくお願いいたします。
（TOMTOM） 2018/06/13(水) 10:03
Sub test()
Dim cFiles As Variant
Dim vw As Variant
Dim F1 As Integer
Dim i As Long
Dim j As Long
Dim iR As Long
Dim iC As Long
Dim iSheet As Long
Dim iFlag As Long
Dim dw As Date
Dim dMax As Date
Dim dMin As Date
Dim cw As String
With Application.FileDialog(msoFileDialogOpen)
.Filters.Clear
.Filters.Add "CSVファイル", "*.csv"
.InitialView = msoFileDialogViewLargeIcons
If Not .Show Then Exit Sub
Set cFiles = .SelectedItems
End With
For i = 1 To Sheets.count
With Sheets(i)
If .Name Like "*週目" Then
.Cells.ClearContents
.Range("D7") = "朝"
.Range("D19") = "昼"
.Range("D29") = "夕"
End If
End With
Next i
For i = 1 To cFiles.count
F1 = FreeFile()
Open cFiles(i) For Input As #F1
While EOF(F1) = False
Line Input #F1, cw
vw = Split(cw, ",")
If iFlag = 0 Then
dw = vw(2)
dMin = DateSerial(Year(dw), Month(dw), 1)
dMax = DateSerial(Year(dw), Month(dw) + 1, 0)
For dw = dMin To dMax
iC = Weekday(dw, vbMonday) * 5 - 2
iSheet = Int((Day(dw) + Weekday(dMin, vbMonday) + 5) / 7)
With Sheets(iSheet & "週目")
.Cells(5, iC).Value = Format(dw, "aaa")
.Cells(6, iC).Value = Day(dw)
End With
Next dw
iFlag = 1
End If
dw = vw(2)
iC = Weekday(dw, vbMonday) * 5 - 2
iSheet = Int((Day(dw) + Weekday(dMin, vbMonday) + 5) / 7)
With Sheets(iSheet & "週目")
Select Case vw(3)
Case "朝食"
iR = 7
Case "昼食"
iR = 19
Case Else
iR = 29
End Select
For j = 0 To 11
If .Cells(iR + j, iC).Value = "" Then
.Cells(iR + j, iC).Value = vw(6)
Exit For
End If
Next j
End With
Wend
Close #F1
Next i
End Sub
（???） 2018/06/13(水) 11:42
Sheet1の1行目は項目行、2行目から献立 必ず1日からスタート 各週のシート名は、1週目、2週目、3週目、4週目、5週目、6週目
上記を前提にしています。
AM、AR、AW、BB、BG、BL、BQ列を作業列として使用します。
6行目の日付だけ、各シートで数式が違います。
7行目以下は全てのシートで同じ数式になります。 (7行目以下は1つのシートに数式を入力したら他のシートは一括でコピペして下さい。)
6行目の数式 1週目シート C6 =IF(WEEKDAY(Sheet1!C2,2)=1,Sheet1!C2,"") H6 =IF(C6="",IF(WEEKDAY(Sheet1!$C2,2)=COLUMN(J1)/5,Sheet1!$C2,""),C6+1) M6、R6、W6、、AB6、、AG6にコピペ
2週目シート C6 ='1週目'!AG6+1 H6 =C6+1 M6、R6、W6、、AB6、、AG6にコピペ 3週目と4週目は、C6がそれぞれ1つ前の週を指定に変更 H6～AG6は2週目のH6～AG6をコピペ
5週目シート
C6 =IF(OR('4週目'!AG6="",DAY('4週目'!AG6+1)=1),"",'4週目'!AG6+1)
H6 =IF(C6="","",IF(DAY(C6+1)<10,"",C6+1))
M6、R6、W6、、AB6、、AG6にコピペ
6週目シート
C6 =IF('5週目'!AG6="","",IF(DAY('5週目'!AG6+1)<10,"",'5週目'!AG6+1))
H6 =IF(C6="","",IF(DAY(C6+1)<10,"",C6+1))
7行目以下の数式(全シート共通) C7 =IF(AM$7=0,"",IF(INDEX(Sheet1!$C:$C,AM$7+ROW(A1)-1)=C$6,INDEX(Sheet1!$E:$E,AM$7+ROW(A1)-1),"")) C18までフィルコピー C19 =IF(AM$19=0,"",IF(INDEX(Sheet1!$C:$C,AM$19+ROW(A1)-1)=C$6,INDEX(Sheet1!$E:$E,AM$19+ROW(A1)-1),"")) C28までフィルコピー C29 =IF(AM$29=0,"",IF(INDEX(Sheet1!$C:$C,AM$29+ROW(A1)-1)=C$6,INDEX(Sheet1!$E:$E,AM$29+ROW(A1)-1),"")) C40？(必要数に合わせて下さい)までフィルコピー C7～C40まで選択してコピー、H7、M7、R7、W7、AB7、AG7を選択してペースト
AM7 =IFERROR(MATCH(C6&"朝食",INDEX(Sheet1!$C$1:$C$1000&Sheet1!$D$1:$D$1000,0),0),0) AM19 =IFERROR(MATCH(C6&"昼食",INDEX(Sheet1!$C$1:$C$1000&Sheet1!$D$1:$D$1000,0),0),0) AM29 =IFERROR(MATCH(C6&"夕食",INDEX(Sheet1!$C$1:$C$1000&Sheet1!$D$1:$D$1000,0),0),0) AM7～AM29を選択してコピー、AR7、AW7、BB7、BG7、BL7、BQ7を選択してペースト
（sy） 2018/06/13(水) 20:56
1点忘れてた。
6行目は他の式で計算しやすいように、日付データになっているので、書式設定で d として下さい。
後5行目の月～日は予め手入力しておいて下さい。
（sy） 2018/06/13(水) 21:02
ありがとうござました。
マクロでやってみようと試みたのですが、
うまく動かず、もう少し時間をかけて検証してみます。
ｓｙ様
ありがとうございました。
だいたいできたのですが、昼食の欄が各曜日（各列）19行目から27行目なのですが、
24行目から夕食のメニューが表示されてしまいました。
24行目はこのような数式が入っています。
=IF(AM$19=0,"",IF(INDEX(Sheet1!$C:$C,AM$19+ROW(A6)-1)=B$6,INDEX(Sheet1!$G:$G,AM$19+ROW(A6)-1),""))
日付のセルはB列でしたので修正しており。sheet1のメニューが入っている列がGなので修正いたしました。
原因が分からない状況です。
よろしくお願いいたします。
（TOMTOM） 2018/06/19(火) 10:13
あとは、ExcelのCSV処理と違って自前で項目分割しているので、CSVの文字列がダブルクォートで括っているとか、データ内にカンマが使われているとかありませんか？ 実際のものに近いテータ例を教えてもらわないと、お望みの結果にならないです。
（???） 2018/06/19(火) 11:46
Dim sht As Worksheet, fd As Date, sd As Date, i As Long, w As Long, gyou As Long, c As Range, r As Range
Set sht = Sheets("Sheet1")
fd = sht.Range("C1").Value
If Not IsDate(fd) Then MsgBox "Ｃ１セルから日付にしてください", vbCritical: Exit Sub
sd = DateSerial(Year(fd), Month(fd), 1)
w = 1
Set r = Sheets(w & "週目").Cells(6, Choose(WorksheetFunction.Weekday(sd), 9, 3, 4, 5, 6, 7, 8))
Do
If Month(sd) <> Month(sd + i) Then Exit Do
r.Value = sd + i
For Each c In sht.Range("C:C").SpecialCells(2)
If c.Value = r.Value Then
gyou = WorksheetFunction.CountIfs(sht.Range("C1").Resize(c.Row), c.Value, sht.Range("D1").Resize(c.Row), c.Offset(, 1).Value) + _
IIf(c.Offset(, 1).Value = "朝食", 6, IIf(c.Offset(, 1).Value = "昼食", 18, 28))
Sheets(w & "週目").Cells(gyou, r.Column).Value = c.Offset(, 2).Value
End If
Next c
If r.Offset(-1).Value = "日" Then
w = w + 1
Set r = Sheets(w & "週目").Cells(6, 3)
Else
Set r = r.Offset(, 1)
End If
i = i + 1
Loop
End Sub
（mm） 2018/06/19(火) 15:08
すいません。
>=IF(AM$19=0,"",IF(INDEX(Sheet1!$C:$C,AM$19+ROW(A6)-1)=B$6,INDEX(Sheet1!$G:$G,AM$19+ROW(A6)-1),"")) 最後C列の式間違ってました。 最後の式も朝昼夕の区別が必要なのに区別してませんでした。
正しくは以下です。
C7 =IF(AM$7=0,"",IF(AND(INDEX(Sheet1!$C:$C,AM$7+ROW(A1)-1)=B$6,INDEX(Sheet1!$D:$D,AM$7+ROW(A1)-1)="朝食"),INDEX(Sheet1!$G:$G,AM$7+ROW(A1)-1),"")) C19 =IF(AM$19=0,"",IF(AND(INDEX(Sheet1!$C:$C,AM$19+ROW(A1)-1)=B$6,INDEX(Sheet1!$D:$D,AM$19+ROW(A1)-1)="昼食"),INDEX(Sheet1!$G:$G,AM$19+ROW(A1)-1),"")) C29 =IF(AM$29=0,"",IF(AND(INDEX(Sheet1!$C:$C,AM$29+ROW(A1)-1)=B$6,INDEX(Sheet1!$D:$D,AM$29+ROW(A1)-1)="夕食"),INDEX(Sheet1!$G:$G,AM$29+ROW(A1)-1),""))
EXCEL2016みたいなので、TEXTJOIN関数が使えるなら以下のように少し短く出来ます。
C7 =IF(AM$7=0,"",IF(TEXTJOIN("",0,INDEX(Sheet1!$C:$D,AM$7+ROW(A1)-1,0))=B$6&"朝食",INDEX(Sheet1!$G:$G,AM$7+ROW(A1)-1),""))
C19 =IF(AM$19=0,"",IF(TEXTJOIN("",0,INDEX(Sheet1!$C:$D,AM$19+ROW(A1)-1,0))=B$6&"昼食",INDEX(Sheet1!$G:$G,AM$19+ROW(A1)-1),""))
C29 =IF(AM$29=0,"",IF(TEXTJOIN("",0,INDEX(Sheet1!$C:$D,AM$29+ROW(A1)-1,0))=B$6&"夕食",INDEX(Sheet1!$G:$G,AM$29+ROW(A1)-1),""))
（sy） 2018/06/19(火) 20:47
確認遅くなりもすみませｎ。
色々推測していただきありがとうございます。
希望のものができました。
ありがとうございました。
???様
おしゃる通り、1行目にタイトルがついていました。
ご指示の通りやってみたのですが、やはりうまく動かず、
参照されるデータが存在しない状態です。
カンマなどは入っていないのですが、知識不足でどのようにお伝えしたら
いいものかわかりませんでした。
ご検討いただきありがとうございました。
ｍｍ様
ＶＬＱの列にシート１のＤ列の内容が表示されてしまい、
何か伝え方がいけなかったのかと思います。
ありがとうございました。
（TOMTOM） 2018/06/26(火) 10:15
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.