Spreadsheet の ARRAYFORMULA 活用術
はじめに
この記事では効率の良いSpreadsheetの処理をするために有効な関数 ARRAYFORMULA の基本的な機能と、その代表的な活用法について自分なりにまとめてみました。
内容としては、ある程度スプレッドシートを扱えて、関数を活用しているがもっと効率よく作業をしたい、スプレッドシートの管理を簡単にしたいと思っている方向けかと思います。
参考になれば幸いです。
ARRAYFORMULA とは
Spreadsheet をある程度使い慣れてくると、同じような関数を複数書きたくなる場面が出てきます。そんなときにオートフィルを使って、同じ関数を一気にコピーしちゃうって方もいるかもしれません。
同じ関数をコピーする代わりに、ARRAYFORMULA という関数を使うとコピーする手間なく同じような計算が一気にできます。
ARRAY は「配列」という意味で、複数の要素からなるものを指す言葉です。スプレッドシートで言えば、範囲を指すことが多いです。つまり ARRAYFORMULA は「範囲」を一度に計算するための関数になります。
ARRAYFORMULA の基本的な使い方
ARRAYFORMULA は範囲で計算する関数なのですが、これだけでは動作しません。既存の関数と組み合わせることで効果を発揮します。
例えば、数字の足し算を考えます。
以下のような表があった場合に、=B17+C17など左と右を足す関数を書いて、オートフィルで伸ばすのがよくあるパターンです
| セル | B列 | C列 | D列 (B列 + C列) |
|---|---|---|---|
| 1 | 3 | 5 | =B1+C1 |
| 2 | 4 | 6 | =B2+C2 |
| 3 | 7 | 8 | =B3+C3 |
ですが、この場合、黄色いところ全てに関数が書かれており変更を加えようと思うと、また関数を一つ書いて、全部のセルに埋め直しになります🥲
ここで、ARRAYFORMULA の出番です。
=ARRAYFORMULA(B1:B3+C1:C3)
このように一箇所書くと、範囲どおしで足した結果を範囲として出力し勝手に埋めてくれます。
| セル | B列 | C列 | D列 (ARRAYFORMULA 使用) |
|---|---|---|---|
| 1 | 3 | 5 | 8 |
| 2 | 4 | 6 | 10 |
| 3 | 7 | 8 | 15 |
一箇所書くだけで、範囲で更新がかかるため、変更があってもこの ARRAYFORMULA の部分を修正するだけで一気に変更がかかり効率的です。
例えば、全てを掛け算に変えたいと思った時には、一番上のセルの ARRAYFORMULAの中身を
=ARRAYFORMULA(B1:B3*C1:C3)
と変えてあげるだけで、掛け算への変更がその下の範囲にも一気に波及します。
後から範囲を足しても対応できる ARRAYFORMULAの書き方
ARRAYFORMULA を使う場合、固定された範囲にだけ適用するのではなく、動的に範囲を増やしても問題なく対応できる書き方をしておくと、さらに便利です。
例えば、以下のようなデータ表があったとします。
| 名前 | 点数 |
|---|---|
| 太郎 | 85 |
| 次郎 | 90 |
| 三郎 | 78 |
点数にボーナス点を加算する計算をしたい場合、通常の固定範囲では以下のように書きます。
=ARRAYFORMULA(B2:B4+10)
ただし、もしデータが後から増えた場合、範囲を手動で更新する必要が出てきます。
これを防ぐために、スプレッドシートの "無限範囲" を指定する書き方を活用します。
=ARRAYFORMULA(IF(B2:B<>"", B2:B+10, ""))
このように書くことで、B列に新しいデータが追加されても、条件付きで自動的に計算が適用されます。この方法では、空のセルには結果が表示されず、既存のデータ範囲だけが計算されるため、見た目もすっきりします。
具体的な活用例
1. VLOOKUP と組み合わせる
ARRAYFORMULA は他の関数と組み合わせることで、さらに応用の幅が広がります。その代表例として VLOOKUP との組み合わせがあります。
例えば、以下のような商品データベースと購入リストがあるとします。
商品データベース
| 商品ID | 商品名 | 価格 |
|---|---|---|
| 101 | ペン | 100 |
| 102 | ノート | 200 |
| 103 | 消しゴム | 50 |
購入リスト
| 購入ID | 商品ID | 商品名 | 価格 |
|---|---|---|---|
| 001 | 101 | ||
| 002 | 103 | ||
| 003 | 102 |
この場合、購入リストの「商品名」と「価格」を自動的に埋めたいとします。通常の VLOOKUP を使う場合、各セルに個別の式を書く必要がありますが、ARRAYFORMULA を使えば一発で解決します。
=ARRAYFORMULA(IF(B2:B<>"", VLOOKUP(B2:B, 商品データベース!A:C, 2, FALSE), ""))
これにより、購入リストの B列に商品IDを入力するだけで、対応する商品名が自動的に表示されます。同様に、価格についても以下の式を使えば対応可能です。
=ARRAYFORMULA(IF(B2:B<>"", VLOOKUP(B2:B, 商品データベース!A:C, 3, FALSE), ""))
このようにすることで、購入リストに新しい行を追加しても、手動で関数を入力する手間を省けます。
2. データクレンジングでの活用
大量のデータを扱う場合、データクレンジングが必要になることがあります。ARRAYFORMULA を使うことで、一括でデータを整形することができます。
例: メールアドレスの正規化
以下のようなリストがあったとします。
| 名前 | メールアドレス |
|---|---|
| 山田 | YAMADA@EXAMPLE.COM |
| 田中 | tanaka@example.com |
| 鈴木 | SUZUKI@EXAMPLE.COM |
全てのメールアドレスを小文字に統一したい場合、通常は一つ一つ関数を入力する必要がありますが、ARRAYFORMULA を使えば簡単です。
=ARRAYFORMULA(LOWER(B2:B))
これにより、全てのメールアドレスが自動的に小文字に変換されます。
例: 空白の削除
データに余計な空白が含まれている場合もあります。このような場合、TRIM 関数と ARRAYFORMULA を組み合わせることで、データ全体を一括で整形できます。
=ARRAYFORMULA(TRIM(A2:A))
3. 条件付きの集計
スプレッドシートで特定の条件に基づいて集計を行う場合にも、ARRAYFORMULA は役立ちます。
例えば、以下のような売上データがあるとします。
| 日付 | 売上額 |
|---|---|
| 2025-01-01 | 1000 |
| 2025-01-02 | 2000 |
| 2025-01-03 | 1500 |
特定の日付以降の売上額を合計したい場合、IF と組み合わせて次のように書けます。
=ARRAYFORMULA(SUM(IF(A2:A>=DATE(2025,1,2), B2:B, 0)))
これにより、条件を満たす範囲内での集計が一発で可能になります。
4. 文字列の分割と結合
大量のデータで特定の列の文字列を分割したり結合したい場合にも、ARRAYFORMULA は便利です。
例: フルネームを名前と苗字に分割
以下のようなリストがあったとします。
| フルネーム |
|---|
| 山田 太郎 |
| 鈴木 次郎 |
| 田中 三郎 |
名前と苗字を分けたい場合、次のように書けます。
=ARRAYFORMULA(SPLIT(A2:A, " "))
例: 名前と苗字を結合
逆に、名前と苗字を結合してフルネームを作りたい場合は次のように書けます。
=ARRAYFORMULA(B2:B & " " & C2:C)
おわりに
ARRAYFORMULA は、一見シンプルな関数ですが、他の関数と組み合わせることで、スプレッドシートの効率を飛躍的に向上させることができます。特に、動的なデータの追加や変更が頻繁に行われる場合、その効果は絶大です。
この記事で紹介した活用法を参考に、ぜひ皆さんのスプレッドシート作業をさらに効率化してみてください。
効率的なスプレッドシート管理で、作業時間を短縮し、より生産的な日々を過ごしましょう！
Discussion