内容
この小さなチュートリアルでは、関数の作成方法を説明しています VPR (VLOOKUP) 大文字と小文字を区別し、Excel が大文字と小文字を区別して検索できる他のいくつかの数式を示し、各関数の長所と短所を指摘します。
Excel ユーザーなら誰でも、縦方向の検索を実行する関数を知っていると思います。 そうです、関数です VPR. ただし、それを知っている人は少ない VPR 大文字と小文字は区別されません。つまり、大文字と小文字は同じです。
これは、不可能を示す簡単な例です VPR レジスタを認識します。 セルで仮定します A1 値「請求書」とセルが含まれています A2 – 「法案」、式:
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
… "bill" の値がリストの最初にあるため、検索を停止し、セルから値を抽出します。 B1.
この記事の後半で、その方法を紹介します VPR 大文字と小文字を区別。 さらに、Excel で大文字と小文字を区別する検索を実行できる関数をさらにいくつか学習します。
最も単純なものから始めましょう – VIEW (ルックアップ) と SUMPRODUCT 残念ながら、いくつかの重大な制限があります。 次に、もう少し複雑な式を詳しく見ていきます。 インデックス+マッチ (INDEX+MATCH)、どんな状況でも、どんなデータセットでも問題なく動作します。
VLOOKUP 関数は大文字と小文字を区別します
ご存知のように、通常の機能 VPR 大文字と小文字を区別しません。 ただし、大文字と小文字を区別する方法があります。 これを行うには、次の例に示すように、テーブルに補助列を追加する必要があります。
列にあるとします B 製品識別子 (Item) があり、列から製品の価格と対応するコメントを抽出したい C и D. 問題は、識別子に小文字と大文字の両方が含まれていることです。 たとえば、セル値 B4 (001Tvci3u) と B5 (001Tvci3U) 最後の文字の場合のみ異なり、 u и U 。
ご想像のとおり、通常の検索式
=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)
=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)
戻ります $ 90、値から 001TVci3u より前に検索範囲にある 001Tvci3U. しかし、それは私たちが必要としているものではありませんよね?
関数で検索するには VPR Excel では大文字と小文字が区別されるため、ヘルパー列を追加し、そのセルに次の式を入力する必要があります (B はルックアップ列です)。
=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & CODE(MID(B2,4,1)) & CODE(MID(B2,5,1)) & CODE(MID(B2,6,1)) & CODE(MID(B2,7,1)) & CODE(MID(B2,8,1)) & IFERROR(CODE(MID(B2,9,1)),"")
=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & КОДСИМВ(ПСТР(B2;4;1)) & КОДСИМВ(ПСТР(B2;5;1)) & КОДСИМВ(ПСТР(B2;6;1)) & КОДСИМВ(ПСТР(B2;7;1)) & КОДСИМВ(ПСТР(B2;8;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;9;1));"")
この数式は、目的の値を個別の文字に分割し、各文字をそのコードに置き換えます (たとえば、 A 代わりに65歳で a コード 97) を作成し、これらのコードを一意の数字列に結合します。
その後、単純な関数を使用します VPR 大文字と小文字を区別する検索の場合:
=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)
=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)
機能の適切な動作 VPR 大文字と小文字の区別は、次の XNUMX つの要因に依存します。
- ヘルパー列は、表示可能な範囲の左端の列である必要があります。
- 探している値には、実際の値ではなく文字コードが含まれている必要があります。
CODE関数の正しい使い方
補助列のセルに挿入された式は、すべての検索値が同じ文字数であると想定しています。 そうでない場合は、最小数と最大数を知り、できるだけ多くの機能を追加する必要があります IFエラー (IFERROR) 最短検索値と最長検索値の差は何文字か。
たとえば、最短の検索値が 3 文字で、最長の検索値が 5 文字の場合、次の式を使用します。
=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & IFERROR(CODE(MID(B2,3,1)),"") & IFERROR(CODE(MID(B2,4,1)),"")
=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;3;1));"") & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;4;1));"")
機能のために PSTR (MID) 次の引数を指定します。
- 第 1 引数 – 클라우드 기반 AI/ML및 고성능 컴퓨팅을 통한 디지털 트윈의 기초 – Edward Hsu, Rescale CPO 많은 엔지니어링 중심 기업에게 클라우드는 R&D디지털 전환의 첫 단계일 뿐입니다. 클라우드 자원을 활용해 엔지니어링 팀의 제약을 해결하는 단계를 넘어, 시뮬레이션 운영을 통합하고 최적화하며, 궁극적으로는 모델 기반의 협업과 의사 결정을 지원하여 신제품을 결정할 때 데이터 기반 엔지니어링을 적용하고자 합니다. Rescale은 이러한 혁신을 돕기 위해 컴퓨팅 추천 엔진, 통합 데이터 패브릭, 메타데이터 관리 등을 개발하고 있습니다. 이번 자리를 빌려 비즈니스 경쟁력 제고를 위한 디지털 트윈 및 디지털 스레드 전략 개발 방법에 대한 인사이트를 나누고자 합니다. (text) は、抽出する文字を含むテキストまたはセル参照です (この場合は B2)。
- 第 2 引数 – 開始番号 (start_position) は、抽出する最初の文字の位置です。 あなたが入る 1 最初の関数で PSTR, 2 – XNUMX 番目の関数で PSTR 等々
- 第 3 引数 – num_chars (number_of_characters) – テキストから抽出する文字数を指定します。 常に1文字しか必要ないため、すべての関数で次のように記述します 1.
制限： 演算 VPR 大文字と小文字を区別する Excel での検索には最適なソリューションではありません。 まず、補助カラムの追加が必要です。 第二に、データが同種である場合、または検索された値の正確な文字数がわかっている場合にのみ、数式が適切に機能します。 これが当てはまらない場合は、以下に示す解決策のいずれかを使用することをお勧めします.
大文字と小文字を区別して検索するための LOOKUP 関数
演算 VIEW （ルックアップ）関連 VPR、ただし、その構文では、補助列を追加せずに大文字と小文字を区別して検索できます。 これを行うには、 VIEW 機能と組み合わせる EXACT （ちょうど）。
前の例 (補助列なし) からデータを取得すると、次の式でタスクに対処できます。
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
範囲内の数式検索 A2：A7 セル値と完全一致 F2 大文字と小文字が区別され、同じ行の列 B の値が返されます。
いいね VPRfunction VIEW 以下のスクリーンショットでわかるように、テキストと数値で同じように機能します。
重要な！ 機能のために VIEW ルックアップ列の値は昇順、つまり最小から最大の順に並べ替えられる必要があります。
機能の仕組みを簡単に説明しましょう EXACT これが重要なポイントです。
演算 EXACT 第 1 引数と第 2 引数の XNUMX つのテキスト値を比較し、まったく同じ場合は TRUE を返し、そうでない場合は FALSE を返します。 私たちにとって重要なのは、機能 EXACT 大文字と小文字を区別。
式がどのように機能するか見てみましょう 表示+正確:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
- 演算 EXACT セル値を比較します F2 列内のすべての要素 A (A2:A7)。 完全一致が見つかった場合は TRUE を返し、それ以外の場合は FALSE を返します。
- 最初の関数引数を与えるので VIEW 値 TRUE の場合、大文字と小文字を区別して完全一致が見つかった場合にのみ、指定された列 (この場合は列 B) から対応する値が抽出されます。
この説明が明確で、主なアイデアが理解できたことを願っています。 もしそうなら、私たちがさらに分析する他の機能に問題はありません。 それらはすべて同じ原理で機能します。
制限： ルックアップ列のデータは昇順で並べ替える必要があります。
SUMPRODUCT – テキスト値を検索し、大文字と小文字を区別しますが、数値のみを返します
タイトルからもわかるように、 SUMPRODUCT (SUMPRODUCT) は、大文字と小文字を区別して検索するのに役立つもう XNUMX つの Excel 関数ですが、数値のみを返します。 このオプションがあなたに合わない場合は、すぐにバンドルに進むことができます インデックス+マッチ、これは、あらゆるケースとあらゆるデータ型のソリューションを提供します。
最初に、この関数の構文を簡単に説明します。これは、大文字と小文字を区別する次の式をよりよく理解するのに役立ちます。
演算 SUMPRODUCT 指定された配列の要素を乗算し、結果の合計を返します。 構文は次のようになります。
SUMPRODUCT(array1,[array2],[array3],...)
СУММПРОИЗВ(массив1;[массив2];[массив3];…)
大文字と小文字を区別する検索が必要なので、関数を使用します EXACT (EXACT) 乗数の XNUMX つとして前の例から:
=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))
=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))
あなたが覚えているように、 EXACT セル値を比較します F2 列内のすべての要素 A. 完全一致が見つかった場合は TRUE を返し、それ以外の場合は FALSE を返します。 数学演算では、Excel は TRUE を 1、および FALSE の場合 0さらに SUMPRODUCT これらの数値を乗算し、結果を合計します。
乗算すると常に 0. 列内の完全一致の場合に何が起こるかを詳しく見てみましょう A 見つかって戻ってきた 1… 関数 SUMPRODUCT 列の数値を乗算します B on 1 結果を返します – まったく同じ数値です! これは、他の積の結果がゼロであり、結果の合計に影響しないためです。
残念ながら機能 SUMPRODUCT 乗算できないため、テキスト値と日付を扱うことはできません。 この場合、エラー メッセージが表示されます。 ＃値！ (#VALUE!) セルのように F4 下の写真で：
制限： 数値のみを返します。
INDEX + MATCH – あらゆるデータ型の大文字と小文字を区別する検索
最後に、任意のデータ セットで機能する、無制限で大文字と小文字を区別する検索式に近づいています。
この例が最後にあるのは、デザートに最善が残されているからではなく、前の例から得られた知識が、大文字と小文字を区別する式をよりよく、より速く理解するのに役立つためです。 インデックス+マッチ (インデックス + 一致)。
ご想像のとおり、機能の組み合わせは もっと露出 и INDEX のより柔軟で強力な代替手段として Excel で使用されます。 VPR. VLOOKUP の代わりに INDEX と MATCH を使用する記事では、これらの関数がどのように連携するかを完全に説明しています。
要点を要約します。
- 演算 もっと露出 (MATCH) 指定された範囲内の値を検索し、その相対位置、つまり行番号または列番号を返します。
- 次に、関数 INDEX (INDEX) は、指定された列や行から値を返します。
式へ インデックス+マッチ 大文字と小文字を区別して検索できるため、関数を XNUMX つ追加するだけで済みます。 それが何であるかを推測するのは難しくありません EXACT （ちょうど）：
=INDEX($B$2:$B$7,MATCH(TRUE,EXACT($A$2:$A$7,$F$2),0))
=ИНДЕКС($B$2:$B$7;ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);0))
この式では EXACT 関数との組み合わせと同じように機能します VIEW、同じ結果が得られます。
式に注意してください インデックス+マッチ 中括弧で囲まれているのは配列数式であり、 を押して完成させる必要があります Ctrl + Shift + Enter.
INDEX+MATCH が大文字と小文字を区別する検索に最適なソリューションである理由
バンドルの主な利点 INDEX и もっと露出:
- とは異なり、補助カラムを追加する必要はありません VPR.
- とは異なり、検索列をソートする必要はありません VIEW.
- 数値、テキスト、日付など、あらゆる種類のデータに対応します。
この式は完璧に思えますね。 実際、そうではありません。 そしてそれが理由です。
ルックアップ値に関連付けられた戻り値列のセルが空であるとします。 数式はどのような結果を返しますか? いいえ？ 式が実際に返すものを見てみましょう。
おっと、数式はゼロを返します! 純粋なテキスト値で作業している場合、これは大きな問題にはなりません。 ただし、テーブルに「実際の」ゼロを含む数値が含まれている場合、これは問題になります。
実際、前述の他のすべてのルックアップ式 (VLOOKUP、LOOKUP、および SUMPRODUCT) は、同じように動作します。 しかし、あなたは完璧な式が欲しいですよね？
数式で大文字と小文字を区別するには インデックス+マッチ 完璧です、関数に入れてください IF (IF) 戻り値でセルをテストし、空の場合は空の結果を返します。
=IF(INDIRECT("B"&(1+MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)))<>"",INDEX($B$2:$B$7, MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)),"")
=ЕСЛИ(ДВССЫЛ("B"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0)))<>"";ИНДЕКС($B$2:$B$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0));"")
この式では：
- B 戻り値を持つ列です
- 1+ 関数によって返されるセルの相対位置を変える数値です もっと露出、セルの実際のアドレスに。 たとえば、私たちの関数では もっと露出 指定された検索配列 A2：A7、つまりセルの相対位置 A2 意志 1、配列の最初のものだからです。 しかし、セルの実際の位置 A2 列には 2、追加します 1違いを補い、機能を持たせる 間接 (間接) 目的のセルから値を取得しました。
以下の図は、大文字と小文字を区別する修正された式を示しています インデックス+マッチ 行動中。 返されたセルが空の場合、空の結果が返されます。
数式を列に書き直しました B:Dスクリーンショットの数式バーに合わせます。
数式が返されます 0返されたセルにゼロが含まれている場合。
リンク希望の方は INDEX и もっと露出 戻り値が空の場合に表示されるメッセージは、式の最後の引用符 ("") に記述できます。たとえば、次のようになります。
=IF(INDIRECT("D"&(1+MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)))<>"",INDEX($D$2:$D$7, MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)),"There is nothing to return, sorry.")
=ЕСЛИ(ДВССЫЛ("D"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0)))<>"";ИНДЕКС($D$2:$D$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0));"There is nothing to return, sorry.")
