Формулы Excel: самые популярные функции и инструменты с примерами

В библиотеке функций Excel имеется более 475 формул, от простой математики до очень сложных статистических, логических и инженерных задач, таких как операторы ЕСЛИ (одна из наших неизменных любимых историй); И, ИЛИ, НЕ функции; COUNT, AVERAGE и MIN/MAX.

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

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

1. СЕГОДНЯ/СЕЙЧАС

В раскрывающемся меню в разделе Формулы> Дата и время перечислены 24 функции даты и времени: 11 форматов даты, 10 форматов времени и столько же пользовательских настраиваемых форматов, которые вы может создать. Функция СЕГОДНЯ показывает текущий месяц, день и год; в то время как функция NOW показывает текущий месяц, день, год и время суток. Это удобная функция, если вы один из тех, кто всегда забывает датировать свою работу.

1. Введите следующую формулу в ячейку A1: = TODAY () и нажмите Enter .

2. Затем введите эту функцию в A1 с помощью = NOW () .

ВАЖНОЕ ПРИМЕЧАНИЕ: Зачем вводить заново? Для правильной работы этих двух формул их необходимо ввести в ячейку «Главная», то есть в ячейку A1, в противном случае они не будут обновляться автоматически при пересчете таблицы. Нажмите Shift- F9 , чтобы вычислить/пересчитать только активную электронную таблицу, или нажмите F9 для всей книги.

После того, как вы введите одну из этих функций в A1, затем вы можете переформатировать дату и время или использовать системные значения по умолчанию. Формат по умолчанию для функции СЕГОДНЯ — 29.08.18, а для СЕЙЧАС — 29.08.18 21:57. Если они вам не подходят, измените их.

3. Наведите курсор на дату или время, которые вы хотите изменить, и выберите Главная > Формат > Формат ячеек .

4. В диалоговом окне Формат ячеек выберите Дата (или Время ) из Категории на вкладке Число .

5. Прокрутите список форматов даты/времени в диалоговом окне «Тип» и выберите формат, который лучше всего подходит для вашего проекта.

JD Sartain/IDG Worldwide

2. Функции СУММ

Вероятно, наиболее часто используемая функция в Excel (или любой другой программе для работы с электронными таблицами) = СУММ делает именно это: суммирует столбец, строку или диапазон чисел, но не просто сумма. Он также вычитает, умножает, делит и использует любой из операторов сравнения для возврата результата 1 (истина) или 0 (ложь).

Вы также можете получить те же результаты, просто используя плюс знак (+) вместо функции СУММ. Например, обе эти формулы дают один и тот же ответ: = СУММ (J7 * 9) и = + (J7 * 9). Обратите внимание, что на приведенном ниже рисунке электронной таблицы в ячейках с E3 по E8 используется функция СУММ, а в ячейках с E9 по E9 по E14 используется знак плюса (+), и результаты одинаковы.

Вы можете ввести функцию СУММ (или знак +) вручную или выберите ее в меню ленты в разделе Формулы > Math & Trig (кнопка), затем выберите из раскрывающегося списка список вниз; или выберите (в меню ленты) Формулы > Вставить функцию , затем прокрутите список вниз и выберите его оттуда.

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

JD Sartain /IDG Worldwide

Проблема возникает, когда диапазон чисел, который вам нужно вычислить, усложняется из-за нескольких операторов вычислений в нескольких ячейках, например: = СУММ (H1 + I1 * J1-M1 * J1. Помните свой математика в средней школе? Если числа в формуле не сгруппированы должным образом, ответ будет неправильным. Обратите внимание на снимок экрана ниже (рис. 2).

Введите следующие заголовки столбцов с H2 по P2 (используйте Alt + Enter, чтобы объединить заголовки в одну ячейку): ежедневный заработок, плюс бонусы, количество отработанных дней в днях, валовая заработная плата (формула), минус питание из расчета 9 долларов в день, общий ежемесячный доход, формула и комментарий.

ПРИМЕЧАНИЕ. Столбцы формул предназначены только для информации и не предоставляют внутренней ценности для электронной таблицы. Они просто «отображают» формулу для вашего удобства (так что вы можете видеть синтаксис каждой формулы, использующей d).

В этом упражнении вы можете ввести те же значения в H3: 11, I3: 11 и J3: 11, с пустыми строками между ними или без них (опять же, добавлены для упрощения просмотр). Выполните следующим образом: 86,00 долларов США, 20,00 долларов США, 22,0 рабочих дня, а остальные — формулы. Обратите внимание, что при построении каждой формулы мы, в конечном итоге, объединяем шаги в одну формулу.

Мы начинаем с трех отдельных формул. Первый — это добавить ежедневный заработок плюс бонусы, умноженный на количество отработанных дней в месяце, что равняется валовой заработной плате: = СУММ (H3 + I3 * J3) в ячейке K3. Обратите внимание, что ответ — 526,00 долларов. Это просто выглядит неправильно.

Используйте свой калькулятор, чтобы проверить формулы, чтобы убедиться, что они верны, ПРЕЖДЕ, чем копировать их в остальные ячейки столбца.

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

Обратите внимание на исправленную формулу в ячейке K4: = СУММ (H4 + I4) * J4. Снова проверьте свои числа (с помощью калькулятора) и обратите внимание, что эта формула верна. Правильный ответ — 2332,00 доллара.

5. Вторая формула (в M4): = СУММ (J4 * 9) умножает рабочие дни (22) на 9 долларов.. 00, стоимость питания в сутки. Правильный ответ — 198,00 долларов.

6. Третья формула (в N4) рассчитывает ежемесячный заработок за вычетом обедов: = СУММ (K4-M4); Ответ — 2134,00 доллара.

7. В следующей группе (H6: N8) формулы в M6: M8 остаются прежними: = СУММ (J7 * 9) и т. Д. — опять же, это количество рабочих дней, умноженное на стоимость обедов. Но формулы в столбце K удаляются, а затем объединяются с формулами в столбце M: = СУММ (H7 + I7) * J7-M7. Обратите внимание, что синтаксис (структура или макет формулы) правильный в ячейках N7 и N8, но неверен в ячейках N6.

8. Следующая группа (H10: H11) объединяет формулы в столбце M с формулами в столбце N: = СУММ (H11 + I11) * J11- (M11 * J11) — обратите внимание, что формула в N10 неверна. Объединив эти формулы в одну, можно исключить столбцы K и L.

9. Кроме того, вместо «жесткого кодирования» цены обедов (как показано в M3: M4 и M6: M8) теперь вы можете изменить цену обедов в столбце M (M10: M11), когда инфляция требует увеличения, а не изменения формулу.

JD Sartain/IDG Worldwide

3. Функция RAND

Функция RAND действительно проста и традиционно используется для статистического анализа, криптографии, игр, азартных игр и теории вероятностей, среди множества других вещей. В Excel функция СЛЧИС генерирует случайное число от 0 до 1. Примечание; однако каждый раз, когда вы вводите новые данные и нажимаете клавишу Enter, список только что созданных случайных чисел изменяется. Если вам нужно поддерживать списки случайных чисел, вы должны отформатировать ячейки как значения.

1. Введите функцию = RAND () в столбцы с A3 по A14. Выберите этот столбец и нажмите Ctrl + C (для копирования) или нажмите кнопку Копировать на вкладке Главная и выберите Копировать из раскрывающегося меню. Переместите курсор в ячейку B3 и выберите Главная > Вставить > Специальная вставка . Нажмите кнопку Значения в диалоговом окне Специальная вставка , затем нажмите ОК .

2. Теперь список содержит значения вместо функций, поэтому он не изменится. Обратите внимание (в строке формул), что случайные числа имеют 15 цифр после десятичной дроби (в Excel по умолчанию 9), которые при необходимости можно изменить (как показано в ячейке F3). Просто нажмите кнопку Увеличить десятичное число в группе Число на вкладке Главная .

3. Если вы предпочитаете работать с целыми числами, введите эту формулу в ячейку F3: = INT (RAND () * 999) , и вы получите трехзначное случайное число. Скопируйте формулу вниз до F12, затем добавьте еще одну цифру «9» в строку, чтобы добавить еще одну цифру к случайному числу, например, четыре девятки равны четырем цифрам, пять девяток равны пяти цифрам. Опять же, вы должны скопировать список и Вставить как значения, чтобы сохранить статический список.

JD Sartain/IDG Worldwide

4. Функции COUNT

Используйте функцию COUNT для подсчета количества числовых значений в диапазоне ячеек; например: C4: C15 содержит количество садовых инструментов, которое мистер МакГрегор должен заказать для своего магазина. Обратите внимание, что ответ — 10 (из 12), потому что функция СЧЁТ не включает пустые ячейки. Однако, если вы введите ноль, числовой код или дату, Excel считает это «занятой» ячейкой и включает в свой ответ.

Введите 10 чисел в столбец C (Количество). Замените одно число с пробелом (или нажмите на пробел), затем замените другой номер точкой с запятой, а затем введите дату в ячейку C7.

Введите эту формулу в нижней части списка номеров (C16 ): = COUNT (C4: C15) . Ответ — 10 (из 12), потому что Excel подсчитал все числа и дату, но проигнорировал пустую ячейку (содержащую пробел) и пунктуация в ячейке C8.

Используйте функцию COUNTA, если вы хотите включить числовые значения, логические значения или значения ошибок, текст, пробел (от пробела), пунктуация, символы или любой другой символ на клавиатуре.

1. Введите сумму в 12 долларов в столбец D (Цена). Замените одну ячейку вопросительным знаком, другую ячейку — символом, а другую ячейку — текстом.

2. Введите эту формулу в D16: = COUNTA (C4: C15) . Ответ — 12 (из 12), потому что Excel включил все «нечисловые» значения и символы.

3. Обратите внимание, что в строке 18 (C и D) отображаются фактические формулы, которые находятся в C и D 16.

JD Sartain/IDG Worldwide

5. Функция AVERAGE

Практически каждый знает, что среднее значение определяется путем сложения всех значений в списке, а затем деления на количество перечисленных значений; например, 4 + 5 + 3 = 12/3 = 4, что является средним значением. Вы можете использовать функцию СУММ и сложить деление в одной формуле или просто использовать функцию СРЕДНИЙ. Синтаксис: = СРЕДНЕЕ (диапазон).

1. Введите числа в столбец A. Введите функцию СРЕДНЕЕ внизу списка: = СРЕДНЕЕ (A4: A13) и обратите внимание, что ответ (в нашем случае) — 53. Вы можете проверить свой ответ с функцией СУММ; то есть: = СУММ (A4: A13/10) = 53.

2. Затем введите еще несколько чисел в столбец C, но на этот раз добавьте текст в одну ячейку, знаки препинания в другую и пробел в другую. Введите ту же формулу: = AVERAGE (A4: A15) и обратите внимание, что ответ — 78. Для проверки введите формулу SUM, опуская ячейки, содержащие нечисловые символы:

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

ВАЖНОЕ ПРИМЕЧАНИЕ. Если вы импортируете огромные базы данных с мэйнфрейма или внешнего внешнего источника, иногда числа экспортируются как текст. Как узнать, действительно ли число является текстом? Как правило, текст выравнивается по левому краю, а числа — по правому краю, но, поскольку все теперь форматируют свои электронные таблицы для эстетики, этот метод ненадежен. Другой вариант — быстро просмотреть длинный список импортированных чисел и посмотреть на панель формул. Если вы видите апостроф перед любым числом, это текст. Наконец, найдите зеленый треугольник в верхнем левом углу ячейки. Если предыдущий владелец электронной таблицы не проинструктировал Excel игнорировать эту ошибку, тогда содержимое ячейки будет текстовым.

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

JD Sartain/IDG Worldwide

6. Функции MIN/MAX

Используйте функцию MIN, чтобы найти наименьшее число в диапазоне значений, и функцию MAX, чтобы найти наибольшее. Синтаксис этих функций: = MIN (диапазон); = MAX (диапазон), где диапазон равен списку вычисляемых чисел.

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

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

Функции MIN/MAX остаются неизменными независимо от изменений в данных , даже если вы добавляете больше строк (если вы добавляете строки с помощью функции «Вставить> Строка» в пределах существующего диапазона, то есть над ячейкой, содержащей формулу).

Введите несколько чисел в столбце A4: A11, затем введите эту формулу в A13: = MIN (A4: A11) и эту формулу в A14: = MAX (A4: A11) .

ПРИМЕЧАНИЕ. Функции MIN/MAX игнорируют пустые ячейки, ответы ИСТИНА/ЛОЖЬ, текст, олицетворение текста числами, символами и пунктуацией.

А теперь мощная функция Concatenate, которая объединяет данные из нескольких ячеек! Продолжайте читать, чтобы узнать больше о формулах.



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

Алиса Форбс

2 июля 2019 г. · 5 минут чтения

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

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

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

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

  4. Сложение и вычитание , в зависимости от того, что наступит раньше

Мнемоника, которая поможет вам запомнить порядок: PEMDAS или P lease E xcuse M y D ear A unt S союзник.

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

Изображение для сообщения

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

Изображение для сообщения

Изображение для сообщения

Изображение для сообщения

 Изображение для сообщения

 Изображение для сообщения

 Изображение для сообщения

 Изображение для сообщения

В приведенном ниже примере мы продемонстрируем, как Excel использует порядок операций для решения более сложной формулы. Здесь , мы хотим рассчитать стоимость налога с продаж для счета за питание. Для этого мы напишем mula как = (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 первыми.

 Изображение для сообщения

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

  1. Выберите ячейку , которая будет содержать формулу. В нашем примере мы выберем ячейку C5 .

 Изображение для сообщения

Изображение для сообщения

  1. Еще раз проверьте точность формулы , затем нажмите Enter на клавиатуре. Формула вычислит и отобразит результат . В нашем примере результат показывает, что промежуточная сумма по заказу составляет

Изображение для сообщения

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

Изображение для публикации

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

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

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

Изображение для сообщения

Подробнее

Оцените статью
clickpad.ru
Добавить комментарий