エクセル初心者です。
勤務は午前、午後、いずれも2名体制ですが、各自の都合があるので毎月出勤シフト表を組んでいます。午前と午後で勤務時間も違い、時折午前休みもあります。月の出勤日数(午前、午後の回数)や勤務時間の合計を出しやすいことからシート1のように、出勤する人には「1」を入力した月別勤務表を作成。ここからが本題です。
シート1で出勤することになった人(「1」を入力した人)を別シート2で名前を出したいです。シート2でプルダウンメニューを使えば名前が入ることは知っていますが、シート1の「1」の入力からシート2の名前を導き出す数式はありますか?
No.7ベストアンサー
- 回答日時:
数式を拝見しました。
おそらく最後のCOLUMN関数でB列を指定しているためだと思います。
このCOLUMN関数はSMALL関数「順位」(何番目か?)を求めている関数なので
B列を指定してしまうと「2番目」から表示されます。
(B1、B100、B1000でも列番号ですのですべて「2」となります)
表示したいのは配列数式が「TRUE」の行の小さい順から1番目と2番目ですので、
かならずA列から指定してやる必要があります。
お示しの数式ではおそらく2番目・3番目が表示されるのでは?
数式内の
>COLUMN(B2)
の部分を
>COLUMN(A1)
(もちろんCOLUMN(A2)でも問題ありません)
に変更したらどうなりますか?m(_ _)m
出来ました!!
数式の意味がわからないので、sheet2の数式を入力した際の指示がある色枠を原型から類推して試みた次第です。
sheet2ももう少し移動したケースも試みてみます!
これで何とか目安がつきそうです。
拙い質問ににお答えいただき、どの回答もベストアンサーです
ありがとうございました
No.6
- 回答日時:
だんだん難しくなっていくような気がします。
個人的にはあまり長い数式は好みではありません。
実際に自分が投稿した数式を変更する場合どこをどう修正していいのか判らなくなってしまい、
結局最初から数式を考えた方が間違いが少ない!ってコトの方が多いです。
今回はSheet1が↓のような配置になった場合の数式を載せてみます。
Excel2003に対応させようとすると長い数式になりますので、Excel2007以降に対応できる数式とします。
(今回も10人分としています。)
まずSheet1の作業列C3セルに
=OFFSET(B3,-MOD(ROW(A3),3),,1)&""
という数式を入れフィルハンドルで下へコピーしておきます。
Sheet2のは配置はそのままだとすると
Sheet2のB2セル(配列数式)は
=IFERROR(INDEX(Sheet1!$C$3:$C$32,SMALL(IF((Sheet1!$D$3:$D$32=$B$1)*(OFFSET(Sheet1!$D$3:$D$32,,MATCH($A2,Sheet1!$2:$2,0)-4,,1)=1),ROW($A$1:$A$30)),COLUMN(A1))),"")
D2セル(配列数式)は
=IFERROR(INDEX(Sheet1!$C$3:$C$32,SMALL(IF((LEFT(Sheet1!$D$3:$D$32,2)=$D$1)*(OFFSET(Sheet1!$D$3:$D$32,,MATCH($A2,Sheet1!$2:$2,0)-4,,1)=1),ROW($A$1:$A$30)),COLUMN(A1))),"")
といった感じになります。
今までの数式との違いは判るでしょうか?
試しにSheet1の「1日」の列の「1」の人を列方向に列挙する数式を載せておきます。
使っていないSheet3のどこのセルでもいいので
=INDEX(Sheet1!$C$3:$C$32,SMALL(IF(Sheet1!$E$3:$E$32=1,ROW($A$1:$A$30)),COLUMN(A1)))
(配列数式です)として、フィルハンドルで右へコピーしてみてください。
Sheet1の「1日」の列に「1」が入力されている人が羅列されそれ以降はエラーになると思います。
基本はこの配列数式になっています。
INDEX関数の「配列」の部分の範囲指定は問題ないと思います。
「行番号」の部分が配列数式になり、「1」が入力されている行の小さい順に表示します。
「列番号」はOFFSET関数関数とMATCH関数の併用で列を決めています。
最終的にはエラー処理とINDEX関数の行・列取得のための枝葉がついて
あのような長い数式になっています。
※ 本来であれば別シートにでも作業用の列を何列でも作成し自身が目的のセルを抽出しやすいような表にした方が良いと思います。
もちろん作成方法は色々あると思いますが、作り方によっては配列数式にしなくても
単純な関数だけで表示させることも可能だと思います。
そうすればメンテナンス面からみてもそちらをおススメします。m(_ _)m
No.5
- 回答日時:
こんばんは!
Excel2003でも対応できる数式ですか!
かなり長い数式になってしまいますが・・・
Sheetの配置は今までアップした画像通りとします。
Sheet2のB2セルに
=IF(SUMPRODUCT((Sheet1!$C$2:$C$31=$B$1)*(OFFSET(Sheet1!$C$2:$C$31,,MATCH($A2,Sheet1!$1:$1,0)-3,,1)=1))<COLUMN(A1),"",INDEX(Sheet1!$B$2:$B$31,SMALL(IF((Sheet1!$C$2:$C$31=$B$1)*(OFFSET(Sheet1!$C$2:$C$31,,MATCH($A2,Sheet1!$1:$1,0)-3,,1)=1),ROW($A$1:$A$30)),COLUMN(A1))))
D2セルに
=IF(SUMPRODUCT((LEFT(Sheet1!$C$2:$C$31,2)="PM")*(OFFSET(Sheet1!$C$2:$C$31,,MATCH($A2,Sheet1!$1:$1,0)-3,,1)=1))<COLUMN(A1),"",INDEX(Sheet1!$B$2:$B$31,SMALL(IF((LEFT(Sheet1!$C$2:$C$31,2)=$D$1)*(OFFSET(Sheet1!$C$2:$C$31,,MATCH($A2,Sheet1!$1:$1,0)-3,,1)=1),ROW($A$1:$A$30)),COLUMN(A1))))
どちらも配列数式ですので、Ctrl+Shift+Enterで確定してください。
こんな感じではどうでしょうか?m(_ _)m
No.4
- 回答日時:
続けてお邪魔します。
ん~~~
前回アップした配置の表で今こちらでこの画面上から数式をコピー&ペーストして確認してみるとちゃんと表示されました。
今一度確認してみてください。
① お手元のSheetは↓の画像と同じ配置になっているか?
② 両シートの日付セルにはちゃんと同じデータが入っているか?
③ Sheet1のC列は「AM」「PMのみ」「PM」の半角アルファベットになっているか?
④ Sheet2のB1・D1(結合セル)もちゃんと半角アルファベットになっているか?
全くなにも表示されないという原因としては
参照セルが別物と判断されている可能性があります。
こちらで考えられる原因としてはこの程度でしょうかね。m(_ _)m
No.2
- 回答日時:
No.1です。
補足を拝見しました。
セル結合してあると何かと厄介です。
特に、今回は一人当たり3行の結合で、「PM」と「PM」のみの区別が必要になってしまいます。
(1)について・・・
はい、全く異なった数式になってしまいます。
(2)について・・・
人数が増えた場合といってもやたらに広い範囲を指定してももっと厄介になりますので
今回は10人まで対応できるようにしてみました。
一案ですが、↓の画像のように作業用の列を設けてみてはどうでしょうか?
今回はB列を挿入し、作業用の列としています。すなわち元のB列以降が右へ1列ずつずれるコトになります。
作業列B2セルに
=IF(A2="",B1,A2)
という数式を入れ10人分のB31セルまでフィル&コピーしておきます。
このときA列にデータがなくても最終の人が表示されますが、表に「1」が入ることはないと思いますので、無視してください。
そしてSheet2のB2セルに
=IFERROR(INDEX(Sheet1!$B$2:$B$31,SMALL(IF((Sheet1!$C$2:$C$31=$B$1)*(OFFSET(Sheet1!$C$2:$C$31,,MATCH($A2,Sheet1!$1:$1,0)-3,,1)=1),ROW($A$1:$A$30)),COLUMN(A1))),"")
前回同様配列数式ですので、Ctrl+Shift+Enterで確定! → 隣りのC2セルまでフィル&コピー!
D2セルに
=IFERROR(INDEX(Sheet1!$B$2:$B$31,SMALL(IF((LEFT(Sheet1!$C$2:$C$31,2)=$D$1)*(OFFSET(Sheet1!$C$2:$C$31,,MATCH($A2,Sheet1!$1:$1,0)-3,,1)=1),ROW($A$1:$A$30)),COLUMN(A1))),"")
としCtrl+Shift+Enter → 隣りのE2セルまでフィル&コピー!
最後にB2~E2セルを範囲指定 → E2セルのフィルハンドルで下へコピー!
これで画像のような感じになります。
※ 作業列が目障りであれば非表示にしておいてください。
※ 余計なお世話かもしれませんが、「AM」・「PM」とも最大二人というコトですが
間違ってSheet1の同一日に3個以上の「1」を入力した場合注意を促す意味で
セルの塗りつぶしの設定をしてみてはどうでしょうか?
Sheet1のD2セル以降「1」が入る可能性があるセルを範囲指定 → 条件付き書式 → 新しいルール → 数式を使用して・・・ → 数式欄に
=IF(AND($C2="AM",D2=1),COUNTIFS($C$2:$C$31,"AM",D$2:D$31,1)>2)
という数式を入れ → 書式 → 塗りつぶしから「赤」(←好みの色で構いません)を選択しOK
同じセル範囲に二つ目の条件付き書式を設定します。
・・・中略・・・ → 数式欄に
=IF(AND(LEFT($C2,2)="PM",D2=1),SUMPRODUCT((LEFT($C$2:$C$31,2)="PM")*(D$2:D$31=1))>2)
という数式を入れ → 書式 → 塗りつぶしから「赤」(←好みの色)を選択しOK
これで1列内に「AM」に3個以上の「1」が入力されれば「AM」の「1」すべてのセルが塗りつぶされ
同じく「PM」または「PMのみ」の行のセルに「1」が3個以上入力されれば
そのセルが塗りつぶされます。
こんな感じではどうでしょうか?m(_ _)m
No.1
- 回答日時:
こんばんは!
一例です。
↓の画像のような配置になっているとします。
尚、同じ日付の「AM」と「PM」は3人以上に「1」が入力されない!という前提です。
(1日あたり「AM」・「PM」とも最大二人まで)
画像ではSheet2のB2セルに
=IFERROR(INDEX(Sheet1!$A$2:$A$11,SMALL(IF((Sheet1!$B$2:$B$10=$B$1)*(OFFSET(Sheet1!$B$2:$B$10,,MATCH($A2,Sheet1!$1:$1,0)-2,,1))=1,ROW($A$1:$A$9)),COLUMN(A1))),"")
配列数式になりますので、Ctrl+Shift+Enterで確定!
この画面からコピー&ペーストする場合は
上記数式をドラッグ&コピー → B2セルを選択 → 数式バー内に貼り付け → そのまま(編集可能なまま)
Ctrl+Shiftキーを押しながらEnterキーで確定!
B2セルのフィルハンドルで隣りのC2セルまでコピー!
次にD2セルに
=IFERROR(INDEX(Sheet1!$A$2:$A$11,SMALL(IF((Sheet1!$B$3:$B$11=$D$1)*(OFFSET(Sheet1!$B$3:$B$11,,MATCH($A2,Sheet1!$1:$1,0)-2,,1))=1,ROW($A$1:$A$9)),COLUMN(A1))),"")
これも配列数式ですので、Ctrl+Shift+Enterで確定!
D2セルのフィルハンドルで隣りのE2セルまでコピー!
最後にB2~E2セルを範囲指定 → E2セルのフィルハンドルで下へコピーすると
画像のような感じになります。
※ 質問文ではSheet1のA列が結合され、人数は5人になっていますので、
5人分だけの数式になります。
※ B2セルの数式とD2セルの数式が微妙に異なります。
IF関数等の範囲が1行ずれているコトに気を付けてください。
こんな感じではどうでしょうか?m(_ _)m
tom04さん、ご回答いただきありがとうございます。
エクセル初心者なので今までも月勤務シフト表作成に至るまでも試行錯誤の連続でした。超難関だったのが今回お尋ねした別シートに名前を出すことでした。詳しい説明、シート添付のおかげで超難関が解決できました。もう感謝感激です。
実は今回初めての質問でしたので質問の一部を簡略化してしまいました(後悔してま~す)
実際は午後だけの勤務日は出勤時間が違うためsheet1では、PMを2列にして「1」の入力場所を変えてます。(AM、PM、PMのみ)の3列にしてます。sheet2の形式に変更はありません。
今回の回答例の数式の一部変更すれば良いでしょか。それとも全く別の数式になりますか?(2人体制に変更はありません)
お手数をおかけしたしますが、お教えいただきましたら幸いです。
追加質問をここに書いて良いのかわからず書きました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
このQ&Aを見た人はこんなQ&Aも見ています
-
映画のエンドロール観る派?観ない派?
映画が終わった後、すぐに席を立って帰る方もちらほら見かけます。皆さんはエンドロールの最後まで観ていきますか?
-
フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
あなたが普段思っている「これまだ誰も言ってなかったけど共感されるだろうな」というあるあるを教えてください
-
映画のエンドロール観る派?観ない派?
映画が終わった後、すぐに席を立って帰る方もちらほら見かけます。皆さんはエンドロールの最後まで観ていきますか?
-
海外旅行から帰ってきたら、まず何を食べる?
帰国して1番食べたくなるもの、食べたくなるだろうなと思うもの、皆さんはありますか?
-
天使と悪魔選手権
悪魔がこんなささやきをしていたら、天使のあなたはなんと言って止めますか?
-
エクセルで勤務表の出勤者を抜き出したい。 日付 名前 5/16 5/17 5/18 5/19 田中
Excel(エクセル)
-
複雑なシフト表から1日ごとの出勤者、シフトを抜き出したいです
Excel(エクセル)
-
月間勤務表から1日~31日までの各日付ごとに出勤している職員を書き出す方法
Excel(エクセル)
-
-
4
エクセルで出勤表から出勤者の名前を出したいのですが。
Excel(エクセル)
-
5
エクセルのセルでシフト表を作っているのですが、それを別のシートに反映させたいのですが、どのようにすれ
Excel(エクセル)
-
6
シフト管理、月間のシフト表を別シートで日ごとの時間別で出勤者の名前を表示したい
Excel(エクセル)
-
7
エクセル シフト勤務表から、シフト別に氏名を抽出。
その他(ソフトウェア)
-
8
エクセルで、勤務表から 日付別に勤務者と勤務形態を抽出して、別シートに抽出したい
Word(ワード)
-
9
月のシフト表から当日のシフトを抽出したいです
Excel(エクセル)
-
10
関数を使用して、エクセルの勤務表から、勤務形態別に担当者を抽出したい
Excel(エクセル)
-
11
作業別のシフト表から職員別のシフト表へ反映させたい
Excel(エクセル)
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・街中で見かけて「グッときた人」の思い出
- ・「一気に最後まで読んだ」本、教えて下さい!
- ・幼稚園時代「何組」でしたか?
- ・激凹みから立ち直る方法
- ・1つだけ過去を変えられるとしたら?
- ・【あるあるbot連動企画】あるあるbotに投稿したけど採用されなかったあるある募集
- ・【あるあるbot連動企画】フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
- ・映画のエンドロール観る派?観ない派?
- ・海外旅行から帰ってきたら、まず何を食べる?
- ・誕生日にもらった意外なもの
- ・天使と悪魔選手権
- ・ちょっと先の未来クイズ第2問
- ・【大喜利】【投稿~9/7】 ロボットの住む世界で流行ってる罰ゲームとは?
- ・推しミネラルウォーターはありますか?
- ・都道府県穴埋めゲーム
- ・この人頭いいなと思ったエピソード
- ・準・究極の選択
- ・ゆるやかでぃべーと タイムマシンを破壊すべきか。
- ・歩いた自慢大会
- ・許せない心理テスト
- ・字面がカッコいい英単語
- ・これ何て呼びますか Part2
- ・人生で一番思い出に残ってる靴
- ・ゆるやかでぃべーと すべての高校生はアルバイトをするべきだ。
- ・初めて自分の家と他人の家が違う、と意識した時
- ・単二電池
- ・チョコミントアイス
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセル1行おきのセルを隣の...
-
SUMIF関数で、「ブランク以外を...
-
エクセルで、指定の値よりも大...
-
自分の左隣のセル
-
文字列から英数字のみを抽出す...
-
条件付き書式の色付きセルのカ...
-
エクセルで特定のセル内にだけ...
-
EXCELでマイナス値の入ったセル...
-
EXCELのcountif関数での大文字...
-
同一セル内の重複文字を削除し...
-
エクセルで、A2のセルにA3...
-
エクセルでエンターを押すと任...
-
AVERAGE関数とINDIRECT関数を使...
-
excelで、空白を除いてデータを...
-
エクセルに入力後、別シートの...
-
エクセルで年月日から月日のみへ
-
セルを結合した時のエクセル集...
-
エクセルでの会社名の集計
-
エクセル関数/任意の桁数の数...
-
EXECL バーコード生成でBarCode...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
SUMIF関数で、「ブランク以外を...
-
文字列から英数字のみを抽出す...
-
エクセル1行おきのセルを隣の...
-
自分の左隣のセル
-
エクセルで、指定の値よりも大...
-
同一セル内の重複文字を削除し...
-
エクセルで、A2のセルにA3...
-
セルを結合した時のエクセル集...
-
エクセルで数字の組み合わせを...
-
エクセルで特定のセル内にだけ...
-
EXCELでマイナス値の入ったセル...
-
条件付き書式の色付きセルのカ...
-
excelで、空白を除いてデータを...
-
EXCELのcountif関数での大文字...
-
エクセルでエンターを押すと任...
-
Excelで大量のセルに一気に関数...
-
エラー「#REF」の箇所を置き換...
-
【Excel】4つとばしで合計する方法
-
エクセルで年月日から月日のみへ
-
EXECL バーコード生成でBarCode...
おすすめ情報
実際は午後だけの勤務日は出勤時間が違うためsheet1では、PMを2列にして「1」の入力場所を変えてます。(AM、PMのみ、PM)の3列にしてます。sheet2の形式に変更はありません。
修正した画像を添付しました。
(1)回答例の数式の一部変更すれば良いでしょうか。それとも全く別の数式になりますか?(2人体制に変更はありません)
2)回答例では5人分だけの数式とありましたが、仮に人数が増えた場合は、数式のどこを手直すればよいのでしょうか?また名前のところのセルは結合しなければ数式はより簡単になりますか?
初めてで、補足質問が多くてすみません。
補足説明にすぐに応じていただいてありがとうございます。
回答No.2にありました
作業列B2セルに
=IF(A2="",B1,A2)を入れたのち(回答NO.3 の数式も入れてみましたが・・・)
Sheet2のB2セルに
=IFERROR(INDEX(Sheet1!$B$2:$B$31,SMALL(IF((Sheet1!$C$2:$C$31=$B$1)*(OFFSET(Sheet1!$C$2:$C$31,,MATCH($A2,Sheet1!$1:$1,0)-3,,1)=1),ROW($A$1:$A$30)),COLUMN(A1))),"")
を配列数式で入れてみたのですが、名前が全く出てきません。
上記数式であってますか?
D2も同様です。
お手数をおかけしますが、ご回答よろしくお願いします。
ご迷惑をおかけしました。確認いたしましたらご指摘④結合したセル内のAM,PMが全角でした。直したところ、出来ました!!
教えて頂いた数式の関数を少しは理解したい(希望ですが・・・)と思ってネットで調べてましたら、IFERROR関数はエクセル2007から新設とありました。
ビスタを使っている人がいるので、2003の場合の数式がお分かりだったらお教えいただけないでしょうか?
今回お教えいただいたシート1、シート2では、いずれも「A1」からスタートしてますが、表題を付けたりして実際は「A1」のスタートとなりません。
数式中にある行、列のことがよく理解できていません。
(1)シート1で行をかえたり、列を変えたりした場合、シート2の数式のどこが違ってくるのでしょうか?
(シート1の行を挿入(表はA2スタート)してもシート2は従来通り、シート1で列挿入するとシート2は変化)
(2)シート2で行を変えたり、列を変えたりしたら、数式のどこを変更したらよいのでしょうか?
(シート1はそのままでシート2で行を挿入したらもう変化)
(3)(1)、(2)の疑問が理解できたら、シート2のAmとPmの間に列を増やして、出勤時間が違うところに印☆(手入力を予定してます)でも入れようかと(1)(2)が解決出来たら可能ですよね。
たびたびの質問でお手数をおかけして本当にすみません。
回答6、たいへんありがとうございま~す。
回答にあった数式例を参照にしたら幾分応用も可能となりました。*_*
応用する際、数式中のMATCH($A2,Sheet1!$1:$1,0)-3,,1)=1の変更に少し苦しみましたが・・・
回答6にあったsheet1(変更したもの)を基にsheet2の変更を自分なりに試みて以下の数式にしましたがどうもうまく出来ません。どこが悪いのでしょうか?
=IFERROR(INDEX(Sheet1!$C$3:$C$32,SMALL(IF((Sheet1!$D$3:$D$32=$C$2)*(OFFSET(Sheet1!$D$3:$D$32,,MATCH($B2,Sheet1!$2:$2,0)-4,,1)=1),ROW($B$1:$B$30)),COLUMN(B2))),"")