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

Question

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

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

Comments

[ttyp03]

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

[退会済みユーザー]

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

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

Answer 1

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

あとは、この手の処理は 関数に正規表現が使える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/

Answer 2

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

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以下の数式は拾い物です。
リンク内容

Comments

[ttyp03]

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

[radames1000]

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

Answer 3

セル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

Comments

[退会済みユーザー]

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

[tosi]

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