空白セルの次の値を取得したい

質問

エクセルの関数に関して質問いたします。

ある行に数字が複数並んでいるとします
その中には数字と空白がランダムにあるとします

例）
12　5　空白　空白　空白　11　空白　13
このような並びがあったとして1列目と2列目の12と5は普通に計算できますが、2列目と6列目、6列目と8列目の　5と11、11と13は計算できないという状態です
足し算だとしたら、12＋5＝17、5+11=16、11+13=24
というような、結果を得たいのです。
空白を飛び越えて計算できるような関数、または方法がありましたら
教えていただきたいです、よろしくお願いします。

回答No.5

SI299792 · ベストアンサー

B2: =IF(B1="","",LOOKUP(9E+99,$A1:A1)+B1)
右へコピペ。
のミスです。

お礼

SI299792さん、ご回答ありがとうございます！
求めていた結果が得られました
他の方にもご指摘受けましたが
画像で質問の状況と結果の状況をＵＰすべきでした
改めて、ありがとうございました！

回答No.12

imogasi

#8には、関数式での回答がでていますね。考えればできるのですね。
質問者は、その式の中の部分で何を捕まえて、どうしているか説明できますか？
勉強された方なら、比較的短い時間内に、式を作れるのでしょう。
小生の場合はＶＢＡでやる方が普通は早いです。こちらも各行などで、何をしているか判らないかもしれませんが、それで関数もVBAもその点で、同じことかもしれません。
それはＶＢＡでは、中間でデータを保存する「変数」というモノに、一旦データを保持できる（そののちその変数を使って処理を考えられる、からです。
ーー
ＶＢＡでやって見たのを挙げてみます。参考にならないようですが。
他のＶＢＡのご回答と似ているかもしれません。
下記で、質問の問題内容の取り違え部分が無いか、自信はないです。
質問には、普通はデータ例と結果を少数でも複数挙げるべきです。
（１）質問には結果をどこ（シートのどこ）に出すにか
（２）２件ずつ足し算するようですが、最後などで、１件しかないケースはどうするのでしょう。
質問に書いてありますか？必要はないですか？
データの、こういうこと（事項、特殊ケースや限界事項）をはっきり認識することこそ、大切と思うます。関数、ＶＢＡでやるにかかわりなく、他人にや江らせるときは特に。
ーーー
Sub test01()
Dim d(20), dn(20):
For i = 1 To 2
n = 1
Erase d, dn
ec = Cells(i, 200).End(xlToLeft).Column
MsgBox "最右列" & ec
For j = 1 To ec
If Cells(i, j) <> "" And IsNumeric(Cells(i, j)) Then
dn(n) = Cells(i, j)
n = n + 1
End If
Next j
If (n - 1) Mod 2 <> 0 Then
MsgBox i & "行目ペア相手無しあり"
ep = n - 2
Else
ep = n - 1
End If
MsgBox "最終列数" & ep
For k = 1 To ep - 1 Step 2
MsgBox dn(k) & "+" & dn(k + 1) & "=" & dn(k) + dn(k + 1)
Next k
Next i
End Sub
VBAでも昨日は別のやり方をこだわったため、数時間かかりました。
どんな」問題でも、やり方が２－３通り以上あるということです。
本件のやり方も、数時間かかりました。
データのあり場所が浮動的な場合は、関数もVBAでも、何かと難しいです。

お礼

imogasiさん、再度のご回答ありがとうございます！

他の方のご回答いただいてから、自分でも気づきましたが
確かに質問の状況と得たい結果の状況を画像でＵＰすべきでした。
わかりにくい質問にも真摯にご回答いただいて助かります

回答No.11

NuboChan

.UsedRangeで範囲限定、
コード修正

Set ws4 = Worksheets("cal")
’－－－－－－－－－－－－－－－－変更 （ここから）
'データがある全てのセル範囲
x = ws1.UsedRange.Columns(ws1.UsedRange.Columns.Count).Column '最大列番号
y = ws1.UsedRange.Rows(ws1.UsedRange.Rows.Count).Row '最大行番号

’－－－－－－－－－－－－－－（ここまで）
ws2.Cells.Clear

回答No.10

kkkkkm

No.7の2，3，4を非表示にする卑怯な手をなくして、愚鈍なマクロでしたら

結果はNo.7の2，3，4行にデータが無い状態になります。

Sub Test()
Dim x As Long, y As Long, t As Long, i As Long
Dim LastColumn As Long, mRow As Long

With Sheets("Sheet1") 'Sheet1の場合
LastColumn = .Cells(1, Columns.Count).End(xlToLeft).Column
x = 0: mRow = 6
For i = 1 To LastColumn
If .Cells(1, i).Value <> "" Then
y = .Cells(1, i).Value
t = x + y
If x <> 0 Then
.Cells(mRow, "B").Value = x & "+" & y & "="
.Cells(mRow, "C").Value = t
mRow = mRow + 1
End If
x = y
End If
Next
End With
End Sub

回答No.9

NuboChan

複数行対応改良版です。

処理する、最大行と最大列は適当に決めています。
必要なら変更するマクロを追加してください。

達人さんなら仮シートなど使わずに配列で処理するなどもっと高速化の手法が有るでしょう

Option Explicit

Sub test02()
Dim x As Long, y As Long
Dim i As Long, ii As Long, k As Long

Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet, ws4 As Worksheet

Set ws1 = Worksheets("DATA")
Set ws2 = Worksheets("separate")
Set ws3 = Worksheets("join")
Set ws4 = Worksheets("cal")

x = 100 '列適当
y = 100 '行適当

ws2.Cells.Clear
ws3.Cells.Clear
ws4.Cells.Clear

Application.ScreenUpdating = False

For i = 1 To y
ws1.Rows(i).Copy ws2.Range("A1")
For ii = x To 1 Step -1
If Application.CountA(ws2.Columns(ii)) = 0 Then ws2.Columns(ii).Delete
Next
ws2.Rows(1).Copy ws3.Rows(i)
ws2.Rows(1).ClearContents
Next

y = ws3.Cells(Rows.Count, 1).End(xlUp).Row

For ii = 1 To y
x = ws3.Cells(ii, Columns.Count).End(xlToLeft).Column
For k = 1 To x - 1
ws4.Cells(ii, k) = ws3.Cells(ii, k) + ws3.Cells(ii, k + 1)
Next
Next

Application.ScreenUpdating = True

End Sub

お礼

NuboChanさん、複数のご回答ありがとうございます！

他の回答者様からご指摘いただいたのですが
私の質問の仕方が不明瞭で求めたい結果がわかりにくい
質問ではありましたが、丁寧なご回答いただきまして感謝いたします！
私自身ＶＢＡに明るくないものですから、自分以外の人が使うファイルに使うのに躊躇している部分があるので
今回は活用できそうにないですが、勉強させていただきました！

回答No.8

kkkkkm

No.7の追加です。

C列が文字列のままなので数値にした方がいいかもで

C6に
=IFERROR(VALUE(MID(INDEX($A$3:$H$3,1,MATCH(ROW(B2),$A$4:$H$4,0)),FIND("=",INDEX($A$3:$H$3,1,MATCH(ROW(B2),$A$4:$H$4,0)),1)+1,LEN(INDEX($A$3:$H$3,1,MATCH(ROW(B2),$A$4:$H$4,0))))),"")
で下にコピー

回答No.7

kkkkkm

No.3をもっと卑怯にすると

1行目と3行目は変えずに

B2を
=IF(AND(A1="",B1=""),A2,IF(AND(A1<>"",B1=""),A1,IF(AND(A1="",B1<>""),A2 &"+" &B1 & "=" &A2+B1,A1 &"+" &B1 & "=" &A1+B1)))
にして右にコピー
で
A4に
=IF(A1<>"",COUNT($A$1:A1),"")
として右にコピー
で

たとえば(画像のようだと)
B6に
=IFERROR(LEFT(INDEX($A$3:$H$3,1,MATCH(ROW(B2),$A$4:$H$4,0)),FIND("=",INDEX($A$3:$H$3,1,MATCH(ROW(B2),$A$4:$H$4,0)),1)),"")
で下にコピー
C6に
=IFERROR(MID(INDEX($A$3:$H$3,1,MATCH(ROW(B2),$A$4:$H$4,0)),FIND("=",INDEX($A$3:$H$3,1,MATCH(ROW(B2),$A$4:$H$4,0)),1)+1,LEN(INDEX($A$3:$H$3,1,MATCH(ROW(B2),$A$4:$H$4,0)))),"")
として下にコピー
で2，3，4を非表示にするとかなり卑怯な手になります。

お礼

kkkkkmさん、複数のご回答ありがとうございます！

今見返してみたら、私の質問がわかりにくい中でも
質問に沿ったお答えいただいて感謝しています
使いこなすにはレベル不足かと思いますが
関数の組み合わせ方とか勉強になりました！

回答No.6

imogasi

この問題を関数でやれ、と言うのは難しいと思う。
「（対象セルの）位置の不定」を含む問題は、関数では難しいことが多い。
エクセルの関数での備えが少ないのだ。
解答で、関数の回答を望む質問者は多いようだが、関数であっても、難しい（組み合わせやアイデアのなかなか思いつかない）課題も多い。関数でなら、やさしいということは決してない。
自分でやってみて難しければ、関数での回答は望むな。関数なら丸ごとコピーも可というのもおかしい。
ＶＢＡでも、結果が正しければ、良しとすべきだ。
もんだいの関数でも、問題の条件が変わったら式のどこを変えたらよいか、当初作った人以外では判らない人も多かろう。
しかし逆に勉強して判る人もありそうです。
今までの質問者は、エクセルを使う同僚を、鼻から多分出来ない人と仮定している例が多いが
そうでもないかもしれない。
VBAでも、ボタンのクリックででも、できるようにしておけば、VBAを知らなくても結果は出る方法なのだ。
だからVBAは避けたいというよりも、他の方法を講じる（解説を充実するとか）べきだと思う。
すべてのソフトは、ユーザーに、どうプログラムしたか、判らないで使わせている。
　そういうことを忘れているのでは。
また仕事で、ソフトを使う場合は（Excelでも）、本当は、プロの援助が必要と思う。
＝＝
そのそも、本質問の課題は、珍奇な課題だと思う。WEBで照会しても出てこないだろう。
だから、データ作成から見直すとかすべきだと思う。
VBAでやっても、小生には２－３時間かかった。
ーー
最後に、思い付きですが、
特に別シートにデータをコピーして置いて、コピー先でジャンプーセル選択ー空白行で空白セルを捉え、CTRL+-で空白セルを詰める。（１行ずつ）。そういう中間ワークのシートを作る。
そうすれば、（不定列ではなく）隣同士の２セルの足し算で良くなるので望むような係数が出やすいのでは？
そうすれば関数式の複写に持ち込めるかも。
ーー
エクセルは第1には、操作で答えを出すソフトだと思う。この面も検討したらどうか。

お礼

imogasiさん、ご回答ありがとうございます！
確かにおっしゃる通りで狙った結果が得られるのなら方法にこだわるようなことはしない方がいいですね。
せっかく回答していただいるのに気分を悪くされた方もいらっしゃるかもしれません。
ＶＢＡは私自身がよくわかってないので、職場で使用するのに躊躇してしまう部分があったのですが、柔軟に考えてみます！

回答No.4

SI299792

どこのデータがあり、どのように結果を出すのかわかりません。
画像の様な表なら、

B2: =IF(B1="","",LOOKUP(E9+99,$A1:A1)+B1)
右へコピペ。

回答No.3

kkkkkm

A2に
=A1
で
B2に
=IF(AND(A1="",B1=""),A2,IF(AND(A1<>"",B1=""),A1,IF(AND(A1="",B1<>""),A2+B1,A1+B1)))
として右にコピー
これで各数値の左側の数値との合計が各数値の下に出ます
途中空白の下に5とか11とか出るので
A3に
=IF(A1="","",A2)
として右にコピーすれば3行目に合計だけが出ますから、2行目を非表示してしまうという卑怯な手があります。

お礼

kkkkkmさん、ご回答ありがとうございます！
教えていただいた式で確かに望んでいた結果が得られます＾＾
ただ、実際に作ろうとしている表では行がかなり多くなりそうなので、できれば作業用の行がない方がありがたいと思っておりました＾＾；
でも、式は応用できそうなので今後活用させていただきます！

回答No.2

NuboChan

下記へ修正します

Option Explicit

Sub test01()
Dim x As Long
Dim i As Long, ii As Long, k As Long

Dim ws1 As Worksheet

Set ws1 = Worksheets("sheet1")
x = ws1.Cells(1, Columns.Count).End(xlToLeft).Column

With ws1
For i = x To 1 Step -1
If Application.CountA(.Columns(i)) = 0 Then .Columns(i).Delete
Next
End With
Stop

x = ws1.Cells(1, Columns.Count).End(xlToLeft).Column

k = x + 1
For ii = 1 To x - 1
ws1.Cells(1, k) = ws1.Cells(1, ii) + ws1.Cells(1, ii + 1)
k = k + 1
Next

End Sub