Excelで2列にまたがったデータを交互に1列にまとめる方法

Excelで受け取ったデータの中には「1列にまとまっていると加工しやすいのに」というものがあったりします。マクロなどを使わずに2列に分かれているデータを交互に1列にまとめる方法を検討してみましょう。

1 こんなケースってありませんか？
2 2列のデータを交互に1列にまとめる方法
3 タネ明かしをすると･･･

こんなケースってありませんか？

Excelで集計や加工などの作業をするにあたり、元データが使いやすい形になっていないことって結構ありませんでしょうか。

例えば、次のようなデータがあったとします。

このデータをみると

「なぜA列に1から20まで順番に並んでいないの」

とつい言ってしまいたくなります。

先日もこれに近いデータがあって、さてどうしたものかと。

20件くらいのデータなら大したことありませんが、実際にはもっと多くてとても手作業で対応できません。

「マクロを組もうか、それともパワークエリー使ったらなんとかなるんだろうか」などといろいろ考えていましたが、今回は関数でなんとかすることにしました。

■加藤博己のプロフィール

■経営者のためのZEI-Kメルマガ(登録無料)

■スポット相談のご依頼(ブログ記事に関する相談も可能です)

■執筆・講演のお問い合わせ(ブログ記事に関連する内容も対応します)

2列のデータを交互に1列にまとめる方法

結論から言うと、今回はC列の1～20行目に次のような式を入れて解決しました。

=INDEX(A:B,ROUNDUP(ROW()/2,0),MOD(ROW()-1,2)+1)

式を入れた後の状態が下図ですが、C列に1～20の数字が並んでいることが確認できます。

この式を作るまでの考え方を解説します。

INDEX関数とは

A列とB列から数字を順番に抜き出すときに数式を入力するC列に必要となるセルの情報は

1行1列
1行2列
2行1列
2行2列
3行1列
3行2列
･･･

となります。

指定した範囲から行列を指定して取り出すための関数としてINDEX関数があります。

INDEX 関数

使い方をザックリと説明すると

INDEX(範囲, 行番号, 列番号)

としてデータを取り出したい範囲と範囲内の行列番号を指定します。

範囲の指定はA列とB列を指定するので A:B とするだけですが、問題は行と列をどうやって指定するかです。

行番号をどうやって指定するか

行番号ですが、必要な数字としては

1, 1, 2, 2, 3, 3,･･･

と同じ数字を2回繰り返しています。

C列に式を入れるときに使えそうなのは、式の入っている行番号とC列という情報くらいです。

このうち列番号はC列(3列目)で固定されていますので、ここから欲しい数字を作るのは難しそう。

ということで行番号を使って何かできないか考えます。

試しに行番号を2で割ると

0.5, 1, 1.5, 2, 2.5, 3･･･

となります。この数字って切上処理をすると

1, 1, 2, 2, 3, 3,･･･

となり求めている数字になりました。

行番号を2で割って切上処理をしますので、Index関数の行番号を指定する欄に

ROUNDUP(ROW()/2,0)

と入力します。

ちなみにROW関数は式が入っている行番号を取り出す関数です。

列番号をどうやって指定するか

列番号として必要な数字は、行番号とは違って

1, 2, 1, 2, 1, 2,･･･

と1と2が交互に現れる必要があります。

こうしたケースでよく使われるのが「余り」を利用する方法です。

それぞれの行番号を2で割った余りは

1, 0, 1, 0, 1, 0,･･･

となり、残念ながら欲しい数字と少し違います。

余りに1を足しても

2, 1, 2, 1, 2, 1,･･･

となり並び方が欲しい数字とは逆です。

この順番を逆にしようとするならば、割った余りが

0, 1, 0, 1, 0, 1,･･･

となるような式を作ってそこに1を足せばよいわけです。

1行目を2で割った余りを0にするには、1行目を0行目にしてしまえばいい。

2行目も1行目になれば2で割った余りは1になります。

つまり

式が入っている行の数字から1を引く(ROW()-1)
引いた後の数字を2で割って余りを求める(MOD関数を使えば余りを計算できる)
余りに1を足す

という式を入れれば

1, 2, 1, 2, 1, 2,･･･

という数字を出せます。

そこでIndex関数の列を指定する欄に

MOD(ROW()-1,2)+1

と入力すると、今回の数式は完成です。

タネ明かしをすると･･･

今回はExcelの2列に分かれているデータを交互に並べて1列にまとめる方法を紹介しました。

実は今回の数式ですが、私が考えたものではなくChatGPTに質問して作ってもらったものです。

今回の記事における解説部分は、ChatGPTに数式を作ってもらった後に私が後から付け加えたものであり、私自身がこのように考えて数式を作ったわけではありません。

従来であれば、必要な情報をどうやって入手できるかすべて自分のアタマで考えて数式に落とし込む必要がありました。

こうしたプロセス自体は勉強になりますし、スキルを磨く上でも大事ではあるのですが、どうしても時間や労力を必要とします。

これからはツールを上手に使うと、こうしたプロセスにかかる時間や労力を大幅に短縮することができます。

今までは関数などの使い方を調べて、数式に落とし込むという「書く力」が重要でしたが、今後は作ってもらった数式をきちんと読み解く「読む力」の方が重要になるのではないでしょうか。

もちろん作ってもらうために「適切な質問・依頼をする」という能力も前提として必要となります。

ツールを使いこなす上で必要とされる能力が大きく変わりそうな状況となっているという点は意識しておいた方がよいでしょう。