Формулы Excel & Функции: обучение на базовых примерах

Содержание:

Anonim

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

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

  • Что такое формулы в Excel?
  • Ошибки, которых следует избегать при работе с формулами в Excel
  • Что такое функция в Excel?
  • Важность функций
  • Общие функции
  • Числовые функции
  • Строковые функции
  • Дата и время функции
  • V Функция поиска

Данные учебников

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

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

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

График строительства дома

S / N ЭЛЕМЕНТ ДАТА НАЧАЛА ДАТА ОКОНЧАНИЯ ПРОДОЛЖИТЕЛЬНОСТЬ (ДНЕЙ)
1 Обследование земли 02.04.2015 02.07.2015
2 Lay Foundation 02.10.2015 15.02.2015
3 Кровля 27.02.2015 03.03.2015
4 Картина 03.09.2015 21.03.2015

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

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

=A2 * D2 / 2

ЗДЕСЬ,

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

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

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

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

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

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

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

=C4*D4

ЗДЕСЬ,

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

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

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

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

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

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

Ознакомьтесь с 10 лучшими формулами электронных таблиц Excel

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

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

  • СУММА для суммирования диапазона чисел
  • СРЕДНИЙ для вычисления среднего значения заданного диапазона чисел
  • COUNT для подсчета количества элементов в заданном диапазоне

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

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

= E4 + E5 + E6 + E7 + E8

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

=SUM (E4:E8)

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

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

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

S / N НАЗНАЧЕНИЕ КАТЕГОРИЯ ОПИСАНИЕ ИСПОЛЬЗОВАНИЕ
01 СУММ Математика и триггер Складывает все значения в диапазоне ячеек = СУММ (E4: E8)
02 MIN Статистическая Находит минимальное значение в диапазоне ячеек = МИН (E4: E8)
03 МАКСИМУМ Статистическая Находит максимальное значение в диапазоне ячеек = МАКС (E4: E8)
04 СРЕДНИЙ Статистическая Вычисляет среднее значение в диапазоне ячеек. = СРЕДНИЙ (E4: E8)
05 СЧИТАТЬ Статистическая Подсчитывает количество ячеек в диапазоне ячеек = СЧЁТ (E4: E8)
06 LEN Текст Возвращает количество символов в текстовой строке. = LEN (B7)
07 СУММЕСЛИ Математика и триггер Складывает все значения в диапазоне ячеек, которые соответствуют указанным критериям. = СУММЕСЛИ (диапазон; критерий; [диапазон_суммы]) = СУММЕСЛИ (D4: D8; "> = 1000"; C4: C8)
08 СРЕДНИЙ Статистическая Вычисляет среднее значение в диапазоне ячеек, соответствующих указанным критериям. = СРЗНАЧЕСЛИ (диапазон; критерии; [средний_ диапазон]) = СРЗНАЧЕСЛИ (F4: F8; «Да»; E4: E8)
09 ДНЕЙ Дата и время Возвращает количество дней между двумя датами. = ДНИ (D4; C4)
10 СЕЙЧАС ЖЕ Дата и время Возвращает текущую системную дату и время. = СЕЙЧАС ()

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

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

S / N НАЗНАЧЕНИЕ КАТЕГОРИЯ ОПИСАНИЕ ИСПОЛЬЗОВАНИЕ
1 ISNUMBER Информация Возвращает True, если предоставленное значение является числовым, и False, если оно не числовое. = ЕЧИСЛО (A3)
2 RAND Математика и триггер Создает случайное число от 0 до 1 = СЛЧИС ()
3 КРУГЛЫЙ Математика и триггер Округляет десятичное значение до указанного количества десятичных знаков. = ОКРУГЛ (3,14455,2)
4 МЕДИАНА Статистическая Возвращает число в середине набора заданных чисел. = МЕДИАНА (3,4,5,2,5)
5 ЧИСЛО ПИ Математика и триггер Возвращает значение математической функции PI (π). = PI ()
6 МОЩНОСТЬ Математика и триггер Возвращает результат возведения числа в степень. МОЩНОСТЬ (число; мощность) = МОЩНОСТЬ (2,4)
7 MOD Математика и триггер Возвращает остаток при делении двух чисел. = МОД (10,3)
8 РИМСКИЙ Математика и триггер Преобразует число в римские цифры. = РИМСКИЙ (1984)

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

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

S / N НАЗНАЧЕНИЕ КАТЕГОРИЯ ОПИСАНИЕ ИСПОЛЬЗОВАНИЕ КОММЕНТАРИЙ
1 ОСТАВИЛИ Текст Возвращает количество указанных символов с начала (слева) строки. = ЛЕВЫЙ («ГУРУ99»; 4) Осталось 4 символа "GURU99"
2 ВЕРНО Текст Возвращает количество указанных символов из конца (правой части) строки = ВПРАВО ("GURU99"; 2) Справа 2 персонажа "GURU99"
3 MID Текст Извлекает количество символов из середины строки из указанной начальной позиции и длины. = MID (текст; начальное_число; число_знаков) = MID ("GURU99"; 2,3) Получение символов от 2 до 5
4 ISTEXT Информация Возвращает True, если предоставленный параметр - Text. = ISTEXT (значение) значение - значение для проверки.
5 НАЙТИ Текст Возвращает начальную позицию текстовой строки в другой текстовой строке. Эта функция чувствительна к регистру. = НАЙТИ (найти_текст; внутри_текста; [начальное_число]) = НАЙТИ ("oo"; "Кровля"; 1) Находим oo в «Кровля», Результат 2
6 ЗАМЕНЯТЬ Текст Заменяет часть строки другой указанной строкой. = ЗАМЕНИТЬ (старый_текст, начальное_число, число_символов, новый_текст) = REPLACE («Кровля»; 2,2; «xx») Заменить «oo» на «xx»

Дата Время Функции

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

S / N НАЗНАЧЕНИЕ КАТЕГОРИЯ ОПИСАНИЕ ИСПОЛЬЗОВАНИЕ
1 ДАТА Дата и время Возвращает число, представляющее дату в коде Excel. = ДАТА (2015; 2; 4)
2 ДНЕЙ Дата и время Найдите количество дней между двумя датами = ДНИ (D6; C6)
3 МЕСЯЦ Дата и время Возвращает месяц из значения даты. = МЕСЯЦ ("2 апреля 2015 г.")
4 МИНУТА Дата и время Возвращает минуты из значения времени. = МИНУТА ("12:31")
5 ГОД Дата и время Возвращает год из значения даты. = ГОД ("02.04.2015")

Функция ВПР

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

=VLOOKUP (C12, A4:B8, 2, FALSE)

ЗДЕСЬ,

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

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

Загрузите приведенный выше код Excel

Резюме

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

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

  • СУММ (функция СУММ) = =SUM(E4:E8)
  • МИН функция = =MIN(E4:E8)
  • Функция МАКС = =MAX(E4:E8)
  • СРЕДНИЙ функция = =AVERAGE(E4:E8)
  • Функция СЧЁТ = =COUNT(E4:E8)
  • ДНЕЙ функция = =DAYS(D4,C4)
  • Функция ВПР = =VLOOKUP (C12, A4:B8, 2, FALSE)
  • ДАТА функция = =DATE(2020,2,4)