[[20100929074541]]　『フリガナでアイウエオ昇順に、空白行を下に並べ替』（う）　ページの最後に飛ぶ

『フリガナでアイウエオ昇順に、空白行を下に並べ替えする方法』（う）
Excel2000　Windows2000

いつもお世話になります。下記のような表があります。

    A       B       C       D        E
 1　名前　ﾌﾘｶﾞﾅ　商品１　商品２　　合計
 2　永　　ｴｲ　　　１　　　　　　　　１
 3　微意　ﾋﾞｲ　　　　　　　２　　　 ２
 4　志井  ｼｲ　　　１　　　 １　　　 ２
 5

これを

    A       B       C       D        E
 1　名前　ﾌﾘｶﾞﾅ　商品１　商品２　　合計
 2　永　　ｴｲ　　　１　　　　　　　　１
 3　志井  ｼｲ　　　１　　　 １　　　 ２
 4　微意　ﾋﾞｲ　　　　　　　２　　　 ２
 5

と並べ替えたいのですが、表全体を選択し、B列を昇順で並べ替えすると

    A       B       C       D        E
 1　名前　ﾌﾘｶﾞﾅ　商品１　商品２　　合計
 2　
 3　永　　ｴｲ　　　１　　　　　　　　１
 4　志井  ｼｲ　　　１　　　 １　　　 ２
 5　微意　ﾋﾞｲ　　　　　　　２　　　 ２

となります。
現在は一度表全体を降順で選択したあと、入力済みの部分だけを選択しなおして再度B列で昇順並べ替えしているのですが、実際は数百のデータで日々何度も繰り返す作業なので、なんとか簡単にならないかと考えています。よいお知恵はございませんでしょうか？
よろしくお願いいたします。

 このデータ、数式で文字列0を入れたものを値だけペーストか何かで持ってきたものですか？
 つまり
 =IF(A2="","",A2)
 みたいに""を使ってるのでしょうか？

 とりあえず簡単そうな方法としては
 昇順の基準となるB列のみを選択。
 「データ」「区切り位置」を開いて「次へ」「次へ」「完了」
 としてから並び替えてみてください。

 （すずめ）

 長さ０の文字列（""）が入っているのですね。

 VBAで手作業と同じことを処理してみてはどうでしょうか？

  Sub test()
  With Columns("B")
    .CurrentRegion.Sort Key1:=.Cells(1), Order1:=xlDescending, Header:=xlGuess, _
          OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
          SortMethod:=xlPinYin, DataOption1:=xlSortNormal
    Set myR = .Find("*", .Cells(1), xlValues, xlPart, xlByRows, xlPrevious, False, False, False)
    Set myR = Application.Intersect(.CurrentRegion, Range(.Cells(1), myR).EntireRow)
    myR.Sort Key1:=.Cells(1), Order1:=xlAscending, Header:=xlGuess, _
          OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
          SortMethod:=xlPinYin, DataOption1:=xlSortNormal
  End With
  End Sub

 （ｍｏｍｏ）

 ご回答有難うございます。
 あいにく操作書とくびっぴきで作業している身のため""がブランクだということしかわからないのですが、空白行につきましては未入力の欄です。おそらくmomoさまのおっしゃっている「長さ0の文字列""」なのだと思います。
 早速ただいまよりお二人のおっしゃられている方法を試してみたいと思います。（う）

 もしかしたら。B列にはPHONETIC関数が入っているって事はないですか。
 そうであれば、A列で並び替えすればよさそうな気がしますが。

 （川野鮎太郎）

 ご相談にのっていただいてありがとうございます。
 いままでmomoさまとすずめさまのお勧めいただいた方法を実施しようと解読を頑張っていたのですが、
 知識不足でまだ思い道理に表を動作させることができていません。
 せっかくお知恵を貸して頂いているのに、申し訳ございません。

 すずめさまのおっしゃるとおりにしてみたのですが、変化はありませんでした(^^;)。
 区切り位置の意味について勉強中です・・・。

 momoさまのVBA組み立てていただいてありがとうございます。
 実際の表が例としてあげさせていただいた表とすこし違うので、解読してみようと頑張っているのですが、
 まだ途中です・・・申し訳ございません。

 川野さま、PHONETIC関数とはなんぞやと検索してみましたが「ふりがなの文字列を取り出します」とのこと
 「？？？」なので帰宅時に本屋さんで初心者用解説本をさがしてみます(^^;)。
 私が入力しているのは普通に入力してF7で半角カタカナにしているだけなので、
 おそらくそのような関数を使っていないと思いますが・・・。

 ご相談にのっていただいているにもかかわらず、あまりにも能力がおよばず・・・な状態ですので、
 丸なげは恥ずかしいと思って簡単にしていた例の表につきまして、ありのままを↓にかかせていただきます。

    A　　　　B　　　　C　・・・・・・・　O　　　　P　　　　Q　　　　R
 1　　　　　商品１　商品２　　　　　　　商品14　 合計
 2 予約総数
 3 予約残　
 4
 5 名前　   商品１　商品２　　　　　　　商品14　 合計　  ﾌﾘｶﾞﾅ　　　℡
 6　永　　　　　　　　1　　　　　　　　　　　　　　1　　　ｴｲ　　　　　1234　　　
 7　志井　　　2                                 2      ｼｲ　　　　　5678
 ・
 ・
 200くらい

 2シートで構成,
 A1からP3まではA5以下の表の合計等が関数で集計されるようになっています。
 A5以下のA列は名前を入力規則で、別シートの名簿に入力した名前が選択できるようにしています。
 B列からO列は商品の注文数を入力しています。
 P列はB～O列の合計をするようsumで集計しています。
 Q列はﾌﾘｶﾞﾅをVLOOKUPを使用して、別シートからA列のﾌﾘｶﾞﾅを検索するようにしています。
 R列も同様にVLOOKUPを使用しています。
 文字列の設定などは特にしていません。
 合計のところだけ表示形式を「数列」にしました。
 希望はA5以下の表に対して、A6以下を並べ替えする事です。

 精進します！（う）

 そのＱ列を基準にソートしたいのですよね？
 そのＱ列のVLOOKUPの式をかけますか？
 こんな風になってませんか？

 =IF(A6="","",VLOOKUP(A6,Sheet2!$A$1:$B$10,2,0))

 私の「区切り位置」の方法は対象が計算式ではダメです。

 上の式はＡ列が空白なら空白を返すようになっています。
 この""ですが、これは完全な空白ではなく、「文字列0」の文字列が入るのです。
 そのため、ソートすると一番上にきます。
 つまり
 数値の昇順　→　””　→　文字の昇順　→　エラー値
 となるのです。

 これを下にもっていくには
 １：エラー値にして条件付書式でエラー値の文字色を白にする
 ２：フリガナが全部半角カタカナなら、””の代わりに"一"＜漢字の１を入れる
 等の対策が必要です。

 どちらがよいですか？
 （すずめ）

 実際の表に手直しするとこんな感じですね。
 違っている所を見比べてみると応用が利くようになります。

  Sub test()
  With Columns("Q")
    .Cells(5).CurrentRegion.Sort Key1:=.Cells(1), Order1:=xlDescending, Header:=xlYes, _
          OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
          SortMethod:=xlPinYin, DataOption1:=xlSortNormal
    Set myR = .Find("*", .Cells(1), xlValues, xlPart, xlByRows, xlPrevious, False, False, False)
    Set myR = Application.Intersect(.Cells(5).CurrentRegion, Range(.Cells(5), myR).EntireRow)
    myR.Sort Key1:=.Cells(5), Order1:=xlAscending, Header:=xlYes, _
          OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
          SortMethod:=xlPinYin, DataOption1:=xlSortNormal
  End With
  End Sub

 （ｍｏｍｏ）

 同じ結果を求めるにも、いろんな方法があるのですね！
 よくばりに両方勉強してみたいなあと思いました。

 すずめさま、おっしゃるとおりの数式がQ列に入力されています。
 =IF(A6="","",VLOOKUP(A6,Sheet2!$A$1:$E$500,5,false))
 区切り位置を調べてみました。
 一つのセルに入っている情報を特定の条件の場合２つに分けることができる方法･･･こんな機能があったのですね！
 これがどのように今回の件に適用できるのか、硬い脳みそで悩んでいます･･･(^^;)。
 空白欄につきましては、「印刷のときに空白に見える」ほうが良いので、それですと･･･
 すずめさまのご提案の「１」でしょうか？
 大変お手数をおかけしますが、ぜひご教授願います。

 momoさま、改めてご再考頂き、本当にありがとうございます。
 みつめてみつめて、久しぶりに学生気分で英訳をしているような気分になりました。
 こんなかんじだとぼんやり認識しました･･･。
 表にマクロをはりつけるとびしっと変更になって魔法をみているようで感激しました！

 Sub test()
  With Columns("Q") ←Q列を選びます
    .Cells(5).CurrentRegion.Sort Key1:=.Cells(1), Order1:=xlDescending, Header:=xlYes, _
          OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
          SortMethod:=xlPinYin, DataOption1:=xlSortNormal　
 ↑5行目からひとまとまりの表をA列で降順並べ替えしてください

    Set myR = .Find("*", .Cells(1), xlValues, xlPart, xlByRows, xlPrevious, False, False, False)
 ↑「何か文字列のはいっているA列（？）の最後」をmyRと設定します

    Set myR = Application.Intersect(.Cells(5).CurrentRegion, Range(.Cells(5), myR).EntireRow)
 ↑A5からmyRまでの範囲をひとかたまりとします

    myR.Sort Key1:=.Cells(5), Order1:=xlAscending, Header:=xlYes, _
          OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
          SortMethod:=xlPinYin, DataOption1:=xlSortNormal
 ↑その範囲を昇順で並び換えします。

  End With
  End Sub

 ご対応くださって本当にありがとうございます（う）

 コメントを見るとかなり調べて勉強されたようですね^^

 > Set myR = .Find("*", .Cells(1), xlValues, xlPart, xlByRows, xlPrevious, False, False, False)
 >↑「何か文字列のはいっているA列（？）の最後」をmyRと設定します

 > Set myR = Application.Intersect(.Cells(5).CurrentRegion, Range(.Cells(5), myR).EntireRow)
 >↑A5からmyRまでの範囲をひとかたまりとします

 ここだけ少し解釈が違うので補足します。

 最初のは
 .Find　と、Findメソッドの前に「.」が付いていますね？
 これはWithステートメントのオブジェクトを継承しますので
 Q列の値が入っている最終セルを検索している事になります。
 たぶん.Cells(1)でA列と思われたと思いますが、
 これも頭に「.」が付いているのでColumns("Q").Cells(1)という事になりますから
 Q1からxlPreviousなので逆方向つまり上方向に検索しますのでQ列の最後のセルになります。

 次の行も.Cells(5).CurrentRegionつまりQ5からひとかたまりの値が入っているセル範囲なので
 A1:R最終行　までの範囲です。
 Range(.Cells(5), myR).EntireRowなのでQ5からmyRつまりQ列最終の値があるセル（""を除く）までの
 セル範囲のEntireRowなので行全体です。
 最初のCurrentregionは""のセルも含んでしまいますので、このままでは都合が悪いので
 この２つのセル範囲が重なっている部分（Intersect)の範囲を対象としてmyRにセットします。

 そうするとA5からR列の""を含まない値があるセル範囲を取得出来るので再度昇順でソートします。

 という感じです。
 これだけご自身で調べられるのでしたら、ＶＢＡを使いこなせるようになるのも相当早いと思います。
 ＶＢＡを覚えると色々な世界が広がってたのしいですよ^^　頑張ってください。
 （ｍｏｍｏ）

 それでは区切り位置の話からしますね。
 区切り位置は本来、特定の条件（カンマやスペース等）で区切られたデータを、その区切りで複数セルに分けることを目的とした機能です。
 その際、区切られたデータが正しいデータとなるよう、区切り位置ウィザードの3/3の画面で、データの形式を設定できるのです。

 たとえば

 001,エクセルの学校

 というデータがあった場合、この001をExcelは数値、つまり「1」だと認識します。
 でも実はこれはシリアル番号で「001」でないと意味が変わる場合がありますよね。

 そこで、3/3の画面で、その列がどんな形式のデータか、指定することが出来るのです。
 「001」の場合は「文字列」に指定してやれば、無事に「001」になります。

 最初に書きました「区切り位置」の機能はコレを利用し、見えない空白（文字列の長さが0）をなくしてしまおう、というものなのです。

 試してみてください。
 まずは今回のデータのＱ列をコピーします。
 新規ファイルをつくり、Ａ列を選択。「編集」メニューから「形式を選択してペースト」を選び、その中の「値」をクリックして「ＯＫ」します。
 こうすると数式ではなく、その結果の値だけがペーストできます。
 このＡ列を並べ替えすると、空白セルが上にきます。セルの中を確認しても数式も文字もなにも入っていないようにみえるのに、です。

 ここで、Ａ列に「区切り位置」をします。
 本来の機能である区切りは、なにも区切る必要は無いのでそのまま3/3まで行きます。
 すると「列のデータ形式」というのがあり、デフォルトでは「Ｇ/標準」になっていると思います。
 そして「表示先」がＡ列になっているはずです。
 これで「完了」を押すと、excelはデータを区切り（区切るものが無いですけど）、指定されたデータの形式に変換します。
 （最初に「次へ」「次へ」「完了」してください、といったのは、デフォルトの設定で変更する必要が無いからです）
 「Ｇ/標準」という形式は「数字は数値に日付は日付形式の値にその他の値は文字列に」してくれるのです。
 つまり、実は反対に「001」（数字）は「1」（数値）にしたかった場合は「区切り位置」を行うと「1」に変換してくれます。
 そして「長さが0の文字列」は「空白」にしてくれます。なにもない、と認識をしてくれるのです。

 他には並べ替える前に空白部分を選択してクリアすればいいとか、いろいろありますが、「区切り位置」では１列限定ですけど、
 その列にどれだけ「長さが0の文字列」があっても変換してくれるので、便利です。

 というわけで、対象が数式ではなく文字列であるならば、区切り位置が便利だよ、という話でした。

 さて、前置きが長くなりましたが本題へ。

 まずはＱ列の数式を、Ａ列が空白の場合はエラー値を返すように変えます。

 =VLOOKUP(A6,Sheet2!$A$1:$E$500,5,false)

 にして、数式の範囲へフィルコピー（全部つながって数式が入っているならば、セルを囲む黒い枠線の右下部分をダブルクリックでも行きます）
 これでエラー値（#N/A）が返るようになります。

 さて次に、コピーした範囲そのままの状態で（Q6が白い状態で選択され、今の数式を入れたセル全部も選択した状態で）
 「書式」メニューの「条件付き書式」をクリック。
 出てきた画面の「条件 1」ボックスの一覧の左にあるプルダウンをクリックして「数式が」にします。
 そして出てきた右側の入力欄に

 =ISERROR(Q6)

 と入れ、右下の「書式」ボタンをクリック。
 出てきた画面の「フォント」タブにて「色」を「白」にし「ＯＫ」。さらに「ＯＫ」して条件付書式を閉じます。

 これでずらずらっと出ていたエラー値#N/Aが見えなくなるはずです。

 注意点としては見えないだけで実際はエラー値が入っています。
 ので、セルに色をつけると白い文字が見えてしまいます（笑）

 個人的にはｍｏｍｏさんのＶＢＡがオススメです。
 数式を変えることなく、セルに色をつけても問題なく、他のファイルに値だけペーストしてもエラー値が見えたりしません（笑）
 他にデータを転用する可能性があるのであれば、絶対にＶＢＡが良いと思います。

 でもこの方法も応用が利きますので、良ければ記憶の片隅においておいてやってくださいね。
 （すずめ）

 反応が大変遅くなってしまい、失礼をいたしました。
 夜遅くまで、親切に対応してくださってありがとうございます。

 momoさま、そうなのですか！
 Intersectが重なる部分、とあったので、これがなんのためにあるのだろうと悩んでいました･･･ｗｗ
 詳しく解説くださってありがとうございます。
 おかげで理解しきれないなりにイメージをすることができました！
 そのためのIntersectだったのですね！
 withステートメントについてもしらべて、ベンリなものがあるものだなあと感心しました。
 昨日英訳のようだと申し上げましたが、VBAはまさに「英単語」ならぬ、関数もろもろの引き出しがたくさんあるもの勝ちですね。
 cells()の（）内になぜ数字が一つしかないのかとうんうん唸りながらネットを検索していたのですが、
 ご説明くださったおかげでとてもすっきりいたしました。

 すずめさま、なるほど･･･。
 区切り位置、をどのように行っているかご存知であったからこその応用だったのですね！
 最初に私が単純に申し上げた「空白」が本当にただの「空白」であるか「長さが0の文字列」であるか
 それとも「関数がこっそりはいっている空白」であるかが重要であったわけがわかりました！
 まことに勝手ながら本日は仕事がいそがしくじっくり勉強できなかったので、
 教えていただいた方法は後日改めて試してみたいと思います。

 このたびは親切にご教授くださり、本当にありがとうございました。（う）

コメント返信：

[ 一覧(最新更新順) ]

[[20100929074541]] 『フリガナでアイウエオ昇順に、空白行を下に並べ替』（う） ページの最後に飛ぶ

[[20100929074541]]　『フリガナでアイウエオ昇順に、空白行を下に並べ替』（う）　ページの最後に飛ぶ