Что такое формулы и функции в Excel и как их использовать?

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

как мне скомпилировать Java-программу

Вот краткий обзор обсуждаемых здесь тем:

• Что такое формула?
• Написание формул Excel
• Редактирование формулы
• Копировать / вставить формулу
• Скрыть формулы в Excel
• Формулы Excel приоритета операторов
• Что такое функции в Excel?
• Наиболее важные функции

Что такое формула?

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

Формулы Excel могут быть следующих типов:

Написание формул Excel:

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

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

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

Редактирование формулы:

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

Как только это будет сделано, нажмите Enter, чтобы увидеть желаемый результат.

Скопируйте или вставьте формулу:

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

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

Как вы можете видеть на изображении, формула изначально написана в A3, а затем я перетащил ее вниз по B3 и C3, чтобы вычислить сумму B1, B2 и C1, C2, не записывая исключительно адреса ячеек.

Если я изменю значения любой из ячеек, выходные данные будут автоматически обновлены Excel в соответствии с Относительные адреса ячеек , Абсолютные адреса ячеек или Смешанные адреса ячеек .

Скрыть формулы в Excel:

Если вы хотите скрыть какую-то формулу с листа Excel, вы можете сделать это следующим образом:

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

Приоритет операторов формул Excel:

Формулы Excel подчиняются правилам BODMAS (сложение сложения и умножения в скобках). Если у вас есть формула, содержащая квадратные скобки, выражение в скобках будет решено до любой другой части полной формулы. Взгляните на изображение ниже:

Как вы можете видеть в приведенном выше примере, у меня есть формула, состоящая из скобок. Таким образом, в соответствии с правилами BODMAS, Excel сначала найдет разницу между A2 и B1, а затем сложит результат с A1.

Что есть «Функции» в Excel?

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

Формулы в Excel делятся на следующие категории:

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

Наиболее важные функции Excel:

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

ДАТА:

Одна из наиболее важных и широко используемых функций даты в Excel - это функция ДАТА. Его синтаксис следующий:

ДАТА (год, месяц, день)

Эта функция возвращает число, представляющее данную дату в формате даты и времени MS Excel. Функцию ДАТА можно использовать следующим образом:

ПРИМЕР:

1. = ДАТА (2019,11,7)
2. = ДАТА (2019,11,7) -7 (возвращает текущую дату - семь дней)

ДЕНЬ:

Эта функция возвращает значение дня месяца (1-31). Его синтаксис следующий:

ДЕНЬ (серийный_номер)

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

ПРИМЕР:

1. = ДЕНЬ (A7)
2. = ДЕНЬ (СЕГОДНЯ ())
3. = ДЕНЬ (ДАТА (2019, 11,8))

сервис сейчас билетная система обучение

МЕСЯЦ:

Так же, как функция ДЕНЬ, Excel предоставляет другую функцию, то есть функцию МЕСЯЦ, чтобы получить месяц с определенной даты. Синтаксис следующий:

МЕСЯЦ (серийный_номер)

ПРИМЕР:

1. = МЕСЯЦ (СЕГОДНЯ ())
2. = МЕСЯЦ (ДАТА (2019, 11,8))

Процент:

Как мы все знаем, процентное соотношение - это отношение, рассчитываемое как доля от 100. Его можно обозначить следующим образом:

Процент = (Часть / Целое) x 100

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

• Выберите ячейку, в которой вы хотите отобразить результат
• Введите знак «=»
• Затем введите формулу как A1 / A2 и нажмите Enter.
• В группе номеров вкладок 'Главная страница' выберите символ '%'.

ЕСЛИ:

Оператор IF - это условный оператор, который возвращает True, когда указанное условие удовлетворяется, и Flase, когда условие не выполняется. Excel предоставляет встроенную функцию «ЕСЛИ», которая служит для этой цели. Его синтаксис следующий:

ЕСЛИ (логический_тест, значение_если_ истинно, значение_если_ ложь)

Вот, логический_тест это условие, которое необходимо проверить

ПРИМЕР:

• Введите значения для сравнения
• Выберите ячейку, в которой будет отображаться вывод
• Введите в строке формул «= ЕСЛИ (A1 = A2,« Да »,« Нет »)» и нажмите Enter.

ВПР:

Эта функция используется для поиска и извлечения определенных данных из столбца на листе Excel. Буква «V» в VLOOKUP означает вертикальный поиск. Это одна из наиболее важных и широко используемых формул в Excel, и для использования этой функции таблица должна быть отсортирована в порядке возрастания. Синтаксис этой функции следующий:

ВПР (lookup_value, table_array, col_index, num_range, lookup)

где,

lookup_value значение для поиска

table_array это таблица, в которой нужно искать

col_index столбец, из которого должно быть получено значение

range_lookup (необязательно) возвращает ИСТИНА в течение прибл. match и FALSE для точного совпадения

ПРИМЕР:

Как вы можете видеть на изображении, я указал значение 2, а диапазон таблицы находится между A1 и D4. Я хочу получить имя сотрудника, поэтому я дал значение столбца как 2, и, поскольку я хочу, чтобы оно было точным совпадением, я использовал False для поиска диапазона.

Подоходный налог:

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

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

СУММА:

Функция СУММ в Excel вычисляет результат, складывая все указанные значения в Excel. Синтаксис этой функции следующий:

СУММ (число1; число2;…)

Добавляет к нему все числа, указанные в качестве параметра.

ПРИМЕР:

Если вы хотите рассчитать сумму, которую вы потратили на покупку овощей, перечислите все цены и затем используйте формулу СУММ следующим образом:

Сложный процент:

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

БС (ставка, кпер, пт, пв, тип)

Чтобы рассчитать ставку, вам нужно будет разделить годовую ставку на количество периодов, то есть годовую ставку / периодов. Количество периодов или nper рассчитывается путем умножения срока (количества лет) на периоды, то есть срок * периоды. pmt означает периодический платеж и может принимать любое значение, включая ноль.

Рассмотрим следующий пример:

В приведенном выше примере я рассчитал сложный процент для 500 долларов по ставке 10% за 5 лет и предполагал, что периодический платеж равен 0. Обратите внимание, что я использовал -B1, что означает, что 500 долларов были взяты у меня.

Средний:

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

СРЕДНИЙ (число1, число2,…)

ПРИМЕР:

Если вы хотите подсчитать средние оценки, полученные студентами на всех экзаменах, вы можете просто создать таблицу, а затем использовать формулу СРЕДНИЙ для расчета средних оценок, полученных каждым студентом.

как сделать массив объектов

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

COUNT:

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

СЧЁТ (значение1; значение2;…)

ПРИМЕР:

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

КРУГЛЫЙ:

Чтобы округлить значения до определенных десятичных знаков, вы можете использовать функцию ОКРУГЛ. Эта функция вернет число, округлив его до указанного количества десятичных знаков. Синтаксис этой функции следующий:

ОКРУГЛ (число; число_цифров)

ПРИМЕР:

Оценка:

Чтобы найти оценки, вам нужно будет использовать вложенные операторы IF в Excel. Например, в примере «Среднее значение» я рассчитал средние оценки, полученные учащимися в тестах. Теперь, чтобы найти оценки, полученные этими студентами, мне нужно будет создать вложенную функцию ЕСЛИ следующим образом:

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

= ЕСЛИ (G19> 90, «A», (IF (G19> 75, «B», IF (G19> 60, «C», IF (G19> 40, «D», «F»)))))

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

РАНГ:

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

РАНГ (ссылка, номер, порядок)

ПРИМЕР:

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

СЧЁТЕСЛИ:

Для подсчета ячеек на основе определенного условия вы можете использовать одну из встроенных формул Excel под названием «СЧЁТЕСЛИ». Эта функция вернет количество ячеек, удовлетворяющих некоторому условию в заданном диапазоне. Синтаксис этой функции следующий:

СЧЁТЕСЛИ (диапазон; критерий)

ПРИМЕР:

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

ПОКАЗАТЕЛЬ:

Функция ИНДЕКС возвращает значение или ссылку на ячейку в некоторой конкретной позиции в указанном диапазоне. Синтаксис этой функции следующий:

ИНДЕКС (массив, row_num, column_num) или

ИНДЕКС (ссылка,row_num, column_num, area_num)

Индексная функция работает следующим образом для Массив форма:

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

Индексная функция работает следующим образом для Справка форма:

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

ПРИМЕР:

Как видите, в приведенном выше примере я использовал функцию ИНДЕКС для определения значения, присутствующего во 2-й строке и 4-м столбце для диапазона ячеек от A18 до G20.

Точно так же вы также можете использовать функцию ИНДЕКС, указав несколько ссылок следующим образом:

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

Есть вопрос к нам? Пожалуйста, укажите это в разделе комментариев в блоге «Формулы и функции Excel», и мы свяжемся с вами в ближайшее время.

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