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

Обновлено: 21.11.2024

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

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

Нахождение инструментов, которые вам понадобятся

Элементы управления формой находятся на вкладке "Разработчик" на ленте, которая по умолчанию не отображается и должна быть включена. Ключ к использованию этих функций лежит в элементах управления формами Excel, которые доступны только на вкладке «Разработчик», и эта вкладка по умолчанию не отображается в Excel. Чтобы отобразить вкладку «Разработчик», выберите Файл > Параметры > Настроить ленту. Теперь посмотрите на правую панель, установите флажок Разработчик и нажмите ОК.

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

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

Начнем с элемента управления формой кнопки прокрутки

Кнопка прокрутки, как и другие элементы управления, требует, чтобы вы настроили ее параметры, в частности ячейку, в которую она помещает свой вывод, прежде чем вы сможете ее использовать. Одним из самых простых для понимания элементов управления является кнопка прокрутки, которая позволяет пользователю электронной таблицы увеличивать или уменьшать значение в ячейке, щелкая по ней. Чтобы увидеть, как это работает, нажмите Разработчик > Вставить > Кнопка прокрутки, а затем перетащите (удерживая левую кнопку мыши нажатой), чтобы создать кнопку прокрутки на листе. Кнопка прокрутки имеет большие стрелки вверху и внизу поля.

Теперь щелкните правой кнопкой мыши кнопку прокрутки, выберите Управление форматом и перейдите на вкладку Управление, чтобы настроить элемент управления. «Текущее значение» — это начальное значение для элемента управления. «Минимальное значение» и «Максимальное значение» — это, соответственно, наименьшее и наибольшее значения, которые может обеспечить элемент управления (от 0 до 30 000). «Инкрементное изменение» относится к величине, на которую значение изменяется каждый раз, когда пользователь электронной таблицы щелкает стрелку. «Ссылка на ячейку» — это ячейка, в которую кнопка счетчика помещает свое значение.

Введите следующие значения:

Нажмите ОК, а затем нажмите за пределами кнопки прокрутки, чтобы отменить выбор.

Чтобы протестировать кнопку прокрутки, используйте ее стрелки: нажмите стрелку вверх, чтобы увеличить значение в ячейке A1 на 25, и нажмите стрелку вниз, чтобы уменьшить значение на 25. Обратите внимание, что значение никогда не может превышать 400, что это максимальное значение, которое вы установили для него. Точно так же он не может уменьшиться ниже своего минимального значения 0.

Изучение дополнительных функций полос прокрутки

Возьмите значение, предоставленное элементом управления формы, и примените к нему формулу, чтобы получить другой диапазон значений. Полоса прокрутки работает аналогично кнопке прокрутки. Нажмите Разработчик > Вставить > Полоса прокрутки, чтобы выбрать этот элемент управления. Теперь перетащите лист вертикально или горизонтально, чтобы создать вертикальную или горизонтальную полосу прокрутки. Оба работают одинаково; просто у них разная ориентация. Теперь щелкните правой кнопкой мыши полосу прокрутки, выберите Управление форматом и перейдите на вкладку Управление, чтобы задать параметры.

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

И полоса прокрутки, и кнопка прокрутки могут выдавать значение только от 0 до 30 000. Это может показаться серьезным ограничением, но вы можете использовать формулу для получения гораздо большего диапазона значений. Например, создайте полосу прокрутки, щелкните ее правой кнопкой мыши и выберите Управление форматом > Управление.

Введите следующие значения для его настроек:

Нажмите ОК, а затем нажмите за пределами полосы прокрутки, чтобы отменить выбор.

Далее щелкните ячейку B3 и введите следующую формулу:

=A3-50

Теперь, когда пользователь щелкает полосу прокрутки, значение в ячейке A3 всегда будет находиться в диапазоне от 0 до 100. Однако ячейка рядом с ней — ячейка B3 — будет отображать значение от -50 до +50 из-за формула, которую он содержит. Если вы скроете значение в ячейке A3 (например, настроив отображение белого текста на белом фоне), пользователь увидит только полосу прокрутки, изменяющую значение в ячейке B3.

Следующая страница: использование и группировка переключателей

Использование и группировка кнопок выбора

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

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

Щелкните правой кнопкой мыши один из переключателей и выберите Формат элемента управления. Вы можете настроить кнопку выбора, чтобы она была отмечена или не отмечена. Поскольку одновременно можно отметить только одну кнопку, установка любой из кнопок как отмеченной автоматически сделает все остальные неотмеченными. Кроме того, любая ячейка, которую вы установите в качестве «Ссылки на ячейку» для одного переключателя в поле группы, будет одинаковой для всех переключателей. Чтобы изменить метку переключателя, щелкните его правой кнопкой мыши и выберите Редактировать текст.

Кнопки опций возвращают значение в ячейку "Ссылка на ячейку" в соответствии с порядком, в котором вы их создали, поэтому первая из них вернет значение 1 при нажатии, вторая вернет значение 2, третья значение 3 и так далее. Вы можете использовать значения, которые вы получаете от кнопок выбора, для выполнения действия. Например, вы можете использовать кнопки выбора с текстом «Ночью», «2 дня» и «Наземный» в счете-фактуре, чтобы пользователь мог выбрать способ доставки доставки. Затем вы можете написать функцию ЕСЛИ для выполнения вычислений на основе выбора пользователя.

Итак, если вы добавили переключатели в следующем порядке: "На ночь", "2 дня" и "Наземный" и сделали их ячейку "Ссылка на сотовую связь" A1, вы могли бы ввести следующую формулу в любую ячейку. чтобы вернуть стоимость доставки в размере 45 долларов США, если выбран вариант "Ночная доставка", 35 долларов США за 2 дня и 20 долларов США за доставку наземным транспортом:

=ЕСЛИ(A1=1,45;ЕСЛИ(A1=2,35,20))

Использование флажка для выбора элемента

Вы можете проверить значение, возвращаемое флажком, с помощью функции ЕСЛИ для выполнения вычислений в зависимости от его состояния. Например, счет-фактура может содержать флажок с надписью «Включить государственный налог». Если вы свяжете флажок с ячейкой A7 и введете формулу = ЕСЛИ (A7, Итого по счету * 0,085,0) в любую ячейку на листе, эта ячейка будет содержать значение из ячейки с именем Итого по счету, умноженное на 8,5 процента (чтобы рассчитать налог штата с продаж), если пользователь электронной таблицы установит флажок, и будет содержать значение 0, если пользователь не установит флажок.

Вы на пути к тому, чтобы стать опытным пользователем Excel

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

Хотели бы вы увидеть еще больше советов для опытных пользователей Excel? Жмите комментарии и дайте нам знать!

поиск меню

Урок 9. Создание сложных формул

Создание сложных формул

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

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

Сложные формулы

Простые формулы содержат одну математическую операцию, например 5+5. Сложные формулы имеют более одной математической операции, например 5+5-2. Когда в формуле более одной операции, порядок операций говорит нам, какую операцию вычислять первой. Чтобы использовать Excel для расчета сложных формул, вам необходимо понимать порядок операций.

Необязательно: вы можете загрузить этот пример для дополнительной практики.

Порядок операций

Excel вычисляет формулы на основе следующего порядка операций:

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

Мнемоника, которая поможет вам запомнить порядок: «Прошу прощения, моя дорогая тетя Салли».

Пример 1

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

Пример порядка действий

Пример 2

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

Пример порядка действий в Excel

Сначала Excel посчитает количество проданных семян в скобках: (19*1,99)=37,81 семян белой лилии и (33*1,99)=65,67 всего семян лилии.
Во-вторых, количество семян White Pete Lily будет разделено на общее количество семян Pete Lily: 37,81/65,67 = 0,5758.
Наконец, он умножит результат на 100, чтобы получить значение в процентах: 0,5758*100=57,58.

Исходя из этой сложной формулы, результат покажет, что 57,58 % всех проданных семян Pete Lily были белыми. Из этого примера видно, что важно вводить сложные формулы с правильным порядком операций. В противном случае Excel не сможет точно вычислить результаты.

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

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

Нажмите на ячейку, в которой должен появиться результат формулы (например, F11).
Введите знак равенства (=).
Введите открывающую скобку, а затем нажмите ячейку, содержащую первое значение в формуле (например, F4).
Введите первый математический оператор (например, знак сложения).
Нажмите ячейку, содержащую второе значение в формуле (например, F5), затем введите закрывающую скобку.
Введите следующий математический оператор (например, знак умножения).
Введите следующее значение в формулу (например, 0,055 для налога 5,5%).

Ввод формулы

Результат F11

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

Работа со ссылками на ячейки

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

Excel интерпретирует ссылки на ячейки как относительные или абсолютные. По умолчанию ссылки на ячейки являются относительными ссылками. При копировании или заполнении они изменяются в зависимости от относительного положения строк и столбцов. Если вы скопируете формулу (=A1+B1) в строку 2, формула изменится на (=A2+B2).

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

Относительные ссылки

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

В следующем примере мы создаем формулу со ссылками на ячейки в строке 4 для расчета общей стоимости счетов за электричество и воду за каждый месяц (B4=B2+B3). В ближайшие месяцы мы хотим использовать ту же формулу с относительными ссылками (C2+C3, D2+D3, E2+E3 и т. д.). Для удобства мы можем скопировать формулу из B4 в остальную часть строки 4, и Excel рассчитает стоимость счетов за эти месяцы, используя относительные ссылки.

Чтобы создать и скопировать формулу с использованием относительных ссылок:

Выберите первую ячейку, в которую вы хотите ввести формулу (например, B4).

Выбор ячейки B4

Ввод формулы в ячейку B4

Результат в B4

Значения рассчитаны в C4:M4

Абсолютные ссылки

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

Абсолютная ссылка обозначается в формуле добавлением знака доллара ($) перед столбцом и строкой. Если он предшествует столбцу или строке (но не обоим), он называется смешанной ссылкой.

В приведенном ниже примере мы хотим рассчитать налог с продаж для списка товаров с разными ценами. Мы будем использовать абсолютную ссылку для налога с продаж ($B$1), потому что мы не хотим, чтобы она изменялась, поскольку мы копируем формулу вниз по столбцу с различными ценами.

Чтобы создать и скопировать формулу с помощью абсолютной ссылки:

Выберите первую ячейку, в которую нужно ввести формулу (например, C4).

Выбор ячейки C4

Ввод формулы

Результат в C4

Значения рассчитаны в C5:C8

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

поиск меню

Урок 14. Создание более сложных формул

Введение

Возможно, у вас есть опыт работы с формулами, содержащими только один оператор, например 7+9. Более сложные формулы могут содержать несколько математических операторов, например 5+2*8. Если в формуле более одной операции, порядок операций сообщает Excel, какую операцию следует вычислить первой. Чтобы написать формулы, которые дадут вам правильный ответ, вам нужно понять порядок операций.

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

Порядок операций

Excel вычисляет формулы на основе следующего порядка операций:

Операции, заключенные в круглые скобки
Экспоненциальные вычисления (например, 3^2)
Умножение и деление, в зависимости от того, что наступит раньше
Сложение и вычитание, в зависимости от того, что наступит раньше

Мнемоника, которая поможет вам запомнить заказ, – это PEMDAS, или "Пожалуйста, извините мою дорогую тетю Салли".

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

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

Сначала мы начнем с вычисления всего, что заключено в круглые скобки. В этом случае нам нужно вычислить только одно: 6-3=3.

Как видите, формула уже выглядит проще. Далее мы посмотрим, есть ли экспоненты. Есть один: 2^2=4.

Далее мы решим любое умножение и деление, работая слева направо. Поскольку операция деления предшествует умножению, она вычисляется первой: 3/4=0,75.

Теперь решим оставшуюся операцию умножения: 0,75*4=3.

Далее мы вычислим любое сложение или вычитание, снова работая слева направо. Сначала идет сложение: 10+3=13.

Наконец у нас осталась одна операция вычитания: 13-1=12.

Теперь у нас есть ответ: 12. И это точно такой же результат, который вы получите, если введете формулу в Excel.

Создание сложных формул

В приведенном ниже примере мы покажем, как Excel использует порядок операций для решения более сложной формулы. Здесь мы хотим рассчитать стоимость налога с продаж для счета за питание. Для этого мы напишем нашу формулу как =(D3+D4+D5)*0,075 в ячейке D6. Эта формула суммирует цены наших товаров, а затем умножает это значение на налоговую ставку 7,5% (которая записывается как 0,075), чтобы вычислить ответ.

Excel следует порядку операций и сначала складывает значения в скобках: (45,80+68,70+159,60) = 274,10. Затем это значение умножается на налоговую ставку: 274,10 * 0,075. Результат покажет, что налог с продаж составляет 20,56 доллара США.

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

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

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

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

Вызов!

В этом задании вы будете работать с другим счетом, подобным тому, что в нашем примере. В счете-фактуре вы найдете сумму налога за заказ, общую сумму заказа и общую сумму заказа, если вам была предоставлена скидка 10 %.

Откройте нашу рабочую тетрадь.
Перейдите на вкладку "Задание" в левом нижнем углу книги.
В ячейке D7 создайте формулу для расчета налога для счета. Используйте ставку налога с продаж в размере 7,5%.
В ячейке D8 создайте формулу для нахождения суммы заказа. Другими словами, эта формула должна добавлять ячейки D3:D7.
В ячейке D9 создайте формулу, которая вычисляет итоговую сумму после 10-процентной скидки. Если вам нужна помощь в понимании того, как уменьшить процент от общей суммы, просмотрите наш урок о скидках, уценках и распродажах.
Когда вы закончите, ваша таблица должна выглядеть так:

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

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

Из-за своей гибкости 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. Уточняйте единицы измерения и числовые форматы

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

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

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

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

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

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

Читайте также: