Комплексная практическая работа по теме создание таблиц в excel: Комплексная практическая работа по Excel

В Excel 2007 и более поздних версиях можно использовать команду «Таблица». для преобразования списка данных в отформатированную таблицу Excel. Столы имеют множество функций, таких как сортировка и фильтрация, которые помогут вам организовать и просмотрите свои данные.

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

Преимущества таблицы Excel

Список в виде именованной таблицы Excel просто отформатировать, и есть много преимуществ и всего несколько недостатков

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

Преимущества

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

Недостатки

Однако использование именованных таблиц Excel имеет несколько недостатков, поэтому могут возникнуть ситуации, когда вы предпочитаете их не использовать. Например:

• Структурированные ссылки на ячейки таблицы не имеют «абсолютной» настройки, поэтому немного сложнее скопировать их по столбцу
.
• Таблицы не будут автоматически расширяться на защищенных листах, даже если ячейки под таблицей разблокированы
• Вы не можете группировать и копировать или перемещать несколько листов, если какой-либо лист содержит таблицу Excel
• Пользовательские представления не допускаются в книге, содержащей 1 или несколько таблиц Excel

Подготовка данных

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

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

Создайте таблицу Excel

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

1. Выберите ячейку в подготовленном списке данных.
2. На ленте щелкните вкладку «Вставка».
3. В группе «Таблицы» выберите команду «Таблица».
4. В диалоговом окне «Создать таблицу» диапазон ваших данных должен автоматически появляются, и опция Моя таблица имеет заголовки проверено. При необходимости вы можете настроить диапазон и флажок.
5. Нажмите OK, чтобы принять эти настройки.

Сортировка и фильтрация данных

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

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

• ПРИМЕЧАНИЕ . В Excel 2013 и более поздних версиях вы также можете использовать срезы таблиц Excel для быстрой фильтрации данных таблицы.

Отключить фильтры таблиц

В некоторых таблицах Excel может потребоваться отключить кнопки автоматического фильтра в строке заголовка таблицы.

Вот несколько причин, по которым вам может понадобиться скрыть фильтры:

• Кнопки фильтра закрывают часть текста в заголовках столбцов
• Не рекомендуется фильтровать таблицу и скрывать некоторые строки данных

Чтобы отключить фильтры:

1. Выберите ячейку в таблице Excel
2. На вкладке «Данные ленты» нажмите команду «Фильтр», чтобы отключить фильтр.

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

Переименование таблицы Excel

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

Чтобы изменить имя таблицы:

1. Выберите любую ячейку в таблице
2. На ленте на вкладке Работа с таблицами щелкните вкладку Дизайн.

3. В крайнем левом углу ленты щелкните поле Имя таблицы, чтобы выберите существующее имя

4. Затем введите новое имя, например Заказы, и нажмите клавишу Enter.

Изменение стиля таблицы Excel

После создания таблицы Excel ее внешний вид можно быстро изменить в любое время.

В Excel имеется набор встроенных стилей таблиц различных цветов с настройками «Светлый», «Средний» и «Темный». Вы также можете создать собственный стиль таблицы, если вам нужны специальные цвета или форматирование.

Чтобы выбрать другой стиль для существующей таблицы Excel, выполните следующие действия:

• Выберите любую ячейку в таблице Excel
• На ленте щелкните вкладку «Конструктор таблиц».
  • Или на вкладке Работа с таблицами щелкните вкладку Дизайн
• В группе «Стили таблиц» нажмите кнопку «Дополнительно» , чтобы открыть палитру «Стили таблиц»

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

• В палитре «Стили таблиц» укажите любой из стилей таблиц
• Таблица на рабочем листе изменится автоматически, чтобы показать предварительный просмотр выделенного стиля.
  • Необязательно. Укажите на другие стили, чтобы найти тот, который вам нравится
• Когда вы выберете стиль таблицы, щелкните этот стиль в палитре
• Палитра «Стили таблиц» закроется, и выбранный стиль будет применен к таблице Excel на рабочем листе.

Совет : Вы можете повторить эти шаги в любое время, чтобы выбрать новый стиль таблицы для существующей таблицы.

Создать таблицу Excel с Особый стиль

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

Посмотрите это видео, чтобы увидеть шаги и написанное Инструкции приведены под видео

Создание таблицы Excel с определенным стилем

При создании таблицы с помощью команды «Таблица» на ленте «Вставка» вкладка, таблица сохраняет любое текущее форматирование, а применяется стиль таблицы по умолчанию.

Если вы хотите применить определенный стиль таблицы при создании таблицы Excel Таблица, выполните следующие действия:

1. Выберите ячейку в подготовленном вами списке данных.
2. На ленте щелкните вкладку Главная.
3. В группе Стили щелкните Форматировать как таблицу
4. Нажмите на стиль, который вы хотите использовать

   ИЛИ, чтобы применить стиль и удалить существующее форматирование, щелкните правой кнопкой мыши в стиле и нажмите «Применить и очистить форматирование»

5. В диалоговом окне «Создать таблицу» диапазон ваших данных должен автоматически появляются, и Моя таблица имеет заголовки вариант проверено. При необходимости вы можете настроить диапазон и флажок.
6. Нажмите OK, чтобы принять эти настройки.

Создана отформатированная таблица Excel с выбранным стилем таблицы.

Создать пользовательский стиль таблицы

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

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

Чтобы создать собственный стиль таблицы на основе встроенного стиля, выполните следующие действия.

• Выберите ячейку в таблице Excel
• Затем на ленте Excel щелкните вкладку «Дизайн».
• В группе «Стили таблиц» щелкните значок Дополнительно кнопка для открытия палитры стилей таблиц
• В галерее стилей таблиц щелкните правой кнопкой мыши стиль, который хотите продублировать.
• Во всплывающем меню нажмите Дублировать
• Открывается диалоговое окно «Изменить таблицу», в котором отображаются текущие настройки стиля таблицы.

Изменить пользовательский стиль таблицы

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

• В верхней части диалогового окна «Изменить таблицу» введите имя для нового стиля таблицы.
  • Если вы создали новый стиль, диалоговое окно будет называться Новый экспресс-стиль сводной таблицы.)
• Затем прокрутите список элементов таблицы, чтобы найти элемент, который вы хотите изменить.
  • Совет: к элементам, выделенным полужирным шрифтом, применено форматирование
• Чтобы изменить форматирование элемента, щелкните его, чтобы выбрать.
  • Вы можете выбрать любой элемент в списке, независимо от того, выделен он жирным шрифтом или нет
• Далее нажмите кнопку Формат
• В диалоговом окне «Формат ячеек» выберите нужные параметры «Шрифт», «Граница» и «Заливка» для выбранного элемента.
• Затем нажмите «ОК», чтобы вернуться в диалоговое окно «Изменить таблицу».
• В правой части диалогового окна вы можете увидеть измененное форматирование в разделе предварительного просмотра
• Необязательно — повторите шаги форматирования для других элементов, если вы хотите их изменить
• Необязательно: установите флажок «Установить стиль таблицы по умолчанию для этого документа».
• Нажмите OK, чтобы закрыть диалоговое окно

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

Применить пользовательский стиль таблицы

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

• Выберите ячейку в таблице Excel, которую вы хотите отформатировать.
• Откройте палитру стилей таблиц.
• Созданный вами пользовательский стиль таблицы добавляется в раздел «Пользовательский» в верхней части палитры «Стили таблиц».
• Щелкните свой собственный стиль, чтобы применить его к активной таблице Excel

На этом снимке экрана новый пользовательский стиль MY GREEN TABLE применяется к таблице Excel.

Показать итоги в таблице

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

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

Строка Итого будет добавлена ​​внизу таблицы, а одна или несколько столбцов чисел могут отображать итог.

Вычисления строки итогов

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

• Если все записи состоят из чисел, первый аргумент равен 109 — СУММ.
• Если есть нечисловые записи, первый аргумент равен 103 – COUNTA

Итоги для отфильтрованных данных

Excel использует функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ в итоговой строке, потому что эта функция вычисляет итог только для элементов, которые видны после применения фильтра.

При желании вы можете заменить автоматическую формулу в строке итогов и добавить формулы итогов в другие столбцы.

Например, добавьте формулу АГРЕГАТ в строку Итого — у нее больше функций и опций, чем ПРОМЕЖУТОЧНЫЙ ИТОГ.

Изменить и добавить итоги

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

1. Щелкните в ячейке Итого для одного из столбцов.
2. Из выпадающего списка выберите функцию, которую вы хотите использовать в текущем столбце.

А ПРОМЕЖУТОЧНЫЙ ИТОГ формула добавляется в ячейку, и она показывает расчет на основе на видимых ячейках в столбце таблицы.

Отключение строки итогов

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

• Выберите ячейку в таблице Excel
• На ленте Excel в разделе Работа с таблицами щелкните вкладку Дизайн
• В группе «Параметры стиля таблицы» установите или снимите флажок «Строка итогов»

Добавить данные в таблицу со строкой итогов

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

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

Добавить одну новую строку

Чтобы добавить одна строка новых данных:

• Выберите последнюю ячейку в последней строке данных
• Нажмите клавишу Tab, чтобы начать новую строку

Добавить несколько строк новых данных

В вставьте новые данные в конец таблицы

• Скопируйте данные, которые вы хотите добавить ( Ctrl+C )
• Выберите первую ячейку в строке Заголовок, даже если там есть текст или формула
• Затем нажмите Enter или Ctrl+V , чтобы вставить скопированные данные

Ссылка на столбец таблицы в формуле

Когда формула ссылается на часть именованной таблицы Excel, структурированная ссылка создано. Структурированная ссылка покажет имя столбца таблицы, и может включать имя таблицы. Например, это ссылка на Столбец Product в таблице с именем OrderRef — OrdersRef[Product]

Формула вне таблицы

В этом примере формула будет создана вне таблицы. формула будет использовать СЧИТАТЬПУСТОТЫ Функция для подсчета пустых ячеек в столбце таблицы. Таблица с именем OrderRef, а столбец с именем Product.

1. Чтобы запустить формулу, выберите пустую ячейку и введите: =СЧИТАТЬПУСТОТЫ(
2. Затем щелкните в самом верху ячейки заголовка для столбца, который вы хотите проверить — указатель изменится на стрелку вниз.

• Не нажимайте кнопку столбца там, где стоит буква столбца.

• И не нажимайте в середине ячейки заголовка

=СЧИТАТЬПУСТОТЫ(OrdersRef[Product]

Затем введите закрывающую скобку и нажмите Enter, чтобы завершить формулу.

Проблема: копирование формул со ссылками на таблицы

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

Это происходит потому, что ссылки на таблицы НЕ заблокированы, поэтому ссылки на столбцы настраиваются автоматически при перетаскивании вправо.

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

Временная шкала видео

• 00:00 Введение
• 00:46 ПРОМЕЖУТОЧНЫЙ ИТОГ Формула для проверки итогов
• 01:19 СУММЕСЛИМН Формула со ссылками на таблицы
• 01:42 Скопируйте формулу на одну строку вниз
• 02:13 Копировать через
• 03:09 Копировать без проблем
• 03:45 Получить дополнительную информацию

Исправление: копирование формулы со ссылками на таблицы

Во избежание проблем при копировании формул со ссылками на таблицы НЕ ПЕРЕТЯГИВАЙТЕ маркер заполнения для копирования.

Вместо этого используйте один из следующих методов для копирования формул:

• Заполните вправо с помощью сочетания клавиш: Ctrl + R
• Скопируйте формулу (Ctrl + C) и вставьте ее в другие ячейки (Ctrl + V)

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

Таблица не расширяется для новых данных

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

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

Настройки автоформата

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

Очистить строки под таблицей

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

Чтобы устранить проблему, попробуйте удалить или очистить строки под таблицей Excel

Вот комментарий Талли в моем блоге Contextures, объясняющий этот тип проблемы и то, как он ее исправил:

• У меня под столом скрывались невидимые данные. (Ячейки с одним пробелом в них, которые были вставлены во время какого-то специального тестирования давным-давно.)
• Я предполагаю, что Excel видит заполненные ячейки под таблицей и не добавляет их в новую строку списка в таблице.
• Удалил эти значения, и он снова заработал как положено

Скрытые строки под таблицей

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

Чтобы отобразить все строки:

• Нажмите кнопку «Выбрать все» в левом верхнем углу листа Excel
• Щелкните правой кнопкой мыши любую из кнопок строки
• Нажмите команду «Показать»

Затем, при необходимости, очистите скрытые строки, чтобы удалить все данные. Или переместите данные в другое место в книге.

Видео: изменение настроек автоформата таблицы Excel

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

ПРИМЕЧАНИЕ : изменение этих параметров повлияет на все книги которые вы открываете в Excel на своем компьютере.

Включить настройки автоформата таблицы

Если таблицы Excel не расширяются автоматически на вашем компьютере, проверьте следующие параметры в окне параметров Excel.

ПРИМЕЧАНИЕ : Изменение этих параметров повлияет на все рабочие книги , которые вы открываете в Excel на своем компьютере.

1. В левом конце ленты щелкните вкладку «Файл», затем щелкните Опции
2. В окне параметров Excel слева щелкните Проверка правописания.

3. В разделе Параметры автозамены щелкните Параметры автозамены

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

6. Дважды нажмите OK, чтобы вернуться в Excel

Теперь, когда вы добавляете новые данные или создаете вычисляемый столбец, Excel таблица должна настроиться автоматически.

Включение или отключение этих параметров с помощью VBA

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

Код макроса показан на этой странице, и вы также можете загрузить образец книги Excel, содержащий код макроса.

Проверка данных отсутствует в новых строках

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

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

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

Исправление проверки данных

Чтобы устранить проблему, выполните следующие действия:

• В таблице Excel выберите одну из ячеек с раскрывающимся списком
• Нажмите Ctrl + C, чтобы скопировать ячейку
• Выберите все ячейки данных в столбце, в который вы скопировали ячейку (не включая ячейку заголовка)
• Щелкните правой кнопкой мыши одну из выделенных ячеек и выберите Специальная вставка
• В диалоговом окне «Специальная вставка» в разделе «Вставка» щелкните Проверка.
• Нажмите OK, чтобы применить параметры проверки данных ко всем выбранным ячейкам

Добавить поле счетчика

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

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

1. На листе OrdersTable добавьте новый заголовок в первый пустой столбец — Продажи
2. В ячейке под заголовком введите формулу: =1
3. Нажмите Enter, чтобы завершить формулу

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

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

Печать только таблицы Excel

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

Команды нет на ленте, поэтому вы можете добавить ее туда или поставить команду на панели быстрого доступа. Посмотрите это короткое видео, чтобы см. шаги.

Табличные макросы Excel

Дополнительные макросы таблиц Excel см. на странице макросов таблиц Excel.

На этой странице вы найдете макросы для автоматизации следующей задачи:

• Список всех таблиц в книге
• Получить имя листа для конкретной таблицы Excel
• Изменить настройки автоформата таблицы

Получить файл примера

1. Базовый : Чтобы загрузить файл примера с таблицами для этого руководства, щелкните здесь: Файл примера таблицы. Заархивированный файл имеет формат xlsx и НЕ содержит макросов.

Связанные руководства по Excel

Макросы таблиц Excel

Слайсеры таблиц Excel

Трекер задач проекта Excel

Создание сводной таблицы

Макрос — копирование строк таблицы Excel

Создание и обслуживание электронных таблиц // Передовой опыт работы с Excel // PerfectXL

Введение в создание и обслуживание качественных электронных таблиц

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

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

21 Рекомендации и советы для экспертов по Excel

1. Подготовка хорошей электронной таблицы

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

2. Создавайте рабочие листы с расчетом на будущее

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

3. Подумайте о порядке рабочих листов

Разместите разные типы данных на разных рабочих листах. Например, используйте первые несколько рабочих листов для ввода информации, следующие рабочие листы для расчетов и последний лист в качестве рабочего листа презентации для графиков и результатов. Ограничьте количество таблиц на листе до одной. Несколько таблиц на листе вызывают проблемы при попытке сортировки, вставки или форматирования ячеек.

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

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

4. Выбирайте ясность, а не внешний вид

Если ваш рабочий лист ориентирован на пользователя, используйте привлекательный «Рабочий лист результатов». Если нет, то не надо! Большинство рабочих листов работают лучше всего, когда они предназначены для обеспечения ясности всех текущих расчетов. Вы можете попробовать разделить длинные формулы, но не скрывайте их, чтобы сделать их более понятными для пользователей.

5. Поддерживайте согласованность временной шкалы

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

6. Организуйте информационный поток

Постарайтесь организовать рабочие листы таким образом, чтобы информация всегда текла сверху слева вниз справа. Это значительно облегчает пользователю понимание того, как работает электронная таблица. Исключениями из правила являются «Вводная таблица» и/или «Рабочая таблица результатов». Размещение их в начале повышает ясность для пользователей, что особенно полезно для электронной таблицы, ориентированной на пользователя. При управлении потоком информации избегайте перекрестных зависимостей, поскольку они сильно снижают понятность. Всегда избегайте циклических ссылок, если необходима ссылка на какие-либо предыдущие данные.

7. Пометьте столбцы и строки

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

8. Сохраняйте формулы читаемыми

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

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

9. Избегайте повторяющихся формул

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

10. Избегайте фиксированных чисел в формулах

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

11. Не объединяйте ячейки

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

12. Избегайте сокрытия данных

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

Скрытая информация увеличивает риск ошибок.

13. Встроенная проверка данных

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

14. Сохранить стиль на конец

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

15. Сохраняйте единый стиль

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

16. Сделайте условное форматирование простым

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

17. Используйте положительные числа

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

18. Четко указывайте единицы измерения и числовые форматы

Убедитесь, что зрители всегда могут отследить единицы измерения и числовые форматы, используемые в электронной таблице. Вы можете сделать это, либо задав единицы измерения в свойствах ячейки в Excel, либо указав их в метке столбца. Но будьте осторожны: никогда не вводите символ валюты (€, $, £ и т. д.) сразу после значения! Это вызывает проблемы при использовании значения в формулах.

19. Уточните свои источники

Когда сложные вычисления в рабочем листе A выполняются с использованием ячеек из рабочего листа B, покажите зрителю эти ячейки из рабочего листа B в рабочем листе A в вашей электронной таблице. Пользователи, которые пытаются понять, как работает вычисление, могут оставаться на одном рабочем листе, вместо того чтобы переключаться на другой. Сделайте ваши ссылки четкими и понятными. Без надлежащего форматирования ссылки могут привести к тому, что рабочий лист будет выглядеть загроможденным, особенно если их много.

20. Избегайте использования макросов

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

21. Используйте простые диапазоны

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

Подробнее о бизнес-курсе

Бизнес-курс Excel для компаний

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

Узнать больше

Индивидуальный бизнес-курс Excel

Пройдите наш 4-недельный курс по основам моделирования в Excel. Учитесь у лучших пользователей Excel в мире и сразу же наблюдайте, как ваше использование Excel улучшается!

Подробнее

Программа бизнес-курса Excel

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

Узнать больше

Преподаватели бизнес-курса Excel

Этот курс был разработан компанией PerfectXL (Infotron) в сотрудничестве с 11 признанными Microsoft «Самыми ценными профессионалами» и 3 самыми престижными университетами Нидерландов.

Узнать больше

Принципы правильного использования Excel

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

Подробнее

Создание и обслуживание качественных электронных таблиц

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

Подробнее

Зачем использовать Excel?

Сегодня люди начинают задавать этот вопрос все чаще. В 1990-е годы, на пике популярности Excel, это даже не вопрос, но времена меняются. Теперь рынок заполнен альтернативами…

Узнать больше

Никогда больше не бороться с этими ошибками VBA

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