[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『シフト』（ゆり）
A B C D E F G H 1 1月 1 2 3 4 5 6 7 2 土 日 月 火 水 木 金 3 佐藤 A A A 公 A A 公 4 山本 12 A A A 公 A2 N 5 小林 N N N N 公 公 A 6 山田 A A 14 13 公 A A 7 田中 A2 A2 公 公 A A 14
となっているシフト表をもとに、別シートに
A B C D E F G H
1 A A2 12 13 14 17 N
2 1月1日佐藤 田中 山本 小林
3 山本
4 山田
と返したいのですが、マクロを使わず求める関数をどなたか教えてください。
長ったらしい式ですが、B2セルに =IF(SUMPRODUCT((INDEX(Sheet1!$B$3:$H$7,,DAY($A$2))=B$1)*1)<ROW(A1),"", INDEX(Sheet1!$A$3:$A$7,SMALL(INDEX((INDEX(Sheet1!$B$3:$H$7,,DAY($A$2))<>B$1)*9^9+ROW(Sheet1!$A$3:$A$7)-2,),ROW(A1)))) こんなので？
(HANA)
HANAさんお返事ありがとうございます。 自分はまだまだ未熟者で、先ほどの方法からVLOOKUPでも使って次の段階に行こうとしていましたが 回り道をせず行けるのならもうひとつ教えてください。
A B C D E F G H I
1 1月1日 Ａ 佐藤 山田 Ａ２ 田中 Ｎ 小林
１２ 山本
上記sheet1のA1に日付だけを入れると、sheet2の↓
A B C D E F G H I
1 1月 1 2 3 4 5 6 7 8
2 佐藤 A A N 12 A A A2 N
3 山本 12 12 12 A A A2 N N
4 小林 N N N N N N N N
5 山田 A A 12 A A A 12 A
6 田中 A2 A A A A A A A
からその日その日のシフトを表示させる方法を是非教えてください。
どういう基準で配置されているのか良くわかりません。
もしかして B,E,H列は、事前に入力されているのですか？ それでしたら、何がどこに配置されれば良いか分かりそうですが。 （にしては、13,14,17 の項目が有りませんが。）
(HANA)
おっしゃる通りＢＥＨ列はあらかじめ入力されているものです。 13、14、17も本当は入力（表示）してあります。
A B C D E F G H I
1 1月1日 Ａ 佐藤 山田 Ａ２ 田中 Ｎ 小林
１２ 山本
１３
１４
１７
この様になります。ＢＥＨ列は不動なものです。
加えることがもうひとつ、ｼﾌﾄAがもし３人以上の場合はC2、D2、C3、D3へ表示され ｼﾌﾄA2と12〜17についてはG列に表示、ｼﾌﾄNはI2、I3、J1、J2に表示したいと思います。
おっと、 ＞加えることがもうひとつ・・・ と衝突しちゃいましたが、タブン大丈夫と思いますので そのままのせておきます。
わかりました。 ドラッグだけで良い式を作ると、無駄に長くなる気がするので。。。 1列目と2列目で別々の式にしました。
なお A,E,H列は、項目が上と同じでも 同じものを入力し 文字色を背景色と同じ色にして見えなく しておいて下さい。
C1 =IF(SUMPRODUCT((INDEX(Sheet1!$B$3:$H$7,,DAY($A$1))=B1)*1)<COUNTIF(B$1:B1,B1)*2-1,"", INDEX(Sheet1!$A$3:$A$7,SMALL(INDEX((INDEX(Sheet1!$B$3:$H$7,,DAY($A$1))<>B1)*9^9+ROW(Sheet1!$A$3:$A$7)-2,),COUNTIF(B$1:B1,B1)*2-1)))
D1 =IF(SUMPRODUCT((INDEX(Sheet1!$B$3:$H$7,,DAY($A$1))=B1)*1)<COUNTIF(B$1:B1,B1)*2,"", INDEX(Sheet1!$A$3:$A$7,SMALL(INDEX((INDEX(Sheet1!$B$3:$H$7,,DAY($A$1))<>B1)*9^9+ROW(Sheet1!$A$3:$A$7)-2,),COUNTIF(B$1:B1,B1)*2)))
C1,D1の数式の違いは、COUNTIF(・・・)*2 の後に -1するかしないか だけです。
C1,D1セルをコピーして、他のセルに貼り付け。
[A] [B] [C] [D] [E] [F] [G] [H] [I] [J] [1] 1月1日 A 佐藤 山田 A2 田中 N 小林 [2] A 12 山本 N [3] A 13 N [4] A 14 N [5] A 17 N
表は、J列まで有るものとしています。 I列までしかない（名前を表示する列が一列しかない）場合は 数式の入ったC1セルをコピーして貼り付けた後、 *2-1 を2ヶ所取り除いて 行方向へコピーして下さい。 （↑この部分は 不要だったみたいですね。：追記）
・・・で、いけそうですか。 やってみて下さい。
(HANA)
もうひとつだけ教えてください。 SHEET2の人数が増えた場合
C1 =IF(SUMPRODUCT((INDEX(Sheet1!$B$3:$H$7,,DAY($A$1))=B1)*1)<COUNTIF(B$1:B1,B1)*2-1,"", INDEX(Sheet1!$A$3:$A$7,SMALL(INDEX((INDEX(Sheet1!$B$3:$H$7,,DAY($A$1))<>B1)*9^9+ROW(Sheet1!$A$3:$A$7)-2,),COUNTIF(B$1:B1,B1)*2-1)))
D1 =IF(SUMPRODUCT((INDEX(Sheet1!$B$3:$H$7,,DAY($A$1))=B1)*1)<COUNTIF(B$1:B1,B1)*2,"", INDEX(Sheet1!$A$3:$A$7,SMALL(INDEX((INDEX(Sheet1!$B$3:$H$7,,DAY($A$1))<>B1)*9^9+ROW(Sheet1!$A$3:$A$7)-2,),COUNTIF(B$1:B1,B1)*2)))
のどの部分を変えればいいのでしょうか。お願いします。
Sheet2が、一覧表が有る方のシートですよね？
数式は、Sheet1に一覧表が有ることに成っていますが
C1セルに入れた式だと
=IF(SUMPRODUCT((INDEX(Sheet1!$B$3:$H$7,,DAY($A$1))=B1)*1)<COUNTIF(B$1:B1,B1)*2-1,"",
~~~~~~~~~~~~~~~~
INDEX(Sheet1!$A$3:$A$7,SMALL(INDEX((INDEX(Sheet1!$B$3:$H$7,,DAY($A$1))<>B1)*9^9
~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~
+ROW(Sheet1!$A$3:$A$7)-2,),COUNTIF(B$1:B1,B1)*2-1)))
~~~~~~~~~~~~~~~~
この辺りです。（Sheet1!○:□ と成っている4ヶ所）
増えるたびに数式を変更するのは面倒なので、 3:7の間のどこかに行の挿入をして、増やして貰うのが良いと思います。
例えば、 B1セルに =SUM($A$3:$A$7) と言う式を入れておいて 5行目を挿入した場合、B1セルの数式は =SUM($A$3:$A$8) と自動で拡張されます。 8行目を増やしたい場合は、7行目をコピーして 行の挿入貼り付け後 下側のデータ(8行目のデータ)を消して貰う（上から新しい情報を入力して貰う） と良いかもしれません。
範囲は事前に多めにとっておいてもらっても、大丈夫と思います。 （多すぎると重くなるかもしれないので適度に。。。）
一覧表の先頭行が 3行目以外から始まる場合は、
+ROW(Sheet1!$A$3:$A$7)-2,),COUNTIF(B$1:B1,B1)*2-1)))
~~
ここの調整部分の変更が必要になります。
現在は、ROW関数の中の範囲の先頭が「$A$3」になっています。 ですから、このセルは 3（行目）です。 これから「-2」をして、1 を得ています。
例えば、範囲の先頭を「$A$5」にする場合は 「-4」として下さい。
修正出来そうですか？
因みに、この式は A1セルの日だけを見ています。 （一覧表のA1セルの月との確認はしていません。） A1セルに「1/1」と書いてあっても、一覧表が2月だった場合 何のメッセージも無く、2月1日の情報が表示されますので 注意して下さい。 また、一覧表の日付を検索しているわけではないので 範囲の先頭は1日から始まる様にして下さい。
(HANA)
最後にもうひとつだけお教えください。
ｼﾌﾄＡの人数が増えた場合
[A] [B] [C] [D] [E] [F] [G] [H] [I] [J] [1] 1月1日 A 佐藤 山田 A2 田中 N 小林 [2] A 12 山本 N [3] A 13 N [4] A 14 N [5] A 17 N
Ｄ、Ｅ列間に挿入が必要（５行より下は事情により使用不可なため）なのですが
C1 =IF(SUMPRODUCT((INDEX(Sheet1!$B$3:$H$7,,DAY($A$1))=B1)*1)<COUNTIF(B$1:B1,B1)*2-1,"", INDEX(Sheet1!$A$3:$A$7,SMALL(INDEX((INDEX(Sheet1!$B$3:$H$7,,DAY($A$1))<>B1)*9^9+ROW(Sheet1!$A$3:$A$7)-2,),COUNTIF(B$1:B1,B1)*2-1)))
D1 =IF(SUMPRODUCT((INDEX(Sheet1!$B$3:$H$7,,DAY($A$1))=B1)*1)<COUNTIF(B$1:B1,B1)*2,"", INDEX(Sheet1!$A$3:$A$7,SMALL(INDEX((INDEX(Sheet1!$B$3:$H$7,,DAY($A$1))<>B1)*9^9+ROW(Sheet1!$A$3:$A$7)-2,),COUNTIF(B$1:B1,B1)*2)))
HANAさんが以前「違いはCOUNTIF（）の後の-1の有無」とおっしゃっていましたが E1の式を教えてください。
何度もすみませんがお願いいたします。
COUNTIF()の後に「*2」がしてありますが これは、一つの項目が 2列 だったからです。
3列に成る場合はまず、「*3」をします。 すると、COUNTIF(B$1:B1,B1)が1だった場合、「3」に成ります。 2だった場合は「6」に成ります。
この数字で 何個目のセルか を表そうとしています。 1個目のセルは C1 2個目のセルは D1 3個目のセルは E1 4個目のセルは C2 ・・・・
なので、C1セル（2個左のセル）に入れる式は それからさらに「-2」を D1セル（1個左のセル）に入れる式は、「-1」をします。
例 C1 =IF(SUMPRODUCT((INDEX(Sheet1!$B$3:$H$7,,DAY($A$1))=B1)*1)<COUNTIF(B$1:B1,B1)*3-2,"", INDEX(Sheet1!$A$3:$A$7,SMALL(INDEX((INDEX(Sheet1!$B$3:$H$7,,DAY($A$1))<>B1)*9^9 +ROW(Sheet1!$A$3:$A$7)-2,),COUNTIF(B$1:B1,B1)*3-2)))
D1は「*3-2」→「*3-1」（2ヶ所変更） E1は「*3-2」→「*3」 （2ヶ所変更）
な感じになります。
(HANA)
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.