何千もの行を持つワークシートがあり、そこには一意の IDAと 3 つの異なるエラー コードが含まれMています。各 ID には、1 つ、2 つ、または 3 つのエラーがある可能性があります。エラーは常に同じ順序になります。
希望する出力を手入力しました。エラーのテキストとともに、N次のような数式を列に書きました。O, P, Q
=IF(IFERROR(SEARCH("Brand is not valid", M42), "") <> "", "Brand", "")
でこのようなことを試してみましたが、明らかな理由で正しく動作しません。 のように見えるようにRする方法はありますか?RN
=CONCATENATE(O42,"/", P42,"/",Q42)
Excel の数式で実行できない場合は、VBA を使用する方法があるでしょうか?
答え1
はい、少し頭を悩ませましたが、わかりました。
列O、P&Qは、Brand、Product&というタイトルが付けられていますOEM。全体をCtrl-を使用してテーブルに変換しますT(必須ではありませんが便利です。私の列はRこれに依存していますが、必要に応じて列参照を使用できます)。
カラムR：
=IF(LEN([Brand])>0,[Brand],IF(LEN([Product])>0,[Product],IF(LEN([OEM])>0,[OEM],"")))
カラムS：
=IF(A2=A4,F2&"/"&F3&"/"&F4,IF(A2=A1,"",IF(A2=A3,F2&"/"&F3,F2)))
残念ながら、別の行へのテーブル参照を使用する唯一の方法は を使用することだと思われるのでOffset、シンプルにするためにセル参照に戻りました。これでは、最初から全体をテーブルに変換するというクールで便利な要素が少し失われてしまいますが、まあ、いいでしょう...
そして...これがその外観の写真です:
答え2
Oまず、、、Pおよび数式を最適化することから始めますQ。現在、
=IF(IFERROR(SEARCH("Brand is not valid", M42), "") <> "", "Brand", "")
IFERRORは、エラーコードになる可能性のある計算値のサニタイズされたバージョンを表示するのに優れた関数です。私はこれを頻繁に使用し、Super Userの回答でも推奨しています。ご存知のとおり、
IFERROR(calculated_value, default_value)
の略称
IF(ISERROR(calculated_value), default_value, calculated_value)
しかし、IFERROR何らかの値のサニタイズされたバージョンを作成するために 使用するとそしてその値をテストして条件付きで何かを実行します
は不必要に不自然な使い方ですIFERROR。上記の式は次のように簡略化できます。
=IF(ISERROR(SEARCH("Brand is not valid", M42)), "", "Brand")
そして、ご存知のとおりSEARCH("Brand is not valid", M42)
、M42 含む Brand is not validただし、列に3つのエラー文字列しか含まれない場合はM、次のように短縮できます。
=IF(ISERROR(SEARCH("Brand", M42)), "", "Brand")
または簡略化して
=IF(M42 = "Brand is not valid", "Brand", "")
さて、ここで、、、OおよびP式Qをもう少し複雑にします。
O42→=IF($A42=$A41, O41, "") & IF(ISERROR(SEARCH("Brand", $M42)), "", "Brand")P42→=IF($A42=$A41, P41, "") & IF(ISERROR(SEARCH("Product", $M42)), "", "Product")Q42→=IF($A42=$A41, Q41, "") & IF(ISERROR(SEARCH("OEM", $M42)), "", "OEM")
の式O42は、
これがこの ID (列 ) の 2 行目または 3 行目である場合
A、このセルの上のセル (つまり、O前の行の列セル) を見て、このアイテムに無効なブランドがあることが既に判明しているかどうかを確認します。また、Mこの行の列 を見て、それが であるかどうかを確認しますBrand is not valid。次に、結果を連結します。
一意の ID が同じエラーで 2 回リストされることは決してないので (そうですよね?)、これら 2 つのサブ結果が両方とも空でない値になることは決してありません。つまり、これは基本的に「OR」を実行していることになります。
Brandこの行、またはこの ID の前の行のいずれかに無効なブランド エラーが含まれている場合は、値を表示します。
Oこれにより、、、PのQ値を各 ID の最後の行までドラッグするのと同じ効果が得られます。
行 41、44、47、および 49 には、列 、 、 のそれぞれの ID に適用されるすべてのエラーの短縮形がそれぞれ表示されていることに注意しOてPくださいQ。
列の定義はRあなたと同じ方法で行いました。空白を除いたセル内容のコンマ区切りリストを生成します。
不要なスラッシュを削除するテクニックについては、こちらをご覧ください。
41、44、47、49行目のみに目的の連結があれば完了です。そうでない場合は、N42次のように定義します。
=IF($A22=$A23, N23, R22)
または
=IF($A22<>$A23, R22, N23)
これは、列、、Oで使用したのとほぼ同じトリックですが、方向が逆です。PQ
これがこの ID の最後の行である場合 (つまり、行 41、44、47、または 49 である場合)、この行の値の連結を使用します (これは、この ID のエラー コードの完全なコレクションです)。それ以外の場合は、このセルの下のセル (つまり、
N次の行の列セル) を確認します。そこに正しい答えがあります。
つまり、目的の値は各 ID の最初の行まで浸透します。