こんにちは。テックブログの萱場です。
今回は、海外のZIPコードに関して、データ作成するときに便利だったSEQUENCE関数を紹介します。
課題
DHLの遠隔地扱いされるZIPコード一覧(PDF)よりデータリスト(アメリカ)を作成しようと思いました。PDFのエクセル化などは、アクロバットを使って行い、該当部分をスプシにコピペして、FLATTEN関数で1行にしました。
これで終われば、話は単純なのですが、PDFのリストには、以下のような表記も含まれていました。
このような、連番の省略形が3400行以上あったので、どうにかならないかと言うのが今回の課題です。
前準備
前準備としてSPLIT関数で、最初の数字と最後の数字を独立したセルに分割しました。
独立したセルにすると、文字列として認識されないので、先頭の0は消えますが、今回の場合、最終的にできたリストを5ケタの数字で0で桁埋めして、再度文字列化すれば良いので、無視します。
SEQUENCE関数を使用する
上記の画像でいうと、22行目のB列とC列にそれぞれ、連番の最初の数字と最後の数字を入力することができました。
ここでSEQUENCE関数を使用します。
書式
SEQUENCE(行数, [列数], [開始値], [増分量])
各パラメーターの説明
行数
関数で作成されるグリッドの行数です。
列数 – [任意]
関数で作成されるグリッドの列数です。省略するとグリッドは 1 列になります。
開始値 – [任意]
数列の開始値です。省略すると数列は 1 から始まります。
増分量 – [任意]
数列内の各値の増分量です。省略すると各値の増分量は 1 になります。
今回の場合、連番を横展開したいので、行は1を指定します。
列の数が C列の値 – B列の値 + 1 になります。
開始値はB列の値になります。
増分量は、今回の場合、省略可です。
上記を元に関数を入力すると以下の様になります。
D、E列に連番が生成されています。
こちらの例でも、D列から、H列まで連番が生成されているのがわかります。
こんな感じで連番生成できました。
応用 文字列を含む場合
アメリカのZIPCODEは、数字だけだったので、良かったですが、以下のカナダのZIPCODEは、また厄介でした。
アルファベットを含んでいます。SEQUENCE関数は、数字の連番を生成してくれるので、このままだとSEQUENCE関数は使えません。
解決法
前準備として、H列、I列にそれぞれ連番の始まりの値と終わりの値をSPLIT関数で、抽出しておきます。
ここで、注目するのは、連番に関して、下1ケタが数字の連番ということです。
つまり、空白を含む、左から6桁と、連番を分けて考えて後からくっつけるイメージです。そしてそのままだと、配列として連番展開されないので、ARRAYFORMULA関数のなかで上記のような関数を入力してあげると、うまく展開できそうです。
=ARRAYFORMULA(
LEFT(H54, LEN(H54) - 1)
&
SEQUENCE(1,RIGHT(I54,1)- RIGHT(H54,1) + 1,RIGHT(H54,1))
)
わかりやすいように改行しましたが、&の前が、連番共通部分、&の後が、下1ケタを抽出してSEQUENCE関数にいれています。
応用 アルファベットの連番の場合
以下の値はカナダのZIPCODEを少しアレンジしたもので、この様に、下1桁がアルファベットならどうでしょうか？
CHAR関数や、CODE関数という、文字をユニコード化してやることもできそうですが、最初と最後の値を参照して、連番を生成するのは難しそうです。
解決法 (試したらできた)
J58セルの数式
=ARRAYFORMULA(
REPLACE(
ARRAYFORMULA(LEFT(H58, LEN(H58) - 1) & SEQUENCE(1,HLOOKUP(RIGHT(I58,1),$J$61:$Q$62,2,FALSE)- HLOOKUP(RIGHT(H58,1),$J$61:$Q$62,2,false)+1,HLOOKUP(RIGHT(H58,1),$J$61:$Q$62,2,false))),
7,
1,
HLOOKUP(RIGHT(ARRAYFORMULA( SEQUENCE(1,HLOOKUP(RIGHT(I58,1),$J$61:$Q$62,2,FALSE)- HLOOKUP(RIGHT(H58,1),$J$61:$Q$62,2,false)+1,HLOOKUP(RIGHT(H58,1),$J$61:$Q$62,2,false))),1),$J$62:$Q$63,2,false)
)
)こんな式が長くなるなら、あまり使わないほうが良い気がしますが、1000行単位で、馴染のない連番を生成するような稀なケースがあったら使えると思います。
手順としては、
アルファベット、数字、アルファベットの相対表を作る。
HLOOKUP関数を使って、アルファベットを数字に置き換えた連番を生成する。
その連番の下1桁を、もう一度HLOOKUP関数を使って、数字からアルファベットに戻して展開する
ていう流れになります。
REPLACE関数の最後の引数である、置換後の値の部分が、ちょっとわかりにくい気がします。
最後に
最後までお読みいただきありがとうございました。
株式会社デザインXでは、アパレル業界のお客様に向けて、ECサイトや業務基幹システム(ERP)の開発・導入支援を行っております。ご興味をお持ちの方は、是非下記リンクよりお気軽にお問い合わせください。
また、株式会社デザインXでは、ソフトウェアエンジニア・社内SEなど、共に働く仲間を募集しています。テクノロジーでアパレル業界のBtoBビジネスを変えたいという熱意をお持ちの方、お待ちしております。ご興味のある方は、以下のリンクからお問い合わせください！
