Excelで受け取ったデータの中には「1列にまとまっていると加工しやすいのに」というものがあったりします。マクロなどを使わずに2列に分かれているデータを交互に1列にまとめる方法を検討してみましょう。
目次
こんなケースってありませんか?
Excelで集計や加工などの作業をするにあたり、元データが使いやすい形になっていないことって結構ありませんでしょうか。
例えば、次のようなデータがあったとします。
このデータをみると
「なぜA列に1から20まで順番に並んでいないの」
とつい言ってしまいたくなります。
先日もこれに近いデータがあって、さてどうしたものかと。
20件くらいのデータなら大したことありませんが、実際にはもっと多くてとても手作業で対応できません。
「マクロを組もうか、それともパワークエリー使ったらなんとかなるんだろうか」などといろいろ考えていましたが、今回は関数でなんとかすることにしました。
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(範囲, 行番号, 列番号)
としてデータを取り出したい範囲と範囲内の行列番号を指定します。
範囲の指定は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に数式を作ってもらった後に私が後から付け加えたものであり、私自身がこのように考えて数式を作ったわけではありません。
従来であれば、必要な情報をどうやって入手できるかすべて自分のアタマで考えて数式に落とし込む必要がありました。
こうしたプロセス自体は勉強になりますし、スキルを磨く上でも大事ではあるのですが、どうしても時間や労力を必要とします。
これからはツールを上手に使うと、こうしたプロセスにかかる時間や労力を大幅に短縮することができます。
今までは関数などの使い方を調べて、数式に落とし込むという「書く力」が重要でしたが、今後は作ってもらった数式をきちんと読み解く「読む力」の方が重要になるのではないでしょうか。
もちろん作ってもらうために「適切な質問・依頼をする」という能力も前提として必要となります。
ツールを使いこなす上で必要とされる能力が大きく変わりそうな状況となっているという点は意識しておいた方がよいでしょう。
投稿者
-
大学卒業後、大手上場企業に入社し約19年間経理業務および経営管理業務を幅広く担当。
31歳のとき英国子会社に出向。その後チェコ・日本国内での勤務を経て、38歳のときスロバキア子会社に取締役として出向。30代のうち7年間を欧州で勤務。
40歳のときに会社を退職。その後3年で税理士資格を取得。
中小企業の経営者と数多く接する中で、業務効率化の支援だけではなく、経営者を総合的にサポートするコンサルティング能力の必要性を痛感し、「コンサル型税理士」(経営支援責任者)のスキルを習得。
現在はこのスキルを活かして、売上アップ支援から個人的な悩みの相談まで、幅広く経営者のお困りごとの解決に尽力中。
さらに、商工会議所での講師やWeb媒体を中心とした執筆活動など、税理士業務以外でも幅広く活動を行っている。
最新の投稿
- 税理士2024年12月5日税理士が提供する「経理」はカスタマイズ商品?
- AI2024年12月1日Gemini for Google Workspaceで現状何ができるか
- IT活用2024年11月28日CanvaでZoom用の背景画像を作成する
- 経理2024年11月24日経理の手順はシンプルに。「へらす」「まとめる」「デジタル化」