Googleスプレッドシートでスピルを用いた行ごとの達成率の計算

Question

実現したいこと

Googleスプレッドシートでスピルを用いた行ごとの達成率の計算

　　

前提

画像のように、列方向に日付、行方向には社員別のとある集計結果があります。

当初、E列には下記のような計算式を入れて行別の達成率を算出していました。

=IF(D3="", "", COUNTIF(G3:AN3, 1) / COUNTA(G3:AN3))

これをE3セㇽにのみ計算式を入れて一括で計算できないかと、

=ARRAYFORMULA(IF(D3:D="", "", COUNTIF(G3:AN, 1) / IF(COUNTA(G3:AN), COUNTA(G3:AN), 1)))

このように入力すると、すべて「14%」となり、各行で集計をしたいという意図とはズレてしまいました。

どうにか、スピルで行ごとに個別の計算結果を表示させられないでしょうか？

▼追記　2024-11-5　※参考（解決した関数）

={
  "達成率";
  ARRAY_CONSTRAIN(
    BYROW(
      INDIRECT(
        "D2:" & 
        LEFT(
          ADDRESS(1, COUNTA(D2:2) + COLUMN(D1) - 1, 4), 
          2
        )
      ), 
      LAMBDA(_r, 
        IF(
          COUNTA(_r) > 0, 
          SUMIF(_r, 1) / COUNTA(_r), 
          0
        )
      )
    ), 
    COUNTA(A2:A), 
    1
  )
}

Answer 1

COUNTIF等は元々範囲を引数にとるので、ARRAYFORMULAと相性が良くないです。
（COUNTIFの第二引数である集計対象を配列式にすればARRAYFORMULAで扱えますが、今回の問題解決にはつながりそうにないです）

こちらの記事が参考になると思います。
https://qiita.com/Shinoda_Naoki/items/9d0de6583e53720c4c4f
今回の件では BYROW で G3:AN3 を範囲として指定し、LAMBDAに渡されたrowを対象としてCOUNTIF,COUNTAすれば良さそうです。

Comments

[Oni_hiro]

＞pecmm様
コメントありがとうございます！
教えていただいた記事を元に少し工夫してみます。

正式なご回答は内容が理解できてからさせてください。

[Oni_hiro]

＞pecmm様
ありがとうございました。参考にさせていただき希望通りの機能が実装できました。

={
"達成率";
ARRAY_CONSTRAIN(
BYROW(
INDIRECT(
"D2:" &
LEFT(
ADDRESS(1, COUNTA(D2:2) + COLUMN(D1) - 1, 4),
2
)
),
LAMBDA(_r,
IF(
COUNTA(_r) > 0,
SUMIF(_r, 1) / COUNTA(_r),
0
)
)
),
COUNTA(A2:A),
1
)
}