[[20050421155349]] 『検索データーの並べ替え』（のＶｉた) ページの最後に飛ぶ
[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『検索データーの並べ替え』（のＶｉた)
工具ごとに使用している部品の生産個数によって左から多い順に並べ替えたいと思っています。
A B C 〜 G H I ←左の部品番号で
工具１ A1001 A1400 A1100 50 100 56 別のシートから
工具２ A1600 A1900 A1800 200 450 0 「VLOOKUP」で
工具３ A2000 A1300 A1700 70 0 200 生産個数を
↓ 拾い出してます
A B C 〜 G H I
工具１ A1400 A1100 A1001 100 56 50
工具２ A1900 A1600 A1800 450 200 0
工具３ A1700 A2000 A1300 200 70 0
できれば元の Ａ Ｂ Ｃ列が並び変われば最高ですが無理のような感じもしますので
別の列、又は新規シートに作成でもＯＫです。
LARGE関数が使えそうなんですがどう使ったらよいのかさっぱりわかりません。
マクロを組むなんていうのはとんでもない・・・・。
（コピーしてモジュールに貼り付けるぐらいはなんとか出来ます）
よろしくお願いいたします！！(Windows2000プロフェッショナル,Excel2000です)
「別のシート」のデータがどのような配置になっているかわかれば、 解決の糸口があるかも。（みやほりん）（-_∂）b
「別のシート」は縦に部品番号が並んでいてその右に生産個数が
これまたさらに別シートの「月別シート」から拾い出しています。
「別のシート」 「月別シート」
A B A B C D A1001 50 部品番号 前月 今月 来月 A1900 450 A1001 10 50 40 A2000 70 A1900 500 450 300 A1001 50 ← A1400 100 100 50 A1700 200 A1600 500 200 700 A1400 100 A1800 10 0 30
「月別シート」の”今月”の数字を「別のシート」に拾い出し、
それをメインのシートに工具別に表示させています。
（「別のシート」，「月別シート」両方とも部品番号の並びはランダムです。）
（のＶｉた)
元のABC列を並び替える場合です。 適当なセルに =INDEX($A1:$C1,,MOD(LARGE(($G1:$I1)*9^9+COLUMN($A1:$C1),COLUMN(A1)),9^9)) と入力してCtrlキーとShiftキーを押しながらEnterで確定します。 後は、右に下に必要分コピーして→形式を選択して貼り付け→値で 元のABC列に貼り付けられたらいいでしょう。 どうでしょうか？ （SoulMan）
＞後は、右に下に必要分コピーして
コピーしようとしましたが「配列の一部を変更できません」と表示されて出来ませんでした。 そこで直接A〜列に［値］，［数式］，［全て］と貼り付けをしましたら「#VALUE」と表示されてしまい A〜列の部品番号で［別のシート」から拾い出してる生産個数も「#VALUE」になってしまいます。 コピーの仕方が悪いんでしょうか？ （式の中にある 9^9 って何を表してるのか参考までにお教え下さい） （のＶｉた)
事務局の方へ
改行がうまくできません。 入力欄では改行しているのにプレビューで見るとつながってしまい
改行させようとEnterを２回押すと空行が１行空いてしまいます。 半角スペースを入れると改行しますが左端に半角スペースが空いてしまいます。
うまく改行させるテクニックをお教え下さい。
（初心者でお世話をおかけ致します）
簡単なサンプルを作ってみました。 >うまく改行させるテクニックをお教え下さい。 先頭に半角のスペースをあけるといいですよ。 編集画面で確認できます。 9^9は9の9乗です。大きな数字が欲しかっただけです。 http://ryusendo.no-ip.com/cgi-bin/upload/src/up0291.xls >コピーしようとしましたが「配列の一部を変更できません とは、どのようにコピーしているのですか？ それから、形式を選択して貼り付けはEnterしますと全てとなってしまいますので OKをクリックしたらESCキーで逃げてください。 分からないことがあったらどんどん聞いてくださいよ。 遠慮してちゃだめですよ。 （SoulMan）
空いたセルに入力してセル右下の黒十字で必要な数だけ右に下にドバーッとコピーしてからそれを 丸ごとA列〜に型式を選択で貼り付けるのかと勘違いしてました.
で、A1に式を入力して必要分貼り付けしてもサンプルの様に別枠に入力、貼り付けしても
「#VALUE」になってしまいます。G列〜の所がサンプルの様に値ではなく、 =IF(ISBLANK($A1),"",IF(ISNA(VLOOKUP($A1,別のシート,3,FALSE)),0,VLOOKUP($A1,別のシート,3,FALSE))) という関数で「別のシート」から数字を拾い出してるせいでうまくいかないんでしょうか？
G列〜を値に変えてもだめでした。（一体何が悪いのかちんぷんかんぷん･･･） （のＶｉた)
横から・・・ SoulManさんのご提示の数式は配列数式ですから、確定時に CtrlキーとShiftキーを押しながらEnterで確定します。 数式が｛｝で囲まれれば成功です。 （ケン）
各氏いろいろご指導有難う御座います。
数式はちゃんと{}で囲まれてるんですけどねえ・・。
あっ、実はA列〜内は空欄もあるんですけど（当然それに対応したG列〜も空欄有り）
それが原因ですか？！
（のＶｉた)
一度この部分をマウスで選択してF9を押してみるとなんと表示されますでしょうか？ ($G1:$I1)*9^9 または、↓のようにして別のセルに書き出してそこを参照するようにしてはどうですか？ =IF(ISNUMBER(G1),G1,0) （SoulMan）
３個ならと思って、試作させていただいたのですが。。 (LOOKUP)
A B C G H I 1 A1001 A1400 A1100 100 200 100 2 A1600 A1900 A1800 200 450 0 3 A2000 A1300 A1700 70 0 200 4 5 A1400 A1100 A1001 200 100 100 6 A1900 A1900 A1600 450 200 0 7 A1700 A1300 A2000 200 70 0 ↑ =IF(H1="","",MIN(G1:I1)) ↑ =IF(G1="","",LARGE(G1:I1,2)) ↑ =IF(COUNT(G1:I1)=3,MAX(G1:I1),"") ↑ =IF(B5="","",IF(COUNTIF(A5:B5,A1),IF(COUNTIF(A5:B5,B1),C1,B1),A1)) ↑ =IF(A5="","",INDEX(A1:C1, IF(G5=H5,MATCH(H5,INDEX(G1:I1,MATCH(H5,G1:I1,)+1):I1,)) +MATCH(H5,G1:I1,)))) ↑ =IF(G5="","",INDEX(A1:C1,MATCH(G5,G1:I1,)))
こんばんは！
今日は昼間時間が無かったのでお返事が出来ませんでした。ごめんなさい。m(__)m
>空いたセルに入力してセル右下の黒十字で必要な数だけ右に下にドバーッとコピーしてからそれを
丸ごとA列〜に型式を選択で貼り付けるのかと勘違いしてました.
その方法でいいんじゃないんですか？
>配列の一部を変更できません」と表示されて出来ませんでした。
その方法からどうやったらこの様なメッセージが出るのか私にはわかりません。
=IF(ISBLANK($A1),"",IF(ISNA(VLOOKUP($A1,別のシート,3,FALSE)),0,VLOOKUP($A1,別のシート,3,FALSE)))
VLOOKUPで参照した数値は計算につかえないか？
そんなことはありません。少なくとも私にはわかりません。
ただ、文字に数値をかけるとエラーになります。
{19371024450,#VALUE!,21695547384}
これは当然のことです。
例には空白がなくて「0」と表示されていましたのでこの辺のことはご理解されているのかと思っておりました。
サンプルまで提示させて頂いて、尚且つ
>G列〜を値に変えてもだめでした。
といわれることが私にはわかりません。
少なくともサンプルでは出来ている事さえご理解いただけなのでしょうか？
出来る出来ないは新規Bookでお試しになってからにしてください。
>あっ、実はA列〜内は空欄もあるんですけど（当然それに対応したG列〜も空欄有り）
それが原因ですか？！
=INDEX($A1:$C1
INDEX関数でインデックスの何番目の値かを返しているだけなので試していませんが関係ないと思います。
それよりも
($G1:$I1)*9^9
この式の中身が問題だと思います。どうでしょうか？
（SoulMan）
「配列の一部を変更できません」とは列,行一度に貼り付けしようとすると出ます。 ですんで、行又は列方向に一列貼り付けしたのちもう一方向に貼り付けすると出来ました。 つまり２段階でないと貼り付け出来無かったという事でした. ＞出来る出来ないは新規Bookでお試しになってからにしてください。 おっしゃる通り新規ブックで試したら３列３行で試したら正常に表示しました。 しかし実際のブックにコピーしてみると「#REF!」や「#NUM!」が表示されます。 シートを新しく追加して１個のセルに手で入力してその後コピー貼り付けで広げても同じです。
一番最初の質問の見本と実際のものとの違いといえば空白が存在するという事と
並べ替え必要部分の大きさが１５列４２０行有るという事です。
＞($G1:$I1)*9^9なんと表示されますでしょうか？
エラーが出ているセルので{0,9298091736,2289267669501,387420489,387420489,0,0,0,0,0,0,0,0,0,0}
というように表示されます。
LOOKUPさん、せっかく作っていただいたのに１５列４２０行では入力が大変なので残念です。 （のＶｉた)
おはようございます。 そのエラーの出ている実際に使われている式をここに貼り付けてみてくださいませんか？ あっ、それから個数には少数点がありますか？ ちょっと追加です。 >行又は列方向に一列貼り付けしたのちもう一方向に貼り付けすると出来ました。 どうも理解しがたいのですが、私は、GHI列の順序によってABC列を並び替える 数式を提示しているのですよ。 つまり >←左の部品番号で別のシートから「VLOOKUP」で 生産個数を 拾い出してます ということは、ABC列が並び変われば自然にGHI列も並び変わるということではないのですか？ >つまり２段階でないと貼り付け出来無かったという事でした. 例えばABC列に420行部品番号があったら、AAABAC列にでもそれと同じ範囲、 つまり、 AA1に最初の数式を入力して配列で確定（CtrlキーとShiftキーを押しながらEnterで確定） AA1から右にAC1までハンドルをつまんでコピー そのままAC420までハンドルをつまんでコピー そのまま右クリック→コピー A1を選択して右クリック→形式を選択して貼り付け→値→OK （Enterすると全て貼り付けとなってしましますのでESCキーで逃げます。） するとAA1からAC420に並び変わってあった部品番号の値だけが A1からC420に張り付くと思いますが、違いますか？ 数式をコピーしたり、その数値だけを形式を選択して貼り付けるなどの基本操作は 既にご存知でしたら失礼にあたると思いあえて御説明は割愛させていただいておりましたが、 どうも私の見解と違う様ですのであえて記載させて頂きました。 既にご存知であったり、思い違いがありましたらごめんなさいです。 どうでしょうか？ （SoulMan）
下から（？？？）失礼します。
>３列３行で試したら正常に表示しました。
>実際のブックにコピーしてみると「#REF!」や「#NUM!」が表示されます。
「#REF!」:INDEX関数の範囲に対して範囲以上の値を求めている為、
「#NUM!」:INDEX関数の行(列）指定をする為のLARGE関数に対し、範囲セル数以上の順位を求めている為、
と推測します。
＆
>並べ替え必要部分の大きさが１５列４２０行有るという事です。
>{0,9298091736,2289267669501,387420489,387420489,0,0,0,0,0,0,0,0,0,0}
実際のデータでは、A〜O列に部品番号が入り、P〜AD列に生産個数が入っているのではないでしょうか？
↓
実際のデータの並びをしっかりと伝えるとすぐに解決するような気がしてならないです。
to SoulManさん なんか式が長いな？ っと思っていたら、同数対策だったんですね。 その配慮すら出来なくなっている近頃のわたしです。 お邪魔しました。。。 (sin)
SoulManさん いつも丁寧なご回答まことに有難う御座います。
コピー＆貼り付けはSoulManさんの方法と全く同じです。(一番最初はちょっと間違ってましたが)
ですが数式を貼り付けで展開した時ですでに「#REF!」や「#NUM!」のエラーは表示されてしまってます。
(手入力して配列で確定しても同じです)
以下が実際の数式です。
{=INDEX(カッター別!$L3:$Z3,,MOD(LARGE(($AA3:$AO3)*9^9+COLUMN(カッター別!$L3:$Z3),COLUMN(カッター別!L3)),9^9))}
最初は「カッター別」という同一シート内で入力＆コピー＆貼り付けしましたがエラー出るので
現在｢カッター別2｣という別シートを追加して入力してみましたが同じ結果です。
又、生産個数の入っているセルの数式は｢カッター別｣シートに
=IF(ISBLANK($L3),"",IF(ISNA(VLOOKUP($L3,VOL来月,4,FALSE)),0,VLOOKUP($L3,VOL来月,4,FALSE)))
｢カッター別2｣シートに
=IF(ISNUMBER(カッター別!AP3),カッター別!AP3,0)
という式で呼び出しておりここはエラーも無く「カッター別」シートと全く同じ数字が表示されています。
本当は実際のブックを見ていただいた方が問題解決も早いんでしょうが部品ごとの生産個数という 社外秘事項な事柄ゆえ残念です。 (｢サンプル｣のような貼り付けの仕方を知らないのでしたくても出来ませんが・・)
こんなので理解していただけましたでしょうか？ （のＶｉた)
今日は出かけていたのでお返事が遅くなってしまいました。ごめんなさい。m(__)m ところで数式のことですが、少し誤解なされている様です。 多分↓こうです。。 =INDEX($L3:$Z3,,MOD(LARGE(($AA3:$AO3)*9^9+COLUMN($A$1:$O$1),COLUMN(A1)),9^9)) まず、 =INDEX($L3:$Z3 は並び替えるデータの範囲ですから上記でOKです。ただし これはエラーの出ているところですね？ 実際に3行目から始まっているのでしたら問題はありませんが一行目から始まって いるのでしたら少し修正が必要かもしれません。 次に↓ここ ($AA3:$AO3)*9^9 並び替えの基準になる数値の範囲ですね？ AAからAOで15個の要素があるので問題ないと思います。 次にここ COLUMN(カッター別!$L3:$Z3) これは↓でないといけません。 COLUMN($A$1:$O$1) 1から15のインデックス番号が欲しいのですから1から15をあらわす数字つまり A1から右にコピーして15になるCOLUMN・・・つまりO1です。 COLUMN(A1)と入力して右にコピーすればすぐにお分かりになると思います。 これが今回の数式の最大のポイントです。 並び替えしたい数値を9^9倍してそれに対応した行番号をプラスする。 そのプラスした数値を9^9で割ります。するとプラスした行番号が並び変わって余りとして 残るのです。わかりますか？わからなかったらまた聞いてくださいよ。 次にここ COLUMN(カッター別!L3) これはこの式が範囲の左上端であるならば ↓これでなければなりません。 COLUMN(A1) つまりこれはインデックスの何番目の値を返すか？なのですから最初は「1」です。 これも同じく COLUMN(A1)と入力して右にコピーすればすぐにお分かりになると思います。 どうでしょうか？ 以上のことを踏まえて数式をもう一度入力してください。 今度上手く出来なかったら、数式の一番最初の式をここに貼り付けてください。 ではでは、お返事をお待ちしております。 追伸！今回の式は理屈さえわかればそんなに難しい式ではないですよ。 もう少しで理解されると思います。理解されれば「なぁ〜〜んだ」と思われることでしょう。。 頑張ってください。 （SoulMan）
そうか！COLUMN($A$1:$O$1),COLUMN(A1)っていうのはデータがそこに有るとか無いとかじゃなくて 1〜15までの列の数字(インデックス番号)と、このセルのデータは元データーとしては 何番目であるかという事を得る為の数式だった という事だったんですね。 (解釈は合っていますか？) 早速入力し直した所,生産個数の出ているものは見事に多い順に並び変わっています！！ 理解力が無いばっかりにSoulManさんには多大なご苦労をおかけいたしました。
って、まだお手を煩わさなければならない事態である事が判明いたしました。 生産個数の拾い出しの大元である｢さらに別のシート｣での状況による (生産が無くなって部品番号が消滅，生産がなくなっているがまだ部品番号が残っている， 生産がなくなっているが生産量0という数字が出ている)違いだと思われる 生産の無い部品番号が右端に固まっちゃっています。 こういうのも自動で左へ寄せる方法って有るでしょうか？ （のＶｉた)
おはようございます。 >(解釈は合っていますか？) 合ってます。バッチリです。よく短時間でそこまで理解されましたね。 ひょっとして・・達人だったりしてぇ(^^;;; んで、そこまで理解されているのなら今回のご質問は簡単に解決されるはずなのですが・・・ 私の解釈が間違っていなければの話ですけど。 では本題に >さらに別のシート｣での状況による 私には、別のシートの状況まではわかりません。ただ、文章から想像すると 空白と「0」との区別がないということではないのでしょうか？ >1〜15までの列の数字(インデックス番号)と、このセルのデータは元データーとしては >何番目であるかという事を得る為の数式だった という事だったんですね。 つまり現在は空白(文字）に数値をかけるとエラーとなってしまうので 「0」と評価しているのではないのですか？ 生産がないのものも「0」また、中止消滅したものも「0」としているために区別がつかずに 降順に並び替えると小さいと評価されて右側へよってしまうのではないのですか？ ここまで整理してみるとなんとなく解決の糸口が見えた様な気がしませんか？？ 生産がないの物の「0」と、中止消滅した物の「0」を区別してあげればいいんじゃないんですか？ 例えば以下の様なデータがあったとして（空白はあえて「空白」としています。） 10 空白 0 20 15 作業列に↓の様な式を入力して空白の場合は範囲の最大値とするのはどうですか？ =IF(A1="空白",MAX($A$1:$E$1),A1) 結果 10 20 0 20 15 この様に空白のところは範囲の最大値「20」と評価されてそれなりのところに並び替わると思います。 どうでしょうか？意味はあってますか？参考になりませんか？？ （SoulMan）
SoulManさんがせっかく良い案を出して下さったのに仕事が連休に入っちゃいまして 試せなくなりました。(問題のファイルは会社のサーバーに有る為) 自分なりの案も考えてSoulManさんの提案のと色々チャレンジしてみます。 その時また報告させていただきますが、うまくいかなかった時は またまたお世話になるかも知れませんが宜しくお願いします。 （のＶｉた)
ゴールデンウィーク中、長らくご無沙汰しておりました。 まことに恐縮ですが、早速質問に入らせてください。 生産の無い部品番号が右端に固まっているものについて 「別のシート」からメインに使っている「工具別」シートに呼び出すときに 同じ生産の無い物でも下記の式により区別しようとしました。
=IF(ISBLANK(L3),"",IF(ISBLANK(VLOOKUP(L3,名前,4,FALSE)),0.02,IF(ISNA(VLOOKUP (L3,名前,4,FALSE)),0.01,VLOOKUP(L3,名前,4,FALSE))))
（VLOOKUP(L3,名前,4,FALSE)の所でF9押すと、生産は無いが部品番号の存在するものは"" 部品番号自体が抹消されているものは#N/Aとなっている） （生産は無いが部品番号の存在するものは""＝0.02、部品番号自体が抹消されているものは#N/A＝0.01 として生産の無い物でも順位付けしようとしました）。
この結果を並べ替える新しいシートに =IF(ISNUMBER(工具別!AP3),工具!AP3*100,0)で さらに拾い出してこの値でSoulManさんの式で並べ替えを行いました. ところが #N/Aは0.01となり左へ寄ったんですが、 ""が0.02にならず空白になる為右に寄ったままです。
0.02 0.01 空白 空白 0.01 空白 200.00 0.01 0.02 となるはずなのが 200.00 0.01 空白
だいぶ考えましたがどこが悪いのかわかりません。 どこを直せばいいんでしょうか？ （のＶｉた）
A1に ="" と入力して B1に =IF(A1="",1,2) C1に =IF(ISBLANK(A1),1,2) と入力するとわかると思うのですが、 1 2 結果はこうなります。 つまり IF(ISBLANK(VLOOKUP(L3,名前,4,FALSE)) これがFALSEと判定されたものと思います。 ということでISBLANK関数を使わずに IF(VLOOKUP(L3,名前,4,FALSE)="",0.02, としてみてはどうでしょうか？ （SoulMan）
ちょっと追加です。 A1に ="" A2に =NA() A3に 2 と入力して B1に =IF(ISNA(A1),0.01,IF(A1="",0.02,A1)) C1に =IF(A1="",0.02,IF(ISNA(A1),0.01,A1)) と入力して下にフィルすると 0.02 0.02 #N/A 0.01 #N/A 2 2 2 こうなります。 つまり、最初にISNAで判定して次に=""か？調べない といけないということです。 =IF(ISBLANK(L3),"", IF(ISNA(VLOOKUP(L3,名前,4,FALSE)),0.01, IF(VLOOKUP (L3,名前,4,FALSE)="",0.02,VLOOKUP(L3,名前,4,FALSE)))) でどうでしょうか？ 失礼！無茶苦茶でしたぁ(^^; （SoulMan）
やっと完成しましたぁー！！
IF(VLOOKUP (L3,名前,4,FALSE)="",0.02を入れるのも試してたんですがSoulManさんの >ちょっと追加です。の前のカキコの様にISNAの前に入れてたんでうまくいかなかったんですね！ 結局下記の式になりました。
=IF(ISBLANK($L3),"",IF(ISNA(VLOOKUP($L3,名前,4,FALSE)),0.01,IF(VLOOKUP($L3,名前,4,FALSE)="",0.02,
IF(VLOOKUP($L3,名前,4,FALSE)=0,0.03,VLOOKUP($L3,名前,4,FALSE)))))
（0という生産個数が表示されている物もあったのを忘れてました）
SoulManさん他、色々ありがとうございました。 又解らない事があったらジャンジャン質問させて頂きますので その時は懲りずに相手して下さいね。 （のＶｉた） おまけ SoulManさんの文章は左端に半角スペースを入れていないのにきちんと改行しているのは何故ですか？
>れていないのにきちんと改行しているのは何故ですか？ 入れてますよ。 段落の最初に入れると以降が形成済みとみなされるんですね。（合ってるかな(^^;） でもね。>こんな記号があると上手く表示されない時があるから、そんな時は プレビューで見て最初に半角のスペースを入れるといいですよ。 だから、そうなってるでしょ？？ >その時は懲りずに相手して下さいね。 了解しました。 頑張ってくださいね。v(=∩_∩=)v （SoulMan）
コメント返信：
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.