[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『特定の複数文字を含む行のセルを抽出』（ななな）
要約がうまく伝えられていないかもしれません。
過去に数度、お世話になりました。
その節はありがとうございました。
今回は下記内容のお力をいただければと投稿させていただきます。
１年分の出社予定表があります。
とある列には日付、とある行には名前があり、50名ほどおります。
また、表には開始日には「出」、終了日には「帰」、合間は出社場所（複数）の単語（アルファベット）がそれぞれ入力されています。
空欄の場合は休みです。
＜例＞
1/1 2 3 4 5 6 7
太郎 出 A A A A 帰
花子 E E E E E E E
次郎
三郎 C C C 帰
毎週月〜日曜日を一週間として管理しており、
表の下にでもその週に動いている人の名前を表示させたいのですが可能でしょうか？
上記の例でいうと、この一週間には
・太郎
・花子
・三郎
の3名が動いているので、指定したセル〜セルの間に表示したい。
お手数おかけしますが、よろしくお願い致します。
＜ 使用 Excel：Excel2016、使用 OS：Windows10 ＞
名前がA列で１行目が日付だとして。
=IFERROR(INDEX(A:A,SMALL(IF(COUNTIF(INDIRECT("B"&ROW($2:$5)&":H"&ROW($2:$5)),""),ROW($2:$5),""),ROW(A1))),"")
と入力してShiftキーとCtrlキーを押しながらEnterキーで式を確定（確定後、式が{}で囲まれればOK）して、下へフィルコピーではどうか。
（ねむねむ） 2018/10/29(月) 13:27
すまない、下記に修正してくれ。
(Shift+Ctrl+Enterは忘れずに）
=IFERROR(INDEX(A:A,SMALL(IF(COUNTIF(INDIRECT("B"&ROW($2:$5)&":H"&ROW($2:$5)),"*"),ROW($2:$5),""),ROW(A1))),"")
（ねむねむ） 2018/10/29(月) 13:29
ありがとうございます！
早速エクセルに反映したのですが、うまくいきませんでした。
今回の場合、
・名前：D列
・日付：8行目
の為、計算式にはその様に書き換えました。
また、{}で囲まれることも確認しました。
一つ不足していた情報で大変恐縮なのですが、
出勤に関する以外の単語も（予定日を「予」などと）記載されておりました。
このせいでうまく反映できなかったのでしょうか。
こちらでもいただいた計算式が何を表しているか検索しながら、
作成できるように進めていきたいと思います。
ありがとうございます。
また、当方に不足している考え方がございましたら
ご教授いただけますと助かります。
（ななな） 2018/10/29(月) 13:51
＞一つ不足していた情報で大変恐縮なのですが、
＞出勤に関する以外の単語も（予定日を「予」などと）記載されておりました。
＞このせいでうまく反映できなかったのでしょうか。
こちら、【拾いたくない単語（複数）】もしくは【拾いたい単語（複数）】のどちらかで指定する事は可能でしょうか？
何度も申し訳ありませんが、よろしくお願い致します。
（ななな） 2018/10/29(月) 15:03
拾いたい単語複数のパターン。
作業列を使う。
I2セルに
=SUM(COUNTIF(B2:H2,{"A","B"}))
と入力して下へコピーする。
そして式を
=IFERROR(INDEX(A:A,SMALL(IF(I$2:I$10,ROW($2:$10),""),ROW(A1))),"")
としてくれ。
Shift+Ctrl+Enterは忘れずに。
（ねむねむ） 2018/10/29(月) 16:13
I列の式の{"A","B"}の部分を拾いたい単語としてくれ。
上記の場合はA、Bを抜き出す。
（ねむねむ） 2018/10/29(月) 16:18
拾いたくない単語の場合はI列の式を =COUNTIFS(B2:H2,"<>A",B2:H2,"<>B",B2:H2,"<>") としてくれ。 上記式ではA、B、空白セルを対象外とする。 （ねむねむ） 2018/10/29(月) 16:20
ありがとうございます。
早速、今回は拾いたくない単語で作成してみたところ、
１週間の管理が非常に楽になりました。
応用の話で恐縮ですが、上記上手くいったのでこれもできれば・・・
と思い質問させていただきます。
【1】
たとえば名前の横に役職欄を作成し、「課長」「主任」など入力。
指定した役職の方のみ、その週に動いている人の名前を表示させることは可能でしょうか？
【2】
例えば1/1〜1/31までの表も、月曜始まりにして1週間ごとに名前を表示させることは可能でしょうか？
上記2点ができるのであれば、さらに管理の工数を削減する事ができそうです。
調べても応用が絡むと反映する事ができず、聞いてばかりで申し訳ありませんが・・・。
どうぞよろしくお願い致します。
（ななな） 2018/10/29(月) 17:25
表はひと月（1日から月の最終日）の表になるのだろうか？ それともその月の第一月曜から最終日曜日の表となっているのだろうか？ もし1日から最終日の場合、例えば1月だと最終日が水曜日になるがその場合、 29(月)、30(火)、31(水)の集計になるのだろうか？ （ねむねむ） 2018/10/30(火) 09:11
おはようございます。
ご返信ありがとうございます。
表は1年（2018年1月1日〜12月31日）のものがベースとしてあります。
＜例＞
1/1 2 3 4 5 6 7
太郎 出 A A A A 帰
花子 E E E E E E E
次郎
三郎 C C C 帰
↑この表が12/31まで続いています。
これまでは手作業で必要期間を切り出しており、
その場合は月〜日曜日を1週間として扱っていたため、
その月の第一月曜から最終日曜日の表にしていました。
また、2か月分にする事もあれば、中旬〜下旬までといった、
必要に応じた作成をしておりました。
＞もし1日から最終日の場合、例えば1月だと最終日が水曜日になるがその場合、
＞29(月)、30(火)、31(水)の集計になるのだろうか？
→ フォーマット上3日だけで表示させる事がなく、
1/29（月）〜2/4（日）までの表にしていました。
お手数おかけしますが、ご検討をお願い致します。
（ななな） 2018/10/30(火) 09:57
ひとまず1を。 B列に役職があるとして =COUNTIFS(B2:H2,"<>A",B2:H2,"<>B",B2:H2,"<>") の式を =COUNTIFS(C2:I2,"<>A",C2:I2,"<>B",C2:I2,"<>")*(B2="課長) としてみてくれ。 2のほうは現在考え中。 （ねむねむ） 2018/10/30(火) 12:00
なななさんの要望にピッタリとはいかないが。 まず、元の表はSHEET1でA列が名前、B列が役職、C列からND列が日ごとのデータで1行目が各日にち（2018/1/1の日付データ）とする。 別シートのA1セルに抜出開始日付（例えば2018/1/1と入力すると2018/1/1から2018/1/7と開始日付から7日間を計算対象とする）
（ねむねむ） 2018/10/30(火) 13:30
B1セルに =A1-DATE(YEAR(A1),1,1)+1 と入力。 C1セルに抜き出したい役職を入力（C1セルが空白の場合はすべての人が対象）。 （ねむねむ） 2018/10/30(火) 13:33
A2セルに =COUNTIFS(OFFSET(INDEX(Sheet1!C2:ND2,,$B$1),,,,7),"<>A",OFFSET(INDEX(Sheet1!C2:ND2,,$B$1),,,,7),"<>B",OFFSET(INDEX(Sheet1!C2:ND2,,$B$1),,,,7),"<>")*COUNTIF(Sheet1!B2,"*"&C$1&"*") と入力して下へフィルコピー。 (この時参照する元データと行が合うようにしてくれ。元データが3行目からであれば式も3行目から） （ねむねむ） 2018/10/30(火) 13:34
最後に =IFERROR(INDEX(Sheet1!A:A,SMALL(IF(A$2:A$10,ROW($2:$10),""),ROW(A1))),"") と入力してShift+Ctrl+Enterで式を確定しそれを下へフィルコピーしてみてくれ。 （ねむねむ） 2018/10/30(火) 13:35
すまない。 A2セルの式を =COUNTIFS(OFFSET(INDEX(Sheet1!C2:ND2,,$B$1),,,,7),"<>A",OFFSET(INDEX(Sheet1!C2:ND2,,$B$1),,,,7),"<>B",OFFSET(INDEX(Sheet1!C2:ND2,,$B$1),,,,7),"<>")*OR(Sheet1!B2=C$1,C$1="") に修正してくれ。 （ねむねむ） 2018/10/30(火) 13:41
ありがとうございます！
これから取り掛かってみたいと思います。
少しずつ意味を調べながら頑張ってみます。
（ななな） 2018/10/30(火) 13:52
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.