[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『日毎のデータを別シートの月間の同じ日付に転記』(新人A)
初めて利用させていただきます。
現在、来場者の会員NOと氏名を毎日50人ほど紙に書いておりますので、データ化したいと思っております。希望は下記のとおりですが、他にいい案があれば教えていただきたいと思っております。
ご回答いただいてもうまく理解出来るか不安ですが、どうぞよろしくお願い致します。
1.会員を新規登録するときは、テーブルにしたシート1『会員リスト』に入力して追加しても範囲選択が出来るようにしています。
2.来場したら、シート2『毎日の入力』に会員NOを入力するとVLOOKUPで氏名、種別、客層などが表示される。同じシートにCOUNTIFでその日の種別、客層など集計をする。これをシート3『4月』の同じ日付に飛ばしたい。
3.シート3『4月』にVLOOKUPで転記させる事は出来ましたが、4/2になると4/1の参照元が消えるので #N/A となってしまう。 どのようにしたらベストかわからずにいます。
※XLOOKUP関数は使えない環境です。
シート1『会員リスト』
NO 氏名 種別 住所 1 あ 70歳 墨田区 2 い 70歳 世田谷区 3 う 18歳 江東区 4 え 70歳 目黒区 5 お 70歳 品川区 6 か 障害 江戸川区
シート2『毎日の入力』
NO 氏名 種別 客層 チケットNO
1 あ 70歳 メンバー 16
3 う 18歳 ビジター 17
4 え 70歳 ビジター 18
5 お 70歳 メンバー 19
8 く 70歳 メンバー 20
9 け 70歳 ビジター 21
70歳 18歳 障害 メンバー ビジター 開始NO 最終NO 合計 2022年4月2日(土) 5 1 0 3 3 16 21 6
シート3『4月』
日付 70歳 18歳 障害 メンバー ビジター 開始NO 最終NO 合計
2022年4月1日(金) #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A
2022年4月2日(土) 5 1 0 3 3 16 21 6
2022年4月3日(日)
2022年4月4日(月)
2022年4月5日(火)
< 使用 Excel:Excel2016、使用 OS:Windows10 >
ただ、参照する範囲が消えてしまう様な使い方は、どうなのかなとは思いますが。
(みかん) 2022/03/21(月) 21:34
『4月1日の業務終了時(又はシート2の日付を変更する前)に、シート3の4月1日の行の関数が入っている範囲を選択してコピーし、そのまま値貼り付けしておくとか。』→4/2,4/3と数式はコピペしてるので、シート2の日付が変われば、その日付の行に数字は反映するのですが、参照元が一つ(1日)だけのせいか、前日のデータが消えて『#N/A』が表示されます。
ご指摘にあるようにやっぱり『参照する範囲が消えてしまう様な使い方は、どうなのか』がいけないのかもしれません、根本的に理解していないところがあると思いますが、そこが気づけずにいます。
(新人A) 2022/03/21(月) 22:09
冒頭に書いた条件が違うのであれば、
もう少しシート2とシート3の全容と、どのような関数で参照しているのか示された方が良いかと思います。
以下は御社のご都合もあろうかと思いますので適当に聞き流して下さい。
シート2の毎日入力シートを毎日使いまわすのではなく、せめて月ごとのデータベース的に運用していくようにされてはどうでしょう。
日付入力列を追加するだけでも後からCOUNTIFS等で集計することができますし、
当日の利用者だけ確認したければ、オートフィルターでその日付だけのデータを抽出することもできます。
(みかん) 2022/03/21(月) 22:45
私の説明不足でしたが、
・日が変わったら入力範囲は消去して、また1から記入
・シート2にある2行の範囲に対してVLOOKUPで参照しているので、「4月1日」が無くなれ#N/Aになる。
みかんさんの推測されたとおりです。
私もとりあえず場当たりで、シート2の2行からシート3に毎日コピペしようかと思いました。
念のため、シート2とシート3の全容です。
シート2
日付の横、
70歳の所には =COUNTIF(D:D,I6)
18歳の所には =COUNTIF(D:D,J6)
障害の所には =COUNTIF(D:D,K6)
開始NO =テーブル2[@チケットNO]
最終NO =MAX(テーブル2[チケットNO])
シート3
ただ、今回みかんさんが最後におっしゃった『毎日入力シートを毎日使いまわすのではなく〜』『せめて月ごとのデータベース的に運用』『日付入力列を追加するだけでも後からCOUNTIFS等で集計』というのが気になったので、どのように作れるか今から考えてみます。
(新人A) 2022/03/21(月) 23:26
(新人A) 2022/03/21(月) 23:55
COUNTIFS等での集計例を挙げておきます。
<70歳〜>
B2セルに =COUNTIFS(テーブル2[日付],$A2,テーブル2[種別],B$1)
として、コピペ
<メンバー〜>
E2セルに =COUNTIFS(テーブル2[日付],$A2,テーブル2[客層],E$1)
として、コピペ
<開始NO〜>
Excelのバージョンが2016ということですが、
MAXIFS関数、MINIFS関数は使えますか?(Microsoft365の2016なのかどうか:わからなければスルーで)
関数の一覧にMAXIFS等があれば、
開始NO =MINIFS(テーブル2[チケットNO],テーブル2[日付],$A2)
最終NO =MAXIFS(テーブル2[チケットNO],テーブル2[日付],$A2)
無ければ、
開始NO =MIN(IF(テーブル2[日付]=$A2,テーブル2[チケットNO],""))
と入力までして、Ctrl+Shift+Enterで確定(配列数式でWeb検索してください)
最終NO =MAX(IF(テーブル2[日付]=A2,テーブル2[チケットNO],""))
上と同様、Ctrl+Shift+Enterで確定。
すみませんが、時間ですので、ここまでです。
質問等々あれば、どなたか回答して下さるかと思います。
(みかん) 2022/03/22(火) 08:23
ただ、また別の問題が出てきたのですが、
只今外出先の為、また自宅に戻って解決しなければ、
こちらで質問させていただきます。
(新人A) 2022/03/22(火) 10:54
どうやら会員登録をされない、その都度会員の方もいるようでして、それはどのように反映させたら良いでしょうか。
そういう方は、会員NOの所に『未』という風に表示させて、名前は手入力でいいと思いますが、『未』と、種別の『70歳』『18歳』『障害』についてはなるべく手入力をしないで済むようにしたいと思っております、
会員NOを入れるセルに、『未』とプルダウンで選択できるよう設定にしてみましたが、VLOOKUPの検索値ともなっているセルなので併用は出来ませんでした。
ちなみに、会員登録者は1800人ほど。
入場者は、5〜80人/1日ほどで幅はあります。
都度会員は、0〜3人/1日ほどです。
(新人A) 2022/03/23(水) 16:56
会員証を発行しない都度会員は、シート1『会員リスト』に
新規登録するわけにはいかないのかな、と思ってまして…。
会員NO.1の前に、会員NO.0を作って、氏名などは、
空白にして、都度会員は皆、会員NO.0を利用するのも
ありなのかな、と思ったりもして。
(新人A) 2022/03/23(水) 19:12
データの入力規則の設定タブ→入力値の種類でリストを使ったということですか?
エラーメッセージタブの「無効なデータが入力されたらエラーメッセージを表示する」のチェックを外すと、
プルダウンの値も選択できるし、任意の文字も入力できます。
>『未』と、種別の『70歳』『18歳』『障害』についてはなるべく手入力をしないで済むようにしたいと思っております
例えばですが、シート1『会員リスト』のNO列に「未70」「未18」「未HC」として、それぞれ対応する種別を入れておく。
プルダウンで「未70」「未18」「未HC」を選べば、VLOOKUPで参照できますよね。
未登録者の集計をしたい場合などは、NO列は数値とリストの値だけでしょうから、条件を"未*"とすれば集計できるでしょう。
(みかん) 2022/03/23(水) 19:35
(新人A) 2022/03/23(水) 19:50
(agjtm) 2022/03/23(水) 20:48
先程、シート1『会員リスト』の会員NO列の一番最初に「未70」「未18」「未HC」と、種別まで登録しました。みかんさんの言うとおりにエラーのチェックを外すと、プルダウンの値も選択でき、会員NOも入力できました!
プルダウンで選択するとVLOOKUPの数式の入った氏名のところに0(ゼロ)と表示されますが、氏名を手入力することも出来ました!
これでどうにか出口が見えてきました。あとは数名がこのシートを触るので数式が消されないようにしたらOKかと思います。 4/1から利用出来るように早めに仕上げて他に問題がないか確認したいと思います。 ここまでご親切に教えていただき本当に感謝の気持ちでいっぱいです。
(新人A) 2022/03/23(水) 22:56
シート2のVLOOKUPの数式が入っている列に、都度会員は氏名を手入力するつもりでしたが、数式の入った列はロックしたいため、またさまよってます(+_+)
都度会員も名前や住所などを書いてもらっているので、
シート1にもうひとつ会員証を作らない都度会員用のテーブルを作って、
2つのテーブルを範囲として、シート2の氏名に返すことは出来るでしょうか。
もしくは、まったく違った案があれば・・。
番号のある会員だけなら良かったのですが、そうでない人もおり、
とりあえず、その都度会員の名前などの情報も残しておきたい為です。
(新人A) 2022/03/24(木) 10:33
シート2の表をメンバーとビジターに分けては、という意見もありましたが、
チケットNOがつながらなくなるので、それは避けたいなと思っております。
何かいい方法がないか知恵をいただきたいと思っております。
(新人A) 2022/03/25(金) 17:00
そもそも、ある会員が利用日によってメンバー/ビジターの立場が変わるわけでもないのなら、
シート1の会員リストにメンバー/ビジターの情報を加えるだけでは?
(みかん) 2022/03/25(金) 21:19
改めて説明させていただきます。
客層(メンバーorビジター)は、その日一緒に来場される人数や予約形態などによって異なるため、 シート1に予め登録することが出来ず、入力規則のリストで選択出来るようにしてあります。
また会員証を発行するのは70歳以上か、18歳未満か、障害者か、このどれかに該当する方のみです(割引がある為)
シート2で会員NOを入力して返されるのは、氏名と種別だけとなります(※読み返すと、客層もと書いてある所がありました(>_<))
よって、NO列に会員番号を入力、または「未70、未18、未HC」を選択して、キーボードの→矢印2回押して客層の列まで来るのは良いが、
そのあとマウスに切り替えて▼を押してメンバーorビジターを選択する事が煩わしいようです。マウス使わずにキーボードのAlt+↓でリストも表示させることも出来るとやって見せましたが、Altが矢印ボタンと離れているせいか、両手を使うためそれも難色を示していました。
これまで紙に書いてたときよりよっぽどましではないか、と思うのですが、
お客様や電話対応に追われる中、慣れないパソコン操作で余計パニックになりそうという事でした。
慣れればいいことなので、このままやってもらう事もありです。
ただ『紙に書いてたほうがいいや』と思われたくないため何か方法があればと思い、またこちらで質問させていただきました。
(新人A) 2022/03/25(金) 22:59
状況は理解しました。 PCの習熟度には個人差があるので、今回出た意見はごもっともだと思います。
>慣れればいいことなので、このままやってもらう事もありです。 今回のケースではPCの基本的操作で行える業務ではあるので、 職員のスキルアップも兼ねて慣れていただく方が良いのかと思います。
上記を踏まえつつ、対策案をいくつか挙げます。 [1]客層列には、メンバーの場合「M」/ビジターの場合「V」と入力することにする。 「Vがどこにあるのかわからない」「大文字にならない」等の意見が出るでしょうから次案へ。
[2-1]客層列には、メンバーの場合「1」/ビジターの場合「2」と入力することにする。 「あれ、メンバーって1だっけ、2だっけ?」等が起きるリスクがある。
[2-2]上記[2-1]を受けて、VBAのWorksheet.Changeイベントを用いて、 「1」と入力されたら「メンバー」/「2」と入力されたら「ビジター」とセルの内容を自動で書き換える。 VBAを扱う知識が必要になるので、ご自身の学習が必要になる事や、 ご自身の異動等によりVBAが用いられていることを把握していない職員が引き継いだ時に戸惑う可能性もあります。
一応、以下のコードでいけるのではと思います。 テーブル名は「テーブル2」と想定しています。 テーブル2があるシートのタブ上で右クリック→コードの表示→出てきた画面に以下を貼り付け。 ワークブックをマクロ有効ブックで保存し直す必要があります。 ※VBAの利用は自己責任でお願いします。
Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range, r As Range Set rng = Range("テーブル2").ListObject.ListColumns("客層").DataBodyRange If Intersect(Target, rng) Is Nothing Then Exit Sub Else Application.EnableEvents = False For Each r In Target Select Case r.Value Case 1 r.Value = "メンバー" Case 2 r.Value = "ビジター" End Select Next r Application.EnableEvents = True End If End Sub (みかん) 2022/03/26(土) 08:08
(みかん) 2022/03/26(土) 08:17
そこでみかんさんの対策案とても興味があります。
私はマクロの記録はやったことありますが、VBAは未経験です。
ですが、→出てきた画面に以下を貼り付け。とありましたので、
とりあえず、やってみたいと思います。
[3]セルの書式設定の〜、も確認してみます。
(新人A) 2022/03/26(土) 09:26
お陰様でだいぶ完成に近づいてきましたが、
また2点質問させていただきたいです。
1つめは、
シート3の『集計』についてです。
70歳以上 18歳未満 障害 メンバー ビジター 開始NO 最終NO 合計(人)
R4/4/1
R4/4/2
R4/4/3
・
・
として、シート2の『入力』の値が自動的に反映されるようにしました。
そこで合計(人)の部分を『最終NO-開始NO+1』として求めていますが、
オートフィルすると未入力の日にちの合計部分が全部1となります。
開始NOと最終NOで求めるのは、番号に抜けがないか確認したいためです。
未入力の日にちの合計は0と表示したい、または何か別の方法がないでしょうか。
2つめは、
シート3の『集計』を事務所で閲覧する方法です。
共有フォルダがないので、毎日集計の数字だけメモを受け取る?
とも考えましたが『Windows10の「共有フォルダ」という仕組み』というのが
あるようなので、それでいけるかな、と思っております。色々方法はあると思いますが
別案がありましたら、アドバイスお願い致します。
(新人A) 2022/03/28(月) 21:52
1つめは、開始か最終NOが空欄だったら空欄にするようIF関数をかませればいいと思います。 2つめが分からないのですが、「どこにあるファイルを」事務所から見たいのですか? 関係性がわからないのでアドバイスしづらいです。 (.:*.ゆ ゅ) 2022/03/29(火) 13:07
2つめの事務所で閲覧とは、
フロントで入場者をシート2に入力し、その集計がシート3に反映されるのですが、隣の事務所でそのシート3のデータを見たいわけです。共有フォルダがないので、毎日フロントのPCからメモるのもどうかと思い、方法を探っているとネットに『Windows10の「共有フォルダ」という仕組み』というのがあったので、それで対応出来そうですが、内容はまだ詳しくみていません。他に何かいい案があれば教えていただきたいと思っております。
※今のところ、シート2とシート3は下記のような感じです。
シート2『毎日の入力』
48 4/1 70歳 い メンバー 11 9 4/1 18歳 う ビジター 12 4 4/1 70歳 え ビジター 13 105 4/2 障害 お ビジター 14 6 4/2 70歳 か ビジター 15 7 4/2 70歳 き メンバー 16
シート3『集計』
・ 0 0 0 0 0 0 0 1 ・ 0 0 0 0 0 0 0 1
(新人A) 2022/03/29(火) 21:27
>『最終NO-開始NO+1』 この数式で連番の抜けを判断できるかはひとまず置いておき、 すでに指摘のあるように、IF関数を噛ませば良いでしょう。 例えば、=IF(最終NO+開始NO=0,0,最終NO-開始NO+1) とかでいいのでは。
現在の数式では、11,12,12,14の場合と11,12,13,14の場合で違いは出なくないですか。 そもそも、提示例の様に必ず昇順並びにしかならないのであれば、 チケットNO列の先頭行だけ手入力しておき、IF関数で会員NO列が空白で無かったら、 チケットNO列の1行上の数値に+1するようにすればタイプミスも防げるのでは。 チケットを発券機で出しているのか、都度手書きしているのか分かりませんが、 渡す時に番号を確認して渡せば良いことではないでしょうか。
2つめの質問は、こちらも既に指摘がありますが、 御社のネットワークの構成要素が不明である以上、誰もアドバイス出来ないのでは? (アドバイスを貰えば機器の導入費用に糸目は付けないというのであれば別ですが) 事務所のPCとフロントのPCが同一ネットワーク上にあるかどうかすら明記されていないです。 無理やり読み取ろうとするならば、 「おそらく同一ネットワーク上あるPC間でファイルの共有をしたい」ということだけですので、 >『Windows10の「共有フォルダ」という仕組み』 で、目的は果たせると思いますよ。 あと、ひとつお伝えしておくと、ここは「エクセルの学校」の質問掲示板です。
以下「初めての方へ」より抜粋 (q) このサイト(掲示板)について ・Excelについての質問や答えなどを書き込んでください
(みかん) 2022/03/29(火) 22:04
=IF(最終NO+開始NO=0,0,最終NO-開始NO+1) であっさり解決致しました。
また、Excel以外の質問をしていたことにも気づかず
大変失礼致しました。危うく更に暴走するところでした。
ご指摘いただきありがとうございました。
今回のタイトル『日毎のデータを別シートの月間の同じ日付に転記』で
分からない事をたくさん教えていただき、やりたいことを形にする事が出来ました。
実際に使う人にも見せたところ『これで楽になりそう』と言ってもらえました。
本当にこれまで親切に教えていただきありがとうございました。
長々となってしまった今回のタイトルからは卒業出来そうです。
また新たなタイトルで質問させていただく時には、お力をお貸しいただければ嬉しいです。
(新人A) 2022/03/30(水) 00:03
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.