Excelの式が上手く行きません。

質問

色々調べてみたんですが、上手く行かないので質問させてください。

　　　　　　　　1日目　　　　　　　　　　　　2日目
セルの列「C15」「C16」「C17」　　「C18」「C19」「C20」　
田中さん　「 」「A」「A」　　　　　　　　　「 」「A」「A」
鈴木さん　「 」「A」「A」　　　　　　　　　「 」「 」「 」

こんな感じでセルに入力したものを、次のシートで自動的にリストが作成されるように式を作っています。
1つのセル内に、
(1)田中さんのみが「 」「A」「A」のとき、田中さんの名前が出る。
(2)鈴木さんのみが「 」「A」「A」のとき、鈴木さんの名前が出る。
(3)田中さん・鈴木さんともに「 」「A」「A」のときは、田中さんと鈴木さんの名前が並んで表示される。
という3つの条件をすべて満たせる式を作りたいのですが、どうしても上手く行きません。

方法I：2つの式の間に「&」を入力した場合。
=IF(AND(Sheet1!R4C15="",Sheet1!R4C16="",Sheet1!R4C17="A"),Sheet1!R4C4&IF(AND(Sheet1!R5C15="",Sheet1!R5C16="",Sheet1!R5C17="A"),Sheet1!R5C4,))

これだと、
(1)田中さんのみが「 」「A」「A」のとき、田中さんの名前が出る⇒○
(2)鈴木さんのみが「 」「A」「A」のとき、なにも表示されない⇒×
(3)田中さん・鈴木さんともに「 」「A」「A」のときに、田中さんと鈴木さんの名前が並んで表示される⇒○
となって、(2)が上手くいきません。

方法II：2つの式の間に「&」、1つ目の式の最後に「,""」(該当しない場合は空白を表示)を入力した場合。
=IF(AND(Sheet1!R4C15="",Sheet1!R4C16="A",Sheet1!R4C17="A"),Sheet1!R4C4,""&IF(AND(Sheet1!R5C15="",Sheet1!R5C16="A",Sheet1!R5C17="A"),Sheet1!R5C4,))

これだと、
(1)田中さんのみが「 」「A」「A」のとき、田中さんの名前が出る⇒○
(2)鈴木さんのみが「 」「A」「A」のとき、鈴木さんの名前が出る⇒○
(3)田中さん・鈴木さんさんともに「 」「A」「A」のときに、Bさんの名前が出ない。⇒×
となって、(3)が上手くいきません。

(1)・(2)・(3)を全てクリアする方法はありませんでしょうか・・・。
知識がないもので、もしかしたら式自体がもっといい方法があるのかもしれませんが・・・。

最終的には人数を増やして、
他のセルでは「A」「 」「 」や別のローマ字「 」「 」「B」の条件に当てはまる名前を表示させて、
自動でリスト化できるようにしたいと考えていますが、これは式の問題が解決すれば応用でいけると思います。

本当は別のセルに「１」と入れれば1日目（C15・16・17）を参照して、「2」と入れれば2日目（C18・19・20）を参照するように作りたかったのですが、これは方法がわからないので諦めています。

困っています。どなたかわかる方いらっしゃいましたら教えてください・・・。

回答No.5

noname#52504 · ベストアンサー

横から失礼します。

私も一応作っては見たのですが、果たして実用に耐えるかどうか…。
質問者さまの意図・条件を読み違えていましたらすみません。
Excel2003で動作確認済ですが、R1C1形式の表示に不慣れなのでもしかすると誤記があるかも(汗
とりあえずご参考まで。

ステップ１．挿入＞名前＞定義　でセル範囲に名前をつける
　・Sheet1の名前が入力されている範囲を"名簿"とする
　　※50人分枠があるとすれば、　Sheet1!R4C4:R53C4　ぐらいでしょうか。
　・Sheet2の日付のセルを"日付"とする
　　※Sheet1では「何日目」とあり、Sheet2では「何月何日」とあります。
　　　もし、例えばSheet2の"9月13日"がSheet1の"13日目"を意味しないのであれば、
　　　日付から何日目に対応するのかを数値で返して、そのセルを"日付"としてください。

ステップ２．Sheet2に数式を入れる
　#3さまへの「お礼」にある形式を念頭においています。
　同じ日同じシフトの人が複数いる場合は縦に並んで表示されますので、
　重複する可能性がある人数に応じて、
　「早」「中」「遅」「通」それぞれの枠について行数を多めに用意して下さい。

　・「早」の場合
　　R3C2セル：
　　=INDEX(名簿,MATCH(LARGE(((OFFSET(名簿,,日付*3+8)&OFFSET(名簿,,日付*3+9)&OFFSET(名簿,,日付*3+10))=R2C&R2C&" ")/ROW(名簿),ROW()-ROW(R3)+1),1/ROW(名簿),0))
　　を配列数式として入力して、右方・下方にフィル。
　　※通常の数式は、数式を入力した後Enterキーで確定しますが、
　　　これは配列数式なので、CtrlとShiftを押しながらEnterで確定してください。

　・「中」の場合
　　例えば「早」を2行用意し、「中」が5行目から始まる場合はR4C2セル：
　　=INDEX(名簿,MATCH(LARGE(((OFFSET(名簿,,日付*3+8)&OFFSET(名簿,,日付*3+9)&OFFSET(名簿,,日付*3+10))=" "&R2C&R2C)/ROW(名簿),ROW()-ROW(R5)+1),1/ROW(名簿),0))
　　を同様に配列数式として入力して、右方・下方にフィル

　　「早」と「中」で異なるのは、
　　・その枠が何行目から始まるかを指定する部分　R3　／　R5
　　・シフトに応じて検索する文字を指定する部分　R2C&R2C&" "　／　" "&R2C&R2C
　　です。
　　以下、「遅」「通」の場合も同様にそれぞれ変更してください。

ステップ３．エラー処理をする。
　数式にエラー処理を組み込むとさらに長大になってしまうので、
　書式＞条件付書式＞数式が：=ISNA(RC)　書式：フォント色白
　として#N/Aを表示しないようにしてください。

　Excel2007ではIFERROR関数という便利なものができたそうなので、
　数式にそれをかぶせても良いかもしれません。
　ただし未検証ですし、Excel2003以下の環境にもっていくと動作しません。

なお、例えば、【「 」「A」「A」】のときに
半角スペースを入れ忘れて【「」「A」「A」】となっているとヒットしません。
私自身、上記の挙動を確認していて良く間違えたので、
半角スペースではなくハイフンを使うなどして【「-」「A」「A」】のようにされた方が良いかもしれません。

ん～、やはり#3さまのご回答を待った方が良さそうですね(^^;
私も#3さまのご回答を参考にしたいと思いますので、
締め切らずにお待ちいただけるとありがたいです。

長乱文陳謝。

回答No.10

noname#52504

#5です。
>「Sheet1!R4C15」が「A」の人をリストアップ
ん～っと、R4C15というのは単一のセルに対する参照ですが、第15列についてのみ処理する、ということでよろしいでしょうか？

今回の表位置と、#6で提示した考え方に沿って書くならば、やはりLARGEを使って、
　=INDEX(Sheet1!R4C4:R54C4,1/LARGE((Sheet1!R4C15:R54C15="A")/ROW(Sheet1!R4C4:R54C4),ROW()-ROW(R3)+1)-3)
ということになります。

もちろん、これに限らず他にもさまざまな書き方ができます。
例えば、
　=INDEX(Sheet1!R4C4:R54C4,SMALL(IF(Sheet1!R4C15:R54C15="A",ROW(Sheet1!R4C4:R54C4),""),ROW()-ROW(R3)+1)-3)
など。

以下、冗長になってしまいましたが、少し詳しく解説すると…

●配列数式について

「配列」というのは、大雑把に言うと「四角いデータの並び（矩形データ）」のことですが、配列数式に用いる場合は、「一定の要素数を持つ一行または一列のデータ(ベクトル)」であることが多いので、「ある一定の長さのリスト」というふうに考えていただいても差し支えないと思います。

配列数式というのは「配列のそれぞれの要素について個別に処理せよ」という数式です。「数式の中で作業列を使った処理をやらせる」と考えると分かりやすいかも知れません。

例えば、
　=INDEX(ROW(R1C1:R10C1)+3,4)
という数式は、
まず、R1C1:R10C1のそれぞれのセルについて、行番号を取った配列 {1,2,3,4,5,6,7,8,9,10} を作り、
次に、その配列のそれぞれの要素について、３を加えた配列 {4,5,6,7,8,9,10,11,12,13} を作り、
更に、その配列のうち４番目を返せ、ということですから、7が返ります。

http://pc.nikkeibp.co.jp/pc21/special/hr/index.shtml

●比較演算、論理演算、算術演算について

＝とか、＞といった比較演算子を用いた演算は、論理値(TRUEまたはFALSE)を返します。
例えば、(1+5=3*2)　という比較演算子を用いた数式は論理値TRUEを返します。
一方、論理値(TRUEまたはFALSE)に対して、＋とか＊といった算術演算子を用いると、TRUEは1，FALSEは0として扱われます。
例えば、((1+5=3*2)*3)、はTRUE*3であり、すなわち、1*3なので、3を返します。
従って、、比較演算子と算術演算子を組み合わせれば、AND関数やIF関数を使った論理演算を代用させることができます。
例えば、
　=IF(AND(R1C1=3,R2C1=4),5,0)　と書いても
　=(R1C1=3)*(R2C1=4)*5　と書いても
ほぼ同じ意味です。
#7の回答では、これを使って、３列それぞれの判定をANDでくくる代わりに、*で掛け合わせることで「かつ」を表現しています。

http://www.kentei.ne.jp/quali/column/knowhow/030815/030815.html

●=INDEX(Sheet1!R4C4:R54C4,1/LARGE((Sheet1!R4C15:R54C15="A")/ROW(Sheet1!R4C4:R54C4),ROW()-ROW(R3)+1)-3)　
　について

目的は、
「検索列の条件を満たすセルのうち、Ｎ番目のセルに対応するセルを名簿列から探して返す」
ということですよね。
まず、R4C15:R54C15="A"　の部分で、
「条件を満たすセルについてはTRUE，そうでないセルはFALSEとなる配列１」
を作ります。
配列１の各要素をそれぞれ、名簿列Sheet1!R4C4:R54C4の行番号で割ることで、
「条件を満たすセルについては0、そうでないセルは行番号の逆数となる配列２」
を作ります。

ココで、割るのではなく掛けてしまってもよさそうな気がしますが、そうすると、後で「Ｎ番目のセル」を探すときに、SMALL関数は0をカウントしてしまうのでうまく行きません。
例えば、{0,2,0,0,5,0,7}のうち5を探すのは「２番目に小さい正の数」ということになって困難ですが、{0,1/2,0,0,1/5,0,1/7}としておけば、「２番目に大きい数」ですからLARGEで探せます。

上記別案のようにIF関数を使って
「条件を満たすセルについては""、そうでないものは行番号の逆数となる配列」
を作るならば、SMALLで判定することもできます。

ROW()-ROW(R3)+1　は自セルの行番号とスタート行番号を比較して、「条件を満たすセルのうち何番目のセルを返すのか」を計算しています。

以下、
・LARGE(配列２，Ｎ)　　で、「Ｎ番目のセルの行番号の逆数」をとります。
・1/LARGE(配列２，Ｎ)　　で、もう一度逆数をとって、「Ｎ番目のセルの行番号」をとります。
・1/LARGE(配列２，Ｎ)-3　で、名簿列の位置に応じて値を調整して、
　「Ｎ番目のセルに対応する配列Ｂの行位置」をとります。
・INDEX(名簿,1/LARGE(Ａ''，Ｎ)-3)　 で、目的の値を返します。

以上、雑駁ですがご参考まで。

説明の至らない部分も多いと思いますが、これ以上の詳細な説明は私の手には余るように思われますので、これで最終回答とさせていただきたいと思います。

長乱文陳謝。

お礼

どうもありがとうございました！
色々調べて、やったところ、上手く行きました！！

結局全て頂いた回答の中に答えがあったのですが、
自分が理解できていなかっただけでした・・・。

詳しく説明していただいてありがとうございました。
おかげさまで理解できたので、これから仕様変更があっても対応できます。

本当にありがとうございました。

回答No.9

Wendy02

補足：

>>単純に4セル利用すれば可能ではあるのですが、

>読んでいて気が付いたのは、複数の名前があるときに、種類がひとつなら、一人に一人づつ名前を出すことは可能ですが、4パターンがあって、それに対して、縦でも横でも、式を入れるセルがないと、次のパターンの始まる部分が論理的には可能でも、関数自体の限界が出てきてしまいます。

後で考えてみたのですが、これに関しては、多少ワークシートは重くなっても、本来、解を返す場所が制限されていないなら、VBAのユーザー定義関数を使わなくても可能ではないか、と考えました。本来、個々の出力に対しては、1列の2種類のパターンの検索なのですから、ワークシートの範囲です。だから、VBAなど使う必要がないはずなのです。

しかし、ワークシート関数には、VBAにはある、JOIN関数といって、配列をまとめ、文字列として一覧に出す関数がありません。解を返す出力の場所が制限されていると、ワークシート関数では、通常では、その配列の1番目しか出てきません。

お礼

どうもありがとうございます。
すごいですね・・・・全く理解不能ですが、書いてある通りにやったら出来ました。
まだまだ勉強が必要なようです。

#3様のものと#5様のもの、どちらでやろうか迷っています・・・。

回答No.8

Wendy02

こんにちは。

遅くなって、ごめんなさい。
こういう問題というのは、気分のノリで作ってしまうのですが、その気持ちが充実するのに、数日かかってしまいました。

この解答の障害になっているのは、最初に、まったく別の問題です。それは、R1C1 方式と A1方式とが混在していて、位置関係が見えなくなっていることなのです。だいたいの所は想像がつきました。

次に、

>単純に4セル利用すれば可能ではあるのですが、

読んでいて気が付いたのは、複数の名前があるときに、種類がひとつなら、一人に一人づつ名前を出すことは可能ですが、4パターンがあって、それに対して、縦でも横でも、式を入れるセルがないと、次のパターンの始まる部分が論理的には可能でも、関数自体の限界が出てきてしまいます。

そこで、ユーザー定義関数を作りました。

こちらで、キメウチさせていただく条件として、まず、パターンです。
違う場合は、元を変えてください。
--------------------------------------
例：

「W」「W」「　」- 「早」
「　」「W」「W」- 「中」
「W」「　」「W」－「遅」
「W」「W」「W」-「通」
--------------------------------------

　　　Ａ　　Ｂ　　Ｃ　　Ｄ　　Ｅ　
１　　月　 　日
２　　　 　 W　　 X 　　Y 　　Z
３　　早　 (数式)
４
５　　中
６
７　　遅
８

なお、日付検索は、複雑になりすぎるので、列数を決定することを考えてください。

標準モジュールへの取り付け方：

Alt　+　 F11 (Altを押しながらF11）を押すと、Visual Basic Editor 画面が出てきます。
次に、メニューの[挿入]－[標準モジュール]と開けて、クリックすると、画面が現れますので、以下のコードを貼り付けて、

Alt　+　Q で、画面を閉じます。

----------------------------------------------------------------

Function DateLISTUP(mNameData As Range, mStartCell As Range, _
　　　　　　　　　 mCol As Integer, KanjiChr As String, AlphaChr As String)
'DateLISTUP (名前のセルの範囲,日付データの始まりのセル, _
　　　　　　 日付データの始まりの列,漢字パターン,アルファベットパターン)
　Dim PtnAtt(3) As String
　Dim PtnChr As Variant
　Dim myPat As String
　Dim c As Variant
　Dim rng As Range
　Dim i As Long
　Dim d As Integer 'セル間の差
　Dim mRow As Long
　Dim Datas() As Variant
　Dim x As String, y As String, z As String
　Dim ret As Variant

　'誤動作を防ぐ
　KanjiChr = Trim(Left$(KanjiChr, 1))
　AlphaChr = Trim(Left$(AlphaChr, 1))

　'組み合わせパターン
　'-------------------------------------
　Const SOU As String = "110" '早
　Const CHU As String = "011"　'中
　Const CHI As String = "101"　'遅
　Const TSU As String = "111"　 '通

　PtnAtt(0) = SOU: PtnAtt(1) = CHU: PtnAtt(2) = CHI: PtnAtt(3) = TSU
　PtnChr = Array("早", "中", "遅", "通")
　'-------------------------------------
　 ret = Application.Match(KanjiChr, PtnChr, 0)
　 If Not IsError(ret) Then
　　 myPat = PtnAtt(ret - 1)
　 End If

　d = mCol - mStartCell.Column + 1　'データの始まりと日付列の差
　Set rng = mNameData.Offset(, d)
　mRow = mNameData.Rows.Count

　For Each c In rng
　　x = -CInt(StrComp(Trim(c.Value), AlphaChr, 1) = 0)
　　y = -CInt(StrComp(Trim(c.Offset(, 1).Value), AlphaChr, 1) = 0)
　　z = -CInt(StrComp(Trim(c.Offset(, 2).Value), AlphaChr, 1) = 0)
　　If myPat = x & y & z Then
　　　ReDim Preserve Datas(j)
　　　Datas(i) = c.Offset(, -d)
　　　i = i + 1
　　End If
　Next
　On Error Resume Next
　 DateLISTUP = Join(Datas(), ",")
　On Error GoTo 0
　Set rng = Nothing
End Function

----------------------------------------------------------------
※あくまでも、A1方式の設定にさせていただきます。

数式の使い方：

=IF($A$3="","",DateLISTUP(Sheet1!$N$4:$N$20,Sheet1!$O$4,$G$1,$A3,B$2))

名前の範囲：
Sheet1!$N$4:$N$20

データの始まりセル
Sheet1!$O$4

G1:
日付セルの決め方
=INT(15+(B1-1)*3)

15列目(O列）が最初だした場合

$A3　は、W, X, Y, Z とアルファベットの位置

B$2 は、早、中,　遅,　通　の位置

---------------------------------------------------------------------
なお、この問題点は、ユーザー関数にしても、重すぎるので、もうマクロの領域です(VBAでは作られていても、これはマクロではありません）。空いている部分は、数式をおかないほうがよいかもしれません。

回答No.7

noname#52504

#5です。

＞実際は何も入力しない空白です。「」が正しいです。
あーナルホド(^^;
私もわざわざ半角スペースを入れるのは妙な仕様だなぁーとは思っていたのですが。

半角スペースがある場合は、連結したときに「 WW」と「WW 」の区別がつきますが、
何も入れない場合はどちらも「WW」になりますからうまくいきませんね。

その場合は、各列毎に個別に判定する必要がありますから、こんな感じになります。

「早」
=INDEX(名簿,1/LARGE(((OFFSET(名簿,,日付*3+8)=R2C)*(OFFSET(名簿,,日付*3+9)=R2C)*(OFFSET(名簿,,日付*3+10)=""))/ROW(名簿),ROW()-ROW(R3)+1)-3)

「中」
=INDEX(名簿,1/LARGE(((OFFSET(名簿,,日付*3+8)="")*(OFFSET(名簿,,日付*3+9)=R2C)*(OFFSET(名簿,,日付*3+10)=R2C))/ROW(名簿),ROW()-ROW(R5)+1)-3)

お礼

どうもありがとうございます。

関数がいっぱいですが、こちらの方はまだギリギリ理解できそうです；；

急な変更などにも対応できそうな感じです。

もう一つ質問をしてもよろしいでしょうか・・・？
単純に、「Sheet1!R4C15」が「A」の人をリストアップするといった、参照列を1つにする場合はどういう式になるのでしょうか・・・？
おそらくLARGEを使わずに別の関数を使うと思って探しているのですが、探しても見つかりません・・・。

本当に勉強不足ですね・・・・申し訳ないです。
がんばります。

回答No.6

noname#52504

#5です。
#5の数式では、元表の位置が決定していることを前提に書いていますから、
MATCHで探さずに、逆数を取り直して定数で調整するべきでした。

「早」の場合
=INDEX(名簿,1/LARGE(((OFFSET(名簿,,日付*3+8)&OFFSET(名簿,,日付*3+9)&OFFSET(名簿,,日付*3+10))=R2C&R2C&" ")/ROW(名簿),ROW()-ROW(R3)+1)-3)
「中」の場合
=INDEX(名簿,1/LARGE(((OFFSET(名簿,,日付*3+8)&OFFSET(名簿,,日付*3+9)&OFFSET(名簿,,日付*3+10))=" "&R2C&R2C)/ROW(名簿),ROW()-ROW(R5)+1)-3)

なお、今度はエラー値は#DIV/0!が返りますから、
エラー処理の部分も、数式が：ISERROR(RC)　に変更して下さい。

もし元表の位置を変更する場合は、
・OFFSETで8足したり9足したり10足している部分（名簿列との相対列位置)
・最後に3引いている部分(行位置)
を変更して調整してください。

お礼

ありがとうございます！
こういうやり方もあるのですか・・・

さっそくやってみましたが、
私のやり方が悪いのか、
「R2C&R2C&" "」と「" "&R2C&R2C」の違いが判別されず、
「早」と「中」が同じリストになってしまいます。
対策として、
=INDEX(名簿,1/LARGE(((OFFSET(名簿,,日付*3+8)&OFFSET(名簿,,日付*3+9))=R2C&R2C)/ROW(名簿),ROW()-ROW(R3)+1)-3)
OFFSETを1つ削除してみたところ、
当然といえば当然なのですが、この方法だと「通」の人が「早」と「中」のリストに重複して表示されてしまいました。

ここが解決できれば完璧なのですが・・・

あと、これは私のミスで申し訳ないのですが、
わかりやすくするつもりで「 」と表記しましたが、
実際は何も入力しない空白です。「」が正しいです。
申し訳ありません。

#3さまの回答も是非見たいので、まだ締め切りません。
皆さまどうもありがとうございます。
自分でも勉強してみます。

回答No.4

Wendy02

#3です。
もう少し、2～3日考えさせてくださいね。
実務で使うとなると、ある程度の実用性が必要だと思いますね。それに、実際は、日にちが右に増えていくわけですよね。１組（１日の対応）が、出来たところで、何もなりませんよね。

数式とマクロと両方から考えてみます。（たぶん、数式が有力..マクロが悪いわけではないけれども、数式が失敗した時のみです。）
今までも、似たようなケースにあたっていますが、今回も難問でした。

回答No.3

Wendy02

こんにちは。

現在のままのご質問なら、その解決は問題ないと思いますが、おそらく、出来上がりは、勤務表などの一覧として出すということだと思います。

>最終的には人数を増やして、
今の質問内容では、回答としては、まかないきれないような気がします。あまり数が多いと、関数では無理のような気がします。

ということは、おそらく、
>2つの式の間に「&」
では無理になってくると思います。

ワークシート関数には、セル間の文字をつなぐ方法は、事実上「&（注）」しかありませんから、同じ式のつなぎ合わせしかありません。物理的に不可能とはいいませんが、そういう回答をすると、時々、質問者さんが、マナーはともかく「めんどくさい」と言って、ダメだしされたことがあります。

セルには一つの解（＝名前）を出すようにしたほうがよいのではありませんか？それを縦に並べるか、横に並べるか、というような出力の仕方をさせます。

ただし、配列数式かマクロを使わざるを得ないです。

(注：CONCATENATEは、この場合では、使い方が違います）

なお、R1C1 方式の絶対参照で書かれていると位置関係が分からないので、数式を入れる場所（出力位置）を特定していただかないと、応用が利きません。1列か2列の何行目かというようにしていただきたいです。そうしないと、相対参照式によるフィルのドラッグ・コピーが出来ません。単に、二名の名を出すなら、以下のようにすれば問題はないのですが。

=TRIM(IF(SUMPRODUCT((R4C15:R4C17={"","A","A"})*1)=3,R4C4,"")&" "&IF(SUMPRODUCT((R5C15:R5C17={"","A","A"})*1)=3,R5C4,""))

お礼

回答ありがとうございます。
お察しの通り、勤務表を作っています。

実際、ANo.1で教えていただいた方法でやってみたところ、13名分の式を入れたところで「数式が長すぎます」となってしまいました。
全員で50人ほどの部署ですので、単純に4セル利用すれば可能ではあるのですが、

>セルには一つの解（＝名前）を出すようにしたほうがよいのではありませんか？それを縦に並べるか、横に並べるか、というような出力の仕方をさせます。

これが出来れば最高です。

ちなみに、Sheet2で以下のような表を作ろうと思っています。
　　　Ａ　　Ｂ　　Ｃ　　Ｄ　　Ｅ　（←横の列）
１　　月　 　日
２　　　 　 W　　 X 　　Y 　　Z
３　　早
４
５　　中
６
７　　遅
８
９　　通
（↑縦の列）

Sheet1の「C15」「C16」「C17」に入力されているのが、
「W」「W」「 」なら「W」の「早」のところへ。
「 」「W」「W」なら「W」の「中」のところへ。
「Z」「Z」「 」なら「Z」の「早」のところへ。
と自動で名前が並んで表示されるようにしたいのです。
※Sheet1の方でWXYZが混ざって入力（「W」「Y」「 」など）されることはありません。

かつ、「Ｂ1」の日付の部分に、
「1」を入力したら、「C15」「C16」「C17」を参照して、
「2」を入力したら、「C18」「C19」「C20」を参照して、
「3」を入力したら、「C21」「C22」「C23」を参照・・・・
と出来れば理想です。

ご回答頂いてから自分でも調べていますが、
1人1セルにして綺麗に並べる方法がわかりません・・・
出来上がりは綺麗ではなくなってしまいますが、諦めて数式が許す範囲の人数で複数のセルにわけて、日付の方の解決に専念しようかとも思っています。

もしいいアドバイスがありましたらよろしくお願いいたします。

回答No.2

ionatsu

作業用シートを作ると全て解決するように思います。
[sheet1]
これはあなたのメインシートと同じですが、参照したい表が途中にあってややこしいので、全て左上にくっつけて考えています。。（/はセルの区切り、:は行名とセルとの区別を示します）
Ａ:氏名/(3つ横に結合して)1/(3つ横に結合して)2
Ｂ:田中/ /A/A/ /A/A
Ｃ:鈴木/ /A/a/ / /
[sheet2]
作業用シートです。以下のように作ってみて下さい。（同様とします）

Ａ:=concatenate(A3,A4)/=concatenate(B3,B4)
Ｂ:1/2
Ｃ=IF(AND(COUNTIF(Sheet1!C2:D2,"A")=2,Sheet1!B2=""),Sheet1!A2,"")/=IF(AND(COUNTIF(Sheet1!F2:G2,"A")=2,Sheet1!E2=""),Sheet1!A2,"")
Ｄ:=IF(AND(COUNTIF(Sheet1!C3:D3,"A")=2,Sheet1!B3=""),Sheet1!A3,"")/=IF(AND(COUNTIF(Sheet1!F3:G3,"A")=2,Sheet1!E3=""),Sheet1!A3,"")

ちなみに、ここまで完成させて、以下のように見えていれば成功です。
Ａ:田中鈴木/田中
Ｂ:1/2
Ｃ:田中/田中
Ｄ:鈴木/

[sheet3]
あなたの言うところの、結果を表示させたい別シートです。以下のように入力して下さい。

Ａ:1
Ｂ:=INDEX(Sheet2!1:1,1,A1)

入力したらこのように結果がでます。

Ａ:1
Ｂ:田中鈴木

ちなみに、このA1セルが、あなたのいう「１と入れれば１日目を参照」です。2に変えればおわかりいただけると思います。以上です。

お礼

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

(COUNTIF(Sheet1!C3:D3,"A")=2,Sheet1!B3="")この方がスマートですね。
日付を変える部分も指摘いただきありがとうございます！

回答No.1

noname#42041

シンプルに
田中さんの表示条件
=IF(AND(Sheet1!R4C15="",Sheet1!R4C16="A",Sheet1!R4C17="A"),Sheet1!R4C4,"")
と
鈴木さんの表示条件
=IF(AND(Sheet1!R5C15="",Sheet1!R5C16="A",Sheet1!R5C17="A"),Sheet1!R5C4,"")
を＆でつなげばいいのでは？
”のみ”を考えすぎてややこしくなっているのかと思います。

=IF(AND(Sheet1!R4C15="",Sheet1!R4C16="A",Sheet1!R4C17="A"),Sheet1!R4C4,"")&IF(AND(Sheet1!R5C15="",Sheet1!R5C16="A",Sheet1!R5C17="A"),Sheet1!R5C4,"")

急いで書いたので間違っていたら失礼

お礼

ありがとうございます！出来ました。
２つの式に分けて繋げば良かったのですね。

括弧の中に入れるのに夢中でした・・・。