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

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

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

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

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

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

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

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

Мнемоника, которая может помочь вам запомнить заказ, – это P lease E xcuse M y D ear A un S ally.

Пример 1

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

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

Пример 2.

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

Пример операции Excel
  1. Во-первых, Excel рассчитает сумму, проданную в круглых скобках: (19 * 1,99) = 37,81. Семена Белой Пти Лили и (33 * 1,99) = 65,67. Семена Пит Лили.
  2. Во-вторых, он разделит количество семян Белого Пета Лили на количество семян Total Pete Lily: 37.81 / 65.67 = .5758 .
  3. Наконец, он умножит результат на 100, чтобы получить значение в процентах: .5758 * 100 = 57.58 .

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

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

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

  1. Щелкните ячейку, в которой вы хотите получить результат формулы (например, F11 ).
  2. Введите знак равенства (=) .
  3. Введите открытую скобку , затем щелкните ячейку, которая содержит первое значение, которое вы хотите в формуле (например, F4 ).
  4. Введите первый математический оператор (например, знак добавления).
  5. Щелкните ячейку, которая содержит второе значение, которое вы хотите в формуле ( например, F5 ), затем введите закрытую круглую скобку .
  6. Введите следующий математический оператор (например, знак умножения).
  7. Введите следующее значение в формуле ( 0,055 для налога 5,5% , например).
    Строка 4, колонка D
  8. Нажмите « Ввод», чтобы рассчитать формулу. Результаты показывают, что 2.12 доллара – это налог на детский заказ.
    Результат в F11

Excel не всегда скажет вам , содержит ли ваша формула ошибку, поэтому вам нужно проверить все ваши формулы. Чтобы узнать, как это сделать, вы можете прочитать урок Double-Check Your Formulas из нашего учебного пособия по Excel .

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

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

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

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

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

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

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

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

  1. Выберите первую ячейку, в которую вы хотите ввести формулу (например, B4 ).
    Выбор ячейки B4
  2. Введите формулу для вычисления требуемого значения (например, B2 + B3 ).
    Ввод формулы в B4
  3. Нажмите Enter . Формула будет рассчитана.
    Результат в B4
  4. Выберите ячейку, которую вы хотите скопировать ( например, B4 ), затем нажмите команду « Копировать» на вкладке « Главная ».
  5. Выберите ячейки, в которые вы хотите вставить формулу, затем нажмите кнопку « Вставить» на вкладке « Главная ». Вы также можете перетащить дескриптор заполнения для заполнения ячеек.
    Значения, рассчитанные в C4: M4
  6. Ваша формула копируется в выбранные ячейки как относительная ссылка (C4 = C2 + C3, D4 = D2 + D3, E4 = E2 + E3 и т. Д.), И значения вычисляются.

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

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

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

Абсолютная справочная таблица

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

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

  1. Выберите первую ячейку, в которую вы хотите ввести формулу (например, C4 ).
    Выбор ячейки C4
  2. Введите знак равенства, а затем щелкните ячейку, которая содержит первое значение, которое вы хотите в формуле (например, B4 ).
  3. Введите первый математический оператор (например, знак умножения).
  4. Введите знак доллара ($) , затем введите букву столбца ячейки, на которую вы делаете абсолютную ссылку (например, B ).
    Ввод формулы
  5. Введите знак доллара ($) , а затем ввести номер строки из одной и той же ячейки вы делаете абсолютную ссылку на ( 1 , например).
    Ввод формулы
  6. Нажмите « Ввод», чтобы рассчитать формулу.
    Результат в C4
  7. Выберите ячейку, которую вы хотите скопировать ( например, C4 ), затем нажмите команду « Копировать» на вкладке « Главная ».
  8. Выберите ячейки, в которые вы хотите вставить формулу, затем нажмите кнопку « Вставить» на вкладке « Главная ». Вы также можете перетащить дескриптор заполнения для заполнения ячеек.
    Значения, рассчитанные в C5: C8
  9. Ваша формула копируется в выбранные ячейки с использованием абсолютной ссылки (C5 = B5 * $ B $ 1, C6 = B6 * $ B $ 1 и т. Д.), И ваши значения вычисляются.

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

Задание!

  1. Откройте существующую книгу Excel 2010 . Если вы хотите, вы можете использовать этот пример .
  2. Создайте формулу, использующую абсолютную ссылку . Если вы используете пример, рассчитать налог с продаж в E4: E20 . Используйте ячейку C23 в качестве абсолютной ссылки на цену налога с продаж.
  3. Создайте формулу, использующую относительную ссылку . Если вы используете пример, создайте формулу, которая добавляет цену каждого элемента в столбце D и налог с продаж для каждого элемента в столбце E, а затем умножает результат на количество каждого элемента в столбце F. Введите ваши результаты в (столбец G). Подсказка: вам нужно подумать о том, как порядок операций будет работать правильно.

Залишити відповідь