「Web出版サイト」ベータ公開

Q&A集[?]

当サイトでのご質問の受付は終了しました

すべてのコンテンツを読み込み専用としたため、回答欄からも投稿できません

Apache OpenOffice/LibreOfficeのご質問はそれぞれのフォーラムへご投稿ください

質問コーナー

サイト内検索

分類メニュー

関連サイト


本日:1
昨日:0
総数:2002
現在:2


2つの条件を満たす値を表示する

ページOpenOffice.org FAQの登録ページ
投稿者はっし
分類
edit/refer
優先順位
edit/refer
状態
edit/refer
カテゴリー
edit/refer
投稿日2010-06-27 18:45:24 (日)
OSXP SP3
依存するページ
バージョン
edit/refer

メッセージ

回答ページでは行末に「~」を付加する必要はありません

いつもお世話になってます。

質問です。
D列に列方向に重複する複数の整数の表があります。
E列にも列方向に重複する複数の整数の表があります。
D列とE列の横並びの数値は重複はしません。
また、D+Eの同行での組合せは他の行の組合せと重複しません。

DとEの2つの条件を満たした数値をF列から検索して表示させたいと思ってます。

   D  E  F
1  10  15  10.5
2   3  10  11.2
3   1  2  18.4
4   8  12  25.0
5   1  3  28.6
6   3  12  30.6
:  : : :
:  : : :
:  : : :

こんな感じで200行程あります。

   G  H  I  J  ……
    1  2  3  4  ……
1   × 18.4 28.6 ……
2  18.4 ×  44.5 ……
3  28.6 30.3 × ……

イメージとしてはこんな感じにしたいと思ってます。
この表の数値表示域にはどのような関数式を入れればよいのか
また、他に良い方法があれば教えていただきたいのですが
宜しくお願い致します。


データパイロットでいかがでしょ?

Y (2010-06-28 00:15:34 (月))

提示された条件ですと、データパイロットで集計してしまえばとりあえずできると思います。
添付ファイルをご参照ください。


ただし、組み合わせが存在しない分は、縦横の数字がありません。どうしてもきれいな集計表にしたい場合は、データパイロットの表を元にしてさらに加工する必要があるでしょう。

無題

はっし (2010-06-28 01:52:51 (月))

Y様 御返答ありがとうございます。

上記データパイロットで試みましたがうまくいきません。

表の縦軸、横軸共に昇順並びにしたいのですが
データパイロットで、できた表で試したところ1,10,11,12…17,2,3,4…
という感じで1の次に10番台が並んでしまいます。

これが1,2,3,4,5…と並ばせることができるのであれば使えそうです。
ただ、元となるデータ側を変更することはできません。
例えば参照元の1を01に表示するなどはしないで対応できませんか?

それが可能ならこれでうまくいけそうなのですが…。

そもそものデータが数値じゃないのでは

Y (2010-06-28 03:06:18 (月))

そもそものデータが、もしかして頭に'(アポストロフィ)が付いて強制的に文字列となっているか、もしくは書式設定でテキストを指定していないですか?
元データが数値として入力されていれば、私の添付したファイルのように数字の小さい順に並びます。
文字列として入力されている場合は、はっしさんの遭遇したような順番でソートされます。

テキスト(文字列)なら

M.Kamataki (2010-06-28 05:14:36 (月))

YさんのサンプルでD列、E列の値を頭に'(アポストロフィ)を付けて、強制的にテキスト(文字列)にすると、はっしさんが「無題」のコメントで書かれた並びになりました。わたしもYさんが指摘されたように、D列、E列の値が「整数」ではなく、テキスト(文字列)なんだと思います。

もし、セルの値がテキスト(文字列)から変更できないのなら、D列、E列の値をDECIMAL関数で整数に変換したデータパイロット用の表を別に作成すれば良いと思います。

なお、ご質問の際に、できればサンプルのCalcファイルを添付してください。シート作成の際の疑問点にも回答者が気づくことができます。添付方法は、「当サイトの使い方/高度な使い方」をご覧ください。

お察しの通りです

はっし (2010-06-28 22:39:13 (月))

Y様、M.Kamataki様ありがとうございます。

実はお察しの通りD,E列には文字列として数字が入っています。
と、いうのもF,G,Hにあるデータをコピー貼り付けしてあり、
その中のG列のデータを抜き出したものをDとEに表示してあります。

Gに貼り付けたデータ例としては”10*15"、"3*10"、などの形式で
入っています。そのデータをLEN関数、LEFT関数、RIGHT関数を使い
抜き出してますので文字列になってます。

貼り付けた数値を数値データとして抜き出せる方法などがあるのか
判らず安易に上記関数を使用してしまいました。
数値データとして抜き出せる関数があればそれを教えて頂きたく思います。
また、無いのであれば現行からM.Kamataki様の提案のDECIMAL関数を
試してみたいと思います。
または、データパイロットを使用せず、別に関数式を使った方法で
より良い方法があればそれらも含めご提案、ご指導、ご教授願えれば
幸です。

あと、M.Kamataki様から最後に「Calcファイルを添付」の件ですが、
ファイル、シート内には他にも社外秘レベルのデータや情報が入っている
ため、大変恐縮ではございますが、ご容赦願いたく思います。

いろいろと、勝手我侭なことばかり、誠に申し訳なく思っておりますが
何卒、引き続きご教授お願いいたします。

サンプルが一番わかりやすいです

M.Kamataki (2010-06-29 09:55:14 (火))

「当サイトの使い方/高度な使い方」ページはご覧いただけたでしょうか。手順の1.に「まず、個人情報などの公開不可の内容を削除したサンプルファイルを作成してください。」と書いてありますので、「ファイル、シート内には他にも社外秘レベルのデータや情報が入っている」というようなサンプルは想定していません。

今回の場合であれば、Yさんのサンプルでデータパイロットの対象とした表が、「D,E列には文字列として数字が入っています。と、いうのもF,G,Hにあるデータをコピー貼り付けしてあり、その中のG列のデータを抜き出したものをDとEに表示してあります。」のようなサンプルであれば良いわけです。

さて、DECIMAL関数の例ですが、「=DECIMAL((現在のD列の式);10)」で済む話ではないでしょうか。あるいは、式が長いためわかりにくくなりそうなら、前にも書いたようにデータパイロット用の表を別に作成すれば良いと思います。

表計算ソフトを学ぶためには、Excelの解説書が役立ちます。関数、データパイロット(Excelの名称はピボットテーブル)の使い方はほぼ同じなので、参考にしてください。

サンプルマクロ

Kuma (2010-06-29 13:56:09 (火))

はっしさんがこのQAに書かれた要望内容を満足するには、マクロで処理するしかない様に思えますが。。
サンプルマクロを作りましたので、添付ファイル「回答サンプル.ods」をダウンロードし、下記操作で動作状況を見て下さい。

「ツール」-->「マクロ」-->「マクロの実行」-->「回答サンプル.ods」-->「Standard」-->「Module1」-->マクロ名Matrixを「実行」

添付マクロの内容は、はっしさんがこのQAに書かれた6行3列データのみに対応しています。
この内容で良ければ、マクロの修正の仕方を再度書き込みます。

その場合次の点が明確である必要があります。
(1)200行のデータ位置は、「D列、E列、F列の各1行目から」とセルの位置を決めているのか、否か。
(2)対戦表(の様に思えた)を作成する位置は[G列1行]セルを左上隅にする、と決めているのか、否か。

修正の手間は少量です。
マクロを作った事がなくても上記(1)(2)の決めさえしっかりしておけば、添付サンプルに書かれたマクロの数値を修正し、コピー貼り付けで所望のcalcに利用すれば楽です。

使い方/高度な使い方」ページ確認しました

はっし (2010-07-01 00:21:18 (木))

M.Kamataki様、Kuma様 ありがとうございます。
返事が遅くなり、申し訳ありません。

M.Kamataki
早速ですが、使い方/高度な使い方ページ確認し、ご指摘どおりでした。
サンプルを作成しましたので確認してみてください。


再度、ご教授お願いします。


Kuma様
マクロの提案、ありがとうございます。
まずはM.Kamataki様の提案を試してみようと思っております。
どうしても納得いく結果が得られなかった場合、マクロでの
方法も試してみようと持っていますので、現段階では
大変申し訳ありませんが、一旦ご遠慮させていただくことに
なってしまうことをご容赦願います。
また、マクロ以外、特にデータパイロットを使用しないで
関数式を使っての処理方法などでの案があれば是非教えて頂きたい
と思います。引き続き宜しくお願いします。

添削

M.Kamataki (2010-07-01 05:35:36 (木))

質問サンプル.ods を添削してみました。以下のファイルです。

filefaq5_431sample1.ods

D4からD23は、DECIMAL関数を使って整数化しています。D4を以下のように変更。

=DECIMAL((IF(G4="";"";IF(LEN(G4)=5;LEFT(G4;2);LEFT(G4;1))));10)

D5からD23は、D4のコピー&貼り付けです。

DECIMAL関数のほかにVALUE関数も文字列を整数化できるので、E5からE23は、以下のようなE4のVALUE関数を使った式をコピー&貼り付けています。

=VALUE(IF(G4="";"";IF(LEN(G4)=3;RIGHT(G4;1);RIGHT(G4;2))))


データパイロットの表は、合計列などは必要無さそうなので、「詳細」ボタンを押して表示される結果欄の設定でオフにしています。

データパイロット以外

ike@九州 (2010-07-01 08:53:20 (木))

スマートで負荷をかけないのはデータパイロット(全く思い付きませんでした)なのですが、どうしても抜ける部分が気になったり整形面で云々の場合…

Excel の場合でも検索すれば複数条件の場合の例が数多くあります。複数条件の為の合成した列を一つ挿入します。
条件が 3 個以上でも合成列を一つ用意すれば OK なのです。

ところが、質問サンプルでは既に"項目"列が複数の条件に当て嵌まっているのでそのまま利用でき、後は MATCH で行を求め INDEX でデータを指定 ISERROR でエラー回避ですね。
(数式セルが多くなるとパフォーマンスが悪くなる事は知っているべきです)


#INDEX パラメータ間違ってました m(_ _)m

解決

はっし (2010-07-02 00:09:46 (金))

M.Kamataki様 ありがとうございました。

とりあえずは作成・使用の目処が付きそうです。
ただ、元データが変更された場合はその都度データパイロットを
呼び出して実行しないとならないようですね。
ただ、頻繁に数値の変更は無いと思われるので変更時のみ
更新という形で行えば済みそうです。

いろいろと質問不備などもありお手数おかけしました。
ありがとうございました。

ike@九州様 ありがとうございます。
当初、ike@九州様案をイメージしていました。
サンプル確認させていただきました。
実際、200行×2列分の処理を考えると
作成も大変ですが、ご指摘の『数式セルが多くなるとパフォーマンスが悪くなる事は知っているべきです』も納得です。

ただ、試みたい気持ちもあるので、今後、検討する方向では考えています。

ありがとうございました。

今後も何かとお世話になる機会があると思いますので
宜しくお願い致します。

お名前:
題名:


添付ファイル: filefaq5_431sample2.ods 933件 [詳細] filefaq5_431sample1.ods 976件 [詳細] file質問サンプル.ods 976件 [詳細] file回答サンプル.ods 548件 [詳細] filefaq5-431.ods 1176件 [詳細]