プロが教えるわが家の防犯対策術！

おしえて

2列のエクセルの表を変更したい

解決済

  • 質問者：ふふふha
  • 質問日時：
  • 回答数：6件

画像の左の表を右の表のように変更したいです。
office365でtranspose関数を使う方法を教えていただき、出来るようになりましたが、
office365の環境がないところでも使いたいので、再度質問します。
できるだけ簡単な方法で、あまり工程も多くない方がいいです。
簡単なVBAレベルまででお願いします。

「2列のエクセルの表を変更したい」の質問画像

質問者からの補足コメント

  • うれしい

    ありがとうございます！出来ました！
    AGGREGATE関数、知りませんでした。。。
    あとで頑張って読み解きます。

    そうなんです。
    D列も自動作成できればもっと有難いです！
    大変図々しいですが、VBAも教えてもらえますか？

    No.1の回答に寄せられた補足コメントです。 補足日時：2022/07/01 14:33
    通報する
通報する

この質問への回答は締め切られました。

質問の本文を隠す

A 回答 (6件)

参考程度に

No.3ベストアンサー

  • 回答者： fujillin
  • 回答日時：

No1です。



＞大変図々しいですが、VBAも教えてもらえますか？
方法はいろいろあると思いますが、以下は一例です。

※ D2以降に記入するだけの処理にしてありますので、事前にクリア等
　が必要なら追加してください。
※ また、担当名は出現順となりますので、並べ替え等が必要な場合は
　追加してください。

Sub Q13022513()
Dim d, v, tmp, t, r As Range
Dim i As Long, j As Long, sep As String

sep = Chr(27)
Set r = Range(Cells(2, 1), Cells(Rows.Count, 2).End(xlUp))
If r(1).Row < 2 Then Exit Sub
v = r.Value
Set d = CreateObject("Scripting.Dictionary")

For i = LBound(v) To UBound(v)
tmp = Split(v(i, 2), ",")
For j = LBound(tmp) To UBound(tmp)
t = Trim(tmp(j))
If t <> "" Then
If d.Exists(t) Then d(t) = d(t) & sep & v(i, 1) Else d.add t, v(i, 1)
End If
Next j
Next i

Set r = Cells(2, 4)
v = d.keys
For i = LBound(v) To UBound(v)
t = Split(v(i) & sep & d(v(i)), sep)
r.Resize(, UBound(t) + 1).Value = t
Set r = r.Offset(1)
Next i
End Sub
    • good
    • 0
    • 件
通報する
この回答へのお礼

ありがとうございます。
出来ました。
頑張って読み解きます。

通報する
お礼日時：2022/07/04 13:15

No.6

失礼しました。


EXCEL2019で検証し、送信時のPCが2013を使用し式をコピペしたので、
_xlfn.TEXTJOINとなっていました。
TEXTJOINが正です。配列数式なのでCtr+Shift+Enterで式確定をしてください。
D2=LEFT(TEXTJOIN("\",1,REPT(IFERROR(MID($B$2:$B$5,FIND("♪",SUBSTITUTE(","&$B$2:$B$5&",",",","♪",TRANSPOSE(ROW($1:$5)))),FIND("♪",SUBSTITUTE(","&$B$2:$B$5&",",",","♪",TRANSPOSE(ROW($1:$5))+1))-FIND("♪",SUBSTITUTE(","&$B$2:$B$5&",",",","♪",TRANSPOSE(ROW($1:$5))))-1),""),COUNTIF($D$1:D1,IFERROR(MID($B$2:$B$5,FIND("♪",SUBSTITUTE(","&$B$2:$B$5&",",",","♪",TRANSPOSE(ROW($1:$5)))),FIND("♪",SUBSTITUTE(","&$B$2:$B$5&",",",","♪",TRANSPOSE(ROW($1:$5))+1))-FIND("♪",SUBSTITUTE(","&$B$2:$B$5&",",",","♪",TRANSPOSE(ROW($1:$5))))-1),""))=0))&"\",FIND("\",TEXTJOIN("\",1,REPT(IFERROR(MID($B$2:$B$5,FIND("♪",SUBSTITUTE(","&$B$2:$B$5&",",",","♪",TRANSPOSE(ROW($1:$5)))),FIND("♪",SUBSTITUTE(","&$B$2:$B$5&",",",","♪",TRANSPOSE(ROW($1:$5))+1))-FIND("♪",SUBSTITUTE(","&$B$2:$B$5&",",",","♪",TRANSPOSE(ROW($1:$5))))-1),""),COUNTIF($D$1:D1,IFERROR(MID($B$2:$B$5,FIND("♪",SUBSTITUTE(","&$B$2:$B$5&",",",","♪",TRANSPOSE(ROW($1:$5)))),FIND("♪",SUBSTITUTE(","&$B$2:$B$5&",",",","♪",TRANSPOSE(ROW($1:$5))+1))-FIND("♪",SUBSTITUTE(","&$B$2:$B$5&",",",","♪",TRANSPOSE(ROW($1:$5))))-1),""))=0))&"\")-1)1),""))=0))&"\")-1)
    • good
    • 0
    • 件
通報する
この回答へのお礼

ありがとうございます、感謝です。

通報する
お礼日時：2022/07/07 18:24

参考程度に

No.5

№４です。


先の回答はABC、あいう等の１文字しか対応していなかったので、実務に合わせた数式を頭を捻って考えました。EXCEL2019以降に対応します。大変長いです。
TRANSPOSE(ROW($1:$5))の数式部分は担当欄がMaxで５名までを意味します。
D2=LEFT(_xlfn.TEXTJOIN("\",1,REPT(IFERROR(MID($B$2:$B$5,FIND("♪",SUBSTITUTE(","&$B$2:$B$5&",",",","♪",TRANSPOSE(ROW($1:$5)))),FIND("♪",SUBSTITUTE(","&$B$2:$B$5&",",",","♪",TRANSPOSE(ROW($1:$5))+1))-FIND("♪",SUBSTITUTE(","&$B$2:$B$5&",",",","♪",TRANSPOSE(ROW($1:$5))))-1),""),COUNTIF($D$1:D1,IFERROR(MID($B$2:$B$5,FIND("♪",SUBSTITUTE(","&$B$2:$B$5&",",",","♪",TRANSPOSE(ROW($1:$5)))),FIND("♪",SUBSTITUTE(","&$B$2:$B$5&",",",","♪",TRANSPOSE(ROW($1:$5))+1))-FIND("♪",SUBSTITUTE(","&$B$2:$B$5&",",",","♪",TRANSPOSE(ROW($1:$5))))-1),""))=0))&"\",FIND("\",_xlfn.TEXTJOIN("\",1,REPT(IFERROR(MID($B$2:$B$5,FIND("♪",SUBSTITUTE(","&$B$2:$B$5&",",",","♪",TRANSPOSE(ROW($1:$5)))),FIND("♪",SUBSTITUTE(","&$B$2:$B$5&",",",","♪",TRANSPOSE(ROW($1:$5))+1))-FIND("♪",SUBSTITUTE(","&$B$2:$B$5&",",",","♪",TRANSPOSE(ROW($1:$5))))-1),""),COUNTIF($D$1:D1,IFERROR(MID($B$2:$B$5,FIND("♪",SUBSTITUTE(","&$B$2:$B$5&",",",","♪",TRANSPOSE(ROW($1:$5)))),FIND("♪",SUBSTITUTE(","&$B$2:$B$5&",",",","♪",TRANSPOSE(ROW($1:$5))+1))-FIND("♪",SUBSTITUTE(","&$B$2:$B$5&",",",","♪",TRANSPOSE(ROW($1:$5))))-1),""))=0))&"\")-1)

配列確定（Ctr+Shift+Enter）し、下へフィルコピー
    • good
    • 0
    • 件
通報する
この回答へのお礼

ありがとうございます。
セルD2が「#NAME?」になってしまいました。

通報する
お礼日時：2022/07/04 13:20

参考程度に

No.4

D列を自動作成する数式になります。

（ローテクで長いです）

D1=CHAR(MIN(INDEX(CODE(MID(
SUBSTITUTE($B$2:$B$5,",",""),
ROW(OFFSET($A$1,,,,LEN(SUBSTITUTE($B$2:$B$5,",","")))),
1)),0)))

D2=IF(ISNA(INDEX(MID(
SUBSTITUTE($B$2:$B$5,",",""),
ROW(OFFSET($A$1,,,,LEN(SUBSTITUTE($B$2:$B$5,",","")))),
1),MATCH(0,COUNTIF(D$2:D2,"="&MID(
SUBSTITUTE($B$2:$B$5,",",""),
ROW(OFFSET($A$1,,,,LEN(SUBSTITUTE($B$2:$B$5,",","")))),
1)),0))),"",INDEX(MID(
SUBSTITUTE($B$2:$B$5,",",""),
ROW(OFFSET($A$1,,,,LEN(SUBSTITUTE($B$2:$B$5,",","")))),
1),MATCH(0,COUNTIF(D$2:D2,"="&MID(
SUBSTITUTE($B$2:$B$5,",",""),
ROW(OFFSET($A$1,,,,LEN(SUBSTITUTE($B$2:$B$5,",","")))),
1)),0)))
配列確定（Ctr+Shift+Enter）し、下へフィルコピー
    • good
    • 0
    • 件
通報する
この回答へのお礼

ありがとうございます。
セルD1は表示できたのですが、セルD2は空白になってしまいます。。。

通報する
お礼日時：2022/07/04 13:16

No.2

  • 回答者： d-q-t-p
  • 回答日時：

PowerQueryでやればいいと思いますけど。



担当列をカンマ指定で列の分割
分割した列を選択して列のピボット解除
値列で行のグループ化
グループ化したテーブルにインデックス列を追加
グループ列から品名・インデックス列を展開
インデックス列を指定して列のピボット（値列「品名」で集計はしない）

インデックス列の追加以外は機能を選択するだけでできます。
インデックス列は行のグループ化の時に「each _」を
「each Table.AddIndexColumn(_, "インデックス", 1, 1)」に
変更すればいいです。
「2列のエクセルの表を変更したい」の回答画像2
    • good
    • 5
    • 件
通報する
この回答へのお礼

「PowerQuery」
すぐに使える環境がなかったので、まだ確認できていませんが、
是非時間を作ってPowerQueryを身に着けたいと思いました。
ありがとうございます。

通報する
お礼日時：2022/07/04 13:12

No.1

  • 回答者： fujillin
  • 回答日時：

こんにちは



ご提示のレイアウトで、E2セルに
=IFERROR(IF($D2="","",INDEX($A:$A,AGGREGATE(15,6,ROW(A$2:A$6)/NOT(ISERROR(FIND($D2,$B$2:$B$5))),COLUMN(A1)))),"")
の式を入力し、右方、下方にフィルコピー。

では、いかがでしょうか？

※ D列も自動作成したいような場合は、関数で行うよりも、VBAの方が簡単だと思います。
この回答への補足あり
    • good
    • 3
    • 件
通報する

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう！

Q質問する（無料）

関連するカテゴリからQ&Aを探す

ページトップページトップ

Q質問する（無料）

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング

おすすめ情報