[[20210222150754]] 『文字列の前後と上下に文字を入れたい。』(武蔵野) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『文字列の前後と上下に文字を入れたい。』(武蔵野)

入力シートのセルの前後に任意で文字を入れる作業が大量にあります。
入力シートのA列二行目から前後に入れるパターン名が入っていて
パターンシートには、A列にパターン名、B列に先頭文字、C列には末尾文字が入力されています。

今まで入力シートのデータの1行がパターンシートの1行だったのでvlookupの関数で
入力シートのB列に関数で表示させていました。

しかし、最近上下にデータを入れる依頼が出てきて困っています。

パターンシートはこの様な感じで、特1だと四行作らないといけません。
A列   |B列   |C列
株    |株式会社 |御中
特1     |〇〇   |
特1     |株式会社 |御中
特1     |△△     |
特1     |□□      |
有    |有限会社 |御中

関数や何かの機能を使って表示させることは可能でしょうか?
マクロは人が作ったものを実行するくらいしかした事ないです。

アドバイスいただけると助かります。
よろしくお願いします。

< 使用 Excel:Excel2010、使用 OS:Windows10 >


 >vlookupの関数で入力シートのB列に関数で表示させていました。

 B列の方は分かりましたが、C列はどの様に取り扱っているんですか?

 ※株式会社「どこそこ」御中 となるべきだとすると、
 「どこそこ」の元はどこにあるのか気になりますね。

  ・・と言うか、今のやり方を全部説明いただけないですか?

  今でも、Vlookupを一つのセルに埋めて終わりになるとも思えないのですが・・

(半平太) 2021/02/22(月) 16:08


(半平太)さん
お返事ありがとうございます。
説明が下手ですいません。
入力シートの説明が抜けていたので書きます。
A列   |B列   |C列
パターン |名前   |
株    |山田   |
特1   |田中   |
有    |木下   |

こんな感じでファイルが来ますので、まずB列をC列に移動させています。
A列   |B列   |C列
パターン |名前   |名前
株    |     |山田
特1   |     |田中
有    |     |木下

それから、B列に関数で入力して一番下までコピーしています。
=VLOOKUP(A2,パターン!$A$1:$C$197,2,FALSE)&C1&VLOOKUP(A2,パターン!$A$1:$C$197,3,FALSE)

今まではそれでできたのですが、複数行になってしまってうまくできなくなりました。
関数だとこのようになりますが
A列   |B列       |C列
パターン |名前       |名前
株    |株式会社山田御中 |山田
特1   |○○田中     |田中
有    |有限会社木下御中 |木下

できたら、このようになってほしいのです。

A列   |B列       |C列
パターン |名前       |名前
株    |株式会社山田御中 |山田
特1   |○○       |
特1   |株式会社田中御中 |田中
特1   |△△       |
特1   |□□       |
有    |有限会社木下御中 |木下

出来上がったものは、B列を値貼り付けしてC列を消したファイルを提出しています。
分かりにくい説明で申し訳ありません。
伝わりましたでしょうか?

(武蔵野) 2021/02/23(火) 21:17


 >こんな感じでファイルが来ます

 「来る」と言うのがピンとこないのですが、

 1.そのファイルは、エクセルブックですか?
 2.パターンシートも一緒に付いてくるのですか?

 >まずB列をC列に移動させています。

 3.すると、B列より右にあるデータ(もしあれば)も一緒に右に移動することになりますが、
   実際、B列より右にデータがあるのでしょうか?

 4.いずれにしても、行を適宜挿入しないとならないので、
   マクロで処理しようと思いますが、構いませんね?

 5.「特1」の「1」は半角ですか、全角ですか、混在ですか?
  入力シートとパターンシートの両方について知りたいです。

(半平太) 2021/02/24(水) 09:48


何度もありがとうございます。
1)ファイルは、エクセルファイルで他部署からメールで送られてきます。
2)入力シートとパターンシートの他にバックアップ用のシートが数シート入っていますが
 使うことがありません。シート名は「バックアップ20201201」と言った感じで
 2-10シートほど入っています。
3)入力シートはA列とB列しかデータは入っていません。
 提出時にC列が残っているとダメなので消しています。
4)マクロで可能でしょうか?
 可能な術があるにであればお願いしたいです。
5)ほとんど全角でしたが数カ所半角のカナが入っていました。
 パターンシートのデータを入力シートのA列に表示されて選択するリストボックス
 が設定されているので、入力シートのA列は必ずパターンシートのA列から選ばれています。
よろしくお願いいたします。
(武蔵野) 2021/02/24(水) 14:15

 >5)ほとんど全角でしたが数カ所半角のカナが入っていました。

 1.「特1」についてお聞きしたのですが、カナとは何ですか?

 2.「特1」以外に、今までと同じやり方だとマズいケースがあるんですか?

 ※特1と言うのが仮称の話なら、実際データの話にしてください。
  さもないと、無駄な遣り取りが増えます。

(半平太) 2021/02/24(水) 14:43


何度もすいません。
特1の1は全角です。

有、株、特1の他に特2、から特11までと特A(Aも全角)からFまであります。
それ以外に数、林、民アメ、民ド、カンと言ったカナが半角で入っています。
パターンシートは197行まで入っています。

全て書き出した方が宜しいでしょうか?
質問いただいた内容に正確に答えられず申し訳ありません。
(武蔵野) 2021/02/24(水) 15:04


 >全て書き出した方が宜しいでしょうか?

 そんな必要はないですが、
 今までと同じやり方ではマズいのは「特1」だけと理解していたのですが、
 他にもあるなら、もっと一般的なルールで説明してください。

 例えば、
  特1は、パターンの特1の数をカウントして、その数(4)と同じ行数の構成にする。
  今までと同じ処理をするのは2行目で、それ以外はパターンシートからそのまま転記する

  特2は、パターンの特2の数をカウントして、その数(x)と同じ行数の構成にする。
  今までと同じ処理をするのはy行目で、それ以外はパターンシートからそのまま転記する。

 この場合、xは自動的に判定できますが、yはどう決める事になるのか?
 いつも2行目なら問題はないですが、場合によりけりとなると、
 どこかに特定する手掛かりがないと処理不能ですよ。

(半平太) 2021/02/24(水) 15:27


 > いつも2行目なら問題はないですが、場合によりけりとなると、
 > どこかに特定する手掛かりがないと処理不能ですよ。

 パターンシートのC列にデータ(御中とかの)が入っている行と考えればいいんですかね?

(半平太) 2021/02/24(水) 15:32


お返事ありがとうございます。
パターンシートを確認したところ、(x)の行数や何行目(y)はバラバラでした。
ただ、(y)の入る行だけC列にデータが入っています。
一行のみのパターンデータと複数行の(y)データ以外はB列までしか
データは入っていません。
また、D列より右側にも何もデータが入っていません。
これだけではルールとならないでしょうか?
(武蔵野) 2021/02/24(水) 15:48

 1.開いているブックの数は、以下の2つしかないものとします。
  (1)マクロが書かれているブック
  (2) 処理対象ブック

 2.処理対象のブックには以下のシートが必ず入っているものとします。
  (1)「入力」シート
  (2)「パターン」シート

 3.マクロ用のブックの標準モジュールに後記マクロをコピペする

 4.マクロ名「getCompanyStatus」を実行する

 結果がD列以降に出力されますので、よければ、A:C列を削除してください。

 ’標準モジュールに貼り付けるマクロ

 Sub getCompanyStatus()
     Dim wb As Workbook
     Dim dicT As Object
     Dim ptCLs As Range
     Dim wsDATA As Worksheet
     Dim v, POS, i, k, numOfItems, DATA

     If Workbooks.Count <> 2 Then
         MsgBox "処理対象ブックとこのマクロブックのみ開いてください"
         Exit Sub
     End If

     For Each wb In Workbooks
         If wb.Name <> ThisWorkbook.Name Then
             Set ptCLs = wb.Worksheets("パターン").Columns("A:C")
             Set wsDATA = wb.Worksheets("入力")
             Exit For
         End If
     Next

     Set dicT = CreateObject("Scripting.Dictionary")

     dicT(0) = Array(wsDATA.Range("A1").Value, wsDATA.Range("B1").Value)

     v = wsDATA.Range("A1").CurrentRegion.Offset(1).Value

     For i = 1 To UBound(v)
         If IsEmpty(v(i, 1)) Then
             dicT(i * 10 + 0) = Array(v(i, 1), v(i, 2))

         Else
             POS = Application.Match(v(i, 1), ptCLs.Columns(1), 0)

             If Not IsError(POS) Then

                 '複数行あるかチェック
                 numOfItems = Application.CountIf(ptCLs.Columns(1), v(i, 1))

                 Select Case numOfItems

                     Case Is > 1
                         For k = POS To POS + numOfItems - 1
                             If ptCLs.Cells(k, 3) <> "" Then
                                 dicT(i * 10 + k - POS) = Array(v(i, 1), ptCLs.Cells(k, 2) & v(i, 2) & ptCLs.Cells(k, 3))
                             Else
                                 dicT(i * 10 + k - POS) = Array(v(i, 1), ptCLs.Cells(k, 2).Value)
                             End If
                         Next k
                     Case Else
                         dicT(i * 10) = Array(v(i, 1), ptCLs.Cells(POS, 2) & v(i, 2) & ptCLs.Cells(POS, 3))
                 End Select
             Else

             End If
         End If
     Next i

     i = 0
     For Each DATA In dicT.items
         i = i + 1
         wsDATA.Cells(i, "D").Resize(, 2).Value = DATA
     Next
 End Sub

(半平太) 2021/02/24(水) 16:46


コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.