У Криса огромное количество данных на листе, и он хочет проанализировать данные на основе различных группировок внутри него. Например, у него есть данные в ячейках A2: B36001, где строка 1 содержит заголовки столбцов «Время» и «Сигнал». Он хочет разделить данные на группы, состоящие из некоторого произвольного числа последовательных значений, а затем извлечь для каждой группы среднее значение времени, среднее значение сигнала и стандартное отклонение сигнала. >
Самый простой способ справиться с этим типом требований – добавить столбец, который используется для указания номера группы для каждой строки. Выполните следующие действия:
- Поместите заголовок Группа в ячейку C1.
- В ячейку E1 введите число ценностей, которые должны быть в каждой группе. Например, если вы хотите, чтобы каждая группа содержала 10 последовательных значений, введите число 10 в ячейку E1.
- В ячейке C2 введите следующую формулу: = INT ((ROW () -ROW ($ C $ 2))/$ E $ 1) +1
- Скопируйте формулу из ячейки C2 в диапазон C3: C36001. Столбец C теперь содержит «номер группы» для каждой строки на основе значения в ячейке E1. Если E1 равно 10, вы получите 3600 групп, от 1 до 3600. Если E1 равно 100, вы получите 360 групп, от 1 до 360.
С установленными номерами групп вверх, вы готовы к анализу. Есть несколько способов сделать это. Один из способов – использовать возможности Excel для подсчета промежуточных итогов. Выберите одну из ячеек в области данных и выполните следующие действия:
- Выберите «Промежуточные итоги» в меню «Данные». Excel отображает диалоговое окно “Промежуточные итоги”.
- Измените раскрывающийся список При каждом изменении в на Группировать.
- Измените раскрывающийся список “Использовать функцию”, чтобы указать тип статистики, которую вы хотите вычислить для каждой группы.
- Измените область Добавить промежуточный итог в так, чтобы при необходимости выбирались только время или сигнал.
- Нажмите ОК.
Excel группирует данные и вычисляет промежуточные итоги в соответствии с указаниями. Вы можете скрыть детали (и показать только промежуточные итоги), щелкнув маленькую цифру 2 (с рамкой вокруг нее) в области контура слева от рабочего листа. Если позже вы захотите изменить то, что рассчитывается, или если вам нужно изменить количество элементов в каждой группе, просто удалите промежуточные итоги (Данные | Промежуточные итоги | Удалить все) и повторите вышеуказанные шаги.
Другой способ получить статистику из ваших данных – использовать сводную таблицу. Убедитесь, что в данных нет промежуточных итогов, и выберите ячейку в данных. Затем выполните следующие действия:
- Выберите «Сводная таблица и отчет сводной диаграммы» в меню «Данные». Excel запустит мастер сводных таблиц и сводных диаграмм.
- Нажмите “Далее”. (Выбор по умолчанию на шаге 1 – ОК.) Отображается шаг 2 мастера сводной таблицы и сводной диаграммы.
- Должен быть выбран весь диапазон ваших данных (A1: C36001). Нажмите “Далее. Отображается шаг 3 мастера сводных таблиц и диаграмм.
- Убедитесь, что выбран вариант «Новый рабочий лист», затем нажмите «Готово». Excel создает пустую сводную таблицу и отображает список полей.
- Перетащите поле Group в область Row.
- Перетащите поле Time в область Data.
- Перетащите поле Signal в область данных.
- Еще раз перетащите поле сигнала в область данных. В сводной таблице теперь должны отображаться «Счетчик времени», «Сумма сигнала» и «Сумма сигнала2» для каждой группы.
- В области «Данные» щелкните правой кнопкой мыши одно из «Счетчиков» Время ». Excel отображает контекстное меню.
- Выберите “Параметры поля” в контекстном меню. Excel отображает диалоговое окно «Поле сводной таблицы».
- В списке «Сводка» выберите «Среднее».
- Нажмите кнопку «ОК». Все метки «Счетчик времени» изменятся на «Среднее время».
- В области данных щелкните правой кнопкой мыши одну из меток «Сумма сигнала». Excel отображает контекстное меню.
- Выберите “Параметры поля” в контекстном меню. В Excel снова отображается диалоговое окно «Поле сводной таблицы».
- В списке «Суммировать» выберите «Среднее».
- Нажмите кнопку «ОК». Все метки «Сумма сигнала» изменятся на «Среднее значение сигнала».
- В области данных щелкните правой кнопкой мыши одну из меток «Сумма сигнала2». Excel отображает контекстное меню.
- Выберите “Параметры поля” в контекстном меню. Excel снова отображает диалоговое окно «Поле сводной таблицы».
- В списке «Суммировать» выберите StdDev.
- Нажмите «ОК». Все метки «Sum of Signal2» меняются на «StdDev of Signal2».
Теперь вы желаете получить данные. Вы можете предпочесть настройку, которая показывает только одну строку для каждой категории ваших данных. Если это так, просто щелкните заголовок данных и, удерживая кнопку мыши, медленно перемещайте мышь вправо. Когда вы перемещаете указатель мыши в область заголовка «Итого», маленький значок рядом с указателем мыши показывает «изменение» в макете. Отпустите кнопку мыши, и у вас будет только одна строка для каждой группы в ваших данных.
Если вам нужно изменить количество элементов данных в каждой группе, просто вернитесь к таблице данных и измените ячейку E1 на другое значение. Затем вы можете вернуться к сводной таблице, щелкнуть ее правой кнопкой мыши и выбрать «Обновить данные».
Источник: