くだものの表だったのに、誰かが他の情報をハイフンで追加してしまいました・・。
元通りにするために、LEFT関数とFIND関数を使って必要な文字列だけ抽出してしまいましょう！
まずは、LEFT関数・FIND関数のそれぞれの働きについてを紹介します。
LEFT関数の仕組み
| 数式 | +LEFT(C4, 3) |
| 意味 | 対象セルの左から3番目までを抽出 |
ハイフン以前のりんごのみを抽出したいので、文字数は3を指定しました。
すると、くだものの名前はそれぞれ文字数が異なるため、途切れたり-が入った名前が返されてしまいます・・。
このままだと上手くいきません。
FIND関数の仕組み
| 数式 | +FIND(“-“, C4) |
| 意味 | 場所を知りたい文字*を入力、対象セルを指定、開始位置*は指定しないと1として認識 |
＊文字*列を指定する場合は、””で囲みましょう。これで文字であることを認識します。
＊検索文字が2個以上ある場合、4番目以降の文字から検索など開始場所の指定ができる
ハイフンを指定したので、それが左から何番目にあるのかを返してくれます。
キウイ-イエローなら、4。パッションフルーツ-フィリピン産なら10となります。
LEFT関数とFIND関数を組み合わせると・・。
それでは、この二つを組み合わせてみましょう。
| 数式 | +LEFT(C4, FIND(“-“,C4)) |
| 意味 | 対象セルを指定、検索文字を入力、対象セルを指定(LEFTの文字列と同じ) |
LEFTで抽出したいセルを選び、FINDで何番目の文字数まで抜き出すかを設定します。
でもこのままだと処理結果がおかしいことに・・・。
| 数式 | +LEFT(C4, FIND(“-“,C4)-1) |
| 意味 | FINDの結果からマイナス1をして1文字前を抽出 |
それは、FINDでハイフンまでの文字数を検索しているから。
そこでFINDの数式のあとに-1を入れてあげましょう。これでハイフンより1文字前までを抽出してくれます。
キウイ-イエローなら(４-1)なので3。パッションフルーツ-フィリピン産なら(10-1)なので9となります。
IF関数・COUNTIF関数を組み合わせて、さらに上級の使い方に
LEFT関数とFIND関数を組み合わせてハイフン以前の文字を抽出する方法をご紹介しました。
しかし下記表のように、ハイフンが含まれるセルと含まれないセルが混在する場合、
どうなるでしょうか？
検索文字が含まれないセルでは値が返されず、#VALUE!とエラーが発生してしまいました。
LEFT・FINDをこのまま使うには、すべての対象セルに検索文字が含まれている必要があるようです💦💦
そうはいっても、一つの関数ですべての計算を終わらせたいですよね。
その場合は、さらにIF関数・COUNTIF関数を追加します！盛り上がってきましたね！！
*なお、COUNTIFの詳しい説明は下記の記事で紹介しております。ご参考ください。
| 数式 | +IF(COUNTIF(C4,“*-*”),LEFT(C4,FIND(“-“,C4)-1),C4) |
| 範囲 | 対象セルを指定 |
| 検索条件 | ハイフンを指定、ハイフンを挟んでいる*はワイルドカードです* |
| 文字列 | 範囲と同じセルを指定 |
| 検索文字 | ハイフンを指定 |
| 対象 | 範囲と同じセルを指定 |
| -1 | FINDでの計算から-1することで、ハイフンより位置文字前を表す |
| 偽の場合 | ハイフンがない場合は指定のセルの値を返します |
*ワイルドカードについては、ほかの記事で紹介します。
ながーい関数が完成しました！それぞれの関数の働きは下記の通りです。
- IF関数： COUNTIFの結果が正だった場合、LEFT＋FIND関数の結果を返す。偽の場合は、そのままの商品名を返す。
- COUNTIF関数：ハイフンが含まれている場合は１を、ない場合は０を返す。
- LEFT関数： FINDで返された文字数を左から抽出する。
- FIND関数： 対象のセルから、ハイフンが何番目にあるか返す。
だいぶごちゃごちゃで、混乱するかもしれませんが・・・とりあえずできました！
いろいろな関数を組み合わせすと、幅広い計算ができるということを
知っていただけると嬉しいです😍😍😍
みなさんもぜひ色んな関数を組み合わせて、遊んでみてください！