白天和黑夜的平均温度在Excel中 [英] Average day and night temperature in excel

查看:177
本文介绍了白天和黑夜的平均温度在Excel中的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有一个很大的温度和湿度读数数据集,一年中每半小时收集一次。我想获得白天和黑夜的平均温度,但是到目前为止,我对averageifs的实验都失败了。

I have a large dataset of temperature and humidity readings, collected every half hour for a year. I'd like to get an average of day and night temperature, but my experiments with averageifs have so far failed. This is as far as I got.

=AVERAGEIFS(Sheet1!D$7:D$17622,Sheet1!$C$7:$C$17622, ">="&$A21, Sheet1!$C$7:$C$17622,"<" &$A22)

A21和A22中的值分别为6:00和18:00,格式为h:mm。

The values in A21 and A22 are 6:00 and 18:00, formatted as h:mm.

这只是返回DIV / 0!错误-我没有任何零,所以我不知道问题出在哪里。

This just returns a DIV/0! error - I don't have any zeros, so I don't know what the issue is.

我对日期使用完全相同的公式。这两个公式都基于相同的输入,像这样输入到每个单元格中:

I have exactly the same formula working with dates. Both formulas are drawing on the same input, entered into each cell like so:

1/02/2016  3:00:00 PM

我将非常感谢您提供的任何帮助(或者如果这是错误的论坛,请告诉我)。

I would be most grateful for any assistance (or if this is the wrong forum, let me know).

推荐答案

您的问题是您指定的范围大小。

Your problems is the size of the ranges you are specifying.

来自文档


语法:

AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2],...)

AVERAGEIFS 函数语法具有以下参数:

The AVERAGEIFS function syntax has the following arguments:

平均范围 必需。平均一个或多个单元格,包括数字或名称,数组或包含数字的引用。

Average_range Required. One or more cells to average, including numbers or names, arrays, or references that contain numbers.

Criteria_range1,criteria_range2,… Criteria_range1是 required ,随后的criteria_ranges是 optional 。在1到127个范围内评估相关标准。

Criteria_range1, criteria_range2, … Criteria_range1 is required, subsequent criteria_ranges are optional. 1 to 127 ranges in which to evaluate the associated criteria.

Criteria1,criteria2,... Criteria1是必填,后续条件为可选。 1到127条准则,其形式为数字,表达式,单元格引用或文本,用于定义要对哪些单元格取平均。例如,条件可以表示为 32 32 > 32 苹果 B4

Criteria1, criteria2, … Criteria1 is required, subsequent criteria are optional. 1 to 127 criteria in the form of a number, expression, cell reference, or text that define which cells will be averaged. For example, criteria can be expressed as 32, "32", ">32", "apples", or B4.

AVERAGEIFS AVERAGEIF 函数中的范围和条件参数不同c>每个criteria_range的大小和形状必须与sum_range 相同。

Unlike the range and criteria arguments in the AVERAGEIF function, in AVERAGEIFS each criteria_range must be the same size and shape as sum_range.

因此, AVERAGEIFS SUMIFS COUNTIFS (如果要计算的范围(例如,拳头参数)是A1: A10 ,那么您指定的条件范围的全部也必须跨越10个单元格。

So, with AVERAGEIFS, SUMIFS, COUNTIFS, if the range to calculate (the fist paramater) is, for example, A1:A10, then all of criteria ranges that you specify also have to span 10 cells.

您可能错误地使用了 AVERAGEIFS 而不是 AVERAGEIF

You may be incorrectly using AVERAGEIFS instead of AVERAGEIF.


  • 如果您只有一个条件行,并且希望对一或多个数据取平均值,请使用 AVERAGEIF

如果您有多个条件(例如,在此时间之前和之后),并且希望平均一个行数据,请使用 AVERAGEIFS

If you have multiple criteria (ie., before this time and after that time) and want to average one row of data, use AVERAGEIFS.

我不相信您可以使用多个条件+多个数据行 。如果这是您要尝试的操作,则可能需要在每行中使用 SUMIFS COUNTIFS ,然后从中计算出平均值。

I don't believe you can use either for multiple criteria + multiple data rows. If that's what you're trying to do, you may need to use SUMIFS and COUNTIFS for each row and then calculate an average from those.

例如:


  • 如果想平均白天的温度(06:00至18:00之间)

  • If you want to average daytime temperatures (between 06:00 and 18:00)

时间存储在 A1:X1 中,例如 00:00 23:00 (*并且也没有附加日期,即使不可见)

times are stored in A1:X1 like 00:00 to 23:00 (*and do not have dates attached to them, even if not 'visible')

温度存储在 A:X 列中(每行一天)

temperatures are stored in columns A:X (one day per row)

并且您要计算一天的平均白天温度,该温度存储在第2行中,

and you want to calculate average daytime temperatures, for one day, which is stored on Row 2,

...那么您可以使用:

=AVERAGEIFS(A2:X2,A1:X1,">="&6/24,A1:X1,"<&18/24)

注意,当根据开始/结束日期/ t进行总计时imes,通常最好使用:

Note that when taking totals based on start/end dates/times, it's generally best to use:


  • > = 开始日期/时间,以及

  • >= the Start date/time, and,

< 结束日期/时间

< the End date/time

...以防止 重叠

...to prevents overlaps.

此外,当进入类似这样的复杂公式时,请确保手动计算几行以比较并验证您是否正确指定公式标准。

Also, when getting into more complex formulas like this, be sure to manually calculate a few rows to compare and verify that you've correctly specified the formula criteria.

更多信息:

Office.com: AVERAGEIF函数

Office.com : AVERAGEIF function

办公室.com: SUMIFS函数

Office.com: COUNTIFS函数

Office.com : COUNTIFS function

Exceltip.com: Microsoft Excel中的AVERAGEIFS,SUMIFS和COUNTIFS函数

Exceltip.com : AVERAGEIFS, SUMIFS and COUNTIFS Functions in Microsoft Excel

这篇关于白天和黑夜的平均温度在Excel中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆