Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 掲示板への投稿には会員登録(無料)が必要です。会員登録がまだの方はこちら
  • 掲示板ご利用上のお願い」に反するご記入はご遠慮ください。
  • Q&A掲示板の使い方はこちらをご覧ください
トピックに返信
質問

 
(指定なし : 指定なし)
社員登録マスタから条件に応じた出力方法
投稿日時: 18/10/11 11:21:49
投稿者: kou5355

@入力用シート
 
No 氏名 入社日 退社日
01 井上 07/01 09/30
02 田中 08/01
03 鈴木 09/01
04 木村 10/01
 
----------------------------
A出力用シート
 
9月 入退社一覧
【入社】
No 氏名 入社日 退社日
03 鈴木 09/01
 
【退社】
No 氏名 入社日 退社日
01 井上 07/01 09/30
 
----------------------------
 
お世話になっております。上記のような表がありまして、入力するのは
@の入力用シート です。No 氏名 入社日 退社日 を社員マスタとして
適宜入力していきます。
 
今回欲しいのは A出力用シート です。"*月"(上の例では9月)のセルに
月の値を入力すると、あらかじめ用意された【入社】および【退社】の表に
@を参照して該当月の入社及び退社の該当者を表示するにはどのように
すべきでしょうか?
なお当月の入退社は最大でも10名以下のため、A出力用シート には
予め【入社】【退社】各10行程度を確保し、予め罫線を引いております。
(簡便な出力方法がある場合、上記の出力様式にこだわりはありません)
 
よろしくお願い致します。 

回答
投稿日時: 18/10/11 13:52:46
投稿者: WinArrow
投稿者のウェブサイトに移動

最初に確認します。
 
質問1
@入力用シート
「入社日」「退社日」について
 
掲示の表では、「月日」しか表示されていませんが、
データは、日付形式ですか?
 
質問2
質問1にも関係するのですが
検索キーで「9月」と指定していますが、
「年」を意識しないと、昨年以前も対象になってしまうと思いますが、
「年」はどのように指定するつもりですか?
 
「意見」
オートフィルタという機能があります。
この機能を使うと、非常に簡単に抽出できます。
ご存知ですか?

回答
投稿日時: 18/10/11 14:13:08
投稿者: WinArrow
投稿者のウェブサイトに移動

質問の追加
 
質問3
 
退社した人は、
@シートから削除されるのですか?
削除されるとしたら、何時ですか?
 
この質問の意図は、
過去に遡って検索する可能性があるか?
という疑問からです。

投稿日時: 18/10/11 15:22:40
投稿者: kou5355

ご回答ありがとうございます。
質問にお答え致します。
 
質問1:
データは、日付形式です。
表現を簡便にするために日付のみ書きましたが
実際は年も必要です。
 
 
質問2:
上記の通り年も指定する必要があります。
 
 
ご意見について:
内部報告において、入社と退社をそれぞれ
1ページ内に、分けて表示する必要がありますため
フィルタ機能では求めるイメージで印刷できません。
 
 
質問3:
退社の方は5年間削除されません。
5年間は遡って検索する可能性があります。
 
 
 
以上よろしくお願い致します。

回答
投稿日時: 18/10/11 15:37:24
投稿者: WinArrow
投稿者のウェブサイトに移動

>フィルタ機能では求めるイメージで印刷できません。
  
オートフィルタで表示されたセル範囲を
印刷用シートにコピペできます。

投稿日時: 18/10/11 16:05:17
投稿者: kou5355

ご回答ありがとうございます。
おっしゃるとおり、都度、フィルタを行った結果を図として貼り付けという
方法も考えはしたのですが、フィルタ作業、ペースト作業も含めて手数を
減らしたい(自動化したい)目的があり、数式等で実現できないかと質問に
至りました。

回答
投稿日時: 18/10/11 17:39:06
投稿者: WinArrow
投稿者のウェブサイトに移動

kou5355 さんの引用:
ご回答ありがとうございます。
おっしゃるとおり、都度、フィルタを行った結果を図として貼り付けという
方法も考えはしたのですが、フィルタ作業、ペースト作業も含めて手数を
減らしたい(自動化したい)目的があり、数式等で実現できないかと質問に
至りました。

 
 
図として貼り付けは、無駄です。
単純にコピペで行けます。
 
この操作を自動化したい場合は、マクロかすれば
コピペまで自動化できます。
 
 
 
 

回答
投稿日時: 18/10/11 19:04:45
投稿者: んなっと

ピボットテーブルでできるかもしれません。頑張ってみてください。
 
関数を望んでいる場合。
Excelのバージョンが選択されていないと正確な回答ができません。
以下、適当に作ってみました。Excel2010以降ならきちんと考えて回答します。
 
●Sheet1 7/1は、2018/7/1と入力されていると仮定します。
 
   A   B    C    D
1 No. 氏名 入社日 退社日
2  01 井上   7/1  9/30
3  02 田中   8/1    
4  03 鈴木   9/1    
5  04 木村  10/1  9/30
 
●Sheet2 2018年9月を抽出。
(最初の質問だと単に9月を抽出するように読み取れます。その場合は別の式。)
 
   A   B    C    D    E
1 2018   9            
2 入社               
3  No. 氏名 入社日 退社日    
4  03 鈴木   9/1        4
5               100002
6               100003
........
14 退社               
15  No. 氏名 入社日 退社日    
16  01 井上   7/1  9/30    2
17  04 木村  10/1  9/30    5
18               100003
 
A4
=IF($E4="","",INDEX(Sheet1!A:A,$E4))
右方向・→下方向・↓
C4
=IF($E4="","",TEXT(INDEX(Sheet1!C:C,$E4)&"","m/d"))
右方向・→下方向・↓
E4
=SMALL(INDEX(ROW($2:$500)+10^5*(TEXT(Sheet1!C$2:C$500,"yyyy/m")<>TEXT(A$1&"/"&B$1,"yyyy/m")),),ROW(E1))
下方向・↓
 
A16
=IF($E16="","",INDEX(Sheet1!A:A,$E16)&"")
右方向・→下方向・↓
C16
=IF($E16="","",TEXT(INDEX(Sheet1!C:C,$E16)&"","m/d"))
右方向・→下方向・↓
E16
=SMALL(INDEX(ROW($2:$500)+10^5*(TEXT(Sheet1!D$2:D$500,"yyyy/m")<>TEXT(A$1&"/"&B$1,"yyyy/m")),),ROW(E1))
下方向・↓

回答
投稿日時: 18/10/11 20:49:08
投稿者: んなっと

10^5*
 ↓
10^4*
 
に修正。

回答
投稿日時: 18/10/12 14:48:03
投稿者: WinArrow
投稿者のウェブサイトに移動

 んなっと さん、お見事です。
 
僭越ながら数式を修正したほうがよいと
思われるヶ所があります。

  

C4
=IF($E4="","",TEXT(INDEX(Sheet1!C:C,$E4)&"","m/d")) 
右方向・→下方向・↓ 

修正内容
C4
=IF($E4>10000,"",TEXT(INDEX(Sheet1!C:C,$E4)&"","yyyy/m/d")*1)
 
D4
=IF(OR($E4>100000,INDEX(Sheet1!D:D,$E4)&""=""),"",TEXT(INDEX(Sheet1!D:D,$E4)&"","yyyy/m/d")*1)
 
セルC4,D4の"m/d"は、表示形式で対応したほうがよいと思います。

回答
投稿日時: 18/10/12 16:38:01
投稿者: んなっと

WinArrowさん、違います。
その式だとこうなってしまいますよ。
 
   A   B    C    D    E
3 No. 氏名 入社日  退社日    
4  03 鈴木   9/1 #VALUE!    4
 
 
「面倒だから」見た目だけの抽出でいいと考え、わざと文字列の"9/1"にしています。
厳密にシリアル値にこだわるなら、せめてこういった式を書くようにしてください。
C4
=IF(INDEX(Sheet1!C:C,$E4)>0,INDEX(Sheet1!C:C,$E4),"")
 
または
C4
=INDEX(Sheet1!C:C,$E4)
表示形式 ユーザー定義 
m/d;;;@
 
 
 
kou5355さんへ。
ちなみに、Excel2010以降なら優れた関数が使えるので...
C4
=IF($E4="","",IFERROR((INDEX(Sheet1!C:C,$E4)&"")*1,""))
E4
=IFERROR(AGGREGATE(15,6,ROW($2:$500)/(TEXT(Sheet1!C$2:C$500,"yyyy/m")=TEXT(A$1&"/"&B$1,"yyyy/m")),ROW(E1)),"")

回答
投稿日時: 18/10/12 18:05:05
投稿者: WinArrow
投稿者のウェブサイトに移動

引用:

WinArrowさん、違います。
その式だとこうなってしまいますよ。

 
#VALUEエラーにならないように
>OR($E4>100000,INDEX(Sheet1!D:D,$E4)&""="")
というようにORでチェックしていますが、
100000の0が1つ多いはミステイク

回答
投稿日時: 18/10/12 18:33:04
投稿者: んなっと

なるほど。C4とD4が別の式ということですね。
わざわざ修正するほどの式ではないですね。
 
修正するなら前述の方法。

引用:
C4
=IF(INDEX(Sheet1!C:C,$E4)>0,INDEX(Sheet1!C:C,$E4),"")
  
または
C4
=INDEX(Sheet1!C:C,$E4)
表示形式 ユーザー定義 
m/d;;;@

回答
投稿日時: 18/10/12 18:34:25
投稿者: んなっと

時間の無駄。

投稿日時: 18/10/16 15:06:25
投稿者: kou5355

ありがとうございます。
 
「右方向・→下方向・↓ 」 というのは数式の一部でしょうか??

回答
投稿日時: 18/10/16 15:57:53
投稿者: んなっと

「オートフィル」でGoogle検索してください。

トピックに返信