エクセル関数式により、列の最後尾から2行目の値からの最大値の求め方。
初めまして、よろしくお願いします。
A B C D E
1 赤 青 黄 緑 黒
2
3
4 1 3 2 0 9
5 9 1 3 4 3
・ ・ ・ ・ ・ ・
・ ・ ・ ・ ・ ・
112 4 6 1 9 6
113 7 7 5 8 1
114 6 9 1 3 7
115 7 2 5 0
116 8 0 0
という表があります。
各列の空白、もしくは0の入力されている後の値(A列では8、B列では9、C列では2、D列では5、E列では7)を最後尾とし、そこから2行目の値(A列では6、B列では6、C列では5、D列では8、E列では6)からの列(A列ではA4の1まで、B列ではB4の3まで、C列ではC4の2まで、D列ではD4の0まで、E列ではE4の9まで)の間の値の最大値(A列で、見えている部分のみではA5の9になります)を抜き出すことのできる関数式の方法がありましたら、教えていただきたく。よろしくお願いします。
No.2ベストアンサー
- 回答日時:
なんだかこの話に聞き覚えがあると思ったら、質問番号5945963の方
ですね。
質問番号5945963では最後尾から2つ上の「値」を返すために、index
関数を使ってindex(範囲,最後尾から2つ上の行番号)という計算をし
ました。今回はmax関数に入れる範囲を「4行目から最後尾から2つ上
の行まで」で生成したいのですから、そのまま応用が利くはずなん
ですが。
最後尾から2つ上の行番号は
large(index(row(A4:A200)*(A4:A200>0),0),3)
でしたね。A4から始まる範囲なら行数は上の式から3引きますから、
たとえばoffset関数で
offset(A4,0,0,large(index(row(A4:A200)*(A4:A200>0),0),3)-3)
のようになります。これをmax関数に放り込むだけで行けると思いま
すよ。
No.4
- 回答日時:
A~E列はそれぞれ4行目からが数値データなんですね?
わたしはこの手の複雑な関数は不得手なのでVBAでやってみました。
F1:J1にA~E列の対象範囲の最大値を転記します。
簡単ですので以下の手順をおためしください。
1.AltキーとF11キー同時に押し(以下Alt+F11キーと記述)て Visual Basic Editor を呼び出します。
2.Visual Basic Editor のメニューから「挿入」、「標準モジュール」で出てきたコードウィンド(右側の白い広い部分)に以下のコード(Sub~End Sub)をコピペします。
'********これより下**********
Sub test01()
Dim i As Integer
Dim tg(1 To 5) As Range
Dim myAr(1 To 5) As Double
Dim myStr As String
For i = 1 To 5
Set tg(i) = Cells(Rows.Count, i).End(xlUp)
Do While tg(i).Value = 0
Set tg(i) = tg(i).Offset(-1)
Loop
myAr(i) = Application.Max(Range(Cells(4, i), tg(i).Offset(-2)))
Next i
For i = 1 To 5
myStr = myStr & vbNewLine & i & "列目:" & myAr(i)
Next i
Range("F1").Resize(, UBound(myAr)).Value = myAr()
MsgBox "F1:J1に転記しました。" & myStr
End Sub
'********これより上**********
3.Alt+F11キーでワークシートへもどります.
4.Alt+F8キーで出てきたマクロ名(test01)を選択して実行します。
No.3
- 回答日時:
数値の入力された最後の行をCOUNT関数で出してみました。
始まりは4行目に特定されていたようなので+3しています。また、最後の数値が0の場合を2回までと考えてネストを組んでいますので、それ以上0があるなら、さらに重ねて下さい。ただ、重ねる回数に制限がありますが、、、。あと、B列、C列・・・の際、"A"のところを全て"B"などに変更して下さい。
=IF(INDIRECT("A"&COUNT(A4:A116)+3)>0,MAX(A4:INDIRECT("A"&COUNT(A4:A116)+1)),IF(INDIRECT("A"&COUNT(A4:A116)+2)>0,MAX(A4:INDIRECT("A"&COUNT(A4:A116))),IF(INDIRECT("A"&COUNT(A4:A116)+1)>0,MAX(A4:INDIRECT("A"&COUNT(A4:A116)-1)))))
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・人生のプチ美学を教えてください!!
- ・10秒目をつむったら…
- ・あなたの習慣について教えてください!!
- ・牛、豚、鶏、どれか一つ食べられなくなるとしたら?
- ・【大喜利】【投稿~9/18】 おとぎ話『桃太郎』の知られざるエピソード
- ・街中で見かけて「グッときた人」の思い出
- ・「一気に最後まで読んだ」本、教えて下さい!
- ・幼稚園時代「何組」でしたか?
- ・激凹みから立ち直る方法
- ・1つだけ過去を変えられるとしたら?
- ・【あるあるbot連動企画】あるあるbotに投稿したけど採用されなかったあるある募集
- ・【あるあるbot連動企画】フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
- ・映画のエンドロール観る派?観ない派?
- ・海外旅行から帰ってきたら、まず何を食べる?
- ・誕生日にもらった意外なもの
- ・天使と悪魔選手権
- ・ちょっと先の未来クイズ第2問
- ・【大喜利】【投稿~9/7】 ロボットの住む世界で流行ってる罰ゲームとは?
- ・推しミネラルウォーターはありますか?
- ・都道府県穴埋めゲーム
- ・この人頭いいなと思ったエピソード
- ・準・究極の選択
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルで二つの数字の小さい...
-
PowerPointで表の1つの列だけ...
-
エクセル 文字数 多い順 並...
-
エクセルで最初のスペースまで...
-
Excelで半角の文字を含むセルを...
-
エクセル(勝手に太字になる)
-
「B列が日曜の場合」C列に/...
-
Excel、市から登録している住所...
-
Excel 文字列を結合するときに...
-
2つのエクセルのデータを同じよ...
-
VBAで文字列を数値に変換したい
-
エクセルのセル内の文字の一部...
-
エクセルで一行毎、一枚づつ自...
-
エクセルの表から正の数、負の...
-
【エクセル】指定した文字以外...
-
ExcelのVBAで連番を振る。
-
Excelで、A列にある文字がB列...
-
エクセルの関数(日数の平均の...
-
エクセルの並び変えで、空白セ...
-
エクセルで文字が混じった数字...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルで二つの数字の小さい...
-
PowerPointで表の1つの列だけ...
-
エクセルで最初のスペースまで...
-
エクセル(勝手に太字になる)
-
Excelで半角の文字を含むセルを...
-
エクセル 文字数 多い順 並...
-
2つのエクセルのデータを同じよ...
-
エクセルで文字が混じった数字...
-
文字列に数字を含むセルを調べたい
-
エクセル 時間帯の重複の有無
-
VBAで文字列を数値に変換したい
-
「B列が日曜の場合」C列に/...
-
エクセル 同じ値を探して隣の...
-
エクセルの項目軸を左寄せにしたい
-
エクセルで、列の空欄に隣の列...
-
お店に入るために行列に並んで...
-
Excel、市から登録している住所...
-
エクセルの並び変えで、空白セ...
-
オートフィルターをかけ、#N/A...
-
エクセルの表から正の数、負の...
おすすめ情報