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

Применение описательной статистики в Microsoft Excel

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

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

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

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

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

  • Медиана;
  • Мода;
  • Дисперсия;
  • Среднее;
  • Стандартное отклонение;
  • Стандартная ошибка;
  • Асимметричность и др.

Рассмотрим, как работает данный инструмент на примере Excel 2010, хотя данный алгоритм применим также в Excel 2007 и в более поздних версиях данной программы.

Подключение «Пакета анализа»

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

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

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

  • Запускается окно стандартных надстроек Excel. Около наименования «Пакет анализа» ставим флажок. Затем жмем на кнопку «OK».
  • После вышеуказанных действий надстройка Пакет анализа будет активирована и станет доступной во вкладке «Данные» Эксель. Теперь мы сможем использовать на практике инструменты описательной статистики.

    Применение инструмента «Описательная статистика»

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

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

    Открывается список инструментов, представленных в Пакете анализа. Ищем наименование «Описательная статистика», выделяем его и щелкаем по кнопке «OK».

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

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

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

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

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

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

    После того, как все указанные данные внесены, жмем на кнопку «OK».

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

  • После того, как данные «причесаны» можно приступать к их непосредственному анализу. Как видим, при помощи инструмента описательной статистики были рассчитаны следующие показатели:
    • Асимметричность;
    • Интервал;
    • Минимум;
    • Стандартное отклонение;
    • Дисперсия выборки;
    • Максимум;
    • Сумма;
    • Эксцесс;
    • Среднее;
    • Стандартная ошибка;
    • Медиана;
    • Мода;
    • Счет.
  • Если какие-то из вышеуказанных данных для конкретного вида анализа не нужны, то их можно удалить, чтобы они не мешали. Далее производится анализ с учетом статистических закономерностей.

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

    Отблагодарите автора, поделитесь статьей в социальных сетях.

    Статистические функции Excel, которые необходимо знать

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

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

    В рамках данной статьи мы не будем затрагивать такие популярные статистические функции Excel, как СЧЕТ и СЧЕТЕСЛИ, для них подготовлен отдельный урок.

    Статистическая функция СРЗНАЧ возвращает среднее арифметическое своих аргументов.

    Данная функция может принимать до 255 аргументов и находить среднее сразу в нескольких несмежных диапазонах и ячейках:

    Если в рассчитываемом диапазоне встречаются пустые или содержащие текст ячейки, то они игнорируются. В примере ниже среднее ищется по четырем ячейкам, т.е. (4+15+11+22)/4 = 13

    Если необходимо вычислить среднее, учитывая все ячейки диапазона, то можно воспользоваться статистической функцией СРЗНАЧА. В следующем примере среднее ищется уже по 6 ячейкам, т.е. (4+15+11+22)/6 = 8,6(6).

    Статистическая функция СРЗНАЧ может использовать в качестве своих аргументов математические операторы и различные функции Excel:

    СРЗНАЧЕСЛИ()

    Если необходимо вернуть среднее арифметическое значений, которые удовлетворяют определенному условию, то можно воспользоваться статистической функцией СРЗНАЧЕСЛИ. Следующая формула вычисляет среднее чисел, которые больше нуля:

    В данном примере для подсчета среднего и проверки условия используется один и тот же диапазон, что не всегда удобно. На этот случай у функции СРЗНАЧЕСЛИ существует третий необязательный аргумент, по которому можно вычислять среднее. Т.е. по первому аргументу проверяем условие, по третьему – находим среднее.

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

    Если требуется соблюсти несколько условий, то всегда можно применить статистическую функцию СРЗНАЧЕСЛИМН, которая позволяет считать среднее арифметическое ячеек, удовлетворяющих двум и более критериям.

    Статистическая функция МАКС возвращает наибольшее значение в диапазоне ячеек:

    Статистическая функция МИН возвращает наименьшее значение в диапазоне ячеек:

    НАИБОЛЬШИЙ()

    Возвращает n-ое по величине значение из массива числовых данных. Например, на рисунке ниже мы нашли пятое по величине значение из списка.

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

    НАИМЕНЬШИЙ()

    Возвращает n-ое наименьшее значение из массива числовых данных. Например, на рисунке ниже мы нашли четвертое наименьшее значение из списка.

    Если отсортировать числа в порядке возрастания, то все станет гораздо очевидней:

    Статистическая функция МЕДИАНА возвращает медиану из заданного массива числовых данных. Медианой называют число, которое является серединой числового множества. Если в списке нечетное количество значений, то функция возвращает то, что находится ровно по середине. Если же количество значений четное, то функция возвращает среднее для двух чисел.

    Например, на рисунке ниже формула возвращает медиану для списка, состоящего из 14 чисел.

    Если отсортировать значения в порядке возрастания, то все становится на много понятней:

    Возвращает наиболее часто встречающееся значение в массиве числовых данных.

    Если отсортировать числа в порядке возрастания, то все становится гораздо понятней:

    Читать еще:  Как сделать abc анализ в excel формула?

    Статистическая функция МОДА на данный момент устарела, точнее, устарела ее форма записи. Вместо нее теперь используется функция МОДА.ОДН. Форма записи МОДА также поддерживается в Excel для совместимости.

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

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

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

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

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

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

    Если вы используете 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 теперь можно создавать гистограммы и диаграммы Парето.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Функции ВПР (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

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

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