Правильный ввод данных в электронную таблицу в первый раз позволяет избежать проблем в будущем и упрощает использование многих инструментов и функций Excel, таких как формулы и диаграммы . В этом руководстве рассматриваются основные задачи и запреты ввода данных в программы для работы с электронными таблицами, такие как Excel, Google Sheets и Open Office Calc.
Инструкции в этой статье относятся к Excel 2019, 2016, 2013, 2010; Excel для Mac, Excel для Office 365 и Excel Online.
Обзор ввода данных в Excel
Рабочие листы Excel — это мощные аналитические инструменты, которые хранят большие объемы данных. Убедитесь, что ваши данные отображаются и анализируются правильно, используя лучшие методы Excel для ввода данных.
Вот основные советы, которые следует учитывать при использовании Excel:
- Планируйте свою таблицу.
- Не оставляйте пустые строки или столбцы при вводе связанных данных.
- Do сохранить часто и сохранить в двух местах.
- Не используйте числа в качестве заголовков столбцов и не включайте единицы с данными.
- Есть ссылки на ячейки и использование именованных диапазонов в формулах.
- Не оставляйте ячейки, содержащие формулы, открытыми.
- Есть ли сортировать данные.
Планируйте свою таблицу
При вводе данных в Excel рекомендуется планировать, прежде чем начать печатать. Решите, как будет использоваться рабочая таблица, какие данные она будет содержать и что будет сделано с этими данными. Эти решения существенно влияют на окончательный макет листа.
Планирование до ввода текста экономит время, если необходимо реорганизовать электронную таблицу, чтобы сделать ее более эффективной.
Вот несколько моментов, которые следует учитывать при планировании книг Excel:
- Какова цель таблицы?
- Сколько данных будет храниться в электронной таблице?
- Нужны ли графики?
- Будет ли электронная таблица напечатана?
Какова цель таблицы?
Назначение электронной таблицы определяет местоположение данных, поскольку наиболее важная информация должна быть видимой и легко доступной. Если ваши данные требуют вычислений, они определяют, какие формулы и функции необходимы и где следует вводить формулы.
Сколько данных будет хранить электронная таблица?
Объем данных, которые будут изначально храниться в электронной таблице, и то, сколько из них будет добавлено позднее, определяет количество рабочих листов, необходимых для рабочей книги.
Вот несколько советов:
- Не распространяйте свои данные слишком много. Когда данные содержатся в нескольких листах, может быть сложно найти конкретную информацию. Кроме того, вычисления, охватывающие несколько рабочих листов или рабочих книг, влияют на производительность Excel.
- Для больших таблиц отображайте значимые итоговые значения столбцов над заголовками столбцов, а не в нижней части таблицы, чтобы быстро находить сводную информацию.
Нужны ли графики?
Если все или часть данных будут использоваться в диаграмме или диаграммах, это может повлиять на структуру информации.
Следуйте этим рекомендациям при использовании диаграмм:
- В зависимости от типа диаграммы данные могут быть сгруппированы по-разному. Например, создайте итоговую область для круговых диаграмм.
- Для больших электронных таблиц переместите диаграммы на отдельные страницы, чтобы рабочие листы не были заполнены данными и изображениями.
Будет ли печататься таблица?
Как вы организуете данные, зависит от того, как данные будут напечатаны. Будут ли распечатаны все данные или только некоторые из них? Будут ли данные напечатаны в книжной или альбомной ориентации?
При печати рабочих листов помните следующие советы:
- Если рабочий лист напечатан в вертикальной ориентации на бумаге формата Letter (8,5 x 11 дюймов), поместите большую часть данных в строки под несколькими столбцами. Таким образом, заголовки видны на одном листе.
- Если рабочий лист напечатан на нескольких листах, напечатайте заголовки столбцов в верхней части каждой страницы. Заголовки столбцов обеспечивают обзор данных и делают данные более доступными и удобными для чтения.
- Если напечатано несколько столбцов данных, используйте альбомную компоновку, чтобы сохранить все заголовки на одной странице. Опять же, если требуется несколько листов, напечатайте заголовки столбцов в верхней части каждой страницы.
- Если рабочая таблица содержит диаграммы, перед печатью рабочей книги переместите диаграммы на отдельный лист.
Не оставляйте пустые строки или столбцы при вводе связанных данных
Пустые строки или столбцы в таблицах данных или связанных диапазонах данных затрудняют правильное использование некоторых функций Excel, таких как диаграммы, сводные таблицы и определенные функции.
Даже пустые ячейки в строке или столбце, содержащие данные, могут вызвать проблемы, как показано на рисунке ниже.
Отсутствие пустых мест помогает Excel обнаруживать и выбирать связанные данные при использовании ряда функций, таких как сортировка , фильтрация или автосуммирование.
Если вы хотите разбить данные, чтобы их было легче читать, используйте границы или форматируйте заголовки и метки. Используйте визуальные подсказки, такие как жирный текст, линии и цвет ячейки, чтобы отделить и выделить данные.
Следуйте этим советам при работе с данными в строках и столбцах:
- Поместите описательный заголовок вверху первого столбца таблицы с данными под ним.
- Если имеется более одного ряда данных, перечислите их один за другим в столбцах (слева направо) с заголовком для каждого ряда данных вверху.
Храните несвязанные данные отдельно
Хотя хранение связанных данных важно, также полезно разделять несвязанные диапазоны данных. Оставьте пустые столбцы или строки между различными диапазонами данных или другими данными на листе, чтобы Excel выбрал правильные связанные диапазоны или таблицы данных.
Сохраняйте часто
Важность сохранения вашей работы часто невозможно переоценить или заявить слишком часто. Если вы используете электронную таблицу, такую как Google Sheets или Excel Online, сохранение не является проблемой. Эти программы не имеют опции сохранения. Вместо этого электронные таблицы автоматически сохраняются в вашей облачной учетной записи с помощью функции автосохранения.
Для настольных электронных таблиц сохраните свою работу после двух или трех изменений. Например, сохранить после добавления данных, форматирования заголовков столбцов или ввода формулы. По крайней мере, сохраняйте свою работу каждые две или три минуты.
Несмотря на то, что стабильность компьютеров и компьютерного программного обеспечения со временем значительно улучшилась, программное обеспечение все еще дает сбой, происходят сбои питания, и другие люди иногда спотыкаются о шнур питания и вытаскивают его из розетки. А когда происходят несчастные случаи, потеря данных увеличивает вашу рабочую нагрузку, когда вы восстанавливаете то, что уже сделали.
В Excel есть функция автосохранения , которая обычно работает очень хорошо, но вы не должны полностью полагаться на нее. Привыкайте защищать свои данные с помощью частых сохранений.
Ярлык для сохранения
Вам не нужно перемещать мышь на ленту и нажимать на значки, чтобы сохранить свою работу. Вместо этого получите привычку сохранять с помощью сочетания клавиш . Если вы хотите сохранить таблицу, нажмите Ctrl + S .
Сохранить в двух местах
Также важно сохранить ваши данные в двух разных местах. Второе местоположение — резервная копия. Лучшая резервная копия — та, которая находится в другом физическом местоположении от оригинала.
Сохраняя резервную копию своих книг, рассмотрите возможность сохранения файлов в своей облачной учетной записи. Когда книги хранятся на вашем компьютере и в облаке, вы не только создаете резервную копию в облаке, но и в облаке создаются резервные резервные копии, что означает, что ваши данные хранятся на нескольких серверах.
Резервное копирование на основе облака
Опять же, создание резервной копии не должно занимать много времени.
Если безопасность не является проблемой, например, если рабочий лист представляет собой список ваших DVD, вероятно, достаточно отправить копию по электронной почте. Если безопасность является проблемой, облачное хранилище является вариантом.
В случае электронных таблиц владельцы программы выполняют резервное копирование своих серверов, включая все пользовательские данные. Но для безопасности загрузите копию файла на свой компьютер.
Не используйте числа в качестве заголовков столбцов и не включайте единицы с данными
Используйте заголовки вверху столбцов и в начале строк, чтобы идентифицировать ваши данные, заголовки упрощают такие операции, как сортировка. Но не используйте номера, такие как 2012, 2013 и т. Д., В качестве заголовков.
Как показано на рисунке выше, заголовки столбцов и строк, которые являются числами, могут быть непреднамеренно включены в вычисления. Если ваши формулы содержат функции, которые автоматически выбирают диапазон данных для аргумента функции , например, AutoSum , числа в заголовках столбцов вызовут проблему.
Как правило, такие функции, которые также включают в себя AutoMax и AutoAverage , сначала будут искать столбцы чисел, а затем искать строку чисел влево. Любые заголовки, которые являются числами, будут включены в выбранный диапазон.
Числа, которые используются в качестве заголовков строк, могут быть ошибочно приняты за другой ряд данных, если они выбраны как часть диапазона для диаграммы, а не как метки осей.
Отформатируйте числа в ячейках заголовка как текст или создайте текстовые метки, поставив перед каждым числом апостроф (‘), например, «2012» и «2013». Апостроф не отображается в ячейке, но меняет номер на текстовые данные.
Хранить единицы в заголовках
Не вводите валюту, температуру, расстояние или другие единицы в каждую ячейку с числовыми данными. Если вы это сделаете, есть вероятность, что Excel или Google Sheets будут просматривать все ваши данные в виде текста.
Вместо этого поместите единицы измерения в заголовки в верхней части столбца, чтобы эти заголовки были в текстовом формате и не создавали проблем.
Текст слева, цифры справа
Быстрый способ определить, есть ли у вас текстовые или числовые данные, — это проверить выравнивание данных в ячейке. По умолчанию текстовые данные выровнены по левому краю в Excel и Google Sheets, а числовые данные выровнены по правому краю в ячейке.
Хотя это выравнивание по умолчанию можно изменить, форматирование следует применять после ввода всех данных и формул. Выравнивание по умолчанию дает подсказку, правильно ли данные отформатированы на листе.
Символы процента и валюты
Рекомендуется вводить данные в лист и вводить обычное число, а затем форматировать ячейку, чтобы правильно отображать число, например, в процентах или в валюте.
Excel и Google Sheets распознают символы процента, которые вводятся в ячейку вместе с числом. Эти приложения также принимают символы общей валюты, такие как знак доллара ($) или символ британского фунта (£), если вы вводите их в ячейку вместе с числовыми данными, но другие символы валюты, такие как южноафриканский рэнд (R) , интерпретируется как текст.
Чтобы избежать возможных проблем, введите сумму, а затем отформатируйте ячейку для валюты, а не вводите символ валюты.
Используйте ссылки на ячейки и именованные диапазоны в формулах
И ссылки на ячейки, и именованные диапазоны могут и должны использоваться в формулах, чтобы в формулах и во всей рабочей таблице не было ошибок и обновлений.
Ссылки на ячейки представляют собой комбинацию буквы столбца и номера строки ячейки , например A1, B23 и W987. Ссылки на ячейки определяют местоположение данных на листе.
Именованный диапазон или определенное имя похоже на ссылку на ячейку в том смысле, что она используется для идентификации ячейки или диапазона ячеек на рабочем листе.
Ссылка на данные в формулах
Формулы используются в Excel для выполнения вычислений, таких как сложение или вычитание.
Если фактические числа в формулах, таких как:
= 5 + 3
Каждый раз, когда данные изменяются, вы должны редактировать формулу. Так что, если новые числа 7 и 6, формула становится:
= 7 + 6
В качестве альтернативы, если вы вводите данные в ячейки на рабочем листе, то используйте в ячейке ссылки на ячейки или имена диапазонов, а не числа.
Если вы введете число 5 в ячейку A1 и 3 в ячейку A2, формула будет выглядеть так:
= А1 + А2
Чтобы обновить данные, вы измените содержимое ячеек А1 и А2, но сохраните формулу как есть. Excel автоматически обновляет результаты формулы.
Этот метод полезен, когда рабочая таблица содержит сложные формулы и когда несколько формул ссылаются на одни и те же данные. Тогда вы будете изменять данные только в одном месте, что, в свою очередь, обновляет формулы, ссылающиеся на них.
Ссылки на ячейки и именованные диапазоны также делают рабочие листы более безопасными, защищая формулы от случайных изменений, оставляя доступными изменяющиеся ячейки данных.
Указать на данные
Другая особенность Excel и Google Sheets заключается в том, что ссылки на ячейки или имена диапазонов можно вводить в формулы, используя указание, которое включает нажатие на ячейку для ввода ссылки в формулу. Указание уменьшает вероятность ошибок, вызванных неправильным указанием ссылки на ячейку или неправильным написанием имени диапазона.
Используйте именованные диапазоны для выбора данных
Присвоение имени области связанных данных облегчает выбор данных, которые будут отсортированы или отфильтрованы.
Если размер области данных изменяется, отредактируйте именованный диапазон с помощью диспетчера имен .
Не оставляйте ячейки, содержащие формулы, открытыми
Потратив так много времени на то, чтобы исправить свои формулы и использовать правильные ссылки на ячейки, многие люди делают ошибку, оставляя эти формулы уязвимыми для случайных или непреднамеренных изменений.
Блокируйте ячейки, содержащие ваши формулы, если вы используете ссылки на ячейки или именованные диапазоны. Если необходимо, защитите ячейки паролем, чтобы сохранить их в безопасности.
В то же время оставьте ячейки, содержащие данные, открытыми, чтобы пользователи могли вносить изменения и поддерживать актуальность электронной таблицы.
Защита рабочего листа или рабочей книги состоит из двух этапов:
- Убедитесь, что правильные ячейки заблокированы .
- Защитите рабочий лист, добавив пароль.
Сортируй свои данные
Сортируйте свои данные после того, как вы закончили вводить их. Работа с небольшими объемами несортированных данных в Excel или Google Sheets обычно не является проблемой, но по мере увеличения электронной таблицы становится трудно работать с ней эффективно.
Сортированные данные легче понять и проанализировать. Некоторые функции и инструменты, такие как ВПР и субтотальная , требуют отсортированных данных для возврата точных результатов.
Кроме того, сортируйте данные различными способами, чтобы определить тенденции, которые на первый взгляд не очевидны.
Выберите данные для сортировки
Excel должен знать точный диапазон, который вам нужно отсортировать, и определяет области связанных данных, если есть:
- Нет пустых строк или столбцов в области связанных данных.
- Пустые строки и столбцы между областями связанных данных.
Excel также определяет наличие в области данных имен полей и исключает эту строку из сортировки.
Однако разрешение Excel выбирать диапазон для сортировки может быть рискованным, особенно с большими объемами данных, которые трудно проверить.
Используйте имена для выбора данных
Чтобы убедиться, что выбраны правильные данные, выделите диапазон перед началом сортировки. Если вы планируете многократно сортировать один и тот же диапазон данных, лучше всего дать ему имя.
При сортировке именованного диапазона данных введите имя в поле « Имя» или выберите его в соответствующем раскрывающемся списке. Excel автоматически выделяет правильный диапазон данных на листе.
Сортировка скрытых строк и столбцов
Excel не включает в себя скрытые строки и столбцы данных при сортировке, поэтому эти строки и столбцы должны быть скрыты до начала сортировки. Например, если строка 7 скрыта в диапазоне данных, которые будут отсортированы, она останется строкой 7 и не будет перемещена в правильное местоположение в отсортированных данных.
То же самое касается столбцов данных. Сортировка по строкам включает в себя изменение порядка столбцов данных, но если столбец B скрыт перед сортировкой, он останется как столбец B после сортировки.
Хранить номера как номера
Убедитесь, что все числа отформатированы как числа. Если результаты не соответствуют ожидаемым, столбец может содержать числа, хранящиеся в виде текста, а не чисел. Например, отрицательные числа, импортированные из некоторых учетных систем, или числа, введенные с начальным апострофом ( ‘ ), сохраняются в виде текста.
Когда данные быстро сортируются с помощью кнопки AZ или ZA , все может пойти не так. Если в данных есть пустая строка или пустые столбцы, некоторые данные будут отсортированы, а некоторые — нет. Самый простой способ убедиться, что вы выбрали правильный диапазон данных перед сортировкой, — дать ему имя.
Если выделена одна ячейка, Excel расширяет выделение до диапазона (очень похожего на нажатие Ctrl + Shift + 8 ), ограниченного одним или несколькими пустыми столбцами и строками. Затем он проверяет первую строку в выбранном диапазоне, чтобы определить, содержит ли она информацию заголовка или нет.
Заголовки столбцов должны соответствовать строгим правилам, прежде чем Excel распознает их как заголовок. Например, если в строке заголовка есть пустые ячейки, Excel может подумать, что это не заголовок. Аналогично, если строка заголовка отформатирована так же, как и другие строки в диапазоне данных, Excel может не распознать заголовок. Кроме того, если таблица данных состоит из текста, а строка заголовка содержит только текст, Excel не распознает строку заголовка; строка заголовка выглядит как другая строка данных в Excel.
Excel сортирует только после выбора диапазона, и Excel определяет, есть ли строка заголовка. Результаты сортировки зависят от того, правильно ли Excel выбрал выбор диапазона и определение строки заголовка. Например, если Excel не думает, что есть строка заголовка, тогда заголовок сортируется в теле данных.
Чтобы убедиться, что ваш диапазон данных распознан правильно, используйте сочетание клавиш Ctrl + Shift + 8, чтобы увидеть, что выбирает Excel. Если это не соответствует вашим ожиданиям, то либо измените символ данных в вашей таблице, либо выберите диапазон данных перед использованием диалогового окна Сортировка .
Чтобы обеспечить правильное распознавание заголовков, используйте сочетание клавиш Ctrl + Shift + 8, чтобы выбрать диапазон данных, а затем посмотрите на первую строку. Если в заголовке есть пустые ячейки среди выбранных в первой строке, первая строка отформатирована так же, как и вторая строка, или вы выбрали более одной строки заголовка, тогда в Excel предполагается, что строки заголовка нет. Чтобы исправить это, внесите изменения в строку заголовка, чтобы Excel распознал их.
Если в вашей таблице данных используются многострочные заголовки, Excel может быть трудно их распознать. Проблема усугубляется, когда вы ожидаете, что Excel будет включать пустые строки в этот заголовок; он просто не может сделать это автоматически. Однако вы можете выбрать строки для сортировки перед выполнением сортировки. Другими словами, будьте конкретны в том, что вы хотите отсортировать в Excel; не позволяйте Excel делать предположения для вас.
Храните даты и время как текст
Если результаты сортировки по дате не получаются ожидаемыми, данные в столбце, содержащем ключ сортировки, могут содержать даты или время, хранящиеся в виде текстовых данных, а не чисел (даты и время являются отформатированными числовыми данными).
На изображении выше запись A. Peterson оказалась внизу списка, когда, исходя из даты заимствования 5 ноября 2014 года, она должна быть выше записи для A. Wilson, которая также имеет дату заимствования 5 ноября. Причиной неожиданных результатов является то, что дата заимствования для А. Петерсона была сохранена как текст, а не как число.
Смешанные данные и быстрые сортировки
При использовании метода быстрой сортировки с записями, содержащими текстовые и числовые данные, Excel сортирует номера и текстовые данные по отдельности, помещая записи с текстовыми данными в нижней части отсортированного списка.
Excel также может включать заголовки столбцов в результаты сортировки, интерпретируя их как просто еще одну строку текстовых данных, а не как имена полей для таблицы данных.
Сортировать Предупреждения
При использовании диалогового окна «Сортировка» даже для сортировок в одном столбце Excel может отображать предупреждение о том, что он обнаружил данные, сохраненные в виде текста, и предоставляет следующие параметры:
- Сортируйте все, что выглядит как число, как число.
- Сортировка чисел и номеров хранится в виде текста отдельно.
Первый вариант размещает текстовые данные в правильном месте результатов сортировки. Второй вариант размещает записи, содержащие текстовые данные, в нижней части результатов сортировки, так же как и при быстрой сортировке.