回答編集履歴
5
訂正
test
CHANGED
|
@@ -1,4 +1,4 @@
|
|
|
1
|
-
優先順位を順不同とし、1000
|
|
1
|
+
優先順位を順不同とし、1000以上の場合はそれ以上加算しない条件としました。
|
|
2
2
|
|
|
3
3
|
優先順位が上位です。
|
|
4
4
|
|
4
数式修正
test
CHANGED
|
@@ -6,6 +6,6 @@
|
|
|
6
6
|
|
|
7
7
|
```D3
|
|
8
8
|
|
|
9
|
-
=IF(COUNTIF($C3:C3,"〇")>0,"",IF(D$2=SUMIFS($B$3:$B$10,$C$3:$C$10,"<"&$C3,D$3:D$10,"〇"),"",IF(D$2*1.1>=SUMIFS($B$3:$B$10,$C$3:$C$10,"<"&$C3,D$3:D$10,"〇")+$B3,"〇","")))
|
|
9
|
+
=IF(COUNTIF($C3:C3,"〇")>0,"",IF(D$2<=SUMIFS($B$3:$B$10,$C$3:$C$10,"<"&$C3,D$3:D$10,"〇"),"",IF(D$2*1.1>=SUMIFS($B$3:$B$10,$C$3:$C$10,"<"&$C3,D$3:D$10,"〇")+$B3,"〇","")))
|
|
10
10
|
|
|
11
11
|
```
|
3
数式追加
test
CHANGED
|
@@ -1,13 +1,11 @@
|
|
|
1
|
-
|
|
1
|
+
優先順位を順不同とし、1000ちょうどの場合はそれ以上加算しない条件としました。
|
|
2
2
|
|
|
3
|
-
|
|
3
|
+
優先順位が上位です。
|
|
4
|
-
|
|
5
|
-
・予算×1.1を最大とする
|
|
6
4
|
|
|
7
5
|
D3~F10
|
|
8
6
|
|
|
9
7
|
```D3
|
|
10
8
|
|
|
11
|
-
=IF(COUNTIF($C3:C3,"〇")>0,"",IF(D$2
|
|
9
|
+
=IF(COUNTIF($C3:C3,"〇")>0,"",IF(D$2=SUMIFS($B$3:$B$10,$C$3:$C$10,"<"&$C3,D$3:D$10,"〇"),"",IF(D$2*1.1>=SUMIFS($B$3:$B$10,$C$3:$C$10,"<"&$C3,D$3:D$10,"〇")+$B3,"〇","")))
|
|
12
10
|
|
|
13
11
|
```
|
2
数式の改善
test
CHANGED
|
@@ -4,32 +4,10 @@
|
|
|
4
4
|
|
|
5
5
|
・予算×1.1を最大とする
|
|
6
6
|
|
|
7
|
+
D3~F10
|
|
8
|
+
|
|
7
9
|
```D3
|
|
8
10
|
|
|
9
|
-
=IF(D$2*1.1>=B3,"〇","")
|
|
11
|
+
=IF(COUNTIF($C3:C3,"〇")>0,"",IF(D$2*1.1>=SUMIF(D$2:D2,"〇",$B$2:$B2)+$B3,"〇",""))
|
|
10
12
|
|
|
11
13
|
```
|
|
12
|
-
|
|
13
|
-
D4~D10
|
|
14
|
-
|
|
15
|
-
```D4
|
|
16
|
-
|
|
17
|
-
=IF(D$2*1.1>=SUMIF($D$3:D3,"〇",$B$3:B3)+B4,"〇","")
|
|
18
|
-
|
|
19
|
-
```
|
|
20
|
-
|
|
21
|
-
E3,F3
|
|
22
|
-
|
|
23
|
-
```E3
|
|
24
|
-
|
|
25
|
-
=IF(COUNTIF($D3:D3,"〇")>0,"",IF(E$2*1.1>=$B3,"〇",""))
|
|
26
|
-
|
|
27
|
-
```
|
|
28
|
-
|
|
29
|
-
E4~F10
|
|
30
|
-
|
|
31
|
-
```E4
|
|
32
|
-
|
|
33
|
-
=IF(COUNTIF($D4:D4,"〇")>0,"",IF(E$2*1.1>=SUMIF(E$3:E3,"〇",$B$3:$B3)+$B4,"〇",""))
|
|
34
|
-
|
|
35
|
-
```
|
1
前提条件追加
test
CHANGED
|
@@ -1,3 +1,9 @@
|
|
|
1
|
+
下記前提条件での式を作ってみました。
|
|
2
|
+
|
|
3
|
+
・優先順位昇順で並べられている
|
|
4
|
+
|
|
5
|
+
・予算×1.1を最大とする
|
|
6
|
+
|
|
1
7
|
```D3
|
|
2
8
|
|
|
3
9
|
=IF(D$2*1.1>=B3,"〇","")
|