计算机函数求销售额公式,利用excel函数公式中的LARGE函数和SUM函数提取前五名的销售额...

excel函数公式大全之利用LARGE函数和SUM函数提取前五名销售额,excel函数与公式在工作中使用非常的频繁,会不会使用公式直接决定了我们的工作效率,今天我们来学习一下提高我们工作效率的函数LARGE函数和SUM函数。今天我们要实现的功能是提取前三季度销售员销售额前五名的销售额是多少,具体如图所示:

8f1d5e0f0ff67d56e951601fa85b913a.png

利用excel函数公式中的LARGE函数和SUM函数提取前五名的销售额

对于SUM函数来说大家在熟悉不过了,今天就不多讲了。直接在销售综合输入公式:=SUM(B4:D4),向下填充即可。

b7a7e39f9aef376991dd5487062f2489.png

利用excel函数公式中的LARGE函数和SUM函数提取前五名的销售额

我们一起来认识一下LARGE函数,对于LARGE函数我们不是很熟悉,因此采用公式----插入函数-----搜索函数框内输入LARGE------点击转到

a43a79d7afa1fdb48d60075a17e9eb15.png

利用excel函数公式中的LARGE函数和SUM函数提取前五名的销售额

LARGE函数有两个参数构成,第一个参数数值区域。第二个参数K返回数组中的第K值,例如第一个最大值,第六个最大值。

d63f29d1315c21aba3fafb75c4832dbf.png

利用excel函数公式中的LARGE函数和SUM函数提取前五名的销售额

我们来简单的运用一下LARGE函数,数值区域我们采用1-5,返回数值区域中第二大值是多少,1-5中第二大值是4,我们一起来看看LARGE函数返回的是否正确。具体如下图所示:

9c47be1adf3caaf2bc4df88a93343441.png

利用excel函数公式中的LARGE函数和SUM函数提取前五名的销售额

我们一起来利用LARGE函数实现提取销售额前五位的销售额是多少,具体公式为:=LARGE($E$4:$E$11,1)

bd98389d73372e2369a1b82ff418400d.png

利用excel函数公式中的LARGE函数和SUM函数提取前五名的销售额

公式LARGE($E$4:$E$11,1)解读:$E$4:$E$11为销售总额的绝对引用,为什么要采用绝对引用呢,是为了数据进行填充的时候不发生错误。1表示$E$4:$E$11区域内第一个最大值为多少。

如果公式LARGE($E$4:$E$11,1)不采用绝对引用,向下填充的时候会发生什么,大家可以自己试一试。

谢谢大家的观看,我们下一期再见,如果有什么问题欢迎评论区留言或私信我们,如果有你想知道的函数公式,可以告诉我们,我们及时为你解答。

如果你对excel函数公式大全感兴趣,可以关注我们,在课程没更新之前,可以先看看我们前面的课程。

excel函数公式大全之利用SUM函数与RANK函数的组合对数值进行排名

excel函数公式大全之利用sum函数进行汇总以及对多个汇总进行求和

excel函数公式大全之利用AVERAGE函数与IF函数的组合标记平均值

  • 0
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
当然可以,以下是一个手写的Python函数np.histogram: ```python def histogram(a, bins=10, range=None): """ Compute the histogram of a set of data. Parameters ---------- a : array_like Input data. The histogram is computed over the flattened array. bins : int or sequence of scalars or str, optional If `bins` is an int, it defines the number of equal-width bins in the given range (10, by default). If `bins` is a sequence, it defines a monotonically increasing array of bin edges, including the rightmost edge, allowing for non-uniform bin widths. .. versionadded:: 1.11.0 If `bins` is a string from the list below, `histogram` will use the method chosen to calculate the optimal bin width and consequently the number of bins (see `Notes` for more detail on the estimators) from the data that falls within the requested range. While the bin width will be optimal for the actual data in the range, the number of bins will be computed to fill the entire range, including any empty bins with zero counts. Here are the possible values for the `bins` string: 'auto' Maximum of the 'sturges' and 'fd' estimators. Provides good all-around performance. 'fd' (Freedman Diaconis Estimator) Robust (resilient to outliers) estimator that takes into account data variability and data size. 'doane' An improved version of Sturges' estimator that works better with non-normal datasets. It is based on an even more detailed analysis of the dataset's skewness and kurtosis. 'scott' Less robust estimator that that takes into account data variability and data size. 'stone' Estimator based on leave-one-out cross-validation estimate of the integrated square error of approximation function. Can be regarded as a generalization of Scott's rule. More estimators are available in the `scipy.stats` module. .. versionadded:: 1.13.0 range : tuple or None, optional The lower and upper range of the bins. Lower and upper outliers are ignored. If not provided, `range` is ``(a.min(), a.max())``. Range has no effect if `bins` is a sequence. If `bins` is a sequence or `range` is specified, autoscaling is based on the specified bin range instead of the range of x. Returns ------- hist : ndarray The values of the histogram. See `density` and `weights` for a description of the possible semantics. bin_edges : ndarray Return the bin edges ``(length(hist)+1)``. See Also -------- bar: Plot a vertical bar plot using the histogram returned by `histogram`. hist2d: Make a 2D histogram plot. histogramdd: Make a multidimensional histogram plot. ``scipy.stats.histogram``: Compute histogram using scipy. Notes ----- All but the last (righthand-most) bin is half-open. In other words, if `bins` is ``[1, 2, 3, 4]``, then the first bin is ``[1, 2)`` (including 1, but excluding 2) and the second ``[2, 3)``. The last bin, however, is ``[4, 4]``, which includes 4. References ---------- .. [1] https://en.wikipedia.org/wiki/Histogram Examples -------- >>> np.histogram([1, 2, 1], bins=[0, 1, 2, 3]) (array([0, 2, 1]), array([0, 1, 2, 3])) >>> np.histogram(np.arange(4), bins=np.arange(5), density=True) (array([0.25, 0.25, 0.25, 0.25]), array([0, 1, 2, 3, 4])) >>> np.histogram([[1, 2, 1], [1, 0, 1]], bins=[0,1,2,3]) (array([1, 4, 1]), array([0, 1, 2, 3])) """ a = np.asarray(a) if not np.isfinite(a).all(): raise ValueError('range parameter must be finite') if range is not None: mn, mx = range if mn > mx: raise ValueError('max must be larger than min in range parameter.') if not (np.isfinite(mn) and np.isfinite(mx)): raise ValueError('range parameter must be finite.') keep = (a >= mn) & (a <= mx) if not keep.any(): return np.zeros(bins, dtype=np.intp), np.asarray([mn, mx]) a = a[keep] if bins is not None: bins = np.asarray(bins) if (np.diff(bins) < 0).any(): raise ValueError('bins must increase monotonically.') if len(bins) == 1: if np.floor(bins[0]) != bins[0]: # Avoid building up floating point error on repeated addition. widths = np.full(bins, (bins[0] - 0.5), dtype=np.float_) widths[0] = bins[0] - 0.5 else: widths = np.full(bins, bins[0]-0.5, dtype=np.float_) bins = np.arange(len(widths)+1, dtype=np.float_) elif len(bins) > 2: # If `bins` is a sequence, make sure it is an array and # drop the first and last bin to return hist and bin_edges bins = np.asarray(bins) if (np.diff(bins) < 0).any(): raise ValueError('bins must increase monotonically.') # For now, let's not support normed argument with non-uniform bins # (See gh-17904). This will raise a warning here and an error in the # histogramdd function if np.any(bins[1:-1] != np.around(bins[1:-1])): warnings.warn( "normed argument is ignored when non-uniform bins are used.") keep = (bins[:-1] != bins[1:]) if np.sum(keep) < len(bins)-1: # Some bins are non-empty. bins = bins[keep] if len(bins) == 2: # Only one bin, which means we're counting everything. return np.array([len(a)]), bins else: # This will ensure that we have len(bins)-1 bins. bins = np.concatenate( [bins[:1], bins[1:][keep], bins[-1:]]) widths = np.diff(bins) else: # All bins are empty. return np.zeros(len(bins)-1, int), bins else: # len(bins) == 2. widths = bins[1] - bins[0] else: bin_size = 1.0 if a.size > 0: bin_size = 1.01 * (a.max() - a.min()) / a.size bins = np.arange(a.min(), a.max() + bin_size, bin_size) widths = np.diff(bins) # We iterate over blocks here for two reasons: the first is that for # datasets with large numbers of bins, it is much faster to loop over the # blocks than to use fancy indexing to add contributions to the bins. # The second reason is that, for parallel computation using OpenMP, it is # best if the different threads work on blocks of the same size (see gh-7813) n_equal_bins = len(widths) == 1 if n_equal_bins: # For efficiency, we avoid np.digitize and compute the bin indices # directly as an integer array. The minimum value of a is mapped # to bin 0, the maximum to len(widths)-1. bin_edges = np.asarray([a.min() - 0.5*widths[0], a.max() + 0.5*widths[0]]) if bin_edges[0] == bin_edges[1]: # handle empty input array such as np.histogram([], bins=1) bin_edges[0] -= 0.5 bin_edges[1] += 0.5 if np.isinf(bin_edges).any(): raise ValueError( "autodetected range of [{}, {}] is not finite".format(*bin_edges)) bin_idxs = np.floor((a - bin_edges[0]) / widths[0]).astype(np.intp) else: bin_edges = bins bin_idxs = np.digitize(a, bins, right=False) - 1 # Ensure that the uppermost bin is non-empty if len(a) > 0 and bin_idxs[-1] == len(widths): bin_idxs[-1] -= 1 # Using bincount is faster than using np.histogram and then adding # the results together. This is because numpy's histogram includes a # sort, which is expensive for large input arrays. if weights is None: hist = np.bincount(bin_idxs, minlength=len(widths)) else: weights = np.asarray(weights) if weights.shape != a.shape: raise ValueError( 'weights should have the same shape as a.') if not np.isfinite(weights).all(): raise ValueError('weights must be finite') hist = np.bincount(bin_idxs, weights=weights, minlength=len(widths)) if density: # If the histogram is empty, then return an array of zeros. if hist.sum() == 0: return hist.astype(float), bin_edges else: hist = hist / hist.sum() if cumulative: hist = np.cumsum(hist) return hist, bin_edges ``` 这个函数的功能与NumPy的np.histogram函数基本相同。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值