実践的な Excel スキルの共有: 数値を抽出するには?

前回の記事「Excelの実践スキルを共有:表の閲覧を効率化する7つの方法」では、表の閲覧を効率化する7つのヒントについて学びました。今日は Excel での数値の抽出について説明し、一般的な抽出状況をいくつか紹介して説明します。

数式を使用してセル内の数値を抽出します。対応する数式を設計するには、特定のデータ構造に基づいて特定のパターンを見つける必要があります。もちろん、いわゆるユニバーサル抽出式もありますが、非常に複雑で多くの計算が必要となるため、記事の最後で簡単に説明します。

最初のタイプの状況: 番号が左側にある

例 1:番号が左側にある左側、数字は です。 数字は固定です。

この例では、データは非常に規則的で、番号はすべて左側の 3 桁にあります。学生番号を個別に抽出したい場合は、左の関数を使用する必要があります: =LEFT(A2,3)

数式も非常に単純です。最初のパラメータは数値を抽出するセルで、2 番目のパラメータは数値を抽出するセルです。パラメータは、抽出される桁数です (「左から数えて」から)。例 1 のこのタイプのデータの場合、使用するときに数値の長さに応じて 2 番目のパラメーターを変更するだけです。

例 2: 数字は左側にあり、桁数は固定されていませんが、明らかな区切り文字があります。

数値の長さが固定の 3 桁でない場合、以下に示すように、前の方法は機能しません。このとき、データソースのルールを見つけて、そのルールを使用して操作する必要があります。現在のデータには一律に「-」記号が付いています。「-」記号の位置に基づいて数値の長さを判断し、 left を使用してそれを抽出できます。

この場合、別の関数、つまり find 関数を使用する必要があります。

式 =FIND("-",A2,1) を使用して、「-」の位置を決定します。

find 関数には 3 つのパラメータがあります。最初のパラメータは検索するコンテンツ、2 番目のパラメータは検索する場所、3 番目のパラメータはどの単語から検索を開始するかです。 （左から数えて）。

=FIND("-",A2,1) は、セル A2 内の "-" を先頭 (左側の最初のセル) から検索することを意味します。数式の結果はセル内の「-」の位置（どの文字か）を表す数値になりますが、このとき抽出したい数値の長さはfindの結果から1を引いた値となるので、方法は次のようになります。 : =LEFT(A2 ,FIND("-",A2,1)-1)

このメソッドには幅広い用途があります。より明白な区切り文字 (記号、漢字、文字など) が見つかる限り、この方法を使用できます。

例 3:数字は左側にあり、桁数は固定されておらず、明らかな区切り文字もありません。

データがこのような場合、前の方法は使用できません。数値の長さは不確かで、区切り文字はありません。唯一のルールは、データが数字と漢字のみが含まれます (文字やその他の記号は含まれません)。

Excelでは文字の長さが2種類あり、半角文字(英語モードで入力した数字、文字、記号)は長さが1、全角文字(漢字、中国語記号、など）の長さは 2 です。

len 関数は、セルの内容の長さを特に計算する関数であり、全角と半角は区別されません。幅とその長さは通常の理解と一致しており、文字の「数」に相当します。全角と半角の違いを見てみましょう:

lenb 関数は len 関数と同じように機能しますが、違いは長さを計算するときの違いです。コンテンツの半角と全角が区別されており、結果から明らかな違いが分かります。全角に基づくテキストの長さは全角と半角の 2 倍ですが、数字と文字はどちらの場合もまったく同じです。

例 3 の場合、len と lenb を使用して数値の長さを決定し、left を使用して数値を抽出する必要があります。

次の図に示すように、式 =LEN(A2)*2-LENB(A2) を使用して数値の長さを決定します。

なぜ len*2-lenb は数値の長さを取得するのでしょうか?

前の 2 つのケースの比較結果によると、lenb 統計長、テキスト長は len 統計値の 2 倍、数値の長さは len 統計値と一致するため、式 len*2 -lenb は数値の長さを計算できます。

数値の長さを数えると、解決策は明らかです: =LEFT(A2,LEN(A2)*2-LENB(A2))

##例 3 のこの状況も典型的なものです。len*2-lenb であることを覚えておいてください。どうしても覚えていない場合は、問題が発生したときに個別に計算できます。まず、数値の長さ。計算して左に結合するだけです。

例 4: 数字は左側にあり、文字などの半角文字が含まれています。

この状況は非常に複雑です。唯一のルールは、番号が左側にあるということです。解決方法を見てみましょう。

式

=-LOOKUP(1,-LEFT(A2,ROW($1:$9))) を使用できます。

この式については、おそらく説明するには 5,000 文字かかると思いますので、手順を覚えておいてください。変更できるのは最後の 9 だけです。最長の数値が 9 桁を超える場合は、9 を変更する必要があります。これは 99 を使用して直接行うことができます。

最後に、

この式は、番号が左側にある場合はすべての状況で機能します。

#2 番目のタイプの状況: 数字が右側にある

数字が右側にある状況は、以下は簡単な説明です:

桁数が固定の場合は右を使用して直接抽出します;

桁数が固定の場合は右を使用します。桁数は固定されていませんが、区切り文字があります。find 関数とmid 関数を使用して抽出を完了します。 =MID(A2,FIND("-",A2,1) 1,9)

（注:

式 最後のパラメータ値「9」は、最大桁数に応じて変更する必要があります。たとえば、最大桁数の場合、抽出される桁数は 10 です。10 または 10 より大きい数値として記述する必要があります。)

桁数は同時に固定されません。区切り文字がない場合でも、 len と lenb の組み合わせ: =RIGHT(A2,LEN(A2)*2-LENB(A2))

#引き続きルックアップに big を使用させますルールなしで移動します: =-LOOKUP(1,-RIGHT(A2,ROW($1:$9)))

#3 番目の状況: 数値が中央にあります

数値が中央にあります。これは非常に特殊です。この状況は、通常、より標準化されたデータ ソースでは発生しません。例を挙げてみましょう:

たとえば、図のデータで、数値の前後の長さが固定されている場合、最初に関数を使用して数値を変更できます。

#計算式は比較的簡単なので説明は省略します。ステップ、それは先行研究ですでにわかっていますよね？

これから説明する方法は、より一般的な方法です。これを万能抽出式と呼ぶ人もいます:

=-LOOKUP(0,-MID(A1,MIN ( FIND(ROW($1:$10)-1,A1&1/17)),ROW($1:$9)))

#配列式、3 つ押す必要があります「

CtrlShiftEnter」キーを押して終了します。

概要

数式を使用してセル内の数値を抽出する方法については、上記でほとんどの状況を説明しましたが、もちろんすべてではありません。たとえば、小数点が含まれる場合は、小数点があるためさらに特殊になります。

最後の配列数式は、より一般的な数式とみなすことができます。強調する必要があるのは、標準的でないデータ ソースにより、ほとんどの問題が非常に厄介になるということです。そのため、名前や数量など、データの 1 つの属性のみをセルに保存する良い習慣を身につけてください。価格やその他の情報は個別に保存されます。これにより、統計効率が大幅に向上します。

もちろん、式を使う能力を高めるという観点からは、数値の抽出などの問題も関数の練習としては良い題材です。これまでに学習した関数を組み合わせて、数値を抽出するための式をさらに考えていただけると幸いです。

関連する学習の推奨事項: Excel チュートリアル

以上が実践的な Excel スキルの共有: 数値を抽出するには?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。