У Криса огромный объем данных на листе, и он хочет проанализировать данные на основе различных группировок внутри него. Например, у него есть данные в ячейках 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. Выберите «Сводная таблица и отчет сводной диаграммы» в меню «Данные». Excel запускает мастер сводных таблиц и сводных диаграмм.

  2. Нажмите кнопку «Далее. (На шаге 1 по умолчанию выбраны все в порядке.) Отображается шаг 2 сводной таблицы и мастера сводных диаграмм.

  3. Следует выбрать весь диапазон ваших данных (A1: C36001). Нажмите кнопку «Далее. Отображается шаг 3 мастера сводных таблиц и диаграмм.

  4. Убедитесь, что выбран New Worksheet, затем нажмите Finish. Excel создает пустую сводную таблицу и отображает список полей.

  5. Перетащите поле Group в область Row.

  6. Перетащите поле «Время» в область «Данные».

  7. Перетащите поле «Сигнал» в область «Данные».

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

для каждой группы.

  1. В области данных щелкните правой кнопкой мыши одну из меток «Счетчик времени». Excel отображает контекстное меню.

  2. Выберите «Параметры поля» в контекстном меню. Excel отображает диалоговое окно «Поле сводной таблицы».

  3. В списке Суммировать выберите Среднее.

  4. Щелкните ОК. Все метки «Счетчик времени» меняются на «Среднее время».

  5. В области данных щелкните правой кнопкой мыши одну из меток «Сумма сигнала». Excel отображает контекстное меню.

  6. Выберите «Параметры поля» в контекстном меню. Excel снова отображает диалоговое окно Поле сводной таблицы.

  7. В списке Суммировать выберите Среднее.

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

  9. В области данных щелкните правой кнопкой мыши одну из меток «Sum of Signal2».

Excel отображает контекстное меню.

  1. Выберите «Параметры поля» в контекстном меню. Excel снова отображает диалоговое окно Поле сводной таблицы.

  2. В списке Суммировать выберите StdDev.

  3. Щелкните ОК. Все метки «Sum of Signal2» меняются на «StdDev of Signal2».

Вы теперь, как данные желали. Вы можете предпочесть настройку, в которой для каждой категории данных отображается только одна строка. Если это так, просто щелкните заголовок данных и, удерживая кнопку мыши, медленно перемещайте мышь вправо. Когда вы перемещаете указатель мыши в область заголовка «Итого», маленький значок рядом с указателем мыши показывает «изменение» в макете. Отпустите кнопку мыши, и у вас будет только одна строка для каждой группы ваших данных.

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

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

Этот совет (2771) применим к Microsoft Excel 97, 2000, 2002 и 2003. Вы можете найти версию этого совета для ленточного интерфейса Excel (Excel 2007 и новее) здесь:

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