【iOS版アプリ】不具合のお知らせ

iferrorなど詳しい方…!!

本日別件で質問をなげ、エクセルの神様のような方から答えをいただいたのですが、

写真の①と②がありまして、①のデータを②に飛ばすという内容でした。
その際は②のB2に月を入れて吸い上げるという内容で、神様からの回答が下記でした。

 ・元のデータがあるシート名は「Sheet1」と仮定
 ・B2セルの月の指定は単なる数値で、Sheet1のC列の日付はシリアル値である。
 ・店舗名のあるセルは、B4,B11,B18,B25固定で、Sheet1のB列の店舗名と同一である


=IFERROR(INDEX(Sheet1!$D:$I,AGGREGATE(15,6,ROW(B$3:B$99)/(Sheet1!$B$3:$B$99=INDEX($B:$B,INT(ROW(A1)/7)*7+4))/(MONTH(Sheet1!$C$3:$C$99)=$B$2),MOD(ROW(A8),7)),COLUMN(A1)),"")

この式でバッチリだったのですが、1行追加するとエラーになってしまいました。
何時間も格闘しましたがクリアできず…

どなたか1行追加したらどこを直せばいいのか分かりますでしょうか(;_;)

「iferrorなど詳しい方…!! 本日別」の質問画像

質問者からの補足コメント

  • こちらにデータを飛ばしたいです!

    「iferrorなど詳しい方…!! 本日別」の補足画像1
      補足日時:2021/09/21 23:38
gooドクター

A 回答 (4件)

ご質問者は


>1行追加するとエラーになってしまいました。
とおっしゃっていますが、前回回答者さんは、
>・店舗名のあるセルは、B4,B11,B18,B25固定で、・・・
という前提条件を付けていらっしゃるので、「1行追加するとエラー」となるのは「店舗名のあるセルが、B4,B11,B18,B25ではなくなる」ためとだと推測でき、ある意味当然だと思います。
そこで、表の形式と数式を修正して、行を追加・削除しても数式の変更が不要な形にする必要があると思います。
ご質問者がお示しになった画像➁では、シートのA列が見かけ上「空白」になっています。
実際にこの列は「空白」であり使用していないものと推測しました。A列がそもそも「空白」ではなく、何等かの役割を担っている場合は、以下の手順は使えませんので、ご了承ください。

添付画像①をご覧ください。A店、B店、・・・の店名がB列にありますが、店名のあるB列セルの左のA列セル(添付画像で言えばA4セル)に「=B4」という数式を記述し、文字色を白色にして見えなくしています。
この数式をB列に店名のあるセルの左側のA列セルにコピーしてください。(添付画像ではA12、A20、A28にコピーしています)
店名が増える場合は、B列に店名を入れた場合は、必ずその左のA列セルにB列セルを参照する数式を入れるというルールにします。準備はこれで完了です。
さて、添付画像➁をご覧ください。
ご質問者が示された前回回答の数式を以下のように修正します。
修正後の数式は、A列に存在する店名を探して、表示内容を決定する仕組みですので、A店の行数を1行増やす(あるいは減らす)とか、C店の行数を増やす(あるいは減らす)などの作業を行っても表示内容に影響がでない仕組みになっています。ただし、とても長い数式になってしまいました。もう少し検討を深めれば短い数式になるかも知れませんが、それは他の回答者さんにお任せします。

=IFERROR(INDEX(Sheet1!$D:$I,AGGREGATE(15,6,ROW(B$3:B$99)/(Sheet1!$B$3:$B$99=INDEX($B$1:$B$99,AGGREGATE(15,6,ROW($A$1:$A$99)/($A$1:$A$99<>""),COUNTIF($A$4:$A6,"<>"))))/(MONTH(Sheet1!$C$3:$C$99)=$B$2),ROW(A6)-AGGREGATE(15,6,ROW($A$1:$A$99)/($A$1:$A$99<>""),COUNTIF($A$4:$A6,"<>"))-1),COLUMN(A1)),"")

これを添付画像で言えばB6セルに入力し、下方、右方にコピーします。コピーしたセル範囲を、次の(下の)表に順にコピペでも可能なのは前回回答者さんの回答と同様です。
「iferrorなど詳しい方…!! 本日別」の回答画像4
    • good
    • 1
この回答へのお礼

ありがとうございます!とても助かりました!

お礼日時:2021/09/24 16:44

ピボットテーブルにした方が楽そうですね。

    • good
    • 1

こんばんは。



神様に近い方がいないと解決しないかも知れませんが、どの行を追加されて
 エラーになったのでしょうか?
添付の画像は、行が追加される前?それとも追加された後?

あと、関数は、結局は使われる方が理解しないと、応用もきかないですし、
 問題が起きると、手に負えなくなります。。。
    • good
    • 1

んー。


私は神様ではありませんが、結局のところ数式を理解することができなかったという事でしょうか。

ちょっと(かなり面倒なので大雑把に)解説してみましょう。
まずは数式を分解します。

=IFERROR(
     INDEX(
        Sheet1!$D:$I
        ,AGGREGATE(
               15
               ,6
               ,ROW(B$3:B$99)/(Sheet1!$B$3:$B$99=INDEX($B:$B,INT(ROW(A1)/7)*7+4))/(MONTH(Sheet1!$C$3:$C$99)=$B$2)
               ,MOD(ROW(A8),7)
               )
        ,COLUMN(A1)
        )
     ,""
     )

こんなところかな。

AGGREGATE関数でエラーになるデータを無視して小さい順に値を拾っていますね。
AGGREGATE関数の
一つ目のパラメーター”15”は小さい順に並べた場合の値を拾う事を示しています。
二つ目のパラメーター”6”はエラーを無視することを示しています。
三つ目のパラメーターは比較する配列データです。
四つ目のパラメーターは何番目に小さいかを指定しています。

この結果はINDEX関数に渡されて、範囲(この場合Sheet1!$D:$I)の中からCOLUMN関数と組み合わせて値を拾うために使われます。
計算結果は横方向、COLUMN関数の結果は縦方向を指定です。


さて、ここから面倒な計算式の読み解きになります。
一行追加して正常に動作しないのは、ここが問題になっていると考えられます。

  ROW(B$3:B$99)
  /(Sheet1!$B$3:$B$99=INDEX($B:$B,INT(ROW(A1)/7)*7+4))
  /(MONTH(Sheet1!$C$3:$C$99)=$B$2)
という割り算。
この数式の中の関数が返す値は、
  3から99
  /TRUE(1として扱う)またはFALSE(0として扱う)
  /TRUE(1として扱う)またはFALSE(0として扱う)
これを計算するって事。
…ここでゼロで割ることになるとエラーになるのでAGGREGATE関数が使われている。
結果、エラーにならなかった行のデータだけを拾うための配列になる。

・・・

とまあ、大雑把な解説でした。
数式の中で「何をやっているか」「なぜそんなことをするのか」は自身で考えてみてください。
こうやって中身を理解しないと今回のように1行追加するだけで動作しないものになります。

要は、【目の前にある疑問を先送りにしただけ】になるって事です。
そんなでは同じような疑問のある問題に直面したとき、そこでまた躓きます。
自身で問題を解決できるよう【理解】する努力を忘れてはいけません。
がんばれ。


・・・余談・・・

ここまで解説して、実は別のところで問題があったなんて話の落ちが付きそうで、
書きたくはないけど……。

途中に行を追加すると、その行は入力済みの計算の範囲外になることがあります。
範囲を見直した数式をもう一度入力し直すと幸せになれることがありますよ。
    • good
    • 1
この回答へのお礼

ありがとうございます。理解…まさにその通りです。
細かく解説してくださってありがとうございます!

お礼日時:2021/09/24 16:44

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

gooドクター

このQ&Aを見た人がよく見るQ&A

このカテゴリの人気Q&Aランキング