Использование пакета анализа

Использование пакета анализа

Если вам нужно разработать сложные статистические или инженерные анализы, вы можете сэкономить этапы и время с помощью пакета анализа. Вы предоставляете данные и параметры для каждого анализа, и в этом средстве используются соответствующие статистические или инженерные функции для вычисления и отображения результатов в выходной таблице. Некоторые инструменты создают диаграммы в дополнение к выходным таблицам.

Функции анализа данных можно применять только на одном листе. Если анализ данных проводится в группе, состоящей из нескольких листов, то результаты будут выведены на первом листе, на остальных листах будут выведены пустые диапазоны, содержащие только форматы. Чтобы провести анализ данных на всех листах, повторите процедуру для каждого листа в отдельности.

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

Откройте вкладку Файл, нажмите кнопку Параметры и выберите категорию Надстройки.

Если вы используете Excel 2007, нажмите кнопку Microsoft Office и выберите пункт Параметры Excel .

В раскрывающемся списке Управление выберите пункт Надстройки Excel и нажмите кнопку Перейти.

Если вы используете Excel для Mac, в строке меню откройте вкладку Средства и в раскрывающемся списке выберите пункт Надстройки для Excel.

В диалоговом окне Надстройки установите флажок Пакет анализа, а затем нажмите кнопку ОК.

Если Пакет анализа отсутствует в списке поля Доступные надстройки, нажмите кнопку Обзор, чтобы выполнить поиск.

Если выводится сообщение о том, что пакет анализа не установлен на компьютере, нажмите кнопку Да, чтобы установить его.

Примечание: Чтобы включить функцию Visual Basic для приложений (VBA) для пакета анализа, вы можете загрузить надстройку «пакет анализа — VBA» таким же образом, как и при загрузке пакета анализа. В диалоговом окне Доступные надстройки установите флажок Пакет анализа — VBA .

Существует несколько видов дисперсионного анализа. Нужный вариант выбирается с учетом числа факторов и имеющихся выборок из генеральной совокупности.

Однофакторный дисперсионный анализ

Это средство выполняет простой анализ дисперсии для данных двух или более образцов. Анализ — это проверка гипотезы о том, что каждый образец выводится из того же основного распределения вероятности, что и для альтернативной гипотезы, для которой базовые распределения вероятностей не одинаковы. Если есть только два примера, вы можете использовать функцию на листе T.Проверка. В более чем двух выборках нет удобной обобщения с T.И может быть вызвана модель однофакторного дисперсионный проверки.

Двухфакторный дисперсионный анализ с повторениями

Этот инструмент анализа применяется, если данные можно систематизировать по двум параметрам. Например, в эксперименте по измерению высоты растений последние обрабатывали удобрениями от различных изготовителей (например, A, B, C) и содержали при различной температуре (например, низкой и высокой). Таким образом, для каждой из 6 возможных пар условий <удобрение, температура>, имеется одинаковый набор наблюдений за ростом растений. С помощью этого дисперсионного анализа можно проверить следующие гипотезы:

Извлечены ли данные о росте растений для различных марок удобрений из одной генеральной совокупности. Температура в этом анализе не учитывается.

Извлечены ли данные о росте растений для различных уровней температуры из одной генеральной совокупности. Марка удобрения в этом анализе не учитывается.

Извлечены ли шесть выборок, представляющих все пары значений <удобрение, температура>, используемые для оценки влияния различных марок удобрений (для первого пункта в списке) и уровней температуры (для второго пункта в списке), из одной генеральной совокупности. Альтернативная гипотеза предполагает, что влияние конкретных пар <удобрение, температура>превышает влияние отдельно удобрения и отдельно температуры.

Двухфакторный дисперсионный анализ без повторений

Этот инструмент анализа применяется, если данные можно систематизировать по двум параметрам, как в случае двухфакторного дисперсионного анализа с повторениями. Однако в таком анализе предполагается, что для каждой пары параметров есть только одно измерение (например, для каждой пары параметров <удобрение, температура>из предыдущего примера).

На листе КОРРЕЛ и Пирсон рассчитываются коэффициент корреляции между двумя переменными измерения, если измерения для каждой переменной отображаются для каждого из N субъектов. (Отсутствие наблюдения для какой-либо из тем приводит к тому, что эта тема пропускается в анализе.) Средство анализа корреляции особенно полезно, если для N тем используется более двух переменных измерения. Она предоставляет выходную таблицу, матрицу корреляции, которая показывает значение КОРРЕЛ (или Пирсона), примененное к каждой возможной паре переменных измерения.

Коэффициент корреляции, например Ковариация, — это мера экстента, в котором одновременно различаются две переменные измерения. В отличие от ковариации коэффициент корреляции масштабируется таким образом, чтобы его значение не зависело от единиц, в которых выражаются две переменные измерения. (Например, если две переменные измерения являются весом и высотой, значение коэффициента корреляции не меняется, если вес конвертируется из килограммов в килограммы). Значение любого коэффициента корреляции должно находиться в диапазоне от-1 до + 1 включительно.

Корреляционный анализ дает возможность установить, ассоциированы ли наборы данных по величине, т. е. большие значения из одного набора данных связаны с большими значениями другого набора (положительная корреляция) или наоборот, малые значения одного набора связаны с большими значениями другого (отрицательная корреляция), или данные двух диапазонов никак не связаны (нулевая корреляция).

Вы можете использовать инструменты корреляции и ковариации в одном и том же параметре, если у вас есть N различных переменных измерения, которые потратили на набор отдельных пользователей. Средства корреляции и ковариации предоставляют выходную таблицу, матрицу, которая показывает коэффициент корреляции или ковариацию соответственно между каждой парой переменных измерения. Разница заключается в том, что коэффициенты корреляции масштабируются в зависимости от-1 и + 1 включительно. Соответствующие ковариации не масштабируются. Как коэффициент корреляции, так и ковариация — это величины экстентов, в которых две переменные различны друг от друга.

Инструмент Ковариация вычисляет значение функции КОвариация на листе. P для каждой пары переменных измерения. (Прямое использование ковариации. Функция P вместо средства Ковариация является разумной альтернативой, если есть только две переменные измерения, т. е. N = 2.) Запись по диагонали в выходной таблице инструмента ковариации в строке i — это Ковариация переменной измерения i-ой. Это всего лишь дисперсия Генеральной совокупности для этой переменной, вычисленная функцией на листе var.P.

Ковариационный анализ дает возможность установить, ассоциированы ли наборы данных по величине, то есть большие значения из одного набора данных связаны с большими значениями другого набора (положительная ковариация) или наоборот, малые значения одного набора связаны с большими значениями другого (отрицательная ковариация), или данные двух диапазонов никак не связаны (ковариация близка к нулю).

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

Инструмент анализа «Экспоненциальное сглаживание» применяется для предсказания значения на основе прогноза для предыдущего периода, скорректированного с учетом погрешностей в этом прогнозе. При анализе используется константа сглаживания a, величина которой определяет степень влияния на прогнозы погрешностей в предыдущем прогнозе.

Примечание: Для константы сглаживания наиболее подходящими являются значения от 0,2 до 0,3. Эти значения показывают, что ошибка текущего прогноза установлена на уровне от 20 до 30 процентов ошибки предыдущего прогноза. Более высокие значения константы ускоряют отклик, но могут привести к непредсказуемым выбросам. Низкие значения константы могут привести к большим промежуткам между предсказанными значениями.

Двухвыборочный F-тест применяется для сравнения дисперсий двух генеральных совокупностей.

Например, можно использовать F-тест по выборкам результатов заплыва для каждой из двух команд. Это средство предоставляет результаты сравнения нулевой гипотезы о том, что эти две выборки взяты из распределения с равными дисперсиями, с гипотезой, предполагающей, что дисперсии различны в базовом распределении.

С помощью этого инструмента вычисляется значение f F-статистики (или F-коэффициент). Значение f, близкое к 1, показывает, что дисперсии генеральной совокупности равны. В таблице результатов, если f 1, «P(F

Инструмент «Анализ Фурье» применяется для решения задач в линейных системах и анализа периодических данных на основе метода быстрого преобразования Фурье (БПФ). Этот инструмент поддерживает также обратные преобразования, при этом инвертирование преобразованных данных возвращает исходные данные.

Инструмент «Гистограмма» применяется для вычисления выборочных и интегральных частот попадания данных в указанные интервалы значений. При этом рассчитываются числа попаданий для заданного диапазона ячеек.

Например, можно получить распределение успеваемости по шкале оценок в группе из 20 студентов. Таблица гистограммы состоит из границ шкалы оценок и групп студентов, уровень успеваемости которых находится между самой нижней границей и текущей границей. Наиболее часто встречающийся уровень является модой диапазона данных.

Совет: В Excel 2016 теперь можно создавать гистограммы и диаграммы Парето.

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

N — число предшествующих периодов, входящих в скользящее среднее;

A j — фактическое значение в момент времени j;

F j — прогнозируемое значение в момент времени j.

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

Инструмент «ранжирование и персентиль» формирует таблицу, содержащую порядковый и процентный ранги для каждого значения в наборе данных. Вы можете проанализировать относительные значения в наборе данных. Это средство использует функции ранжирования на листе. EQ и ПРОЦЕНТРАНГ. INC. Если вы хотите учитывать привязанные значения, используйте ранг. EQ , который обрабатывает привязанные значения в соответствии с одинаковым рангом или использует ранг.Функция AVG , возвращающая среднее значение ранга для привязанных значений.

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

Средство регрессия использует функцию листа ЛИНЕЙН.

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

Двухвыборочный t-тест проверяет равенство средних значений генеральной совокупности по каждой выборке. Три вида этого теста допускают следующие условия: равные дисперсии генерального распределения, дисперсии генеральной совокупности не равны, а также представление двух выборок до и после наблюдения по одному и тому же субъекту.

Для всех трех средств, перечисленных ниже, значение t вычисляется и отображается как «t-статистика» в выводимой таблице. В зависимости от данных это значение t может быть отрицательным или неотрицательным. Если предположить, что средние генеральной совокупности равны, при t =0 «P(T

Читайте также  Отображение и скрытие сетки на листе - Служба поддержки Office

Двухвыборочный z-тест для средних — это Двухвыборочный z-тест для средних и известных отклонений. Это средство используется для проверки гипотезы на то, что в двух или двусторонних вариантах есть различия между двумя единицами заполнения. Если вариативность неизвестна, то функция листа Z.Вместо этого следует использовать проверку .

При использовании этого инструмента следует внимательно просматривать результат. «P(Z = ABS(z)), вероятность z-значения, удаленного от 0 в том же направлении, что и наблюдаемое z-значение при одинаковых средних значениях генеральной совокупности. «P(Z = ABS(z) или Z

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

Источник:
http://support.microsoft.com/ru-ru/office/%D0%B8%D1%81%D0%BF%D0%BE%D0%BB%D1%8C%D0%B7%D0%BE%D0%B2%D0%B0%D0%BD%D0%B8%D0%B5-%D0%BF%D0%B0%D0%BA%D0%B5%D1%82%D0%B0-%D0%B0%D0%BD%D0%B0%D0%BB%D0%B8%D0%B7%D0%B0-6c67ccf0-f4a9-487c-8dec-bdb5a2cefab6

Включение блока инструментов «Анализ данных» в Microsoft Excel

Программа Excel – это не просто табличный редактор, но ещё и мощный инструмент для различных математических и статистических вычислений. В приложении имеется огромное число функций, предназначенных для этих задач. Правда, не все эти возможности по умолчанию активированы. Именно к таким скрытым функциям относится набор инструментов «Анализ данных». Давайте выясним, как его можно включить.

Включение блока инструментов

Чтобы воспользоваться возможностями, которые предоставляет функция «Анализ данных», нужно активировать группу инструментов «Пакет анализа», выполнив определенные действия в настройках Microsoft Excel. Алгоритм этих действий практически одинаков для версий программы 2010, 2013 и 2016 года, и имеет лишь незначительные отличия у версии 2007 года.

  1. Перейдите во вкладку «Файл». Если вы используете версию Microsoft Excel 2007, то вместо кнопки «Файл» нажмите значок Microsoft Office в верхнем левом углу окна.

В открывшемся окне параметров Эксель переходим в подраздел «Надстройки» (предпоследний в списке в левой части экрана).

В этом подразделе нас будет интересовать нижняя часть окна. Там представлен параметр «Управление». Если в выпадающей форме, относящейся к нему, стоит значение отличное от «Надстройки Excel», то нужно изменить его на указанное. Если же установлен именно этот пункт, то просто кликаем на кнопку «Перейти…» справа от него.

После выполнения этих действий указанная функция будет активирована, а её инструментарий доступен на ленте Excel.

Запуск функций группы «Анализ данных»

Теперь мы можем запустить любой из инструментов группы «Анализ данных».

    Переходим во вкладку «Данные».

  • После этого запускается окошко с большим перечнем различных инструментов, которые предлагает функция «Анализ данных». Среди них можно выделить следующие возможности:
    • Корреляция;
    • Гистограмма;
    • Регрессия;
    • Выборка;
    • Экспоненциальное сглаживание;
    • Генератор случайных чисел;
    • Описательная статистика;
    • Анализ Фурье;
    • Различные виды дисперсионного анализа и др.

    Выбираем ту функцию, которой хотим воспользоваться и жмем на кнопку «OK».

    Работа в каждой функции имеет свой собственный алгоритм действий. Использование некоторых инструментов группы «Анализ данных» описаны в отдельных уроках.

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

    Источник:
    http://lumpics.ru/how-to-enable-data-analysis-in-excel/

    4 техники анализа данных в Microsoft Excel

    Если вам по работе или учёбе приходится погружаться в океан цифр и искать в них подтверждение своих гипотез, вам определённо пригодятся эти техники работы в Microsoft Excel. Как их применять — показываем с помощью гифок.

    Тренер Учебного центра Softline с 2008 года.

    1. Сводные таблицы

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

    Полезное дополнение. Вы также можете создавать сводные диаграммы на основе сводных таблиц, которые будут автоматически обновляться при их изменении. Это полезно, если вам, например, нужно регулярно создавать отчёты по одним и тем же параметрам.

    Как работать

    Исходные данные могут быть любыми: данные по продажам, отгрузкам, доставкам и так далее.

    1. Откройте файл с таблицей, данные которой надо проанализировать.
    2. Выделите диапазон данных для анализа.
    3. Перейдите на вкладку «Вставка» → «Таблица» → «Сводная таблица» (для macOS на вкладке «Данные» в группе «Анализ»).
    4. Должно появиться диалоговое окно «Создание сводной таблицы».
    5. Настройте отображение данных, которые есть у вас в таблице.

    Перед нами таблица с неструктурированными данными. Мы можем их систематизировать и настроить отображение тех данных, которые есть у нас в таблице. «Сумму заказов» отправляем в «Значения», а «Продавцов», «Дату продажи» — в «Строки». По данным разных продавцов за разные годы тут же посчитались суммы. При необходимости можно развернуть каждый год, квартал или месяц — получим более детальную информацию за конкретный период.

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

    Можно её детализировать, например, по странам. Переносим «Страны».

    Можно посмотреть результаты по продавцам. Меняем «Страну» на «Продавцов». По продавцам результаты будут такие.

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

    Полезное дополнение. Координаты нигде прописывать не нужно — достаточно лишь корректно указать географическое название в таблице.

    Как работать

    1. Откройте файл с таблицей, данные которой нужно визуализировать. Например, с информацией по разным городам и странам.
    2. Подготовьте данные для отображения на карте: «Главная» → «Форматировать как таблицу».
    3. Выделите диапазон данных для анализа.
    4. На вкладке «Вставка» есть кнопка 3D-карта.

    Точки на карте — это наши города. Но просто города нам не очень интересны — интересно увидеть информацию, привязанную к этим городам. Например, суммы, которые можно отобразить через высоту столбика. При наведении курсора на столбик показывается сумма.

    Также достаточно информативной является круговая диаграмма по годам. Размер круга задаётся суммой.

    3. Лист прогнозов

    Зачастую в бизнес-процессах наблюдаются сезонные закономерности, которые необходимо учитывать при планировании. Лист прогноза — наиболее точный инструмент для прогнозирования в Excel, чем все функции, которые были до этого и есть сейчас. Его можно использовать для планирования деятельности коммерческих, финансовых, маркетинговых и других служб.

    Полезное дополнение. Для расчёта прогноза потребуются данные за более ранние периоды. Точность прогнозирования зависит от количества данных по периодам — лучше не меньше, чем за год. Вам требуются одинаковые интервалы между точками данных (например, месяц или равное количество дней).

    Как работать

    1. Откройте таблицу с данными за период и соответствующими ему показателями, например, от года.
    2. Выделите два ряда данных.
    3. На вкладке «Данные» в группе нажмите кнопку «Лист прогноза».
    4. В окне «Создание листа прогноза» выберите график или гистограмму для визуального представления прогноза.
    5. Выберите дату окончания прогноза.

    В примере ниже у нас есть данные за 2011, 2012 и 2013 годы. Важно указывать не числа, а именно временные периоды (то есть не 5 марта 2013 года, а март 2013-го).

    Для прогноза на 2014 год вам потребуются два ряда данных: даты и соответствующие им значения показателей. Выделяем оба ряда данных.

    На вкладке «Данные» в группе «Прогноз» нажимаем на «Лист прогноза». В появившемся окне «Создание листа прогноза» выбираем формат представления прогноза — график или гистограмму. В поле «Завершение прогноза» выбираем дату окончания, а затем нажимаем кнопку «Создать». Оранжевая линия — это и есть прогноз.

    4. Быстрый анализ

    Эта функциональность, пожалуй, первый шаг к тому, что можно назвать бизнес-анализом. Приятно, что эта функциональность реализована наиболее дружественным по отношению к пользователю способом: желаемый результат достигается буквально в несколько кликов. Ничего не нужно считать, не надо записывать никаких формул. Достаточно выделить нужный диапазон и выбрать, какой результат вы хотите получить.

    Полезное дополнение. Мгновенно можно создавать различные типы диаграмм или спарклайны (микрографики прямо в ячейке).

    Как работать

    1. Откройте таблицу с данными для анализа.
    2. Выделите нужный для анализа диапазон.
    3. При выделении диапазона внизу всегда появляется кнопка «Быстрый анализ». Она сразу предлагает совершить с данными несколько возможных действий. Например, найти итоги. Мы можем узнать суммы, они проставляются внизу.

    В быстром анализе также есть несколько вариантов форматирования. Посмотреть, какие значения больше, а какие меньше, можно в самих ячейках гистограммы.

    Также можно проставить в ячейках разноцветные значки: зелёные — наибольшие значения, красные — наименьшие.

    Надеемся, что эти приёмы помогут ускорить работу с анализом данных в Microsoft Excel и быстрее покорить вершины этого сложного, но такого полезного с точки зрения работы с цифрами приложения.

    Источник:
    http://lifehacker.ru/analiz-dannyx-v-ms-excel/

    Как проводить анализ данных в excel

    Microsoft Office Excel имея табличный вид отображения информации позволяет работать с большим количеством данных. Также редактор обладает огромной библиотекой функций, благодаря которой с массивами можно проводить различные математические операции. Помимо стандартных логических, финансовых и статистических формул, программа содержит в себе более сложные алгоритмы. Сегодня разберемся, как проводить анализ данных в excel, а также какие варианты существуют.

    Расположение

    Блок анализа находится во вкладке Данные на Панели инструментов. Если по каким-то причинам его нет, то сейчас подробно рассмотрим, как включить анализ данных в excel. Для примера воспользуемся редактором 2007 года.

    1. Нажимаете кнопку в верхней левой части документа и переходите к Параметрам.

    1. Ищете строку Надстройки и в правой части в самом низу нажимаете кнопку Перейти.

    Важно! Строка выбора рядом с кнопкой должна содержать Надстройки Excel.

    1. В новом диалоговом окне ставите галочку напротив Пакет анализа–VBA, затем нажимаете ОК.

    1. Если компонент не установлен, то редактор предложит инсталлировать его. Выбираете ДА.

    1. Далее программа проводит процесс установки в автоматическом режиме.

    1. После успешной инсталляции надстройки во вкладке Данные появится новый блок.

    Для версий редактора 2010,2013,2016 годов процесс включения будет одинаковым, только вместо кнопки Office необходимо перейти к параметрам excel через вкладку Файл.

    Возможности анализа данных

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

    1. Дисперсионный анализ позволяет искать зависимости в массиве данных на основании значимости неких средних значений, при этом может использоваться несколько групп данных. Бывает трех видов: однофакторный, двухфакторный с повторениями и без них.

    1. Корреляция нужна для отображения зависимости между двумя и более значениями или группами.
    2. Ковариация похожа на корреляцию, однако позволяет построить только линейную зависимость двух случайных величин.
    3. Экспоненциальное сглаживание один из основных приемов сглаживания числовых рядов.
    4. Анализ Фурье позволяет представить сложные математические функции в более простом виде при помощи тригонометрических функций.
    5. Гистограмма обрабатывает данные и подготавливает их для графического отображения в виде прямоугольников с одинаковым интервалом.
    6. Генератор случайных чисел добавляет в отмеченный диапазон ячеек случайные числа.

    1. Регрессия в глобальном смысле есть отход, но с точки зрения математики отображает зависимость одной переменной от других независимых величин.
    2. Выборка – инструмент, который позволяет сделать отбор чисел по определенным параметрам из большого массива данных.

    И это далеко не полный перечень инструментов, которые содержит установленный пакет данных.

    Прочие инструменты

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

    Читайте также  Как прибавить проценты к числу в Excel

    Как видите, Microsoft Office Excel имеет большое количество функций для анализа и отбора информации. При помощи надстроек можно расширить функционал программы более серьезными инструментами, которые позволяют решать специфические и сложные задачи. Все подпрограммы содержат в себе элементы статистического анализа. Такие дополнительные функции отлично подойдут для банковских организаций, финансовых компаний и статистических органов.

    Жми «Нравится» и получай только лучшие посты в Facebook ↓

    Источник:
    http://mir-tehnologiy.ru/analiz-dannyh-v-excel/

    Анализ данных «ЧТО-ЕСЛИ» в Excel

    С помощью средств анализа «что если» в Microsoft Excel можно экспериментировать с различными наборами значений в одной или нескольких формулах для изучения всех возможных результатов.

    Формулы и функции в Excel автоматически пересчитывают результат при изменении содержимого ячеек, на которые имеются ссылки в данной формуле или функции. Другими словами, можно отвечать на вопросы типа «что-если». Например, при анализе финансовой функции ПЛТ ответить на вопрос, что будет, если первый взнос при получении ипотечной ссуды будет составлять не 20% от цены, а 15%.

    Итак, проиллюстрируем проведение анализа данных «что-если» на примере работы функции ПЛТ, которая вычисляет величину выплаты по ссуде на основе постоянных выплат и постоянной процентной ставки.

    Вызов функции имеет вид: ПЛТ (ставка;кпер;пс;бс;тип)

    Ставка — процентная ставка по ссуде.

    Кпер — общее число выплат по ссуде.

    Пс — приведенная к текущему моменту стоимость или общая сумма, которая на текущий момент равноценна ряду будущих платежей, называемая также основной суммой.

    Бс — значение будущей стоимости, т. е. желаемого остатка средств после последней выплаты. Если этот аргумент опущен, предполагается, что он равен 0 (например, значение «бс» для займа равно 0).

    Тип — число 0 (ноль) или 1, обозначающее, когда должна производиться выплата.

    Рассмотрим пример использования функции ПЛТ в Exceel.

    Итак, требуется определить ежемесячные выплаты по займу в 20 000 руб., взятому на 16 месяцев под 11% годовых.

    Для решения задачи выделяем ячейку на рабочем листе Excel (в нашел случаи ячейка А1) и в строку формул вводим следующее выражение: =ПЛТ(11%/12; 16; 20000) (Рис.1.1)

    Рис. 1.1 — Ввод формулы Excel.

    Нажав на клавишу Enter , мы получаем величину ежемесячных выплат по ссуде, которая составит -1350 руб. Рис.1.2

    Рис. 1.2 – Величина ежемесячной выплаты по ссуде.

    При ином значении банковской учетной ставки, следует сделать исправления в ранее введенной функции в Excel.

    Другой подход к вычислению функции ПЛТ методом «что если» в Excel проиллюстрирован на Рис. 1.3. Функция ПЛТ определена в ячейке D7, а значения аргументов записаны в ячейках D2, D3 и D4. Для получения значения функции при новых значениях аргумента достаточно внести соответствующие изменения в исходные данные. В этом случаи в строке формул на рис.1.3 мы вводим не конкретное значение аргумента, а ссылку ни соответствующую ячейку.

    Рис. 1.3 — Пример расчета Excel, в котором исходные данные в отдельные ячейки

    При изменении любых значений на рис.3 результаты расчета автоматически обновляются в разделе Результат расчета.

    Вывод: Рассмотренный выше примеры показывают, что размещение исходных данных в отдельные ячейки упрощает анализ зависимости выходного результата от изменения исходных данных с использованием анализа данных «Что если» в Exceel.

    Подбор параметра в Excel

    При вычислении различных функций возникает вопрос: «Каким должно быть значение определенного аргумента функции, чтобы функция возвратила заданный результат?».

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

    Из расчетной части рис.1.3 видно, что при заданных исходных данных требуется ежемесячно выплачивать по 1350 руб. для погашения займа. Предположим, что по каким-то причинам кредитор имеется возможность выплачивать не более 1200 руб. в месяц. Спрашивается, какую максимальную величину ссуды может он запросить, если все прочие условия сохраняются?

    Для решения этой задачи выберем команду Данные > Анализ «что если» > Подбор параметра (рис. 2.1). В верхнем поле этого окна указывается ссылка на ячейку D7, в которой устанавливается желаемый результат (в нашем случае – это -1200 руб). В нижнее поле диалогового окна вставляется ссылка на ячейку, в которой хранится значение искомого параметра, т.е. D4.

    Рис. 2.1 — Диалоговое окно Подбор параметра в Excel

    При нажатии клавиши ОК мы получим максимальную сумму займа, при условии выплаты ежемесячно 1 200 руб. Рис.2.2

    Рис. 2.2 – Максимальная величина займа 17 783 руб.

    Вывод: Выполнение анализа «что-если» в Excel обеспечивает достаточно оперативную оценку влияния того или иного аргумента на результат вычисления.

    Проведение анализа на основе таблицы подстановки в Excel

    Таблицы подстановки для одной переменной.

    В Excel предусмотрено средство, позволяющее без особых усилий строить таблицу подстановки для одной и двух переменных.

    Рассмотрим способ построения так называемой таблицы подстановки для одной переменной, используя приведенный выше пример вычисления функции ПЛТ.

    Для построения таблицы подстановки необходимо подготовить исходные данные рис.3.1

    Рис. 3.1 – Подготовка исходных данных для построения таблицы подстановки Excel

    В ячейке G3 этой таблицы определена точно такая же формула, как и в ячейке D7. Первый столбец таблицы подстановки заполнен значениями аргумента функции ПЛТ, в зависимости от которого требуется проанализировать поведение финансовой функции (в нашем случае от 11 до 15%).

    Чтобы получить соответствующие значения функции во втором столбце, нужно выделить диапазон ячеек — F3:G7, и после этого выполнить команду меню Данные > Анализ «что если» > Таблица данных… . В результате появляется диалоговое окно этой команды (рис. 3.2).

    Это окно служит для задания абсолютного адреса рабочей ячейки, на которую ссылается расчетная функция (ячейка D2). В случае вертикальной организации таблицы подстановки ссылку на рабочую ячейку необходимо ввести в поле Подставлять значения по строкам.

    Рис. 3.2. — Диалоговое окно Таблица подстановки в Excel

    После щелчка на кнопке ОК столбец результатов таблицы подстановки будет заполнен (рис. 3.3).

    Рис.3.3. Таблица подстановки для одной переменной в Excel

    Таблица подстановки для двух переменных в Excel.

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

    Поставим задачу проследить характер изменения функции ПЛТ в зависимости от изменения годовой процентной ставки и срока погашения ссуды.

    Для начала, подготовить исходные данные на рабочем листе, как это показано на рис. 3.4

    В ячейке F2 таблицы подстановки определена точно такая же формула, как и в ячейке D7 в Excel. Первый столбец таблицы подстановки заполнен значениями годовой процентной ставки. Первая строка таблицы заполнена значениями срока вклада. Требуется в зависимости от изменения этих двух аргументов проанализировать поведение финансовой функции.

    Рис. 3.4 — Подготовка исходных данных для построения таблицы подстановки Excel

    Чтобы получить значения функции в таблице, выделяем диапазон ячеек F2:J7, который содержит исходные значения процентных ставок, исходные значения срока погашения ссуды и расчетную функцию. После этого нужно выполнить команду меню Данные > Анализ «что если» > Таблица подстановки. В результате появится диалоговое окно (рис. 3.5).

    Рис. 3.5 Диалоговое окно Excel Таблица подстановки

    Это окно служит для задания абсолютных адресов ячеек, на которые ссылается расчетная функция. После щелчка на кнопке ОК столбец результатов таблицы подстановки будет заполнен (рис.3.6).

    Рис. 3.6 Расчетные значения таблицы подстановки Excelдля двух переменных

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

    Проведение графического анализа в Excel.

    Графическое представление табличных данных, например в форме диаграммы, облегчает анализ функции, так как диаграмма отличается большей наглядностью.

    На рис. 3.7 и 3.8 представлены диаграммы, построенные на базе таблиц подстановки для одной-двух переменных соответственно. Так, для построения диаграммы для двух переменных выделим диапазон ячеек F3:J7 и выберем тип диаграммы «точечная». Затем следует отредактировать полученную диаграмму.

    Ежемесячные выплаты по ссуде

    Рис. 3.7 Диаграмма excel, построенная на основе диапазона ячеек F3:G7 таблицы подстановки для одной переменной (см. рис. 3.3)

    Ежемесячные выплаты по ссуде

    Рис. 3.8 — Диаграмма Excel, построенная на базе диапазона ячеек F3:J7 таблицы подстановки для двух переменных (см. рис. 3.6)

    Поиск решения в Exceel

    Существует достаточно широкий класс относительно сложных задач поиска оптимального решения, которые описываются системами уравнений с несколькими неизвестными и набором ограничений на решения. Для решения подобных задач весьма эффективным может оказаться средство Excel Поиск решения.

    Средство Поиск решения — это надстройка Excel. Для ее подключения следует выполнить команду меню Сервис > Надстройки. В появившемся диалоговом окне Надстройки нужно установить флажок опции Поиск решения.

    Характерные особенности задач, для решения которых предназначено данное средство, заключаются в следующем:

    имеется единственная цель, например максимизация прибыли, минимизация расходов и т.п.;

    имеются ограничения, выраженные в виде неравенств;

    имеются переменные, значения которых влияют на ограничения и оптимизируемую величину.

    Правильная формулировка ограничений — самая ответственная часть описания модели для поиска решения. Следует особенно внимательно следить за тем, чтобы задавать все объективно существующие ограничения. Неполнота описания ограничений приводит к неправильному решению.

    Следует различать линейные и нелинейные модели, поскольку для линейных моделей существуют быстрые и надежные методы поиска решения.

    Чтобы исключить использование общих более медленных методов для решения линейных задач, следует установить параметр Линейная модель в окне Параметры поиска решения.

    Решение задачи оптимизации.

    Для пояснения принципа работы средства Поиск решения рассмотрим пример, используя данные таблицы на рис. 4.1.

    Рис. 4.1 — Таблица Excel для определения количества товаров, приносящих максимальную прибыль

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

    Ячейка (Е7), в которую помещается ответ, называется целевой. Целевая ячейка содержит формулу, результат которой зависит от значений, содержащихся в других ячейках, называемых изменяемыми.

    Ограничения — это спецификации, которые применяются к целевой и изменяемым ячейкам для задания диапазона возможных значений.

    Предположим, что имеются следующие ограничения, которые необходимо учитывать при составлении плана выпуска продукции:

    общее число производимых товаров за отчетный период должно составлять ровно 1000 шт.;

    товар С пользуется наименьшим спросом, поэтому, как показал опыт, удается реализовать товар этого вида не более 140 шт.;

    на товары вида A, B, D имеются заказы соответственно на 50, 100 и 200 шт., которые необходимо выполнить.

    Для реализации процедуры поиска решения необходимо выполнить следующие действия.

    Ввести исходные данные, как это показано на рис. 4.1.

    • Выполнить команду меню Сервис > Поиск решения, чтобы вызвать диалоговое окно Поиск решения (рис. 4.2)
    • Установить курсор в поле Установить целевую ячейку диалогового окна и щелкнуть мышкой на целевой ячейке Е7 (рис. 4.2).
    • Установить курсор в поле Изменяя ячейки диалогового окна и выделить диапазон изменяемых ячеек С3:С6.
    • Установить курсор в поле Ограничения и щелкнуть на кнопке Добавить . В появившееся диалоговое окно, показанное на рис. 4.3, вводить поочередно все ограничения (рис. 4.4).
    • Щелкнуть на кнопке Выполнить диалогового окна Поиск решения.

    Результат поиска решения представлен на рис. 4.5.

    Рис. 4.2 – Диалоговое окно Поиск решений в Excel

    Рис 4.3 – Диалоговое отношение Добавление ограничений Excel

    Читайте также  Блокировка и разблокировка определенных областей защищенного листа

    Рис. 4.4. – Введение ограничения Excel

    После того как найдем оптимальное решение, мы можем выбрать одну из следующих возможностей:

    1) сохранить найденное решение;

    2) восстановить исходные значения в изменяемых ячейках;

    3) создать отчеты о процедуре поиска решения;

    4) щелкнуть на кнопке Сохранить сценарий. Сохраненный сценарий может быть использован в средстве Диспетчер сценариев.

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

    Информационные технологии. Другие материалы

    Источник:
    http://ya-znau.ru/znaniya/zn/217

    Анализ данных в Excel. Часть 1

    Рассказываем о простых функциях Excel, с которых можно начать его изучение

    Для работы с данными, которые представлены в табличном формате, можно использовать программы, установленные на компьютере – Microsoft Excel или Numbers (для Mac OS). Или воспользоваться практически аналогичной по функционалу онлайн-версией Google Sheets. Панели инструментов разных версий Excel отличаются, поэтому мы покажем возможности работы с этим инструментом в Google Sheets, которые на любом компьютере выглядят одинаково.

    • Для примера мы будем использовать набор данных с количеством созданных во время эпидемии коронавируса сайтов по продаже медицинских масок в русскоязычном сегменте интернета (использован в нашем материале «Дефицит доверия»).

    Как устроен Excel

    Пространство внутри программы похоже на лист бумаги с клетками. Каждая колонка здесь имеет свое название – по букве алфавита, а каждая строка – свой номер. У каждой ячейки есть свой адрес, который состоит из сочетания буквы столбца и номера строки – например, ячейка А1 или B2 – это чем-то похоже на игру в Морской бой. Сам файл похож на книгу со множеством листов. Нажимая на знак плюса в левом нижнем углу страницы, можно создавать новые листы, и например, помещать каждый набор данных на отдельный лист.

    Импорт данных

    Excel работает с различными форматами данных. Самое распространенное расширение табличного файла – это xlsx, в котором Excel по умолчанию сохраняет данные. Чтобы открыть файл в этом формате, необходимо нажать «Файл» – «Открыть» – и указать путь к файлу.

    Еще одно распространенное расширение – csv. Это текстовый файл, значения в котором разделены специальными символами – например, запятыми (отсюда и название – comma-separated values) или другими. Его можно открыть в обычном Блокноте. Там можно посмотреть содержимое файла, но чтобы обрабатывать такие данные, пригодится Excel. Чтобы открыть csv, необходимо нажать «Файл» – «Импортировать» – и указать путь к файлу.

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

    Предварительная работа с данными

    Когда данные загружены, первым делом стоит проверить, в удобном ли для работе виде они представлены. Важно, например, проверить, есть ли у столбцов (а иногда и строк) названия, это упростит работу с данными.

    Данные внутри ячеек в Excel представлены в разных форматах – в нашем примере это даты, текст или числа. Все виды форматов, с которыми работает программа, можно увидеть во вкладке меню «Формат». Перед работой с данными, стоит оценить, верно ли распознан их формат. Например, если числам придать формат текста, с ними нельзя будет производить вычисления. Менять их можно в том же разделе меню «Формат».

    Затем важно оценить, хватает ли данных или их стоит преобразовать для дальнейшего анализа. Рассмотрим на нашем примере. В наборе данных с количеством новых сайтов по продаже медицинских масок есть столбец с количеством сайтов в зоне «.рф», и столбец с количеством сайтов в зоне «.ru». Нас интересует общее количество сайтов в обеих зонах. Можно добавить еще один столбец, дать ему название «.рф и .ru» и самостоятельно заполнить. Сложить значения из двух столбцов («.рф» и «.ru») нам поможет формула.

    В программе можно использовать простые математические формулы – например, сложение. Нужно активировать ячейку, которую необходимо заполнить (в нашем примере D2), и ввести =. Именно со знака равенства начинается любая формула в Excel. Затем нужно указать адрес первой ячейки, поставить +, и указать адрес второй ячейки, которую мы хотим прибавить к первой, и нажать клавишу ввода (Enter или Return). Программа сама посчитает сумму двух ячеек. В формуле можно указывать столько ячеек, сколько понадобится сложить. Сложим значения из столбцов «маск.рф» и «mask.ru». Формула будет такой: =B2+C2.

    Автозаполнение ячеек

    Чтобы не проделывать ту же самую операцию для каждой даты в нашем примере, можно сразу применить формулу для всего диапазона данных. Для этого существует функция автозаполнения ячеек. При выделении ячейки, в которой введена формула, в ее правом нижнем углу появится специальный символ – квадрат. Нужно его зажать, растянуть до конца таблицы и затем отпустить курсор. Тогда формула автоматически подставится в каждую ячейку нашего диапазона, и в каждой новой ячейке появится своя сумма. То же самое можно сделать быстрее: кликнуть два раза на квадрат в правом нижнем углу ячейки с формулой, и она сама растянется до конца таблицы.

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

    Для подсчета суммы нескольких значений, существует специальная функция СУММ. Предположим, что мы хотим посчитать сумму сайтов, созданных за весь период времени, доступный нам в данных. Введите =, а затем название функции – СУММ. Как только вы начнете вводить название функции, рядом с ней появится всплывающее окно со списком всех доступных функций, начинающихся с этих символов. Если нажать на нужную функцию из этого списка, она появится в ячейке, а после нее автоматически появится открывающая скобка.

    Затем выделите курсором диапазон чисел, которые необходимо сложить, поставьте закрывающую скобку и нажмите клавишу ввода. В нашем примере функция будет выглядеть так: =СУММ(D2:D114). Этот же диапазон значений внутри скобок можно не выделять курсором, а указать вручную таким образом: адрес первой ячейки диапазона (D2), двоеточие, адрес последней ячейки диапазона (D114).

    Сортировка

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

    Для этого необходимо выделить курсором всю таблицу – нажать в меню «Данные» – «Сортировать диапазон». Затем отметить галочкой «Данные со строкой заголовка» – это необходимо, чтобы заголовки остались на месте и не участвовали в сортировке. Затем выбрать, по какому показателю (или заголовку столбца) нужно отсортировать данные. В нашем случае – это «Дата». Выбрать вид сортировки: «от А до Я» (от меньшего к большему) или «от Я до А» (от большего к меньшему). В нашем примере, чтобы даты отобразились в хронологическом порядке, надо отметить «от А до Я».

    Функцию сортировки можно использовать и для анализа данных. Например, нам интересно, в какой день было зарегистрировано самое большое количество сайтов по продаже медицинских масок. Для этого надо проделать те же действия, но в качестве параметра сортировки выбрать «Все сайты» и «от Я до А». Тогда максимальное количество сайтов отобразится в самом начале таблицы, и мы увидим, что пик регистрации таких сайтов пришелся на 4 апреля.

    Общая информация о данных

    То же самое можно узнать и другим способом. Google Sheets автоматически подсчитывают основные характеристики данных и показывают их в одном месте. Можно выделить любой участок данных – например, целый столбец с количеством сайтов – и в правом нижнем углу листа появится меню с основными показателями. Там есть сумма, среднее значение, максимум, минимум и другие значения. Этим удобно пользоваться, чтобы быстро получить предварительное представление о данных.

    Фильтрация

    Фильтры используют, чтобы отобразить только нужные для анализа данные, а лишние скрыть. Они же помогают извлекать из данных только те, которые соответствуют нужным критериям, что помогает при анализе. Чтобы создать фильтр, необходимо выделить таблицу, нажать в меню «Данные» – «Создать фильтр». После этого рядом с заголовками каждого столбца появятся перевернутые треугольники – это и есть меню фильтрации.

    Давайте узнаем, в какие дни сайты по продаже масок не регистрировались. Нужно нажать на меню фильтрации в столбце «Все сайты». В нем мы увидим список всех значений, которые есть в наборе данных. Нажмем на «Очистить» (по умолчанию выделены все значения, а нам нужно оставить только одно) и выберем только «0». После этого таблица изменит вид, и в ней будут показаны только строки с датами, когда сайты не регистрировались. Например, мы увидим, что этого не происходило на новогодних каникулах.

    Фильтрацию можно выполнять и по условиям. Давайте узнаем, в какие дни зарегистрировали больше 50 сайтов. Для этого в меню фильтрации надо выбрать «Фильтровать по условию», выбрать из списка условие «Больше» и в окно вписать значение – «50». Осталось только три даты, когда было зарегистрировано больше 50 сайтов – это первые три дня апреля.

    Есть и другие примеры условий. Например, мы хотим отобразить данные только за апрель. Нажимаем в меню фильтрации столбца «Дата» «Фильтровать по условию» – «Дата после» – «Точная дата» – и вводим «31.03.2020». Отобразились данные только за апрель, и теперь можно приступать к их анализу. Например, выделить этот столбец и в правом нижнем углу листа посмотреть общую информацию о данных. Мы увидим, например, что каждый день апреля, в среднем, регистрировали 34 таких сайта.

    Важно понимать, что фильтры не удаляют лишние данные из таблицы, а лишь скрывают их. Чтобы навсегда оставить данные только за апрель, их нужно сохранить как новый набор данных. Для этого можно скопировать таблицу с включенным фильтром и вставить на новый лист.

    Сохранение

    Google Sheets при наличии подключения к интернету самостоятельно сохраняют каждое действие и результаты работы. Если же вам понадобится сохранить обработанные данные на компьютер, это можно сделать так: выбрать в меню «Файл», нажать «Скачать» и выбрать из выпадающего меню необходимый формат – там есть как xlsx, так и csv, а также другие форматы.

    Источник:
    http://istories.media/workshops/2020/04/30/analiz-dannikh-v-excel-chast-/