Excelで質問です。連番に関してです。

質問

Excelで質問です。連番に関してです。

明日仕事で使うので急いでいます。

A 　1 　1
A 　1 　2
A 　1 　3
A 　2 　1
A 　2 　2
A 　2 　3
A 　2 　4
A 　2 　7
A 　2 　8
A 　3 　1
A 　3 　2
A 　3 　3
A 　3 　4
B 　1 　1
B 　1 　2
B 　1 　3
B 　2 　1
B 　2 　2
B 　2 　3

例えると上記のような感じのデータがあります。

Aというグループ内（左側の列）で
1というデータ（真ん中の列）に
1,2,3…という連番（右側の列）が
順番に振られています。
イメージとしてはA-1というキーに対して1,2,3...
という数字が連番としてそれぞれ振られている感じです。
※並び替えは昇順で済んでいます

目的としては右側の列である連番の途切れた部分に追加で番号を
2つ分入力し、
A-1-4
A-1-5
というキーが欲しいのです。
つまりが若い空き番から順に2つ使用するデータを拾うには
どうすれば良いか…ですね。

（例）1,2,3で途切れている場合は4,5と入力。

もしこれだけならば問題は無かったんです。

=IF(C2+1=C3,"","▲")

で、▲のついているところから途切れの箇所を拾えます。
しかし、他にもこんなパターンがあるのです。

（パターン1）1,2,3,8,9,10...
これで欲しいのは4,5。
上記のやり方だと必要ない11,12も拾ってしまいます。
※あくまで「左側の列-真ん中の列」というキーに対して
番号を割り振るのは2つまで。同じキー内に途切れが
二つある時点で上記のやり方が出来ません。

（パターン2）4,5,6...
これで欲しいのは1,2。
最初から抜けています。

（パターン3）1,2,3,5,6,10,11...
これで欲しいのは4,7
1つ空き番があって、次が離れています。

Excelの関数だけでどうにかできるかは分かりません。
マクロで解けるらしいですが、自分はマクロは使えません…。
あとAccessを活用して何とかできないか…とも思ってます。
※元々このデータはAccessからエクスポートしたデータです

数万行あるデータだけに手入力は絶対に無理です。
空き番に2つ数字を割り振る方法があれば教えていただけると助かります。
Accessを組み合わせての方法でも良いです。
ただし、当方AccessはExcel以上に無知なもので…。

よろしくお願いいたします。

回答No.4

_Kyle · ベストアンサー

作業列が多くなってしまいますが
手順を踏んでやれば数式だけでも。

■Sheet1を元データシートとし、A1からデータが入っているとします。

・作業列１：[グループ]と[データ]を結合
　D1：　=A1&"_"&B1
として下方にフィル（以下同様）

・作業列２：各ブロック最上行の行番号
　E1：　=IF(MATCH(D1,$D$1:D1,0)=ROW(D1),ROW(),"")

・作業列３：ブロック毎の行数
　F1：　=IF(E1="","",COUNTIF(D:D,D1))

・作業列４：有無をチェックすべき番号の範囲
　G1：　=IF(E1="","","1:"&F1+2)

・作業列５：空番号１
　H1：　=IF(E1="","",ROUND(1/LARGE(INDEX(ISNA(MATCH(ROW(INDIRECT(G1)),OFFSET(C1,,,F1),0))/ROW(INDIRECT(G1)),),1),))

・作業列６：空番号２
　I2：　=IF(E1="","",ROUND(1/LARGE(INDEX(ISNA(MATCH(ROW(INDIRECT(G1)),OFFSET(C1,,,F1),0))/ROW(INDIRECT(G1)),),2),))

■Sheet2を結果シートとします。

・作業列７：各ブロック最上行の行番号
　D1：　=SMALL(Sheet1!E:E,INT((ROW()-1)/2)+1)

・結果列１：[グループ]
　A1：　=INDEX(Sheet1!A:A,$D1)

・結果列２：[データ]
　B1：　=INDEX(Sheet1!B:B,$D1)

・結果列３：[連番]
　C1：　=INDEX(IF(MOD(ROW(),2),Sheet1!H:H,Sheet1!I:I),D1)

Excel2003で動作確認。以上ご参考まで。

お礼

こちらの回答者様の手順でやってみたところ、
自分が求めていた結果が反映されました！
おそらくこれで大丈夫かと思います。
関数を使うとこれだけややこしくなるんですね…
自分が想像していたより全然複雑でした。
ややこしい質問に答えていただいて
本当にありがとうございました！

回答No.9

DOUGLAS_

#7・8 DOUGLAS_ です。

＞明日仕事で使うので急いでいます。
　まだ、間に合いますかね？

　どうにか改善できたようですので、お知らせいたします。

　ただし、
・当該シート に見出しなどがあった場合には、新規シート に余分な データ が書き込まれます。
・C列に正の整数以外の データ（負の数、０、小数、文字列、など）があった場合の動作は未確認です。

　マクロ を実行すると、下のようになります。

'---------------------------------------------------------

【当該シート の データ】
A 1 1
A 1 2
A 1 3
A 2 1
A 2 2
A 2 3
A 2 4
A 2 7
A 2 8
A 3 1
A 3 2
A 3 3
A 3 4
B 1 1
B 1 2
B 1 3
B 2 1
B 2 2
B 2 3
B 3 1
B 3 2
B 3 3
B 3 8
B 3 9
B 3 10
C 1 4
C 1 5
C 1 6
C 2 1
C 2 2
C 2 3
C 2 5
C 2 6
C 2 10
C 2 11
【新規シート の データ】
A 1 4
A 1 5
A 2 5
A 2 6
A 3 5
A 3 6
B 1 4
B 1 5
B 2 4
B 2 5
B 3 4
B 3 5
C 1 1
C 1 2
C 2 4
C 2 7

'#7（２）の コードウィンドウ に下記の マクロ を コピペ してください。
'---------------------------------------------------------

Sub test()
　If Range("A1") = "" Then Exit Sub
'変数の宣言
　Dim newSh As Worksheet '書込用新規シート
　Dim myRow As Integer　 '調査中の行
　Dim strCnt As String　 '連番値文字列
　Dim myCnt As Integer　 '連番値
　Dim myArr As Variant　 '連番値の配列
　Dim newRow As Integer　'書込行数
　Dim newCnt As Integer　'新連番数

'書込用新規シートの作成
　Sheets.Add Type:="ワークシート"
　Set newSh = ActiveSheet
　ActiveSheet.Next.Select

'Ａ・Ｂ列のデータが変わるまで、Ｃ列の値を配列に格納する
　Do
　　Do
　　　myRow = myRow + 1
　　　strCnt = strCnt & " " & Cells(myRow, 3).Value
　　Loop Until Cells(myRow + 1, 1).Value <> Cells(myRow, 1).Value _
　　　Or Cells(myRow + 1, 2).Value <> Cells(myRow, 2).Value
　　myArr = Split(Trim(strCnt))

'配列の要素を「１」から数えて欠番が見付かった場合は
'２件のみ新規シートに書き込む
　　Do
　　　myCnt = myCnt + 1
　　　If UBound(Filter(myArr, CStr(myCnt), False)) = UBound(myArr) Then
　　　　newRow = newRow + 1
　　　　newSh.Cells(newRow, 1) = Cells(myRow, 1).Value
　　　　newSh.Cells(newRow, 2) = Cells(myRow, 2).Value
　　　　newSh.Cells(newRow, 3) = myCnt
　　　　newCnt = newCnt + 1
　　　End If
　　Loop Until newCnt = 2

'Ａ・Ｂ列のデータが変わったら、カウンタを初期化する
　　strCnt = ""
　　myCnt = 0
　　newCnt = 0
　　Erase myArr

'Ａ列が空白になったら、操作を中止する
　Loop Until Cells(myRow + 1, 1).Value = ""
　newSh.Activate
　Set newSh = Nothing
End Sub

回答No.8

DOUGLAS_

#7 DOUGLAS_ です。

　マクロ に バグ が見付かりました。
　１番が欠番になった場合に、不具合があるようですが、ややこしくて、直ぐに改善できそうにありませんので、前回答は取り下げます。

　スレッド を汚して申し訳ございません。
　大変、失礼いたしました。　　<(_ _)>

回答No.7

DOUGLAS_

#1 さんへの「補足」の３・４行目は
A 2 5
A 2 6
の間違いですよね。

＞マクロで解けるらしいですが、自分はマクロは使えません…。
とのことですが、マクロ を使った操作をご案内いたします。

　新規シート を作成し、#1 さんへの「補足」にお書きの結果を書き込むという マクロ です。
　念のため ダミー の ブック（当該ブック の コピー）を作成してお試しください。

１）当該ブック を アクティブ にして、[Alt] + [F11] で Visual Basic Editor の ウィンドウ を開きます。
　ウィンドウ の タイトルバー には [Microsoft Visual Basic - {ブック名}] と表示されています。

２）[挿入(I)] - [標準モジュール(M)] を クリック して現われた コードウィンドウ に下記の マクロ を コピペ します。

３）[Alt] - [F4] で Visual Basic Editor の ウィンドウ を閉じます。

４）データ の書き込まれた ワークシート を アクティブ にします。

５）[Alt] - [F8] で [マクロ] ダイアログ を開き、[マクロ名(M):] の テキストボックス の下の ボックス にある「test」を クリック すると、[マクロ名(M):] の テキストボックス に「test」と表示されますので、[実行(R)] を クリック します。

６）以上で、#1 さんへの「補足」にお書きの結果が書き込まれた 新規シート が現われます。

'以下の コード を（２）の コードウィンドウ に コピペ してください。
'---------------------------------------------------------
Sub test()
'変数の宣言
　Dim newSh As Worksheet '書込用新規シート
　Dim myRow As Integer　 '調査中の行
　Dim myCnt As Integer　 '連番値
　Dim newRow As Integer　'書込行数
　Dim newCnt As Integer　'新連番数

'書込用新規シートの作成
　Sheets.Add Type:="ワークシート"
　Set newSh = ActiveSheet
　ActiveSheet.Next.Select

'１行ずつデータを見る
　Do
　　Do
　　　myRow = myRow + 1
　　　myCnt = myCnt + 1

'「１」から数えて欠番が見付かった場合は
'新規シートに書き込む
　　　If Cells(myRow, 3).Value <> myCnt Then
　　　　newRow = newRow + 1
　　　　newSh.Cells(newRow, 1) = Cells(myRow, 1).Value
　　　　newSh.Cells(newRow, 2) = Cells(myRow, 2).Value
　　　　newSh.Cells(newRow, 3) = myCnt
　　　　newCnt = newCnt + 1
　　　End If

'Ａ・Ｂ列のデータが変わるまでスルーする
　　　If Cells(myRow + 1, 1).Value <> Cells(myRow, 1).Value _
　　　　Or Cells(myRow + 1, 2).Value <> Cells(myRow, 2).Value Then
　　　　myRow = myRow - 1
　　　End If
　　Loop Until newCnt = 2

'Ａ・Ｂ列のデータが変わったら、カウンタを初期化する
　　myCnt = 0
　　newCnt = 0
　　myRow = myRow + 1

'Ａ列が空白になったら、操作を中止する
　Loop Until Cells(myRow + 1, 1).Value = ""
　newSh.Activate
　Set newSh = Nothing
End Sub

お礼

現状、マクロを使えない自分なので回答者様の方法を
すぐに確認するのは難しいです。近々時間が空いた時に
マクロに触れてみて実際に確認してみたいと思います。
方法を記述していただいているにも係わらず
試してみる事ができずに本当に申し訳ありません。
しかしおそらくは難解な関数を組むよりも
マクロを組んだほうが効率が良いのでしょうね。
今回の回答内容は今後の参考にさせていただきます。
ご回答いただきありがとうございました！

回答No.6

web2525

最初に訂正：

D2の計算式は
=IF(AND(A2=A3,B2=B3),"",ROW()-1-SUM($D1:D$2))
に修正してください。

一応計算式の説明も

D列ではそれぞれのキーとなるデーターの個数を表示

E列ではキーとなるデーターごとの連番が表示されています

F列ではC列の連番とE列の連番を照らし合わせて抜けている番号を表示させています
（E列は単純に連番表示だけなので本来はF列の計算式にE列の計算式を組み込めばよかったのですが）

G/H　J/K列は最後にデーターの移動を行う際に利用するためだけの表示です

I列では抜け番が1つ以上あった場合抜け番の最小値を表示、抜け番がない場合はE列の連番＋1しています

L列では抜け番がない場合はI列に続く番号、2つ以上あった場合は抜け番の2番目に小さい数値、1つしかない場合はC列の最終番号により数値を判断しています

回答No.5

web2525

考え方の一つとして
D列以降を作業列とし、2行目からデーターがあると仮定
D2：
=IF(AND(A2=A3,B2=B3),"",ROW()-SUM($D$2:D2))
E2:
=IF(D1="",E1+1,1)
F2：
=IF(ISERROR(MATCH(E2,OFFSET(C2,,,-E2),0)),E2,"")

これで下方向にフィル
F列に
抜けている連番が表示されます

G2：
=IF($D2="","",$A2)
H2:
=IF($D2="","",$B2)
I2:
=IF($D2="","",CHOOSE((COUNT(OFFSET(F2,,,-E2))>0)+1,E2+1,SMALL(OFFSET($F2,,,-$D2),1)))
J2:
=IF($D2="","",$A2)
K2:
=IF($D2="","",$B2)
L2:
=IF($D2="","",CHOOSE((COUNT(OFFSET(F2,,,-E2))>0)+(COUNT(OFFSET(F2,,,-D2))>1)+1,I2+1,IF(C2-1=D2,C2+1,D2+1),SMALL(OFFSET($F2,,,-$D2),2)))

G~I列　J~L列　それぞれに求めたい連番表示されますので
それぞれの列をコピーしてA~C列のデーターの下の貼り付け
そのままソートすれば目的の連番込のデーターになるはずです

お礼

こちらの回答者様の方法で確認をしてみたところ、
自分の求めていたデータが出てきました！
別の回答者様の方法でデータは無事に完成できて
おりましたが、確認（二重チェック）の意味を込めて
方法を使わせていただきました。関数は奥が深いですね…。
複雑な要求に関わらず回答いただきありがとうございました！

回答No.3

mimeu

仮にＡ列とＢ列の組み合わせがキー、Ｃ列が連番、なおかつ
データは２行目以下に書かれている、として
簡単・低機能な方法の一案です。

条件は
(1) 一番上の連番が３以上
(2) キーが不連続のときは、最初の連番が３以上
(3) キーが連続しているときは、連番が２ヶ以上あいている
このどれかの条件が成立するときだけ、Ｄ列に "○" を表示する関数を使う。

キーを挿入したいときは、手作業で "○" を検索し、そこに２行挿入する。
という使い方をすればよいかと思います。

下の式をＤ２以下一番下までのセルにコピーします。
=IF(ROW(D2)=2,IF(C2>2,"○",""),IF(AND((A1=A2),(B1=B2)),IF((C2-C1)>1,"○",""),IF(C2>2,"○","")))

お礼

式を使ってみましたが思っていた結果にはなりませんでした。
自分の説明不足もあったと思います。申し訳ありません。
ややこしい質問にお答えいただいてありがとうございました！

回答No.2

mettee

かなり複雑な処理になりますね。プログラム組むしかないでしょう。

お礼

今回のいろんな回答者様の回答を拝見しても
自力には到底思いつかない程の難解な手順でした。
回答、ありがとうございました！

補足

やはりマクロなり使うしかなさそうでしょうか…。

回答No.1

hallo-2007

どの様な表示結果が必要なのかわかりませんが
Ａ　Ｂ　Ｃ　Ｄ　Ｅ
A 　1 　1
A 　1 　2
A 　1 　3
・・・・・
Ｄ列に　=A2&B2　下までコピィ
Ｅ列に　=IF(D2=D1,IF(C1+1=C2,"連番です","飛びました"),IF(C2=1,"1に戻りました","最初の番号も飛びました"))
この様なことでしょうか。

お礼

今回、いろいろと説明不足な点がありました。
補足説明ができたおかげで無事にデータを作成することが出来ました。
回答、ありがとうございました！

補足

自分が質問欄に例えたデータの場合

A 　1 　4
A 　1 　5
A 　2 　4
A 　2 　5
A 　3 　5
A 　3 　6
B 　1 　4
B 　1 　5
B 　2 　4
B 　2 　5

…最終的にはこんな感じの表示結果が欲しいわけです。

A列とB列のキーさえあればリンクされるデータがあるので、
C列はその同じデータに対して番号を割り振っているだけです。
ある意味D列やE列は必要ありません。
必要なのはA列B列C列の3列だけです。
完成した上記のようなデータを別のシートなりで保存するわけです。
そうすればリンクするデータをVLOOKなりで引っ張ってこれるので。

回答者様の関数を試してみましたが上手くいきませんでした。

知識不足＆上手く説明できなくて申し訳ありません。