こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

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

Microsoft Excelの質問です。

1~9までのある数値が
約5000行にわたって入力されている列があるのですが、
その中から





と連続しているパターンの部分のみ





というパターンに置換したいのですが、
どのようにしたら良いでしょうか?

投稿日時 - 2010-03-05 16:00:21

QNo.5727068

困ってます

質問者が選んだベストアンサー

A列に数値が並んでいるとします。
D1セルからD5セルには1,2,3,4,5を入力し、E1セルからE5セルには1,2,3,2,1と入力します。
B1セルには次の式を入力して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),"")

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

=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))))))

投稿日時 - 2010-03-05 17:40:20

このQ&Aは役に立ちましたか?

1人が「このQ&Aが役に立った」と投票しています

回答(5)

ANo.5

ぁれっ? 失礼...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 。という意図です。

投稿日時 - 2010-03-16 22:48:18

ANo.4

12345123412312345671234567891234512312111234567891234

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

12321122112112343211234543211232112111111234543211221
こういう結果?

『連続が途切れた場合に再び1から始まる』という条件があるなら、
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

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

投稿日時 - 2010-03-16 22:23:34

ANo.3

 今仮に、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列全体を選択
   ↓
マウスの右クリック
   ↓
選択肢の[形式を選択して貼り付け]をクリック
   ↓
[形式を選択して貼り付け]ウィンドウの「貼り付け」欄の[値]と、「演算」欄の[しない]にチェックを入れる
   ↓
[OK]ボタンをクリック

 これで、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

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

投稿日時 - 2010-03-06 03:52:48

お礼

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

投稿日時 - 2010-03-16 11:49:20

ANo.2

例えば

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
だったらマクロのほうが面倒ないんじゃないかと思います。

投稿日時 - 2010-03-05 22:52:24

お礼

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

12345123412312345671234567891234512312111234567891234

投稿日時 - 2010-03-16 11:48:26

あなたにオススメの質問