エクセルです。とある条件下で固有名詞を別のシートに反映！（２）

質問

いつもお世話になっています。
さて、前回の質問を若干、手直しました。

Sheet1です。
　　　Ａ　　　　Ｂ　　　　Ｃ　　　　Ｄ
１　　　　　　１日　　　２日　　 ３日
２　鈴木　　早番　　遅番　　休み
３　木村　　休み　　休み　　遅番
４　田中　　遅番　　早番　　早番

以上のように打ち込んであったとして、
これを別の表で１日に早番で出勤する人、遅番で出勤する人をそのまま名前で抽出したいのです。（以下のような感じです）

　　　　Ａ　　　　Ｂ　　　　Ｃ　　　　Ｄ
　６　　　　　　１日　　　２日　　 ３日
　７　早番　　鈴木　　田中　　田中
　８
　９　遅番　　田中　　鈴木　　木村　　
１０
１１　休み　　木村　　木村　　鈴木

上記のような状態から更に急遽、１日に休みの木村が急遽、早番になり、結果として二人になったり、あるいは１日に早番の鈴木と遅番の田中が急に入れかわったりした時に　自動で修正してくれる表が欲しいのです。
どうぞ宜しくお願い申し上げます。

回答No.2

shiotan99 · ベストアンサー

こんにちは～

配列数式を使えば、1つの数式をコピーするだけでご希望がかなうと思いますよ。

質問のタイトルには「別のシートに反映」とあるので、Sheet1 以外に表を作るんですよね？
例を見ると、抽出表の左に 6 ～ 11の数字がありますが、これは行番号ですか？
6行目から表を始めるってことでしょうか？
とりあえず、この数字には特に意味はないと勝手に解釈させていただきました。

次のようにしてみてください。

【 前提 】
・ 元の表は Sheet1 の 1行目から4行目にある

ここでは例として抽出シート（Sheet1 とは別のシート）の1行目から表を作ります。
開始位置は別に何行目でもかまいません。数式を修正する必要はありますが・・。

B1 から右に、日付を入れてください。
A2・A3セルに 「早番」、A4・A5セルに 「遅番」、A6・A7セルに 「休み」と入力（「」は不要）。
同じ日に二人が休むことがなければ、A7は不要です。A6だけに 「休み」と入力。
質問の例では、抽出表の A列は「早番」→空白→「遅番」→空白→「休み」となっていますが、空白にはしないでください。

抽出シートの B2に、下の数式をコピペ。

=IF(COUNTIF(Sheet1!B$2:B$4,$A2)<MOD(ROW(),2)+1,"",INDEX(Sheet1!$A$2:$A$4,SMALL(IF(Sheet1!B$2:B$4=$A2,ROW($A$1:$A$3),""),MOD(ROW(),2)+1)))

貼り付けたあと、B2セルが選択された状態で F2キーを押し、
そのあと 〔Ctrl〕+ 〔Shift〕+ 〔Enter〕を同時に押して確定（配列数式）。

数式バーを見て、数式が { } で囲まれていれば OKです。
囲まれていなければ NGですので、
もう一度、B2を選択 → F2キー → 〔Ctrl〕+〔Shift〕+〔Enter〕

確定後、下に右に数式をフィルコピーすれば完成です。

抽出表を 1行目以外から始める場合、この数式をコピーするだけではうまくいきません。
参照するセル番号を変更する必要があります。
また、抽出表を偶数行から始める場合（日付が入る行が偶数行の場合）、
MOD(ROW() の部分（2ヶ所）を MOD(ROW(A2) のように、ROW のあとの（）内に A2 を入れてください。
1行目のように奇数行の場合はそのままでOKです。

わからなければ補足してください。
その際、抽出表が何行目のどの位置から始まるのかを具体的に書いてください。

お礼

ご回答、有難うございます。
エクセルは奥が深いですよね。いつも思うのは、パソコンはどこまでのデータ処理を行えるのか、です。特に私が疑問とする内容等は簡単にできそうなものですが、これが中々、上手くいきません。
あるいは、shiotan99さんのようにパソコンに精通していそうな方に比べると私は単に勉強不足、ということなのでしょうかね。
何はともあれ、教えて頂いた内容で試してみます。
有難うございました。

回答No.1

kaisendon

改めましてこんばんは

かなり難易度がアップしてますね＾＾；

早番　遅番　休み　それぞれ二人までという条件の下で
半ば強引に作ってみました。
（いずれかが三人になった場合には対応しません）

１日がＢ列なら３１日はＡＦ列になると思います。
ＡＨ列を作業列として使いました。

ＡＨ４に　１　　ＡＨ３に　２　　ＡＨ２に　１　と入力して下さい。
（人数が増えたら一番下の人を　１　下から２番目の人を　２
　として上方向にフィルコピー）

さらに作業用の表も作りました。（Ａ１４：ＡＦ１６）
作業用の表は元の表の名前の順番を反転させる為のものです
Ａ１４に　=INDEX($A$2:$A$4,AH2)
Ａ１６までコピー
Ｂ１４に　=INDEX(B$2:B$4,MATCH($A14,$A$2:$A$4,0))
ＡＦ１４までコピー
そのまま１６行目までコピー

Ｂ７に　=INDEX($A$2:$A$4,MATCH($A7,B$2:B$4,0))
Ｂ８に　=IF(COUNTIF(B$14:B$16,$A7)=1,"",INDEX($A$14:$A$16,MATCH($A7,B$14:B$16,0)))
Ｂ７とＢ８を選択して下方向にＢ１２までフィルコピー
Ｂ７：Ｂ１２が選択された状態のまま
書式　-　条件付書式　-　数式が　-　=ISERROR(B7)　-　書式ボタン
-　フォントタブ　で色を白に設定　ＯＫボタン
Ｂ７：Ｂ１２が選択された状態のまま
ＡＦ列までコピー

質問の表の７行目９行目１１行目（つまり一人目）は元の表を
８行目１０行目１２行目（二人目）は順序を反転させた作業用の表を参照しています。

必要に応じて作業列、作業用の表は非表示にして下さい。

追記：
作業列（ＡＨ列）は　直接数字を入力しましたが、
ＡＨ２に　=COUNTA(A2:A4)
ＡＨ３に　=AH2-1
として、ＡＨ３の式を下方向にフィルコピーしてもいいです。

配列数式を使ったもっとスマートな方法もあるかも知れませんが
私にはこれが精一杯です o(_ _)o

↓前回の質問

参考URL：

http://okweb.jp/kotaeru.php3?q=1504685

お礼

度重なるご回答、有難うございます。
エクセルの場合、意外と出来そうな事が出来ない、こういった事が多々あり、苦労しています。
何はともあれ、教えて頂いた内容で試してみたいと思います。