Чтобы преодолеть ограничение таблиц Excel в 1 048 576 (или 220) строк и 16 384 (214) столбцов, используем функцию «Получить и преобразовать данные». Функция может работать с неограниченным количеством строк и столбцов. Данные можно агрегировать и обобщать, чтобы затем работать с ними в виде сводной таблицы.
Возможности Power Query ограничены только количеством ресурсов, доступных в вашей системе, и позволяют подключаться к внешним источникам данных, таким как базы SQL или наборы данных Power BI.
Зачем превышать лимит в 1 миллион строк в Excel
Компании собирают данные в геометрической прогрессии. По оценкам Statista, общий объём данных, собранных в 2010 году, составил 2 зеттабайта, а к 2025 году он превысит 181 зеттабайт. Это означает, что специалистам в области бизнеса, таким как бухгалтеры, финансовые и бизнес-аналитики, приходится работать со всё более большими объёмами данных.
У Microsoft Office более 1 миллиарда пользователей по всему миру. Это делает Excel одним из самых популярных инструментов бизнес-аналитики и анализа.
Если учесть быстрый рост объёмов данных и широкое использование Microsoft Excel, становится понятно, насколько важно иметь возможность работать с большими наборами данных непосредственно в Excel.
Каково максимальное количество строк в Excel?
В Excel 2007 и более поздних версиях поддерживается максимум 1 048 576 строк и 16 384 столбца (XFD). Microsoft Power Query — это инструмент преобразования данных, встроенный в Excel в разделе «Получение и преобразование данных», который не имеет жёстких ограничений по объёму данных и может работать с наборами данных, содержащими более 1 миллиона строк.
Единственным ограничением использования Power Query является объем доступных системных ресурсов.
Каково максимальное количество строк в Excel Online?
Excel Online не ограничен количеством строк и столбцов на листе и ограничен только размером файла. Большие файлы Excel размером до 250 МБ можно публиковать в сервисе Power BI. Для работы с файлами еще большего размера подписчики Power BI Pro могут работать с файлами Power BI размером до 1 ГБ, а с Premium Capacity — до 10 ГБ.
Импорт больших наборов данных в Excel
Первым шагом к работе с большими наборами данных в Excel является импорт данных. Чтобы перенести большие файлы в Excel, перейдите к Данные> Получить данные в разделе «Получение и преобразование данных» ленты Microsoft Excel.
В примере мы импортируем большой файл .csv, который сохранили на рабочем столе.
На скриншоте ниже показаны некоторые из доступных источников данных, которые можно использовать в Excel. Можно работать не только с одним файлом, но и объединять папки с отдельными файлами, подключаться к серверам SQL, наборам данных Power BI и другим форматам.
Гибкость в выборе типов поддерживаемых соединений делает получение и преобразование данных лучшим решением для работы с большими объёмами данных в Excel.
После выбора «Из текстового файла/CSV» появится экран с предварительным просмотром первых 200 строк данных. Этот экран поможет убедиться, что вы выбрали правильный файл перед его импортом.
На этом этапе можно либо загрузить данные, либо преобразовать их.
Преобразование данных — запускает редактор Power Query. Инструмент для подготовки, объединения и преобразования больших наборов данных, что невозможно сделать в обычном Excel.
Загрузить — импортирует данные в Excel. Данные будут либо загружены в виде подключения к большому файлу данных, либо импортированы в таблицу Excel, либо загружены в Power Pivot.
В качестве примера будем использовать Power Pivot. Это разновидность сводной таблицы, которая работает с большими наборами данных и позволяет управлять ими в формате, с которым уже знакомо большинство пользователей Excel.
Выберите опцию “Загрузить в”, нажав на кнопку Загрузить.
На следующем экране вы увидите несколько вариантов, куда можно импортировать данные.
Выберите Отчет сводной таблицы на новом листе
В результате вы получите новую сводную таблицу на новом листе, которая связана с 3 миллионами строк данных. Вы можете перетаскивать поля в столбцы или строки и управлять ими так же, как при работе с обычной сводной таблицей.
Excel поддерживает связь с файлом необработанных данных или может импортировать его в модель данных. Это позволяет встраивать данные в Excel, превышая максимальный предел количества строк, если весь файл находится на нескольких листах.
Варианты работы за пределами ограничения в миллион строк Excel
При загрузке данных в Excel есть несколько вариантов работы с ними. Вы можете загрузить данные в таблицу с ограничением по количеству строк. Сводные таблицы и сводные диаграммы не имеют ограничений, или вы можете создать подключение к неограниченному источнику данных.
В следующей таблице описаны варианты работы с большими объёмами данных после их импорта в Microsoft Excel.
| Опция импорта | Что он Делает | Ограничения | Пригодность для более чем 1 миллиона строк |
|---|---|---|---|
| Таблица | Импортирует данные на лист Excel в виде таблицы. | Максимум 1 048 576 строк в таблице. | Не подходит |
| Отчет по сводной таблице | Создает отчет сводной таблицы на основе импортированных данных. | Нет | Подходит |
| Сводная диаграмма | Создает сводную диаграмму на основе импортированных данных. | Нет | Подходит |
| Только Создать соединение | Создаёт подключение к источнику данных для последующего использования. Не импортирует данные в таблицу Excel. | Нет | Подходит |
| Добавление данных в модель данных | Импортирует данные непосредственно в модель данных Excel, а не в рабочий лист. | Нет | Подходит |
Для работы со сводными таблицами и сводными диаграммами их необходимо подключить к модели данных или к источнику данных. Если в качестве источника данных используется обычная таблица, вы будете ограничены 1 миллионом строк.
Как исправить ошибку «Запрос вернул больше данных, чем помещается на листе»
Если вы попытаетесь импортировать в книгу Excel более 1 миллиона строк данных, Excel выдаст ошибку, сообщающую, что запрос вернул больше данных, чем поместится на листе. Это означает, что набор данных, импортируемый в Excel, превышает максимальное количество строк.
Если вы нажмёте «ОК», Excel загрузит только столько данных, сколько поместится в Excel, и не будет загружать данные после достижения предела количества строк.
Чтобы исправить ошибку, нажмите кнопку Отмена.
Набор данных по-прежнему будет отображаться как доступный, но будет отображаться ошибка «Не удалось загрузить на рабочий лист».
Наведите курсор мыши на ошибку «Не удалось загрузить в рабочий лист» и выберите «Загрузить в модель данных»
После загрузки данных вы можете навести курсор мыши на соединение с набором данных, нажать на … три точки в нижней части страницы и выбрать Загрузить в…
Это дает возможность вставить данные в виде сводной таблицы.
Также можно щёлкнуть правой кнопкой мыши по указанным данным и выбрать «Изменить», чтобы запустить редактор Power Query.
Как отредактировать сводный набор данных
После загрузки данных в Power Pivot в правой части Microsoft Excel появится панель «Запросы и подключения». На ней перечислены наборы данных и файлы, которые были загружены в Excel. Щелкните правой кнопкой мыши по набору данных и выберите «Изменить». Откроется редактор Power Query, в котором можно настраивать и преобразовывать входящие данные.
Редактор Power Query позволяет работать с миллионами строк и не ограничен обычным лимитом рабочего листа. Однако потребуется время на освоение, так как интерфейс отличается.
Как выполнять вычисления и преобразовывать большие наборы данных в Excel
После того как вы получите данные для импорта в Excel, вы можете преобразовать их или загрузить. Если выберать преобразование данных, откроется редактор Power Query — инструмент для преобразования и подготовки данных, предназначенный для их анализа. Он используется в широком спектре продуктов Microsoft, включая Excel, Power BI и Azure Analysis Services.
На скриншоте ниже показан редактор запросов.
Можно щелкнуть правой кнопкой мыши по названиям столбцов и выполнить множество распространенных задач, таких как удаление пробелов, замена значений, преобразование в верхний регистр и т. д. На верхней панели можно добавлять вычисляемые столбцы для сложения чисел, фильтровать и сортировать строки, а также выполнять сотни других функций.
Каждый раз, когда вы выполняете вычисления в Excel, на правой панели «Настройки запроса» появляется новый применяемый шаг. После выполнения всех необходимых шагов по преобразованию данных нажмите «Закрыть и загрузить» в левом верхнем углу, чтобы сохранить изменения.
Примечание: Power Query — единственный способ изменить миллионы строк данных в Power BI. Стандартные инструменты и формулы будут работать только в том случае, если данные находятся на листе, который ограничен 1 миллионом строк.
Изменение больших наборов данных в Excel Power Query
Вы можете изменять миллионы строк одним нажатием кнопки и даже переносить запросы между Excel и Power BI, если сотрудники вашей организации используют обе платформы.
Ознакомьтесь с этим обзором от Microsoft, в котором рассказывается о ряде функций, доступных в редакторе Power Query.
Как видите, интерфейс Power Query отличается от Excel, но он удобен и прост в использовании после небольшой практики!
Заключение
Excel — один из наиболее широко используемых в мире инструментов бизнес-аналитики. Компании генерируют и собирают данные в геометрической прогрессии. Специалистам в области бухгалтерского учёта и финансов, как и многим другим, необходимо освоить новый способ использования Excel для работы с большими наборами данных.
В Microsoft Excel есть ограничение в 1 миллион строк, которое можно обойти с помощью функций «Получить и преобразовать данные», Power Query и Power Pivot.
это машинный перевод статьи https://www.popautomation.com/post/excel-million-row-limit