エクセル◎○の人は、一つ昇格した表にしたい。

Question

今月の審査で○◎等を、可否欄にリスト入力します。次月用に◎○の人を二級は一級に、二段は三段にした表をつくりたい。VBAマクロボタンでしたいが、関数のレベルの質問者です。よろしくお願いします。
90種の階級名ですが、シート下タブ30個で毛筆小３とか、かな部とかにそれぞれ表があります。

goomania · Accepted Answer

本件ご質問は、以下のご質問の続きだと理解しています。

https://oshiete.goo.ne.jp/qa/12674594.html
上記のご質問で回答した者です。
既に、No.4さんがVBAでの回答をされておられます。
当方は、前回の回答に続いて、関数を使った方法を回答したいと思います。
ただ、前回のご質問では、「区分」は「区分順位」「区分名」に分かれておらず、A、B、C･･･という規則性のあるものだったのですが、今回のご質問では「区分順位」「区分名」の2つに別れており、「１、９、10、21･･･」「総師範、十段、準十段･･･」という規則性のないものになっています。
このため、No.4さんのVBAと同じく、どこかにこの「区分順位」や「区分名」を一覧表として定義しないといけません。
そこで、添付画像➁のように、「段位区分名称一覧」というシートを用意し、そこに「区分順位」「区分名」の一覧を作成しておきます。
なお、区分順位の最大値は99までとします。また、元の名簿表は最大でも300行程度のデータだと仮定しています。
添付画像①をご覧ください。ご質問者のご提示の表ではG列が「並びIF関数で」となっており、並替え後の段位・氏名が表示されるものと思います。前回のご質問の回答では作業列を３列使用しましたが、今回は4列使用します。
この列の左側に4列確保します。
G列のG2セルに、

=IF(B2="",IF(A3="","",RIGHT("0"&A3,2)),RIGHT("0"&IF((A2<>1)*((D2="◎")+(D2="○")),INDEX(段位区分名称一覧!$A$2:$A$6,MATCH(A2,段位区分名称一覧!$A$2:$A$6,0)-1),A2),2))&IF(D2="","　",D2)&C2

という数式を記述し、下方向へコピーしています。
H列のH2セルに、

=IF(TRIM(G2)<>"",AGGREGATE(15,6,1/(1/VALUE(LEFT($G$2:$G$300,2))),ROW(B1)),"")

という数式を記述し、下方向へコピーしています。
I列のI2セルに、

=IF(H2<>"",COUNTIF($H$2:H2,H2),"")

という数式を記述し、下方向へコピーしています。
J列のJ2セルに、

=IFERROR(INDEX(G:G,AGGREGATE(15,6,ROW($G$2:$G$300)/(VALUE(LEFT($G$2:$G$300,2))=H2),I2)),"")

という数式を記述し、下方向へコピーしています。
そして本来のご質問者のご希望である、「並びIF関数で」はK列に

=IF(MID(J2,4,1)="",MID(J2,1,2),MID(J2,3,20))

という数式を記述し、下方向へコピーしています。
L列から右は、次月分の段位に基づく名簿になります。
L列は次月の「区分順位」になります。L2セルに、

=IF(MID($J2,4,1)="","",VALUE(MID($J2,1,2)))

という数式を記述し、下方向へコピーしています。
M列は次月の「区分名」になります。M2セルに、

=IF(L2="","",VLOOKUP(L2,段位区分名称一覧!$A$2:$B$6,2,FALSE))

という数式を記述し、下方向へコピーしています。
N列は並び替え後の名前です。N2セルに、

=IF(MID($J2,4,1)="","",MID($J2,4,20))

という数式を記述し、下方向へコピーしています。
O列は次月の「可否」記号の入力セルですのでとりあえず空欄です。
P列、Q列はいわゆる「付属情報」ですが、これを表示するための考え方は２通りあります。
(1)次月の段位と名前から、前月の名簿の段位名前を検索して、一致したものの付属情報を表示する
(2)段位の並び替えで前月名簿が次月名簿のどの位置に移動したかを見て、移動後位置へ付属情報を表示する
ここでは、(2)の方法で表示することにします。P２セルに

=IFERROR(IF(INDEX(E:E,AGGREGATE(15,6,ROW($G$2:$G$300)/(VALUE(LEFT($G$2:$G$300,2))=$H2),$I2))="","",IFERROR(INDEX(E:E,AGGREGATE(15,6,ROW($G$2:$G$300)/(VALUE(LEFT($G$2:$G$300,2))=$H2),$I2)),"")),"")

という数式を記述し、Q列および下方向へコピーしています。
これで、ご質問者のご掲示の表は完成です。

この方法は作業列を4列使用します。ただ、J列は次月へ繋げる基本情報なので、省略困難ですが、だとすると、そもそもご質問者のご希望であるK列の表示は省略できないのでしょうか?

もともと前回のご質問での要望は当初、「次月へ繋げる」というご要望ではなく、「並べ替え後の【順番を明らかにするための表示】が欲しい」だと理解していました。その後途中から「次月へ繋げる」というご要望が明らかになったわけです。これだと次月分の名簿が「順番を明らかにするための表示」そのものなので、K列の「順番を明らかにするための表示」はあまり意味があるとは思えません。

つまり、添付画像①のJ列をご質問者のご掲示の表のG列とみなせば、添付画像①のK列を省略して、G、H、Iの３列が作業列となるというイメーシです。

次月以降は元シートをシートコビーした上で、添付画像①L列から右の部分をコピーして、コピーシートのA列から右の部分へ「値貼り付け」して使用すれば、毎月繰返して使用できると思います。

fujillin · Answer

No4です。

＞次月用のエクセルをつくるのに、不確実な手作業でなくVBAでできるのか。
内容がわからないので、はっきりとは言えませんが、仕組みや操作に関する方法が明確にできるのであれば、大抵のことは可能であろうと推測されます。

とは言え、文面からすると質問者様は「作業をして欲しい」と思っているように見受けられますが、世の中にはVBAを作れる人はいくらでもいます。

作成を依頼なさりたいのなら、そのようなところで探せば見つかるものと思いますよ。
例えば、
https://crowdworks.jp/public/employees

fujillin · Answer

こんばんは

関数でもできないことはなさそうですが、空白行を入れたりしているようなので、それだけでも大変面倒な処理になりますね。
また、「区分」の種類も順序も全体像がわからない（どこかにリストがあるのかも知れないけれど、何も書かれていてないので情報がない）ということであきらめました。

以下は、各種の不明点は勝手に解釈したVBAでの例です。
あくまでも例なので、雰囲気のみです。
多分、いろいろと違っているでしょうけれど、骨格は使えるのではないかと思いますので、細部は調整してみてください。

添付図は、実行前のデータがA：F列、実行後のデータがJ:N列です。
使用したコードは以下です。ご参考までに。

Sub Q12680391()
Dim order, data, destR As Range
Dim maxRow As Long, rw As Long, i As Long
Dim d As String, f As Boolean

order = Split("総師範,十段,準十段,一級上,一級", ",")

maxRow = Cells(Rows.Count, 2).End(xlUp).Row
data = Cells(1, 2).Resize(maxRow).Value

For rw = 3 To maxRow
If Cells(rw, 4).Value = "◎" Or Cells(rw, 4).Value = "〇" Then
For i = 0 To UBound(order)
If order(i) = data(rw, 1) Then Exit For
Next i
data(rw, 1) = order(Application.Max(i - 1, 0))
End If
Next rw

Set destR = Cells(3, 10).Resize(, 5)
destR.Resize(maxRow).ClearContents

For i = 0 To UBound(order)
f = False
d = order(i)
For rw = 3 To maxRow
If data(rw, 1) = d Then
destR.Value = Cells(rw, 2).Resize(, 5).Value
destR(1).Value = d
f = True
Set destR = destR.Offset(1)
End If
Next rw