エクセル2013で表の組み替え
エクセル2013で表の組み替えのしかたを教えてください。
勤務表のイメージです。
下の例で、A2からA7までは担当者名(実際はもっと多く)、B1からF1までは日付(架空の例として5日まで)、B2からF7までに担当箇所(実際はもっと多く)がはいります。
1つの担当箇所に2人以上がはいることもあり得ます(見習、応援など)。
この上の表を、下の表のようにかんたんに組み替えたいのです。
下の表では、A2からA11までは担当箇所(2人以上はいることもあるので同一担当箇所の行が2行以上にふえることもあり得ます)、B1からF1までは日付、B2からF11まで担当者名です。
このような組み替えは、関数でやるのでしょうか、それともVBAでしょうか?
それとも(使った事はなくて勉強しなければなりませんが)データベースソフトのアクセスで処理すべき事でしょうか?
手作業でコピーするのが大変なのでどうか教えてくださいませ。
No.1ベストアンサー
- 回答日時:
>このような組み替えは、関数でやるのでしょうか、それともVBAでしょうか?
関数でできますがかなり面倒な論理を考えないと上手くいきません。
上の表をSheet1として、下の表をSheet2とした時に次のような数式で処理できます。
Sheet2!B2=IFERROR(INDEX(Sheet1!$A$2:$A$7,SUMPRODUCT(SMALL((Sheet1!B$2:B$7<>INDEX($A1:$A2,SUMPRODUCT(($A1:$A2<>"")*ROW(B$1:B$2))))*1000+ROW(B$1:B$6),MOD(ROW(),2)+1),1)),"")
オートフィルで右と下へコピーします。
貼付画像は提示されたデータのみを対象にしています。
元データの大きさに合わせて表を作り直す必要があります。
>1つの担当箇所に2人以上がはいることもあり得ます(見習、応援など)。
状況に応じてSheet2の配列を変更し、数式も変更することが必要かと思います。
ありがとうございました。たしかに動作確認し、おもわず顔がほころんでしまいました。シート2のA列の文字列でいろいろ変更できるのですね。質問して良かったです。使わせていただきます。
なやましい選択でしたが、最初に答えをいただいたbunjiiさまをベストアンサーとさせていただきます。
No.3
- 回答日時:
今回の課題はちょっと、簡単に、とはいかないので、VBA が相場かもしれません。
ベストアンサーは辞退します。ただ今後のことを考えるなら、できれば、オートフィルタやピボットテーブルで扱いやすい構造の元データを作っておくことが望まれます。添付図のような表です。
そのような元データを作っておくと、一発では質問文のような表を作ることはできないかもしれませんが、オートフィルタやピボットテーブルと、比較的簡単な数式の組み合わせにより、作ることは可能です。
以下は、添付図のデータからピボットテーブルで抽出した例です。
合計 / ID 日付
場所 2014/1/1 2014/1/2 2014/1/3 2014/1/4 2014/1/5 総計
西館 17 27 24 5 22 95
東館 27 24 9 27 2 89
南館 2 9 17 31 17 76
北館 5 17 5 2 36 65
本館 31 5 27 17 5 85
総計 82 82 82 82 82 410
No.2
- 回答日時:
こんばんは!
VBAでの一例です。
Sheet1のデータをSheet2に表示するようにしてみました。
尚、Sheet3を作業用のSheetとして使用していますので、
Sheet3は使っていない状態にしておいてください。
画面通り 東~本館の5種類としています。
Alt+F11キー → メニュー → 挿入 → 標準モジュール → VBE画面のカーソルが点滅しているところに
↓のコードをコピー&ペースト → Excel画面に戻りマクロを実行してみてください。
(Alt+F8キー → マクロ → マクロ実行です)
Sub Sample1() 'この行から
Dim i As Long, j As Long, k As Long, lastRow As Long
Dim wS2 As Worksheet, wS3 As Worksheet, myArry
Set wS2 = Worksheets("Sheet2")
Set wS3 = Worksheets("Sheet3")
myArry = Array("東館", "西館", "南館", "北館", "本館")
Application.ScreenUpdating = False
wS2.Cells.Clear
With Worksheets("Sheet1")
.Rows(1).Copy wS2.Range("A1")
For k = 0 To UBound(myArry)
wS3.Range("A2") = myArry(k)
For i = 2 To .Cells(Rows.Count, "A").End(xlUp).Row
For j = 2 To .Cells(1, Columns.Count).End(xlToLeft).Column
If .Cells(i, j) = myArry(k) Then
wS3.Cells(Rows.Count, j).End(xlUp).Offset(1) = .Cells(i, "A")
End If
Next j
Next i
lastRow = wS2.UsedRange.Rows.Count
wS3.Range("A2").CurrentRegion.Cut wS2.Cells(lastRow + 1, "A")
Next k
wS2.Columns.AutoFit
wS2.Range("A1").CurrentRegion.Borders.LineStyle = xlContinuous
End With
Application.ScreenUpdating = True
End Sub 'この行まで
こんな感じではどうでしょうか?m(_ _)m
ありがとうございました。たしかに動作確認し、おもわず顔がほころんでしまいました。質問して良かったです。配列の中身など、これからいろいろ試みてみます。いろいろ重宝しそうです。使わせていただきます。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) WORKDAY関数 4 2023/06/08 13:23
- その他(Microsoft Office) 従業員増減対応で当番種類の増減対応な当番表 21 2022/07/19 07:30
- Excel(エクセル) Excel2007での条件付き書式について 6 2023/05/02 10:56
- Excel(エクセル) 製品番号での整列と、検索に関して 3 2023/06/28 19:20
- CPU・メモリ・マザーボード メモリー増強 2 2023/05/04 11:59
- Excel(エクセル) 隣り合っていないセルを まとめて税込表示したい 8 2022/09/25 14:32
- Visual Basic(VBA) Excel VBA 最終行を取得しVlookup関数をコピーする方法をコーディングで教えてください。 3 2023/05/11 13:14
- 運転免許・教習所 教習所の指名について。 私の通っている教習所は、自分で予約を取るのではなく、予定を提出して、みきわめ 4 2022/12/29 23:28
- Excel(エクセル) エクセルでの勤務表作成、インターバルの設定について 3 2022/06/03 15:35
- Excel(エクセル) ExcelのIF関数について 4 2023/05/24 12:54
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルのツールバーから数値...
-
祝日と土曜、日曜の合計をカウ...
-
【マクロ】2回実行したら、エ...
-
特定の文字列を含む、住所を抽...
-
EXCELの散布図で日付が1900年に...
-
マイクロソフトのPADを使ってい...
-
Excel分数の表示について
-
Excelで表を作ったところに文字...
-
マクロエクセルのブロック解除
-
文字2桁、3桁交じりの文字列...
-
絶対参照
-
Excelについての質問です。 B2...
-
在庫管理表に使うエクセルの関...
-
【マクロ】VLOOKUPにて参照元に...
-
【EXCEL】画像の黄色部分の抽出...
-
DATE関数で現在の年齢を出した...
-
ユーザー定義関数をアドイン登...
-
エクセルでの作業計算方法について
-
行数が不規則な一週間ごとの合...
-
Excelピボットテーブルの1行目
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
半角カタカナをヘボン式ローマ...
-
(マクロ)vlookupの元データを同...
-
エクセルで上位バイトのセルと...
-
exselの質問です
-
Excel 大小比較演算子による「...
-
Excel VBについての質問です。
-
エクセルの問題です。絶対値の...
-
非表示列の再表示に失敗
-
職場の人から聞かれており、こ...
-
Excel関数-文字列で自動作成さ...
-
Excelデータをコピペして、ペー...
-
ユーザー定義関数をアドイン登...
-
【マクロ】for next構文について
-
エクセルの日付を編集する
-
【マクロ】VLOOKUPにて参照元に...
-
exselで最小数で並び替える関数
-
libre 表計算ソフトの計算がう...
-
エクセルで表
-
エクセルの表で1年間の曜日を...
-
西暦和暦
おすすめ情報