社会人&学生におすすめする色彩検定の勉強術

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

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

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

  • うれしい

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

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

    No.1の回答に寄せられた補足コメントです。 補足日時:2022/07/01 14:33
教えて!goo グレード

A 回答 (6件)

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

失礼しました。


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

№4です。


先の回答はABC、あいう等の1文字しか対応していなかったので、実務に合わせた数式を頭を捻って考えました。EXCEL2019以降に対応します。大変長いです。
TRANSPOSE(ROW($1:$5))の数式部分は担当欄がMaxで5名までを意味します。
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

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

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



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

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

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

お礼日時:2022/07/04 13:12

こんにちは



ご提示のレイアウトで、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&Aを見た人はこんなQ&Aも見ています

教えて!goo グレード

このQ&Aを見た人がよく見るQ&A

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