- ベストアンサー
エクセルの並べ替えをマクロを使わず関数で
- エクセルの並べ替えをマクロを使わず関数で行いたい。LARGE関数を使って数値のみは大きい順に並べ替えることはできるが、日付も一緒に並べ替えたい。VLOOKUP関数を使って日付の並べ替えも考えたが、同じ数値があると一緒に並べ替える方法が分からない。どの関数を使えば良いか教えて欲しい。
- エクセルの並べ替えをマクロを使わず関数で行いたい。LARGE関数を使って数値のみは大きい順に並べ替えることはできるが、日付も一緒に並べ替えたい。VLOOKUP関数を使って日付の並べ替えも考えたが、同じ数値があると一緒に並べ替える方法が見つからない。どの関数を使えば良いか教えてください。
- エクセルの並べ替えをマクロを使わず関数で行いたい。LARGE関数を使って数値のみは大きい順に並べ替えることはできるが、日付も一緒に並べ替えたい。VLOOKUP関数を使って日付の並べ替えも考えたが、同じ数値があると一緒に並べ替える方法が分からない。どの関数を使えば良いか教えてください。
- pototto7
- お礼率63% (12/19)
- その他MS Office製品
- 回答数8
- ありがとう数6
- みんなの回答 （8）
- 専門家の回答
質問者が選んだベストアンサー
- ベストアンサー
添付図参照（小さ過ぎて見づらいかな？） Sheet1 において、 １行目の「4月」、「5月」、「…」は、実際には“ユーザー定義”で G/標準"月" に書式設定して、数値の 4、5、… を入力 Sheet2 において、 書式 m/d を設定したセル A2 に、日付形式で例えば 2010/4/1 を入力して、此れを下方にズズーッと（セル A367 まで）ドラッグ＆ペースト 次式を入力して、範囲 B2:C2 を下方にズズーッと（セル A367 まで）ドラッグ＆ペースト B2: =IF(ISERROR(VLOOKUP(DAY(A2),Sheet1!A:C,MATCH(MONTH(A2),Sheet1!A$1:L$1,0),FALSE)),"",VLOOKUP(DAY(A2),Sheet1!A:C,MATCH(MONTH(A2),Sheet1!A$1:L$1,0),FALSE)) C2: =IF(B2="","",B2-A2/100000) ちなみに、Ｃ列は標準書式に設定 Sheet3 において、次式を入力して、範囲 B2:C2 を下方にズズーッとドラッグ＆ペース B2: =INDEX(Sheet2!B:B,MATCH(LARGE(Sheet2!C:C,Sheet2!E2),Sheet2!C:C,0)) C2: =INDEX(Sheet2!A:A,MATCH(LARGE(Sheet2!C:C,Sheet2!E2),Sheet2!C:C,0))
その他の回答 (7)
[No.1補足]への回答、 》 Sheet3のB2とC2の式にある Sheet2!E2 にはどんな関数または数値が入りますか？ ごめんなさい、間違えていました。 「Sheet2!E2」は「A2」に書き替えてください。つまり、… Sheet3 に入力するのは、次式です。 B2: =INDEX(Sheet2!B:B,MATCH(LARGE(Sheet2!C:C,A2),Sheet2!C:C,0)) C2: =INDEX(Sheet2!A:A,MATCH(LARGE(Sheet2!C:C,A2),Sheet2!C:C,0))
- KURUMITO
- ベストアンサー率42% (1835/4283)
A１セルに日、B１セルに４月、C１セルに５月の文字がそれぞれ入力されており、それらの列の２行目から下方にデータがあるとします。作業列をD列およびE列に、お求めの表はG列からI列に表示させるとします。 D１セルは空白のままにして、D２セルには次の式を入力し下方にオートフィルドラッグします。 =IF(OR($A2="",B$1="",B2=""),"",RANK(B2,$B:$C,0)+COUNTIF($D$1:$E1,RANK(B2,$B:$C,0))/10000) E１セルは空白のままにして、E２セルには次の式を入力して下方にオートフィルドラッグします。 =IF(OR($A2="",C$1="",C2=""),"",RANK(C2,$B:$C,0)+COUNTIF($D$1:$E1,RANK(C2,$B:$C,0))/10000) G１セルには順位、H１セルには数値、I１セルには月日と入力します。 G２セルには次の式を入力して下方にオートフィルドラッグします。 =IF($G1="","",IF(SMALL($D:$E,ROW(A1))>=11,"",SMALL($D:$E,ROW(A1)))) 小数点以下が表示されるようでしたら「小数点以下の表示桁数を減らす」で整数の表示にします。同じ順位がある場合にはその順位がダブって表示されることになります。 H２セルには次の式を入力して下方にオートフィルドラッグします。 =IF(G2="","",IF(COUNTIF($D:$D,$G2),INDEX($B:$C,MATCH($G2,$D:$D,0),1),INDEX($B:$C,MATCH($G2,$E:$E,0),2))) I２セルには次の式を入力して下方にオートフィルドラッグします。 =IF(H2="","",IF(COUNTIF($D:$D,$G2),SUBSTITUTE($B$1,"月","")*1&"/"&INDEX($A:$A,MATCH($G2,$D:$D,0)),SUBSTITUTE($C$1,"月","")*1&"/"&INDEX($A:$A,MATCH($G2,$E:$E,0))))
お礼
ありがとうございます。大変参考にさせていただきました。
- keithin
- ベストアンサー率66% (5278/7941)
B2: =DATE(2000,MOD(102-LARGE(Sheet1!$B$2:$M$32*100+100-COLUMN($B$2:$M$32),ROW(B1)),100),MOD(10199-LARGE(Sheet1!$B$2:$M$32*10000+10000-COLUMN($B$2:$M$32)*100-ROW($B$2:$M$32),ROW(B1)),100)) と記入してコントロールキーとシフトキーを押しながらEnterで入力。 セルの書式設定で表示形式のユーザー定義でm/dに設定。 以下コピー。 C2: =INT(LARGE(Sheet1!$B$2:$M$32*10000+10000-COLUMN($B$2:$M$32)*100-ROW($B$2:$M$32),ROW(B1))/10000) と記入してコントロールキーとシフトキーを押しながらEnterで入力。 以下コピー。 何度も配列計算するのもどうかと思いましたが，対象が365日しかないので，ま，いいかと。 ＃日付の計算結果がキモチ悪ければ，先頭の2000を2011などにしても構いません。元のシート１の１行目で「４月」とかじゃなくもっと細工しておく手もあります。
お礼
ありがとうございます。大変参考にさせていただきました。
- kagakusuki
- ベストアンサー率51% (2610/5101)
今仮に、 元の表の「日」と入力されているセルがSheet1のA1であるものとして、 Sheet3のA列とB列を作業列として使用して、 Sheet2に並べ替えた表を表示するものとします。 又、元の表の月はシリアル値ではなく、文字列であるものとします。 まず、Sheet3のB1セルに次の数式を入力して下さい。 =IF(AND(ROW()<367,ISNUMBER(INDEX(Sheet1!$A$1:$M$32,MATCH(DAY(ROW()),Sheet1!$A$1:$A$32,0),MATCH(MONTH(ROW())&"月",Sheet1!$A$1:$M$1,0)))),INDEX(Sheet1!$A$1:$M$32,MATCH(DAY(ROW()),Sheet1!$A$1:$A$32,0),MATCH(MONTH(ROW())&"月",Sheet1!$A$1:$M$1,0)),"") 次に、Sheet3のA1セルに次の数式を入力して下さい。 =IF(ISNUMBER($B1),$B1+COUNTIF($B$1:$B1,$B1)/COUNTIF($B:$B,$B1),"") 次に、Sheet3のA1～B1の範囲をコピーして、同じ列の2行目以下に貼り付けて下さい。 次に、Sheet2の A1セルに 順位 B1セルに 数値 C1セルに 月日 A2セルに 1 A3セルに 2 A4セルに 3 A5セルに 4 A6セルに 5 A7セルに 6 A8セルに 7 A9セルに 8 A10セルに 9 A11セルに 10 と入力して下さい。 次に、Sheet2のB2セルに次の数式を入力して下さい。 =IF(ROWS($2:2)>COUNT(Sheet1!$B$2:$M$32),"",VLOOKUP(LARGE(Sheet3!$A:$A,ROWS($2:2)),Sheet3!$A:$B,2,0)) 次に、Sheet2のC2セルに次の数式を入力して下さい。 =IF(ROWS($2:2)>COUNT(Sheet1!$B$2:$M$32),"",TEXT(MATACH(LARGE(Sheet3!$A:$A,ROWS($2:2)),Sheet3!$A:$A,0),"m/d")) 次に、Sheet2のB2～C2の範囲をコピーして、同じ列の2行目以下に貼り付けて下さい。 以上です。
お礼
ありがとうございます。大変参考にさせていただきました。
- 某HN クロメート(Chromate)（@CoalTar）
- ベストアンサー率40% (705/1742)
＃３です 追加。 4月1日～翌年3月31日だったら、年も考慮して G1セル =F1*10^3+DATE(YEAR(E$1)+1,3,31)-E1
- 某HN クロメート(Chromate)（@CoalTar）
- ベストアンサー率40% (705/1742)
考え方だけなので適宜数式を直してください データの桁数が12桁以内が条件 サンプルのように月は全角入力しているとします E1セル 4/1 下へオートフィル (シリアル値) F1セル =INDEX($B$2:$C$32,MATCH(DAY(E1),$A$2:$A$32,0),MATCH(JIS(TEXT(E1,"m月")),$B$1:$C$1,0)) データの取得(5月までの検索になっています) G1セル =F1*10^3+DATE(YEAR(E1),12,31)-E1 データ×1000 - 12月31日までの日数 H1セル =RANK(F1,$F$1:$F$366) ランク付け K2セル =INDEX($F$1:$F$366,MATCH(J2,$H$1:$H$366,0)) L2セル =INDEX($E$1:$E$366,MATCH(J2,$H$1:$H$366,0))
お礼
ありがとうございます。大変参考にさせていただきました。
- tom04
- ベストアンサー率49% (2537/5117)
こんばんは！ ご希望の方法とはかけ離れるかもしれませんが・・・ VBAでもお示しの表配置ではかなり難しいと思います。 いずれにしてもデータを1列にまとまる方が簡単だと思います。 そこで一つの案です。 ↓の画像のようにSheet2にすべての月のデータをコピー＆ペーストしていきます。 （これは手作業になるかもしれません。） 空白セルがあっても良いですので、あらかじめ31日分ずつ数式をコピーしておく方法もあります。 画像の配置でSheet2のE2セルに =IF(F2="","",DATE(2011,4,F2)) F2セルに =Sheet1!A2 G2セルに =Sheet1!B2 としてE2・G2セルを範囲指定しG2セルのフィルハンドルで31日目（大の月・小の月の区別は必要ありません）までコピー！ 同様にその下に次の月（5月）のデータを貼り付けていきます。 E列の数式は =IF(F33="","",DATE(2011,5,F33)) のように月の部分だけを変えます。 これを1年分作っておきます。 そして、H2セルには単純にRANK関数で =IF(G2="","",RANK(G2,G:G)) として最後の行までオートフィルでコピー！ このデータを利用すればあとは簡単です。 同順位がある可能性がありますので、それにも対応できるようにしています。 Sheet2のA2セル（セルの表示形式はユーザー定義から 0位 としておきます）に =IF(COUNTIF(H:H,"<=10")<ROW(A1),"",SMALL(H:H,ROW(A1))) B2セルは =IF(A2="","",INDEX($G$1:$G$500,SMALL(IF($H$1:$H$500=A2,ROW($A$1:$A$500)),COUNTIF($A$2:A2,A2)))) これは配列数式になってしまいますので、この画面からコピー＆ペーストする場合はB2セルに貼り付け後、数式バー内で一度クリック → 編集可能になりますので Shift＋Ctrlキーを押しながらEnterキーで確定！ 数式の前後に｛ ｝マークが入り配列数式になります。 C2セル（これも配列数式で表示形式は日付に！）は =IF(A2="","",INDEX($E$1:$E$500,SMALL(IF($H$1:$H$500=A2,ROW($A$1:$A$500)),COUNTIF($A$2:A2,A2)))) 最後にA2～C2セルを範囲指定し、C2セルのフィルハンドルで下へコピーすると 画像のような感じになります。 以上、長々と書きましたが他に良い方法があればごめんなさいね。m(__)m
お礼
ありがとうございます。大変参考にさせていただきました。
お礼
ありがとうございました。大変参考になり、私にとって一番わかりやすいこの方法を使わせてもらいます。
補足
Sheet3のB2とC2の式にある Sheet2!E2 にはどんな関数または数値が入りますか？