EXCELのDATAを抽出して日別の表を作る式は

質問

最近は自分で表などを作る機会が少ない高齢者ですが、
目的は、Office2013　EXCELで
AH2セルに、１日,鹿児島,山口,広島
AH３セルに２日熊本,大分,長崎　　
という文字列をそれぞれ一つのセル(AH2セルからAH32)に書き出したいのです、

EXCELの表の１行目にB列からAF列まで1から31までの一ヶ月の日付を書き入れ、
A列の2行（A２セル）から21行（A２1セル）に氏名を書き入れている表の、
DATAとして、　B2セルからAF21セルの、不特定のセルの★や☆の記号を、
一番目に、
B2～B21のなか（仮にB3セル）に　★☆の記号が有ったら、　
その行の該当氏名（鹿児島（A3セル））をWORD文字列に、書き出し、
二番目に、
B2～B21のなか(仮にB5セル)に　★☆の記号が有ると、
その行の該当氏名（山口（A５セル））をWORD文字列に、書き出すことを、

一ヶ月すべての日付（B1セルからAF1セルに該当）の★☆がある列
（B2セルからAF21セルのすべて）から抽出して、
AH2セルからAH32セルに書きだす　 式 / 関数のご指導お願いいたします。

回答No.2

MackyNo1 · ベストアンサー

Excelでは複数のデータを1つのセルに表示するのは合理的な操作法ではありません。

添付画像のようにAIセルから右に該当する名前を列挙するならA1セルに以下の式を入力して右方向および下方向にオートフィルします。

1日、名前1、名前2、…のようなテキストが欲しいなら（Wordに貼り付けたいなら）以下のような手順で処理することができます。

AH2から表示データ範囲を選択し右クリック「コピー」、そのままワードの新規ファイルに「貼り付け」して、貼り付け後に右下に表示される貼り付けオプションをクリックして「テキストのみ」を選択します。
最後に、Ctrl+Hで置換ダイアログを出し、「オプション」ボタンを押し、「ワイルドカードを使用する」にチェックを入れ、検索する文字列に「^t」置換後の文字列に「、」を入力いして「すべて置換」し、そのまま検索する文字列に「、^13」置換後の文字列に「^13」で「すべて置換」し、さらに検索する文字列を「、、」置換後の文字列に何も記入せず「すべて置換」してください。

お礼

早速のご指導ありがとうございます。
『貼り付けオプションをクリックして「テキストのみ」を選択』は日常的に使用していましたが、
検索と置換という私には未体験のダイアログの利用方法を、何度も繰り返しながら、試行を続けています。
　是非習熟して活用させていただきます。
お忙しいなかご指導ありがとうございました。

補足

OKWaveのルールでは　ベストアンサーはおひとりしか選べないのと
ご指導の手順で、
最初の「すべて置換」　の時点でボタンを押しますと　
「0個の置換、、、」と表示されて、
　ご指導の『そのまま検索する文字列に「、^13」置換後の文字列に「^13」で「すべて置換」し、さらに検索する文字列を「、、」置換後の文字列に何も記入せず「すべて置換」してください』
に進めません。　このように思いましたので、
一度この質問を終了させていただいてから、別件でこの疑問を質問させてください。

回答No.3

MackyNo1

>ご指導の手順で、 最初の「すべて置換」　の時点でボタンを押しますと「0個の置換、、、」と表示されて、

添付画像のようにAH2セルから右方向にご希望のデータを表示できたということでよいのですね。

もし、表示できているのであれば、対象セル範囲をコピーしてwordにテキスト貼り付けすれば、セルの間隔がタブで区切られた文字列になっていると思います。

このような書式になっていれば、すでに回答したようにワイルドカードを使用して検索する文字列に「^t」（タブ文字・・・そのままコピー貼り付けしてください）を指定すればタブが検索されるはずです。

回答No.1

dogs_cats

同一セル内に全てを抽出する数式は無いのではないかと思います、仮にあったとしてもとても長い数式で理解出来る数式にはならないかもしれません。

同じ表の範囲+１行文の作業列を作成する方法で良ければお試し下さい。

20人分の作表をされるようですが、人数が増加しない事を前提としてB23から作業列を設定
指定数式を指定セルにコピーして下さい。
B23式
=IF(COUNTIF(B2,"☆")+COUNTIF(B2,"★")>0,B$1&"," &$A2,"")
B23式をAF23までコピー
B24式
=IF(COUNTIF(B3,"☆")+COUNTIF(B3,"★")>0,IF(B23<>"",B23&","&$A3,B$1&","&$A3),B23)
B24式をAF42までコピー
B43式
=IF(B42="","",COUNTA($B$42:B42)-COUNTBLANK($B$42:B42))
B43式をAF43までコピー
AH2式
=IF(MAX($AJ$22:$BN$22)<ROW(A1),"",INDEX($AJ$21:$BN$21,MATCH(ROW(A1),$AJ$22:$BN$22,0)))
AH2式をAH32までコピー

表を印刷する時は印刷範囲の設定でA1~AH21にするか、23~42行を非表示にして下さい。

人数増員がある場合はAJ２以降に作業列設定
AJ2式
=IF(COUNTIF(B2,"☆")+COUNTIF(B2,"★")>0,B$1&"," &$A2,"")
AJ2式をBN2までコピー
AJ3式
=IF(COUNTIF(B3,"☆")+COUNTIF(B3,"★")>0,IF(AJ2<>"",AJ2&","&$A3,B$1&","&$A3),AJ2)
AJ3式をBN21までコピー
B22式
=IF(AJ21="","",COUNTA($AJ$21:AJ21)-COUNTBLANK($AJ$21:AJ21))
B22をBN22までコピー
AH2式
=IF(MAX($AJ$22:$BN$22)<ROW(A1),"",INDEX($AJ$21:$BN$21,MATCH(ROW(A1),$AJ$22:$BN$22,0)))
20人以上増員する場合は、B21式を下方に増やす。
最終行の下に
=IF(AJ21="","",COUNTA($AJ$21:AJ21)-COUNTBLANK($AJ$21:AJ21))を修正
１人増員すれば
=IF(AJ22="","",COUNTA($AJ$22:AJ22)-COUNTBLANK($AJ$22:AJ22))となります。

AHの数式もセル範囲の修正が必要になります。

セルアドレスの＄マークの位置によって絶対参照（行も列も固定）、複合参照（行固定、列固定）、となりますので間違わないようにして下さい。
絶対参照、複合参照は下記参照
http://www.eurus.dti.ne.jp/~yoneyama/Excel/zettai.html

添付画像はB23以降に作業列を設けた場合です。
表全体を表示すると見えませんので、８~19行、25~40行、E~AD列は非表示にしています。AH列のデータは日付欄に指定マークが無い場合は日付を飛ばして表示するようにしています。

VBAで実施する事を勧める方もいるでしょうが、VBAの知識の無い方にコードを記載しない事にしていますので了承下さい。

お礼

早速のご指導ありがとうございます。
添付画像のファイルから　稚拙な質問文に対して、希望の内容が実現可能と思いました。
ご指導内容を、私のレベルでは時間が掛かりそうですが、
順に取り込んで勉強いたします。
ありがとうございました。