やりたいこと
スプレッドシートで、次のような表の変換をしたい。
実現方法
- 見出しを結合する
- 見出しを分割
- 見出しに対応する値を入れる
ここで紹介する方法は、見出しが縦・横ともに一意である場合に限ります。
見出しに重複がある場合は使用できませんのでご注意ください。
1. 見出しを結合する
上記の例の「月」と「科目」を結合した見出しを作る。
このとき、「月」と「科目」の間に全ての見出しで使用していない文字を挟むこと。
※ 今回は「-」を使用。
=FLATTEN(ARRAYFORMULA(A2:A4&"-"&B1:F1))
【解説】
FLATTEN(範囲)
引数に与えた二次元配列を一次元化する関数。
ARRAYFORMULA(A2:A4&"-"&B1:F1) で作成した二次元配列を一次元化している。
ARRAYFORMULA(配列数式)
引数に与えた配列の各値に対して数式を適用する関数。
戻り値は、複数行および複数列になる。
A2&"-"&B1 で 5月-英語 を作れるが、ARRAYFORMULA(A2:A4&"-"&B1:F1) では、A列の2行目〜4行目の値 と B列〜F列の1行目の値を使用して、新規配列を作成している。
この数式のみを実行した結果は以下となる。
※ &"-"&の「-」を好きな文字列に変えることで見出しの間に挟む文字を変更することができる。
上記結果では、横方向に結合していますが、縦に結合したいときもあるかもしれません。
そういう場合は、下記の様に一次元配列にする前に転置行列とすることで実現することができます。
=FLATTEN(TRANSPOSE(ARRAYFORMULA(A2:A4&"-"&B1:F1)))
結果:
2. 見出しを分割
「月-科目」となってる見出しを「月」と「科目」に分割する。
その時の分割の区切りとして「月」と「科目」の間に挟んだ文字列を使用する。
=ARRAYFORMULA(REGEXEXTRACT(H2:H16,"(.*)-"))
// 又は
=ARRAYFORMULA(LEFT(H2:H16,FIND("-",H2:H16)-1))
=ARRAYFORMULA(REGEXEXTRACT(H2:H16,"-(.*)"))
// 又は
=ARRAYFORMULA(RIGHT(H2:H16,LEN(H2:H16)-FIND("-",H2:H16)))
【解説】
ARRAYFORMULA(配列数式) は上記と同様。
ここでは、H列の2行目〜16行目の値を数式に適用している。
J2に展開される数式 : REGEXEXTRACT(H2,"(.*)-") / LEFT(H2,FIND("-",H2)-1)
K2に展開される数式 : REGEXEXTRACT(H2,"-(.*)") / RIGHT(H2,LEN(H2)-FIND("-",H2))
■ 正規表現を使った方法
REGEXEXTRACT(テキスト, 正規表現)
引数のテキストから正規表現と一致する文字列を取り出す関数。
"(.*)-" : 「-」の前の任意の0文字以上の文字列。
"-(.*)" : 「-」の後の任意の0文字以上の文字列。
※ ()で括ることで、正規表現に一致する文字列中から()の中のみに対応する文字列を取り出すことが可能。
■ 指定文字を検索する方法
LEFT(文字列, [文字数])
指定した文字列の先頭から文字数分の部分文字列を取り出す関数。
RIGHT(文字列, [文字数])
指定した文字列の後ろから文字数分の部分文字列を取り出す関数。
「月-教科」の文字列に対して「月」の文字数を LEFT で指定することで、「月」の見出し、「教科」の文字数を RIGHT で指定することで「教科」の見出しを取得する。
FIND(検索文字列, 検索対象のテキスト, [開始位置])
検索対象のテキストから検索文字列が最初に表れる位置を返却する関数。開始位置で検索対象のテキストの検索開始位置を指定可能。
LEN(テキスト)
文字列の長さを返却する関数。
FIND("-","5月-英語") は 3 となり、その1文字前までが「月」の見出しとなるため、1を引いた文字数をLEFTで指定している。
LEN("5月-英語") は 5 となり、「-」の先頭からの位置(3)を引くと「教科」の文字数になるため、この値をRIGHTで指定している。
※ 区切り文字を複数文字で指定している場合は「区切り文字の文字数-1」分調整が必要。
3. 見出しに対応する値を入れる
最後に、見出しに対応する値を最初の表から取得する。
【方法1】 見出しを検索する
=ARRAYFORMULA(VLOOKUP(J2:J16,A2:F4,MATCH(K2:K16,A1:F1,0),FALSE))
【解説】
ARRAYFORMULA(配列数式) は上記と同様。
ここでは、J列とK列の2行目〜16行目の値を数式に適用している。
L2に展開される数式 : VLOOKUP(J2,A2:F4,MATCH(K2,A1:F1,0),FALSE)
VLOOKUP(検索キー, 範囲, 指数, [並べ替え済み])
範囲で指定した1列目から検索キーに一致するものを検索し、同じ行の指数で指定した列の値を返却する関数。範囲で指定した1列目がソート済みか否かを指定できる。今回は FALSE(並べ替えなし) を指定。
MATCH(検索キー, 範囲, [検索の種類])
範囲で指定した一次元配列の中から検索キーに一致するものを検索し、位置を返却する関数。開始は1から(0にはならない)。検索の種類を指定できる。今回は 0(完全一致) を指定。
VLOOKUP(J2,A2:F4,MATCH(K2,A1:F1,0),FALSE) は、J2の値(5月) を A2〜A4 の中から検索。その行から返却する列は、A1〜F1 が K2の値(英語) となる列。
MATCH(K2,A1:F1,0) は 2 となるので、VLOOKUP の引数の「指数」に指定する。
二次元の表から検索する方法として以下の方法もありますが、INDEX は ARRAYFORMULA 内で使用することができないため、今回は上記方法を紹介しました。
=INDEX(B2:F4,MATCH(J2,A2:A4),MATCH(K2,B1:F1))
各セルに数式を入れるのであればこれを使用することも可能です。
※ 見やすさの観点から $ は省略しているため、必要に応じて入れてください。
【方法2】 2次元配列を展開する
見出しが、上記の手順で作られたものと確約されている場合は以下の様にデータを対応させることも可能。
=FLATTEN(B2:F6)
FLATTEN関数は上記の見出し結合の時と同じ。
見出し結合とデータは1対1対応になるため、同じ様に1次元配列にすることで値を対応させることがでk
余談
そもそも、この変換をしたい時はどういう時かというお話。
変換前の表は人の目には見やすい代わりに、どうしても2つ以上の情報を与えることができません。
今回の例で言えば、各教科に対しての平均点があったり、目標点があったりします。
この様なとき、入力は見やすい二次元の表で、データの管理は変換して行うと良いでしょう。
変換後の表で管理をしていればフィルター機能も使えて検索もしやすくなります。
注意点としては、上記の表に対して「O列を追加し直接データを入力する」など、変換後の表に直接データを入れることは避けたいということ。
変換後の表は変換前の表に依存しているため、変換前の表を変更した際に変換後の表は全てのデータが連動して変更される様にするべきだと思います。
最後に
「スプレッドシート 二次元配列 一次元配列 変換」で検索してもなかなかこれを実現する方法が書いてあるところがなく色々と模索したのでメモとして残しました。
他にもこんないい方法があるよ〜とかあればコメントしていただければありがたいです。
紹介した数式には、複雑化を避けるため、空白セルを含んだ時の処理や見つからなかった時の処理などのエラー処理は入れていません。必要に応じて適宜追加ください。