Excelで1つのセルに並んだ数字から、同じ数字が2回目・4回目・6回目……と現れたときだけ削除したり、その偶数回目の数字だけを別セルへ取り出したりしたい場合があります。Microsoft 365なら、LET・MAP・SEQUENCE・MID・FILTER・TEXTJOINなどの動的配列関数を組み合わせることで、途中に離れて存在する同じ数字についても出現回数を数えて処理できます。
たとえばA1に「12324566007888890」がある場合、各数字について左から何回目に登場したかを数えると、「2」の2回目、「6」の2回目、「0」の2回目、「8」の2回目と4回目が偶数回目に該当します。これらを削除すると「123456078890」、反対に偶数回目だけを取り出すと「26088」になります。
この記事では、この処理をExcel 365の関数だけで実現する方法と、17桁以上の数字を扱う際に必ず知っておきたいExcelの桁数制限について解説します。
今回の「偶数個目」は文字位置の偶数番目ではなく同じ数字の出現回数
最初に処理の条件を整理しておくことが重要です。今回数えるのは、文字列全体の2文字目・4文字目・6文字目という意味の「偶数位置」ではありません。それぞれの数字について、左から見て何回目に登場したかを数えます。
「12324566007888890」を例にすると、数字の「2」は2文字目と4文字目にあります。最初の「2」は1回目なので残し、次の「2」は2回目なので偶数回目として取り除きます。
同様に「8」は途中に4個連続しています。1個目は残す、2個目は削除、3個目は残す、4個目は削除となります。この考え方をすべての数字に適用します。
| 数字 | 出現回数 | 処理 |
|---|---|---|
| 2 | 1回目 | 残す |
| 2 | 2回目 | 削除・偶数回目として抽出 |
| 6 | 1回目 | 残す |
| 6 | 2回目 | 削除・偶数回目として抽出 |
| 8 | 1回目 | 残す |
| 8 | 2回目 | 削除・偶数回目として抽出 |
| 8 | 3回目 | 残す |
| 8 | 4回目 | 削除・偶数回目として抽出 |
A2に「偶数回目を削除した結果」を表示する関数
A1に元の文字列が入っている場合、同じ数字の1回目・3回目・5回目……だけを残すには、A2へ次の式を入力します。
=LET(s,A1,x,MID(s,SEQUENCE(LEN(s)),1),n,MAP(SEQUENCE(LEN(s)),LAMBDA(i,COUNTIF(TAKE(x,i),INDEX(x,i)))),TEXTJOIN("",TRUE,FILTER(x,ISODD(n))))
A1が「12324566007888890」であれば、結果は次のようになります。
A2：123456078890
この式では、まずMID関数とSEQUENCE関数でA1の文字を1文字ずつ分解します。その後MAP関数で、それぞれの文字について「その位置までに同じ数字が何回出たか」をCOUNTIF関数で数えています。
最後にISODD関数で出現回数が奇数のものだけをFILTER関数で残し、TEXTJOIN関数で再び1本の文字列へ結合しています。「偶数回目を削除する」という処理は、言い換えれば「奇数回目だけ残す」処理なので、この式になります。
A3に「偶数回目の数字だけ」を取り出す関数
反対に、削除対象となった2回目・4回目・6回目……の数字だけをA3へ表示したい場合は、ISODDをISEVENへ変更します。
=LET(s,A1,x,MID(s,SEQUENCE(LEN(s)),1),n,MAP(SEQUENCE(LEN(s)),LAMBDA(i,COUNTIF(TAKE(x,i),INDEX(x,i)))),TEXTJOIN("",TRUE,FILTER(x,ISEVEN(n))))
同じ「12324566007888890」で計算すると、結果は次のとおりです。
A3：26088
内訳は、「2」の2回目が2、「6」の2回目が6、「0」の2回目が0、「8」の2回目と4回目が8と8なので、「26088」になります。
数式が何をしているのか順番に理解する
長い数式ですが、LET関数によって途中の計算結果へ名前を付けているため、処理を分解すると理解しやすくなります。
sにはA1の元データを入れています。続くxでは、MID(s,SEQUENCE(LEN(s)),1)によって文字列を1文字ずつ縦方向の配列に分解します。
たとえばA1が「1223」であれば、内部的には「1」「2」「2」「3」という4つの要素として扱われます。
次のnではMAP関数を使い、各文字について先頭から現在位置までに同じ文字が何個存在したかをCOUNTIFで求めています。「1」「2」「2」「3」なら出現回数は「1、1、2、1」となります。
最後にISODDなら1・3・5回目などの奇数回目、ISEVENなら2・4・6回目などの偶数回目だけを選びます。そしてTEXTJOINで選択された文字を区切り文字なしで連結しています。
重要：17桁の数字はExcelでは「文字列」として入力する
今回のようなデータを扱うときは、数式そのものより注意したいポイントがあります。例に挙げた「12324566007888890」は17桁ありますが、Excelが数値として正確に保持できる有効桁数は15桁までです。
そのため、17桁の数字を通常の数値としてセルへ入力すると、16桁目以降が正確に保持されない可能性があります。今回の処理では1文字ごとの数字そのものが重要なので、元データは数値ではなく文字列として保存する必要があります。
たとえばA1へ入力するときは、先頭にアポストロフィを付けて次のように入力します。
'12324566007888890
セル上では通常「12324566007888890」と表示されますが、Excel内部では文字列として扱われるため、17桁すべてをそのまま保持できます。
別の方法として、A1を入力する前にセルの表示形式を「文字列」に変更してから17桁のデータを入力しても構いません。
すでに17桁の数字を数値として入力していた場合は注意
Excelへ17桁以上の値をすでに「数値」として入力している場合、後からセルの表示形式を文字列へ変更するだけでは元の値に戻らないことがあります。Excelが15桁を超える部分をすでに変更して保存しているためです。
たとえば本来の末尾が「890」だったとしても、Excel内部で別の値へ丸められていれば、そのセルをTEXT関数などで文字列化しても失われた数字は復元されません。
したがって、元の17桁以上の番号が別の資料などに残っている場合は、A1を文字列形式にしてから元データを入力し直すのが確実です。
電話番号、会員番号、管理番号、JANコードなど、計算に使わない長い数字も同じ理由で文字列として管理するのが基本です。
連続していない重複にも対応できる
今回の式の利点は、同じ数字が隣り合っていなくても出現回数を数えられることです。単純に連続する重複だけを削除する式ではありません。
たとえば「121212」という文字列では、「1」は1・3・5文字目に3回、「2」は2・4・6文字目に3回登場しています。それぞれについて2回目だけが偶数回目になります。
そのため偶数回目を削除する式なら、1の1回目、2の1回目、1の3回目、2の3回目が残ります。このように、文字列全体を通して同じ文字の登場回数を管理できます。
「隣のセルと同じなら削除する」といった単純な重複除去とは処理内容が違うため、離れた位置の重複まで対象にしたい場合に適しています。
数字以外の文字列にも同じ式を使える
MID関数で1文字ずつ分解して処理しているため、この方法は数字だけに限定されません。アルファベットや日本語などの文字列でも、同じ文字の偶数回目・奇数回目を判定できます。
たとえば「ABACAB」のような文字列でも、A・B・Cそれぞれについて何回目の出現なのかを左から数え、偶数回目だけを除外または抽出できます。
したがって、コード文字列の整理や、同じ記号の1回おきの抽出などにも応用できます。
Microsoft 365だから使える関数が含まれている
今回紹介した式では、LET、SEQUENCE、MAP、TAKE、FILTERなどの比較的新しいExcel関数を使用しています。そのため、Microsoft 365の更新されたExcelで利用することを前提としています。
特にMAP関数は、配列の各要素に対してLAMBDAで指定した処理を実行できるため、「文字列の各位置について、それまでに同じ文字が何回登場したかを調べる」といった処理を1つの数式で実現しやすくなります。
古いExcelでは同じ結果を出すために補助列を使用したり、より複雑な数式を組み合わせたりする必要があります。Microsoft 365であれば、今回のように1セルの数式だけで結果を得られるのが大きな利点です。
まとめ：Excel 365ならMAPとCOUNTIFで同じ数字の偶数回目を判定できる
Excel 365で文字列中の同じ数字について2回目・4回目・6回目……だけを取り除きたい場合は、各文字の出現回数をMAPとCOUNTIFで数え、ISODDで奇数回目だけを残す方法が使えます。
A1が「12324566007888890」の場合、偶数回目を削除して「123456078890」にする式は=LET(s,A1,x,MID(s,SEQUENCE(LEN(s)),1),n,MAP(SEQUENCE(LEN(s)),LAMBDA(i,COUNTIF(TAKE(x,i),INDEX(x,i)))),TEXTJOIN("",TRUE,FILTER(x,ISODD(n))))です。
削除した偶数回目だけを「26088」として取り出す場合は、最後の判定をISEVENにして=LET(s,A1,x,MID(s,SEQUENCE(LEN(s)),1),n,MAP(SEQUENCE(LEN(s)),LAMBDA(i,COUNTIF(TAKE(x,i),INDEX(x,i)))),TEXTJOIN("",TRUE,FILTER(x,ISEVEN(n))))とします。
また、例の元データは17桁あるため、Excelへ数値として入力すると15桁を超える部分が正確に保持されない可能性があります。この種の長い数字列では、A1を文字列形式にしてから入力することが非常に重要です。この点まで押さえておけば、Excel 365の関数だけで目的の文字列を安全に生成できます。
コメント