Как работать в эксель инструкция: Функции и формулы в Excel с примерами

Содержание

Справка и обучение по Excel

Учитесь у инструкторов в режиме реального времени

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


Получить совет



Обзор Excel



поиск шаблонов Excel

Воплотить свои идеи в жизнь и упростить работу, начните с профессионально разработанных и полностью настраиваемых шаблонов от Microsoft Create.


Обзор шаблонов


Анализ данных

Можно задавать вопросы о данных, нет необходимости писать сложные формулы. Доступно не для всех языковых стандартов.


Изучение данных


Планирование и отслеживание состояния здоровья

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


Укрепляйте здоровье


Поддержка Excel 2010 прекращена

Узнайте, что означает окончание поддержки Excel 2010, и узнайте, как перейти на Microsoft 365.


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




Поиск премиум-шаблонов

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


Поиск шаблонов


Анализ данных

Можно задавать вопросы о данных, нет необходимости писать сложные формулы. Доступно не для всех языковых стандартов.


Изучение данных


Планирование и отслеживание состояния здоровья

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


Укрепляйте здоровье


Поддержка Excel 2010 прекращена

Узнайте, что означает окончание поддержки Excel 2010, и узнайте, как перейти на Microsoft 365.


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



Популярные разделы



Новые возможности


Поиск нужных данных с помощью функции ПРОСМОТРX


Работа с акциями и географическими данными


Преобразование данных в аналитику


Использование функций


Список всех доступных функций Excel


ВПР


ЕСЛИ


Настройка данных


Создание раскрывающегося списка


Объединение текста из двух или нескольких ячеек


Закрепление областей окна для блокировки строк и столбцов


Анализ с помощью сводных таблиц


Создание сводной таблицы для анализа данных на листе


Разработка макета и формата сводной таблицы


Сортировка данных в сводной таблице или сводной диаграмме



Следите за акциями

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


Изучайте акции и географические данные в Excel



краткое руководство и инструкции к пользованию

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

  1. Работать в Excel с данными.
  2. Создавать таблицы и графики.
  3. Производить расчеты.
  4. Проводить анализ итоговых значений.

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

Чтобы начать пользоваться Экселем необходимо следующее: заходим в меню Пуск, далее выбираем Все программы, потом Microsoft Office, Excel. Этот вариант подходит для версии офиса не раньше 2003 года. Для других: Пуск, Все программы, далее Майкрософт Эксель. Третий вариант: Пуск, Выполнить, пишем Excel, нажимаем Enter.

Общий вид и основные функции

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

  1. Дата.
  2. Время.
  3. Денежный, процентный форматы.

    Схема расположения основных элементов интерфейса Excel

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

Работа с формулами и диаграммами в Экселе

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

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

  1. Гистограммы.
  2. Круговые диаграммы.
  3. Точечные.
  4. Графики.

Пример построения круговой диаграммы на основе табличных данных Excel

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

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

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

Оставить комментарий ВКонтакте

Как создать рабочий лист Excel: пошаговое руководство

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

Что такое рабочий лист Excel?

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

Итак, это было краткое введение в рабочий лист Excel. В следующем разделе вы узнаете, как создать новый рабочий лист Excel.

Как создать рабочий лист Excel?

Чтобы создать новый рабочий лист Excel, необходимо выполнить шаги, указанные ниже.

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

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

Вставка нового рабочего листа Excel

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

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

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

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

Переименовать рабочий лист Excel

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

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

Удалить рабочий лист Excel

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

Рабочие листы в Excel также можно скрыть. Теперь вы увидите, как это можно сделать.

Скрыть рабочий лист Excel

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

Если вы хотите отобразить скрытый рабочий лист, вы можете щелкнуть правой кнопкой мыши существующий рабочий лист и выбрать параметр «Показать», как показано ниже.

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

В следующей части вы научитесь перемещать или копировать рабочий лист Excel

Перемещение или копирование листа Excel

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

После выбора опции вы увидите новое диалоговое окно, как показано ниже.

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

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

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

В следующей части вы научитесь защищать ячейки на листе Excel.

Защита ячеек в листе Excel

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

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

На этом вы подошли к концу этой статьи «Рабочий лист Excel».

Следующие шаги

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

Вы хотите углубиться в изучение Microsoft Excel Возможно, вы заинтересованы в развитии своей карьеры в области бизнес-аналитики?

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

У вас есть какие-либо вопросы к нам по этому учебнику по таблицам Excel? Если да, не стесняйтесь поделиться ими в разделе комментариев ниже. Наши профильные специалисты решат их для вас в кратчайшие сроки.

Часто задаваемые вопросы

1. Что такое рабочий лист Excel?

Вы можете сортировать и анализировать необработанные данные в Excel. Это набор строк и столбцов. Каждый рабочий лист имеет 1048576 строк и 16384 столбца.

2. Каковы основные функции Excel?

Ниже перечислены самые основные и важные функции Excel: 

1. =ЕСЛИ(логический тест, значение если правда, значение если ложь)

2. СУММЕСЛИМН

=СУММЕСЛИМН(диапазон сумм, диапазон критериев 1, критерии 1, …)

3. СЧЁТЕСЛИ

=СЧЁТЕСЛИМН(диапазон критериев 1, критерии 1, …)

4. ТРИМ

=ОТРЕЗАТЬ(текст)

5. СЦЕПИТЬ

= СЦЕПИТЬ (текст1, текст2, текст3, …)

6. ЛЕВЫЙ/ПРАВЫЙ

=ЛЕВО(текст, количество символов)

=ВПРАВО(текст, количество символов)

7. ВПР

= ВПР (искомое значение, массив таблиц, номер индекса столбца, поиск в диапазоне)

8. ЕСЛИ ОШИБКА

=ЕСЛИОШИБКА(ВПР(B2,$G$2:$H$12,2,ЛОЖЬ),»Имя не найдено. Проверьте оба списка»)

9. ЗНАЧЕНИЕ

=ЗНАЧ(текст)

10. УНИКАЛЬНЫЙ

=УНИКАЛЬНЫЙ(массив, по столбцу, ровно один раз)

3. Сколько рабочих листов в Excel?

В одну книгу можно добавить 255 отдельных листов.

4. Как называются файлы Excel?

В Excel есть рабочие листы или электронные таблицы, с которыми мы обычно работаем. Эти рабочие листы хранятся в рабочей книге. В одной книге может быть несколько рабочих листов.

5. Какие есть сочетания клавиш для Excel?

Некоторые распространенные сочетания клавиш для Excel: 

  1. Закрыть книгу — Ctrl+W
  2. Открыть книгу — Ctrl+O
  3. Перейти на вкладку «Главная» — Alt+H
  4. Сохранить книгу — Ctrl+S
  5. Копировать выделение — Ctrl+C
  6. Вставить выделение — Ctrl+V 
  7. Отменить недавнее действие — Ctrl+Z 
  8. Удалить содержимое ячейки — Удалить
  9. Выберите цвет заливки — Alt+H, H
  10. Вырезать выделение — Ctrl+X 
  11. Перейдите на вкладку «Вставка» — Alt+N 
  12. Применение жирного шрифта — Ctrl+B 
  13. Выровнять содержимое ячейки по центру — Alt+H, A, C
  14. Перейти на вкладку «Разметка страницы» – Alt+P 
  15. Перейти на вкладку Данные — Alt+A

Power Pivot для Excel — руководство и примеры использования

Резюме

Что такое Power Pivot?

  • Представленный в Excel 2010 и 2013 в качестве надстройки, но теперь встроенный в приложение, Power Pivot является частью стека бизнес-аналитики Microsoft, способного (но не ограничиваясь) работать с аналитикой больших данных без специальной инфраструктуры или программного обеспечения.
  • Согласно Microsoft, «Power Pivot позволяет импортировать миллионы строк данных из нескольких источников данных в единую книгу Excel, создавать отношения между разнородными данными, создавать вычисляемые столбцы и меры с использованием формул, создавать сводные таблицы и сводные диаграммы, а также дополнительно анализировать данных, чтобы вы могли принимать своевременные бизнес-решения, не нуждаясь в помощи ИТ».
  • Power Pivot был создан в прямом ответе на потребности современных бизнес-аналитиков в области больших данных, с которыми предыдущие поколения Excel — с учетом ограничения в 1 048 576 строк или недостатков скорости обработки — изо всех сил пытались справиться.
  • Power Pivot выражается корпорацией Майкрософт с использованием DAX (выражения анализа данных), который представляет собой набор функций, операторов и констант, используемых в формуле или выражении для вычисления/возврата одного или нескольких значений.

Каковы преимущества Power Pivot по сравнению с Basic Excel?

  • Power Pivot позволяет импортировать и управлять сотнями миллионов строк данных, в то время как Excel имеет жесткое ограничение чуть более миллиона строк.
  • Power Pivot позволяет импортировать данные из нескольких источников в одну исходную книгу без необходимости создавать несколько исходных листов и решать потенциальные проблемы контроля версий и переноса.
  • Power Pivot позволяет манипулировать импортированными данными, анализировать их и делать выводы, не замедляя работу компьютерной системы, как это типично для Basic Excel.
  • Power Pivot позволяет визуализировать большие наборы данных и манипулировать ими с помощью сводных диаграмм и Power BI, тогда как в базовом Excel эти возможности отсутствуют.

Как финансовый эксперт или консультант по Excel может помочь вашему бизнесу?

  • Работая вместе с вами в качестве идейного партнера для разработки, структурирования, создания и предоставления ряда финансовых моделей, бюджетов и анализов/проектов больших данных, все на пути к решениям, касающимся собственных проектов, слияний и поглощений или стратегических вложения.
  • Путем создания индивидуальных моделей, уникальных для вашего бизнеса, с помощью Power Pivot и других специальных функций Excel.
  • Кроме того, путем создания готовых готовых шаблонов, которые могут быть индивидуально адаптированы практически любым сотрудником вашей организации практически для любых целей с помощью Power Pivot и других специальных функций Excel.
  • Путем обучения отдельных лиц или групп в вашей организации всему, от основ Excel, моделирования и анализа до продвинутых количественных методов с использованием Power Pivot, таблиц Power Pivot, диаграмм Power Pivot и PowerQuery.
  • Путем реализации каждого из этих и многих других аспектов, а также разработки, создания и презентации профессиональной презентации PowerPoint до принятия стратегических решений.

Загрузите набор данных здесь, чтобы следовать руководству.

Новая одежда императора: учебник по Excel Power Pivot

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

В частности, инфраструктура Excel предыдущего поколения и ограничения обработки, такие как ограничение на количество строк в 1 048 576 строк или неизбежное замедление обработки, когда речь идет о больших наборах данных, таблицах данных и взаимосвязанных электронных таблицах, снижали его удобство использования в качестве эффективного средства обработки больших данных. инструмент. Однако в 2010 году Microsoft добавила в Excel новое измерение под названием Power Pivot. Power Pivot предложила Excel функции бизнес-аналитики и бизнес-аналитики следующего поколения благодаря своей способности извлекать, объединять и анализировать почти неограниченные наборы данных без снижения скорости обработки. Однако, несмотря на его выпуск восемь лет назад, большинство финансовых аналитиков до сих пор не знают, как использовать Excel Power Pivot, а многие даже не знают, что он вообще существует.

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

Что такое Power Pivot и чем он полезен?

Power Pivot — это функция Microsoft Excel, которая была представлена ​​как надстройка для Excel 2010 и 2013, а теперь является встроенной функцией для Excel 2016 и 365. Как объясняет Microsoft, Power Pivot для Excel «позволяет импортировать миллионы строк данных из нескольких источников данных в единую книгу Excel, создание связей между разнородными данными, создание вычисляемых столбцов и показателей с использованием формул, построение сводных таблиц и сводных диаграмм, а затем дальнейший анализ данных, чтобы вы могли принимать своевременные бизнес-решения без привлечения ИТ-специалистов. помощь».

Основным языком выражений, который Microsoft использует в Power Pivot, является DAX (выражения анализа данных), хотя в определенных ситуациях могут использоваться и другие языки. Опять же, как объясняет Microsoft, «DAX — это набор функций, операторов и констант, которые можно использовать в формуле или выражении для вычисления и возврата одного или нескольких значений. Проще говоря, DAX помогает вам создавать новую информацию из данных, уже имеющихся в вашей модели». К счастью для тех, кто уже знаком с Excel, формулы DAX будут выглядеть знакомыми, поскольку многие формулы имеют схожий синтаксис (например, 9).0261 СУММА , СРЕДНИЙ , СБОР ).

Для ясности основные преимущества использования Power Pivot по сравнению с базовым Excel можно обобщить следующим образом: ряды.

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

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

    Как использовать Power Pivot

    1) Импорт больших наборов данных

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

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

    Используя вкладку Данные на ленте, я создал Новый запрос из файла CSV (см. Создание нового запроса ниже). Раньше эта функция называлась PowerQuery, но в Excel 2016 и 365 она была более тесно интегрирована во вкладку «Данные» в Excel.

    От пустой книги в Excel до загрузки всех двух миллионов строк в Power Pivot потребовалось около одной минуты! Обратите внимание, что я смог выполнить небольшое форматирование данных, превратив первую строку в имена столбцов. За последние несколько лет функциональность Power Query значительно улучшилась: от надстройки Excel до тесно интегрированной части вкладки «Данные» на панели инструментов. Power Query может сводить, сводить, очищать и формировать данные с помощью набора параметров и собственного языка M.9.0003

    2) Импорт данных из нескольких источников

    Одним из других ключевых преимуществ Power Pivot для Excel является возможность легкого импорта данных из нескольких источников. Раньше многие из нас создавали несколько листов для различных источников данных. Часто этот процесс включал написание кода VBA и копирование/вставку из этих разрозненных источников. Однако, к счастью для нас, Power Pivot позволяет импортировать данные из разных источников данных непосредственно в Excel, не сталкиваясь с упомянутыми выше проблемами.

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

    • Microsoft Azure
    • SQL-сервер
    • Терадата
    • Фейсбук
    • Продажи
    • JSON-файлы
    • книги Excel
    • …и многие другие

    Кроме того, несколько источников данных можно объединить либо в функции запроса, либо в окне Power Pivot для интеграции данных. Например, вы можете получить данные о производственных затратах из рабочей книги Excel и фактические результаты продаж с сервера SQL через запрос в Power Pivot. Оттуда вы можете объединить два набора данных, сопоставив номера производственных партий, чтобы получить валовую прибыль на единицу продукции.

    3) Работа с большими наборами данных

    Еще одним ключевым преимуществом Power Pivot в Excel является возможность манипулировать большими наборами данных и работать с ними для получения соответствующих выводов и анализа. Ниже я приведу несколько распространенных примеров, чтобы дать вам представление о возможностях этого инструмента.

    Меры

    Любители Excel, несомненно, согласятся с тем, что сводные таблицы — одна из самых полезных и в то же время одна из самых неприятных задач, которые мы выполняем. Разочаровывает, особенно когда дело доходит до работы с большими наборами данных. К счастью, Power Pivot для Excel позволяет легко и быстро создавать сводные таблицы при работе с большими наборами данных.

    На изображении ниже под названием Создание показателей обратите внимание на то, как окно Power Pivot разделено на две панели. На верхней панели находятся данные, а на нижней — меры. Мера — это вычисление, которое выполняется для всего набора данных. Я ввел меру, введя текст в выделенную ячейку.

     Общий объем продаж: = СУММА («Учетные данные» [Сумма])

    Это создает новую меру, которая суммирует значения в столбце Сумма. Точно так же я могу ввести другую меру в ячейку ниже 9. 0003

     Средние продажи:=СРЗНАЧ('Учетные данные'[Сумма])

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

    Таблицы измерений

    Как финансовые аналитики, использующие Excel, мы научились использовать сложные формулы, чтобы подчинить технологию нашей воле. Мы осваиваем VLOOKUP , SUMIF и даже ужасный INDEX(MATCH()) . Однако, используя Power Pivot, мы можем выбросить многое из этого.

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

    Я также создал таблицу дат для использования с нашим набором данных (см. Создание таблицы дат ниже). Power Pivot для Excel позволяет легко и быстро создать таблицу дат для консолидации по месяцам, кварталам и дням недели. Пользователь также может создать более настраиваемую таблицу дат для анализа по неделям, финансовым годам или любым группировкам, характерным для организации.

    Вычисляемые столбцы

    Помимо мер, существует еще один тип расчета: вычисляемые столбцы. Пользователям Excel будет удобно писать эти формулы, так как они очень похожи на написание формул в таблицах данных. Ниже я создал новый вычисляемый столбец (см. Создание вычисляемого столбца ниже), который сортирует таблицу учетных данных по сумме. Продажи ниже 50 долларов помечены как «маленькие», а все остальные — как «крупные». Разве формула не кажется интуитивно понятной?

    Отношения

    Затем мы можем создать связь между полем «Категория» таблицы «Учетные данные» и полем «Категория» таблицы «Категория», используя представление диаграммы. Кроме того, мы можем определить связь между полем «Дата продажи» таблицы «Учетные данные» и полем «Дата» таблицы «Календарь».

    Теперь, без каких-либо функций СУММЕСЛИ или ВПР , мы можем создать сводную таблицу, которая вычисляет общий объем продаж по годам, и ввести срез для размера транзакции.

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

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

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

    Расширенные функции

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

    Time Intelligence

    Часто, когда мы изучаем финансовые результаты, мы хотим сравнить их с сопоставимыми временными рамками предыдущего года. В Power Pivot есть несколько встроенных функций анализа времени.

     Продажи за тот же период прошлого года: = РАСЧЕТ ([Общий объем продаж], SAMEPERIODLASTYEAR («Календарь» [Дата]))
Рост продаж в годовом исчислении:=if(not(ISBLANK([Продажи за тот же период прошлого года])),([Общий объем продаж]/[Продажи за тот же период прошлого года])-1,ПУСТО())

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

    Несоответствие детализации

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

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

    В Excel следующая сводная таблица собирается быстро.

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

     Отклонение фактического от запланированного: = ДЕЛИТЬ ([Общий объем продаж], [Общий объем продаж по бюджету])-1

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

    Процент от общего числа

    Наконец, давайте рассмотрим продажи в определенной категории как процент от всех продаж (например, вклад категории в общий объем продаж) и продажи в определенной категории как процент от всех продаж того же типа (например, , вклад категории в продажи сезонного типа). Ниже я создал две меры:

     Общий объем продаж в процентах от всех продаж:=[Общий объем продаж]/CALCULATE([Общий объем продаж],ALL('Учетные данные'))
Общий объем продаж в процентах от типа: = [Общий объем продаж]/РАССЧИТАТЬ ([Общий объем продаж], ВСЕ («Учетные данные» [Категория]))

    Теперь эти показатели можно развернуть в новой сводной таблице:

    Обратите внимание, как выполняются расчеты на уровне категорий и сезонных типов. Мне нравится, как быстро и легко выполняются эти расчеты на таком большом наборе данных. Это всего лишь несколько примеров элегантности и чистой вычислительной мощности Power Pivot.

    Сжатие

    Еще одним преимуществом является уменьшение размера файла. Первоначальный размер файла был 91 МБ, а теперь меньше 4 МБ. Это сжатие 96% исходного файла.

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

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

    SSAS Tabular

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

    4) Визуализация и анализ данных

    CUBE Formulas

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

    Как мы можем заполнить данные о продажах без создания сводных таблиц, если все наши продажи размещены с помощью Power Pivot для Excel? Используя формулы КУБ! Мы можем писать формулы CUBE в любой ячейке Excel, и она будет выполнять вычисления с использованием уже созданной нами модели Power Pivot.

    Например, следующая формула вводится в ячейку под «Общий объем продаж за 2016 год»:

    =КУБЗНАЧ(" ThisWorkbookDataModel "," [Показатели].[Общий объем продаж] "," [Календарь].[Год].[2016] ")

    Первая часть формулы, выделенная желтым цветом, относится к названию модели Power Pivot. Как правило, это ThisWorkbookDataModel для более новых версий Power Pivot для Excel. Зеленая часть указывает на то, что мы хотим использовать показатель Total Sales. Часть, выделенная синим цветом, указывает Excel фильтровать только те строки, в которых дата продажи имеет год, равный 2016 году.

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

    Power BI

    Еще одно преимущество Power Pivot для Excel заключается в том, что вы можете быстро взять любую созданную вами книгу Power Pivot и быстро преобразовать ее в модель Power BI. Импортировав книгу Excel непосредственно в приложение Power BI Desktop или Power BI Online, вы можете анализировать, визуализировать данные и делиться ими с кем-либо в вашей организации. По сути, Power BI — это Power Pivot, PowerQuery и SharePoint в одном флаконе. Ниже я создал панель мониторинга, импортировав предыдущую книгу Power Pivot для Excel в настольное приложение Power BI. Обратите внимание на интерактивный интерфейс:

    Одна из замечательных особенностей Power BI — функция вопросов и ответов на естественном языке. Для демонстрации я загрузил модель Power BI в свою учетную запись Power BI в Интернете. На веб-сайте я могу задавать вопросы, и Power BI строит соответствующий анализ по мере того, как я набираю:

    Этот тип возможности запроса позволяет пользователю задавать вопросы о модели данных и взаимодействовать с данными более простым способом, чем в Excel.

    Еще одним преимуществом Power BI является то, что разработчики Microsoft постоянно выпускают для него обновления. Ежемесячно выпускаются новые функции, многие из которых запрашиваются пользователями. Лучше всего то, что это плавный переход от Power Pivot к Excel. Таким образом, время, которое вы потратили на изучение формул DAX, можно использовать в Power BI! Для аналитика, которому необходимо поделиться своим анализом со многими пользователями на разных устройствах, возможно, стоит изучить Power BI.

    Лучшие практики

    После того, как вы начнете, вам следует следовать нескольким рекомендациям.

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

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

    Наконец, я бы предложил использовать простые английские имена для показателей. Этот мне потребовалось много времени, чтобы принять. Первые несколько лет я придумывал такие имена, как 9.0261 SumExpPctTotal , но как только другие люди начали использовать одни и те же книги, мне пришлось много объяснять. Теперь, когда я начинаю новую рабочую книгу, я использую имена показателей, такие как Статья расходов как процент от общих расходов . Хотя имя длиннее, его гораздо проще использовать кому-то другому.

    Варианты использования в реальной жизни

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

    Вот некоторые:

    • Анализ производительности большого портфеля активов в различных временных диапазонах: Поскольку Power Pivot для Excel позволяет нам определять показатели, которые сравнивают период времени с предыдущим, мы можем быстро получать квартальные, годовые годовой и месячной производительности — все на скользящей основе, записав всего несколько показателей.
    • Суммирование данных бухгалтерского учета с использованием настраиваемых уровней агрегирования: Идентифицируя каждую строку главной книги по имени, категории и финансовому отчету, можно быстро создавать отчеты, включающие соответствующие позиции строки.
    • Сети могут определять продажи в одном и том же магазине: С помощью таблицы, отображающей, когда магазины выходят в онлайн, можно сравнивать финансовые результаты для одного и того же магазина.
    • Выявление лидеров продаж и отставаний: Можно создать сводные таблицы, в которых выделяются пять лучших и пять худших SKU по объему продаж, валовой прибыли, срокам производства и т. д.
    • Розничные продавцы могут определять календарные таблицы, использующие конфигурацию 4-4-5: Используя настраиваемую таблицу дат, розничный продавец может легко назначать каждый день определенному месяцу 4-4-5, после чего ежедневные результаты продаж могут быть объединены в соответствующие месяц.

    От неуклюжих электронных таблиц к современным рабочим тетрадям

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

    Благодаря функциям CUBE Power Pivot для Excel легко интегрируется в существующие книги Excel. Прирост вычислительной эффективности нельзя не заметить. Если предположить, что скорость обработки будет на 20 % выше, то финансовый аналитик, проводящий шесть часов в день в Excel, может сэкономить 300 часов в год!

    Кроме того, теперь мы можем анализировать наборы данных, которые намного больше, чем мы могли раньше с нашим традиционным Excel. Благодаря эффективно разработанным моделям мы можем легко получить в 10 раз больше данных, чем раньше было разрешено в традиционном Excel, сохраняя при этом быструю аналитическую гибкость. Благодаря возможности конвертировать модели из Power Pivot в SSAS Tabular объем данных, которые можно обработать, в 100–1000 раз больше, чем мы можем получить в Excel.

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

    Если вы хотите попробовать Power Pivot для Excel, ниже приведены некоторые полезные материалы для начала работы.

    Полезные ссылки и руководства

    Колли, Р., и Сингх, А. (2016). Power Pivot и Power BI: руководство пользователя Excel по DAX, Power Query, Power BI и Power Pivot в Excel 2010–2016. Соединенные Штаты: Святой макрос! Книги.

    Феррари, А., и Руссо, М. (2015). Полное руководство по DAX: бизнес-аналитика с Microsoft Excel, SQL Server Analysis Services и Power BI. США: Microsoft Press, США.

    Дополнительная литература в блоге Toptal Finance:

    • Изучение функций Excel Get & Transform
    • Формулировки задач проектирования – что это такое и как их формулировать
    • Введение в оконные функции SQL
    • Стратегия продукта: руководство по основным концепциям и процессам
    • Рэй Далио из Bridgewater: тихий пионер больших данных, машинного обучения и финансовых технологий

    Понимание основ

    • Что такое таблица Power Pivot?

      Когда вы смотрите на Power Pivot и сводные таблицы, Power Pivot — это функция Excel, которая позволяет импортировать, обрабатывать и анализировать большие данные без потери скорости и функциональности.

    Читайте также: