gooドクター有料プランが1ヶ月間無料!

数年前、出勤シフト表作成についてお尋ねして、有意義な回答を頂きました。その折に、教えて頂いた数式を使って新たに以下のような出勤表(午前、午後に各2名出勤)を作成しました。
sheet1の該当日のセルに「1」を入力すると、名前入りの出勤シフト表であるsheet2中の該当日セルに出勤者の名前が載るというものです。(エクセル2007使用中、エクセル関数初心者です)

今回、当時教えて頂いた数式に真似て、sheet2のF3セル(午前に該当)に
以下の数式

=IFERROR(INDEX(Sheet1!$A$4:$A$32,SMALL(IF((Sheet1!$B$4:$B$33=$F$2)*(OFFSET(Sheet1!$B$4:$B$33,,MATCH($B3,Sheet1!$1:$1,0)-2,,1)=1),ROW($A$1:$A$30)),COLUMN(A1))),"")
を入力、教えて頂いたように、Ctrl+shft+enterを押して配列数式なるものを作成、これをコピーしたところ、午前は該当箇所に希望通りに名前が出ました。

ところが、sheet2のI3セル(午後に該当)に
以下の数式

=IFERROR(INDEX(Sheet1!$A$4:$A$32,SMALL(IF((Sheet1!$B$4:$B$33=$I$2)*(OFFSET(Sheet1!$B$4:$B$33,,MATCH($B3,Sheet1!$1:$1,0)-2,,1)=1),ROW($A$1:$A$30)),COLUMN(A1))),"")

入力、同様に配列数式にしても、午後の該当箇所に名前が出ずに、「0」が出ます。
午前の数式中の=$F$2を午後の数式中で=$I$2に変えただけです。

午後の数式のどこを直したら、該当箇所に名前が出るようになりますか? 
回答のほどよろしくお願いいたします。

「sheet1で数字1を入力、sheet2」の質問画像
gooドクター

A 回答 (5件)

[No.4お礼]へのコメント、


》 数式中にある「NAM」「A_P」「TBL」について、…
》 …お教え頂きましたら、幸いです。
添付図参照(Excel 2019)

セル(範囲)に“名前”を付けるのは、
“スカートと式の長さは短いのがお好き”な
私の性癖(?)です。(*^_^*)

ちなみに、咖啡はこちら↓(^_^)
h ttps://www.youtube.com/watch?v=8fRrcb2BqoY
「sheet1で数字1を入力、sheet2」の回答画像5
    • good
    • 0
この回答へのお礼

早速のご回答ありがとうございます
名前の管理で使った「名前」だったのですね
カレンダー作成時の祝日、年末年始休日等で名前の管理は使っております。
名前の意味が理解出来ました。
ありがとうございます。

お礼日時:2021/04/19 14:37

肝心な箇処だけネ。


ヒントということで、長くなりそうな解説は省略します。

Sheet2 において、
F3: =IFERROR(INDEX(NAM,SMALL(IF((A_P=F$2)*(INDEX(TBL,,$C3)=1),ROW(NAM)),COLUMN(A1))-3),"")

I3: =IFERROR(INDEX(NAM,SMALL(IF((A_P=I$2)*(INDEX(TBL,,$C3)=1),ROW(NAM)),COLUMN(A1))-3),"")

【お断わり】上式は何れも必ず配列(CSE)数式として入力のこと
「sheet1で数字1を入力、sheet2」の回答画像4
    • good
    • 0
この回答へのお礼

遅くなりました。
ご回答ありがとうございます
昨夜、悪戦苦闘して一応問題解決出来ました。
今回、お教え頂きました数式では3人(Eさんは間違えて入力しました)も名前入りで出来るものでした。今後勤務先の体制変化も考えられますので、後日、必ず検討させて頂きます。
数式中にある「NAM」「A_P」「TBL」について、どのようなことを意味しているのか理解できません。
各項目についてお教え頂きましたら、幸いです。

お礼日時:2021/04/19 11:44

確認させてください。



Sheet1 を眺むるに、1日(日曜)のPMに1が入力されている人は B、C、E の3名ですよね?
嗚呼それなのに、それなのに、Sheet2 のAM/PM欄は各2名分のスペースしかないのはなぜですか?そんだけェ~で構わないと?
    • good
    • 0
この回答へのお礼

確認の件、ご返事が遅くなり申し訳なく存じます。
こちらの、入力ミスです。Sheet2 のAM/PM欄は各2名分しか勤務しません。
仕事先は、小さなところで勤務者も所謂有償ボランティアのようなものです。Sheet1中に各人が出勤出来ない日に×を入れて、AM/PM2人体制のシフトを毎月作成。各人の出勤の可否、また「1」を入れるだけで名前入りシフト表が出来、シート2枚で済むので気に入っています。

お礼日時:2021/04/19 11:44

こんにちは



すでに回答がでていますけれど・・・

>エクセル2007使用中
とのことなので、配列数式しかないのかな…
もう少し後のバージョンだと配列数式にしなくても計算できるのですが…

ご提示の式では該当する行番号を求めていますけれど、PMの場合は必ず奇数行の番号になります。
A列からその行番号に該当する名前を参照すると、空白セルなので「0」と表示さることになります。
これが、ご質問の状態で起こっていることと考えられます。

ですので、そのあたりを調整なされば良いのではないでしょうか。
    • good
    • 0
この回答へのお礼

お礼が遅くなりました。
回答1の方の数式説明に加えてあなた様の回答内容にあった,「A列」のキーワードを参考にして、このシフトの仕事が終わった昨夜、悪戦苦闘して、
I3列に
=IFERROR(INDEX(Sheet1!$A$1:$A$32,SMALL(IF((Sheet1!$B$4:$B$33=$I$2)*(OFFSET(Sheet1!$B$4:$B$33,,MATCH($B3,Sheet1!$1:$1,0)-2,,1)=1),ROW($A$3:$A$30)),COLUMN(A1))),"")

を入れたところ、解決出来ました。
補足して頂きまして本当にありがとうございました。
コロナワクチン優先に該当する年齢なので、数式は嫌いではないのでが、なかなかですね。
ご回答頂きましてありがとうございました。

お礼日時:2021/04/19 11:04

教えていただいた数式の意味を理解せずに利用しようとした結果がそれなんです。


理解するための努力はしましたか?

便利に使える ”テンプレート” を作ってくださいという作業依頼をしたのです。
使いまわせないのであれば諦めましょう。

業務で使うなら自身で作れるようになるか、お金を出して依頼しましょう。
有料で作成してくれるサイトは多数あります。
「ココナラ」であれば1000円くらいから受けてくれるようです。


・・・そんなわけで数式の説明(長いよ)・・・

どこを直せばよいのかの説明ではありません。
また、理解したくない人には ”分かりやすい説明” ではりません。
そして数式に対する説明ですので、その数式が何を示してるのかは質問者さん自身で関連付けて考えてください。
それができなければ、以下の説明は読む価値は ”まったく” ありません。
ここは作業依頼をするサイトではなく、自身で問題解決のためのアドバイスをもらう場所だからです。

=IFERROR(
     INDEX(
        Sheet1!$A$4:$A$32
        ,SMALL(
            IF(
             (Sheet1!$B$4:$B$33=$F$2)*(OFFSET(Sheet1!$B$4:$B$33,,MATCH($B3,Sheet1!$1:$1,0)-2,,1)=1)
             ,ROW($A$1:$A$30)
             )
            ,COLUMN(A1)
            )
        )
     ,"")
大きく分けるとこんな感じになる。

IFERROR関数はエラー処理なので省略して考えます。
=INDEX(
    Sheet1!$A$4:$A$32
    ,SMALL(
        IF(
         (Sheet1!$B$4:$B$33=$F$2)*(OFFSET(Sheet1!$B$4:$B$33,,MATCH($B3,Sheet1!$1:$1,0)-2,,1)=1)
         ,ROW($A$1:$A$30)
         )
        ,COLUMN(A1)
        )
    )
こう考えると少しは楽かな。
もう少し整理して考えます。

=INDEX(
    Sheet1!$A$4:$A$32
    ,SMALL(IF((Sheet1!$B$4:$B$33=$F$2)*(OFFSET(Sheet1!$B$4:$B$33,,MATCH($B3,Sheet1!$1:$1,0)-2,,1)=1),ROW($A$1:$A$30)),COLUMN(A1))
    )
 ↓
=INDEX(
    Sheet1!$A$4:$A$32
    ,SMALL(~)
    )
これで何をしているのかが分かると思います。
SMALL関数が返す数値で、Sheet1のA4セルからA32セルの中から値を選んでいるのです。
(A4セルからA32セルが何の値なのかを関連付けて考えてください)
値が見つからない場合はエラーになります。エラーなら先にエラー処理のために使っていたIFERROR関数で回収されます。

ここからSMALL関数の中身を考える。

SMALL(
   IF(
    (Sheet1!$B$4:$B$33=$F$2)*(OFFSET(Sheet1!$B$4:$B$33,,MATCH($B3,Sheet1!$1:$1,0)-2,,1)=1)
    ,ROW($A$1:$A$30)
    )
   ,COLUMN(A1)
   )
 ↓
SMALL(
   IF(~)
   ,COLUMN(A1)
   )
COLUMN関数が返す数値で、IF関数が返す範囲から値を選んでいます。
値が見つからない場合はエラーになります。エラーなら先にエラー処理のために使っていたIFERROR関数で回収されます。

そしてIF関数の中身を考える。

IF(
  (Sheet1!$B$4:$B$33=$F$2)*(OFFSET(Sheet1!$B$4:$B$33,,MATCH($B3,Sheet1!$1:$1,0)-2,,1)=1)
  ,ROW($A$1:$A$30)
 )
 ↓
IF(
  (~)
  ,ROW($A$1:$A$30)
 )
条件が成立したときに、ROW関数の値を返すようになってます。
条件が成立しない時はエラーになります。エラーなら先にエラー処理のために使っていたIFERROR関数で回収されます。

さらにIF関数の ”条件式” について考える。

 (Sheet1!$B$4:$B$33=$F$2)
 *
 (OFFSET(Sheet1!$B$4:$B$33,,MATCH($B3,Sheet1!$1:$1,0)-2,,1)=1)
 ↓
 (Sheet1!$B$4:$B$33=$F$2)
 *
 (OFFSET(~)=1)
Sheet1のB4からB33セルの値がF2セルの値と同じならTRUE。
OFFSET関数で返ってきた値が1ならTRUE。
掛け算になっているので、
 どちらもTRUEなら「1」
 一つでもFALSEなら「0」
が返る。

さらにOFFSET関数の中身を考える。

OFFSET(
    Sheet1!$B$4:$B$33
    ,
    ,MATCH($B3,Sheet1!$1:$1,0)-2
    ,
    ,1
    )
Sheet1のB4からB33セルの範囲を基準に、MATCH関数で返った数値から2を引いた数値の分だけ右方向の列の値を返す。

MATCH関数については説明はいりませんね。
分からないのであれば、自身で調べてみましょう。

・・・

とまあ、こんな感じです。
どこを直せばよいのか分かりましたか?
ちょっと難しい内容ですが、理解しようと考えを巡らせば必ず答えにたどり着きます。


・・・余談・・・

気になった数式なので解析してみましたが、本当によく考えられた数式です。無駄がない。
OFFSET関数の4つ目5つ目のパラメータは本来なら必要無いんですが、配列数式にするために敢えて指定するとか、
エラーは値が無い場合なので、IFERROR関数でまとめて処理するとか、
答えて下さったかたの知識に脱帽します。

作成にあたり、かなりの試行錯誤をされていると思います。
もしこれを実機を使って作られたのでなければ、そのかたは天才ですよ。
    • good
    • 0
この回答へのお礼

この仕事の午後出勤日でしたのでお礼が遅れました。
長文のご回答ありがとうございました。
数式に対する詳しいご説明ありがとうございます。
数式理解の助けにかなりなりました。
今回、回答2の方の補足回答内容も参考にして、昨夜帰宅後に、数時間悪戦苦闘し、
I3セルに、
=IFERROR(INDEX(Sheet1!$A$1:$A$32,SMALL(IF((Sheet1!$B$4:$B$33=$I$2)*(OFFSET(Sheet1!$B$4:$B$33,,MATCH($B3,Sheet1!$1:$1,0)-2,,1)=1),ROW($A$3:$A$30)),COLUMN(A1))),"")

を入れたところ、一応解決が出来ました。
数式理解に努めてはいますが、コロナワクチン優先に該当するような年齢なのでなかなか身につかないですね。
数式は嫌いではないので、今回のご説明内容を基に、より理解を深めたいと存じます。
ありがとうございました。

お礼日時:2021/04/19 10:46

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

gooドクター

このQ&Aを見た人がよく見るQ&A

このカテゴリの人気Q&Aランキング