質問: エクセルの表の列の最下行から指定数のデータで データが縦方向に入っている表があります。 新しいデータは最下行に追加していきます。 それぞれの列の最下行から1つ上のセルから任意の数で平均や最大、最小値を求めたいのです。 (つまり統計値に最新値を含まない値で最新値を評価したい) 但し、下記の条件が付きます。 1.データは17行目から始まって、それより上の行は列によってデータ数が違っています。 2.出来ればデータ数が指定(例えば30個)に名足りない場合は最新値を含むデータにしたい。 最初はデータ数が少ないのでそれも含めて統計処理したい。 (この条件でハードルが上がるようなら無視でもok) 3.データ列には空白セルや「ー」が入っている場合が混在しているので扱うのは数値のみ。 昔ここで教えてもらいながら、試行錯誤してかなり長い式を作って使っていたのですが、最新データを含むと非常にまずいケースがあることに気付き、色々試行しているうちに全く混乱してしまったのでhelpしました。
回答: no.3です. 私が考えた方法は以下のとおりです. (a) データは 17:10000の行範囲に入力する. (b) 10017:20000の行範囲に 17:10000の有効データ(数値データ)の番号を付与する. (c) 6:13の行範囲に途中の計算結果を表示する. 特に長い数式は後でワケがわからなくので (c)のように小分けにしたほうが保守しやすいです. 以下に手順を示します. (1) b10017に数式 =count(b$17:b17) を入力する($の使い方に注意). (2) b10017の数式を b20000までコピーする. (3) b6にデータの抽出数を入力する(質問者の例では 30). (4) b7に以下の数式を入力する. =count(b$17:b$10016) (5) b8に以下の数式を入力する. =b9-b6+1 を入力する. (6) b9に以下の数式を入力する. =b7-1 を入力する. (7) b10に以下の数式を入力する. =substitute(address(1,column(),4,1),"1","") (8) b11に以下の数式を入力する. =b$10 & match(b8,b$10017:b$20000,0)+16 (9) b12に以下の数式を入力する. =b$10 & match(b9,b$10017:b$20000,0)+16 (10) b13に以下の数式を入力する. =b11&":"&b12 (11) b2に以下の数式を入力する. =if(b$7<=b$6,average(b$17:b$10000),average(indirect(b$13))) (12) b3に以下の数式を入力する. =if(b$7<=b$6,max(b$17:b$10000),max(indirect(b$13))) (13) b4に以下の数式を入力する. =if(b$7<=b$6,min(b$17:b$10000),min(indirect(b$13))) (14) b列をc列以降必要なだけコピーする. では,各手順の説明です. (1)~(2) countで数値データを数えています.これが有効データ番号になります. (3) 何件抽出するかを設定できるようにしました.数式に入れ込むと使い勝手と保守性が悪くなります. (4) countで有効データ数を数えています.これは最下行の有効データ番号になります. (6) 番号が前後しますが, 最下行の 1つ上の有効データ番号(=抽出終了no.)を求めています. (5) (6)の抽出終了no.と (3)の抽出数から抽出開始no.を求めています. (7) 列番号(アルファベット)を求めています. 提示された数式では "d" のようにダイレクトに入力されていますが,入力ミスがないように数式にしました. (8) machtで抽出開始no.が何行目に記入されているかを求め,(7)と組み合わせて抽出開始セルを求めています. (9) (8)と同様にして抽出終了セルを求めています. (10) 抽出開始セルと抽出終了セルを ":" で繋いで計算範囲セルを求めています. (11)~(13) 統計計算の部分です.計算範囲を indirectで指定しています. ifを使って,式の前半部分でデータ数が抽出数以下の場合を計算し,後半部分でデータ数が抽出数を超えた場合を計算しています. これまでのほとんどの手順は,後半部分のための準備になります. 複雑な数式もこのように小分けすれば理解しやすくなります. ふぅ~ 画面の部分コピーは,win10なら標準で付属されている snipping tool を使えば簡単です. 画面左下の検索窓に snip と入力すれば見つかると思います.