[[20180603212001]] 『特定条件を満たすセルの内容を指定した別のセルに』(shoko) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『特定条件を満たすセルの内容を指定した別のセルに一行ずつ抽出したい』(shoko)


画像のような表データがあるとして
出欠で×がついてる人の名前をI列に全て抽出させたいのですがどのような式を使えば可能でしょうか?

自分で試したのは以下の式です。

=INDEX(A3:A10,MATCH(A12,B3:B10,0))&INDEX(C3:C10,MATCH(A12,D3:D10,0))&INDEX(E3:E10,MATCH(A12,F3:F10,0))&INDEX(G3:G10,MATCH(A12,H3:H10,0))

これだと「×」が無い範囲の列(画像だとD列)が含まれると#N/Aが出てしまいました。
また、エラーが出ない場合でも一つのセルに「ローラマックスジョン…」のように名前が連続して表示されてしまいます。
一行につき一つの名前を表示させるにはどのような式にすればよいでしょうか。

< 使用 Excel:Excel2010、使用 OS:Windows7 >


 おはようございます ^^
マクロなら。。。何とか思いつくのですが。。。式は解かりません。
で
気が付いた点だけ
1.同じ人が複数日、欠席の場合はどうされるのですか。
2.日数は4日だけですか。
3.氏名欄は全て同じと考えていいですか。(同じなら、A列だけで良いような気が。。。^^;;;。)
など
ご説明されると、回答がつきやすいかもです。
(隠居じーさん) 2018/06/04(月) 06:51

アドバイスありがとうございます。
やはりマクロじゃないと難しいでしょうか…。

1.同じ人が複数日欠席の場合ですが、同じ名前が複数回登場することになります。
例)
ローラ
マックス
ローラ
ジョン...のように
2.日数は月ごとに集計なので基本4日だけです。年末年始やお盆の時期は2〜3日のときもあります。
3.氏名欄はここではわかりやすく揃えていますが、実際は月半ばからの参加やその逆があったりで名前は増えたり減ったりします。
わかりやすくしたつもりでしたが、実態にそぐわないので逆にややこしくしただけでした。ご指摘ありがとうございます。
あくまで四週それぞれの欠席者名を一つの列にリストアップしたいです。

(shoko) 2018/06/04(月) 23:54


 I3セル =IFERROR(INDEX($A:$G,MOD(AGGREGATE(15,6,(ROW(B$3:B$12)+COLUMN(A$3:G$3)*1000)/(B$3:H$12="×"),ROW(A1)),1000),INT(AGGREGATE(15,6,(ROW(B$3:B$12)+COLUMN(A$3:G$3)*1000)/(B$3:H$12="×"),ROW(A1))/1000)),"")

 下にコピー

 <結果図>
  行 _____A_____ __B__ _____C_____ __D__ _____E_____ __F__ _____G_____ __H__ _____I_____
   1                                                                         欠席者総数 
   2 氏名        欠席  氏名        欠席  氏名        欠席  氏名        欠席             
   3 ジョン      ×    マックス          ジョン            ジョン            ジョン     
   4 ローラ      ×    ジェイソン        ローラ            ローラ      ×    ローラ     
   5 エミール          キム              エミール          エミール          マックス   
   6 マックス    ×    ジョン      ×    マックス          マックス          ジョン     
   7 ジェイソン        ローラ            ジェイソン        ジェイソン  ×    キム       
   8 キム              エミール          キム        ×    キム              ローラ     
   9                                                                         ジェイソン 

(半平太) 2018/06/05(火) 11:17


 既に、回答がついていますが。 数式でできるのですね。 ^^
いや〜 いつもながら 勉強になります。
いちおう。。。VBA 作ってみましたので。
半平太さん の数式の方が名前が並んで、わかりやすいかもです。

 何時もの如くバックアップ必須 VBA。。。!  ^^;;;

 Option Explicit
Sub main()
    Dim sh01 As Worksheet
    Dim i As Long, j As Long, y As Long, rr As Range
    Set sh01 = Worksheets("Sheet1")
    Set rr = sh01.Range("A1").CurrentRegion
    Intersect(Range("I:I"), Range("" & rr.Row + 2 & ":" & rr.Rows.Count & "")).ClearContents
    y = 3
    For i = 3 To rr.Rows.Count
        For j = 2 To rr.Columns.Count
            If rr(i, j) = "×" Then
                rr(y, 9) = rr(i, 1)
                y = y + 1
            End If
        Next
    Next
End Sub
(隠居じーさん) 2018/06/05(火) 12:03

半平太さま、隠居じーさんさま

本当にありがとうございました。
ここの皆様の知識量に敬服するばかりです。
同時に自分の勉強不足をただただ実感します。

時間がなくてまだ自分で試せていないのですが、取り急ぎお礼まで…。

(shoko) 2018/06/05(火) 23:30


コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.