Любая формула в программе ms excel начинается с математического знака

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

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

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

Важная особенность электронной таблицы – автоматический пересчет значений при изменении данных в ячейках. Математические функции в 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).

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

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

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

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

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

MS Excel — Ввод формул

Под абсолютными ссылками понимаются ячейки, которые в процессе копирования не меняют своего вида. К примеру, имеется клетка А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. Их синтаксис состоит из 2 частей: имени и аргументов. Последних может быть один или несколько. Обязательно формула начинается со знака «=». В противном случае Excel выдаст ошибку о неверном вводе функции.

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

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

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

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

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

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

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

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

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

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

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

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

Источник: fb.ru

Работа в Excel с формулами и таблицами для чайников

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

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

Формулы в Excel для чайников

Чтобы задать формулу для ячейки, необходимо активизировать ее (поставить курсор) и ввести равно (=). Так же можно вводить знак равенства в строку формул. После введения формулы нажать Enter. В ячейке появится результат вычислений.

В Excel применяются стандартные математические операторы:

Оператор	Операция	Пример
+ (плюс)	Сложение	=В4+7
— (минус)	Вычитание	=А9-100
* (звездочка)	Умножение	=А3*2
/ (наклонная черта)	Деление	=А7/А8
^ (циркумфлекс)	Степень	=6^2
= (знак равенства)	Равно
Больше
=	Больше или равно
<>	Не равно

Символ «*» используется обязательно при умножении. Опускать его, как принято во время письменных арифметических вычислений, недопустимо. То есть запись (2+3)5 Excel не поймет.

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

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

При изменении значений в ячейках формула автоматически пересчитывает результат.

Ссылки можно комбинировать в рамках одной формулы с простыми числами.

Оператор умножил значение ячейки В2 на 0,5. Чтобы ввести в формулу ссылку на ячейку, достаточно щелкнуть по этой ячейке.

В нашем примере:

1. Поставили курсор в ячейку В3 и ввели =.
2. Щелкнули по ячейке В2 – Excel «обозначил» ее (имя ячейки появилось в формуле, вокруг ячейки образовался «мелькающий» прямоугольник).
3. Ввели знак *, значение 0,5 с клавиатуры и нажали ВВОД.

Если в одной формуле применяется несколько операторов, то программа обработает их в следующей последовательности:

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

Как в формуле Excel обозначить постоянную ячейку

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

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

1. Вручную заполним первые графы учебной таблицы. У нас – такой вариант:
2. Вспомним из математики: чтобы найти стоимость нескольких единиц товара, нужно цену за 1 единицу умножить на количество. Для вычисления стоимости введем формулу в ячейку D2: = цена за единицу * количество. Константы формулы – ссылки на ячейки с соответствующими значениями.
3. Нажимаем ВВОД – программа отображает значение умножения. Те же манипуляции необходимо произвести для всех ячеек. Как в Excel задать формулу для столбца: копируем формулу из первой ячейки в другие строки. Относительные ссылки – в помощь.

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

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

Ссылки в ячейке соотнесены со строкой.

Формула с абсолютной ссылкой ссылается на одну и ту же ячейку. То есть при автозаполнении или копировании константа остается неизменной (или постоянной).

Чтобы указать Excel на абсолютную ссылку, пользователю необходимо поставить знак доллара ($). Проще всего это сделать с помощью клавиши F4.

1. Создадим строку «Итого». Найдем общую стоимость всех товаров. Выделяем числовые значения столбца «Стоимость» плюс еще одну ячейку. Это диапазон D2:D9
2. Воспользуемся функцией автозаполнения. Кнопка находится на вкладке «Главная» в группе инструментов «Редактирование».
3. После нажатия на значок «Сумма» (или комбинации клавиш ALT+«=») слаживаются выделенные числа и отображается результат в пустой ячейке.

Сделаем еще один столбец, где рассчитаем долю каждого товара в общей стоимости. Для этого нужно:

1. Разделить стоимость одного товара на стоимость всех товаров и результат умножить на 100. Ссылка на ячейку со значением общей стоимости должна быть абсолютной, чтобы при копировании она оставалась неизменной.
2. Чтобы получить проценты в Excel, не обязательно умножать частное на 100. Выделяем ячейку с результатом и нажимаем «Процентный формат». Или нажимаем комбинацию горячих клавиш: CTRL+SHIFT+5
3. Копируем формулу на весь столбец: меняется только первое значение в формуле (относительная ссылка). Второе (абсолютная ссылка) остается прежним. Проверим правильность вычислений – найдем итог. 100%. Все правильно.

При создании формул используются следующие форматы абсолютных ссылок:

• $В$2 – при копировании остаются постоянными столбец и строка;
• B$2 – при копировании неизменна строка;
• $B2 – столбец не изменяется.

Как составить таблицу в Excel с формулами

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

Простейшие формулы заполнения таблиц в Excel:

1. Перед наименованиями товаров вставим еще один столбец. Выделяем любую ячейку в первой графе, щелкаем правой кнопкой мыши. Нажимаем «Вставить». Или жмем сначала комбинацию клавиш: CTRL+ПРОБЕЛ, чтобы выделить весь столбец листа. А потом комбинация: CTRL+SHIFT+» Новая графа.» src=»https://exceltable.com/formuly/images/formuly18-16.png» >
2. По такому же принципу можно заполнить, например, даты. Если промежутки между ними одинаковые – день, месяц, год. Введем в первую ячейку «окт.15», во вторую – «ноя.15». Выделим первые две ячейки и «протянем» за маркер вниз.
3. Найдем среднюю цену товаров. Выделяем столбец с ценами + еще одну ячейку. Открываем меню кнопки «Сумма» — выбираем формулу для автоматического расчета среднего значения.

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

• Создать таблицу
• Форматирование
• Функции Excel
• Формулы и диапазоны
• Фильтр и сортировка
• Диаграммы и графики
• Сводные таблицы
• Печать документов
• Базы данных и XML
• Возможности Excel
• Настройки параметры
• Уроки Excel
• Карта сайта
• Скачать примеры

Источник: exceltable.com

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

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

2020-07-21 21:20:50 161

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

Работа с формулами

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

Ввод

В первую очередь нужно активизировать любую ячейку, осуществив двойной клик по ней. Также можно использовать верхнюю строку. Ввод формулы обязательно начинается со знака «=», далее вписывается числовое значение и нажимается Enter. В выбранной ячейке отображается результат.

Программа понимает стандартные математические операторы:

• сложение «+»;
• вычитание «-»;
• умножение «*»;
• деление «/»;
• степень «^»;
• меньше « »;
• меньше или равно « =»;
• не равно «<>»;
• процент «%».

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

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

• «%» и «^»;
• «*» и «/»;
• «+» и «-».

Меняется последовательность математических действий и при помощи скобок – программа в первую очередь вычисляет значение в скобках.

Постоянные и абсолютные ссылки

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

Относительные ссылки помогают «растянуть» одну формулу на любое количество столбцов и строк. Как это работает на практике:

1. Создать таблицу с нужными данными.

1. Чтобы найти общую стоимость за каждый товар, нужно количество единиц умножить на цену. Для этого в первую ячейку вписать формулу через знак «=» и нажать Enter.

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

Абсолютный адрес обозначается знаком «$». Форматы отличаются:

1. Неизменна строка – A$1.
2. Неизменен столбец – $A
3. Неизменны строка и столбец – $A$1.

Работу абсолютной ссылки рассмотрим на примере расчета доли каждого товара в общей стоимости:

1. Сначала посчитать общую стоимость. Это делается несколькими способами: обычным сложением всех значений или автосуммой (выделить столбец с одной пустой ячейкой и на вкладке «Главная» справа выбрать опцию «Сумма», либо вкладка «Формулы» – «Автосумма»).

1. В отдельном столбце разделить стоимость первого товара на общую стоимость. При этом значение общей стоимости сделать абсолютным.

1. Для получения результата в процентах можно произвести умножение на 100. Однако проще выделить ячейку и выбрать в разделе «Главная» формат в виде значка «%».

1. С помощью маркера заполнения опустить формулу вниз. В итоге должно получиться 100%.

Виды формул

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

Простые

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

СУММ

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

ПРОИЗВЕД

Перемножает все числа в выделенном диапазоне.

ОКРУГЛ

Помогает произвести округление дробного числа в большую (ОКРУГЛВВЕРХ) или меньшую сторону (ОКРУГЛВНИЗ).

ВПР

Это – поиск необходимых данных в таблице или диапазоне по строкам. Рассмотрим функцию на примере поиска сотрудника из списка по коду.

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

Таблица – диапазон, в котором будет осуществляться поиск.

Номер столбца – порядковый номер столбца, где будет осуществляться поиск.

Альтернативные функции – ИНДЕКС/ПОИСКПОЗ.

СЦЕПИТЬ/СЦЕП

Объединение содержимого нескольких ячеек.

=СЦЕПИТЬ(значение1;значение2) – цельный текст

=СЦЕПИТЬ(значение1;» «;значение2) – между словами пробел или знак препинания

КОРЕНЬ

Вычисление квадратного корня любого числа.

ПРОПИСН

Альтернатива Caps Lock для преобразования текста.

СТРОЧН

Преобразует текст в нижний регистр.

СЧЁТ

Подсчитывает количество ячеек с числами.

=СЧЁТ(диапазон_ячеек)

СЖПРОБЕЛЫ

Убирает лишние пробелы. Это будет полезно, когда данные переносятся в таблицу из другого источника.

Сложные

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

Важно! Критерии обязательно нужно брать в кавычки.

ПСТР

Позволяет «достать» требуемое количество знаков из текста. Обычно используется при редактировании тайтлов в семантике.

ЕСЛИ

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

СУММЕСЛИ

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

Исходя из примера, программа посчитала суммы всех чисел, которые больше 10.

СУММЕСЛИМН

Суммирование чисел на основе нескольких заданных условий.

Программа посчитала общую сумму зарплат женщин-кассиров.

По такому же принципу работают функции СЧЁТЕСЛИ, СРЗНАЧЕСЛИ и т.п.

Комбинированные

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

Есть задача: найти сумму трех чисел и умножить ее на коэффициент 1,3, если она меньше 80, и на коэффициент 1,6 – если больше 80.

Комментарии

Максим Орлов

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

Источник: sysadmin-note.ru