どの関数を使えばよいですか？

質問

はじめまして。EXCELも駆け出しで
まだまだ関数の意味もわからないままやっているものなのでトンチンカンな事を書くかもしれませんがあらかじめご了承下さい。

　　　　売上　　
A県　　 10
B県　　 15
C県　　 　8
I地区　 32
D県 　 　12
E県 　 　10
II地区 　 22
F県 　　 18
G県 　　 10
H県 　　 12
III地区 　40

※A～C県の合計がI地区というふうになっています。
II、III地区も同様にD・E県合計、F～H県合計です。

すごく略して申し訳ございませんが上記のようなEXCELの形式を
使った場合、各県の売上の降順に（各地区は除く）別のシートに並べたいのですがどのような関数を使えばよろしいでしょうか？

今、各県名毎をctrlキーを押しながらまとめて左上の升目に名称をつけ
（各県の合計の地区が入らないように）同時に各県毎の売上も同様に名称をつけ、

=LARGE('週間販売計画（EXCEL名.xls'!売上,1)

というので売上数字の降順はかけられたのですが、連動して県名箇所に

=INDEX('EXCEL名.xls'!県名,MATCH(B11,INDEX('EXCEL名.xls'!売上1,0),1))

という関数をいれたところ一部は出たもののほとんどN/Aでした。
もっと簡単な方法があればよろしくお願いいたします。

回答No.8

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

#7です
＞出来ましたらsheet1をいじらずsheet2での方法をお教えできませんか？
Sheet2で作業列を作れば良いと思いますがダメなんでしょうか？
そして、関数を使わなければいけないのでしょうか？

Excel一般機能でできるものを関数で行うのは非常に大変です。
数式を使った方法です。作成後にSheet2へ切り取って貼り付けます。
D2:E12セルを選択して下記を数式バーに貼り付けます。

=IF(COUNTIF($A$2:$A$12,"<>*地区")<ROW($A$2:$A$12),"",
INDEX(A:B,MOD(LARGE(IF(RIGHT($A$2:$A$12,2)<>"地区",$B$2:$B$12*10^5+ROW($A$2:$A$12)),
ROW($A$1:INDEX($A:$A,COUNTIF($A$2:$A$12,"<>*地区")))),10^5),{1,2}))
セルへの入力は[Ctrl]+[Shift] ＋[Enter] で確定します
配列数式です({}で囲まれる)
数式の考え方は#1のKURUMITOさんと同じです。

回答No.7

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

DドライブのルートにTEST.xlsで保存した。
また、A1セルの項目名は「県名」とした
Sheet2で外部データの取り込み - 新しいデータベースクエリ
Excel Files
TEST.xlsを選択して MSクエリにて編集する
降順や抽出など 下記参考
＊＊＊＊＊SQL文＊＊＊＊＊
SELECT `Sheet1$`.県名, `Sheet1$`.売上
FROM `D:\TEST`.`Sheet1$` `Sheet1$`
WHERE (`Sheet1$`.県名 Not Like '%地区')
ORDER BY `Sheet1$`.売上 DESC
＊＊＊＊SQL END＊＊＊＊＊
Sheet2!A1へ書き出す
外部データ範囲のプロパティで「定期的に更新する」などチェックを入れる

回答No.6

rivoisu

＞出来たらsheet1の形状は変えなく、sheet2で県だけの降順を
かけたいのです。

だから最初にシート2にコピーしたじゃないか

回答No.5

WWolf

こんにちは。
発想を変えて関数を使わない方法を記載します。参考に・・・
仮にシート１に質問内容の表があったとして、シート２にはシート１を参照します。
例、
シート２
A1セル　=sheet1'A1
B1セル　=sheet1'B1
・
・
・
シート２にオートフィルタ
A列の条件は”*県”に”等しい”
B列は昇順

これで別のシートに質問者さんの意図する事が可能です。
質問を取り間違えていたらすいません。

お礼

図解まで入れていただきありがとうございます。

もう一歩つっこんで申し訳ございませんが、これはsheet1の数値が
変わったつどオートフィルターをクイックするのでしょうか？

sheeet1の数値を替えてもsheet2は何も触ることなく降順になれば
いいな～と思ったのですが無理なんですかね？

回答No.4

cistronezk

発想を替えてはどうでしょう。
お示しの表をデータから集計するのは何かと不便です。今回だけなく今後もまた問題が出てくるでしょう。
そこでこの際、回答３の方が提示された形式のものを本データにします。
そこから、お示しの表や他の集計表をつくるようにします。これによって現在そして今後も出てくるであろう負担が大いに軽減されます。

回答No.3

noname#235092

「地区名」と「県名」が同じ列に混在しているのが難しくしている原因と思われます。
もし作り変えられるようであれば下記のように作り直すとご希望の作業が簡単になります。

　　　Ａ　　Ｂ　　Ｃ　　Ｄ　　Ｅ　　Ｆ　　Ｇ　　Ｈ
１　　番号　地区　県名　売上
２　　１　　１　　Ａ　　１０
３　　２　　１　　Ｂ　　１５
４　　３　　１　　Ｃ　　　８
５　　４　　２　　Ｄ　　１２
６　　５　　２　　Ｅ　　１０
７　　６　　３　　Ｆ　　１８
８　　７　　３　　Ｇ　　１０
９　　８　　３　　Ｈ　　１２
１０
１１　　　　　　　　　　　　　　　　番号　地区　合計
１２　　　　　　　　　　　　　　　　１　　１　　３３
１３　　　　　　　　　　　　　　　　２　　２　　２２
１４　　　　　　　　　　　　　　　　３　　３　　４０
１５　　　　　　　　　　　　　　　　　　　合計　９５

これだとご希望の作業をするのにもう説明はいらないのでは？
そのまま番号ごと並べ替えればもとの順序にも戻せます。難しい関数は不要ですね。
参考にしてください。

なお、合計の「３３」「２２」「４０」のセルにはこの場合実際は下記の関数をそれぞれ入力します。自動でその地区の合計を計算してくれます。
=SUMIF(B2:B9,"1",D2:D9)
=SUMIF(B2:B9,"2",D2:D9)
=SUMIF(B2:B9,"3",D2:D9)

※数字は表示がずれないように全角を使いました。

お礼

ありがとうございます。
県名と地区名は固定されて変更することが出来ないので
別シートで県名の売上降順が出来る方法があれば伝授していただけないでしょうか？

回答No.2

rivoisu

まずシート１をシート2にコピーします。
一番左に１列挿入します。A列とします
その列に
=if(right(B2,2)="地区",9,0)　　　先頭が２行目の場合
｛B2の右２文字が"地区”　ならば　9　それ以外は　0｝

として一番したまでコピーします。ドラッグフィルでも
こうするとA列に県の場合は0、地区の場合は9という数字が入ります。
Aをキーにソートすると地区がまとめて一番下に固まるはずです。
これを削除してA列を削除したら元の表から地区を除いた表ができます。
これを売上で降順ソートしたらどうです。

お礼

ありがとうございます。
出来たらsheet1の形状は変えなく、sheet2で県だけの降順を
かけたいのです。

我がままで申し訳ございません。

回答No.1

KURUMITO

作業列を作って対応するのがよいでしょう。
シート1ではＡ列に県名や地区名が、また、Ｂ列には売上高がそれぞれ2行目から下方にあるとします。
Ｃ2セルには次の式を入力して下方にオートフィルドラッグします。
=IF(OR(A2="",ISNUMBER(FIND("地区",A2))),"",B2-0.0001*COUNTIF(B$2:B2,B2))
この式では同じ売上高があっても問題なく処理できるようにしています。
Ｄ2セルには次の式を入力して下方にオートフィルドラッグします。
=IF(C2="","",RANK(C2,C:C))
シート2にはお望みの表を作るとします。
Ａ2セルには次の式を入力しＢ2セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。
=IF(OR(COUNTIF(Sheet1!$D:$D,ROW(A1))=0,COLUMN(A2)>2),"",INDEX(Sheet1!$A:$B,MATCH(ROW(A1),Sheet1!$D:$D,0),COLUMN(A2)))
これで、売上高の降順に並んで表示されます。

補足

すぐのご回答ありがとうございます。
僕にはとても難しい関数です（涙）

出来ましたらsheet1をいじらずsheet2での方法をお教えできませんか？