Excelの表から今日の表を作る その2

以前質問した「Excelの表から今日の表を作る」ですが
セルの中の文字　1A　が5つまで重複している場合はどうでしょうか？

No.2 ベストアンサー 回答者： tom04 回答日時： 2018/06/21 08:33

続けてお邪魔します。

＞実はもっと複雑1Aとか2Aは20種類あります。

元データに重複はいくつあっても構いません。
前回の数式は最大5個まで表示する数式です。

＞複数のダブリはMOD(ROW(),5)の5で数を・・・
この部分はSmall関数の何番目を返すか？の「順位」の部分になります。
大切なのはどの行に数式を入れるかによって決まってきます。

お手元の「当番表」の配置がこちらでは判らないので勝手に前回のような配置にした場合の数式でした。
6行目に入れているので
＞MOD(ROW(),5)　は「1」が返り、下へフィル＆コピーするたびに「2」　→　「3」　・・・
となります。
仮に前回の配置の1Aの場合（C6の場合）の数式は
「マスター」シートの日にちが一致する列で「1A」と入力されている行のC列を小さい順に表示する！という配列数式です。
これを下のC11セルにも同じ数式を入れないといけないので
＞MOD(ROW(),5)　としてみました。
（11行目に上記数式を入れるとC6セルと同じように「1」が返ります）

※　ここで当方が文章でいくら説明してもお手元の「当番表」の配置が判らない限り
おそらく数式を投稿しても無意味な数式になると思います。

※　以下は余談　※
＞MOD(ROW(),5)
の部分は
＞ROW(A1)
としても構いません。
ただこの場合、C11セルの数式は2か所手を加えて、
＞ROW(A11)
となっている部分を
＞=ROW(A1)
にする必要があります。

尚、配列数式の説明は割愛します。m(_ _)m

この回答へのお礼

>尚、配列数式の説明は割愛します。
とかかれてアハハですが、どのようにして勉強しましたか？

お礼日時：2018/06/22 14:38

No.3 回答者： tom04 回答日時： 2018/06/22 16:09

続けてお邪魔します。

配列数式の件ですが・・・
＞どのようにして勉強しましたか？

コレ！といって勉強はしていません。
ただ、このサイトで回答をしているうちに、配列数式で処理する方が簡単な場合が何度もありました。
そのため、ネットで検索するといくらでも配列数式に関するものがありましたので
そちらで自分なりに理解したつもりです。
（たとえば、一致する条件だけを抽出する場合など・・・）

とりあえず参考になるかどうか判りませんが、
今回の場合の理屈を↓の画像で説明します。

A列が「A」の場合のB列だけを表示する方法にしてみました。
D・E列を利用し、結果としてF列の結果を導き出しています。

D4セルに
=A4="A"
という数式を入れフィルハンドルで下へコピー！
これでA列が「A」の場合の行だけた「TRUE」になります。（その他は「FALSE」）

E4セルに
=IF(D4=TRUE,ROW(),"")
とし、下へフィル＆コピー！
これでA列が「A」の場合のみの行番号だけが表示されます。

結果のF4セルに
=IF(COUNT(E$4:E$1000)<ROW(A1),"",INDEX(B$4:B$1000,SMALL(E$4:E$1000,ROW(A1))-3))
エラー処理も含めて、E列に数値（行番号）が入っている行のB列データを行番号の小さい順に表示する数式です。
ここで気を付けなければならないのは
INDEX関数の範囲を何行目からするか？というコトです。
今回は4行目以降を範囲指定し、その行番号を掛け合わせていますので、
仮にINDEX関数範囲の1番目がヒットしても4番目以降が表示されてしまいます。
そのため数式内の「-3」を入れないとINDEX関数の範囲の4番目から表示されます。
ちなみに、数式内の「ROW(A1)」はSMALL関数の順位に当たります。数式が入っている行（F4は「1」、F5は「2」・・・となります。

これらの操作を一気に行っているのがH列で、H4セルには
=IFERROR(INDEX(B$4:B$1000,SMALL(IF(A$4:A$1000="A",ROW(A$4:A$1000)-3),ROW(A1))),"")

配列数式なので、当然Ctrl＋Shift＋Enterとし、フィルハンドルで下へコピーしています。

今回の質問は上記方法の応用編となります。

この程度でよろしいでしょうかね？m(_ _)m

No.1 回答者： tom04 回答日時： 2018/06/19 15:06

こんにちは！

https://oshiete.goo.ne.jp/qa/10563263.html

↑のサイトの関連ですね。

＞1A　が5つまで重複している場合は
というコトなので、↓の画像のように「当番表」に手を加えました。
最大5行としています。

画像では「当番表」のC6セルに
=IFERROR(INDEX(勤務表!$C$10:$C$27,SMALL(IF(OFFSET(勤務表!$C$10:$C$27,,MATCH($F$3,勤務表!$D$6:$AI$6,0),,1)=B$6,ROW(C$10:C$27)-9),MOD(ROW(),5))),"")

配列数式なのでCtrl＋Shift＋Enterで確定！（←必須★）しC10セルまでコピーしています。
E6・G6セルへコピーは前回同様Ctrlキーを押しながらドラッグします。

次にC6セルをCtrlキーを押しながらC11セルにコピー＆ペースト
数式バー内で1か所手を加えます。
＞=B$6
を
＞=B$11
とします。結局C11セルの数式は
=IFERROR(INDEX(勤務表!$C$10:$C$27,SMALL(IF(OFFSET(勤務表!$C$10:$C$27,,MATCH($F$3,勤務表!$D$6:$AI$6,0),,1)=B$11,ROW(C$10:C$27)-9),MOD(ROW(),5))),"")

となりますので、同じくCtrl＋Shift＋Enterで確定　→　C15セルまでフィル＆コピー！
E11・G11セルへのコピーは同様にしてください。

※　「勤務表」シートは1名あたり2行使うようになっていますが、
上側の行のみ入力する！という前提です。m(_ _)m

この回答へのお礼

実はもっと複雑1Aとか2Aは20種類あります。

複数のダブリはMOD(ROW(),5)の5で数を調整できますか？
1つしかないのなら5を1にすればいいのでしょうか？

お礼日時：2018/06/20 23:42