EXCELの関数を使った集計

質問

EXCELの関数についてご教授ください。

１年｜○○裕貴｜男｜３年｜▽▽杏香｜女｜
２年｜◇◇秀樹｜男｜＿＿｜＿＿＿＿｜＿｜５年|☆☆りか｜女
＿＿｜＿＿＿＿｜＿｜３年｜△△果菜｜女｜
＿＿｜＿＿＿＿｜＿｜＿＿｜＿＿＿＿｜＿｜６年｜□□正進｜男

このようなシートがあります。(|はセルの区切り、_は空のセル）
これを↓のように関数を使って男女別、学年順に
並べ替えたいです。

1年|○○裕貴|男|3年|▽▽杏香|女|
2年|◇◇秀樹|男|3年|△△果菜|女|
6年|□□正進|男|5年|☆☆りか|女|

空白のセルの扱いやある範囲にランダムにある値を
並べ替えるのに
いろいろネットで調べてみましたがうまくいきません。
地道にコピペするのも手ですが量が多いので
大変困っております。どうかよろしくお願いいたします。

回答No.4

cistronezk · ベストアンサー

マクロを知らなくてもある程度は自動化できます。以下の手順をマクロ記録してください。

（１）
各シートの最大行を５０行とします。
D１：F50の範囲を選択し、Ctrl＋「+」で「下へシフト」します。
G１：I100の範囲を選択し、Ctrl＋「+」で「下へシフト」します。
J１：L150の範囲を選択し、Ctrl＋「+」で「下へシフト」します。
・・・
＃というように、列のあるところまで、５０行ずつ増やして下へシフトしていきます。
＃もし最大行が１００行なら１００行ずつ増やします。

（２）
セルA1を選択してから、Ctrl+Gで「ジャンプ」画面を表示し「セル選択」の選択オプションで「空白セル」をチェックします。
Ctrl+「-」で「左方向にシフト」して空欄を削除します。
＃これで、すべてのデータがA列からC列にまとまります。ただしこのままでは途中に空欄があります。

（３）
セルA1を選択してから、Ctrl+「-」で「上方向にシフト」します。
＃これで、空欄なしにA列からC列にデータがまとまります。

以上をマクロで記録しておきます。
全シートでこのマクロを実行すれば、A-C列にデータまとまります。
各シートでA-C列にまとまったデータを１シートにまとめるのは、手作業となります。
まとめたら、ソートします。第1キーは性別、第2キーは学年を指定します。
女性の部分をD-F列に移動します。

以上です。

お礼

マクロの記録ですね。もともとの表から抽出されるのが
このような面倒な表になっています。
試してみます。
ありがとうございました。

回答No.3

cistronezk

なんらかの規則か制約があってのことでしょうが、元の名簿が不自然な並び順ですよね。集計用データとして活用するには非常に不便です。
ですのでご希望の形に集約したい場合は、特殊なマクロが必要になります。

元データを、学年、氏名、性別の３列（たとえば、他の属性が必要なら別の３列を使うのではなく列を追加して「所属クラブ」とか入れる）だけを使って縦にずらっと並べる形式にしておいた方が、いろんな集計をするのに格段に楽になります。
以上、ご参考までに。

回答No.2

KURUMITO

関数を使うというよりも並べ替えなどの操作をすればよいでしょう。
初めに最初に1行を追加し、Ａ1セルに学年、Ｂ1セルに氏名、Ｃ1セルに性別とでも入力します。
その後にＤ，Ｅ，Ｆ列を選択してコピーし、Ａ列の行のデータがなくなった行のセルをアクティブセルにして「貼りつけ」を行います。同様にＧ，Ｈ,I列を選択してコピーし、Ａ列の行のデータがなくなった行のセルをアクティブセルにして貼り付けます。
ＤからＩ列までを選択して「Ｄｅlete」します。
その後にＡ1セルからデータの入った最後の行のＣ列のセルを範囲として選択し、「ユーザー設定の並べ替え」を選択します。
「先頭行をデータの見出しとして使用する」にチェックをしてから、最優先されるキーに「性別」を選んで「降順」に設定します。次に「レベルの追加」で「学年」を選んで「昇順」に設定してＯＫします。
男で学年順に、女で学年順に並びますので、女の関連データを選択して「切り取り」し、Ｄ２セルをアクティブセルにして貼りつけをすればよいでしょう。

お礼

はやり、手作業が入るのですね。
もともとが変な並びの表なので仕方ないですね。
ありがとうございます。

補足

補足です。
このようなリストが複数の列にまた、複数のシートにあります。
それをひとつのシートにまとめて男女別、学年順に集計したいです。
名簿なのでたびたび変更がはいりますのでそのたびに手操作は
避けて、各シートの指定された範囲に名簿を追加・修正すれば自動で
男女別、学年順のリストができるようにしたかったのです。
ですので関数で組んでおけばその範囲に追加・修正された名簿が
勝手に修正されるようなものを組んでおきたいのです。
情報不足ですいません。マクロを使わないと無理でしょうか？

回答No.1

某HN クロメート(Chromate)（@CoalTar）

関数でやる必要性がわかりません
3列分が一組として3組だけでしょうか？

[Ctrl]+[G]ジャンプ [セル選択] ●空白セル
[Ctrl]+[-]セルの削除 ●上方向にシフト
あとはカット＆ペーストでデータベースとして扱える形式にする
並び替えと抽出でコピペ

補足

補足です。
このようなリストが複数の列にまた、複数のシートにあります。
それをひとつのシートにまとめて男女別、学年順に集計したいです。
名簿なのでたびたび変更がはいりますのでそのたびに手操作は
避けて、各シートの指定された範囲に名簿を追加・修正すれば自動で
男女別、学年順のリストができるようにしたかったのです。
ですので関数で組んでおけばその範囲に追加・修正された名簿が
勝手に修正されるようなものを組んでおきたいのです。
情報不足ですいません。マクロを使わないと無理でしょうか？