[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『表数値を用いて計算』(つぶ)
表のデータを用いて計算する関数を教えて欲しいです。
仮に下記の表があったとします。
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」と名称の後に番号が並んでいるのですが、
リストボックスから番号(5桁〜6桁)のみをを読み取り、
その番号をもとに計算させることはできるのでしょうか??
よろしくお願いします。
(つぶ) 2015/10/02(金) 13:43
「******* 8484」 だと番号は4桁になるが本来のデータでは5桁から6桁ということか?
また、番号は数値のみか数値以外も入るのか?
名称と番号の間にはスペースが入るのか? (ねむねむ) 2015/10/02(金) 13:48
もう一つ確認。
数値表のほうだが、番号は文字列なのか、それとも数値を入力して表示形式であたまの「0」を表示させているのかどちらだろうか? (ねむねむ) 2015/10/02(金) 13:55
スペースの件ですが、
一個スペースだけでなく、結構なパターンがありました。
例えば、
「******* 08484」
「*** 08484」
「* 08484」
など、ある程度文字と数値を綺麗にならべる為に、
2個スペースや3個スペースが入ります。
また、本来のデータは例えの様に綺麗でなく、
半角スペースと全角スペースが混ざり合ってたりして、
ならんで見える風になってます。
念のため補足ですが、必ず末尾が番号になります。
番号のなかに、ハイフン等なども入りません。
宜しくお願いします。
(つぶ) 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
=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
=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.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
お教え頂いた式で、表作成進めそうです!!
ですが、すぐに壁にぶつかりそうなので、
その時は、宜しくお願いします。。。
(つぶ) 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
1つ質問なんですが(見当違いかもしれませんが)、
教えて頂いた関数で、計算した値をコピーして貼り付けを行ってるんですが、
普通の貼り付けでは、式も一緒に貼り付けられちゃいまして上手く無く、
形式を選択して貼り付け→値では、セルの書式設定まで反映されないので上手くない無いです。
値と書式を一緒に貼り付ける方法は、あるのでしょうか?
(つぶ) 2015/10/05(月) 14:50
>値と書式を一緒に貼り付ける方法は、あるのでしょうか? 一回でまとめて、だとマクロということになる。
マクロを使わないならば「貼り付け」後、「値の貼り付け」、または「値の貼り付け」後、「書式の貼り付け」ではどうか?
あと、主題が最初の質問と変わる場合は改めて質問を行うほうがいいだろう。 (ねむねむ) 2015/10/06(火) 09:08
>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
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.