Во всех версиях есть функция, позволяющая вычислять среднее арифметическое – это функция СРЗНАЧ [AVERAGE].
В данном случае видно, что вряд ли подобное значение может быть похоже на среднее значение ежедневного пробега. Отсюда и возникает необходимость рассчитывать и средневзвешенное, т.е. среднее значение в перерасчете на фактор-единицу, например, день. Подобной функции в Excel нет. Тем не менее, можно вычислить, используя математические функции.
1-й способ: СУММПРОИЗВ [SUMPRODUCT] и СУММ [SUM].
Необходимо перемножить каждый пробег на количество дней, за которые он был указан, а затем разделить на общее количество дней:
=СУММПРОИЗВ(A3:A11;B3:B11)/СУММ(B3:B11) или =SUMPRODUCT(A3:A11;B3:B11)/SUM(B3:B11)
2-й способ: СУММ [SUM] и формула массива.
Сперва вводится обычная формула, а затем завершается нажатием на клавиши CTRL+SHIFT+ALT (в результате в формуле появляются фигурные скобки – признак формулы массива).
{=СУММ(A3:A11*B3:B11)/СУММ(B3:B11)} или {=SUM(A3:A11*B3:B11)/SUM(B3:B11)}
В случае, если расчет средневзвешенного нужно произвести по более сложным формулам, то 2-й способ с использованием формулы массива дает больше гибкости в вычислениях.
Если использовать защиту листа, то в дальнейшем возможны действия в соответствии с предварительными настройками: атрибутами ячеек , настройкой возможностей редактирования защищенных ячеек (Разрешить изменение диапазонов [Allow Users To Edit Ranges]), а так же выбор в списке окна защиты листа:
Полный список возможностей выглядит следующим образом:
Очень часто многие делают следующую ошибку – устанавливают защиту листа, разрешая, использование автофильтра и сортировку. Однако, в дальнейшем, эти команды не будут доступны. Правильная последовательность действий в этом случае следующая:
- Установить Автофильтр – на вкладке Данные [Data], в группе Сортировка и фильтр [Sort&Filter], выбрать Фильтр [Filter].
- Поставить защиту листа – на вкладке Рецензирование [Review], в группе Изменения [Changes], выбрать Защитить лист [Protect Sheet].
После установки защиты, и сортировкой и фильтром можно пользоваться:
Крайне удивительно, что среди многообразия функций нет в чистом виде функции КВАРТАЛ.
Несколько способов определения квартала по дате я уже описывала (см. здесь). Эти способы можно использовать в любых расчетах Excel. Однако при работе в модели PowerPivot есть ещё один из простых способов – это задействовать текстовую функцию FORMAT.
FORMAT(Date; “формат”), где формат для определения номера квартала следует задать q.
С помощью функции FORMAT можно получать не только номер квартала, но и название месяца и название дня недели. для этого формат нужно задать следующим образом:
mmm – название месяца первыми 3-мя буквами
mmmm – название месяца целиком
ddd – день недели сокращенно
dddd – день недели целиком