7 порад щодо роботи з формулами Excel

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

Головний пріоритет оператора

Одним з найважливіших факторів у написанні формул є правильність операторів, і я не маю на увазі операторів телефонної компанії. Це пов’язано з математичними операторами — ви знаєте, дрібними деталями, такими як знаки плюса, знаки множення та місця, де йдуть дужки. Пріоритетність оператора — порядок, у якому виконуються операції — може мати велике значення в результаті. У вас є простий спосіб зберегти пріоритет вашого оператора в порядку. Все, що вам потрібно пам’ятати, це: «Будь ласка, вибачте мою любу тітоньку Саллі».

Ця фраза є мнемонікою для наступного:

• Круглі дужки

• Показники

• Множення

• Відділ

• Додавання

• Віднімання

Таким чином, дужки мають перший (найвищий) пріоритет, а віднімання — останній. Ну, чесно кажучи, множення має такий же пріоритет, як ділення, а додавання має такий же пріоритет, як і віднімання, але ви зрозуміли!

Наприклад, формула =1 + 2 × 15 дорівнює 31. Якщо ви думаєте, що воно має дорівнювати 45, вам краще піти в гості до тітки! Відповідь дорівнює 45, якщо включити дужки, наприклад: =(1 + 2) × 15.

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

Кілька хвилин освіження пам’яті на замовлення оператора можуть позбавити вас від багатьох головних болів у майбутньому.

Відобразити формули

Якщо ви не помітили, вам важко переглянути формули, не відредагувавши їх випадково. Це тому, що щоразу, коли ви перебуваєте в режимі «редагування», а активна клітинка має формулу, формула може включати адресу будь-якої іншої клітинки, яку ви клацнете. Це повністю зіпсує речі.

Чи не було б легко, якби ви могли просто подивитися на всі свої формули? Є спосіб! Це просто. Клацніть «Файл» у верхньому лівому куті робочої області Excel, натисніть «Параметри», перейдіть на вкладку «Додатково» та прокрутіть униз до розділу «Параметри відображення для цього аркуша».

Параметри налаштування.

Зверніть увагу на прапорець Показувати формули в клітинках замість результатів обчислення. Це поле повідомляє Excel, що для будь-яких клітинок, які мають формули, відображатиметься сама формула замість результату обчислення. На наступному малюнку показано робочий аркуш, який відображає формули. Щоб повернутися до звичайного вигляду, повторіть ці кроки та зніміть вибір. Ця опція дозволяє легко побачити, які всі формули!

Легкий спосіб перегляду формул.

Ви можете випадково редагувати функції, навіть якщо вибрано параметр Переглянути формули. Будьте обережні, клацаючи по аркушу.

Виправити формули

Припустимо, що у вашому робочому аркуші є помилки. Не панікуйте! Це трапляється навіть з найдосвідченішими користувачами, і Excel може допомогти вам зрозуміти, що відбувається не так. На вкладці Формули в розділі Аудит формул знаходиться кнопка Перевірка помилок. При натисканні кнопки відкриється діалогове вікно Перевірка помилок, яке показано тут. Тобто діалогове вікно з’являється, якщо на аркуші є помилки. В іншому випадку просто з’явиться повідомлення про те, що перевірка помилок завершена. Це так розумно!

Перевірка на наявність помилок.

Якщо є помилки, з’являється діалогове вікно, яке залишається на екрані, поки ви працюєте над кожною помилкою. Кнопки «Далі» та «Назад» дають змогу перебирати всі помилки, перш ніж діалогове вікно закриється. Для кожної знайденої помилки ви вибираєте, яку дію виконати:

• Довідка щодо цієї помилки: Це веде до довідкової системи та відображає тему для конкретного типу помилки.

• Показати кроки обчислення: відкриється діалогове вікно Оцінити формулу, і ви можете крок за кроком спостерігати, як обчислюється формула. Це дозволяє визначити конкретний крок, який спричинив помилку.

• Помилка ігнорування: можливо, Excel помиляється. Ігноруйте помилку.

• Редагувати в рядку формул: це швидкий спосіб самостійно виправити формулу, якщо вам не потрібна інша допомога.

У діалоговому вікні «Перевірка помилок» також є кнопка «Параметри». Натискання кнопки відкриває вкладку «Формули» діалогового вікна «Параметри Excel». На вкладці «Формули» можна вибрати параметри та правила розпізнавання та запуску помилок.

Використовуйте абсолютні посилання

Якщо ви збираєтеся використовувати ту саму формулу для групи комірок, наприклад тих, що йдуть вниз по стовпцю, найкращим методом буде написати формулу один раз, а потім перетягнути її вниз до інших клітинок за допомогою маркера заповнення. Проблема в тому, що коли ви перетягуєте формулу до нових місць, будь-які відносні посилання змінюються.

Часто це і є намір. Якщо є один стовпець даних і сусідній стовпець формул, зазвичай кожна клітинка в стовпці формул посилається на свого сусіда в стовпці даних. Але якщо всі формули посилаються на клітинку, яка не є суміжною, зазвичай всі клітинки формули посилаються на незмінне посилання на клітинку. Зробіть це правильно, використовуючи абсолютне посилання на клітинку.

Щоб використовувати абсолютне посилання на клітинку, використовуйте знак долара ($) перед номером рядка, перед літерою стовпця або перед обома. Зробіть це, коли ви пишете першу формулу, перш ніж перетягувати її в інші клітинки, інакше вам доведеться оновити всі формули.

Наприклад, не пишіть цього:

=A4 x (B4 + A2)

Натомість запишіть це так:

=A4 x (B4 + $A$2)

Таким чином, усі формули посилаються на A2, незалежно від того, куди ви їх скопіюєте, замість того, щоб це посилання перетворювалося на A3, A4 тощо.

Використовуйте аудит формул

Є прецеденти та залежні особи. Є зовнішні посилання. Скрізь існує взаємодія. Як ви можете відстежити, звідки надходять і куди надходять посилання на формули?

Використовуйте інструменти аудиту формул, ось як! На вкладці Формули знаходиться розділ Аудит формул. У розділі розташовані різні кнопки, які керують видимістю стрілок аудиту.

Формули аудиту.

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

Використовуйте умовне форматування

Подібно до того, як функція IF повертає певне значення, коли перша умова аргументу є істинною, і інше значення, коли вона хибна, умовне форматування дозволяє застосувати певний формат до комірки, коли умова є істинною. На вкладці «Головна» в розділі «Стилі» є спадне меню з багатьма параметрами умовного форматування.

На цьому малюнку показано деякі значення, які були оброблені за допомогою умовного форматування. Умовне форматування дозволяє встановити умову та вибрати формат, який буде застосовано, коли умова виконується. Наприклад, ви можете вказати, щоб клітинка відображалася жирним курсивом, якщо значення, яке вона містить, перевищує 100.

Застосування формату, коли виконується умова.

Умови встановлюються як правила. Типи правил

• Відформатуйте всі клітинки на основі їх значень.

• Форматуйте лише клітинки, які містять. . . .

• Форматуйте лише значення з верхньою або нижньою позицією.

• Форматуйте лише значення, які вищі або нижче середнього.

• Форматуйте лише унікальні або повторювані значення.

• Використовуйте формулу, щоб визначити, які клітинки форматувати.

Коли умова виконується, форматування може керувати таким:

• Кордони

• Налаштування шрифту (стиль, колір, напівжирний, курсив тощо)

• Заливка (колір або візерунок клітинки)

Комірки також можна відформатувати за допомогою колірних схем або зображень піктограм, розміщених у клітинці.

Використовуйте перевірку даних

На вкладці «Дані» в розділі «Інструменти даних» знаходиться перевірка даних. Перевірка даних дозволяє застосувати правило до клітинки (або клітинок), щоб запис відповідати правилу. Наприклад, клітинку можна налаштувати на прийом лише цілого запису від 50 до 100.

Налаштування перевірки даних.

Якщо введення не відповідає правилу, відображається повідомлення.

Упіймали при неправильному введенні.

Повідомлення про помилку можна налаштувати. Наприклад, якщо хтось введе неправильне число, у відображеному повідомленні про помилку може бути написано Noodlehead — навчіться рахувати! Тільки не дозволяйте босу це бачити.