Как сделать статистический отчет в excel?

Застосування описової статистики в Microsoft Excel

Користувачі Ексель знають, що дана програма має дуже широкий набір статистичних функцій, за рівнем яких вона цілком може позмагатися зі спеціалізованими додатками. Але крім того, у Excel є інструмент, за допомогою якого проводиться обробка даних з цілого ряду основних статистичних показників буквально в один клік.

Цей інструмент називається «Описова статистика». З його допомогою можна в дуже короткі терміни, використавши ресурси програми, обробити масив даних і отримати про нього інформацію з цілого ряду статистичних критеріїв. Давайте поглянемо, як працює цей інструмент, і зупинимося на деяких нюансах роботи з ним.

Використання описової статистики

Під описової статистикою розуміють систематизацію емпіричних даних з цілого ряду основних статистичних критеріїв. Причому на основі отриманого результату з цих підсумкових показників можна сформувати загальні висновки про досліджуваному масиві даних.

У Ексель існує окремий інструмент, що входить в «Пакет аналізу», за допомогою якого можна провести даний вид обробки даних. Він так і називається «Описова статистика». Серед критеріїв, які вираховує даний інструмент наступні показники:

  • медіана;
  • Мода;
  • дисперсія;
  • середнє;
  • Стандартне відхилення;
  • Стандартна помилка;
  • Асиметричність і ін.

Розглянемо, як працює цей інструмент на прикладі Excel 2010, хоча даний алгоритм застосуємо також в Excel 2007 і в більш пізніх версіях даної програми.

Підключення «Пакета аналізу»

Як вже було сказано вище, інструмент «Описова статистика» входить в більш широкий набір функцій, який прийнято називати Пакет аналізу. Але справа в тому, що за замовчуванням дана надбудова в Ексель відключена. Тому, якщо ви до сих пір її не включили, то для використання можливостей описової статистики, доведеться це зробити.

  1. Переходимо у вкладку «Файл». Далі виробляємо переміщення в пункт «Параметри».

В активувати вікні параметрів переміщаємося в підрозділ «Надбудови». У самій нижній частині вікна знаходиться поле «Управління». Потрібно в ньому переставити перемикач в позицію «Надбудови Excel», якщо він знаходиться в іншому положенні. Слідом за цим тиснемо на кнопку «Перейти . ».

  • Запускається вікно стандартних надбудов Excel. Близько найменування «Пакет аналізу» ставимо прапорець. Потім тиснемо на кнопку «OK».
  • Після вищевказаних дій надбудова Пакет аналізу буде активована і стане доступною у вкладці «Дані» Ексель. Тепер ми зможемо використовувати на практиці інструменти описової статистики.

    Застосування інструменту «Описова статистика»

    Тепер подивимося, як інструмент описова статистика можна застосувати на практиці. Для цих цілей використовуємо готову таблицю.

      Переходимо у вкладку «Дані» і виконуємо клацання по кнопці «Аналіз даних», яка розміщена на стрічці в блоці інструментів «Аналіз».

    Відкривається список інструментів, представлених в пакеті аналізу. Шукаємо найменування «Описова статистика», виділяємо його і клацаємо по кнопці «OK».

    Після виконання даних дій безпосередньо запускається вікно «Описова статистика».

    В поле «Вхідний інтервал» вказуємо адресу діапазону, який буде піддаватися обробці цим інструментом. Причому вказуємо його разом з шапкою таблиці. Для того, щоб внести потрібні нам координати, встановлюємо курсор в вказане поле. Потім, затиснувши ліву кнопку миші, виділяємо на аркуші відповідну табличну область. Як бачимо, її координати тут же відображаються в поле. Так як ми захопили дані разом з шапкою, то близько параметра «Мітки в першому рядку» слід встановити прапорець. Тут же вибираємо тип групування, переставивши перемикач в позицію «По стовпцях» або «За рядками». У нашому випадку підходить варіант «По стовпцях», але в інших випадках, можливо, доведеться виставити перемикач інакше.

    Вище ми говорили виключно про вхідних даних. Тепер переходимо до розбору налаштувань параметрів виведення, які розташовані в цьому ж вікні формування описової статистики. Перш за все, нам потрібно визначитися, куди саме будуть виводитися оброблені дані:

    У першому випадку потрібно вказати конкретний діапазон на поточному аркуші або його верхнє ліве вічко, куди буде виводитися оброблена інформація. У другому випадку слід вказати назву конкретного листа даної книги, де буде відображатися результат обробки. Якщо листа з таким найменуванням в даний момент немає, то він буде створений автоматично після того, як ви натиснете на кнопку «OK». У третьому випадку ніяких додаткових параметрів вказувати не потрібно, так як дані будуть виводитися в окремому файлі Excel (книзі). Ми вибираємо висновок результатів на новому робочому аркуші під назвою «Підсумки».

    Далі, якщо ви хочете щоб виводилася також підсумкова статистика, то потрібно встановити прапорець біля відповідного пункту. Також можна встановити рівень надійності, поставивши галочку біля відповідного значення. За замовчуванням він буде дорівнює 95%, але його можна змінити, внісши інші числа в полі праворуч.

    Крім цього, можна встановити галочки в пунктах «K-ий найменший» і «K-ий найбільший», встановивши значення у відповідних полях. Але в нашому випадку цей параметр так само, як і попередній, не є обов’язковим, тому прапорці ми не ставимо.

    Після того, як всі зазначені дані внесені, тиснемо на кнопку «OK».

    Після виконання цих дій таблиця з описової статистикою виводиться на окремому аркуші, який був нами названий «Підсумки». Як бачимо, дані представлені сумбурно, тому їх слід відредагувати, розширивши відповідні колонки для більш зручного перегляду.

  • Після того, як дані «причесані» можна приступати до їх безпосереднього аналізу. Як бачимо, за допомогою інструменту описової статистики були розраховані наступні показники:
    • асиметричність;
    • інтервал;
    • мінімум;
    • Стандартне відхилення;
    • Дисперсія вибірки;
    • максимум;
    • сума;
    • ексцес;
    • середнє;
    • Стандартна помилка;
    • медіана;
    • Мода;
    • Рахунок.
  • Якщо якісь з вищевказаних даних для конкретного виду аналізу не потрібні, то їх можна видалити, щоб вони не заважали. Далі проводиться аналіз з урахуванням статистичних закономірностей.

    Як бачимо, за допомогою інструменту «Описова статистика» можна відразу отримати результат за цілою низкою критеріїв, які в іншому випадку розраховувалися із застосуванням окремо призначеної для кожного розрахунку функцією, що зайняло б чимало часу у користувача. А так, всі ці розрахунки можна отримати практично в один клік, використавши відповідний інструмент — Пакета аналізу.

    Excel-лайфхаки для тех, кто занимается отчётностью и обработкой данных

    В этом посте Ренат Шагабутдинов, ассистент генерального директора издательства «Манн, Иванов и Фербер», делится классными Excel-лайфхаками. Приведённые советы будут полезны для всех, кто занимается различной отчётностью, обработкой данных и созданием презентаций.

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

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

    Читать еще:  Как сделать синусоиду в excel?

    Простые альтернативы ВПР и ГПР, если искомые значения не в первом столбце таблицы: ПРОСМОТР, ИНДЕКС+ПОИСКПОЗ

    Функции ВПР (VLOOKUP) и ГПР (HLOOKUP) работают только в том случае, если искомые значения находятся в первом столбце или строке той таблицы, из которой вы планируете получить данные.

    В остальных случаях есть два варианта:

    1. Использовать функцию ПРОСМОТР (LOOKUP).
      У неё следующий синтаксис: ПРОСМОТР (искомое_значение; вектор_просмотра; вектор_результата). Но для её корректной работы нужно, чтобы значения диапазона вектор_просмотра были отсортированы по возрастанию:
    2. Использовать сочетание функций ПОИСКПОЗ (MATCH) и ИНДЕКС (INDEX).
      Функция ПОИСКПОЗ возвращает порядковый номер элемента в массиве (с её помощью вы можете найти, в какой строке таблицы искомый элемент), а функция ИНДЕКС возвращает элемент массива с заданным номером (который мы и узнаем с помощью функции ПОИСКПОЗ).Синтаксис функций:
      • ПОИСКПОЗ (искомое_значение; массив_поиска; тип_сопоставления) — для нашего случая нам нужен тип сопоставления «точное сопоставление», ему соответствует цифра 0.
      • ИНДЕКС (массив; номер_строки; [номер_столбца]). В данном случае номер столбца указывать не нужно, так как массив состоит из одной строки.

    Как быстро заполнить пустые ячейки в списке

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

    Выделяем столбец «Тематика», нажимаем на ленте в группе «Главная» кнопку «Найти и выделить» → «Выделить группу ячеек» → «Пустые ячейки» и начинаем ввод формулы (то есть ставим знак равно) и ссылаемся на ячейку сверху, просто нажимая стрелку вверх на клавиатуре. После этого нажимаем Ctrl + Enter. После этого можно сохранить полученные данные как значения, так как формулы больше не нужны:

    Как найти ошибки в формуле

    Вычисление отдельной части формулы

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

    1. Чтобы вычислить часть формулы прямо в строке формул, выделите эту часть и нажмите F9:

      В данном примере была проблема с функцией ПОИСК (SEARCH) — в ней были перепутаны местами аргументы. Важно помнить, что если вы не отмените вычисление части функции и нажмёте Enter, то вычисленная часть так и останется числом.
    2. Нажмите на кнопку «Вычислить формулу» в группе «Формулы» на ленте:


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

    Как определить, от чего зависит или на что ссылается формула

    Чтобы определить, от каких ячеек зависит формула, в группе «Формулы» на ленте нажмите на кнопку «Влияющие ячейки»:

    Появляются стрелки, указывающие, от чего зависит результат вычислений.

    Если отображается символ, выделенный на картинке красным цветом, то формула зависит от ячеек, находящихся на других листах или в других книгах:

    Щёлкнув на него, мы увидим, где именно находятся влияющие ячейки или диапазоны:

    Рядом с кнопкой «Влияющие ячейки» находится кнопка «Зависимые ячейки», работающая аналогично: она отображает стрелки от активной ячейки с формулой к ячейкам, которые зависят от неё.

    Кнопка «Убрать стрелки», расположенная в том же блоке, позволяет убрать стрелки к влияющим ячейкам, стрелки к зависимым ячейкам или же оба типа стрелок сразу:

    Как найти сумму (количество, среднее) значений ячеек с нескольких листов

    Допустим, у вас есть несколько однотипных листов с данными, которые вы хотите сложить, посчитать или обработать как-то иначе:

    Для этого в ячейку, в которой вы хотите видеть результат, введите стандартную формулу, например СУММ (SUM), и укажите в аргументе через двоеточие название первого и последнего листов из списка тех листов, что вам нужно обработать:

    Вы получите сумму ячеек с адресом B3 с листов «Данные1», «Данные2», «Данные3»:

    Такая адресация работает для листов, расположенных последовательно. Синтаксис следующий: =ФУНКЦИЯ (первый_лист:последний_лист!ссылка на диапазон).

    Как автоматически строить шаблонные фразы

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

    • Объединяем текст с помощью знака & (можете заменить его функцией СЦЕПИТЬ (CONCATENATE), но в этом нет особого смысла).
    • Текст всегда записывается в кавычках, ссылки на ячейки с текстом — всегда без.
    • Чтобы получить служебный символ «кавычки», используем функцию СИМВОЛ (CHAR) с аргументом 32.

    Пример создания шаблонной фразы с помощью формул:

    В данном случае, кроме функции СИМВОЛ (CHAR) (для отображения кавычек) используется функция ЕСЛИ (IF), позволяющая изменять текст в зависимости от того, наблюдается ли положительная динамика продаж, и функция ТЕКСТ (TEXT), позволяющая отобразить число в любом формате. Её синтаксис описан ниже:

    ТЕКСТ (значение; формат)

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

    Автоматизировать можно и более сложные тексты. В моей практике была автоматизация длинных, но рутинных комментариев к управленческой отчётности в формате «ПОКАЗАТЕЛЬ упал/вырос на XX относительно плана в основном из-за роста/снижения ФАКТОРА1 на XX, роста/снижения ФАКТОРА2 на YY…» с меняющимся списком факторов. Если вы пишете такие комментарии часто и процесс их написания можно алгоритмизировать — стоит один раз озадачиться созданием формулы или макроса, которые избавят вас хотя бы от части работы.

    Как сохранить данные в каждой ячейке после объединения

    При объединении ячеек сохраняется только одно значение. Excel предупреждает об этом при попытке объединить ячейки:

    Соответственно, если у вас была формула, зависящая от каждой ячейки, она перестанет работать после их объединения (ошибка #Н/Д в строках 3–4 примера):

    Чтобы объединить ячейки и при этом сохранить данные в каждой из них (возможно, у вас есть формула, как в этом абстрактном примере; возможно, вы хотите объединить ячейки, но сохранить все данные на будущее или скрыть их намеренно), объедините любые ячейки на листе, выделите их, а затем с помощью команды «Формат по образцу» перенесите форматирование на те ячейки, которые вам и нужно объединить:

    Как построить сводную из нескольких источников данных

    Если вам нужно построить сводную сразу из нескольких источников данных, придётся добавить на ленту или панель быстрого доступа «Мастер сводных таблиц и диаграмм», в котором есть такая опция.

    Сделать это можно следующим образом: «Файл» → «Параметры» → «Панель быстрого доступа» → «Все команды» → «Мастер сводных таблиц и диаграмм» → «Добавить»:

    После этого на ленте появится соответствующая иконка, нажатие на которую вызывает того самого мастера:

    При щелчке на неё появляется диалоговое окно:

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

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

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

    Отчёт сводной таблицы готов. В фильтре «Страница 1» вы можете выбрать только один из источников данных, если это необходимо:

    Как рассчитать количество вхождений текста A в текст B («МТС тариф СуперМТС» — два вхождения аббревиатуры МТС)

    В данном примере в столбце A есть несколько текстовых строк, и наша задача — выяснить, сколько раз в каждой из них встречается искомый текст, расположенный в ячейке E1:

    Для решения этой задачи можно воспользоваться сложной формулой, состоящей из следующих функций:

    1. ДЛСТР (LEN) — вычисляет длину текста, единственный аргумент — текст. Пример: ДЛСТР (“машина”) = 6.
    2. ПОДСТАВИТЬ (SUBSTITUTE) — заменяет в текстовой строке определённый текст другим. Синтаксис: ПОДСТАВИТЬ (текст; стар_текст; нов_текст). Пример: ПОДСТАВИТЬ (“автомобиль”;“авто”;“”)= “мобиль”.
    3. ПРОПИСН (UPPER) — заменяет все символы в строке на прописные. Единственный аргумент — текст. Пример: ПРОПИСН (“машина”) = “МАШИНА”. Эта функция понадобится нам, чтобы делать поиск без учёта регистра. Ведь ПРОПИСН(“машина”)=ПРОПИСН(“Машина”)

    Чтобы найти вхождение определённой текстовой строки в другую, нужно удалить все её вхождения в исходную и сравнить длину полученной строки с исходной:

    ДЛСТР(“Тариф МТС Супер МТС”) – ДЛСТР(“Тариф Супер”) = 6

    А затем разделить эту разницу на длину той строки, которую мы искали:

    6 / ДЛСТР (“МТС”) = 2

    Именно два раза строка «МТС» входит в исходную.

    Осталось записать этот алгоритм на языке формул (обозначим «текстом» тот текст, в котором мы ищем вхождения, а «искомым» — тот, число вхождений которого нас интересует):

    В нашем примере формула выглядит следующим образом:

    Создание красивого отчёта

    Допустим у нас имеется вот такая база данных по продажам, из которой нужно сделать отчёт:

    Вы создали небольшую сводную таблицу (Вставка – Сводная таблица):

    Но вашему начальнику не нравится внешний вид отчёта, и он хочет видеть что-то похожее на это:

    То есть мы имеем несколько ощутимых трудностей:

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

    2. Из всей сводной для отчёта вам нужны не все данные, а только конкретные модели Ford по Питеру — придётся руками фильтровать.

    3. Стандартные итоги в сводной нам не подходят, т.к. нужны суммы по выручке в зелёных ячейках, но среднее по месяцу в итогах — сводная так не умеет.

    4. Полученные в сводной результаты — ещё не конец, нам необходимо произвести с ними какие-то дополнительные вычисления: пересчитать выручку в тысячах, добавить прогноз на апрель, сравнить этот год с прошлым. Многое из перечисленного в сводных или невозможно в принципе, или делается весьма мучительно и долго с помощью вычисляемых полей и объектов.

    5. Нужно построить по результатам хитрую диаграмму (обычные сводные диаграммы имеют много ограничений).

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

    1 способ. Прямая ссылка на ячейку в сводной

    Это, что называется, решение проблемы «в лоб». Сделаем на отдельном листе заготовку отчёта:

    Теперь в ячейку D7 можно вручную прописать ссылку:

    Где Лист1 – имя листа со сводной таблицей, а B8 – нужная нам ячейка в сводной с данными по продажам Ford Fiesta за январь.

    При внешней простоте и очевидности у этого способа есть две проблемы:

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

    2. Завтра, после обновления, структура сводной таблицы может измениться — например, Fiesta может оказаться уже не третьей, а седьмой строкой, Focus переехать во вторую и т.д. И тогда все ссылки придётся переделывать.

    2 способ. Функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ

    Изящным решением всех этих проблем может стать функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (GETPIVOTDATA), которая умеет извлекать нужные нам данные из сводной, чтобы использовать их в других таблицах или расчетах.

    Чтобы её использовать, убедитесь, что при выделении любой ячейки сводной таблицы на вкладке Анализ (Analysis) или Параметры (Options) в выпадающем списке Параметры (Options) включена галочка Создать GetPivotData:

    Теперь выделите первую ячейку зелёного диапазона, введите знак «равно» и щёлкните по ячейке в сводной, которая содержит нужные данные, т.е. по B8, где лежит выручка Fiesta за январь. Вместо привычной ссылки Excel вставит функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ:

    («Выручка»;’Сводная для отчета’!$A$4;

    Давайте разберём её подробно:

    1. Первый её аргумент («Выручка») – это имя извлекаемого поля.

    2. Второй (Лист1!$A$4) — это адрес первой ячейки сводной таблицы, откуда мы берём данные. Этот параметр нужен, т.к. на листе может быть несколько сводных и Excel должен понимать, из какой именно нужно вытащить число.

    3. Все остальные аргументы начиная с третьего – это попарно название поля и его значение, т.е. в нашем случае это имя модели (Наименование=»Fiesta») и временной период (Дата=1). Поскольку в сводной была применена группировка дат по месяцам, то в функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ мы получили не имя месяца, а его номер. Если бы в исходной базе данных был столбец не с датой, а с названием месяца, то группировка была бы не нужна и вместо единички был бы просто «январь».

    У этой функции есть несколько серьёзных преимуществ перед обычной ссылкой на B8, которая приводила бы, на первый взгляд, к тому же результату. Главный плюс в том, что если завтра после обновления в сводной таблице изменится количество строк/столбцов или Ford Fiesta станет не третьей, а пятой строкой, то нам об этом волноваться уже не придётся – функция корректно извлечёт нужное нам значение. Достаточно только обновить сводную правой кнопкой мыши – и наша красивая форма отчёта «для шефа» пересчитается автоматически.

    Далее, замените в формуле «Fiesta» на $С7 (т.е. на ячейку с названием модели), а единичку на D$5 (т.е. ячейку с номером месяца) и допишите в конце формулы деление на 1000, т.к. нам нужно отобразить данные в тысячах. Затем нажмите на Enter и протяните формулу на оставшиеся зелёные ячейки.

    («Выручка»;’Сводная для отчета’!$A$4;»Наименование»;

    Функция извлечёт из сводной нужные нам данные, заполнив нашу корпоративную форму отчёта.

    Теперь с данными в диапазоне D7:F10 можно работать как с обычными формулами, а не как со сводной таблицей с её жёсткими ограничениями. Дальше можно спокойно считать любые итоги, динамику, прогнозы, строить любую диаграмму и т.д. Как украсить документ можно посмотреть здесь Минидиаграммы в ячейках

    Формула для прогноза: =ПРЕДСКАЗ($G$5;D7:F7;$D$5:$F$5)

    Формула для среднего значения: =СРЗНАЧ(D7:D10)

    Формула для динамики: =D13/D14-1

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

    Финансовый анализ в Excel с примером

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

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

    Пример финансового анализа предприятия в Excel

    Задача – изучение результатов финансовой деятельности и состояния предприятия. Цели:

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

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

    Анализ финансового состояния предприятия подразумевает

    • анализ баланса и отчета о прибылях и убытках;
    • анализ ликвидности баланса;
    • анализ платежеспособности, финансовой стабильности предприятия;
    • анализ деловой активности, состояния активов.

    Рассмотрим приемы анализа балансового отчета в Excel.

    Сначала составляем баланс (для примера – схематично, не используя все данные из формы 1).

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

    1. Представим значения на начало и на конец года в виде относительных величин. Формула: =B4/$B$14 (отношение значения на начало года к величине баланса на начало года). По такому же принципу составляем формулы для «конца года» и «пассива». Копируем на весь столбец. В новых столбцах устанавливаем процентный формат.
    2. Проанализируем динамику изменений в абсолютных величинах. Делаем дополнительный расчетный столбец, в котором отразим разницу между значением на конец года и на начало.
    3. Покажем изменения в относительных величинах. В новом расчетном столбце найдем разницу между относительными показателями конца года и начала.
    4. Чтобы найти динамику в процентах к значению показателя начала года, считаем отношение абсолютного показателя к значению начала года. Формула: =F4/B4. Копируем на весь столбец.
    5. По такому же принципу находим динамику в процентах для значений конца года.

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

    Какие результаты дает аналитический баланс:

    1. Валюта баланса в конце отчетного периода стала больше в сравнении с начальным периодом.
    2. Внеоборотные активы приращиваются с более высокими темпами, чем оборотные.
    3. Собственный капитал предприятия больше, чем заемный. Причем темпы роста собственного превышают динамику заемного.
    4. Кредиторская и дебиторская задолженность приращиваются примерно в одинаковом темпе.

    

    Статистический анализ данных в Excel

    Для реализации статистических методов в программе Excel предусмотрен огромный набор средств. Часть из них – встроенные функции. Специализированные способы обработки данных доступны в надстройке «Пакет анализа».

    Рассмотрим популярные статистические функции.

    1. СРЗНАЧ – Среднее значение – рассчитывает выборочное или генеральное среднее. Аргумент функции – набор чисел, указанный в виде ссылки на диапазон ячеек.
    2. ДИСП – для вычисления выборочной дисперсии (без учета текстовых и логических значений); ДИСПА – учитывает текстовые и логические значения. ДИСПР – для вычисления генеральной дисперсии (ДИСПРА – с учетом текстовых и логических параметров).
    3. Для нахождения квадратного корня из дисперсии – СТАНДОТКЛОН (для выборочного стандартного отклонения) и СТАНДОТКЛОНП (для генерального стандартного отклонения).
    4. Для нахождения моды совокупности данных применяется одноименная функция. Разделяет диапазон данных на две равные по числу элементов части МЕДИАНА.
    5. Размах варьирования – это разность между наибольшим и наименьшим значением совокупности данных. В Excel можно найти следующим образом:
    6. Проверить отклонение от нормального распределения позволяют функции СКОС (асимметрия) и ЭКСЦЕСС. Асимметрия отражает величину несимметричности распределения данных: большая часть значений больше или меньше среднего.

    В примере большая часть данных выше среднего, т.к. асимметрия больше «0».

    ЭКСЦЕСС сравнивает максимум экспериментального с максимумом нормального распределения.

    В примере максимум распределения экспериментальных данных выше нормального распределения.

    Рассмотрим, как для целей статистики применяется надстройка «Пакет анализа».

    Задача: Сгенерировать 400 случайных чисел с нормальным распределением. Оформить полный перечень статистических характеристик и гистограмму.

    1. Открываем меню инструмента «Анализ данных» на вкладка «Данные» (если данный инструмент недоступен, то нужно подключить настройку анализа ). Выбираем строку «Генерация случайных чисел».
    2. Вносим в поля диалогового окна следующие данные:
    3. После нажатия ОК:
    4. Зададим интервалы решения. Предположим, что их длины одинаковые и равны 3. Ставим курсор в ячейку В2. Вводим начальное число для автоматического составления интервалов. К примеру, 65. Далее нужно сделать доступной команду «Заполнить». Открываем меню «Параметры Excel» (кнопка «Офис»). Выполняем действия, изображенные на рисунке:
    5. На панели быстрого доступа появляется нужная кнопка. В выпадающем меню выбираем команду «Прогрессия». Заполняем диалоговое окно. В столбце В появятся интервалы разбиения.
    6. Первый результат работы:
    7. Снова открываем список инструмента «Анализ данных». Выбираем «Гистограмма». Заполняем диалоговое окно:
    8. Второй результат работы:
    9. Построить таблицу статистических характеристик поможет команда «Описательная статистика» (пакет «Анализ данных»). Диалоговое окно заполним следующим образом:

    После нажатия ОК отображаются основные статистические параметры по данному ряду.

    Это третий окончательный результат работы в данном примере.

    Ссылка на основную публикацию
    Adblock
    detector