kna********

2022/5/14 8:45

66回答

Excelで１つの系統が複数行にわたって入力されているものを１行にしたい。 標記の件、これまで添付画像の左のようにデータが管理されていました。

Excelで１つの系統が複数行にわたって入力されているものを１行にしたい。 標記の件、これまで添付画像の左のようにデータが管理されていました。 これだと管理がしづらいため、右側のように一つのデータは1行という形にしたいです。 単価の登録個数は商品によってばらばらで、例えばAには単価が３つ登録されていますが、Bには２つ、Cには１つのみしか登録されていません。 5000を超える商品が登録されているため、手作業で行うのは現実的ではありません。 お知恵を貸していただければと思います。よろしくお願いいたします。

画像

Excel | Office系ソフトウェア・225閲覧

ベストアンサー

0
kky********

2022/5/14 9:29

データを整理して1行1データにするなら、数式によう方法でセルに式を残すのは望ましくないと思います（後で値コピーすればいいかもしれませんが） マクロで値だけのデータを作成した方がいいのではないでしょうか？ マクロでもいいのであれば、一つの商品に対して最大何個の単価があるのかを教えて下さい。

ThanksImg質問者からのお礼コメント

ありがとうございました。できました！

お礼日時：5/17 11:22

その他の回答（5件）

0
Chiquilin

2022/5/14 14:49

クエリでやったらどうですか？ 空のクエリを作成して 詳細エディタを開いて下記を貼り付け let ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content], 型の変更1 = Table.TransformColumnTypes(ソース,{{"商品", type text}, {"単価", Int64.Type}, {"個数", Int64.Type}, {"合計", Int64.Type}}), 下方向にフィル = Table.FillDown(型の変更1,{"商品"}), 行のグループ化 = Table.Group(下方向にフィル, {"商品"}, {"グループ", each _}), 連番列の追加 = Table.TransformColumns(行のグループ化, {"グループ", each Table.AddIndexColumn(_,"連番",1,1)}), グループ列の展開 = Table.ExpandTableColumn(連番列の追加, "グループ", {"単価", "個数", "合計", "連番"}), 列のピボット解除 = Table.UnpivotOtherColumns(グループ列の展開, {"商品", "連番"}, "属性", "値"), 型の変更2 = Table.TransformColumnTypes(列のピボット解除,{"連番", type text}), 列のマージ = Table.CombineColumns(型の変更2,{"属性", "連番"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"結合"), 列のピボット = Table.Pivot(列のマージ, List.Distinct(列のマージ[結合]), "結合", "値") in 列のピボット 以上

画像
Chiquilin

2022/5/15 8:24

ただ一応書いておきますが Accessにインポートするなら 基の 表の空いているところを全部データで埋めるだけでいいです。 組み換えした右側の表は データの形として考えると微妙です。

0
taihenda****

カテゴリマスター

2022/5/14 14:20

元になるデータシートが「Sheet1」として Sheet1のA3セルに =IF(C3="","",IF(B3="",A2,B3)) と入れて下にコピーします。 この列は文字色を白にしておくといいでしょう。 別シートです。見出しは作成しておきます。 ※3000行を想定しています B3に =IFERROR(INDEX(Sheet1!B:B,1/LARGE(INDEX((Sheet1!B$3:B$3000<>"")/ROW($3:$3000),),ROW(A1))),"") 下にコピー C3に =IF($B3="","",IFERROR(INDEX(Sheet1!$C:$E,1/LARGE(INDEX((Sheet1!$A$3:$A$3000=$B3)/ROW($3:$3000),),INT(COLUMN(C1)/3)),MOD(COLUMN(C1),3)+1),"")) 右と下にコピー PCがどのぐらいのパフォーマンスか分からないのですが、数式で重かったらマクロを考えます。セルに一つ一つ書き込んでいくマクロなら数式と同じようなものですが配列を使えば相当速度を出せると思います。

画像
0
spe********

2022/5/14 10:42

こんな感じでどうでしょうか？ Sub データ変換() Dim ws1 As Worksheet, ws2 As Worksheet Dim itmAry As Variant Dim myRng As Range Dim strCnt As String Dim lstRow As Long, tblCol As Long, sttCol As Long Dim rowCnt As Long, colCnt As Long, i As Long, j As Long Application.ScreenUpdating = False Set ws1 = Worksheets("Sheet1") Set ws2 = Worksheets("Sheet2") itmAry = Array("商品", "単価", "個数", "合計") With ws1 lstRow = .Cells(.Rows.Count, 3).End(xlUp).Row rowCnt = 2 colCnt = 2 For i = 3 To lstRow sttCol = .Cells(i, 1).End(xlToRight).Column Set myRng = .Range(.Cells(i, sttCol), .Cells(i, 5)) If sttCol = 2 Then rowCnt = rowCnt + 1 colCnt = 2 Else colCnt = ws2.Cells(rowCnt, colCnt).End(xlToRight).Column + 1 End If myRng.Copy ws2.Cells(rowCnt, colCnt).PasteSpecial Paste:=xlPasteValues '/ 値でコピーしてますがお好みで Next i End With With ws2 tblCol = .Cells(3, 3).CurrentRegion.Columns.Count j = 0 For i = 2 To tblCol + 1 If i > 2 Then strCnt = Str(i \ 3) Else strCnt = "" End If .Cells(2, i) = itmAry(j) & strCnt If j = 3 Then j = 0 j = j + 1 Next i .Cells(2, 2).CurrentRegion.Borders.LineStyle = xlContinuous End With Application.ScreenUpdating = True MsgBox "完了" Set myRng = Nothing Set ws1 = Nothing Set ws2 = Nothing End Sub

1
uqy********

2022/5/14 10:21

右の表よりも左の表のB列を埋めて商品ごとに枝番を取るなどの方が管理しやすいと思います。

1人がナイス！しています

0
mit********

2022/5/14 9:34

左の表を一部改良するだけで便利に使える表にすることができるでしょう。 右側の表にすることは止めるべきです。左側の表でも個数が決まって単価が決まることになるのですから個数についてはそれぞれの商品について昇順で並べるようにすることでしょう。お示しのような個数の並びでは関数を使って解析するなどが全くできない状態です。表には無い個数の時の対応も今のままでは出来ませんね。ましてや表を変更した場合にはその利用は全くできない状態です。まずは変更前の表を利用しやすいように変えることでしょう。

kna********

質問者2022/5/14 9:57

ありがとうございます。 このテーブルをそのままアクセスにインポートしたいのですが、アスクセスは基本1行1データでの管理だと理解しており、左のままだとExcelのままでしか管理できず頭を抱えているという状況です。 アクセスへのインポートの際に何か作業をした方が効率的なのでしょうか。