Excel（エクセル）での指定した並べ替えと表わけ

質問

まず
Excelでの
指定した並べ替えを自動的にする事は可能でしょうか？
例えば図１のように
５０音順ではなく
りんご→みかん→スイカ→モモの順番で並べ替えたいです。
この時、行ごとの並べ替えをしたいです。

次に図２のように
種類ごとの売り上げがあった件数と無かった件数を数えたいです。
「文字列」や「空白」は無かった件数として数えたいです。
（セルＦ２～Ｇ５に入る関数式を教えて欲しいです。）

最後に
種類＆値段ごとの合計数が知りたいです。
（セルＢ２～Ｅ５に入る関数式を教えて欲しいです。）

今まで手入力をしていたのですが、
何度も何度も数え間違いや合計間違いをしてしまい
上司には怒られ呆れられ
関係者へは迷惑をかなりかけました。

もし手入力せずに済む
上記のようなことが可能であるならば、ぜひ教えてください！！
よろしくお願い致します！！

回答No.1

keithin · ベストアンサー

Excel2007以降を使っているならCOUNTIFS、SUMIFS関数で求められますが、何を使っているのですか。

とりあえず次の通り作成します。

手順１．
ツールメニューのオプションの「ユーザー設定リスト」で、あなたの思っている並び順の通りに

りんご
みかん
スイカ
モモ

と登録します

手順２．
表全体をまず値段の昇順で並べ替え、続けて種類の昇順で、並べ替えのオプションで登録したユーザー設定リストを指定して、並べ替えます

手順３：
シート１に元のリスト（並べ替える前でもあとでも）があるとして。
シート２に図２の左の表を、縦項目と横項目をキチンと用意して。

Ｇ２には
=SUMPRODUCT((Sheet1!$A$2:$A$100=A2)*(Sheet1!$C$2:$C$100=""))
Ｆ２には
=COUNTIF(Sheet1!A:A,A2)-G2
それぞれ下にコピー

Ｂ２には
=SUMPRODUCT((Sheet1!$A$2:$A$100=$A2)*((Sheet1!$B$2:$B$100=B$1)*Sheet1!$C$2:$C$100+(Sheet1!$D$2:$D$100=B$1)*Sheet1!$E$2:$E$100))
右にコピー、下にコピー。

＃補足
「種類の一覧」や「出てくる値段の一覧」は、エクセルの機能を使いたとえばデータメニューのフィルタの「フィルタオプションの設定」で「重複を無視する」を利用して収集し、漏れの無いようにリストアップします。
ネットで「フィルタオプションの設定」で検索して、使えるようになってください。

お礼

私のパソコン内のExcel2007でやってみたらできました！
丁寧な手順も書いていただき、とても参考になりました
ありがとうございました。

補足

さっそくの回答をありがとうございます。
同僚に確認したところ、Excel2003とのことでした。

回答No.2

KURUMITO

すべての答えを出すには式がたくさんになって大変ですが一度こちらが述べる通りで試験してみてください。
図1の表がシート1に有るとします。
指定する通りの果物の名前や順序はデータベースとしてシート3のＡ2セルから下方に入力します。
そこで作業になりますがシート1には作業列を設けＦ2セルには次の式を入力して下方にドラッグコピーします。

=IF(A2="","",MATCH(A2,Sheet3!A$2:A$500,0)*10000+IF(ISNUMBER(B2),B2,IF(B2="",9000,9500)))

G2セルには次の式を入力して下方にドラッグコピーします。

=IF(OR(A2="",D2=""),"",MATCH(A2,Sheet3!A$2:A$500,0)*10000+D2)

そこでお求めの表はシート2に表示させることにします。
シート2での作業は次のようにします。
Ａ1セルからＥ1セルにはシート1と同じ項目名を入力します。
Ａ2セルには次の式を入力してＥ2セルまでドラッグコピーしたのちに下方にもドラッグコピーします。

=IF(ROW(A1)>COUNT(Sheet1!$F:$F),"",IF(INDEX(Sheet1!$A:$E,MATCH(SMALL(Sheet1!$F:$F,ROW(A1)),Sheet1!$F:$F,0),COLUMN(A1))=0,"",INDEX(Sheet1!$A:$E,MATCH(SMALL(Sheet1!$F:$F,ROW(A1)),Sheet1!$F:$F,0),COLUMN(A1))))

シート1のデータがシート3で指おまけねだんが定した果物の名前順に表示されます。
つぎにお求めの表ですが例えばＩ1セルから１０００、500、350、20、売り有り件数、無し件数とＮ1セルまでに入力します。
Ｈ2セルには次の式を入力して下方にドラッグコピーします。

=IF(Sheet3!A2=0,"",Sheet3!A2)

Ｉ2セルには次の式を入力してＫ2セルまでドラッグコピーしたのちに下方にもドラッグコピーします。

=IF(ISERROR(INDEX(Sheet1!$C:$C,IF(COUNTIF(Sheet1!$F:$F,ROW(A1)*10000+I$1),MATCH(ROW(A1)*10000+I$1,Sheet1!$F:$F,0),MATCH(ROW(A1)*10000+I$1+1,Sheet1!$F:$F,0)))),"",INDEX(Sheet1!$C:$C,IF(COUNTIF(Sheet1!$F:$F,ROW(A1)*10000+I$1),MATCH(ROW(A1)*10000+I$1,Sheet1!$F:$F,0),MATCH(ROW(A1)*10000+I$1+1,Sheet1!$F:$F,0))))

Ｌ2セルには次の式を入力し下方にドラッグコピーします。

=IF(ISERROR(INDEX(Sheet1!$E:$E,MATCH(ROW(A1)*10000+L$1,Sheet1!$G:$G,0))),"",INDEX(Sheet1!$E:$E,MATCH(ROW(A1)*10000+L$1,Sheet1!$G:$G,0)))

Ｍ2セルには次の式を入力して下方にドラッグコピーします。

=IF(H2="","",COUNTIF(Sheet1!$F:$F,">"&ROW(A1)*10000)-COUNTIF(Sheet1!$F:$F,">="&ROW(A1)*10000+9000))

Ｎ2セルには次の式を入力し下方にドラッグコピーします。

=IF(H2="","",IF(COUNTIF(Sheet1!$F:$F,ROW(A1)*10000+9000)=0,"",COUNTIF(Sheet1!$F:$F,ROW(A1)*10000+9000)))

これでお求めの表は完成しますが売りの値段が１000,500,350の他にある場合でしたらその数値をＫ1セルの横に並べてＩ2の式をその列までドラッグコピーする操作をすればできるようになります。その場合には最初のＬ列からＮ列は右にずれることになります。同様に20円の他にもおまけの値段が有るでしょう。その時には20の横にそれらの値段を並べ、Ｌ2への入力した式をそれらの列まで横にドラックコピーすればよいでしょう。
すべてが関数で処理されます。並べ替えなどの操作を必要としません。

お礼

本当に魔法のような式を丁寧に書いていただき
ありがとうございました。
よくわからないながらも
書いていただいた文を手順通りにコピペしてみました。
ありがとうございました。