日々、Gogole スプレッドシートで作業をしていると、こんなシチュエーションに出くわします。

• 「iPhone SE（第2世代）/ 8 / 7 / 6s / 6 [NIPPON GLASS] 超神硬EX 8倍強いガラス ブルーライト低減 光沢」という製品名から端末名だけを抜き出したい。
• 8週間の売上から1週間分の売上を計算して、それを元に今後4週間分と12週間分の売上見込み数を計算したい。また、時期によっては、売上見込み数を計算する週数を変更したい。

前者の端末名を抜き出す場合は、地道に「iPhone SE」以下端末名を別のセルにコピペすれば済む話ですが、端末名を抜き出したい製品名が100個あると、だいぶ面倒です。また、後者の売上見込み数の週数を変更する場合には、表の見出しを変更するとともに、計算式も変更しなければならないため、うっかり計算式の変更漏れが発生するかもしれません。

そこで、できるだけ手間とミスが少なくなるように、関数を使って表を作ってみたいと思います。今回ご紹介する関数は次の3つです。

• LEFT関数
• SPLIT関数
• MID関数

では、シチュエーションに沿って表を作っていきましょう。
1. 「iPhone SE（第2世代）/ 8 / 7 / 6s / 6 [NIPPON GLASS] 超神硬EX 8倍強いガラス ブルーライト低減 光沢」という製品名から端末名だけを抜き出したい。
端末名を抜き出す方法はいくつかあります。関数で言うと、

• LEFT関数
• SPLIT関数

をシチュエーションに応じて使うのが良いと思います。

▼LEFT 関数

LEFT – ドキュメント エディタ ヘルプ

LEFT関数はとてもシンプルで、「左から何番目までの文字を抜き出す」ことができます。たとえば、「iPhone SE（第2世代）/ 8 / 7 / 6s / 6 [NIPPON GLASS] 超神硬EX 8倍強いガラス ブルーライト低減 光沢」から「iPhone SE」を抜き出す場合は、以下のスクリーンショットのようになります。

（計算式） =LEFT(A2,9）

セル：A2 に入力されている文字列の左から9番目までを抜き出すという計算式になっています。半角スペースも1文字としてカウントするのがポイントです。

もし「iPhone SE（第2世代）/ 8 / 7 / 6s / 6」を抜き出すとなると、計算式は「=LEFT(A2,32)」になります。抜き出したい文字数が増えると、数えるのが大変です。

（計算式） =LEFT(A2,32)

ただ、計算式をひとつ作ってしまえば、下の行にコピペするだけで端末名を抜き出せるので便利です。

ARRAYFORMULA関数と組み合わせると、製品名が増えても計算式のコピペ漏れを防げていいです。

（計算式）=ARRAYFORMULA(LEFT(A2:A,32)

ただ、9行目以下は、含まれる端末名が異なったり、端末名が製品名の頭にないものもあって、うまく抜き出せていません。こういうときは、うまく抜き出せたところは値コピペして、抜き出せていない製品の計算式を考えていきます。

値コピペすると計算式がなくなり、ただの文字列になります。ただの文字列にしたいテキストをコピーして、「値のみ貼り付け」を選択すると、ただの文字列になります。ショートカットキー「command + shift + V」でも値ペーストすることができます。

計算式「=ARRAYFORMULA(LEFT(A2:A,32)」で入力した端末名のうち、「iPhone SE（第2世代）/ 8 / 7 / 6s / 6」と入力されたセルだけをコピーして、値ペーストしたので、9〜11行目に入力されていた端末名は消えます。セル：B2に入力されていた計算式「=ARRAYFORMULA(LEFT(A2:A,32)」が、値ペーストされることで消えてしまったからです。

9行目の「iPhone X [Aegis Pro] フルカバーTPUケース＆ガラスセット クリアケース＋ホワイトフレームガラス」もLEFT関数を使って端末名を抜き出すことができます。

（計算式） =LEFT(A9,9)

▼SPLIT関数

SPLIT – ドキュメント エディタ ヘルプ

また、これは最適な方法ではないと思ってはいるのですが、SPLIT関数を使っても端末名を抜き出せます。最適ではないと思っているのになぜ使うのかというと、LEFT関数を使って抜き出す文字数を数えるのが面倒だからです（汗）。

SPLIT関数は、その文字列に含まれる記号や文字を境に分けることができるので、端末名をうまく抜き出せそうな半角スペースや記号で文字列を分解してから、端末名に必要な部分を「&」で結合させることが多いです。今回は「[」を境に分けると良さそうなので、次のような計算式を作りました。

（計算式）=SPLIT(A9,“[“)

「[」に分けると、製品名が「iPhone X」と「Aegis Pro] フルカバーTPUケース＆ガラスセット クリアケース＋ホワイトフレームガラス」に分かれます。

この状態で、セル：B9を値コピペすると、入力されていた計算式「=SPLIT(A9,“[“)」が消えて、値コピペした「iPhone X」だけが残ります。

半角スペースを境に文字を分けるときは、「”[“」の代わりに「” “」を使います。ダブルクオテーションの間に半角スペースが入っています。地道に端末名をコピペして抜き出す作業ほどではないものの、半分手作業なので苦肉の策といった感じです。

2. 8週間の売上から1週間分の売上を計算して、それを元に今後4週間分と12週間分の売上見込み数を計算したい。また、時期によっては、売上見込み数を計算する週数を変更したい。

表にすると、下記のようなスクリーンショットになります。3行目が売上数で、8週間の売上の平均と、それを4週分、12週分した数字を入れていきます。ちなみに、2行目には日付が入っていて、たとえば、2020年9月21日は「20200921」というように8桁の数字で入力されています。

計算自体は結構簡単なので、

• 直近8週平均
• 直近8週平均×4週
• 直近8週平均×12週

をサクッと計算してみます。青字の計算式は、次のとおりです。

• J列：直近8週平均→ =AVERAGE(B3:I3)
• K列：直近8週平均×4週→ =J3*4
• L列：直近8週平均×12週→ =J3*12

この計算式さえできれば、型番数が増えて100行になっても、1,000行になっても、作った計算式を下の行にコピペするだけなのですが、最近作る表が大きすぎて、どんな計算式が入っているのか、どこまで変更したのか自分でもわからなくなってきました。

エラーが起こっていたら計算式の内容を見てたどっていけばいいのですが、参照するセルが間違っているのになまじ正しく計算されていると、計算が間違っていることにすら気づかないという怖い状態です。

ARRAYFORMULA関数を使えば、入力した計算式が下の行まで自動的に計算されるのですが、今度は見出しを変更し忘れて、見出しには「直近8週平均×4週」と書いてあるのに、計算式は週数を変更して「直近8週平均×5週」の計算結果が入力されているという厄介なことが起こります。そこで考えたのが、見出し名を変えると、計算式も変更されるという仕組みでした。MID関数を使います。

▼MID関数

MID – ドキュメント エディタ ヘルプ

• 直近8週平均×4週
  • （変更前の計算式）=J3*4
  • （変更後の計算式）=J3*MID(K2,8,1)
• 直近8週平均×12週
  • （変更前の計算式）=J3*12
  • （変更後の計算式）=J3*MID(L2,8,2)

直近8週平均を4倍するときは、「4」の代わりに「MID(K2,8,1)」、12倍するときは「12」の代わりに「MID(L2,8,2)」を入力しました。MID関数は、入力されている文字列の左からの文字を起点として、指定した文字数を抜き出すことができます。

「直近8週平均×4週」の場合は、左から8番目の文字を1文字抜き出すことになるので「4」が抜き出されます。同じように、「直近8週平均×12週」の場合は、左から8番目の文字から2文字抜き出して「12」となります。

なんだかまわりくどいことをしていますが、ARRAYFORMULA関数と組み合わせて使うことができるので、計算式を変えたいときは、見出しの週数を変更するだけで済むようになります。ARRAYFORMULA関数を組み合わせた場合の計算式は以下のとおりです。

• 直近8週平均×4週
  • （計算式） =ARRAYFORMULA(J3:J*MID($K$2,8,1))
• 直近8週平均×12週
  • （計算式） =ARRAYFORMULA(J3:J*MID($L$2,8,2))

※ 「J3」を「J3:J」に書き換えることと、MID関数の中の参照セルを「$K$2」「$L$2」というように絶対参照に書き換えることがポイントです。

これで、見出し名の週数を書き換えただけで、計算結果も変わるようになりました。2行目の見出し名を「直近8週平均×4週」から「直近8週平均×5週」に変えると、計算結果も5週分になります。同じように、「直近8週平均×12週」から「直近8週平均×15週」に変えると、計算結果も15週分になります。

以上、今回もマニアックな内容になってしまいました。表をできるだけ簡単に楽に作る、更新したときのミスをなくす（漏れのないよう更新しやすくする）ための技を人それぞれ持っているんだろうなぁと思う今日この頃です。

使える機会があったら、ぜひレパートリーに加えてみてください。

★

「あさすけ先生の、超役に立つGoogleスプレッドシート解説」関連記事

 
▶ Googleスプレッドシートで関数を使って単語から文字や数字を抜き出す方法（本記事）
▶ 【スプレッドシート時短術】19,767個の空白セルを3秒で「0」にする小技
▶ 計算式のコピペ漏れを防ぐ！　気難しそうに見えて案外シンプルないいヤツ 〜ARRAYFORMULA関数〜
▶ VLOOKUP関数のエラー原因を探すのに2時間もかかっていた私が、いろいろな関数を使いこなせるようになったコツを考えてみた。
▶ Googleスプレッドシートに週ごとにタスクをまとめて、その週の未完了タスク・完了タスク・タスクの達成率がひと目でわかるようにする
▶ IMPORTRANGE関数で休日設定シートを転記すればすべてが丸く収まった
▶ 値ペーストと書式ペーストを使いこなすと作業が格段に速くなる – エクセルやGoogleスプレッドシート
▶ Googleスプレッドシートでデータの結合・分割の幅を広げたい。改行するなら CHAR(10) を使おう！
▶ 「$」マークを使いこなして表をパパッと作る 〜相対参照と絶対参照の復習〜
▶ わざわざ使いたい、転置して貼り付けができるGoogleスプレッドシートの「TRANSPOSE関数」
▶ 地味にはまっている、GoogleスプレッドシートでUNIQUE関数とSORT関数で入力規則（プルダウン）用のデータリストをつくる方法
▶ Googleスプレッドシートのフィルタ表示機能が便利
▶ Googleスプレッドシートでチェックリストを5分で作ってみる
▶ 営業日の異なる日本と中国のスケジュール作成ツールをGoogleスプレッドシートで作ってみた