[[20151001115112]]　『表数値を用いて計算』（つぶ）　ページの最後に飛ぶ

『表数値を用いて計算』（つぶ）

表のデータを用いて計算する関数を教えて欲しいです。
仮に下記の表があったとします。

        A　　　　B　　　C　　　 D　 　　E　　　 F　　 　D
1               78      84      35      45      63      96
2      10      4452    1154    5224    2456    1122    5821
3      20      5680    2453    8484    0031    8902    2060
4      30      2984    7714    0108    1001    1998    7035
5      40      5681    6514    3765    8888    0845    6648
6      50      7989    3334    6868     ･　　　 ・　　　・
7      60　　　 ・　　　・
8      70　　　 ・　　　・
9      80

この時、
表内の数値が一覧してあるリストボックスで、
「8484」を選択した場合、
別のセルに「35」と「20」を取り出して、
＝35×20
とする関数を教えて欲しいです。

＜使用アプリ：Excel2000、使用 OS：WindowsXP ＞

 A15セルに「8484」と入れる場合。

 =SUMPRODUCT(($B$2:$G$9=A15)*A$2:$A$9*$B$1:$G$1)

 なお、
 =SUMPRODUCT(($B$2:$G$9=A15)*A$2:$A$9)
 で20
 =SUMPRODUCT(($B$2:$G$9=A15)$B$1:$G$1)
 で35を求められる。
（ねむねむ） 2015/10/01(木) 13:50

ねむねむさんバッチリでした！
凄いっす！ありがとございます！！

ねむねむさんの御蔭で表から数値が取り出せたのですが、
もう一つ壁にぶち当たりました。

今の使いかたとして、リストボックスから選択しまして、
「8484」と選択するのでは無くてですね、
「＊＊＊＊＊＊＊　8484」と名称の後に番号が並んでいるのですが、
リストボックスから番号（５桁～６桁）のみをを読み取り、
その番号をもとに計算させることはできるのでしょうか？？

よろしくお願いします。
（つぶ） 2015/10/02(金) 13:43

 「＊＊＊＊＊＊＊　8484」
 だと番号は4桁になるが本来のデータでは5桁から6桁ということか？

 また、番号は数値のみか数値以外も入るのか？

 名称と番号の間にはスペースが入るのか？
（ねむねむ） 2015/10/02(金) 13:48

 もう一つ確認。

 数値表のほうだが、番号は文字列なのか、それとも数値を入力して表示形式であたまの「0」を表示させているのかどちらだろうか？
（ねむねむ） 2015/10/02(金) 13:55

ねむねむさんこんにちは。
えっとですね、
本来のデータは、5桁～6桁です。基本5桁です。
例えが間違ってました。すみません。
また、＊＊＊＊＊のあとにスペースが入り番号となっております。
あと、番号は文字列です。

スペースの件ですが、
一個スペースだけでなく、結構なパターンがありました。
例えば、
　　「＊＊＊＊＊＊＊　08484」
　　「＊＊＊　　　　　08484」
　　「＊　　　　　　　08484」
など、ある程度文字と数値を綺麗にならべる為に、
２個スペースや３個スペースが入ります。
また、本来のデータは例えの様に綺麗でなく、
半角スペースと全角スペースが混ざり合ってたりして、
ならんで見える風になってます。　　　

念のため補足ですが、必ず末尾が番号になります。
番号のなかに、ハイフン等なども入りません。

宜しくお願いします。
（つぶ） 2015/10/02(金) 14:23

 =SUMPRODUCT(($B$2:$G$9=TRIM(RIGHT(A15,6))*A$2:$A$9*$B$1:$G$1)
 ではどうだろうか？

 RIGHT関数で右から6文字をとりだし、5文字の時の余計なスペースをTRIM関数で消している。

 追記
 すまない、カッコが一つ足りなかった。

 =SUMPRODUCT(($B$2:$G$9=TRIM(RIGHT(A15,6)))*A$2:$A$9*$B$1:$G$1)
 としてくれ。
（ねむねむ） 2015/10/02(金) 14:56

ねむねむさんカッコを付け足したらバッチリ動きました！
流石っす！超人っす！ありがとうございました！！
（つぶ） 2015/10/02(金) 15:27

ねむねむさん足し算にする時は、

=SUMPRODUCT(($B$2:$G$9=TRIM(RIGHT(A15,6)))*A$2:$A$9*$B$1:$G$1)

を、

=SUMPRODUCT(($B$2:$G$9=TRIM(RIGHT(A15,6)))*A$2:$A$9+$B$1:$G$1)

に変えるだけではダメなんですか？
前に教えて貰った式で、別々の求めるんでしょうか？
（つぶ） 2015/10/02(金) 17:45

 足す場合は
 =SUMPRODUCT(($B$2:$G$9=TRIM(RIGHT(A15,6)))*(A$2:$A$9+$B$1:$G$1))
 と足し算のほうにカッコをつけてみてくれ。
（ねむねむ） 2015/10/02(金) 17:54

おぉ～バッチリです！
ねむねむさんありがとうございます！
関数の魔術師ですね！！

因みに、横方向のみある特定の数値を引いて計算する場合は、

 =SUMPRODUCT(($B$2:$G$9=TRIM(RIGHT(A15,6)))*((A$2:$A$9)+($B$1:$G$1-0.05)))

で良いんですか？
（つぶ） 2015/10/02(金) 18:08

あと、何度もすみませんが、
掛け算した結果が、
「0.5以下は、0」にするのと「0.5以下は、0.5」
ってなる様に、

 =SUMPRODUCT(($B$2:$G$9=TRIM(RIGHT(A15,6)))*A$2:$A$9*$B$1:$G$1)

に組み込めますか？
（つぶ） 2015/10/02(金) 18:22

 >因みに、横方向のみある特定の数値を引いて計算する場合は、 
 >=SUMPRODUCT(($B$2:$G$9=TRIM(RIGHT(A15,6)))*((A$2:$A$9)+($B$1:$G$1-0.05)))
 こちらはこれで構わない。
 もっとも縦と横の足し算をしている場合、どちらから引いても結果は変わらないので
 =SUMPRODUCT(($B$2:$G$9=TRIM(RIGHT(A15,6)))*(A$2:$A$9+$B$1:$G$1-0.05))
 でもいいが。

 >「0.5以下は、0」にするのと「0.5以下は、0.5」 
 >ってなる様に、 
 すまないが一つ確認。

 「0.5以下」というのは結果そのものが「0.5以下」ということか、それとも結果の「0.5」以下部分ということか？

 結果が0.5以上のたとえば1.4とかは変更しないということか、1.4などの小数点以下が0.5以下だったらということか？
（ねむねむ） 2015/10/05(月) 09:12

ねむねむさんおはようございます。
「0.5以下は、0」というのは、結果そのものが「0.5以下」の場合、
結果を「0」にすることです。
それで、「0.5以下を0.5」というのは、結果そのものが「0.5以下」の場合、
結果を「0.5」にということです。

例です
「0.5以下は、0」
　　0.6→0.6
　　0.5→0.5
　　0.49→0
　　0.3→0
　　0.2→0

「0.5以下は、0.5」
　　0.6→0.6
　　0.5→0.5
　　0.49→0.5
　　0.3→0.5
　　0.2→0.5

よろしくお願いします。
（つぶ） 2015/10/05(月) 10:11

 0.5以下→0

 =IF(SUMPRODUCT(($B$2:$G$9=TRIM(RIGHT(A15,6)))*A$2:$A$9*$B$1:$G$1)<=0.5,0,SUMPRODUCT(($B$2:$G$9=TRIM(RIGHT(A15,6)))*A$2:$A$9*$B$1:$G$1))

 0.5以下→0.5

 =MAX(0.5,SUMPRODUCT(($B$2:$G$9=TRIM(RIGHT(A15,6)))*A$2:$A$9*$B$1:$G$1))

 ではどうだろうか？

 なお、値そのものではなく見た目だけ変わればいい場合（他から参照した場合は元の値が返る)は
 式はそのままで、条件付き書式で値が0.5以下の場合に「"0"」あるいは「"0.5"」とする方法もある。
（ねむねむ） 2015/10/05(月) 10:30

ねむねむさん完璧です！
何から何までありがとうございましたm(T口T)m
めちゃめちゃ助かりました！！
条件付き書式もお教え頂きありがとうございます！！

お教え頂いた式で、表作成進めそうです！！
ですが、すぐに壁にぶつかりそうなので、
その時は、宜しくお願いします。。。
（つぶ） 2015/10/05(月) 11:50

 0.5以下→0
 は
 =TEXT(SUMPRODUCT(($B$2:$G$9=TRIM(RIGHT(A15,6)))*A$2:$A$9*$B$1:$G$1),"[<=0.5]!0;G/標準")*1
 でも。

（ねむねむ） 2015/10/05(月) 11:59

ねむねむさん
=TEXT(SUMPRODUCT(($B$2:$G$9=TRIM(RIGHT(A15,6)))*A$2:$A$9*$B$1:$G$1),"[<=0.5]!0;G/標準")*1
でも、バッチリ動きました！ありがとうございます！！

１つ質問なんですが(見当違いかもしれませんが）、
教えて頂いた関数で、計算した値をコピーして貼り付けを行ってるんですが、
普通の貼り付けでは、式も一緒に貼り付けられちゃいまして上手く無く、
形式を選択して貼り付け→値では、セルの書式設定まで反映されないので上手くない無いです。
値と書式を一緒に貼り付ける方法は、あるのでしょうか？
（つぶ） 2015/10/05(月) 14:50

 >値と書式を一緒に貼り付ける方法は、あるのでしょうか？
 一回でまとめて、だとマクロということになる。

 マクロを使わないならば「貼り付け」後、「値の貼り付け」、または「値の貼り付け」後、「書式の貼り付け」ではどうか？

 あと、主題が最初の質問と変わる場合は改めて質問を行うほうがいいだろう。
（ねむねむ） 2015/10/06(火) 09:08

了解しました！すみません！
ねむねむさんありがとうございました！！
（つぶ） 2015/10/06(火) 14:46

 >0.6→0.6 
 >0.5→0.5 
 >0.49→0 
 >0.3→0 
 >0.2→0 
 すまない、例をきちんと見ていなかった。
 この例からすると「0.5以下を0」ではなく「0.5未満を0」となる。
 (0.5以下だと0.5も含むが0.5未満は0.5を含まない)

 未満の場合は
 <=0.5
 部分を
 <0.5
 としてくれ。
（ねむねむ） 2015/10/06(火) 14:56

ほんとだ！未満でした！すいません！
ありがとうございます！！
（つぶ） 2015/10/06(火) 15:18

コメント返信：

[ 一覧(最新更新順) ]

[[20151001115112]] 『表数値を用いて計算』（つぶ） ページの最後に飛ぶ

[[20151001115112]]　『表数値を用いて計算』（つぶ）　ページの最後に飛ぶ