はじめに：その表、まっすぐにできますか？
Excelでデータを扱っていると、複数の列にまたがった表を、縦一列のシンプルなリストに変換したくなる時、ありませんか？
「あいうえお…」と「さしすせそ…」の2列のデータを、「あいうえお…さしすせそ…」という1列のリストにしたい。そんな場面です。
「え、そんなのコピペでやればいいじゃない？」
そう思ったあなた、その通り！
多くの場合、それが一番手っ取り早いかもしれません。（しかし、実務でそんな場面は皆無です・・・笑）
でも、ちょっと待ってください！
もし、元の表が変更されるたびに、リストも自動で更新されてほしいとしたら？
また、この変換を「数式」だけで、スマートに解決できたとしたら？
今回の記事は、この「2次元配列を1次元に変換する」という、配列操作の真髄とも言えるパズルに、8種類もの異なるアプローチで挑む、知的な冒険です。
最新の関数を使えば一瞬で解けるこの問題を、あえて古い関数だけで解いてみることで、Excelの進化と、その裏側にある普遍的なロジックの両方を体感していきますよ！
本記事では、無料のWeb版Excelを使用して検証および画像の作成を行っています。Windowsはもちろん、MacやLinuxの方もブラウザさえあれば挑戦できます！
準備：挑戦者となる配列を用意する
何事も、まずは準備から。まっさらなシートのA1セルを起点に、10行2列のひらがなの表を作成してください。
必要な方は、以下の数式をA1セルに貼り付けてくださいね！
={"あ","さ";"い","し";"う","す";"え","せ";"お","そ";"か","た";"き","ち";"く","つ";"け","て";"こ","と"}
私たちのゴールは、このA1:B10の範囲を、数式を使って20行1列のリストに変換することです。
さあ、準備はできましたか？
Part 1：現代の魔法 – 新関数なら、こんなに簡単！
まずは、現代のExcelがいかにパワフルかを見ていきましょう。最新の動的配列関数を使えば、この問題は驚くほど簡単に解決してしまいます。
アプローチ1：TOCOL – まさに、このための関数
考えかた
「配列を1列（Column）にする」…もう、そのまんまの名前の関数が、実は用意されています。
それがTOCOL関数です。
数式と解説
=TOCOL(A1:B10,0,TRUE)
この数式を一つのセルに入力するだけで、答えがスピルして一気に表示されます！
・TOCOL(A1:B10, ...): 第1引数に、変換したい配列を指定します。
・..., 0, ...: 第2引数は、空白やエラーを無視するかどうかのオプションです。「0」はすべてを保持します。
・..., TRUE): 第3引数が重要！これは「スキャン方向」を指定します。
TRUEにすると「列単位」でスキャンします。つまり、まずA列を上から下まで読み込み、次にB列を上から下まで読み込んで連結します。
もしここをFALSE（または省略）にすると、「行単位」（A1, B1, A2, B2…）でスキャンします。
アプローチ2：VSTACK – 縦に積む、という発想
考えかた
これも非常にシンプルな発想です。
A列（A1:A10）とB列（B1:B10）を、単純に「縦方向（Vertically）」にガチャンと連結すればいいじゃないか！
れを実現するのがVSTACK関数です。
数式と解説
=VSTACK(A1:A10,B1:B10)
これ以上ないほど、やっていることが見たままの数式ですね。
A1:A10の下に、B1:B10が連結され、20行1列の配列が完成します。
アプローチ3：TEXTSPLIT – 文字列操作の合わせ技
考えかた
「文字列で考えればいいんじゃね？」という、関数好きおなじみの発想です。
一度すべての文字を特殊な記号で連結し、最後にその記号で分割し直す、というアクロバティックな方法です。
数式と解説
=TRANSPOSE(TEXTSPLIT(TEXTJOIN("§",TRUE,TRANSPOSE(A1:B10)),"§",,TRUE))
1. TRANSPOSE(A1:B10): まず、10×2の配列を2×10の配列にひっくり返します。
2. TEXTJOIN("§",TRUE,...): 次に、各行を「§」で連結します。結果、"あ§い§...§こ" と "さ§し§...§と" という2つの文字列ができます。
3. TEXTSPLIT(...,"§",,TRUE): この2つの文字列を、今度は「§」で分割します。これにより、20個のひらがなが1行に並んだ配列ができます。
4. TRANSPOSE(...): 最後に、もう一度TRANSPOSEでひっくり返し、1列のリストに戻します。
Part 2：いにしえの技 – 動的配列、禁止！
さて、ここからが本番です！
「新関数を使えばいいじゃない？なぜ、わざわざ古い関数で苦労するの？」
それは、ロジックの本質を理解するためです。
便利な新関数は、時としてその内部処理が「ブラックボックス」になりがちです。
ブラックボックス化するということは、つまり「なぜそうなるのか」という中身をよく理解しないまま、魔法の杖のように使ってしまうということです。
これでは、少しでも想定外の問題に直面したときに、応用が利きません。しかし、基本的な関数を組み合わせることで、「なぜ、これで答えが求まるのか？」その根源を知ることができます。
その知識こそが、真の応用力を鍛える原動力になるのです！
さあ、ここからは動的配列関数を禁止して、この難題に挑みましょう！
アプローチ4：INDEX + SEQUENCE – 配列計算の基本形
【使用禁止関数】
TOCOL, VSTACK, TEXTSPLITなどの最新の動的配列関数
考えかた
ここからは、動的配列関数の多くを禁止しますが、その中でも比較的早い段階で提供され、買い切り版のExcel 2021でも使用可能なSEQUENCE関数だけは、特別に使用を許可します！
1番目から20番目までのリストを作るために、元の2次元配列の「何行目、何列目」を参照すればよいか、その対応関係を数式で計算します。これぞ配列操作の王道です。
数式と解説
=INDEX(A1:B10,MOD(SEQUENCE(20)-1,10)+1,INT((SEQUENCE(20)-1)/10)+1)
・SEQUENCE(20): まず、1から20までの連番配列を作ります。（今回は分かりやすさ重視で20と直接入力しています！）
・MOD(SEQUENCE(20)-1,10)+1: これが、参照すべき「行番号」を計算している部分です。SEQUENCE(20)-1で0〜19の配列を作り、それを10で割った「余り」に1を足すと、{1,2,...,10,1,2,...,10}という、行番号の配列が生まれます。
・INT((SEQUENCE(20)-1)/10)+1: こちらが「列番号」です。0〜19の配列を10で割った商の「整数部分」に1を足すと、{1,1,...,1,2,2,...,2}という、列番号の配列が生まれます。
・INDEX(A1:B10, ..., ...): 最後に、INDEX関数が、計算された行番号と列番号の配列に従って、元の表から値を一つずつ取り出し、1列のリストを再構築してくれます。
アプローチ5：INDEX + ROW + INDIRECT – SEQUENCE禁止令
【使用禁止関数】
TOCOL, VSTACK, TEXTSPLIT, SEQUENCEなどのExcel 2021以降の全ての新関数
考えかた
アプローチ4とロジックは全く同じですが、SEQUENCE関数さえも禁止された場合、ROW(INDIRECT(...))という古典的なテクニックで連番を生成します。
数式と解説
=INDEX(A1:B10,MOD(ROW(INDIRECT("A1:A"&ROWS(A1:B10)*COLUMNS(A1:B10)))-1,ROWS(A1:B10))+1,INT((ROW(INDIRECT("A1:A"&ROWS(A1:B10)*COLUMNS(A1:B10)))-1)/ROWS(A1:B10))+1)
・ROW(INDIRECT("A1:A"&ROWS(A1:B10)*COLUMNS(A1:B10))): この長い部分が、SEQUENCE(20)の代わりです。
ROWS*COLUMNSで合計セル数「20」を計算し、INDIRECT("A1:A20")でA1:A20という参照を作り、その行番号を取得することで、{1;2;…;20}という連番を生成しています。
古いExcelでは、この数式をCtrl+Shift+Enterで確定（CSE確定）する必要がありました。
アプローチ6：OFFSET – INDEX禁止！フィルコピーの技
【使用禁止関数】
Excel 2021以降の全ての関数, INDEX
考えかた
ここからは、スピルで一発表示も難しくなります。
数式を一つ作り、それを下までフィルコピーする方法で挑戦です。
INDEX関数が禁止されたなら、OFFSET関数の出番です。
数式と解説
C1セルに以下の数式を入力し、C20セルまでフィルコピーします。
=OFFSET($A$1,MOD( ROW(A1)-1, 10),INT((ROW(A1)-1)/10),1,1)
・ROW(A1)-1: フィルコピーすることで、0, 1, 2, …と変化するカウンターの役割を果たします。
Excelの説明画像
・MOD(..., 10): 参照すべき「行の移動量」を計算します。
・INT(.../10): 参照すべき「列の移動量」を計算します。
・OFFSET($A$1, ...): OFFSET関数が、A1セルを基準に、計算された行と列の移動量だけ動いた場所の値を、一つずつ返していきます。
アプローチ7：INDIRECT + ADDRESS – OFFSET禁止！
【使用禁止関数】
Excel 2021以降の全ての関数, INDEX, OFFSET
考えかた
OFFSETも禁止！となると、セルの位置を直接指定するしかありません。
目的のセルの「行番号」と「列番号」を計算し、それをADDRESS関数でセル番地の文字列に変え、INDIRECT関数で値を抜き出します。
数式と解説
C1セルに以下の数式を入力し、C20セルまでフィルコピーします。
=INDIRECT(ADDRESS(MOD(ROW(A1)-1,10)+1,INT((ROW(A1)-1)/10)+1))
・MOD(...)+1 と INT(...)+1: これらは、アプローチ4と同じロジックで、目的のセルの「絶対的な行番号と列番号」を計算しています。
・ADDRESS(...): 計算された行番号と列番号から、「$A$1」や「$B$5」のような、セル番地の文字列を生成します。
・INDIRECT(...): 最後に、INDIRECT関数が、生成されたセル番地の文字列を本物のセル参照に変換し、その中の値を表示させます。
アプローチ8：CONCAT + IF – 究極の原始的手法
【使用禁止関数】
Excel 2021以降の全ての関数, INDEX, OFFSET, INDIRECT, ADDRESS
考えかた
最後の砦です。位置を計算する関数も禁止！
ならば、元の範囲全体に対して「今欲しいのは、この場所の値だけだ！」という条件を突きつけ、一致したものだけを残す、という非常に汎用性の高い方法です。
数式と解説
C1セルに以下の数式を入力し、C20セルまでフィルコピーします。
=CONCAT(IF((ROW($A$1:$B$10)=MOD(ROW(A1)-1,10)+1)*(COLUMN($A$1:$B$10)=INT((ROW(A1)-1)/10)+1),$A$1:$B$10,""))
・(ROW($A$1:$B$10)=...) * (COLUMN($A$1:$B$10)=...): この部分で、「行番号がこれ、かつ、列番号がこれ」という条件に合う場所だけが「1」になる、10×2の条件配列を作ります。
・IF(...,$A$1:$B$10,""): 条件配列が1の場所だけ、元の表の値を残し、他はすべて空白にします。
・CONCAT(...): 最後にCONCAT関数が、この値1個と空白19個の配列を連結します。空白は無視されるので、結果として欲しかった値だけが残る、というわけです。
まとめ：回り道こそ、最大の近道だ！
TOCOL関数なら一瞬で終わる問題を、あえて8つもの回り道で解いてみました。
しかし、その回り道の途中で、MODとINTで座標を計算するロジックや、配列を合体させる様々なテクニック、そしてExcel関数の進化の歴史に触れることができたのではないでしょうか。
一見、無駄に見える「回り道」こそが、実は関数の本質的な理解へとつながる、最大の近道なのかもしれません。
今回の関数パズルが、皆さんの「配列脳」をさらに鍛え、Excelの持つ無限の可能性を感じるきっかけになれば、これほど嬉しいことはありません。
