Как в excel открыть файл больше 1048576 строк
Обновлено: 06.07.2024
Такой вопрос часто можно увидеть на форумах, тематических сайтах или просто в списке запросов поисковика. Чаще всего подобная проблема сопровождается гипертрофированными размерами книги Excel. Из этой статьи вы узнаете несколько самых действенных способов уменьшения размера файла и ускорения его работы.
1. Уменьшаем размер используемого диапазона листа
Наиболее часто проблема увеличения размера книги и ее торможения бывает связана с разросшимся используемым диапазоном листа. Это тот диапазон, который Excel запоминает и с которым каждый раз работает. Если вы используете на листе всего несколько ячеек, но когда то у вас там была таблица в 1000 строк, то даже после ее удаления Excel будет обрабатывать все эти строки, тем самым замедляя работу файла.
Чтобы проверить, есть ли на листе лишние пустые столбцы и строки нужно нажать сочетание клавиш «Ctrl+End» . Вы попадете в последнюю ячейку, которую использует программа. Если она явно за пределами ваших данных, то лишние строки и столбцы стоит удалить. Для этого в столбце А встаем в ячейку ниже последней нужной нам строки и нажимаем «Ctrl+Shift+End» .
Выделятся все лишние строки. Удаляем их. То же самое повторяем для столбцов. Если их много – файл может зависнуть. В таком случае проще использовать небольшой макрос. Нажмите Alt+F11 или кнопку Visual Basic на вкладке Разработчик (как ее активировать - показывали здесь ), вставьте новый модуль через меню Insert - Module и скопируйте туда код макроса:
Sub УдалениеЛишнихЯчеек()
For Each Sht In ActiveWorkbook.Worksheets
Чтобы запустить его - на той же вкладке (1) нажмите кнопку "Макросы" (2), выберите в списке "УдалениеЛишнихЯчеек" (3) и нажмите кнопку "Выполнить" (4).
После проделывания всех операций обязательно сохраняем книгу.
2. Пересохраняем файл в другом формате
Если кто-то еще пользуется файлами в старом формате XLS, но уже сидит на более новом ПО (Excel 2007 и новее), то есть смысл пересохранить файл в один из новых форматов: XLSX, XLSM, XLSB. Они более современные, лучше оптимизированы, весят меньше и работают быстрее. Самый компактный из них – XSLB. При сохранении в этом формате размер файла существенно уменьшится (даже если пересохранить в него "новый" XLSX).
3. Удаляем лишние объекты из книги
Часто при копировании в файл данных из сторонних программ (например, 1С) вместе с ними копируются лишние объекты (фигуры, картинки и прочее), которые не всегда можно сразу заметить на листе. Чтобы проверить, есть ли в файле лишние объекты, нужно найти на ленте команду "Найти и выделить" (1) и выбрать "Область выделения" (2). Откроется список объектов листа (3).
Удалить все объекты можно выделив их и нажав клавишу Delete . Чтобы выделить все объекты снова используем команду "Найти и выделить" (1), выбираем пункт "Выделить группу ячеек" (2) и в открывшемся окне выбираем "Объекты" (3).
Если объектов много, то имеет смысл удалять их простым макросом. Код - ниже. Его нужно запустить поочередно на каждом листе.
Sub УдаляемОбъекты()
For each shp in ActiveSheet.Shapes
4. Уменьшаем размер сводных таблиц
Если в вашем файле есть сводные таблицы, то имеет смысл отключить хранение данных для них вместе с файлом. Это потребует обновления таблицы каждый раз при открытии книги, но уменьшит размер файла.
Для отключения этой опции выберите нужную сводную таблицу, перейдите в ее Параметры (правая кнопка мыши - Параметры сводной таблицы) и на вкладке Данные (1) снимите галочку с пункта "Сохранять исходные данные вместе с файлом" (2).
5. Заменяем формулы на значения
Если в файле содержится очень много формул, то нужно постараться везде, где это возможно, заменить их на значения. Постоянный пересчет сильно замедляет работу файла. И хоть его можно отключить (как - смотри здесь ), рекомендуем избавляться ото всех лишних формул на листах.
6. Удаляем лишнее форматирование
Красивые документы нравятся всем, но чем более пёстрый лист у Вас получился, тем медленнее будет работать файл. А условное форматирование сказывается на быстродействии еще больше, так как основано на формулах и постоянно пересчитывается. Рекомендуем удалять все лишние форматы, оставляя только то, что действительно нужно (как очистить форматы - смотри здесь ). Минимализм сейчас в моде.
Чтобы удалить лишние правила условного форматирования выбираем на вкладке "Главная" инструмент "Условное форматирование", кнопка "Управление правилами". В открывшемся диспетчере выбираем весь лист (1), выделяем лишнее правило (2) и удаляем его (3). Повторяем, пока не удалим всё лишнее.
Excel – невероятно функциональная программа, которая имеет очень много возможностей. Но вместе с тем, у нее есть некоторые ограничения. В частности, на количество строк. Если вдруг попытаться сделать таблицу, в которой больше строк, чем задано, то появится окно с ошибкой.
Правда, этот предел на самом деле, только теоретический, потому что на практике его достичь, скорее всего, не получится. Тем не менее, многих пользователей интересует такой вопрос чисто для интереса, чтобы понять, насколько безграничными являются возможности программы и действительно ли они настолько безграничны, как часто это преподносится в мануалах по Excel.
Технические характеристики и ограничения в Excel
Есть еще и второй способ, который можно использовать, если не получается достичь желаемого результата с помощью первого. Можно попробовать открыть электронную таблицу в программе Microsoft Access, а потом сохранить несколько подмножеств информации в формат Excel.
Microsoft Access – программа для создания и ведения баз данных. Является не настолько популярной, как Excel, поскольку имеет более специализированное предназначение.
Есть еще одна причина, почему возникает эта ошибка. Она может появляться из-за того, что пользователь пытается вставить данные, общий объем которых превышает максимально поддерживаемый таблицей. Для решения этой проблемы необходимо нажать по области экрана и выбрать ту, которая будет помещать все элементы, включая разделители.
Давайте теперь более подробно рассмотрим тему, сколько максимально строк и колонок может использоваться в одном документе Excel? На самом деле, цифра будет гораздо большей, чем та, которая приводится выше. Почему? А вот уже совсем скоро вы узнаете.
Максимальное количество строк в Excel
По стандарту Excel дает возможность работать в рамках одного документа с тремя листами, каждый из которых имеет по 1 048 576 строк. При этом количество листов на самом деле ограничено только оперативной памятью компьютера. Поэтому большие документы не получится открыть на дешевых ноутбуках, в которых объем оперативной памяти не превышает 2 ГБ (типа смартбуков и других устройств подобного класса).
Также такая таблица не будет открываться очень хорошо на смартфонах и планшетах. Тем не менее, по стандарту рабочая книга не должна открываться с проблемами, если в ней содержится три листа с таким количеством строк.
Но есть и мнение, которое говорит, что если использовать 64-разрядную версию офисного пакета, то максимальное количество поддерживаемых строк будет больше. Соответствует ли это утверждение действительности? На самом деле, да, но только теоретически. На практике же оказывается, что это ограничение заложен непосредственно в продукт, а не обусловлено количеством битов, которое поддерживается.
Кроме всего прочего, если размеры листа слишком большие, то могут возникнуть некоторые проблемы, если использовать такой файл на другом компьютере или с другой версией Excel. Поскольку разработчики желают, чтобы документ был доступен каждому, они специально занижают количество поддерживаемых строк.
Поэтому рабочий лист будет работать правильно как на 32-разрядных машинах, так и 64-битных, независимо от того, сколько оперативной памяти содержится в них.
Как же определить количество строк в Excel? Для этого необходимо нажать по любому столбцу, в котором не содержится никаких значений, после чего нажать на комбинацию клавиш Ctrl + стрелка вниз. После этого программа сама покажет вам последний ряд.
Кстати, этот трюк можно использовать для хранения информации, которая не должна быть непосредственно перед глазами пользователя или же ее лучше скрыть, чтобы все было более эстетично. Например, указать формулу или промежуточные данные, которые важны для работоспособности конкретного документа, но при этом они загромождают драгоценное место основной таблицы.
Видим, что ничего сложного в том, чтобы самостоятельно определить максимальное количество поддерживаемых рядов Excel нет. Необходимо просто нажать буквально две кнопки.
Максимальное количество столбцов в Excel
Чтобы определить, какое наибольшее количество колонок поддерживает программа Microsoft Excel, необходимо нажать комбинацию клавиш Ctrl + стрелка вправо. После этого приложение автоматически покажет вам границу листа. Аналогично последней строке, последние столбцы также могут использоваться для хранения вспомогательной информации, которые хотя и не имеют особой секретности (обнаружить их очень просто), но зато подходят для освобождения основного пространства документа под другие задачи.
Ведь практика показывает, что даже этот лимит достичь невозможно. Нет такой таблицы, в которой потребовалось бы так много рядов и колонок использовать. Даже если одну и ту же таблицу дополнять в течение нескольких лет, все равно еще куча места остается. Поэтому этот предел является лишь теоретическим, поэтому не нужно переживать, что когда-нибудь вы столкнетесь с тем, что нужно будет его превышать. А даже если вы станете первым человеком, которому придется это сделать, то всегда можно предложить таблицу на втором и третьем листах или же создать новый файл, а потом просто установить взаимосвязь между этими двумя документами.
Так все же, сколько максимально колонок поддерживает программа? На одном листе может поместиться до 16 384 колонок. Точно так же, как и в случае со строками, максимальное количество листов в этом случае будет составлять три штуки.
В файле Excel 2010 имеется около 100 строк, содержащих данные, но весь рабочий лист отображает 1048576 пустых строк (делая файл размером около 2,5 мб). Мне нужно удалить пустые строки после данных. Но выбор строки и ее удаление ничего не делают. Как удалить эти нежелательные строки?
Может кто-нибудь объяснить, что не так с этим файлом?
В вашем файле определенно что-то не так. Может быть проще скопировать 100 строк, которые вы хотите сохранить , в новый файл рабочей книги, а не пытаться удалить миллион строк .
Поиск дубликатов во всем найденном документе и удаление более 1 миллиона дубликатов. Таким образом, в этой книге есть скрытые символы или что-то занимающее место. Это все еще не помогло, потому что размер сохранения после этого увеличился до 35 МБ.
Решение: . Когда я скопировал строки, которые вы заселили в новую книгу, размер сохраненного файла составляет всего 10 тыс.
2007 и более поздних версий: в начале ленты на ленте есть кнопка delete (block called cells), щелкните маленькую стрелку под ней и выберите delete rows.
Microsoft описывает шаги в своих «Как восстановить последнюю ячейку в Excel» . Они также упоминают шаг «Сохранить», но я бы хотел, чтобы они придавали ему больше внимания. Пока вы не сохраните и не откроете книгу, это не похоже на то, что ваше удаление ничего не сделало!
Копирование хороших строк в новую книгу также является хорошим решением, особенно если у вас нет макросов или формул со сложными ссылками на ячейки, о которых нужно беспокоиться.
Другая причина может заключаться в том, что один или несколько столбцов рабочего листа ссылаются в какой-либо формуле на другом листе без указания количества строк.
Удостоверьтесь, что в столбцах нет форматирования (например, границы ячеек применяются ко всему столбцу). Возьмите полосу прокрутки и перетащите ее до конца. Выберите нижний ряд (1048579, я считаю) и кучу над ним, около 20-30, все, что видно на экране. Щелкните правой кнопкой мыши область заголовка строки и нажмите Delete . Перетащите полосу прокрутки вверх и выберите ячейку, где находятся ваши данные. Выберите другой рабочий лист, затем вернитесь назад. Он должен быть исправлен.
Немного предыстории: файл CSV является файлом Excel CSV, и тот, кто дал файл, сказал, что существует около 2m строки данных.
Когда я импортирую его в Excel, я получаю данные до строки 1,048,576 , затем повторно импортируйте его в новую вкладку, начиная со строки 1,048,577 в данных, но это дает мне только одну строку, и я точно знаю, что их должно быть больше (не только из-за того, что "человек" сказал, что их более 2 миллионов, но из-за информации в нескольких последних рядах строк)
Я подумал, что, возможно, причина этого в том, что мне предоставили файл CSV в виде файла Excel CSV, и поэтому вся информация за 1,048,576 потерян (?).
Нужно ли запрашивать файл в формате базы данных SQL?
Вы должны попробовать delimit он может открыть до 2 миллиардов строк, и 2 миллиона столбцов очень быстро имеют бесплатную 15-дневную пробную версию. Делает работу за меня!
Я бы предложил загрузить файл .CSV в MS-Access.
С помощью MS-Excel вы можете создать соединение данных с этим источником (без фактической загрузки записей на листе) и создать связанную сводную таблицу. Тогда в вашей таблице может быть практически неограниченное количество строк (в зависимости от процессора и памяти: у меня сейчас 15 млн строк с 3 Гб памяти).
Дополнительным преимуществом является то, что теперь вы можете создать сводное представление в MS-Access. Таким образом, вы можете создавать обзоры из сотен миллионов строк, а затем просматривать их в MS-Excel (остерегайтесь ограничения 2ГБ файлов NTFS в 32-битной ОС).
Сначала вы хотите изменить формат файла с CSV на TXT. Это просто сделать, просто отредактируйте имя файла и измените csv на txt. (Windows предупредит вас о возможном повреждении данных, но это нормально, просто нажмите ОК). Затем сделайте копию txt-файла, чтобы у вас было два файла с двумя миллионами строк данных. Затем откройте первый текстовый файл, удалите второй миллион строк и сохраните файл. Затем откройте второй текстовый файл и удалите первый миллион строк и сохраните файл. Теперь измените два файла обратно на csv так же, как вы изначально изменили их на txt.
Excel 2007+ ограничен несколькими миллионами строк (2 ^ 20, если быть точным), поэтому он никогда не загрузит ваш 2-строчный файл. Я думаю, что техника, которую вы называете расщеплением, является встроенной в Excel, но на самом деле она работает только для проблем с шириной , а не для длина проблемы.
Самый простой способ, который я сейчас вижу, - это использовать инструмент для разделения файлов - их много и использовать его для загрузки получающихся частичных csv-файлов в несколько рабочих листов.
pS: "Excel CSV-файлы" не существует, есть только файлы, созданные в Excel, которые используют один из форматов, обычно называемых CSV-файлами .
Вы можете использовать PowerPivot для работы с файлами объемом до 2 ГБ, что будет достаточно для ваших нужд.
Читайте также: