質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

Q&A

解決済

2回答

1531閲覧

VBA ソート機能 参照データ 挿入データ 対応

King_of_Flies

総合スコア382

VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

0グッド

0クリップ

投稿2018/02/08 06:18

タイトルだけでは不明だと思うので、
シートの状態を説明しながら質問をしたいと思います。

現在使用しているシートは二種類で「sheet1,sheet2」とします。

sheet1には、
項目行として1行目に
「No,学年,学級,学籍番号,出席番号,生徒氏名,コース,単位」があるとします。(左からA1,A2,A3・・・セルです。)

データ行として2行目以降には
1,二年,一組,GG00001,01,岡部,文系,64
2,二年,一組,GG00002,02,橋田,文系,64
3,二年,一組,GG00003,03,栗栖,文系,64
4,二年,一組,GG00004,04,まゆり,文系,64
5,二年,二組,GG00005,01,天音,理系,64
6,二年,二組,GG00006,02,ルカ子,理系,64




X,X年,X組,GG00000,XX,織田信長,理系,64

sheet2は、
項目行として1行目に
「No,学年,学級,学籍番号,出席番号,生徒氏名,部活,進路」があります。(左からA1,A2,A3・・・セルです。)

データ行として2行目以降には
=sheet1!A1,=sheet1!B1,=sheet1!C1,=sheet1!D1,=sheet1!E1,=sheet1!F1,剣道部,法学部
=sheet1!A2,=sheet1!B2,=sheet1!C2,=sheet1!D2,=sheet1!E2,=sheet1!F2,柔道部,法学部
=sheet1!A3,=sheet1!B3,=sheet1!C3,=sheet1!D3,=sheet1!E3,=sheet1!F3,野球部,医学部
=sheet1!A4,=sheet1!B4,=sheet1!C4,=sheet1!D4,=sheet1!E4,=sheet1!F4,サッカー部,法学部
=sheet1!A5,=sheet1!B5,=sheet1!C5,=sheet1!D5,=sheet1!E5,=sheet1!F5,バスケ部,芸術部




=sheet1!AX,=sheet1!BX,=sheet1!CX,=sheet1!DX,=sheet1!EX,=sheet1!FX,天文部,法学部
という形で、sheet1を参照して6項目のデータを入れていて、7項目目と8項目目の部活、進路についてはVBA側のプログラムで、
他ブックのファイル読み込み時に挿入され、実データとして格納されます。

以下本題。

sheet1のソート機能が実装され、学年、学級、出席番号の順に第一ソート、第二ソート、第三ソートの設定をし
sheet1の「X,X年,X組,GG00000,XX,織田信長,理系,64」の行の後に、
「X + 1,二年,一組,GG00005,05,織田信奈,文系64」を入れてソートをした場合、
sheet1では下記のようになります。
1,二年,一組,GG00001,01,岡部,文系,64
2,二年,一組,GG00002,02,橋田,文系,64
3,二年,一組,GG00003,03,栗栖,文系,64
4,二年,一組,GG00004,04,まゆり,文系,64
5,二年,一組,GG00005,05,織田信奈,文系64
6,二年,二組,GG00005,01,天音,理系,64
7,二年,二組,GG00006,02,ルカ子,理系,64




X,X年,X組,GG00000,XX,織田信長,理系,64

この時のsheet2のデータは、シート参照でデータを取ってきているので、
項目「No,学年,学級,学籍番号,出席番号,生徒氏名」がソートされ、部活、進路がソートされないまま残り置いてきぼりとなります。

この状態を解決すべく、「部活、進路」の項目に対して、左側の参照セルが変更された場合、
同じくソートの範囲に含めて移動させたいのですが、
sheet1にソート設定をしたのであり、sheet2にはソートをしたわけではないので、対応が難しいです。

ロジック的にはどのような実装が良いのか教えてください。
なお、sheet2の参照データについては、参照のまま、変更せずに対応したいと思います。

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答2

0

ベストアンサー

質問内容で、直接的な要望は理解できますが、
要望の背景は何なのでしょうか?

本当の要望が分かれば、簡単に解決できる可能性があり、
広くアドバイスを頂けると思います。

もちろん、背景 → 直面の問題 → 自分で考える解決方法
というような話の流れにはなり、細かい点も重要ですが、
問題を解決するには、背景も重要だと思います。

長くなりましたが、
ほぼ同じデータ(学年 ~ 生徒氏名が同じですよね?)が、
2シートに分かれている理由がよく分かりませんが、
Sheet1で、Sheet2の追加情報が見れれば良いのではないでしょうか?

解決方法の1つとして、Sheet1で、データの右隣りに、
VlookupでSheet2の情報(部活、進路)を表示すれば良いのでは?
(学籍番号がキーになるのでは? キーが無ければ、学年+組+学籍番号等で複合キーを作れるはずです)

投稿2018/02/08 06:31

ExcelVBAer

総合スコア1175

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

King_of_Flies

2018/02/08 06:41

コメントありがとうございます。 解決方法の一つとして提示いただいた方法ですが、 実際のデータをお見せできるわけでは無いので説明用に作成したデータです。 このシート情報は実際には項目数が100近くあり、 シート一つにまとめると、横にとても長くなってしまうため、 マスタとして使い分けをしていて、 生徒情報+マスタデータとして切り分けています。 たとえば、生徒の試験の点数は、 生徒得点シートというシートが用意され、 生徒ごとの得点をそのシートに記載。 生徒の教科ごとの授業出欠状態は 出欠状況シートが用意され、 生徒ごとに各科目の出欠状況を記載するような作りになっているため、 一つのシートにすべての情報を詰め込むことができません。
King_of_Flies

2018/02/08 06:47

背景としては、あらゆるシートは開発方針により生徒マスタから生徒情報を格納し、 そのシートの項目に対応するデータを入れてもらう。あるいは取り込むような作りになっています。 今までは、生徒が転校してきた場合は生徒マスタの一番下の行に新しく入ってきた生徒を入れるだけだったのですが、 ユーザが使用する際、クラス所属者が飛び石でマスタに登録されていると、 データを挿入する際、クラスごとに纏まっていないため、わかりにくいよね~という話から、 生徒のマスタをクラスごとに纏まるようソート機能を追加しました。 そして、他シートの参照問題がありますよね、っていう状態です。
ExcelVBAer

2018/02/08 06:51 編集

2つのシートに分けている理由は分かりました。 ソートする機能が追加された背景もわかりました。 しかし、「他シートの参照問題」とはなんでしょうか? なぜ用途の違うシート間で、ソート順を同期する必要があるのでしょうか?
ExcelVBAer

2018/02/08 06:59 編集

やっと理解できました。 Sheet2 の共通データ部分が、数式で =Sheet1!A1 のように、直接参照してるんですね。 単純に設計のミスで、ソートの問題ではないようですね。
King_of_Flies

2018/02/08 07:01

生徒マスタに対してデータを入れる。 ファイル出力を行う。 ここで出力されるファイルは、各教科の担当の教師にそれぞれ配られ、 生徒が何の科目で何点取ったか、欠席回数は?などの情報を入力する。 ファイル取り込み。 このファイル取り込みでは教師が入力したデータをsheetに反映していく。 成績一覧というシートがあり、そこに全生徒のそれぞれの科目の点数が格納される。 欠席一覧というシートがあり、そこに全生徒のそれぞれの教科ごとの欠席数が格納される。 過去成績というシートがあり、成績一覧のデータの一部項目を保存していく。(前期期末試験や、後期期末試験など、成績一覧は”今回”の結果のみを格納しているため、過去データとして残しておく必要がある。) ここで転入生が来たので、生徒マスタをソートします。 すると、成績一覧については、今回限りなので、次回分では影響がない。 出欠一覧についても同様。 過去成績一覧については、ソートの同期をしなければ、実データがずれてしまう。 というような状態です。
ExcelVBAer

2018/02/08 07:07

状況がようやく理解できてきました。 データベース的に考える場合、 キー(Key)が最重要項目なんですが、 生徒に該当するキーは何でしょうか?
King_of_Flies

2018/02/08 07:10

学年、学級、出席番号の三つでデータを一意にできます。
ExcelVBAer

2018/02/08 07:23

現時点で提示できる解決策は3つです。 ①上級者向け シート上のデータをデータベース化し、 各情報を分類毎にテーブルに分けて、 データベースに対して、追加・更新・削除を実行する  ②中級者向け ソート機能で、Sheet1をソートし、全シートにソートを疑似的に再現する。 具体的には、Dictionary を使い、シート毎に、Sheet1ソート前の情報を Dictionaryに格納(Key:学年+学級+出席番号、Item:行単位のデータ)しておき、 Sheet1ソート後に、シート毎にDictionaryに格納した情報を、再配置する。 ③初級者向け ソート機能で、Sheet1だけでなく、全シートをソートする。 具体的には、Sheet2 の参照数式(=Sheet1!A1等) をひとまず値貼付け等で値化し、 同じ列でソートを実施し、 その後、参照数式があったエリアに再度数式をセットする。 ※【注意】ただし、データ行数が常に完全一致していないと、データがズレるリスクがつきまといます。
King_of_Flies

2018/02/08 07:32

現状考えている手は②版ですかね。 データベース化は案件のコスト面にもかかわってくるので。 方針だけ見えてきました。 ありがとうございます。
ExcelVBAer

2018/02/08 07:36

なるほど。コスト面・スケジュール面も重要ですからねぇ。 個人的には、Dictionary は使い勝手がいいのでよく使います。 重複チェック、存在チェック、集計、DB的処理、動的配列、 色んな使い方があるので、これを機に使い倒してみてください~
guest

0

※解決済みとなっていましたが、参考情報として投稿させていただきます。


まず「sheet1をソートしたらsheet2の一部も連動してソートされる」というのは見た目だけの話で、実際ソートされているのとは違います。
実際、sheet1をソートしてもsheet2としては何も変わっていません。
sheet2のA1セルの中身は=Sheet1!A1という式のままです。
ただ、参照先のセルの値が変わったから表示内容が変わったというだけです。

当然、sheet1を参照していないセルにも変化はありません。


考え方として、まずsheet1は学生の基本情報を管理するマスタですよね。
マスタはマスタ(コードと情報を結びつけるもの)なので、どんな順番で記載されていようが本来あまり関係ないはずのものです。
そういう意味で、どちらかというとsheet2の方に問題があると思います。

おそらくsheet2も、立ち位置としては「学生の追加情報」を管理するマスタ的な存在なのではないかと思います。

しかしマスタとして重要な「キー情報」を自身では持っておらず、別シート参照になっています。
このため、マスタとしては動いてはいけないはずのキー情報が参照元の変更に釣られて動いてしまいます。
そういう意味で、キー情報と入力データの結びつけが不安定な状態と言えます。


ではどうするか?というと、一番楽なのはsheet1で全部管理することです。
しかし環境や要望がそれを許さないのであれば、sheet2の内容(キー情報と入力データ部の紐付け)を確定してあげればよいのではないでしょうか。

具体的には、シート切り替えのタイミングでsheet2の内容をsheet3に退避したり、sheet3の情報からsheet2に紐づけなおしたりといったことをすればできそうです。

①WorkbookのSheetChangeイベントでシート選択を監視する ②sheet1がアクティブになった際、sheet2の内容をsheet3にバックアップする(値の貼り付けをする) ③sheet2がアクティブになった際、キー情報の部分はセル参照しているのでsheet1と同じ並び順に変わっている。  この変更後のキー情報をもとにsheet3を検索し、「部活」「進路」の情報をsheet2に転記する。

こんな流れを作ってあげれば実現できるのではないでしょうか?

ひとつの案として、参考までに。

投稿2018/02/08 11:09

jawa

総合スコア3013

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問