Формулы Excel и описание их использования

Обновлено: 06.07.2024

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

В этой статье мы рассмотрим следующие темы.

Данные учебных пособий

В этом руководстве мы будем работать со следующими наборами данных.

Бюджет товаров для дома

S/N	ПУНКТ	КОЛИЧЕСТВО	ЦЕНА
1	Манго	9	600
2	Апельсины	3	1200
3	Помидоры	1	2500
4	Масло для жарки	5	6500
5	Тоник	13	3900

Расписание проекта строительства дома

S/N	ПУНКТ	ДАТА НАЧАЛА	ДАТА ЗАВЕРШЕНИЯ
1	Обзор земли	02/04/2015	07 /02/2015
2	Lay Foundation	02/10/2015	15/02 /2015
3	Кровля	27/02/2015	03/03/2015< /td>
4	Живопись	03.09.2015	03.09.2015

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

ФОРМУЛЫ В EXCEL – это выражение, которое работает со значениями в диапазоне адресов ячеек и операторов. Например, =A1+A2+A3, который находит сумму диапазона значений от ячейки A1 до ячейки A3. Пример формулы, состоящей из дискретных значений, таких как =6*3.

ЗДЕСЬ

"A2" * D2" ссылается на адреса ячеек A2 и D2, а затем умножает значения, найденные в этих адресах ячеек.
"/" – арифметический оператор деления.
"2" – это дискретное значение.

Практическое упражнение по формулам

Мы будем работать с примерными данными для домашнего бюджета, чтобы рассчитать промежуточный итог.

Создать новую книгу в Excel
Введите данные, указанные выше в бюджете товаров для дома.
Ваш лист должен выглядеть следующим образом.

Теперь мы напишем формулу для расчета промежуточного итога

Установите фокус на ячейку E4

Введите следующую формулу.

ЗДЕСЬ

"C4*D4" использует арифметический оператор умножения (*) для умножения значения адреса ячейки C4 и D4.

Нажмите клавишу ввода

Вы получите следующий результат

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

Ошибки, которых следует избегать при работе с формулами в Excel

Запомните правила скобок деления, умножения, сложения и вычитания (BODMAS). Это означает, что выражения в квадратных скобках оцениваются в первую очередь. Для арифметических операций сначала оценивается деление, затем умножение, а затем сложение и вычитание. Используя это правило, мы можем переписать приведенную выше формулу как =(A2 * D2) / 2. Это гарантирует, что A2 и D2 сначала вычисляются, а затем делятся на два.
Формулы электронных таблиц Excel обычно работают с числовыми данными; вы можете воспользоваться проверкой данных, чтобы указать тип данных, которые должна принимать ячейка, т. е. только числа.
Чтобы убедиться, что вы работаете с правильными адресами ячеек, указанными в формулах, вы можете нажать клавишу F2 на клавиатуре. При этом будут выделены адреса ячеек, используемые в формуле, и вы сможете перепроверить, чтобы убедиться, что они являются нужными адресами ячеек.
Если вы работаете с большим количеством строк, вы можете использовать порядковые номера для всех строк и иметь количество записей в нижней части листа. Вы должны сравнить количество серийных номеров с общим количеством записей, чтобы убедиться, что ваши формулы включают все строки.

Что такое функция в Excel?

ФУНКЦИЯ В EXCEL – это предопределенная формула, которая используется для определенных значений в определенном порядке. Функция используется для быстрых задач, таких как нахождение суммы, количества, среднего, максимального значения и минимального значения для диапазона ячеек. Например, ячейка A3 ниже содержит функцию СУММ, которая вычисляет сумму диапазона A1:A2.

СУММ для суммирования диапазона чисел
СРЗНАЧ для расчета среднего заданного диапазона чисел.
COUNT для подсчета количества элементов в заданном диапазоне.

Важность функций

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

Для функции вы бы записали приведенную выше формулу как

Как видно из приведенной выше функции, используемой для получения суммы диапазона ячеек, гораздо эффективнее использовать функцию для получения суммы, чем использовать формулу, которая должна ссылаться на множество ячеек.< /p>

Общие функции

Давайте рассмотрим некоторые из наиболее часто используемых функций в формулах MS Excel. Мы начнем со статистических функций.

< td>Находит минимальное значение в диапазоне ячеек < td>=COUNT(E4:E8)

Серийный номер	ФУНКЦИЯ	КАТЕГОРИЯ	ОПИСАНИЕ	ИСПОЛЬЗОВАНИЕ
01	SUM	Math & Trig	Складывает все значения в диапазоне ячеек	=СУММ(E4:E8)
02	MIN	Статистические	=MIN(E4:E8)
03	MAX	Статистический	Находит максимальное значение в диапазоне ячеек	=MAX(E4:E8)
04	СРЕДНЕЕ	Статистическое	Вычисляет среднее значение в диапазоне ячеек	=СРЗНАЧ(E4:E8)
05	COUNT	Статистический	Подсчитывает количество ячеек в диапазоне ячеек
06	LEN	Text	Возвращает число символов в текстовой строке	=LEN(B7)
07	СУММЕСЛИ	Math & Trig	Складывает все значения в ar количество ячеек, отвечающих заданным критериям. =СУММЕСЛИ(диапазон,критерий,[сумма_диапазон])	=СУММЕСЛИ(D4:D8”,>=1000″,C4:C8)
08	СРЗНАЧЕСЛИ	Статистический	Вычисляет среднее значение в диапазоне ячеек, соответствующих заданным критериям. =СРЗНАЧЕСЛИ(диапазон,критерий,[средний_диапазон])	=СРЗНАЧЕСЛИ(F4:F8”,Да”,E4:E8)
09	DAYS	Дата и время	Возвращает количество дней между двумя датами	=DAYS(D4,C4)< /td>
10	СЕЙЧАС	Дата и время	Возвращает текущую системную дату и время	=СЕЙЧАС()

Числовые функции

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

td> < tr> < td>Math & Trig

Серийный номер	ФУНКЦИЯ	КАТЕГОРИЯ	ОПИСАНИЕ	ИСПОЛЬЗОВАНИЕ
1	ISNUMBER	Информация	Возвращает True, если предоставленное значение является числовым, и False, если это не числовое значение	=ISNUMBER(A3)
2	RAND	Math & Trig< /td>	Генерирует случайное число от 0 до 1	=RAND()
3	ОКРУГЛ	Math & Trig	Округляет десятичное значение до указанного числа знаков после запятой	=ROUND(3.14455,2)
4	МЕДИАНА	Статистическая	Возвращает число в середине набора заданных чисел	=МЕДИАНА (3,4,5,2,5)
5	PI	Math & Trig	Возвращает значение математической функции PI(π)	=PI()
6	POWER	Возвращает результат возведения числа в степень. POWER(число, мощность)	=POWER(2,4)
7	MOD	Math & Trig	Возвращает остаток при делении двух чисел	=MOD(10,3)
8	ROMAN	Math & Trig	Преобразует число в римские цифры	=ROMAN(1984)

Строковые функции

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

< /tr>

Серийный номер	ФУНКЦИЯ	КАТЕГОРИЯ	ОПИСАНИЕ	ИСПОЛЬЗОВАНИЕ	КОММЕНТАРИЙ
1	LEFT	Text	Возвращает число указанных символов от начала (слева) строки	=LEFT(“GURU99”,4)	Левые 4 символа “GURU99”
2	RIGHT	Text	Возвращает количество указанных символов с конца (правой стороны) string	=RIGHT(“GURU99”,2)	Правые 2 символа “GURU99”
3	MID	Text	Извлекает количество символов из середины строки с указанной начальной позиции и длины. =MID (text, start_num, num_chars)	=MID("GURU99",2,3)	Получение символов со 2 по 5
4	ISTEXT	Информация	Возвращает True, если предоставленный параметр имеет значение Text	=ISTEXT(значение )	value — значение для проверки.
5	НАЙТИ	Text	Возвращает начальную позицию текстовой строки внутри другой текстовой строки. Эта функция чувствительна к регистру. =НАЙТИ(найти_текст, внутри_текста, [начальный_номер])	=НАЙТИ("оо","Кровля",1)	Найти оо в "Кровля", Результат is 2
6	REPLACE	Text	Заменяет часть строки другой указанной строкой. =REPLACE (old_text, start_num, num_chars, new_text)	=REPLACE("Roofing",2,2,xx")	Замените "oo" на " xx”

Функции даты и времени

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

Серийный номер	ФУНКЦИЯ	КАТЕГОРИЯ	ОПИСАНИЕ	ИСПОЛЬЗОВАНИЕ
1	ДАТА	Дата и время	Возвращает число, представляющее дату в Excel code	=DATE(2015,2,4)
2	DAYS	Дата и время	Найти количество дней между двумя датами	=DAYS(D6,C6)
3	МЕСЯЦ	Дата и время	Возвращает месяц из значения даты	=МЕСЯЦ("4/2/2015")
4	МИНУТЫ	Дата и время	Возвращает минуты из значения времени	=МИНУТЫ («12:31»)
5	YEAR	Дата и время	Возвращает год из значения даты	=YEAR("02/04/2015")

Функция ВПР

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

ЗДЕСЬ

"=ВПР" вызывает функцию вертикального поиска
"C12" указывает значение для поиска в крайнем левом столбце.
"A4:B8" указывает массив таблиц с данными
"2" указывает номер столбца со значением строки, возвращаемым функцией ВПР.
"ЛОЖЬ" сообщает функции ВПР, что мы ищем точное совпадение с предоставленным поисковым значением.

Анимированное изображение ниже показывает это в действии

Обзор

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

Освоение основных формул Excel имеет решающее значение для начинающих, чтобы стать высококвалифицированными в финансовом анализе Должностная инструкция финансового аналитика Приведенная ниже должностная инструкция финансового аналитика дает типичный пример всех навыков, образования и опыта, необходимых для найма на должность аналитика. в банке, учреждении или корпорации. Выполняйте финансовое прогнозирование, отчетность и отслеживайте операционные показатели, анализируйте финансовые данные, создавайте финансовые модели. Ресурсы Microsoft Excel Excel Изучайте Excel онлайн с помощью сотен бесплатных руководств, ресурсов, руководств и шпаргалок по Excel! Ресурсы CFI — лучший способ изучить Excel на своих условиях. считается отраслевым стандартом программного обеспечения для анализа данных. Программа Microsoft для работы с электронными таблицами также является одним из наиболее предпочтительных программ для инвестиционных банкиров Инвестиционно-банковские услуги Описание работы В этом описании работы инвестиционно-банковских услуг описываются основные навыки, образование и опыт работы, необходимые для того, чтобы стать аналитиком IB или ассоциированным и финансовым аналитиком в области обработки данных, финансовое моделирование Что такое финансовое моделирование Финансовое моделирование выполняется в Excel для прогнозирования финансовых показателей компании. Обзор того, что такое финансовое моделирование, как и зачем строить модель. и презентация. В этом руководстве представлен обзор и список основных функций Excel.

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

Основные термины в Excel

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

1. Формулы

В Excel формула — это выражение, которое работает со значениями в диапазоне ячеек или ячейке. Например, =A1+A2+A3, который находит сумму диапазона значений от ячейки A1 до ячейки A3.

2. Функции

Функции – это предопределенные формулы в Excel. Они избавляют от трудоемкого ручного ввода формул и присваивают им понятные имена. Например: =СУММ(A1:A3). Функция суммирует все значения от A1 до A3.

Пять способов, позволяющих сэкономить время, чтобы вставить данные в Excel

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

1. Простая вставка: ввод формулы внутри ячейки

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

Основы Excel - формулы для финансов Вы ищете ускоренный курс Excel? Пройдите бесплатное обучение Excel, чтобы начать карьеру в сфере корпоративных финансов и инвестиционно-банковских услуг, от Института корпоративных финансов.

Изображение: бесплатный ускоренный курс Excel от CFI Основы Excel — формулы для финансов Вы ищете ускоренный курс Excel? Получите бесплатное обучение Excel для карьеры в области корпоративных финансов и инвестиционно-банковской деятельности от Института корпоративных финансов. .

2. Использование параметра «Вставить функцию» на вкладке «Формулы»

Если вам нужен полный контроль над вставкой функций, все, что вам нужно, это диалоговое окно Excel Insert Function. Для этого перейдите на вкладку «Формулы» и выберите первое меню с надписью «Вставить функцию». Диалоговое окно будет содержать все функции, необходимые для завершения финансового анализа. Типы финансового анализа Финансовый анализ включает использование финансовых данных для оценки эффективности компании и выработки рекомендаций о том, как ее можно улучшить в будущем. Финансовые аналитики в основном выполняют свою работу в Excel, используя электронные таблицы для анализа исторических данных и создания прогнозов. Типы финансового анализа.

3. Выбор формулы из одной из групп на вкладке «Формула»

Этот вариант предназначен для тех, кто хочет быстро изучить свои любимые функции. Чтобы найти это меню, перейдите на вкладку «Формулы» и выберите предпочитаемую группу. Нажмите, чтобы отобразить подменю со списком функций. Оттуда вы можете выбрать свои предпочтения. Однако, если вы обнаружите, что нужной вам группы нет на вкладке, нажмите на опцию «Дополнительные функции» — возможно, она просто там скрыта.

Курсы Excel

4. Использование опции автосуммы

Для быстрых и повседневных задач функция автосуммы Автосумма Формула автосуммы Excel — это ярлык, который может сэкономить время при финансовом моделировании в Excel. Введите «ALT lazy» src="" alt="Основные формулы Excel для начинающих - AutoSum" width="860" height="404" srcset="" размеры="" data-src="https://cdn.corporatefinanceinstitute" .com/assets/Basic-Excel-Function-AutoSum-1-1024x481.jpg" data-srcset="//cdn.corporatefinanceinstitute.com/assets/Basic-Excel-Function-AutoSum-1-1024x481.jpg 1024w, / /cdn.corporatefinanceinstitute.com/assets/Basic-Excel-Function-AutoSum-1-300x141.jpg 300 Вт, //cdn.corporatefinanceinstitute.com/assets/Basic-Excel-Function-AutoSum-1-768x361.jpg 768 Вт, / /cdn.corporatefinanceinstitute.com/assets/Basic-Excel-Function-AutoSum-1-1200x564.jpg 1200 Вт, //cdn.Corporatefinanceinstitute.com/assets/Basic-Excel-Function-AutoSum-1-600x282.jpg 600 Вт, //cdn.corporatefinanceinstitute.com/assets/Basic-Excel-Function-AutoSum-1.jpg 1952 Вт" data-sizes="( максимальная ширина: 860px) 100vw, 860px">

5. Быстрая вставка: использование недавно использовавшихся вкладок

Если повторный ввод последней формулы кажется вам монотонной задачей, воспользуйтесь меню «Недавно использованные». Эта опция находится на вкладке «Формулы» — третьем пункте меню рядом с «Автосумма».

Бесплатное руководство по формулам Excel на YouTube

Посмотрите БЕСПЛАТНОЕ видео на YouTube от CFI, чтобы быстро изучить самые важные формулы Excel. Посмотрев видеодемонстрацию, вы быстро изучите самые важные формулы и функции.

Семь основных формул Excel для вашего рабочего процесса

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

1. СУММ

Функция СУММ Функция СУММ Функция СУММ относится к категории математических и тригонометрических функций. Функция суммирует ячейки, переданные в качестве нескольких аргументов. Это самая популярная и широко используемая функция в Excel. SUM помогает пользователям выполнять быстрое суммирование указанных ячеек в MS Excel. Например, нам дана стоимость 100 — это первая формула, которую необходимо знать в Excel. Обычно он объединяет значения из набора столбцов или строк из выбранного диапазона.

=СУММ(число1, [число2], …)

=SUM(B2:G2) — простой выбор, который суммирует значения строки.

=SUM(A2:A8) — простой выбор, который суммирует значения столбца.

=SUM(A2:A7, A9, A12:A15) – сложная коллекция, которая суммирует значения из диапазона от A2 до A7, пропускает A8, добавляет A9, пропускает A10 и A11, а затем, наконец, добавляет от A12 до A15.

=СУММ(A2:A8)/20 — показывает, что вы также можете преобразовать функцию в формулу.

2. СРЕДНЕЕ

Функция СРЗНАЧ Функция СРЗНАЧ Вычисляет среднее значение в Excel. Функция СРЗНАЧ относится к категории статистических функций. Он вернет среднее значение аргументов. Он используется для вычисления среднего арифметического заданного набора аргументов. Как финансовый аналитик, эта функция полезна для определения среднего числа чисел. должны напоминать вам о простых средних данных, таких как среднее количество акционеров в данном пуле акций.

=СРЗНАЧ(число1, [число2], …)

=AVERAGE(B2:B11) – показывает простое среднее значение, также похожее на (SUM(B2:B11)/10)

3. СЧЕТ

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

=COUNT(значение1, [значение2], …)

COUNT(A:A) – подсчитывает все числовые значения в столбце A. Однако для подсчета строк необходимо настроить диапазон внутри формулы.

COUNT(A1:C1) — теперь он может считать строки.

Курсы Excel

4. СЧЕТЧИК

Как и функция COUNT, функция COUNTA COUNTA Функция COUNTA вычисляет количество непустых ячеек в заданном наборе значений. Функцию =counta() также часто называют формулой Excel Countif Not Blank. Как финансовый аналитик, функция полезна для подсчета ячеек, которые не являются пустыми или пустыми в заданном диапазоне.подсчитывает все клетки в заданной ярости. Однако он считает все ячейки независимо от типа. То есть, в отличие от COUNT, который подсчитывает только числа, он также подсчитывает даты, время, строки, логические значения, ошибки, пустую строку или текст.

=COUNTA(значение1, [значение2], …)

COUNTA(C2:C13) – подсчитывает строки со 2 по 13 в столбце C независимо от типа. Однако, как и в случае с COUNT, вы не можете использовать одну и ту же формулу для подсчета строк. Вы должны внести коррективы в выделение внутри квадратных скобок — например, COUNTA(C2:H2) будет подсчитывать столбцы от C до H

Функция ЕСЛИ Функция ЕСЛИ Функция оператора ЕСЛИ в Excel проверяет заданное условие и возвращает одно значение для результата ИСТИНА, а другое — для результата ЛОЖЬ. Например, если общая сумма продаж превышает 5000 долларов, верните «Да» для бонуса, в противном случае верните «Нет». Мы также можем создавать вложенные операторы IF, которые часто используются, когда вы хотите отсортировать данные в соответствии с заданной логикой. Преимущество формулы ЕСЛИ в том, что в нее можно встраивать формулы и функции.

=IF(логическая_проверка, [значение_если_истина], [значение_если_ложь])

=IF(C2 SUM(D1:D10), SUM(C1:C10), SUM(D1:D10)) — пример сложной логики IF. Сначала он суммирует C1 и C10 и D1 и D10, а затем сравнивает сумму. Если сумма от C1 до C10 больше суммы от D1 до D10, тогда значение ячейки становится равным сумме от C1 до C10. В противном случае получается СУММА C1–C10.

6. ОБРЕЗАТЬ

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

=ОТРЕЗАТЬ(текст)

TRIM(A2) — удаляет пустые места в значении в ячейке A2.

7. МАКС. И МИН.

Функция MAX MAX Функция MAX относится к категории статистических функций Excel. MAX вернет наибольшее значение в заданном списке аргументов. Из заданного набора числовых значений он вернет наибольшее значение. В отличие от функции МАКС, функция МАКС подсчитывает числа, но игнорирует пустые ячейки и функцию МИН. МИН. Функция МИН. относится к категории статистических функций Excel. MIN вернет минимальное значение в заданном списке аргументов. Из заданного набора числовых значений он вернет наименьшее значение. В отличие от функции МИНА, функции помогают найти максимальное и минимальное число в диапазоне значений.

=MIN(число1, [число2], …)

=MIN(B2:C11) – находит минимальное число между столбцом B из B2 и столбцом C из C2 до строки 11 в обоих столбцах B и C.

=MAX(число1, [число2], …)

=MAX(B2:C11) — аналогичным образом находит максимальное число между столбцом B из B2 и столбцом C из C2 в строку 11 в обоих столбцах B и C.

Дополнительные ресурсы

Спасибо, что прочитали руководство CFI по основным формулам Excel.Чтобы продолжить свое развитие в качестве финансового аналитика мирового уровня Станьте сертифицированным аналитиком по финансовому моделированию и оценке (FMVA)® Сертификация CFI по финансовому моделированию и оценке (FMVA)® поможет вам обрести уверенность, необходимую в вашей финансовой карьере. Зарегистрируйтесь сегодня! , эти дополнительные ресурсы CFI будут полезны:

Усовершенствованные формулы Excel Усовершенствованные формулы Excel, которые необходимо знать Эти расширенные формулы Excel крайне важны для понимания и позволят вывести ваши навыки финансового анализа на новый уровень. Загрузите нашу бесплатную электронную книгу Excel!
Преимущества ярлыков Excel Обзор ярлыков Excel Ярлыки Excel — это недооцененный метод повышения производительности и скорости работы в Excel. Ярлыки Excel предлагают финансовому аналитику мощный инструмент. Эти ярлыки могут выполнять множество функций. так же просто, как навигация в электронной таблице для заполнения формул или группировки данных.
Список функций Excel Функции Список наиболее важных функций Excel для финансовых аналитиков. Эта шпаргалка охватывает сотни функций, которые важно знать аналитику Excel.
Моделирование оценки Excel Моделирование оценки в Excel Моделирование оценки в Excel может относиться к нескольким различным типам анализа, включая анализ дисконтированных денежных потоков (DCF), сопоставимые торговые мультипликаторы.

Бесплатное руководство по Excel

Чтобы овладеть искусством работы с Excel, ознакомьтесь с БЕСПЛАТНЫМ ускоренным курсом CFI по Excel. Основы Excel — формулы для финансов Вы ищете ускоренный курс по Excel? Получите бесплатное обучение Excel для карьеры в области корпоративных финансов и инвестиционно-банковской деятельности от Института корпоративных финансов. , который научит вас, как стать опытным пользователем Excel. Изучите самые важные формулы, функции и сочетания клавиш, чтобы уверенно проводить финансовый анализ.

Запустите бесплатный курс CFI по Excel прямо сейчас Основы Excel — формулы для финансов Вы ищете ускоренный курс Excel? Пройдите бесплатное обучение Excel, чтобы начать карьеру в сфере корпоративных финансов и инвестиционно-банковских услуг, от Института корпоративных финансов.

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

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

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

Формула в Excel – это выражение, которое возвращает определенный результат. Например:

Примечание. Все формулы в Excel должны начинаться со знака равенства (=).

Ссылки на ячейки

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

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

Обратите внимание, поскольку мы используем ссылки на ячейки для A1, A2 и A3, эти значения можно изменить в любое время, и C1 по-прежнему будет показывать точный результат.

Все формулы возвращают результат

Существуют разные способы обработки ошибок. В этом случае вы можете указать отсутствующее значение в ячейке B4 или «поймать» ошибку с помощью функции ЕСЛИОШИБКА и отобразить более понятное сообщение (или вообще ничего).

Копировать и вставлять формулы

Прелесть ссылок на ячейки заключается в том, что они автоматически обновляются при копировании формулы в новое место. Это означает, что вам не нужно вводить одну и ту же базовую формулу снова и снова. На приведенном ниже экране формула в E1 была скопирована в буфер обмена с помощью Control + C:

Ниже: формула вставлена в ячейку E2 с помощью Control + V. Обратите внимание, что ссылки на ячейки изменились:

Та же формула вставлена в E3. Адреса ячеек снова обновлены:

Относительные и абсолютные ссылки

Ссылки на ячейки выше называются относительными ссылками. Это означает, что ссылка относится к ячейке, в которой она находится. Формула в E1 выше:

Буквально это означает "ячейка 3 столбца слева" + "ячейка 2 столбца слева" + "ячейка 1 столбца слева". Вот почему, когда формула копируется в ячейку E2, она продолжает работать так же.

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

Например, на экране ниже мы хотим умножить каждое значение в столбце D на 10, которое введено в A1. Используя абсолютную ссылку для A1, мы «блокируем» эту ссылку, чтобы она не изменялась при копировании формулы в E2 и E3:

Вот окончательные формулы в E1, E2 и E3:

Обратите внимание, что ссылка на D1 обновляется при копировании формулы, но ссылка на A1 никогда не меняется. Теперь мы можем легко изменить значение в A1, и все три формулы пересчитаются. Ниже значение в A1 изменилось с 10 на 12:

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

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

Как ввести формулу

Чтобы ввести формулу:

Выберите ячейку
Введите знак равенства (=)
Введите формулу и нажмите клавишу ввода.

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

Все формулы в Excel должны начинаться со знака равенства (=). Без знака равенства, без формулы:

Как изменить формулу

Чтобы изменить формулу, у вас есть 3 варианта:

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

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

Что такое функция?

Работая в Excel, вы часто будете слышать слова "формула" и "функция", иногда взаимозаменяемые. Они тесно связаны, но не совсем одно и то же. Технически формула – это любое выражение, начинающееся со знака равенства (=).

Функция, с другой стороны, представляет собой формулу со специальным именем и назначением. В большинстве случаев функции имеют имена, отражающие их предполагаемое использование. Например, вы, вероятно, уже знаете функцию СУММ, которая возвращает сумму заданных ссылок:

Функция СРЗНАЧ, как и следовало ожидать, возвращает среднее значение заданных ссылок:

А функции MIN и MAX возвращают минимальное и максимальное значения соответственно:

Аргументы функции

Большинству функций требуются входные данные для возврата результата. Эти входные данные называются «аргументами». Аргументы функции появляются после имени функции в круглых скобках, разделенных запятыми.Все функции требуют совпадающих открывающих и закрывающих скобок (). Шаблон выглядит следующим образом:

Например, функция СЧЁТЕСЛИ подсчитывает ячейки, соответствующие критериям, и принимает два аргумента: диапазон и критерий:

На приведенном ниже экране диапазон — A1:A5, а критерий — «красный». Формула в C1:

Не все аргументы обязательны. Аргументы, показанные в квадратных скобках, являются необязательными. Например, функция YEARFRAC возвращает дробное количество лет между датой начала и датой окончания и принимает 3 аргумента:

Дата начала и дата окончания являются обязательными аргументами, база — необязательным аргументом. Ниже приведен пример использования YEARFRAC для расчета текущего возраста на основе даты рождения.

Как ввести функцию

Если вы знаете название функции, просто начните вводить его. Вот шаги:

<р>1. Введите знак равенства (=) и начните печатать. Excel будет отображать совпадающие функции по мере ввода:

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

<р>2. Нажмите клавишу Tab, чтобы принять функцию. Excel выполнит функцию:

<р>3. Заполните необходимые аргументы:

<р>4. Нажмите Enter, чтобы подтвердить формулу:

Объединение функций (вложенность)

Многие формулы Excel используют более одной функции, и функции могут быть "вложены" друг в друга. Например, ниже у нас есть дата рождения в ячейке B1, и мы хотим рассчитать текущий возраст в ячейке B2:

Функция YEARFRAC вычисляет годы с датой начала и датой окончания:

Мы можем использовать B1 для даты начала, а затем использовать функцию TODAY для указания даты окончания:

Когда мы нажимаем Enter для подтверждения, мы получаем текущий возраст на основе сегодняшней даты:

Обратите внимание, что мы используем функцию СЕГОДНЯ, чтобы передать дату окончания в функцию ДОЛЯ ГОДА. Другими словами, функция СЕГОДНЯ может быть вложена в функцию ДОЛЯ ГОДА для предоставления аргумента конечной даты. Мы можем сделать еще один шаг в формуле и использовать функцию INT, чтобы отсечь десятичное значение:

Здесь исходная формула ДОЛЯ ГОДА возвращает 20,4 в функцию ЦЕЛОЕ, а функция ЦЕЛОЕ возвращает окончательный результат 20.

Текущая дата на изображениях выше — 22 февраля 2019 года.
Вложенные функции ЕСЛИ — классический пример вложенных функций.
Функция СЕГОДНЯ — это редкая функция Excel без обязательных аргументов.

Ключевой вывод: выходные данные любой формулы или функции можно передать непосредственно в другую формулу или функцию.

Математические операторы

В таблице ниже показаны стандартные математические операторы, доступные в Excel:

< td>=9-2=7 < td>Возведение в степень

Символ	Операция	Пример
+	Сложение	=2+3=5
-	Вычитание
*	Умножение	=6*7=42
/	Подразделение	=9/3=3
^	=4^2=16
()	Скобки	=(2 +4)/3=2

Логические операторы

Логические операторы обеспечивают поддержку сравнений, таких как "больше", "меньше" и т. д. Логические операторы, доступные в Excel, показаны в таблице ниже:

Что дальше?

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

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

При этом в этой статье я познакомлю вас с одной из самых основных и самых полезных функций Microsoft Excel: формулами.

Как вводить формулы Excel

Все формулы Excel должны начинаться со знака равенства (=), за которым следует операция, которая будет использоваться для возврата значения. Если вы не начинаете формулу со знака равенства, Excel будет считать, что вы вводите текст, а не формулу.

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

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

Пример формулы Excel

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

Введите формулу =F13*G13 (цена книги, умноженная на количество книг, заказанных покупателем). Когда вы вводите формулу, во всплывающем окне будет появляться список функций Excel, предоставляя вам быстрый способ ввода функций (но мы рассмотрим функции в другой статье). Не волнуйтесь, список закроется, когда вы введете формулу.
После того как вы ввели формулу, нажмите клавишу «Ввод». Формула вводится в H13, после нажатия «Ввод» будет отображаться значение 25,90 долларов США. Результат отображается в валюте, поскольку ячейка F13 в формуле содержит значение в валюте.

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

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

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

При этом имейте в виду, что Excel сначала вычисляет значение любой операции в скобках, затем возведение в степень (^), умножение (*) и деление (/), и, наконец, сложение (+) и вычитание ( -).

Совет. Запомните аббревиатуру PEMDAS! Скобки, Возведение в степень, Умножение, Деление, Сложение, Вычитание.

Совет. Хотите узнать больше советов по Excel? Научитесь делить в Excel

Как редактировать формулы Excel

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

В этом примере я собираюсь изменить исходную формулу со сложения на умножение.

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

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

Теперь, когда вы отредактировали то, что нужно было изменить, нажмите «Ввод»!

Как копировать и вставлять формулы Excel

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

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

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

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

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

Выберите ячейку H13 (ячейка, содержащая исходную формулу, которую вы хотите скопировать).
Нажмите Ctrl+C, чтобы скопировать формулу в буфер обмена.
Выделите диапазон ячеек, в которые вы хотите вставить формулу, и нажмите Ctrl+V, чтобы вставить. Excel вставит формулу в ячейки и изменит каждую формулу, чтобы общие расходы рассчитывались на основе соответствующих значений в каждой строке (поскольку значения в каждой строке различаются).

Основные формулы и функции Excel

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

Но прежде чем я перейду к этому, что такое функция?

Функции Excel на самом деле представляют собой стандартные формулы, доступные пользователям.

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

Вы можете выполнить поиск, нажав "Вставить функцию", или просмотреть списки, нажав на каждую категорию отдельно.

В Excel доступны десятки полезных функций — вот 10 лучших функций Excel, которые вы должны начать использовать уже сегодня:

Функция СУММ

Функция СУММ (=СУММ) — это одна из математических и триггерных функций Excel, которая используется для добавления отдельных значений, ссылок на ячейки или диапазонов ячеек.

Функция СЧЁТ

Функция COUNT используется для подсчета общего количества записей в определенном диапазоне, содержащих числа в ячейке.

СРЕДНЯЯ функция

Функция СРЗНАЧ (=СРЗНАЧ) используется для вычисления среднего значения выбранного диапазона ячеек.

Функция ЕСЛИ

Функция ЕСЛИ (=ЕСЛИ) используется для логического сравнения значений с тем, что вы ожидаете.

Функция СУММЕСЛИ

Функция СУММЕСЛИ (=СУММЕСЛИ) используется для вычисления суммы значений в диапазоне, соответствующем заданным критериям.

СУММЕСЛИ используется для добавления значений, соответствующих определенным критериям. Например, допустим, вы хотите найти сумму всех значений больше 5 на вашем листе. Это ситуация, когда вы должны использовать СУММЕСЛИ.

Как это работает:

Функция СУММЕСЛИ требует указания диапазона, критерия и иногда диапазона суммы. Уравнение выглядит следующим образом:

Критерий — это условие, которое необходимо выполнить. Это может быть число, текст или выражение. Например, «Четверг», «Функция СЦЕПИТЬ (= СЦЕПИТЬ) — это одна из текстовых функций Excel, которая используется для объединения двух или более строк текста в одну строку. Эта функция будет работать одновременно с 30 строками текста.

Как это работает:

Функция СЦЕПИТЬ требует от вас указать ячейки, которые вы хотите объединить. Уравнение будет выглядеть так:

Каждый указанный вами "текст" будет из другой ячейки, например:

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

Максимальные и минимальные функции

Функция MAX (=MAX) используется для возврата наибольшего значения в наборе значений. Функция MIN (=MIN) используется для возврата наименьшего значения в наборе значений.

Советы по формулам Excel

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

Когда дело доходит до написания этих формул, помните об этих нескольких советах:

Сделайте свои формулы простыми

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

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

Проверьте свои формулы

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

Например, предположим, что вы только учитесь вычитать в Excel. Сначала используйте 1 или 10 в качестве входных значений — это легко вычислить в уме — чтобы убедиться, что формула введена правильно и работает должным образом.

Не скрывайте значения данных в формулах

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

Например, вы хотите рассчитать процентную ставку в размере 7 процентов для значения в B5. Вы можете ввести формулу =0,07*B5. Однако это не покажет, как рассчитывается значение. Вместо этого поместите значение 0,07 в ячейку с меткой и используйте ссылку на ячейку в формуле. Таким образом, если вы поместите 0,07 в ячейку B6, формула =B6*B5 рассчитает значение процента.

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

Пришло время освоить формулы Excel

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

Теперь возьмите свой компьютер и погрузитесь в Excel и попробуйте сами! Обязательно ознакомьтесь с этой статьей о ярлыках Excel!

Джордан Уол — бывший контент-менеджер в G2. Она имеет степень бакалавра делового администрирования в области маркетинга Университета Висконсин-Уайтуотер. Она любит все, что ставит ее в ее творческое пространство. включая письмо, искусство и музыку.

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