Adelitusn.ru

ПК и Техника
1 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Применение встроенных функций Excel

Применение встроенных функций Excel.

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

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

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

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

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

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

Встроенные функции Excel делятся на следующие категории:

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

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

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

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

Функции просмотра и ссылок позволяет "просматривать" информацию, хранящуюся в списке или таблице, а также обрабатывать ссылки.

Каждая функция состоит из трех обязательных элементов:

1. Знак равенства (=). Признак того, что в данную ячейку введена функция или формула, а не данные какого-либо другого типа (например, текстовый фрагмент, числовое значение или дата).

2. Имя функции. Характеризует тип выполняемых операций, например СУММ (суммировать), СРЗНАЧ (среднее значение), ПЛТ (платеж) и т.д.

3. Аргументы. Значения, на основе которых выполняются вычисления. Аргументы вводят в круглых скобках сразу после имени функции. Как правило, это адреса ячеек, значения которых используются в вычислениях. Например, адрес диапазона ячеек A5:D23. Аргументы могут быть и более сложными; это зависит от назначения функции.

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

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

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

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

1. Активизировать ячейку, в которую необходимо вставить функцию.

2. Щелкнуть на кнопке “Вставить функцию” в строке формул.

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

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

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

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

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

5. Щелкнуть на кнопке ОК или нажмите клавишу Enter .

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

Для того чтобы изменить аргументы ранее веденной функции, щелкните на ячейке, в которой эта функция находится. Затем щелкните на кнопке Вставить функцию. Появится диалоговое окно Аргументы функции с перечнем прежних значений аргументов. Введите новые значения аргументов и щелкните мышью на кнопке ОК.
Некоторые функции, такие как СУММ (SUM), SIN (SIN) и ФАКТР (FACT), являются эквивалентами длинных математических формул, которые можно создать самим. Другие функции, такие как ЕСЛИ (IF) и ВПР (VLOOKUP), в виде формул реализовать невозможно.

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

Быстро получить информацию о функциях можно также с помощью кнопки Вставка функции.

Читайте так же:
Драйвера для Xerox Phaser 3140

Некоторые функции, такие как ПИ (PI) и ИСТИНА (TRUE), не имеют аргументов. Даже если функция не имеет аргументов, она все равно должна содержать круглые скобки.

При использовании в функции нескольких аргументов они отделяются один от другого точкой с запятой. Например, следующая формула указывает Excel, что необходимо перемножить числа в ячейках С1, С2 и С5:

=ПРОИЗВЕД(С1;С2;С5).

В функции можно использовать до 30 аргументов, если при этом общая длина формулы не превосходит 1024 символов. Однако любой аргумент может быть диапазоном, содержащим произвольное число ячеек листа. Например, следующая функция имеет три аргумента, но суммирует числа в 29 ячейках (первый аргумент, А1:А5, ссылается на диапазон пяти ячеек от А1 до А5 и т.д.):

=CУMM(A1:A5;C2:C10;D3:D17).

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

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

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

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

Аргументы функции могут быть числовыми. Например, функция СУММ в следующей формуле суммирует числа 327, 209 и 176:

=СУММ(327;209;176).

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

В качестве аргумента функции могут использоваться текстовые значения. Например:

=ТЕКСТ(ТДАТА();«Д МММ ГГГГ»).

В этой формуле второй аргумент функции ТЕКСТ «Д МММ ГГГГ», является текстовым и задает шаблон для преобразования десятичного значения даты, возвращаемого функцией ТДАТА(), в строку символов. Текстовый аргумент может быть строкой символов, заключенной в двойные кавычки, или ссылкой на ячейку, которая содержит текст.

Аргументы ряда функций могут принимать только логические значения ИСТИНА (TRUE) или ЛОЖЬ (FALSE). Логическое выражение возвращает значение ИСТИНА или ЛОЖЬ в ячейку или формулу, содержащую это выражение. Например, первый аргумент функции ЕСЛИ (IF) в следующей формуле является логическим выражением, которое использует значение:

=ЕСЛИ(А1=ИСТИНА, «Новая», «Старая»)& «аппаратура».

Если значение в ячейке А1 равно ИСТИНА, то выражение А1=ИСТИНА возвращает значение ИСТИНА, и функция ЕСЛИ возвращает строку Новая, а формула в целом возвращает текстовое значение Новая аппаратура.

В качестве аргумента функции можно указать имя диапазона. Например, если выбрать команду Присвоить подменю Имя меню Вставка и назначить диапазону С3:С6 имя Получено, то для вычисления суммы чисел в ячейках С3, С4, С5 и С6 можно использовать формулу:

=СУММ(Получено).

Аргументом функции может быть массив. Некоторые функции, такие как ТЕНДЕНЦИЯ (TREND) и ТРАНСП (TRANSPOSE) требуют задания массива аргументов. Другие функции не требуют задания массива, но могут использовать такие аргументы. Массивы могут содержать числовые, текстовые или логические значения.

В одной функции можно использовать аргументы различных типов. Например, в следующей формуле аргументами являются имя диапазона (Группа 1), ссылка на ячейку (A3) и числовое выражение (5*3), а сама формула возвращает единственное числовое значение:

=СРЗНАЧ(Группа1;А3;5*3).

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

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

Базовые функции Excel для работы

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

Исходный файл (скачать CSV-файл, 1.5 КБ)

В качестве исходных данных рассмотрим файл типа «Распределение» в котором собраны продвигаемые поисковые запросы с указанием (Рис. 1):

  • Продвигаемого URL
  • Релевантного URL
  • Позиции в Яндексе
  • Частоты
  • Позиции в Google
  • Недостающих слов в теге Title
  • Прочих

Рис. 1. Исходная таблица для работы.

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

Сортировка по любому полю

Для этой операции будет достаточно преобразовать рабочую область таблицу с заголовками (Рис. 2). После чего будет доступна сортировка по любому из полей (Рис. 3) при нажатии на квадратик со стрелочкой справа от названия колонки.

Рис. 2. Вставка таблицы с заголовками в Excel файл для дальнейшей работы.

Рис. 3. Сортировка текстовых полей от «А до Я» и от «Я до А» в таблице в Excel. Для численных полей доступна сортировка от минимального к максимальному значению и наоборот.

Выделение дублей или уникальных значений

Часто, поисковые запросы в таблице могут дублировать друг друга или наоборот, вам требуется найти все уникальные запросы, чтобы сравнить два списка. Для этого пригодится функция «Условное форматирование» * (Рис. 4) и создание нового правила для неё. Прежде чем нажать на кнопку «Условное форматирование» требуется выделить область, с которой будет происходить дальнейшая работа по выделению/форматированию значений. В нашем случае, выделена первая колонка целиком.

Рис. 4. Создание нового правила для условного форматирования выделенной области.

После, выбираете «Форматировать только уникальные или повторяющие значения», задаете тип, на примере это «Повторяющиеся» и Формат, на примере это оранжевый цвет (Рис. 5).

Рис. 5. Задание оранжевого цвета для форматирования повторяющихся значений в выделенной области.

Удаление повторяющихся значений

После применения правила повторяющиеся значения в выделенной области будут подсвечены оранжевым цветом (Рис. 6). По данному цвету можно осуществить сортировку в таблице и проработать или удалить данные строчки.

Читайте так же:
Как задавать вопросы покупателям на алиэкспресс?

Рис. 6. Удаление повторяющегося ключевого запроса после сортировки по оранжевому цвету в таблице.

Выделение цветов значений в диапазоне

Для цветового выделения значений в заданном диапазоне также удобным оказывается применение условного форматирования. Для этого требуется выделить интересующие нас колонки или ячейки и создать новое правило для функции «Условное форматирование», далее выбрать «Форматировать только ячейки, которые содержат» и задать значения ячейки в требуемом диапазоне, на примере это от 1 до 10 (Рис. 7).

Рис. 7. Задание форматирования зеленых цветом для ячеек между 1 и 10 через функцию условного форматирования.

Далее, ячейки в заданном диапазоне будут выделены нужным цветом (зеленым), что упрощаем визуальное восприятие таблицы (Рис. 8).

Рис. 8. Пример выделения в таблице нужных ячеек с позициями в ТОП-10 зеленым цветом.

Поиск запросов с заданным словом

Часто, требуется быстро найти и выделить все запросы, в которых содержится заданное слово, скажем, слово «сайт». Для этого аналогично можно использовать функцию условного форматирования с заданием формата для ячеек, которые содержат текст «сайт» (Рис. 9).

Рис. 9. Пример быстрого поиска и работы с поисковыми запросами, в которых содержится слово «сайт».

Расчет значения по формуле

В таблице также удобным оказывается производить расчёт какого-либо показателя по формуле, опираясь на значения в других показателей. В частности, можно вычислить прогнозируемый бюджет как среднее значение между бюджетом из системы SeoPult и MegaIndex (Рис. 10). Для этого достаточно задать формулу для первой ячейки таблицы и значение вычиститься для всей таблицы.

Рис. 10. Расчёт ссылочного бюджета, в таблице Excel опираясь на значения от агрегаторов SeoPult и MegaIndex.

Копирование значений из колонки, вычисленной по формуле

Если вы заходите теперь скопировать на другой лист или в другой файл значения из вычисляемой колонки «На ссылки», то столкнетесь с небольшими трудностями. Так как значения вычисляются по формуле, которая «забита» в ячейке, то простое копирование CTRL+C и CTRL+V окажется некорректным (скопируется именно формула, а не числа) и вам потребуется использовать функцию «Специальная вставка». Пошагово это выглядит так (Рис. 11):

  1. Выделяете значения, которые вам требуется скопировать мышкой.
  2. Нажимаете CTRL+C.
  3. Далее выбираете ячейку, начиная с которой вы планируете осуществить вставку.
  4. Нажимаете правку кнопку мышки.
  5. Выбираете «Специальная вставка».
  6. Задаете «Вставить значения».

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

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

Сравнение значений в двух столбцах

Для понимания, совпадает ли продвигаемая и релевантная в выдаче страница (и ряда других задач), требуется использовать логическую функцию «ЕСЛИ». Требуется добавить колонку сравнения «Совпадает ли?» в таблицу и вставить в первую ячейку данной колонки функцию, следующей последовательностью действий: «Формулы», далее «Логические», далее «ЕСЛИ» (Рис. 12). Задать логическое выражение, скажем [@[ ПРОДВИГАЕМЫЙ URL]]=[@[ РЕЛЕВАНТНЫЙ В Я]]» и значения функции: «1» и «0». Чтобы ускорить процесс, можно сразу вставить в столбец функцию:

=ЕСЛИ([@[ ПРОДВИГАЕМЫЙ URL]]=[@[ РЕЛЕВАНТНЫЙ В Я]];1;0)

Рис. 12. Вызов функции логического «ЕСЛИ» в Excel для сравнения значений в двух столбцах.

После нажатия на кнопку «OK» столбец заполнится значениями «0» (если страницы не совпадают) и «1», если значения совпадают. Это позволит быстро найти все запросы, по которым релевантный и продвигаемый документ не совпадают, и начать анализ возможных причин данного поведения.

Использование формул: среднее значение и сумма значений в ячейках

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

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

Рис. 13. Выбор ячейки и вставка нужной математической функции ячейку.

После поиска нужной функции, требуется задать аргументы (значения с которыми будет работать функция) и нажать «OK». Если вы всё сделали верно, то значение будет вычислено и вставлено автоматически. Примеры вставки функций среднего значения (Рис. 14) и суммы значений (Рис. 15) представлены на иллюстрациях ниже.

Рис. 14. Вставка функции вычисления среднего значения ячеек для колонки «ЯНДЕКС».

Рис. 15. Вставка математической функции «Автосумма» для быстрого вычисления суммы значений в колонке.

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

  • Поиск максимального и минимального значения в колонке.
  • Использование логических операторов: «И», «ИЛИ», «ЕСЛИ», «НЕ».
  • Работы с датой и временем, вывод текущей даты по календарю.
  • Сумма, сумма значений с условием, медиана.

Задание формата ячеек

Для задания требуемого формата ячеек (числового, денежного, финансового, временного, процентного, текстового и т.д.) достаточно использовать функцию «Формат ячеек», предварительно выделив интересующую область форматирования и нажав правую кнопку мыши (Рис. 16), во всплывающем модальном окне нажать «Формат ячеек…».

Рис. 16. Пример вызова функции «Форма ячеек» для выделенной области.

После указания нужного формата значений в ячейках, нажмите «OK» (Рис. 17) и выбранный формат будет применен в выделенной области. С помощью данной функции можно избавиться от принудительного превращения некоторых значений в формат даты в Excel и задать наиболее наглядный и подходящий формат для данных (скажем, выводить вместо 0,1 → 10%, добавить разрядку групп разрядов у больших значений 340339493 → 340 339 493, скрыть лишние знаки после запятой 5,100015 → 5,1).

Читайте так же:
Как сохранить видео в Camtasia Studio

Рис. 17. Задание двух различных форматов (числовой и процентный) для двух соседних колонок.

Фиксация положения одной из ячеек в формуле

Если вам требуется зафиксировать положение (ячейку) для одной из переменных в формуле, то требуется просто заменить в самой формуле значение вида =F2 на значение =$F$2 (вставить знак доллара). После чего, вы сможете «протягивать» формулы для всей строки или столбца с фиксацией одной из переменный (ячеек). Пример использования:

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

Встроенные математические функции Excel (Эксель)

Встроенные математические функции Excel (Эксель)Самые простые математические функции в Эксель это сложение, вычитание, умножение и деление. Для этого нам понадобится знак «+» и функция =СУММ. При написании формулы в ячейках Excel (Эксель) мы используем знак «=». Пример: в любой ячейке пишем =5+5, нажимаем Ввод и получаем результат. Программа посчитала за нас.
Можно проделать это, используя связь ячеек. Пример: если в отдельной ячейке написать формулу =СУММ и выделить 2 ячейки с числами, мы получим их сумму.

Встроенные математические функции Excel (Эксель)

Встроенные математические функции Excel (Эксель)

Встроенные математические функции Excel (Эксель)

Умножение похоже на обе, вышеперечисленные функции. Только знака умножения выступает звездочка (*).

Встроенные математические функции Excel (Эксель)

Встроенные математические функции Excel (Эксель)

Встроенные математические функции Excel (Эксель)

Встроенные математические функции Excel (Эксель)

В ячейке пишем =МИН(выделяем диапазон чисел) и нажимаем Ввод
Из примера следует, что из списка стоимости абонементов в тренажерном зале минимальная – 80.
Функция МАКС выполняет противоположное действие, находит наибольшее значение в таблице.

Встроенные математические функции Excel (Эксель)

Встроенные математические функции Excel (Эксель)

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

Встроенные математические функции Excel (Эксель)

Функция ЕСЛИ часто является востребованной в любой сфере бизнеса. Рассмотрим пример с рестораном и планом продаж официанта. Допустим официанту ставиться план по продаже продукции на сумму 70000. В первом случае он не достиг этой отметки и заработал для заведения 60000. Сейчас мы узнаем, получит он бонус или нет.
В нужной ячейке пишем формулу =ЕСЛИ(B2>C2;D2;0), где знак «>» — означает «больше, чем», B2 – это заработанные официантом средства, С2 – план, D2 – бонус, если официант выполнил план и 0 – если не выполнил.

Встроенные математические функции Excel (Эксель)

Встроенные математические функции Excel (Эксель)

Встроенные математические функции Excel (Эксель)

Встроенные математические функции Excel (Эксель)

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

Функция ОКРУГЛ
Достаточно проста в использовании, и популярная функция. Главная ее особенность — это округление значений согласно всем математическим правилам, то есть, если число от 1 до 4 то, функция ОКРУГЛ будет округлять результат до 0 вниз, а если число от 5 до 9, то округление будет производиться вверх до 10. Эта функция очень нужна, когда результат очень важен и каждая копеечка на счету, особенно это касается работы в бухгалтерском учёте. Очень часто возникает цепочка вычислений, когда число после запятой уходит в бесконечность, а вам нужно только два знака после запятой для обозначения копеек, в случае, когда вам такие значения нужно суммировать может оказаться что потеряны копейки в вычислениях. Они конечно не потеряны, а просто скрыты, но от этого вам легче не будет, а вот эта функция поможет вам в промежуточных вычислениях получить правильные результаты и как результат получить точные цифры и меньше головной боли.

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ
Одна из интересных функций, так как ее аналог — это итоги при форматировании из простой таблицы в умную. Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ позволит вам найти промежуточные итоги по нужному вами критерию в указанном диапазоне. Но, увы, диапазон для подсчёта промежуточных итогов должен быть только вертикальным, и поскольку большинство таблиц вертикальные это не является проблемой. Функция функционально может подводить итог по 11 формулах. Что очень расширяет ее границы использования, это и среднее значение, и сумма, и минимальное с максимальным значениями, и отклонения, и многие другие полезные функции. А также данная функция может игнорировать вложенные итоги, чтобы избежать двойного суммирования.

Функция СЛУЧМЕЖДУ
Функция СЛУЧМЕЖДУ позволит вам случайным образом получить любое число в заданных диапазонах значений. Генерация таких чисел позволит вам тестировать формулы, заполнять диапазоны случайными числами, формировать списки вопросников, да и многое другое. Применение функции достаточно специфично и не всегда ее можно использовать, но вот при проработке новых форм и заполнений диапазонов случайными числами, это отличный инструмент, хотя для многих она хорошо и в других возможностях.

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

Функция СУММЕСЛИ
Это также одна из самых полезных функций в MS Excel. Пользуются этой функцией в основном опытные пользователи, так как она соединяет в себе логическую функцию ЕСЛИ и математическую СУММ, а вот такую полезную смесь добавили в раздел «Математические функции». В этом заключается ее сложность, это соединение двух начал, но это только на первый взгляд так кажется, сама по себе функция очень проста, удобна и полезна. Необычайно полезной станет при проведении анализа, когда с массы данных нужно произвести суммирование ваших данных по нужному критерию, выбрать данные за месяц, по определённому контрагенту и прочее. Функция СУММЕСЛИ также является одной из самых полезных и нужных инструментов при работе с данными в бухгалтерской и экономической деятельности, особенно в последнем случае.

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

Читайте так же:
Если после установки Windows 10 не работает

Функция СУММПРОИЗВ
Это еще одна из топовых функций в арсенале MS Excel, это функция СУММПРОИЗВ. Эта функция является благодатью для экономистов, так как она совмещает в себе возможности таких функций как СУММ, ЕСЛИ, СУММЕСЛИ и СУММЕСЛИМН и является мощным инструментом при работе с диапазонами значений и массивами. И я замечу, что одновременная работа с 255 массивами, это очень сильно и мощно. Если вам функция СУММПРОИЗВ еще неизвестна, то я советую вам ее более детально изучить, так ее возможности помогут вам увеличить собственную производительность.


Функция ОТБР

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

Еще одна из функций округления, это функция ОКРУГЛВВЕРХ. Как вы уже догадались, эта функция всегда округляет до ближайшего большого числа по модулю, то есть если у вас 12, то округление будет 20, если в условиях формулы стоят другие критерия, то будет округлять и к 100 и к 1000 и так далее. Я частенько ее использую, когда есть необходимость плановые расчёты вести в тысячах и все мои полученные данные аккуратно округляются к ближайшей тысяче, что делает данные наглядными и красивыми, отнюдь не влияя негативно на полученный результат.

Следующая наша функция, это будет функция ОКРУГЛВВЕРХ, является антиподом предыдущей функции нашего списка ТОП 10 математических функций. Как видно из самого названия основное отличие ее это в вертикальной направленности округления, то было вверх, а эта функция округляет вниз. Ее применение также порадует экономистов или других специалистов финансового сектора, которые не нуждаются в абсолютно точных значениях, а существует необходимость их округления, в этом случае функция ОКРУГЛВНИЗ поможет привести ваш отчёт в красивый и функциональный документ.

СУММ
СУММ(Диапазон1[; Диапазон2; . ])
Вычисляет сумму содержимого ячеек указанного диапазона или нескольких диапазонов.

СУММПРОИЗВ
СУММПРОИЗВ(Диапазон1; Диапазон2; . Диапазон k)
Вычисляет сумму произведений указанных диапазонов. Количество элементов во всех диапазонах должно быть одинаковым.
Пример:
СУММПРОИЗВ(C2:C6;D2:D6)

ПРОИЗВЕД
ПРОИЗВЕД(Диапазон1; Диапазон2; . Диапазон k)
Вычисляет произведение значений указанных диапазонов.
Пример:
ПРОИЗВЕД(B3:B7)

ОКРУГЛ
ОКРУГЛ(Выражение, Разрядов)
Возвращает значение, полученное путем округления числа до указанного количества разрядов после запятой.
Примеры:
ОКРУГЛ(B11;2)
ОКРУГЛ(СУММ(В4:B10);2)
Округление выполняется в соответствии с известным правилом: если значение отбрасываемого разряда больше или равно пяти, то округление выполняется путем добавления единицы в последний значащий разряд, в противном случае незначащие разряды отбрасываются.
Примеры округления чисел

ОКРВВЕРХ
ОКРВВЕРХ(Выражение; Точность)
Возвращает значение, полученное в результате округления числа в сторону увеличения с указанной точностью. Например, при округлении до десятков разряд единиц в числе обнуляется, а число десятков увеличивается на единицу (если разряд единиц исходного числа не был равен нулю).
Примеры округления чисел в сторону увеличения
Функция Значение
=ОКРВВЕРХ(351;10) 360
=ОКРВВЕРХ(353;100) 400
=ОКРВВЕРХ(125300;1000) 126000
ОКРВНИЗ
ОКРВНИЗ(Выражение; Точность)
Возвращает значение, полученное в результате округления числа в сторону уменьшения с указанной точностью. Например, при округлении до десятков разряд единиц в числе обнуляется.
Примеры округления чисел в сторону уменьшения
Функция Значение
=ОКРВНИЗ(351;10) 350
=ОКРВНИЗ(353;100) 300
=ОКРВНИЗ(125300;1000) 125000
ОСТАТ
ОСТАТ(Делимое; Делитель)
Вычисляет остаток от деления одного числа на другое.

Формула для выделения дробной части числа, находящегося в ячейке:
=ОСТАТ(A1;ЕСЛИ(A1

Математические функции в Excel: особенности и примеры

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

Excel: общие сведения

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

How dangerous is the new coronavirus? You will be interested: How dangerous is the new coronavirus?

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

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

использование математических функций в excel

Основа рабочего листа – таблица, состоящая из строк и столбцов. Их перекрестие составляет ячейка, куда вводятся данные или формулы. Строки названы арабскими цифрами, а столбцы – латинскими буквами. Считалось, что рабочий лист бесконечен в обе стороны, однако это не так. Он содержит 65536 строк и 256 столбцов. По другим данным в рабочем листе содержатся 16384 столбцов и 1048576 строк. Каждой ячейке присваивается уникальный адрес:А5.

Использование ссылок

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

  • простые;
  • ссылки на другой лист;
  • абсолютные;
  • относительные ссылки.

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

  • пересечение столбца и строки (А4);
  • массив ячеек по столбцу А со строки 5 до 20 (А5:А20);
  • диапазон клеток по строке 5 со столбца В до R (В5:R5);
  • все ячейки строки (10:10);
  • все клетки в диапазоне с 10 по 15 строку (10:15);
  • по аналогии обозначаются и столбцы: В:В, В:К;
  • все ячейки диапазона с А5 до С4 (А5:С4).
Читайте так же:
Использование скрытой копии в Outlook

Следующий формат адресов: ссылки на другой лист. Оформляется это следующим образом: Лист2!А4:С6. Подобный адрес вставляется в любую функцию.

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

Особого внимания требуют такие форматы адресов.

  • абсолютные;
  • относительные;
  • смешанные ссылки.

Под относительными адресами понимают указанные диапазоны или конкретные ячейки, которые при копировании формулы и ее последующей вставке изменяются автоматически. К примеру, необходимо суммировать в столбце С несколько значений. Формула будет выглядеть следующим образом: =сумм(С5:С9). Если допустить, что подобных столбцов несколько, и в каждом нужно найти сумму, проще скопировать формулу и вставить в нужные клетки. После проделанных манипуляций можно заметить, что диапазоны поменялись автоматически.

Под абсолютными ссылками понимаются ячейки, которые в процессе копирования не меняют своего вида. К примеру, имеется клетка А5. Она участвует в формуле вычисления суммы нескольких значений. Чтобы она не изменилась ни при одном копировании, перед обозначением столбца и строки ставят знак $. Абсолютная ссылка будет выглядеть следующим образом: $А$5.

Основные математические функции Excel предпочитают использовать при больших объемах данных смешанные адреса. В таком формате можно зафиксировать только столбец или строку. К примеру, $С5 или С$5. В первом случае не меняется название столбца, во втором – строки.

Ссылки формата R1C1

В новых версиях Excel адреса ячеек видоизменились. Некоторые не могут понять, в чем разница между А1 и R[-1]C[-5].

Разработчики приводят несколько примеров подобных адресов:

  • относительный адрес строки, расположенной на 3 позиции выше указанной: R[-3];
  • абсолютная ссылка на ячейку, содержащуюся на 10-й строке 10-го столбца: R10C10;
  • относительный адрес на ячейку, расположенную на 5 позиций выше в активном столбце (где прописывается формула): R[-5]C;
  • абсолютная ссылка на текущую клетку, где прописывается формула: R;
  • относительная ссылка на ячейку, расположенную на 8 строк правее и 5 строк ниже активной: R[5]C[8].

Категории математических функций

При выпадающем списке в программе на вкладке «Формулы» содержится 11 групп. В их число входят и математические функции в Excel, которые условно разделяются на следующие категории. Рассмотрим основные. Арифметические операции:

  • СУММ: складывает необходимые значения.
  • ПРОИЗВЕД: находит произведение заданных чисел или содержимого ячеек.
  • ЦЕЛОЕ: необходимо для нахождения целой части.
  • СТЕПЕНЬ: возводит число в заданную степень.
  • КОРЕНЬ: извлекает корень из содержимого ячейки или заданного вручную числа и т. д.

Также можно выделить тригонометрические функции:

  • SIN: находит синус от заданного значения.
  • ASIN: необходима для вычисления арксинуса числа.
  • LN: находит натуральный логарифм.
  • EXP: возводит число Е в заданную пользователем степень и др.

Округление по различным критериям:

  • ОКРВВЕРХ: округляет значение до ближайшего целого.
  • ОКРУГЛВВЕРХ: находит число, округленное до ближайшего целого по модулю.
  • НЕЧЕТ: функция, позволяющая округлить до нечетного числа. Положительное – в большую сторону, отрицательное – в меньшую.
  • ОКРУГЛ: необходима для нахождения результата округления с заданием количества десятичных разрядов и др.

Для работы с векторами и матрицами:

  • СУММПРОИЗВ: функция, необходимая для возвращения суммы массивов.
  • СУММВРАЗН: при наличии 2 диапазонов возвращает сумму квадратов разностей.
  • МОБР: функция, необходимая для получения обратной матрицы.
  • МОПРЕД: требуется для нахождения определителя матрицы и т. д.

Применение математических функций

Математические функции в Excel могут вводиться тремя способами:

  • вручную;
  • через панель инструментов;
  • посредством диалогового окна «Вставка функции».

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

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

Чтобы воспользоваться третьим вариантом, нужно нажать кнопку Fx или CTRL+F3. На панели инструментов также имеется «Мастер подстановки» на вкладке «Формулы». Послы выполнения одного из перечисленных действий появляется окно, в котором нужно выбрать соответствующую группу, а после – нужную функцию. Далее последует окно «Аргументы и функции», в котором выполняются необходимые манипуляции.

встроенные математические функции в excel

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

Использование встроенных функций

Популярны среди пользователей встроенные математические функции в Excel. Их синтаксис состоит из 2 частей: имени и аргументов. Последних может быть один или несколько. Обязательно формула начинается со знака «=». В противном случае Excel выдаст ошибку о неверном вводе функции.

К примеру, имеется формула «=СУММ(А2:М10)». В таком случае речь пойдет о суммировании всех значений диапазона А2:М10. Аргументы обязательно заключаются в скобки и указываются без пробелов. Писать имя функции с заглавной или строчной буквы – дело каждого. Для программного продукта это не играет никакой роли.

Если указана формула «=СУММ(А2;С7;М10)», это означает, что суммируются только 3 указанные ячейки. В состав функции могут входить до 30 элементов в аргументе.

Графики математических функций

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

Пример. Построить график функции для оператора sin. Шаг приращения = 0,5.

графики математических функций в excel

Примеры математических функций в Excel

Задание 1. Использовать функцию «суммесли». Данный оператор суммирует значения, если они удовлетворяют определенному условию и находятся в нужном диапазоне.

встроенные математические функции в excel

Задание 2. Использовать функцию «abs». Она находит модуль от заданного числа. В диалоговом окне указывают лишь один аргумент в виде одной ячейки. Диапазон участвовать в данном операторе не может.

особенности вызова встроенных математических функций excel

Задание 3. Применить функцию «степень». Суть заключается в возведении заданного числа в указанную пользователем степень. У функции 2 аргумента.

примеры математических функций в excel

Задание 4. Получить римскую запись от числа, написанного арабскими цифрами. Для этого понадобится формула «Римское».

основные математические функции excel

Задание на закрепление материала

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

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector