エクセルでセルの値分の個数の数字列を自動で入れたい

一番簡単な関数で入力方法を教えていただけると助かります。

例題として基本的な表示させる数字が【5000】の決まりがあるとして
エクセル欄のA欄3行目から6行目までが①コマになっていて
Ｂ欄の３行目から６行目は通常のマスになっています。
そこでですがA欄の3行欄に【3】と入力したときに
Ｂ欄の３行目～5行目すべてに【5000】と表示させる方法を教えてください。

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

• ご回答頂きありがとうございます。
  事細かく親切にご説明頂き感謝申し上げます。
  
  しかしながら
  私の知識不足の為、理解できない事があり
  式を変えてみても上手く表示が出来ない為、再度お教えいただけたらと思います。
  
  最初の質問で3～6で１セルとして数字を入力し固定数字を表示するように教えていただきましたが
  4～7を１セルにしたく1～3行を別表示をしようと1行を追加したら固定数字が表示しなくなってしまいました。
  教えていただいた式を変更してみましたが思うような表示が出来ませんでした。
  何度もお手間を取らせてしまい申し訳ありませんがご教授をお願いします。

  No.4の回答に寄せられた補足コメントです。 補足日時：2023/03/17 16:16

• 

  皆様からのご回答頂きありがとうございます。
  最初にご質問させていただいた内容は解決できそうです。
  
  関連した内容について新たな疑問が生じてしまいましたので
  再度お教え頂きたくお願い致します。
  
  5800及び5000と表示されている数値を縦列にて合計を出したいと思うのですが
  空白欄があるとエラーになってしまうので合計数値の算出が出来ません。
  もちろんIF関数を入れているのですがエラーになってしまいます。
  現在の式は下記になっています。（空白がなければOKなのですが）
  ＝IF(LEN(E120)=0,"",(E120+E121+E122+E123+E124+E125+E126+E127))
  　　　　E
  120 　5800
  121 　5000
  122
  123
  124 　 5800
  125 　 5000
  126 　5000
  127 　5000

    補足日時：2023/03/24 16:30

No.8 回答者： goomania 回答日時： 2023/03/25 12:54

No.6です。

追加質問についてはenunokokoroさんのご指摘のとおりだと思います。

さて、当初のご質問だと、
＞A欄3行目から6行目までが①コマになっていて･･･
とのことでしたので、セル位置は異なっているとしても、E120～E123のセルの数字を決定する値、E124～E127の数字を決定する値が、それぞれどこか別のセルにあるのだと推測しました。

そうではなく、E120～E127の数字を一度に決める値を別セルに設置したということなら以下の説明は無視してください。

最初のご質問のとおり、4セルづつ数字を決定する値を別セルに入れているのだとすると、
【E120～E123は「空白」(="")だが、E124～E127に数字がある】
というケースもあるのではないかと思いますが、違いますか?

仮に上記のようなケースがあるとすると
＝IF(LEN(E120)=0,"",(E120+E121+E122+E123+E124+E125+E126+E127))という数式
あるいはenunokokoroさんの回答の数式だと、E124～E127に数字があるのに、合計が「空白」になってしまいます。

enunokokoroさんの回答にあるとおり、SUM関数は””(空白)や文字列は無視して計算し、エラーにならないので、取り敢えず合計を求めて、合計が0なら表示しない(空白を表示)という方法はいかがでしょう。つまり、数式は

=SUM(E120:E127)

として、合計を表示するセルの書式設定「表示形式」をユーザー定義で「#」とすれば0は非表示(空白)という設定が可能です。(3桁ごとのカンマが必要なら「#,###」としてください)

書式設定でなく、数式で空白表示にするなら、

=IFERROR(1/(1/SUM(E120:E127)),"")

とする手もあります。

No.7 回答者： enunokokoro 回答日時： 2023/03/24 18:13

追加質問は回答者への負担です。

列の追加や行の変更などでも負担なのに、新たな疑問としての
追加質問は、締め切ってから新規質問で投稿してください。
今回は初めての質問みたいなので特別に回答します。

四則計算だと、IF関数で空白処理した場合エラーになります。
合計を出すならSUM関数を使うことで空白処理の””があっても
計算してくれます。（""は文字列扱いなのでエラーとなる）
　=IF(LEN(E120)=0,"",SUM(E120:E127))
SUM(E120:E127)は連続したセルの合計みたいなので、この
設定で大丈夫です。

No.6 回答者： goomania 回答日時： 2023/03/19 15:37

既に、enunokokoroさんの回答でご質問者の課題は解決していると思いますが、別の数式を投稿します。

ご質問者は、
>A欄に数字を入れた場合【5000】、B欄に数字を入れた場合【5800】
と仰っているだけで、数字以外のものを入れた場合どうするのか指定していますせん。
従って数字をいれたときにご質問者の意図した結果になれば良いということになります。
添付画像①をご覧ください。C4セルに

=IFERROR(AGGREGATE(14,6,{5800;5800;5800;5800;5000;5000;5000;5000}/(ROW($1:$8)>=(5-SUM(OFFSET(C4,0,-1,-4))))/(ROW($1:$8)<(5+SUM(OFFSET(C4,0,-2,-4)))),MOD(ROW(C4),4)+1),"")････(a)

という数式を記述し、下方向へコピーしています。
C列の表示はご質問者のご希望のものになっていると思います。

前述のとおり、A列、B列に数値以外のもを入れた場合の指定はないのですが、enunokokoroさんの回答の数式に、試しに数値以外の「文字」を入れてみました。

添付画像②をご覧ください。B列に「文字」がある場合、C列には1コマ分全てに【5800】が(4つ連続して)表示されるようです。
また、A列に「文字」がある場合C列には「#VALUE!」が表示されるようです。
添付画像③をご覧ください。この回答でお示しした、(a)の数式で数字以外のものを入れた状態です。B列に「文字」がある場合は5800の表示のみ無視されます。A列に「文字」がある場合も5000の表示のみ無視されます。

なお、B列に負の数値を入れた場合、enunokokoroさんの回答の数式でも、上記(a)の数式でも数式記述位置の上方へ負の数分遡って、5800を表示し、そこから折り返してA列に入れた数分5000を表示するという意味になります。
従って、「A列に入れた正の数値の絶対値>B列に入れた負の数値の絶対値」のときのみ、絶対値の差分だけ5000が表示されます。
但し、enunokokoroさんの回答の数式では計算上、入れる最大値を制限していないのに対し、(a)の数式では計算上入れる数値を最大値「4」と制限しており、「4」より大きい数値を入れたときは「4」を入れたものと見做して計算します。
このため、enunokokoroさんの回答の数式と(a)とでは、B列に負の数値、A列に4より大きい数値の組合せで入れたときの挙動が異なります。

また、enunokokoroさんの回答の数式では、A列に入れた負の数値は無視されますが、(a)ではA列に入れた負の値は数式記述位置の上方へ負の数分遡って、5000を表示することを意味します。このため、既に表示される筈の5800に5000を表示しようとしますので、結果として遡った分だけ「空白」になります。

No.5 回答者： enunokokoro 回答日時： 2023/03/17 17:24

1行足すことでずれるのは、ROW関数との関係で計算値がずれた

ためです。
作業列を使った説明でもわかるように、
　=MOD(ROW()+1,4)+1
は、3行目から開始した場合なので、4行目の場合はROWの後ろに
ある+1を消します。
　=MOD(ROW(),4)+1
これで、4～7を１セルにしたときの参照する行の位置が修正
されます。

次にINDEX関数を使ったときのA列の取得位置も、
　=INDEX(A:A,INT((ROW(A1)-1)/4)*4+3)
だと、INT関数で4行単位で同じ値になるようにしたものに、+3して
3行目を指定しているので、+4に修正することで4行目から開始を
する設定にします。
　=INDEX(A:A,INT((ROW(A1)-1)/4)*4+4)
これで4行目から取得できるようになります。

C列に直接入れる場合は、
　=IF(MOD(ROW(),4)+1<=INDEX(B:B,INT((ROW(B1)-1)/4)*4+4),"【5800】",IF(MOD(ROW(),4)+1<=INDEX(A:A,INT((ROW(A1)-1)/4)*4+4)+INDEX(B:B,INT((ROW(B1)-1)/4)*4+4),"【5000】",""))
とすることで、表示してくれると思います。

考え方として記載した作業列の式が、何を意味しているのか理解
してあれば修正もできたと思います。
時間があるときに、各関数の意味などを理解してください。

No.4 回答者： enunokokoro 回答日時： 2023/03/15 17:50

No.2のお礼にある条件だと、前と同じ式を応用した場合には

　=IF(MOD(ROW()+1,4)+1<=INDEX(B:B,INT((ROW(B1)-1)/4)*4+3),"【5800】",IF(MOD(ROW()+1,4)+1<=INDEX(A:A,INT((ROW(A1)-1)/4)*4+3)+INDEX(B:B,INT((ROW(B1)-1)/4)*4+3),"【5000】",""))
で求めることは可能です。

もっと短くできるのでしょうが、考えるのが面倒だったので、
IF関数の入れ子で処理しました。

考え方が分かるように添付画像では作業列を用意しました。
E列が結合セルに対応するための式で
　=MOD(ROW()+1,4)+1
F列が前の式と同じA列にB列を足したもので
　=INDEX(A:A,INT((ROW(A1)-1)/4)*4+3)+G3
G列がB列の場合の式で
　=INDEX(B:B,INT((ROW(B1)-1)/4)*4+3)
H列は作業列を元にしたものです。
　=IF(E3<=G3,"【5800】",IF(E3<=F3,"【5000】",""))

作業列を用意できるのなら、式は単純になります。
メンテナンスし易さを考えるのならば、作業列を作ったほうが
管理しやすいですよ。

ちなみに、A列とB列の合計が4以上にならないようにさせて、
B列単独でも4以上にならないように入力規則などで対応する
設定を用意しておくことをお勧めします。
一応、5以上でも対応はしますが、間違った内容なのですから
エラーメッセージを出すようにしておくのが大事だからです。

No.3 回答者： enunokokoro 回答日時： 2023/03/14 19:20

No.2の回答者です。

式が途中で改行されていましたね。

　=IF(MOD(ROW()+1,4)+1<=INDEX(A:A, INT((ROW(A1)-1)/4)*4+3),"【5000】","")

これを貼り付けてみて。

No.2 回答者： enunokokoro 回答日時： 2023/03/14 19:17

A列は3行目から6行目まで結合してあるのですね。

B列は結合していないのなら、B3セルに以下の式を入れてみて。
　=IF(MOD(ROW()+1,4)+1<=INDEX(A:A, INT((ROW(A1)-1)/4)*4+3),"【5000】","")
B3セル以降はフィルコピーで参照先がA列の値になります。

この回答へのお礼

細かく絵図までつけて頂きありがとうございます。追加で教えていただきたいのですが【5000】の表示場所に【5800】も表示をさせたい場合にＢ欄もA欄と同じような形状にして表示場所をⅭ欄にする場合の方法にありますか？
表示する行桁が
【5800】
【5000】
になるような方法はありますでしょうか？
補足ですが
A欄に数字を入れた場合【5000】
B欄に数字を入れた場合【5800】
上記の内容で下記の感じに入力した場合ですが
A欄に入力する数字を２
B欄に入力する数字を１
とした場合のⅭ欄表示が
【5800】
【5000】
【5000】
のような感じになればベストなんですが・・・
いろいろ後から申し上げてお手数をお掛けし申し訳ありません。
ご教授頂ければと思います。
宜しくお願いします。

お礼日時：2023/03/15 15:40

No.1 回答者： crysmile 回答日時： 2023/03/14 18:06

そんなあなたにChatGPT