excelで複数行単位で置換する

質問

Microsoft Excelの質問です。

１～９までのある数値が
約５０００行にわたって入力されている列があるのですが、
その中から
１
２
３
４
５
と連続しているパターンの部分のみ
１
２
３
２
１
というパターンに置換したいのですが、
どのようにしたら良いでしょうか？

回答No.1

KURUMITO · ベストアンサー

Ａ列に数値が並んでいるとします。
Ｄ1セルからＤ5セルには1，2，3，4，5を入力し、Ｅ1セルからＥ5セルには1，2，3，2，1と入力します。
Ｂ1セルには次の式を入力してCtrl+Shift+Enterキーで確定します。

=IF(OFFSET(A1,0,0):OFFSET(A1,4,0)=OFFSET(D$1,0,0):OFFSET(D$1,4,0),OFFSET(E$1,0,0):OFFSET(E$1,4,0),"")

置換をした答えの列の表示はＣ列に行うこととして、Ｃ1セルには次の式を入力して下方にオートフィルドラッグします。

=IF(A1="","",IF(INDIRECT("B"&ROW(A1))=1,E$1,IF(INDIRECT("B"&ROW(A1)-1)=1,E$2,IF(INDIRECT("B"&ROW(A1)-2)=1,E$3,IF(INDIRECT("B"&ROW(A1)-3)=1,E$4,IF(INDIRECT("B"&ROW(A1)-4)=1,E$5,A1))))))

回答No.5

end-u

ぁれっ？　失礼...orz

＞=IF(A2+1<3,1,IF(A1<=B2,A1,B2+1))
『A2+1<3』って何...って突っ込まれますね。

=IF(A2<2,1,IF(A1<=B2,A1,B2+1))

でいいです。
左ななめ下のセルが 1 か 空白 だったら 1 。という意図です。

回答No.4

end-u

12345123412312345671234567891234512312111234567891234

このデータはどんな結果が正解なのでしょう。
『1から始まる連続数値があった場合、最後が1で終わるように途中で（折り返すように）漸減させる』
というのが条件ですか？
1,2
の場合は 1,1 で良いのでしょうか。
それを踏まえると

12321122112112343211234543211232112111111234543211221
こういう結果？

『連続が途切れた場合に再び１から始まる』という条件があるなら、
A1:A5000にデータがあり、B1:B5000に数式で結果を出す場合

=IF(A2+1<3,1,IF(A1<=B2,A1,B2+1))

でいいはずです。

マクロでA1:A5000をそのまま書き換えるなら

Sub test()
　　Dim v As Variant
　　Dim x As Long
　　Dim n As Long
　　Dim i As Long
　　Dim j As Long

　　On Error GoTo Err_Handler
　　With ActiveSheet
　　　　With .Range("A1", .Range("A1").End(xlDown))
　　　　　　v = .Value
　　　　　　i = UBound(v)
　　　　　　Do Until i <= 1
　　　　　　　　n = i - v(i, 1)
　　　　　　　　x = v(i, 1) \ 2
　　　　　　　　For j = 1 To x
　　　　　　　　　　v(i, 1) = j
　　　　　　　　　　i = i - 1
　　　　　　　　Next
　　　　　　　　i = n
　　　　　　Loop
　　　　　　'.ClearContents
　　　　　　.Value = v
　　　　End With
　　End With
　　Exit Sub
Err_Handler:
　　MsgBox Err().Number & " : " & Err().Description
End Sub

こんな感じです。
試す場合はコピーしたテストブックで試してください。

回答No.3

kagakusuki

今仮に、A1～A5000にかけて数値が入力されていて、置換後の数値をB列に表示する事とします。
　まず、B1セルに

=$A1

と入力した後、B1をコピーして、B2セルとB3セルに貼り付けて下さい。
　次に、B4セルに

=IF($A1&$A2&$A3&$A4&$A5="12345",2,$A4)

と入力して下さい。
　そしてA5セルに

=IF($A2&$A3&$A4&$A5$&A6="12345",2,IF($A1&$A2&$A3&$A4&$A5="12345",1,$A5))

と入力して下さい。
　最後に、B5セルをB6～B5000セルにコピーして下さい。

　これで、B列に置換後の数値が表示されます。
　もし、A列のデータを書き換えたい場合には、
　まず、A列中のA1～A5000以外のセルの内、データや何らかの設定が有る全てセルをコピーして、B列の同じ行のセルに貼り付けて下さい。（この作業が必要な場合には、上記の数式を入力するよりも先に、前もってシートの上にある列番号欄のAをクリックして、A列全体を選択した後、コピー&ペースト又はオートフィル機能を使って、B列にA列全体の内容をコピーしておいた方が、作業が楽になると思います。）
　次に、B列全体をコピーして下さい。
　その次に、
A列全体を選択
　　　↓
マウスの右クリック
　　　↓
選択肢の[形式を選択して貼り付け]をクリック
　　　↓
[形式を選択して貼り付け]ウィンドウの「貼り付け」欄の［値］と、「演算」欄の［しない］にチェックを入れる
　　　↓
［ＯＫ］ボタンをクリック

　これで、A列のデータが置換後の数値に、書き換わります。
　但し、この方法で列全体を、形式を選択して貼り付けますと、A列の中に数式が入力されていた場合には、その数式が消去されてしまいます。
　ですから、A列に数式が入力されている場合には、必要な箇所のセルのみを、コピーして貼り付けて下さい。

　ところで、もしも元の数値の中に、

1
2
3
4
5
2
3
4
5

という順番で、数値が並んでいた場合には、置換後の数値は

1
2
3
2
1
2
3
4
5

となってしまい、途中に

1
2
3
4
5

というパターンが現れますが、この場合には、どの様に処理すれば良いのでしょうか。
　B列の値のみを、A列にコピーすれば、B列に再度置換が繰り返された数値が表示されますが、今度は元の数値が

1
2
3
4
5
2
3
4
5
2
3
4
5

という順番で、数値が並んでいた部分が、

1
2
3
2
1
2
3
2
1
2
3
4
5

となってしまいますから、切りが無くなってしまうと思います。

お礼

回答ありがとうございます。
１つ前の方のレスでも申し上げましたが
そのようなことにはならないと考えています。

回答No.2

end-u

例えば

1,2,3,4,5,6
と連続していたら
1,2,3,3,2,1

1,2,3,4,5,6,7,8,9
は
1,2,3,4,5,4,3,2,1

になったりするんですか？

1,2,3,4,5
のパターンのみだったら
A1:A5000にあるとして、B4から始めて
=IF(AND(A3=4,B3=2),1,IF(A1&A2&A3&A4&A5="12345",2,A4))
で良いような気もしますが、ひょっとして

1,2,3,4,5,2,3,4,5
は
1,2,3,2,1,2,3,2,1
のように前方置換後の数値も置換条件になったりします？

その場合はB1に=A1と入れてB3までコピー。B4から
=IF(AND(A3=4,B3=2),1,IF(B1&A2&A3&A4&A5="12345",2,A4))
かな？

もし
>1,2,3,4,5,6,7,8,9
>は
>1,2,3,4,5,4,3,2,1
だったらマクロのほうが面倒ないんじゃないかと思います。

お礼

説明不足でしたが、その通りです。
数字はかならず連続しており、
連続が途切れた場合に再び１から始まるという性質を持っています。
ですので、生データはこのような数値です。
例
12345123412312345671234567891234512312111234567891234