[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『別シート転記のデータ抽出について』（Excelシロウトの年寄り）
シート1に以下のような1ヶ月の出席表のデータが、たて列に氏名数百行、横列に 31日までのセルがあります。
1日 2日 3日 4日 5日 6日 ・・・・・ 氏名① ○ × ○ × ○ ○ ・・・・・ 氏名② × ○ × ○ × × ・・・・・ 氏名③ × × × ○ × ○ ・・・・・
○は出席 ×は欠席です。このシートから別の1名毎の出席表シートに 転記したいのですが、問題は、1名毎のシートには1ヶ月の出席上限があり 20行しか表記できません。氏名②の場合、最初の行に2日、氏名③の場合 最初の行に4日の日付を入れなければいけないと言うことになります。 要は出席した日だけの日付を下記の様式で抽出したいのです。
氏名①の○○月の出席個別シート
第1回目の出席日 1日 第2回目の出席日 3日 第3回目の出席日 5日 第4回目の出席日 6日 ・ ・ ・
氏名②の○○月の出席個別シート
第1回目の出席日 2日 第2回目の出席日 4日 ・ ・ ・
と、このように抽出をしたいのです。マクロを使わずに何か方法は 無いものでしょうか？ どなたかお助け下さい。マクロのことが全く分からない年寄りの初心者です。 どうぞよろしくお願い致します。マクロを勉強しなくては無理な場合、 その可能性も含めご教授願えれば幸いでございます。
追加記載 出席の記号には○と◎と●の3種類の表記を使用していますが、全て出席として 出席個別シートには自動転記したいのです。 出席表のデータシートから出席個別シートに自動抽出して印刷したいのです。
>シート1に以下のような1ヶ月の出席表のデータが、たて列に氏名数百行 >このシートから別の1名毎の出席表シートに との事ですので、、、 シート数が「数百シート」という事になりますよね？ シートの量産は、あまりお勧めできません。。。 更に、マクロではないもので考えていらっしゃるようですが、 計算式でそのような大量のシートのものを管理しようとすると、 ファイルがとてつもなく重くなると思いますので、こちらもお勧めできません。。。 シート構成の見直しや、運用方法の見直しをしたほうがいい気がします。 みやほりんさんの失敗談を参考にしてくださいｗ http://miyahorinn.fc2web.com/schooltxt/Ex060120.html （みやさん、勝手にリンク張っちゃいました。ごめんなさいm(_ _)m） （キリキ）(〃⌒o⌒)b
ご回答ありがとうございました。
説明不足をお詫びいたします。 出席表シートは5つのグループに分かれており、5シートのみです。 1つのシートに約40名分の単票をページ分けして作成してあり、 そのページ分けした各個人の出席表に出席日を自動転記し、 月末にその個人出席表をプリントアウトしたい訳です。 Excelファイルは1ヶ月毎に保存します。 どうぞ宜しくお願い致します。
えっと、ご本人さんと衝突してしまったのですが せっかく書いたのでそのまま載せます。 ・・・使えますかね？ 以下、衝突前の文章。
キリキさんも仰ってますが、個人毎にシートを分けて・・・ と言う作業はやらないことをお薦めします。
ただ、 >出席表のデータシートから出席個別シートに自動抽出して印刷したいのです。 この一言が、もしかして 「印刷するために、データを選び出したい。 印刷してしまえばそのシートは不要」 と言うことであれば、以下の様にするのはどうでしょうか。
Sheet1のA列を挿入し、通し番号を付けます。 Sheet1 [A] [B] [C] [D] [E] [F] ・・・[AG] [1] NO. 名前 1日 2日 3日 4日 [2] 1 氏名① ○ × ○ × [3] 2 氏名② × ○ × ○ [4] 3 氏名③ × × × ○
Sheet2は、A1,A2,B1に入力 Sheet2 [A] [B] [C] [1] NO. 名前 [2] 1 氏名① [3] 第1回目の出席日 1日 [4] 第2回目の出席日 3日 [5] 第3回目の出席日 5日 [6] 第4回目の出席日 6日 ・ ・ [22]
A1,B1はA2,B2の項目なので未記入でも良いです。 B2=VLOOKUP(A2,Sheet1!$A$2:$B$4,2,0) （Sheet1の行数が増えた場合は、この式の「$B$4」を変更して下さい。） B3=IF(C3="","","第"&ROW(A1)&"回目の出席日") （B22までコピー。） C3=IF((COUNTA(OFFSET(Sheet1!$C$1:$AG$1,$A$2,0))-COUNTIF(OFFSET(Sheet1!$C$1:$AG$1,$A$2,0),"×"))<ROW(A1),"", SMALL(IF(OFFSET(Sheet1!$C$1:$AG$1,$A$2,0)="×","",COLUMN($A$1:$AE$1)),ROW(B1))&"日") （【Ctrl+Shift+Enter】で確定し、C22までコピー。）
A2に入れたNO.で、個人のデータが表示されると思います。
(HANA)
ご回答ありがとうございました。 度重なる説明不足で申し訳ございませんでした。 おっしゃるとおり印刷さえしてしまえばそのシートは不要な訳です。 早速試させていただきます。ありがとうございました。深謝。
良心が痛むので、やっぱり先に書いておきますが 「Sheet2,A2に入力された値をSheet1,A列から探し出し 同じ行で"マル"がついている列の１行目の日付を探しだし 左から順にSheet2,C3から下に表示させる。」 と言う作業を行っているわけではないので、 悪しからずご了承下さい。
<追記> >おっしゃるとおり印刷さえしてしまえばそのシートは不要な訳です。 この部分が、気になったので追記。
私は上で「印刷してしまえばそのシートは不要」と書きましたが、 「一人ずつ抽出して、その都度印刷すれば良く 抽出された形でデータを残しておくことが不要 （一度に数名のデータを抽出する必要はない）」 と言う意味で書いたものでした・・・。（大丈夫ですかね？） C列の数式はあまりたくさんの場所にコピーして使うと 計算に時間がかかったりしますのでお気をつけ下さい。
(HANA)
HANA様色々とお気遣い有難う御座います。 おっしゃるとおり「一人ずつ抽出して、その都度印刷すれば良く 抽出された形でデータを残しておくことが不要で、一度に数名のデータを 抽出する必要はない」と言うことに相違御座いません。
浅はかな知識の中で色々とやってみたものの、うまくいきません。 セルの位置はシート1もシート2も全く同じですので、教えていただいたとおりに 入力してみたのですが、B2セルに指定したVLOOKUP関数はうまくいったのですが、 B3及びC3にご教授頂いたとおりに指定して見ましたがエラーになってしまいます。 現在、ご教授いただいた関数を一所懸命理解しようと努力中で御座います。 ちなみにExcel2003を使用しております。 この年になるとなかなか理解力が遅く苦労いたします。 申し訳御座いません。
エラーって、どんなエラーですかね？
取り敢えず、新しいブックをつくって
上のSheet1の表をコピー。
新しいブックのSheet1A1セルを選択して右クリック
→形式を選択して貼り付け→テキスト
として、貼り付けてみて下さい。
各セルにデータが入ると思うので、A列と1行目を削除です。
Sheet2のA2に「1（半角数字）」を入力してC3に数式をコピーして
（=を含めて２行続けてコピーして、数式バーに貼り付けてくださいね）
【Ctrl+Shift+Enter】で確定 です。
数式が{}で囲われると思うので、下にコピーして下さい。
C3に「1日」C4に「3日」後は空欄が表示されると思います。
新しいブック、サンプルと同じデータで試しても やっぱりうまくいきませんか？
(HANA)
HANA様 大変お世話をおかけいたしました。 おかげさまをもちましてデータの自動転記に成功いたしました。 当初私が入力したデータには入力規則やその他の関数を 指定していたなどの問題があったものと思われます。 データから必要なものだけを新しいシートに転記させ、 そのシートにご教授戴いた数式を入力することにより 印刷用シートへの自動転記が出来るようになりました。 本当に有難う御座いました。 心より御礼申し上げます。 今後は少しずつマクロなるものも勉強していきたいと思います。 (Excelシロウトの年寄り）でした。
できましたか。よかったです。 しかし、毎月新しいシートに転記するのは手間ですよね。 現在のシートでそのまま使えるようにチャレンジしてみますので もしよろしければ詳細を教えて下さい。
(HANA)
HANA様 本当にご親切に教えていただき有難う御座います。
お言葉に甘えて詳細のご報告と再度のお尋ねを申し上げます。
実は原簿のシートには出席の表記種類が○と◎と●があり、欠席は×と表記 されています。集計の抽出上３種類の○◎●には何等意味がないので全て ○に置換して集計しようと考えたものですから、全て○とお伝えしておりました。 更に原簿のシートには、出席した場合、その右隣のセルは出席した時間が 表示されています。そのまま横に31日分続きます。 時間は15分単位でドロップダウンにて選択するようになっております。 出欠の記号もドロップダウンより選択します。 当初この時間は必要ないと勝手に思い込んでいたものですからこの時間帯の列を 削除した新たなシートを作成し、そのシートから出席のみを抽出しようと考えた 訳で御座います。 しかしながら、よくよく考えてみると印刷するシートにこの時間帯も表示させなければ ならないことに気付き、頭を悩ませておりました。 出来る事なら、印刷をするシートにこの時間帯も表示させたいのですが、 これは可能でしょうか？ もちろん印刷は１名分ずつ行い、印刷した後はそのシートを保存する必要は ありません。 データの原簿シートの構成を記載いたします。
sheet1 [A] [B] [C] [D] [E] [F] [G] [H] ・・・[CQ] [1] № 氏名 1日 開始 終了 2日 開始 終了 [2] 1 氏名① ○ 9:15 16:00 ◎ 9:45 15:15 [3] 2 氏名② × ○ 9:30 16:30 [4] 3 氏名③ × ● 9:45 16:45 [5] 4 氏名④ × ◎ 9:45 16:15
印刷をしたい項目のシートを記載いたします。 [C1]～[E1]のセルには、それぞれ別のシートからデータが転記されます。 [A1]にはsheet1より通し№を[B1]にはsheet1より氏名を[A2]には出席した日付けを、 [B2]には[A2]の曜日を[C2]には[A2]の開始時間を[D2]には[A2]の終了時間を、 [E2]には合計時間を入力したいのです。 以前の説明と同じく、20日分しか記載行がありませんので[21]行で終わりです。 厄介なことは、この印刷様式が変更できないと言うことなのです。 この印刷様式に当てはめてデータを抽出しなければならないのです。 時間の集計は出来ます。 通常の年月日から曜日を返す術は理解しておりますが、この場合曜日を返すことは 可能でしょうか？(表記に年月が記載されていないため) 別シートに一旦抽出した後に印刷用シートに転記すべきでしょうか？ 又、開始・終了時間の抽出は可能でしょうか？
sheet2 [A] [B] [C] [D] [E] [1] № 氏名 [2] 日 曜日 開始 終了 合計時間 [3] 日 曜日 開始 終了 合計時間 [4] 日 曜日 開始 終了 合計時間 [5] 日 曜日 開始 終了 合計時間 [6] 日 曜日 開始 終了 合計時間 [7] 日 曜日 開始 終了 合計時間 以上で御座います。 どうぞ宜しくご教授の程お願い申し上げます。
原簿シートの構成は了解しました。 問題は「開始・終了時間の列がある」という部分です。
>厄介なことは、この印刷様式が変更できないと言うことなのです。 この理由なのですが、「用紙のフォーマットが決まっている」からですか？ 「他の所には、他のものが入力されている」からですか？ 前者であれば、作業セルを使って入力するが印刷はしないようにする という戦法が使えそうな気がします。 （でも、印刷物の中に、○○年○月の出席表 とか言うタイトルがあっても 良いような気がしますが・・・・。）
式に関しては、時間も引っ張れるものを考えますが 今回作った数式を手直しするか、新しく作り直すか・・・ その点から悩み始めますので、気長に待ってやって下さい。 ギブアップの時は、ちゃんとその旨書きますからね。
(HANA)
ご迷惑をおかけいたしております。 「用紙のフォーマットが決まっているから」でございます。 一度20行の日付を31行に変更し、出席日のみの表記では出来ないものか？ と相談いたしましたが、フォーマットの変更は不可との回答があり、 困り果てていた次第でした。 またしても説明不足で申し訳ございません、[E1]セルが、何年何月分の出席状況と 表記されるセルでございます。 結果として、印刷フォームには上記sheet2の情報が全て必要なのですが、 sheet1のデータ原簿の記入様式は変更することが可能です。 sheet1の情報からsheet2の印刷様式にプリントアウトできれば良い訳です。 sheet1の様式はどのように変更しても構いません。 例えば「開始時間」「終了時間」の入力を別シートに作成するとか・・・・ しかしながらその場合、印刷物にデータを抽出する場合、どのように作成すれば ベストなのか？また、原簿のシートの時間の項目を別の場所に入力した場合、 日付との関連付けを判りやすくするにはどうすればベストか、試行錯誤しております。 どうぞ宜しくお願い申し上げます。 (Excelシロウトの年寄り)でした。
「sheet1のデータ原簿の記入様式は変更することが可能です。」 と言うことなので、好きにさせてもらいました。 私なら、以下のように作ると思います。 利用できるところはご利用下さい。 「これはちょっと・・・。使いたくないけど使わない方法が知りたい」 という事なら、またそのように仰って下さい。
sheet1を「データ原簿」という名前に sheet2を「印刷」という名前にしました。
まず、「データ原簿」の方ですが、１行目が作業行です。
[A] [B] [C] [D] [E] [F] [G] [H] ・・・ [CQ]
[1] 2006年 12月 1 4
[2] № 氏名 1日 開始 終了 2日 開始 終了 ・・・ 終了
[3] 1 氏名① ○ 9:15 16:00 ◎ 9:45 15:15
[4] 2 氏名② × ○ 9:30 16:30
[5] 3 氏名③ × ● 9:45 16:45
[6] 4 氏名④ × ◎ 9:45 16:15
A1に「2006」年を半角数字で入力。表示形式を[0"年"]と設定。
B1に「12」 月を半角数字で入力。表示形式を[0"月"]と設定。
C1に「=IF((OFFSET(C2,印刷!$A$1,0)<>"×")*(OFFSET(C2,印刷!$A$1,0)<>"")*(MOD(COLUMN(A1)+2,3)=0),COLUMN(A1),"")」
と入力し、CQ1までコピー。
C2に「=IF(MONTH(DATE($A$1,$B$1,0)+INT(COLUMN(C1)/3))=$B$1,DATE($A$1,$B$1,0)+INT(COLUMN(C1)/3),"")」
表示形式を[d"日"]と設定。
D2に「=IF(C2="","","開始")」
E2に「=IF(C2="","","終了")」
C2,D2,E2の３セルを選択して、CQ2までコピー。
A3に「=IF(B3="","",ROW(A1))」として、必要行(+α)コピー。
これで、「データ原簿」の準備は終了です。 C1～CQ1は完全な作業セルなので、文字の色を白にしておくと良いかもしれません。
次に「印刷」のシートの関数です。
sheet2 [A] [B] [C] [D] [E]
[1] 1 氏名① 2006年12月分の出席状況
[2] 1日 金 9:15 16:00 6:45
[3] 2日 土 9:45 15:15 5:30
[4]
[5]
[6]
[7]
：
：
[21]
B1に「=VLOOKUP(A1,データ原簿!A3:B6,2)」
E1に「=データ原簿!A1&"年"&データ原簿!B1&"月分の出席状況"」
A2に「=IF(COUNT(データ原簿!$C$1:$CQ$1)<ROW(A1),"",INDEX(データ原簿!$C$2:$CQ$2,SMALL(データ原簿!$C$1:$CQ$1,ROW(A1))))」
表示形式を[d"日"]と設定。
B2に「=A2」
表示形式を[aaa]と設定。
C2に「=IF(A2="","",INDEX(OFFSET(データ原簿!$D$2:$CR$2,$A$1,0),SMALL(データ原簿!$C$1:$CQ$1,ROW(A1))))」
D2に「=IF(A2="","",INDEX(OFFSET(データ原簿!$E$2:$CS$2,$A$1,0),SMALL(データ原簿!$C$1:$CQ$1,ROW(A1))))」
E2に「=IF(A2="","",D2-C2)」
C2,D2,E2は表示形式を[h:mm]と設定。
（E列の「合計」って、「出席時間」で良いのですかね？
違うなら、変更して下さい。）
A2～E2を選んで21行目までコピー。
ってな感じです。 どうせ印刷の時に年月を入れる必要があるのですから、データ原簿の日付も 連動させておいた方が、晦日の心配をしなくてもいいと思うのですが。
(HANA)
HANA様、有難うございました。
教えていただいたとおり新たに作成して、データを入力してテスト致しました所 全てうまく行きました。本当に有難うございました。 この方法で使用させていただきたく思います。 ただ、私の伝え方が悪く、実際の印刷フォームは[2]の行がタイトル行だったため 「=IF(COUNT(データ原簿!$C$1:$CQ$1)<ROW(A1),"",INDEX(データ原簿!$C$2:$CQ$2,SMALL(データ原簿!$C$1:$CQ$1,ROW(A1))))」 と言う計算式を[A3]に入力し、B2～E2の計算式も1行ずつずらして B3～E3に入力しました。 ところが、どういうわけか出席の最終日の時間表示のみが#NUM!となってしまいます。 昨夜ご回答いただいてから、自分なりに試行錯誤しながら色々試してみましたが どうしてもうまく行きません。 データを他からコピーして貼り付け、ナンバーを入力するときちんと表示される ときもあります。データ原簿シートの「月」の入力を変更した場合に、最後の 出席時間がエラーになるようですが、私が行をずらしたことに問題があるとしか 考えられません。 ちなみに[A3]には「=IF(COUNT(データ原簿!$C$1:$CQ$1)<ROW(A1),"",INDEX(データ原簿!$C$2:$CQ$2,SMALL(データ原簿!$C$1:$CQ$1,ROW(A1))))」 と入力しており、[B3]には「=A3」と、[C3]には 「=IF(A3="","",INDEX(OFFSET(データ原簿!$D$2:$CR$2,$A$1,0),SMALL(データ原簿!$C$1:$CQ$1,ROW(A2))))」 [D3]には「=IF(A3="","",INDEX(OFFSET(データ原簿!$D$2:$CR$2,$A$1,0),SMALL(データ原簿!$C$1:$CQ$1,ROW(A2))))」と入力、[E3]には「=IF(A3="","",D3-C3)」と入力 しました。 データ原簿の出欠項目の○●◎×と、時間項目はドロップダウンにしました。 このことも何か関係あるのでしょうか？データのコピーの仕方に問題が あるのでしょうか。 もし私が入力した数式が間違っている場合は、印刷シートの[3]の行が最初の出席日の 表示列となる数式を再度ご教授願えないでしょうか？ 本当にご迷惑をおかけして申し訳ございません。どうぞ宜しくお願い申し上げます。
外からなのでしっかりみれてませんが C3とD3に同じの数式が入ってませんか？ D3にはD2用の式を入れてみて下さい。
(HANA)
お忙しいところ見ていただいて有難うございます。 すみません、私の記載ミスでした。D3には、 =IF(A3="","",INDEX(OFFSET(データ原簿!$E$2:$CS$2,$A$1,0),SMALL(データ原簿!$C$1:$CQ$1,ROW(A2)))) と、D2用の式を入れています。
ROW関数の参照セルをA１にしてみて下さい。
(HANA)
HANA様、問題は見事に解決いたしました。 本当に有難うございました。お礼の申し上げようも御座いません。
最後に後学のために、もし宜しければデータ原簿シートの開始・終了の後にデータを もう1項目増やした場合の数式をご教授願えませんでしょうか。
sheet1 [A] [B] [C] [D] [E] [F] [G] ・・・[DV] [1] 作業行 [2] № 氏名 1日 開始 終了 助成 2日 ・・・[DV] [3] 1 氏名① ○ 9:15 16:00 1 × ・・・[DV] [4] 2 氏名② × 0 × ・・・[DV] [5] 3 氏名③ × 0 × ・・・[DV] [6] 4 氏名④ ○ 9:15 16:00 1 × ・・・[DV]
と言う具合なのですが、[F]列の「助成」とは数字の1か0の数字で表記されます。 現在はこの項目は不要なのですが、今後この項目の追加を求められそうで、 ご教授戴いた数式表を参考にして色々とやってみましたが、やはり今の私の レベルでは到底無理な話でした。 大変我侭ばかりで申し訳ございませんが、後学のためにこの項目を追加した 場合、(印刷項目へも転記する場合)の計算式をご教授願えれば幸いです。 印刷シートにはF列に印刷する事になろうかと思います。
それはよかったです。 >もう1項目増やした場合の数式を・・・ ということですが １．現在はこの項目は不要 → 緊急性は無い ２．色々とやってみましたが → ご自身で式をつつく意志がある ３．データをもう1項目増やした場合 → すでに提示した式のマイナーチェンジで作れる 以上の３点から、「数式」を載せるのではなく 「数式の変更箇所」と「式の内容を考える足がかり（になるか？）」 を載せようと思います。 チャレンジしてみて下さい。
まず、「数式の変更箇所」を載せます。
(1)現在３列が１日分 → 今後４列が１日分
データ原簿 C1とC2の式の中で使われている「3」という数字はこの数字です。
(2)現在開始時間がD列から、終了時間がE列から始まっている
→ 今後さらに、助成がF列から始まる
印刷 C2とD2の式を見比べて下さい。
データ原簿の式は、(1)の２カ所を変更して、D2,E2に対応するF2の式を作る。
印刷 の式は、(2)を参考にF2の式を作る。
以上、４つの式を作成・変更（単純に列が増える分は各参照場所変更）で
目的のものは出来ると思います。
式の内容が知りたいと思うなら、まず一番肝要なのは データ原簿のC2に入力し右にコピーした式ですので、ここから攻めて下さい。 関数がいくつか使われていますので、まずは一つ一つの関数が何を求めるものなのか。 たとえば、印刷!$A$1=1だった場合、実際OFFSET(C2,印刷!$A$1,0)はどこなのか？ 式は出来ているのですから、各セルに表示されるものから推測して 個々は何をやっているのか考えてみて下さい。
もしも、どちらも（或いはどちらか）思ったようにいかなければ 「このように考えるがどうか？」 「このようにしたがどうか？」 という書き込みをお願いします。 （気になるので、完成した暁にはその旨教えて下さい。）
(HANA)
HANA様、本当に有難う御座いました。 意味が全く判らなかった私にもようやく光が見えてきた気が致します。 HANA様のおかげで、今回の件を通して今はこの数式を自分なりに理解することが 楽しくって仕方ありません。 時間はかかると思いますが、必ず自分でチャレンジいたします。 壁に突き当たり立ち往生したときは、またお助けくださいませ。 また、完成した折は、必ず書き込みいたします。 この度は本当に有難う御座いました。 今後ともどうぞ宜しくお願いいたします。 又、この書き込みボードを長々と使用させて頂いた事にも御礼申し上げます。 寒い折、お体をご自愛くださいませ。深謝。 (Excelシロウトの年寄り)でした。
(2)に関してもう一カ所参考になるところがあったのを書き忘れていました。 そこだけ追記するのも芸がないので、これらの式の目の付け所を書いておきます。
まず、IF関数に関してですが・・・・ たとえば、B1に「=IF(A1="あ","","い")」C1に「=IF(B1="","","う")」 という式があった場合、C1に「う」と表示されるのは B1=""でない時→A1="あ"でない時 ですから、C1は「=IF(A1="あ","","う")」 と書いても同じ結果を出すことが出来ます。 でも、各々の式に同じ条件をそれぞれ書いていたら、各々の式に関して確認が必要になってしまいます。 今回のように条件部分の式が長かったりするとどんな条件を確認するだけで一苦労です。 そこで、同じ条件ならどこか一カ所だけ計算して後は結果だけを参照してしまおう という安易な考え方で、C1の条件が成り立っています。 ここで、印刷シートのA2,C2,D2に入れた式を見てみると A2=IF(条件,"",INDEX関数) C2=IF(A2="","",INDEX関数) D2=IF(A2="","",INDEX関数) となっているので、C2,D2の条件は、A2に入っている長い条件が入っている事と等しくなります。
この３つの式の重要な部分は、INDEX関数の内容ですので、今度はそれを書き出します。
A2「INDEX( データ原簿!$C$2:$CQ$2 ,SMALL(データ原簿!$C$1:$CQ$1,ROW(A1))))
C2「INDEX(OFFSET(データ原簿!$D$2:$CR$2,$A$1,0),SMALL(データ原簿!$C$1:$CQ$1,ROW(A1)))」
D2「INDEX(OFFSET(データ原簿!$E$2:$CS$2,$A$1,0),SMALL(データ原簿!$C$1:$CQ$1,ROW(A1)))」
ほとんど同じ形をしていると思いませんか？
（私がものぐさだと言う推論が妥当なのですが・・・・）
ちなみに、日付はデータ原簿のC列から
開始時間はデータ原簿のD列から
終了時間はデータ原簿のE列から 始まっています。
「実は２行目に項目があって、データは３行目から表示させようとした時に どうしてもうまくいかなかった式」の３つの式を同じように書き出して比べると 上のように必要なところがそろってないことが分かると思いますよ。 （それが原因で、日付だけはちゃんとでるのに他の項目は一つずれた数字がでてました。）
(HANA)
HANA様 大変参考になるご教授、誠に有難う御座いました。 おかげさまをもちまして、どうにか式を完成させることが出来ました。 詳細なアドバイスのおかげで内容も理解することが出来ました。 ここ数日のやり取りで、私が今まで勉強して来た、数か月分の知識を 一度に得たかのごとく自分なりに進歩できました。本当に有難う御座いました。 以下に作成した計算式を記します。
データ原簿シート sheet1 [A] [B] [C] [D] [E] [F] [G] ・・・[DV] [1] 作業行 [2] № 氏名 1日 開始 終了 助成 2日 ・・・[DV] [3] 1 氏名① ○ 9:15 16:00 1 × ・・・[DV] [4] 2 氏名② × 0 × ・・・[DV] [5] 3 氏名③ × 0 × ・・・[DV] [6] 4 氏名④ ○ 9:15 16:00 1 × ・・・[DV]
[C1]の計算式「=IF((OFFSET(C2,印刷!$A$1,0)<>"×")*(OFFSET(C2,印刷!$A$1,0)<>"")*(MOD(COLUMN(A1)+3,4)=0),COLUMN(A1),"")」 [C2]の計算式「=IF(MONTH(DATE($A$1,$B$1,1)+INT(COLUMN(C1)/4))=$B$1,DATE($A$1,$B$1,1)+INT(COLUMN(C1)/4),"")」 [D2]の計算式「=IF(C2="","","開始")」 [E2]の計算式「=IF(C2="","","終了")」 [F2]の計算式「=IF(C2="","","助成")」
印刷シート sheet1 [A] [B] [C] [D] [E] [F] [1] 氏名 月分出席簿 [2] 日付 曜日 開始 終了 合計 助成 [3] 9:15 16:00 1 [4] 0 [5] 0 [6] 9:15 16:00 1
[A1]の計算式「=IF(COUNT(データ原簿!$C$1:$CQ$1)<ROW(A1),"",INDEX(データ原簿!$C$2:$CQ$2,SMALL(データ原簿!$C$1:$CQ$1,ROW(A1))))」 [B1]の計算式「=VLOOKUP(A1,データ原簿!A3:B50,2)」 [E1]の計算式「=データ原簿!A1&"年"&データ原簿!B1&"月分の出席状況"」 [A3]の計算式「=IF(COUNT(データ原簿!$C$1:$CQ$1)<ROW(A1),"",INDEX(データ原簿!$C$2:$CQ$2,SMALL(データ原簿!$C$1:$CQ$1,ROW(A1))))」 [B3]の計算式「=A3」 [C3]の計算式「=IF(A3="","",INDEX(OFFSET(データ原簿!$D$2:$CR$2,$A$1,0),SMALL(データ原簿!$C$1:$CQ$1,ROW(A1))))」 [D3]の計算式「=IF(A3="","",INDEX(OFFSET(データ原簿!$E$2:$CS$2,$A$1,0),SMALL(データ原簿!$C$1:$CQ$1,ROW(A1))))」 [E3]の計算式「=IF(A3="","",D3-C3)」 [F3]の計算式「=IF(A3="","",INDEX(OFFSET(データ原簿!$F$2:$CT$2,$A$1,0),SMALL(データ原簿!$C$1:$CQ$1,ROW(A1))))」
50行の仮データを作成して全てチェックした結果、うまく行きました。 業務上、法律の変更による、突然のフォーマットの変更などが多数あり、 困り果てていた所でした。 この年でExcelの勉強を始めたものですから、見渡す限り高い壁ばかりで・・・・ まだまだ沢山の書類がありますので、今回の知識を駆使して頑張って作成して みようと思います。 この度は本当に有難う御座いました。深謝いたします。 (Excelシロウトの年寄り)でした。
まず１点。 >50行の仮データを作成して全てチェックした結果、うまく行きました。 ということなので、転記ミスだと思いますが 印刷シート用の関数で、A3以降の各関数に入っているデータ原簿のセル範囲が 「$C$1:$CQ$1」（或いはそれをシフトさせたもの）のままですが、 実際のシートではDVまで広げてますよね？
もう１カ所確認ですが、印刷シートのA1セルは手入力ですよね？
なんだか確認してたら不安になってきました。（笑） 印刷シートのサンプルは、うまくいった数式の結果を コピーしたものではないですよね？ （書き込み用に作ったものと思っているのですが・・・）
ごめんなさい、やっぱり 印刷シート[B1]の計算式「=VLOOKUP(A1,データ原簿!A3:B50,2)」なのですが 「=VLOOKUP(A1,データ原簿!A3:B50,2,0)」としておいた方が良さそうです。 これは訂正して於いて下さい。
(HANA)
HANA 様。最後までミスばかりで、ご心配をおかけいたしました。
この書き込みボードに仮データからコピーすれば良かったのですが、 (調子に乗って)直接書き込んだ為、実際にはDVセルまで広げているにも拘らず、 以前の2項目抽出時のCQセルまでとミスってしまいました。 それから、印刷シートのA1セルは手入力です。申し訳ございません。 まだまだ力不足と反省しつつも、今後のExcelに対するトライが楽しくなってきました。
印刷シート[B1]の計算式のアドバイスありがとうございました。 只今、この意味について勉強させて頂いております。 本当にご迷惑をおかけいたしました。 (Excelシロウトの年寄り)でした。
別にかまいやしませんよ。私はただ単に、講釈好きなだけですから。
ということで、もう少しだけ垂れておきます。
１．「シート」という言葉の使い方
当初のご希望は「このシートから別の1名毎の出席表シートに転記したい」
ということでしたが、「この"シート"」と「出席表"シート"」はどちらも
ブック内に作成するSheet1とかSheet2の「シート」という意味で
使ってらっしゃいましたか？（後者のシートはどうも違うような気がしたのですが・・・）
今回のご質問はもしかしたら「この様に入力されている表から、
この様なフォーマットで、１名毎にこの様なデータのみ選び出して印刷したい」
という書き方をなさっていた方が伝わりやすかったかもしれません。
２．原簿シートの入力方法
原簿シートの入力方法が分からないので、現在の方法が一番入力が簡単なのかもしれませんが、
たとえば、「当日分を当日入力する」とか言うのであれば
☆入力用シート☆
[A] [B] [C] [D] [E] [F] [G]
[1] 氏名 出欠 日付 曜日 開始 終了 合計
[2] 氏名① ○ 1日 金
[3] 氏名② × 1日 金
[4] 氏名③ × 1日 金
[5] 氏名④ ○ 1日 金
↑=TODAY() ↑ドロップダウンリスト ↑=IF(・・・)
ドロップダウンリスト↑ ↑=B列 ↑ドロップダウンリスト
この様な表を作成しておいて、
☆データシート☆
[A] [B] [C] [D] [E] [F] [G]
[1] 2006年12月分の出席状況
[2] 氏名 出欠 日付 曜日 開始 終了 合計
[3] 氏名① ○ 1日 金
[4] 氏名② × 1日 金
[5] 氏名③ × 1日 金
[6] 氏名④ ○ 1日 金
データシートの最終行に、入力シートの２行目以下を日々値貼り付けで蓄積していく。
（データシートの書式設定は事前に済ませておく。
C～G列を印刷範囲に設定しておく。）
印刷するときは、2行目にオートフィルタをかけて、氏名で印刷したい氏名を 出欠で「×と等しくない」として絞り込むと、(C1に勝手に氏名は表示されませんが） ご希望の印刷様式に違い形で表示されると思います。
利点としては、ドロップダウンリストを設定する箇所数が少なくてすむ ・・・くらいかな？（関数はほとんど使わない とかも利点？）
いろいろやってみて下さいね。 (HANA)
HANA様、色々とアドバイス有難うございます。大変参考になります。 まず、シートについてですが、同じブック内のSheet1とSheet2の意味で お尋ねしたものでした。 今回ご教授戴いた方法を使ってみると、なるほど！と言う感じです。 ただ、ここ数日Excelを触るのが楽しく、次々と欲望が増えてきております。 今考えているのは、今回のこのシートの情報とは別に20列以上のデータが (同じ人のデータ)があり、このデータからも別のフォーマットに印刷する 必要がありますので、今回作成した原簿にくっつけようともくろんでいます。 もちろんデータ原簿は一つの方が良いのでくっつけたいのですが、 その場合、データが横に長くなる事とその別データをくっつけた場合、 大変横に長いデータになってしまい。入力し辛い(現在でも結構長い)ので、 そこで、Access見たいに一人分のデータをカード形式で入力できないものかと 「Excelの学校」を検索していたところ、やはり同じ悩みをお持ちの方が おられる様子で、昨日の書き込みより見つけました。とりあえずその方の書き込みの 動向を拝見し、勉強させて戴こうと考えております。 この年でパソコンを扱うことが楽しくなろうとは・・・・・
HANA様、もう一度お助け下さい。 データ原簿の項目を5項目に増やしたのですが、・・・・
データ原簿シート
[A] [B] [C] [D] [E] [F] [G] [H] ・・・[GF]
[1] 作業行
[2]項目行 № 氏名 1日 開始 終了 助成 時間① 時間② ・・・[GF]
[3] 1 氏名① ○ 9:15 16:00 1 1 2 ・・・[GF]
[4] 2 氏名② × 0 0 0 ・・・[GF]
[C3]に入れる計算式をもう一度教えていただけませんでしょうか？ C3に「=IF(MONTH(DATE($A$1,$B$1,1)+INT(COLUMN(C1)/5))=$B$1,DATE($A$1,$B$1,1)+INT(COLUMN(C1)/5),"")」 と入れているのですが、私の理解違いと思います。どうかご指摘お願い致します。 3日・9日・15日・21日・27日が表示されません。 どうかよろしくお願い致します。 (Excelシロウトの年寄り)
ごめんなさい、説明が間違っていました。 C1をE1に変更して下さい。 詳細は、時間がとれ次第書き込みします。
(HANA)
遅くなりましたが（・・・いつもと大して変わらないか・・・） 訂正箇所をまず訂正しておきます。 >まず、「数式の変更箇所」を載せます。 > (1)現在３列が１日分 → 今後４列が１日分 > データ原簿 C1とC2の式の中で使われている「3」という数字はこの数字です。 もう一つ「３」という数字を表す部分がありました。 それがC2セル用の COLUMN(C1) です。 今回５列が１日分にしたいので、「５」という数字を表す COLUMN(E1) に変更です。
申し訳程度に式を分解しておきますので、実際のシートでご確認下さい。 実際の式は >C2に「=IF(MONTH(DATE($A$1,$B$1,0)+INT(COLUMN(C1)/3))=$B$1,DATE($A$1,$B$1,0)+INT(COLUMN(C1)/3),"")」 ですが、まず最初に分解するのは「INT(COLUMN(C1)/3)」この部分です。 ３行目の下に５行、行を挿入して下さい。 C4 =COLUMN(C1) C5 =C4/3 ←小数点以下も表示させて下さい。 C6 =INT(C5) C列の各行に上の式を入れて右にずるずるっとコピーすると 4行目は「３」を先頭に一つずつ数が上がっていき 5行目は4行目を「３」で割った数が入り 6行目は5行目の商のみの数値が表示されます。 6行目は同じ数字が３つセットで「１」を先頭に順番に数が上がっていると思います。 ここで、実際の式の「INT(COLUMN(C1)/3)」の前の部分を見ると 「DATE($A$1,$B$1,0)+」となっていますよね？ つまり、“A1の年のB1の月の0日”＋6行目の数値 がその列に表示される日付です。 C7に =DATE($A$1,$B$1,0)+C6 （DATEの３番目の引数は「0」です。完成した式を見た時にチェック漏れでした。ごめんなさい。 0の時と、1の時と 式を右にフィルドラッグして結果の相違を確認してみて下さい。 ・・・思えばC2用の式のチェックはダメダメでしたね・・・。）
これにIF関数で、「その列に表示される予定の日付の月」と「B1の月」とを比べて 同じだったら「予定の日付」を違っていたら「""」を表示させるようにしています。 C8 =IF(MONTH(C7)=$B$1,C7,"")
と言った感じなのですが、すごい早口なので「これってなに？」「何をしろと？」 なんて疑問がありましたら遠慮なくお申し付け下さい。
そうそう、それぞれの数式は右にコピーして結果がどんな値になるか確認して下さいね。
(HANA)
HANA 様 解説ありがとうございました。もうひとつ教えてください。
式を分解して頂いた事により私にも全貌が見えてきたような気がいたします。 なるほど!と言った感じです。しかしすごい！！ 分解していただいたおかげで、「「５」という数字を表す COLUMN(E1) 」の意味も 理解できました。 この解説を踏まえたうえで下記のとおり式を入れてみました。
[C1]の計算式「=IF((OFFSET(C2,実績記録表!$A$1,0)<>"×")*(OFFSET(C2,実績記録表!$A$1,0)<>"")*(MOD(COLUMN(A1)+5,6)=0),COLUMN(A1),"")」
[C2]の計算式「=IF(MONTH(DATE($A$1,$B$1,0)+INT(COLUMN(E1)/5))=$B$1,DATE($A$1,$B$1,0)+INT(COLUMN(E1)/5),"")」
この場合、6・12・18・24・30の 6の倍数が日付表示されません。 どこか私の設定が悪いのでしょうか？お助け下さい。 (Excelシロウトの年寄り)
>日付表示されません。 データ原簿のシートに表示されないのですか？ C2用の式を上と同じように分解して確認してください。
「DATE($A$1,$B$1,0)+INT(COLUMN(E1)/5)」 この部分を分解してセルの書式設定で月日を表示するようにしておけば 確認しやすいと思います。
どこかおかしなところが見つかると思いますが・・・。 （ちなみに、私は見つけられていませんので悪しからず。）
・・・それで、日付表示されずになにが表示されるのですかね？
(HANA)
HANA 様。分解してみてようやく分かりました。1日の抽出項目が5項目ありますので、 日付を入れると6列と言うことになります。 したがって、 [C2]の計算式は「=IF(MONTH(DATE($A$1,$B$1,0)+INT(COLUMN(F1)/6))=$B$1,DATE($A$1,$B$1,0)+INT(COLUMN(F1)/6),"")」 となりました。 5項目だから「5を返すE1」と思い込んでおりました。 5項目だから日付を入れると6列ですので「6を返すF1」を指定しなければならない事に ようやく気付きました。 今度はきちんと1日から31日まで1日も抜けることなく表示できました。 本当にお世話をおかけしました。 私の単純な勘違いでした。申し訳ありません。 しかしながら式を分解すると手に取るように分かります。ありがとうございました。 (Excelシロウトの年寄り)
どうも気になるので戻ってみたら ・・・確かに、５項目は６列でしたね。 （完璧に「５列で１日分」だと勘違いしていました。） まぁ、問題が解決したようで良かったです。
数式が思った結果を出さない時は、まずはその式を分かる所まで分解して それぞれの数式が思った結果を出しているか確認して下さいね。 式を作るときは、その逆が良いでしょう。 個々の数式で思った結果を出せるような物が出来てから（C4～C7）、 それを組み合わせて思った結果が出ることを確認してから(C8) 最後に一つの式に纏めます。
ただ、内容がころころ変わるようでしたら、無理に一つの式に纏めずに 作業行としてある程度残しておくのも一案だと思います。 （その方がメンテが簡単ですし、式の意味も分かりやすいです。）
因みに、C1用の式の中の 「COLUMN(A1)+5」この部分は、右にコピーすると「6,7,8･･･」と増えていきます。 どこかでこの並び見た気がしませんか？ どちらかに統一しておいた方が良さそうですね。（笑）
(HANA)
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.