[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『一定以上の数値の時に一定の値まで引き算をしたい』（kk2）
A
1 461
2 105
3 57
4 210
5 75
と数値があった場合に100以上の数値が入っているセルの値だけ
B C D
1 100 100 100
2 100 5 100
3 100 10
4 100
5 61
というような感じで100で引けるまで100で表示し最後に余りを表示させられるような、
関数もしくはマクロはできないでしょうか？
＜ 使用 Excel：Excel2010、使用 OS：Windows7 ＞
縦横入替えではありません・・・。
A1=461 A2=IF(A$1-(ROW()-2)*100<=0,"",IF(A$1-(ROW()-2)*100>100,100,A$1-(ROW()-2)*100)) A2を必要なだけ↓へコピー （カリーニン） 2015/10/06(火) 23:12
>100以上の数値が入っているセルの値だけ
これを見落としてました。 （カリーニン） 2015/10/06(火) 23:13
=IF(A$1>=100,IF(A$1-(ROW()-2)*100<=0,"",IF(A$1-(ROW()-2)*100>100,100,A$1-(ROW()-2)*100)),"")
100以下の場合は計算しないで詰める、なら、私の案は全くダメです。 （カリーニン） 2015/10/06(火) 23:15
ただこの関数もきちんと理解したいのでいくつか質問したいのですが、
(ROW()-2)*100
この部分で-2、*100をしている理由はなぜでしょうか？
また上記の式だと最後に余りが出てこないのですが、余りを表示させることはできますか？
（kk2） 2015/10/06(火) 23:23
B1 : =IF(INDEX($A:$A,COLUMN()-1)="","",IF(INDEX($A:$A,COLUMN()-1)>=ROW()*100,100,IF(INDEX($A:$A,COLUMN()-1)-100*(ROW()-1)<0,"",INDEX($A:$A,COLUMN()-1)-100*(ROW()-1))))
これを、下に、右にフィルコピー。
スマートな式は、早晩、専門家さんから。
（β） 2015/10/06(火) 23:27
>また上記の式だと最後に余りが出てこないのですが、余りを表示させることはできますか？
?? 余りは出てますよ。式を必要なだけ↓方向にコピーしてますか？
>(ROW()-2)*100 >この部分で-2、*100をしている理由はなぜでしょうか？
>-2 これは、A1に数値を入れて、数式をA2から入力しているからです。 A1からですと-1になり、A3からですと-3になります。
>*100
説明を考え中です。 （カリーニン） 2015/10/06(火) 23:34
カリーニンさんありがとうございます。
-2について理解することができました。
（kk2） 2015/10/06(火) 23:56
マクロの例です。
Sub test()
Dim r As Range
Dim c As Range
Dim cnt As Integer
Dim num As Integer
Set r = ActiveSheet.Range(ActiveSheet.Range("A1"), ActiveSheet.Range("A" & Rows.Count).End(xlUp))
cnt = 0
For Each c In r
If IsNumeric(c.Value) And c.Value >= 100 Then
cnt = cnt + 1
num = c.Value \ 100
ActiveSheet.Cells(1, cnt + 1).Resize(num).Value = 100
If c.Value Mod 100 <> 0 Then ActiveSheet.Cells(1, cnt + 1).Offset(num).Value = c.Value Mod 100
End If
Next c
Set r = Nothing
End Sub
（カリーニン） 2015/10/06(火) 23:57
う〜ん、、、私のマクロですと、最後のあまりが小数点以下が丸められてしまいますね・・・。 原因究明中です。 （カリーニン） 2015/10/07(水) 00:14
>If c.Value Mod 100 <> 0 Then ActiveSheet.Cells(1, cnt + 1).Offset(num).Value = c.Value Mod 100
↓に修正してください。
If c.Value - 100 * num <> 0 Then ActiveSheet.Cells(1, cnt + 1).Offset(num).Value = c.Value - 100 * num （カリーニン） 2015/10/07(水) 00:18
コードの解説です。
'変数の宣言 Dim r As Range Dim c As Range Dim cnt As Integer Dim num As Integer
'セル範囲/アクティブシートのA1からA列の最終行まで
Set r = ActiveSheet.Range(ActiveSheet.Range("A1"), ActiveSheet.Range("A" & Rows.Count).End(xlUp))
'カウントの初期化 cnt = 0
'セル範囲をループ For Each c In r
'セルが数値であり、かつ、100以上だったら If IsNumeric(c.Value) And c.Value >= 100 Then
'カウントアップ
cnt = cnt + 1
'セルの値を100で割った商を取得(1)
num = c.Value \ 100
A1から右方向にカウント分列移動したセルから(1)の数値分下方向に拡張したセル範囲に100を入力(2)
ActiveSheet.Cells(1, cnt + 1).Resize(num).Value = 100
'セルの値を100で割った余りが0でなかったら、余りを(2)の次の行に入力
If c.Value - 100 * num <> 0 Then ActiveSheet.Cells(1, cnt + 1).Offset(num).Value = c.Value - 100 * num
End If
'次へ Next c
'変数の解放 Set r = Nothing （カリーニン） 2015/10/07(水) 00:36
もう見ていないかもしれませんが。
>>もう一つこれを100以下の数字は省いて、100以上のみ結果表示することはできるでしょうか？
=IF(INDEX($A:$A,COLUMN()-1)<100,"",IF(INDEX($A:$A,COLUMN()-1)>=ROW()*100,100,IF(INDEX($A:$A,COLUMN()-1)-100*(ROW()-1)<0,"",INDEX($A:$A,COLUMN()-1)-100*(ROW()-1))))
とすれば、当該列は空白になります。
これを、空白列を作らず、左詰めということであれば、関数音痴のβの手には負えません。
（β） 2015/10/07(水) 05:14
>う〜ん、、、私のマクロですと、最後のあまりが小数点以下が丸められてしまいますね・・・。 >原因究明中です。
VBAヘルプの「Mod 演算子」に↓の解説がありました。
>解説
>剰余演算子は、数式 number1 を数式 number2 で除算し、その余りを演算結果 result として返します。このとき浮動小数点数は整数に丸められます。たとえば、次に示す式では、変数 A (演算結果 result) の値は 5 になります。
>A = 19 Mod 6.7 （カリーニン） 2015/10/07(水) 09:18
式での場合。
B1セルに =IF(COUNTIF($A:$A,">100")<COLUMN(A1),"",100) と入力して右へコピー。
B2セルに
=IFERROR(TEXT(IF(B1="","",MIN(100,INDEX($A$1:$A$100,SMALL(IF($A$1:$A$100>=100,ROW($A$1:$A$100),""),COLUMN(A1)))-SUM(B$1:B1))),"0;;")*1,"")
と入力してShiftキーとCtrlキーを押しながらEnterキーで式を確定（確定後、式が{}で囲まれればOK）
その後、右と下へフィルコピーでどうか？
追記
kk2さんすまない。
この式では小数点以下を考慮していなかった。 小数点以下もある場合は式中の「"0;;"」部分を「"G/標準;;"」としてくれ。
追記の2 何度もすまない。 式中に無駄な部分があった。
=IFERROR(TEXT(MIN(100,INDEX($A$1:$A$1000,SMALL(IF($A$1:$A$1000>=100,ROW($A$1:$A$1000),""),COLUMN(A1)))-SUM(B$1:B1)),"G/標準;;")*1,"") としてくれ。
（ねむねむ） 2015/10/07(水) 09:56
憚りながら・・・。
>B1セルに >=IF(COUNTIF($A:$A,">100")<COLUMN(A1),"",100)
↓と思われます。
=IF(COUNTIF($A:$A,">=100")<COLUMN(A1),"",100) （カリーニン） 2015/10/07(水) 10:36
カリーニンさんありがとう。
kk2さんすまない、カリーニンさんのとおりに修正してくれ。
（ねむねむ） 2015/10/07(水) 10:42
B1 =IFERROR(IF(INT(INDEX($A:$A,SMALL(INDEX(($A$1:$A$10<100)*10^16+ROW(A$1:A$10),),COLUMN(A1)))/100)+1<ROW(A1),"",IF(INT(INDEX($A:$A,SMALL(INDEX(($A$1:$A$10<100)*10^16+ROW(A$1:A$10),),COLUMN(A1)))/100)<ROW(A1),MOD(INDEX($A:$A,SMALL(INDEX(($A$1:$A$10<100)*10^16+ROW(A$1:A$10),),COLUMN(A1))),100),100)),"")
範囲コピー。
※だらだら。 （GobGob） 2015/10/07(水) 14:02
実際に使っているのがねむねむさんとカリーニンさんが書いていただいた式で
「１」、=IF(COUNTIF($A:$A,">=100")<COLUMN(A1),"",100) と
「２」、=
IFERROR(TEXT(MIN(100,INDEX($A$1:$A$1000,SMALL(IF($A$1:$A$1000>=100,ROW($A$1:$A$1000),""),COLUMN(A1)))-SUM(B$1:B1)),"0;;")*1,"")
なのですがB1とB2に入れた場合は当たり前ですが問題なく動きます。
今回式を入れるセルをF2に「１」、F3に「２」を入れようと以下のように修正してみたました。
461、105、57などのバラけさせたい数字はD2からD列に羅列しています。
=IF(COUNTIF($D:$D,">=100")<COLUMN(D2),"",100)
=IFERROR(TEXT(MIN(100,INDEX($D$2:$D$1000,SMALL(IF($D$2:$D$1000>=100,ROW($D$2:$D$1000),""),COLUMN(D2)))-SUM(F$2:F2)),"0;;")*1,"")
このように実際のセルの位置だけ変えたのですが最初のようにうまく計算してくれません。
どこが間違っているのでしょうか？
表記の仕方としては以下の通りです。
A B C D E F
1 461
2 105
3 57
4 210
5 75
と数値があった場合に100以上の数値が入っているセルの値だけ
A B C D E F G H I
1
2 461 100 100 100
3 105 100 5 100
4 57 100 10
5 210 100
5 75 61
とてもややこしいですがよろしくお願いします。
（kk2） 2015/10/18(日) 11:17
新しい使用前、使用後ですけど、使用前は 1行目から、で、使用後は元のＤ列も含めて 2行目からにしたいのですか？？
（β） 2015/10/18(日) 11:27
以下が正しい表記の仕方です
A B C D E F
1
2 461
3 105
4 57
5 210
6 75
と数値があった場合に100以上の数値が入っているセルの値だけ
A B C D E F G H I
1
2 461 100 100 100
3 105 100 5 100
4 57 100 10
5 210 100
5 75 61
（kk2） 2015/10/18(日) 11:39
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.