☰

Excel のセルの最初の数字を選択するにはどうすればよいですか?

tag-icon • tag-icon microsoft-excel microsoft-excel-2013 string-manipulation
Excel のセルの最初の数字を選択するにはどうすればよいですか?

セルに数値とテキストの両方を格納するテーブルがあり、数値に基づいて算術演算を実行したいと考えています。テーブルは次のようになります。

Number1       | Number2       | Product
2             | 3             | 6
3 (some text) | 40            | 120
4             | 5 (here too)  | 20

したがって、これらの醜いテキストが存在する場合でも、列 3 を自動的に生成できるようにする必要があります。最初の非数値文字までの部分文字列を解析して数値にする関数が必要だと思います。Excel でこれを行うことはできますか?

追伸:数字は 1 桁以上になることがあります。整数であると想定しても問題ありませんが、浮動小数点数にも適用されるソリューションがあると便利です :)

ありがとう、

答え1

文字列の先頭にある数字を取得するには、次のようにします。

=MID(A2,1,AGGREGATE(14,6,SEARCH("}}}",SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(A2)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"")))),1))

基本式として、これは数値の末尾を見つけ、それを MID() 関数の末尾として返します。

ここではたくさんのことが起こっています:

SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(A2)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"")))この部分は数字を反復処理して、各数字の最後のインスタンスを に置き換えます}}}。

SUBSTITUTE の 3 番目の基準はインスタンスです。 のインスタンスの数を検索しますLEN(A2)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},""))。数値を反復処理し、一度に 1 つずつ何もないものに置き換えます。次に、元の文字列と新しい文字列の長さの差を検索します。

したがって、A2 の場合、 を反復すると22 が見つかり、外側の Substitute が最後の 1 つを に置き換えます}}}。これは単なる一時的なプレースホルダーです。

Aggregate 関数は多機能関数です。14 はLarge()関数を使用していることを関数に伝えます。6関数にエラーを無視するように伝えます。これは、反復処理の多くで何も見つからずエラーが返されるという点で重要です。

最後に を付けると、各番号の最後のインスタンスで反復処理によって配置される1一時的なものを検索する Search 関数から最大の戻り値を取得することを関数に指示します。}}}

したがって、Aggregate は見つかった最大数を返します。これを Mid 関数の長さ基準に渡します。

これで、文字列の先頭の数字が見つかりました。

したがって、これら 2 つを掛け合わせると、目的の出力が得られます (任意の数学関数は、返された文字列を数値に変換します)。

=MID(A2,1,AGGREGATE(14,6,SEARCH("}}}",SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(A2)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"")))),1))*MID(B2,1,AGGREGATE(14,6,SEARCH("}}}",SUBSTITUTE(B2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(B2)-LEN(SUBSTITUTE(B2,{1,2,3,4,5,6,7,8,9,0},"")))),1))

ここに画像の説明を入力してください

注意点集計関数は Excel 2010 で導入されました。古いバージョンでは動作しない可能性があります。

古いバージョンをお持ちの場合は、次の長い式を使用する必要があります。

=MID(A2,1,MAX(IF(ISNUMBER(SEARCH("}}}",SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(A2)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},""))))),SEARCH("}}}",SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(A2)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"")))))))*MID(B2,1,MAX(IF(ISNUMBER(SEARCH("}}}",SUBSTITUTE(B2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(B2)-LEN(SUBSTITUTE(B2,{1,2,3,4,5,6,7,8,9,0},""))))),SEARCH("}}}",SUBSTITUTE(B2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(B2)-LEN(SUBSTITUTE(B2,{1,2,3,4,5,6,7,8,9,0},"")))))))

これは、最大値を見つける前にまずエラーをテストする必要があることを除けば、上記のものとほぼ同じです。

答え2

数字が常にスペースでテキストと区切られていると仮定すると、次のようなものを使用できます。例に A2 から始まるデータがあるとすると、C2 の最初の結果は通常次のようになります。

=A2*B2

単純なセル参照を、ソース セルが数値のみの場合は数値として扱い、テキストも含まれている場合は数値を抽出する数式に置き換える必要があります。したがって、C2 は次のようになります。

=IF(ISNUMBER(A2),A2,LEFT(A2,FIND(" ",A2)-1)) * IF(ISNUMBER(B2),B2,LEFT(B2,FIND(" ",B2)-1))

ソース セルが数値でない場合は、最初のスペースを見つけて、その前の文字を返します。技術的には、返されるのは数字を含むテキスト値ですが、Excel は、積を計算する際にそれを数値として扱うほどスマートです (数値に変換するために別の関数内にネストする必要はありません)。また、数値が浮動小数点の場合でも、これは機能することに注意してください。

注意: これは「クリーンな」ソース データ、つまり数値エントリ、または数字で始まりスペースが続くテキスト エントリに依存します。これに従わないセルがある場合は、データをクリーンアップするか、何らかのエラー チェックを含める必要があります。最適な解決策は、2 つのデータ列を分割して、テキストが別のセルにあるようにすることです。

関連情報