[[20220626233537]]　『sort関数以外の関数で整列させたい』（simo）　ページの最後に飛ぶ

『sort関数以外の関数で整列させたい』（simo）

     [A] 　[B]   [C] 
  1  数   名称　支店
  2   1    肉   青森
  3 　2    魚   東京
  4 　3    肉   愛知
  5 　4    米   新潟
  6 　5    肉   東京
  7 　6    魚   大阪

上記から

    [E] 　[F]   [G] 
  1  数   名称　支店
  2   1    肉   青森
  3 　3    肉   愛知
  4 　5    肉   東京
  5 　2    魚   東京
  6 　6    魚   大阪
  7 　4    米   新潟

のように関数を使用して名称順に整列させることはできるでしょうか？
ちなみにsort関数が使用できないので
それ以外の関数でご教授いただければ幸いです。

＜使用 Excel：Excel2011(Mac)、使用 OS：Windows10 ＞

D列に1列作業セルを追加すれば悩む事はないんですけどね。
こんな感じに
=IF(B2="肉",1,IF(B2="魚",2,IF(B2="米",3,"")))

もしくは、ユーザー設定リストに好みの順番を追加。

こうすれば、普通にエクセル標準機能のソートで済みますけど。
作業セルや標準機能のソートを使う人は馬鹿と思っているんですかね？

ソート順番用リスト作ってvlookupも使うとかも、頭にはない？
（不思議ちゃん） 2022/06/27(月) 01:58

不思議ちゃん様、お返事ありがとうございます。

恥ずかしながら自分自身が未熟なもので
作業セルや標準機能のソートを使う知識(発想)がありませんでした。

=IF(B2="肉",1,IF(B2="魚",2,IF(B2="米",3,"")))
これで名称に対する番号は振り分けることができましたが
これを利用して

   [E] 　[F]   [G] 
  1  数   名称　支店
  2   1    肉   青森
  3 　3    肉   愛知
  4 　5    肉   東京
  5 　2    魚   東京
  6 　6    魚   大阪
  7 　4    米   新潟

に並び替える発想が自分にはできません。

標準機能のソートを使用したところ綺麗に整列させることが
できましたが名称の種類が増えた際自動的に反映されるよう
何かしらの関数を用いて整列させることはできますでしょうか？
（simo） 2022/06/27(月) 23:01

 | 並び替える発想が自分にはできません。

 ↓のサイトの
http://www.eurus.dti.ne.jp/~yoneyama/Excel/waza/sort.html
 中ごろにある
 「作業列を使って並べ替えます」を参照してください。

 なお、元のデータをもとにして、並び替えたものを別のセルに作ることはできますが、
 元のデータそのものを関数で並び替えることはできません。

 | 名称の種類が増えた際自動的に反映されるよう
 | 何かしらの関数を用いて整列させることはできますでしょうか？

 そのような汎用的なものはありません。
 ・新しい要素が追加になったり、
 ・全く別のものを並び替えるには、
 それらの要素の順序を決める作業は必ず追加で必要になります。
 関数に全幅の信頼を置いているようですが、なんでもできるわけじゃないですよ。

（γ） 2022/06/28(火) 06:03

念のためですが、上記の記述はExcel365で使えるSort関数などは
使わない（使えない）前提ですので、
そこのところ、閲覧者さんは誤解されないよう。
（γ） 2022/06/28(火) 07:01

γ様、お返事ありがとうございます。

わざわざリンクを貼っていただきありがとうございます。
リンク先を参照して整列させることができました。

知識の浅い自分は手間(長い数式)さえかければ
関数でなんでもできると思っていましたが
γさんのように知識が豊富になればなるほど
関数の限界もみえてくるのですね。

大変勉強になりました。
ありがとうございます。
（simo） 2022/06/29(水) 00:30

 誤解があるようです。
 | 名称の種類が増えた際自動的に反映されるよう
 | 何かしらの関数を用いて整列させることはできますでしょうか
 という話でしたよね。

 一定のルール（例：文字コードの順）があるなら、
 「自動的に」順序を決めることもできるでしょう。

 しかし、例えば、自分の好きなものの順、に類した話であれば、
 新しいものが追加されたときに、それがどの順番になるかを
 Excelに逐一指示しなければいけませんよね。
 肉 > 魚 > 米 に 野菜 を追加したとき、野菜 がどの順番になるかは、
 Excelが「自動的に」判断することはできません。
 そういう至極、当然のことを申し上げたまでです。

 | 手間(長い数式)さえかければ関数でなんでもできる
 それは前に言われたこととは全く別の話です。
 それが自動的という話の内容なら、できるでしょう。

 なお、既に指摘のあったように、
 ユーザー設定リストを利用してソートすれば、
 それはリストの修正だけで対応可能でしょう。
 そちらも検討されたらどうですか？
（γ） 2022/06/29(水) 07:58

 並べ替えの基準が曖昧なまま終わってしまったという印象です。

 ・任意の順番に並べ替えたい
 ・初出の登場順に並べ替えたい（例示はこうなっています）
 ・同じものがかたまっていれば順番はどうでもいい
 ・その他

 どれだったんですかね？

 順番はどうでもいい（昇順・降順でいい）のなら
 並べ替えの「昇順」か「降順」をクリックするだけ（ユーザー設定リストは不要）
 元の並びに戻したければ、A列を昇順に並べ替え
 ※元表を並べ替える場合です

 別の場所で並べ替えたい、元表をコピーする手間も省きたい、ということなら
 ＞Excel2011(Mac)
 このバージョンで使えるかどうか知りませんけど、パワークエリでもできるでしょう。

 数式でやるにしても、並べ替えの基準を明確にしてもらう必要があります。

 以上、参考まで
（笑） 2022/06/29(水) 15:37

γ様、お返事ありがとうございます。

今回はとにかく名称がまとまってさえいれば問題なかったので
自動的に反映させることができました。
説明不足で申し訳ありません。

ユーザー設定リストを使用すると取り込む範囲が
純粋な文字列のみ取り出し可能というのが気になりますが
試させていただきます。

ご丁寧に解説していただき本当にありがとうございました。

笑様、お返事ありがとうございます。

質問させていただく際の注記が不十分で申し訳ありませんでした。

・同じものがかたまっていれば順番はどうでもいい

今回は上記の条件となりますので

並べ替えの「昇順」か「降順」をクリックするだけ

上記の条件でも可能なんですね。

パワークエリというものを初めて知りましたので
今後勉強させていただきます。

ご丁寧に解説していただき本当にありがとうございました。

（simo） 2022/06/30(木) 03:33

 ＞自動的に反映させることができました。 
 数式で解決したってことですよね？
 どんな式なのか見当もつきませんが、参考までに一例

 1）作業列：D列

  　D2 =IF(B2="","",IF(COUNTIF($B$2:B2,B2)=1,INT(MAX($D$1:D1))+1,INT(VLOOKUP(B2,$B$1:D1,3,FALSE)))+ROW()/1000)
  　下コピー

 2）F～H列で並べ替え

    F2 =IFERROR(INDEX(A:A,MOD(SMALL($D$2:$D$100,ROW(A1)),1)*1000),"")
  　右・下コピー

 名称の種類が新たに追加されても何も変更する必要はありません。

 以上
（笑） 2022/06/30(木) 17:47　F2の式を変更 18:21

笑様、お返事ありがとうございます。

す、凄いですね・・・まさに自分が求めていた自動数式です。
笑様の数式をあてはめると結果は見事に成立しますが
数式の意図はまだ完全に把握できてません。
本当は最初からこのような数式を教えていただきたかったのですが
少しは自分で努力しろと叩かれそうだったので自分で考えて解決していました。

解決といいましても自分の場合はD列に不思議ちゃん様に
教えていただいた=IF(B2="肉",1,IF(B2="魚",2,IF(B2="米",3,"")))
をあてはめてE列にD列に出た数値をSUM(PRODUCT($D2,1000),$B2))で算出して
さらにF列に=IF($E2="","",SMALL($E$2:$E$200,ROW()-1))と小さい数字順に並べて
G列にINDEX+MATCH関数で数を返して最後にVLOOKUP関数を使用した感じです。

作業列が3列にもおよびますし新しい種類に
対応するには別のリストを作成して
=IF(B2="肉",1,IF(B2="魚",2,IF(B2="米",3,"")))の文字列を
地道にセル参照しなければなりませんでした。

どのようにすれば笑様のような発想ができるのか羨ましい限りです。
やはり場数を踏むのが一番なのですかね。

早速ご教授いただいた数式を利用させていただきます。
本当にありがとうございました。
（simo） 2022/07/01(金) 00:40

 >=IF(B2="肉",1,IF(B2="魚",2,IF(B2="米",3,"")))の文字列を 
 >地道にセル参照しなければなりませんでした。

 いやだから、ソート順番用リスト作ってvlookupも使うとかも
 って、こんな感じなのだけれど。

 肉    1
 魚    2
 米    3
 野菜  4
 熊    5
（不思議ちゃん） 2022/07/01(金) 05:43

不思議ちゃん様、お返事ありがとうございます。

自分の理解力が乏しくて申し訳ありません。
なるほど！そういう解き方も可能なんですね！
それですと番号を入れ替えれば自分の任意の順番にも対応できそうですね。
大変参考になりました。早速試してみます。

本当にありがとうございました。
（simo） 2022/07/01(金) 23:53

コメント返信：

[ 一覧(最新更新順) ]

[[20220626233537]] 『sort関数以外の関数で整列させたい』（simo） ページの最後に飛ぶ

[[20220626233537]]　『sort関数以外の関数で整列させたい』（simo）　ページの最後に飛ぶ