| 失效链接处理 |
|
Learn Data Mining Through Excel PDF 下载
本站整理下载：
提取码：esz7
相关截图：
主要内容：
Chapter 1 Excel and Data Mining
Our formula to compute the commissions is based on the IF function. Enter the
following formula in cell D2:
=IF(C2<=50000,0,IF(C2<=100000,(C2-50000)*10%,50000*0.1+(C2-100000)*20%))
IF function has the basic form: IF(boolean-expression, true-value, false-value). In our
formula, if the sales in C2 are no more than 50000, 0 is returned. However, when C2 is more
than 50000, a nested IF function is used here as we have two more conditions to assess.
In the inner IF function, if C2<=100000, the amount more than 50000 is multiplied
by 0.1 (10%) and returned. Otherwise, C2 must be more than 100000, and therefore the
calculated result of the expression 50000*0.1+(C2-100000)*20%) is returned.
Our worksheet looks exactly like Figure 1-12.
Figure 1-12. Commissions calculated by using IF function
What if we want to count how many sales representatives have sales between
50000 and 100000 in season 1? To answer this question, we need to apply the function
COUNTIFS. In Excel, there is another function named COUNTIF. COUNTIFS is capable
of every task that COUNTIF is capable of, and there are many tasks for which we must
use COUNTIFS instead of COUNTIF.
The explanation of COUNTIFS from Microsoft Office Support states, “The
COUNTIFS function applies criteria to cells across multiple ranges and counts the
number of times all criteria are met.” Its syntax is like COUNTIFS(criteria_range1,
criteria_1, criteria_range2, criteria_2, …). Based on this understanding, enter the
following formula in cell E1:
=COUNTIFS(B2:B10,1,C2:C10,">50000",C2:C10,"<=100000")
|
| Java1234官方群25： | 
|
| Java1234官方群25： | 838462530 |
苏公网安备 32061202001004号
