EXCEL数式について質問です

質問

はじめまして、EXCELについて教えて頂きたいです。

※添付画像を参照お願い致します。

A1：D6に　Aさん、Bさん、Cさんが担当するものを○として決定します。

その決定した値をF1：F17（F1、F7、F12は名前）にA2:A6の値を
数式を使って代入、またはボタンを押して反映としたいです。

各反映のセルは5行（F2～F6)と固定いたします。

以上の件ご回答よろしくお願い致します。

回答No.1

kagakusuki · ベストアンサー

今仮に、H列を作業列として使用するものとします。

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

=IF(OR(COUNTIF($B$1:$D$1,$F1),AND($F1="",INDEX($F:$F,ROW()-1)<>"")),ROW(),"")

　次に、H１セルをコピーして、H2:H17の範囲に貼り付けて下さい。
　次に、B2セルに次の数式を入力して下さい。

=IF(COUNTIF($F:$F,B$1),IF(COUNTIF(INDEX($F:$F,MATCH(B$1,$F:$F,0)):INDEX($F:$F,SMALL($H:$H,RANK(MATCH(B$1,$F:$F,0),$H:$H,1)+1)),$A2),"○",""),"")

　次に、B2セルをコピーして、B2:D6の範囲に貼り付けて下さい。

　以上です。

補足

回答ありがとうございます。

一点違う点がありますので教えて頂きたいです。

B2:D6の○がついたA2:A6の値をF列に反映させたいです。

以上よろしくお願い致します。

回答No.2

kagakusuki

>B2:D6の○がついたA2:A6の値をF列に反映させたいです。

　申し訳御座いません、勘違いをしておりました。
　只、担当者の氏名と果物の名称を同列に並べますと、見辛い上に処理も面倒になります。

　ですから、御質問の内容とは少し違いますが、担当者の氏名と果物の名称を別の列に分けて表示するのではだめでしょうか？

　今仮に、B1～D1に担当者名が並んでいて、A2～A6に果物の名称が並んでいて、J列とK列を作業列として使用して、G列に担当者の氏名を、H列に果物の名称を表示させるものとします。（添付画像を参照のこと:もし、ご利用のサイトでは画像が表示されていない場合には、次のURLのページを御覧下さい。　http://okwave.jp/qa/q7690732.html）

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

=IF(INDEX($B:$E,INT((ROW()-ROW(J$2))/COLUMNS($B:$E))+ROW($A$1)+1,MOD(ROW()-ROW(J$2),COLUMNS($B:$E))+1)="○",INDEX($B$1:$E$1,MOD(ROW()-ROW(J$2),COLUMNS($B:$E))+1)&"■"&COUNTIF(J$1:INDEX(J:J,ROW()-1),INDEX($B$1:$E$1,MOD(ROW()-ROW(J$2),COLUMNS($B:$E))+1)&"■*")+1,"")

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

=IF(INDEX($B:$E,INT((ROW()-ROW(K$2))/COLUMNS($B:$E))+ROW($A$1)+1,MOD(ROW()-ROW(K$2),COLUMNS($B:$E))+1)="○",INDEX($A:$A,INT((ROW()-ROW(K$2))/COLUMNS($B:$E))+ROW($A$1)+1),"")

　次に、J2～K2の範囲をコピーして、同じ列の3行目以下に、元の表の升目の数以上の行数となるまで、下にある行に貼り付けて下さい。（例えば担当者の人数が4名で、果物の種類が5種類の場合には、4×5＝20で、20行に亘って貼り付ける）
　次に、G2セルに次の数式を入力して下さい。

=IF(ROWS($2:2)=1,INDEX($1:$1,COLUMN($A$1)+1)&"",IF(OR(COUNTIF($G1:$G$2,"*?")>=COUNTIF($B$1:$E$1,"*?"),ROWS($1:2)-MATCH(INDEX($B$1:$E$1,COUNTIF($G1:$G$2,"*?")),$G$1:$G1,0)<COUNTIF($J:$J,INDEX($B$1:$E$1,COUNTIF($G1:$G$2,"*?"))&"■*")),"",INDEX($B$1:$E$1,COUNTIF($G1:$G$2,"*?")+1)))

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

=IF(ROWS($2:2)-MATCH(INDEX($B$1:$E$1,COUNTIF($G$2:$G2,"*?")),$G$2:$G2,0)<COUNTIF($J:$J,INDEX($B$1:$E$1,COUNTIF($G$2:$G2,"*?"))&"■*"),VLOOKUP(INDEX($B$1:$E$1,COUNTIF($G$2:$G2,"*?"))&"■"&ROWS($2:2)-MATCH(INDEX($B$1:$E$1,COUNTIF($G$2:$G2,"*?")),$G$2:$G2,0)+1,$J:$K,2,FALSE),IF($G2="","","(無し)"))

　次に、G2セルとH2セルの両方のセルに対して、セル毎に囲う罫線を設定して下さい。
　次に、以下の操作を行って、条件付き書式を設定して下さい。

【Excel２００７以降のバージョンの場合】
G2セルを選択
　　↓
[ホーム]タブ内の「スタイル」グループの中にある[条件付き書式]ボタンをクリック
　　↓
現れた選択肢の中にある[新しいルール]をクリック
　　↓
現れた「新しい書式ルール」ダイアログボックスの「ルールの種類を選択してください」欄の中にある[数式を使用して、書式設定するセルを決定]を選択してクリック
　　↓
現れた「次の数式を満たす場合に値を書式設定」欄に

=AND(G2="",$H2<>"")

と入力
　　↓
「新しい書式ルール」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[罫線]タブをクリック
　　↓
現れた「スタイル」欄の中にある[なし]をクリック
　　↓
「罫線」欄の中の四角形の上辺をクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「新しい書式ルール」ダイアログボックスの[OK]ボタンをクリック
　　↓
G2～H2の範囲を纏めて選択
　　↓
[ホーム]タブ内の「スタイル」グループの中にある[条件付き書式]ボタンをクリック
　　↓
現れた選択肢の中にある[新しいルール]をクリック
　　↓
現れた「新しい書式ルール」ダイアログボックスの「ルールの種類を選択してください」欄の中にある[数式を使用して、書式設定するセルを決定]を選択してクリック
　　↓
現れた「次の数式を満たす場合に値を書式設定」欄に

=$H2=""

と入力
　　↓
「新しい書式ルール」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[罫線]タブをクリック
　　↓
現れた「スタイル」欄の中にある[なし]をクリック
　　↓
「罫線」欄の中の四角形の左辺と右辺と下辺をクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「新しい書式ルール」ダイアログボックスの[OK]ボタンをクリック

【Excel２００７よりも前のバージョンの場合】
G2セルを選択
　　↓
メニューの[書式]ボタンをクリック
　　↓
現れた選択肢の中にある[条件付き書式]をクリック
　　↓
現れた「条件付き書式の設定」ダイアログボックスの左端の欄をクリック
　　↓
現れた選択肢の中にある「数式が」をクリック
　　↓
「条件付き書式の設定」ダイアログボックスの左から2番目の欄に次の数式を入力

=AND(G2="",$H2<>"")

　　↓
「条件付き書式の設定」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[罫線]タブをクリック
　　↓
現れた「スタイル」欄の中にある[なし]をクリック
　　↓
「罫線」欄の中の四角形の上辺をクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式の設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
G2～H2の範囲を纏めて選択
　　↓
メニューの[書式]ボタンをクリック
　　↓
現れた選択肢の中にある[条件付き書式]をクリック
　　↓
現れた「条件付き書式の設定」ダイアログボックスの左端の欄をクリック
　　↓
現れた選択肢の中にある「数式が」をクリック
　　↓
「条件付き書式の設定」ダイアログボックスの左から2番目の欄に次の数式を入力

=$H2=""

　　↓
「条件付き書式の設定」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[罫線]タブをクリック
　　↓
現れた「スタイル」欄の中にある[なし]をクリック
　　↓
「罫線」欄の中の四角形の左辺と右辺と下辺をクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式の設定」ダイアログボックスの[OK]ボタンをクリック

　尚、条件付き書式の設定方法に関しましては、次の参考URLのページを御参考にされると宜しいかと思います。

【参考URL】
　よねさんのWordとExcelの小部屋 > Excel総目次 > Excel2007(エクセル2007)基本講座の総目次 > 条件付き書式(数式を利用する)
　　http://www.eurus.dti.ne.jp/~yoneyama/Excel2007/excel2007-jyosyo2.html

　よねさんのWordとExcelの小部屋 > Excel総目次 > 条件付き書式
　　http://www.eurus.dti.ne.jp/~yoneyama/Excel/jyo-syo.html

　次に、G2～H2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。

　以上です。