У Криса огромный объем данных на листе, и он хочет проанализировать данные на основе различных группировок внутри него. Например, у него есть данные в ячейках A2: B36001, где строка 1 содержит заголовки столбцов «Время» и «Сигнал». Он хочет разделить данные на группы, состоящие из некоторого произвольного количества последовательных значений, а затем извлечь для каждой группы среднее значение для времени, среднее значение для сигнала и стандартное отклонение для сигнала.

Самый простой способ справиться с этим типом требований — добавить столбец, который используется для указания номера группы для каждой строки. Выполните следующие действия:

  1. Поместите заголовок Group в ячейку C1.

  2. В ячейку E1 введите количество значений, которые должны быть в каждой группе.

Например, если вы хотите, чтобы каждая группа содержала 10 последовательных значений, введите число 10 в ячейку E1.

  1. В ячейке C2 введите эту формулу: = ЦЕЛОЕ СТРОКА () — СТРОКА ($ C $ 2 / $ E $ 1) +1. Скопируйте формулу из ячейки C2 в диапазон C3: C36001. Столбец C теперь содержит «номер группы» для каждой строки на основе значения в ячейке E1.

Если E1 равно 10, вы получите 3600 групп, от 1 до 3600. Если E1 равно 100, вы получите 360 групп, от 1 до 360.

После настройки номеров групп вы готовы к анализу. Есть несколько способов сделать это. Один из способов — использовать возможности Excel для вычисления промежуточных итогов. Выберите одну из ячеек в области данных и выполните следующие действия:

  1. Выберите «Промежуточные итоги» в меню «Данные». Excel отображает диалоговое окно «Промежуточный итог».

  2. Измените раскрывающийся список При каждом изменении в на Группировать.

  3. Измените раскрывающийся список «Использовать функцию», чтобы указать тип статистики, которую вы хотите вычислить для каждой группы.

  4. Измените область «Добавить промежуточный итог в», чтобы при необходимости выбирались только время или сигнал.

  5. Щелкните ОК.

Excel группирует и подытоживает данные в соответствии с указаниями. (Этот процесс может занять некоторое время в зависимости от размера ваших групп.) Вы можете скрыть детали и показывать только промежуточные итоги, щелкнув маленькую цифру 2 (с рамкой вокруг нее) в области контура слева от рабочего листа. Если позже вы захотите изменить то, что рассчитывается, или если вам нужно изменить количество элементов в каждой группе, просто удалите промежуточные итоги (используя кнопку в диалоговом окне «Промежуточные итоги») и повторите вышеуказанные шаги.

Другой способ получить статистику из ваших данных — использовать сводную таблицу. Убедитесь, что в данных нет промежуточных итогов, и выберите ячейку в данных. Затем выполните следующие действия:

  1. Откройте вкладку Вставка на ленте.

  2. Щелкните инструмент Сводная таблица. (Этот инструмент является первым слева от вкладки «Вставка».) Excel отображает диалоговое окно «Создать сводную таблицу».

  3. Щелкните ОК. (Параметры по умолчанию в диалоговом окне вполне подходят.) Excel создает пустую сводную таблицу и отображает список полей в правой части листа.

  4. Перетащите поле «Группа» в область «Ярлыки строк» ​​сразу под списком полей.

  5. Перетащите поле «Время» в область «Значения» сразу под списком полей.

  6. Перетащите поле «Сигнал» в область «Значения» сразу под списком полей.

  7. Еще раз перетащите поле «Сигнал» в область «Значения». В сводной таблице теперь должны отображаться «Счетчик времени», «Сумма сигнала» и «Сумма сигнала 2».

  8. В области «Значения» щелкните метку «Счетчик времени». Excel отображает контекстное меню.

  9. Выберите Параметры поля значений. Excel отображает диалоговое окно «Параметры поля значений».

  10. В списке Суммировать поле значения по выберите Среднее.

  11. Щелкните ОК. Ярлыки «Счетчик времени» изменятся на «Среднее время».

  12. В области значений щелкните метку «Сумма сигнала». Excel отображает контекстное меню.

  13. Выберите Параметры поля значений. Excel отображает диалоговое окно «Параметры поля значений».

  14. В списке Суммировать поле значения по выберите Среднее.

  15. Щелкните ОК. Ярлыки «Сумма сигнала» изменятся на «Среднее значение сигнала».

  16. В области «Значения» щелкните метку «Сумма сигнала2». Excel отображает контекстное меню.

  17. Выберите Параметры поля значений. Excel отображает диалоговое окно «Параметры поля значений».

  18. В списке Суммировать поле значения по выберите StdDev.

  19. Щелкните ОК. Ярлыки «Сумма сигнала» изменятся на «Стандартное отклонение сигнала».

Теперь у вас есть желаемые данные. Если вам нужно изменить количество элементов данных в каждой группе, просто вернитесь к листу данных и измените ячейку E1 на другое значение. Затем вы можете вернуться в сводную таблицу, отобразить вкладку «Параметры» на ленте и нажать кнопку «Обновить».

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (8628) применим к Microsoft Excel 2007, 2010, 2013, 2016, 2019 и Excel в Office 365. Вы можете найти версию этого совета для старого интерфейса меню Excel здесь:

link: / excel-Calculating_Statistical_Values_on_Different-Sized_Subsets_of_Data [Расчет статистических значений для подмножеств данных разного размера].