関数で複数データを取り出す:Excel関数の技
「Excelの便利機能活用術」 LEFT関数とMID関数、FIND関数で文字列から任意の文字を抽出する ITライター 立山秀利 ExcelのLEFT関数とMID関数を使うと、文字列から指定した文字を抽出できる。 さらにFIND関数を組み合わせると、条件に応じて文字を抽出することも可能だ。 今回は各関数の基本的な使い方、活用例として氏名から姓と名を分ける方法を解説する。 文字列を抽出や検索を行う Excelには文字列の中から文字を抽出するための関数が数多く用意されている。 その一つであるLEFT関数は、文字列の先頭から指定した数の文字を抽出する関数である。 この文字列の先頭から2文字抽出して、B1セルに表示するには、LEFT関数を用いて次のような数式を入力すればよい。 A1セルに「Orange」が入力されている場合、先頭から2文字目から3文字を抽出して、B1セルに表示するには、MID関数を用いて次のような数式を入力すればよい。 FIND関数は指定した文字の位置を検索する関数である。 検索対象の文字列の中で何文字目に位置するのか、数値として求められる。 省略すると1が指定されたと見なされる。 A1セルに「Orange」が入力されている場合、「r」という文字が先頭から何文字目にあるのか検索し、B1セルに表示するには、FIND関数を用いて次のような数式を入力すればよい。 文字「r」は「Orange」の先頭から2文字目にあるので、2という数値が求められる。 （画像をクリックすると拡大表示します） 氏名から姓と名を別のセルに取り出す これら3つの関数の組み合わせの実例として、今回は姓と名が半角スペースで区切られたかたちで1つのセルに入力された氏名から、姓と名をそれぞれ別のセルに抽出する方法を紹介する。 今回は図4のように、姓と名が半角スペースで区切られたかたちで、氏名がA2～A6セルに入力されているとする。 B2～B6セルに姓、C2～C3セルに名を取り出したいとする。 どのような数式を入力すればよいか、先頭行のB2セルで考えよう。 まずはA2セルの氏名で、姓と名の区切りとなっている半角スペースの位置をFIND関数で調べる。 すると、FIND関数は「FIND " ",A2 」と記述すればよいことになる。 A2セルの「山田 圭子」では、姓が「山田」の2文字であるため、半角スペースの位置は3と検索される。 半角スペースの1文字前が姓の最後の文字であり、その位置は姓の文字数と等しいことになる。 従って、「FIND " ",A2 -1」をLEFT関数の引数「文字数」に指定すればよい。 引数「文字列」には、氏名が格納されたA2セルを指定する。 この数式をB6セルまでコピーすると、各セルに姓が抽出される。 1文字でも2文字でも3文字の姓でも抽出することができる。 （画像をクリックすると拡大表示します） 次は名を抽出する数式を先頭行のC2セルで考えてみる。 半角スペースの位置は姓と同様に、「FIND " ",A2 」で求められる。 名の開始位置は半角スペースの1文字後ろなので、「FIND " ",A2 」に1を足せば求められる。 引数「文字数」だが、多めの文字列を指定しておけば、その文字数以内で抽出できるようになっている。 今回は10を指定するとする。 この数式をC6セルまでコピーすると、各セルに名が抽出される。 （画像をクリックすると拡大表示します） これら3つの関数の組み合わせなら、例えばハイフン区切りで入力された電話番号から市外局番を抽出するなど、様々な応用ができる。 また、Excelには他にも、文字列の末尾から指定した数の文字を抽出するRIGHT関数など、文字列から抽出するための関数がいくつか用意されている。 このようにLEFT関数とMID関数を使えば、文字列から任意の場所から任意の数だけ文字を抽出できる。 さらにFIND関数と組み合わせれば、文字列から指定した条件で文字を抽出することも可能となるのだ。 その他の【Excel】を見る•
次の
関数で複数データを取り出す:Excel関数の技
関数で複数データを取り出す:Excel関数の技• 関数で複数データを取り出す• VLOOKUP関数で検索条件と一致したデータを取り出すことができますが、一致するデータが複数あるときには先頭だけしか取り出せません。 などの機能を使えばよいのですが操作が分からない人も使うので、関数で取り出したいというケースがあります。 そのようなケースではどのようにしたら良いか・・・ということについて書いてみます。 スポンサードリンク 作業列を使って複数データを取り出す サンプルデータ• ある条件と合致するデータを 複数 取り出したい・・・という時の考え方の例です。 下表のようなデータで、ある日付のデータをすべて抽出する方法を考えてみます。 コピーして練習に使えるように表を追記しておきます。 日付 商品 販売数 5月1日 みかん 100 5月2日 りんご 120 5月3日 バナナ 110 5月4日 桃 50 5月1日 バナナ 140 5月2日 桃 110 5月3日 みかん 105 5月4日 りんご 150 5月1日 バナナ 120 5月2日 桃 140• Microsoft 365では Filter関数を使うことができます。 Filter関数の使い方は をご覧ください。 次項のように、作業列や配列数式を使う必要がなくなり、関数を使って簡単にデータを抽出できるようになりました。 商品が「バナナ」のデータを抽出し、さらに、日付の昇順に並べ替えてみます。 SORT関数の使い方は をご覧ください。 作業列を使いたくないというケースがあります。 そのようなときは配列数式を利用することができます。 【考え方】条件と一致する行は何行目かを計算します。 計算された行数の小さい方から順番にデータを取り出します。 配列数式にします G5セルをI5セルまでコピーし、更にG5:I5セルを下方向へ必要なだけコピーします。 G2セルの検索値を5月2日に変えた結果の例です。 この部分を配列で処理しています。 SMALL ・・・・・・・,ROW A1 で、小さな値 行位置 から順番に取り出すようにしています。 【考え方】条件と一致しているか否かを調べます 作業列を利用します。 一致しているものについては連番を付けてやります。 この連番順にデータを取り出せばよさそう・・・と考えています。 検索する値はG2セルに入力するものとします。 D列を作業列として使用します。 条件と一致した行に連番を入れます。 A2セルとG2セルが同じなら、同じデータはA2セルから数えて何個目かを表示しています。 作業列の連番順にデータを取り出します。 行番号だけを絶対参照にします• ROW A1 を使うのは下方向へコピーして使うとき、1,2,3 と連番を発生させるためです。 D列の連番よりROW A1 が大きくなると、一致するデータは無いので、エラーとなります。 G5セルに入力した数式を右方向、および下方向へコピーすると完成です。 G5セルを選択し、フィルハンドルを右方向へ、I5セルまでドラッグして数式をコピーします。 G5:I5セルを選択して、フィルハンドルを下方向へドラッグして数式をコピーします。 下方向へは必要なだけコピーします。 一致するデータが表示しきれるだけの行にあらかじめコピーします。 Microsoft 365では XLOOKUP関数やSEQUENCE関数を使うことができます。 XLOOKUP関数の使い方は をご覧ください。 SEQUENCE関数の使い方は をご覧ください。 数式がシンプルなものになります。 しかし、Filter関数が使えるので・・・あくまでも使い方の一例ということです。 SEQUENCE 5 は連続した数値の配列（1,2,3,4,5 を作成しています。 該当するデータが5個程度を想定しています。 多くのデータが該当すると思われる場合はこの値を調整してください。 G5セルの数式を H5セル、I5セルにコピーします。 作業列を使う 2• 作業列をデータリストの左端列に設けることができると、なじみの多いVLOOKUP関数で取り出すことができます。 Filter関数を使うことができない場合は、左端列に作業列を作れるのであれば非常に簡単な数式となります。 データの左端列、A列に作業列を設けます。 この数式を横方向へ I5セルまでコピーし、さらに、G5:I5セルを選択して、下方向へデータが抽出されると想定される行までコピーします。 VLOOKUP関数では検索値が見つからないとエラーになるので、IFERROR関数でエラー処理をしています。 VLOOKUP関数の引数の列番号は数式をコピーするのを前提にCOLUMN関数を使って 連番 2,3,4 を生成するように小細工をしています。 IFERROR関数はExcel2007で追加された関数です。 詳細な使い方は をご覧ください。
次の
