教えて!gooグレードポイントがdポイントに!

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

「エクセル◎○の人は、一つ昇格した表にした」の質問画像

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

  • 等級だけでなく、添付画像矢印右側のように、昇格した行・名前・ほか1・ごと並び替えた表にしたいです。よろしくお願いします。

    No.1の回答に寄せられた補足コメントです。 補足日時:2021/11/20 14:54
  • どう思う?

    横がズレますけどこんな感じ
    区分順位 区分名 名前 当月可否 ほか ほか2
      9 十段   お ◎ 岡山支部 倉敷市
      9 十段   か ×

      10 準十段 き ○ 愛知支部 岩倉市
      10 準十段 く ×

      21 一級上 こ × 大阪支部 高槻市

      22 一級   さ × 兵庫支部 姫路市
      22 一級   す ○

    これ次月◎○ともに一つUP
    区分順位 区分名 名前 当月可否 ほか ほか2
      1 総師範 お   岡山支部 倉敷市

      9 十段   か 記入
      9 十段   き 空欄   愛知支部 岩倉市

      10 準十段 く

      21 一級上 こ   大阪支部 高槻市
      21 一級上 す

      22 一級   さ   兵庫支部 姫路市

    No.2の回答に寄せられた補足コメントです。 補足日時:2021/11/20 15:40
  • うれしい

    fujillinさん、ありがとうございます。検証勉強しますので、お時間下さい。

    No.4の回答に寄せられた補足コメントです。 補足日時:2021/11/22 11:47
  • HAPPY

    goomania さん、勉強検証にお時間下さい。初めての質問投稿はなるべく簡潔にと思っていましたので、今回の回答には感激しています。ありがとうございます。

    No.5の回答に寄せられた補足コメントです。 補足日時:2021/11/22 11:54
  • 色々と、ありがとうございました。

    No.6の回答に寄せられた補足コメントです。 補足日時:2021/11/26 17:06
教えて!goo グレード

A 回答 (6件)

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


https://oshiete.goo.ne.jp/qa/12674594.html
上記のご質問で回答した者です。
既に、No.4さんがVBAでの回答をされておられます。
当方は、前回の回答に続いて、関数を使った方法を回答したいと思います。
ただ、前回のご質問では、「区分」は「区分順位」「区分名」に分かれておらず、A、B、C・・・という規則性のあるものだったのですが、今回のご質問では「区分順位」「区分名」の2つに別れており、「1、9、10、21・・・」「総師範、十段、準十段・・・」という規則性のないものになっています。
このため、No.4さんのVBAと同じく、どこかにこの「区分順位」や「区分名」を一覧表として定義しないといけません。
そこで、添付画像➁のように、「段位区分名称一覧」というシートを用意し、そこに「区分順位」「区分名」の一覧を作成しておきます。
なお、区分順位の最大値は99までとします。また、元の名簿表は最大でも300行程度のデータだと仮定しています。
添付画像①をご覧ください。ご質問者のご提示の表ではG列が「並びIF関数で」となっており、並替え後の段位・氏名が表示されるものと思います。前回のご質問の回答では作業列を3列使用しましたが、今回は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列はいわゆる「付属情報」ですが、これを表示するための考え方は2通りあります。
(1)次月の段位と名前から、前月の名簿の段位名前を検索して、一致したものの付属情報を表示する
(2)段位の並び替えで前月名簿が次月名簿のどの位置に移動したかを見て、移動後位置へ付属情報を表示する
ここでは、(2)の方法で表示することにします。P2セルに

=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の3列が作業列となるというイメーシです。

次月以降は元シートをシートコビーした上で、添付画像①L列から右の部分をコピーして、コピーシートのA列から右の部分へ「値貼り付け」して使用すれば、毎月繰返して使用できると思います。
「エクセル◎○の人は、一つ昇格した表にした」の回答画像5
この回答への補足あり
    • good
    • 0

No4です。



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

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

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

こんばんは



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

以下は、各種の不明点は勝手に解釈した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

If f Then Set destR = destR.Offset(1)
Next i

Cells(3, 12).Resize(maxRow).ClearContents
End Sub
「エクセル◎○の人は、一つ昇格した表にした」の回答画像4
この回答への補足あり
    • good
    • 0

>◎○ともに一つUP


これについては、質問としての意味は理解できるのですが、他に何が聞きたいの?例だけではなく、日本語の文章で質問を書いてください。
    • good
    • 0

画像が見えないので無理!!

この回答への補足あり
    • good
    • 0

こうかな?

「エクセル◎○の人は、一つ昇格した表にした」の回答画像1
この回答への補足あり
    • good
    • 0

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

このQ&Aを見た人はこんなQ&Aも見ています

教えて!goo グレード

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

人気Q&Aランキング