[Excel] 関数のみでデータの整形をしたい

画像のように、整形前データを関数によって処理し、整形後データのようにしたいです。

＜処理内容＞
特定の文字を含んだ文字列を除外した上で、左から最大２つ分のデータを取り出し、さらに「○日」の部分は削除した状態で表示させたいです。
ただし、Excel2016で、マクロ使用不可の環境です。Excel2021などで追加された関数は使えません。

＜除外する対象＞
・「出席」「学級」「忌引」のいずれかを含む文字列

＜整形前データの構造＞
項目ごとに「～○日,～○日,～○日」となり、項目間は「,」（半角コンマ）で区切られる。項目数は可変。
～：文字列
○：半角数字1～3桁

＜自分自身でつくった部分＞
データ1のみですが、次の関数で何とか再現はできましたが、データ2～4の再現をどのようにしたらよいか・・・
=IF(OR(LEFT(B2,2)="出席",LEFT(B2,2)="学級",LEFT(B2,2)="忌引"),IFERROR(MID(B2,FIND(",",B2)+1,2),"なし"))

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

• おっしゃる通りです。
  B列のようなデータパターンをC列のような結果になるように処理したいです。

  No.1の回答に寄せられた補足コメントです。 補足日時：2022/01/26 21:08

• 日数やカンマの数、項目の組み合わせは、個々のデータによってまちまちです。
  そのため、VLOOKUP関数を組むには途方もないリストを用意する必要があります・・・
  
  なので、個々のデータの中でも共通している部分を説明の中に挙げました。

  No.2の回答に寄せられた補足コメントです。 補足日時：2022/01/26 21:21

• 

  後付けの説明で申し訳ないですが・・・
  
  B列のデータは、およそ1000件あります。
  ただ、クラウドシステムからExcel専用帳票で出力されるもの（クラウド上のデータが値貼付されて出てくる。1ブック40シート(1シート2件)の残念仕様）で、整理するにも・・・
  
  ということで、専用帳票に関数を入れ込んで処理させてしまおうと考えたのです。

  No.4の回答に寄せられた補足コメントです。 補足日時：2022/01/26 21:56

No.12 回答者： mygoonickname 回答日時： 2022/01/27 04:21

No.の者6です。

直接の回答ではありませんが、
管理者がいれば、何か良い方法がないか相談してみては？と思いました。
若しくは、アドインや、マクロを特例として使用可にして貰うとか？
作ったアドインや、マクロファイルを、ウィルスチェックなどで検査して
貰い、問題ない子をそのうえで、使用するとか。

No.11 回答者： 銀鱗 回答日時： 2022/01/27 00:56

(´・ω・`)

別シートにデータを分けた票を作るのが面倒ということですが、
ハッキリ言って予め処理するシートを作っておけばその方が早く処理できますよ。
CSV出力でないなら尚のことです。

ひな形を作っておけば、それを使いまわすだけで済みます。
一番初めの手間を惜しんでいては横着はできません。
それとも、ここで
　「代わりに作れ。オレ様の成果として使ってやるｗｗｗ」　
のような作業依頼をしたいという事でしょうか。

No.10 回答者： ぐーるぐる 回答日時： 2022/01/26 22:53

もう、1sheetに1000レコードまとめたのなら、力業でやる。

データ区切りで切り分けて、ピボット状態にする。

この回答へのお礼

力業で整形しました。もっといいやり方があるとよいですがね（苦笑）

セル①E2セル：「○日」の消去
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,0,""),1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,""),"日","")

セル②E2セル：「除外する対象」を含む項目の消去
=IFERROR(IF(OR(LEFT(E2,2)="出席",LEFT(E2,2)="学級",LEFT(E2,2)="忌引"),IF(OR(MID(E2,FIND(",",E2)+1,2)="出席",MID(E2,FIND(",",E2)+1,2)="学級",MID(E2,FIND(",",E2)+1,2)="忌引"),IF(OR(MID(E2,FIND(",",E2,FIND(",",E2)+1)+1,2)="出席",MID(E2,FIND(",",E2,FIND(",",E2)+1)+1,2)="学級",MID(E2,FIND(",",E2,FIND(",",E2)+1)+1,2)="忌引",),IF(OR(MID(E2,FIND(",",E2,FIND(",",E2,FIND(",",E2)+1)+1)+1,2)="出席",MID(E2,FIND(",",E2,FIND(",",E2,FIND(",",E2)+1)+1)+1,2)="学級",MID(E2,FIND(",",E2,FIND(",",E2,FIND(",",E2)+1)+1)+1,2)="忌引"),"",MID(E2,FIND(",",E2,FIND(",",E2,FIND(",",E2)+1)+1)+1,999)),MID(E2,FIND(",",E2,FIND(",",E2)+1)+1,999)),MID(E2,FIND(",",E2)+1,999)),E2),"")

セル③C2セル：最終整形（左から最大２つ）
=IF(C2="","なし",IFERROR(MID(C2,1,FIND(",",C2,FIND(",",C2)+1)-1),C2))

お礼日時：2022/01/27 00:49

No.9 回答者： ぐーるぐる 回答日時： 2022/01/26 22:52

まず、現状、一般ユーザーではなく管理者アカウントならいじれるとは思うので、管理者にCSVでエクスポートさせる方法がないか？確認する。

管理者自体が、何もできない名前だけの管理者なら、システム自体は外注でシステム導入サポートした会社があるはずなので、担当に連絡して、CSVでエクスポートさせる方法がないか？確認する。

結果的に今後もルーチンで同じ処理が必要なら、
カラム毎に使いやすいデータを出力できるように設定を追加してくれと
オーダーした方がいいかもね。

No.8 回答者： ぐーるぐる 回答日時： 2022/01/26 22:30

これって、そもそも別々に記録されたものを

No.2の（人なんでしょう？）に対して、状態、理由、日数という風な選択があって、2回のお休みがあったということをわざわざ1セルに入れて出力してるのよね？
ということは、元データを出力して、いじった方が、早いと思うけどな。

この回答へのお礼

>>No.7
全くその通りで、csv出力未対応です。

>>No.8
システム内で別々に記録されたものが統合されて出力されます。
システムは外注なので、こちらでどうしようもありません。出力されたデータを力技で加工するしかありませんかね・・・

お礼日時：2022/01/26 22:44

No.7 回答者： ぐーるぐる 回答日時： 2022/01/26 22:23

ん？1sheet２レコードで40sheet×2レコード＝80レコード/１Bookで

1000レコードということは、12.5Bookということですか？
むだ～ｗｗ なるほどです。
csvで出てこないのかな？

ちなみに、もう、1sheetに1000レコードまとめたのですか？

No.6 回答者： mygoonickname 回答日時： 2022/01/26 22:15

こんばんは。

直接の回答ではありませんが、
VBAはダメとの事ですが、アドインもダメなのでしょうか？
VBAで作成が必要ですが、ユーザー関数を作って、アドインファイルとして
保存したものを、使うPCに取り込んで、ユーザー関数で処理するとか？

https://ramq-cat.com/make-userdefinition/

きっと、関数で作ろうとすると、一覧表が必要だったり、整形後の文字の
種類ってどれ位あるかにもよりますが、中々難しいと思ったので。

この回答へのお礼

回答ありがとうございます。
アドインもダメですね。使えたら便利なんですが・・・

お礼日時：2022/01/26 22:34

No.5 回答者： 銀鱗 回答日時： 2022/01/26 22:12

この手の問題は一つのセルに複数のデータを入れていることで起きているのです。

まずは一つのセルにデータが一つになるように加工することを強く薦めます。

別のシートに元のシートを参照した表を作り直せば良いでしょう。
その作り直したシートのデータに対して処理をすれば見た目もスッキリします。
別シートにデータを並べ直したら、そこから先は質問者さん自身で解決できるような雰囲気なんですけど、いかがでしょう。

この回答へのお礼

回答ありがとうございます。
最初は別シートor別ブックを考えたんですが、元シート参照の表を作るだけでも手間がかかりすぎるので・・・

お礼日時：2022/01/26 22:36

No.4 回答者： ぐーるぐる 回答日時： 2022/01/26 21:25

だとしたら、既にこの形の情報があるのか？

それとも、今から作っていくのかどちらでしょうか？
今、既にこのようなデータがあるのであれば、レコード数にもよりますが、一旦、カンマ区切りでデータを切り分けて整理するか、
今から作るのであれば、票の作り方を変えた方がいいですね。
B列のなにもかもごっちゃなデータよりは、上と左を使って、
情報を整理されることをお勧めします。

No.3 回答者： ぐーるぐる 回答日時： 2022/01/26 21:12

B＝Cなら、これをリストにして、

Vlookupで結果を返せばいいんじゃないの？