Как двоичные данные сохранить в xlsx

8 способов уменьшить размер файла Excel (которые действительно работают)

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

Иногда легко избавиться от лишних хлопот и контролировать размер файла Excel, а иногда это не так.

А поскольку Excel не является веб-инструментом, большинство из нас работает и сотрудничает, отправляя файлы Excel по электронной почте. А большим файлом Excel, который весит 50 МБ, может быть сложно поделиться с коллегами / клиентами.

В моей последней работе мы использовали почтовый клиент Lotus Notes, который работает очень медленно, и отправка тяжелых файлов Excel через это была пыткой.

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

Примечание: влияние этих методов на уменьшение размера файла Excel будет зависеть от ваших данных. Для некоторых это может помочь уменьшить размер на 50%, а для некоторых это может привести к уменьшению размера только на 5%.

Советы по уменьшению размера файла Excel

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

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

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

Удалите ненужные рабочие листы / данные

Лучшие решения часто самые простые.

И эта техника больше соответствует здравому смыслу, а не волшебству Excel.

Если у вас есть книга Excel, в которой есть листы, которые не нужны (и не используются ни в каких формулах), попробуйте избавиться от них.

Это напрямую связано с размером файла Excel. Поэтому, как только вы удалите некоторые листы и сохраните книгу, вы сразу увидите уменьшенный размер файла.

Результаты теста:

Я создал файл Excel с 10 листами, каждый из которых содержит 2,6 миллиона точек данных (26 столбцов по 100 000 точек данных в каждом столбце).

В результате размер этой книги составляет 143 МБ (и требуется некоторое время, чтобы сохранить и открыть).

Теперь, когда я удалил пять листов, размер уменьшился вдвое (да).

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

Преобразовать в двоичный формат (XLSB)

Знаете ли вы, что просто сохранив файл Excel в формате XLSB, можно уменьшить размер файла?

Все, что вам нужно сделать, это при сохранении книги использовать формат .xlsb.

Вот как это сделать:

1. Щелкните вкладку Файл.
2. Щелкните Сохранить как.
3. Щелкните Обзор.
4. В диалоговом окне «Сохранить как» измените тип файла на двоичная книга Excel (.xlsb).

Результаты теста

У меня была книга Excel, которая занимала 14,6 МБ места.

После преобразования того же файла в формат XLSB я смог уменьшить его размер до 10,4 МБ (уменьшение размера на

Что такое XLSB?

Когда вы сохраняете файл Excel (.xlsx или .xlsm), он сохраняется в формате XML.

Например, если я изменю расширение файла XLSX на ZIP, а затем открою его, я получу вот что.

И если я дальше перейду в папку «xl», а затем перейду в папку рабочего листа, он покажет мне рабочие листы в этой книге (и эти рабочие листы сохранены как XML-документ).

Теперь сделаем то же самое с файлом XLSB.

Поэтому я сначала меняю расширение файла на ZIP, затем открываю его и нахожу Sheet1. Вот что вы получите:

Вы заметите, что и XLSX, и XLSB сохраняют лист в другом формате.

XLSX / XLSM сохраняет его как файл XML, а XLSB сохраняет его как двоичный (.bin) файл.

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

Поэтому, когда вы сохраняете книгу Excel в формате XLSB, размер файла уменьшается. В приведенном выше примере уменьшение размера составило

XLSB против XLSX / XLSM

Когда дело доходит до использования формата XLSB, уменьшение размера является огромным преимуществом. Но при работе с форматом XLSB нужно быть немного осторожным.

Преимущества формата XLSB:

1. Уменьшает размер файла Excel. Это может быть полезно, если у вас есть большие файлы, и вы хотите поделиться ими по электронной почте. Вы можете быстро уменьшить размер, сохранив его в формате XLSB.
2. Файлы XLSB открываются и сохраняются быстрее, чем файлы XML. Судя по проведенным мною ограниченным тестам, файлы XLSB открывались и сохранялись на 25-50% быстрее. Эта разница заметна только в больших файлах Excel. С файлами меньшего размера и XLSB, и XLSX работают быстро.

Хотя XLSB выглядит великолепно, есть несколько причин, по которым вам следует придерживаться файлов XLSX / XLSM:

1. С форматом XLSB у вас нет возможности узнать, есть ли в нем макрос или нет. Это делает его более рискованным, поскольку кто-то может использовать его для выполнения вредоносного кода.
2. Файлы XLSB кодируются в проприетарном формате файлов bin, в то время как XML — это читаемый формат файлов с открытым исходным кодом. Это означает, что если вы используете сторонние инструменты с Excel, лучше использовать формат XML.
3. Power Query не может читать данные из файла XLSB. Поэтому, если вы используете Power Query, лучше хранить данные в форматах XLSX / XLSM.

Как правило, если размер вашего файла небольшой (менее 5 МБ), лучше придерживаться форматов XLSX / XLSM.

Основываясь на том, что я слышал от людей и читал на многих форумах, многие люди предпочитают использовать XLSB в качестве формата файла, когда дело доходит до использования Excel.

Вот хорошая статья о формате XLSB.

Удалите ненужные формулы

Слишком много формул увеличат размер файла вашей книги.

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

Вот шаги, чтобы быстро преобразовать формулы в значения в Excel:

1. Выберите весь рабочий лист / набор данных.
2. нажмите F5 ключ.
3. В диалоговом окне «Перейти» нажмите «Special».
4. В диалоговом окне «Перейти к специальному» выберите «Формулы».
5. Щелкните ОК.

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

Теперь используйте следующие шаги, чтобы преобразовать эти формулы в значения:

1. Скопируйте выделенные ячейки (Ctrl + C)
2. Щелкните вкладку "Главная".
3. В группе «Буфер обмена» нажмите «Вставить».
4. Щелкните значок «Вставить значение».

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

Результаты теста

Я создал файл Excel с 2,7 миллионами точек данных (1048576 * 26), а затем добавил три столбца с формулами (SUM, COUNT и AVERAGE).

Затем я преобразовал эти формулы в значения и сохранил файл под другим именем.

Размер файла без формул примерно на 8% меньше (обратите внимание, что у меня было всего 3 столбца с формулами).

Если у вас много формул, можно увеличить размер.

ПРИМЕЧАНИЕ: Неустойчивые формулы могут действительно увеличить размер вашего файла. В рамках теста, когда я заменил формулу СУММ на изменчивую формулу (СМЕЩЕНИЕ), это привело к скачку размера файла (с 186 МБ до 198 МБ). Поэтому, если у вас есть изменчивые формулы, которые вы можете заменить энергонезависимыми формулами или преобразовать их в значения, это поможет вам уменьшить размер файла.

Сжать изображения (до и после загрузки)

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

Проблема с изображениями заключается в том, что при их сжатии также происходит снижение качества, которое может вам не понадобиться.

При работе с изображениями лучший способ сохранить небольшой размер файла Excel — это сжать изображения перед их вставкой в ​​Excel.

Хороший онлайн-инструмент для этого — TinyPNG (или вы можете использовать Photoshop или любой другой аналогичный инструмент).

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

Ниже приведен снимок экрана, на котором я смог сжать изображение размером 3,9 МБ до 331 КБ с минимальной потерей качества.

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

Вот шаги для сжатия изображения в Excel:

1. Выберите изображение, которое вы хотите сжать.
2. Перейдите на вкладку «Формат работы с изображениями». Это контекстная вкладка, которая появляется только при нажатии на картинку.
3. В группе «Настроить» выберите параметр «Сжать изображение».
4. В диалоговом окне «Сжать изображения»:
   • Снимите флажок «Применить только к этому изображению», если вы хотите сжать все изображения в книге.
   • Выберите «Электронная почта» (96 пикселей на дюйм) для максимального сжатия.
5. Щелкните ОК.
6. Сохраните файл.

Это сжимает все изображения и помогает уменьшить размер файла книги Excel.

Результаты теста:

Я использовал опцию сжатия изображения в Excel, используя одно изображение.

Ниже приведены результаты:

При использовании параметра сжатия изображения размер файла уменьшился с 445 КБ до 43 КБ (уменьшение размера файла примерно на 90%).

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

Удалите сводный кеш для экономии места

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

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

Сводный кеш — это объект, содержащий реплику источника данных.

Хотя вы его не видите, он является частью книги и связан со сводной таблицей. Когда вы вносите какие-либо изменения в сводную таблицу, она не использует источник данных, а использует сводный кеш.

Хотя вы думаете, что напрямую связаны с исходными данными, на самом деле вы получаете доступ к сводному кешу (а не к исходным данным), когда вносите изменения в сводную таблицу.

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

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

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

Теперь есть два способа уменьшить размер файла при работе со сводными таблицами.

1. Сохраните исходные данные и удалите Pivot Cache
2. Сохраните Pivot Cache и удалите исходные данные

Давайте посмотрим на оба этих метода.

Сохранить исходные данные и удалить сводный кеш

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

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

Ниже указана разница в размере при сохранении файла Excel со сводным кешем и без него.

Вот шаги, чтобы НЕТ сохранить сводный кеш:

1. Выберите любую ячейку в сводной таблице.
2. Щелкните вкладку "Анализ".
3. Щелкните значок «Параметры» сводной таблицы.
4. В диалоговом окне «Параметры сводной таблицы» перейдите на вкладку «Данные».
5. В параметрах данных сводной таблицы снимите отметку «Сохранить исходные данные в файл». Этот параметр гарантирует, что сводный кэш не сохраняется при сохранении книги (и помогает уменьшить размер файла Excel).
6. Установите флажок «Обновлять данные при открытии файла». Этот параметр гарантирует, что сразу после открытия книги сводная таблица будет обновлена, а сводный кеш будет автоматически сгенерирован. Если вы не отметили этот параметр, вам придется обновить сводную таблицу вручную, чтобы создать сводный кеш.
7. Щелкните ОК.

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

Сохраните сводный кеш и удалите исходные данные

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

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

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

Для этого просто удалите данные (либо удалив данные, либо рабочий лист с данными).

Если вы хотите вернуть исходные данные, дважды щелкните ячейку «Общий итог» сводной таблицы, и она мгновенно сгенерирует весь набор данных на новом листе.

Сжать файл (ZIP IT)

Это то, что вы можете делать вне приложения Excel.

Когда вы заархивируете файл Excel, вы сразу увидите уменьшение его размера примерно на 10-15%.

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

Вот шаги, чтобы заархивировать файл Excel (для Windows):

1. Щелкните правой кнопкой мыши файл Excel.
2. Наведите курсор на опцию Отправить.
3. Нажмите на опцию «Сжатая (заархивированная) папка».

Это создаст заархивированный файл для выбранной книги Excel.

Вы также заметите уменьшение размера файла (как показано ниже).

Результаты теста

С помощью этой техники мне удалось уменьшить размер файла книги Excel на 8%.

Этот метод лучше подходит для файлов размером 20–50 МБ. По мере увеличения размера файла процентное уменьшение не очень велико.

Удалить форматирование данных

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

Хотя, основываясь на своих тестах, я заметил, что это не оказывает большого влияния.

Ниже приведены размеры двух книг Excel, одна с 2,6 миллиона точек данных (на одном листе), а другая с теми же данными, но с примененным к ней форматированием (цвет ячейки, граница и курсив).

Результаты теста

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

Размер файла с данными составляет 14,6 МБ, а файла с данными и форматированием — 14,9 МБ.

Поэтому, если вы хотите уменьшить размер файла книги Excel, вы можете найти ненужное форматирование. Удаление может сэкономить место на диске.

Вот шаги, чтобы мгновенно удалить все форматирование, сохранив при этом данные:

1. Выберите весь набор данных (из которого вы хотите удалить форматирование).
2. Щелкните вкладку "Главная".
3. В группе редактирования нажмите «Очистить».
4. Нажмите «Очистить форматы».
5. Сохраните книгу.

Удалить условное форматирование

Как и обычное форматирование данных, условное форматирование также увеличивает размер файла.

Однако, основываясь на своих тестах, я заметил, что разница меньше (если у вас нет нескольких правил, применяемых к нескольким листам с огромными наборами данных).

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

Помимо размера, есть еще одна веская причина убрать условное форматирование — СКОРОСТЬ.

Условное форматирование непостоянно и при любых изменениях на листе пересчитывается. Это может привести к замедлению работы книги.

Результаты теста

Я создал файл с 27,2 миллионами точек данных и применил к нему 5 правил условного форматирования.

Разница в размере составляет всего несколько килобайт.

Советы по повышению производительности Excel

Хотя описанные выше методы помогут вам уменьшить размер файла Excel, вот несколько дополнительных советов по повышению производительности книг Excel:

1. Избегайте использования летучих формул. Помимо увеличения размера файла, изменчивые формулы также замедляют работу ваших книг. Примеры изменчивых функций: RAND, TODAY, OFFSET, INDIRECT и т. Д.
2. Если вам нужно создать сводку для большого набора данных, вместо использования формул, посмотрите, можете ли вы вместо этого использовать сводную таблицу. Сводная таблица работает быстро и интуитивно понятно по сравнению с формулами.
3. Если вы извлекаете данные из нескольких источников или объединяете данные из нескольких файлов, используйте Power Query для получения и преобразования данных. Вы можете сохранить данные как соединение Power Query, а не хранить их на листе. Вы можете легко создать сводную таблицу, используя подключения, созданные с помощью Power Query.
4. Преобразуйте табличные данные в таблицу Excel и используйте таблицу Excel в формулах. Это ускоряет работу вашей книги, а формулами, созданными с использованием структурированных ссылок Excel Table, легче управлять (поскольку они более интуитивно понятны).
5. Избегайте ссылки на всю строку или столбец в формуле. Это повысит эффективность ваших формул.
6. Если в вашей книге много формул, отключите автоматический расчет и переключитесь на ручной расчет. Таким образом, каждый раз при изменении рабочих листов формулы не будут пересчитываться. Вы можете внести это изменение, перейдя на вкладку «Формула» и изменив «Параметры расчета».

Надеюсь, вы найдете метод и советы из этого руководства полезными.

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

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

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

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

Как импортировать данные в Excel

Независимо от того, какие данные вы импортируете, вы начнете процесс одинаково. Нажмите File> Import, чтобы начать. Вы увидите это всплывающее окно, позволяющее вам выбрать формат исходного файла, который вы хотите импортировать:

В этом диалоговом окне вы можете импортировать разделенные запятыми значения (CSV), FileMaker Pro, HTML и текстовые файлы. Если вы хотите импортировать другой тип файла электронной таблицы, лучше всего экспортировать электронную таблицу в другом формате из исходной программы. У большинства программ не должно возникнуть никаких проблем с экспортом в CSV или текст. В этом примере я буду использовать CSV, потому что это общий формат данных, используемый для всего: от данных исследований до электронных таблиц контактов в социальных сетях.

Запустить мастер импорта текста

Нажмите Импорт , и вы увидите Мастер импорта текста:

Мастер импорта текста в Excel делает все возможное, чтобы упростить этот процесс, и по большей части он работает действительно хорошо. В большинстве случаев вы будете нажимать « Далее» до тех пор, пока мастер не исчезнет, ​​а ваши данные не отобразятся в электронной таблице. Как вы можете видеть на изображении выше, текстовый мастер определил, что этот файл разделен — каждая ячейка разделена запятой. Поскольку это правильно, мы можем просто нажать Далее>, чтобы продолжить импорт (если это не так, читайте советы по импорту с фиксированной шириной).

Установить разделители данных

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

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

Обращайтесь к последовательным разделителям как к одному блоку, делая именно то, что он говорит; в случае запятых наличие двух запятых подряд создаст одну новую ячейку. Если флажок не установлен (по умолчанию), это создаст две новые ячейки.

Текстовое поле имеет важное значение; когда мастер импортирует электронную таблицу, некоторые ячейки будут обрабатываться как числа, а некоторые — как текст. Символ в этом поле сообщит Excel, какие ячейки следует рассматривать как текст. Обычно вокруг текста будут кавычки («»), так что это опция по умолчанию. Текстовые квалификаторы не будут отображаться в окончательной электронной таблице. Вы также можете изменить его на одинарные кавычки (») или ни одного, и в этом случае все кавычки останутся на месте, когда они будут импортированы в окончательную электронную таблицу.

Задать форматы данных

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

Значением по умолчанию для формата данных столбца является « Общее» , которое автоматически преобразует данные в числовой формат, формат даты и текст. По большей части, это будет работать просто отлично. Если у вас есть особые потребности в форматировании, вы можете выбрать Текст или Дата:. Параметр даты также позволяет выбрать формат, в который импортируется дата. И если вы хотите пропустить определенные столбцы, вы можете сделать это тоже.

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

Последний параметр в этом диалоговом окне — это меню « Дополнительно» , в котором можно настроить параметры, используемые для распознавания числовых данных. По умолчанию используется точка в качестве десятичного разделителя и запятая в качестве разделителя тысяч, но вы можете изменить это, если ваши данные отформатированы по-разному.

После того, как эти настройки набраны по вашему вкусу, просто нажмите « Готово» и импорт завершен.

Использовать фиксированную ширину вместо разделителей

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

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

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

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

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

Импорт HTML аналогичен импорту CSV или текстовых файлов; выберите файл, выполните те же действия, что и выше, и ваш HTML-документ будет преобразован в электронную таблицу, с которой вы можете работать (это может оказаться полезным, если вы хотите загрузить HTML-таблицы с веб-сайта или если данные веб-формы сохранено в формате HTML).

Экспорт данных из Excel

Экспортировать данные намного проще, чем импортировать их. Когда вы будете готовы к экспорту, нажмите Файл> Сохранить как… (или воспользуйтесь удобным сочетанием клавиш Excel сочетания клавиш ), и вам будет предложен ряд вариантов. Просто выберите тот, который вам нужен.

Вот разбивка нескольких наиболее распространенных:

• .xlsx / .xls : стандартные форматы Excel.
• .xlt : шаблон Excel.
• .xlsb : формат Excel, написанный в двоичном формате вместо XML, который позволяет сохранять очень большие таблицы быстрее, чем стандартные форматы.
• .csv : значение, разделенное запятыми (как в первом примере импорта, использованном выше; может быть прочитано любой программой для работы с электронными таблицами).
• .txt : несколько слегка отличающихся форматов, которые используют вкладки для разделения ячеек в вашей электронной таблице (если есть сомнения, выберите текст с разделителями табуляции вместо другой опции .txt).

Когда вы выбираете формат и нажимаете « Сохранить» , вы можете получить предупреждение, которое выглядит так:

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

Один шаг ближе к мастерству в Excel

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

Вы импортируете или экспортируете файлы Excel на регулярной основе? Для чего вы считаете это полезным? Есть ли у вас какие-либо советы, чтобы поделиться или конкретные проблемы, для которых вы еще не нашли решение? Поделитесь ими ниже!

Как сохранить файл CSV в таблице Excel

Этим способом Вы можете импортировать данные из файла .csv в существующий или в новый лист Excel. Вы не просто откроете CSV файл в таблице Excel, а именно конвертируете формат .csv в .xlsx (если Вы используете Excel 2007, 2010 или 2013) или .xls (в версиях Excel 2003 и более ранних).

1. Откройте нужный лист Excel и кликните по ячейке, куда нужно импортировать данные из файла .csv или .txt.
2. На вкладке Data (Данные) в разделе Get External Data (Получение внешних данных) кликните From Text (Из текста).
3. Найдите файл .csv, который требуется импортировать, выберите его и нажмите кнопку Import (Импорт), или просто дважды кликните по нужному CSV файлу.
   
4. Откроется Мастер импорта текстов, Вам нужно просто выполнить его шаги.

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

• Шаг 1. Выберите формат данных и номер строки, с которой нужно начинать импорт. Чаще всего выбирают Delimited (С разделителями) и со строки 1. Область предварительного просмотра в нижней части диалогового окна мастера показывает первые несколько записей импортируемого файла CSV.
• Шаг 2. На этом шаге нужно выбрать разделители и ограничитель строк. Delimiter (Разделитель) – это символ, который разделяет значения в файле CSV. Если в Вашем файле CSV использован какой-то символ, которого нет в предложенном списке, то поставьте галочку в варианте Other (Другой) и введите нужный символ. В нашем примере мы указали Tab (Знак табуляции) и Comma (Запятая), чтобы каждый товар (они разделены табуляцией) начинался с новой строки, а информация о товаре, например, ID и данные о продажах (они разделены запятыми), были помещены в разные ячейки.Text qualifier (Ограничитель строк) – это символ, в который заключены отдельные значения. Весь текст, заключённый между такими символами, например, “текст1, текст2”, будет импортирован как одно значение, даже если в этом тексте содержится символ, указанный Вами как разделитель.В этом примере мы указали запятую как разделитель, и кавычки как ограничитель строк. В результате, все числа с разделителем десятичных разрядов (которым тоже в нашем случае служит запятая!) будут импортированы в одну ячейку, как это видно в области предпросмотра на рисунке ниже. Если мы не укажем кавычки как ограничитель строк, то все числа будут импортированы в разные ячейки.

Совет: Если в Вашем файле CSV используется последовательно более одной запятой или другого символа-разделителя, то поставьте галочку для параметра Treat consecutive delimiters as one (Считать последовательные разделители одним), чтобы избежать появления пустых ячеек.

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

Совет: Вы можете нажать кнопку Properties (Свойства), чтобы настроить дополнительные параметры, такие как обновление, разметка и форматирование для импортированных данных.

1. 

Замечание: Если Ваш файл CSV содержит численные данные или даты, Excel может преобразовать их с ошибками. Чтобы изменить формат импортированных данных, выберите столбец (столбцы), в которых возникли ошибки, кликните по ним правой кнопкой мыши и в контекстном меню выберите Format cells (Формат ячеек).

Преобразование CSV в Excel: проблемы и решения

Формат CSV используется уже более 30 лет, но несмотря на его длительную историю, он никогда не был официально задокументирован. Название CSV (Comma-Separated Values) возникло из-за использования запятых для разделения полей данных. Но это в теории. На самом деле, множество так называемых CSV-файлов используют другие символы для разделения данных, например:

• Табуляция – TSV-файлы (tab-separated values)
• Точка с запятой – SCSV-файлы (semicolon separated values)

Некоторые вариации файлов CSV разделяют поля данных одинарными или двойными кавычками, другие требуют маркер последовательности байтов из Юникода (BOM), например, UTF-8, для корректной интерпретации Юникода.

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

(все данные помещены в первый столбец)

Файл CSV отображается в Excel неправильно

Признаки: Вы пытаетесь открыть файл CSV в Excel, и все данные попадают в первый столбец.

Причина: Корень проблемы кроется в том, что в Ваших региональных и языковых настройках Windows и в Вашем файле CSV установлены различные разделители. В Северной Америке и некоторых других странах разделителем полей списка по умолчанию является запятая. В то время как в Европейских странах запятая используется как разделитель десятичных разрядов, а разделителем полей списка является точка с запятой.

Решение: Есть несколько возможных решений этой проблемы. Вы можете быстро просмотреть приведённые ниже рекомендации и выбрать наиболее подходящие для конкретно Вашей задачи.

1. Укажите правильный разделитель непосредственно в файле CSV. Откройте файл CSV в любом текстовом редакторе (подойдёт даже обычный блокнот) и в первой строке вставьте следующий текст. Обратите внимание, что это должна быть отдельная строка перед любыми другими данными:
   • Чтобы установить разделитель запятую: sep=,
   • Чтобы установить разделитель точку с запятой: sep=;

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

Замечание: Все показанные решения изменяют разделитель только для данного файла CSV. Если Вы хотите раз и навсегда изменить разделитель, заданный по умолчанию, то Вам подойдёт следующее решение.

1. Изменяем разделители в настройках региональных стандартов. Нажмите кнопку Start (Пуск) и запустите Control Panel (Панель управления), кликните пункт Region and Language (Региональные стандарты) > Additional Settings (Дополнительные параметры). Откроется диалоговое окно Customize Format (Настройка формата), в котором Вам нужно выбрать точку (.) для параметра Decimal symbol (Разделитель целой и дробной части), и установить запятую (,) для параметра List separator(Разделитель элементов списка).

Примечание переводчика: Данные настройки приведены для английской локализации Excel (и ряда других стран). Для русской локализации привычнее будет использовать запятую в качестве разделителя целой и дробной части и точку с запятой для разделения элементов списка.

1. Дважды нажмите ОК, чтобы закрыть диалоговые окна – всё готово! С этого момента Microsoft Excel будет открывать и отображать все файлы CSV (с разделителем запятой) корректно.

Замечание: Установка в Панели управления Windows символов-разделителей целой и дробной части и элементов списка изменит настройки символов, заданные по умолчанию, для всех программ на Вашем компьютере, а не только в Microsoft Excel.

Первые нули теряются при открытии файла CSV в Excel

Признаки: Ваш файл CSV содержит значения с первыми нулями, и эти нули теряются при открытии файла CSV в Excel.

Причина: По умолчанию, Microsoft Excel отображает файл CSV в формате General(Общий), в котором первые нули отсекаются.

Решение: Вместо того, чтобы открывать файл .csv в Excel, запустите, как мы это делали ранее, Мастер импорта текстов, чтобы конвертировать файл CSV в Excel.

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

Excel преобразует некоторые значения в даты при открытии файла CSV

Признаки: Некоторые значения в Вашем файле CSV похожи на даты, и Excel автоматически преобразует такие значения из текстового формата в формат даты.

Причина: Как упоминалось выше, Excel открывает файл CSV в формате General (Общий), при этом значения, похожие на даты, преобразует из текстового формата в формат даты. Например, если Вы открываете файл CSV, содержащий логины пользователей, то запись “апр23” будет преобразована в дату.

Решение: Преобразуйте файл CSV в Excel при помощи Мастера импорта текстов. На шаге 3 мастера выберите столбцы с записями, похожими на даты, и измените формат столбца на текстовый.

Если Вам нужно достичь противоположного результата, то есть в определённом столбце преобразовать значения в даты, тогда установите формат Date (Дата) и выберите подходящий формат даты в выпадающем списке.

Данная статья полностью позаимствована у Антона
Андронова с сайта Microsoft Excel для начинающих
Спасибо ему за подробное и внятное руководство!