エクセル2007で、sheet1に元となるデータがあり、その中からsheet2に月毎に列でまとめて、しかも日付順に並ばせたいと思っています。
まず、下記がsheet1のデータ例です。
--------------------------------------------
[sheet1]
A列 B列 C列 D列
1 名前 レンタル希望 血液型 担当
2 山田太郎 2013/9/12 A型 営業
3 鈴木花子 2013/8/1 B型 開発
4 山下健 2013/9/10 O型 広報
--------------------------------------------
上記のような、随時追加・修正されていくデータです。
それを、下記のようにsheet2にB列(レンタル希望日)の月で検索し、B列の日付順でA列(名前)とB列(レンタル希望日)を随時表示させていきたいです。
--------------------------------------------
[sheet2]
A列 B列 C列 D列
1 8月レンタル 9月レンタル
2 名前 レンタル希望 名前 レンタル希望
3 鈴木花子 2013/8/1 山下健 2013/9/10
4 山田太郎 2013/9/12
--------------------------------------------
このような形でsheet1を変えたものをレンタル希望日を基点にとして
随時並ばれていて、この月はこのぐらいの希望がこの人からあるのか(あったのか)
というのがわかるような管理表を作りたいと思っています。
フィルタオプション等をいろいろ試してみましたがうまくいかず、
また、なかなかマッチするQAが見つからず困っています。。。
どうかよろしくお願いします!
A 回答 (5件)
- 最新から表示
- 回答順に表示
No.5
- 回答日時:
こんばんは!
極端にデータ量が多い場合はおススメしませんが・・・
↓の画像で説明します。
上側がSheet1、下側がSheet2とします。
Sheet2のA1セル(B1セルと結合しています)とC1セル(D1セルと結合)の表示形式に手を加えます。
A1~C1セルを範囲指定 → 右クリック → セルの書式設定 → 表示形式タブ → ユーザー定義
→ G/標準 と表示されているところを消して
0月レンタル
と入力し、OK
そしてA1・C1セルには単に月の数値のみを入力します。
次にSheet2のA3セルに
=IFERROR(INDEX(Sheet1!A$2:A$1000,MATCH(SMALL(IF(MONTH(Sheet1!$B$2:$B$1000)=$A$1,Sheet1!$B$2:$B$1000),ROW(A1)),Sheet1!$B$2:$B$1000,0)),"")
これは配列数式になりますので、Shift+Ctrl+Enterで確定!
この画面からコピー&ペーストする場合はA3セルを選択 → 数式バー内に貼り付け
→ そのまま(編集可能のまま)Shift+Ctrlキーを押しながらEnterキーで確定!
数式の前後に{ }マークが入り配列数式になります。
これを隣のB3セルまでオートフィルでコピー!
C3セルには
=IFERROR(INDEX(Sheet1!A$2:A$1000,MATCH(SMALL(IF(MONTH(Sheet1!$B$2:$B$1000)=$C$1,Sheet1!$B$2:$B$1000),ROW(A1)),Sheet1!$B$2:$B$1000,0)),"")
(これも配列数式です)
として隣のD3セルまでコピー!
B3・D3セルの表示形式を「日付」にして
最後にA3~D3セルを範囲指定 → D3セルのフィルハンドルで下へコピー!
これで画像のような感じになります。
※ 同一日がある場合は最初の行が重複して表示されると思います。m(_ _)m
No.4
- 回答日時:
分かり易い方法は作業列を作って対応する方法です。
シート1のF2セルには次の式を入力してI2セルまで横にドラッグコピーしたのちに下方にもドラッグコピーします。
=IF($A2="","",INDEX($A:$D,MATCH(SMALL($B:$B,ROW(A1)),$B:$B,0),COLUMN(A1)))
これによってすべてのデータは日付について昇順で並べられます。
ところでシート2ですがお示しのような表にしますがA1セルに入力する8月レンタルというのは2013年8月レンタルのように入力します。C1セルには2013年9月レンタルのように入力します。左の横列方向には他の年月のレンタル文字が入力されても対応することができます。
なお、年や月の数字は必ず半角英数文字にします。
2行目はお示しのように項目名を並べます。
ところでシート2を完成するためにシート1ではさらに作業列を追加します。
J2セルには次の式を入力して下方にドラッグコピーします。
=IF(COUNTIF(Sheet2!$1:$1,YEAR(G2)&"年"&MONTH(G2)&"月レンタル"),MATCH(YEAR(G2)&"年"&MONTH(G2)&"月レンタル",Sheet2!$1:$1,0)*1000,"")
さらにK2セルには次の式を入力して下方にドラッグコピーします。
=IF(J2="","",J2+COUNTIF(J$2:J2,J2))
そこでシート2ですがA3セルには次の式を入力したのちに右側方向にドラッグコピーしたのちに下方にもドラッグコピーします。
=IF(COUNTIF(Sheet1!$K:$K,IF(MOD(COLUMN(A1),2)=1,COLUMN(A1)*1000,(COLUMN(A1)-1)*1000)+ROW(A1)),INDEX(Sheet1!$F:$G,MATCH(IF(MOD(COLUMN(A1),2)=1,COLUMN(A1)*1000,(COLUMN(A1)-1)*1000)+ROW(A1),Sheet1!$K:$K,0),MOD(COLUMN(A1)-1,2)+1),"")
どんなにデータが多くなっても計算に負担が少ない方法としてお勧めです。
作業列が目障りでしたらそれらの列を選択したのちに「非表示」を選択すればよいでしょう。
回答ありがとうございます!
sheet1のデータが列も行も追加していき膨大なデータになりそうだったので、
作業用のシートを作成し、そちらで作成してみました。
ただ、”レンタル希望日”が重複したデータの場合、すべて同じ名前が表示されてしまいます。
こちら解消方法はありませんでしょうか?
No.3
- 回答日時:
>このところで、列ラベルとΣ値の両方に「レンタル希望」を入れる方法がわかりませんでした。
フィールドリストの中の「レンタル希望]の部分にカーソルを置いて、そのまま左クリックでその下の列ラベルにドラッグすれば、列ラベルに「レンタル希望」が入ります。同様にもう一度「レンタル希望]の部分にカーソルを置いて、そのまま左クリックでΣ値の部分にドラッグしてください。
No.2
- 回答日時:
参考までに。
関数で対応するなら、例えば以下のような数式になります。
8月のデータを表示するなら、添付画像のA14セル、B14セルに以下の式を入力します。
A14セル:=INDEX($A:$A,SMALL(INDEX((MONTH($B$2:$B$10)<>8)*1000+ROW($A$2:$A$10),),ROW(1:1)))&""
B14セル:=IF(A14="","",SMALL(INDEX((MONTH($B$2:$B$10)<>8)*100000+$B$2:$B$10,),ROW(1:1)))
9月のセルには上記の数式の「8」を「9」に変更してください(セル参照してもよい)。
No.1
- 回答日時:
ご希望の表示は関数でも対応できますが、配列数式を使うと数式が煩雑でメンテナンスできなくなる可能性があるだけでなくシートの動きが重くなるなどのデメリットがあります。
このようなケースでは(ただし同じ月に同じ人が2回以上のレンタル希望日が無い場合)ピボットテーブルを使用されることをお勧めします。
「挿入」「ピボットテーブル」で行ラベルに「名前」列ラベルとΣ値に「レンタル希望」をドラッグし、作成された列ラベルの日付の上で右クリックし「グループ化」で「月」を選択します。
次にデータフィールドの上で右クリックし「値フィールドの設定」で「最大値」を選択し、最後にこのフィールド全体を選択して右クリック「セルの書式設定」から日付の表示形式を設定します。
最後にピボットテーブル上で右クリックし、「ピボットテーブルオプション」の集計とフィルタタブで行と列の総計を表示しない設定にします(添付図参照)。
さっそく回答いただきありがとうございます。
>列ラベルとΣ値に「レンタル希望」をドラッグし
このところで、列ラベルとΣ値の両方に「レンタル希望」を入れる方法がわかりませんでした。
教えていただいたにも関わらず、追加の教えてになってしまいますが、
ご教示いただけると助かります!
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) エクセルについて教えてください。 3 2023/06/28 09:11
- Excel(エクセル) SUMIFSと日付変換 10 2023/04/16 15:38
- その他(Microsoft Office) 従業員増減対応で当番種類の増減対応な当番表 21 2022/07/19 07:30
- Excel(エクセル) マクロか関数で処理したいのですが、教えて頂けませんか。 8 2022/10/31 15:18
- Excel(エクセル) エクセルの参照について教えてください 1 2022/12/08 16:06
- Excel(エクセル) 【困っています】VBA 追加処理の記述を教えてください。 1 2022/08/25 22:54
- Visual Basic(VBA) リストポックス検索 1 2022/06/19 21:32
- 電車・路線・地下鉄 南海特急の座席指定 2 2022/10/02 16:09
- Visual Basic(VBA) AdvancedFilterについての質問 2 2022/07/02 22:58
- Excel(エクセル) EXCEL 関数を教えてください。(A列の同じ値が複数ある場合vlookupで出来ますか) 4 2022/12/07 20:54
このQ&Aを見た人はこんなQ&Aも見ています
-
プロが教えるわが家の防犯対策術!
ホームセキュリティのプロが、家庭の防犯対策を真剣に考える 2組のご夫婦へ実際の防犯対策術をご紹介!どうすれば家と家族を守れるのかを教えます!
-
エクセル 指定した月のデータを自動的に反映させられる方法
Excel(エクセル)
-
1年分のデータから特定の月分のデータを抽出→表示
Excel(エクセル)
-
【エクセル関数】複数条件(月別・個人別)で集計したい
Excel(エクセル)
-
-
4
別シートから月(MONTH)で抽出する方法について
Excel(エクセル)
-
5
エクセルで、月によって参照するシートを自動的に変える関数
Excel(エクセル)
-
6
Excelでシートに年月を自動で変更するやり方
Excel(エクセル)
-
7
リンク元の日付が空白の時リンク先セルも空白にしたい
Excel(エクセル)
-
8
Excelで指定した月ごとの数値を別シートにある基データから抽出し表示させたい。
Excel(エクセル)
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
Access レポート印刷するときに...
-
Accessでフォームに自動入力し...
-
アクセスクエリで教えて下さい...
-
【Access】Dcount関数の複数条...
-
Access VBA [リモートサーバー...
-
CSVファイルの「0落ち」にVBA
-
Accessのリンクテーブルのパス...
-
Accessで作ったデータベースをw...
-
Access VBA を利用して、フォル...
-
Accessのクエリの結果を、既存...
-
Accessのクエリで、replace関数...
-
ACCESS VBA でのエラー解決の根...
-
ACCESS2019でのエラーメッセージ
-
Access DAOのExecuteメソッドの...
-
Accessのスプレッドシートエク...
-
Access Error3061 パラメータが...
-
accessデータを指定したExcel、...
-
accessの代わりになるもの
-
日付のテキストボックスに(例...
-
Accessのテキストボックスの入...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Access レポート印刷するときに...
-
Microsoft365にAccessってあり...
-
Accessのクエリで、replace関数...
-
ACCESS VBA でのエラー解決の根...
-
Accessのスプレッドシートエク...
-
Access VBA を利用して、フォル...
-
【Access】Dcount関数の複数条...
-
accessデータを指定したExcel、...
-
Accessレポートのチェックボッ...
-
Access VBA [リモートサーバー...
-
Vba Userformを前面に出すについて
-
Accessのリンクテーブルのパス...
-
実行時エラー3131 FROM 句の構...
-
Accessのフォーム上のテキスト...
-
CSVファイルの「0落ち」にVBA
-
Access 複数条件検索の設定が上...
-
アクセス 削除するレコードを含...
-
Access で半角スペースと全角ス...
-
access 更新クエリについて
-
Accessのクエリの結果を、既存...
おすすめ情報