エクセルの関数について、お知恵をお貸しください。 添付のような表があり、①の集計を②(H3〜H8、ｉ

エクセルの関数について、お知恵をお貸しください。
添付のような表があり、①の集計を②(H3〜H8、ｉ3〜ｉ8)に表示させ、個人ごとの初算と検算の件数がでるように関数を組みたいと考えております。

色々試してみてはいるのですが、私の知識だけではどうにもこうにも解決できず…
現在、電卓で計算しているため、かなりの時間を要しております。
50人分算出する必要があり、人の手では限界を感じています。
どなたか効率的な算出方法を教えていただけないでしょうか。
①の体裁は変更可能です。
何卒よろしくお願います。

<参考 ①の表の見方について>
◆初算者欄に3名入力ある時は、業務毎の件数を3分けします。業務311なら、66件をAさんBさんCさんの3名で処理するので、1人あたり22件です。
◆検算者も初算者と同様です。業務311なら、
初算者AさんCさん分をBさんが検算します。Bさんの検算件数は44件です。
初算者Bさん分をAさんが検算します。Aさんの検算件数は22件です。
◆Aさんの件数算出式は以下の通りです。
・初算
業務111(23件)+業務122(2件)+業務133(9件)+業務311(66件÷3=22件)+業務322(48件÷3=16件)+業務444(165件÷3=55件)=127件
・検算
業務222(52件÷2=26件)+業務311(66件÷3=22件)+業務322(48件÷3=16件)=64件

No.9 回答者： hakobulu 回答日時： 2018/08/26 19:27

＃７です。

＞業務122の検算は、C＝1・E＝1です！
：
そうでしたか。
わたしが勘違いしていました。
そうしますと、「業務件数÷検算者数＝検算件数」という数式が常に成立すると考えて良いのですね。
であるなら、検算件数も自動で表示できますね。
下記画像では、F列に「検算者数」という列を挿入しています。
H3の「検算件数」の式は下記のとおりです。
=IF(G3<>"",IF(B3<>"",B3/F3,H2),"")
これを下に向けて必要な行数だけコピペする。

No.8 回答者： hakobulu 回答日時： 2018/08/26 18:56

＃６です。

ご返事ありがとうございます。
早速手順からです。
都合上、業務３１１でシミュレーションします。
前回「カーソルを下に移動して」という表現が誤っていましたね。
申し訳ありません。
「真下のセルに移動して」と訂正させてください。

１．
（業務）　３１１
（件数）　66
（初算者数）３
（初算者）A
の順番で４つのセルに入力した状態です。（初算件数が自動表示されている）
業務の手順を、わたしがまだよく理解していないと思われますので、初算者人数のCOUNTA関数は今のところ保留にしておきます。
https://gyazo.com/496971d800cb6d3b9b5ae752bb305f91

２．
（初算者）A を入力してエンターキーを押すと真下のセル（D4）に移動するので、そこに、
（初算者）B と入力する。
続けてセル（D５）に、
（初算者）C と入力する。
https://gyazo.com/5cea3a563d662eaafc854b865c81e08d
初算者が１０人いても、下のセルにどんどん入力していけます。
その後、検算者の入力を行なう。

因みに、初算者人数は結構多いのでしょうか。
あまり多いと数えるのは確かに手間ですね。
取り急ぎここまで投稿します。
疑問点はどんなことでも補足質問していただけると助かります。
できる範囲でですが、でしゃばって一緒に考えさせていただきたいと思っているところですので。

No.7 回答者： hakobulu 回答日時： 2018/08/26 03:02

＃６です。

すみません。

業務１２２の検算ですが、
C=2
E=2
で良かったでしょうか？
ひょっとして１でしたか？
CとEが一件づつ検算したということですか。
私の総計が違っているみたいなので。
検算件数も、業務件数を検算者の数で割って（常に）出すのなら、初算件数と同じく自動計算できます。

この回答へのお礼

業務122の検算は、C＝1・E＝1です！
CさんとEさんが1件ずつ検算をしたということです◎

お礼日時：2018/08/26 17:00

No.6 回答者： hakobulu 回答日時： 2018/08/26 02:25

＃５です。

１．
読みづらい文章だったと思いますが、ご返事をいただきありがとうございます。
＃２でお示しした表は、業務毎に初算者や検算者を表示するようにしたものでした。
質問者さんの業務内容をよく把握できていないため、的外れの回答になってしまったかもしれませんね。
申し訳ないことです。
ただ、ご参考までに申し上げておきますと、人数はいくら増えても問題ありません。
入力手順は、たとえば、業務１１１⇒初算者⇒初算者件数⇒検算者⇒検算件数の順です。
初算者が増える場合は、初算者A を入力後カーソルを（横ではなく）下に移動して次の初算者B などを入力します。
検算者の場合も同様で、人数が増えるに従って業務１１１の行数が増えていくわけです。極端に言えば最後の行まで伸ばせます。
そして、この場合でも右側集計欄の計算式は常に正しく機能します。
また、その元となっている非表示にした計算式もエクセルの最後の列まで伸ばすことができる。
行・列共に、どこまでも増やしていくことができるわけです。

２．
たぶん、私自身、現在もまだよく把握できていないと思いますので、今回は、初算件数を計算する方法についてだけ少し。
たとえば初算者の人数を手入力するのは不都合があるでしょうか。
もし可能なら、画像のようにすることもできます。
この表では、C 列に「初算者数」を挿入しています。
「初算件数」F3 の式は⇒　=IF(D3<>"",IF(B3<>"",B3/C3,E2),"")
これを下にコピーすると「初算件数」の自動表示は可能です。
割り切れない場合の問題は残りますが、ROUND関数などで丸めることができるようなら問題はないでしょう。

３．
＞①の表は、チームメンバーに配布する役割分担表として活用しているものです。(全メンバーがどの業務を誰が担当しているか把握しておく必要があるため)
：
わかりました。
わたしの表の場合も、
たとえば業務322は、
件数＝４８
初算者A⇒検算者B
初算者B⇒検算者A
初算者C⇒検算者B
という関係性が一目瞭然になるように作ったつもりだったんですけどね。＾＾；
わたしも素人なので却ってご迷惑をおかけしてしまったかもしれません。
説明文の中で不明な点や誤解があるようでしたら教えてくださると有難いです。

この回答へのお礼

何度もご丁寧にありがとうございます！！！

1 「入力手順は、たとえば、業務１１１⇒初算者⇒初算者件数⇒検算者⇒検算件数の順です。初算者が増える場合は、初算者A を入力後カーソルを（横ではなく）下に移動して次の初算者B などを入力します。」について、私の勉強不足でうまくいきませんでした…
カーソルを下に移動して…のやり方がよくわからないので、再度ご教示いただけないでしょうか？

2 IF関数の複数条件が苦手なので、式をのせていただいて大変助かりました！
初算者人数はCOUNTA関数で算出しようと思います！

3 いやいやいや、私の伝え方が悪いのです…親身に相談に乗っていただき感謝の気持ちでいっぱいです^ ^

お礼日時：2018/08/26 16:58

No.5 回答者： hakobulu 回答日時： 2018/08/24 16:03

＃２です。

またお邪魔します。
以前、わたしも拙いながら自我流でエクセルを仕事に活用していたことがあるので、気になってしまって・・・。
わたしであれば、このように作ってみるが・・・という程度の代物ですので、参考にできるところだけ参考になさっていただければ幸いです。
説明で腑に落ちない点がございましたら、ご遠慮なく補足なさってみてください。
また、このような形にすると不都合が生じるようでしたら、その点もご指摘いただけるとありがたいです。

１．
まず訂正です。すみません。
×
HI、L1 、P1・・・・のように４列ごとの一行目は「A」と表示されるので、
〇
HI、L1 、P1・・・・のように４列ごとの２行目は「A」と表示されるので、

２．
集計欄が見ずらいので、やはり、gmiicnhai さんのように表示させるべきですね。
まず、G列からK列までを選択してから「（列の）挿入」をします。
次に、
H2⇒ =M2（ A と表示される）
H3⇒ =Q2（ B と表示される）
H4⇒ =U2（ C と表示される）
・
・
・
・
のように、５０人分の担当者名が縦に並ぶよう式を作ります。
続いて、
H 列に表示された担当者名（セル指定の計算式が入っている）をすべて選択状態にしてからコピーし、I~K 列へペーストする。
例として、担当者 C までの操作すると以下のようになります。
https://gyazo.com/cf761404289a864cff39543ccead5d57
これで集計欄も、当初のgmiicnhai さんのように表示されます。
シミュレーションを、ある程度繰り返し、問題がないことを確認後、L 列から右側はすべて非表示にするとよろしいでしょう。
また、この時点で、万が一計算式を書き換えてしまった場合のために、保存用としてワークシートのコピーをひとつ作成しておくと良いと思います。
ご存知とは思いますが、計算式の入っているセルはすべて、というか、Ｇ列を含めて右側の列全体（非表示列も含めて）にはセルのロックをかけ、シートの保護を行っておくのが無難です。

３．
あと、余計なことかもしれませんが、一年分をひとつのワークシートにしても良いと思います。
たとえば、今回のワークシートを「２０１９年度業務」という名前に変更して一旦、保存する。
次に、今回作ったの表の SHEET 名を「２０１９年４月」に変更する。
そして、この SHEET のコピーを１１個作り月別の名前に変更する。
さらに、もうひとつSHEETを挿入し、「２０１９年度集計」という名前にする。
ここには、先に作った「２０１９年４月」「２０１９年５月」「２０１９年６月」・・・「２０２０年３月」まで１２個のSHEETのデータを集計する計算式を作っておく。（これはそう難しくないでしょう）
正確に言うならデータの集計を集計するということで、要するに各月別SHEETのG列からK列までのデータを反映するように計算式を作っておく、という意味です。

これで、「２０１９年度業務」というワークシートの中に、「２０１９年４月」～「２０２０年３月」までの月別データ、および「２０１９年度集計」という１３個のSHEETができたことになります。
このようにしておくと、いつでも現段階までの総集計を確認することができるようになる。（必要であれば、ですが）
年度が替わる場合は、「２０１９年度業務」というワークシートをコピペして、「２０２０年度業務」という名前のワークシートを新しく作る。
月別や集計のSHEET名を変更する必要はありますが、各月SHEETのデータ入力欄（A~F列）のみdelete、または、「数式と値のクリア」をすれば全データが初期値に戻るので簡単に新しいワークシートを作ることができます。
「削除」ではありませんので、くれぐれもご注意を。
新年度が来るのを待たずに早い時期から数年分を準備してしまうのも良いでしょう。
気が楽になります。

この回答へのお礼

ご連絡が遅くなり申し訳ありません(><)
親切な回答ありがとうございます！！
こんなに親身になってくださる方がいらっしゃるとは！と感激しております✧*゜
そして、とっても勉強になります！
=IF($C3="A",$D3,0)の計算式は早速使わせていただきます◎

以下補足です。
私の言葉足らずで、わかりにくく大変申し訳ありません。
①の表は、チームメンバーに配布する役割分担表として活用しているものです。(全メンバーがどの業務を誰が担当しているか把握しておく必要があるため)
また、件数は月平均です。日々の処理件数を集計したい訳ではなく、担当者ごとの処理件数にバラツキがでないようにするため、初算と検算の件数を算出しています。
メンバーに全業務を段階的に習得してもらうため、個人の習得状況にあわせて、2ヶ月に1度のペースで業務と担当者の組み替えを行います。
例えば、業務111の初算を担当するのは、Aさんだけでなく、AさんBさんCさんの3人の場合もあります。
そうなった時に、初算者欄にAさんBさんCさんと入力したら、件数23件を自動で3分けして、個人の集計表に自動反映させたいのです。
①の表は人数の増減が発生するので、汎用性の高いものにしなければならないと思っています。

やっぱり、そんな夢みたいな計算式はないですかね…

お礼日時：2018/08/25 23:52

No.4 回答者： hakobulu 回答日時： 2018/08/23 23:52

＃３です。

確認してたら、画像内 F5 の入力を忘れていました。
すみません。

No.3 回答者： hakobulu 回答日時： 2018/08/23 19:03

＃２ですが、念のため。

最初の計算式で、
初算件数　I3⇒ =IF($C3="A",$D3,0)
検算件数 J3⇒ =IF($E3="A",$F3,0)
と入れますが、列に対して絶対番地を使わないと正しく式がコピーされません。
「＄」をお忘れにならないで、という意味です。

No.2 回答者： hakobulu 回答日時： 2018/08/23 18:58

ご意図に沿っていないかもしれませんが、入力部をとりあえず画像のようにしてみました。

日常作業としては、

業務→件数→初算者→初算件数→検算者→検算件数

の順で入力する。

あらかじめ用意する A の集計計算式は以下のとおり。
初算件数　I3⇒ =IF($C3="A",$D3,0)
検算件数 J3⇒ =IF($E3="A",$F3,0)
初算＋検算 K3⇒ =I3+J3
入力後、I3:K3 を選択してお望みの行までドラッグしコピー。
大体１か月分ぐらいが入りそうな行数に計算式を入れておき、月が替わったら新しいシートを追加すれば良いかと思います。

仮に２００行まで計算式をコピーした場合。
H1:K200 を選択し、（４列×担当者の人数分）の列を右側にドラッグコピーします。
HI、L1 、P1・・・・のように４列ごとの一行目は「A」と表示されるので、これをA→B、A→C、A→D・・・のように変更する。
次に、たとえば B さんの集計欄の場合、M3:O200 を選択してから、編集→置き換え操作を行います。
検索する文字列⇒ "A"
置き換える文字列⇒"B"
です。
Cさんの集計欄に対しても、Q3:S200 を選択してから、
検索する文字列⇒ "A"
置き換える文字列⇒"C"
で置き換える。
「""」を付けるのを忘れないでください。
蛇足ですが集計欄の2行目は各列３～２００行までのSUMです。

うまく説明できたかわかりませんが、ご不明な点は補足していただけるとありがたいです。
もっと良い方法がありそうな気もしますが、とりあえず私の知識ではこの程度。
何かのご参考になれば幸いです。

No.1 回答者： シニア入り 回答日時： 2018/08/21 23:12

業務によって件数が固定しているように見えますが、可変でしょうか？

表を集計し易いように整理することをお勧めします。
A列に50名の名前を記入して、B列に業務111の初算者、C列に業務111の検算者、D列に業務311の初算者、E列に業務311の検算者というようにして該当セルに１を記入。B列トップ下セルに総件数、C列トップ下セルに１人あたりの件数を記入するようにすれば、各自の合計件数は簡単に計算できます。