HOME > Q&A > Excel （一般機能）

Excel （一般機能）
Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 11 Home : Excel 2019)
色付きセルの値を取り出したい
投稿日時: 23/07/05 16:27:14
投稿者: なつ

いつも参考にさせていただいています。
 
表題のとおり、色付きセルの値を取り出したいです。
 
座席表にそれぞれ番号が振ってあり、そのうち色で塗りつぶされたセルの
値(席番号)をすべて取り出したいです。
 
・セル色付けのルールはありません。手作業でランダムに色付けされています。
 
・座席表は空白行をはさみながら全部で11行あります。
 
一番上の列が　B2:J2(9席)、L2:T2(9席)、V2:AD2(9席)
その一段下が　B4:J4(9席)、L4：T4(9席)、V4：AD4(9席)と一行ごとに
空白行を含み、10行までは同じ配列ですが、一番下のみD22:J22(7席)、
L22:T22(9席)、V22：AB22(7席)　となっています。
 
・塗りつぶしは1セル置きだったり、すべての行が塗りつぶされていたり
規則性がないです。
 
以上のような状況で、色を付けたセルの値(席番号)を取り出すには
どうすればいいでしょうか。
 
よろしくお願いいたします。
回答
投稿日時: 23/07/05 17:45:42
投稿者: 半平太

セルの塗りつぶしを判定できる関数は無かったと思います。
 
なので、VBAになりますが、それでも数式のようなリアルタイム性はないです。
確実な結果を取得したいと思ったら、
その都度何等かの「あらかじめ決めた方法」でVBAを実行させる必要があります。
 
それでよければVBA案を提示しますが、
「すべて取り出す」とは具体的にどう云う状態にするのか、
その説明がないとコードは書けません。

回答
投稿日時: 23/07/05 18:18:56
投稿者: WinArrow

半平太さんのレスの通り、Ｅｘｃｅｌに備わっている関数には、色（セルの書式の塗りつぶし、文字色など）
を取得する関数はないと思います。
関数の殆どはセルの値を参照するもので、書式は対象にはなりません。
 
ユーザー定義関数など、ＶＢＡで対応する方法を推奨します。
 
取り出す先を指定する必要があるので、
どこに取り出す（多分、参照するセルと相対的な位置になる）セルを説明してください。
 
>規則性がないです。
その日の気分で仕事をしているのですか？

回答
投稿日時: 23/07/05 18:28:44
投稿者: sk

引用:
座席表にそれぞれ番号が振ってあり、そのうち色で塗りつぶされたセルの
値(席番号)をすべて取り出したいです。

引用:
一番上の列が　B2:J2(9席)、L2:T2(9席)、V2:AD2(9席)
その一段下が　B4:J4(9席)、L4：T4(9席)、V4：AD4(9席)と一行ごとに
空白行を含み、10行までは同じ配列ですが、一番下のみD22:J22(7席)、
L22:T22(9席)、V22：AB22(7席)　となっています。

（標準モジュール）
----------------------------------------------------------------
Sub TestMacro1()
 
    Dim rngSeatsArray As Range
    Dim rngSeats As Range
    Dim rngSeat As Range
    Dim strUsedSeatIDs As String
     
    'B2セルからAD22セルまでの範囲に含まれる定数セルを参照
    Set rngSeatsArray = Range("B2:AD22").SpecialCells(xlCellTypeConstants)
     
    'セル領域ごとにループ
    For Each rngSeats In rngSeatsArray.Areas
        '参照中のセル領域内のセルごとにループ
        For Each rngSeat In rngSeats.Cells
            '参照中のセルの塗りつぶし書式が「塗りつぶしなし」ではない場合
            If rngSeat.Interior.ColorIndex <> xlColorIndexNone Then
                'そのセルの値（席番号）をカンマ区切りで文字列変数に追記
                strUsedSeatIDs = strUsedSeatIDs & _
                                 "," & rngSeat.Value
            End If
        Next
    Next
     
    '文字列変数の値から先頭のカンマを取り除く
    strUsedSeatIDs = Mid(strUsedSeatIDs, 2)
     
    '文字列変数の値をイミディエイトウィンドウに出力
    Debug.Print strUsedSeatIDs
    'ついでにメッセージボックスに表示
    MsgBox strUsedSeatIDs
 
End Sub
----------------------------------------------------------------
 
基本的には、以上のようなマクロを実行なさればよろしいでしょう。
回答
投稿日時: 23/07/05 20:44:18
投稿者: WinArrow

値(席番号)をセルに表示する「ユーザー定義関数」を紹介します。
  
標準モジュールに、次のコードを貼り付けます。 

Function CheckBackcolor(ByVal RC As Range)
    CheckBackcolor = ""
    If RC.Interior.ColorIndex <> xlColorIndexNone Then
        If RC.Value <> "" Then
            CheckBackcolor = RC.Value
        End If
    End If
End Function

 
  
当該シートの任意のセルに、
=CHECKBCKCOLOR(B2)
のような数式を入力します。
  
意味：セルＢ２について
「塗りつぶしが設定されている」＆「空白以外」を条件で
セルＢ２の値が返ります。
  
このままだと、何処のセルの値なのか判別できないと思いますので、
どの様に表示すればよいのかを整理して、自分で修正するなど、してお使いください。
  
投稿日時: 23/07/05 21:48:05
投稿者: なつ

皆さま、コメントありがとうございます。
 
半平太さん
 
関数ではできないのですね。
 
> 「すべて取り出す」とは具体的にどう云う状態にするのか
 
出席者の席決めのため、色つきセル(実際に座る席の番号)を
同ブックの別シートに一列に取り出し、所属ごとに固まらないように
乱数で出席者Noを並べ替えて席を決めるための準備です。
以下のようなイメージです。
 

　    A
1    席番号
2      231
3      125
4      182

 
WinArrowさん
 
やはりVBA対応なのですね。
 
>取り出す先を指定する必要があるので、
>どこに取り出す（多分、参照するセルと相対的な位置になる）セルを説明してください
 
取り出す先は別シートのA列に取り出せればと思います。
座席表は　Sheet1、取り出すのはSheet2 にしたいです。
 
規則性がないのは、概ね１つおきなのですが、それだと席が足りないので
やむをえず続いて席を指定しているところがあるためです。(座席表は他の方が作っています)
 
書いているうちにご返事をいただいたようで、コードのご提示ありがとうございます。
 
skさん
 
コードのご提示をありがとうございます。
VBAの本を買ってあるので、コードの意味を調べつつ実行したいと思います。
 
皆さま、ありがとうございます。
実際のファイルは会社にあるため、明日挑戦してみます。
 
回答
投稿日時: 23/07/06 06:49:59
投稿者: WinArrow

引用:
座席表は　Sheet1、取り出すのはSheet2 にしたいです。
 

私に提案の「ユーザ定義関数」でよければ、
関数は、Sheet2のセルに関数を入力することにします。
引数をシート名付きに変更するだけです。
  
入力するセルは、Sheet１のセルと同じすると分かりやすいかも・・・
 
=CHECKBCKCOLOR(B2)
⇓
Sheet2のＢ２セルに
=CHECKBCKCOLOR(Sheet1!B2)
投稿日時: 23/07/06 09:09:52
投稿者: なつ

おはようございます。
 
skさん、WinArrowさん
 
お二方からご提示いただいたどちらのVBAでも座席番号がかえりました。
 
参加者数が変更になる可能性があるため、塗りつぶしの場所が変わる
ことがあるのですが、このVBAで対応できます。
 
本当にありがとうございました。