座標と座標値を表にする その２

質問

何度も申し訳ないのですが、教えて下さい。

今、表1の様なxyzの座標があります。

これを表５の様に変換したいと考えております。

表１を直接表5に、もしくは表1を表３に変換することはできるのでしょうか？
（表３に変換したら後は力技で被りを消去かなぁと思っております）

ちなみに、
先程、皆様のお力で、表1を表2に変換することは出来ました。

大変申し訳ございませんが、再びお力を貸して下さい。

よろしくお願いいたします。

回答No.5

keithin · ベストアンサー

＞x座標y座標共に6桁程の数字で、
　：
＞それぞれのデータは751データ程あります。

こういった具体的な目に見える情報があれば、皆さん二度手間も無くもっとすっきり進んだと思いますよ。

ただＸ／Ｙについてダブりデータが無いという事なら、もっと簡単な数式（これなら間違いようが無いと思います）で
Ｆ２は
=SUMIFS($C:$C,$A:$A,F$1,$B:$B,$E2)
と記入、右に左にコピーします。

＞z座標は、0.503678のように有効数字6桁程度の小さい数字です。
　：
＞zは無表示となります。

再掲：
＞必要ならたとえばセルの書式設定の表示形式のユーザー定義で…

実際のデータがそういう状況なら余計なお世話だったので、セルの書式を標準に戻して下さい。

＞それ以外は表示されるのですが…

他の３や８は出てくるのにＨ４の６だけ出ないということなら、数式は正常で「あなたのサンプルが間違ってる」または「やり方を間違えてる」と考えるのが妥当です。
もう一回落ち着いて、「回答の手順をよく読みながら」最初からやり直してみてはいかがですか。他の方からのアドバイスと、ごっちゃにしないようによく気を付けて作業してください。

お礼

keithin様

ご回答ありがとうございます。

ご教示頂いた方法を試したところ、
解が得られました。

丁寧に教えて頂き、ありがとうございます。

＞こういった具体的な目に見える情報があれば、皆さん二度手間も無くもっとすっきり進んだと思いますよ。

本当に申し訳ございません。
例に示したもので十分かと思っておりました。
私のエクセルへの知識や情報伝達手段が拙かったため、皆様にご迷惑をお掛けいたしました。

＞もう一回落ち着いて、「回答の手順をよく読みながら」最初からやり直してみてはいかがですか。他の方からのアドバイスと、ごっちゃにしないようによく気を付けて作業してください。

ありがとうございます。
時間が迫っていたのであわてておりました。
私があわてていたため、皆様にお手数をお掛けしてしまったこと、
また、丁寧に教えて頂いたのに失礼な言い方をしてしまったことをお詫びいたします。

もっと落ち着いて、具体的に伝えられるよう気をつけます。

ご回答頂いた他の皆様にも、この場を借りましてお礼とお詫びを申し上げます。
将来、皆様の様に他の人にアドバイスできるよう努力致します。

この度は本当にありがとうございました。

回答No.6

bunjii

>私のエクセルがひどいのか、H4セルに６と表示されません。
xの値とｙの値を再確認してください。
私の添付画像でH4セルは6になっていますがH1が15でE4が14ですからA5=15、B5=14、C5=6なので目的通りです。

>xの値が等しいと共にyの値も等しいデータが複数存在していて、尚且つzの値が異なっているデータが存在していないことが先程分かりました。
重複データは無視されますので気にしなくて良いでしょう。

回答No.4

bunjii

>表１を直接表5に、
配列演算で対応できます。

ｘ軸の値をF1～H1へ小さい順に転記するには次の式を使います。

=IFERROR(SMALL($A2:$A5,COUNTIF($A2:$A5,"<="&E1)+1),"")

ｙ軸の値をE2～E4へ小さい順に転記するには次の式を使います。

=IFERROR(SMALL($B2:$B5,COUNTIF($B2:$B5,"<="&E1)+1),"")

F2に次の式を入力して結果を配列とするためCtrl+Shift+Enterで確定します。
F2を右へH2までコピーし、F2～H2を選択した状態で下へ4行までコピーします。
基本的に回答No.2と同じですが、関数式を理解し易くしてあります。

=IF(MAX(($A$2:$A$5=F$1)*($B$2:$B$5=$E2)*($C$2:$C$5)),MAX(($A$2:$A$5=F$1)*($B$2:$B$5=$E2)*($C$2:$C$5)),"")

結果は添付画像のようになります。

お礼

bunjii様

ご回答ありがとうございます。

解決法をご提示頂いたのに申し訳ないのですが、
私のエクセルがひどいのか、H4セルに６と表示されません。
それ以外は表示されるのですが…

また、
実際の数字は、x座標y座標共に6桁程の数字で、
z座標は、0.503678のように有効数字6桁程度の小さい数字です。
また、それぞれのデータは751データ程あります。
そのせいか分かりませんが、実際のデータでやってみるとxとyは表示されるのですが、
zは無表示となります。

なお、
データについて補足がございます。
xの値が等しいと共にyの値も等しいデータが複数存在していて、尚且つzの値が異なっているデータが存在していないことが先程分かりました。
（回答No1の補足が間違っておりました。すみません）

もしお時間がございましたら、
引き続き、お力を貸して頂けると助かります。

回答No.3

kagakusuki

今仮に、表1において「x」と入力されているセルがSheet1のA1セルであり、Sheet2のA列～B列を作業列として使用して、Sheet3及びSheet4に、表5の様な変換後の表を表示させるものとします。
　尚、Sheet3の表は「xの値が等しいと共にyの値も等しいデータが複数存在している場合には、その中でzの値が最も大きいものの値を表示する」表とし、
Sheet4の表は「xの値が等しいと共にyの値も等しいデータが複数存在している場合には、その中でzの値が最も小さいものの値を表示する」表とします。

　まず、Sheet2のA1セルに次の関数を入力して下さい。

=IF(COUNT(Sheet1!$C:$C),REPLACE(REPT(0,15),ROUNDUP(LOG10(MAX(ABS(MAX(Sheet1!$C:$C)),ABS(MIN(Sheet1!$C:$C)))),0),1,"0."),"")

　次に、Sheet2のA2セルに次の関数を入力して下さい。

=IF(AND($A$1<>"",COUNT(INDEX(Sheet1!$A:$A,ROW()):INDEX(Sheet1!$C:$C,ROW()))=3),IF(COUNTIF(A$1:A1,INDEX(Sheet1!$A:$A,ROW())&"◆"&INDEX(Sheet1!$B:$B,ROW())&"◆"&TEXT(INDEX(Sheet1!$C:$C,ROW()),"1"&$A$1&";0"&$A$1)),"",INDEX(Sheet1!$A:$A,ROW())&"◆"&INDEX(Sheet1!$B:$B,ROW())&"◆"&TEXT(INDEX(Sheet1!$C:$C,ROW()),"1"&$A$1&";0"&$A$1)),"")

　次に、Sheet2のB2セルに次の関数を入力して下さい。

=IF(COUNTIF($A2,"*?◆*?◆*?"),COUNTIF($A:$A,">"&$A2),"")

　次に、Sheet2のA2～B2の範囲をコピーして、同じ列範囲の3行目以下に貼り付けて下さい。

　次に、Sheet3とSheet4のB1セルに次の関数を入力して下さい。

=IF(OR(ISNUMBER(A1),COLUMNS($B:B)=1),IF(COUNTIF(Sheet1!$A:$A,">"&IF(COLUMNS($B:B)=1,-9E+307,A1)),LARGE(Sheet1!$A:$A,COUNTIF(Sheet1!$A:$A,">"&IF(COLUMNS($B:B)=1,-9E+307,A1))),""),"")

　次に、Sheet3とSheet4の各々のシートにおいて、B1セルをコピーして、同じ表内の1行目においてB1セルよりも右側にある全てのセルに貼り付けて下さい。

　次に、Sheet3とSheet4のA2セルに次の関数を入力して下さい。

=IF(OR(ISNUMBER(A1),ROWS($2:2)=1),IF(COUNTIF(Sheet1!$B:$B,">"&IF(ROWS($2:2)=1,-9E+307,A1)),LARGE(Sheet1!$B:$B,COUNTIF(Sheet1!$B:$B,">"&IF(ROWS($2:2)=1,-9E+307,A1))),""),"")

　次に、Sheet3とSheet4の各々のシートにおいて、A2セルをコピーして、A3以下に貼り付けて下さい。

　次に、Sheet3のB2セルに次の関数を入力して下さい。

=IF(OR($B$1="",$A2=""),"",IF(COUNTIF(Sheet2!$A:$A,B$1&"◆"&$A2&"◆*"),INDEX(Sheet1!$C:$C,MATCH(COUNTIF(Sheet2!$A:$A,">"&B$1&"◆"&$A2&"◆9"),Sheet2!$B:$B,0)),""))

　次に、Sheet3のB2セルをコピーして、同じ表内において、zの値を表示する可能性のある全てのセルに貼り付けて下さい。

　次に、Sheet4のB2セルに次の関数を入力して下さい。

=IF(OR($B$1="",$A2=""),"",IF(COUNTIF(Sheet2!$A:$A,B$1&"◆"&$A2&"◆*"),INDEX(Sheet1!$C:$C,MATCH(COUNTIF(Sheet2!$A:$A,">"&B$1&"◆"&$A2&"◆")-1,Sheet2!$B:$B,0)),""))

　次に、Sheet4のB2セルをコピーして、同じ表内において、zの値を表示する可能性のある全てのセルに貼り付けて下さい。

　これで、表5の様な変換後の表がSheet3とSheet4に表示されます。

　尚、

>(3)異なるｚが存在した場合、その行列を残す。

との事ですが、それは一体どの様な残し方にすれば宜しいのでしょうか？
　例えば、表1において、xの値が15でyの値が12となっていて、尚且つzの値が異なっている行が複数存在していた場合、表5の1行目（xの値欄）には15が複数列に表示され、1列目（yの値欄）には15が複数行に表示される事になりますが、
その際、表1においてxの値が15で、yの値が例えば14、zの値が例えば6となっている行が1行だけ存在していた場合には、
表5において、yの値が14となってる行においては、xの値が15となっている全ての列において6と表示しなければならないのでしょうか？

お礼

kagakusuki様

ご回答ありがとうございます。
例題の形では上手くいきました。

解決法をご提示頂いたのに申し訳ないのですが、

実際の数字は、x座標y座標共に467057のような6桁程の数字で、
z座標は、0.503678のように有効数字6桁程度の小さい数字です。
（大きくても16程度です。ちなみに負の値はありません）

また、
それぞれのデータは751データ程あります。
そのせいか分かりませんが、実際のデータでやってみるとxとyは表示されるのですが、
zは無表示となります。

なお、
データについて補足がございます。
xの値が等しいと共にyの値も等しいデータが複数存在していて、尚且つzの値が異なっているデータが存在していないことが先程分かりました。
（回答No1の補足が間違っておりました。丁寧に場合分けまでして下さったのに、申し訳ございません）

もしお時間がございましたら、
引き続き、お力を貸して頂けると助かります。

回答No.2

keithin

添付図：
Ａ、Ｂ、Ｃ列にＸ，Ｙ，Ｚ

Ｅ２に
=IF(ROW(A1)>COUNT(0/FREQUENCY(B:B,B:B)),"",SMALL(B:B,COUNTIF(B:B,"<="&SUM(E1))+1))
下向けにコピー

Ｆ１に
=IF(COLUMN(A1)>COUNT(0/FREQUENCY($A:$A,$A:$A)),"",SMALL($A:$A,COUNTIF($A:$A,"<="&SUM(E1))+1))
右向けにコピー

Ｆ２に
=MAX(IF(($A$1:$A$100=F$1)*($B$1:$B$100=$E2),$C$1:$C$100))
と記入、必ずコントロールキーとシフトキーを押しながらＥｎｔｅｒで入力
表範囲にコピー
必要ならたとえばセルの書式設定の表示形式のユーザー定義で
#
と設定しておく。

お礼

keithin様

ありがとうございます。

解決法をご提示頂いたのに申し訳ないのですが、
私のエクセルがひどいのか、H4セルに６と表示されません。
それ以外は表示されるのですが…

また、
実際の数字は、x座標y座標共に6桁程の数字で、
z座標は、0.503678のように有効数字6桁程度の小さい数字です。
また、それぞれのデータは751データ程あります。
そのせいか分かりませんが、実際のデータでやってみるとxとyは表示されるのですが、
zは無表示となります。

なお、
データについて補足がございます。
xの値が等しいと共にyの値も等しいデータが複数存在していて、尚且つzの値が異なっているデータが存在していないことが先程分かりました。
（回答No1の補足が間違っておりました。すみません）

もしお時間がございましたら、
引き続き、お力を貸して頂けると助かります。

回答No.1

kagakusuki

確認したい事があります。
　表1において、万が一、xの値が等しいと共にyの値も等しいデータが複数存在していて、尚且つzの値が異なっているデータが存在していた場合には、表5にはどのzの値を表示させる様にすれば良いのでしょうか？

補足

おはようございます。

kagakusukiさんのおっしゃる場合も考えられますね。
(1)大きい値を採用する。
(2)小さい値を採用する。
(3)異なるｚが存在した場合、その行列を残す。
としたいと思います。
ちなみに(1)の優先順位が高いです。
（(2)と(3)は保険として持っておきたい考えです）

大変恐縮なのですが、(1)に加えて、
表(5)からxおよびyが昇順になっていて、且つzの値がxとyに対応していることが理想です。
（今のところ表(5)の状態からvlookupを使って、別シートにあらわす方法しか思いつきません）

もし、良い案があればご助言頂けると光栄です。