エクセルで質問があります。

質問

エクセルで質問があります。
大文字と小文字で区別されている以下のデータを
まとめたいのですがどのような関数を使用すれば
いいのでしょう。

AAa　　11
aaa　　20
Aaa　　30

aaa　　11,　20,　30

aaaの部分はまとまってくれさえすれば

AAa　　11,　20,　30
Aaa　　11,　20,　30

でも構いません。

よろしくお願いします。

回答No.5

kagakusuki

回答番号:ANo.2、3です。
　Excelの関数とリストの並べ替え機能も併用する、半自動的な方法で、ANo.3よりも簡単、且つ早く計算出来る数式を考えつきました。
　まず、Sheet3のB2セルに

=IF(Sheet1!$A1="","",LOWER(Sheet1!$A1))

という数式を入力し、次にSheet3のA2セルに

=IF(ROWS($1:1)=1,1,IF(OR($B2="",COUNTIF($B$1:$B1,"="&$B2)>0),"",COUNT($A$1:$A1)+1))
という数式を入力し、次にSheet3のC2セルに

=IF(Sheet1!$B1="","",Sheet1!$B1&IF($B2=$B3,","&C3,""))

という数式を入力して下さい。
　次に、Sheet3のA2～C2の範囲をコピーして、Sheet3の3行目以下に（Sheet1に入力する予定の単語の数以上の行数になるまで）貼り付けて下さい。
　続いて、Sheet2のA2セルに

=IF(ROWS($1:1)>COUNTIF(Sheet3!$A:$A),"",VLOOKUP(ROWS($1:1),Sheet3!$A:$C,COLUNS($A:B)))

という数式を入力して下さい。
　次に、Sheet2のA2セルをコピーして、A列の2行目から、B列（A列ではない）の単語の種類数以上の行数になるまでの範囲にかけて、貼り付けて下さい。
　ここまでで、関数の入力は終了で、引き続いてSheet1のA列に単語を、Sheet1のB列にページ番号を、入力して下さい。
　そして、単語とページ番号を全て入力し終えてから、Sheet1のA列中で単語が入力されているセルの内、何れか一つをクリックして下さい。
　次に

メニューの[データ]ボタンをクリック
　　↓
現れた選択肢中の[並べ替え]をクリック
　　↓
現れた「並べ替え」ウィンドウの中の、「最優先されるキー」の欄をアルファベットにして、その右側にある「昇順」と記されている箇所にチェックを入れる
　　↓
「2番目に優先されるキー」の欄を数字にして、その右側にある「昇順」と記されている箇所にチェックを入れる
　　↓
[OK]ボタンをクリック

　すると、単語の列がスペル順に並べ替えられ、それに合わせてページ番号の列も入れ替えられ、同じスペルの単語のページ番号同士であれば、番号順に並べ替えられます。
　その後で、Sheet2を開けば、重複無しの単語がスペル順にA列に並び、B列のセルにページ番号が小さい順番に並びます。
　尚、この関数の場合は、ANo.3のものとは異なり、同じ単語のページ番号同士が昇順に並んでいない場合には、Sheet2のB列のセルの表示も、ページ番号順ではなくなりますが、その点は並べ替え操作のやり方でカバー出来ます。
　又、単語を追加する場合には、追加の単語とページ番号を全て入力してから、再度、並べ替えを行って下さい。
　それから、関数を使う方法が、フィルター等を使った方法と比べて優れている点は、少ない手動操作の回数で、ほぼ完成した状態のリストが得られるという点だと思います。

回答No.4

noname#164823

関数を使わないで、簡単にできる方法です。
「フィルタ」を使い、抽出された数字を別シートにコピーする方法で
いかがでしょう。

まず、

名称　　ページ
Apple 120
apple 150
beer 30
Beer 60
BEER 130
egg 10
EGG 130

のように、
1．セルの最上に「列見出し」を入れて下さい。
2．全体の文字を半角か全角に統一して下さい。
　もし、混じっている場合、補足して下さい。関数で一発で変換できます。
　大文字・小文字は混在OKです。
なるべく、空白セルを作らないで下さい。
3．この表を「リスト」といいます。作業を開始するとき、リスト内のどこでも
　いいので、1セルだけクリックします。これで、4．からリスト全体が選択されます。
4．ツールボタンのページ数のどれか１つのセルを選択し、「昇順」を押して下さい。
5．次に「データ」「フィルタ」「オートフィルタ」をクリック。
6．「列見出し」の右に▼が出ます（フィルタモードという）。
7．▼をクリックし、「オプション」を選択。
8．「オートフィルタオプション」のダイアログが出るので、
　「タイトル」欄に「a*」と半角で入力し（大文字・小文字無関係）、
　右側のボックスから「と等しい」を選択し、OK。
　「*」を入れる事により、aで始まるデータは全て抽出されます。
9．あらかじめシート2にでも[Apple]など、縦方向に表題を入力しておき、
　上の操作でAppleから抽出された数字を全て選択し、「コピー」→シート2の
　Appleの右隣のセルを1つ選択し「形式を選択して貼り付け」→下にある
　「行列を入れ替える」にチェックを入れて「OK」。
　これで、横方向に数字が並びます。

10．7.を繰り返し、次はBeerを同様に抽出、コピーします。

フィルタモードは5,の手順で「オートフィルタ」のチェックを
外せば解除できます。

回答No.3

kagakusuki

回答番号:ANo.2です。
　関数だけで自動計算するのではなく、Excelのリストの並べ替え機能も併用する、半自動的な方法ですが、それでも宜しいでしょうか。
　今仮に、Sheet1のA列に単語、B列にページ番号が並んでいる場合、Sheet3を作業用シートとして使用して、Sheet2のA列に単語リストが重複無しに表示され、同じ行のB列から右側のセルに、各単語のページ番号が、小さい数順に表示されるものとします。
　まず、Sheet3のA2セルに

=IF(Sheet1!$A1="","",Sheet1!$A1)

という数式を入力し、次にSheet3のB2セルに

=IF(Sheet1!$B1="","",Sheet1!$B1)

という数式を入力し、次にSheet3のC2セルに

=IF(ROWS($1:1)=1,1,IF(OR($A2="",COUNTIF($A$1:$A1,"="&$A2)>0),"",COUNT($C$1:$C1)+1))

という数式を入力し、次にSheet3のD2セルに

=LOWER($A2)

という数式を入力し、次にSheet3のE2セルに

=IF($A2="","",RANK($B2,OFFSET($B$1,SUMPRODUCT(($C$2:$C$65536<>"")*($A$2:$A$65536=$A2)*(ROW(E$2:E$65536)))-1,,COUNTIF($A$2:$A$65536,"="&$A2)),1))

という数式を入力して下さい。
　次に、Sheet3のA2～E2の範囲をコピーして、Sheet3の3行目以下に（Sheet1に入力する予定の単語の数以上の行数になるまで）貼り付けて下さい。
　続いて、Sheet2のA2セルに

=IF(ROWS($1:1)>COUNTIF(Sheet3!$C:$C),"",VLOOKUP(ROWS($1:1),Sheet3!$C:$D,2))

という数式を入力し、次にSheet2のB2セルに

=IF(OR($A2="",COLUMNS($A:A)>COUNTIF(Sheet3!$A:$A,"="&$A2)),"",SUMPRODUCT((Sheet3!$B$2:$B$65536),(Sheet3!$A$2:$A$65536=$A2)*(Sheet3!E$2:E$65536=COLUMNS($A:A))))

という数式を入力して下さい。
　次に、Sheet2のB2セルをコピーして、単語で最も多く重複しているものがリスト中に登場する回数を上回るのに充分な個数の、Sheet2のC2セルから右側にあるセルに貼り付けて下さい。（例えば仮に、リスト中に「aaa」という単語が7個あるのが最多で、その他の単語は7個以下しか登場していない場合には、C3～C8の範囲に貼り付けて下さい）
　次に、Sheet2の2行目中で数式が入力されているセルの全てを範囲コピーして、Sheet2の3行目以下に（Sheet1に入力する予定の単語の種類の数以上の行数になるまで）貼り付けて下さい。
　ここまでで、関数の入力は終了で、引き続いてSheet1のA列に単語を、Sheet1のB列にページ番号を、入力して下さい。
　そして、単語とページ番号を全て入力し終えてから、Sheet1のA列中で単語が入力されているセルの内、何れか一つをクリックして下さい。
　次に、[昇順で並べ替え]ボタンをクリックして下さい。
　もし、[昇順で並べ替え]ボタンが見つからない場合には、代わりにメニューの[データ]ボタンをクリックして、現れた選択肢中の[並べ替え]をクリックすると現れる、「並べ替え」ウィンドウの中の、最優先されるキーの欄をアルファベットにしてから、その右側にある「昇順」と記されている箇所にチェックを入れた後、[OK]ボタンをクリックして下さい。
　すると、A列が単語のスペル順に並べ替えられ、それに合わせてB列も（数字の順番とは関わりなく）並べ替えられます。
　その後で、Sheet2を開けば、重複無しの単語がスペル順にA列に並び、その右側のセルに左から順番に、ページ番号が小さい順に並びます。
　尚、この関数の場合は、Sheet1に単語のスペルのみが昇順で並んでいれば良く、同じ単語のページ番号同士に関しては、必ずしも昇順に並んでいる必要はありません。
　又、単語を追加する場合には、追加の単語とページ番号を全て入力してから、再度、並べ替えを行って下さい。

　それと、入力する度に自動計算が行われると時間を要しますから、計算方法の設定を手動に切り替えてから、単語を入力するのも一つの手だと思います。

回答No.2

kagakusuki

Excelのリストの並べ替え機能を利用されると良いと思います。

　まず、単語が入力されているセルの内、何れか一つをクリック
　　↓
メニューの[データ]ボタンをクリック
　　↓
現れた選択肢中の[並べ替え]をクリック
　　↓
現れた「並べ替え」ウィンドウの中の、「最優先されるキー」の欄を「A」にして、その右側にある「昇順」と記されている箇所にチェックを入れる
　　↓
「2番目に優先されるキー」の欄を数字にして、その右側にある「昇順」と記されている箇所にチェックを入れる
　　↓
[OK]ボタンをクリック

　すると、単語の列がスペル順に並べ替えられ、それに合わせてページ番号の列も入れ替えられ、同じスペルの単語のページ番号同士であれば、番号順に並べ替えられます。
　尚、単語のスペルのみに従って並べ替えて、同じスペルの単語のページ番号同士は、番号順にはしなくとも良い場合には、単語が入力されているセルをクリックしてから、「標準」バー中の[昇順で並べ替え]ボタンをクリックする、という方法もあります。

　それから、重複した単語を一つにまとめて、その右側にページ番号を順番に表示させる方法も、この後で回答致します。

回答No.1

Executione

う～ん、情報不足です。

データには、AAa、aaa、Aaa以外に、bbbやcccはないのですか？
なければ、ここはキー入力した方が早いですよね。

11,　20,　30

この並べ方は、「,」が必要なのですか？
それとも、1つのセルに、数値1つが入って、「,」はいらないのですか？
これでしたら、縦横変換で出来ます。

補足

説明不足ですいません。
用途としては索引に使用したいのですが、

単語の大文字と小文字で分かれてしまっているので
一つにまとめたいのです。
同じスペルの単語を検索してページ番号をカンマ区切りで
まとめる感じです。

ページ番号は1つのセルに入っていてもそうでなくても
構いません。

Apple 120
apple 150
beer 30
Beer 60
BEER 130
egg 10
EGG 130

　　　　↓
apple 120,150
beer 30, 60,130
egg 10,130

このようなイメージです。
よろしくお願いします。