質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

88.91%

Excel関数で略称と対応するものに並び替えたいです

解決済

回答 3

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 309
退会済みユーザー

退会済みユーザー

イメージ説明

B列の会社略称をもとにF列の請求書(順不同となっているもの)を並び替えC列に入るようにしたいです。
よろしくお願いいたします。

※1 会社略称は2~3文字の英字となっております。(画像の通り「AB」、「ABC」のような略称が付く場合がございます。)
※2 請求書は「(0文字以上の任意文字)会社略称(0文字以上の任意文字)請求書」となっております。

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • 退会済みユーザー

    2020/07/21 09:18

    複数のユーザーから「やってほしいことだけを記載した丸投げの質問」という意見がありました
    「質問を編集する」ボタンから編集を行い、調査したこと・試したことを記入していただくと、回答が得られやすくなります。

  • ttyp03

    2020/07/21 09:28

    ワークシート関数では制約が多くtosiさんの回答にあるような問題点の解消が難しい可能性があります。マクロの使用も視野にいれた方がいいと思いますが、その点についてはどうでしょうか?

    キャンセル

  • 退会済みユーザー

    退会済みユーザー

    2020/07/21 20:38

    誠に申し訳ございません
    自身の実行例の掲載を失念しておりました。

    修正の件、ご指摘ありがとうございます。
    いただいた回答で実現したい内容はできましたので、そちらをベストアンサーとさせていただきます。

    キャンセル

回答 3

+2

そもそもの会社略称のつけ方と、並びが統一されていない請求書名のつけ方に問題アリと思います。
請求書名を (略称)_(文字列)請求書 みたいな形で統一ルール化すべきです。

あとは、この手の処理は 関数に正規表現が使えるGoogleスプレッドシートの方がやりやすいんですが、どーしてもExcel関数でやるなら

・作業列が必要
・会社略称以外に英数字(半角文字)を含まない & 100文字以内

という前提を用意した上で、Ctrl+shift+Enterでおなじみの配列数式を使えば対応できるかなと。

  1. 横並びにA~Zを入力したセルを用意 (仮に K1:AI1 とする)
  2. E列に請求書名から英字部分を抜き出す為に、E3に以下の式を入れる({}の部分はCtrl+shift+Enterすると自動で付きます)E3を下にオートフィル
  3. あとはC3にVLOOKUP入れて下にオートフィル
//こちらは ABAのようなケースで不具合がでるので下に変更
{=MID(F3,MIN(IFERROR(FIND($K$1:$AI$1,F3),1000)),MAX(IFERROR(FIND($K$1:$AI$1,F3),0))-MIN(IFERROR(FIND($K$1:$AI$1,F3),1000))+1)}


↓修正版 E3の式

{=LEFT(MID(F3,MIN(IFERROR(FIND($K$1:$AI$1,F3),1000)),100),LENB(JIS(MID(F3,MIN(IFERROR(FIND($K$1:$AI$1,F3),1000)),100)))-LENB(MID(F3,MIN(IFERROR(FIND($K$1:$AI$1,F3),1000)),100)))}

C3の式

=VLOOKUP(B3,$E$3:$F$7,2,FALSE)

英字が小文字も含むならFINDじゃなくて SEARCH に変えてください。

■参考 Findで複数条件指定を行う方法
https://excelkamiwaza.com/find_hukusuu.html

【追記・修正】
失礼しました。最初の回答の方法だと ABAや BACBみたいな 同じアルファベットが繰り返し登場する際に不具合が出るので、修正しました。

■参考 英字(数字)と日本語を分ける
https://excel-forest.net/2016/07/12/eisuujiwake/

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

checkベストアンサー

+1

下記条件で対応しています。
・作業セルの使用
・略称以外の英字不使用

E3以下

=MID(F3,SUMPRODUCT(ROW(OFFSET(INDIRECT("Z1"),,,LEN(F3)))*ISNUMBER(FIND(MID(LOWER(ASC(F3)),ROW(OFFSET(INDIRECT("Z1"),,,LEN(F3))),1),"abcdefghijklmnopqrstuvwxyz"))*
ISERROR(FIND(MID(" "&LOWER(ASC(F3)),ROW(OFFSET(INDIRECT("Z1"),,,LEN(F3))),1),"abcdefghijklmnopqrstuvwxyz"))),SUMPRODUCT(ISNUMBER(FIND(MID(LOWER(ASC(F3)),ROW(OFFSET(INDIRECT("Z1"),,,LEN(F3))),1),"abcdefghijklmnopqrstuvwxyz"))*1))


C3以下

=IF(SUMPRODUCT(EXACT(B3,E:E)*ROW(E:E))=0,"",INDEX(F:F,SUMPRODUCT(EXACT(B3,E:E)*ROW(E:E))))


大文字小文字は分けて判断されます。
E3以下の数式は拾い物です。
リンク内容

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2020/07/21 10:40

    "ABC"限定処理はちょっとあれなので低評価です。

    キャンセル

  • 2020/07/21 10:54 編集

    確かにちょっとあれですよね。失礼しました。

    キャンセル

+1

セルC3へこちらの関数式をコピペして、後のセルへもこのC3セルを複写して見て下さい。
=VLOOKUP("*"&B4&"*",$F$3:$F$7,1,FALSE)
(追記)
ユーザー関数も作成して見ました。
標準モジュールへ張り付けて、C3へ関数「=GetSeikyuName(B3,$B$3:$B$7,$F$3:$F$7)」を張り付け。
後はセル複写で行くかと思います。

'(Test_Sample_Miniature)
Option Explicit
Function GetSeikyuName(ByRef Target As Range, ByRef Data As Range, ByRef Master As Range) As String
    Application.Volatile
    Dim strTarget As String
    Dim strCoi3 As String
    Dim varDataArray As Variant
    Dim varMastArray As Variant
    Dim lX As Long
    '
    GetSeikyuName = ""
    strTarget = Target.Parent.Cells(Target.Row, Target.Column)
    strCoi3 = ""
    varDataArray = Data.Value
    varMastArray = Master.Value
    '
    strTarget = Target.Value
    If Len(strTarget) <= 2 Then
        For lX = 1 To UBound(varDataArray, 1)
            If InStr(CStr(varDataArray(lX, 1)), strTarget) <> 0 Then
                If Len(CStr(varDataArray(lX, 1))) = 3 Then
                    strCoi3 = CStr(varDataArray(lX, 1))
                    Exit For
                End If
            End If
        Next
    End If
    '
    For lX = 1 To UBound(varMastArray, 1)
        If InStr(CStr(varMastArray(lX, 1)), strTarget) > 0 Then
            If strCoi3 = "" Then
                GetSeikyuName = CStr(varMastArray(lX, 1))
                Exit For
            Else
                If InStr(CStr(varMastArray(lX, 1)), strCoi3) = 0 Then
                    GetSeikyuName = CStr(varMastArray(lX, 1))
                    Exit For
                End If
            End If
        End If
    Next
End Function

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2020/07/21 08:47

    tosiさん、ご回答ありがとうございます!
    いただいた関数を試してみたのですが、
    「えええABCおお請求書」が「いいいAB請求書」より上にある場合、
    C3は「えええABCおお請求書」になってしまうようです。。

    キャンセル

  • 2020/07/21 10:08 編集

    なるほど。
    3文字比較で一致確認→2文字比較で一致確認という手順を盛り込まないとダメですね。
    VBA(マクロ)を使ったユーザー関数では出来そうです。
    また、データベース関数でも可能かもしれません。(データベース関数は得意ではありません)
    ユーザー関数以外の通常関数でやるとしたら私の力量不足の為無理です。

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 88.91%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る