エクセルの関数についての質問です。

質問

エクセルの関数についての質問です。

画像のように横に１、２、３…（１００くらいまで続きます）縦にＡ、Ｂ、Ｃの行があります（端が切れちゃってますが、Ａ２にＡ、Ａ３にＢ、Ａ４にＣです）

Ｂ２から横の列にはそれぞれ☆や○等いろいろな記号が入ります。

そしてこの記号には特定の値が決められています。☆＝１０、○＝５、△＝３

Ｂ３から横には、Ｂ２の記号にそった数字が入ります。（１０やら５等）

そこまではＩＦ関数で式を作ったのですが、ここからが問題でして…

Ｂ２から横に１から１００まで、記号（Ｂ３から横に数字）を入力していった時のＢ３列の合計が仮に２１２とします。

合計した値が２００を越えてしまった場合、Ｂ４からの列に数字を入力するようにしたいのです。

（２００ギリギリ前くらいまではＢ３の列で、それを越える時からＢ４の列に入力します。その際、Ｂ３列に入る数字は消えます）

なおかつ可能であれば、このＢ４からの列に入るのはランダムにできるといいのですが…（画像では○の数字を下ろしていますが、☆の数字をおろしても良いし、△の数字をおろしても良い）

●計算式によって入力された数字（Ｂ３列）を特定の数字を越えた場合、別の枠（Ｂ４列）に移す関数はありますでしょうか？？

●また、それをランダム（関数）で選ぶことは可能でしょうか？？

よろしくお願いします。

回答No.9

kagakusuki · ベストアンサー

もしかしますと、次の部分が悪影響をしているのかも知れません。
　ANo.6で述べたB3セルに入力する数式

=IF(ISNA(MATCH(B$2,{"△","○","☆"},0)),"",IF(OR(ROWS($2:2)>INT((SUMPRODUCT(LOOKUP($B$2:$IV2&"@",{"@","△@","○@","☆@";0,3,5,10}))-0.5)/190)+1,COUNT(B4:B$17)>0,RAND()*(190*ROWS($2:2)-SUM($A$3:A3))>190-SUM($A3:A3)),"",LOOKUP(B$2,{"△","○","☆";3,5,10})))

の途中にある

$IV2

の部分を、試しに

$IV$2

に変えて、数式を

=IF(ISNA(MATCH(B$2,{"△","○","☆"},0)),"",IF(OR(ROWS($2:2)>INT((SUMPRODUCT(LOOKUP($B$2:$IV$2&"@",{"@","△@","○@","☆@";0,3,5,10}))-0.5)/190)+1,COUNT(B4:B$17)>0,RAND()*(190*ROWS($2:2)-SUM($A$3:A3))>190-SUM($A3:A3)),"",LOOKUP(B$2,{"△","○","☆";3,5,10})))

に変更してから、B3セルをコピーして、他のセルに貼り付けてみて下さい。

お礼

いろいろ回答していただき、非常に勉強になりました。

おかげさまで、思ってたよりかなり良い表ができそうです。

まだまだ他にもいろいろな表を作らないといけないので大変です…
（もぅ少し試行錯誤してみます…）

ありがとうございましたm(__)m

補足

ありがとうございますm(__)m
おかげさまで、無事ランダム表示されました。

長々と、親切＆丁寧にご回答していただいてすごく感謝しておりますm(__)m

最後に確認しておきたいのですが…

ＡＮｏ５でおっしゃってた最下段以外に表示される数字の各行毎の合計を200近くになる様にする（極端に言うと、合計が２０１の場合、Ｂ３セルには１９１、Ｂ４には☆１つ（１０）のみランダムに移る）

これのやり方は、ＡＮｏ２の式（２００を越えると、最右側の数字がI行下におりるが、ランダムにはおりない）を使ってＲＡＮＤを入力すればよろしいということですよね…たぶん…

回答No.8

kagakusuki

>今度は表示がうまくいかなくて…

　[ツール]の[オプション]で、計算方法を手動にしますと、キーボードのF9キーを押す等の方法で、人間が計算開始させる指示を出してあげないと、パソコンは計算を開始しませんから、コピー元のセルと同じ表示が並ぶ事になりますが、その様な状態なのでしょうか？
　もし、そうであれば、キーボードのF9キーを押してみて下さい。

>循環なんとかが…みたいな感じででてきたり、少しいじると、上の列が１９０を越えるとランダムで下にいくようにはなったのですが、上下両方に数字が入ってたり、両方なかったり…

　もしかすると、コピーと貼り付けを行った後で、数式を貼り付けたセルか、又は記号を入力する予定のセルの、どれかを削除されたのでしょうか？
　もし、そうであれば、B3セルのコピーから操作をやり直して下さい。
　又、数式をいじるのは、１つのセルだけで行い、その数式を変更したセルを、B3セルの代わりにコピーして、B3から、記号を入力する予定の範囲の右端の列（この数式では更に右側にある列にまで貼り付けても構いません）の16行目にかけての範囲に貼り付けて下さい。

補足

ご回答ありがとうございますm(__)m

ＡＮｏ７のやり方をみて、私のやり方も間違っていないですね。（ホッ）

具体的な状態としては…Ｂ２記号を入力すると、『数式を計算できません。数式のセル参照は計算結果を参照（循環参照）しています。次のいずれかを実行………』とエラー表示がでます。（Ｂ３～１６には何も表示されません。

また、Ｃ２より右方向に記号を入力すると、Ｃ３より右に数字（１０や５）はでるのですが、Ｃ４～１６にかけての右方向に『＃Ｎ/Ａ』とずらーっと表示されます（１９０を越える辺りから、記号（２列目）の下の数字（３列目）が消え、４列目～１６列目にかけてには
『＃Ｎ/Ａ』だけが残ります。（この際、Ｂ２に記号を入れても、何も表示されません…）

ちなみに自動計算になっていました。

回答No.7

kagakusuki

>『B3セルをコピーして、B4～B16の範囲に貼り付けて、B3～B16の範囲をコピーし、同じ行のC列から右に向かった範囲に貼り付ける』でよろしいんですよね…

　質問者様も既に御存知の事とは思いますが、念のために書きます。
「B3セルをコピーして、」は
セルに数式を入力した後で、
キーボードのEnterキーを押し、
その後で「B3セルをクリックする」か「キーボードの矢印キーを使って黒い太枠が囲んでいる位置をB3セルに合わせる」事で、B3セルを選択し、
B3セルが黒い太枠で囲まれている状態のままで、
「B3セルを右クリック」するか又は「メニューの[編集]をクリック」
すると現れる選択肢の中の[コピー]をクリックする
という操作になります。

　「B4～B16の範囲に貼り付けて、」は、
B4セルにカーソルを合わせて、
マウスの左ボタンを押しっ放しにし、
マウスの左ボタンを押したままカーソルをB16に合わせる事で、
B4～B16の範囲内のセルの全てが同時に、１つの黒い太枠で囲まれている状態にした後で、
「黒い太枠の内側を右クリック」するか又は「メニューの[編集]をクリック」
すると現れる選択肢の中の[貼り付け]をクリックする
という操作になります。

　しかし一般的には、
「B3セルをクリックする」か「キーボードの矢印キーを使って黒い太枠が囲んでいる位置をB3セルに合わせる」事で、B3セルを選択し、
B3セルが黒い太枠で囲まれている状態にした後で、
黒い太枠の右下の隅にある黒い点にカーソルを合わせて、
カーソルの形状が「＋」の形になっている事を確認し、
その状態のままマウスの左ボタンを押しっ放しにし、
マウスの左ボタンを押したままカーソルをB16に合わせる事で、
B3～B16の範囲内のセルの全てが同時に、１つの黒い太枠で囲まれている状態にしてから、
マウスの左ボタンから指を放す
という操作の方が楽なので多用されます。

「B3～B16の範囲をコピーし、」は、
B3セルにカーソルを合わせて、
マウスの左ボタンを押しっ放しにし、
マウスの左ボタンを押したままカーソルをB16に合わせる事で、
B3～B16の範囲内のセルの全てが同時に、１つの黒い太枠で囲まれている状態にした後で、
「黒い太枠の内側を右クリック」するか又は「メニューの[編集]をクリック」
すると現れる選択肢の中の[コピー]をクリックする
という操作になります。

「同じ行のC列から右に向かった範囲に貼り付ける」は、
C3セルにカーソルを合わせて、
マウスの左ボタンを押しっ放しにし、
マウスの左ボタンを押したままカーソルを、記号を入力する予定の範囲の右端の列（この数式では更に右側にある列にまで貼り付けても構いません）の16行目にあるセルに合わせる事で、
その範囲内のセルの全てが同時に、１つの黒い太枠で囲まれている状態にした後で、
「黒い太枠の内側を右クリック」するか又は「メニューの[編集]をクリック」
すると現れる選択肢の中の[貼り付け]をクリックする
という操作になります。

　しかし一般的には、
B3セルにカーソルを合わせて、
マウスの左ボタンを押しっ放しにし、
マウスの左ボタンを押したままカーソルをB16に合わせる事で、
B3～B16の範囲内のセルの全てが同時に、１つの黒い太枠で囲まれている状態にする事で、
B3～B16の範囲を選択されている状態にした後で、
黒い太枠の右下の隅にある黒い点にカーソルを合わせて、
カーソルの形状が「＋」の形になっている事を確認し、
その状態のままマウスの左ボタンを押しっ放しにし、
マウスの左ボタンを押したまま、記号を入力する予定の範囲の右端の列（この数式では更に右側にある列にまで貼り付けても構いません）の16行目にあるセルに合わせる事で、
その範囲内のセルの全てが同時に、１つの黒い太枠で囲まれている状態にしてから、
マウスの左ボタンから指を放す
という操作の方が楽なので多用されます。

回答No.6

kagakusuki

>ランダムの式ですが…なぜかエラーがでちゃいまして…

　重ね重ね申し訳御座いません、未だ抜けている部分が御座いました。
　正しくは

=IF(ISNA(MATCH(B$2,{"△","○","☆"},0)),"",IF(OR(ROWS($2:2)>INT((SUMPRODUCT(LOOKUP($B$2:$IV2&"@",{"@","△@","○@","☆@";0,3,5,10}))-0.5)/190)+1,COUNT(B4:B$17)>0,RAND()*(190*ROWS($2:2)-SUM($A$3:A3))>190-SUM($A3:A3)),"",LOOKUP(B$2,{"△","○","☆";3,5,10})))

です。
　又、前にも少し触れましたが、この数式では、例え表示が2行目までしかない場合であっても、3行目～17行目の範囲内に、数値が入力されていますと、正常な表示が出来ません。

>もしお手数でなければギリギリの方も教えていただければ助かります。

　前回の回答で述べました通り、ANo.2の数式で出来ます。

>式の中のＬＯＯＫＵＰ内にある、☆や○とそれにたいする数字（１０や５）を例えば、別枠（Ｍ５＝☆）（Ｍ６＝１０）に入力しておき、式で（Ｍ５，…，：Ｍ６，…）なんて感じにすることって可能なんでしょうか？

　LOOKUP内の数値をセル参照に変える事は可能です。
　但し、M5、M6は使用済みですから、別枠を作られるのであれば、18行目以下か、又は別Sheetになさって下さい。
　それから、別枠を作られるのであれば、例えば

　　　　A列　B列　C列
18行目　 @
19行目　△@　△　　3
20行目　○@　○　　5
21行目　☆@　☆　　10

とした上で、

LOOKUP(B$2,{"△","○","☆";3,5,10})

の部分を、

VLOOKUP(B$2,$B$19:$C$21,2,0)

に変えた方が良いと思います。
　同様に、

LOOKUP($B$2:$IV2&"@",{"@","△@","○@","☆@";0,3,5,10})

の部分を、

VLOOKUP($B$2:$IV2&"@",$A$18:$C$21,3,0)

に変え、

MATCH(B$2,{"△","○","☆"},0)

の部分を、

MATCH(B$2,$B$19:$B$21,0)

に変えた方が良いと思います。

補足

いろいろありがとうございました。
なんとか無事入力ができました。…が今度は表示がうまくいかなくて…

以前ＡＮｏ３でおっしゃってた、『B3セルをコピーして、B4～B16の範囲に貼り付けて、B3～B16の範囲をコピーし、同じ行のC列から右に向かった範囲に貼り付ける』でよろしいんですよね…

もしかしたら、範囲をコピーとか右に向かって貼り付けたりとかのやり方が違ってるのかも…

循環なんとかが…みたいな感じででてきたり、少しいじると、上の列が１９０を越えるとランダムで下にいくようにはなったのですが、上下両方に数字が入ってたり、両方なかったり…
さらにいじると、下におりなくなったり…

先におっしゃってた数式を入れてはいけない。ということが原因でしょうか…

回答No.5

kagakusuki

>☆等を入力しても数字が出てこないのは、先におっしゃってたことが原因でしょうか…？

　申し訳御座いません。
　数式を手入力で回答文に入力する際に、抜けてしまった箇所が御座いました。
　B3セルに入力する数式ですが

=IF(ISNA(MATCH(B$2,{"△","○","☆"},0)),"",IF(OR(ROW($2:2)>INT((SUMPRODUCT(LOOKUP(B$2&"@",{"@","△@","○@","☆@";0,3,5,10}))-0.5)/190)+1,COUNT(B4:B$17)>0,RAND()*(190*ROWS($2:2)-SUM($A$3:A3))>190-SUM($A$3:A3)),"",LOOKUP(B$2,{"△","○","☆";3,5,10})))

は間違いで、正しくは

=IF(ISNA(MATCH(B$2,{"△","○","☆"},0)),"",IF(OR(ROWS($2:2)>INT((SUMPRODUCT(LOOKUP(B$2:$IV&"@",{"@","△@","○@","☆@";0,3,5,10}))-0.5)/190)+1,COUNT(B4:B$17)>0,RAND()*(190*ROWS($2:2)-SUM($A$3:A3))>190-SUM($A$3:A3)),"",LOOKUP(B$2,{"△","○","☆";3,5,10})))

です。

>ランダムの件ですが、２００よりさらに低く（１００ちょっと）になるのですと、やはりキビシイかもしれません…

　表示される数字の各行毎の合計に、極端な差が出るのは良くないのではないかと勝手に考えてしまい、その様にしたのですが、余計な御世話だったのでしょうか。
　もし、最下段以外に表示される数字の各行毎の合計を200近くになる様にしますと、例えば、表示される全ての数字の合計が、401の様に200の倍数より少しだけ大きな値の場合には、表示がランダムに行われているのか否かには関わらず、最下段に表示される数字の個数が極端に少なくなります（401の場合は最下段に表示される数字は、ほんの数個程度になると思います）が、その方が宜しいのでしょうか？

>最初に教えて頂いた式（ランダムではなく、２００を越えると下におりる式）で同じ式を２回目、３回目になるたびに下に付け加えることは可能でしょうか…？

　ANo.2の数式のままでも、合計値が増えれば、自動的に表示行数を増やします。（13行にまで分ける事が出来ます)
　その点は、ANo.3の数式（の間違いを訂正した上記の数式）でも同様です。
　同じ式のままでは、コピーして別の行に貼り付けた後、貼り付け先の行番号に合わせて、数式の細部を修正しなくてはなりません。
　貼り付けても修正が必要ではない数式を組む事は、おそらく可能だとは思いますが、数式が更に長くなります。
　ですから、Excel Sheetの横幅がIV列まででは不足するのでしたら、記号を横に並べるのではなく、縦に並べる事をお勧めします。
　それならば、記号を65535個まで並べる事が出来ますが如何でしょうか。
　そのための数式は、質問者様の御承諾を頂いた後で作成します。

補足

度々手入力でのご回答ありがとうございますm(__)m

ランダムの式ですが…なぜかエラーがでちゃいまして…ただ今試行錯誤中です(*_*)

それから、ランダムにおろす件ですが決して余計なお世話なんかではないですよ（むしろ感謝していますので）

ただ、私が思ってたのは、おっしゃる通りギリギリまで上にきていて、一部下にくるというやり方を想像しておりましたので…もしお手数でなければギリギリの方も教えていただければ助かります。
（というのも、実際にどんな感じになるかを見てから考えようかと思っていたので…まだどちらがいいのか考え中でして）

同じ式を下につけれる？件ですが、そこまで横には長くならないと思いますので今のまま（横並び）でたぶん大丈夫かな？と思います。（式がどんな感じで長くなるのかが検討つかないですけど…）
縦ですと…６万個も…すごい数。。。

それから、後１つお聞きしたかったのですが…

式の中のＬＯＯＫＵＰ内にある、☆や○とそれにたいする数字（１０や５）を例えば、別枠（Ｍ５＝☆）（Ｍ６＝１０）に入力しておき、式で（Ｍ５，…，：Ｍ６，…）なんて感じにすることって可能なんでしょうか？

☆の数が変更になった場合、式の数字を直接変えれば良い話なのですが…別セルの数字を変更すれば…なんていうこともできるのかな？と思いまして…

ご注文ばかりつけてすみませんm(__)m
よろしくお願いたしますm(__)m

回答No.4

kagakusuki

回答番号:ANo.1,2,3です。
　先程の回答に関してですが、先述した様に、関数のみで自動的にランダムな処理を行う場合、自動計算される設定を解除しないと、ファイルを開く際や、保存する際、及び"その数式が入力されているBookが開いている時に、同時に開いている全てBookの中のセルに入力した際"に、表示が変わってしまいます。
　そのため、一度計算結果が表示された段階で、計算結果の値を別の場所にコピーしておく事をお勧め致します。
　その方法は、

A1セルの左上、行番号が表示されている欄と列番号が表示されている欄が交わっている箇所にある、長方形の部分をクリックして、そのシート全体を範囲選択
　　↓
マウスを右クリック
　　↓
現れた選択肢の中にある[コピー]をクリック
　　↓
必要となるデータが入力されていないシートのA1セルをクリック
　　↓
マウスを右クリック
　　↓
現れた選択肢の中にある[形式を選択して貼り付け]をクリック
　　↓
現れた「形式を選択して貼り付け」ウィンドウの中にある[値]にチェックを入れる
　　↓
[OK]ボタンをクリック

以上です。

　若しくは、自動計算を解除されても良いと思います。
　その方法は、

メニューの［ツール］をクリック
　　↓
現れた選択肢の中にある[オプション]をクリック
　　↓
現れた「オプション」ウィンドウの中の「計算方法」タグをクリック
　　↓
「計算方法」欄の「手動」と記されている箇所にチェックを入れ、その下にある「保存前に再計算」と記されている箇所のチェックを外す
　　↓
[OK]ボタンをクリック

以上です。

　尚、この設定はBookの設定ではなく、Excelの設定ですから、全てのBookに対して有効ですので、後で別のExcelファイルを開いた際にも、自動計算は行われなくなります。
　上記の設定を行った後で、手動で計算を開始させる場合には、キーボードのF9キーを押して下さい。
　又、設定を元に戻す際には、「オプション」ウィンドウの「計算方法」タグの中の「自動」と記されている箇所にチェックを入れて下さい。（自動計算モードにしたままで、本件で作成したファイルを開くと、表示が変わってしまいますから、注意して下さい）

補足

ご回答ありがとうございますm(__)m

ランダムの件ですが、２００よりさらに低く（１００ちょっと）になるのですと、やはりキビシイかもしれません…

今はどんな感じになるのかな？と試しに入力している所ですが…なかなか難しいですね～…☆等を入力しても数字が出てこないのは、先におっしゃってたことが原因でしょうか…？
（それとも私のやり方が違ってたりして…）
もう少し頑張ってみますm(__)m（仕事の時くらいしかできないので…）

すみませんあと１つ質問なのですが…

最初に教えて頂いた式（ランダムではなく、２００を越えると下におりる式）で同じ式を２回目、３回目になるたびに下に付け加えることは可能でしょうか…？（Ｂ２～４までが１回目、Ｂ５～７までが２回目）…（４と５の間には線がを入れたりして区切ったり）

　Ｂ　Ｃ　Ｄ
２☆　○　△
３１０　　３
４　　５
５○　△　☆…
６５　　　１０
７　　３

どちらも合計２００を越えると下におりる式が入っているのですが、先におっしゃってたことからすると範囲とかの問題が出てきてしまうのでしょうか…

今は１回目だけで十分なのですがゆくゆくは…２回目ある…のかな。。

回答No.3

kagakusuki

回答番号:ANo.1,2です。
　試した結果、複数の行に対して、完全にランダムに数字を割り振ると、
表示される数字の合計が多い行と少ない行が出て来るため、
前半で数字の合計が大きかったの列では、列番号が若い内に200に達するため、右端に近い列になると、数字が現れなくなる事があります。
　又、数字の合計が丁度200になる事は少ないため、200に達した列に、数字が現れなくなる様にしますと、例えばある行において合計が191になっている列の次に☆が入ると、合計が201になってしまい、200を超えてしまいます。
　後述の数式では、これらの不具合を避けるために、完全にランダムに割り振る事は止めて、他の行と比べて、数字の合計が190に「より近く」なるに従って、数字が割り振られる確率が低くなる様にした上で、数字の合計が190以上になった行には、数字が割り振られない様にしています。
　そのため、各行の数式の合計値の間の差が少なくなってしまいましたが、その点は問題になりはしないでしょうか？
　又、全ての数字の合計値を190で割った際の余りが少ない場合には、各行における数字の合計は200よりもかなり少なくなりますが、その点は問題になりはしないでしょうか？（例えば、全ての数字の合計値が201の場合、2行に振り分けると、各行における数字の合計は、平均して100.5に近づく筈です）
　それから、前回の回答でも述べましたが、Excelの設定が標準的な設定である自動計算を行うモードのままでは、関数のみでランダムな処理をすると、ファイルを開いたり、入力をしたりする度に、表示が変わる事になります。
　もし、これらの事が問題ではない場合には、以下の様にされると良いと思います。

　まず、B3セルに次の数式を入力して下さい。

=IF(ISNA(MATCH(B$2,{"△","○","☆"},0)),"",IF(OR(ROW($2:2)>INT((SUMPRODUCT(LOOKUP(B$2&"@",{"@","△@","○@","☆@";0,3,5,10}))-0.5)/190)+1,COUNT(B4:B$17)>0,RAND()*(190*ROWS($2:2)-SUM($A$3:A3))>190-SUM($A$3:A3)),"",LOOKUP(B$2,{"△","○","☆";3,5,10})))

　次に、B3セルをコピーして、B4～B16の範囲に貼り付けて下さい。
　続いて、B3～B16の範囲をコピーして、同じ行のC列から右に向かった範囲に貼り付けて下さい。
　それから、A3～A16の範囲と数式を入力したのと同じ列の17行目は、判定のために使用していますから、セルの削除や切り取り、及び数値の入力は行わない様にして下さい。（文字列や記号は入力しても構いません）

回答No.2

kagakusuki

回答番号:ANo.1です、こたらこそ勘違いをしてしまいすみません。

　合計値が200を超える毎に、数字を表示させる行を、1つ下の行に移す場合には、
まず、B3セルに次の数式を入力して下さい。

=IF(ISNA(MATCH(B$2,{"△","○","☆"},0)),"",IF(AND(SUM($A$3:A$15)+LOOKUP(B$2,{"△","○","☆";3,5,10})>200*(ROWS($2:2)-1),SUM($A$3:A$15)+LOOKUP(B$2,{"△","○","☆";3,5,10})<=200*ROWS($2:2)),LOOKUP(B$2,{"△","○","☆";3,5,10}),""))

　続いて、B3セルをコピーして、B3から右端の列の15行目にかけての範囲に貼り付けて下さい。
　以上で完成です。

　それから、ランダムに表示させる件ですが、Excelの設定が標準的な設定である自動計算を行うモードのままでは、関数のみでランダムな処理をすると、ファイルを開いたり、入力をしたりする度に、表示が変わる事になります。
　これは、RAND関数という乱数を発生させる関数を使用して、ランダムな表示を行うのですが、ファイルを開いたり、入力をしたりする度に、RAND関数の部分も再計算されるため、RAND関数によって発生させた乱数の値も変わり、乱数に従っているランダムな表示も変化してしまうからです。
　再計算しない様に設定する方法はありますが、別のExcelファイルを一緒に開いた際に、別のExcelファイルを再計算させると、一緒に開いていたファイルも同時に再計算されるため、注意する必要があります。
　それでも宜しいのでしたら、現在検討中ですので、お待ち下さい。（1～2日かかるかも知れません）

補足

度重なる回答ありがとうございますm(__)m

おかげさまで何とか、行を１つ下げることができました。

表を見て思ったのですが、やはりランダムの方がいいかもしれません…

ぜひその方法をお教えしていただけるとありがたいです。
よろしくお願しますm(__)m

回答No.1

kagakusuki

3行目に入力する数式は、IF関数のみで値を求めるよりは、次の様にされた方が宜しいかも知れません。
　まず、B3セルに次の数式入力して下さい。

=IF(ISNA(MATCH(B$2,{"△","○","☆"},0)),"",LOOKUP(B$2,{"△","○","☆";3,510}))

　そして、B3セルをコピーして、B3から右側に向かって貼り付けて下さい。

　それと、少々確認したい事があるのですが、

>合計した値が２００を越えてしまった場合、Ｂ４からの列に数字を入力するようにしたいのです。

>２００ギリギリ前くらいまではＢ３の列で、それを越える時からＢ４の列に入力します。

という事は合計値が丁度200（200ギリギリ前でもなく、200を超えてもいない値）の場合は4行目に表示させると考えれば宜しいのでしょうか。
　もし、そうだとすれば、
B4セルには、

=IF(SUM($B$3:B$3)<200,SUM($B$3:B$3),"")

B5セルには、

=IF(SUM($B$3:B$3)<200,"",SUM($B$3:B$3))

と入力した後、B4～B5の範囲をコピーして、同じ列のC列から右方向に向かって貼り付けて下さい。

　又、入力する数式を次の様に変更すると、3行目の数値データが無くても、同様の結果を表示させる事が出来ます。

B4セルの数式、

=IF(MAX($A$4:A$4)+IF(ISNA(MATCH(B$2,{"△","○","☆"},0)),0,LOOKUP(B$2,{"△","○","☆";3,510}))<200,MAX($A$4:A$4)+IF(ISNA(MATCH(B$2,{"△","○","☆"},0)),0,LOOKUP(B$2,{"△","○","☆";3,510})),"")

B5セルの数式、

=IF(MAX($A$4:A$4)+IF(ISNA(MATCH(B$2,{"△","○","☆"},0)),0,LOOKUP(B$2,{"△","○","☆";3,510}))<200,"",MAX($A$4:A$4)+IF(ISNA(MATCH(B$2,{"△","○","☆"},0)),0,LOOKUP(B$2,{"△","○","☆";3,510})))

　それから、記号ごとに別個に合計値を計算して行き、各記号ごとに合計値が200を超えた場合に、5行目に表示させる場合には、入力する数式を次の様に変更して下さい。

B4セルの数式、

=IF(OR(ISNA(MATCH(B$2,{"△","○","☆"},0)),SUMIF($B$2:B$2,B$2,$B$3:B$3)>=200),"",SUMIF($B$2:B$2,B$2,$B$3:B$3))

B5セルの数式、

=IF(OR(ISNA(MATCH(B$2,{"△","○","☆"},0)),SUMIF($B$2:B$2,B$2,$B$3:B$3)<200),"",SUMIF($B$2:B$2,B$2,$B$3:B$3))

　又、この場合も入力する数式を次の様に変更すると、3行目の数値データが無くても、同様の結果を表示させる事が出来ます。

B4セルの数式、

=IF(OR(ISNA(MATCH(B$2,{"△","○","☆"},0)),COUNTIF($B$2:B$2,B$2)*LOOKUP(B$2,{"△","○","☆";3,510})>=200),"",COUNTIF($B$2:B$2,B$2)*LOOKUP(B$2,{"△","○","☆";3,510}))

B5セルの数式、

=IF(OR(ISNA(MATCH(B$2,{"△","○","☆"},0)),COUNTIF($B$2:B$2,B$2)*LOOKUP(B$2,{"△","○","☆";3,510})<200),"",COUNTIF($B$2:B$2,B$2)*LOOKUP(B$2,{"△","○","☆";3,510}))

補足

ご回答ありがとうございますm(__)m
参考になります。

誤解を招くような書き方になってしまいすみませんm(__)m

Ｂ３列の合計数が ２００を越えた場合、通常Ｂ３列に入力されている数字をそのままＢ４列に移せるでしょうか？

例えばＢ３列の合計が２０１になった場合、２００以内に減らしたいのでＢ３列の数字（２００を切るよう適当に）がＢ４列に移ります。

合計が２００以内の場合

　　Ｂ Ｃ Ｄ
２　☆ ○ △
３　10 5 3
４ 空白の状態

２００を越えると

　Ｂ Ｃ Ｄ
２　☆ ○ △
３　10 5 空白になる
４　 空白 3 ←Ｃ３から移動

上記のように入力されてある数字を上のセルから下のセルに移すことは可能でしょうか？

また、２００以内に減らせば良いので、２０１の場合、上記のようにＤ３をそのままおろしてもいいですし、Ｂ３をおろしても、Ｃ３をそのままおろしてもＯＫなのです。

このようにランダムでおろすことは可能なのでしょうか？

わかりずらい説明ですみませんが、よろしくお願いしますm(__)m