Как работать в эксель инструкция: Как работать в Excel правильно (для новичков и не только)

Содержание

Основы работы с Microsoft Excel: создаем и настраиваем таблицу

Утилита от Microsoft под названием Exel установлена практически на каждом компьютере. Без неё нельзя представить ни одну современную компанию, так как именно в этой программе можно осуществлять все манипуляции с числами и буквами. Большинство пользователей владеет базовыми навыками Word (знает как изменить шрифт, размер шрифта и его цвет). Совершенно по другому дела обстоят у Exel. Хоть программы и схожи по интерфейсу, функционал у них кардинально разниться. Встроенной инструкции у Exel нет, поэтому с освоением программы могут быть некоторые проблемы. Прочитав данную статью, вы получите все необходимые основы Exel, которые точно пригодятся в будущем.

Основы работы в Excel

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

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

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

Первые шаги

Лучший способ что-то выучить – это практика. Поработав в программе часа 2-3 можно смело её освоить на базовом уровне. И за это стоит отдать должное Microsoft, так как их продукты всегда были легкие к восприятию. Чего стоит PowerPoint, который можно освоить за 30 минут. Пару потраченных часов времени на изучение Exel точно окупят себя в будущем, когда эти знания пригодятся на работе.

Да, за это время вы не станете экспертом в Excel, однако этого вполне хватит для создания простых графиков и таблиц с использованием базовых инструментов. Стоит отметить, что на большинстве работах требуется базовое знание «Таблиц».

Знакомство с терминами

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

Таблица – самый главный элемент в данной программе. С помощью данного инструмента можно легко группировать данные и удобно производить вычисления. Нумерация строк таблицы начинается от 1 и до бесконечности. Нумерация столбцов происходит с использованием буквенных значений. К примеру, столбец может иметь значение A, которая равно единице. 27 по счёту и выше столбцы обозначаются уже следующим образом: АА, АС.

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

Ячейки же имеют интересный алгоритм нумерации. Например, ячейка на первой строчке и первом столбце будет иметь номер А1. Такой вид был сделан специально для удобной записи ячейки в командную строку. Также, если выделить сразу несколько ячеек, то получиться выделенная область с определенным диапазоном, например, А3:В4. Если один раз нажать курсором на ячейку будет редактироваться не сам текст внутри, а сама ячейка. Двойное де нажатие позволяет работать с текстом.

Рабочий лист в Excel – это вся рабочая область с таблицами и графиками.

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

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

Работа в программе Exel: создаем новый документ

Есть два варианта создания нового документа Excel.

1.Прямо с рабочего стола. ПКМ нажимаем на пустую область на рабочем столе. Переходим в пункт «Создать», затем в списке выбираем «Лист Microsoft Excel».

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

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

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

Как добавить столбец или строку

Если нужно добавить новую строку:

Щелкаем мышкой по строке, на место которой нужно вставить новую.

Щелкаем правой кнопкой мыши на цифру нашей строки (в данном случае на 4-ку). Выпадает контекстное меню, в котором нужно выбрать пункт «Вставить».

После чего появится новая пустая строка.

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

Редактирование размера ячеек

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

В строке с индексами необходимо клацнуть по правой границе нужного столбца ЛКМ. Далек с зажатой клавишей нужно растянуть столбец, тем самым сделав его шире или уже. Этот способ удобен, если у вас в данном столбце будут помещены данные, приблизительно равные по объему.

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

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

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

Создаем простую таблицу в Excel

Всё заполнение таблицы происходит вручную. Обычно, пользователи приступают к созданию новой таблицы с наименования столбцов. Например, в ячейке А1 пишем «Код товара», в ячейке А2 – «Наименование товара», А3 – «Количество», А4 — «Цена». Теперь легче понять, какие данные будут размещены в каждом столбце. К слову, первую строку можно закрепить. Для этого перейдите в «Вид» – «Закрепить области» – «Закрепить верхнюю строку». Тогда наименование ваших столбцов будет показано даже на дальней строчке.

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

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

Что еще может понадобиться при создании таблицы, так это выделение границ ячеек. Можно отрисовать границы у выбранного диапазона ячеек. Для этого, выделяем диапазон и нажимаем на элемент «Границы». Выбираем, например, «Все границы» и получаем красиво очерченную таблицу.

Основные формулы в Excel

Все доступные формулы представлены в одноименном разделе «Формулы». Здесь вы найдете формулы для решения логических, математических, финансовых, текстовых и иных задач. При использовании формул номера ячеек можно прописывать вручную, например диапазон B1-B22 или выбирать ячейки вручную: поставьте знак «=» в свободной ячейке (там, где вам нужно увидеть полученный результат) и поочередно нажимайте на те ячейки, которые следует использовать.

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

СУММ (ячейка №; ячейка №; ячейка №…..). Эта формула покажет сумму данных в пределах выделенной области. Поставьте знак «=», пропишите формулу СУММ и выделяйте те ячейки, сумму которых вы хотели бы увидеть. Важно: выделяйте диапазон ячеек, зажимая клавишу CTRL. После выделения всех ячеек нажимаем Enter.

ПРОИЗВЕД (ячейка №; ячейка №; ячейка №…..). Выполняет умножение компонентов. Точно также, как и в предыдущей формуле, выделять диапазон ячеек нужно при нажатии клавиши CTRL, а после выделения всех ячеек нажимаем Enter.

Как построить диаграмму в таблице Excel

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

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

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

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

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

Итог

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

Это были все основные пункты в работе с Exel. Этих навыков вполне хватит для создания несложных графиков или таблиц. Стоит отметить, что полученные в этой статье знания, помогут в самообучении, так как основные пункты вам уже знакомы. Стоит отметить, что знание «Таблиц» на профессиональном уровне редко где требуется — чаще всего это профессии, связанные с финансами или программированием. Если всё же хочется лично для себя знать Exel на уровне профи, то придется потратить не один месяц и перечитать десятки обучающих книг. А это требует времени, выдержки, и большого желания. Успехов!

Post Views: 170

Как работать с таблицами в Excel

Unknown 21:47:00 инструкция , совет , столбец , строка , таблица , Эксель , ячейка , Excel для новичков , Excel для чайников

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

Основные понятия

Строки — строки в Excel нумеруются числами начиная от 1. Ячейки в первой строке будут A1, B1, C1 и так далее.
Столбцы — столбцы в Excel обозначаются латинскими буквами начиная с A. Ячейки в первом столбце будут A1, A2, A3 и так далее.

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

Как выбрать ячейку

Существует несколько способов, что бы выбрать ячейку:

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

Чтобы выбрать каждую клетку на листе, нажмите в правом верхнем углу листа слева от «A»

Как внести данные в ячейку

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

Как редактировать содержимое ячейки.

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

Как переместиться между ячейками при помощи клавиатуры

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

Как переместить или копировать ячейку

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

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

Как вставить столбец или строку

Для того, что бы вставить столбец в Excel, кликните правой кнопкой на букве столбца и выберите Вставить. Excel всегда вставляет столбец слева от выбранного столбца.

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

Резюме

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

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

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). 0053 СУММА , СРЕДНИЙ , СБОР ).

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

Он позволяет импортировать сотни миллионов строк данных и управлять ими, в то время как 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.0039

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.0039

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

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

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

Как финансовые аналитики, использующие 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 чрезвычайно полезен.

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

Анализ производительности большого портфеля активов в различных временных диапазонах: Поскольку Power Pivot для Excel позволяет нам определять показатели, которые сравнивают период времени с предыдущим, мы можем быстро получать квартальные, годовые годовой и месячной производительности — все на скользящей основе, записав всего несколько показателей.
Суммировать данные бухгалтерского учета с использованием настраиваемых уровней агрегирования: Идентифицируя каждую строку главной книги по имени, категории и финансовому отчету, можно быстро создавать отчеты, включающие соответствующие позиции строки.
Сети могут определять продажи в одном и том же магазине: С помощью таблицы, отображающей, когда магазины выходят в онлайн, можно сравнивать финансовые результаты для одного и того же магазина.
Выявление лидеров продаж и отставаний: можно создать сводные таблицы , в которых выделяются пять лучших SKU и пять худших 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:

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

Основные сведения

Что такое Power Pivot table?
Когда вы смотрите на Power Pivot и сводные таблицы, Power Pivot — это функция Excel, которая позволяет импортировать, обрабатывать и анализировать большие данные без потери скорости и функциональности. Таблицы Power Pivot — это сводные таблицы, которые позволяют пользователю смешивать данные из разных таблиц, предоставляя им мощную цепочку фильтров при работе с несколькими таблицами.
Как создать таблицу Power Pivot?
Сначала импортируйте набор данных в книгу Power Pivot. Затем в окне Power Pivot на вкладке Главная Power Pivot щелкните Сводная таблица. Затем выберите «Новый рабочий лист» (Excel добавит пустую сводную таблицу). Затем выберите пустую сводную таблицу и следуйте приведенным там инструкциям.
Как создать диаграмму Power Pivot?
Сначала импортируйте набор данных в книгу Power Pivot. Затем создайте таблицу Power Pivot. Затем на вкладке Power Pivot щелкните стрелку под сводной таблицей и выберите Сводная диаграмма. Выберите «Существующий рабочий лист» и нажмите «ОК». Excel добавит пустую сводную диаграмму на тот же лист. Следуйте инструкциям там.

Как использовать Excel для создания интерактивных листов | Малый бизнес

Дэвид Уидмарк

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

Ограничения данных и инструкции для ячеек

Запустите Excel и создайте новую книгу. Выберите ячейку, которая будет использоваться для ввода данных. Перейдите на вкладку «Данные». Выберите «Проверка данных» в разделе ленты «Инструменты данных». Откроется диалоговое окно «Проверка данных».
Используйте параметры вкладки «Настройки» в диалоговом окне, чтобы ограничить тип данных, которые можно вводить. Например, чтобы ограничить количество записей в ячейке 4-значным числом, обозначающим год, выберите «Длина текста» в меню «Разрешить», «Равно» в меню «Данные» и введите «4» в поле « Текстовое поле «Длина».
Щелкните вкладку «Ввод сообщения» в диалоговом окне, чтобы добавить сообщение или подсказку для ячейки. Введите заголовок сообщения в поле «Заголовок» для дальнейшего использования. Введите подсказку в поле «Сообщение», например: «В этом поле должно быть 4 цифры».
Щелкните вкладку «Предупреждение об ошибке», чтобы создать сообщение об ошибке, если в ячейку введены неправильные данные. Выберите значок в меню «Стиль». Введите заголовок в поле «Заголовок» и сообщение в поле «Сообщение об ошибке». Обратите внимание, что и заголовок, и сообщение будут частью сообщения об ошибке.
Нажмите «ОК», чтобы сохранить настройки «Проверки данных» и закрыть диалоговое окно. Щелкните ячейку, чтобы просмотреть инструкции во всплывающем окне. Введите неверное значение в ячейку, чтобы увидеть сообщение об ошибке. Введите правильное значение в ячейку, чтобы убедиться, что сообщение об ошибке не появляется.

Скрыть и заблокировать формулы

Щелкните ячейку, которую можно использовать для применения формулы к значению, введенному пользователем. Введите формулу в ячейку. Например, если пользователь вводит в ячейку B2 значение, представляющее месячные продажи, рядом с ним можно создать годовой прогноз, введя «=(B2*12)» в ячейке C2.
Перейдите на вкладку «Главная», пока выделена ячейка, содержащая формулу. Нажмите «Формат», затем «Форматировать ячейки». Откроется диалоговое окно «Формат ячеек».
Установите флажок «Скрытый» на вкладке «Защита» диалогового окна. Нажмите «ОК».
Перейдите на вкладку «Просмотр», затем нажмите «Защитить лист», расположенный в группе «Изменения» на ленте. Установите флажок рядом с «Защитить лист и содержимое заблокированных ячеек». Нажмите «ОК». 9
Microsoft Office: Отображение или скрытие формул ячейки должны использоваться для данных, а какие нет. Например, если сделать определяемые пользователем ячейки белыми с черной рамкой, а остальные ячейки сделать светло-голубыми, то пользователям будет очевидно, какие ячейки следует использовать.