戻る  □VBAのサロン  □ 使用方法  □ 新着記事  □ 新規に質問する!  □ トピック一覧  □ 検索  □ 過去ログ
[ 最新記事及び返信フォームをトピックトップへ ]
このトピック参照回数 :
右に値が追加されていく場合で、averageの範囲も自動で右にズレるようにするには

    [130357] 右に値が追加されていく場合で、averageの範囲も自動で右にズレるようにするには-

    ■親トピック/記事引用/メール受信=ON■

    □投稿者/ まま22 -(2019/06/18(19:38))
    □U R L/

      はじめまして、どうしても分からず質問させていただきました。
      一日ごとに数字が右方向に追加されていく表があり、その時の最新の値(一番右)を基準として、左へ4つの平均を取るような関数を作成したいです。自動でaverageの範囲が右にズレてくれたらいいのにと考えております。

      =LOOKUP(1,0/(A1:Z1<>""),A1:Z1)で一番右の値3をとることはできたのですが、
      さらにこれを基準として、左に3つの平均を取る方法があれば教えていただけますでしょうか。
      下記の場合ですと、H1の値3を基準として、7、5、4の平均値で4.7という答えになるものです。 OSはWindows10でExcelは一番最新のものを使用しています。

      どうぞよろしくお願いいたします。


      A B C D E F G H・・・Z
      6 5 5 3 4 5 7 3・・・


      ++++++++++++++++++++
      OS      ⇒OTHER
      Version⇒OTHER
      ++++++++++++++++++++




    [130361] Re[1]: 右に値が追加されていく場合で、averageの範囲も自動で右にズレるようにするには-

    記事引用/メール受信=OFF■

    □投稿者/ Mike -(2019/06/18(21:53))
    □U R L/

      =AVERAGE(OFFSET(A1,,MAX(IF(1:1<>"",COLUMN(1:1)))-1,,-4))
      【お断り】上式は必ず配列数式として入力のこと




    [130362] Re[2]: 右に値が追加されていく場合で、averageの範囲も自動で右にズレるようにするには-

    記事引用/メール受信=OFF■

    □投稿者/ メジロ -(2019/06/19(07:41))
    □U R L/

      Mike さんの式とほぼ同じですが。
      データに空白がないものとして。

      >一日ごとに数字が右方向に追加されていく

       =AVERAGE(OFFSET(A1,0,COUNT(A1:AE1)-4,1,4))




    [130363] Re[3]: 右に値が追加されていく場合で、averageの範囲も自動で右にズレるようにするには-

    記事引用/メール受信=OFF■

    □投稿者/ まま22 -(2019/06/19(09:14))
    □U R L/


      Mike様、メジロ様 ご回答いただきましてありがとうございます。
      私の質問が不明瞭でもうありません。

      A〜Hまでは全く関連性のない値が入っております。ですのでAA〜AIの範囲を指定して、その中で一番右の値を基準にして、そこから左に4つの平均値を取りたいです。セルには数字、文字列、空欄が含まれております。

      A社の場合ですとAF1の値8を基準として、8、4、6、5の4つの平均値、B社の場合ですとAG2の値 8を基準として、8、9、7、4の平均値、C社の場合ですとAD3の値 1を基準として、1、4、3、4の平均値をそれぞれB列に算出したいと考えております。

      質問が分かりづらくて申し訳ありませんが、どうぞよろしくお願い致します。


      A  B C D E F G H・・・・・・・・AA AB AC AD AE AF AG AH AI
      1 A社  6 5 5 3 4 5 7 3・空欄・・8 5 5 6 4 8   
      2 B社  1 3 4 5 5 3 3 3 空欄・・9 6 4 4 7 9 8
      3 C社  3 4 5 3 5 1 2 4 空欄・・4 3 4 1
       




    [130364] Re[4]: 右に値が追加されていく場合で、averageの範囲も自動で右にズレるようにするには-

    記事引用/メール受信=OFF■

    □投稿者/ まま22 -(2019/06/19(09:21))
    □U R L/

      ずが崩れていますので再度アップ致します。

        A  B C D E F G H・・・・ AA AB AC AD AE AF AG AH AI
      1 A社 6 5 5 3 4 5 7 3 空欄 8 5 5 6 4 8   
      2 B社 1 3 4 5 5 3 3 3 空欄 9 6 4 4 7 9 8
      3 C社 3 4 5 3 5 1 2 4 空欄 4 3 4 1
       




    [130365] Re[5]: 右に値が追加されていく場合で、averageの範囲も自動で右にズレるようにするには-

    記事引用/メール受信=OFF■

    □投稿者/ まま22 -(2019/06/19(09:26))
    □U R L/


        A  B C D E F G H・・・・   AA AB AC AD AE AF AG AH AI
      1 A社  6 5 5 3 4 5 7 3 空欄 8 5 5 6 4 8   
      2 B社  1 3 4 5 5 3 3 3 空欄 9 6 4 4 7 9 8
      3 C社  3 4 5 3 5 1 2 4 空欄 4 3 4 1




    [130366] Re[4]: 右に値が追加されていく場合で、averageの範囲も自動で右にズレるようにするには-

    記事引用/メール受信=OFF■

    □投稿者/ Mike -(2019/06/19(09:46))
    □U R L/

      > 私の質問が不明瞭でもうありません。
      > 質問が分かりづらくて…>
      「質問が不明瞭」とか「質問が分かりづらくて」とかではなく、「A〜Hまでは全く関連性のない値」を示して質問された訳で、チコちゃんに叱られないように願います!




    [130367] Re[5]: 右に値が追加されていく場合で、averageの範囲も自動で右にズレるようにするには-

    記事引用/メール受信=OFF■

    □投稿者/ 半平太 -(2019/06/19(10:28))
    □U R L/
      >セルには数字、文字列、空欄が含まれております。
        ↑
      と言っているのに,なぜ数値しかない計算例ばかり出すんですか?
                ↓
      >A社の場合ですとAF1の値8を基準として、8、4、6、5の4つの平均値、
      >B社の場合ですとAG2の値8を基準として、8、9、7、4の平均値、
      >C社の場合ですとAD3の値1を基準として、1、4、3、4の平均値
      
      説明と整合性あるサンプルを提示してください。
      
      あと、4つも存在しなかったらどうするのかも、説明してください。
      (それはあり得ないとか、有るだけで算出するとか)




    [130368] Re[6]: 右に値が追加されていく場合で、averageの範囲も自動で右にズレるようにするには-

    記事引用/メール受信=OFF■

    □投稿者/ メジロ -(2019/06/19(11:46))
    □U R L/

      半平太さんのご質問の回答により式は変化しそうですが、
      前回書き込みの延長で、OFFSET関数の起点が変化するだけです。

      =AVERAGE(OFFSET(AA1,0,COUNT(AA1:AI1)-4,1,4))




    [130369] Re[7]: 右に値が追加されていく場合で、averageの範囲も自動で右にズレるようにするには-

    記事引用/メール受信=OFF■

    □投稿者/ まま22 -(2019/06/19(13:18))
    □U R L/

      ご意見を頂きありがとうございます。
      今後質問する際に気を付けるようにいたします。

      再度、前提条件と教えていただきたい点について記載いたしましたので、
      どうぞよろしくお願いいたします。

      1.前提条件

      @対象の範囲はAA〜AIまで

      A毎日、@の範囲内の一番右に値を手入力する、最終列はAI列まで

      B平均対象範囲は一番右のセルの値+左に3つ分

      C平均対象の範囲に空欄や文字列が含まれている場合は無視し、平均の範囲内にある
      数値のみを平均する。※4つのセルには必ず1つは数値が含まれています。


      2.教えていただきたい点
      その時点においてAA〜AI列範囲内で一番右の値(可変する)を基準として、その値を含む左3つ分の平均を算出したいです。


      3.具体例 A社の場合

      今日 → AG1の値 「8」を基準として「8」、「黒」、「空欄」、「4」の数値のみの平均
         → 8と4の平均なので「6」

      翌日AH1に「2」を手入力する 
         → AH1の値 「2」を基準として「2」、「8」、黒、「空欄」の数値のみの平均
         → 2と8の平均なので「5」



        A  B C D E F G H・・・・   AA AB AC AD AE AF AG AH AI
      1 A社  6 5 5 3 4 5 7 3 空欄 空 空 4 空 黒 8   
      2 B社  1 3 4 5 5 3 3 3 空欄 9 黒 4 4 赤 9 8
      3 C社  3 4 5 3 5 1 2 4 空欄 空 3 4 1



      ※ 「空」=空欄
      ※ 「黒」又は「赤」=文字列





    [130370] Re[8]: 右に値が追加されていく場合で、averageの範囲も自動で右にズレるようにするには-

    記事引用/メール受信=OFF■

    □投稿者/ 半平太 -(2019/06/19(14:14))
    □U R L/
      入力される数値は、16,000,000 以下とします。
      
      =AVERAGE(INDEX($AA1:$AI1,MAX(1,MATCH(8^8,$AA1:$AI1)-3)):INDEX($AA1:$AI1,MATCH(8^8,$AA1:$AI1)))
      




    [130371] Re[9]: 右に値が追加されていく場合で、averageの範囲も自動で右にズレるようにするには-

    解決!!ありがとうございました! / 記事引用/メール受信=OFF■

    □投稿者/ まま22 -(2019/06/19(14:48))
    □U R L/

      半平太様
      教えていただきました数式で、解決することができました。
      本当にありがとうございました。

      メジロ様
      アドバイスをいただき、ありがとうございました。
      前提条件が足りず、申し訳ありませんでした。

      次回質問する場合には、内容を簡略化することなく書くようにします。

      皆様本当にありがとうございました。




    [130375] Re[10]: 右に値が追加されていく場合で、averageの範囲も自動で右にズレるようにするには-

    保留中です・・・ / 記事引用/メール受信=OFF■

    □投稿者/ まま22 -(2019/06/20(09:05))
    □U R L/

      追加で教えていただきたい点があります。例1のA社の場合で一番右の値であるAF1の「黒」を起点とし、左に4つ平均を取る場合、文字列と空欄のみであるため、該当なしとしたいのですがどの様にすれば良いでしょうか?

      (例1)
        A  B C D E F G H・・・・    AA AB AC AD AE AF AG・・
      1 A社  6 5 5 3 4 5 7 3 空欄 4 8 空 空 黒 赤 黒  
      2 B社  1 3 4 5 5 3 3 3 空欄 9 黒 4 4 赤 9 8







    [130376] Re[11]: 右に値が追加されていく場合で、averageの範囲も自動で右にズレるようにするには-

    記事引用/メール受信=OFF■

    □投稿者/ 半平太 -(2019/06/20(10:19))
    □U R L/
      あれー? 
      1番右にある「数値のセルから」じゃなかったんですか?
      
      1番右にある「数字または文字のセルから」に変わったんでしょうか?
      
      私はこれから出かけます。
      どなたかのレスが付くといいですね。




    [130377] Re[12]: 右に値が追加されていく場合で、averageの範囲も自動で右にズレるようにするには-

    記事引用/メール受信=OFF■

    □投稿者/ まま22 -(2019/06/20(10:49))
    □U R L/


      半平太様のおっしゃる通りです。一番右に文字列が来ることを想定しておりませんでした、複数調べていましたら、上記の例1のように一番右の値が文字で、左に空欄や文字が連続するパターンがあり、質問させていただきました。





    [130380] Re[13]: 右に値が追加されていく場合で、averageの範囲も自動で右にズレるようにするには-

    記事引用/メール受信=OFF■

    □投稿者/ 半平太 -(2019/06/20(18:17))
    □U R L/

      これで

      =IFERROR(AVERAGE(INDEX(AA1:AI1,MAX(1,MATCH(1,INDEX(0/(AA1:AI1<>""),0))-3)):INDEX(AA1:AI1,MATCH(1,INDEX(0/(AA1:AI1<>""),0)))),"該当なし")





    [130381] Re[14]: 右に値が追加されていく場合で、averageの範囲も自動で右にズレるようにするには-

    解決!!ありがとうございました! / 記事引用/メール受信=OFF■

    □投稿者/ まま22 -(2019/06/20(19:17))
    □U R L/

      半平太様

      本当にありがとうございました。解決致しました!!

      また機会がありましたら、どうぞよろしくお願い致します。




このトピック内容の全ページ数 / [0]

このトピックに書きこむ
Name/
E-Mail/

└>このツリーのレス記事をメールで受信しますか? YES/ NO/
Title/
URL/
Comment/ 通常モード->  図表モード-> (←の場合適当に改行を入れて下さい)
タグが使用できます。例 ⇒ <font color="blue">文字</font>
解決!!ありがとうございました! 解決       保留中です・・・ 保留        迷宮入りorほかあたって見ます・・ 迷宮入       すいませ〜ん。誰か〜! Help! ←※回答者専用
解決したらチェック       保留(コードテスト中など・・)         解決不可orレスが全く付かなくてほかのサイトに行くときなど・・
削除キー/
(半角8文字以内) 


- Child Forum -
Edit:ゆう-G