Обработка больших массивов данных в Excel
Любой сотрудник компании, работающий в отделе продаж, финансов, маркетинга, логистики, сталкивается с необходимостью работать с данными, анализировать их.
Excel — незаменимый помощник для достижения этих целей. Мы импортируем информацию, «подтягиваем» ее, систематизируем. На ее основе строим диаграммы, сводные таблицы, планируем, прогнозируем.
Однако в Excel до недавнего времени было 2 важных ограничения:
Мы не могли разместить на рабочем листе Excel более миллиона строк (а наши данные о продажах за 2 года занимают, например, 10 млн строк).
Мы знали, как создать и настроить интерактивные и обновляемые отчеты, но это отнимало много времени.
Единственный инструмент в Excel — сводные таблицы — позволял быстро обрабатывать наши данные.
С другой стороны, есть категория пользователей, которые работают со сложными BI-системами. Это системы бизнес-аналитики (business intelligence), которые дают возможность быстро визуализировать, «крутить» данные и извлекать из них ценную информацию (data mining).
Однако внедрение и поддержка таких систем требует значительного участия IT-специалистов и больших финансовых вложений.
До Excel 2010 было четкое разделение на анализ малого и большого объема данных: Excel с одной стороны и сложные BI-системы — с другой.
Начиная с версии 2010, в Excel добавили инструменты, в названиях которых присутствует слово power: Power Query, Power Pivot и Power View. Они позволили сгладить грань между пользователями Excel и комплексных BI-систем.
Power Query
Чтобы работать с данными, к ним нужно подключиться, отобрать, преобразовать или, другими словами, привести их к нужному виду.
Для этого и необходим Power Query. До версии Excel 2013 включительно этот инструмент был в виде надстройки, которую можно было установить бесплатно с сайта Microsoft.
В версии 2016 это уже встроенный в программу инструментарий, находящийся на вкладке «Данные» (Data) в разделе «Скачать и преобразовать» (Get and Transform).
Если вам интересна эта статья, то вам
точно пригодится наша шпаргалка
Перечень источников информации, к которым можно подключаться — огромный: от баз данных (их в последней версии 10) до Facebook и Google таблиц (рис. 1).
Рис 1. Выбор источника данных в Power Query
Вот некоторые возможности Power Query по подготовке и преобразованию данных:
отбор строк и столбцов, создание пользовательских (вычисляемых) столбцов
преобразование данных с помощью числовых, текстовых функций, функций даты и времени
транспонирование таблицы, разворачивание по столбцам (Pivot) и наоборот — сворачивание данных, организованных по столбцам, в построчный вид (Unpivot)
объединение нескольких таблиц: как вниз — одну под другую, так и связывание по общей колонке (единому ключу)
Рис 2. Окно редактора Power Query
Ну и конечно, после выгрузки подготовленных данных в Excel они будут автоматически обновляться, если в источнике данных появятся новые строки.
Пример
Компания в своей аналитике использует текущие курсы трех валют, которые ежедневно обновляются на сайте Национального банка.
Таблица на сайте непригодна для прямого использования (рисунок 2-1):
все валюты не нужны
в колонке «Курс» в качестве разделителя целой и дробной частей используется точка (в наших региональных настройках — запятая)
в колонке «Курс» отображается показатель за разное количество единиц валюты: за 100, за 1000 и т. д. (указано в отдельной колонке «Количество единиц»)
Рис. 2-1. Так выглядит таблица с курсами валют на сайте Нацбанка.
С помощью Power Query мы подключаемся к таблице текущих курсов валют на сайте НБУ и в этом редакторе готовим запрос на извлечение данных:
В колонке «Курс» меняем точку на запятую (инструмент «Замена значений»).
Создаем вычисляемый столбец, в котором курсы валют в колонке «Курс» делятся на количество единиц валюты из колонки «Количество единиц».
Удаляем лишние столбцы и оставляем только строки валют, с которыми работаем.
Выгружаем полученную таблицу на рабочий лист Excel.
Результат показан на рисунке 2-2.
Рис. 2-2. Так выглядит результирующая таблица в нашем Excel файле.
Курсы валют на сайте Нацбанка меняются каждый день. Но при обновлении данных в документе Excel наш, один раз подготовленный, запрос пройдет через все шаги, и результирующая таблица всегда будет в нужном виде, но уже с актуальными курсами.
Если вам интересна эта статья, то вам точно пригодится наша шпаргалка
Скачать в Telegram Скачать в ViberPower Pivot
У вас данные находятся в разрозненных источниках? Некоторые таблицы содержат больше 1 млн строк? Вам нужно все это объединить в одну модель данных и анализировать с помощью, например, сводной таблицы Excel? Здесь понадобится Power Pivot — надстройка Excel, которая по умолчанию включена в версии Pro Plus и выше (начиная с версии 2010).
В Power Pivot вы можете добавлять данные из разных источников, связывать таблицы между собой (рисунок 3). Таблицы при этом не обязательно должны находиться на рабочих листах Excel. Вместо этого они по-прежнему будут храниться в файле Excel, но просматривать их можно в окне Power Pivot (рис. 4). Поэтому нет ограничения на количество строк — в вашем файле Excel могут находиться таблицы и в сотни миллионов строк.
Рис. 3. Окно Power Pivot в представлении диаграммы
Рис. 4. Окно Power Pivot в представлении данных
Вот некоторые возможности Power Pivot, помимо описанных выше:
добавлять вычисляемые столбцы и поля (меры), в том числе основанные на расчетах из нескольких таблиц
создавать и мониторить в сводной таблице ключевые показатели эффективности (KPI)
создавать иерархические структуры (например, по географическому признаку — регион, область, город, район)
И обрабатывать все это с помощью сводной таблицы Excel, построенной на модели данных.
Пример. У предприятия в базе данных (или отдельных файлах Excel) в 5 таблицах хранится информация о продажах, клиентах, товаре и его классификации, менеджерах по продажам и закупочных ценах продукции. Необходимо провести анализ по объемам продаж и маржинальности по менеджерам.
С помощью Power Pivot:
добавляем все 5 таблиц в модель данных
связываем таблицы по общим ключам (столбцам)
в таблице «Продажи» создаем вычисляемый столбец «Продажи в закупочных ценах», умножив количество штук из таблицы «Продажи» на закупочную цену из таблицы «Цена закупки»
создаем вычисляемое поле (меру) «Маржа»
с помощью инструмента «Ключевые показатели эффективности» устанавливаем цель по маржинальности и настраиваем визуализацию — как выполнение цели будет визуализироваться в сводной таблице
Теперь можно «крутить» эти данные в сводной таблице или в отчете Power View (следующий инструмент) и анализировать маржинальность по товарам, менеджерам, регионам, клиентам.
Power View
Иногда сводная таблица — не лучший вариант визуализации данных. В таком случае можно создавать отчеты Power View. Как и Power Pivot, Power View — это надстройка Excel, которая по умолчанию включена в версии Pro Plus и выше (начиная с версии 2010).
В отличие от сводной таблицы, в отчет Power View можно добавлять диаграммы и другие визуальные объекты. Здесь нет такого количества настроек, как в диаграммах Excel. Но в том то и сила инструмента — мы не тратим время на настройку, а быстро создаем отчет, визуализирующий данные в определенном разрезе.
Вот некоторые возможности Power View:
— быстро добавлять в отчет таблицы, диаграммы (без необходимости настройки)
организовывать срезы и фильтры
уходить на разные уровни детализации данных
добавлять карты и располагать на них данные
создавать анимированные диаграммы
Пример отчета Power View — на рисунке 5.
Рис. 5. Пример отчета Power View
Даже самые внушительные массивы данных можно систематизировать и визуализировать — главное не ограничиваться поверхностными возможностями Excel, а брать из его функций все возможное.
Курс Excel для анализа данных. Онлайн-обучение аналитики в эксель
Купить
Обучение
Преподаватели
Программа
Стоимость
Кому подойдет
Вопросы
Обучение
Преподаватели
Программа
Стоимость
Кому подойдет
Excel — незаменимый инструмент для бухгалтеров, экономистов, маркетологов, менеджеров, предпринимателей и аналитиков. Но мало кто умеет профессионально им пользоваться. После курса вы поймете, что Excel — это не только таблицы.
Длительность
2,5 месяца
Стоимость
6 320 ₽
Почему этот курс такой полезный
Руководители компаний смогут самостоятельно анализировать данные и на этой основе принимать более точные эффективные решения
Молодые специалисты научатся работать с большим массивом данных — анализировать и визуализировать их
Менеджеры и аналитики узнают о функциях Excel, научатся строить понятные интерактивные отчеты, смогут решать более сложные задачи
Как проходит обучение
Готовый проект в портфолио
В конце курса создаете калькулятор юнит-экономики и показываете работодателям, что умеете считать прибыльность бизнес-модели.
Новые уроки раз в неделю
Смотрите по ролику в день или все в один присест.
Пересматривайте, ставьте на паузу, включайте скорость х2 — подстраивайте курс под себя.
Самостоятельная практика
После урока получаете Excel-файл и алгоритм действий. Выполняете домашнее задание и сверяетесь с верными ответами.
Чему вы научитесь
Понимать бизнес-задачи и переводить их в план действий
Собирать, обрабатывать и анализировать данные в Excel
Работать с продуктовыми и бизнес-метриками, рассчитывать комплексные метрики
Настраивать фильтры, работать с формулами. Продумывать многоступенчатые решения для анализа
Объединять данные из разных выгрузок, делать отчеты для любых целей, строить графики и видеть тренды
Извлекать инсайты для бизнеса, находить закономерности и делать прогнозы
Преподаватели
Руководитель направления аналитики в «Яндекс.
Практикуме», бывший руководитель направления аналитики в Skyeng
Марк Сысоев
Санжар Ессенгараев
Финансовый и дата-аналитик
Программа курса
Модуль 1
Первичная обработка данных
— Ориентироваться в интерфейсе Excel.
— Переводить бизнес-задачи на аналитический язык.
— Применять основные функции Excel для работы с данными.
— Работать с бизнес- метриками.
— Работать с сырыми данными.
— Применять различные методы обработки данных.
Занятия
модуля
В этом модуле вы научитесь
Основы работы с Excel
1 урок
Обработка данных
2 урок
Модуль 2
Работа со сложными данными
Занятия
модуля
В этом модуле вы научитесь
— Работать со сложными функциями.
— Правильно объединять данные.
— Делать расчет комплексных метрик на основе существующих данных.
— Решать сложные аналитические задачи.
— Работать с основными ошибками при анализе данных.
— Оформлять калькулятор для визуализации результатов.
— Строить понятные отчеты.
— Настраивать фильтры и визуализации.
— Переносить готовые данные в GS. Узнаете основные различия GS от Excel.
Расчет комплексных метрик.
4 урок
Агрегация данных.
3 урок
5 урок
Работа с нестандартными данными.
6 урок
Настройка фильтров и визуализация.
7 урок
Сборка калькулятора юнит-экономики.
8 урок
Переход из Excel в Google Sheets.
Модуль 3
Прогнозирование
9 урок
Сложные прогнозы в Excel.
10 урок
Сложные прогнозы в Excel. Часть 2.
— Строить сложные прогнозы на основе имеющихся данных.
— Применять в работе основы математической статистики.
Занятия
модуля
В этом модуле вы научитесь
Проект для портфолио
Проект по анализу бизнес-метрик
Учебный проект
Проект по анализу бизнес-метрик на датасете онлайн-школы за 2021 год.
Тема проекта
Отзывы о Skypro
Длительность — 2,5 месяца
Стоимость обучения
6 320 ₽
Полная стоимость со скидкой:
Оплата
Остались вопросы?
Если в первую неделю обучения что-то пойдет не так, мы вернем вам полную сумму. Если вы захотите отказаться от обучения спустя две недели, мы вернем сумму за вычетом стоимости пройденных занятий.
Если не понравится, можно вернуть деньги?
Да, сможете. Вам понадобится всего 4 часа в неделю для теории и практики. Это комфортный темп для тех, кто работает.
Получится совмещать с работой?
Конечно, все материалы курса останутся в вашем личном кабинете навсегда. Вы сможете обращаться к ним, когда захотите освежить знания.
Я смогу изучать материалы после окончания курса?
Лекции можно смотреть в любое удобное время.
В какое время проходят занятия?
Купить
Как анализировать большие наборы данных в Excel (6 эффективных методов)
В статье показано, как анализировать больших набора данных в Excel.
Очень важно знать плюсы и минусы своего бизнеса. Ведение счетов и записей о продажах организации — это динамичный процесс. Потому что продажи, покупки или обмен происходят часто в течение определенного периода времени. Поэтому, если вы хотите узнать данные о продажах или прибыли за последние 3 или 4 месяца с сегодняшнего дня, вы можете найти огромное количество данных о них. К счастью, в Excel есть несколько довольно интересных функций, которые помогут вам в этом вопросе. Вы можете анализировать эти большие наборы данных по частям в Excel и, следовательно, упростить ваши вычисления.
Скачать практическую рабочую тетрадь
6 способов анализа больших наборов данных в Excel
1. Анализ больших наборов данных с помощью сводной таблицы
2. Команда фильтра для анализа больших наборов данных
3. Внедрение редактора Excel Power Query для анализа
4. Анализ больших данных с помощью сводной диаграммы
5.
Использование Power Pivot для анализа больших наборов данных
6. Применение функции анализа данных
Раздел практики
Вывод
Статьи по Теме
Загрузить рабочую тетрадь
6 способов анализа больших наборов данных в Excel
В этой статье я буду использовать следующий набор данных. Хотя этот набор данных не содержит большого объема данных, мы можем показать примеры того, как с его помощью анализировать больших набора данных .
У нас есть продажи информации о некоторых электронных устройствах в этом наборе данных.
Этот набор данных удобно использовать как таблицу при их анализе. Чтобы преобразовать эти данные в таблицу
- Сначала выберите набор данных, а затем перейдите к Вставить >> Таблица .
- После этого появится диалоговое окно . Убедитесь, что вы выбрали Моя таблица имеет заголовки .
- Далее нажмите OK .
При выполнении этой операции ваш набор данных будет преобразован в формат Excel 9.0003 таблица .
1. Analyz ing Большие наборы данных с Сводная таблица
Существует несколько различных способов анализа больших наборов данных в Excel. Один из них — использовать сводную таблицу из вкладки «Вставка ». Сводная таблица помогает нам увидеть информацию о продажах товаров по нужным столбцам и строкам. Кроме того, мы можем увидеть их через месяцы или годы. Давайте рассмотрим процесс ниже.
Шаги:
- Сначала выберите таблицу данных, а затем перейдите к Вставить >> Сводная таблица .
- Позже появится диалоговое окно . Вы можете выбрать Новый рабочий лист или Существующий рабочий лист . Выбирайте по своему удобству.
Вы можете выбрать Новый рабочий лист или Существующий рабочий лист . Выбирайте по своему удобству.- После этого, когда мы выбрали New Worksheet , мы найдем наши функции Pivot Table на новом рабочем листе. Перетащите нужные диапазоны ( Дата , Цена , Имя продавца и т. д.) в Поля сводной таблицы . Это покажет вам сводку информации о продажах в сводной таблице .
- Здесь я перетащил диапазон Date в поле Rows . Это автоматически дало мне другой диапазон, который является диапазоном месяцев , чтобы я мог наблюдать информацию о продажах в месяцах.
- После этого я также добавил Имя продавца в поле столбцов , потому что вы можете захотеть узнать, какой сотрудник продал больше всего товаров, и дать ему премию.
- И в поле Values я перетащил диапазон Price , чтобы увидеть, сколько продаж произошло за этот период.
Если после этого вы посмотрите на сводную таблицу , вы увидите следующую информацию. В соответствии с Полями , которые мы выбрали, мы увидим
- Вы также можете посмотреть эту информацию по датам . Просто щелкните значок плюс ( + ) рядом с названием месяца .
Таким образом, вы можете анализировать больших набора данных с помощью сводной таблицы . Это действительно полезно, когда вы работаете с почти повседневной информацией о продажах.
Подробнее: Как анализировать данные в Excel с помощью сводных таблиц (9 подходящих примеров)
2. Команда фильтра для анализа больших наборов данных
Другим способом анализа больших наборов данных в Excel является использование команды фильтра . Это может помочь вам отфильтровать информацию на основе заданных вами критериев.
Шаги:
- Сначала выберите диапазон набора данных, а затем перейдите к шагу 9.0003 Главная >> Сортировка и фильтрация >> Фильтр
- После этого в заголовках появится значок фильтра .
- Однако, если вы хотите увидеть общий объем продаж с НДС при использовании команды фильтра , вам необходимо использовать следующую формулу.
= ПРОМЕЖУТОЧНЫЙ ИТОГ (9, Таблица 13 [Цена нетто])
В этой формуле используется Функция ПРОМЕЖУТОЧНЫЙ ИТОГ для возврата общего объема чистых продаж отфильтрованных данных.
- Теперь отфильтруйте по своему желанию. Во-первых, я покажу вам, как фильтровать по месяцам. Просто щелкните раскрывающийся значок рядом с заголовком Date и отметьте один или несколько месяцев .
- После проверки May и нажатия OK вы увидите информацию о продажах за этот месяц. Вы также увидите общий объем продаж с НДС в этом месяце.
- Точно так же, если вы хотите узнать, сколько продаж было сделано отдельным продавцом, просто проверьте его имя и нажмите OK .
- После этого вы увидите соответствующую информацию о продажах на листе Excel.
Таким образом, вы можете анализировать больших набора данных с помощью Filter Command .
3. Внедрение редактора Excel Power Query для анализа
Редактор Power Query может быть очень полезен для анализа больших наборов данных в Excel. Давайте рассмотрим процесс ниже.
Шаги:
- Сначала выберите таблицу данных, а затем перейдите к Данные >> Из таблицы/диапазона .
- После этого вы увидите свой набор данных в редакторе Power Query . Эта функция также имеет фильтр . Мы можем применить их для подведения итогов или просмотра некоторых конкретных записей о продажах или сотрудниках.
- Хотя это и не обязательно, удобно опустить часть времени в датах
- Позже используйте раскрывающийся значок , чтобы отфильтровать дату. Вы можете фильтровать по отдельным датам или месяцам.
- Я предпочел фильтровать по месяцам, поэтому выбрал Май . Существуют варианты фильтрации за определенный период времени с помощью пользовательского фильтра . Вы можете использовать его, если хотите просмотреть записи о продажах за несколько месяцев или периодов.
- После этого вы увидите продажи за месяц май .
- Если вы хотите загрузить эти данные на лист Excel, просто нажмите Close & Load .
После этого вы увидите записи о продажах за месяц май в виде таблицы на новом листе.
Вы также можете фильтровать по Именам продавцов или Ценовому диапазону в редакторе Power Query и загружать их на новый лист, следуя той же процедуре.
Таким образом, вы можете проанализировать большие наборы данных с помощью редактора Power Query .
Аналогичные показания
- Как анализировать данные шкалы Лайкерта в Excel (с помощью Quick Steps)
- Анализ качественных данных анкеты в Excel
- Как анализировать масштабированные по времени данные в Excel (с помощью простых шагов)
4. Анализ больших данных с помощью сводной диаграммы
Если вы хотите проанализировать свои данные с помощью диаграммы, вы можете эффективно использовать Сводная диаграмма . Давайте пройдемся по описанию ниже.
Шаги:
- Сначала выполните процедуру Раздела 1 , чтобы создать сводную таблицу .
- Позже на листе сводной таблицы перейдите к Анализ сводной таблицы >> Сводная диаграмма .
- Убедитесь, что вы выбрали любую из ячеек сводной таблицы .
- После этого вы увидите множество вариантов Сводная диаграмма . Выберите любой из них и нажмите OK . Я выбрал простую гистограмму .
- После этого вы увидите общий объем продаж за месяц на сводной диаграмме .
- Кроме того, если вы хотите увидеть продаж по дат , просто перетащите диапазон Дата над диапазоном Месяц в полях сводной таблицы .
- Позже вы увидите продажи по датам на графике .
Таким образом, вы можете анализировать больших набора данных с помощью сводной диаграммы . Если ваши данные содержат большое количество годовых продаж или транзакций, вы можете эффективно использовать сводную диаграмму для лучшей визуализации.
5. Использование Power Pivot для анализа больших наборов данных
Вы также можете сделать сводную таблицу анализ больших наборов данных с помощью функции Power Pivot . Пожалуйста, следуйте приведенным ниже инструкциям.
Шаги:
- Сначала закройте текущую рабочую книгу , откройте новую рабочую книгу и перейдите к Power Pivot >> Управление .
- Далее в окне Power Pivot выберите Home >> From Other Sources .
- После этого появится мастер импорта таблиц . В этом мастере выберите Excel File >> Next .
- После этого просмотрите книгу Excel, в которой хранится набор данных.
- Позже нажмите Далее .
- Мы будем работать над листом Power Pivot этой рабочей тетради. Итак, мы проверили его и нажали Finish .
- Появится подтверждающее сообщение, просто нажмите Закрыть .
- После этого эта операция загрузит набор данных выбранного листа в виде таблицы в Power Pivot
- Данные, которые вы видите, не имеют правильного имени заголовка. Переименуйте заголовки столбцов, щелкнув правой кнопкой мыши и выбрав Переименовать столбец в контекстном меню .
- Чтобы выполнить анализ сводной таблицы , мы выбираем сводную таблицу .
- В это время появится диалоговое окно . Если вы хотите, чтобы ваша сводная таблица находилась в Existing Worksheet , выберите ее и нажмите OK . В моем случае я выбрал сводную таблицу , чтобы она появилась в новом рабочем листе .
В моем случае я выбрал сводную таблицу , чтобы она появилась в новом рабочем листе .- Далее вы увидите Поля сводной таблицы на новом листе. Выберите Power Pivot , и он покажет вам все диапазоны , которые он содержит, например, Дата , Цена , НДС и т. д.
- После этого перейдите по этой ссылке раздела 1 , чтобы узнать, как анализировать данные с помощью сводной таблицы .
- Вы можете использовать диаграмму для визуализации ежемесячной информации о продажах. Чтобы узнать, как анализировать данные с помощью сводной диаграммы , пожалуйста, перейдите по этой ссылке Раздел 4 .
6. Применение функции анализа данных
И последнее, но не менее важное: если вы хотите, чтобы весь анализ данных был на одном листе, вы должны использовать функцию анализа данных из вкладки данных .
Это сэкономит вам огромное количество времени. Давайте рассмотрим процесс ниже.
Шаги:
- Сначала выберите таблицу и выберите Analyze Data .
- Сразу после этого вы увидите окно Analyze Data в правой части листа Excel.
Прокрутите вниз, и вы получите опции для анализа ваших данных. Ниже вы увидите вариант анализа сводной таблицы .
На следующем изображении показан вариант анализа Bar Chart для сравнения Price и Net Price продукта.
- Давайте просто выберем график Net Price by Product , чтобы показать вам анализ.
Эта операция покажет вам гистограмму анализа изменения Чистая цена по Продуктам .
Вы найдете другие варианты, если прокрутите вниз.
Выберите любой на ваш выбор и приступайте к анализу.
Таким образом, вы можете анализировать больших набора данных с помощью Функция анализа данных .
Подробнее: [Исправлено:] Анализ данных не отображается в Excel (2 эффективных решения)
Секция практики
Здесь я даю вам набор данных из этой статьи, чтобы вы могли практиковать эти методы самостоятельно.
Заключение
В конце концов, мы можем предположить, что вы узнаете очень продуктивные методы анализа больших наборов данных в Excel. Если вы не знаете, как анализировать свои большие наборы данных , вам придется использовать ручные процессы, которые будут стоить вам много времени. Если у вас есть какие-либо лучшие методы, вопросы или отзывы об этой статье, поделитесь ими в поле для комментариев. Это поможет мне обогатить мои будущие статьи. Для получения дополнительных запросов, пожалуйста, посетите наш веб-сайт ExcelDemy .
Связанные статьи
- Анализ качественных данных в Excel (с помощью простых шагов)
- Как анализировать данные количественной ПЦР в Excel (2 простых метода)
- Выполнение тематического исследования с использованием анализа данных Excel
- Как анализировать текстовые данные в Excel (5 подходящих способов)
Анализ больших наборов данных с помощью Power Pivot в Microsoft Excel – Excel with Business
Контекст для Power Pivot… Если вы часто пользуетесь Excel, то вы, вероятно, знакомы со сводными таблицами. Они используются для быстрого получения информации из небольших объемов данных, а также могут быть преобразованы в простые для понимания графики. Но даже у Excel есть свои ограничения. При объединении таблиц, обработке больших наборов данных, содержащих более миллиона строк, или выборе данных из нескольких источников Excel будет сталкиваться с трудностями.
Неожиданное завершение работы Excel, очень медленная работа или истечение времени ожидания, требующее принудительного завершения работы, могут вызвать разочарование!
Итак, что произойдет, если у вас есть более одного миллиона строк (точнее, 1 048 576) данных? Вы используете Power Pivots.
В 2010 году Microsoft добавила Power Pivots в Excel, чтобы облегчить анализ больших объемов данных. Power Pivot может обрабатывать сотни миллионов строк данных, что делает его лучшей альтернативой Microsoft Access, который до Excel был единственным способом сделать это. Думайте о Power Pivot как о способе использования сводных таблиц в очень больших наборах данных.
Это также полезно, когда данные поступают из нескольких источников. С помощью Power Pivot вы можете импортировать эти данные только в одну книгу, не используя несколько исходных листов, что может запутать и разочаровать.
Power Pivot был создан для импорта и анализа данных из нескольких источников.
Что угодно, от баз данных Microsoft SQL, Oracle или Access до данных списка SharePoint и текстовых документов, можно использовать в качестве источников данных в Power Pivot.
Доступ к Power Pivot
Power Pivot — это бесплатная надстройка в Excel и постоянная встроенная функция в Excel 2016 и 365. Первым шагом в использовании Power Pivot является добавление его на ленту Excel. В последних версиях Microsoft Excel (13–17 дюймов) Power Pivot встроен, но вам может потребоваться его активация.
Включите Power Pivot, нажав Файл -> Параметры -> Надстройки -> Microsoft Power Pivot для Excel:
Теперь Power Pivot включен, но не совсем готов к использованию. Есть еще один шаг.
Вам нужно будет указать Power Pivot, куда идти для импорта данных. Для этого щелкните вкладку Power Pivot на ленте -> Управление данными -> Получить внешние данные. В списке источников данных много вариантов. В этом примере будут использоваться данные из другого файла Excel, поэтому выберите опцию Microsoft Excel внизу списка. Для больших объемов данных импорт займет некоторое время.
После завершения импорта вы увидите данные в главном окне Power Pivot. Одновременно откроются два окна — обычное окно Excel и окно Power Pivot. Однако вам не обязательно иметь данные на открытой странице Excel.
Создание базовой сводной таблицы Power
Предположим, у вас есть электронная таблица со списком продаж продуктов. Каждая строка представляет собой идентификатор клиента, а столбцы — это имя, номер счета, дата, количество и цена.
Поскольку клиенты имеют более одного счета, но только один идентификатор клиента, информация о клиенте должна повторяться несколько раз. Это не кажется проблемой, когда есть только несколько счетов-фактур. Но когда сумма счетов начнет достигать одного миллиона, будет менее эффективно использовать этот формат в базовом Excel и более эффективно создавать Power Pivot.
Для этого откройте новую книгу Excel. Выберите Power Pivot на ленте, затем нажмите «Управление» -> «Из других источников» и прокрутите вниз до Excel.
На экране выберите файл с помощью кнопки Обзор.
Выбрав файл, нажмите «Далее». Хорошо переименовать заголовок «Дружественное имя» в название, описывающее набор данных. В данном случае заголовок был изменен на «Счета-фактуры». Нажмите Готово.
Успехов! Список счетов был импортирован в Power Pivot Table. Отсюда вы можете создавать диаграммы сводных таблиц так же, как и с меньшими наборами данных (объяснено в следующем разделе). Опять же, причина, по которой Power Pivot будет использоваться здесь, заключается в том, что данные были в другом формате (SQL, Access, Oracle) или если в файле Excel было более миллиона строк. В противном случае использование базовой функции сводной таблицы в Excel будет работать без ошибок.
Создание сводной диаграммы из сводной таблицы Power
Чтобы создать диаграмму из этой сводной таблицы Power, щелкните значок сводной таблицы на ленте Excel и выберите «Сводная диаграмма» (выберите «Сводная таблица», если вы хотите сначала создать обычную сводную таблицу в Excel).
перед созданием диаграммы).
Откроется новая рабочая книга. Используйте панель инструментов полей справа, чтобы выбрать поля для таблицы. В этом примере заказ каждой компании сравнивается по месяцам. Итак, имя клиента, дата и количество выбраны и включены в сводную диаграмму.
Совет: формулы Power Pivot
Power Pivot имеет множество полезных функций и преимуществ. В дополнение к обычным функциям Excel в нем представлено более 75 новых формул. Вот два хороших варианта:
=COUNTROWS: Подсчитывает количество строк в источнике данных. Если у вас есть несколько источников данных, связанных друг с другом идентификатором, например, названием продукта, вы также можете подсчитать количество строк по отношению к этим идентификаторам.
= ПЕРЕКЛЮЧАТЕЛЬ: Switch невероятно полезен для данных, которые необходимо редактировать. Например, у вас могут быть строки с числом, обозначающим определенный месяц, вместо фактического названия месяца — 1 для января, 2 для февраля, 3 для марта и т.
д. Используйте =SWITCH, чтобы изменить (переключить) все числа на фактический месяц.
Power Pivot автоматически использует вычисление =SUM для суммирования числовых данных, что является отличной функцией. Чтобы изменить тип используемого расчета, щелкните правой кнопкой мыши внутри сводной таблицы и выберите «Настройки поля значений» -> «Суммировать значения по вкладке». Как вы можете видеть на изображении, =COUNT, =AVERAGE, =MIN, =MAX и многие другие являются вариантами.
Многие организации используют Microsoft Sharepoint. Панели мониторинга, графики и сводные диаграммы Power Pivot можно публиковать прямо в Sharepoint для быстрого просмотра любым сотрудником вашей организации. Чтобы использовать это, установите подключаемый модуль «Power Pivot для SharePoint» на сайте Sharepoint вашей компании.
Power Pivots может показаться расширенной функцией Excel, но их легко использовать, если вы понимаете, как получить доступ к этой функции и импортировать набор данных.